AR Receipts – With Cash Clearing Date Available (Oracle Fusion SQL)

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

This CE AR Receipts – With Cash Clearing Date Available query displays receipt details with cash clearing dates for each operating unit, helping users track cleared AR receipts and support cash flow analysis.

#sqlquery

SELECT
  --SQL4Fusion (An Orbit Analytics Project)
  --Fetches AR receipts with available cash clearing 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 ACTUAL_VALUE_DATE,
  ACRA.ANTICIPATED_CLEARING_DATE EFFECTIVE_DATE,
    NVL (
      ACRA.ACTUAL_VALUE_DATE,
      ACRA.ANTICIPATED_CLEARING_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 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'
  )