SELECT FF.USER_FUNCTION_NAME,
FM.MENU_NAME,
FR.RESPONSIBILITY_NAME,
FU.USER_NAME
FROM (SELECT SUBSTR(PATH, 2, (INSTR(PATH, '/', 2) - 2)) MENU_ID,
FUNCTION_ID
FROM (SELECT MENU_ID,
SUB_MENU_ID,
FUNCTION_ID,
LEVEL,
SYS_CONNECT_BY_PATH(MENU_ID, '/') PATH
FROM FND_MENU_ENTRIES
WHERE FUNCTION_ID IN (SELECT FUNCTION_ID
FROM FND_FORM_FUNCTIONS_VL
WHERE USER_FUNCTION_NAME IN
('Responsibilities', 'Users',
'Menus', 'Functions')
AND TYPE = 'FORM')
START WITH MENU_ID IN
(SELECT DISTINCT (MENU_ID)
FROM FND_RESPONSIBILITY
WHERE NVL(END_DATE, SYSDATE) >= SYSDATE)
CONNECT BY PRIOR SUB_MENU_ID = MENU_ID
ORDER SIBLINGS BY MENU_ID)
GROUP BY SUBSTR(PATH, 2, (INSTR(PATH, '/', 2) - 2)), FUNCTION_ID) A,
FND_MENUS FM,
FND_RESPONSIBILITY_VL FR,
FND_FORM_FUNCTIONS_VL FF,
FND_USER_RESP_GROUPS_DIRECT FUR,
FND_USER FU
WHERE FM.MENU_ID = A.MENU_ID
AND A.MENU_ID = FR.MENU_ID
AND FF.FUNCTION_ID = A.FUNCTION_ID
AND FUR.RESPONSIBILITY_ID = FR.RESPONSIBILITY_ID
AND FU.USER_ID = FUR.USER_ID
AND ((FF.FUNCTION_ID,FR.RESPONSIBILITY_ID) NOT IN
(SELECT ACTION_ID,RESPONSIBILITY_ID
FROM FND_RESP_FUNCTIONS
WHERE RULE_TYPE IN ('F','M')))
FM.MENU_NAME,
FR.RESPONSIBILITY_NAME,
FU.USER_NAME
FROM (SELECT SUBSTR(PATH, 2, (INSTR(PATH, '/', 2) - 2)) MENU_ID,
FUNCTION_ID
FROM (SELECT MENU_ID,
SUB_MENU_ID,
FUNCTION_ID,
LEVEL,
SYS_CONNECT_BY_PATH(MENU_ID, '/') PATH
FROM FND_MENU_ENTRIES
WHERE FUNCTION_ID IN (SELECT FUNCTION_ID
FROM FND_FORM_FUNCTIONS_VL
WHERE USER_FUNCTION_NAME IN
('Responsibilities', 'Users',
'Menus', 'Functions')
AND TYPE = 'FORM')
START WITH MENU_ID IN
(SELECT DISTINCT (MENU_ID)
FROM FND_RESPONSIBILITY
WHERE NVL(END_DATE, SYSDATE) >= SYSDATE)
CONNECT BY PRIOR SUB_MENU_ID = MENU_ID
ORDER SIBLINGS BY MENU_ID)
GROUP BY SUBSTR(PATH, 2, (INSTR(PATH, '/', 2) - 2)), FUNCTION_ID) A,
FND_MENUS FM,
FND_RESPONSIBILITY_VL FR,
FND_FORM_FUNCTIONS_VL FF,
FND_USER_RESP_GROUPS_DIRECT FUR,
FND_USER FU
WHERE FM.MENU_ID = A.MENU_ID
AND A.MENU_ID = FR.MENU_ID
AND FF.FUNCTION_ID = A.FUNCTION_ID
AND FUR.RESPONSIBILITY_ID = FR.RESPONSIBILITY_ID
AND FU.USER_ID = FUR.USER_ID
AND ((FF.FUNCTION_ID,FR.RESPONSIBILITY_ID) NOT IN
(SELECT ACTION_ID,RESPONSIBILITY_ID
FROM FND_RESP_FUNCTIONS
WHERE RULE_TYPE IN ('F','M')))
No comments:
Post a Comment