Query to get responsibility level profile option values

Application Object Library
Oracle Apps EBS query to get responsibility level profile option values
How to find profile option value at responsibility level
Oracle Profile Options
--Add new subqueries for your profile options
  SELECT resp_list.resp_name,
         g.responsibility_name,
         h.application_name,
         g.responsibility_key,
         i.request_group_name,
         m.MENU_NAME,
         m.user_menu_name,
         (SELECT (SELECT L.MEANING User_Type
                    FROM FND_COMMON_LOOKUPS L
                   WHERE     L.LOOKUP_TYPE = 'HR_USER_TYPE'
                         AND L.APPLICATION_ID BETWEEN 800 AND 899
                         AND L.LOOKUP_CODE = fpov.profile_option_value)
            FROM fnd_profile_option_values fpov, fnd_profile_options_vl fpo
           WHERE     fpov.profile_option_id = fpo.profile_option_id
                 AND USER_PROFILE_OPTION_NAME = 'HR:User Type'
                 AND fpov.level_value = responsibility_id)
            "HR:User Type",
         (SELECT (SELECT NAME
                    FROM HR_OPERATING_UNITS
                   WHERE ORGANIZATION_ID = fpov.profile_option_value)
            FROM fnd_profile_option_values fpov, fnd_profile_options_vl fpo
           WHERE     fpov.profile_option_id = fpo.profile_option_id
                 AND USER_PROFILE_OPTION_NAME = 'MO: Default Operating Unit'
                 AND fpov.level_value = responsibility_id)
            "MO: Default Operating Unit",
         (SELECT (SELECT NAME
                    FROM HR_OPERATING_UNITS
                   WHERE ORGANIZATION_ID = fpov.profile_option_value)
            FROM fnd_profile_option_values fpov, fnd_profile_options_vl fpo
           WHERE     fpov.profile_option_id = fpo.profile_option_id
                 AND USER_PROFILE_OPTION_NAME = 'MO: Operating Unit'
                 AND fpov.level_value = responsibility_id)
            "MO: Operating Unit",
         (SELECT (SELECT ORGANIZATION_NAME
                    FROM ORG_ORGANIZATION_DEFINITIONS
                   WHERE     SYSDATE <= NVL (DISABLE_DATE, SYSDATE)
                         AND ORGANIZATION_ID = fpov.profile_option_value)
            FROM fnd_profile_option_values fpov, fnd_profile_options_vl fpo
           WHERE     fpov.profile_option_id = fpo.profile_option_id
                 AND USER_PROFILE_OPTION_NAME =
                        'Service: Inventory Validation Organization'
                 AND fpov.level_value = responsibility_id)
            "Service: Inventory Vld Org",
         (SELECT (SELECT ORGANIZATION_NAME
                    FROM ORG_ORGANIZATION_DEFINITIONS
                   WHERE     SYSDATE <= NVL (DISABLE_DATE, SYSDATE)
                         AND ORGANIZATION_ID = fpov.profile_option_value)
            FROM fnd_profile_option_values fpov, fnd_profile_options_vl fpo
           WHERE     fpov.profile_option_id = fpo.profile_option_id
                 AND USER_PROFILE_OPTION_NAME =
                        'CSI: Operating Unit Restriction'
                 AND fpov.level_value = responsibility_id)
            "CSI: Operating Unit Restr"
    FROM apps.fnd_responsibility_vl g,
         apps.fnd_application_vl  h,
         apps.fnd_request_groups  i,
         fnd_menus_vl             m,
         (SELECT 'Responsibility 1 Name' resp_name FROM DUAL
          UNION
          SELECT 'Responsibility 2 Name' resp_name FROM DUAL) resp_list
   WHERE     h.application_id(+) = g.application_id
         AND g.request_group_id = i.request_group_id(+)
         AND g.menu_id = m.menu_id(+)
         AND g.responsibility_name(+) = resp_list.resp_name
ORDER BY 1;

For more reusable queries, click https://tech7.in/category/technologyblog/oracle/

Related posts: Upload your own post and refer it anywhere anytime:
Upload your own post