How to join (ap invoice distributions all) AP table info with PO Table

注: 转自http://www.cnblogs.com/benio/archive/2011/01/06/1928932.html

 

Below is the SQL for AP->PO->SLA->GL  for *R12*.

 

select ph.segment1 po_number,
       aps.vendor_name,
       msi.segment1 item_number,
       msi.description item_description,
      
       'A/P PO Match' accrual_transaction,
       'AP*' source,
       (select organization_name
          from apps.org_organization_definitions
         where organization_id = pd.destination_organization_id)
      
       org,
       ai.invoice_num doc_number,
       ai.invoice_date doc_date,
       aid.invoice_line_number line,
       pl.unit_meas_lookup_code uom,
      
       aid.quantity_invoiced quantity,
       aid.unit_price,
       (nvl(xdl.unrounded_accounted_dr, 0) -
       nvl(xdl.unrounded_accounted_cr, 0)) accounted_amount,
       (nvl(xdl.unrounded_entered_cr, 0) - nvl(xdl.unrounded_entered_cr, 0)) entered_amount,
       xal.currency_code
  from apps.gl_je_headers                gjh,
       apps.gl_je_lines                  gjl,
       apps.gl_import_references         gir,
       apps.xla_ae_lines                 xal,
       apps.xla_ae_headers               xah,
       apps.xla_events                   xe,
       apps.xla_event_types_tl           xet,
       apps.xla_event_classes_tl         xect,
       apps.xla_distribution_links       xdl,
       apps.ap_invoice_distributions_all aid,
       apps.ap_invoices_all              ai,
       apps.po_headers_all               ph,
       apps.po_distributions_all         pd,
       apps.po_lines_all                 pl,
       apps.ap_suppliers                 aps,
       apps.mtl_system_items_b           msi
 where gjh.je_header_id = gjl.je_header_id
   and gjh.je_header_id = gir.je_header_id
   and gjl.je_header_id = gir.je_header_id
   and gir.je_line_num = gjl.je_line_num
   and gir.gl_sl_link_id = xal.gl_sl_link_id
   and xal.ae_header_id = xah.ae_header_id
   and xah.event_id = xe.event_id
   and xe.event_type_code = xet.event_type_code
   and xe.application_id = xet.application_id
   and xet.language = userenv('LANG')
   and xect.entity_code = xet.entity_code
   and xect.event_class_code = xet.event_class_code
   and xect.application_id = xe.application_id
   and xect.language = userenv('LANG')
   and xah.ae_header_id = xdl.ae_header_id
   and xal.ae_line_num = xdl.ae_line_num
   and xdl.source_distribution_type = 'AP_INV_DIST'
   and xdl.source_distribution_id_num_1 = aid.invoice_distribution_id
   and ai.invoice_id = aid.invoice_id
   and aid.po_distribution_id = pd.po_distribution_id
   and gjh.je_source = 'Payables'
   and ph.po_header_id = pd.po_header_id
   and pl.po_header_id = ph.po_header_id
   and pd.po_line_id = pl.po_line_id
   and pd.org_id = &org_id
   and ph.org_id = &org_id
   and ai.org_id = &org_id
   and aid.org_id = &org_id
   and gjl.code_combination_id = &ccid
   and ph.vendor_id = aps.vendor_id
   and msi.inventory_item_id = pl.item_id
   and msi.organization_id = pd.destination_organization_id