How To Correct Misclassified Accounts in General Ledger in 11i

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem: How To Correct Misclassified Accounts in General Ledger in 11i

Inherit Segment Value Attributes’, which is completed with fatal errors PERF0005: Oracle error detected in glnupd() - ORA-01407: cannot update ("GL"."GL_SEGVAL_INHERIT_INT_2020"."DETAIL_PO

On : 11.5.10.2 version, Setup forms and programs

When attempting to run the Program - Inherit Segment Value Attributes, the following error occurs.
PERF0005: Oracle error detected in glnupd() - ORA-01407: cannot update ("GL"."GL_SEGVAL_INHERIT_INT_2020"."DETAIL_PO

Symptoms:

The issue can be reproduced at will with the following steps:
1. Reports : Request : Standard

Log files:
LOG FILE
-----------------------
Filename = Program___Inherit_Segment_Valu_211210.txt
See the following error:
PERF0005: Oracle error detected in glnupd() - ORA-01407: cannot update ("GL"."GL_SEGVAL_INHERIT_INT_2020"."DETAIL_PO


Solution:



The problem could be occuring because the customer is missing some
values from the value set.

To check if values are missing from the value set, see the following script:
SELECT cc.code_combination_id, cc.segmentX
FROM gl_code_combinations cc
WHERE cc.chart_of_accounts_id =
AND cc.segmentX not in
(SELECT 1
FROM fnd_flex_valuesffv
WHERE ffv.flex_value_set_id =
AND ffv.summary_flag = decode(nvl(cc.template_id, -1), -1, 'N',
'Y')
AND ffv.flex_value = cc.segmentX)

segmentX = segment1, segment2, segment3....etc.

You can get the FLEX_VALUE_SET_ID by going to the Value Set form,
query up the segments and do the equivalent of
Help->Tools->Examine.

To Correct Misclassified Accounts in General Ledger in 11i
Please follow the below steps to accomplish your task --

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

References:

Note 248842.1 - Scripts To Troubleshoot The Error ORA-1407 When Running The Inherit Segment Value Attributes Program

Get Oracle Certifications for all Exams
Free Online Exams.com