1 PACKAGE BODY inv_decimals_pub AS
2 /* $Header: INVDECPB.pls 120.3 2005/07/12 02:05:58 varajago noship $ */
3
4
5 /*--------------------------------------------------------------------------+
6 |Procedure validate_compare_quantities(..)
7 |Returns the quantity converted from the first UOM in the second UOM.
8 |If quantities in 2 UOMs are already available, then this procedure will
9 |compare and validate these quantities based on conversion rates
10 |and UOM and decimal qty controls. This procedure may be used to validate
11 |scenarios where quatities are entered in dual UOMs. We want to make sure
12 |quantities are valid based on conversion, and conversion
13 |rate tolerances.
14 |
15 |Procedure validate_and_compare(
16 |p_api_version_number IN NUMBER, -- version # of API
17 |p_init_msg_list IN VARCHAR2, -- whether to initialize list
18 |p_inventory_item_id IN NUMBER, -- inventory_item_id
19 |p_organization_id IN NUMBER, -- organization_id
20 |p_lot_control_code IN NUMBER, -- item's lot control code
21 |p_lot_number IN VARCHAR2, -- lot number
22 |p_sub_lot_control_code IN NUMBER, --sub lot control code
23 |p_sublot_number IN VARCHAR2, -- sublot number
24 |p_from_quantity IN NUMBER, -- qty in first UOM
25 |p_from_uom_code IN VARCHAR2, -- UOM of fisrt qty
26 |p_to_uom_code IN VARCHAR2, -- UOM of second qty
27 |p_to_quantity_to_check IN NUMBER, -- qty in second UOM
28 |x_resultant_to_quantity OUT NOCOPY NUMBER, -- calculated qty in second UOM
29 |x_comparison OUT NOCOPY NUMBER,--Possible values are 1,0,-1,-99
30 |x_msg_count OUT NOCOPY NUMBER, -- number of messages
31 |x_msg_data OUT NOCOPY VARCHAR2, -- populated,if msg count = 1
32 |x_return_status OUT NOCOPY VARCHAR2) -- return status
33 |
34 |Note: The comparisons are done in base UOM
35 | of the UOM class to which the first UOM belongs. x_comparison returns:
36 |-1 if from_quantity is less than to_quantity (A < B)
37 | 0 if from_quantity is equal to to_quantity (A = B)
38 | 1 if from_quantity is greater than to_quantity (A > B)
39 | -99 if the validations for the first/second quantity failed
40 | If the UOMs belong to different classes, then users can specify whether
41 | they want to use the effective interclass UOM conversion tolerance, say, T.
42 | CASE: p_use_interclass_tolerance = 1
43 | ------
44 | Q1 > Q2 if (Q1 - Q2) >= T
45 | Q1 = Q2 if ABS(Q1 - Q2) < T
46 | Q1 < Q2 if (Q1 - Q2 ) <= -T
47 |
48 |The output variable x_resultant_to_quantity will contain the converted
49 |quantity
50 |in the second UOM, using effective conversion rates.
51 |Usage: In a dual UOM scenario, this api will confirm whether quantities in
52 |the two UOMs are equal or not, based on x_comparison output variable.
53 +--------------------------------------------------------------------------*/
54
55 -- Package Globals
56 -- a warning exception
57 g_inv_warning exception ;
58 g_ret_warning CONSTANT VARCHAR2(1):= 'W';
59
60
61 g_package_name CONSTANT VARCHAR2(50) := 'INV_DECIMALS';
62 g_max_decimal_digits CONSTANT NUMBER := 9 ;
63 g_max_real_digits CONSTANT NUMBER := 10 ;
64 g_max_total_digits CONSTANT NUMBER := 19 ;
65
66 Procedure validate_compare_quantities(
67 p_api_version_number IN NUMBER,
68 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
69 p_inventory_item_id IN NUMBER,
70 p_organization_id IN NUMBER,
71 p_lot_control_code IN NUMBER,
72 p_lot_number IN VARCHAR2,
73 p_sub_lot_control_code IN NUMBER,
74 p_sublot_number IN VARCHAR2,
75 p_from_quantity IN NUMBER,
76 p_from_uom_code IN VARCHAR2,
77 p_to_uom_code IN VARCHAR2,
78 p_to_quantity_to_check IN NUMBER,
79 x_resultant_to_quantity OUT NOCOPY NUMBER,
80 x_valid_conversion OUT NOCOPY NUMBER,
81 x_msg_count OUT NOCOPY NUMBER,
82 x_msg_data OUT NOCOPY VARCHAR2,
83 x_return_status OUT NOCOPY VARCHAR2) IS
84
85 BEGIN
86
87 null;
88
89 end validate_compare_quantities ;
90
91
92 /*--------------------------------------------------------------------------+
93 |Function convert_UOM(..) return NUMBER ;
94 |Returns the quantity converted from the first unit into the second unit.
95 |If conversion is not possible, return status is failure.
96 |Function convert(
97 |p_api_version_number IN NUMBER,
98 |p_init_msg_list IN VARCHAR2, -- whether to initialize list
99 |p_inventory_item_id IN NUMBER, -- inventory_item_id
100 |p_organization_id IN NUMBER, -- organization_id
101 |p_lot_control_code IN NUMBER, -- item's lot control code
102 |p_lot_number IN VARCHAR2, -- lot number
103 |p_sub_lot_control_code IN NUMBER,
104 |p_sublot_number IN VARCHAR2,
105 |p_from_quantity IN NUMBER, -- qty in first UOM
106 |p_from_uom_code IN VARCHAR2, -- UOM of fisrt qty
107 |p_to_uom_code IN VARCHAR2, -- UOM of second qty
108 |x_msg_count OUT NOCOPY NUMBER,
109 |x_msg_data OUT NOCOPY VARCHAR2,
110 |x_return_status OUT NOCOPY VARCHAR2)
111 | return NUMBER ;
112 |If there is an error, then -99 is returned.
113 |1) From_quantity must be an absolute value.
114 |2) From_quantity will then converted to base UOM in the class,
115 |3) Then converted to base UOM of the
116 | to_UOM class,
117 |4) Then converted to the quantity in to_UOM,
118 |5) Then rounded to 9 decimals
119 +--------------------------------------------------------------------------*/
120
121 Function convert_UOM(
122 p_api_version_number IN NUMBER,
123 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
124 p_inventory_item_id IN NUMBER,
125 p_organization_id IN NUMBER,
126 p_lot_control_code IN NUMBER,
127 p_lot_number IN VARCHAR2,
128 p_sub_lot_control_code IN NUMBER,
129 p_sublot_number IN VARCHAR2,
130 p_from_quantity IN NUMBER,
131 p_from_uom_code IN VARCHAR2,
132 p_to_uom_code IN VARCHAR2,
133 x_msg_count OUT NOCOPY NUMBER,
134 x_msg_data OUT NOCOPY VARCHAR2,
135 x_return_status OUT NOCOPY VARCHAR2) return NUMBER IS
136
137 BEGIN
138
139 return 0;
140
141 end convert_uom ;
142
143
144 /*--------------------------------------------------------------------------+
145 | get_uom_properties(..)
146 | This procedure is used to interrogate the UOM.
147 | It returns:
148 | uom class, base uom.
149 | if the UOM is not found, the return status indicates this.
150 | Procedure get_uom_properties(
151 | p_api_version_number IN NUMBER,
152 | p_init_msg_list IN VARCHAR2,
153 | p_uom_code IN VARCHAR2,
154 | x_uom_class OUT NOCOPY VARCHAR2,
155 | x_base_uom OUT NOCOPY VARCHAR2,
156 | x_msg_count OUT NOCOPY NUMBER,
157 | x_msg_data OUT NOCOPY VARCHAR2,
158 | x_return_status OUT NOCOPY VARCHAR2);
159 +--------------------------------------------------------------------------*/
160
161
162 Procedure get_uom_properties(
163 p_api_version_number IN NUMBER,
164 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
165 p_uom_code IN VARCHAR2,
166 x_uom_class OUT NOCOPY VARCHAR2,
167 x_base_uom OUT NOCOPY VARCHAR2,
168 x_msg_count OUT NOCOPY NUMBER,
169 x_msg_data OUT NOCOPY VARCHAR2,
170 x_return_status OUT NOCOPY VARCHAR2) IS
171
172 BEGIN
173
174 null;
175
176 end get_uom_properties ;
177
178
179
180 /*-------------------------------------------------------------------------+
181 | Procedure compare_quantities(..)
182 | Procedure compare_quantities(
183 | p_api_version_number IN NUMBER,
184 | p_init_msg_list IN VARCHAR2,
185 | p_inventory_item_id IN NUMBER,
186 | p_organization_id IN NUMBER,
187 | p_lot_control_code IN NUMBER,
188 | p_lot_number IN VARCHAR2,
189 | p_sub_lot_control_code IN NUMBER,
190 | p_sublot_number IN VARCHAR2,
191 | p_fisrt_qauantity IN NUMBER,
192 | p_first_uom IN VARCHAR2,
193 | p_second_quantity IN NUMBER,
194 | p_second_uom IN VARCHAR2,
195 | p_use_interclass_tolerance IN VARCHAR2, -- Yes = 1, 2 = No
196 | x_comaprison_result OUT NOCOPY NUMBER,
197 | x_msg_count OUT NOCOPY NUMBER,
198 | x_msg_data OUT NOCOPY VARCHAR2,
199 | x_return_status OUT NOCOPY VARCHAR2);
200 |
201 | This procedure compares the quantities A and B and returns result in the
202 | output variable x_comparison_result. The comparisons are done in base UOM
203 | of the UOM class to which the first UOM belongs:
204 |-1 if quantity A is less than quantity B (A < B)
205 | 0 if quantity A is equal to quantity B (A = B)
206 | 1 if quantity A is greater than quantity B (A > B)
207 | If the UOMs belong to different classes, then users can specify whether
208 | they want to use interclass UOM conversion tolerance, say, T.
209 | CASE: p_use_interclass_tolerance = 1
210 | ------
211 | Q1 > Q2 if (Q1 - Q2) >= T
212 | Q1 = Q2 if ABS(Q1 - Q2) < T
213 | Q1 < Q2 if (Q1 - Q2 ) <= -T
214 +------------------------------------------------------------------------*/
215
216 Procedure compare_quantities(
217 p_api_version_number IN NUMBER,
218 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
219 p_inventory_item_id IN NUMBER,
220 p_organization_id IN NUMBER,
221 p_lot_control_code IN NUMBER,
222 p_lot_number IN VARCHAR2,
223 p_sub_lot_control_code IN NUMBER,
224 p_sublot_number IN VARCHAR2,
225 p_fisrt_qauantity IN NUMBER,
226 p_first_uom IN VARCHAR2,
227 p_second_quantity IN NUMBER,
228 p_second_uom IN VARCHAR2,
229 p_use_interclass_tolerance IN VARCHAR2,
230 x_comaprison_result OUT NOCOPY NUMBER,
231 x_msg_count OUT NOCOPY NUMBER,
232 x_msg_data OUT NOCOPY VARCHAR2,
233 x_return_status OUT NOCOPY VARCHAR2) IS
234
235 BEGIN
236
237 null;
238
239 end compare_quantities ;
240
241
242
243 /*-----------------------------------------------------------------------+
244 | Procedure Validate_Quantity(
245 | p_item_id IN NUMBER,
246 | p_organization_id IN NUMBER,
247 | p_input_quantity IN NUMBER,
248 | p_UOM_code IN VARCHAR2,
249 | x_output_quantity OUT NOCOPY NUMBER,
250 | x_primary_quantity OUT NOCOPY NUMBER,
251 | x_return_status OUT NOCOPY VARCHAR2);
252 |
253 | Validates and returns the quantity in this manner (the caller does not need
254 | to adjust the result):
255 | This routine checks to make sure that the input quantity precision does not exceed
256 | the decimal precision. Max Precision is: 10 digits before the decimal point and
257 | 9 digits after the decimal point.
258 | The routine also makes sure that if the item is serial number controlled, then
259 | the quantity in primary UOM is an integer number.
260 | The routine also makes sure that if the item's indivisible_flag is set to yes,
261 | then the item quantity is an integer in the primary UOM.
262 | The routine also checks if the profile, INV:DETECT TRUNCATION, is set to yes
263 | the item quantity in primary UOM also obeys max precision and that it is not zero
264 | if the input quantity was not zero.
265 | The procedure retruns a correct output quantity in the transaction UOM, returns the
266 | the primary quantity (in priamry UOM, of course), and returns a status of success,failure
267 | or warning.
268 |
269 +-------------------------------------------------------------------------*/
270
271 Procedure Validate_Quantity(
272 p_item_id IN NUMBER,
273 p_organization_id IN NUMBER,
274 p_input_quantity IN NUMBER,
275 p_UOM_code IN VARCHAR2,
276 x_output_quantity OUT NOCOPY NUMBER,
277 x_primary_quantity OUT NOCOPY NUMBER,
278 x_return_status OUT NOCOPY VARCHAR2) IS
279
280 -- Constants
281 c_api_version_number CONSTANT NUMBER := 1.0 ;
282 c_api_name CONSTANT VARCHAR2(50):= 'VALIDATE_QUANTITY';
283
284 -- Variables
285 l_qty_string VARCHAR2(50);
286 l_decimal_len NUMBER; -- number of decimal digits
287 l_real_len NUMBER; -- number of digits before decimal point
288 l_total_len NUMBER; -- total number of digits
289 l_uom_class VARCHAR2(50); -- uom class name
290 l_base_uom VARCHAR2(50); -- base uom in class
291 l_primary_uom VARCHAR2(10); -- primary uom of item
292 l_highest_factor NUMBER; -- biggest factor in class w.r.t uom
293 l_lowest_factor NUMBER; -- lowest factor in class w.r.t. uom
294 l_conv_factor NUMBER;
295 l_exp_factor NUMBER;
296 l_decimal_profile VARCHAR2(240);
297 l_serial_control NUMBER ;
298 l_do_conversion NUMBER := 1;
299 l_raise_warning NUMBER := 0;
300 l_indivisible_flag VARCHAR2(10):= 'N';
301
302 BEGIN
303
304 -- initialize return status to success
305 x_return_status := fnd_api.g_ret_sts_success;
306
307 -- now make sure that # of decimal digits does not exceed g_max_decimal_digits
308 if ( p_input_quantity <> ROUND(p_input_quantity, g_max_decimal_digits)) then
309 fnd_message.set_name('INV', 'MAX_DECIMAL_LENGTH');
310 x_output_quantity := ROUND(p_input_quantity, g_max_decimal_digits);
311 l_raise_warning := 1 ;
312 else
313 if (x_output_quantity IS NULL) then
314 x_output_quantity := p_input_quantity;
315 end if;
316 end if;
317
318 -- Now make sure that the length of real part of number doesn't exceed
319 -- g_max_real_digits
320 if ( trunc(abs(p_input_quantity)) > (POWER(10,g_max_real_digits) - 1) ) then
321 fnd_message.set_name('INV', 'MAX_REAL_LENGTH');
322 raise fnd_api.g_exc_error;
323 end if;
324
325 -- now that in the given UOM the item quantity obeys the decimal precision rules
326 -- we can now make sure that when converted to primary qty, decimal precision
327 -- rules will still be obeyed.
328
329
330 -- get the item's primary uom, serial_number_control_code, and the item's indivisible flag
331
332 SELECT primary_uom_code, serial_number_control_code, NVL(indivisible_flag,'N')
333 INTO l_primary_uom, l_serial_control, l_indivisible_flag
334 FROM mtl_system_items
335 WHERE inventory_item_id = p_item_id
336 AND organization_id = p_organization_id ;
337
338 -- if the primary uom is same as input uom, then nothing more to validate
339 if ( l_primary_uom = p_uom_code) then
340 x_primary_quantity := p_input_quantity ;
341 l_do_conversion := 0;
342 end if;
343
344 if ( l_do_conversion = 1 ) then
345 -- get the conversion rate. call inv_convert.uom_convert procedure.
346 -- NOTE: this convert routines ROUNDS (not truncates) to precision specified
347 l_conv_factor := inv_convert.inv_um_convert(
348 item_id => p_item_id,
349 precision => g_max_decimal_digits +2,
350 from_quantity => p_input_quantity,
351 from_unit => p_uom_code,
352 to_unit => l_primary_uom,
353 from_name => null,
354 to_name => null);
355
356 x_primary_quantity := l_conv_factor;
357
358 -- Begin fix 2256336
359 IF x_primary_quantity <> TRUNC(x_primary_quantity) AND l_indivisible_flag = 'Y' THEN
360 l_conv_factor := inv_convert.inv_um_convert(
361 item_id => p_item_id,
362 precision => g_max_decimal_digits +2,
363 from_quantity => 1,
364 from_unit => l_primary_uom,
365 to_unit => p_uom_code,
366 from_name => null,
367 to_name => null);
368 IF l_conv_factor <> 0 THEN
369 x_primary_quantity := p_input_quantity/l_conv_factor ;
370 END IF;
371 END IF;
372 -- End fix 2256336
373
374
375 -- check if the profile detect_truncation is set. If yes, then make sure primary qty
376 -- also does not break decimal precision rules.
377 fnd_profile.get('INV_DETECT_TRUNCATION',l_decimal_profile);
378
379 if ( l_decimal_profile = '1' ) then -- '1'= yes, '2' = no
380
381 if ( x_primary_quantity <> ROUND(x_primary_quantity,g_max_decimal_digits) ) then
382 fnd_message.set_name('INV', 'PRI_MAX_DECIMAL_LENGTH');
383 raise fnd_api.g_exc_error;
384 end if;
385
386 -- Now make sure that the length of real part of number doesn't exceed
387 -- g_max_real_digits
388 if ( trunc(abs(x_primary_quantity)) > ( POWER(10,g_max_real_digits) - 1) ) then
389 fnd_message.set_name('INV', 'PRI_MAX_REAL_LENGTH');
390 raise fnd_api.g_exc_error;
391 end if;
392
393 -- now check if the quantity in primary UOM is zero
394 if ( (x_primary_quantity = 0) AND (p_input_quantity <> 0) ) then
395 fnd_message.set_name('INV', 'PRI_QTY_IS_ZERO');
396 raise fnd_api.g_exc_error ;
397 end if;
398
399 end if;
400
401 end if;
402
403 -- if item has indivisible flag set, then make sure that quantity is integer in
404 -- primary UOM
405
406 if (( l_indivisible_flag = 'Y' ) AND ( Round(x_primary_quantity,(g_max_decimal_digits-1)) <> TRUNC(x_primary_quantity)) ) then
407 fnd_message.set_name('INV', 'DIVISIBILITY_VIOLATION');
408 raise fnd_api.g_exc_error ;
409 end if;
410
411 -- if item is serial number controlled, make the qty in primary UOM is integer
412 -- Assumption: Whenever an item is unser serial number control, teh quantity must be
413 -- integer in primary uom. Even if specific serial may not be required at the time, the
414 -- qty of a serial number should be integer in primary uom.
415
416 if ( (l_serial_control > 1 ) AND ( x_primary_quantity <> TRUNC(x_primary_quantity)) ) then
417 fnd_message.set_name('INV', 'SERIAL_QTY_VIOLATION');
418 raise fnd_api.g_exc_error ;
419 end if;
420
421 if ( l_raise_warning = 1 ) then
422 raise g_inv_warning ;
423 end if;
424
425 EXCEPTION
426 when fnd_api.g_exc_error then
427 x_return_status := fnd_api.g_ret_sts_error ;
428
429 when fnd_api.g_exc_unexpected_error then
430 x_return_status := fnd_api.g_ret_sts_unexp_error;
431
432 when g_inv_warning then
433 x_return_status := g_ret_warning ;
434
435 when others then
436 x_return_status := fnd_api.g_ret_sts_unexp_error;
437
438 if (fnd_msg_pub.check_msg_level
439 (fnd_msg_pub.g_msg_lvl_unexp_error))then
440 fnd_msg_pub.add_exc_msg(g_package_name,c_api_name);
441 end if;
442
443 end validate_quantity ;
444
445 /*-----------------------------------------------------------------------+
446 | Procedure Validate_Quantity(
447 | p_item_id IN NUMBER,
448 | p_organization_id IN NUMBER,
449 | p_input_quantity IN NUMBER,
450 | p_UOM_code IN VARCHAR2,
451 | p_max_decimal_digits IN NUMBER,
452 | x_output_quantity OUT NOCOPY NUMBER,
453 | x_primary_quantity OUT NOCOPY NUMBER,
454 | x_return_status OUT NOCOPY VARCHAR2);
455 |
456 | This procedure overloads validate_quantity with one more parameter p_max_decimal_digits
457 | to adjust max precision. If the value of p_max_decimal_digits is null, then default
458 | it to g_max_decimal_digits. Other works the same as the above Validate_Quantity procedure.
459 +-------------------------------------------------------------------------*/
460
461 Procedure Validate_Quantity(
462 p_item_id IN NUMBER,
463 p_organization_id IN NUMBER,
464 p_input_quantity IN NUMBER,
465 p_UOM_code IN VARCHAR2,
466 p_max_decimal_digits IN NUMBER,
467 p_primary_uom IN VARCHAR2,
468 p_indivisible_flag IN VARCHAR2,
469 x_output_quantity OUT NOCOPY NUMBER,
470 x_primary_quantity OUT NOCOPY NUMBER,
471 x_return_status OUT NOCOPY VARCHAR2) IS
472
473 -- Constants
474 c_api_version_number CONSTANT NUMBER := 1.0 ;
475 c_api_name CONSTANT VARCHAR2(50):= 'VALIDATE_QUANTITY';
476
477 -- Variables
478 l_qty_string VARCHAR2(50);
479 l_decimal_len NUMBER; -- number of decimal digits
480 l_real_len NUMBER; -- number of digits before decimal point
481 l_total_len NUMBER; -- total number of digits
482 l_uom_class VARCHAR2(50); -- uom class name
483 l_base_uom VARCHAR2(50); -- base uom in class
484 l_primary_uom VARCHAR2(10) := p_primary_uom; -- primary uom of item
485 l_highest_factor NUMBER; -- biggest factor in class w.r.t uom
486 l_lowest_factor NUMBER; -- lowest factor in class w.r.t. uom
487 l_conv_factor NUMBER;
488 l_exp_factor NUMBER;
489 l_decimal_profile VARCHAR2(240);
490 l_serial_control NUMBER := 1;
491 l_do_conversion NUMBER := 1;
492 l_raise_warning NUMBER := 0;
493 l_indivisible_flag VARCHAR2(10):= NVL(p_indivisible_flag, 'N');
494 l_max_decimal_digits NUMBER := p_max_decimal_digits;
495
496 BEGIN
497
498 -- initialize return status to success
499 x_return_status := fnd_api.g_ret_sts_success;
500
501 -- put the default = g_max_decimal_digits if l_max_decimal_digits is null
502 if (l_max_decimal_digits IS NULL) then
503 l_max_decimal_digits := g_max_decimal_digits;
504 end if;
505
506 -- now make sure that # of decimal digits does not exceed l_max_decimal_digits
507 if ( p_input_quantity <> ROUND(p_input_quantity, l_max_decimal_digits)) then
508 fnd_message.set_name('INV', 'MAX_DECIMAL_LENGTH');
509 x_output_quantity := ROUND(p_input_quantity, l_max_decimal_digits);
510 l_raise_warning := 1 ;
511 else
512 if (x_output_quantity IS NULL) then
513 x_output_quantity := p_input_quantity;
514 end if;
515 end if;
516
517 -- Now make sure that the length of real part of number doesn't exceed
518 -- g_max_real_digits
519 if ( trunc(abs(p_input_quantity)) > (POWER(10,g_max_real_digits) - 1) ) then
520 fnd_message.set_name('INV', 'MAX_REAL_LENGTH');
521 raise fnd_api.g_exc_error;
522 end if;
523
524 -- now that in the given UOM the item quantity obeys the decimal precision rules
525 -- we can now make sure that when converted to primary qty, decimal precision
526 -- rules will still be obeyed.
527
528
529 -- get the item's primary uom, serial_number_control_code, and the item's indivisible flag
530 IF (p_item_id IS NOT NULL) then
531 SELECT primary_uom_code, serial_number_control_code, NVL(indivisible_flag,'N')
532 INTO l_primary_uom, l_serial_control, l_indivisible_flag
533 FROM mtl_system_items
534 WHERE inventory_item_id = p_item_id
535 AND organization_id = p_organization_id ;
536 END IF;
537
538 -- if the primary uom is same as input uom, then nothing more to validate
539 if ( l_primary_uom = p_uom_code) then
540 x_primary_quantity := p_input_quantity ;
541 l_do_conversion := 0;
542 end if;
543
544 if ( l_do_conversion = 1 ) then
545 -- get the conversion rate. call inv_convert.uom_convert procedure.
546 -- NOTE: this convert routines ROUNDS (not truncates) to precision specified
547 l_conv_factor := inv_convert.inv_um_convert(
548 item_id => p_item_id,
549 precision => l_max_decimal_digits +2,
550 from_quantity => p_input_quantity,
551 from_unit => p_uom_code,
552 to_unit => l_primary_uom,
553 from_name => null,
554 to_name => null);
555
556 x_primary_quantity := l_conv_factor ;
557
558
559 -- check if the profile detect_truncation is set. If yes, then make sure primary qty
560 -- also does not break decimal precision rules.
561 fnd_profile.get('INV_DETECT_TRUNCATION',l_decimal_profile);
562
563 if ( l_decimal_profile = '1' ) then -- '1'= yes, '2' = no
564
565 if ( x_primary_quantity <> ROUND(x_primary_quantity,l_max_decimal_digits) ) then
566 fnd_message.set_name('INV', 'PRI_MAX_DECIMAL_LENGTH');
567 raise fnd_api.g_exc_error;
568 end if;
569
570 -- Now make sure that the length of real part of number doesn't exceed
571 -- g_max_real_digits
572 if ( trunc(abs(x_primary_quantity)) > ( POWER(10,g_max_real_digits) - 1) ) then
573 fnd_message.set_name('INV', 'PRI_MAX_REAL_LENGTH');
574 raise fnd_api.g_exc_error;
575 end if;
576
577 -- now check if the quantity in primary UOM is zero
578 if ( (x_primary_quantity = 0) AND (p_input_quantity <> 0) ) then
579 fnd_message.set_name('INV', 'PRI_QTY_IS_ZERO');
580 raise fnd_api.g_exc_error ;
581 end if;
582
583 end if;
584
585 end if;
586
587 -- if item has indivisible flag set, then make sure that quantity is integer in
588 -- primary UOM
589
590 if (( l_indivisible_flag = 'Y' ) AND ( Round(x_primary_quantity,(l_max_decimal_digits-1)) <> TRUNC(x_primary_quantity)) ) then
591 fnd_message.set_name('INV', 'DIVISIBILITY_VIOLATION');
592 raise fnd_api.g_exc_error ;
593 end if;
594
595 -- if item is serial number controlled, make the qty in primary UOM is integer
596 -- Assumption: Whenever an item is unser serial number control, teh quantity must be
597 -- integer in primary uom. Even if specific serial may not be required at the time, the
598 -- qty of a serial number should be integer in primary uom.
599
600 if ( (l_serial_control > 1 ) AND ( x_primary_quantity <> TRUNC(x_primary_quantity)) ) then
601 fnd_message.set_name('INV', 'SERIAL_QTY_VIOLATION');
602 raise fnd_api.g_exc_error ;
603 end if;
604
605 if ( l_raise_warning = 1 ) then
606 raise g_inv_warning ;
607 end if;
608
609 EXCEPTION
610 when fnd_api.g_exc_error then
611 x_return_status := fnd_api.g_ret_sts_error ;
612
613 when fnd_api.g_exc_unexpected_error then
614 x_return_status := fnd_api.g_ret_sts_unexp_error;
615
616 when g_inv_warning then
617 x_return_status := g_ret_warning ;
618
619 when others then
620 x_return_status := fnd_api.g_ret_sts_unexp_error;
621
622 if (fnd_msg_pub.check_msg_level
623 (fnd_msg_pub.g_msg_lvl_unexp_error))then
624 fnd_msg_pub.add_exc_msg(g_package_name,c_api_name);
625 end if;
626
627 end Validate_Quantity;
628
629
630
631 function get_primary_quantity(
632 p_organization_id IN NUMBER,
633 p_inventory_item_id IN NUMBER,
634 p_uom IN VARCHAR2,
635 p_quantity IN NUMBER) return number IS
636
637 -- local variables
638 l_primary_uom VARCHAR2(10);
639 BEGIN
640 -- if input qty is null, assume 0, in which case we return 0 as converted
641 -- qty also
642 if ( ( p_quantity IS NULL ) OR (p_quantity = 0) ) then
643 return 0;
644 end if;
645
646 SELECT primary_uom_code
647 INTO l_primary_uom
648 FROM mtl_system_items
649 WHERE inventory_item_id = p_inventory_item_id
650 AND organization_id = p_organization_id ;
651
652 return( inv_convert.inv_um_convert(
653 item_id => p_inventory_item_id,
654 precision => 9,
655 from_quantity => p_quantity,
656 from_unit => p_uom,
657 to_unit => l_primary_uom,
658 from_name => null,
659 to_name => null) );
660 end get_primary_quantity ;
661
662
663 end INV_DECIMALS_PUB;
[Home] [Help]