[Home] [Help]
PACKAGE BODY: APPS.INV_LOT_EO_PVT
Source
1 package body INV_LOT_EO_PVT as
2 /* $Header: INVLTEOB.pls 120.4 2006/09/15 23:52:48 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_LOT_EO_PVT',
13 p_level => 4);
14
15 END IF;
16 -- dbms_output.put_line( p_msg );
17 END mydebug;
18
19 procedure preprocess_lot(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_reference_inventory_item_id NUMBER
27 , p_reference_lot_number VARCHAR2 -- OSFM need this to inherite the attributes
28 , p_source NUMBER
29 , x_is_new_lot out nocopy VARCHAR2
30 ) IS
31 l_in_lot_rec MTL_LOT_NUMBERS%ROWTYPE;
32 l_out_lot_rec MTL_LOT_NUMBERS%ROWTYPE;
33 x_lot_rec MTL_LOT_NUMBERS%ROWTYPE;
34 l_api_version NUMBER;
35 l_init_msg_list VARCHAR2(100);
36 l_commit VARCHAR2(100);
37 l_validation_level NUMBER;
38 l_origin_txn_id NUMBER;
39 l_source NUMBER;
40 l_return_status VARCHAR2(1) ;
41 l_msg_data VARCHAR2(3000) ;
42 l_msg_count NUMBER ;
43 l_row_id ROWID ;
44 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
45 l_lot_number VARCHAR2(80);
46
47 l_user_context VARCHAR2(30);
48 l_mapping_context VARCHAR2(30);
49
50 BEGIN
51 SAVEPOINT inv_new_lot;
52 x_return_status := fnd_api.g_ret_sts_success;
53 x_is_new_lot := 'N';
54
55 BEGIN
56 select lot_number
57 into l_lot_number
58 from mtl_lot_numbers
59 where organization_id = p_organization_id
60 and inventory_item_id = p_inventory_item_id
61 and lot_number = p_lot_number;
62 -- this is an existing lot, nothing to do
63 return;
64
65 EXCEPTION
66 WHEN NO_DATA_FOUND THEN
67 x_is_new_lot := 'Y';
68 END;
69
70
71 /* Populating the variables and calling the new overloaded API */
72
73 l_in_lot_rec.inventory_item_id := p_inventory_item_id;
74 l_in_lot_rec.organization_id := p_organization_id;
75 l_in_lot_rec.lot_number := p_lot_number;
76 l_in_lot_rec.parent_lot_number := p_parent_lot_number;
77 l_source := p_source;
78 l_api_version := 1.0;
79 l_init_msg_list := fnd_api.g_false;
80 l_commit := fnd_api.g_false;
81 -- l_validation_level := fnd_api.g_valid_level_full;
82 l_validation_level := fnd_api.g_valid_level_none; -- for testing
83
84 l_origin_txn_id := NULL;
85
86 -- copy the lot attributes from the reference lot to the current lot
87 -- osfm's requirements
88
89 -- get the lot context mapping defination
90 inv_lot_sel_attr.get_context_code
91 (
92 l_mapping_context,
93 p_organization_id,
94 p_inventory_item_id,
95 'Lot Attributes'
96 );
97 mydebug('defined context mapping:'||l_mapping_context);
98
99
100 if p_reference_lot_number is not null and p_reference_inventory_item_id is not null then
101 mydebug('copy the attributes from lot '||p_reference_lot_number);
102 BEGIN
103 -- get the lot attribute context
104 SELECT lot_attribute_category
105 INTO l_user_context
106 FROM mtl_lot_numbers
107 WHERE organization_id = p_organization_id
108 AND inventory_item_id = p_reference_inventory_item_id
109 AND lot_number = p_reference_lot_number;
110 mydebug('reference lot context:'||l_user_context);
111
112 if (l_user_context is null or
113 (l_user_context is not null and
114 (l_mapping_context is null or l_user_context= l_mapping_context))) THEN
115
116 SELECT vendor_id ,
117 grade_code,
118 origination_date,
119 date_code,
120 status_id,
121 change_date,
122 age,
123 retest_date,
124 maturity_date,
125 lot_attribute_category,
126 item_size,
127 color,
128 volume,
129 volume_uom,
130 place_of_origin,
131 best_by_date,
132 LENGTH,
133 length_uom,
134 recycled_content,
135 thickness,
136 thickness_uom,
137 width,
138 width_uom,
139 curl_wrinkle_fold,
140 c_attribute1,
141 c_attribute2,
142 c_attribute3,
143 c_attribute4,
144 c_attribute5,
145 c_attribute6,
146 c_attribute7,
147 c_attribute8,
148 c_attribute9,
149 c_attribute10,
150 c_attribute11,
151 c_attribute12,
152 c_attribute13,
153 c_attribute14,
154 c_attribute15,
155 c_attribute16,
156 c_attribute17,
157 c_attribute18,
158 c_attribute19,
159 c_attribute20,
160 d_attribute1,
161 d_attribute2,
162 d_attribute3,
163 d_attribute4,
164 d_attribute5,
165 d_attribute6,
166 d_attribute7,
167 d_attribute8,
168 d_attribute9,
169 d_attribute10,
170 n_attribute1,
171 n_attribute2,
172 n_attribute3,
173 n_attribute4,
174 n_attribute5,
175 n_attribute6,
176 n_attribute7,
177 n_attribute8,
178 n_attribute10,
179 supplier_lot_number,
180 n_attribute9,
181 territory_code,
182 vendor_name,
183 description,
184 attribute_category,
185 attribute1,
186 attribute2,
187 attribute3,
188 attribute4,
189 attribute5,
190 attribute6,
191 attribute7,
192 attribute8,
193 attribute9,
194 attribute10,
195 attribute11,
196 attribute12,
197 attribute13,
198 attribute14,
199 attribute15
200 INTO
201 l_in_lot_rec.vendor_id ,
202 l_in_lot_rec.grade_code,
203 l_in_lot_rec.origination_date,
204 l_in_lot_rec.date_code,
205 l_in_lot_rec.status_id,
206 l_in_lot_rec.change_date,
207 l_in_lot_rec.age,
208 l_in_lot_rec.retest_date,
209 l_in_lot_rec.maturity_date,
210 l_in_lot_rec.lot_attribute_category,
211 l_in_lot_rec.item_size,
212 l_in_lot_rec.color,
213 l_in_lot_rec.volume,
214 l_in_lot_rec.volume_uom,
215 l_in_lot_rec.place_of_origin,
216 l_in_lot_rec.best_by_date,
217 l_in_lot_rec.LENGTH,
218 l_in_lot_rec.length_uom,
219 l_in_lot_rec.recycled_content,
220 l_in_lot_rec.thickness,
221 l_in_lot_rec.thickness_uom,
222 l_in_lot_rec.width,
223 l_in_lot_rec.width_uom,
224 l_in_lot_rec.curl_wrinkle_fold,
225 l_in_lot_rec.c_attribute1,
226 l_in_lot_rec.c_attribute2,
227 l_in_lot_rec.c_attribute3,
228 l_in_lot_rec.c_attribute4,
229 l_in_lot_rec.c_attribute5,
230 l_in_lot_rec.c_attribute6,
231 l_in_lot_rec.c_attribute7,
232 l_in_lot_rec.c_attribute8,
233 l_in_lot_rec.c_attribute9,
234 l_in_lot_rec.c_attribute10,
235 l_in_lot_rec.c_attribute11,
236 l_in_lot_rec.c_attribute12,
237 l_in_lot_rec.c_attribute13,
238 l_in_lot_rec.c_attribute14,
239 l_in_lot_rec.c_attribute15,
240 l_in_lot_rec.c_attribute16,
241 l_in_lot_rec.c_attribute17,
242 l_in_lot_rec.c_attribute18,
243 l_in_lot_rec.c_attribute19,
244 l_in_lot_rec.c_attribute20,
245 l_in_lot_rec.d_attribute1,
246 l_in_lot_rec.d_attribute2,
247 l_in_lot_rec.d_attribute3,
248 l_in_lot_rec.d_attribute4,
249 l_in_lot_rec.d_attribute5,
250 l_in_lot_rec.d_attribute6,
251 l_in_lot_rec.d_attribute7,
252 l_in_lot_rec.d_attribute8,
253 l_in_lot_rec.d_attribute9,
254 l_in_lot_rec.d_attribute10,
255 l_in_lot_rec.n_attribute1,
256 l_in_lot_rec.n_attribute2,
257 l_in_lot_rec.n_attribute3,
258 l_in_lot_rec.n_attribute4,
259 l_in_lot_rec.n_attribute5,
260 l_in_lot_rec.n_attribute6,
261 l_in_lot_rec.n_attribute7,
262 l_in_lot_rec.n_attribute8,
263 l_in_lot_rec.n_attribute10,
264 l_in_lot_rec.supplier_lot_number,
265 l_in_lot_rec.n_attribute9,
266 l_in_lot_rec.territory_code,
267 l_in_lot_rec.vendor_name,
268 l_in_lot_rec.description,
269 l_in_lot_rec.attribute_category,
270 l_in_lot_rec.attribute1,
271 l_in_lot_rec.attribute2,
272 l_in_lot_rec.attribute3,
273 l_in_lot_rec.attribute4,
274 l_in_lot_rec.attribute5,
275 l_in_lot_rec.attribute6,
276 l_in_lot_rec.attribute7,
277 l_in_lot_rec.attribute8,
278 l_in_lot_rec.attribute9,
279 l_in_lot_rec.attribute10,
280 l_in_lot_rec.attribute11,
281 l_in_lot_rec.attribute12,
282 l_in_lot_rec.attribute13,
283 l_in_lot_rec.attribute14,
284 l_in_lot_rec.attribute15
285 FROM mtl_lot_numbers
286 WHERE organization_id = p_organization_id
287 AND inventory_item_id = p_reference_inventory_item_id
288 AND lot_number = p_reference_lot_number;
289 ELSE /* (l_user_context is not null and
290 (l_mapping_context is not null and l_user_context<> l_mapping_context)) */
291 SELECT vendor_id ,
292 grade_code,
293 origination_date,
294 date_code,
295 status_id,
296 change_date,
297 age,
298 retest_date,
299 maturity_date,
300 supplier_lot_number,
301 territory_code,
302 vendor_name,
303 description,
304 attribute_category,
305 attribute1,
306 attribute2,
307 attribute3,
308 attribute4,
309 attribute5,
310 attribute6,
311 attribute7,
312 attribute8,
313 attribute9,
314 attribute10,
315 attribute11,
316 attribute12,
317 attribute13,
318 attribute14,
319 attribute15
320 INTO
321 l_in_lot_rec.vendor_id ,
322 l_in_lot_rec.grade_code,
323 l_in_lot_rec.origination_date,
324 l_in_lot_rec.date_code,
325 l_in_lot_rec.status_id,
326 l_in_lot_rec.change_date,
327 l_in_lot_rec.age,
328 l_in_lot_rec.retest_date,
329 l_in_lot_rec.maturity_date,
330 l_in_lot_rec.supplier_lot_number,
331 l_in_lot_rec.territory_code,
332 l_in_lot_rec.vendor_name,
333 l_in_lot_rec.description,
334 l_in_lot_rec.attribute_category,
335 l_in_lot_rec.attribute1,
336 l_in_lot_rec.attribute2,
337 l_in_lot_rec.attribute3,
338 l_in_lot_rec.attribute4,
339 l_in_lot_rec.attribute5,
340 l_in_lot_rec.attribute6,
341 l_in_lot_rec.attribute7,
342 l_in_lot_rec.attribute8,
343 l_in_lot_rec.attribute9,
344 l_in_lot_rec.attribute10,
345 l_in_lot_rec.attribute11,
346 l_in_lot_rec.attribute12,
347 l_in_lot_rec.attribute13,
348 l_in_lot_rec.attribute14,
349 l_in_lot_rec.attribute15
350 FROM mtl_lot_numbers
351 WHERE organization_id = p_organization_id
352 AND inventory_item_id = p_reference_inventory_item_id
353 AND lot_number = p_reference_lot_number;
354
355 -- default the context to the defined context mapping
356 l_in_lot_rec.lot_attribute_category := l_mapping_context;
357
358 END IF;
359 EXCEPTION
360 WHEN NO_DATA_FOUND THEN
361 mydebug('the reference lot ' || p_reference_lot_number ||'do not exist');
362 END;
363 else
364 /** Populate Lot Attribute Category info. **/
365 l_in_lot_rec.lot_attribute_category := l_mapping_context;
366 end if;
367
368 /* Calling the overloaded procedure */
369 inv_lot_api_pub.Create_Inv_lot(
370 x_return_status => l_return_status
371 , x_msg_count => l_msg_count
372 , x_msg_data => l_msg_data
373 , x_row_id => l_row_id
374 , x_lot_rec => x_lot_rec
375 , p_lot_rec => l_in_lot_rec
376 , p_source => p_source
377 , p_api_version => l_api_version
378 , p_init_msg_list => l_init_msg_list
379 , p_commit => l_commit
380 , p_validation_level => l_validation_level
381 , p_origin_txn_id => l_origin_txn_id
382 );
383
384 IF g_debug = 1 THEN
385 mydebug('Program Create_Inv_lot return ' || l_return_status);
386 END IF;
387 IF l_return_status = fnd_api.g_ret_sts_error THEN
388 IF g_debug = 1 THEN
389 mydebug('Program Create_Inv_lot has failed with a user defined exception');
390 END IF;
391 RAISE g_exc_error;
392 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
393 IF g_debug = 1 THEN
394 mydebug('Program Create_Inv_lot has failed with a Unexpected exception');
395 END IF;
396 FND_MESSAGE.SET_NAME('INV','INV_PROGRAM_ERROR');
397 FND_MESSAGE.SET_TOKEN('PROG_NAME','Create_Inv_lot');
398 fnd_msg_pub.ADD;
399 RAISE g_exc_unexpected_error;
400 END IF;
401
402 mydebug('End of the program create_inv_lot. Program has completed successfully ');
403
404
405 EXCEPTION
406 WHEN NO_DATA_FOUND THEN
407 x_return_status := fnd_api.g_ret_sts_error;
408 ROLLBACK TO inv_new_lot;
409 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
410 if( x_msg_count > 1 ) then
411 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE);
412 end if;
413 mydebug('In No data found ' || SQLERRM);
414 WHEN g_exc_error THEN
415 x_return_status := fnd_api.g_ret_sts_error;
416 ROLLBACK TO inv_new_lot;
417 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
418 if( x_msg_count > 1 ) then
419 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE);
420 end if;
421 mydebug('In g_exc_error ' || SQLERRM);
422 WHEN g_exc_unexpected_error THEN
423 x_return_status := fnd_api.g_ret_sts_unexp_error;
424 ROLLBACK TO inv_new_lot;
425 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
426 if( x_msg_count > 1 ) then
427 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE);
428 end if;
429 mydebug('In g_exc_unexpected_error ' || SQLERRM);
430 WHEN OTHERS THEN
431 x_return_status := fnd_api.g_ret_sts_unexp_error;
432 ROLLBACK TO inv_new_lot;
433 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
434 if( x_msg_count > 1 ) then
435 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE);
436 end if;
437 mydebug('In others ' || SQLERRM);
438
439 END preprocess_lot;
440
441 procedure delete_lot(x_return_status out nocopy VARCHAR2
442 , x_msg_count out nocopy NUMBER
443 , x_msg_data out nocopy VARCHAR2
444 , p_inventory_item_id NUMBER
445 , p_organization_id NUMBER
446 , p_lot_number VARCHAR2
447 )
448 IS
449 BEGIN
450
451 delete from mtl_lot_numbers
452 where organization_id = p_organization_id
453 and inventory_item_id = p_inventory_item_id
454 and lot_number = p_lot_number;
455 EXCEPTION
456 WHEN OTHERS THEN
457 x_return_status := fnd_api.g_ret_sts_unexp_error;
458 mydebug('In g_exc_unexpected_error ' || SQLERRM);
459 END delete_lot;
460
461
462
463 procedure rosetta_table_copy_in_p0(t out nocopy inv_lot_api_pub.char_tbl, a0 JTF_VARCHAR2_TABLE_1000) as
464 ddindx binary_integer; indx binary_integer;
465 begin
466 if a0 is not null and a0.count > 0 then
467 if a0.count > 0 then
468 indx := a0.first;
469 ddindx := 1;
470 while true loop
471 t(ddindx) := a0(indx);
472 ddindx := ddindx+1;
473 if a0.last =indx
474 then exit;
475 end if;
476 indx := a0.next(indx);
477 end loop;
478 end if;
479 end if;
480 end rosetta_table_copy_in_p0;
481 procedure rosetta_table_copy_out_p0(t inv_lot_api_pub.char_tbl, a0 out nocopy JTF_VARCHAR2_TABLE_1000) as
482 ddindx binary_integer; indx binary_integer;
483 begin
484 if t is null or t.count = 0 then
485 a0 := JTF_VARCHAR2_TABLE_1000();
486 else
487 a0 := JTF_VARCHAR2_TABLE_1000();
488 if t.count > 0 then
489 a0.extend(t.count);
490 ddindx := t.first;
491 indx := 1;
492 while true loop
493 a0(indx) := t(ddindx);
494 indx := indx+1;
495 if t.last =ddindx
496 then exit;
497 end if;
498 ddindx := t.next(ddindx);
499 end loop;
500 end if;
501 end if;
502 end rosetta_table_copy_out_p0;
503
504 procedure rosetta_table_copy_in_p1(t out nocopy inv_lot_api_pub.number_tbl, a0 JTF_NUMBER_TABLE) as
505 ddindx binary_integer; indx binary_integer;
506 begin
507 if a0 is not null and a0.count > 0 then
508 if a0.count > 0 then
509 indx := a0.first;
510 ddindx := 1;
511 while true loop
512 t(ddindx) := a0(indx);
513 ddindx := ddindx+1;
514 if a0.last =indx
515 then exit;
516 end if;
517 indx := a0.next(indx);
518 end loop;
519 end if;
520 end if;
521 end rosetta_table_copy_in_p1;
522 procedure rosetta_table_copy_out_p1(t inv_lot_api_pub.number_tbl, a0 out nocopy JTF_NUMBER_TABLE) as
523 ddindx binary_integer; indx binary_integer;
524 begin
525 if t is null or t.count = 0 then
526 a0 := JTF_NUMBER_TABLE();
527 else
528 a0 := JTF_NUMBER_TABLE();
529 if t.count > 0 then
530 a0.extend(t.count);
531 ddindx := t.first;
532 indx := 1;
533 while true loop
534 a0(indx) := t(ddindx);
535 indx := indx+1;
536 if t.last =ddindx
537 then exit;
538 end if;
539 ddindx := t.next(ddindx);
540 end loop;
541 end if;
542 end if;
543 end rosetta_table_copy_out_p1;
544
545 procedure rosetta_table_copy_in_p2(t out nocopy inv_lot_api_pub.date_tbl, a0 JTF_DATE_TABLE) as
546 ddindx binary_integer; indx binary_integer;
547 begin
548 if a0 is not null and a0.count > 0 then
549 if a0.count > 0 then
550 indx := a0.first;
551 ddindx := 1;
552 while true loop
553 t(ddindx) := a0(indx);
554 ddindx := ddindx+1;
555 if a0.last =indx
556 then exit;
557 end if;
558 indx := a0.next(indx);
559 end loop;
560 end if;
561 end if;
562 end rosetta_table_copy_in_p2;
563 procedure rosetta_table_copy_out_p2(t inv_lot_api_pub.date_tbl, a0 out nocopy JTF_DATE_TABLE) as
564 ddindx binary_integer; indx binary_integer;
565 begin
566 if t is null or t.count = 0 then
567 a0 := JTF_DATE_TABLE();
568 else
569 a0 := JTF_DATE_TABLE();
570 if t.count > 0 then
571 a0.extend(t.count);
572 ddindx := t.first;
573 indx := 1;
574 while true loop
575 a0(indx) := t(ddindx);
576 indx := indx+1;
577 if t.last =ddindx
578 then exit;
579 end if;
580 ddindx := t.next(ddindx);
581 end loop;
582 end if;
583 end if;
584 end rosetta_table_copy_out_p2;
585
586 procedure create_inv_lot(x_return_status out nocopy VARCHAR2
587 , x_msg_count out nocopy NUMBER
588 , x_msg_data out nocopy VARCHAR2
589 , p_inventory_item_id NUMBER
590 , p_organization_id NUMBER
591 , p_lot_number VARCHAR2
592 , p_expiration_date DATE
593 , p_disable_flag NUMBER
594 , p_attribute_category VARCHAR2
595 , p_lot_attribute_category VARCHAR2
596 , p_attributes_tbl JTF_VARCHAR2_TABLE_1000
597 , p_c_attributes_tbl JTF_VARCHAR2_TABLE_1000
598 , p_n_attributes_tbl JTF_NUMBER_TABLE
599 , p_d_attributes_tbl JTF_DATE_TABLE
600 , p_grade_code VARCHAR2
601 , p_origination_date DATE
602 , p_date_code VARCHAR2
603 , p_status_id NUMBER
604 , p_change_date DATE
605 , p_age NUMBER
606 , p_retest_date DATE
607 , p_maturity_date DATE
608 , p_item_size NUMBER
609 , p_color VARCHAR2
610 , p_volume NUMBER
611 , p_volume_uom VARCHAR2
612 , p_place_of_origin VARCHAR2
613 , p_best_by_date DATE
614 , p_length NUMBER
615 , p_length_uom VARCHAR2
616 , p_recycled_content NUMBER
617 , p_thickness NUMBER
618 , p_thickness_uom VARCHAR2
619 , p_width NUMBER
620 , p_width_uom VARCHAR2
621 , p_territory_code VARCHAR2
622 , p_supplier_lot_number VARCHAR2
623 , p_vendor_name VARCHAR2
624 , p_source NUMBER
625 )
626
627 as
628 ddp_attributes_tbl inv_lot_api_pub.char_tbl;
629 ddp_c_attributes_tbl inv_lot_api_pub.char_tbl;
630 ddp_n_attributes_tbl inv_lot_api_pub.number_tbl;
631 ddp_d_attributes_tbl inv_lot_api_pub.date_tbl;
632 ddindx binary_integer; indx binary_integer;
633 begin
634
635 -- copy data to the local IN or IN-OUT args, if any
636
637
638
639
640
641
642
643
644
645
646 rosetta_table_copy_in_p0(ddp_attributes_tbl, p_attributes_tbl);
647
648 rosetta_table_copy_in_p0(ddp_c_attributes_tbl, p_c_attributes_tbl);
649
650 rosetta_table_copy_in_p1(ddp_n_attributes_tbl, p_n_attributes_tbl);
651
652 rosetta_table_copy_in_p2(ddp_d_attributes_tbl, p_d_attributes_tbl);
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679 -- here's the delegated call to the old PL/SQL routine
680 inv_lot_api_pub.create_inv_lot(x_return_status,
681 x_msg_count,
682 x_msg_data,
683 p_inventory_item_id,
684 p_organization_id,
685 p_lot_number,
686 p_expiration_date,
687 p_disable_flag,
688 p_attribute_category,
689 p_lot_attribute_category,
690 ddp_attributes_tbl,
691 ddp_c_attributes_tbl,
692 ddp_n_attributes_tbl,
693 ddp_d_attributes_tbl,
694 p_grade_code,
695 p_origination_date,
696 p_date_code,
697 p_status_id,
698 p_change_date,
699 p_age,
700 p_retest_date,
701 p_maturity_date,
702 p_item_size,
703 p_color,
704 p_volume,
705 p_volume_uom,
706 p_place_of_origin,
707 p_best_by_date,
708 p_length,
709 p_length_uom,
710 p_recycled_content,
711 p_thickness,
712 p_thickness_uom,
713 p_width,
714 p_width_uom,
715 p_territory_code,
716 p_supplier_lot_number,
717 p_vendor_name,
718 p_source);
719
720 -- copy data back from the local variables to OUT or IN-OUT args, if any
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759 end;
760
761 procedure update_inv_lot(x_return_status out nocopy VARCHAR2
762 , x_msg_count out nocopy NUMBER
763 , x_msg_data out nocopy VARCHAR2
764 , p_inventory_item_id NUMBER
765 , p_organization_id NUMBER
766 , p_lot_number VARCHAR2
767 , p_expiration_date DATE
768 , p_disable_flag NUMBER
769 , p_attribute_category VARCHAR2
770 , p_lot_attribute_category VARCHAR2
771 , p_attributes_tbl JTF_VARCHAR2_TABLE_1000
772 , p_c_attributes_tbl JTF_VARCHAR2_TABLE_1000
773 , p_n_attributes_tbl JTF_NUMBER_TABLE
774 , p_d_attributes_tbl JTF_DATE_TABLE
775 , p_grade_code VARCHAR2
776 , p_origination_date DATE
777 , p_date_code VARCHAR2
778 , p_status_id NUMBER
779 , p_change_date DATE
780 , p_age NUMBER
781 , p_retest_date DATE
782 , p_maturity_date DATE
783 , p_item_size NUMBER
784 , p_color VARCHAR2
785 , p_volume NUMBER
786 , p_volume_uom VARCHAR2
787 , p_place_of_origin VARCHAR2
788 , p_best_by_date DATE
789 , p_length NUMBER
790 , p_length_uom VARCHAR2
791 , p_recycled_content NUMBER
792 , p_thickness NUMBER
793 , p_thickness_uom VARCHAR2
794 , p_width NUMBER
795 , p_width_uom VARCHAR2
796 , p_territory_code VARCHAR2
797 , p_supplier_lot_number VARCHAR2
798 , p_vendor_name VARCHAR2
799 , p_source NUMBER
800 )
801
802 as
803 ddp_attributes_tbl inv_lot_api_pub.char_tbl;
804 ddp_c_attributes_tbl inv_lot_api_pub.char_tbl;
805 ddp_n_attributes_tbl inv_lot_api_pub.number_tbl;
806 ddp_d_attributes_tbl inv_lot_api_pub.date_tbl;
807 ddindx binary_integer; indx binary_integer;
808 begin
809
810 -- copy data to the local IN or IN-OUT args, if any
811
812
813
814
815
816
817
818
819
820
821 rosetta_table_copy_in_p0(ddp_attributes_tbl, p_attributes_tbl);
822
823 rosetta_table_copy_in_p0(ddp_c_attributes_tbl, p_c_attributes_tbl);
824
825 rosetta_table_copy_in_p1(ddp_n_attributes_tbl, p_n_attributes_tbl);
826
827 rosetta_table_copy_in_p2(ddp_d_attributes_tbl, p_d_attributes_tbl);
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854 -- here's the delegated call to the old PL/SQL routine
855 mydebug('calling inv_lot_api_pub.update_inv_lot with:');
856 mydebug('p_expiration_date '||p_expiration_date);
857 mydebug('p_source '|| p_source);
858 inv_lot_api_pub.update_inv_lot(x_return_status,
859 x_msg_count,
860 x_msg_data,
861 p_inventory_item_id,
862 p_organization_id,
863 p_lot_number,
864 p_expiration_date,
865 p_disable_flag,
866 p_attribute_category,
867 p_lot_attribute_category,
868 ddp_attributes_tbl,
869 ddp_c_attributes_tbl,
870 ddp_n_attributes_tbl,
871 ddp_d_attributes_tbl,
872 p_grade_code,
873 p_origination_date,
874 p_date_code,
875 p_status_id,
876 p_change_date,
877 p_age,
878 p_retest_date,
879 p_maturity_date,
880 p_item_size,
881 p_color,
882 p_volume,
883 p_volume_uom,
884 p_place_of_origin,
885 p_best_by_date,
886 p_length,
887 p_length_uom,
888 p_recycled_content,
889 p_thickness,
890 p_thickness_uom,
891 p_width,
892 p_width_uom,
893 p_territory_code,
894 p_supplier_lot_number,
895 p_vendor_name,
896 p_source);
897
898 -- copy data back from the local variables to OUT or IN-OUT args, if any
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937 end;
938
939 end INV_LOT_EO_PVT;