Does anyone have an idea how to query to find Receipts in AR which have not been or are partially Reconciled in Cash Management(R12)?
Once the receipt is created its status get s changed from Approved-> confirmed-> Remitted (once it gets remitted to bank for receiving payment)-> cleared (once payment received)
AR_CASH_RECEIPT_HISTORY_ALL: The status field for that row tells you which step the receipt has reached Possible statuses are Approved, Confirmed, Remitted, Cleared, and Reversed
Even if the receipt status is cleared doesn’t mean it’s been reconciled in cash management
from ce_statement_lines a,
where a.BANK_TRX_NUMBER = b.RECEIPT_NUMBER
and b.RECEIPT_NUMBER = 'Receipt no you want to check'
If you find any statement no against the receipt no that means it has been reconciled in cash management. if not then it still waiting for reconciliation.
From front end you can observe the same:
Receivables -> Receipts-> Receipt-> Receipt history -> Status History