CE AR Receipts Pending Payment Dates (Oracle Fusion SQL)

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

This CE AR Receipts Pending Payment Dates query displays AR receipts without due or clearing dates for operating units, helping users monitor and manage pending receivables.

#sqlquery

SELECT
  --SQL4Fusion (An Orbit Analytics Project)
  --Shows AR receipts missing due dates or clearing dates, tracking pending receivables.
  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,
  ACRHA.TRX_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.CASH_RECEIPT_ID(+) = ACRHA.CASH_RECEIPT_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 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'
  )