Unable to establish one to one relation between Invoice and Receipt. One receipt mapped to Multiple Payable Invoice Numbers. Because of same po_distribution_id across.
Using po_distributions_all.po_distribution_id to map ap_invoice_distributions_all.po_distribution_id.
And also rcv_transaction_id populates as null in ap_invoice_distributions_all. So can't join to RCV_TRANSACTIONS.
https://docs.oracle.com/en/cloud/saas/supply-chain-and-manufacturing/24b/oedsc/rcvtransactions-8520.html#Indexes
Any help on one to one relation between Receipt and Invoice.
SELECT aia.invoice_num
FROM
po_headers_all pha,
po_distributions_all pda,
ap_invoice_distributions_all aid,
ap_invoices_all aia,
rcv_shipment_lines rsl,
rcv_shipment_headers rsh,
rcv_transactions rt,
ap_invoice_lines_all ail,
po_line_locations_all pll,
po_lines_all PL
WHERE 1=1
AND pha.po_header_id = pda.po_header_id
AND aid.po_distribution_id = pda.po_distribution_id
AND aia.invoice_id = aid.invoice_id
AND aia.invoice_id = ail.invoice_id
AND rt.po_line_location_id = ail.po_line_location_id
AND rsl.po_header_id = pha.po_header_id
AND rsl.shipment_header_id = rsh.shipment_header_id
AND rt.shipment_line_id = rsl.shipment_line_id
AND rt.shipment_header_id = rsl.shipment_header_id
AND pha.po_header_id = pll.po_header_id
AND pll.po_line_id = pl.po_line_id
AND rt.transaction_type ='RECEIVE'
--AND rsh.receipt_num IN (15480)