应付的帐龄分析SQL

select hao.name 公司名称,
       
       pha.segment1 订单编号,
       
       to_char(pla.line_num) 订单行编号,
       
       pv.vendor_name 供应商名,
       
       pvs.vendor_site_code 供应商地点,
       
       nvl(msib.segment1, null) 物料编码, 
       
       pla.item_description 物料名称,
       
       hao1.name 发运组织,
       
       rec.currency_code 订单币种,
       
       decode(rec.transaction_type,
              
              'RETURN TO VENDOR',
              
              -rec.quantity,
              
              rec.quantity) 数量,
       
       pla.unit_price 单价,
       
       decode(rrs.entered_cr, null, -rrs.entered_dr, rrs.entered_cr) 金额,
       
       rec.currency_conversion_rate 接收汇率,
       
       decode(rrs.accounted_cr, null, -rrs.accounted_dr, rrs.accounted_cr) 本位币金额,
       
       plla.accrue_on_receipt_flag 接收应计标志,
       
       gcc.segment3 科目,
       
       to_char(rec.transaction_date, 'YYYY-MM-DD') 总帐日期,
       
       '采购接收' 来源,
       
       rsh.receipt_num 接收编号,
       
       rec.currency_conversion_date 接收日期,
       
       pl2.displayed_field 接收类型,
       
       pda.recovery_rate 退税率,
       
       apca.name 税码

  from po_headers_all pha,
       
       po_lines_all pla,
       
       po_line_locations_all plla,
       
       po_distributions_all pda,
       
       po_vendors pv,
       
       po_vendor_sites_all pvs,
       
       hr_all_organization_units hao,
       
       hr_all_organization_units hao1,
       
       gl_code_combinations gcc,
       
       rcv_transactions rec,
       
       rcv_shipment_headers rsh,
       
       ap_tax_codes_all apca,
       
       rcv_receiving_sub_ledger rrs,
       
       po_lookup_codes pl2,
       
       mtl_system_items_b msib

 where pha.org_id = hao.organization_id
      
   and msib.organization_id = 103
      
   and pha.vendor_id = pv.vendor_id
      
   and pha.vendor_site_id = pvs.vendor_site_id
      
   and plla.ship_to_organization_id = hao1.organization_id
      
   and pha.po_header_id = pla.po_header_id
      
   and plla.po_line_id = pla.po_line_id
      
   and user_entered_flag = 'Y'
      
   and rec.po_line_id = pla.po_line_id
      
   and rec.po_header_id = pha.po_header_id
      
   and rec.po_line_location_id = plla.line_location_id
      
   and pda.line_location_id = plla.line_location_id
      
   and pda.accrual_account_id = gcc.code_combination_id
      
   and rec.shipment_header_id = rsh.shipment_header_id
      
   and plla.tax_code_id = apca.tax_id(+)
      
   and pda.po_distribution_id = rrs.reference3
      
   and rec.transaction_id = rrs.rcv_transaction_id
      
   and gcc.code_combination_id = rrs.code_combination_id
      
   and rrs.set_of_books_id = fnd_profile.value('GL_SET_OF_BKS_ID')
      
   and rec.transaction_type in ('RECEIVE', 'RETURN TO VENDOR', 'CORRECT')
      
   and pl2.lookup_code = rec.transaction_type
      
   and pl2.lookup_type = 'RCV TRANSACTION TYPE'
      
   and pla.item_id = msib.inventory_item_id 

union all

