R12 Accrual Balance Mismatch Between Accrual Reconciliation Report and GL - Troubleshooting (Doc ID 1107953.1)

R12 Accrual Balance Mismatch Between Accrual Reconciliation Report and GL - Troubleshooting (Doc ID 1107953.1)

Applies to:

Oracle Purchasing - Version 12.0.0 to 12.2 [Release 12 to 12.2]
Oracle Payables - Version 12.0.0 to 12.2 [Release 12.0 to 12.2]

Purpose

This Paper gives an introduction about the concept and design of R12 Accrual Reconciliation report and explains the R12 Reconciliation process under two different scenarios:

 

1) First scenario will provide reconciliation steps to use when accrual reconciliation is done for the first time or when the reconciliation is done considering the 'as-on-date' accrual balance

2) Second scenario will provide reconciliation steps to use when accrual reconciliation is done for a specific period considering the accrual balance in GL for that specific period

--Above mentioned scenarios are discussed for both the instance - Fresh install and Upgraded instance

Scope

Reconciliation of Accrual Reports and GL must be done monthly.  Company Accountants and Financial Departments are charged with this responsibility. This document will assist all  employees who perform the task of  accrual reconciliation with GL.  It will also assist Oracle support Engineers  and Consultants who assist company employees to achieve this goal.

Details

Functionality:

In R12, Accrual Reconciliation Reports (ARR) do not show the accrual balance for a specific date range and they only show 'as of date' balance.Hence it is recommended to compare the ARR balance with YTD balance for the accrual account shown in Accounts Inquiry screen/Ending Balance shown in the Account Analysis report.
First run of  Accrual Load run  Program
When the accrual load run program is run for the first time, it will not consider the start date given by the User. It will instead build the data in cst_ap_po_reconciliation, cst_reconciliation_summary, cst_misc_reconciliation tables using the first transaction date available in XLA that has been transferred to GL. It will however build the data to the End Date provided by the User.
Subsequent run of Accrual Load run program
For subsequent load run, the Load Program will append the recent data according to the date range given by user.
Whenever there is no accrual balance for a po_distribution_id, the accrual entries pertaining to those po_distribution_id will get removed from these tables as this new R12 design is intended to show only the po_distribution_ids that have accrual balances.
The Accrual Reconciliation Summary report has the accrual balances related to all the accrual codes - AP PO, AP NO PO and Miscellaneous transactions. The other two reports AP PO Reconciliation and Misc.accrual reconciliation only show the details pertaining to AP PO transactions or Misc transactions respectively.
Total accrual balance shown in Accrual Reconciliation Summary report should be compared with Account Analysis Report in GL for the source Cost Management with category Receiving (assuming that period end accruals have a different je category) + Source Cost Management with category Inventory and WIP + Source Payables with category Purchase Invoices.

Prerequisites and basic elements in R12 reconciliation

Before we go into the exact Reconciliation steps there are some general comments that we need to make.
1. Accrual reconciliation reports in R12 will only consider data from SLA that are transferred to GL. Manual Journals and Adjustments made in GL will not be considered.
2. Accrual Reconciliation Reports in R12 are based on po_distribution_ids. It will only show Accrual Balances of po_distribution_ids. If a po_distribution_id is fully received and invoiced it will not appear on the Report
3. The Ending balance of the Account Analysis Report is what is used to reconcile the balance of the Reconciliation summary Report.
   The only valid sources from Account Analysis Report considered during Accrual Reconciliation  are:
   i) Source Cost Management and Category Receiving
   ii) Source Cost Management and Category Inventory
   iii) Source Cost Management and Category WIP
   iv) Source Payables and Category Purchase Invoices
The following sources if included in the Account Analysis Opening Balances should be eliminated:
   i) Source Cost Management and Category Accrual
   ii) Source Purchasing
   iii) Source MANUAL/SPREADSHEET ENTRIES/Write Offs
4. If  a Company has Multiple Accrual Accounts, in order to avoid confusion one should  do the reconciliation for one accrual account at a time
5. All the accrual accounts defined in a particular operating unit should be listed in the 'Select Accrual Accounts' form in Purchasing  > Accounting > Accrual Write offs so that Accrual Reconciliation Reports will show the accrual balance for those accrual accounts
6. Accounts Requirements:
In Release 12 each of the following Accounts used in Accrual reconciliation process must be separate.
i) Inventory AP Accrual Account
Navigate to:  Inventory Responsibility Setup/Organization/Parameters/Other Accounts
ii) Expense AP Accrual Account
Navigate to:  Purchasing Responsibility Setup/Organization/Purchasing Options/Receipt Accounting
iii) Receiving Inventory Account
Navigate to:  Purchasing Responsibility: Setup Organization/Receiving Options/Select the Organization/Receiving Inventory Account
iv) Invoice Price Variance Account
Navigate to:  Inventory Responsibility Setup/Organization/Parameters/Other Accounts
v) Encumbrance account
Navigate to:  Inventory Responsibility Setup/Organization/Parameters/Other Accounts
7. It is highly recommended that  je_category used in SLA for Period end accruals should be 'Accrual' and je_category used in SLA for ON LINE ACCRUALS should be 'Receiving'
8. It is also highly recommended that Customers should have the latest version of CSTACCLB.pls file .    This is the executable for the Load Program and there are many new fixes always included in the latest file.
9. Find out whether the instance in which reconciliation is being carried out is a fresh R12 install or an R12 upgraded instance.
How to determine whether the instance is a fresh install or an upgrade?
Run the query given below to find out whether the instance is a fresh install or an upgraded instance.

