TRIGGER: APPS.CZ_RULE_FOLDERS_T2

Source

Description

CZ_RULE_FOLDERS_T2
   BEFORE UPDATE OF NAME, DESC_TEXT, DELETED_FLAG, DISABLED_FLAG, PARENT_RULE_FOLDER_ID
  ON "CZ"."CZ_RULE_FOLDERS#"
  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;
  DELETED_CHANGED INTEGER := 0;
  DISABLED_CHANGED INTEGER := 0;
  FOLDER_CHANGED INTEGER := 0;
BEGIN

  -- if rule folder or rule sequence, don't need to propagate anything
  IF :NEW.OBJECT_TYPE <> 'RFL' AND :NEW.OBJECT_TYPE <> 'RSQ' THEN

    IF :OLD.NAME <> :NEW.NAME THEN
      NAME_CHANGED := 1;
    END IF;
    IF (:OLD.DESC_TEXT <> :NEW.DESC_TEXT) OR
       (:OLD.DESC_TEXT IS NULL AND :NEW.DESC_TEXT IS NOT NULL) OR
       (:OLD.DESC_TEXT IS NOT NULL AND :NEW.DESC_TEXT IS NULL) THEN
      DESCR_CHANGED := 1;
    END IF;
    IF :OLD.DELETED_FLAG <> :NEW.DELETED_FLAG THEN
      DELETED_CHANGED := 1;
    END IF;
    IF :OLD.DISABLED_FLAG <> :NEW.DISABLED_FLAG THEN
      DISABLED_CHANGED := 1;
    END IF;
    IF :OLD.PARENT_RULE_FOLDER_ID <> :NEW.PARENT_RULE_FOLDER_ID THEN
      FOLDER_CHANGED := 1;
    END IF;

    IF NAME_CHANGED + DESCR_CHANGED + DELETED_CHANGED + DISABLED_CHANGED + FOLDER_CHANGED > 0 THEN

      IF :OLD.OBJECT_TYPE = 'FNC' THEN
        UPDATE CZ_FUNC_COMP_SPECS SET
          NAME = DECODE(NAME_CHANGED, 1, :NEW.NAME, CZ_FUNC_COMP_SPECS.NAME),
          DESC_TEXT = DECODE(DESCR_CHANGED, 1, :NEW.DESC_TEXT, CZ_FUNC_COMP_SPECS.DESC_TEXT),
          DELETED_FLAG = DECODE(DELETED_CHANGED, 1, :NEW.DELETED_FLAG, CZ_FUNC_COMP_SPECS.DELETED_FLAG),
          RULE_FOLDER_ID = DECODE(FOLDER_CHANGED, 1, :NEW.PARENT_RULE_FOLDER_ID, CZ_FUNC_COMP_SPECS.RULE_FOLDER_ID)
      WHERE FUNC_COMP_ID = :OLD.RULE_FOLDER_ID;

      -- all other rules are stored in cz_rules
      ELSE
        UPDATE CZ_RULES SET
          NAME	= DECODE(NAME_CHANGED, 1, :NEW.NAME, CZ_RULES.NAME),
          DESC_TEXT = DECODE(DESCR_CHANGED, 1, :NEW.DESC_TEXT, CZ_RULES.DESC_TEXT),
          DELETED_FLAG	= DECODE(DELETED_CHANGED, 1, :NEW.DELETED_FLAG, CZ_RULES.DELETED_FLAG),
          DISABLED_FLAG = DECODE(DISABLED_CHANGED, 1, :NEW.DISABLED_FLAG, CZ_RULES.DISABLED_FLAG),
          RULE_FOLDER_ID = DECODE(FOLDER_CHANGED, 1, :NEW.PARENT_RULE_FOLDER_ID, CZ_RULES.RULE_FOLDER_ID)
        WHERE RULE_ID = :OLD.RULE_FOLDER_ID;
      END IF;
    END IF;
  END IF;
END;