[Home] [Help]
PACKAGE BODY: APPS.PO_REQS_CONTROL_SV
Source
1 PACKAGE BODY po_reqs_control_sv AS
2 /* $Header: POXRQCNB.pls 120.1.12010000.2 2008/08/04 08:32:04 rramasam ship $*/
3
4 /*===========================================================================
5
6 FUNCTION NAME: val_doc_security
7
8 ===========================================================================*/
9
10 FUNCTION val_doc_security
11 (X_doc_agent_id IN NUMBER,
12 X_agent_id IN NUMBER,
13 X_doc_type IN VARCHAR2,
14 X_doc_subtype IN VARCHAR2) RETURN BOOLEAN IS
15
16 X_progress VARCHAR2(3) := NULL;
17 X_row_exists NUMBER := 0;
18
19 BEGIN
20 -- dbms_output.put_line('Enter val_doc_security');
21
22 X_progress := '010';
23
24 IF X_doc_type = 'REQUISITION' THEN
25
26 -- Check for requisition document security/access control
27
28 SELECT COUNT(1)
29 INTO X_row_exists
30 FROM PO_DOCUMENT_TYPES PODT
31 WHERE PODT.DOCUMENT_TYPE_CODE = X_doc_type
32 AND PODT.DOCUMENT_SUBTYPE = X_doc_subtype
33 AND (X_doc_agent_id = X_agent_id
34 OR (PODT.SECURITY_LEVEL_CODE = 'PUBLIC'
35 AND PODT.access_level_code = 'FULL')
36 OR (PODT.SECURITY_LEVEL_CODE = 'PURCHASING'
37 AND PODT.access_level_code = 'FULL'
38 AND EXISTS
39 (SELECT 'Is the user an agent'
40 FROM PO_AGENTS POA
41 WHERE POA.AGENT_ID = X_agent_id
42 AND sysdate BETWEEN POA.start_date_active
43 AND nvl(POA.end_date_active,sysdate+1)))
44 OR (PODT.SECURITY_LEVEL_CODE = 'HIERARCHY'
45 AND PODT.access_level_code = 'FULL'
46 AND X_agent_id IN
47 (SELECT POEH.SUPERIOR_ID
48 FROM PO_EMPLOYEE_HIERARCHIES POEH,
49 PO_SYSTEM_PARAMETERS POSP
50 WHERE POEH.EMPLOYEE_ID = X_doc_agent_id
51 AND POEH.POSITION_STRUCTURE_ID =
52 POSP.SECURITY_POSITION_STRUCTURE_ID)));
53
54 ELSE
55
56 -- Check for PO/Release document security/access control
57 /*Bug6640107 Reverting the changes done in the previous version */
58
59 SELECT COUNT(1)
60 INTO X_row_exists
61 FROM PO_DOCUMENT_TYPES PODT
62 WHERE PODT.DOCUMENT_TYPE_CODE = X_doc_type
63 AND PODT.DOCUMENT_SUBTYPE = X_doc_subtype
64 AND (X_doc_agent_id = X_agent_id
65 OR (PODT.SECURITY_LEVEL_CODE = 'PUBLIC'
66 AND PODT.access_level_code IN ('MODIFY','FULL'))
67 OR (PODT.SECURITY_LEVEL_CODE = 'PURCHASING'
68 AND PODT.access_level_code IN ('MODIFY','FULL')
69 AND EXISTS
70 (SELECT 'Is the user an agent'
71 FROM PO_AGENTS POA
72 WHERE POA.AGENT_ID = X_agent_id
73 AND sysdate BETWEEN POA.start_date_active
74 AND nvl(POA.end_date_active,sysdate+1)))
75 OR (PODT.SECURITY_LEVEL_CODE = 'HIERARCHY'
76 AND PODT.access_level_code IN ('MODIFY','FULL')
77 AND X_agent_id IN
78 (SELECT POEH.SUPERIOR_ID
79 FROM PO_EMPLOYEE_HIERARCHIES POEH,
80 PO_SYSTEM_PARAMETERS POSP
81 WHERE POEH.EMPLOYEE_ID = X_doc_agent_id
82 AND POEH.POSITION_STRUCTURE_ID =
83 POSP.SECURITY_POSITION_STRUCTURE_ID)));
84
85 END IF;
86
87
88 IF X_row_exists > 0 THEN
89 /* document security check is passed */
90 RETURN (TRUE);
91 ELSE
92 /* document security check is failed */
93 RETURN (FALSE);
94 END IF;
95
96 EXCEPTION
97 WHEN OTHERS THEN
98 po_message_s.sql_error('val_doc_security', X_progress, sqlcode);
99 RAISE;
100
101 -- dbms_output.put_line('Exit val_doc_security');
102
103 END val_doc_security;
104
105
106 /*===========================================================================
107
108 FUNCTION NAME: val_reqs_action
109
110 ===========================================================================*/
111
112 FUNCTION val_reqs_action
113 (X_req_header_id IN NUMBER,
114 X_req_line_id IN NUMBER,
115 X_agent_id IN NUMBER,
116 X_req_doc_type IN VARCHAR2,
117 X_req_doc_subtype IN VARCHAR2,
118 X_req_control_action IN VARCHAR2,
119 X_req_control_reason IN VARCHAR2,
120 X_req_action_date IN DATE,
121 X_encumbrance_flag IN VARCHAR2,
122 X_oe_installed_flag IN VARCHAR2) RETURN BOOLEAN IS
123
124 X_progress VARCHAR2(3) := NULL;
125 X_req_control_error_rc VARCHAR2(1) := 'N';
126 X_action_code VARCHAR2 (30);
127 X_action_dsp VARCHAR2 (80);
128 X_document_dsp VARCHAR2 (80);
129
130 BEGIN
131
132 -- dbms_output.put_line('Enter val_reqs_action');
133
134 X_progress := '000';
135 IF po_req_lines_sv.val_reqs_po_shipment(X_req_header_id,
136 X_req_line_id) = FALSE THEN
137 -- dbms_output.put_line('val_reqs_po_shipment return FALSE');
138 /* DEBUG - display req. has open PO shipment message */
139
140 IF SubStr(X_req_control_action,1,6) = 'CANCEL' THEN
141 X_action_code := SubStr(X_req_control_action,1,6);
142 ELSE
143 X_action_code := X_req_control_action;
144 END IF;
145
146 po_headers_sv4.get_lookup_code_dsp ('CONTROL ACTIONS',
147 X_action_code,
148 X_action_dsp);
149
150 po_headers_sv4.get_lookup_code_dsp ('DOCUMENT TYPE',
151 X_req_doc_type,
152 X_document_dsp);
153
154 po_message_s.app_error ('PO_CONTROL_OPEN_PO_SHIPMENT',
155 'ACTION', X_action_dsp,
156 'DOCUMENT',X_document_dsp);
157
158 RETURN (FALSE);
159 END IF;
160
161 IF X_oe_installed_flag = 'Y' THEN
162 IF po_req_lines_sv.val_reqs_oe_shipment (X_req_header_id,
163 X_req_line_id) = FALSE THEN
164 -- dbms_output.put_line('val_reqs_oe_shipment return FALSE');
165 /* DEBUG - display req. has open OE shipment message */
166 IF SubStr(X_req_control_action,1,6) = 'CANCEL' THEN
167 X_action_code := SubStr(X_req_control_action,1,6);
168 ELSE
169 X_action_code := X_req_control_action;
170 END IF;
171
172 po_headers_sv4.get_lookup_code_dsp ('CONTROL ACTIONS',
173 X_action_code,
174 X_action_dsp);
175
176 po_headers_sv4.get_lookup_code_dsp ('DOCUMENT TYPE',
177 X_req_doc_type,
178 X_document_dsp);
179
180 po_message_s.app_error ('PO_CONTROL_OPEN_OE_SHIPMENT',
181 'ACTION', X_action_dsp,
182 'DOCUMENT',X_document_dsp);
183
184 RETURN (FALSE);
185
186 END IF;
187
188 /* Bug# 2607180: kagarwal
189 ** Desc: Call po_req_lines_sv.val_reqs_qty_received to verify if internal
190 ** requisition lines which are sourced from inventory, have been received
191 ** or not. For this we will check if the 'SHIPMENT' supply exists for the
192 ** requisition lines.
193 **
194 ** If 'SHIPMENT' supply exists return FALSE.
195 */
196
197 IF po_req_lines_sv.val_reqs_qty_received (X_req_header_id,
198 X_req_line_id) = FALSE THEN
199 IF SubStr(X_req_control_action,1,6) = 'CANCEL' THEN
200 X_action_code := SubStr(X_req_control_action,1,6);
201 ELSE
202 X_action_code := X_req_control_action;
203 END IF;
204
205 po_headers_sv4.get_lookup_code_dsp ('CONTROL ACTIONS',
206 X_action_code,
207 X_action_dsp);
208
209 po_headers_sv4.get_lookup_code_dsp ('DOCUMENT TYPE',
210 X_req_doc_type,
211 X_document_dsp);
212
213 po_message_s.app_error ('PO_CONTROL_OPEN_OE_SHIPMENT',
214 'ACTION', X_action_dsp,
215 'DOCUMENT',X_document_dsp);
216
217 RETURN (FALSE);
218 END IF;
219 END IF;
220
221
222 IF X_encumbrance_flag = 'Y' THEN
223
224 IF po_req_lines_sv.val_reqs_qty_delivered (X_req_header_id,
225 X_req_line_id) = FALSE THEN
226
227 -- dbms_output.put_line('val_reqs_qty_delivered return FALSE');
228 /* DEBUG - display PO_RQCON_NOT_DELIVERED message */
229 po_message_s.app_error('PO_RQCON_NOT_DELIVERED');
230 RETURN (FALSE);
231
232 END IF;
233
234 IF SubStr(X_req_control_action,1,6) = 'CANCEL' THEN
235 po_req_lines_sv.update_reqs_lines_incomplete (X_req_header_id,
236 X_req_line_id,
237 X_req_control_error_rc,
238 X_oe_installed_flag);
239 IF X_req_control_error_rc = 'Y' THEN
240 -- dbms_output.put_line('update_reqs_lines_incomplete return ERROR');
241 RETURN (FALSE);
242 END IF;
243 END IF;
244 ELSE
245 /*
246 ** if encumbrance flag is OFF, continue the process of updating the
247 ** requisition status on the server side
248 */
249 po_reqs_control_sv.update_reqs_status (X_req_header_id,
250 X_req_line_id,
251 X_agent_id,
252 X_req_doc_type,
253 X_req_doc_subtype,
254 X_req_control_action,
255 X_req_control_reason,
256 X_req_action_date,
257 X_encumbrance_flag,
258 X_oe_installed_flag,
259 X_req_control_error_rc);
260 IF X_req_control_error_rc = 'Y' THEN
261 -- dbms_output.put_line('update_reqs_status return ERROR');
262 RETURN (FALSE);
263
264 END IF;
265 END IF;
266
267 RETURN (TRUE);
268 -- dbms_output.put_line('Exit process_req_control');
269
270 EXCEPTION
271 WHEN OTHERS THEN
272 po_message_s.sql_error('val_reqs_action', X_progress, sqlcode);
273 RAISE;
274
275 END val_reqs_action;
276
277
278
279 /*===========================================================================
280
281 PROCEDURE NAME: update_reqs_status
282
283 ===========================================================================*/
284
285 PROCEDURE update_reqs_status
286 (X_req_header_id IN NUMBER,
287 X_req_line_id IN NUMBER,
288 X_agent_id IN NUMBER,
289 X_req_doc_type IN VARCHAR2,
290 X_req_doc_subtype IN VARCHAR2,
291 X_req_control_action IN VARCHAR2,
292 X_req_control_reason IN VARCHAR2,
293 X_req_action_date IN DATE,
294 X_encumbrance_flag IN VARCHAR2,
295 X_oe_installed_flag IN VARCHAR2,
296 X_req_control_error_rc IN OUT NOCOPY VARCHAR2) IS
297
298
299 X_progress VARCHAR2(3) := NULL;
300 X_req_header_auth_status PO_REQUISITION_HEADERS.authorization_status%TYPE := NULL;
301 X_req_action_history_code PO_ACTION_HISTORY.action_code%TYPE := NULL;
302 X_supply_action VARCHAR2(50) := NULL;
303 X_supply_id NUMBER;
304 X_req_header_auth_status_dsp VARCHAR2(80);
305 x_item_type varchar2(8);
306 x_item_key varchar2(240);
307
308 BEGIN
309
310 -- dbms_output.put_line('Enter update_reqs_status');
311
312 X_progress := '000';
313 X_req_control_error_rc := 'N';
314
315
316 po_reqs_sv2.get_reqs_auth_status (X_req_header_id,
317 X_req_header_auth_status,
318 X_req_header_auth_status_dsp,
319 X_req_control_error_rc);
320
321 IF X_encumbrance_flag = 'Y' THEN
322 po_req_dist_sv.update_reqs_distributions (X_req_header_id,
323 X_req_line_id,
324 X_req_control_action,
325 X_req_action_date,
326 X_req_control_error_rc);
327 END IF;
328
329 /*
330 ** Call maintain_supply to set:
331 ** 1. Req header: quantity in mtl_supply to 0 for a given req header
332 ** 2. Req line: quantity in mtl_supply to 0 for a given req line.
333 **
334 ** DEBUG: The maintain_supply used in this server package is
335 ** for testing. It should be moved to the maintain_supply server
336 ** package when it is ready.
337 */
338
339 /*BUG: 969859 FRKHAN 9/1/99
340 In the overall fix, the supply is not removed when the req is returned.So
341 if the returned req is cancelled, its supply needs to be removed then.
342 */
343 /*
344 Bug:2361695
345 When a requisition is cancelled the supply lines corresponding
346 to that requisition should be deleted irrespective of the status
347 of the requisition. Hence commenting out the condition below.
348 */
349
350 /* IF ((X_req_header_auth_status = 'APPROVED') OR (X_req_header_auth_status = 'RETURNED')) THEN
351 */
352 IF X_req_line_id IS NULL THEN
353 X_supply_action := 'Remove_Req_Supply';
354 X_supply_id := X_req_header_id;
355 ELSE
356 X_supply_action := 'Remove_Req_Line_Supply';
357 X_supply_id := X_req_line_id;
358 END IF;
359
360 po_reqs_control_sv.maintain_supply (X_supply_action,
361 X_supply_id,
362 X_req_control_error_rc);
363
364 -- END IF;
365
366
367 po_req_lines_sv.update_reqs_lines_status (X_req_header_id,
368 X_req_line_id,
369 X_req_control_action,
370 X_req_control_reason,
371 X_req_action_date,
372 X_oe_installed_flag,
373 X_req_control_error_rc);
374
375 X_req_action_history_code := NULL;
376
377 po_reqs_sv.update_reqs_header_status (X_req_header_id,
378 X_req_line_id,
379 X_req_control_action,
380 X_req_control_reason,
381 X_req_action_history_code,
382 X_req_control_error_rc);
383
384 IF X_req_action_history_code IS NOT NULL THEN
385
386 IF X_req_header_auth_status = 'IN PROCESS' OR
387 X_req_header_auth_status = 'PRE-APPROVED' THEN
388
389 -- Bug 5108975 Start
390 -- If the action is cancel then delete the rows where action_code is null
391 -- and insert a row with action_code CANCEL.
392 IF X_req_action_history_code = 'CANCEL' THEN
393
394 DELETE FROM PO_ACTION_HISTORY
395 WHERE object_id = X_req_header_id
396 AND object_type_code = X_req_doc_type
397 AND action_code IS NULL;
398
399 po_forward_sv1.insert_action_history (X_req_header_id,
400 X_req_doc_type,
401 X_req_doc_subtype,
402 NULL,
403 X_req_action_history_code,
404 sysdate,
405 X_agent_id,
406 NULL,
407 X_req_control_reason,
408 NULL,
409 NULL,
410 NULL,
411 NULL,
412 NULL,
413 NULL,
414 fnd_global.user_id,
415 fnd_global.login_id);
416 -- Bug 5108975 End
417 ELSE
418 po_forward_sv1.update_action_history (X_req_header_id,
419 X_req_doc_type,
420 NULL,
421 X_req_action_history_code,
422 X_req_control_reason,
423 fnd_global.user_id,
424 fnd_global.login_id);
425 END IF;
426
427 ELSE
428 po_forward_sv1.insert_action_history (X_req_header_id,
429 X_req_doc_type,
430 X_req_doc_subtype,
431 NULL,
432 X_req_action_history_code,
433 sysdate,
434 X_agent_id,
435 NULL,
436 X_req_control_reason,
437 NULL,
438 NULL,
439 NULL,
440 NULL,
441 NULL,
442 NULL,
443 fnd_global.user_id,
444 fnd_global.login_id);
445 END IF;
446
447 /*hvadlamu commenting out and adding the workflow call */
448 /* po_notifications_sv1.delete_po_notif (x_Req_doc_type,
449 X_req_header_id); */
450 SELECT wf_item_type,wf_item_key
451 INTO x_item_type,x_item_key
452 FROM PO_REQUISITION_HEADERS
453 WHERE requisition_header_id = x_req_header_id;
454
455 if ((x_item_type is null) and (x_item_key is null)) then
456 po_approval_reminder_sv.cancel_notif (x_req_doc_subtype,
457 x_req_header_id);
458 else
459 /* Bug# 1499199
460 Forward fix of bug# 13721671
461 When the wf_item_type and item_type are not null
462 even then, there is a possibility that some outstanding
463 notifications exist. These should be cancelled when the
464 requisition is cancelled. The above cancel_notif call is also
465 modified to send doc_subtype as parameter instead of doc_type */
466
467 po_approval_reminder_sv.cancel_notif (x_req_doc_subtype, x_req_header_id);
468 po_approval_reminder_sv.stop_process(x_item_type,x_item_key);
469 end if;
470
471 END IF;
472
473 -- dbms_output.put_line('Exit update_reqs_status');
474
475
476 EXCEPTION
477 WHEN OTHERS THEN
478 po_message_s.sql_error('update_reqs_status', x_progress, sqlcode);
479 RAISE;
480
481 END update_reqs_status;
482
483 /*===========================================================================
484
485 PROCEDURE NAME: commit_changes
486
487 ===========================================================================*/
488
489 PROCEDURE commit_changes IS
490
491 X_progress VARCHAR2(3) := NULL;
492 BEGIN
493
494 X_progress := '005';
495 COMMIT;
496
497 EXCEPTION
498 WHEN OTHERS THEN
499 po_message_s.sql_error('commit_changes', x_progress, sqlcode);
500 RAISE;
501
502 END commit_changes;
503
504 /*===========================================================================
505
506 PROCEDURE NAME: rollback_changes
507
508 ===========================================================================*/
509
510 PROCEDURE rollback_changes IS
511
512 X_progress VARCHAR2(3) := NULL;
513 BEGIN
514
515 X_progress := '005';
516 ROLLBACK;
517
518 EXCEPTION
519 WHEN OTHERS THEN
520 po_message_s.sql_error('rollback_changes', x_progress, sqlcode);
521 RAISE;
522
523 END rollback_changes;
524
525
526 /*===========================================================================
527
528 PROCEDURE NAME: maintain_supply
529
530 ===========================================================================*/
531
532 PROCEDURE maintain_supply
533 (X_supply_action IN VARCHAR2,
534 X_supply_id IN NUMBER,
535 X_req_control_error_rc IN OUT NOCOPY VARCHAR2) IS
536
537 X_progress VARCHAR2(3) := NULL;
538 l_return_status VARCHAR2(2);
539
540 BEGIN
541
542 -- dbms_output.put_line('Enter maintain_supply');
543 X_req_control_error_rc := '';
544
545 IF X_supply_action = 'Remove_Req_Supply' THEN
546 X_progress := '005';
547
548 /*
549 ** Sets the req quantity in mtl_supply to 0 for a given req header.
550 */
551
552 UPDATE MTL_SUPPLY
553 SET quantity = 0,
554 change_flag = 'Y'
555 WHERE supply_type_code = 'REQ'
556 AND req_header_id = X_supply_id;
557
558 /* bug 404433: - Update the MTL_SUPPLY table in two steps to keep
559 ** the code compatible as in R10 userexit.
560 ** The delete step is to fire the MRP triggers.
561 */
562
563 DELETE FROM MTL_SUPPLY
564 WHERE supply_type_code = 'REQ'
565 AND quantity = 0
566 AND change_flag = 'Y'
567 AND req_header_id = X_supply_id;
568
569
570 PO_RESERVATION_MAINTAIN_SV.maintain_reservation(
571 p_header_id => X_supply_id,
572 p_action => X_supply_action,
573 x_return_status => l_return_status);
574
575
576 ELSIF X_supply_action = 'Remove_Req_Line_Supply' THEN
577 X_progress := '010';
578
579 /*
580 ** Sets the req quantity in mtl_supply to 0 for a given req line.
581 */
582
583 UPDATE MTL_SUPPLY
584 SET quantity = 0,
585 change_flag = 'Y'
586 WHERE supply_type_code = 'REQ'
587 AND req_line_id = X_supply_id;
588
589
590 DELETE FROM MTL_SUPPLY
591 WHERE supply_type_code = 'REQ'
592 AND quantity = 0
593 AND change_flag = 'Y'
594 AND req_line_id = X_supply_id;
595
596
597 PO_RESERVATION_MAINTAIN_SV.maintain_reservation(
598 p_line_id => X_supply_id,
599 p_action => X_supply_action,
600 x_return_status => l_return_status);
601
602 END IF;
603
604 -- dbms_output.put_line('Exit maintain_supply');
605
606 EXCEPTION
607 WHEN NO_DATA_FOUND THEN
608 X_req_control_error_rc := '';
609 WHEN OTHERS THEN
610 X_req_control_error_rc := 'Y';
611 po_message_s.sql_error('maintain_supply', x_progress, sqlcode);
612 RAISE;
613
614 END maintain_supply;
615
616
617 END po_reqs_control_sv;