


--------------------------------------------------------------------------
--Trigger Name: C1_BI_D_CPTD (Generated Code)
--Date: 04-07-2011 17:38:31
--------------------------------------------------------------------------

CREATE OR REPLACE TRIGGER C1_BI_D_CPTD
   AFTER INSERT OR UPDATE OR DELETE
   ON CI_COLL_PROC_TM_L
   FOR EACH ROW

DECLARE

   ws_proc_name     VARCHAR2(30) := 'C1_BI_D_CPTD';
   ws_label         VARCHAR2(200);
   ws_batch_cd      ci_batch_ctrl.batch_cd%TYPE := 'EXTCPT';
   ws_batch_nbr     ci_batch_ctrl.next_batch_nbr%TYPE;
   ws_action_ind    CHAR(1) := NULL;
   ws_chg_log_id    CHAR(14) := NULL;

BEGIN

   ws_label := 'Fetching batch_nbr from ci_batch_ctrl';
   SELECT next_batch_nbr
     INTO ws_batch_nbr
     FROM ci_batch_ctrl
    WHERE batch_cd = ws_batch_cd;

   ws_label := 'Setting insert action flag';
   IF INSERTING THEN
      ws_action_ind := 'I';
   END IF;

   ws_label := 'Setting update action flag';
   IF UPDATING THEN

      IF ((:NEW.DESCR <> :OLD.DESCR)) THEN
         ws_action_ind := 'U';

      END IF;

   END IF;

   ws_label := 'Setting delete action flag';
   IF DELETING THEN
      ws_action_ind := 'D';
   END IF;

   IF ws_action_ind IN ('I','U') THEN

      LOOP
         ws_chg_log_id := TO_NUMBER(ROUND(DBMS_RANDOM.VALUE(1,99999999999999),0));

         BEGIN

            ws_label := 'Populating change log table for insert/update action';
            INSERT INTO ci_chg_log (
               chg_log_id
               ,batch_cd
               ,batch_nbr
               ,chg_dttm
               ,chg_type_flg
               ,tbl_name
               ,pk_val1
            ) VALUES (
               ws_chg_log_id
               ,ws_batch_cd
               ,ws_batch_nbr
               ,SYSDATE
               ,ws_action_ind
               ,'CI_COLL_PROC_TM_L'
               ,:NEW.COLL_PROC_TMPL_CD
            );

            EXIT;

         EXCEPTION
            WHEN DUP_VAL_ON_INDEX THEN
               NULL;

         END;

      END LOOP;

   END IF;

   IF ws_action_ind = 'D' THEN

      LOOP
         ws_chg_log_id := TO_NUMBER(ROUND(DBMS_RANDOM.VALUE(1,99999999999999),0));

         BEGIN

            ws_label := 'Populating change log table';
            INSERT INTO ci_chg_log (
               chg_log_id
               ,batch_cd
               ,batch_nbr
               ,chg_dttm
               ,chg_type_flg
               ,tbl_name
               ,pk_val1
            ) VALUES (
               ws_chg_log_id
               ,ws_batch_cd
               ,ws_batch_nbr
               ,SYSDATE
               ,ws_action_ind
               ,'CI_COLL_PROC_TM_L'
               ,:OLD.COLL_PROC_TMPL_CD
            );

            EXIT;

         EXCEPTION
            WHEN DUP_VAL_ON_INDEX THEN
               NULL;

         END;

      END LOOP;

   END IF;

EXCEPTION
   WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR(-20001,'Database Error ' || SQLCODE || ' occured while ' || ws_label || ' in ' || ws_proc_name);

END;
/