AR with GL reconciliation in Oracle

AR VS GL RECONCILIATION

The "AR Reconciliation Report" shows the summary amounts of various AR reports that are used to reconcile AR. This report mainly has 3 parts
a) Period Beginning Balance
b) Period Activity in different Areas and their differences
c) Period Ending Balance

AR is reconciled when the
"Aging on the beginning of the Period" +
"The Total Activity in the Period" =
"Aging on the end of the Period"

AR Reconciliation Report

GL Date from 01-JAN-2012 to 31-JAN-2012

Sign	Particulars	Rs.
	Beginning Balance as of 31-DEC-2011	10,000.00
+	Transaction Register	500.00
-	Applied Receipts Register	2,500.00
-	Unapplied Receipts Register	3,500.00
+	Adjustments Register	20
-	Invoice Exceptions	0.00
+	Credit Memo Gain/Loss (*)	0.00
+	Rounding (*)	0.00
=	Totals for the Period	4,520.00
=	Ending Balance as of 31-JAN-2012	4,520.00

(*) These can occur only in the case of foreign currency transactions.

Oracle 11.5.10.2  Standard Reconciliation can be done at the Operating Unit level alone.

• Run the AGING - 7 BUCKETS report as of the closing of previous month for taking the opening balance for the current month.

• Run TRANSACTION REGISTER to identify the Invoices, Credit memos, Debit Memos and Supplementary Invoice details.

• Run APPLIED RECEIPT REGISTER to identify the receipts which are adjusted against invoices/Debit memo.

• Run UN APPLIED RECEIPT REGISTER to identify the receipts which are lying unapplied to any invoice/Debit memo.

• Run ADJUSTMENT REGISTER to identify the small balance adjustments those were done on invoices.

• Run INVOICE EXCEPTION REPORT to adjust the Transaction Register for any transactions that are not opens to Receivables and therefore do not show up on your Aging.

• Run the AGING - 7 BUCKETS report as of the current months closing for closing balance.

The Arithmetic process for reconciling Oracle Receivables
Open Aging
+ Transaction Register balance
+/- Adjustment Register balance
(-) Invoice Exception balance
(-) Unapplied Receipts balance
(-) Applied Receipts balance
-------------------------------------------------------------
Arithmetic total should be equal to Closing Aging
-------------------------------------------------------------

Now compare the AGING - 7 BUCKETS – GL ACCOUNT with the TRIAL BALANCE to see if the balance in the Sub Ledger as shown by the Aging report is the same as in the Trial Balance.


Technical (Table details):

Query to Identify the Code Combination ID (assuming Account code is of 5 segments separated by a period)

select code_combination_id from gl_code_combinations
where (segment1||'.'||segment2||'.'||segment3||'.'||segment4||'.'||segment5) =
'000.CC.99999.IC.99 '
Query that gives the Accounting Entries to be passed in GL
Select
ad.source_type, ara.postable, segment1||'.'||segment2||'.'||segment3||'.'||segment4||'.'||segment5
acc, sum(ad.acctd_amount_dr), sum(ad.acctd_amount_cr) from ar_distributions_all ad,
ar_receivable_applications_all ara, gl_code_combinations gc
where ara.payment_schedule_id in
(select crh.payment_schedule_id from ar_distributions_all ad, ar_receivable_applications_all crh
where ad.code_combination_id= <ID from the above query>
and crh.receivable_application_id=ad.source_id
and ad.source_type='REC' and crh.posting_control_id<0 and crh.gl_date between '&From_GL_date' and
'&To_Gl_Date' and crh.org_id=&Org_id) and gc.code_combination_id = ad.code_combination_id and
ara.receivable_application_id = ad.source_id group by ad.source_type,ara.postable,
segment1||'.'||segment2||'.'||segment3||'.'||segment4||'.'||segment5