This AP PO to AP Status query retrieves a detailed report of Purchase Orders (POs) and their matching Invoices. It is designed to help analyze how purchase lines are matched to supplier invoices.
#sqlquery
SELECT
-- SQL4Fusion (An Orbit Analytics Project).
-- This query provides a detailed report of Purchase Orders and their matching Invoices.
PHA.SEGMENT1 PURCHASE_ORDER_NUMBER,
PHA.PO_HEADER_ID,
PHA.TYPE_LOOKUP_CODE PURCHASE_ORDER_TYPE,
PHA.VENDOR_ID SUPPLIER_ID,
PLA.LINE_NUM PURCHASE_LINE_NUMBER,
ESIB.ITEM_NUMBER PURCHASE_ITEM_NUMBER,
ESIB.INVENTORY_ITEM_ID,
PSV.VENDOR_NAME SUPPLIER_NAME,
AIA.INVOICE_NUM INVOICE_NUMBER,
AIA.INVOICE_AMOUNT,
AIA.INVOICE_DATE,
AIA.INVOICE_CURRENCY_CODE,
AIA.INVOICE_TYPE_LOOKUP_CODE,
AIA.PAYMENT_STATUS_FLAG,
AIA.SOURCE,
AILA.LINE_NUMBER INVOICE_LINE_NUMBER,
AILA.ITEM_DESCRIPTION,
AILA.LINE_TYPE_LOOKUP_CODE INVOICE_LINE_TYPE,
AILA.INVOICE_ID,
AIDA.AMOUNT DISTRIBUTION_AMOUNT,
AIDA.FINAL_MATCH_FLAG,
AIDA.DIST_MATCH_TYPE,
AIDA.DIST_CODE_COMBINATION_ID,
AIDA.INVOICE_DISTRIBUTION_ID,
HAOU.NAME ORGANIZATION_NAME
FROM
EGP_SYSTEM_ITEMS_B ESIB,
PO_HEADERS_ALL PHA,
PO_LINES_ALL PLA,
PO_DISTRIBUTIONS_ALL PDA,
POZ_SUPPLIERS_V PSV,
AP_INVOICES_ALL AIA,
AP_INVOICE_LINES_ALL AILA,
AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
HR_ALL_ORGANIZATION_UNITS HAOU
WHERE
1 = 1
AND ESIB.INVENTORY_ITEM_ID(+) = PLA.ITEM_ID
AND PHA.PO_HEADER_ID = PLA.PO_HEADER_ID
AND PLA.PO_LINE_ID = PDA.PO_LINE_ID
AND PSV.VENDOR_ID = PHA.VENDOR_ID
AND AIA.INVOICE_ID = AILA.INVOICE_ID
AND AILA.LINE_NUMBER = AIDA.INVOICE_LINE_NUMBER
AND AIA.INVOICE_ID = AIDA.INVOICE_ID
AND AIDA.PO_DISTRIBUTION_ID = PDA.PO_DISTRIBUTION_ID
AND HAOU.ORGANIZATION_ID = AIA.ORG_ID
ORDER BY INVOICE_LINE_NUMBER