Change Account Qualifier
From: PeriasamyR@mashreqbank.com
Subject: Unable to update Acct Type...
Date: Tue, 27 Apr 1999 11:14:36 -0400 (EDT)
Hello all,
We are unable to update the Account Type or other account qualifiers, even after
unfreezing all the accounting flexfields which reference the concerned value set. We are
on Rel 11.02 with Db server running HPUX and Applt server running on NT.
We had done it several times in 10.6/10.7, but this is the first time I am getting this
error on Release 11. Is there any patch or am I missing out something.
Thanks in advance Peri
Date: Tue, 27 Apr 1999 10:43:02 -0700
From: Subba.Rao@Notes.airtouch.com
Subject: Re: Unable to update Acct Type...
I am enclosing the text from user manual. I hope it will help.
Account Type
You see this qualifier, which requires a value, for the natural account segment only.
Enter the type of your proprietary account (Asset, Liability, Owners' Equity, Revenue or
Expense) or the type of your budgetary account (Budgetary Dr or Budgetary Cr) your segment
value represents. Choose any proprietary balance sheet account type if you are defining a
statistical account segment value. If you choose a proprietary income statement account
type for a statistical account segment value, your statistical balance will zero-out at
the end of the fiscal year.
Your GL account combinations have the same account type as the account segment which they
include. You cannot change this field unless you first unfreeze all Accounting Flexfield
structures that reference your account segment. Changing the account type only affects new
GL accounts created with the reclassified account segment. Changing the account type does
not change the account type of existing GL accounts.
To change the account type of existing Accounting Flexfields, refer to the Misclassified
Account Types topical essay and/or call Oracle customer support for assistance.
Correcting Misclassified Account Types
In the event you have an account with a misclassified account type that results in an
erroneous Retained Earnings calculation, you can correct your account balances and the
misclassified account type.
For example, after running a trial balance for the first period of your new fiscal year,
you notice that your Cash account balance is zero. You discover that your Cash account was
originally created with an account type of Expense rather than Asset. Therefore, when you
opened the first period of your new fiscal year, General Ledger automatically closed out
your Cash account balance to Retained Earnings.
To correct balances for a misclassified account:
1. Reopen the last period of your prior fiscal year, if it is closed.
2. Create a journal entry that brings the misclassified account balance to zero for the
last day of the last period of your prior fiscal year. Use a temporary account such as
Suspense for the offsetting amount.
3. Post the journal entry on the last day of the last period of your prior fiscal year.
4. Verify that the misclassified account balance is zero by reviewing account balances
online or in reports.
5. Correct the account type of the misclassified account by changing the segment value
qualifiers. General Ledger prevents you from changing the account type unless you first
unfreeze all account structures that reference your account segment using the Key
Flexfield Segments window.
6. Ask your System Administrator to correct the account type of all accounts referencing
the misclassified account by updating the ACCOUNT_TYPE column in the GL_CODE_COMBINATIONS
table using SQL*Plus.
7. Restore the misclassified account balance by reversing the journal entry you posted in
Step 3 above. Reverse the journal entry into the last day of the same period that it was
originally posted.
8. Post the reversing journal entry.
Using the previous example, the Cash account type is now Asset so when you post the
reversing journal entry, General Ledger rolls forward your Cash and Retained Earnings
balances into the first period of the new fiscal year.
9. Review the corrected account balances online or in reports.
Date: Tue, 27 Apr 1999 16:25:39 -0500
From: Oracle - Kathy Farmer oracleadmin@co.scott.mn.us
Subject: RE: Unable to update Acct Type...
This may not solve your problem, but you might want to just double check all of your
flexfields. We had the same problem and found that we had some HR/Payroll flexfields which
were referencing the accounting flexfield value sets - one of these flexfields was not
even referencing the account segment, but the cost center segment - we had to unfreeze all
the flexfields that referenced anything related to the AFF to get this to work.
We are on 10.7 16.1 SC. Good luck.
Date: Fri, 22 Oct 1999 13:38:26 -0600
From: "Kossmann, Bill" BKossmann@mail.dthr.ab.ca
Subject: RE: GL Changing Account Type
Joe,
I just finished going through this exercise two weeks ago. Check out the
paper "Correcting Misclassified Account Types in General Ledger" on the
MetaLink site. The document ID is 1050920.6.
Worked like a charm for me, and I had to do multiple years; we're on 10.7
NCA. Contact me directly if you have any questions.
HTH
Bill
Date: Sat, 23 Oct 1999 08:51:43 -0400
From: "Bob Delaney" rdelaney@prodigy.net
To: "Ora Apps" OraApps-L@cpa.qc.ca
Subject: RE: GL Changing Account Type
Here's a copy of a procedure I put together a while ago to keep this
situation from happening in the first place. I've used it in both 10.7 SC
and R11 NCA, but I've never tried it in character.
Bob Delaney
ISS, Inc
---------------------------------------------------------------
Every Oracle App's client I've ever worked at to date has hated the
standard Oracle process of defaulting GL account types to "Expense".
If a user enters a new balance sheet account and description and
saves, the account is automatically saved with a valid but incorrect
account type. Incorrect account types can cause a BUNCH of
problems with year-end retained earnings processing, budget
uploads and OFA interfaces.
Its not pretty, but here's a process that will generate an error if the
user does not specify a valid account type.
1) Sign on to the Application Developer responsibility:
a) Menu path Application-Validation-Quick Codes-Special
Use Find, select ACCOUNT_TYPE
Add a new account type "X" with a description of "Invalid"
b) Menu path Flexfield-Key-Register
Use Find, specify Application of Oracle General Ledger, hit Find button
Select Qualifiers button for Accounting Flexfield
Down arrow to GL_Account
Change the default value from Expense to "Invalid"
c) Menu path Application-Validation-Quick Codes-Special
Use Find, select ACCOUNT_TYPE
Disable the new account type "X"
2) For R10 installations (only), you must also connect to
SQL*Plus (as APPS) and create the following trigger.
Remember to update the value set ID specific to your installation.
If your installation has multiple sets of books, the when clause
may need to include multiple ID's. the trigger should be named
according to client's standards. This trigger is NOT required in
R11 since the Value Set Maintenance form generates an error
if the account type quick code is disabled.
/*+==========================================+
|
| FILENAME: custom_fnd_flex_values_trg.sql
|
+===========================================+
|
| DESCRIPTION
| This trigger will generate an error if a user attempts to
| create a main account without specifying a valid acct type.
|
| MODIFICATION HISTORY - Created by ISS - 08/16/99
|
*===========================================*/
CREATE OR REPLACE TRIGGER
CUSTOM_FND_FLEX_VALUES_TRG
BEFORE INSERT ON FND_FLEX_VALUES
FOR EACH ROW
WHEN (new.flex_value_set_id = xxxxxxx)
DECLARE
v_temp NUMBER(15);
BEGIN
IF :NEW.COMPILED_VALUE_ATTRIBUTES IS NULL THEN
FND_MESSAGE.SET_NAME('FND','Account Type');
FND_MESSAGE.SET_TOKEN
('Error','PLEASE ENTER A VALID ACCOUNT TYPE...');
APP_EXCEPTION.RAISE_EXCEPTION;
END IF;
IF SUBSTR(:NEW.COMPILED_VALUE_ATTRIBUTES,5,1) = 'X' THEN
FND_MESSAGE.SET_NAME('FND','Account Type');
FND_MESSAGE.SET_TOKEN
('Error','PLEASE ENTER A VALID ACCOUNT TYPE...');
APP_EXCEPTION.RAISE_EXCEPTION;
END IF;
END;
/