[Home] [Help]
PACKAGE BODY: APPS.WSM_LOTATTR_PVT
Source
1 PACKAGE body WSM_LotAttr_PVT as
2 /* $Header: WSMVATRB.pls 120.7.12010000.2 2008/08/06 07:46:26 amayadav ship $ */
3
4 procedure lot_exists(x_err_code OUT NOCOPY NUMBER,
5 x_err_msg OUT NOCOPY VARCHAR2,
6 p_org_id IN NUMBER,
7 p_lot_number IN VARCHAR2,
8 p_inventory_item_id IN NUMBER,
9 x_lot_exists OUT NOCOPY VARCHAR2) is
10
11 -- To check if lot already exists
12 BEGIN
13 x_err_code:=0;
14 SELECT 'Y'
15 INTO x_lot_exists
16 FROM mtl_lot_numbers
17 WHERE organization_id = p_org_id
18 AND inventory_item_id = p_inventory_item_id
19 AND lot_number = p_lot_number;
20
21 EXCEPTION
22 WHEN no_data_found THEN
23 x_lot_exists:='N';
24 WHEN OTHERS THEN
25 x_err_code := SQLCODE;
26 x_err_msg := 'WSMVATRB.lot_exists' || SUBSTR(SQLERRM,1,60);
27 END;
28
29
30 /*
31 PROCEDURE create_update_lotattr(x_err_code OUT NOCOPY NUMBER,
32 x_err_msg OUT NOCOPY varchar2,
33 p_lot_number VARCHAR2,
34 p_inv_item_id NUMBER,
35 p_org_id NUMBER) IS
36 BEGIN
37 create_update_lotattr( x_err_code => x_err_code,
38 x_err_msg => x_err_msg,
39 p_lot_number => p_lot_number,
40 p_inv_item_id => p_inv_item_id,
41 p_org_id => p_org_id,
42 p_src_lot_number => NULL,
43 p_src_inv_item_id => NULL);
44
45 END create_update_lotattr; */
46
47 procedure pdebug(x_mesg varchar2) is
48 begin
49 --dbms_output.put_line(x_mesg);
50 fnd_file.put_line(fnd_file.log,x_mesg);
51 end;
52
53 Procedure create_update_lotattr(x_err_code OUT NOCOPY VARCHAR2,
54 x_err_msg OUT NOCOPY VARCHAR2,
55 p_lot_number IN VARCHAR2,
56 p_inv_item_id IN NUMBER,
57 p_org_id IN NUMBER,
58 p_intf_txn_id IN NUMBER,
59 p_intf_src_code IN VARCHAR2,
60 p_src_lot_number IN VARCHAR2 DEFAULT NULL,
61 p_src_inv_item_id IN NUMBER DEFAULT NULL) is
62
63 l_wms_installed BOOLEAN:=FALSE;
64 x_context_code MTL_LOT_NUMBERS.LOT_ATTRIBUTE_CATEGORY%TYPE:=NULL;
65 x_src_context_code MTL_LOT_NUMBERS.LOT_ATTRIBUTE_CATEGORY%TYPE:=NULL;
66 l_copy_from_src BOOLEAN:=FALSE;
67 l_intf_rec_found BOOLEAN:=FALSE;
68 l_inv_attr_required BOOLEAN:=FALSE;
69 l_wms_attr_required BOOLEAN:=FALSE;
70 l_call_inv_lotapi BOOLEAN:=FALSE;
71
72 l_lot_attribute_category varchar2(30):=NULL;
73 l_attribute_category varchar2(30):=NULL;
74 l_description mtl_lot_numbers.description%TYPE:=NULL;
75 l_invattr_tbl inv_lot_api_pub.char_tbl;
76 l_Cattr_tbl inv_lot_api_pub.char_tbl;
77 l_Nattr_tbl inv_lot_api_pub.number_tbl;
78 l_Dattr_tbl inv_lot_api_pub.date_tbl;
79
80 l_grade_code mtl_lot_numbers.grade_code%TYPE:=NULL;
81 l_origination_date DATE:=NULL;
82 l_date_code mtl_lot_numbers.date_code%TYPE:=NULL;
83 l_change_date DATE:=NULL;
84 l_age NUMBER:=NULL;
85 l_retest_date DATE:=NULL;
86 l_maturity_date DATE:=NULL;
87 l_item_size NUMBER:=NULL;
88 l_color mtl_lot_numbers.color%TYPE:=NULL;
89 l_volume NUMBER:=NULL;
90 l_volume_uom mtl_lot_numbers.volume_uom%TYPE:=NULL;
91 l_place_of_origin mtl_lot_numbers.place_of_origin%TYPE:=NULL;
92 l_best_by_date DATE:=NULL;
93 l_length NUMBER:=NULL;
94 l_length_uom mtl_lot_numbers.volume_uom%TYPE:=NULL;
95 l_recycled_content NUMBER:=NULL;
96 l_thickness NUMBER:=NULL;
97 l_thickness_uom mtl_lot_numbers.volume_uom%TYPE:=NULL;
98 l_width NUMBER:=NULL;
99 l_width_uom mtl_lot_numbers.volume_uom%TYPE:=NULL;
100 l_vendor_id NUMBER:=NULL;
101 l_vendor_name mtl_lot_numbers.vendor_name%TYPE:=NULL;
102 l_territory_code mtl_lot_numbers.territory_code%TYPE:=NULL;
103 l_supplier_lot_number mtl_lot_numbers.supplier_lot_number%TYPE:=NULL;
104 l_curl_wrinkle_fold mtl_lot_numbers.supplier_lot_number%TYPE:=NULL;
105 l_status_id number;
106
107 -- Source lot variables
108 l_Sinvattr_tbl inv_lot_api_pub.char_tbl;
109 l_SCattr_tbl inv_lot_api_pub.char_tbl;
110 l_SNattr_tbl inv_lot_api_pub.number_tbl;
111 l_SDattr_tbl inv_lot_api_pub.date_tbl;
112
113 l_Sgrade_code mtl_lot_numbers.grade_code%TYPE:=NULL;
114 l_Sorigination_date DATE:=NULL;
115 l_Sdate_code mtl_lot_numbers.date_code%TYPE:=NULL;
116 l_Schange_date DATE:=NULL;
117 l_Sage NUMBER:=NULL;
118 l_Sretest_date DATE:=NULL;
119 l_Smaturity_date DATE:=NULL;
120 l_Sitem_size NUMBER:=NULL;
121 l_Scolor mtl_lot_numbers.color%TYPE:=NULL;
122 l_Svolume NUMBER:=NULL;
123 l_Svolume_uom mtl_lot_numbers.volume_uom%TYPE:=NULL;
124 l_Splace_of_origin mtl_lot_numbers.place_of_origin%TYPE:=NULL;
125 l_Sbest_by_date DATE:=NULL;
126 l_Slength NUMBER:=NULL;
127 l_Slength_uom mtl_lot_numbers.volume_uom%TYPE:=NULL;
128 l_Srecycled_content NUMBER:=NULL;
129 l_Sthickness NUMBER:=NULL;
130 l_Sthickness_uom mtl_lot_numbers.volume_uom%TYPE:=NULL;
131 l_Swidth NUMBER:=NULL;
132 l_Swidth_uom mtl_lot_numbers.volume_uom%TYPE:=NULL;
133 l_Svendor_id NUMBER:=NULL;
134 l_Svendor_name mtl_lot_numbers.vendor_name%TYPE:=NULL;
135 l_Sterritory_code mtl_lot_numbers.territory_code%TYPE:=NULL;
136 l_Ssupplier_lot_number mtl_lot_numbers.supplier_lot_number%TYPE:=NULL;
137 l_Scurl_wrinkle_fold mtl_lot_numbers.supplier_lot_number%TYPE:=NULL;
138
139 x_lot_exists VARCHAR2(1):='N';
140 x_src_lot_exists VARCHAR2(1):='N';
141 l_mtli_txn_id NUMBER;
142 x_return_status VARCHAR2(1);
143 x_msg_count NUMBER;
144
145 l_intf_temp NUMBER; -- added for bug 5126021.
146
147
148 -- Bug 5367283 Added the following variables
149
150 l_context_r fnd_dflex.context_r;
151 l_contexts_dr fnd_dflex.contexts_dr;
152 l_dflex_r fnd_dflex.dflex_r;
153 l_segments_dr fnd_dflex.segments_dr;
154 l_context VARCHAR2(1000);
155 l_global_context BINARY_INTEGER;
156 wms_context_index NUMBER;
157
158 l_temp_lot_attribute_category varchar2(30):=NULL;
159
160 l_temp_Cattr_tbl inv_lot_api_pub.char_tbl;
161 l_temp_Nattr_tbl inv_lot_api_pub.number_tbl;
162 l_temp_Dattr_tbl inv_lot_api_pub.date_tbl;
163
164 l_temp_description mtl_lot_numbers.description%type:=null;
165 l_temp_grade_code mtl_lot_numbers.grade_code%type:=null;
166 l_temp_origination_date date:=null;
167 l_temp_date_code mtl_lot_numbers.date_code%type:=null;
168 l_temp_change_date date:=null;
169 l_temp_age number:=null;
170 l_temp_retest_date date:=null;
171 l_temp_maturity_date date:=null;
172 l_temp_item_size number:=null;
173 l_temp_color mtl_lot_numbers.color%type:=null;
174 l_temp_volume number:=null;
175 l_temp_volume_uom mtl_lot_numbers.volume_uom%type:=null;
176 l_temp_place_of_origin mtl_lot_numbers.place_of_origin%type:=null;
177 l_temp_best_by_date date:=null;
178 l_temp_length number:=null;
179 l_temp_length_uom mtl_lot_numbers.volume_uom%type:=null;
180 l_temp_recycled_content number:=null;
181 l_temp_thickness number:=null;
182 l_temp_thickness_uom mtl_lot_numbers.volume_uom%type:=null;
183 l_temp_width number:=null;
184 l_temp_width_uom mtl_lot_numbers.volume_uom%type:=null;
185 l_temp_vendor_id number:=null;
186 l_temp_vendor_name mtl_lot_numbers.vendor_name%type:=null;
187 l_temp_territory_code mtl_lot_numbers.territory_code%type:=null;
188 l_temp_supplier_lot_number mtl_lot_numbers.supplier_lot_number%type:=null;
189 l_temp_curl_wrinkle_fold mtl_lot_numbers.supplier_lot_number%type:=null;
190
191 -- Bug 5367283 the above variables are added
192
193 BEGIN
194 fnd_msg_pub.initialize;
195 if g_debug then
196 pdebug('Entering wsmvatrb.create_update_lotattr lot#= ' || p_lot_number);
197 pdebug('inv itemid= ' || p_inv_item_id || ' org_id= ' || p_org_id);
198 end if;
199 IF (p_lot_number IS NULL OR
200 p_inv_item_id IS NULL OR
201 p_org_id IS NULL) THEN
202 fnd_message.set_name('WSM','WSM_INVALID_FIELD');
203 fnd_message.set_token('FLD_NAME', 'Lot Number');
204 x_err_msg := fnd_message.get;
205 x_err_code:= -1;
206 return;
207 END IF;
208
209 --IF (p_lot_number IS NULL OR
210 x_err_code:=0;
211 lot_exists(x_err_code, x_err_msg,p_org_id,p_lot_number,
212 p_inv_item_id,x_lot_exists);
213 if g_debug then
214 pdebug('x_lot_exists=' || x_lot_exists);
215 end if;
216 if (x_err_code <> 0) THEN
217 return;
218 end if;
219 IF (p_src_lot_number IS NOT NULL AND
220 p_src_inv_item_id IS NOT NULL) THEN
221 lot_exists(x_err_code, x_err_msg,p_org_id,p_src_lot_number,
222 p_src_inv_item_id,x_src_lot_exists);
223 if (x_err_code <> 0) THEN
224 return;
225 end if;
226 if g_debug then
227 pdebug('x_src_lot_exists=' || x_src_lot_exists);
228 pdebug('p_src_inv_item_id=' ||p_src_inv_item_id);
229 pdebug('p_src_lot_number=' ||p_src_lot_number);
230 end if;
231 ELSE
232 x_src_lot_exists:='N';
233 if g_debug then -- added for bug 5126021.
234 pdebug('x_src_lot_exists=' || x_src_lot_exists);
235 pdebug('x_src_lot_number=' || p_src_lot_number);
236 end if;
237 END IF;
238
239 FOR cntr in 1..10 LOOP -- added for bug 5126021:Start .
240 l_invattr_tbl(cntr):=null;
241 l_Cattr_tbl(cntr):=null;
242 l_Nattr_tbl(cntr):=null;
243 l_Dattr_tbl(cntr):=null;
244 END LOOP;
245
246 FOR cntr in 11..15 LOOP
247 l_invattr_tbl(cntr):=null;
248 l_Cattr_tbl(cntr):=null;
249 END LOOP;
250
251 FOR cntr in 16..20 LOOP
252 l_Cattr_tbl(cntr):=null;
253 END LOOP;
254
255 begin
256 select 1
257 into l_intf_temp
258 from mtl_transaction_lots_interface mtli
259 where mtli.product_transaction_id=p_intf_txn_id
260 and mtli.product_code=p_intf_src_code
261 and mtli.lot_number=p_lot_number;
262
263 l_intf_rec_found :=TRUE;
264
265 if g_debug then
266 pdebug('l_intf_rec_found= TRUE');
267 end if;
268
269 exception
270 when NO_DATA_FOUND THEN
271 l_intf_rec_found :=FALSE;
272 if g_debug then
273 pdebug('l_intf_rec_found= FALSE');
274 end if;
275 end; -- added for bug 5126021:End.
276
277 --IF (inv_install.adv_inv_installed(p_org_id) = TRUE) THEN
278 IF (inv_install.adv_inv_installed(NULL) = TRUE) THEN
279 l_wms_installed := TRUE;
280 if g_debug then
281 pdebug('l_wms_installed = TRUE');
282 end if;
283 IF x_src_lot_exists='Y' THEN
284 inv_lot_sel_attr.get_context_code(
285 x_context_code,
286 p_org_id,
287 p_inv_item_id,
288 'Lot Attributes');
289 -- Commented for bug 5463921
290 /* inv_lot_sel_attr.get_context_code(
291 x_src_context_code,
292 p_org_id,
293 p_src_inv_item_id,
294 'Lot Attributes'); */
295 -- Added for bug 5463921.
296 select lot_attribute_category
297 into x_src_context_code
298 from mtl_lot_numbers
299 where lot_number=p_src_lot_number
300 and inventory_item_id=p_src_inv_item_id
301 and organization_id= p_org_id;
302
303 -- IF (x_src_context_code=x_context_code AND
304 -- x_src_context_code IS NOT NULL AND
305 -- x_context_code IS NOT NULL ) THEN
306 -- ST Fix for Bug 4881542
307 --IF (nvl(x_src_context_code,'&&$$$') = nvl(x_context_code,'&&$$$')) THEN
308 -- l_copy_from_src:=TRUE;
309 --END IF; -- added for bug 5126021.
310 If ( x_src_context_code=x_context_code or
311 x_context_code is null) THEN
312 l_copy_from_src:=TRUE;
313 else
314 l_copy_from_src:=FALSE;
315 end if;
316 ELSE
317 l_copy_from_src:=TRUE;
318 END IF; -- x_src_lot_exists='Y'
319 END IF; -- inv_install.adv_inv_installed(NULL) = TRUE
320
321 /* A big section of commented out code was present here. It has been removed
322 for code readability and clarity. For some reason, if you would like to know
323 more about the commented out code, please refer to file version 120.4
324 */
325
326 if NOT (x_lot_exists='N' and x_src_lot_exists='N') THEN --Bug 5282172.
327 if l_copy_from_src then
328
329 if g_debug then
330 pdebug('l_copy_from_src= TRUE ');
331 end if;
332
333 SELECT
334 description -- This is Not a named attr, right?
335 ,grade_code
336 ,origination_date
337 ,date_code
338 ,change_date
339 ,age
340 ,retest_date
341 ,maturity_date
342 ,item_size
343 ,color
344 ,volume
345 ,volume_uom
346 ,place_of_origin
347 ,best_by_date
348 ,length
349 ,length_uom
350 ,recycled_content
351 ,thickness
352 ,thickness_uom
353 ,width
354 ,width_uom
355 ,vendor_id -- are vendor_id is missing in create_inv_lot
356 ,vendor_name
357 ,territory_code --MISSING in named record
358 ,supplier_lot_number --MISSING in named record
359 ,curl_wrinkle_fold --MISSING in named record
360 ,lot_attribute_category
361 ,c_attribute1
362 ,c_attribute2
363 ,c_attribute3
364 ,c_attribute4
365 ,c_attribute5
366 ,c_attribute6
367 ,c_attribute7
368 ,c_attribute8
369 ,c_attribute9
370 ,c_attribute10
371 ,c_attribute11
372 ,c_attribute12
373 ,c_attribute13
374 ,c_attribute14
375 ,c_attribute15
376 ,c_attribute16
377 ,c_attribute17
378 ,c_attribute18
379 ,c_attribute19
380 ,c_attribute20
381 ,d_attribute1
382 ,d_attribute2
383 ,d_attribute3
384 ,d_attribute4
385 ,d_attribute5
386 ,d_attribute6
387 ,d_attribute7
388 ,d_attribute8
389 ,d_attribute9
390 ,d_attribute10
391 ,n_attribute1
392 ,n_attribute2
393 ,n_attribute3
394 ,n_attribute4
395 ,n_attribute5
396 ,n_attribute6
397 ,n_attribute7
398 ,n_attribute8
399 ,n_attribute9
400 ,n_attribute10
401 ,attribute_category
402 ,attribute1
403 ,attribute2
404 ,attribute3
405 ,attribute4
406 ,attribute5
407 ,attribute6
408 ,attribute7
409 ,attribute8
410 ,attribute9
411 ,attribute10
412 ,attribute11
413 ,attribute12
414 ,attribute13
415 ,attribute14
416 ,attribute15
417 INTO
418 l_description
419 ,l_grade_code
420 ,l_origination_date
421 ,l_date_code
422 ,l_change_date
423 ,l_age
424 ,l_retest_date
425 ,l_maturity_date
426 ,l_item_size
427 ,l_color
428 ,l_volume
429 ,l_volume_uom
430 ,l_place_of_origin
431 ,l_best_by_date
432 ,l_length
433 ,l_length_uom
434 ,l_recycled_content
435 ,l_thickness
436 ,l_thickness_uom
437 ,l_width
438 ,l_width_uom
439 ,l_vendor_id
440 ,l_vendor_name
441 ,l_territory_code
442 ,l_supplier_lot_number
443 ,l_curl_wrinkle_fold
444 ,l_lot_attribute_category
445 ,l_Cattr_tbl(1)
446 ,l_Cattr_tbl(2)
447 ,l_Cattr_tbl(3)
448 ,l_Cattr_tbl(4)
449 ,l_Cattr_tbl(5)
450 ,l_Cattr_tbl(6)
451 ,l_Cattr_tbl(7)
452 ,l_Cattr_tbl(8)
453 ,l_Cattr_tbl(9)
454 ,l_Cattr_tbl(10)
455 ,l_Cattr_tbl(11)
456 ,l_Cattr_tbl(12)
457 ,l_Cattr_tbl(13)
458 ,l_Cattr_tbl(14)
459 ,l_Cattr_tbl(15)
460 ,l_Cattr_tbl(16)
461 ,l_Cattr_tbl(17)
462 ,l_Cattr_tbl(18)
463 ,l_Cattr_tbl(19)
464 ,l_Cattr_tbl(20)
465 ,l_Dattr_tbl(1)
466 ,l_Dattr_tbl(2)
467 ,l_Dattr_tbl(3)
468 ,l_Dattr_tbl(4)
469 ,l_Dattr_tbl(5)
470 ,l_Dattr_tbl(6)
471 ,l_Dattr_tbl(7)
472 ,l_Dattr_tbl(8)
473 ,l_Dattr_tbl(9)
474 ,l_Dattr_tbl(10)
475 ,l_Nattr_tbl(1)
476 ,l_Nattr_tbl(2)
477 ,l_Nattr_tbl(3)
478 ,l_Nattr_tbl(4)
479 ,l_Nattr_tbl(5)
480 ,l_Nattr_tbl(6)
481 ,l_Nattr_tbl(7)
482 ,l_Nattr_tbl(8)
483 ,l_Nattr_tbl(9)
484 ,l_Nattr_tbl(10)
485 ,l_attribute_category
486 ,l_invattr_tbl(1)
487 ,l_invattr_tbl(2)
488 ,l_invattr_tbl(3)
489 ,l_invattr_tbl(4)
490 ,l_invattr_tbl(5)
491 ,l_invattr_tbl(6)
492 ,l_invattr_tbl(7)
493 ,l_invattr_tbl(8)
494 ,l_invattr_tbl(9)
495 ,l_invattr_tbl(10)
496 ,l_invattr_tbl(11)
497 ,l_invattr_tbl(12)
498 ,l_invattr_tbl(13)
499 ,l_invattr_tbl(14)
500 ,l_invattr_tbl(15)
501 FROM mtl_lot_numbers
502 WHERE lot_number=nvl(p_src_lot_number,p_lot_number) /* modified for fixing bug 5126021 */
503 AND inventory_item_id=nvl(p_src_inv_item_id,p_inv_item_id) /* modified for fixing bug 5126021 */
504 AND organization_id=p_org_id;
505
506 l_call_inv_lotapi :=TRUE;
507
508 else
509
510 if g_debug then
511 pdebug('l_copy_from_src= FALSE ');
512 end if;
513
514
515
516 -- Bug 5367283 Begin adding code
517
518 if (l_wms_installed ) then
519
520 SELECT
521 description
522 ,grade_code
523 ,origination_date
524 ,date_code
525 ,change_date
526 ,age
527 ,retest_date
528 ,maturity_date
529 ,item_size
530 ,color
531 ,volume
532 ,volume_uom
533 ,place_of_origin
534 ,best_by_date
535 ,length
536 ,length_uom
537 ,recycled_content
538 ,thickness
539 ,thickness_uom
540 ,width
541 ,width_uom
542 ,vendor_id
543 ,vendor_name
544 ,territory_code
545 ,supplier_lot_number
546 ,curl_wrinkle_fold
547 ,lot_attribute_category
548 ,c_attribute1
549 ,c_attribute2
550 ,c_attribute3
551 ,c_attribute4
552 ,c_attribute5
553 ,c_attribute6
554 ,c_attribute7
555 ,c_attribute8
556 ,c_attribute9
557 ,c_attribute10
558 ,c_attribute11
559 ,c_attribute12
560 ,c_attribute13
561 ,c_attribute14
562 ,c_attribute15
563 ,c_attribute16
564 ,c_attribute17
565 ,c_attribute18
566 ,c_attribute19
567 ,c_attribute20
568 ,d_attribute1
569 ,d_attribute2
570 ,d_attribute3
571 ,d_attribute4
572 ,d_attribute5
573 ,d_attribute6
574 ,d_attribute7
575 ,d_attribute8
576 ,d_attribute9
577 ,d_attribute10
578 ,n_attribute1
579 ,n_attribute2
580 ,n_attribute3
581 ,n_attribute4
582 ,n_attribute5
583 ,n_attribute6
584 ,n_attribute7
585 ,n_attribute8
586 ,n_attribute9
587 ,n_attribute10
588 ,attribute_category
589 ,attribute1
590 ,attribute2
591 ,attribute3
592 ,attribute4
593 ,attribute5
594 ,attribute6
595 ,attribute7
596 ,attribute8
597 ,attribute9
598 ,attribute10
599 ,attribute11
600 ,attribute12
601 ,attribute13
602 ,attribute14
603 ,attribute15
604 INTO
605 l_temp_description
606 ,l_temp_grade_code
607 ,l_temp_origination_date
608 ,l_temp_date_code
609 ,l_temp_change_date
610 ,l_temp_age
611 ,l_temp_retest_date
612 ,l_temp_maturity_date
613 ,l_temp_item_size
614 ,l_temp_color
615 ,l_temp_volume
616 ,l_temp_volume_uom
617 ,l_temp_place_of_origin
618 ,l_temp_best_by_date
619 ,l_temp_length
620 ,l_temp_length_uom
621 ,l_temp_recycled_content
622 ,l_temp_thickness
623 ,l_temp_thickness_uom
624 ,l_temp_width
625 ,l_temp_width_uom
626 ,l_temp_vendor_id
627 ,l_temp_vendor_name
628 ,l_temp_territory_code
629 ,l_temp_supplier_lot_number
630 ,l_temp_curl_wrinkle_fold
631 ,l_temp_lot_attribute_category
632 ,l_temp_Cattr_tbl(1)
633 ,l_temp_Cattr_tbl(2)
634 ,l_temp_Cattr_tbl(3)
635 ,l_temp_Cattr_tbl(4)
636 ,l_temp_Cattr_tbl(5)
637 ,l_temp_Cattr_tbl(6)
638 ,l_temp_Cattr_tbl(7)
639 ,l_temp_Cattr_tbl(8)
640 ,l_temp_Cattr_tbl(9)
641 ,l_temp_Cattr_tbl(10)
642 ,l_temp_Cattr_tbl(11)
643 ,l_temp_Cattr_tbl(12)
644 ,l_temp_Cattr_tbl(13)
645 ,l_temp_Cattr_tbl(14)
646 ,l_temp_Cattr_tbl(15)
647 ,l_temp_Cattr_tbl(16)
648 ,l_temp_Cattr_tbl(17)
649 ,l_temp_Cattr_tbl(18)
650 ,l_temp_Cattr_tbl(19)
651 ,l_temp_Cattr_tbl(20)
652 ,l_temp_Dattr_tbl(1)
653 ,l_temp_Dattr_tbl(2)
654 ,l_temp_Dattr_tbl(3)
655 ,l_temp_Dattr_tbl(4)
656 ,l_temp_Dattr_tbl(5)
657 ,l_temp_Dattr_tbl(6)
658 ,l_temp_Dattr_tbl(7)
659 ,l_temp_Dattr_tbl(8)
660 ,l_temp_Dattr_tbl(9)
661 ,l_temp_Dattr_tbl(10)
662 ,l_temp_Nattr_tbl(1)
663 ,l_temp_Nattr_tbl(2)
664 ,l_temp_Nattr_tbl(3)
665 ,l_temp_Nattr_tbl(4)
666 ,l_temp_Nattr_tbl(5)
667 ,l_temp_Nattr_tbl(6)
668 ,l_temp_Nattr_tbl(7)
669 ,l_temp_Nattr_tbl(8)
670 ,l_temp_Nattr_tbl(9)
671 ,l_temp_Nattr_tbl(10)
672 ,l_attribute_category
673 ,l_invattr_tbl(1)
674 ,l_invattr_tbl(2)
675 ,l_invattr_tbl(3)
676 ,l_invattr_tbl(4)
677 ,l_invattr_tbl(5)
678 ,l_invattr_tbl(6)
679 ,l_invattr_tbl(7)
680 ,l_invattr_tbl(8)
681 ,l_invattr_tbl(9)
682 ,l_invattr_tbl(10)
683 ,l_invattr_tbl(11)
684 ,l_invattr_tbl(12)
685 ,l_invattr_tbl(13)
686 ,l_invattr_tbl(14)
687 ,l_invattr_tbl(15)
688 FROM mtl_lot_numbers
689 WHERE lot_number=nvl(p_src_lot_number,p_lot_number)
690 AND inventory_item_id=nvl(p_src_inv_item_id,p_inv_item_id)
691 AND organization_id=p_org_id;
692
693
694
695 -- Populate the flex field record
696
697 l_dflex_r.application_id := 401;
698 l_dflex_r.flexfield_name := 'Lot Attributes';
699
700 -- Get all contexts
701
702 fnd_dflex.get_contexts(flexfield => l_dflex_r, contexts => l_contexts_dr);
703
704 -- From the l_contexts_dr, get the position of the global context
705
706 l_global_context := NULL;
707 l_context := NULL;
708 l_global_context := l_contexts_dr.global_context;
709 l_context := l_contexts_dr.context_code(l_global_context);
710
711 -- Prepare the l_context_r type for getting the segments associated with the global context
712
713 l_context_r.flexfield := l_dflex_r;
714 l_context_r.context_code := l_context;
715
716 -- Get the segments for the context
717
718 fnd_dflex.get_segments(CONTEXT => l_context_r, segments => l_segments_dr, enabled_only => TRUE);
719
720 -- Loop through the global segments
721
722 FOR wms_context_index IN 1 .. l_segments_dr.nsegments LOOP
723
724 IF SUBSTR(l_segments_dr.application_column_name(wms_context_index),
725 INSTR(l_segments_dr.application_column_name(wms_context_index),'ATTRIBUTE')
726 -2,2) = 'C_'
727 THEN
728 l_Cattr_tbl(SUBSTR( l_segments_dr.application_column_name(wms_context_index)
729 ,INSTR(l_segments_dr.application_column_name(wms_context_index),
730 'ATTRIBUTE') + 9))
731 :=
732
733 l_temp_Cattr_tbl(SUBSTR( l_segments_dr.application_column_name(wms_context_index)
734 ,INSTR(l_segments_dr.application_column_name(wms_context_index),
735 'ATTRIBUTE') + 9));
736
737
738 ELSIF SUBSTR(l_segments_dr.application_column_name(wms_context_index),
739 INSTR(l_segments_dr.application_column_name(wms_context_index),'ATTRIBUTE')
740 -2,2) = 'N_'
741 THEN
742 l_Nattr_tbl(SUBSTR( l_segments_dr.application_column_name(wms_context_index)
743 ,INSTR(l_segments_dr.application_column_name(wms_context_index),
744 'ATTRIBUTE') + 9))
745 :=
746
747 l_temp_Nattr_tbl(SUBSTR( l_segments_dr.application_column_name(wms_context_index)
748 ,INSTR(l_segments_dr.application_column_name(wms_context_index),
749 'ATTRIBUTE') + 9));
750
751
752 ELSIF SUBSTR(l_segments_dr.application_column_name(wms_context_index),
753 INSTR(l_segments_dr.application_column_name(wms_context_index),'ATTRIBUTE')
754 -2,2) = 'D_'
755 THEN
756 l_Dattr_tbl(SUBSTR( l_segments_dr.application_column_name(wms_context_index)
757 ,INSTR(l_segments_dr.application_column_name(wms_context_index),
758 'ATTRIBUTE') + 9))
759 :=
760
761 l_temp_Dattr_tbl(SUBSTR( l_segments_dr.application_column_name(wms_context_index)
762 ,INSTR(l_segments_dr.application_column_name(wms_context_index),
763 'ATTRIBUTE') + 9));
764
765
766 ELSIF l_segments_dr.application_column_name(wms_context_index) = 'GRADE_CODE' THEN
767 l_grade_code := l_temp_grade_code;
768 ELSIF l_segments_dr.application_column_name(wms_context_index) = 'ORIGINATION_DATE' THEN
769 l_origination_date := l_temp_origination_date;
770 ELSIF l_segments_dr.application_column_name(wms_context_index) = 'DATE_CODE' THEN
771 l_date_code := l_temp_date_code;
772 ELSIF l_segments_dr.application_column_name(wms_context_index) = 'CHANGE_DATE' THEN
773 l_change_date := l_temp_change_date;
774 ELSIF l_segments_dr.application_column_name(wms_context_index) = 'AGE' THEN
775 l_age := l_temp_age;
776 ELSIF l_segments_dr.application_column_name(wms_context_index) = 'RETEST_DATE' THEN
777 l_retest_date := l_temp_retest_date;
778 ELSIF l_segments_dr.application_column_name(wms_context_index) = 'MATURITY_DATE' THEN
779 l_maturity_date := l_temp_maturity_date;
780 ELSIF l_segments_dr.application_column_name(wms_context_index) = 'ITEM_SIZE' THEN
781 l_item_size := l_temp_item_size;
782 ELSIF l_segments_dr.application_column_name(wms_context_index) = 'COLOR' THEN
783 l_color := l_temp_color;
784 ELSIF l_segments_dr.application_column_name(wms_context_index) = 'VOLUME' THEN
785 l_volume := l_temp_volume;
786 ELSIF l_segments_dr.application_column_name(wms_context_index) = 'VOLUME_UOM' THEN
787 l_volume_uom := l_temp_volume_uom;
788 ELSIF l_segments_dr.application_column_name(wms_context_index) = 'PLACE_OF_ORIGIN' THEN
789 l_place_of_origin := l_temp_place_of_origin;
790 ELSIF l_segments_dr.application_column_name(wms_context_index) = 'BEST_BY_DATE' THEN
791 l_best_by_date := l_temp_best_by_date;
792 ELSIF l_segments_dr.application_column_name(wms_context_index) = 'LENGTH' THEN
793 l_length := l_temp_length;
794 ELSIF l_segments_dr.application_column_name(wms_context_index) = 'LENGTH_UOM' THEN
795 l_length_uom := l_temp_length_uom;
796 ELSIF l_segments_dr.application_column_name(wms_context_index) = 'RECYCLED_CONTENT' THEN
797 l_recycled_content := l_temp_recycled_content;
798 ELSIF l_segments_dr.application_column_name(wms_context_index) = 'THICKNESS' THEN
799 l_thickness := l_temp_thickness;
800 ELSIF l_segments_dr.application_column_name(wms_context_index) = 'THICKNESS_UOM' THEN
801 l_thickness_uom := l_temp_thickness_uom;
802 ELSIF l_segments_dr.application_column_name(wms_context_index) = 'WIDTH' THEN
803 l_width := l_temp_width;
804 ELSIF l_segments_dr.application_column_name(wms_context_index) = 'WIDTH_UOM' THEN
805 l_width_uom := l_temp_width_uom;
806 ELSIF l_segments_dr.application_column_name(wms_context_index) = 'VENDOR_ID' THEN
807 l_vendor_id := l_temp_vendor_id;
808 ELSIF l_segments_dr.application_column_name(wms_context_index) = 'VENDOR_NAME' THEN
809 l_vendor_name := l_temp_vendor_name;
810 ELSIF l_segments_dr.application_column_name(wms_context_index) = 'TERRITORY_CODE' THEN
811 l_territory_code := l_temp_territory_code;
812 ELSIF l_segments_dr.application_column_name(wms_context_index) = 'SUPPLIER_LOT_NUMBER' THEN
813 l_supplier_lot_number := l_temp_supplier_lot_number;
814 ELSIF l_segments_dr.application_column_name(wms_context_index) = 'CURL_WRINKLE_FOLD' THEN
815 l_curl_wrinkle_fold := l_curl_wrinkle_fold;
816 END IF;
817
818 END LOOP;
819
820
821
822 else -- Bug 5367283 if wms is not installed
823
824 SELECT
825 attribute_category
826 , attribute1
827 , attribute2
828 , attribute3
829 , attribute4
830 , attribute5
831 , attribute6
832 , attribute7
833 , attribute8
834 , attribute9
835 , attribute10
836 , attribute11
837 , attribute12
838 , attribute13
839 , attribute14
840 , attribute15
841 INTO
842 l_attribute_category
843 ,l_invattr_tbl(1)
844 ,l_invattr_tbl(2)
845 ,l_invattr_tbl(3)
846 ,l_invattr_tbl(4)
847 ,l_invattr_tbl(5)
848 ,l_invattr_tbl(6)
849 ,l_invattr_tbl(7)
850 ,l_invattr_tbl(8)
851 ,l_invattr_tbl(9)
852 ,l_invattr_tbl(10)
853 ,l_invattr_tbl(11)
854 ,l_invattr_tbl(12)
855 ,l_invattr_tbl(13)
856 ,l_invattr_tbl(14)
857 ,l_invattr_tbl(15)
858 FROM mtl_lot_numbers
859 WHERE lot_number=nvl(p_src_lot_number,p_lot_number) -- modified for fixing bug 5126021
860 AND inventory_item_id=nvl(p_src_inv_item_id,p_inv_item_id) -- modified for fixing bug 5126021
861 AND organization_id=p_org_id;
862
863
864 end if; -- bug 5367283 if clause for wms installed
865
866 l_call_inv_lotapi :=TRUE;
867
868 end if; -- l_copy_from_src
869 end if; -- NOT (x_lot_exists='N' and x_src_lot_exists='N') Bug 5282172.
870 --END IF; -- x_lot_exists='N'
871
872 if g_debug then
873 pdebug('before select from mtli');
874 end if;
875
876 /* Added for fixing bug 5126021 */
877
878 if l_intf_rec_found then
879 SELECT
880 transaction_interface_id
881 ,decode(description,l_miss_char, NULL,NULL,l_description,description)
882 ,decode(grade_code,l_miss_char, NULL,NULL,l_grade_code,grade_code)
883 ,decode(origination_date,l_miss_date, NULL,NULL,l_origination_date,origination_date)
884 ,decode(date_code,l_miss_char, NULL,NULL,l_date_code,date_code)
885 ,decode(change_date,l_miss_date, NULL,NULL,l_change_date,change_date)
886 ,decode(age,l_miss_num, NULL,NULL,l_age,age)
887 ,decode(retest_date,l_miss_date, NULL,NULL,l_retest_date,retest_date)
888 ,decode(maturity_date,l_miss_date, NULL,NULL,l_maturity_date,maturity_date)
889 ,decode(item_size,l_miss_num, NULL,NULL,l_item_size,item_size)
890 ,decode(color,l_miss_char, NULL,NULL,l_color,color)
891 ,decode(volume,l_miss_num, NULL,NULL,l_volume,volume)
892 ,decode(volume_uom,l_miss_char, NULL,NULL,l_volume_uom,volume_uom)
893 ,decode(place_of_origin,l_miss_char, NULL,NULL,l_place_of_origin,place_of_origin)
894 ,decode(best_by_date,l_miss_date, NULL,NULL,l_best_by_date,best_by_date)
895 ,decode(length,l_miss_num, NULL,NULL,l_length,length)
896 ,decode(length_uom,l_miss_char, NULL,NULL,l_length_uom,length_uom)
897 ,decode(recycled_content,l_miss_num, NULL,NULL,l_recycled_content,recycled_content)
898 ,decode(thickness,l_miss_num, NULL,NULL,l_thickness,thickness)
899 ,decode(thickness_uom,l_miss_char, NULL,NULL,l_thickness_uom,thickness_uom)
900 ,decode(width,l_miss_num, NULL,NULL,l_width,width)
901 ,decode(width_uom,l_miss_char, NULL,NULL,l_width_uom,width_uom)
902 ,decode(vendor_id,l_miss_num, NULL,NULL,l_vendor_id,vendor_id)
903 ,decode(vendor_name,l_miss_char, NULL,NULL,l_vendor_name,vendor_name)
904 ,decode(territory_code,l_miss_char,NULL,NULL,l_territory_code,territory_code)
905 ,decode(supplier_lot_number,l_miss_char, NULL,NULL,l_supplier_lot_number,supplier_lot_number)
906 ,decode(curl_wrinkle_fold,l_miss_char, NULL,NULL,l_curl_wrinkle_fold,curl_wrinkle_fold)
907 ,decode(lot_attribute_category,l_miss_char, NULL,NULL,l_lot_attribute_category,lot_attribute_category)
908 ,decode(c_attribute1,l_miss_char, NULL,NULL,l_Cattr_tbl(1),c_attribute1)
909 ,decode(c_attribute2,l_miss_char, NULL,NULL,l_Cattr_tbl(2),c_attribute2)
910 ,decode(c_attribute3,l_miss_char, NULL,NULL,l_Cattr_tbl(3),c_attribute3)
911 ,decode(c_attribute4,l_miss_char, NULL,NULL,l_Cattr_tbl(4),c_attribute4)
912 ,decode(c_attribute5,l_miss_char, NULL,NULL,l_Cattr_tbl(5),c_attribute5)
913 ,decode(c_attribute6,l_miss_char, NULL,NULL,l_Cattr_tbl(6),c_attribute6)
914 ,decode(c_attribute7,l_miss_char, NULL,NULL,l_Cattr_tbl(7),c_attribute7)
915 ,decode(c_attribute8,l_miss_char, NULL,NULL,l_Cattr_tbl(8),c_attribute8)
916 ,decode(c_attribute9,l_miss_char, NULL,NULL,l_Cattr_tbl(9),c_attribute9)
917 ,decode(c_attribute10,l_miss_char, NULL,NULL,l_Cattr_tbl(10),c_attribute10)
918 ,decode(c_attribute11,l_miss_char, NULL,NULL,l_Cattr_tbl(11),c_attribute11)
919 ,decode(c_attribute12,l_miss_char, NULL,NULL,l_Cattr_tbl(12),c_attribute12)
920 ,decode(c_attribute13,l_miss_char, NULL,NULL,l_Cattr_tbl(13),c_attribute13)
921 ,decode(c_attribute14,l_miss_char, NULL,NULL,l_Cattr_tbl(14),c_attribute14)
922 ,decode(c_attribute15,l_miss_char, NULL,NULL,l_Cattr_tbl(15),c_attribute15)
923 ,decode(c_attribute16,l_miss_char, NULL,NULL,l_Cattr_tbl(16),c_attribute16)
924 ,decode(c_attribute17,l_miss_char, NULL,NULL,l_Cattr_tbl(17),c_attribute17)
925 ,decode(c_attribute18,l_miss_char, NULL,NULL,l_Cattr_tbl(18),c_attribute18)
926 ,decode(c_attribute19,l_miss_char, NULL,NULL,l_Cattr_tbl(19),c_attribute19)
927 ,decode(c_attribute20,l_miss_char, NULL,NULL,l_Cattr_tbl(20),c_attribute20)
928 ,decode(d_attribute1,l_miss_date, NULL,NULL,l_Dattr_tbl(1),d_attribute1)
929 ,decode(d_attribute2,l_miss_date, NULL,NULL,l_Dattr_tbl(2),d_attribute2)
930 ,decode(d_attribute3,l_miss_date, NULL,NULL,l_Dattr_tbl(3),d_attribute3)
931 ,decode(d_attribute4,l_miss_date, NULL,NULL,l_Dattr_tbl(4),d_attribute4)
932 ,decode(d_attribute5,l_miss_date, NULL,NULL,l_Dattr_tbl(5),d_attribute5)
933 ,decode(d_attribute6,l_miss_date, NULL,NULL,l_Dattr_tbl(6),d_attribute6)
934 ,decode(d_attribute7,l_miss_date, NULL,NULL,l_Dattr_tbl(7),d_attribute7)
935 ,decode(d_attribute8,l_miss_date, NULL,NULL,l_Dattr_tbl(8),d_attribute8)
936 ,decode(d_attribute9,l_miss_date, NULL,NULL,l_Dattr_tbl(9),d_attribute9)
937 ,decode(d_attribute10,l_miss_date, NULL,NULL,l_Dattr_tbl(10),d_attribute10)
938 ,decode(n_attribute1,l_miss_num, NULL,NULL,l_Nattr_tbl(1),n_attribute1)
939 ,decode(n_attribute2,l_miss_num, NULL,NULL,l_Nattr_tbl(2),n_attribute2)
940 ,decode(n_attribute3,l_miss_num, NULL,NULL,l_Nattr_tbl(3),n_attribute3)
941 ,decode(n_attribute4,l_miss_num, NULL,NULL,l_Nattr_tbl(4),n_attribute4)
942 ,decode(n_attribute5,l_miss_num, NULL,NULL,l_Nattr_tbl(5),n_attribute5)
943 ,decode(n_attribute6,l_miss_num, NULL,NULL,l_Nattr_tbl(6),n_attribute6)
944 ,decode(n_attribute7,l_miss_num, NULL,NULL,l_Nattr_tbl(7),n_attribute7)
945 ,decode(n_attribute8,l_miss_num, NULL,NULL,l_Nattr_tbl(8),n_attribute8)
946 ,decode(n_attribute9,l_miss_num, NULL,NULL,l_Nattr_tbl(9),n_attribute9)
947 ,decode(n_attribute10,l_miss_num, NULL,NULL,l_Nattr_tbl(10),n_attribute10)
948 ,decode(attribute_category,l_miss_char, NULL,NULL,l_attribute_category,attribute_category)
949 ,decode(attribute1,l_miss_char, NULL,NULL,l_invattr_tbl(1),attribute1)
950 ,decode(attribute2,l_miss_char, NULL,NULL,l_invattr_tbl(2),attribute2)
951 ,decode(attribute3,l_miss_char, NULL,NULL,l_invattr_tbl(3),attribute3)
952 ,decode(attribute4,l_miss_char, NULL,NULL,l_invattr_tbl(4),attribute4)
953 ,decode(attribute5,l_miss_char, NULL,NULL,l_invattr_tbl(5),attribute5)
954 ,decode(attribute6,l_miss_char, NULL,NULL,l_invattr_tbl(6),attribute6)
955 ,decode(attribute7,l_miss_char, NULL,NULL,l_invattr_tbl(7),attribute7)
956 ,decode(attribute8,l_miss_char, NULL,NULL,l_invattr_tbl(8),attribute8)
957 ,decode(attribute9,l_miss_char, NULL,NULL,l_invattr_tbl(9),attribute9)
958 ,decode(attribute10,l_miss_char, NULL,NULL,l_invattr_tbl(10),attribute10)
959 ,decode(attribute11,l_miss_char, NULL,NULL,l_invattr_tbl(11),attribute11)
960 ,decode(attribute12,l_miss_char, NULL,NULL,l_invattr_tbl(12),attribute12)
961 ,decode(attribute13,l_miss_char, NULL,NULL,l_invattr_tbl(13),attribute13)
962 ,decode(attribute14,l_miss_char, NULL,NULL,l_invattr_tbl(14),attribute14)
963 ,decode(attribute15,l_miss_char, NULL,NULL,l_invattr_tbl(15),attribute15)
964 INTO
965 l_mtli_txn_id
966 ,l_description
967 ,l_grade_code
968 ,l_origination_date
969 ,l_date_code
970 ,l_change_date
971 ,l_age
972 ,l_retest_date
973 ,l_maturity_date
974 ,l_item_size
975 ,l_color
976 ,l_volume
977 ,l_volume_uom
978 ,l_place_of_origin
979 ,l_best_by_date
980 ,l_length
981 ,l_length_uom
982 ,l_recycled_content
983 ,l_thickness
984 ,l_thickness_uom
985 ,l_width
986 ,l_width_uom
987 ,l_vendor_id
988 ,l_vendor_name
989 ,l_territory_code
990 ,l_supplier_lot_number
991 ,l_curl_wrinkle_fold
992 ,l_lot_attribute_category
993 ,l_Cattr_tbl(1)
994 ,l_Cattr_tbl(2)
995 ,l_Cattr_tbl(3)
996 ,l_Cattr_tbl(4)
997 ,l_Cattr_tbl(5)
998 ,l_Cattr_tbl(6)
999 ,l_Cattr_tbl(7)
1000 ,l_Cattr_tbl(8)
1001 ,l_Cattr_tbl(9)
1002 ,l_Cattr_tbl(10)
1003 ,l_Cattr_tbl(11)
1004 ,l_Cattr_tbl(12)
1005 ,l_Cattr_tbl(13)
1006 ,l_Cattr_tbl(14)
1007 ,l_Cattr_tbl(15)
1008 ,l_Cattr_tbl(16)
1009 ,l_Cattr_tbl(17)
1010 ,l_Cattr_tbl(18)
1011 ,l_Cattr_tbl(19)
1012 ,l_Cattr_tbl(20)
1013 ,l_Dattr_tbl(1)
1014 ,l_Dattr_tbl(2)
1015 ,l_Dattr_tbl(3)
1016 ,l_Dattr_tbl(4)
1017 ,l_Dattr_tbl(5)
1018 ,l_Dattr_tbl(6)
1019 ,l_Dattr_tbl(7)
1020 ,l_Dattr_tbl(8)
1021 ,l_Dattr_tbl(9)
1022 ,l_Dattr_tbl(10)
1023 ,l_Nattr_tbl(1)
1024 ,l_Nattr_tbl(2)
1025 ,l_Nattr_tbl(3)
1026 ,l_Nattr_tbl(4)
1027 ,l_Nattr_tbl(5)
1028 ,l_Nattr_tbl(6)
1029 ,l_Nattr_tbl(7)
1030 ,l_Nattr_tbl(8)
1031 ,l_Nattr_tbl(9)
1032 ,l_Nattr_tbl(10)
1033 ,l_attribute_category
1034 ,l_invattr_tbl(1)
1035 ,l_invattr_tbl(2)
1036 ,l_invattr_tbl(3)
1037 ,l_invattr_tbl(4)
1038 ,l_invattr_tbl(5)
1039 ,l_invattr_tbl(6)
1040 ,l_invattr_tbl(7)
1041 ,l_invattr_tbl(8)
1042 ,l_invattr_tbl(9)
1043 ,l_invattr_tbl(10)
1044 ,l_invattr_tbl(11)
1045 ,l_invattr_tbl(12)
1046 ,l_invattr_tbl(13)
1047 ,l_invattr_tbl(14)
1048 ,l_invattr_tbl(15)
1049 FROM mtl_transaction_lots_interface
1050 WHERE product_transaction_id=p_intf_txn_id
1051 AND product_code=p_intf_src_code
1052 AND lot_number=p_lot_number;
1053
1054 l_call_inv_lotapi :=TRUE;
1055
1056 if g_debug then
1057 pdebug('l_intf_rec_found=TRUE l_call_inv_lotapi=TRUE and l_copy_from_src=FALSE');
1058 end if;
1059
1060 else
1061
1062 if g_debug then
1063 pdebug('intf txnid=' || p_intf_txn_id);
1064 pdebug('product_code=' || p_intf_src_code ||' lotnumber=' || p_lot_number);
1065 end if;
1066 l_intf_rec_found:=FALSE;
1067 end if; -- l_intf_rec_found
1068
1069 /* Added for fixing bug 5126021 */
1070
1071 if (NOT l_call_inv_lotapi) and x_lot_exists='N' THEN
1072 if inv_lot_sel_attr.is_enabled('Lot Attributes',
1073 p_org_id,p_inv_item_id) >= 2 THEN
1074 l_wms_attr_required:=TRUE;
1075 end if;
1076 if inv_lot_sel_attr.is_enabled('MTL_LOT_NUMBERS',
1077 p_org_id,p_inv_item_id) >= 2 THEN
1078 l_inv_attr_required:=TRUE;
1079 end if;
1080 if (l_wms_attr_required OR l_inv_attr_required) THEN
1081 fnd_message.set_name('WSM','WSM_REQUIRED_ATTR_NO_INTF');
1082 x_err_msg:=FND_MESSAGE.GET;
1083 x_err_code:=-1;
1084 return;
1085 end if;
1086 end if;
1087
1088 if l_call_inv_lotapi THEN
1089 if x_lot_exists='Y' THEN
1090 if g_debug then
1091 pdebug('x_lot_exists=Y');
1092 pdebug('Before Calling inv_lot_api_pub.Update_inv_lot lot attrcat ' ||
1093 l_lot_attribute_category ||
1094 ' attr_category='|| l_attribute_category);
1095 end if;
1096 inv_lot_api_pub.Update_inv_lot(
1097 x_return_status => x_return_status,
1098 x_msg_count => x_msg_count,
1099 x_msg_data => x_err_msg,
1100 p_inventory_item_id => p_inv_item_id,
1101 p_organization_id => p_org_id,
1102 p_lot_number => p_lot_number,
1103 p_expiration_date => NULL,
1104 p_disable_flag => NULL,
1105 p_attribute_category => l_attribute_category,
1106 p_lot_attribute_category=> l_lot_attribute_category,
1107 p_attributes_tbl => l_Invattr_tbl,
1108 p_c_attributes_tbl => l_CAttr_tbl,
1109 p_n_attributes_tbl => l_NAttr_tbl,
1110 p_d_attributes_tbl => l_DAttr_tbl,
1111 p_grade_code => l_grade_code,
1112 p_origination_date => l_origination_date,
1113 p_date_code => l_date_code,
1114 p_status_id => l_status_id,
1115 p_change_date => l_change_date,
1116 p_age => l_age,
1117 p_retest_date => l_retest_date,
1118 p_maturity_date => l_maturity_date,
1119 p_item_size => l_item_size,
1120 p_color => l_color,
1121 p_volume => l_volume,
1122 p_volume_uom => l_volume_uom,
1123 p_place_of_origin => l_place_of_origin,
1124 p_best_by_date => l_best_by_date,
1125 p_length => l_length,
1126 p_length_uom => l_length_uom,
1127 p_recycled_content => l_recycled_content,
1128 p_thickness => l_thickness,
1129 p_thickness_uom => l_thickness_uom,
1130 p_width => l_width,
1131 p_width_uom => l_width_uom,
1132 p_territory_code => l_territory_code,
1133 p_supplier_lot_number => l_supplier_lot_number,
1134 p_vendor_name => l_vendor_name,
1135 p_source => 2);
1136 ELSE -- x_lot_exists='N'
1137 if g_debug then
1138 pdebug('x_lot_exists=N');
1139 pdebug('Before Calling inv_lot_api_pub.Update_inv_lot lot attrcat ' ||
1140 l_lot_attribute_category ||
1141 'attr_category=' || l_attribute_category);
1142 end if;
1143 inv_lot_api_pub.create_inv_lot(
1144 x_return_status => x_return_status,
1145 x_msg_count => x_msg_count,
1146 x_msg_data => x_err_msg,
1147 p_inventory_item_id => p_inv_item_id,
1148 p_organization_id => p_org_id,
1149 p_lot_number => p_lot_number,
1150 p_expiration_date => NULL,
1151 p_disable_flag => NULL,
1152 p_attribute_category => l_attribute_category,
1153 p_lot_attribute_category=> l_lot_attribute_category,
1154 p_attributes_tbl => l_Invattr_tbl,
1155 p_c_attributes_tbl => l_CAttr_tbl,
1156 p_n_attributes_tbl => l_NAttr_tbl,
1157 p_d_attributes_tbl => l_DAttr_tbl,
1158 p_grade_code => l_grade_code,
1159 p_origination_date => l_origination_date,
1160 p_date_code => l_date_code,
1161 p_status_id => l_status_id,
1162 p_change_date => l_change_date,
1163 p_age => l_age,
1164 p_retest_date => l_retest_date,
1165 p_maturity_date => l_maturity_date,
1166 p_item_size => l_item_size,
1167 p_color => l_color,
1168 p_volume => l_volume,
1169 p_volume_uom => l_volume_uom,
1170 p_place_of_origin => l_place_of_origin,
1171 p_best_by_date => l_best_by_date,
1172 p_length => l_length,
1173 p_length_uom => l_length_uom,
1174 p_recycled_content => l_recycled_content,
1175 p_thickness => l_thickness,
1176 p_thickness_uom => l_thickness_uom,
1177 p_width => l_width,
1178 p_width_uom => l_width_uom,
1179 p_territory_code => l_territory_code,
1180 p_supplier_lot_number => l_supplier_lot_number,
1181 p_vendor_name => l_vendor_name,
1182 p_source => 2);
1183 END IF; -- x_lot_exists='Y'
1184 if g_debug then
1185 pdebug('Return status= ' || x_return_status);
1186 end if;
1187 if x_return_status <> 'S' THEN
1188 pdebug('Error msg=' || x_err_msg);
1189 x_err_code:=-1;
1190 end if;
1191 END IF; -- l_call_inv_lotapi
1192
1193 EXCEPTION
1194 WHEN OTHERS THEN
1195 x_err_code := SQLCODE;
1196 x_err_msg := 'WSM_LotAttr_PVT.create_update_lotattr '|| SUBSTR(SQLERRM,1,2000);
1197 return;
1198 END create_update_lotattr;
1199
1200 Procedure create_update_lotattr(x_err_code OUT NOCOPY VARCHAR2,
1201 x_err_msg OUT NOCOPY VARCHAR2,
1202 p_wip_entity_id IN NUMBER,
1203 p_org_id IN NUMBER,
1204 p_intf_txn_id IN NUMBER,
1205 p_intf_src_code IN VARCHAR2,
1206 p_src_lot_number IN VARCHAR2 DEFAULT NULL,
1207 p_src_inv_item_id IN NUMBER DEFAULT NULL) is
1208
1209 l_lot_number VARCHAR2(80):=NULL; -- Changed for OPM Convergence project
1210 l_inv_item_id NUMBER:=NULL;
1211
1212 BEGIN
1213 SELECT lot_number, primary_item_id
1214 INTO l_lot_number, l_inv_item_id
1215 FROM wip_discrete_jobs
1216 WHERE wip_entity_id=p_wip_entity_id;
1217
1218 WSM_LotAttr_PVT.create_update_lotattr(x_err_code,
1219 x_err_msg,
1220 l_lot_number,
1221 l_inv_item_id,
1222 p_org_id,
1223 p_intf_txn_id,
1224 p_intf_src_code,
1225 p_src_lot_number,
1226 p_src_inv_item_id);
1227
1228 EXCEPTION
1229 WHEN OTHERS THEN
1230 fnd_message.set_name('WSM','WSM_INVALID_FIELD');
1231 fnd_message.set_token('FLD_NAME','WIP_ENTITY_ID');
1232 x_err_msg:=fnd_message.get;
1233 x_err_code:=-1;
1234 return;
1235
1236 END create_update_lotattr;
1237
1238 END WSM_LotAttr_PVT;