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:- Register a test concurrent program from backend in Oracle EBS
- Supervisor Hierarchy and Approval Limits in Oracle EBS R12
- Query to find vacation rules in Oracle R12
- View java class source in oracle database
- Load Java source code in oracle database
- Query to find all APIs of Oracle Apps modules
- OAF customizations queries
- Oracle apps list of concurrent programs in a Request Set
- Query to get Organization Hierarchy in Oracle apps
- Oracle apps query to find incompatible programs blocking a concurrent request
- Form function attached to which responsibility and user
- Query to find the responsibility attached to user in oracle apps
- How to enable debug log in Oracle apps EBS
- How to add AME role and responsibility to user
- Oracle form attached to which responsibility
- SQL query to get MIME type for file extension
- Query to get concurrent program and executable details
- Query to get responsibility level profile option values
- Query to validate migration of XML/BI Publisher Template and Data Definition
- Query to get details of XML/BI Publisher Template and Data Definition
- Query to get Site level and Organization level Profile Option Value
- PL-SQL code to reset application user password
- PL-SQL Procedure to send email with attachment
- PL-SQL to delete Concurrent Program and Executable
- Query to get the details of the lock in the database
- PL-SQL code to delete XML/BI Publisher template and Data Definition
- Query to find Form Personalization
- Query to find profile option values
- Query to get details of concurrent program parameters
- PL-SQL Code to find Menu Hierarchy of a responsibility
- Query to find details of Flex value sets
- Query to find concurrent program Responsibility and Request Group
- Concurrent program SQL statement currently running in background
- Query to find scheduled concurrent programs
- Query to kill database session for a concurrent request
- Query to get DFF (Descriptive Flex Field) Details