R12: Scripts to Check Year-End Carry Forward of Actual Balances and Retained Earnings (Doc ID 1365597.1)

Last updated on MARCH 22, 2024

Applies to:

Goal

Scripts to check the carry forward 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 carry forward 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" to the correct segment number.

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