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 Registr.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
