/*********************************************************
*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
;