Query to find all the responsibility names from which we can run a particular concurrent program

Query to find all the responsibility names from which we can run a particular concurrent program

The below query lists all the responsibilities from which we can run a particular concurrent program as well as lists all the concurrent programs which you can run from a particular responsibility.

/* *******************************************************************
Modules Involved: Application object library (AOL)
Purpose         : To extract Concurrent Program, Responsibility and Request Group Info.
Description     : We can find the list of Responsibilities from which we can run the specified concurrent program.

This query need any one of the 2 parameters (Conc_Prog_Name, Responsibility_Name):
1)If you pass Conc_Prog_Name, You will get all the Responsibilities from which you can run that Program.
2)If you pass Responsibility_Name, You will get all the Concurrent Programs which you can run from that responsibility.
****************************************************************** */

SELECT fcpt.user_concurrent_program_name,
  frg.request_group_name,
  fcp.concurrent_program_name,
  FRT.RESPONSIBILITY_NAME,
  FAT.APPLICATION_NAME,
  FE.EXECUTABLE_NAME "EXECUTABLE CODE",
  FL.MEANING "EXECUTION METHOD",
  FE.EXECUTION_FILE_NAME
FROM
  APPS.FND_REQUEST_GROUP_UNITS FRGU,
  APPS.FND_CONCURRENT_PROGRAMS FCP,
  APPS.FND_CONCURRENT_PROGRAMS_TL FCPT,
  APPS.FND_REQUEST_GROUPS FRG,
  APPS.FND_EXECUTABLES FE,
  APPS.FND_RESPONSIBILITY FR,
  APPS.FND_RESPONSIBILITY_TL FRT,
  APPS.FND_APPLICATION_TL FAT,
  APPS.FND_LOOKUPS FL
WHERE 1                       = 1
AND fat.application_id        = frgu.application_id
AND frgu.request_unit_id      = fcp.concurrent_program_id
AND frgu.request_group_id     = frg.request_group_id
AND fe.executable_id          = fcp.executable_id
AND fcp.concurrent_program_id = fcpt.concurrent_program_id
AND frg.request_group_id      = fr.request_group_id
AND FR.RESPONSIBILITY_ID      = FRT.RESPONSIBILITY_ID
and FCPT.USER_CONCURRENT_PROGRAM_NAME = NVL(:CONC_PROG_NAME, FCPT.USER_CONCURRENT_PROGRAM_NAME)
and FRT.RESPONSIBILITY_NAME = NVL(:RESPONSIBILITY_NAME,FRT.RESPONSIBILITY_NAME)
and FL.LOOKUP_TYPE = 'CP_EXECUTION_METHOD_CODE'
and FE.EXECUTION_METHOD_CODE = FL.LOOKUP_CODE
;