[Home] [Help]
PACKAGE BODY: APPS.PO_REQS_SV
Source
1 PACKAGE BODY PO_REQS_SV as
2 /* $Header: POXRQR1B.pls 120.2 2005/06/09 23:57:52 sjadhav noship $ */
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 IF X_authorization_status IS NOT NULL OR
129 X_closed_code IS NOT NULL THEN
130 X_progress := '015';
131 UPDATE PO_REQUISITION_HEADERS
132 SET authorization_status = nvl(X_authorization_status, authorization_status),
133 closed_code = nvl(X_closed_code, closed_code),
134 contractor_status = decode(X_authorization_status,'CANCELLED',null,
135 contractor_status), -- Bug 3495679
136 last_update_login = fnd_global.login_id,
137 last_updated_by = fnd_global.user_id,
138 last_update_date = sysdate
139 WHERE requisition_header_id = X_req_header_id;
140 END IF;
141
142 -- dbms_output.put_line('Exit update_reqs_header_status');
143
144 EXCEPTION
145 WHEN NO_DATA_FOUND THEN
146 X_req_control_error_rc := 'Y';
147 po_message_s.sql_error('update_reqs_header_status', X_progress, sqlcode);
148 RAISE;
149 WHEN OTHERS THEN
150 po_message_s.sql_error('update_reqs_header_status', X_progress, sqlcode);
151 RAISE;
152
153 END update_reqs_header_status;
154
155 -- Bug 930894
156
157 /*==========================================================================
158
159 PROCEDURE NAME: get_req_encumbered()
160
161 ===========================================================================*/
162
163 FUNCTION get_req_encumbered(X_req_hdr_id IN number)
164 return boolean is
165
166 X_encumbered boolean := FALSE;
167
168 X_progress VARCHAR2(3) := '';
169
170 cursor c1 is SELECT 'Y'
171 FROM po_req_distributions
172 WHERE requisition_line_id
173 IN (SELECT requisition_line_id
174 FROM po_requisition_lines
175 WHERE requisition_header_id = X_req_hdr_id)
176 AND nvl(encumbered_flag,'N') <> 'N';
177
178 Recinfo c1%rowtype;
179
180 BEGIN
181 X_progress := '010';
182 open c1;
183 X_progress := '020';
184
185 /* Check if any distributions for a given req_header_id is encumbered
186 ** If there are encumbered distributions, return TRUE else
187 ** return FALSE */
188
189 fetch c1 into recinfo;
190
191 X_progress := '030';
192
193 if (c1%notfound) then
194 close c1;
195 X_encumbered := FALSE;
196 return(X_encumbered);
197 end if;
198
199 close c1;
200 X_encumbered := TRUE;
201 return(X_encumbered);
202
203
204 exception
205 when others then
206 po_message_s.sql_error('get_req_encumbered', X_progress, sqlcode);
207 raise;
208
209 END get_req_encumbered;
210
211 -- Bug 930894
212
213 /*===========================================================================
214
215 PROCEDURE NAME: val_req_delete()
216
217 ===========================================================================*/
218
219 FUNCTION val_req_delete(X_req_hdr_id IN NUMBER)
220 return boolean is
221 X_allow_delete boolean;
222
223 X_progress VARCHAR2(3) := NULL;
224 X_encumbered boolean;
225
226 BEGIN
227
228 /* Check if the Reuisition is encumbered */
229
230 X_progress := '005';
231
232 X_encumbered := po_reqs_sv.get_req_encumbered(X_req_hdr_id);
233
234 /* If the REQ is encumbered, it has to be cancelled */
235
236 if X_encumbered then
237 X_allow_delete := FALSE;
238 po_message_s.app_error('PO_RQ_USE_LINE_DEL');
239 else
240 X_allow_delete := TRUE;
241 end if;
242
243 return(X_allow_delete);
244
245
246 EXCEPTION
247 when others then
248 X_allow_delete := FALSE;
249 po_message_s.sql_error('val_req_delete', x_progress, sqlcode);
250 raise;
251
252 END val_req_delete;
253
254 /*===========================================================================
255
256 PROCEDURE NAME: delete_children
257
258 ===========================================================================*/
259
260 PROCEDURE delete_children(X_req_hdr_id IN NUMBER) IS
261
262 x_progress VARCHAR2(3) := NULL;
263
264 CURSOR S IS SELECT requisition_line_id
265 FROM po_requisition_lines
266 WHERE requisition_header_id = X_req_hdr_id;
267
268 BEGIN
269
270 x_progress := '010';
271
272 -- dbms_output.put_line('Before open cursor');
273
274 FOR Srec IN S LOOP
275 DELETE FROM po_req_distributions
276 WHERE requisition_line_id = Srec.requisition_line_id;
277
278 x_progress := '020';
279
280 fnd_attached_documents2_pkg.delete_attachments('REQ_LINE',
281 Srec.requisition_line_id,
282 '',
283 '',
284 '',
285 '',
286 'Y');
287 x_progress := '030';
288
289 DELETE FROM po_requisition_lines
290 WHERE requisition_line_id = Srec.requisition_line_id;
291
292 END LOOP;
293 -- dbms_output.put_line('After delete of distributions and lines');
294
295 EXCEPTION
296 WHEN OTHERS THEN
297 -- dbms_output.put_line('In exception');
298 po_message_s.sql_error('delete_children', x_progress, sqlcode);
299 raise;
300 END delete_children;
301
302 /*===========================================================================
303
304 PROCEDURE NAME: delete_req
305
306 ===========================================================================*/
307
308 PROCEDURE delete_req(X_req_hdr_id IN NUMBER) IS
309
310 x_progress VARCHAR2(3) := NULL;
311 x_rowid VARCHAR2(30);
312 x_type_lookup_code VARCHAR2(25):= NULL;
313 x_item_type VARCHAR2(8);
314 x_item_key VARCHAR2(240);
315 x_allow_delete BOOLEAN;
316
317 BEGIN
318
319 x_progress := '010';
320
321 SELECT type_lookup_code
322 INTO x_type_lookup_code
323 FROM po_requisition_headers
324 WHERE requisition_header_id = X_req_hdr_id;
325
326 /* Validate if the Document can be deleted */
327
328 x_allow_delete := val_req_delete(X_req_hdr_id);
329
330 /* If the Documnet can be deleted */
331
332 IF (x_allow_delete) THEN
333
334 /*
335 ** Delete the notification.
336 **/
337
338 x_progress := '020';
339
340 /* hvadlamu : commnting out the delete and adding the WorkFlow call */
341
342 SELECT wf_item_type,wf_item_key
343 INTO x_item_type,x_item_key
344 FROM PO_REQUISITION_HEADERS
345 WHERE requisition_header_id = x_req_hdr_id;
346
347 if ((x_item_type is null) and (x_item_key is null)) then
348 po_approval_reminder_sv.cancel_notif (x_type_lookup_code,
349 x_req_hdr_id);
350 else
351 /*Bug 3047646 : the line below has been added to ensure that
352 po send notification items are deleted.
353 when trying to delete a requisition it could be that it was submitted to
354 approval workflow and was never approved and also po send notification
355 was also invoked for it,in which case we need to stop the approval
356 workflow as well as the reminder workflow */
357
358 po_approval_reminder_sv.cancel_notif (x_type_lookup_code,
359 x_req_hdr_id);
360 po_approval_reminder_sv.stop_process(x_item_type,x_item_key);
361 end if;
362 /* Bug 2904413 Need to delete the action history also */
363
364 Delete po_action_history
365 Where OBJECT_TYPE_CODE = 'REQUISITION' and
366 OBJECT_SUB_TYPE_CODE = x_type_lookup_code and
367 OBJECT_ID = x_req_hdr_id;
368
369 /* po_notifications_sv1.delete_po_notif (x_type_lookup_code,
370 x_req_hdr_id); */
371
372 x_progress := '030';
373
374 SELECT rowid
375 INTO x_rowid
376 FROM po_requisition_headers
377 WHERE requisition_header_id = X_req_hdr_id;
378
379 -- dbms_output.put_line('After selecting rowid');
380
381 /*
382 ** Delete all the distributions and lines
383 ** for this requisition header.
384 */
385
386 x_progress := '040';
387
388 po_reqs_sv.delete_children(X_req_hdr_id);
389
390 -- dbms_output.put_line('After delete children');
391
392 /*
393 ** Delete the attachments.
394 */
395
396 x_progress := '050';
397
398 fnd_attached_documents2_pkg.delete_attachments('REQ_HEADER',
399 x_req_hdr_id,
400 '',
401 '',
402 '',
403 '',
404 'Y');
405
406 /*
407 ** Delete the requisition header.
408 */
409
410 x_progress := '060';
411
412 po_requisition_headers_pkg.delete_row(X_rowid);
413
414 -- dbms_output.put_line('After delete row');
415
416 END IF;
417
418 EXCEPTION
419 WHEN OTHERS THEN
420 -- dbms_output.put_line('In exception');
421 po_message_s.sql_error('delete_req', x_progress, sqlcode);
422 raise;
423 END delete_req;
424
425 /*===========================================================================
426
427 PROCEDURE NAME: insert_req()
428
429 ===========================================================================*/
430
431 PROCEDURE insert_req(X_Rowid IN OUT NOCOPY VARCHAR2,
432 X_Requisition_Header_Id IN OUT NOCOPY NUMBER,
433 X_Preparer_Id NUMBER,
434 X_Last_Update_Date DATE,
435 X_Last_Updated_By NUMBER,
436 X_Segment1 IN OUT NOCOPY VARCHAR2,
437 X_Summary_Flag VARCHAR2,
438 X_Enabled_Flag VARCHAR2,
439 X_Segment2 VARCHAR2,
440 X_Segment3 VARCHAR2,
441 X_Segment4 VARCHAR2,
442 X_Segment5 VARCHAR2,
443 X_Start_Date_Active DATE,
444 X_End_Date_Active DATE,
445 X_Last_Update_Login NUMBER,
446 X_Creation_Date DATE,
447 X_Created_By NUMBER,
448 X_Description VARCHAR2,
449 X_Authorization_Status VARCHAR2,
450 X_Note_To_Authorizer VARCHAR2,
451 X_Type_Lookup_Code VARCHAR2,
452 X_Transferred_To_Oe_Flag VARCHAR2,
453 X_Attribute_Category VARCHAR2,
454 X_Attribute1 VARCHAR2,
455 X_Attribute2 VARCHAR2,
456 X_Attribute3 VARCHAR2,
457 X_Attribute4 VARCHAR2,
458 X_Attribute5 VARCHAR2,
459 X_On_Line_Flag VARCHAR2,
460 X_Preliminary_Research_Flag VARCHAR2,
461 X_Research_Complete_Flag VARCHAR2,
462 X_Preparer_Finished_Flag VARCHAR2,
463 X_Preparer_Finished_Date DATE,
464 X_Agent_Return_Flag VARCHAR2,
465 X_Agent_Return_Note VARCHAR2,
466 X_Cancel_Flag VARCHAR2,
467 X_Attribute6 VARCHAR2,
468 X_Attribute7 VARCHAR2,
469 X_Attribute8 VARCHAR2,
470 X_Attribute9 VARCHAR2,
471 X_Attribute10 VARCHAR2,
472 X_Attribute11 VARCHAR2,
473 X_Attribute12 VARCHAR2,
474 X_Attribute13 VARCHAR2,
475 X_Attribute14 VARCHAR2,
476 X_Attribute15 VARCHAR2,
477 X_Ussgl_Transaction_Code VARCHAR2,
478 X_Government_Context VARCHAR2,
479 X_Interface_Source_Code VARCHAR2,
480 X_Interface_Source_Line_Id NUMBER,
481 X_Closed_Code VARCHAR2,
482 X_Manual BOOLEAN,
483 X_amount NUMBER,
484 X_currency_code VARCHAR2,
485 p_org_id IN NUMBER default null -- <R12 MOAC>
486 ) IS
487
488
489 x_progress VARCHAR2(3) := NULL;
490
491 BEGIN
492
493 x_progress := '010';
494
495 po_requisition_headers_pkg.insert_row(X_Rowid,
496 X_Requisition_Header_Id,
497 X_Preparer_Id,
498 X_Last_Update_Date,
499 X_Last_Updated_By,
500 X_Segment1,
501 X_Summary_Flag,
502 X_Enabled_Flag,
503 X_Segment2,
504 X_Segment3,
505 X_Segment4,
506 X_Segment5,
507 X_Start_Date_Active,
508 X_End_Date_Active,
509 X_Last_Update_Login,
510 X_Creation_Date,
511 X_Created_By,
512 X_Description,
513 X_Authorization_Status,
514 X_Note_To_Authorizer,
515 X_Type_Lookup_Code,
516 X_Transferred_To_Oe_Flag,
517 X_Attribute_Category,
518 X_Attribute1,
519 X_Attribute2,
520 X_Attribute3,
521 X_Attribute4,
522 X_Attribute5,
523 X_On_Line_Flag,
524 X_Preliminary_Research_Flag,
525 X_Research_Complete_Flag,
526 X_Preparer_Finished_Flag,
527 X_Preparer_Finished_Date,
528 X_Agent_Return_Flag,
529 X_Agent_Return_Note,
530 X_Cancel_Flag,
531 X_Attribute6,
532 X_Attribute7,
533 X_Attribute8,
534 X_Attribute9,
535 X_Attribute10,
536 X_Attribute11,
537 X_Attribute12,
538 X_Attribute13,
539 X_Attribute14,
540 X_Attribute15,
541 NULL, --<R12 SLA>
542 X_Government_Context,
543 X_Interface_Source_Code,
544 X_Interface_Source_Line_Id,
545 X_Closed_Code,
546 X_Manual,
547 p_org_id -- <R12 MOAC>
548 );
549
550 -- dbms_output.put_line('After call to insert row');
551
552 /*
553 ** DEBUG. Call the routine to insert
554 ** notifications.
555 */
556
557 x_progress := '020';
558
559 /* bug# 465696 8/5/97. The previous fix to this performance problem introduced
560 a problem with the notifications (the bogus value used temporarily as the
561 document number was being inserted into the fnd_notifications table, since
562 the call below was made before we called the procedure to get the real
563 document number (segment1) in the POST-FORMS-COMMIT trigger.
564 Therefore, remove the call below from here and moving it to procedure
565 PO_REQUISITION_HEADERS_PKG.get_real_segment1.
566 */
567
568 IF X_Manual THEN
569
570 /*hvadlamu : commenting out since notifications will be handled by workflow */
571 /*po_notifications_sv1.send_po_notif (x_type_lookup_code,
572 x_requisition_header_id,
573 x_currency_code,
574 null,
575 null,
576 null,
577 null,
578 null); */
579 null;
580 END IF;
581
582 EXCEPTION
583 WHEN OTHERS THEN
584 -- dbms_output.put_line('In exception');
585 po_message_s.sql_error('insert_req', x_progress, sqlcode);
586 raise;
587 END insert_req;
588
589
590 /*===========================================================================
591
592 PROCEDURE NAME: update_oe_flag
593
594 ===========================================================================*/
595
596 PROCEDURE update_oe_flag(X_req_hdr_id IN NUMBER,
597 X_flag IN VARCHAR2) IS
598
599 x_progress VARCHAR2(3) := NULL;
600
601 BEGIN
602
603 x_progress := '010';
604
605 UPDATE po_requisition_headers
606 SET transferred_to_oe_flag = X_flag
607 WHERE requisition_header_id = X_req_hdr_id;
608
609 -- dbms_output.put_line('After update');
610
611 EXCEPTION
612 WHEN OTHERS THEN
613 -- dbms_output.put_line('In exception');
614 po_message_s.sql_error('update_oe_flag', x_progress, sqlcode);
615 raise;
616 END update_oe_flag;
617
618
619 /*===========================================================================
620
621 PROCEDURE NAME: get_req_startup_values
622
623 ===========================================================================*/
624
625 PROCEDURE get_req_startup_values (X_source_inventory IN OUT NOCOPY VARCHAR2,
626 X_source_vendor IN OUT NOCOPY VARCHAR2) IS
627
628 x_progress VARCHAR2(3) := NULL;
629
630 BEGIN
631
632 x_progress := '010';
633
634 po_core_s.get_displayed_value ('REQUISITION SOURCE TYPE',
635 'INVENTORY',
636 x_source_inventory);
637
638 x_progress := '020';
639
640 po_core_s.get_displayed_value ('REQUISITION SOURCE TYPE',
641 'VENDOR',
642 x_source_vendor);
643
644
645 -- dbms_output.put_line('After update');
646
647 EXCEPTION
648 WHEN OTHERS THEN
649 -- dbms_output.put_line('In exception');
650 po_message_s.sql_error('get_req_startup_values', x_progress, sqlcode);
651 raise;
652 END get_req_startup_values;
653
654
655 END PO_REQS_SV;