查看菜单&功能

SELECT res.RESPONSIBILITY_NAME 职责名称,

       menu.MENU_NAME 菜单编码,

       menu.USER_MENU_NAME 菜单名称,

       func.FUNCTION_NAME 功能编码,

       func.USER_FUNCTION_NAME 菜单名称,

       sub_menu.PROMPT

  FROM FND_RESPONSIBILITY_VL res,

       fnd_menus_vl          menu,

       FND_MENU_ENTRIES_VL   sub_menu,

       FND_FORM_FUNCTIONS_VL func

 WHERE res.MENU_ID = sub_menu.MENU_ID

   AND menu.MENU_ID = sub_menu.MENU_ID

   AND sub_menu.FUNCTION_ID = func.FUNCTION_ID

   AND res.RESPONSIBILITY_NAME LIKE 'HJSJ%'

   AND sub_menu.SUB_MENU_ID IS NULL

   AND sub_menu.FUNCTION_ID IS NOT NULL

   AND sub_menu.PROMPT IS NOT NULL

UNION ALL

SELECT res.RESPONSIBILITY_NAME,

       menu.MENU_NAME,

       menu.USER_MENU_NAME,

       func.FUNCTION_NAME,

       func.USER_FUNCTION_NAME,

       sub_menu1.PROMPT

  FROM FND_RESPONSIBILITY_VL res,

       fnd_menus_vl          menu,

       FND_MENU_ENTRIES_VL   sub_menu,

       FND_MENU_ENTRIES_VL   sub_menu1,

       FND_FORM_FUNCTIONS_VL func

 WHERE res.MENU_ID = sub_menu.MENU_ID

   AND sub_menu.MENU_ID = menu.MENU_ID

   AND sub_menu.SUB_MENU_ID = sub_menu1.MENU_ID

   AND sub_menu1.FUNCTION_ID = func.FUNCTION_ID

   AND res.RESPONSIBILITY_NAME LIKE 'HJSJ%'

   AND sub_menu.SUB_MENU_ID IS NOT NULL

   AND sub_menu.FUNCTION_ID IS NULL

   AND sub_menu1.PROMPT IS NOT NULL;

查看职责对应请求

SELECT z1 "职责", z2 "请求组", z3 "请求"

  FROM (SELECT res.RESPONSIBILITY_NAME         z1,

               RG.REQUEST_GROUP_NAME           z2,

               RV.USER_CONCURRENT_PROGRAM_NAME z3

          FROM FND_RESPONSIBILITY_VL      res,

               FND_REQUEST_GROUP_UNITS    rgu,

               FND_CONCURRENT_PROGRAMS_VL RV,

               FND_REQUEST_GROUPS         RG

         WHERE rgu.request_unit_type = 'P'

           AND res.CREATION_DATE > to_date('2011-01-01', 'yyyy-mm-dd')

           AND RES.APPLICATION_ID = RGU.APPLICATION_ID

           AND RES.REQUEST_GROUP_ID = RGU.REQUEST_GROUP_ID

           AND RV.APPLICATION_ID = RGU.UNIT_APPLICATION_ID

           AND RV.CONCURRENT_PROGRAM_ID = rgu.request_unit_id

           AND RG.Application_Id = RES.APPLICATION_ID

           AND RG.REQUEST_GROUP_ID = RGU.REQUEST_GROUP_ID

        UNION ALL

        SELECT res.RESPONSIBILITY_NAME z1,

               RG.REQUEST_GROUP_NAME,

               RV.USER_CONCURRENT_PROGRAM_NAME

          FROM FND_RESPONSIBILITY_VL      res,

               FND_REQUEST_GROUP_UNITS    rgu,

               FND_CONCURRENT_PROGRAMS_VL RV,

               FND_REQUEST_GROUPS         RG

         WHERE rgu.request_unit_type = 'A'

           AND res.CREATION_DATE > to_date('2011-01-01', 'yyyy-mm-dd')

           AND RES.APPLICATION_ID = RGU.APPLICATION_ID

           AND RES.REQUEST_GROUP_ID = RGU.REQUEST_GROUP_ID

           AND RV.APPLICATION_ID = RGU.UNIT_APPLICATION_ID

           AND RG.Application_Id = RES.APPLICATION_ID

           AND RG.REQUEST_GROUP_ID = RGU.REQUEST_GROUP_ID

        )

 ORDER BY z1;