Suppress Workflow Cancellation without using the global setting

Recently, I got an email asking me about whether there was a way to suppress the cancellation notification on an Item Type basis – the business had a requirement that for certain processes, the cancellation message needed to be sent, whereas for others, the message needed to be suppressed:

I am wondering if it is possible to stop the workflow cancellation from being sent to a user when he workflow is cancelled. I realize there is a global profile setting but I can’t do it globally. It is a custom workflow that I am developing. I was looking into possibly using the #wfm_cancel template and hard code an email address into the peformer but that didn’t seem to work. Has anyone done this type of thing before or have thoughts on how this might be achieved.

Some years back, the global preference didn’t even exist – the cancellation message was always sent, much to the consternation of some of my clients.  The way that we dealt with the problem to suppress all the cancellation messages was to create a database trigger which fired when the record in WF_NOTIFICATIONS is updated.

When the global preference was introduced, this trigger became irrelevant and was disabled.  However, if there is a requirement to provide a conditional suppression of the cancellation message, the trigger can be extended to provide this functionality.

Firstly, to remove any need for hard-coding a list of messages which need to have the cancellation message suppressed, define a value set which will contain the notifications which we want to suppress cancellation messages.  The values in the value set can be either the item type internal name (if all the messages in the item type should not have cancellation messages) or the internal names of the item type and the message with a colon delimiter if a specific message should have the cancellation suppressed.

Once the values have been defined in the value set, then create a trigger on WF_NOTIFICATIONS to check whether the message is in the value set.  If it is in the value set, and a response is not required, and the notification is being cancelled, then update the record so that the notification appears to have been sent already by the notification mailer:

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;

As ever, if you are relying on the code sample here, it is provided without any warranty – test it fully before deploying to a production environment.

SQL script

2 thoughts on “Suppress Workflow Cancellation without using the global setting

  1. In general my understanding that database triggers are not sanctioned method of extending Oracle Applications. I see some of your solutions involve the use of triggers. Can you offer suggestion without using database triggers for supressing Workflow Cancellation notifications

  2. Oracle documentation says “We strongly advise against the use of database triggers”, but Oracle don’t (as far as I know) say that you can’t use triggers.

Comments are closed.

By continuing to use the site, you agree to the use of cookies. more information

In common with almost all professionally run websites, this website logs the IP address of each visitor in order to keep it running reliably. This is also essential for protecting the website and its visitors from malicious attacks, including infection with malware.

This website provides information as a service to visitors such as yourself, and to do this reliably and efficiently, it sometimes places small amounts of information on your computer or device (e.g. mobile phone). This includes small files known as cookies. The cookies stored by this website cannot be used to identify you personally.

We use cookies to understand what pages and information visitors find useful, and to detect problems such as broken links, or pages which are taking a long time to load.

We sometimes use cookies to remember a choice you make on one page, when you have moved to another page if that information can be used to make the website work better. For example:
- avoiding the need to ask for the same information several times during a session (e.g. when filling in forms), or
- remembering that you have logged in, so that you don’t have to re-enter your username and password on every page.

You can prevent the setting of cookies by adjusting the settings on your browser (see your browser Help for how to do this). Be aware that disabling cookies will affect the functionality of this and many other websites that you visit.

Close