AP Supplier Invoice Exceptions (Oracle Fusion SQL)

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

This AP supplier invoice exceptions query retrieves invoice distribution records from Oracle Fusion Accounts Payable where accounting exceptions are present — specifically where the POSTED_FLAG indicates Unaccounted or Did Not Sweep.

#sqlquery

SELECT
  --SQL4Fusion (An Orbit Analytics Project)
  --This query retrieves invoice distribution records in oracle fusion accounts payable where accounting exceptions exist
  HAOU.NAME ORGANIZATION_NAME,
  PSV.VENDOR_NAME SUPPLIER_NAME,
  PSV.SEGMENT1 SUPPLIER_NUMBER,
  AIA.INVOICE_NUM INVOICE_NUMBER,
  AIA.INVOICE_DATE,
  AIA.INVOICE_TYPE_LOOKUP_CODE INVOICE_TYPE,
  AIA.SOURCE INVOICE_SOURCE,
  AIA.DESCRIPTION INVOICE_DESCRIPTION,
  AIDA.INVOICE_LINE_NUMBER INVOICE_LINE,
  AIDA.DISTRIBUTION_LINE_NUMBER DISTRIBUTION_LINE,
  AIDA.ACCOUNTING_DATE,
  AIA.INVOICE_CURRENCY_CODE INVOICE_CURRENCY,
  NVL(AIA.INVOICE_AMOUNT, 0) INVOICE_AMOUNT,
  AIDA.AMOUNT DISTRIBUTION_AMOUNT,
  DECODE(AIDA.POSTED_FLAG,'N','Un Accounted','S','Did Not Sweep',AIDA.POSTED_FLAG) EXCEPTION,
  AIDA.FINAL_MATCH_FLAG FINAL_MATCH,
  AIDA.LINE_TYPE_LOOKUP_CODE LINE_TYPE,
  AIDA.DIST_MATCH_TYPE DISTRIBUTION_MATCH_TYPE,
  AIDA.PA_ADDITION_FLAG
FROM
  AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
  AP_INVOICES_ALL AIA,
  POZ_SUPPLIERS_V PSV,
  FND_CURRENCIES FC,
  HR_ALL_ORGANIZATION_UNITS HAOU
WHERE
  1 = 1
  AND AIDA.INVOICE_ID = AIA.INVOICE_ID
  AND AIA.VENDOR_ID = PSV.VENDOR_ID
  AND AIDA.POSTED_FLAG IN ('N', 'S')
  AND AIA.INVOICE_CURRENCY_CODE = FC.CURRENCY_CODE
  AND AIA.ORG_ID = HAOU.ORGANIZATION_ID
ORDER BY
  AIA.INVOICE_NUM,
  AIDA.INVOICE_LINE_NUMBER