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_register.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	Package to generate calls to FND_PROGRAM APIs
REM	which can then be extracted into a sql file
REM	and ran on any other database to register custom
REM	reports including their EXEs, PROGRAM Definitions,
REM	PROGRAM Parameters and register PROGRAM in the group.
REM	</SQLDIR_TXT>
REM
REM Usage:
REM	a_conc_prog_register.sql
REM
REM Example:
REM	a_conc_prog_register.sql
REM
REM
REM History:
REM	08-01-2001	VMOGILEV	Created
REM
REM

DROP TABLE SQL_TEMP CASCADE CONSTRAINTS ;

CREATE TABLE SQL_TEMP (
  SQL_STAGE   VARCHAR2(4),
  LINE_ID     NUMBER(15),
  SQL_STRING  VARCHAR2(300));


CREATE OR REPLACE package vm_apps_program AS
-- ===================================================================================
-- PROGRAM NAME  : VM_APPS_PROGRAM
-- SOURCE NAME   : a_conc_prog_register.sql
-- AUTHOR        : Vitaliy Mogilevskiy / vit100gain@earthlink.net
-- DATE CREATED  : 09/09/1999
-- APPLICATION   : AOL
-- VERSION       : 1.0 (Oracle APPS 11.0.3)
-- DESCRIPTION   : Package to generate calls to FND_PROGRAM APIs
--                 which can then be extracted into a sql file
--                 and ran on any other database to register custom
--                 reports including their EXEs, PROGRAM Definitions,
--                 PROGRAM Parameters and register PROGRAM in the group.
-- EXAMPLE       : execute VM_APPS_PROGRAM('HKIS','HKISRMA','P');
--                                           |     |         |
--                                           |     |         +-> report EXE type
--                                           |     +-----------> report EXEs name
--                                           +-----------------> report EXEs application name
--                 Valid Executable Types:
--                 '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'
-- ===================================================================================
-- MODIFICATION HISTORY
-- ===================================================================================
-- DATE      NAME          DESCRIPTION
-- -----------------------------------------------------------------------------------
-- -----------------------------------------------------------------------------------
PROCEDURE reg_exe(
    p_app_short_name  IN VARCHAR2,
    p_exe_name        IN VARCHAR2,
    p_exe_type        IN VARCHAR2 DEFAULT 'P' );

PROCEDURE reg_program(
    p_exe_id          IN NUMBER,
    p_exe_short_name  IN VARCHAR2,
    p_exe_app_name    IN VARCHAR2 );

PROCEDURE reg_param(
    p_prog_app_id     IN NUMBER,
    p_prog_app_name   IN VARCHAR2,
    p_prog_short_name IN VARCHAR2 );

PROCEDURE reg_in_group(
    p_prog_app_id     IN NUMBER,
    p_prog_app_name   IN VARCHAR2,
    p_prog_short_name IN VARCHAR2,
    p_prog_id         IN NUMBER );

END vm_apps_program;
/

CREATE OR REPLACE package body vm_apps_program AS
-- ===================================================================================
-- PROGRAM NAME  : VM_APPS_PROGRAM
-- SOURCE NAME   : a_conc_prog_register.sql
-- AUTHOR        : Vitaliy Mogilevskiy / vit100gain@earthlink.net
-- DATE CREATED  : 09/09/1999
-- APPLICATION   : AOL
-- VERSION       : 1.0 (Oracle APPS 11.0.3)
-- DESCRIPTION   : Package to generate calls to FND_PROGRAM APIs
--                 which can then be extracted into a sql file
--                 and ran on any other database to register custom
--                 reports including their EXEs, PROGRAM Definitions,
--                 PROGRAM Parameters and register PROGRAM in the group.
-- EXAMPLE       : execute VM_APPS_PROGRAM('HKIS','HKISRMA','P');
--                                           |     |         |
--                                           |     |         +-> report EXE type
--                                           |     +-----------> report EXEs name
--                                           +-----------------> report EXEs application name
--                 Valid Executable Types:
--                 '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'
-- ===================================================================================
-- MODIFICATION HISTORY
-- ===================================================================================
-- DATE      NAME          DESCRIPTION
-- -----------------------------------------------------------------------------------
-- -----------------------------------------------------------------------------------

