R12: Scripts to Check Year-End Carry Forward of Actual Balances and Retained Earnings

(Doc ID 1365597.1)

Last updated on SEPTEMBER 10, 2016

Applies to:

Oracle General Ledger - Version 12.0 and later
Information in this document applies to any platform.
***Checked for relevance on 10-Sept-2016***

Goal

Scripts to check the carryforward of the Account Actual Balances from the last period of one financial year into the first period of next financial year.

This note is not applicable for Translated balances.

This note is intended to provide SQL scripts to check the accuracy of the carryforward of the closing retained earnings to the opening retained earnings accounts in the new year. The complexity of the checking increases because the income and expense account balances become zero in the across the year scenario and most of the scripts used to verify the accuracy of the opening balances are rendered useless.

 

The following is the summary of what can be done in the across the year scenario:

1. Run script one to ensure that the balances of the code combinations of the type Assets and Liabilities are carried forward correctly. These types of accounts must have the opening balances equal to the closing balances of previous year.

2. Run script two to ensure that in the new year there are no opening balances for any Income and Expense accounts. These balances are automatically moved (without journalling) to the Retained Earnings Account defined for the Ledger in Accounting Setup Manager.

3. Script three will provide the figure of profit or loss for all the combinations of currencies and balancing segments.

4. Script four is the most important of all the scripts, this should give the change in the retained earnings accounts for all the combinations of currencies and balancing segments.

So you can compare the output script three with the results of script four. The idea here is that the change in the retained earnings code combinations (given by script four) should match line-by-line with the profit or loss for the year (output of script three).

 

Software Requirements/Prerequisites

These are SQL scripts. When uploading the results of the scripts to a Service Request, please use Spreadsheet format. Also provide the scripts used for reviewing the parameters as well.

 

Configuring the Script

You need to have access to the GL schema to run the scripts successfully.

 

Running the Script

The scripts request a few input parameters. These are the standard parameters which you will be asked for when the scripts are run, they include : Ledger ID, First Period of the new year, Last Period of the prior year.

Note: the scripts assume that the balancing segment (company) is number 1. If this is not your case you need to change the word "segment1" by the correct segment number, for example "segment3".

Caution

This script is provided for educational purposes only and not supported by Oracle Support Services. It has been tested internally, however, and works as documented. We do not guarantee that it will work for you, so be sure to test it in your environment before relying on it.

Proofread this script before using it! Due to the differences in the way text editors, e-mail packages and operating systems handle text formatting (spaces, tabs and carriage returns), this script may not be in an executable state when you first receive it. Check over the script to ensure that errors of this type are corrected.

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms