AP Invoice Payment Exceptions (Oracle Fusion SQL)

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

This AP Invoice Payment Exceptions query retrieves supplier payment records in Oracle Fusion Accounts Payable where accounting exceptions exist — specifically payments whose related invoice payments have a POSTED_FLAG indicating Unaccounted or Did Not Sweep.

#sqlquery

SELECT
  --SQL4Fusion (An Orbit Analytics Project)
  --This query retrieves a list of supplier payments in oracle fusion where the related accounting entries have exceptions
  HAOU.NAME ORGANIZATION_NAME,
  PSV.VENDOR_NAME SUPPLIER_NAME,
  PSV.SEGMENT1 SUPPLIER_NUMBER,
  AIA.INVOICE_NUM INVOICE_NUMBER, 
  ACA.CHECK_NUMBER CHECK_NUMBER,
  ACA.CURRENCY_CODE CURRENCY,
  NVL (ACA.AMOUNT, 0) CHECK_AMOUNT, 
  ACA.STATUS_LOOKUP_CODE  CHECK_STATUS_LOOKUP_CODE , 
  AIPA.ACCOUNTING_DATE,  
  DECODE(AIPA.POSTED_FLAG, 'N', 'Un Accounted', 'S', 'Did Not Sweep',AIPA.POSTED_FLAG) EXCEPTION   
 FROM
  AP_CHECKS_ALL ACA,
  AP_INVOICE_PAYMENTS_ALL AIPA,
  AP_INVOICES_ALL AIA, 
  POZ_SUPPLIERS_V PSV,
  HR_ALL_ORGANIZATION_UNITS HAOU,
  AP_SYSTEM_PARAMETERS_ALL ASPA 
WHERE
  1 = 1
  AND ACA.CHECK_ID = AIPA.CHECK_ID
  AND AIPA.INVOICE_ID=AIA.INVOICE_ID 
  AND AIA.VENDOR_ID = PSV.VENDOR_ID
  AND AIPA.POSTED_FLAG IN ('N', 'S')
  AND AIA.ORG_ID = HAOU.ORGANIZATION_ID
  AND HAOU.ORGANIZATION_ID = ASPA.ORG_ID
ORDER BY AIA.INVOICE_NUM