[Home] [Help]
PACKAGE BODY: APPS.PO_SUPPLY
Source
1 PACKAGE BODY PO_SUPPLY AS
2 /* $Header: RCVRCSUB.pls 120.11.12010000.2 2008/09/01 09:49:28 sdpaul 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_all
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_all 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_all 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 BEGIN
924
925 IF (PO_LOG.d_proc) THEN
926 PO_LOG.proc_begin(d_module);
927 PO_LOG.proc_begin(d_module, 'p_entity_id', p_entity_id);
928 PO_LOG.proc_begin(d_module, 'p_entity_type', p_entity_type);
929 END IF;
930
931 d_progress := 10;
932
933 BEGIN
934
935 l_return_value := delete_supply(
936 p_entity_id => p_entity_id
937 , p_entity_type => p_entity_type
938 );
939
940 IF (NOT l_return_value) THEN
941
942 IF (PO_LOG.d_stmt) THEN
943 PO_LOG.stmt(d_module, d_progress, 'delete_supply not successful');
944 END IF;
945
946 RAISE PO_CORE_S.g_early_return_exc;
947
948 END IF;
949
950 d_progress := 20;
951
952 b_entity_id := p_entity_id;
953
954
955 -- Setup Dynamic SQL for Distributions
956 /*Bug 4537860:Hit the _all tables instead of the striped views.*/
957 sql_dist := 'SELECT pd.po_distribution_id, '
958 || 'pd.quantity_ordered - nvl(pd.quantity_delivered, 0) - nvl(pd.quantity_cancelled, 0), '
959 || 'pl.unit_meas_lookup_code, '
960 || 'pd.line_location_id '
961 || 'FROM po_distributions_all pd, po_lines_all pl ';
962
963
964 IF (p_entity_type = 'PO') THEN
965
966 d_progress := 30;
967
968 sql_dist := sql_dist
969 || 'WHERE pd.po_header_id = :b_entity_id '
970 || 'AND pd.po_line_id = pl.po_line_id '
971 || 'AND pd.po_release_id IS NULL '; -- <Doc Manager Rewrite R12>
972
973
974 ELSIF (p_entity_type = 'RELEASE') THEN
975
976 d_progress := 40;
977
978 sql_dist := sql_dist
979 || 'WHERE pd.po_release_id = :b_entity_id '
980 || 'AND pd.po_line_id = pl.po_line_id ';
981
982
983 ELSIF (p_entity_type = 'PO LINE') THEN
984
985 d_progress := 50;
986
987 sql_dist := sql_dist
988 || 'WHERE pd.po_line_id = :b_entity_id '
989 || 'AND pd.po_line_id = pl.po_line_id '
990 || 'AND pd.po_release_id IS NULL '; -- <Doc Manager Rewrite R12>
991
992
993 ELSIF (p_entity_type IN ('PO SHIPMENT', 'RELEASE SHIPMENT')) THEN
994
995 d_progress := 60;
996 sql_dist := sql_dist
997 || 'WHERE pd.line_location_id = :b_entity_id '
998 || 'AND pd.po_line_id = pl.po_line_id ';
999
1000 END IF; -- IF p_entity_type = ...
1001
1002 -- <Doc Manager Rewrite R12>: Add order-by clause as in Pro*C
1003 sql_dist := sql_dist || 'ORDER BY pd.line_location_id';
1004
1005 IF (PO_LOG.d_stmt) THEN
1006 PO_LOG.stmt(d_module, d_progress, 'sql_dist', sql_dist);
1007 END IF;
1008
1009 d_progress := 70;
1010
1011 cur_dist := dbms_sql.open_cursor;
1012 dbms_sql.parse(cur_dist, sql_dist, dbms_sql.v7);
1013 dbms_sql.bind_variable(cur_dist, ':b_entity_id', b_entity_id);
1014
1015 dbms_sql.define_column(cur_dist, 1, l_distid);
1016 dbms_sql.define_column(cur_dist, 2, l_qty);
1017 dbms_sql.define_column(cur_dist, 3, l_uom, 25);
1018 dbms_sql.define_column(cur_dist, 4, l_line_loc_id);
1019
1020 num_dist := dbms_sql.execute(cur_dist);
1021
1022 LOOP
1023
1024 d_progress := 80;
1025
1026 IF (dbms_sql.fetch_rows(cur_dist) > 0) THEN
1027
1028 d_progress := 90;
1029
1030 dbms_sql.column_value(cur_dist, 1, l_distid);
1031 dbms_sql.column_value(cur_dist, 2, l_qty);
1032 dbms_sql.column_value(cur_dist, 3, l_uom);
1033 dbms_sql.column_value(cur_dist, 4, l_line_loc_id);
1034
1035
1036 IF (PO_LOG.d_stmt) THEN
1037 PO_LOG.stmt(d_module, d_progress, 'l_distid', l_distid);
1038 PO_LOG.stmt(d_module, d_progress, 'l_qty', l_qty);
1039 PO_LOG.stmt(d_module, d_progress, 'l_uom', l_uom);
1040 PO_LOG.stmt(d_module, d_progress, 'l_line_loc_id', l_line_loc_id);
1041 END IF;
1042
1043 -- <Doc Manager Rewrite R12 Start> : From Pro*C
1044 -- For PO distribution, get the sum of quantity already
1045 -- released against that distribution.
1046
1047 IF (p_entity_type IN ('PO', 'PO LINE', 'PO SHIPMENT')) THEN
1048
1049 d_progress := 100;
1050 /*Bug 4537860:Hit the _all tables instead of the striped views.*/
1051
1052 SELECT NVL(SUM(pod.quantity_ordered - NVL(pod.quantity_delivered, 0)
1053 - NVL(pod.quantity_cancelled, 0)), 0)
1054 INTO l_released_qty
1055 FROM po_distributions_all pod
1056 , po_releases_all por
1057 WHERE pod.source_distribution_id = l_distid
1058 AND pod.po_release_id = por.po_release_id
1059 AND NVL(por.authorization_status, 'IN PROCESS') = 'APPROVED';
1060
1061 ELSE
1062
1063 d_progress := 105;
1064 l_released_qty := 0;
1065
1066 END IF; -- p_entity IN ...
1067
1068 IF (PO_LOG.d_stmt) THEN
1069 PO_LOG.stmt(d_module, d_progress, 'l_released_qty', l_released_qty);
1070 END IF;
1071
1072 -- <Doc Manager Rewrite R12 End>
1073
1074
1075 IF (l_qty < 0) THEN
1076 l_out_poqty := 0;
1077 ELSE
1078 l_out_poqty := l_qty - l_released_qty; -- <Doc Manager Rewrite R12>
1079 END IF;
1080
1081 d_progress := 100;
1082
1083 IF (l_line_loc_id <> l_prev_line_loc_id) THEN
1084
1085 l_supply_qty := 0;
1086 l_supply_qty_in_pouom := 0;
1087
1088 d_progress := 110;
1089
1090 OPEN supply_lloc(l_line_loc_id); -- Bug#4962625
1091 LOOP
1092
1093 FETCH supply_lloc INTO l_supply_qty, l_supply_uom, l_supply_itemid;
1094 EXIT WHEN supply_lloc%NOTFOUND;
1095
1096 IF (PO_LOG.d_stmt) THEN
1097 PO_LOG.stmt(d_module, d_progress, 'l_supply_qty', l_supply_qty);
1098 PO_LOG.stmt(d_module, d_progress, 'l_supply_uom', l_supply_uom);
1099 PO_LOG.stmt(d_module, d_progress, 'l_supply_itemid', l_supply_itemid);
1100 END IF;
1101
1102 d_progress := 120;
1103
1104 IF (l_supply_qty > 0) THEN
1105
1106 l_supply_qty_in_pouom := l_supply_qty_in_pouom +
1107 INV_CONVERT.INV_UM_CONVERT(
1108 item_id => l_supply_itemid
1109 , precision => 5
1110 , from_quantity => l_supply_qty
1111 , from_unit => NULL
1112 , to_unit => NULL
1113 , from_name => l_supply_uom
1114 , to_name => l_uom
1115 );
1116
1117 END IF; -- IF (l_supply_qty > 0)
1118
1119 END LOOP; -- supply_lloc cursor
1120
1121 IF supply_lloc%ISOPEN THEN
1122 close supply_lloc;
1123 END IF;
1124
1125 d_progress := 130;
1126
1127 IF (PO_LOG.d_stmt) THEN
1128 PO_LOG.stmt(d_module, d_progress, 'l_supply_qty_in_pouom', l_supply_qty_in_pouom);
1129 END IF;
1130
1131 l_prev_line_loc_id := l_line_loc_id;
1132
1133 END IF; -- IF (l_line_loc_id <> l_prev_line_loc_id)
1134
1135
1136 IF (l_out_poqty >= l_supply_qty_in_pouom) THEN
1137 l_out_poqty := l_out_poqty - l_supply_qty_in_pouom;
1138 l_supply_qty_in_pouom := 0;
1139 ELSE
1140 l_out_poqty := 0;
1141 l_supply_qty_in_pouom := l_supply_qty_in_pouom - l_out_poqty;
1142 END IF;
1143
1144 IF l_out_poqty < 0 THEN
1145 l_out_poqty := 0;
1146 END IF;
1147
1148 d_progress := 140;
1149
1150 IF (PO_LOG.d_stmt) THEN
1151 PO_LOG.stmt(d_module, d_progress, 'l_supply_qty_in_pouom', l_supply_qty_in_pouom);
1152 PO_LOG.stmt(d_module, d_progress, 'l_out_poqty', l_out_poqty);
1153 END IF;
1154
1155 -- Create PO Supply
1156 /*Bug 4537860:Hit the _all tables instead of the striped views.*/
1157
1158 INSERT INTO mtl_supply(supply_type_code,
1159 supply_source_id,
1160 last_updated_by,
1161 last_update_date,
1162 last_update_login,
1163 created_by,
1164 creation_date,
1165 po_header_id,
1166 po_line_id,
1167 po_line_location_id,
1168 po_distribution_id,
1169 po_release_id, -- <Doc Manager Rewrite R12>
1170 item_id,
1171 item_revision,
1172 quantity,
1173 unit_of_measure,
1174 receipt_date,
1175 need_by_date,
1176 destination_type_code,
1177 location_id,
1178 to_organization_id,
1179 to_subinventory,
1180 change_flag)
1181 SELECT 'PO',
1182 pd.po_distribution_id,
1183 pd.last_updated_by,
1184 pd.last_update_date,
1185 pd.last_update_login,
1186 pd.created_by,
1187 pd.creation_date,
1188 pd.po_header_id,
1189 pd.po_line_id,
1190 pd.line_location_id,
1191 pd.po_distribution_id,
1192 pd.po_release_id,
1193 pl.item_id,
1194 pl.item_revision,
1195 l_out_poqty,
1196 pl.unit_meas_lookup_code,
1197 nvl(pll.promised_date, pll.need_by_date),
1198 nvl(pll.promised_date, pll.need_by_date), -- bug 4300150
1199 pd.destination_type_code,
1200 pd.deliver_to_location_id,
1201 pd.destination_organization_id,
1202 pd.destination_subinventory,
1203 'Y'
1204 FROM po_distributions_all pd,
1205 po_line_locations_all pll,
1206 po_lines_all pl
1207 WHERE pd.po_distribution_id = l_distid
1208 AND pll.line_location_id = pd.line_location_id
1209 AND pl.item_id IS NOT NULL -- <Complex Work R12>
1210 AND pl.po_line_id = pd.po_line_id
1211 AND nvl(pll.closed_code, 'OPEN') not in ('FINALLY CLOSED', 'CLOSED', 'CLOSED FOR RECEIVING')
1212 AND nvl(pll.cancel_flag, 'N') = 'N'
1213 AND nvl(pll.approved_flag, 'Y') = 'Y'
1214 AND pll.quantity IS NOT NULL -- <Doc Manager Rewrite R12>
1215 AND not exists
1216 (
1217 SELECT 'Supply Exists'
1218 FROM mtl_supply ms1
1219 WHERE ms1.supply_type_code = 'PO'
1220 AND ms1.supply_source_id = pd.po_distribution_id
1221 );
1222
1223
1224 -- <Doc Manager Rewrite R12>: After analysis, no rows is OK, not error.
1225 -- This is to handle services lines.
1226
1227 ELSE
1228
1229 -- no rows in distributions cursor
1230 EXIT;
1231
1232 END IF; -- IF (dbms_sql.fetch_rows(cur_dist) > 0)
1233
1234 END LOOP; -- dynamic dists cursor
1235
1236 l_return_value := TRUE;
1237
1238 EXCEPTION
1239 WHEN PO_CORE_S.g_early_return_exc THEN
1240 NULL;
1241 END;
1242
1243 IF supply_lloc%ISOPEN THEN
1244 close supply_lloc;
1245 END IF;
1246
1247 IF (dbms_sql.is_open(cur_dist)) THEN
1248 dbms_sql.close_cursor(cur_dist);
1249 END IF;
1250
1251 IF (PO_LOG.d_proc) THEN
1252 PO_LOG.proc_return(d_module, l_return_value);
1253 PO_LOG.proc_end(d_module);
1254 END IF;
1255
1256 return (l_return_value);
1257
1258 EXCEPTION
1259
1260 WHEN others THEN
1261
1262 IF supply_lloc%ISOPEN THEN
1263 close supply_lloc;
1264 END IF;
1265
1266 IF (dbms_sql.is_open(cur_dist)) THEN
1267 dbms_sql.close_cursor(cur_dist);
1268 END IF;
1269
1270 IF (PO_LOG.d_exc) THEN
1271 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
1272 END IF;
1273
1274 return(FALSE);
1275
1276 END create_po_supply;
1277
1278
1279 /* ----------------------------------------------------------------------- */
1280 /* */
1281 /* Delete Supply for PO Header or PO Release */
1282 /* */
1283 /* New PO Supply is inserted based on Entity Type */
1284 /* */
1285 /* Entity Type Action */
1286 /* ----------- ----------------------------------------- */
1287 /* PO Remove PO Supply for PO Header */
1288 /* */
1289 /* RELEASE Remove PO Supply for PO Release */
1290 /* */
1291 /* PO LINE Remove PO Supply for PO Line */
1292 /* */
1293 /* PO SHIPMENT Remove PO Supply for PO Shipment */
1294 /* */
1295 /* RELEASE SHIPMENT Remove PO Supply for Release Shipment */
1296 /* */
1297 /* ----------------------------------------------------------------------- */
1298
1299
1300 FUNCTION delete_supply(
1301 p_entity_id IN NUMBER
1302 , p_entity_type IN VARCHAR2
1303 ) RETURN BOOLEAN
1304 IS
1305
1306 d_module VARCHAR2(70) := 'po.plsql.PO_SUPPLY.delete_supply';
1307 d_progress NUMBER;
1308
1309 BEGIN
1310
1311 IF (PO_LOG.d_proc) THEN
1312 PO_LOG.proc_begin(d_module);
1313 PO_LOG.proc_begin(d_module, 'p_entity_id', p_entity_id);
1314 PO_LOG.proc_begin(d_module, 'p_entity_type', p_entity_type);
1315 END IF;
1316
1317 d_progress := 10;
1318
1319 IF (p_entity_type = 'PO') THEN
1320
1321 DELETE FROM mtl_supply
1322 WHERE supply_type_code = 'PO'
1323 AND po_header_id = p_entity_id
1324 AND po_release_id IS NULL; -- <Doc Manager Rewrite R12>: From Pro*C
1325
1326 ELSIF (p_entity_type = 'RELEASE') THEN
1327
1328 DELETE FROM mtl_supply
1329 WHERE supply_type_code = 'PO'
1330 AND po_release_id = p_entity_id;
1331
1332 ELSIF (p_entity_type = 'PO LINE') THEN
1333
1334 DELETE FROM mtl_supply
1335 WHERE supply_type_code = 'PO'
1336 AND po_line_id = p_entity_id
1337 AND po_release_id IS NULL; -- <Doc Manager Rewrite R12>: From Pro*C
1338
1339 ELSIF (p_entity_type in ('PO SHIPMENT', 'RELEASE SHIPMENT')) THEN
1340
1341 DELETE FROM mtl_supply
1342 WHERE supply_type_code = 'PO'
1343 AND po_line_location_id = p_entity_id;
1344
1345 END IF;
1346
1347 d_progress := 30;
1348
1349 IF (PO_LOG.d_stmt) THEN
1350 PO_LOG.stmt(d_module, d_progress, 'Deleted ' || SQL%ROWCOUNT || ' records');
1351 END IF;
1352
1353 IF (PO_LOG.d_proc) THEN
1354 PO_LOG.proc_return(d_module, TRUE);
1355 PO_LOG.proc_end(d_module);
1356 END IF;
1357
1358 return(TRUE);
1359
1360 EXCEPTION
1361
1362 WHEN others THEN
1363 IF (PO_LOG.d_exc) THEN
1364 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
1365 END IF;
1366
1367 return(FALSE);
1368
1369 END delete_supply;
1370
1371
1372
1373
1374 /* ----------------------------------------------------------------------- */
1375 /* */
1376 /* Update Supply Quantity to 0 for PO Line, PO Shipment or Release */
1377 /* Shipment */
1378 /* */
1379 /* PO Supply is Updated based on the Entity Type */
1380 /* */
1381 /* Entity Type Action */
1382 /* ----------- --------------------------------------------- */
1383 /* PO LINE Update Supply Quantity for PO Line */
1384 /* */
1385 /* PO SHIPMENT Update Supply Quantity for PO Shipment */
1386 /* */
1387 /* RELEASE Update Supply Quantity for Release Shipment */
1388 /* SHIPMENT */
1389 /* */
1390 /* ----------------------------------------------------------------------- */
1391
1392
1393 FUNCTION update_supply(
1394 p_entity_id IN NUMBER
1395 , p_entity_type IN VARCHAR2
1396 , p_shipid IN NUMBER DEFAULT 0
1397 ) RETURN BOOLEAN
1398 IS
1399
1400 d_module VARCHAR2(70) := 'po.plsql.PO_SUPPLY.update_supply';
1401 d_progress NUMBER;
1402
1403 BEGIN
1404
1405 IF (PO_LOG.d_proc) THEN
1406 PO_LOG.proc_begin(d_module);
1407 PO_LOG.proc_begin(d_module, 'p_entity_id', p_entity_id);
1408 PO_LOG.proc_begin(d_module, 'p_entity_type', p_entity_type);
1409 PO_LOG.proc_begin(d_module, 'p_shipid', p_shipid);
1410 END IF;
1411
1412 d_progress := 10;
1413
1414 IF (p_entity_type = 'PO LINE') THEN
1415
1416 UPDATE mtl_supply
1417 SET quantity = 0
1418 , change_flag = 'Y'
1419 WHERE supply_type_code = 'PO'
1420 AND po_line_id = p_entity_id
1421 AND po_release_id IS NULL; -- <Doc Manager Rewrite R12>
1422
1423 ELSIF (p_entity_type = 'PO SHIPMENT') THEN
1424
1425 UPDATE mtl_supply
1426 SET quantity = 0
1427 , change_flag = 'Y'
1428 WHERE supply_type_code = 'PO'
1429 AND po_line_location_id = p_entity_id;
1430
1431 ELSIF (p_entity_type = 'RELEASE SHIPMENT') THEN
1432
1433 UPDATE mtl_supply
1434 SET quantity = 0
1435 , change_flag = 'Y'
1436 WHERE supply_type_code = 'PO'
1437 AND po_release_id = p_entity_id
1438 AND po_line_location_id = p_shipid;
1439
1440 END IF;
1441
1442 d_progress := 30;
1443
1444 IF (PO_LOG.d_stmt) THEN
1445 PO_LOG.stmt(d_module, d_progress, 'Updated ' || SQL%ROWCOUNT || ' records');
1446 END IF;
1447
1448 IF (PO_LOG.d_proc) THEN
1449 PO_LOG.proc_return(d_module, TRUE);
1450 PO_LOG.proc_end(d_module);
1451 END IF;
1452
1453 return(TRUE);
1454
1455 EXCEPTION
1456
1457 WHEN others THEN
1458 IF (PO_LOG.d_exc) THEN
1459 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
1460 END IF;
1461
1462 return(FALSE);
1463
1464 END update_supply;
1465
1466
1467
1468 /* ----------------------------------------------------------------------- */
1469 /* */
1470 /* Cancel Supply for PO Header, Line or Shipment */
1471 /* */
1472 /* PO Supply is Cancelled based on Entity Type */
1473 /* */
1474 /* Entity Type Action */
1475 /* ----------- --------------------------------------- */
1476 /* PO Cancel PO Supply for PO Header */
1477 /* */
1478 /* PO LINE Cancel PO Supply for PO Line */
1479 /* */
1480 /* PO SHIPMENT Cancel PO Supply for PO Shipment */
1481 /* */
1482 /* RELEASE Cancel PO Supply for PO Release */
1483 /* */
1484 /* RELEASE SHIPMENT Cancel PO Supply for Release Shipment */
1485 /* */
1486 /* ----------------------------------------------------------------------- */
1487
1488 FUNCTION cancel_supply(
1489 p_entity_id IN NUMBER
1490 , p_entity_type IN VARCHAR2
1491 , p_shipid IN NUMBER
1492 ) RETURN BOOLEAN
1493 IS
1494
1495 d_module VARCHAR2(70) := 'po.plsql.PO_SUPPLY.cancel_supply';
1496 d_progress NUMBER;
1497
1498 l_return_value BOOLEAN := FALSE;
1499
1500 BEGIN
1501
1502 IF (PO_LOG.d_proc) THEN
1503 PO_LOG.proc_begin(d_module);
1504 PO_LOG.proc_begin(d_module, 'p_entity_id', p_entity_id);
1505 PO_LOG.proc_begin(d_module, 'p_entity_type', p_entity_type);
1506 PO_LOG.proc_begin(d_module, 'p_shipid', p_shipid);
1507 END IF;
1508
1509 d_progress := 10;
1510
1511 -- Requisition Line Supply is created in Cancel PO Routine. We just remove
1512 -- the existing PO Supply
1513
1514 IF (p_entity_type = 'PO') THEN
1515
1516 l_return_value := delete_supply(
1517 p_entity_id => p_entity_id
1518 , p_entity_type => 'PO'
1519 );
1520
1521 ELSIF (p_entity_type = 'PO LINE') THEN
1522
1523 l_return_value := update_supply(
1524 p_entity_id => p_entity_id
1525 , p_entity_type => 'PO LINE'
1526 );
1527
1528 ELSIF (p_entity_type = 'PO SHIPMENT') THEN
1529
1530 l_return_value := update_supply(
1531 p_entity_id => p_entity_id
1532 , p_entity_type => 'PO SHIPMENT'
1533 );
1534
1535 ELSIF (p_entity_type = 'RELEASE') THEN
1536
1537 l_return_value := delete_supply(
1538 p_entity_id => p_entity_id
1539 , p_entity_type => 'RELEASE'
1540 );
1541
1542 ELSIF (p_entity_type = 'RELEASE SHIPMENT') THEN
1543
1544 l_return_value := update_supply(
1545 p_entity_id => p_entity_id
1546 , p_entity_type => 'RELEASE SHIPMENT'
1547 , p_shipid => p_shipid
1548 );
1549
1550 END IF;
1551
1552 IF (PO_LOG.d_proc) THEN
1553 PO_LOG.proc_return(d_module, l_return_value);
1554 PO_LOG.proc_end(d_module);
1555 END IF;
1556
1557 return (l_return_value);
1558
1559 EXCEPTION
1560
1561 WHEN others THEN
1562 IF (PO_LOG.d_exc) THEN
1563 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
1564 END IF;
1565
1566 return(FALSE);
1567
1568 END cancel_supply;
1569
1570
1571
1572 /* ----------------------------------------------------------------------- */
1573 /* */
1574 /* Cancel Planned Release or Planned Shipment */
1575 /* */
1576 /* Update mtl_supply for Cancel Planned Release or Cancel Planned */
1577 /* Release Shipment Action */
1578 /* */
1579 /* Cancellation of Planned Release and Planned Shipment is based on */
1580 /* Entity Type */
1581 /* */
1582 /* Entity Type Action */
1583 /* ----------- ------------------------------------------ */
1584 /* RELEASE Cancel Planned Release */
1585 /* */
1586 /* RELEASE SHIPMENT Cancel Planned Release Shipment */
1587 /* */
1588 /* ----------------------------------------------------------------------- */
1589
1590
1591 FUNCTION cancel_planned(
1592 p_entity_id IN NUMBER
1593 , p_entity_type IN VARCHAR2
1594 , p_shipid IN NUMBER DEFAULT 0
1595 , p_recreate_flag IN BOOLEAN
1596 ) RETURN BOOLEAN
1597 IS
1598
1599 l_supply_flag BOOLEAN := TRUE;
1600
1601 l_headid po_releases.po_header_id%TYPE;
1602
1603 /*Bug 4537860:Hit the _all tables instead of the striped views.*/
1604
1605 CURSOR rel(release_id NUMBER)
1606 IS
1607 SELECT por.po_header_id
1608 FROM po_releases_all por
1609 WHERE por.po_release_id = release_id;
1610
1611 d_module VARCHAR2(70) := 'po.plsql.PO_SUPPLY.cancel_planned';
1612 d_progress NUMBER;
1613
1614 l_return_value BOOLEAN := FALSE;
1615
1616 BEGIN
1617
1618 IF (PO_LOG.d_proc) THEN
1619 PO_LOG.proc_begin(d_module);
1620 PO_LOG.proc_begin(d_module, 'p_entity_id', p_entity_id);
1621 PO_LOG.proc_begin(d_module, 'p_entity_type', p_entity_type);
1622 PO_LOG.proc_begin(d_module, 'p_shipid', p_shipid);
1623 PO_LOG.proc_begin(d_module, 'p_recreate_flag', p_recreate_flag);
1624 END IF;
1625
1626 d_progress := 10;
1627
1628 BEGIN
1629
1630 -- Remove Planned PO Supply
1631
1632 IF (p_entity_type = 'RELEASE') THEN
1633
1634 d_progress := 20;
1635
1636 l_return_value := delete_supply(
1637 p_entity_id => p_entity_id
1638 , p_entity_type => 'RELEASE'
1639 );
1640
1641 IF (NOT l_return_value) THEN
1642 IF (PO_LOG.d_stmt) THEN
1643 PO_LOG.stmt(d_module, d_progress, 'delete_supply not successful');
1644 END IF;
1645
1646 RAISE PO_CORE_S.g_early_return_exc;
1647 END IF;
1648
1649
1650 IF (p_recreate_flag) THEN
1651
1652 d_progress := 30;
1653
1654 -- Add to existing Planned PO Supply
1655
1656 l_return_value := update_planned_po(
1657 p_docid => p_entity_id
1658 , p_entity_type => 'ADD PLANNED'
1659 , p_supply_flag => l_supply_flag
1660 );
1661
1662 IF (NOT l_return_value) THEN
1663 IF (PO_LOG.d_stmt) THEN
1664 PO_LOG.stmt(d_module, d_progress, 'update_planned_po not successful');
1665 END IF;
1666
1667 RAISE PO_CORE_S.g_early_return_exc;
1668 END IF;
1669
1670 -- Insert Planned PO Supply if it does not exist
1671
1672 IF (NOT l_supply_flag) THEN
1673
1674 d_progress := 40;
1675
1676 OPEN rel(p_entity_id);
1677 FETCH rel INTO l_headid;
1678
1679 d_progress := 50;
1680
1681 IF (rel%NOTFOUND) THEN
1682
1683 IF (PO_LOG.d_stmt) THEN
1684 PO_LOG.stmt(d_module, d_progress, 'no rows in release cursor');
1685 END IF;
1686
1687 l_return_value := TRUE;
1688 RAISE PO_CORE_S.g_early_return_exc;
1689 END IF;
1690
1691 CLOSE rel;
1692
1693 d_progress := 60;
1694
1695 l_return_value := create_po_supply(
1696 p_entity_id => p_entity_id
1697 , p_entity_type => 'PO'
1698 );
1699
1700 END IF; -- if (NOT l_supply_flag)
1701
1702 END IF; -- if (p_recreate_flag)
1703
1704 ELSIF (p_entity_type = 'RELEASE SHIPMENT') THEN
1705
1706 d_progress := 70;
1707
1708 l_return_value := update_supply(
1709 p_entity_id => p_entity_id
1710 , p_entity_type => 'RELEASE SHIPMENT'
1711 , p_shipid => p_shipid
1712 );
1713
1714 IF (NOT l_return_value) THEN
1715 IF (PO_LOG.d_stmt) THEN
1716 PO_LOG.stmt(d_module, d_progress, 'update_supply not successful');
1717 END IF;
1718
1719 RAISE PO_CORE_S.g_early_return_exc;
1720 END IF;
1721
1722
1723 IF (p_recreate_flag) THEN
1724
1725 d_progress := 80;
1726
1727 -- Add to existing Planned PO Supply
1728
1729 l_return_value := update_planned_po(
1730 p_docid => p_entity_id
1731 , p_shipid => p_shipid
1732 , p_entity_type => 'UPDATE PLANNED'
1733 , p_supply_flag => l_supply_flag
1734 );
1735
1736 IF (NOT l_return_value) THEN
1737 IF (PO_LOG.d_stmt) THEN
1738 PO_LOG.stmt(d_module, d_progress, 'update_planned_po not successful');
1739 END IF;
1740
1741 RAISE PO_CORE_S.g_early_return_exc;
1742 END IF;
1743
1744
1745 -- Insert Planned PO Supply if it does not exist
1746
1747 IF (NOT l_supply_flag) THEN
1748
1749 d_progress := 90;
1750
1751 l_return_value := create_po_supply(
1752 p_entity_id => p_shipid
1753 , p_entity_type => 'PO SHIPMENT'
1754 );
1755
1756 END IF; -- if (NOT l_supply_flag)
1757
1758 END IF; -- if (p_recreate_flag)
1759
1760 END IF; -- if (p_entity_type = ...)
1761
1762 EXCEPTION
1763 WHEN PO_CORE_S.g_early_return_exc THEN
1764 NULL;
1765 END;
1766
1767 IF (rel%ISOPEN) THEN
1768 CLOSE rel;
1769 END IF;
1770
1771 IF (PO_LOG.d_proc) THEN
1772 PO_LOG.proc_return(d_module, l_return_value);
1773 PO_LOG.proc_end(d_module);
1774 END IF;
1775
1776 return (l_return_value);
1777
1778 EXCEPTION
1779 WHEN others THEN
1780 IF (PO_LOG.d_exc) THEN
1781 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
1782 END IF;
1783
1784 IF (rel%ISOPEN) THEN
1785 CLOSE rel;
1786 END IF;
1787
1788 return(FALSE);
1789
1790 END cancel_planned;
1791
1792
1793
1794 /* ----------------------------------------------------------------------- */
1795 /* */
1796 /* Maintain mtl_supply */
1797 /* */
1798 /* ----------------------------------------------------------------------- */
1799
1800 FUNCTION maintain_mtl_supply RETURN BOOLEAN IS
1801
1802 l_uom mtl_system_items.primary_unit_of_measure%TYPE;
1803 l_lead_time mtl_system_items.postprocessing_lead_time%TYPE;
1804 l_pri_qty mtl_supply.to_org_primary_quantity%TYPE;
1805
1806
1807 -- <Doc Manager Rewrite R12>: Brought in from Pro*C to cursor sup2:
1808 -- 1. index hint
1809 -- 2. order by
1810
1811 /* Bug# 7368176
1812 * Added an extra FOR UPDATE clause in the below cursor to avoid the
1813 * deadlock scenario.
1814 */
1815
1816 CURSOR sup2
1817 IS
1818 SELECT /*+ index(mtl_supply MTL_SUPPLY_N10) */
1819 quantity
1820 , unit_of_measure
1821 , nvl(item_id, -1) item_id
1822 , from_organization_id
1823 , to_organization_id
1824 , receipt_date
1825 , rowid
1826 FROM mtl_supply
1827 WHERE change_flag = 'Y'
1828 ORDER BY DECODE (supply_type_code,
1829 'REQ', 1,
1830 'PO', 2,
1831 'SHIPMENT', 3,
1832 'RECEIVING', 4,
1833 5), QUANTITY
1834 FOR UPDATE;
1835
1836 CURSOR uom(from_uom VARCHAR2)
1837 IS
1838 SELECT muom.unit_of_measure
1839 , NULL
1840 FROM mtl_units_of_measure muom
1841 , mtl_units_of_measure tuom
1842 WHERE tuom.unit_of_measure = from_uom
1843 AND tuom.uom_class = muom.uom_class
1844 AND muom.base_uom_flag = 'Y';
1845
1846 CURSOR uom_itemid(item_id NUMBER, to_org NUMBER)
1847 IS
1848 SELECT primary_unit_of_measure
1849 , postprocessing_lead_time
1850 FROM mtl_system_items
1851 WHERE inventory_item_id = item_id
1852 AND organization_id = to_org;
1853
1854 d_module VARCHAR2(70) := 'po.plsql.PO_SUPPLY.maintain_mtl_supply';
1855 d_progress NUMBER;
1856
1857 BEGIN
1858
1859 IF (PO_LOG.d_proc) THEN
1860 PO_LOG.proc_begin(d_module);
1861 END IF;
1862
1863 d_progress := 10;
1864
1865 FOR c_sup2 IN sup2
1866 LOOP
1867
1868 IF (c_sup2.quantity = 0) THEN
1869
1870 d_progress := 20;
1871
1872 DELETE FROM mtl_supply
1873 WHERE rowid = c_sup2.rowid;
1874
1875 d_progress := 25;
1876 IF (PO_LOG.d_stmt) THEN
1877 PO_LOG.stmt(d_module, d_progress, 'Deleted ' || SQL%ROWCOUNT || ' rows');
1878 END IF;
1879
1880 ELSE
1881
1882 IF (c_sup2.item_id = -1) THEN
1883
1884 -- one time item
1885
1886 d_progress := 30;
1887
1888 OPEN uom(c_sup2.unit_of_measure);
1889 FETCH uom INTO l_uom, l_lead_time;
1890 CLOSE uom;
1891
1892 ELSE
1893
1894 d_progress := 40;
1895
1896 OPEN uom_itemid(c_sup2.item_id, c_sup2.to_organization_id);
1897 FETCH uom_itemid INTO l_uom, l_lead_time;
1898 CLOSE uom_itemid;
1899
1900 END IF; -- if (c_sup2.item_id = -1)
1901
1902 d_progress := 50;
1903
1904 IF (PO_LOG.d_stmt) THEN
1905 PO_LOG.stmt(d_module, d_progress, 'l_uom', l_uom);
1906 PO_LOG.stmt(d_module, d_progress, 'l_lead_time', l_lead_time);
1907 PO_LOG.stmt(d_module, d_progress, 'c_sup2.item_id', c_sup2.item_id);
1908 PO_LOG.stmt(d_module, d_progress, 'c_sup2.quantity', c_sup2.quantity);
1909 PO_LOG.stmt(d_module, d_progress, 'c_sup2.unit_of_measure', c_sup2.unit_of_measure);
1910 END IF;
1911
1912 l_pri_qty := INV_CONVERT.INV_UM_CONVERT(
1913 item_id => c_sup2.item_id
1914 , precision => 5
1915 , from_quantity => c_sup2.quantity
1916 , from_unit => NULL
1917 , to_unit => NULL
1918 , from_name => c_sup2.unit_of_measure
1919 , to_name => l_uom
1920 );
1921
1922 d_progress := 60;
1923
1924 IF (PO_LOG.d_stmt) THEN
1925 PO_LOG.stmt(d_module, d_progress, 'l_pri_qty', l_pri_qty);
1926 END IF;
1927
1928
1929 UPDATE mtl_supply
1930 SET to_org_primary_quantity = l_pri_qty
1931 , to_org_primary_uom = l_uom
1932 , change_flag = null
1933 , change_type = null
1934 , expected_delivery_date =
1935 DECODE(c_sup2.item_id, -1, to_date(NULL),
1936 c_sup2.receipt_date + NVL(l_lead_time, 0))
1937 WHERE rowid = c_sup2.rowid;
1938
1939 d_progress := 70;
1940 IF (PO_LOG.d_stmt) THEN
1941 PO_LOG.stmt(d_module, d_progress, 'Updated ' || SQL%ROWCOUNT || ' rows');
1942 END IF;
1943
1944 END IF; -- if (c_sup2.quantity = 0)
1945
1946 END LOOP;
1947
1948 IF (PO_LOG.d_proc) THEN
1949 PO_LOG.proc_return(d_module, TRUE);
1950 PO_LOG.proc_end(d_module);
1951 END IF;
1952
1953 return(TRUE);
1954
1955 EXCEPTION
1956
1957 WHEN others THEN
1958
1959 IF (PO_LOG.d_exc) THEN
1960 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
1961 END IF;
1962
1963 IF uom%ISOPEN THEN
1964 close uom;
1965 END IF;
1966
1967 IF uom_itemid%ISOPEN THEN
1968 close uom_itemid;
1969 END IF;
1970
1971 return(FALSE);
1972
1973 END maintain_mtl_supply;
1974
1975
1976 /* ----------------------------------------------------------------------- */
1977
1978 -- Approve Requisition
1979
1980 -- Create Requisition Supply for an Approve Requisition Action
1981
1982 /* ----------------------------------------------------------------------- */
1983
1984 FUNCTION approve_req(p_docid IN NUMBER) RETURN BOOLEAN
1985 IS
1986
1987 /*Bug 4537860:Hit the _all tables instead of the striped views.*/
1988
1989 CURSOR auth_status(header_id NUMBER)
1990 IS
1991 SELECT authorization_status
1992 FROM po_requisition_headers_all
1993 WHERE requisition_header_id = header_id;
1994
1995 l_auth_status po_requisition_headers.authorization_status%TYPE;
1996
1997 d_module VARCHAR2(70) := 'po.plsql.PO_SUPPLY.approve_req';
1998 d_progress NUMBER;
1999
2000 l_return_value BOOLEAN := FALSE;
2001
2002 BEGIN
2003
2004 IF (PO_LOG.d_proc) THEN
2005 PO_LOG.proc_begin(d_module);
2006 PO_LOG.proc_begin(d_module, 'p_docid', p_docid);
2007 END IF;
2008
2009 d_progress := 10;
2010
2011 OPEN auth_status(p_docid);
2012 FETCH auth_status INTO l_auth_status;
2013 CLOSE auth_status;
2014
2015 -- Create Requisition Supply if the Requisition has been Approved
2016
2017 IF (l_auth_status = 'APPROVED') THEN
2018
2019 l_return_value := create_req(
2020 p_entity_id => p_docid
2021 , p_entity_type => 'REQ HDR'
2022 );
2023 ELSE
2024
2025 l_return_value := TRUE;
2026
2027 END IF;
2028
2029 IF (PO_LOG.d_proc) THEN
2030 PO_LOG.proc_return(d_module, l_return_value);
2031 PO_LOG.proc_end(d_module);
2032 END IF;
2033
2034 return(l_return_value);
2035
2036 EXCEPTION
2037
2038 WHEN others THEN
2039
2040 IF (PO_LOG.d_exc) THEN
2041 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
2042 END IF;
2043
2044 IF (auth_status%ISOPEN) THEN
2045 close auth_status;
2046 END IF;
2047
2048 return(FALSE);
2049
2050 END approve_req;
2051
2052 /* ----------------------------------------------------------------------- */
2053
2054 -- Clear Requisition Header, Requisition Lines
2055 /* ----------------------------------------------------------------------- */
2056
2057
2058 FUNCTION remove_req(
2059 p_entity_id IN NUMBER
2060 , p_entity_type IN VARCHAR2
2061 ) RETURN BOOLEAN
2062 IS
2063
2064 d_module VARCHAR2(70) := 'po.plsql.PO_SUPPLY.remove_req';
2065 d_progress NUMBER;
2066
2067 BEGIN
2068
2069 IF (PO_LOG.d_proc) THEN
2070 PO_LOG.proc_begin(d_module);
2071 PO_LOG.proc_begin(d_module, 'p_entity_id', p_entity_id);
2072 PO_LOG.proc_begin(d_module, 'p_entity_type', p_entity_type);
2073 END IF;
2074
2075 d_progress := 10;
2076
2077 IF (p_entity_type = 'REQ HDR') THEN
2078
2079 d_progress := 20;
2080
2081 UPDATE mtl_supply
2082 SET quantity = 0
2083 , change_flag = 'Y'
2084 WHERE supply_type_code = 'REQ'
2085 AND req_header_id = p_entity_id;
2086
2087 ELSIF (p_entity_type = 'REQ LINE') THEN
2088
2089 d_progress := 30;
2090
2091 UPDATE mtl_supply
2092 SET quantity = 0
2093 , change_flag = 'Y'
2094 WHERE supply_type_code = 'REQ'
2095 AND req_line_id = p_entity_id;
2096
2097 END IF; -- if (p_entity_type = ...)
2098
2099 IF (PO_LOG.d_stmt) THEN
2100 PO_LOG.stmt(d_module, d_progress, 'Updated ' || SQL%ROWCOUNT || ' rows');
2101 END IF;
2102
2103 IF (PO_LOG.d_proc) THEN
2104 PO_LOG.proc_return(d_module, TRUE);
2105 PO_LOG.proc_end(d_module);
2106 END IF;
2107
2108 return(TRUE);
2109
2110
2111 EXCEPTION
2112
2113 WHEN others THEN
2114 IF (PO_LOG.d_exc) THEN
2115 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
2116 END IF;
2117
2118 return(FALSE);
2119
2120 END remove_req;
2121
2122 /* ----------------------------------------------------------------------- */
2123
2124 -- Clear Requisition Vendor Sourced Lines
2125
2126 /* ----------------------------------------------------------------------- */
2127
2128
2129 FUNCTION remove_req_vend_lines(p_docid IN NUMBER) RETURN BOOLEAN IS
2130
2131 d_module VARCHAR2(70) := 'po.plsql.PO_SUPPLY.remove_req_vend_lines';
2132 d_progress NUMBER;
2133
2134 BEGIN
2135
2136 IF (PO_LOG.d_proc) THEN
2137 PO_LOG.proc_begin(d_module);
2138 PO_LOG.proc_begin(d_module, 'p_docid', p_docid);
2139 END IF;
2140
2141 d_progress := 10;
2142
2143 /*Bug 4537860:Hit the _all tables instead of the striped views.*/
2144
2145 UPDATE mtl_supply ms
2146 SET ms.quantity = 0
2147 , ms.change_flag = 'Y'
2148 WHERE ms.supply_type_code = 'REQ'
2149 AND ms.req_header_id = p_docid
2150 AND EXISTS
2151 (
2152 SELECT 1
2153 FROM po_requisition_lines_all porl
2154 WHERE porl.source_type_code = 'VENDOR'
2155 AND porl.requisition_line_id = ms.req_line_id
2156 );
2157
2158 IF (PO_LOG.d_stmt) THEN
2159 PO_LOG.stmt(d_module, d_progress, 'Updated ' || SQL%ROWCOUNT || ' rows');
2160 END IF;
2161
2162 IF (PO_LOG.d_proc) THEN
2163 PO_LOG.proc_return(d_module, TRUE);
2164 PO_LOG.proc_end(d_module);
2165 END IF;
2166
2167 return(TRUE);
2168
2169 EXCEPTION
2170
2171 WHEN others THEN
2172 IF (PO_LOG.d_exc) THEN
2173 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
2174 END IF;
2175
2176 return(FALSE);
2177
2178 END remove_req_vend_lines;
2179
2180
2181 /* ----------------------------------------------------------------------- */
2182
2183 -- Create Requisition Header, Line Supply
2184
2185 /* ----------------------------------------------------------------------- */
2186
2187
2188
2189 FUNCTION create_req(
2190 p_entity_id IN NUMBER
2191 , p_entity_type IN VARCHAR2
2192 ) RETURN BOOLEAN
2193 IS
2194
2195 d_module VARCHAR2(70) := 'po.plsql.PO_SUPPLY.create_req';
2196 d_progress NUMBER;
2197
2198 l_return_value BOOLEAN := FALSE;
2199
2200 BEGIN
2201
2202 IF (PO_LOG.d_proc) THEN
2203 PO_LOG.proc_begin(d_module);
2204 PO_LOG.proc_begin(d_module, 'p_entity_id', p_entity_id);
2205 PO_LOG.proc_begin(d_module, 'p_entity_type', p_entity_type);
2206 END IF;
2207
2208 d_progress := 10;
2209
2210
2211 IF (p_entity_type = 'REQ HDR') THEN
2212
2213 d_progress := 20;
2214
2215 -- <Doc Manager Rewrite R12 Start>: From Pro*C
2216
2217 /*Bug 4537860:Hit the _all tables instead of the striped views.*/
2218
2219 DELETE FROM mtl_supply ms1
2220 WHERE ms1.supply_source_id IN
2221 (
2222 SELECT pl.requisition_line_id
2223 FROM po_requisition_lines_all pl
2224 WHERE pl.requisition_header_id = p_entity_id
2225 AND NVL(pl.modified_by_agent_flag, 'N') <> 'Y'
2226 AND NVL(pl.closed_code, 'OPEN') = 'OPEN'
2227 AND NVL(pl.cancel_flag, 'N') = 'N'
2228 AND pl.line_location_id IS NULL
2229 )
2230 AND ms1.supply_type_code = 'REQ';
2231
2232 IF (PO_LOG.d_stmt) THEN
2233 PO_LOG.stmt(d_module, d_progress, 'Deleted ' || SQL%ROWCOUNT || ' rows');
2234 END IF;
2235
2236 -- <Doc Manager Rewrite R12 End>
2237
2238 d_progress := 30;
2239
2240 /*Bug 4537860:Hit the _all tables instead of the striped views.*/
2241
2242 INSERT INTO mtl_supply
2243 (supply_type_code,
2244 supply_source_id,
2245 last_updated_by,
2246 last_update_date,
2247 last_update_login,
2248 created_by,
2249 creation_date,
2250 req_header_id,
2251 req_line_id,
2252 item_id,
2253 item_revision,
2254 quantity,
2255 unit_of_measure,
2256 receipt_date,
2257 need_by_date,
2258 destination_type_code,
2259 location_id,
2260 from_organization_id,
2261 from_subinventory,
2262 to_organization_id,
2263 to_subinventory,
2264 change_flag)
2265 SELECT 'REQ',
2266 prl.requisition_line_id,
2267 last_updated_by,
2268 last_update_date,
2269 last_update_login,
2270 created_by,
2271 creation_date,
2272 prl.requisition_header_id,
2273 prl.requisition_line_id,
2274 prl.item_id,
2275 decode(prl.source_type_code,'INVENTORY', null,
2276 prl.item_revision),
2277 prl.quantity - ( nvl(prl.QUANTITY_CANCELLED, 0) +
2278 nvl(prl.QUANTITY_DELIVERED, 0) ),
2279 prl.unit_meas_lookup_code,
2280 prl.need_by_date,
2281 prl.need_by_date,
2282 prl.destination_type_code,
2283 prl.deliver_to_location_id,
2284 prl.source_organization_id,
2285 prl.source_subinventory,
2286 prl.destination_organization_id,
2287 prl.destination_subinventory,
2288 'Y'
2289 FROM po_requisition_lines_all prl
2290 WHERE prl.requisition_header_id = p_entity_id
2291 AND nvl(prl.modified_by_agent_flag,'N') <> 'Y'
2292 AND nvl(prl.CLOSED_CODE,'OPEN') = 'OPEN'
2293 AND nvl(prl.CANCEL_FLAG, 'N') = 'N'
2294 -- <Doc Manager Rewrite R12>: Filter out amount basis
2295 AND prl.matching_basis <> 'AMOUNT'
2296 AND prl.line_location_id is null
2297 AND not exists
2298 (SELECT 'supply exists'
2299 FROM mtl_supply ms
2300 WHERE ms.supply_type_code = 'REQ'
2301 AND ms.supply_source_id = prl.requisition_line_id);
2302
2303 ELSIF (p_entity_type = 'REQ LINE') THEN
2304
2305 d_progress := 40;
2306
2307 INSERT INTO mtl_supply
2308 (supply_type_code,
2309 supply_source_id,
2310 last_updated_by,
2311 last_update_date,
2312 last_update_login,
2313 created_by,
2314 creation_date,
2315 req_header_id,
2316 req_line_id,
2317 item_id,
2318 item_revision,
2319 quantity,
2320 unit_of_measure,
2321 receipt_date,
2322 need_by_date,
2323 destination_type_code,
2324 location_id,
2325 from_organization_id,
2326 from_subinventory,
2327 to_organization_id,
2328 to_subinventory,
2329 change_flag)
2330 SELECT 'REQ',
2331 prl.requisition_line_id,
2332 last_updated_by,
2333 last_update_date,
2334 last_update_login,
2335 created_by,
2336 creation_date,
2337 prl.requisition_header_id,
2338 prl.requisition_line_id,
2339 prl.item_id,
2340 decode(prl.source_type_code,'INVENTORY', null,
2341 prl.item_revision),
2342 prl.quantity - ( nvl(prl.QUANTITY_CANCELLED, 0) +
2343 nvl(prl.QUANTITY_DELIVERED, 0) ),
2344 prl.unit_meas_lookup_code,
2345 prl.need_by_date,
2346 prl.need_by_date,
2347 prl.destination_type_code,
2348 prl.deliver_to_location_id,
2349 prl.source_organization_id,
2350 prl.source_subinventory,
2351 prl.destination_organization_id,
2352 prl.destination_subinventory,
2353 'Y'
2354 FROM po_requisition_lines_all prl
2355 WHERE prl.requisition_line_id = p_entity_id
2356 AND nvl(prl.modified_by_agent_flag,'N') <> 'Y'
2357 AND nvl(prl.CLOSED_CODE, 'OPEN') = 'OPEN'
2358 AND nvl(prl.CANCEL_FLAG, 'N') = 'N'
2359 AND prl.line_location_id IS NULL
2360 -- <Doc Manager Rewrite R12 Start>: Add filters from Pro*C
2361 AND prl.matching_basis <> 'AMOUNT'
2362 AND NOT EXISTS
2363 (
2364 SELECT 'supply exists'
2365 FROM mtl_supply
2366 WHERE supply_type_code = 'REQ'
2367 AND supply_source_id = prl.requisition_line_id
2368 );
2369 -- <Doc Manager Rewrite R12 End>
2370
2371 END IF; -- if p_entity_type = ...
2372
2373 d_progress := 100;
2374
2375 IF (PO_LOG.d_stmt) THEN
2376 PO_LOG.stmt(d_module, d_progress, 'Inserted ' || SQL%ROWCOUNT || ' rows');
2377 END IF;
2378
2379 -- <Doc Manager Rewrite R12>: After analysis, no rows is OK, not error.
2380 -- This is to handle services lines.
2381
2382 l_return_value := TRUE;
2383
2384 IF (PO_LOG.d_proc) THEN
2385 PO_LOG.proc_return(d_module, l_return_value);
2386 PO_LOG.proc_end(d_module);
2387 END IF;
2388
2389 return(l_return_value);
2390
2391 EXCEPTION
2392
2393 WHEN others THEN
2394
2395 IF (PO_LOG.d_exc) THEN
2396 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
2397 END IF;
2398
2399 return(FALSE);
2400
2401 END create_req;
2402
2403
2404 /* ----------------------------------------------------------------------- */
2405
2406 -- Maintain mtl_supply for Explode or Multisource Action
2407
2408 /* ----------------------------------------------------------------------- */
2409
2410 FUNCTION explode(p_lineid IN NUMBER) RETURN BOOLEAN IS
2411
2412 d_module VARCHAR2(70) := 'po.plsql.PO_SUPPLY.explode';
2413 d_progress NUMBER;
2414
2415 l_return_value BOOLEAN := FALSE;
2416
2417 BEGIN
2418
2419 IF (PO_LOG.d_proc) THEN
2420 PO_LOG.proc_begin(d_module);
2421 PO_LOG.proc_begin(d_module, 'p_lineid', p_lineid);
2422 END IF;
2423
2424 d_progress := 10;
2425
2426 BEGIN
2427
2428 -- Set the Supply Quantity of Parent to 0
2429
2430 l_return_value := remove_req(
2431 p_entity_id => p_lineid
2432 , p_entity_type => 'REQ LINE'
2433 );
2434
2435 IF (NOT l_return_value) THEN
2436 IF (PO_LOG.d_stmt) THEN
2437 PO_LOG.stmt(d_module, d_progress, 'remove_req not successful');
2438 END IF;
2439
2440 RAISE PO_CORE_S.g_early_return_exc;
2441 END IF;
2442
2443 d_progress := 20;
2444
2445 -- Insert New Supply for each new Line created by the Explode or
2446 -- Multisource Action
2447
2448 /*Bug 4537860:Hit the _all tables instead of the striped views.*/
2449
2450 insert into mtl_supply(supply_type_code,
2451 supply_source_id,
2452 last_updated_by,
2453 last_update_date,
2454 last_update_login,
2455 created_by,
2456 creation_date,
2457 req_header_id,
2458 req_line_id,
2459 item_id,
2460 item_revision,
2461 quantity,
2462 unit_of_measure,
2463 receipt_date,
2464 need_by_date,
2465 destination_type_code,
2466 location_id,
2467 from_organization_id,
2468 from_subinventory,
2469 to_organization_id,
2470 to_subinventory,
2471 change_flag)
2472 select 'REQ',
2473 prl.requisition_line_id,
2474 prl.last_updated_by,
2475 prl.last_update_date,
2476 prl.last_update_login,
2477 prl.created_by,
2478 prl.creation_date,
2479 prl.requisition_header_id,
2480 prl.requisition_line_id,
2481 prl.item_id,
2482 prl.item_revision,
2483 prl.quantity - (nvl(prl.quantity_cancelled, 0) +
2484 nvl(prl.quantity_delivered, 0)),
2485 prl.unit_meas_lookup_code,
2486 prl.need_by_date,
2487 prl.need_by_date,
2488 prl.destination_type_code,
2489 prl.deliver_to_location_id,
2490 prl.source_organization_id,
2491 prl.source_subinventory,
2492 prl.destination_organization_id,
2493 prl.destination_subinventory,
2494 'Y'
2495 from po_requisition_lines_all prl
2496 where prl.requisition_line_id in
2497 (select prl1.requisition_line_id
2498 from po_requisition_lines_all prl1
2499 where prl1.requisition_header_id =
2500 (select prl2.requisition_header_id
2501 from po_requisition_lines_all prl2
2502 where prl2.requisition_line_id = p_lineid
2503 and prl2.modified_by_agent_flag = 'Y'))
2504 and nvl(prl.modified_by_agent_flag, 'N') <> 'Y'
2505 and nvl(prl.closed_code, 'OPEN') = 'OPEN'
2506 and nvl(prl.cancel_flag, 'N') = 'N'
2507 and prl.line_location_id is null
2508 and not exists
2509 (select 'Supply Exists'
2510 from mtl_supply
2511 where supply_type_code = 'REQ'
2512 and supply_source_id = prl.requisition_line_id);
2513
2514
2515 IF (PO_LOG.d_stmt) THEN
2516 PO_LOG.stmt(d_module, d_progress, 'Exploded ' || SQL%ROWCOUNT || ' rows');
2517 END IF;
2518
2519 l_return_value := TRUE;
2520
2521 EXCEPTION
2522 WHEN PO_CORE_S.g_early_return_exc THEN
2523 NULL;
2524 END;
2525
2526 IF (PO_LOG.d_proc) THEN
2527 PO_LOG.proc_return(d_module, l_return_value);
2528 PO_LOG.proc_end(d_module);
2529 END IF;
2530
2531 return(l_return_value);
2532
2533 EXCEPTION
2534
2535 WHEN others THEN
2536
2537 IF (PO_LOG.d_exc) THEN
2538 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
2539 END IF;
2540
2541 return(FALSE);
2542
2543 END explode;
2544
2545 /* ----------------------------------------------------------------------- */
2546
2547 -- Updates Requisition Quantity in mtl_supply
2548
2549 /* ----------------------------------------------------------------------- */
2550
2551
2552 FUNCTION update_req_line_qty(
2553 p_lineid IN NUMBER
2554 , p_qty IN NUMBER
2555 ) RETURN BOOLEAN
2556 IS
2557
2558 d_module VARCHAR2(70) := 'po.plsql.PO_SUPPLY.update_req_line_qty';
2559 d_progress NUMBER;
2560
2561 BEGIN
2562
2563 IF (PO_LOG.d_proc) THEN
2564 PO_LOG.proc_begin(d_module);
2565 PO_LOG.proc_begin(d_module, 'p_lineid', p_lineid);
2566 PO_LOG.proc_begin(d_module, 'p_qty', p_qty);
2567 END IF;
2568
2569 d_progress := 10;
2570
2571 UPDATE mtl_supply
2572 SET quantity = p_qty
2573 , change_flag = 'Y'
2574 WHERE supply_type_code = 'REQ'
2575 AND req_line_id = p_lineid;
2576
2577 IF (PO_LOG.d_stmt) THEN
2578 PO_LOG.stmt(d_module, d_progress, 'Updated ' || SQL%ROWCOUNT || ' rows');
2579 END IF;
2580
2581
2582 IF (PO_LOG.d_proc) THEN
2583 PO_LOG.proc_return(d_module, TRUE);
2584 PO_LOG.proc_end(d_module);
2585 END IF;
2586
2587 return(TRUE);
2588
2589 EXCEPTION
2590
2591 WHEN others THEN
2592
2593 IF (PO_LOG.d_exc) THEN
2594 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
2595 END IF;
2596
2597 return(FALSE);
2598
2599 END update_req_line_qty;
2600
2601 /* ----------------------------------------------------------------------- */
2602
2603 -- Updates Receipt Date in mtl_supply
2604
2605 /* ----------------------------------------------------------------------- */
2606
2607 FUNCTION update_req_line_date(
2608 p_lineid IN NUMBER
2609 , p_receipt_date IN DATE
2610 ) RETURN BOOLEAN
2611 IS
2612
2613 d_module VARCHAR2(70) := 'po.plsql.PO_SUPPLY.update_req_line_date';
2614 d_progress NUMBER;
2615
2616 BEGIN
2617
2618 IF (PO_LOG.d_proc) THEN
2619 PO_LOG.proc_begin(d_module);
2620 PO_LOG.proc_begin(d_module, 'p_lineid', p_lineid);
2621 PO_LOG.proc_begin(d_module, 'p_receipt_date', p_receipt_date);
2622 END IF;
2623
2624 d_progress := 10;
2625
2626 UPDATE mtl_supply
2627 SET receipt_date = p_receipt_date
2628 , need_by_date = p_receipt_date -- Bug 3443313
2629 , change_flag = 'Y'
2630 WHERE supply_type_code = 'REQ'
2631 AND req_line_id = p_lineid;
2632
2633 IF (PO_LOG.d_stmt) THEN
2634 PO_LOG.stmt(d_module, d_progress, 'Updated ' || SQL%ROWCOUNT || ' rows');
2635 END IF;
2636
2637
2638 IF (PO_LOG.d_proc) THEN
2639 PO_LOG.proc_return(d_module, TRUE);
2640 PO_LOG.proc_end(d_module);
2641 END IF;
2642
2643 return(TRUE);
2644
2645 EXCEPTION
2646
2647 WHEN others THEN
2648
2649 IF (PO_LOG.d_exc) THEN
2650 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
2651 END IF;
2652
2653 return(FALSE);
2654
2655 END update_req_line_date;
2656
2657
2658
2659 /* ----------------------------------------------------------------------- */
2660
2661 -- Update Planned PO, Planned PO Shipment Supply
2662
2663 -- Update of Planned PO is based on Entity Type
2664 --
2665 -- Entity Type Action
2666 -- ----------- ------------------------------------------
2667 -- <Doc Manager Rewrite R12>: REMOVE PLANNED is not used anywhere; removed
2668 --
2669 -- UPDATE PLANNED Update Quantity in mtl_supply
2670 --
2671 -- ADD PLANNED Update Quantity in mtl_supply
2672 --
2673
2674 /* ----------------------------------------------------------------------- */
2675
2676 FUNCTION update_planned_po(
2677 p_docid IN NUMBER
2678 , p_shipid IN NUMBER DEFAULT 0
2679 , p_entity_type IN VARCHAR2
2680 , p_supply_flag IN OUT NOCOPY BOOLEAN
2681 ) RETURN BOOLEAN
2682 IS
2683
2684 d_module VARCHAR2(70) := 'po.plsql.PO_SUPPLY.update_planned_po';
2685 d_progress NUMBER;
2686
2687 l_ppo_dist_id_tbl po_tbl_number;
2688 l_ppo_dist_qty_tbl po_tbl_number;
2689
2690
2691 BEGIN
2692
2693 IF (PO_LOG.d_proc) THEN
2694 PO_LOG.proc_begin(d_module);
2695 PO_LOG.proc_begin(d_module, 'p_docid', p_docid);
2696 PO_LOG.proc_begin(d_module, 'p_shipid', p_shipid);
2697 PO_LOG.proc_begin(d_module, 'p_entity_type', p_entity_type);
2698 PO_LOG.proc_begin(d_module, 'p_supply_flag', p_supply_flag);
2699 END IF;
2700
2701 d_progress := 10;
2702
2703 IF (p_entity_type = 'UPDATE PLANNED') THEN
2704
2705 d_progress := 20;
2706
2707 -- <Doc Manager Rewrite R12>: Use logic from Pro*C
2708 /*Bug 4537860:Hit the _all tables instead of the striped views.*/
2709
2710 UPDATE mtl_supply ms
2711 SET ms.quantity =
2712 (
2713 SELECT ms.quantity +
2714 NVL( sum(nvl(pd.quantity_cancelled,0)),0)
2715 FROM po_distributions_all pd
2716 WHERE pd.po_release_id = p_docid
2717 AND pd.line_location_id = p_shipid
2718 AND pd.source_distribution_id = ms.supply_source_id
2719 )
2720 , ms.change_flag = 'Y'
2721 WHERE ms.supply_type_code = 'PO'
2722 AND ms.po_line_location_id =
2723 (
2724 SELECT poll. source_shipment_id
2725 FROM po_line_locations_all poll
2726 WHERE poll.line_location_id = p_shipid
2727 );
2728
2729 IF (SQL%NOTFOUND) THEN
2730 p_supply_flag := FALSE;
2731 ELSE
2732 p_supply_flag := TRUE;
2733 END IF;
2734
2735 ELSIF (p_entity_type = 'ADD PLANNED') THEN
2736
2737 -- <Doc Manager Rewrite R12>: Use logic from Pro*C and
2738 -- use bulk processing to avoid unnecessary nested cursors
2739
2740 /*Bug 4537860:Hit the _all tables instead of the striped views.*/
2741
2742 d_progress := 30;
2743
2744 SELECT pod.source_distribution_id, pod.quantity_ordered
2745 BULK COLLECT INTO l_ppo_dist_id_tbl, l_ppo_dist_qty_tbl
2746 FROM po_distributions_all pod
2747 WHERE pod.po_release_id = p_docid
2748 AND (pod.po_line_id IS NOT NULL AND pod.line_location_id IS NOT NULL);
2749
2750 d_progress := 40;
2751
2752 FORALL i IN 1..l_ppo_dist_id_tbl.COUNT
2753 UPDATE mtl_supply mts
2754 SET mts.quantity = l_ppo_dist_qty_tbl(i) -
2755 (
2756 SELECT NVL(sum(pod.quantity_ordered -
2757 NVL(pod.quantity_cancelled, 0)), 0)
2758 FROM po_distributions_all pod
2759 WHERE pod.source_distribution_id = l_ppo_dist_id_tbl(i)
2760 AND pod.po_line_id IS NOT NULL
2761 AND pod.line_location_id IS NOT NULL
2762 )
2763 , mts.change_flag = 'Y'
2764 WHERE mts.po_distribution_id = l_ppo_dist_id_tbl(i);
2765
2766 d_progress := 50;
2767
2768 IF ((l_ppo_dist_id_tbl.COUNT = 0) OR (SQL%NOTFOUND)) THEN
2769 p_supply_flag := FALSE;
2770 ELSE
2771 p_supply_flag := TRUE;
2772 END IF;
2773
2774 END IF;
2775
2776 IF (PO_LOG.d_stmt) THEN
2777 PO_LOG.stmt(d_module, d_progress, 'Updated ' || SQL%ROWCOUNT || ' rows');
2778 END IF;
2779
2780 IF (PO_LOG.d_proc) THEN
2781 PO_LOG.proc_return(d_module, TRUE);
2782 PO_LOG.proc_end(d_module, 'p_supply_flag', p_supply_flag);
2783 PO_LOG.proc_end(d_module);
2784 END IF;
2785
2786 return(TRUE);
2787
2788 EXCEPTION
2789
2790 WHEN others THEN
2791
2792 IF (PO_LOG.d_exc) THEN
2793 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
2794 END IF;
2795
2796 return(FALSE);
2797
2798 END update_planned_po;
2799
2800
2801 /* ----------------------------------------------------------------------- */
2802 -- Obsolete debug method
2803 /* ----------------------------------------------------------------------- */
2804 FUNCTION get_debug RETURN VARCHAR2 IS
2805 BEGIN
2806 return NULL;
2807 END get_debug;
2808
2809
2810
2811 END PO_SUPPLY;
2812