REM Program: samecmt.sql

REM Author : Chester West II

REM          Tactics, Inc.  (704) 423-0220 

REM          [Charlotte, NC Office]

REM          Part of the Tactics' Designer/2000 Toolbox

REM Genesis: 15-Sep-1996

REM

REM Purpose: To copy a domain's description, if the first character

REM          is a tilda '~', to the entity attribute comment text

REM          and to the table column comment and hint text, for items

REM          in the domain.

REM

REM Note:    1)When this is run prior to DB-Design Wizard run, the entity 

REM          attribute comment text will be copied to the table column

REM          comment and hint text automatically by the DB-Design Wizard.

REM          2)Restrict your domain description to 80 characters or less

REM          since this is all that is allowed in the comment text for

REM          attributes.

REM

REM Warning: Although this can be run repeatedly for a given application,

REM          it will override any manually crafted attribute comment text

REM          and column comment and hint text.



PROMPT Enter the Application System Name

DEFINE p_application_system_name='&1'

PROMPT Enter the Application System Version

DEFINE p_application_system_version='&2'

PROMPT Enter Domain Name or % for ALL

DEFINE p_domain_name='&3'

PROMPT Enter Entity Name or % for ALL

DEFINE p_entity_name='&4'

PROMPT Enter Table Name or % for ALL

DEFINE p_table_name='&5'

PROMPT (U)pdate names or just (S)how and tell

DEFINE p_update_or_show='&6'

PROMPT Enter the output filename (without the .lis extension)

DEFINE p_filename='&7'



REM Let's not clutter up the screen and page with program code.

REM set verify   off

REM set echo     off

REM set feedback off



REM Force diagnostic output on so that the user can see what progress

REM          is being made and what the program did.

REM Increase the size of this buffer if your application is very large

REM          and you get a buffer-overflow error.

SET SERVEROUTPUT ON SIZE 400000

SPOOL &p_filename..lis



DECLARE -- Distribute domain comment text



cannot_init_appsys   EXCEPTION;



CURSOR get_domains IS

   SELECT ci_dom.id id,

          ci_dom.name name,

          ci_dom.description description

   FROM

      ci_domains ci_dom,

      ci_application_systems ci_apps

   WHERE ci_apps.id = ci_dom.application_system_owned_by

    AND  ci_apps.name = UPPER('&p_application_system_name')

    AND  ci_apps.version = &p_application_system_version

    AND  ci_dom.name LIKE UPPER('&p_domain_name')

    AND  SUBSTR(RTRIM(LTRIM(ci_dom.description)),1,1)='~';



activity_status     VARCHAR2(1);

activity_warning    VARCHAR2(1);

/************* Inline Function ***********************************/

FUNCTION init_api RETURN boolean IS

BEGIN

  -- Initialize the Designer/2000 API

  IF NOT cdapi.initialized THEN

    --- Not already initialized

    CDAPI.INITIALIZE(UPPER('&p_application_system_name')

                           ,&p_application_system_version);

  END IF;

  RETURN TRUE;

EXCEPTION

  WHEN OTHERS THEN

    RETURN FALSE;

END;



/************* End of Inline Function ****************************/



/************* Inline Procedure ***********************************/

PROCEDURE update_attributes (dom_id  IN ci_domains.id%TYPE,

                             dom_txt IN ci_attributes.notes%TYPE) IS



  attribute_to_update  CIOATTRIBUTE.DATA;

  

  CURSOR get_attrs IS

    SELECT ci_attr.id id, ci_attr.name name, ci_ent.name ent_name

    FROM ci_attributes ci_attr, ci_entities ci_ent,

         ci_application_systems ci_apps

    WHERE ci_apps.id = ci_ent.application_system_owned_by

     AND  ci_apps.name = UPPER('&p_application_system_name')

     AND  ci_apps.version = &p_application_system_version

     AND  ci_ent.id = ci_attr.entity_reference

     AND  ci_ent.name LIKE UPPER('&p_entity_name')

     AND  ci_attr.domain_reference = dom_id

    ORDER BY ci_ent.name, ci_attr.name;



BEGIN

  FOR curr_attr IN get_attrs LOOP

    DBMS_OUTPUT.PUT_LINE('Attribute==>'||curr_attr.ent_name||'.'||curr_attr.name);

    IF UPPER(RTRIM('&p_update_or_show')) = 'U' THEN

      -- Open an activity

      -- This issues a savepoint, so incomplete changes 

        -- can be rolled back to here.

      CDAPI.OPEN_ACTIVITY;

      -- Set attribute values and their indicators.

      attribute_to_update.v.notes := dom_txt;

      attribute_to_update.i.notes := TRUE;

      

      -- Update the attribute record.

      CIOATTRIBUTE.UPD(curr_attr.id, attribute_to_update);

      

      -- Validate the attribute insert.

      CDAPI.VALIDATE_ACTIVITY(activity_status, activity_warning);

      

      -- Show violation messages here.

      FOR each_violation IN (SELECT * FROM ci_violations) LOOP

        DBMS_OUTPUT.PUT_LINE(CDAPI.INSTANTIATE_MESSAGE(each_violation.facility

                                                ,each_violation.code

                                                ,each_violation.p0

                                                ,each_violation.p1

                                                ,each_violation.p2

                                                ,each_violation.p3

                                                ,each_violation.p4

                                                ,each_violation.p5

                                                ,each_violation.p6

                                                ,each_violation.p7

                                                )

                            );

      END LOOP;

      

      -- Attempt to close the activity.

      CDAPI.CLOSE_ACTIVITY(activity_status);

  

      -- If activity did not close, then abort and roll back changes.

      IF activity_status != 'Y' THEN

        CDAPI.ABORT_ACTIVITY;   

      END IF;

    END IF;

  END LOOP;

