How To Check Cross Validation Rules

The cross validation rule setup can be very complex depending on the complexity of the Accounting Flexfield Structure.
There are a number of ways to get the wrong results and the best way to investigate is listed below:

1. Make sure that you include all the values you want the rule to apply to. If they are not included then they are automatically excluded.
Where the segment values are not of a fixed length start with 0 not 00 otherwise 0 is not in the rule.
Example
Include 01-00-00-0000 to 01-ZZ-ZZZZ-ZZZZ
should be
Include 01-0-0-0 to 01-zz-zzzz-zzzz
Otherwise the Exclude 01-0-01-0 to  01-AA-ZZZZ-ZZZZ
is not going to affect the outcome since it is not within the range that the rule will be applied to.

Note: Lower case letters are looked at after upper case hence 0 to z includes Z

2. Apply the same logic to the exclude rules
so the exclude should be 01-0-0-0 to  01-AA-ZZZZ-ZZZZ

In order to check whether the range will appear in a rule we have some sql below.
a. First find your chart of account details using script 1 or 2.
b. Adjust the sql to work for your chart of accounts by adding or taking away any statement relating to segmentn in scripts 3 and 4
c. check that the combination in your exclude lines exists in the include using script 3, in the example above test 01-0-0-0 and 01-AA-ZZZZ-ZZZZ

For combinations failing when they should not:
d. check that the combination is in the include using sql 3   -  it should be
e. check that the combination is in the exclude using sql 4  -  it should not be if it is restructure the rule until it is excluded.

For a combination passing when it should fail
f. check that the combination is in the include using sql 3 -   it should be if it is not restructure the rule until it is included
g. check that the combination is in the exclude using sql 4 - it should not be if it is restructure the rule until it is excluded.

SCRIPTS

1. To find the details of the Accounting Flexfield structure you are working with in R12 run this sql

SELECT gls.name,
idfs.id_flex_num chart_of_accounts_id,
idfs.segment_num,
idfs.flex_value_set_id,
fvs.flex_value_set_name,
idfs.application_id,
idfs.id_flex_code,
idfs.application_column_name,
idfs.segment_name,
fvs.security_enabled_flag,
(CASE
WHEN fvs.validation_type = 'F' THEN 'Table'
WHEN fvs.validation_type = 'I' THEN 'Independent'
WHEN fvs.validation_type = 'D' THEN 'Dependent'
WHEN fvs.validation_type = 'N' THEN 'None'
WHEN fvs.validation_type = 'P' THEN 'Pair'
WHEN fvs.validation_type = 'U' THEN 'Special'
ELSE 'Unknown Type'
END) validation_type,
(CASE
WHEN fvs.validation_type = 'F' then fvt.application_table_name
ELSE 'Not Applicable' END) validation_table_name
FROM gl_ledgers gls,
fnd_id_flex_segments idfs,
fnd_flex_value_sets fvs,
fnd_flex_validation_tables fvt
WHERE gls.chart_of_accounts_id (+) = idfs.id_flex_num
AND fvs.flex_value_set_id = idfs.flex_value_set_id
AND gls.ledger_id = &LedgerId
AND idfs.application_id = 101
AND idfs.id_flex_code IN ('GL#', 'GLLE')
AND fvs.flex_value_set_id = fvt.flex_value_set_id (+)
ORDER BY idfs.id_flex_code, idfs.id_flex_num , idfs.segment_num ASC;

2. To find the details of the Accounting Flexfield structure you are working with in R11i run this sql:

SELECT glsob.name,
idfs.id_flex_num chart_of_accounts_id,
idfs.segment_num,
idfs.flex_value_set_id,
idfs.application_id,
idfs.application_column_name,
idfs.segment_name,
fvs.security_enabled_flag
FROM gl_sets_of_books glsob,
fnd_id_flex_segments idfs,
fnd_flex_value_sets fvs
WHERE glsob.chart_of_accounts_id = idfs.id_flex_num
AND fvs.flex_value_set_id = idfs.flex_value_set_id
AND glsob.set_of_books_id = &SoBID
AND idfs.application_id = 101
AND idfs.id_flex_code = 'GL#'
ORDER BY idfs.segment_num ASC;rt code here]

You will need to modify the ranges in the where clause from ('&s1' between segment1_low and segment1_high) onwards so that you have the segment values returned above in the order of use. If you have only 5 segments for example and they are segment1, segment2, segment3,segment4,segment5 then you would have
(('&s1' between segment1_low and segment1_high) and
('&s2' between segment2_low and segment2_high) and
('&s3' between segment3_low and segment3_high) and
('&s4' between segment4_low and segment4_high) and
('s&5' between segment5_low and segment5_high));

3. To find if a code combination will be in the include range in either version use this giving the segment value for each prompt:

select
rule_line_id,
application_id,
id_flex_code,
id_flex_num,
flex_validation_rule_name,
enabled_flag
from fnd_flex_include_rule_lines
where application_id = 101
and id_flex_code = 'GL#'
and id_flex_num = 101
and (('&s1' between segment1_low and segment1_high) and
('&s2' between segment2_low and segment2_high) and
('&s3' between segment3_low and segment3_high) and
('&s4' between segment4_low and segment4_high) and
('s&5' between segment5_low and segment5_high) and
('&s6' between segment6_low and segment6_high) and
('&s7' between segment7_low and segment7_high));

4. To find if a combination is in the exclude range in either version use this giving the segment value for each prompt:

select
rule_line_id,
application_id,
id_flex_code,
id_flex_num,
flex_validation_rule_name,
enabled_flag
from fnd_flex_exclude_rule_lines
where application_id = 101
and id_flex_code = 'GL#'
and id_flex_num = 101
and (('&s1' between segment1_low and segment1_high) and
('&s2' between segment2_low and segment2_high) and
('&s3' between segment3_low and segment3_high) and
('&s4' between segment4_low and segment4_high) and
('s&5' between segment5_low and segment5_high) and
('&s6' between segment6_low and segment6_high) and
('&s7' between segment7_low and segment7_high));