How To Correct Misclassified Accounts in General Ledger

Last Updated 10-July-2008
This affects all versions up to 11i. The sam issue exists in R12 but the scripts in the appendix will not work.

PROBLEM DESCRIPTION
-------------------
The Goal of the Note is explain the correction of Misclassified Accounts.

In the first period of the new year you find that the Opening balance
does not match the previous years closing balance. Running the period
end diagnostic Note 222628.1 shows that there are no apparent errors.
On further investigation you notice that either the retained earnings
is inflated by the amount or that the account has rolled into the new
year when you did not expect it. This is noticed typically after year end
when the Beginning Balance of Asset Accounts do not equal the closing
balance sheet value.

Or the Balance sheet account zeros out at year end because an Accrual made
in last period of the year is rolled over to Retained earnings at year end.
The account should have been a liability account. You may also see the retained
earnings is calculated incorrectly.
   
Or you notice that the wrong account type defaults in when you enter a code
combination in the form create account combinations GLXACCMB.

Note that Budgets and Encumbrance Journals do not rollover to retained earnings at
year end so these type of amounts are not affected in this way. However with
budget upload the account type will decide whether its a Dr or Cr transaction
so your budget will need adjusting if amounts were uploaded as a liability (CR)
instead of an expense(DR)for example. For more about the affects on Budget and
Encumbrance see Note 308219.1 and Note 357956.1 to help identify budgets loaded
the wrong way round because of misclassifications

CAUSE
-----
You defined an account with the wrong account type. For example you
may have defined it as an expense or revenue account type. You later
realized it should have been defined as an asset or liability account type
but you did not realise that the code combinations already created would not
be automatically corrected.

As a result, you need to change the account type for the accounts segment
values involved and you will also need to correct the code combinations.


DETECTING MISCLASSIFIED ACCOUNTS
--------------------------------
To detect misclassified accounts in 11.5.4 and higher use Note 279061.1
Diagnostic test Oracle General Ledger (GL): Misclassified Accounts Activity
For earlier versions use Note 259210.1 to help identify misclassified accounts.



SOLUTION DESCRIPTION
--------------------
This is a 5 step process:
1. Identify all the code combinations that are misclassified
2. Zeroise the balances in the code combination with the problem
3. Ask the DBA to run the script that correct the problem.
4. Reverse the Journals that zeroised the balances in step 1
   to bring them back into the accounts

Note 1: If mutliple books share a Chart of Accounts you will need to make
sure that all affected accounts in all books are zeroised

Note 2:  You may have a situation where the code combination have been corrected
but balances were not. In that case you may be asked to reset the code
combinations to the original incorrect setting before zeroising the accounts.

Note 3 If you use Average Daily Balances you must use the follwing note:
Note 150687.1 How to Correct Misclassified Account Types if Using Average Daily Balances

This article contains the steps Oracle General Ledger users must complete in
order to change the account type of a mis-classified account. You need to
complete all steps if the mis-classification has existed across fiscal years.
 
If the misclassification has not crossed fiscal years, you only need to 
complete steps 8 and 9. 

If the misclassification only affects summary templates then you can
drop and recreate the summary template after step 8 instead of journalling the
affected accounts if you wish.

NOTE:  In 11i the Account type attribute is not inherited from the Value
       to the Combinations when you run GLNSVI - Program - Inherit Segment
       Value Attributes.

       If you are using Average Daily Balances, and have a misclassified
       account type, and the account type is misclassified to be an income
       statement instead of a balance sheet, or a balance sheet instead
       of an income statement, then you need to reverse all journals in
       all periods that have been posted to that account.
      
       Once you have posted these reversals, you can then fix the account
       type, reverse the reversals, and post them.

       Note that this is not necessary if the account type was classified
       as one balance sheet type (Asset, Liability, Equity) and should have
       been another balance sheet type or the wrong type of income statement
       (Revenue or Expense) and needs changing to another income statement type.
       It is only necessary if it was classified as balance sheet and needs to be
       income statement or vice versa.

       Enter Budget values and generate budget journals use the account type to
       distinguish between Credit and debit Entries. Where the wrong account type
       is in place the wrong values can be entered. This can be corrected by
       Budget Journals or overwriting the previous amount.

       When there are consolidation or MRC books to consider and the books share the
       same value set for the accounts segment then the appropriate journals
       should be raised in both sets of books and the accounts zeroised before changing
       the account type.

To detect misclassified accounts use Note 279061.1 or Note 259210.1 to help identify
them or use steps 1 to 3 if sql access is limited.
If you know the segments you can use a script like the following one. Adjustments have
to be made for the number of segments and the segment number:

select code_combination_id segment1, segment2, segment3, segment4, segment5, account_type, enabled_flag,template_id, detail_posting_allowed_flag, start_date_active, end_date_active
from gl_code_combinations
where segment2 in &segmentlist --(list the segment numbers with commas between);

Once you are satisfied you know what combinations need changing:

