[Home] [Help]
PACKAGE BODY: APPS.MTL_LOT_UOM_CONV_PUB
Source
1 PACKAGE BODY MTL_LOT_UOM_CONV_PUB AS
2 /* $Header: INVPLUCB.pls 120.2 2011/07/05 07:10:45 gausriva ship $
3 +==========================================================================+
4 | Copyright (c) 1998 Oracle Corporation |
5 | Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +==========================================================================+
8 | FILE NAME |
9 | INVPLUCB.pls |
10 | |
11 | PACKAGE NAME |
12 | MTL_LOT_UOM_CONV_PUB |
13 | TYPE |
14 | Public |
15 | |
16 | DESCRIPTION |
17 | Public layer for Lot Uom Conversion APIs. |
18 | |
19 | CONTENTS |
20 | |
21 | |
22 | HISTORY |
23 | Created Joe DiIorio |
24 | Updated Joe DiIorio - 08/01/2004 |
25 | Changed named and parms for lot_uom_conversion to |
26 | create_lot_uom_conversion. |
27 | Changed call to business_logic to |
28 | validate_lot_conversion_rules. |
29 | Updated Joe DiIorio - 09/16/2004 |
30 | Added check for from/to null values. |
31 | Updated Joe DiIorio - 10/22/2004 |
32 | Added x_sequence to capture transaction manager header id. |
33 | Updated Joe DiIorio - 11/12/2004 |
34 ! removed do check for now. 4005057 !
38 */
35 | Updated Joe DiIorio - 12/08/2004 |
36 ! removed defaults for gscc. Kept in header. !
37 ============================================================================
39
40 PROCEDURE log_msg(p_msg_text IN VARCHAR2);
41
42 /* Global variables */
43 G_PKG_NAME CONSTANT VARCHAR2(30):='MTL_LOT_UOM_CONV_PUB';
44 G_tmp BOOLEAN := FND_MSG_PUB.Check_Msg_Level(0) ; -- temp call to initialize the
45 -- msg level threshhold gobal
46 -- variable.
47 G_debug_level NUMBER(2) := FND_MSG_PUB.G_Msg_Level_Threshold; -- Use this variable everywhere
48 -- to decide to log a debug msg.
49
50
51 /*===========================================================================
52 -- PROCEDURE
53 -- create_lot_uom_conversion
54 --
55 -- DESCRIPTION:
56 -- This validates and creates/updates a lot uom conversions.
57 --
58 -- PARAMETERS:
59 -- p_api_version IN NUMBER - Standard api parameter
60 -- p_init_msg_list IN VARCHAR2 - Standard api parameter
61 -- p_commit IN VARCHAR2 - Standard api parameter
62 -- p_validation_level IN NUMBER - Standard api parameter
63 -- p_action_type IN VARCHAR2 - I for insert, U for update
64 -- p_update_type_indicator IN VARCHAR2 - Quantity Change identifier
65 -- 0 = Update onhand balances
66 -- 1 = Recalculate Batch Primary Quantity
67 -- 2 = Recalculate Batch Secondary Quantity
68 -- 3 = Recalculate On-Hand Primary Quantity
69 -- 4 = Recalculate On-Hand Secondary Quantity
70 -- 5 = No Quantity Updates
71 -- p_reason_id IN NUMBER - Surrogate key for Reason Code.
72 -- p_batch_id IN NUMBER - Surrogate key for Batch number.
73 -- p_process_data IN VARCHAR2
74 -- p_lot_uom_conv_rec IN ROW - Lot conversion record.
75 -- p_qty_update_tbl IN - Table of quantity changes.
76 -- x_return_status OUT VARCHAR2 - 'S'uccess, 'E'rror, 'U'nexpected Error
77 -- x_msg_count OUT - Standard api parameter
78 -- x_msg_data OUT - Standard api parameter.
79 -- x_sequence IN OUT - For transaction processing.
80 --
81 -- SYNOPSIS:
82 -- Create/validate lot uom conversion
83 --
84 -- HISTORY
85 -- Joe DiIorio 01-Sept-2004 Created.
86 -- Joe DiIorio 14-Sept-2005 Updated for bug#4107431
87 --
88 --=========================================================================== */
89
90 PROCEDURE create_lot_uom_conversion
91 ( p_api_version IN NUMBER
92 , p_init_msg_list IN VARCHAR2
93 , p_commit IN VARCHAR2
94 , p_validation_level IN NUMBER
95 , p_action_type IN VARCHAR2
96 , p_update_type_indicator IN NUMBER DEFAULT 5
97 , p_reason_id IN NUMBER
98 , p_batch_id IN NUMBER
99 , p_process_data IN VARCHAR2
100 , p_lot_uom_conv_rec IN OUT NOCOPY mtl_lot_uom_class_conversions%ROWTYPE
101 , p_qty_update_tbl IN OUT NOCOPY MTL_LOT_UOM_CONV_PUB.quantity_update_rec_type
102 , x_return_status OUT NOCOPY VARCHAR2
103 , x_msg_count OUT NOCOPY NUMBER
104 , x_msg_data OUT NOCOPY VARCHAR2
105 , x_sequence IN OUT NOCOPY NUMBER)
106
107
108 IS
109 l_api_name CONSTANT VARCHAR2(30) := 'create_lot_uom_conversion';
110 l_api_version CONSTANT NUMBER := 1.0 ;
111 l_conv_seq MTL_LOT_UOM_CLASS_CONVERSIONS.CONVERSION_ID%TYPE;
112 l_aud_seq MTL_LOT_CONV_AUDIT.CONV_AUDIT_ID%TYPE;
113
114 l_org INV_VALIDATE.org;
115 l_item INV_VALIDATE.item;
116 l_locator INV_VALIDATE.locator;
117 l_lot INV_VALIDATE.lot;
118 l_sub INV_VALIDATE.sub;
119 l_revision varchar2(10);
120 l_ret NUMBER;
121 l_err_msg VARCHAR2(2000);
122 l_reason_id NUMBER;
123 l_update_type_indicator VARCHAR2(1);
124
125 l_violation BOOLEAN;
126 l_trans_count NUMBER;
127 l_conversion_id NUMBER;--bug#12566380
128 l_action_type VARCHAR2(10); --bug#12566380
129
130 CURSOR get_uom_code_values (p_uom_code VARCHAR2) IS
131 SELECT unit_of_measure, uom_class
132 FROM mtl_units_of_measure
133 WHERE uom_code = p_uom_code;
134
135 CURSOR get_unit_of_meas_values (p_unit_of_measure VARCHAR2) IS
136 SELECT uom_code, uom_class
137 FROM mtl_units_of_measure
138 WHERE unit_of_measure = p_unit_of_measure;
139
140 CURSOR get_uom_class_values (p_uom_class VARCHAR2) IS
141 SELECT unit_of_measure, uom_code
142 FROM mtl_units_of_measure
143 WHERE uom_class = p_uom_class and base_uom_flag = 'Y';
144
145 l_from_uom_code mtl_units_of_measure.uom_code%TYPE;
146 l_from_unit_of_measure mtl_units_of_measure.unit_of_measure%TYPE;
147 l_from_uom_class mtl_units_of_measure.uom_class%TYPE;
148 l_to_uom_code mtl_units_of_measure.uom_code%TYPE;
149 l_to_unit_of_measure mtl_units_of_measure.unit_of_measure%TYPE;
150 l_to_uom_class mtl_units_of_measure.uom_class%TYPE;
151
152
153 l_return_status VARCHAR2(2);
154 l_msg_count NUMBER;
155 l_msg_data VARCHAR2(2000);
156 l_tran_seq NUMBER;
157
158 DO_CHECK_ERROR EXCEPTION;
159
163
160 /*=======================================
161 Joe DiIorio 01/13/2005 BUG#4107431
162 =======================================*/
164
165 l_from_base_uom_code MTL_UNITS_OF_MEASURE.UOM_CODE%TYPE;
166 l_from_base_unit_of_measure MTL_UNITS_OF_MEASURE.UNIT_OF_MEASURE%TYPE;
167 l_to_base_uom_code MTL_UNITS_OF_MEASURE.UOM_CODE%TYPE;
168 l_to_base_unit_of_measure MTL_UNITS_OF_MEASURE.UNIT_OF_MEASURE%TYPE;
169
170
171 /*======================================
172 Cursor to get base values for a
173 given uom class.
174 ======================================*/
175
176 CURSOR get_base_values (l_uom_class VARCHAR2) IS
177 SELECT unit_of_measure, uom_code
178 FROM mtl_units_of_measure
179 WHERE uom_class = l_uom_class and base_uom_flag = 'Y';
180
181
182 l_factor NUMBER;
183
184
185 /*======================================
186 Cursor to get primary uom code for
187 and item.
188 ======================================*/
189
190 CURSOR get_item_uom IS
191 SELECT primary_uom_code
192 FROM mtl_system_items
193 WHERE organization_id = p_lot_uom_conv_rec.organization_id
194 AND inventory_item_id = p_lot_uom_conv_rec.inventory_item_id;
195
196 l_item_uom_code MTL_SYSTEM_ITEMS.PRIMARY_UOM_CODE%TYPE;
197
198
199 /*======================================
200 Cursor to get uom_class for a given
201 uom code.
202 ======================================*/
203
204 CURSOR get_item_uom_class IS
205 SELECT uom_class
206 FROM mtl_units_of_measure
207 WHERE uom_code = l_item_uom_code;
208
209
210 l_item_uom_class MTL_UNITS_OF_MEASURE.UOM_CLASS%TYPE;
211
212
213 l_from_rate NUMBER;
214
215
216 /*=======================================
217 Joe DiIorio 01/13/2005 BUG#4107431
218 =======================================*/
219
220 BEGIN
221
222
223 l_tran_seq := x_sequence;
224
225 SAVEPOINT LOT_UOM_CONVERSION;
226
227 l_update_type_indicator := p_update_type_indicator;
228 /*================================
229 Initialize Message List Logic
230 ================================*/
231 IF FND_API.to_boolean(p_init_msg_list) THEN
232 FND_MSG_PUB.Initialize;
233 END IF;
234
235 /*==================================================
236 Standard call to check for call compatibility.
237 *==================================================*/
238 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
239 p_api_version ,
240 l_api_name ,
241 G_PKG_NAME ) THEN
242 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
243 END IF;
244 x_return_status := FND_API.G_RET_STS_SUCCESS;
245
246
247 /*===============================
248 Validate Action Type
249 ==============================*/
250
251 IF (p_action_type <> 'I' AND p_action_type <> 'U') THEN
252 FND_MESSAGE.SET_NAME('INV','INV_LOTC_ACTIONTYPE_INVALID');
253 FND_MSG_PUB.Add;
254 RAISE FND_API.G_EXC_ERROR;
255 END IF;
256
257 l_update_type_indicator := p_update_type_indicator;
258 IF (p_validation_level = FND_API.G_VALID_LEVEL_FULL) THEN
259 /*===============================
260 Validate Organization
261 ==============================*/
262 l_org.organization_id := p_lot_uom_conv_rec.organization_id;
263 l_ret := INV_VALIDATE.organization(l_org);
264 IF (l_ret = INV_VALIDATE.F) THEN
265 FND_MESSAGE.SET_NAME('INV','INV_LOTC_ORG_INVALID');
266 FND_MSG_PUB.Add;
267 RAISE FND_API.G_EXC_ERROR;
268 END IF;
269
270 /*===============================
271 Validate Item
272 ==============================*/
273 l_item.inventory_item_id := p_lot_uom_conv_rec.inventory_item_id;
274 l_item.organization_id := p_lot_uom_conv_rec.organization_id;
275
276 l_ret := INV_VALIDATE.inventory_item(l_item, l_org);
277 IF (l_ret = INV_VALIDATE.F) THEN
278 FND_MESSAGE.SET_NAME('INV','INV_LOTC_ITEM_INVALID');
279 FND_MSG_PUB.Add;
280 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
281 END IF;
282
283 /*===============================
284 Validate Lot
285 ==============================*/
286 l_lot.inventory_item_id := p_lot_uom_conv_rec.inventory_item_id;
287 l_lot.organization_id := p_lot_uom_conv_rec.organization_id;
288 l_lot.lot_number := p_lot_uom_conv_rec.lot_number;
289 l_ret := INV_VALIDATE.lot_number(l_lot, l_org, l_item);
290 IF (l_ret = INV_VALIDATE.F) THEN
291 FND_MESSAGE.SET_NAME('INV','INV_LOTC_LOT_INVALID');
292 FND_MSG_PUB.Add;
293 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
294 END IF;
295
296
297 /*===============================
298 Fill in missing uom values.
299 ==============================*/
300
301 l_from_uom_code := p_lot_uom_conv_rec.from_uom_code;
302 l_from_unit_of_measure := p_lot_uom_conv_rec.from_unit_of_measure;
303 l_from_uom_class := p_lot_uom_conv_rec.from_uom_class;
304 l_to_uom_code := p_lot_uom_conv_rec.to_uom_code;
305 l_to_unit_of_measure := p_lot_uom_conv_rec.to_unit_of_measure;
306 l_to_uom_class := p_lot_uom_conv_rec.to_uom_class;
307
308 IF ( l_from_uom_code IS NULL AND
309 l_from_unit_of_measure IS NULL AND
310 l_from_uom_class IS NULL ) THEN
314 END IF;
311 FND_MESSAGE.SET_NAME('INV','INV_LOTC_SOURCE_UOM_REQD');
312 FND_MSG_PUB.Add;
313 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
315
316 IF ( l_to_uom_code IS NULL AND
317 l_to_unit_of_measure IS NULL AND
318 l_to_uom_class IS NULL) THEN
319 FND_MESSAGE.SET_NAME('INV','INV_LOTC_TARGET_UOM_REQD');
320 FND_MSG_PUB.Add;
321 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
322 END IF;
323
324
325
326 IF ( l_from_uom_code IS NULL OR
327 l_from_unit_of_measure IS NULL OR
328 l_from_uom_class IS NULL ) THEN
329
330 IF ( l_from_uom_code IS NOT NULL) THEN
331 OPEN get_uom_code_values(l_from_uom_code);
332 FETCH get_uom_code_values INTO l_from_unit_of_measure,l_from_uom_class;
333 IF (get_uom_code_values%NOTFOUND) THEN
334 CLOSE get_uom_code_values;
335 FND_MESSAGE.SET_NAME('INV','INV_GET_UOM_ERR');
336 FND_MSG_PUB.Add;
337 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
338 END IF;
339 CLOSE get_uom_code_values;
340
341 ELSIF ( l_from_unit_of_measure IS NOT NULL) THEN
342 OPEN get_unit_of_meas_values(l_from_unit_of_measure);
343 FETCH get_unit_of_meas_values INTO l_from_uom_code,l_from_uom_class;
344 IF (get_unit_of_meas_values%NOTFOUND) THEN
345 CLOSE get_unit_of_meas_values;
346 FND_MESSAGE.SET_NAME('INV','INV_GET_UNITOFMEASURE_ERR');
347 FND_MSG_PUB.Add;
348 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
349 END IF;
350 CLOSE get_unit_of_meas_values;
351
352 ELSIF ( l_from_uom_class IS NOT NULL) THEN
353 OPEN get_uom_class_values(l_from_uom_class);
354 FETCH get_uom_class_values INTO l_from_unit_of_measure,l_from_uom_code;
355 IF (get_uom_class_values%NOTFOUND) THEN
356 CLOSE get_uom_class_values;
357 FND_MESSAGE.SET_NAME('INV','INV_GET_UOM_CLASS_ERR');
358 FND_MSG_PUB.Add;
359 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
360 END IF;
361 CLOSE get_uom_class_values;
362
363 END IF; /* Which value if not NULL */
364
365 END IF; /* If missing at least one of the FROM values */
366
367 IF ( l_to_uom_code IS NULL OR
368 l_to_unit_of_measure IS NULL OR
369 l_to_uom_class IS NULL) THEN
370
371 IF ( l_to_uom_code IS NOT NULL) THEN
372 OPEN get_uom_code_values(l_to_uom_code);
373 FETCH get_uom_code_values INTO l_to_unit_of_measure,l_to_uom_class;
374 IF (get_uom_code_values%NOTFOUND) THEN
375 CLOSE get_uom_code_values;
376 FND_MESSAGE.SET_NAME('INV','INV_GET_UOM_ERR');
377 FND_MSG_PUB.Add;
378 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
379 END IF;
380 CLOSE get_uom_code_values;
381
382 ELSIF ( l_to_unit_of_measure IS NOT NULL) THEN
383 OPEN get_unit_of_meas_values(l_to_unit_of_measure);
384 FETCH get_unit_of_meas_values INTO l_to_uom_code,l_to_uom_class;
385 IF (get_unit_of_meas_values%NOTFOUND) THEN
386 CLOSE get_unit_of_meas_values;
387 FND_MESSAGE.SET_NAME('INV','INV_GET_UNITOFMEASURE_ERR');
388 FND_MSG_PUB.Add;
389 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
390 END IF;
391 CLOSE get_unit_of_meas_values;
392
393 ELSIF ( l_to_uom_class IS NOT NULL) THEN
394 OPEN get_uom_class_values(l_to_uom_class);
395 FETCH get_uom_class_values INTO l_to_unit_of_measure,l_to_uom_code;
396 IF (get_uom_class_values%NOTFOUND) THEN
397 CLOSE get_uom_class_values;
398 FND_MESSAGE.SET_NAME('INV','INV_GET_UOM_CLASS_ERR');
399 FND_MSG_PUB.Add;
400 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
401 END IF;
402 CLOSE get_uom_class_values;
403
404 END IF; /* Which value if not NULL */
405
406 END IF; /* If missing at least one of the TO values */
407
408
409
410 /*======================================
411 Start of 41074312 changes.
412 ======================================*/
413
414 /*==========================================
415 Make sure the From uom class is the same
416 as the items base uom class. First get
417 the item uom code and then get the
418 codes class.
419 ==========================================*/
420
421 OPEN get_item_uom;
422 FETCH get_item_uom INTO l_item_uom_code;
423 IF (get_item_uom%NOTFOUND) THEN
424 FND_MESSAGE.SET_NAME('INV','INV_ITEMUOM');
425 FND_MSG_PUB.Add;
426 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
427 END IF;
428 CLOSE get_item_uom;
429
430 /*==========================================
431 Get base class for the item's uom.
432 ==========================================*/
433
434 OPEN get_item_uom_class;
435 FETCH get_item_uom_class INTO l_item_uom_class;
436 IF (get_item_uom_class%NOTFOUND) THEN
437 FND_MESSAGE.SET_NAME('INV','INV_ITEMUOM_CLASS');
438 FND_MSG_PUB.Add;
439 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
440 END IF;
441 CLOSE get_item_uom_class;
442
443 /*==========================================
444 Check if from uom class matches items
445 uom class.
446 ==========================================*/
447
448
449 IF (l_item_uom_class <> l_from_uom_class) THEN
450 FND_MESSAGE.SET_NAME('INV','INV_CLASSMISMATCH');
451 FND_MSG_PUB.Add;
452 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
453 END IF;
454
455
459
456 /*==========================================
457 Get base values for the Input From class.
458 ==========================================*/
460 OPEN get_base_values(l_from_uom_class);
461 FETCH get_base_values INTO l_from_base_unit_of_measure, l_from_base_uom_code;
462 IF (get_base_values%NOTFOUND) THEN
463 FND_MESSAGE.SET_NAME('INV','INV_FROMBASE_ERROR');
464 FND_MSG_PUB.Add;
465 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
466 END IF;
467 CLOSE get_base_values;
468
469
470 /*==========================================
471 If base uom code is what was entered it
472 is ok. Otherwise convert to the base.
473 ==========================================*/
474
475 IF (l_from_base_uom_code <> l_from_uom_code) THEN
476
477 l_from_rate := inv_convert.INV_UM_CONVERT(
478 ITEM_ID => p_lot_uom_conv_rec.inventory_item_id,
479 LOT_NUMBER => p_lot_uom_conv_rec.lot_number,
480 ORGANIZATION_ID => p_lot_uom_conv_rec.organization_id,
481 PRECISION => 38,
482 FROM_QUANTITY => p_lot_uom_conv_rec.conversion_rate,
483 FROM_UNIT => l_from_uom_code,
484 TO_UNIT => l_from_base_uom_code,
485 FROM_NAME => l_from_unit_of_measure,
486 TO_NAME => l_from_base_unit_of_measure
487 );
488
489
490
491 IF (l_ret = -99999) THEN
492 FND_MESSAGE.SET_NAME('INV','INV_FROMBASE_CONV_ERROR');
493 FND_MSG_PUB.Add;
494 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
495 ELSE
496 p_lot_uom_conv_rec.conversion_rate := l_from_rate;
497 l_from_uom_code := l_from_base_uom_code;
498 l_from_unit_of_measure := l_from_base_unit_of_measure;
499 END IF;
500 END IF;
501
502 /*==========================================
503 Get base value for To Uom class.
504 ==========================================*/
505
506
507 OPEN get_base_values(l_to_uom_class);
508 FETCH get_base_values INTO l_to_base_unit_of_measure, l_to_base_uom_code;
509 IF (get_base_values%NOTFOUND) THEN
510 FND_MESSAGE.SET_NAME('INV','INV_TOBASE_ERROR');
511 FND_MSG_PUB.Add;
512 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
513 END IF;
514 CLOSE get_base_values;
515
516
517 /*==========================================
518 If base uom code is what was entered it
519 is ok. Otherwise convert to the base.
520 ==========================================*/
521
522
523 IF (l_to_base_uom_code <> l_to_uom_code) THEN
524
525
526 l_factor := inv_convert.INV_UM_CONVERT(
527 ITEM_ID => p_lot_uom_conv_rec.inventory_item_id,
528 LOT_NUMBER => p_lot_uom_conv_rec.lot_number,
529 ORGANIZATION_ID => p_lot_uom_conv_rec.organization_id,
530 PRECISION => 38,
531 FROM_QUANTITY => 1,
532 FROM_UNIT => l_to_uom_code,
533 TO_UNIT => l_to_base_uom_code,
534 FROM_NAME => l_to_unit_of_measure,
535 TO_NAME => l_to_base_unit_of_measure
536 );
537
538 IF (l_ret = -99999) THEN
539 FND_MESSAGE.SET_NAME('INV','INV_TOBASE_CONV_ERROR');
540 FND_MSG_PUB.Add;
541 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
542 ELSE
543 p_lot_uom_conv_rec.conversion_rate := p_lot_uom_conv_rec.conversion_rate/l_factor;
544 l_to_uom_code := l_to_base_uom_code;
545 l_to_unit_of_measure := l_to_base_unit_of_measure;
546 END IF;
547 END IF;
548
549
550
551 /*==================================
552 End BUG#4107431.
553 ==================================*/
554
555
556 p_lot_uom_conv_rec.from_uom_code := l_from_uom_code;
557 p_lot_uom_conv_rec.from_unit_of_measure := l_from_unit_of_measure;
558 p_lot_uom_conv_rec.from_uom_class := l_from_uom_class;
559
560 p_lot_uom_conv_rec.to_uom_code := l_to_uom_code;
561 p_lot_uom_conv_rec.to_unit_of_measure := l_to_unit_of_measure;
562 p_lot_uom_conv_rec.to_uom_class := l_to_uom_class;
563
564
565
566 /*===============================
567 Validate Conversion Rate
568 -- it is changed and is numeric
569 -- and is greater than zero.
570 ==============================*/
571 IF (p_lot_uom_conv_rec.conversion_rate <= 0 OR
572 p_lot_uom_conv_rec.conversion_rate IS NULL) THEN
573 FND_MESSAGE.SET_NAME('INV','INV_LOTC_CONVRATE_INVALID');
574 FND_MSG_PUB.Add;
575 RAISE FND_API.G_EXC_ERROR;
576 END IF;
577
578 /*===============================
579 Validate Disable Date
580 Check for proper format.
581 ==============================*/
582 IF (p_lot_uom_conv_rec.disable_date IS NOT NULL) THEN
583 --- check for formatting.
584 -- check for disable date passed and update types
585 -- should be suppressed.
586 NULL;
587 END IF;
588
589
590 /*===============================
591 Validate Event Spec.
592 check qc tables.
593 ==============================*/
594
595 /*===============================
596 Validate Reason Code.
597 ==============================*/
598
599 l_ret := G_TRUE;
600 IF (p_reason_id IS NOT NULL) THEN
601 l_reason_id := p_reason_id;
602 l_ret := INV_VALIDATE.reason(l_reason_id);
603 IF (l_ret = INV_VALIDATE.F) THEN
604 FND_MSG_PUB.Add;
608
605 RAISE FND_API.G_EXC_ERROR;
606 END IF;
607 END IF;
609
610 /*===============================
611 Validate Update Type Indicator
612 check against lookup table.
613 ==============================*/
614
615 l_ret := mtl_lot_uom_conv_pvt.validate_update_type(
616 l_update_type_indicator);
617 IF (l_ret = G_FALSE) THEN
618 FND_MSG_PUB.Add;
619 RAISE FND_API.G_EXC_ERROR;
620 END IF;
621
622 /*===============================
623 Validate WHO.
624 Must figure out how to do this.
625 Check fnd_user. if value entered
626 else get default, if none exists
627 then error. populate last on U
628 both create and upd on I.
629 ==============================*/
630 END IF; -- end validation level
631
632
633 /*=============================================
634 Call Business Rules
635 ===========================================*/
636
637 l_tran_seq := x_sequence;
638
639 l_ret := MTL_LOT_UOM_CONV_PVT.validate_lot_conversion_rules
640 ( p_organization_id => p_lot_uom_conv_rec.organization_id
641 , p_inventory_item_id => p_lot_uom_conv_rec.inventory_item_id
642 , p_lot_number => p_lot_uom_conv_rec.lot_number
643 , p_from_uom_code => p_lot_uom_conv_rec.from_uom_code
644 , p_to_uom_code => p_lot_uom_conv_rec.to_uom_code
645 , p_quantity_updates => l_update_type_indicator
646 , p_update_type => p_action_type
647 , p_header_id => l_tran_seq
648 );
649
650
651
652 IF (l_ret = 0) THEN
653 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
654 END IF;
655
656 /*================================================
657 Only update database if process_data flag set.
658 ================================================*/
659
660
661 IF (p_process_data = 'Y') THEN
662 /*=============================================
663 Insert Row to mtl_lot_uom_class_conversions.
664 Perform all database updates.
665 ===========================================*/
666 --bug#12566380
667 BEGIN
668 SELECT conversion_id into l_conversion_id
669 FROM mtl_lot_uom_class_conversions
670 WHERE organization_id = p_lot_uom_conv_rec.organization_id AND
671 inventory_item_id = p_lot_uom_conv_rec.inventory_item_id AND
672 lot_number = p_lot_uom_conv_rec.lot_number AND
673 from_uom_code = p_lot_uom_conv_rec.from_uom_code AND
674 to_uom_code = p_lot_uom_conv_rec.to_uom_code;
675 EXCEPTION
676 WHEN OTHERS THEN
677 l_conversion_id := NULL;
678 END;
679 IF l_conversion_id is not NULL then
680 l_action_type := 'U';
681 ELSE
682 l_action_type := 'I';
683 END IF;
684 --bug#12566380
685
686 l_tran_seq := x_sequence;
687
688 MTL_LOT_UOM_CONV_PVT.process_conversion_data
689 ( -- p_action_type => p_action_type --bug#12566380
690 p_action_type => l_action_type --bug#12566380
691 , p_update_type_indicator => l_update_type_indicator
692 , p_reason_id => p_reason_id
693 , p_batch_id => p_batch_id
694 , p_lot_uom_conv_rec => p_lot_uom_conv_rec
695 , p_qty_update_tbl => p_qty_update_tbl
696 , x_return_status => l_return_status
697 , x_msg_count => l_msg_count
698 , x_msg_data => l_msg_data
699 , x_sequence => l_tran_seq
700 );
701
702 x_sequence := l_tran_seq;
703
704 --Bug#5453231 changed x_return_status to l_return_status
705 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
706 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
707 END IF;
708
709
710 FND_MSG_PUB.Count_AND_GET
711 (p_count => x_msg_count, p_data => x_msg_data);
712
713 END IF; -- process data check
714
715
716
717 /*===============================
718 Process Transactions
719 ===============================*/
720
721 l_tran_seq := x_sequence;
722
723
724 IF (x_sequence IS NOT NULL) THEN
725 l_ret := INV_TXN_MANAGER_PUB.PROCESS_TRANSACTIONS(
726 p_api_version => 1.0,
727 p_init_msg_list => FND_API.G_TRUE,
728 p_commit => FND_API.G_FALSE,
729 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
730 x_return_status => l_return_status,
731 x_msg_count => l_msg_count,
732 x_msg_data => l_msg_data,
733 x_trans_count => l_trans_count,
734 p_table => 2,
735 p_header_id => l_tran_seq);
736
737 -- old code 1 returned IF (l_ret = G_FALSE) THEN
738 -- g_false = 0 inconsistent behaviour between l_ret and l_return_status
739
740 IF (l_ret <> 0) THEN
741 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
742 END IF;
743
744 END IF;
745
746 IF (p_commit = FND_API.G_TRUE) THEN
747 COMMIT;
748 END IF;
749
750 EXCEPTION
751
752 WHEN DO_CHECK_ERROR THEN
753 ROLLBACK TO SAVEPOINT LOT_UOM_CONVERSION;
754 x_return_status := FND_API.G_RET_STS_ERROR;
755 FND_MSG_PUB.Count_AND_GET
756 (p_count => x_msg_count, p_data => x_msg_data);
757
758 WHEN FND_API.G_EXC_ERROR THEN
759 ROLLBACK TO SAVEPOINT LOT_UOM_CONVERSION;
760 x_return_status := FND_API.G_RET_STS_ERROR;
761 FND_MSG_PUB.Count_AND_GET
762 (p_count => x_msg_count, p_data => x_msg_data);
763
764 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
765 ROLLBACK TO SAVEPOINT LOT_UOM_CONVERSION;
766 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
767 FND_MSG_PUB.Count_AND_GET
768 (p_count => x_msg_count, p_data => x_msg_data);
769
770 WHEN OTHERS THEN
771 ROLLBACK TO SAVEPOINT LOT_UOM_CONVERSION;
772 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
773 FND_MSG_PUB.Count_AND_GET
774 (p_count => x_msg_count, p_data => x_msg_data);
775
776 END create_lot_uom_conversion;
777
778
779 PROCEDURE log_msg(p_msg_text IN VARCHAR2) IS
780 BEGIN
781
782 FND_MESSAGE.SET_NAME('GMI','GMI_DEBUG_API');
783 FND_MESSAGE.SET_TOKEN('MSG',p_msg_text);
784 FND_MSG_PUB.Add;
785
786 END log_msg ;
787
788 END MTL_LOT_UOM_CONV_PUB;