Displaying the notification history

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

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