This AP Supplier Payment History query provides the supplier payment information including the bank details.
#sqlquery
SELECT
-- SQL4Fusion (An Orbit Analytics Project)
-- This query generates a detailed report of supplier payments.
PSV.VENDOR_NAME SUPPLIER_NAME,
PSV.SEGMENT1 SUPPLIER_NUMBER,
AIPA.PAYMENT_NUM PAYMENT_NUMBER,
ASS.VENDOR_SITE_CODE SUPPLIER_SITE_CODE,
DECODE (
ASS.ADDRESS_LINE1,
NULL,
'',
ASS.ADDRESS_LINE1 || ','
) ADDRESS_LINE1,
DECODE (
ASS.CITY,
NULL,
'',
ASS.CITY || ','
) CITY,
DECODE (
ASS.STATE,
NULL,
ASS.PROVINCE || ',',
ASS.STATE || ','
) STATE,
ASS.ZIP,
ACA.BANK_ACCOUNT_NAME,
ACA.CHECK_NUMBER,
ACA.CHECK_DATE,
NVL (ACA.AMOUNT, 0) CHECK_AMOUNT,
ACA.CURRENCY_CODE,
SUM (
DECODE (
AIA.PAYMENT_CURRENCY_CODE,
GL.CURRENCY_CODE,
AIPA.AMOUNT,
AIPA.PAYMENT_BASE_AMOUNT
)
) BASE_AMOUNT,
AIPA.ACCOUNTING_DATE,
ACA.VOID_DATE,
PSV.VENDOR_TYPE_LOOKUP_CODE SUPPLIER_TYPE,
HAOU.NAME ORGANIZATION_NAME,
PSV.VENDOR_ID SUPPLIER_ID,
ACA.CHECK_ID,
AIA.ORG_ID,
AIA.SET_OF_BOOKS_ID,
GL.NAME LEDGER_NAME
FROM
AP_CHECKS_ALL ACA,
AP_INVOICE_PAYMENTS_ALL AIPA,
AP_INVOICES_ALL AIA,
POZ_SUPPLIER_SITES_V ASS,
POZ_SUPPLIERS_V PSV,
HR_ALL_ORGANIZATION_UNITS HAOU,
GL_LEDGERS GL
WHERE
1 = 1
AND HAOU.ORGANIZATION_ID = AIA.ORG_ID
AND ASS.VENDOR_ID = PSV.VENDOR_ID
AND ACA.VENDOR_SITE_ID = ASS.VENDOR_SITE_ID
AND AIPA.INVOICE_ID = AIA.INVOICE_ID
AND ACA.CHECK_ID = AIPA.CHECK_ID
AND GL.LEDGER_ID = AIA.SET_OF_BOOKS_ID
GROUP BY
PSV.VENDOR_NAME,
PSV.SEGMENT1,
AIPA.PAYMENT_NUM,
ASS.VENDOR_SITE_CODE,
DECODE (
ASS.ADDRESS_LINE1,
NULL,
'',
ASS.ADDRESS_LINE1 || ','
),
DECODE (
ASS.CITY,
NULL,
'',
ASS.CITY || ','
),
DECODE (
ASS.STATE,
NULL,
ASS.PROVINCE || ',',
ASS.STATE || ','
),
ASS.ZIP,
ACA.BANK_ACCOUNT_NAME,
ACA.CHECK_NUMBER,
ACA.CHECK_DATE,
NVL (ACA.AMOUNT, 0),
ACA.CURRENCY_CODE,
AIPA.ACCOUNTING_DATE,
ACA.VOID_DATE,
PSV.VENDOR_TYPE_LOOKUP_CODE,
HAOU.NAME,
PSV.VENDOR_ID,
ACA.CHECK_ID,
AIA.ORG_ID,
AIA.SET_OF_BOOKS_ID,
GL.CURRENCY_CODE,
GL.NAME