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

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:

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



Solution:




The Inherit Segment Value Attributes Program terminates with fatal errors.
The log file shows the following information:

...
>> glnupd() 11-SEP-2003 11:38:02
PERF0005: Oracle error detected in glnupd() - ORA-01407: cannot update ("
GL"."GL_SEGVAL_INHERIT_INT_62997"."DETAIL_P

GSVI0004: The Segment Value Inheritance program terminated with fatal errors.
...

ORA-01407: cannot update mandatory (NOT NULL) column to NULL

Run the following queries and provide the information to Oracle Support.

1. Find the different accounting structures defined, use the following script:

select id_flex_structure_name, id_flex_num
from fnd_id_flex_structures_vl
where id_flex_code = 'GL#';


2. Find the identification (set_of_books_id) of corresponding set of books. Use
the following query:

col sob format 9999999
col coa format 9999999
col name format a35
col curr format a3
Select set_of_books_id sob, chart_of_accounts_id coa, name, currency_code curr
from gl_sets_of_books
order by 1;

3. Use the identification of the flexfield found in step 1 to execute the
following query. It gives the accounting structure and displays the name of
each part of the structure and its corresponding database column.:

col app_col format a12
col segment format a15
col num format 999
col value_set format a25
select s.segment_name segment
, s.application_column_name app_col
, s.segment_num num
, s.flex_value_set_id
, f.flex_value_set_name value_set
from fnd_id_flex_segments s
, fnd_flex_value_sets f
where s.id_flex_num = &id_flex_num
and s.application_id = 101
and s.id_flex_code = 'GL#'
and s.flex_value_set_id = f.flex_value_set_id
order by s.segment_num;

4. Check that every segment value used in an account combination exists in a
value set.
Replace segmentN and flex_value_set_id appropriately. This statement needs to be
executed for every segment in the chart of accounts.
SELECT cc.code_combination_id, cc.segmentN
FROM gl_code_combinations cc
WHERE cc.chart_of_accounts_id = &chart_of_accounts_id
AND NOT EXISTS (
SELECT ffv.flex_value
FROM fnd_flex_values ffv
WHERE ffv.flex_value_set_id = &flex_value_set_id_of_segmentN
AND ffv.flex_value = cc.segmentN);

5. Determine order of segment qualifiers.
SELECT rownum, value_attribute_type
FROM (
SELECT value_attribute_type
FROM fnd_flex_validation_qualifiers
WHERE id_flex_code = 'GL#'
AND id_flex_application_id = 101
AND flex_value_set_id = &flex_value_set_id_of_natural_account_segment
ORDER BY assignment_date, value_attribute_type);

6. Check that every segment value has a valid value for the detail posting
allowed and detail budgeting allowed qualifiers.
SELECT flex_value_set_id, flex_value
from fnd_flex_values
where flex_value_set_id in
(select flex_value_set_id
from fnd_id_flex_segments
where application_id = 101
and id_flex_code = 'GL#'
and id_flex_num = &chart_of_accounts_id)
and (substr(compiled_value_attributes, 1, 1) IS NULL
or substr(compiled_value_attributes, 3, 1) IS NULL);

7. Check that every segment value in an independent value set does not have a
parent segment value.
SELECT flex_value_set_id, flex_value
from fnd_flex_values
where flex_value_set_id in
(select fnd.flex_value_set_id
from fnd_id_flex_segments fnd, fnd_flex_value_sets fvs
where fnd.application_id = 101
and fnd.id_flex_code = 'GL#'
and fnd.id_flex_num = &chart_of_accounts_id
and fvs.flex_value_set_id = fnd.flex_value_set_id
and fvs.validation_type = 'I')
and parent_flex_value_low is not null;

8. Execute the programs $FND_TOP/sql/afffckff.sql and $FND_TOP/sql/afffcvst.sql.



References:


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

Get Oracle Certifications for all Exams
Free Online Exams.com