This AP Supplier Payment History Invoices query is designed to provide a comprehensive overview of supplier-related invoices and the corresponding payments made to them.
#sqlquery
SELECT
-- SQL4Fusion (An Orbit Analytics Project)
-- This query provides a summary of supplier invoice and payment transactions.
AIA.INVOICE_NUM INVOICE_NUMBER,
PSV.VENDOR_NAME SUPPLIER_NAME,
PSV.VENDOR_TYPE_LOOKUP_CODE SUPPLIER_TYPE,
AIA.INVOICE_DATE,
NVL (AIA.INVOICE_AMOUNT, 0) INVOICE_AMOUNT,
NVL (AIPA.AMOUNT, 0) PAYMENT_AMOUNT,
AIA.DESCRIPTION INVOICE_DESCRIPTION,
AIA.INVOICE_CURRENCY_CODE INVOICE_CURRENCY,
AIA.PAYMENT_CURRENCY_CODE PAYMENT_CURRENCY,
GL.CURRENCY_CODE GL_CURRENCY_CODE,
SUM (
DECODE (
AIA.PAYMENT_CURRENCY_CODE,
GL.CURRENCY_CODE,
AIPA.AMOUNT,
AIPA.PAYMENT_BASE_AMOUNT
)
) BASE_AMOUNT,
AIPA.PAYMENT_BASE_AMOUNT,
AIA.SOURCE,
AIA.INVOICE_TYPE_LOOKUP_CODE,
AIA.PAYMENT_STATUS_FLAG,
AIA.PAYMENT_METHOD_LOOKUP_CODE,
HAOU.NAME ORGANIZATION_NAME,
ACA.CHECK_NUMBER,
AIA.INVOICE_ID,
AIA.VENDOR_ID SUPPLIER_ID,
AIPA.CHECK_ID
FROM
AP_INVOICES_ALL AIA,
AP_INVOICE_PAYMENTS_ALL AIPA,
POZ_SUPPLIERS_V PSV,
AP_CHECKS_ALL ACA,
HR_ALL_ORGANIZATION_UNITS HAOU,
GL_LEDGERS GL
WHERE
1 = 1
AND AIPA.INVOICE_ID = AIA.INVOICE_ID
AND PSV.VENDOR_ID = AIA.VENDOR_ID
AND HAOU.ORGANIZATION_ID = AIA.ORG_ID
AND ACA.CHECK_ID = AIPA.CHECK_ID
AND GL.LEDGER_ID = AIA.SET_OF_BOOKS_ID
GROUP BY
AIA.INVOICE_NUM,
PSV.VENDOR_NAME,
PSV.VENDOR_TYPE_LOOKUP_CODE,
AIA.INVOICE_DATE,
NVL (AIA.INVOICE_AMOUNT, 0),
NVL (AIPA.AMOUNT, 0),
AIA.DESCRIPTION,
AIA.INVOICE_CURRENCY_CODE,
AIA.PAYMENT_CURRENCY_CODE,
GL.CURRENCY_CODE,
AIPA.PAYMENT_BASE_AMOUNT,
AIPA.AMOUNT,
AIA.SOURCE,
AIA.INVOICE_TYPE_LOOKUP_CODE,
AIA.PAYMENT_STATUS_FLAG,
AIA.PAYMENT_METHOD_LOOKUP_CODE,
HAOU.NAME,
ACA.CHECK_NUMBER,
AIA.INVOICE_ID,
AIA.VENDOR_ID,
AIPA.CHECK_ID