
--
-- FLA_BRI_SEQ  (Trigger) 
--
--  Dependencies: 
--   FB_LAB_ADDRESSES (Table)
--
CREATE OR REPLACE TRIGGER FLA_BRI_SEQ
 BEFORE INSERT
 ON FB_LAB_ADDRESSES
 FOR EACH ROW
BEGIN
   IF :new.id IS NULL THEN
       SELECT FLA_SEQ.NEXTVAL
       INTO :new.id
       FROM dual;
       IF SQL%notfound THEN
       raise_application_error(-20031,
           'The sequence generator IS NOT working');
       END IF;
   END IF;
END;
/
SHOW ERRORS;



--
-- SET_CSM_ROLE_PRIV_ROLE_PRIVILE  (Trigger) 
--
--  Dependencies: 
--   CSM_ROLE_PRIVILEGE (Table)
--
CREATE OR REPLACE TRIGGER SET_CSM_ROLE_PRIV_ROLE_PRIVILE
BEFORE INSERT
ON CSM_ROLE_PRIVILEGE
FOR EACH ROW
BEGIN
  SELECT CSM_ROLE_PRIV_ROLE_PRIVILE_SEQ.NEXTVAL
  INTO :NEW.ROLE_PRIVILEGE_ID
  FROM DUAL;
END;
/
SHOW ERRORS;



--
-- SET_CSM_ROLE_PRIV_UPDATE_DATE  (Trigger) 
--
--  Dependencies: 
--   CSM_ROLE_PRIVILEGE (Table)
--
CREATE OR REPLACE TRIGGER SET_CSM_ROLE_PRIV_UPDATE_DATE
BEFORE INSERT
ON CSM_ROLE_PRIVILEGE
FOR EACH ROW
BEGIN
  SELECT SYSDATE
  INTO :NEW.UPDATE_DATE
  FROM DUAL;
END;
/
SHOW ERRORS;



--
-- SET_CSM_PG_PE_PG_PE_ID  (Trigger) 
--
--  Dependencies: 
--   CSM_PG_PE (Table)
--
CREATE OR REPLACE TRIGGER SET_CSM_PG_PE_PG_PE_ID
BEFORE INSERT
ON CSM_PG_PE
FOR EACH ROW
BEGIN
  SELECT CSM_PG_PE_PG_PE_ID_SEQ.NEXTVAL
  INTO :NEW.PG_PE_ID
  FROM DUAL;
END;
/
SHOW ERRORS;



--
-- SET_CSM_PG_PE_UPDATE_DATE  (Trigger) 
--
--  Dependencies: 
--   CSM_PG_PE (Table)
--
CREATE OR REPLACE TRIGGER SET_CSM_PG_PE_UPDATE_DATE
BEFORE INSERT
ON CSM_PG_PE
FOR EACH ROW
BEGIN
  SELECT SYSDATE
  INTO :NEW.UPDATE_DATE
  FROM DUAL;
END;
/
SHOW ERRORS;



--
-- SET_CSM_USER_GROU_USER_GROUP_I  (Trigger) 
--
--  Dependencies: 
--   CSM_USER_GROUP (Table)
--
CREATE OR REPLACE TRIGGER SET_CSM_USER_GROU_USER_GROUP_I
BEFORE INSERT
ON CSM_USER_GROUP
FOR EACH ROW
BEGIN
  SELECT CSM_USER_GROU_USER_GROUP_I_SEQ.NEXTVAL
  INTO :NEW.USER_GROUP_ID
  FROM DUAL;
END;
/
SHOW ERRORS;



--
-- SET_CSM_USER_PE_UPDATE_DATE  (Trigger) 
--
--  Dependencies: 
--   CSM_USER_PE (Table)
--
CREATE OR REPLACE TRIGGER SET_CSM_USER_PE_UPDATE_DATE
BEFORE INSERT
ON CSM_USER_PE
FOR EACH ROW
BEGIN
  SELECT SYSDATE
  INTO :NEW.UPDATE_DATE
  FROM DUAL;
END;
/
SHOW ERRORS;



--
-- SET_CSM_USER_PE_USER_PROTECTIO  (Trigger) 
--
--  Dependencies: 
--   CSM_USER_PE (Table)
--
CREATE OR REPLACE TRIGGER SET_CSM_USER_PE_USER_PROTECTIO
BEFORE INSERT
ON CSM_USER_PE
FOR EACH ROW
BEGIN
  SELECT CSM_USER_PE_USER_PROTECTIO_SEQ.NEXTVAL
  INTO :NEW.USER_PROTECTION_ELEMENT_ID
  FROM DUAL;
END;
/
SHOW ERRORS;




--
-- FRF_ARU_TRG  (Trigger) 
--
--  Dependencies: 
--   FB_REG_FDFS (Table)
--
CREATE OR REPLACE TRIGGER FRF_ARU_TRG
AFTER  UPDATE  OF
   COMPLETION_STATUS
 ON FB_REG_FDFS
REFERENCING
 NEW AS NEW
 OLD AS OLD
FOR EACH ROW
declare
 ln_friId number(10);
 i number;
 reg_type      fb_reg_1572s.MANUAL_REG_PACKET_FLAG%type;
 frm_type      fb_reg_mains.frm_type%type;
 cv_attached   BOOLEAN  := FALSE;
 rv_status     fb_reg_1572s.reg_status%type  := 'No Change';
 cur_status    fb_reg_1572s.reg_status%type;

BEGIN
 IF :OLD.FRI_ID IS NOT NULL THEN
        SELECT nvl(reg_status,'NULL'), manual_reg_packet_flag, frm_type
        INTO cur_status, reg_type, frm_type
        FROM   fb_reg_1572s f1572, fb_reg_mains frm
        WHERE  f1572.frm_id = frm.id
        AND    f1572.id = :old.fri_id;

        IF (:NEW.COMPLETION_STATUS = 'Signed' OR :NEW.COMPLETION_STATUS = 'Completed') AND cur_status = 'Disapproved' THEN
 -- check 1572 status
  select count(*)
         into i
         from fb_reg_1572s
         where id = :old.fri_id
         and ( nvl(completion_status,'New') = 'Signed' or completion_status = 'Completed');

         IF i = 1 then
  -- check CV Attached
                 select count(*) into i
                 from fb_dms_links fdl, fb_dms_types fdt
                 where fdl.fdt_id = fdt.id
                 and ((reg_type = 'Electronic' and document_category = 'CV Signed')
                     or (reg_type != 'Electronic' and document_category = 'CV FORM'))
                 and fri_id = :OLD.FRI_ID
                 and status = 'Active';

                 IF i > 0 THEN
   -- check for Investigator FDF
                 Update fb_reg_1572s
                 set reg_status = 'To be Submitted'
                 Where id = :OLD.FRI_ID;
                END IF;
  END IF;
 END IF;
END IF;
END;
/
SHOW ERRORS;

