1 package body WF_ADVANCED_WORKLIST as
2 /* $Header: wfadvwb.pls 120.2.12010000.3 2009/10/29 10:47:43 sudchakr ship $ */
3
4 -- Home page navigation is set false
5 Home_Page_Worklist boolean := false;
6 --
7 -- Authenticate (PUBLIC)
8 -- Verify user is allowed access to this notification
9 -- IN
10 -- nid - notification id
11 -- nkey - notification access key (if disconnected); currently unused
12 -- RETURNS
13 -- Current user name
14 --
15 function Authenticate(username in varchar2,
16 nid in number,
17 nkey in varchar2)
18 return varchar2
19 is
20 recipient varchar2(320);
21 orig_recipient varchar2(320);
22 from_role varchar2(320);
23 more_info_role varchar2(320);
24
25 dummy pls_integer;
26 admin_role varchar2(2000);
27 begin
28 if (nkey is null) then
29 admin_role := wf_core.translate('WF_ADMIN_ROLE');
30 -- Get recipient and original recipient of this notification
31 begin
32 select RECIPIENT_ROLE, ORIGINAL_RECIPIENT, FROM_ROLE, MORE_INFO_ROLE
33 into recipient, orig_recipient, from_role, more_info_role
34 from WF_NOTIFICATIONS WN
35 where WN.NOTIFICATION_ID = nid;
36 exception
37 when no_data_found then
38 Wf_Core.Token('NID', nid);
39 Wf_Core.Raise('WFNTF_NID');
40 end;
41
42 --first check whether the user is intended recipient or from role or more info role
43 -- Check if current user has WF_ADMIN_ROLE privileges.
44 if((Wf_Directory.IsPerformer(username, recipient)) OR
45 (Wf_Directory.IsPerformer(username, orig_recipient)) OR
46 (Wf_Directory.IsPerformer(username, more_info_role)) OR
47 (Wf_Directory.IsPerformer(username, from_role)) OR
48 (Wf_Directory.IsPerformer(username, admin_role)) OR
49 (admin_role = '*')) then
50 return(username);
51 else
52 -- Authenticate functionality to be in sync with "Notifications from me" view
53 begin
54 select 1
55 into dummy
56 from sys.dual
57 where exists ( select null
58 from wf_item_activity_statuses ias,
59 wf_item_activity_statuses_h iash,
60 wf_notifications ntf
61 where ntf.status = 'OPEN'
62 and ias.notification_id = ntf.notification_id
63 and ias.item_type = iash.item_type
64 and ias.item_key = iash.item_key
65 and ias.process_activity = iash.process_activity
66 and iash.notification_id in (select notification_id
67 from wf_notifications
68 where status in ('CLOSED','CANCELED','INVALID')
69 and from_role = username)
70 and ntf.notification_id = nid
71 );
72 exception
73 when no_data_found then
74 Wf_Core.Token('USER', username);
75 Wf_Core.Token('NID', to_char(nid));
76 Wf_Core.Raise('WFNTF_ACCESS_USER');
77 end;
78 end if;
79 end if;
80 return(username);
81 exception
82 when others then
83 wf_core.context('Wf_Advanced_Worklist_Html', 'Authenticate', to_char(nid), nkey);
84 raise;
85 end Authenticate;
86
87
88 procedure getInfoAfterDenorm( p_nid in number,
89 p_langcode in varchar2,
90 p_subject out nocopy varchar2,
91 p_touser out nocopy varchar2,
92 p_fromuser out nocopy varchar2)
93 is
94 begin
95 wf_notification.Denormalize_Notification(nid => p_nid, langcode => p_langcode );
96 select DECODE(MORE_INFO_ROLE, NULL, SUBJECT, FND_MESSAGE.GET_STRING('FND','FND_MORE_INFO_REQUESTED')||' '||SUBJECT) AS subject, to_user, from_user
97 into p_subject, p_touser, p_fromuser
98 from wf_notifications
99 where notification_id = p_nid;
100 exception
101 when OTHERS then
102 wf_core.context('Wf_Notification', 'getInfoAfterDenorm', p_nid);
103 raise;
104 end getInfoAfterDenorm;
105
106 --
107 -- Authenticate2 (PUBLIC)
108 -- Verify if user allowed access to this notification. This API takes into
109 -- consideration if the user being authenticated is a proxy to the original
110 -- notification recipient
111 -- IN
112 -- nid - notification id
113 -- nkey - notification access key (if disconnected); currently unused
114 -- RETURNS
115 -- Current user name
116 --
117 function Authenticate2(username in varchar2,
118 nid in number,
119 nkey in varchar2)
120 return varchar2
121 is
122 l_username varchar2(320);
123 dummy pls_integer;
124 begin
125
126 begin
127 return wf_advanced_worklist.Authenticate(username, nid, nkey);
128 exception
129 when others then
130 if (wf_core.error_name <> 'WFNTF_ACCESS_USER') then
131 raise;
132 end if;
133 end;
134
135 -- Perform authentication for proxy, if Authenticate had failed.
136 -- If Recipient Role or More Info Role is
137 -- 1. User - Proxy is grantee for the user.
138 -- 2. Role - Proxy is grantee of one of the users of the role.
139 begin
140 SELECT 1
141 INTO dummy
142 FROM dual
143 WHERE username IN
144 (
145 SELECT fg.grantee_key
146 FROM wf_notifications wn,
147 wf_user_roles wur,
148 fnd_grants fg
149 WHERE ((wn.more_info_role IS NOT NULL AND wur.role_name = wn.more_info_role)
150 OR wur.role_name = wn.recipient_role)
151 AND fg.parameter1 = wur.user_name
152 AND fg.menu_id IN
153 (SELECT menu_id
154 FROM fnd_menus
155 WHERE menu_name = 'FND_WF_WORKLIST')
156 AND fg.object_id IN
157 (SELECT object_id
158 FROM fnd_objects
159 WHERE obj_name = 'NOTIFICATIONS')
160 AND fg.instance_set_id IN
161 (SELECT instance_set_id
162 FROM fnd_object_instance_sets
163 WHERE instance_set_name = 'WL_PROXY_ACCESS')
164 AND fg.instance_type = 'SET'
165 AND fg.start_date <= sysdate
166 AND (fg.end_date IS NULL OR fg.end_date > sysdate)
167 AND (fg.parameter2 IS NULL OR
168 (fg.parameter2 IS NOT NULL AND
169 INSTR(','||replace(trim(fg.parameter2), ' ')||',',
170 ','||replace(trim(wn.message_type), ' ')||',') > 0)
171 )
172 AND wn.notification_id = nid
173 );
174 exception
175 when no_data_found then
176 Wf_Core.Token('USER', username);
177 Wf_Core.Token('NID', to_char(nid));
178 Wf_Core.Raise('WFNTF_ACCESS_USER');
179 end;
180
181 return (username);
182
183 exception
184 when others then
185 wf_core.context('Wf_Advanced_Worklist', 'Authenticate2', username, to_char(nid), nkey);
186 raise;
187 end Authenticate2;
188
189 procedure SetNavFromHomePage(isebizhomepage in number)
190 is
191 begin
192 if (isebizhomepage = 1) then
193 Home_Page_Worklist := true;
194 else
195 Home_Page_Worklist := false;
196 end if;
197 exception
198 when others then
199 raise;
200 end SetNavFromHomePage;
201
202
203 function GetNavFromHomePage return boolean is
204 begin
205 return Home_Page_Worklist;
206 end GetNavFromHomePage;
207
208 end WF_ADVANCED_WORKLIST;