[Home] [Help]
70: -- leads to a cartesian product.
71: -- So, splitting the cursor into two different sqls.
72:
73: /***
74: cursor standard_conversions is
75: select t.conversion_rate std_to_rate,
76: t.uom_class std_to_class,
77: f.conversion_rate std_from_rate,
78: f.uom_class std_from_class
86: and nvl(f.disable_date, trunc(sysdate) + 1) > trunc(sysdate)
87: order by t.inventory_item_id desc,
88: f.inventory_item_id desc;
89:
90: std_rec standard_conversions%rowtype;
91:
92: *****/
93:
94: cursor from_standard_conversions is
90: std_rec standard_conversions%rowtype;
91:
92: *****/
93:
94: cursor from_standard_conversions is
95: select conversion_rate std_from_rate,
96: uom_class std_from_class
97: from mtl_uom_conversions
98: where inventory_item_id in (item_id, 0)
99: and uom_code = from_unit
100: and nvl(disable_date, trunc(sysdate) + 1) > trunc(sysdate)
101: order by inventory_item_id desc;
102:
103: from_std_rec from_standard_conversions%rowtype;
104:
105: cursor to_standard_conversions is
106: select conversion_rate std_to_rate,
107: uom_class std_to_class
101: order by inventory_item_id desc;
102:
103: from_std_rec from_standard_conversions%rowtype;
104:
105: cursor to_standard_conversions is
106: select conversion_rate std_to_rate,
107: uom_class std_to_class
108: from mtl_uom_conversions
109: where inventory_item_id in (item_id, 0)
110: and uom_code = to_unit
111: and nvl(disable_date, trunc(sysdate) + 1) > trunc(sysdate)
112: order by inventory_item_id desc;
113:
114: to_std_rec to_standard_conversions%rowtype;
115:
116:
117: cursor interclass_conversions is
118: select decode(to_uom_class, to_class, 1, 2) to_flag,
131: Cursor added for lot specific interclass./
132: ===============================================*/
133:
134: /* Fix for #7434784. Lot conversion should look into lot_conversion table and
135: also standard interclass conversion.
136:
137: e.g. User will define conversion between primary and secondary UOM for a specfic lot
138: However conversion between transaction uom and primary/secondary uom will exists only in
139: interclass conversion table.
210: **
211: ** 1. The conversion always starts from the conversion defined, if exists,
212: ** for an specified item.
213: ** 2. If the conversion id not defined for that specific item, then the
214: ** standard conversion, which is defined for all items, is used.
215: ** 3. When the conversion involves two different classes, then
216: ** interclass conversion is activated.
217: */
218:
237: p_lot_number := lot_number;
238: p_organization_id := organization_id;
239:
240: /*
241: ** Get item specific or standard conversions
242: */
243:
244: open from_standard_conversions;
245:
240: /*
241: ** Get item specific or standard conversions
242: */
243:
244: open from_standard_conversions;
245:
246: std_index := 0;
247:
248: loop
246: std_index := 0;
247:
248: loop
249:
250: fetch from_standard_conversions into from_std_rec;
251: exit when from_standard_conversions%notfound;
252:
253: std_index := std_index + 1;
254:
247:
248: loop
249:
250: fetch from_standard_conversions into from_std_rec;
251: exit when from_standard_conversions%notfound;
252:
253: std_index := std_index + 1;
254:
255: from_rate_tab(std_index) := from_std_rec.std_from_rate;
256: from_class_tab(std_index) := from_std_rec.std_from_class;
257:
258: end loop;
259:
260: close from_standard_conversions;
261:
262: if (std_index = 0) then
263:
264: /*
264: /*
265: ** No conversions defined
266: */
267:
268: msgbuf := msgbuf||'Invalid standard conversion : ';
269: msgbuf := msgbuf||'From UOM code: '||from_unit||' ';
270: msgbuf := msgbuf||'To UOM code: '||to_unit||' ';
271: raise invalid_conversion;
272:
281: from_rate := from_rate_tab(1);
282:
283: end if;
284:
285: open to_standard_conversions;
286:
287: std_index := 0;
288:
289: loop
287: std_index := 0;
288:
289: loop
290:
291: fetch to_standard_conversions into to_std_rec;
292: exit when to_standard_conversions%notfound;
293:
294: std_index := std_index + 1;
295:
288:
289: loop
290:
291: fetch to_standard_conversions into to_std_rec;
292: exit when to_standard_conversions%notfound;
293:
294: std_index := std_index + 1;
295:
296: to_rate_tab(std_index) := to_std_rec.std_to_rate;
297: to_class_tab(std_index) := to_std_rec.std_to_class;
298:
299: end loop;
300:
301: close to_standard_conversions;
302:
303: if (std_index = 0) then
304:
305: /*
305: /*
306: ** No conversions defined
307: */
308:
309: msgbuf := msgbuf||'Invalid standard conversion : ';
310: msgbuf := msgbuf||'From UOM code: '||from_unit||' ';
311: msgbuf := msgbuf||'To UOM code: '||to_unit||' ';
312: raise invalid_conversion;
313:
333: -- /*
334: -- ** No conversions defined
335: -- */
336:
337: -- msgbuf := msgbuf||'Invalid standard conversion : ';
338: -- msgbuf := msgbuf||'From UOM code: '||from_unit||' ';
339: -- msgbuf := msgbuf||'To UOM code: '||to_unit||' ';
340: -- raise invalid_conversion;
341:
471:
472:
473: /*
474: ** conversion rates are defaulted to '1' at the start of the procedure
475: ** so seperate calculations are not required for standard/interclass
476: ** conversions
477: */
478:
479: uom_rate := (from_rate * interclass_rate) / to_rate;
594: end if;
595:
596:
597: /** Default precision for inventory was 6 decimals
598: Changed the default precision to 5 since INV supports a standard
599: precision of 5 decimal places.
600: */
601: if (precision IS NULL) then
602: eff_precision := 5 ;
756: end if;
757:
758:
759: /** Default precision for inventory was 6 decimals
760: Changed the default precision to 5 since INV supports a standard
761: precision of 5 decimal places.
762: */
763: if (precision IS NULL) then
764: eff_precision := 5 ;
941: Then, if we still dont get a hit, we will test for the more complex cases,
942: like interclass conversions.
943:
944: Get the primary_uom_code for the item. Also, get the allowed conversions
945: (standard, item only, or both) in case, we need it later.
946: */
947: open c_msi;
948: fetch c_msi into l_primary_uom_code, l_allowed_units;
949:
958:
959: return(TRUE);
960: END IF;
961:
962: /* If only standard conversion is allowed, then check for UOM_CODE in
963: the same UOM_CLASS as the PRIMARY_UOM_CODE as the item
964: */
965: open c_std_cvr_sameClass;
966: fetch c_std_cvr_sameClass into l_uom_code, l_uom_class;
1792: WHERE inventory_item_id = 0
1793: AND uom_code = p_to_uom_code ;
1794: EXCEPTION
1795: WHEN no_data_found THEN
1796: print_debug(' Creating Standard conversion ' || g_pkg_version, 1);
1797: l_conversion_exists := 'N' ;
1798: END ;
1799:
1800: END IF ;