1.  Reopen the last period of your prior fiscal year, if it is closed using the
    open close period form. If the account was not active before the begining of this year
    skip to step 8.

2.  Sometimes the problem code combination is fixed before you discover the unbalanced
    account. Ensure the account type is still misclassified because you need to make
    a correction journal while the combination and segment value are the same as it
    was when the original journals were raised.
    To find the account type in use on the segment:
    Navigation: Setup > financial > flexfields > Key > segments
   
    Application: Select 'Oracle General Ledger' from the list of values
    (LOV).

    Title: Select 'Accounting Flexfield' from the LOV
    Structure: Select the applicable set of books from the LOV
    Segment: Select your natural account segment from the LOV
    Query back the bad account use the qualifier tab and check the account type.
    In order to correct the balances over the year end this must remain
    Incorrect until steps 3-6 are complete.

3.  To check all the code combinations with this account value to see if they
    match the segment value account type.
     Navigate
    Setup> Account> Combinations Query on the account segment value with
    the rogue value. Find out what the account type is for each one
    Make a note or screenprint these so you can see what values are
    currently against them.
    Obtain balances as of the last period of the previous fiscal year
    by running an accounting report (General Ledger, Trial Balance,
    Account Analysis) or view the balance online by using the Account
    Inquiry form. Make sure you check all currencies.

    Note: If you use Multiple Reporting Currency (MRC) then you must
    check the reporting book as well.

4.  Use the account inquiry screen or the account analysis report to find what
    the balances were in the last period of the previous year(s).

