[Home] [Help]
PACKAGE BODY: APPS.PO_REQS_SV
Source
1 PACKAGE BODY PO_REQS_SV as
2 /* $Header: POXRQR1B.pls 120.5.12020000.2 2013/02/10 11:23:52 vegajula ship $ */
3 /*============================= po_reqs_sv ===============================*/
4
5 /*===========================================================================
6
7 PROCEDURE NAME: lock_row_for_status_update
8
9 ===========================================================================*/
10
11 PROCEDURE lock_row_for_status_update (x_requisition_header_id IN NUMBER)
12 IS
13 CURSOR C IS
14 SELECT *
15 FROM po_requisition_headers
16 WHERE requisition_header_id = x_requisition_header_id
17 FOR UPDATE of requisition_header_id NOWAIT;
18 Recinfo C%ROWTYPE;
19
20 x_progress VARCHAR2(3) := '';
21
22 BEGIN
23 x_progress := '010';
24 OPEN C;
25 FETCH C INTO Recinfo;
26 IF (C%NOTFOUND) then
27 CLOSE C;
28 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
29 APP_EXCEPTION.Raise_Exception;
30 END IF;
31 CLOSE C;
32
33 EXCEPTION
34 WHEN app_exception.record_lock_exception THEN
35 po_message_s.app_error ('PO_ALL_CANNOT_RESERVE_RECORD');
36
37 WHEN OTHERS THEN
38 -- dbms_output.put_line('In Exception');
39 PO_MESSAGE_S.SQL_ERROR('LOCK_ROW_FOR_STATUS_UPDATE', x_progress, sqlcode);
40 RAISE;
41 END;
42
43 /*===========================================================================
44
45 PROCEDURE NAME: update_reqs_header_status
46
47 ===========================================================================*/
48
49 PROCEDURE update_reqs_header_status
50 (X_req_header_id IN NUMBER,
51 X_req_line_id IN NUMBER,
52 X_req_control_action IN VARCHAR2,
53 X_req_control_reason IN VARCHAR2,
54 X_req_action_history_code IN OUT NOCOPY VARCHAR2,
55 X_req_control_error_rc IN OUT NOCOPY VARCHAR2) IS
56
57 X_progress VARCHAR2(3) := NULL;
58 X_authorization_status PO_REQUISITION_HEADERS.authorization_status%TYPE := NULL;
59 X_closed_code PO_REQUISITION_HEADERS.closed_code%TYPE := NULL;
60 X_req_has_open_shipment NUMBER := 0;
61 X_req_has_open_line NUMBER := 0;
62
63 BEGIN
64
65 -- dbms_output.put_line('Enter update_reqs_header_status');
66
67 X_progress := '000';
68
69 /* 1) set the default header authorization status or closed code value
70 ** according to the control action.
71 */
72
73 IF SubStr(X_req_control_action,1,6) = 'CANCEL' THEN
74 X_authorization_status := 'CANCELLED';
75 X_req_action_history_code := SubStr(X_req_control_action,1,6);
76 ELSE
77 X_authorization_status := NULL;
78 END IF;
79
80 IF X_req_control_action = 'FINALLY CLOSE' THEN
81 X_closed_code := 'FINALLY CLOSED';
82 X_req_action_history_code := X_req_control_action;
83 ELSE
84 X_closed_code := NULL;
85 END IF;
86
87 /* 2) When cancel or final close a line, continue to test
88 ** if requisition still has lines that are not finally closed
89 ** and are associated with a po shipment.
90 ** If YES, set the header authorization status to 'APPROVED' and do not
91 ** change the header closed_code.
92 */
93 /* BUG: 889643 - Changed the ELSEIF to END IF so that the system
94 ** goes through both the IF statement if the condition matches. This is
95 ** done to keep the authorization status as the same if there are any
96 ** open lines in the Req. */
97
98
99 IF X_req_line_id is NOT NULL THEN
100
101 X_progress := '010';
102 SELECT COUNT(1),
103 nvl(sum(decode(PORL.line_location_id,NULL,0,1)),0)
104 INTO X_req_has_open_line, X_req_has_open_shipment
105 FROM PO_REQUISITION_LINES PORL
106 WHERE PORL.requisition_header_id = X_req_header_id
107 AND nvl(PORL.cancel_flag, 'N') IN ('N', 'I')
108 AND nvl(PORL.closed_code, 'OPEN') <> 'FINALLY CLOSED';
109
110 IF X_req_has_open_shipment > 0 THEN
111 X_authorization_status := 'APPROVED';
112 X_req_action_history_code := 'APPROVE';
113 X_closed_code := NULL;
114
115 END IF;
116 IF X_req_has_open_line > 0 THEN
117
118 /* Requisition still has open lines. Do not update
119 ** requisition header.
120 */
121 X_authorization_status := NULL;
122 X_closed_code := NULL;
123 X_req_action_history_code := NULL;
124 END IF;
125
126 END IF;
127
128 --Start Bug 9611149 - FP of Bug 9537322
129 UPDATE PO_REQUISITION_HEADERS
130 SET active_shopping_cart_flag = null
131 WHERE requisition_header_id = X_req_header_id;
132 --End Bug 9611149 - FP of Bug 9537322
133
134 IF X_authorization_status IS NOT NULL OR
135 X_closed_code IS NOT NULL THEN
136 X_progress := '015';
137 UPDATE PO_REQUISITION_HEADERS
138 SET authorization_status = nvl(X_authorization_status, authorization_status),
139 closed_code = nvl(X_closed_code, closed_code),
140 contractor_status = decode(X_authorization_status,'CANCELLED',null,
141 contractor_status), -- Bug 3495679
142 last_update_login = fnd_global.login_id,
143 last_updated_by = fnd_global.user_id,
144 last_update_date = sysdate
145 WHERE requisition_header_id = X_req_header_id;
146 END IF;
147
148 -- dbms_output.put_line('Exit update_reqs_header_status');
149
150 EXCEPTION
151 WHEN NO_DATA_FOUND THEN
152 X_req_control_error_rc := 'Y';
153 po_message_s.sql_error('update_reqs_header_status', X_progress, sqlcode);
154 RAISE;
155 WHEN OTHERS THEN
156 po_message_s.sql_error('update_reqs_header_status', X_progress, sqlcode);
157 RAISE;
158
159 END update_reqs_header_status;
160
161 -- Bug 930894
162
163 /*==========================================================================
164
165 PROCEDURE NAME: get_req_encumbered()
166
167 ===========================================================================*/
168
169 FUNCTION get_req_encumbered(X_req_hdr_id IN number)
170 return boolean is
171
172 X_encumbered boolean := FALSE;
173
174 X_progress VARCHAR2(3) := '';
175
176 cursor c1 is SELECT 'Y'
177 FROM po_req_distributions
178 WHERE requisition_line_id
179 IN (SELECT requisition_line_id
180 FROM po_requisition_lines
181 WHERE requisition_header_id = X_req_hdr_id)
182 AND nvl(encumbered_flag,'N') <> 'N';
183
184 Recinfo c1%rowtype;
185
186 BEGIN
187 X_progress := '010';
188 open c1;
189 X_progress := '020';
190
191 /* Check if any distributions for a given req_header_id is encumbered
192 ** If there are encumbered distributions, return TRUE else
193 ** return FALSE */
194
195 fetch c1 into recinfo;
196
197 X_progress := '030';
198
199 if (c1%notfound) then
200 close c1;
201 X_encumbered := FALSE;
202 return(X_encumbered);
203 end if;
204
205 close c1;
206 X_encumbered := TRUE;
207 return(X_encumbered);
208
209
210 exception
211 when others then
212 po_message_s.sql_error('get_req_encumbered', X_progress, sqlcode);
213 raise;
214
215 END get_req_encumbered;
216
217 -- Bug 930894
218
219 /*===========================================================================
220
221 PROCEDURE NAME: val_req_delete()
222
223 ===========================================================================*/
224
225 FUNCTION val_req_delete(X_req_hdr_id IN NUMBER)
226 return boolean is
227 X_allow_delete boolean;
228
229 X_progress VARCHAR2(3) := NULL;
230 X_encumbered boolean;
231
232 BEGIN
233
234 /* Check if the Reuisition is encumbered */
235
236 X_progress := '005';
237
238 X_encumbered := po_reqs_sv.get_req_encumbered(X_req_hdr_id);
239
240 /* If the REQ is encumbered, it has to be cancelled */
241
242 if X_encumbered then
243 X_allow_delete := FALSE;
244 po_message_s.app_error('PO_RQ_USE_LINE_DEL');
245 else
246 X_allow_delete := TRUE;
247 end if;
248
249 return(X_allow_delete);
250
251
252 EXCEPTION
253 when others then
254 X_allow_delete := FALSE;
255 po_message_s.sql_error('val_req_delete', x_progress, sqlcode);
256 raise;
257
258 END val_req_delete;
259
260 /*===========================================================================
261
262 PROCEDURE NAME: delete_children
263
264 ===========================================================================*/
265
266 PROCEDURE delete_children(X_req_hdr_id IN NUMBER) IS
267
268 x_progress VARCHAR2(3) := NULL;
269
270 CURSOR S IS SELECT requisition_line_id
271 FROM po_requisition_lines
272 WHERE requisition_header_id = X_req_hdr_id;
273
274 BEGIN
275
276 x_progress := '010';
277
278 -- dbms_output.put_line('Before open cursor');
279
280 FOR Srec IN S LOOP
281 DELETE FROM po_req_distributions
282 WHERE requisition_line_id = Srec.requisition_line_id;
283
284 x_progress := '020';
285
286 fnd_attached_documents2_pkg.delete_attachments('REQ_LINE',
287 Srec.requisition_line_id,
288 '',
289 '',
290 '',
291 '',
292 'Y');
293 x_progress := '030';
294
295 DELETE FROM po_requisition_lines
296 WHERE requisition_line_id = Srec.requisition_line_id;
297
298 END LOOP;
299 -- dbms_output.put_line('After delete of distributions and lines');
300
301 EXCEPTION
302 WHEN OTHERS THEN
303 -- dbms_output.put_line('In exception');
304 po_message_s.sql_error('delete_children', x_progress, sqlcode);
305 raise;
306 END delete_children;
307
308 /*===========================================================================
309
310 PROCEDURE NAME: delete_req
311
312 ===========================================================================*/
313
314 PROCEDURE delete_req(X_req_hdr_id IN NUMBER) IS
315
316 x_progress VARCHAR2(3) := NULL;
317 x_rowid VARCHAR2(30);
318 x_type_lookup_code VARCHAR2(25):= NULL;
319 x_item_type VARCHAR2(8);
320 x_item_key VARCHAR2(240);
321 x_allow_delete BOOLEAN;
322
323 BEGIN
324
325 x_progress := '010';
326
327 SELECT type_lookup_code
328 INTO x_type_lookup_code
329 FROM po_requisition_headers
330 WHERE requisition_header_id = X_req_hdr_id;
331
332 /* Validate if the Document can be deleted */
333
334 x_allow_delete := val_req_delete(X_req_hdr_id);
335
336 /* If the Documnet can be deleted */
337
338 IF (x_allow_delete) THEN
339
340 /*
341 ** Delete the notification.
342 **/
343
344 x_progress := '020';
345
346 /* hvadlamu : commnting out the delete and adding the WorkFlow call */
347
348 SELECT wf_item_type,wf_item_key
349 INTO x_item_type,x_item_key
350 FROM PO_REQUISITION_HEADERS
351 WHERE requisition_header_id = x_req_hdr_id;
352
353 if ((x_item_type is null) and (x_item_key is null)) then
354 po_approval_reminder_sv.cancel_notif (x_type_lookup_code,
355 x_req_hdr_id);
356 else
357 /*Bug 3047646 : the line below has been added to ensure that
358 po send notification items are deleted.
359 when trying to delete a requisition it could be that it was submitted to
360 approval workflow and was never approved and also po send notification
361 was also invoked for it,in which case we need to stop the approval
362 workflow as well as the reminder workflow */
363
364 po_approval_reminder_sv.cancel_notif (x_type_lookup_code,
365 x_req_hdr_id);
366 po_approval_reminder_sv.stop_process(x_item_type,x_item_key);
367 end if;
368 /* Bug 2904413 Need to delete the action history also */
369
370 Delete po_action_history
371 Where OBJECT_TYPE_CODE = 'REQUISITION' and
372 OBJECT_SUB_TYPE_CODE = x_type_lookup_code and
373 OBJECT_ID = x_req_hdr_id;
374
375 /* po_notifications_sv1.delete_po_notif (x_type_lookup_code,
376 x_req_hdr_id); */
377
378 x_progress := '030';
379
380 SELECT rowid
381 INTO x_rowid
382 FROM po_requisition_headers
383 WHERE requisition_header_id = X_req_hdr_id;
384
385 -- dbms_output.put_line('After selecting rowid');
386
387 /*
388 ** Delete all the distributions and lines
389 ** for this requisition header.
390 */
391
392 x_progress := '040';
393
394 po_headers_sv1.delete_events_entities('REQUISITION', X_req_hdr_id); --Bug 12405805
395
396 po_reqs_sv.delete_children(X_req_hdr_id);
397
398 -- dbms_output.put_line('After delete children');
399
400 /*
401 ** Delete the attachments.
402 */
403
404 x_progress := '050';
405
406 fnd_attached_documents2_pkg.delete_attachments('REQ_HEADER',
407 x_req_hdr_id,
408 '',
409 '',
410 '',
411 '',
412 'Y');
413
414 /*
415 ** Delete the requisition header.
416 */
417
418 x_progress := '060';
419
420 po_requisition_headers_pkg.delete_row(X_rowid);
421
422 -- dbms_output.put_line('After delete row');
423
424 END IF;
425
426 EXCEPTION
427 WHEN OTHERS THEN
428 -- dbms_output.put_line('In exception');
429 po_message_s.sql_error('delete_req', x_progress, sqlcode);
430 raise;
431 END delete_req;
432
433 /*===========================================================================
434
435 PROCEDURE NAME: insert_req()
436
437 ===========================================================================*/
438
439 PROCEDURE insert_req(X_Rowid IN OUT NOCOPY VARCHAR2,
440 X_Requisition_Header_Id IN OUT NOCOPY NUMBER,
441 X_Preparer_Id NUMBER,
442 X_Last_Update_Date DATE,
443 X_Last_Updated_By NUMBER,
444 X_Segment1 IN OUT NOCOPY VARCHAR2,
445 X_Summary_Flag VARCHAR2,
446 X_Enabled_Flag VARCHAR2,
447 X_Segment2 VARCHAR2,
448 X_Segment3 VARCHAR2,
449 X_Segment4 VARCHAR2,
450 X_Segment5 VARCHAR2,
451 X_Start_Date_Active DATE,
452 X_End_Date_Active DATE,
453 X_Last_Update_Login NUMBER,
454 X_Creation_Date DATE,
455 X_Created_By NUMBER,
456 X_Description VARCHAR2,
457 X_Authorization_Status VARCHAR2,
458 X_Note_To_Authorizer VARCHAR2,
459 X_Type_Lookup_Code VARCHAR2,
460 X_Transferred_To_Oe_Flag VARCHAR2,
461 X_Attribute_Category VARCHAR2,
462 X_Attribute1 VARCHAR2,
463 X_Attribute2 VARCHAR2,
464 X_Attribute3 VARCHAR2,
465 X_Attribute4 VARCHAR2,
466 X_Attribute5 VARCHAR2,
467 X_On_Line_Flag VARCHAR2,
468 X_Preliminary_Research_Flag VARCHAR2,
469 X_Research_Complete_Flag VARCHAR2,
470 X_Preparer_Finished_Flag VARCHAR2,
471 X_Preparer_Finished_Date DATE,
472 X_Agent_Return_Flag VARCHAR2,
473 X_Agent_Return_Note VARCHAR2,
474 X_Cancel_Flag VARCHAR2,
475 X_Attribute6 VARCHAR2,
476 X_Attribute7 VARCHAR2,
477 X_Attribute8 VARCHAR2,
478 X_Attribute9 VARCHAR2,
479 X_Attribute10 VARCHAR2,
480 X_Attribute11 VARCHAR2,
481 X_Attribute12 VARCHAR2,
482 X_Attribute13 VARCHAR2,
483 X_Attribute14 VARCHAR2,
484 X_Attribute15 VARCHAR2,
485 X_Ussgl_Transaction_Code VARCHAR2,
486 X_Government_Context VARCHAR2,
487 X_Interface_Source_Code VARCHAR2,
488 X_Interface_Source_Line_Id NUMBER,
489 X_Closed_Code VARCHAR2,
490 X_Manual BOOLEAN,
491 X_amount NUMBER,
492 X_currency_code VARCHAR2,
493 p_org_id IN NUMBER default null -- <R12 MOAC>
494 ) IS
495
496
497 x_progress VARCHAR2(3) := NULL;
498
499 BEGIN
500
501 x_progress := '010';
502
503 po_requisition_headers_pkg.insert_row(X_Rowid,
504 X_Requisition_Header_Id,
505 X_Preparer_Id,
506 X_Last_Update_Date,
507 X_Last_Updated_By,
508 X_Segment1,
509 X_Summary_Flag,
510 X_Enabled_Flag,
511 X_Segment2,
512 X_Segment3,
513 X_Segment4,
514 X_Segment5,
515 X_Start_Date_Active,
516 X_End_Date_Active,
517 X_Last_Update_Login,
518 X_Creation_Date,
519 X_Created_By,
520 X_Description,
521 X_Authorization_Status,
522 X_Note_To_Authorizer,
523 X_Type_Lookup_Code,
524 X_Transferred_To_Oe_Flag,
525 X_Attribute_Category,
526 X_Attribute1,
527 X_Attribute2,
528 X_Attribute3,
529 X_Attribute4,
530 X_Attribute5,
531 X_On_Line_Flag,
532 X_Preliminary_Research_Flag,
533 X_Research_Complete_Flag,
534 X_Preparer_Finished_Flag,
535 X_Preparer_Finished_Date,
536 X_Agent_Return_Flag,
537 X_Agent_Return_Note,
538 X_Cancel_Flag,
539 X_Attribute6,
540 X_Attribute7,
541 X_Attribute8,
542 X_Attribute9,
543 X_Attribute10,
544 X_Attribute11,
545 X_Attribute12,
546 X_Attribute13,
547 X_Attribute14,
548 X_Attribute15,
549 NULL, --<R12 SLA>
550 X_Government_Context,
551 X_Interface_Source_Code,
552 X_Interface_Source_Line_Id,
553 X_Closed_Code,
554 X_Manual,
555 p_org_id -- <R12 MOAC>
556 );
557
558 -- dbms_output.put_line('After call to insert row');
559
560 /*
561 ** DEBUG. Call the routine to insert
562 ** notifications.
563 */
564
565 x_progress := '020';
566
567 /* bug# 465696 8/5/97. The previous fix to this performance problem introduced
568 a problem with the notifications (the bogus value used temporarily as the
569 document number was being inserted into the fnd_notifications table, since
570 the call below was made before we called the procedure to get the real
571 document number (segment1) in the POST-FORMS-COMMIT trigger.
572 Therefore, remove the call below from here and moving it to procedure
573 PO_REQUISITION_HEADERS_PKG.get_real_segment1.
574 */
575
576 IF X_Manual THEN
577
578 /*hvadlamu : commenting out since notifications will be handled by workflow */
579 /*po_notifications_sv1.send_po_notif (x_type_lookup_code,
580 x_requisition_header_id,
581 x_currency_code,
582 null,
583 null,
584 null,
585 null,
586 null); */
587 null;
588 END IF;
589
590 EXCEPTION
591 WHEN OTHERS THEN
592 -- dbms_output.put_line('In exception');
593 po_message_s.sql_error('insert_req', x_progress, sqlcode);
594 raise;
595 END insert_req;
596
597
598 /*===========================================================================
599
600 PROCEDURE NAME: update_oe_flag
601
602 ===========================================================================*/
603
604 PROCEDURE update_oe_flag(X_req_hdr_id IN NUMBER,
605 X_flag IN VARCHAR2) IS
606
607 x_progress VARCHAR2(3) := NULL;
608
609 BEGIN
610
611 x_progress := '010';
612
613 UPDATE po_requisition_headers
614 SET transferred_to_oe_flag = X_flag
615 WHERE requisition_header_id = X_req_hdr_id;
616
617 -- dbms_output.put_line('After update');
618
619 EXCEPTION
620 WHEN OTHERS THEN
621 -- dbms_output.put_line('In exception');
622 po_message_s.sql_error('update_oe_flag', x_progress, sqlcode);
623 raise;
624 END update_oe_flag;
625
626
627 /*===========================================================================
628
629 PROCEDURE NAME: get_req_startup_values
630
631 ===========================================================================*/
632
633 PROCEDURE get_req_startup_values (X_source_inventory IN OUT NOCOPY VARCHAR2,
634 X_source_vendor IN OUT NOCOPY VARCHAR2) IS
635
636 x_progress VARCHAR2(3) := NULL;
637
638 BEGIN
639
640 x_progress := '010';
641
642 po_core_s.get_displayed_value ('REQUISITION SOURCE TYPE',
643 'INVENTORY',
644 x_source_inventory);
645
646 x_progress := '020';
647
648 po_core_s.get_displayed_value ('REQUISITION SOURCE TYPE',
649 'VENDOR',
650 x_source_vendor);
651
652
653 -- dbms_output.put_line('After update');
654
655 EXCEPTION
656 WHEN OTHERS THEN
657 -- dbms_output.put_line('In exception');
658 po_message_s.sql_error('get_req_startup_values', x_progress, sqlcode);
659 raise;
660 END get_req_startup_values;
661
662
663 END PO_REQS_SV;