[Home] [Help]
PACKAGE BODY: APPS.INV_LOT_SEL_ATTR
Source
1 PACKAGE BODY INV_LOT_SEL_ATTR AS
2 /* $Header: INVLSDFB.pls 120.8 2008/01/25 12:44:11 mporecha 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.8 2008/01/25 12:44:11 mporecha 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
437 l_context_column_name := v_flexinfo.context_column_name;
434 inv_log_util.trace('Get context ' , 'LOTSERATTR');
435 END IF;
436 /*bug 2474713 retrieve the context_column_name and the context_prompt */
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
560 if( x_attributes_default(v_rec_index).COLUMN_TYPE = 'DATE') THEN
557 open default_value_csr for v_segments.default_value(j);
558 l_count := 0;
559 LOOP
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 */
646 inv_log_util.trace('error inv_lot_sel_default_required', 'LOTSERATTR');
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
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
783 /* End of 3418790 */
780 WHEN OTHERS THEN
781 RETURN(0);
782 END is_context_displayed;
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_contexts.is_global(i))
832 ) THEN
833
834 -- Get segments
835 fnd_dflex.get_segments(fnd_dflex.make_context(v_flexfield,
836 v_contexts.context_code(i)),
837 v_segments, TRUE);
838
839 <<segmentLoop>>
840 FOR j IN 1..v_segments.nsegments LOOP
841 IF v_segments.is_enabled(j) THEN
842 IF v_segments.is_required(j) THEN
843 -- Found enabled and required segment, return
844 v_is_enabled := 2;
845 --Return 3 if the context is global
846 IF v_contexts.is_global(i) THEN
847 v_is_enabled := 3;
848 END IF;
849 EXIT contextLoop;
850 ELSE
851 v_is_enabled := 1;
852 END IF;
853 END IF;
854 END LOOP segmentLoop;
855 END IF;
856 END LOOP contextLoop;
857 RETURN v_is_enabled;
858 EXCEPTION
859 WHEN others THEN
860 v_is_enabled :=0;
861 RETURN v_is_enabled;
862 END is_enabled;
863
864
865 FUNCTION is_enabled_segment(
866 p_flex_name IN VARCHAR2,
867 p_segment_name in VARCHAR2,
868 p_organization_id IN NUMBER,
869 p_inventory_item_id IN NUMBER) RETURN NUMBER IS
870
871 c_api_name CONSTANT VARCHAR2(30) := 'is_enabled_segment';
872 v_flexfield fnd_dflex.dflex_r;
873 v_flexinfo fnd_dflex.dflex_dr;
874 v_contexts fnd_dflex.contexts_dr;
875 v_segments fnd_dflex.segments_dr;
876
877 v_attributes_category VARCHAR2(50) :=NULL;
878 i BINARY_INTEGER;
879 j BINARY_INTEGER;
880
881 v_is_enabled NUMBER := 0;
882 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
883 BEGIN
884
885 v_is_enabled :=0;
886
887 -- Get flexfield
888 fnd_dflex.get_flexfield('INV', p_flex_name, v_flexfield, v_flexinfo);
889
890 -- Get Contexts
891 fnd_dflex.get_contexts(v_flexfield, v_contexts);
892
893 -- Get attributes category
894 INV_LOT_SEL_ATTR.get_context_code(
895 context_value => v_attributes_category
896 , org_id => p_organization_id
897 , item_id => p_inventory_item_id
898 , flex_name => p_flex_name
899 , p_lot_serial_number => null);
900
901 <<contextLoop>>
902 FOR i IN 1..v_contexts.ncontexts LOOP
903 IF(v_contexts.is_enabled(i) AND
904 ((UPPER(v_contexts.context_code(i)) = UPPER(v_attributes_category)) OR
905 v_contexts.is_global(i))
906 ) THEN
907
908 -- Get segments
909 fnd_dflex.get_segments(fnd_dflex.make_context(v_flexfield,
910 v_contexts.context_code(i)),
911 v_segments, TRUE);
912
913 <<segmentLoop>>
914 FOR j IN 1..v_segments.nsegments LOOP
915 --dbms_output.put_line(v_segments.segment_name(j));
916 IF upper(v_segments.segment_name(j)) = upper(p_segment_name) THEN
917 IF v_segments.is_enabled(j) THEN
918 IF v_segments.is_required(j) THEN
919 -- Found enabled and required segment, return
920 v_is_enabled := 2;
921 EXIT contextLoop;
922 ELSE
923 v_is_enabled := 1;
924 EXIT segmentLoop;
925 END IF;
926 END IF;
927 END IF;
928 END LOOP segmentLoop;
929 END IF;
933 WHEN others THEN
930 END LOOP contextLoop;
931 RETURN v_is_enabled;
932 EXCEPTION
934 v_is_enabled :=0;
935 RETURN v_is_enabled;
936 END is_enabled_segment;
937
938 /* Function that returns True in case the Lot Exists in MLN, False otherwise */
939 FUNCTION does_lot_exist(p_lot_number IN VARCHAR2, p_inventory_item_id IN NUMBER, p_org_id IN NUMBER) RETURN BOOLEAN IS
940 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
941 l_lot_number VARCHAR2(80);
942 BEGIN
943 SELECT lot_number
944 INTO l_lot_number
945 FROM mtl_lot_numbers
946 WHERE lot_number = p_lot_number
947 AND inventory_item_id = p_inventory_item_id
948 AND organization_id = p_org_id;
949 RETURN TRUE;
950 EXCEPTION
951 WHEN OTHERS THEN
952 RETURN FALSE;
953 END does_lot_exist;
954
955
956 /* Added extra IN parameter p_issue_receipt to determine if the txn
957 * is of type issue or receipt. For transaction_action_id = 12,
958 * a value of 'IR' is passed, since for intransit receipt, though
959 * it is a receipt txn, we need to display the serial attributes from
960 * the source organization --bug 2756040
961 */
962 /* Bug 4328865: Added default value '@@@' for p_issue_receipt parameter*/
963
964
965 PROCEDURE get_attribute_values
966 ( x_lot_serial_attributes OUT NOCOPY lot_sel_attributes_tbl_type
967 , x_lot_serial_attributes_count OUT NOCOPY NUMBER
968 , x_return_status OUT NOCOPY VARCHAR2
969 , x_msg_count OUT NOCOPY NUMBER
970 , x_msg_data OUT NOCOPY VARCHAR2
971 , p_table_name IN VARCHAR2
972 , p_attributes_name IN VARCHAR2
973 , p_inventory_item_id IN NUMBER
974 , p_organization_id IN NUMBER
975 , p_lot_serial_number IN VARCHAR2
976 , p_issue_receipt IN VARCHAR2 DEFAULT '@@@') IS
977
978 l_sel_stmt VARCHAR2(32067):= 'SELECT ' ;
979 l_colnum NUMBER := 0;
980 l_sql_p INTEGER := NULL;
981 l_rows_processed INTEGER := NULL;
982 /* BUG 5334967 */
983 l_precision NUMBER := 0;
984 l_index NUMBER := 0;
985
986 l_flexfield fnd_dflex.dflex_r;
987 l_flexinfo fnd_dflex.dflex_dr;
988 l_contexts_info fnd_dflex.contexts_dr;
989 l_contexts fnd_dflex.context_r;
990 l_segments fnd_dflex.segments_dr;
991 l_attributes_category VARCHAR2(50) :=NULL;
992 l_global_code VARCHAR2(50);
993 l_rec_index BINARY_INTEGER := 0;
994
995 l_count NUMBER;
996 l_lot_serial_number VARCHAR2(240) := p_lot_serial_number;
997 l_column_attributes INV_LOT_SEL_ATTR.LOT_SEL_ATTRIBUTES_TBL_TYPE;
998 l_context_prompt fnd_descriptive_flexs_vl.form_context_prompt%TYPE; --bug 6636904
999 l_context_column_name VARCHAR2(240); --bug 2474713
1000 l_module_name VARCHAR2(25) := 'GET_ATTRIBUTE_VALUES';
1001 l_debug_level NUMBER := 9;
1002 l_status VARCHAR2(1);
1003 l_industry VARCHAR2(1);
1004 l_oracle_schema VARCHAR2(30);
1005 l_column_idx BINARY_INTEGER;
1006 l_default_format VARCHAR2(30) := 'YYYY/MM/DD HH24:MI:SS';
1007 l_date DATE;
1008 l_get_default BOOLEAN DEFAULT FALSE; -- true if we are to get the default values for the attributes
1009 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1010 l_ret boolean;
1011
1012 cursor column_csr(p_table_name VARCHAR2, p_owner VARCHAR2) is
1013 select column_name, data_type, data_length
1014 from all_tab_columns
1015 where table_name = p_table_name
1016 and owner = p_owner
1017 order by column_id;
1018
1019
1020 BEGIN
1021
1022 IF (l_debug = 1) THEN
1023 inv_trx_util_pub.trace('inputs to get_attribute_values: ','INV_LOT_SEL_ATTR',9);
1024 inv_trx_util_pub.trace('p_table_name ' || p_table_name,'INV_LOT_SEL_ATTR',9);
1025 inv_trx_util_pub.trace('p_attributes_name '|| p_attributes_name,'INV_LOT_SEL_ATTR',9);
1026 inv_trx_util_pub.trace('p_inventory_item_id '||p_inventory_item_id,'INV_LOT_SEL_ATTR',9);
1027 inv_trx_util_pub.trace('p_organization_id '||p_organization_id,'INV_LOT_SEL_ATTR',9);
1028 inv_trx_util_pub.trace('p_lot_serial_number '||p_lot_serial_number,'INV_LOT_SEL_ATTR',9);
1029 inv_trx_util_pub.trace('p_issue_receipt '||p_issue_receipt,'INV_LOT_SEL_ATTR',9);
1030 END IF;
1031 fnd_dflex.get_flexfield('INV', p_attributes_name, l_flexfield, l_flexinfo);
1032 -- Get Contexts
1033 fnd_dflex.get_contexts(l_flexfield, l_contexts_info);
1034 --Retrieve the context column name also --bug#2474713
1035 l_context_column_name := l_flexinfo.context_column_name;
1036 --Adding for bug 6636904
1037 l_context_prompt := l_flexinfo.form_context_prompt;
1038
1039 /* Bug# 3418790
1040 Check whether we need to get the default attribtues or not based on the attribute name and
1041 transaction type */
1042 IF (p_attributes_name = 'Serial Attributes') THEN
1043 /* Serial Attributes */
1044 IF(p_issue_receipt = 'R') THEN
1045 /* Serial Receipt...Get the default Attributes */
1046 l_get_default := TRUE;
1047 ELSIF (p_issue_receipt = 'I') THEN
1048 /* Serial Issue..Dont get the default values for the attribtues */
1052 /* Lot Attributes */
1049 l_get_default := FALSE;
1050 END IF;
1051 ELSIF (p_attributes_name = 'Lot Attributes') THEN
1053 IF((p_issue_receipt = 'R') AND (does_lot_exist(p_lot_serial_number
1054 ,p_inventory_item_id
1055 ,p_organization_id) = FALSE)) THEN
1056 /* Lot attributes for Receipt and the Lot Number is new */
1057 l_get_default := TRUE;
1058 ELSE
1059 l_get_default := FALSE;
1060 END IF;
1061 END IF;
1062 /* End of Bug# 3418790 */
1063
1064 /* BUG 2756040 Get attributes category from MSN if not Receipt txn (except Intransit
1065 * Receipt) and the attributes are Serial Attributes
1066 */
1067 IF( p_issue_receipt <> 'R'
1068 --AND p_attributes_name = 'Serial Attributes'
1069 ) THEN
1070 IF (l_debug = 1) THEN
1071 inv_trx_util_pub.trace('is not Receipt. calling the code to fetch from MSN or MLN depending on flex types');
1072 END IF;
1073 get_lot_serial_context(context_value => l_attributes_category
1074 , org_id => p_organization_id
1075 , item_id => p_inventory_item_id
1076 , p_lot_serial => p_lot_serial_number
1077 , flex_name => p_attributes_name);
1078 IF (l_debug = 1) THEN
1079 inv_trx_util_pub.trace('got the context value ' || l_attributes_category);
1080 END IF;
1081 ELSE
1082 get_context_code(
1083 context_value => l_attributes_category
1084 , org_id => p_organization_id
1085 , item_id => p_inventory_item_id
1086 , flex_name => p_attributes_name,
1087 p_lot_serial_number => null);
1088 END IF;
1089 IF (l_debug=1) THEN
1090 debug('No of Contexts ' ||l_contexts_info.ncontexts ,l_module_name,l_debug_level);
1091 END IF;
1092 FOR i IN 1..l_contexts_info.ncontexts
1093 LOOP
1094 IF(l_contexts_info.is_enabled(i) AND ((UPPER(l_contexts_info.context_code(i)) = UPPER(l_attributes_category)) OR
1095 l_contexts_info.is_global(i))) THEN
1096 /*bug #2474713 insert the context column.. This was added because the descriptive flexfield window
1097 expects the context_value also*/
1098 IF(NOT(l_contexts_info.is_global(i))) THEN
1099 l_rec_index := l_rec_index + 1;
1100 x_lot_serial_attributes(l_rec_index).COLUMN_NAME := l_context_column_name;
1101 get_column_type(
1102 p_table_name => p_table_name,
1103 p_column_name => x_lot_serial_attributes(l_rec_index).COLUMN_NAME,
1104 x_column_type => x_lot_serial_attributes(l_rec_index).COLUMN_TYPE);
1105 x_lot_serial_attributes(l_rec_index).REQUIRED := 'TRUE';
1106 --Adding for bug 6636904
1107 x_lot_serial_attributes(l_rec_index).PROMPT := l_context_prompt;
1108 END IF; /*bug #2474713 */
1109
1110 --Get segments
1111 l_contexts := fnd_dflex.make_context(l_flexfield, l_contexts_info.context_code(i));
1112 fnd_dflex.get_segments(l_contexts, l_segments, TRUE);
1113 ---dbms_output.put_line('number of segment is ' || l_segments.nsegments);
1114 IF (l_debug=1) THEN
1115 debug('No of Segments ' ||l_segments.nsegments ,l_module_name,l_debug_level);
1116 END IF;
1117
1118
1119 FOR j IN 1..l_segments.nsegments LOOP
1120 IF l_segments.is_enabled(j) THEN
1121 l_rec_index := l_rec_index +1 ;
1122 x_lot_serial_attributes(l_rec_index).COLUMN_NAME := l_segments.application_column_name(j);
1123 IF (l_debug=1) THEN
1124 debug('value set id is '||l_segments.value_set(j),l_module_name,l_debug_level);
1125 END IF;
1126 get_column_type( p_table_name => p_table_name,
1127 p_column_name => x_lot_serial_attributes(l_rec_index).COLUMN_NAME,
1128 x_column_type => x_lot_serial_attributes(l_rec_index).COLUMN_TYPE);
1129
1130 IF(l_segments.is_required(j)) THEN
1131 x_lot_serial_attributes(l_rec_index).REQUIRED := 'TRUE';
1132 ELSE
1133 x_lot_serial_attributes(l_rec_index).REQUIRED := 'FALSE';
1134 END IF;
1135
1136 /* Bug# 3418790
1137 Get the default values for the segments */
1138 IF (l_debug=1) THEN
1139 debug('Column Type ' ||x_lot_serial_attributes(l_rec_index).COLUMN_TYPE ,l_module_name,l_debug_level);
1140 debug('Column Name ' ||x_lot_serial_attributes(l_rec_index).COLUMN_NAME ,l_module_name,l_debug_level);
1141 END IF;
1142 IF(l_get_default = TRUE) THEN
1143 IF(x_lot_serial_attributes(l_rec_index).COLUMN_TYPE = 'DATE') THEN
1144 IF (l_debug=1) THEN
1145 debug('Default Type is ' || l_segments.default_type(j),l_module_name,l_debug_level);
1146 END IF;
1147 IF(l_segments.default_type(j) = 'D') THEN
1148 /* Get the Value of the Current Date */
1149 SELECT SYSDATE
1150 INTO l_date
1151 FROM dual;
1155 l_date := fnd_date.canonical_to_date(l_segments.default_value(j));
1152 ELSIF(l_segments.default_type(j) = 'C') THEN
1153 /* Constant Value is default */
1154 --l_date := to_date(l_segments.default_value(j),L_default_format);
1156 END IF;
1157 x_lot_serial_attributes(l_rec_index).COLUMN_VALUE := fnd_date.date_to_displayDT(l_date);
1158 ELSE
1159 x_lot_serial_attributes(l_rec_index).COLUMN_VALUE := l_segments.default_value(j);
1160 END IF;
1161 END IF;
1162
1163 --Adding for bug 6636904
1164 x_lot_serial_attributes(l_rec_index).PROMPT := l_segments.row_prompt(j);
1165 END IF;
1166 END LOOP;
1167
1168 IF (l_debug=1) THEN
1169 debug('No of Records ' ||l_rec_index ,l_module_name,l_debug_level);
1170 FOR j IN 1..l_rec_index LOOP
1171 debug('Column Name ' || x_lot_serial_attributes(j).COLUMN_NAME,l_module_name,l_debug_level);
1172 debug('Column Value ' || x_lot_serial_attributes(j).COLUMN_VALUE,l_module_name,l_debug_level);
1173 debug('Column Type ' || x_lot_serial_attributes(j).COLUMN_TYPE,l_module_name,l_debug_level);
1174 END LOOP;
1175 END IF;
1176
1177 l_sel_stmt := 'SELECT ' ;
1178 l_column_idx := 0;
1179 l_ret := fnd_installation.get_app_info('INV', l_status, l_industry, l_oracle_schema);
1180
1181 OPEN column_csr(upper(p_table_name), l_oracle_schema);
1182 LOOP
1183
1184 l_column_idx := l_column_idx + 1;
1185
1186 FETCH column_csr INTO l_column_attributes(l_column_idx).column_name,
1187 l_column_attributes(l_column_idx).column_type,
1188 l_column_attributes(l_column_idx).column_length;
1189 EXIT WHEN column_csr%NOTFOUND;
1190 /*dbms_output.put_line (' column_csr: ' || l_column_attributes(l_column_idx).column_name||':'||
1191 l_column_attributes(l_column_idx).column_type);*/
1192 IF l_column_idx = 1 then
1193 l_sel_stmt := l_sel_stmt || l_column_attributes(l_column_idx).column_name ;
1194 --dbms_output.put_line (' l_sel_stmt'||l_sel_stmt);
1195 ELSE
1196 -- dbms_output.put_line (' l_endcol'||l_endcol);
1197 l_sel_stmt := l_sel_stmt || ', ' || l_column_attributes(l_column_idx).column_name ;
1198 END IF;
1199
1200 END LOOP;
1201 CLOSE column_csr;
1202
1203 IF p_table_name = 'MTL_LOT_NUMBERS' THEN
1204 l_sel_stmt := l_sel_stmt || ' ' ||
1205 'from mtl_lot_numbers ' ||
1206 'where lot_number = :b_lot_number ' ||
1207 'and inventory_item_id = :b_item_id ' ||
1208 'and organization_id = :b_org_id ' ;
1209 ELSIF p_table_name = 'MTL_SERIAL_NUMBERS' THEN
1210 l_sel_stmt := l_sel_stmt || ' ' ||
1211 'from mtl_serial_numbers ' ||
1212 'where serial_number = :b_serial_number ' ||
1213 'and inventory_item_id = :b_item_id ' ||
1214 'and CURRENT_organization_id = :b_org_id ' ;
1215 END IF;
1216
1217 l_sql_p := DBMS_SQL.OPEN_CURSOR;
1218
1219 DBMS_SQL.PARSE( l_sql_p, l_sel_stmt , DBMS_SQL.NATIVE );
1220
1221
1222 DBMS_SQL.BIND_VARIABLE(l_sql_p, 'b_org_id', p_organization_id);
1223 DBMS_SQL.BIND_VARIABLE(l_sql_p, 'b_item_id', p_inventory_item_id);
1224
1225 IF p_table_name = 'MTL_LOT_NUMBERS' THEN
1226 DBMS_SQL.BIND_VARIABLE(l_sql_p, 'b_lot_number', p_lot_serial_number);
1227 ELSIF p_table_name = 'MTL_SERIAL_NUMBERS' THEN
1228 DBMS_SQL.BIND_VARIABLE(l_sql_p, 'b_serial_number', p_lot_serial_number);
1229 END IF;
1230
1231 l_colnum := 0;
1232 --dbms_output.put_line ('Count(): ' || l_column_attributes.count());
1233 --dbms_output.put_line ('Count: ' || l_column_attributes.count);
1234 FOR y in 1..l_column_attributes.count() - 1
1235 LOOP
1236 l_colnum := l_colnum + 1;
1237
1238 IF UPPER(l_column_attributes(y).column_type) = ('DATE') THEN
1239 l_column_attributes(y).column_length := 10;
1240 ELSIF UPPER(l_column_attributes(y).column_type) = 'NUMBER' THEN
1241 l_column_attributes(y).column_length := 38;
1242 END IF;
1243 DBMS_SQL.DEFINE_COLUMN(l_sql_p, l_colnum, l_column_attributes(y).column_value,
1244 l_column_attributes(y).column_length);
1245
1246 END LOOP;
1247
1248 l_rows_processed := DBMS_SQL.EXECUTE(l_sql_p);
1249 IF (l_debug=1) THEN
1253 LOOP
1250 debug('l_rows_processes = ' || l_rows_processed, l_module_name, l_debug_level);
1251 END IF;
1252
1254 IF (DBMS_SQL.FETCH_ROWS(l_sql_p) > 0 ) THEN
1255 l_colnum := 0;
1256 FOR y in 1..l_column_attributes.count() -1
1257 LOOP
1258 l_colnum := l_colnum + 1;
1259 DBMS_SQL.COLUMN_VALUE(l_sql_p, l_colnum, l_column_attributes(y).column_value);
1260 END LOOP;
1261 ELSE
1262 IF (l_debug=1) THEN
1263 debug('in the else part of dbms_sql.fetch_rows ' , l_module_name, l_debug_level);
1264 END IF;
1265
1266 --dbms_sql.close_cursor(l_sql_p);
1267 EXIT;
1268 /* we do'nt care, if no record is found */
1269 END IF;
1270 EXIT;
1271 END LOOP;
1272
1273 dbms_sql.close_cursor(l_sql_p);
1274
1275
1276 IF(l_get_Default = FALSE) THEN
1277 FOR x in 1.. l_rec_index LOOP
1278 FOR y IN 1..l_column_attributes.count() -1 LOOP
1279 IF( upper(l_column_attributes(y).column_name) = upper(x_lot_serial_attributes(x).column_name)) then
1280
1281 IF (l_debug=1) THEN
1282 debug('column_name = ' || l_column_attributes(y).column_name, l_module_name, l_debug_level);
1283 debug('column_TYPE = ' || x_lot_serial_attributes(x).column_type, l_module_name, l_debug_level);
1284 debug(x,l_module_name,l_debug_level);
1285 END IF;
1286 IF(x_lot_serial_attributes(x).column_type = 'DATE') THEN
1287 x_lot_serial_attributes(x).column_value := fnd_date.date_to_displayDT(l_column_attributes(y).column_Value);
1288
1289 /* BUG 5334967 added the ELSIF condition for checking if the column type is NUMBER*/
1290 ELSIF (x_lot_serial_attributes(x).column_type = 'NUMBER') THEN
1291 IF (l_debug=1) THEN
1292 debug('in NUMBER', l_module_name, l_debug_level);
1293 END IF;
1294
1295 x_lot_serial_attributes(x).column_value := l_column_attributes(y).column_Value;
1296
1297 FOR i in 1..l_segments.nsegments LOOP
1298 /* When you find that the application col name = sement column name, exit. The index thus obtained will be used for the
1299 value set in getting the precision */
1300 IF l_segments.application_column_name(i) = l_column_attributes(y).column_name THEN
1301 l_index := i;
1302 EXIT;
1303 END IF;
1304 END LOOP;
1305 IF (l_debug=1) THEN
1306 debug('Index = ' || l_index, l_module_name, l_debug_level);
1307 debug('col index = ' || l_segments.value_set(l_index), l_module_name, l_debug_level);
1308 END IF;
1309 /*getting the precision for segment value of type Number*/
1310 SELECT number_precision INTO l_precision
1311 FROM FND_FLEX_VALUE_SETS
1312 WHERE flex_value_set_id = l_segments.value_set(l_index);
1313
1314 IF (l_debug=1) THEN
1315 debug('PRECISION = ' || l_precision, l_module_name, l_debug_level);
1316 END IF;
1317 IF (l_precision >0) THEN
1318 SELECT round(x_lot_serial_attributes(x).column_value, l_precision) INTO x_lot_serial_attributes(x).column_value FROM DUAL;
1319 END IF;
1320 IF (l_debug=1) THEN
1321 debug('column_value1 = ' || x_lot_serial_attributes(x).column_value, l_module_name, l_debug_level);
1322 END IF;
1323 /* End of changes for BUG 5334967 */
1324 ELSE
1325 x_lot_serial_attributes(x).column_value := l_column_attributes(y).column_Value;
1326 IF (l_debug=1) THEN
1327 debug('In ELSE ' ,l_module_name, l_debug_level);
1328 END IF;
1329 END IF;
1330 EXIT;
1331 END IF;
1332 END LOOP;
1333 END LOOP;
1334 END IF;
1335 END IF;
1336 END LOOP;
1337 -- assign it to the out variable
1338 x_lot_serial_attributes_count := l_rec_index;
1339 x_return_status := fnd_api.g_ret_sts_success ;
1340
1341 EXCEPTION
1342 WHEN fnd_api.g_exc_error THEN
1343 x_return_status := fnd_api.g_ret_sts_error ;
1344
1345 WHEN fnd_api.g_exc_unexpected_error THEN
1346 x_return_status := fnd_api.g_ret_sts_unexp_error;
1347
1348 WHEN others THEN
1349 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1350 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)) THEN
1351 fnd_msg_pub.add_exc_msg(g_pkg_name, 'GET_ATTRIBUTE_VALUES');
1352 END IF;
1353 END get_attribute_values;
1354
1355
1356 PROCEDURE get_dflex_context(
1357 x_context OUT NOCOPY t_genref,
1361 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1358 p_application_id IN NUMBER,
1359 p_flex_name IN VARCHAR2) IS
1360
1362 BEGIN
1363 OPEN x_context FOR
1364 SELECT descriptive_flex_context_code, descriptive_flex_context_name,
1365 global_flag, enabled_flag
1366 FROM fnd_descr_flex_contexts_vl
1367 WHERE application_id = p_application_id
1368 AND descriptive_flexfield_name = p_flex_name;
1369
1370 END get_dflex_context;
1371
1372 PROCEDURE get_dflex_segment(
1373 x_segment OUT NOCOPY t_genref,
1374 p_application_id IN NUMBER,
1375 p_flex_name IN VARCHAR2,
1376 p_flex_context_code IN VARCHAR2) IS
1377 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1378 BEGIN
1379 OPEN x_segment FOR
1380 SELECT end_user_column_name, application_column_name, enabled_flag,
1381 required_flag, default_type, default_value
1382 FROM fnd_descr_flex_col_usage_vl
1383 WHERE application_id = p_application_id
1384 AND descriptive_flexfield_name = p_flex_name
1385 AND descriptive_flex_context_code = p_flex_context_code;
1386
1387 END get_dflex_segment;
1388
1389 ---J Develop
1390 /* New Procedure to get the Inventory Attributes */
1391 procedure get_inv_lot_attributes( x_return_status OUT NOCOPY VARCHAR2
1392 ,x_msg_count OUT NOCOPY NUMBER
1393 ,x_msg_data OUT NOCOPY VARCHAR2
1394 ,x_inv_lot_attributes OUT NOCOPY inv_lot_sel_attr.lot_sel_attributes_tbl_type
1395 ,P_inventory_item_id IN NUMBER
1396 ,P_LOT_NUMBER IN VARCHAR2
1397 ,p_organization_id IN NUMBER
1398 ,p_attribute_category IN VARCHAR2
1399 )
1400
1401 IS
1402 TYPE inv_lot_attr IS TABLE OF VARCHAR2(1000) INDEX BY BINARY_INTEGER;
1403 l_inv_lot_attr inv_lot_attr;
1404
1405 CURSOR inv_attr IS
1406 SELECT attribute1
1407 ,attribute2
1408 ,attribute3
1409 ,attribute4
1410 ,attribute5
1411 ,attribute6
1412 ,attribute7
1413 ,attribute8
1414 ,attribute9
1415 ,attribute10
1416 ,attribute11
1417 ,attribute12
1418 ,attribute13
1419 ,attribute14
1420 ,attribute15
1421 FROM mtl_lot_numbers
1422 WHERE inventory_item_id = p_inventory_item_id
1423 AND organization_id = p_organization_id
1424 AND lot_number = p_lot_number;
1425
1426 l_inv_attr inv_attr%ROWTYPE;
1427 l_context VARCHAR2(1000);
1428 l_context_r fnd_dflex.context_r;
1429 l_contexts_dr fnd_dflex.contexts_dr;
1430 l_dflex_r fnd_dflex.dflex_r;
1431 l_segments_dr fnd_dflex.segments_dr;
1432 l_global_context BINARY_INTEGER;
1433 l_nsegments BINARY_INTEGER;
1434
1435 BEGIN
1436
1437 OPEN inv_attr;
1438 FETCH inv_attr INTO l_inv_attr;
1439
1440 /* Fill the lot data into l_inv_lot_attr table */
1441 l_inv_lot_attr(1) := l_inv_attr.attribute1;
1442 l_inv_lot_attr(2) := l_inv_attr.attribute2;
1443 l_inv_lot_attr(3) := l_inv_attr.attribute3;
1444 l_inv_lot_attr(4) := l_inv_attr.attribute4;
1445 l_inv_lot_attr(5) := l_inv_attr.attribute5;
1446 l_inv_lot_attr(6) := l_inv_attr.attribute6;
1447 l_inv_lot_attr(7) := l_inv_attr.attribute7;
1448 l_inv_lot_attr(8) := l_inv_attr.attribute8;
1449 l_inv_lot_attr(9) := l_inv_attr.attribute9;
1450 l_inv_lot_attr(10) := l_inv_attr.attribute10;
1451 l_inv_lot_attr(11) := l_inv_attr.attribute11;
1452 l_inv_lot_attr(12) := l_inv_attr.attribute12;
1453 l_inv_lot_attr(13) := l_inv_attr.attribute13;
1454 l_inv_lot_attr(14) := l_inv_attr.attribute14;
1455 l_inv_lot_attr(15) := l_inv_attr.attribute15;
1456 CLOSE inv_attr;
1457
1458 x_return_status := 'S';
1459 l_dflex_r.application_id := 401;
1460 l_dflex_r.flexfield_name := 'MTL_LOT_NUMBERS';
1461 fnd_dflex.get_contexts(flexfield => l_dflex_r, contexts => l_contexts_dr);
1462 l_global_context := l_contexts_dr.global_context;
1463 l_context := l_contexts_dr.context_code(l_global_context);
1464
1465 /* Prepare the context_r type for getting the segments associated with the global context */
1466 l_context_r.flexfield := l_dflex_r;
1467 l_context_r.context_code := l_context;
1468
1469 fnd_dflex.get_segments(CONTEXT => l_context_r, segments => l_segments_dr, enabled_only => TRUE);
1470
1471 /* read through the segments */
1472
1473 l_nsegments := l_segments_dr.nsegments;
1474 FOR j IN 1..l_nsegments LOOP
1475 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);
1476 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';
1477 IF l_segments_dr.is_required(j) THEN
1481 END IF;
1478 x_inv_lot_attributes(substr(l_segments_dr.application_column_name(j),instr(l_segments_dr.application_column_name(j),'ATTRIBUTE')+9)).required :='TRUE';
1479 ELSE
1480 x_inv_lot_attributes(substr(l_segments_dr.application_column_name(j),instr(l_segments_dr.application_column_name(j),'ATTRIBUTE')+9)).required :='FALSE';
1482 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;
1483 x_inv_lot_attributes(substr(l_segments_dr.application_column_name(j),instr(l_segments_dr.application_column_name(j),'ATTRIBUTE')+9)).column_value :=
1484 l_inv_lot_attr(substr(l_segments_dr.application_column_name(j),instr(l_segments_dr.application_column_name(j),'ATTRIBUTE')+9));
1485 END LOOP;
1486 l_context := NULL;
1487 l_nsegments := NULL;
1488
1489 IF p_attribute_category IS NOT NULL THEN
1490
1491 l_context := p_attribute_category;
1492
1493 /* Prepare the context_r type for getting the segments associated with the input context */
1494
1495 l_context_r.flexfield := l_dflex_r;
1496 l_context_r.context_code := l_context;
1497
1498 fnd_dflex.get_segments(CONTEXT => l_context_r, segments => l_segments_dr, enabled_only => TRUE);
1499 l_nsegments := l_segments_dr.nsegments;
1500 FOR i IN 1..l_nsegments LOOP
1501
1502 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);
1503 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';
1504 IF l_segments_dr.is_required(i) THEN
1505 x_inv_lot_attributes(substr(l_segments_dr.application_column_name(i),instr(l_segments_dr.application_column_name(i),'ATTRIBUTE')+9)).required :='TRUE';
1506 ELSE
1507 x_inv_lot_attributes(substr(l_segments_dr.application_column_name(i),instr(l_segments_dr.application_column_name(i),'ATTRIBUTE')+9)).required :='FALSE';
1508 END IF;
1509 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;
1510 x_inv_lot_attributes(substr(l_segments_dr.application_column_name(i),instr(l_segments_dr.application_column_name(i),'ATTRIBUTE')+9)).column_value :=
1511 l_inv_lot_attr(substr(l_segments_dr.application_column_name(i),instr(l_segments_dr.application_column_name(i),'ATTRIBUTE')+9));
1512 END LOOP;
1513 END IF;
1514 END get_inv_lot_attributes;
1515
1516 procedure get_inv_serial_attributes( x_return_status OUT NOCOPY VARCHAR2
1517 ,x_msg_count OUT NOCOPY NUMBER
1518 ,x_msg_data OUT NOCOPY VARCHAR2
1519 ,x_inv_serial_attributes OUT NOCOPY inv_lot_sel_attr.lot_sel_attributes_tbl_type
1520 ,x_concatenated_values OUT NOCOPY VARCHAR2
1521 ,P_inventory_item_id IN NUMBER
1522 ,P_SERIAL_NUMBER IN VARCHAR2
1523 ,p_attribute_category IN VARCHAR2
1524 ,p_transaction_temp_id IN NUMBER DEFAULT NULL
1525 ,p_transaction_source IN VARCHAR2 DEFAULT NULL
1526 )
1527
1528 IS
1529 TYPE inv_serial_attr IS TABLE OF VARCHAR2(150) INDEX BY BINARY_INTEGER;
1530 l_inv_serial_attr inv_serial_attr;
1531
1532 CURSOR inv_attr IS
1533 SELECT attribute1
1534 ,attribute2
1535 ,attribute3
1536 ,attribute4
1537 ,attribute5
1538 ,attribute6
1539 ,attribute7
1540 ,attribute8
1541 ,attribute9
1542 ,attribute10
1543 ,attribute11
1544 ,attribute12
1545 ,attribute13
1546 ,attribute14
1547 ,attribute15
1548 FROM mtl_SERIAL_numbers
1549 WHERE inventory_item_id = p_inventory_item_id
1550 AND SERIAL_number = p_serial_number;
1551 /*
1552 CURSOR inv_ship_attr IS
1553 SELECT nvl(msn.attribute1, msnt.attribute1) attribute1
1554 , nvl(msn.attribute2, msnt.attribute2) attribute2
1555 , nvl(msn.attribute3, msnt.attribute3) attribute3
1556 , nvl(msn.attribute4, msnt.attribute4) attribute4
1557 , nvl(msn.attribute5, msnt.attribute5) attribute5
1558 , nvl(msn.attribute6, msnt.attribute6) attribute6
1559 , nvl(msn.attribute7, msnt.attribute7) attribute7
1560 , nvl(msn.attribute8, msnt.attribute8) attribute8
1561 , nvl(msn.attribute9, msnt.attribute9) attribute9
1562 , nvl(msn.attribute10, msnt.attribute10) attribute10
1563 , nvl(msn.attribute11, msnt.attribute11) attribute11
1564 , nvl(msn.attribute12, msnt.attribute12) attribute12
1565 , nvl(msn.attribute13, msnt.attribute13) attribute13
1566 , nvl(msn.attribute14, msnt.attribute14) attribute14
1567 , nvl(msn.attribute15, msnt.attribute15) attribute15
1568 FROM mtl_serial_numbers msn, mtl_serial_numbers_temp msnt
1569 WHERE msn.inventory_item_id = p_inventory_item_id
1570 AND msn.serial_number = p_serial_number
1571 AND msnt.transaction_temp_id = p_transaction_temp_id
1572 AND msnt.fm_serial_number = p_serial_number
1573 AND msn.serial_number = msnt.fm_serial_number;
1574 */
1578 ,msnt.attribute2 attribute2
1575 /* Commented the above cursor and the following cursor for Bug 3839336 */
1576 CURSOR inv_ship_attr IS
1577 SELECT msnt.attribute1 attribute1
1579 ,msnt.attribute3 attribute3
1580 ,msnt.attribute4 attribute4
1581 ,msnt.attribute5 attribute5
1582 ,msnt.attribute6 attribute6
1583 ,msnt.attribute7 attribute7
1584 ,msnt.attribute8 attribute8
1585 ,msnt.attribute9 attribute9
1586 ,msnt.attribute10 attribute10
1587 ,msnt.attribute11 attribute11
1588 ,msnt.attribute12 attribute12
1589 ,msnt.attribute13 attribute13
1590 ,msnt.attribute14 attribute14
1591 ,msnt.attribute15 attribute15
1592 FROM mtl_serial_numbers msn, mtl_serial_numbers_temp msnt
1593 WHERE msn.inventory_item_id = p_inventory_item_id
1594 AND msn.serial_number = p_serial_number
1595 AND msnt.transaction_temp_id = p_transaction_temp_id
1596 AND msnt.fm_serial_number = p_serial_number
1597 AND msn.serial_number = msnt.fm_serial_number;
1598
1599
1600 l_inv_attr inv_attr%ROWTYPE;
1601 l_inv_ship_attr inv_ship_attr%ROWTYPE;
1602 l_context VARCHAR2(1000);
1603 l_context_r fnd_dflex.context_r;
1604 l_contexts_dr fnd_dflex.contexts_dr;
1605 l_dflex_r fnd_dflex.dflex_r;
1606 l_segments_dr fnd_dflex.segments_dr;
1607 l_global_context BINARY_INTEGER;
1608 l_nsegments BINARY_INTEGER;
1609
1610 BEGIN
1611
1612 debug('Inside get_inv_serial_attributes', 'GET_INV_SERIAL_ATTR', 10);
1613 debug('p_transaction_source is ' || p_transaction_source, 'GET_INV_SERIAL_ATTR', 10);
1614 debug('p_serial_number is ' || p_serial_number, 'GET_INV_SERIAL_ATTR', 10);
1615 debug('p_inventory_item_id is '|| p_inventory_item_id, 'GET_INV_SERIAL_ATTR', 10);
1616 debug('p_transaction_temp_id is ' || p_transaction_temp_id, 'GET_INV_SERIAL_ATTR', 10);
1617
1618 IF (nvl(p_transaction_source, 'INV') = 'WSH') THEN
1619 debug('OPEN inv_ship_attr', 'GET_INV_SERIAL_ATTR', 9);
1620 if( p_transaction_temp_id is null ) THEN
1621 BEGIN
1622 open inv_attr;
1623 FETCH inv_attr INTO l_inv_attr;
1624 EXCEPTION
1625 when no_data_found then
1626 l_inv_attr.attribute1 := null;
1627 l_inv_attr.attribute2 := null;
1628 l_inv_attr.attribute3 := null;
1629 l_inv_attr.attribute4 := null;
1630 l_inv_attr.attribute5 := null;
1631 l_inv_attr.attribute6 := null;
1632 l_inv_attr.attribute7 := null;
1633 l_inv_attr.attribute8 := null;
1634 l_inv_attr.attribute9 := null;
1635 l_inv_attr.attribute10 := null;
1636 l_inv_attr.attribute11 := null;
1637 l_inv_attr.attribute12 := null;
1638 l_inv_attr.attribute13 := null;
1639 l_inv_attr.attribute14 := null;
1640 l_inv_attr.attribute15 := null;
1641 END;
1642 else
1643 BEGIN
1644 OPEN inv_ship_attr;
1645 FETCH inv_ship_attr INTO l_inv_ship_attr;
1646 EXCEPTION
1647 WHEN no_data_found then
1648 l_inv_ship_attr.attribute1 := null;
1649 l_inv_ship_attr.attribute2 := null;
1650 l_inv_ship_attr.attribute3 := null;
1651 l_inv_ship_attr.attribute4 := null;
1652 l_inv_ship_attr.attribute5 := null;
1653 l_inv_ship_attr.attribute6 := null;
1654 l_inv_ship_attr.attribute7 := null;
1655 l_inv_ship_attr.attribute8 := null;
1656 l_inv_ship_attr.attribute9 := null;
1657 l_inv_ship_attr.attribute10 := null;
1658 l_inv_ship_attr.attribute11 := null;
1659 l_inv_ship_attr.attribute12 := null;
1660 l_inv_ship_attr.attribute13 := null;
1661 l_inv_ship_attr.attribute14 := null;
1662 l_inv_ship_attr.attribute15 := null;
1663 END;
1664 end if;
1665 ELSE
1666 --Bug #3765098 ( If txn temp id has value , the retrive the attr from msnt
1667 -- or get the attr from msn)
1668 if( p_transaction_temp_id is null ) THEN
1669 BEGIN
1670 OPEN inv_attr;
1671 FETCH inv_attr INTO l_inv_attr;
1672 EXCEPTION
1673 when no_data_found then
1674 l_inv_attr.attribute1 := null;
1675 l_inv_attr.attribute2 := null;
1676 l_inv_attr.attribute3 := null;
1677 l_inv_attr.attribute4 := null;
1678 l_inv_attr.attribute5 := null;
1679 l_inv_attr.attribute6 := null;
1680 l_inv_attr.attribute7 := null;
1681 l_inv_attr.attribute8 := null;
1682 l_inv_attr.attribute9 := null;
1683 l_inv_attr.attribute10 := null;
1684 l_inv_attr.attribute11 := null;
1685 l_inv_attr.attribute12 := null;
1686 l_inv_attr.attribute13 := null;
1687 l_inv_attr.attribute14 := null;
1688 l_inv_attr.attribute15 := null;
1689 end;
1690 else
1691 BEGIN
1692 OPEN inv_ship_attr;
1693 FETCH inv_ship_attr INTO l_inv_ship_attr;
1694 EXCEPTION
1695 WHEN no_data_found then
1696 l_inv_ship_attr.attribute1 := null;
1697 l_inv_ship_attr.attribute2 := null;
1698 l_inv_ship_attr.attribute3 := null;
1699 l_inv_ship_attr.attribute4 := null;
1703 l_inv_ship_attr.attribute8 := null;
1700 l_inv_ship_attr.attribute5 := null;
1701 l_inv_ship_attr.attribute6 := null;
1702 l_inv_ship_attr.attribute7 := null;
1704 l_inv_ship_attr.attribute9 := null;
1705 l_inv_ship_attr.attribute10 := null;
1706 l_inv_ship_attr.attribute11 := null;
1707 l_inv_ship_attr.attribute12 := null;
1708 l_inv_ship_attr.attribute13 := null;
1709 l_inv_ship_attr.attribute14 := null;
1710 l_inv_ship_attr.attribute15 := null;
1711 END;
1712 end if;
1713 END IF;
1714
1715 IF (nvl(p_transaction_source, 'INV') = 'WSH') THEN
1716 if( p_transaction_temp_id is not null ) then
1717 l_inv_serial_attr(1) := l_inv_ship_attr.attribute1;
1718 l_inv_serial_attr(2) := l_inv_ship_attr.attribute2;
1719 l_inv_serial_attr(3) := l_inv_ship_attr.attribute3;
1720 l_inv_serial_attr(4) := l_inv_ship_attr.attribute4;
1721 l_inv_serial_attr(5) := l_inv_ship_attr.attribute5;
1722 l_inv_serial_attr(6) := l_inv_ship_attr.attribute6;
1723 l_inv_serial_attr(7) := l_inv_ship_attr.attribute7;
1724 l_inv_serial_attr(8) := l_inv_ship_attr.attribute8;
1725 l_inv_serial_attr(9) := l_inv_ship_attr.attribute9;
1726 l_inv_serial_attr(10) := l_inv_ship_attr.attribute10;
1727 l_inv_serial_attr(11) := l_inv_ship_attr.attribute11;
1728 l_inv_serial_attr(12) := l_inv_ship_attr.attribute12;
1729 l_inv_serial_attr(13) := l_inv_ship_attr.attribute13;
1730 l_inv_serial_attr(14) := l_inv_ship_attr.attribute14;
1731 l_inv_serial_attr(15) := l_inv_ship_attr.attribute15;
1732 CLOSE inv_ship_attr;
1733 else
1734 l_inv_serial_attr(1) := l_inv_attr.attribute1;
1735 l_inv_serial_attr(2) := l_inv_attr.attribute2;
1736 l_inv_serial_attr(3) := l_inv_attr.attribute3;
1737 l_inv_serial_attr(4) := l_inv_attr.attribute4;
1738 l_inv_serial_attr(5) := l_inv_attr.attribute5;
1739 l_inv_serial_attr(6) := l_inv_attr.attribute6;
1740 l_inv_serial_attr(7) := l_inv_attr.attribute7;
1741 l_inv_serial_attr(8) := l_inv_attr.attribute8;
1742 l_inv_serial_attr(9) := l_inv_attr.attribute9;
1743 l_inv_serial_attr(10) := l_inv_attr.attribute10;
1744 l_inv_serial_attr(11) := l_inv_attr.attribute11;
1745 l_inv_serial_attr(12) := l_inv_attr.attribute12;
1746 l_inv_serial_attr(13) := l_inv_attr.attribute13;
1747 l_inv_serial_attr(14) := l_inv_attr.attribute14;
1748 l_inv_serial_attr(15) := l_inv_attr.attribute15;
1749 CLOSE inv_attr;
1750 end if;
1751 ELSE
1752 --Bug #3765098 ( If txn temp id has value , the retrive the attr from msnt
1753 -- or get the attr from msn)
1754 if( p_transaction_temp_id is not null ) then
1755 l_inv_serial_attr(1) := l_inv_ship_attr.attribute1;
1756 l_inv_serial_attr(2) := l_inv_ship_attr.attribute2;
1757 l_inv_serial_attr(3) := l_inv_ship_attr.attribute3;
1758 l_inv_serial_attr(4) := l_inv_ship_attr.attribute4;
1759 l_inv_serial_attr(5) := l_inv_ship_attr.attribute5;
1760 l_inv_serial_attr(6) := l_inv_ship_attr.attribute6;
1761 l_inv_serial_attr(7) := l_inv_ship_attr.attribute7;
1762 l_inv_serial_attr(8) := l_inv_ship_attr.attribute8;
1763 l_inv_serial_attr(9) := l_inv_ship_attr.attribute9;
1764 l_inv_serial_attr(10) := l_inv_ship_attr.attribute10;
1765 l_inv_serial_attr(11) := l_inv_ship_attr.attribute11;
1766 l_inv_serial_attr(12) := l_inv_ship_attr.attribute12;
1767 l_inv_serial_attr(13) := l_inv_ship_attr.attribute13;
1768 l_inv_serial_attr(14) := l_inv_ship_attr.attribute14;
1769 l_inv_serial_attr(15) := l_inv_ship_attr.attribute15;
1770 CLOSE inv_ship_attr;
1771 else
1772 l_inv_serial_attr(1) := l_inv_attr.attribute1;
1773 l_inv_serial_attr(2) := l_inv_attr.attribute2;
1774 l_inv_serial_attr(3) := l_inv_attr.attribute3;
1775 l_inv_serial_attr(4) := l_inv_attr.attribute4;
1776 l_inv_serial_attr(5) := l_inv_attr.attribute5;
1777 l_inv_serial_attr(6) := l_inv_attr.attribute6;
1778 l_inv_serial_attr(7) := l_inv_attr.attribute7;
1779 l_inv_serial_attr(8) := l_inv_attr.attribute8;
1780 l_inv_serial_attr(9) := l_inv_attr.attribute9;
1781 l_inv_serial_attr(10) := l_inv_attr.attribute10;
1782 l_inv_serial_attr(11) := l_inv_attr.attribute11;
1783 l_inv_serial_attr(12) := l_inv_attr.attribute12;
1784 l_inv_serial_attr(13) := l_inv_attr.attribute13;
1785 l_inv_serial_attr(14) := l_inv_attr.attribute14;
1786 l_inv_serial_attr(15) := l_inv_attr.attribute15;
1787 CLOSE inv_attr;
1788 end if;
1789 END IF;
1790
1791 x_return_status := 'S';
1792 l_dflex_r.application_id := 401;
1793 l_dflex_r.flexfield_name := 'MTL_SERIAL_NUMBERS';
1794 fnd_dflex.get_contexts(flexfield => l_dflex_r, contexts => l_contexts_dr);
1795 l_global_context := l_contexts_dr.global_context;
1796 l_context := l_contexts_dr.context_code(l_global_context);
1797
1798 /* Prepare the context_r type for getting the segments associated with the global context */
1799 l_context_r.flexfield := l_dflex_r;
1800 l_context_r.context_code := l_context;
1801
1802 fnd_dflex.get_segments(CONTEXT => l_context_r, segments => l_segments_dr, enabled_only => TRUE);
1803 debug('after calling fnd_dflex.get_segments', 'GET_INV_SERIAL_ATTR', 9);
1804
1808 FOR j IN 1..l_nsegments LOOP
1805 /* read through the segments */
1806
1807 l_nsegments := l_segments_dr.nsegments;
1809 debug('j = ' || j, 'GET_INV_SERIAL_ATTR', 9);
1810 debug('column application name is ' || l_segments_dr.application_column_name(j), 'GET_INV_SERIAL_ATTR', 9);
1811 debug(substr(l_segments_dr.application_column_name(j), instr(l_segments_dr.application_column_name(j), 'ATTRIBUTE')+9), 'GET_INV_SERIAL_ATTR', 9);
1812
1813 x_inv_serial_attributes(
1814 substr(l_segments_dr.application_column_name(j),
1815 instr(l_segments_dr.application_column_name(j),'ATTRIBUTE')+9)).column_name :=
1816 l_segments_dr.application_column_name(j);
1817 x_inv_serial_attributes(substr(l_segments_dr.application_column_name(j),
1818 instr(l_segments_dr.application_column_name(j),'ATTRIBUTE')+9)).column_type :='VARCHAR2';
1819 IF l_segments_dr.is_required(j) THEN
1820 x_inv_serial_attributes(substr(l_segments_dr.application_column_name(j),
1821 instr(l_segments_dr.application_column_name(j),'ATTRIBUTE')+9)).required :='TRUE';
1822 ELSE
1823 x_inv_serial_attributes(substr(l_segments_dr.application_column_name(j),
1824 instr(l_segments_dr.application_column_name(j),'ATTRIBUTE')+9)).required :='FALSE';
1825 END IF;
1826 x_inv_serial_attributes(substr(l_segments_dr.application_column_name(j),
1827 instr(l_segments_dr.application_column_name(j),'ATTRIBUTE')+9)).column_length :=150;
1828 x_inv_serial_attributes(substr(l_segments_dr.application_column_name(j),
1829 instr(l_segments_dr.application_column_name(j),'ATTRIBUTE')+9)).column_value :=
1830 l_inv_serial_attr(substr(l_segments_dr.application_column_name(j),
1831 instr(l_segments_dr.application_column_name(j),'ATTRIBUTE')+9));
1832 fnd_flex_descval.set_column_value(l_segments_dr.application_column_name(j),
1833 l_inv_serial_attr(substr(l_segments_dr.application_column_name(j),
1834 instr(l_segments_dr.application_column_name(j), 'ATTRIBUTE')+9)));
1835 END LOOP;
1836 l_context := NULL;
1837 l_nsegments := NULL;
1838
1839 IF p_attribute_category IS NOT NULL THEN
1840 debug('getting context specific segments', 'GET_INV_SERIAL_ATTR', 9);
1841 l_context := p_attribute_category;
1842 debug('setting context value ' || l_context, 'GET_INV_SERIAL_ATTR', 9);
1843 fnd_flex_descval.set_context_value(l_context);
1844
1845 /* Prepare the context_r type for getting the segments associated with the input context */
1846
1847 l_context_r.flexfield := l_dflex_r;
1848 l_context_r.context_code := l_context;
1849
1850 fnd_dflex.get_segments(CONTEXT => l_context_r, segments => l_segments_dr, enabled_only => TRUE);
1851 l_nsegments := l_segments_dr.nsegments;
1852 FOR i IN 1..l_nsegments LOOP
1853 debug('application column name is ' || l_segments_dr.application_column_name(i), 'GET_INV_SERIAL_ATTR', 9);
1854 x_inv_serial_attributes(substr(l_segments_dr.application_column_name(i),
1855 instr(l_segments_dr.application_column_name(i),'ATTRIBUTE')+9)).column_name :=
1856 l_segments_dr.application_column_name(i);
1857 x_inv_serial_attributes(substr(l_segments_dr.application_column_name(i),
1858 instr(l_segments_dr.application_column_name(i),'ATTRIBUTE')+9)).column_type :='VARCHAR2';
1859 IF l_segments_dr.is_required(i) THEN
1860 x_inv_serial_attributes(substr(l_segments_dr.application_column_name(i),
1861 instr(l_segments_dr.application_column_name(i),'ATTRIBUTE')+9)).required :='TRUE';
1862 ELSE
1863 x_inv_serial_attributes(substr(l_segments_dr.application_column_name(i),
1864 instr(l_segments_dr.application_column_name(i),'ATTRIBUTE')+9)).required :='FALSE';
1865 END IF;
1866 x_inv_serial_attributes(substr(l_segments_dr.application_column_name(i),
1867 instr(l_segments_dr.application_column_name(i),'ATTRIBUTE')+9)).column_length :=150;
1868 x_inv_serial_attributes(substr(l_segments_dr.application_column_name(i),
1869 instr(l_segments_dr.application_column_name(i),'ATTRIBUTE')+9)).column_value :=
1870 l_inv_serial_attr(substr(l_segments_dr.application_column_name(i),
1871 instr(l_segments_dr.application_column_name(i),'ATTRIBUTE')+9));
1872 fnd_flex_descval.set_column_value(l_segments_dr.application_column_name(i),
1873 l_inv_serial_attr(substr(l_segments_dr.application_column_name(i),
1874 instr(l_segments_dr.application_column_name(i), 'ATTRIBUTE')+9)));
1875 END LOOP;
1876 END IF;
1877
1878 IF ( p_attribute_Category IS NULL ) then
1879 l_context := l_contexts_dr.context_code(l_global_context);
1880 fnd_flex_descval.set_context_value(l_context);
1881 end if;
1882 debug('calling fnd_flex_descval.concatenated_values', 'GET_INV_SERIAL_ATTR', 9);
1883 IF fnd_flex_descval.validate_desccols(appl_short_name => 'INV',
1884 desc_flex_name => 'MTL_SERIAL_NUMBERS', values_or_ids => 'I' , validation_date => SYSDATE) THEN
1885 x_concatenated_values := fnd_flex_descval.concatenated_values;
1886 ELSE
1887
1888 x_concatenated_values := null;
1889 FND_MESSAGE.SET_NAME('INV', 'INV_FND_GENERIC_MSG');
1890 FND_MESSAGE.SET_TOKEN('MSG', fnd_flex_descval.error_message);
1891 FND_MSG_PUB.ADD;
1892 raise fnd_api.g_exc_unexpected_error;
1893 END IF;
1894 debug('after getting x_concatenated_values ' || x_concatenated_values, 'GET_INV_SERIAL_ATTR', 9);
1895 EXCEPTION
1896 when no_data_found THEN
1897 null;
1898 when FND_API.G_EXC_UNEXPECTED_ERROR THEN
1899 x_return_status := 'U';
1900 fnd_msg_pub.count_and_get(p_encoded => FND_API.G_FALSE, p_count => x_msg_count, p_data => x_msg_data);
1901 when others then
1902 x_return_status := 'U';
1903 fnd_msg_pub.count_and_get(p_encoded => FND_API.G_FALSE, p_count => x_msg_count, p_data => x_msg_data);
1904 END get_inv_serial_attributes;
1905
1906
1907 END INV_LOT_SEL_ATTR;