[Home] [Help]
PACKAGE BODY: APPS.INV_LOT_SEL_ATTR
Source
1 PACKAGE BODY INV_LOT_SEL_ATTR AS
2 /* $Header: INVLSDFB.pls 120.16.12020000.6 2013/01/08 08:28:05 xzhixong ship $ */
3
4 /* Global constant holding package name */
5 g_pkg_name CONSTANT VARCHAR2(20) := 'INV_LOT_SEL_ATTR' ;
6
7 g_version_printed BOOLEAN := FALSE;
8
9 PROCEDURE debug(
10 p_message IN VARCHAR2,
11 p_module IN VARCHAR2,
12 p_level IN NUMBER
13 ) IS
14
15 l_debug NUMBER := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
16
17 BEGIN
18
19 IF NOT g_version_printed THEN
20 IF (l_debug = 1 ) THEN /* Bug#5401181*/
21 inv_log_util.TRACE ('$Header: INVLSDFB.pls 120.16.12020000.6 2013/01/08 08:28:05 xzhixong ship $',
22 g_pkg_name,
23 9
24 );
25 END IF;
26 g_version_printed := TRUE;
27
28 END IF;
29 IF (l_debug = 1 ) THEN /* Bug#5401181*/
30 inv_log_util.TRACE (
31 p_message,
32 g_pkg_name || '.' || p_module,
33 p_level
34 );
35 END IF;
36
37 --dbms_output.put_line(p_message);
38 END debug;
39
40
41 /* ----------------------------------------------------------
42 * Procedure to fetch descriptive flexfield context category
43 * for a given item and organization
44 *----------------------------------------------------------*/
45 PROCEDURE get_context_code(context_value OUT NOCOPY VARCHAR2,
46 org_id IN NUMBER,
47 item_id IN NUMBER,
48 flex_name IN VARCHAR2,
49 p_lot_serial_number IN VARCHAR2 ) IS
50
51 i NUMBER;
52 l_context_column_name VARCHAR2(12);
53 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
54 l_module_name VARCHAR2(30) := 'GET_CONTEXT_CODE';
55 BEGIN
56 context_value := NULL;
57 l_context_column_name := 'ITEM';
58 IF(p_lot_serial_number IS NOT NULL) THEN
59 IF(flex_name = 'Lot Attributes') THEN
60 SELECT lot_attribute_category
61 INTO context_value
62 FROM mtl_lot_numbers
63 WHERE lot_number = p_lot_serial_number
64 AND inventory_item_id = item_id
65 AND organization_id = org_id;
66 ELSIF (flex_name = 'Serial Attributes') THEN
67 SELECT serial_attribute_category
68 INTO context_value
69 FROM mtl_serial_numbers
70 WHERE serial_number = p_lot_serial_number
71 AND inventory_item_id = item_id
72 AND current_organization_id = org_id;
73 END IF;
74 ELSE
75 BEGIN
76 SELECT descriptive_flex_context_code
77 INTO context_value
78 FROM mtl_flex_context
79 WHERE organization_id = -1
80 AND context_column_name = l_context_column_name
81 AND descriptive_flexfield_name = flex_name
82 AND context_column_value_id = item_id;
83
84 EXCEPTION
85 WHEN NO_DATA_FOUND THEN
86 NULL;
87 END;
88
89 IF context_value IS NULL THEN
90
91 BEGIN
92
93 SELECT descriptive_flex_context_code
94 INTO context_value
95 FROM mtl_flex_context
96 WHERE organization_id = org_id
97 AND context_column_name = l_context_column_name
98 AND descriptive_flexfield_name = flex_name
99 AND context_column_value_id = item_id;
100
101 EXCEPTION
102 WHEN NO_DATA_FOUND THEN
103 NULL;
104 END;
105
106 END IF;
107
108 l_context_column_name := 'CATEGORY';
109
110 IF context_value IS NULL THEN
111 BEGIN
112 SELECT descriptive_flex_context_code
113 INTO context_value
114 FROM mtl_flex_context mfc,
115 mtl_item_categories mic
116 WHERE mfc.organization_id = -1
117 AND mic.organization_id = org_id
118 AND mfc.category_set_id = mic.category_set_id
119 AND mfc.context_column_value_id = mic.category_id
120 AND mfc.descriptive_flexfield_name = flex_name
121 AND mic.inventory_item_id = item_id
122 AND mfc.context_column_name = l_context_column_name;
123
124 EXCEPTION
125 WHEN NO_DATA_FOUND THEN
126 context_value := NULL;
127 END;
128 END IF;
129
130 IF context_value IS NULL THEN
131 BEGIN
132 SELECT descriptive_flex_context_code
133 INTO context_value
134 FROM mtl_flex_context mfc,
135 mtl_item_categories mic
136 WHERE mfc.organization_id = org_id
137 AND mfc.organization_id = mic.organization_id
138 AND mfc.category_set_id = mic.category_set_id
139 AND mfc.context_column_value_id = mic.category_id
140 AND mfc.descriptive_flexfield_name = flex_name
141 AND mic.inventory_item_id = item_id
142 AND mfc.context_column_name = l_context_column_name;
143
144 EXCEPTION
145 WHEN NO_DATA_FOUND THEN
146 context_value := NULL;
147 END;
148 END IF;
149 END IF;
150 EXCEPTION
151 WHEN OTHERS THEN
152 context_value := NULL;
153 debug('Unexpected exception : '||SQLERRM,l_module_name, 0);
154 END get_context_code;
155
156 PROCEDURE get_context_code( context_value OUT NOCOPY VARCHAR2,
157 org_id IN NUMBER,
158 item_id IN NUMBER,
159 flex_name IN VARCHAR2)
160 IS
161 BEGIN
162 get_context_code(
163 context_value => context_value
164 , org_id => org_id
165 , item_id => item_id
166 , flex_name => flex_name
167 , p_lot_serial_number => null);
168
169 END get_context_code;
170
171
172 /* ----------------------------------------------------------
173 * Procedure to fetch descriptive flexfield context category
174 * for a given item and organization -- 2756040
175 *----------------------------------------------------------*/
176
177 PROCEDURE get_lot_serial_context(
178 context_value OUT NOCOPY VARCHAR2,
179 org_id IN NUMBER,
180 item_id IN NUMBER,
181 p_lot_serial IN VARCHAR2,
182 flex_name IN VARCHAR2
183 ) IS
184
185 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
186
187 l_module_name VARCHAR2(30) := 'GET_LOT_SERIAL_CONTEXT';
188 l_progress_indicator VARCHAR2(10) := '0';
189
190 SERIAL_ATTRIBUTES VARCHAR2(30) := 'Serial Attributes';
191 LOT_ATTRIBUTES VARCHAR2(30) := 'Lot Attributes';
192
193 l_context_value VARCHAR2(200);
194
195 BEGIN
196
197 debug('In procedure : ', l_module_name, 0);
198
199 IF (l_debug > 0) THEN
200
201 debug('org_id => '||org_id,l_module_name,9);
202 debug('itemid => '||item_id,l_module_name,9);
203 debug('p_lot_serial => '||p_lot_serial,l_module_name,9);
204 debug('flex_name => '||flex_name,l_module_name,9);
205
206 END IF;
207
208 l_progress_indicator := '10';
209
210 IF (flex_name = SERIAL_ATTRIBUTES) THEN
211
212 l_progress_indicator := '20';
213
214 SELECT serial_attribute_category
215 INTO l_context_value
216 FROM mtl_serial_numbers
217 WHERE serial_number = p_lot_serial
218 AND inventory_item_id = item_id
219 AND current_organization_id = org_id;
220
221 ELSIF (flex_name = LOT_ATTRIBUTES) THEN
222
223 l_progress_indicator := '30';
224
225 SELECT lot_attribute_category
226 INTO l_context_value
227 FROM mtl_lot_numbers
228 WHERE lot_number = p_lot_serial
229 AND inventory_item_id = item_id
230 AND organization_id = org_id;
231
232 ELSE
233
234 l_progress_indicator := '30';
235
236 debug('Invalid value of parameter flex name :'||flex_name,
237 l_module_name,
238 0);
239
240 RAISE NO_DATA_FOUND;
241
242 END IF;
243
244 IF (l_debug > 0) THEN
245
246 debug(' context value => '||l_context_value, l_module_name, 9);
247
248 END IF;
249
250 context_value := l_context_value;
251
252 debug('Call success ', l_module_name, 0);
253
254 EXCEPTION
255 WHEN OTHERS THEN
256
257 debug('Unexpected exception : '||SQLERRM||
258 ' at '||l_progress_indicator, l_module_name, 0);
259
260 context_value := NULL;
261
262 END get_lot_serial_context;
263
264
265 /*------------------------------------------------
266 * Private procedure to obtain the column type
267 * given table name and column name
268 *------------------------------------------------*/
269 PROCEDURE get_column_type(
270 p_table_name IN VARCHAR2
271 , p_column_name IN VARCHAR2
272 , x_column_type OUT NOCOPY VARCHAR2) IS
273
274 /** Bug 2600351 -- selecting from all_Tab_columns causes performance issue.
275 It takes about 171 second from 173 thousand rows and the only rows we need is
276 only 10 rows **/
277
278 /** Instead of selecting from all_tab_columns, we will just use PL/SQL processing to
279 find out the column type, anyway, it is only 3 return types **/
280
281 l_retVarchar VARCHAR2(15) := 'VARCHAR2';
282 l_retDate VARCHAR2(15) := 'DATE';
283 l_retNumber VARcHAR2(15) := 'NUMBER';
284 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
285 BEGIN
286 if( upper(p_table_name) = 'MTL_LOT_NUMBERS' ) then
287 if( upper(p_column_name ) = 'VENDOR_ID') then
288 x_column_type := l_retNumber;
289 elsif( upper(p_column_name) = 'GRADE_CODE') then
290 x_column_type := l_retVarchar;
291 elsif( upper(p_column_name) = 'ORIGINATION_DATE') then
292 x_column_type := l_retDate;
293 elsif( upper(p_column_name) = 'DATE_CODE') then
294 x_column_type := l_retVarchar;
295 elsif( upper(p_column_name) = 'STATUS_ID') then
296 x_column_type := l_retNumber;
297 elsif( upper(p_column_name) = 'CHANGE_DATE') then
298 x_column_type := l_retDate;
299 elsif( upper(p_column_name) = 'AGE') then
300 x_column_type := l_retNumber;
301 elsif( upper(p_column_name) = 'RETEST_DATE') then
302 x_column_type := l_retDate;
303 elsif( upper(p_column_name) = 'MATURITY_DATE') then
304 x_column_type := l_retDate;
305 elsif( upper(p_column_name) = 'LOT_ATTRIBUTE_CATEGORY') then
306 x_column_type := l_retVarchar;
307 elsif( upper(p_column_name) = 'ITEM_SIZE') then
308 x_column_type := l_retNumber;
309 elsif( upper(p_column_name) = 'COLOR') then
310 x_column_type := l_retVarchar;
311 elsif( upper(p_column_name) = 'VOLUME') then
312 x_column_type := l_retNumber;
313 elsif( upper(p_column_name) = 'VOLUME_UOM') then
314 x_column_type := l_retVarchar;
315 elsif( upper(p_column_name) = 'PLACE_OF_ORIGIN') then
316 x_column_type := l_retVarchar;
317 elsif( upper(p_column_name) = 'BEST_BY_DATE') then
318 x_column_type := l_retDate;
319 elsif( upper(p_column_name) = 'LENGTH') then
320 x_column_type := l_retNumber;
321 elsif( upper(p_column_name) = 'LENGTH_UOM') then
322 x_column_type := l_retVarchar;
323 elsif( upper(p_column_name) = 'RECYCLED_CONTENT') then
324 x_column_type := l_retNumber;
325 elsif( upper(p_column_name) = 'THICKNESS') then
326 x_column_type := l_retNumber;
327 elsif( upper(p_column_name) = 'THICKNESS_UOM') then
328 x_column_type := l_retVarchar;
329 elsif( upper(p_column_name) = 'WIDTH') then
330 x_column_type := l_retNumber;
331 elsif( upper(p_column_name) = 'WIDTH_UOM') then
332 x_column_type := l_retVarchar;
333 elsif( upper(p_column_name) = 'CURL_WRINKLE_FOLD') then
334 x_column_type := l_retVarchar;
335 elsif( upper(p_column_name) = 'SUPPLIER_LOT_NUMBER') then
336 x_column_type := l_retVarchar;
337 elsif( upper(p_column_name) = 'TERRITORY_CODE') then
338 x_column_type := l_retVarchar;
339 elsif( upper(p_column_name) = 'VENDOR_NAME') then
340 x_column_type := l_retVarchar;
341 elsif( substr(upper(p_column_name), 1, 11) = 'C_ATTRIBUTE') then
342 x_column_type := l_retVarchar;
343 elsif( substr(upper(p_column_name), 1, 11) = 'N_ATTRIBUTE') then
344 x_column_type := l_retNumber;
345 elsif( substr(upper(p_column_name), 1, 11) = 'D_ATTRIBUTE') then
346 x_column_type := l_retDate;
347 else
348 x_column_type := NULL;
349 end if;
350 elsif( upper(p_table_name) = 'MTL_SERIAL_NUMBERS' ) then
351 if( upper(p_column_name) = 'SERIAL_ATTRIBUTE_CATEGORY' )then
352 x_column_type := l_retVarchar;
353 elsif( upper(p_column_name) = 'ORIGINATION_DATE') then
354 x_column_type := l_retDate;
355 elsif( substr(upper(p_column_name), 1, 11) = 'C_ATTRIBUTE') then
356 x_column_type := l_retVarchar;
357 elsif( substr(upper(p_column_name), 1, 11) = 'N_ATTRIBUTE') then
358 x_column_type := l_retNumber;
359 elsif( substr(upper(p_column_name), 1, 11) = 'D_ATTRIBUTE') then
360 x_column_type := l_retDate;
361 elsif( upper(p_column_name) = 'STATUS_ID') then
362 x_column_type := l_retNumber;
363 elsif( upper(p_column_name) = 'TERRITORY_CODE') then
364 x_column_type := l_retVarchar;
365 else
366 x_column_type := NULL;
367 end if;
368 end if;
369
370 /*
371 OPEN c_column_type;
372 FETCH c_column_type INTO x_column_type;
373 CLOSE c_column_type;
374 */
375 /*EXCEPTION
376 WHEN NO_DATA_FOUND THEN
377 x_column_type := NULL;*/
378 END;
379
380 /*---------------------------------------------------------------------
381 * procedure definition for get lot number attributes defaults
382 *---------------------------------------------------------------------*/
383 PROCEDURE get_default(
384 x_attributes_default OUT NOCOPY lot_sel_attributes_tbl_type
385 , x_attributes_default_count OUT NOCOPY NUMBER
386 , x_return_status OUT NOCOPY VARCHAR2
387 , x_msg_count OUT NOCOPY NUMBER
388 , x_msg_data OUT NOCOPY VARCHAR2
389 , p_table_name IN VARCHAR2
390 , p_attributes_name IN VARCHAR2
391 , p_inventory_item_id IN NUMBER
392 , p_organization_id IN NUMBER
393 , p_lot_serial_number IN VARCHAR2
394 , p_attributes IN lot_sel_attributes_tbl_type) IS
395
396 c_api_name CONSTANT VARCHAR2(30) := 'get_default';
397 v_flexfield fnd_dflex.dflex_r;
398 v_flexinfo fnd_dflex.dflex_dr;
399 v_contexts fnd_dflex.contexts_dr;
400 v_segments fnd_dflex.segments_dr;
401
402 v_attributes_category VARCHAR2(50) :=NULL;
403 v_global_code VARCHAR2(50);
404 i BINARY_INTEGER;
405 j BINARY_INTEGER;
406 k BINARY_INTEGER;
407 v_col_index BINARY_INTEGER;
408 v_rec_index NUMBER;
409 v_isAllNull BOOLEAN;
410 v_enabled BOOLEAN;
411 v_colName VARCHAR2(50);
412 v_value VARCHAR2(200);
413 v_a_name VARCHAR2(50);
414 l_context_column_name VARCHAR2(240); --bug 2474713
415 l_context_prompt fnd_descriptive_flexs_vl.form_context_prompt%TYPE; --bug#2474713
416 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
417
418 TYPE t_genref is REF CURSOR;
419 default_value_csr t_genref;
420 l_default_date_value DATE;
421 l_default_numeric_value NUMBER;
422 l_default_char_value VARCHAR2(2000);
423 l_count NUMBER;
424 BEGIN
425 -- Get flexfield
426 IF (l_debug = 1) THEN
427 inv_log_util.trace('Get Flexfield ' || p_attributes_name, 'LOTSERATTR');
428 END IF;
429 fnd_dflex.get_flexfield('INV', p_attributes_name, v_flexfield, v_flexinfo);
430
431 -- Get Contexts
432 fnd_dflex.get_contexts(v_flexfield, v_contexts);
433 IF (l_debug = 1) THEN
434 inv_log_util.trace('Get context ' , 'LOTSERATTR');
435 END IF;
436 /*bug 2474713 retrieve the context_column_name and the context_prompt */
437 l_context_column_name := v_flexinfo.context_column_name;
438 l_context_prompt := v_flexinfo.form_context_prompt;
439 -- Get attributes category
440 get_context_code(
441 context_value => v_attributes_category
442 , org_id => p_organization_id
443 , item_id => p_inventory_item_id
444 , flex_name => p_attributes_name
445 , p_lot_serial_number => p_lot_serial_number);
446
447 /*-------------------------------------------------------
448 * STEP 1: Check whether all the input columns are NULL
449 *------------------------------------------------------*/
450
451 v_isAllNull := TRUE; /* set initial value to true */
452 v_enabled := FALSE; /* set initially no segments is enabled */
453
454 <<contextLoop>>
455 FOR i IN 1..v_contexts.ncontexts LOOP
456 IF(v_contexts.is_enabled(i) AND
457 ((UPPER(v_contexts.context_code(i)) = UPPER(v_attributes_category)) OR
458 v_contexts.is_global(i))) THEN
459
460 -- Get segmentse
461 IF (l_debug = 1) THEN
462 inv_log_util.trace('get segment', 'LOTSERATTR');
463 END IF;
464 fnd_dflex.get_segments(fnd_dflex.make_context(
465 v_flexfield, v_contexts.context_code(i)), v_segments, TRUE);
466
467 <<segmentLoop>>
468 FOR j IN 1..v_segments.nsegments LOOP
469 IF v_segments.is_enabled(j) THEN
470 v_enabled := TRUE;
471 v_colName := v_segments.application_column_name(j);
472
473 v_col_index := NULL;
474 <<columnLoop>>
475 FOR k IN 1..p_attributes.count() LOOP
476 IF UPPER(v_colName) = UPPER(p_attributes(k).column_name) THEN
477 v_col_index := k;
478 EXIT columnLoop; -- found column
479 END IF;
480 END LOOP columnLoop;
481
482 IF v_col_index IS NOT NULL THEN
483 IF(p_attributes(v_col_index).column_value IS NOT NULL) THEN
484 v_isAllNull := FALSE;
485 EXIT contextLoop;
486 END IF;
487 END IF;
488 END IF;
489 END LOOP segmentLoop;
490 END IF;
491 END LOOP contextLoop;
492
493 x_attributes_default_count := 0;
494 IF(v_enabled) THEN
495 /*-------------------------------------------------------------------
496 * STEP 2. ASSIGN DEFAUL VALUE when there is/are segment(s) enabled
497 * If all the input values are null, return default values for
498 * all the segments of this context
499 * If there are not null input values, return default values for
500 * only required segments.
501 *------------------------------------------------------------------*/
502
503 IF (l_debug = 1) THEN
504 inv_log_util.trace('assign default value ', 'LOTSERATTR');
505 END IF;
506 v_rec_index := 0;
507
508 <<contextLoop1>>
509 FOR i IN 1..v_contexts.ncontexts LOOP
510 IF(v_contexts.is_enabled(i) AND
511 ((UPPER(v_contexts.context_code(i)) = UPPER(v_attributes_category)) OR
512 v_contexts.is_global(i))) THEN
513 /*bug 2474713 populate x_attributes_default with the context_column_name.
514 All the other fields in the record are also populated */
515
516 IF(NOT(v_contexts.is_global(i))) THEN
517 v_rec_index := v_rec_index + 1;
518 x_attributes_default(v_rec_index).COLUMN_NAME := l_context_column_name;
519 IF (l_debug = 1) THEN
520 inv_log_util.trace('get_column_type', 'LOTSERATTR');
521 END IF;
522 get_column_type(
523 p_table_name => p_table_name,
524 p_column_name => x_attributes_default(v_rec_index).COLUMN_NAME,
525 x_column_type => x_attributes_default(v_rec_index).COLUMN_TYPE);
526
527 x_attributes_default(v_rec_index).REQUIRED := 'TRUE';
528 x_attributes_default(v_rec_index).PROMPT := l_context_prompt;
529 END IF; /*bug2474713*/
530
531 -- Get segments
532 fnd_dflex.get_segments(fnd_dflex.make_context(v_flexfield,
533 v_contexts.context_code(i)),
534 v_segments, TRUE);
535 <<segmentLoop1>>
536 FOR j IN 1..v_segments.nsegments LOOP
537 IF v_segments.is_enabled(j) THEN
538 v_rec_index := v_rec_index +1 ;
539 x_attributes_default(v_rec_index).COLUMN_NAME := v_segments.application_column_name(j);
540 get_column_type(
541 p_table_name,
542 x_attributes_default(v_rec_index).COLUMN_NAME,
543 x_attributes_default(v_rec_index).COLUMN_TYPE);
544 IF(v_segments.is_required(j)) THEN
545 x_attributes_default(v_rec_index).REQUIRED := 'TRUE';
546 ELSE
547 x_attributes_default(v_rec_index).REQUIRED := 'FALSE';
548 END IF;
549
550 /* Case 1: all the input are null, give default values to all the columns*/
551 IF(v_isAllNull) THEN
552 if( l_debug = 1) then
553 inv_log_util.trace('is all null', 'LOTSERATTR');
554 end if;
555
556 if( v_segments.default_type(j) = 'S') then
557 open default_value_csr for v_segments.default_value(j);
558 l_count := 0;
559 LOOP
560 if( x_attributes_default(v_rec_index).COLUMN_TYPE = 'DATE') THEN
561 FETCH default_value_Csr into l_default_date_value;
562 x_attributes_default(v_rec_index).COLUMN_VALUE :=
563 fnd_date.date_to_canonical(l_default_date_value);
564 elsif ( x_attributes_default(v_rec_index).COLUMN_TYPE = 'NUMBER') then
565 FETCH default_value_csr into l_default_numeric_value;
566 x_attributes_default(v_rec_index).COLUMN_VALUE :=
567 to_char(l_default_numeric_value);
568 else
569 FETCH default_value_csr into l_default_char_value;
570 x_attributes_default(v_rec_index).COLUMN_VALUE := substr(l_default_char_value, 1, 150);
571 end if;
572 EXIT WHEN default_value_csr%NOTFOUND;
573 l_count := l_count + 1;
574 end loop;
575 if( l_count = 0 ) then
576 FND_MESSAGE.SET_NAME('FND', 'FLEX-DFLT NO SQL ROWS');
577 FND_MESSAGE.SET_TOKEN('SEGMENT_NAME', v_segments.segment_name(j));
578 FND_MESSAGE.SET_TOKEN('APPLICATION_SHORT_NAME', 'INV');
579 FND_MESSAGE.SET_TOKEN('FLEXFIELD_NAME', p_attributes_name);
580 FND_MESSAGE.SET_TOKEN('SQL_STRING', v_segments.default_value(j));
581 FND_MSG_PUB.ADD;
582 elsif( l_count > 1 ) then
583 FND_MESSAGE.SET_NAME('FND', 'FLEX-DFLT MULTIPLE SQL ROWS');
584 FND_MESSAGE.SET_TOKEN('SQLSTR', v_segments.default_value(j));
585 end if;
586 else
587 x_attributes_default(v_rec_index).COLUMN_VALUE := v_segments.default_value(j);
588 end if;
589 x_attributes_default(v_rec_index).PROMPT := v_segments.row_prompt(j);
590 ELSE
591 /* Case 2: not all are null, set those required segments and not null inputs*/
592 v_col_index := NULL;
593 <<columnLoop1>>
594 FOR k IN 1..p_attributes.count() LOOP
595 IF UPPER(v_segments.application_column_name(j))=UPPER(p_attributes(k).column_name) THEN
596 v_col_index := k;
597 EXIT columnLoop1; -- found column
598 END IF;
599 END LOOP columnLoop1;
600
601 IF((v_col_index IS NOT NULL) AND (p_attributes(v_col_index).COLUMN_VALUE IS NOT NULL))THEN
602 x_attributes_default(v_rec_index).COLUMN_VALUE := p_attributes(v_col_index).COLUMN_VALUE;
603
604 ELSIF(v_segments.is_required(j)) THEN
605 if( v_segments.default_type(j) = 'S') then
606 open default_value_csr for v_segments.default_value(j);
607 l_count := 0;
608 LOOP
609 if( x_attributes_default(v_rec_index).COLUMN_TYPE = 'DATE') THEN
610 FETCH default_value_Csr into l_default_date_value;
611 x_attributes_default(v_rec_index).COLUMN_VALUE :=
612 fnd_date.date_to_canonical(l_default_date_value);
613 elsif ( x_attributes_default(v_rec_index).COLUMN_TYPE = 'NUMBER') then
614 FETCH default_value_csr into l_default_numeric_value;
615 x_attributes_default(v_rec_index).COLUMN_VALUE :=
616 to_char(l_default_numeric_value);
617 else
618 FETCH default_value_csr into l_default_char_value;
619 x_attributes_default(v_rec_index).COLUMN_VALUE := substr(l_default_char_value, 1, 150);
620 end if;
621 EXIT WHEN default_value_csr%NOTFOUND;
622 l_count := l_count + 1;
623 end loop;
624 if( l_count = 0 ) then
625 FND_MESSAGE.SET_NAME('FND', 'FLEX-DFLT NO SQL ROWS');
626 FND_MESSAGE.SET_TOKEN('SEGMENT_NAME', v_segments.segment_name(j));
627 FND_MESSAGE.SET_TOKEN('APPLICATION_SHORT_NAME', 'INV');
628 FND_MESSAGE.SET_TOKEN('FLEXFIELD_NAME', p_attributes_name);
629 FND_MESSAGE.SET_TOKEN('SQL_STRING', v_segments.default_value(j));
630 FND_MSG_PUB.ADD;
631 elsif( l_count > 1 ) then
632 FND_MESSAGE.SET_NAME('FND', 'FLEX-DFLT MULTIPLE SQL ROWS');
633 FND_MESSAGE.SET_TOKEN('SQLSTR', v_segments.default_value(j));
634 end if;
635 else
636 x_attributes_default(v_rec_index).COLUMN_VALUE := v_segments.default_value(j);
637 end if;
638 END IF;
639 x_attributes_default(v_rec_index).PROMPT := v_segments.row_prompt(j);
640 END IF;
641
642 /* Check if segment is required, default value can not be NULL */
643 IF (v_segments.is_required(j)) THEN
644 IF(x_attributes_default(v_rec_index).COLUMN_VALUE IS NULL) THEN
645 IF (l_debug = 1) THEN
646 inv_log_util.trace('error inv_lot_sel_default_required', 'LOTSERATTR');
647 END IF;
648 fnd_message.set_name('INV', 'INV_LOT_SEL_DEFAULT_REQUIRED');
649 fnd_message.set_token('ATTRNAME',p_attributes_name);
650 fnd_message.set_token('CONTEXTCODE', v_contexts.context_code(i));
651 fnd_message.set_token('SEGMENT', v_segments.application_column_name(j));
652 fnd_msg_pub.add;
653 END IF;
654 END IF;
655 END IF; -- segment is enabled
656 END LOOP segmentLoop1;
657 END IF; -- context is enabled
658 END LOOP contextLoop1;
659
660 x_attributes_default_count := v_rec_index;
661 x_return_status := fnd_api.g_ret_sts_success ;
662
663 --inv_debug.message('x_return_status is ' || x_return_status);
664 ELSE /* no segment is enabled */
665 x_attributes_default_count := 0;
666 x_return_status := fnd_api.g_ret_sts_success ;
667 END IF; /* v_enabled */
668
669 EXCEPTION
670 WHEN fnd_api.g_exc_error THEN
671 x_return_status := fnd_api.g_ret_sts_error ;
672
673 WHEN fnd_api.g_exc_unexpected_error THEN
674 x_return_status := fnd_api.g_ret_sts_unexp_error ;
675
676 WHEN others THEN
677 x_return_status := fnd_api.g_ret_sts_unexp_error ;
678 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)) THEN
679 fnd_msg_pub.add_exc_msg(g_pkg_name, c_api_name);
680 END IF;
681 END get_default;
682
683 /* Returns the delimiter for the given dff
684 If flexfield is not found, then returns -1' */
685 FUNCTION get_delimiter(p_flex_name IN VARCHAR2,
686 p_application_short_name IN VARCHAR2) RETURN VARCHAR2 IS
687 v_flexfield fnd_dflex.dflex_r;
688 v_flexinfo fnd_dflex.dflex_dr;
689
690 BEGIN
691
692 -- Get flexfield
693 fnd_dflex.get_flexfield(p_application_short_name, p_flex_name, v_flexfield, v_flexinfo);
694 return(v_flexinfo.segment_delimeter);
695 EXCEPTION
696 WHEN OTHERS THEN
697 RETURN('-1');
698 END get_delimiter;
699
700
701
702 /* 2949575 */
703 /* Checks whether the DFF has a required Context or Global segment value */
704 /* Returns 1 if its required, 0 otherwise */
705 FUNCTION is_dff_required(p_flex_name IN VARCHAR2,
706 p_application_short_name IN VARCHAR2,
707 p_organization_id IN NUMBER,
708 p_inventory_item_id IN NUMBER) RETURN NUMBER IS
709
710 v_dflex_context_flag VARCHAR2(10);
711 v_flexfield fnd_dflex.dflex_r;
712 v_flexinfo fnd_dflex.dflex_dr;
713 v_contexts fnd_dflex.contexts_dr;
714 v_segments fnd_dflex.segments_dr;
715 v_attributes_category VARCHAR2(50) :=NULL;
716 i BINARY_INTEGER;
717 j BINARY_INTEGER;
718
719 BEGIN
720
721
722 SELECT df.context_required_flag
723 INTO v_dflex_context_flag
724 FROM fnd_application_vl a, fnd_descriptive_flexs_vl df
725 WHERE a.application_short_name = p_application_short_name
726 AND df.application_id = a.application_id
727 AND df.descriptive_flexfield_name = p_flex_name
728 AND a.application_id = df.table_application_id;
729
730 /* Check if it has a required context */
731 IF(v_dflex_context_flag = 'Y') THEN
732 /* Check if the context has a default value and whether the Default Value has any
733 Required segments, or the Global segments are Required */
734 IF(is_enabled(p_flex_name,p_organization_id,p_inventory_item_id) >= 2) THEN
735 -- It has required segments
736 RETURN(1);
737 else
738 RETURN(0);
739 end if;
740 else
741 --return (0); -- Bug:3839336: Commented this line and added the following to complete the fix done in Bug 3802523
742 --3 means that there are required and enabled segments in global context.
743 IF(is_enabled(p_flex_name,p_organization_id,p_inventory_item_id) = 3) THEN
744 return (1);
745 ELSE
746 return (0);
747 END IF;
748
749 end if;
750
751 EXCEPTION
752 WHEN OTHERS
753 THEN
754 RETURN(0);
755 END is_dff_required;
756 /*End of 2949575 */
757
758 /* Bug# 3418790
759 Code that returns 1 if the Context_User_Override_Flag is Y, 0 otherwise */
760 FUNCTION is_context_displayed(p_flex_name IN VARCHAR2,
761 p_application_short_name IN VARCHAR2) RETURN NUMBER IS
762 l_context_override_flag VARCHAR2(10);
763 BEGIN
764
765 SELECT df.context_user_override_flag
766 INTO l_context_override_flag
767 FROM fnd_application_vl a, fnd_descriptive_flexs_vl df
768 WHERE a.application_short_name = p_application_short_name
769 AND df.application_id = a.application_id
770 AND df.descriptive_flexfield_name = p_flex_name
771 AND a.application_id = df.table_application_id;
772
773 IF(l_context_override_flag = 'Y') THEN
774 RETURN(1);
775 ELSE
776 RETURN(0);
777 END IF;
778
779 EXCEPTION
780 WHEN OTHERS THEN
781 RETURN(0);
782 END is_context_displayed;
783 /* End of 3418790 */
784
785
786 /*-------------------------------------------------
787 * Check whether a descriptive flexfield has enabled(required)
788 segements. The return value can be:
789 0 - no enabled segments
790 1 - has enabled segments but are not required
791 2 - had enabled and required segments
792 -------------------------------------------------*/
793 FUNCTION is_enabled(p_flex_name IN VARCHAR2,
794 p_organization_id IN NUMBER,
795 p_inventory_item_id IN NUMBER) RETURN NUMBER IS
796
797 c_api_name CONSTANT VARCHAR2(30) := 'is_enabled';
798 v_flexfield fnd_dflex.dflex_r;
799 v_flexinfo fnd_dflex.dflex_dr;
800 v_contexts fnd_dflex.contexts_dr;
801 v_segments fnd_dflex.segments_dr;
802
803 v_attributes_category VARCHAR2(50) :=NULL;
804 i BINARY_INTEGER;
805 j BINARY_INTEGER;
806
807 v_is_enabled NUMBER := 0;
808 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
809 BEGIN
810
811 v_is_enabled :=0;
812
813 -- Get flexfield
814 fnd_dflex.get_flexfield('INV', p_flex_name, v_flexfield, v_flexinfo);
815
816 -- Get Contexts
817 fnd_dflex.get_contexts(v_flexfield, v_contexts);
818
819 -- Get attributes category
820 get_context_code(
821 context_value => v_attributes_category
822 , org_id => p_organization_id
823 , item_id => p_inventory_item_id
824 , flex_name => p_flex_name
825 , p_lot_serial_number => null);
826
827 <<contextLoop>>
828 FOR i IN 1..v_contexts.ncontexts LOOP
829 IF(v_contexts.is_enabled(i) AND
830 ((UPPER(v_contexts.context_code(i)) = UPPER(v_attributes_category)) OR
831 v_attributes_category IS NULL OR -- bug 11804383
832 v_contexts.is_global(i))
833 ) THEN
834
835 -- Get segments
836 fnd_dflex.get_segments(fnd_dflex.make_context(v_flexfield,
837 v_contexts.context_code(i)),
838 v_segments, TRUE);
839
840 <<segmentLoop>>
841 FOR j IN 1..v_segments.nsegments LOOP
842 IF v_segments.is_enabled(j) THEN
843 IF v_segments.is_required(j) THEN
844 -- Found enabled and required segment, return
845 v_is_enabled := 2;
846
847 --Return 3 if the context is global
848 IF v_contexts.is_global(i) THEN
849 v_is_enabled := 3;
850
851 -- Begin for bug 13493178
852 ELSE
853
854
855 IF (Upper(v_flexinfo.default_context_field) = '$PROFILES$.MFG_ORGANIZATION_ID')
856 AND (p_organization_id <> UPPER(v_contexts.context_code(i))) THEN
857 v_is_enabled := 1;
858
859 END IF;
860 -- End for bug 13493178
861
862 END IF;
863
864 IF v_is_enabled IN (2,3) THEN -- Added for bug 13493178
865 EXIT contextLoop;
866 END IF; -- Added for bug 13493178
867 ELSE
868 v_is_enabled := 1;
869 END IF;
870 END IF;
871 END LOOP segmentLoop;
872 END IF;
873 END LOOP contextLoop;
874 RETURN v_is_enabled;
875 EXCEPTION
876 WHEN others THEN
877 v_is_enabled :=0;
878 RETURN v_is_enabled;
879 END is_enabled;
880
881
882 FUNCTION is_enabled_segment(
883 p_flex_name IN VARCHAR2,
884 p_segment_name in VARCHAR2,
885 p_organization_id IN NUMBER,
886 p_inventory_item_id IN NUMBER) RETURN NUMBER IS
887
888 c_api_name CONSTANT VARCHAR2(30) := 'is_enabled_segment';
889 v_flexfield fnd_dflex.dflex_r;
890 v_flexinfo fnd_dflex.dflex_dr;
891 v_contexts fnd_dflex.contexts_dr;
892 v_segments fnd_dflex.segments_dr;
893
894 v_attributes_category VARCHAR2(50) :=NULL;
895 i BINARY_INTEGER;
896 j BINARY_INTEGER;
897
898 v_is_enabled NUMBER := 0;
899 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
900 BEGIN
901
902 v_is_enabled :=0;
903
904 -- Get flexfield
905 fnd_dflex.get_flexfield('INV', p_flex_name, v_flexfield, v_flexinfo);
906
907 -- Get Contexts
908 fnd_dflex.get_contexts(v_flexfield, v_contexts);
909
910 -- Get attributes category
911 INV_LOT_SEL_ATTR.get_context_code(
912 context_value => v_attributes_category
913 , org_id => p_organization_id
914 , item_id => p_inventory_item_id
915 , flex_name => p_flex_name
916 , p_lot_serial_number => null);
917
918 <<contextLoop>>
919 FOR i IN 1..v_contexts.ncontexts LOOP
920 IF(v_contexts.is_enabled(i) AND
921 ((UPPER(v_contexts.context_code(i)) = UPPER(v_attributes_category)) OR
922 v_contexts.is_global(i))
923 ) THEN
924
925 -- Get segments
926 fnd_dflex.get_segments(fnd_dflex.make_context(v_flexfield,
927 v_contexts.context_code(i)),
928 v_segments, TRUE);
929
930 <<segmentLoop>>
931 FOR j IN 1..v_segments.nsegments LOOP
932 --dbms_output.put_line(v_segments.segment_name(j));
933 IF upper(v_segments.segment_name(j)) = upper(p_segment_name) THEN
934 IF v_segments.is_enabled(j) THEN
935 IF v_segments.is_required(j) THEN
936 -- Found enabled and required segment, return
937 v_is_enabled := 2;
938 EXIT contextLoop;
939 ELSE
940 v_is_enabled := 1;
941 EXIT segmentLoop;
942 END IF;
943 END IF;
944 END IF;
945 END LOOP segmentLoop;
946 END IF;
947 END LOOP contextLoop;
948 RETURN v_is_enabled;
949 EXCEPTION
950 WHEN others THEN
951 v_is_enabled :=0;
952 RETURN v_is_enabled;
953 END is_enabled_segment;
954
955 /* Function that returns True in case the Lot Exists in MLN, False otherwise */
956 FUNCTION does_lot_exist(p_lot_number IN VARCHAR2, p_inventory_item_id IN NUMBER, p_org_id IN NUMBER) RETURN BOOLEAN IS
957 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
958 l_lot_number VARCHAR2(80);
959 BEGIN
960 SELECT lot_number
961 INTO l_lot_number
962 FROM mtl_lot_numbers
963 WHERE lot_number = p_lot_number
964 AND inventory_item_id = p_inventory_item_id
965 AND organization_id = p_org_id;
966 RETURN TRUE;
967 EXCEPTION
968 WHEN OTHERS THEN
969 RETURN FALSE;
970 END does_lot_exist;
971
972
973 /* Added extra IN parameter p_issue_receipt to determine if the txn
974 * is of type issue or receipt. For transaction_action_id = 12,
975 * a value of 'IR' is passed, since for intransit receipt, though
976 * it is a receipt txn, we need to display the serial attributes from
977 * the source organization --bug 2756040
978 */
979 /* Bug 4328865: Added default value '@@@' for p_issue_receipt parameter*/
980
981
982 PROCEDURE get_attribute_values
983 ( x_lot_serial_attributes OUT NOCOPY lot_sel_attributes_tbl_type
984 , x_lot_serial_attributes_count OUT NOCOPY NUMBER
985 , x_return_status OUT NOCOPY VARCHAR2
986 , x_msg_count OUT NOCOPY NUMBER
987 , x_msg_data OUT NOCOPY VARCHAR2
988 , p_table_name IN VARCHAR2
989 , p_attributes_name IN VARCHAR2
990 , p_inventory_item_id IN NUMBER
991 , p_organization_id IN NUMBER
992 , p_lot_serial_number IN VARCHAR2
993 , p_issue_receipt IN VARCHAR2 DEFAULT '@@@'
994 , p_where_called IN VARCHAR2 DEFAULT '@@@'
995 ) IS
996
997 l_sel_stmt VARCHAR2(32067):= 'SELECT ' ;
998 l_colnum NUMBER := 0;
999 l_sql_p INTEGER := NULL;
1000 l_rows_processed INTEGER := NULL;
1001 /* BUG 5334967 */
1002 l_precision NUMBER := 0;
1003 l_index NUMBER := 0;
1004
1005 l_flexfield fnd_dflex.dflex_r;
1006 l_flexinfo fnd_dflex.dflex_dr;
1007 l_contexts_info fnd_dflex.contexts_dr;
1008 l_contexts fnd_dflex.context_r;
1009 l_segments fnd_dflex.segments_dr;
1010 l_attributes_category VARCHAR2(50) :=NULL;
1011 l_global_code VARCHAR2(50);
1012 l_rec_index BINARY_INTEGER := 0;
1013
1014 l_count NUMBER;
1015 l_lot_serial_number VARCHAR2(240) := p_lot_serial_number;
1016 l_column_attributes INV_LOT_SEL_ATTR.LOT_SEL_ATTRIBUTES_TBL_TYPE;
1017 l_context_prompt fnd_descriptive_flexs_vl.form_context_prompt%TYPE; --bug 6636904
1018 l_context_column_name VARCHAR2(240); --bug 2474713
1019 l_module_name VARCHAR2(25) := 'GET_ATTRIBUTE_VALUES';
1020 l_debug_level NUMBER := 9;
1021 l_status VARCHAR2(1);
1022 l_industry VARCHAR2(1);
1023 l_oracle_schema VARCHAR2(30);
1024 l_column_idx BINARY_INTEGER;
1025 l_default_format VARCHAR2(30) := 'YYYY/MM/DD HH24:MI:SS';
1026 l_date DATE;
1027 l_get_default BOOLEAN DEFAULT FALSE; -- true if we are to get the default values for the attributes
1028 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1029 l_ret boolean;
1030
1031 cursor column_csr(p_table_name VARCHAR2, p_owner VARCHAR2) is
1032 /* bug 15933838 */
1033 /* select column_name, data_type, data_length
1034 from all_tab_columns
1035 where table_name = p_table_name
1036 and owner = p_owner
1037 order by column_id; */
1038
1039 select col.column_name, col.data_type, col.data_length
1040 from user_synonyms syn
1041 ,all_tab_columns col
1042 where syn.synonym_name = p_table_name
1043 and col.owner = p_owner
1044 and col.owner = syn.table_owner
1045 and col.table_name = syn.table_name
1046 order by col.column_id;
1047
1048 /* end of bug 15933838*/
1049
1050 /* Bug 9828930. Added below varialbes */
1051 l_appl_table_name VARCHAR2(240) ; /* Bug# 14299392 change the size to 240 matches the form field */
1052 l_value_column_name VARCHAR2(30);
1053 l_id_column_name VARCHAR2(30);
1054 l_final_value_char VARCHAR2(240) ;
1055 l_final_value_number number(20) ;
1056 l_final_value_date date ;
1057 l_no_data_found VARCHAR2(1);
1058 TYPE l_value_set IS TABLE OF NUMBER(10)INDEX BY BINARY_INTEGER ;
1059 l_value_set_id l_value_set ;
1060 /* End of Bug 9828930 */
1061
1062 l_additional_where_clause VARCHAR2(2000) ; --14828695
1063
1064 BEGIN
1065
1066 IF (l_debug = 1) THEN
1067 inv_trx_util_pub.trace('inputs to get_attribute_values: ','INV_LOT_SEL_ATTR',9);
1068 inv_trx_util_pub.trace('p_table_name ' || p_table_name,'INV_LOT_SEL_ATTR',9);
1069 inv_trx_util_pub.trace('p_attributes_name '|| p_attributes_name,'INV_LOT_SEL_ATTR',9);
1070 inv_trx_util_pub.trace('p_inventory_item_id '||p_inventory_item_id,'INV_LOT_SEL_ATTR',9);
1071 inv_trx_util_pub.trace('p_organization_id '||p_organization_id,'INV_LOT_SEL_ATTR',9);
1072 inv_trx_util_pub.trace('p_lot_serial_number '||p_lot_serial_number,'INV_LOT_SEL_ATTR',9);
1073 inv_trx_util_pub.trace('p_issue_receipt '||p_issue_receipt,'INV_LOT_SEL_ATTR',9);
1074 END IF;
1075 fnd_dflex.get_flexfield('INV', p_attributes_name, l_flexfield, l_flexinfo);
1076 -- Get Contexts
1077 fnd_dflex.get_contexts(l_flexfield, l_contexts_info);
1078 --Retrieve the context column name also --bug#2474713
1079 l_context_column_name := l_flexinfo.context_column_name;
1080 --Adding for bug 6636904
1081 l_context_prompt := l_flexinfo.form_context_prompt;
1082
1083 -- Commented entire block for bug 8315483
1084 /* Bug# 3418790
1085 Check whether we need to get the default attribtues or not based on the attribute name and
1086 transaction type */
1087 --IF (p_attributes_name = 'Serial Attributes') THEN
1088 -- /* Serial Attributes */
1089 -- IF(p_issue_receipt = 'R') THEN
1090 -- /* Serial Receipt...Get the default Attributes */
1091 -- l_get_default := TRUE;
1092 -- ELSIF (p_issue_receipt = 'I') THEN
1093 -- /* Serial Issue..Dont get the default values for the attribtues */
1094 -- l_get_default := FALSE;
1095 -- END IF;
1096 --ELSIF (p_attributes_name = 'Lot Attributes') THEN
1097 -- /* Lot Attributes */
1098 -- IF((p_issue_receipt = 'R') AND (does_lot_exist(p_lot_serial_number
1099 -- ,p_inventory_item_id
1100 -- ,p_organization_id) = FALSE)) THEN
1101 -- /* Lot attributes for Receipt and the Lot Number is new */
1102 -- l_get_default := TRUE;
1103 -- ELSE
1104 -- l_get_default := FALSE;
1105 -- END IF;
1106 --END IF;
1107 /* End of Bug# 3418790 */
1108
1109 /* BUG 2756040 Get attributes category from MSN if not Receipt txn (except Intransit
1110 * Receipt) and the attributes are Serial Attributes
1111 */
1112 IF( p_issue_receipt <> 'R'
1113 --AND p_attributes_name = 'Serial Attributes'
1114 ) THEN
1115 IF (l_debug = 1) THEN
1116 inv_trx_util_pub.trace('is not Receipt. calling the code to fetch from MSN or MLN depending on flex types');
1117 END IF;
1118 get_lot_serial_context(context_value => l_attributes_category
1119 , org_id => p_organization_id
1120 , item_id => p_inventory_item_id
1121 , p_lot_serial => p_lot_serial_number
1122 , flex_name => p_attributes_name);
1123 IF (l_debug = 1) THEN
1124 inv_trx_util_pub.trace('got the context value ' || l_attributes_category);
1125 END IF;
1126 ELSE
1127 get_context_code(
1128 context_value => l_attributes_category
1129 ,org_id => p_organization_id
1130 ,item_id => p_inventory_item_id
1131 ,flex_name => p_attributes_name
1132 ,p_lot_serial_number => null);
1133 END IF;
1134 IF (l_debug=1) THEN
1135 debug('No of Contexts ' ||l_contexts_info.ncontexts ,l_module_name,l_debug_level);
1136 END IF;
1137 FOR i IN 1..l_contexts_info.ncontexts
1138 LOOP
1139 IF(l_contexts_info.is_enabled(i) AND ((UPPER(l_contexts_info.context_code(i)) = UPPER(l_attributes_category)) OR
1140 l_contexts_info.is_global(i))) THEN
1141 /*bug #2474713 insert the context column.. This was added because the descriptive flexfield window
1142 expects the context_value also*/
1143 IF(NOT(l_contexts_info.is_global(i))) THEN
1144 l_rec_index := l_rec_index + 1;
1145 x_lot_serial_attributes(l_rec_index).COLUMN_NAME := l_context_column_name;
1146 IF NVL(p_where_called, '@@@') = 'INVMWBIV' THEN
1147 l_value_set_id(l_rec_index) := 0 ; -- Bug 9828930
1148 END IF ;
1149 get_column_type(
1150 p_table_name => p_table_name,
1151 p_column_name => x_lot_serial_attributes(l_rec_index).COLUMN_NAME,
1152 x_column_type => x_lot_serial_attributes(l_rec_index).COLUMN_TYPE);
1153 x_lot_serial_attributes(l_rec_index).REQUIRED := 'TRUE';
1154 --Adding for bug 6636904
1155 x_lot_serial_attributes(l_rec_index).PROMPT := l_context_prompt;
1156 END IF; /*bug #2474713 */
1157
1158 --Get segments
1159 l_contexts := fnd_dflex.make_context(l_flexfield, l_contexts_info.context_code(i));
1160 fnd_dflex.get_segments(l_contexts, l_segments, TRUE);
1161 ---dbms_output.put_line('number of segment is ' || l_segments.nsegments);
1162 IF (l_debug=1) THEN
1163 debug('No of Segments ' ||l_segments.nsegments ,l_module_name,l_debug_level);
1164 END IF;
1165
1166
1167 FOR j IN 1..l_segments.nsegments LOOP
1168 IF l_segments.is_enabled(j) THEN
1169 l_rec_index := l_rec_index +1 ;
1170 x_lot_serial_attributes(l_rec_index).COLUMN_NAME := l_segments.application_column_name(j);
1171 /* Bug 9828930 */
1172 IF NVL(p_where_called, '@@@') = 'INVMWBIV' THEN
1173 IF l_segments.value_set(j) IS NOT NULL THEN
1174 l_value_set_id(l_rec_index) := l_segments.value_set(j);
1175 ELSE
1176 l_value_set_id(l_rec_index) := 0 ;
1177 END IF ;
1178
1179 IF (l_debug=1) THEN
1180 debug('value set id is '||l_segments.value_set(j),l_module_name,l_debug_level);
1181 END IF;
1182 END IF ;
1183 /* End of Bug 9828930 */
1184 get_column_type( p_table_name => p_table_name,
1185 p_column_name => x_lot_serial_attributes(l_rec_index).COLUMN_NAME,
1186 x_column_type => x_lot_serial_attributes(l_rec_index).COLUMN_TYPE);
1187
1188 IF(l_segments.is_required(j)) THEN
1189 x_lot_serial_attributes(l_rec_index).REQUIRED := 'TRUE';
1190 ELSE
1191 x_lot_serial_attributes(l_rec_index).REQUIRED := 'FALSE';
1192 END IF;
1193
1194 /* Bug# 3418790
1195 Get the default values for the segments */
1196 IF (l_debug=1) THEN
1197 debug('Column Type ' ||x_lot_serial_attributes(l_rec_index).COLUMN_TYPE ,l_module_name,l_debug_level);
1198 debug('Column Name ' ||x_lot_serial_attributes(l_rec_index).COLUMN_NAME ,l_module_name,l_debug_level);
1199 END IF;
1200
1201 -- Bug 8315483
1202 --IF(l_get_default = TRUE) THEN
1203 IF l_segments.default_type(j) IS NOT NULL THEN
1204 IF(x_lot_serial_attributes(l_rec_index).COLUMN_TYPE = 'DATE') THEN
1205 IF (l_debug=1) THEN
1206 debug('Default Type is ' || l_segments.default_type(j),l_module_name,l_debug_level);
1207 END IF;
1208 IF(l_segments.default_type(j) = 'D') THEN
1209 /* Get the Value of the Current Date */
1210 SELECT SYSDATE
1211 INTO l_date
1212 FROM dual;
1213 ELSIF(l_segments.default_type(j) = 'C') THEN
1214 /* Constant Value is default */
1215 --l_date := to_date(l_segments.default_value(j),L_default_format);
1216 l_date := fnd_date.canonical_to_date(l_segments.default_value(j));
1217 END IF;
1218 x_lot_serial_attributes(l_rec_index).COLUMN_VALUE := fnd_date.date_to_displayDT(l_date);
1219 ELSE
1220 x_lot_serial_attributes(l_rec_index).COLUMN_VALUE := l_segments.default_value(j);
1221 END IF;
1222 END IF;
1223
1224 --Adding for bug 6636904
1225 x_lot_serial_attributes(l_rec_index).PROMPT := l_segments.row_prompt(j);
1226 END IF;
1227 END LOOP;
1228
1229 IF (l_debug=1) THEN
1230 debug('No of Records ' ||l_rec_index ,l_module_name,l_debug_level);
1231 FOR j IN 1..l_rec_index LOOP
1232 debug('Column Name ' || x_lot_serial_attributes(j).COLUMN_NAME,l_module_name,l_debug_level);
1233 debug('Column Value ' || x_lot_serial_attributes(j).COLUMN_VALUE,l_module_name,l_debug_level);
1234 debug('Column Type ' || x_lot_serial_attributes(j).COLUMN_TYPE,l_module_name,l_debug_level);
1235 END LOOP;
1236 END IF;
1237
1238 IF l_rec_index > 0 THEN
1239
1240 l_sel_stmt := 'SELECT ' ;
1241 l_column_idx := 0;
1242 l_ret := fnd_installation.get_app_info('INV', l_status, l_industry, l_oracle_schema);
1243
1244 OPEN column_csr(upper(p_table_name), l_oracle_schema);
1245 LOOP
1246
1247 l_column_idx := l_column_idx + 1;
1248
1249 FETCH column_csr INTO l_column_attributes(l_column_idx).column_name,
1250 l_column_attributes(l_column_idx).column_type,
1251 l_column_attributes(l_column_idx).column_length;
1252 EXIT WHEN column_csr%NOTFOUND;
1253 /*dbms_output.put_line (' column_csr: ' || l_column_attributes(l_column_idx).column_name||':'||
1254 l_column_attributes(l_column_idx).column_type);*/
1255 IF l_column_idx = 1 then
1256 l_sel_stmt := l_sel_stmt || l_column_attributes(l_column_idx).column_name ;
1257 --dbms_output.put_line (' l_sel_stmt'||l_sel_stmt);
1258 ELSE
1259 -- dbms_output.put_line (' l_endcol'||l_endcol);
1260 l_sel_stmt := l_sel_stmt || ', ' || l_column_attributes(l_column_idx).column_name ;
1261 END IF;
1262
1263 END LOOP;
1264 CLOSE column_csr;
1265
1266 IF p_table_name = 'MTL_LOT_NUMBERS' THEN
1267 l_sel_stmt := l_sel_stmt || ' ' ||
1268 'from mtl_lot_numbers ' ||
1269 'where lot_number = :b_lot_number ' ||
1270 'and inventory_item_id = :b_item_id ' ||
1271 'and organization_id = :b_org_id ' ;
1272 ELSIF p_table_name = 'MTL_SERIAL_NUMBERS' THEN
1273 l_sel_stmt := l_sel_stmt || ' ' ||
1274 'from mtl_serial_numbers ' ||
1275 'where serial_number = :b_serial_number ' ||
1276 'and inventory_item_id = :b_item_id ' ||
1277 'and CURRENT_organization_id = :b_org_id ' ;
1278 END IF;
1279
1280 l_sql_p := DBMS_SQL.OPEN_CURSOR;
1281
1282 DBMS_SQL.PARSE( l_sql_p, l_sel_stmt , DBMS_SQL.NATIVE );
1283
1284 DBMS_SQL.BIND_VARIABLE(l_sql_p, 'b_org_id', p_organization_id);
1285 DBMS_SQL.BIND_VARIABLE(l_sql_p, 'b_item_id', p_inventory_item_id);
1286
1287 IF p_table_name = 'MTL_LOT_NUMBERS' THEN
1288 DBMS_SQL.BIND_VARIABLE(l_sql_p, 'b_lot_number', p_lot_serial_number);
1289 ELSIF p_table_name = 'MTL_SERIAL_NUMBERS' THEN
1290 DBMS_SQL.BIND_VARIABLE(l_sql_p, 'b_serial_number', p_lot_serial_number);
1291 END IF;
1292
1293 l_colnum := 0;
1294 --dbms_output.put_line ('Count(): ' || l_column_attributes.count());
1295 --dbms_output.put_line ('Count: ' || l_column_attributes.count);
1296 FOR y in 1..l_column_idx - 1 --Bug#8232936
1297 LOOP
1298 l_colnum := l_colnum + 1;
1299
1300 IF UPPER(l_column_attributes(y).column_type) = ('DATE') THEN
1301 l_column_attributes(y).column_length := 10;
1302 ELSIF UPPER(l_column_attributes(y).column_type) = 'NUMBER' THEN
1303 l_column_attributes(y).column_length := 38;
1304 END IF;
1305 DBMS_SQL.DEFINE_COLUMN(l_sql_p, l_colnum, l_column_attributes(y).column_value,
1306 l_column_attributes(y).column_length);
1307 END LOOP;
1308
1309 l_rows_processed := DBMS_SQL.EXECUTE(l_sql_p);
1310 IF (l_debug=1) THEN
1311 debug('l_rows_processes = ' || l_rows_processed, l_module_name, l_debug_level);
1312 END IF;
1313
1314 --Unnecessary loop
1315 --LOOP
1316 IF (DBMS_SQL.FETCH_ROWS(l_sql_p) > 0 ) THEN
1317 l_colnum := 0;
1318 FOR y in 1..l_column_idx -1 --8232936
1319 LOOP
1320 l_colnum := l_colnum + 1;
1321 DBMS_SQL.COLUMN_VALUE(l_sql_p, l_colnum, l_column_attributes(y).column_value);
1322 END LOOP;
1323 ELSE
1324 IF (l_debug=1) THEN
1325 debug('in the else part of dbms_sql.fetch_rows ' , l_module_name, l_debug_level);
1326 END IF;
1327 --dbms_sql.close_cursor(l_sql_p);
1328 --EXIT;
1329 /* we do'nt care, if no record is found */
1330 END IF;
1331 --EXIT;
1332 --END LOOP;
1333
1334 dbms_sql.close_cursor(l_sql_p);
1335
1336 END IF; -- IF l_rec_index > 0 THEN
1337
1338 -- Bug 8315483 commented if l_get_default condition
1339 -- and added another condition in IF( upper(l_column_attributes(y)..
1340
1341 --IF(l_get_Default = FALSE) THEN
1342 FOR x in 1.. l_rec_index LOOP
1343 FOR y IN 1..l_column_idx-1 LOOP --8232936
1344 IF( upper(l_column_attributes(y).column_name) = upper(x_lot_serial_attributes(x).column_name)
1345 AND (x_lot_serial_attributes(x).column_value IS NULL
1346 OR l_column_attributes(y).column_value IS NOT NULL)) then
1347
1348 /* Bug 9828930 */
1349 IF NVL(p_where_called, '@@@') = 'INVMWBIV' THEN
1350 l_no_data_found :='Y';
1351 debug(' valueset = ' || l_value_set_id(x), l_module_name, l_debug_level);
1352 if l_value_set_id(x) <> 0 then
1353 debug('xlotserattr column_name = ' || x_lot_serial_attributes(x).column_name, l_module_name, l_debug_level);
1354 BEGIN
1355 l_no_data_found :='N';
1356 SELECT application_Table_name , value_column_name , id_column_name, additional_where_clause
1357 INTO l_appl_table_name , l_value_column_name , l_id_column_name, l_additional_where_clause --14828695 add l_additional_where_clause
1358 FROM fnd_flex_validation_Tables
1359 WHERE flex_value_set_id = l_value_set_id(x);
1360 EXCEPTION
1361 WHEN No_Data_Found THEN
1362 debug('no data found for Value set = ' || l_value_set_id(x), l_module_name, l_debug_level);
1363 l_no_data_found := 'Y' ;
1364
1365 WHEN others THEN /* Bug# 14299392, Add debug lines but we shouldn't come to here */
1366 debug('other exceptions for Value set = ' || l_value_set_id(x), l_module_name, l_debug_level);
1367 debug('SQLCODE:'||SQLCODE||' SQLERRM:'|| SQLERRM,l_module_name, l_debug_level);
1368 END ;
1369 debug('value set - after exception = ' || l_appl_table_name ||', ' ||l_value_column_name || ', '||l_id_column_name, l_module_name, l_debug_level);
1370 END IF ;
1371 END IF ;
1372 /* End of Bug 9828930 */
1373
1374 IF (l_debug=1) THEN
1375 debug('column_name = ' || l_column_attributes(y).column_name, l_module_name, l_debug_level);
1376 debug('column_TYPE = ' || x_lot_serial_attributes(x).column_type, l_module_name, l_debug_level);
1377 debug(x,l_module_name,l_debug_level);
1378 END IF;
1379 IF(x_lot_serial_attributes(x).column_type = 'DATE') THEN
1380 x_lot_serial_attributes(x).column_value := fnd_date.date_to_displayDT(l_column_attributes(y).column_value);
1381
1382 /* BUG 5334967 added the ELSIF condition for checking if the column type is NUMBER*/
1383 ELSIF (x_lot_serial_attributes(x).column_type = 'NUMBER') THEN
1384 IF (l_debug=1) THEN
1385 debug('in NUMBER', l_module_name, l_debug_level);
1386 END IF;
1387
1388 x_lot_serial_attributes(x).column_value := l_column_attributes(y).column_value;
1389
1390 l_index:=0;
1391
1392 FOR i in 1..l_segments.nsegments LOOP
1393 /* When you find that the application col name = sement column name, exit.
1394 The index thus obtained will be used for the value set in getting the precision */
1395 IF l_segments.application_column_name(i) = l_column_attributes(y).column_name THEN
1396 l_index := i;
1397 EXIT;
1398 END IF;
1399 END LOOP;
1400 --Bug 13065158. We need to access the value set only when the l_index>0.
1401 IF(l_index>0) THEN
1402 IF (l_debug=1) THEN
1403 debug('Index = ' || l_index, l_module_name, l_debug_level);
1404 debug('col index = ' || l_segments.value_set(l_index), l_module_name, l_debug_level);
1405 END IF;
1406 /*getting the precision for segment value of type Number*/
1407 SELECT number_precision INTO l_precision
1408 FROM FND_FLEX_VALUE_SETS
1409 WHERE flex_value_set_id = l_segments.value_set(l_index);
1410
1411 IF (l_debug=1) THEN
1412 debug('PRECISION = ' || l_precision, l_module_name, l_debug_level);
1413 END IF;
1414 IF (l_precision >0) THEN
1415 SELECT round(x_lot_serial_attributes(x).column_value, l_precision)
1416 INTO x_lot_serial_attributes(x).column_value
1417 FROM DUAL;
1418 END IF;
1419 IF (l_debug=1) THEN
1420 debug('column_value1 = ' || x_lot_serial_attributes(x).column_value, l_module_name, l_debug_level);
1421 END IF;
1422 END IF;
1423 /* End of changes for BUG 5334967 */
1424 ELSE
1425 x_lot_serial_attributes(x).column_value := l_column_attributes(y).column_value;
1426 IF (l_debug=1) THEN
1427 debug('In ELSE ' ,l_module_name, l_debug_level);
1428 END IF;
1429 END IF;
1430 --Bug 13399986 We need to compare only when l_id_column_name is not null .
1431 /* Bug 9828930 */
1432 IF NVL(p_where_called, '@@@') = 'INVMWBIV' THEN
1433 IF l_no_data_found = 'N' AND x_lot_serial_attributes(x).column_value IS NOT NULL and l_id_column_name is not null THEN
1434 debug('getting value from valueset id ' , l_module_name, l_debug_level);
1435 debug('idcolumnvalue, tablename, valuecolumn, id column -' ||x_lot_serial_attributes(x).COLUMN_VALUE||'..'|| l_appl_table_name ||', ' ||l_value_column_name || ', '||l_id_column_name, l_module_name, l_debug_level);
1436 BEGIN
1437
1438 IF (x_lot_serial_attributes(x).column_type = 'DATE') THEN
1439 EXECUTE IMMEDIATE 'SELECT distinct ' ||l_value_Column_name || ' FROM ' ||l_appl_table_name || ' WHERE '
1440 || l_id_column_name||' = ' || x_lot_serial_attributes(x).COLUMN_VALUE INTO l_final_value_date ;
1441 --ELSIF (x_lot_serial_attributes(x).column_type = 'NUMBER') THEN
1442 -- EXECUTE IMMEDIATE 'SELECT distinct ' ||l_value_Column_name || ' FROM ' ||l_appl_table_name || ' WHERE '
1443 -- || l_id_column_name||' = ' || x_lot_serial_attributes(x).COLUMN_VALUE INTO l_final_value_number ;
1444 ELSE
1445 if l_additional_where_clause is null then
1446 EXECUTE IMMEDIATE 'SELECT distinct ' ||l_value_Column_name || ' FROM ' ||l_appl_table_name || ' WHERE '
1447 || l_id_column_name||' = ' || x_lot_serial_attributes(x).COLUMN_VALUE INTO l_final_value_char ;
1448 ELSE --14828695
1449
1450
1451 EXECUTE IMMEDIATE 'SELECT distinct ' ||l_value_Column_name || ' FROM ' ||l_appl_table_name || ' WHERE '
1452 || l_id_column_name||' = ''' || x_lot_serial_attributes(x).COLUMN_VALUE || ''''
1453 || ' and ' || l_additional_where_clause
1454 INTO l_final_value_char ;
1455
1456
1457 END IF;
1458
1459 END IF;
1460
1461 EXCEPTION
1462 WHEN No_Data_Found THEN
1463 debug('value from valueset_id - nodatafound ' , l_module_name, l_debug_level);
1464 l_no_data_found := 'Y' ;
1465 WHEN OTHERS THEN
1466 debug('Others exception raised' , l_module_name, l_debug_level);
1467 l_no_data_found := 'Y' ;
1468 END ;
1469 IF l_no_data_found = 'N' THEN
1470 IF (x_lot_serial_attributes(x).column_type = 'DATE') THEN
1471 x_lot_serial_attributes(x).column_value := fnd_date.date_to_displayDT(l_final_value_date);
1472 -- ELSIF (x_lot_serial_attributes(x).column_type = 'NUMBER') THEN
1473 -- x_lot_serial_attributes(x).column_value := l_final_value_number;
1474 ELSE
1475 x_lot_serial_attributes(x).column_value := l_final_value_char;
1476 END IF;
1477 END IF;
1478 END IF ;
1479 END IF ;
1480 /* End of Bug 9828930 */
1481
1482 EXIT;
1483 END IF;
1484 END LOOP;
1485 END LOOP;
1486 --END IF; bug 8315483
1487 END IF;
1488 END LOOP;
1489 -- assign it to the out variable
1490 x_lot_serial_attributes_count := l_rec_index;
1491 x_return_status := fnd_api.g_ret_sts_success;
1492
1493 EXCEPTION
1494 WHEN fnd_api.g_exc_error THEN
1495 x_return_status := fnd_api.g_ret_sts_error ;
1496
1497 WHEN fnd_api.g_exc_unexpected_error THEN
1498 x_return_status := fnd_api.g_ret_sts_unexp_error;
1499
1500 WHEN others THEN
1501 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1502 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)) THEN
1503 fnd_msg_pub.add_exc_msg(g_pkg_name, 'GET_ATTRIBUTE_VALUES');
1504 END IF;
1505 END get_attribute_values;
1506
1507
1508 PROCEDURE get_dflex_context(
1509 x_context OUT NOCOPY t_genref,
1510 p_application_id IN NUMBER,
1511 p_flex_name IN VARCHAR2) IS
1512
1513 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1514 BEGIN
1515 OPEN x_context FOR
1516 SELECT descriptive_flex_context_code, descriptive_flex_context_name,
1517 global_flag, enabled_flag
1518 FROM fnd_descr_flex_contexts_vl
1519 WHERE application_id = p_application_id
1520 AND descriptive_flexfield_name = p_flex_name;
1521
1522 END get_dflex_context;
1523
1524 PROCEDURE get_dflex_segment(
1525 x_segment OUT NOCOPY t_genref,
1526 p_application_id IN NUMBER,
1527 p_flex_name IN VARCHAR2,
1528 p_flex_context_code IN VARCHAR2) IS
1529 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1530 BEGIN
1531 OPEN x_segment FOR
1532 SELECT end_user_column_name, application_column_name, enabled_flag,
1533 required_flag, default_type, default_value
1534 FROM fnd_descr_flex_col_usage_vl
1535 WHERE application_id = p_application_id
1536 AND descriptive_flexfield_name = p_flex_name
1537 AND descriptive_flex_context_code = p_flex_context_code;
1538
1539 END get_dflex_segment;
1540
1541 ---J Develop
1542 /* New Procedure to get the Inventory Attributes */
1543 procedure get_inv_lot_attributes( x_return_status OUT NOCOPY VARCHAR2
1544 ,x_msg_count OUT NOCOPY NUMBER
1545 ,x_msg_data OUT NOCOPY VARCHAR2
1546 ,x_inv_lot_attributes OUT NOCOPY inv_lot_sel_attr.lot_sel_attributes_tbl_type
1547 ,P_inventory_item_id IN NUMBER
1548 ,P_LOT_NUMBER IN VARCHAR2
1549 ,p_organization_id IN NUMBER
1550 ,p_attribute_category IN VARCHAR2
1551 )
1552
1553 IS
1554 TYPE inv_lot_attr IS TABLE OF VARCHAR2(1000) INDEX BY BINARY_INTEGER;
1555 l_inv_lot_attr inv_lot_attr;
1556
1557 CURSOR inv_attr IS
1558 SELECT attribute1
1559 ,attribute2
1560 ,attribute3
1561 ,attribute4
1562 ,attribute5
1563 ,attribute6
1564 ,attribute7
1565 ,attribute8
1566 ,attribute9
1567 ,attribute10
1568 ,attribute11
1569 ,attribute12
1570 ,attribute13
1571 ,attribute14
1572 ,attribute15
1573 FROM mtl_lot_numbers
1574 WHERE inventory_item_id = p_inventory_item_id
1575 AND organization_id = p_organization_id
1576 AND lot_number = p_lot_number;
1577
1578 l_inv_attr inv_attr%ROWTYPE;
1579 l_context VARCHAR2(1000);
1580 l_context_r fnd_dflex.context_r;
1581 l_contexts_dr fnd_dflex.contexts_dr;
1582 l_dflex_r fnd_dflex.dflex_r;
1583 l_segments_dr fnd_dflex.segments_dr;
1584 l_global_context BINARY_INTEGER;
1585 l_nsegments BINARY_INTEGER;
1586
1587 BEGIN
1588
1589 OPEN inv_attr;
1590 FETCH inv_attr INTO l_inv_attr;
1591
1592 /* Fill the lot data into l_inv_lot_attr table */
1593 l_inv_lot_attr(1) := l_inv_attr.attribute1;
1594 l_inv_lot_attr(2) := l_inv_attr.attribute2;
1595 l_inv_lot_attr(3) := l_inv_attr.attribute3;
1596 l_inv_lot_attr(4) := l_inv_attr.attribute4;
1597 l_inv_lot_attr(5) := l_inv_attr.attribute5;
1598 l_inv_lot_attr(6) := l_inv_attr.attribute6;
1599 l_inv_lot_attr(7) := l_inv_attr.attribute7;
1600 l_inv_lot_attr(8) := l_inv_attr.attribute8;
1601 l_inv_lot_attr(9) := l_inv_attr.attribute9;
1602 l_inv_lot_attr(10) := l_inv_attr.attribute10;
1603 l_inv_lot_attr(11) := l_inv_attr.attribute11;
1604 l_inv_lot_attr(12) := l_inv_attr.attribute12;
1605 l_inv_lot_attr(13) := l_inv_attr.attribute13;
1606 l_inv_lot_attr(14) := l_inv_attr.attribute14;
1607 l_inv_lot_attr(15) := l_inv_attr.attribute15;
1608 CLOSE inv_attr;
1609
1610 x_return_status := 'S';
1611 l_dflex_r.application_id := 401;
1612 l_dflex_r.flexfield_name := 'MTL_LOT_NUMBERS';
1613 fnd_dflex.get_contexts(flexfield => l_dflex_r, contexts => l_contexts_dr);
1614 l_global_context := l_contexts_dr.global_context;
1615 l_context := l_contexts_dr.context_code(l_global_context);
1616
1617 /* Prepare the context_r type for getting the segments associated with the global context */
1618 l_context_r.flexfield := l_dflex_r;
1619 l_context_r.context_code := l_context;
1620
1621 fnd_dflex.get_segments(CONTEXT => l_context_r, segments => l_segments_dr, enabled_only => TRUE);
1622
1623 /* read through the segments */
1624
1625 l_nsegments := l_segments_dr.nsegments;
1626 FOR j IN 1..l_nsegments LOOP
1627 x_inv_lot_attributes(substr(l_segments_dr.application_column_name(j),instr(l_segments_dr.application_column_name(j),'ATTRIBUTE')+9)).column_name :=l_segments_dr.application_column_name(j);
1628 x_inv_lot_attributes(substr(l_segments_dr.application_column_name(j),instr(l_segments_dr.application_column_name(j),'ATTRIBUTE')+9)).column_type :='VARCHAR2';
1629 IF l_segments_dr.is_required(j) THEN
1630 x_inv_lot_attributes(substr(l_segments_dr.application_column_name(j),instr(l_segments_dr.application_column_name(j),'ATTRIBUTE')+9)).required :='TRUE';
1631 ELSE
1632 x_inv_lot_attributes(substr(l_segments_dr.application_column_name(j),instr(l_segments_dr.application_column_name(j),'ATTRIBUTE')+9)).required :='FALSE';
1633 END IF;
1634 x_inv_lot_attributes(substr(l_segments_dr.application_column_name(j),instr(l_segments_dr.application_column_name(j),'ATTRIBUTE')+9)).column_length :=150;
1635 x_inv_lot_attributes(substr(l_segments_dr.application_column_name(j),instr(l_segments_dr.application_column_name(j),'ATTRIBUTE')+9)).column_value :=
1636 l_inv_lot_attr(substr(l_segments_dr.application_column_name(j),instr(l_segments_dr.application_column_name(j),'ATTRIBUTE')+9));
1637 END LOOP;
1638 l_context := NULL;
1639 l_nsegments := NULL;
1640
1641 IF p_attribute_category IS NOT NULL THEN
1642
1643 l_context := p_attribute_category;
1644
1645 /* Prepare the context_r type for getting the segments associated with the input context */
1646
1647 l_context_r.flexfield := l_dflex_r;
1648 l_context_r.context_code := l_context;
1649
1650 fnd_dflex.get_segments(CONTEXT => l_context_r, segments => l_segments_dr, enabled_only => TRUE);
1651 l_nsegments := l_segments_dr.nsegments;
1652 FOR i IN 1..l_nsegments LOOP
1653
1654 x_inv_lot_attributes(substr(l_segments_dr.application_column_name(i),instr(l_segments_dr.application_column_name(i),'ATTRIBUTE')+9)).column_name :=l_segments_dr.application_column_name(i);
1655 x_inv_lot_attributes(substr(l_segments_dr.application_column_name(i),instr(l_segments_dr.application_column_name(i),'ATTRIBUTE')+9)).column_type :='VARCHAR2';
1656 IF l_segments_dr.is_required(i) THEN
1657 x_inv_lot_attributes(substr(l_segments_dr.application_column_name(i),instr(l_segments_dr.application_column_name(i),'ATTRIBUTE')+9)).required :='TRUE';
1658 ELSE
1659 x_inv_lot_attributes(substr(l_segments_dr.application_column_name(i),instr(l_segments_dr.application_column_name(i),'ATTRIBUTE')+9)).required :='FALSE';
1660 END IF;
1661 x_inv_lot_attributes(substr(l_segments_dr.application_column_name(i),instr(l_segments_dr.application_column_name(i),'ATTRIBUTE')+9)).column_length :=150;
1662 x_inv_lot_attributes(substr(l_segments_dr.application_column_name(i),instr(l_segments_dr.application_column_name(i),'ATTRIBUTE')+9)).column_value :=
1663 l_inv_lot_attr(substr(l_segments_dr.application_column_name(i),instr(l_segments_dr.application_column_name(i),'ATTRIBUTE')+9));
1664 END LOOP;
1665 END IF;
1666 END get_inv_lot_attributes;
1667
1668 procedure get_inv_serial_attributes( x_return_status OUT NOCOPY VARCHAR2
1669 ,x_msg_count OUT NOCOPY NUMBER
1670 ,x_msg_data OUT NOCOPY VARCHAR2
1671 ,x_inv_serial_attributes OUT NOCOPY inv_lot_sel_attr.lot_sel_attributes_tbl_type
1672 ,x_concatenated_values OUT NOCOPY VARCHAR2
1673 ,P_inventory_item_id IN NUMBER
1674 ,P_SERIAL_NUMBER IN VARCHAR2
1675 ,p_attribute_category IN VARCHAR2
1676 ,p_transaction_temp_id IN NUMBER DEFAULT NULL
1677 ,p_transaction_source IN VARCHAR2 DEFAULT NULL
1678 )
1679
1680 IS
1681 TYPE inv_serial_attr IS TABLE OF VARCHAR2(150) INDEX BY BINARY_INTEGER;
1682 l_inv_serial_attr inv_serial_attr;
1683
1684 CURSOR inv_attr IS
1685 SELECT attribute1
1686 ,attribute2
1687 ,attribute3
1688 ,attribute4
1689 ,attribute5
1690 ,attribute6
1691 ,attribute7
1692 ,attribute8
1693 ,attribute9
1694 ,attribute10
1695 ,attribute11
1696 ,attribute12
1697 ,attribute13
1698 ,attribute14
1699 ,attribute15
1700 FROM mtl_SERIAL_numbers
1701 WHERE inventory_item_id = p_inventory_item_id
1702 AND SERIAL_number = p_serial_number;
1703 /*
1704 CURSOR inv_ship_attr IS
1705 SELECT nvl(msn.attribute1, msnt.attribute1) attribute1
1706 , nvl(msn.attribute2, msnt.attribute2) attribute2
1707 , nvl(msn.attribute3, msnt.attribute3) attribute3
1708 , nvl(msn.attribute4, msnt.attribute4) attribute4
1709 , nvl(msn.attribute5, msnt.attribute5) attribute5
1710 , nvl(msn.attribute6, msnt.attribute6) attribute6
1711 , nvl(msn.attribute7, msnt.attribute7) attribute7
1712 , nvl(msn.attribute8, msnt.attribute8) attribute8
1713 , nvl(msn.attribute9, msnt.attribute9) attribute9
1714 , nvl(msn.attribute10, msnt.attribute10) attribute10
1715 , nvl(msn.attribute11, msnt.attribute11) attribute11
1716 , nvl(msn.attribute12, msnt.attribute12) attribute12
1717 , nvl(msn.attribute13, msnt.attribute13) attribute13
1718 , nvl(msn.attribute14, msnt.attribute14) attribute14
1719 , nvl(msn.attribute15, msnt.attribute15) attribute15
1720 FROM mtl_serial_numbers msn, mtl_serial_numbers_temp msnt
1721 WHERE msn.inventory_item_id = p_inventory_item_id
1722 AND msn.serial_number = p_serial_number
1723 AND msnt.transaction_temp_id = p_transaction_temp_id
1724 AND msnt.fm_serial_number = p_serial_number
1725 AND msn.serial_number = msnt.fm_serial_number;
1726 */
1727 /* Commented the above cursor and the following cursor for Bug 3839336 */
1728 CURSOR inv_ship_attr IS
1729 SELECT msnt.attribute1 attribute1
1730 ,msnt.attribute2 attribute2
1731 ,msnt.attribute3 attribute3
1732 ,msnt.attribute4 attribute4
1733 ,msnt.attribute5 attribute5
1734 ,msnt.attribute6 attribute6
1735 ,msnt.attribute7 attribute7
1736 ,msnt.attribute8 attribute8
1737 ,msnt.attribute9 attribute9
1738 ,msnt.attribute10 attribute10
1739 ,msnt.attribute11 attribute11
1740 ,msnt.attribute12 attribute12
1741 ,msnt.attribute13 attribute13
1742 ,msnt.attribute14 attribute14
1743 ,msnt.attribute15 attribute15
1744 FROM mtl_serial_numbers msn, mtl_serial_numbers_temp msnt
1745 WHERE msn.inventory_item_id = p_inventory_item_id
1746 AND msn.serial_number = p_serial_number
1747 AND msnt.transaction_temp_id = p_transaction_temp_id
1748 AND msnt.fm_serial_number = p_serial_number
1749 AND msn.serial_number = msnt.fm_serial_number;
1750
1751
1752 l_inv_attr inv_attr%ROWTYPE;
1753 l_inv_ship_attr inv_ship_attr%ROWTYPE;
1754 l_context VARCHAR2(1000);
1755 l_context_r fnd_dflex.context_r;
1756 l_contexts_dr fnd_dflex.contexts_dr;
1757 l_dflex_r fnd_dflex.dflex_r;
1758 l_segments_dr fnd_dflex.segments_dr;
1759 l_global_context BINARY_INTEGER;
1760 l_nsegments BINARY_INTEGER;
1761
1762 BEGIN
1763
1764 debug('Inside get_inv_serial_attributes', 'GET_INV_SERIAL_ATTR', 10);
1765 debug('p_transaction_source is ' || p_transaction_source, 'GET_INV_SERIAL_ATTR', 10);
1766 debug('p_serial_number is ' || p_serial_number, 'GET_INV_SERIAL_ATTR', 10);
1767 debug('p_inventory_item_id is '|| p_inventory_item_id, 'GET_INV_SERIAL_ATTR', 10);
1768 debug('p_transaction_temp_id is ' || p_transaction_temp_id, 'GET_INV_SERIAL_ATTR', 10);
1769
1770 IF (nvl(p_transaction_source, 'INV') = 'WSH') THEN
1771 debug('OPEN inv_ship_attr', 'GET_INV_SERIAL_ATTR', 9);
1772 if( p_transaction_temp_id is null ) THEN
1773 BEGIN
1774 open inv_attr;
1775 FETCH inv_attr INTO l_inv_attr;
1776 EXCEPTION
1777 when no_data_found then
1778 l_inv_attr.attribute1 := null;
1779 l_inv_attr.attribute2 := null;
1780 l_inv_attr.attribute3 := null;
1781 l_inv_attr.attribute4 := null;
1782 l_inv_attr.attribute5 := null;
1783 l_inv_attr.attribute6 := null;
1784 l_inv_attr.attribute7 := null;
1785 l_inv_attr.attribute8 := null;
1786 l_inv_attr.attribute9 := null;
1787 l_inv_attr.attribute10 := null;
1788 l_inv_attr.attribute11 := null;
1789 l_inv_attr.attribute12 := null;
1790 l_inv_attr.attribute13 := null;
1791 l_inv_attr.attribute14 := null;
1792 l_inv_attr.attribute15 := null;
1793 END;
1794 else
1795 BEGIN
1796 OPEN inv_ship_attr;
1797 FETCH inv_ship_attr INTO l_inv_ship_attr;
1798 EXCEPTION
1799 WHEN no_data_found then
1800 l_inv_ship_attr.attribute1 := null;
1801 l_inv_ship_attr.attribute2 := null;
1802 l_inv_ship_attr.attribute3 := null;
1803 l_inv_ship_attr.attribute4 := null;
1804 l_inv_ship_attr.attribute5 := null;
1805 l_inv_ship_attr.attribute6 := null;
1806 l_inv_ship_attr.attribute7 := null;
1807 l_inv_ship_attr.attribute8 := null;
1808 l_inv_ship_attr.attribute9 := null;
1809 l_inv_ship_attr.attribute10 := null;
1810 l_inv_ship_attr.attribute11 := null;
1811 l_inv_ship_attr.attribute12 := null;
1812 l_inv_ship_attr.attribute13 := null;
1813 l_inv_ship_attr.attribute14 := null;
1814 l_inv_ship_attr.attribute15 := null;
1815 END;
1816 end if;
1817 ELSE
1818 --Bug #3765098 ( If txn temp id has value , the retrive the attr from msnt
1819 -- or get the attr from msn)
1820 if( p_transaction_temp_id is null ) THEN
1821 BEGIN
1822 OPEN inv_attr;
1823 FETCH inv_attr INTO l_inv_attr;
1824 EXCEPTION
1825 when no_data_found then
1826 l_inv_attr.attribute1 := null;
1827 l_inv_attr.attribute2 := null;
1828 l_inv_attr.attribute3 := null;
1829 l_inv_attr.attribute4 := null;
1830 l_inv_attr.attribute5 := null;
1831 l_inv_attr.attribute6 := null;
1832 l_inv_attr.attribute7 := null;
1833 l_inv_attr.attribute8 := null;
1834 l_inv_attr.attribute9 := null;
1835 l_inv_attr.attribute10 := null;
1836 l_inv_attr.attribute11 := null;
1837 l_inv_attr.attribute12 := null;
1838 l_inv_attr.attribute13 := null;
1839 l_inv_attr.attribute14 := null;
1840 l_inv_attr.attribute15 := null;
1841 end;
1842 else
1843 BEGIN
1844 OPEN inv_ship_attr;
1845 FETCH inv_ship_attr INTO l_inv_ship_attr;
1846 EXCEPTION
1847 WHEN no_data_found then
1848 l_inv_ship_attr.attribute1 := null;
1849 l_inv_ship_attr.attribute2 := null;
1850 l_inv_ship_attr.attribute3 := null;
1851 l_inv_ship_attr.attribute4 := null;
1852 l_inv_ship_attr.attribute5 := null;
1853 l_inv_ship_attr.attribute6 := null;
1854 l_inv_ship_attr.attribute7 := null;
1855 l_inv_ship_attr.attribute8 := null;
1856 l_inv_ship_attr.attribute9 := null;
1857 l_inv_ship_attr.attribute10 := null;
1858 l_inv_ship_attr.attribute11 := null;
1859 l_inv_ship_attr.attribute12 := null;
1860 l_inv_ship_attr.attribute13 := null;
1861 l_inv_ship_attr.attribute14 := null;
1862 l_inv_ship_attr.attribute15 := null;
1863 END;
1864 end if;
1865 END IF;
1866
1867 IF (nvl(p_transaction_source, 'INV') = 'WSH') THEN
1868 if( p_transaction_temp_id is not null ) then
1869 l_inv_serial_attr(1) := l_inv_ship_attr.attribute1;
1870 l_inv_serial_attr(2) := l_inv_ship_attr.attribute2;
1871 l_inv_serial_attr(3) := l_inv_ship_attr.attribute3;
1872 l_inv_serial_attr(4) := l_inv_ship_attr.attribute4;
1873 l_inv_serial_attr(5) := l_inv_ship_attr.attribute5;
1874 l_inv_serial_attr(6) := l_inv_ship_attr.attribute6;
1875 l_inv_serial_attr(7) := l_inv_ship_attr.attribute7;
1876 l_inv_serial_attr(8) := l_inv_ship_attr.attribute8;
1877 l_inv_serial_attr(9) := l_inv_ship_attr.attribute9;
1878 l_inv_serial_attr(10) := l_inv_ship_attr.attribute10;
1879 l_inv_serial_attr(11) := l_inv_ship_attr.attribute11;
1880 l_inv_serial_attr(12) := l_inv_ship_attr.attribute12;
1881 l_inv_serial_attr(13) := l_inv_ship_attr.attribute13;
1882 l_inv_serial_attr(14) := l_inv_ship_attr.attribute14;
1883 l_inv_serial_attr(15) := l_inv_ship_attr.attribute15;
1884 CLOSE inv_ship_attr;
1885 else
1886 l_inv_serial_attr(1) := l_inv_attr.attribute1;
1887 l_inv_serial_attr(2) := l_inv_attr.attribute2;
1888 l_inv_serial_attr(3) := l_inv_attr.attribute3;
1889 l_inv_serial_attr(4) := l_inv_attr.attribute4;
1890 l_inv_serial_attr(5) := l_inv_attr.attribute5;
1891 l_inv_serial_attr(6) := l_inv_attr.attribute6;
1892 l_inv_serial_attr(7) := l_inv_attr.attribute7;
1893 l_inv_serial_attr(8) := l_inv_attr.attribute8;
1894 l_inv_serial_attr(9) := l_inv_attr.attribute9;
1895 l_inv_serial_attr(10) := l_inv_attr.attribute10;
1896 l_inv_serial_attr(11) := l_inv_attr.attribute11;
1897 l_inv_serial_attr(12) := l_inv_attr.attribute12;
1898 l_inv_serial_attr(13) := l_inv_attr.attribute13;
1899 l_inv_serial_attr(14) := l_inv_attr.attribute14;
1900 l_inv_serial_attr(15) := l_inv_attr.attribute15;
1901 CLOSE inv_attr;
1902 end if;
1903 ELSE
1904 --Bug #3765098 ( If txn temp id has value , the retrive the attr from msnt
1905 -- or get the attr from msn)
1906 if( p_transaction_temp_id is not null ) then
1907 l_inv_serial_attr(1) := l_inv_ship_attr.attribute1;
1908 l_inv_serial_attr(2) := l_inv_ship_attr.attribute2;
1909 l_inv_serial_attr(3) := l_inv_ship_attr.attribute3;
1910 l_inv_serial_attr(4) := l_inv_ship_attr.attribute4;
1911 l_inv_serial_attr(5) := l_inv_ship_attr.attribute5;
1912 l_inv_serial_attr(6) := l_inv_ship_attr.attribute6;
1913 l_inv_serial_attr(7) := l_inv_ship_attr.attribute7;
1914 l_inv_serial_attr(8) := l_inv_ship_attr.attribute8;
1915 l_inv_serial_attr(9) := l_inv_ship_attr.attribute9;
1916 l_inv_serial_attr(10) := l_inv_ship_attr.attribute10;
1917 l_inv_serial_attr(11) := l_inv_ship_attr.attribute11;
1918 l_inv_serial_attr(12) := l_inv_ship_attr.attribute12;
1919 l_inv_serial_attr(13) := l_inv_ship_attr.attribute13;
1920 l_inv_serial_attr(14) := l_inv_ship_attr.attribute14;
1921 l_inv_serial_attr(15) := l_inv_ship_attr.attribute15;
1922 CLOSE inv_ship_attr;
1923 else
1924 l_inv_serial_attr(1) := l_inv_attr.attribute1;
1925 l_inv_serial_attr(2) := l_inv_attr.attribute2;
1926 l_inv_serial_attr(3) := l_inv_attr.attribute3;
1927 l_inv_serial_attr(4) := l_inv_attr.attribute4;
1928 l_inv_serial_attr(5) := l_inv_attr.attribute5;
1929 l_inv_serial_attr(6) := l_inv_attr.attribute6;
1930 l_inv_serial_attr(7) := l_inv_attr.attribute7;
1931 l_inv_serial_attr(8) := l_inv_attr.attribute8;
1932 l_inv_serial_attr(9) := l_inv_attr.attribute9;
1933 l_inv_serial_attr(10) := l_inv_attr.attribute10;
1934 l_inv_serial_attr(11) := l_inv_attr.attribute11;
1935 l_inv_serial_attr(12) := l_inv_attr.attribute12;
1936 l_inv_serial_attr(13) := l_inv_attr.attribute13;
1937 l_inv_serial_attr(14) := l_inv_attr.attribute14;
1938 l_inv_serial_attr(15) := l_inv_attr.attribute15;
1939 CLOSE inv_attr;
1940 end if;
1941 END IF;
1942
1943 x_return_status := 'S';
1944 l_dflex_r.application_id := 401;
1945 l_dflex_r.flexfield_name := 'MTL_SERIAL_NUMBERS';
1946 fnd_dflex.get_contexts(flexfield => l_dflex_r, contexts => l_contexts_dr);
1947 l_global_context := l_contexts_dr.global_context;
1948 l_context := l_contexts_dr.context_code(l_global_context);
1949
1950 /* Prepare the context_r type for getting the segments associated with the global context */
1951 l_context_r.flexfield := l_dflex_r;
1952 l_context_r.context_code := l_context;
1953
1954 fnd_dflex.get_segments(CONTEXT => l_context_r, segments => l_segments_dr, enabled_only => TRUE);
1955 debug('after calling fnd_dflex.get_segments', 'GET_INV_SERIAL_ATTR', 9);
1956
1957 /* read through the segments */
1958
1959 l_nsegments := l_segments_dr.nsegments;
1960 FOR j IN 1..l_nsegments LOOP
1961 debug('j = ' || j, 'GET_INV_SERIAL_ATTR', 9);
1962 debug('column application name is ' || l_segments_dr.application_column_name(j), 'GET_INV_SERIAL_ATTR', 9);
1963 debug(substr(l_segments_dr.application_column_name(j), instr(l_segments_dr.application_column_name(j), 'ATTRIBUTE')+9), 'GET_INV_SERIAL_ATTR', 9);
1964
1965 x_inv_serial_attributes(
1966 substr(l_segments_dr.application_column_name(j),
1967 instr(l_segments_dr.application_column_name(j),'ATTRIBUTE')+9)).column_name :=
1968 l_segments_dr.application_column_name(j);
1969 x_inv_serial_attributes(substr(l_segments_dr.application_column_name(j),
1970 instr(l_segments_dr.application_column_name(j),'ATTRIBUTE')+9)).column_type :='VARCHAR2';
1971 IF l_segments_dr.is_required(j) THEN
1972 x_inv_serial_attributes(substr(l_segments_dr.application_column_name(j),
1973 instr(l_segments_dr.application_column_name(j),'ATTRIBUTE')+9)).required :='TRUE';
1974 ELSE
1975 x_inv_serial_attributes(substr(l_segments_dr.application_column_name(j),
1976 instr(l_segments_dr.application_column_name(j),'ATTRIBUTE')+9)).required :='FALSE';
1977 END IF;
1978 x_inv_serial_attributes(substr(l_segments_dr.application_column_name(j),
1979 instr(l_segments_dr.application_column_name(j),'ATTRIBUTE')+9)).column_length :=150;
1980 x_inv_serial_attributes(substr(l_segments_dr.application_column_name(j),
1981 instr(l_segments_dr.application_column_name(j),'ATTRIBUTE')+9)).column_value :=
1982 l_inv_serial_attr(substr(l_segments_dr.application_column_name(j),
1983 instr(l_segments_dr.application_column_name(j),'ATTRIBUTE')+9));
1984 fnd_flex_descval.set_column_value(l_segments_dr.application_column_name(j),
1985 l_inv_serial_attr(substr(l_segments_dr.application_column_name(j),
1986 instr(l_segments_dr.application_column_name(j), 'ATTRIBUTE')+9)));
1987 END LOOP;
1988 l_context := NULL;
1989 l_nsegments := NULL;
1990
1991 IF p_attribute_category IS NOT NULL THEN
1992 debug('getting context specific segments', 'GET_INV_SERIAL_ATTR', 9);
1993 l_context := p_attribute_category;
1994 debug('setting context value ' || l_context, 'GET_INV_SERIAL_ATTR', 9);
1995 fnd_flex_descval.set_context_value(l_context);
1996
1997 /* Prepare the context_r type for getting the segments associated with the input context */
1998
1999 l_context_r.flexfield := l_dflex_r;
2000 l_context_r.context_code := l_context;
2001
2002 fnd_dflex.get_segments(CONTEXT => l_context_r, segments => l_segments_dr, enabled_only => TRUE);
2003 l_nsegments := l_segments_dr.nsegments;
2004 FOR i IN 1..l_nsegments LOOP
2005 debug('application column name is ' || l_segments_dr.application_column_name(i), 'GET_INV_SERIAL_ATTR', 9);
2006 x_inv_serial_attributes(substr(l_segments_dr.application_column_name(i),
2007 instr(l_segments_dr.application_column_name(i),'ATTRIBUTE')+9)).column_name :=
2008 l_segments_dr.application_column_name(i);
2009 x_inv_serial_attributes(substr(l_segments_dr.application_column_name(i),
2010 instr(l_segments_dr.application_column_name(i),'ATTRIBUTE')+9)).column_type :='VARCHAR2';
2011 IF l_segments_dr.is_required(i) THEN
2012 x_inv_serial_attributes(substr(l_segments_dr.application_column_name(i),
2013 instr(l_segments_dr.application_column_name(i),'ATTRIBUTE')+9)).required :='TRUE';
2014 ELSE
2015 x_inv_serial_attributes(substr(l_segments_dr.application_column_name(i),
2016 instr(l_segments_dr.application_column_name(i),'ATTRIBUTE')+9)).required :='FALSE';
2017 END IF;
2018 x_inv_serial_attributes(substr(l_segments_dr.application_column_name(i),
2019 instr(l_segments_dr.application_column_name(i),'ATTRIBUTE')+9)).column_length :=150;
2020 x_inv_serial_attributes(substr(l_segments_dr.application_column_name(i),
2021 instr(l_segments_dr.application_column_name(i),'ATTRIBUTE')+9)).column_value :=
2022 l_inv_serial_attr(substr(l_segments_dr.application_column_name(i),
2023 instr(l_segments_dr.application_column_name(i),'ATTRIBUTE')+9));
2024 fnd_flex_descval.set_column_value(l_segments_dr.application_column_name(i),
2025 l_inv_serial_attr(substr(l_segments_dr.application_column_name(i),
2026 instr(l_segments_dr.application_column_name(i), 'ATTRIBUTE')+9)));
2027 END LOOP;
2028 END IF;
2029
2030 IF ( p_attribute_Category IS NULL ) then
2031 l_context := l_contexts_dr.context_code(l_global_context);
2032 fnd_flex_descval.set_context_value(l_context);
2033 end if;
2034 debug('calling fnd_flex_descval.concatenated_values', 'GET_INV_SERIAL_ATTR', 9);
2035 IF fnd_flex_descval.validate_desccols(appl_short_name => 'INV',
2036 desc_flex_name => 'MTL_SERIAL_NUMBERS', values_or_ids => 'I' , validation_date => SYSDATE) THEN
2037 x_concatenated_values := fnd_flex_descval.concatenated_values;
2038 ELSE
2039
2040 x_concatenated_values := null;
2041 FND_MESSAGE.SET_NAME('INV', 'INV_FND_GENERIC_MSG');
2042 FND_MESSAGE.SET_TOKEN('MSG', fnd_flex_descval.error_message);
2043 FND_MSG_PUB.ADD;
2044 raise fnd_api.g_exc_unexpected_error;
2045 END IF;
2046 debug('after getting x_concatenated_values ' || x_concatenated_values, 'GET_INV_SERIAL_ATTR', 9);
2047 EXCEPTION
2048 when no_data_found THEN
2049 null;
2050 when FND_API.G_EXC_UNEXPECTED_ERROR THEN
2051 x_return_status := 'U';
2052 fnd_msg_pub.count_and_get(p_encoded => FND_API.G_FALSE, p_count => x_msg_count, p_data => x_msg_data);
2053 when others then
2054 x_return_status := 'U';
2055 fnd_msg_pub.count_and_get(p_encoded => FND_API.G_FALSE, p_count => x_msg_count, p_data => x_msg_data);
2056 END get_inv_serial_attributes;
2057
2058 -- Bug 7350762 Code changes start
2059
2060 FUNCTION is_lot_attributes_required( p_flex_name IN VARCHAR2,
2061 p_organization_id IN NUMBER,
2062 p_inventory_item_id IN NUMBER,
2063 p_lot_number IN VARCHAR2) RETURN BOOLEAN IS
2064
2065 c_api_name CONSTANT VARCHAR2(30) := 'is_lot_attributes_required';
2066 v_flexfield fnd_dflex.dflex_r;
2067 v_flexinfo fnd_dflex.dflex_dr;
2068 v_contexts fnd_dflex.contexts_dr;
2069 v_segments fnd_dflex.segments_dr;
2070 v_attributes_category VARCHAR2(50) := NULL;
2071
2072 i BINARY_INTEGER;
2073 j BINARY_INTEGER;
2074 l_cursor_handle NUMBER;
2075 l_rows_affected NUMBER;
2076 l_lot_att_required BOOLEAN := FALSE;
2077 l_where_clause VARCHAR2(2000) := NULL;
2078 l_lot_exist NUMBER := 0;
2079 l_context_req_flag VARCHAR2(1) := 'N';
2080 l_attribute_category VARCHAR2(255) := NULL;
2081
2082 BEGIN
2083
2084 l_lot_exist := 0;
2085
2086 IF (UPPER(p_flex_name) = 'MTL_LOT_NUMBERS') THEN
2087
2088 BEGIN
2089 SELECT 1
2090 , attribute_category
2091 INTO l_lot_exist
2092 , l_attribute_category
2093 FROM mtl_lot_numbers
2094 WHERE organization_id = p_organization_id
2095 AND inventory_item_id = p_inventory_item_id
2096 AND lot_number = p_lot_number;
2097 EXCEPTION
2098 WHEN no_data_found THEN
2099 l_lot_exist := 0;
2100 END;
2101
2102 ELSIF (UPPER(p_flex_name) = 'LOT ATTRIBUTES') THEN
2103
2104 BEGIN
2105 SELECT 1
2106 , lot_attribute_category
2107 INTO l_lot_exist
2108 , l_attribute_category
2109 FROM mtl_lot_numbers
2110 WHERE organization_id = p_organization_id
2111 AND inventory_item_id = p_inventory_item_id
2112 AND lot_number = p_lot_number;
2113 EXCEPTION
2114 WHEN no_data_found THEN
2115 l_lot_exist := 0;
2116 END;
2117
2118 END IF;
2119
2120 BEGIN
2121 SELECT df.context_required_flag
2122 INTO l_context_req_flag
2123 FROM fnd_application_vl a, fnd_descriptive_flexs_vl df
2124 WHERE a.application_short_name = 'INV'
2125 AND df.application_id = a.application_id
2126 AND df.descriptive_flexfield_name = p_flex_name
2127 AND a.application_id = df.table_application_id;
2128 EXCEPTION
2129 WHEN no_data_found THEN
2130 l_context_req_flag := 'N';
2131 END;
2132
2133
2134 IF ((l_lot_exist = 1 AND l_context_req_flag = 'Y' AND l_attribute_category IS NULL) OR
2135 (l_lot_exist = 0 AND l_context_req_flag = 'Y')) THEN
2136 l_lot_att_required := TRUE;
2137 ELSE
2138
2139 -- Get flexfield
2140 fnd_dflex.get_flexfield('INV', p_flex_name, v_flexfield, v_flexinfo);
2141
2142 -- Get Contexts
2143 fnd_dflex.get_contexts(v_flexfield, v_contexts);
2144
2145 -- Get attributes category
2146 get_context_code(
2147 context_value => v_attributes_category
2148 , org_id => p_organization_id
2149 , item_id => p_inventory_item_id
2150 , flex_name => p_flex_name
2151 , p_lot_serial_number => null);
2152
2153 <<contextLoop>>
2154 FOR i IN 1..v_contexts.ncontexts LOOP
2155 IF(v_contexts.is_enabled(i) AND
2156 ((UPPER(v_contexts.context_code(i)) = UPPER(v_attributes_category)) OR
2157 v_contexts.is_global(i))
2158 ) THEN
2159
2160 -- Get segments
2161 fnd_dflex.get_segments(fnd_dflex.make_context(v_flexfield,
2162 v_contexts.context_code(i)), v_segments, TRUE);
2163
2164 <<segmentLoop>>
2165 FOR j IN 1..v_segments.nsegments LOOP
2166 IF v_segments.is_enabled(j) THEN
2167 IF v_segments.is_required(j) THEN
2168 IF ( l_lot_exist = 1) THEN
2169 l_where_clause := l_where_clause || ' AND ' ||
2170 v_segments.application_column_name(j) || ' IS NOT NULL ';
2171 ELSE
2172 l_lot_att_required := TRUE;
2173 EXIT contextLoop;
2174 END IF;
2175 END IF;
2176 END IF;
2177 END LOOP segmentLoop;
2178 END IF;
2179 END LOOP contextLoop;
2180
2181 IF l_where_clause IS NOT NULL THEN
2182
2183 l_where_clause := 'SELECT 1 FROM mtl_lot_numbers ' ||
2184 ' WHERE organization_id = :org_id ' ||
2185 ' AND inventory_item_id = :item_id ' ||
2186 ' AND lot_number = :lot ' || l_where_clause;
2187
2188 l_cursor_handle := dbms_sql.open_cursor;
2189 dbms_sql.parse(l_cursor_handle, l_where_clause, dbms_sql.native);
2190 dbms_sql.bind_variable(l_cursor_handle, ':org_id', p_organization_id);
2191 dbms_sql.bind_variable(l_cursor_handle, ':item_id', p_inventory_item_id);
2192 dbms_sql.bind_variable(l_cursor_handle, ':lot', p_lot_number);
2193
2194 l_rows_affected := dbms_sql.execute(l_cursor_handle);
2195
2196 IF dbms_sql.fetch_rows(l_cursor_handle) <= 0 then
2197 l_lot_att_required := TRUE;
2198 END IF;
2199
2200 END IF;
2201
2202 END IF;
2203
2204 RETURN l_lot_att_required;
2205
2206 EXCEPTION
2207 WHEN others THEN
2208 l_lot_att_required := TRUE;
2209 debug('Unexpected exception : ' || SQLERRM, c_api_name, 0);
2210 RETURN l_lot_att_required;
2211
2212 END is_lot_attributes_required;
2213 -- Bug 7350762 Code changes end
2214
2215 /* Added following function for bug 8428348 */
2216
2217 FUNCTION lock_lot_records( p_org_id IN NUMBER
2218 , p_inventory_item_id IN NUMBER
2219 , p_lot_uniqueness IN NUMBER DEFAULT NULL
2220 , p_lot_generation IN NUMBER DEFAULT NULL
2221 , p_lot_prefix IN VARCHAR2
2222 , x_return_status OUT NOCOPY VARCHAR2
2223 ) RETURN BOOLEAN IS
2224
2225 l_module_name VARCHAR2(30) := 'lock_lot_records';
2226 l_already_locked VARCHAR2(1) := 'Y';
2227
2228 TYPE loc_exists IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
2229 loc_tab loc_exists;
2230
2231 BEGIN
2232
2233 SAVEPOINT lock_lot_records;
2234
2235 x_return_status := fnd_api.g_ret_sts_success;
2236
2237 debug('Lot Generation => '|| p_lot_generation ,l_module_name, 9);
2238 debug('Lot Uniqueness => '|| p_lot_uniqueness ,l_module_name, 9);
2239 debug('Lot Prefix => '|| p_lot_prefix ,l_module_name, 9);
2240
2241 IF (p_lot_generation = 2 AND p_lot_uniqueness = 1) THEN
2242
2243 SELECT 'N' BULK COLLECT
2244 INTO loc_tab
2245 FROM mtl_system_items_b
2246 WHERE auto_lot_alpha_prefix = p_lot_prefix
2247 AND lot_control_code = 2
2248 FOR UPDATE NOWAIT;
2249
2250 l_already_locked := 'N';
2251
2252 ELSIF(p_lot_generation = 2 AND p_lot_uniqueness <> 1) THEN
2253
2254 SELECT 'N'
2255 INTO l_already_locked
2256 FROM mtl_system_items_b
2257 WHERE organization_id = p_org_id
2258 AND inventory_item_id = p_inventory_item_id
2259 FOR UPDATE NOWAIT;
2260
2261 END IF;
2262
2263 IF l_already_locked = 'N' THEN /* Got the lock */
2264 RETURN TRUE;
2265 ELSE
2266 x_return_status := fnd_api.g_ret_sts_error;
2267 RETURN FALSE;
2268 END IF;
2269
2270 EXCEPTION
2271 WHEN OTHERS THEN /* could not lock row */
2272 ROLLBACK TO lock_lot_records;
2273 IF SQLCODE = -54 THEN
2274 -- item with same prefix / same item currently locked in MSI
2275 debug('Item with same prefix currently locked in Master or Organization items form',l_module_name,9);
2276 fnd_message.set_name('INV', 'INV_LOT_GEN_ERROR');
2277 fnd_msg_pub.ADD;
2278 END IF;
2279 x_return_status := fnd_api.g_ret_sts_error;
2280 RETURN FALSE;
2281 END lock_lot_records;
2282
2283 /* End of changes for bug 8428348 */
2284
2285
2286 END INV_LOT_SEL_ATTR;