用户职责菜单请求组

--1、用户职责查询
select fu.user_id,
       fu.user_name,
       ppf.FULL_NAME,
       fr.RESPONSIBILITY_ID,
       FR.RESPONSIBILITY_KEY,
       FR.RESPONSIBILITY_NAME,
       FU.START_DATE,
       FU.END_DATE
  from fnd_responsibility_vl    fr,
       fnd_user_resp_groups_all fur,
       fnd_user                 fu,
       per_people_f             ppf
 where fr.RESPONSIBILITY_ID = fur.RESPONSIBILITY_ID
   and fur.user_id = fu.user_id
   and fu.employee_id = ppf.PERSON_ID(+)
   and ppf.EFFECTIVE_END_DATE(+) >= sysdate
 order by 1;
--2、职责菜单请求组查询
select fr.RESPONSIBILITY_ID,
       fr.RESPONSIBILITY_KEY,
       fr.RESPONSIBILITY_NAME,
       fr.APPLICATION_ID,
       fa.APPLICATION_NAME,
       frg.request_group_id,
       frg.request_group_code,
       frg.request_group_name,
       fm.MENU_ID,
       fm.MENU_NAME
  from fnd_responsibility_vl fr,
       Fnd_Request_Groups    frg,
       fnd_menus_vl          fm,
       fnd_application_vl    fa
 where fr.REQUEST_GROUP_ID = frg.request_group_id
   and fm.MENU_ID = fr.MENU_ID
   and fa.APPLICATION_ID = fr.APPLICATION_ID;
--3、菜单明细查询
select fm.MENU_ID,
       FM.MENU_NAME,
       FM.USER_MENU_NAME,
       FM.TYPE,
       FM.DESCRIPTION,
       FME.ENTRY_SEQUENCE,
       FME.PROMPT,
       (select fm1.USER_MENU_NAME
          from FND_MENUS_VL fm1
         where fm1.MENU_ID = FME.SUB_MENU_ID) SUB_MENU,
       (select fff.USER_FUNCTION_NAME
          from FND_FORM_FUNCTIONS_VL fff
         where fff.FUNCTION_ID = FME.FUNCTION_ID) FUNCTION,
       FME.DESCRIPTION,
       FME.GRANT_FLAG
  from FND_MENUS_VL fm, FND_MENU_ENTRIES_VL fme
 where FM.MENU_ID = FME.MENU_ID;

--4、请求组明细查询
select frg.request_group_id,
       FRG.REQUEST_GROUP_NAME ,
       fav.APPLICATION_NAME ,
       FRG.REQUEST_GROUP_CODE ,
       FRG.DESCRIPTION ,
       decode(FRGU.REQUEST_UNIT_TYPE,
              'P',
              '程序',
              'S',
              '请求集',
              'A',
              '应用') TYPE,
       cp.USER_CONCURRENT_PROGRAM_NAME ,
       fav1.APPLICATION_NAME 
  from FND_REQUEST_GROUPS         FRG,
       FND_REQUEST_GROUP_UNITS    FRGU,
       FND_APPLICATION_VL         FAV,
       FND_APPLICATION_VL         FAV1,
       FND_CONCURRENT_PROGRAMS_VL cp --程序
 where FRG.REQUEST_GROUP_ID = FRGU.REQUEST_GROUP_ID
   and frgu.application_id = fav1.APPLICATION_ID
   and frg.application_id = fav.APPLICATION_ID
   and frgu.request_unit_type = 'P'
   and cp.CONCURRENT_PROGRAM_ID(+) = frgu.request_unit_id
-- AND FRG.REQUEST_GROUP_NAME like 'JBJT%'
union all
select frg.request_group_id,
       FRG.REQUEST_GROUP_NAME 请求组,
       fav.APPLICATION_NAME 应用,
       FRG.REQUEST_GROUP_CODE 请求组代码,
       FRG.DESCRIPTION 请求组描述,
       decode(FRGU.REQUEST_UNIT_TYPE,
              'P',
              '程序',
              'S',
              '请求集',
              'A',
              '应用') 类型,
       rs.USER_REQUEST_SET_NAME 名称,
       fav1.APPLICATION_NAME 应用
  from FND_REQUEST_GROUPS      FRG,
       FND_REQUEST_GROUP_UNITS FRGU,
       FND_APPLICATION_VL      FAV,
       FND_APPLICATION_VL      FAV1,
       FND_REQUEST_SETS_VL     rs --请求集
 where FRG.REQUEST_GROUP_ID = FRGU.REQUEST_GROUP_ID
   and frgu.application_id = fav1.APPLICATION_ID
   and frg.application_id = fav.APPLICATION_ID
   AND FRGU.REQUEST_UNIT_TYPE = 'S'
   AND rs.REQUEST_SET_ID(+) = frgu.request_unit_id
union all
select frg.request_group_id,
       FRG.REQUEST_GROUP_NAME 请求组,
       fav.APPLICATION_NAME 应用,
       FRG.REQUEST_GROUP_CODE 请求组代码,
       FRG.DESCRIPTION 请求组描述,
       decode(FRGU.REQUEST_UNIT_TYPE,
              'P',
              '程序',
              'S',
              '请求集',
              'A',
              '应用') 类型,
       FAV2.APPLICATION_NAME 名称,
       fav1.APPLICATION_NAME 应用
  from FND_REQUEST_GROUPS      FRG,
       FND_REQUEST_GROUP_UNITS FRGU,
       FND_APPLICATION_VL      FAV,
       FND_APPLICATION_VL      FAV1,
       FND_APPLICATION_VL      FAV2 --应用
 where FRG.REQUEST_GROUP_ID = FRGU.REQUEST_GROUP_ID
   and frgu.application_id = fav1.APPLICATION_ID
   and frg.application_id = fav.APPLICATION_ID
   AND FRGU.REQUEST_UNIT_TYPE = 'A'
   AND FAV2.APPLICATION_ID(+) = frgu.request_unit_id
 ORDER BY 1, 5, 6