[Home] [Help]
PACKAGE BODY: APPS.INV_SERIAL_EO_PVT
Source
1 package body INV_SERIAL_EO_PVT as
2 /* $Header: INVSNEOB.pls 120.5 2005/08/01 16:56 janetli noship $ */
3 g_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 2);
4
5
6 PROCEDURE mydebug( p_msg IN VARCHAR2)
7 IS
8 BEGIN
9 IF (g_debug = 1) THEN
10 inv_mobile_helper_functions.tracelog(
11 p_err_msg => p_msg,
12 p_module => 'INV_SERIAL_EO_PVT',
13 p_level => 4);
14
15 END IF;
16 -- dbms_output.put_line( p_msg );
17 END mydebug;
18
19 procedure preprocess_serial(x_return_status out nocopy VARCHAR2
20 , x_msg_count out nocopy NUMBER
21 , x_msg_data out nocopy VARCHAR2
22 , p_inventory_item_id NUMBER
23 , p_organization_id NUMBER
24 , p_lot_number VARCHAR2
25 , p_parent_lot_number VARCHAR2
26 , p_from_serial_number VARCHAR2
27 , x_is_new_serial out nocopy VARCHAR2
28 , p_revision VARCHAR2
29 , p_to_serial_number VARCHAR2
30 ) IS
31 l_serial_number VARCHAR2(30);
32 x_object_id NUMBER;
33 BEGIN
34
35 SAVEPOINT inv_new_serial;
36 x_return_status := fnd_api.g_ret_sts_success;
37 x_is_new_serial := 'N';
38
39 BEGIN
40 select serial_number
41 into l_serial_number
42 from mtl_serial_numbers
43 where current_organization_id = p_organization_id
44 and inventory_item_id = p_inventory_item_id
45 and serial_number = p_from_serial_number;
46 -- this is an existing serial, nothing to do
47 return;
48 EXCEPTION
49 WHEN NO_DATA_FOUND THEN
50 x_is_new_serial := 'Y';
51 END;
52
53 -- start to create a new serial number
54 /*
55 inv_serial_number_pub.insert_range_serial(
56 p_api_version => 1.0
57 , p_validation_level => fnd_api.g_valid_level_full
58 , p_inventory_item_id => p_inventory_item_id
59 , p_organization_id => p_organization_id
60 , p_from_serial_number => p_from_serial_number
61 , p_to_serial_number => p_to_serial_number
62 , p_initialization_date => null
63 , p_completion_date => null
64 , p_ship_date =>null
65 , p_revision =>null -- p_revision
66 , p_lot_number =>p_lot_number
67 , p_current_locator_id =>null
68 , p_subinventory_code =>null
69 , p_trx_src_id =>null
70 , p_unit_vendor_id =>null
71 , p_vendor_lot_number =>null
72 , p_vendor_serial_number =>null
73 , p_receipt_issue_type =>null
74 , p_txn_src_id =>null
75 , p_txn_src_name =>null
76 , p_txn_src_type_id =>null
77 , p_transaction_id =>null
78 , p_current_status =>1 -- current status, need to verify with osfm
79 , p_parent_item_id => null
80 , p_parent_serial_number =>null
81 , p_cost_group_id =>null
82 , p_transaction_action_id =>null
83 , p_transaction_temp_id =>null
84 , p_status_id =>1
85 , p_inspection_status =>null
86 , x_object_id =>x_object_id
87 , x_return_status =>x_return_status
88 , x_msg_count =>x_msg_count
89 , x_msg_data =>x_msg_data
90 );*/
91
92 inv_serial_number_pub.insertserial(
93 p_api_version =>1.0
94 , p_validation_level => fnd_api.g_valid_level_full
95 , p_inventory_item_id =>p_inventory_item_id
96 , p_organization_id =>p_organization_id
97 , p_serial_number =>p_from_serial_number
98 , p_current_status =>1
99 , p_group_mark_id =>null --
100 , p_lot_number =>p_lot_number
101 , x_return_status =>x_return_status
102 , x_msg_count =>x_msg_count
103 , x_msg_data =>x_msg_data
104 );
105
106 IF g_debug = 1 THEN
107 mydebug('Program insert_range_serial ' || x_return_status);
108 END IF;
109 IF x_return_status = fnd_api.g_ret_sts_error THEN
110 IF g_debug = 1 THEN
111 mydebug('Program insert_range_serial has failed with a user defined exception');
112 END IF;
113 RAISE g_exc_error;
114 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
115 IF g_debug = 1 THEN
116 mydebug('Program insert_range_serial has failed with a Unexpected exception');
117 END IF;
118 FND_MESSAGE.SET_NAME('INV','INV_PROGRAM_ERROR');
119 FND_MESSAGE.SET_TOKEN('PROG_NAME','insert_range_serial');
120 fnd_msg_pub.ADD;
121 RAISE g_exc_unexpected_error;
122 END IF;
123
124 mydebug('End of the program insert_range_serial. Program has completed successfully ');
125
126
127
128 EXCEPTION
129 WHEN NO_DATA_FOUND THEN
130 x_return_status := fnd_api.g_ret_sts_error;
131 ROLLBACK TO inv_new_serial;
132 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
133 if( x_msg_count > 1 ) then
134 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE);
135 end if;
136 mydebug('In No data found ' || SQLERRM);
137 WHEN g_exc_error THEN
138 x_return_status := fnd_api.g_ret_sts_error;
139 ROLLBACK TO inv_new_serial;
140 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
141 if( x_msg_count > 1 ) then
142 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE);
143 end if;
144 mydebug('In g_exc_error ' || SQLERRM);
145 WHEN g_exc_unexpected_error THEN
146 x_return_status := fnd_api.g_ret_sts_unexp_error;
147 ROLLBACK TO inv_new_serial;
148 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
149 if( x_msg_count > 1 ) then
150 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE);
151 end if;
152 mydebug('In g_exc_unexpected_error ' || SQLERRM);
153 WHEN OTHERS THEN
154 x_return_status := fnd_api.g_ret_sts_unexp_error;
155 ROLLBACK TO inv_new_serial;
156 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
157 if( x_msg_count > 1 ) then
158 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE);
159 end if;
160 mydebug('In others ' || SQLERRM);
161
162 END preprocess_serial;
163
164 procedure delete_serial(x_return_status out nocopy VARCHAR2
165 , x_msg_count out nocopy NUMBER
166 , x_msg_data out nocopy VARCHAR2
167 , p_inventory_item_id NUMBER
168 , p_organization_id NUMBER
169 , p_from_serial_number VARCHAR2
170 , p_to_serial_number VARCHAR2
171 )
172 IS
173 BEGIN
174
175 delete from mtl_serial_numbers
176 where current_organization_id = p_organization_id
177 and inventory_item_id = p_inventory_item_id
178 and serial_number between p_from_serial_number and p_to_serial_number;
179
180 EXCEPTION
181 WHEN OTHERS THEN
182 x_return_status := fnd_api.g_ret_sts_unexp_error;
183 mydebug('In g_exc_unexpected_error ' || SQLERRM);
184 END delete_serial;
185
186
187 procedure rosetta_table_copy_in_p0(t out nocopy inv_lot_api_pub.char_tbl, a0 JTF_VARCHAR2_TABLE_1000) as
188 ddindx binary_integer; indx binary_integer;
189 begin
190 if a0 is not null and a0.count > 0 then
191 if a0.count > 0 then
192 indx := a0.first;
193 ddindx := 1;
194 while true loop
195 t(ddindx) := a0(indx);
196 ddindx := ddindx+1;
197 if a0.last =indx
198 then exit;
199 end if;
200 indx := a0.next(indx);
201 end loop;
202 end if;
203 end if;
204 end rosetta_table_copy_in_p0;
205 procedure rosetta_table_copy_out_p0(t inv_lot_api_pub.char_tbl, a0 out nocopy JTF_VARCHAR2_TABLE_1000) as
206 ddindx binary_integer; indx binary_integer;
207 begin
208 if t is null or t.count = 0 then
209 a0 := JTF_VARCHAR2_TABLE_1000();
210 else
211 a0 := JTF_VARCHAR2_TABLE_1000();
212 if t.count > 0 then
213 a0.extend(t.count);
214 ddindx := t.first;
215 indx := 1;
216 while true loop
217 a0(indx) := t(ddindx);
218 indx := indx+1;
219 if t.last =ddindx
220 then exit;
221 end if;
222 ddindx := t.next(ddindx);
223 end loop;
224 end if;
225 end if;
226 end rosetta_table_copy_out_p0;
227
228 procedure rosetta_table_copy_in_p1(t out nocopy inv_lot_api_pub.number_tbl, a0 JTF_NUMBER_TABLE) as
229 ddindx binary_integer; indx binary_integer;
230 begin
231 if a0 is not null and a0.count > 0 then
232 if a0.count > 0 then
233 indx := a0.first;
234 ddindx := 1;
235 while true loop
236 t(ddindx) := a0(indx);
237 ddindx := ddindx+1;
238 if a0.last =indx
239 then exit;
240 end if;
241 indx := a0.next(indx);
242 end loop;
243 end if;
244 end if;
245 end rosetta_table_copy_in_p1;
246 procedure rosetta_table_copy_out_p1(t inv_lot_api_pub.number_tbl, a0 out nocopy JTF_NUMBER_TABLE) as
247 ddindx binary_integer; indx binary_integer;
248 begin
249 if t is null or t.count = 0 then
250 a0 := JTF_NUMBER_TABLE();
251 else
252 a0 := JTF_NUMBER_TABLE();
253 if t.count > 0 then
254 a0.extend(t.count);
255 ddindx := t.first;
256 indx := 1;
257 while true loop
258 a0(indx) := t(ddindx);
259 indx := indx+1;
260 if t.last =ddindx
261 then exit;
262 end if;
263 ddindx := t.next(ddindx);
264 end loop;
265 end if;
266 end if;
267 end rosetta_table_copy_out_p1;
268
269 procedure rosetta_table_copy_in_p2(t out nocopy inv_lot_api_pub.date_tbl, a0 JTF_DATE_TABLE) as
270 ddindx binary_integer; indx binary_integer;
271 begin
272 if a0 is not null and a0.count > 0 then
273 if a0.count > 0 then
274 indx := a0.first;
275 ddindx := 1;
276 while true loop
277 t(ddindx) := a0(indx);
278 ddindx := ddindx+1;
279 if a0.last =indx
280 then exit;
281 end if;
282 indx := a0.next(indx);
283 end loop;
284 end if;
285 end if;
286 end rosetta_table_copy_in_p2;
287 procedure rosetta_table_copy_out_p2(t inv_lot_api_pub.date_tbl, a0 out nocopy JTF_DATE_TABLE) as
288 ddindx binary_integer; indx binary_integer;
289 begin
290 if t is null or t.count = 0 then
291 a0 := JTF_DATE_TABLE();
292 else
293 a0 := JTF_DATE_TABLE();
294 if t.count > 0 then
295 a0.extend(t.count);
296 ddindx := t.first;
297 indx := 1;
298 while true loop
299 a0(indx) := t(ddindx);
300 indx := indx+1;
301 if t.last =ddindx
302 then exit;
303 end if;
304 ddindx := t.next(ddindx);
305 end loop;
306 end if;
307 end if;
308 end rosetta_table_copy_out_p2;
309
310
311 procedure insert_serial(p_api_version NUMBER
312 , p_init_msg_list VARCHAR2
313 , p_commit VARCHAR2
314 , p_validation_level NUMBER
315 , p_inventory_item_id NUMBER
316 , p_organization_id NUMBER
317 , p_serial_number VARCHAR2
318 , p_initialization_date DATE
319 , p_completion_date DATE
320 , p_ship_date DATE
321 , p_revision VARCHAR2
322 , p_lot_number VARCHAR2
323 , p_current_locator_id NUMBER
324 , p_subinventory_code VARCHAR2
325 , p_trx_src_id NUMBER
326 , p_unit_vendor_id NUMBER
327 , p_vendor_lot_number VARCHAR2
328 , p_vendor_serial_number VARCHAR2
329 , p_receipt_issue_type NUMBER
330 , p_txn_src_id NUMBER
331 , p_txn_src_name VARCHAR2
332 , p_txn_src_type_id NUMBER
333 , p_transaction_id NUMBER
334 , p_current_status NUMBER
335 , p_parent_item_id NUMBER
336 , p_parent_serial_number VARCHAR2
337 , p_cost_group_id NUMBER
338 , p_transaction_action_id NUMBER
339 , p_transaction_temp_id NUMBER
340 , p_status_id NUMBER
341 , x_object_id out nocopy NUMBER
342 , x_return_status out nocopy VARCHAR2
343 , x_msg_count out nocopy NUMBER
344 , x_msg_data out nocopy VARCHAR2
345 , p_organization_type NUMBER
346 , p_owning_org_id NUMBER
347 , p_owning_tp_type NUMBER
348 , p_planning_org_id NUMBER
349 , p_planning_tp_type NUMBER
350 , p_wip_entity_id NUMBER
351 , p_operation_seq_num NUMBER
352 , p_intraoperation_step_type NUMBER
353 , p_attribute_category VARCHAR2
354 , p_attributes_tbl JTF_VARCHAR2_TABLE_1000
355 , p_serial_attribute_category VARCHAR2
356 , p_c_attributes_tbl JTF_VARCHAR2_TABLE_1000
357 , p_n_attributes_tbl JTF_NUMBER_TABLE
358 , p_d_attributes_tbl JTF_DATE_TABLE
359 , p_origination_date DATE
360 , p_territory_code VARCHAR2
361 )
362
363 as
364 ddp_attributes_tbl inv_lot_api_pub.char_tbl;
365 ddp_c_attributes_tbl inv_lot_api_pub.char_tbl;
366 ddp_n_attributes_tbl inv_lot_api_pub.number_tbl;
367 ddp_d_attributes_tbl inv_lot_api_pub.date_tbl;
368 ddindx binary_integer; indx binary_integer;
369 begin
370
371 -- copy data to the local IN or IN-OUT args, if any
372
373
374
375
376
377
378
379 INV_LOT_EO_PVT.rosetta_table_copy_in_p0(ddp_attributes_tbl, p_attributes_tbl);
380
381 INV_LOT_EO_PVT.rosetta_table_copy_in_p0(ddp_c_attributes_tbl, p_c_attributes_tbl);
382
383 INV_LOT_EO_PVT.rosetta_table_copy_in_p1(ddp_n_attributes_tbl, p_n_attributes_tbl);
384
385 INV_LOT_EO_PVT.rosetta_table_copy_in_p2(ddp_d_attributes_tbl, p_d_attributes_tbl);
386
387
388
389
390 -- here's the delegated call to the old PL/SQL routine
391 inv_serial_number_pub.insertserial(p_api_version,
392 p_init_msg_list,
393 p_commit,
394 p_validation_level,
395 p_inventory_item_id,
396 p_organization_id,
397 p_serial_number,
398 p_initialization_date,
399 p_completion_date,
400 p_ship_date,
401 p_revision,
402 p_lot_number,
403 p_current_locator_id,
404 p_subinventory_code,
405 p_trx_src_id,
406 p_unit_vendor_id,
407 p_vendor_lot_number,
408 p_vendor_serial_number,
409 p_receipt_issue_type,
410 p_txn_src_id,
411 p_txn_src_name,
412 p_txn_src_type_id,
413 p_transaction_id,
414 p_current_status,
415 p_parent_item_id,
416 p_parent_serial_number,
417 p_cost_group_id,
418 p_transaction_action_id,
419 p_transaction_temp_id,
420 p_status_id,
421 x_object_id,
422 x_return_status,
423 x_msg_count,
424 x_msg_data,
425 p_organization_type,
426 p_owning_org_id,
427 p_owning_tp_type,
428 p_planning_org_id,
429 p_planning_tp_type,
430 p_wip_entity_id,
431 p_operation_seq_num,
432 p_intraoperation_step_type);
433
434 -- calling validate_update_serial_att to update attributes
435 /* p_attribute_category,
436 ddp_attributes_tbl,
437 ddp_c_attributes_tbl,
438 ddp_n_attributes_tbl,
439 ddp_d_attributes_tbl,
440 p_origination_date,
441 p_territory_code); */
442
443 -- copy data back from the local variables to OUT or IN-OUT args, if any
444
445
446
447
448
449 end;
450
451 procedure update_serial(p_api_version NUMBER
452 , p_init_msg_list VARCHAR2
453 , p_commit VARCHAR2
454 , p_validation_level NUMBER
455 , p_inventory_item_id NUMBER
456 , p_organization_id NUMBER
457 , p_serial_number VARCHAR2
458 , p_initialization_date DATE
459 , p_completion_date DATE
460 , p_ship_date DATE
461 , p_revision VARCHAR2
462 , p_lot_number VARCHAR2
463 , p_current_locator_id NUMBER
464 , p_subinventory_code VARCHAR2
465 , p_trx_src_id NUMBER
466 , p_unit_vendor_id NUMBER
467 , p_vendor_lot_number VARCHAR2
468 , p_vendor_serial_number VARCHAR2
469 , p_receipt_issue_type NUMBER
470 , p_txn_src_id NUMBER
471 , p_txn_src_name VARCHAR2
472 , p_txn_src_type_id NUMBER
473 , p_current_status NUMBER
474 , p_parent_item_id NUMBER
475 , p_parent_serial_number VARCHAR2
476 , p_serial_temp_id NUMBER
477 , p_last_status NUMBER
478 , p_status_id NUMBER
479 , x_object_id out nocopy NUMBER
480 , x_return_status out nocopy VARCHAR2
481 , x_msg_count out nocopy NUMBER
482 , x_msg_data out nocopy VARCHAR2
483 , p_organization_type NUMBER
484 , p_owning_org_id NUMBER
485 , p_owning_tp_type NUMBER
486 , p_planning_org_id NUMBER
487 , p_planning_tp_type NUMBER
488 , p_transaction_action_id NUMBER
489 , p_wip_entity_id NUMBER
490 , p_operation_seq_num NUMBER
491 , p_intraoperation_step_type NUMBER
492 , p_line_mark_id NUMBER
493 , p_attribute_category VARCHAR2
494 , p_attributes_tbl JTF_VARCHAR2_TABLE_1000
495 , p_serial_attribute_category VARCHAR2
496 , p_c_attributes_tbl JTF_VARCHAR2_TABLE_1000
497 , p_n_attributes_tbl JTF_NUMBER_TABLE
498 , p_d_attributes_tbl JTF_DATE_TABLE
499 , p_origination_date DATE
500 , p_territory_code VARCHAR2
501 )
502
503 as
504 ddp_attributes_tbl inv_lot_api_pub.char_tbl;
505 ddp_c_attributes_tbl inv_lot_api_pub.char_tbl;
506 ddp_n_attributes_tbl inv_lot_api_pub.number_tbl;
507 ddp_d_attributes_tbl inv_lot_api_pub.date_tbl;
508 ddindx binary_integer; indx binary_integer;
509
510 l_serial_attributes_tbl inv_lot_sel_attr.lot_sel_attributes_tbl_type;
511 l_validation_status VARCHAR2(1);
512 begin
513
514 SAVEPOINT inv_update_serial;
515 x_return_status := fnd_api.g_ret_sts_success;
516
517 -- copy data to the local IN or IN-OUT args, if any
518
519
520 mydebug('entering update_serial');
521
522 rosetta_table_copy_in_p0(ddp_attributes_tbl, p_attributes_tbl);
523
524 rosetta_table_copy_in_p0(ddp_c_attributes_tbl, p_c_attributes_tbl);
525
526 rosetta_table_copy_in_p1(ddp_n_attributes_tbl, p_n_attributes_tbl);
527
528 rosetta_table_copy_in_p2(ddp_d_attributes_tbl, p_d_attributes_tbl);
529
530
531 mydebug('calling updateserial');
532
533 -- here's the delegated call to the old PL/SQL routine
534 inv_serial_number_pub.updateserial(p_api_version,
535 p_init_msg_list,
536 p_commit,
537 p_validation_level,
538 p_inventory_item_id,
539 p_organization_id,
540 p_serial_number,
541 p_initialization_date,
542 p_completion_date,
543 p_ship_date,
544 p_revision,
545 p_lot_number,
546 p_current_locator_id,
547 p_subinventory_code,
548 p_trx_src_id,
549 p_unit_vendor_id,
550 p_vendor_lot_number,
551 p_vendor_serial_number,
552 p_receipt_issue_type,
553 p_txn_src_id,
554 p_txn_src_name,
555 p_txn_src_type_id,
556 p_current_status,
557 p_parent_item_id,
558 p_parent_serial_number,
559 p_serial_temp_id,
560 p_last_status,
561 p_status_id,
562 x_object_id,
563 x_return_status,
564 x_msg_count,
565 x_msg_data,
566 p_organization_type,
567 p_owning_org_id,
568 p_owning_tp_type,
569 p_planning_org_id,
570 p_planning_tp_type,
571 p_transaction_action_id,
572 p_wip_entity_id,
573 p_operation_seq_num,
574 p_intraoperation_step_type,
575 p_line_mark_id);
576
577 mydebug('after calling updateSerial');
578 IF x_return_status = fnd_api.g_ret_sts_error THEN
579 IF g_debug = 1 THEN
580 mydebug('Program insert_range_serial has failed with a user defined exception');
581 END IF;
582 RAISE g_exc_error;
583 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
584 IF g_debug = 1 THEN
585 mydebug('Program insert_range_serial has failed with a Unexpected exception');
586 END IF;
587 FND_MESSAGE.SET_NAME('INV','INV_PROGRAM_ERROR');
588 FND_MESSAGE.SET_TOKEN('PROG_NAME','insert_range_serial');
589 fnd_msg_pub.ADD;
590 RAISE g_exc_unexpected_error;
591 END IF;
592
593 -- then call the update attribute routine to update the attributes
594 --populate attributes table
595 l_serial_attributes_tbl(1).column_name := 'SERIAL_ATTRIBUTE_CATEGORY';
596 l_serial_attributes_tbl(1).column_value := p_serial_attribute_category;
597 l_serial_attributes_tbl(2).column_name := 'ORIGINATION_DATE';
598 l_serial_attributes_tbl(2).column_value := p_origination_date;
599 l_serial_attributes_tbl(3).column_name := 'C_ATTRIBUTE1';
600 l_serial_attributes_tbl(3).column_value := ddp_c_attributes_tbl(1);
601 l_serial_attributes_tbl(4).column_name := 'C_ATTRIBUTE2';
602 l_serial_attributes_tbl(4).column_value := ddp_c_attributes_tbl(2);
603 l_serial_attributes_tbl(5).column_name := 'C_ATTRIBUTE3';
604 l_serial_attributes_tbl(5).column_value := ddp_c_attributes_tbl(3);
605 l_serial_attributes_tbl(6).column_name := 'C_ATTRIBUTE4';
606 l_serial_attributes_tbl(6).column_value := ddp_c_attributes_tbl(4);
607 l_serial_attributes_tbl(7).column_name := 'C_ATTRIBUTE5';
608 l_serial_attributes_tbl(7).column_value := ddp_c_attributes_tbl(5);
609 l_serial_attributes_tbl(8).column_name := 'C_ATTRIBUTE6';
610 l_serial_attributes_tbl(8).column_value := ddp_c_attributes_tbl(6);
611 l_serial_attributes_tbl(9).column_name := 'C_ATTRIBUTE7';
612 l_serial_attributes_tbl(9).column_value := ddp_c_attributes_tbl(7);
613 l_serial_attributes_tbl(10).column_name := 'C_ATTRIBUTE8';
614 l_serial_attributes_tbl(10).column_value := ddp_c_attributes_tbl(8);
615 l_serial_attributes_tbl(11).column_name := 'C_ATTRIBUTE9';
616 l_serial_attributes_tbl(11).column_value := ddp_c_attributes_tbl(9);
617 l_serial_attributes_tbl(12).column_name := 'C_ATTRIBUTE10';
618 l_serial_attributes_tbl(12).column_value := ddp_c_attributes_tbl(10);
619 l_serial_attributes_tbl(13).column_name := 'C_ATTRIBUTE11';
620 l_serial_attributes_tbl(13).column_value := ddp_c_attributes_tbl(11);
621 l_serial_attributes_tbl(14).column_name := 'C_ATTRIBUTE12';
622 l_serial_attributes_tbl(14).column_value := ddp_c_attributes_tbl(12);
623 l_serial_attributes_tbl(15).column_name := 'C_ATTRIBUTE13';
624 l_serial_attributes_tbl(15).column_value := ddp_c_attributes_tbl(13);
625 l_serial_attributes_tbl(16).column_name := 'C_ATTRIBUTE14';
626 l_serial_attributes_tbl(16).column_value := ddp_c_attributes_tbl(14);
627 l_serial_attributes_tbl(17).column_name := 'C_ATTRIBUTE15';
628 l_serial_attributes_tbl(17).column_value := ddp_c_attributes_tbl(15);
629 l_serial_attributes_tbl(18).column_name := 'C_ATTRIBUTE16';
630 l_serial_attributes_tbl(18).column_value := ddp_c_attributes_tbl(16);
631 l_serial_attributes_tbl(19).column_name := 'C_ATTRIBUTE17';
632 l_serial_attributes_tbl(19).column_value := ddp_c_attributes_tbl(17);
633 l_serial_attributes_tbl(20).column_name := 'C_ATTRIBUTE18';
634 l_serial_attributes_tbl(20).column_value := ddp_c_attributes_tbl(18);
635 l_serial_attributes_tbl(21).column_name := 'C_ATTRIBUTE19';
636 l_serial_attributes_tbl(21).column_value := ddp_c_attributes_tbl(19);
637 l_serial_attributes_tbl(22).column_name := 'C_ATTRIBUTE20';
638 l_serial_attributes_tbl(22).column_value := ddp_c_attributes_tbl(20);
639 l_serial_attributes_tbl(23).column_name := 'D_ATTRIBUTE1';
640 l_serial_attributes_tbl(23).column_value := ddp_d_attributes_tbl(1);
641 l_serial_attributes_tbl(24).column_name := 'D_ATTRIBUTE2';
642 l_serial_attributes_tbl(24).column_value := ddp_d_attributes_tbl(2);
643 l_serial_attributes_tbl(25).column_name := 'D_ATTRIBUTE3';
644 l_serial_attributes_tbl(25).column_value := ddp_d_attributes_tbl(3);
645 l_serial_attributes_tbl(26).column_name := 'D_ATTRIBUTE4';
646 l_serial_attributes_tbl(26).column_value := ddp_d_attributes_tbl(4);
647 l_serial_attributes_tbl(27).column_name := 'D_ATTRIBUTE5';
648 l_serial_attributes_tbl(27).column_value := ddp_d_attributes_tbl(5);
649 l_serial_attributes_tbl(28).column_name := 'D_ATTRIBUTE6';
650 l_serial_attributes_tbl(28).column_value := ddp_d_attributes_tbl(6);
651 l_serial_attributes_tbl(29).column_name := 'D_ATTRIBUTE7';
652 l_serial_attributes_tbl(29).column_value := ddp_d_attributes_tbl(7);
653 l_serial_attributes_tbl(30).column_name := 'D_ATTRIBUTE8';
654 l_serial_attributes_tbl(30).column_value := ddp_d_attributes_tbl(8);
655 l_serial_attributes_tbl(31).column_name := 'D_ATTRIBUTE9';
656 l_serial_attributes_tbl(31).column_value := ddp_d_attributes_tbl(9);
657 l_serial_attributes_tbl(32).column_name := 'D_ATTRIBUTE10';
658 l_serial_attributes_tbl(32).column_value := ddp_d_attributes_tbl(10);
659 l_serial_attributes_tbl(33).column_name := 'N_ATTRIBUTE1';
660 l_serial_attributes_tbl(33).column_value := ddp_n_attributes_tbl(1);
661 l_serial_attributes_tbl(34).column_name := 'N_ATTRIBUTE2';
662 l_serial_attributes_tbl(34).column_value := ddp_n_attributes_tbl(2);
663 l_serial_attributes_tbl(35).column_name := 'N_ATTRIBUTE3';
664 l_serial_attributes_tbl(35).column_value := ddp_n_attributes_tbl(3);
665 l_serial_attributes_tbl(36).column_name := 'N_ATTRIBUTE4';
666 l_serial_attributes_tbl(36).column_value := ddp_n_attributes_tbl(4);
667 l_serial_attributes_tbl(37).column_name := 'N_ATTRIBUTE5';
668 l_serial_attributes_tbl(37).column_value := ddp_n_attributes_tbl(5);
669 l_serial_attributes_tbl(38).column_name := 'N_ATTRIBUTE6';
670 l_serial_attributes_tbl(38).column_value := ddp_n_attributes_tbl(6);
671 l_serial_attributes_tbl(39).column_name := 'N_ATTRIBUTE7';
672 l_serial_attributes_tbl(39).column_value := ddp_n_attributes_tbl(7);
673 l_serial_attributes_tbl(40).column_name := 'N_ATTRIBUTE8';
674 l_serial_attributes_tbl(40).column_value := ddp_n_attributes_tbl(8);
675 l_serial_attributes_tbl(41).column_name := 'N_ATTRIBUTE9';
676 l_serial_attributes_tbl(41).column_value := ddp_n_attributes_tbl(9);
677 l_serial_attributes_tbl(42).column_name := 'N_ATTRIBUTE10';
678 l_serial_attributes_tbl(42).column_value := ddp_n_attributes_tbl(10);
679 l_serial_attributes_tbl(43).column_name := 'STATUS_ID';
680 l_serial_attributes_tbl(43).column_value := p_status_id;
681 l_serial_attributes_tbl(44).column_name := 'TERRITORY_CODE';
682 l_serial_attributes_tbl(44).column_value := p_territory_code;
683
684 mydebug('calling validate_update_serial_att');
685
686 --validate and update the attributes.
687 inv_serial_number_pub.validate_update_serial_att
688 (x_return_status => x_return_status,
689 x_msg_count => x_msg_count,
690 x_msg_data => x_msg_data,
691 x_validation_status => l_validation_status,
692 p_serial_number => p_serial_number,
693 p_organization_id => p_organization_id,
694 p_inventory_item_id => p_inventory_item_id,
695 p_serial_att_tbl => l_serial_attributes_tbl,
696 p_validate_only => FALSE
697 );
698
699 IF (l_validation_status <> 'Y'
700 OR x_return_status <> 'S') THEN
701 --raise error
702 fnd_message.set_name ('INV' , 'INV_FAIL_VALIDATE_SERIAL' );
703 fnd_msg_pub.ADD;
704
705 RAISE fnd_api.g_exc_error;
706 END IF;
707
708 mydebug('After calling validate_update_serial_att');
709
710 -- update the DFF
711 mydebug('update_attributes');
712 update mtl_serial_numbers
713 set attribute1= ddp_attributes_tbl(1)
714 , attribute2= ddp_attributes_tbl(2)
715 , attribute3= ddp_attributes_tbl(3)
716 , attribute4= ddp_attributes_tbl(4)
717 , attribute5= ddp_attributes_tbl(5)
718 , attribute6= ddp_attributes_tbl(6)
719 , attribute7= ddp_attributes_tbl(7)
720 , attribute8= ddp_attributes_tbl(8)
721 , attribute9= ddp_attributes_tbl(9)
722 , attribute10= ddp_attributes_tbl(10)
723 , attribute11= ddp_attributes_tbl(11)
724 , attribute12= ddp_attributes_tbl(12)
725 , attribute13= ddp_attributes_tbl(13)
726 , attribute14= ddp_attributes_tbl(14)
727 , attribute15= ddp_attributes_tbl(15)
728 , attribute_category = p_attribute_category
729 WHERE current_organization_id = p_organization_id
730 and inventory_item_id = p_inventory_item_id
731 and serial_number = p_serial_number;
732
733 -- copy data back from the local variables to OUT or IN-OUT args, if any
734
735
736 EXCEPTION
737 WHEN NO_DATA_FOUND THEN
738 x_return_status := fnd_api.g_ret_sts_error;
739 ROLLBACK TO inv_update_serial;
740 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
741 if( x_msg_count > 1 ) then
742 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE);
743 end if;
744 mydebug('In No data found ' || SQLERRM);
745 WHEN g_exc_error THEN
746 x_return_status := fnd_api.g_ret_sts_error;
747 ROLLBACK TO inv_update_serial;
748 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
749 if( x_msg_count > 1 ) then
750 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE);
751 end if;
752 mydebug('In g_exc_error ' || SQLERRM);
753 WHEN g_exc_unexpected_error THEN
754 x_return_status := fnd_api.g_ret_sts_unexp_error;
755 ROLLBACK TO inv_update_serial;
756 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
757 if( x_msg_count > 1 ) then
758 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE);
759 end if;
760 mydebug('In g_exc_unexpected_error ' || SQLERRM);
761 WHEN OTHERS THEN
762 x_return_status := fnd_api.g_ret_sts_unexp_error;
763 ROLLBACK TO inv_update_serial;
764 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
765 if( x_msg_count > 1 ) then
766 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE);
767 end if;
768 mydebug('In others ' || SQLERRM);
769
770
771
772
773
774
775
776 end;
777
778 end INV_SERIAL_EO_PVT;