TRIGGER: APPS.CZ_DEVL_PROJECTS_T3

Source

Description

CZ_DEVL_PROJECTS_T3
   BEFORE UPDATE OF NAME, DESC_TEXT, DELETED_FLAG
  ON "CZ"."CZ_DEVL_PROJECTS#"
  REFERENCING OLD AS OLD NEW AS NEW
  FOR EACH ROW
 

Type

BEFORE EACH ROW

Event

UPDATE

Column

When

Referencing

REFERENCING NEW AS NEW OLD AS OLD

Body

DECLARE
   NAME_CHANGED INTEGER := 0;
   DESCR_CHANGED INTEGER := 0;
   DELFLAG_CHANGED INTEGER := 0;
 BEGIN
   IF :OLD.NAME <> :NEW.NAME THEN
     NAME_CHANGED := 1;
   END IF;
   IF ( ( (:old.DESC_TEXT IS NULL)<>(:new.DESC_TEXT IS NULL) ) OR (:old.DESC_TEXT <>:new.DESC_TEXT) ) THEN
     DESCR_CHANGED := 1;
   END IF;
   IF :OLD.DELETED_FLAG <> :NEW.DELETED_FLAG THEN
     DELFLAG_CHANGED := 1;
   END IF;
 -- Check if there are any of 4 columns being updated
   IF NAME_CHANGED+DESCR_CHANGED+DELFLAG_CHANGED > 0 THEN
     UPDATE CZ_RP_ENTRIES SET
        NAME  = DECODE(NAME_CHANGED, 1, :NEW.NAME, CZ_RP_ENTRIES.NAME),
        DESCRIPTION = DECODE(DESCR_CHANGED, 1, :NEW.DESC_TEXT, CZ_RP_ENTRIES.DESCRIPTION),
        DELETED_FLAG = DECODE(DELFLAG_CHANGED, 1, :NEW.DELETED_FLAG, CZ_RP_ENTRIES.DELETED_FLAG)
     WHERE OBJECT_TYPE='PRJ' AND OBJECT_ID=:OLD.DEVL_PROJECT_ID;

     IF (DELFLAG_CHANGED=1 and :NEW.DELETED_FLAG=1)
    then
      update CZ_PERSISTENT_REC_IDS set
      deleted_flag=1
      where devl_project_id=:new.devl_project_id;
    END IF;

    --Re: bug #2131259.

     UPDATE CZ_XFR_PROJECT_BILLS SET
        DELETED_FLAG = DECODE(DELFLAG_CHANGED, 1, :NEW.DELETED_FLAG, CZ_XFR_PROJECT_BILLS.DELETED_FLAG)
     WHERE MODEL_PS_NODE_ID = :OLD.DEVL_PROJECT_ID;
   END IF;

   IF NAME_CHANGED = 1 THEN
     UPDATE cz_rule_folders SET name = :new.name || ' Rules'
     WHERE devl_project_id = :OLD.DEVL_PROJECT_ID
     AND object_type = 'RFL'
     AND parent_rule_folder_id IS NULL;
   END IF;
 END;