REM
REM DBAToolZ NOTE:
REM	This script was obtained from DBAToolZ.com
REM	It's configured to work with SQL Directory (SQLDIR).
REM	SQLDIR is a utility that allows easy organization and
REM	execution of SQL*Plus scripts using user-friendly menu.
REM	Visit DBAToolZ.com for more details and free SQL scripts.
REM
REM
REM File:
REM 	a_conc_prog_grp_e_R11.sql
REM
REM <SQLDIR_GRP>APPS_CONC_PROG</SQLDIR_GRP>
REM
REM Author:
REM 	Vitaliy Mogilevskiy (vit100gain@earthlink.net)
REM
REM Purpose:
REM	<SQLDIR_TXT>
REM	Reports concurrent program definition
REM	and group set it is assigned to. Search is done
REM	by executable name
REM	</SQLDIR_TXT>
REM
REM Usage:
REM	a_conc_prog_grp_e_R11.sql
REM
REM Example:
REM	a_conc_prog_grp_e_R11.sql
REM
REM
REM History:
REM	MM-DD-RRRR	VMOGILEV	Created
REM
REM

set verify off
set feedback off
set serveroutput on size 100000

declare
cursor prog_cur IS
SELECT  'Program User Name: '||   FCPT.USER_CONCURRENT_PROGRAM_NAME  prog_name
,       'Description:       '||   FCPT.DESCRIPTION                   description
,       'Executable Name:   '||   EXE.EXECUTABLE_NAME                exe_name
,       'Executable File:   '||   EXE.EXECUTION_FILE_NAME            exe_file_name
,       'Execution Type:    '||   decode(EXE.EXECUTION_METHOD_CODE,
                        'B', 'Request Set Stage Function',
                        'Q', 'SQL*Plus',
                        'H', 'Host',
                        'L', 'SQL*Loader',
                        'A', 'Spawned',
                        'I', 'PL/SQL Stored Procedure',
                        'P', 'Oracle Reports',
                        'S', 'Immediate',EXE.EXECUTION_METHOD_CODE)  exe_method
,       'Style Req ?:       '||   FND.REQUIRED_STYLE                 style_r
,       'Style Type:        '||   FND.OUTPUT_PRINT_STYLE             style_p
,       'Printer Name:      '||   FND.PRINTER_NAME                   printer_name
,       'Min WIDTH:         '||   FND.MINIMUM_WIDTH                  min_w
,       'Max WIDTH:         '||   FND.MAXIMUM_WIDTH                  max_w
,       'Min LENGHT:        '||   FND.MINIMUM_LENGTH                 min_l
,       'Max LENGHT:        '||   FND.MAXIMUM_LENGTH                 max_l
,       fnd.concurrent_program_id
FROM  APPLSYS.FND_EXECUTABLES             EXE
,     APPLSYS.FND_CONCURRENT_PROGRAMS     FND
,     APPLSYS.FND_CONCURRENT_PROGRAMS_TL  FCPT
WHERE EXE.EXECUTION_FILE_NAME    = '&EXE_NAME'
AND   EXE.EXECUTABLE_ID          = FND.EXECUTABLE_ID
AND   FND.CONCURRENT_PROGRAM_ID  = FCPT.CONCURRENT_PROGRAM_ID
AND   FND.APPLICATION_ID         = FCPT.APPLICATION_ID
ORDER BY 1;

cursor group_cur (p_conc_prog_id   NUMBER) IS
select FRG.REQUEST_GROUP_NAME r_g_name
FROM  APPLSYS.FND_REQUEST_GROUPS          FRG
,     APPLSYS.FND_REQUEST_GROUP_UNITS     FRGU
where FRGU.REQUEST_UNIT_ID       = p_conc_prog_id
AND   FRGU.REQUEST_GROUP_ID      = FRG.REQUEST_GROUP_ID;

begin
    for prog_rec in prog_cur
    LOOP
       dbms_output.put_line(prog_rec.prog_name);
       dbms_output.put_line(prog_rec.description);
       dbms_output.put_line(prog_rec.exe_name);
       dbms_output.put_line(prog_rec.exe_file_name);
       dbms_output.put_line(prog_rec.exe_method);
       dbms_output.put_line(prog_rec.style_r);
       dbms_output.put_line(prog_rec.style_p);
       dbms_output.put_line(prog_rec.printer_name);
       dbms_output.put_line(prog_rec.min_w);
       dbms_output.put_line(prog_rec.max_w);
       dbms_output.put_line(prog_rec.min_l);
       dbms_output.put_line(prog_rec.max_l);
       dbms_output.put_line('---- Request Group Name ----');
       for group_rec in group_cur(prog_rec.concurrent_program_id)
       LOOP
          dbms_output.put_line(group_rec.r_g_name);
       END LOOP;
       dbms_output.put_line('-----------------------------------------');
       dbms_output.put_line('.');
    END LOOP;
end;
/