I was asked recently whether there was any way to stop one particular notification going to one particular user via email – the user wanted to receive all their other notifications via email, but not this one.

Since the user wanted to receive all their other notifications via email, it wasn’t a case of being able to change their notification preference, since that would stop all the others going out as well.  We could consider flicking the profile before sending the notification and then changing it back immediately afterwards, but I’m not convinced that this would work.  Also, it’s a pretty intrusive change to make to process, since you would need new logic before and after the notification activity.

So, there are two other ways that the client could do this.

Firstly, they could write a trigger on WF_NOTIFICATIONS which fires when a new notification is created.  The trigger would check whether the recipient is the user we are interested in and the notification type being created, and if so immediately set the MAIL_STATUS to SENT.  This would fool the mailer into believing that the notification had already been emailed, so would never go out via email.

The second approach would be to change the Workflow definition and include a post notification function for the one notification which performs the same logic – if the user is the one person who doesn’t want to receive the notification via email, then update the MAIL_STATUS to SENT again to stop the email being sent.

Both of these approaches have pros and cons.  Creating a trigger on the table may introduce a performance overhead – every notification which is created would need to be checked to determine whether to suppress the email being sent.  Creating a post-notification function means that changes need to be made to the Workflow definition, and the support guidelines would need to be checked before making changes to a seeded notification.  Also, if the notification already has a post notification function, then you would need to either change that PNF, or create a wrapper which calls the original PNF and then does the new logic.

There were three questions that I put to the client before recommending an approach to adopt:

1. Is it possible / likely that more than one user will want this functionality?
2. Is is possible / likely that the users might change their minds about whether they want this enabled or disabled?
3. Is it possible / likely that the user(s) might want to include different notifications?

If the answer to the last question is “No”, then I would change the existing Workflow definition and include a post notification function.  If the answers to the other questions are “Yes”, but it still only applies to one notification, then I would still create a PNF to perform the logic, but build extra flexibility into the logic.  For example, I would also have a profile option set at the right level which indicates whether emails should be suppressed or not, which provides flexibility and ease of use to enable / disable the suppression of emails via the front-end screens rather than needing a coding change.

If, however, the answer to the last question is “Yes”, then I would define a trigger on the table which can then fire under multiple scenarios.  I would define a value set which dictates which notifications should be suppressed (either for a whole item type or on an individual notification basis), and have the trigger check the value set first to see whether this notification is one where email might not be needed.  If the notification is one which shouldn’t be emailed, then again I would have a profile option for the user / responsibility / org / site which determines whether this particular notification should not be emailed.  This may be more processor intensive than a quick and easy check of one thing, but it provides the most flexibility to the solution – no coding changes are required to add / remove notifications or people to the list, which means that changes can be implemented quickly and easily without the need for further testing to be performed.

In the end, the client adopted the post notification function approach and decided not to make it widely known that this was a possibility.  It may be that in future years, more notifications / users are required, at which point they should revisit the recommendations and switch to the trigger approach.  There is nothing wrong with the way they have gone – it meets their current requirements, but if those requirements change significantly in the future then a review will be required.

This entry was posted on Friday, October 7th, 2011 at 9:06 am and is filed under Functional, Oracle, Technical. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.