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  

AR Receipt application - table changes

When a cash receipt is created , sooner or later the Cash receipt will be applied to an open AR transaction in the system.

In this post we will discuss the entries in the table  and how are the records affected during this process.

1. Receipt Creation : When cash receipt is created entry will be made in the system in the tables
   AR_CASH_RECEIPTS_ALL --> storing details of the Cash receipt
   AR_CASH_RECEIPT_HISTORY_AL --> Detail of the changes made to the receipt which need                tracking Eg : Creation , reversal etc
   AR_RECEIVABLE_APPLICATIONS_ALL --> Detail of the RECEIPT creation record with STATUS = UNAPP , Amount_applied as the Receipt amount

2. Receipt Application : When the Cashe Receipt is applied to an AR transaction the open amount of         the Receipt and the Ar invoice will decrease. Entry is made into the table AR_RECEIVABLE_APPLICATIONS_ALL with 2 records .one with Status = UNAPP and a negative amount of the amount applied debiting the acutal account affected when the receipt was created , since the Open balance in the account decreases.
Another entry is made with status = APP , Amount_applied is the actual amount applied to the receipt and the column applied_customer_trx_id , applied_payment_schedule_id all get populated with the details of the AR Invoice which has been impacted.  In the relevant columns the split of LINE and TAX amount is also visible.


Eg : Receipt crearted for amount : 100

entry is made in table AR_RECEIVABLE_APPLICATIONS_ALL with amount : 100 , STAUS = UNAPP
60 is applied to a transaction

2 entries are made in the table : AR_RECEIVABLE_APPLICATIONS_ALL

1. Amount : -60 , status = UNAPP debiting the actual account
2. Amount : 60 , status = APP , Crediting the receivale Account and also storing the details of the affected invoice in the columns applied_customer_trx_id , applied_payment_schedule_id.

Depending on the Receipt method used and the Receipt method Setup  , If the receipt is fully applied the status will be remitted or cleared

If Receipt is using a receipt method with Setup : By Matching and receipt is fully applied the status will be remitted . Once the receipt is matched with Bank statement the status will be cleared.