Oracle EBS find profile option values using SQL query
SELECT FPO.USER_PROFILE_OPTION_NAME,
FPO.PROFILE_OPTION_NAME,
DECODE (FPOV.LEVEL_ID,
10001, ‘Site’,
10002, ‘Application’,
10003, ‘Responsibility’,
10004, ‘User’,
10005, ‘Server’,
10006, ‘Organization’,
‘Unknown’)
AS PROFILE_LEVEL,
DECODE (FPOV.level_id,
10001, ‘Site’,
10002, FA.application_short_name,
10003, FR.RESPONSIBILITY_NAME,
10004, FU.USER_NAME,
10005, FN.NODE_NAME,
10006, HOU.name,
‘Unknown’)
AS Level_Value,
FPOV.PROFILE_OPTION_VALUE
FROM apps.FND_PROFILE_OPTIONS_VL FPO,
apps.FND_PROFILE_OPTION_VALUES FPOV,
apps.FND_USER FU,
apps.FND_RESPONSIBILITY_VL FR,
apps.FND_APPLICATION FA,
apps.FND_NODES FN,
apps.HR_OPERATING_UNITS HOU
WHERE FPO.PROFILE_OPTION_ID = FPOV.PROFILE_OPTION_ID
AND FPO.APPLICATION_ID = FPOV.APPLICATION_ID
AND FPOV.LEVEL_VALUE = FU.USER_ID(+) — details on user profile option
AND FPOV.LEVEL_VALUE = FN.NODE_ID(+) — details on server profile option
AND TO_CHAR (FPOV.LEVEL_VALUE) = FN.NODE_NAME(+) — dummy where, see code for more info
AND FPOV.LEVEL_VALUE = FR.RESPONSIBILITY_ID(+) — details on responsibility profile option
AND FPOV.LEVEL_VALUE = FA.APPLICATION_ID(+) — details on application profile option
AND FPOV.LEVEL_VALUE = HOU.ORGANIZATION_ID(+) — details on organization profile option
ORDER BY FPO.PROFILE_OPTION_NAME,
DECODE (FPOV.LEVEL_ID,
10001, ‘Site’,
10002, ‘Application’,
10003, ‘Responsibility’,
10004, ‘User’,
10005, ‘Server’,
10006, ‘Organization’,
‘Unknown’),
DECODE (FPOV.level_id,
10001, ‘Site’,
10002, FA.application_short_name,
10003, FR.RESPONSIBILITY_NAME,
10004, FU.USER_NAME,
10005, FN.NODE_NAME,
10006, HOU.name,
‘Unknown’),
FPOV.PROFILE_OPTION_VALUE;