Oracle EBS Profile Values for any level

If you get curios which level has a profile value, that’s the answer.

SELECT SUBSTR (E.PROFILE_OPTION_NAME, 1, 25) PROFILE_NAME,
SUBSTR (POT.USER_PROFILE_OPTION_NAME, 1, 60) USER_PROFILE_NAME,
DECODE (A.LEVEL_ID,
10001, 'Site',
10002, 'Application',
10003, 'Responsibility',
10004, 'User',
10006, 'Organization',
10005, 'Server',
10007, 'Server+Resp',
A.LEVEL_ID)
WHICH_LEVEL,
DECODE (A.LEVEL_ID,
10001, 'Site',
10002, C.APPLICATION_SHORT_NAME,
10003, B.RESPONSIBILITY_NAME,
10004, D.USER_NAME,
10005, N.NODE_NAME,
10006, HAOU.NAME,
10007, M.NODE_NAME || ' + ' || B.RESPONSIBILITY_NAME,
A.LEVEL_ID)
LEVEL_VALUE,
NVL (A.PROFILE_OPTION_VALUE, 'Is Null') PROFILE_VALUE,
TO_CHAR (A.LAST_UPDATE_DATE, 'DD-MON-YYYY HH24:MI') LAST_UPDATE_DATE,
DD.USER_NAME LAST_UPDATE_USER
FROM APPLSYS.FND_PROFILE_OPTION_VALUES A,
APPLSYS.FND_RESPONSIBILITY_TL B,
APPLSYS.FND_APPLICATION C,
APPLSYS.FND_USER D,
APPLSYS.FND_PROFILE_OPTIONS E,
APPLSYS.FND_NODES N,
APPLSYS.FND_NODES M,
APPLSYS.FND_RESPONSIBILITY_TL X,
APPLSYS.FND_USER DD,
APPLSYS.FND_PROFILE_OPTIONS_TL POT,
APPS.HR_ALL_ORGANIZATION_UNITS_VL HAOU
WHERE 1 = 1
AND E.PROFILE_OPTION_NAME = '&XXXXXX'
AND E.PROFILE_OPTION_NAME = POT.PROFILE_OPTION_NAME(+)
AND E.PROFILE_OPTION_ID = A.PROFILE_OPTION_ID(+)
AND A.LEVEL_VALUE = B.RESPONSIBILITY_ID(+)
AND A.LEVEL_VALUE = C.APPLICATION_ID(+)
AND A.LEVEL_VALUE = D.USER_ID(+)
AND A.LEVEL_VALUE = N.NODE_ID(+)
AND A.LEVEL_VALUE = HAOU.ORGANIZATION_ID(+)
AND A.LEVEL_VALUE_APPLICATION_ID = X.RESPONSIBILITY_ID(+)
AND A.LEVEL_VALUE2 = M.NODE_ID(+)
AND A.LAST_UPDATED_BY = DD.USER_ID(+)
AND POT.LANGUAGE = 'US'
ORDER BY E.PROFILE_OPTION_NAME

Leave a Reply

Your email address will not be published. Required fields are marked *