Ap_invoice_distributions_all and PO tables

In ap_invoice_distributions_all, rcv_transaciton_id is null and sometimes has a value. Why? (See po_line_locations_all.match_option)
Maybe you still remember that when we select a Match on the invoice workbench, sometimes it is a Match PO, sometimes it is a Match Receipt, in fact this is the reason, of course, the root cause of this matching item is a switch on the shipment of the PO: receip, or Purchase Order. If it is receip, when the PO is matched, you can only select Receipt (rcv_transaciton_id has a value after the matching is successfully saved), and vice versa, you can only select Purchase Order for the matching value (rcv_transaciton_id has no value after the matching is saved successfully)
In fact, there is also a po_distribution_id field in this ap_invoice_distributions_all. No matter whether rcv_transaciton_id is null or not, the po_distribution_id field is always set to a value with the PO.
There is a need like this: Query ---- invoice number, receipt number, and purchase order number
(I always use po_distribution_id as the condition, instead of rcv_transaciton_id)
----------------------------------------
SELECT ai. invoice_num,
Rsh. receipt_num,
Ph. segment1,
Aid. distribution_line_number,
Aid. rcv_transaction_id,
Aid. po_distribution_id
FROM ap_invoices_all ai,
Ap_invoice_distributions_all aid,
Po_distributions_all pd,
Rcv_transactions rt,
Rcv_shipment_lines rsl,
Rcv_shipment_headers rsh,
Po_headers_all ph
WHERE ai. invoice_id = aid. invoice_id
AND aid. set_of_books_id = '& sob'
AND aid. period_name = '& period_name'
AND aid. po_distribution_id = pd. po_distribution_id
AND pd. po_distribution_id = rt. po_distribution_id
AND rt. po_distribution_id = rsl. po_distribution_id
AND rsl. shipment_line_id = rt. shipment_line_id
AND rsh. shipment_header_id = rsl. shipment_header_id
AND rt. destination_type_code = 'grouping ing'
AND rt. po_header_id = ph. po_header_id
Order by 1,
2

Ap_invoice_distributions_all

 

The line_type_Lookup_code field identifies the type of the verification content.

Line_type_Lookup_code value:
FREIGHT: FREIGHT
ITEM: Line
PREPAY: Prepayment
TAX: TAX