EXCEPTION

  WHEN OTHERS THEN

    DBMS_OUTPUT.PUT_LINE('Error : ' || SQLCODE || ', ' || SQLERRM);

    DBMS_OUTPUT.PUT_LINE('Procedure UPDATE_ATTRIBUTES aborted with errors!');

    IF cdapi.activity IS NOT null THEN

       cdapi.abort_activity;

    END IF;

END;

/************* End of Inline Procedure ****************************/



/************* Inline Procedure ***********************************/

PROCEDURE update_columns (dom_id  IN ci_domains.id%TYPE,

                          dom_txt IN ci_columns.remark%TYPE) IS



  column_to_update     CIOCOLUMN.DATA;



  CURSOR get_cols IS

    SELECT ci_col.id id, ci_col.name name, ci_tbl.name tbl_name

    FROM ci_columns ci_col, ci_table_definitions ci_tbl,

         ci_application_systems ci_apps

    WHERE ci_apps.id = ci_tbl.application_system_owned_by

     AND  ci_apps.name = UPPER('&p_application_system_name')

     AND  ci_apps.version = &p_application_system_version

     AND  ci_tbl.id = ci_col.table_reference

     AND  ci_tbl.name LIKE UPPER('&p_table_name')

     AND  ci_col.domain_reference = dom_id;



BEGIN

  FOR curr_col IN get_cols LOOP

    DBMS_OUTPUT.PUT_LINE('Column==>'||curr_col.tbl_name||'.'||curr_col.name);

    IF UPPER(RTRIM('&p_update_or_show')) = 'U' THEN

      -- Open an activity

      -- This issues a savepoint, so incomplete changes 

      -- can be rolled back to here.

      CDAPI.OPEN_ACTIVITY;

      -- Set column values and their indicators.

      column_to_update.v.remark    := dom_txt;

      column_to_update.i.remark    := TRUE;

      column_to_update.v.help_text := SUBSTR(dom_txt,1,132);

      column_to_update.i.help_text := TRUE;

      

      -- Update the attribute record.

      CIOCOLUMN.UPD(curr_col.id, column_to_update);

      

      -- Validate the column insert.

      CDAPI.VALIDATE_ACTIVITY(activity_status, activity_warning);

      

      -- Show violation messages here.

      FOR each_violation IN (SELECT * FROM ci_violations) LOOP

        DBMS_OUTPUT.PUT_LINE(CDAPI.INSTANTIATE_MESSAGE(each_violation.facility

                                                ,each_violation.code

                                                ,each_violation.p0

                                                ,each_violation.p1

                                                ,each_violation.p2

                                                ,each_violation.p3

                                                ,each_violation.p4

                                                ,each_violation.p5

                                                ,each_violation.p6

                                                ,each_violation.p7

                                                )

                            );

      END LOOP;

      

      -- Attempt to close the activity.

      CDAPI.CLOSE_ACTIVITY(activity_status);

  

      -- If activity did not close, then abort and roll back changes.

      IF activity_status != 'Y' THEN

        CDAPI.ABORT_ACTIVITY;   

      END IF;

    END IF;

  END LOOP;

EXCEPTION

  WHEN OTHERS THEN

    DBMS_OUTPUT.PUT_LINE('Error : ' || SQLCODE || ', ' || SQLERRM);

    DBMS_OUTPUT.PUT_LINE('Procedure UPDATE_COLUMNS aborted with errors!');

    IF cdapi.activity IS NOT null THEN

       cdapi.abort_activity;

    END IF;

END;

/************* End of Inline Procedure ****************************/



/************* Start of Main Program Body ************************/

BEGIN



  IF NOT init_api THEN

    RAISE cannot_init_appsys;

  ELSE

    FOR curr_domain IN get_domains LOOP

      DBMS_OUTPUT.PUT(curr_domain.name);

      DBMS_OUTPUT.PUT('->');

      DBMS_OUTPUT.PUT_LINE(curr_domain.description);



      -- Process attributes in domain.

      update_attributes(curr_domain.id, SUBSTR(RTRIM(LTRIM(curr_domain.description)),2,81));



      -- Process columns in domain.    

      update_columns(curr_domain.id, SUBSTR(RTRIM(LTRIM(curr_domain.description)),2,240));

    END LOOP;

  END IF;

EXCEPTION

  WHEN OTHERS THEN

    DBMS_OUTPUT.PUT_LINE('Error : ' || SQLCODE || ', ' || SQLERRM);

    DBMS_OUTPUT.PUT_LINE('Procedure aborted with errors!');

    IF cdapi.activity IS NOT null THEN

       cdapi.abort_activity;

    END IF;

END;

/