Who Started This? – Determining the Initiator of a Notification Thread

Introduction

When logging into Oracle EBS as a user who has a part in a workflow process, such as an approver in a purchase order, that user could easily determine the owner or initiator of the request by simply opening the notification and reviewing the action history.  The sysadmin user could also view the action history of the notification.

But that is a visual (UI) display of the initiator info and not something that could be captured in hand.  For example, there may be a need for a third-party audit or metrics collection or perhaps that info is to be used in custom code or script such as in another workflow process.  So, how would one obtain that single piece of info quicker and in hand?

The user who started a notification thread would be the one listed in the WF_COMMENTS table with an action of SEND_FIRST.  Of course, one could query the WF_COMMENTS table to determine the initiator while correlating with the WF_NOTIFICATIONS table based on the notification.  But it may not be that straightforward and quick because a typical single transaction involving multiple users and notifications (requesting more info, delegating, approving, etc.) would have multiple notification IDs.  Then multiply that by the fact there would be many more than a single transaction in a real-life production system where even a sequential list of notification IDs is not just for a single workflow or notification thread.

Solution

A single transaction or notification thread can be identified by its context.  That context could be used to trace back who initiated it and that involves checking both current and non-current activities as there may be multiple related and unrelated transactions of the same workflow process.

The context could be obtained from the WF_NOTIFICATIONS table based on a particular notification ID, and then its individual components (item type, item key, and process activity ID) could be extracted via string functions and operators.

Based on the components of that context the following query could be used to easily obtain the initiator info.  Note that an activity could either be current or non-current so even with the union all operator there is no chance of getting multiple row returns from this query.

select FROM_ROLE
(
  select FROM_ROLE
    from WF_ITEM_ACTIVITY_STATUSES IAS,
         WF_COMMENTS C
    where IAS.ITEM_TYPE        = <item_type>
      and IAS.ITEM_KEY         = <item_key>
      and IAS.PROCESS_ACTIVITY = <process_activity_ID>
      and IAS.NOTIFICATION_ID  = C.NOTIFICATION_ID
      and C.ACTION             = ‘SEND_FIRST’
  union all
  select FROM_ROLE
    from WF_ITEM_ACTIVITY_STATUSES_H IAS,
         WF_COMMENTS C
    where IAS.ITEM_TYPE        = <item_type>
      and IAS.ITEM_KEY         = <item_key>
      and IAS.PROCESS_ACTIVITY = <process_activity_ID>
      and IAS.NOTIFICATION_ID  = C.NOTIFICATION_ID
      and C.ACTION             = ‘SEND_FIRST’
);


A self-contained PL/SQL block using the above query could be implemented to obtain the initiator of any notification thread from just a single notification ID belonging to that thread.

Practical Scenario

The following approval structure is set up but KWALKER has a vacation/routing rule to BPALMER:

[CBAKER] -> [CBROWN] -> [KWALKER] -> [BERICKSO]

Steps:

– CBAKER creates a request via iProcurement, which is sent to CBROWN.

– CBROWN requests info from CBAKER.

– CBAKER provides info to CBROWN.

– CBROWN approves request and is sent to next level (KWALKER).

– KWALKER’s routing rule kicks in and delegates approval to BPALMER.

– BPALMER approves request and is sent to next level (BERICKSO).

– BERICKSO manually delegates approval to WTUCKER.

– WTUCKER requests info from BERICKSO.

– BERICKSO provides info to WTUCKER.

– WTUCKER requests info from DLEWIS (perhaps another department).

– DLEWIS provides info to WTUCKER.

– WTUCKER finally approves request.

– CBAKER receives confirmation of approval.

Results:

Checking the WF_NOTIFICATIONS and WF_COMMENTS tables:

SQL> select notification_id nid, status, from_role, responder
  2    from WF_NOTIFICATIONS
  3    where notification_id >= 4681928
  4    order by notification_id;

NID     FROM_ROLE  TO_ROLE    ACTION          USER_COMMENT
——- ———- ———- ————— ——————————
4681928 CBAKER     CBROWN     SEND_FIRST
4681928 CBROWN     CBAKER     QUESTION        Request info from CBAKER.
4681928 CBAKER     CBROWN     ANSWER          Provide info to CBROWN.
4681928 CBROWN     KWALKER    RESPOND
4681929 CBROWN     KWALKER    SEND
4681929 KWALKER    BPALMER    DELEGATE_RULE   Delegate (route) to BPALMER.
4681929 BPALMER    BERICKSO   RESPOND
4681930 KWALKER    BERICKSO   SEND
4681930 BERICKSO   WTUCKER    DELEGATE        Delegate (manual) to WTUCKER.
4681930 WTUCKER    BERICKSO   QUESTION        Request info from BERICKSO.
4681930 BERICKSO   WTUCKER    ANSWER          Provide info to WTUCKER.
4681930 WTUCKER    DLEWIS     QUESTION        Request info from DLEWIS.
4681930 DLEWIS     WTUCKER    ANSWER          Provide info to WTUCKER.
4681930 WTUCKER    WF_SYSTEM  RESPOND
4681931 BERICKSO   CBAKER     SEND_FIRST


In this hypothetical scenario we might wonder what is special about this request such that BERICKSO chose to delegate approval to WTUCKER and then that was followed by a few back-and-forth questions and answers.  Thus, we would want to determine who the initiator is and question the user.  If this list was 100’s of lines long and mixed up with other transactions it would not be as obvious to determine who the initiator was although it might seem obvious from this short scenario.

By obtaining the context from the WF_NOTIFICATIONS table for the notification ID associated with the manual delegation from BERICKSO to WTUCKER (i.e. 4681930) and then plugging in the components of that context into the above query the initiator could be correctly identified as CBAKER.