Subject: PLSQL Script to check Chart of Account Is Restricted by which Cross Validation Rule.
---PLSQL Code---
SET SERVEROUTPUT ON SIZE 1000000;
DECLARE
p_conc_segments VARCHAR2(200) := '47.2020.65100.00.000.0000' ;
--'&Conc_Segments';
p_accounting_flex VARCHAR2(100) := 'Accounting Flexfield';
--'&Flex_Structure';
CURSOR c_flex_rules(cp_id_flex_num NUMBER)
IS
SELECT
R.flex_validation_rule_name rule_name,
T.error_message_text error_message,
nvl(to_number(to_char(R.start_date_active,'J')), 0) start_date,
nvl(to_number(to_char(R.end_date_active,'J')), 0) end_date,
L.include_exclude_indicator IE_indicator,
L.concatenated_segments_low segments_low,
L.concatenated_segments_high segments_high
FROM
fnd_flex_validation_rule_lines L,
fnd_flex_validation_rules R,
FND_FLEX_VDATION_RULES_TL T
WHERE
R.enabled_flag = 'Y' AND
L.enabled_flag = R.enabled_flag AND
R.application_id = L.application_id AND
R.application_id = T.application_id AND
R.flex_validation_rule_name = L.flex_validation_rule_name AND
R.flex_validation_rule_name = T.flex_validation_rule_name AND
R.application_id = 101 AND
R.id_flex_num = L.id_flex_num AND
R.id_flex_num = T.id_flex_num AND
R.id_flex_num = cp_id_flex_num AND
R.id_flex_code = L.id_flex_code AND
R.id_flex_code = T.id_flex_code AND
T.language = 'US' AND
R.id_flex_code = 'GL#' AND
trunc(nvl(R.start_date_active, sysdate)) <= trunc(sysdate) AND
trunc(nvl(R.end_date_active, sysdate+1)) > trunc(sysdate)
ORDER BY
R.flex_validation_rule_name asc, L.Include_Exclude_Indicator desc;
v_flex_rule c_flex_rules%ROWTYPE;
v_delimiter VARCHAR2(1);
v_id_flex_num NUMBER;
v_invalid BOOLEAN;
v_seg_count NUMBER;
v_segment VARCHAR2(100);
v_segments FND_FLEX_EXT.SegmentArray;
v_segments_low FND_FLEX_EXT.SegmentArray;
v_segments_high FND_FLEX_EXT.SegmentArray;
v_temp NUMBER;
i NUMBER;
v_rule_name
fnd_flex_vdation_rules_vl.flex_validation_rule_name%TYPE;
v_intro VARCHAR2(100);
v_result VARCHAR2(2000);
CRLF VARCHAR2(1) := CHR(10);
BEGIN
v_intro := CRLF||'For flex structure
"'||p_accounting_flex||'" the combination '||CRLF||'"'||p_conc_segments||'"';
v_result := ' ';
BEGIN
SELECT concatenated_segment_delimiter, id_flex_num
INTO v_delimiter, v_id_flex_num
FROM fnd_id_flex_structures FS
WHERE
application_id = 101 AND
id_flex_code = 'GL#' AND
id_flex_num =
(SELECT min(id_flex_num)
FROM fnd_id_flex_structures_tl
WHERE
application_id = 101 AND
id_flex_code = 'GL#' AND
id_flex_structure_name = p_accounting_flex);
EXCEPTION
WHEN NO_DATA_FOUND THEN BEGIN
raise_application_error(-20001, 'Unable to locate the flex structure
data.');
END;
END;
v_seg_count :=
FND_FLEX_EXT.Breakup_Segments (p_conc_segments, v_delimiter, v_segments);
OPEN c_flex_rules(v_id_flex_num);
FETCH c_flex_rules INTO v_flex_rule;
<<rule_cursor>>
LOOP
EXIT WHEN c_flex_rules%NOTFOUND;
v_temp := FND_FLEX_EXT.Breakup_Segments (v_flex_rule.segments_low,
v_delimiter, v_segments_low);
v_temp := FND_FLEX_EXT.Breakup_Segments (v_flex_rule.segments_high,
v_delimiter, v_segments_high);
i
:= 1;
IF
v_flex_rule.IE_indicator = 'I' THEN
v_rule_name := v_flex_rule.rule_name;
v_invalid := FALSE;
<<include>>
WHILE v_flex_rule.rule_name = v_rule_name AND -- cycle on Include lines
of a rule
v_flex_rule.IE_indicator = 'I' AND
NOT v_invalid AND -- until one Include line is found
c_flex_rules%FOUND LOOP -- including the cc in question
v_invalid := FALSE;
FOR i IN 1 .. v_seg_count LOOP
v_segment := v_segments(i);
IF v_segments(i) NOT BETWEEN v_segments_low(i) AND v_segments_high(i)
THEN
v_invalid := TRUE;
END IF;
END LOOP;
FETCH c_flex_rules INTO v_flex_rule;
v_temp := FND_FLEX_EXT.Breakup_Segments (v_flex_rule.segments_low,
v_delimiter, v_segments_low);
v_temp := FND_FLEX_EXT.Breakup_Segments (v_flex_rule.segments_high,
v_delimiter, v_segments_high);
END LOOP include;
IF v_invalid THEN
v_result := v_result ||CRLF||'Not included in
"'||v_rule_name||'" rule.';
END IF;
ELSE
<<exclude>>
LOOP
v_segment := v_segments(i);
EXIT exclude WHEN v_segments(i) NOT BETWEEN v_segments_low(i) AND
v_segments_high(i);
IF (i = v_seg_count) THEN
v_result := v_result ||CRLF||'Excluded by
"'||v_flex_rule.rule_name||'" rule.';
v_invalid := TRUE;
EXIT exclude;
END IF;
i := i + 1;
END LOOP exclude;
FETCH c_flex_rules INTO v_flex_rule;
END IF;
END LOOP rule_cursor;
IF v_result <> ' ' THEN
dbms_output.put_line( v_intro ||' is ' ||v_result );
ELSE
dbms_output.put_line( v_intro||' does not violate any cross-validation
rules.');
END IF;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20002, SQLERRM);
END;
/
Output:
