Procure to Pay CycleSELECT R.SEGMENT1 AS RFQ_NUM, Q.SEGMENT1 AS QUOTATION_NUM
FROM PO_HEADERS_ALL R, PO_HEADERS_ALL Q, PO_HEADERS_ALL P
WHERE R.PO_HEADER_ID = Q.FROM_HEADER_ID
AND Q.PO_HEADER_ID = P.FROM_HEADER_ID
AND P.SEGMENT1 = '6128'(Purchase Order number)
Joining between PO Requisitions and Purchase Orders
SELECT prha.segment1 AS pr_number
FROM po_requisition_headers_all prha,
po_requisition_lines_all prla,
po_req_distributions_all prda,
po_distributions_all pda,
po_headers_all pha
WHERE prha.requisition_header_id = prla.requisition_header_id
AND prla.requisition_line_id = prda.requisition_line_id
AND prda.distribution_id = pda.req_distribution_id
AND pda.po_header_id = pha.po_header_id
AND pha.segment1 = '6129';
Joining between AP_INVOICES AND PO_HEADERS
select pha.segment1,apa.invoice_id
from
ap_invoices_all apa
,ap_invoice_lines_all aila
,po_headers_all pha
, ap_suppliers as1
where apa.invoice_id=aila.invoice_id
and aila.po_header_id=pha.po_header_id
and apa.invoice_num='10012'
and apa.vendor_id=as1.vendor_id
and trunc(apa.creation_date)=trunc(sysdate)-2
Joining between AP_INVOICES and xla tables
SELECT DISTINCT xe.*
FROM ap_invoices_all ai,
xla_events xe,
xla.xla_transaction_entities xte
WHERE xte.application_id = 200
AND xte.application_id = xe.application_id
AND ai.invoice_id = '212200'---Invoice id is dervied from above query
AND xte.entity_code = 'AP_INVOICES'
AND xte.source_id_int_1 = ai.invoice_id
AND xte.entity_id = xe.entity_id
ORDER BY
xe.entity_id,
xe.event_number;
xla_transaction_entities (xte).entity_code =
'TRANSACTIONS' or 'RECEIPTS' or 'ADJUSTMENTS' or 'PURCHASE_ORDER' or
'AP_INVOICES' or 'AP_PAYMENTS' or 'MTL_ACCOUNTING_EVENTS' or
'WIP_ACCOUNTING_EVENTS'
xte.source_id_int_1 = 'INVOICE_ID' or 'CHECK_ID' or'TRX_NUMBER'
