1 PACKAGE BODY RCV_QUANTITIES_S AS
2 /* $Header: RCVTXQUB.pls 120.16.12020000.2 2012/07/10 09:29:37 ptkumar ship $*/
3
4 g_asn_debug VARCHAR2(1) := asn_debug.is_debug_on; -- Bug 9152790
5
6 /*
7 ** Forward declarations of all the local procedures and functions called
8 ** by get_available_quantity().
9 */
10
11 PROCEDURE get_receive_quantity(p_parent_id IN NUMBER,
12 p_receipt_source_code IN VARCHAR2,
13 p_available_quantity IN OUT NOCOPY NUMBER,
14 p_tolerable_quantity IN OUT NOCOPY NUMBER,
15 p_unit_of_measure IN OUT NOCOPY VARCHAR2,
16 p_secondary_available_qty IN OUT NOCOPY NUMBER );
17
18 PROCEDURE get_po_quantity(p_line_location_id IN NUMBER,
19 p_available_quantity IN OUT NOCOPY NUMBER,
20 p_tolerable_quantity IN OUT NOCOPY NUMBER,
21 p_unit_of_measure IN OUT NOCOPY VARCHAR2,
22 p_secondary_available_qty IN OUT NOCOPY NUMBER );
23
24 PROCEDURE get_rma_quantity(p_oe_order_line_id IN NUMBER,
25 p_available_quantity IN OUT NOCOPY NUMBER,
26 p_tolerable_quantity IN OUT NOCOPY NUMBER,
27 p_unit_of_measure IN OUT NOCOPY VARCHAR2,
28 p_secondary_available_qty IN OUT NOCOPY NUMBER);
29
30 PROCEDURE get_shipment_quantity(p_shipment_line_id IN NUMBER,
31 p_available_quantity IN OUT NOCOPY NUMBER,
32 p_unit_of_measure IN OUT NOCOPY VARCHAR2,
33 p_secondary_available_qty IN OUT NOCOPY NUMBER );
34
35 PROCEDURE get_transaction_quantity(p_transaction_id IN NUMBER,
36 p_available_quantity IN OUT NOCOPY NUMBER,
37 p_unit_of_measure IN OUT NOCOPY VARCHAR2,
38 p_secondary_available_qty IN OUT NOCOPY NUMBER );
39
40 PROCEDURE get_correction_quantity(p_correction_type IN VARCHAR2,
41 p_parent_transaction_type IN VARCHAR2,
42 p_receipt_source_code IN VARCHAR2,
43 p_parent_id IN NUMBER,
44 p_grand_parent_id IN NUMBER,
45 p_available_quantity IN OUT NOCOPY NUMBER,
46 p_tolerable_quantity IN OUT NOCOPY NUMBER,
47 p_unit_of_measure IN OUT NOCOPY VARCHAR2,
48 p_secondary_available_qty IN OUT NOCOPY NUMBER );
49
50 PROCEDURE get_deliver_quantity(p_transaction_id IN NUMBER,
51 p_available_quantity IN OUT NOCOPY NUMBER,
52 p_unit_of_measure IN OUT NOCOPY VARCHAR2,
53 p_secondary_available_qty IN OUT NOCOPY NUMBER );
54
55 PROCEDURE get_po_dist_quantity(p_po_distribution_id IN NUMBER,
56 p_available_quantity IN OUT NOCOPY NUMBER,
57 p_tolerable_quantity IN OUT NOCOPY NUMBER, -- 1337787
58 p_unit_of_measure IN OUT NOCOPY VARCHAR2);
59
60 PROCEDURE get_rcv_dist_quantity(p_po_distribution_id IN NUMBER,
61 p_transaction_id IN NUMBER,
62 p_available_quantity IN OUT NOCOPY NUMBER,
63 p_unit_of_measure IN OUT NOCOPY VARCHAR2 );
64
65 PROCEDURE get_receive_amount(p_parent_id IN NUMBER,
66 p_receipt_source_code IN VARCHAR2,
67 p_available_amount IN OUT NOCOPY NUMBER,
68 p_tolerable_amount IN OUT NOCOPY NUMBER );
69
70 PROCEDURE get_po_amount(p_line_location_id IN NUMBER,
71 p_available_amount IN OUT NOCOPY NUMBER,
72 p_tolerable_amount IN OUT NOCOPY NUMBER );
73
74 PROCEDURE get_correction_amount(p_correction_type IN VARCHAR2,
75 p_parent_transaction_type IN VARCHAR2,
76 p_receipt_source_code IN VARCHAR2,
77 p_parent_id IN NUMBER,
78 p_grand_parent_id IN NUMBER,
79 p_available_amount IN OUT NOCOPY NUMBER,
80 p_tolerable_amount IN OUT NOCOPY NUMBER );
81
82 PROCEDURE get_deliver_amount(p_transaction_id IN NUMBER,
83 p_available_amount IN OUT NOCOPY NUMBER );
84
85 PROCEDURE get_transaction_amount(p_transaction_id IN NUMBER,
86 p_available_amount IN OUT NOCOPY NUMBER );
87
88 PROCEDURE get_po_dist_amount(p_po_distribution_id IN NUMBER,
89 p_available_amount IN OUT NOCOPY NUMBER,
90 p_tolerable_amount IN OUT NOCOPY NUMBER);
91
92 /*===========================================================================
93
94 PROCEDURE NAME: get_available_quantity()
95
96 ALGORITHM :
97
98 The following is the overall stucture for the entire quantity validation
99 program. Details of each procedure are given in the individual procedures
100 themselves. This structure explodes all functions to all the relevant
101 functions calls within them.
102 e.g.
103 get_receive_quantity() is exploded to show that it calls get_po_quantity()
104 and get_shipment_quantity().
105
106 IF (p_transaction_type IN ('RECEIVE', 'MATCH')) THEN
107
108 get_receive_quantity();
109
110 IF (p_receipt_source_code = 'VENDOR') THEN
111
112 get_po_quantity();
113
114 ELSIF (p_receipt_source_code in ('INVENTORY', 'INTERNAL ORDER')) THEN
115
116 get_shipment_quantity();
117
118 ELSE
119
120 return invalid receipt_source_code.
121
122 END IF;
123
124 ELSIF (p_transaction_type IN ('TRANSFER', 'INSPECT', 'DELIVER')) THEN
125
126 get_transaction_quantity();
127
128 ELSIF (p_transaction_type IN ('CORRECT', 'RETURN TO VENDOR',
129 'RETURN TO RECEIVING')) THEN
130
131 get_correction_quantity();
132
133 IF p_correction_type = 'NEGATIVE' THEN
134
135 IF (p_parent_transaction_type IN ('UNORDERED', 'RECEIVE', 'MATCH',
136 'TRANSFER', 'ACCEPT', 'REJECT')) THEN
137
138 get_transaction_quantity();
139
140 ELSIF (p_parent_transaction_type IN
141 ('RETURN TO VENDOR', 'DELIVER')) THEN
142
143 get_deliver_quantity();
144
145 ELSE
146
147 return invalid parent transaction type.
148
149 END IF;
150
151 ELSIF p_correction_type = 'POSITIVE' THEN
152
153 IF (p_parent_transaction_type IN ('RECEIVE', 'MATCH')) THEN
154
155 get_receive_quantity();
156
157 ELSIF (p_parent_transaction_type IN ('TRANSFER', 'ACCEPT',
158 'REJECT', 'DELIVER', 'RETURN TO VENDOR')) THEN
159
160 get_transaction_quantity();
161
162 ELSE
163
164 raise invalid parent transaction type.
165
166 END IF;
167
168 END IF;
169
170 ELSE
171
172 return invalid transaction type.
173
174 END IF;
175
176 Please fix any bug in both the get_available_quantity procedures since this is overloaded.
177 ===========================================================================================*/
178
179 PROCEDURE get_available_quantity(p_transaction_type IN VARCHAR2,
180 p_parent_id IN NUMBER,
181 p_receipt_source_code IN VARCHAR2,
182 p_parent_transaction_type IN VARCHAR2,
183 p_grand_parent_id IN NUMBER,
184 p_correction_type IN VARCHAR2,
185 p_available_quantity IN OUT NOCOPY NUMBER,
186 p_tolerable_quantity IN OUT NOCOPY NUMBER,
187 p_unit_of_measure IN OUT NOCOPY VARCHAR2) IS
188
189 x_progress VARCHAR2(3) := NULL;
190
191 invalid_transaction_type EXCEPTION;
192 x_secondary_available_qty NUMBER :=0;
193
194 BEGIN
195
196 x_progress := '005';
197
198 IF (p_transaction_type IN ('RECEIVE', 'MATCH')) THEN
199
200 get_receive_quantity(p_parent_id, p_receipt_source_code,
201 p_available_quantity, p_tolerable_quantity,
202 p_unit_of_measure, x_secondary_available_qty);
203
204 ELSIF (p_transaction_type IN ('TRANSFER', 'INSPECT', 'DELIVER')) THEN
205
206 get_transaction_quantity(p_parent_id, p_available_quantity,
207 p_unit_of_measure,x_secondary_available_qty);
208
209 ELSIF (p_transaction_type IN ('CORRECT', 'RETURN TO VENDOR',
210 'RETURN TO CUSTOMER',
211 'RETURN TO RECEIVING')) THEN
212
213 get_correction_quantity(p_correction_type, p_parent_transaction_type,
214 p_receipt_source_code, p_parent_id,
215 p_grand_parent_id, p_available_quantity,
216 p_tolerable_quantity,p_unit_of_measure,x_secondary_available_qty);
217
218 ELSIF (p_transaction_type = 'DIRECT RECEIPT') THEN
219
220 get_po_dist_quantity(p_parent_id, p_available_quantity,
221 p_tolerable_quantity, p_unit_of_measure);
222
223 ELSIF (p_transaction_type = 'STANDARD DELIVER') THEN
224
225 get_rcv_dist_quantity(p_parent_id, p_grand_parent_id,
226 p_available_quantity, p_unit_of_measure);
227
228 ELSE
229
230 /*
231 ** The function was called with the wrong p_transaction_type
232 ** parameter. Raise an invalid transaction type exception.
233 */
234
235 RAISE invalid_transaction_type;
236
237 END IF;
238
239 EXCEPTION
240
241 WHEN invalid_transaction_type THEN
242
243 /*
244 ** debug - need to define a new message and also need to understand
245 ** how exactly to handle application error messages. A call to
246 ** some generic API is needed.
247 */
248
249 RAISE;
250
251 WHEN OTHERS THEN
252
253 po_message_s.sql_error('get_available_quantity', x_progress, sqlcode);
254
255 RAISE;
256
257 END get_available_quantity;
258
259 /*==========================================================================
260 Over loaded Procedure
261 ============================================================================
262 This procedure performs the same funciton as the one above
263 It is overloaded for comon receiving project as this is called from
264 POXPOVPO2.pld to calculate quantity_due and in order not to hhave PO library
265 dependednt on a receivcing package this was overloaded.
266 ===========================================================================*/
267
268 PROCEDURE get_available_quantity(p_transaction_type IN VARCHAR2,
269 p_parent_id IN NUMBER,
270 p_receipt_source_code IN VARCHAR2,
271 p_parent_transaction_type IN VARCHAR2,
272 p_grand_parent_id IN NUMBER,
273 p_correction_type IN VARCHAR2,
274 p_available_quantity IN OUT NOCOPY NUMBER,
275 p_tolerable_quantity IN OUT NOCOPY NUMBER,
276 p_unit_of_measure IN OUT NOCOPY VARCHAR2,
277 p_secondary_available_qty IN OUT NOCOPY NUMBER ) IS
278
279 x_progress VARCHAR2(3) := NULL;
280
281 invalid_transaction_type EXCEPTION;
282
283 BEGIN
284
285 x_progress := '005';
286
287 IF (p_transaction_type IN ('RECEIVE', 'MATCH')) THEN
288
289 get_receive_quantity(p_parent_id, p_receipt_source_code,
290 p_available_quantity, p_tolerable_quantity,
291 p_unit_of_measure, p_secondary_available_qty);
292
293 ELSIF (p_transaction_type IN ('TRANSFER', 'INSPECT', 'DELIVER')) THEN
294
295 get_transaction_quantity(p_parent_id, p_available_quantity,
296 p_unit_of_measure,p_secondary_available_qty);
297
298 ELSIF (p_transaction_type IN ('CORRECT', 'RETURN TO VENDOR',
299 'RETURN TO CUSTOMER',
300 'RETURN TO RECEIVING')) THEN
301
302 get_correction_quantity(p_correction_type, p_parent_transaction_type,
303 p_receipt_source_code, p_parent_id,
304 p_grand_parent_id, p_available_quantity,
305 p_tolerable_quantity,p_unit_of_measure,p_secondary_available_qty);
306
307 ELSIF (p_transaction_type = 'DIRECT RECEIPT') THEN
308
309 get_po_dist_quantity(p_parent_id, p_available_quantity,
310 p_tolerable_quantity, p_unit_of_measure);
311
312 ELSIF (p_transaction_type = 'STANDARD DELIVER') THEN
313
314 get_rcv_dist_quantity(p_parent_id, p_grand_parent_id,
315 p_available_quantity, p_unit_of_measure);
316
317 ELSE
318
319 /*
320 ** The function was called with the wrong p_transaction_type
321 ** parameter. Raise an invalid transaction type exception.
322 */
323
324 RAISE invalid_transaction_type;
325
326 END IF;
327
328 EXCEPTION
329
330 WHEN invalid_transaction_type THEN
331
332 /*
333 ** debug - need to define a new message and also need to understand
334 ** how exactly to handle application error messages. A call to
335 ** some generic API is needed.
336 */
337
338 RAISE;
339
340 WHEN OTHERS THEN
341
342 po_message_s.sql_error('get_available_quantity', x_progress, sqlcode);
343
344 RAISE;
345
346 END get_available_quantity;
347 /*===========================================================================*/
348
349 /*===========================================================================
350
351 PROCEDURE NAME: get_available_amount()
352
353 ===========================================================================*/
354 PROCEDURE get_available_amount(p_transaction_type IN VARCHAR2,
355 p_parent_id IN NUMBER,
356 p_receipt_source_code IN VARCHAR2,
357 p_parent_transaction_type IN VARCHAR2,
358 p_grand_parent_id IN NUMBER,
359 p_correction_type IN VARCHAR2,
360 p_available_amount IN OUT NOCOPY NUMBER,
361 p_tolerable_amount IN OUT NOCOPY NUMBER) IS
362
363 x_progress VARCHAR2(3) := NULL;
364
365 invalid_transaction_type EXCEPTION;
366
367 BEGIN
368
369 x_progress := '005';
370
371 IF (g_asn_debug = 'Y') THEN
372 asn_debug.put_line('p_transaction_type '||p_transaction_type);
373 asn_debug.put_line('p_parent_id '||p_parent_id);
374 asn_debug.put_line('p_parent_transaction_type '||p_parent_transaction_type);
375 asn_debug.put_line('p_grand_parent_id '||p_grand_parent_id);
376 asn_debug.put_line('p_correction_type '||p_correction_type);
377 END IF;
378 IF p_transaction_type = 'RECEIVE' THEN
379
380 get_receive_amount(p_parent_id, p_receipt_source_code,
381 p_available_amount, p_tolerable_amount);
382
383 ELSIF p_transaction_type = 'DELIVER' THEN
384
385 get_transaction_amount(p_parent_id, p_available_amount);
386
387 ELSIF (p_transaction_type = 'DIRECT RECEIPT') THEN
388
389 get_po_dist_amount(p_parent_id, p_available_amount,
390 p_tolerable_amount);
391
392 ELSIF p_transaction_type = 'CORRECT' THEN
393
394 get_correction_amount(p_correction_type, p_parent_transaction_type,
395 p_receipt_source_code, p_parent_id,
396 p_grand_parent_id, p_available_amount,
397 p_tolerable_amount);
398
399 ELSE
400
401 /*
402 ** The function was called with the wrong p_transaction_type
403 ** parameter. Raise an invalid transaction type exception.
404 */
405
406 RAISE invalid_transaction_type;
407
408 END IF;
409
410 IF (g_asn_debug = 'Y') THEN
411 asn_debug.put_line('Exit get_available_amt '||p_transaction_type);
412 asn_debug.put_line('p_available_amt '||p_available_amount);
413 asn_debug.put_line('p_tolerable_amount '||p_tolerable_amount);
414 end if;
415 EXCEPTION
416
417 WHEN invalid_transaction_type THEN
418
419 /*
420 ** debug - need to define a new message and also need to understand
421 ** how exactly to handle application error messages. A call to
422 ** some generic API is needed.
423 */
424
425 RAISE;
426
427 WHEN OTHERS THEN
428
429 IF (g_asn_debug = 'Y') THEN
430 asn_debug.put_line('When others exception in get_available_amt' );
431 end if;
432 po_message_s.sql_error('get_available_amount', x_progress, sqlcode);
433
434 RAISE;
435
436 END get_available_amount;
437 /*===========================================================================
438
439 PROCEDURE NAME: get_receive_quantity()
440
441 ===========================================================================*/
442
443 PROCEDURE get_receive_quantity(p_parent_id IN NUMBER,
444 p_receipt_source_code IN VARCHAR2,
445 p_available_quantity IN OUT NOCOPY NUMBER,
446 p_tolerable_quantity IN OUT NOCOPY NUMBER,
447 p_unit_of_measure IN OUT NOCOPY VARCHAR2,
448 p_secondary_available_qty IN OUT NOCOPY NUMBER ) IS
449
450 x_progress VARCHAR2(3) := NULL;
451
452 x_return_status VARCHAR2(80);
453 x_msg_count NUMBER;
454 x_msg_data VARCHAR2(240);
455
456 invalid_receipt_source_code EXCEPTION;
457
458 BEGIN
459
460 IF (p_receipt_source_code = 'VENDOR') THEN
461
462 get_po_quantity(p_parent_id, p_available_quantity,
463 p_tolerable_quantity, p_unit_of_measure,p_secondary_available_qty);
464
465 ELSIF (p_receipt_source_code = 'CUSTOMER') THEN
466
467 get_rma_quantity(p_parent_id, p_available_quantity,
468 p_tolerable_quantity, p_unit_of_measure,p_secondary_available_qty);
469
470 ELSIF (p_receipt_source_code in ('INTERNAL', 'INVENTORY', 'INTERNAL ORDER')) THEN
471
472 get_shipment_quantity(p_parent_id, p_available_quantity,
473 p_unit_of_measure,p_secondary_available_qty);
474
475 ELSE
476
477 /*
478 ** The function was called with the wrong p_receipt_source_code
479 ** parameter. Raise an invalid source code exception.
480 */
481
482 RAISE invalid_receipt_source_code;
483
484 END IF;
485
486 EXCEPTION
487
488 WHEN invalid_receipt_source_code THEN
489
490 /*
491 ** debug - need to define a new message and also need to understand
492 ** how exactly to handle application error messages. A call to
493 ** some generic API is needed.
494 */
495
496 RAISE;
497
498 WHEN OTHERS THEN
499
500 po_message_s.sql_error('get_receive_quantity', x_progress, sqlcode);
501
502 RAISE;
503
504 END get_receive_quantity;
505
506 /*===========================================================================
507
508 PROCEDURE NAME: get_receive_amount()
509
510 ===========================================================================*/
511
512 PROCEDURE get_receive_amount(p_parent_id IN NUMBER,
513 p_receipt_source_code IN VARCHAR2,
514 p_available_amount IN OUT NOCOPY NUMBER,
515 p_tolerable_amount IN OUT NOCOPY NUMBER ) IS
516
517 x_progress VARCHAR2(3) := NULL;
518
519 x_return_status VARCHAR2(80);
520 x_msg_count NUMBER;
521 x_msg_data VARCHAR2(240);
522
523 invalid_receipt_source_code EXCEPTION;
524
525 BEGIN
526 IF (g_asn_debug = 'Y') THEN
527 asn_debug.put_line('in get_receive_amount ' );
528 asn_debug.put_line('p_parent_id '||p_parent_id);
529 asn_debug.put_line('p_receipt_source_code '||p_receipt_source_code);
530 END IF;
531
532 IF (p_receipt_source_code = 'VENDOR') THEN
533 IF (g_asn_debug = 'Y') THEN
534 asn_debug.put_line('before get_po_amount ' );
535 end if;
536
537 get_po_amount(p_parent_id, p_available_amount,
538 p_tolerable_amount);
539
540 ELSE
541
542 /*
543 ** The function was called with the wrong p_receipt_source_code
544 ** parameter. Raise an invalid source code exception.
545 */
546
547 RAISE invalid_receipt_source_code;
548
549 END IF;
550
551 IF (g_asn_debug = 'Y') THEN
552 asn_debug.put_line('p_available_amount '||p_available_amount);
553 asn_debug.put_line('p_tolerable_amount '||p_tolerable_amount);
554 END IF;
555 EXCEPTION
556
557 WHEN invalid_receipt_source_code THEN
558
559 /*
560 ** debug - need to define a new message and also need to understand
561 ** how exactly to handle application error messages. A call to
562 ** some generic API is needed.
563 */
564
565 RAISE;
566
567 WHEN OTHERS THEN
568
569 IF (g_asn_debug = 'Y') THEN
570 asn_debug.put_line('Exception get_receive_amount ' );
571 end if;
572 po_message_s.sql_error('get_receive_amount', x_progress, sqlcode);
573
574 RAISE;
575
576 END get_receive_amount;
577
578 /*===========================================================================
579
580 PROCEDURE NAME: get_available_rma_quantity()
581
582 ===========================================================================*/
583
584 PROCEDURE get_available_rma_quantity(p_transaction_type IN VARCHAR2,
585 p_parent_id IN NUMBER,
586 p_receipt_source_code IN VARCHAR2,
587 p_parent_transaction_type IN VARCHAR2,
588 p_grand_parent_id IN NUMBER,
589 p_correction_type IN VARCHAR2,
590 p_oe_order_header_id IN NUMBER,
591 p_oe_order_line_id IN NUMBER,
592 p_available_quantity IN OUT NOCOPY NUMBER,
593 p_tolerable_quantity IN OUT NOCOPY NUMBER,
594 p_unit_of_measure IN OUT NOCOPY VARCHAR2,
595 p_secondary_available_qty IN OUT NOCOPY NUMBER ) IS
596
597 x_progress VARCHAR2(3) := NULL;
598 x_quantity_ordered NUMBER := 0;
599 x_quantity_received NUMBER := 0;
600 x_interface_quantity NUMBER := 0; /* in primary_uom */
601 x_quantity_cancelled NUMBER := 0;
602 x_qty_rcv_tolerance NUMBER := 0;
603 x_qty_rcv_exception_code VARCHAR2(26);
604 x_po_uom VARCHAR2(26);
605 x_item_id NUMBER;
606 x_primary_uom VARCHAR2(26);
607 x_interface_qty_in_po_uom NUMBER := 0;
608 invalid_transaction_type EXCEPTION;
609
610 BEGIN
611
612 IF (p_transaction_type IN ('RECEIVE', 'MATCH')) THEN
613
614 get_receive_quantity(p_oe_order_line_id, p_receipt_source_code,
615 p_available_quantity, p_tolerable_quantity,
616 p_unit_of_measure, p_secondary_available_qty );
617
618 ELSIF (p_transaction_type IN ('TRANSFER', 'INSPECT', 'DELIVER')) THEN
619
620 get_transaction_quantity(p_parent_id, p_available_quantity,
621 p_unit_of_measure,p_secondary_available_qty);
622
623 ELSIF (p_transaction_type IN ('CORRECT', 'RETURN TO VENDOR',
624 'RETURN TO RECEIVING')) THEN
625
626 get_correction_quantity(p_correction_type, p_parent_transaction_type,
627 p_receipt_source_code, p_parent_id,
628 p_grand_parent_id, p_available_quantity,
629 p_tolerable_quantity,p_unit_of_measure,p_secondary_available_qty);
630
631 ELSIF (p_transaction_type = 'DIRECT RECEIPT') THEN
632
633 get_po_dist_quantity(p_parent_id, p_available_quantity,
634 p_tolerable_quantity, p_unit_of_measure);
635
636 ELSIF (p_transaction_type = 'STANDARD DELIVER') THEN
637
638 get_rcv_dist_quantity(p_parent_id, p_grand_parent_id,
639 p_available_quantity, p_unit_of_measure);
640
641 ELSE
642
643 /*
644 ** The function was called with the wrong p_transaction_type
645 ** parameter. Raise an invalid transaction type exception.
646 */
647
648 RAISE invalid_transaction_type;
649
650 END IF;
651
652 EXCEPTION
653
654 WHEN OTHERS THEN
655
656 po_message_s.sql_error('get_rma_quantity', x_progress, sqlcode);
657
658 RAISE;
659
660
661 END get_available_rma_quantity;
662
663 /*===========================================================================
664
665 PROCEDURE NAME: get_po_quantity()
666
667 ===========================================================================*/
668
669 PROCEDURE get_po_quantity(p_line_location_id IN NUMBER,
670 p_available_quantity IN OUT NOCOPY NUMBER,
671 p_tolerable_quantity IN OUT NOCOPY NUMBER,
672 p_unit_of_measure IN OUT NOCOPY VARCHAR2,
673 p_secondary_available_qty IN OUT NOCOPY NUMBER ) IS
674
675 x_progress VARCHAR2(3) := NULL;
676 x_quantity_ordered NUMBER := 0;
677 x_quantity_received NUMBER := 0;
678 x_interface_quantity NUMBER := 0; /* in primary_uom */
679 x_quantity_cancelled NUMBER := 0;
680
681 x_qty_rcv_tolerance NUMBER := 0;
682 x_qty_rcv_exception_code VARCHAR2(26);
683 x_po_uom VARCHAR2(26);
684 x_item_id NUMBER;
685 x_primary_uom VARCHAR2(26);
686 x_interface_qty_in_po_uom NUMBER := 0;
687
688 /*Bug# 1548597*/
689 x_secondary_qty_ordered NUMBER := 0;
690 x_secondary_qty_received NUMBER := 0;
691 x_secondary_interface_qty NUMBER := 0;
692 x_secondary_qty_cancelled NUMBER := 0;
693 x_secondary_uom VARCHAR2(26);
694 --end bug 1548597
695
696 -- <Bug 9342280 : Added for CLM project>
697 l_is_clm_po VARCHAR2(5) := 'N';
698 l_distribution_type VARCHAR2(100);
699 l_matching_basis VARCHAR2(100);
700 l_accrue_on_receipt_flag VARCHAR2(100);
701 l_code_combination_id NUMBER;
702 l_budget_account_id NUMBER;
703 l_partial_funded_flag VARCHAR2(5) := 'N';
704 l_unit_meas_lookup_code VARCHAR2(100);
705 l_funded_value NUMBER;
706 l_quantity_funded NUMBER;
707 l_amount_funded NUMBER;
708 l_quantity_received NUMBER;
709 l_amount_received NUMBER;
710 l_quantity_delivered NUMBER;
711 l_amount_delivered NUMBER;
712 l_quantity_billed NUMBER;
713 l_amount_billed NUMBER;
714 l_quantity_cancelled NUMBER;
715 l_amount_cancelled NUMBER;
716 l_return_status VARCHAR2(100);
717 -- <CLM END>
718
719
720
721 BEGIN
722
723 x_progress := '005';
724
725 /*
726 ** Get PO quantity information.
727 */
728
729 SELECT nvl(pll.quantity, 0),
730 nvl(pll.quantity_received, 0),
731 nvl(pll.quantity_cancelled,0),
732 nvl(pll.secondary_quantity, 0),
733 nvl(pll.secondary_quantity_received, 0),
734 nvl(pll.secondary_quantity_cancelled,0),
735 1 + (nvl(pll.qty_rcv_tolerance,0)/100),
736 pll.qty_rcv_exception_code,
737 pl.item_id,
738 pl.unit_meas_lookup_code
739 INTO x_quantity_ordered,
740 x_quantity_received,
741 x_quantity_cancelled,
742 /*Bug# 1548597*/
743 x_secondary_qty_ordered,
744 x_secondary_qty_received,
745 x_secondary_qty_cancelled,
746 --end bug 1548597
747 x_qty_rcv_tolerance,
748 x_qty_rcv_exception_code,
749 x_item_id,
750 x_po_uom
751 FROM po_line_locations_all pll, --<Shared Proc FPJ>
752 po_lines_all pl --<Shared Proc FPJ>
753 WHERE pll.line_location_id = p_line_location_id
754 AND pll.po_line_id = pl.po_line_id;
755
756 x_progress := '010';
757
758 /*
759 ** Get any unprocessed receipt or match transaction against the
760 ** PO shipment. x_interface_quantity is in primary uom.
761 **
762 ** The min(primary_uom) is neccessary because the
763 ** select may return multiple rows and we only want one value
764 ** to be returned. Having a sum and min group function in the
765 ** select ensures that this sql statement will not raise a
766 ** no_data_found exception even if no rows are returned.
767 */
768
769 /* Bug# 2347348 : Primary Unit of Measure cannot have value
770 for One time Items. So Added a decode statement to fetch
771 unit_of_measure in case of One Time Items and Primary
772 Unit of Measure for Inventory Items.
773 */
774
775
776 SELECT nvl(sum(decode(nvl(order_transaction_id,-999),-999,primary_quantity,nvl(interface_transaction_qty,0))),0),
777 decode(min(item_id),null,min(unit_of_measure),min(primary_unit_of_measure))
778 INTO x_interface_quantity,
779 x_primary_uom
780 FROM rcv_transactions_interface rti
781 WHERE (transaction_status_code = 'PENDING'
782 and processing_status_code <> 'ERROR')
783 AND transaction_type IN ('RECEIVE', 'MATCH','CORRECT','SHIP') -- bug 657347 should include 'SHIP'
784 -- when calculating total quantity
785 -- in the interface table
786 AND NOT EXISTS(SELECT 1 FROM rcv_transactions rt -- bug 9583207 should not include
787 WHERE rt.transaction_type='DELIVER' -- Correction to Deliver transaction
788 AND rt.transaction_id = rti.parent_transaction_id
789 AND rti.transaction_type = 'CORRECT')
790 AND po_line_location_id = p_line_location_id;
791 /*
792 SELECT nvl(sum(primary_quantity),0),
793 decode(min(item_id),null,min(unit_of_measure),min(primary_unit_of_measure))
794 INTO x_interface_quantity,
795 x_primary_uom
796 FROM rcv_transactions_interface
797 WHERE (transaction_status_code = 'PENDING'
798 and processing_status_code <> 'ERROR')
799 */
800 /*
801 ** Modified by Subhajit on 09/06/95
802 ** Earlier transaction type were ('RECEIVE','MATCH')
803 ** CORRECT transaction were not taken into consideration
804 AND transaction_type IN ('RECEIVE', 'MATCH')
805 */
806 /*
807 AND transaction_type IN ('RECEIVE', 'MATCH','CORRECT','SHIP') -- bug 657347 should include 'SHIP'
808 -- when calculating total quantity
809 -- in the interface table
810 AND po_line_location_id = p_line_location_id;
811 */
812
813 IF (x_interface_quantity = 0) THEN
814
815 /*
816 ** There is no unprocessed quantity. Simply set the
817 ** x_interface_qty_in_po_uom to 0. There is no need for uom
818 ** conversion.
819 */
820
821 x_interface_qty_in_po_uom := 0;
822
823 ELSE
824
825 /*
826 ** There is unprocessed quantity. Convert it to the PO uom
827 ** so that the available quantity can be calculated in the PO uom
828 */
829
830 x_progress := '015';
831 po_uom_s.uom_convert(x_interface_quantity, x_primary_uom, x_item_id,
832 x_po_uom, x_interface_qty_in_po_uom);
833
834 END IF;
835
836 /*bug 1548597*/
837 SELECT nvl(sum(secondary_quantity),0),
838 min(secondary_unit_of_measure)
839 INTO x_secondary_interface_qty,
840 x_secondary_uom
841 FROM rcv_transactions_interface
842 WHERE (transaction_status_code = 'PENDING'
843 and processing_status_code <> 'ERROR')
844 AND transaction_type IN ('RECEIVE', 'MATCH','CORRECT','SHIP')
845 AND po_line_location_id = p_line_location_id;
846 /*bug 1548597*/
847
848 /*
849 ** Calculate the quantity available to be received.
850 */
851
852 -- <Bug 9342280 : Added for CLM project>
853 IF (g_asn_debug = 'Y') THEN
854 asn_debug.put_line('before calling po_clm_intg_grp.is_clm_po()');
855 END IF;
856
857 l_is_clm_po := po_clm_intg_grp.is_clm_po( p_po_header_id => NULL,
858 p_po_line_id => NULL,
859 p_po_line_location_id => p_line_location_id,
860 p_po_distribution_id => NULL);
861
862 l_partial_funded_flag := 'N';
863
864 IF (g_asn_debug = 'Y') THEN
865 asn_debug.put_line('line_location_id : ' || p_line_location_id);
866 asn_debug.put_line('l_is_clm_po: ' || l_is_clm_po);
867 END IF;
868
869 IF l_is_clm_po = 'Y' THEN
870 IF (g_asn_debug = 'Y') THEN
871 asn_debug.put_line('before calling po_clm_intg_grp.get_funding_info()');
872 END IF;
873
874 po_clm_intg_grp.get_funding_info( p_po_header_id => NULL,
875 p_po_line_id => NULL,
876 p_line_location_id => p_line_location_id,
877 p_po_distribution_id => NULL,
878 x_distribution_type => l_distribution_type,
879 x_matching_basis => l_matching_basis,
880 x_accrue_on_receipt_flag => l_accrue_on_receipt_flag,
881 x_code_combination_id => l_code_combination_id,
882 x_budget_account_id => l_budget_account_id,
883 x_partial_funded_flag => l_partial_funded_flag,
884 x_unit_meas_lookup_code => l_unit_meas_lookup_code,
885 x_funded_value => l_funded_value,
886 x_quantity_funded => l_quantity_funded,
887 x_amount_funded => l_amount_funded,
888 x_quantity_received => l_quantity_received,
889 x_amount_received => l_amount_received,
890 x_quantity_delivered => l_quantity_delivered,
891 x_amount_delivered => l_amount_delivered,
892 x_quantity_billed => l_quantity_billed,
893 x_amount_billed => l_amount_billed,
894 x_quantity_cancelled => l_quantity_cancelled,
895 x_amount_cancelled => l_amount_cancelled,
896 x_return_status => l_return_status
897
898 );
899
900 IF (g_asn_debug = 'Y') THEN
901 asn_debug.put_line('l_partial_funded_flag : ' || l_partial_funded_flag);
902 asn_debug.put_line('l_quantity_funded: ' || l_quantity_funded);
903 asn_debug.put_line('l_quantity_received : ' || l_quantity_received);
904 asn_debug.put_line('l_quantity_cancelled: ' || l_quantity_cancelled);
905 END IF;
906
907 IF l_partial_funded_flag = 'Y' THEN
908
909 x_quantity_ordered := l_quantity_funded;
910
911 END IF;
912
913 END IF;
914 -- <CLM END>
915
916 p_available_quantity := x_quantity_ordered - x_quantity_received -
917 x_quantity_cancelled - x_interface_qty_in_po_uom;
918
919 /*bug 1548597*/
920 p_secondary_available_qty := x_secondary_qty_ordered - x_secondary_qty_received -
921 x_secondary_qty_cancelled - x_secondary_interface_qty;
922 /*bug 1548597*/
923
924
925
926
927 /*
928 ** p_available_quantity can be negative if this shipment has been over
929 ** received. In this case, the available quantity that needs to be passed
930 ** back should be 0.
931 */
932
933 IF (p_available_quantity < 0) THEN
934
935 p_available_quantity := 0;
936
937 END IF;
938
939 /*bug 1548597*/
940 IF (p_secondary_available_qty < 0) THEN
941 p_secondary_available_qty := 0;
942 END IF;
943 /*bug 1548597*/
944
945 /*
946 ** Calculate the maximum quantity that can be received allowing for
947 ** tolerance.
948 */
949 -- <Bug 9342280 : Added for CLM project>
950 IF l_is_clm_po = 'Y' AND l_partial_funded_flag = 'Y' THEN
951 IF (g_asn_debug = 'Y') THEN
952 asn_debug.put_line('set p_tolerable_quantity for clm po');
953 END IF;
954
955 p_tolerable_quantity := p_available_quantity;
956
957 ELSE
958 IF (g_asn_debug = 'Y') THEN
959 asn_debug.put_line('set p_tolerable_quantity for non-clm po');
960 END IF;
961 -- <CLM END>
962 p_tolerable_quantity := (x_quantity_ordered * x_qty_rcv_tolerance) -
963 x_quantity_received - x_quantity_cancelled -
964 x_interface_qty_in_po_uom;
965
966 END IF; -- <Bug 9342280 : Added for CLM project>
967 /*
968 ** p_tolerable_quantity can be negative if this shipment has been over
969 ** received. In this case, the tolerable quantity that needs to be passed
970 ** back should be 0.
971 */
972
973 IF (p_tolerable_quantity < 0) THEN
974
975 p_tolerable_quantity := 0;
976
977 END IF;
978
979 /*
980 ** Return the PO unit of measure
981 */
982
983 p_unit_of_measure := x_po_uom;
984
985 EXCEPTION
986
987 WHEN OTHERS THEN
988
989 po_message_s.sql_error('get_po_quantity', x_progress, sqlcode);
990
991 RAISE;
992
993 END get_po_quantity;
994
995 /*===========================================================================
996
997 PROCEDURE NAME: get_po_amount()
998
999 ===========================================================================*/
1000
1001 PROCEDURE get_po_amount(p_line_location_id IN NUMBER,
1002 p_available_amount IN OUT NOCOPY NUMBER,
1003 p_tolerable_amount IN OUT NOCOPY NUMBER ) IS
1004
1005 x_progress VARCHAR2(3) := NULL;
1006 x_amount_ordered NUMBER := 0;
1007 x_amount_received NUMBER := 0;
1008 x_interface_amount NUMBER := 0;
1009 x_amount_cancelled NUMBER := 0;
1010
1011 x_qty_rcv_tolerance NUMBER := 0;
1012 x_qty_rcv_exception_code VARCHAR2(26);
1013
1014 -- <Bug 9342280 : Added for CLM project>
1015 l_is_clm_po VARCHAR2(5) := 'N';
1016 l_distribution_type VARCHAR2(100);
1017 l_matching_basis VARCHAR2(100);
1018 l_accrue_on_receipt_flag VARCHAR2(100);
1019 l_code_combination_id NUMBER;
1020 l_budget_account_id NUMBER;
1021 l_partial_funded_flag VARCHAR2(5) := 'N';
1022 l_unit_meas_lookup_code VARCHAR2(100);
1023 l_funded_value NUMBER;
1024 l_quantity_funded NUMBER;
1025 l_amount_funded NUMBER;
1026 l_quantity_received NUMBER;
1027 l_amount_received NUMBER;
1028 l_quantity_delivered NUMBER;
1029 l_amount_delivered NUMBER;
1030 l_quantity_billed NUMBER;
1031 l_amount_billed NUMBER;
1032 l_quantity_cancelled NUMBER;
1033 l_amount_cancelled NUMBER;
1034 l_return_status VARCHAR2(100);
1035 -- <CLM END>
1036
1037 BEGIN
1038
1039 x_progress := '005';
1040
1041 /*
1042 ** Get PO quantity information.
1043 */
1044
1045 SELECT nvl(pll.amount, 0),
1046 nvl(pll.amount_received, 0),
1047 nvl(pll.amount_cancelled,0),
1048 1 + (nvl(pll.qty_rcv_tolerance,0)/100),
1049 pll.qty_rcv_exception_code
1050 INTO x_amount_ordered,
1051 x_amount_received,
1052 x_amount_cancelled,
1053 x_qty_rcv_tolerance,
1054 x_qty_rcv_exception_code
1055 FROM po_line_locations_all pll, --<Shared Proc FPJ>
1056 po_lines_all pl --<Shared Proc FPJ>
1057 WHERE pll.line_location_id = p_line_location_id
1058 AND pll.po_line_id = pl.po_line_id;
1059
1060 x_progress := '010';
1061
1062 SELECT nvl(sum(decode(nvl(order_transaction_id,-999),-999,amount,nvl(interface_transaction_amt,0))),0)
1063 INTO x_interface_amount
1064 FROM rcv_transactions_interface
1065 WHERE (transaction_status_code = 'PENDING'
1066 and processing_status_code <> 'ERROR')
1067 AND transaction_type IN ('RECEIVE', 'MATCH','CORRECT')
1068 AND po_line_location_id = p_line_location_id;
1069
1070 -- <Bug 9342280 : Added for CLM project>
1071 IF (g_asn_debug = 'Y') THEN
1072 asn_debug.put_line('before calling po_clm_intg_grp.is_clm_po()');
1073 END IF;
1074
1075 l_is_clm_po := po_clm_intg_grp.is_clm_po( p_po_header_id => NULL,
1076 p_po_line_id => NULL,
1077 p_po_line_location_id => p_line_location_id,
1078 p_po_distribution_id => NULL);
1079
1080 l_partial_funded_flag := 'N';
1081
1082 IF (g_asn_debug = 'Y') THEN
1083 asn_debug.put_line('line_location_id : ' || p_line_location_id);
1084 asn_debug.put_line('l_is_clm_po: ' || l_is_clm_po);
1085 END IF;
1086
1087 IF l_is_clm_po = 'Y' THEN
1088 IF (g_asn_debug = 'Y') THEN
1089 asn_debug.put_line('before calling po_clm_intg_grp.get_funding_info()');
1090 END IF;
1091
1092 po_clm_intg_grp.get_funding_info( p_po_header_id => NULL,
1093 p_po_line_id => NULL,
1094 p_line_location_id => p_line_location_id,
1095 p_po_distribution_id => NULL,
1096 x_distribution_type => l_distribution_type,
1097 x_matching_basis => l_matching_basis,
1098 x_accrue_on_receipt_flag => l_accrue_on_receipt_flag,
1099 x_code_combination_id => l_code_combination_id,
1100 x_budget_account_id => l_budget_account_id,
1101 x_partial_funded_flag => l_partial_funded_flag,
1102 x_unit_meas_lookup_code => l_unit_meas_lookup_code,
1103 x_funded_value => l_funded_value,
1104 x_quantity_funded => l_quantity_funded,
1105 x_amount_funded => l_amount_funded,
1106 x_quantity_received => l_quantity_received,
1107 x_amount_received => l_amount_received,
1108 x_quantity_delivered => l_quantity_delivered,
1109 x_amount_delivered => l_amount_delivered,
1110 x_quantity_billed => l_quantity_billed,
1111 x_amount_billed => l_amount_billed,
1112 x_quantity_cancelled => l_quantity_cancelled,
1113 x_amount_cancelled => l_amount_cancelled,
1114 x_return_status => l_return_status
1115
1116 );
1117
1118 IF (g_asn_debug = 'Y') THEN
1119 asn_debug.put_line('l_partial_funded_flag : ' || l_partial_funded_flag);
1120 asn_debug.put_line('l_amount_funded: ' || l_amount_funded);
1121 asn_debug.put_line('l_amount_received : ' || l_amount_received);
1122 asn_debug.put_line('l_amount_cancelled: ' || l_amount_cancelled);
1123 END IF;
1124
1125 IF l_partial_funded_flag = 'Y' THEN
1126
1127 x_amount_ordered := l_amount_funded;
1128
1129 END IF;
1130
1131 END IF;
1132 -- <CLM END>
1133
1134 /*
1135 ** Calculate the quantity available to be received.
1136 */
1137
1138 p_available_amount := x_amount_ordered - x_amount_received -
1139 x_amount_cancelled - x_interface_amount;
1140
1141 /*
1142 ** p_available_quantity can be negative if this shipment has been over
1143 ** received. In this case, the available quantity that needs to be passed
1144 ** back should be 0.
1145 */
1146
1147 IF (p_available_amount < 0) THEN
1148 p_available_amount := 0;
1149 END IF;
1150
1151 /*
1152 ** Calculate the maximum quantity that can be received allowing for
1153 ** tolerance.
1154 */
1155
1156 -- <Bug 9342280 : Added for CLM project>
1157 IF l_is_clm_po = 'Y' AND l_partial_funded_flag = 'Y' THEN
1158 IF (g_asn_debug = 'Y') THEN
1159 asn_debug.put_line('set p_tolerable_amount for clm po');
1160 END IF;
1161
1162 p_tolerable_amount := p_available_amount;
1163
1164 ELSE
1165 IF (g_asn_debug = 'Y') THEN
1166 asn_debug.put_line('set p_tolerable_amount for non-clm po');
1167 END IF;
1168 -- <CLM END>
1169
1170 p_tolerable_amount := (x_amount_ordered * x_qty_rcv_tolerance) -
1171 x_amount_received - x_amount_cancelled -
1172 x_interface_amount;
1173 END IF; -- <Bug 9342280 : Added for CLM project>
1174
1175 /*
1176 ** p_tolerable_quantity can be negative if this shipment has been over
1177 ** received. In this case, the tolerable quantity that needs to be passed
1178 ** back should be 0.
1179 */
1180
1181 IF (p_tolerable_amount < 0) THEN
1182 p_tolerable_amount := 0;
1183 END IF;
1184
1185 EXCEPTION
1186
1187 WHEN OTHERS THEN
1188
1189 po_message_s.sql_error('get_po_amount', x_progress, sqlcode);
1190
1191 RAISE;
1192
1193 END get_po_amount;
1194
1195 /*===========================================================================
1196
1197 PROCEDURE NAME: get_rma_quantity()
1198
1199 ===========================================================================*/
1200
1201 PROCEDURE get_rma_quantity(
1202 p_oe_order_line_id IN NUMBER,
1203 p_available_quantity IN OUT NOCOPY NUMBER,
1204 p_tolerable_quantity IN OUT NOCOPY NUMBER,
1205 p_unit_of_measure IN OUT NOCOPY VARCHAR2,
1206 p_secondary_available_qty IN OUT NOCOPY NUMBER
1207 ) IS
1208 x_progress VARCHAR2(3) := NULL;
1209 x_return_status VARCHAR2(80);
1210 x_msg_count NUMBER;
1211 x_msg_data VARCHAR2(240);
1212 x_interface_quantity NUMBER := 0; /* in primary_uom */
1213 x_qty_rcv_tolerance NUMBER := 0;
1214 x_oe_uom VARCHAR2(26);
1215 x_item_id NUMBER;
1216 x_primary_uom VARCHAR2(26);
1217 x_interface_qty_in_oe_uom NUMBER := 0;
1218 x_secondary_qty_ordered NUMBER := 0;
1219 x_secondary_qty_received NUMBER := 0;
1220 x_secondary_interface_qty NUMBER := 0;
1221 x_secondary_qty_cancelled NUMBER := 0;
1222 x_secondary_uom VARCHAR2(26);
1223
1224 CURSOR get_lines IS
1225 SELECT NVL(quantity, interface_transaction_qty) quantity,
1226 unit_of_measure,
1227 secondary_quantity,
1228 secondary_unit_of_measure
1229 FROM rcv_transactions_interface
1230 WHERE ( transaction_status_code = 'PENDING'
1231 AND processing_status_code <> 'ERROR')
1232 AND transaction_type IN('RECEIVE', 'MATCH', 'CORRECT', 'SHIP')
1233 AND oe_order_line_id = p_oe_order_line_id;
1234 BEGIN
1235 asn_debug.put_line('***BEGIN*** get_po_quantity');
1236 asn_debug.put_line('p_oe_order_line_id = ' || p_oe_order_line_id);
1237 asn_debug.put_line('p_available_quantity = ' || p_available_quantity);
1238 asn_debug.put_line('p_tolerable_quantity = ' || p_tolerable_quantity);
1239 asn_debug.put_line('p_unit_of_measure = ' || p_unit_of_measure);
1240 asn_debug.put_line('p_secondary_available_qty = ' || p_secondary_available_qty);
1241 x_progress := '010';
1242
1243 SELECT NVL(oel.ordered_quantity2, 0),
1244 NVL(oel.shipped_quantity2, 0),
1245 NVL(oel.cancelled_quantity2, 0),
1246 NVL(oel.ship_tolerance_above, 0),
1247 oel.inventory_item_id,
1248 uom.unit_of_measure
1249 INTO x_secondary_qty_ordered,
1250 x_secondary_qty_received,
1251 x_secondary_qty_cancelled,
1252 x_qty_rcv_tolerance,
1253 x_item_id,
1254 x_oe_uom
1255 FROM oe_order_lines_all oel,
1256 mtl_units_of_measure uom
1257 WHERE oel.line_id = p_oe_order_line_id
1258 AND uom.uom_code = oel.order_quantity_uom;
1259
1260 x_progress := '020';
1261 oe_rma_receiving.get_rma_available_quantity(p_oe_order_line_id,
1262 p_available_quantity,
1263 x_return_status,
1264 x_msg_count,
1265 x_msg_data
1266 );
1267 x_progress := '030';
1268
1269 FOR c_lines IN get_lines LOOP
1270 po_uom_s.uom_convert(c_lines.quantity,
1271 c_lines.unit_of_measure,
1272 x_item_id,
1273 x_oe_uom,
1274 x_interface_quantity
1275 );
1276 x_interface_qty_in_oe_uom := NVL(x_interface_qty_in_oe_uom, 0) + NVL(x_interface_quantity, 0);
1277 x_secondary_interface_qty := NVL(x_secondary_interface_qty, 0) + NVL(c_lines.secondary_quantity, 0);
1278 x_secondary_uom := NVL(x_secondary_uom, c_lines.secondary_unit_of_measure);
1279 END LOOP;
1280
1281 x_progress := '040';
1282 p_available_quantity := p_available_quantity - NVL(x_interface_qty_in_oe_uom, 0);
1283 p_secondary_available_qty := x_secondary_qty_ordered - x_secondary_qty_received - x_secondary_qty_cancelled - NVL(x_secondary_interface_qty, 0);
1284
1285 /*
1286 ** Calculate the maximum quantity that can be received allowing for
1287 ** tolerance.
1288 */
1289 p_tolerable_quantity := p_available_quantity + x_qty_rcv_tolerance;
1290
1291 /*
1292 ** p_available_quantity can be negative if this shipment has been over
1293 ** received. In this case, the available quantity that needs to be passed
1294 ** back should be 0.
1295 */
1296 IF (p_available_quantity < 0) THEN
1297 p_available_quantity := 0;
1298 END IF;
1299
1300 IF (p_secondary_available_qty < 0) THEN
1301 p_secondary_available_qty := 0;
1302 END IF;
1303
1304 /*
1305 ** p_tolerable_quantity can be negative if this shipment has been over
1306 ** received. In this case, the tolerable quantity that needs to be passed
1307 ** back should be 0.
1308 */
1309 IF (p_tolerable_quantity < 0) THEN
1310 p_tolerable_quantity := 0;
1311 END IF;
1312
1313 /*
1314 ** Return the PO unit of measure
1315 */
1316 p_unit_of_measure := x_oe_uom;
1317 asn_debug.put_line('p_oe_order_line_id = ' || p_oe_order_line_id);
1318 asn_debug.put_line('p_available_quantity = ' || p_available_quantity);
1319 asn_debug.put_line('p_tolerable_quantity = ' || p_tolerable_quantity);
1320 asn_debug.put_line('p_unit_of_measure = ' || p_unit_of_measure);
1321 asn_debug.put_line('p_secondary_available_qty = ' || p_secondary_available_qty);
1322 asn_debug.put_line('***END*** get_po_quantity');
1323 EXCEPTION
1324 WHEN OTHERS THEN
1325 po_message_s.sql_error('get_rma_quantity',
1326 x_progress,
1327 SQLCODE
1328 );
1329 RAISE;
1330 END get_rma_quantity;
1331
1332 /*===========================================================================
1333
1334 PROCEDURE NAME: get_shipment_quantity()
1335
1336 ===========================================================================*/
1337
1338 PROCEDURE get_shipment_quantity(p_shipment_line_id IN NUMBER,
1339 p_available_quantity IN OUT NOCOPY NUMBER,
1340 p_unit_of_measure IN OUT NOCOPY VARCHAR2,
1341 p_secondary_available_qty IN OUT NOCOPY NUMBER ) IS
1342
1343 x_progress VARCHAR2(3) := NULL;
1344 x_quantity_shipped NUMBER := 0;
1345 x_quantity_received NUMBER := 0;
1346 x_interface_quantity NUMBER := 0; /* in primary_uom */
1347 x_shipment_uom VARCHAR2(26);
1348 x_item_id NUMBER;
1349 x_primary_uom VARCHAR2(26);
1350 x_interface_qty_in_ship_uom NUMBER := 0;
1351
1352
1353 /*Bug# 1548597*/
1354 x_secondary_qty_shipped NUMBER := 0;
1355 x_secondary_qty_received NUMBER := 0;
1356 x_secondary_interface_qty NUMBER := 0;
1357 x_secondary_uom VARCHAR2(26);
1358 --end bug 1548597
1359
1360
1361 BEGIN
1362
1363 x_progress := '005';
1364
1365 /*
1366 ** Get shipment quantity information.
1367 */
1368
1369 SELECT nvl(rsl.quantity_shipped, 0),
1370 nvl(rsl.quantity_received, 0),
1371 rsl.item_id,
1372 rsl.unit_of_measure,
1373 /*Bug# 1548597 */
1374 nvl(rsl.secondary_quantity_shipped, 0),
1375 nvl(rsl.secondary_quantity_received, 0)
1376 --End Bug 1548597
1377 INTO x_quantity_shipped,
1378 x_quantity_received,
1379 x_item_id,
1380 x_shipment_uom,
1381 x_secondary_qty_shipped,
1382 x_secondary_qty_received
1383 FROM rcv_shipment_lines rsl
1384 WHERE rsl.shipment_line_id = p_shipment_line_id;
1385
1386 x_progress := '010';
1387
1388 /*
1389 ** Get any unprocessed receipt transaction against the
1390 ** shipment. x_interface_quantity is in primary uom.
1391 **
1392 ** The min(primary_uom) is neccessary because the
1393 ** select may return multiple rows and we only want one value
1394 ** to be returned. Having a sum and min group function in the
1395 ** select ensures that this sql statement will not raise a
1396 ** no_data_found exception even if no rows are returned.
1397 */
1398
1399 SELECT nvl(sum(decode(nvl(order_transaction_id,-999),-999,primary_quantity,nvl(interface_transaction_qty,0))),0),
1400 min(primary_unit_of_measure)
1401 INTO x_interface_quantity,
1402 x_primary_uom
1403 FROM rcv_transactions_interface
1404 WHERE (transaction_status_code = 'PENDING'
1405 and processing_status_code <> 'ERROR')
1406 AND transaction_type = 'RECEIVE'
1407 AND shipment_line_id = p_shipment_line_id;
1408
1409 IF (x_interface_quantity = 0) THEN
1410
1411 /*
1412 ** There is no unprocessed quantity. Simply set the
1413 ** x_interface_qty_in_ship_uom to 0. There is no need for uom
1414 ** conversion.
1415 */
1416
1417 x_interface_qty_in_ship_uom := 0;
1418
1419 ELSE
1420
1421 /*
1422 ** There is unprocessed quantity. Convert it to the shipment uom
1423 ** so that the available quantity can be calculated in the shipment uom
1424 */
1425 x_progress := '015';
1426
1427 po_uom_s.uom_convert(x_interface_quantity, x_primary_uom, x_item_id,
1428 x_shipment_uom, x_interface_qty_in_ship_uom);
1429
1430 END IF;
1431
1432 /*
1433 ** Calculate the quantity available to be received.
1434 */
1435
1436 p_available_quantity := x_quantity_shipped - x_quantity_received -
1437 x_interface_qty_in_ship_uom;
1438
1439 /*
1440 ** p_available_quantity can be negative if this shipment has been over
1441 ** received. In this case, the available quantity that needs to be passed
1442 ** back should be 0.
1443 */
1444
1445 IF (p_available_quantity < 0) THEN
1446
1447 p_available_quantity := 0;
1448
1449 END IF;
1450
1451 /*
1452 ** Return the SHIPMENT unit of measure
1453 */
1454
1455 p_unit_of_measure := x_shipment_uom;
1456
1457
1458 /*Bug # 1548597 */
1459 SELECT nvl(sum(secondary_quantity),0),
1460 min(secondary_unit_of_measure)
1461 INTO x_secondary_interface_qty,
1462 x_secondary_uom
1463 FROM rcv_transactions_interface
1464 WHERE (transaction_status_code = 'PENDING'
1465 and processing_status_code <> 'ERROR')
1466 AND transaction_type = 'RECEIVE'
1467 AND shipment_line_id = p_shipment_line_id;
1468
1469 /*
1470 ** Calculate the quantity available to be received.
1471 */
1472
1473 p_secondary_available_qty := x_secondary_qty_shipped - x_secondary_qty_received -
1474 x_secondary_interface_qty;
1475
1476 /*
1477 ** p_available_quantity can be negative if this shipment has been over
1478 ** received. In this case, the available quantity that needs to be passed
1479 ** back should be 0.
1480 */
1481
1482 IF (p_secondary_available_qty < 0) THEN
1483
1484 p_secondary_available_qty := 0;
1485
1486 END IF;
1487 /*End Bug # 1548597 */
1488
1489 EXCEPTION
1490
1491 WHEN OTHERS THEN
1492
1493 po_message_s.sql_error('get_shipment_quantity', x_progress, sqlcode);
1494
1495 RAISE;
1496
1497 END get_shipment_quantity;
1498
1499 /*===========================================================================
1500
1501 PROCEDURE NAME: get_transaction_quantity()
1502
1503 ===========================================================================*/
1504
1505 PROCEDURE get_transaction_quantity(p_transaction_id IN NUMBER,
1506 p_available_quantity IN OUT NOCOPY NUMBER,
1507 p_unit_of_measure IN OUT NOCOPY VARCHAR2,
1508 p_secondary_available_qty IN OUT NOCOPY NUMBER ) IS
1509
1510 x_progress VARCHAR2(3) := NULL;
1511 x_transaction_quantity NUMBER := 0;
1512 x_interface_quantity NUMBER := 0; /* in primary uom */
1513 x_transaction_uom VARCHAR2(26);
1514 x_primary_uom VARCHAR2(26);
1515 x_item_id NUMBER;
1516 x_interface_qty_in_trx_uom NUMBER;
1517
1518 /*Bug # 1548597 */
1519 x_secondary_transaction_qty NUMBER := 0;
1520 x_secondary_interface_qty NUMBER := 0;
1521 x_secondary_uom VARCHAR2(26);
1522 --end bug 1548597
1523
1524 BEGIN
1525
1526 x_progress := '005';
1527
1528 /*
1529 ** Get available supply quantity information.
1530 */
1531
1532 /*
1533 ** There may be no supply quantity hence the exception no data found
1534 ** needs to be trapped here
1535 */
1536
1537 -- bug 4873207
1538 IF (g_asn_debug = 'Y') THEN
1539 asn_debug.put_line('get_transaction_quantity >> ' || x_progress );
1540 asn_debug.put_line('p_transaction_id ' || p_transaction_id);
1541 END IF;
1542 -- bug 4873207
1543
1544 BEGIN
1545
1546 /*
1547 Bug#5369121 - Fetching the primary uom from rcv_supply or rcv_transactions
1548 rather than RTI since it could be null in RTI
1549 */
1550
1551 SELECT quantity,
1552 secondary_quantity, /*Bug#9159988 selecting the secondary quantity from rcv_supply*/
1553 unit_of_measure,
1554 item_id,
1555 to_org_primary_uom
1556 INTO x_transaction_quantity,
1557 x_secondary_transaction_qty,
1558 x_transaction_uom,
1559 x_item_id,
1560 x_primary_uom
1561 FROM rcv_supply
1562 WHERE supply_type_code = 'RECEIVING'
1563 AND supply_source_id = p_transaction_id;
1564
1565 /* Bug# 1548597 */
1566 /* select SUM(decode(transaction_type,
1567 'RECEIVE', Secondary_Quantity,
1568 'CORRECT',DECODE(Secondary_Quantity/DECODE(ABS(Secondary_Quantity),0,1,ABS(Secondary_Quantity)),
1569 1,Secondary_Quantity,
1570 -1,-1*Secondary_Quantity,
1571 Secondary_Quantity),
1572 'RETURN TO VENDOR', Secondary_Quantity,
1573 'RETURN TO RECEIVING', Secondary_Quantity,
1574 'ACCEPT', Secondary_Quantity,
1575 'REJECT', Secondary_Quantity,
1576 'DELIVER',-1*Secondary_Quantity,
1577 'UNORDERED', Secondary_Quantity,
1578 'MATCH',Secondary_Quantity,
1579 'TRANSFER',Secondary_quantity,0))
1580 into x_secondary_transaction_qty
1581 from rcv_transactions
1582 start with transaction_id = p_transaction_id
1583 connect by parent_transaction_id = prior transaction_id;*/
1584 -- Bug# 1548597
1585
1586 EXCEPTION
1587
1588 WHEN NO_DATA_FOUND THEN
1589
1590 x_transaction_quantity := 0;
1591
1592 -- bug 4873207
1593 SELECT rt.unit_of_measure,
1594 rsl.item_id,
1595 rt.primary_unit_of_measure
1596 INTO x_transaction_uom,
1597 x_item_id,
1598 x_primary_uom
1599 FROM rcv_transactions rt,
1600 rcv_shipment_lines rsl
1601 WHERE rsl.shipment_line_id = rt.shipment_line_id
1602 AND rt.transaction_id = p_transaction_id;
1603 -- bug 4873207
1604
1605
1606 WHEN OTHERS THEN RAISE;
1607
1608 END;
1609
1610 -- bug 4873207
1611 IF (g_asn_debug = 'Y') THEN
1612 asn_debug.put_line('get_transaction_quantity >> ');
1613 asn_debug.put_line('x_transaction_quantity '||x_transaction_quantity);
1614 asn_debug.put_line('x_transaction_uom '||x_transaction_uom);
1615 asn_debug.put_line('item_id '||x_item_id);
1616 asn_debug.put_line('x_secondary_transaction_qty '||x_secondary_transaction_qty);
1617 END IF;
1618 -- bug 4873207
1619
1620
1621 x_progress := '010';
1622
1623 /*
1624 ** Get any unprocessed receipt transaction against the
1625 ** parent transaction. x_interface_quantity is in primary uom.
1626 **
1627 ** The min(primary_uom) is neccessary because the
1628 ** select may return multiple rows and we only want one value
1629 ** to be returned. Having a sum and min group function in the
1630 ** select ensures that this sql statement will not raise a
1631 ** no_data_found exception even if no rows are returned.
1632 */
1633 /*Bug#9159988 */
1634 SELECT nvl(sum(decode(transaction_type,
1635 'CORRECT', -1 * (decode(nvl(order_transaction_id,-999),-999,primary_quantity,nvl(interface_transaction_qty,0))),
1636 decode(nvl(order_transaction_id,-999),-999,primary_quantity,nvl(interface_transaction_qty,0))
1637 )),0),
1638 nvl(sum(decode(transaction_type,
1639 'CORRECT', -1 * secondary_quantity,
1640 secondary_quantity))
1641 ,0)
1642
1643
1644 INTO x_interface_quantity,
1645 x_secondary_interface_qty
1646 FROM rcv_transactions_interface
1647 WHERE (transaction_status_code = 'PENDING'
1648 and processing_status_code <> 'ERROR')
1649 AND parent_transaction_id = p_transaction_id;
1650
1651 IF (x_interface_quantity = 0) THEN
1652
1653 /*
1654 ** There is no unprocessed quantity. Simply set the
1655 ** x_interface_qty_in_trx_uom to 0. There is no need for uom
1656 ** conversion.
1657 */
1658
1659 x_interface_qty_in_trx_uom := 0;
1660
1661 ELSE
1662
1663 /*
1664 ** There is unprocessed quantity. Convert it to the transaction uom
1665 ** so that the available quantity can be calculated in the trx uom
1666 */
1667 IF (g_asn_debug = 'Y') THEN
1668 asn_debug.put_line('Before uom_convert:');
1669 asn_debug.put_line('x_interface_quantity:' || x_interface_quantity);
1670 asn_debug.put_line('x_primary_uom:' || x_primary_uom);
1671 asn_debug.put_line('x_transaction_uom:' || x_transaction_uom);
1672 asn_debug.put_line('x_item_id:' || x_item_id);
1673 END IF;
1674
1675 x_progress := '015';
1676 po_uom_s.uom_convert(x_interface_quantity, x_primary_uom, x_item_id,
1677 x_transaction_uom, x_interface_qty_in_trx_uom);
1678
1679 END IF;
1680
1681
1682
1683 /*
1684 ** Calculate the quantity available to be transacted
1685 */
1686
1687 p_available_quantity := x_transaction_quantity - x_interface_qty_in_trx_uom;
1688
1689 /*
1690 ** Return the parent transactions unit of measure
1691 */
1692
1693 p_unit_of_measure := x_transaction_uom;
1694
1695 /*dbms_output.put_line ('get_transaction_quantity.p_available_quantity = '||
1696 to_char(p_available_quantity));
1697
1698 dbms_output.put_line ('get_transaction_quantity.p_unit_of_measure = '||
1699 p_unit_of_measure);*/
1700
1701 /* Bug 1548597 */
1702 /* SELECT nvl(sum(decode(transaction_type,
1703 'CORRECT', -1 * (decode(nvl(order_transaction_id,-999),
1704 -999,primary_quantity,nvl(interface_transaction_qty,0))),
1705 secondary_quantity)),0),
1706 min(secondary_unit_of_measure)
1707 INTO x_secondary_interface_qty,
1708 x_secondary_uom
1709 FROM rcv_transactions_interface
1710 WHERE (transaction_status_code = 'PENDING'
1711 and processing_status_code <> 'ERROR')
1712 AND parent_transaction_id = p_transaction_id; */
1713
1714 p_secondary_available_qty := x_secondary_transaction_qty - x_secondary_interface_qty;
1715 -- end bug 1548597
1716
1717
1718 EXCEPTION
1719
1720 WHEN OTHERS THEN
1721
1722 po_message_s.sql_error('get_transaction_quantity', x_progress, sqlcode);
1723
1724 RAISE;
1725
1726 END get_transaction_quantity;
1727
1728 /*===========================================================================
1729
1730 PROCEDURE NAME: get_transaction_amount()
1731
1732 p_transaction_id => RECEIVE transaction
1733
1734 Called when:
1735 txn type = NEGATIVE CORRECT AND parent type = RECEIVE => get_txn_amt(parent_id)
1736 txn type = POSITIVE CORRECT AND parent type = DELIVER => get_txn_amt(grand_parent_id)
1737
1738 ===========================================================================*/
1739
1740 PROCEDURE get_transaction_amount(p_transaction_id IN NUMBER,
1741 p_available_amount IN OUT NOCOPY NUMBER ) IS
1742
1743 x_progress VARCHAR2(3) := NULL;
1744 x_transaction_amount NUMBER := 0;
1745 x_interface_amount NUMBER := 0;
1746 x_interface_deliver_amount NUMBER := 0;
1747 l_deliver_id NUMBER;
1748 l_receive_correct NUMBER;
1749 l_deliver_correct NUMBER;
1750
1751 BEGIN
1752
1753 x_progress := '005';
1754
1755 /*
1756 ** Get available amount information from processed transactions.
1757 */
1758
1759 IF (g_asn_debug = 'Y') THEN
1760 asn_debug.put_line('get_transaction_amount ' );
1761 asn_debug.put_line('p_transaction_id '||p_transaction_id );
1762 end if;
1763 BEGIN
1764
1765 SELECT nvl(sum(amount),0)
1766 into l_receive_correct
1767 from rcv_transactions
1768 where parent_transaction_id = p_transaction_id
1769 and transaction_type = 'CORRECT';
1770
1771 IF (g_asn_debug = 'Y') THEN
1772 asn_debug.put_line('l_receive_correct '||l_receive_correct);
1773 end if;
1774 select transaction_id
1775 into l_deliver_id
1776 from rcv_transactions
1777 where parent_transaction_id= p_transaction_id
1778 and transaction_type='DELIVER';
1779
1780 IF (g_asn_debug = 'Y') THEN
1781 asn_debug.put_line('l_deliver_id '||l_deliver_id);
1782 end if;
1783 SELECT nvl(sum(amount),0)
1784 into l_deliver_correct
1785 from rcv_transactions
1786 where parent_transaction_id = l_deliver_id
1787 and transaction_type = 'CORRECT';
1788
1789 IF (g_asn_debug = 'Y') THEN
1790 asn_debug.put_line('l_deliver_correct '||l_deliver_correct);
1791 end if;
1792
1793 EXCEPTION
1794 WHEN NO_DATA_FOUND THEN
1795 x_transaction_amount := 0;
1796 END;
1797
1798 x_progress := '010';
1799
1800 /*
1801 ** Get any unprocessed receipt transaction against the
1802 ** parent transaction. x_interface_quantity is in primary uom.
1803 **
1804 ** The min(primary_uom) is neccessary because the
1805 ** select may return multiple rows and we only want one value
1806 ** to be returned. Having a sum and min group function in the
1807 ** select ensures that this sql statement will not raise a
1808 ** no_data_found exception even if no rows are returned.
1809 */
1810
1811 SELECT nvl(sum(decode(transaction_type,
1812 'CORRECT', -1 * (decode(nvl(order_transaction_id,-999),-999,amount,nvl(interface_transaction_amt,0))),
1813 decode(nvl(order_transaction_id,-999),-999,amount,nvl(interface_transaction_amt,0))
1814 )),0)
1815 INTO x_interface_amount
1816 FROM rcv_transactions_interface
1817 WHERE (transaction_status_code = 'PENDING'
1818 and processing_status_code <> 'ERROR')
1819 AND parent_transaction_id = p_transaction_id;
1820
1821 IF (g_asn_debug = 'Y') THEN
1822 asn_debug.put_line('x_interface_amount '||x_interface_amount);
1823 end if;
1824
1825 -- do the same for the children of the receive transaction
1826 SELECT nvl(sum(decode(transaction_type,
1827 'CORRECT', -1 * (decode(nvl(order_transaction_id,-999),-999,amount,nvl(interface_transaction_amt,0))),
1828 decode(nvl(order_transaction_id,-999),-999,amount,nvl(interface_transaction_amt,0))
1829 )),0)
1830 INTO x_interface_deliver_amount
1831 FROM rcv_transactions_interface
1832 WHERE (transaction_status_code = 'PENDING'
1833 and processing_status_code <> 'ERROR')
1834 AND parent_transaction_id = l_deliver_id;
1835
1836 IF (g_asn_debug = 'Y') THEN
1837 asn_debug.put_line('x_interface_deliver_amount '||x_interface_deliver_amount);
1838 END IF;
1839
1840 /*
1841 ** Calculate the quantity available to be transacted
1842 */
1843
1844 p_available_amount := l_receive_correct - l_deliver_correct - (x_interface_amount - x_interface_deliver_amount);
1845
1846 IF (g_asn_debug = 'Y') THEN
1847 asn_debug.put_line('p_available_amount '||p_available_amount);
1848 end if;
1849
1850 EXCEPTION
1851
1852 WHEN OTHERS THEN
1853
1854 IF (g_asn_debug = 'Y') THEN
1855 asn_debug.put_line('Exception in get_transaction_amount');
1856 end if;
1857 po_message_s.sql_error('get_transaction_amount', x_progress, sqlcode);
1858
1859 RAISE;
1860
1861 END get_transaction_amount;
1862
1863 /*===========================================================================
1864
1865 PROCEDURE NAME: get_correction_quantity()
1866
1867 ===========================================================================*/
1868
1869 PROCEDURE get_correction_quantity(p_correction_type IN VARCHAR2,
1870 p_parent_transaction_type IN VARCHAR2,
1871 p_receipt_source_code IN VARCHAR2,
1872 p_parent_id IN NUMBER,
1873 p_grand_parent_id IN NUMBER,
1874 p_available_quantity IN OUT NOCOPY NUMBER,
1875 p_tolerable_quantity IN OUT NOCOPY NUMBER,
1876 p_unit_of_measure IN OUT NOCOPY VARCHAR2,
1877 p_secondary_available_qty IN OUT NOCOPY NUMBER ) IS
1878
1879 x_progress VARCHAR2(3) := NULL;
1880 x_parent_uom VARCHAR2(26);
1881 x_item_id NUMBER;
1882 x_primary_uom VARCHAR2(26);
1883 use_primary_uom VARCHAR2(26) := NULL;
1884 x_trx_quantity NUMBER := 0;
1885 X_interface_quantity NUMBER := 0;
1886 invalid_parent_trx_type EXCEPTION;
1887 x_interface_qty_in_trx_uom NUMBER := 0;
1888 l_quantity_in_parent_uom RCV_TRANSACTIONS.quantity%TYPE; -- Bug 2737257
1889
1890 -- <Bug 9342280 : Added for CLM project>
1891 l_po_line_location_id NUMBER;
1892 l_is_clm_po VARCHAR2(5) := 'N';
1893 l_distribution_type VARCHAR2(100);
1894 l_matching_basis VARCHAR2(100);
1895 l_accrue_on_receipt_flag VARCHAR2(100);
1896 l_code_combination_id NUMBER;
1897 l_budget_account_id NUMBER;
1898 l_partial_funded_flag VARCHAR2(5) := 'N';
1899 l_unit_meas_lookup_code VARCHAR2(100);
1900 l_funded_value NUMBER;
1901 l_quantity_funded NUMBER;
1902 l_amount_funded NUMBER;
1903 l_quantity_received NUMBER;
1904 l_amount_received NUMBER;
1905 l_quantity_delivered NUMBER;
1906 l_amount_delivered NUMBER;
1907 l_quantity_billed NUMBER;
1908 l_amount_billed NUMBER;
1909 l_quantity_cancelled NUMBER;
1910 l_amount_cancelled NUMBER;
1911 l_return_status VARCHAR2(100);
1912 -- <CLM END>
1913
1914 BEGIN
1915
1916 IF (g_asn_debug = 'Y') THEN
1917 asn_debug.put_line(' in get_correction_quantity');
1918 end if;
1919 IF p_correction_type = 'NEGATIVE' THEN
1920
1921 /*
1922 ** Return transactions and negative corrections have the
1923 ** same logic for getting available quantity.
1924 */
1925
1926 IF (p_parent_transaction_type IN ('UNORDERED', 'RECEIVE', 'MATCH',
1927 'TRANSFER', 'ACCEPT', 'REJECT')) THEN
1928
1929 /*
1930 ** All of the above transactions supply is stored in RCV_SUPPLY.
1931 ** Use get_transaction_quantity logic to get the available quantity
1932 ** and uom.
1933 */
1934
1935 IF (g_asn_debug = 'Y') THEN
1936 asn_debug.put_line(' Before get_transaction_quantity');
1937 end if;
1938 get_transaction_quantity(p_parent_id, p_available_quantity,
1939 p_unit_of_measure,p_secondary_available_qty);
1940
1941
1942 ELSIF (p_parent_transaction_type IN
1943 ('RETURN TO VENDOR', 'RETURN TO CUSTOMER', 'DELIVER')) THEN
1944
1945 /*
1946 ** Return to Vendor and Deliver transactions do not have any
1947 ** supply associated with them. You need to get the available
1948 ** quantity from the actual transaction tables themselves.
1949 **
1950 ** Debug - Currently, (22-MAR-95) we do not support corrections
1951 ** to Return To Receiving transactions. However, it is a good
1952 ** candidate for an ER. If we do, we need to add to this function
1953 ** to handle this case.
1954 */
1955 IF (g_asn_debug = 'Y') THEN
1956 asn_debug.put_line(' Before get_deliver_quantity');
1957 end if;
1958
1959 get_deliver_quantity(p_parent_id, p_available_quantity,
1960 p_unit_of_measure,p_secondary_available_qty);
1961
1962 ELSE
1963
1964 /*
1965 ** The function was called with the wrong p_parent_transaction_type
1966 ** parameter. Raise an invalid parent transaction type exception.
1967 */
1968
1969 RAISE invalid_parent_trx_type;
1970
1971 END IF; /* Matches if (p_parent_transaction_type IN .... */
1972
1973 ELSIF p_correction_type = 'POSITIVE' THEN
1974
1975 /*
1976 ** In general, for +ve correction quantity, we need to calculate the
1977 ** outstanding quantity against the grand parent transaction. e.g.
1978 **
1979 ** Receipt 100 - p_grand_parent_id
1980 ** Transfer 60 - p_parent_id
1981 ** Correct ?? - p_transaction_type
1982 **
1983 ** To do a +ve correction against the transfer, we need to calculate
1984 ** the outstanding quantity against the Receipt (40).
1985 */
1986
1987 IF (p_parent_transaction_type IN ('RECEIVE', 'MATCH')) THEN
1988
1989 /*
1990 ** Need to calculate the outstanding quantity to be received
1991 ** either against the shipment or the PO depending on the
1992 ** receipt_source_code.
1993 ** This is the same logic to be used for get_receive_quantity().
1994 ** p_grand_parent_id is either the po_line_location_id or the
1995 ** rcv_shipment_line_id as the case may be.
1996 */
1997
1998 get_receive_quantity(p_grand_parent_id, p_receipt_source_code,
1999 p_available_quantity, p_tolerable_quantity,
2000 p_unit_of_measure,p_secondary_available_qty);
2001
2002 ELSIF (p_parent_transaction_type IN ('TRANSFER', 'ACCEPT', 'REJECT',
2003 'DELIVER', 'RETURN TO VENDOR', 'RETURN TO CUSTOMER')) THEN
2004
2005 /*
2006 ** Need to calculate the outstanding quantity against the parent
2007 ** of the above transactions. This will always be a receiving
2008 ** transaction. Hence, use get_transaction_quantity() function.
2009 ** p_grand_parent_id is the grand parent transaction for which
2010 ** we need to get the outstanding quantity.
2011 */
2012
2013 get_transaction_quantity(p_grand_parent_id, p_available_quantity,
2014 p_unit_of_measure,p_secondary_available_qty);
2015
2016 ELSIF (p_parent_transaction_type IN ('UNORDERED')) THEN
2017
2018 /*
2019 ** Need to calculate the outstanding quantity against the parent
2020 ** of the above transactions. This will always be a receiving
2021 ** transaction. Hence, use get_transaction_quantity() function.
2022 ** p_grand_parent_id is the grand parent transaction for which
2023 ** we need to get the outstanding quantity. The p_grand_parent_id
2024 ** which is set in the rcv_corrections_sv.post_query to be the
2025 ** parent transaction id
2026 */
2027
2028 get_transaction_quantity(p_grand_parent_id, p_available_quantity,
2029 p_unit_of_measure,p_secondary_available_qty);
2030 /*
2031 ** This is kind of goofy but since there are no limits on the
2032 ** positive correction to the unorderded receipt we have to just
2033 ** make this a huge quantity.
2034 */
2035
2036 p_available_quantity := 9999999999999999;
2037
2038 ELSE
2039
2040 /*
2041 ** The function was called with the wrong p_parent_transaction_type
2042 ** parameter. Raise an invalid parent transaction type exception.
2043 */
2044
2045 RAISE invalid_parent_trx_type;
2046
2047 END IF; /* Matches if p_parent_transaction_type IN .... */
2048
2049 /*
2050 ** Convert the available quantity and tolerable quantity to the
2051 ** parent's uom. This is neccessary because in the case of a +ve
2052 ** correction, the available quantity will be in the grand parent's
2053 ** unit of measure.
2054 */
2055
2056 /*
2057 ** Get the parent transaction's info.
2058 */
2059
2060 x_progress := '005';
2061
2062 select rt.unit_of_measure,
2063 rsl.item_id
2064 into x_parent_uom,
2065 x_item_id
2066 from rcv_transactions rt,
2067 rcv_shipment_lines rsl
2068 where rt.transaction_id = p_parent_id
2069 and rt.shipment_line_id = rsl.shipment_line_id;
2070
2071 /*
2072 ** Convert available quantity in the parent's unit of measure
2073 */
2074
2075 /*dbms_output.put_line ('get_correction_qty : p_unit_of_measure : '||
2076 p_unit_of_measure);
2077
2078 dbms_output.put_line ('get_correction_qty : x_parent_uom : '||
2079 x_parent_uom);*/
2080
2081
2082 /* Bug#1769067.smididud.Date:05/15/2001. */
2083
2084 IF ( (p_available_quantity <> 0) AND (p_unit_of_measure IS NOT NULL) ) THEN
2085
2086 po_uom_s.uom_convert(p_available_quantity, p_unit_of_measure,
2087 x_item_id, x_parent_uom,
2088 l_quantity_in_parent_uom -- Bug 2737257
2089 );
2090 p_available_quantity := l_quantity_in_parent_uom; -- Bug 2737257
2091
2092 END IF;
2093
2094 /*
2095 ** Convert the tolerable quantity to the parent's unit of measure
2096 */
2097
2098 IF (p_tolerable_quantity <> 0) THEN
2099
2100 -- <Bug 9342280 : Added for CLM project>
2101 SELECT rt.po_line_location_id
2102 INTO l_po_line_location_id
2103 FROM rcv_transactions rt
2104 WHERE rt.transaction_id = p_parent_id ;
2105
2106
2107 IF (g_asn_debug = 'Y') THEN
2108 asn_debug.put_line('before calling po_clm_intg_grp.is_clm_po()');
2109 asn_debug.put_line('line_location_id : ' || l_po_line_location_id);
2110 END IF;
2111
2112 IF l_po_line_location_id IS NOT NULL THEN
2113 l_is_clm_po := po_clm_intg_grp.is_clm_po( p_po_header_id => NULL,
2114 p_po_line_id => NULL,
2115 p_po_line_location_id => l_po_line_location_id,
2116 p_po_distribution_id => NULL);
2117 END IF;
2118
2119 IF (g_asn_debug = 'Y') THEN
2120 asn_debug.put_line('l_is_clm_po: ' || l_is_clm_po);
2121 END IF;
2122
2123 IF l_is_clm_po = 'Y' THEN
2124
2125 IF (g_asn_debug = 'Y') THEN
2126 asn_debug.put_line(' before po_clm_intg_grp.get_funding_info()');
2127 end if;
2128
2129 po_clm_intg_grp.get_funding_info( p_po_header_id => NULL,
2130 p_po_line_id => NULL,
2131 p_line_location_id => l_po_line_location_id,
2132 p_po_distribution_id => NULL,
2133 x_distribution_type => l_distribution_type,
2134 x_matching_basis => l_matching_basis,
2135 x_accrue_on_receipt_flag => l_accrue_on_receipt_flag,
2136 x_code_combination_id => l_code_combination_id,
2137 x_budget_account_id => l_budget_account_id,
2138 x_partial_funded_flag => l_partial_funded_flag,
2139 x_unit_meas_lookup_code => l_unit_meas_lookup_code,
2140 x_funded_value => l_funded_value,
2141 x_quantity_funded => l_quantity_funded,
2142 x_amount_funded => l_amount_funded,
2143 x_quantity_received => l_quantity_received,
2144 x_amount_received => l_amount_received,
2145 x_quantity_delivered => l_quantity_delivered,
2146 x_amount_delivered => l_amount_delivered,
2147 x_quantity_billed => l_quantity_billed,
2148 x_amount_billed => l_amount_billed,
2149 x_quantity_cancelled => l_quantity_cancelled,
2150 x_amount_cancelled => l_amount_cancelled,
2151 x_return_status => l_return_status
2152 );
2153
2154 IF (g_asn_debug = 'Y') THEN
2155 asn_debug.put_line('l_partial_funded_flag : ' || l_partial_funded_flag);
2156 asn_debug.put_line('l_quantity_funded: ' || l_quantity_funded);
2157 asn_debug.put_line('l_quantity_received : ' || l_quantity_received);
2158 asn_debug.put_line('l_quantity_cancelled: ' || l_quantity_cancelled);
2159 END IF;
2160
2161 END IF;
2162
2163 IF l_is_clm_po = 'Y' AND l_partial_funded_flag = 'Y' THEN
2164 IF (g_asn_debug = 'Y') THEN
2165 asn_debug.put_line('set p_tolerable_quantity for clm po');
2166 END IF;
2167
2168 p_tolerable_quantity := p_available_quantity;
2169
2170 ELSE
2171 IF (g_asn_debug = 'Y') THEN
2172 asn_debug.put_line('set p_tolerable_quantity for non-clm po');
2173 END IF;
2174 -- <CLM END>
2175
2176
2177 po_uom_s.uom_convert(p_tolerable_quantity, p_unit_of_measure,
2178 x_item_id, x_parent_uom,
2179 l_quantity_in_parent_uom -- Bug 2737257
2180 );
2181 p_tolerable_quantity := l_quantity_in_parent_uom; -- Bug 2737257
2182
2183 END IF; -- <Bug 9342280 : Added for CLM project>
2184
2185 END IF;
2186
2187 /*
2188 ** Return parent unit of measure
2189 */
2190
2191 p_unit_of_measure := x_parent_uom;
2192
2193 END IF; /* Matches if p_correction_type = 'NEGATIVE' .... */
2194
2195
2196 EXCEPTION
2197
2198 WHEN invalid_parent_trx_type THEN
2199
2200 /*
2201 ** debug - need to define a new message and also need to understand
2202 ** how exactly to handle application error messages. A call to
2203 ** some generic API is needed.
2204 */
2205
2206 RAISE;
2207
2208
2209 WHEN OTHERS THEN
2210
2211 po_message_s.sql_error('get_correction_quantity', x_progress, sqlcode);
2212
2213 RAISE;
2214
2215 END get_correction_quantity;
2216
2217 /*===========================================================================
2218
2219 PROCEDURE NAME: get_correction_amount()
2220
2221 ===========================================================================*/
2222
2223 PROCEDURE get_correction_amount(p_correction_type IN VARCHAR2,
2224 p_parent_transaction_type IN VARCHAR2,
2225 p_receipt_source_code IN VARCHAR2,
2226 p_parent_id IN NUMBER,
2227 p_grand_parent_id IN NUMBER,
2228 p_available_amount IN OUT NOCOPY NUMBER,
2229 p_tolerable_amount IN OUT NOCOPY NUMBER ) IS
2230
2231 x_progress VARCHAR2(3) := NULL;
2232 x_trx_amount NUMBER := 0;
2233 X_interface_amount NUMBER := 0;
2234 invalid_parent_trx_type EXCEPTION;
2235
2236 -- <Bug 9342280 : Added for CLM project>
2237 l_po_line_location_id NUMBER;
2238 l_is_clm_po VARCHAR2(5) := 'N';
2239 l_distribution_type VARCHAR2(100);
2240 l_matching_basis VARCHAR2(100);
2241 l_accrue_on_receipt_flag VARCHAR2(100);
2242 l_code_combination_id NUMBER;
2243 l_budget_account_id NUMBER;
2244 l_partial_funded_flag VARCHAR2(5) := 'N';
2245 l_unit_meas_lookup_code VARCHAR2(100);
2246 l_funded_value NUMBER;
2247 l_quantity_funded NUMBER;
2248 l_amount_funded NUMBER;
2249 l_quantity_received NUMBER;
2250 l_amount_received NUMBER;
2251 l_quantity_delivered NUMBER;
2252 l_amount_delivered NUMBER;
2253 l_quantity_billed NUMBER;
2254 l_amount_billed NUMBER;
2255 l_quantity_cancelled NUMBER;
2256 l_amount_cancelled NUMBER;
2257 l_return_status VARCHAR2(100);
2258 -- <CLM END>
2259
2260 BEGIN
2261
2262 IF (g_asn_debug = 'Y') THEN
2263 asn_debug.put_line('p_correction_type '||p_correction_type );
2264 asn_debug.put_line('p_parent_transaction_type '||p_parent_transaction_type );
2265 asn_debug.put_line('p_receipt_source_code '||p_receipt_source_code );
2266 asn_debug.put_line('p_parent_id '||p_parent_id );
2267 asn_debug.put_line('p_grand_parent_id '||p_grand_parent_id );
2268 end if;
2269 IF p_correction_type = 'NEGATIVE' THEN
2270
2271 /*
2272 ** Return transactions and negative corrections have the
2273 ** same logic for getting available quantity.
2274 */
2275
2276 IF p_parent_transaction_type = 'RECEIVE' THEN
2277
2278 /*
2279 ** All of the above transactions supply is stored in RCV_SUPPLY.
2280 ** Use get_transaction_quantity logic to get the available quantity
2281 ** and uom.
2282 */
2283
2284 IF (g_asn_debug = 'Y') THEN
2285 asn_debug.put_line(' before get_transaction_amount');
2286 end if;
2287 get_transaction_amount(p_parent_id, p_available_amount);
2288
2289
2290 ELSIF p_parent_transaction_type = 'DELIVER' THEN
2291
2292 /*
2293 ** Return to Vendor and Deliver transactions do not have any
2294 ** supply associated with them. You need to get the available
2295 ** quantity from the actual transaction tables themselves.
2296 **
2297 ** Debug - Currently, (22-MAR-95) we do not support corrections
2298 ** to Return To Receiving transactions. However, it is a good
2299 ** candidate for an ER. If we do, we need to add to this function
2300 ** to handle this case.
2301 */
2302
2303 IF (g_asn_debug = 'Y') THEN
2304 asn_debug.put_line(' before get_deliver_amount');
2305 end if;
2306 get_deliver_amount(p_parent_id, p_available_amount);
2307
2308 ELSE
2309
2310 /*
2311 ** The function was called with the wrong p_parent_transaction_type
2312 ** parameter. Raise an invalid parent transaction type exception.
2313 */
2314
2315 RAISE invalid_parent_trx_type;
2316
2317 END IF; /* Matches if (p_parent_transaction_type IN .... */
2318
2319 ELSIF p_correction_type = 'POSITIVE' THEN
2320
2321 /*
2322 ** In general, for +ve correction quantity, we need to calculate the
2323 ** outstanding quantity against the grand parent transaction. e.g.
2324 **
2325 ** Receipt 100 - p_grand_parent_id
2326 ** Transfer 60 - p_parent_id
2327 ** Correct ?? - p_transaction_type
2328 **
2329 ** To do a +ve correction against the transfer, we need to calculate
2330 ** the outstanding quantity against the Receipt (40).
2331 */
2332
2333 IF p_parent_transaction_type = 'RECEIVE' THEN
2334
2335 /*
2336 ** Need to calculate the outstanding quantity to be received
2337 ** either against the shipment or the PO depending on the
2338 ** receipt_source_code.
2339 ** This is the same logic to be used for get_receive_quantity().
2340 ** p_grand_parent_id is either the po_line_location_id or the
2341 ** rcv_shipment_line_id as the case may be.
2342 */
2343
2344 IF (g_asn_debug = 'Y') THEN
2345 asn_debug.put_line(' before get_receive_amount');
2346 end if;
2347 get_receive_amount(p_grand_parent_id, p_receipt_source_code,
2348 p_available_amount, p_tolerable_amount);
2349
2350 ELSIF p_parent_transaction_type = 'DELIVER' THEN
2351
2352 /*
2353 ** Need to calculate the outstanding quantity against the parent
2354 ** of the above transactions. This will always be a receiving
2355 ** transaction. Hence, use get_transaction_quantity() function.
2356 ** p_grand_parent_id is the grand parent transaction for which
2357 ** we need to get the outstanding quantity.
2358 */
2359
2360 IF (g_asn_debug = 'Y') THEN
2361 asn_debug.put_line(' before get_transaction_amount');
2362 end if;
2363 get_transaction_amount(p_grand_parent_id, p_available_amount);
2364
2365 ELSE
2366
2367 /*
2368 ** The function was called with the wrong p_parent_transaction_type
2369 ** parameter. Raise an invalid parent transaction type exception.
2370 */
2371
2372 RAISE invalid_parent_trx_type;
2373
2374 END IF; /* Matches if p_parent_transaction_type IN .... */
2375
2376
2377 -- <Bug 9342280 : Added for CLM project>
2378 IF (p_tolerable_amount <> 0) THEN
2379
2380 SELECT rt.po_line_location_id
2381 INTO l_po_line_location_id
2382 FROM rcv_transactions rt
2383 WHERE rt.transaction_id = p_parent_id ;
2384
2385
2386 IF (g_asn_debug = 'Y') THEN
2387 asn_debug.put_line('before calling po_clm_intg_grp.is_clm_po()');
2388 asn_debug.put_line('line_location_id : ' || l_po_line_location_id);
2389 END IF;
2390
2391 IF l_po_line_location_id IS NOT NULL THEN
2392 l_is_clm_po := po_clm_intg_grp.is_clm_po( p_po_header_id => NULL,
2393 p_po_line_id => NULL,
2394 p_po_line_location_id => l_po_line_location_id,
2395 p_po_distribution_id => NULL);
2396 END IF;
2397
2398 IF (g_asn_debug = 'Y') THEN
2399 asn_debug.put_line('l_is_clm_po: ' || l_is_clm_po);
2400 END IF;
2401
2402 IF l_is_clm_po = 'Y' THEN
2403
2404 IF (g_asn_debug = 'Y') THEN
2405 asn_debug.put_line(' before po_clm_intg_grp.get_funding_info()');
2406 end if;
2407
2408 po_clm_intg_grp.get_funding_info( p_po_header_id => NULL,
2409 p_po_line_id => NULL,
2410 p_line_location_id => l_po_line_location_id,
2411 p_po_distribution_id => NULL,
2412 x_distribution_type => l_distribution_type,
2413 x_matching_basis => l_matching_basis,
2414 x_accrue_on_receipt_flag => l_accrue_on_receipt_flag,
2415 x_code_combination_id => l_code_combination_id,
2416 x_budget_account_id => l_budget_account_id,
2417 x_partial_funded_flag => l_partial_funded_flag,
2418 x_unit_meas_lookup_code => l_unit_meas_lookup_code,
2419 x_funded_value => l_funded_value,
2420 x_quantity_funded => l_quantity_funded,
2421 x_amount_funded => l_amount_funded,
2422 x_quantity_received => l_quantity_received,
2423 x_amount_received => l_amount_received,
2424 x_quantity_delivered => l_quantity_delivered,
2425 x_amount_delivered => l_amount_delivered,
2426 x_quantity_billed => l_quantity_billed,
2427 x_amount_billed => l_amount_billed,
2428 x_quantity_cancelled => l_quantity_cancelled,
2429 x_amount_cancelled => l_amount_cancelled,
2430 x_return_status => l_return_status
2431 );
2432
2433 IF (g_asn_debug = 'Y') THEN
2434 asn_debug.put_line('l_partial_funded_flag : ' || l_partial_funded_flag);
2435 asn_debug.put_line('l_amount_funded: ' || l_amount_funded);
2436 asn_debug.put_line('l_amount_received : ' || l_amount_received);
2437 asn_debug.put_line('l_amount_cancelled: ' || l_amount_cancelled);
2438 END IF;
2439
2440 END IF;
2441
2442 IF l_is_clm_po = 'Y' AND l_partial_funded_flag = 'Y' THEN
2443 IF (g_asn_debug = 'Y') THEN
2444 asn_debug.put_line('set p_tolerable_amount for CLM PO');
2445 END IF;
2446
2447 p_tolerable_amount := p_available_amount;
2448
2449 END IF;
2450
2451
2452 END IF;
2453 -- <CLM END>
2454
2455 END IF; /* Matches if p_correction_type = 'NEGATIVE' .... */
2456
2457
2458 EXCEPTION
2459
2460 WHEN invalid_parent_trx_type THEN
2461
2462 /*
2463 ** debug - need to define a new message and also need to understand
2464 ** how exactly to handle application error messages. A call to
2465 ** some generic API is needed.
2466 */
2467
2468 RAISE;
2469
2470
2471 WHEN OTHERS THEN
2472
2473 po_message_s.sql_error('get_correction_amount', x_progress, sqlcode);
2474
2475 RAISE;
2476
2477 END get_correction_amount;
2478
2479 /* modified for bug 13892629
2480 * Override this procedure by add one public precedure get_deliver_quantity
2481 */
2482 PROCEDURE get_deliver_quantity(p_transaction_id IN NUMBER,
2483 p_available_quantity IN OUT NOCOPY NUMBER,
2484 p_unit_of_measure IN OUT NOCOPY VARCHAR2,
2485 p_secondary_available_qty IN OUT NOCOPY NUMBER ) IS
2486 BEGIN
2487
2488 get_deliver_quantity(p_transaction_id,
2489 NULL,
2490 p_available_quantity,
2491 p_unit_of_measure,
2492 p_secondary_available_qty);
2493
2494 END get_deliver_quantity;
2495
2496 /*===========================================================================
2497
2498 PROCEDURE NAME: get_deliver_quantity()
2499
2500 ===========================================================================*/
2501 PROCEDURE get_deliver_quantity(p_transaction_id IN NUMBER,
2502 p_interface_transaction_id IN NUMBER, /*added in bug 13892629*/
2503 p_available_quantity IN OUT NOCOPY NUMBER,
2504 p_unit_of_measure IN OUT NOCOPY VARCHAR2,
2505 p_secondary_available_qty IN OUT NOCOPY NUMBER ) IS
2506 x_progress VARCHAR2(3) := NULL;
2507 x_deliver_quantity NUMBER := 0;
2508 x_transaction_quantity NUMBER := 0;
2509 x_trx_quantity NUMBER := 0; /* in primary uom */
2510 x_interface_quantity NUMBER := 0; /* in primary uom */
2511 primary_trx_qty NUMBER := 0; /* in primary uom */
2512 x_deliver_uom VARCHAR2(26);
2513 x_primary_uom VARCHAR2(26);
2514 use_primary_uom VARCHAR2(26) := NULL;
2515 x_item_id NUMBER;
2516 x_interface_qty_in_trx_uom NUMBER;
2517
2518 /* Bug# 1548597 */
2519 x_secondary_deliver_quantity NUMBER := 0;
2520 x_secondary_deliver_uom VARCHAR2(26);
2521 x_secondary_interface_qty NUMBER := 0;
2522 x_secondary_trx_quantity NUMBER := 0;
2523 -- end bug 1548597
2524
2525 /* Bug 3735987 Start declarations*/
2526 l_consigned_flag rcv_transactions.consigned_flag%TYPE;
2527 l_org_id rcv_transactions.organization_id%TYPE;
2528 l_consigned_quantity NUMBER;
2529 l_opm_installed BOOLEAN;
2530 l_opm_process_org VARCHAR2(1);
2531 l_vendor_site_id rcv_transactions.vendor_site_id%TYPE;
2532 l_subinventory rcv_transactions.subinventory%TYPE;
2533 l_locator_id rcv_transactions.locator_id%TYPE;
2534 l_po_header_id rcv_transactions.po_header_id%TYPE;
2535 l_po_line_id rcv_transactions.po_line_id%TYPE;
2536 l_item_revision rcv_shipment_lines.item_revision%TYPE;
2537 l_primary_rt_uom rcv_transactions.primary_unit_of_measure%TYPE;
2538
2539 l_return_status VARCHAR2(1);
2540 l_msg_count NUMBER;
2541 l_msg_data VARCHAR2(2000);
2542 l_revision_control VARCHAR2(1);
2543 l_quantity_on_hand NUMBER;
2544 l_consigned_deliver_uom_qty NUMBER;
2545 /* Bug 3735987 End declarations*/
2546
2547 x_rtv_running_qty NUMBER; -- rtv project
2548 x_rtv_running_sec_qty NUMBER; -- rtv project
2549
2550 /* bug 13892629 */
2551 l_lpn_id NUMBER;
2552
2553 BEGIN
2554
2555 x_progress := '005';
2556
2557 /*
2558 ** Get available transaction quantity information.
2559 */
2560
2561 /* Bug 3735987 : If the item is consigned then we need to check the quantity
2562 ** available in the consigned stock. We cannot have returns or corrections
2563 ** on the quantity in the Regular stock.
2564 */
2565
2566 select rt.quantity,
2567 rt.unit_of_measure,
2568 rsl.item_id,
2569 /* bug# 1548597 */
2570 rt.secondary_quantity,
2571 rt.secondary_unit_of_measure,
2572 --end bug # 1548597
2573 /* Bug 3735987 Start */
2574 rt.consigned_flag,
2575 rt.organization_id,
2576 rt.vendor_site_id,
2577 rt.subinventory,
2578 rt.locator_id,
2579 rt.po_header_id,
2580 rt.po_line_id,
2581 rsl.item_revision,
2582 lpn_id, /*added in bug 13892629 */
2583 rt.primary_unit_of_measure
2584 into x_deliver_quantity,
2585 x_deliver_uom,
2586 x_item_id,
2587 x_secondary_deliver_quantity,
2588 x_secondary_deliver_uom,
2589 l_consigned_flag ,
2590 l_org_id,
2591 l_vendor_site_id,
2592 l_subinventory ,
2593 l_locator_id,
2594 l_po_header_id,
2595 l_po_line_id,
2596 l_item_revision,
2597 l_lpn_id, /*added in bug 13892629 */
2598 l_primary_rt_uom
2599 from rcv_transactions rt,
2600 rcv_shipment_lines rsl
2601 where rt.transaction_id = p_transaction_id
2602 and rt.shipment_line_id = rsl.shipment_line_id;
2603
2604 x_progress := '010';
2605
2606
2607 /*
2608 ** Get any unprocessed receipt transaction against the
2609 ** parent transaction. x_interface_quantity is in primary uom.
2610 **
2611 ** The min(primary_uom) is neccessary because the
2612 ** select may return multiple rows and we only want one value
2613 ** to be returned. Having a sum and min group function in the
2614 ** select ensures that this sql statement will not raise a
2615 ** no_data_found exception even if no rows are returned.
2616 */
2617
2618 SELECT nvl(sum(decode(transaction_type,
2619 'CORRECT', -1 * (decode(nvl(order_transaction_id,-999),-999,primary_quantity,nvl(interface_transaction_qty,0))),
2620 decode(nvl(order_transaction_id,-999),-999,primary_quantity,nvl(interface_transaction_qty,0))
2621 )),0),
2622 min(primary_unit_of_measure)
2623 INTO x_interface_quantity,
2624 x_primary_uom
2625 FROM rcv_transactions_interface
2626 WHERE (transaction_status_code = 'PENDING'
2627 and processing_status_code <> 'ERROR')
2628 AND parent_transaction_id = p_transaction_id;
2629
2630 IF (g_asn_debug = 'Y') THEN
2631 asn_debug.put_line('p_transaction_id = ' || p_transaction_id);
2632 asn_debug.put_line('x_interface_quantity = ' || x_interface_quantity);
2633 END IF;
2634
2635 -- rtv project : start
2636 SELECT nvl( sum(primary_quantity),0)
2637 INTO x_rtv_running_qty
2638 FROM rcv_transactions_interface rti
2639 WHERE parent_transaction_id = p_transaction_id
2640 AND processing_status_code = 'RUNNING'
2641 AND transaction_type IN ('RETURN TO VENDOR', 'RETURN TO RECEIVING')
2642 AND exists ( select 1 from wsh_delivery_details wdd
2643 where wdd.delivery_detail_id = rti.interface_source_line_id
2644 and wdd.source_code = 'RTV');
2645
2646 x_interface_quantity := x_interface_quantity - x_rtv_running_qty;
2647
2648 IF (g_asn_debug = 'Y') THEN
2649 asn_debug.put_line('x_rtv_running_qty = ' || x_rtv_running_qty);
2650 asn_debug.put_line('x_interface_quantity = ' || x_interface_quantity);
2651 END IF;
2652
2653 -- rtv project : end
2654
2655 IF (x_interface_quantity = 0) THEN
2656
2657 /*
2658 ** There is no unprocessed quantity. Simply set the
2659 ** x_interface_qty_in_trx_uom to 0. There is no need for uom
2660 ** conversion.
2661 */
2662
2663 x_interface_qty_in_trx_uom := 0;
2664
2665 ELSE
2666
2667 /*
2668 ** There is unprocessed quantity. Convert it to the transaction uom
2669 ** so that the available quantity can be calculated in the trx uom
2670 */
2671
2672 po_uom_s.uom_convert(x_interface_quantity, x_primary_uom, x_item_id,
2673 x_deliver_uom, x_interface_qty_in_trx_uom);
2674
2675 END IF;
2676
2677 /* Bug 1548597 */
2678 SELECT nvl(sum(decode(transaction_type,
2679 'CORRECT', -1 * secondary_quantity,
2680 secondary_quantity)),0)
2681 INTO x_secondary_interface_qty
2682 FROM rcv_transactions_interface
2683 WHERE (transaction_status_code = 'PENDING'
2684 and processing_status_code <> 'ERROR')
2685 AND parent_transaction_id = p_transaction_id;
2686
2687 IF (g_asn_debug = 'Y') THEN
2688 asn_debug.put_line('x_secondary_interface_qty = ' || x_secondary_interface_qty);
2689 END IF;
2690
2691 -- rtv project : start
2692 SELECT nvl(sum(secondary_quantity),0)
2693 INTO x_rtv_running_sec_qty
2694 FROM rcv_transactions_interface rti
2695 WHERE parent_transaction_id = p_transaction_id
2696 AND processing_status_code = 'RUNNING'
2697 AND transaction_type IN ('RETURN TO VENDOR', 'RETURN TO RECEIVING')
2698 AND exists ( select 1 from wsh_delivery_details wdd
2699 where wdd.delivery_detail_id = rti.interface_source_line_id
2700 and wdd.source_code = 'RTV');
2701
2702 x_secondary_interface_qty := x_secondary_interface_qty - (2 * x_rtv_running_sec_qty);
2703
2704 IF (g_asn_debug = 'Y') THEN
2705 asn_debug.put_line('x_rtv_running_sec_qty = ' || x_rtv_running_sec_qty);
2706 asn_debug.put_line('x_secondary_interface_qty = ' || x_secondary_interface_qty);
2707 END IF;
2708
2709 -- rtv project : end
2710
2711
2712 p_secondary_available_qty := x_secondary_deliver_quantity - x_secondary_interface_qty;
2713
2714 -- end bug# 1548507
2715
2716 /*
2717 ** Calculate the quantity available to be transacted
2718 */
2719
2720 p_available_quantity := x_deliver_quantity - x_interface_qty_in_trx_uom;
2721
2722 /*
2723 ** Return the parent transactions unit of measure
2724 */
2725
2726 p_unit_of_measure := x_deliver_uom;
2727
2728
2729 /*
2730 ** Get any rows already precessed against this transaction
2731 ** parent transaction. x_interface_quantity is in primary uom.
2732 **
2733 ** The min(primary_uom) is neccessary because the
2734 ** select may return multiple rows and we only want one value
2735 ** to be returned. Having a sum and min group function in the
2736 ** select ensures that this sql statement will not raise a
2737 ** no_data_found exception even if no rows are returned.
2738 */
2739
2740 SELECT nvl(sum(decode(transaction_type,
2741 'CORRECT', -1 * primary_quantity,
2742 primary_quantity)),0),
2743 min(primary_unit_of_measure)
2744 INTO x_trx_quantity,
2745 x_primary_uom
2746 FROM rcv_transactions
2747 WHERE parent_transaction_id = p_transaction_id
2748 AND transaction_type in ('CORRECT','RETURN TO RECEIVING');
2749
2750 IF (x_primary_uom IS NOT NULL) THEN
2751
2752 use_primary_uom := x_primary_uom;
2753
2754 END IF;
2755
2756 /* Bug# 1548597 */
2757 SELECT nvl(sum(decode(transaction_type,
2758 'CORRECT', -1 * secondary_quantity,
2759 secondary_quantity)),0)
2760 INTO x_secondary_trx_quantity
2761 FROM rcv_transactions
2762 WHERE parent_transaction_id = p_transaction_id
2763 AND transaction_type in ('CORRECT','RETURN TO RECEIVING');
2764 --end Bug# 1548597
2765
2766 IF (x_trx_quantity <> 0 AND use_primary_uom IS NOT NULL) THEN
2767
2768 /*
2769 ** There is unprocessed quantity. Convert it to the transaction uom
2770 ** so that the available quantity can be calculated in the trx uom
2771 */
2772 /*dbms_output.put_line ('get_correction_qty : use_primary_uom: '||
2773 use_primary_uom);
2774
2775 dbms_output.put_line ('get_correction_qty : p_unit_of_measure : '||
2776 p_unit_of_measure);*/
2777
2778
2779 x_progress := '015';
2780
2781 /* Bug# 2274636 : UOM convert function should be called only
2782 ** when uoms are different. Calling the UOM convert function
2783 ** at this place affects the computation of available quantity
2784 */
2785
2786 If ( use_primary_uom = p_unit_of_measure ) then
2787 primary_trx_qty := x_trx_quantity ;
2788 else
2789 po_uom_s.uom_convert(x_trx_quantity,
2790 use_primary_uom, x_item_id,
2791 p_unit_of_measure, primary_trx_qty);
2792 end if;
2793
2794
2795 /*
2796 ** Calculate the quantity available to be transacted
2797 */
2798
2799 p_available_quantity := p_available_quantity - primary_trx_qty;
2800
2801 /* Bug 1548597 */
2802 p_secondary_available_qty := p_secondary_available_qty - x_secondary_trx_quantity;
2803 --end Bug# 1548597
2804
2805
2806 END IF;
2807
2808 /*dbms_output.put_line ('Convert = ' || to_char(primary_trx_qty));
2809 dbms_output.put_line ('Avail = ' || to_char(p_available_quantity ));*/
2810
2811 /*
2812 ** Bug#4587282
2813 ** Moved the following piece of code here so that the stock in
2814 ** consigned inventory would be checked against the actual
2815 ** quantity available for the parent transaction
2816 */
2817 IF (l_consigned_flag = 'Y') THEN
2818 /* INVCONV PBAMB BEGIN - Remove the restriction for consigned items */
2819 /* If item is consigned, check if OPM is installed and the organization is a
2820 ** OPM process organization. We do not need to check the stock availability
2821 ** for OPM enabled organization.
2822 DECLARE
2823 opm_status varchar2(10);
2824 opm_ind varchar2(10);
2825 opm_ora_schema varchar2(10);
2826 BEGIN
2827 l_opm_installed := fnd_installation.get_app_info('GMI',opm_status,opm_ind,opm_ora_schema);
2828 l_opm_process_org := PO_GML_DB_COMMON.check_process_org(l_org_id);
2829 END;
2830
2831 IF NOT(( l_opm_installed = TRUE ) AND (l_opm_process_org = 'Y')) THEN
2832 */
2833 /* Check for the item revision control. If item is not revision controlled
2834 ** pass the item revision as null.
2835 */
2836 SELECT decode(msi.revision_qty_control_code,1,'F',2,'T')
2837 INTO l_revision_control
2838 FROM mtl_system_items_b msi
2839 WHERE msi.inventory_item_id = x_item_id
2840 AND msi.organization_id = l_org_id;
2841
2842 IF ( l_revision_control = 'F' ) THEN
2843 l_item_revision := NULL;
2844 END IF;
2845
2846 /*begin fix of bug 13892629 */
2847 IF (p_interface_transaction_id IS NOT NULL) THEN
2848 select FROM_SUBINVENTORY,
2849 FROM_LOCATOR_ID
2850 into l_subinventory,
2851 l_locator_id
2852 from rcv_transactions_interface
2853 where interface_transaction_id = p_interface_transaction_id;
2854 END IF;
2855
2856 /* Call inventory API to get quantity available in consigned inventory.
2857 */
2858 /*For WMS org, as LPN involved, we need to do different validation
2859 This is the same as WMSTXERE.pld does */
2860 IF (l_lpn_id is not null and l_lpn_id > 0) THEN
2861
2862 INV_CONSIGNED_VALIDATIONS.GET_CONSIGNED_LPN_QUANTITY(
2863 x_return_status => l_return_status,
2864 x_return_msg => l_msg_data,
2865 p_tree_mode => 2,
2866 p_organization_id => l_org_id,
2867 p_owning_org_id => l_vendor_site_id,
2868 p_planning_org_id => NULL,
2869 p_inventory_item_id => x_item_id,
2870 p_is_revision_control => l_revision_control,
2871 p_is_lot_control => 'F',
2872 p_is_serial_control => 'F',
2873 p_revision => l_item_revision,
2874 p_lot_number => NULL,
2875 p_lot_expiration_date => NULL,
2876 p_subinventory_code => l_subinventory,
2877 p_locator_id => l_locator_id,
2878 p_source_type_id => 1,
2879 p_demand_source_line_id => l_po_line_id,
2880 p_demand_source_header_id => l_po_header_id,
2881 p_demand_source_name => NULL,
2882 p_onhand_source => 3,
2883 p_cost_group_id => NULL,
2884 p_query_mode => 1,
2885 p_lpn_id => l_lpn_id,
2886 x_qoh => l_quantity_on_hand,
2887 x_att => l_consigned_quantity);
2888
2889 ELSE
2890
2891 INV_CONSIGNED_VALIDATIONS_GRP.get_consigned_quantity (
2892 p_api_version_number => 1.0,
2893 p_init_msg_lst => 'F',
2894 x_return_status => l_return_status,
2895 x_msg_count => l_msg_count,
2896 x_msg_data => l_msg_data,
2897 p_tree_mode => NULL,
2898 p_organization_id => l_org_id,
2899 p_owning_org_id => l_vendor_site_id,
2900 p_planning_org_id => NULL,
2901 p_inventory_item_id => x_item_id,
2902 p_is_revision_control => l_revision_control,
2903 p_is_lot_control => 'F',
2904 p_is_serial_control => 'F',
2905 p_revision => l_item_revision,
2906 p_lot_number => NULL,
2907 p_lot_expiration_date => NULL,
2908 p_subinventory_code => l_subinventory,
2909 p_locator_id => l_locator_id,
2910 p_source_type_id => 1,
2911 p_demand_source_line_id => l_po_line_id,
2912 p_demand_source_header_id => l_po_header_id,
2913 p_demand_source_name => NULL,
2914 p_onhand_source => 3,
2915 p_cost_group_id => NULL,
2916 p_query_mode => 1,
2917 x_qoh => l_quantity_on_hand,
2918 x_att => l_consigned_quantity);
2919
2920 END IF;
2921 /*end fix of bug 13892629 */
2922
2923 IF (l_return_status = 'S') THEN
2924 /* The Inventory API returns the quantity in primary UOM. We need to
2925 ** convert the quantity in the deliver UOM.
2926 */
2927 IF ( l_primary_rt_uom <> x_deliver_uom ) THEN
2928 PO_UOM_S.uom_convert(l_consigned_quantity,
2929 l_primary_rt_uom,
2930 x_item_id,
2931 x_deliver_uom,
2932 l_consigned_deliver_uom_qty );
2933 ELSE
2934 l_consigned_deliver_uom_qty := l_consigned_quantity;
2935 END IF;
2936
2937 /* If consigned quantity is greater than or equal to the delivered quantity,
2938 ** then the available quantity is same as the quantity delivered. Else the
2939 ** available quantity is equal to the quantity in consigned inventory.
2940 */
2941 -- Bug 4587282 : use p_available_quantity instead of x_deliver_quantity in the IF stmt.
2942 /* IF ( l_consigned_deliver_uom_qty < x_deliver_quantity ) THEN
2943 x_deliver_quantity := l_consigned_deliver_uom_qty;
2944 END IF;
2945 */
2946 IF ( l_consigned_deliver_uom_qty < p_available_quantity ) THEN
2947 p_available_quantity := l_consigned_deliver_uom_qty;
2948 END IF;
2949 ELSE
2950 IF (g_asn_debug = 'Y') THEN
2951 asn_debug.put_line('Error in get_deliver_quantity() during call to INV consigned API');
2952 asn_debug.put_line('Return status : '||l_return_status);
2953 asn_debug.put_line('Error Message : '||SUBSTRB(l_msg_data,1,50));
2954 END IF;
2955 APP_EXCEPTION.RAISE_EXCEPTION;
2956 --END IF;
2957 END IF;
2958 END IF;
2959
2960 EXCEPTION
2961
2962 WHEN OTHERS THEN
2963
2964 po_message_s.sql_error('get_deliver_quantity', x_progress, sqlcode);
2965
2966 RAISE;
2967
2968 END get_deliver_quantity;
2969
2970 /*===========================================================================
2971
2972 PROCEDURE NAME: get_deliver_amount()
2973
2974 Algo: Amount available for deliver :=Total amount delivered + Corrections on delivery.
2975 ===========================================================================*/
2976
2977 PROCEDURE get_deliver_amount(p_transaction_id IN NUMBER,
2978 p_available_amount IN OUT NOCOPY NUMBER ) IS
2979
2980 x_progress VARCHAR2(3) := NULL;
2981 x_deliver_amount NUMBER := 0;
2982 x_transaction_amount NUMBER := 0;
2983 x_interface_amount NUMBER := 0;
2984
2985 BEGIN
2986
2987 x_progress := '005';
2988
2989 IF (g_asn_debug = 'Y') THEN
2990 asn_debug.put_line('in get_receive_amount ' );
2991 asn_debug.put_line('p_transaction_id '||p_transaction_id);
2992 END IF;
2993 /*
2994 ** Get available transaction amount information.
2995 */
2996
2997 select rt.amount
2998 into x_deliver_amount
2999 from rcv_transactions rt
3000 where rt.transaction_id = p_transaction_id;
3001
3002 IF (g_asn_debug = 'Y') THEN
3003 asn_debug.put_line('x_deliver_amount '||x_deliver_amount );
3004 end if;
3005 x_progress := '010';
3006
3007 /*
3008 ** Get any unprocessed receipt transaction against the
3009 ** parent transaction. x_interface_quantity is in primary uom.
3010 */
3011
3012 SELECT nvl(sum(decode(transaction_type,
3013 'CORRECT', -1 * (decode(nvl(order_transaction_id,-999),-999,amount,nvl(interface_transaction_amt,0))),
3014 decode(nvl(order_transaction_id,-999),-999,amount,nvl(interface_transaction_amt,0))
3015 )),0)
3016 INTO x_interface_amount
3017 FROM rcv_transactions_interface
3018 WHERE (transaction_status_code = 'PENDING'
3019 and processing_status_code <> 'ERROR')
3020 AND parent_transaction_id = p_transaction_id;
3021
3022 IF (g_asn_debug = 'Y') THEN
3023 asn_debug.put_line('x_interface_amount '||x_interface_amount );
3024 end if;
3025 /*
3026 ** Calculate the quantity available to be transacted
3027 */
3028
3029 p_available_amount := x_deliver_amount - x_interface_amount;
3030
3031 IF (g_asn_debug = 'Y') THEN
3032 asn_debug.put_line('p_available_amount '||p_available_amount );
3033 end if;
3034 /*
3035 ** Get any rows already precessed against this transaction
3036 ** parent transaction. x_interface_quantity is in primary uom.
3037 **
3038 ** The min(primary_uom) is neccessary because the
3039 ** select may return multiple rows and we only want one value
3040 ** to be returned. Having a sum and min group function in the
3041 ** select ensures that this sql statement will not raise a
3042 ** no_data_found exception even if no rows are returned.
3043 */
3044
3045 SELECT nvl(sum(amount),0)
3046 INTO x_transaction_amount
3047 FROM rcv_transactions
3048 WHERE parent_transaction_id = p_transaction_id
3049 AND transaction_type = 'CORRECT';
3050
3051 IF (g_asn_debug = 'Y') THEN
3052 asn_debug.put_line('x_transaction_amount '||x_transaction_amount );
3053 end if;
3054 x_progress := '015';
3055
3056 /*
3057 ** Calculate the quantity available to be transacted
3058 */
3059
3060 p_available_amount := p_available_amount + x_transaction_amount;
3061 IF (g_asn_debug = 'Y') THEN
3062 asn_debug.put_line('p_available_amount 1 '||p_available_amount );
3063 end if;
3064
3065 EXCEPTION
3066
3067 WHEN OTHERS THEN
3068
3069 po_message_s.sql_error('get_deliver_amount', x_progress, sqlcode);
3070
3071 RAISE;
3072
3073 END get_deliver_amount;
3074
3075 /*===========================================================================
3076
3077 PROCEDURE NAME: get_po_dist_quantity()
3078
3079 ===========================================================================*/
3080
3081 PROCEDURE get_po_dist_quantity(p_po_distribution_id IN NUMBER,
3082 p_available_quantity IN OUT NOCOPY NUMBER,
3083 p_tolerable_quantity IN OUT NOCOPY NUMBER,
3084 p_unit_of_measure IN OUT NOCOPY VARCHAR2) IS
3085
3086 x_progress VARCHAR2(3) := NULL;
3087 x_deliver_quantity NUMBER := 0;
3088 x_balance_receipt_quantity NUMBER := 0;
3089 x_interface_quantity NUMBER := 0; /* in primary uom */
3090 x_primary_uom VARCHAR2(26);
3091 x_item_id NUMBER;
3092 x_interface_qty_in_trx_uom NUMBER;
3093
3094 -- 1337787
3095 x_qty_rcv_tolerance NUMBER := 0;
3096 x_qty_ordered NUMBER := 0;
3097 x_qty_received NUMBER := 0;
3098 x_qty_cancelled NUMBER := 0;
3099 l_quantity NUMBER := 0; /* Bug 1710046 */
3100
3101 -- <Bug 9342280 : Added for CLM project>
3102 l_po_line_location_id NUMBER;
3103 l_is_clm_po VARCHAR2(5) := 'N';
3104 l_distribution_type VARCHAR2(100);
3105 l_matching_basis VARCHAR2(100);
3106 l_accrue_on_receipt_flag VARCHAR2(100);
3107 l_code_combination_id NUMBER;
3108 l_budget_account_id NUMBER;
3109 l_partial_funded_flag VARCHAR2(5) := 'N';
3110 l_unit_meas_lookup_code VARCHAR2(100);
3111 l_funded_value NUMBER;
3112 l_quantity_funded NUMBER;
3113 l_amount_funded NUMBER;
3114 l_quantity_received NUMBER;
3115 l_amount_received NUMBER;
3116 l_quantity_delivered NUMBER;
3117 l_amount_delivered NUMBER;
3118 l_quantity_billed NUMBER;
3119 l_amount_billed NUMBER;
3120 l_quantity_cancelled NUMBER;
3121 l_amount_cancelled NUMBER;
3122 l_return_status VARCHAR2(100);
3123 -- <CLM END>
3124
3125
3126 BEGIN
3127
3128 x_progress := '005';
3129
3130 BEGIN
3131 /* Bug 1710046 - The following sql is to get the quantity
3132 returned to receiving or quantity received but not delivered
3133 for a particular distribution for a PO having multiple
3134 Distribution. This quantity should not be shown in
3135 Enter Receipt form */
3136
3137 select nvl(sum(decode(supply_type_code,'RECEIVING',quantity,0)),0)
3138 into l_quantity
3139 from mtl_supply
3140 where po_distribution_id = p_po_distribution_id;
3141
3142 /* GMUDGAL - 04-FEB-98 - Bug #610897
3143 ** Here the problem was that if the shipment has been received
3144 ** within tolerance then in the C code we are removing the
3145 ** balance mtl_supply so the above select will raise no_data_found.
3146 ** We now select from po_distributions the quantity yet to be
3147 ** delivered so that after exploding the distributions we don't
3148 ** see zero quantities
3149 ** Also see 624832.
3150 */
3151
3152 select (pod.QUANTITY_ORDERED - nvl(pod.QUANTITY_DELIVERED,0) -
3153 nvl(pod.QUANTITY_CANCELLED,0)) qty,
3154 (poll.quantity - nvl(poll.quantity_received,0) -
3155 nvl(poll.quantity_cancelled,0)) qty_rcvd,
3156 pol.UNIT_MEAS_LOOKUP_CODE, -- should get it from po_lines actually
3157 pol.item_id,
3158 1 + (nvl(poll.qty_rcv_tolerance,0)/100), -- 1337787
3159 nvl(poll.quantity,0),
3160 nvl(poll.quantity_received,0),
3161 nvl(poll.quantity_cancelled,0),
3162 poll.line_location_id -- <Bug 9342280 : Added for CLM project>
3163 INTO p_available_quantity,
3164 x_balance_receipt_quantity,
3165 p_unit_of_measure,
3166 x_item_id,
3167 x_qty_rcv_tolerance,
3168 x_qty_ordered,
3169 x_qty_received,
3170 x_qty_cancelled,
3171 l_po_line_location_id -- <Bug 9342280 : Added for CLM project>
3172 from po_distributions_all pod, --<Shared Proc FPJ>
3173 po_line_locations_all poll, --<Shared Proc FPJ>
3174 po_lines_all pol --<Shared Proc FPJ>
3175 where pod.line_location_id = poll.line_location_id
3176 and pod.po_distribution_id = p_po_distribution_id
3177 and pod.po_line_id = pol.po_line_id;
3178
3179 -- <Bug 9342280 : Added for CLM project>
3180
3181 IF (g_asn_debug = 'Y') THEN
3182 asn_debug.put_line('before calling po_clm_intg_grp.is_clm_po()');
3183 END IF;
3184
3185 l_is_clm_po := po_clm_intg_grp.is_clm_po( p_po_header_id => NULL,
3186 p_po_line_id => NULL,
3187 p_po_line_location_id => l_po_line_location_id,
3188 p_po_distribution_id => p_po_distribution_id);
3189
3190 IF (g_asn_debug = 'Y') THEN
3191 asn_debug.put_line('line_location_id : ' || l_po_line_location_id);
3192 asn_debug.put_line('p_po_distribution_id : ' || p_po_distribution_id);
3193 asn_debug.put_line('l_is_clm_po: ' || l_is_clm_po);
3194 END IF;
3195
3196
3197 IF l_is_clm_po = 'Y' THEN
3198 IF (g_asn_debug = 'Y') THEN
3199 asn_debug.put_line(' before po_clm_intg_grp.get_funding_info()');
3200 end if;
3201
3202 po_clm_intg_grp.get_funding_info( p_po_header_id => NULL,
3203 p_po_line_id => NULL,
3204 p_line_location_id => l_po_line_location_id,
3205 p_po_distribution_id => p_po_distribution_id,
3206 x_distribution_type => l_distribution_type,
3207 x_matching_basis => l_matching_basis,
3208 x_accrue_on_receipt_flag => l_accrue_on_receipt_flag,
3209 x_code_combination_id => l_code_combination_id,
3210 x_budget_account_id => l_budget_account_id,
3211 x_partial_funded_flag => l_partial_funded_flag,
3212 x_unit_meas_lookup_code => l_unit_meas_lookup_code,
3213 x_funded_value => l_funded_value,
3214 x_quantity_funded => l_quantity_funded,
3215 x_amount_funded => l_amount_funded,
3216 x_quantity_received => l_quantity_received,
3217 x_amount_received => l_amount_received,
3218 x_quantity_delivered => l_quantity_delivered,
3219 x_amount_delivered => l_amount_delivered,
3220 x_quantity_billed => l_quantity_billed,
3221 x_amount_billed => l_amount_billed,
3222 x_quantity_cancelled => l_quantity_cancelled,
3223 x_amount_cancelled => l_amount_cancelled,
3224 x_return_status => l_return_status
3225 );
3226
3227 IF (g_asn_debug = 'Y') THEN
3228 asn_debug.put_line('l_partial_funded_flag : ' || l_partial_funded_flag);
3229 asn_debug.put_line('l_quantity_funded: ' || l_quantity_funded);
3230 asn_debug.put_line('l_quantity_delivered : ' || l_quantity_delivered);
3231 asn_debug.put_line('l_quantity_cancelled: ' || l_quantity_cancelled);
3232 END IF;
3233
3234 IF l_partial_funded_flag = 'Y' THEN
3235
3236 p_available_quantity := l_quantity_funded - l_quantity_delivered - l_quantity_cancelled;
3237 END IF;
3238
3239 END IF;
3240 -- <CLM END>
3241
3242 /* Bug 1710046 - Deducting the quantity received but not delivered
3243 ** from the available quantity as this quantity should not be
3244 ** shown in Enter Receipts form */
3245
3246 p_available_quantity := p_available_quantity - l_quantity;
3247
3248
3249 /*GMUDGAL 610897
3250 ** Check if there is some quantity which has been received
3251 ** but not delivered yet. In that case we want to show the
3252 ** quantity in the form as zero.
3253 */
3254
3255 if (p_available_quantity > 0) and
3256 (x_balance_receipt_quantity <= 0) then
3257 p_available_quantity := 0;
3258 p_unit_of_measure := '';
3259 end if;
3260
3261 exception
3262 when no_data_found then
3263 p_available_quantity := 0;
3264 p_unit_of_measure := '';
3265
3266 WHEN OTHERS THEN RAISE;
3267
3268 END;
3269
3270 x_progress := '015';
3271
3272 /* Bug# 2123470 : Primary Unit of Measure cannot have value
3273 for One time Items. So Added a decode statement to fetch
3274 unit_of_measure in case of One Time Items and Primary
3275 Unit of Measure for Inventory Items.
3276 */
3277
3278 /* Bug 10384588: for direct receive ,
3279 * 1. positive correction in RTI should reduce available qty,
3280 * negative correction in RTI should add available qty
3281 * 2. return to vendor would add available qty
3282 * 3. exclude 'DELIVER FOR CORRECTION','DELIVER','RETURN TO RECEIVING' in RTI computation
3283 * 4. when item_is null,only the primary uom is null then get uom instead of primar uom
3284 */
3285 /*
3286 SELECT nvl(sum(decode(transaction_type,
3287 'CORRECT', -1 * (decode(nvl(order_transaction_id,-999),-999,primary_quantity,nvl(interface_transaction_qty,0))),
3288 decode(nvl(order_transaction_id,-999),-999,primary_quantity,nvl(interface_transaction_qty,0))
3289 )),0),
3290 decode(min(item_id),null,min(unit_of_measure),min(primary_unit_of_measure))
3291 INTO x_interface_quantity,
3292 x_primary_uom
3293 FROM rcv_transactions_interface
3294 WHERE (transaction_status_code = 'PENDING'
3295 and processing_status_code <> 'ERROR')
3296 AND po_distribution_id = p_po_distribution_id;
3297 */
3298
3299 SELECT nvl(sum(decode(transaction_type,
3300 'RETURN TO VENDOR', -1 * (decode(nvl(order_transaction_id,-999),-999,primary_quantity,nvl(interface_transaction_qty,0))),
3301 decode(nvl(order_transaction_id,-999),-999,primary_quantity,nvl(interface_transaction_qty,0))
3302 )),0),
3303 decode(min(item_id),null
3304 ,decode(min(primary_unit_of_measure),null,min(unit_of_measure),min(primary_unit_of_measure))
3305 ,min(primary_unit_of_measure))
3306 INTO x_interface_quantity,
3307 x_primary_uom
3308 FROM rcv_transactions_interface
3309 WHERE (transaction_status_code = 'PENDING'
3310 and processing_status_code <> 'ERROR')
3311 AND po_distribution_id = p_po_distribution_id
3312 AND NOT EXISTS (SELECT 1 FROM rcv_transactions rt
3313 WHERE rt.transaction_type = 'DELIVER'
3314 AND rt.transaction_id = rcv_transactions_interface.parent_transaction_id)
3315 AND transaction_type NOT IN ('DELIVER','RETURN TO RECEIVING');
3316
3317 /* End Bug 10384588 */
3318
3319 IF (x_interface_quantity = 0) THEN
3320
3321 /*
3322 ** There is no unprocessed quantity. Simply set the
3323 ** x_interface_qty_in_trx_uom to 0. There is no need for uom
3324 ** conversion.
3325 */
3326
3327 x_interface_qty_in_trx_uom := 0;
3328
3329 ELSE
3330
3331 /*
3332 ** There is unprocessed quantity. Convert it to the transaction uom
3333 ** so that the available quantity can be calculated in the trx uom
3334 */
3335
3336 po_uom_s.uom_convert(x_interface_quantity, x_primary_uom, x_item_id,
3337 p_unit_of_measure, x_interface_qty_in_trx_uom);
3338
3339 END IF;
3340
3341 /*
3342 ** Calculate the quantity available to be transacted
3343 */
3344
3345 p_available_quantity := p_available_quantity - x_interface_qty_in_trx_uom;
3346
3347 IF (p_available_quantity < 0) THEN
3348
3349 p_available_quantity := 0;
3350
3351 END IF;
3352
3353 -- <Bug 9342280 : Added for CLM project>
3354 IF l_is_clm_po = 'Y' AND l_partial_funded_flag = 'Y' THEN
3355 IF (g_asn_debug = 'Y') THEN
3356 asn_debug.put_line('set p_tolerable_quantity for clm po');
3357 END IF;
3358
3359 p_tolerable_quantity := p_available_quantity;
3360
3361 ELSE
3362 IF (g_asn_debug = 'Y') THEN
3363 asn_debug.put_line('set p_tolerable_quantity for non-clm po');
3364 END IF;
3365 -- <CLM END>
3366
3367 -- 1337787
3368 p_tolerable_quantity := (x_qty_ordered * x_qty_rcv_tolerance)-
3369 x_qty_received - x_qty_cancelled -
3370 x_interface_qty_in_trx_uom;
3371 END IF; -- <Bug 9342280 : Added for CLM project>
3372
3373 IF (p_tolerable_quantity < 0) THEN
3374
3375 p_tolerable_quantity := 0;
3376
3377 END IF;
3378
3379 EXCEPTION
3380
3381 WHEN OTHERS THEN
3382
3383 po_message_s.sql_error('get_po_dist_quantity',
3384 x_progress, sqlcode);
3385
3386 RAISE;
3387
3388 END get_po_dist_quantity;
3389
3390
3391 /*===========================================================================
3392
3393 PROCEDURE NAME: get_po_dist_amount()
3394
3395 ===========================================================================*/
3396
3397 PROCEDURE get_po_dist_amount(p_po_distribution_id IN NUMBER,
3398 p_available_amount IN OUT NOCOPY NUMBER,
3399 p_tolerable_amount IN OUT NOCOPY NUMBER) IS
3400
3401 x_progress VARCHAR2(3) := NULL;
3402 x_deliver_amount NUMBER := 0;
3403 x_balance_receipt_amount NUMBER := 0;
3404 x_interface_amount NUMBER := 0; /* in primary uom */
3405
3406 -- 1337787
3407 x_amt_rcv_tolerance NUMBER := 0;
3408 x_amt_ordered NUMBER := 0;
3409 x_amt_received NUMBER := 0;
3410 x_amt_cancelled NUMBER := 0;
3411 l_amount NUMBER := 0; /* Bug 1710046 */
3412
3413 -- <Bug 9342280 : Added for CLM project>
3414 l_po_line_location_id NUMBER;
3415 l_is_clm_po VARCHAR2(5) := 'N';
3416 l_distribution_type VARCHAR2(100);
3417 l_matching_basis VARCHAR2(100);
3418 l_accrue_on_receipt_flag VARCHAR2(100);
3419 l_code_combination_id NUMBER;
3420 l_budget_account_id NUMBER;
3421 l_partial_funded_flag VARCHAR2(5) := 'N';
3422 l_unit_meas_lookup_code VARCHAR2(100);
3423 l_funded_value NUMBER;
3424 l_quantity_funded NUMBER;
3425 l_amount_funded NUMBER;
3426 l_quantity_received NUMBER;
3427 l_amount_received NUMBER;
3428 l_quantity_delivered NUMBER;
3429 l_amount_delivered NUMBER;
3430 l_quantity_billed NUMBER;
3431 l_amount_billed NUMBER;
3432 l_quantity_cancelled NUMBER;
3433 l_amount_cancelled NUMBER;
3434 l_return_status VARCHAR2(100);
3435 -- <CLM END>
3436
3437
3438 BEGIN
3439
3440 x_progress := '005';
3441
3442 BEGIN
3443
3444 select (pod.AMOUNT_ORDERED - nvl(pod.AMOUNT_DELIVERED,0) -
3445 nvl(pod.AMOUNT_CANCELLED,0)) amt,
3446 (poll.amount - nvl(poll.amount_received,0) -
3447 nvl(poll.amount_cancelled,0)) amt_rcvd,
3448 1 + (nvl(poll.qty_rcv_tolerance,0)/100), -- 1337787
3449 nvl(poll.amount,0),
3450 nvl(poll.amount_received,0),
3451 nvl(poll.amount_cancelled,0),
3452 poll.line_location_id -- <Bug 9342280 : Added for CLM project>
3453 INTO p_available_amount,
3454 x_balance_receipt_amount,
3455 x_amt_rcv_tolerance,
3456 x_amt_ordered,
3457 x_amt_received,
3458 x_amt_cancelled,
3459 l_po_line_location_id -- <Bug 9342280 : Added for CLM project>
3460 from po_distributions_all pod, --<Shared Proc FPJ>
3461 po_line_locations_all poll, --<Shared Proc FPJ>
3462 po_lines_all pol --<Shared Proc FPJ>
3463 where pod.line_location_id = poll.line_location_id
3464 and pod.po_distribution_id = p_po_distribution_id
3465 and pod.po_line_id = pol.po_line_id;
3466
3467 -- <Bug 9342280 : Added for CLM project>
3468
3469 IF (g_asn_debug = 'Y') THEN
3470 asn_debug.put_line('before calling po_clm_intg_grp.is_clm_po()');
3471 END IF;
3472
3473 l_is_clm_po := po_clm_intg_grp.is_clm_po( p_po_header_id => NULL,
3474 p_po_line_id => NULL,
3475 p_po_line_location_id => l_po_line_location_id,
3476 p_po_distribution_id => p_po_distribution_id);
3477
3478 IF (g_asn_debug = 'Y') THEN
3479 asn_debug.put_line('line_location_id : ' || l_po_line_location_id);
3480 asn_debug.put_line('p_po_distribution_id : ' || p_po_distribution_id);
3481 asn_debug.put_line('l_is_clm_po: ' || l_is_clm_po);
3482 END IF;
3483
3484
3485 IF l_is_clm_po = 'Y' THEN
3486 IF (g_asn_debug = 'Y') THEN
3487 asn_debug.put_line(' Before po_clm_intg_grp.get_funding_info()');
3488 end if;
3489
3490 po_clm_intg_grp.get_funding_info( p_po_header_id => NULL,
3491 p_po_line_id => NULL,
3492 p_line_location_id => l_po_line_location_id,
3493 p_po_distribution_id => p_po_distribution_id,
3494 x_distribution_type => l_distribution_type,
3495 x_matching_basis => l_matching_basis,
3496 x_accrue_on_receipt_flag => l_accrue_on_receipt_flag,
3497 x_code_combination_id => l_code_combination_id,
3498 x_budget_account_id => l_budget_account_id,
3499 x_partial_funded_flag => l_partial_funded_flag,
3500 x_unit_meas_lookup_code => l_unit_meas_lookup_code,
3501 x_funded_value => l_funded_value,
3502 x_quantity_funded => l_quantity_funded,
3503 x_amount_funded => l_amount_funded,
3504 x_quantity_received => l_quantity_received,
3505 x_amount_received => l_amount_received,
3506 x_quantity_delivered => l_quantity_delivered,
3507 x_amount_delivered => l_amount_delivered,
3508 x_quantity_billed => l_quantity_billed,
3509 x_amount_billed => l_amount_billed,
3510 x_quantity_cancelled => l_quantity_cancelled,
3511 x_amount_cancelled => l_amount_cancelled,
3512 x_return_status => l_return_status
3513 );
3514
3515 IF (g_asn_debug = 'Y') THEN
3516 asn_debug.put_line('l_partial_funded_flag : ' || l_partial_funded_flag);
3517 asn_debug.put_line('l_amount_funded: ' || l_amount_funded);
3518 asn_debug.put_line('l_amount_delivered : ' || l_amount_delivered);
3519 asn_debug.put_line('l_amount_cancelled: ' || l_amount_cancelled);
3520 END IF;
3521
3522 IF l_partial_funded_flag = 'Y' THEN
3523
3524 p_available_amount := l_amount_funded - l_amount_delivered - l_amount_cancelled;
3525 END IF;
3526
3527 END IF;
3528 -- <CLM END>
3529
3530
3531 if (p_available_amount > 0) and
3532 (x_balance_receipt_amount <= 0) then
3533 p_available_amount := 0;
3534 end if;
3535
3536 exception
3537 when no_data_found then
3538 p_available_amount := 0;
3539
3540 WHEN OTHERS THEN RAISE;
3541
3542 END;
3543
3544 x_progress := '015';
3545
3546
3547 SELECT nvl(sum(decode(transaction_type,
3548 'CORRECT', -1 * (decode(nvl(order_transaction_id,-999),-999,amount,nvl(interface_transaction_amt,0))),
3549 decode(nvl(order_transaction_id,-999),-999,amount,nvl(interface_transaction_amt,0))
3550 )),0)
3551 INTO x_interface_amount
3552 FROM rcv_transactions_interface
3553 WHERE (transaction_status_code = 'PENDING'
3554 and processing_status_code <> 'ERROR')
3555 AND po_distribution_id = p_po_distribution_id;
3556
3557 /*
3558 ** Calculate the quantity available to be transacted
3559 */
3560
3561 p_available_amount := p_available_amount - x_interface_amount;
3562
3563 IF (p_available_amount < 0) THEN
3564
3565 p_available_amount := 0;
3566
3567 END IF;
3568
3569 -- <Bug 9342280 : Added for CLM project>
3570 IF l_is_clm_po = 'Y' AND l_partial_funded_flag = 'Y' THEN
3571 IF (g_asn_debug = 'Y') THEN
3572 asn_debug.put_line('set p_tolerable_amount for clm po');
3573 END IF;
3574
3575 p_tolerable_amount := p_available_amount;
3576
3577 ELSE
3578 IF (g_asn_debug = 'Y') THEN
3579 asn_debug.put_line('set p_tolerable_amount for non-clm po');
3580 END IF;
3581 -- <CLM END>
3582
3583 -- 1337787
3584 p_tolerable_amount := (x_amt_ordered * x_amt_rcv_tolerance)-
3585 x_amt_received - x_amt_cancelled -
3586 x_interface_amount;
3587 END IF; -- <Bug 9342280 : Added for CLM project>
3588
3589 IF (p_tolerable_amount < 0) THEN
3590
3591 p_tolerable_amount := 0;
3592
3593 END IF;
3594
3595 EXCEPTION
3596
3597 WHEN OTHERS THEN
3598
3599 po_message_s.sql_error('get_po_dist_amount',
3600 x_progress, sqlcode);
3601
3602 RAISE;
3603
3604 END get_po_dist_amount;
3605
3606 /*===========================================================================
3607
3608 PROCEDURE NAME: get_rcv_dist_quantity()
3609
3610 ===========================================================================*/
3611
3612 PROCEDURE get_rcv_dist_quantity(p_po_distribution_id IN NUMBER,
3613 p_transaction_id IN NUMBER,
3614 p_available_quantity IN OUT NOCOPY NUMBER,
3615 p_unit_of_measure IN OUT NOCOPY VARCHAR2) IS
3616
3617 x_progress VARCHAR2(3) := NULL;
3618 x_deliver_quantity NUMBER := 0;
3619 x_interface_quantity NUMBER := 0; /* in primary uom */
3620 x_primary_uom VARCHAR2(26);
3621 x_item_id NUMBER;
3622 x_interface_qty_in_trx_uom NUMBER;
3623
3624 -- <Bug 9342280 : Added for CLM project>
3625 l_po_line_location_id NUMBER;
3626 l_is_clm_po VARCHAR2(5) := 'N';
3627 l_distribution_type VARCHAR2(100);
3628 l_matching_basis VARCHAR2(100);
3629 l_accrue_on_receipt_flag VARCHAR2(100);
3630 l_code_combination_id NUMBER;
3631 l_budget_account_id NUMBER;
3632 l_partial_funded_flag VARCHAR2(100) := 'N';
3633 l_unit_meas_lookup_code VARCHAR2(100);
3634 l_funded_value NUMBER;
3635 l_quantity_funded NUMBER :=0;
3636 l_amount_funded NUMBER;
3637 l_quantity_received NUMBER;
3638 l_amount_received NUMBER;
3639 l_quantity_delivered NUMBER :=0;
3640 l_amount_delivered NUMBER;
3641 l_quantity_billed NUMBER;
3642 l_amount_billed NUMBER;
3643 l_quantity_cancelled NUMBER :=0;
3644 l_amount_cancelled NUMBER;
3645 l_return_status VARCHAR2(100);
3646 p_available_funded_quantity NUMBER;
3647 -- <CLM END>
3648
3649
3650
3651 BEGIN
3652
3653 x_progress := '005';
3654
3655 /*
3656 ** Get available supply quantity information.
3657 */
3658
3659 /*
3660 ** There may be no supply quantity hence the exception no data found
3661 ** needs to be trapped here
3662 */
3663
3664 BEGIN
3665
3666 SELECT quantity,
3667 unit_of_measure
3668 INTO p_available_quantity,
3669 p_unit_of_measure
3670 FROM mtl_supply
3671 WHERE supply_type_code = 'RECEIVING'
3672 AND supply_source_id = p_transaction_id
3673 AND po_distribution_id = p_po_distribution_id;
3674
3675 EXCEPTION
3676
3677 WHEN NO_DATA_FOUND THEN
3678
3679 p_available_quantity := 0;
3680
3681 p_unit_of_measure := '';
3682
3683 WHEN OTHERS THEN RAISE;
3684
3685 END;
3686
3687
3688 -- <Bug 9342280 : Added for CLM project>
3689
3690 IF (g_asn_debug = 'Y') THEN
3691 asn_debug.put_line('before calling po_clm_intg_grp.is_clm_po()');
3692 END IF;
3693
3694 l_is_clm_po := po_clm_intg_grp.is_clm_po(p_po_header_id => NULL,
3695 p_po_line_id => NULL,
3696 p_po_line_location_id => NULL,
3697 p_po_distribution_id => p_po_distribution_id);
3698
3699
3700 IF (g_asn_debug = 'Y') THEN
3701 asn_debug.put_line('p_po_distribution_id : ' || p_po_distribution_id);
3702 asn_debug.put_line('l_is_clm_po: ' || l_is_clm_po);
3703 END IF;
3704
3705 IF l_is_clm_po = 'Y' THEN
3706 IF (g_asn_debug = 'Y') THEN
3707 asn_debug.put_line('before calling po_clm_intg_grp.get_funding_info()');
3708 END IF;
3709
3710 po_clm_intg_grp.get_funding_info(p_po_header_id => NULL,
3711 p_po_line_id => NULL,
3712 p_line_location_id => NULL,
3713 p_po_distribution_id => p_po_distribution_id,
3714 x_distribution_type => l_distribution_type,
3715 x_matching_basis => l_matching_basis,
3716 x_accrue_on_receipt_flag => l_accrue_on_receipt_flag,
3717 x_code_combination_id => l_code_combination_id,
3718 x_budget_account_id => l_budget_account_id,
3719 x_partial_funded_flag => l_partial_funded_flag,
3720 x_unit_meas_lookup_code => l_unit_meas_lookup_code,
3721 x_funded_value => l_funded_value,
3722 x_quantity_funded => l_quantity_funded,
3723 x_amount_funded => l_amount_funded,
3724 x_quantity_received => l_quantity_received,
3725 x_amount_received => l_amount_received,
3726 x_quantity_delivered => l_quantity_delivered,
3727 x_amount_delivered => l_amount_delivered,
3728 x_quantity_billed => l_quantity_billed,
3729 x_amount_billed => l_amount_billed,
3730 x_quantity_cancelled => l_quantity_cancelled,
3731 x_amount_cancelled => l_amount_cancelled,
3732 x_return_status => l_return_status);
3733
3734 IF (g_asn_debug = 'Y') THEN
3735 asn_debug.put_line('l_partial_funded_flag : ' || l_partial_funded_flag);
3736 asn_debug.put_line('l_quantity_funded: ' || l_quantity_funded);
3737 asn_debug.put_line('l_quantity_delivered : ' || l_quantity_delivered);
3738 asn_debug.put_line('l_quantity_cancelled: ' || l_quantity_cancelled);
3739 END IF;
3740
3741 IF (l_partial_funded_flag = 'Y') THEN
3742
3743 p_available_funded_quantity := l_quantity_funded - l_quantity_delivered - l_quantity_cancelled;
3744
3745 IF p_available_funded_quantity < p_available_quantity THEN
3746
3747 p_available_quantity := p_available_funded_quantity;
3748
3749 END IF;
3750
3751 END IF;
3752
3753 END IF;
3754 -- <CLM END>
3755
3756
3757 select nvl(sum(decode(transaction_type,
3758 'CORRECT', -1 * (decode(nvl(order_transaction_id,-999),-999,primary_quantity,nvl(interface_transaction_qty,0))),
3759 decode(nvl(order_transaction_id,-999),-999,primary_quantity,nvl(interface_transaction_qty,0))
3760 )),0),
3761 min(primary_unit_of_measure),
3762 min(item_id) -- Bug 11833312
3763 INTO x_interface_quantity,
3764 x_primary_uom,
3765 x_item_id
3766 FROM rcv_transactions_interface
3767 WHERE (transaction_status_code = 'PENDING'
3768 and processing_status_code <> 'ERROR')
3769 AND parent_transaction_id = p_transaction_id
3770 AND po_distribution_id = p_po_distribution_id;
3771
3772 IF (x_interface_quantity = 0) THEN
3773
3774 /*
3775 ** There is no unprocessed quantity. Simply set the
3776 ** x_interface_qty_in_trx_uom to 0. There is no need for uom
3777 ** conversion.
3778 */
3779
3780 x_interface_qty_in_trx_uom := 0;
3781
3782 ELSE
3783
3784 /*
3785 ** There is unprocessed quantity. Convert it to the transaction uom
3786 ** so that the available quantity can be calculated in the trx uom
3787 */
3788
3789 po_uom_s.uom_convert(x_interface_quantity, x_primary_uom, x_item_id,
3790 p_unit_of_measure, x_interface_qty_in_trx_uom);
3791
3792 END IF;
3793
3794 /*
3795 ** Calculate the quantity available to be transacted
3796 */
3797
3798 p_available_quantity := p_available_quantity - x_interface_qty_in_trx_uom;
3799
3800 IF (p_available_quantity < 0) THEN
3801
3802 p_available_quantity := 0;
3803
3804 END IF;
3805
3806 EXCEPTION
3807
3808 WHEN OTHERS THEN
3809
3810 po_message_s.sql_error('get_rcv_dist_quantity',
3811 x_progress, sqlcode);
3812
3813 RAISE;
3814
3815 END get_rcv_dist_quantity;
3816
3817 /*===========================================================================
3818
3819 PROCEDURE NAME: val_quantity()
3820
3821 ===========================================================================*/
3822
3823 PROCEDURE val_quantity IS
3824
3825 x_progress VARCHAR2(3) := NULL;
3826
3827 BEGIN
3828
3829 null;
3830
3831 EXCEPTION
3832
3833 WHEN OTHERS THEN
3834
3835 po_message_s.sql_error('val_quantity', x_progress, sqlcode);
3836
3837 RAISE;
3838
3839 END val_quantity;
3840
3841 /*===========================================================================
3842
3843 PROCEDURE NAME: get_primary_qty_uom
3844
3845 ===========================================================================*/
3846 /*
3847 ** go get the primary quantity an uom for an item based on a transaction
3848 ** quantity and uom
3849 */
3850
3851 PROCEDURE get_primary_qty_uom (
3852 X_transaction_qty IN NUMBER,
3853 X_transaction_uom IN VARCHAR2,
3854 X_item_id IN NUMBER,
3855 X_organization_id IN NUMBER,
3856 X_primary_qty IN OUT NOCOPY NUMBER,
3857 X_primary_uom IN OUT NOCOPY VARCHAR2) IS
3858
3859 X_progress VARCHAR2(4) := '000';
3860
3861 BEGIN
3862
3863 /*dbms_output.put_line ('get_primary_qty : X_transaction_qty : '||
3864 TO_CHAR(X_transaction_qty));
3865 dbms_output.put_line ('get_primary_qty : X_transaction_uom : '||
3866 X_transaction_uom);
3867 dbms_output.put_line ('get_primary_qty : X_item_id : '||
3868 TO_CHAR(X_item_id));
3869 dbms_output.put_line ('get_primary_qty : X_organization_Id : '||
3870 TO_CHAR(X_organization_id));*/
3871 /*
3872 ** Check if item_id = 0, if TRUE get primary unit of measure from
3873 ** MTL_UNITS_OF_MEASURE else get primary unit of measure from
3874 ** MTL_SYSTEM_ITEMS
3875 */
3876
3877 IF (X_item_id IS NULL) THEN
3878
3879 X_progress := '1100';
3880
3881 SELECT MUOM2.unit_of_measure
3882 INTO X_primary_uom
3883 FROM mtl_units_of_measure MUOM1,
3884 mtl_units_of_measure MUOM2
3885 WHERE MUOM1.unit_of_measure = X_transaction_uom
3886 AND MUOM1.uom_class = MUOM2.uom_class
3887 AND MUOM2.base_uom_flag = 'Y';
3888
3889 ELSE
3890
3891 X_progress := '1110';
3892
3893 SELECT MSI.primary_unit_of_measure
3894 INTO X_primary_uom
3895 FROM mtl_system_items_kfv MSI
3896 WHERE MSI.inventory_item_id = X_item_id
3897 AND MSI.organization_id = X_organization_id;
3898
3899 END IF;
3900
3901 /*
3902 ** Go get the primary quantity based on the transaction quantity and the
3903 ** conversions between the UOMS
3904 */
3905 X_progress := '1120';
3906 po_uom_s.uom_convert (X_transaction_qty,
3907 X_transaction_uom,
3908 X_item_id,
3909 X_primary_uom,
3910 X_primary_qty);
3911
3912 /*dbms_output.put_line ('get_primary_qty : X_primary_qty : '||
3913 TO_CHAR(X_primary_qty));
3914 dbms_output.put_line ('get_primary_qty : X_primary_uom : '||
3915 X_primary_uom);*/
3916
3917 RETURN;
3918
3919 EXCEPTION
3920 WHEN OTHERS THEN
3921 po_message_s.sql_error('get_primary_qty_uom', X_progress, sqlcode);
3922 RAISE;
3923
3924 END get_primary_qty_uom;
3925
3926 /*===========================================================================
3927
3928 FUNCTION NAME: get_pending_qty_
3929
3930 ===========================================================================*/
3931 FUNCTION get_pending_qty(p_line_location_id IN NUMBER) RETURN NUMBER IS
3932 x_progress VARCHAR2(3) := NULL;
3933 x_interface_quantity NUMBER := 0;
3934 x_po_uom VARCHAR2(26);
3935 x_item_id NUMBER;
3936 x_primary_uom VARCHAR2(26);
3937 x_interface_qty_in_po_uom NUMBER := 0;
3938
3939 BEGIN
3940 x_progress := '005';
3941 SELECT PL.ITEM_ID,
3942 PL.UNIT_MEAS_LOOKUP_CODE
3943 INTO x_item_id,
3944 x_po_uom
3945 FROM PO_LINE_LOCATIONS PLL,
3946 PO_LINES PL
3947 WHERE PLL.LINE_LOCATION_ID = p_line_location_id AND
3948 PLL.PO_LINE_ID = PL.PO_LINE_ID;
3949
3950 x_progress := '010';
3951 SELECT nvl(sum(decode(nvl(order_transaction_id,-999),-999,primary_quantity,nvl(interface_transaction_qty,0))),0),
3952 MIN(PRIMARY_UNIT_OF_MEASURE)
3953 INTO x_interface_quantity,
3954 x_primary_uom
3955 FROM RCV_TRANSACTIONS_INTERFACE
3956 WHERE (TRANSACTION_STATUS_CODE = 'PENDING'
3957 and processing_status_code <> 'ERROR') AND
3958 TRANSACTION_TYPE IN ('RECEIVE','MATCH','CORRECT') AND
3959 PO_LINE_LOCATION_ID = p_line_location_id;
3960
3961 IF (x_interface_quantity = 0) THEN
3962 /* ** There is no unprocessed quantity. Simply set the
3963 ** x_interface_qty_in_po_uom to 0. There is no need for uom
3964 ** conversion. */
3965 x_interface_qty_in_po_uom := 0;
3966 ELSE
3967 /* ** There is unprocessed quantity. Convert it to the PO uom
3968 ** so that the available quantity can be calculated in the PO uom */
3969 x_progress := '015';
3970 /*
3971 po_uom_s.uom_convert(x_interface_quantity,
3972 x_primary_uom,
3973 x_item_id,
3974 x_po_uom,
3975 x_interface_qty_in_po_uom);
3976 */
3977 /* Had to reverse engineer the call to po_uom_s.uom_convert */
3978 x_interface_qty_in_po_uom := x_interface_quantity *
3979 po_uom_s.po_uom_convert(x_primary_uom,x_po_uom,x_item_id);
3980 END IF;
3981
3982 x_progress := '020';
3983 RETURN x_interface_qty_in_po_uom;
3984
3985 /* Had to remove the exception handling section because it violates WNDS pragma */
3986 /*
3987 EXCEPTION
3988 WHEN OTHERS THEN
3989 po_message_s.sql_error('get_pending_qty',x_progress,SQLCODE);
3990 RAISE;
3991 */
3992
3993 END get_pending_qty;
3994
3995 /*===========================================================================
3996
3997 PROCEDURE NAME: get_ship_qty_in_int
3998
3999 ===========================================================================*/
4000 /*
4001 ** get qty in RTI for a particular PO shipment and ASN shipment
4002 */
4003
4004 PROCEDURE get_ship_qty_in_int (
4005 p_shipment_line_id IN NUMBER,
4006 p_line_location_id IN NUMBER,
4007 p_ship_qty_in_int IN OUT NOCOPY NUMBER) IS
4008
4009 X_progress VARCHAR2(4) := '000';
4010 x_interface_quantity NUMBER := 0; /* in primary_uom */
4011 x_shipment_uom VARCHAR2(26);
4012 x_item_id NUMBER;
4013 x_primary_uom VARCHAR2(26);
4014 x_interface_qty_in_ship_uom NUMBER := 0;
4015
4016 BEGIN
4017
4018 SELECT rsl.item_id,
4019 rsl.unit_of_measure
4020 INTO x_item_id,
4021 x_shipment_uom
4022 FROM rcv_shipment_lines rsl
4023 WHERE rsl.shipment_line_id = p_shipment_line_id;
4024
4025 x_progress := '010';
4026
4027 SELECT nvl(sum(decode(nvl(order_transaction_id,-999),-999,primary_quantity,nvl(interface_transaction_qty,0))),0),
4028 min(primary_unit_of_measure)
4029 INTO x_interface_quantity,
4030 x_primary_uom
4031 FROM rcv_transactions_interface
4032 WHERE (transaction_status_code = 'PENDING'
4033 and processing_status_code <> 'ERROR')
4034 AND transaction_type = 'RECEIVE'
4035 AND shipment_line_id = p_shipment_line_id
4036 AND po_line_location_id = p_line_location_id;
4037
4038 x_progress := '020';
4039
4040 IF (x_interface_quantity = 0) THEN
4041
4042 /*
4043 ** There is no unprocessed quantity. Simply set the
4044 ** x_interface_qty_in_ship_uom to 0. There is no need for uom
4045 ** conversion.
4046 */
4047
4048 x_interface_qty_in_ship_uom := 0;
4049
4050 ELSE
4051
4052 /*
4053 ** There is unprocessed quantity. Convert it to the shipment uom
4054 ** so that the available quantity can be calculated in the shipment uom
4055 */
4056 x_progress := '015';
4057
4058 po_uom_s.uom_convert(x_interface_quantity, x_primary_uom, x_item_id,
4059 x_shipment_uom, x_interface_qty_in_ship_uom);
4060
4061 END IF;
4062
4063 x_progress := '030';
4064
4065 p_ship_qty_in_int := x_interface_qty_in_ship_uom;
4066
4067 EXCEPTION
4068 WHEN OTHERS THEN
4069 po_message_s.sql_error('get_ship_qty_in_int', X_progress, sqlcode);
4070 RAISE;
4071
4072 END get_ship_qty_in_int;
4073
4074 PROCEDURE get_available_asn_quantity(
4075 p_transaction_type IN VARCHAR2,
4076 p_shipment_line_id IN NUMBER,
4077 p_line_location_id IN NUMBER,
4078 p_distribution_id IN VARCHAR2,
4079 x_unit_of_measure IN OUT NOCOPY VARCHAR2,
4080 x_available_quantity IN OUT NOCOPY NUMBER,
4081 x_tolerable_quantity IN OUT NOCOPY NUMBER,
4082 x_secondary_available_qty IN OUT NOCOPY NUMBER
4083 ) IS
4084 l_available_qty_hold number;
4085 l_uom_hold VARCHAR2(26);
4086 l_secondary_available_qty_hold number;
4087 l_quantity_shipped NUMBER :=0; -- ASN Phase 2
4088 l_quantity_returned NUMBER :=0; -- ASN Phase 2
4089 l_ship_qty_int NUMBER :=0; -- ASN Phase 2 bug 623925
4090 l_interface_qty_in_trx_uom NUMBER;
4091 l_interface_quantity NUMBER := 0; /* in primary uom */
4092 l_primary_uom VARCHAR2(26);
4093 l_item_id NUMBER;
4094 l_qty_rcv_tolerance number :=0;
4095 l_qty_ordered number :=0;
4096 l_qty_received number :=0;
4097 l_qty_cancelled number :=0;
4098 l_progress VARCHAR2(3) := NULL;
4099 begin
4100
4101 l_progress := '000';
4102 IF (g_asn_debug = 'Y') THEN
4103 asn_debug.put_line('Enter get_available_asn_quantity ');
4104 asn_debug.put_line('p_transaction_type '||p_transaction_type);
4105 asn_debug.put_line('p_shipment_line_id '||p_shipment_line_id);
4106 asn_debug.put_line('p_line_location_id '||p_line_location_id );
4107 asn_debug.put_line('p_distribution_id '||p_distribution_id );
4108 asn_debug.put_line('x_unit_of_measure '||x_unit_of_measure );
4109 END IF;
4110
4111 IF (p_transaction_type = 'RECEIVE') then --{
4112
4113 /* This part of the code is the same as in RCVRCPQB.pls
4114 * where we calculate the available qty in forms.
4115 */
4116 rcv_quantities_s.get_available_quantity ('RECEIVE',
4117 p_shipment_line_id,
4118 'INVENTORY',
4119 NULL,
4120 NULL,
4121 NULL,
4122 x_available_quantity,
4123 x_tolerable_quantity,
4124 x_unit_of_measure,
4125 X_secondary_available_qty);
4126
4127 IF (g_asn_debug = 'Y') THEN
4128 asn_debug.put_line('x_available_quantity '||x_available_quantity);
4129 asn_debug.put_line('x_tolerable_quantity '||x_tolerable_quantity);
4130 asn_debug.put_line('x_unit_of_measure '||x_unit_of_measure);
4131 asn_debug.put_line('X_secondary_available_qty '||X_secondary_available_qty);
4132 END IF;
4133
4134 l_progress := '010';
4135
4136 rcv_quantities_s.get_available_quantity ('RECEIVE',
4137 p_line_location_id,
4138 'VENDOR',
4139 NULL,
4140 NULL,
4141 NULL,
4142 l_available_qty_hold,
4143 x_tolerable_quantity,
4144 l_uom_hold,
4145 x_secondary_available_qty);
4146
4147
4148 IF (g_asn_debug = 'Y') THEN
4149 asn_debug.put_line('l_available_qty_hold '||l_available_qty_hold);
4150 asn_debug.put_line('x_tolerable_quantity '||x_tolerable_quantity);
4151 asn_debug.put_line('l_uom_hold '||l_uom_hold);
4152 asn_debug.put_line('X_secondary_available_qty '||X_secondary_available_qty);
4153 END IF;
4154
4155 /* Bug 9593466
4156 We do not need to do any more calculation to get the available qty
4157 and tolerable quantity since we already take care of the quantities
4158 in the interface table in the above apis.
4159 */
4160
4161
4162 -- Handle the return to vendor here
4163 l_progress := '040';
4164
4165 select sum(nvl(quantity,0))
4166 into l_quantity_returned
4167 from rcv_transactions
4168 where shipment_line_id = p_shipment_line_id and
4169 transaction_type = 'RETURN TO VENDOR';
4170
4171
4172 IF (g_asn_debug = 'Y') THEN
4173 asn_debug.put_line('l_quantity_returned '||l_quantity_returned );
4174 END IF;
4175
4176 IF l_quantity_returned > 0 then --}
4177
4178 x_available_quantity := X_available_quantity - l_quantity_returned;
4179 x_tolerable_quantity := x_tolerable_quantity - l_quantity_returned;
4180
4181 If x_available_quantity < 0 THEN
4182 x_available_quantity := 0;
4183 end if;
4184
4185 IF x_tolerable_quantity < 0 THEN
4186 x_tolerable_quantity := 0;
4187 end if;
4188
4189
4190 IF (g_asn_debug = 'Y') THEN
4191 asn_debug.put_line('x_available_quantity '||x_available_quantity );
4192 asn_debug.put_line('x_tolerable_quantity '||x_tolerable_quantity );
4193 END IF;
4194
4195 END IF; --}
4196
4197 elsif(p_transaction_type = 'DIRECT RECEIPT') then
4198
4199
4200 IF (g_asn_debug = 'Y') THEN
4201 asn_debug.put_line('In direct receipt of ASN ');
4202 END IF;
4203
4204 BEGIN
4205
4206 SELECT quantity,
4207 unit_of_measure
4208 INTO x_available_quantity,
4209 x_unit_of_measure
4210 FROM mtl_supply
4211 WHERE supply_type_code = 'SHIPMENT'
4212 AND supply_source_id = p_shipment_line_id
4213 AND po_distribution_id = p_distribution_id;
4214
4215 EXCEPTION
4216
4217 WHEN NO_DATA_FOUND THEN
4218
4219 x_available_quantity := 0;
4220
4221 x_unit_of_measure := '';
4222
4223 WHEN OTHERS THEN RAISE;
4224
4225 END;
4226
4227 IF (g_asn_debug = 'Y') THEN
4228 asn_debug.put_line('x_available_quantity '||x_available_quantity);
4229 END IF;
4230
4231
4232 l_progress := '050';
4233
4234 select nvl(sum( decode(nvl(order_transaction_id,-999),-999,primary_quantity,nvl(interface_transaction_qty,0))),0),
4235 min(primary_unit_of_measure),
4236 min(item_id) -- Bug 12336493
4237 INTO l_interface_quantity,
4238 l_primary_uom,
4239 l_item_id
4240 FROM rcv_transactions_interface
4241 WHERE (transaction_status_code = 'PENDING'
4242 AND processing_status_code <> 'ERROR')
4243 AND transaction_type = 'RECEIVE'
4244 AND shipment_line_id = p_shipment_line_id
4245 AND po_line_location_id = p_line_location_id
4246 AND po_distribution_id = p_distribution_id;
4247
4248
4249 IF (g_asn_debug = 'Y') THEN
4250 asn_debug.put_line('l_interface_quantity '||l_interface_quantity);
4251 asn_debug.put_line('l_primary_uom '||l_primary_uom);
4252 END IF;
4253
4254 IF (l_interface_quantity = 0) THEN
4255
4256 l_interface_qty_in_trx_uom := 0;
4257
4258 ELSE
4259
4260 /*
4261 * There is unprocessed quantity. Convert it to the
4262 * transaction uom so that the available quantity can be
4263 * calculated in the trx uom
4264 */
4265
4266 po_uom_s.uom_convert( l_interface_quantity,
4267 l_primary_uom,
4268 l_item_id,
4269 x_unit_of_measure,
4270 l_interface_qty_in_trx_uom);
4271
4272 END IF;
4273
4274
4275 IF (g_asn_debug = 'Y') THEN
4276 asn_debug.put_line('l_interface_qty_in_trx_uom '||l_interface_qty_in_trx_uom);
4277 END IF;
4278
4279 x_available_quantity := x_available_quantity -
4280 l_interface_qty_in_trx_uom;
4281
4282 IF (x_available_quantity < 0) THEN
4283
4284 x_available_quantity := 0;
4285
4286 END IF;
4287
4288 IF (g_asn_debug = 'Y') THEN
4289 asn_debug.put_line('x_available_quantity '||x_available_quantity);
4290 END IF;
4291
4292
4293
4294 select
4295 1 + (nvl(poll.qty_rcv_tolerance,0)/100), -- 1337787
4296 nvl(poll.quantity,0),
4297 nvl(poll.quantity_received,0),
4298 nvl(poll.quantity_cancelled,0)
4299 INTO
4300 l_qty_rcv_tolerance,
4301 l_qty_ordered,
4302 l_qty_received,
4303 l_qty_cancelled
4304 from po_distributions_all pod, --<Shared Proc FPJ>
4305 po_line_locations_all poll, --<Shared Proc FPJ>
4306 po_lines_all pol --<Shared Proc FPJ>
4307 where pod.line_location_id = poll.line_location_id
4308 and pod.po_distribution_id = p_distribution_id
4309 and pod.po_line_id = pol.po_line_id;
4310
4311
4312 IF (g_asn_debug = 'Y') THEN
4313 asn_debug.put_line('l_qty_rcv_tolerance '||l_qty_rcv_tolerance);
4314 asn_debug.put_line('l_qty_ordered '||l_qty_ordered);
4315 asn_debug.put_line('l_qty_received '||l_qty_received);
4316 asn_debug.put_line('l_qty_cancelled '||l_qty_cancelled);
4317 END IF;
4318
4319 x_tolerable_quantity := (l_qty_ordered * l_qty_rcv_tolerance)-
4320 l_qty_received - l_qty_cancelled -
4321 l_interface_qty_in_trx_uom;
4322
4323 IF (x_tolerable_quantity < 0) THEN
4324
4325 x_tolerable_quantity := 0;
4326
4327 END IF;
4328
4329
4330 IF (g_asn_debug = 'Y') THEN
4331 asn_debug.put_line('x_tolerable_quantity '||x_tolerable_quantity);
4332 END IF;
4333
4334
4335
4336 end if; --}
4337
4338 IF (g_asn_debug = 'Y') THEN
4339 asn_debug.put_line('Leave get_available_asn_qty ');
4340 END IF;
4341
4342 EXCEPTION
4343 WHEN OTHERS THEN
4344 po_message_s.sql_error('get_available_asn_quantity', l_progress, sqlcode);
4345 RAISE;
4346
4347 END get_available_asn_quantity;
4348
4349
4350 END RCV_QUANTITIES_S;
[Home] [Help]