This PO Invoice Payments query displays detailed invoice payment data for suppliers and buyers across purchase orders, helping users perform financial reconciliation, analyze supplier transactions, and track payment statuses.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--Supplier invoice payments with PO and buyer info for reconciliation.
PU.USERNAME BUYER_NAME,
PU.USER_ID BUYER_NUMBER,
-- Supplier Info
PSV.VENDOR_NAME SUPPLIER_NAME,
PSV.SEGMENT1 SUPPLIER_NUMBER,
PSV.VENDOR_TYPE_LOOKUP_CODE SUPPLIER_TYPE_CODE,
-- Invoice Info
AIA.INVOICE_NUM INVOICE_NUMBER,
AIA.INVOICE_AMOUNT,
AIA.SOURCE INVOICE_SOURCE,
AIA.APPROVAL_DESCRIPTION,
AIA.CREATION_DATE,
DECODE (
AIA.PAYMENT_STATUS_FLAG,
'N', 'Not paid',
'P', 'Partially paid',
'Y', 'Fully paid',
NULL
) PAYMENT_STATUS,
-- Invoice Distribution Info
AIDA.INVOICE_LINE_NUMBER,
AIDA.DISTRIBUTION_LINE_NUMBER,
AIDA.AMOUNT AMOUNT_DISPLAYED,
-- Payment Info
ACA.BANK_ACCOUNT_NAME,
AIPA.BANK_ACCOUNT_NUM BANK_ACCOUNT_NUMBER,
AIPA.BANK_NUM BANK_NUMBER,
AIPA.AMOUNT PAYMENT_AMOUNT,
AIPA.CREATION_DATE PAYMENT_CREATION_DATE,
AIPA.ACCOUNTING_DATE PAYMENT_GL_DATE,
AIPA.PAYMENT_NUM PAYMENT_NUMBER,
AIPA.PERIOD_NAME,
-- Currency / GL Info
GL.CURRENCY_CODE BASE_CURRENCY_CODE,
-- Shipment / PO Info
PLLA.SHIP_TO_ORGANIZATION_ID SHIP_TO_ORGANIZATION,
NVL(PLLA.QUANTITY * PLA.UNIT_PRICE, 0) SHIPMENT_AMOUNT,
(
NVL(PLLA.QUANTITY * PLA.UNIT_PRICE, 0)
) * NVL(
PHA.RATE,
DECODE(PHA.CURRENCY_CODE, GL.CURRENCY_CODE, 1, NULL)
) SHIPMENT_AMOUNT_BASE,
PLLA.SHIPMENT_NUM SHIPMENT_NUMBER,
-- Terms Info
ATT.NAME TERMS
FROM
PER_USERS PU,
PER_ALL_PEOPLE_F PAPF,
POZ_SUPPLIERS_V PSV,
AP_INVOICES_ALL AIA,
AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
AP_CHECKS_ALL ACA,
AP_INVOICE_PAYMENTS_ALL AIPA,
GL_LEDGERS GL,
PO_LINE_LOCATIONS_ALL PLLA,
PO_HEADERS_ALL PHA,
PO_LINES_ALL PLA,
AP_TERMS_TL ATT,
PO_DISTRIBUTIONS_ALL PDA,
AP_PAYMENT_SCHEDULES_ALL APSA,
GL_CODE_COMBINATIONS GCC,
EGP_SYSTEM_ITEMS ESI
WHERE
1 = 1
AND PAPF.PERSON_ID = PU.PERSON_ID
AND PHA.PO_HEADER_ID = PLA.PO_HEADER_ID
AND PLA.PO_LINE_ID = PLLA.PO_LINE_ID
AND PLLA.LINE_LOCATION_ID = PDA.LINE_LOCATION_ID
AND PDA.PO_DISTRIBUTION_ID = AIDA.PO_DISTRIBUTION_ID
AND AIDA.INVOICE_ID = AIA.INVOICE_ID
AND AIA.INVOICE_ID = AIPA.INVOICE_ID
AND AIPA.INVOICE_ID = APSA.INVOICE_ID
AND AIPA.CHECK_ID(+) = ACA.CHECK_ID
AND AIPA.PAYMENT_NUM = APSA.PAYMENT_NUM
AND AIA.VENDOR_ID = PSV.VENDOR_ID
AND AIPA.SET_OF_BOOKS_ID = GL.LEDGER_ID
AND AIPA.ACCTS_PAY_CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID(+)
AND AIA.TERMS_ID = ATT.TERM_ID(+)
AND ATT.LANGUAGE = USERENV('LANG')
AND PAPF.PERSON_ID = PHA.AGENT_ID
AND PLA.ITEM_ID = ESI.INVENTORY_ITEM_ID(+)
AND (
PHA.AGENT_ID IS NULL
OR TO_CHAR(PAPF.EFFECTIVE_START_DATE, 'YYYYMMDDHH24MISS') ||
TO_CHAR(PAPF.EFFECTIVE_END_DATE, 'YYYYMMDDHH24MISS') = (
SELECT
MAX(
TO_CHAR(PAPF1.EFFECTIVE_START_DATE, 'YYYYMMDDHH24MISS') ||
TO_CHAR(PAPF1.EFFECTIVE_END_DATE, 'YYYYMMDDHH24MISS')
)
FROM
PER_ALL_PEOPLE_F PAPF1
WHERE
PAPF1.PERSON_ID = PHA.AGENT_ID
)
);