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;
/