CREATE OR REPLACE TRIGGER UBR_POB_VENDOR_CLAIM BEFORE UPDATE ON POB_VENDOR_CLAIM FOR EACH ROW
DECLARE
iLastUpdatedSourceCount NUMBER;
BEGIN
  SELECT COUNT(1)
    INTO iLastUpdatedSourceCount
  FROM 
    POB_USR PU,
  	POB_CHANNEL PC
  WHERE
  	PU.POB_USR_ID = :NEW.LAST_UPDATED_SOURCE AND
  	PU.POB_CHANNEL_ID = PC.POB_CHANNEL_ID AND
  	PC.CHANNEL_ID IN (1,5);
  IF (:NEW.POB_VENDOR_SHIP_ADDRESS <> :OLD.POB_VENDOR_SHIP_ADDRESS
  OR NVL(:NEW.POB_VENDOR_SHIP_ADDRESS_2,'0') <> NVL(:OLD.POB_VENDOR_SHIP_ADDRESS_2,'0')
  OR :NEW.VLD_COUNTRY_ID <> :OLD.VLD_COUNTRY_ID
  OR NVL(:NEW.POB_REGION,'0') <> NVL(:OLD.POB_REGION,'0')
  OR :NEW.POB_CITY <> :OLD.POB_CITY
  OR :NEW.POB_SHIP_POSTAL <> :OLD.POB_SHIP_POSTAL
  OR NVL(:NEW.POB_VENDOR_PAYS_FREIGHT,2) <> :OLD.POB_VENDOR_PAYS_FREIGHT
  OR :NEW.POB_DC_END_DTTM <> :OLD.POB_DC_END_DTTM
  OR :NEW.POB_CLAIM_TOTAL <> :OLD.POB_CLAIM_TOTAL)
  AND (:OLD.POB_VENDOR_CLAIM_STATUS <> 10)
  AND :NEW.INACTIVE = 0
  AND iLastUpdatedSourceCount = 1
  THEN
	:NEW.POB_VENDOR_CLAIM_STATUS := 20;
  :NEW.POB_EMAIL_SENT := 0;
  UPDATE POB_VENDOR_CLAIM_APPROVAL 
  SET POB_VENDOR_APPROVAL_STATUS = NULL
  WHERE POB_VENDOR_CLAIM_ID = (SELECT 
                                 POB_VENDOR_CLAIM_ID 
                               FROM 
                                 POB_VENDOR_CLAIM 
                               WHERE 
                                 POB_VENDOR_CLAIM_ID = :NEW.POB_VENDOR_CLAIM_ID AND
                                 INACTIVE = 0);
  END IF;
END;

UPDATE POB_VENDOR_CLAIM_APPROVAL 
  SET POB_VENDOR_APPROVAL_STATUS = NULL
  WHERE POB_VENDOR_CLAIM_ID = :NEW.POB_VENDOR_CLAIM_ID AND
                                 INACTIVE = 0

IF :NEW.inactive = 0 THEN
  UPDATE POB_VENDOR_CLAIM_APPROVAL 
  SET POB_VENDOR_APPROVAL_STATUS = NULL
  WHERE POB_VENDOR_CLAIM_ID = :NEW.POB_VENDOR_CLAIM_ID;
END IF;