select hao.name 公司名称,
       
       pha.segment1 订单编号,
       
       to_char(pla.line_num) 订单行编号,
       
       pv.vendor_name 供应商名,
       
       pvs.vendor_site_code 供应商地点,
       
       nvl(msib.segment1, null) 物料编码,
       
       pla.item_description 物料名称,
       
       hao1.name 发运组织,
       
       decode(rt.currency_code, null, pha.currency_code, rt.currency_code) 订单币种,
       
       -aida.quantity_invoiced 数量,
       
       aida.unit_price 单价,
       
       nvl(aala.entered_cr, 0) - nvl(aala.entered_dr, 0) 金额,
       
       rt.currency_conversion_rate 接收汇率,
       
       nvl(aala.accounted_cr, 0) - nvl(aala.accounted_dr, 0) 本位币金额,
       
       plla.accrue_on_receipt_flag 接收应计标志,
       
       gcc.segment3 科目,
       
       to_char(aida.accounting_date, 'YYYY-MM-DD') 总帐日期,
       
       '发票匹配' 来源,
       
       rsh.receipt_num 接收编号,
       
       rt.currency_conversion_date 接收日期,
       
       pl1.displayed_field 接收类型,
       
       pda.recovery_rate 退税率,
       
       apca.name 税码

  from ap_invoices_all aia,
       
       ap_invoice_distributions_all aida,
       
       ap_ae_lines_all aala,
       
       hr_all_organization_units hao,
       
       gl_code_combinations gcc,
       
       po_vendors pv,
       
       po_vendor_sites_all pvs,
       
       po_distributions_all pda,
       
       po_line_locations_all plla,
       
       hr_all_organization_units hao1,
       
       po_lines_all pla,
       
       po_headers_all pha,
       
       rcv_shipment_headers rsh,
       
       rcv_transactions rt,
       
       ap_tax_codes_all apca,
       
       po_lookup_codes pl1,
       
       mtl_system_items_b msib 
       
 where aia.invoice_id = aida.invoice_id
      
   and msib.organization_id = 103 
      
   and aida.org_id = hao.organization_id
      
   and aala.source_table = 'AP_INVOICE_DISTRIBUTIONS'
      
   and aida.invoice_distribution_id = aala.source_id
      
   and aala.code_combination_id = gcc.code_combination_id
      
   and aida.po_distribution_id = pda.po_distribution_id
      
   and pda.line_location_id = plla.line_location_id
      
   and plla.ship_to_organization_id = hao1.organization_id
      
   and plla.po_line_id = pla.po_line_id
      
   and pla.po_header_id = pha.po_header_id
      
   and aia.vendor_id = pv.vendor_id
      
   and aia.vendor_site_id = pvs.vendor_site_id
      
   and aida.po_distribution_id is not null
      
   and aala.ae_line_type_code = 'AP ACCRUAL'
      
   and aida.rcv_transaction_id = rt.transaction_id(+)
      
   and rt.shipment_header_id = rsh.shipment_header_id(+)
      
   and plla.tax_code_id = apca.tax_id(+)
      
   and (rt.transaction_type in ('RECEIVE', 'RETURN TO VENDOR') or
       
       rt.transaction_type is null)
      
   and pl1.lookup_code(+) = rt.transaction_type
      
   and pl1.lookup_type(+) = 'RCV TRANSACTION TYPE'
      
   and pla.item_id = msib.inventory_item_id 

union all

select hao.name 公司名称,
       
       aia.invoice_num 订单编号,
       
       to_char(aida.distribution_line_number) 订单行编号,
       
       pv.vendor_name 供应商名,
       
       pvs.vendor_site_code 供应商地点,
       
       null 物料编码,
       
       null 物料名称,
       
       null 发运组织,
       
       aia.invoice_currency_code 订单币种,
       
       null 数量,
       
       null 单价,
       
       aia.invoice_amount * (-1) 金额,
       
       aida.exchange_rate 接收汇率,
       
       nvl(aida.base_amount * (-1), aida.amount * (-1)) 本位币金额,
       
       null 接收应计标志,
       
       gcc.segment3 科目,
       
       to_char(aida.accounting_date, 'YYYY-MM-DD') 总帐日期,
       
       '发票无匹配' 来源,
       
       null 接收编号,
       
       null 接收日期,
       
       null 接收类型,
       
       null 退税率,
       
       null 税码

  from ap_invoice_distributions_all aida,
       
       ap_invoices_all aia,
       
       hr_all_organization_units hao,
       
       gl_code_combinations gcc,
       
       po_vendors pv,
       
       po_vendor_sites_all pvs

 where gcc.code_combination_id = aida.dist_code_combination_id
      
   and aia.invoice_id = aida.invoice_id
      
   and pv.vendor_id = aia.vendor_id
      
   and aida.org_id = hao.organization_id
      
   and pv.vendor_id = pvs.vendor_id
      
   and aia.vendor_site_id = pvs.vendor_site_id
      
   and gcc.segment3 like '2121020000'
      
   and aida.po_distribution_id is null
      
   and aida.reversal_flag is null