AP Payment History Exceptions (Oracle Fusion SQL)

Enterprise Resource Planning (ERP) Oracle Account Payables (AP-Fusion-SQL's)
1

This AP payment history exceptions query retrieves payment history records in Oracle Fusion Accounts Payable where accounting exceptions exist — specifically payment transactions whose POSTED_FLAG indicates Unaccounted or Did Not Sweep.

#sqlquery


SELECT
  --SQL4Fusion (An Orbit Analytics Project)
  --This query retrieves payment transactions in oracle fusion accounts payable where the accounting process has exceptions
  HAOU.NAME ORGANIZATION_NAME,
  PSV.VENDOR_NAME SUPPLIER_NAME,
  PSV.SEGMENT1 SUPPLIER_NUMBER, 
  ACA.CHECK_NUMBER,
  ACA.CURRENCY_CODE CURRENCY,
  NVL (ACA.AMOUNT, 0) CHECK_AMOUNT,  
  APHA.ACCOUNTING_DATE,  
  DECODE(APHA.POSTED_FLAG,'N','Un Accounted','S','Did Not Sweep',APHA.POSTED_FLAG) EXCEPTION, 
  SUBSTR (APHA.TRANSACTION_TYPE, 9) TRANSACTION_TYPE,
  ACA.BANK_ACCOUNT_NAME   
FROM
  AP_PAYMENT_HISTORY_ALL APHA,
  AP_CHECKS_ALL ACA,
  POZ_SUPPLIERS_V PSV,
  AP_SYSTEM_PARAMETERS_ALL ASPA,
  HR_ALL_ORGANIZATION_UNITS HAOU
WHERE
  1 = 1
  AND APHA.POSTED_FLAG IN ('N', 'S')
  AND APHA.CHECK_ID = ACA.CHECK_ID
  AND ACA.VENDOR_ID= PSV.VENDOR_ID 
  AND APHA.ORG_ID=ASPA.ORG_ID
  AND ACA.ORG_ID = HAOU.ORGANIZATION_ID