Triiger

CREATE OR REPLACE TRIGGER XX.TT_OE_CUSTOM_ALERT
AFTER INSERT OR UPDATE ON APPS.OE_ORDER_HEADERS_ALL
FOR EACH ROW
WHEN (NEW.ATTRIBUTE16 IS NOT NULL AND NEW.FLOW_STATUS_CODE = 'BOOKED')
DECLARE

V TT_CUSTOM_ALERTS%ROWTYPE;



BEGIN



SELECT *

INTO   V

FROM  TT_CUSTOM_ALERTS

WHERE  ORDER_NUMBER = :NEW.ORDER_NUMBER

AND      ORDER_TYPE_ID = :NEW.ORDER_TYPE_ID;



IF :NEW.ATTRIBUTE16 != :OLD.ATTRIBUTE16 THEN



UPDATE TT_CUSTOM_ALERTS

SET      NO_TIMES_UDT = 3

WHERE  ORDER_NUMBER = :NEW.ORDER_NUMBER

AND      ORDER_TYPE_ID = :NEW.ORDER_TYPE_ID;



END IF;



EXCEPTION WHEN NO_DATA_FOUND THEN

INSERT INTO ITW_CUSTOM_ALERTS (ORDER_NUMBER,LIKELY_DISPATCH_DATE,NO_TIMES,ORDER_TYPE_ID,NO_TIMES_UDT)
                              VALUES(:NEW.ORDER_NUMBER,
                                              :NEW.ATTRIBUTE16,
                                                  1,
                                                  :NEW.ORDER_TYPE_ID,1);
END;