To retrieve the user concurrent programs, responsibilities, menus, request groups details

To retrieve the user concurrent programs, responsibilities, menus, request groups details

Modules Involved: Application object library(AOL)
Purpose         : To retrieve the user concurrent programs, responsibilities, menus, request groups details
Description     : This query needs one parameter(User Name).
****************************************************************** */
SELECT DISTINCT
  USR.USER_NAME ,
  EMP.FULL_NAME ,
  HRO.name ,
  FAT.APPLICATION_NAME ,
  FRV.RESPONSIBILITY_NAME ,
  FRT.DESCRIPTION RESPONSIBILITY_DESC ,
  FRG.REQUEST_GROUP_NAME ,
  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 = :User_Name