AP Batch Control Details (Oracle Fusion SQL)

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

This AP Batch Control Report query reviews invoice batches and can be run to identify invoices and resolve batch variances.

#sqlquery

SELECT
  -- SQL4Fusion (An Orbit Analytics Project) 
  --  This query retrieves invoice batches and  variances.
  ABA.BATCH_NAME,
  ABA.BATCH_DATE,
  PU.USERNAME USER_NAME,
  HAOU.NAME ORGANIZATION_NAME,
  COUNT (*) ACTUAL_INVOICE_COUNT,
  NVL (
    ABA.INVOICE_CURRENCY_CODE,
    ASPA.BASE_CURRENCY_CODE
  ) CURRENCY,
  SUM (AIA.INVOICE_AMOUNT) AS ACTUAL_INVOICE_TOTAL,
  NVL (ABA.CONTROL_INVOICE_COUNT, 0) - COUNT (*) AS VARIANCE_INVOICE_COUNT,
  NVL (ABA.CONTROL_INVOICE_TOTAL, 0) - SUM (AIA.INVOICE_AMOUNT) AS VARIANCE_INVOICE_TOTAL,
  NVL (ABA.CONTROL_INVOICE_COUNT, 0) CONTROL_INVOICE_COUNT,
  NVL (ABA.CONTROL_INVOICE_TOTAL, 0) CONTROL_INVOICE_TOTAL,
  AIA.ORG_ID,
  ABA.BATCH_ID
FROM
  AP_INVOICES_ALL AIA,
  AP_BATCHES_ALL ABA,
  PER_USERS PU,
  HR_ALL_ORGANIZATION_UNITS HAOU,
  AP_SYSTEM_PARAMETERS_ALL ASPA
WHERE
  1 = 1
  AND ABA.BATCH_ID = AIA.BATCH_ID
  AND ABA.LAST_UPDATED_BY = PU.USERNAME
  AND HAOU.ORGANIZATION_ID = AIA.ORG_ID
  AND ASPA.ORG_ID = HAOU.ORGANIZATION_ID
GROUP BY
  ABA.BATCH_NAME,
  ABA.BATCH_ID,
  ABA.BATCH_DATE,
  PU.USERNAME,
  ABA.CONTROL_INVOICE_COUNT,
  ABA.CONTROL_INVOICE_TOTAL,
  AIA.ORG_ID,
  HAOU.NAME,
  NVL (
    ABA.INVOICE_CURRENCY_CODE,
    ASPA.BASE_CURRENCY_CODE
  )