-- Get profile option value
at all levels for profile option name ORG_ID
SELECT
PO.PROFILE_OPTION_NAME "NAME", PO.USER_PROFILE_OPTION_NAME,
DECODE (TO_CHAR (POV.LEVEL_ID),
'10001', 'SITE',
'10002', 'APP',
'10003', 'RESP',
'10005', 'SERVER',
'10006', 'ORG',
'10004', 'USER',
'???'
) "LEVEL",
DECODE (TO_CHAR (POV.LEVEL_ID),
'10001', '',
'10002', APP.APPLICATION_SHORT_NAME,
'10003', RSP.RESPONSIBILITY_NAME,
'10005', SVR.NODE_NAME,
'10006', ORG.NAME,
'10004', USR.USER_NAME,
'???'
) "CONTEXT",
POV.PROFILE_OPTION_VALUE "VALUE"
FROM FND_PROFILE_OPTIONS_VL PO,
FND_PROFILE_OPTION_VALUES POV,
FND_USER USR,
FND_APPLICATION APP,
FND_RESPONSIBILITY_VL RSP,
FND_NODES SVR,
HR_OPERATING_UNITS ORG
WHERE 1 = 1
AND POV.APPLICATION_ID = PO.APPLICATION_ID
AND POV.PROFILE_OPTION_ID = PO.PROFILE_OPTION_ID
AND USR.USER_ID(+) = POV.LEVEL_VALUE
AND RSP.APPLICATION_ID(+) = POV.LEVEL_VALUE_APPLICATION_ID
AND RSP.RESPONSIBILITY_ID(+) = POV.LEVEL_VALUE
AND APP.APPLICATION_ID(+) = POV.LEVEL_VALUE
AND SVR.NODE_ID(+) = POV.LEVEL_VALUE
AND ORG.ORGANIZATION_ID(+) = POV.LEVEL_VALUE
AND PO.PROFILE_OPTION_NAME = 'ORG_ID'
ORDER BY "NAME", POV.LEVEL_ID, "VALUE";
SELECT FPO.PROFILE_OPTION_ID,
FPOT.PROFILE_OPTION_NAME
PROFILE_SHORT_NAME,
SUBSTR (FPOT.USER_PROFILE_OPTION_NAME, 1, 60) PROFILE_NAME,
DECODE (FPOV.LEVEL_ID,
10001, 'site',
10002, 'Appl',
10003, 'Resp',
10004, 'User')
PROFILE_LEVEL,
SUBSTR (
DECODE (FPOV.LEVEL_ID,
10001, NULL,
10002, FA.APPLICATION_SHORT_NAME,
10003, FR.RESPONSIBILITY_NAME,
10004, FU.USER_NAME),
1,
30)
LEVEL_VALUE,
FPOV.PROFILE_OPTION_VALUE
PROFILE_VALUE
FROM FND_PROFILE_OPTION_VALUES
FPOV,
FND_PROFILE_OPTIONS FPO,
FND_PROFILE_OPTIONS_TL FPOT,
FND_APPLICATION FA,
FND_RESPONSIBILITY_TL FR,
FND_USER FU
WHERE 1 = 1
AND FPO.PROFILE_OPTION_NAME = FPOT.PROFILE_OPTION_NAME
AND FPO.PROFILE_OPTION_ID = FPOV.PROFILE_OPTION_ID
AND FA.APPLICATION_ID(+) = FPOV.LEVEL_VALUE
AND FR.RESPONSIBILITY_ID(+) = FPOV.LEVEL_VALUE
AND FU.USER_ID(+) = FPOV.LEVEL_VALUE
AND FPO.PROFILE_OPTION_NAME LIKE 'PO%';
/*
and (fpo.profile_option_name
like nvl('X',fpo.profile_option_name) -- Not mandatory. Replace X with profile
short name, ie 'ORG_ID'
or
fpot.user_profile_option_name like nvl('MO:
Op%',fpot.user_profile_option_name)) -- Not mandatory. Replace Y with profile
user name, ie 'MO: Op%'
*/
SELECT SUBSTR (PRO1.USER_PROFILE_OPTION_NAME, 1, 35) PROFILE,
DECODE (POV.LEVEL_ID,
10001, 'Site',
10002, 'Application',
10003, 'Resp',
10004, 'User')
OPTION_LEVEL,
DECODE (POV.LEVEL_ID,
10001, 'Site',
10002, APPL.APPLICATION_SHORT_NAME,
10003, RESP.RESPONSIBILITY_NAME,
10004, U.USER_NAME)
LEVEL_VALUE,
NVL (POV.PROFILE_OPTION_VALUE, 'Is Null') PROFILE_OPTION_VALUE
FROM FND_PROFILE_OPTION_VALUES
POV,
FND_RESPONSIBILITY_TL RESP,
FND_APPLICATION APPL,
FND_USER U,
FND_PROFILE_OPTIONS PRO,
FND_PROFILE_OPTIONS_TL PRO1
WHERE PRO1.USER_PROFILE_OPTION_NAME
LIKE ('%Ledger%')
AND PRO.PROFILE_OPTION_NAME = PRO1.PROFILE_OPTION_NAME
AND PRO.PROFILE_OPTION_ID = POV.PROFILE_OPTION_ID
AND RESP.RESPONSIBILITY_NAME LIKE '%General%Ledger%'
/* comment
this line if you need to check profiles
for all responsibilities */
AND POV.LEVEL_VALUE = RESP.RESPONSIBILITY_ID(+)
AND POV.LEVEL_VALUE = APPL.APPLICATION_ID(+)
AND POV.LEVEL_VALUE = U.USER_ID(+)
ORDER BY 1, 2;
SELECT SUBSTR (PRO1.USER_PROFILE_OPTION_NAME, 1, 35) PROFILE,
DECODE (POV.LEVEL_ID,
10001, 'Site',
10002, 'Application',
10003, 'Resp',
10004, 'User')
OPTION_LEVEL,
DECODE (POV.LEVEL_ID,
10001, 'Site',
10002, APPL.APPLICATION_SHORT_NAME,
10003, RESP.RESPONSIBILITY_NAME,
10004, U.USER_NAME)
LEVEL_VALUE,
NVL (POV.PROFILE_OPTION_VALUE, 'Is Null') PROFILE_OPTION_VALUE
FROM FND_PROFILE_OPTION_VALUES
POV,
FND_RESPONSIBILITY_TL RESP,
FND_APPLICATION APPL,
FND_USER U,
FND_PROFILE_OPTIONS PRO,
FND_PROFILE_OPTIONS_TL PRO1
WHERE PRO.PROFILE_OPTION_NAME
= PRO1.PROFILE_OPTION_NAME
AND PRO.PROFILE_OPTION_ID = POV.PROFILE_OPTION_ID
AND RESP.RESPONSIBILITY_NAME LIKE '%General%Ledger%'
AND POV.LEVEL_VALUE = RESP.RESPONSIBILITY_ID(+)
AND POV.LEVEL_VALUE = APPL.APPLICATION_ID(+)
AND POV.LEVEL_VALUE = U.USER_ID(+)
ORDER BY 1, 2;
SELECT P.PROFILE_OPTION_NAME
SHORT_NAME,
N.USER_PROFILE_OPTION_NAME NAME,
DECODE (V.LEVEL_ID,
10001, 'Site',
10002, 'Application',
10003, 'Responsibility',
10004, 'User')
LEVEL_SET,
DECODE (TO_CHAR (V.LEVEL_ID),
'10001', '',
'10002', APP.APPLICATION_SHORT_NAME,
'10003', RSP.RESPONSIBILITY_KEY,
'10004', USR.USER_NAME)
"CONTEXT",
V.PROFILE_OPTION_VALUE VALUE
FROM FND_PROFILE_OPTIONS P,
FND_PROFILE_OPTION_VALUES V,
FND_PROFILE_OPTIONS_TL N,
FND_USER USR,
FND_APPLICATION APP,
FND_RESPONSIBILITY RSP,
FND_NODES SVR,
HR_OPERATING_UNITS ORG
WHERE P.PROFILE_OPTION_ID = V.PROFILE_OPTION_ID(+)
AND P.PROFILE_OPTION_NAME = N.PROFILE_OPTION_NAME
AND UPPER (N.USER_PROFILE_OPTION_NAME) LIKE UPPER ('%&profile_name%')
AND USR.USER_ID(+) = V.LEVEL_VALUE
AND RSP.APPLICATION_ID(+) = V.LEVEL_VALUE_APPLICATION_ID
AND RSP.RESPONSIBILITY_ID(+) = V.LEVEL_VALUE
AND APP.APPLICATION_ID(+) = V.LEVEL_VALUE
AND SVR.NODE_ID(+) = V.LEVEL_VALUE
AND ORG.ORGANIZATION_ID(+) = V.LEVEL_VALUE
ORDER BY SHORT_NAME, LEVEL_SET;
SELECT B.USER_PROFILE_OPTION_NAME "Long Name",
A.PROFILE_OPTION_NAME "Short Name",
DECODE (TO_CHAR (C.LEVEL_ID),
'10001', 'Site',
'10002', 'Application',
'10003', 'Responsibility',
'10004', 'User',
'Unknown')
"Level",
DECODE (
TO_CHAR (C.LEVEL_ID),
'10001', 'Site',
'10002', NVL (H.APPLICATION_SHORT_NAME, TO_CHAR (C.LEVEL_VALUE)),
'10003', NVL (G.RESPONSIBILITY_NAME, TO_CHAR (C.LEVEL_VALUE)),
'10004', NVL (E.USER_NAME, TO_CHAR (C.LEVEL_VALUE)),
'Unknown')
"Level Value",
C.PROFILE_OPTION_VALUE "Profile Value",
C.PROFILE_OPTION_ID "Profile ID",
TO_CHAR (C.LAST_UPDATE_DATE, 'DD-MON-YYYY HH24:MI') "Updated Date",
NVL (D.USER_NAME, TO_CHAR (C.LAST_UPDATED_BY)) "Updated By"
FROM APPS.FND_PROFILE_OPTIONS A,
APPS.FND_PROFILE_OPTIONS_VL B,
APPS.FND_PROFILE_OPTION_VALUES C,
APPS.FND_USER D,
APPS.FND_USER E,
APPS.FND_RESPONSIBILITY_VL G,
APPS.FND_APPLICATION H
WHERE A.PROFILE_OPTION_NAME
= B.PROFILE_OPTION_NAME
AND A.PROFILE_OPTION_ID = C.PROFILE_OPTION_ID
AND A.APPLICATION_ID = C.APPLICATION_ID
AND C.LAST_UPDATED_BY = D.USER_ID(+)
AND C.LEVEL_VALUE = E.USER_ID(+)
AND C.LEVEL_VALUE = G.RESPONSIBILITY_ID(+)
AND C.LEVEL_VALUE = H.APPLICATION_ID(+)
--and
b.user_profile_option_name LIKE '&ProfileName'
ORDER BY B.USER_PROFILE_OPTION_NAME,
C.LEVEL_ID,
DECODE (
TO_CHAR (C.LEVEL_ID),
'10001', 'Site',
'10002', NVL (H.APPLICATION_SHORT_NAME, TO_CHAR (C.LEVEL_VALUE)),
'10003', NVL (G.RESPONSIBILITY_NAME, TO_CHAR (C.LEVEL_VALUE)),
'10004', NVL (E.USER_NAME, TO_CHAR (C.LEVEL_VALUE)),
'Unknown');