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
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;
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 completed3.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')
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:
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;
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.
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'
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'
No comments:
Post a Comment
Please review my topic and update your comments
Note: only a member of this blog may post a comment.