Tuesday, 29 April 2014

To find the function attached to the Responsibility and User

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')))

No comments:

Post a Comment