1 PACKAGE BODY por_notification_pkg AS
2 /* $Header: PORNOTFB.pls 115.4 2002/11/19 00:46:01 jjessup ship $ */
3 /*
4 * For SSP5 Home page
5 */
6 procedure getTodoNotifications(
7 username in varchar2 default null,
8 subject1 out nocopy varchar2,
9 subject2 out nocopy varchar2,
10 subject3 out nocopy varchar2,
11 nid1 out nocopy varchar2,
12 nid2 out nocopy varchar2,
13 nid3 out nocopy varchar2,
14 display_more out nocopy varchar2)
15 as
16 subject_d varchar2(240):= '@#$%';
17 dummy_nid pls_integer := -999;
18 dummy_nid1 pls_integer := -999;
19 dummy_subject varchar2(240);
20 dummy_subject2 varchar2(240);
21 origSystem varchar2(240);
22 origSystemId number;
23
24 cursor c_user(user in varchar2) is
25 select orig_system_id, orig_system
26 from wf_users
27 where name = user
28 and orig_system not in ('POS', 'ENG_LIST', 'CUST_CONT');
29
30 cursor attrs(user in varchar2, origSys in varchar2, origId in number) is
31 select WN.notification_id
32 from WF_NOTIFICATIONS WN
33 where WN.RECIPIENT_ROLE in
34 ((select user from dual) union all
35 (select role_name from wf_user_roles
36 where user_name = user and
37 user_orig_system = origSys and user_orig_system_id = origId ))
38 and wn.message_type in
39 ( select distinct WF_CREATEDOC_ITEMTYPE
40 from PO_DOCUMENT_TYPES
41 union
42 select distinct WF_APPROVAL_ITEMTYPE
43 from PO_DOCUMENT_TYPES
44 union
45 select 'PORCPT' from dual)
46 and wn.status = 'OPEN'
47 and exists( select 1 from WF_NOTIFICATION_ATTRIBUTES NA,
48 WF_MESSAGE_ATTRIBUTES_VL MA,
49 WF_NOTIFICATIONS N
50 where
51 N.NOTIFICATION_ID=WN.NOTIFICATION_ID
52 and NA.NOTIFICATION_ID = N.NOTIFICATION_ID
53 and MA.MESSAGE_NAME = N.MESSAGE_NAME
54 and MA.MESSAGE_TYPE = N.MESSAGE_TYPE
55 and MA.NAME = NA.NAME
56 and MA.SUBTYPE = 'RESPOND'
57 and MA.TYPE <> 'FORM')
58 and sysdate - wn.begin_date <=60
59 order by WN.notification_id desc;
60
61
62 begin
63 /* no need to authenticate in this routine because we're getting the notifications based on the username, so of course this user has access to these notifications */
64 open c_user(username);
65 fetch c_user into origSystemId, origSystem;
66 close c_user;
67
68 open attrs(username, origSystem, origSystemId);
69 fetch attrs into dummy_nid;
70
71 if (dummy_nid <>-999) then
72 nid1 := to_char(dummy_nid);
73 subject1 := wf_notification.Getsubject(dummy_nid);
74 end if;
75 dummy_nid := -999;
76 fetch attrs into dummy_nid;
77
78 if (dummy_nid <>-999) then
79 nid2 := to_char(dummy_nid);
80 subject2 := wf_notification.Getsubject(dummy_nid);
81 end if;
82 dummy_nid := -999;
83 fetch attrs into dummy_nid;
84
85 if (dummy_nid <> -999) then
86 nid3 := to_char(dummy_nid);
87 subject3 := wf_notification.Getsubject(dummy_nid);
88 end if;
89
90 fetch attrs into dummy_nid1;
91 if (dummy_nid1 <> -999) then
92 display_more := '1';
93 else
94 display_more := '0';
95 end if;
96 close attrs;
97
98
99 exception
100 when others then
101 -- Wf_Core.Context('wfa_html_jsp', 'getTruncatedSubjects');
102 raise;
103 end getTodoNotifications;
104
105 procedure getNotificationSubjects(
106 nid1 in integer,
107 nid2 in integer,
108 nid3 in integer,
109 subject1 out nocopy varchar2,
110 subject2 out nocopy varchar2,
111 subject3 out nocopy varchar2) as
112 begin
113
114 if (nid1 <>-999) then
115 subject1 := wf_notification.Getsubject(nid1);
116 end if;
117 if (nid2 <>-999) then
118 subject2 := wf_notification.Getsubject(nid2);
119 end if;
120 if (nid3 <>-999) then
121 subject3 := wf_notification.Getsubject(nid3);
122 end if;
123
124
125 exception
126 when others then
127 raise;
128 end getNotificationSubjects;
129
130 end por_notification_pkg;
[Home] [Help]