AR Receipt and Bank Statement Reconciliation

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

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  

Journal Line Bank Statement Reconciliation

Many times business wants to reconcile a Journal with a bank statement line but it might happen that the Journal Line is not available in the list of Reconcilable lines  , Below are some of the simple checks you can perform to find the reason why the Journal is not available

• Is the Journal Line Posted
• Is the Journal line account same as the Cash Account on the bank account setup
• If you have entered any search criteria in the Find screen which might not be matching the Journal you are expecting to reconcile
• The Journal has not been already reconciled with a bank statement . In Order to check if a Journal line is reconciled against a bank statement  , use the below SQL

          SELECT Count(1)

              FROM ce_statement_lines sl, 

                          ce_statement_reconcils_all sr, 

                          gl_je_headers jh, 

                          gl_je_lines jl ,

                          ce_statement_lines sh

            WHERE 1=1

                 AND sr.statement_line_id=sl.statement_line_id

                 AND sr.je_header_id=jh.je_header_id

                 AND jh.je_header_id=jl.je_header_id

                 AND sr.reference_id=jl.je_line_num

                AND sr.reference_type='JE_LINE'

                AND sl.statement_header_id = sh.statement_header_id

                AND gjh.name = :journal_name

Hope this Helps

Thanks