5.  Create a journal entry that brings the misclassified account combinations
    balances to zero for the last period of your prior fiscal year.  If this
    affects multiple fiscal years, create a similar journal for the last period
    of each fiscal year. Start at the oldest and post the journals as you work
    forward so you have the YTD figure for each year.
    Often the old figures are closed and reported on so you must decide if
    this is worthwhile for balance sheet accounts. For Income statemnt (P&L)
    the process is the same but you could make the adjustment from the
    retained earnings account at the end of the last year if the amount is not
    significant.  
    The contra entry should go to a temporary account such as Suspense
    (you will reverse this batch in a later step to re-post the balances).
   
    eg.
    01.100.5555.00 is misclassified because account 5555 is expense
    instead of asset. This is causing the opening balance of 01.100.5555.00
    to be zero (the amount has been passed to the retained earnings account.

    99.100.9999.00 is a clearing account with the currect classification
    (any account type as long as its right).

    At the end of the year there is an amount of 110.00 GBP (base currency)

    To clear this the appropriate journal raised in the last period is:
         Dr 99.100.9999.00         110.00 GBP
         Cr 01.100.5555.00                         110.00 GBP



    Note: If you have multiple currencies in the account balance,
    create journal entries to zero out entries in your functional
    currency and to zero out entries for each foreign currency.
    You would set the profile 'Journals: Allow Multiple Exchange Rates'
    to Yes and enter the entered amount and accounted amount such
    that it clears the account.
   
    Note: If you use Multiple Reporting Currency (MRC) then you must
    check that the journal generated in the reporting book will zeroize
    the reporting book. If not generate a journal here to cope with the
    rounding differences.
         
6.  Post the journal entry. For MRC users this must include the Reporting
    Book Journals. Then run a summary Trial Balance for this account to
    ensure the closing balance is zero.

7.  Verify the misclassifed account's balance is zero by running an
    accounting report (General Ledger, Trial Balance, Account Analysis)
    or view the balance online by using the Account Inquiry form.
   

8.  Correct the account type of the misclassified account as follows:

    Responsibility = General Ledger Super User GUI
    GUI Navigation = Setup/Financials/Flexfields/Key/Segments
    GUI Short Form = FNDFFMIS (Key Flexfield Segments)

      a.  Navigate to the Key Flexfield Segments form.

      b.  Oracle General Ledger prevents you from changing the account
          type unless you first unfreeze all Accounting Flexfield structures
          that reference your account segment. see Note 1015950.6

          -- Uncheck the Freeze Flexfield Definition check box.
                
          -- Repeat steps a and b for each accounting flexfield structure
             which references your misclassified account type.

      c.  Navigate to Setup/Financials/Flexfields/Key/Values.

          Application: Select 'Oracle General Ledger' from the list of values
                       (LOV).

                Title: Select 'Accounting Flexfield' from the LOV
            Structure: Select the applicable set of books from the LOV
              Segment: Select your natural account segment from the LOV

            -- Enter your account value and click on the [Find] button.
              
            -- With your cursor on the value, tab over to the 'Qualifier'
               column.  This will bring up the Qualifier window and you
               will see for example:

                    - Allow Budgeting   Yes
                    - Allow Posting     Yes  
                    - Account Type      Expense

            -- In the 'Account Type' field, select Asset, Liability,
               Ownership/Stock or Revenue from LOV.

            -- Save your changes before exiting the form.

       d.  Navigate back to the Key Flexfield Segment form, refreeze
           your accounting structure(s) and click on the [Compile] button.

9.  You must ask your Database or System Administrator to
    correct the account type of all accounts referencing the misclassified
    account by correcting the ACCOUNT_TYPE column in the GL_CODE_COMBINATIONS
    table using SQL*Plus an example script is shown below (appendix A) and
    appendix B.

    If you attempt to correct it on the code combination screen you will
    receive the message 'FRM-40200 Field Protected Against Update'.

    Exception: In 10.7 Character, SQL is not needed to update the account
    type on the account combination.
    You can call back the code combinations in Navigate Setup> Account>
    Combinations and Query on the account segment value with the rogue
    account type. Simply tab through the hierarchy field and the account type
    should change.

10. Restore the misclassified account balance by reversing the journal entry
    you posted to the last period of your prior fiscal year (Step 6).
    Reverse the journal entry into the same period in which it was
    originally posted.  Posting rolls the balances forward correctly to 
    the next fiscal year and updates retained earnings accordingly. 

11. Post the reversing journal entry. If you are using MRC make sure the
    reversal is posted in the Reporting Books.

12. Review the corrected account balance by running an accounting report
    such as a Trial Balance or view the balance online by using the
    Account Inquiry form.



REFERENCES
----------
Oracle General Ledger Applications Release 10.7, Technical Reference Manual
Update, part number A47829-1, pages 8-134 through 8-135.

Oracle General Ledger Release 10SC, User's Guide, part number A21643-7,
pages 5-73 through 5-74.

Oracle General Ledger User Guide Release 11  part A58472-01 6-138
Oracle General Ledger User Guide Release 11i part A82849-01 10-17

APPENDIX A
==========

This script is for situations where only a few values are affected but many combinations.
It should be used in its entirety. Copy and paste it into a text file
called comb.sql and ftp or copy it to the server you are running the sql from
and run it there. This is for versions up to 11i only.


REM Changing account types script by Simon Goddard 02-APRIL-2002
PROMPT To run this sql you will need to know the name of your Set of Books
PROMPT the name of the 'Natural account segment'
PROMPT and the Account Type you are changing from and to.
PROMPT WARNING: make sure your finance department have taken the initial steps 1- 7
PROMPT to correct the Misclassified Accounts for all sets of  books according to the manual
PROMPT or the Note 1050920.6.

select set_of_books_id books_id, name books_name, chart_of_accounts_id chart_id
from gl_sets_of_books;

PROMPT select the chart_id of the set of books you need to change from
PROMPT results above. Ensure journals have been posted for all sets of books affected.
ACCEPT chart_id number PROMPT 'Chart_id:  '

select application_column_name segment_num, segment_name
from fnd_id_flex_segments_vl
where id_flex_num=&&chart_id
and (ID_FLEX_CODE='GL#')
and (APPLICATION_ID=101);

PROMPT This gives the column names for all segments.
PROMPT Enter the Account segment from segment_num above as SEGMENT#
ACCEPT segment_num Char PROMPT 'SEGMENT#: '

select code_combination_id ccid, account_type
from gl_code_combinations
where &&segment_num = '&&acct_value'
and chart_of_accounts_id = &&chart_id;

PROMPT This gives a list of what the account type is currently set to
PROMPT store these results as backup
PROMPT To update the combinations press enter else CTRL C
ACCEPT endbit PROMPT 'continue:'

    
Update gl_code_combinations
set account_type = '&&New_Account_Type'
where &&segment_num = '&&acct_value'
and chart_of_accounts_id = &&chart_id
and account_type= '&¤t_account_type';
commit;
select code_combination_id ccid, account_type
from gl_code_combinations
where &&segment_num = '&&acct_value'
and chart_of_accounts_id = &&chart_id;

Clear Buffer

APPENDIX B
==========
This script is for where there are many values that need changing each with
a number of combinations. These scripts can be run individually.
This is for versions up to 11i only.

Perform this on your test system first. Verify that you have a backup copy
of the gl_code_combinations table before proceeding. Do not do this while
users could be accessing the gl_code_combinations table.

1. Get the Chart Of Accounts id (COA id)
select set_of_books_id books_id, name books_name, chart_of_accounts_id chart_id
from gl_sets_of_books;

2. List the code combinations that contain the misclassified account type:

     select code_combination_id,
            account_type
     from gl_code_combinations
     where segment# = '&acct_value'
     and chart_of_accounts_id = &coa_id

     NOTE: segment# represents the segment that holds the natural account
     segment value (i.e. account segment).

3. Update the combinations:

     Update gl_code_combinations
     set account_type = '&New_Account_Type'
     where segment# = '&acct_value'
     and chart_of_accounts_id = &coa_id

     Valid Values for Account_type : A = assets
                                     E = expense
                                     L = liability
                                     O = owner's equity
                                     R = revenue

     NOTE: segment# represents the segment that holds the natural account
     segment value (i.e. account segment).

SEARCH WORDS
------------
SETUP
Average Daily Balances
ADB
balance sheet
income statement