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.