1 package body poa_dbi_uom_pkg as
2 /* $Header: poadbiuomb.pls 120.2 2008/01/17 10:53:18 nchava ship $ */
3
4
5 function convert_to_item_base_uom(p_item_id number
6 ,p_org_id number
7 ,p_from_unit_of_measure varchar2
8 ,p_item_primary_uom_code VARCHAR2)
9 return number is
10
11 begin
12
13 if(p_item_primary_uom_code is null) then
14
15 if (p_from_unit_of_measure = get_item_base_uom(p_item_id, p_org_id, p_from_unit_of_measure)) then
16 return 1;
17 end if;
18
19 end if;
20
21 RETURN convert_to_item_base_uom(
22 p_item_id => p_item_id
23 , p_org_id => p_org_id
24 , p_from_unit_of_measure => p_from_unit_of_measure
25 , p_from_uom_code => NULL
26 , p_item_primary_uom_code => p_item_primary_uom_code) ;
27 end convert_to_item_base_uom;
28
29
30
31 /* Overloaded function: convert_to_item_base_uom
32 Additional parameters: p_from_uom_code
33 */
34 function convert_to_item_base_uom
35 ( p_item_id NUMBER
36 , p_org_id NUMBER
37 , p_from_unit_of_measure VARCHAR2
38 , p_from_uom_code VARCHAR2
39 , p_item_primary_uom_code VARCHAR2
40 ) RETURN NUMBER IS
41
42 l_line_uom_class varchar2(10);
43 l_line_base_uom varchar2(1);
44 l_item_uom_class varchar2(10) := p_item_primary_uom_code;
45 l_item_base_uom varchar2(1);
46 l_item_primary_uom_code varchar2(3);
47
48 -- standard and intra class conversion using unit_of_measure
49 cursor l_standard_conversion(l_item_id number
50 ,l_uom_class varchar2
51 ,l_unit_of_measure varchar2) is
52 select conversion_rate
53 from mtl_uom_conversions
54 where uom_class = l_uom_class
55 and unit_of_measure = l_unit_of_measure
56 and INVENTORY_ITEM_ID in (l_item_id,0)
57 -- to take care of item specific vs standard conversion
58 order by INVENTORY_ITEM_ID desc;
59
60 l_standard_conversion_rec l_standard_conversion%rowtype;
61
62
63 -- standard and intra class conversion using uom_code
64 cursor l_standard_conversion2(l_item_id NUMBER
65 ,l_uom_class VARCHAR2
66 ,l_uom_code VARCHAR2) is
67 SELECT conversion_rate
68 FROM mtl_uom_conversions
69 WHERE uom_class = l_uom_class
70 AND uom_code = l_uom_code
71 AND INVENTORY_ITEM_ID in (l_item_id,0)
72 -- to take care of item specific vs standard conversion
73 ORDER BY INVENTORY_ITEM_ID desc ;
74
75 l_standard_conversion2_rec l_standard_conversion2%rowtype;
76
77 cursor l_class_conversion(l_item_id number
78 ,l_from_uom_class varchar2
79 ,l_to_uom_class varchar2) is
80 select conversion_rate,from_uom_class
81 -- to take care of two way conversions
82 from MTL_UOM_CLASS_CONVERSIONS
83 where inventory_item_id = l_item_id
84 and FROM_UOM_CLASS in (l_from_uom_class,l_to_uom_class)
85 and TO_UOM_CLASS in (l_from_uom_class,l_to_uom_class)
86 order by decode(FROM_UOM_CLASS,l_from_uom_class,1,2);
87
88 l_class_conversion_rec l_class_conversion%rowtype;
89
90 l_from_uom_class varchar2(10);
91
92 l_conversion NUMBER;
93 l_item_conversion NUMBER;
94 l_class_conv NUMBER;
95 begin
96 -- Given a PO Line, get the uom class and if it is the base uom
97
98 if (p_item_id is null) then return 1; end if;
99
100 begin
101 IF (p_from_uom_code IS NULL) THEN
102 -- Get uom_class and base_uom_flag using unit_of_measure
103 select uom.UOM_CLASS,uom.BASE_UOM_FLAG
104 into l_line_uom_class,l_line_base_uom
105 from mtl_units_of_measure uom
106 where uom.UNIT_OF_MEASURE = p_from_unit_of_measure;
107 ELSE
108 -- Get uom_class and base_uom_flag using uom_code
109 SELECT uom.uom_class
110 , uom.base_uom_flag
111 INTO l_line_uom_class
112 , l_line_base_uom
113 FROM mtl_units_of_measure uom
114 WHERE uom.uom_code = p_from_uom_code ;
115 END IF ;
116 exception
117 when no_data_found then
118 return -1;
119 end;
120
121 -- Given a non-one-time item PO Line, get the uom class and if it is
122 -- the base uom for the primary uom of the given item and the
123 -- corresponding fsp.inventory org
124 begin
125 if(p_item_primary_uom_code is null) then
126 select uom.UOM_CLASS, uom.base_uom_flag, item.primary_uom_code
127 into l_item_uom_class, l_item_base_uom, l_item_primary_uom_code
128 from mtl_units_of_measure uom
129 ,mtl_system_items item
130 where uom.UOM_CODE = item.PRIMARY_UOM_CODE
131 and item.inventory_item_id = p_item_id
132 and item.organization_id = p_org_id;
133 else
134 select uom_class, base_uom_flag
135 into l_item_uom_class, l_item_base_uom
136 from mtl_units_of_measure
137 where uom_code = p_item_primary_uom_code;
138 end if;
139 exception
140 when no_data_found then
141 return -2;
142 end;
143
144 -- to convert to the primary uom of the item if the primary UOM is not the base UOM of that UOM class
145 if(l_item_base_uom = 'Y') then
146 l_item_conversion := 1.0;
147 else
148 open l_standard_conversion2(0, l_item_uom_class, nvl(p_item_primary_uom_code, l_item_primary_uom_code));
149 fetch l_standard_conversion2 INTO l_standard_conversion2_rec ;
150 IF l_standard_conversion2%NOTFOUND then
151 CLOSE l_standard_conversion2 ;
152 RETURN -6 ;
153 ELSE
154 CLOSE l_standard_conversion2 ;
155 l_item_conversion := 1.0/l_standard_conversion2_rec.conversion_rate ;
156 end if ;
157 end if;
158
159 -- same class
160 if(l_line_uom_class = l_item_uom_class) then
161 if(l_line_base_uom = 'Y') then
162 return 1.0 * l_item_conversion; -- no further conversion required
163 else
164 IF p_from_uom_code IS NULL THEN
165 open l_standard_conversion(p_item_id,l_line_uom_class,p_from_unit_of_measure);
166 fetch l_standard_conversion into l_standard_conversion_rec;
167 if l_standard_conversion%NOTFOUND then
168 close l_standard_conversion;
169 return -3;
170 else
171 close l_standard_conversion;
172 return l_standard_conversion_rec.conversion_rate * l_item_conversion;
173 end if;
174 ELSE
175 -- get conversion based on uom_code
176 OPEN l_standard_conversion2(p_item_id,l_line_uom_class,p_from_uom_code) ;
177 FETCH l_standard_conversion2 INTO l_standard_conversion2_rec ;
178 IF l_standard_conversion2%NOTFOUND then
179 CLOSE l_standard_conversion2 ;
180 RETURN -3 ;
181 ELSE
182 CLOSE l_standard_conversion2 ;
183 RETURN l_standard_conversion2_rec.conversion_rate * l_item_conversion;
184 end if ;
185 end if ;
186 end if;
187 else -- inter class
188 -- lets first get conversion factor for the line uom to line uom class's base uom
189 if(l_line_base_uom = 'Y') then
190 l_conversion := 1.0;
191 else
192 IF p_from_uom_code IS NULL THEN
193 open l_standard_conversion(p_item_id,l_line_uom_class,p_from_unit_of_measure);
194 fetch l_standard_conversion into l_standard_conversion_rec;
195 if l_standard_conversion%NOTFOUND then
196 close l_standard_conversion;
197 return -4;
198 else
199 close l_standard_conversion;
200 l_conversion := l_standard_conversion_rec.conversion_rate;
201 end if;
202 ELSE
203 -- get conversion based on uom_code
204 OPEN l_standard_conversion2(p_item_id,l_line_uom_class,p_from_uom_code);
205 FETCH l_standard_conversion2 into l_standard_conversion2_rec;
206 IF l_standard_conversion2%NOTFOUND then
207 CLOSE l_standard_conversion2;
208 RETURN -4;
209 ELSE
210 CLOSE l_standard_conversion2;
211 l_conversion := l_standard_conversion2_rec.conversion_rate;
212 END IF;
213 END IF ;
214 end if;
215 -- There could be a single conersion or two conversions back and forth
216 -- with different values. We want to start from line uom class if possible
217 open l_class_conversion(p_item_id,l_line_uom_class,l_item_uom_class);
218 fetch l_class_conversion into l_class_conversion_rec;
219 if(l_class_conversion%NOTFOUND) then
220 close l_class_conversion;
221 return -5;
222 else
223 close l_class_conversion;
224 l_class_conv := l_class_conversion_rec.conversion_rate;
225 l_from_uom_class := l_class_conversion_rec.from_uom_class;
226 end if;
227
228 if(l_from_uom_class = l_line_uom_class) then
229 l_class_conv := 1.0/l_class_conv;
230 end if;
231
232 return l_conversion * l_class_conv * l_item_conversion;
233 end if;
234 end convert_to_item_base_uom;
235
236 /*
237 This function is called by the POD Fact Refreshing program for populating
238 the conversion rates between a Negotiation Transaction UOM to that of PO
239 transaction UOM, only when the Item is being looked into is a One Time Item.
240 */
241 FUNCTION convert_neg_to_po_uom( p_from_unit_of_measure VARCHAR2,
242 p_to_unit_of_measure VARCHAR2
243 )
244 RETURN NUMBER
245 IS
246 l_conversion_rate number;
247 BEGIN
248 l_conversion_rate := 1;
249
250 SELECT (from_uom.conversion_rate * (1/to_uom.conversion_rate)) rate into l_conversion_rate
251 FROM
252 (SELECT conversion_rate, uom_class FROM mtl_uom_conversions WHERE unit_of_measure=p_from_unit_of_measure AND inventory_item_id=0) from_uom,
253 (SELECT conversion_rate, uom_class FROM mtl_uom_conversions WHERE unit_of_measure=p_to_unit_of_measure AND inventory_item_id=0) to_uom
254 WHERE
255 from_uom.uom_class=to_uom.uom_class;
256
257 return l_conversion_rate;
258 END convert_neg_to_po_uom;
259
260
261 function get_item_base_uom(p_item_id number
262 ,p_org_id number
263 ,p_from_unit_of_measure varchar2)
264 return varchar2
265 is
266
267 l_base_uom varchar2(25);
268
269 begin
270
271 if(p_item_id is not null) then
272 select primary_unit_of_measure
273 into l_base_uom
274 from mtl_system_items
275 where inventory_item_id = p_item_id
276 and organization_id = p_org_id;
277 else
278 l_base_uom := p_from_unit_of_measure;
279 end if;
280
281 return l_base_uom;
282
283 exception
284 when others then
285 return '-1';
286
287 end;
288
289 end poa_dbi_uom_pkg;
[Home] [Help]