1 PACKAGE BODY POA_SUPPERF_API_PKG AS
2 /* $Header: POASPAPB.pls 115.7 2003/12/09 09:04:34 bthammin ship $ */
3
4 -- get_receipt_date
5 -- ----------------
6 -- This function returns the receipt date from rcv_transactions. There can
7 -- be multiple receipts for a single shipment, so we are only accounting for
8 -- the earliest receipt date
9 --
10 FUNCTION get_receipt_date(p_line_location_id NUMBER)
11 RETURN DATE
12 IS
13 v_receipt_date DATE := NULL;
14 x_progress VARCHAR2(3);
15 BEGIN
16
17 x_progress := '001';
18
19 SELECT MIN(transaction_date)
20 INTO v_receipt_date
21 FROM rcv_transactions
22 WHERE po_line_location_id = p_line_location_id
23 AND transaction_type = 'RECEIVE';
24
25 RETURN(v_receipt_date);
26
27 EXCEPTION
28 WHEN NO_DATA_FOUND THEN
29 RETURN(v_receipt_date);
30 WHEN OTHERS THEN
31 POA_LOG.put_line('get_receipt_date: ' || x_progress
32 || ' ' || sqlerrm);
33 POA_LOG.put_line(' ');
34 RAISE;
35 RETURN(v_receipt_date);
36
37 END get_receipt_date;
38
39
40
41
42
43 -- get_avg_price - this may need to be converted to primary uom
44 -- -------------
45 -- This function returns the average purchase price of an item on a single
46 -- shipment across multiple distribution lines. The price is also
47 -- converted to functional currency. Since there is only one price at
48 -- the shipment level, we can first find the average rate across the
49 -- distributions and then multiplying that with the price that is
50 -- passed in as a parameter.
51 --
52 FUNCTION get_avg_price(p_line_location_id NUMBER,
53 p_price_override NUMBER)
54 RETURN NUMBER
55 IS
56 v_ave_rate NUMBER;
57 v_ave_price NUMBER := 0;
58 x_progress VARCHAR2(3);
59 BEGIN
60
61 x_progress := '001';
62
63 SELECT sum(quantity_ordered * nvl(rate, 1)) /
64 DECODE(sum(quantity_ordered), 0, 1, sum(quantity_ordered))
65 INTO v_ave_rate
66 FROM po_distributions_all
67 WHERE line_location_id = p_line_location_id
68 AND nvl(distribution_type,'-99') <> 'AGREEMENT';
69
70 v_ave_price := v_ave_rate * p_price_override;
71
72 RETURN(v_ave_price);
73
74 EXCEPTION
75 WHEN OTHERS THEN
76 POA_LOG.put_line('get_avg_price: ' || x_progress
77 || ' ' || sqlerrm);
78 POA_LOG.put_line(' ');
79 RAISE;
80 RETURN(v_ave_price);
81
82 END get_avg_price;
83
84
85
86
87
88 -- get_primary_avg_price
89 -- ---------------------
90 -- This function returns the average price of an item on a single
91 -- shipment across multiple distribution lines. Basically, it calls
92 -- get_avg_price() and then converts the result into the primary
93 -- unit of measure of the item.
94 --
95 FUNCTION get_primary_avg_price(p_line_location_id NUMBER,
96 p_price_override NUMBER,
97 p_item_id NUMBER,
98 p_organization_id NUMBER,
99 p_uom VARCHAR2)
100 RETURN NUMBER
101 IS
102 v_avg_price NUMBER;
103 v_uom_rate NUMBER;
104 v_primary_uom NUMBER;
105 v_primary_avg_price NUMBER := 0;
106 x_progress VARCHAR2(3);
107 BEGIN
108
109 v_avg_price := get_avg_price(p_line_location_id, p_price_override);
110 v_primary_uom := get_primary_uom(p_item_id, p_organization_id);
111
112 v_uom_rate := inv_convert.inv_um_convert(p_item_id,
113 5,
114 NULL, NULL, NULL,
115 p_uom,
116 v_primary_uom);
117
118 IF (v_uom_rate IS NOT NULL) AND (v_uom_rate <> 0) THEN
119 v_primary_avg_price := v_avg_price / v_uom_rate;
120 ELSE
121 v_primary_avg_price := v_avg_price;
122 END IF;
123
124 RETURN(v_primary_avg_price);
125
126 EXCEPTION
127 WHEN OTHERS THEN
128 POA_LOG.put_line('get_primary_avg_price: ' || x_progress
129 || ' ' || sqlerrm);
130 POA_LOG.put_line(' ');
131 RAISE;
132 RETURN(v_primary_avg_price);
133
134 END get_primary_avg_price;
135
136
137
138
139
140 -- get_num_receipts
141 -- ----------------
142 -- This function counts the number of receipts for a specific shipment
143 -- and returns that number. If there has not been a receive transaction
144 -- for this shipment, we return 0.
145 --
146 FUNCTION get_num_receipts(p_line_location_id NUMBER)
147 RETURN NUMBER
148 IS
149 v_num_receipts NUMBER := 0;
150 x_progress VARCHAR2(3);
151 BEGIN
152
153 x_progress := '001';
154
155 SELECT count(*)
156 INTO v_num_receipts
157 FROM rcv_transactions
158 WHERE po_line_location_id = p_line_location_id
159 AND transaction_type = 'RECEIVE';
160
161 RETURN(v_num_receipts);
162
163 EXCEPTION
164 WHEN NO_DATA_FOUND THEN
165 RETURN(v_num_receipts);
166 WHEN OTHERS THEN
167 POA_LOG.put_line('get_num_receipts: ' || x_progress
168 || ' ' || sqlerrm);
169 POA_LOG.put_line(' ');
170 RAISE;
171 RETURN(v_num_receipts);
172
173 END get_num_receipts;
174
175
176
177
178 -- get_quantity_late
179 -- -----------------
180 -- This function returns the quantity of a shipment that is received late.
181 -- The quantity returned is in the primary unit of measure. If expected
182 -- date is null, then we return zero and assume the shipment is on time.
183 --
184 FUNCTION get_quantity_late(p_line_location_id NUMBER,
185 p_expected_date DATE,
186 p_days_late_allowed NUMBER)
187 RETURN NUMBER
188 IS
189 v_quantity_late NUMBER := 0;
190 v_txn_qty NUMBER := 0;
191 v_correction_qty NUMBER := 0;
192 x_progress VARCHAR2(3);
193 BEGIN
194
195 IF p_expected_date IS NOT NULL THEN
196 x_progress := '001';
197
198 SELECT SUM(primary_quantity)
199 INTO v_txn_qty
200 FROM rcv_transactions
201 WHERE po_line_location_id = p_line_location_id
202 AND transaction_type = 'RECEIVE'
203 AND transaction_date - NVL(p_days_late_allowed, 0)
204 > p_expected_date;
205
206 x_progress := '002';
207
208 SELECT SUM(rcor.primary_quantity)
209 INTO v_correction_qty
210 FROM rcv_transactions rcor,
211 rcv_transactions rct
212 WHERE rcor.po_line_location_id = p_line_location_id
213 AND rcor.transaction_type = 'CORRECT'
214 AND rct.transaction_id = rcor.parent_transaction_id
215 AND rct.transaction_type = 'RECEIVE'
216 AND rct.transaction_date - NVL(p_days_late_allowed, 0)
217 > p_expected_date;
218 END IF;
219
220 v_quantity_late := NVL(v_txn_qty, 0) + NVL(v_correction_qty, 0);
221
222 RETURN(v_quantity_late);
223
224 EXCEPTION
225 WHEN OTHERS THEN
226 POA_LOG.put_line('get_quantity_late: ' || x_progress
227 || ' ' || sqlerrm);
228 POA_LOG.put_line(' ');
229 RAISE;
230 RETURN(v_quantity_late);
231
232 END get_quantity_late;
233
234
235
236
237
238 -- get_quantity_early
239 -- ------------------
240 -- This function returns the quantity of a shipment that is received early.
241 -- The quantity returned is in the primary unit of measure. If expected
242 -- date is null, then we return zero and assume the shipment is on time.
243 --
244 FUNCTION get_quantity_early(p_line_location_id NUMBER,
245 p_expected_date DATE,
246 p_days_early_allowed NUMBER)
247 RETURN NUMBER
248 IS
249 v_quantity_early NUMBER := 0;
250 v_txn_qty NUMBER := 0;
251 v_correction_qty NUMBER := 0;
252 x_progress VARCHAR2(3);
253 BEGIN
254
255 IF p_expected_date IS NOT NULL THEN
256 x_progress := '001';
257
258 SELECT SUM(primary_quantity)
259 INTO v_txn_qty
260 FROM rcv_transactions
261 WHERE po_line_location_id = p_line_location_id
262 AND transaction_type = 'RECEIVE'
263 AND transaction_date + NVL(p_days_early_allowed, 0)
264 < p_expected_date;
265
266 x_progress := '002';
267
268 SELECT SUM(rcor.primary_quantity)
269 INTO v_correction_qty
270 FROM rcv_transactions rcor,
271 rcv_transactions rct
272 WHERE rcor.po_line_location_id = p_line_location_id
273 AND rcor.transaction_type = 'CORRECT'
274 AND rct.transaction_id = rcor.parent_transaction_id
275 AND rct.transaction_type = 'RECEIVE'
276 AND rct.transaction_date + NVL(p_days_early_allowed, 0)
277 < p_expected_date;
278 END IF;
279
280 v_quantity_early := NVL(v_txn_qty, 0) + NVL(v_correction_qty, 0);
281
282 RETURN(v_quantity_early);
283
284 EXCEPTION
285 WHEN OTHERS THEN
286 POA_LOG.put_line('get_quantity_early: ' || x_progress
287 || ' ' || sqlerrm);
288 POA_LOG.put_line(' ');
289 RAISE;
290 RETURN(v_quantity_early);
291
292 END get_quantity_early;
293
294
295
296
297
298 -- get_quantity_past_due
299 -- ---------------------
300 -- This function returns the past-due quantity.
301 -- A shipment has past-due quantity if today is past the expected date
302 -- plus the late days allowed and there are still quantity not received.
303 -- If there is no expected date the shipment will never be past due.
304 --
305 FUNCTION get_quantity_past_due(p_quantity_ordered NUMBER,
306 p_quantity_received NUMBER,
307 p_expected_date DATE,
308 p_days_late_allowed NUMBER)
309 RETURN NUMBER
310 IS
311 v_quantity_past_due NUMBER;
312 BEGIN
313
314 IF ((p_expected_date IS NOT NULL) AND
315 (sysdate > (p_expected_date + p_days_late_allowed)) AND
316 (p_quantity_received < p_quantity_ordered)) THEN
317
318 v_quantity_past_due := p_quantity_ordered - p_quantity_received;
319 ELSE
320
321 v_quantity_past_due := 0;
322
323 END IF;
324
325 RETURN(v_quantity_past_due);
326
327 END get_quantity_past_due;
328
329
330
331
332
333 -- get_primary_uom
334 -- ---------------
335 -- This function returns the primary unit of measure of an item.
336 --
337 FUNCTION get_primary_uom(p_item_id NUMBER, p_organization_id NUMBER)
338 RETURN VARCHAR2
339 IS
340 v_primary_uom VARCHAR2(25) := NULL;
341 x_progress VARCHAR2(3);
342 BEGIN
343
344 x_progress := '001';
345
346 SELECT primary_unit_of_measure
347 INTO v_primary_uom
348 FROM mtl_system_items items,
349 financials_system_params_all fsp
350 WHERE NVL(fsp.org_id, -999) = NVL(p_organization_id, -999)
351 AND items.organization_id = fsp.inventory_organization_id
352 AND inventory_item_id = p_item_id;
353
354 RETURN(v_primary_uom);
355
356 EXCEPTION
357 WHEN NO_DATA_FOUND THEN
358 POA_LOG.put_line('No primary UOM found for item ' || p_item_id || ' and org ' || p_organization_id);
359 POA_LOG.put_line(' ');
360 RAISE;
361 RETURN(v_primary_uom);
362 WHEN OTHERS THEN
363 POA_LOG.put_line('get_primary_uom: ' || x_progress || ' ' || sqlerrm);
364 POA_LOG.put_line(' ');
365 RAISE;
366 RETURN(v_primary_uom);
367 END get_primary_uom;
368
369
370
371
372
373 -- get_primary_quantity
374 -- --------------------
375 -- This is a function to convert a given quantity to a primary quantity
376 -- that is in the item's primary unit of measure.
377 --
378 FUNCTION get_primary_quantity(p_quantity NUMBER,
379 p_item_id NUMBER,
380 p_organization_id NUMBER,
381 p_uom VARCHAR2)
382 RETURN NUMBER
383 IS
384 v_primary_uom VARCHAR2(25);
385 v_primary_quantity NUMBER := 0;
386 BEGIN
390 v_primary_quantity := inv_convert.inv_um_convert(p_item_id,
387
388 v_primary_uom := get_primary_uom(p_item_id, p_organization_id);
389
391 5,
392 p_quantity,
393 NULL,
394 NULL,
395 p_uom,
396 v_primary_uom
397 );
398 RETURN(v_primary_quantity);
399
400 END get_primary_quantity;
401
402
403
404
405
406 -- get_quantity_purchased
407 -- ----------------------
408 -- This function returns the quantity that is purchased for the
409 -- shipment line. If the shipment is finally closed, the maximum
410 -- betweent the quantity received and the quantity billed is returned.
411 -- Otherwise, If the shipment is not finally closed, we check to see if
412 -- it has been cancelled. If it has been cancelled, the difference
413 -- between the quantity ordered and quantity cancelled is returned,
414 -- otherwise, we return the quantity ordered as the quantity purchased.
415 --
416 FUNCTION get_quantity_purchased(p_quantity_ordered NUMBER,
417 p_quantity_billed NUMBER,
418 p_quantity_cancelled NUMBER,
419 p_quantity_received NUMBER,
420 p_cancel_flag VARCHAR2,
421 p_closed_code VARCHAR2)
422
423 RETURN NUMBER
424 IS
425 v_quantity_purchased NUMBER := 0;
426 BEGIN
427
428 IF p_closed_code = 'FINALLY CLOSED' THEN
429 v_quantity_purchased := greatest(p_quantity_received,
430 p_quantity_billed);
431 ELSE
432 IF p_cancel_flag = 'Y' THEN
433 v_quantity_purchased := p_quantity_ordered - p_quantity_cancelled;
434 ELSE
435 v_quantity_purchased := p_quantity_ordered;
436 END IF;
437 END IF;
438
439 RETURN(v_quantity_purchased);
440
441 END get_quantity_purchased;
442
443
444
445
446
447 -- get_total_amount
448 -- ----------------
449 -- This function returns the total purchase amount for a single shipment.
450 -- To perform this calculation, we first select all the distributions for
451 -- the given shipment. For each distribution, we multiply the quantity
452 -- purchased, the price override, and the currency conversion rate. We
453 -- then sum up this amount to find the total amount for the shipment.
454 -- Question: What happens if price override is null?
455 --
456 FUNCTION get_total_amount(p_line_location_id NUMBER,
457 p_cancel_flag VARCHAR2,
458 p_closed_code VARCHAR2,
459 p_price NUMBER)
460 RETURN NUMBER
461 IS
462 CURSOR C_DIST IS
463 SELECT nvl(quantity_delivered, 0) quantity_delivered,
464 nvl(quantity_billed, 0) quantity_billed,
465 nvl(quantity_cancelled, 0) quantity_cancelled,
466 quantity_ordered,
467 nvl(rate, 1) rate
468 FROM po_distributions_all
469 WHERE line_location_id = p_line_location_id
470 AND nvl(distribution_type,'-99') <> 'AGREEMENT';
471
472 v_c_info C_DIST%ROWTYPE;
473 v_quantity_purchased NUMBER;
474 v_total_amount NUMBER := 0;
475 x_progress VARCHAR2(3);
476 BEGIN
477 x_progress := '001';
478
479 OPEN C_DIST;
480 LOOP
481
482 FETCH C_DIST INTO v_c_info;
483 EXIT WHEN C_DIST%NOTFOUND;
484
485 -- gotta make sure all quantities are in the same uom...
486 -- all these are in distributions level, so i think i'm ok...
487 v_quantity_purchased := get_quantity_purchased(
488 v_c_info.quantity_ordered,
489 v_c_info.quantity_billed,
490 v_c_info.quantity_cancelled,
491 v_c_info.quantity_delivered,
492 p_cancel_flag,
493 p_closed_code);
494
495 v_total_amount := v_total_amount +
496 (v_quantity_purchased * p_price * v_c_info.rate);
497
498 END LOOP;
499 CLOSE C_DIST;
500
501 RETURN(v_total_amount);
502
503 EXCEPTION
504 WHEN OTHERS THEN
505 POA_LOG.put_line('get_total_amount: ' || x_progress
506 || ' ' || sqlerrm);
507 POA_LOG.put_line(' ');
508 RAISE;
509 RETURN(v_total_amount);
510
511 END get_total_amount;
512
513
514
515
516
517 -- get_suppliers
518 -- -------------
522 --
519 -- This function gets the supplier ids of the top suppliers limited by
520 -- a user specified sort criteria and a user specified number of suppliers
521 -- parameter. This is only called from the report client.
523 FUNCTION get_suppliers(p_order_by NUMBER,
524 p_item NUMBER,
525 p_fdate DATE,
526 p_tdate DATE,
527 p_number_of_suppliers NUMBER)
528 RETURN VARCHAR2
529 IS
530 type T_FLEXREF is REF CURSOR;
531 v_cursor_blk T_FLEXREF;
532 v_supplier_id NUMBER;
533 v_totals NUMBER;
534 v_num_suppliers NUMBER := 0;
535 v_suppliers VARCHAR2(2000);
536 x_progress VARCHAR2(3);
537 BEGIN
538 IF p_order_by = 1 THEN
539 x_progress := '001';
540
541 OPEN v_cursor_blk FOR
542 SELECT supplier_id,
543 sum(quantity_rejected) /
544 decode(sum(nvl(quantity_received, 0)), 0, 1,
545 sum(nvl(quantity_received, 0))) defects
546 FROM poa_bis_supplier_performance_v
547 WHERE item_id = p_item
548 AND date_dimension between p_fdate and p_tdate
549 GROUP BY supplier_id
550 ORDER BY defects desc;
551
552 ELSIF p_order_by = 2 THEN
553 x_progress := '002';
554
555 OPEN v_cursor_blk FOR
556 SELECT supplier_id,
557 sum(nvl(quantity_received_late, 0) +
558 nvl(quantity_received_early, 0) +
559 nvl(quantity_past_due, 0)) /
560 nvl(sum(quantity_ordered), 1) exceptions
561 FROM poa_bis_supplier_performance_v
562 WHERE item_id = p_item
563 AND date_dimension between p_fdate and p_tdate
564 GROUP BY supplier_id
565 ORDER BY exceptions desc;
566
567 ELSIF p_order_by = 3 THEN
568 x_progress := '003';
569
570 OPEN v_cursor_blk FOR
571 SELECT supplier_id,
572 sum(quantity_purchased) volume
573 FROM poa_bis_supplier_performance_v
574 WHERE item_id = p_item
575 AND date_dimension between p_fdate and p_tdate
576 GROUP BY supplier_id
577 ORDER BY volume desc;
578
579 ELSIF p_order_by = 4 THEN
580 x_progress := '004';
581
582 OPEN v_cursor_blk FOR
583 SELECT supplier_id,
584 sum(quantity_ordered * purchase_price) /
585 nvl(sum(quantity_ordered), 1) price
586 FROM poa_bis_supplier_performance_v
587 WHERE item_id = p_item
588 AND date_dimension between p_fdate and p_tdate
589 GROUP BY supplier_id
590 ORDER BY price desc;
591
592 ELSIF p_order_by = 5 THEN
593 x_progress := '005';
594
595 OPEN v_cursor_blk FOR
596 SELECT supplier_id,
597 sum(amount) amount
598 FROM poa_bis_supplier_performance_v
599 WHERE item_id = p_item
600 AND date_dimension between p_fdate and p_tdate
601 GROUP BY supplier_id
602 ORDER BY amount desc;
603
604 ELSIF p_order_by = 6 THEN
605 x_progress := '006';
606
607 OPEN v_cursor_blk FOR
608 SELECT supplier_id,
609 sum(quantity_rejected) /
610 decode(sum(nvl(quantity_received, 0)), 0, 1,
611 sum(nvl(quantity_received, 0))) defects
612 FROM poa_bis_supplier_performance_v
613 WHERE item_id = p_item
614 AND date_dimension between p_fdate and p_tdate
615 GROUP BY supplier_id
616 ORDER BY defects asc;
617
618 ELSIF p_order_by = 7 THEN
619 x_progress := '007';
620
621 OPEN v_cursor_blk FOR
622 SELECT supplier_id,
623 sum(nvl(quantity_received_late, 0) +
624 nvl(quantity_received_early, 0) +
625 nvl(quantity_past_due, 0)) /
626 nvl(sum(quantity_ordered), 1) exceptions
627 FROM poa_bis_supplier_performance_v
628 WHERE item_id = p_item
629 AND date_dimension between p_fdate and p_tdate
630 GROUP BY supplier_id
631 ORDER BY exceptions asc;
632
633 ELSIF p_order_by = 8 THEN
634 x_progress := '008';
635
636 OPEN v_cursor_blk FOR
637 SELECT supplier_id,
638 sum(quantity_purchased) volume
639 FROM poa_bis_supplier_performance_v
640 WHERE item_id = p_item
641 AND date_dimension between p_fdate and p_tdate
642 GROUP BY supplier_id
643 ORDER BY volume asc;
644
645 ELSIF p_order_by = 9 THEN
646 x_progress := '009';
647
648 OPEN v_cursor_blk FOR
649 SELECT supplier_id,
650 sum(quantity_ordered * purchase_price) /
651 nvl(sum(quantity_ordered), 1) price
655 GROUP BY supplier_id
652 FROM poa_bis_supplier_performance_v
653 WHERE item_id = p_item
654 AND date_dimension between p_fdate and p_tdate
656 ORDER BY price asc;
657
658 ELSIF p_order_by = 10 THEN
659 x_progress := '010';
660
661 OPEN v_cursor_blk FOR
662 SELECT supplier_id,
663 sum(amount) amount
664 FROM poa_bis_supplier_performance_v
665 WHERE item_id = p_item
666 AND date_dimension between p_fdate and p_tdate
667 GROUP BY supplier_id
668 ORDER BY amount asc;
669 END IF;
670
671 LOOP
672
673 FETCH v_cursor_blk INTO v_supplier_id, v_totals;
674 EXIT WHEN v_cursor_blk%NOTFOUND
675 OR v_num_suppliers >= p_number_of_suppliers;
676
677 v_suppliers := v_suppliers || ', ' || v_supplier_id;
678 v_num_suppliers := v_num_suppliers + 1;
679
680 END LOOP;
681
682 v_suppliers := '( -1' || v_suppliers || ')';
683
684 RETURN(v_suppliers);
685
686 CLOSE v_cursor_blk;
687
688 EXCEPTION
689 WHEN OTHERS THEN
690 POA_LOG.put_line('get_suppliers: ' || x_progress
691 || ' ' || sqlerrm);
692 POA_LOG.put_line(' ');
693 RAISE;
694 RETURN(v_suppliers);
695
696 END get_suppliers;
697
698
699
700
701 -- get_last_trx_date
702 -- -----------------
703 -- This procedure returns the last update date for a particular
704 -- shipment line.
705 -- The dates considered are for:
706 -- - rcv_transactions - last_update_date
707 -- - po_line_locations_all - last_update_date
708 -- If any of these records changed after the last collection,
709 -- the shipment data need to be re-collected.
710 --
711 FUNCTION get_last_trx_date(p_line_location_id NUMBER)
712 RETURN DATE
713 IS
714 x_progress VARCHAR2(3);
715 v_max_rcv_date DATE := NULL;
716 v_shipment_date DATE := NULL;
717 BEGIN
718
719 x_progress := '001';
720
721 --
722 -- Get max date from rcv_transactions, including corrections
723 --
724 BEGIN
725 SELECT MAX(last_update_date)
726 INTO v_max_rcv_date
727 FROM rcv_transactions
728 WHERE po_line_location_id = p_line_location_id;
729
730 EXCEPTION
731 WHEN NO_DATA_FOUND THEN
732 v_max_rcv_date := NULL;
733 END;
734
735 x_progress := '002';
736
737 --
738 -- Get max date from po_line_locations_all
739 --
740 BEGIN
741 SELECT last_update_date
742 INTO v_shipment_date
743 FROM po_line_locations_all
744 WHERE line_location_id = p_line_location_id;
745 END;
746
747 RETURN(GREATEST(NVL(v_max_rcv_date, v_shipment_date - 1),
748 v_shipment_date));
749
750 EXCEPTION
751 WHEN OTHERS THEN
752 RAISE;
753 RETURN NULL;
754
755 END get_last_trx_date;
756
757
758
759
760 -- get_rcv_txn_qty
761 -- --------------------
762 -- This function returns the quantity received, accepted, or rejected,
763 -- from a shipment line.
764 -- The quantity returned is in the primary unit of measure.
765 --
766 FUNCTION get_rcv_txn_qty(p_line_location_id NUMBER,
767 p_txn_type VARCHAR2)
768 RETURN NUMBER
769 IS
770 v_quantity NUMBER := 0;
771 v_txn_qty NUMBER := 0;
772 v_correction_qty NUMBER := 0;
773 x_progress VARCHAR2(3);
774 invalid_type EXCEPTION;
775 BEGIN
776
777 x_progress := '001';
778
779 IF p_txn_type NOT IN ('RECEIVE', 'ACCEPT', 'REJECT') THEN
780 RAISE invalid_type;
781 END IF;
782
783 x_progress := '002';
784
785 SELECT SUM(primary_quantity)
786 INTO v_txn_qty
787 FROM rcv_transactions
788 WHERE po_line_location_id = p_line_location_id
789 AND transaction_type = p_txn_type;
790
791 x_progress := '003';
792
793 SELECT SUM(rcor.primary_quantity)
794 INTO v_correction_qty
795 FROM rcv_transactions rcor,
796 rcv_transactions rct
797 WHERE rcor.po_line_location_id = p_line_location_id
798 AND rcor.transaction_type = 'CORRECT'
799 AND rct.transaction_id = rcor.parent_transaction_id
800 AND rct.transaction_type = p_txn_type;
801
802 v_quantity := NVL(v_txn_qty, 0) + NVL(v_correction_qty, 0);
803
804 RETURN(v_quantity);
805
806 EXCEPTION
807 WHEN invalid_type THEN
808 POA_LOG.put_line('get_rcv_txn_qty: ' || x_progress
809 || ' ' || 'Invalid transaction Type - '
810 || p_txn_type);
811 POA_LOG.put_line(' ');
812 RAISE;
813 RETURN(v_quantity);
814
815 WHEN OTHERS THEN
816 POA_LOG.put_line('get_rcv_txn_qty: ' || x_progress
817 || ' ' || sqlerrm);
818 POA_LOG.put_line(' ');
819 RAISE;
820 RETURN(v_quantity);
821
822 END get_rcv_txn_qty;
823
824
825
826 END POA_SUPPERF_API_PKG;
[Home] [Help]