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