AP invoice payable Certificate

Module: AP

Related table: AP. AP_INVOICES_ALL AIA Invoice header

AP. AP_INVOICE_LINES_ALL AIL invoice line

APPS. PO_VENDORS supplier

PO. PO_HEADERS_ALL purchase Header

RCV_SHIPMENT_HEADERS RSH receives the transaction Header

RCV_SHIPMENT_LINES RSL receives transaction rows

Other: connection between the receiving transaction and the invoice, AIL. RCV_SHIPMENT_LINE_ID = RSL. SHIPMENT_LINE_ID (+)
And rsl. SHIPMENT_HEADER_ID = RSH. SHIPMENT_HEADER_ID (+)

SQL:

Select aia. invoice_num,
Aia. invoice_date,
Aia. creation_date,
Aia. vendor_id,
Aia. invoice_currency_code,
Ail. amount,
Ail. base_amount,
Aia. description,
Decode (ail. base_amount,
Null,
1,
Round (ail. base_amount/ail. amount, 4 )),
Ail. inventory_item_id,
Ail. quantity_invoiced,
Ail. unit_price,
Ail. amount,
Ail. base_amount,
Ail. rcv_transaction_id,
Msi. segment1,
Msi. description,
Rsh. receipt_num,
Pha. segment1,
Pv. vendor_name,
Rsl. quantity_policed
From ap. ap_invoices_all aia,
Ap. ap_invoice_lines_all ail,
Po. po_headers_all pha,
Apps. po_vendors pv,
Inv. mtl_system_items_ B msi,
Rcv_shipment_headers rsh, -- receiving Header
Rcv_shipment_lines rsl -- receiving line
Where aia. invoice_id = ail. invoice_id
And ail. po_header_id = pha. po_header_id (+)
And aia. vendor_id = pv. vendor_id
And ail. inventory_item_id = msi. inventory_item_id (+)
And ail.org _ id = msi. organization_id (+)
And ail. rcv_shipment_line_id = rsl. shipment_line_id (+)
And rsl. shipment_header_id = rsh. shipment_header_id (+)
And ail. line_type_lookup_code = 'item'