1 PACKAGE BODY PO_UOM_S as
2 /* $Header: RCVTXU1B.pls 120.9.12020000.9 2013/05/03 10:39:14 bpulivar ship $*/
3
4 -- <INVCONV R12>
5 g_chktype_TRACKING_QTY_IND_S CONSTANT
6 MTL_SYSTEM_ITEMS_B.TRACKING_QUANTITY_IND%TYPE
7 := 'PS';
8
9 /*============================== PO_UOM_S ================================*/
10
11 --BUG 5080295: unit_of_measure is a VARCHAR2(25). so we need size
12 --25+25+2+38 = 90
13 TYPE uom_conversion_table_type IS TABLE OF NUMBER INDEX BY VARCHAR2(90);
14
15 g_uom_conversion_table uom_conversion_table_type;
16
17 /*===========================================================================
18
19 FUNCTION NAME : val_unit_of_measure()
20
21 ===========================================================================*/
22 FUNCTION val_unit_of_measure(X_unit_of_measure IN VARCHAR2) return BOOLEAN IS
23
24 X_progress varchar2(3) := NULL;
25 X_unit_of_measure_v varchar2(25) := NULL;
26
27 BEGIN
28
29 X_progress := '010';
30
31 /* Check if the given Unit of Measure is active */
32
33 SELECT unit_of_measure
34 INTO X_unit_of_measure_v
35 FROM mtl_units_of_measure
36 WHERE sysdate < nvl(disable_date, sysdate + 1)
37 AND unit_of_measure = X_unit_of_measure;
38
39 return (TRUE);
40
41 EXCEPTION
42
43 when no_data_found then
44 return (FALSE);
45 when others then
46 po_message_s.sql_error('val_unit_of_measure',X_progress,sqlcode);
47 raise;
48
49 END val_unit_of_measure;
50
51 /*===========================================================================
52
53 PROCEDURE NAME: uom_convert()
54
55 ===========================================================================*/
56
57 PROCEDURE uom_convert( from_quantity in number,
58 from_uom in varchar2,
59 item_id in number,
60 to_uom in varchar2,
61 to_quantity out NOCOPY number)
62 IS
63
64 uom_rate number := 0;
65 x_progress VARCHAR2(3) := NULL;
66
67 BEGIN
68
69 /*
70 ** debug
71 ** Call the stored function po_uom_convert to get the rate for now.
72 ** Once Inventory has defined their procedure, change this call to the
73 ** Inventory procedure
74 */
75
76 x_progress := 5;
77
78 uom_rate := po_uom_convert(from_uom, to_uom, item_id);
79
80 /*
81 * BUG: 972611 (Base 11.0 bug 972454)
82 * The variable to_quantity was rounded to 6 digits in bug 491623
83 * for 107. This change was not carried over to 11.
84 */
85
86 to_quantity := round(from_quantity * uom_rate,9) ; /* Bug 7348590 */
87 /*changed precision to 9 as OM accepts precision value upto 9 only. and it satisfies the need of changing precision from 6 to 15 also bug 8393676*/
88
89 EXCEPTION
90
91 WHEN OTHERS THEN
92 po_message_s.sql_error('uom_convert', x_progress, sqlcode);
93 RAISE;
94
95 END uom_convert;
96
97 --Bug 15937903,override uom_convert to round quantity correctly
98 PROCEDURE uom_convert2(item_id in number,
99 source_org_id in number,
100 from_uom in varchar2,
101 unit_of_issue in varchar2,
102 from_quantity in number,
103 to_quantity out NOCOPY number)
104 IS
105
106 rounding_factor number := null;
107 round_test boolean := null;
108 converted_quantity number := NULL;
109 remainder number := NULL;
110 error_type varchar2(30) := NULL;
111 x_progress VARCHAR2(3) := NULL;
112 x_unit_of_issue VARCHAR2(30) := NULL;
113
114 BEGIN
115
116 x_progress := 5;
117 if (from_quantity = 0) then
118 to_quantity := from_quantity;
119 return;
120 end if;
121
122 x_progress := 10;
123 x_unit_of_issue := unit_of_issue;
124 round_test := po_uom_sv2.convert_quantity(item_id,
125 source_org_id,
126 from_quantity,
127 from_uom,
128 converted_quantity,
129 rounding_factor,
130 x_unit_of_issue,
131 error_type);
132
133 if (round_test = TRUE) then
134
135 if (converted_quantity < 1) then
136 remainder := converted_quantity;
137 else
138 remainder := mod(converted_quantity,trunc(converted_quantity));
139 end if;
140
141 x_progress := 15;
142
143 if (remainder >= rounding_factor) then
144 to_quantity := trunc(converted_quantity)+1;
145 elsif (remainder < rounding_factor) then
146 to_quantity := trunc(converted_quantity);
147 end if;
148
149 elsif (round_test = FALSE) then
150 to_quantity := from_quantity;
151 end if;
152 EXCEPTION
153 WHEN OTHERS THEN
154 po_message_s.sql_error('uom_convert2', x_progress, sqlcode);
155 RAISE;
156
157 END uom_convert2;
158 --Bug 15937903
159
160 /*===========================================================================
161
162 PROCEDURE NAME: val_uom_conversion()
163
164 ===========================================================================*/
165
166 PROCEDURE val_uom_conversion IS
167
168 x_progress VARCHAR2(3) := NULL;
169
170 BEGIN
171
172 null;
173
174 EXCEPTION
175
176 WHEN OTHERS THEN
177 po_message_s.sql_error('val_uom_conversion', x_progress, sqlcode);
178 RAISE;
179
180 END val_uom_conversion;
181
182 procedure po_uom_conversion ( from_unit in varchar2,
183 to_unit in varchar2,
184 item_id in number,
185 uom_rate out NOCOPY number ) IS
186 /*
187 ** declare variables that are referenced in the cursor definitions
188 */
189
190 from_class varchar2(10);
191 to_class varchar2(10);
192
193 x_progress varchar2(3) := NULL;
194
195 cursor standard_conversions is
196 select t.conversion_rate std_to_rate,
197 t.uom_class std_to_class,
198 f.conversion_rate std_from_rate,
199 f.uom_class std_from_class
200 from mtl_uom_conversions t,
201 mtl_uom_conversions f
202 where t.inventory_item_id in (item_id, 0)
203 and t.unit_of_measure = to_unit
204 and nvl(t.disable_date, trunc(sysdate) + 1) > trunc(sysdate)
205 and f.inventory_item_id in (item_id, 0)
206 and f.unit_of_measure = from_unit
207 and nvl(f.disable_date, trunc(sysdate) + 1) > trunc(sysdate)
208 order by t.inventory_item_id desc,
209 f.inventory_item_id desc;
210
211 std_rec standard_conversions%rowtype;
212
213 /* Bug# 1834317 - Added the condition where inventory_item_id in
214 (item_id, 0) */
215
216 /* the above fix caused bug : 2076110
217 we should handle inter-class conv. for inventory and one-time items separately */
218
219 cursor interclass_conversions(inv_item_flag varchar2) is
220 select decode(to_uom_class, to_class, 1, 2) to_flag,
221 decode(from_uom_class, from_class, 1, to_class, 2, 0) from_flag,
222 conversion_rate rate
223 from mtl_uom_class_conversions
224 where ((inv_item_flag = 'Y' and inventory_item_id = item_id)
225 or
226 (inv_item_flag = 'N' and inventory_item_id = 0))
227 and to_uom_class in (from_class, to_class)
228 and nvl(disable_date, trunc(sysdate) + 1) > trunc(sysdate);
229
230 class_rec interclass_conversions%rowtype;
231
232
233 invalid_conversion exception;
234 invalid_interclass_conversion exception; -- Bug 10202212
235
236 type conv_tab is table of number
237 index by binary_integer;
238
239 type class_tab is table of varchar2(10)
240 index by binary_integer;
241
242 interclass_rate_tab conv_tab;
243 from_class_flag_tab conv_tab;
244 to_class_flag_tab conv_tab;
245 from_rate_tab conv_tab;
246 to_rate_tab conv_tab;
247 from_class_tab class_tab;
248 to_class_tab class_tab;
249
250 std_index number;
251 class_index number;
252
253 from_rate number := 1;
254 to_rate number := 1;
255 interclass_rate number := 1;
256 to_class_rate number := 1;
257 from_class_rate number := 1;
258 msgbuf varchar2(200);
259 inv_item_flag varchar2(1);
260
261 begin
262
263 /*
264 ** Conversion between between two UOMS.
265 **
266 ** 1. The conversion always starts from the conversion defined, if exists,
267 ** for an specified item.
268 ** 2. If the conversion id not defined for that specific item, then the
269 ** standard conversion, which is defined for all items, is used.
270 ** 3. When the conversion involves two different classes, then
271 ** interclass conversion is activated.
272 */
273
274
275 /*
276 ** If from and to units are the same, conversion rate is 1.
277 ** Go immediately to the end of the procedure to exit.
278 */
279 x_progress := '010';
280
281 if (from_unit = to_unit) then
282
283 uom_rate := 1;
284 return;
285
286 end if;
287
288 /* Bug 12915619 remove this cache since two RTP can run with same db session in online mode
289 * IF g_uom_conversion_table.EXISTS(from_unit || '-' || to_unit || '-' ||to_char(nvl(item_id,0))) THEN
290 * uom_rate := g_uom_conversion_table(from_unit || '-' || to_unit || '-' ||to_char(nvl(item_id,0)));
291 * RETURN;
292 * END IF;
293 */
294
295 /*
296 ** Get item specific or standard conversions
297 */
298 x_progress := '020';
299
300 open standard_conversions;
301
302 std_index := 0;
303
304 loop
305
306 x_progress := '030';
307
308 fetch standard_conversions into std_rec;
309 exit when standard_conversions%notfound;
310
311 std_index := std_index + 1;
312
313 from_rate_tab(std_index) := std_rec.std_from_rate;
314 from_class_tab(std_index) := std_rec.std_from_class;
315 to_rate_tab(std_index) := std_rec.std_to_rate;
316 to_class_tab(std_index) := std_rec.std_to_class;
317
318 end loop;
319
320 close standard_conversions;
321
322 /*
323 **
324 */
325 x_progress := '040';
326
327 if (std_index = 0) then
328
329 /*
330 ** No conversions defined
331 */
332
333 msgbuf := msgbuf||'Invalid standard conversion : ';
334 msgbuf := msgbuf||'From unit: '||from_unit||' ';
335 msgbuf := msgbuf||'To unit: '||to_unit||' ';
336 raise invalid_conversion;
337
338 else
339
340 /*
341 ** Conversions are ordered. Item specific conversions will be
342 ** returned first.
343 */
344
345 from_class := from_class_tab(1);
346 to_class := to_class_tab(1);
347 from_rate := from_rate_tab(1);
348 to_rate := to_rate_tab(1);
349
350 end if;
351
352 x_progress := '050';
353
354 if (from_class <> to_class) then
355
356 /*
357 ** Load interclass conversion tables
358 ** If two rows are returned, it implies that there is no direct
359 ** conversion between them.
360 ** If one row is returned, then it may imply that there is a direct
361 ** conversion between them or one class is not defined in the
362 ** class conversion table.
363 */
364
365 class_index := 0;
366
367 if (item_id is null or item_id = 0) then
368 inv_item_flag := 'N';
369 else
370 inv_item_flag := 'Y';
371 end if;
372
373 open interclass_conversions(inv_item_flag);
374
375 loop
376
377 x_progress := '060';
378
379 fetch interclass_conversions into class_rec;
380 exit when interclass_conversions%notfound;
381
382 class_index := class_index + 1;
383
384 to_class_flag_tab(class_index) := class_rec.to_flag;
385 from_class_flag_tab(class_index) := class_rec.from_flag;
386 interclass_rate_tab(class_index) := class_rec.rate;
387
388 end loop;
389
390 close interclass_conversions;
391
392 x_progress := '070';
393
394 if (class_index = 2) then
395
396 /* Start Bug 3654053 : We should error out for Expense Items if the UOM
397 Interclass Conversions are not defined between the two UOMs. Added the
398 following if condition. If the inv_item_flag = N then we raise an
399 exception.
400 */
401
402 if inv_item_flag = 'Y' then
403
404 if (to_class_flag_tab(1) = 1) then
405
406 to_class_rate := interclass_rate_tab(1);
407 from_class_rate := interclass_rate_tab(2);
408
409 else
410
411 to_class_rate := interclass_rate_tab(2);
412 from_class_rate := interclass_rate_tab(1);
413
414 end if;
415
416 /* Bug 3385209 start
417 ** Added the following statement to calculate the interclass_rate between the
418 ** Source Doc UOM and the Transaction UOM when there is no direct conversion
419 ** defined between them.*/
420
421 interclass_rate := from_class_rate/to_class_rate;
422
423 /* Bug 3385209 End */
424
425 else
426
427 /*
428 ** No interclass conversion is defined for Expense Items
429 */
430
431 msgbuf := msgbuf||'Invalid Interclass conversion : ';
432 msgbuf := msgbuf||'From unit: '||from_unit||' ';
433 msgbuf := msgbuf||'To unit: '||to_unit||' ';
434 raise invalid_interclass_conversion; -- Bug 10202212
435
436
437 end if;
438
439 /* End Bug# 3654053 */
440
441 elsif ((class_index = 1) and
442 (to_class_flag_tab(1) = from_class_flag_tab(1) )) then
443
444
445 if (to_class_flag_tab(1) = 1) then
446
447 to_class_rate := interclass_rate_tab(1);
448 from_class_rate := 1;
449
450 else
451
452 to_class_rate := 1;
453 from_class_rate := interclass_rate_tab(1);
454
455 end if;
456
457 x_progress := '080';
458
459 interclass_rate := from_class_rate/to_class_rate;
460
461 else
462
463 /*
464 ** No interclass conversion is defined
465 */
466
467 msgbuf := msgbuf||'Invalid Interclass conversion : ';
468 msgbuf := msgbuf||'From unit: '||from_unit||' ';
469 msgbuf := msgbuf||'To unit: '||to_unit||' ';
470 raise invalid_interclass_conversion; -- Bug 10202212
471
472
473
474 end if;
475
476
477 end if;
478
479
480 /*
481 ** conversion rates are defaulted to '1' at the start of the procedure
482 ** so seperate calculations are not required for standard/interclass
483 ** conversions
484 */
485 x_progress := '090';
486
487 uom_rate := (from_rate * interclass_rate) / to_rate;
488
489 /* Bug 12915619 remove this cache since two RTP can run with same db session in online mode
490 * g_uom_conversion_table(from_unit || '-' || to_unit || '-' ||to_char(nvl(item_id,0))) := uom_rate;
491 */
492
493 /*
494 ** Put a label and a null statement over here so that you can
495 ** the goto statements can branch here.
496 */
497
498 <<procedure_end>>
499
500 null;
501
502 -- Block modified for Bug 10202212 to display appropriate error message to user.
503
504 exception
505 WHEN invalid_interclass_conversion THEN
506 PO_MESSAGE_S.APP_ERROR('PO_SUB_UOM_CLASS_CONVERSION');
507 WHEN invalid_conversion THEN
508 --Bug 14061304: set tokens
509 PO_MESSAGE_S.APP_ERROR('PO_UOM_CONVERSION_FAIL',
510 'PREV_UOM', from_unit, 'CURR_UOM', to_unit);
511 --Bug 14061304: End
512 WHEN OTHERS THEN
513 po_message_s.sql_error('po_uom_conversion', x_progress, sqlcode);
514 RAISE;
515
516 -- Bug 10202212 ends
517
518 end po_uom_conversion;
519
520 function po_uom_convert ( from_unit in varchar2,to_unit in varchar2,
521 item_id in number ) return number is
522
523 /*
524 ** declare variables that are passed to po_uom_conversion
525 */
526
527 uom_rate number;
528 msgbuf varchar2(200);
529 x_progress VARCHAR2(3) := NULL;
530
531 begin
532
533 /* Call the po_uom_conversion procedure to calculate the uom_rate
534 ** and return.
535 ** This function previously calculated the conversion rate in the
536 ** body of the function itself. This was replaced by the present
537 ** procedure call because of a PL/SQL bug which caused memory leaks
538 ** while using tables in functions. Refer to bug 191321 for details.
539 */
540
541 /* Bug 5218352: Call po_uom_conversion only if from and to UOMs are
542 ** different.
543 */
544
545 IF from_unit <> to_unit THEN
546 po_uom_s.po_uom_conversion(from_unit, to_unit, item_id, uom_rate);
547 ELSE
548 uom_rate := 1;
549 END IF;
550
551 return uom_rate;
552
553 exception
554
555 WHEN OTHERS THEN
556 po_message_s.sql_error('po_uom_convert', x_progress, sqlcode);
557 RAISE;
558
559 end po_uom_convert;
560
561 /*
562 ** Function GET_PRIMARY_UOM
563 **
564 ** function returns the primary UOM based on item_id/organization
565 ** for both pre-defined and one-time items
566 */
567
568 function get_primary_uom ( item_id in number, org_id in number,
569 current_unit_of_measure in varchar2 ) return varchar2 is
570
571 primary_unit varchar2(25);
572 msgbuf varchar2(200);
573 x_progress VARCHAR2(3) := NULL;
574
575 begin
576
577 msgbuf := '';
578 primary_unit := '';
579
580
581 if (item_id is null) then
582
583 /*
584 ** for a one-time item, the primary uom is the
585 ** base uom for the item's current uom class
586 */
587
588 begin
589
590 select buom.unit_of_measure
591 into primary_unit
592 from mtl_units_of_measure cuom,
593 mtl_units_of_measure buom
594 where cuom.unit_of_measure = current_unit_of_measure
595 and cuom.uom_class = buom.uom_class
596 and buom.base_uom_flag = 'Y';
597
598 exception
599
600 when others then
601 msgbuf := msgbuf||'Statement: 001: ';
602 msgbuf := msgbuf||'Current unit: ';
603 msgbuf := msgbuf||current_unit_of_measure||' ';
604 raise;
605
606 end;
607
608 else
609
610 /*
611 ** for pre-defined items, get the primary uom
612 ** from mtl_system_items
613 */
614
615 begin
616
617 select msi.primary_unit_of_measure
618 into primary_unit
619 from mtl_system_items msi
620 where msi.inventory_item_id = item_id
621 and msi.organization_id = org_id;
622
623 exception
624
625 when others then
626 msgbuf := msgbuf||'Statement: 002: ';
627 msgbuf := msgbuf||'Item ID: '||item_id||' ';
628 msgbuf := msgbuf||'Organization ID: '||org_id||' ';
629 raise;
630
631 end;
632
633 end if;
634
635 return primary_unit;
636
637 exception
638
639 WHEN OTHERS THEN
640 po_message_s.sql_error('get_primary_uom', x_progress, sqlcode);
641 RAISE;
642
643 end get_primary_uom;
644
645 /*========================================================================
646
647 FUNCTION : po_uom_convert_p() -dreddy
648
649 Created a function po_uom_convert_p which is pure function to be used in
650 the where and select clauses of a SQL stmt.bug 1365577
651 ******************************************************
652 So, any change in the po_uom_convertion proc in rvpo02
653 should be implemented in this new function.
654 ******************************************************
655 ========================================================================*/
656 function po_uom_convert_p ( from_unit varchar2, to_unit
657 varchar2, item_id number ) return number as
658
659 /*
660 ** declare variables that are referenced in the cursor definitions
661 */
662
663 from_class varchar2(10);
664 to_class varchar2(10);
665
666
667 cursor standard_conversions is
668 select t.conversion_rate std_to_rate,
669 t.uom_class std_to_class,
670 f.conversion_rate std_from_rate,
671 f.uom_class std_from_class
672 from mtl_uom_conversions t,
673 mtl_uom_conversions f
674 where t.inventory_item_id in (item_id, 0)
675 and t.unit_of_measure = to_unit
676 and nvl(t.disable_date, trunc(sysdate) + 1) > trunc(sysdate)
677 and f.inventory_item_id in (item_id, 0)
678 and f.unit_of_measure = from_unit
679 and nvl(f.disable_date, trunc(sysdate) + 1) > trunc(sysdate)
680 order by t.inventory_item_id desc,
681 f.inventory_item_id desc;
682
683 std_rec standard_conversions%rowtype;
684
685
686 cursor interclass_conversions is
687 select decode(to_uom_class, to_class, 1, 2) to_flag,
688 decode(from_uom_class, from_class, 1, to_class, 2, 0) from_flag,
689 conversion_rate rate
690 from mtl_uom_class_conversions
691 where inventory_item_id = item_id
692 and to_uom_class in (from_class, to_class)
693 and nvl(disable_date, trunc(sysdate) + 1) > trunc(sysdate);
694
695 class_rec interclass_conversions%rowtype;
696
697
698 invalid_conversion exception;
699
700 type conv_tab is table of number
701 index by binary_integer;
702
703 type class_tab is table of varchar2(10)
704 index by binary_integer;
705
706 interclass_rate_tab conv_tab;
707 from_class_flag_tab conv_tab;
708 to_class_flag_tab conv_tab;
709 from_rate_tab conv_tab;
710 to_rate_tab conv_tab;
711 from_class_tab class_tab;
712 to_class_tab class_tab;
713
714 std_index number;
715 class_index number;
716
717 uom_rate number := 1;
718 from_rate number := 1;
719 to_rate number := 1;
720 interclass_rate number := 1;
721 to_class_rate number := 1;
722 from_class_rate number := 1;
723 msgbuf varchar2(200);
724
725 begin
726
727 /*
728 ** Conversion between between two UOMS.
729 **
730 ** 1. The conversion always starts from the conversion defined, if exists,
731 ** for an specified item.
732 ** 2. If the conversion id not defined for that specific item, then the
733 ** standard conversion, which is defined for all items, is used.
734 ** 3. When the conversion involves two different classes, then
735 ** interclass conversion is activated.
736 */
737
738
739 /*
740 ** If from and to units are the same, conversion rate is 1.
741 ** Go immediately to the end of the procedure to exit.
742 */
743
744 if (from_unit = to_unit) then
745
746 uom_rate := 1;
747 goto procedure_end;
748
749 end if;
750
751
752 /*
753 ** Get item specific or standard conversions
754 */
755
756 open standard_conversions;
757
758 std_index := 0;
759
760 loop
761
762 fetch standard_conversions into std_rec;
763 exit when standard_conversions%notfound;
764
765 std_index := std_index + 1;
766
767 from_rate_tab(std_index) := std_rec.std_from_rate;
768 from_class_tab(std_index) := std_rec.std_from_class;
769 to_rate_tab(std_index) := std_rec.std_to_rate;
770 to_class_tab(std_index) := std_rec.std_to_class;
771
772 end loop;
773
774 close standard_conversions;
775
776 /*
777 **
778 */
779
780 if (std_index = 0) then
781
782 /*
783 ** No conversions defined
784 */
785
786 msgbuf := msgbuf||'Invalid standard conversion : ';
787 msgbuf := msgbuf||'From unit: '||from_unit||' ';
788 msgbuf := msgbuf||'To unit: '||to_unit||' ';
789 return -999 ;
790
791 else
792
793 /*
794 ** Conversions are ordered. Item specific conversions will be
795 ** returned first.
796 */
797
798 from_class := from_class_tab(1);
799 to_class := to_class_tab(1);
800 from_rate := from_rate_tab(1);
801 to_rate := to_rate_tab(1);
802
803 end if;
804
805
806 if (from_class <> to_class) then
807
808 /*
809 ** Load interclass conversion tables
810 ** If two rows are returned, it implies that there is no direct
811 ** conversion between them.
812 ** If one row is returned, then it may imply that there is a direct
813 ** conversion between them or one class is not defined in the
814 ** class conversion table.
815 */
816
817 class_index := 0;
818
819 open interclass_conversions;
820
821 loop
822
823 fetch interclass_conversions into class_rec;
824 exit when interclass_conversions%notfound;
825
826 class_index := class_index + 1;
827
828 to_class_flag_tab(class_index) := class_rec.to_flag;
829 from_class_flag_tab(class_index) := class_rec.from_flag;
830 interclass_rate_tab(class_index) := class_rec.rate;
831
832 end loop;
833
834 close interclass_conversions;
835
836 if (class_index = 2) then
837
838 if (to_class_flag_tab(1) = 1) then
839
840 to_class_rate := interclass_rate_tab(1);
841 from_class_rate := interclass_rate_tab(2);
842
843 else
844
845 to_class_rate := interclass_rate_tab(2);
846 from_class_rate := interclass_rate_tab(1);
847
848 end if;
849
850 elsif ((class_index = 1) and
851 (to_class_flag_tab(1) = from_class_flag_tab(1) )) then
852
853
854 if (to_class_flag_tab(1) = 1) then
855
856 to_class_rate := interclass_rate_tab(1);
857 from_class_rate := 1;
858
859 else
860
861 to_class_rate := 1;
862 from_class_rate := interclass_rate_tab(1);
863
864 end if;
865
866
867 interclass_rate := from_class_rate/to_class_rate;
868
869 else
870
871 /*
872 ** No interclass conversion is defined
873 */
874
875 msgbuf := msgbuf||'Invalid Interclass conversion : ';
876 msgbuf := msgbuf||'From unit: '||from_unit||' ';
877 msgbuf := msgbuf||'To unit: '||to_unit||' ';
878 return -999 ;
879
880
881 end if;
882
883
884 end if;
885
886
887 /*
888 ** conversion rates are defaulted to '1' at the start of the procedure
889 ** so seperate calculations are not required for standard/interclass
890 ** conversions
891 */
892
893 uom_rate := (from_rate * interclass_rate) / to_rate;
894
895 /*
896 ** Put a label and a null statement over here so that you can
897 ** the goto statements can branch here.
898 */
899
900 <<procedure_end>>
901
902 return ROUND(NVL(uom_rate, 1), 5) ;
903
904 end po_uom_convert_p;
905
906
907 /*===========================================================================
908 PROCEDURE NAME: get_secondary_uom()
909
910 DESCRIPTION:
911 This function returns the primary UOM based on item_id/organization
912 for both pre-defined and one-time items
913
914 USAGE:
915 uom := po_uom_s.get_secondary_uom ( item_id number, org_id number,
916 current_sec_unit_of_measure varchar2 )
917
918 PARAMETERS:
919 item_id IN number - item id (null for one time items)
920 org_id IN number - org id
921 current_sec_unit_of_measure IN VARCHAR2 - currently defined uom on trx.
922
923 RETURNS:
924
925 secondary_uom - VARCHAR2 - Secondary UOM for given item and org
926
927 DESIGN REFERENCES: Generic
928
929 ALGORITHM:
930
931 NOTES:
932
933 OPEN ISSUES:
934
935 CLOSED ISSUES:
936
937 CHANGE HISTORY:
938 09-SEP-05 Preetam Bamb Created
939 ===========================================================================*/
940 procedure get_secondary_uom (p_item_id in number,
941 p_org_id in number,
942 x_secondary_uom_code out NOCOPY varchar2,
943 x_secondary_unit_of_measure out NOCOPY varchar2) is
944
945 msgbuf varchar2(200);
946 x_progress varchar2(3) := NULL;
947 l_tracking_quantity_ind mtl_system_items.tracking_quantity_ind%TYPE:= NULL;
948
949 begin
950 msgbuf := '';
951 x_progress := '001';
952
953 if (p_item_id is not null) then
954 begin
955 select msi.tracking_quantity_ind,msi.secondary_uom_code, muom.unit_of_measure
956 into l_tracking_quantity_ind, x_secondary_uom_code,x_secondary_unit_of_measure
957 from mtl_system_items msi, mtl_units_of_measure muom
958 where msi.inventory_item_id = p_item_id
959 and msi.organization_id = p_org_id
960 and msi.secondary_uom_code = muom.uom_code(+);
961
962 x_progress := '002';
963 if l_tracking_quantity_ind <> g_chktype_TRACKING_QTY_IND_S then
964 x_secondary_uom_code := NULL;
965 x_secondary_unit_of_measure := NULL;
966 end if;
967
968 exception when NO_DATA_FOUND then
969 x_secondary_uom_code := NULL;
970 x_secondary_unit_of_measure := NULL;
971 end;
972 else
973 x_secondary_uom_code := NULL;
974 x_secondary_unit_of_measure := NULL;
975 end if;
976
977 x_progress := '003';
978 exception
979
980 WHEN OTHERS THEN
981 po_message_s.sql_error('get_secondary_uom', x_progress, sqlcode);
982 RAISE;
983
984 end;
985 /*===========================================================================
986 PROCEDURE NAME: get_unit_of_measure()
987
988 DESCRIPTION:
989 This function returns the unit of measure for the passed uom code
990
991 USAGE:
992 uom := po_uom_s.get_unit_of_measure(
993 p_uom_code in varchar2,
994 x_unit_of_measure out NOCOPY varchar2);
995
996 PARAMETERS:
997 x_uom_code IN VARCHAR2 - items secondary uom code.
998 x_unit_of_measure OUT VARCHAR2 - items secondary unit of meas
999
1000 DESIGN REFERENCES: Generic
1001
1002 ALGORITHM:
1003
1004 NOTES:
1005
1006 OPEN ISSUES:
1007
1008 CLOSED ISSUES:
1009
1010 CHANGE HISTORY:
1011 09-SEP-05 Preetam Bamb Created
1012 ===========================================================================*/
1013 procedure get_unit_of_measure(
1014 p_uom_code in varchar2,
1015 x_unit_of_measure out NOCOPY varchar2)
1016 IS
1017 x_progress varchar2(3) := NULL;
1018 BEGIN
1019
1020 x_progress := '001';
1021
1022 if p_uom_code is NOT NULL THEN
1023 begin
1024 select unit_of_measure
1025 into x_unit_of_measure
1026 from mtl_units_of_measure
1027 where uom_code = p_uom_code;
1028
1029 x_progress := '002';
1030 exception when no_data_found then
1031 x_unit_of_measure := NULL;
1032 end;
1033 end if;
1034
1035 x_progress := '003';
1036 exception
1037
1038 WHEN OTHERS THEN
1039 po_message_s.sql_error('get_secondary_uom', x_progress, sqlcode);
1040 RAISE;
1041 END;
1042
1043 /*========================================================================
1044
1045 FUNCTION : rti_trx_qty_to_soc_qty()
1046
1047 Created a function rti_trx_qty_to_soc_qty which is pure function to be used in
1048 the where and select clauses of rvtvq.lpc the lot specific UOM convertion for rti
1049 source_doc_quantity for bug 14106596
1050
1051 ========================================================================*/
1052
1053
1054 FUNCTION RTI_TRX_QTY_TO_SOC_QTY(P_INTERFACE_TRANSACTION_ID IN NUMBER,
1055 P_TO_ORG_ID IN NUMBER,
1056 P_ITEM_ID IN NUMBER,
1057 P_FROM_QTY IN NUMBER,
1058 P_FROM_UOM IN VARCHAR2,
1059 P_TO_UOM IN VARCHAR2)
1060 /*this fuction is used to caculate the source_doc_quantity for rti*/
1061 RETURN NUMBER IS
1062
1063 L_TOT_QTY NUMBER := 0;
1064 L_TOT_QTY1 NUMBER := 0;
1065 L_SRC_DOC_QTY NUMBER := 0;
1066 -- Local variables here
1067 L_QTY NUMBER := 0;
1068 L_QTY1 NUMBER := 0;
1069
1070 L_MTLT_COUNT NUMBER := 0;
1071 L_MTLI_COUNT NUMBER := 0;
1072 L_RATE NUMBER := 1;
1073 L_RATE1 NUMBER := 1;
1074
1075 --For interclass Bug15932062
1076 -- From UOM
1077 l_base_fuom_flag mtl_units_of_measure.base_uom_flag%TYPE;
1078 l_fuom_class mtl_units_of_measure.uom_class%TYPE;
1079 l_base_fuom mtl_units_of_measure.unit_of_measure%TYPE;
1080 -- To UOM
1081 l_base_tuom_flag mtl_units_of_measure.base_uom_flag%TYPE;
1082 l_tuom_class mtl_units_of_measure.uom_class%TYPE;
1083 l_base_tuom mtl_units_of_measure.unit_of_measure%TYPE;
1084
1085 x_progress varchar2(3) := NULL;
1086 --For interclass end Bug15932062
1087
1088 CURSOR LOT_NUM_CUR(P_INTERFACE_TRANSACTION_ID NUMBER) IS
1089 SELECT TRANSACTION_QUANTITY, LOT_NUMBER
1090 FROM MTL_TRANSACTION_LOTS_TEMP
1091 WHERE PRODUCT_TRANSACTION_ID = P_INTERFACE_TRANSACTION_ID;
1092
1093 CURSOR LOT_NUM_INT_CUR(P_INTERFACE_TRANSACTION_ID NUMBER) IS
1094 SELECT TRANSACTION_QUANTITY, LOT_NUMBER
1095 FROM MTL_TRANSACTION_LOTS_INTERFACE
1096 WHERE PRODUCT_TRANSACTION_ID = P_INTERFACE_TRANSACTION_ID;
1097
1098 BEGIN
1099
1100 --Bug15932062
1101
1102 x_progress := '001';
1103
1104 SELECT BASE_UOM_FLAG, uom_CLASS
1105 INTO l_base_fuom_flag, l_fuom_class
1106 FROM MTL_UNITS_OF_MEASURE
1107 WHERE UNIT_OF_MEASURE = P_FROM_UOM
1108 AND nvl(disable_date, trunc(sysdate) + 1) > trunc(sysdate) ;
1109
1110
1111 x_progress := '002';
1112
1113 SELECT BASE_UOM_FLAG, uom_CLASS
1114 INTO l_base_tuom_flag, l_tuom_class
1115 FROM MTL_UNITS_OF_MEASURE
1116 WHERE UNIT_OF_MEASURE = P_TO_UOM
1117 AND nvl(disable_date, trunc(sysdate) + 1) > trunc(sysdate) ;
1118
1119
1120 x_progress := '003';
1121 IF l_base_fuom_flag='Y' THEN
1122 l_base_fuom :=P_FROM_UOM;
1123 ELSE
1124 SELECT DISTINCT UNIT_OF_MEASURE
1125 INTO l_base_fuom
1126 FROM MTL_UNITS_OF_MEASURE
1127 WHERE uom_CLASS=l_fuom_class
1128 AND BASE_UOM_FLAG='Y'
1129 AND nvl(disable_date, trunc(sysdate) + 1) > trunc(sysdate);
1130 END IF;
1131
1132
1133 x_progress := '004';
1134 IF l_base_tuom_flag='Y' THEN
1135 l_base_tuom :=P_to_UOM;
1136 ELSE
1137 SELECT DISTINCT UNIT_OF_MEASURE
1138 INTO l_base_tuom
1139 FROM MTL_UNITS_OF_MEASURE
1140 WHERE uom_CLASS=l_tuom_class
1141 AND BASE_UOM_FLAG='Y'
1142 AND nvl(disable_date, trunc(sysdate) + 1) > trunc(sysdate);
1143 END IF;
1144
1145
1146
1147 --End Bug15932062
1148
1149 x_progress := '005';
1150
1151 BEGIN
1152
1153 SELECT COUNT(1)
1154 INTO L_MTLT_COUNT
1155 FROM MTL_LOT_UOM_CLASS_CONVERSIONS MLUC,
1156 MTL_TRANSACTION_LOTS_TEMP MTLT -- Used for form action
1157 WHERE MTLT.LOT_NUMBER = MLUC.LOT_NUMBER
1158 AND MLUC.ORGANIZATION_ID = P_TO_ORG_ID
1159 AND MLUC.INVENTORY_ITEM_ID = P_ITEM_ID
1160 AND MLUC.FROM_UNIT_OF_MEASURE = l_base_fuom
1161 AND MLUC.TO_UNIT_OF_MEASURE = l_base_tuom
1162 AND MTLT.PRODUCT_TRANSACTION_ID = P_INTERFACE_TRANSACTION_ID
1163 ;
1164
1165 EXCEPTION
1166 WHEN OTHERS THEN
1167
1168 L_MTLT_COUNT := 0;
1169
1170 END;
1171
1172 x_progress := '006';
1173 BEGIN
1174 SELECT COUNT(1)
1175 INTO L_MTLI_COUNT
1176 FROM MTL_LOT_UOM_CLASS_CONVERSIONS MLUC,
1177 MTL_TRANSACTION_LOTS_INTERFACE MTLI -- Used for ROI&LOT action
1178 WHERE MTLI.LOT_NUMBER = MLUC.LOT_NUMBER
1179 AND MLUC.ORGANIZATION_ID = P_TO_ORG_ID
1180 AND MLUC.INVENTORY_ITEM_ID = P_ITEM_ID
1181 AND MLUC.FROM_UNIT_OF_MEASURE = l_base_fuom
1182 AND MLUC.TO_UNIT_OF_MEASURE = l_base_tuom
1183 AND MTLI.PRODUCT_TRANSACTION_ID = P_INTERFACE_TRANSACTION_ID;
1184
1185 EXCEPTION
1186 WHEN OTHERS THEN
1187
1188 L_MTLI_COUNT := 0;
1189
1190 END;
1191
1192 x_progress := '007';
1193 /* for the Form*/
1194 IF L_MTLT_COUNT > 0 THEN
1195
1196 FOR LOT_NUM_REC IN LOT_NUM_CUR(P_INTERFACE_TRANSACTION_ID) LOOP
1197
1198 L_QTY := INV_CONVERT.INV_UM_CONVERT(ITEM_ID => P_ITEM_ID,
1199 LOT_NUMBER => LOT_NUM_REC.LOT_NUMBER,
1200 ORGANIZATION_ID => P_TO_ORG_ID,
1201 PRECISION => 15,
1202 FROM_QUANTITY => LOT_NUM_REC.TRANSACTION_QUANTITY,
1203 FROM_UNIT => NULL,
1204 TO_UNIT => NULL,
1205 FROM_NAME => P_FROM_UOM,
1206 TO_NAME => P_TO_UOM);
1207
1208 L_TOT_QTY := L_QTY + L_TOT_QTY;
1209
1210 END LOOP;
1211
1212
1213
1214
1215 END IF;
1216
1217 /* for the ROI*/
1218 x_progress := '008';
1219 IF L_MTLI_COUNT > 0 THEN
1220
1221 FOR LOT_NUM_INT_REC IN LOT_NUM_INT_CUR(P_INTERFACE_TRANSACTION_ID) LOOP
1222
1223 L_QTY1 := INV_CONVERT.INV_UM_CONVERT(ITEM_ID => P_ITEM_ID,
1224 LOT_NUMBER => LOT_NUM_INT_REC.LOT_NUMBER,
1225 ORGANIZATION_ID => P_TO_ORG_ID,
1226 PRECISION => 15,
1227 FROM_QUANTITY => LOT_NUM_INT_REC.TRANSACTION_QUANTITY,
1228 FROM_UNIT => NULL,
1229 TO_UNIT => NULL,
1230 FROM_NAME => P_FROM_UOM,
1231 TO_NAME => P_TO_UOM);
1232
1233 L_TOT_QTY1 := L_QTY1 + L_TOT_QTY1;
1234
1235 END LOOP;
1236
1237
1238
1239 END IF;
1240
1241
1242 L_SRC_DOC_QTY := NVL(L_TOT_QTY, 0) + NVL(L_TOT_QTY1, 0);
1243 x_progress := '009';
1244
1245 IF (L_MTLT_COUNT = 0 AND L_MTLI_COUNT =0 ) THEN
1246
1247 L_RATE := PO_UOM_S.PO_UOM_CONVERT(P_FROM_UOM, P_TO_UOM, P_ITEM_ID);
1248 L_TOT_QTY := ROUND(P_FROM_QTY * L_RATE, 15);
1249 RETURN L_TOT_QTY;
1250 ELSE
1251 RETURN L_SRC_DOC_QTY;
1252 END IF;
1253
1254 exception WHEN OTHERS THEN
1255 po_message_s.sql_error('rti_trx_qty_to_soc_qty', x_progress, sqlcode);
1256 RAISE;
1257
1258
1259 END;
1260
1261
1262
1263
1264 END PO_UOM_S;
[Home] [Help]