Purpose: 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.
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
-- 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
-- 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
