Oracle EBS - PLSQL Script to check Chart of Account is restricted by which Cross Validation Rule

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: