set serveroutput on
set verify off
rem ******************************************************
rem *
rem * This utility rettrofits display properties of DCUs
rem * into their related relations columns.
rem *
rem * We wrote "relation columns" because these columns may
rem * belong to a view as well as a table.
rem *
rem * This module can operate on one to many modules within
rem * an application system.
rem *
rem *
rem * Written by: H. Deschamps, 7/18/97.
rem * Last Modified: 7/18/97.
rem ******************************************************
declare
relcol ciocolumn.data;
act_status varchar2(1); -- Activity
status
act_warnings varchar2(1); -- Activity warning
flag
v_app_id number(38);
cursor get_application_id (p_app_name varchar2,
p_app_version number) is
select id
from ci_application_systems
where name = p_app_name and
version
= p_app_version;
unknown_application_system exception;
pragma exception_init(unknown_application_system, -20103);
cursor disp_relcolumns (p_app_id number, p_mod_sname varchar2)
is
select rel.name
rel_name,
relcol.id
relcol_id,
relcol.name
relcol_name,
modcol.display_flag
modcol_display_flag,
modcol.display_datatype
modcol_display_datatype,
modcol.justification
modcol_justification,
modcol.display_length
modcol_display_length,
modcol.display_height
modcol_display_height,
modcol.usage_sequence
modcol_usage_sequence,
modcol.display_format
modcol_display_format,
modcol.highlighting
modcol_highlighting,
modcol.prompt
modcol_prompt,
modcol.hint_text
modcol_hint_text,
modcol.order_sequence
modcol_order_sequence,
modcol.sorting_order
modcol_sorting_order,
modcol.remark
modcol_remark,
modcol.default_value
modcol_default_value
from ci_module_detail_column_usages
modcol,
ci_columns
relcol,
ci_relation_definitions
rel,
ci_modules
modu
where modu.short_name like p_mod_sname
and
modu.application_system_owned_by
= p_app_id and
modu.id
= modcol.module_reference and
modcol.column_reference
= relcol.id and
(
relcol.display_flag
is null or
relcol.display_datatype
is null or
relcol.justification
is null or
relcol.display_length
is null or
relcol.display_height
is null or
relcol.display_sequence
is null or
relcol.format_modifier
is null or
relcol.highlighting
is null or
relcol.prompt
is null or
relcol.help_text
is null or
relcol.order_sequence
is null or
relcol.sorting_order
is null or
relcol.remark
is null or
relcol.default_value
is null
)
and
relcol.table_reference
= rel.id;
cur_disp_relcolumn disp_relcolumns%rowtype;
procedure instantiate_messages is
m_facility varchar2(3);
m_code number;
arg1 varchar2(240);
arg2 varchar2(64);
arg3 varchar2(64);
arg4 varchar2(64);
arg5 varchar2(20);
arg6 varchar2(20);
arg7 varchar2(20);
arg8 varchar2(20);
begin
-- Report all violations regardless of the activity
status
for viol in (select * from ci_violations) loop
dbms_output.put_line( cdapi.instantiate_message(
viol.facility,viol.code,
viol.p0,viol.p1,viol.p2,
viol.p3,viol.p4,viol.p5,
viol.p6,viol.p7
) );
end loop;
-- Pop messages off the stack and format them
into
-- a single text string
while cdapi.stacksize > 0 loop
rmmes.pop( m_facility,m_code,arg1,arg2,arg3,arg4,arg5,
arg6,arg7,arg8);
dbms_output.put_line(cdapi.instantiate_message
( m_facility,m_code,arg1,arg2,arg3,arg4,arg5,arg6,arg7,arg8));
end loop;
end;
begin
-- Get Application ID
open get_application_id(upper('&&app_name'),&&app_version);
fetch get_application_id into v_app_id;
if get_application_id%notfound then
raise_application_error(-20103,
'Sorry, the application
that you have entered is unknown.');
end if;
close get_application_id;
-- Initialize API if not already done
if cdapi.initialized = false then
-- Initialize the API globals
cdapi.initialize(upper('&&app_name'),
&&app_version);
end if;
-- Set DBMS Output Buffer to Max Size
dbms_output.enable(1000000);
for cur_disp_relcolumn in disp_relcolumns(v_app_id, upper('&&mod_short_name'))
loop
cdapi.open_activity;
-- next relation column
ciocolumn.sel(cur_disp_relcolumn.relcol_id ,relcol);
-- Retrofit display info when necessary
if relcol.v.display_flag is null then
relcol.v.display_flag := cur_disp_relcolumn.modcol_display_flag;
relcol.i.display_flag := TRUE;
end if;
if relcol.v.display_datatype is null then
relcol.v.display_datatype := cur_disp_relcolumn.modcol_display_datatype;
relcol.i.display_datatype := TRUE;
end if;
if relcol.v.justification is null then
relcol.v.justification := cur_disp_relcolumn.modcol_justification;
relcol.i.justification := TRUE;
end if;
if relcol.v.display_length is null then
relcol.v.display_length := cur_disp_relcolumn.modcol_display_length;
relcol.i.display_length := TRUE;
end if;
if relcol.v.display_height is null then
relcol.v.display_height := cur_disp_relcolumn.modcol_display_height;
relcol.i.display_height := TRUE;
end if;
if relcol.v.display_sequence is null then
relcol.v.display_sequence := cur_disp_relcolumn.modcol_usage_sequence;
relcol.i.display_sequence := TRUE;
end if;
if relcol.v.format_modifier is null then
relcol.v.format_modifier := cur_disp_relcolumn.modcol_display_format;
relcol.i.format_modifier := TRUE;
end if;
if relcol.v.highlighting is null then
relcol.v.highlighting := cur_disp_relcolumn.modcol_highlighting;
relcol.i.highlighting := TRUE;
end if;
if relcol.v.prompt is null then
relcol.v.prompt := cur_disp_relcolumn.modcol_prompt;
relcol.i.prompt := TRUE;
end if;
if relcol.v.help_text is null then
relcol.v.help_text := cur_disp_relcolumn.modcol_hint_text;
relcol.i.help_text := TRUE;
end if;
if relcol.v.order_sequence is null then
relcol.v.order_sequence := cur_disp_relcolumn.modcol_order_sequence;
relcol.i.order_sequence := TRUE;
end if;
if relcol.v.sorting_order is null then
relcol.v.sorting_order := cur_disp_relcolumn.modcol_sorting_order;
relcol.i.sorting_order := TRUE;
end if;
if relcol.v.remark is null then
relcol.v.remark := cur_disp_relcolumn.modcol_remark;
relcol.i.remark := TRUE;
end if;
if relcol.v.default_value is null then
relcol.v.default_value := cur_disp_relcolumn.modcol_default_value;
relcol.i.default_value := TRUE;
end if;
-- Update the relcolumn
ciocolumn.upd(cur_disp_relcolumn.relcol_id,
relcol);
-- Validate the update
cdapi.validate_activity(act_status,act_warnings);
-- Get feedback
instantiate_messages;
cdapi.close_activity(act_status);
-- If the activity did not close successfully,
roll back
-- all changes made during the activity
if act_status != 'Y' then
cdapi.abort_activity;
dbms_output.put_line('Activity aborted
with constraint violations');
else
dbms_output.put_line(cur_disp_relcolumn.rel_name||'.'||
cur_disp_relcolumn.relcol_name);
end if;
end loop;
exception
when unknown_application_system then
dbms_output.put_line('Sorry, the application
that you have entered is unknown.');
cdapi.abort_activity;
when others then
-- If any messages have been posted on the stack,
then print them now
-- and then roll back all changes made during
the activity
if cdapi.stacksize > 0 then
-- Print all messages on the API
stack
while cdapi.stacksize > 0 loop
dbms_output.put_line(cdapi.pop_instantiated_message);
end loop;
if cdapi.activity is not null then
cdapi.abort_activity;
dbms_output.put_line('Activity
aborted with API errors');
else
dbms_output.put_line('API
Session aborted with API errors');
end if;
-- Otherwise, this must have been an ORACLE SQL
or internal error so
-- roll back all changes made during the activity
and re-raise the
-- exception
else
if cdapi.activity is not null then
cdapi.abort_activity;
dbms_output.put_line('Activity
aborted with ORACLE internal errors');
else
dbms_output.put_line('API
Session aborted with ORACLE internal errors');
end if;
raise;
end if;
END;
/ |