Tuesday, 15 November 2016

AR Receipt and Bank Statement Reconciliation oracle apps

When the AR Receipt is Created and Applied to the Invoice based on the receipt class setup for the receipt method used in the AR transaction the Receipt status will change to Remitted or cleared.
If the Receipt class Matching setup is labelled as By Matching : In that case the Cash receipt status will change to Remitted,
The cash receipt needs to be reconciled against a bank statement to clear it off.

In order for this to happen we need to


  • make an entry for the bank statement
  • Select the receipt from the list of available receipts available for reconciliation
  • Once the bank statement is reconciled with the receipt the receipt status changes to Cleared and the bank statement line is reconciled.
Below SQL can be used to find out details of a AR cash receipt being reconciled with a bank statement
/* Formatted on 11/15/2016 11:52:30 AM (QP5 v5.114.809.3010) */
SELECT   cba.BANK_ACCOUNT_NAME,
         cba.bank_account_num,
         csh.statement_number,
         csl.STATUS,
         csl.amount,
         csl.trx_type,
         acr.receipt_number,
         arm.name
  FROM   CE_STATEMENT_HEADERS csh,
         ce_statement_lines csl,
         ce_bank_accounts cba,
         ce_statement_reconcils_all csr,
         apps.ar_cash_receipts_all acr,
         ar_receivable_applications_all ara,
         ar_receipt_methods arm,
         hr_operating_units hou,
         AR_CASH_RECEIPT_HISTORY_ALL ach
 WHERE       csh.STATEMENT_HEADER_ID = csl.STATEMENT_HEADER_ID
         AND csh.BANK_ACCOUNT_ID = cba.BANK_ACCOUNT_ID
         AND csl.STATEMENT_LINE_ID = csr.STATEMENT_LINE_ID
         AND csr.reference_id = ach.CASH_RECEIPT_HISTORY_ID
         AND csh.statement_number = :statement_number
         AND bank_account_num = :bank_account_num
         AND csr.REFERENCE_TYPE = 'RECEIPT'
         AND acr.cash_receipt_id = ara.cash_receipt_id
         AND acr.receipt_method_id = arm.receipt_method_id
         AND ara.org_id = hou.organization_id
         AND hou.name = :operating_unit
         AND receipt_number = :receipt_number
         AND acr.cash_receipt_id = ach.cash_receipt_id

No comments:

Post a Comment