BOMTBICX
/* $Header: BOMTBICX.sql 120.5 2006/06/12 14:01:35 arudresh noship $ */
AFTER UPDATE OR INSERT OR DELETE ON BOM_COMPONENTS_B
FOR EACH ROW
-- OLD.IMPLEMENTATION_DATE IS NOT NULL)
Type
AFTER EACH ROW
Event
INSERT OR UPDATE OR DELETE
Column
When
Referencing
REFERENCING NEW AS NEW OLD AS OLD
Body
DECLARE
error_msg VARCHAR2(80);
BEGIN
IF BOM_GLOBALS.G_SKIP_BOMTBICX <> 'Y' THEN
IF INSERTING THEN
UPDATE BOM_EXPLOSIONS BE
SET REXPLODE_FLAG = 1
WHERE BE.COMP_COMMON_BILL_SEQ_ID = :new.bill_sequence_id;
UPDATE BOM_EXPLOSIONS_ALL BE
SET REXPLODE_FLAG = 1
WHERE BE.COMP_COMMON_BILL_SEQ_ID = :new.bill_sequence_id;
END IF;
IF UPDATING THEN
IF ( :NEW.EFFECTIVITY_DATE <> :OLD.EFFECTIVITY_DATE OR
nvl(:NEW.DISABLE_DATE,:OLD.DISABLE_DATE+1) <> nvl(:OLD.DISABLE_DATE,:new.disable_date+1) OR
:NEW.COMPONENT_QUANTITY <> :OLD.COMPONENT_QUANTITY OR
:NEW.PLANNING_FACTOR <> :OLD.PLANNING_FACTOR OR
:NEW.COMPONENT_YIELD_FACTOR <> :OLD.COMPONENT_YIELD_FACTOR OR
:NEW.OPTIONAL <> :OLD.OPTIONAL OR
nvl(:NEW.IMPLEMENTATION_DATE,:old.implementation_date+1) <>
nvl(:OLD.IMPLEMENTATION_DATE,:new.implementation_date + 1)) THEN
UPDATE BOM_EXPLOSIONS BE
SET REXPLODE_FLAG = 1
WHERE BE.COMP_COMMON_BILL_SEQ_ID = :new.bill_sequence_id;
ELSE
UPDATE BOM_EXPLOSIONS BE
SET BE.ITEM_NUM = :new.ITEM_NUM,
BE.OPERATION_SEQ_NUM = :new.OPERATION_SEQ_NUM,
BE.CHECK_ATP = :new.CHECK_ATP,
BE.MUTUALLY_EXCLUSIVE_OPTIONS = :new.MUTUALLY_EXCLUSIVE_OPTIONS,
BE.SO_BASIS = :new.SO_BASIS,
BE.SHIPPING_ALLOWED = :new.SHIPPING_ALLOWED,
BE.REQUIRED_TO_SHIP = :new.REQUIRED_TO_SHIP,
BE.REQUIRED_FOR_REVENUE = :new.REQUIRED_FOR_REVENUE,
BE.INCLUDE_ON_SHIP_DOCS = :new.INCLUDE_ON_SHIP_DOCS,
BE.LOW_QUANTITY = :new.LOW_QUANTITY,-- added for bug 4451904
BE.HIGH_QUANTITY = :new.HIGH_QUANTITY
WHERE BE.COMPONENT_SEQUENCE_ID = :new.COMPONENT_SEQUENCE_ID;
END IF;
/*
UPDATE BOM_EXPLOSIONS_ALL BE
SET REXPLODE_FLAG = 1
WHERE BE.COMP_COMMON_BILL_SEQ_ID = :new.bill_sequence_id;
*/
IF ( :NEW.EFFECTIVITY_DATE <> :OLD.EFFECTIVITY_DATE OR
nvl(:NEW.DISABLE_DATE,:OLD.DISABLE_DATE+1) <> nvl(:OLD.DISABLE_DATE,:new.disable_date+1) OR
:NEW.COMPONENT_QUANTITY <> :OLD.COMPONENT_QUANTITY OR
:NEW.PLANNING_FACTOR <> :OLD.PLANNING_FACTOR OR
:NEW.COMPONENT_YIELD_FACTOR <> :OLD.COMPONENT_YIELD_FACTOR OR
:NEW.REVISED_ITEM_SEQUENCE_ID <> :OLD.REVISED_ITEM_SEQUENCE_ID OR
:NEW.FROM_END_ITEM_UNIT_NUMBER <> :OLD.FROM_END_ITEM_UNIT_NUMBER OR
nvl(:NEW.TO_END_ITEM_UNIT_NUMBER,'-1') <> nvl(:OLD.TO_END_ITEM_UNIT_NUMBER,'-1') OR
nvl(:NEW.COMPONENT_ITEM_REVISION_ID,-1) <> nvl(:OLD.COMPONENT_ITEM_REVISION_ID,-1) OR
nvl(:NEW.SUPPLY_LOCATOR_ID,-1) <> nvl(:OLD.SUPPLY_LOCATOR_ID,-1) OR
--:NEW.FROM_BILL_REVISION_ID <> :OLD.FROM_BILL_REVISION_ID OR
--:NEW.TO_BILL_REVISION_ID <> :OLD.TO_BILL_REVISION_ID OR
:NEW.FROM_END_ITEM_REV_ID <> :OLD.FROM_END_ITEM_REV_ID OR
nvl(:NEW.TO_END_ITEM_REV_ID,-1) <> nvl(:OLD.TO_END_ITEM_REV_ID,-1) OR
:NEW.FROM_OBJECT_REVISION_ID <> :OLD.FROM_OBJECT_REVISION_ID
--:NEW.FROM_MINOR_REVISION_ID <> :OLD.FROM_MINOR_REVISION_ID OR
--:NEW.TO_OBJECT_REVISION_ID <> :OLD.TO_OBJECT_REVISION_ID OR
--:NEW.TO_MINOR_REVISION_ID <> :OLD.TO_MINOR_REVISION_ID OR
--:NEW.FROM_END_ITEM_MINOR_REV_ID <> :OLD.FROM_END_ITEM_MINOR_REV_ID OR
--:NEW.TO_END_ITEM_MINOR_REV_ID <> :OLD.TO_END_ITEM_MINOR_REV_ID OR
--:NEW.COMPONENT_MINOR_REVISION_ID <> :OLD.COMPONENT_MINOR_REVISION_ID
OR nvl(:NEW.IMPLEMENTATION_DATE,:old.implementation_date+1) <> nvl(:OLD.IMPLEMENTATION_DATE,:new.implementation_date + 1)
) THEN /* Bug 5228583 */
UPDATE BOM_EXPLOSIONS_ALL BE
SET REXPLODE_FLAG = 1
WHERE BE.COMP_COMMON_BILL_SEQ_ID = :new.bill_sequence_id;
END IF;
END IF;
IF DELETING THEN
UPDATE BOM_EXPLOSIONS BE
SET REXPLODE_FLAG = 1
WHERE BE.COMP_COMMON_BILL_SEQ_ID = :old.bill_sequence_id;
UPDATE BOM_EXPLOSIONS_ALL BE
SET REXPLODE_FLAG = 1
WHERE BE.COMP_COMMON_BILL_SEQ_ID = :old.bill_sequence_id;
END IF;
END IF;
EXCEPTION
when others then
error_msg := 'BOMTBICX ' || substrb(SQLERRM, 1, 60);
raise_application_error(-20500, error_msg);
END;
[Home] [Help]