This AP Void Payment Register query displays detailed information about voided payments, including check details, payee information, void date, associated bank account, and organization unit. It captures payment document name, addresses, and bank branch information for reconciliation.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--This query provides details of voided payments
HAOU.NAME ORGANIZATION_NAME,
CPD.PAYMENT_DOCUMENT_NAME,
ACA.CHECK_STOCK_ID,
ACA.CHECK_NUMBER,
ACA.CHECK_DATE,
ACA.AMOUNT,
ACA.VENDOR_NAME SUPPLIER_NAME,
ACA.VENDOR_SITE_CODE SUPPLIER_SITE_CODE,
ACA.ADDRESS_LINE1,
ACA.ADDRESS_LINE2,
ACA.ADDRESS_LINE3,
ACA.CITY ,
ACA.STATE ,
ACA.ZIP,
FTV.TERRITORY_SHORT_NAME COUNTRY,
ACA.VOID_DATE,
CPD.PAYMENT_DOCUMENT_ID,
ACA.BANK_ACCOUNT_NAME,
ACA.CHECK_ID,
ACA.DOC_SEQUENCE_ID,
ACA.VENDOR_ID SUPPLIER_ID,
CBA.CURRENCY_CODE BANK_CURRENCY_CODE,
CBBV.BANK_NAME,
CBBV.BANK_BRANCH_NAME,
CBBV.BRANCH_PARTY_ID PARTY_ID,
CBBV.BANK_INSTITUTION_TYPE,
ACA.CE_BANK_ACCT_USE_ID
FROM
AP_CHECKS_ALL ACA,
CE_BANK_ACCOUNTS CBA,
CE_BANK_ACCT_USES_ALL CBAUA,
CE_BANK_BRANCHES_V CBBV,
CE_PAYMENT_DOCUMENTS CPD,
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 = CBAUA.BANK_ACCT_USE_ID
AND CBAUA.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID
AND CBA.BANK_BRANCH_ID = CBBV.BRANCH_PARTY_ID
AND ACA.VOID_DATE IS NOT NULL
AND FTV.TERRITORY_CODE(+) = ACA.COUNTRY
AND HAOU.ORGANIZATION_ID = ACA.ORG_ID
AND HAOU.ORGANIZATION_ID = CBAUA.ORG_ID