Displaying the history of a Notification
A thread on OTN recently (which I missed while I was ill) asked about displaying the history of a notification, and when I got round to it, I recalled the same thing being asked in September on Metalink here.
However, I knew that I’d addressed the problem somewhere, but didn’t know whether it was on OTN, WorkflowFAQ or on Metalink. So, to make sure that I can find it again, here’s the contents of the post from Metalink:
From: Dmitry Lidvansky 18-Sep-08 09:29
Subject: how to select notification history (?)
I need a select statement that returns notification history as it shown in notification:
http://www.4freeimagehost.com/show.php?i=b2d4948a2dbf.png (see image)
please help
it seems that WF_ITEM_ACTIVITY_STATUSES/WF_ITEM_ACTIVITY_STATUSES_H does not contain all neccessary information for example, Delegate action
From: Matthew Searle 18-Sep-08 10:39
Subject: Re : how to select notification history (?)
Hi,
This should do the trick:
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*
APPS@SOLDEV2 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 : SEARLEM4B : Hello
From: Dmitry Lidvansky 19-Sep-08 10:10
Subject: Re : Re : how to select notification history (?)
3 v_hist VARCHAR2(32767);
that means notification history cannot be longer than 32767 ?
what if it will be so ?
From: Matthew Searle 22-Sep-08 11:50
Subject: Re : Re : Re : how to select notification history (?)
If it’s over 32767, then it will error 
The API returns a VARCHAR2, so that’s the limit – I’ve not tested it to see what happens if it’s too long.
If you want something that will cater for something bigger than 32K then you will need to look at the underlying code and pick out the cursor(s) it uses, then use that instead.
So, if you need to know how to display the full history of a notification from the notification ID, that’s how to do it.
Leave a Reply