1 PACKAGE BODY PO_SECURITY_CHECK_SV as
2 /* $Header: POXSCHKB.pls 120.2.12020000.2 2013/02/10 19:29:49 vegajula 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),wf.from_role --Bug 12534279
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 GROUP BY wf.from_role; --Bug 14205891
241
242 CURSOR c_responderid(p_responder VARCHAR2) IS
243 SELECT nvl((wfu.orig_system_id), -9996)
244 FROM wf_users wfu
245 WHERE wfu.name = p_responder
246 AND wfu.orig_system not in ('HZ_PARTY', 'POS', 'ENG_LIST', 'CUST_CONT');
247
248 -- Bug 4633202: Cursor to get list of all granters who has provided worklist
249 -- access to current employee. It also lists the current user.
250
251 CURSOR c_worklist_grants IS
252 SELECT PARAMETER1 AS granter_key
253 FROM FND_GRANTS g,
254 fnd_menus m,
255 fnd_objects o,
256 fnd_object_instance_sets s
257 WHERE g.MENU_ID = m.menu_id
258 AND m.menu_name = 'FND_WF_WORKLIST'
259 AND g.OBJECT_ID = o.object_id
260 AND o.obj_name = 'NOTIFICATIONS'
261 AND g.INSTANCE_SET_ID = s.instance_set_id
262 AND s.instance_set_name = 'WL_PROXY_ACCESS'
263 AND g.GRANTEE_KEY = FND_GLOBAL.USER_NAME
264 AND g.INSTANCE_TYPE = 'SET'
265 AND g.START_DATE <= SYSDATE
266 AND Nvl(g.END_DATE, SYSDATE ) >= SYSDATE
267 UNION
268 SELECT FND_GLOBAL.USER_NAME AS granter_key
269 FROM dual;
270
271 -- Bug 4633202: Temp variables
272 l_notification_key wf_notifications.ACCESS_KEY%TYPE;
273 l_granter_key FND_GRANTS.PARAMETER1%TYPE;
274 l_has_access BOOLEAN;
275
276
277 l_notification_id WF_NOTIFICATIONS.notification_id%type;
278 l_responder WF_NOTIFICATIONS.responder%type;
279 l_original_recipient_id wf_users.orig_system_id%type;
280 l_recipient_id wf_users.orig_system_id%type;
281 l_recipient_role WF_NOTIFICATIONS.recipient_role%type;
282 l_original_recipient WF_NOTIFICATIONS.ORIGINAL_RECIPIENT%type;
283
284 l_from_role WF_NOTIFICATIONS.from_role%TYPE; --Bug 12534279
285
286 Begin
287 Open getcurrnotif;
288 fetch getcurrnotif into l_notification_id, l_from_role; --Bug 12534279
289 close getcurrnotif;
290
291 If l_notification_id = -9995 Then
292 return FALSE;
293 End If;
294
295 -- Bug 4633202.
296 -- Loop for all granters and current user
297 l_has_access := FALSE;
298 FOR r_grants IN c_worklist_grants LOOP
299 BEGIN
300 IF( r_grants.granter_key <> l_from_role) THEN --Bug 12534279
301 -- Validate the access
302 l_granter_key := wf_advanced_worklist.Authenticate
303 ( username => r_grants.granter_key,
304 nid => l_notification_id,
305 nkey => l_notification_key
306 );
307 -- If we are here, means there is no exception. Access is validated.
308 -- Since one valid record is found, we can exit
309 l_has_access := TRUE;
310 EXIT;
311
312 END IF;
313
314 EXCEPTION
315 WHEN OTHERS THEN
316 NULL; -- Loop through for other grants
317 END;
318 END LOOP;
319
320 RETURN l_has_access;
321
322 /* internal name of responder */
323 /* Bug 4633202.
324 This check is not required. WF API itself
325 will take care of all these checks.
326 SELECT wfn.recipient_role, wfn.original_recipient
327 INTO l_recipient_role, l_original_recipient
328 FROM wf_notifications wfn
329 WHERE wfn.notification_id = l_notification_id;
330
331 OPEN c_responderid(l_recipient_role);
332 FETCH c_responderid INTO l_recipient_id;
333 CLOSE c_responderid;
334
335 OPEN c_responderid(l_original_recipient);
336 FETCH c_responderid INTO l_original_recipient_id;
337 CLOSE c_responderid;
338
339 If ((l_recipient_id = x_logged_emp_id) AND
340 (l_original_recipient_id = x_action_emp_id)) Then
341 return TRUE;
342 End If;
343
344 return FALSE;
345 */
346 Exception
347 when others then
348 return FALSE;
349
350 End check_for_delegation;
351
352 END PO_SECURITY_CHECK_SV;
[Home] [Help]