1 PACKAGE BODY PO_REQ_LINES_SV as
2 /* $Header: POXRQL1B.pls 120.20.12020000.8 2013/04/18 12:38:39 rparise ship $ */
3 /*========================== po_req_lines_sv ============================*/
4
5
6 -- Constants :
7 -- Read the profile option that enables/disables the debug log
8 g_fnd_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
9
10 /*===========================================================================
11
12 PROCEDURE NAME: lock_row_for_status_update
13
14 ===========================================================================*/
15
16 PROCEDURE lock_row_for_buyer_update (x_rowid IN VARCHAR2)
17 IS
18 CURSOR C IS
19 SELECT *
20 FROM po_requisition_lines
21 WHERE rowid = x_rowid
22 FOR UPDATE of requisition_line_id NOWAIT;
23 Recinfo C%ROWTYPE;
24
25 x_progress VARCHAR2(3) := '';
26
27 BEGIN
28 x_progress := '010';
29 OPEN C;
30 FETCH C INTO Recinfo;
31 IF (C%NOTFOUND) then
32 CLOSE C;
33 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
34 APP_EXCEPTION.Raise_Exception;
35 END IF;
36 CLOSE C;
37
38 EXCEPTION
39 WHEN app_exception.record_lock_exception THEN
40 po_message_s.app_error ('PO_ALL_CANNOT_RESERVE_RECORD');
41
42 WHEN OTHERS THEN
43 -- dbms_output.put_line('In Exception');
44 PO_MESSAGE_S.SQL_ERROR('LOCK_ROW_FOR_BUYER_UPDATE', x_progress, sqlcode);
45 RAISE;
46 END;
47
48
49 /*===========================================================================
50
51 PROCEDURE NAME: delete_line
52
53 ===========================================================================*/
54
55 PROCEDURE delete_line(X_line_id IN NUMBER,
56 X_mode IN VARCHAR2,
57 X_transferred_to_oe_flag OUT NOCOPY VARCHAR2) IS
58
59 x_progress VARCHAR2(3) := NULL;
60 x_rowid VARCHAR2(30);
61
62 BEGIN
63
64 x_progress := '010';
65
66 SELECT rowid
67 INTO x_rowid
68 FROM po_requisition_lines
69 WHERE requisition_line_id = x_line_id;
70
71 -- dbms_output.put_line('After selecting rowid');
72
73 /*
74 ** DEBUG: We need to delete attachments.
75 */
76
77 /*
78 ** Delete the children before deleting the line.
79 */
80
81 x_progress := '020';
82
83 po_req_lines_sv.delete_children(X_line_id, X_mode);
84
85 -- dbms_output.put_line('After call to delete children');
86
87 /*
88 ** Delete the requisition line.
89 */
90
91 x_progress := '030';
92
93 po_requisition_lines_pkg1.delete_row(x_rowid, x_transferred_to_oe_flag);
94
95 -- dbms_output.put_line('After call to delete line');
96
97 EXCEPTION
98 WHEN OTHERS THEN
99 -- dbms_output.put_line('In exception');
100 po_message_s.sql_error('delete_line', x_progress, sqlcode);
101 raise;
102 END delete_line;
103
104 /*===========================================================================
105
106 PROCEDURE NAME: delete_children
107
108 ===========================================================================*/
109
110 PROCEDURE delete_children(X_line_id IN NUMBER,
111 X_mode IN VARCHAR2) IS
112
113 x_progress VARCHAR2(3) := NULL;
114
115 BEGIN
116
117 x_progress := '010';
118
119 /*
120 ** DEBUG. Call to delete attachments.
121 */
122
123 -- dbms_output.put_line('After call to delete attachments');
124
125 x_progress := '020';
126
127 DELETE FROM po_req_distributions
128 WHERE requisition_line_id = X_line_id;
129
130 -- dbms_output.put_line('After call to delete distributions');
131
132 EXCEPTION
133 WHEN OTHERS THEN
134 -- dbms_output.put_line('In exception');
135 po_message_s.sql_error('delete_children', x_progress, sqlcode);
136 raise;
137 END delete_children;
138
139
140
141 /*===========================================================================
142
143 FUNCTION NAME: val_reqs_po_shipment
144
145 ===========================================================================*/
146
147 FUNCTION val_reqs_po_shipment
148 (X_req_header_id IN NUMBER,
149 X_req_line_id IN NUMBER) RETURN BOOLEAN IS
150
151 X_progress VARCHAR2(3) := NULL;
152 X_row_exists NUMBER := 0;
153
154 BEGIN
155
156 -- dbms_output.put_line('Enter val_reqs_po_shipment');
157
158 IF X_req_header_id is NOT NULL OR
159 X_req_line_id is NOT NULL THEN
160
161 /* Search for line exists.
162 ** The following SQL statement is optimized to search for either
163 ** 1. all document lines - if header_id is passed or,
164 ** 2. one document line - if both header_id and line_id are passed.
165 */
166
167 X_progress := '010';
168 SELECT COUNT(1)
169 INTO X_row_exists
170 FROM PO_REQUISITION_LINES PORL,
171 PO_LINE_LOCATIONS_ALL POLL -- Bug 8659519
172 WHERE PORL.requisition_header_id = X_req_header_id
173 AND PORL.requisition_line_id = nvl(X_req_line_id, PORL.requisition_line_id)
174 AND PORL.line_location_id = POLL.line_location_id
175 AND PORL.line_location_id is NOT NULL
176 AND (nvl(POLL.closed_code, 'OPEN') <> 'FINALLY CLOSED'
177 AND nvl(POLL.cancel_flag, 'N') = 'N');
178 ELSE
179 /* DEBUG - show error message */
180 X_progress := '015';
181 po_message_s.sql_error('val_reqs_po_shipment', X_progress, sqlcode);
182 END IF;
183
184 IF X_row_exists > 0 THEN
185 RETURN (FALSE);
186 ELSE
187 RETURN (TRUE);
188 END IF;
189
190 -- dbms_output.put_line('Exit val_reqs_po_shipment');
191
192 EXCEPTION
193 WHEN OTHERS THEN
194 po_message_s.sql_error('val_reqs_po_shipment', X_progress, sqlcode);
195 RAISE;
196
197 END val_reqs_po_shipment;
198
199
200
201 /*===========================================================================
202
203 FUNCTION NAME: val_reqs_oe_shipment
204
205 ===========================================================================*/
206
207 FUNCTION val_reqs_oe_shipment
208 (X_req_header_id IN NUMBER,
209 X_req_line_id IN NUMBER) RETURN BOOLEAN IS
210
211 X_progress VARCHAR2(3) := NULL;
212 X_row_exists NUMBER := 0;
213 p_req_line_id NUMBER;
214
215 Cursor get_req_lines_cur is
216 Select requisition_line_id
217 From po_requisition_lines
218 Where requisition_line_id = nvl(X_req_line_id, requisition_line_id)
219 And requisition_header_id = X_req_header_id
220 and nvl(cancel_flag,'N')<>'Y' --Added for bug 13036681
221 And source_type_code = 'INVENTORY';
222
223 BEGIN
224
225 -- dbms_output.put_line('Enter val_reqs_oe_shipment');
226
227 IF X_req_header_id is NOT NULL OR
228 X_req_line_id is NOT NULL THEN
229
230 /* Search for line exists.
231 ** The following SQL statement is optimized to search for either
232 ** 1. all document lines - if header_id is passed or,
233 ** 2. one document line - if both header_id and line_id are passed.
234 */
235
236 X_progress := '010';
237
238 --Bug# 1392077
239 --Toju George 08/31/2000
240 --Modified the call to procedure to replace req_num and line_num with ids.
241 /* SELECT COUNT(1)
242 INTO X_row_exists
243 FROM PO_REQUISITION_HEADERS PORH, PO_REQUISITION_LINES PORL,
244 PO_SYSTEM_PARAMETERS POSP
245 WHERE PORH.requisition_header_id = X_req_header_id
246 AND PORL.requisition_line_id = nvl(X_req_line_id, PORL.requisition_line_id)
247 AND PORH.requisition_header_id = PORL.requisition_header_id
248 AND PORL.source_type_code = 'INVENTORY'
249 AND OE_ORDER_IMPORT_INTEROP_PUB .Get_Open_Qty(POSP.order_source_id,
250 PORH.segment1,
251 PORL.line_num ) > 0 ;
252 */
253 SELECT COUNT(1)
254 INTO X_row_exists
255 FROM PO_REQUISITION_HEADERS PORH, PO_REQUISITION_LINES PORL,
256 PO_SYSTEM_PARAMETERS POSP
257 WHERE PORH.requisition_header_id = X_req_header_id
258 AND PORL.requisition_line_id = nvl(X_req_line_id, PORL.requisition_line_id)
259 AND PORH.requisition_header_id = PORL.requisition_header_id
260 AND PORL.source_type_code = 'INVENTORY'
261 AND OE_ORDER_IMPORT_INTEROP_PUB .Get_Open_Qty(POSP.order_source_id,
262 PORH.requisition_header_id,
263 PORL.requisition_line_id ) > 0 ;
264 ELSE
265 /* DEBUG - show error message */
266 X_progress := '015';
267 po_message_s.sql_error('val_reqs_oe_shipment', X_progress, sqlcode);
268 END IF;
269
270 IF X_row_exists > 0 THEN
271 RETURN (FALSE);
272 ELSE
273 /* Bug#2492314: kagarwal
274 ** Now call to check whether the SO shipments are still in process.
275 */
276
277 OPEN get_req_lines_cur;
278 LOOP
279 Fetch get_req_lines_cur Into p_req_line_id;
280 Exit When get_req_lines_cur%NotFound;
281
282 If NOT (val_oe_shipment_in_proc(X_req_header_id, p_req_line_id)) Then
283 RETURN(FALSE);
284 End If;
285
286 END LOOP;
287
288 RETURN (TRUE);
289 END IF;
290
291 -- dbms_output.put_line('Exit val_reqs_oe_shipment');
292
293 EXCEPTION
294 WHEN OTHERS THEN
295 po_message_s.sql_error('val_reqs_oe_shipment', X_progress, sqlcode);
296 RAISE;
297
298 END val_reqs_oe_shipment;
299
300
301 /*===========================================================================
302
303 FUNCTION NAME: val_reqs_qty_delivered
304
305 ===========================================================================*/
306
307 FUNCTION val_reqs_qty_delivered
308 (X_req_header_id IN NUMBER,
309 X_req_line_id IN NUMBER) RETURN BOOLEAN IS
310
311 X_progress VARCHAR2(3) := NULL;
312 X_row_exists NUMBER := 0;
313
314 BEGIN
315
316 -- dbms_output.put_line('Enter val_reqs_qty_delivered');
317
318 IF X_req_header_id is NOT NULL OR
319 X_req_line_id is NOT NULL THEN
320
321 /* Search for line exists.
322 ** The following SQL statement is optimized to search for either
323 ** 1. all document lines - if header_id is passed or,
324 ** 2. one document line - if both header_id and line_id are passed.
325 */
326 /* Bug# 5414478, We were using RCV_SHIPMENT_LINES quantity_received
327 to compare with quantity_deliverd in po_requisition_lines.
328 But in certain cases the Req. line and rcv_shipment_line are
329 in different UOM and were not doing the quantity conversion while
330 comparing quantity. Since we store the quantity_received in
331 po_requisition_lines and it is in the same UOM as quantity_received
332 we are going on quantity_received of po_requisition_lines
333 to do the comparision. */
334 X_progress := '010';
335 SELECT COUNT(1)
336 INTO X_row_exists
337 FROM PO_REQUISITION_LINES PORL
338 WHERE PORL.requisition_header_id = X_req_header_id
339 AND PORL.requisition_line_id = nvl(X_req_line_id, PORL.requisition_line_id)
340 AND PORL.source_type_code = 'INVENTORY'
341 AND nvl(PORL.cancel_flag, 'N') = 'N'
342 AND nvl(PORL.closed_code, 'OPEN') <> 'FINALLY CLOSED'
343 AND PORL.quantity_delivered < nvl(PORL.quantity_received,0);
344
345 /* Start Bug# 5414478, Commented the code below
346 (select nvl(sum(quantity_received),0)
347 from RCV_SHIPMENT_LINES RSL
348 where RSL.requisition_line_id = PORL.requisition_line_id);
349 End Bug# 5414478*/
350 ELSE
351 /* DEBUG - show error message */
352 X_progress := '015';
353 po_message_s.sql_error('val_reqs_qty_delivered', X_progress, sqlcode);
354 END IF;
355
356 IF X_row_exists > 0 THEN
357 RETURN (FALSE);
358 ELSE
359 RETURN (TRUE);
360 END IF;
361
362 -- dbms_output.put_line('Exit val_reqs_qty_delivered');
363
364 EXCEPTION
365 WHEN OTHERS THEN
366 po_message_s.sql_error('val_reqs_qty_delivered', X_progress, sqlcode);
367 RAISE;
368
369 END val_reqs_qty_delivered;
370
371
372 /*===========================================================================
373
374 PROCEDURE NAME: update_reqs_lines_incomplete
375
376 ===========================================================================*/
377
378 PROCEDURE update_reqs_lines_incomplete
379 (X_req_header_id IN NUMBER,
380 X_req_line_id IN NUMBER,
381 X_req_control_error_rc IN OUT NOCOPY VARCHAR2,
382 X_oe_installed_flag IN VARCHAR2) IS
383
384 X_progress VARCHAR2(3) := NULL;
385 X_quantity_cancelled PO_REQUISITION_LINES.quantity_cancelled%TYPE := NULL;
386 X_order_source_id po_system_parameters.order_source_id%TYPE;
387
388 BEGIN
389
390 -- dbms_output.put_line('Enter update_reqs_lines_incomplete');
391
392 X_req_control_error_rc := '';
393
394 IF X_req_header_id is NOT NULL OR
395 X_req_line_id is NOT NULL THEN
396
397 X_progress := '010';
398
399 IF (X_oe_installed_flag = 'Y') THEN
400
401 X_progress := '020';
402
403 /* Bug#2275686: kagarwal
404 ** Desc: When OM is installed, we need to get the cancelled qty and
405 ** update it on the Req line before calling the code to reverse
406 ** encumbrance.
407 */
408
409 BEGIN
410 select order_source_id
411 into X_order_source_id
412 from po_system_parameters;
413
414 X_quantity_cancelled := OE_ORDER_IMPORT_INTEROP_PUB.Get_Cancelled_Qty(
415 X_order_source_id,
416 to_char(X_req_header_id),
417 to_char(X_req_line_id));
418 EXCEPTION
419 WHEN NO_DATA_FOUND THEN
420 X_quantity_cancelled := NULL;
421 END;
422
423 ELSE
424 X_quantity_cancelled := NULL;
425 END IF;
426
427 /* The following SQL statement is optimized to update either
428 ** 1. all document lines - if header_id is passed or,
429 ** 2. one document line - if both header_id and line_id are passed.
430 */
431 X_progress := '010';
432 UPDATE PO_REQUISITION_LINES
433 SET cancel_flag = 'I',
434 quantity_cancelled = NVL(X_quantity_cancelled, quantity_cancelled),
435 reqs_in_pool_flag = NULL, -- <REQINPOOL>
436 last_update_login = fnd_global.login_id,
437 last_updated_by = fnd_global.user_id,
438 last_update_date = sysdate
439 WHERE requisition_header_id = X_req_header_id
440 AND requisition_line_id = nvl(X_req_line_id, requisition_line_id)
441 AND nvl(cancel_flag, 'N') IN ('N', 'I')
442 AND nvl(closed_code, 'OPEN') <> 'FINALLY CLOSED';
443
444 ELSE
445 /* DEBUG - show error message */
446 X_req_control_error_rc := 'Y';
447 X_progress := '015';
448 po_message_s.sql_error('update_reqs_lines_incomplete', X_progress, sqlcode);
449 END IF;
450
451 -- dbms_output.put_line('Exit update_reqs_lines_incomplete');
452
453 EXCEPTION
454
455 WHEN OTHERS THEN
456 X_req_control_error_rc := 'Y';
457 po_message_s.sql_error('update_reqs_lines_incomplete', X_progress, sqlcode);
458 RAISE;
459
460
461 END update_reqs_lines_incomplete;
462
463
464 /*===========================================================================
465
466 PROCEDURE NAME: update_reqs_lines_status
467
468 ===========================================================================*/
469
470 PROCEDURE update_reqs_lines_status
471 (X_req_header_id IN NUMBER,
472 X_req_line_id IN NUMBER,
473 X_req_control_action IN VARCHAR2,
474 X_req_control_reason IN VARCHAR2,
475 X_req_action_date IN DATE,
476 X_oe_installed_flag IN VARCHAR2,
477 X_req_control_error_rc IN OUT NOCOPY VARCHAR2) IS
478
479 X_progress VARCHAR2(3) := NULL;
480 X_cancel_flag PO_REQUISITION_LINES.cancel_flag%TYPE := NULL;
481 X_cancel_date PO_REQUISITION_LINES.cancel_date%TYPE := NULL;
482 X_cancel_reason PO_REQUISITION_LINES.cancel_reason%TYPE := NULL;
483 X_closed_code PO_REQUISITION_LINES.closed_code%TYPE := NULL;
484 X_closed_reason PO_REQUISITION_LINES.closed_reason%TYPE := NULL;
485 X_closed_date PO_REQUISITION_LINES.closed_date%TYPE := NULL;
486 X_quantity_cancelled PO_REQUISITION_LINES.quantity_cancelled%TYPE := NULL;
487 X_terminal_performed NUMBER(1) := 0; -- <REQINPOOL>
488 X_order_source_id PO_SYSTEM_PARAMETERS.order_source_id%TYPE;
489 x_last_update_login po_requisition_lines.last_update_login%TYPE := fnd_global.login_id;
490 x_last_updated_by po_requisition_lines.last_updated_by%TYPE := fnd_global.user_id;
491 x_last_update_date po_requisition_lines.last_update_date%TYPE := SYSDATE;
492
493 TYPE requisition_line_id_tb IS TABLE OF po_requisition_lines.requisition_line_id%TYPE INDEX BY PLS_INTEGER;
494 TYPE cancel_flag_tb IS TABLE OF po_requisition_lines.cancel_flag%TYPE INDEX BY PLS_INTEGER;
495 TYPE cancel_date_tb IS TABLE OF po_requisition_lines.cancel_date%TYPE INDEX BY PLS_INTEGER;
496 TYPE cancel_reason_tb IS TABLE OF po_requisition_lines.cancel_reason%TYPE INDEX BY PLS_INTEGER;
497 TYPE closed_code_tb IS TABLE OF po_requisition_lines.closed_code%TYPE INDEX BY PLS_INTEGER;
498 TYPE closed_reason_tb IS TABLE OF po_requisition_lines.closed_reason%TYPE INDEX BY PLS_INTEGER;
499 TYPE closed_date_tb IS TABLE OF po_requisition_lines.closed_date%TYPE INDEX BY PLS_INTEGER;
500 TYPE contractor_status_tb IS TABLE OF po_requisition_lines.contractor_status%TYPE INDEX BY PLS_INTEGER;
501 TYPE last_update_login_tb IS TABLE OF po_requisition_lines.last_update_login%TYPE INDEX BY PLS_INTEGER;
502 TYPE last_updated_by_tb IS TABLE OF po_requisition_lines.last_updated_by%TYPE INDEX BY PLS_INTEGER;
503 TYPE last_update_date_tb IS TABLE OF po_requisition_lines.last_update_date%TYPE INDEX BY PLS_INTEGER;
504 TYPE quantity_cancelled_tb IS TABLE OF po_requisition_lines.quantity_cancelled%TYPE INDEX BY PLS_INTEGER;
505
506 requisition_line_id_v requisition_line_id_tb;
507 cancel_flag_v cancel_flag_tb;
508 cancel_date_v cancel_date_tb;
509 cancel_reason_v cancel_reason_tb;
510 closed_code_v closed_code_tb;
511 closed_reason_v closed_reason_tb;
512 closed_date_v closed_date_tb;
513 contractor_status_v contractor_status_tb;
514 last_update_login_v last_update_login_tb;
515 last_updated_by_v last_updated_by_tb;
516 last_update_date_v last_update_date_tb;
517 quantity_cancelled_v quantity_cancelled_tb;
518
519 -- bug 16240233 :: modifying cursor select statement to consider transferred_to_oe_flag while getting
520 -- cancelled quantity for internal requisitions.
521
522 CURSOR cancel_cursor IS SELECT
523 nvl(X_req_line_id, requisition_line_id),
524 nvl(X_cancel_flag, cancel_flag),
525 nvl(X_cancel_date, cancel_date),
526 nvl(X_cancel_reason, cancel_reason),
527 Nvl(X_closed_code, closed_code),
528 nvl(X_closed_reason, closed_reason),
529 nvl(X_closed_date, closed_date),
530 decode(X_cancel_flag,'Y',null,contractor_status), -- Bug 3495679
531 x_last_update_login,
532 x_last_updated_by,
533 x_last_update_date,
534 decode(X_cancel_flag, 'Y', Decode(SOURCE_TYPE_CODE,'INVENTORY',
535 Decode(TRANSFERRED_TO_OE_FLAG,'Y',
536 OE_ORDER_IMPORT_INTEROP_PUB.Get_Cancelled_Qty(
537 X_order_source_id,
538 to_char(X_req_header_id),
539 Nvl(to_char(X_req_line_id), requisition_line_id)),quantity),
540 quantity - quantity_delivered), quantity_cancelled)
541
542
543 FROM po_requisition_lines
544 WHERE requisition_header_id = X_req_header_id
545 AND requisition_line_id = nvl(X_req_line_id, requisition_line_id)
546 AND nvl(cancel_flag, 'N') IN ('N', 'I')
547 AND nvl(closed_code, 'OPEN') <> 'FINALLY CLOSED';
548
549 BEGIN
550
551 -- dbms_output.put_line('Enter update_reqs_lines_status');
552
553 IF SubStr(X_req_control_action,1,6) = 'CANCEL' THEN
554 X_cancel_flag := 'Y';
555 X_cancel_reason := X_req_control_reason;
556
557 /* <Modified Action Date TZ FPJ 10-10-2003>
558 * change: should set cancel date to current time
559 * previous: set cancel date to action_date field
560 * That field was intended for GL/encumbrance only.
561 */
562
563 X_cancel_date := SYSDATE;
564
565 -- <End Action Date TZ FPJ>
566
567 X_terminal_performed := 1; -- <REQINPOOL>
568
569 ELSIF X_req_control_action = 'FINALLY CLOSE' THEN
570 X_closed_code := 'FINALLY CLOSED';
571 X_closed_reason := X_req_control_reason;
572
573 -- <Modified Action Date TZ FPJ 10-10-2003, similar to above>
574 X_closed_date := SYSDATE;
575 -- <End Action Date TZ FPJ>
576
577 X_terminal_performed := 1; -- <REQINPOOL>
578
579 END IF;
580
581 IF (X_oe_installed_flag = 'Y' AND
582 SubStr(X_req_control_action,1,6) = 'CANCEL') THEN
583
584 /* Bug#2275686: kagarwal
585 ** Desc: When OM is installed, we need to get the cancelled qty and
586 ** update it on the Req line.
587 */
588
589 BEGIN
590 select order_source_id
591 into X_order_source_id
592 from po_system_parameters;
593
594 -- for bug 16240233 adding exception clause to handle cases of req lines not transferred to OE.
595
596 EXCEPTION
597
598 WHEN No_Data_Found THEN
599
600 NULL;
601
602
603 END;
604 END IF;
605
606
607 X_progress := '015';
608
609 /* The following SQL statement is optimized to update either
610 ** 1. all document lines - if only header_id is passed.
611 ** 2. one document line - if line_id is also passed.
612 */
613 /* Bug 4036549 - changed the below sql assignment from
614 quantity_cancelled = nvl(X_quantity_cancelled, quantity_cancelled) to
615 quantity_cancelled = nvl(X_quantity_cancelled, decode(X_cancel_flag,'Y',quantity,quantity_cancelled))
616 */
617
618 --Bug 6849650 - When cancelling from Header level, the cancelled quantity was not updated correctly for
619 --internal requisition. In case of Cancel done from header leve, line_id will be null and hence cancelled
620 --quantity was always taken as null as per previous logic. Changed the logic to get the cancelled quantity.
621 --Also, used bulk collect to improve performance in case of large requisitions.
622
623 OPEN cancel_cursor;
624
625 LOOP
626 FETCH cancel_cursor
627 BULK COLLECT INTO
628 requisition_line_id_v,
629 cancel_flag_v,
630 cancel_date_v,
631 cancel_reason_v,
632 closed_code_v,
633 closed_reason_v,
634 closed_date_v,
635 contractor_status_v,
636 last_update_login_v,
637 last_updated_by_v,
638 last_update_date_v,
639 quantity_cancelled_v
640 LIMIT 2500;
641 EXIT WHEN cancel_flag_v.Count = 0;
642
643 FORALL indx IN requisition_line_id_v.First .. requisition_line_id_v.LAST
644 UPDATE po_requisition_lines SET
645 cancel_flag = cancel_flag_v(indx),
646 cancel_date = cancel_date_v(indx),
647 cancel_reason = cancel_reason_v(indx),
648 closed_code = closed_code_v(indx),
649 closed_reason = closed_reason_v(indx),
650 closed_date = closed_date_v(indx),
651 contractor_status = contractor_status_v(indx),
652 reqs_in_pool_flag = DECODE(X_terminal_performed,
653 1,NULL,
654 reqs_in_pool_flag), -- <REQINPOOL>
655 last_update_login = last_update_login_v(indx),
656 last_updated_by = last_updated_by_v(indx),
657 last_update_date = last_update_date_v(indx),
658 quantity_cancelled = quantity_cancelled_v(indx)
659 WHERE requisition_line_id = requisition_line_id_v(indx);
660 END LOOP;
661
662 -- dbms_output.put_line('Exit update_reqs_lines_status');
663 /* Start Bug 4036549 - updating the req_line_quantity to 0 for 'Purchase Req'*/
664 UPDATE PO_REQ_DISTRIBUTIONS
665 SET req_line_quantity = 0
666 WHERE requisition_line_id IN
667 (SELECT requisition_line_id
668 FROM po_requisition_lines PORL
669 WHERE PORL.requisition_header_id = X_req_header_id
670 AND nvl(PORL.cancel_flag,'N') = 'Y'
671 -- AND PORL.source_type_code = 'VENDOR' /* commenting this condition for bug 16240233 to update dist quantity for internal reqs also */
672 AND PORL.requisition_line_id = nvl(X_req_line_id, PORL.requisition_line_id));
673 /* End Bug 4036549 */
674
675 EXCEPTION
676
677 WHEN OTHERS THEN
678 X_req_control_error_rc := 'Y';
679 po_message_s.sql_error('update_reqs_lines_status', X_progress, sqlcode);
680 RAISE;
681
682 END update_reqs_lines_status;
683
684
685 /*===========================================================================
686
687 PROCEDURE NAME: remove_req_from_po
688
689 ===========================================================================*/
690
691 PROCEDURE remove_req_from_po(X_entity_id IN NUMBER,
692 X_entity IN VARCHAR2) IS
693
694 x_progress VARCHAR2(3) := NULL;
695
696 l_is_clm_document VARCHAR2(1);
697 l_deleted_line_list PO_TBL_NUMBER;
698 l_deleted_dist_list PO_TBL_NUMBER;
699
700
701 --<DropShip FPJ Start>
702 l_po_header_id PO_TBL_NUMBER;
703 l_po_release_id PO_TBL_NUMBER;
704 l_po_line_id PO_TBL_NUMBER;
705 l_line_location_id PO_TBL_NUMBER;
706 L_req_header_id PO_TBL_NUMBER;
707 l_req_line_id PO_TBL_NUMBER;
708 l_linked_req_line_id PO_TBL_NUMBER;
709 l_req_dist_id PO_TBL_NUMBER;
710 l_return_status VARCHAR2(30);
711 l_msg_data VARCHAR2(3000);
712 l_msg_count NUMBER;
713 l_api_name CONSTANT VARCHAR(60) := 'po.plsql.PO_REQ_LINES_SV.REMOVE_REQ_FROM_PO';
714
715 --<DropShip FPJ End>
716
717 BEGIN
718
719 IF g_fnd_debug = 'Y' THEN
720 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
721 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_api_name||'.'
722 || x_progress, 'Entered Procedure. Entity ' || X_entity || ' ID:' || X_entity_id);
723 END IF;
724 END IF;
725
726 IF (X_entity = 'PURCHASE ORDER') THEN
727 x_progress := '010';
728 -- dbms_output.put_line('In update for Purchase Order related req lines');
729
730 --<DropShip FPJ Start>
731 --SQL What: Finds DropShip Req Lines for this Purchase Order Header ID
732 SELECT s.po_header_id,
733 s.po_release_id,
734 s.po_line_id,
735 s.line_location_id,
736 rl.requisition_header_id,
737 rl.requisition_line_id
738 BULK COLLECT INTO
739 l_po_header_id,
740 l_po_release_id,
741 l_po_line_id,
742 l_line_location_id,
743 L_req_header_id,
744 l_req_line_id
745 FROM po_line_locations s, po_requisition_lines rl
746 WHERE s.line_location_id = rl.line_location_id and nvl(rl.drop_ship_flag, 'N') = 'Y'
747 AND s.po_header_id = X_entity_id;
748 --<DropShip FPJ End>
749
750 UPDATE po_requisition_lines_all rl -- Bug 3592153
751 SET rl.line_location_id = NULL,
752 --reqs_in_pool_flag = 'Y', -- Bug 2781027 resetting the reqs in pool flag
753 --Bug 9976204.Set the reqs_in_pool_flag back to Y only if the Req is APPROVED or PRE-APPROVED
754 rl.reqs_in_pool_flag = (SELECT Decode(rh.authorization_status,'APPROVED','Y','PRE-APPROVED','Y',rl.reqs_in_pool_flag)
755 FROM po_requisition_headers_all rh
756 WHERE rh.requisition_header_id = rl.requisition_header_id),
757 rl.last_update_login = fnd_global.login_id, -- Bug5623016 (updating who column)
758 rl.last_updated_by = fnd_global.user_id, -- Bug5623016 (updating who column)
759 rl.last_update_date = sysdate -- Bug5623016 (updating who column)
760 WHERE rl.line_location_id in (SELECT pll.line_location_id
761 FROM po_line_locations_all pll --Bug 8777237: Looking into the base table instead of po_line_locations
762 WHERE pll.po_header_id = X_entity_id);
763
764 ELSIF (X_entity = 'RELEASE') THEN
765 x_progress := '020';
766 -- dbms_output.put_line('In update for Release related req lines');
767
768 --<DropShip FPJ Start>
769 --SQL What: Finds Drop Ship Req Lines for this Release ID
770 SELECT s.po_header_id,
771 s.po_release_id,
772 s.po_line_id,
773 s.line_location_id,
774 rl.requisition_header_id,
775 rl.requisition_line_id
776 BULK COLLECT INTO
777 l_po_header_id,
778 l_po_release_id,
779 l_po_line_id,
780 l_line_location_id,
781 L_req_header_id,
782 l_req_line_id
783 FROM po_line_locations s, po_requisition_lines rl
784 WHERE s.line_location_id = rl.line_location_id and nvl(rl.drop_ship_flag, 'N') = 'Y'
785 AND s.po_release_id = X_entity_id;
786 --<DropShip FPJ End>
787
788 UPDATE po_requisition_lines_all rl -- Bug 3592153
789 SET rl.line_location_id = NULL,
790 --reqs_in_pool_flag = 'Y', -- Bug 2781027
791 --Bug 9976204.Set the reqs_in_pool_flag back to Y only if the Req is APPROVED or PRE-APPROVED
792 rl.reqs_in_pool_flag = (SELECT Decode(rh.authorization_status,'APPROVED','Y','PRE-APPROVED','Y',rl.reqs_in_pool_flag)
793 FROM po_requisition_headers_all rh
794 WHERE rh.requisition_header_id = rl.requisition_header_id),
795 rl.last_update_login = fnd_global.login_id, -- Bug5623016 (updating who column)
796 rl.last_updated_by = fnd_global.user_id, -- Bug5623016 (updating who column)
797 rl.last_update_date = sysdate -- Bug5623016 (upda ting who column)
798 WHERE rl.line_location_id in (SELECT pll.line_location_id
799 FROM po_line_locations_all pll--Bug 8777237: Looking into the base table instead of po_line_locations
800 WHERE pll.po_release_id = X_entity_id);
801
802 ELSIF (X_entity = 'LINE') THEN
803 x_progress := '030';
804 -- dbms_output.put_line('In update for Line related req lines');
805
806 --<DropShip FPJ Start>
807 --SQL What: Finds Drop Ship Req Lines for this Line ID
808 SELECT s.po_header_id,
809 s.po_release_id,
810 s.po_line_id,
811 s.line_location_id,
812 rl.requisition_header_id,
813 rl.requisition_line_id
814 BULK COLLECT INTO
815 l_po_header_id,
816 l_po_release_id,
817 l_po_line_id,
818 l_line_location_id,
819 L_req_header_id,
820 l_req_line_id
821 FROM po_line_locations s, po_requisition_lines rl
822 WHERE s.line_location_id = rl.line_location_id and nvl(rl.drop_ship_flag, 'N') = 'Y'
823 AND s.po_line_id = X_entity_id;
824 --<DropShip FPJ End>
825
826 UPDATE po_requisition_lines_all rl -- Bug 3592153
827 SET rl.line_location_id = NULL,
828 -- reqs_in_pool_flag = 'Y', -- Bug 2781027
829 --Bug 9976204.Set the reqs_in_pool_flag back to Y only if the Req is APPROVED or PRE-APPROVED
830 rl.reqs_in_pool_flag = (SELECT Decode(rh.authorization_status,'APPROVED','Y','PRE-APPROVED','Y',rl.reqs_in_pool_flag)
831 FROM po_requisition_headers_all rh
832 WHERE rh.requisition_header_id = rl.requisition_header_id
833 -- Bug 15875473
834 AND rl.line_location_id in (SELECT pll.line_location_id FROM po_line_locations_all pll WHERE
835 pll.po_line_id = X_entity_id)
836 -- Bug 15875473
837
838 ),
839 rl.last_update_login = fnd_global.login_id, -- Bug5623016 (updating who column)
840 rl.last_updated_by = fnd_global.user_id, -- Bug5623016 (updating who column)
841 rl.last_update_date = sysdate -- Bug5623016 (updating who column)
842 WHERE rl.line_location_id IN (SELECT pll.line_location_id
843 FROM po_line_locations_all pll --Bug 8777237: Looking into the base table instead of po_line_locations
844 WHERE pll.po_line_id = X_entity_id);
845
846 ELSIF (X_entity = 'SHIPMENT') THEN
847 x_progress := '040';
848 -- dbms_output.put_line('In update for Shipment related req lines');
849
850 --<DropShip FPJ Start>
851 --SQL What: Finds Drop Ship Req Lines for this Line Location ID
852 SELECT s.po_header_id,
853 s.po_release_id,
854 s.po_line_id,
855 s.line_location_id,
856 rl.requisition_header_id,
857 rl.requisition_line_id
858 BULK COLLECT INTO
859 l_po_header_id,
860 l_po_release_id,
861 l_po_line_id,
862 l_line_location_id,
863 L_req_header_id,
864 l_req_line_id
865 FROM po_line_locations s, po_requisition_lines rl
866 WHERE s.line_location_id = rl.line_location_id and nvl(rl.drop_ship_flag, 'N') = 'Y'
867 AND s.line_location_id = X_entity_id;
868 --<DropShip FPJ End>
869
870
871 UPDATE po_requisition_lines_all rl -- Bug 3592153
872 SET rl.line_location_id = NULL,
873 --reqs_in_pool_flag = 'Y', -- Bug 2781027
874 --Bug 9976204.Set the reqs_in_pool_flag back to Y only if the Req is APPROVED or PRE-APPROVED
875 rl.reqs_in_pool_flag = (SELECT Decode(rh.authorization_status,'APPROVED','Y','PRE-APPROVED','Y',rl.reqs_in_pool_flag)
876 FROM po_requisition_headers_all rh
877 WHERE rh.requisition_header_id = rl.requisition_header_id),
878 rl.last_update_login = fnd_global.login_id, -- Bug5623016 (updating who column)
879 rl.last_updated_by = fnd_global.user_id, -- Bug5623016 (updating who column)
880 rl.last_update_date = SYSDATE -- Bug5623016 (updating who column)
881 WHERE rl.line_location_id IN (SELECT pll.line_location_id
882 FROM po_line_locations_all pll --Bug 8777237: Looking into the base table instead of po_line_locations
883 WHERE pll.line_location_id = X_entity_id);
884 END IF;
885
886 --<DropShip FPJ Start>
887 -- Remove deleted PO References from Drop Ship Sources Table for all deleted Shipments
888 FOR I IN 1..l_line_location_id.count LOOP
889
890 OE_DROP_SHIP_GRP.Update_Drop_Ship_links(
891 p_api_version => 1.0,
892 p_po_header_id => l_po_header_id(i),
893 p_po_release_id => l_po_release_id(i),
894 p_po_line_id => l_po_line_id(i),
895 p_po_line_location_id => l_line_location_id(i),
896 p_new_req_hdr_id => l_req_header_id(i),
897 p_new_req_line_id => l_req_line_id(i),
898 x_return_status => l_return_status,
899 x_msg_data => l_msg_data,
900 x_msg_count => l_msg_count);
901
902 IF g_fnd_debug = 'Y' THEN
903 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
904 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_api_name|| '.' || x_progress,
905 'After Call to OE_DROP_SHIP_GRP.Update_Drop_Ship_links RetStatus: ' || l_return_status
906 || 'POHeader:' || l_po_header_id(i) || ' Release:' || l_po_release_id(i)
907 || ' Line:' || l_po_line_id(i) || ' LineLoc:' || l_line_location_id(i)
908 || ' ReqHdr:' || l_req_header_id(i) || ' ReqLine:' || l_req_line_id(i));
909 END IF;
910 END IF;
911
912 IF (l_return_status IS NULL) THEN
913 l_return_status := FND_API.g_ret_sts_success;
914 END IF;
915
916 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
917 po_message_s.sql_error('remove_req_from_po', x_progress, l_msg_data);
918 END IF;
919
920 END LOOP;
921 --<DropShip FPJ End>
922
923 EXCEPTION
924 WHEN OTHERS THEN
925 po_message_s.sql_error('remove_req_from_po', x_progress, sqlcode);
926
927 END remove_req_from_po;
928
929
930 /*===========================================================================
931
932 PROCEDURE NAME: val_dest_details()
933
934 ===========================================================================*/
935 PROCEDURE val_dest_details (x_dest_org_id IN OUT NOCOPY NUMBER,
936 x_item_id IN NUMBER,
937 x_item_rev IN VARCHAR,
938 x_location_id IN OUT NOCOPY NUMBER,
939 x_dest_sub IN OUT NOCOPY VARCHAR2,
940 x_dest_type IN VARCHAR2,
941 x_val_code IN VARCHAR2,
942 x_sob_id IN NUMBER) IS
943
944 x_progress VARCHAR2(3) := NULL;
945 x_error_type VARCHAR2(50);
946
947 BEGIN
948
949 x_progress := '010';
950
951 /*
952 ** Stop processing if the destination type
953 ** is null.
954 */
955
956 IF (x_dest_type is null) THEN
957 x_dest_org_id := null;
958 x_location_id := null;
959 x_dest_sub := null;
960
961 return;
962
963 END IF;
964
965
966 /*
967 ** Determine which set of fields are to
968 ** be validated, call the corresponding
969 ** validation functions.
970 */
971
972 IF (x_dest_org_id is null) THEN
973 x_location_id := null;
974 x_dest_sub := null;
975
976 return;
977
978 END IF;
979
980 IF (x_val_code = 'ORG') THEN
981
982 x_progress := '020';
983
984 IF (po_orgs_sv2.val_dest_org (x_dest_org_id,
985 x_item_id,
986 x_item_rev,
987 x_dest_type,
988 x_sob_id,
989 '') = FALSE) THEN
990
991 x_dest_org_id := null;
992 x_location_id := null;
993 x_dest_sub := null;
994
995 return;
996
997 END IF;
998 END IF;
999
1000 IF ((x_val_code = 'ORG') OR
1001 (x_val_code = 'LOC')) THEN
1002
1003 IF (x_location_id is not null) THEN
1004
1005 x_progress := '030';
1006
1007 IF (po_locations_sv2.val_location (x_location_id,
1008 x_dest_org_id,
1009 'N',
1010 'N',
1011 'N') = FALSE) THEN
1012
1013 x_location_id := null;
1014
1015 END IF;
1016 END IF;
1017 END IF;
1018
1019 /*
1020 ** We would like to continue with the
1021 ** validation even if the deliver-to location
1022 ** is null since the subinventory is not
1023 ** dependent on the deliver-to location.
1024 */
1025
1026 IF ((x_val_code = 'ORG') OR
1027 (x_val_code = 'LOC') OR
1028 (x_val_code = 'SUB')) THEN
1029
1030 IF (x_dest_sub is not null) THEN
1031
1032 x_progress := '040';
1033
1034 IF (po_subinventories_s2.val_subinventory (x_dest_sub,
1035 x_dest_org_id,
1036 null,
1037 null,
1038 null,
1039 trunc(sysdate),
1040 x_item_id,
1041 x_dest_type,
1042 'DESTINATION',
1043 x_error_type) = FALSE) THEN
1044 x_dest_sub := null;
1045
1046 END IF;
1047 END IF;
1048 END IF;
1049
1050
1051 EXCEPTION
1052 WHEN OTHERS THEN
1053 po_message_s.sql_error('val_dest_details', x_progress, sqlcode);
1054 RAISE;
1055
1056 END val_dest_details;
1057
1058 /*===========================================================================
1059
1060 PROCEDURE NAME : val_src_type()
1061
1062 ===========================================================================*/
1063
1064 FUNCTION val_src_type( x_src_type IN VARCHAR2,
1065 x_item_id IN NUMBER,
1066 x_internal_orderable IN VARCHAR2,
1067 x_stock_enabled_flag IN VARCHAR2,
1068 x_purchasable IN VARCHAR2,
1069 x_customer_id IN NUMBER,
1070 x_outside_op_line_type IN VARCHAR2)
1071 RETURN BOOLEAN IS
1072
1073 x_progress varchar2(3) := '';
1074
1075
1076 BEGIN
1077
1078
1079 IF (x_src_type is null) THEN
1080 return (FALSE);
1081
1082 END IF;
1083
1084 /*
1085 ** Debug: Have this code
1086 ** reviewed by either Liza or
1087 ** Kevin.
1088 */
1089
1090 IF (x_src_type = 'INVENTORY') THEN
1091 IF ((x_customer_id is null) OR
1092 (x_item_id is null) OR
1093 (x_internal_orderable = 'N') OR
1094 (x_stock_enabled_flag = 'N') OR
1095 (x_outside_op_line_type = 'Y')) THEN
1096
1097 -- Bug 5028505 , Added the Error message when deliver_to_location
1098 -- does not have customer location association setup in src org OU
1099
1100 IF (x_customer_id is null) then
1101 fnd_message.set_name ('PO','PO_REQ_SRC_REQUIRES_CUST');
1102 END IF;
1103
1104 return (FALSE);
1105
1106 ELSE
1107
1108 return (TRUE);
1109
1110 END IF;
1111
1112 ELSIF (x_src_type = 'VENDOR') THEN
1113 IF (x_purchasable = 'N') THEN
1114
1115 return (FALSE);
1116
1117 ELSE
1118
1119 return (TRUE);
1120
1121 END IF;
1122 END IF;
1123
1124 exception
1125 when others then
1126 po_message_s.sql_error('val_src_type',x_progress,sqlcode);
1127 raise;
1128
1129 end val_src_type;
1130
1131
1132
1133 /*===========================================================================
1134
1135 PROCEDURE NAME: update_transferred_to_oe_flag
1136
1137 ===========================================================================*/
1138
1139 PROCEDURE update_transferred_to_oe_flag(X_req_hdr_id IN NUMBER,
1140 X_transferred_to_oe_flag OUT NOCOPY VARCHAR2)
1141 IS
1142
1143 x_progress VARCHAR2(3) := NULL;
1144 x_inv_count NUMBER := 0;
1145 x_flag VARCHAR2(1) := NULL;
1146
1147 BEGIN
1148
1149
1150 /*
1151 ** Verify that there is at least one
1152 ** inventory sourced line.
1153 */
1154
1155 x_progress := '010';
1156
1157 SELECT count(*)
1158 INTO x_inv_count
1159 FROM po_requisition_lines prl
1160 WHERE prl.requisition_header_id = x_req_hdr_id
1161 AND prl.source_type_code = 'INVENTORY';
1162
1163 -- dbms_output.put_line ('x_inv_count: ' ||to_char(x_inv_count));
1164
1165 x_progress := '020';
1166
1167 /*
1168 ** Set the appropriate value for the
1169 ** transferred_to_oe_flag.
1170 */
1171
1172 IF (x_inv_count = 0) THEN
1173 x_flag := NULL;
1174
1175 ELSE
1176
1177 /* Bug: 689677 - transferred_to_oe_flag need to be set to 'N',
1178 only if it is NULL. Need not update the flag, if it is already 'Y'or 'N' */
1179
1180 SELECT transferred_to_oe_flag
1181 INTO x_flag
1182 FROM po_requisition_headers
1183 WHERE requisition_header_id = x_req_hdr_id;
1184
1185 IF (x_flag = 'Y' or x_flag = 'N') then
1186 x_transferred_to_oe_flag := x_flag;
1187 return;
1188 END IF;
1189
1190 IF x_flag is NULL then
1191 x_flag := 'N';
1192 END IF;
1193
1194 END IF;
1195
1196 x_transferred_to_oe_flag := x_flag;
1197
1198 /*
1199 ** Update the flag on requisition headers.
1200 */
1201
1202 x_progress := '030';
1203 -- dbms_output.put_line ('Before updating requisition headers');
1204
1205 po_reqs_sv.update_oe_flag (X_req_hdr_id, X_flag);
1206
1207 -- dbms_output.put_line ('After updating requisition headers');
1208
1209
1210
1211 EXCEPTION
1212 WHEN OTHERS THEN
1213 -- dbms_output.put_line('In exception');
1214 po_message_s.sql_error('update_transferred_to_oe_flag',
1215 x_progress, sqlcode);
1216 raise;
1217 END update_transferred_to_oe_flag;
1218
1219 -- <REQINPOOL BEGIN>
1220
1221
1222 /*===========================================================================
1223
1224 PROCEDURE NAME: update_reqs_in_pool_flag
1225
1226 ===========================================================================*/
1227
1228 PROCEDURE update_reqs_in_pool_flag
1229 ( x_req_line_id IN NUMBER ,
1230 x_req_header_id IN NUMBER ,
1231 x_return_status OUT NOCOPY VARCHAR2
1232 )
1233
1234 IS
1235 x_progress VARCHAR2(3) := NULL;
1236
1237 BEGIN
1238
1239 x_progress := '010';
1240
1241 IF ( x_req_line_id IS NOT NULL OR x_req_header_id IS NOT NULL ) THEN
1242
1243 x_progress := '020';
1244
1245 --SQL What: Update reqs_in_pool_flag for all lines on the passed-in
1246 -- requisition to be NULL where the line has been cancelled,
1247 -- finally closed, attached to a PO, modified since approval,
1248 -- sent to sourcing, or when the requisition is either
1249 -- internal or not approved. If none of these conditions
1250 -- are met, set the flag to 'Y'.
1251 --
1252 --SQL Why: Requisition lines where any of the above applies cannot
1253 -- be placed on a purchasing document; those that fail all
1254 -- the above criteria can.
1255 --
1256 --SQL Join: requisition_header_id from PO_REQUISITION_HEADERS_ALL
1257 UPDATE po_requisition_lines_all prl
1258 SET prl.reqs_in_pool_flag =
1259 CASE
1260 WHEN NVL(prl.cancel_flag,'N') = 'Y'
1261 OR NVL(prl.closed_code,'OPEN') = 'FINALLY CLOSED'
1262 OR NVL(prl.line_location_id,-999) <> -999
1263 OR NVL(prl.po_line_id,-999) <> -999
1264 OR NVL(prl.modified_by_agent_flag,'N') = 'Y'
1265 OR prl.at_sourcing_flag = 'Y'
1266 OR prl.source_type_code <> 'VENDOR'
1267 OR NVL((SELECT prh.authorization_status
1268 FROM PO_REQUISITION_HEADERS_ALL prh
1269 WHERE prh.requisition_header_id = prl.requisition_header_id)
1270 , 'INCOMPLETE') <> 'APPROVED'
1271 OR NVL((SELECT prh.contractor_status
1272 FROM PO_REQUISITION_HEADERS_ALL prh
1273 WHERE prh.requisition_header_id = prl.requisition_header_id)
1274 , 'NOT APPLICABLE') = 'PENDING'
1275 OR DECODE(
1276 (select prh.clm_mipr_type
1277 from po_requisition_headers_all prh
1278 where prh.requisition_header_id = prl.requisition_header_id),
1279 'MIPR_OWN', NVL((select prh.clm_mipr_acknowledged_flag
1280 from po_requisition_headers_all prh
1281 where prh.requisition_header_id = prl.requisition_header_id),'N'),
1282 'MIPR_OTHERS', (select 'Y'
1283 from po_requisition_headers_all prh
1284 where prh.requisition_header_id = prl.requisition_header_id
1285 AND ICX_DATATEMPLATE_PKG.GET_MIPR_ACCEPTANCE_STATUS(
1286 prl.requisition_header_id) = 'ACCEPT'), 'Y') <> 'Y'
1287
1288 THEN
1289 NULL
1290 ELSE
1291 'Y'
1292 END
1293 , prl.last_update_date = SYSDATE
1294 , prl.last_updated_by = FND_GLOBAL.USER_ID
1295 , prl.last_update_login = FND_GLOBAL.LOGIN_ID
1296 WHERE
1297 PRL.Requisition_Line_ID in (
1298 SELECT SUB.Requisition_Line_ID
1299 FROM PO_REQUISITION_LINES_ALL SUB
1300 WHERE SUB.Requisition_Header_Id = x_req_header_id
1301 AND x_req_line_id IS NULL
1302 UNION ALL
1303 SELECT SUB2.Requisition_Line_ID
1304 FROM PO_REQUISITION_LINES_ALL SUB2
1305 WHERE SUB2.Requisition_Line_Id = x_req_line_id
1306 );
1307
1308 ELSE
1309 x_progress := '030';
1310
1311 END IF; -- IF ( x_req_line_id IS NOT NULL OR x_req_header_id IS NOT NULL )
1312
1313 x_progress := '040';
1314 x_return_status := FND_API.G_RET_STS_SUCCESS;
1315
1316 EXCEPTION
1317
1318 WHEN OTHERS THEN
1319 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1320 PO_MESSAGE_S.sql_error('UPDATE_REQS_IN_POOL_FLAG',x_progress,sqlcode);
1321
1322 END update_reqs_in_pool_flag;
1323
1324 -- <REQINPOOL END>
1325
1326 /*===========================================================================
1327
1328 FUNCTION NAME: val_oe_shipment_in_proc
1329
1330 ===========================================================================*/
1331
1332 /* Bug# 2492314: kagarwal */
1333
1334 FUNCTION val_oe_shipment_in_proc
1335 (X_req_header_id IN NUMBER,
1336 X_req_line_id IN NUMBER) RETURN BOOLEAN IS
1337
1338 X_progress VARCHAR2(3) := NULL;
1339 X_order_source_id po_system_parameters.order_source_id%TYPE;
1340 X_req_num po_requisition_headers_all.segment1%TYPE;
1341 X_oe_line_tbl OE_ORDER_IMPORT_INTEROP_PUB.LineId_Tbl_Type;
1342 X_oe_return VARCHAR2(30);
1343 X_oe_line_ctr NUMBER := 0;
1344
1345 X_in_rec_type WSH_INTEGRATION.LineIntfInRecType;
1346 X_out_rec_type WSH_INTEGRATION.LineIntfOutRecType;
1347 X_io_rec_type WSH_INTEGRATION.LineIntfInOutRecType;
1348 X_wsh_return VARCHAR2(200);
1349 X_msg_count NUMBER;
1350 X_msg_data VARCHAR2(200);
1351
1352 BEGIN
1353
1354 IF X_req_header_id is NOT NULL AND
1355 X_req_line_id is NOT NULL THEN
1356
1357 X_progress := '010';
1358
1359 select order_source_id
1360 into X_order_source_id
1361 from po_system_parameters;
1362
1363 X_progress := '020';
1364
1365 SELECT segment1
1366 INTO X_req_num
1367 FROM po_requisition_headers_all
1368 WHERE requisition_header_id = X_req_header_id;
1369
1370 X_progress := '030';
1371
1372 OE_ORDER_IMPORT_INTEROP_PUB.Get_Line_Id (
1373 p_order_source_id => X_order_source_id,
1374 p_orig_sys_document_ref => X_req_num,
1375 p_requisition_header_id => X_req_header_id,
1376 p_line_num => NULL,
1377 p_requisition_line_id => X_req_line_id,
1378 x_line_id_tbl => X_oe_line_tbl,
1379 x_return_status => X_oe_return);
1380
1381 X_progress := '040';
1382
1383 If X_oe_return = fnd_api.g_ret_sts_success then
1384
1385 /* If the table X_oe_line_tbl is empty, return TRUE */
1386 If (X_oe_line_tbl.FIRST is NULL) Then
1387 return(TRUE);
1388 End If;
1389
1390 FOR X_oe_line_ctr IN 1..X_oe_line_tbl.COUNT
1391 LOOP
1392 X_in_rec_type.api_version_number := 1.0;
1393 X_in_rec_type.source_code := 'PO';
1394 X_in_rec_type.line_id := X_oe_line_tbl(X_oe_line_ctr).line_id;
1395
1396 WSH_INTEGRATION.Get_Nonintf_Shpg_line_qty
1397 ( p_in_attributes => X_in_rec_type,
1398 p_out_attributes => X_out_rec_type,
1399 p_inout_attributes => X_io_rec_type,
1400 x_return_status => X_wsh_return,
1401 x_msg_count => X_msg_count,
1402 x_msg_data => X_msg_data);
1403
1404 If X_wsh_return = fnd_api.g_ret_sts_success then
1405 If X_out_rec_type.nonintf_line_qty > 0 then
1406 return FALSE;
1407
1408 End If; /* If X_out_rec_type.nonintf_line_qty */
1409
1410 Else
1411 /* DEBUG - show error message */
1412 X_progress := '080';
1413 po_message_s.sql_error('val_oe_shipment_in_proc', X_progress, sqlcode);
1414 End If; /* If X_wsh_return */
1415
1416 END LOOP;
1417
1418 Else
1419 /* DEBUG - show error message */
1420 X_progress := '090';
1421 po_message_s.sql_error('val_oe_shipment_in_proc', X_progress, sqlcode);
1422
1423 End If; /* If X_oe_return */
1424
1425 /* Nothing stuck in mti */
1426 X_progress := '100';
1427 return TRUE;
1428
1429 ELSE
1430 /* DEBUG - show error message */
1431 X_progress := '999';
1432 po_message_s.sql_error('val_oe_shipment_in_proc', X_progress, sqlcode);
1433 End IF; /* IF X_req_header_id */
1434
1435 EXCEPTION
1436 WHEN OTHERS THEN
1437 po_message_s.sql_error('val_oe_shipment_in_proc', X_progress, sqlcode);
1438 RAISE;
1439
1440 END val_oe_shipment_in_proc;
1441
1442 /* Bug# 2607180: kagarwal
1443 ** Desc: Verify if internal requisition lines which are sourced from inventory,
1444 ** have been received or not. For this we will check if the 'SHIPMENT' supply
1445 ** exists for the requisition lines.
1446 **
1447 ** If 'SHIPMENT' supply exists return FALSE else TRUE
1448 */
1449
1450 /*===========================================================================
1451
1452 FUNCTION NAME: val_reqs_qty_received
1453
1454 ===========================================================================*/
1455
1456 FUNCTION val_reqs_qty_received
1457 (X_req_header_id IN NUMBER,
1458 X_req_line_id IN NUMBER) RETURN BOOLEAN IS
1459
1460 X_progress VARCHAR2(3) := NULL;
1461 X_row_exists NUMBER := 0;
1462
1463 BEGIN
1464 IF X_req_header_id is NOT NULL OR
1465 X_req_line_id is NOT NULL THEN
1466
1467 X_progress := '010';
1468
1469 SELECT COUNT(1)
1470 INTO X_row_exists
1471 FROM MTL_SUPPLY
1472 WHERE req_header_id = X_req_header_id
1473 AND req_line_id = NVL(X_req_line_id, req_line_id)
1474 AND supply_type_code = 'SHIPMENT'
1475 AND quantity > 0;
1476
1477 ELSE
1478 /* DEBUG - show error message */
1479 X_progress := '015';
1480 po_message_s.sql_error('val_reqs_qty_received', X_progress, sqlcode);
1481 END IF;
1482
1483 IF X_row_exists > 0 THEN
1484 RETURN (FALSE);
1485 ELSE
1486 RETURN (TRUE);
1487 END IF;
1488
1489 EXCEPTION
1490 WHEN OTHERS THEN
1491 po_message_s.sql_error('val_reqs_qty_received', X_progress, sqlcode);
1492 RAISE;
1493
1494 END val_reqs_qty_received;
1495
1496 /*===========================================================================
1497 PROCEDURE NAME: update_req_for_linked_po_count
1498
1499 DESCRIPTION: It'll update the linked po count of the requsitions those are linked to the
1500 deleted entities(Line, Schedule, Distribution) and mark(insert into GT table) the req line's
1501 Clin(AutoCreate and SoftLinked) and Pslin(SoftLinked) for further processing
1502 in the next step, to update the req pool flag for its whole strucure and send it back to pool
1503
1504
1505 ===========================================================================*/
1506 PROCEDURE update_req_for_linked_po_count(
1507 X_entity_id IN PO_TBL_NUMBER,
1508 X_entity IN VARCHAR2)
1509 IS
1510
1511 l_unlinked_reqLine_ids PO_TBL_NUMBER := PO_TBL_NUMBER() ;
1512 l_unlinked_po_count PO_TBL_NUMBER := PO_TBL_NUMBER();
1513 l_eligible_reqLine_ids PO_TBL_NUMBER := PO_TBL_NUMBER() ;
1514 l_eligible_po_line_ids PO_TBL_NUMBER := PO_TBL_NUMBER() ;
1515 l_eligible_group_line_id PO_TBL_NUMBER := PO_TBL_NUMBER() ;
1516 l_eligible_clm_info_flag PO_TBL_VARCHAR1 := PO_TBL_VARCHAR1() ;
1517 l_eligible_option_indicator PO_TBL_VARCHAR1 := PO_TBL_VARCHAR1() ;
1518 l_eligible_clm_base_line_num PO_TBL_NUMBER := PO_TBL_NUMBER() ;
1519 l_pclin_ids PO_TBL_NUMBER := PO_TBL_NUMBER() ;
1520
1521 l_info_funded_slin_ids PO_TBL_NUMBER := PO_TBL_NUMBER() ;
1522 l_info_funded_slin_count PO_TBL_NUMBER := PO_TBL_NUMBER() ;
1523
1524 l_non_infofunded_slin_ids PO_TBL_NUMBER := PO_TBL_NUMBER() ;
1525 l_non_infofunded_slin_count PO_TBL_NUMBER := PO_TBL_NUMBER() ;
1526
1527 l_infofunded_slin_grp_ids PO_TBL_NUMBER := PO_TBL_NUMBER() ;
1528 l_infofd_group_line_ids PO_TBL_NUMBER := PO_TBL_NUMBER() ;
1529
1530
1531 l_clm_document VARCHAR2(1);
1532
1533 l_api_name CONSTANT VARCHAR(60) := 'po.plsql.PO_REQ_LINES_SV.UPDATE_REQ_FOR_LINKED_PO_COUNT';
1534 x_progress VARCHAR2(3) := NULL;
1535
1536 BEGIN
1537
1538 x_progress := 10;
1539
1540 IF g_fnd_debug = 'Y' THEN
1541 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1542 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_api_name||'.'
1543 || x_progress, 'Entered update_req_for_linked_po_count Procedure. Entity ' || X_entity );
1544
1545 FOR I IN 1..X_entity_id.count LOOP
1546 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_api_name||'.'
1547 || x_progress, 'Entity('||I||'):'|| X_entity_id(I) );
1548 END LOOP;
1549
1550 END IF;
1551 END IF;
1552
1553 BEGIN
1554 -- For Award lines and Mod lines. It will process only the Info(Non Funded) and Option lines
1555 -- Priced lines will be processed later when called from PO_DISTRIBUTIONS_DRAFT_PVT
1556 IF (X_entity = 'LINE') THEN
1557
1558 -- Linked_PO _Count would be 1 for all req lines, as Info and Option lines
1559 -- can be linked to Award only by Autocreate and its one to one mapping.
1560 SELECT requisition_line_id, linked_po_count
1561 BULK COLLECT INTO l_unlinked_reqLine_ids, l_unlinked_po_count
1562 FROM po_requisition_lines_all reqline, TABLE(X_entity_id) entity
1563 WHERE (reqline.clm_info_flag = 'Y' OR reqline.clm_option_indicator = 'O'
1564 OR reqLine.par_draft_id IS NOT NULL) --To process PAR lines without any distribution
1565 -- Dod - Exclude Info Funded Slins
1566 AND NOT EXISTS ( SELECT 1 FROM po_req_distributions_all prd
1567 WHERE prd.info_line_id = reqline.requisition_line_id)
1568 AND reqline.po_line_id = entity.column_value;
1569 -- For priced lines.
1570 ELSIF (X_entity = 'DISTRIBUTION') THEN
1571
1572 BEGIN
1573 -- Collect Req lines(Not Info Funded for Dod) which are linked using Autocreate or SoftLink to the Award
1574 SELECT reqdist.requisition_line_id , Count(1)
1575 BULK COLLECT INTO l_non_infofunded_slin_ids, l_non_infofunded_slin_count
1576 FROM po_distributions_all pod, po_req_distributions_all reqdist, TABLE(X_entity_id) entity
1577 WHERE pod.req_distribution_id = reqdist.distribution_id
1578 AND reqdist.info_line_id IS NULL
1579 AND pod.po_distribution_id = entity.column_value
1580 GROUP BY reqdist.requisition_line_id ;
1581
1582 EXCEPTION WHEN No_Data_Found THEN
1583 -- If not records found, do nothing.
1584 NULL;
1585 END;
1586
1587 x_progress := 15;
1588 IF g_fnd_debug = 'Y' THEN
1589 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1590 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_api_name|| '.' || x_progress,
1591 'No of l_non_infofunded_slin_ids requisitions :'||l_non_infofunded_slin_ids.Count||' l_non_infofunded_slin_count:'||l_non_infofunded_slin_count.Count );
1592 END IF;
1593 END IF;
1594
1595
1596 BEGIN
1597 -- Dod , Collect req lines(Info Funded) which are linked using Autocreate or SoftLink to the Award
1598 -- Fecth info_line_id, rather the requisition_line_id, so that we get InfoFundedLineId
1599 SELECT reqdist.info_line_id , Min(reqdist.requisition_line_id), Count(1)
1600 BULK COLLECT INTO l_info_funded_slin_ids, l_infofunded_slin_grp_ids, l_info_funded_slin_count
1601 FROM po_distributions_all pod, po_req_distributions_all reqdist, TABLE(X_entity_id) entity
1602 WHERE pod.req_distribution_id = reqdist.distribution_id
1603 AND reqdist.info_line_id IS NOT NULL
1604 AND pod.po_distribution_id = entity.column_value
1605 GROUP BY reqdist.info_line_id ;
1606
1607 EXCEPTION WHEN No_Data_Found THEN
1608 -- If not records found, do nothing.
1609 NULL;
1610 END;
1611
1612 x_progress := 18;
1613 IF g_fnd_debug = 'Y' THEN
1614 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1615 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_api_name|| '.' || x_progress,
1616 'No of l_info_funded_slin_ids requisitions :'||l_info_funded_slin_ids.Count||' l_info_funded_slin_count:'||l_info_funded_slin_count.Count );
1617 END IF;
1618 END IF;
1619
1620
1621
1622 l_unlinked_reqLine_ids := l_non_infofunded_slin_ids MULTISET UNION l_info_funded_slin_ids;
1623 l_unlinked_po_count := l_non_infofunded_slin_count MULTISET UNION l_info_funded_slin_count;
1624
1625 ELSIF (X_entity = 'DISTRIBUTION MOD') THEN
1626
1627 BEGIN
1628 -- Collect Req lines(Not Info Funded for Dod) which are linked using Autocreate or SoftLink to the Modification
1629 SELECT reqdist.requisition_line_id , Count(1)
1630 BULK COLLECT INTO l_non_infofunded_slin_ids, l_non_infofunded_slin_count
1631 FROM po_distributions_draft_all pod, po_req_distributions_all reqdist, TABLE(X_entity_id) entity
1632 WHERE pod.req_distribution_id = reqdist.distribution_id
1633 AND reqdist.info_line_id IS NULL
1634 AND pod.po_distribution_id = entity.column_value
1635 AND change_status = 'NEW'
1636 GROUP BY reqdist.requisition_line_id ;
1637
1638 EXCEPTION WHEN No_Data_Found THEN
1639 -- If not records found, do nothing.
1640 NULL;
1641 END;
1642
1643 x_progress := 15;
1644 IF g_fnd_debug = 'Y' THEN
1645 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1646 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_api_name|| '.' || x_progress,
1647 'No of l_non_infofunded_slin_ids requisitions :'||l_non_infofunded_slin_ids.Count||' l_non_infofunded_slin_count:'||l_non_infofunded_slin_count.Count );
1648 END IF;
1649 END IF;
1650
1651
1652 BEGIN
1653 -- Dod , Collect req lines(Info Funded) which are linked using Autocreate or SoftLink to the Modification
1654 -- Fecth info_line_id, rather the requisition_line_id, so that we get InfoFundedLineId
1655 SELECT reqdist.info_line_id , Min(reqdist.requisition_line_id), Count(1)
1656 BULK COLLECT INTO l_info_funded_slin_ids, l_infofunded_slin_grp_ids, l_info_funded_slin_count
1657 FROM po_distributions_draft_all pod, po_req_distributions_all reqdist, TABLE(X_entity_id) entity
1658 WHERE pod.req_distribution_id = reqdist.distribution_id
1659 AND reqdist.info_line_id IS NOT NULL
1660 AND pod.po_distribution_id = entity.column_value
1661 AND change_status = 'NEW'
1662 GROUP BY reqdist.info_line_id ;
1663
1664 EXCEPTION WHEN No_Data_Found THEN
1665 -- If not records found, do nothing.
1666 NULL;
1667 END;
1668
1669 x_progress := 18;
1670 IF g_fnd_debug = 'Y' THEN
1671 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1672 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_api_name|| '.' || x_progress,
1673 'No of l_info_funded_slin_ids requisitions :'||l_info_funded_slin_ids.Count||' l_info_funded_slin_count:'||l_info_funded_slin_count.Count );
1674 END IF;
1675 END IF;
1676
1677 l_unlinked_reqLine_ids := l_non_infofunded_slin_ids MULTISET UNION l_info_funded_slin_ids;
1678 l_unlinked_po_count := l_non_infofunded_slin_count MULTISET UNION l_info_funded_slin_count;
1679
1680
1681 END IF;
1682
1683 EXCEPTION WHEN No_Data_Found THEN
1684 -- If not records found, do nothing.
1685 NULL;
1686 END;
1687
1688 x_progress := 20;
1689 IF g_fnd_debug = 'Y' THEN
1690 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1691 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_api_name|| '.' || x_progress,
1692 'No of unlinked requisitions :'||l_unlinked_reqLine_ids.Count ||' l_unlinked_po_count:'||l_unlinked_po_count.Count );
1693 END IF;
1694 END IF;
1695 -- If atleast on requisition line found whioh is linked to deleted entity.
1696 IF l_unlinked_reqLine_ids.Count > 0 THEN
1697 -- Update the linked po count by decrementing it.
1698 FORALL i IN 1..l_unlinked_reqLine_ids.Count
1699 UPDATE po_requisition_lines_all
1700 SET linked_po_count = linked_po_count - l_unlinked_po_count(i),
1701 last_update_date = SYSDATE,
1702 last_updated_by = FND_GLOBAL.USER_ID,
1703 last_update_login = FND_GLOBAL.LOGIN_ID
1704 WHERE requisition_line_id = l_unlinked_reqLine_ids(i);
1705
1706 x_progress := 30;
1707 IF g_fnd_debug = 'Y' THEN
1708 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1709 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_api_name|| '.' || x_progress,
1710 'Linked Po Count Updated.'||SQL%ROWCOUNT);
1711 END IF;
1712 END IF;
1713
1714 -- Nullify the po-Line-id and line_location_id for the all the requisition lines
1715 -- whose linked_po_count is zero.
1716 FORALL i IN 1..l_unlinked_reqLine_ids.Count
1717 UPDATE po_requisition_lines_all
1718 SET po_line_id = NULL, line_location_id = NULL
1719 WHERE requisition_line_id = l_unlinked_reqLine_ids(i)
1720 AND linked_po_count = 0;
1721
1722 x_progress := 40;
1723 IF g_fnd_debug = 'Y' THEN
1724 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1725 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_api_name|| '.' || x_progress,
1726 'Po Line Id and Po Line Loc Id Updated Po Count Update.'||SQL%ROWCOUNT);
1727 END IF;
1728 END IF;
1729 -- This is handled seperately for distributions deleted from distributions tab
1730 -- We have Autocreated PClin-ISlin, we deleted the PClin Dist from distribution tab, saved it. It will update linkedPoCount to 0 for PClin.
1731 -- We came back and now delete the PClin Line(From Line tab now), it'll now have only deleted Slin's Ids, but not the
1732 -- PClin(as its reference dist already deleted and no Req Ref). But we are considering only the Clins for Autocreated to store in GT,
1733 -- hence though all LinkedPoCount is 0, it'll not be sent back to pool.
1734 IF (X_entity = 'DISTRIBUTION' OR X_entity = 'DISTRIBUTION MOD') THEN
1735 -- Priced Clins those got deleted from Ditribution tab
1736 BEGIN
1737 SELECT requisition_line_id
1738 BULK COLLECT INTO l_pclin_ids
1739 FROM po_requisition_lines_all rl, TABLE(l_unlinked_reqLine_ids) entity
1740 WHERE rl.requisition_line_id = entity.column_value
1741 AND group_line_id IS NULL
1742 AND Nvl(clm_info_flag,'N') <> 'Y' AND Nvl(clm_option_indicator,'X') <> 'O';
1743
1744 EXCEPTION WHEN No_Data_Found THEN
1745 NULL;
1746 END;
1747 -- Nullify the po-Line-id, line_location_id and linked_po_count to 0 for the all Slins,
1748 -- if deleted/unlinked line is Priced Clin from distribution tab
1749 FORALL i IN 1..l_pclin_ids.Count
1750 UPDATE po_requisition_lines_all
1751 SET po_line_id = NULL, line_location_id = NULL , linked_po_count = 0
1752 WHERE group_line_id = l_pclin_ids(i);
1753
1754 x_progress := 50;
1755 IF g_fnd_debug = 'Y' THEN
1756 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1757 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_api_name|| '.' || x_progress,
1758 'Slins for PriceClin ,Po Line Id and Po Line Loc Id Updated Po Count Update.'||SQL%ROWCOUNT);
1759 END IF;
1760 END IF;
1761
1762 x_progress := 60;
1763 IF g_fnd_debug = 'Y' THEN
1764 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1765 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_api_name|| '.' || x_progress,
1766 'No of l_infofunded_slin_grp_ids requisitions :'||l_infofunded_slin_grp_ids.Count );
1767 END IF;
1768 END IF;
1769
1770 -- For Dod, Info Funded Slins
1771 IF (l_infofunded_slin_grp_ids.Count > 0) THEN
1772
1773 BEGIN
1774 -- Check if all the Info Funded Slins are unlinked for the group_line_id
1775 SELECT group_line_id
1776 BULK COLLECT INTO l_infofd_group_line_ids
1777 FROM po_requisition_lines_all rl, TABLE(l_infofunded_slin_grp_ids) entity
1778 WHERE group_line_id = entity.column_value
1779 GROUP BY group_line_id HAVING Sum(Nvl(linked_po_count,0)) = 0;
1780
1781 EXCEPTION WHEN No_Data_Found THEN
1782 NULL;
1783 END;
1784
1785 x_progress := 70;
1786 IF g_fnd_debug = 'Y' THEN
1787 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1788 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_api_name|| '.' || x_progress,
1789 'No of l_infofd_group_line_ids requisitions :'||l_infofd_group_line_ids.Count );
1790 END IF;
1791 END IF;
1792
1793 -- Update the Priced Clin of Info Funded Slins, if all Slins unlinked
1794 FORALL i IN 1..l_infofd_group_line_ids.Count
1795 UPDATE po_requisition_lines_all
1796 SET po_line_id = NULL, line_location_id = NULL , linked_po_count = 0
1797 WHERE requisition_line_id = l_infofd_group_line_ids(i);
1798
1799 -- Enter Priced Clin of Info funded slins into GT table, for sending all reqs in structure back to pool
1800 FORALL i IN 1..l_infofd_group_line_ids.Count
1801 INSERT INTO po_session_gt(
1802 index_char1,
1803 index_num1, -- ReqLineId
1804 index_num2, -- ReqHeadreId
1805 num1, --GroupLineId
1806 num2, -- CLmBaseLineNum
1807 num3, -- LinkedPOCount
1808 num10 -- Strcuture Id (Would be used to identify the structure)
1809 )
1810 SELECT 'UNLINKED_REQUISITIONS',
1811 requisition_line_id,
1812 requisition_header_id,
1813 group_line_id,
1814 clm_base_line_num,
1815 linked_po_count,
1816 requisition_line_id
1817 FROM po_requisition_lines_all l
1818 WHERE requisition_line_id = l_infofd_group_line_ids(i);
1819
1820 x_progress := 80;
1821 IF g_fnd_debug = 'Y' THEN
1822 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1823 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_api_name|| '.' || x_progress,
1824 'Inserted into GT.'||SQL%ROWCOUNT);
1825 END IF;
1826 END IF;
1827
1828 END IF;
1829
1830 END IF;
1831 -- We will indert all Clins(Auticreated and SoftLinked) and Priced Slins(SoftLinked using Link Requisitions action)
1832 -- for inserting into the GT table,
1833 -- which is used in update_reqs_in_pool_flag for fetching all the IDs of that ClinSlin structure
1834 FORALL i IN 1..l_unlinked_reqLine_ids.Count
1835 INSERT INTO po_session_gt(
1836 index_char1,
1837 index_num1, -- ReqLineId
1838 index_num2, -- ReqHeadreId
1839 num1, --GroupLineId
1840 num2, -- CLmBaseLineNum
1841 num3, -- LinkedPOCount
1842 num10 -- Strcuture Id (Would be used to identify the structure)
1843 )
1844 SELECT 'UNLINKED_REQUISITIONS',
1845 requisition_line_id,
1846 requisition_header_id,
1847 group_line_id,
1848 clm_base_line_num,
1849 linked_po_count,
1850 requisition_line_id
1851 FROM po_requisition_lines_all l
1852 WHERE requisition_line_id = l_unlinked_reqLine_ids(i)
1853 AND (
1854 (group_line_id IS NULL ) -- All Clins (AutoCreate and SoftLinked)
1855 OR
1856 (group_line_id IS NOT NULL AND po_line_id = -1) -- PSlin for SoftLinked
1857 );
1858
1859 x_progress := 90;
1860 IF g_fnd_debug = 'Y' THEN
1861 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1862 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_api_name|| '.' || x_progress,
1863 'Inserted into GT.'||SQL%ROWCOUNT);
1864 END IF;
1865 END IF;
1866
1867 END IF;
1868
1869 IF g_fnd_debug = 'Y' THEN
1870 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1871 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_api_name||'.'
1872 || x_progress, 'Completed update_req_for_linked_po_count Procedure. Entity ' || X_entity );
1873 END IF;
1874 END IF;
1875
1876 EXCEPTION WHEN OTHERS THEN
1877
1878 RAISE;
1879 PO_MESSAGE_S.add_exc_msg
1880 ( p_pkg_name => 'PO_REQ_LINES_SV',
1881 p_procedure_name => 'update_req_for_linked_po_count' || '.' || x_progress
1882 );
1883
1884 END update_req_for_linked_po_count;
1885
1886 /*===========================================================================
1887 PROCEDURE NAME: update_par_draft_line_status
1888
1889 DESCRIPTION: This procedure will update the DRAFT_LINE_STATUS of the PAR lines
1890 to appropriate status once deleted from mod.
1891
1892 ===========================================================================*/
1893
1894 PROCEDURE update_par_draft_line_status
1895 (p_reqLine_ids IN PO_TBL_NUMBER,
1896 p_reqHeader_ids IN PO_TBL_NUMBER
1897 )
1898 IS
1899 l_api_name CONSTANT VARCHAR(60) := 'po.plsql.PO_REQ_LINES_SV.update_par_draft_line_status';
1900 x_progress VARCHAR2(3) := '000';
1901 BEGIN
1902 x_progress := 10;
1903
1904 IF g_fnd_debug = 'Y' THEN
1905 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1906 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_api_name||'.'
1907 || x_progress, 'Entered Procedure.update_par_draft_line_status');
1908 END IF;
1909 END IF;
1910
1911 FORALL i IN 1..p_reqLine_ids.Count
1912 UPDATE po_lines_draft_all
1913 SET DRAFT_LINE_STATUS = 'COMPLETED'
1914 WHERE (po_line_id, draft_id) IN ( SELECT par_line_id, par_draft_id
1915 FROM po_requisition_lines_all
1916 WHERE requisition_line_id = p_reqLine_ids(i)
1917 AND requisition_header_id = p_reqHeader_ids(i)
1918 AND assignment_number IS NULL
1919 );
1920
1921 FORALL i IN 1..p_reqLine_ids.Count
1922 UPDATE po_lines_draft_all
1923 SET DRAFT_LINE_STATUS = 'ASSIGNED'
1924 WHERE (po_line_id, draft_id) IN ( SELECT par_line_id, par_draft_id
1925 FROM po_requisition_lines_all
1926 WHERE requisition_line_id = p_reqLine_ids(i)
1927 AND requisition_header_id = p_reqHeader_ids(i)
1928 AND assignment_number IS NOT NULL
1929 );
1930
1931 x_progress := 20;
1932 IF g_fnd_debug = 'Y' THEN
1933 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1934 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_api_name||'.'
1935 || x_progress, 'Completed update_par_draft_line_status Procedure.');
1936 END IF;
1937 END IF;
1938
1939 END update_par_draft_line_status;
1940
1941
1942 /*===========================================================================
1943 PROCEDURE NAME: update_reqs_in_pool_flag
1944
1945 DESCRIPTION: This procedure will update the ReqsInPoolFlag of the requisition lines
1946 by checking its entire clin-slin structure for any linked POs.
1947
1948 ===========================================================================*/
1949 PROCEDURE update_reqs_in_pool_flag
1950 IS
1951
1952 l_api_name CONSTANT VARCHAR(60) := 'po.plsql.PO_REQ_LINES_SV.UPDATE_REQS_IN_POOL_FLAG';
1953 x_progress VARCHAR2(3) := '000';
1954
1955 l_eligible_reqLine_ids PO_TBL_NUMBER := PO_TBL_NUMBER() ;
1956 l_eligible_reqHeader_ids PO_TBL_NUMBER := PO_TBL_NUMBER() ;
1957 l_structure_ids PO_TBL_NUMBER := PO_TBL_NUMBER() ;
1958 l_linked_po_count PO_TBL_NUMBER := PO_TBL_NUMBER() ;
1959
1960 l_update_req_pool VARCHAR2(1) := 'N';
1961
1962 BEGIN
1963
1964 x_progress := 10;
1965
1966 IF g_fnd_debug = 'Y' THEN
1967 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1968 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, l_api_name||'.'
1969 || x_progress, 'Entered Procedure.update_reqs_in_pool_flag');
1970 END IF;
1971 END IF;
1972
1973 -- This loop will insert the whole structure into the GT table.
1974 -- As the Clin-Slin structure relation is based on three columns, group_line_id,
1975 -- clm_base_line_num and requisition_line_id, this will give us the duplicate records but will give the
1976 -- entire structure, which can be used to check if any the req lines still linked to PO, else send the entire
1977 -- structure back to pool.
1978 -- This iterates till it won't found any records to insert. NOT EXISTS statment will handle the number of
1979 -- iterations and make sure it wont go in infinite loop.
1980
1981 WHILE 1=1
1982 LOOP
1983 INSERT INTO po_session_gt gt3 (index_char1, index_num1, index_num2, num1, num2, num3, num10)
1984
1985 SELECT 'UNLINKED_REQUISITIONS', r.requisition_line_id, r.requisition_header_id, r.group_line_id, r.clm_base_line_num, r.linked_po_count, num10
1986 FROM po_requisition_lines_all r,
1987 po_session_gt gt
1988 WHERE
1989 r.requisition_header_id = gt.index_num2
1990 AND gt.index_char1 = 'UNLINKED_REQUISITIONS'
1991 AND ( r.requisition_line_id = gt.index_num1
1992 OR r.group_line_id = gt.index_num1
1993 OR r.clm_base_line_num = gt.index_num1
1994 OR r.requisition_line_id = gt.num1
1995 OR r.group_line_id = gt.num1
1996 OR r.clm_base_line_num = gt.num1
1997 OR r.requisition_line_id = gt.num2
1998 OR r.group_line_id = gt.num2
1999 OR r.clm_base_line_num = gt.num2
2000 )
2001 AND NOT EXISTS (SELECT 1 FROM po_session_gt gt2
2002 WHERE gt2.index_num1 = r.requisition_line_id
2003 AND r.requisition_header_id = gt2.index_num2
2004 AND gt2.index_char1 = 'UNLINKED_REQUISITIONS'
2005 AND gt2.num10 = gt.num10
2006 );
2007
2008 IF (SQL%ROWCOUNT = 0) THEN
2009 exit;
2010 END IF;
2011
2012 END loop;
2013
2014 l_eligible_reqLine_ids := PO_TBL_NUMBER();
2015 l_eligible_reqHeader_ids := PO_TBL_NUMBER();
2016
2017 -- Collect the entire structure into table type, if that structure is eligible.
2018 -- Note: Delete can happen to PO lines, which are linked to 1 or more Clin-Slin structures
2019 -- and structure Id(Num10) will identify the unique structure.
2020 SELECT distinct
2021 index_num1,
2022 index_num2
2023
2024 BULK COLLECT INTO
2025 l_eligible_reqLine_ids,
2026 l_eligible_reqHeader_ids
2027
2028 FROM po_session_gt ogt
2029 WHERE index_char1 = 'UNLINKED_REQUISITIONS'
2030 AND num10 IN (SELECT num10 FROM po_session_gt igt
2031 WHERE igt.index_char1 = 'UNLINKED_REQUISITIONS'
2032 GROUP BY num10 HAVING sum(Nvl(num3,0)) <= 0
2033 ) ;
2034
2035 -- Update the reqs_in_pool_flag for the entire Clin-Slin structure if it dont have any
2036 -- POs linked. Above l_eligible_reqLine_ids will give us the list.
2037 FORALL i IN 1..l_eligible_reqLine_ids.Count
2038 UPDATE po_requisition_lines_all
2039 SET reqs_in_pool_flag = 'Y',
2040 linked_po_count = NULL,
2041 last_update_date = SYSDATE,
2042 last_updated_by = FND_GLOBAL.USER_ID,
2043 last_update_login = FND_GLOBAL.LOGIN_ID
2044 WHERE requisition_line_id = l_eligible_reqLine_ids(i)
2045 AND requisition_header_id = l_eligible_reqHeader_ids(i) ;
2046
2047 --Call update_par_draft_line_status to update draft_line_status on PAR lines
2048 update_par_draft_line_status(p_reqLine_ids => l_eligible_reqLine_ids,
2049 p_reqHeader_ids => l_eligible_reqHeader_ids );
2050
2051 -- Refresh the GT table once this method end.
2052 DELETE po_session_gt WHERE index_char1 = 'UNLINKED_REQUISITIONS';
2053
2054 EXCEPTION WHEN OTHERS THEN
2055
2056 x_progress := 100;
2057 PO_MESSAGE_S.add_exc_msg
2058 ( p_pkg_name => 'PO_REQ_LINES_SV',
2059 p_procedure_name => 'update_reqs_in_pool_flag' || '.' || x_progress
2060 );
2061 DELETE po_session_gt WHERE index_char1 = 'UNLINKED_REQUISITIONS';
2062
2063 END update_reqs_in_pool_flag ;
2064
2065 END po_req_lines_sv;
[Home] [Help]