应付的帐龄分析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