AR Receipts Awaiting Remittance (Oracle Fusion SQL)

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

#oracle fusion sql

AR Receipts Awaiting Remittance — Oracle Fusion SQL Query

This post provides a ready-to-use SQL query for Oracle Fusion Accounts Receivable (AR) to extract receipts that are pending remittance — i.e. customer receipts that have been recorded but not yet remitted to bank or cleared.

What this query retrieves

  • Receipt number and receipt date
  • Customer (party) name and customer account / site (if applicable)
  • Receipt currency and amount (transactional and/or functional)
  • Payment method and bank account / remittance account details (if configured)
  • Receipt status (e.g. “Awaiting Remittance”, “Uncleared”, or custom statuses)
  • Remittance due date or expected remittance date (if available)
  • Deposit or remittance batch or group identifier (if your setup uses remittance batches)

Why this report is useful

  • To identify customer receipts that need to be remitted or deposited — helps AR teams avoid delays or missing cash remittance.
  • To support cash flow forecasting and bank-deposit planning — by listing all pending receipts in one report.
  • To help reconciliation during month-end — ensuring all receipts are either cleared or properly remitted before closing.
  • To provide audit visibility into uncleared receipts or potential issues (e.g. payments received but not deposited).

How to use & customize the query

  • Run the SQL against your Fusion AR schema. Adjust table aliases and joins based on your implementation (e.g. payment method tables, remittance account tables).
  • Optionally include additional fields — such as customer site, invoice applied, aging since receipt date, payment reference, or source of receipt (cash, transfer, cheque).
  • Use filters to narrow down results — e.g. by organization/ledger, receipt date range, payment method, or remittance status.
  • You can also group by customer, payment method, or remittance account to get summarized pending-remittance amounts.

#sqlquery

SELECT
 --SQL4Fusion (An Orbit Analytics Project) 
 --This SQL query retrieves a detailed report of confirmed and unreversed customer receipts
  HAOU.NAME ORGANIZATION_NAME,
  ACRA.CURRENCY_CODE,
  AL.MEANING STATUS,
  CBA.BANK_ACCOUNT_NAME,
  DECODE(IFPA.INSTRUMENT_TYPE,'BANKACCOUNT',IFPA.BANK_NAME,'CREDITCARD',IFPA.CARD_ISSUER_NAME,'DEBITCARD',IFPA.CARD_ISSUER_NAME,NULL ) BANK_NAME,
  DECODE(IFPA.INSTRUMENT_TYPE,'BANKACCOUNT',IFPA.BRANCH_NAME,NULL) BRANCH_NAME,
  APSA.DUE_DATE MATURITY_DATE,
  AL2.MEANING REMITTANCE_METHOD,
  ARM.NAME PAYMENT_METHOD,
  ACRA.RECEIPT_NUMBER,
  NVL (ACRA.AMOUNT, 0) RECEIPT_AMOUNT,
  ACRA.FACTOR_DISCOUNT_AMOUNT BANK_CHARGES,
  ACRA.ORG_ID,
  CBA.BANK_ACCOUNT_ID,
  ACRHA.NOTE_STATUS,
  ACRA.STATUS AS CASH_RECEIPTS_STATUS,
  ACRA.REVERSAL_REASON_CODE,
  APSA.FOLLOW_UP_CODE_LAST,
  APSA.CLASS,
  APSA.STATUS AS PAYMENT_SCHEDULES_STATUS,
  ACRHA.STATUS AS CASH_RECEIPT_HISTORY_STATUS,
  ACRA.REVERSAL_CATEGORY
FROM
  AR_CASH_RECEIPTS_ALL ACRA,
  AR_CASH_RECEIPT_HISTORY_ALL ACRHA, 
  AR_PAYMENT_SCHEDULES_ALL APSA,
  AR_BATCHES_ALL ABA,
  AR_RECEIPT_METHODS ARM,
  AR_RECEIPT_CLASSES ARC,
  CE_BANK_ACCT_USES_ALL CBAUA,
  CE_BANK_ACCOUNTS CBA,
  IBY_TRXN_EXTENSIONS_V ITEV,
  IBY_FNDCPT_PAYER_ASSGN_INSTR_V IFPA,
  AR_LOOKUPS AL,
  AR_LOOKUPS AL2,
  HR_ALL_ORGANIZATION_UNITS HAOU
WHERE
  1 = 1
  AND ACRHA.STATUS = 'CONFIRMED'
  AND ACRHA.CURRENT_RECORD_FLAG = 'Y'
  AND ACRA.REVERSAL_DATE IS NULL
  AND ACRA.CASH_RECEIPT_ID = ACRHA.CASH_RECEIPT_ID
  AND ACRA.CASH_RECEIPT_ID = APSA.CASH_RECEIPT_ID
  AND ACRA.SELECTED_REMITTANCE_BATCH_ID = ABA.BATCH_ID(+)
  AND ACRA.RECEIPT_METHOD_ID = ARM.RECEIPT_METHOD_ID
  AND ARM.RECEIPT_CLASS_ID = ARC.RECEIPT_CLASS_ID
  AND ACRA.REMIT_BANK_ACCT_USE_ID = CBAUA.BANK_ACCT_USE_ID(+)
  AND CBAUA.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID(+)
  AND ACRA.PAYMENT_TRXN_EXTENSION_ID = ITEV.TRXN_EXTENSION_ID(+)
  AND ITEV.INSTRUMENT_ID=IFPA.INSTRUMENT_ID(+)
  AND ITEV.INSTR_ASSIGNMENT_ID = IFPA.INSTR_ASSIGNMENT_ID(+) 
  AND AL.LOOKUP_CODE(+) = NVL(ABA.BATCH_APPLIED_STATUS,'AVAILABLE_FOR_REMITT')
  AND AL.LOOKUP_TYPE(+) = 'ARXAPRRM_BATCH_APPLIED_STATUS'
  AND ARC.REMIT_METHOD_CODE = AL2.LOOKUP_CODE(+)
  AND AL2.LOOKUP_TYPE(+) = 'REMITTANCE_METHOD'
  AND ARC.REMIT_FLAG = 'Y'  
  AND HAOU.ORGANIZATION_ID = ACRA.ORG_ID
ORDER BY
  ACRA.RECEIPT_NUMBER