Process of reconciling AR to GL at period end

Introduction

Month end reconciliation is a significant activity and one that causes a lot of stress for the users in financial accounting and costing department. This is the time when the impact of all the wrong transactions entered in the month comes back to haunt the finance department. Invariably the user will find that the period figures matching with sub ledger figures is more of an exception than a rule!!

This document explains the reconciliation process that I have followed to reconcile AR to GL. This document was prepared in 11i. You may need to add some more information in R12 since SLA Tables are also involved in reconciliation

Objective of the document.

To explain the steps involved in reconciling AR to GL.

Step 1: Check the AR accounting setup

The reconciliation process begins even before a single transaction has been entered in Oracle. A tight set up with very few Debtors Control Account will go a long way to simplify the reconciliation process by reducing the reconciliation issues. Another key set up is that of Auto accounting in AR.Since you have already gone live and now has to close the month, the first step is to check your auto accounting set up in AR.

The following queries will help you check the setup. These queries are applicable to the client setup and will need to be modified based on your Auto accounting setup.

--QUERY 1--

To obtain data from receivables tables first we have to run the following query--giving the Organization_id of the OU--as the parameter (81 in the script below). This is because Access control of--customer data is at the OU level in AR.

begin

fnd_client_info.set_org_context('81');

end;

--QUERY 2--

This query will give you the table from which the system will pick the-- receivable account for auto accounting

Select table_name from RA_ACCOUNT_DEFAULT_SEGMENTS where gl_default_id in(Select gl_default_id from RA_ACCOUNT_DEFAULTS where type='REC') and segment='SEGMENT3'

--QUERY 3--

If the output of Query2 is RA_CUST_TRX_TYPES then run the following query--to get the receivables GL account for reconciliation

Select distinct (segment3) from gl_code_combinations where code_combination_id in(Select gl_id_rec from RA_CUST_TRX_TYPES)

--QUERY 4--

Find out if there are transaction types which have--'UPDATE CUSTOMER BALANCES FLAG' enabled and 'POST TO GL FLAG'-- not enabled. These transactions will cause mismatch between--Sub ledger and GL. If there are transactions which have used these Transaction types, you may have to pass JV to update the Debtors account in GL to ensure reconciliation. These transaction types are normally used during OB intake and after OB is signed off these transaction types should be end dated.

Select Name from RA_CUST_TRX_TYPES where post_to_Gl='N' and accounting_affect_flag='Y'

--QUERY 5--

To check if there are any transaction types which will update GL but will not update customer balances. To rectify these, JV need to be passed in GL to reverse the accounting entries and a new document need to be generated using a correct Transaction type. Please ensure to end date these transaction types.

select Name from RA_CUST_TRX_TYPES where post_to_Gl='Y' and accounting_affect_flag='N'

Step 2: Reconciliation Process

Create an FSG report for reconciliation purposes. Though this step is not essential for the reconciliation purposes, this can be very useful for providing a concise picture of the reconciliation. I am giving below the specs of the report that I generated.

Name: RECO ROW SET

Description: RECONCILIATION

ROW SET

Rows----------10

Name: Description: AR Reconciliation20

Name: Description: Company 01----------------------------------------------------------------------+ 01..231001.. 01..231002.. T.T.E.T.T No Net30

Name: Description: Company 02---- ------------------------------------------------- ---------------+ 02..231001.. 02..231002.. T.T.E.T.T No Net40

Name: Description: Company 03---- ------------------------------------------------- ---------------+ 03..231001.. 03..231002.. T.T.E.T.T No Net50

Name: Description: Company 04---- ------------------------------------------------- ---------------+ 04..231001.. 04..231002.. T.T.E.T.T No Net60

Name: Description: Company 05-----------------------------------------------------------------------+ 05..231001.. 05..231002.. T.T.E.T.T No Net70

Name: Description: Company 06---- ------------------------------------------------- ---------------+ 06..231001.. 06..231002.. T.T.E.T.T No Net80

Name: Description: Company 07---- ------------------------------------------------- ---------------+ 07..231001.. 07..231002.. T.T.E.T.T No Net90

Name: Description: Company 08---- ------------------------------------------------- ---------------+ 08..231001.. 08..231002.. T.T.E.T.T No Net100

