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.
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 ;
Do you think this Article is useful?
Subscribe to:
Post Comments (Atom)
0 Responses to “Tables involved with security rules in oracle apps R12 & R11i”
Post a Comment