NaturalOracle DataGaurd tutorial Series Coming Soon!! Natural
For Any queries, please mail us at support@funoracleapps.com

Find Oracle Application Profiles Change in N Day

No comments
We can use below script to check Oracle EBS profile changed in last N days.

SELECT p.profile_option_name SHORT_NAME,
  n.user_profile_option_name "PROFILE NAME",
  DECODE(v.level_id, 10001, 'Site', 10002, 'Application', 10003, 'Responsibility', 10004, 'User', 10005, 'Server', 10007, 'SERVRESP', 'UnDef') LEVEL_SET,
  DECODE(TO_CHAR(v.level_id), '10001', '', '10002', app.application_short_name, '10003', rsp.responsibility_key, '10005', svr.node_name, '10006', org.name, '10004', usr.user_name, '10007', 'Serv/resp', 'UnDef') "CONTEXT",
  v.profile_option_value VALUE,
  v.LAST_UPDATE_DATE
FROM fnd_profile_options p,
  fnd_profile_option_values v,
  fnd_profile_options_tl n,
  fnd_user usr,
  fnd_application app,
  fnd_responsibility rsp,
  fnd_nodes svr,
  hr_operating_units org
WHERE p.profile_option_id = v.profile_option_id (+)
AND p.profile_option_name = n.profile_option_name
--and upper(n.user_profile_option_name) like upper('&profile_name')
AND TRUNC(v.LAST_UPDATE_DATE) > TRUNC(sysdate-&days)
AND usr.user_id (+)           = v.level_value
AND rsp.application_id (+)    = v.level_value_application_id
AND rsp.responsibility_id (+) = v.level_value
AND app.application_id (+)    = v.level_value
AND svr.node_id (+)           = v.level_value
AND org.organization_id (+)   = v.level_value
AND v.LAST_UPDATE_DATE       IS NOT NULL
AND n.language='US'
ORDER BY last_update_date DESC,
  short_name,
  level_set;

No comments :

Post a Comment