AR Automatic Receipt Batch Management (Oracle Fusion SQL)

Enterprise Resource Planning (ERP) Oracle Account Receivables (AR-Fusion-SQL's)
1

This AR Automatic Receipt Batch Management Report query retrieves details of in-progress or pending AR receipt batches (excluding those with “COMPLETED_FORMAT” status). It includes batch name, status, batch date, receipt method, GL date, currency details and organization name.

#sqlquery

SELECT
 -- SQL4Fusion (An Orbit Analytics Project). 
 -- This query retrieves details of in-progress or pending AR receipt batches.
  HAOU.NAME ORGANIZATION_NAME,
  DECODE (
    ABA.BATCH_APPLIED_STATUS,
    'COMPLETED_CREATION',
    1,
    'COMPLETED_APPROVAL',
    2,
    'STARTED_CREATION',
    3,
    'STARTED_APPROVAL',
    4,
    'STARTED_FORMAT',
    5,
    10
  ) ORDER_STATUS,
  AL.MEANING BATCH_STATUS,
  ABA.NAME BATCH_NAME,
  ABA.BATCH_DATE,
  ABA.MEDIA_REFERENCE REFERENCE,
  ARM.NAME RECEIPT_METHOD_NAME,
  ABA.GL_DATE,
  ABA.CURRENCY_CODE,
  ABA.BATCH_APPLIED_STATUS,
  GSOB.CURRENCY_CODE FUNCTIONAL_CURRENCY,
  NVL (ARM.BR_MIN_ACCTD_AMOUNT, 0) MINIMUM_AMOUNT,
  NVL (ARM.BR_MAX_ACCTD_AMOUNT, 0) MAXIMUM_AMOUNT,
  ABA.BATCH_ID BATCH_ID,
  ABA.ORG_ID ORG_ID
FROM
  AR_BATCHES_ALL ABA,
  AR_RECEIPT_METHODS ARM,
  AR_RECEIPT_CLASSES ARC,
  GL_SETS_OF_BOOKS GSOB,
  AR_LOOKUPS AL,
  HR_ALL_ORGANIZATION_UNITS HAOU
WHERE
  1 = 1
  AND ABA.BATCH_APPLIED_STATUS != 'COMPLETED_FORMAT'
  AND ABA.RECEIPT_METHOD_ID = ARM.RECEIPT_METHOD_ID
  AND ARC.RECEIPT_CLASS_ID = ARM.RECEIPT_CLASS_ID
  AND GSOB.SET_OF_BOOKS_ID = ABA.SET_OF_BOOKS_ID
  AND ABA.BATCH_APPLIED_STATUS = AL.LOOKUP_CODE
  AND AL.LOOKUP_TYPE = 'BATCH_APPLIED_STATUS'
  AND ABA.TYPE = 'CREATION'
  AND ABA.ORG_ID=HAOU.ORGANIZATION_ID  
ORDER BY
  ABA.NAME,
  ABA.BATCH_DATE,
  ABA.MEDIA_REFERENCE,
  ARM.NAME,
  ABA.GL_DATE