This AP Payment Register query lists detailed information about payments made via checks, including payment method, document details, vendor address, cleared amounts, bank accounts, and status.
#sqlquery
SELECT
-- SQL4Fusion (An Orbit Analytics Project)
-- This query generates a Payment Register Report, listing payment details
HAOU.NAME ORGANIZATION_NAME,
CPD.PAYMENT_DOCUMENT_NAME PAYMENT_DOCUMENT,
CBB.BANK_NAME,
CBB.BANK_BRANCH_NAME,
ACA.CHECK_NUMBER PAYMENT_NUMBER,
ACA.PAYMENT_METHOD_CODE PAYMENT_METHOD,
ACA.CHECK_DATE PAYMENT_DATE,
ACA.AMOUNT CHECK_AMOUNT,
SUBSTR (ACA.VENDOR_NAME, 1, 240) SUPPLIER_NAME,
ACA.VENDOR_SITE_CODE VENDOR_SITE_CODE,
SUBSTR (ACA.ADDRESS_LINE1, 1, 23) ADDRESS_LINE1,
SUBSTR (ACA.ADDRESS_LINE2, 1, 23) ADDRESS_LINE2,
SUBSTR (ACA.ADDRESS_LINE3, 1, 23) ADDRESS_LINE3,
ACA.CITY,
ACA.STATE,
ACA.ZIP,
SUBSTR (FTV.TERRITORY_SHORT_NAME, 1, 23) COUNTRY,
ACA.CLEARED_DATE CLEARED_DATE,
ACA.CLEARED_AMOUNT CLEARED_AMOUNT,
ACA.BANK_ACCOUNT_NAME,
CBB.BRANCH_PARTY_ID,
CBA.CURRENCY_CODE CURRENCY_CODE,
ACA.CURRENCY_CODE PAY_CURRENCY_CODE,
ACA.PAYMENT_TYPE_FLAG PAYMENT_TYPE,
CBB.BANK_INSTITUTION_TYPE,
ACA.CHECK_ID ,
ACA.ORG_ID ,
HAOU.ORGANIZATION_ID
FROM
AP_CHECKS_ALL ACA,
CE_PAYMENT_DOCUMENTS CPD,
CE_BANK_ACCOUNTS CBA,
CE_BANK_ACCT_USES_ALL CBAU,
CE_BANK_BRANCHES_V CBB,
AP_LOOKUP_CODES ALC,
FND_TERRITORIES_VL FTV,
HR_ALL_ORGANIZATION_UNITS HAOU
WHERE 1=1
AND CPD.PAYMENT_DOCUMENT_ID(+) = ACA.PAYMENT_DOCUMENT_ID
AND ACA.CE_BANK_ACCT_USE_ID = CBAU.BANK_ACCT_USE_ID
AND CBAU.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID
AND CBA.BANK_BRANCH_ID = CBB.BRANCH_PARTY_ID
AND ACA.COUNTRY = FTV.TERRITORY_CODE(+)
AND ALC.LOOKUP_TYPE = 'CHECK STATE'
AND ALC.LOOKUP_CODE = ACA.STATUS_LOOKUP_CODE
AND HAOU.ORGANIZATION_ID = ACA.ORG_ID