[Home] [Help]
PACKAGE BODY: APPS.PO_SUPPLY
Source
1 PACKAGE BODY po_supply AS
2 /* $Header: RCVRCSUB.pls 120.22.12020000.2 2012/07/10 09:28:25 ptkumar ship $ */
3
4
5
6 /* ----------------------------------------------------------------------- */
7 /* */
8 /* Private Function Definition */
9 /* */
10 /* ----------------------------------------------------------------------- */
11
12
13 FUNCTION approve_req(p_docid IN NUMBER) RETURN BOOLEAN;
14
15
16 FUNCTION remove_req(
17 p_entity_id IN NUMBER
18 , p_entity_type IN VARCHAR2
19 ) RETURN BOOLEAN;
20
21
22 FUNCTION remove_req_vend_lines(p_docid IN NUMBER) RETURN BOOLEAN;
23
24
25 FUNCTION update_req_line_qty(
26 p_lineid IN NUMBER
27 , p_qty IN NUMBER
28 ) RETURN BOOLEAN;
29
30
31 FUNCTION update_req_line_date(
32 p_lineid IN NUMBER
33 , p_receipt_date IN DATE
34 ) RETURN BOOLEAN;
35
36
37 FUNCTION update_planned_po(
38 p_docid IN NUMBER
39 , p_shipid IN NUMBER DEFAULT 0
40 , p_entity_type IN VARCHAR2
41 , p_supply_flag IN OUT NOCOPY BOOLEAN
42 ) RETURN BOOLEAN;
43
44
45
46
47 /* ----------------------------------------------------------------------- */
48 /* */
49 /* Switchboard for PO and Requisition Actions */
50 /* */
51 /* Invokes the required PO and Requisition functions depending on the */
52 /* Action being passed in */
53 /* */
54 /* ----------------------------------------------------------------------- */
55
56
57 FUNCTION po_req_supply(
58 p_docid IN NUMBER
59 , p_lineid IN NUMBER
60 , p_shipid IN NUMBER
61 , p_action IN VARCHAR2
62 , p_recreate_flag IN BOOLEAN
63 , p_qty IN NUMBER
64 , p_receipt_date IN DATE
65 , p_reservation_action IN VARCHAR2 DEFAULT NULL --<R12 PLAN CROSS DOCK>
66 , p_ordered_uom IN VARCHAR2 DEFAULT NULL --5253916
67 ) RETURN BOOLEAN
68 IS
69
70 --<R12 PLAN CROSS DOCK START>
71 l_recreate_flag VARCHAR2(1);
72 l_return_status VARCHAR2(1);
73 l_action VARCHAR2(200);
74 --<R12 PLAN CROSS DOCK END>
75
76 d_module VARCHAR2(70) := 'po.plsql.PO_SUPPLY.po_req_supply';
77 d_progress NUMBER;
78 l_doc_id NUMBER;
79
80 l_return_value BOOLEAN;
81
82 BEGIN
83
84 IF (PO_LOG.d_proc) THEN
85 PO_LOG.proc_begin(d_module);
86 PO_LOG.proc_begin(d_module, 'p_docid', p_docid);
87 PO_LOG.proc_begin(d_module, 'p_lineid', p_lineid);
88 PO_LOG.proc_begin(d_module, 'p_shipid', p_shipid);
89 PO_LOG.proc_begin(d_module, 'p_action', p_action);
90 PO_LOG.proc_begin(d_module, 'p_recreate_flag', p_recreate_flag);
91 PO_LOG.proc_begin(d_module, 'p_qty', p_qty);
92 PO_LOG.proc_begin(d_module, 'p_receipt_date', p_receipt_date);
93 PO_LOG.proc_begin(d_module, 'p_reservation_action', p_reservation_action);
94 PO_LOG.proc_begin(d_module, 'p_ordered_uom', p_ordered_uom);
95 END IF;
96
97 d_progress := 10;
98
99 --<R12 PLAN CROSS DOCK START>
100 IF p_recreate_flag THEN
101 l_recreate_flag := 'Y';
102 ELSE
103 l_recreate_flag := 'N';
104 END IF;
105 --<R12 PLAN CROSS DOCK END>
106
107 d_progress := 20;
108
109 BEGIN
110
111 IF (p_action = 'Approve_Req_Supply') THEN
112
113 d_progress := 30;
114 l_return_value := approve_req(p_docid => p_docid);
115
116 ELSIF (p_action = 'Remove_Req_Supply') THEN
117
118 d_progress := 40;
119 l_return_value := remove_req(
120 p_entity_id => p_docid
121 , p_entity_type => 'REQ HDR'
122 );
123
124 ELSIF (p_action = 'Remove_Return_Req_Supply') THEN
125
126 d_progress := 50;
127 l_return_value := remove_req_vend_lines(p_docid => p_docid);
128
129 ELSIF (p_action = 'Remove_Req_Line_Supply') THEN
130
131 d_progress := 60;
132 l_return_value := remove_req(
133 p_entity_id => p_lineid
134 , p_entity_type => 'REQ LINE'
135 );
136
137 ELSIF (p_action = 'Create_Req_Line_Supply') THEN
138
139 d_progress := 70;
140 l_return_value := create_req(
141 p_entity_id => p_lineid
142 , p_entity_type => 'REQ LINE'
143 );
144
145 ELSIF (p_action = 'Explode_Req') THEN
146
147 d_progress := 80;
148 l_return_value := explode(p_lineid => p_lineid);
149
150 ELSIF (p_action = 'Update_Req_Line_Qty') THEN
151
152 d_progress := 90;
153 l_return_value := update_req_line_qty(
154 p_lineid => p_lineid
155 , p_qty => p_qty
156 );
157
158 ELSIF (p_action = 'Update_Req_Line_Date') THEN
159
160 d_progress := 100;
161 l_return_value := update_req_line_date(
162 p_lineid => p_lineid
163 , p_receipt_date => p_receipt_date
164 );
165
166 ELSIF (p_action = 'Approve_PO_Supply') THEN
167
168 d_progress := 110;
169 l_return_value := approve_po_supply(p_docid => p_docid);
170
171 ELSIF (p_action = 'Approve_Blanket_Release_Supply') THEN
172
173 d_progress := 120;
174 l_return_value := approve_blanket_supply(p_docid => p_docid);
175
176 ELSIF (p_action = 'Approve_Planned_Release_Supply') THEN
177
178 d_progress := 130;
179 l_return_value := approve_planned_supply(p_docid => p_docid);
180
181 ELSIF (p_action = 'Create_PO_Supply') THEN
182
183 d_progress := 140;
184 l_return_value := create_po_supply(
185 p_entity_id => p_docid
186 , p_entity_type => 'PO'
187 );
188
189 ELSIF (p_action = 'Create_Release_Supply') THEN
190
191 d_progress := 150;
192 l_return_value := create_po_supply(
193 p_entity_id => p_docid
194 , p_entity_type => 'RELEASE'
195 );
196
197 ELSIF (p_action = 'Create_PO_Line_Supply') THEN
198
199 d_progress := 160;
200 l_return_value := create_po_supply(
201 p_entity_id => p_lineid
202 , p_entity_type => 'PO LINE'
203 );
204
205 ELSIF (p_action = 'Create_PO_Shipment_Supply') THEN
206
207 d_progress := 170;
208 l_return_value := create_po_supply(
209 p_entity_id => p_shipid
210 , p_entity_type => 'PO SHIPMENT'
211 );
212
213 ELSIF (p_action = 'Create_Release_Shipment_Supply') THEN
214
215 d_progress := 180;
216 l_return_value := create_po_supply(
217 p_entity_id => p_shipid
218 , p_entity_type => 'RELEASE SHIPMENT'
219 );
220
221 ELSIF (p_action = 'Remove_PO_Supply') THEN
222
223 d_progress := 190;
224 l_return_value := delete_supply(
225 p_entity_id => p_docid
226 , p_entity_type => 'PO'
227 );
228
229 ELSIF (p_action = 'Remove_Release_Supply') THEN
230
231 d_progress := 200;
232 l_return_value := delete_supply(
233 p_entity_id => p_docid
234 , p_entity_type => 'RELEASE'
235 );
236
237 ELSIF (p_action = 'Remove_PO_Line_Supply') THEN
238
239 d_progress := 210;
240 l_return_value := update_supply(
241 p_entity_id => p_lineid
242 , p_entity_type => 'PO LINE'
243 );
244
245 ELSIF (p_action = 'Remove_PO_Shipment_Supply') THEN
246
247 d_progress := 220;
248 l_return_value := update_supply(
249 p_entity_id => p_shipid
250 , p_entity_type => 'PO SHIPMENT'
251 );
252
253 ELSIF (p_action = 'Remove_Release_Shipment') THEN
254
255 d_progress := 230;
256 l_return_value := update_supply(
257 p_entity_id => p_docid
258 , p_entity_type => 'RELEASE SHIPMENT'
259 , p_shipid => p_shipid
260 );
261
262 ELSIF (p_action = 'Cancel_PO_Supply') THEN
263
264 d_progress := 240;
265 l_return_value := cancel_supply(
266 p_entity_id => p_docid
267 , p_entity_type => 'PO'
268 );
269
270 ELSIF (p_action = 'Cancel_PO_Line') THEN
271
272 d_progress := 250;
273 l_return_value := cancel_supply(
274 p_entity_id => p_lineid
275 , p_entity_type => 'PO LINE'
276 );
277
278 ELSIF (p_action = 'Cancel_PO_Shipment') THEN
279
280 d_progress := 260;
281 l_return_value := cancel_supply(
282 p_entity_id => p_shipid
283 , p_entity_type => 'PO SHIPMENT'
284 );
285
286 ELSIF (p_action = 'Cancel_Blanket_Release') THEN
287
288 d_progress := 270;
289 l_return_value := cancel_supply(
290 p_entity_id => p_docid
291 , p_entity_type => 'RELEASE'
292 );
293
294 ELSIF (p_action = 'Cancel_Blanket_Shipment') THEN
295
296 d_progress := 280;
297 l_return_value := cancel_supply(
298 p_entity_id => p_docid
299 , p_entity_type => 'RELEASE SHIPMENT'
300 , p_shipid => p_shipid
301 );
302
303 ELSIF (p_action = 'Cancel_Planned_Release') THEN
304
305 d_progress := 290;
306 l_return_value := cancel_planned(
307 p_entity_id => p_docid
308 , p_entity_type => 'RELEASE'
309 , p_recreate_flag => p_recreate_flag
310 );
311
312 ELSIF (p_action = 'Cancel_Planned_Shipment') THEN
313
314 d_progress := 300;
315 l_return_value := cancel_planned(
316 p_entity_id => p_docid
317 , p_entity_type => 'RELEASE SHIPMENT'
318 , p_shipid => p_shipid
319 , p_recreate_flag => p_recreate_flag
320 );
321
322 END IF; -- Switchboard
323
324 IF (NOT l_return_value) THEN
325
326 IF (PO_LOG.d_stmt) THEN
327 PO_LOG.stmt(d_module, d_progress, 'Supply action failed.');
328 END IF;
329
330 RAISE PO_CORE_S.g_early_return_exc;
331
332 END IF;
333
334
335 d_progress := 600;
336
337 l_return_value := maintain_mtl_supply;
338
339 IF (NOT l_return_value) THEN
340
341 IF (PO_LOG.d_stmt) THEN
342 PO_LOG.stmt(d_module, d_progress, 'maintain_mtl_supply not successful.');
343 END IF;
344
345 RAISE PO_CORE_S.g_early_return_exc;
346
347 END IF;
348
349 d_progress := 700;
350
351
352 --< R12 PLAN CROSS DOCK START >
353
354 --Maintain Reservations
355 --UPDATE_SO_QUANTITY would be passed by OM for update order quantity
356
357 IF (UPPER(p_reservation_action) = 'UPDATE_SO_QUANTITY') THEN
358
359 d_progress := 710;
360
361 IF (PO_LOG.d_stmt) THEN
362 PO_LOG.stmt(d_module, d_progress, 'p_reservation_action', p_reservation_action);
363 END IF;
364
365 PO_RESERVATION_MAINTAIN_SV.MAINTAIN_RESERVATION(
366 p_header_id => p_docid
367 , p_line_id => p_lineid
368 , p_line_location_id => p_shipid
369 , p_action => p_reservation_action
370 , p_ordered_quantity => p_qty
371 , p_recreate_demand_flag => l_recreate_flag
372 , p_ordered_uom => p_ordered_uom --5253916
373 , x_return_status => l_return_status
374 );
375
376 --Bug5060175 START
377 ELSIF p_action = 'Explode_Req' THEN
378
379 d_progress := 720;
380 IF (PO_LOG.d_stmt) THEN
381 PO_LOG.stmt(d_module, d_progress, 'Calling PO Maintain Reservations for Req split');
382 END IF;
383
384 select requisition_header_id
385 into l_doc_id
386 from po_requisition_lines_clm_v
387 where requisition_line_id = p_lineid;
388
389 PO_RESERVATION_MAINTAIN_SV.MAINTAIN_RESERVATION(
390 p_header_id => p_docid
391 , p_line_id => p_lineid
392 , p_line_location_id => p_shipid
393 , p_action => 'Remove_Req_Line_Supply'
394 , p_recreate_demand_flag => l_recreate_flag
395 , x_return_status => l_return_status
396 );
397
398
399 PO_RESERVATION_MAINTAIN_SV.MAINTAIN_RESERVATION(
400 p_header_id => l_doc_id
401 , p_line_id => NULL
402 , p_line_location_id => NULL
403 , p_action => 'Approve_Req_Supply'
404 , p_recreate_demand_flag => l_recreate_flag
405 , x_return_status => l_return_status
406 );
407
408 --Bug5060175 END
409
410 ELSIF p_action NOT IN ( 'Remove_PO_Supply'
411 ,'Remove_PO_Line_Supply'
412 ,'Remove_PO_Shipment_Supply'
413 ) THEN
414
415 --Bug 5255656: Reverting the change to pass cancel reservation
416 -- actions for Close actions apart from FINALLY_CLOSE
417 -- Though the actions would be handled with no action by the INV Reservation API,
418 -- it would better to filter out these actions at this point, to facilate better
419 -- maintenance
420
421 d_progress := 730;
422 IF (PO_LOG.d_stmt) THEN
423 PO_LOG.stmt(d_module, d_progress, 'Calling PO Maintain Reservations');
424 END IF;
425
426 PO_RESERVATION_MAINTAIN_SV.MAINTAIN_RESERVATION(
427 p_header_id => p_docid
428 , p_line_id => p_lineid
429 , p_line_location_id => p_shipid
430 , p_action => p_action
431 , p_recreate_demand_flag => l_recreate_flag
432 , x_return_status => l_return_status
433 );
434
435 END IF; -- IF (UPPER(p_reservation_action) = 'UPDATE_SO_QUANTITY')...
436
437 d_progress := 800;
438
439 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
440 IF (PO_LOG.d_stmt) THEN
441 PO_LOG.stmt(d_module, d_progress, 'Reservation api failed.');
442 END IF;
443
444 l_return_value := FALSE;
445 RAISE PO_CORE_S.g_early_return_exc;
446
447 END IF;
448
449 -- End Maintain Reservations
450 --< R12 PLAN CROSS DOCK END >
451
452 l_return_value := TRUE;
453
454 EXCEPTION
455 WHEN PO_CORE_S.g_early_return_exc THEN
456 NULL;
457 END;
458
459 IF (PO_LOG.d_proc) THEN
460 PO_LOG.proc_return(d_module, l_return_value);
461 PO_LOG.proc_end(d_module);
462 END IF;
463
464 return(l_return_value);
465
466
467 EXCEPTION
468
469 WHEN others THEN
470 IF (PO_LOG.d_exc) THEN
471 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
472 END IF;
473
474 return(FALSE);
475
476 END po_req_supply;
477
478
479 /* ----------------------------------------------------------------------- */
480 /* */
481 /* Update mtl_supply for an Approve PO Action */
482 /* */
483 /* ----------------------------------------------------------------------- */
484
485
486 FUNCTION approve_po_supply(p_docid IN NUMBER) RETURN BOOLEAN IS
487
488 l_auth_status po_headers.authorization_status%TYPE;
489
490 -- <Doc Manager Rewrite R12>: This cursor was incorrectly accessing
491 -- po_requisition_headers instead of po_headers. Verified against
492 -- older Pro*C code, which was using po_headers.
493
494 /*Bug 4537860:Hit the _all tables instead of the striped views.*/
495 CURSOR auth_status(header_id NUMBER)
496 IS
497 SELECT poh.authorization_status
498 FROM po_headers_all poh
499 WHERE poh.po_header_id = header_id;
500
501 d_module VARCHAR2(70) := 'po.plsql.PO_SUPPLY.approve_po_supply';
502 d_progress NUMBER;
503
504 l_return_value BOOLEAN := FALSE;
505
506 BEGIN
507
508 IF (PO_LOG.d_proc) THEN
509 PO_LOG.proc_begin(d_module);
510 PO_LOG.proc_begin(d_module, 'p_docid', p_docid);
511 END IF;
512
513 d_progress := 10;
514
515 OPEN auth_status(p_docid);
516 FETCH auth_status INTO l_auth_status;
517 CLOSE auth_status;
518
519 d_progress := 20;
520
521 BEGIN
522
523 -- Create PO Supply if the PO has been Approved
524
525 IF (l_auth_status = 'APPROVED') THEN
526
527 d_progress := 30;
528
529 l_return_value := create_po_supply(
530 p_entity_id => p_docid
531 , p_entity_type => 'PO'
532 );
533
534 IF (NOT l_return_value) THEN
535
536 IF (PO_LOG.d_stmt) THEN
537 PO_LOG.stmt(d_module, d_progress, 'create_po_supply not successful');
538 END IF;
539
540 RAISE PO_CORE_S.g_early_return_exc;
541 END IF;
542
543
544 d_progress := 40;
545
546 -- Remove Old Requisition Supply
547 /*Bug 4537860:Hit the _all tables instead of the striped views.*/
548
549 UPDATE mtl_supply ms
550 SET ms.quantity = 0
551 , ms.change_flag = 'Y'
552 WHERE ms.supply_type_code = 'REQ'
553 AND ms.supply_source_id IN
554 (
555 SELECT prl.requisition_line_id
556 FROM po_requisition_lines_clm_v prl
557 , po_distributions_all pd
558 WHERE prl.line_location_id = pd.line_location_id
559 AND pd.po_header_id = p_docid
560 );
561
562 d_progress := 50;
563
564 IF (PO_LOG.d_stmt) THEN
565 PO_LOG.stmt(d_module, d_progress, 'Updated ' || SQL%ROWCOUNT || ' rows.');
566 END IF;
567
568 END IF; -- if (l_auth_status = 'APPROVED')...
569
570 l_return_value := TRUE;
571
572 EXCEPTION
573 WHEN PO_CORE_S.g_early_return_exc THEN
574 NULL;
575 END;
576
577 IF (PO_LOG.d_proc) THEN
578 PO_LOG.proc_return(d_module, l_return_value);
579 PO_LOG.proc_end(d_module);
580 END IF;
581
582 return (l_return_value);
583
584 EXCEPTION
585 WHEN others THEN
586
587 IF auth_status%ISOPEN THEN
588 close auth_status;
589 END IF;
590
591 IF (PO_LOG.d_exc) THEN
592 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
593 END IF;
594
595 return(FALSE);
596
597 END approve_po_supply;
598
599
600
601 /* ----------------------------------------------------------------------- */
602 /* */
603 /* Update mtl_supply for an Approve Blanket Release Action */
604 /* */
605 /* ----------------------------------------------------------------------- */
606
607
608
609 FUNCTION approve_blanket_supply(p_docid IN NUMBER)
610 RETURN BOOLEAN
611 IS
612
613 l_auth_status po_headers.authorization_status%TYPE;
614 /*Bug 4537860:Hit the _all tables instead of the striped views.*/
615 CURSOR auth_status(release_id NUMBER)
616 IS
617 SELECT por.authorization_status
618 FROM po_releases_all por
619 WHERE por.po_release_id = release_id;
620
621 d_module VARCHAR2(70) := 'po.plsql.PO_SUPPLY.approve_blanket_supply';
622 d_progress NUMBER;
623
624 l_return_value BOOLEAN := FALSE;
625
626 BEGIN
627
628 IF (PO_LOG.d_proc) THEN
629 PO_LOG.proc_begin(d_module);
630 PO_LOG.proc_begin(d_module, 'p_docid', p_docid);
631 END IF;
632
633 d_progress := 10;
634
635 OPEN auth_status(p_docid);
636 FETCH auth_status INTO l_auth_status;
637 CLOSE auth_status;
638
639 d_progress := 20;
640
641 BEGIN
642
643 -- Create PO Release Supply if the Release has been Approved
644
645 IF (l_auth_status = 'APPROVED') THEN
646
647 d_progress := 30;
648
649 l_return_value := create_po_supply(
650 p_entity_id => p_docid
651 , p_entity_type => 'RELEASE'
652 );
653
654 IF (NOT l_return_value) THEN
655
656 IF (PO_LOG.d_stmt) THEN
657 PO_LOG.stmt(d_module, d_progress, 'create_po_supply not successful');
658 END IF;
659
660 RAISE PO_CORE_S.g_early_return_exc;
661 END IF;
662
663
664 d_progress := 40;
665
666
667 -- Remove Old Requisition Supply
668 --Bugfix5219471: Removed POD and used '_ALL' tables for share memory issue.
669 UPDATE mtl_supply ms
670 SET ms.quantity = 0
671 , ms.change_flag = 'Y'
672 WHERE ms.supply_type_code = 'REQ'
673 AND ms.supply_source_id IN
674 (
675 SELECT prl.requisition_line_id
676 FROM po_requisition_lines_clm_v prl
677 , po_line_locations_all pll
678 WHERE prl.line_location_id = pll.line_location_id
679 AND pll.po_release_id = p_docid
680 );
681
682 d_progress := 50;
683
684 IF (PO_LOG.d_stmt) THEN
685 PO_LOG.stmt(d_module, d_progress, 'Updated ' || SQL%ROWCOUNT || ' rows.');
686 END IF;
687
688 END IF; -- if (l_auth_status = 'APPROVED')...
689
690 l_return_value := TRUE;
691
692 EXCEPTION
693 WHEN PO_CORE_S.g_early_return_exc THEN
694 NULL;
695 END;
696
697 IF (PO_LOG.d_proc) THEN
698 PO_LOG.proc_return(d_module, l_return_value);
699 PO_LOG.proc_end(d_module);
700 END IF;
701
702 return (l_return_value);
703
704 EXCEPTION
705 WHEN others THEN
706
707 IF auth_status%ISOPEN THEN
708 close auth_status;
709 END IF;
710
711 IF (PO_LOG.d_exc) THEN
712 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
713 END IF;
714
715 return(FALSE);
716
717 END approve_blanket_supply;
718
719
720 /* ----------------------------------------------------------------------- */
721 /* */
722 /* Update mtl_supply for an Approve Planned Release Action */
723 /* */
724 /* ----------------------------------------------------------------------- */
725
726 FUNCTION approve_planned_supply(p_docid IN NUMBER)
727 RETURN BOOLEAN
728 IS
729
730 l_auth_status po_releases.authorization_status%TYPE;
731 l_po_header_id po_releases.po_header_id%TYPE;
732
733 l_supply_flag BOOLEAN;
734 /*Bug 4537860:Hit the _all tables instead of the striped views.*/
735 CURSOR auth_status(release_id NUMBER)
736 IS
737 SELECT por.authorization_status, por.po_header_id
738 FROM po_releases_all por
739 WHERE por.po_release_id = release_id;
740
741 d_module VARCHAR2(70) := 'po.plsql.PO_SUPPLY.approve_planned_supply';
742 d_progress NUMBER;
743
744 l_return_value BOOLEAN := FALSE;
745
746 BEGIN
747
748 IF (PO_LOG.d_proc) THEN
749 PO_LOG.proc_begin(d_module);
750 PO_LOG.proc_begin(d_module, 'p_docid', p_docid);
751 END IF;
752
753 d_progress := 10;
754
755 OPEN auth_status(p_docid);
756 FETCH auth_status INTO l_auth_status, l_po_header_id;
757 CLOSE auth_status;
758
759 d_progress := 20;
760
761
762 BEGIN
763
764 -- Create PO Release Supply if the Release has been Approved
765
766 IF (l_auth_status = 'APPROVED') THEN
767
768 d_progress := 30;
769
770 l_return_value := create_po_supply(
771 p_entity_id => p_docid
772 , p_entity_type => 'RELEASE'
773 );
774
775 IF (NOT l_return_value) THEN
776
777 IF (PO_LOG.d_stmt) THEN
778 PO_LOG.stmt(d_module, d_progress, 'create_po_supply not successful');
779 END IF;
780
781 RAISE PO_CORE_S.g_early_return_exc;
782 END IF;
783
784
785 d_progress := 40;
786
787
788 -- Remove Planned PO Supply
789
790 -- <Doc Manager Rewrite R12>: Pro*C and existing code
791 -- conflicted in how planned PO supply was removed. The
792 -- Pro*C version was used. Calling create_supply will
793 -- do the necessary subtractions.
794
795 l_return_value := create_po_supply(
796 p_entity_id => l_po_header_id
797 , p_entity_type => 'PO'
798 );
799
800 IF (NOT l_return_value) THEN
801
802 IF (PO_LOG.d_stmt) THEN
803 PO_LOG.stmt(d_module, d_progress, 'create_po_supply not successful');
804 END IF;
805
806 RAISE PO_CORE_S.g_early_return_exc;
807 END IF;
808
809 END IF; -- if (l_auth_status = 'APPROVED')...
810
811 d_progress := 50;
812
813 l_return_value := TRUE;
814
815 EXCEPTION
816 WHEN PO_CORE_S.g_early_return_exc THEN
817 NULL;
818 END;
819
820 IF (PO_LOG.d_proc) THEN
821 PO_LOG.proc_return(d_module, l_return_value);
822 PO_LOG.proc_end(d_module);
823 END IF;
824
825 return (l_return_value);
826
827 EXCEPTION
828 WHEN others THEN
829
830 IF auth_status%ISOPEN THEN
831 close auth_status;
832 END IF;
833
834 IF (PO_LOG.d_exc) THEN
835 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
836 END IF;
837
838 return(FALSE);
839
840 END approve_planned_supply;
841
842
843
844
845
846 /* ----------------------------------------------------------------------- */
847 /* */
848 /* Create PO Supply */
849 /* */
850 /* Insert new PO Supply into mtl_supply for Standard or Planned PO */
851 /* Approval, Blanket or Planned Release Approval, Standard or Planned */
852 /* PO Line Approval, Standard or Planned PO Shipment Approval, Blanket */
853 /* or Planned Release Shipment */
854 /* */
855 /* New PO Supply is inserted based on Entity Type */
856 /* */
857 /* Entity Type Action */
858 /* ----------- --------------------------------------------- */
859 /* PO New PO Supply for Standard or Planned PO */
860 /* Approval */
861 /* */
862 /* RELEASE New PO Supply for Blanket or Planned Release */
863 /* Approval */
864 /* */
865 /* PO LINE New PO Supply for Standard or Planned PO Line */
866 /* Approval */
867 /* */
868 /* PO SHIPMENT New PO Supply for Standard or Planned PO */
869 /* Shipment Approval */
870 /* */
871 /* RELEASE New PO Supply for Blanket or Planned Release */
872 /* SHIPMENT Shipment */
873 /* */
874 /* ----------------------------------------------------------------------- */
875
876 -- <Doc Manager Rewrite R12>: create_po_supply had conflicting logic in PO_SUPPLY
877 -- vs. the Pro*C code. The latter is more accurate, so the method has been changed
878 -- to reflect the logic in Pro*C wherever there is a conflict.
879
880 FUNCTION create_po_supply(
881 p_entity_id IN NUMBER
882 , p_entity_type IN VARCHAR2
883 ) RETURN BOOLEAN
884 IS
885
886 l_distid po_distributions.po_distribution_id%TYPE;
887 l_qty po_distributions.quantity_ordered%TYPE;
888 l_out_poqty po_distributions.quantity_ordered%TYPE := 0;
889 l_line_loc_id po_distributions.line_location_id%TYPE;
890
891 l_supply_qty_in_pouom po_distributions.quantity_ordered%TYPE := 0;
892 l_uom po_lines.unit_meas_lookup_code%TYPE;
893 l_supply_qty mtl_supply.quantity%TYPE := 0;
894 l_supply_uom mtl_supply.unit_of_measure%TYPE;
895 l_supply_itemid mtl_supply.item_id%TYPE;
896
897 l_message VARCHAR2(50);
898
899 sql_dist VARCHAR2(800);
900 cur_dist INTEGER;
901 num_dist INTEGER;
902 b_entity_id NUMBER;
903
904 CURSOR supply_lloc(p_line_loc_id NUMBER)
905 IS
906 SELECT SUM(to_org_primary_quantity),
907 to_org_primary_uom,
908 NVL(item_id, -1)
909 FROM mtl_supply
910 WHERE supply_type_code IN ('RECEIVING', 'SHIPMENT')
911 AND po_line_location_id = p_line_loc_id
912 GROUP BY to_org_primary_uom, nvl(item_id, -1);
913
914 l_prev_line_loc_id NUMBER := -9999;
915
916 d_module VARCHAR2(70) := 'po.plsql.PO_SUPPLY.create_po_supply';
917 d_progress NUMBER;
918
919 l_return_value BOOLEAN := FALSE;
920
921 l_released_qty po_distributions.quantity_ordered%TYPE;
922
923 -- <Bug 9342280 : Added for CLM project>
924 l_is_clm_po VARCHAR2(5) := 'N';
925 l_distribution_type VARCHAR2(100);
926 l_matching_basis VARCHAR2(100);
927 l_accrue_on_receipt_flag VARCHAR2(100);
928 l_code_combination_id NUMBER;
929 l_budget_account_id NUMBER;
930 l_partial_funded_flag VARCHAR2(100) := 'N';
931 l_unit_meas_lookup_code VARCHAR2(100);
932 l_funded_value NUMBER;
933 l_quantity_funded NUMBER;
934 l_amount_funded NUMBER;
935 l_quantity_received NUMBER;
936 l_amount_received NUMBER;
937 l_quantity_delivered NUMBER;
938 l_amount_delivered NUMBER;
939 l_quantity_billed NUMBER;
940 l_amount_billed NUMBER;
941 l_quantity_cancelled NUMBER;
942 l_amount_cancelled NUMBER;
943 l_return_status VARCHAR2(100);
944 l_dist_count NUMBER;
945 -- <CLM END>
946
947 BEGIN
948
949 IF (PO_LOG.d_proc) THEN
950 PO_LOG.proc_begin(d_module);
951 PO_LOG.proc_begin(d_module, 'p_entity_id', p_entity_id);
952 PO_LOG.proc_begin(d_module, 'p_entity_type', p_entity_type);
953 END IF;
954
955 d_progress := 10;
956
957 BEGIN
958
959 l_return_value := delete_supply(
960 p_entity_id => p_entity_id
961 , p_entity_type => p_entity_type
962 );
963
964 IF (NOT l_return_value) THEN
965
966 IF (PO_LOG.d_stmt) THEN
967 PO_LOG.stmt(d_module, d_progress, 'delete_supply not successful');
968 END IF;
969
970 RAISE PO_CORE_S.g_early_return_exc;
971
972 END IF;
973
974 d_progress := 20;
975
976 b_entity_id := p_entity_id;
977
978
979 -- Setup Dynamic SQL for Distributions
980 /*Bug 4537860:Hit the _all tables instead of the striped views.*/
981 sql_dist := 'SELECT pd.po_distribution_id, '
982 || 'pd.quantity_ordered - nvl(pd.quantity_delivered, 0) - nvl(pd.quantity_cancelled, 0), '
983 || 'pl.unit_meas_lookup_code, '
984 || 'pd.line_location_id '
985 || 'FROM po_distributions_all pd, po_lines_all pl ';
986
987
988 IF (p_entity_type = 'PO') THEN
989
990 d_progress := 30;
991
992 sql_dist := sql_dist
993 || 'WHERE pd.po_header_id = :b_entity_id '
994 || 'AND pd.po_line_id = pl.po_line_id '
995 || 'AND pd.po_release_id IS NULL '; -- <Doc Manager Rewrite R12>
996
997
998 ELSIF (p_entity_type = 'RELEASE') THEN
999
1000 d_progress := 40;
1001
1002 sql_dist := sql_dist
1003 || 'WHERE pd.po_release_id = :b_entity_id '
1004 || 'AND pd.po_line_id = pl.po_line_id ';
1005
1006
1007 ELSIF (p_entity_type = 'PO LINE') THEN
1008
1009 d_progress := 50;
1010
1011 sql_dist := sql_dist
1012 || 'WHERE pd.po_line_id = :b_entity_id '
1013 || 'AND pd.po_line_id = pl.po_line_id '
1014 || 'AND pd.po_release_id IS NULL '; -- <Doc Manager Rewrite R12>
1015
1016
1017 ELSIF (p_entity_type IN ('PO SHIPMENT', 'RELEASE SHIPMENT')) THEN
1018
1019 d_progress := 60;
1020 sql_dist := sql_dist
1021 || 'WHERE pd.line_location_id = :b_entity_id '
1022 || 'AND pd.po_line_id = pl.po_line_id ';
1023
1024 -- <Bug 9342280 : Added for CLM project>
1025 SELECT COUNT(po_distribution_id)
1026 INTO l_dist_count
1027 FROM po_distributions_all pd
1028 WHERE pd.line_location_id = b_entity_id;
1029 -- <END CLM>
1030
1031 END IF; -- IF p_entity_type = ...
1032
1033 -- <Doc Manager Rewrite R12>: Add order-by clause as in Pro*C
1034 sql_dist := sql_dist || 'ORDER BY pd.line_location_id';
1035
1036 IF (PO_LOG.d_stmt) THEN
1037 PO_LOG.stmt(d_module, d_progress, 'sql_dist', sql_dist);
1038 END IF;
1039
1040 d_progress := 70;
1041
1042 cur_dist := dbms_sql.open_cursor;
1043 dbms_sql.parse(cur_dist, sql_dist, dbms_sql.v7);
1044 dbms_sql.bind_variable(cur_dist, ':b_entity_id', b_entity_id);
1045
1046 dbms_sql.define_column(cur_dist, 1, l_distid);
1047 dbms_sql.define_column(cur_dist, 2, l_qty);
1048 dbms_sql.define_column(cur_dist, 3, l_uom, 25);
1049 dbms_sql.define_column(cur_dist, 4, l_line_loc_id);
1050
1051 num_dist := dbms_sql.execute(cur_dist);
1052
1053 LOOP
1054
1055 d_progress := 80;
1056
1057 IF (dbms_sql.fetch_rows(cur_dist) > 0) THEN
1058
1059 d_progress := 90;
1060
1061 dbms_sql.column_value(cur_dist, 1, l_distid);
1062 dbms_sql.column_value(cur_dist, 2, l_qty);
1063 dbms_sql.column_value(cur_dist, 3, l_uom);
1064 dbms_sql.column_value(cur_dist, 4, l_line_loc_id);
1065
1066
1067 IF (PO_LOG.d_stmt) THEN
1068 PO_LOG.stmt(d_module, d_progress, 'l_distid', l_distid);
1069 PO_LOG.stmt(d_module, d_progress, 'l_qty', l_qty);
1070 PO_LOG.stmt(d_module, d_progress, 'l_uom', l_uom);
1071 PO_LOG.stmt(d_module, d_progress, 'l_line_loc_id', l_line_loc_id);
1072 END IF;
1073
1074 -- <Doc Manager Rewrite R12 Start> : From Pro*C
1075 -- For PO distribution, get the sum of quantity already
1076 -- released against that distribution.
1077
1078 IF (p_entity_type IN ('PO', 'PO LINE', 'PO SHIPMENT')) THEN
1079
1080 d_progress := 100;
1081 /*Bug 4537860:Hit the _all tables instead of the striped views.*/
1082
1083 SELECT NVL(SUM(pod.quantity_ordered - NVL(pod.quantity_delivered, 0)
1084 - NVL(pod.quantity_cancelled, 0)), 0)
1085 INTO l_released_qty
1086 FROM po_distributions_all pod
1087 , po_releases_all por
1088 WHERE pod.source_distribution_id = l_distid
1089 AND pod.po_release_id = por.po_release_id
1090 AND NVL(por.authorization_status, 'IN PROCESS') = 'APPROVED';
1091
1092 ELSE
1093
1094 d_progress := 105;
1095 l_released_qty := 0;
1096
1097 END IF; -- p_entity IN ...
1098
1099 IF (PO_LOG.d_stmt) THEN
1100 PO_LOG.stmt(d_module, d_progress, 'l_released_qty', l_released_qty);
1101 END IF;
1102
1103 -- <Doc Manager Rewrite R12 End>
1104
1105
1106 IF (l_qty < 0) THEN
1107 l_out_poqty := 0;
1108 ELSE
1109 l_out_poqty := l_qty - l_released_qty; -- <Doc Manager Rewrite R12>
1110 END IF;
1111
1112 d_progress := 100;
1113
1114 IF (l_line_loc_id <> l_prev_line_loc_id) THEN
1115
1116 -- <Bug 9342280 : Added for CLM project>
1117 IF (p_entity_type = 'PO SHIPMENT' AND l_dist_count > 1) THEN
1118
1119 l_is_clm_po := 'N';
1120 l_partial_funded_flag := 'N';
1121
1122 l_is_clm_po := po_clm_intg_grp.is_clm_po(p_po_header_id => NULL,
1123 p_po_line_id => NULL,
1124 p_po_line_location_id => l_line_loc_id,
1125 p_po_distribution_id => NULL);
1126
1127 IF l_is_clm_po = 'Y' THEN
1128 po_clm_intg_grp.get_funding_info(p_po_header_id => NULL,
1129 p_po_line_id => NULL,
1130 p_line_location_id => l_line_loc_id,
1131 p_po_distribution_id => NULL,
1132 x_distribution_type => l_distribution_type,
1133 x_matching_basis => l_matching_basis,
1134 x_accrue_on_receipt_flag => l_accrue_on_receipt_flag,
1135 x_code_combination_id => l_code_combination_id,
1136 x_budget_account_id => l_budget_account_id,
1137 x_partial_funded_flag => l_partial_funded_flag,
1138 x_unit_meas_lookup_code => l_unit_meas_lookup_code,
1139 x_funded_value => l_funded_value,
1140 x_quantity_funded => l_quantity_funded,
1141 x_amount_funded => l_amount_funded,
1142 x_quantity_received => l_quantity_received,
1143 x_amount_received => l_amount_received,
1144 x_quantity_delivered => l_quantity_delivered,
1145 x_amount_delivered => l_amount_delivered,
1146 x_quantity_billed => l_quantity_billed,
1147 x_amount_billed => l_amount_billed,
1148 x_quantity_cancelled => l_quantity_cancelled,
1149 x_amount_cancelled => l_amount_cancelled,
1150 x_return_status => l_return_status);
1151
1152 END IF;
1153
1154 END IF;
1155
1156 -- if it's clm po, but not partial funded, will use the original logic
1157 IF l_is_clm_po = 'N' OR l_partial_funded_flag = 'N' THEN
1158
1159 -- <END CLM>
1160
1161
1162 l_supply_qty := 0;
1163 l_supply_qty_in_pouom := 0;
1164
1165 d_progress := 110;
1166
1167 OPEN supply_lloc(l_line_loc_id); -- Bug#4962625
1168 LOOP
1169
1170 FETCH supply_lloc INTO l_supply_qty, l_supply_uom, l_supply_itemid;
1171 EXIT WHEN supply_lloc%NOTFOUND;
1172
1173 IF (PO_LOG.d_stmt) THEN
1174 PO_LOG.stmt(d_module, d_progress, 'l_supply_qty', l_supply_qty);
1175 PO_LOG.stmt(d_module, d_progress, 'l_supply_uom', l_supply_uom);
1176 PO_LOG.stmt(d_module, d_progress, 'l_supply_itemid', l_supply_itemid);
1177 END IF;
1178
1179 d_progress := 120;
1180
1181 IF (l_supply_qty > 0) THEN
1182
1183 l_supply_qty_in_pouom := l_supply_qty_in_pouom +
1184 INV_CONVERT.INV_UM_CONVERT(
1185 item_id => l_supply_itemid
1186 , precision => 5
1187 , from_quantity => l_supply_qty
1188 , from_unit => NULL
1189 , to_unit => NULL
1190 , from_name => l_supply_uom
1191 , to_name => l_uom
1192 );
1193
1194 END IF; -- IF (l_supply_qty > 0)
1195
1196 END LOOP; -- supply_lloc cursor
1197
1198 IF supply_lloc%ISOPEN THEN
1199 close supply_lloc;
1200 END IF;
1201
1202 d_progress := 130;
1203
1204 IF (PO_LOG.d_stmt) THEN
1205 PO_LOG.stmt(d_module, d_progress, 'l_supply_qty_in_pouom', l_supply_qty_in_pouom);
1206 END IF;
1207
1208 END IF; -- <Bug 9342280 : Added for CLM project>
1209
1210 l_prev_line_loc_id := l_line_loc_id;
1211
1212 END IF; -- IF (l_line_loc_id <> l_prev_line_loc_id)
1213
1214 -- <Bug 9342280 : Added for CLM project>
1215 /* this is for std receipt against multi dists for CLM POs
1216 * the PO supply qty should be consume against specific dist_id, not in FIFO manner for line_loc_id
1217 */
1218 IF (p_entity_type = 'PO SHIPMENT' AND l_dist_count > 1) AND
1219 (l_is_clm_po = 'Y' AND l_partial_funded_flag = 'Y') THEN
1220
1221 l_supply_qty := 0;
1222 l_supply_qty_in_pouom := 0;
1223
1224 BEGIN
1225 SELECT SUM(to_org_primary_quantity),
1226 to_org_primary_uom,
1227 NVL(item_id, -1)
1228 INTO l_supply_qty, l_supply_uom, l_supply_itemid
1229 FROM mtl_supply
1230 WHERE supply_type_code IN ('RECEIVING', 'SHIPMENT')
1231 AND po_distribution_id = l_distid
1232 GROUP BY to_org_primary_uom, NVL(item_id, -1);
1233 EXCEPTION
1234 WHEN OTHERS THEN
1235 l_supply_qty := 0;
1236 END;
1237
1238 IF l_supply_qty > 0 THEN
1239 l_supply_qty_in_pouom := inv_convert.inv_um_convert(item_id => l_supply_itemid,
1240 precision => 5,
1241 from_quantity => l_supply_qty,
1242 from_unit => NULL,
1243 to_unit => NULL,
1244 from_name => l_supply_uom,
1245 to_name => l_uom);
1246 END IF;
1247 END IF;
1248 -- <END CLM>
1249
1250 IF (l_out_poqty >= l_supply_qty_in_pouom) THEN
1251 l_out_poqty := l_out_poqty - l_supply_qty_in_pouom;
1252 l_supply_qty_in_pouom := 0;
1253 ELSE
1254 l_out_poqty := 0;
1255 l_supply_qty_in_pouom := l_supply_qty_in_pouom - l_out_poqty;
1256 END IF;
1257
1258 IF l_out_poqty < 0 THEN
1259 l_out_poqty := 0;
1260 END IF;
1261
1262 d_progress := 140;
1263
1264 IF (PO_LOG.d_stmt) THEN
1265 PO_LOG.stmt(d_module, d_progress, 'l_supply_qty_in_pouom', l_supply_qty_in_pouom);
1266 PO_LOG.stmt(d_module, d_progress, 'l_out_poqty', l_out_poqty);
1267 END IF;
1268
1269 -- Create PO Supply
1270 /*Bug 4537860:Hit the _all tables instead of the striped views.*/
1271
1272 INSERT INTO mtl_supply(supply_type_code,
1273 supply_source_id,
1274 last_updated_by,
1275 last_update_date,
1276 last_update_login,
1277 created_by,
1278 creation_date,
1279 po_header_id,
1280 po_line_id,
1281 po_line_location_id,
1282 po_distribution_id,
1283 po_release_id, -- <Doc Manager Rewrite R12>
1284 item_id,
1285 item_revision,
1286 quantity,
1287 unit_of_measure,
1288 receipt_date,
1289 need_by_date,
1290 destination_type_code,
1291 location_id,
1292 to_organization_id,
1293 to_subinventory,
1294 change_flag)
1295 SELECT 'PO',
1296 pd.po_distribution_id,
1297 pd.last_updated_by,
1298 pd.last_update_date,
1299 pd.last_update_login,
1300 pd.created_by,
1301 pd.creation_date,
1302 pd.po_header_id,
1303 pd.po_line_id,
1304 pd.line_location_id,
1305 pd.po_distribution_id,
1306 pd.po_release_id,
1307 pl.item_id,
1308 pl.item_revision,
1309 l_out_poqty,
1310 pl.unit_meas_lookup_code,
1311 nvl(pll.promised_date, pll.need_by_date),
1312 nvl(pll.promised_date, pll.need_by_date), -- bug 4300150
1313 pd.destination_type_code,
1314 pd.deliver_to_location_id,
1315 pd.destination_organization_id,
1316 pd.destination_subinventory,
1317 'Y'
1318 FROM po_distributions_all pd,
1319 po_line_locations_all pll,
1320 po_lines_all pl
1321 WHERE pd.po_distribution_id = l_distid
1322 AND pll.line_location_id = pd.line_location_id
1323 AND pl.item_id IS NOT NULL -- <Complex Work R12>
1324 AND pl.po_line_id = pd.po_line_id
1325 AND nvl(pll.closed_code, 'OPEN') not in ('FINALLY CLOSED', 'CLOSED', 'CLOSED FOR RECEIVING')
1326 AND nvl(pll.cancel_flag, 'N') = 'N'
1327 AND nvl(pll.approved_flag, 'Y') = 'Y'
1328 AND pll.quantity IS NOT NULL -- <Doc Manager Rewrite R12>
1329 AND not exists
1330 (
1331 SELECT 'Supply Exists'
1332 FROM mtl_supply ms1
1333 WHERE ms1.supply_type_code = 'PO'
1334 AND ms1.supply_source_id = pd.po_distribution_id
1335 );
1336
1337
1338 -- <Doc Manager Rewrite R12>: After analysis, no rows is OK, not error.
1339 -- This is to handle services lines.
1340
1341 ELSE
1342
1343 -- no rows in distributions cursor
1344 EXIT;
1345
1346 END IF; -- IF (dbms_sql.fetch_rows(cur_dist) > 0)
1347
1348 END LOOP; -- dynamic dists cursor
1349
1350 l_return_value := TRUE;
1351
1352 EXCEPTION
1353 WHEN PO_CORE_S.g_early_return_exc THEN
1354 NULL;
1355 END;
1356
1357 IF supply_lloc%ISOPEN THEN
1358 close supply_lloc;
1359 END IF;
1360
1361 IF (dbms_sql.is_open(cur_dist)) THEN
1362 dbms_sql.close_cursor(cur_dist);
1363 END IF;
1364
1365 IF (PO_LOG.d_proc) THEN
1366 PO_LOG.proc_return(d_module, l_return_value);
1367 PO_LOG.proc_end(d_module);
1368 END IF;
1369
1370 return (l_return_value);
1371
1372 EXCEPTION
1373
1374 WHEN others THEN
1375
1376 IF supply_lloc%ISOPEN THEN
1377 close supply_lloc;
1378 END IF;
1379
1380 IF (dbms_sql.is_open(cur_dist)) THEN
1381 dbms_sql.close_cursor(cur_dist);
1382 END IF;
1383
1384 IF (PO_LOG.d_exc) THEN
1385 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
1386 END IF;
1387
1388 return(FALSE);
1389
1390 END create_po_supply;
1391
1392
1393 /* ----------------------------------------------------------------------- */
1394 /* */
1395 /* Delete Supply for PO Header or PO Release */
1396 /* */
1397 /* New PO Supply is inserted based on Entity Type */
1398 /* */
1399 /* Entity Type Action */
1400 /* ----------- ----------------------------------------- */
1401 /* PO Remove PO Supply for PO Header */
1402 /* */
1403 /* RELEASE Remove PO Supply for PO Release */
1404 /* */
1405 /* PO LINE Remove PO Supply for PO Line */
1406 /* */
1407 /* PO SHIPMENT Remove PO Supply for PO Shipment */
1408 /* */
1409 /* RELEASE SHIPMENT Remove PO Supply for Release Shipment */
1410 /* */
1411 /* ----------------------------------------------------------------------- */
1412
1413
1414 FUNCTION delete_supply(
1415 p_entity_id IN NUMBER
1416 , p_entity_type IN VARCHAR2
1417 ) RETURN BOOLEAN
1418 IS
1419
1420 d_module VARCHAR2(70) := 'po.plsql.PO_SUPPLY.delete_supply';
1421 d_progress NUMBER;
1422
1423 BEGIN
1424
1425 IF (PO_LOG.d_proc) THEN
1426 PO_LOG.proc_begin(d_module);
1427 PO_LOG.proc_begin(d_module, 'p_entity_id', p_entity_id);
1428 PO_LOG.proc_begin(d_module, 'p_entity_type', p_entity_type);
1429 END IF;
1430
1431 d_progress := 10;
1432
1433 IF (p_entity_type = 'PO') THEN
1434
1435 DELETE FROM mtl_supply
1436 WHERE supply_type_code = 'PO'
1437 AND po_header_id = p_entity_id
1438 AND po_release_id IS NULL; -- <Doc Manager Rewrite R12>: From Pro*C
1439
1440 ELSIF (p_entity_type = 'RELEASE') THEN
1441
1442 DELETE FROM mtl_supply
1443 WHERE supply_type_code = 'PO'
1444 AND po_release_id = p_entity_id;
1445
1446 ELSIF (p_entity_type = 'PO LINE') THEN
1447
1448 DELETE FROM mtl_supply
1449 WHERE supply_type_code = 'PO'
1450 AND po_line_id = p_entity_id
1451 AND po_release_id IS NULL; -- <Doc Manager Rewrite R12>: From Pro*C
1452
1453 ELSIF (p_entity_type in ('PO SHIPMENT', 'RELEASE SHIPMENT')) THEN
1454
1455 DELETE FROM mtl_supply
1456 WHERE supply_type_code = 'PO'
1457 AND po_line_location_id = p_entity_id;
1458
1459 END IF;
1460
1461 d_progress := 30;
1462
1463 IF (PO_LOG.d_stmt) THEN
1464 PO_LOG.stmt(d_module, d_progress, 'Deleted ' || SQL%ROWCOUNT || ' records');
1465 END IF;
1466
1467 IF (PO_LOG.d_proc) THEN
1468 PO_LOG.proc_return(d_module, TRUE);
1469 PO_LOG.proc_end(d_module);
1470 END IF;
1471
1472 return(TRUE);
1473
1474 EXCEPTION
1475
1476 WHEN others THEN
1477 IF (PO_LOG.d_exc) THEN
1478 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
1479 END IF;
1480
1481 return(FALSE);
1482
1483 END delete_supply;
1484
1485
1486
1487
1488 /* ----------------------------------------------------------------------- */
1489 /* */
1490 /* Update Supply Quantity to 0 for PO Line, PO Shipment or Release */
1491 /* Shipment */
1492 /* */
1493 /* PO Supply is Updated based on the Entity Type */
1494 /* */
1495 /* Entity Type Action */
1496 /* ----------- --------------------------------------------- */
1497 /* PO LINE Update Supply Quantity for PO Line */
1498 /* */
1499 /* PO SHIPMENT Update Supply Quantity for PO Shipment */
1500 /* */
1501 /* RELEASE Update Supply Quantity for Release Shipment */
1502 /* SHIPMENT */
1503 /* */
1504 /* ----------------------------------------------------------------------- */
1505
1506
1507 FUNCTION update_supply(
1508 p_entity_id IN NUMBER
1509 , p_entity_type IN VARCHAR2
1510 , p_shipid IN NUMBER DEFAULT 0
1511 ) RETURN BOOLEAN
1512 IS
1513
1514 d_module VARCHAR2(70) := 'po.plsql.PO_SUPPLY.update_supply';
1515 d_progress NUMBER;
1516
1517 BEGIN
1518
1519 IF (PO_LOG.d_proc) THEN
1520 PO_LOG.proc_begin(d_module);
1521 PO_LOG.proc_begin(d_module, 'p_entity_id', p_entity_id);
1522 PO_LOG.proc_begin(d_module, 'p_entity_type', p_entity_type);
1523 PO_LOG.proc_begin(d_module, 'p_shipid', p_shipid);
1524 END IF;
1525
1526 d_progress := 10;
1527
1528 IF (p_entity_type = 'PO LINE') THEN
1529
1530 UPDATE mtl_supply
1531 SET quantity = 0
1532 , change_flag = 'Y'
1533 WHERE supply_type_code = 'PO'
1534 AND po_line_id = p_entity_id
1535 AND po_release_id IS NULL; -- <Doc Manager Rewrite R12>
1536
1537 ELSIF (p_entity_type = 'PO SHIPMENT') THEN
1538
1539 UPDATE mtl_supply
1540 SET quantity = 0
1541 , change_flag = 'Y'
1542 WHERE supply_type_code = 'PO'
1543 AND po_line_location_id = p_entity_id;
1544
1545 ELSIF (p_entity_type = 'RELEASE SHIPMENT') THEN
1546
1547 UPDATE mtl_supply
1548 SET quantity = 0
1549 , change_flag = 'Y'
1550 WHERE supply_type_code = 'PO'
1551 AND po_release_id = p_entity_id
1552 AND po_line_location_id = p_shipid;
1553
1554 END IF;
1555
1556 d_progress := 30;
1557
1558 IF (PO_LOG.d_stmt) THEN
1559 PO_LOG.stmt(d_module, d_progress, 'Updated ' || SQL%ROWCOUNT || ' records');
1560 END IF;
1561
1562 IF (PO_LOG.d_proc) THEN
1563 PO_LOG.proc_return(d_module, TRUE);
1564 PO_LOG.proc_end(d_module);
1565 END IF;
1566
1567 return(TRUE);
1568
1569 EXCEPTION
1570
1571 WHEN others THEN
1572 IF (PO_LOG.d_exc) THEN
1573 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
1574 END IF;
1575
1576 return(FALSE);
1577
1578 END update_supply;
1579
1580
1581
1582 /* ----------------------------------------------------------------------- */
1583 /* */
1584 /* Cancel Supply for PO Header, Line or Shipment */
1585 /* */
1586 /* PO Supply is Cancelled based on Entity Type */
1587 /* */
1588 /* Entity Type Action */
1589 /* ----------- --------------------------------------- */
1590 /* PO Cancel PO Supply for PO Header */
1591 /* */
1592 /* PO LINE Cancel PO Supply for PO Line */
1593 /* */
1594 /* PO SHIPMENT Cancel PO Supply for PO Shipment */
1595 /* */
1596 /* RELEASE Cancel PO Supply for PO Release */
1597 /* */
1598 /* RELEASE SHIPMENT Cancel PO Supply for Release Shipment */
1599 /* */
1600 /* ----------------------------------------------------------------------- */
1601
1602 FUNCTION cancel_supply(
1603 p_entity_id IN NUMBER
1604 , p_entity_type IN VARCHAR2
1605 , p_shipid IN NUMBER
1606 ) RETURN BOOLEAN
1607 IS
1608
1609 d_module VARCHAR2(70) := 'po.plsql.PO_SUPPLY.cancel_supply';
1610 d_progress NUMBER;
1611
1612 l_return_value BOOLEAN := FALSE;
1613
1614 BEGIN
1615
1616 IF (PO_LOG.d_proc) THEN
1617 PO_LOG.proc_begin(d_module);
1618 PO_LOG.proc_begin(d_module, 'p_entity_id', p_entity_id);
1619 PO_LOG.proc_begin(d_module, 'p_entity_type', p_entity_type);
1620 PO_LOG.proc_begin(d_module, 'p_shipid', p_shipid);
1621 END IF;
1622
1623 d_progress := 10;
1624
1625 -- Requisition Line Supply is created in Cancel PO Routine. We just remove
1626 -- the existing PO Supply
1627
1628 IF (p_entity_type = 'PO') THEN
1629
1630 l_return_value := delete_supply(
1631 p_entity_id => p_entity_id
1632 , p_entity_type => 'PO'
1633 );
1634
1635 ELSIF (p_entity_type = 'PO LINE') THEN
1636
1637 l_return_value := update_supply(
1638 p_entity_id => p_entity_id
1639 , p_entity_type => 'PO LINE'
1640 );
1641
1642 ELSIF (p_entity_type = 'PO SHIPMENT') THEN
1643
1644 l_return_value := update_supply(
1645 p_entity_id => p_entity_id
1646 , p_entity_type => 'PO SHIPMENT'
1647 );
1648
1649 ELSIF (p_entity_type = 'RELEASE') THEN
1650
1651 l_return_value := delete_supply(
1652 p_entity_id => p_entity_id
1653 , p_entity_type => 'RELEASE'
1654 );
1655
1656 ELSIF (p_entity_type = 'RELEASE SHIPMENT') THEN
1657
1658 l_return_value := update_supply(
1659 p_entity_id => p_entity_id
1660 , p_entity_type => 'RELEASE SHIPMENT'
1661 , p_shipid => p_shipid
1662 );
1663
1664 END IF;
1665
1666 IF (PO_LOG.d_proc) THEN
1667 PO_LOG.proc_return(d_module, l_return_value);
1668 PO_LOG.proc_end(d_module);
1669 END IF;
1670
1671 return (l_return_value);
1672
1673 EXCEPTION
1674
1675 WHEN others THEN
1676 IF (PO_LOG.d_exc) THEN
1677 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
1678 END IF;
1679
1680 return(FALSE);
1681
1682 END cancel_supply;
1683
1684
1685
1686 /* ----------------------------------------------------------------------- */
1687 /* */
1688 /* Cancel Planned Release or Planned Shipment */
1689 /* */
1690 /* Update mtl_supply for Cancel Planned Release or Cancel Planned */
1691 /* Release Shipment Action */
1692 /* */
1693 /* Cancellation of Planned Release and Planned Shipment is based on */
1694 /* Entity Type */
1695 /* */
1696 /* Entity Type Action */
1697 /* ----------- ------------------------------------------ */
1698 /* RELEASE Cancel Planned Release */
1699 /* */
1700 /* RELEASE SHIPMENT Cancel Planned Release Shipment */
1701 /* */
1702 /* ----------------------------------------------------------------------- */
1703
1704
1705 FUNCTION cancel_planned(
1706 p_entity_id IN NUMBER
1707 , p_entity_type IN VARCHAR2
1708 , p_shipid IN NUMBER DEFAULT 0
1709 , p_recreate_flag IN BOOLEAN
1710 ) RETURN BOOLEAN
1711 IS
1712
1713 l_supply_flag BOOLEAN := TRUE;
1714
1715 l_headid po_releases.po_header_id%TYPE;
1716
1717 /*Bug 4537860:Hit the _all tables instead of the striped views.*/
1718
1719 CURSOR rel(release_id NUMBER)
1720 IS
1721 SELECT por.po_header_id
1722 FROM po_releases_all por
1723 WHERE por.po_release_id = release_id;
1724
1725 d_module VARCHAR2(70) := 'po.plsql.PO_SUPPLY.cancel_planned';
1726 d_progress NUMBER;
1727
1728 l_return_value BOOLEAN := FALSE;
1729
1730 BEGIN
1731
1732 IF (PO_LOG.d_proc) THEN
1733 PO_LOG.proc_begin(d_module);
1734 PO_LOG.proc_begin(d_module, 'p_entity_id', p_entity_id);
1735 PO_LOG.proc_begin(d_module, 'p_entity_type', p_entity_type);
1736 PO_LOG.proc_begin(d_module, 'p_shipid', p_shipid);
1737 PO_LOG.proc_begin(d_module, 'p_recreate_flag', p_recreate_flag);
1738 END IF;
1739
1740 d_progress := 10;
1741
1742 BEGIN
1743
1744 -- Remove Planned PO Supply
1745
1746 IF (p_entity_type = 'RELEASE') THEN
1747
1748 d_progress := 20;
1749
1750 l_return_value := delete_supply(
1751 p_entity_id => p_entity_id
1752 , p_entity_type => 'RELEASE'
1753 );
1754
1755 IF (NOT l_return_value) THEN
1756 IF (PO_LOG.d_stmt) THEN
1757 PO_LOG.stmt(d_module, d_progress, 'delete_supply not successful');
1758 END IF;
1759
1760 RAISE PO_CORE_S.g_early_return_exc;
1761 END IF;
1762
1763
1764 IF (p_recreate_flag) THEN
1765
1766 d_progress := 30;
1767
1768 -- Add to existing Planned PO Supply
1769
1770 l_return_value := update_planned_po(
1771 p_docid => p_entity_id
1772 , p_entity_type => 'ADD PLANNED'
1773 , p_supply_flag => l_supply_flag
1774 );
1775
1776 IF (NOT l_return_value) THEN
1777 IF (PO_LOG.d_stmt) THEN
1778 PO_LOG.stmt(d_module, d_progress, 'update_planned_po not successful');
1779 END IF;
1780
1781 RAISE PO_CORE_S.g_early_return_exc;
1782 END IF;
1783
1784 -- Insert Planned PO Supply if it does not exist
1785
1786 IF (NOT l_supply_flag) THEN
1787
1788 d_progress := 40;
1789
1790 OPEN rel(p_entity_id);
1791 FETCH rel INTO l_headid;
1792
1793 d_progress := 50;
1794
1795 IF (rel%NOTFOUND) THEN
1796
1797 IF (PO_LOG.d_stmt) THEN
1798 PO_LOG.stmt(d_module, d_progress, 'no rows in release cursor');
1799 END IF;
1800
1801 l_return_value := TRUE;
1802 RAISE PO_CORE_S.g_early_return_exc;
1803 END IF;
1804
1805 CLOSE rel;
1806
1807 d_progress := 60;
1808
1809 l_return_value := create_po_supply(
1810 p_entity_id => p_entity_id
1811 , p_entity_type => 'PO'
1812 );
1813
1814 END IF; -- if (NOT l_supply_flag)
1815
1816 END IF; -- if (p_recreate_flag)
1817
1818 ELSIF (p_entity_type = 'RELEASE SHIPMENT') THEN
1819
1820 d_progress := 70;
1821
1822 l_return_value := update_supply(
1823 p_entity_id => p_entity_id
1824 , p_entity_type => 'RELEASE SHIPMENT'
1825 , p_shipid => p_shipid
1826 );
1827
1828 IF (NOT l_return_value) THEN
1829 IF (PO_LOG.d_stmt) THEN
1830 PO_LOG.stmt(d_module, d_progress, 'update_supply not successful');
1831 END IF;
1832
1833 RAISE PO_CORE_S.g_early_return_exc;
1834 END IF;
1835
1836
1837 IF (p_recreate_flag) THEN
1838
1839 d_progress := 80;
1840
1841 -- Add to existing Planned PO Supply
1842
1843 l_return_value := update_planned_po(
1844 p_docid => p_entity_id
1845 , p_shipid => p_shipid
1846 , p_entity_type => 'UPDATE PLANNED'
1847 , p_supply_flag => l_supply_flag
1848 );
1849
1850 IF (NOT l_return_value) THEN
1851 IF (PO_LOG.d_stmt) THEN
1852 PO_LOG.stmt(d_module, d_progress, 'update_planned_po not successful');
1853 END IF;
1854
1855 RAISE PO_CORE_S.g_early_return_exc;
1856 END IF;
1857
1858
1859 -- Insert Planned PO Supply if it does not exist
1860
1861 IF (NOT l_supply_flag) THEN
1862
1863 d_progress := 90;
1864
1865 l_return_value := create_po_supply(
1866 p_entity_id => p_shipid
1867 , p_entity_type => 'PO SHIPMENT'
1868 );
1869
1870 END IF; -- if (NOT l_supply_flag)
1871
1872 END IF; -- if (p_recreate_flag)
1873
1874 END IF; -- if (p_entity_type = ...)
1875
1876 EXCEPTION
1877 WHEN PO_CORE_S.g_early_return_exc THEN
1878 NULL;
1879 END;
1880
1881 IF (rel%ISOPEN) THEN
1882 CLOSE rel;
1883 END IF;
1884
1885 IF (PO_LOG.d_proc) THEN
1886 PO_LOG.proc_return(d_module, l_return_value);
1887 PO_LOG.proc_end(d_module);
1888 END IF;
1889
1890 return (l_return_value);
1891
1892 EXCEPTION
1893 WHEN others THEN
1894 IF (PO_LOG.d_exc) THEN
1895 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
1896 END IF;
1897
1898 IF (rel%ISOPEN) THEN
1899 CLOSE rel;
1900 END IF;
1901
1902 return(FALSE);
1903
1904 END cancel_planned;
1905
1906
1907
1908 /* ----------------------------------------------------------------------- */
1909 /* */
1910 /* Maintain mtl_supply */
1911 /* */
1912 /* ----------------------------------------------------------------------- */
1913
1914 FUNCTION maintain_mtl_supply RETURN BOOLEAN IS
1915
1916 l_uom mtl_system_items.primary_unit_of_measure%TYPE;
1917 l_lead_time mtl_system_items.postprocessing_lead_time%TYPE;
1918 l_pri_qty mtl_supply.to_org_primary_quantity%TYPE;
1919 l_exclude_from_planning mtl_supply.exclude_from_planning%TYPE; --<CLM INTG - PLANNING>
1920
1921 -- <Doc Manager Rewrite R12>: Brought in from Pro*C to cursor sup2:
1922 -- 1. index hint
1923 -- 2. order by
1924
1925 /* Bug# 7368176
1926 * Added an extra FOR UPDATE clause in the below cursor to avoid the
1927 * deadlock scenario.
1928 */
1929 /*
1930 <CLM INTG - PLANNING>
1931 Adding po_header_id, req_header_id and exclude_from_planning for clm integration with planning
1932 */
1933 CURSOR sup2
1934 IS
1935 SELECT /*+ index(mtl_supply MTL_SUPPLY_N10) */
1936 quantity
1937 , unit_of_measure
1938 , nvl(item_id, -1) item_id
1939 , from_organization_id
1940 , to_organization_id
1941 , receipt_date
1942 , po_header_id
1943 , req_header_id
1944 , exclude_from_planning
1945 , rowid
1946 FROM mtl_supply
1947 WHERE change_flag = 'Y'
1948 ORDER BY DECODE (supply_type_code,
1949 'REQ', 1,
1950 'PO', 2,
1951 'SHIPMENT', 3,
1952 'RECEIVING', 4,
1953 5), QUANTITY,
1954 SUPPLY_TYPE_CODE,
1955 SUPPLY_SOURCE_ID,
1956 SHIPMENT_LINE_ID,
1957 PO_LINE_ID,
1958 PO_DISTRIBUTION_ID,
1959 REQ_LINE_ID,
1960 RCV_TRANSACTION_ID,
1961 ITEM_ID,
1962 TO_ORGANIZATION_ID
1963 FOR UPDATE;
1964
1965 CURSOR uom(from_uom VARCHAR2)
1966 IS
1967 SELECT muom.unit_of_measure
1968 , NULL
1969 FROM mtl_units_of_measure muom
1970 , mtl_units_of_measure tuom
1971 WHERE tuom.unit_of_measure = from_uom
1972 AND tuom.uom_class = muom.uom_class
1973 AND muom.base_uom_flag = 'Y';
1974
1975 CURSOR uom_itemid(item_id NUMBER, to_org NUMBER)
1976 IS
1977 SELECT primary_unit_of_measure
1978 , postprocessing_lead_time
1979 FROM mtl_system_items
1980 WHERE inventory_item_id = item_id
1981 AND organization_id = to_org;
1982
1983 d_module VARCHAR2(70) := 'po.plsql.PO_SUPPLY.maintain_mtl_supply';
1984 d_progress NUMBER;
1985
1986 BEGIN
1987
1988 IF (PO_LOG.d_proc) THEN
1989 PO_LOG.proc_begin(d_module);
1990 END IF;
1991
1992 d_progress := 10;
1993
1994 FOR c_sup2 IN sup2
1995 LOOP
1996
1997 IF (c_sup2.quantity = 0) THEN
1998
1999 d_progress := 20;
2000
2001 DELETE FROM mtl_supply
2002 WHERE rowid = c_sup2.rowid;
2003
2004 d_progress := 25;
2005 IF (PO_LOG.d_stmt) THEN
2006 PO_LOG.stmt(d_module, d_progress, 'Deleted ' || SQL%ROWCOUNT || ' rows');
2007 END IF;
2008
2009 ELSE
2010
2011 IF (c_sup2.item_id = -1) THEN
2012
2013 -- one time item
2014
2015 d_progress := 30;
2016
2017 OPEN uom(c_sup2.unit_of_measure);
2018 FETCH uom INTO l_uom, l_lead_time;
2019 CLOSE uom;
2020
2021 ELSE
2022
2023 d_progress := 40;
2024
2025 OPEN uom_itemid(c_sup2.item_id, c_sup2.to_organization_id);
2026 FETCH uom_itemid INTO l_uom, l_lead_time;
2027 CLOSE uom_itemid;
2028
2029 END IF; -- if (c_sup2.item_id = -1)
2030
2031 d_progress := 50;
2032
2033 IF (PO_LOG.d_stmt) THEN
2034 PO_LOG.stmt(d_module, d_progress, 'l_uom', l_uom);
2035 PO_LOG.stmt(d_module, d_progress, 'l_lead_time', l_lead_time);
2036 PO_LOG.stmt(d_module, d_progress, 'c_sup2.item_id', c_sup2.item_id);
2037 PO_LOG.stmt(d_module, d_progress, 'c_sup2.quantity', c_sup2.quantity);
2038 PO_LOG.stmt(d_module, d_progress, 'c_sup2.unit_of_measure', c_sup2.unit_of_measure);
2039 END IF;
2040
2041 l_pri_qty := INV_CONVERT.INV_UM_CONVERT(
2042 item_id => c_sup2.item_id
2043 , precision => 5
2044 , from_quantity => c_sup2.quantity
2045 , from_unit => NULL
2046 , to_unit => NULL
2047 , from_name => c_sup2.unit_of_measure
2048 , to_name => l_uom
2049 );
2050
2051 d_progress := 60;
2052
2053 IF (PO_LOG.d_stmt) THEN
2054 PO_LOG.stmt(d_module, d_progress, 'l_pri_qty', l_pri_qty);
2055 END IF;
2056
2057 --<CLM INTG - PLANNING>
2058 DECLARE
2059 l_is_clm_document VARCHAR2(1);
2060 BEGIN
2061 l_exclude_from_planning := c_sup2.exclude_from_planning;
2062 --#1:If its already marked as Excluded From Planning Do not Change it
2063 IF l_exclude_from_planning IS NULL
2064 OR l_exclude_from_planning <> 'Y' THEN
2065 d_progress := 62;
2066 IF (PO_LOG.d_stmt) THEN
2067 PO_LOG.stmt(d_module, d_progress, 'Check: Excluded From Planning');
2068 END IF;
2069 --#2:Should perform this Check only when CLM is installed
2070 IF PO_CLM_INTG_GRP.IS_CLM_INSTALLED = 'Y' THEN
2071 d_progress := 64;
2072 IF (PO_LOG.d_stmt) THEN
2073 PO_LOG.stmt(d_module, d_progress, 'CLM is installed.');
2074 END IF;
2075 IF c_sup2.po_header_id IS NOT NULL THEN
2076 l_is_clm_document := PO_CLM_INTG_GRP.IS_CLM_DOCUMENT(p_doc_type => 'PO',p_document_id => c_sup2.po_header_id);
2077 ELSE
2078 IF c_sup2.req_header_id IS NOT NULL THEN
2079 l_is_clm_document := PO_CLM_INTG_GRP.IS_CLM_DOCUMENT(p_doc_type => 'REQUISITION',p_document_id => c_sup2.req_header_id);
2080 END IF;
2081 --#3: When Both REQ Header Id and PO Header Id or Null, Need not modify anything
2082 END IF;
2083 d_progress := 66;
2084 IF (PO_LOG.d_stmt) THEN
2085 PO_LOG.stmt(d_module, d_progress, ' IS CLM document : '
2086 ||l_is_clm_document );
2087 END IF;
2088 --#4: If its a CLM Document, then Exclude it From Planning, else mark it as N
2089 IF l_is_clm_document IS NOT NULL THEN
2090 l_exclude_from_planning := l_is_clm_document;
2091 END IF;
2092 END IF;
2093 END IF;
2094 EXCEPTION
2095 WHEN OTHERS THEN
2096 NULL;
2097 END;
2098 --<CLM INTG - PLANNING>
2099 /* Bug 9611148: For non-CLM documents, exclude_from_planning should be NULL, not N. */
2100 UPDATE mtl_supply
2101 SET to_org_primary_quantity = l_pri_qty
2102 , to_org_primary_uom = l_uom
2103 , change_flag = null
2104 , change_type = null
2105 , expected_delivery_date =
2106 DECODE(c_sup2.item_id, -1, to_date(NULL),
2107 c_sup2.receipt_date + NVL(l_lead_time, 0)),
2108 exclude_from_planning = DECODE(l_exclude_From_Planning,'Y','Y','N',NULL,NULL) --<CLM INTG - PLANNING>
2109 WHERE rowid = c_sup2.rowid;
2110
2111 d_progress := 70;
2112 IF (PO_LOG.d_stmt) THEN
2113 PO_LOG.stmt(d_module, d_progress, 'Updated ' || SQL%ROWCOUNT || ' rows');
2114 END IF;
2115
2116 END IF; -- if (c_sup2.quantity = 0)
2117
2118 END LOOP;
2119
2120 IF (PO_LOG.d_proc) THEN
2121 PO_LOG.proc_return(d_module, TRUE);
2122 PO_LOG.proc_end(d_module);
2123 END IF;
2124
2125 return(TRUE);
2126
2127 EXCEPTION
2128
2129 WHEN others THEN
2130
2131 IF (PO_LOG.d_exc) THEN
2132 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
2133 END IF;
2134
2135 IF uom%ISOPEN THEN
2136 close uom;
2137 END IF;
2138
2139 IF uom_itemid%ISOPEN THEN
2140 close uom_itemid;
2141 END IF;
2142
2143 return(FALSE);
2144
2145 END maintain_mtl_supply;
2146
2147
2148 /* ----------------------------------------------------------------------- */
2149
2150 -- Approve Requisition
2151
2152 -- Create Requisition Supply for an Approve Requisition Action
2153
2154 /* ----------------------------------------------------------------------- */
2155
2156 FUNCTION approve_req(p_docid IN NUMBER) RETURN BOOLEAN
2157 IS
2158
2159 /*Bug 4537860:Hit the _all tables instead of the striped views.*/
2160
2161 CURSOR auth_status(header_id NUMBER)
2162 IS
2163 SELECT authorization_status
2164 FROM po_requisition_headers_all
2165 WHERE requisition_header_id = header_id;
2166
2167 l_auth_status po_requisition_headers.authorization_status%TYPE;
2168
2169 d_module VARCHAR2(70) := 'po.plsql.PO_SUPPLY.approve_req';
2170 d_progress NUMBER;
2171
2172 l_return_value BOOLEAN := FALSE;
2173
2174 BEGIN
2175
2176 IF (PO_LOG.d_proc) THEN
2177 PO_LOG.proc_begin(d_module);
2178 PO_LOG.proc_begin(d_module, 'p_docid', p_docid);
2179 END IF;
2180
2181 d_progress := 10;
2182
2183 OPEN auth_status(p_docid);
2184 FETCH auth_status INTO l_auth_status;
2185 CLOSE auth_status;
2186
2187 -- Create Requisition Supply if the Requisition has been Approved
2188
2189 IF (l_auth_status = 'APPROVED') THEN
2190
2191 l_return_value := create_req(
2192 p_entity_id => p_docid
2193 , p_entity_type => 'REQ HDR'
2194 );
2195 ELSE
2196
2197 l_return_value := TRUE;
2198
2199 END IF;
2200
2201 IF (PO_LOG.d_proc) THEN
2202 PO_LOG.proc_return(d_module, l_return_value);
2203 PO_LOG.proc_end(d_module);
2204 END IF;
2205
2206 return(l_return_value);
2207
2208 EXCEPTION
2209
2210 WHEN others THEN
2211
2212 IF (PO_LOG.d_exc) THEN
2213 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
2214 END IF;
2215
2216 IF (auth_status%ISOPEN) THEN
2217 close auth_status;
2218 END IF;
2219
2220 return(FALSE);
2221
2222 END approve_req;
2223
2224 /* ----------------------------------------------------------------------- */
2225
2226 -- Clear Requisition Header, Requisition Lines
2227 /* ----------------------------------------------------------------------- */
2228
2229
2230 FUNCTION remove_req(
2231 p_entity_id IN NUMBER
2232 , p_entity_type IN VARCHAR2
2233 ) RETURN BOOLEAN
2234 IS
2235
2236 d_module VARCHAR2(70) := 'po.plsql.PO_SUPPLY.remove_req';
2237 d_progress NUMBER;
2238
2239 BEGIN
2240
2241 IF (PO_LOG.d_proc) THEN
2242 PO_LOG.proc_begin(d_module);
2243 PO_LOG.proc_begin(d_module, 'p_entity_id', p_entity_id);
2244 PO_LOG.proc_begin(d_module, 'p_entity_type', p_entity_type);
2245 END IF;
2246
2247 d_progress := 10;
2248
2249 IF (p_entity_type = 'REQ HDR') THEN
2250
2251 d_progress := 20;
2252
2253 UPDATE mtl_supply
2254 SET quantity = 0
2255 , change_flag = 'Y'
2256 WHERE supply_type_code = 'REQ'
2257 AND req_header_id = p_entity_id;
2258
2259 ELSIF (p_entity_type = 'REQ LINE') THEN
2260
2261 d_progress := 30;
2262
2263 UPDATE mtl_supply
2264 SET quantity = 0
2265 , change_flag = 'Y'
2266 WHERE supply_type_code = 'REQ'
2267 AND req_line_id = p_entity_id;
2268
2269 END IF; -- if (p_entity_type = ...)
2270
2271 IF (PO_LOG.d_stmt) THEN
2272 PO_LOG.stmt(d_module, d_progress, 'Updated ' || SQL%ROWCOUNT || ' rows');
2273 END IF;
2274
2275 IF (PO_LOG.d_proc) THEN
2276 PO_LOG.proc_return(d_module, TRUE);
2277 PO_LOG.proc_end(d_module);
2278 END IF;
2279
2280 return(TRUE);
2281
2282
2283 EXCEPTION
2284
2285 WHEN others THEN
2286 IF (PO_LOG.d_exc) THEN
2287 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
2288 END IF;
2289
2290 return(FALSE);
2291
2292 END remove_req;
2293
2294 /* ----------------------------------------------------------------------- */
2295
2296 -- Clear Requisition Vendor Sourced Lines
2297
2298 /* ----------------------------------------------------------------------- */
2299
2300
2301 FUNCTION remove_req_vend_lines(p_docid IN NUMBER) RETURN BOOLEAN IS
2302
2303 d_module VARCHAR2(70) := 'po.plsql.PO_SUPPLY.remove_req_vend_lines';
2304 d_progress NUMBER;
2305
2306 BEGIN
2307
2308 IF (PO_LOG.d_proc) THEN
2309 PO_LOG.proc_begin(d_module);
2310 PO_LOG.proc_begin(d_module, 'p_docid', p_docid);
2311 END IF;
2312
2313 d_progress := 10;
2314
2315 /*Bug 4537860:Hit the _all tables instead of the striped views.*/
2316
2317 UPDATE mtl_supply ms
2318 SET ms.quantity = 0
2319 , ms.change_flag = 'Y'
2320 WHERE ms.supply_type_code = 'REQ'
2321 AND ms.req_header_id = p_docid
2322 AND EXISTS
2323 (
2324 SELECT 1
2325 FROM po_requisition_lines_clm_v porl
2326 WHERE porl.source_type_code = 'VENDOR'
2327 AND porl.requisition_line_id = ms.req_line_id
2328 AND porl.line_location_id is null--Bug 13518969
2329 );
2330
2331 IF (PO_LOG.d_stmt) THEN
2332 PO_LOG.stmt(d_module, d_progress, 'Updated ' || SQL%ROWCOUNT || ' rows');
2333 END IF;
2334
2335 IF (PO_LOG.d_proc) THEN
2336 PO_LOG.proc_return(d_module, TRUE);
2337 PO_LOG.proc_end(d_module);
2338 END IF;
2339
2340 return(TRUE);
2341
2342 EXCEPTION
2343
2344 WHEN others THEN
2345 IF (PO_LOG.d_exc) THEN
2346 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
2347 END IF;
2348
2349 return(FALSE);
2350
2351 END remove_req_vend_lines;
2352
2353
2354 /* ----------------------------------------------------------------------- */
2355
2356 -- Create Requisition Header, Line Supply
2357
2358 /* ----------------------------------------------------------------------- */
2359
2360
2361
2362 FUNCTION create_req(
2363 p_entity_id IN NUMBER
2364 , p_entity_type IN VARCHAR2
2365 ) RETURN BOOLEAN
2366 IS
2367
2368 d_module VARCHAR2(70) := 'po.plsql.PO_SUPPLY.create_req';
2369 d_progress NUMBER;
2370 l_conformed_id NUMBER;
2371
2372 l_return_value BOOLEAN := FALSE;
2373
2374 BEGIN
2375
2376 IF (PO_LOG.d_proc) THEN
2377 PO_LOG.proc_begin(d_module);
2378 PO_LOG.proc_begin(d_module, 'p_entity_id', p_entity_id);
2379 PO_LOG.proc_begin(d_module, 'p_entity_type', p_entity_type);
2380 END IF;
2381
2382 d_progress := 10;
2383
2384
2385 IF (p_entity_type = 'REQ HDR') THEN
2386
2387 d_progress := 20;
2388
2389 BEGIN
2390 SELECT conformed_header_id INTO l_conformed_id
2391 FROM po_requisition_headers_all
2392 WHERE requisition_header_id = p_entity_id;
2393 EXCEPTION
2394 WHEN No_Data_Found THEN
2395 l_conformed_id := NULL;
2396 END;
2397
2398 IF l_conformed_id IS NOT NULL THEN
2399 RETURN TRUE;
2400 END IF;
2401
2402 DELETE FROM mtl_supply ms1
2403 WHERE ms1.supply_source_id IN
2404 (
2405 SELECT pl.requisition_line_id
2406 FROM po_requisition_lines_clm_v pl
2407 WHERE pl.requisition_header_id = p_entity_id
2408 AND NVL(pl.modified_by_agent_flag, 'N') <> 'Y'
2409 AND NVL(pl.closed_code, 'OPEN') = 'OPEN'
2410 AND NVL(pl.cancel_flag, 'N') = 'N'
2411 AND pl.line_location_id IS NULL
2412 )
2413 AND ms1.supply_type_code = 'REQ';
2414
2415 IF (PO_LOG.d_stmt) THEN
2416 PO_LOG.stmt(d_module, d_progress, 'Deleted ' || SQL%ROWCOUNT || ' rows');
2417 END IF;
2418
2419 -- <Doc Manager Rewrite R12 End>
2420
2421 d_progress := 30;
2422
2423 /*Bug 4537860:Hit the _all tables instead of the striped views.*/
2424
2425 INSERT INTO mtl_supply
2426 (supply_type_code,
2427 supply_source_id,
2428 last_updated_by,
2429 last_update_date,
2430 last_update_login,
2431 created_by,
2432 creation_date,
2433 req_header_id,
2434 req_line_id,
2435 item_id,
2436 item_revision,
2437 quantity,
2438 unit_of_measure,
2439 receipt_date,
2440 need_by_date,
2441 destination_type_code,
2442 location_id,
2443 from_organization_id,
2444 from_subinventory,
2445 to_organization_id,
2446 to_subinventory,
2447 change_flag)
2448 SELECT 'REQ',
2449 prl.requisition_line_id,
2450 last_updated_by,
2451 last_update_date,
2452 last_update_login,
2453 created_by,
2454 creation_date,
2455 prl.requisition_header_id,
2456 prl.requisition_line_id,
2457 prl.item_id,
2458 decode(prl.source_type_code,'INVENTORY', null,
2459 prl.item_revision),
2460 prl.quantity - ( nvl(prl.QUANTITY_CANCELLED, 0) +
2461 nvl(prl.QUANTITY_DELIVERED, 0) ),
2462 prl.unit_meas_lookup_code,
2463 prl.need_by_date,
2464 prl.need_by_date,
2465 prl.destination_type_code,
2466 prl.deliver_to_location_id,
2467 prl.source_organization_id,
2468 prl.source_subinventory,
2469 prl.destination_organization_id,
2470 prl.destination_subinventory,
2471 'Y'
2472 FROM po_requisition_lines_clm_v prl
2473 WHERE prl.requisition_header_id = p_entity_id
2474 AND nvl(prl.modified_by_agent_flag,'N') <> 'Y'
2475 AND nvl(prl.CLOSED_CODE,'OPEN') = 'OPEN'
2476 AND nvl(prl.CANCEL_FLAG, 'N') = 'N'
2477 -- <Doc Manager Rewrite R12>: Filter out amount basis
2478 AND prl.matching_basis <> 'AMOUNT'
2479 AND prl.line_location_id is null
2480 AND not exists
2481 (SELECT 'supply exists'
2482 FROM mtl_supply ms
2483 WHERE ms.supply_type_code = 'REQ'
2484 AND ms.supply_source_id = prl.requisition_line_id);
2485
2486 ELSIF (p_entity_type = 'REQ LINE') THEN
2487
2488 d_progress := 40;
2489
2490 BEGIN
2491 SELECT conformed_line_id INTO l_conformed_id
2492 FROM po_requisition_lines_all
2493 WHERE requisition_line_id = p_entity_id;
2494 EXCEPTION
2495 WHEN No_Data_Found THEN
2496 l_conformed_id := NULL;
2497 END;
2498
2499 IF l_conformed_id IS NOT NULL THEN
2500 RETURN TRUE;
2501 END IF;
2502
2503
2504 INSERT INTO mtl_supply
2505 (supply_type_code,
2506 supply_source_id,
2507 last_updated_by,
2508 last_update_date,
2509 last_update_login,
2510 created_by,
2511 creation_date,
2512 req_header_id,
2513 req_line_id,
2514 item_id,
2515 item_revision,
2516 quantity,
2517 unit_of_measure,
2518 receipt_date,
2519 need_by_date,
2520 destination_type_code,
2521 location_id,
2522 from_organization_id,
2523 from_subinventory,
2524 to_organization_id,
2525 to_subinventory,
2526 change_flag)
2527 SELECT 'REQ',
2528 prl.requisition_line_id,
2529 last_updated_by,
2530 last_update_date,
2531 last_update_login,
2532 created_by,
2533 creation_date,
2534 prl.requisition_header_id,
2535 prl.requisition_line_id,
2536 prl.item_id,
2537 decode(prl.source_type_code,'INVENTORY', null,
2538 prl.item_revision),
2539 prl.quantity - ( nvl(prl.QUANTITY_CANCELLED, 0) +
2540 nvl(prl.QUANTITY_DELIVERED, 0) ),
2541 prl.unit_meas_lookup_code,
2542 prl.need_by_date,
2543 prl.need_by_date,
2544 prl.destination_type_code,
2545 prl.deliver_to_location_id,
2546 prl.source_organization_id,
2547 prl.source_subinventory,
2548 prl.destination_organization_id,
2549 prl.destination_subinventory,
2550 'Y'
2551 FROM po_requisition_lines_clm_v prl
2552 WHERE prl.requisition_line_id = p_entity_id
2553 AND nvl(prl.modified_by_agent_flag,'N') <> 'Y'
2554 AND nvl(prl.CLOSED_CODE, 'OPEN') = 'OPEN'
2555 AND nvl(prl.CANCEL_FLAG, 'N') = 'N'
2556 AND prl.line_location_id IS NULL
2557 -- <Doc Manager Rewrite R12 Start>: Add filters from Pro*C
2558 AND prl.matching_basis <> 'AMOUNT'
2559 AND NOT EXISTS
2560 (
2561 SELECT 'supply exists'
2562 FROM mtl_supply
2563 WHERE supply_type_code = 'REQ'
2564 AND supply_source_id = prl.requisition_line_id
2565 );
2566 -- <Doc Manager Rewrite R12 End>
2567
2568 END IF; -- if p_entity_type = ...
2569
2570 d_progress := 100;
2571
2572 IF (PO_LOG.d_stmt) THEN
2573 PO_LOG.stmt(d_module, d_progress, 'Inserted ' || SQL%ROWCOUNT || ' rows');
2574 END IF;
2575
2576 -- <Doc Manager Rewrite R12>: After analysis, no rows is OK, not error.
2577 -- This is to handle services lines.
2578
2579 l_return_value := TRUE;
2580
2581 IF (PO_LOG.d_proc) THEN
2582 PO_LOG.proc_return(d_module, l_return_value);
2583 PO_LOG.proc_end(d_module);
2584 END IF;
2585
2586 return(l_return_value);
2587
2588 EXCEPTION
2589
2590 WHEN others THEN
2591
2592 IF (PO_LOG.d_exc) THEN
2593 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
2594 END IF;
2595
2596 return(FALSE);
2597
2598 END create_req;
2599
2600
2601
2602 /* ----------------------------------------------------------------------- */
2603
2604 -- Maintain mtl_supply for Explode or Multisource Action
2605
2606 /* ----------------------------------------------------------------------- */
2607
2608 FUNCTION explode(p_lineid IN NUMBER) RETURN BOOLEAN IS
2609
2610 d_module VARCHAR2(70) := 'po.plsql.PO_SUPPLY.explode';
2611 d_progress NUMBER;
2612
2613 l_return_value BOOLEAN := FALSE;
2614
2615 BEGIN
2616
2617 IF (PO_LOG.d_proc) THEN
2618 PO_LOG.proc_begin(d_module);
2619 PO_LOG.proc_begin(d_module, 'p_lineid', p_lineid);
2620 END IF;
2621
2622 d_progress := 10;
2623
2624 BEGIN
2625
2626 -- Set the Supply Quantity of Parent to 0
2627
2628 l_return_value := remove_req(
2629 p_entity_id => p_lineid
2630 , p_entity_type => 'REQ LINE'
2631 );
2632
2633 IF (NOT l_return_value) THEN
2634 IF (PO_LOG.d_stmt) THEN
2635 PO_LOG.stmt(d_module, d_progress, 'remove_req not successful');
2636 END IF;
2637
2638 RAISE PO_CORE_S.g_early_return_exc;
2639 END IF;
2640
2641 d_progress := 20;
2642
2643 -- Insert New Supply for each new Line created by the Explode or
2644 -- Multisource Action
2645
2646 /*Bug 4537860:Hit the _all tables instead of the striped views.*/
2647
2648 insert into mtl_supply(supply_type_code,
2649 supply_source_id,
2650 last_updated_by,
2651 last_update_date,
2652 last_update_login,
2653 created_by,
2654 creation_date,
2655 req_header_id,
2656 req_line_id,
2657 item_id,
2658 item_revision,
2659 quantity,
2660 unit_of_measure,
2661 receipt_date,
2662 need_by_date,
2663 destination_type_code,
2664 location_id,
2665 from_organization_id,
2666 from_subinventory,
2667 to_organization_id,
2668 to_subinventory,
2669 change_flag)
2670 select 'REQ',
2671 prl.requisition_line_id,
2672 prl.last_updated_by,
2673 prl.last_update_date,
2674 prl.last_update_login,
2675 prl.created_by,
2676 prl.creation_date,
2677 prl.requisition_header_id,
2678 prl.requisition_line_id,
2679 prl.item_id,
2680 prl.item_revision,
2681 prl.quantity - (nvl(prl.quantity_cancelled, 0) +
2682 nvl(prl.quantity_delivered, 0)),
2683 prl.unit_meas_lookup_code,
2684 prl.need_by_date,
2685 prl.need_by_date,
2686 prl.destination_type_code,
2687 prl.deliver_to_location_id,
2688 prl.source_organization_id,
2689 prl.source_subinventory,
2690 prl.destination_organization_id,
2691 prl.destination_subinventory,
2692 'Y'
2693 from po_requisition_lines_clm_v prl
2694 where prl.requisition_line_id in
2695 (select prl1.requisition_line_id
2696 from po_requisition_lines_clm_v prl1
2697 where prl1.requisition_header_id =
2698 (select prl2.requisition_header_id
2699 from po_requisition_lines_clm_v prl2
2700 where prl2.requisition_line_id = p_lineid
2701 and prl2.modified_by_agent_flag = 'Y'))
2702 and nvl(prl.modified_by_agent_flag, 'N') <> 'Y'
2703 and nvl(prl.closed_code, 'OPEN') = 'OPEN'
2704 and nvl(prl.cancel_flag, 'N') = 'N'
2705 and prl.line_location_id is null
2706 and not exists
2707 (select 'Supply Exists'
2708 from mtl_supply
2709 where supply_type_code = 'REQ'
2710 and supply_source_id = prl.requisition_line_id);
2711
2712
2713 IF (PO_LOG.d_stmt) THEN
2714 PO_LOG.stmt(d_module, d_progress, 'Exploded ' || SQL%ROWCOUNT || ' rows');
2715 END IF;
2716
2717 l_return_value := TRUE;
2718
2719 EXCEPTION
2720 WHEN PO_CORE_S.g_early_return_exc THEN
2721 NULL;
2722 END;
2723
2724 IF (PO_LOG.d_proc) THEN
2725 PO_LOG.proc_return(d_module, l_return_value);
2726 PO_LOG.proc_end(d_module);
2727 END IF;
2728
2729 return(l_return_value);
2730
2731 EXCEPTION
2732
2733 WHEN others THEN
2734
2735 IF (PO_LOG.d_exc) THEN
2736 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
2737 END IF;
2738
2739 return(FALSE);
2740
2741 END explode;
2742
2743 /* ----------------------------------------------------------------------- */
2744
2745 -- Updates Requisition Quantity in mtl_supply
2746
2747 /* ----------------------------------------------------------------------- */
2748
2749
2750 FUNCTION update_req_line_qty(
2751 p_lineid IN NUMBER
2752 , p_qty IN NUMBER
2753 ) RETURN BOOLEAN
2754 IS
2755
2756 d_module VARCHAR2(70) := 'po.plsql.PO_SUPPLY.update_req_line_qty';
2757 d_progress NUMBER;
2758
2759 BEGIN
2760
2761 IF (PO_LOG.d_proc) THEN
2762 PO_LOG.proc_begin(d_module);
2763 PO_LOG.proc_begin(d_module, 'p_lineid', p_lineid);
2764 PO_LOG.proc_begin(d_module, 'p_qty', p_qty);
2765 END IF;
2766
2767 d_progress := 10;
2768
2769 UPDATE mtl_supply
2770 SET quantity = p_qty
2771 , change_flag = 'Y'
2772 WHERE supply_type_code = 'REQ'
2773 AND req_line_id = p_lineid;
2774
2775 IF (PO_LOG.d_stmt) THEN
2776 PO_LOG.stmt(d_module, d_progress, 'Updated ' || SQL%ROWCOUNT || ' rows');
2777 END IF;
2778
2779
2780 IF (PO_LOG.d_proc) THEN
2781 PO_LOG.proc_return(d_module, TRUE);
2782 PO_LOG.proc_end(d_module);
2783 END IF;
2784
2785 return(TRUE);
2786
2787 EXCEPTION
2788
2789 WHEN others THEN
2790
2791 IF (PO_LOG.d_exc) THEN
2792 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
2793 END IF;
2794
2795 return(FALSE);
2796
2797 END update_req_line_qty;
2798
2799 /* ----------------------------------------------------------------------- */
2800
2801 -- Updates Receipt Date in mtl_supply
2802
2803 /* ----------------------------------------------------------------------- */
2804
2805 FUNCTION update_req_line_date(
2806 p_lineid IN NUMBER
2807 , p_receipt_date IN DATE
2808 ) RETURN BOOLEAN
2809 IS
2810
2811 d_module VARCHAR2(70) := 'po.plsql.PO_SUPPLY.update_req_line_date';
2812 d_progress NUMBER;
2813
2814 BEGIN
2815
2816 IF (PO_LOG.d_proc) THEN
2817 PO_LOG.proc_begin(d_module);
2818 PO_LOG.proc_begin(d_module, 'p_lineid', p_lineid);
2819 PO_LOG.proc_begin(d_module, 'p_receipt_date', p_receipt_date);
2820 END IF;
2821
2822 d_progress := 10;
2823
2824 UPDATE mtl_supply
2825 SET receipt_date = p_receipt_date
2826 , need_by_date = p_receipt_date -- Bug 3443313
2827 , change_flag = 'Y'
2828 WHERE supply_type_code = 'REQ'
2829 AND req_line_id = p_lineid;
2830
2831 IF (PO_LOG.d_stmt) THEN
2832 PO_LOG.stmt(d_module, d_progress, 'Updated ' || SQL%ROWCOUNT || ' rows');
2833 END IF;
2834
2835
2836 IF (PO_LOG.d_proc) THEN
2837 PO_LOG.proc_return(d_module, TRUE);
2838 PO_LOG.proc_end(d_module);
2839 END IF;
2840
2841 return(TRUE);
2842
2843 EXCEPTION
2844
2845 WHEN others THEN
2846
2847 IF (PO_LOG.d_exc) THEN
2848 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
2849 END IF;
2850
2851 return(FALSE);
2852
2853 END update_req_line_date;
2854
2855
2856
2857 /* ----------------------------------------------------------------------- */
2858
2859 -- Update Planned PO, Planned PO Shipment Supply
2860
2861 -- Update of Planned PO is based on Entity Type
2862 --
2863 -- Entity Type Action
2864 -- ----------- ------------------------------------------
2865 -- <Doc Manager Rewrite R12>: REMOVE PLANNED is not used anywhere; removed
2866 --
2867 -- UPDATE PLANNED Update Quantity in mtl_supply
2868 --
2869 -- ADD PLANNED Update Quantity in mtl_supply
2870 --
2871
2872 /* ----------------------------------------------------------------------- */
2873
2874 FUNCTION update_planned_po(
2875 p_docid IN NUMBER
2876 , p_shipid IN NUMBER DEFAULT 0
2877 , p_entity_type IN VARCHAR2
2878 , p_supply_flag IN OUT NOCOPY BOOLEAN
2879 ) RETURN BOOLEAN
2880 IS
2881
2882 d_module VARCHAR2(70) := 'po.plsql.PO_SUPPLY.update_planned_po';
2883 d_progress NUMBER;
2884
2885 l_ppo_dist_id_tbl po_tbl_number;
2886 l_ppo_dist_qty_tbl po_tbl_number;
2887
2888
2889 BEGIN
2890
2891 IF (PO_LOG.d_proc) THEN
2892 PO_LOG.proc_begin(d_module);
2893 PO_LOG.proc_begin(d_module, 'p_docid', p_docid);
2894 PO_LOG.proc_begin(d_module, 'p_shipid', p_shipid);
2895 PO_LOG.proc_begin(d_module, 'p_entity_type', p_entity_type);
2896 PO_LOG.proc_begin(d_module, 'p_supply_flag', p_supply_flag);
2897 END IF;
2898
2899 d_progress := 10;
2900
2901 IF (p_entity_type = 'UPDATE PLANNED') THEN
2902
2903 d_progress := 20;
2904
2905 -- <Doc Manager Rewrite R12>: Use logic from Pro*C
2906 /*Bug 4537860:Hit the _all tables instead of the striped views.*/
2907
2908 UPDATE mtl_supply ms
2909 SET ms.quantity =
2910 (
2911 SELECT ms.quantity +
2912 NVL( sum(nvl(pd.quantity_cancelled,0)),0)
2913 FROM po_distributions_all pd
2914 WHERE pd.po_release_id = p_docid
2915 AND pd.line_location_id = p_shipid
2916 AND pd.source_distribution_id = ms.supply_source_id
2917 )
2918 , ms.change_flag = 'Y'
2919 WHERE ms.supply_type_code = 'PO'
2920 AND ms.po_line_location_id =
2921 (
2922 SELECT poll. source_shipment_id
2923 FROM po_line_locations_all poll
2924 WHERE poll.line_location_id = p_shipid
2925 );
2926
2927 IF (SQL%NOTFOUND) THEN
2928 p_supply_flag := FALSE;
2929 ELSE
2930 p_supply_flag := TRUE;
2931 END IF;
2932
2933 ELSIF (p_entity_type = 'ADD PLANNED') THEN
2934
2935 -- <Doc Manager Rewrite R12>: Use logic from Pro*C and
2936 -- use bulk processing to avoid unnecessary nested cursors
2937
2938 /*Bug 4537860:Hit the _all tables instead of the striped views.*/
2939
2940 d_progress := 30;
2941
2942 SELECT pod.source_distribution_id, pod.quantity_ordered
2943 BULK COLLECT INTO l_ppo_dist_id_tbl, l_ppo_dist_qty_tbl
2944 FROM po_distributions_all pod
2945 WHERE pod.po_release_id = p_docid
2946 AND (pod.po_line_id IS NOT NULL AND pod.line_location_id IS NOT NULL);
2947
2948 d_progress := 40;
2949
2950 FORALL i IN 1..l_ppo_dist_id_tbl.COUNT
2951 UPDATE mtl_supply mts
2952 SET mts.quantity = l_ppo_dist_qty_tbl(i) -
2953 (
2954 SELECT NVL(sum(pod.quantity_ordered -
2955 NVL(pod.quantity_cancelled, 0)), 0)
2956 FROM po_distributions_all pod
2957 WHERE pod.source_distribution_id = l_ppo_dist_id_tbl(i)
2958 AND pod.po_line_id IS NOT NULL
2959 AND pod.line_location_id IS NOT NULL
2960 )
2961 , mts.change_flag = 'Y'
2962 WHERE mts.po_distribution_id = l_ppo_dist_id_tbl(i);
2963
2964 d_progress := 50;
2965
2966 IF ((l_ppo_dist_id_tbl.COUNT = 0) OR (SQL%NOTFOUND)) THEN
2967 p_supply_flag := FALSE;
2968 ELSE
2969 p_supply_flag := TRUE;
2970 END IF;
2971
2972 END IF;
2973
2974 IF (PO_LOG.d_stmt) THEN
2975 PO_LOG.stmt(d_module, d_progress, 'Updated ' || SQL%ROWCOUNT || ' rows');
2976 END IF;
2977
2978 IF (PO_LOG.d_proc) THEN
2979 PO_LOG.proc_return(d_module, TRUE);
2980 PO_LOG.proc_end(d_module, 'p_supply_flag', p_supply_flag);
2981 PO_LOG.proc_end(d_module);
2982 END IF;
2983
2984 return(TRUE);
2985
2986 EXCEPTION
2987
2988 WHEN others THEN
2989
2990 IF (PO_LOG.d_exc) THEN
2991 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
2992 END IF;
2993
2994 return(FALSE);
2995
2996 END update_planned_po;
2997
2998
2999 /* ----------------------------------------------------------------------- */
3000 -- Obsolete debug method
3001 /* ----------------------------------------------------------------------- */
3002 FUNCTION get_debug RETURN VARCHAR2 IS
3003 BEGIN
3004 return NULL;
3005 END get_debug;
3006
3007
3008
3009 END PO_SUPPLY;