[Home] [Help]
PACKAGE BODY: APPS.INV_UOM_API_PUB
Source
1 PACKAGE BODY INV_UOM_API_PUB AS
2 /* $Header: INVUOMPB.pls 120.0.12020000.5 2013/04/26 07:51:35 agembali noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'inv_uom_api_pub';
5 TYPE CHAR_TBL IS TABLE OF VARCHAR2(1500) INDEX BY BINARY_INTEGER;
6 L_DEBUG NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
7
8 -----------------------------------------------
9 -- Procedure
10 -- UOM_CLASS_EXISTS
11 --
12 -- Description
13 -- Function to check whether uom class is already existed or not.
14 -- This function will accept the uom class as input and returns True if it exists
15 -- otherwise False
16 --
17 -- Input Paramters
18 -- P_UOM_CLASS uom_class data.
19 -- Output Parameters
20 -- x_return_status = fnd_api.g_ret_sts_success, if succeeded
21 -- = fnd_api.g_ret_sts_exc_error, if an expected error occurred
22 -- = fnd_api.g_ret_sts_unexp_error, if
23 -- an unexpected error occurred
24 --
25 ---------------------------------------------
26
27 FUNCTION Uom_class_exists(p_uom_class IN VARCHAR2)
28 RETURN BOOLEAN
29 IS
30 CURSOR l_cur IS
31 SELECT 1
32 FROM mtl_uom_classes_tl
33 WHERE uom_class = p_uom_class;
34 l_found BOOLEAN;
35 l_count NUMBER;
36 BEGIN
37 -- validate whether the uom class already exists for the current language
38 l_found := FALSE;
39
40 OPEN l_cur;
41
42 FETCH l_cur INTO l_count;
43
44 l_found := l_cur%FOUND;
45
46 CLOSE l_cur;
47
48 RETURN l_found;
49 END UOM_CLASS_EXISTS;
50
51
52 -----------------------------------------------
53 -- Procedure
54 -- validate_attr_info
55 --
56 -- Description
57 -- Procedure to validate the descriptive flex filed attributes are enabled or not ,
58 -- required or not etc.
59 --
60 -- Input Paramters
61 -- x_return_status
62 --
63 -- p_attribute_category
64 -- p_attributes_tbl
65 -- P_ATTRIBUTES_CNT
66 -- P_DESC_FLEX_NAME
67
68 -- Output Parameters
69 -- x_return_status = fnd_api.g_ret_sts_success, if succeeded
70 -- = fnd_api.g_ret_sts_exc_error, if an expected error occurred
71 -- = fnd_api.g_ret_sts_unexp_error, if
72 -- an unexpected error occurred
73 -- x_msg_count
74 -- X_MSG_DATA
75 ---------------------------------------------
76
77 PROCEDURE VALIDATE_ATTR_INFO(
78 x_return_status OUT NOCOPY VARCHAR2
79 , x_msg_count OUT NOCOPY NUMBER
80 , X_MSG_DATA OUT NOCOPY VARCHAR2
81 , p_attribute_category IN VARCHAR2
82 , p_attributes_tbl IN char_tbl
83 , P_ATTRIBUTES_CNT IN NUMBER
84 , P_DESC_FLEX_NAME IN VARCHAR2
85 ) IS
86 TYPE seg_name IS TABLE OF VARCHAR2(1000)
87 INDEX BY BINARY_INTEGER;
88
89 l_context VARCHAR2(1000);
90 l_context_r fnd_dflex.context_r;
91 l_contexts_dr fnd_dflex.contexts_dr;
92 l_dflex_r fnd_dflex.dflex_r;
93 l_segments_dr fnd_dflex.segments_dr;
94 l_enabled_seg_name seg_name;
95 l_wms_all_segs_tbl seg_name;
96 l_nsegments BINARY_INTEGER;
97 l_global_context BINARY_INTEGER;
98 v_index NUMBER := 1;
99 v_index1 NUMBER := 1;
100 l_chk_flag NUMBER := 0;
101 l_char_count NUMBER;
102 l_num_count NUMBER;
103 l_date_count NUMBER;
104 l_wms_attr_chk NUMBER := 1;
105 l_return_status VARCHAR2(1);
106 l_msg_count NUMBER;
107 l_msg_data VARCHAR2(1000);
108
109 /* Variables used for Validate_desccols procedure */
110 error_segment VARCHAR2(30);
111 errors_received EXCEPTION;
112 error_msg VARCHAR2(5000);
113 s NUMBER;
114 e NUMBER;
115 l_null_char_val VARCHAR2(1000);
116 l_null_num_val NUMBER;
117 l_null_date_val DATE;
118 l_global_nsegments NUMBER := 0;
119 col NUMBER;
120 BEGIN
121
122 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
123
124 SAVEPOINT get_lot_attr_information;
125
126 /* Populate the flex field record */
127
128 L_DFLEX_R.APPLICATION_ID := 401;
129 l_dflex_r.flexfield_name := P_DESC_FLEX_NAME ; -- 'MTL_UOM_CLASSES';
130 /* Get all contexts */
131
132 IF (L_DEBUG = 1) THEN
133 INV_LOG_UTIL.TRACE('Before getting context', 'inv_uom_api_pub', '9');
134 END IF;
135
136
137 FND_DFLEX.GET_CONTEXTS(FLEXFIELD => L_DFLEX_R, CONTEXTS => L_CONTEXTS_DR);
138
139 -- dbms_output.put_line('after Getting context');
140
141 /* From the l_contexts_dr, get the position of the global context */
142 l_global_context := l_contexts_dr.global_context;
143
144
145 /* Using the position get the segments in the global context which are enabled */
146 l_context := l_contexts_dr.context_code(l_global_context);
147
148 IF (L_DEBUG = 1) THEN
149 INV_LOG_UTIL.TRACE('l_context:' || L_CONTEXT, 'inv_uom_api_pub', '9');
150 END IF;
151
152
153 /* Prepare the context_r type for getting the segments associated with the global context */
154 l_context_r.flexfield := l_dflex_r;
155 L_CONTEXT_R.CONTEXT_CODE := L_CONTEXT;
156
157
158 fnd_dflex.get_segments(CONTEXT => l_context_r, segments => l_segments_dr, enabled_only => TRUE);
159
160
161 /* read through the segments */
162 l_nsegments := l_segments_dr.nsegments;
163 l_global_nsegments := l_segments_dr.nsegments;
164
165 --DBMS_output.put_line('The number of enabled segments for the Global Context are ' || l_nsegments);
166
167 IF (p_attributes_cnt > l_nsegments) AND
168 p_attribute_category IS NULL THEN
169
170 IF (L_DEBUG = 1) THEN
171 INV_LOG_UTIL.TRACE('Parameter attribute count greater than segments in DFF:', 'inv_uom_api_pub', '9');
172 END IF;
173
174 /* user passed more parameters than needed by global data elements,
175 * even though context is passed as null. hence error out
176 */
177 --DBMS_output.put_line('more params passed than needed');
178 fnd_message.set_name('FND', 'FLEX-INVALID CONTEXT');
179 fnd_message.set_token('CONTEXT', 'NULL');
180 fnd_message.set_token('ROUTINE', 'inv_uom_api_pub');
181 fnd_msg_pub.ADD;
182 RAISE fnd_api.g_exc_error;
183 END IF;
184
185
186 IF (L_DEBUG = 1) THEN
187 INV_LOG_UTIL.TRACE('Before for loop:', 'inv_uom_api_pub', '9');
188 END IF;
189
190 FOR i IN 1 .. l_nsegments LOOP
191 L_ENABLED_SEG_NAME(V_INDEX) := L_SEGMENTS_DR.APPLICATION_COLUMN_NAME(I);
192
193 IF l_segments_dr.is_required(i) THEN
194 col := SUBSTR(l_segments_dr.application_column_name(i)
195 , INSTR(l_segments_dr.application_column_name(i), 'ATTRIBUTE') + 9);
196 --DBMS_output.put_line('col is ' || col);
197 IF ((p_attributes_tbl.EXISTS(col) AND p_attributes_tbl(col) = fnd_api.g_miss_char) OR
198 NOT p_attributes_tbl.EXISTS(col))
199 THEN
200 --DBMS_output.put_line('y r we here');
201 fnd_message.set_name('INV', 'INV_REQ_SEG_MISS');
202 fnd_message.set_token('SEGMENT', l_segments_dr.segment_name(i));
203 fnd_msg_pub.ADD;
204 RAISE fnd_api.g_exc_error;
205 END IF;
206 ELSE
207 --DBMS_output.put_line('This segment is not required');
208 NULL;
209 END IF;
210
211 IF p_attributes_tbl.EXISTS(SUBSTR(l_segments_dr.application_column_name(i)
212 , INSTR(l_segments_dr.application_column_name(i), 'ATTRIBUTE') + 9)) THEN
213 fnd_flex_descval.set_column_value(
214 l_segments_dr.application_column_name(i)
215 , p_attributes_tbl(SUBSTR(l_segments_dr.application_column_name(i)
216 , INSTR(l_segments_dr.application_column_name(i), 'ATTRIBUTE') + 9))
217 );
218 ELSE
219 fnd_flex_descval.set_column_value(l_segments_dr.application_column_name(i), l_null_char_val);
220 END IF;
221
222 v_index := v_index + 1;
223 END LOOP;
224
225
226 IF (L_DEBUG = 1) THEN
227 INV_LOG_UTIL.TRACE('After for loop:', 'inv_uom_api_pub', '9');
228 END IF;
229
230 IF l_enabled_seg_name.COUNT > 0 THEN
231 FOR i IN l_enabled_seg_name.FIRST .. l_enabled_seg_name.LAST LOOP
232
233 IF (L_DEBUG = 1) THEN
234 INV_LOG_UTIL.TRACE('l_enabled_seg_name:' || l_enabled_seg_name(i), 'inv_uom_api_pub', '9');
235 END IF;
236
237 NULL;
238 END LOOP;
239 END IF;
240
241 /* Initialise the l_context_value to null */
242 l_context := NULL;
243 l_nsegments := 0;
244
245 IF (L_DEBUG = 1) THEN
246 INV_LOG_UTIL.TRACE('p_attribute_category:' || p_attribute_category, 'inv_uom_api_pub', '9');
247 END IF;
248
249 /*Get the context for the item passed */
250 IF p_attribute_category IS NOT NULL THEN
251
252 L_CONTEXT := P_ATTRIBUTE_CATEGORY;
253
254 IF (L_DEBUG = 1) THEN
255 INV_LOG_UTIL.TRACE('p_attribute_category:' || P_ATTRIBUTE_CATEGORY, 'inv_uom_api_pub', '9');
256 END IF;
257
258 /* Set flex context for validation of the value set */
259 fnd_flex_descval.set_context_value(l_context);
260
261 /* Prepare the context_r type */
262 l_context_r.flexfield := l_dflex_r;
263 l_context_r.context_code := l_context;
264 fnd_dflex.get_segments(CONTEXT => l_context_r, segments => l_segments_dr, enabled_only => TRUE);
265 /* read through the segments */
266 l_nsegments := l_segments_dr.nsegments;
267
268 FOR i IN 1 .. l_nsegments LOOP
269 l_enabled_seg_name(v_index) := l_segments_dr.application_column_name(i);
270
271 IF (L_DEBUG = 1) THEN
272 INV_LOG_UTIL.TRACE('l_segments_dr.appl_column_name'||i||l_segments_dr.application_column_name(i), 'inv_uom_api_pub', '9');
273 END IF;
274
275 IF l_segments_dr.is_required(i) THEN
276 col := SUBSTR(l_segments_dr.application_column_name(i)
277 , INSTR(l_segments_dr.application_column_name(i), 'ATTRIBUTE') + 9);
278 --DBMS_output.put_line('col is ' || col);
279 IF ((p_attributes_tbl.EXISTS(col) AND p_attributes_tbl(col) IS NULL) OR
280 NOT p_attributes_tbl.EXISTS(col))
281 THEN
282 fnd_message.set_name('INV', 'INV_REQ_SEG_MISS');
283 fnd_message.set_token('SEGMENT', l_segments_dr.segment_name(i));
284 fnd_msg_pub.ADD;
285 RAISE fnd_api.g_exc_error;
286 --DBMS_output.put_line('Req segment is not populated');
287 END IF;
288 END IF;
289
290 IF p_attributes_tbl.EXISTS(SUBSTR(l_segments_dr.application_column_name(i)
291 , INSTR(l_segments_dr.application_column_name(i), 'ATTRIBUTE') + 9)) THEN
292 fnd_flex_descval.set_column_value(
293 l_segments_dr.application_column_name(i)
294 , p_attributes_tbl(SUBSTR(l_segments_dr.application_column_name(i)
295 , INSTR(l_segments_dr.application_column_name(i), 'ATTRIBUTE') + 9))
296 );
297 ELSE
298 fnd_flex_descval.set_column_value(l_segments_dr.application_column_name(i), l_null_char_val);
299 END IF;
300
301 v_index := v_index + 1;
302 END LOOP;
303
304 END IF;
305
306
307 /*Make a call to FND_FLEX_DESCVAL.validate_desccols */
308 IF (L_GLOBAL_NSEGMENTS > 0 AND P_ATTRIBUTE_CATEGORY IS NULL ) THEN
309
310 l_context := l_contexts_dr.context_code(l_global_context);
311
312 FND_FLEX_DESCVAL.SET_CONTEXT_VALUE(L_CONTEXT);
313
314 END IF;
315
316 IF( l_global_nsegments > 0 OR p_attribute_category IS NOT NULL )
317 THEN
318 --DBMS_output.put_line('global segments > 0 or attrib cat is not null');
319 IF FND_FLEX_DESCVAL.VALIDATE_DESCCOLS(APPL_SHORT_NAME => 'INV',
320 DESC_FLEX_NAME => P_DESC_FLEX_NAME ,
321 VALUES_OR_IDS => 'I' ,
322 validation_date => SYSDATE) THEN
323
324 --DBMS_output.put_line('Value set validation successful');
325
326 NULL;
327 ELSE
328
329 ERROR_SEGMENT := FND_FLEX_DESCVAL.ERROR_SEGMENT;
330 -- x_req_error_flag := 'Y';
331 -- dbms_output.put_line('error_segment:'||error_segment);
332
333 IF (L_DEBUG = 1) THEN
334 INV_LOG_UTIL.TRACE('ERROR_SEGMENT'||ERROR_SEGMENT, 'inv_uom_api_pub', '9');
335 END IF;
336
337 RAISE errors_received;
338
339 END IF;
340 END IF; /*If P attribute category is not null */
341 --END IF; /* p_attribute_category IS NOT NULL */
342
343
344 IF (L_DEBUG = 1) THEN
345 INV_LOG_UTIL.TRACE('End of validate_attr_info', 'inv_uom_api_pub', '9');
346 END IF;
347
348 EXCEPTION
349 WHEN ERRORS_RECEIVED THEN
350 x_return_status := fnd_api.g_ret_sts_error;
351 error_msg := fnd_flex_descval.error_message;
352 s := 1;
353 e := 200;
354
355 --DBMS_output.put_line('Here are the error messages: ');
356 WHILE e < 5001
357 AND SUBSTR(error_msg, s, e) IS NOT NULL LOOP
358 fnd_message.set_name('INV', 'INV_FND_GENERIC_MSG');
359 fnd_message.set_token('MSG', SUBSTR(error_msg, s, e));
360 FND_MSG_PUB.ADD;
361
362 s := s + 200;
363 e := e + 200;
364 END LOOP;
365
366 ROLLBACK TO get_lot_attr_information;
367 FND_MSG_PUB.COUNT_AND_GET(P_ENCODED => FND_API.G_FALSE, P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA);
368 X_MSG_DATA := FND_MSG_PUB.GET(X_MSG_COUNT, 'F');
369
370 IF (L_DEBUG = 1) THEN
371 INV_LOG_UTIL.TRACE('errors_received exception:'||X_MSG_DATA, 'inv_uom_api_pub', '9');
372 END IF;
373
374 WHEN fnd_api.g_exc_error THEN
375 x_return_status := fnd_api.g_ret_sts_error;
376 ROLLBACK TO get_lot_attr_information;
377 FND_MSG_PUB.COUNT_AND_GET(P_ENCODED => FND_API.G_FALSE, P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA);
378
379 WHEN fnd_api.g_exc_unexpected_error THEN
380 x_return_status := fnd_api.g_ret_sts_unexp_error;
381 ROLLBACK TO get_lot_attr_information;
382 FND_MSG_PUB.COUNT_AND_GET(P_ENCODED => FND_API.G_FALSE, P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA);
383
384 WHEN OTHERS THEN
385 x_return_status := fnd_api.g_ret_sts_unexp_error;
386 ROLLBACK TO get_lot_attr_information;
387 FND_MSG_PUB.COUNT_AND_GET(P_ENCODED => FND_API.G_FALSE, P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA);
388
389 END validate_attr_info;
390
391 -----------------------------------------------
392 -- Procedure
393 -- UOM_Class
394 --
395 -- Description
396 -- Creates UOM class data.
397 --
398 -- Input Paramters
399 -- P_UOM_CLASS uom_class data.
400 -- P_DESCRIPTION class description.
401 -- p_base_unit_of_measure base unit of measure.
402 -- p_base_uom_code base uom code.
403 -- P_UOM_DESCRIPTION unit of measure description
404 -- p_disable_date In active date of class.
405 -- p_attribute 1-15 DFF of Unit of measure classes.
406 -- P_ATTRIBUTE_CATEGORY Context of UOM Class
407 -- P_UOM_ATTRIBUTE_CATEGORY Context of Unit of Measure
408 -- P_UOM_ATTRIBUTE 1 - 15 DFF of Define Unit of Measures
409 -- p_action_type Object type of UOM class that whether uom class need to insert or update
410 -- i.e 'I' or 'U'
411 -- Output Parameters
412 -- x_return_status = fnd_api.g_ret_sts_success, if succeeded
413 -- = fnd_api.g_ret_sts_exc_error, if an expected error occurred
414 -- = fnd_api.g_ret_sts_unexp_error, if an unexpected error occurred
415 --
416 -- x_msg_count = Message count
417 -- x_msg_data = Message text
418 --
419 --------------------------------------------
420
421
422 PROCEDURE UOM_Class( P_UOM_CLASS IN VARCHAR2,
423 P_DESCRIPTION IN VARCHAR2,
424 P_BASE_UNIT_OF_MEASURE IN VARCHAR2,
425 P_BASE_UOM_CODE IN VARCHAR2,
426 P_UOM_DESCRIPTION IN VARCHAR2,
427 P_DISABLE_DATE IN DATE ,
428 P_ATTRIBUTE_CATEGORY IN VARCHAR2 ,
429 P_ATTRIBUTE1 IN VARCHAR2 ,
430 P_ATTRIBUTE2 IN VARCHAR2 ,
431 P_ATTRIBUTE3 IN VARCHAR2 ,
432 P_ATTRIBUTE4 IN VARCHAR2 ,
433 P_ATTRIBUTE5 IN VARCHAR2 ,
434 P_ATTRIBUTE6 IN VARCHAR2 ,
435 P_ATTRIBUTE7 IN VARCHAR2 ,
436 P_ATTRIBUTE8 IN VARCHAR2 ,
437 P_ATTRIBUTE9 IN VARCHAR2 ,
438 P_ATTRIBUTE10 IN VARCHAR2 ,
439 P_ATTRIBUTE11 IN VARCHAR2 ,
440 P_ATTRIBUTE12 IN VARCHAR2 ,
441 P_ATTRIBUTE13 IN VARCHAR2 ,
442 P_ATTRIBUTE14 IN VARCHAR2,
443 P_ATTRIBUTE15 IN VARCHAR2,
444 P_UOM_ATTRIBUTE_CATEGORY IN VARCHAR2,
445 P_UOM_ATTRIBUTE1 IN VARCHAR2,
446 P_UOM_ATTRIBUTE2 IN VARCHAR2,
447 P_UOM_ATTRIBUTE3 IN VARCHAR2,
448 P_UOM_ATTRIBUTE4 IN VARCHAR2,
449 p_UOM_attribute5 IN VARCHAR2,
450 P_UOM_ATTRIBUTE6 IN VARCHAR2,
451 P_UOM_ATTRIBUTE7 IN VARCHAR2,
452 P_UOM_ATTRIBUTE8 IN VARCHAR2,
453 P_UOM_ATTRIBUTE9 IN VARCHAR2,
454 P_UOM_ATTRIBUTE10 IN VARCHAR2,
455 P_UOM_ATTRIBUTE11 IN VARCHAR2,
456 P_UOM_ATTRIBUTE12 IN VARCHAR2,
457 P_UOM_ATTRIBUTE13 IN VARCHAR2,
458 P_UOM_ATTRIBUTE14 IN VARCHAR2,
459 P_UOM_ATTRIBUTE15 IN VARCHAR2,
460 p_action_type IN VARCHAR2,
461 X_MSG_COUNT OUT NOCOPY NUMBER,
462 X_MSG_DATA OUT NOCOPY VARCHAR2,
463 X_RETURN_STATUS OUT NOCOPY VARCHAR2 )
464 IS
465 L_FOUND BOOLEAN;
466 L_SUCCESS VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
467 -- L_UNIT_OF_MEASURE VARCHAR2(25);
468 L_UNIT_OF_MEASURE MTL_UNITS_OF_MEASURE_TL.UNIT_OF_MEASURE%TYPE;
469
470 -- L_UOM_CODE VARCHAR2(3);
471
472 L_UOM_CODE MTL_UNITS_OF_MEASURE_TL.UOM_CODE%type;
473
474 -- p_uom_class_tl VARCHAR2(10);
475 p_uom_class_tl MTL_UOM_CLASSES_TL.UOM_CLASS_TL%type;
476 L_ROW_ID VARCHAR2(40);
477 P_API_VERSION_NUMBER NUMBER := 1.0;
478 P_INIT_MSG_LST VARCHAR2(50);
479
480 L_USERID NUMBER := FND_GLOBAL.USER_ID;
481 L_LOGINID NUMBER := FND_GLOBAL.LOGIN_ID;
482
483 L_INV_ATTRIBUTES_TBL CHAR_TBL;
484 L_UOM_ATTRIBUTES_TBL CHAR_TBL;
485 -- CNT NUMBER;
486 l_att_count number;
487 L_RETURN_STATUS VARCHAR2(10);
488 L_MSG_COUNT NUMBER;
489 l_msg_data VARCHAR2(4000);
490
491 L_INVALID_OBJECT_TYPE EXCEPTION ;
492 L_APPLICATION_COLUMN_NAME VARCHAR2(10);
493
494 l_req_error_flag VARCHAR2(30);
495 L_DESC_FLEX_NAME VARCHAR2(30) := 'MTL_UOM_CLASSES';
496
497 L_FLAG VARCHAR2(2) := 'N';
498
499 CURSOR ENABLED_SEG_CUR
500 IS
501 SELECT APPLICATION_COLUMN_NAME
502 FROM FND_DESCR_FLEX_COL_USAGE_VL
503 WHERE DESCRIPTIVE_FLEXFIELD_NAME = L_DESC_FLEX_NAME
504 AND ENABLED_FLAG = 'Y'
505 ORDER BY TO_NUMBER(SUBSTR(APPLICATION_COLUMN_NAME, 10)) ;
506
507 BEGIN
508
509 p_uom_class_tl := P_UOM_CLASS;
510
511 -- first if p_action_type = 'I'
512 IF p_action_type = 'I' THEN
513
514 IF (L_DEBUG = 1) THEN
515 INV_LOG_UTIL.TRACE('Entered into the UOM_Class', 'inv_uom_api_pub', '9');
516 INV_LOG_UTIL.TRACE('P_UOM_CLASS:' || P_UOM_CLASS, 'inv_uom_api_pub', '9');
517 INV_LOG_UTIL.TRACE('testing..', 'inv_uom_api_pub', '9');
518 END IF;
519
520
521 IF (L_DEBUG = 1) THEN
522 INV_LOG_UTIL.TRACE('Entered into the UOM_Class', 'inv_uom_api_pub', '9');
523 INV_LOG_UTIL.TRACE('L_USERID:' || L_USERID, 'inv_uom_api_pub', '9');
524 INV_LOG_UTIL.TRACE('L_LOGINID:' || L_LOGINID, 'inv_uom_api_pub', '9');
525 END IF;
526
527 -- Validating P_UOM_CLASS
528 BEGIN
529
530 L_FOUND := UOM_CLASS_EXISTS(P_UOM_CLASS);
531
532 IF L_FOUND THEN
533
534 IF (L_DEBUG = 1) THEN
535 INV_LOG_UTIL.TRACE('Uom class exists:', 'inv_uom_api_pub', '9');
536 END IF;
537
538 FND_MESSAGE.SET_NAME('INV', 'INV_DUP');
539 FND_MESSAGE.SET_TOKEN('VALUE1', P_UOM_CLASS);
540 FND_MSG_PUB.ADD;
541 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
542
543 RAISE FND_API.G_EXC_ERROR;
544 END IF; -- UOM class already exists.
545
546 END;
547 -- End of Validating P_UOM_CLASS
548
549 IF (L_DEBUG = 1) THEN
550 INV_LOG_UTIL.TRACE('P_DISABLE_DATE:' || P_DISABLE_DATE, 'inv_uom_api_pub', '9');
551 END IF;
552
553
554 IF P_DISABLE_DATE IS NOT NULL AND TRUNC(P_DISABLE_DATE) < TRUNC(SYSDATE) THEN
555
556 FND_MESSAGE.SET_NAME('INV', 'INV_DISABLE_DATE');
557 FND_MSG_PUB.ADD;
558 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
559 RAISE FND_API.G_EXC_ERROR;
560
561 END IF;
562
563 -- Validating p_base_unit_of_measure and p_base_uom_code
564 IF (P_BASE_UNIT_OF_MEASURE IS NULL OR P_BASE_UOM_CODE IS NULL) THEN
565
566 -- FND_MESSAGE.SET_NAME('INV', 'INV_UOM_NOTFOUND');
567 FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_PARAMETER_TYPE');
568 FND_MESSAGE.SET_TOKEN('VALUE1', 'P_BASE_UNIT_OF_MEASURE OR P_BASE_UOM_CODE');
569 FND_MSG_PUB.ADD;
570
571 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
572 RAISE FND_API.G_EXC_ERROR;
573 -- The unit of measure and base uom code for this uom class need to be passed.
574 END IF;
575
576
577 BEGIN
578
579 IF LENGTH(P_BASE_UOM_CODE) > 3 THEN
580
581 IF (L_DEBUG = 1) THEN
582 INV_LOG_UTIL.TRACE('UOM code exceeds its length 3 ', 'inv_uom_api_pub', '9');
583 END IF;
584
585 FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_PARAMETER_TYPE');
586 FND_MESSAGE.SET_TOKEN('VALUE1', 'P_BASE_UOM_CODE');
587 FND_MSG_PUB.ADD;
588
589 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
590 RAISE FND_API.G_EXC_ERROR;
591
592 END IF;
593
594 END;
595
596
597 BEGIN
598
599 SELECT UNIT_OF_MEASURE
600 INTO L_UNIT_OF_MEASURE
601 FROM MTL_UNITS_OF_MEASURE_VL
602 WHERE UNIT_OF_MEASURE_TL = P_BASE_UNIT_OF_MEASURE
603 AND NVL(DISABLE_DATE, TRUNC(SYSDATE) + 1) > TRUNC(SYSDATE);
604
605 EXCEPTION
606 WHEN NO_DATA_FOUND THEN
607 NULL;
608 END ;
609
610 IF (L_DEBUG = 1) THEN
611 INV_LOG_UTIL.TRACE('l_unit_of_measure:' || L_UNIT_OF_MEASURE, 'inv_uom_api_pub', '9');
612 INV_LOG_UTIL.TRACE('l_uom_code:' || L_UOM_CODE, 'inv_uom_api_pub', '9');
613 END IF;
614
615 IF ( L_UNIT_OF_MEASURE IS NOT NULL) THEN
616 FND_MESSAGE.SET_NAME('INV', 'INV_UNIT_EXISTS');
617 FND_MESSAGE.SET_TOKEN('VALUE1', L_UNIT_OF_MEASURE);
618 FND_MSG_PUB.ADD;
619 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
620 RAISE FND_API.G_EXC_ERROR;
621 -- Already unit of measure and uom code exists with some other class exists.
622 END IF;
623
624
625 BEGIN
626
627 SELECT UOM_CODE
628 INTO L_UOM_CODE
629 FROM MTL_UNITS_OF_MEASURE_VL
630 WHERE UOM_CODE = P_BASE_UOM_CODE
631 AND NVL(DISABLE_DATE, TRUNC(SYSDATE) + 1) > TRUNC(SYSDATE);
632
633 EXCEPTION
634 WHEN NO_DATA_FOUND THEN
635 NULL;
636 END ;
637
638
639 IF ( L_UOM_CODE IS NOT NULL) THEN
640
641 FND_MESSAGE.SET_NAME('INV', 'INV_UNIT_EXISTS');
642 FND_MESSAGE.SET_TOKEN('VALUE1', L_UOM_CODE);
643 FND_MSG_PUB.ADD;
644 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
645 RAISE FND_API.G_EXC_ERROR;
646 -- Already unit of measure and uom code exists with some other class exists.
647 END IF;
648
649
650 END IF ; -- end of first if p_action_type = 'I'
651
652 IF (L_DEBUG = 1) THEN
653 INV_LOG_UTIL.TRACE('p_action_type:' || p_action_type, 'inv_uom_api_pub', '9');
654 END IF;
655
656 IF p_action_type = 'U' THEN
657
658 BEGIN
659
660 L_FOUND := UOM_CLASS_EXISTS(P_UOM_CLASS);
661
662 IF NOT L_FOUND THEN
663
664 IF (L_DEBUG = 1) THEN
665 INV_LOG_UTIL.TRACE('UOM Class does not exists', 'inv_uom_api_pub', '9');
666 END IF;
667
668 -- FND_MESSAGE.SET_NAME('INV', 'INV_UOM_NOTFOUND'); -- added now
669 -- FND_MSG_PUB.ADD;
670 FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_PARAMETER_TYPE');
671 FND_MESSAGE.SET_TOKEN('VALUE1', 'P_UOM_CLASS');
672 FND_MSG_PUB.ADD;
673
674 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
675 RAISE fnd_api.g_exc_error;
676
677 END IF;
678
679 END;
680
681 -- End of Validating P_UOM_CLASS
682
683 -- DBMS_OUTPUT.PUT_LINE('L_FOUND: False i.e uom class does not exists');
684
685
686 BEGIN
687
688 IF LENGTH(P_BASE_UOM_CODE) > 3 THEN
689
690 IF (L_DEBUG = 1) THEN
691 INV_LOG_UTIL.TRACE('UOM code exceeds its length 3 ', 'inv_uom_api_pub', '9');
692 END IF;
693
694 FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_PARAMETER_TYPE');
695 FND_MESSAGE.SET_TOKEN('VALUE1', 'P_BASE_UOM_CODE');
696 FND_MSG_PUB.ADD;
697
698 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
699 RAISE FND_API.G_EXC_ERROR;
700
701 END IF;
702
703 END;
704
705
706
707 IF P_DISABLE_DATE IS NOT NULL AND TRUNC(P_DISABLE_DATE) < TRUNC(SYSDATE) THEN
708
709 IF (L_DEBUG = 1) THEN
710 INV_LOG_UTIL.TRACE('Disable date is past date', 'inv_uom_api_pub', '9');
711 END IF;
712
713 FND_MESSAGE.SET_NAME('INV', 'INV_DISABLE_DATE');
714 FND_MSG_PUB.ADD;
715 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
716 RAISE FND_API.G_EXC_ERROR;
717
718 END IF;
719
720 -- Validating p_base_unit_of_measure and p_base_uom_code
721 IF (P_BASE_UNIT_OF_MEASURE IS NULL OR P_BASE_UOM_CODE IS NULL) THEN
722
723 -- FND_MESSAGE.SET_NAME('INV', 'INV_UOM_NOTFOUND');
724 FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_PARAMETER_TYPE');
725 FND_MESSAGE.SET_TOKEN('VALUE1', 'P_BASE_UNIT_OF_MEASURE OR P_BASE_UOM_CODE');
726 FND_MSG_PUB.ADD;
727
728 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
729 RAISE FND_API.G_EXC_ERROR;
730 -- The unit of measure and base uom code for this uom class need to be passed.
731 END IF;
732
733 BEGIN
734
735 SELECT UNIT_OF_MEASURE,
736 UOM_CODE
737 INTO L_UNIT_OF_MEASURE ,
738 L_UOM_CODE
739 FROM MTL_UNITS_OF_MEASURE_VL
740 WHERE UOM_CLASS = P_UOM_CLASS
741 AND UNIT_OF_MEASURE_TL = P_BASE_UNIT_OF_MEASURE
742 AND UOM_CODE = P_BASE_UOM_CODE ;
743
744 EXCEPTION
745 WHEN NO_DATA_FOUND THEN
746 FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_COMB_MSG');
747 -- FND_MESSAGE.SET_TOKEN('UOM', 'P_UOM_CODE'); -- added now
748 FND_MSG_PUB.ADD;
749 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
750 RAISE FND_API.G_EXC_ERROR;
751 END ;
752
753 IF (L_DEBUG = 1) THEN
754 INV_LOG_UTIL.TRACE('l_unit_of_measure:' || L_UNIT_OF_MEASURE, 'inv_uom_api_pub', '9');
755 INV_LOG_UTIL.TRACE('l_uom_code:' || L_UOM_CODE, 'inv_uom_api_pub', '9');
756 END IF;
757
758 IF ( L_UNIT_OF_MEASURE IS NULL OR L_UOM_CODE IS NULL) THEN
759 -- DBMS_OUTPUT.PUT_LINE('unit of measure already exists');
760 FND_MESSAGE.SET_NAME('INV', 'INV_UNIT_EXISTS');
761 FND_MESSAGE.SET_TOKEN('VALUE1', L_UNIT_OF_MEASURE);
762 FND_MSG_PUB.ADD;
763 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
764 RAISE FND_API.G_EXC_ERROR;
765 -- Already unit of measure and uom code exists with some other class exists.
766 END IF;
767
768 END IF; -- end of p_action_type = 'U'
769
770 IF p_action_type NOT IN('U', 'I') THEN
771
772 FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_PARAMETER_TYPE');
773 fnd_message.set_token('VALUE1', 'p_action_type');
774 FND_MSG_PUB.ADD;
775
776 RAISE L_INVALID_OBJECT_TYPE;
777
778 END IF;
779
780 l_att_count := 0;
781 IF p_attribute1 IS NOT NULL THEN
782 l_att_count := l_att_count + 1;
783 END IF;
784 IF p_attribute2 IS NOT NULL THEN
785 l_att_count := l_att_count + 1;
786 END IF;
787 IF p_attribute3 IS NOT NULL THEN
788 l_att_count := l_att_count + 1;
789 END IF;
790 IF p_attribute4 IS NOT NULL THEN
791 l_att_count := l_att_count + 1;
792 END IF;
793 IF p_attribute5 IS NOT NULL THEN
794 l_att_count := l_att_count + 1;
795 END IF;
796 IF p_attribute6 IS NOT NULL THEN
797 l_att_count := l_att_count + 1;
798 END IF;
799 IF p_attribute7 IS NOT NULL THEN
800 l_att_count := l_att_count + 1;
801 END IF;
802 IF p_attribute8 IS NOT NULL THEN
803 l_att_count := l_att_count + 1;
804 END IF;
805 IF p_attribute9 IS NOT NULL THEN
806 l_att_count := l_att_count + 1;
807 END IF;
808 IF p_attribute10 IS NOT NULL THEN
809 l_att_count := l_att_count + 1;
810 END IF;
811 IF p_attribute11 IS NOT NULL THEN
812 l_att_count := l_att_count + 1;
813 END IF;
814 IF p_attribute12 IS NOT NULL THEN
815 l_att_count := l_att_count + 1;
816 END IF;
817 IF p_attribute13 IS NOT NULL THEN
818 l_att_count := l_att_count + 1;
819 END IF;
820 IF p_attribute14 IS NOT NULL THEN
821 l_att_count := l_att_count + 1;
822 END IF;
823 IF p_attribute15 IS NOT NULL THEN
824 l_att_count := l_att_count + 1;
825 END IF;
826
827 IF (L_DEBUG = 1) THEN
828 INV_LOG_UTIL.TRACE('No.of attributes passed to create class:'|| l_att_count, 'inv_uom_api_pub', '9');
829 END IF;
830
831 l_inv_attributes_tbl (1) := p_attribute1;
832 l_inv_attributes_tbl (2) := p_attribute2;
833 l_inv_attributes_tbl (3) := p_attribute3;
834 l_inv_attributes_tbl (4) := p_attribute4;
835 l_inv_attributes_tbl (5) := p_attribute5;
836 l_inv_attributes_tbl (6) := p_attribute6;
837 l_inv_attributes_tbl (7) := p_attribute7;
838 l_inv_attributes_tbl (8) := p_attribute8;
839 l_inv_attributes_tbl (9) := p_attribute9;
840 l_inv_attributes_tbl (10) := p_attribute10;
841 l_inv_attributes_tbl (11) := p_attribute11;
842 l_inv_attributes_tbl (12) := p_attribute12;
843 l_inv_attributes_tbl (13) := p_attribute13;
844 l_inv_attributes_tbl (14) := p_attribute14;
845 l_inv_attributes_tbl (15) := p_attribute15;
846
847 validate_attr_info(
848 x_return_status => l_return_status
849 , x_msg_count => l_msg_count
850 , X_MSG_DATA => L_MSG_DATA
851 , p_attribute_category => p_attribute_category
852 , p_attributes_tbl => l_inv_attributes_tbl
853 , P_ATTRIBUTES_CNT => l_att_count
854 , P_DESC_FLEX_NAME => L_DESC_FLEX_NAME
855 );
856
857 IF (L_DEBUG = 1) THEN
858 INV_LOG_UTIL.TRACE('l_return_status:' || l_return_status, 'inv_uom_api_pub', '9');
859 INV_LOG_UTIL.TRACE('L_MSG_DATA:' || L_MSG_DATA, 'inv_uom_api_pub', '9');
860 END IF;
861
862 IF L_RETURN_STATUS = 'E' THEN
863 RAISE fnd_api.g_exc_error;
864 ELSIF l_return_status = 'U' THEN
865 RAISE fnd_api.g_exc_unexpected_error;
866 END IF;
867
868
869 -- Defaulting the DFF attribute parameters to null if the corresponding attributes are not enabled
870
871 FOR I IN 1..15 LOOP
872
873 L_FLAG := 'N';
874 FOR J IN ENABLED_SEG_CUR
875 LOOP
876
877 IF (I = SUBSTR(J.APPLICATION_COLUMN_NAME, 10)) THEN
878 L_FLAG := 'Y';
879 END IF;
880
881 END LOOP;
882
883 IF L_FLAG = 'N' THEN
884 L_INV_ATTRIBUTES_TBL (I) := NULL;
885 L_FLAG := 'N';
886 END IF;
887
888 END LOOP;
889
890 -- Code logic to insert MTL_UOM_CLASSES_TL table. (user installed languages)
891
892 -- second if p_action_type = 'I'
893 IF p_action_type = 'I' THEN
894
895 begin
896
897 mtl_uom_classes_tl_pkg.insert_row(x_rowid => l_row_id,
898 x_uom_class => p_uom_class,
899 x_uom_class_tl => p_uom_class_tl,
900 x_description => p_description,
901 x_disable_date => p_disable_date,
902 x_attribute_category => p_attribute_category,
903 x_attribute1 => L_INV_ATTRIBUTES_TBL(1) ,
904 x_attribute2 => L_INV_ATTRIBUTES_TBL(2) ,
905 x_attribute3 => L_INV_ATTRIBUTES_TBL(3) ,
906 x_attribute4 => L_INV_ATTRIBUTES_TBL(4) ,
907 x_attribute5 => L_INV_ATTRIBUTES_TBL(5) ,
908 X_ATTRIBUTE6 => L_INV_ATTRIBUTES_TBL(6) ,
909 X_ATTRIBUTE7 => L_INV_ATTRIBUTES_TBL(7) ,
910 x_attribute8 => L_INV_ATTRIBUTES_TBL(8) ,
911 X_ATTRIBUTE9 => L_INV_ATTRIBUTES_TBL(9) ,
912 X_ATTRIBUTE10 => L_INV_ATTRIBUTES_TBL(10) ,
913 X_ATTRIBUTE11 => L_INV_ATTRIBUTES_TBL(11) ,
914 X_ATTRIBUTE12 => L_INV_ATTRIBUTES_TBL(12) ,
915 X_ATTRIBUTE13 => L_INV_ATTRIBUTES_TBL(13) ,
916 X_ATTRIBUTE14 => L_INV_ATTRIBUTES_TBL(14) ,
917 x_attribute15 => L_INV_ATTRIBUTES_TBL(15) ,
918 x_request_id => To_number(NULL),
919 x_program_id => To_number(NULL),
920 X_PROGRAM_APPLICATION_ID => TO_NUMBER(NULL),
921 x_program_update_date => sysdate,
922 x_creation_date => Sysdate,
923 x_created_by => L_USERID,
924 x_last_update_date => Sysdate,
925 x_last_updated_by => L_USERID,
926 X_LAST_UPDATE_LOGIN => L_LOGINID
927 );
928
929 EXCEPTION
930 WHEN OTHERS THEN
931 RAISE FND_API.G_EXC_ERROR;
932 END;
933
934 IF (L_DEBUG = 1) THEN
935 INV_LOG_UTIL.TRACE('After inserting uom class, X_RETURN_STATUS:'|| X_RETURN_STATUS, 'inv_uom_api_pub', '9');
936 END IF;
937
938 IF NOT (X_RETURN_STATUS = L_SUCCESS) THEN
939 APP_EXCEPTION.RAISE_EXCEPTION;
940 END IF;
941
942 -- End of Code logic to insert MTL_UOM_CLASSES_TL table.
943 -- Code logic to insert MTL_UNITS_OF_MEASURE table. (user installed languages)
944 -- From this procedure always base_unit_of_measure gets inserted into unit of measure table.
945 -- DBMS_OUTPUT.PUT_LINE('Before calling UOM insert');
946
947 L_DESC_FLEX_NAME := 'MTL_UNITS_OF_MEASURE';
948
949 l_att_count := 0;
950 IF p_uom_attribute1 IS NOT NULL THEN
951 l_att_count := l_att_count + 1;
952 END IF;
953 IF p_uom_attribute2 IS NOT NULL THEN
954 l_att_count := l_att_count + 1;
955 END IF;
956 IF P_UOM_ATTRIBUTE3 IS NOT NULL THEN
957 l_att_count := l_att_count + 1;
958 END IF;
959 IF P_UOM_ATTRIBUTE4 IS NOT NULL THEN
960 l_att_count := l_att_count + 1;
961 END IF;
962 IF p_uom_attribute5 IS NOT NULL THEN
963 l_att_count := l_att_count + 1;
964 END IF;
965 IF P_UOM_ATTRIBUTE6 IS NOT NULL THEN
966 l_att_count := l_att_count + 1;
967 END IF;
968 IF P_UOM_ATTRIBUTE7 IS NOT NULL THEN
969 l_att_count := l_att_count + 1;
970 END IF;
971 IF P_UOM_ATTRIBUTE8 IS NOT NULL THEN
972 l_att_count := l_att_count + 1;
973 END IF;
974 IF P_UOM_ATTRIBUTE9 IS NOT NULL THEN
975 l_att_count := l_att_count + 1;
976 END IF;
977 IF P_UOM_ATTRIBUTE10 IS NOT NULL THEN
978 l_att_count := l_att_count + 1;
979 END IF;
980 IF P_UOM_ATTRIBUTE11 IS NOT NULL THEN
981 l_att_count := l_att_count + 1;
982 END IF;
983 IF P_UOM_ATTRIBUTE12 IS NOT NULL THEN
984 l_att_count := l_att_count + 1;
985 END IF;
986 IF P_UOM_ATTRIBUTE13 IS NOT NULL THEN
987 l_att_count := l_att_count + 1;
988 END IF;
989 IF P_UOM_ATTRIBUTE14 IS NOT NULL THEN
990 l_att_count := l_att_count + 1;
991 END IF;
992 IF P_UOM_ATTRIBUTE15 IS NOT NULL THEN
993 l_att_count := l_att_count + 1;
994 END IF;
995
996 IF (L_DEBUG = 1) THEN
997 INV_LOG_UTIL.TRACE('No.of Attributes passed to create uom:'|| l_att_count, 'inv_uom_api_pub', '9');
998 END IF;
999
1000 l_uom_attributes_tbl (1) := p_uom_attribute1;
1001 L_UOM_ATTRIBUTES_TBL (2) := P_UOM_ATTRIBUTE2;
1002 L_UOM_ATTRIBUTES_TBL (3) := P_UOM_ATTRIBUTE3;
1003 L_UOM_ATTRIBUTES_TBL (4) := P_UOM_ATTRIBUTE4;
1004 L_UOM_ATTRIBUTES_TBL (5) := P_UOM_ATTRIBUTE5;
1005 L_UOM_ATTRIBUTES_TBL (6) := P_UOM_ATTRIBUTE6;
1006 L_UOM_ATTRIBUTES_TBL (7) := P_UOM_ATTRIBUTE7;
1007 L_UOM_ATTRIBUTES_TBL (8) := P_UOM_ATTRIBUTE8;
1008 L_UOM_ATTRIBUTES_TBL (9) := P_UOM_ATTRIBUTE9;
1009 L_UOM_ATTRIBUTES_TBL (10) := P_UOM_ATTRIBUTE10;
1010 L_UOM_ATTRIBUTES_TBL (11) := P_UOM_ATTRIBUTE11;
1011 L_UOM_ATTRIBUTES_TBL (12) := P_UOM_ATTRIBUTE12;
1012 L_UOM_ATTRIBUTES_TBL (13) := P_UOM_ATTRIBUTE13;
1013 L_UOM_ATTRIBUTES_TBL (14) := P_UOM_ATTRIBUTE14;
1014 L_uom_ATTRIBUTES_TBL (15) := P_UOM_ATTRIBUTE15;
1015
1016
1017 validate_attr_info(
1018 x_return_status => l_return_status
1019 , x_msg_count => l_msg_count
1020 , X_MSG_DATA => L_MSG_DATA
1021 , p_attribute_category => p_uom_attribute_category
1022 , p_attributes_tbl => L_UOM_ATTRIBUTES_TBL
1023 , P_ATTRIBUTES_CNT => l_att_count
1024 , P_DESC_FLEX_NAME => L_DESC_FLEX_NAME
1025 );
1026
1027 IF (L_DEBUG = 1) THEN
1028 INV_LOG_UTIL.TRACE('l_return_status:' || l_return_status, 'inv_uom_api_pub', '9');
1029 INV_LOG_UTIL.TRACE('L_MSG_DATA:' || L_MSG_DATA, 'inv_uom_api_pub', '9');
1030 END IF;
1031
1032 IF l_return_status = 'E' THEN
1033 --DBMS_output.put_line('Error from validate_loc_attr_info');
1034 RAISE fnd_api.g_exc_error;
1035 ELSIF l_return_status = 'U' THEN
1036 --DBMS_output.put_line('Unexpected Error from validate_loc_attr_info');
1037 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1038 END IF;
1039
1040 -- Defaulting the DFF attribute parameters to null if the corresponding attributes are not enabled
1041
1042 FOR I IN 1..15 LOOP
1043
1044 L_FLAG := 'N';
1045 FOR J IN ENABLED_SEG_CUR
1046 LOOP
1047
1048 IF (I = SUBSTR(J.APPLICATION_COLUMN_NAME, 10)) THEN
1049 L_FLAG := 'Y';
1050 END IF;
1051
1052 END LOOP;
1053
1054 IF L_FLAG = 'N' THEN
1055 L_UOM_ATTRIBUTES_TBL (I) := NULL;
1056 L_FLAG := 'N';
1057 END IF;
1058
1059 END LOOP;
1060
1061 MTL_UNITS_OF_MEASURE_TL_PKG.INSERT_ROW(X_ROW_ID => L_ROW_ID,
1062 X_UNIT_OF_MEASURE => P_BASE_UNIT_OF_MEASURE ,
1063 X_UNIT_OF_MEASURE_TL => P_BASE_UNIT_OF_MEASURE,
1064 X_ATTRIBUTE_CATEGORY => P_UOM_ATTRIBUTE_CATEGORY,
1065 X_ATTRIBUTE1 => L_UOM_ATTRIBUTES_TBL(1),
1066 X_ATTRIBUTE2 => L_UOM_ATTRIBUTES_TBL(2),
1067 X_ATTRIBUTE3 => L_UOM_ATTRIBUTES_TBL(3),
1068 X_ATTRIBUTE4 => L_UOM_ATTRIBUTES_TBL(4),
1069 X_ATTRIBUTE5 => L_UOM_ATTRIBUTES_TBL(5),
1070 X_ATTRIBUTE6 => L_UOM_ATTRIBUTES_TBL(6),
1071 X_ATTRIBUTE7 => L_UOM_ATTRIBUTES_TBL(7),
1072 X_ATTRIBUTE8 => L_UOM_ATTRIBUTES_TBL(8),
1073 X_ATTRIBUTE9 => L_UOM_ATTRIBUTES_TBL(9),
1074 X_ATTRIBUTE10 => L_UOM_ATTRIBUTES_TBL(10),
1075 X_ATTRIBUTE11 => L_UOM_ATTRIBUTES_TBL(11),
1076 X_ATTRIBUTE12 => L_UOM_ATTRIBUTES_TBL(12),
1077 X_ATTRIBUTE13 => L_UOM_ATTRIBUTES_TBL(13),
1078 X_ATTRIBUTE14 => L_UOM_ATTRIBUTES_TBL(14),
1079 X_ATTRIBUTE15 => L_UOM_ATTRIBUTES_TBL(15),
1080 X_REQUEST_ID => To_number(NULL),
1081 X_DISABLE_DATE => P_DISABLE_DATE,
1082 X_BASE_UOM_FLAG => 'Y',
1083 X_UOM_CODE => P_BASE_UOM_CODE,
1084 X_UOM_CLASS => P_UOM_CLASS,
1085 X_DESCRIPTION => P_UOM_DESCRIPTION,
1086 X_CREATION_DATE => SYSDATE,
1087 X_CREATED_BY => L_USERID,
1088 X_LAST_UPDATE_DATE => SYSDATE ,
1089 X_LAST_UPDATED_BY => L_USERID,
1090 X_LAST_UPDATE_LOGIN => L_LOGINID,
1091 X_PROGRAM_APPLICATION_ID => TO_NUMBER(NULL),
1092 X_PROGRAM_ID => TO_NUMBER(NULL),
1093 X_PROGRAM_UPDATE_DATE => SYSDATE );
1094
1095
1096 -- End of Code logic to insert MTL_UNITS_OF_MEASURE table.
1097 -- Code logic to insert MTL_UOM_CONVERSIONS table.
1098 BEGIN
1099 INSERT
1100 INTO MTL_UOM_CONVERSIONS
1101 (
1102 INVENTORY_ITEM_ID,
1103 UNIT_OF_MEASURE,
1104 UOM_CODE,
1105 UOM_CLASS,
1106 LAST_UPDATE_DATE,
1107 LAST_UPDATED_BY,
1108 CREATION_DATE,
1109 CREATED_BY,
1110 LAST_UPDATE_LOGIN,
1111 CONVERSION_RATE,
1112 DEFAULT_CONVERSION_FLAG
1113 )
1114 VALUES
1115 (
1116 0,
1117 P_BASE_UNIT_OF_MEASURE,
1118 P_BASE_UOM_CODE,
1119 P_UOM_CLASS,
1120 SYSDATE,
1121 L_USERID , --fnd_global.user_id,
1122 SYSDATE,
1123 L_USERID, -- fnd_global.user_id,
1124 L_LOGINID,
1125 1,
1126 'N'
1127 );
1128 END;
1129 -- End of Code logic to insert MTL_UOM_CONVERSIONS table.
1130 -- Code logic to insert MTL_UOM_CLASS_CONVERSIONS table.
1131 BEGIN
1132 INSERT
1133 INTO MTL_UOM_CLASS_CONVERSIONS
1134 (
1135 INVENTORY_ITEM_ID,
1136 FROM_UNIT_OF_MEASURE,
1137 FROM_UOM_CODE,
1138 FROM_UOM_CLASS,
1139 TO_UNIT_OF_MEASURE,
1140 TO_UOM_CODE,
1141 TO_UOM_CLASS,
1142 LAST_UPDATE_DATE,
1143 LAST_UPDATED_BY,
1144 CREATION_DATE,
1145 CREATED_BY,
1146 LAST_UPDATE_LOGIN,
1147 CONVERSION_RATE
1148 )
1149 VALUES
1150 (
1151 0,
1152 P_BASE_UNIT_OF_MEASURE,
1153 P_BASE_UOM_CODE,
1154 P_UOM_CLASS,
1155 P_BASE_UNIT_OF_MEASURE,
1156 P_BASE_UOM_CODE,
1157 P_UOM_CLASS,
1158 SYSDATE,
1159 L_USERID , -- fnd_global.user_id,
1160 SYSDATE,
1161 L_USERID , -- fnd_global.user_id,
1162 L_LOGINID,
1163 1
1164 );
1165 END;
1166
1167 END IF; -- second if p_action_type = 'I'
1168
1169 IF p_action_type = 'U' THEN
1170
1171 BEGIN
1172
1173 MTL_UOM_CLASSES_TL_PKG.UPDATE_ROW ( X_UOM_CLASS => P_UOM_CLASS,
1174 X_UOM_CLASS_TL => P_UOM_CLASS_TL,
1175 X_DESCRIPTION => P_DESCRIPTION,
1176 x_disable_date => p_disable_date,
1177 x_attribute_category => p_attribute_category,
1178 x_attribute1 => L_INV_ATTRIBUTES_TBL(1) ,
1179 x_attribute2 => L_INV_ATTRIBUTES_TBL(2) ,
1180 x_attribute3 => L_INV_ATTRIBUTES_TBL(3) ,
1181 x_attribute4 => L_INV_ATTRIBUTES_TBL(4) ,
1182 x_attribute5 => L_INV_ATTRIBUTES_TBL(5) ,
1183 X_ATTRIBUTE6 => L_INV_ATTRIBUTES_TBL(6) ,
1184 X_ATTRIBUTE7 => L_INV_ATTRIBUTES_TBL(7) ,
1185 x_attribute8 => L_INV_ATTRIBUTES_TBL(8) ,
1186 X_ATTRIBUTE9 => L_INV_ATTRIBUTES_TBL(9) ,
1187 X_ATTRIBUTE10 => L_INV_ATTRIBUTES_TBL(10) ,
1188 X_ATTRIBUTE11 => L_INV_ATTRIBUTES_TBL(11) ,
1189 X_ATTRIBUTE12 => L_INV_ATTRIBUTES_TBL(12) ,
1190 X_ATTRIBUTE13 => L_INV_ATTRIBUTES_TBL(13) ,
1191 X_ATTRIBUTE14 => L_INV_ATTRIBUTES_TBL(14) ,
1192 x_attribute15 => L_INV_ATTRIBUTES_TBL(15) ,
1193 x_request_id => To_number(NULL),
1194 x_last_update_date => Sysdate,
1195 x_last_updated_by => L_USERID,
1196 X_LAST_UPDATE_LOGIN => L_LOGINID
1197 );
1198 EXCEPTION
1199 WHEN OTHERS THEN
1200 RAISE FND_API.G_EXC_ERROR;
1201 END;
1202
1203 IF (L_DEBUG = 1) THEN
1204 INV_LOG_UTIL.TRACE('After update uom class , X_RETURN_STATUS:'|| X_RETURN_STATUS, 'inv_uom_api_pub', '9');
1205 END IF;
1206
1207 IF X_RETURN_STATUS <> 'S' THEN
1208 RAISE FND_API.G_EXC_ERROR;
1209 END IF;
1210
1211 END IF;
1212
1213 -- End of Code logic to insert MTL_UOM_CLASS_CONVERSIONS table.
1214 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS ;
1215
1216 COMMIT;
1217
1218 EXCEPTION
1219 WHEN L_INVALID_OBJECT_TYPE THEN
1220
1221 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1222 FND_MSG_PUB.COUNT_AND_GET
1223 ( P_COUNT => X_MSG_COUNT
1224 , P_DATA => X_MSG_DATA
1225 );
1226
1227 X_MSG_DATA := FND_MSG_PUB.GET(X_MSG_COUNT, 'F');
1228
1229 WHEN FND_API.G_EXC_ERROR THEN
1230 ROLLBACK;
1231 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1232
1233 -- Get message count and data
1234 fnd_msg_pub.count_and_get
1235 ( p_count => x_msg_count
1236 , p_data => x_msg_data
1237 );
1238 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1239 ROLLBACK;
1240 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
1241
1242 -- Get message count and data
1243 fnd_msg_pub.count_and_get
1244 ( p_count => x_msg_count
1245 , p_data => x_msg_data
1246 );
1247 WHEN OTHERS THEN
1248 ROLLBACK;
1249 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
1250
1251 -- Get message count and data
1252 fnd_msg_pub.count_and_get
1253 ( p_count => x_msg_count
1254 , P_DATA => X_MSG_DATA
1255 );
1256
1257 END UOM_Class;
1258
1259 --------------------------------------------
1260 -- Procedure
1261 -- Unit_Of_Measure
1262 --
1263 -- Description
1264 -- Creates Unit of measure data.
1265 --
1266 -- Input Paramters
1267 -- P_UOM_CLASS uom_class data.
1268 -- P_DESCRIPTION uom class description.
1269 -- p_unit_of_measure unit of measure.
1270 -- p_uom_code uom code.
1271 -- p_disable_date InActive date of unit of measure.
1272 -- P_ATTRIBUTE_CATEGORY Context of Unit of Measure
1273 -- p_attribute 1-15 DFF of Define Unit of measure.
1274 -- p_action_type Object type whether 'I' or 'U'
1275 -- Output Parameters
1276 -- x_return_status = fnd_api.g_ret_sts_success, if succeeded
1277 -- = fnd_api.g_ret_sts_exc_error, if an expected error occurred
1278 -- = fnd_api.g_ret_sts_unexp_error, if an unexpected error occurred
1279 --
1280 -- x_msg_count = Message count
1281 -- x_msg_data = Message text
1282 --
1283 -----------------------------------------------
1284
1285 PROCEDURE Unit_Of_Measure( P_UOM_CLASS IN VARCHAR2,
1286 P_DESCRIPTION IN VARCHAR2,
1287 P_UNIT_OF_MEASURE IN VARCHAR2,
1288 P_UOM_CODE IN VARCHAR2,
1289 P_DISABLE_DATE IN DATE ,
1290 P_ATTRIBUTE_CATEGORY IN VARCHAR2,
1291 P_ATTRIBUTE1 IN VARCHAR2,
1292 P_ATTRIBUTE2 IN VARCHAR2,
1293 P_ATTRIBUTE3 IN VARCHAR2,
1294 P_ATTRIBUTE4 IN VARCHAR2,
1295 P_ATTRIBUTE5 IN VARCHAR2,
1296 P_ATTRIBUTE6 IN VARCHAR2,
1297 P_ATTRIBUTE7 IN VARCHAR2,
1298 P_ATTRIBUTE8 IN VARCHAR2,
1299 P_ATTRIBUTE9 IN VARCHAR2,
1300 P_ATTRIBUTE10 IN VARCHAR2,
1301 P_ATTRIBUTE11 IN VARCHAR2,
1302 P_ATTRIBUTE12 IN VARCHAR2,
1303 P_ATTRIBUTE13 IN VARCHAR2,
1304 P_ATTRIBUTE14 IN VARCHAR2,
1305 P_ATTRIBUTE15 IN VARCHAR2,
1306 p_action_type IN VARCHAR2,
1307 X_MSG_COUNT OUT NOCOPY NUMBER,
1308 X_MSG_DATA OUT NOCOPY VARCHAR2,
1309 X_RETURN_STATUS OUT NOCOPY VARCHAR2
1310 )
1311 IS
1312 L_BASE_UOM_FLAG VARCHAR2(10);
1313
1314 -- L_UNIT_OF_MEASURE VARCHAR2(25);
1315
1316 L_UNIT_OF_MEASURE MTL_UNITS_OF_MEASURE_TL.UNIT_OF_MEASURE%TYPE;
1317
1318 -- L_UOM_CODE VARCHAR2(3) ;
1319 L_UOM_CODE MTL_UNITS_OF_MEASURE_TL.UOM_CODE%type;
1320
1321 L_FOUND BOOLEAN;
1322 L_ROW_ID VARCHAR2( 40) ;
1323 -- P_CREATED_BY NUMBER := -1;
1324
1325 L_DESC_FLEX_NAME VARCHAR2(30) := 'MTL_UNITS_OF_MEASURE' ;
1326
1327 L_USERID NUMBER := FND_GLOBAL.USER_ID;
1328 L_LOGINID NUMBER := FND_GLOBAL.LOGIN_ID;
1329
1330 L_INV_ATTRIBUTES_TBL CHAR_TBL;
1331 -- CNT NUMBER;
1332 l_att_count number;
1333 L_RETURN_STATUS VARCHAR2(10);
1334 L_MSG_COUNT NUMBER;
1335 L_MSG_DATA VARCHAR2(4000);
1336
1337 L_RET_CONV_EXISTS_WARNING CONSTANT VARCHAR2(1) := 'W' ;
1338
1339 l_req_error_flag VARCHAR2(30);
1340
1341 L_FLAG VARCHAR2(2) := 'N';
1342
1343 L_INVALID_OBJECT_TYPE EXCEPTION ;
1344 l_class_count number;
1345
1346 CURSOR ENABLED_SEG_CUR
1347 IS
1348 SELECT APPLICATION_COLUMN_NAME
1349 FROM FND_DESCR_FLEX_COL_USAGE_VL
1350 WHERE DESCRIPTIVE_FLEXFIELD_NAME = L_DESC_FLEX_NAME
1351 AND ENABLED_FLAG = 'Y'
1352 ORDER BY TO_NUMBER(SUBSTR(APPLICATION_COLUMN_NAME, 10)) ;
1353
1354 BEGIN
1355
1356 -- DBMS_OUTPUT.PUT_LINE('Entered into Unit_Of_Measure');
1357
1358 -- Validating P_UOM_CLASS
1359 L_FOUND := UOM_CLASS_EXISTS(P_UOM_CLASS);
1360
1361 IF L_FOUND THEN
1362
1363 -- L_BASE_UOM_FLAG := 'N';
1364 IF (L_DEBUG = 1) THEN
1365 INV_LOG_UTIL.TRACE('UOM class exists', 'inv_uom_api_pub', '9');
1366 END IF;
1367
1368 -- The unit of measure and uom code are not base uom.
1369 ELSE
1370
1371 -- FND_MESSAGE.SET_NAME('INV', 'INV_UOM_NOTFOUND');
1372 -- FND_MSG_PUB.ADD;
1373 FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_PARAMETER_TYPE');
1374 FND_MESSAGE.SET_TOKEN('VALUE1', 'P_UOM_CLASS');
1375 FND_MSG_PUB.ADD;
1376 L_BASE_UOM_FLAG := 'Y';
1377 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1378 RAISE FND_API.G_EXC_ERROR;
1379
1380 END IF;
1381 -- End of Validating P_UOM_CLASS
1382
1383
1384 BEGIN
1385
1386 IF LENGTH(P_UOM_CODE) > 3 THEN
1387
1388 IF (L_DEBUG = 1) THEN
1389 INV_LOG_UTIL.TRACE('UOM code exceeds its length 3 ', 'inv_uom_api_pub', '9');
1390 END IF;
1391
1392 FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_PARAMETER_TYPE');
1393 FND_MESSAGE.SET_TOKEN('VALUE1', 'P_UOM_CODE');
1394 FND_MSG_PUB.ADD;
1395
1396 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1397 RAISE FND_API.G_EXC_ERROR;
1398
1399 END IF;
1400
1401 END;
1402
1403
1404 IF p_action_type = 'U' THEN
1405
1406 IF P_UNIT_OF_MEASURE IS NULL THEN
1407
1408 FND_MESSAGE.SET_NAME('INV', 'INV_UOM_NOTFOUND');
1409 FND_MESSAGE.SET_TOKEN('UOM', 'P_UNIT_OF_MEASURE'); -- added now
1410 FND_MSG_PUB.ADD;
1411 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1412 RAISE FND_API.G_EXC_ERROR;
1413 -- The unit of measure and base uom code for this uom class need to be passed.
1414
1415 END IF;
1416
1417 IF P_UOM_CODE IS NULL THEN
1418
1419 FND_MESSAGE.SET_NAME('INV', 'INV_UOM_NOTFOUND');
1420 FND_MESSAGE.SET_TOKEN('UOM', 'P_UOM_CODE'); -- added now
1421 FND_MSG_PUB.ADD;
1422 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1423 RAISE FND_API.G_EXC_ERROR;
1424 -- The unit of measure and base uom code for this uom class need to be passed.
1425
1426 END IF;
1427
1428
1429 IF P_DISABLE_DATE IS NOT NULL AND TRUNC(P_DISABLE_DATE) < TRUNC(SYSDATE) THEN
1430
1431 FND_MESSAGE.SET_NAME('INV', 'INV_DISABLE_DATE');
1432 FND_MSG_PUB.ADD;
1433 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1434 RAISE FND_API.G_EXC_ERROR;
1435
1436 END IF;
1437
1438 BEGIN
1439
1440 SELECT UNIT_OF_MEASURE , UOM_CODE , BASE_UOM_FLAG
1441 INTO L_UNIT_OF_MEASURE , L_UOM_CODE , L_BASE_UOM_FLAG
1442 FROM MTL_UNITS_OF_MEASURE_VL
1443 WHERE UOM_CLASS = P_UOM_CLASS
1444 AND NVL(DISABLE_DATE, TRUNC(SYSDATE) + 1) > TRUNC(SYSDATE)
1445 AND (UNIT_OF_MEASURE_TL = P_UNIT_OF_MEASURE
1446 OR UOM_CODE = P_UOM_CODE) ;
1447
1448 EXCEPTION
1449 WHEN NO_DATA_FOUND THEN
1450 FND_MESSAGE.SET_NAME('INV', 'INV_UOM_NOTFOUND');
1451 -- FND_MESSAGE.SET_TOKEN('UOM', 'P_UOM_CODE'); -- added now
1452 FND_MSG_PUB.ADD;
1453 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1454 RAISE FND_API.G_EXC_ERROR;
1455 END ;
1456
1457 END IF;
1458
1459 IF p_action_type = 'I' THEN -- start object type if
1460
1461 L_BASE_UOM_FLAG := 'N';
1462
1463 begin
1464 SELECT 1
1465 into l_class_count
1466 FROM MTL_UOM_CLASSES
1467 WHERE UOM_CLASS = P_UOM_CLASS
1468 and NVL(DISABLE_DATE, TRUNC(SYSDATE) + 1) > TRUNC(SYSDATE);
1469 EXCEPTION
1470 WHEN NO_DATA_FOUND THEN
1471 FND_MESSAGE.SET_NAME('INV', 'INV_UOM_NOTFOUND');
1472 FND_MSG_PUB.ADD;
1473 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1474 RAISE fnd_api.g_exc_error;
1475
1476 end;
1477
1478
1479 -- Validating p_base_unit_of_measure and p_uom_code
1480 IF P_UNIT_OF_MEASURE IS NULL THEN
1481
1482 FND_MESSAGE.SET_NAME('INV', 'INV_UOM_NOTFOUND');
1483 FND_MESSAGE.SET_TOKEN('UOM', 'P_UNIT_OF_MEASURE'); -- added now
1484 FND_MSG_PUB.ADD;
1485 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1486 RAISE FND_API.G_EXC_ERROR;
1487 -- The unit of measure and base uom code for this uom class need to be passed.
1488
1489 END IF;
1490
1491 IF P_UOM_CODE IS NULL THEN
1492
1493 FND_MESSAGE.SET_NAME('INV', 'INV_UOM_NOTFOUND');
1494 FND_MESSAGE.SET_TOKEN('UOM', 'P_UOM_CODE'); -- added now
1495 FND_MSG_PUB.ADD;
1496 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1497 RAISE FND_API.G_EXC_ERROR;
1498 -- The unit of measure and base uom code for this uom class need to be passed.
1499
1500 END IF;
1501
1502
1503 IF P_DISABLE_DATE IS NOT NULL AND TRUNC(P_DISABLE_DATE) < TRUNC(SYSDATE) THEN
1504
1505 FND_MESSAGE.SET_NAME('INV', 'INV_DISABLE_DATE');
1506 FND_MSG_PUB.ADD;
1507 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1508 RAISE FND_API.G_EXC_ERROR;
1509
1510 END IF;
1511
1512 BEGIN
1513
1514 SELECT UNIT_OF_MEASURE , UOM_CODE
1515 INTO L_UNIT_OF_MEASURE , L_UOM_CODE
1516 FROM MTL_UNITS_OF_MEASURE_VL
1517 WHERE UNIT_OF_MEASURE_TL = P_UNIT_OF_MEASURE
1518 -- AND uom_code = p_uom_code
1519 OR UOM_CODE = P_UOM_CODE
1520 AND NVL(DISABLE_DATE, TRUNC(SYSDATE) + 1) > TRUNC(SYSDATE);
1521
1522 /*
1523 DBMS_OUTPUT.PUT_LINE('l_unit_of_measure:'||L_UNIT_OF_MEASURE);
1524 DBMS_OUTPUT.PUT_LINE('l_uom_code:'||l_uom_code);
1525 */
1526 EXCEPTION
1527 WHEN NO_DATA_FOUND THEN
1528 -- DBMS_OUTPUT.PUT_LINE('uom and uom_code does not exists');
1529 NULL;
1530 END ;
1531
1532 IF L_UNIT_OF_MEASURE IS NOT NULL OR L_UOM_CODE IS NOT NULL THEN
1533
1534 --DBMS_OUTPUT.PUT_LINE('Raising exception');
1535
1536 FND_MESSAGE.SET_NAME('INV', 'INV_UNIT_EXISTS');
1537 FND_MESSAGE.SET_TOKEN('VALUE1', L_UNIT_OF_MEASURE);
1538 FND_MSG_PUB.ADD;
1539 RAISE FND_API.G_EXC_ERROR;
1540
1541 --fnd_message.raise_error;
1542 -- Already unit of measure and uom code exists with some other class exists.
1543
1544 END IF;
1545 -- end of Validating p_unit_of_measure and p_uom_code
1546 -- Code logic to insert MTL_UNITS_OF_MEASURE table. (user installed languages)
1547 -- From this procedure unit_of_measure gets inserted into unit of measure table.
1548
1549 END IF;
1550
1551 l_att_count := 0;
1552 IF p_attribute1 IS NOT NULL THEN
1553 l_att_count := l_att_count + 1;
1554 END IF;
1555 IF p_attribute2 IS NOT NULL THEN
1556 l_att_count := l_att_count + 1;
1557 END IF;
1558 IF p_attribute3 IS NOT NULL THEN
1559 l_att_count := l_att_count + 1;
1560 END IF;
1561 IF p_attribute4 IS NOT NULL THEN
1562 l_att_count := l_att_count + 1;
1563 END IF;
1564 IF p_attribute5 IS NOT NULL THEN
1565 l_att_count := l_att_count + 1;
1566 END IF;
1567 IF p_attribute6 IS NOT NULL THEN
1568 l_att_count := l_att_count + 1;
1569 END IF;
1570 IF p_attribute7 IS NOT NULL THEN
1571 l_att_count := l_att_count + 1;
1572 END IF;
1573 IF p_attribute8 IS NOT NULL THEN
1574 l_att_count := l_att_count + 1;
1575 END IF;
1576 IF p_attribute9 IS NOT NULL THEN
1577 l_att_count := l_att_count + 1;
1578 END IF;
1579 IF p_attribute10 IS NOT NULL THEN
1580 l_att_count := l_att_count + 1;
1581 END IF;
1582 IF p_attribute11 IS NOT NULL THEN
1583 l_att_count := l_att_count + 1;
1584 END IF;
1585 IF p_attribute12 IS NOT NULL THEN
1586 l_att_count := l_att_count + 1;
1587 END IF;
1588 IF p_attribute13 IS NOT NULL THEN
1589 l_att_count := l_att_count + 1;
1590 END IF;
1591 IF p_attribute14 IS NOT NULL THEN
1592 l_att_count := l_att_count + 1;
1593 END IF;
1594 IF p_attribute15 IS NOT NULL THEN
1595 l_att_count := l_att_count + 1;
1596 END IF;
1597
1598 l_inv_attributes_tbl (1) := p_attribute1;
1599 l_inv_attributes_tbl (2) := p_attribute2;
1600 l_inv_attributes_tbl (3) := p_attribute3;
1601 l_inv_attributes_tbl (4) := p_attribute4;
1602 l_inv_attributes_tbl (5) := p_attribute5;
1603 l_inv_attributes_tbl (6) := p_attribute6;
1604 l_inv_attributes_tbl (7) := p_attribute7;
1605 l_inv_attributes_tbl (8) := p_attribute8;
1606 l_inv_attributes_tbl (9) := p_attribute9;
1607 l_inv_attributes_tbl (10) := p_attribute10;
1608 l_inv_attributes_tbl (11) := p_attribute11;
1609 l_inv_attributes_tbl (12) := p_attribute12;
1610 l_inv_attributes_tbl (13) := p_attribute13;
1611 l_inv_attributes_tbl (14) := p_attribute14;
1612 l_inv_attributes_tbl (15) := p_attribute15;
1613
1614 validate_attr_info(
1615 x_return_status => l_return_status
1616 , x_msg_count => l_msg_count
1617 , X_MSG_DATA => L_MSG_DATA
1618 , p_attribute_category => p_attribute_category
1619 , p_attributes_tbl => l_inv_attributes_tbl
1620 , P_ATTRIBUTES_CNT => l_att_count
1621 , P_DESC_FLEX_NAME => L_DESC_FLEX_NAME
1622 );
1623
1624 IF L_RETURN_STATUS = 'E' THEN
1625 RAISE FND_API.G_EXC_ERROR;
1626 ELSIF L_RETURN_STATUS = 'U' THEN
1627 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1628 END IF;
1629
1630 -- Defaulting the DFF attribute parameters to null if the corresponding attributes are not enabled
1631
1632 FOR I IN 1..15 LOOP
1633
1634 L_FLAG := 'N';
1635 FOR J IN ENABLED_SEG_CUR
1636 LOOP
1637
1638 IF (I = SUBSTR(J.APPLICATION_COLUMN_NAME, 10)) THEN
1639 L_FLAG := 'Y';
1640 END IF;
1641
1642 END LOOP;
1643
1644 IF L_FLAG = 'N' THEN
1645 L_INV_ATTRIBUTES_TBL (I) := NULL;
1646 L_FLAG := 'N';
1647 END IF;
1648
1649 END LOOP;
1650
1651 IF p_action_type = 'I' THEN
1652
1653 MTL_UNITS_OF_MEASURE_TL_PKG.INSERT_ROW(X_ROW_ID => L_ROW_ID,
1654 X_UNIT_OF_MEASURE => P_UNIT_OF_MEASURE ,
1655 X_UNIT_OF_MEASURE_TL => P_UNIT_OF_MEASURE,
1656 X_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY,
1657 X_ATTRIBUTE1 => L_INV_ATTRIBUTES_TBL(1) ,
1658 X_ATTRIBUTE2 => L_INV_ATTRIBUTES_TBL(2) ,
1659 X_ATTRIBUTE3 => L_INV_ATTRIBUTES_TBL(3) ,
1660 X_ATTRIBUTE4 => L_INV_ATTRIBUTES_TBL(4) ,
1661 X_ATTRIBUTE5 => L_INV_ATTRIBUTES_TBL(5) ,
1662 X_ATTRIBUTE6 => L_INV_ATTRIBUTES_TBL(6) ,
1663 X_ATTRIBUTE7 => L_INV_ATTRIBUTES_TBL(7) ,
1664 X_ATTRIBUTE8 => L_INV_ATTRIBUTES_TBL(8) ,
1665 X_ATTRIBUTE9 => L_INV_ATTRIBUTES_TBL(9) ,
1666 X_ATTRIBUTE10 => L_INV_ATTRIBUTES_TBL(10) ,
1667 X_ATTRIBUTE11 => L_INV_ATTRIBUTES_TBL(11) ,
1668 X_ATTRIBUTE12 => L_INV_ATTRIBUTES_TBL(12) ,
1669 X_ATTRIBUTE13 => L_INV_ATTRIBUTES_TBL(13) ,
1670 X_ATTRIBUTE14 => L_INV_ATTRIBUTES_TBL(14) ,
1671 X_ATTRIBUTE15 => l_inv_attributes_tbl(15) ,
1672 X_REQUEST_ID => TO_NUMBER(NULL),
1673 X_DISABLE_DATE => P_DISABLE_DATE,
1674 X_BASE_UOM_FLAG => L_BASE_UOM_FLAG,
1675 X_UOM_CODE => P_UOM_CODE,
1676 X_UOM_CLASS => P_UOM_CLASS,
1677 X_DESCRIPTION => P_DESCRIPTION,
1678 X_CREATION_DATE => SYSDATE,
1679 X_CREATED_BY => L_USERID ,
1680 X_LAST_UPDATE_DATE => SYSDATE ,
1681 X_LAST_UPDATED_BY => L_USERID ,
1682 X_LAST_UPDATE_LOGIN => L_LOGINID ,
1683 X_PROGRAM_APPLICATION_ID => TO_NUMBER(NULL),
1684 X_PROGRAM_ID => TO_NUMBER(NULL),
1685 X_PROGRAM_UPDATE_DATE => SYSDATE );
1686
1687
1688 ELSIF p_action_type = 'U' THEN -- object type is update
1689
1690 MTL_UNITS_OF_MEASURE_TL_PKG.UPDATE_ROW (X_UNIT_OF_MEASURE => P_UNIT_OF_MEASURE ,
1691 X_UNIT_OF_MEASURE_TL => P_UNIT_OF_MEASURE ,
1692 X_UOM_CODE => P_UOM_CODE ,
1693 X_UOM_CLASS => P_UOM_CLASS,
1694 X_BASE_UOM_FLAG => L_BASE_UOM_FLAG,
1695 X_DESCRIPTION => P_DESCRIPTION ,
1696 X_DISABLE_DATE => P_DISABLE_DATE ,
1697 X_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY ,
1698 X_ATTRIBUTE1 => L_INV_ATTRIBUTES_TBL(1) ,
1699 X_ATTRIBUTE2 => L_INV_ATTRIBUTES_TBL(2) ,
1700 X_ATTRIBUTE3 => L_INV_ATTRIBUTES_TBL(3) ,
1701 X_ATTRIBUTE4 => L_INV_ATTRIBUTES_TBL(4) ,
1702 X_ATTRIBUTE5 => L_INV_ATTRIBUTES_TBL(5) ,
1703 X_ATTRIBUTE6 => L_INV_ATTRIBUTES_TBL(6) ,
1704 X_ATTRIBUTE7 => L_INV_ATTRIBUTES_TBL(7) ,
1705 X_ATTRIBUTE8 => L_INV_ATTRIBUTES_TBL(8) ,
1706 X_ATTRIBUTE9 => L_INV_ATTRIBUTES_TBL(9) ,
1707 X_ATTRIBUTE10 => L_INV_ATTRIBUTES_TBL(10) ,
1708 X_ATTRIBUTE11 => L_INV_ATTRIBUTES_TBL(11) ,
1709 X_ATTRIBUTE12 => L_INV_ATTRIBUTES_TBL(12) ,
1710 X_ATTRIBUTE13 => L_INV_ATTRIBUTES_TBL(13) ,
1711 X_ATTRIBUTE14 => L_INV_ATTRIBUTES_TBL(14) ,
1712 X_ATTRIBUTE15 => L_INV_ATTRIBUTES_TBL(15) ,
1713 X_REQUEST_ID => TO_NUMBER(NULL),
1714 X_LAST_UPDATE_DATE => SYSDATE ,
1715 X_LAST_UPDATED_BY => L_USERID ,
1716 X_LAST_UPDATE_LOGIN => L_LOGINID
1717 );
1718 ELSE
1719
1720 FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_PARAMETER_TYPE');
1721 fnd_message.set_token('VALUE1', 'p_action_type');
1722 FND_MSG_PUB.ADD;
1723 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1724
1725 RAISE L_INVALID_OBJECT_TYPE;
1726
1727 END IF;
1728
1729 -- End of Code logic to insert MTL_UNITS_OF_MEASURE table.
1730 -- print_debug(' successfully returned from the package create_unit_of_measure ' || g_pkg_version, 1);
1731 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS ;
1732
1733 COMMIT;
1734
1735 EXCEPTION
1736 WHEN L_INVALID_OBJECT_TYPE THEN
1737 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1738 FND_MSG_PUB.COUNT_AND_GET
1739 ( P_COUNT => X_MSG_COUNT
1740 , P_DATA => X_MSG_DATA
1741 );
1742
1743 X_MSG_DATA := FND_MSG_PUB.GET(X_MSG_COUNT, 'F');
1744 WHEN FND_API.G_EXC_ERROR THEN
1745
1746 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
1747
1748 -- Get message count and data
1749 FND_MSG_PUB.COUNT_AND_GET
1750 ( P_COUNT => X_MSG_COUNT
1751 , P_DATA => X_MSG_DATA
1752 );
1753 X_MSG_DATA := FND_MSG_PUB.GET(X_MSG_COUNT, 'F');
1754
1755 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1756
1757 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
1758
1759 -- Get message count and data
1760 FND_MSG_PUB.COUNT_AND_GET
1761 ( P_COUNT => X_MSG_COUNT
1762 , P_DATA => X_MSG_DATA
1763 );
1764 WHEN OTHERS THEN
1765 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
1766 FND_MSG_PUB.COUNT_AND_GET
1767 ( P_COUNT => X_MSG_COUNT
1768 , P_DATA => X_MSG_DATA
1769 );
1770 END Unit_Of_Measure;
1771
1772 -------------------------------------------------------
1773 -- Procedure
1774 -- UOM_Conversion
1775 --
1776 -- Description
1777 -- Creates UOM conversion data.
1778 --
1779 -- Input Paramters
1780 -- p_lot_number Lot number.
1781 -- p_from_uom_code From uom code
1782 -- p_to_uom_code TO uom code.
1783 -- p_item_id inventory item id.
1784 -- p_organization_id Organization id.
1785 -- p_uom_rate Conversion rate.
1786 -- p_reason_id Reason.
1787 -- p_onhand_update Number Update onhand
1788 -- (1 - No, 2 - Yes which is based on update_type)
1789 -- p_update_type number Type of update to onhand
1790 -- 1 - recalculate Batch Primary qty.
1791 -- 2 - recalculate Batch Secondary qty.
1792 -- 3 - recalculate Onhand Primary qty.
1793 -- 4 - recalculate Onhand Secondary qty.
1794 --
1795 -- p_disable_date In active date.
1796 -- p_action_type Object type whether 'I' or 'U'
1797 -- Output Parameters
1798 -- x_return_status = fnd_api.g_ret_sts_success, if succeeded
1799 -- = fnd_api.g_ret_sts_exc_error, if an expected error occurred
1800 -- = fnd_api.g_ret_sts_unexp_error, if an unexpected error occurred
1801 --
1802 -- x_msg_count = Message count
1803 -- x_msg_data = Message text
1804 --
1805 -----------------------------------------------------------
1806
1807
1808 PROCEDURE UOM_Conversion( P_LOT_NUMBER IN VARCHAR2,
1809 P_FROM_UOM_CODE IN VARCHAR2,
1810 P_TO_UOM_CODE IN VARCHAR2 ,
1811 P_ITEM_ID IN NUMBER ,
1812 P_ORG_ID IN NUMBER,
1813 P_UOM_RATE IN NUMBER ,
1814 P_DISABLE_DATE IN DATE,
1815 P_REASON_ID IN NUMBER,
1816 P_ONHAND_UPDATE IN NUMBER,
1817 P_UPDATE_TYPE IN NUMBER,
1818 p_action_type IN VARCHAR2,
1819 X_MSG_COUNT OUT NOCOPY NUMBER,
1820 X_MSG_DATA OUT NOCOPY VARCHAR2,
1821 X_RETURN_STATUS OUT NOCOPY VARCHAR2 )
1822 IS
1823
1824 CURSOR GET_NON_BATCH_TXNS
1825 IS
1826 SELECT 1
1827 FROM dual
1828 WHERE EXISTS
1829 (SELECT mtln.transaction_id
1830 FROM mtl_transaction_lot_numbers mtln,
1831 mtl_material_transactions mmt
1832 WHERE mtln.transaction_id = mmt.transaction_id
1833 AND mmt.inventory_item_id = p_item_id
1834 AND mmt.organization_id = p_org_id
1835 AND mtln.lot_number = p_lot_number
1836 AND ( (mmt.transaction_source_type_id NOT IN (13, 5))
1837 OR (mmt.transaction_action_id NOT IN ( 1 , 27 , 2 , 3 , 31 , 32)) )
1838 );
1839
1840 CURSOR GET_BATCH_INFO
1841 IS
1842 SELECT DISTINCT b.batch_no,
1843 d.line_type,
1844 b.batch_status,
1845 b.batch_id,
1846 b.parentline_id,
1847 d.phantom_id,
1848 D.DTL_UM,
1849 t.opm_costed_flag
1850 FROM gme_batch_header b,
1851 gme_material_details d,
1852 mtl_material_transactions t,
1853 mtl_transaction_lot_numbers l
1854 WHERE t.inventory_item_id = p_item_id
1855 AND t.organization_id = p_org_id
1856 AND T.TRANSACTION_SOURCE_TYPE_ID = 5
1857 AND NOT EXISTS
1858 (SELECT transaction_id1
1859 FROM gme_transaction_pairs
1860 WHERE transaction_id1 = t.transaction_id
1861 AND pair_type = 1
1862 )
1863 AND l.transaction_id = t.transaction_id
1864 AND l.lot_number = p_lot_number
1865 AND d.material_detail_id = t.trx_source_line_id
1866 AND d.batch_id = t.transaction_source_id
1867 AND B.BATCH_ID = D.BATCH_ID
1868 ORDER BY b.parentline_id;
1869
1870
1871 CURSOR CUR_ONHAND
1872 IS
1873 SELECT REVISION,
1874 SUBINVENTORY_CODE,
1875 LPN_ID,
1876 LOCATOR_ID,
1877 on_hand,
1878 secondary_onhand
1879 FROM mtl_lot_onhand_sum_v
1880 WHERE inventory_item_id = p_item_id
1881 AND lot_number = p_lot_number
1882 AND ORGANIZATION_ID = P_ORG_ID
1883 ORDER BY organization_code,
1884 revision,
1885 lpn,
1886 subinventory_code ,
1887 LOCATOR;
1888
1889 L_TEMP NUMBER := 1;
1890 L_BATCH_ID NUMBER(10);
1891
1892 CURSOR batch_txns
1893 IS
1894 SELECT SUM(mtln.primary_quantity),
1895 SUM(mtln.secondary_transaction_quantity)
1896 FROM mtl_material_transactions mmt ,
1897 mtl_transaction_lot_numbers mtln
1898 WHERE mmt.transaction_source_id = L_BATCH_ID
1899 AND mmt.transaction_source_type_id = 5
1900 AND NOT EXISTS
1901 (SELECT transaction_id1
1902 FROM gme_transaction_pairs
1903 WHERE transaction_id1 = mmt.transaction_id
1904 AND pair_type = 1
1905 )
1906 AND mmt.inventory_item_id = p_item_id
1907 AND mmt.organization_id = p_org_id
1908 AND mmt.transaction_id = mtln.transaction_id
1909 AND MTLN.LOT_NUMBER = P_LOT_NUMBER;
1910
1911 X_BATCH_QTY1 NUMBER;
1912 x_batch_qty2 NUMBER;
1913 X_LINE_TYPE NUMBER;
1914 X_BATCH_ID NUMBER(10);
1915 L_CONV_REC MTL_LOT_UOM_CLASS_CONVERSIONS%ROWTYPE;
1916 -- L_FROM_UNIT_OF_MEASURE VARCHAR(25);
1917 L_FROM_UNIT_OF_MEASURE MTL_UNITS_OF_MEASURE_TL.UNIT_OF_MEASURE%TYPE;
1918 -- L_FROM_UOM_CLASS VARCHAR2(30);
1919 L_FROM_UOM_CLASS MTL_UOM_CLASSES_TL.UOM_CLASS_TL%TYPE;
1920 L_TO_UNIT_OF_MEASURE MTL_UNITS_OF_MEASURE_TL.UNIT_OF_MEASURE%TYPE;
1921 -- L_TO_UNIT_OF_MEASURE VARCHAR2(40);
1922 -- l_to_uom_class VARCHAR2(40);
1923 L_TO_UOM_CLASS MTL_UOM_CLASSES_TL.UOM_CLASS_TL%TYPE;
1924 --L_UOM_CODE VARCHAR2(3);
1925 L_UOM_CODE MTL_UNITS_OF_MEASURE_TL.UOM_CODE%type;
1926 -- l_class VARCHAR2(10);
1927 l_conversion_id NUMBER;
1928 l_primary_uom_code VARCHAR2(3);
1929 l_secondary_uom_code VARCHAR2(3);
1930 new_primary_qty NUMBER;
1931 new_secondary_qty NUMBER;
1932 x_calc_qty NUMBER;
1933 transaction_primary_qty NUMBER;
1934 transaction_secondary_qty NUMBER;
1935 l_get_non_batch_txns NUMBER;
1936 x_batch_no VARCHAR2(32);
1937 x_batch_status NUMBER(5);
1938 x_parentline_id NUMBER(10);
1939 x_phantom_id NUMBER(10);
1940 X_ROW_COUNT NUMBER;
1941 X_LAST_BATCH_ID NUMBER(10);
1942 l_sequence NUMBER;
1943 L_PENDING_COUNT NUMBER;
1944 l_ITEM_EXISTS NUMBER;
1945 l_ORG_EXISTS NUMBER;
1946 L_COUNT NUMBER;
1947 V_LOT_CONTROL_CODE NUMBER;
1948 l_lot_number VARCHAR2(30);
1949 x_dtl_um VARCHAR2(5);
1950 L_RETURN_STATUS VARCHAR2(10);
1951 L_MSG_COUNT NUMBER;
1952 L_MSG_DATA VARCHAR2(1000) ;
1953 l_qoh NUMBER;
1954 L_RQOH NUMBER;
1955 L_QR NUMBER;
1956 L_QS NUMBER;
1957 L_ATT NUMBER;
1958 L_ATR NUMBER;
1959 l_reason_count number;
1960
1961 L_USERID NUMBER := FND_GLOBAL.USER_ID;
1962 L_LOGINID NUMBER := FND_GLOBAL.LOGIN_ID;
1963
1964 CURSOR GET_BATCH
1965 IS
1966 SELECT * FROM GME_BATCH_HEADER WHERE BATCH_ID = NVL(X_PHANTOM_ID, X_BATCH_ID);
1967
1968 CURSOR GET_MATL_LINES
1969 IS
1970 SELECT *
1971 FROM GME_MATERIAL_DETAILS
1972 WHERE BATCH_ID = X_BATCH_ID
1973 AND LINE_TYPE = X_LINE_TYPE
1974 and inventory_item_id = P_ITEM_ID;
1975
1976 CURSOR GET_UOM_CLASS(V_UOM MTL_UNITS_OF_MEASURE.UOM_CODE%TYPE)
1977 IS
1978 SELECT UOM_CLASS
1979 FROM MTL_UNITS_OF_MEASURE
1980 WHERE uom_code = v_uom;
1981
1982 X_MATL_LINE GME_MATERIAL_DETAILS%ROWTYPE;
1983 l_tracking_quantity_ind mtl_system_items_b.tracking_quantity_ind%TYPE;
1984 l_secondary_def_ind mtl_system_items_b.secondary_default_ind%TYPE;
1985 l_lot_divisible_flag mtl_system_items_b.lot_divisible_flag%TYPE;
1986 L_QTY_TBL MTL_LOT_UOM_CONV_PUB.QUANTITY_UPDATE_REC_TYPE;
1987 X_BATCH_ROW GME_BATCH_HEADER%ROWTYPE;
1988
1989 -- L_FROM_CLASS VARCHAR2(100);
1990 L_FROM_CLASS MTL_UOM_CLASSES_TL.UOM_CLASS_TL%TYPE;
1991
1992 X_UOM_CLASS MTL_UOM_CLASSES_TL.UOM_CLASS_TL%TYPE;
1993 X_SEC_UOM_CLASS MTL_UOM_CLASSES_TL.UOM_CLASS_TL%TYPE;
1994
1995 P_FROM_BASE_UOM_FLAG VARCHAR2(10);
1996 --L_TO_CLASS VARCHAR2(30);
1997 L_TO_CLASS MTL_UOM_CLASSES_TL.UOM_CLASS_TL%TYPE;
1998
1999 x_opm_costed_flag MTL_MATERIAL_TRANSACTIONS.OPM_COSTED_FLAG%TYPE ;
2000
2001 P_TO_BASE_UOM_FLAG VARCHAR2(10);
2002 l_shelf_life_code number;
2003
2004 l_invalid_uom_exc EXCEPTION ;
2005 L_UOM_FROMTO_EXC EXCEPTION ;
2006 l_invalid_item_exc EXCEPTION ;
2007 L_CONVERSION_EXISTS_EXC EXCEPTION ;
2008 L_RET_CONV_EXISTS_WARNING CONSTANT VARCHAR2(1) := 'W' ;
2009 L_INVALID_UOM_RATE EXCEPTION;
2010 L_PENDING_TRAN_EXISTS EXCEPTION;
2011 L_CONV_NOT_EXISTS EXCEPTION;
2012 L_LOT_CONV_NOT_EXISTS EXCEPTION;
2013 L_INVALID_LOT EXCEPTION;
2014 L_INVALID_OBJECT EXCEPTION;
2015 L_INVALID_REASON EXCEPTION;
2016 L_INVALID_BASE_UOM exception;
2017
2018
2019 BEGIN
2020
2021 IF (P_UOM_RATE IS NULL) THEN
2022
2023 FND_MESSAGE.SET_NAME('INV', 'INV_LOTC_CONVRATE_REQUIRED');
2024 FND_MSG_PUB.ADD;
2025 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
2026 RAISE L_INVALID_UOM_RATE;
2027
2028 END IF;
2029
2030 IF (P_UOM_RATE <= 0) THEN
2031
2032 FND_MESSAGE.SET_NAME('INV', 'INV_NEG_CONV');
2033 FND_MSG_PUB.ADD;
2034 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
2035 RAISE L_INVALID_UOM_RATE;
2036
2037 END IF;
2038
2039 IF P_DISABLE_DATE IS NOT NULL AND TRUNC(P_DISABLE_DATE) < TRUNC(SYSDATE) THEN
2040
2041 IF (L_DEBUG = 1) THEN
2042 INV_LOG_UTIL.TRACE('Disable date is past date', 'inv_uom_api_pub', '9');
2043 END IF;
2044
2045 FND_MESSAGE.SET_NAME('INV', 'INV_DISABLE_DATE');
2046 FND_MSG_PUB.ADD;
2047 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
2048 RAISE FND_API.G_EXC_ERROR;
2049
2050 END IF;
2051
2052
2053
2054 IF P_LOT_NUMBER IS NULL THEN
2055 --this is non lot uom case.
2056
2057 BEGIN
2058
2059 SELECT count(*)
2060 into l_qr
2061 FROM MTL_RESERVATIONS
2062 WHERE INVENTORY_ITEM_ID = P_ITEM_ID ;
2063
2064 EXCEPTION
2065 WHEN no_data_found THEN
2066 l_qr := 0;
2067 END;
2068
2069 IF (L_DEBUG = 1) THEN
2070 INV_LOG_UTIL.TRACE('Reservations l_qr:'|| l_qr, 'inv_uom_api_pub', '9');
2071 END IF;
2072
2073 if l_qr > 0 then
2074
2075 FND_MESSAGE.SET_NAME('INV', 'INV_LOTCONV_RSV_EXIST');
2076 FND_MSG_PUB.ADD;
2077 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
2078 RAISE FND_API.G_EXC_ERROR;
2079 end if;
2080
2081
2082 BEGIN
2083
2084 SELECT count(*)
2085 into L_PENDING_COUNT
2086 FROM MTL_MATERIAL_TRANSACTIONS_TEMP
2087 WHERE INVENTORY_ITEM_ID = P_ITEM_ID;
2088
2089 EXCEPTION
2090 WHEN NO_DATA_FOUND THEN
2091 L_PENDING_COUNT := 0;
2092
2093 END;
2094
2095 IF (L_PENDING_COUNT >0) THEN
2096
2097 RAISE L_PENDING_TRAN_EXISTS;
2098
2099 END IF;
2100
2101
2102 BEGIN
2103 SELECT UNIT_OF_MEASURE , UOM_CLASS , BASE_UOM_FLAG
2104 INTO L_FROM_UNIT_OF_MEASURE , L_FROM_CLASS , P_FROM_BASE_UOM_FLAG
2105 FROM MTL_UNITS_OF_MEASURE_VL
2106 WHERE UOM_CODE = P_FROM_UOM_CODE
2107 AND NVL(DISABLE_DATE, TRUNC(SYSDATE) + 1) > TRUNC(SYSDATE);
2108 EXCEPTION
2109 WHEN NO_DATA_FOUND THEN
2110 -- print_debug(p_from_uom_code || ' doesnot exist ' || g_pkg_version, 1);
2111 RAISE L_INVALID_UOM_EXC ;
2112 END ;
2113
2114 IF (L_DEBUG = 1) THEN
2115 INV_LOG_UTIL.TRACE('L_FROM_UNIT_OF_MEASURE:' || L_FROM_UNIT_OF_MEASURE, 'inv_uom_api_pub', '9');
2116 INV_LOG_UTIL.TRACE('L_FROM_CLASS:' || L_FROM_CLASS, 'inv_uom_api_pub', '9');
2117 INV_LOG_UTIL.TRACE('P_FROM_BASE_UOM_FLAG:' || P_FROM_BASE_UOM_FLAG, 'inv_uom_api_pub', '9');
2118 END IF;
2119
2120 BEGIN
2121 SELECT UNIT_OF_MEASURE , UOM_CLASS , BASE_UOM_FLAG
2122 INTO L_TO_UNIT_OF_MEASURE , L_TO_CLASS , P_TO_BASE_UOM_FLAG
2123 FROM MTL_UNITS_OF_MEASURE_VL
2124 WHERE UOM_CODE = P_TO_UOM_CODE
2125 AND NVL(DISABLE_DATE, TRUNC(SYSDATE) + 1) > TRUNC(SYSDATE);
2126 EXCEPTION
2127 WHEN NO_DATA_FOUND THEN
2128 RAISE L_INVALID_UOM_EXC ;
2129 END ;
2130
2131 IF (L_DEBUG = 1) THEN
2132 INV_LOG_UTIL.TRACE('L_TO_UNIT_OF_MEASURE:' || L_TO_UNIT_OF_MEASURE, 'inv_uom_api_pub', '9');
2133 INV_LOG_UTIL.TRACE('L_TO_CLASS:' || L_TO_CLASS, 'inv_uom_api_pub', '9');
2134 INV_LOG_UTIL.TRACE('P_TO_BASE_UOM_FLAG:' || P_TO_BASE_UOM_FLAG, 'inv_uom_api_pub', '9');
2135 END IF;
2136
2137
2138
2139
2140
2141 IF p_action_type = 'I' THEN
2142
2143 BEGIN
2144
2145 INV_CONVERT.CREATE_UOM_CONVERSION(P_FROM_UOM_CODE ,
2146 P_TO_UOM_CODE ,
2147 P_ITEM_ID ,
2148 P_UOM_RATE ,
2149 X_RETURN_STATUS );
2150
2151 IF (L_DEBUG = 1) THEN
2152 INV_LOG_UTIL.TRACE('After standard uom conversion creations,X_RETURN_STATUS:'|| X_RETURN_STATUS, 'inv_uom_api_pub', '9');
2153 END IF;
2154
2155 IF X_RETURN_STATUS = FND_API.G_RET_STS_SUCCESS THEN
2156
2157 IF L_FROM_CLASS = L_TO_CLASS THEN -- standard intra class conversion.
2158
2159 UPDATE MTL_UOM_CONVERSIONS
2160 SET DISABLE_DATE = P_DISABLE_DATE
2161 WHERE INVENTORY_ITEM_ID = NVL(P_ITEM_ID,0)
2162 AND UOM_CODE = P_TO_UOM_CODE;
2163 ELSE
2164
2165 UPDATE MTL_UOM_CLASS_CONVERSIONS
2166 SET DISABLE_DATE = P_DISABLE_DATE
2167 WHERE INVENTORY_ITEM_ID = NVL(P_ITEM_ID,0)
2168 AND FROM_UOM_CODE = P_FROM_UOM_CODE
2169 AND TO_UOM_CODE = P_TO_UOM_CODE;
2170
2171 END IF;
2172
2173 END IF;
2174
2175
2176 IF X_RETURN_STATUS = 'W' THEN
2177
2178 IF (L_DEBUG = 1) THEN
2179 INV_LOG_UTIL.TRACE('Conversion already exists', 'inv_uom_api_pub', '9');
2180 END IF;
2181
2182 FND_MESSAGE.SET_NAME('INV', 'INV_CONV_EXISTS');
2183 FND_MESSAGE.SET_TOKEN('VALUE1', P_FROM_UOM_CODE);
2184 fnd_message.set_token('VALUE2', P_TO_UOM_CODE);
2185 FND_MSG_PUB.ADD;
2186
2187 RAISE L_CONVERSION_EXISTS_EXC;
2188 ELSIF X_RETURN_STATUS <> 'S' THEN
2189 RAISE FND_API.G_EXC_ERROR;
2190 END IF;
2191
2192 END;
2193
2194 --END IF; -- end if of p_action_type = I
2195
2196 ELSIF p_action_type = 'U' THEN
2197
2198 BEGIN
2199
2200 IF L_FROM_CLASS = L_TO_CLASS THEN -- standard intra class conversion.
2201
2202 IF (L_DEBUG = 1) THEN
2203 INV_LOG_UTIL.TRACE('Intra class conversion', 'inv_uom_api_pub', '9');
2204 END IF;
2205
2206 IF P_FROM_BASE_UOM_FLAG = 'Y' AND P_TO_BASE_UOM_FLAG = 'N' THEN
2207
2208 BEGIN
2209
2210 SELECT 1
2211 INTO L_COUNT
2212 FROM MTL_UOM_CONVERSIONS
2213 WHERE INVENTORY_ITEM_ID = nvl(P_ITEM_ID,0)
2214 AND UOM_CODE = P_TO_UOM_CODE ;
2215 EXCEPTION
2216 WHEN OTHERS THEN
2217 RAISE L_CONV_NOT_EXISTS;
2218 END;
2219
2220 UPDATE MTL_UOM_CONVERSIONS
2221 SET CONVERSION_RATE = P_UOM_RATE,
2222 DISABLE_DATE = P_DISABLE_DATE
2223 WHERE INVENTORY_ITEM_ID = nvl(P_ITEM_ID,0)
2224 AND UOM_CODE = P_TO_UOM_CODE ;
2225
2226 ELSE
2227
2228 RAISE L_INVALID_UOM_EXC;
2229 END IF;
2230
2231 ELSE
2232
2233 IF P_FROM_BASE_UOM_FLAG = 'Y' AND P_TO_BASE_UOM_FLAG = 'Y' THEN
2234
2235 BEGIN
2236
2237 SELECT 1
2238 INTO L_COUNT
2239 FROM MTL_UOM_CLASS_CONVERSIONS
2240 WHERE INVENTORY_ITEM_ID = P_ITEM_ID
2241 AND FROM_UOM_CODE = P_FROM_UOM_CODE
2242 AND TO_UOM_CODE = P_TO_UOM_CODE ;
2243 -- AND NVL(DISABLE_DATE, TRUNC(SYSDATE) + 1) > TRUNC(SYSDATE);
2244 EXCEPTION
2245 WHEN OTHERS THEN
2246 RAISE L_CONV_NOT_EXISTS;
2247 END;
2248
2249 UPDATE MTL_UOM_CLASS_CONVERSIONS
2250 SET CONVERSION_RATE = P_UOM_RATE,
2251 DISABLE_DATE = P_DISABLE_DATE
2252 WHERE INVENTORY_ITEM_ID = P_ITEM_ID
2253 AND FROM_UOM_CODE = P_FROM_UOM_CODE
2254 AND TO_UOM_CODE = P_TO_UOM_CODE ;
2255 -- AND NVL(DISABLE_DATE, TRUNC(SYSDATE) + 1) > TRUNC(SYSDATE);
2256
2257 ELSE
2258 RAISE L_INVALID_UOM_EXC;
2259 END IF;
2260
2261 END IF;
2262
2263 END;
2264
2265 ELSE
2266
2267 FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_PARAMETER_TYPE');
2268 FND_MESSAGE.SET_TOKEN('VALUE1', 'p_action_type');
2269 FND_MSG_PUB.ADD;
2270 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
2271
2272 RAISE L_INVALID_OBJECT;
2273
2274 END IF; -- end if of p_action_type = U
2275
2276 ELSE -- Lot specific conversion.
2277 -- Validating item and organization.
2278 BEGIN
2279 SELECT 1
2280 INTO l_ORG_EXISTS
2281 FROM MTL_PARAMETERS
2282 WHERE ORGANIZATION_ID = P_ORG_ID;
2283 EXCEPTION
2284 WHEN OTHERS THEN
2285 FND_MESSAGE.SET_NAME('INV', 'INV_INT_ORGCODE');
2286 FND_MSG_PUB.ADD;
2287 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
2288 RAISE FND_API.G_EXC_ERROR;
2289 END;
2290
2291 IF (L_DEBUG = 1) THEN
2292 INV_LOG_UTIL.TRACE('l_ORG_EXISTS:' || l_ORG_EXISTS, 'inv_uom_api_pub', '9');
2293 END IF;
2294
2295 BEGIN
2296 SELECT 1
2297 INTO l_ITEM_EXISTS
2298 FROM MTL_SYSTEM_ITEMS_B
2299 WHERE inventory_item_id = p_item_id
2300 AND organization_id = p_org_id;
2301 EXCEPTION
2302 WHEN OTHERS THEN
2303 FND_MESSAGE.SET_NAME('INV', 'INV_INT_ITMCODE');
2304 FND_MSG_PUB.ADD;
2305 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
2306 RAISE FND_API.G_EXC_ERROR;
2307
2308 END;
2309
2310 IF (L_DEBUG = 1) THEN
2311 INV_LOG_UTIL.TRACE('l_ITEM_EXISTS:' || l_ITEM_EXISTS, 'inv_uom_api_pub', '9');
2312 END IF;
2313
2314
2315
2316 BEGIN
2317 SELECT PRIMARY_UOM_CODE,
2318 SECONDARY_UOM_CODE,
2319 SECONDARY_DEFAULT_IND,
2320 TRACKING_QUANTITY_IND,
2321 LOT_DIVISIBLE_FLAG
2322 INTO l_primary_uom_code,
2323 l_secondary_uom_code,
2324 l_secondary_def_ind,
2325 l_tracking_quantity_ind,
2326 l_lot_divisible_flag
2327 FROM MTL_SYSTEM_ITEMS_B
2328 WHERE inventory_item_id = p_item_id
2329 AND ORGANIZATION_ID = P_ORG_ID;
2330 EXCEPTION
2331 WHEN OTHERS THEN
2332 NULL;
2333 END;
2334
2335
2336 IF (L_DEBUG = 1) THEN
2337 INV_LOG_UTIL.TRACE('l_primary_uom_code:' || L_PRIMARY_UOM_CODE || ',l_secondary_uom_code' || L_SECONDARY_UOM_CODE, 'inv_uom_api_pub', '9');
2338 INV_LOG_UTIL.TRACE('l_secondary_def_ind:' || L_SECONDARY_DEF_IND || ',l_tracking_quantity_ind' || L_TRACKING_QUANTITY_IND, 'inv_uom_api_pub', '9');
2339 INV_LOG_UTIL.TRACE('l_lot_divisible_flag:' || L_LOT_DIVISIBLE_FLAG, 'inv_uom_api_pub', '9');
2340 END IF;
2341
2342 -- End of Validating item and organization.
2343 -- Validating p_from_uom_code and p_to_uom_code.
2344
2345 -- Validating p_from_uom_code and p_to_uom_code.
2346
2347 IF (P_FROM_UOM_CODE = NULL) OR (P_TO_UOM_CODE = NULL) THEN
2348 RAISE l_invalid_uom_exc ;
2349 ELSIF P_FROM_UOM_CODE = P_TO_UOM_CODE THEN
2350 RAISE L_UOM_FROMTO_EXC ;
2351 END IF ;
2352
2353 -- These uom code must be base uom.
2354 BEGIN
2355 SELECT UOM_CODE , UOM_CLASS
2356 INTO l_uom_code , l_from_uom_class
2357 FROM MTL_UNITS_OF_MEASURE_VL
2358 WHERE UOM_CODE = P_FROM_UOM_CODE
2359 AND base_uom_flag = 'Y'
2360 AND NVL(disable_date, TRUNC(SYSDATE) + 1) > TRUNC(SYSDATE);
2361 EXCEPTION
2362 WHEN NO_DATA_FOUND THEN
2363 FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_BASE_UOM');
2364 fnd_message.set_token('UOM', P_FROM_UOM_CODE);
2365 FND_MSG_PUB.ADD;
2366 RAISE L_INVALID_BASE_UOM;
2367 END ;
2368
2369 IF (L_DEBUG = 1) THEN
2370 INV_LOG_UTIL.TRACE('l_uom_code:' || l_uom_code, 'inv_uom_api_pub', '9');
2371 END IF;
2372
2373 BEGIN
2374
2375 SELECT UOM_CODE
2376 INTO L_PRIMARY_UOM_CODE
2377 FROM MTL_UNITS_OF_MEASURE_VL
2378 WHERE UOM_CODE = L_PRIMARY_UOM_CODE
2379 and uom_class = l_from_uom_class
2380 AND NVL(disable_date, TRUNC(SYSDATE) + 1) > TRUNC(SYSDATE);
2381
2382 EXCEPTION
2383 WHEN NO_DATA_FOUND THEN
2384 IF (L_DEBUG = 1) THEN
2385 INV_LOG_UTIL.TRACE('From UOM code is not belongs to item primary uom code', 'inv_uom_api_pub', '9');
2386 END IF;
2387
2388 FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_ITEM_BASE');
2389 FND_MESSAGE.SET_TOKEN('VALUE', P_FROM_UOM_CODE);
2390 FND_MSG_PUB.ADD;
2391 raise l_invalid_base_uom;
2392 END;
2393
2394 BEGIN
2395 SELECT uom_code
2396 INTO l_uom_code
2397 FROM MTL_UNITS_OF_MEASURE_VL
2398 WHERE UOM_CODE = P_TO_UOM_CODE
2399 AND BASE_UOM_FLAG = 'Y'
2400 AND NVL(disable_date, TRUNC(SYSDATE) + 1) > TRUNC(SYSDATE);
2401 EXCEPTION
2402 WHEN NO_DATA_FOUND THEN
2403 FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_BASE_UOM');
2404 FND_MESSAGE.SET_TOKEN('UOM', P_TO_UOM_CODE);
2405 FND_MSG_PUB.ADD;
2406 RAISE L_INVALID_BASE_UOM;
2407 -- passed p_base_uom_code is not assosiated with any other uom class.
2408 END ;
2409
2410 -- End of Validating p_from_uom_code and p_to_uom_code.
2411
2412 BEGIN
2413
2414 SELECT LOT_CONTROL_CODE , SHELF_LIFE_CODE
2415 INTO V_LOT_CONTROL_CODE , l_shelf_life_code
2416 FROM mtl_system_items_b
2417 WHERE INVENTORY_ITEM_ID = P_ITEM_ID
2418 AND ORGANIZATION_ID = P_ORG_ID;
2419
2420 IF (L_DEBUG = 1) THEN
2421 INV_LOG_UTIL.TRACE('v_lot_control_code:' || v_lot_control_code, 'inv_uom_api_pub', '9');
2422 INV_LOG_UTIL.TRACE('l_shelf_life_code:' || l_shelf_life_code, 'inv_uom_api_pub', '9');
2423 END IF;
2424
2425 IF( v_lot_control_code = 1 ) THEN
2426 FND_MESSAGE.SET_NAME('INV', 'INV_LOTC_LOT_CTL_ERROR');
2427 FND_MSG_PUB.ADD;
2428 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
2429 FND_MESSAGE.RAISE_ERROR;
2430 END IF;
2431
2432 END;
2433
2434 -- End of item is lot controlled check.
2435
2436 -- Validating the passed lot valid with the same item.
2437 BEGIN
2438
2439 SELECT LOT_NUMBER
2440 INTO l_lot_number
2441 FROM mtl_lot_numbers
2442 WHERE lot_number = p_lot_number
2443 AND inventory_item_id = p_item_id
2444 AND organization_id = p_org_id;
2445 EXCEPTION
2446 WHEN NO_DATA_FOUND THEN
2447 FND_MESSAGE.SET_NAME('INV', 'INV_LOTC_LOT_INVALID');
2448 FND_MSG_PUB.ADD;
2449 RAISE FND_API.G_EXC_ERROR;
2450 END ;
2451
2452 -- End of Validating the passed lot valid with the same item.
2453
2454 if l_shelf_life_code <> 1 then
2455
2456 BEGIN
2457
2458 SELECT LOT_NUMBER
2459 INTO L_LOT_NUMBER
2460 FROM MTL_LOT_NUMBERS
2461 WHERE LOT_NUMBER = P_LOT_NUMBER
2462 AND INVENTORY_ITEM_ID = P_ITEM_ID
2463 AND ORGANIZATION_ID = P_ORG_ID
2464 AND EXPIRATION_DATE > SYSDATE;
2465 EXCEPTION
2466 WHEN NO_DATA_FOUND THEN
2467 IF (L_DEBUG = 1) THEN
2468 INV_LOG_UTIL.TRACE('Lot expired' , 'inv_uom_api_pub', '9');
2469 END IF;
2470 FND_MESSAGE.SET_NAME('INV', 'INV_LOT_EXPIRED');
2471 FND_MSG_PUB.ADD;
2472 RAISE FND_API.G_EXC_ERROR;
2473 END;
2474
2475 END IF;
2476
2477 -- End of Validating p_lot_number
2478
2479 IF p_action_type = 'I' THEN
2480
2481 -- Check if the lot uom conversion already present.
2482 BEGIN
2483 SELECT COUNT(1)
2484 INTO L_COUNT
2485 FROM MTL_LOT_UOM_CLASS_CONVERSIONS
2486 WHERE ORGANIZATION_ID = P_ORG_ID
2487 AND INVENTORY_ITEM_ID = P_ITEM_ID
2488 AND LOT_NUMBER = P_LOT_NUMBER
2489 AND TO_UOM_CODE = P_TO_UOM_CODE ;
2490 --AND to_unit_of_measure = p_to_unit_of_measure;
2491 -- Fetch the p_to_unit_of_measure from MTL_UNITS_OF_MEASURE_VL.
2492 EXCEPTION
2493 WHEN NO_DATA_FOUND THEN
2494 IF (L_DEBUG = 1) THEN
2495 INV_LOG_UTIL.TRACE('uom class conversion not exists', 'inv_uom_api_pub', '9');
2496 END IF;
2497 NULL;
2498 END ;
2499
2500 IF (L_DEBUG = 1) THEN
2501 INV_LOG_UTIL.TRACE('l_count:'||l_count, 'inv_uom_api_pub', '9');
2502 END IF;
2503
2504 IF (l_count > 0) THEN
2505 FND_MESSAGE.SET_NAME('INV', 'INV_LOTC_UOM_INUSE');
2506 FND_MSG_PUB.ADD;
2507 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
2508 FND_MESSAGE.RAISE_ERROR;
2509 END IF;
2510 -- End of Check if the lot uom conversion already present.
2511 -- From and to UOM cannot be the same.
2512
2513 IF (p_from_uom_code = p_to_uom_code) THEN
2514 FND_MESSAGE.SET_NAME('INV', 'INV_LOTC_UOM_FROMTO_ERROR');
2515 FND_MSG_PUB.ADD;
2516 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
2517 fnd_message.raise_error;
2518 END IF;
2519 -- End of From and to UOM cannot be the same.
2520 -- Check if item is lot controlled.
2521
2522 ELSIF p_action_type = 'U' THEN
2523
2524 BEGIN
2525 SELECT 1 --COUNT(1)
2526 INTO l_count
2527 FROM mtl_lot_uom_class_conversions
2528 WHERE organization_id = p_org_id
2529 AND inventory_item_id = p_item_id
2530 AND LOT_NUMBER = P_LOT_NUMBER
2531 AND TO_UOM_CODE = P_TO_UOM_CODE
2532 AND from_uom_code = P_FROM_UOM_CODE ;
2533 --AND to_unit_of_measure = p_to_unit_of_measure;
2534 -- Fetch the p_to_unit_of_measure from MTL_UNITS_OF_MEASURE_VL.
2535 EXCEPTION
2536 WHEN NO_DATA_FOUND THEN
2537 IF (L_DEBUG = 1) THEN
2538 INV_LOG_UTIL.TRACE('No data found exception, lot conversion does not exists', 'inv_uom_api_pub', '9');
2539 END IF;
2540 RAISE L_LOT_CONV_NOT_EXISTS;
2541 END ;
2542
2543 ELSE
2544
2545 IF (L_DEBUG = 1) THEN
2546 INV_LOG_UTIL.TRACE('Invalid object type', 'inv_uom_api_pub', '9');
2547 END IF;
2548
2549 FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_PARAMETER_TYPE');
2550 fnd_message.set_token('VALUE1', 'p_action_type');
2551 FND_MSG_PUB.ADD;
2552 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
2553
2554 RAISE L_INVALID_OBJECT;
2555
2556 END IF;
2557
2558 IF P_REASON_ID IS NOT NULL THEN
2559
2560 BEGIN
2561
2562 SELECT 1
2563 into l_reason_count
2564 FROM MTL_TRANSACTION_REASONS
2565 WHERE REASON_ID = P_REASON_ID
2566 AND NVL(DISABLE_DATE, SYSDATE+1) > SYSDATE;
2567
2568 EXCEPTION
2569 when no_data_found then
2570 FND_MESSAGE.SET_NAME('INV', 'INV_INT_REACODE');
2571 FND_MSG_PUB.ADD;
2572
2573 IF (L_DEBUG = 1) THEN
2574 INV_LOG_UTIL.TRACE('NO data found for the reason id:'||P_REASON_ID, 'inv_uom_api_pub', '9');
2575 END IF;
2576
2577 RAISE L_INVALID_REASON;
2578 end;
2579
2580 end if;
2581
2582 IF P_ONHAND_UPDATE = 2 THEN
2583
2584 BEGIN
2585
2586 IF ( P_UPDATE_TYPE NOT IN (1, 2, 3, 4) OR P_ONHAND_UPDATE NOT IN (1, 2) ) THEN
2587
2588 IF (L_DEBUG = 1) THEN
2589 INV_LOG_UTIL.TRACE('Invalid Update type ', 'inv_uom_api_pub', '9');
2590 END IF;
2591
2592 FND_MESSAGE.SET_NAME('INV', 'INV_LOTC_UPDATETYPE_INVALID');
2593 FND_MSG_PUB.ADD;
2594
2595 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
2596 -- fnd_message.raise_error;
2597 RAISE FND_API.G_EXC_ERROR;
2598
2599 END IF;
2600 -- check for object_type 1,2-----
2601
2602 IF (P_UPDATE_TYPE IN (1, 2)) THEN
2603
2604 IF (L_TRACKING_QUANTITY_IND <> 'PS') THEN
2605
2606 FND_MESSAGE.SET_NAME('INV', 'INV_LOTCONV_TYPE0_ERROR');
2607 FND_MSG_PUB.ADD;
2608
2609 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
2610 FND_MESSAGE.RAISE_ERROR;
2611 -- Error if the item is not Dual Fixed or dual default.
2612 ELSIF (L_SECONDARY_DEF_IND NOT IN ('F', 'D')) THEN
2613
2614 FND_MESSAGE.SET_NAME('INV', 'INV_LOTCONV_TYPE3_ERROR');
2615 FND_MSG_PUB.ADD;
2616
2617 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
2618 FND_MESSAGE.RAISE_ERROR;
2619
2620 END IF;
2621 -- END IF;
2622
2623
2624 -- Raise error if there are any non-production txns.
2625
2626 BEGIN
2627
2628 OPEN GET_NON_BATCH_TXNS;
2629
2630 FETCH GET_NON_BATCH_TXNS INTO L_GET_NON_BATCH_TXNS;
2631
2632 IF (GET_NON_BATCH_TXNS % FOUND) THEN
2633
2634 IF (L_DEBUG = 1) THEN
2635 INV_LOG_UTIL.TRACE(' INV_LOTCONV_NON_BATCH ', 'inv_uom_api_pub', '9');
2636 END IF;
2637
2638 CLOSE GET_NON_BATCH_TXNS;
2639 FND_MESSAGE.SET_NAME('INV', 'INV_LOTCONV_NON_BATCH');
2640 FND_MSG_PUB.ADD;
2641
2642 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
2643 RAISE FND_API.G_EXC_ERROR;
2644
2645 END IF;
2646
2647 CLOSE GET_NON_BATCH_TXNS;
2648
2649 END;
2650
2651
2652 IF (L_DEBUG = 1) THEN
2653 INV_LOG_UTIL.TRACE('Retrive batch associated with the lot.', 'inv_uom_api_pub', '9');
2654 END IF;
2655
2656 -- Retrive batch associated with the lot.
2657 BEGIN
2658
2659 x_row_count := 0;
2660 x_last_batch_id := 0;
2661 OPEN GET_BATCH_INFO;
2662 LOOP
2663 FETCH GET_BATCH_INFO
2664 INTO x_batch_no,
2665 x_line_type,
2666 x_batch_status,
2667 x_batch_id,
2668 x_parentline_id,
2669 X_PHANTOM_ID,
2670 X_DTL_UM,
2671 X_OPM_COSTED_FLAG;
2672
2673 IF GET_BATCH_INFO%NOTFOUND THEN
2674 IF (L_DEBUG = 1) THEN
2675 INV_LOG_UTIL.TRACE('record doest not exists in GET_BATCH_INFO ', 'inv_uom_api_pub', '9');
2676 END IF;
2677 EXIT;
2678 ELSE
2679 IF (x_row_count = 0) THEN
2680 x_last_batch_id := x_batch_id;
2681 END IF;
2682
2683 X_ROW_COUNT := X_ROW_COUNT + 1;
2684
2685 --If the second batch is not a parent batch.
2686 IF (X_LAST_BATCH_ID <> X_BATCH_ID AND X_PHANTOM_ID IS NULL) THEN
2687
2688 CLOSE GET_BATCH_INFO;
2689 FND_MESSAGE.SET_NAME('INV', 'INV_LOTCONV_MULT_BATCHES');
2690 FND_MSG_PUB.ADD;
2691
2692 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
2693 FND_MESSAGE.RAISE_ERROR;
2694
2695 END IF;
2696
2697 -- Phantom product will be an ingredient in the parent batch
2698 IF (X_LINE_TYPE IN (1, 2) OR (X_LINE_TYPE = - 1 AND X_PHANTOM_ID = X_LAST_BATCH_ID)) THEN
2699 NULL;
2700 ELSE
2701 CLOSE GET_BATCH_INFO;
2702 FND_MESSAGE.SET_NAME('INV', 'INV_LOTCONV_INGREDIENT');
2703 FND_MSG_PUB.ADD;
2704
2705 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
2706 FND_MESSAGE.RAISE_ERROR;
2707
2708 END IF;
2709
2710 IF (L_DEBUG = 1) THEN
2711 INV_LOG_UTIL.TRACE('X_OPM_COSTED_FLAG:'||X_OPM_COSTED_FLAG, 'inv_uom_api_pub', '9');
2712 END IF;
2713
2714 IF (X_OPM_COSTED_FLAG IS NULL) THEN
2715
2716 FND_MESSAGE.SET_NAME('INV','INV_LOTCONV_INDITEM_COSTED');
2717 FND_MSG_PUB.ADD;
2718 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
2719 FND_MESSAGE.RAISE_ERROR;
2720
2721 END IF ;
2722
2723 END IF;
2724
2725 END LOOP;
2726 CLOSE GET_BATCH_INFO;
2727
2728 END;
2729 -- Conversion cannot be changed if no batches are associated with this item/lot.
2730
2731 IF (X_ROW_COUNT = 0) THEN
2732
2733 FND_MESSAGE.SET_NAME('INV', 'INV_LOTCONV_NO_BATCHES');
2734 FND_MSG_PUB.ADD;
2735
2736 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
2737 FND_MESSAGE.RAISE_ERROR;
2738
2739 END IF;
2740 -- Conversion can be updated only if batch status is WIP or Completed.
2741
2742 IF (X_BATCH_STATUS IN (2, 3)) THEN
2743 -- DBMS_OUTPUT.PUT_LINE('X_BATCH_STATUS:'||X_BATCH_STATUS||' ..Do nothing..');
2744 NULL;
2745 ELSE
2746 FND_MESSAGE.SET_NAME('INV', 'INV_LOTCONV_INVALID_BATCH_STS');
2747 FND_MSG_PUB.ADD;
2748
2749 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
2750 FND_MESSAGE.RAISE_ERROR;
2751
2752 END IF;
2753
2754 -- Primary qty can get affected only when conv is changed for batch detail uom.
2755 -- Raise error if from uom class is not same as batch secondary uom class.
2756
2757 IF (L_DEBUG = 1) THEN
2758 INV_LOG_UTIL.TRACE('Openning Cursor GET_BATCH:', 'inv_uom_api_pub', '9');
2759 END IF;
2760
2761 OPEN GET_BATCH;
2762 FETCH GET_BATCH INTO x_batch_row;
2763 CLOSE GET_BATCH;
2764
2765 L_BATCH_ID := X_BATCH_ROW.BATCH_ID; -- Batch id has been populated if the lot is yielded from single batch.
2766
2767 OPEN GET_MATL_LINES;
2768 LOOP
2769
2770 FETCH GET_MATL_LINES INTO x_matl_line;
2771 EXIT when GET_MATL_LINES%NOTFOUND ;
2772
2773 OPEN GET_UOM_CLASS (x_matl_line.dtl_um);
2774 FETCH GET_UOM_CLASS INTO x_uom_class;
2775 CLOSE GET_UOM_CLASS;
2776
2777 OPEN GET_UOM_CLASS(l_secondary_uom_code);
2778 FETCH GET_UOM_CLASS INTO x_sec_uom_class;
2779 CLOSE GET_UOM_CLASS;
2780
2781 -- Raise error if dtl_um class is not same as primary or secondary uom class.
2782 IF l_from_uom_class <> x_uom_class THEN
2783 IF x_uom_class <> x_sec_uom_class THEN
2784 CLOSE GET_MATL_LINES;
2785 FND_MESSAGE.SET_NAME('INV', 'INV_LOTCONV_UPD_WRNG_DTLUM_CLS');
2786 FND_MSG_PUB.ADD;
2787 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
2788 RAISE FND_API.G_EXC_ERROR;
2789
2790 END IF;
2791 END IF;
2792
2793 END LOOP;
2794 CLOSE GET_MATL_LINES;
2795
2796
2797 ELSIF (P_UPDATE_TYPE IN (3, 4)) THEN
2798
2799
2800 IF (L_TRACKING_QUANTITY_IND = 'P') THEN
2801
2802 FND_MESSAGE.SET_NAME('INV', 'INV_LOTC_TYPE_NOT_ALLOWED');
2803 FND_MSG_PUB.ADD;
2804
2805 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
2806 FND_MESSAGE.RAISE_ERROR;
2807
2808 END IF;
2809
2810 END IF; --- added
2811
2812 --populating onhand table for the item in loop with correct values based on p_onhand_type.
2813
2814 IF (L_DEBUG = 1) THEN
2815 INV_LOG_UTIL.TRACE('Openning Cursor CUR_ONHAND:', 'inv_uom_api_pub', '9');
2816 END IF;
2817
2818 FOR CC IN CUR_ONHAND
2819 LOOP
2820
2821 IF (L_DEBUG = 1) THEN
2822 INV_LOG_UTIL.TRACE('l_temp:' ||L_TEMP , 'inv_uom_api_pub', '9');
2823 END IF;
2824
2825 new_primary_qty := NULL;
2826 new_secondary_qty := NULL;
2827 transaction_primary_qty := NULL;
2828 TRANSACTION_SECONDARY_QTY := NULL;
2829
2830 L_QTY_TBL(L_TEMP).REVISION := CC.REVISION;
2831 L_QTY_TBL(L_TEMP).ORGANIZATION_ID := P_ORG_ID;
2832 L_QTY_TBL(L_TEMP).SUBINVENTORY_CODE := CC.SUBINVENTORY_CODE;
2833
2834 L_QTY_TBL(L_TEMP).LPN_ID := CC.LPN_ID;
2835 L_QTY_TBL(L_TEMP).LOCATOR_ID := CC.LOCATOR_ID;
2836 L_QTY_TBL(L_TEMP).OLD_PRIMARY_QTY := CC.ON_HAND;
2837 L_QTY_TBL(L_TEMP).OLD_SECONDARY_QTY := CC.SECONDARY_ONHAND;
2838 l_qty_tbl(l_temp).transaction_update_flag := 1;
2839
2840 IF ( P_UPDATE_TYPE = 4 OR P_UPDATE_TYPE = 2 ) THEN
2841
2842 -- calculate sec qty based on conversion.
2843 NEW_PRIMARY_QTY := L_QTY_TBL(L_TEMP).OLD_PRIMARY_QTY;
2844
2845 X_CALC_QTY := NEW_PRIMARY_QTY / P_UOM_RATE ;
2846
2847 IF (L_DEBUG = 1) THEN
2848 INV_LOG_UTIL.TRACE('Recalculate primary...X_CALC_QTY:' || X_CALC_QTY, 'inv_uom_api_pub', '9');
2849 END IF;
2850
2851 IF (X_CALC_QTY = - 99999) THEN
2852
2853 FND_MESSAGE.SET_NAME('INV', 'INV_LOTC_CONV_FUNC');
2854 FND_MSG_PUB.ADD;
2855
2856 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
2857 FND_MESSAGE.RAISE_ERROR;
2858
2859 ELSE
2860 NEW_SECONDARY_QTY := X_CALC_QTY;
2861
2862 END IF;
2863
2864 ELSE --recalculate primary
2865
2866 NEW_SECONDARY_QTY := L_QTY_TBL(L_TEMP).OLD_SECONDARY_QTY;
2867
2868 X_CALC_QTY := NEW_SECONDARY_QTY * P_UOM_RATE;
2869
2870 IF (L_DEBUG = 1) THEN
2871 INV_LOG_UTIL.TRACE('Recalculate primary...X_CALC_QTY:' || X_CALC_QTY, 'inv_uom_api_pub', '9');
2872 END IF;
2873
2874 IF (X_CALC_QTY = - 99999) THEN
2875
2876 FND_MESSAGE.SET_NAME('INV', 'INV_LOTC_CONV_FUNC');
2877 FND_MSG_PUB.ADD;
2878
2879 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
2880 FND_MESSAGE.RAISE_ERROR;
2881
2882 ELSE
2883 NEW_PRIMARY_QTY := X_CALC_QTY;
2884
2885 END IF;
2886
2887 END IF;
2888
2889 --========================================
2890 -- For type 3 and 4 populate the
2891 -- transaction quantities.
2892 -- ========================================
2893
2894 IF (P_UPDATE_TYPE = 3 OR P_UPDATE_TYPE = 4 ) THEN
2895
2896 TRANSACTION_PRIMARY_QTY := (NEW_PRIMARY_QTY - L_QTY_TBL(L_TEMP).OLD_PRIMARY_QTY);
2897 transaction_secondary_qty := (new_secondary_qty - l_qty_tbl(l_temp).old_secondary_qty);
2898
2899 IF (L_DEBUG = 1) THEN
2900 INV_LOG_UTIL.TRACE('TRANSACTION_PRIMARY_QTY:' || TRANSACTION_PRIMARY_QTY, 'inv_uom_api_pub', '9');
2901 INV_LOG_UTIL.TRACE('transaction_secondary_qty:' || TRANSACTION_SECONDARY_QTY, 'inv_uom_api_pub', '9');
2902 END IF;
2903
2904 END IF;
2905
2906 IF (P_UPDATE_TYPE = 1 OR P_UPDATE_TYPE = 2) THEN
2907
2908 OPEN BATCH_TXNS;
2909
2910 FETCH BATCH_TXNS INTO X_BATCH_QTY1, X_BATCH_QTY2;
2911
2912 CLOSE BATCH_TXNS;
2913
2914 IF (L_DEBUG = 1) THEN
2915 INV_LOG_UTIL.TRACE('X_BATCH_QTY1:' || X_BATCH_QTY1, 'inv_uom_api_pub', '9');
2916 INV_LOG_UTIL.TRACE('X_BATCH_QTY2:' || X_BATCH_QTY2, 'inv_uom_api_pub', '9');
2917 END IF;
2918
2919
2920 IF (P_UPDATE_TYPE = 1) THEN
2921
2922 TRANSACTION_PRIMARY_QTY := (P_UOM_RATE * (L_QTY_TBL(L_TEMP).OLD_SECONDARY_QTY - X_BATCH_QTY2)) - (L_QTY_TBL(L_TEMP).OLD_PRIMARY_QTY - X_BATCH_QTY1);
2923 TRANSACTION_SECONDARY_QTY := 0;
2924
2925 -- DBMS_OUTPUT.PUT_LINE('..TRANSACTION_PRIMARY_QTY:' || TRANSACTION_PRIMARY_QTY);
2926 -- DBMS_OUTPUT.PUT_LINE('..TRANSACTION_SECONDARY_QTY:' || TRANSACTION_SECONDARY_QTY);
2927
2928 ELSIF (P_UPDATE_TYPE = 2) THEN
2929
2930 TRANSACTION_SECONDARY_QTY := ((L_QTY_TBL(L_TEMP).OLD_PRIMARY_QTY - X_BATCH_QTY1) / P_UOM_RATE) - (L_QTY_TBL(L_TEMP).OLD_SECONDARY_QTY - X_BATCH_QTY2);
2931 TRANSACTION_PRIMARY_QTY := 0;
2932
2933 END IF;
2934
2935
2936 END IF;
2937
2938 L_QTY_TBL(L_TEMP).NEW_PRIMARY_QTY := NEW_PRIMARY_QTY;
2939 L_QTY_TBL(L_TEMP).NEW_SECONDARY_QTY := NEW_SECONDARY_QTY;
2940 L_QTY_TBL(L_TEMP).TRANSACTION_PRIMARY_QTY := TRANSACTION_PRIMARY_QTY;
2941 L_QTY_TBL(L_TEMP).TRANSACTION_SECONDARY_QTY := TRANSACTION_SECONDARY_QTY;
2942
2943 IF (L_DEBUG = 1) THEN
2944 INV_LOG_UTIL.TRACE('L_QTY_TBL(L_TEMP).TRANSACTION_PRIMARY_QTY' || L_QTY_TBL(L_TEMP).TRANSACTION_PRIMARY_QTY, 'inv_uom_api_pub', '9');
2945 END IF;
2946
2947 L_TEMP := L_TEMP + 1;
2948
2949 END LOOP;
2950 --end of populating pl-sql qty table.
2951
2952 IF (L_DEBUG = 1) THEN
2953 INV_LOG_UTIL.TRACE('Checing reservations exists or not ', 'inv_uom_api_pub', '9');
2954 END IF;
2955 -- Verify that no reservations exists for the item.
2956
2957 INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES (
2958 p_api_version_number => 1.0,
2959 p_init_msg_lst => 'T',
2960 x_return_status => l_return_status,
2961 x_msg_count => l_msg_count,
2962 x_msg_data => l_msg_data,
2963 p_organization_id => p_org_id,
2964 p_inventory_item_id => p_item_id,
2965 p_tree_mode => 2,
2966 P_IS_REVISION_CONTROL => FALSE,
2967 p_is_lot_control => TRUE, -- L_IS_LOT_CONTROL , hard coded because this is always true
2968 p_is_serial_control => FALSE,
2969 p_demand_source_type_id => 13,
2970 p_demand_source_header_id => - 9999,
2971 P_DEMAND_SOURCE_LINE_ID => - 9999,
2972 P_DEMAND_SOURCE_NAME => NULL,
2973 p_lot_expiration_date => NULL,
2974 p_revision => NULL,
2975 p_lot_number => l_lot_number,
2976 p_subinventory_code => NULL,
2977 p_locator_id => NULL,
2978 p_onhand_source => 3,
2979 x_qoh => l_qoh,
2980 x_rqoh => l_rqoh,
2981 x_qr => l_qr,
2982 x_qs => l_qs,
2983 X_ATT => L_ATT,
2984 X_ATR => L_ATR,
2985 P_TRANSFER_SUBINVENTORY_CODE => NULL,
2986 P_COST_GROUP_ID => NULL ,
2987 P_LPN_ID => NULL ,
2988 p_transfer_locator_id => NULL
2989 );
2990
2991 IF (L_DEBUG = 1) THEN
2992 INV_LOG_UTIL.TRACE('Query Quantities', 'inv_uom_api_pub', '9');
2993 INV_LOG_UTIL.TRACE('L_QR:' || L_QR, 'inv_uom_api_pub', '9');
2994 END IF;
2995
2996 IF (L_RETURN_STATUS <> 'S') THEN
2997
2998 FOR I IN 1..L_MSG_COUNT LOOP
2999 L_MSG_DATA := FND_MSG_PUB.GET(I, 'F');
3000 FND_MSG_PUB.ADD;
3001 FND_MESSAGE.RAISE_ERROR;
3002 END LOOP;
3003
3004 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
3005
3006 END IF;
3007
3008 IF (L_QR > 0) THEN
3009
3010 FND_MESSAGE.SET_NAME('INV', 'INV_LOTCONV_RSV_EXIST');
3011 FND_MSG_PUB.ADD;
3012 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
3013 RAISE FND_API.G_EXC_ERROR;
3014
3015 END IF;
3016
3017 -- End of reservation validationg for the item.
3018
3019 IF (L_DEBUG = 1) THEN
3020 INV_LOG_UTIL.TRACE('Checing pending transactions exists or not ', 'inv_uom_api_pub', '9');
3021 END IF;
3022
3023 --check for pending transactions against the item -
3024
3025 BEGIN
3026
3027 SELECT 1
3028 INTO L_PENDING_COUNT
3029 FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
3030 WHERE MMTT.INVENTORY_ITEM_ID = P_ITEM_ID
3031 AND MMTT.ORGANIZATION_ID = P_ORG_ID
3032 AND EXISTS( SELECT 1 FROM MTL_TRANSACTION_LOTS_TEMP MTLT
3033 WHERE MTLT.TRANSACTION_TEMP_ID = MMTT.TRANSACTION_TEMP_ID
3034 AND MTLT.LOT_NUMBER = L_LOT_NUMBER
3035 );
3036
3037 EXCEPTION
3038 WHEN OTHERS THEN
3039 L_PENDING_COUNT := 0;
3040 END;
3041
3042 IF (L_DEBUG = 1) THEN
3043 INV_LOG_UTIL.TRACE('Pending transactions:', 'inv_uom_api_pub', '9');
3044 INV_LOG_UTIL.TRACE('L_PENDING_COUNT:' || L_PENDING_COUNT, 'inv_uom_api_pub', '9');
3045 END IF;
3046
3047 IF (l_pending_count >0) THEN
3048
3049 RAISE L_PENDING_TRAN_EXISTS;
3050
3051 END IF;
3052
3053 -- End of check for pending transactions--
3054
3055 END;
3056
3057 ELSIF P_ONHAND_UPDATE NOT IN (1, 2) THEN
3058
3059 FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_PARAMETER_TYPE');
3060 FND_MESSAGE.SET_TOKEN('VALUE1', 'P_ONHAND_UPDATE');
3061 FND_MSG_PUB.ADD;
3062
3063 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
3064
3065 RAISE FND_API.G_EXC_ERROR;
3066
3067 END IF;
3068
3069 BEGIN
3070
3071 SELECT UNIT_OF_MEASURE , UOM_CLASS
3072 INTO L_FROM_UNIT_OF_MEASURE , L_FROM_UOM_CLASS
3073 FROM MTL_UNITS_OF_MEASURE
3074 WHERE UOM_CODE = P_FROM_UOM_CODE;
3075
3076 SELECT UNIT_OF_MEASURE , UOM_CLASS
3077 INTO L_TO_UNIT_OF_MEASURE , L_TO_UOM_CLASS
3078 FROM MTL_UNITS_OF_MEASURE
3079 WHERE UOM_CODE = P_TO_UOM_CODE;
3080
3081 EXCEPTION
3082 WHEN OTHERS THEN
3083 IF (L_DEBUG = 1) THEN
3084 INV_LOG_UTIL.TRACE('Exception raised when retriving uom and uom class:', 'inv_uom_api_pub', '9');
3085 END IF;
3086 null;
3087 END;
3088
3089 IF p_action_type = 'I' THEN
3090
3091 SELECT MTL_CONVERSION_ID_S.NEXTVAL
3092 INTO L_CONVERSION_ID
3093 FROM FND_DUAL;
3094
3095 -- populating conversion pl-sql table.
3096 L_CONV_REC.CONVERSION_ID := L_CONVERSION_ID;
3097
3098 END IF;
3099
3100 L_CONV_REC.LOT_NUMBER := P_LOT_NUMBER;
3101 l_conv_rec.organization_id := p_org_id;
3102 l_conv_rec.inventory_item_id := p_item_id;
3103 l_conv_rec.from_unit_of_measure := l_from_unit_of_measure;
3104 l_conv_rec.from_uom_code := p_from_uom_code;
3105 l_conv_rec.from_uom_class := l_from_uom_class;
3106 l_conv_rec.to_unit_of_measure := l_to_unit_of_measure;
3107 l_conv_rec.to_uom_code := p_to_uom_code;
3108 l_conv_rec.to_uom_class := l_to_uom_class;
3109 l_conv_rec.conversion_rate := p_uom_rate;
3110 L_CONV_REC.DISABLE_DATE := P_DISABLE_DATE;
3111 L_CONV_REC.CREATED_BY := L_USERID ;
3112 L_CONV_REC.CREATION_DATE := SYSDATE;
3113 l_conv_rec.last_updated_by := L_USERID ;
3114 L_CONV_REC.LAST_UPDATE_DATE := SYSDATE;
3115 l_conv_rec.program_update_date := SYSDATE;
3116
3117 --end of populating pl-sql conversion table.
3118
3119 BEGIN
3120
3121 MTL_LOT_UOM_CONV_PUB.create_lot_uom_conversion (p_api_version => 1.0 ,
3122 P_INIT_MSG_LIST => 'T' ,
3123 P_COMMIT => 'F' , -- 'T'
3124 P_VALIDATION_LEVEL => '0' ,
3125 p_action_type => p_action_type , -- 'I' or 'U'
3126 P_UPDATE_TYPE_INDICATOR => P_UPDATE_TYPE ,
3127 p_reason_id => p_reason_id ,
3128 P_BATCH_ID => L_BATCH_ID ,
3129 P_PROCESS_DATA => 'Y' , --'N' ,
3130 P_LOT_UOM_CONV_REC => L_CONV_REC , -- pl/sql table of lot uom conversion record.
3131 P_QTY_UPDATE_TBL => L_QTY_TBL , -- pl/sql table of onhand.
3132 X_RETURN_STATUS => L_RETURN_STATUS ,
3133 X_MSG_COUNT => L_MSG_COUNT ,
3134 x_msg_data => l_msg_data ,
3135 X_SEQUENCE => L_SEQUENCE );
3136
3137 END;
3138
3139 IF (l_return_status <> 'S') THEN
3140
3141 FOR I IN 1..l_msg_count
3142 LOOP
3143 L_MSG_DATA := FND_MSG_PUB.GET(I, 'F');
3144 FND_MSG_PUB.ADD;
3145 fnd_message.raise_error;
3146 END LOOP;
3147
3148 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
3149
3150 END IF;
3151
3152 END IF; -- Lot specific conversion.
3153
3154 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS ;
3155
3156 COMMIT;
3157
3158 EXCEPTION
3159 WHEN L_INVALID_BASE_UOM THEN
3160 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
3161 FND_MSG_PUB.COUNT_AND_GET
3162 ( P_COUNT => X_MSG_COUNT
3163 , P_DATA => X_MSG_DATA
3164 );
3165 X_MSG_DATA := FND_MSG_PUB.GET(X_MSG_COUNT, 'F');
3166
3167 WHEN L_INVALID_REASON THEN
3168 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
3169 FND_MSG_PUB.COUNT_AND_GET
3170 ( P_COUNT => X_MSG_COUNT
3171 , P_DATA => X_MSG_DATA
3172 );
3173 X_MSG_DATA := FND_MSG_PUB.GET(X_MSG_COUNT, 'F');
3174 WHEN L_INVALID_OBJECT THEN
3175
3176 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
3177 FND_MSG_PUB.COUNT_AND_GET
3178 ( P_COUNT => X_MSG_COUNT
3179 , P_DATA => X_MSG_DATA
3180 );
3181
3182 X_MSG_DATA := FND_MSG_PUB.GET(X_MSG_COUNT, 'F');
3183
3184 /* WHEN L_INVALID_LOT THEN
3185
3186 FND_MESSAGE.SET_NAME('INV', 'INV_LOTC_LOT_INVALID');
3187 FND_MSG_PUB.ADD;
3188 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
3189 FND_MSG_PUB.COUNT_AND_GET
3190 ( P_COUNT => X_MSG_COUNT
3191 , P_DATA => X_MSG_DATA
3192 );
3193
3194 X_MSG_DATA := FND_MSG_PUB.GET(X_MSG_COUNT, 'F');
3195 */
3196 WHEN L_LOT_CONV_NOT_EXISTS THEN
3197
3198 FND_MESSAGE.SET_NAME('INV', 'INV_NO_CONVERSIONS');
3199 FND_MSG_PUB.ADD;
3200 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
3201
3202 FND_MSG_PUB.COUNT_AND_GET
3203 ( P_COUNT => X_MSG_COUNT
3204 , P_DATA => X_MSG_DATA
3205 );
3206
3207 X_MSG_DATA := FND_MSG_PUB.GET(X_MSG_COUNT, 'F');
3208 -- X_MSG_DATA := 'Lot Conversion does not exists';
3209
3210 WHEN L_PENDING_TRAN_EXISTS THEN
3211
3212 FND_MESSAGE.SET_NAME('GMI', 'GMI_LOTCONV_PENDING_EXIST');
3213 FND_MSG_PUB.ADD;
3214 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
3215 FND_MSG_PUB.COUNT_AND_GET
3216 ( P_COUNT => X_MSG_COUNT
3217 , P_DATA => X_MSG_DATA
3218 );
3219
3220 X_MSG_DATA := FND_MSG_PUB.GET(X_MSG_COUNT, 'F');
3221
3222 WHEN L_INVALID_UOM_RATE THEN
3223
3224 ROLLBACK;
3225 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
3226 FND_MESSAGE.SET_NAME('INV', 'INV_NEG_CONV');
3227 FND_MSG_PUB.ADD;
3228 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
3229 FND_MSG_PUB.COUNT_AND_GET
3230 ( P_COUNT => X_MSG_COUNT
3231 , P_DATA => X_MSG_DATA
3232 );
3233
3234 X_MSG_DATA := FND_MSG_PUB.GET(X_MSG_COUNT, 'F');
3235
3236 WHEN FND_API.G_EXC_ERROR THEN
3237
3238 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
3239
3240 -- Get message count and data
3241 FND_MSG_PUB.COUNT_AND_GET
3242 ( P_COUNT => X_MSG_COUNT
3243 , P_DATA => X_MSG_DATA
3244 );
3245
3246 X_MSG_DATA := FND_MSG_PUB.GET(X_MSG_COUNT, 'F');
3247
3248 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3249
3250 X_RETURN_STATUS := fnd_api.g_ret_sts_unexp_error;
3251
3252 -- Get message count and data
3253 FND_MSG_PUB.COUNT_AND_GET
3254 ( P_COUNT => X_MSG_COUNT
3255 , P_DATA => X_MSG_DATA
3256 );
3257
3258 WHEN L_INVALID_UOM_EXC THEN
3259
3260 FND_MESSAGE.SET_NAME('INV', 'INV_UOM_NOTFOUND');
3261 FND_MSG_PUB.ADD;
3262 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
3263 FND_MSG_PUB.COUNT_AND_GET
3264 ( P_COUNT => X_MSG_COUNT
3265 , P_DATA => X_MSG_DATA
3266 );
3267 X_MSG_DATA := FND_MSG_PUB.GET(X_MSG_COUNT, 'F');
3268
3269 WHEN L_CONVERSION_EXISTS_EXC THEN
3270 X_RETURN_STATUS := L_RET_CONV_EXISTS_WARNING;
3271
3272 FND_MSG_PUB.COUNT_AND_GET
3273 ( P_COUNT => X_MSG_COUNT
3274 , P_DATA => X_MSG_DATA
3275 );
3276
3277 X_MSG_DATA := FND_MSG_PUB.GET(X_MSG_COUNT, 'F');
3278
3279 WHEN L_INVALID_ITEM_EXC THEN
3280
3281 FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_ITEM');
3282 FND_MSG_PUB.ADD;
3283 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
3284 FND_MSG_PUB.COUNT_AND_GET
3285 ( P_COUNT => X_MSG_COUNT
3286 , P_DATA => X_MSG_DATA
3287 );
3288
3289 WHEN L_UOM_FROMTO_EXC THEN
3290
3291 FND_MESSAGE.SET_NAME('INV', 'INV_LOTC_UOM_FROMTO_ERROR');
3292 FND_MSG_PUB.ADD;
3293 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
3294 FND_MSG_PUB.COUNT_AND_GET
3295 ( P_COUNT => X_MSG_COUNT
3296 , P_DATA => X_MSG_DATA
3297 );
3298 X_MSG_DATA := FND_MSG_PUB.GET(X_MSG_COUNT, 'F');
3299
3300 WHEN L_CONV_NOT_EXISTS THEN
3301
3302 FND_MESSAGE.SET_NAME('INV', 'INV_NO_CONVERSIONS');
3303 FND_MSG_PUB.ADD;
3304 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
3305
3306 FND_MSG_PUB.COUNT_AND_GET
3307 ( P_COUNT => X_MSG_COUNT
3308 , P_DATA => X_MSG_DATA
3309 );
3310
3311 X_MSG_DATA := FND_MSG_PUB.GET(X_MSG_COUNT, 'F');
3312
3313 WHEN OTHERS THEN
3314
3315 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
3316 FND_MSG_PUB.COUNT_AND_GET
3317 ( P_COUNT => X_MSG_COUNT
3318 , P_DATA => X_MSG_DATA
3319 );
3320 X_MSG_DATA := FND_MSG_PUB.GET(X_MSG_COUNT, 'F');
3321 END UOM_CONVERSION;
3322
3323 END INV_UOM_API_PUB;