1 PACKAGE BODY PO_SECURITY_CHECK_SV as
2 /* $Header: POXSCHKB.pls 120.0.12000000.2 2007/09/25 15:37:11 adbharga ship $ */
3
4 /*============================= PO_SECURITY_CHECK_SV ===============================*/
5
6
7 /*===========================================================================
8
9 PROCEDURE NAME: check_before_lock()
10
11 ===========================================================================*/
12
13 function check_for_delegation (x_itemtype in varchar2,
14 x_itemkey in varchar2,
15 x_logged_emp_id in number,
16 x_action_emp_id in number) return boolean;
17
18 PROCEDURE check_before_lock (x_type_lookup_code in varchar2,
19 x_object_id in number,
20 x_logged_emp_id in number,
21 x_modify_action in out NOCOPY boolean) IS
22
23 x_can_approver_modify_flag varchar2(25);
24 x_progress varchar2(3);
25 x_last_action_emp_id number;
26 x_document_type_code varchar2(25);
27 x_document_subtype varchar2(25);
28 x_count number := 0;
29
30 l_authorization_status varchar2(25);
31
32 x_wf_item_type po_headers_all.wf_item_type%type := NULL;
33 x_wf_item_key po_headers_all.wf_item_key%type := NULL; -- Modified for Bug 2783162
34
35 BEGIN
36
37 IF x_type_lookup_code = 'RELEASE' THEN
38
39 x_document_type_code := 'RELEASE';
40 x_document_subtype := 'BLANKET';
41
42 ELSE
43
44 x_document_subtype := x_type_lookup_code;
45 IF x_type_lookup_code IN ('STANDARD', 'PLANNED') THEN
46 x_document_type_code := 'PO';
47 ELSIF x_type_lookup_code IN ('BLANKET', 'CONTRACT') THEN
48 x_document_type_code := 'PA';
49 ELSIF x_type_lookup_code IN ('INTERNAL', 'PURCHASE') THEN
50 x_document_type_code := 'REQUISITION';
51 ELSIF x_type_lookup_code = 'SCHEDULED' THEN
52 x_document_type_code := 'RELEASE';
53 ELSE
54 x_document_type_code := null;
55 END IF;
56
57 END IF;
58
59 /* Bug# 1266226
60 ** Desc: When a document is INCOMPLETE, we need not verify that
61 ** the action history exists or not. The INCOMPLETE status
62 ** signifies that the document has not been submitted for
63 ** approval or has been returned in original status without
64 ** any approval action (approve, forward) being taken on it
65 ** when the document was first submitted for the approval
66 ** process.
67 **
68 ** If the authorization status of the doc is INCOMPLETE
69 ** set x_modify_action := TRUE and return.
70 */
71
72 x_progress := '005';
73
74 IF x_document_type_code='REQUISITION' THEN
75
76 x_progress := '006'; -- Added for bug 2783162
77 select NVL(AUTHORIZATION_STATUS, 'INCOMPLETE'),
78 wf_item_type, wf_item_key
79 into l_authorization_status, x_wf_item_type, x_wf_item_key
80 from po_requisition_headers_all
81 where REQUISITION_HEADER_ID = x_object_id;
82
83 ELSIF x_document_type_code IN ('PO','PA') THEN
84 x_progress := '007'; -- Added for bug 2783162
85
86 select NVL(AUTHORIZATION_STATUS, 'INCOMPLETE'),
87 wf_item_type, wf_item_key
88 into l_authorization_status, x_wf_item_type, x_wf_item_key
89 from po_headers_all
90 where PO_HEADER_ID = x_object_id;
91
92 ELSIF x_document_type_code = 'RELEASE' THEN
93 x_progress := '008'; -- Added for bug 2783162
94
95 select NVL(AUTHORIZATION_STATUS, 'INCOMPLETE'),
96 wf_item_type, wf_item_key
97 into l_authorization_status, x_wf_item_type, x_wf_item_key
98 from po_releases_all
99 where PO_RELEASE_ID = x_object_id;
100
101 END IF;
102
103 /* Bug# 2454444: kagarwal
104 ** Desc: When a document is in REQUIRES REAPPROVAL status, we need not verify
105 ** the action history. The REQUIRES REAPPROVAL status signifies that the
106 ** document is NOT in between the Approval process hence the buyers can
107 ** modify the document depending on the Security setup.
108 */
109
110 IF ((l_authorization_status = 'INCOMPLETE') OR
111 (l_authorization_status = 'REQUIRES REAPPROVAL')) THEN
112 x_modify_action := TRUE;
113 return;
114 END IF;
115
116 /* End Fix Bug# 1266226
117 */
118
119
120 -- First check whether there has been any action at all on the
121 -- document. If there has been no action then the document is
122 -- still with the creator (no approval actions) and he can modify.
123
124 x_progress := '010';
125
126 select count(*) into x_count
127 from po_action_history pah
128 where
129 pah.object_id = x_object_id and
130 pah.object_type_code = x_document_type_code and
131 pah.object_sub_type_code = x_document_subtype and
132 pah.action_code is null;
133
134 if x_count = 0 then -- no records in po_action_history
135 -- dbms_output.put_line('Count is zero');
136 x_modify_action := TRUE;
137 return;
138
139 else
140 -- check the approver can modify flag
141
142 x_progress := '020';
143
144 select nvl(podt.can_approver_modify_doc_flag,'N')
145 into x_can_approver_modify_flag
146 from po_document_types podt
147 where
148 podt.document_type_code = x_document_type_code and
149 podt.document_subtype = x_document_subtype;
150
151 IF x_can_approver_modify_flag = 'Y' then
152
153 -- dbms_output.put_line('approver may modify');
154 -- making the assumption that max(sequence_num) would have null action code
155 -- select the last emp_id
156
157 x_progress := '030';
158 select EMPLOYEE_ID
159 into x_last_action_emp_id
160 from po_action_history pah
161 where
162 pah.object_id = x_object_id and
163 pah.object_type_code = x_document_type_code and
164 pah.object_sub_type_code = x_document_subtype and
165 pah.action_code is null;
166 IF x_last_action_emp_id = x_logged_emp_id THEN
167
168 -- dbms_output.put_line('The employee ids are the same');
169 x_modify_action := TRUE;
170 return;
171
172 ELSE
173
174 -- dbms_output.put_line('The employee ids are not the same');
175
176 /* Bug# 2559747: kagarwal
177 ** Check if this is a delegated doc and if the logged user is the recipient_role
178 ** of the notification then allow the user to modify the doc.
179 */
180 If ((x_wf_item_type is not NULL) AND (x_wf_item_key is not NULL)) Then
181 If (check_for_delegation(x_wf_item_type, x_wf_item_key,
182 x_logged_emp_id, x_last_action_emp_id)) Then
183 x_modify_action := TRUE;
184 return;
185 End If;
186 End If;
187
188 x_modify_action := FALSE;
189 return;
190
191 END IF;
192
193 ELSE
194
195 -- dbms_output.put_line('Approver cannot modify document');
196 x_modify_action := FALSE;
197 return;
198
199 END IF; -- x_can_approver_modify_flag = Y
200 -- dbms_output.put_line('No records were found in po_action_history');
201 -- dbms_output.put_line('We have a problem here');
202
203 END IF; -- count(*)
204
205 EXCEPTION
206 WHEN OTHERS THEN
207 -- dbms_output.put_line('Errors');
208 NULL;
209 /* Bug 2814939 - removed RAISE introduced in bug 2783162
210 The problem with doing this is that this proc is called in
211 many places, and they were not designed to handle raised exceptions
212 from this procedure. May be refactored in the future.
213 -- Added for bug 2783162
214 -- PO_MESSAGE_S.sql_error('check_before_lock', X_progress, sqlcode);
215 -- RAISE;
216 */
217
218 end check_before_lock;
219
220 /* Bug# 2559747: kagarwal
221 ** Added new function check_for_delegation to validate that
222 ** the delegated user is the logged employee.
223 */
224
225 function check_for_delegation (x_itemtype in varchar2,
226 x_itemkey in varchar2,
227 x_logged_emp_id in number,
228 x_action_emp_id in number)
229 return boolean IS
230
231 /* use MAX to get the latest notification sent for the wf item */
232
233 CURSOR getcurrnotif is
234 SELECT NVL(MAX(wf.notification_id), -9995)
235 FROM WF_NOTIFICATIONS WF,
236 WF_ITEM_ACTIVITY_STATUSES WIAS
237 WHERE WIAS.ITEM_TYPE = x_itemtype AND
238 WIAS.ITEM_KEY = x_itemkey AND
239 WIAS.NOTIFICATION_ID = WF.group_id;
240
241 CURSOR c_responderid(p_responder VARCHAR2) IS
242 SELECT nvl((wfu.orig_system_id), -9996)
243 FROM wf_users wfu
244 WHERE wfu.name = p_responder
245 AND wfu.orig_system not in ('HZ_PARTY', 'POS', 'ENG_LIST', 'CUST_CONT');
246
247 -- Bug 4633202: Cursor to get list of all granters who has provided worklist
248 -- access to current employee. It also lists the current user.
249
250 CURSOR c_worklist_grants IS
251 SELECT PARAMETER1 AS granter_key
252 FROM FND_GRANTS g,
253 fnd_menus m,
254 fnd_objects o,
255 fnd_object_instance_sets s
256 WHERE g.MENU_ID = m.menu_id
257 AND m.menu_name = 'FND_WF_WORKLIST'
258 AND g.OBJECT_ID = o.object_id
259 AND o.obj_name = 'NOTIFICATIONS'
260 AND g.INSTANCE_SET_ID = s.instance_set_id
261 AND s.instance_set_name = 'WL_PROXY_ACCESS'
262 AND g.GRANTEE_KEY = FND_GLOBAL.USER_NAME
263 AND g.INSTANCE_TYPE = 'SET'
264 AND g.START_DATE <= SYSDATE
265 AND Nvl(g.END_DATE, SYSDATE ) >= SYSDATE
266 UNION
267 SELECT FND_GLOBAL.USER_NAME AS granter_key
268 FROM dual;
269
270 -- Bug 4633202: Temp variables
271 l_notification_key wf_notifications.ACCESS_KEY%TYPE;
272 l_granter_key FND_GRANTS.PARAMETER1%TYPE;
273 l_has_access BOOLEAN;
274
275
276 l_notification_id WF_NOTIFICATIONS.notification_id%type;
277 l_responder WF_NOTIFICATIONS.responder%type;
278 l_original_recipient_id wf_users.orig_system_id%type;
279 l_recipient_id wf_users.orig_system_id%type;
280 l_recipient_role WF_NOTIFICATIONS.recipient_role%type;
281 l_original_recipient WF_NOTIFICATIONS.ORIGINAL_RECIPIENT%type;
282
283 Begin
284 Open getcurrnotif;
285 fetch getcurrnotif into l_notification_id;
286 close getcurrnotif;
287
288 If l_notification_id = -9995 Then
289 return FALSE;
290 End If;
291
292 -- Bug 4633202.
293 -- Loop for all granters and current user
294 l_has_access := FALSE;
295 FOR r_grants IN c_worklist_grants LOOP
296 BEGIN
297 -- Validate the access
298 l_granter_key := wf_advanced_worklist.Authenticate
299 ( username => r_grants.granter_key,
300 nid => l_notification_id,
301 nkey => l_notification_key
302 );
303 -- If we are here, means there is no exception. Access is validated.
304 -- Since one valid record is found, we can exit
305 l_has_access := TRUE;
306 EXIT;
307
308 EXCEPTION
309 WHEN OTHERS THEN
310 NULL; -- Loop through for other grants
311 END;
312 END LOOP;
313
314 RETURN l_has_access;
315
316 /* internal name of responder */
317 /* Bug 4633202.
318 This check is not required. WF API itself
319 will take care of all these checks.
320 SELECT wfn.recipient_role, wfn.original_recipient
321 INTO l_recipient_role, l_original_recipient
322 FROM wf_notifications wfn
323 WHERE wfn.notification_id = l_notification_id;
324
325 OPEN c_responderid(l_recipient_role);
326 FETCH c_responderid INTO l_recipient_id;
327 CLOSE c_responderid;
328
329 OPEN c_responderid(l_original_recipient);
330 FETCH c_responderid INTO l_original_recipient_id;
331 CLOSE c_responderid;
332
333 If ((l_recipient_id = x_logged_emp_id) AND
334 (l_original_recipient_id = x_action_emp_id)) Then
335 return TRUE;
336 End If;
337
338 return FALSE;
339 */
340 Exception
341 when others then
342 return FALSE;
343
344 End check_for_delegation;
345
346 END PO_SECURITY_CHECK_SV;
[Home] [Help]