I’ve just read a post on the Workflow forums in Metalink asking about what the SQL is to display the history at the bottom of a Notification – there is nothing in the WF_ITEM_ATTRIBUTE_STATUSES_H for the history, so where does it reside?

The history is recorded as a comment in the WF_COMMENTS table, which you need to join to from the WF_NOTIFICATIONS table to get the history.  However, the optimum was to get the history is to use the API (as ever!) that Oracle have provided: WF_NOTIFICATION.GetComments or WF_NOTIFICATION.GetComments2.

Since the original question was looking for a SELECT statement to retrieve the contents, I wrapped this in a function which can be called with the notification ID and returns the history either as plain text or as HTML that can then be embedded somewhere:

  1  CREATE OR REPLACE FUNCTION get_hist ( p_nid       IN PLS_INTEGER
  2          , p_disp_type IN VARCHAR2 DEFAULT WF_NOTIFICATION.doc_text )
     RETURN VARCHAR2 AS
  3    v_hist VARCHAR2(32767);
  4  BEGIN
  5     Wf_Notification.GetComments2 ( p_nid => p_nid
  6                                  , p_display_type =>
          NVL(p_disp_type,WF_NOTIFICATION.doc_text)
  7                                  , p_hide_reassign => 'Y'
  8                                  , p_hide_requestinfo => 'Y'
  9                                  , p_action_history => v_hist );
 10
 11    RETURN v_hist;
 12
 13  END get_hist;
 14/

Then you can just call it in a select statement (or in PL/SQL), e.g.

APPS@DEV on 18-SEP-2008 10:43:34> select get_hist(1574024) from dual;

GET_HIST(1574024)
------------------------------------------------------------------------------------------------------------------------
Action History
Num : Action Date : Action : From : To : Details
1 : 17-JUL-2008 12:03:45 : Submit : SYSADMIN : Searle, Matthew :
2 : 18-SEP-2008 10:21:00 : Request Information : Searle, Matthew : MATT2 : Hello

I’m not going to show an output of the HTML, but you can retrieve it by calling GET_HIST(<nid>,’text/html’) instead of the default parameter.

Matt

This entry was posted on Thursday, September 18th, 2008 at 10:51 am and is filed under 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.