AR Receipts Pending Clearing by Due Date (Oracle Fusion SQL)

Enterprise Resource Planning (ERP) Oracle Cash Management (CE-Fusion-SQL's)
1

This CE AR Receipts Pending Clearing by Due Date query displays uncleared AR receipt details with due dates for each operating unit, helping users to monitor pending cash applications and improve receivables follow-up.

#sqlquery

SELECT
  --SQL4Fusion (An Orbit Analytics Project)
  --Displays uncleared AR receipts with due dates, excluding netting accounts.
  HAOU.NAME OPERATING_UNIT,
  ACRA.RECEIPT_NUMBER ,
  NVL (ACRHA.AMOUNT, 0) AMOUNT,
  ACRHA.ACCTD_AMOUNT BASE_AMOUNT,
  ACRA.CURRENCY_CODE ,
  ACRHA.TRX_DATE TRANSACTION_DATE,
  APSA.DUE_DATE MATURITY_DATE,
  ACRA.ACTUAL_VALUE_DATE,
  ACRA.ANTICIPATED_CLEARING_DATE EFFECTIVE_DATE,
  APSA.DUE_DATE CASH_ACTIVITY_DATE,
  ACRHA.STATUS 
FROM
  AR_CASH_RECEIPTS_ALL ACRA,
  AR_CASH_RECEIPT_HISTORY_ALL ACRHA,
  AR_PAYMENT_SCHEDULES_ALL APSA,
  HR_ALL_ORGANIZATION_UNITS HAOU
WHERE
  1 = 1
  AND HAOU.ORGANIZATION_ID = ACRA.ORG_ID
  AND ACRA.CASH_RECEIPT_ID = ACRHA.CASH_RECEIPT_ID
  AND APSA.ORG_ID = ACRHA.ORG_ID
  AND APSA.CASH_RECEIPT_ID(+) = ACRHA.CASH_RECEIPT_ID
  AND ACRHA.ORG_ID = ACRA.ORG_ID
  AND ACRHA.CURRENT_RECORD_FLAG = 'Y'
  AND ACRHA.STATUS NOT IN ('REVERSED', 'APPROVED')
  AND ACRHA.REVERSAL_GL_DATE IS NULL
  AND NVL (
    ACRA.ACTUAL_VALUE_DATE,
    ACRA.ANTICIPATED_CLEARING_DATE
  ) IS NULL
  AND APSA.DUE_DATE IS NOT NULL
  AND NOT EXISTS (
    SELECT
      NULL
    FROM
      CE_BANK_ACCOUNTS CBA,
      CE_BANK_ACCT_USES CBAU
    WHERE
      CBA.BANK_ACCOUNT_ID = CBAU.BANK_ACCOUNT_ID
      AND CBAU.BANK_ACCT_USE_ID = ACRA.REMIT_BANK_ACCT_USE_ID
      AND CBA.NETTING_ACCT_FLAG = 'Y'
  )