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

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
   


         

            成长

       /      |     \

    学习   总结   分享

QQ交流群:122230156

原文地址:https://www.cnblogs.com/benio/p/1928932.html