SELECT /*+ parallel(gl_period_statuses) */
set_of_books_id
,min(start_date)
,max(end_date)
FROM gl_period_statuses
WHERE migration_status_code = 'U'
AND set_of_books_id = &ledger_id
GROUP BY set_of_books_id;

-- If this query does not return any rows, it is a fresh R12 install.   We will divide the Troubleshooting discussion under 2 different scenarios.
1) First scenario will provide reconciliation steps to use when accrual reconciliation is done for the first time or when the reconciliation is done considering the 'as-on-date' accrual balance

2) Second scenario will provide reconciliation steps to use when accrual reconciliation is done for a specific period considering the accrual balance in GL for that specific period

Reconciliation Steps to use when accrual reconciliation is done for the first time or when the reconciliation is done considering the 'as-on-date' accrual balance

1.Ensure all Receiving transactions for the current period are completed and the Create Accounting -Receiving  with Transfer to GL and Post to GL completed.

2.Ensure all AP Transactions for the current Period are completed and that Create Accounting from Invoicing with Transfer to GL and Post to GL completed
3.Verify whether Accrual load run program is run for the period which is given as the ending period in Account Analysis report using the following query.

Input Parameters:

i)ledger_id
ii)period which is given as ending period in Account Analysis report

SELECT *
FROM cst_reconciliation_build
WHERE operating_unit_id =
&operating_unit_id
AND to_date >=
(SELECT end_date
FROM gl_period_statuses
WHERE application_id = 101
AND ledger_id = &ledger_id
AND period_name = '&ending_period')

Inference from script output:

a) If this query returns records, it can be confirmed that load run has been run for the to-period already. If so, Verify the 'to_date' returned by this query. If it is equal to the last date of ending period given in Account Analysis report, you can proceed with the accrual reconciliation process. If the 'to_date' returned by this query is greater than the last date of ending period given in Account Analysis report, perform the action plan given in step 4.

b) If this query does not return any rows, run the accrual load run program with the end date as last date of to-period given in Account Analysis report and run the same query given in step 3 for confirmation and then proceed with reconciliation process.

4. If the 'to_date' returned by the query given in step 3 is greater than the last date of ending period given in Account Analysis report, perform the following steps and run the accrual load run program again with end date as the last date of the ending period and proceed with the reconciliation process.

 

a) DELETE
FROM cst_write_offs
WHERE operating_unit_id =
&&p_operating_unit
AND EXISTS
(SELECT 1
FROM po_accrual_write_offs_all pawo
WHERE pawo.write_off_id=cst_write_offs.write_off_id
);

b) DELETE
FROM cst_write_off_details
WHERE operating_unit_id =
&&p_operating_unit
AND EXISTS
(SELECT 1
FROM po_accrual_write_offs_all pawo
WHERE pawo.write_off_id=cst_write_off_details.write_off_id
);

-- The above mentioned scripts will not delete the records from write off tables if the instance is a fresh R12 installation and there is also no need to delete the write off records from these tables if it a fresh R12 install.

c) DELETE FROM CST_RECONCILIATION_BUILD
WHERE operating_unit_id = &Operating_unit_id;

d) DELETE
FROM CST_RECONCILIATION_SUMMARY
WHERE operating_unit_id =
&&p_operating_unit;

e) DELETE
FROM cst_misc_reconciliation
WHERE operating_unit_id =
&&p_operating_unit;

f) DELETE
FROM CST_AP_PO_RECONCILIATION
WHERE operating_unit_id =
&&p_operating_unit;

COMMIT;

Reconciliation Process

5. Submit the Account Analysis Report from General Ledger responsibility giving the accrual account, ending period and balance type as Actual and note down the ending balance. This ending balance should not be compared directly with the accrual balance shown in Accrual Reconciliation Report if manual journals or any Spreadsheet entries exist in GL for the accrual account.

For more accurate reconciliation results,Development in Bug14826281 recommends that customers should use the Subledger Account Analysis report in reconciliation rather than Account analysis Report from GL


6. Since Accrual reconciliation report shows accrual balances for transactions pertaining to the sources Cost Management and Payables, accrual balance from Account analysis report should be considered only for these two sources. Hence run the script given below to get the accrual balance.

Input Parameters:

i)accrual account
ii)ledger_id

SELECT period_name,(SUM(NVL(accounted_dr,0))- SUM(NVL(accounted_cr,0)))
FROM gl_je_lines
WHERE code_combination_id IN
(SELECT code_combination_id
FROM gl_code_combinations_kfv
WHERE concatenated_segments = '&accrual_account'
)
AND ledger_id =
&ledger_id
AND je_header_id IN
(SELECT je_header_id
FROM gl_je_headers
WHERE je_source IN ('Cost Management','Payables')
AND je_category IN ('Receiving','Inventory','Purchase Invoices')
AND status ='P'
AND actual_flag = 'A'