


--------------------------------------------------------------------------
--Trigger Name: C1_BI_F_UNCPR (Generated Code)
--Date: 04-07-2011 17:41:07
--------------------------------------------------------------------------

CREATE OR REPLACE TRIGGER C1_BI_F_UNCPR
   AFTER INSERT OR UPDATE
   ON CI_WO_PROC
   FOR EACH ROW

DECLARE

   ws_proc_name     VARCHAR2(30) := 'C1_BI_F_UNCPR';
   ws_label         VARCHAR2(200);
   ws_batch_cd      ci_batch_ctrl.batch_cd%TYPE := 'EXTUNCPR';
   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.WO_STATUS_FLG <> :OLD.WO_STATUS_FLG)) THEN
         ws_action_ind := 'U';

      END IF;

   END IF;

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

      LOOP

         ws_label := 'Generating primary key for insert/update action';
         ws_chg_log_id := LPAD(SUBSTR(:NEW.WO_PROC_ID,1,5),5,'0');
         ws_chg_log_id := TO_NUMBER(RTRIM(ws_chg_log_id) || LPAD(TO_CHAR(ROUND(DBMS_RANDOM.VALUE(1,999999999),0)),9,'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_WO_PROC'
               ,:NEW.WO_PROC_ID
            );

            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;
/