/* +--------------------------------------------------------+ */
/* +---------------------- PRIVATE VARS --------------------+ */
/* +--------------------------------------------------------+ */
l_line_id        NUMBER(15) := 1;


/* +--------------------------------------------------------+ */
/* +---------------------- PRIVATE MODULES -----------------+ */
/* +--------------------------------------------------------+ */
FUNCTION write_out (
    p_sql_stage   IN VARCHAR2,
    p_line_id     IN NUMBER,
    p_sql_string  IN VARCHAR2) RETURN NUMBER IS

    l_next_line_id NUMBER(15);
BEGIN
    insert into sql_temp values (p_sql_stage, p_line_id, p_sql_string);
    l_next_line_id := p_line_id + 1;
    RETURN l_next_line_id;
END write_out;


/* +--------------------------------------------------------+ */
/* +---------------------- PUBLIC MODULES ------------------+ */
/* +--------------------------------------------------------+ */
PROCEDURE reg_exe(
    p_app_short_name  IN VARCHAR2,
    p_exe_name        IN VARCHAR2,
    p_exe_type        IN VARCHAR2 DEFAULT 'P' ) AS

 CURSOR exe_cur IS
   select fe.executable_id                          e_id
   ,      fe.executable_name                        e_name
   ,      fet.description                           e_desc
   ,      decode(fe.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',    'N/A')          e_method
   ,      fe.execution_file_name                    e_f_name
   from   fnd_executables     fe
   ,      fnd_executables_tl  fet
   where  fe.execution_file_name   = p_exe_name
   and    fe.execution_method_code = p_exe_type
   and    fe.executable_id         = fet.executable_id(+)
   order by execution_file_name;

 l_cur_exe_id         fnd_executables.executable_id%TYPE;
 l_cur_exe_name       fnd_executables.executable_name%TYPE;
 l_cur_app_short_name VARCHAR2(15) := p_app_short_name;
 l_error_message      VARCHAR2(100);

BEGIN
    FOR e_rec IN exe_cur
    LOOP
       l_cur_exe_id   := e_rec.e_id;
       l_cur_exe_name := e_rec.e_name;
       l_line_id := write_out('EXE',l_line_id,'          ');
       l_line_id := write_out('EXE',l_line_id,'prompt REGISTERING executable '||e_rec.e_name);
       l_line_id := write_out('EXE',l_line_id,'BEGIN');
       l_line_id := write_out('EXE',l_line_id,'FND_PROGRAM.EXECUTABLE( ');
       l_line_id := write_out('EXE',l_line_id,'     executable           => '||chr(39)||e_rec.e_name     ||chr(39)||', ');
       l_line_id := write_out('EXE',l_line_id,'     application          => '||chr(39)||p_app_short_name ||chr(39)||', ');
       l_line_id := write_out('EXE',l_line_id,'     short_name           => '||chr(39)||e_rec.e_name     ||chr(39)||', ');
       l_line_id := write_out('EXE',l_line_id,'     description          => '||chr(39)||e_rec.e_desc     ||chr(39)||', ');
       l_line_id := write_out('EXE',l_line_id,'     execution_method     => '||chr(39)||e_rec.e_method   ||chr(39)||', ');
       l_line_id := write_out('EXE',l_line_id,'     execution_file_name  => '||chr(39)||e_rec.e_f_name   ||chr(39)||');');
       l_line_id := write_out('EXE',l_line_id,'END;');
       l_line_id := write_out('EXE',l_line_id,'/');
       /*
       || Register All Programs
       || for this executable calling reg_program
       */
       reg_program(
              p_exe_id         => e_rec.e_id,
              p_exe_short_name => e_rec.e_name,
              p_exe_app_name   => p_app_short_name);
    END LOOP;
    EXCEPTION
       WHEN OTHERS
       THEN
          l_error_message := substr(SQLERRM,1,100);
          dbms_output.put_line('Error On: '||l_cur_exe_id||'           EXE ID');
          dbms_output.put_line('Error On: '||l_cur_exe_name||'          EXE Name');
          dbms_output.put_line('Error On: '||l_cur_app_short_name||' APP Short Name');
          dbms_output.put_line('Error Message: '||l_error_message);
--    COMMIT;
END reg_exe;

PROCEDURE reg_program(
    p_exe_id          IN NUMBER,
    p_exe_short_name  IN VARCHAR2,
    p_exe_app_name    IN VARCHAR2 ) AS

 CURSOR prog_cur IS
   select  fcp.application_id                 p_app_id
   ,       fcp.concurrent_program_id          p_id
   ,       fcpt.user_concurrent_program_name  p_name
   ,       fa.application_short_name          p_app_name
   ,       fcp.enabled_flag                   p_enabled_flag
   ,       fcp.concurrent_program_name        p_short_name
   ,       fcpt.description                   p_desc
   ,       fcp.execution_options              p_exe_option
   ,       fcp.request_priority               p_priority
   ,       fcp.save_output_flag               p_save_output
   ,       fcp.print_flag                     p_print
   ,       fcp.minimum_width                  p_cols
   ,       fcp.minimum_length                 p_rows
   ,       fcp.output_print_style             p_style
   ,       fcp.required_style                 p_style_required
   ,       fcp.printer_name                   p_printer
   ,       'Y'                                p_use_in_srs
   ,       'Y'                                p_allow_disabled_values
   ,       fcp.run_alone_flag                 p_run_alone
   ,       fcp.output_file_type               p_output_type
   ,       fcp.enable_trace                   p_enable_trace
   ,       fcp.restart                        p_restart
   ,       fcp.nls_compliant                  p_nls_compliant
   from    fnd_application              fa
   ,       fnd_concurrent_programs_tl   fcpt
   ,       fnd_concurrent_programs      fcp
   where   fcp.executable_id          = p_exe_id
   and     fcp.enabled_flag           = 'Y'
   and     fcp.concurrent_program_id  = fcpt.concurrent_program_id
   and     fcp.application_id         = fcpt.application_id
   and     fcp.application_id         = fa.application_id
   order by fcp.concurrent_program_name;

BEGIN
    FOR p_rec IN prog_cur
    LOOP
       l_line_id := write_out('PROG',l_line_id,'          ');
       l_line_id := write_out('PROG',l_line_id,'prompt REGISTERING program '||p_rec.p_name);
       l_line_id := write_out('PROG',l_line_id,'BEGIN');
       l_line_id := write_out('PROG',l_line_id,'FND_PROGRAM.REGISTER( ');
       l_line_id := write_out('PROG',l_line_id,'   program                => '||chr(39)||p_rec.p_name                  ||chr(39)||', ');
       l_line_id := write_out('PROG',l_line_id,'   application            => '||chr(39)||p_rec.p_app_name              ||chr(39)||', ');
       l_line_id := write_out('PROG',l_line_id,'   enabled                => '||chr(39)||p_rec.p_enabled_flag          ||chr(39)||', ');
       l_line_id := write_out('PROG',l_line_id,'   short_name             => '||chr(39)||p_rec.p_short_name            ||chr(39)||', ');
       l_line_id := write_out('PROG',l_line_id,'   description            => '||chr(39)||p_rec.p_desc                  ||chr(39)||', ');
       l_line_id := write_out('PROG',l_line_id,'   executable_short_name  => '||chr(39)||p_exe_short_name              ||chr(39)||', ');
       l_line_id := write_out('PROG',l_line_id,'   executable_application => '||chr(39)||p_exe_app_name                ||chr(39)||', ');
       l_line_id := write_out('PROG',l_line_id,'   execution_options      => '||chr(39)||p_rec.p_exe_option            ||chr(39)||', ');
       l_line_id := write_out('PROG',l_line_id,'   priority               => '||chr(39)||p_rec.p_priority              ||chr(39)||', ');
       l_line_id := write_out('PROG',l_line_id,'   save_output            => '||chr(39)||p_rec.p_save_output           ||chr(39)||', ');
       l_line_id := write_out('PROG',l_line_id,'   print                  => '||chr(39)||p_rec.p_print                 ||chr(39)||', ');
       l_line_id := write_out('PROG',l_line_id,'   cols                   => '||chr(39)||p_rec.p_cols                  ||chr(39)||', ');
       l_line_id := write_out('PROG',l_line_id,'   rows                   => '||chr(39)||p_rec.p_rows                  ||chr(39)||', ');
       l_line_id := write_out('PROG',l_line_id,'   style                  => '||chr(39)||p_rec.p_style                 ||chr(39)||', ');
       l_line_id := write_out('PROG',l_line_id,'   style_required         => '||chr(39)||p_rec.p_style_required        ||chr(39)||', ');
       l_line_id := write_out('PROG',l_line_id,'   printer                => '||chr(39)||p_rec.p_printer               ||chr(39)||', ');
       l_line_id := write_out('PROG',l_line_id,'   use_in_srs             => '||chr(39)||p_rec.p_use_in_srs            ||chr(39)||', ');
       l_line_id := write_out('PROG',l_line_id,'   allow_disabled_values  => '||chr(39)||p_rec.p_allow_disabled_values ||chr(39)||', ');
       l_line_id := write_out('PROG',l_line_id,'   run_alone              => '||chr(39)||p_rec.p_run_alone             ||chr(39)||', ');
       l_line_id := write_out('PROG',l_line_id,'   output_type            => '||chr(39)||p_rec.p_output_type           ||chr(39)||', ');
       l_line_id := write_out('PROG',l_line_id,'   enable_trace           => '||chr(39)||p_rec.p_enable_trace          ||chr(39)||', ');
       l_line_id := write_out('PROG',l_line_id,'   restart                => '||chr(39)||p_rec.p_restart               ||chr(39)||', ');
       l_line_id := write_out('PROG',l_line_id,'   nls_compliant          => '||chr(39)||p_rec.p_nls_compliant         ||chr(39)||');');
       l_line_id := write_out('PROG',l_line_id,'END;');
       l_line_id := write_out('PROG',l_line_id,'/');
       reg_param(
            p_prog_app_id     => p_rec.p_app_id,
            p_prog_app_name   => p_rec.p_app_name,
            p_prog_short_name => p_rec.p_short_name);
       reg_in_group(
            p_prog_app_id     => p_rec.p_app_id,
            p_prog_app_name   => p_rec.p_app_name,
            p_prog_short_name => p_rec.p_short_name,
            p_prog_id         => p_rec.p_id);
    END LOOP;
--    COMMIT;
END reg_program;


PROCEDURE reg_param(
    p_prog_app_id     IN NUMBER,
    p_prog_app_name   IN VARCHAR2,
    p_prog_short_name IN VARCHAR2 ) IS

 CURSOR par_cur IS
   SELECT      fdfcu.application_id                  p_app_id
   ,           fdfcu.column_seq_num                  p_seq
   ,           fdfcu.end_user_column_name            p_param_name
   ,           fdfcu.enabled_flag                    p_enabled
   ,           ffvs.flex_value_set_name              p_value_set
   ,           decode(fdfcu.default_type,
                       'C','Constant',
                       'P','Profile',
                       'S','SQL Statement',
                       'A','Segment')                p_defaul_type
   ,           replace(fdfcu.default_value,
                        chr(39),chr(39)||chr(39))    p_default_value
   ,           fdfcu.required_flag                   p_required
   ,           fdfcu.security_enabled_flag           p_enable_security
   ,           decode(fdfcu.range_code,
                       'P','Pair',
                       'H','High',
                       'L','Low')                    p_range
   ,           fdfcu.display_flag                    p_display
   ,           fdfcu.display_size                    p_display_size
   ,           fdfcu.maximum_description_len         p_desc_size
   ,           fdfcu.concatenation_description_len   p_conc_desc_size
   ,           replace(fdfcu.form_left_prompt,
                        chr(39),chr(39)||chr(39))    p_prompt
   ,           fdfcu.srw_param                       p_token
   FROM        fnd_flex_value_sets           ffvs
   ,           fnd_descr_flex_col_usage_vl   fdfcu
   WHERE       fdfcu.descriptive_flexfield_name = '$SRS$.'||p_prog_short_name
   AND         fdfcu.application_id = p_prog_app_id
   AND         fdfcu.application_id = fdfcu.application_id
   AND         fdfcu.flex_value_set_id = ffvs.flex_value_set_id
   ORDER BY    fdfcu.column_seq_num;

BEGIN
    FOR par_rec IN par_cur
    LOOP
       l_line_id := write_out('FLEX',l_line_id,'DECLARE');
       l_line_id := write_out('FLEX',l_line_id,' dummy VARCHAR2(1);');
       l_line_id := write_out('FLEX',l_line_id,'BEGIN');
       l_line_id := write_out('FLEX',l_line_id,'  SELECT '||chr(39)||'X'||chr(39) );
       l_line_id := write_out('FLEX',l_line_id,'  INTO   dummy');
       l_line_id := write_out('FLEX',l_line_id,'  FROM   fnd_flex_value_sets');
       l_line_id := write_out('FLEX',l_line_id,'  WHERE  flex_value_set_name = '||chr(39)||par_rec.p_value_set||chr(39)||';');
       l_line_id := write_out('FLEX',l_line_id,'    EXCEPTION');
       l_line_id := write_out('FLEX',l_line_id,'      WHEN NO_DATA_FOUND');
       l_line_id := write_out('FLEX',l_line_id,'      THEN');
       l_line_id := write_out('FLEX',l_line_id,'         dbms_output.put_line('||chr(39)||'Value Set: '||par_rec.p_value_set||' missing ...'||chr(39)||');' );
       l_line_id := write_out('FLEX',l_line_id,'         dbms_output.put_line('||chr(39)||'===> Program Name: '||p_prog_short_name||chr(39)||');' );
       l_line_id := write_out('FLEX',l_line_id,'END;');
       l_line_id := write_out('FLEX',l_line_id,'/');
       l_line_id := write_out('PAR',l_line_id,'          ');
       l_line_id := write_out('PAR',l_line_id,'prompt REGISTERING parameter '||p_prog_short_name||': '||par_rec.p_param_name);
       l_line_id := write_out('PAR',l_line_id,'DECLARE');
       l_line_id := write_out('PAR',l_line_id,'   l_error_message varchar2(500);');
       l_line_id := write_out('PAR',l_line_id,'   l_par_already_exist BOOLEAN;');
       l_line_id := write_out('PAR',l_line_id,'BEGIN');
       l_line_id := write_out('PAR',l_line_id,'  l_par_already_exist :=  FND_PROGRAM.parameter_exists(');
       l_line_id := write_out('PAR',l_line_id,'       program_short_name => '||chr(39)||p_prog_short_name       ||chr(39)||', ');
       l_line_id := write_out('PAR',l_line_id,'       application        => '||chr(39)||p_prog_app_name         ||chr(39)||', ');
       l_line_id := write_out('PAR',l_line_id,'       parameter          => '||chr(39)||par_rec.p_param_name    ||chr(39)||');');
       l_line_id := write_out('PAR',l_line_id,'  IF NOT l_par_already_exist');
       l_line_id := write_out('PAR',l_line_id,'  THEN');
       l_line_id := write_out('PAR',l_line_id,'     FND_PROGRAM.PARAMETER( ');
       l_line_id := write_out('PAR',l_line_id,'       program_short_name                => '||chr(39)||p_prog_short_name       ||chr(39)||', ');
       l_line_id := write_out('PAR',l_line_id,'       application                       => '||chr(39)||p_prog_app_name         ||chr(39)||', ');
       l_line_id := write_out('PAR',l_line_id,'       sequence                          => '||chr(39)||par_rec.p_seq           ||chr(39)||', ');
       l_line_id := write_out('PAR',l_line_id,'       parameter                         => '||chr(39)||par_rec.p_param_name    ||chr(39)||', ');
       l_line_id := write_out('PAR',l_line_id,'       description                       => '||chr(39)||par_rec.p_param_name    ||chr(39)||', ');
       l_line_id := write_out('PAR',l_line_id,'       enabled                           => '||chr(39)||par_rec.p_enabled       ||chr(39)||', ');
       l_line_id := write_out('PAR',l_line_id,'       value_set                         => '||chr(39)||par_rec.p_value_set     ||chr(39)||', ');
       l_line_id := write_out('PAR',l_line_id,'       default_type                      => '||chr(39)||par_rec.p_defaul_type   ||chr(39)||', ');
       l_line_id := write_out('PAR',l_line_id,'       default_value                     => '||chr(39)||par_rec.p_default_value ||chr(39)||', ');
       l_line_id := write_out('PAR',l_line_id,'       required                          => '||chr(39)||par_rec.p_required      ||chr(39)||', ');
       l_line_id := write_out('PAR',l_line_id,'       enable_security                   => '||chr(39)||par_rec.p_enable_security  ||chr(39)||', ');
       l_line_id := write_out('PAR',l_line_id,'       range                             => '||chr(39)||par_rec.p_range            ||chr(39)||', ');
       l_line_id := write_out('PAR',l_line_id,'       display                           => '||chr(39)||par_rec.p_display          ||chr(39)||', ');
       l_line_id := write_out('PAR',l_line_id,'       display_size                      => '||chr(39)||par_rec.p_display_size     ||chr(39)||', ');
       l_line_id := write_out('PAR',l_line_id,'       description_size                  => '||chr(39)||par_rec.p_desc_size        ||chr(39)||', ');
       l_line_id := write_out('PAR',l_line_id,'       concatenated_description_size     => '||chr(39)||par_rec.p_conc_desc_size   ||chr(39)||', ');
       l_line_id := write_out('PAR',l_line_id,'       prompt                            => '||chr(39)||par_rec.p_prompt           ||chr(39)||', ');
       l_line_id := write_out('PAR',l_line_id,'       token                             => '||chr(39)||par_rec.p_token            ||chr(39)||');');
       l_line_id := write_out('PAR',l_line_id,'  ELSE');
       l_line_id := write_out('PAR',l_line_id,'     insert into vm_process values('||chr(39)||p_prog_short_name||chr(39)||', ');
       l_line_id := write_out('PAR',l_line_id,'                                   '||chr(39)||par_rec.p_param_name||chr(39)||', ');
       l_line_id := write_out('PAR',l_line_id,'                                   '||chr(39)||'Parameter Already Exist'||chr(39)||');');
       l_line_id := write_out('PAR',l_line_id,'  END IF;');
       l_line_id := write_out('PAR',l_line_id,'  EXCEPTION');
       l_line_id := write_out('PAR',l_line_id,'    WHEN OTHERS');
       l_line_id := write_out('PAR',l_line_id,'    THEN');
       l_line_id := write_out('PAR',l_line_id,'        l_error_message := SQLERRM;');
       l_line_id := write_out('PAR',l_line_id,'        insert into vm_process values('||chr(39)||p_prog_short_name||chr(39)||', ');
       l_line_id := write_out('PAR',l_line_id,'                                      '||chr(39)||par_rec.p_param_name||chr(39)||', ');
       l_line_id := write_out('PAR',l_line_id,'                                      l_error_message);');
       l_line_id := write_out('PAR',l_line_id,'END;');
       l_line_id := write_out('PAR',l_line_id,'/');
    END LOOP;
--    COMMIT;
END reg_param;

PROCEDURE reg_in_group(
    p_prog_app_id     IN NUMBER,
    p_prog_app_name   IN VARCHAR2,
    p_prog_short_name IN VARCHAR2,
    p_prog_id         IN NUMBER ) IS

 CURSOR grp_cur IS
    select  frg.request_group_name    p_request_group
    ,       fa.application_short_name p_group_application
    from    fnd_application         fa
    ,       fnd_request_groups      frg
    ,       fnd_request_group_units frgu
    where   frgu.request_unit_id     = p_prog_id
    and     frgu.unit_application_id = p_prog_app_id
    and     frgu.request_group_id    = frg.request_group_id
    and     frgu.application_id      = frg.application_id
    and     frgu.application_id      = fa.application_id;

BEGIN
    FOR g_rec IN grp_cur
    LOOP
       l_line_id := write_out('GRP',l_line_id,'          ');
       l_line_id := write_out('GRP',l_line_id,'prompt REGISTERING program '||p_prog_short_name||' into Group '||g_rec.p_request_group);
       l_line_id := write_out('GRP',l_line_id,'BEGIN');
       l_line_id := write_out('GRP',l_line_id,'FND_PROGRAM.ADD_TO_GROUP( ');
       l_line_id := write_out('GRP',l_line_id,'   program_short_name   => '||chr(39)||p_prog_short_name         ||chr(39)||', ');
       l_line_id := write_out('GRP',l_line_id,'   program_application  => '||chr(39)||p_prog_app_name           ||chr(39)||', ');
       l_line_id := write_out('GRP',l_line_id,'   request_group        => '||chr(39)||g_rec.p_request_group     ||chr(39)||', ');
       l_line_id := write_out('GRP',l_line_id,'   group_application    => '||chr(39)||g_rec.p_group_application ||chr(39)||');');
       l_line_id := write_out('GRP',l_line_id,'END;');
       l_line_id := write_out('GRP',l_line_id,'/');
    END LOOP;
END reg_in_group;

END vm_apps_program;
/


--
-- Create API calls for all CORETEC custom reports
--
truncate table SQL_TEMP;
EXECUTE vm_apps_program.reg_exe('HKIS','HKIS Transaction Print','P');
EXECUTE vm_apps_program.reg_exe('HKIS','HKISACK','P');
EXECUTE vm_apps_program.reg_exe('HKIS','HKISCINV','P');
EXECUTE vm_apps_program.reg_exe('HKIS','HKISDLP','P');
EXECUTE vm_apps_program.reg_exe('HKIS','HKISNOTE','P');
EXECUTE vm_apps_program.reg_exe('HKIS','HKISPAK','P');
EXECUTE vm_apps_program.reg_exe('HKIS','HKISPOL','P');
EXECUTE vm_apps_program.reg_exe('HKIS','HKISRBOMS','P');
EXECUTE vm_apps_program.reg_exe('HKIS','HKISRFP','P');
EXECUTE vm_apps_program.reg_exe('HKIS','HKISRMA','P');
commit;




--
-- Create SPOOL file reg_prog.sql with API calls
--
set pages 0
set term off
set feedback off
spool reg_prog.sql
prompt set feedback off
prompt set serveroutput on
prompt
prompt drop table vm_process
prompt /
prompt
prompt create table vm_process(
prompt stage1 varchar2(50),
prompt stage2 varchar2(100),
prompt error_message varchar2(500)
prompt )
prompt /
prompt

set lines 100

prompt prompt Checking Validation Sets ...
select sql_string
from   sql_temp
where  sql_stage = 'FLEX'
order by sql_stage, line_id;
prompt prompt Done Checking Validation Sets ...
prompt accept dummy prompt "Press Enter To Continue, or CTL-C to CANCEL"


select sql_string
from   sql_temp
where  sql_stage = 'EXE'
order by sql_stage, line_id;
prompt accept dummy prompt "Press Enter To Continue, or CTL-C to CANCEL"

select sql_string
from   sql_temp
where  sql_stage = 'PROG'
order by sql_stage, line_id;
prompt accept dummy prompt "Press Enter To Continue, or CTL-C to CANCEL"

select sql_string
from   sql_temp
where  sql_stage = 'GRP'
order by sql_stage, line_id;
prompt accept dummy prompt "Press Enter To Continue, or CTL-C to CANCEL"


set lines 300

select sql_string
from   sql_temp
where  sql_stage = 'PAR'
order by sql_stage, line_id;

prompt set pages 0
prompt set lines 80
prompt col line_br format a80
prompt
prompt select 'Program Name:   '||stage1            line_br
prompt ,      'Parameter Name: '||stage2            line_br
prompt ,      'Error: '||error_message              line_br
prompt from vm_process
prompt where ERROR_MESSAGE != 'Parameter Already Exist'
prompt /


spool off
set term on
ed reg_prog.sql