Name: Description: Company 09---- ------------------------------------------------- ---------------+ 09..231001.. 09..231002.. T.T.E.T.T No Net110

Name: Description: Total Receivables Calculations ------- Row Sequence ---------Sequence Operator Constant Low High Row Name-------------------- ---------- ------------------------------ --------1 + 20 100

Name: RECO COLUMN SET

Description: RECONCILIATION COLUMN SET

Columns----------

1 Name: Description:Amount Type: YTD-Actual Period Offset: -1 Override Value: Display: Yes

2 Name: Description:Amount Type: PTD-Actual Period Offset: Override Value: Display: Yes

3 Name: Description:Amount Type: YTD-Actual Period Offset: Override Value: Display: Yes

This report will give the closing balance of the previous month in column no. 1, the period balances of the current period in column no. 2 and the closing balance of the current month in column no. 3

Run the 4 Bucket Analysis Report for the current month as of the last day of the current month.

Run the 4 Bucket analysis report for the previous month.

Value from step B minus the value from step C is the net customer transactions for the current month.

Run the ‘Reco Report’ in GL for the current period to obtain the period balances in GL

The values obtained in steps D and E should tally.

If the above values do not tally, proceed to the next step.

Run the ‘Unapplied Receipts Register’ in AR. This report will give the ‘On-Account’, ‘Unapplied’ and ‘Unidentified’ receipts from AR.

Subtract this value from the value obtained in step D.

This figure should match with GL. If not proceed further.

Run the ‘Account analysis (132 Char) report in GL. Search for any entries with source other than ‘Receivables’. If any debit entries are found , then subtract these values from GL Balances. On the other hand if any credit entries are found, then add these values to the GL Balances. This figure should match with the figure obtained in step G.

If these figures do not tally, proceed further.

Analyse the transaction register for the current period (run giving the low and high GL date as parameters) to see if there are any transactions that are not posted to GL. These transactions would have used the transaction types identified in Query 4 above.

There is a very high chance the now your receivables balance will tally with GL Balances.

If they do not tally, then you have to proceed to analyse the issue in depth.

I have found the following queries useful in this analysis. Also you need to run all the reports mentioned above in detail mode and compare the entries transaction by transaction using Excel.

--QUERY 6--

To check if there are transactions which are created with receivables account other than those identified in QUERY 3.These are wrong accounts selected for debtors account and will cause mismatch between AR and GL. This query will be different for your implementation.

select distinct(segment3) from gl_code_combinations where code_combination_id in(select code_combination_id from ra_cust_trx_line_gl_dist where account_class='REC') and segment3 not in (select distinct(segment3) from gl_code_combinations where code_combination_id in(select gl_id_rec from RA_CUST_TRX_TYPES))

--QUERY 7--

To identify those transactions which have used wrong debtors account identified in QUERY 6 Select rct.trx_number, gcc.segment3 from ra_customer_trx_all rct,ra_cust_trx_line_gl_dist gd, gl_code_combinations gccwhere rct.CUSTOMER_TRX_ID=gd.CUSTOMER_TRX_ID and gd.CODE_COMBINATION_ID=gcc.CODE_COMBINATION_ID and gd.account_class ='REC'and gcc.segment3 in (select distinct(segment3) from gl_code_combinations where code_combination_id in(select code_combination_id from ra_cust_trx_line_gl_dist)and segment3 not in (select distinct(segment3) from gl_code_combinations where code_combination_id in(select gl_id_rec from RA_CUST_TRX_TYPES)))

--QUERY 8--

To check if there is any difference between transaction month and GL_Month for those transactions being posted to GL. These transactions will not cause any mismatch between Sub ledger and GL in Oracle but can cause quite a few surprises in the organization!!!. You don’t need to run this for reconciliation.

select rct.trx_number,to_char(to_date(rct.trx_date,'DD-MON-YYYY'),'MON') TRXMonth,to_char(to_date(gd.gl_date,'DD-MON-YYYY'),'MON') GLMonth,gd.gl_date-rct.trx_date Difffromra_customer_trx_all rct,ra_cust_trx_line_gl_dist gdwhere rct.CUSTOMER_TRX_ID=gd.CUSTOMER_TRX_IDand trunc(gd.gl_date)-trunc(rct.trx_date)<>0and gd.ACCOUNT_CLASS='REC'