1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!
saifkhan76

How to correct misclassified accounts in general ledger 2013-04-19

How to correct misclassified accounts in general ledger

  1. saifkhan76
    How To Correct Misclassified Accounts in General Ledger
    PROBLEM DESCRIPTION

    The goal of this note is explain the correction of Misclassified Accounts.

    An account is misclassified if the account type of the segment value is different from the account type on a code combination that uses that value. It occurs when the account was initially created with the wrong account type and had code combinations generated from it. Then the account type is subsequently changed on the account value screen to a different account type. This change does not change the account type of the code combination it keeps its original type.

    The process of changing the account type of a code combination is complicated because you can not do it through the form. This is deliberate as it prevents the account type of a code combination being changed after it is used without users making decisions on how to correct the accounting of journals posted in earlier years. At year end balance sheet balances will be rolled forward and P&L balances will be rolled up into retained earnings. Manual intervention is required to correct the year end balances.


    There are 5 account types available and they form two groups:
    Profit and loss accounts or income statement accounts:

    Expense
    Revenue

    Balance sheet accounts:

    Asset
    Liability
    Owners equity

    The major difference between these two groups is that at year end the Income statement accounts are rolled up into the retained earnings account when you open the first period. Balance sheet accounts are rolled forward to the same code combination when you open the first period of a new year.

    There are two type of misclassification
    a. Simple
    b. Complex

    The simple form is between account types in the same group. At year end the behaviour of the rollforward is unchanged.

    Example
    Account 1000 is setup as Expense
    Code combination 01.1000.1000 is created
    Account 1000 is changed to account type Revenue. This does not change 01.1000.1000 but any new code combinations will be revenue.

    The complex form is where the account type of one is in a different group to the other. For example the account type is an asset in the account value 1100 and expense in the code combination 01.5000.1000. At year end the behaviour of the rollforward is different between these two account types so the code combination does not behave correctly for its position in the statement of accounts.



    Example
    Acount 1100 is setup as Expense (the default)
    Code combination 01.5000.1000 is created
    Account value 2000 is changed to Asset. This does not change 01.5000.1000 but any new code combinations will be Asset.

    SYMPTOMS

    In Budgets

    You are unlikely to see the Simple form causing symptoms in many circumstances but you will find that
    budget uploads become debit Or Credits depending on whether they are account type Expense, Asset
    or owners equity for Dr's or Revenue and Liability for Credits.
    << Note.357956.1>> 'Misclassified Accounts Caused Budget Amounts to be Loaded Incorrectly' to help
    identify budgets loaded the wrong way round because of misclassifications. You also see this in the complex form if for example the account type started as expense and was changed to liability. The budget would be a Dr instead of a Cr.

    Standard Reports

    In standard reports like the Trial balance or account analysis in the first period of the new
    year you find that the Opening balance is zero instead of matching the previous years closing
    balance. 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. You will not get this in the simple form only the complex form.

    FSG Reports

    The Balance sheet account YTD amount start at zeros in the new year because the balance as at the last
    period of the year is rolled over to Retained earnings at year end. This only happens for the complex case.

    Create Account Combination Form


    In Setup > Account > Combinations you can see the wrong account type defaults in when you enter
    a code combination in the form GLXACCMB - Create Account Combinations. You can see this in either case.

    DETECTING MISCLASSIFIED ACCOUNTS

    Detection of misclassified accounts depends on a change being made to the account segment value
    after code combinations have been generated causing the account type of one to vary from the
    account type of the other. If you have not yet changed th account type on the account value the
    fact that the code combination is of the wrong account type will not show up with any of these
    methods. Then it is a case of spotting that the code combination is not behaving properly (see
    symptoms)

    IMPORTANT NOTE
    This article is a technical description of how to correct misclassified accounts. It is envisaged
    that the misclassification will be discovered shortly after year end and that accounts have not
    yet been published. For the complex type problem if accounts have been published or the problem
    goes back over several years then consideration must be given to whether the amounts are
    material. You may need advice from your accountants and auditors in that case as for material
    amounts it would be normal to report this as a prior year adjustment in the income statement.

    IMPACT OF NOT RESOLVING MISCLASSIFIED ACCOUNT
    The first year where there is a misclassified code combination there is no impact. In the First
    period of the second year the balance will roll forward incorrectly. It will either remain as the
    bought forward balance for Owners equity, Asset or Liability account or it will be rolled forward
    as part of the retained earnings balance for the new year for Expense and Revenue accounts. As yet
    the reports at year end will still be correct because the accounts are reported according to where they
    appear in the chart of accounts not by the account type.

    In the first period of the second year the Year To Date (YTD) figures reported for the account
    will be incorrect in monthly management reports. For Income statement accounts this means that
    the expenditure or income against budget will be overstated or in the balance sheet the asset or
    liability will be understated the difference being in retained earning.

    In the last period of the second year the published accounts will have incorrectly stated the
    retained earnings. This will be higher or lower depending on the type of account and whether its
    a debit or credit. This time although the code combination will still appear in the right part of
    the accounts the value will be under or over stated. Income statement accounts classified as
    balance sheet will be showing last years balance included in this year while balance sheet accounts will be understated by last years year end balance.

    In the third years opening balance you will have two years of miscalculation. If you have gone
    past the second years reporting and published the accounts without fixing the code combination
    the accounts will contain errors. If you think these errors are material in size the accounting
    treatment of this error must be discussed with your accountants or auditors to satisfy the rules
    governing the publication of company financial statements. If the misclass is to an income
    statement account type the difference will be between the retained earnings and the balance sheet
    ie an asset or liability is understated the difference going to retained earning which is in
    Owners Equity. For a balance sheet account type the difference is that the revenue or expense
    will be overstated.

    Technically not fixing all the years that the code combination existed would only be a problem if
    you had to later do a fix to the same account combination and that fix corrected all the
    balances for previous years. This might lead you to think the problem had been incorrectly
    resolved. Finally translation looks back at all transactions since the code combination was
    raised so it will be affected by changes.

    Solution

    SOLUTION DESCRIPTION
    For the simple situations where the account type is within the same group then go to the sql
    solutions in the appendix. You do not need to make any journal adjustments for the case where
    you are changing an expense to a revenue or revenue to expense or asset to liability or asset to
    owners equity or liability to asset or liablity to owners equity or owners equity to liability or
    owners equity to asset. You also do not need journals if there are no balances in previous years.

    For complex situations you will need to clear the balance from the previous year then re-enter it
    once the correction is done. If more than one year is involved you need to consider whether the
    amounts are material and need to be reported in the year end report otherwise this process will
    suffice.

    This is a 4 step process:

    1. Identify all the code combinations that are misclassified
    2. Zeroise the balances in the code combinations with the problem as at the end of last year in
    all currencies and all Ledgers that share the chart of accounts. If you dont do it in the end
    of last year it will not effect this years balances and so will not show in the reports correctly at year end.
    3. Ask the DBA to run the script that corrects the problem code combinations.
    4. Reverse the Journals that zeroised the balances in step 2 to bring them back into the accounts
    for this year

    EXAMPLE 1

    Changing P&L to Balance sheet.
    In 2007 the account 01.5000.1000 is setup as an expense account. Its location in the accounts
    hierarchy means its reported as an asset in the financial statement. At year end there is
    $20,000.00 in this account. This amount is passed to retained earnings 01.8900.1000 in the first
    period of the year 2008.

    Account Dec-07 Jan-08
    dr Cr Dr Cr
    01.5000.1000 20000 0
    01.8900.1000 0 20000

    In 2008 a dr of 30000 is journaled to this account if its not corrected the position relating to
    this account in the first period 2009 is

    Account Dec-08 YTD Jan -09 YTD
    dr Cr Dr Cr
    01.5000.1000 30000 0
    01.8900.1000 20000 50000

    so if you only adjust the last years accounts the balance sheet accounts opening balance is
    incorrect by 20000, we need to adjust both years. The fix would be
    step 1 journal to zero both year ends. That is open Dec 2007 and Dec 2008 and zero both accounts
    to another balance sheet account say suspense 01.2999.1000
    2007

    Account Dec-07 Jan-08
    dr Cr Dr Cr
    01.5000.1000 20000 20000 0
    01.8900.1000 0 20000 20000
    01.2999.1000 20000 20000

    In 2008
    Account Dec-08 YTD Jan -09 YTD
    dr Cr Dr Cr
    01.5000.1000 30000 30000 0
    01.8900.1000 20000 20000 50000 50000
    01.2999.1000 50000 0

    Now if we correct the code combination step 2 and reverse the correcting journal in both years we
    would have:

    2007

    Account Dec-07 Jan-08
    dr Cr Dr Cr
    01.5000.1000 40000 20000 0
    01.8900.1000 20000 20000 20000 20000
    01.2999.1000 20000 20000 0

    In 2008
    Account Dec-08 YTD Jan -09 YTD
    dr Cr Dr Cr
    01.5000.1000 30000 30000 30000
    01.8900.1000 50000 50000 50000 50000
    01.2999.1000 50000 20000 50000 50000

    Note the retained earnings account is netting to zero but the correction will remain on it.
    This would look better if we had reversed the adjustment using change signs

    Account Dec-07 Jan-08
    dr Cr Dr Cr

    01.5000.1000 20000 0 20000
    01.8900.1000 20000 20000 20000 20000
    01.2999.1000 0 0 0 0

    In 2008
    Account Dec-08 YTD Jan -09 YTD
    dr Cr Dr Cr
    01.5000.1000 50000 0 50000
    01.8900.1000 50000 50000 50000 50000
    01.2999.1000 0 0

    Because we adjusted both years the opening balance of both years is correct. We could have
    achieved the same roll forward by doing a journal for Dec 2008 as above and in the last step when
    the account combination had been corrected we could journal 20k from the retained earnings to the
    account 01.5000.1000
    There are certain ramifications in this case for teh accuracy of the accounting that you need to be aware of.

    Before this fix you have a balance sheet account that does not have the correct balance. That balance does not reflect whatever it is supposed to represent whether its cash in the bank or the value of laptops in the office. Each year for the last number of years this account combination was misclassified and the balance has been effctively 'written off' to retained earnings at each year end.

    If you want a true and fair representation of the balance sheet this needs to be fixed. That means changing the balance that have already been published and/or reporting in the published accounts if its large enough to be necessary.

    I can think of 4 methods to fix it:

    1. Fix the opening balance of this year and all past years as above. This gives you a true reflection of the opening balance but it changes the published accounts of past years.

    2. Fix last years closing balance so all the amounts written off to retained earnings in previous years are journalled back into this account in the last period of last year before you fix the code combination using sql. Then run the glbalfix against the fixed code combination. This would still change the reported figures permanently so they dont match published reports but you wont need to reopen last years accounts. You will need an Sr raised to get the glbalfix script. this is time consuming and gives no added benefit. Its only used where localisations mean you cannot open the period again.

    3. Only journal last years balance so the opening balance this year matches the closing balance last year. This would be bad accounting as the balance sheet figure would have no basis in reality.

    4. You could correct the code combination using sql and create a journal between retained earnings and the misclassified accounts in the current period or the first period of this year (using the first period of this year avoids making this months PTD figures look distorted, when managers look at them the YTD will still have changed). This does mean that the fixing journal would be identifiable as the cause of the change in the retained earnings from last year and may need explaining with a note in the published accounts. This is the most visible method and possible the best for audit purposes since its easily explained why it was done where as the sudden unexplained change in last years accounts may look like an attempt to hide the need to fix the issue. It does not fix the years opening balance though so is not usually used.
    Top of Page

    EXAMPLE 2


    If the problem account is P&L and should be Balance sheet.
    In 2007 the account 01.1000.1000 is setup as an expense account. Its location in the accounts
    means its reported as an expense in the financial statement. At year end there is $10,000.00 in
    this account. This amount is rolled forward to 2008 not to retained earnings 01.8900.1000 in the
    first period of the year 2008.

    Account Dec-07 Jan-08
    dr Cr Dr Cr
    01.1000.1000 10000 10000
    01.8900.1000 0 0

    In 2008 a dr of 15000 is journaled to this account if its not corrected the position relating to
    this account in the first period 2009 is

    Account Dec-08 Jan-09
    dr Cr Dr Cr
    01.5000.1000 25000 25000
    01.8900.1000 0 0

    In this example the correction journal is only required in Dec-2008 since we have the YTD figure
    We zero the amount to another balance sheet account say suspense 01.2999.1000 step 2

    Account Dec-08 YTD Jan -09 YTD
    dr Cr Dr Cr
    01.5000.1000 25000 25000 25000 25000
    01.8900.1000 0 0 0 0
    01.2999.1000 25000

    Then we get the DBA to change the code combination to P&L step 3 and reverse the journal using

    change signs step 4

    In 2008
    Account Dec-08 YTD Jan -09 YTD
    dr Cr Dr Cr
    01.5000.1000 25000 0 0 0
    01.8900.1000 0 0 25000 0
    01.2999.1000 0

    You can see in this illustration it only effects the last year end. This is because the amount
    was reported in the correct place and the adjustment is for what had been a balance sheet account
    so the last YTD holds all the values from prior years.

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

    select name ledgername, chart_of_accounts_id from gl_ledgers
    where chart_of_accounts_id in (Select chart_of_accounts_id
    from gl_ledgers where name = '&ledgername');

    When there are consolidation, secondary or reporting ledgers and the ledgers share the same value set for the accounts segment then check that the code combinations associated with that account value are correct in those other ledger. If changing the value then the appropriate journals should be raised in both sets of ledgers and the accounts zeroised before changing the account type.
    In R12 you can see which other hierarchies are associated with a valueset in Setup : Financials : Flexfields : Key: Segments query your hierarchy, click on segments button, click on the account segment and click on the valueset button. On the valueset screen you have a 'Usages' button top right. Click on this and you can see which hierarchies use this valueset. Click on the Key flexfield tab and it will give you the Application name, Flexfield Title, Structure name and segment name. If you change the account type it will change for all the structures (hierarchies) that share that valueset.

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

    Note 3. If the misclassification has not crossed fiscal years, you only need to
    complete steps 8 and 9.


    Note 4. 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 before the current financial year.

    Note 5. 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. see
    Note 150687.1 How to Correct Misclassified Account Types if Using Average Daily Balances
    There is a script available (log an SR and request it quiting this note) by using glbalfix.sql that does this but it has some performance issues due to the volume of work.

    Note 6. The Account type attribute is not inherited from the Value to the Combinations
    when you run GLNSVI - Program - Inherit Segment Value Attributes.

    Note 7. If the misclassification only effects summary templates then you can drop and recreate the summary template after step 8 instead of journaling the affected accounts if you wish.
    Summary template combinations always have a type of Owners Equity. Since the summary template is updated by the detail level only and a summary code combination could have several account types rollup into it. As you cannot post directly to a summary account template it is virtually impossible for this to occur in a summary template only.


    SOLUTION
    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 beginning 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 > values.

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

    Title: Select 'Accounting Flexfield' from the LOV
    Structure: Select the applicable chart of accounts for your ledger 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.


    3. If you did not run the diagnostic to check which code combinations are misclassified follow
    this step to do it manually.

    a. 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
    account type value.

    b. Find out what the account type is for each one.
    Make a note or screen shot of these so you can see what values are currently against them.

    c. 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.


    In order to correct the balances over the year end and so effect your reports this must remain
    Incorrect until steps 3-6 are complete.

    Note: If you use Reporting/secondary ledgers then you must check the reporting/secondary ledger 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). Where there is no balance at
    the end of last year skip to step 8.

    If multiple years are involved its a business decision on how you would handle this. For changing
    an existing code combination with a Revenue or Expense account type it may be necessary to
    correct many prior years so that amounts originally passed to retained earnings can form the YTD
    opening balance of the current year otherwise the YTD balance would be inaccurate.
    The assumption here is that you would have discovered the issue in the first year.

    5. Create a journal entry that brings the misclassified account combinations
    balances to zero for the last period of your prior fiscal year for every currency.


    The contra entry should go to a temporary account such as Suspense but not to retained
    earnings or another combination with the same account segment value as the one being fixed.
    (you will reverse this batch in a later step to re-post the balances using change signs so there will be no visible effect).
    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 Reporting/secondary ledgers then you must check that the journal generated in these ledgers will zeroize the account as well. If not generate a journal in the ledger to cope with the rounding differences.The same applies to any secondary ledgers.

    6. Post the journal entry. For those with reporting ledgers or secondary ledgers that share this valueset users this must post the Reporting/secondary ledger Journals. Then run a summary Trial Balance for this account to ensure the closing balance is zero in all ledgers.

    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 ledger 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'.


    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 using the change signs method of reversal (change signs will reverse a 100Dr with a -100dr so the YTD amount will be unchanged by this fix. Posting rolls the balances forward correctly to the next fiscal year and updates retained earnings where necessary.

    11. Post the reversing journal entry. If you are using Reporting or Secondary ledgers make sure the
    reversal is posted in those ledgers as well.

    12. If in your close process you normally run 'Create Income Statement Journals' or 'Create Balance Sheet Closing Journals' then this will now be incorrect so you may need to take steps to correct them. The decision whether or not to do this is an accounting decision so you may need to discuss with your accountants and/or auditors if the Journal results have been published.

    Should you need to make adjustments after the income statement closing journals or Create Balance Sheet Closing Journals are posted, reverse and post the original closing entries, make your adjustments, then rerun the closing process to capture the new adjustments.

    If the 'Create Balance Sheet Closing Journals' have already been reversed in the following year you may need to reverse the reversed journal. Change the period to the last period of the previous year post it and reverse it again in that period to get the balances back to their original state. Then proceed with regenerating the 'Create Balance Sheet Closing Journals'.
    13. 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.