add

About Me

My photo
Oracle Apps - Techno Functional consultant

Thursday, February 16

TO Retrieve the Entered USER Concurrent Programs,Responsibilities, Request Groups, Menus

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 FCPT.USER_CONCURRENT_PROGRAM_NAME LIKE '%&CONCU_NAME%'
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: