Wednesday, March 2, 2011

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 ;

0 Responses to “Tables involved with security rules in oracle apps R12 & R11i”

Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.