Tables involved with security rules in oracle apps R12 & R11i

Tables involved with security rules in oracle apps R12 & R11i

Tables involved with security rules in oracle apps R12 & R11i

                                                                          

In this post, Let us see the tables involved with Security rules and a query to collect the data related to security rules both in R12 and 11i releases.

TEST INSTANCE: R12.1.3

SELECT frt.responsibility_name,

       frt.responsibility_id,

       ifs.id_flex_num Chart_of_accounts_id,

       led.name ledger_name,

       ffv.flex_value_set_name Value_set_name,

       ifs.segment_name,

       ifs.segment_num,

       fvr.flex_value_rule_name,

       DECODE(fvrl.include_exclude_indicator,

               'E', 'Exclude',

               'I', 'Include',

               'Error') Inc_exc,

       fvrl.flex_value_low,

       fvrl.flex_value_high

  FROM fnd_flex_value_rule_usages fvru,

       fnd_flex_value_rules fvr,

       fnd_flex_value_rule_lines fvrl,

       fnd_responsibility_tl frt,

       fnd_id_flex_segments ifs,

       fnd_flex_value_sets  ffv,

       gl_ledgers led

 WHERE fvru.responsibility_id  = frt.responsibility_id

   AND fvru.flex_value_rule_id = fvr.flex_value_rule_id

   AND fvrl.flex_value_rule_id = fvr.flex_value_rule_id

   AND ifs.flex_value_set_id   = fvr.flex_value_set_id

   and fvr.flex_value_set_id   = ffv.flex_value_set_id

   AND ifs.id_flex_num         = led.chart_of_accounts_id

   AND led.name                = 'Vision Operations (USA)'

   AND ifs.id_flex_code        = 'GL#'

 ORDER BY

       frt.responsibility_name,

       ifs.segment_num,

       fvr.flex_value_rule_name,

       fvrl.include_exclude_indicator Desc;

TEST INSTANCE: 11i

SELECT frt.responsibility_name,

       frt.responsibility_id,

       ifs.id_flex_num Chart_of_accounts_id,

       ifs.segment_name,

       ifs.segment_num,

       fvr.flex_value_rule_name,

       DECODE(fvrl.include_exclude_indicator,

               'E', 'Exclude',

               'I', 'Include',

               'Error') Inc_exc,

       fvrl.flex_value_low,

       fvrl.flex_value_high

  FROM fnd_flex_value_rule_usages fvru,

       fnd_flex_value_rules fvr,

       fnd_flex_value_rule_lines fvrl,

       fnd_responsibility_tl frt,

       fnd_id_flex_segments ifs,

       gl_sets_of_books glsob

 WHERE fvru.responsibility_id  = frt.responsibility_id

   AND fvru.flex_value_rule_id = fvr.flex_value_rule_id

   AND fvrl.flex_value_rule_id = fvr.flex_value_rule_id

   AND ifs.flex_value_set_id   = fvr.flex_value_set_id

   AND ifs.id_flex_num         = glsob.chart_of_accounts_id

   AND glsob.name              = 'Vision Operations (USA)'

   AND ifs.id_flex_code        = 'GL#'

 ORDER BY ifs.segment_num,

       fvr.flex_value_rule_name,

       fvrl.include_exclude_indicator Desc,

       frt.responsibility_name ;