add

About Me

My photo
Oracle Apps - Techno Functional consultant

Thursday, September 29

Return which functions a user can access

SELECT
FU.USER_NAME,
FRTL.RESPONSIBILITY_NAME,
FFL.USER_FUNCTION_NAME,
FFF.FUNCTION_NAME
FROM
FND_USER FU,
FND_USER_RESP_GROUPS FURG,
FND_RESPONSIBILITY FR,
FND_COMPILED_MENU_FUNCTIONS FCMF,
FND_FORM_FUNCTIONS FFF,
FND_RESPONSIBILITY_TL FRTL,
FND_FORM_FUNCTIONS_TL FFL
WHERE
FURG.RESPONSIBILITY_ID = FR.RESPONSIBILITY_ID
AND FURG.RESPONSIBILITY_APPLICATION_ID = FR.APPLICATION_ID
AND FR.MENU_ID = FCMF.MENU_ID
AND FCMF.GRANT_FLAG = 'Y'
AND FCMF.FUNCTION_ID = FFF.FUNCTION_ID
AND FURG.USER_ID = FU.USER_ID
AND SYSDATE BETWEEN FU.START_DATE AND NVL(FU.END_DATE, SYSDATE+1)
AND SYSDATE BETWEEN FR.START_DATE AND NVL(FR.END_DATE, SYSDATE+1)
AND FURG.RESPONSIBILITY_ID = FRTL.RESPONSIBILITY_ID
AND FR.RESPONSIBILITY_ID = FRTL.RESPONSIBILITY_ID
AND FRTL.LANGUAGE = 'US'
AND FFL.LANGUAGE = 'US'
AND FFF.FUNCTION_ID = FFL.FUNCTION_ID
AND
(
FURG.END_DATE > SYSDATE
OR FURG.END_DATE IS NULL
)
AND FU.USER_NAME = 'USERNAME_TO_BE_CHECKED'
AND FRTL.RESPONSIBILITY_NAME = 'CURRENT_RESPONSIBILITY'
AND FFF.FUNCTION_NAME NOT IN
(
SELECT
FF.FUNCTION_NAME
FROM
FND_RESPONSIBILITY R,
FND_USER_RESP_GROUPS RG,
FND_USER U,
FND_RESP_FUNCTIONS RF,
FND_FORM_FUNCTIONS FF,
FND_RESPONSIBILITY_TL FRTL
WHERE
RG.RESPONSIBILITY_ID = R.RESPONSIBILITY_ID
AND U.USER_ID = RG.USER_ID
AND RF.RESPONSIBILITY_ID = R.RESPONSIBILITY_ID
AND RF.RULE_TYPE = 'F'
AND FF.FUNCTION_ID = RF.ACTION_ID
AND FRTL.RESPONSIBILITY_ID = R.RESPONSIBILITY_ID
AND FRTL.RESPONSIBILITY_ID = RG.RESPONSIBILITY_ID
AND FRTL.LANGUAGE = 'US'
AND U.USER_NAME = UPPER('USERNAME_TO_BE_CHECKED')
AND FRTL.RESPONSIBILITY_NAME = 'CURRENT_RESPONSIBILITY'
)
AND FFF.FUNCTION_NAME NOT IN
(
SELECT
FUNCTION_NAME
FROM
(
SELECT DISTINCT
(
SELECT
FUNCTION_NAME
FROM
FND_FORM_FUNCTIONS F
WHERE
F.FUNCTION_ID = ME.FUNCTION_ID
)
FUNCTION_NAME,
MENU_ID
FROM
FND_MENU_ENTRIES ME START
WITH ME.MENU_ID IN
(
SELECT
RF.ACTION_ID
FROM
FND_RESPONSIBILITY R,
FND_USER_RESP_GROUPS RG,
FND_USER U,
FND_RESP_FUNCTIONS RF,
FND_RESPONSIBILITY_TL FRTL
WHERE
RG.RESPONSIBILITY_ID = R.RESPONSIBILITY_ID
AND U.USER_ID = RG.USER_ID
AND RF.RESPONSIBILITY_ID = R.RESPONSIBILITY_ID
AND RF.RULE_TYPE = 'M'
AND FRTL.RESPONSIBILITY_ID = R.RESPONSIBILITY_ID
AND FRTL.RESPONSIBILITY_ID = RF.RESPONSIBILITY_ID
AND U.USER_NAME = UPPER('USERNAME_TO_BE_CHECKED')
AND FRTL.RESPONSIBILITY_NAME = 'CURRENT_RESPONSIBILITY'
)
CONNECT BY ME.MENU_ID = PRIOR ME.SUB_MENU_ID
)
WHERE
FUNCTION_NAME IS NOT NULL
)
ORDER BY
1,2,3

No comments: