This CE AP Payment Details Report query retrieves AP check payment details excluding netting accounts, providing payment and supplier information to support payment tracking.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--Retrieves AP check payment details excluding netting accounts.
PS.SEGMENT1 SUPPLIER_NUMBER,
HP.PARTY_NAME SUPPLIER_NAME,
PSSAM.VENDOR_SITE_CODE SUPPLIER_SITE_CODE,
NVL (PS.VENDOR_TYPE_LOOKUP_CODE, '-1') SUPPLIER_TYPE,
ACA.CHECK_NUMBER,
NVL (
ACA.CLEARED_AMOUNT,
ACA.AMOUNT
) AMOUNT,
NVL (
NVL (
ACA.CLEARED_BASE_AMOUNT,
ACA.CLEARED_AMOUNT
),
NVL (BASE_AMOUNT, AMOUNT)
) BASE_AMOUNT,
ACA.CURRENCY_CODE,
NVL (PSSAM.PAYMENT_PRIORITY, 99) PAYMENT_PRIORITY,
NVL (
PSSAM.PAY_GROUP_LOOKUP_CODE,
'-1'
) PAYGROUP,
ACA.CLEARED_DATE,
ACA.FUTURE_PAY_DUE_DATE MATURITY_DATE,
ACA.CHECK_DATE PAYMENT_DATE,
ACA.ACTUAL_VALUE_DATE,
ACA.ANTICIPATED_VALUE_DATE
FROM
AP_CHECKS_ALL ACA,
CE_BANK_ACCT_USES_ALL CBAUA,
POZ_SUPPLIER_SITES_ALL_M PSSAM,
POZ_SUPPLIERS PS,
HZ_PARTIES HP,
HR_ALL_ORGANIZATION_UNITS HAOU
WHERE
1 = 1
AND ACA.VENDOR_SITE_ID = PSSAM.VENDOR_SITE_ID
AND PS.PARTY_ID = HP.PARTY_ID (+)
AND ACA.CE_BANK_ACCT_USE_ID = CBAUA.BANK_ACCT_USE_ID
AND ACA.ORG_ID = HAOU.ORGANIZATION_ID
AND (
PSSAM.PRC_BU_ID = ACA.ORG_ID
OR PSSAM.PRC_BU_ID IS NULL
)
AND PSSAM.VENDOR_ID = PS.VENDOR_ID
AND (
(
ACA.STATUS_LOOKUP_CODE IN (
'NEGOTIABLE',
'CLEARED',
'CLEARED BUT UNACCOUNTED',
'RECONCILED',
'RECONCILED UNACCOUNTED'
)
)
OR (
ACA.STATUS_LOOKUP_CODE = 'ISSUED'
AND ACA.FUTURE_PAY_DUE_DATE IS NOT NULL
)
)
AND NOT EXISTS (
SELECT
NULL
FROM
ce_bank_accounts ba
WHERE
ba.bank_account_id = CBAUA.bank_account_id
AND ba.netting_acct_flag = 'Y'
)