PJC: Table/Join for Purchase Receipt in PJC_EXP_ITEMS_ALL to AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_ID

Summary:

We are developing a report for business users such that -

For a given Cost Transaction sourced from P2P it must derive the related invoice(s) and Payments.

Challenge:

For Oracle Fusion Cost Management Transaction Source

(a) Purchase Receipt Number:

Derive via -

PJC_EXP_ITEMS_ALL.ORIGINAL_HEADER_ID → CMR_RCV_TRANSACTIONS.EXTERNAL_SYSTEM_REF_ID 

(b) Purchase Order Number:

Derive via -

PJC_EXP_ITEMS_ALL.PARENT_DIST_ID → PO_DISTRIBUTIONS.PO_DISTRIBUTION_ID

(c) Purchase/AP Invoice:

In this case, it seems there's no direct relation to AP is maintained at PJC_EXP_ITEMS_ALL

and it can only be derived via (b) to Join AP_INVOICE_DISTRIBUTIONS_ALL