TRIGGER: APPS.CZ_PS_NODES_T2

Source

Description

CZ_PS_NODES_T2
  BEFORE INSERT OR DELETE OR UPDATE OF
     initial_value,
     initial_num_value,
     parent_id,
     minimum,
     maximum,
     ps_node_type,
     feature_type,
     bom_required_flag,
     minimum_selected,
     maximum_selected,
     decimal_qty_flag,
     deleted_flag,
     virtual_flag,
     ib_trackable,
     effective_from,
     effective_until,
     counted_options_flag,
     max_qty_per_option,
     effectivity_set_id,
     domain_order
 ON CZ_PS_NODES
 REFERENCING OLD AS OLD NEW AS NEW
 FOR EACH ROW

Type

BEFORE EACH ROW

Event

INSERT OR UPDATE OR DELETE

Column

When

Referencing

REFERENCING NEW AS NEW OLD AS OLD

Body

BEGIN

 --Bug #4920028. If deleted_flag = '1', we are purging a logically deleted record.

 IF DELETING AND NVL(:old.deleted_flag, '0') = '0' THEN
   UPDATE CZ_DEVL_PROJECTS SET
     LAST_STRUCT_UPDATE=SYSDATE,
     LAST_LOGIC_UPDATE=SYSDATE,
     post_migr_change_flag = DECODE(post_migr_change_flag, 'N', 'R', 'Z', 'R', post_migr_change_flag)
   WHERE
     DEVL_PROJECT_ID=:old.DEVL_PROJECT_ID;
 END IF;
 IF INSERTING OR (UPDATING AND (
      (((:old.parent_id IS NULL)<>(:new.parent_id IS NULL))OR(:old.parent_id<>:new.parent_id)) OR
      (((:old.minimum IS NULL)<>(:new.minimum IS NULL))OR(:old.minimum<>:new.minimum)) OR
      (((:old.maximum IS NULL)<>(:new.maximum IS NULL))OR(:old.maximum<>:new.maximum)) OR
      (((:old.ps_node_type IS NULL)<>(:new.ps_node_type IS NULL))OR(:old.ps_node_type<>:new.ps_node_type
         AND (:old.ps_node_type <> 258 OR :new.ps_node_type <> 259))) OR -- no update if upgrade prd to comp
      (((:old.feature_type IS NULL)<>(:new.feature_type IS NULL))OR(:old.feature_type<>:new.feature_type)) OR
      (((:old.minimum_selected IS NULL)<>(:new.minimum_selected IS NULL))OR(:old.minimum_selected<>:new.minimum_selected)) OR
      (((:old.maximum_selected IS NULL)<>(:new.maximum_selected IS NULL))OR(:old.maximum_selected<>:new.maximum_selected)) OR
      (((:old.virtual_flag IS NULL)<>(:new.virtual_flag IS NULL))OR(:old.virtual_flag<>:new.virtual_flag)) OR
      (((:old.deleted_flag IS NULL)<>(:new.deleted_flag IS NULL))OR(:old.deleted_flag<>:new.deleted_flag)) OR
      (((:old.domain_order IS NULL)<>(:new.domain_order IS NULL))OR(:old.domain_order<>:new.domain_order)) OR
      (((:old.counted_options_flag IS NULL)<>(:new.counted_options_flag IS NULL))OR(:old.counted_options_flag<>:new.counted_options_flag)) OR
      (((:old.max_qty_per_option IS NULL)<>(:new.max_qty_per_option IS NULL))OR(:old.max_qty_per_option<>:new.max_qty_per_option)) OR
      (((:old.effectivity_set_id IS NULL)<>(:new.effectivity_set_id IS NULL))OR(:old.effectivity_set_id<>:new.effectivity_set_id))
     )) THEN
       UPDATE CZ_DEVL_PROJECTS SET
         LAST_STRUCT_UPDATE=SYSDATE,
         LAST_LOGIC_UPDATE=SYSDATE,
         post_migr_change_flag = DECODE(post_migr_change_flag, 'N', 'R', 'Z', 'R', post_migr_change_flag)
       WHERE
         DEVL_PROJECT_ID=:new.DEVL_PROJECT_ID;
   END IF;

   IF (UPDATING AND (
      (((:old.decimal_qty_flag IS NULL)<>(:new.decimal_qty_flag IS NULL)) OR
       (:old.decimal_qty_flag<>:new.decimal_qty_flag)) OR
      (((:old.domain_order IS NULL)<>(:new.domain_order IS NULL)) OR
       (:old.domain_order<>:new.domain_order)) OR
      (((:old.initial_value IS NULL)<>(:new.initial_value IS NULL)) OR
       (:old.initial_value<>:new.initial_value)) OR
      (((:old.initial_num_value IS NULL)<>(:new.initial_num_value IS NULL)) OR
       (:old.initial_num_value<>:new.initial_num_value)) OR
      (((:old.ib_trackable IS NULL)<>(:new.ib_trackable IS NULL)) OR
       (:old.ib_trackable<>:new.ib_trackable)) OR
      (((:old.bom_required_flag IS NULL)<>(:new.bom_required_flag IS NULL)) OR
       (:old.bom_required_flag<>:new.bom_required_flag)) OR
       (((:old.effective_from IS NULL)<>(:new.effective_from IS NULL)) OR
        (:old.effective_from<>:new.effective_from)) OR
       (((:old.effective_until IS NULL)<>(:new.effective_until IS NULL)) OR
        (:old.effective_until<>:new.effective_until))
                    )
      ) THEN
         UPDATE CZ_DEVL_PROJECTS SET
         LAST_LOGIC_UPDATE=SYSDATE,
         post_migr_change_flag = DECODE(post_migr_change_flag, 'N', 'R', 'Z', 'R', post_migr_change_flag)
       WHERE
         DEVL_PROJECT_ID=:new.DEVL_PROJECT_ID;
   END IF;
END;