1 PACKAGE BODY poa_edw_util AS
2 /* $Header: poautilb.pls 120.0 2005/06/01 16:27:46 appldev noship $ */
3
4 -- ========================================================================
5 -- convert_uom
6 --
7 -- Cloned from inv_convert.inv_um_conversion
8 -- ========================================================================
9 PROCEDURE convert_uom (
10 from_uom_code VARCHAR2,
11 to_uom_code VARCHAR2,
12 item_id NUMBER,
13 uom_rate OUT NOCOPY NUMBER)
14 IS
15
16 /*
17 ** declare variables that are referenced in the cursor definitions
18 */
19
20 from_class varchar2(10);
21 to_class varchar2(10);
22
23
24 cursor standard_conversions is
25 select t.conversion_rate std_to_rate,
26 t.uom_class std_to_class,
27 f.conversion_rate std_from_rate,
28 f.uom_class std_from_class
29 from mtl_uom_conversions t,
30 mtl_uom_conversions f
31 where t.inventory_item_id in (item_id, 0)
32 and t.uom_code = to_uom_code
33 and nvl(t.disable_date, trunc(sysdate) + 1) > trunc(sysdate)
34 and f.inventory_item_id in (item_id, 0)
35 and f.uom_code = from_uom_code
36 and nvl(f.disable_date, trunc(sysdate) + 1) > trunc(sysdate)
37 order by t.inventory_item_id desc,
38 f.inventory_item_id desc;
39
40 std_rec standard_conversions%rowtype;
41
42
43 cursor interclass_conversions is
44 select decode(to_uom_class, to_class, 1, 2) to_flag,
45 decode(from_uom_class, from_class, 1, to_class, 2, 0) from_flag,
46 conversion_rate rate
47 from mtl_uom_class_conversions
48 where inventory_item_id = item_id
49 and to_uom_class in (from_class, to_class)
50 and nvl(disable_date, trunc(sysdate) + 1) > trunc(sysdate);
51
52 class_rec interclass_conversions%rowtype;
53
54
55 invalid_conversion exception;
56
57 type conv_tab is table of number
58 index by binary_integer;
59
60 type class_tab is table of varchar2(10)
61 index by binary_integer;
62
63 interclass_rate_tab conv_tab;
64 from_class_flag_tab conv_tab;
65 to_class_flag_tab conv_tab;
66 from_rate_tab conv_tab;
67 to_rate_tab conv_tab;
68 from_class_tab class_tab;
69 to_class_tab class_tab;
70
71 std_index number;
72 class_index number;
73
74 from_rate number := 1;
75 to_rate number := 1;
76 interclass_rate number := 1;
77 to_class_rate number := 1;
78 from_class_rate number := 1;
79 msgbuf varchar2(200);
80
81 BEGIN
82
83 /*
84 ** Conversion between between two UOMS.
85 **
86 ** 1. The conversion always starts from the conversion defined, if exists,
87 ** for an specified item.
88 ** 2. If the conversion id not defined for that specific item, then the
89 ** standard conversion, which is defined for all items, is used.
90 ** 3. When the conversion involves two different classes, then
91 ** interclass conversion is activated.
92 */
93
94
95 /*
96 ** If from and to units are the same, conversion rate is 1.
97 ** Go immediately to the end of the procedure to exit.
98 */
99
100 if (from_uom_code = to_uom_code) then
101
102 uom_rate := 1;
103 goto procedure_end;
104
105 end if;
106
107 /*
108 ** Get item specific or standard conversions
109 */
110
111 open standard_conversions;
112
113 std_index := 0;
114
115 loop
116
117 fetch standard_conversions into std_rec;
118 exit when standard_conversions%notfound;
119
120 std_index := std_index + 1;
121
122 from_rate_tab(std_index) := std_rec.std_from_rate;
123 from_class_tab(std_index) := std_rec.std_from_class;
124 to_rate_tab(std_index) := std_rec.std_to_rate;
125 to_class_tab(std_index) := std_rec.std_to_class;
126
127 end loop;
128
129 close standard_conversions;
130
131 /*
132 **
133 */
134
135 if (std_index = 0) then
136
137 /*
138 ** No conversions defined
139 */
140
141 msgbuf := msgbuf||'Invalid standard conversion : ';
142 msgbuf := msgbuf||'From UOM code: '||from_uom_code||' ';
143 msgbuf := msgbuf||'To UOM code: '||to_uom_code||' ';
144 raise invalid_conversion;
145
146 else
147
148 /*
149 ** Conversions are ordered. Item specific conversions will be
150 ** returned first.
151 */
152
153 from_class := from_class_tab(1);
154 to_class := to_class_tab(1);
155 from_rate := from_rate_tab(1);
156 to_rate := to_rate_tab(1);
157
158 end if;
159
160 if (from_class <> to_class) then
161
162 /*
163 ** Load interclass conversion tables
164 ** If two rows are returned, it implies that there is no direct
165 ** conversion between them.
166 ** If one row is returned, then it may imply that there is a direct
167 ** conversion between them or one class is not defined in the
168 ** class conversion table.
169 */
170
171 class_index := 0;
172
173 open interclass_conversions;
174
175 loop
176
177 fetch interclass_conversions into class_rec;
178 exit when interclass_conversions%notfound;
179
180 class_index := class_index + 1;
181
182 to_class_flag_tab(class_index) := class_rec.to_flag;
183 from_class_flag_tab(class_index) := class_rec.from_flag;
184 interclass_rate_tab(class_index) := class_rec.rate;
185
186 end loop;
187
188 close interclass_conversions;
189
190 if (class_index = 2) then
191
192 if (to_class_flag_tab(1) = 1) then
193
194 to_class_rate := interclass_rate_tab(1);
195 from_class_rate := interclass_rate_tab(2);
196
197 else
198
199 to_class_rate := interclass_rate_tab(2);
200 from_class_rate := interclass_rate_tab(1);
201
202 end if;
203
204 elsif ((class_index = 1) and
205 (to_class_flag_tab(1) = from_class_flag_tab(1) )) then
206
207 if (to_class_flag_tab(1) = 1) then
208
209 to_class_rate := interclass_rate_tab(1);
210 from_class_rate := 1;
211
212 else
213
214 to_class_rate := 1;
215 from_class_rate := interclass_rate_tab(1);
216
217 end if;
218
219 interclass_rate := from_class_rate/to_class_rate;
220
221 else
222
223 /*
224 ** No interclass conversion is defined
225 */
226
227 msgbuf := msgbuf||'Invalid Interclass conversion : ';
228 msgbuf := msgbuf||'From UOM code: '||from_uom_code||' ';
229 msgbuf := msgbuf||'To UOM code: '||to_uom_code||' ';
230 raise invalid_conversion;
231
232 end if;
233
234 end if;
235
236 /*
237 ** conversion rates are defaulted to '1' at the start of the procedure
238 ** so seperate calculations are not required for standard/interclass
239 ** conversions
240 */
241
242 uom_rate := (from_rate * interclass_rate) / to_rate;
243
244 /*
245 ** Put a label and a null statement over here so that you can
246 ** the goto statements can branch here.
247 */
248
249 <<procedure_end>>
250
251 null;
252
253 exception
254 when others then
255 uom_rate := -99999;
256
257 END convert_uom;
258
259
260 -- ========================================================================
261 -- get_uom_rate
262 --
263 -- Cloned from inv_convert.inv_um_convert
264 -- ========================================================================
265 FUNCTION get_uom_rate(
266 item_id NUMBER,
267 precision NUMBER,
268 from_quantity NUMBER,
269 from_uom_code VARCHAR2,
270 to_uom_code VARCHAR2,
271 from_uom_name VARCHAR2,
272 to_uom_name VARCHAR2)
273 RETURN NUMBER
274 IS
275
276 /*
277 ** declare variables that are passed to inv_uom_conversion
278 */
279
280 uom_rate number;
281 msgbuf varchar2(200);
282 from_qty_num number;
283 fm_unt varchar2(3);
284 to_unt varchar2(3);
285 eff_precision number;
286
287 BEGIN
288
289 /* Call the inv_uom_conversion procedure to calculate the uom_rate
290 ** and return. If from_quantity is not null, the function
291 ** assumes that to_quantity is the desired result, and this is
292 ** what is returned. Precision is defaulted to 2 decimals, unless
293 ** a different value is provided by caller of function.
294 ** This function previously calculated the conversion rate in the
295 ** body of the function itself. This was replaced by the present
296 ** procedure call because of a PL/SQL bug which caused memory leaks
297 ** while using tables in functions. Refer to bug 191321 for details.
298 */
299
300 if ( from_uom_code IS NULL and to_uom_code IS NULL ) then
301 SELECT uom_code
302 INTO fm_unt
303 FROM mtl_units_of_measure
304 WHERE unit_of_measure = from_uom_name;
305
306 SELECT uom_code
307 INTO to_unt
308 FROM mtl_units_of_measure
309 WHERE unit_of_measure = to_uom_name;
310 else
311 fm_unt := from_uom_code;
312 to_unt := to_uom_code;
313 end if;
314
315 convert_uom(fm_unt, to_unt, item_id, uom_rate);
316
317 if ( uom_rate = -99999 ) then
318 return(-99999);
319 end if;
320
321 if ( from_quantity IS NOT NULL ) then
322 uom_rate := from_quantity * uom_rate;
323 end if;
324
325 /*
326 ** Default precision for inventory is 6 decimals
327 */
328 if (precision IS NULL) then
329 eff_precision := 6 ;
330 else
331 eff_precision := precision ;
332 end if;
333
334 uom_rate := round(uom_rate, eff_precision);
335
336 RETURN uom_rate;
337
338 EXCEPTION
339 when others then
340 return (-99999);
341
342 END get_uom_rate;
343
344 END poa_edw_util;
[Home] [Help]