REM---------------------------------------------------------------------------------------------------------
REM-- Copywright 2001, chet  west
REM---------------------------------------------------------------------------------------------------------
REM
REM NAME: table.constraints error messages
REM
REM PURPOSE: Create default error messages for a tables constraints
REM
REM PARAMETERS: NAME            TYPE     DESCRIPTION
REM            --------------- -------- -----------------------------------
REM            p_table_name    VARCHAR2 Name of the table.
REM
REM RETURN VALUE: TRUE if it worked.
REM
REM NOTES:
REM
REM GENESIS: Chester R. West II - 10/11/2001
REM MODIFICATION HISTORY:
REM   NAME           DATE     CHANGE DESCRIPTION
REM   -------------- -------- ---------------------------------------
REM
REM---------------------------------------------------------------------------------------------------------

REM This is a wide report, so set the line width accordingly.
SET linesize 132
SET pagesize  66
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
SET serveroutput ON SIZE 1000000
PROMPT Enter the Context Workarea Name
DEFINE p_context_workarea_name='&1'
PROMPT Enter the Application SYSTEM Name
DEFINE p_appl_sys_name='&2'
PROMPT Enter the output filename
DEFINE p_filename='&3'
PROMPT Enter the TABLE Name (Wildcards OK)
DEFINE p_table_name='&4'
SPOOL &p_filename

DECLARE -- create table.column error messages.
   --
   CURSOR c_tables (
      cp_table_name   IN   VARCHAR2
   ) IS
      SELECT tab.id
            ,tab.name
            ,NVL (tab.display_title, INITCAP (tab.name)) display_title
        FROM ci_table_definitions tab
            ,ci_application_systems app
            ,ci_app_sys_tables apptab
       WHERE tab.name LIKE cp_table_name
         AND apptab.table_reference = tab.id
         AND apptab.application_system_reference = app.id
         AND app.name = UPPER ('&p_appl_sys_name');

   CURSOR c_table (
      cp_table_id   IN   NUMBER
   ) IS
      SELECT tab.id
            ,tab.name
            ,NVL (tab.display_title, INITCAP (tab.name)) display_title
        FROM ci_table_definitions tab
       WHERE tab.id = cp_table_id;

   CURSOR c_constraints (
      cp_tab_ref   IN   NUMBER
   ) IS
      SELECT con.id
            ,con.constraint_type
            ,con.name
            ,con.error_message
            ,con.table_reference
            ,con.foreign_table_reference
        FROM ci_constraints con
       WHERE con.table_reference = cp_tab_ref;

   --
   fk_constraint_to_update   cioforeign_key_constraint.data;
   pk_constraint_to_update   cioprimary_key_constraint.data;
   uk_constraint_to_update   ciounique_key_constraint.data;
   v_errmsg                  VARCHAR2 (70);
   v_foreigntab              VARCHAR2 (30);
   activity_status           VARCHAR2 (1);
   activity_warning          VARCHAR2 (1);
--
BEGIN
   --
   -- Initialize
   d2k_api_util.show_messages_on;
   d2k_api_util.initialize_api ('&p_context_workarea_name', '&p_appl_sys_name');

   --
   -- Loop through the entities...
   --
   FOR tab IN c_tables ('&p_table_name') LOOP
      DBMS_OUTPUT.PUT_LINE ('Error messages for '|| tab.name);

      FOR con IN c_constraints (tab.id) LOOP
         IF con.error_message IS NULL THEN
            CDAPI.OPEN_ACTIVITY;

            IF con.constraint_type = 'PRIMARY' THEN
               v_errmsg := 'That ' || tab.display_title || ' already exists.';
               pk_constraint_to_update.i.error_message := TRUE;
               pk_constraint_to_update.v.error_message := v_errmsg;
               cioprimary_key_constraint.UPD (con.id, pk_constraint_to_update);
            ELSIF con.constraint_type = 'UNIQUE' THEN
               v_errmsg := 'That ' || tab.display_title || ' already exists.';
               uk_constraint_to_update.i.error_message := TRUE;
               uk_constraint_to_update.v.error_message := v_errmsg;
               ciounique_key_constraint.UPD (con.id, uk_constraint_to_update);
            ELSIF con.constraint_type = 'FOREIGN' THEN
               
--                OPEN c_table (con.foreiggn_table_reference);
--                FETCH c_table.display_tiitle INTO v_foreigntab;
--                CLOSE c_table;   
               SELECT NVL (tab.display_title, INITCAP (tab.name)) display_title
                 INTO v_foreigntab
                 FROM ci_table_definitions tab
                WHERE tab.id = con.foreign_table_reference;
               v_errmsg := 'That ' || v_foreigntab || ' was not found.';
               fk_constraint_to_update.i.error_message := TRUE;
               fk_constraint_to_update.v.error_message := v_errmsg;
               cioforeign_key_constraint.UPD (con.id, fk_constraint_to_update);
            ELSE
               pk_constraint_to_update.i.error_message := FALSE;
               uk_constraint_to_update.i.error_message := FALSE;
               fk_constraint_to_update.i.error_message := FALSE;
               v_errmsg := '';
            END IF;
         END IF;

         -- 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 LOOP;
   END LOOP;

   DBMS_OUTPUT.PUT_LINE ('Table.Column error messages program finished.');
--EXCEPTION
--   WHEN OTHERS THEN
--      DBMS_OUTPUT.PUT_LINE ('Error : '||| SQLCODE || ', ' || SQLERRM);
--      DBMS_OUTPUT.PUT_LINE ('Procedure aaborted with errors!');
END;