TRIGGER: APPS.CZ_UI_DEFS_T2

Source

Description

CZ_UI_DEFS_T2
  BEFORE UPDATE OF NAME, DESC_TEXT, DELETED_FLAG
  ON "CZ"."CZ_UI_DEFS#"
  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;
BEGIN
  IF :OLD.MASTER_TEMPLATE_FLAG = '1' THEN
    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 AND :NEW.DELETED_FLAG = '1' THEN
      DELETE FROM CZ_RP_ENTRIES
      WHERE OBJECT_TYPE = 'UMT' AND OBJECT_ID = :OLD.UI_DEF_ID;
    -- Check if there are any of 3 columns being updated
    ELSIF NAME_CHANGED+DESCR_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)
      WHERE OBJECT_TYPE='UMT' AND OBJECT_ID=:OLD.UI_DEF_ID;
    END IF;
  END IF;
END;