add

About Me

My photo
Oracle Apps - Techno Functional consultant

Monday, July 2

Query to Retrieve the User Concurrent Progrms, Responsibilities, Menus, Request Groups

SELECT DISTINCT
          USR.USER_NAME ,
          EMP.FULL_NAME ,
          HRO.name ,
        --  FAT.APPLICATION_ID ,
          FAT.APPLICATION_NAME ,
        --  FCP.CONCURRENT_PROGRAM_ID ,
          FCP.CONCURRENT_PROGRAM_NAME ,
          FCPT.USER_CONCURRENT_PROGRAM_NAME ,
        --  FRV.RESPONSIBILITY_ID ,
          FRV.RESPONSIBILITY_NAME ,
          FRT.DESCRIPTION RESPONSIBILITY_DESC ,
        --  frg.REQUEST_GROUP_ID ,
          FRG.REQUEST_GROUP_NAME ,
          FRG.DESCRIPTION REQUEST_GROUP_DESCRIPTION ,
        --  FMT.MENU_ID ,
          FMT.USER_MENU_NAME ,
          FMT.DESCRIPTION MENU_DESCRIPTION
FROM
          FND_USER_RESP_GROUPS_ALL URGV ,
          FND_USER USR ,
          PER_ALL_PEOPLE_F EMP ,
          HR_ALL_ORGANIZATION_UNITS_TL HRO ,
          PER_ALL_ASSIGNMENTS_F HRA ,
          APPS.FND_REQUEST_GROUPS FRG ,
          APPS.FND_REQUEST_GROUP_UNITS FRGU ,
          APPS.FND_RESPONSIBILITY_VL FRV ,
          APPS.FND_RESPONSIBILITY_TL FRT ,
          APPS.FND_RESPONSIBILITY FRB ,
          APPS.fnd_menus_tl fmt ,
          APPS.FND_MENUS FMS ,
          APPS.FND_APPLICATION_TL FAT ,
          APPS.FND_APPLICATION FAL ,
          APPS.FND_CONCURRENT_PROGRAMS FCP ,
          APPS.FND_CONCURRENT_PROGRAMS_TL FCPT
WHERE     1 = 1
        AND URGV.RESPONSIBILITY_ID        = FRT.responsibility_id
        AND URGV.USER_ID                  = USR.USER_ID
        AND usr.employee_id               = emp.person_id
        AND usr.end_date                 IS NULL
        AND emp.person_id                 = hra.person_id
        AND HRO.ORGANIZATION_ID           = HRA.ORGANIZATION_ID
        AND HRA.ASSIGNMENT_STATUS_TYPE_ID = 1
        AND FRV.REQUEST_GROUP_ID          = FRG.REQUEST_GROUP_ID
        AND frgu.request_group_id         = frg.request_group_id
        AND FRV.RESPONSIBILITY_ID(+)      = FRB.RESPONSIBILITY_ID
        AND FRT.responsibility_id         = frb.responsibility_id
        AND FRB.MENU_ID                   = FMT.MENU_ID
        AND frb.menu_id                   = fms.menu_id
        AND fat.application_id            = fal.application_id
        AND fal.application_id            = frb.application_id
        AND frgu.request_unit_id          = fcp.concurrent_program_id
        AND FCP.CONCURRENT_PROGRAM_ID     = FCPT.CONCURRENT_PROGRAM_ID
        AND USR.USER_NAME LIKE '%&USERNAME%'

ORDER BY
      USR.USER_NAME ,
      FAT.APPLICATION_NAME ,
      FCPT.USER_CONCURRENT_PROGRAM_NAME ,
      FRV.RESPONSIBILITY_NAME ,
      FRG.REQUEST_GROUP_NAME ,
      FMT.USER_MENU_NAME ;

No comments: