[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,
180: **
181: ** 1. The conversion always starts from the conversion defined, if exists,
182: ** for an specified item.
183: ** 2. If the conversion id not defined for that specific item, then the
184: ** standard conversion, which is defined for all items, is used.
185: ** 3. When the conversion involves two different classes, then
186: ** interclass conversion is activated.
187: */
188:
207: p_lot_number := lot_number;
208: p_organization_id := organization_id;
209:
210: /*
211: ** Get item specific or standard conversions
212: */
213:
214: open from_standard_conversions;
215:
210: /*
211: ** Get item specific or standard conversions
212: */
213:
214: open from_standard_conversions;
215:
216: std_index := 0;
217:
218: loop
216: std_index := 0;
217:
218: loop
219:
220: fetch from_standard_conversions into from_std_rec;
221: exit when from_standard_conversions%notfound;
222:
223: std_index := std_index + 1;
224:
217:
218: loop
219:
220: fetch from_standard_conversions into from_std_rec;
221: exit when from_standard_conversions%notfound;
222:
223: std_index := std_index + 1;
224:
225: from_rate_tab(std_index) := from_std_rec.std_from_rate;
226: from_class_tab(std_index) := from_std_rec.std_from_class;
227:
228: end loop;
229:
230: close from_standard_conversions;
231:
232: if (std_index = 0) then
233:
234: /*
234: /*
235: ** No conversions defined
236: */
237:
238: msgbuf := msgbuf||'Invalid standard conversion : ';
239: msgbuf := msgbuf||'From UOM code: '||from_unit||' ';
240: msgbuf := msgbuf||'To UOM code: '||to_unit||' ';
241: raise invalid_conversion;
242:
251: from_rate := from_rate_tab(1);
252:
253: end if;
254:
255: open to_standard_conversions;
256:
257: std_index := 0;
258:
259: loop
257: std_index := 0;
258:
259: loop
260:
261: fetch to_standard_conversions into to_std_rec;
262: exit when to_standard_conversions%notfound;
263:
264: std_index := std_index + 1;
265:
258:
259: loop
260:
261: fetch to_standard_conversions into to_std_rec;
262: exit when to_standard_conversions%notfound;
263:
264: std_index := std_index + 1;
265:
266: to_rate_tab(std_index) := to_std_rec.std_to_rate;
267: to_class_tab(std_index) := to_std_rec.std_to_class;
268:
269: end loop;
270:
271: close to_standard_conversions;
272:
273: if (std_index = 0) then
274:
275: /*
275: /*
276: ** No conversions defined
277: */
278:
279: msgbuf := msgbuf||'Invalid standard conversion : ';
280: msgbuf := msgbuf||'From UOM code: '||from_unit||' ';
281: msgbuf := msgbuf||'To UOM code: '||to_unit||' ';
282: raise invalid_conversion;
283:
303: -- /*
304: -- ** No conversions defined
305: -- */
306:
307: -- msgbuf := msgbuf||'Invalid standard conversion : ';
308: -- msgbuf := msgbuf||'From UOM code: '||from_unit||' ';
309: -- msgbuf := msgbuf||'To UOM code: '||to_unit||' ';
310: -- raise invalid_conversion;
311:
441:
442:
443: /*
444: ** conversion rates are defaulted to '1' at the start of the procedure
445: ** so seperate calculations are not required for standard/interclass
446: ** conversions
447: */
448:
449: uom_rate := (from_rate * interclass_rate) / to_rate;
564: end if;
565:
566:
567: /** Default precision for inventory was 6 decimals
568: Changed the default precision to 5 since INV supports a standard
569: precision of 5 decimal places.
570: */
571: if (precision IS NULL) then
572: eff_precision := 5 ;
726: end if;
727:
728:
729: /** Default precision for inventory was 6 decimals
730: Changed the default precision to 5 since INV supports a standard
731: precision of 5 decimal places.
732: */
733: if (precision IS NULL) then
734: eff_precision := 5 ;
911: Then, if we still dont get a hit, we will test for the more complex cases,
912: like interclass conversions.
913:
914: Get the primary_uom_code for the item. Also, get the allowed conversions
915: (standard, item only, or both) in case, we need it later.
916: */
917: open c_msi;
918: fetch c_msi into l_primary_uom_code, l_allowed_units;
919:
928:
929: return(TRUE);
930: END IF;
931:
932: /* If only standard conversion is allowed, then check for UOM_CODE in
933: the same UOM_CLASS as the PRIMARY_UOM_CODE as the item
934: */
935: open c_std_cvr_sameClass;
936: fetch c_std_cvr_sameClass into l_uom_code, l_uom_class;