[Home] [Help]
PACKAGE BODY: APPS.WMS_ASN_LOT_ATT
Source
1 PACKAGE BODY WMS_ASN_LOT_ATT AS
2 /* $Header: WMSINTLB.pls 120.4 2005/10/17 10:38:04 methomas noship $ */
3
4 PROCEDURE print_debug(p_err_msg VARCHAR2,
5 p_level NUMBER)
6 IS
7 l_trace_on NUMBER := 0;
8 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
9 BEGIN
10
11 inv_mobile_helper_functions.tracelog
12 (p_err_msg => p_err_msg,
13 p_module => 'WMS_ASN_LOT_ATT',
14 p_level => p_level);
15
16
17 SELECT fnd_profile.value('INV_DEBUG_TRACE')
18 INTO l_trace_on
19 FROM dual;
20
21 IF l_trace_on = 1 THEN
22 FND_FILE.put_line(FND_FILE.LOG, 'WMS_ASN_LOT_ATT : ' || p_err_msg);
23 END IF;
24
25 --dbms_output.put_line('WMS_ASN_LOT_ATT -msg: '|| p_err_msg);
26
27 END print_debug;
28
29
30 procedure populatelotattributescolumn IS
31 l_column_idx BINARY_INTEGER;
32 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
33 BEGIN
34 g_lot_attributes_tbl(1).COLUMN_NAME := 'GRADE_CODE';
35 g_lot_attributes_tbl(1).COLUMN_TYPE := 'VARCHAR2';
36
37 g_lot_attributes_tbl(2).COLUMN_NAME := 'ORIGINATION_DATE';
38 g_lot_attributes_tbl(2).COLUMN_TYPE := 'DATE';
39
40 g_lot_attributes_tbl(3).COLUMN_NAME := 'DATE_CODE';
41 g_lot_attributes_tbl(3).COLUMN_TYPE := 'VARCHAR2';
42
43 g_lot_attributes_tbl(4).COLUMN_NAME := 'STATUS_ID';
44 g_lot_attributes_tbl(4).COLUMN_TYPE := 'NUMBER';
45
46 g_lot_attributes_tbl(5).COLUMN_NAME := 'CHANGE_DATE';
47 g_lot_attributes_tbl(5).COLUMN_TYPE := 'DATE';
48
49 g_lot_attributes_tbl(6).COLUMN_NAME := 'AGE';
50 g_lot_attributes_tbl(6).COLUMN_TYPE := 'NUMBER';
51
52 g_lot_attributes_tbl(7).COLUMN_NAME := 'RETEST_DATE';
53 g_lot_attributes_tbl(7).COLUMN_TYPE := 'DATE';
54
55 g_lot_attributes_tbl(8).COLUMN_NAME := 'MATURITY_DATE';
56 g_lot_attributes_tbl(8).COLUMN_TYPE := 'DATE';
57
58 g_lot_attributes_tbl(9).COLUMN_NAME := 'LOT_ATTRIBUTE_CATEGORY';
59 g_lot_attributes_tbl(9).COLUMN_TYPE := 'VARCHAR2';
60
61 g_lot_attributes_tbl(10).COLUMN_NAME := 'ITEM_SIZE';
62 g_lot_attributes_tbl(10).COLUMN_TYPE := 'NUMBER';
63
64 g_lot_attributes_tbl(11).COLUMN_NAME := 'COLOR';
65 g_lot_attributes_tbl(11).COLUMN_TYPE := 'VARCHAR2';
66
67 g_lot_attributes_tbl(12).COLUMN_NAME := 'VOLUME';
68 g_lot_attributes_tbl(12).COLUMN_TYPE := 'NUMBER';
69
70 g_lot_attributes_tbl(13).COLUMN_NAME := 'VOLUME_UOM';
71 g_lot_attributes_tbl(13).COLUMN_TYPE := 'VARCHAR2';
72
73 g_lot_attributes_tbl(14).COLUMN_NAME := 'PLACE_OF_ORIGIN';
74 g_lot_attributes_tbl(14).COLUMN_TYPE := 'VARCHAR2';
75
76 g_lot_attributes_tbl(15).COLUMN_NAME := 'BEST_BY_DATE';
77 g_lot_attributes_tbl(15).COLUMN_TYPE := 'DATE';
78
79 g_lot_attributes_tbl(16).COLUMN_NAME := 'LENGTH';
80 g_lot_attributes_tbl(16).COLUMN_TYPE := 'NUMBER';
81
82 g_lot_attributes_tbl(17).COLUMN_NAME := 'LENGTH_UOM';
83 g_lot_attributes_tbl(17).COLUMN_TYPE := 'VARCHAR2';
84
85 g_lot_attributes_tbl(18).COLUMN_NAME := 'RECYCLED_CONTENT';
86 g_lot_attributes_tbl(18).COLUMN_TYPE := 'NUMBER';
87
88 g_lot_attributes_tbl(19).COLUMN_NAME := 'THICKNESS';
89 g_lot_attributes_tbl(19).COLUMN_TYPE := 'NUMBER';
90
91 g_lot_attributes_tbl(20).COLUMN_NAME := 'THICKNESS_UOM';
92 g_lot_attributes_tbl(20).COLUMN_TYPE := 'VARCHAR2';
93
94 g_lot_attributes_tbl(21).COLUMN_NAME := 'WIDTH';
95 g_lot_attributes_tbl(21).COLUMN_TYPE := 'NUMBER';
96
97 g_lot_attributes_tbl(22).COLUMN_NAME := 'WIDTH_UOM';
98 g_lot_attributes_tbl(22).COLUMN_TYPE := 'VARCHAR2';
99
100 g_lot_attributes_tbl(23).COLUMN_NAME := 'CURL_WRINKLE_FOLD';
101 g_lot_attributes_tbl(23).COLUMN_TYPE := 'VARCHAR2';
102
103 g_lot_attributes_tbl(24).COLUMN_NAME := 'C_ATTRIBUTE1';
104 g_lot_attributes_tbl(24).COLUMN_TYPE := 'VARCHAR2';
105
106 g_lot_attributes_tbl(25).COLUMN_NAME := 'C_ATTRIBUTE2';
107 g_lot_attributes_tbl(25).COLUMN_TYPE := 'VARCHAR2';
108
109 g_lot_attributes_tbl(26).COLUMN_NAME := 'C_ATTRIBUTE3';
110 g_lot_attributes_tbl(26).COLUMN_TYPE := 'VARCHAR2';
111
112 g_lot_attributes_tbl(27).COLUMN_NAME := 'C_ATTRIBUTE4';
113 g_lot_attributes_tbl(27).COLUMN_TYPE := 'VARCHAR2';
114
115 g_lot_attributes_tbl(28).COLUMN_NAME := 'C_ATTRIBUTE5';
116 g_lot_attributes_tbl(28).COLUMN_TYPE := 'VARCHAR2';
117
118 g_lot_attributes_tbl(29).COLUMN_NAME := 'C_ATTRIBUTE6';
119 g_lot_attributes_tbl(29).COLUMN_TYPE := 'VARCHAR2';
120
121 g_lot_attributes_tbl(30).COLUMN_NAME := 'C_ATTRIBUTE7';
122 g_lot_attributes_tbl(30).COLUMN_TYPE := 'VARCHAR2';
123
124 g_lot_attributes_tbl(31).COLUMN_NAME := 'C_ATTRIBUTE8';
125 g_lot_attributes_tbl(31).COLUMN_TYPE := 'VARCHAR2';
126
127 g_lot_attributes_tbl(32).COLUMN_NAME := 'C_ATTRIBUTE9';
128 g_lot_attributes_tbl(32).COLUMN_TYPE := 'VARCHAR2';
129
130 g_lot_attributes_tbl(33).COLUMN_NAME := 'C_ATTRIBUTE10';
131 g_lot_attributes_tbl(33).COLUMN_TYPE := 'VARCHAR2';
132
133 g_lot_attributes_tbl(34).COLUMN_NAME := 'C_ATTRIBUTE11';
134 g_lot_attributes_tbl(34).COLUMN_TYPE := 'VARCHAR2';
135
136 g_lot_attributes_tbl(35).COLUMN_NAME := 'C_ATTRIBUTE12';
137 g_lot_attributes_tbl(35).COLUMN_TYPE := 'VARCHAR2';
138
139 g_lot_attributes_tbl(36).COLUMN_NAME := 'C_ATTRIBUTE13';
140 g_lot_attributes_tbl(36).COLUMN_TYPE := 'VARCHAR2';
141
142 g_lot_attributes_tbl(37).COLUMN_NAME := 'C_ATTRIBUTE14';
143 g_lot_attributes_tbl(37).COLUMN_TYPE := 'VARCHAR2';
144
145 g_lot_attributes_tbl(38).COLUMN_NAME := 'C_ATTRIBUTE15';
146 g_lot_attributes_tbl(38).COLUMN_TYPE := 'VARCHAR2';
147
148 g_lot_attributes_tbl(39).COLUMN_NAME := 'C_ATTRIBUTE16';
149 g_lot_attributes_tbl(39).COLUMN_TYPE := 'VARCHAR2';
150
151 g_lot_attributes_tbl(40).COLUMN_NAME := 'C_ATTRIBUTE17';
152 g_lot_attributes_tbl(40).COLUMN_TYPE := 'VARCHAR2';
153
154 g_lot_attributes_tbl(41).COLUMN_NAME := 'C_ATTRIBUTE18';
155 g_lot_attributes_tbl(41).COLUMN_TYPE := 'VARCHAR2';
156
157 g_lot_attributes_tbl(42).COLUMN_NAME := 'C_ATTRIBUTE19';
158 g_lot_attributes_tbl(42).COLUMN_TYPE := 'VARCHAR2';
159
160 g_lot_attributes_tbl(43).COLUMN_NAME := 'C_ATTRIBUTE20';
161 g_lot_attributes_tbl(43).COLUMN_TYPE := 'VARCHAR2';
162
163 g_lot_attributes_tbl(44).COLUMN_NAME := 'D_ATTRIBUTE1';
164 g_lot_attributes_tbl(44).COLUMN_TYPE := 'DATE';
165
166 g_lot_attributes_tbl(45).COLUMN_NAME := 'D_ATTRIBUTE2';
167 g_lot_attributes_tbl(45).COLUMN_TYPE := 'DATE';
168
169 g_lot_attributes_tbl(46).COLUMN_NAME := 'D_ATTRIBUTE3';
170 g_lot_attributes_tbl(46).COLUMN_TYPE := 'DATE';
171
172 g_lot_attributes_tbl(47).COLUMN_NAME := 'D_ATTRIBUTE4';
173 g_lot_attributes_tbl(47).COLUMN_TYPE := 'DATE';
174
175 g_lot_attributes_tbl(48).COLUMN_NAME := 'D_ATTRIBUTE5';
176 g_lot_attributes_tbl(48).COLUMN_TYPE := 'DATE';
177
178 g_lot_attributes_tbl(49).COLUMN_NAME := 'D_ATTRIBUTE6';
179 g_lot_attributes_tbl(49).COLUMN_TYPE := 'DATE';
180
181 g_lot_attributes_tbl(50).COLUMN_NAME := 'D_ATTRIBUTE7';
182 g_lot_attributes_tbl(50).COLUMN_TYPE := 'DATE';
183
184 g_lot_attributes_tbl(51).COLUMN_NAME := 'D_ATTRIBUTE8';
185 g_lot_attributes_tbl(51).COLUMN_TYPE := 'DATE';
186
187 g_lot_attributes_tbl(52).COLUMN_NAME := 'D_ATTRIBUTE9';
188 g_lot_attributes_tbl(52).COLUMN_TYPE := 'DATE';
189
190 g_lot_attributes_tbl(53).COLUMN_NAME := 'D_ATTRIBUTE10';
191 g_lot_attributes_tbl(53).COLUMN_TYPE := 'DATE';
192
193 g_lot_attributes_tbl(54).COLUMN_NAME := 'N_ATTRIBUTE1';
194 g_lot_attributes_tbl(54).COLUMN_TYPE := 'NUMBER';
195
196 g_lot_attributes_tbl(55).COLUMN_NAME := 'N_ATTRIBUTE2';
197 g_lot_attributes_tbl(55).COLUMN_TYPE := 'NUMBER';
198
199 g_lot_attributes_tbl(56).COLUMN_NAME := 'N_ATTRIBUTE3';
200 g_lot_attributes_tbl(56).COLUMN_TYPE := 'NUMBER';
201
202 g_lot_attributes_tbl(57).COLUMN_NAME := 'N_ATTRIBUTE4';
203 g_lot_attributes_tbl(57).COLUMN_TYPE := 'NUMBER';
204
205 g_lot_attributes_tbl(58).COLUMN_NAME := 'N_ATTRIBUTE5';
206 g_lot_attributes_tbl(58).COLUMN_TYPE := 'NUMBER';
207
208 g_lot_attributes_tbl(59).COLUMN_NAME := 'N_ATTRIBUTE6';
209 g_lot_attributes_tbl(59).COLUMN_TYPE := 'NUMBER';
210
211 g_lot_attributes_tbl(60).COLUMN_NAME := 'N_ATTRIBUTE7';
212 g_lot_attributes_tbl(60).COLUMN_TYPE := 'NUMBER';
213
214 g_lot_attributes_tbl(61).COLUMN_NAME := 'N_ATTRIBUTE8';
215 g_lot_attributes_tbl(61).COLUMN_TYPE := 'NUMBER';
216
217 g_lot_attributes_tbl(62).COLUMN_NAME := 'N_ATTRIBUTE10';
218 g_lot_attributes_tbl(62).COLUMN_TYPE := 'NUMBER';
219
220 g_lot_attributes_tbl(63).COLUMN_NAME := 'SUPPLIER_LOT_NUMBER';
221 g_lot_attributes_tbl(63).COLUMN_TYPE := 'VARCHAR2';
222
223 g_lot_attributes_tbl(64).COLUMN_NAME := 'N_ATTRIBUTE9';
224 g_lot_attributes_tbl(64).COLUMN_TYPE := 'NUMBER';
225
226 g_lot_attributes_tbl(65).COLUMN_NAME := 'TERRITORY_CODE';
227 g_lot_attributes_tbl(65).COLUMN_TYPE := 'VARCHAR2';
228 END;
229
230
231 procedure validate_lot (
232 x_return_status OUT NOCOPY VARCHAR2
233 , x_msg_count OUT NOCOPY NUMBER
234 , x_msg_data OUT NOCOPY VARCHAR2
235 , p_interface_transaction_id IN NUMBER
236 ) IS
237
238 CURSOR c_rcv_txn_interface_rec IS
239 SELECT group_id,
240 to_organization_id,
241 item_id,
242 item_revision,
243 shipment_header_id,
244 po_line_id,
245 quantity,
246 unit_of_measure,
247 uom_code,
248 header_interface_id
249 FROM rcv_transactions_interface
250 WHERE interface_transaction_id = p_interface_transaction_id;
251
252
253
254 CURSOR c_lot_cursor IS
255 select GRADE_CODE
256 , fnd_date.date_to_canonical(ORIGINATION_DATE)
257 , DATE_CODE
258 , to_char(STATUS_ID)
259 , fnd_date.date_to_canonical(CHANGE_DATE)
260 , to_number(AGE)
261 , fnd_date.date_to_canonical(RETEST_DATE)
262 , fnd_date.date_to_canonical(MATURITY_DATE)
263 , LOT_ATTRIBUTE_CATEGORY
264 , to_char(ITEM_SIZE)
265 , COLOR
266 , to_char(VOLUME )
267 , VOLUME_UOM_CODE
268 , PLACE_OF_ORIGIN
269 , fnd_date.date_to_canonical(BEST_BY_DATE )
270 , to_char(LENGTH )
271 , LENGTH_UOM_CODE
272 , to_char(RECYCLED_CONTENT )
273 , to_char(THICKNESS )
274 , THICKNESS_UOM_CODE
275 , to_char(WIDTH )
276 , WIDTH_UOM_CODE
277 , CURL_WRINKLE_FOLD
278 , C_ATTRIBUTE1
279 , C_ATTRIBUTE2
280 , C_ATTRIBUTE3
281 , C_ATTRIBUTE4
282 , C_ATTRIBUTE5
283 , C_ATTRIBUTE6
284 , C_ATTRIBUTE7
285 , C_ATTRIBUTE8
286 , C_ATTRIBUTE9
287 , C_ATTRIBUTE10
288 , C_ATTRIBUTE11
289 , C_ATTRIBUTE12
290 , C_ATTRIBUTE13
291 , C_ATTRIBUTE14
292 , C_ATTRIBUTE15
293 , C_ATTRIBUTE16
294 , C_ATTRIBUTE17
295 , C_ATTRIBUTE18
296 , C_ATTRIBUTE19
297 , C_ATTRIBUTE20
298 , fnd_date.date_to_canonical(D_ATTRIBUTE1)
299 , fnd_date.date_to_canonical(D_ATTRIBUTE2)
300 , fnd_date.date_to_canonical(D_ATTRIBUTE3)
301 , fnd_date.date_to_canonical(D_ATTRIBUTE4)
302 , fnd_date.date_to_canonical(D_ATTRIBUTE5)
303 , fnd_date.date_to_canonical(D_ATTRIBUTE6)
304 , fnd_date.date_to_canonical(D_ATTRIBUTE7)
305 , fnd_date.date_to_canonical(D_ATTRIBUTE8)
306 , fnd_date.date_to_canonical(D_ATTRIBUTE9)
307 , fnd_date.date_to_canonical(D_ATTRIBUTE10)
308 , to_char(N_ATTRIBUTE1)
309 , to_char(N_ATTRIBUTE2)
310 , to_char(N_ATTRIBUTE3)
311 , to_char(N_ATTRIBUTE4)
312 , to_char(N_ATTRIBUTE5)
313 , to_char(N_ATTRIBUTE6)
314 , to_char(N_ATTRIBUTE7)
315 , to_char(N_ATTRIBUTE8)
316 , to_char(N_ATTRIBUTE10)
317 , SUPPLIER_LOT_NUMBER
318 , to_char(N_ATTRIBUTE9)
319 , territory_code
320 , STATUS_NAME
321 , EXPIRATION_DATE
322 , LOT_NUMBER
323 -- other columns
324 , ATTRIBUTE_CATEGORY
325 , ATTRIBUTE1
326 , ATTRIBUTE2
327 , ATTRIBUTE3
328 , ATTRIBUTE4
329 , ATTRIBUTE5
330 , ATTRIBUTE6
331 , ATTRIBUTE7
332 , ATTRIBUTE8
333 , ATTRIBUTE9
334 , ATTRIBUTE10
335 , ATTRIBUTE11
336 , ATTRIBUTE12
337 , ATTRIBUTE13
338 , ATTRIBUTE14
339 , ATTRIBUTE15
340 from wms_lpn_contents_interface
341 WHERE interface_transaction_id = p_interface_transaction_id
342 AND rownum = 1 ; --Bug#4437403.
343
344
345 l_rcv_txn_interface_rec c_rcv_txn_interface_rec%ROWTYPE;
346 l_organization_id NUMBER;
347 l_inventory_item_id NUMBER;
348 x_context_value mtl_flex_context.descriptive_flex_context_code%type;
349 l_attributes_name VARCHAR2(50) := 'Lot Attributes';
350 v_flexfield fnd_dflex.dflex_r;
351 v_flexinfo fnd_dflex.dflex_dr;
352 v_contexts fnd_dflex.contexts_dr;
353 v_segments fnd_dflex.segments_dr;
354 l_return_status VARCHAR2(1);
355 l_status BOOLEAN;
356 l_progress varchar2(100);
357 v_colname varchar2(50);
358 l_status_name varchar2(30);
359 l_expiration_date date;
360 l_object_id number;
361
362 l_lot_control_code NUMBER;
363 l_lotunique NUMBER;
364 l_lotcount NUMBER;
365 l_userid NUMBER;
366 l_loginid NUMBER;
367 l_shelf_life_code NUMBER;
368 l_shelf_life_days NUMBER;
369 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
370 l_lot_number varchar2(80);
371
372 l_attributes_default INV_LOT_SEL_ATTR.LOT_SEL_ATTRIBUTES_TBL_TYPE;
373 l_attributes_default_count NUMBER;
374 l_attributes_in INV_LOT_SEL_ATTR.LOT_SEL_ATTRIBUTES_TBL_TYPE;
375
376
377 l_lot_status_enabled VARCHAR2(1);
378 l_default_lot_status_id NUMBER := NULL;
379 l_lot_status_id NUMBER := NULL;
380 l_serial_status_enabled VARCHAR2(1);
381 l_default_serial_status_id NUMBER;
382 l_status_rec INV_MATERIAL_STATUS_PUB.mtl_status_update_rec_type;
383
384 l_input_idx BINARY_INTEGER;
385 l_msg_count number;
386
387
388 l_ATTRIBUTE_CATEGORY VARCHAR2(30) ;
389 l_ATTRIBUTE1 VARCHAR2(150) ;
390 l_ATTRIBUTE2 VARCHAR2(150) ;
391 l_ATTRIBUTE3 VARCHAR2(150) ;
392 l_ATTRIBUTE4 VARCHAR2(150) ;
393 l_ATTRIBUTE5 VARCHAR2(150) ;
394 l_ATTRIBUTE6 VARCHAR2(150) ;
395 l_ATTRIBUTE7 VARCHAR2(150) ;
396 l_ATTRIBUTE8 VARCHAR2(150) ;
397 l_ATTRIBUTE9 VARCHAR2(150) ;
401 l_ATTRIBUTE13 VARCHAR2(150) ;
398 l_ATTRIBUTE10 VARCHAR2(150) ;
399 l_ATTRIBUTE11 VARCHAR2(150) ;
400 l_ATTRIBUTE12 VARCHAR2(150) ;
402 l_ATTRIBUTE14 VARCHAR2(150) ;
403 l_ATTRIBUTE15 VARCHAR2(150) ;
404
405
406
407
408 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
409 BEGIN
410
411 l_progress := '10';
412
413 IF (l_debug = 1) THEN
414 print_debug('Inside Validate Procedure at step '|| l_progress, 4);
415 END IF;
416
417 SAVEPOINT InsertLot_sv;
418
419 -- Initialize return status to success
420 x_return_status := FND_API.G_RET_STS_SUCCESS;
421
422 l_progress := '10';
423
424 OPEN c_rcv_txn_interface_rec;
425 FETCH c_rcv_txn_interface_rec INTO l_rcv_txn_interface_rec;
426
427 IF c_rcv_txn_interface_rec%NOTFOUND THEN
428 CLOSE c_rcv_txn_interface_rec;
429 IF (l_debug = 1) THEN
430 print_debug('No record exists in RCV_TRANSACTIONS_INTERFACE for this interface_transaction_ID', 4);
431 END IF;
432 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
433 END IF;
434
435 CLOSE c_rcv_txn_interface_rec;
436
437 --Get The Values from rti.
438
439 l_organization_id := l_rcv_txn_interface_rec.to_organization_id;
440 l_inventory_item_id := l_rcv_txn_interface_rec.item_id;
441
442 l_progress := '20';
443 IF (l_debug = 1) THEN
444 print_debug('Inside Validate Procedure at step '|| l_progress, 4);
445 END IF;
446
447 --Get The Values for Lot Related Attributes
448
449 select mtl_gen_object_id_s.nextval into l_object_id from dual;
450
451 populatelotattributescolumn;
452
453 l_progress := '30';
454 IF (l_debug = 1) THEN
455 print_debug('Inside Validate Procedure After Calling populateattributecolumns ', 4);
456 END IF;
457
458
459 open c_lot_cursor;
460 LOOP
461
462 l_progress := '40';
463 IF (l_debug = 1) THEN
464 print_debug('Validate Procedure after opening Lot cursor ', 4);
465 END IF;
466
467 fetch c_lot_cursor into g_lot_attributes_tbl(1).column_value, g_lot_attributes_tbl(2).column_value,
468 g_lot_attributes_tbl(3).column_value, g_lot_attributes_tbl(4).column_value,
469 g_lot_attributes_tbl(5).column_value, g_lot_attributes_tbl(6).column_value,
470 g_lot_attributes_tbl(7).column_value, g_lot_attributes_tbl(8).column_value,
471 g_lot_attributes_tbl(9).column_value, g_lot_attributes_tbl(10).column_value,
472 g_lot_attributes_tbl(11).column_value, g_lot_attributes_tbl(12).column_value,
473 g_lot_attributes_tbl(13).column_value, g_lot_attributes_tbl(14).column_value,
474 g_lot_attributes_tbl(15).column_value, g_lot_attributes_tbl(16).column_value,
475 g_lot_attributes_tbl(17).column_value, g_lot_attributes_tbl(18).column_value,
476 g_lot_attributes_tbl(19).column_value, g_lot_attributes_tbl(20).column_value,
477 g_lot_attributes_tbl(21).column_value, g_lot_attributes_tbl(22).column_value,
478 g_lot_attributes_tbl(23).column_value, g_lot_attributes_tbl(24).column_value,
479 g_lot_attributes_tbl(25).column_value, g_lot_attributes_tbl(26).column_value,
480 g_lot_attributes_tbl(27).column_value, g_lot_attributes_tbl(28).column_value,
481 g_lot_attributes_tbl(29).column_value, g_lot_attributes_tbl(30).column_value,
482 g_lot_attributes_tbl(31).column_value, g_lot_attributes_tbl(32).column_value,
483 g_lot_attributes_tbl(33).column_value, g_lot_attributes_tbl(34).column_value,
484 g_lot_attributes_tbl(35).column_value, g_lot_attributes_tbl(36).column_value,
485 g_lot_attributes_tbl(37).column_value, g_lot_attributes_tbl(38).column_value,
486 g_lot_attributes_tbl(39).column_value, g_lot_attributes_tbl(40).column_value,
487 g_lot_attributes_tbl(41).column_value, g_lot_attributes_tbl(42).column_value,
488 g_lot_attributes_tbl(43).column_value, g_lot_attributes_tbl(44).column_value,
489 g_lot_attributes_tbl(45).column_value, g_lot_attributes_tbl(46).column_value,
490 g_lot_attributes_tbl(47).column_value, g_lot_attributes_tbl(48).column_value,
491 g_lot_attributes_tbl(49).column_value, g_lot_attributes_tbl(50).column_value,
492 g_lot_attributes_tbl(51).column_value, g_lot_attributes_tbl(52).column_value,
493 g_lot_attributes_tbl(53).column_value, g_lot_attributes_tbl(54).column_value,
494 g_lot_attributes_tbl(55).column_value, g_lot_attributes_tbl(56).column_value,
495 g_lot_attributes_tbl(57).column_value, g_lot_attributes_tbl(58).column_value,
496 g_lot_attributes_tbl(59).column_value, g_lot_attributes_tbl(60).column_value,
497 g_lot_attributes_tbl(61).column_value, g_lot_attributes_tbl(62).column_value,
498 g_lot_attributes_tbl(63).column_value, g_lot_attributes_tbl(64).column_value,
499 g_lot_attributes_tbl(65).column_value,
500 l_status_name, l_expiration_date, l_lot_number
501 , l_ATTRIBUTE_CATEGORY
502 , l_ATTRIBUTE1
503 , l_ATTRIBUTE2
504 , l_ATTRIBUTE3
505 , l_ATTRIBUTE4
506 , l_ATTRIBUTE5
507 , l_ATTRIBUTE6
508 , l_ATTRIBUTE7
512 , l_ATTRIBUTE11
509 , l_ATTRIBUTE8
510 , l_ATTRIBUTE9
511 , l_ATTRIBUTE10
513 , l_ATTRIBUTE12
514 , l_ATTRIBUTE13
515 , l_ATTRIBUTE14
516 , l_ATTRIBUTE15
517 ;
518 exit when c_lot_cursor%NOTFOUND;
519 end loop;
520 close c_lot_cursor;
521
522 -- Check for Lot UniqueNess
523 BEGIN
524 SELECT LOT_CONTROL_CODE
525 INTO l_lot_control_code
526 FROM MTL_SYSTEM_ITEMS
527 WHERE INVENTORY_ITEM_ID = l_inventory_item_id
528 AND ORGANIZATION_ID = l_organization_id;
529
530 if(l_lot_control_code = 1) then
531 fnd_message.set_name('INV','INV_NO_LOT_CONTROL');
532 fnd_msg_pub.add;
533 x_return_status := FND_API.G_RET_STS_ERROR;
534 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
535 end if;
536 EXCEPTION
537 WHEN NO_DATA_FOUND THEN
538 fnd_message.set_name('INV','INV_INVALID_ITEM');
539 fnd_msg_pub.add;
540 x_return_status := FND_API.G_RET_STS_ERROR;
541 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
542 END;
543
544 l_progress := '40.1';
545
546 SELECT LOT_NUMBER_UNIQUENESS
547 INTO l_lotunique
548 FROM MTL_PARAMETERS
549 WHERE ORGANIZATION_ID = l_organization_id;
550
551 if(l_lotunique = 1) then
552 SELECT count(1)
553 INTO l_lotcount
554 FROM MTL_LOT_NUMBERS
555 WHERE inventory_item_id <> l_inventory_item_id
556 AND lot_number = l_lot_number;
557
558 if(l_lotcount > 0) then
559 fnd_message.set_name('INV','INV_INT_LOTUNIQEXP');
560 fnd_msg_pub.add;
561 x_return_status := FND_API.G_RET_STS_ERROR;
562 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
563 end if;
564 end if;
565
566 SELECT count(1)
567 INTO l_lotcount
568 FROM MTL_LOT_NUMBERS
569 WHERE INVENTORY_ITEM_ID = l_inventory_item_id
570 AND ORGANIZATION_ID = l_organization_id
571 AND LOT_NUMBER = l_lot_number;
572
573 if(l_lotcount = 0) then
574 l_userid := fnd_global.user_id;
575 l_loginid := fnd_global.login_id;
576 else
577 fnd_message.set_name('INV','INV_INT_LOTUNIQEXP');
578 fnd_msg_pub.add;
579 x_return_status := FND_API.G_RET_STS_ERROR;
580 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
581 -- Stop PRocessing HERE
582 end if;
583
584
585 l_progress := '40.2';
586
587 if(l_expiration_date IS NULL) then
588 SELECT SHELF_LIFE_CODE,
589 SHELF_LIFE_DAYS
590 INTO l_shelf_life_code,
591 l_shelf_life_days
592 FROM MTL_SYSTEM_ITEMS
593 WHERE INVENTORY_ITEM_ID = l_inventory_item_id
594 AND ORGANIZATION_ID = l_organization_id;
595
596 if(l_shelf_life_code = 2) then
597 SELECT SYSDATE + l_shelf_life_days
598 INTO l_expiration_date
599 FROM DUAL;
600 elsif(l_shelf_life_code = 4) then
601 fnd_message.set_name('INV','INV_LOT_EXPREQD');
602 fnd_msg_pub.add;
603 x_return_status := FND_API.G_RET_STS_ERROR;
604 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
605 end if;
606 end if;
607
608 --Get All The Not Null vAlues and put in a temp area
609 l_input_idx := 0;
610 for x in 1..65 LOOP
611 if( g_lot_attributes_tbl(x).column_value is not null ) then
612 l_input_idx := l_input_idx + 1;
613 l_attributes_in(l_input_idx).column_name := g_lot_attributes_tbl(x).column_name;
614 l_attributes_in(l_input_idx).column_value := g_lot_attributes_tbl(x).column_value;
615 l_attributes_in(l_input_idx).column_type := g_lot_attributes_tbl(x).column_type;
616 end if;
617 end loop;
618
619 if( inv_install.adv_inv_installed(null) = true )then
620 inv_lot_sel_attr.get_default(
621 x_attributes_default => l_attributes_default,
622 x_attributes_default_count => l_attributes_default_count,
623 x_return_status => l_return_status,
624 x_msg_count => l_msg_count,
625 x_msg_data => x_msg_data,
626 p_table_name => 'MTL_LOT_NUMBERS',
627 p_attributes_name => 'Lot Attributes',
628 p_inventory_item_id => l_inventory_item_id,
629 p_organization_id => l_organization_id,
630 p_lot_serial_number => l_lot_number,
631 p_attributes => l_attributes_in);
632
633 if( l_return_status <> FND_API.G_RET_STS_SUCCESS ) then
634 x_return_status := l_return_status;
635 raise FND_API.G_EXC_UNEXPECTED_ERROR;
636 end if;
637
638 if( l_attributes_default_count > 0 ) then
639 for i in 1..l_attributes_default_count LOOP
640 for j in 1..g_lot_attributes_tbl.count LOOP
641 if( upper(l_attributes_default(i).COLUMN_NAME) = upper(g_lot_attributes_tbl(j).COLUMN_NAME) ) then
645 end loop;
642 g_lot_attributes_tbl(j).COLUMN_VALUE := l_attributes_default(i).COLUMN_VALUE;
643 end if;
644 exit when (upper(l_attributes_default(i).COLUMN_NAME) = upper(g_lot_attributes_tbl(j).COLUMN_NAME));
646 end loop;
647 end if;
648 end if;
649
650
651 -- Get the default Status Id
652
653 INV_MATERIAL_STATUS_GRP.get_lot_serial_status_control(
654 p_organization_id => l_organization_id
655 , p_inventory_item_id => l_inventory_item_id
656 , x_return_status => l_return_status
657 , x_msg_count => l_msg_count
658 , x_msg_data => x_msg_data
659 , x_lot_status_enabled => l_lot_status_enabled
660 , x_default_lot_status_id => l_default_lot_status_id
661 , x_serial_status_enabled => l_serial_status_enabled
662 , x_default_serial_status_id => l_default_serial_status_id);
663
664
665 if( l_return_status <> FND_API.G_RET_STS_SUCCESS ) then
666 x_return_status := l_return_status;
667 raise FND_API.G_EXC_UNEXPECTED_ERROR;
668 end if;
669
670 if(NVL(l_lot_status_enabled, 'Y') = 'Y') then
671 -- For consistency, fill after converting to 'char'
672 g_lot_attributes_tbl(4).COLUMN_VALUE := to_char(l_default_lot_status_id);
673
674 -- This will be overwritten if something is specified in STATUS_NAME
675 Begin
676 if l_status_name is not null then
677 select status_id
678 into l_lot_status_id
679 from mtl_material_statuses_vl
680 where status_code = l_status_name
681 ;
682 g_lot_attributes_tbl(4).COLUMN_VALUE := to_char(l_lot_status_id);
683 end if;
684 Exception
685 When others then null;
686 End;
687 End if;
688
689 l_progress := '50';
690 IF (l_debug = 1) THEN
691 print_debug('Getting All the context Codes '||' Progress ='|| l_progress , 4);
692 END IF;
693
694 -- Get the Context Code for this Item First
695
696 -- Get flexfield
697 fnd_dflex.get_flexfield('INV', l_attributes_name, v_flexfield, v_flexinfo);
698
699 -- Get Contexts
700 fnd_dflex.get_contexts(v_flexfield, v_contexts);
701
702
703 l_progress := '60';
704 IF (l_debug = 1) THEN
705 print_debug('Number of contexts Found '||v_contexts.ncontexts , 4);
706 END IF;
707
708
709 l_progress := '70';
710
711 -- First Check whether context is present in the interface
712 -- row, if not then get the context code
713 ---
714
715 if g_lot_attributes_tbl(9).column_value is null then
716 inv_lot_sel_attr.get_context_code(x_context_value,
717 l_organization_id,l_inventory_item_id,l_attributes_name);
718 g_lot_attributes_tbl(9).column_value := x_context_value;
719
720 IF (l_debug = 1) THEN
721 print_debug('No context set in interface record Setting context to '||x_context_value, 4);
722 END IF;
723 else
724 x_context_value := g_lot_attributes_tbl(9).column_value;
725 IF (l_debug = 1) THEN
726 print_debug('Context set in interface record value = '||x_context_value, 4);
727 END IF;
728 end if;
729
730
731 -- Set the Context Code for validating the Lot Attributes.
732 l_progress := '80';
733
734
735 -- SKIP THE VALIDATION IF CONTEXT FOUND IS NULL
736
737 if x_context_value is not null
738 then
739
740 fnd_flex_descval.set_context_value(x_context_value);
741 fnd_flex_descval.clear_column_values;
742 fnd_flex_descval.set_column_value('LOT_ATTRIBUTE_CATEGORY',
743 g_lot_attributes_tbl(9).column_value);
744
745 IF (l_debug = 1) THEN
746 print_debug('After Setting the context Code for validation '||' Progress ='|| l_progress , 4);
747 END IF;
748
749
750 -- Setting the Values for Validating
751
752 FOR i IN 1..v_contexts.ncontexts LOOP
753
754 IF (l_debug = 1) THEN
755 print_debug('cheking for context Code '||v_contexts.context_code(i) , 4);
756 END IF;
757
758 IF(v_contexts.is_enabled(i) AND
759 ((UPPER(v_contexts.context_code(i)) = UPPER(x_context_value)) OR
760 v_contexts.is_global(i))
761 ) THEN
762 -- Get segments
763 fnd_dflex.get_segments(fnd_dflex.make_context(v_flexfield,
764 v_contexts.context_code(i)),
765 v_segments, TRUE);
766
767 <<segmentLoop>>
768 FOR j IN 1..v_segments.nsegments LOOP
769 IF v_segments.is_enabled(j) THEN
770 v_colName := v_segments.application_column_name(j);
771 <<columnLoop>>
772 FOR k IN 1..g_lot_attributes_tbl.count() LOOP
773 IF UPPER(v_colName) = UPPER(g_lot_attributes_tbl(k).column_name) THEN
774
775 -- Sets the Values for Validation
776
777 -- Setting the column data type for validation
778 if g_lot_attributes_tbl(k).column_type = 'DATE' then
782 fnd_flex_descval.set_column_value(g_lot_attributes_tbl(k).column_name,
779 IF (l_debug = 1) THEN
780 print_debug('Setting the columns for validation -- Date' , 4);
781 END IF;
783 fnd_date.canonical_to_date(g_lot_attributes_tbl(k).column_value));
784 end if;
785
786 if g_lot_attributes_tbl(k).column_type = 'NUMBER' then
787 IF (l_debug = 1) THEN
788 print_debug('Setting the columns for validation -- Number' , 4);
789 END IF;
790 fnd_flex_descval.set_column_value(g_lot_attributes_tbl(k).column_name,
791 to_number(g_lot_attributes_tbl(k).column_value));
792 end if;
793
794 if g_lot_attributes_tbl(k).column_type = 'VARCHAR2' then
795 IF (l_debug = 1) THEN
796 print_debug('Setting the columns for validation -- Varchar2' , 4);
797 END IF;
798 fnd_flex_descval.set_column_value(g_lot_attributes_tbl(k).column_name,
799 g_lot_attributes_tbl(k).column_value);
800 end if;
801
802 IF (l_debug = 1) THEN
803 print_debug('Setting the columns for validation ' , 4);
804 print_debug('column Name '|| g_lot_attributes_tbl(k).column_name , 4);
805 print_debug('column Value '||g_lot_attributes_tbl(k).column_value , 4);
806 END IF;
807
808 --print_debug('Calling the Validation API ' , 4);
809
810 EXIT columnLoop; -- found column
811 END IF;
812 END LOOP columnLoop;
813
814 END IF;
815 END LOOP segmentLoop;
816 END IF;
817 END LOOP contextLoop;
818
819
820 -- Call the validating routine for Lot Attributes.
821
822 l_progress := '90';
823 IF (l_debug = 1) THEN
824 print_debug('Before Calling The API for validation ' , 4);
825 END IF;
826
827 l_status := fnd_flex_descval.validate_desccols(
828 appl_short_name => 'INV',
829 desc_flex_name => l_attributes_name);
830
831 if l_status = TRUE then
832 IF (l_debug = 1) THEN
833 print_debug('API for validation is successfull' , 4);
834 END IF;
835 null;
836 else
837 IF (l_debug = 1) THEN
838 print_debug('API for validation is failure' , 4);
839 print_debug('Error Messages '|| fnd_flex_descval.error_message , 4);
840 END IF;
841 x_return_status := FND_API.G_RET_STS_ERROR ;
842 x_msg_data := fnd_flex_descval.error_message;
843 raise FND_API.G_EXC_ERROR;
844 end if;
845
846 l_progress := '100';
847 --
848 -- Else Insert the Row into mtl_lot_number
849 --
850 --
851
852
853 end if;
854
855 --
856 -- END OF VALIDATION
857 --
858 --
859
860
861 l_progress := '110';
862 IF (l_debug = 1) THEN
863 print_debug('Before Inserting into MTL_LOT_NUMBERS ' , 4);
864 END IF;
865
866 INSERT INTO MTL_LOT_NUMBERS
867 (INVENTORY_ITEM_ID,
868 ORGANIZATION_ID,
869 LOT_NUMBER,
870 LAST_UPDATE_DATE,
871 LAST_UPDATED_BY,
872 CREATION_DATE,
873 CREATED_BY,
874 LAST_UPDATE_LOGIN,
875 EXPIRATION_DATE,
876 DISABLE_FLAG,
877 ATTRIBUTE_CATEGORY,
878 ATTRIBUTE1,
879 ATTRIBUTE2,
880 ATTRIBUTE3,
881 ATTRIBUTE4,
882 ATTRIBUTE5,
883 ATTRIBUTE6,
884 ATTRIBUTE7,
885 ATTRIBUTE8,
886 ATTRIBUTE9,
887 ATTRIBUTE10,
888 ATTRIBUTE11,
889 ATTRIBUTE12,
890 ATTRIBUTE13,
891 ATTRIBUTE14,
892 ATTRIBUTE15,
893 REQUEST_ID,
894 PROGRAM_APPLICATION_ID,
895 PROGRAM_ID,
896 PROGRAM_UPDATE_DATE,
897 GEN_OBJECT_ID,
898 DESCRIPTION,
899 VENDOR_ID,
900 GRADE_CODE,
901 ORIGINATION_DATE,
902 DATE_CODE,
903 STATUS_ID,
904 CHANGE_DATE,
905 AGE,
906 RETEST_DATE,
907 MATURITY_DATE,
908 LOT_ATTRIBUTE_CATEGORY,
909 ITEM_SIZE,
910 COLOR,
911 VOLUME,
912 VOLUME_UOM,
913 PLACE_OF_ORIGIN,
914 BEST_BY_DATE,
915 LENGTH,
916 LENGTH_UOM,
917 RECYCLED_CONTENT,
918 THICKNESS,
919 THICKNESS_UOM,
920 WIDTH,
921 WIDTH_UOM,
922 CURL_WRINKLE_FOLD,
923 C_ATTRIBUTE1,
924 C_ATTRIBUTE2,
925 C_ATTRIBUTE3,
926 C_ATTRIBUTE4,
927 C_ATTRIBUTE5,
928 C_ATTRIBUTE6,
932 C_ATTRIBUTE10,
929 C_ATTRIBUTE7,
930 C_ATTRIBUTE8,
931 C_ATTRIBUTE9,
933 C_ATTRIBUTE11,
934 C_ATTRIBUTE12,
935 C_ATTRIBUTE13,
936 C_ATTRIBUTE14,
937 C_ATTRIBUTE15,
938 C_ATTRIBUTE16,
939 C_ATTRIBUTE17,
940 C_ATTRIBUTE18,
941 C_ATTRIBUTE19,
942 C_ATTRIBUTE20,
943 D_ATTRIBUTE1,
944 D_ATTRIBUTE2,
945 D_ATTRIBUTE3,
946 D_ATTRIBUTE4,
947 D_ATTRIBUTE5,
948 D_ATTRIBUTE6,
949 D_ATTRIBUTE7,
950 D_ATTRIBUTE8,
951 D_ATTRIBUTE9,
952 D_ATTRIBUTE10,
953 N_ATTRIBUTE1,
954 N_ATTRIBUTE2,
955 N_ATTRIBUTE3,
956 N_ATTRIBUTE4,
957 N_ATTRIBUTE5,
958 N_ATTRIBUTE6,
959 N_ATTRIBUTE7,
960 N_ATTRIBUTE8,
961 N_ATTRIBUTE9,
962 SUPPLIER_LOT_NUMBER,
963 N_ATTRIBUTE10,
964 TERRITORY_CODE)
965 VALUES
966 (l_inventory_item_id,
967 l_organization_id,
968 l_lot_number,
969 SYSDATE,
970 l_userid,
971 SYSDATE,
972 l_userid,
973 l_loginid,
974 l_expiration_date,
975 null,
976 l_ATTRIBUTE_CATEGORY ,
977 l_ATTRIBUTE1 ,
978 l_ATTRIBUTE2 ,
979 l_ATTRIBUTE3 ,
980 l_ATTRIBUTE4 ,
981 l_ATTRIBUTE5 ,
982 l_ATTRIBUTE6 ,
983 l_ATTRIBUTE7 ,
984 l_ATTRIBUTE8,
985 l_ATTRIBUTE9 ,
986 l_ATTRIBUTE10 ,
987 l_ATTRIBUTE11,
988 l_ATTRIBUTE12 ,
989 l_ATTRIBUTE13 ,
990 l_ATTRIBUTE14 ,
991 l_ATTRIBUTE15 ,
992 null,
993 null,
994 null,
995 null,
996 l_object_id,
997 null,
998 to_number(null), -- Vendor ID currently Set as Null
999 g_lot_attributes_tbl(1).COLUMN_VALUE,
1000 fnd_date.canonical_to_date(g_lot_attributes_tbl(2).COLUMN_VALUE),
1001 g_lot_attributes_tbl(3).COLUMN_VALUE,
1002 to_number(g_lot_attributes_tbl(4).COLUMN_VALUE),
1003 fnd_date.canonical_to_date(g_lot_attributes_tbl(5).COLUMN_VALUE),
1004 to_number(g_lot_attributes_tbl(6).COLUMN_VALUE),
1005 fnd_date.canonical_to_date(g_lot_attributes_tbl(7).COLUMN_VALUE),
1006 fnd_date.canonical_to_date(g_lot_attributes_tbl(8).COLUMN_VALUE),
1007 g_lot_attributes_tbl(9).COLUMN_VALUE,
1008 to_number(g_lot_attributes_tbl(10).COLUMN_VALUE),
1009 g_lot_attributes_tbl(11).COLUMN_VALUE,
1010 to_number(g_lot_attributes_tbl(12).COLUMN_VALUE),
1011 g_lot_attributes_tbl(13).COLUMN_VALUE,
1012 g_lot_attributes_tbl(14).COLUMN_VALUE,
1013 fnd_date.canonical_to_date(g_lot_attributes_tbl(15).COLUMN_VALUE),
1014 to_number(g_lot_attributes_tbl(16).COLUMN_VALUE),
1015 g_lot_attributes_tbl(17).COLUMN_VALUE,
1016 to_number(g_lot_attributes_tbl(18).COLUMN_VALUE),
1017 to_number(g_lot_attributes_tbl(19).COLUMN_VALUE),
1018 g_lot_attributes_tbl(20).COLUMN_VALUE,
1019 to_number(g_lot_attributes_tbl(21).COLUMN_VALUE),
1020 g_lot_attributes_tbl(22).COLUMN_VALUE,
1021 g_lot_attributes_tbl(23).COLUMN_VALUE,
1022 g_lot_attributes_tbl(24).COLUMN_VALUE,
1023 g_lot_attributes_tbl(25).COLUMN_VALUE,
1024 g_lot_attributes_tbl(26).COLUMN_VALUE,
1025 g_lot_attributes_tbl(27).COLUMN_VALUE,
1026 g_lot_attributes_tbl(28).COLUMN_VALUE,
1027 g_lot_attributes_tbl(29).COLUMN_VALUE,
1028 g_lot_attributes_tbl(30).COLUMN_VALUE,
1029 g_lot_attributes_tbl(31).COLUMN_VALUE,
1030 g_lot_attributes_tbl(32).COLUMN_VALUE,
1031 g_lot_attributes_tbl(33).COLUMN_VALUE,
1032 g_lot_attributes_tbl(34).COLUMN_VALUE,
1033 g_lot_attributes_tbl(35).COLUMN_VALUE,
1034 g_lot_attributes_tbl(36).COLUMN_VALUE,
1035 g_lot_attributes_tbl(37).COLUMN_VALUE,
1036 g_lot_attributes_tbl(38).COLUMN_VALUE,
1037 g_lot_attributes_tbl(39).COLUMN_VALUE,
1038 g_lot_attributes_tbl(40).COLUMN_VALUE,
1039 g_lot_attributes_tbl(41).COLUMN_VALUE,
1040 g_lot_attributes_tbl(42).COLUMN_VALUE,
1041 g_lot_attributes_tbl(43).COLUMN_VALUE,
1042 fnd_date.canonical_to_date(g_lot_attributes_tbl(44).COLUMN_VALUE),
1043 fnd_date.canonical_to_date(g_lot_attributes_tbl(45).COLUMN_VALUE),
1044 fnd_date.canonical_to_date(g_lot_attributes_tbl(46).COLUMN_VALUE),
1045 fnd_date.canonical_to_date(g_lot_attributes_tbl(47).COLUMN_VALUE),
1046 fnd_date.canonical_to_date(g_lot_attributes_tbl(48).COLUMN_VALUE),
1047 fnd_date.canonical_to_date(g_lot_attributes_tbl(49).COLUMN_VALUE),
1048 fnd_date.canonical_to_date(g_lot_attributes_tbl(50).COLUMN_VALUE),
1049 fnd_date.canonical_to_date(g_lot_attributes_tbl(51).COLUMN_VALUE),
1050 fnd_date.canonical_to_date(g_lot_attributes_tbl(52).COLUMN_VALUE),
1051 fnd_date.canonical_to_date(g_lot_attributes_tbl(53).COLUMN_VALUE),
1052 to_number(g_lot_attributes_tbl(54).COLUMN_VALUE),
1053 to_number(g_lot_attributes_tbl(55).COLUMN_VALUE),
1054 to_number(g_lot_attributes_tbl(56).COLUMN_VALUE),
1055 to_number(g_lot_attributes_tbl(57).COLUMN_VALUE),
1056 to_number(g_lot_attributes_tbl(58).COLUMN_VALUE),
1057 to_number(g_lot_attributes_tbl(59).COLUMN_VALUE),
1058 to_number(g_lot_attributes_tbl(60).COLUMN_VALUE),
1059 to_number(g_lot_attributes_tbl(61).COLUMN_VALUE),
1060 to_number(g_lot_attributes_tbl(62).COLUMN_VALUE),
1061 g_lot_attributes_tbl(63).COLUMN_VALUE,
1062 to_number(g_lot_attributes_tbl(64).COLUMN_VALUE),
1066 l_progress := '120';
1063 g_lot_attributes_tbl(65).COLUMN_VALUE);
1064
1065
1067 IF (l_debug = 1) THEN
1068 print_debug('After Inserting into MTL_LOT_NUMBERS ' , 4);
1069 END IF;
1070
1071
1072 EXCEPTION
1073 WHEN FND_API.g_exc_error THEN
1074 ROLLBACK TO InsertLot_sv;
1075 IF (l_debug = 1) THEN
1076 print_debug('Process - expected error happened - l_progress : '||l_progress, 1);
1077 END IF;
1078 x_return_status := FND_API.G_RET_STS_ERROR;
1079 FND_MSG_PUB.Count_And_Get
1080 (p_count => x_msg_count,
1081 p_data => x_msg_data
1082 );
1083
1084 WHEN FND_API.g_exc_unexpected_error THEN
1085 ROLLBACK TO InsertLot_sv;
1086 IF (l_debug = 1) THEN
1087 print_debug('Process - unexpected error happened - l_progress : '||l_progress, 1);
1088 END IF;
1089 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1090 FND_MSG_PUB.Count_And_Get
1091 (p_count => x_msg_count,
1092 p_data => x_msg_data
1093 );
1094
1095 WHEN OTHERS THEN
1096 ROLLBACK TO InsertLot_sv;
1097 IF (l_debug = 1) THEN
1098 print_debug('Process - other error happened - l_progress : '||l_progress, 1);
1099 END IF;
1100 IF SQLCODE IS NOT NULL THEN
1101 IF (l_debug = 1) THEN
1102 print_debug('SQL Error : '||SQLERRM(SQLCODE)||' SQL Error code : '||SQLCODE, 1);
1103 END IF;
1104 END IF;
1105 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1106 END;
1107
1108
1109 procedure populateSerAttributesColumn IS
1110 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1111 BEGIN
1112 g_serial_attributes_tbl(1).COLUMN_NAME := 'SERIAL_ATTRIBUTE_CATEGORY';
1113 g_serial_attributes_tbl(1).COLUMN_TYPE := 'VARCHAR2';
1114
1115 g_serial_attributes_tbl(2).COLUMN_NAME := 'ORIGINATION_DATE';
1116 g_serial_attributes_tbl(2).COLUMN_TYPE := 'DATE';
1117
1118 g_serial_attributes_tbl(3).COLUMN_NAME := 'C_ATTRIBUTE1';
1119 g_serial_attributes_tbl(3).COLUMN_TYPE := 'VARCHAR2';
1120
1121 g_serial_attributes_tbl(4).COLUMN_NAME := 'C_ATTRIBUTE2';
1122 g_serial_attributes_tbl(4).COLUMN_TYPE := 'VARCHAR2';
1123
1124 g_serial_attributes_tbl(5).COLUMN_NAME := 'C_ATTRIBUTE3';
1125 g_serial_attributes_tbl(5).COLUMN_TYPE := 'VARCHAR2';
1126
1127 g_serial_attributes_tbl(6).COLUMN_NAME := 'C_ATTRIBUTE4';
1128 g_serial_attributes_tbl(6).COLUMN_TYPE := 'VARCHAR2';
1129
1130 g_serial_attributes_tbl(7).COLUMN_NAME := 'C_ATTRIBUTE5';
1131 g_serial_attributes_tbl(7).COLUMN_TYPE := 'VARCHAR2';
1132
1133 g_serial_attributes_tbl(8).COLUMN_NAME := 'C_ATTRIBUTE6';
1134 g_serial_attributes_tbl(8).COLUMN_TYPE := 'VARCHAR2';
1135
1136 g_serial_attributes_tbl(9).COLUMN_NAME := 'C_ATTRIBUTE7';
1137 g_serial_attributes_tbl(9).COLUMN_TYPE := 'VARCHAR2';
1138
1139 g_serial_attributes_tbl(10).COLUMN_NAME := 'C_ATTRIBUTE8';
1140 g_serial_attributes_tbl(10).COLUMN_TYPE := 'VARCHAR2';
1141
1142 g_serial_attributes_tbl(11).COLUMN_NAME := 'C_ATTRIBUTE9';
1143 g_serial_attributes_tbl(11).COLUMN_TYPE := 'VARCHAR2';
1144
1145 g_serial_attributes_tbl(12).COLUMN_NAME := 'C_ATTRIBUTE10';
1146 g_serial_attributes_tbl(12).COLUMN_TYPE := 'VARCHAR2';
1147
1148 g_serial_attributes_tbl(13).COLUMN_NAME := 'C_ATTRIBUTE11';
1149 g_serial_attributes_tbl(13).COLUMN_TYPE := 'VARCHAR2';
1150
1151 g_serial_attributes_tbl(14).COLUMN_NAME := 'C_ATTRIBUTE12';
1152 g_serial_attributes_tbl(14).COLUMN_TYPE := 'VARCHAR2';
1153
1154 g_serial_attributes_tbl(15).COLUMN_NAME := 'C_ATTRIBUTE13';
1155 g_serial_attributes_tbl(15).COLUMN_TYPE := 'VARCHAR2';
1156
1157 g_serial_attributes_tbl(16).COLUMN_NAME := 'C_ATTRIBUTE14';
1158 g_serial_attributes_tbl(16).COLUMN_TYPE := 'VARCHAR2';
1159
1160 g_serial_attributes_tbl(17).COLUMN_NAME := 'C_ATTRIBUTE15';
1161 g_serial_attributes_tbl(17).COLUMN_TYPE := 'VARCHAR2';
1162
1163 g_serial_attributes_tbl(18).COLUMN_NAME := 'C_ATTRIBUTE16';
1164 g_serial_attributes_tbl(18).COLUMN_TYPE := 'VARCHAR2';
1165
1166 g_serial_attributes_tbl(19).COLUMN_NAME := 'C_ATTRIBUTE17';
1167 g_serial_attributes_tbl(19).COLUMN_TYPE := 'VARCHAR2';
1168
1169 g_serial_attributes_tbl(20).COLUMN_NAME := 'C_ATTRIBUTE18';
1170 g_serial_attributes_tbl(20).COLUMN_TYPE := 'VARCHAR2';
1171
1172 g_serial_attributes_tbl(21).COLUMN_NAME := 'C_ATTRIBUTE19';
1173 g_serial_attributes_tbl(21).COLUMN_TYPE := 'VARCHAR2';
1174
1175 g_serial_attributes_tbl(22).COLUMN_NAME := 'C_ATTRIBUTE20';
1176 g_serial_attributes_tbl(22).COLUMN_TYPE := 'VARCHAR2';
1177
1178 g_serial_attributes_tbl(23).COLUMN_NAME := 'D_ATTRIBUTE1';
1179 g_serial_attributes_tbl(23).COLUMN_TYPE := 'DATE';
1180
1181 g_serial_attributes_tbl(24).COLUMN_NAME := 'D_ATTRIBUTE2';
1182 g_serial_attributes_tbl(24).COLUMN_TYPE := 'DATE';
1183
1184 g_serial_attributes_tbl(25).COLUMN_NAME := 'D_ATTRIBUTE3';
1185 g_serial_attributes_tbl(25).COLUMN_TYPE := 'DATE';
1186
1187 g_serial_attributes_tbl(26).COLUMN_NAME := 'D_ATTRIBUTE4';
1188 g_serial_attributes_tbl(26).COLUMN_TYPE := 'DATE';
1189
1190 g_serial_attributes_tbl(27).COLUMN_NAME := 'D_ATTRIBUTE5';
1191 g_serial_attributes_tbl(27).COLUMN_TYPE := 'DATE';
1192
1193 g_serial_attributes_tbl(28).COLUMN_NAME := 'D_ATTRIBUTE6';
1194 g_serial_attributes_tbl(28).COLUMN_TYPE := 'DATE';
1195
1199 g_serial_attributes_tbl(30).COLUMN_NAME := 'D_ATTRIBUTE8';
1196 g_serial_attributes_tbl(29).COLUMN_NAME := 'D_ATTRIBUTE7';
1197 g_serial_attributes_tbl(29).COLUMN_TYPE := 'DATE';
1198
1200 g_serial_attributes_tbl(30).COLUMN_TYPE := 'DATE';
1201
1202 g_serial_attributes_tbl(31).COLUMN_NAME := 'D_ATTRIBUTE9';
1203 g_serial_attributes_tbl(31).COLUMN_TYPE := 'DATE';
1204
1205 g_serial_attributes_tbl(32).COLUMN_NAME := 'D_ATTRIBUTE10';
1206 g_serial_attributes_tbl(32).COLUMN_TYPE := 'DATE';
1207
1208 g_serial_attributes_tbl(33).COLUMN_NAME := 'N_ATTRIBUTE1';
1209 g_serial_attributes_tbl(33).COLUMN_TYPE := 'NUMBER';
1210
1211 g_serial_attributes_tbl(34).COLUMN_NAME := 'N_ATTRIBUTE2';
1212 g_serial_attributes_tbl(34).COLUMN_TYPE := 'NUMBER';
1213
1214 g_serial_attributes_tbl(35).COLUMN_NAME := 'N_ATTRIBUTE3';
1215 g_serial_attributes_tbl(35).COLUMN_TYPE := 'NUMBER';
1216
1217 g_serial_attributes_tbl(36).COLUMN_NAME := 'N_ATTRIBUTE4';
1218 g_serial_attributes_tbl(36).COLUMN_TYPE := 'NUMBER';
1219
1220 g_serial_attributes_tbl(37).COLUMN_NAME := 'N_ATTRIBUTE5';
1221 g_serial_attributes_tbl(37).COLUMN_TYPE := 'NUMBER';
1222
1223 g_serial_attributes_tbl(38).COLUMN_NAME := 'N_ATTRIBUTE6';
1224 g_serial_attributes_tbl(38).COLUMN_TYPE := 'NUMBER';
1225
1226 g_serial_attributes_tbl(39).COLUMN_NAME := 'N_ATTRIBUTE7';
1227 g_serial_attributes_tbl(39).COLUMN_TYPE := 'NUMBER';
1228
1229 g_serial_attributes_tbl(40).COLUMN_NAME := 'N_ATTRIBUTE8';
1230 g_serial_attributes_tbl(40).COLUMN_TYPE := 'NUMBER';
1231
1232 g_serial_attributes_tbl(41).COLUMN_NAME := 'N_ATTRIBUTE9';
1233 g_serial_attributes_tbl(41).COLUMN_TYPE := 'NUMBER';
1234
1235 g_serial_attributes_tbl(42).COLUMN_NAME := 'N_ATTRIBUTE10';
1236 g_serial_attributes_tbl(42).COLUMN_TYPE := 'NUMBER';
1237
1238 g_serial_attributes_tbl(43).COLUMN_NAME := 'STATUS_ID';
1239 g_serial_attributes_tbl(43).COLUMN_TYPE := 'NUMBER';
1240
1241 g_serial_attributes_tbl(44).COLUMN_NAME := 'TERRITORY_CODE';
1242 g_serial_attributes_tbl(44).COLUMN_TYPE := 'VARCHAR2';
1243
1244 -- New Columns--
1245 g_serial_attributes_tbl(45).COLUMN_NAME := 'TIME_SINCE_NEW';
1246 g_serial_attributes_tbl(45).COLUMN_TYPE := 'NUMBER';
1247
1248 g_serial_attributes_tbl(46).COLUMN_NAME := 'CYCLES_SINCE_NEW';
1249 g_serial_attributes_tbl(46).COLUMN_TYPE := 'NUMBER';
1250
1251 g_serial_attributes_tbl(47).COLUMN_NAME := 'TIME_SINCE_OVERHAUL';
1252 g_serial_attributes_tbl(47).COLUMN_TYPE := 'NUMBER';
1253
1254 g_serial_attributes_tbl(48).COLUMN_NAME := 'CYCLES_SINCE_OVERHAUL' ;
1255 g_serial_attributes_tbl(48).COLUMN_TYPE := 'NUMBER';
1256
1257 g_serial_attributes_tbl(49).COLUMN_NAME := 'TIME_SINCE_REPAIR' ;
1258 g_serial_attributes_tbl(49).COLUMN_TYPE := 'NUMBER';
1259
1260 g_serial_attributes_tbl(50).COLUMN_NAME := 'CYCLES_SINCE_REPAIR';
1261 g_serial_attributes_tbl(50).COLUMN_TYPE := 'NUMBER';
1262
1263 g_serial_attributes_tbl(51).COLUMN_NAME := 'TIME_SINCE_VISIT' ;
1264 g_serial_attributes_tbl(51).COLUMN_TYPE := 'NUMBER';
1265
1266 g_serial_attributes_tbl(52).COLUMN_NAME := 'CYCLES_SINCE_VISIT' ;
1267 g_serial_attributes_tbl(52).COLUMN_TYPE := 'NUMBER';
1268
1269 g_serial_attributes_tbl(53).COLUMN_NAME := 'TIME_SINCE_MARK' ;
1270 g_serial_attributes_tbl(53).COLUMN_TYPE := 'NUMBER';
1271
1272 g_serial_attributes_tbl(54).COLUMN_NAME := 'CYCLES_SINCE_MARK' ;
1273 g_serial_attributes_tbl(54).COLUMN_TYPE := 'NUMBER';
1274
1275 g_serial_attributes_tbl(55).COLUMN_NAME := 'NUMBER_OF_REPAIRS' ;
1276 g_serial_attributes_tbl(55).COLUMN_TYPE := 'NUMBER';
1277
1278
1279 END;
1280
1281 PROCEDURE insertSerial(
1282 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1283 p_inventory_item_id IN NUMBER,
1284 p_organization_id IN NUMBER,
1285 p_serial_number IN VARCHAR2,
1286 p_initialization_date IN DATE,
1287 p_completion_date IN DATE,
1288 p_ship_date IN DATE,
1289 p_revision IN VARCHAR2,
1290 p_lot_number IN VARCHAR2,
1291 p_current_locator_id IN NUMBER,
1292 p_subinventory_code IN VARCHAR2,
1293 p_trx_src_id IN NUMBER,
1294 p_unit_vendor_id IN NUMBER,
1295 p_vendor_lot_number IN VARCHAR2,
1296 p_vendor_serial_number IN VARCHAR2,
1297 p_receipt_issue_type IN NUMBER,
1298 p_txn_src_id IN NUMBER,
1299 p_txn_src_name IN VARCHAR2,
1300 p_txn_src_type_id IN NUMBER,
1301 p_transaction_id IN NUMBER,
1302 p_current_status IN NUMBER,
1303 p_parent_item_id IN NUMBER,
1304 p_parent_serial_number IN VARCHAR2,
1305 p_cost_group_id IN NUMBER,
1306 p_serial_transaction_intf_id IN NUMBER,
1307 p_status_id IN NUMBER,
1308 x_object_id OUT NOCOPY NUMBER,
1309 x_return_status OUT NOCOPY VARCHAR2,
1310 x_msg_count OUT NOCOPY NUMBER,
1314 l_api_name CONSTANT VARCHAR2(30):= 'insertSerial';
1311 x_msg_data OUT NOCOPY VARCHAR2)
1312 IS
1313 l_api_version CONSTANT NUMBER := 1.0;
1315 l_userid NUMBER;
1316 l_loginid NUMBER;
1317 l_serial_control_code NUMBER;
1318 l_attributes_default INV_LOT_SEL_ATTR.LOT_SEL_ATTRIBUTES_TBL_TYPE;
1319 l_attributes_default_count NUMBER;
1320 l_attributes_in INV_LOT_SEL_ATTR.LOT_SEL_ATTRIBUTES_TBL_TYPE;
1321
1322 l_column_idx BINARY_INTEGER := 55;
1323
1324 l_return_status VARCHAR2(1);
1325 l_msg_data VARCHAR2(2000);
1326 l_msg_count NUMBER;
1327 l_status_rec INV_MATERIAL_STATUS_PUB.mtl_status_update_rec_type;
1328 l_status_id NUMBER := null;
1329 l_lot_status_enabled VARCHAR2(1);
1330 l_default_lot_status_id NUMBER := NULL;
1331 l_serial_status_enabled VARCHAR2(1);
1332 l_default_serial_status_id NUMBER;
1333 l_attributes_name varchar2(100) := 'Serial Attributes';
1334
1335 l_status_name varchar2(30);
1336 l_progress varchar2(100);
1337
1338
1339 x_context_value mtl_flex_context.descriptive_flex_context_code%type;
1340 l_status BOOLEAN;
1341
1342 v_flexfield fnd_dflex.dflex_r;
1343 v_flexinfo fnd_dflex.dflex_dr;
1344 v_contexts fnd_dflex.contexts_dr;
1345 v_segments fnd_dflex.segments_dr;
1346
1347 v_colname varchar2(50);
1348
1349 l_ATTRIBUTE_CATEGORY VARCHAR2(30) ;
1350 l_ATTRIBUTE1 VARCHAR2(150) ;
1351 l_ATTRIBUTE2 VARCHAR2(150) ;
1352 l_ATTRIBUTE3 VARCHAR2(150) ;
1353 l_ATTRIBUTE4 VARCHAR2(150) ;
1354 l_ATTRIBUTE5 VARCHAR2(150) ;
1355 l_ATTRIBUTE6 VARCHAR2(150) ;
1356 l_ATTRIBUTE7 VARCHAR2(150) ;
1357 l_ATTRIBUTE8 VARCHAR2(150) ;
1358 l_ATTRIBUTE9 VARCHAR2(150) ;
1359 l_ATTRIBUTE10 VARCHAR2(150) ;
1360 l_ATTRIBUTE11 VARCHAR2(150) ;
1361 l_ATTRIBUTE12 VARCHAR2(150) ;
1362 l_ATTRIBUTE13 VARCHAR2(150) ;
1363 l_ATTRIBUTE14 VARCHAR2(150) ;
1364 l_ATTRIBUTE15 VARCHAR2(150) ;
1365
1366
1367
1368 cursor serial_intf_csr(P_SERIAL_TRANSACTION_INTF_ID NUMBER) is
1369 select SERIAL_ATTRIBUTE_CATEGORY
1370 , fnd_date.date_to_canonical(ORIGINATION_DATE )
1371 , C_ATTRIBUTE1
1372 , C_ATTRIBUTE2
1373 , C_ATTRIBUTE3
1374 , C_ATTRIBUTE4
1375 , C_ATTRIBUTE5
1376 , C_ATTRIBUTE6
1377 , C_ATTRIBUTE7
1378 , C_ATTRIBUTE8
1379 , C_ATTRIBUTE9
1380 , C_ATTRIBUTE10
1381 , C_ATTRIBUTE11
1382 , C_ATTRIBUTE12
1383 , C_ATTRIBUTE13
1384 , C_ATTRIBUTE14
1385 , C_ATTRIBUTE15
1386 , C_ATTRIBUTE16
1387 , C_ATTRIBUTE17
1388 , C_ATTRIBUTE18
1389 , C_ATTRIBUTE19
1390 , C_ATTRIBUTE20
1391 , fnd_date.date_to_canonical(D_ATTRIBUTE1 )
1392 , fnd_date.date_to_canonical(D_ATTRIBUTE2 )
1393 , fnd_date.date_to_canonical(D_ATTRIBUTE3 )
1394 , fnd_date.date_to_canonical(D_ATTRIBUTE4 )
1395 , fnd_date.date_to_canonical(D_ATTRIBUTE5 )
1396 , fnd_date.date_to_canonical(D_ATTRIBUTE6 )
1397 , fnd_date.date_to_canonical(D_ATTRIBUTE7)
1398 , fnd_date.date_to_canonical(D_ATTRIBUTE8)
1399 , fnd_date.date_to_canonical( D_ATTRIBUTE9)
1400 , fnd_date.date_to_canonical(D_ATTRIBUTE10 )
1401 , to_char(N_ATTRIBUTE1 )
1402 , to_char(N_ATTRIBUTE2)
1403 , to_char(N_ATTRIBUTE3)
1404 , to_char(N_ATTRIBUTE4)
1405 , to_char(N_ATTRIBUTE5)
1406 , to_char(N_ATTRIBUTE6)
1407 , to_char(N_ATTRIBUTE7)
1408 , to_char(N_ATTRIBUTE8)
1409 ,to_char( N_ATTRIBUTE9)
1410 , to_char(N_ATTRIBUTE10)
1411 , STATUS_ID
1412 , TERRITORY_CODE
1413 , TIME_SINCE_NEW
1414 , CYCLES_SINCE_NEW
1415 , TIME_SINCE_OVERHAUL
1416 , CYCLES_SINCE_OVERHAUL
1417 , TIME_SINCE_REPAIR
1418 , CYCLES_SINCE_REPAIR
1419 , TIME_SINCE_VISIT
1420 , CYCLES_SINCE_VISIT
1421 , TIME_SINCE_MARK
1422 , CYCLES_SINCE_MARK
1423 , NUMBER_OF_REPAIRS
1424 , STATUS_NAME
1425 -- other columns
1426 , ATTRIBUTE_CATEGORY
1430 , ATTRIBUTE4
1427 , ATTRIBUTE1
1428 , ATTRIBUTE2
1429 , ATTRIBUTE3
1431 , ATTRIBUTE5
1432 , ATTRIBUTE6
1433 , ATTRIBUTE7
1434 , ATTRIBUTE8
1435 , ATTRIBUTE9
1436 , ATTRIBUTE10
1437 , ATTRIBUTE11
1438 , ATTRIBUTE12
1439 , ATTRIBUTE13
1440 , ATTRIBUTE14
1441 , ATTRIBUTE15
1442 from mtl_serial_numbers_interface
1443 where TRANSACTION_INTERFACE_ID = P_SERIAL_TRANSACTION_INTF_ID;
1444
1445 l_input_idx BINARY_INTEGER;
1446
1447 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1448 BEGIN
1449
1450 IF (l_debug = 1) THEN
1451 print_debug('Inside Insert Serial ',4);
1452 END IF;
1453
1454 x_return_status := FND_API.G_RET_STS_SUCCESS;
1455
1456 -- Standard Start of API savepoint
1457 SAVEPOINT insertSerial_sv;
1458
1459 l_progress := '10';
1460 IF (l_debug = 1) THEN
1461 print_debug('At Step = '|| l_progress,4);
1462 END IF;
1463
1464 BEGIN
1465 SELECT serial_number_control_code
1466 INTO l_serial_control_code
1467 FROM MTL_SYSTEM_ITEMS
1468 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
1469 AND ORGANIZATION_ID = p_organization_id;
1470
1471 if(l_serial_control_code = 1) then
1472 fnd_message.set_name('INV','INV_NO_SERIAL_CONTROL');
1473 fnd_msg_pub.add;
1474 x_return_status := FND_API.G_RET_STS_ERROR;
1475 return;
1476 end if;
1477 EXCEPTION
1478 WHEN NO_DATA_FOUND THEN
1479 fnd_message.set_name('INV','INV_INVALID_ITEM');
1480 fnd_msg_pub.add;
1481 x_return_status := FND_API.G_RET_STS_ERROR;
1482 return;
1483 END;
1484
1485 l_progress := '20';
1486 IF (l_debug = 1) THEN
1487 print_debug('At Step = '|| l_progress,4);
1488 END IF;
1489
1490 select mtl_gen_object_id_s.nextval into x_object_id from dual;
1491
1492 l_progress := '30';
1493 IF (l_debug = 1) THEN
1494 print_debug('At Step = '|| l_progress,4);
1495 END IF;
1496
1497 populateSerAttributesColumn();
1498
1499 l_progress := '40';
1500 IF (l_debug = 1) THEN
1501 print_debug('At Step = '|| l_progress,4);
1502 END IF;
1503
1504 if( p_serial_transaction_intf_id is not null ) then
1505 open serial_intf_csr(p_serial_transaction_intf_id);
1506 LOOP
1507 fetch serial_intf_csr into g_serial_attributes_tbl(1).column_value, g_serial_attributes_tbl(2).column_value,
1508 g_serial_attributes_tbl(3).column_value, g_serial_attributes_tbl(4).column_value,
1509 g_serial_attributes_tbl(5).column_value, g_serial_attributes_tbl(6).column_value,
1510 g_serial_attributes_tbl(7).column_value, g_serial_attributes_tbl(8).column_value,
1511 g_serial_attributes_tbl(9).column_value, g_serial_attributes_tbl(10).column_value,
1512 g_serial_attributes_tbl(11).column_value, g_serial_attributes_tbl(12).column_value,
1513 g_serial_attributes_tbl(13).column_value, g_serial_attributes_tbl(14).column_value,
1514 g_serial_attributes_tbl(15).column_value, g_serial_attributes_tbl(16).column_value,
1515 g_serial_attributes_tbl(17).column_value, g_serial_attributes_tbl(18).column_value,
1516 g_serial_attributes_tbl(19).column_value, g_serial_attributes_tbl(20).column_value,
1517 g_serial_attributes_tbl(21).column_value, g_serial_attributes_tbl(22).column_value,
1518 g_serial_attributes_tbl(23).column_value, g_serial_attributes_tbl(24).column_value,
1519 g_serial_attributes_tbl(25).column_value, g_serial_attributes_tbl(26).column_value,
1520 g_serial_attributes_tbl(27).column_value, g_serial_attributes_tbl(28).column_value,
1521 g_serial_attributes_tbl(29).column_value, g_serial_attributes_tbl(30).column_value,
1522 g_serial_attributes_tbl(31).column_value, g_serial_attributes_tbl(32).column_value,
1523 g_serial_attributes_tbl(33).column_value, g_serial_attributes_tbl(34).column_value,
1524 g_serial_attributes_tbl(35).column_value, g_serial_attributes_tbl(36).column_value,
1525 g_serial_attributes_tbl(37).column_value, g_serial_attributes_tbl(38).column_value,
1526 g_serial_attributes_tbl(39).column_value, g_serial_attributes_tbl(40).column_value,
1527 g_serial_attributes_tbl(41).column_value, g_serial_attributes_tbl(42).column_value,
1528 g_serial_attributes_tbl(43).column_value, g_serial_attributes_tbl(44).column_value,
1529 g_serial_attributes_tbl(45).column_value, g_serial_attributes_tbl(46).column_value,
1530 g_serial_attributes_tbl(47).column_value, g_serial_attributes_tbl(48).column_value,
1531 g_serial_attributes_tbl(49).column_value, g_serial_attributes_tbl(50).column_value,
1532 g_serial_attributes_tbl(51).column_value, g_serial_attributes_tbl(52).column_value,
1536 , l_ATTRIBUTE1
1533 g_serial_attributes_tbl(53).column_value, g_serial_attributes_tbl(54).column_value,
1534 g_serial_attributes_tbl(55).column_value, l_status_name
1535 , l_ATTRIBUTE_CATEGORY
1537 , l_ATTRIBUTE2
1538 , l_ATTRIBUTE3
1539 , l_ATTRIBUTE4
1540 , l_ATTRIBUTE5
1541 , l_ATTRIBUTE6
1542 , l_ATTRIBUTE7
1543 , l_ATTRIBUTE8
1544 , l_ATTRIBUTE9
1545 , l_ATTRIBUTE10
1546 , l_ATTRIBUTE11
1547 , l_ATTRIBUTE12
1548 , l_ATTRIBUTE13
1549 , l_ATTRIBUTE14
1550 , l_ATTRIBUTE15
1551 ;
1552 exit when serial_intf_csr%NOTFOUND;
1553 END LOOP;
1554 close serial_intf_csr;
1555
1556 l_input_idx := 0;
1557 for x in 1..55 LOOP
1558 if( g_serial_attributes_tbl(x).column_value is not null ) then
1559 l_input_idx := l_input_idx + 1;
1560 l_attributes_in(l_input_idx).column_name := g_serial_attributes_tbl(x).column_name;
1561 l_attributes_in(l_input_idx).column_type := g_serial_attributes_tbl(x).column_type;
1562 l_attributes_in(l_input_idx).column_value := g_serial_attributes_tbl(x).column_value;
1563 end if;
1564 end loop;
1565 end if;
1566 ----------------------------------------------------------
1567 -- call inv_lot_sel_attr.get_default to get the default value
1568 -- of the lot attributes
1569 ---------------------------------------------------------
1570
1571 l_progress := '50';
1572 IF (l_debug = 1) THEN
1573 print_debug('At Step = '|| l_progress,4);
1574 END IF;
1575
1576 inv_lot_sel_attr.get_default(
1577 x_attributes_default => l_attributes_default,
1578 x_attributes_default_count => l_attributes_default_count,
1579 x_return_status => l_return_status,
1580 x_msg_count => l_msg_count,
1581 x_msg_data => l_msg_data,
1582 p_table_name => 'MTL_SERIAL_NUMBERS',
1583 p_attributes_name => 'Serial Attributes',
1584 p_inventory_item_id => p_inventory_item_id,
1585 p_organization_id => p_organization_id,
1586 p_lot_serial_number => p_serial_number,
1587 p_attributes => l_attributes_in);
1588
1589 l_progress := '60';
1590 IF (l_debug = 1) THEN
1591 print_debug('At Step = '|| l_progress,4);
1592 END IF;
1593
1594 if( l_return_status <> FND_API.G_RET_STS_SUCCESS ) then
1595 x_return_status := l_return_status;
1596 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1597 end if;
1598
1599 if( l_attributes_default_count > 0 ) then
1600 for i in 1..l_attributes_default_count LOOP
1601 for j in 1..g_serial_attributes_tbl.COUNT LOOP
1602 if( l_attributes_default(i).COLUMN_NAME = g_serial_attributes_tbl(j).COLUMN_NAME ) then
1603 g_serial_attributes_tbl(j).COLUMN_VALUE := l_attributes_default(i).COLUMN_VALUE;
1604 end if;
1605 end loop;
1606 end loop;
1607 end if;
1608 l_userid := fnd_global.user_id;
1609 l_loginid := fnd_global.login_id;
1610
1611
1612 l_progress := '70';
1613 IF (l_debug = 1) THEN
1614 print_debug('At Step = '|| l_progress,4);
1615 END IF;
1616
1617 --Get the default status Id
1618 --
1619
1620 INV_MATERIAL_STATUS_GRP.get_lot_serial_status_control(
1621 p_organization_id => p_organization_id
1622 , p_inventory_item_id => p_inventory_item_id
1623 , x_return_status => l_return_status
1624 , x_msg_count => l_msg_count
1625 , x_msg_data => l_msg_data
1626 , x_lot_status_enabled => l_lot_status_enabled
1627 , x_default_lot_status_id => l_default_lot_status_id
1628 , x_serial_status_enabled => l_serial_status_enabled
1629 , x_default_serial_status_id => l_default_serial_status_id);
1630
1631 if( l_return_status <> FND_API.G_RET_STS_SUCCESS ) then
1632 x_return_status := l_return_status;
1633 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1634 end if;
1635
1636 if (NVL(l_serial_status_enabled, 'Y') = 'Y') then
1637 l_status_id := l_default_serial_status_id;
1638 Begin
1639 if l_status_name is not null then
1640 select status_id
1641 into l_status_id
1642 from mtl_material_statuses_vl
1643 where status_code = l_status_name
1644 ;
1645 end if;
1646 Exception
1647 When others then
1648 IF (l_debug = 1) THEN
1649 print_debug('Status = '|| l_status_name || ' is invalid , setting to default status' ,4);
1650 END IF;
1654
1651 null;
1652 End;
1653 end if;
1655 l_progress := '80';
1656 IF (l_debug = 1) THEN
1657 print_debug('At Step = '|| l_progress,4);
1658 END IF;
1659
1660 IF (l_debug = 1) THEN
1661 print_debug('Getting All the context Codes '||' Progress ='|| l_progress , 4);
1662 END IF;
1663
1664 -- Get the Context Code for this Item First
1665
1666 -- Get flexfield
1667 fnd_dflex.get_flexfield('INV', l_attributes_name, v_flexfield, v_flexinfo);
1668
1669 l_progress := '80';
1670 IF (l_debug = 1) THEN
1671 print_debug('At Step = '|| l_progress,4);
1672 END IF;
1673
1674 -- Get Contexts
1675 fnd_dflex.get_contexts(v_flexfield, v_contexts);
1676
1677
1678 IF (l_debug = 1) THEN
1679 print_debug('Number of contexts Found '||v_contexts.ncontexts , 4);
1680 END IF;
1681 l_progress := '90';
1682 IF (l_debug = 1) THEN
1683 print_debug('At Step = '|| l_progress,4);
1684 END IF;
1685
1686 -- First Check whether context is present in the interface
1687 -- row, if not then get the context code
1688 ---
1689
1690 if g_serial_attributes_tbl(1).column_value is null then
1691 l_progress := '90.1';
1692 IF (l_debug = 1) THEN
1693 print_debug('At Step = '|| l_progress,4);
1694 END IF;
1695 inv_lot_sel_attr.get_context_code(x_context_value,
1696 p_organization_id,p_inventory_item_id,l_attributes_name);
1697
1698 g_serial_attributes_tbl(1).column_value := x_context_value;
1699
1700 IF (l_debug = 1) THEN
1701 print_debug('No context set in interface record Setting context to '||x_context_value, 4);
1702 END IF;
1703 else
1704 x_context_value := g_serial_attributes_tbl(1).column_value;
1705 end if;
1706
1707
1708 -- Set the Context Code for validating the Serial Attributes.
1709 l_progress := '100';
1710
1711
1712 -- IF CONTEXT FOUND NULL THEN SKIP THE VALIDATION
1713
1714 if x_context_value is not null
1715 then
1716
1717 fnd_flex_descval.set_context_value(x_context_value);
1718 fnd_flex_descval.clear_column_values;
1719 fnd_flex_descval.set_column_value('SERIAL_ATTRIBUTE_CATEGORY',
1720 g_serial_attributes_tbl(1).column_value);
1721
1722 IF (l_debug = 1) THEN
1723 print_debug('After Setting the context Code for validation '||' Progress ='|| l_progress , 4);
1724 END IF;
1725
1726
1727 -- Setting the Values for Validating
1728
1729 FOR i IN 1..v_contexts.ncontexts LOOP
1730
1731 IF (l_debug = 1) THEN
1732 print_debug('cheking for context Code '||v_contexts.context_code(i) , 4);
1733 END IF;
1734
1735 IF(v_contexts.is_enabled(i) AND
1736 ((UPPER(v_contexts.context_code(i)) = UPPER(x_context_value)) OR
1737 v_contexts.is_global(i))
1738 ) THEN
1739 -- Get segments
1740 fnd_dflex.get_segments(fnd_dflex.make_context(v_flexfield,
1741 v_contexts.context_code(i)),
1742 v_segments, TRUE);
1743
1744 <<segmentLoop>>
1745 FOR j IN 1..v_segments.nsegments LOOP
1746 IF v_segments.is_enabled(j) THEN
1747 v_colName := v_segments.application_column_name(j);
1748 <<columnLoop>>
1749 FOR k IN 1..g_serial_attributes_tbl.count() LOOP
1750 IF UPPER(v_colName) = UPPER(g_serial_attributes_tbl(k).column_name) THEN
1751
1752 -- Sets the Values for Validation
1753
1754 -- Setting the column data type for validation
1755 if g_serial_attributes_tbl(k).column_type = 'DATE' then
1756 IF (l_debug = 1) THEN
1757 print_debug('Setting the columns for validation -- Date' , 4);
1758 END IF;
1759 fnd_flex_descval.set_column_value(g_serial_attributes_tbl(k).column_name,
1760 fnd_date.canonical_to_date(g_serial_attributes_tbl(k).column_value));
1761 end if;
1762
1763 if g_serial_attributes_tbl(k).column_type = 'NUMBER' then
1764 IF (l_debug = 1) THEN
1765 print_debug('Setting the columns for validation -- Number' , 4);
1766 END IF;
1767 fnd_flex_descval.set_column_value(g_serial_attributes_tbl(k).column_name,
1768 to_number(g_serial_attributes_tbl(k).column_value));
1769 end if;
1770
1771 if g_serial_attributes_tbl(k).column_type = 'VARCHAR2' then
1772 IF (l_debug = 1) THEN
1773 print_debug('Setting the columns for validation -- Varchar2' , 4);
1774 END IF;
1778
1775 fnd_flex_descval.set_column_value(g_serial_attributes_tbl(k).column_name,
1776 g_serial_attributes_tbl(k).column_value);
1777 end if;
1779 IF (l_debug = 1) THEN
1780 print_debug('Setting the columns for validation ' , 4);
1781 print_debug('column Name '|| g_serial_attributes_tbl(k).column_name , 4);
1782 print_debug('column Value '||g_serial_attributes_tbl(k).column_value , 4);
1783 END IF;
1784
1785 --print_debug('Calling the Validation API ' , 4);
1786
1787 EXIT columnLoop; -- found column
1788 END IF;
1789 END LOOP columnLoop;
1790
1791 END IF;
1792 END LOOP segmentLoop;
1793 END IF;
1794 END LOOP contextLoop;
1795
1796
1797 -- Call the validating routine for Lot Attributes.
1798
1799 l_progress := '110';
1800 IF (l_debug = 1) THEN
1801 print_debug('Before Calling The API for validation ' , 4);
1802 END IF;
1803
1804 l_status := fnd_flex_descval.validate_desccols(
1805 appl_short_name => 'INV',
1806 desc_flex_name => l_attributes_name);
1807
1808 if l_status = TRUE then
1809 IF (l_debug = 1) THEN
1810 print_debug('API for validation is successfull' , 4);
1811 END IF;
1812 null;
1813 else
1814 IF (l_debug = 1) THEN
1815 print_debug('API for validation is failure' , 4);
1816 print_debug('Error Messages '|| fnd_flex_descval.error_message , 4);
1817 END IF;
1818 x_return_status := FND_API.G_RET_STS_ERROR ;
1819 x_msg_data := fnd_flex_descval.error_message;
1820 raise FND_API.G_EXC_ERROR;
1821 end if;
1822
1823 end if;
1824
1825
1826 --
1827 --
1828 l_progress := '120';
1829 IF (l_debug = 1) THEN
1830 print_debug('Before Inserting into MTL_SERIAL_NUMBER' , 4);
1831 END IF;
1832
1833
1834 INSERT INTO MTL_SERIAL_NUMBERS(
1835 INVENTORY_ITEM_ID,
1836 SERIAL_NUMBER,
1837 LAST_UPDATE_DATE,
1838 LAST_UPDATED_BY,
1839 CREATION_DATE,
1840 CREATED_BY,
1841 LAST_UPDATE_LOGIN,
1842 REQUEST_ID,
1843 PROGRAM_APPLICATION_ID,
1844 PROGRAM_ID,
1845 PROGRAM_UPDATE_DATE,
1846 INITIALIZATION_DATE,
1847 COMPLETION_DATE,
1848 SHIP_DATE,
1849 CURRENT_STATUS,
1850 REVISION,
1851 LOT_NUMBER,
1852 FIXED_ASSET_TAG,
1853 RESERVED_ORDER_ID,
1854 PARENT_ITEM_ID,
1855 PARENT_SERIAL_NUMBER,
1856 ORIGINAL_WIP_ENTITY_ID,
1857 ORIGINAL_UNIT_VENDOR_ID,
1858 VENDOR_SERIAL_NUMBER,
1859 VENDOR_LOT_NUMBER,
1860 LAST_TXN_SOURCE_TYPE_ID,
1861 LAST_TRANSACTION_ID,
1862 LAST_RECEIPT_ISSUE_TYPE,
1863 LAST_TXN_SOURCE_NAME,
1864 LAST_TXN_SOURCE_ID,
1865 DESCRIPTIVE_TEXT,
1866 CURRENT_SUBINVENTORY_CODE,
1867 CURRENT_LOCATOR_ID,
1868 CURRENT_ORGANIZATION_ID,
1869 ATTRIBUTE_CATEGORY,
1870 ATTRIBUTE1,
1871 ATTRIBUTE2,
1872 ATTRIBUTE3,
1873 ATTRIBUTE4,
1874 ATTRIBUTE5,
1875 ATTRIBUTE6,
1876 ATTRIBUTE7,
1877 ATTRIBUTE8,
1878 ATTRIBUTE9,
1879 ATTRIBUTE10,
1880 ATTRIBUTE11,
1881 ATTRIBUTE12,
1882 ATTRIBUTE13,
1883 ATTRIBUTE14,
1884 ATTRIBUTE15,
1885 GROUP_MARK_ID,
1886 LINE_MARK_ID,
1887 LOT_LINE_MARK_ID,
1888 END_ITEM_UNIT_NUMBER,
1889 GEN_OBJECT_ID,
1890 SERIAL_ATTRIBUTE_CATEGORY,
1891 ORIGINATION_DATE,
1892 C_ATTRIBUTE1,
1893 C_ATTRIBUTE2,
1894 C_ATTRIBUTE3,
1895 C_ATTRIBUTE4,
1896 C_ATTRIBUTE5,
1897 C_ATTRIBUTE6,
1898 C_ATTRIBUTE7,
1899 C_ATTRIBUTE8,
1900 C_ATTRIBUTE9,
1901 C_ATTRIBUTE10,
1902 C_ATTRIBUTE11,
1903 C_ATTRIBUTE12,
1904 C_ATTRIBUTE13,
1908 C_ATTRIBUTE17,
1905 C_ATTRIBUTE14,
1906 C_ATTRIBUTE15,
1907 C_ATTRIBUTE16,
1909 C_ATTRIBUTE18,
1910 C_ATTRIBUTE19,
1911 C_ATTRIBUTE20,
1912 D_ATTRIBUTE1,
1913 D_ATTRIBUTE2,
1914 D_ATTRIBUTE3,
1915 D_ATTRIBUTE4,
1916 D_ATTRIBUTE5,
1917 D_ATTRIBUTE6,
1918 D_ATTRIBUTE7,
1919 D_ATTRIBUTE8,
1920 D_ATTRIBUTE9,
1921 D_ATTRIBUTE10,
1922 N_ATTRIBUTE1,
1923 N_ATTRIBUTE2,
1924 N_ATTRIBUTE3,
1925 N_ATTRIBUTE4,
1926 N_ATTRIBUTE5,
1927 N_ATTRIBUTE6,
1928 N_ATTRIBUTE7,
1929 N_ATTRIBUTE8,
1930 N_ATTRIBUTE9,
1931 N_ATTRIBUTE10,
1932 STATUS_ID,
1933 TERRITORY_CODE,
1934 COST_GROUP_ID,
1935 TIME_SINCE_NEW,
1936 CYCLES_SINCE_NEW,
1937 TIME_SINCE_OVERHAUL,
1938 CYCLES_SINCE_OVERHAUL,
1939 TIME_SINCE_REPAIR,
1940 CYCLES_SINCE_REPAIR,
1941 TIME_SINCE_VISIT,
1942 CYCLES_SINCE_VISIT,
1943 TIME_SINCE_MARK,
1944 CYCLES_SINCE_MARK,
1945 NUMBER_OF_REPAIRS
1946 )
1947 VALUES
1948 (p_inventory_item_id,
1949 p_serial_number,
1950 SYSDATE,
1951 l_userid,
1952 SYSDATE,
1953 l_userid,
1954 l_loginid,
1955 null,
1956 null,
1957 null,
1958 null,
1959 p_initialization_date,
1960 p_completion_date,
1961 p_ship_date,
1962 p_current_status,
1963 p_revision,
1964 p_lot_number,
1965 null,
1966 null,
1967 p_parent_item_id,
1968 p_parent_serial_number,
1969 p_trx_src_id,
1970 p_unit_vendor_id,
1971 p_vendor_serial_number,
1972 p_vendor_lot_number,
1973 p_txn_src_type_id,
1974 p_transaction_id,
1975 p_receipt_issue_type,
1976 p_txn_src_name,
1977 p_txn_src_id,
1978 g_serial_attributes_tbl(31).COLUMN_VALUE,
1979 p_subinventory_code,
1980 p_current_locator_id,
1981 p_organization_id,
1982 l_ATTRIBUTE_CATEGORY ,
1983 l_ATTRIBUTE1 ,
1984 l_ATTRIBUTE2 ,
1985 l_ATTRIBUTE3 ,
1986 l_ATTRIBUTE4 ,
1987 l_ATTRIBUTE5 ,
1988 l_ATTRIBUTE6 ,
1989 l_ATTRIBUTE7 ,
1990 l_ATTRIBUTE8,
1991 l_ATTRIBUTE9 ,
1992 l_ATTRIBUTE10 ,
1993 l_ATTRIBUTE11,
1994 l_ATTRIBUTE12 ,
1995 l_ATTRIBUTE13 ,
1996 l_ATTRIBUTE14 ,
1997 l_ATTRIBUTE15 ,
1998 null,
1999 null,
2000 null,
2001 null,
2002 x_object_id,
2003 g_serial_attributes_tbl(1).COLUMN_VALUE,
2004 fnd_date.canonical_to_date(g_serial_attributes_tbl(2).COLUMN_VALUE),
2005 g_serial_attributes_tbl(3).COLUMN_VALUE,
2006 g_serial_attributes_tbl(4).COLUMN_VALUE,
2007 g_serial_attributes_tbl(5).COLUMN_VALUE,
2008 g_serial_attributes_tbl(6).COLUMN_VALUE,
2009 g_serial_attributes_tbl(7).COLUMN_VALUE,
2010 g_serial_attributes_tbl(8).COLUMN_VALUE,
2011 g_serial_attributes_tbl(9).COLUMN_VALUE,
2012 g_serial_attributes_tbl(10).COLUMN_VALUE,
2013 g_serial_attributes_tbl(11).COLUMN_VALUE,
2014 g_serial_attributes_tbl(12).COLUMN_VALUE,
2015 g_serial_attributes_tbl(13).COLUMN_VALUE,
2016 g_serial_attributes_tbl(14).COLUMN_VALUE,
2017 g_serial_attributes_tbl(15).COLUMN_VALUE,
2018 g_serial_attributes_tbl(16).COLUMN_VALUE,
2019 g_serial_attributes_tbl(17).COLUMN_VALUE,
2020 g_serial_attributes_tbl(18).COLUMN_VALUE,
2021 g_serial_attributes_tbl(19).COLUMN_VALUE,
2022 g_serial_attributes_tbl(20).COLUMN_VALUE,
2023 g_serial_attributes_tbl(21).COLUMN_VALUE,
2024 g_serial_attributes_tbl(22).COLUMN_VALUE,
2025 fnd_date.canonical_to_date(g_serial_attributes_tbl(23).COLUMN_VALUE),
2026 fnd_date.canonical_to_date(g_serial_attributes_tbl(24).COLUMN_VALUE),
2027 fnd_date.canonical_to_date(g_serial_attributes_tbl(25).COLUMN_VALUE),
2028 fnd_date.canonical_to_date(g_serial_attributes_tbl(26).COLUMN_VALUE),
2029 fnd_date.canonical_to_date(g_serial_attributes_tbl(27).COLUMN_VALUE),
2030 fnd_date.canonical_to_date(g_serial_attributes_tbl(28).COLUMN_VALUE),
2031 fnd_date.canonical_to_date(g_serial_attributes_tbl(29).COLUMN_VALUE),
2032 fnd_date.canonical_to_date(g_serial_attributes_tbl(30).COLUMN_VALUE),
2033 fnd_date.canonical_to_date(g_serial_attributes_tbl(31).COLUMN_VALUE),
2034 fnd_date.canonical_to_date(g_serial_attributes_tbl(32).COLUMN_VALUE),
2035 to_number(g_serial_attributes_tbl(33).COLUMN_VALUE),
2036 to_number(g_serial_attributes_tbl(34).COLUMN_VALUE),
2040 to_number(g_serial_attributes_tbl(38).COLUMN_VALUE),
2037 to_number(g_serial_attributes_tbl(35).COLUMN_VALUE),
2038 to_number(g_serial_attributes_tbl(36).COLUMN_VALUE),
2039 to_number(g_serial_attributes_tbl(37).COLUMN_VALUE),
2041 to_number(g_serial_attributes_tbl(39).COLUMN_VALUE),
2042 to_number(g_serial_attributes_tbl(40).COLUMN_VALUE),
2043 to_number(g_serial_attributes_tbl(41).COLUMN_VALUE),
2044 to_number(g_serial_attributes_tbl(42).COLUMN_VALUE),
2045 l_status_id,
2046 g_serial_attributes_tbl(44).COLUMN_VALUE,
2047 INV_COST_GROUP_PUB.G_COST_GROUP_ID,
2048 to_number(g_serial_attributes_tbl(45).COLUMN_VALUE),
2049 to_number(g_serial_attributes_tbl(46).COLUMN_VALUE),
2050 to_number(g_serial_attributes_tbl(47).COLUMN_VALUE),
2051 to_number(g_serial_attributes_tbl(48).COLUMN_VALUE),
2052 to_number(g_serial_attributes_tbl(49).COLUMN_VALUE),
2053 to_number(g_serial_attributes_tbl(50).COLUMN_VALUE),
2054 to_number(g_serial_attributes_tbl(51).COLUMN_VALUE),
2055 to_number(g_serial_attributes_tbl(52).COLUMN_VALUE),
2056 to_number(g_serial_attributes_tbl(53).COLUMN_VALUE),
2057 to_number(g_serial_attributes_tbl(54).COLUMN_VALUE),
2058 to_number(g_serial_attributes_tbl(55).COLUMN_VALUE) );
2059
2060 l_progress := '120';
2061
2062 if( l_status_id is not null ) then
2063 l_status_rec.update_method := INV_MATERIAL_STATUS_PUB.g_update_method_auto;
2064 l_status_rec.organization_id := p_organization_id;
2065 l_status_rec.inventory_item_id := p_inventory_item_id;
2066 l_status_rec.serial_number := p_serial_number;
2067 l_status_rec.status_id := l_status_id;
2068 l_status_rec.INITIAL_STATUS_FLAG := 'Y';
2069 INV_MATERIAL_STATUS_PKG.Insert_status_history(l_status_rec);
2070 end if;
2071
2072
2073 l_progress := '130';
2074
2075 -- End of API body.
2076 -- Standard check of p_commit.
2077 IF FND_API.To_Boolean( p_commit ) THEN
2078 COMMIT WORK;
2079 END IF;
2080
2081 x_return_status := FND_API.G_RET_STS_SUCCESS;
2082
2083 -- Standard call to get message count and if count is 1, get message info.
2084 FND_MSG_PUB.Count_And_Get
2085 ( p_encoded => FND_API.G_FALSE,
2086 p_count => x_msg_count ,
2087 p_data => x_msg_data
2088 );
2089
2090
2091 EXCEPTION
2092 WHEN OTHERS THEN
2093 ROLLBACK TO insertSerial_sv;
2094 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2095
2096 IF SQLCODE IS NOT NULL THEN
2097 IF (l_debug = 1) THEN
2098 print_debug('SQL Error : '||SQLERRM(SQLCODE)||' SQL Error code : '||SQLCODE, 1);
2099 END IF;
2100 END IF;
2101
2102 FND_MSG_PUB.Count_And_Get
2103 ( p_encoded => FND_API.G_FALSE,
2104 p_count => x_msg_count ,
2105 p_data => x_msg_data
2106 );
2107
2108 END insertSerial;
2109
2110
2111
2112 PROCEDURE insert_range_serial
2113 (p_commit IN VARCHAR2 := FND_API.G_FALSE,
2114 p_inventory_item_id IN NUMBER,
2115 p_organization_id IN NUMBER,
2116 p_from_serial_number IN VARCHAR2,
2117 p_to_serial_number IN VARCHAR2,
2118 p_initialization_date IN DATE,
2119 p_completion_date IN DATE,
2120 p_ship_date IN DATE,
2121 p_revision IN VARCHAR2,
2122 p_lot_number IN VARCHAR2,
2123 p_current_locator_id IN NUMBER,
2124 p_subinventory_code IN VARCHAR2,
2125 p_trx_src_id IN NUMBER,
2126 p_unit_vendor_id IN NUMBER,
2127 p_vendor_lot_number IN VARCHAR2,
2128 p_vendor_serial_number IN VARCHAR2,
2129 p_receipt_issue_type IN NUMBER,
2130 p_txn_src_id IN NUMBER,
2131 p_txn_src_name IN VARCHAR2,
2132 p_txn_src_type_id IN NUMBER,
2133 p_transaction_id IN NUMBER,
2134 p_current_status IN NUMBER,
2135 p_parent_item_id IN NUMBER,
2136 p_parent_serial_number IN VARCHAR2,
2137 p_cost_group_id IN NUMBER,
2138 p_serial_transaction_intf_id IN NUMBER,
2139 p_status_id IN NUMBER,
2140 p_inspection_status IN NUMBER,
2141 x_object_id OUT NOCOPY NUMBER,
2142 x_return_status OUT NOCOPY VARCHAR2,
2143 x_msg_count OUT NOCOPY NUMBER,
2144 x_msg_data OUT NOCOPY VARCHAR2)
2145 IS
2146 l_from_ser_number NUMBER;
2147 l_to_ser_number NUMBER;
2148 l_range_numbers NUMBER;
2149 l_temp_prefix VARCHAR2(30);
2150 l_cur_serial_number VARCHAR2(30);
2151 l_cur_ser_number NUMBER;
2152 l_object_id NUMBER;
2153 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2154 l_msg_count NUMBER;
2155 l_msg_data VARCHAR2(2000);
2156 l_current_status NUMBER;
2157 l_group_mark_id NUMBER;
2158
2159 l_progress varchar2(100);
2160
2161 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2162 l_serial_profile NUMBER := NVL(FND_PROFILE.VALUE('INV_RESTRICT_RCPT_SER'),2); --Bug#4122161
2163 BEGIN
2164
2165 IF (l_debug = 1) THEN
2169 l_progress := '10';
2166 print_debug('Inside Range Serial ',4);
2167 END IF;
2168
2170 IF (l_debug = 1) THEN
2171 print_debug('At Step = '|| l_progress,4);
2172 END IF;
2173
2174 SAVEPOINT SP_insert_range_serial;
2175
2176 x_return_status := FND_API.G_RET_STS_SUCCESS;
2177
2178
2179 -- get the number part of the from serial
2180 inv_validate.number_from_sequence(p_from_serial_number,
2181 l_temp_prefix,
2182 l_from_ser_number);
2183
2184 l_progress := '20';
2185 IF (l_debug = 1) THEN
2186 print_debug('At Step = '|| l_progress,4);
2187 END IF;
2188
2189 -- get the number part of the to serial
2190 inv_validate.number_from_sequence(p_to_serial_number,
2191 l_temp_prefix,
2192 l_to_ser_number);
2193
2194 l_progress := '30';
2195 IF (l_debug = 1) THEN
2196 print_debug('At Step = '|| l_progress,4);
2197 END IF;
2198
2199 -- total number of serials inserted into mtl_serial_numbers
2200 l_range_numbers := l_to_ser_number - l_from_ser_number + 1;
2201
2202 l_progress := '40';
2203 IF (l_debug = 1) THEN
2204 print_debug('At Step = '|| l_progress,4);
2205 END IF;
2206
2207 FOR i IN 1..l_range_numbers LOOP
2208 l_cur_ser_number := l_from_ser_number + i -1;
2209
2210 -- concatenate the serial number to be inserted
2211 --Bug 4539454: If serial number ends in a letter, l_from_ser_number=l_to_ser_number=-1
2212 --So, assign it directly to l_cur_serial_number
2213 --Handles a single serial number ending in a letter OR
2214 --range of serial numbers, each ending in a digit.
2215 IF ( (l_from_ser_number=-1) and (l_to_ser_number=-1) ) THEN
2216 l_cur_serial_number := p_from_serial_number;
2217 ELSE
2218 l_cur_serial_number := Substr(p_from_serial_number, 1,
2219 Length(p_from_serial_number) - Length(l_cur_ser_number))
2220 || l_cur_ser_number;
2221 END IF;
2222
2223 -- check the status code and group_mark_id
2224 BEGIN
2225 SELECT current_status, group_mark_id
2226 INTO l_current_status, l_group_mark_id
2227 FROM mtl_serial_numbers
2228 WHERE serial_number = l_cur_serial_number
2229 AND inventory_item_id = p_inventory_item_id;
2230
2231 EXCEPTION
2232 WHEN no_data_found THEN
2233 l_current_status := -1;
2234 l_group_mark_id := -1;
2235 WHEN OTHERS THEN
2236 NULL;
2237 END;
2238
2239 l_progress := '50';
2240 IF (l_debug = 1) THEN
2241 print_debug('At Step = '|| l_progress,4);
2242 print_debug('serial profile : '||l_serial_profile || ',current_status :'||l_current_status,4);
2243 END IF;
2244
2245 IF ( (l_current_status <> 5) OR (l_current_status = 5 AND l_group_mark_id > 0) )
2246 AND NOT (l_current_status = 4 AND l_serial_profile = 2 ) --Added bug#4122161.
2247 THEN
2248
2249 IF inv_serial_number_pub.is_serial_unique
2250 -- Need to do uniqueness check here
2251 -- If any serial is in use, then
2252 -- discard the entire range insertion.
2253 (p_org_id => p_organization_id,
2254 p_item_id => p_inventory_item_id,
2255 p_serial => l_cur_serial_number,
2256 x_proc_msg => l_msg_data) = 1 THEN
2257 FND_MESSAGE.SET_NAME('INV', 'INV_SERIAL_USED');
2258 FND_MSG_PUB.ADD;
2259 RAISE FND_API.G_EXC_ERROR;
2260 ELSE
2261 -- uniqueness check passed
2262 -- and it is not a pre-defined serial
2263
2264 IF (l_debug = 1) THEN
2265 print_debug('Calling Insert Serial for = '|| l_cur_serial_number ,4);
2266 END IF;
2267
2268 insertserial
2269 (p_commit => p_commit,
2270 p_inventory_item_id => p_inventory_item_id,
2271 p_organization_id => p_organization_id,
2272 p_serial_number => l_cur_serial_number,
2273 p_initialization_date => p_initialization_date,
2274 p_completion_date => p_completion_date,
2275 p_ship_date => p_ship_date,
2276 p_revision => p_revision,
2277 p_lot_number => p_lot_number,
2278 p_current_locator_id => p_current_locator_id,
2279 p_subinventory_code => p_subinventory_code,
2280 p_trx_src_id => p_trx_src_id,
2281 p_unit_vendor_id => p_unit_vendor_id,
2282 p_vendor_lot_number => p_vendor_lot_number,
2283 p_vendor_serial_number => p_vendor_serial_number,
2284 p_receipt_issue_type => p_receipt_issue_type,
2285 p_txn_src_id => p_txn_src_id,
2286 p_txn_src_name => p_txn_src_name,
2287 p_txn_src_type_id => p_txn_src_type_id,
2288 p_transaction_id => p_transaction_id,
2289 p_current_status => p_current_status,
2290 p_parent_item_id => p_parent_item_id,
2291 p_parent_serial_number => p_parent_serial_number,
2292 p_cost_group_id => p_cost_group_id,
2293 p_serial_transaction_intf_id => p_serial_transaction_intf_id,
2294 p_status_id => p_status_id,
2295 x_object_id => l_object_id,
2296 x_return_status => l_return_status,
2297 x_msg_count => l_msg_count,
2298 x_msg_data => l_msg_data);
2299
2300 IF( l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
2301 FND_MESSAGE.SET_NAME('INV', 'INV_SERIAL');
2302 FND_MSG_PUB.ADD;
2303 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2304 END IF;
2305 END IF;
2306 END IF;
2307 END LOOP;
2308
2309 x_return_status := FND_API.G_RET_STS_SUCCESS;
2310
2311 -- End of API body
2312 -- Standard check of p_commit.
2313 IF FND_API.To_Boolean( p_commit ) THEN
2314 COMMIT WORK;
2315 END IF;
2316
2317 -- Standard call to get message count and if count is 1,
2318 -- get message info.
2319 FND_MSG_PUB.Count_And_Get
2320 ( p_count => x_msg_count,
2321 p_data => x_msg_data
2322 );
2323
2324 EXCEPTION
2325 WHEN FND_API.G_EXC_ERROR THEN
2326 ROLLBACK TO SP_insert_range_serial;
2327 x_return_status := FND_API.G_RET_STS_ERROR;
2328 FND_MSG_PUB.Count_And_Get
2329 (p_count => x_msg_count,
2330 p_data => x_msg_data
2331 );
2332
2333 WHEN FND_API.g_exc_unexpected_error THEN
2334 ROLLBACK TO SP_insert_range_serial;
2335 x_return_status := FND_API.g_ret_sts_unexp_error;
2336 FND_MSG_PUB.Count_And_Get
2337 (p_count => x_msg_count,
2338 p_data => x_msg_data
2339 );
2340
2341 WHEN OTHERS THEN
2342 ROLLBACK TO SP_insert_range_serial;
2343 x_return_status := FND_API.g_ret_sts_unexp_error;
2344 FND_MSG_PUB.Count_And_Get
2345 (p_count => x_msg_count,
2346 p_data => x_msg_data
2347 );
2348
2349 END insert_range_serial;
2350
2351
2352
2353
2354 END WMS_ASN_LOT_ATT;