REM---------------------------------------------------------------------------------------------------------
REM-- Copywright 2001, chet  west
REM---------------------------------------------------------------------------------------------------------
REM
REM NAME: tabcolerr
REM
REM PURPOSE: Create default error messages for a table's or view's columns
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 IS
   SELECT tab.id, tab.name
   FROM ci_table_definitions tab
       ,ci_application_systems app
       ,ci_app_sys_tables apptab
   WHERE tab.name LIKE '&p_table_name'
    AND  apptab.table_reference = tab.id
    AND  apptab.application_system_reference = app.id
    AND  app.name = UPPER('&p_appl_sys_name')
   UNION
   SELECT vw.id, vw.name
   FROM ci_view_definitions vw
       ,ci_application_systems app
       ,ci_app_sys_tables apptab
   WHERE vw.name LIKE '&p_table_name'
    AND  apptab.table_reference = vw.id
    AND  apptab.application_system_reference = app.id
    AND  app.name = UPPER('&p_appl_sys_name');

   CURSOR c_columns (cp_tab_ref IN NUMBER) IS
   SELECT col.id, col.name, col.prompt, col.validation_failure_message
   FROM ci_columns col
   WHERE col.table_reference = cp_tab_ref;
   --
   column_to_update   ciocolumn.data;
   v_errmsg        VARCHAR2(70);
   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 LOOP
      DBMS_OUTPUT.PUT_LINE('Error messages for '||tab.name);
      FOR col IN c_columns(tab.id) LOOP
         v_errmsg := 'That is not a valid value for ';
         IF col.validation_failure_message IS NULL THEN
            CDAPI.OPEN_ACTIVITY;
            column_to_update.i.validation_failure_message := TRUE;
            IF col.prompt IS NULL THEN
               v_errmsg := v_errmsg || UPPER(col.name);
            ELSE
               v_errmsg := v_errmsg || UPPER(col.prompt);
            END IF;
         ELSE
            v_errmsg := col.validation_failure_message;
         END IF;
         column_to_update.v.validation_failure_message := v_errmsg;
         CIOCOLUMN.UPD(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 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 aborted with errors!');
END;