Designer 2000 API

Retrofitting DCU Display Properties

By Hervé Deschamps.

In theory we should define all the display properties against the table/view columns before creating modules. We all know this. Yet we often "forget" to do it. There are several reasons for that, some good, some bad. One good reason is that we can get immediate feedback on the module by generating it several times until we get it right. But when we get it fine tuned, how often do we update the underlying table/view columns? There is always something more urgent to do. Yet someone in the team (maybe you) will have to do the same work again. This is a waste of effort and can lead to inconsistencies of look and feel.

Wouldn't it be nice if we could:

This is what this utility helps you to do. Give it an application system name and version and a module short name and it will retroffit all the DCUs' display properties for you: You can also retroffit several module by entering '%' or any other search pattern as a module short name. But beware! Several modules may use the same underlying columns with different display properties. There is no telling what module will win the contest!

Of course, this utility will not override any existing display properties.

You may also note that I did not include the 'Displayed' flag in the list of display properties above. The reason is that this flag is always set to either true or false, never to null. This utility only overrides properties set to null.

You can run this code from Sql*Plus without creating any object in the database.

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 ******************************************************

  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,
           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
  = modcol.module_reference and
           modcol.column_reference = 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 =;

  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);
    -- Report all violations regardless of the activity status
    for viol in (select * from ci_violations) loop
      dbms_output.put_line( cdapi.instantiate_message(
         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,
          ( m_facility,m_code,arg1,arg2,arg3,arg4,arg5,arg6,arg7,arg8));
    end loop;


  -- 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
         '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

  for cur_disp_relcolumn in disp_relcolumns(v_app_id, upper('&&mod_short_name')) loop
    -- 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

    -- Get feedback


    -- If the activity did not close successfully, roll back
    -- all changes made during the activity
    if act_status != 'Y' then
      dbms_output.put_line('Activity aborted with constraint violations');
    end if;
  end loop;
  when unknown_application_system then
    dbms_output.put_line('Sorry, the application that you have entered is unknown.');
  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
      end loop;
      if cdapi.activity is not null then
        dbms_output.put_line('Activity aborted with API errors');
        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
      if cdapi.activity is not null then
        dbms_output.put_line('Activity aborted with ORACLE internal errors');
        dbms_output.put_line('API Session aborted with ORACLE internal errors');
      end if;
    end if;


Please send me comments or suggestions:

 Take a look at my HomePage