Tuesday, 17 January 2017

How to get Values at All Levels for a Profile Option

SELECT fpot.user_profile_option_name  "Profile"
       ,fpov.profile_option_value     "Value"
       ,decode(fpov.level_id
             ,10001,'Site'
             ,10002,'Application'
             ,10003,'Responsibility'
             ,10004,'User'
             ,'UNKNOWN')              "Level"
       ,decode(fpov.level_id
             ,10002,fat.application_name
             ,10003,frt.responsibility_name
             ,10004,fu.user_name
             ,'n/a')                  "Level value"
FROM  applsys.fnd_application_tl         fat
      ,applsys.fnd_user                  fu
      ,applsys.fnd_responsibility_tl     frt
      ,applsys.fnd_profile_option_values fpov
      ,applsys.fnd_profile_options       fpo
      ,applsys.fnd_profile_options_tl    fpot
WHERE    UPPER(fpot.user_profile_option_name) LIKE '%&profile_name_case_sensitive%'
  AND    fpo.hierarchy_type = 'SECURITY'
  AND    fpot.profile_option_name   = fpo.profile_option_name
  AND    fpot.LANGUAGE              = 'US'
  AND    fpo.application_id         = fpov.application_id(+)
  AND    fpo.profile_option_id      = fpov.profile_option_id(+)
  AND    fpov.level_value           = frt.responsibility_id(+)
  AND    frt.LANGUAGE(+)            = 'US'
  AND    fpov.level_value           = fu.user_id(+)
  AND    fpov.level_value           = fat.application_id(+)
  AND    fat.LANGUAGE(+)            = 'US'
ORDER  BY "Profile"
          ,"Level"
          ,"Level value"
          ,"Value";

No comments:

Post a Comment