CREATE OR REPLACE TRIGGER XXX_STOP_CANCEL_MSG
BEFORE UPDATE ON WF_NOTIFICATIONS
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW

DECLARE

  dummy PLS_INTEGER;
  v_suppress_ntf          VARCHAR2(10);

  CURSOR c_check_value_set ( cp_vs_name   IN VARCHAR2
                           , cp_type_name IN VARCHAR2
                           , cp_msg_name  IN VARCHAR2 ) IS
    SELECT 'EXISTS'
    FROM   fnd_flex_values_vl       ffvv
    JOIN   fnd_flex_value_sets      ffvs ON ( ffvv.flex_value_set_id = ffvs.flex_value_set_id )
    WHERE  ffvs.flex_value_set_name = cp_vs_name
    AND    (    ffvv.flex_value = cp_type_name
            OR  ffvv.flex_value = cp_type_name||':'||cp_msg_name
           )
    AND    NVL(ffvv.enabled_flag,'N') = 'Y'
    AND    SYSDATE BETWEEN NVL(ffvv.start_date_active,SYSDATE - 1) AND NVL(ffvv.end_date_active,SYSDATE + 1);

BEGIN

  IF     :new.status      = 'CANCELED'
     AND :old.status      = 'OPEN'
     AND :new.mail_status = 'MAIL'
     AND :old.mail_status = 'SENT' THEN
  
    BEGIN
      -- Check whether the notification type is in the Value Set
      OPEN  c_check_value_set ( cp_vs_name   => 'XXX_SUPPRESS_LIST_VS'
                              , cp_type_name => :new.message_type
                              , cp_msg_name  => :new.message_name );
      FETCH c_check_value_set INTO v_suppress_ntf;
      CLOSE c_check_value_set;
      
      IF v_suppress_ntf = 'EXISTS' THEN
        -- Record is included in the value set, so need to include it
        -- Check that the notification expects a response otherwise do not cancel it
    
        SELECT 1
        INTO   dummy
        FROM   DUAL
        WHERE  EXISTS ( SELECT NULL
                        FROM   wf_message_attributes wma
                        WHERE  wma.message_name = :new.message_name
                        AND    wma.message_type = :new.message_type
                        AND    wma.subtype = 'RESPOND' );
        
        :new.mail_status := 'SENT';
      END IF;
  
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        -- Not a response, so no need to cancel it
        NULL;
    END;
  END IF;

END;
/