OracleAppsDNA

SQL Query to findout Profile Option Values

/*********************************************************
*PURPOSE: To find out profile option Values              *
*AUTHOR: Shailender Thallam                              *
**********************************************************/
SELECT DISTINCT POT.PROFILE_OPTION_NAME "PROFILE_CODE" 
  , POT.USER_PROFILE_OPTION_NAME "PROFILE_NAME" 
       , DECODE (a.profile_option_value
             , '1', '1 (may be "Yes")'
             , '2', '2 (may be "No")'
             , a.profile_option_value
              ) "PF_VALUE"
     , DECODE (a.level_id
             , 10001, 'Site'
             , 10002, 'Application'
             , 10003, 'Responsibility'
             , 10004, 'User'
             , 10005, 'Server'
             , 10006, 'Organization'
			 , a.level_id
              ) "LEVEL_IDENTIFIER"
     , DECODE (a.level_id
             , 10002, e.application_name
             , 10003, c.responsibility_name
             , 10004, D.USER_NAME
             , 10005, F.HOST || '.' || F.DOMAIN
             , 10006, g.name
             , '-'
              ) "LEVEL_NAME"
  
FROM fnd_application_tl e ,
  fnd_user d ,
  fnd_responsibility_tl c ,
  fnd_profile_option_values a ,
  fnd_profile_options b ,
  fnd_profile_options_tl pot ,
  fnd_nodes f ,
  hr_all_organization_units g
WHERE 1=1
AND UPPER(pot.USER_PROFILE_OPTION_NAME) LIKE UPPER('MO: Default Operating Unit')
AND pot.profile_option_name = b.profile_option_name
AND b.application_id        = a.application_id(+)
AND b.profile_option_id     = a.profile_option_id(+)
AND a.level_value           = c.responsibility_id(+)
AND a.level_value           = d.user_id(+)
AND a.level_value           = e.application_id(+)
AND a.level_value           = f.node_id(+)
AND a.level_value           = g.organization_id(+)
AND pot.language            ='US'
ORDER BY PROFILE_NAME ,
  LEVEL_IDENTIFIER ,
  LEVEL_NAME ,
  PF_VALUE
  ;
Exit mobile version