1 PACKAGE BODY PO_UOM_S as
2 /* $Header: RCVTXU1B.pls 120.3.12010000.2 2008/08/04 08:43:30 rramasam 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,6) ;
87
88 EXCEPTION
89
90 WHEN OTHERS THEN
91 po_message_s.sql_error('uom_convert', x_progress, sqlcode);
92 RAISE;
93
94 END uom_convert;
95
96
97 /*===========================================================================
98
99 PROCEDURE NAME: val_uom_conversion()
100
101 ===========================================================================*/
102
103 PROCEDURE val_uom_conversion IS
104
105 x_progress VARCHAR2(3) := NULL;
106
107 BEGIN
108
109 null;
110
111 EXCEPTION
112
113 WHEN OTHERS THEN
114 po_message_s.sql_error('val_uom_conversion', x_progress, sqlcode);
115 RAISE;
116
117 END val_uom_conversion;
118
119 procedure po_uom_conversion ( from_unit in varchar2,
120 to_unit in varchar2,
121 item_id in number,
122 uom_rate out NOCOPY number ) IS
123 /*
124 ** declare variables that are referenced in the cursor definitions
125 */
126
127 from_class varchar2(10);
128 to_class varchar2(10);
129
130 x_progress varchar2(3) := NULL;
131
132 cursor standard_conversions is
133 select t.conversion_rate std_to_rate,
134 t.uom_class std_to_class,
135 f.conversion_rate std_from_rate,
136 f.uom_class std_from_class
137 from mtl_uom_conversions t,
138 mtl_uom_conversions f
139 where t.inventory_item_id in (item_id, 0)
140 and t.unit_of_measure = to_unit
141 and nvl(t.disable_date, trunc(sysdate) + 1) > trunc(sysdate)
142 and f.inventory_item_id in (item_id, 0)
143 and f.unit_of_measure = from_unit
144 and nvl(f.disable_date, trunc(sysdate) + 1) > trunc(sysdate)
145 order by t.inventory_item_id desc,
146 f.inventory_item_id desc;
147
148 std_rec standard_conversions%rowtype;
149
150 /* Bug# 1834317 - Added the condition where inventory_item_id in
151 (item_id, 0) */
152
153 /* the above fix caused bug : 2076110
154 we should handle inter-class conv. for inventory and one-time items separately */
155
156 cursor interclass_conversions(inv_item_flag varchar2) is
157 select decode(to_uom_class, to_class, 1, 2) to_flag,
158 decode(from_uom_class, from_class, 1, to_class, 2, 0) from_flag,
159 conversion_rate rate
160 from mtl_uom_class_conversions
161 where ((inv_item_flag = 'Y' and inventory_item_id = item_id)
162 or
163 (inv_item_flag = 'N' and inventory_item_id = 0))
164 and to_uom_class in (from_class, to_class)
165 and nvl(disable_date, trunc(sysdate) + 1) > trunc(sysdate);
166
167 class_rec interclass_conversions%rowtype;
168
169
170 invalid_conversion exception;
171
172 type conv_tab is table of number
173 index by binary_integer;
174
175 type class_tab is table of varchar2(10)
176 index by binary_integer;
177
178 interclass_rate_tab conv_tab;
179 from_class_flag_tab conv_tab;
180 to_class_flag_tab conv_tab;
181 from_rate_tab conv_tab;
182 to_rate_tab conv_tab;
183 from_class_tab class_tab;
184 to_class_tab class_tab;
185
186 std_index number;
187 class_index number;
188
189 from_rate number := 1;
190 to_rate number := 1;
191 interclass_rate number := 1;
192 to_class_rate number := 1;
193 from_class_rate number := 1;
194 msgbuf varchar2(200);
195 inv_item_flag varchar2(1);
196
197 begin
198
199 /*
200 ** Conversion between between two UOMS.
201 **
202 ** 1. The conversion always starts from the conversion defined, if exists,
203 ** for an specified item.
204 ** 2. If the conversion id not defined for that specific item, then the
205 ** standard conversion, which is defined for all items, is used.
206 ** 3. When the conversion involves two different classes, then
207 ** interclass conversion is activated.
208 */
209
210
211 /*
212 ** If from and to units are the same, conversion rate is 1.
213 ** Go immediately to the end of the procedure to exit.
214 */
215 x_progress := '010';
216
217 if (from_unit = to_unit) then
218
219 uom_rate := 1;
220 return;
221
222 end if;
223
224
225 IF g_uom_conversion_table.EXISTS(from_unit || '-' || to_unit || '-' ||to_char(nvl(item_id,0))) THEN
226 uom_rate := g_uom_conversion_table(from_unit || '-' || to_unit || '-' ||to_char(nvl(item_id,0)));
227 RETURN;
228 END IF;
229
230
231 /*
232 ** Get item specific or standard conversions
233 */
234 x_progress := '020';
235
236 open standard_conversions;
237
238 std_index := 0;
239
240 loop
241
242 x_progress := '030';
243
244 fetch standard_conversions into std_rec;
245 exit when standard_conversions%notfound;
246
247 std_index := std_index + 1;
248
249 from_rate_tab(std_index) := std_rec.std_from_rate;
250 from_class_tab(std_index) := std_rec.std_from_class;
251 to_rate_tab(std_index) := std_rec.std_to_rate;
252 to_class_tab(std_index) := std_rec.std_to_class;
253
254 end loop;
255
256 close standard_conversions;
257
258 /*
259 **
260 */
261 x_progress := '040';
262
263 if (std_index = 0) then
264
265 /*
266 ** No conversions defined
267 */
268
269 msgbuf := msgbuf||'Invalid standard conversion : ';
270 msgbuf := msgbuf||'From unit: '||from_unit||' ';
271 msgbuf := msgbuf||'To unit: '||to_unit||' ';
272 raise invalid_conversion;
273
274 else
275
276 /*
277 ** Conversions are ordered. Item specific conversions will be
278 ** returned first.
279 */
280
281 from_class := from_class_tab(1);
282 to_class := to_class_tab(1);
283 from_rate := from_rate_tab(1);
284 to_rate := to_rate_tab(1);
285
286 end if;
287
288 x_progress := '050';
289
290 if (from_class <> to_class) then
291
292 /*
293 ** Load interclass conversion tables
294 ** If two rows are returned, it implies that there is no direct
295 ** conversion between them.
296 ** If one row is returned, then it may imply that there is a direct
297 ** conversion between them or one class is not defined in the
298 ** class conversion table.
299 */
300
301 class_index := 0;
302
303 if (item_id is null or item_id = 0) then
304 inv_item_flag := 'N';
305 else
306 inv_item_flag := 'Y';
307 end if;
308
309 open interclass_conversions(inv_item_flag);
310
311 loop
312
313 x_progress := '060';
314
315 fetch interclass_conversions into class_rec;
316 exit when interclass_conversions%notfound;
317
318 class_index := class_index + 1;
319
320 to_class_flag_tab(class_index) := class_rec.to_flag;
321 from_class_flag_tab(class_index) := class_rec.from_flag;
322 interclass_rate_tab(class_index) := class_rec.rate;
323
324 end loop;
325
326 close interclass_conversions;
327
328 x_progress := '070';
329
330 if (class_index = 2) then
331
332 /* Start Bug 3654053 : We should error out for Expense Items if the UOM
333 Interclass Conversions are not defined between the two UOMs. Added the
334 following if condition. If the inv_item_flag = N then we raise an
335 exception.
336 */
337
338 if inv_item_flag = 'Y' then
339
340 if (to_class_flag_tab(1) = 1) then
341
342 to_class_rate := interclass_rate_tab(1);
343 from_class_rate := interclass_rate_tab(2);
344
345 else
346
347 to_class_rate := interclass_rate_tab(2);
348 from_class_rate := interclass_rate_tab(1);
349
350 end if;
351
352 /* Bug 3385209 start
353 ** Added the following statement to calculate the interclass_rate between the
354 ** Source Doc UOM and the Transaction UOM when there is no direct conversion
355 ** defined between them.*/
356
357 interclass_rate := from_class_rate/to_class_rate;
358
359 /* Bug 3385209 End */
360
361 else
362
363 /*
364 ** No interclass conversion is defined for Expense Items
365 */
366
367 msgbuf := msgbuf||'Invalid Interclass conversion : ';
368 msgbuf := msgbuf||'From unit: '||from_unit||' ';
369 msgbuf := msgbuf||'To unit: '||to_unit||' ';
370 raise invalid_conversion;
371
372 end if;
373
374 /* End Bug# 3654053 */
375
376 elsif ((class_index = 1) and
377 (to_class_flag_tab(1) = from_class_flag_tab(1) )) then
378
379
380 if (to_class_flag_tab(1) = 1) then
381
382 to_class_rate := interclass_rate_tab(1);
383 from_class_rate := 1;
384
385 else
386
387 to_class_rate := 1;
388 from_class_rate := interclass_rate_tab(1);
389
390 end if;
391
392 x_progress := '080';
393
394 interclass_rate := from_class_rate/to_class_rate;
395
396 else
397
398 /*
399 ** No interclass conversion is defined
400 */
401
402 msgbuf := msgbuf||'Invalid Interclass conversion : ';
403 msgbuf := msgbuf||'From unit: '||from_unit||' ';
404 msgbuf := msgbuf||'To unit: '||to_unit||' ';
405 raise invalid_conversion;
406
407
408 end if;
409
410
411 end if;
412
413
414 /*
415 ** conversion rates are defaulted to '1' at the start of the procedure
416 ** so seperate calculations are not required for standard/interclass
417 ** conversions
418 */
419 x_progress := '090';
420
421 uom_rate := (from_rate * interclass_rate) / to_rate;
422
423 g_uom_conversion_table(from_unit || '-' || to_unit || '-' ||to_char(nvl(item_id,0))) := uom_rate;
424
425 /*
426 ** Put a label and a null statement over here so that you can
427 ** the goto statements can branch here.
428 */
429
430 <<procedure_end>>
431
432 null;
433
434 exception
435
436 WHEN OTHERS THEN
437 po_message_s.sql_error('po_uom_conversion', x_progress, sqlcode);
438 RAISE;
439
440 end po_uom_conversion;
441
442 function po_uom_convert ( from_unit in varchar2,to_unit in varchar2,
443 item_id in number ) return number is
444
445 /*
446 ** declare variables that are passed to po_uom_conversion
447 */
448
449 uom_rate number;
450 msgbuf varchar2(200);
451 x_progress VARCHAR2(3) := NULL;
452
453 begin
454
455 /* Call the po_uom_conversion procedure to calculate the uom_rate
456 ** and return.
457 ** This function previously calculated the conversion rate in the
458 ** body of the function itself. This was replaced by the present
459 ** procedure call because of a PL/SQL bug which caused memory leaks
460 ** while using tables in functions. Refer to bug 191321 for details.
461 */
462
463 /* Bug 5218352: Call po_uom_conversion only if from and to UOMs are
464 ** different.
465 */
466
467 IF from_unit <> to_unit THEN
468 po_uom_s.po_uom_conversion(from_unit, to_unit, item_id, uom_rate);
469 ELSE
470 uom_rate := 1;
471 END IF;
472
473 return uom_rate;
474
475 exception
476
477 WHEN OTHERS THEN
478 po_message_s.sql_error('po_uom_convert', x_progress, sqlcode);
479 RAISE;
480
481 end po_uom_convert;
482
483 /*
484 ** Function GET_PRIMARY_UOM
485 **
486 ** function returns the primary UOM based on item_id/organization
487 ** for both pre-defined and one-time items
488 */
489
490 function get_primary_uom ( item_id in number, org_id in number,
491 current_unit_of_measure in varchar2 ) return varchar2 is
492
493 primary_unit varchar2(25);
494 msgbuf varchar2(200);
495 x_progress VARCHAR2(3) := NULL;
496
497 begin
498
499 msgbuf := '';
500 primary_unit := '';
501
502
503 if (item_id is null) then
504
505 /*
506 ** for a one-time item, the primary uom is the
507 ** base uom for the item's current uom class
508 */
509
510 begin
511
512 select buom.unit_of_measure
513 into primary_unit
514 from mtl_units_of_measure cuom,
515 mtl_units_of_measure buom
516 where cuom.unit_of_measure = current_unit_of_measure
517 and cuom.uom_class = buom.uom_class
518 and buom.base_uom_flag = 'Y';
519
520 exception
521
522 when others then
523 msgbuf := msgbuf||'Statement: 001: ';
524 msgbuf := msgbuf||'Current unit: ';
525 msgbuf := msgbuf||current_unit_of_measure||' ';
526 raise;
527
528 end;
529
530 else
531
532 /*
533 ** for pre-defined items, get the primary uom
534 ** from mtl_system_items
535 */
536
537 begin
538
542 where msi.inventory_item_id = item_id
539 select msi.primary_unit_of_measure
540 into primary_unit
541 from mtl_system_items msi
543 and msi.organization_id = org_id;
544
545 exception
546
547 when others then
548 msgbuf := msgbuf||'Statement: 002: ';
549 msgbuf := msgbuf||'Item ID: '||item_id||' ';
550 msgbuf := msgbuf||'Organization ID: '||org_id||' ';
551 raise;
552
553 end;
554
555 end if;
556
557 return primary_unit;
558
559 exception
560
561 WHEN OTHERS THEN
562 po_message_s.sql_error('get_primary_uom', x_progress, sqlcode);
563 RAISE;
564
565 end get_primary_uom;
566
567 /*========================================================================
568
569 FUNCTION : po_uom_convert_p() -dreddy
570
571 Created a function po_uom_convert_p which is pure function to be used in
572 the where and select clauses of a SQL stmt.bug 1365577
573 ******************************************************
574 So, any change in the po_uom_convertion proc in rvpo02
575 should be implemented in this new function.
576 ******************************************************
577 ========================================================================*/
578 function po_uom_convert_p ( from_unit varchar2, to_unit
579 varchar2, item_id number ) return number as
580
581 /*
582 ** declare variables that are referenced in the cursor definitions
583 */
584
585 from_class varchar2(10);
586 to_class varchar2(10);
587
588
589 cursor standard_conversions is
590 select t.conversion_rate std_to_rate,
591 t.uom_class std_to_class,
592 f.conversion_rate std_from_rate,
593 f.uom_class std_from_class
594 from mtl_uom_conversions t,
595 mtl_uom_conversions f
596 where t.inventory_item_id in (item_id, 0)
597 and t.unit_of_measure = to_unit
598 and nvl(t.disable_date, trunc(sysdate) + 1) > trunc(sysdate)
599 and f.inventory_item_id in (item_id, 0)
600 and f.unit_of_measure = from_unit
601 and nvl(f.disable_date, trunc(sysdate) + 1) > trunc(sysdate)
602 order by t.inventory_item_id desc,
603 f.inventory_item_id desc;
604
605 std_rec standard_conversions%rowtype;
606
607
608 cursor interclass_conversions is
609 select decode(to_uom_class, to_class, 1, 2) to_flag,
610 decode(from_uom_class, from_class, 1, to_class, 2, 0) from_flag,
611 conversion_rate rate
612 from mtl_uom_class_conversions
613 where inventory_item_id = item_id
614 and to_uom_class in (from_class, to_class)
615 and nvl(disable_date, trunc(sysdate) + 1) > trunc(sysdate);
616
617 class_rec interclass_conversions%rowtype;
618
619
620 invalid_conversion exception;
621
622 type conv_tab is table of number
623 index by binary_integer;
624
625 type class_tab is table of varchar2(10)
626 index by binary_integer;
627
628 interclass_rate_tab conv_tab;
629 from_class_flag_tab conv_tab;
630 to_class_flag_tab conv_tab;
631 from_rate_tab conv_tab;
632 to_rate_tab conv_tab;
633 from_class_tab class_tab;
634 to_class_tab class_tab;
635
636 std_index number;
637 class_index number;
638
639 uom_rate number := 1;
640 from_rate number := 1;
641 to_rate number := 1;
642 interclass_rate number := 1;
643 to_class_rate number := 1;
644 from_class_rate number := 1;
645 msgbuf varchar2(200);
646
647 begin
648
649 /*
650 ** Conversion between between two UOMS.
651 **
652 ** 1. The conversion always starts from the conversion defined, if exists,
653 ** for an specified item.
654 ** 2. If the conversion id not defined for that specific item, then the
655 ** standard conversion, which is defined for all items, is used.
656 ** 3. When the conversion involves two different classes, then
657 ** interclass conversion is activated.
658 */
659
660
661 /*
662 ** If from and to units are the same, conversion rate is 1.
663 ** Go immediately to the end of the procedure to exit.
664 */
665
666 if (from_unit = to_unit) then
667
668 uom_rate := 1;
669 goto procedure_end;
670
671 end if;
672
673
674 /*
675 ** Get item specific or standard conversions
676 */
677
678 open standard_conversions;
679
680 std_index := 0;
681
682 loop
683
684 fetch standard_conversions into std_rec;
685 exit when standard_conversions%notfound;
689 from_rate_tab(std_index) := std_rec.std_from_rate;
686
687 std_index := std_index + 1;
688
690 from_class_tab(std_index) := std_rec.std_from_class;
691 to_rate_tab(std_index) := std_rec.std_to_rate;
692 to_class_tab(std_index) := std_rec.std_to_class;
693
694 end loop;
695
696 close standard_conversions;
697
698 /*
699 **
700 */
701
702 if (std_index = 0) then
703
704 /*
705 ** No conversions defined
706 */
707
708 msgbuf := msgbuf||'Invalid standard conversion : ';
709 msgbuf := msgbuf||'From unit: '||from_unit||' ';
710 msgbuf := msgbuf||'To unit: '||to_unit||' ';
711 return -999 ;
712
713 else
714
715 /*
716 ** Conversions are ordered. Item specific conversions will be
717 ** returned first.
718 */
719
720 from_class := from_class_tab(1);
721 to_class := to_class_tab(1);
722 from_rate := from_rate_tab(1);
723 to_rate := to_rate_tab(1);
724
725 end if;
726
727
728 if (from_class <> to_class) then
729
730 /*
731 ** Load interclass conversion tables
732 ** If two rows are returned, it implies that there is no direct
733 ** conversion between them.
734 ** If one row is returned, then it may imply that there is a direct
735 ** conversion between them or one class is not defined in the
736 ** class conversion table.
737 */
738
739 class_index := 0;
740
741 open interclass_conversions;
742
743 loop
744
745 fetch interclass_conversions into class_rec;
746 exit when interclass_conversions%notfound;
747
748 class_index := class_index + 1;
749
750 to_class_flag_tab(class_index) := class_rec.to_flag;
751 from_class_flag_tab(class_index) := class_rec.from_flag;
752 interclass_rate_tab(class_index) := class_rec.rate;
753
754 end loop;
755
756 close interclass_conversions;
757
758 if (class_index = 2) then
759
760 if (to_class_flag_tab(1) = 1) then
761
762 to_class_rate := interclass_rate_tab(1);
763 from_class_rate := interclass_rate_tab(2);
764
765 else
766
767 to_class_rate := interclass_rate_tab(2);
768 from_class_rate := interclass_rate_tab(1);
769
770 end if;
771
772 elsif ((class_index = 1) and
773 (to_class_flag_tab(1) = from_class_flag_tab(1) )) then
774
775
776 if (to_class_flag_tab(1) = 1) then
777
778 to_class_rate := interclass_rate_tab(1);
779 from_class_rate := 1;
780
781 else
782
783 to_class_rate := 1;
784 from_class_rate := interclass_rate_tab(1);
785
786 end if;
787
788
789 interclass_rate := from_class_rate/to_class_rate;
790
791 else
792
793 /*
794 ** No interclass conversion is defined
795 */
796
797 msgbuf := msgbuf||'Invalid Interclass conversion : ';
798 msgbuf := msgbuf||'From unit: '||from_unit||' ';
799 msgbuf := msgbuf||'To unit: '||to_unit||' ';
800 return -999 ;
801
802
803 end if;
804
805
806 end if;
807
808
809 /*
810 ** conversion rates are defaulted to '1' at the start of the procedure
811 ** so seperate calculations are not required for standard/interclass
812 ** conversions
813 */
814
815 uom_rate := (from_rate * interclass_rate) / to_rate;
816
817 /*
818 ** Put a label and a null statement over here so that you can
819 ** the goto statements can branch here.
820 */
821
822 <<procedure_end>>
823
824 return ROUND(NVL(uom_rate, 1), 5) ;
825
826 end po_uom_convert_p;
827
828
829 /*===========================================================================
830 PROCEDURE NAME: get_secondary_uom()
831
832 DESCRIPTION:
833 This function returns the primary UOM based on item_id/organization
834 for both pre-defined and one-time items
835
836 USAGE:
837 uom := po_uom_s.get_secondary_uom ( item_id number, org_id number,
838 current_sec_unit_of_measure varchar2 )
839
840 PARAMETERS:
841 item_id IN number - item id (null for one time items)
842 org_id IN number - org id
843 current_sec_unit_of_measure IN VARCHAR2 - currently defined uom on trx.
844
845 RETURNS:
846
847 secondary_uom - VARCHAR2 - Secondary UOM for given item and org
848
849 DESIGN REFERENCES: Generic
850
851 ALGORITHM:
852
853 NOTES:
854
855 OPEN ISSUES:
856
857 CLOSED ISSUES:
858
859 CHANGE HISTORY:
860 09-SEP-05 Preetam Bamb Created
861 ===========================================================================*/
862 procedure get_secondary_uom (p_item_id in number,
863 p_org_id in number,
864 x_secondary_uom_code out NOCOPY varchar2,
865 x_secondary_unit_of_measure out NOCOPY varchar2) is
866
867 msgbuf varchar2(200);
868 x_progress varchar2(3) := NULL;
869 l_tracking_quantity_ind mtl_system_items.tracking_quantity_ind%TYPE:= NULL;
870
871 begin
872 msgbuf := '';
873 x_progress := '001';
874
875 if (p_item_id is not null) then
876 begin
877 select msi.tracking_quantity_ind,msi.secondary_uom_code, muom.unit_of_measure
878 into l_tracking_quantity_ind, x_secondary_uom_code,x_secondary_unit_of_measure
879 from mtl_system_items msi, mtl_units_of_measure muom
880 where msi.inventory_item_id = p_item_id
881 and msi.organization_id = p_org_id
882 and msi.secondary_uom_code = muom.uom_code(+);
883
884 x_progress := '002';
885 if l_tracking_quantity_ind <> g_chktype_TRACKING_QTY_IND_S then
886 x_secondary_uom_code := NULL;
887 x_secondary_unit_of_measure := NULL;
888 end if;
889
890 exception when NO_DATA_FOUND then
891 x_secondary_uom_code := NULL;
892 x_secondary_unit_of_measure := NULL;
893 end;
894 else
895 x_secondary_uom_code := NULL;
896 x_secondary_unit_of_measure := NULL;
897 end if;
898
899 x_progress := '003';
900 exception
901
902 WHEN OTHERS THEN
903 po_message_s.sql_error('get_secondary_uom', x_progress, sqlcode);
904 RAISE;
905
906 end;
907 /*===========================================================================
908 PROCEDURE NAME: get_unit_of_measure()
909
910 DESCRIPTION:
911 This function returns the unit of measure for the passed uom code
912
913 USAGE:
914 uom := po_uom_s.get_unit_of_measure(
915 p_uom_code in varchar2,
916 x_unit_of_measure out NOCOPY varchar2);
917
918 PARAMETERS:
919 x_uom_code IN VARCHAR2 - items secondary uom code.
920 x_unit_of_measure OUT VARCHAR2 - items secondary unit of meas
921
922 DESIGN REFERENCES: Generic
923
924 ALGORITHM:
925
926 NOTES:
927
928 OPEN ISSUES:
929
930 CLOSED ISSUES:
931
932 CHANGE HISTORY:
933 09-SEP-05 Preetam Bamb Created
934 ===========================================================================*/
935 procedure get_unit_of_measure(
936 p_uom_code in varchar2,
937 x_unit_of_measure out NOCOPY varchar2)
938 IS
939 x_progress varchar2(3) := NULL;
940 BEGIN
941
942 x_progress := '001';
943
944 if p_uom_code is NOT NULL THEN
945 begin
946 select unit_of_measure
947 into x_unit_of_measure
948 from mtl_units_of_measure
949 where uom_code = p_uom_code;
950
951 x_progress := '002';
952 exception when no_data_found then
953 x_unit_of_measure := NULL;
954 end;
955 end if;
956
957 x_progress := '003';
958 exception
959
960 WHEN OTHERS THEN
961 po_message_s.sql_error('get_secondary_uom', x_progress, sqlcode);
962 RAISE;
963 END;
964
965 END PO_UOM_S;
[Home] [Help]