Responsibility plus rule basics

/*##############################################################################

#        RESP PLUS RULE BASICS

/*############################################################################*/

 

  SELECT rtl.responsibility_name

       , fvr.flex_value_rule_name rule

       , fvrtl.error_message

       , DECODE(ffvrl.include_exclude_indicator,'E','Exclude','I','Include') inc_exc

       , ffvrl.flex_value_low

       , ffvrl.flex_value_high

    FROM applsys.fnd_flex_value_rules fvr

       , applsys.fnd_flex_value_rules_tl fvrtl

       , applsys.fnd_flex_value_rule_usages fvru

       , applsys.fnd_responsibility_tl rtl

       , applsys.fnd_responsibility fr

       , applsys.fnd_application_tl fatl

       , applsys.fnd_flex_value_rule_lines ffvrl

       , applsys.fnd_flex_value_sets ffvs

   WHERE fvr.flex_value_rule_id = fvrtl.flex_value_rule_id

     AND fvrtl.flex_value_rule_id = fvru.flex_value_rule_id

     AND fvru.responsibility_id = rtl.responsibility_id

     AND fatl.application_id = rtl.application_id

     AND ffvrl.flex_value_rule_id = fvrtl.flex_value_rule_id

     AND ffvs.flex_value_set_id = ffvrl.flex_value_set_id

     AND rtl.responsibility_id = fr.responsibility_id

ORDER BY rtl.responsibility_name

       , fvrtl.error_message;

 

/*##############################################################################

#        RULES ONLY

/*############################################################################*/

 

  SELECT fvr.flex_value_rule_name rule

       , fvrtl.error_message

       , DECODE(ffvrl.include_exclude_indicator,'E','Exclude','I','Include') inc_exc

       , ffvrl.flex_value_low

       , ffvrl.flex_value_high

    FROM applsys.fnd_flex_value_rules fvr

       , applsys.fnd_flex_value_rules_tl fvrtl

       , applsys.fnd_flex_value_rule_lines ffvrl

       , applsys.fnd_flex_value_sets ffvs

   WHERE fvr.flex_value_rule_id = fvrtl.flex_value_rule_id

     AND ffvrl.flex_value_rule_id = fvrtl.flex_value_rule_id

     AND ffvs.flex_value_set_id = ffvrl.flex_value_set_id

ORDER BY fvrtl.error_message;

 

/*##############################################################################

#        RESP PLUS RULE NAME ONLY

/*############################################################################*/

 

  SELECT DISTINCT

         rtl.responsibility_name

       , fvr.flex_value_rule_name rule

    FROM applsys.fnd_flex_value_rules fvr

       , applsys.fnd_flex_value_rules_tl fvrtl

       , applsys.fnd_flex_value_rule_usages fvru

       , applsys.fnd_responsibility_tl rtl

       , applsys.fnd_responsibility fr

       , applsys.fnd_application_tl fatl

       , applsys.fnd_flex_value_rule_lines ffvrl

       , applsys.fnd_flex_value_sets ffvs

   WHERE fvr.flex_value_rule_id = fvrtl.flex_value_rule_id

     AND fvrtl.flex_value_rule_id = fvru.flex_value_rule_id

     AND fvru.responsibility_id = rtl.responsibility_id

     AND fatl.application_id = rtl.application_id

     AND ffvrl.flex_value_rule_id = fvrtl.flex_value_rule_id

     AND ffvs.flex_value_set_id = ffvrl.flex_value_set_id

     AND rtl.responsibility_id = fr.responsibility_id

ORDER BY rtl.responsibility_name

       , fvr.flex_value_rule_name;

 

/*##############################################################################

#        COUNT PER RESPONSIBILITY

/*############################################################################*/

 

  SELECT DISTINCT

         rtl.responsibility_name

       , COUNT (*) ct

    FROM applsys.fnd_flex_value_rules fvr

       , applsys.fnd_flex_value_rules_tl fvrtl

       , applsys.fnd_flex_value_rule_usages fvru

       , applsys.fnd_responsibility_tl rtl

       , applsys.fnd_responsibility fr

       , applsys.fnd_application_tl fatl

       , applsys.fnd_flex_value_rule_lines ffvrl

       , applsys.fnd_flex_value_sets ffvs

   WHERE fvr.flex_value_rule_id = fvrtl.flex_value_rule_id

     AND fvrtl.flex_value_rule_id = fvru.flex_value_rule_id

     AND fvru.responsibility_id = rtl.responsibility_id

     AND fatl.application_id = rtl.application_id

     AND ffvrl.flex_value_rule_id = fvrtl.flex_value_rule_id

     AND ffvs.flex_value_set_id = ffvrl.flex_value_set_id

     AND rtl.responsibility_id = fr.responsibility_id

     AND rtl.responsibility_name LIKE 'GL%'

GROUP BY rtl.responsibility_name

ORDER BY rtl.responsibility_name;