1 PACKAGE BODY RCV_QUANTITIES_S AS
2 /* $Header: RCVTXQUB.pls 120.5 2006/09/15 05:49:49 amony noship $*/
3
4 g_asn_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('RCV_DEBUG_MODE'),'N');
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 BEGIN
697
698 x_progress := '005';
699
700 /*
701 ** Get PO quantity information.
702 */
703
704 SELECT nvl(pll.quantity, 0),
705 nvl(pll.quantity_received, 0),
706 nvl(pll.quantity_cancelled,0),
707 nvl(pll.secondary_quantity, 0),
708 nvl(pll.secondary_quantity_received, 0),
709 nvl(pll.secondary_quantity_cancelled,0),
710 1 + (nvl(pll.qty_rcv_tolerance,0)/100),
711 pll.qty_rcv_exception_code,
712 pl.item_id,
713 pl.unit_meas_lookup_code
714 INTO x_quantity_ordered,
715 x_quantity_received,
716 x_quantity_cancelled,
717 /*Bug# 1548597*/
718 x_secondary_qty_ordered,
719 x_secondary_qty_received,
720 x_secondary_qty_cancelled,
721 --end bug 1548597
722 x_qty_rcv_tolerance,
723 x_qty_rcv_exception_code,
724 x_item_id,
725 x_po_uom
726 FROM po_line_locations_all pll, --<Shared Proc FPJ>
727 po_lines_all pl --<Shared Proc FPJ>
728 WHERE pll.line_location_id = p_line_location_id
729 AND pll.po_line_id = pl.po_line_id;
730
731 x_progress := '010';
732
733 /*
734 ** Get any unprocessed receipt or match transaction against the
735 ** PO shipment. x_interface_quantity is in primary uom.
736 **
737 ** The min(primary_uom) is neccessary because the
738 ** select may return multiple rows and we only want one value
739 ** to be returned. Having a sum and min group function in the
740 ** select ensures that this sql statement will not raise a
741 ** no_data_found exception even if no rows are returned.
742 */
743
744 /* Bug# 2347348 : Primary Unit of Measure cannot have value
745 for One time Items. So Added a decode statement to fetch
746 unit_of_measure in case of One Time Items and Primary
747 Unit of Measure for Inventory Items.
748 */
749
750 SELECT nvl(sum(decode(nvl(order_transaction_id,-999),-999,primary_quantity,nvl(interface_transaction_qty,0))),0),
751 decode(min(item_id),null,min(unit_of_measure),min(primary_unit_of_measure))
752 INTO x_interface_quantity,
753 x_primary_uom
754 FROM rcv_transactions_interface
755 WHERE (transaction_status_code = 'PENDING'
756 and processing_status_code <> 'ERROR')
757 AND transaction_type IN ('RECEIVE', 'MATCH','CORRECT','SHIP') -- bug 657347 should include 'SHIP'
758 -- when calculating total quantity
759 -- in the interface table
760 AND po_line_location_id = p_line_location_id;
761 /*
762 SELECT nvl(sum(primary_quantity),0),
763 decode(min(item_id),null,min(unit_of_measure),min(primary_unit_of_measure))
764 INTO x_interface_quantity,
765 x_primary_uom
766 FROM rcv_transactions_interface
767 WHERE (transaction_status_code = 'PENDING'
768 and processing_status_code <> 'ERROR')
769 */
770 /*
771 ** Modified by Subhajit on 09/06/95
772 ** Earlier transaction type were ('RECEIVE','MATCH')
773 ** CORRECT transaction were not taken into consideration
774 AND transaction_type IN ('RECEIVE', 'MATCH')
775 */
776 /*
777 AND transaction_type IN ('RECEIVE', 'MATCH','CORRECT','SHIP') -- bug 657347 should include 'SHIP'
778 -- when calculating total quantity
779 -- in the interface table
780 AND po_line_location_id = p_line_location_id;
781 */
782
783 IF (x_interface_quantity = 0) THEN
784
785 /*
786 ** There is no unprocessed quantity. Simply set the
787 ** x_interface_qty_in_po_uom to 0. There is no need for uom
788 ** conversion.
789 */
790
791 x_interface_qty_in_po_uom := 0;
792
793 ELSE
794
795 /*
796 ** There is unprocessed quantity. Convert it to the PO uom
797 ** so that the available quantity can be calculated in the PO uom
798 */
799
800 x_progress := '015';
801 po_uom_s.uom_convert(x_interface_quantity, x_primary_uom, x_item_id,
802 x_po_uom, x_interface_qty_in_po_uom);
803
804 END IF;
805
806 /*bug 1548597*/
807 SELECT nvl(sum(secondary_quantity),0),
808 min(secondary_unit_of_measure)
809 INTO x_secondary_interface_qty,
810 x_secondary_uom
811 FROM rcv_transactions_interface
812 WHERE (transaction_status_code = 'PENDING'
813 and processing_status_code <> 'ERROR')
814 AND transaction_type IN ('RECEIVE', 'MATCH','CORRECT','SHIP')
815 AND po_line_location_id = p_line_location_id;
816 /*bug 1548597*/
817
818 /*
819 ** Calculate the quantity available to be received.
820 */
821
822 p_available_quantity := x_quantity_ordered - x_quantity_received -
823 x_quantity_cancelled - x_interface_qty_in_po_uom;
824
825 /*bug 1548597*/
826 p_secondary_available_qty := x_secondary_qty_ordered - x_secondary_qty_received -
827 x_secondary_qty_cancelled - x_secondary_interface_qty;
828 /*bug 1548597*/
829
830 /*
831 ** p_available_quantity can be negative if this shipment has been over
832 ** received. In this case, the available quantity that needs to be passed
833 ** back should be 0.
834 */
835
836 IF (p_available_quantity < 0) THEN
837
838 p_available_quantity := 0;
839
840 END IF;
841
842 /*bug 1548597*/
843 IF (p_secondary_available_qty < 0) THEN
844 p_secondary_available_qty := 0;
845 END IF;
846 /*bug 1548597*/
847
848 /*
849 ** Calculate the maximum quantity that can be received allowing for
850 ** tolerance.
851 */
852
853 p_tolerable_quantity := (x_quantity_ordered * x_qty_rcv_tolerance) -
854 x_quantity_received - x_quantity_cancelled -
855 x_interface_qty_in_po_uom;
856
857 /*
858 ** p_tolerable_quantity can be negative if this shipment has been over
859 ** received. In this case, the tolerable quantity that needs to be passed
860 ** back should be 0.
861 */
862
863 IF (p_tolerable_quantity < 0) THEN
864
865 p_tolerable_quantity := 0;
866
867 END IF;
868
869 /*
870 ** Return the PO unit of measure
871 */
872
873 p_unit_of_measure := x_po_uom;
874
875 EXCEPTION
876
877 WHEN OTHERS THEN
878
879 po_message_s.sql_error('get_po_quantity', x_progress, sqlcode);
880
881 RAISE;
882
883 END get_po_quantity;
884
885 /*===========================================================================
886
887 PROCEDURE NAME: get_po_amount()
888
889 ===========================================================================*/
890
891 PROCEDURE get_po_amount(p_line_location_id IN NUMBER,
892 p_available_amount IN OUT NOCOPY NUMBER,
893 p_tolerable_amount IN OUT NOCOPY NUMBER ) IS
894
895 x_progress VARCHAR2(3) := NULL;
896 x_amount_ordered NUMBER := 0;
897 x_amount_received NUMBER := 0;
898 x_interface_amount NUMBER := 0;
899 x_amount_cancelled NUMBER := 0;
900
901 x_qty_rcv_tolerance NUMBER := 0;
902 x_qty_rcv_exception_code VARCHAR2(26);
903
904 BEGIN
905
906 x_progress := '005';
907
908 /*
909 ** Get PO quantity information.
910 */
911
912 SELECT nvl(pll.amount, 0),
913 nvl(pll.amount_received, 0),
914 nvl(pll.amount_cancelled,0),
915 1 + (nvl(pll.qty_rcv_tolerance,0)/100),
916 pll.qty_rcv_exception_code
917 INTO x_amount_ordered,
918 x_amount_received,
919 x_amount_cancelled,
920 x_qty_rcv_tolerance,
921 x_qty_rcv_exception_code
922 FROM po_line_locations_all pll, --<Shared Proc FPJ>
923 po_lines_all pl --<Shared Proc FPJ>
924 WHERE pll.line_location_id = p_line_location_id
925 AND pll.po_line_id = pl.po_line_id;
926
927 x_progress := '010';
928
929 SELECT nvl(sum(decode(nvl(order_transaction_id,-999),-999,amount,nvl(interface_transaction_amt,0))),0)
930 INTO x_interface_amount
931 FROM rcv_transactions_interface
932 WHERE (transaction_status_code = 'PENDING'
933 and processing_status_code <> 'ERROR')
934 AND transaction_type IN ('RECEIVE', 'MATCH','CORRECT')
935 AND po_line_location_id = p_line_location_id;
936
937 /*
938 ** Calculate the quantity available to be received.
939 */
940
941 p_available_amount := x_amount_ordered - x_amount_received -
942 x_amount_cancelled - x_interface_amount;
943
944 /*
945 ** p_available_quantity can be negative if this shipment has been over
946 ** received. In this case, the available quantity that needs to be passed
947 ** back should be 0.
948 */
949
950 IF (p_available_amount < 0) THEN
951 p_available_amount := 0;
952 END IF;
953
954 /*
955 ** Calculate the maximum quantity that can be received allowing for
956 ** tolerance.
957 */
958
959 p_tolerable_amount := (x_amount_ordered * x_qty_rcv_tolerance) -
960 x_amount_received - x_amount_cancelled -
961 x_interface_amount;
962
963 /*
964 ** p_tolerable_quantity can be negative if this shipment has been over
965 ** received. In this case, the tolerable quantity that needs to be passed
966 ** back should be 0.
967 */
968
969 IF (p_tolerable_amount < 0) THEN
970 p_tolerable_amount := 0;
971 END IF;
972
973 EXCEPTION
974
975 WHEN OTHERS THEN
976
977 po_message_s.sql_error('get_po_amount', x_progress, sqlcode);
978
979 RAISE;
980
981 END get_po_amount;
982
983 /*===========================================================================
984
985 PROCEDURE NAME: get_rma_quantity()
986
987 ===========================================================================*/
988
989 PROCEDURE get_rma_quantity(
990 p_oe_order_line_id IN NUMBER,
991 p_available_quantity IN OUT NOCOPY NUMBER,
992 p_tolerable_quantity IN OUT NOCOPY NUMBER,
993 p_unit_of_measure IN OUT NOCOPY VARCHAR2,
994 p_secondary_available_qty IN OUT NOCOPY NUMBER
995 ) IS
996 x_progress VARCHAR2(3) := NULL;
997 x_return_status VARCHAR2(80);
998 x_msg_count NUMBER;
999 x_msg_data VARCHAR2(240);
1000 x_interface_quantity NUMBER := 0; /* in primary_uom */
1001 x_qty_rcv_tolerance NUMBER := 0;
1002 x_oe_uom VARCHAR2(26);
1003 x_item_id NUMBER;
1004 x_primary_uom VARCHAR2(26);
1005 x_interface_qty_in_oe_uom NUMBER := 0;
1006 x_secondary_qty_ordered NUMBER := 0;
1007 x_secondary_qty_received NUMBER := 0;
1008 x_secondary_interface_qty NUMBER := 0;
1009 x_secondary_qty_cancelled NUMBER := 0;
1010 x_secondary_uom VARCHAR2(26);
1011
1012 CURSOR get_lines IS
1013 SELECT NVL(quantity, interface_transaction_qty) quantity,
1014 unit_of_measure,
1015 secondary_quantity,
1016 secondary_unit_of_measure
1017 FROM rcv_transactions_interface
1018 WHERE ( transaction_status_code = 'PENDING'
1019 AND processing_status_code <> 'ERROR')
1020 AND transaction_type IN('RECEIVE', 'MATCH', 'CORRECT', 'SHIP')
1021 AND oe_order_line_id = p_oe_order_line_id;
1022 BEGIN
1023 asn_debug.put_line('***BEGIN*** get_po_quantity');
1024 asn_debug.put_line('p_oe_order_line_id = ' || p_oe_order_line_id);
1025 asn_debug.put_line('p_available_quantity = ' || p_available_quantity);
1026 asn_debug.put_line('p_tolerable_quantity = ' || p_tolerable_quantity);
1027 asn_debug.put_line('p_unit_of_measure = ' || p_unit_of_measure);
1028 asn_debug.put_line('p_secondary_available_qty = ' || p_secondary_available_qty);
1029 x_progress := '010';
1030
1031 SELECT NVL(oel.ordered_quantity2, 0),
1032 NVL(oel.shipped_quantity2, 0),
1033 NVL(oel.cancelled_quantity2, 0),
1034 NVL(oel.ship_tolerance_above, 0),
1035 oel.inventory_item_id,
1036 uom.unit_of_measure
1037 INTO x_secondary_qty_ordered,
1038 x_secondary_qty_received,
1039 x_secondary_qty_cancelled,
1040 x_qty_rcv_tolerance,
1041 x_item_id,
1042 x_oe_uom
1043 FROM oe_order_lines_all oel,
1044 mtl_units_of_measure uom
1045 WHERE oel.line_id = p_oe_order_line_id
1046 AND uom.uom_code = oel.order_quantity_uom;
1047
1048 x_progress := '020';
1049 oe_rma_receiving.get_rma_available_quantity(p_oe_order_line_id,
1050 p_available_quantity,
1051 x_return_status,
1052 x_msg_count,
1053 x_msg_data
1054 );
1055 x_progress := '030';
1056
1057 FOR c_lines IN get_lines LOOP
1058 po_uom_s.uom_convert(c_lines.quantity,
1059 c_lines.unit_of_measure,
1060 x_item_id,
1061 x_oe_uom,
1062 x_interface_quantity
1063 );
1064 x_interface_qty_in_oe_uom := NVL(x_interface_qty_in_oe_uom, 0) + NVL(x_interface_quantity, 0);
1065 x_secondary_interface_qty := NVL(x_secondary_interface_qty, 0) + NVL(c_lines.secondary_quantity, 0);
1066 x_secondary_uom := NVL(x_secondary_uom, c_lines.secondary_unit_of_measure);
1067 END LOOP;
1068
1069 x_progress := '040';
1070 p_available_quantity := p_available_quantity - NVL(x_interface_qty_in_oe_uom, 0);
1071 p_secondary_available_qty := x_secondary_qty_ordered - x_secondary_qty_received - x_secondary_qty_cancelled - NVL(x_secondary_interface_qty, 0);
1072
1073 /*
1074 ** Calculate the maximum quantity that can be received allowing for
1075 ** tolerance.
1076 */
1077 p_tolerable_quantity := p_available_quantity + x_qty_rcv_tolerance;
1078
1079 /*
1080 ** p_available_quantity can be negative if this shipment has been over
1081 ** received. In this case, the available quantity that needs to be passed
1082 ** back should be 0.
1083 */
1084 IF (p_available_quantity < 0) THEN
1085 p_available_quantity := 0;
1086 END IF;
1087
1088 IF (p_secondary_available_qty < 0) THEN
1089 p_secondary_available_qty := 0;
1090 END IF;
1091
1092 /*
1093 ** p_tolerable_quantity can be negative if this shipment has been over
1094 ** received. In this case, the tolerable quantity that needs to be passed
1095 ** back should be 0.
1096 */
1097 IF (p_tolerable_quantity < 0) THEN
1098 p_tolerable_quantity := 0;
1099 END IF;
1100
1101 /*
1102 ** Return the PO unit of measure
1103 */
1104 p_unit_of_measure := x_oe_uom;
1105 asn_debug.put_line('p_oe_order_line_id = ' || p_oe_order_line_id);
1106 asn_debug.put_line('p_available_quantity = ' || p_available_quantity);
1107 asn_debug.put_line('p_tolerable_quantity = ' || p_tolerable_quantity);
1108 asn_debug.put_line('p_unit_of_measure = ' || p_unit_of_measure);
1109 asn_debug.put_line('p_secondary_available_qty = ' || p_secondary_available_qty);
1110 asn_debug.put_line('***END*** get_po_quantity');
1111 EXCEPTION
1112 WHEN OTHERS THEN
1113 po_message_s.sql_error('get_rma_quantity',
1114 x_progress,
1115 SQLCODE
1116 );
1117 RAISE;
1118 END get_rma_quantity;
1119
1120 /*===========================================================================
1121
1122 PROCEDURE NAME: get_shipment_quantity()
1123
1124 ===========================================================================*/
1125
1126 PROCEDURE get_shipment_quantity(p_shipment_line_id IN NUMBER,
1127 p_available_quantity IN OUT NOCOPY NUMBER,
1128 p_unit_of_measure IN OUT NOCOPY VARCHAR2,
1129 p_secondary_available_qty IN OUT NOCOPY NUMBER ) IS
1130
1131 x_progress VARCHAR2(3) := NULL;
1132 x_quantity_shipped NUMBER := 0;
1133 x_quantity_received NUMBER := 0;
1134 x_interface_quantity NUMBER := 0; /* in primary_uom */
1135 x_shipment_uom VARCHAR2(26);
1136 x_item_id NUMBER;
1137 x_primary_uom VARCHAR2(26);
1138 x_interface_qty_in_ship_uom NUMBER := 0;
1139
1140
1141 /*Bug# 1548597*/
1142 x_secondary_qty_shipped NUMBER := 0;
1143 x_secondary_qty_received NUMBER := 0;
1144 x_secondary_interface_qty NUMBER := 0;
1145 x_secondary_uom VARCHAR2(26);
1146 --end bug 1548597
1147
1148
1149 BEGIN
1150
1151 x_progress := '005';
1152
1153 /*
1154 ** Get shipment quantity information.
1155 */
1156
1157 SELECT nvl(rsl.quantity_shipped, 0),
1158 nvl(rsl.quantity_received, 0),
1159 rsl.item_id,
1160 rsl.unit_of_measure,
1161 /*Bug# 1548597 */
1162 nvl(rsl.secondary_quantity_shipped, 0),
1163 nvl(rsl.secondary_quantity_received, 0)
1164 --End Bug 1548597
1165 INTO x_quantity_shipped,
1166 x_quantity_received,
1167 x_item_id,
1168 x_shipment_uom,
1169 x_secondary_qty_shipped,
1170 x_secondary_qty_received
1171 FROM rcv_shipment_lines rsl
1172 WHERE rsl.shipment_line_id = p_shipment_line_id;
1173
1174 x_progress := '010';
1175
1176 /*
1177 ** Get any unprocessed receipt transaction against the
1178 ** shipment. x_interface_quantity is in primary uom.
1179 **
1180 ** The min(primary_uom) is neccessary because the
1181 ** select may return multiple rows and we only want one value
1182 ** to be returned. Having a sum and min group function in the
1183 ** select ensures that this sql statement will not raise a
1184 ** no_data_found exception even if no rows are returned.
1185 */
1186
1187 SELECT nvl(sum(decode(nvl(order_transaction_id,-999),-999,primary_quantity,nvl(interface_transaction_qty,0))),0),
1188 min(primary_unit_of_measure)
1189 INTO x_interface_quantity,
1190 x_primary_uom
1191 FROM rcv_transactions_interface
1192 WHERE (transaction_status_code = 'PENDING'
1193 and processing_status_code <> 'ERROR')
1194 AND transaction_type = 'RECEIVE'
1195 AND shipment_line_id = p_shipment_line_id;
1196
1197 IF (x_interface_quantity = 0) THEN
1198
1199 /*
1200 ** There is no unprocessed quantity. Simply set the
1201 ** x_interface_qty_in_ship_uom to 0. There is no need for uom
1202 ** conversion.
1203 */
1204
1205 x_interface_qty_in_ship_uom := 0;
1206
1207 ELSE
1208
1209 /*
1210 ** There is unprocessed quantity. Convert it to the shipment uom
1211 ** so that the available quantity can be calculated in the shipment uom
1212 */
1213 x_progress := '015';
1214
1215 po_uom_s.uom_convert(x_interface_quantity, x_primary_uom, x_item_id,
1216 x_shipment_uom, x_interface_qty_in_ship_uom);
1217
1218 END IF;
1219
1220 /*
1221 ** Calculate the quantity available to be received.
1222 */
1223
1224 p_available_quantity := x_quantity_shipped - x_quantity_received -
1225 x_interface_qty_in_ship_uom;
1226
1227 /*
1228 ** p_available_quantity can be negative if this shipment has been over
1229 ** received. In this case, the available quantity that needs to be passed
1230 ** back should be 0.
1231 */
1232
1233 IF (p_available_quantity < 0) THEN
1234
1235 p_available_quantity := 0;
1236
1237 END IF;
1238
1239 /*
1240 ** Return the SHIPMENT unit of measure
1241 */
1242
1243 p_unit_of_measure := x_shipment_uom;
1244
1245
1246 /*Bug # 1548597 */
1247 SELECT nvl(sum(secondary_quantity),0),
1248 min(secondary_unit_of_measure)
1249 INTO x_secondary_interface_qty,
1250 x_secondary_uom
1251 FROM rcv_transactions_interface
1252 WHERE (transaction_status_code = 'PENDING'
1253 and processing_status_code <> 'ERROR')
1254 AND transaction_type = 'RECEIVE'
1255 AND shipment_line_id = p_shipment_line_id;
1256
1257 /*
1258 ** Calculate the quantity available to be received.
1259 */
1260
1261 p_secondary_available_qty := x_secondary_qty_shipped - x_secondary_qty_received -
1262 x_secondary_interface_qty;
1263
1264 /*
1265 ** p_available_quantity can be negative if this shipment has been over
1266 ** received. In this case, the available quantity that needs to be passed
1267 ** back should be 0.
1268 */
1269
1270 IF (p_secondary_available_qty < 0) THEN
1271
1272 p_secondary_available_qty := 0;
1273
1274 END IF;
1275 /*End Bug # 1548597 */
1276
1277 EXCEPTION
1278
1279 WHEN OTHERS THEN
1280
1281 po_message_s.sql_error('get_shipment_quantity', x_progress, sqlcode);
1282
1283 RAISE;
1284
1285 END get_shipment_quantity;
1286
1287 /*===========================================================================
1288
1289 PROCEDURE NAME: get_transaction_quantity()
1290
1291 ===========================================================================*/
1292
1293 PROCEDURE get_transaction_quantity(p_transaction_id IN NUMBER,
1294 p_available_quantity IN OUT NOCOPY NUMBER,
1295 p_unit_of_measure IN OUT NOCOPY VARCHAR2,
1296 p_secondary_available_qty IN OUT NOCOPY NUMBER ) IS
1297
1298 x_progress VARCHAR2(3) := NULL;
1299 x_transaction_quantity NUMBER := 0;
1300 x_interface_quantity NUMBER := 0; /* in primary uom */
1301 x_transaction_uom VARCHAR2(26);
1302 x_primary_uom VARCHAR2(26);
1303 x_item_id NUMBER;
1304 x_interface_qty_in_trx_uom NUMBER;
1305
1306 /*Bug # 1548597 */
1307 x_secondary_transaction_qty NUMBER := 0;
1308 x_secondary_interface_qty NUMBER := 0;
1309 x_secondary_uom VARCHAR2(26);
1310 --end bug 1548597
1311
1312 BEGIN
1313
1314 x_progress := '005';
1315
1316 /*
1317 ** Get available supply quantity information.
1318 */
1319
1320 /*
1321 ** There may be no supply quantity hence the exception no data found
1322 ** needs to be trapped here
1323 */
1324
1325 -- bug 4873207
1326 IF (g_asn_debug = 'Y') THEN
1327 asn_debug.put_line('get_transaction_quantity >> ' || x_progress );
1328 asn_debug.put_line('p_transaction_id ' || p_transaction_id);
1329 END IF;
1330 -- bug 4873207
1331
1332 BEGIN
1333
1334 /*
1335 Bug#5369121 - Fetching the primary uom from rcv_supply or rcv_transactions
1336 rather than RTI since it could be null in RTI
1337 */
1338
1339 SELECT quantity,
1340 unit_of_measure,
1341 item_id,
1342 to_org_primary_uom
1343 INTO x_transaction_quantity,
1344 x_transaction_uom,
1345 x_item_id,
1346 x_primary_uom
1347 FROM rcv_supply
1348 WHERE supply_type_code = 'RECEIVING'
1349 AND supply_source_id = p_transaction_id;
1350
1351 /* Bug# 1548597 */
1352 select SUM(decode(transaction_type,
1353 'RECEIVE', Secondary_Quantity,
1354 'CORRECT',DECODE(Secondary_Quantity/DECODE(ABS(Secondary_Quantity),0,1,ABS(Secondary_Quantity)),
1355 1,Secondary_Quantity,
1356 -1,-1*Secondary_Quantity,
1357 Secondary_Quantity),
1358 'RETURN TO VENDOR', Secondary_Quantity,
1359 'RETURN TO RECEIVING', Secondary_Quantity,
1360 'ACCEPT', Secondary_Quantity,
1361 'REJECT', Secondary_Quantity,
1362 'DELIVER',-1*Secondary_Quantity,
1363 'UNORDERED', Secondary_Quantity,
1364 'MATCH',Secondary_Quantity,
1365 'TRANSFER',Secondary_quantity,0))
1366 into x_secondary_transaction_qty
1367 from rcv_transactions
1368 start with transaction_id = p_transaction_id
1369 connect by parent_transaction_id = prior transaction_id;
1370 -- Bug# 1548597
1371
1372 EXCEPTION
1373
1374 WHEN NO_DATA_FOUND THEN
1375
1376 x_transaction_quantity := 0;
1377
1378 -- bug 4873207
1379 SELECT rt.unit_of_measure,
1380 rsl.item_id,
1381 rt.primary_unit_of_measure
1382 INTO x_transaction_uom,
1383 x_item_id,
1384 x_primary_uom
1385 FROM rcv_transactions rt,
1386 rcv_shipment_lines rsl
1387 WHERE rsl.shipment_line_id = rt.shipment_line_id
1388 AND rt.transaction_id = p_transaction_id;
1389 -- bug 4873207
1390
1391
1392 WHEN OTHERS THEN RAISE;
1393
1394 END;
1395
1396 -- bug 4873207
1397 IF (g_asn_debug = 'Y') THEN
1398 asn_debug.put_line('get_transaction_quantity >> ');
1399 asn_debug.put_line('x_transaction_quantity '||x_transaction_quantity);
1400 asn_debug.put_line('x_transaction_uom '||x_transaction_uom);
1401 asn_debug.put_line('item_id '||x_item_id);
1402 asn_debug.put_line('x_secondary_transaction_qty '||x_secondary_transaction_qty);
1403 END IF;
1404 -- bug 4873207
1405
1406
1407 x_progress := '010';
1408
1409 /*
1410 ** Get any unprocessed receipt transaction against the
1411 ** parent transaction. x_interface_quantity is in primary uom.
1412 **
1413 ** The min(primary_uom) is neccessary because the
1414 ** select may return multiple rows and we only want one value
1415 ** to be returned. Having a sum and min group function in the
1416 ** select ensures that this sql statement will not raise a
1417 ** no_data_found exception even if no rows are returned.
1418 */
1419
1420 SELECT nvl(sum(decode(transaction_type,
1421 'CORRECT', -1 * (decode(nvl(order_transaction_id,-999),-999,primary_quantity,nvl(interface_transaction_qty,0))),
1422 decode(nvl(order_transaction_id,-999),-999,primary_quantity,nvl(interface_transaction_qty,0))
1423 )),0)
1424 INTO x_interface_quantity
1425 FROM rcv_transactions_interface
1426 WHERE (transaction_status_code = 'PENDING'
1427 and processing_status_code <> 'ERROR')
1428 AND parent_transaction_id = p_transaction_id;
1429
1430 IF (x_interface_quantity = 0) THEN
1431
1432 /*
1433 ** There is no unprocessed quantity. Simply set the
1434 ** x_interface_qty_in_trx_uom to 0. There is no need for uom
1435 ** conversion.
1436 */
1437
1438 x_interface_qty_in_trx_uom := 0;
1439
1440 ELSE
1441
1442 /*
1443 ** There is unprocessed quantity. Convert it to the transaction uom
1444 ** so that the available quantity can be calculated in the trx uom
1445 */
1446 IF (g_asn_debug = 'Y') THEN
1447 asn_debug.put_line('Before uom_convert:');
1448 asn_debug.put_line('x_interface_quantity:' || x_interface_quantity);
1449 asn_debug.put_line('x_primary_uom:' || x_primary_uom);
1450 asn_debug.put_line('x_transaction_uom:' || x_transaction_uom);
1451 asn_debug.put_line('x_item_id:' || x_item_id);
1452 END IF;
1453
1454 x_progress := '015';
1455 po_uom_s.uom_convert(x_interface_quantity, x_primary_uom, x_item_id,
1456 x_transaction_uom, x_interface_qty_in_trx_uom);
1457
1458 END IF;
1459
1460
1461
1462 /*
1463 ** Calculate the quantity available to be transacted
1464 */
1465
1466 p_available_quantity := x_transaction_quantity - x_interface_qty_in_trx_uom;
1467
1468 /*
1469 ** Return the parent transactions unit of measure
1470 */
1471
1472 p_unit_of_measure := x_transaction_uom;
1473
1474 /*dbms_output.put_line ('get_transaction_quantity.p_available_quantity = '||
1475 to_char(p_available_quantity));
1476
1477 dbms_output.put_line ('get_transaction_quantity.p_unit_of_measure = '||
1478 p_unit_of_measure);*/
1479
1480 /* Bug 1548597 */
1481 SELECT nvl(sum(decode(transaction_type,
1482 'CORRECT', -1 * (decode(nvl(order_transaction_id,-999),
1483 -999,primary_quantity,nvl(interface_transaction_qty,0))),
1484 secondary_quantity)),0),
1485 min(secondary_unit_of_measure)
1486 INTO x_secondary_interface_qty,
1487 x_secondary_uom
1488 FROM rcv_transactions_interface
1489 WHERE (transaction_status_code = 'PENDING'
1490 and processing_status_code <> 'ERROR')
1491 AND parent_transaction_id = p_transaction_id;
1492
1493 p_secondary_available_qty := x_secondary_transaction_qty - x_secondary_interface_qty;
1494 -- end bug 1548597
1495
1496
1497 EXCEPTION
1498
1499 WHEN OTHERS THEN
1500
1501 po_message_s.sql_error('get_transaction_quantity', x_progress, sqlcode);
1502
1503 RAISE;
1504
1505 END get_transaction_quantity;
1506
1507 /*===========================================================================
1508
1509 PROCEDURE NAME: get_transaction_amount()
1510
1511 p_transaction_id => RECEIVE transaction
1512
1513 Called when:
1514 txn type = NEGATIVE CORRECT AND parent type = RECEIVE => get_txn_amt(parent_id)
1515 txn type = POSITIVE CORRECT AND parent type = DELIVER => get_txn_amt(grand_parent_id)
1516
1517 ===========================================================================*/
1518
1519 PROCEDURE get_transaction_amount(p_transaction_id IN NUMBER,
1520 p_available_amount IN OUT NOCOPY NUMBER ) IS
1521
1522 x_progress VARCHAR2(3) := NULL;
1523 x_transaction_amount NUMBER := 0;
1524 x_interface_amount NUMBER := 0;
1525 x_interface_deliver_amount NUMBER := 0;
1526 l_deliver_id NUMBER;
1527 l_receive_correct NUMBER;
1528 l_deliver_correct NUMBER;
1529
1530 BEGIN
1531
1532 x_progress := '005';
1533
1534 /*
1535 ** Get available amount information from processed transactions.
1536 */
1537
1538 IF (g_asn_debug = 'Y') THEN
1539 asn_debug.put_line('get_transaction_amount ' );
1540 asn_debug.put_line('p_transaction_id '||p_transaction_id );
1541 end if;
1542 BEGIN
1543
1544 SELECT nvl(sum(amount),0)
1545 into l_receive_correct
1546 from rcv_transactions
1547 where parent_transaction_id = p_transaction_id
1548 and transaction_type = 'CORRECT';
1549
1550 IF (g_asn_debug = 'Y') THEN
1551 asn_debug.put_line('l_receive_correct '||l_receive_correct);
1552 end if;
1553 select transaction_id
1554 into l_deliver_id
1555 from rcv_transactions
1556 where parent_transaction_id= p_transaction_id
1557 and transaction_type='DELIVER';
1558
1559 IF (g_asn_debug = 'Y') THEN
1560 asn_debug.put_line('l_deliver_id '||l_deliver_id);
1561 end if;
1562 SELECT nvl(sum(amount),0)
1563 into l_deliver_correct
1564 from rcv_transactions
1565 where parent_transaction_id = l_deliver_id
1566 and transaction_type = 'CORRECT';
1567
1568 IF (g_asn_debug = 'Y') THEN
1569 asn_debug.put_line('l_deliver_correct '||l_deliver_correct);
1570 end if;
1571
1572 EXCEPTION
1573 WHEN NO_DATA_FOUND THEN
1574 x_transaction_amount := 0;
1575 END;
1576
1577 x_progress := '010';
1578
1579 /*
1580 ** Get any unprocessed receipt transaction against the
1581 ** parent transaction. x_interface_quantity is in primary uom.
1582 **
1583 ** The min(primary_uom) is neccessary because the
1584 ** select may return multiple rows and we only want one value
1585 ** to be returned. Having a sum and min group function in the
1586 ** select ensures that this sql statement will not raise a
1587 ** no_data_found exception even if no rows are returned.
1588 */
1589
1590 SELECT nvl(sum(decode(transaction_type,
1591 'CORRECT', -1 * (decode(nvl(order_transaction_id,-999),-999,amount,nvl(interface_transaction_amt,0))),
1592 decode(nvl(order_transaction_id,-999),-999,amount,nvl(interface_transaction_amt,0))
1593 )),0)
1594 INTO x_interface_amount
1595 FROM rcv_transactions_interface
1596 WHERE (transaction_status_code = 'PENDING'
1597 and processing_status_code <> 'ERROR')
1598 AND parent_transaction_id = p_transaction_id;
1599
1600 IF (g_asn_debug = 'Y') THEN
1601 asn_debug.put_line('x_interface_amount '||x_interface_amount);
1602 end if;
1603
1604 -- do the same for the children of the receive transaction
1605 SELECT nvl(sum(decode(transaction_type,
1606 'CORRECT', -1 * (decode(nvl(order_transaction_id,-999),-999,amount,nvl(interface_transaction_amt,0))),
1607 decode(nvl(order_transaction_id,-999),-999,amount,nvl(interface_transaction_amt,0))
1608 )),0)
1609 INTO x_interface_deliver_amount
1610 FROM rcv_transactions_interface
1611 WHERE (transaction_status_code = 'PENDING'
1612 and processing_status_code <> 'ERROR')
1613 AND parent_transaction_id = l_deliver_id;
1614
1615 IF (g_asn_debug = 'Y') THEN
1616 asn_debug.put_line('x_interface_deliver_amount '||x_interface_deliver_amount);
1617 END IF;
1618
1619 /*
1620 ** Calculate the quantity available to be transacted
1621 */
1622
1623 p_available_amount := l_receive_correct - l_deliver_correct - (x_interface_amount - x_interface_deliver_amount);
1624
1625 IF (g_asn_debug = 'Y') THEN
1626 asn_debug.put_line('p_available_amount '||p_available_amount);
1627 end if;
1628
1629 EXCEPTION
1630
1631 WHEN OTHERS THEN
1632
1633 IF (g_asn_debug = 'Y') THEN
1634 asn_debug.put_line('Exception in get_transaction_amount');
1635 end if;
1636 po_message_s.sql_error('get_transaction_amount', x_progress, sqlcode);
1637
1638 RAISE;
1639
1640 END get_transaction_amount;
1641
1642 /*===========================================================================
1643
1644 PROCEDURE NAME: get_correction_quantity()
1645
1646 ===========================================================================*/
1647
1648 PROCEDURE get_correction_quantity(p_correction_type IN VARCHAR2,
1649 p_parent_transaction_type IN VARCHAR2,
1650 p_receipt_source_code IN VARCHAR2,
1651 p_parent_id IN NUMBER,
1652 p_grand_parent_id IN NUMBER,
1653 p_available_quantity IN OUT NOCOPY NUMBER,
1654 p_tolerable_quantity IN OUT NOCOPY NUMBER,
1655 p_unit_of_measure IN OUT NOCOPY VARCHAR2,
1656 p_secondary_available_qty IN OUT NOCOPY NUMBER ) IS
1657
1658 x_progress VARCHAR2(3) := NULL;
1659 x_parent_uom VARCHAR2(26);
1660 x_item_id NUMBER;
1661 x_primary_uom VARCHAR2(26);
1662 use_primary_uom VARCHAR2(26) := NULL;
1663 x_trx_quantity NUMBER := 0;
1664 X_interface_quantity NUMBER := 0;
1665 invalid_parent_trx_type EXCEPTION;
1666 x_interface_qty_in_trx_uom NUMBER := 0;
1667 l_quantity_in_parent_uom RCV_TRANSACTIONS.quantity%TYPE; -- Bug 2737257
1668 BEGIN
1669
1670 IF (g_asn_debug = 'Y') THEN
1671 asn_debug.put_line(' in get_correction_quantity');
1672 end if;
1673 IF p_correction_type = 'NEGATIVE' THEN
1674
1675 /*
1676 ** Return transactions and negative corrections have the
1677 ** same logic for getting available quantity.
1678 */
1679
1680 IF (p_parent_transaction_type IN ('UNORDERED', 'RECEIVE', 'MATCH',
1681 'TRANSFER', 'ACCEPT', 'REJECT')) THEN
1682
1683 /*
1684 ** All of the above transactions supply is stored in RCV_SUPPLY.
1685 ** Use get_transaction_quantity logic to get the available quantity
1686 ** and uom.
1687 */
1688
1689 IF (g_asn_debug = 'Y') THEN
1690 asn_debug.put_line(' Before get_transaction_quantity');
1691 end if;
1692 get_transaction_quantity(p_parent_id, p_available_quantity,
1693 p_unit_of_measure,p_secondary_available_qty);
1694
1695
1696 ELSIF (p_parent_transaction_type IN
1697 ('RETURN TO VENDOR', 'RETURN TO CUSTOMER', 'DELIVER')) THEN
1698
1699 /*
1700 ** Return to Vendor and Deliver transactions do not have any
1701 ** supply associated with them. You need to get the available
1702 ** quantity from the actual transaction tables themselves.
1703 **
1704 ** Debug - Currently, (22-MAR-95) we do not support corrections
1705 ** to Return To Receiving transactions. However, it is a good
1706 ** candidate for an ER. If we do, we need to add to this function
1707 ** to handle this case.
1708 */
1709 IF (g_asn_debug = 'Y') THEN
1710 asn_debug.put_line(' Before get_deliver_quantity');
1711 end if;
1712
1713 get_deliver_quantity(p_parent_id, p_available_quantity,
1714 p_unit_of_measure,p_secondary_available_qty);
1715
1716 ELSE
1717
1718 /*
1719 ** The function was called with the wrong p_parent_transaction_type
1720 ** parameter. Raise an invalid parent transaction type exception.
1721 */
1722
1723 RAISE invalid_parent_trx_type;
1724
1725 END IF; /* Matches if (p_parent_transaction_type IN .... */
1726
1727 ELSIF p_correction_type = 'POSITIVE' THEN
1728
1729 /*
1730 ** In general, for +ve correction quantity, we need to calculate the
1731 ** outstanding quantity against the grand parent transaction. e.g.
1732 **
1733 ** Receipt 100 - p_grand_parent_id
1734 ** Transfer 60 - p_parent_id
1735 ** Correct ?? - p_transaction_type
1736 **
1737 ** To do a +ve correction against the transfer, we need to calculate
1738 ** the outstanding quantity against the Receipt (40).
1739 */
1740
1741 IF (p_parent_transaction_type IN ('RECEIVE', 'MATCH')) THEN
1742
1743 /*
1744 ** Need to calculate the outstanding quantity to be received
1745 ** either against the shipment or the PO depending on the
1746 ** receipt_source_code.
1747 ** This is the same logic to be used for get_receive_quantity().
1748 ** p_grand_parent_id is either the po_line_location_id or the
1749 ** rcv_shipment_line_id as the case may be.
1750 */
1751
1752 get_receive_quantity(p_grand_parent_id, p_receipt_source_code,
1753 p_available_quantity, p_tolerable_quantity,
1754 p_unit_of_measure,p_secondary_available_qty);
1755
1756 ELSIF (p_parent_transaction_type IN ('TRANSFER', 'ACCEPT', 'REJECT',
1757 'DELIVER', 'RETURN TO VENDOR', 'RETURN TO CUSTOMER')) THEN
1758
1759 /*
1760 ** Need to calculate the outstanding quantity against the parent
1761 ** of the above transactions. This will always be a receiving
1762 ** transaction. Hence, use get_transaction_quantity() function.
1763 ** p_grand_parent_id is the grand parent transaction for which
1764 ** we need to get the outstanding quantity.
1765 */
1766
1767 get_transaction_quantity(p_grand_parent_id, p_available_quantity,
1768 p_unit_of_measure,p_secondary_available_qty);
1769
1770 ELSIF (p_parent_transaction_type IN ('UNORDERED')) THEN
1771
1772 /*
1773 ** Need to calculate the outstanding quantity against the parent
1774 ** of the above transactions. This will always be a receiving
1775 ** transaction. Hence, use get_transaction_quantity() function.
1776 ** p_grand_parent_id is the grand parent transaction for which
1777 ** we need to get the outstanding quantity. The p_grand_parent_id
1778 ** which is set in the rcv_corrections_sv.post_query to be the
1779 ** parent transaction id
1780 */
1781
1782 get_transaction_quantity(p_grand_parent_id, p_available_quantity,
1783 p_unit_of_measure,p_secondary_available_qty);
1784 /*
1785 ** This is kind of goofy but since there are no limits on the
1786 ** positive correction to the unorderded receipt we have to just
1787 ** make this a huge quantity.
1788 */
1789
1790 p_available_quantity := 9999999999999999;
1791
1792 ELSE
1793
1794 /*
1795 ** The function was called with the wrong p_parent_transaction_type
1796 ** parameter. Raise an invalid parent transaction type exception.
1797 */
1798
1799 RAISE invalid_parent_trx_type;
1800
1801 END IF; /* Matches if p_parent_transaction_type IN .... */
1802
1803 /*
1804 ** Convert the available quantity and tolerable quantity to the
1805 ** parent's uom. This is neccessary because in the case of a +ve
1806 ** correction, the available quantity will be in the grand parent's
1807 ** unit of measure.
1808 */
1809
1810 /*
1811 ** Get the parent transaction's info.
1812 */
1813
1814 x_progress := '005';
1815
1816 select rt.unit_of_measure,
1817 rsl.item_id
1818 into x_parent_uom,
1819 x_item_id
1820 from rcv_transactions rt,
1821 rcv_shipment_lines rsl
1822 where rt.transaction_id = p_parent_id
1823 and rt.shipment_line_id = rsl.shipment_line_id;
1824
1825 /*
1826 ** Convert available quantity in the parent's unit of measure
1827 */
1828
1829 /*dbms_output.put_line ('get_correction_qty : p_unit_of_measure : '||
1830 p_unit_of_measure);
1831
1832 dbms_output.put_line ('get_correction_qty : x_parent_uom : '||
1833 x_parent_uom);*/
1834
1835
1836 /* Bug#1769067.smididud.Date:05/15/2001. */
1837
1838 IF ( (p_available_quantity <> 0) AND (p_unit_of_measure IS NOT NULL) ) THEN
1839
1840 po_uom_s.uom_convert(p_available_quantity, p_unit_of_measure,
1841 x_item_id, x_parent_uom,
1842 l_quantity_in_parent_uom -- Bug 2737257
1843 );
1844 p_available_quantity := l_quantity_in_parent_uom; -- Bug 2737257
1845
1846 END IF;
1847
1848 /*
1849 ** Convert the tolerable quantity to the parent's unit of measure
1850 */
1851
1852 IF (p_tolerable_quantity <> 0) THEN
1853
1854 po_uom_s.uom_convert(p_tolerable_quantity, p_unit_of_measure,
1855 x_item_id, x_parent_uom,
1856 l_quantity_in_parent_uom -- Bug 2737257
1857 );
1858 p_tolerable_quantity := l_quantity_in_parent_uom; -- Bug 2737257
1859
1860 END IF;
1861
1862 /*
1863 ** Return parent unit of measure
1864 */
1865
1866 p_unit_of_measure := x_parent_uom;
1867
1868 END IF; /* Matches if p_correction_type = 'NEGATIVE' .... */
1869
1870
1871 EXCEPTION
1872
1873 WHEN invalid_parent_trx_type THEN
1874
1875 /*
1876 ** debug - need to define a new message and also need to understand
1877 ** how exactly to handle application error messages. A call to
1878 ** some generic API is needed.
1879 */
1880
1881 RAISE;
1882
1883
1884 WHEN OTHERS THEN
1885
1886 po_message_s.sql_error('get_correction_quantity', x_progress, sqlcode);
1887
1888 RAISE;
1889
1890 END get_correction_quantity;
1891
1892 /*===========================================================================
1893
1894 PROCEDURE NAME: get_correction_amount()
1895
1896 ===========================================================================*/
1897
1898 PROCEDURE get_correction_amount(p_correction_type IN VARCHAR2,
1899 p_parent_transaction_type IN VARCHAR2,
1900 p_receipt_source_code IN VARCHAR2,
1901 p_parent_id IN NUMBER,
1902 p_grand_parent_id IN NUMBER,
1903 p_available_amount IN OUT NOCOPY NUMBER,
1904 p_tolerable_amount IN OUT NOCOPY NUMBER ) IS
1905
1906 x_progress VARCHAR2(3) := NULL;
1907 x_trx_amount NUMBER := 0;
1908 X_interface_amount NUMBER := 0;
1909 invalid_parent_trx_type EXCEPTION;
1910 BEGIN
1911
1912 IF (g_asn_debug = 'Y') THEN
1913 asn_debug.put_line('p_correction_type '||p_correction_type );
1914 asn_debug.put_line('p_parent_transaction_type '||p_parent_transaction_type );
1915 asn_debug.put_line('p_receipt_source_code '||p_receipt_source_code );
1916 asn_debug.put_line('p_parent_id '||p_parent_id );
1917 asn_debug.put_line('p_grand_parent_id '||p_grand_parent_id );
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 = 'RECEIVE' THEN
1927
1928 /*
1929 ** All of the above transactions supply is stored in RCV_SUPPLY.
1930 ** Use get_transaction_quantity logic to get the available quantity
1931 ** and uom.
1932 */
1933
1934 IF (g_asn_debug = 'Y') THEN
1935 asn_debug.put_line(' before get_transaction_amount');
1936 end if;
1937 get_transaction_amount(p_parent_id, p_available_amount);
1938
1939
1940 ELSIF p_parent_transaction_type = 'DELIVER' THEN
1941
1942 /*
1943 ** Return to Vendor and Deliver transactions do not have any
1944 ** supply associated with them. You need to get the available
1945 ** quantity from the actual transaction tables themselves.
1946 **
1947 ** Debug - Currently, (22-MAR-95) we do not support corrections
1948 ** to Return To Receiving transactions. However, it is a good
1949 ** candidate for an ER. If we do, we need to add to this function
1950 ** to handle this case.
1951 */
1952
1953 IF (g_asn_debug = 'Y') THEN
1954 asn_debug.put_line(' before get_deliver_amount');
1955 end if;
1956 get_deliver_amount(p_parent_id, p_available_amount);
1957
1958 ELSE
1959
1960 /*
1961 ** The function was called with the wrong p_parent_transaction_type
1962 ** parameter. Raise an invalid parent transaction type exception.
1963 */
1964
1965 RAISE invalid_parent_trx_type;
1966
1967 END IF; /* Matches if (p_parent_transaction_type IN .... */
1968
1969 ELSIF p_correction_type = 'POSITIVE' THEN
1970
1971 /*
1972 ** In general, for +ve correction quantity, we need to calculate the
1973 ** outstanding quantity against the grand parent transaction. e.g.
1974 **
1975 ** Receipt 100 - p_grand_parent_id
1976 ** Transfer 60 - p_parent_id
1977 ** Correct ?? - p_transaction_type
1978 **
1979 ** To do a +ve correction against the transfer, we need to calculate
1980 ** the outstanding quantity against the Receipt (40).
1981 */
1982
1983 IF p_parent_transaction_type = 'RECEIVE' THEN
1984
1985 /*
1986 ** Need to calculate the outstanding quantity to be received
1987 ** either against the shipment or the PO depending on the
1988 ** receipt_source_code.
1989 ** This is the same logic to be used for get_receive_quantity().
1990 ** p_grand_parent_id is either the po_line_location_id or the
1991 ** rcv_shipment_line_id as the case may be.
1992 */
1993
1994 IF (g_asn_debug = 'Y') THEN
1995 asn_debug.put_line(' before get_receive_amount');
1996 end if;
1997 get_receive_amount(p_grand_parent_id, p_receipt_source_code,
1998 p_available_amount, p_tolerable_amount);
1999
2000 ELSIF p_parent_transaction_type = 'DELIVER' THEN
2001
2002 /*
2003 ** Need to calculate the outstanding quantity against the parent
2004 ** of the above transactions. This will always be a receiving
2005 ** transaction. Hence, use get_transaction_quantity() function.
2006 ** p_grand_parent_id is the grand parent transaction for which
2007 ** we need to get the outstanding quantity.
2008 */
2009
2010 IF (g_asn_debug = 'Y') THEN
2011 asn_debug.put_line(' before get_transaction_amount');
2012 end if;
2013 get_transaction_amount(p_grand_parent_id, p_available_amount);
2014
2015 ELSE
2016
2017 /*
2018 ** The function was called with the wrong p_parent_transaction_type
2019 ** parameter. Raise an invalid parent transaction type exception.
2020 */
2021
2022 RAISE invalid_parent_trx_type;
2023
2024 END IF; /* Matches if p_parent_transaction_type IN .... */
2025
2026 END IF; /* Matches if p_correction_type = 'NEGATIVE' .... */
2027
2028
2029 EXCEPTION
2030
2031 WHEN invalid_parent_trx_type THEN
2032
2033 /*
2034 ** debug - need to define a new message and also need to understand
2035 ** how exactly to handle application error messages. A call to
2036 ** some generic API is needed.
2037 */
2038
2039 RAISE;
2040
2041
2042 WHEN OTHERS THEN
2043
2044 po_message_s.sql_error('get_correction_amount', x_progress, sqlcode);
2045
2046 RAISE;
2047
2048 END get_correction_amount;
2049
2050 /*===========================================================================
2051
2052 PROCEDURE NAME: get_deliver_quantity()
2053
2054 ===========================================================================*/
2055
2056 PROCEDURE get_deliver_quantity(p_transaction_id IN NUMBER,
2057 p_available_quantity IN OUT NOCOPY NUMBER,
2058 p_unit_of_measure IN OUT NOCOPY VARCHAR2,
2059 p_secondary_available_qty IN OUT NOCOPY NUMBER ) IS
2060
2061 x_progress VARCHAR2(3) := NULL;
2062 x_deliver_quantity NUMBER := 0;
2063 x_transaction_quantity NUMBER := 0;
2064 x_trx_quantity NUMBER := 0; /* in primary uom */
2065 x_interface_quantity NUMBER := 0; /* in primary uom */
2066 primary_trx_qty NUMBER := 0; /* in primary uom */
2067 x_deliver_uom VARCHAR2(26);
2068 x_primary_uom VARCHAR2(26);
2069 use_primary_uom VARCHAR2(26) := NULL;
2070 x_item_id NUMBER;
2071 x_interface_qty_in_trx_uom NUMBER;
2072
2073 /* Bug# 1548597 */
2074 x_secondary_deliver_quantity NUMBER := 0;
2075 x_secondary_deliver_uom VARCHAR2(26);
2076 x_secondary_interface_qty NUMBER := 0;
2077 x_secondary_trx_quantity NUMBER := 0;
2078 -- end bug 1548597
2079
2080 /* Bug 3735987 Start declarations*/
2081 l_consigned_flag rcv_transactions.consigned_flag%TYPE;
2082 l_org_id rcv_transactions.organization_id%TYPE;
2083 l_consigned_quantity NUMBER;
2084 l_opm_installed BOOLEAN;
2085 l_opm_process_org VARCHAR2(1);
2086 l_vendor_site_id rcv_transactions.vendor_site_id%TYPE;
2087 l_subinventory rcv_transactions.subinventory%TYPE;
2088 l_locator_id rcv_transactions.locator_id%TYPE;
2089 l_po_header_id rcv_transactions.po_header_id%TYPE;
2090 l_po_line_id rcv_transactions.po_line_id%TYPE;
2091 l_item_revision rcv_shipment_lines.item_revision%TYPE;
2092 l_primary_rt_uom rcv_transactions.primary_unit_of_measure%TYPE;
2093
2094 l_return_status VARCHAR2(1);
2095 l_msg_count NUMBER;
2096 l_msg_data VARCHAR2(2000);
2097 l_revision_control VARCHAR2(1);
2098 l_quantity_on_hand NUMBER;
2099 l_consigned_deliver_uom_qty NUMBER;
2100 /* Bug 3735987 End declarations*/
2101
2102 BEGIN
2103
2104 x_progress := '005';
2105
2106 /*
2107 ** Get available transaction quantity information.
2108 */
2109
2110 /* Bug 3735987 : If the item is consigned then we need to check the quantity
2111 ** available in the consigned stock. We cannot have returns or corrections
2112 ** on the quantity in the Regular stock.
2113 */
2114
2115 select rt.quantity,
2116 rt.unit_of_measure,
2117 rsl.item_id,
2118 /* bug# 1548597 */
2119 rt.secondary_quantity,
2120 rt.secondary_unit_of_measure,
2121 --end bug # 1548597
2122 /* Bug 3735987 Start */
2123 rt.consigned_flag,
2124 rt.organization_id,
2125 rt.vendor_site_id,
2126 rt.subinventory,
2127 rt.locator_id,
2128 rt.po_header_id,
2129 rt.po_line_id,
2130 rsl.item_revision,
2131 rt.primary_unit_of_measure
2132 into x_deliver_quantity,
2133 x_deliver_uom,
2134 x_item_id,
2135 x_secondary_deliver_quantity,
2136 x_secondary_deliver_uom,
2137 l_consigned_flag ,
2138 l_org_id,
2139 l_vendor_site_id,
2140 l_subinventory ,
2141 l_locator_id,
2142 l_po_header_id,
2143 l_po_line_id,
2144 l_item_revision,
2145 l_primary_rt_uom
2146 from rcv_transactions rt,
2147 rcv_shipment_lines rsl
2148 where rt.transaction_id = p_transaction_id
2149 and rt.shipment_line_id = rsl.shipment_line_id;
2150
2151 x_progress := '010';
2152
2153 /*
2154 ** Get any unprocessed receipt transaction against the
2155 ** parent transaction. x_interface_quantity is in primary uom.
2156 **
2157 ** The min(primary_uom) is neccessary because the
2158 ** select may return multiple rows and we only want one value
2159 ** to be returned. Having a sum and min group function in the
2160 ** select ensures that this sql statement will not raise a
2161 ** no_data_found exception even if no rows are returned.
2162 */
2163
2164 SELECT nvl(sum(decode(transaction_type,
2165 'CORRECT', -1 * (decode(nvl(order_transaction_id,-999),-999,primary_quantity,nvl(interface_transaction_qty,0))),
2166 decode(nvl(order_transaction_id,-999),-999,primary_quantity,nvl(interface_transaction_qty,0))
2167 )),0),
2168 min(primary_unit_of_measure)
2169 INTO x_interface_quantity,
2170 x_primary_uom
2171 FROM rcv_transactions_interface
2172 WHERE (transaction_status_code = 'PENDING'
2173 and processing_status_code <> 'ERROR')
2174 AND parent_transaction_id = p_transaction_id;
2175
2176 IF (x_interface_quantity = 0) THEN
2177
2178 /*
2179 ** There is no unprocessed quantity. Simply set the
2180 ** x_interface_qty_in_trx_uom to 0. There is no need for uom
2181 ** conversion.
2182 */
2183
2184 x_interface_qty_in_trx_uom := 0;
2185
2186 ELSE
2187
2188 /*
2189 ** There is unprocessed quantity. Convert it to the transaction uom
2190 ** so that the available quantity can be calculated in the trx uom
2191 */
2192
2193 po_uom_s.uom_convert(x_interface_quantity, x_primary_uom, x_item_id,
2194 x_deliver_uom, x_interface_qty_in_trx_uom);
2195
2196 END IF;
2197
2198 /* Bug 1548597 */
2199 SELECT nvl(sum(decode(transaction_type,
2200 'CORRECT', -1 * secondary_quantity,
2201 secondary_quantity)),0)
2202 INTO x_secondary_interface_qty
2203 FROM rcv_transactions_interface
2204 WHERE (transaction_status_code = 'PENDING'
2205 and processing_status_code <> 'ERROR')
2206 AND parent_transaction_id = p_transaction_id;
2207
2208 p_secondary_available_qty := x_secondary_deliver_quantity - x_secondary_interface_qty;
2209
2210 -- end bug# 1548507
2211
2212 /*
2213 ** Calculate the quantity available to be transacted
2214 */
2215
2216 p_available_quantity := x_deliver_quantity - x_interface_qty_in_trx_uom;
2217
2218 /*
2219 ** Return the parent transactions unit of measure
2220 */
2221
2222 p_unit_of_measure := x_deliver_uom;
2223
2224
2225 /*
2226 ** Get any rows already precessed against this transaction
2227 ** parent transaction. x_interface_quantity is in primary uom.
2228 **
2229 ** The min(primary_uom) is neccessary because the
2230 ** select may return multiple rows and we only want one value
2231 ** to be returned. Having a sum and min group function in the
2232 ** select ensures that this sql statement will not raise a
2233 ** no_data_found exception even if no rows are returned.
2234 */
2235
2236 SELECT nvl(sum(decode(transaction_type,
2237 'CORRECT', -1 * primary_quantity,
2238 primary_quantity)),0),
2239 min(primary_unit_of_measure)
2240 INTO x_trx_quantity,
2241 x_primary_uom
2242 FROM rcv_transactions
2243 WHERE parent_transaction_id = p_transaction_id
2244 AND transaction_type in ('CORRECT','RETURN TO RECEIVING');
2245
2246 IF (x_primary_uom IS NOT NULL) THEN
2247
2248 use_primary_uom := x_primary_uom;
2249
2250 END IF;
2251
2252 /* Bug# 1548597 */
2253 SELECT nvl(sum(decode(transaction_type,
2254 'CORRECT', -1 * secondary_quantity,
2255 secondary_quantity)),0)
2256 INTO x_secondary_trx_quantity
2257 FROM rcv_transactions
2258 WHERE parent_transaction_id = p_transaction_id
2259 AND transaction_type in ('CORRECT','RETURN TO RECEIVING');
2260 --end Bug# 1548597
2261
2262 IF (x_trx_quantity <> 0 AND use_primary_uom IS NOT NULL) THEN
2263
2264 /*
2265 ** There is unprocessed quantity. Convert it to the transaction uom
2266 ** so that the available quantity can be calculated in the trx uom
2267 */
2268 /*dbms_output.put_line ('get_correction_qty : use_primary_uom: '||
2269 use_primary_uom);
2270
2271 dbms_output.put_line ('get_correction_qty : p_unit_of_measure : '||
2272 p_unit_of_measure);*/
2273
2274
2275 x_progress := '015';
2276
2277 /* Bug# 2274636 : UOM convert function should be called only
2278 ** when uoms are different. Calling the UOM convert function
2279 ** at this place affects the computation of available quantity
2280 */
2281
2282 If ( use_primary_uom = p_unit_of_measure ) then
2283 primary_trx_qty := x_trx_quantity ;
2284 else
2285 po_uom_s.uom_convert(x_trx_quantity,
2286 use_primary_uom, x_item_id,
2287 p_unit_of_measure, primary_trx_qty);
2288 end if;
2289
2290
2291 /*
2292 ** Calculate the quantity available to be transacted
2293 */
2294
2295 p_available_quantity := p_available_quantity - primary_trx_qty;
2296
2297 /* Bug 1548597 */
2298 p_secondary_available_qty := p_secondary_available_qty - x_secondary_trx_quantity;
2299 --end Bug# 1548597
2300
2301
2302 END IF;
2303
2304 /*dbms_output.put_line ('Convert = ' || to_char(primary_trx_qty));
2305 dbms_output.put_line ('Avail = ' || to_char(p_available_quantity ));*/
2306
2307 /*
2308 ** Bug#4587282
2309 ** Moved the following piece of code here so that the stock in
2310 ** consigned inventory would be checked against the actual
2311 ** quantity available for the parent transaction
2312 */
2313 IF (l_consigned_flag = 'Y') THEN
2314 /* INVCONV PBAMB BEGIN - Remove the restriction for consigned items */
2315 /* If item is consigned, check if OPM is installed and the organization is a
2316 ** OPM process organization. We do not need to check the stock availability
2317 ** for OPM enabled organization.
2318 DECLARE
2319 opm_status varchar2(10);
2320 opm_ind varchar2(10);
2321 opm_ora_schema varchar2(10);
2322 BEGIN
2323 l_opm_installed := fnd_installation.get_app_info('GMI',opm_status,opm_ind,opm_ora_schema);
2324 l_opm_process_org := PO_GML_DB_COMMON.check_process_org(l_org_id);
2325 END;
2326
2327 IF NOT(( l_opm_installed = TRUE ) AND (l_opm_process_org = 'Y')) THEN
2328 */
2329 /* Check for the item revision control. If item is not revision controlled
2330 ** pass the item revision as null.
2331 */
2332 SELECT decode(msi.revision_qty_control_code,1,'F',2,'T')
2333 INTO l_revision_control
2334 FROM mtl_system_items_b msi
2335 WHERE msi.inventory_item_id = x_item_id
2336 AND msi.organization_id = l_org_id;
2337
2338 IF ( l_revision_control = 'F' ) THEN
2339 l_item_revision := NULL;
2340 END IF;
2341
2342 /* Call inventory API to get quantity available in consigned inventory.
2343 */
2344
2345 INV_CONSIGNED_VALIDATIONS_GRP.get_consigned_quantity (
2346 p_api_version_number => 1.0,
2347 p_init_msg_lst => 'F',
2348 x_return_status => l_return_status,
2349 x_msg_count => l_msg_count,
2350 x_msg_data => l_msg_data,
2351 p_tree_mode => NULL,
2352 p_organization_id => l_org_id,
2353 p_owning_org_id => l_vendor_site_id,
2354 p_planning_org_id => NULL,
2355 p_inventory_item_id => x_item_id,
2356 p_is_revision_control => l_revision_control,
2357 p_is_lot_control => 'F',
2358 p_is_serial_control => 'F',
2359 p_revision => l_item_revision,
2360 p_lot_number => NULL,
2361 p_lot_expiration_date => NULL,
2362 p_subinventory_code => l_subinventory,
2363 p_locator_id => l_locator_id,
2364 p_source_type_id => 1,
2365 p_demand_source_line_id => l_po_line_id,
2366 p_demand_source_header_id => l_po_header_id,
2367 p_demand_source_name => NULL,
2368 p_onhand_source => 3,
2369 p_cost_group_id => NULL,
2370 p_query_mode => 1,
2371 x_qoh => l_quantity_on_hand,
2372 x_att => l_consigned_quantity );
2373
2374 IF (l_return_status = 'S') THEN
2375 /* The Inventory API returns the quantity in primary UOM. We need to
2376 ** convert the quantity in the deliver UOM.
2377 */
2378 IF ( l_primary_rt_uom <> x_deliver_uom ) THEN
2379 PO_UOM_S.uom_convert(l_consigned_quantity,
2380 l_primary_rt_uom,
2381 x_item_id,
2382 x_deliver_uom,
2383 l_consigned_deliver_uom_qty );
2384 ELSE
2385 l_consigned_deliver_uom_qty := l_consigned_quantity;
2386 END IF;
2387
2388 /* If consigned quantity is greater than or equal to the delivered quantity,
2389 ** then the available quantity is same as the quantity delivered. Else the
2390 ** available quantity is equal to the quantity in consigned inventory.
2391 */
2392 -- Bug 4587282 : use p_available_quantity instead of x_deliver_quantity in the IF stmt.
2393 /* IF ( l_consigned_deliver_uom_qty < x_deliver_quantity ) THEN
2394 x_deliver_quantity := l_consigned_deliver_uom_qty;
2395 END IF;
2396 */
2397 IF ( l_consigned_deliver_uom_qty < p_available_quantity ) THEN
2398 p_available_quantity := l_consigned_deliver_uom_qty;
2399 END IF;
2400 ELSE
2401 IF (g_asn_debug = 'Y') THEN
2402 asn_debug.put_line('Error in get_deliver_quantity() during call to INV consigned API');
2403 asn_debug.put_line('Return status : '||l_return_status);
2404 asn_debug.put_line('Error Message : '||SUBSTRB(l_msg_data,1,50));
2405 END IF;
2406 APP_EXCEPTION.RAISE_EXCEPTION;
2407 --END IF;
2408 END IF;
2409 END IF;
2410
2411 EXCEPTION
2412
2413 WHEN OTHERS THEN
2414
2415 po_message_s.sql_error('get_deliver_quantity', x_progress, sqlcode);
2416
2417 RAISE;
2418
2419 END get_deliver_quantity;
2420
2421 /*===========================================================================
2422
2423 PROCEDURE NAME: get_deliver_amount()
2424
2425 Algo: Amount available for deliver :=Total amount delivered + Corrections on delivery.
2426 ===========================================================================*/
2427
2428 PROCEDURE get_deliver_amount(p_transaction_id IN NUMBER,
2429 p_available_amount IN OUT NOCOPY NUMBER ) IS
2430
2431 x_progress VARCHAR2(3) := NULL;
2432 x_deliver_amount NUMBER := 0;
2433 x_transaction_amount NUMBER := 0;
2434 x_interface_amount NUMBER := 0;
2435
2436 BEGIN
2437
2438 x_progress := '005';
2439
2440 IF (g_asn_debug = 'Y') THEN
2441 asn_debug.put_line('in get_receive_amount ' );
2442 asn_debug.put_line('p_transaction_id '||p_transaction_id);
2443 END IF;
2444 /*
2445 ** Get available transaction amount information.
2446 */
2447
2448 select rt.amount
2449 into x_deliver_amount
2450 from rcv_transactions rt
2451 where rt.transaction_id = p_transaction_id;
2452
2453 IF (g_asn_debug = 'Y') THEN
2454 asn_debug.put_line('x_deliver_amount '||x_deliver_amount );
2455 end if;
2456 x_progress := '010';
2457
2458 /*
2459 ** Get any unprocessed receipt transaction against the
2460 ** parent transaction. x_interface_quantity is in primary uom.
2461 */
2462
2463 SELECT nvl(sum(decode(transaction_type,
2464 'CORRECT', -1 * (decode(nvl(order_transaction_id,-999),-999,amount,nvl(interface_transaction_amt,0))),
2465 decode(nvl(order_transaction_id,-999),-999,amount,nvl(interface_transaction_amt,0))
2466 )),0)
2467 INTO x_interface_amount
2468 FROM rcv_transactions_interface
2469 WHERE (transaction_status_code = 'PENDING'
2470 and processing_status_code <> 'ERROR')
2471 AND parent_transaction_id = p_transaction_id;
2472
2473 IF (g_asn_debug = 'Y') THEN
2474 asn_debug.put_line('x_interface_amount '||x_interface_amount );
2475 end if;
2476 /*
2477 ** Calculate the quantity available to be transacted
2478 */
2479
2480 p_available_amount := x_deliver_amount - x_interface_amount;
2481
2482 IF (g_asn_debug = 'Y') THEN
2483 asn_debug.put_line('p_available_amount '||p_available_amount );
2484 end if;
2485 /*
2486 ** Get any rows already precessed against this transaction
2487 ** parent transaction. x_interface_quantity is in primary uom.
2488 **
2489 ** The min(primary_uom) is neccessary because the
2490 ** select may return multiple rows and we only want one value
2491 ** to be returned. Having a sum and min group function in the
2492 ** select ensures that this sql statement will not raise a
2493 ** no_data_found exception even if no rows are returned.
2494 */
2495
2496 SELECT nvl(sum(amount),0)
2497 INTO x_transaction_amount
2498 FROM rcv_transactions
2499 WHERE parent_transaction_id = p_transaction_id
2500 AND transaction_type = 'CORRECT';
2501
2502 IF (g_asn_debug = 'Y') THEN
2503 asn_debug.put_line('x_transaction_amount '||x_transaction_amount );
2504 end if;
2505 x_progress := '015';
2506
2507 /*
2508 ** Calculate the quantity available to be transacted
2509 */
2510
2511 p_available_amount := p_available_amount + x_transaction_amount;
2512 IF (g_asn_debug = 'Y') THEN
2513 asn_debug.put_line('p_available_amount 1 '||p_available_amount );
2514 end if;
2515
2516 EXCEPTION
2517
2518 WHEN OTHERS THEN
2519
2520 po_message_s.sql_error('get_deliver_amount', x_progress, sqlcode);
2521
2522 RAISE;
2523
2524 END get_deliver_amount;
2525
2526 /*===========================================================================
2527
2528 PROCEDURE NAME: get_po_dist_quantity()
2529
2530 ===========================================================================*/
2531
2532 PROCEDURE get_po_dist_quantity(p_po_distribution_id IN NUMBER,
2533 p_available_quantity IN OUT NOCOPY NUMBER,
2534 p_tolerable_quantity IN OUT NOCOPY NUMBER,
2535 p_unit_of_measure IN OUT NOCOPY VARCHAR2) IS
2536
2537 x_progress VARCHAR2(3) := NULL;
2538 x_deliver_quantity NUMBER := 0;
2539 x_balance_receipt_quantity NUMBER := 0;
2540 x_interface_quantity NUMBER := 0; /* in primary uom */
2541 x_primary_uom VARCHAR2(26);
2542 x_item_id NUMBER;
2543 x_interface_qty_in_trx_uom NUMBER;
2544
2545 -- 1337787
2546 x_qty_rcv_tolerance NUMBER := 0;
2547 x_qty_ordered NUMBER := 0;
2548 x_qty_received NUMBER := 0;
2549 x_qty_cancelled NUMBER := 0;
2550 l_quantity NUMBER := 0; /* Bug 1710046 */
2551
2552 BEGIN
2553
2554 x_progress := '005';
2555
2556 BEGIN
2557 /* Bug 1710046 - The following sql is to get the quantity
2558 returned to receiving or quantity received but not delivered
2559 for a particular distribution for a PO having multiple
2560 Distribution. This quantity should not be shown in
2561 Enter Receipt form */
2562
2563 select nvl(sum(decode(supply_type_code,'RECEIVING',quantity,0)),0)
2564 into l_quantity
2565 from mtl_supply
2566 where po_distribution_id = p_po_distribution_id;
2567
2568 /* GMUDGAL - 04-FEB-98 - Bug #610897
2569 ** Here the problem was that if the shipment has been received
2570 ** within tolerance then in the C code we are removing the
2571 ** balance mtl_supply so the above select will raise no_data_found.
2572 ** We now select from po_distributions the quantity yet to be
2573 ** delivered so that after exploding the distributions we don't
2574 ** see zero quantities
2575 ** Also see 624832.
2576 */
2577
2578 select (pod.QUANTITY_ORDERED - nvl(pod.QUANTITY_DELIVERED,0) -
2579 nvl(pod.QUANTITY_CANCELLED,0)) qty,
2580 (poll.quantity - nvl(poll.quantity_received,0) -
2581 nvl(poll.quantity_cancelled,0)) qty_rcvd,
2582 pol.UNIT_MEAS_LOOKUP_CODE, -- should get it from po_lines actually
2583 pol.item_id,
2584 1 + (nvl(poll.qty_rcv_tolerance,0)/100), -- 1337787
2585 nvl(poll.quantity,0),
2586 nvl(poll.quantity_received,0),
2587 nvl(poll.quantity_cancelled,0)
2588 INTO p_available_quantity,
2589 x_balance_receipt_quantity,
2590 p_unit_of_measure,
2591 x_item_id,
2592 x_qty_rcv_tolerance,
2593 x_qty_ordered,
2594 x_qty_received,
2595 x_qty_cancelled
2596 from po_distributions_all pod, --<Shared Proc FPJ>
2597 po_line_locations_all poll, --<Shared Proc FPJ>
2598 po_lines_all pol --<Shared Proc FPJ>
2599 where pod.line_location_id = poll.line_location_id
2600 and pod.po_distribution_id = p_po_distribution_id
2601 and pod.po_line_id = pol.po_line_id;
2602
2603 /* Bug 1710046 - Deducting the quantity received but not delivered
2604 ** from the available quantity as this quantity should not be
2605 ** shown in Enter Receipts form */
2606
2607 p_available_quantity := p_available_quantity - l_quantity;
2608
2609
2610 /*GMUDGAL 610897
2611 ** Check if there is some quantity which has been received
2612 ** but not delivered yet. In that case we want to show the
2613 ** quantity in the form as zero.
2614 */
2615
2616 if (p_available_quantity > 0) and
2617 (x_balance_receipt_quantity <= 0) then
2618 p_available_quantity := 0;
2619 p_unit_of_measure := '';
2620 end if;
2621
2622 exception
2623 when no_data_found then
2624 p_available_quantity := 0;
2625 p_unit_of_measure := '';
2626
2627 WHEN OTHERS THEN RAISE;
2628
2629 END;
2630
2631 x_progress := '015';
2632
2633 /* Bug# 2123470 : Primary Unit of Measure cannot have value
2634 for One time Items. So Added a decode statement to fetch
2635 unit_of_measure in case of One Time Items and Primary
2636 Unit of Measure for Inventory Items.
2637 */
2638
2639 SELECT nvl(sum(decode(transaction_type,
2640 'CORRECT', -1 * (decode(nvl(order_transaction_id,-999),-999,primary_quantity,nvl(interface_transaction_qty,0))),
2641 decode(nvl(order_transaction_id,-999),-999,primary_quantity,nvl(interface_transaction_qty,0))
2642 )),0),
2643 decode(min(item_id),null,min(unit_of_measure),min(primary_unit_of_measure))
2644 INTO x_interface_quantity,
2645 x_primary_uom
2646 FROM rcv_transactions_interface
2647 WHERE (transaction_status_code = 'PENDING'
2648 and processing_status_code <> 'ERROR')
2649 AND po_distribution_id = p_po_distribution_id;
2650
2651 IF (x_interface_quantity = 0) THEN
2652
2653 /*
2654 ** There is no unprocessed quantity. Simply set the
2655 ** x_interface_qty_in_trx_uom to 0. There is no need for uom
2656 ** conversion.
2657 */
2658
2659 x_interface_qty_in_trx_uom := 0;
2660
2661 ELSE
2662
2663 /*
2664 ** There is unprocessed quantity. Convert it to the transaction uom
2665 ** so that the available quantity can be calculated in the trx uom
2666 */
2667
2668 po_uom_s.uom_convert(x_interface_quantity, x_primary_uom, x_item_id,
2669 p_unit_of_measure, x_interface_qty_in_trx_uom);
2670
2671 END IF;
2672
2673 /*
2674 ** Calculate the quantity available to be transacted
2675 */
2676
2677 p_available_quantity := p_available_quantity - x_interface_qty_in_trx_uom;
2678
2679 IF (p_available_quantity < 0) THEN
2680
2681 p_available_quantity := 0;
2682
2683 END IF;
2684
2685 -- 1337787
2686 p_tolerable_quantity := (x_qty_ordered * x_qty_rcv_tolerance)-
2687 x_qty_received - x_qty_cancelled -
2688 x_interface_qty_in_trx_uom;
2689
2690 IF (p_tolerable_quantity < 0) THEN
2691
2692 p_tolerable_quantity := 0;
2693
2694 END IF;
2695
2696 EXCEPTION
2697
2698 WHEN OTHERS THEN
2699
2700 po_message_s.sql_error('get_po_dist_quantity',
2701 x_progress, sqlcode);
2702
2703 RAISE;
2704
2705 END get_po_dist_quantity;
2706
2707
2708 /*===========================================================================
2709
2710 PROCEDURE NAME: get_po_dist_amount()
2711
2712 ===========================================================================*/
2713
2714 PROCEDURE get_po_dist_amount(p_po_distribution_id IN NUMBER,
2715 p_available_amount IN OUT NOCOPY NUMBER,
2716 p_tolerable_amount IN OUT NOCOPY NUMBER) IS
2717
2718 x_progress VARCHAR2(3) := NULL;
2719 x_deliver_amount NUMBER := 0;
2720 x_balance_receipt_amount NUMBER := 0;
2721 x_interface_amount NUMBER := 0; /* in primary uom */
2722
2723 -- 1337787
2724 x_amt_rcv_tolerance NUMBER := 0;
2725 x_amt_ordered NUMBER := 0;
2726 x_amt_received NUMBER := 0;
2727 x_amt_cancelled NUMBER := 0;
2728 l_amount NUMBER := 0; /* Bug 1710046 */
2729
2730 BEGIN
2731
2732 x_progress := '005';
2733
2734 BEGIN
2735
2736 select (pod.AMOUNT_ORDERED - nvl(pod.AMOUNT_DELIVERED,0) -
2737 nvl(pod.AMOUNT_CANCELLED,0)) amt,
2738 (poll.amount - nvl(poll.amount_received,0) -
2739 nvl(poll.amount_cancelled,0)) amt_rcvd,
2740 1 + (nvl(poll.qty_rcv_tolerance,0)/100), -- 1337787
2741 nvl(poll.amount,0),
2742 nvl(poll.amount_received,0),
2743 nvl(poll.amount_cancelled,0)
2744 INTO p_available_amount,
2745 x_balance_receipt_amount,
2746 x_amt_rcv_tolerance,
2747 x_amt_ordered,
2748 x_amt_received,
2749 x_amt_cancelled
2750 from po_distributions_all pod, --<Shared Proc FPJ>
2751 po_line_locations_all poll, --<Shared Proc FPJ>
2752 po_lines_all pol --<Shared Proc FPJ>
2753 where pod.line_location_id = poll.line_location_id
2754 and pod.po_distribution_id = p_po_distribution_id
2755 and pod.po_line_id = pol.po_line_id;
2756
2757
2758
2759
2760 if (p_available_amount > 0) and
2761 (x_balance_receipt_amount <= 0) then
2762 p_available_amount := 0;
2763 end if;
2764
2765 exception
2766 when no_data_found then
2767 p_available_amount := 0;
2768
2769 WHEN OTHERS THEN RAISE;
2770
2771 END;
2772
2773 x_progress := '015';
2774
2775
2776 SELECT nvl(sum(decode(transaction_type,
2777 'CORRECT', -1 * (decode(nvl(order_transaction_id,-999),-999,amount,nvl(interface_transaction_amt,0))),
2778 decode(nvl(order_transaction_id,-999),-999,amount,nvl(interface_transaction_amt,0))
2779 )),0)
2780 INTO x_interface_amount
2781 FROM rcv_transactions_interface
2782 WHERE (transaction_status_code = 'PENDING'
2783 and processing_status_code <> 'ERROR')
2784 AND po_distribution_id = p_po_distribution_id;
2785
2786
2787 /*
2788 ** Calculate the quantity available to be transacted
2789 */
2790
2791 p_available_amount := p_available_amount - x_interface_amount;
2792
2793 IF (p_available_amount < 0) THEN
2794
2795 p_available_amount := 0;
2796
2797 END IF;
2798
2799 -- 1337787
2800 p_tolerable_amount := (x_amt_ordered * x_amt_rcv_tolerance)-
2801 x_amt_received - x_amt_cancelled -
2802 x_interface_amount;
2803
2804 IF (p_tolerable_amount < 0) THEN
2805
2806 p_tolerable_amount := 0;
2807
2808 END IF;
2809
2810 EXCEPTION
2811
2812 WHEN OTHERS THEN
2813
2814 po_message_s.sql_error('get_po_dist_amount',
2815 x_progress, sqlcode);
2816
2817 RAISE;
2818
2819 END get_po_dist_amount;
2820
2821 /*===========================================================================
2822
2823 PROCEDURE NAME: get_rcv_dist_quantity()
2824
2825 ===========================================================================*/
2826
2827 PROCEDURE get_rcv_dist_quantity(p_po_distribution_id IN NUMBER,
2828 p_transaction_id IN NUMBER,
2829 p_available_quantity IN OUT NOCOPY NUMBER,
2830 p_unit_of_measure IN OUT NOCOPY VARCHAR2) IS
2831
2832 x_progress VARCHAR2(3) := NULL;
2833 x_deliver_quantity NUMBER := 0;
2834 x_interface_quantity NUMBER := 0; /* in primary uom */
2835 x_primary_uom VARCHAR2(26);
2836 x_item_id NUMBER;
2837 x_interface_qty_in_trx_uom NUMBER;
2838
2839 BEGIN
2840
2841 x_progress := '005';
2842
2843 /*
2844 ** Get available supply quantity information.
2845 */
2846
2847 /*
2848 ** There may be no supply quantity hence the exception no data found
2849 ** needs to be trapped here
2850 */
2851
2852 BEGIN
2853
2854 SELECT quantity,
2855 unit_of_measure
2856 INTO p_available_quantity,
2857 p_unit_of_measure
2858 FROM mtl_supply
2859 WHERE supply_type_code = 'RECEIVING'
2860 AND supply_source_id = p_transaction_id
2861 AND po_distribution_id = p_po_distribution_id;
2862
2863 EXCEPTION
2864
2865 WHEN NO_DATA_FOUND THEN
2866
2867 p_available_quantity := 0;
2868
2869 p_unit_of_measure := '';
2870
2871 WHEN OTHERS THEN RAISE;
2872
2873 END;
2874
2875 select nvl(sum(decode(transaction_type,
2876 'CORRECT', -1 * (decode(nvl(order_transaction_id,-999),-999,primary_quantity,nvl(interface_transaction_qty,0))),
2877 decode(nvl(order_transaction_id,-999),-999,primary_quantity,nvl(interface_transaction_qty,0))
2878 )),0),
2879 min(primary_unit_of_measure)
2880 INTO x_interface_quantity,
2881 x_primary_uom
2882 FROM rcv_transactions_interface
2883 WHERE (transaction_status_code = 'PENDING'
2884 and processing_status_code <> 'ERROR')
2885 AND parent_transaction_id = p_transaction_id
2886 AND po_distribution_id = p_po_distribution_id;
2887
2888 IF (x_interface_quantity = 0) THEN
2889
2890 /*
2891 ** There is no unprocessed quantity. Simply set the
2892 ** x_interface_qty_in_trx_uom to 0. There is no need for uom
2893 ** conversion.
2894 */
2895
2896 x_interface_qty_in_trx_uom := 0;
2897
2898 ELSE
2899
2900 /*
2901 ** There is unprocessed quantity. Convert it to the transaction uom
2902 ** so that the available quantity can be calculated in the trx uom
2903 */
2904
2905 po_uom_s.uom_convert(x_interface_quantity, x_primary_uom, x_item_id,
2906 p_unit_of_measure, x_interface_qty_in_trx_uom);
2907
2908 END IF;
2909
2910 /*
2911 ** Calculate the quantity available to be transacted
2912 */
2913
2914 p_available_quantity := p_available_quantity - x_interface_qty_in_trx_uom;
2915
2916 IF (p_available_quantity < 0) THEN
2917
2918 p_available_quantity := 0;
2919
2920 END IF;
2921
2922 EXCEPTION
2923
2924 WHEN OTHERS THEN
2925
2926 po_message_s.sql_error('get_rcv_dist_quantity',
2927 x_progress, sqlcode);
2928
2929 RAISE;
2930
2931 END get_rcv_dist_quantity;
2932
2933 /*===========================================================================
2934
2935 PROCEDURE NAME: val_quantity()
2936
2937 ===========================================================================*/
2938
2939 PROCEDURE val_quantity IS
2940
2941 x_progress VARCHAR2(3) := NULL;
2942
2943 BEGIN
2944
2945 null;
2946
2947 EXCEPTION
2948
2949 WHEN OTHERS THEN
2950
2951 po_message_s.sql_error('val_quantity', x_progress, sqlcode);
2952
2953 RAISE;
2954
2955 END val_quantity;
2956
2957 /*===========================================================================
2958
2959 PROCEDURE NAME: get_primary_qty_uom
2960
2961 ===========================================================================*/
2962 /*
2963 ** go get the primary quantity an uom for an item based on a transaction
2964 ** quantity and uom
2965 */
2966
2967 PROCEDURE get_primary_qty_uom (
2968 X_transaction_qty IN NUMBER,
2969 X_transaction_uom IN VARCHAR2,
2970 X_item_id IN NUMBER,
2971 X_organization_id IN NUMBER,
2972 X_primary_qty IN OUT NOCOPY NUMBER,
2973 X_primary_uom IN OUT NOCOPY VARCHAR2) IS
2974
2975 X_progress VARCHAR2(4) := '000';
2976
2977 BEGIN
2978
2979 /*dbms_output.put_line ('get_primary_qty : X_transaction_qty : '||
2980 TO_CHAR(X_transaction_qty));
2981 dbms_output.put_line ('get_primary_qty : X_transaction_uom : '||
2982 X_transaction_uom);
2983 dbms_output.put_line ('get_primary_qty : X_item_id : '||
2984 TO_CHAR(X_item_id));
2985 dbms_output.put_line ('get_primary_qty : X_organization_Id : '||
2986 TO_CHAR(X_organization_id));*/
2987 /*
2988 ** Check if item_id = 0, if TRUE get primary unit of measure from
2989 ** MTL_UNITS_OF_MEASURE else get primary unit of measure from
2990 ** MTL_SYSTEM_ITEMS
2991 */
2992
2993 IF (X_item_id IS NULL) THEN
2994
2995 X_progress := '1100';
2996
2997 SELECT MUOM2.unit_of_measure
2998 INTO X_primary_uom
2999 FROM mtl_units_of_measure MUOM1,
3000 mtl_units_of_measure MUOM2
3001 WHERE MUOM1.unit_of_measure = X_transaction_uom
3002 AND MUOM1.uom_class = MUOM2.uom_class
3003 AND MUOM2.base_uom_flag = 'Y';
3004
3005 ELSE
3006
3007 X_progress := '1110';
3008
3009 SELECT MSI.primary_unit_of_measure
3010 INTO X_primary_uom
3011 FROM mtl_system_items_kfv MSI
3012 WHERE MSI.inventory_item_id = X_item_id
3013 AND MSI.organization_id = X_organization_id;
3014
3015 END IF;
3016
3017 /*
3018 ** Go get the primary quantity based on the transaction quantity and the
3019 ** conversions between the UOMS
3020 */
3021 X_progress := '1120';
3022 po_uom_s.uom_convert (X_transaction_qty,
3023 X_transaction_uom,
3024 X_item_id,
3025 X_primary_uom,
3026 X_primary_qty);
3027
3028 /*dbms_output.put_line ('get_primary_qty : X_primary_qty : '||
3029 TO_CHAR(X_primary_qty));
3030 dbms_output.put_line ('get_primary_qty : X_primary_uom : '||
3031 X_primary_uom);*/
3032
3033 RETURN;
3034
3035 EXCEPTION
3036 WHEN OTHERS THEN
3037 po_message_s.sql_error('get_primary_qty_uom', X_progress, sqlcode);
3038 RAISE;
3039
3040 END get_primary_qty_uom;
3041
3042 /*===========================================================================
3043
3044 FUNCTION NAME: get_pending_qty_
3045
3046 ===========================================================================*/
3047 FUNCTION get_pending_qty(p_line_location_id IN NUMBER) RETURN NUMBER IS
3048 x_progress VARCHAR2(3) := NULL;
3049 x_interface_quantity NUMBER := 0;
3050 x_po_uom VARCHAR2(26);
3051 x_item_id NUMBER;
3052 x_primary_uom VARCHAR2(26);
3053 x_interface_qty_in_po_uom NUMBER := 0;
3054
3055 BEGIN
3056 x_progress := '005';
3057 SELECT PL.ITEM_ID,
3058 PL.UNIT_MEAS_LOOKUP_CODE
3059 INTO x_item_id,
3060 x_po_uom
3061 FROM PO_LINE_LOCATIONS PLL,
3062 PO_LINES PL
3063 WHERE PLL.LINE_LOCATION_ID = p_line_location_id AND
3064 PLL.PO_LINE_ID = PL.PO_LINE_ID;
3065
3066 x_progress := '010';
3067 SELECT nvl(sum(decode(nvl(order_transaction_id,-999),-999,primary_quantity,nvl(interface_transaction_qty,0))),0),
3068 MIN(PRIMARY_UNIT_OF_MEASURE)
3069 INTO x_interface_quantity,
3070 x_primary_uom
3071 FROM RCV_TRANSACTIONS_INTERFACE
3072 WHERE (TRANSACTION_STATUS_CODE = 'PENDING'
3073 and processing_status_code <> 'ERROR') AND
3074 TRANSACTION_TYPE IN ('RECEIVE','MATCH','CORRECT') AND
3075 PO_LINE_LOCATION_ID = p_line_location_id;
3076
3077 IF (x_interface_quantity = 0) THEN
3078 /* ** There is no unprocessed quantity. Simply set the
3079 ** x_interface_qty_in_po_uom to 0. There is no need for uom
3080 ** conversion. */
3081 x_interface_qty_in_po_uom := 0;
3082 ELSE
3083 /* ** There is unprocessed quantity. Convert it to the PO uom
3084 ** so that the available quantity can be calculated in the PO uom */
3085 x_progress := '015';
3086 /*
3087 po_uom_s.uom_convert(x_interface_quantity,
3088 x_primary_uom,
3089 x_item_id,
3090 x_po_uom,
3091 x_interface_qty_in_po_uom);
3092 */
3093 /* Had to reverse engineer the call to po_uom_s.uom_convert */
3094 x_interface_qty_in_po_uom := x_interface_quantity *
3095 po_uom_s.po_uom_convert(x_primary_uom,x_po_uom,x_item_id);
3096 END IF;
3097
3098 x_progress := '020';
3099 RETURN x_interface_qty_in_po_uom;
3100
3101 /* Had to remove the exception handling section because it violates WNDS pragma */
3102 /*
3103 EXCEPTION
3104 WHEN OTHERS THEN
3105 po_message_s.sql_error('get_pending_qty',x_progress,SQLCODE);
3106 RAISE;
3107 */
3108
3109 END get_pending_qty;
3110
3111 /*===========================================================================
3112
3113 PROCEDURE NAME: get_ship_qty_in_int
3114
3115 ===========================================================================*/
3116 /*
3117 ** get qty in RTI for a particular PO shipment and ASN shipment
3118 */
3119
3120 PROCEDURE get_ship_qty_in_int (
3121 p_shipment_line_id IN NUMBER,
3122 p_line_location_id IN NUMBER,
3123 p_ship_qty_in_int IN OUT NOCOPY NUMBER) IS
3124
3125 X_progress VARCHAR2(4) := '000';
3126 x_interface_quantity NUMBER := 0; /* in primary_uom */
3127 x_shipment_uom VARCHAR2(26);
3128 x_item_id NUMBER;
3129 x_primary_uom VARCHAR2(26);
3130 x_interface_qty_in_ship_uom NUMBER := 0;
3131
3132 BEGIN
3133
3134 SELECT rsl.item_id,
3135 rsl.unit_of_measure
3136 INTO x_item_id,
3137 x_shipment_uom
3138 FROM rcv_shipment_lines rsl
3139 WHERE rsl.shipment_line_id = p_shipment_line_id;
3140
3141 x_progress := '010';
3142
3143 SELECT nvl(sum(decode(nvl(order_transaction_id,-999),-999,primary_quantity,nvl(interface_transaction_qty,0))),0),
3144 min(primary_unit_of_measure)
3145 INTO x_interface_quantity,
3146 x_primary_uom
3147 FROM rcv_transactions_interface
3148 WHERE (transaction_status_code = 'PENDING'
3149 and processing_status_code <> 'ERROR')
3150 AND transaction_type = 'RECEIVE'
3151 AND shipment_line_id = p_shipment_line_id
3152 AND po_line_location_id = p_line_location_id;
3153
3154 x_progress := '020';
3155
3156 IF (x_interface_quantity = 0) THEN
3157
3158 /*
3159 ** There is no unprocessed quantity. Simply set the
3160 ** x_interface_qty_in_ship_uom to 0. There is no need for uom
3161 ** conversion.
3162 */
3163
3164 x_interface_qty_in_ship_uom := 0;
3165
3166 ELSE
3167
3168 /*
3169 ** There is unprocessed quantity. Convert it to the shipment uom
3170 ** so that the available quantity can be calculated in the shipment uom
3171 */
3172 x_progress := '015';
3173
3174 po_uom_s.uom_convert(x_interface_quantity, x_primary_uom, x_item_id,
3175 x_shipment_uom, x_interface_qty_in_ship_uom);
3176
3177 END IF;
3178
3179 x_progress := '030';
3180
3181 p_ship_qty_in_int := x_interface_qty_in_ship_uom;
3182
3183 EXCEPTION
3184 WHEN OTHERS THEN
3185 po_message_s.sql_error('get_ship_qty_in_int', X_progress, sqlcode);
3186 RAISE;
3187
3188 END get_ship_qty_in_int;
3189
3190 PROCEDURE get_available_asn_quantity(
3191 p_transaction_type IN VARCHAR2,
3192 p_shipment_line_id IN NUMBER,
3193 p_line_location_id IN NUMBER,
3194 p_distribution_id IN VARCHAR2,
3195 x_unit_of_measure IN OUT NOCOPY VARCHAR2,
3196 x_available_quantity IN OUT NOCOPY NUMBER,
3197 x_tolerable_quantity IN OUT NOCOPY NUMBER,
3198 x_secondary_available_qty IN OUT NOCOPY NUMBER
3199 ) IS
3200 l_available_qty_hold number;
3201 l_uom_hold VARCHAR2(26);
3202 l_secondary_available_qty_hold number;
3203 l_quantity_shipped NUMBER :=0; -- ASN Phase 2
3204 l_quantity_returned NUMBER :=0; -- ASN Phase 2
3205 l_ship_qty_int NUMBER :=0; -- ASN Phase 2 bug 623925
3206 l_interface_qty_in_trx_uom NUMBER;
3207 l_interface_quantity NUMBER := 0; /* in primary uom */
3208 l_primary_uom VARCHAR2(26);
3209 l_item_id NUMBER;
3210 l_qty_rcv_tolerance number :=0;
3211 l_qty_ordered number :=0;
3212 l_qty_received number :=0;
3213 l_qty_cancelled number :=0;
3214 l_progress VARCHAR2(3) := NULL;
3215 begin
3216
3217 l_progress := '000';
3218 IF (g_asn_debug = 'Y') THEN
3219 asn_debug.put_line('Enter get_available_asn_quantity ');
3220 asn_debug.put_line('p_transaction_type '||p_transaction_type);
3221 asn_debug.put_line('p_shipment_line_id '||p_shipment_line_id);
3222 asn_debug.put_line('p_line_location_id '||p_line_location_id );
3223 asn_debug.put_line('p_distribution_id '||p_distribution_id );
3224 asn_debug.put_line('x_unit_of_measure '||x_unit_of_measure );
3225 END IF;
3226
3227 IF (p_transaction_type = 'RECEIVE') then --{
3228
3229 /* This part of the code is the same as in RCVRCPQB.pls
3230 * where we calculate the available qty in forms.
3231 */
3232 rcv_quantities_s.get_available_quantity ('RECEIVE',
3233 p_shipment_line_id,
3234 'INVENTORY',
3235 NULL,
3236 NULL,
3237 NULL,
3238 x_available_quantity,
3239 x_tolerable_quantity,
3240 x_unit_of_measure,
3241 X_secondary_available_qty);
3242
3243 IF (g_asn_debug = 'Y') THEN
3244 asn_debug.put_line('x_available_quantity '||x_available_quantity);
3245 asn_debug.put_line('x_tolerable_quantity '||x_tolerable_quantity);
3246 asn_debug.put_line('x_unit_of_measure '||x_unit_of_measure);
3247 asn_debug.put_line('X_secondary_available_qty '||X_secondary_available_qty);
3248 END IF;
3249
3250 l_progress := '010';
3251
3252 rcv_quantities_s.get_available_quantity ('RECEIVE',
3253 p_line_location_id,
3254 'VENDOR',
3255 NULL,
3256 NULL,
3257 NULL,
3258 l_available_qty_hold,
3259 x_tolerable_quantity,
3260 l_uom_hold,
3261 x_secondary_available_qty);
3262
3263
3264 IF (g_asn_debug = 'Y') THEN
3265 asn_debug.put_line('l_available_qty_hold '||l_available_qty_hold);
3266 asn_debug.put_line('x_tolerable_quantity '||x_tolerable_quantity);
3267 asn_debug.put_line('l_uom_hold '||l_uom_hold);
3268 asn_debug.put_line('X_secondary_available_qty '||X_secondary_available_qty);
3269 END IF;
3270
3271 l_progress := '020';
3272
3273 select nvl(quantity_shipped,0)
3274 into l_quantity_shipped
3275 from po_line_locations
3276 where
3277 line_location_id = p_line_location_id;
3278
3279
3280 IF (g_asn_debug = 'Y') THEN
3281 asn_debug.put_line('l_quantity_shipped '||l_quantity_shipped);
3282 END IF;
3283
3284 l_progress := '030';
3285
3286 IF l_quantity_shipped > 0 then
3287 rcv_quantities_s.get_ship_qty_in_int
3288 (p_shipment_line_id,
3289 p_line_location_id,
3290 l_ship_qty_int);
3291
3292 IF (g_asn_debug = 'Y') THEN
3293 asn_debug.put_line('l_ship_qty_int '||l_ship_qty_int);
3294 END IF;
3295
3296
3297 x_tolerable_quantity := x_tolerable_quantity - l_quantity_shipped +
3298 x_available_quantity + l_ship_qty_int ;
3299
3300
3301 IF (g_asn_debug = 'Y') THEN
3302 asn_debug.put_line('x_tolerable_quantity '||x_tolerable_quantity );
3303 END IF;
3304
3305 IF x_tolerable_quantity < 0 THEN
3306 x_tolerable_quantity := 0;
3307 end if;
3308
3309 END IF;
3310
3311
3312 -- Handle the return to vendor here
3313 l_progress := '040';
3314
3315 select sum(nvl(quantity,0))
3316 into l_quantity_returned
3317 from rcv_transactions
3318 where shipment_line_id = p_shipment_line_id and
3319 transaction_type = 'RETURN TO VENDOR';
3320
3321
3322 IF (g_asn_debug = 'Y') THEN
3323 asn_debug.put_line('l_quantity_returned '||l_quantity_returned );
3324 END IF;
3325
3326 IF l_quantity_returned > 0 then --}
3327
3328 x_available_quantity := X_available_quantity - l_quantity_returned;
3329 x_tolerable_quantity := x_tolerable_quantity - l_quantity_returned;
3330
3331 If x_available_quantity < 0 THEN
3332 x_available_quantity := 0;
3333 end if;
3334
3335 IF x_tolerable_quantity < 0 THEN
3336 x_tolerable_quantity := 0;
3337 end if;
3338
3339
3340 IF (g_asn_debug = 'Y') THEN
3341 asn_debug.put_line('x_available_quantity '||x_available_quantity );
3342 asn_debug.put_line('x_tolerable_quantity '||x_tolerable_quantity );
3343 END IF;
3344
3345 END IF; --}
3346
3347 elsif(p_transaction_type = 'DIRECT RECEIPT') then
3348
3349
3350 IF (g_asn_debug = 'Y') THEN
3351 asn_debug.put_line('In direct receipt of ASN ');
3352 END IF;
3353
3354 BEGIN
3355
3356 SELECT quantity,
3357 unit_of_measure
3358 INTO x_available_quantity,
3359 x_unit_of_measure
3360 FROM mtl_supply
3361 WHERE supply_type_code = 'SHIPMENT'
3362 AND supply_source_id = p_shipment_line_id
3363 AND po_distribution_id = p_distribution_id;
3364
3365 EXCEPTION
3366
3367 WHEN NO_DATA_FOUND THEN
3368
3369 x_available_quantity := 0;
3370
3371 x_unit_of_measure := '';
3372
3373 WHEN OTHERS THEN RAISE;
3374
3375 END;
3376
3377 IF (g_asn_debug = 'Y') THEN
3378 asn_debug.put_line('x_available_quantity '||x_available_quantity);
3379 END IF;
3380
3381
3382 l_progress := '050';
3383
3384 select nvl(sum( decode(nvl(order_transaction_id,-999),-999,primary_quantity,nvl(interface_transaction_qty,0))),0),
3385 min(primary_unit_of_measure)
3386 INTO l_interface_quantity,
3387 l_primary_uom
3388 FROM rcv_transactions_interface
3389 WHERE (transaction_status_code = 'PENDING'
3390 and processing_status_code <> 'ERROR')
3391 AND transaction_type = 'RECEIVE'
3392 AND shipment_line_id = p_shipment_line_id
3393 AND po_line_location_id = p_line_location_id
3394 AND po_distribution_id = p_distribution_id;
3395
3396
3397 IF (g_asn_debug = 'Y') THEN
3398 asn_debug.put_line('l_interface_quantity '||l_interface_quantity);
3399 asn_debug.put_line('l_primary_uom '||l_primary_uom);
3400 END IF;
3401
3402 IF (l_interface_quantity = 0) THEN
3403
3404 l_interface_qty_in_trx_uom := 0;
3405
3406 ELSE
3407
3408 /*
3409 * There is unprocessed quantity. Convert it to the
3410 * transaction uom so that the available quantity can be
3411 * calculated in the trx uom
3412 */
3413
3414 po_uom_s.uom_convert( l_interface_quantity,
3415 l_primary_uom,
3416 l_item_id,
3417 x_unit_of_measure,
3418 l_interface_qty_in_trx_uom);
3419
3420 END IF;
3421
3422
3423 IF (g_asn_debug = 'Y') THEN
3424 asn_debug.put_line('l_interface_qty_in_trx_uom '||l_interface_qty_in_trx_uom);
3425 END IF;
3426
3427 x_available_quantity := x_available_quantity -
3428 l_interface_qty_in_trx_uom;
3429
3430 IF (x_available_quantity < 0) THEN
3431
3432 x_available_quantity := 0;
3433
3434 END IF;
3435
3436 IF (g_asn_debug = 'Y') THEN
3437 asn_debug.put_line('x_available_quantity '||x_available_quantity);
3438 END IF;
3439
3440
3441
3442 select
3443 1 + (nvl(poll.qty_rcv_tolerance,0)/100), -- 1337787
3444 nvl(poll.quantity,0),
3445 nvl(poll.quantity_received,0),
3446 nvl(poll.quantity_cancelled,0)
3447 INTO
3448 l_qty_rcv_tolerance,
3449 l_qty_ordered,
3450 l_qty_received,
3451 l_qty_cancelled
3452 from po_distributions_all pod, --<Shared Proc FPJ>
3453 po_line_locations_all poll, --<Shared Proc FPJ>
3454 po_lines_all pol --<Shared Proc FPJ>
3455 where pod.line_location_id = poll.line_location_id
3456 and pod.po_distribution_id = p_distribution_id
3457 and pod.po_line_id = pol.po_line_id;
3458
3459
3460 IF (g_asn_debug = 'Y') THEN
3461 asn_debug.put_line('l_qty_rcv_tolerance '||l_qty_rcv_tolerance);
3462 asn_debug.put_line('l_qty_ordered '||l_qty_ordered);
3463 asn_debug.put_line('l_qty_received '||l_qty_received);
3464 asn_debug.put_line('l_qty_cancelled '||l_qty_cancelled);
3465 END IF;
3466
3467 x_tolerable_quantity := (l_qty_ordered * l_qty_rcv_tolerance)-
3468 l_qty_received - l_qty_cancelled -
3469 l_interface_qty_in_trx_uom;
3470
3471 IF (x_tolerable_quantity < 0) THEN
3472
3473 x_tolerable_quantity := 0;
3474
3475 END IF;
3476
3477
3478 IF (g_asn_debug = 'Y') THEN
3479 asn_debug.put_line('x_tolerable_quantity '||x_tolerable_quantity);
3480 END IF;
3481
3482
3483
3484 end if; --}
3485
3486 IF (g_asn_debug = 'Y') THEN
3487 asn_debug.put_line('Leave get_available_asn_qty ');
3488 END IF;
3489
3490 EXCEPTION
3491 WHEN OTHERS THEN
3492 po_message_s.sql_error('get_available_asn_quantity', l_progress, sqlcode);
3493 RAISE;
3494
3495 END get_available_asn_quantity;
3496
3497
3498 END RCV_QUANTITIES_S;
[Home] [Help]