[Home] [Help]
PACKAGE BODY: APPS.INV_LOT_APIS
Source
1 PACKAGE BODY INV_LOT_APIS AS
2 /* $Header: INVLOTAB.pls 120.7.12020000.3 2013/04/17 13:21:24 ptian ship $ */
3
4
5 -- Global constant holding the package name
6 g_pkg_name CONSTANT VARCHAR2 ( 30 ) := 'INV_LOT_APIS';
7
8
9 PROCEDURE print_debug ( p_err_msg VARCHAR2, p_level NUMBER DEFAULT 1)
10 IS
11 l_debug number := 1;--NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
12 BEGIN
13 IF (g_debug = 1) THEN
14 inv_mobile_helper_functions.tracelog (
15 p_err_msg => p_err_msg,
16 p_module => 'INV_LOT_APIS',
17 p_level => p_level
18 );
19 --DBMS_OUTPUT.PUT_LINE(p_err_msg);
20 END IF;
21 --DBMS_OUTPUT.PUT_LINE(p_err_msg);
22 END print_debug;
23
24 PROCEDURE EXPIRATION_ACTION_CODE( x_codes OUT NOCOPY t_genref,
25 p_code IN VARCHAR2) IS
26 BEGIN
27 If p_code IS NOT NULL
28 THEN
29 OPEN x_codes for
30 SELECT action_code, Description
31 FROM mtl_actions
32 WHERE NVL(disable_flag,'N') = 'N'
33 AND action_code like (p_code);
34 Else
35 OPEN x_codes for
36 SELECT action_code, Description
37 FROM mtl_actions
38 WHERE NVL(disable_flag,'N') = 'N';
39 END IF;
40
41 END expiration_action_code;
42
43
44 PROCEDURE GET_YES_NO( x_option OUT NOCOPY t_genref) IS
45 BEGIN
46 OPEN x_option for
47 SELECT 'YES' FROM DUAL
48 UNION
49 SELECT 'NO' FROM DUAL;
50 END get_yes_no;
51
52 PROCEDURE GET_YES_NO( x_option OUT NOCOPY t_genref
53 , p_option IN VARCHAR2) IS
54 BEGIN
55 OPEN x_option for
56 SELECT 'YES' FROM DUAL WHERE 'YES' LIKE upper(p_option)
57 UNION
58 SELECT 'NO' FROM DUAL WHERE 'NO' LIKE upper(p_option);
59 END get_yes_no;
60
61
62 PROCEDURE get_grade_codes ( x_grades OUT NOCOPY t_genref
63 , p_grade_code IN VARCHAR2) IS
64 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
65 BEGIN
66
67 IF (l_debug = 1) THEN
68 inv_pick_wave_pick_confirm_pub.tracelog ( 'Inside get_grace_codes API' , 'INV_PROCESS_LOT_API');
69 END IF;
70 If p_grade_code IS NOT NULL THEN
71 OPEN x_grades FOR
72 SELECT grade_code
73 , description
74 FROM mtl_grades
75 WHERE grade_code LIKE (p_grade_code)
76 AND disable_flag <> 'Y';
77 ELSE
78 OPEN x_grades FOR
79 SELECT grade_code
80 , description
81 FROM mtl_grades
82 WHERE disable_flag <> 'Y';
83 End IF;
84
85 END get_grade_codes;
86
87 PROCEDURE get_named_attributes ( x_lot_att OUT nocopy t_genref --- get_opm_lot_attributes
88 , p_inventory_item_id IN NUMBER
89 , p_organization_id IN NUMBER
90 , p_lot_number IN VARCHAR2
91 , p_parent_lot_number IN VARCHAR2) IS
92 /**
93 * A new api has been written to populate the lot attributes given a lot, item and parent lot.
94 * The API details can be found in the Create Lot Api TDD. Since there is no direct way to return
95 * plsql tables back to the java client, this procedure is meant to act as a wrapper over
96 * populate_lot_attributes, which returns the attributes in a plsql table.
97 * These attributes are selected from dual and passed back as a ref cursor to the client.
98 **/
99 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
100 BEGIN
101
102 IF (l_debug = 1) THEN
103 inv_pick_wave_pick_confirm_pub.tracelog ( 'Inside get_opm_lot_attributes API' , 'INV_LOT_APIS');
104 END IF;
105
106 If nvl(p_parent_lot_number, ' ') = ' ' THEN
107 OPEN x_lot_att for
108 SELECT
109 ---- Added for Bug #3952081 + #4093379
110 nvl(mln.parent_lot_number,'') parent_lot_number
111 , nvl(mln.grade_code,'') grade_code
112 , nvl(mln.origination_type,'') origination_type
113 , nvl(TO_CHAR(mln.origination_date,'YYYY-MM-DD'),'') origination_date --YYYY-MM-DD
114 , nvl(TO_CHAR(mln.expiration_action_date,'YYYY-MM-DD'),'') expiration_action_date
115 , nvl(mln.expiration_action_code,'') expiration_action_code
116 , nvl(TO_CHAR(mln.retest_date,'YYYY-MM-DD'),'') retest_date
117 , nvl(TO_CHAR(mln.hold_date,'YYYY-MM-DD'),'') hold_date
118 , nvl(TO_CHAR(mln.maturity_date,'YYYY-MM-DD'),'') maturity_date
119 , nvl(mln.supplier_lot_number,'') supplier_lot_number
120 FROM mtl_lot_numbers mln
121 WHERE inventory_item_id = p_inventory_item_id
122 AND organization_id = p_organization_id
123 AND lot_number = p_lot_number;
124
125 ELSE
126 OPEN x_lot_att for
127 SELECT
128 ---- Added for Bug #3952081 + #4093379
129 nvl(mln.parent_lot_number,'') parent_lot_number
130 , nvl(mln.grade_code,'') grade_code
131 , nvl(mln.origination_type,'') origination_type
132 , nvl(TO_CHAR(mln.origination_date,'YYYY-MM-DD'),'') origination_date --YYYY-MM-DD
133 , nvl(TO_CHAR(mln.expiration_action_date,'YYYY-MM-DD'),'') expiration_action_date
134 , nvl(mln.expiration_action_code,'') expiration_action_code
135 , nvl(TO_CHAR(mln.retest_date,'YYYY-MM-DD'),'') retest_date
136 , nvl(TO_CHAR(mln.hold_date,'YYYY-MM-DD'),'') hold_date
137 , nvl(TO_CHAR(mln.maturity_date,'YYYY-MM-DD'),'') maturity_date
138 , nvl(mln.supplier_lot_number,'') supplier_lot_number
139 FROM mtl_lot_numbers mln
140 WHERE inventory_item_id = p_inventory_item_id
141 AND organization_id = p_organization_id
142 AND lot_number = p_lot_number
143 AND nvl(parent_lot_number,' ') = nvl(p_parent_lot_number, ' ');
144
145 END IF;
146
147
148 END get_named_attributes;
149
150
151 PROCEDURE get_opm_item_attributes( x_item_lot_att OUT nocopy t_genref
152 , p_inventory_item_id IN NUMBER
153 , p_organization_id IN NUMBER ) IS
154 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
155 BEGIN
156
157 IF (l_debug = 1) THEN
158 inv_pick_wave_pick_confirm_pub.tracelog ( 'Inside get_opm_item_attributes API' , 'INV_PROCESS_LOT_API');
159 END IF;
160
161 open x_item_lot_att for
162 SELECT tracking_quantity_ind
163 , secondary_default_ind
164 , secondary_uom_code
165 , dual_uom_deviation_high
166 , dual_uom_deviation_low
167 , grade_control_flag
168 , default_grade
169 , child_lot_flag
170 , retest_interval
171 , expiration_action_interval
172 , expiration_action_code
173 , maturity_days
174 , hold_days
175 , copy_lot_attribute_flag
176 FROM mtl_system_items
177 WHERE inventory_item_id = p_inventory_item_id
178 AND organization_id = p_organization_id;
179
180 END get_opm_item_attributes;
181
182 PROCEDURE check_reservations(p_inventory_item_id IN NUMBER
183 , p_organization_id IN NUMBER
184 , p_lot_number IN VARCHAR2
185 , p_exists OUT NOCOPY VARCHAR2 )
186 IS
187
188 l_dummy NUMBER := 0;
189
190 BEGIN
191
192 SELECT 1
193 INTO l_dummy
194 FROM MTL_RESERVATIONS
195 WHERE inventory_item_id = p_inventory_item_id
196 AND organization_id = p_organization_id
197 AND lot_number = p_lot_number;
198
199 p_exists := 'TRUE';
200 EXCEPTION WHEN NO_DATA_FOUND THEN
201 p_exists := 'FALSE';
202 WHEN TOO_MANY_ROWS THEN
203 p_exists := 'TRUE';
204 END;
205
206 PROCEDURE validate_grade_code( p_grade_code IN VARCHAR
207 , p_org_id IN NUMBER
208 , p_inventory_item_id IN NUMBER
209 , p_grade_control_flag IN VARCHAR2
210 , x_return_status OUT NOCOPY VARCHAR2
211 , x_msg_count OUT NOCOPY NUMBER
212 , x_msg_data OUT NOCOPY VARCHAR2
213 , x_valid OUT NOCOPY VARCHAR2)
214 IS
215 IsVALID BOOLEAN := false;
216 BEGIN
217 ISVALID := INV_LOT_ATTR_PUB.validate_grade_code(
218 p_grade_code
219 , p_org_id
220 , p_inventory_item_id
221 , p_grade_control_flag
222 , x_return_status
223 , x_msg_count
224 , x_msg_data );
225
226 IF ISVALID = TRUE THEN
227 x_valid := 'TRUE';
228 ELSE
229 x_valid := 'FALSE';
230 END IF;
231
232 END;
233
234 PROCEDURE validate_exp_action_code( p_expiration_action_code IN VARCHAR
235 , p_org_id IN NUMBER
236 , p_inventory_item_id IN NUMBER
237 , p_shelf_life_code IN VARCHAR2
238 , x_return_status OUT NOCOPY VARCHAR2
239 , x_msg_count OUT NOCOPY NUMBER
240 , x_msg_data OUT NOCOPY VARCHAR2
241 , x_valid OUT NOCOPY VARCHAR2)
242 IS
243 IsVALID BOOLEAN := false;
244 BEGIN
245 ISVALID := INV_LOT_ATTR_PUB.validate_exp_action_code(
246 p_expiration_action_code
247 , p_org_id
248 , p_inventory_item_id
249 , p_shelf_life_code
250 , x_return_status
251 , x_msg_count
252 , x_msg_data );
253
254 IF ISVALID = TRUE THEN
255 x_valid := 'TRUE';
256 ELSE
257 x_valid := 'FALSE';
258 END IF;
259
260 END;
261
262 PROCEDURE validate_exp_action_date(
263 p_expiration_action_date IN DATE
264 , p_expiration_date IN DATE
265 , x_return_status OUT NOCOPY VARCHAR2
266 , x_msg_count OUT NOCOPY NUMBER
267 , x_msg_data OUT NOCOPY VARCHAR2
268 , x_valid OUT NOCOPY VARCHAR2)
269 IS
270 IsVALID BOOLEAN := false;
271 BEGIN
272 ISVALID := INV_LOT_ATTR_PUB.validate_exp_action_date(
273 p_expiration_action_date
274 , p_expiration_date
275 , x_return_status
276 , x_msg_count
277 , x_msg_data );
278 IF ISVALID = TRUE THEN
279 x_valid := 'TRUE';
280 ELSE
281 x_valid := 'FALSE';
282 END IF;
283
284 END;
285
286 PROCEDURE validate_hold_date(
287 p_hold_date IN DATE
288 , p_origination_date IN DATE
289 , x_return_status OUT NOCOPY VARCHAR2
290 , x_msg_count OUT NOCOPY NUMBER
291 , x_msg_data OUT NOCOPY VARCHAR2
292 , x_valid OUT NOCOPY VARCHAR2)
293 IS
294 IsVALID BOOLEAN := false;
295 BEGIN
296 ISVALID := INV_LOT_ATTR_PUB.validate_hold_date(
297 p_hold_date
298 , p_origination_date
299 , x_return_status
300 , x_msg_count
301 , x_msg_data );
302
303 IF ISVALID = TRUE THEN
304 x_valid := 'TRUE';
305 ELSE
306 x_valid := 'FALSE';
307 END IF;
308
309 END;
310
311
312 PROCEDURE validate_retest_date(
313 p_retest_date IN DATE
314 , p_origination_date IN DATE
315 , x_return_status OUT NOCOPY VARCHAR2
316 , x_msg_count OUT NOCOPY NUMBER
317 , x_msg_data OUT NOCOPY VARCHAR2
318 , x_valid OUT NOCOPY VARCHAR2)
319 IS
320 IsVALID BOOLEAN := false;
321 BEGIN
322 ISVALID := INV_LOT_ATTR_PUB.validate_retest_date(
323 p_retest_date
324 , p_origination_date
325 , x_return_status
326 , x_msg_count
327 , x_msg_data );
328
329 IF ISVALID = TRUE THEN
330 x_valid := 'TRUE';
331 ELSE
332 x_valid := 'FALSE';
333 END IF;
334
335 END;
336
337
338 PROCEDURE validate_maturity_date(
339 p_maturity_date IN DATE
340 , p_origination_date IN DATE
341 , x_return_status OUT NOCOPY VARCHAR2
342 , x_msg_count OUT NOCOPY NUMBER
343 , x_msg_data OUT NOCOPY VARCHAR2
344 , x_valid OUT NOCOPY VARCHAR2)
345 IS
346 IsVALID BOOLEAN := false;
347 BEGIN
348 ISVALID := INV_LOT_ATTR_PUB.validate_maturity_date(
349 p_maturity_date ,
350 p_origination_date ,
351 x_return_status ,
352 x_msg_count ,
353 x_msg_data );
354 IF ISVALID = TRUE THEN
355 x_valid := 'TRUE';
356 ELSE
357 x_valid := 'FALSE';
358 END IF;
359
360 END;
361
362
363 PROCEDURE GET_COPY_LOT_ATTR_FLAG( x_return_status OUT NOCOPY VARCHAR2
364 , x_msg_count OUT NOCOPY NUMBER
365 , x_msg_data OUT NOCOPY VARCHAR2
366 , x_copy_lot_attr_flag OUT NOCOPY VARCHAR2
367 , p_organization_id IN NUMBER
368 , p_inventory_item_id IN NUMBER
369 )
370 IS
371 /* Cursor definition to check if Lot UOM Conversion is needed */
372 CURSOR c_lot_uom_conv (cp_organization_id NUMBER) IS
373 SELECT copy_lot_attribute_flag,
374 lot_number_generation
375 FROM mtl_parameters
376 WHERE organization_id = cp_organization_id;
377
378 l_lot_uom_conv c_lot_uom_conv%ROWTYPE ;
379 l_copy_lot_attribute_flag Varchar2(10);
380
381 BEGIN
382 /* Check needed for Lot UOM conversion */
383 OPEN c_lot_uom_conv (p_organization_id) ;
384 FETCH c_lot_uom_conv INTO l_lot_uom_conv ;
385
386 IF c_lot_uom_conv%FOUND THEN
387 -- Possible values for mtl_parameters.lot_number_generation are:
388 -- 1 At organization level
389 -- 3 User defined
390 -- 2 At item level
391
392 IF l_lot_uom_conv.lot_number_generation = 1 THEN
393 l_copy_lot_attribute_flag := NVL(l_lot_uom_conv.copy_lot_attribute_flag,'N') ;
394
395 ELSIF l_lot_uom_conv.lot_number_generation IN (2,3) THEN
396 SELECT copy_lot_attribute_flag INTO l_copy_lot_attribute_flag
397 FROM mtl_system_items
398 WHERE inventory_item_id = p_inventory_item_id
399 AND organization_id = p_organization_id;
400 END IF;
401 ELSIF c_lot_uom_conv%FOUND THEN
402 SELECT copy_lot_attribute_flag INTO l_copy_lot_attribute_flag
403 FROM mtl_system_items
404 WHERE inventory_item_id = p_inventory_item_id
405 AND organization_id = p_organization_id;
406
407 END IF ;
408 CLOSE c_lot_uom_conv ;
409 x_copy_lot_attr_flag := l_copy_lot_attribute_flag ;
410
411 END ;
412
413
414 -- Procedure to Set Attributes of new Lot
415
416
417 PROCEDURE get_grade_codes(
418 x_grade_codes OUT NOCOPY t_genref
419 ) IS
420 BEGIN
421 OPEN x_grade_codes FOR
422 SELECT
423 GRADE_CODE , DESCRIPTION
424 FROM MTL_GRADES;
425 END;
426
427 PROCEDURE get_parent_lot_attributes ( x_lot_att OUT nocopy t_genref
428 , p_inventory_item_id IN NUMBER
429 , p_organization_id IN NUMBER
430 , p_lot_number IN VARCHAR2
431 ) IS
432 /**
433 * This API populates the lot attributes for a new Lot based on attributes of its parent lot.
434 * These attributes are selected from dual and passed back as a ref cursor to the client.
435 **/
436 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
437 BEGIN
438
439 IF (l_debug = 1) THEN
440 inv_pick_wave_pick_confirm_pub.tracelog ( 'Inside get_opm_lot_attributes API' , 'INV_LOT_APIS');
441 END IF;
442
443 OPEN x_lot_att for
444 SELECT
445 nvl(mln.grade_code,'') grade_code
446 , nvl(mln.origination_type,'') origination_type
447 , nvl(TO_CHAR(mln.origination_date,'YYYY-MM-DD'),'') origination_date --YYYY-MM-DD
448 , nvl(mln.expiration_action_code,'') expiration_action_code
449 , nvl(TO_CHAR(mln.expiration_action_date,'YYYY-MM-DD'),'') expiration_action_date
450 , nvl(TO_CHAR(mln.retest_date,'YYYY-MM-DD'),'') retest_date
451 , nvl(TO_CHAR(mln.hold_date,'YYYY-MM-DD'),'') hold_date
452 , nvl(TO_CHAR(mln.maturity_date,'YYYY-MM-DD'),'') maturity_date
453 , nvl(mln.supplier_lot_number,'') supplier_lot_number
454 -- nsinghi bug#5209065 rework. Fetch exp date also, to default it.
455 , nvl(TO_CHAR(mln.expiration_date,'YYYY-MM-DD'),'') expiration_date
456 FROM mtl_lot_numbers mln
457 WHERE inventory_item_id = p_inventory_item_id
458 AND organization_id = p_organization_id
459 AND lot_number = p_lot_number;
460
461 END get_parent_lot_attributes;
462
463 PROCEDURE Set_Msi_Default_Attr( x_lot_att OUT NOCOPY t_genref
464 , p_organization_id IN NUMBER
465 , p_inventory_item_id IN NUMBER
466 , p_lot_number IN VARCHAR2 DEFAULT NULL -- nsinghi bug#5209065 rework. Added this param.
467 ) IS
468
469 CURSOR c_get_dft_attr ( cp_inventory_item_id NUMBER, cp_organization_id NUMBER ) IS
470 SELECT grade_control_flag
471 , default_grade
472 , shelf_life_code
473 , shelf_life_days
474 , expiration_action_code
475 , expiration_action_interval
476 , retest_interval
477 , maturity_days
478 , hold_days
479 FROM mtl_system_items_b
480 WHERE organization_id = cp_organization_id
481 AND inventory_item_id = cp_inventory_item_id;
482
483 -- nsinghi bug#5209065 rework START. If existing lot,
484 -- fetch the lot attributes and assign those, otherwise default from item.
485 CURSOR c_get_lot_attr ( cp_inventory_item_id NUMBER, cp_organization_id NUMBER, cp_lot_number VARCHAR2 ) IS
486 SELECT grade_code
487 , expiration_date
488 , expiration_action_code
489 , expiration_action_date
490 , origination_date
491 , retest_date
492 , maturity_date
493 , hold_date
494 FROM mtl_lot_numbers
495 WHERE organization_id = cp_organization_id
496 AND inventory_item_id = cp_inventory_item_id
497 AND lot_number = cp_lot_number;
498
499 l_get_lot_attr_rec c_get_lot_attr%ROWTYPE;
500 l_new_lot BOOLEAN;
501 -- nsinghi bug#5209065 rework END.
502
503 -- nsinghi bug 5209065 START
504 l_mmtt_txn_tbl INV_CALCULATE_EXP_DATE.MMTT_TAB;
505 l_mti_txn_rec MTL_TRANSACTIONS_INTERFACE%ROWTYPE;
506 l_mtli_txn_rec MTL_TRANSACTION_LOTS_INTERFACE%ROWTYPE;
507 l_mmtt_txn_rec MTL_MATERIAL_TRANSACTIONS_TEMP%ROWTYPE;
508 l_mtlt_txn_rec MTL_TRANSACTION_LOTS_TEMP%ROWTYPE;
509 l_lot_expiration_date DATE;
510 -- nsinghi bug 5209065 END
511
512 l_get_dft_attr_rec c_get_dft_attr%ROWTYPE;
513
514 l_return_status VARCHAR2(1);
515 l_msg_count NUMBER;
516 l_msg_data VARCHAR2(3000);
517 x_grade_code VARCHAR2(150);
518 x_exp_action_code VARCHAR2(50) ;
519 x_origination_date DATE ;
520 x_exp_action_date DATE ;
521 x_hold_date DATE ;
522 x_maturity_date DATE ;
523 x_retest_date DATE ;
524 x_expiration_date DATE ;
525 BEGIN
526
527 x_grade_code := '';
528
529
530 /*Get default information from Mtl_System_Item */
531 OPEN c_get_dft_attr(p_inventory_item_id,p_organization_id);
532 FETCH c_get_dft_attr INTO l_get_dft_attr_rec;
533 CLOSE c_get_dft_attr;
534
535 /* Grade */
536 IF l_get_dft_attr_rec.grade_control_flag = 'Y' THEN
537 x_grade_code := l_get_dft_attr_rec.default_grade;
538 END IF;
539
540
541 /* Origination Date */
542 x_origination_date := SYSDATE ;
543
544 /* Expiration Date */
545 IF l_get_dft_attr_rec.shelf_life_code = 2 THEN -- Item shelf life days
546
547 /* nsinghi bug 5209065 START. For Receipt txn, there is no information available for
548 MTLI/MTLT record. This is because, when tabbing out of Lot LOV, there is no data related
549 to lot transaction. This data only gets built after user navigates through all lot fields.
550 Hence only passing the MMTT record to custom lot API. */
551
552 l_mmtt_txn_tbl := inv_calculate_exp_date.get_mmtt_tbl;
553 IF l_mmtt_txn_tbl.COUNT > 0 THEN
554 l_mmtt_txn_rec := l_mmtt_txn_tbl(0);
555 inv_calculate_exp_date.get_lot_expiration_date(
556 p_mtli_lot_rec => l_mtli_txn_rec
557 ,p_mti_trx_rec => l_mti_txn_rec
558 ,p_mtlt_lot_rec => l_mtlt_txn_rec
559 ,p_mmtt_trx_rec => l_mmtt_txn_rec
560 ,p_table => 2
561 ,x_lot_expiration_date => l_lot_expiration_date
562 ,x_return_status => l_return_status);
563
564 inv_calculate_exp_date.purge_mmtt_tab;
565 IF l_return_status <> fnd_api.g_ret_sts_success THEN
566 IF g_debug = 1 THEN
567 print_debug('Program inv_calculate_exp_date.get_lot_expiration_date has failed with a Unexpected exception', 9);
568 END IF;
569 FND_MESSAGE.SET_NAME('INV','INV_PROGRAM_ERROR');
570 FND_MESSAGE.SET_TOKEN('PROG_NAME','inv_calculate_exp_date.get_lot_expiration_date');
571 fnd_msg_pub.ADD;
572 RAISE fnd_api.g_exc_unexpected_error;
573 END IF;
574 IF g_debug = 1 THEN
575 print_debug('l_lot_expiration_date '||l_lot_expiration_date, 9);
576 END IF;
577 x_expiration_date := l_lot_expiration_date;
578 ELSE
579 x_expiration_date := x_origination_date + l_get_dft_attr_rec.shelf_life_days;
580 END IF;
581 -- nsinghi bug 5209065 END
582 END IF;
583
584 /* Retest Date */
585 x_retest_date := x_origination_date + l_get_dft_attr_rec.retest_interval;
586
587 /* Hold Date */
588 x_hold_date := x_origination_date + l_get_dft_attr_rec.hold_days;
589
590 /* Maturity Date */
591 x_maturity_date := x_origination_date + l_get_dft_attr_rec.maturity_days;
592
593 /* Shelf Life Code */
594 IF NVL (l_get_dft_attr_rec.shelf_life_code, -1) <> 1 THEN -- No shelf life control
595
596 /* Expiration Action Date */
597 x_exp_action_date := x_expiration_date + l_get_dft_attr_rec.expiration_action_interval ;
598
599 /* Expiration Action Code */
600 x_exp_action_code := l_get_dft_attr_rec.expiration_action_code ;
601
602 END IF; /* Shelf Life Code */
603
604 -- nsinghi bug#5209065 rework START.
605 l_new_lot := FALSE;
606 OPEN c_get_lot_attr(p_inventory_item_id,p_organization_id,p_lot_number);
607 FETCH c_get_lot_attr INTO l_get_lot_attr_rec;
608 IF c_get_lot_attr%NOTFOUND THEN
609 l_new_lot := TRUE;
610 END IF;
611 CLOSE c_get_lot_attr;
612 IF (NOT l_new_lot) THEN
613 IF l_get_lot_attr_rec.grade_code IS NOT NULL THEN
614 x_grade_code := l_get_lot_attr_rec.grade_code;
615 END IF;
616
617 IF l_get_lot_attr_rec.expiration_date IS NOT NULL THEN
618 x_expiration_date := l_get_lot_attr_rec.expiration_date;
619 END IF;
620
621 IF l_get_lot_attr_rec.expiration_action_code IS NOT NULL THEN
622 x_exp_action_code := l_get_lot_attr_rec.expiration_action_code;
623 END IF;
624
625 IF l_get_lot_attr_rec.expiration_action_date IS NOT NULL THEN
626 x_exp_action_date := l_get_lot_attr_rec.expiration_action_date;
627 ELSIF l_get_lot_attr_rec.expiration_date IS NOT NULL
628 AND l_get_lot_attr_rec.expiration_action_date IS NULL
629 AND l_get_dft_attr_rec.shelf_life_code = 2
630 THEN
631 x_exp_action_date := l_get_lot_attr_rec.expiration_date +
632 l_get_dft_attr_rec.expiration_action_interval ;
633 END IF;
634
635 IF l_get_lot_attr_rec.origination_date IS NOT NULL THEN
636 x_origination_date := l_get_lot_attr_rec.origination_date;
637 END IF;
638
639 IF l_get_lot_attr_rec.retest_date IS NOT NULL THEN
640 x_retest_date := l_get_lot_attr_rec.retest_date;
641 END IF;
642
643 IF l_get_lot_attr_rec.maturity_date IS NOT NULL THEN
644 x_maturity_date := l_get_lot_attr_rec.maturity_date;
645 END IF;
646
647 IF l_get_lot_attr_rec.hold_date IS NOT NULL THEN
648 x_hold_date := l_get_lot_attr_rec.hold_date;
649 END IF;
650 END IF;
651 -- nsinghi bug#5209065 rework END.
652
653 OPEN x_lot_att FOR
654 SELECT
655 x_grade_code,
656 x_origination_date,
657 x_exp_action_date,
658 x_exp_action_code,
659 x_hold_date,
660 x_maturity_date,
661 x_retest_date,
662 x_expiration_date
663 FROM dual ;
664
665
666
667 EXCEPTION
668 WHEN NO_DATA_FOUND THEN
669 print_debug('In Set_Msi_Default_Attr, No data found ' || SQLERRM, 9);
670 WHEN fnd_api.g_exc_error THEN
671 print_debug('In Set_Msi_Default_Attr, g_exc_error ' || SQLERRM, 9);
672 WHEN fnd_api.g_exc_unexpected_error THEN
673 print_debug('In Set_Msi_Default_Attr, g_exc_unexpected_error ' || SQLERRM, 9);
674 WHEN OTHERS THEN
675 print_debug('In Set_Msi_Default_Attr, Others ' || SQLERRM, 9);
676
677 END Set_Msi_Default_Attr ;
678
679 /*Added p_subinventory_code , p_locator_id in below procedure for Onhand status support
680 Also passed p_subinventory_code,p_locator_id in
681 inv_material_status_grp.is_status_applicable */
682 PROCEDURE get_parent_lov(x_lot_num_lov OUT NOCOPY t_genref, p_wms_installed IN VARCHAR2, p_organization_id IN NUMBER, p_txn_type_id IN NUMBER, p_inventory_item_id IN VARCHAR2, p_lot_number IN VARCHAR2, p_project_id IN NUMBER, p_task_id IN NUMBER ,
683 p_subinventory_code IN VARCHAR2,p_locator_id IN NUMBER ) IS
684 l_inventory_item_id VARCHAR2(100);
685 BEGIN
686 IF p_inventory_item_id IS NULL THEN
687 l_inventory_item_id := '%';
688 ELSE
689 l_inventory_item_id := p_inventory_item_id;
690 END IF;
691
692 IF p_txn_type_id = inv_globals.g_type_inv_lot_split -- Lot Split (82)
693 THEN
694 OPEN x_lot_num_lov FOR
695 SELECT mln.lot_number lot_number
696 , mln.inventory_item_id
697 , msik.concatenated_segments concatenated_segments
698 , msik.description
699 , TO_CHAR(mln.expiration_date, 'YYYY-MM-DD')
700 , mms.status_code status_code
701 , mms.status_id
702 FROM mtl_lot_numbers mln, mtl_system_items_kfv msik, mtl_material_statuses_vl mms
703 WHERE mms.status_id = msik.default_lot_status_id -- Bug#2267947
704 AND msik.default_lot_status_id IS NOT NULL -- Bug#2267947
705 AND mln.organization_id = p_organization_id
706 AND mln.organization_id = msik.organization_id
707 AND mln.inventory_item_id = msik.inventory_item_id
708 AND mln.inventory_item_id LIKE l_inventory_item_id
709 AND msik.lot_split_enabled = 'Y'
710 AND mln.lot_number = p_lot_number
711 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_txn_type_id, msik.lot_status_enabled, msik.serial_status_enabled,
712 p_organization_id, msik.inventory_item_id, p_subinventory_code , p_locator_id , mln.lot_number, NULL, 'O') = 'Y'
713 UNION
714 SELECT mln.lot_number lot_number
715 , mln.inventory_item_id
716 , msik.concatenated_segments concatenated_segments
717 , msik.description
718 , TO_CHAR(mln.expiration_date, 'YYYY-MM-DD')
719 , NULL status_code
720 , msik.default_lot_status_id -- Bug#2267947
721 FROM mtl_lot_numbers mln, mtl_system_items_kfv msik
722 WHERE msik.default_lot_status_id IS NULL -- Bug#2267947
723 AND mln.organization_id = p_organization_id
724 AND mln.organization_id = msik.organization_id
725 AND mln.inventory_item_id = msik.inventory_item_id
726 AND mln.inventory_item_id LIKE l_inventory_item_id
727 AND msik.lot_split_enabled = 'Y'
728 AND mln.lot_number = p_lot_number
729 UNION
730 SELECT nvl(mln.parent_lot_number,mln.lot_number) lot_number
731 , mln.inventory_item_id
732 , msik.concatenated_segments concatenated_segments
733 , msik.description
734 , TO_CHAR(mln.expiration_date, 'YYYY-MM-DD')
735 , mms.status_code status_code
736 , mms.status_id
737 FROM mtl_lot_numbers mln, mtl_system_items_kfv msik, mtl_material_statuses_vl mms
738 WHERE mms.status_id = msik.default_lot_status_id -- Bug#2267947
739 AND msik.default_lot_status_id IS NOT NULL -- Bug#2267947
740 AND mln.organization_id = p_organization_id
741 AND mln.organization_id = msik.organization_id
742 AND mln.inventory_item_id = msik.inventory_item_id
743 AND mln.inventory_item_id LIKE l_inventory_item_id
744 AND msik.lot_split_enabled = 'Y'
745 AND mln.lot_number = p_lot_number
746 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_txn_type_id, msik.lot_status_enabled, msik.serial_status_enabled,
747 p_organization_id, msik.inventory_item_id, p_subinventory_code , p_locator_id , mln.lot_number, NULL, 'O') = 'Y'
748 UNION
749 SELECT nvl(mln.parent_lot_number,mln.lot_number) lot_number
750 , mln.inventory_item_id
751 , msik.concatenated_segments concatenated_segments
752 , msik.description
753 , TO_CHAR(mln.expiration_date, 'YYYY-MM-DD')
754 , NULL status_code
755 , msik.default_lot_status_id -- Bug#2267947
756 FROM mtl_lot_numbers mln, mtl_system_items_kfv msik
757 WHERE msik.default_lot_status_id IS NULL -- Bug#2267947
758 AND mln.organization_id = p_organization_id
759 AND mln.organization_id = msik.organization_id
760 AND mln.inventory_item_id = msik.inventory_item_id
761 AND mln.inventory_item_id LIKE l_inventory_item_id
762 AND msik.lot_split_enabled = 'Y'
763 AND mln.lot_number = p_lot_number
764 ORDER BY lot_number, concatenated_segments;
765 ELSE
766 IF p_txn_type_id = inv_globals.g_type_inv_lot_merge -- Lot Merge 83
767 THEN
768 IF (p_project_id IS NOT NULL) THEN
769 OPEN x_lot_num_lov FOR
770 SELECT DISTINCT moq.lot_number
771 , moq.inventory_item_id
772 , msik.concatenated_segments concatenated_segments
773 , msik.description
774 , TO_CHAR(mln.expiration_date, 'YYYY-MM-DD')
775 , mms.status_code
776 , mms.status_id
777 FROM mtl_onhand_quantities_detail moq, mtl_lot_numbers mln, mtl_system_items_kfv msik, mtl_material_statuses_vl mms, mtl_item_locations mil
778 WHERE moq.organization_id = p_organization_id
779 AND moq.lot_number IS NOT NULL
780 AND moq.organization_id = mil.organization_id
781 AND moq.organization_id = mln.organization_id
782 AND moq.organization_id = msik.organization_id
783 AND mil.segment19 = p_project_id
784 AND (mil.segment20 = p_task_id
785 OR (mil.segment20 IS NULL
786 AND p_task_id IS NULL
787 )
788 )
789 AND mln.lot_number = moq.lot_number
790 AND mms.status_id = msik.default_lot_status_id -- Bug#2267947
791 AND msik.default_lot_status_id IS NOT NULL -- Bug#2267947
792 AND mln.inventory_item_id = msik.inventory_item_id
793 AND mln.inventory_item_id LIKE l_inventory_item_id
794 AND msik.lot_merge_enabled = 'Y'
795 AND mln.lot_number LIKE (p_lot_number)
796 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_txn_type_id, msik.lot_status_enabled, msik.serial_status_enabled, p_organization_id,
797 msik.inventory_item_id, p_subinventory_code , p_locator_id , mln.lot_number, NULL, 'O') = 'Y'
798 UNION ALL
799 SELECT DISTINCT moq.lot_number
800 , moq.inventory_item_id
801 , msik.concatenated_segments concatenated_segments
802 , msik.description
803 , TO_CHAR(mln.expiration_date, 'YYYY-MM-DD')
804 , NULL status_code
805 , msik.default_lot_status_id -- Bug#2267947
806 FROM mtl_onhand_quantities_detail moq, mtl_lot_numbers mln, mtl_system_items_kfv msik, mtl_material_statuses_vl mms, mtl_item_locations mil
807 WHERE moq.organization_id = p_organization_id
808 AND moq.lot_number IS NOT NULL
809 AND moq.organization_id = mil.organization_id
810 AND moq.organization_id = mln.organization_id
811 AND moq.organization_id = msik.organization_id
812 AND mil.segment19 = p_project_id
813 AND (mil.segment20 = p_task_id
814 OR (mil.segment20 IS NULL
815 AND p_task_id IS NULL
816 )
817 )
818 AND mln.lot_number = moq.lot_number
819 AND msik.default_lot_status_id IS NULL -- Bug#2267947
820 AND mln.inventory_item_id = msik.inventory_item_id
821 AND mln.inventory_item_id LIKE l_inventory_item_id
822 AND msik.lot_merge_enabled = 'Y'
823 AND mln.lot_number LIKE (p_lot_number)
824 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_txn_type_id, msik.lot_status_enabled, msik.serial_status_enabled, p_organization_id,
825 msik.inventory_item_id, p_subinventory_code , p_locator_id , mln.lot_number, NULL, 'O') = 'Y'
826 ORDER BY 1, concatenated_segments;
827 ELSE
828 OPEN x_lot_num_lov FOR
829 SELECT mln.lot_number lot_number
830 , mln.inventory_item_id
831 , msik.concatenated_segments concatenated_segments
832 , msik.description
833 , TO_CHAR(mln.expiration_date, 'YYYY-MM-DD')
834 , mms.status_code
835 , mms.status_id
836 FROM mtl_lot_numbers mln, mtl_system_items_kfv msik, mtl_material_statuses_vl mms
837 WHERE mms.status_id = msik.default_lot_status_id -- Bug#2267947
838 AND msik.default_lot_status_id IS NOT NULL -- Bug#2267947
839 AND mln.organization_id = p_organization_id
840 AND mln.organization_id = msik.organization_id
841 AND mln.inventory_item_id = msik.inventory_item_id
842 AND mln.inventory_item_id LIKE l_inventory_item_id
843 AND msik.lot_merge_enabled = 'Y'
844 AND mln.lot_number LIKE (p_lot_number)
845 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_txn_type_id, msik.lot_status_enabled, msik.serial_status_enabled,
846 p_organization_id, msik.inventory_item_id, p_subinventory_code , p_locator_id , mln.lot_number, NULL, 'O') = 'Y'
847 UNION ALL
848 SELECT mln.lot_number lot_number
849 , mln.inventory_item_id
850 , msik.concatenated_segments concatenated_segments
851 , msik.description
852 , TO_CHAR(mln.expiration_date, 'YYYY-MM-DD')
853 , NULL status_code
854 , msik.default_lot_status_id -- Bug#2267947
855 FROM mtl_lot_numbers mln, mtl_system_items_kfv msik
856 WHERE msik.default_lot_status_id IS NULL -- Bug#2267947
857 AND mln.organization_id = p_organization_id
858 AND mln.organization_id = msik.organization_id
859 AND mln.inventory_item_id = msik.inventory_item_id
860 AND mln.inventory_item_id LIKE l_inventory_item_id
861 AND msik.lot_merge_enabled = 'Y'
862 AND mln.lot_number LIKE (p_lot_number)
863 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_txn_type_id, msik.lot_status_enabled, msik.serial_status_enabled, p_organization_id, msik.inventory_item_id,
864 p_subinventory_code , p_locator_id , mln.lot_number, NULL, 'O') = 'Y'
865 ORDER BY lot_number, concatenated_segments;
866 END IF;
867 ELSE -- for Lot Translate
868 OPEN x_lot_num_lov FOR
869 SELECT mln.lot_number lot_number
870 , mln.inventory_item_id
871 , msik.concatenated_segments concatenated_segments
872 , msik.description
873 , TO_CHAR(mln.expiration_date, 'YYYY-MM-DD')
874 , mms.status_code
875 , mms.status_id
876 FROM mtl_lot_numbers mln, mtl_system_items_kfv msik, mtl_material_statuses_vl mms
877 WHERE mms.status_id = msik.default_lot_status_id -- Bug#2267947
878 AND msik.default_lot_status_id IS NOT NULL -- Bug#2267947
879 AND mln.organization_id = p_organization_id
880 AND mln.organization_id = msik.organization_id
881 AND mln.inventory_item_id = msik.inventory_item_id
882 AND msik.lot_control_code = 2
883 AND mln.inventory_item_id LIKE l_inventory_item_id
884 AND mln.lot_number LIKE (p_lot_number)
885 AND inv_material_status_grp.is_status_applicable(p_wms_installed, NULL, p_txn_type_id, msik.lot_status_enabled, msik.serial_status_enabled, p_organization_id, msik.inventory_item_id,
886 p_subinventory_code , p_locator_id , mln.lot_number, NULL, 'O') = 'Y'
887 UNION ALL
888 SELECT mln.lot_number LN
889 , mln.inventory_item_id
890 , msik.concatenated_segments cs
891 , msik.description
892 , TO_CHAR(mln.expiration_date, 'YYYY-MM-DD')
893 , NULL status_code
894 , msik.default_lot_status_id -- Bug#2267947
895 FROM mtl_lot_numbers mln, mtl_system_items_kfv msik
896 WHERE msik.default_lot_status_id IS NULL -- Bug#2267947
897 AND mln.organization_id = p_organization_id
898 AND mln.organization_id = msik.organization_id
899 AND mln.inventory_item_id = msik.inventory_item_id
900 AND msik.lot_control_code = 2
901 AND mln.inventory_item_id LIKE l_inventory_item_id
902 AND mln.lot_number LIKE (p_lot_number)
903 ORDER BY lot_number, concatenated_segments;
904 END IF;
905 END IF;
906 END get_parent_lov;
907 PROCEDURE validate_child_lot (
908 p_org_id IN NUMBER
909 , p_inventory_item_id IN NUMBER
910 , p_parent_lot_number IN VARCHAR2
911 , p_lot_number IN VARCHAR2
912 , x_return_status OUT NOCOPY VARCHAR2
913 , x_msg_count OUT NOCOPY NUMBER
914 , x_msg_data OUT NOCOPY VARCHAR2)
915
916 IS
917 l_api_version NUMBER ;
918 l_init_msg_list VARCHAR2(50) ;
919 l_commit VARCHAR2 (50) ;
920 l_return_status VARCHAR2 (50) ;
921 l_msg_count NUMBER ;
922 l_msg_data VARCHAR2(3000) ;
923 BEGIN
924
925 x_return_status := fnd_api.G_RET_STS_SUCCESS;
926 l_api_version := 1.0;
927 l_init_msg_list := fnd_api.g_false;
928 l_commit := fnd_api.g_false;
929
930 INV_LOT_API_PUB.validate_child_lot (
931 x_return_status => l_return_status
932 , x_msg_count => l_msg_count
933 , x_msg_data => l_msg_data
934 , p_api_version => l_api_version
935 , p_init_msg_list => l_init_msg_list
936 , p_commit => l_commit
937 , p_organization_id => p_org_id
938 , p_inventory_item_id => p_inventory_item_id
939 , p_parent_lot_number => p_parent_lot_number
940 , p_child_lot_number => p_lot_number
941 ) ;
942
943 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
944 FND_MESSAGE.SET_NAME('INV','INV_PROGRAM_ERROR');
945 FND_MESSAGE.SET_TOKEN('PGM_NAME','INV_LOT_API_PUB.VALIDATE_CHILD_LOT');
946 fnd_msg_pub.ADD;
947 RAISE fnd_api.g_exc_unexpected_error;
948 END IF;
949
950 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
951 RAISE fnd_api.g_exc_error;
952 END IF;
953
954
955
956 EXCEPTION
957 WHEN NO_DATA_FOUND THEN
958 x_return_status := FND_API.G_RET_STS_ERROR;
959
960 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
961 p_count => x_msg_count,
962 p_data => x_msg_data);
963 if( x_msg_count > 1 ) then
964 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE);
965 end if;
966
967 WHEN FND_API.G_EXC_ERROR THEN
968 x_return_status := FND_API.G_RET_STS_ERROR;
969
970 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
971 p_count => x_msg_count,
972 p_data => x_msg_data);
973 if( x_msg_count > 1 ) then
974 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE);
975 end if;
976
977 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
978 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
979
980 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
981 p_count => x_msg_count,
982 p_data => x_msg_data);
983 if( x_msg_count > 1 ) then
984 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE);
985 end if;
986
987
988 WHEN OTHERS THEN
989 x_return_status := fnd_api.g_ret_sts_error;
990
991 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
992 p_count => x_msg_count,
993 p_data => x_msg_data);
994 if( x_msg_count > 1 ) then
995 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE);
996 end if;
997
998 END validate_child_lot;
999
1000 PROCEDURE Save_Conversions ( p_org_id IN NUMBER,
1001 p_frm_uom IN VARCHAR2,
1002 p_to_uom IN VARCHAR2,
1003 p_saveConv OUT NOCOPY VARCHAR2)
1004 Is
1005
1006 l_org_id NUMBER ;
1007 l_create_lot_uom_conv NUMBER;
1008 l_from_uom_code VARCHAR2(10);
1009 l_to_uom_code VARCHAR2(10);
1010 l_from_unit_of_measure MTL_UNITS_OF_MEASURE.unit_of_measure_tl%TYPE;
1011 l_from_uom_class MTL_UNITS_OF_MEASURE.uom_class%TYPE;
1012 l_to_unit_of_measure MTL_UNITS_OF_MEASURE.unit_of_measure_tl%TYPE;
1013 l_to_uom_class MTL_UNITS_OF_MEASURE.uom_class%TYPE;
1014 l_display_conversions VARCHAR2(10) ;
1015
1016 Begin
1017
1018 l_org_id := p_org_id;
1019 BEGIN
1020 SELECT create_lot_uom_conversion
1021 INTO l_create_lot_uom_conv
1022 FROM mtl_parameters
1023 WHERE organization_id = l_org_id;
1024 EXCEPTION
1025 WHEN OTHERS THEN
1026 l_create_lot_uom_conv := 1;
1027 END;
1028
1029 l_display_conversions := '2';
1030 IF NVL(l_create_lot_uom_conv,1 ) = 2 THEN
1031 l_display_conversions := '2';
1032 END IF;
1033
1034 IF NVL(l_create_lot_uom_conv,1 ) IN (1,3) THEN
1035
1036 l_from_uom_code := p_frm_uom; -- Transaction UOM
1037 l_to_uom_code := p_to_uom; -- Secondary UOM;
1038
1039 BEGIN
1040 SELECT unit_of_measure_tl, uom_class
1041 INTO l_from_unit_of_measure, l_from_uom_class
1042 FROM MTL_UNITS_OF_MEASURE
1043 WHERE UOM_CODE = l_from_uom_Code;
1044 EXCEPTION
1045 WHEN OTHERS THEN
1046 l_from_unit_of_measure := NULL;
1047 l_from_uom_class := NULL;
1048 END;
1049
1050 BEGIN
1051 SELECT unit_of_measure_tl, uom_class
1052 INTO l_to_unit_of_measure, l_to_uom_class
1053 FROM MTL_UNITS_OF_MEASURE
1054 WHERE UOM_CODE = l_to_uom_Code;
1055 EXCEPTION
1056 WHEN OTHERS THEN
1057 l_to_unit_of_measure := NULL;
1058 l_to_uom_class := NULL;
1059 END;
1060
1061 IF l_from_uom_class <> l_to_uom_class THEN
1062 IF NVL(l_create_lot_uom_conv,1 ) = 1 THEN
1063 l_display_conversions := '1';
1064 ELSIF NVL(l_create_lot_uom_conv,1 ) = 3 THEN
1065 l_display_conversions := '3';
1066 END IF;
1067 END IF;
1068 END IF;
1069 p_saveConv := l_display_conversions;
1070
1071 End;
1072
1073 PROCEDURE Save_Lot_UOM_Conv(
1074 p_inventory_item_id MTL_LOT_NUMBERS.inventory_item_id%TYPE,
1075 p_org_id NUMBER,
1076 P_TRANSACTION_QUANTITY IN NUMBER,
1077 p_primary_quantity IN NUMBER ,
1078 P_TRANSACTION_UOM IN VARCHAR2 ,
1079 p_primary_uom IN VARCHAR2 ,
1080 p_lot_number MTL_LOT_NUMBERS.lot_number%TYPE,
1081 p_expiration_date MTL_LOT_NUMBERS.expiration_date%TYPE,
1082 x_return_status OUT NOCOPY VARCHAR2,
1083 x_msg_data OUT NOCOPY VARCHAR2,
1084 x_msg_count OUT NOCOPY NUMBER,
1085 P_SUPPLIER_LOT_NUMBER MTL_LOT_NUMBERS.SUPPLIER_LOT_NUMBER%TYPE,
1086 p_grade_code MTL_LOT_NUMBERS.grade_code%TYPE,
1087 p_ORIGINATION_DATE MTL_LOT_NUMBERS.ORIGINATION_DATE%TYPE,
1088 P_STATUS_ID MTL_LOT_NUMBERS.STATUS_ID%TYPE,
1089 p_RETEST_DATE MTL_LOT_NUMBERS.RETEST_DATE%TYPE,
1090 P_MATURITY_DATE MTL_LOT_NUMBERS.MATURITY_DATE%TYPE,
1091 P_LOT_ATTRIBUTE_CATEGORY MTL_LOT_NUMBERS.LOT_ATTRIBUTE_CATEGORY%TYPE,
1092 P_C_ATTRIBUTE1 MTL_LOT_NUMBERS.C_ATTRIBUTE1%TYPE,
1093 P_C_ATTRIBUTE2 MTL_LOT_NUMBERS.C_ATTRIBUTE2%TYPE,
1094 P_C_ATTRIBUTE3 MTL_LOT_NUMBERS.C_ATTRIBUTE3%TYPE,
1095 P_C_ATTRIBUTE4 MTL_LOT_NUMBERS.C_ATTRIBUTE4%TYPE,
1096 P_C_ATTRIBUTE5 MTL_LOT_NUMBERS.C_ATTRIBUTE5%TYPE,
1097 P_C_ATTRIBUTE6 MTL_LOT_NUMBERS.C_ATTRIBUTE6%TYPE,
1098 P_C_ATTRIBUTE7 MTL_LOT_NUMBERS.C_ATTRIBUTE7%TYPE,
1099 P_C_ATTRIBUTE8 MTL_LOT_NUMBERS.C_ATTRIBUTE8%TYPE,
1100 P_C_ATTRIBUTE9 MTL_LOT_NUMBERS.C_ATTRIBUTE9%TYPE,
1101 P_C_ATTRIBUTE10 MTL_LOT_NUMBERS.C_ATTRIBUTE10%TYPE,
1102 P_C_ATTRIBUTE11 MTL_LOT_NUMBERS.C_ATTRIBUTE11%TYPE,
1103 P_C_ATTRIBUTE12 MTL_LOT_NUMBERS.C_ATTRIBUTE12%TYPE,
1104 P_C_ATTRIBUTE13 MTL_LOT_NUMBERS.C_ATTRIBUTE13%TYPE,
1105 P_C_ATTRIBUTE14 MTL_LOT_NUMBERS.C_ATTRIBUTE14%TYPE,
1106 P_C_ATTRIBUTE15 MTL_LOT_NUMBERS.C_ATTRIBUTE15%TYPE,
1107 P_C_ATTRIBUTE16 MTL_LOT_NUMBERS.C_ATTRIBUTE16%TYPE,
1108 P_C_ATTRIBUTE17 MTL_LOT_NUMBERS.C_ATTRIBUTE17%TYPE,
1109 P_C_ATTRIBUTE18 MTL_LOT_NUMBERS.C_ATTRIBUTE18%TYPE,
1110 P_C_ATTRIBUTE19 MTL_LOT_NUMBERS.C_ATTRIBUTE19%TYPE,
1111 P_C_ATTRIBUTE20 MTL_LOT_NUMBERS.C_ATTRIBUTE20%TYPE,
1112 P_D_ATTRIBUTE1 MTL_LOT_NUMBERS.D_ATTRIBUTE1%TYPE,
1113 P_D_ATTRIBUTE2 MTL_LOT_NUMBERS.D_ATTRIBUTE2%TYPE,
1114 P_D_ATTRIBUTE3 MTL_LOT_NUMBERS.D_ATTRIBUTE3%TYPE,
1115 P_D_ATTRIBUTE4 MTL_LOT_NUMBERS.D_ATTRIBUTE4%TYPE,
1116 P_D_ATTRIBUTE5 MTL_LOT_NUMBERS.D_ATTRIBUTE5%TYPE,
1117 P_D_ATTRIBUTE6 MTL_LOT_NUMBERS.D_ATTRIBUTE6%TYPE,
1118 P_D_ATTRIBUTE7 MTL_LOT_NUMBERS.D_ATTRIBUTE7%TYPE,
1119 P_D_ATTRIBUTE8 MTL_LOT_NUMBERS.D_ATTRIBUTE8%TYPE,
1120 P_D_ATTRIBUTE9 MTL_LOT_NUMBERS.D_ATTRIBUTE9%TYPE,
1121 P_D_ATTRIBUTE10 MTL_LOT_NUMBERS.D_ATTRIBUTE10%TYPE,
1122 P_N_ATTRIBUTE1 MTL_LOT_NUMBERS.N_ATTRIBUTE1%TYPE,
1123 P_N_ATTRIBUTE2 MTL_LOT_NUMBERS.N_ATTRIBUTE2%TYPE,
1124 P_N_ATTRIBUTE3 MTL_LOT_NUMBERS.N_ATTRIBUTE3%TYPE,
1125 P_N_ATTRIBUTE4 MTL_LOT_NUMBERS.N_ATTRIBUTE4%TYPE,
1126 P_N_ATTRIBUTE5 MTL_LOT_NUMBERS.N_ATTRIBUTE5%TYPE,
1127 P_N_ATTRIBUTE6 MTL_LOT_NUMBERS.N_ATTRIBUTE6%TYPE,
1128 P_N_ATTRIBUTE7 MTL_LOT_NUMBERS.N_ATTRIBUTE7%TYPE,
1129 P_N_ATTRIBUTE8 MTL_LOT_NUMBERS.N_ATTRIBUTE8%TYPE,
1130 P_N_ATTRIBUTE9 MTL_LOT_NUMBERS.N_ATTRIBUTE9%TYPE,
1131 P_N_ATTRIBUTE10 MTL_LOT_NUMBERS.N_ATTRIBUTE10%TYPE,
1132 P_SECONDARY_QUANTITY IN NUMBER,
1133 P_SECONDARY_UOM_CODE IN VARCHAR2 ,
1134 p_parent_lot_number MTL_LOT_NUMBERS.parent_lot_number%TYPE,
1135 P_ORIGINATION_TYPE MTL_LOT_NUMBERS.ORIGINATION_TYPE%TYPE,
1136 P_EXPIRATION_ACTION_DATE MTL_LOT_NUMBERS.EXPIRATION_ACTION_DATE%TYPE,
1137 P_EXPIRATION_ACTION_CODE MTL_LOT_NUMBERS.EXPIRATION_ACTION_CODE%TYPE,
1138 P_HOLD_DATE MTL_LOT_NUMBERS.HOLD_DATE%TYPE,
1139 P_REASON_ID IN VARCHAR2 ,
1140 p_response IN VARCHAR2 ,
1141 P_ATTRIBUTE_CATEGORY MTL_LOT_NUMBERS.ATTRIBUTE_CATEGORY%TYPE,
1142 P_ATTRIBUTE1 MTL_LOT_NUMBERS.ATTRIBUTE1%TYPE,
1143 P_ATTRIBUTE2 MTL_LOT_NUMBERS.ATTRIBUTE2%TYPE,
1144 P_ATTRIBUTE3 MTL_LOT_NUMBERS.ATTRIBUTE3%TYPE,
1145 P_ATTRIBUTE4 MTL_LOT_NUMBERS.ATTRIBUTE4%TYPE,
1146 P_ATTRIBUTE5 MTL_LOT_NUMBERS.ATTRIBUTE5%TYPE,
1147 P_ATTRIBUTE6 MTL_LOT_NUMBERS.ATTRIBUTE6%TYPE,
1148 P_ATTRIBUTE7 MTL_LOT_NUMBERS.ATTRIBUTE7%TYPE,
1149 P_ATTRIBUTE8 MTL_LOT_NUMBERS.ATTRIBUTE8%TYPE,
1150 P_ATTRIBUTE9 MTL_LOT_NUMBERS.ATTRIBUTE9%TYPE,
1151 P_ATTRIBUTE10 MTL_LOT_NUMBERS.ATTRIBUTE10%TYPE,
1152 P_ATTRIBUTE11 MTL_LOT_NUMBERS.ATTRIBUTE11%TYPE,
1153 P_ATTRIBUTE12 MTL_LOT_NUMBERS.ATTRIBUTE12%TYPE,
1154 P_ATTRIBUTE13 MTL_LOT_NUMBERS.ATTRIBUTE13%TYPE,
1155 P_ATTRIBUTE14 MTL_LOT_NUMBERS.ATTRIBUTE14%TYPE,
1156 P_ATTRIBUTE15 MTL_LOT_NUMBERS.ATTRIBUTE15%TYPE,
1157 P_ITEM_DUAL_UOM_CONTROL IN VARCHAR2 , -- hold item's Tracking indicator
1158 P_copy_pnt_lot_att_flag IN VARCHAR2 ,
1159 p_secondary_default_ind IN VARCHAR2 ,
1160 p_disable_flag IN MTL_LOT_NUMBERS.DISABLE_FLAG%TYPE DEFAULT NULL, -- 4239238 Start
1161 p_territory_code IN MTL_LOT_NUMBERS.TERRITORY_CODE%TYPE DEFAULT NULL,
1162 p_date_code IN MTL_LOT_NUMBERS.DATE_CODE%TYPE DEFAULT NULL,
1163 p_change_date IN MTL_LOT_NUMBERS.CHANGE_DATE%TYPE DEFAULT NULL,
1164 p_age IN MTL_LOT_NUMBERS.AGE%TYPE DEFAULT NULL,
1165 p_item_size IN MTL_LOT_NUMBERS.ITEM_SIZE%TYPE DEFAULT NULL,
1166 p_color IN MTL_LOT_NUMBERS.COLOR%TYPE DEFAULT NULL,
1167 p_volume IN MTL_LOT_NUMBERS.VOLUME%TYPE DEFAULT NULL,
1168 p_volume_uom IN MTL_LOT_NUMBERS.VOLUME_UOM%TYPE DEFAULT NULL,
1169 p_place_of_origin IN MTL_LOT_NUMBERS.PLACE_OF_ORIGIN%TYPE DEFAULT NULL,
1170 p_best_by_date IN MTL_LOT_NUMBERS.BEST_BY_DATE%TYPE DEFAULT NULL,
1171 p_length IN MTL_LOT_NUMBERS.LENGTH%TYPE DEFAULT NULL,
1172 p_length_uom IN MTL_LOT_NUMBERS.LENGTH_UOM%TYPE DEFAULT NULL,
1173 p_recycled_content IN MTL_LOT_NUMBERS.RECYCLED_CONTENT%TYPE DEFAULT NULL,
1174 p_thickness IN MTL_LOT_NUMBERS.THICKNESS%TYPE DEFAULT NULL,
1175 p_thickness_uom IN MTL_LOT_NUMBERS.THICKNESS_UOM%TYPE DEFAULT NULL,
1176 p_width IN MTL_LOT_NUMBERS.WIDTH%TYPE DEFAULT NULL,
1177 p_width_uom IN MTL_LOT_NUMBERS.WIDTH_UOM%TYPE DEFAULT NULL,
1178 p_curl_wrinkle_fold IN MTL_LOT_NUMBERS.CURL_WRINKLE_FOLD%TYPE DEFAULT NULL,
1179 p_vendor_name IN MTL_LOT_NUMBERS.VENDOR_NAME%TYPE DEFAULT NULL, -- 4239238 End
1180 p_source_lot IN VARCHAR2 DEFAULT NULL, --Bug#5349912
1181 p_copy_other_conversions IN VARCHAR2 DEFAULT 'F', --Bug#5349912
1182 p_vendor_id IN MTL_LOT_NUMBERS.VENDOR_ID%TYPE DEFAULT NULL, --Bug 9762204
1183 p_caculate_flag IN VARCHAR2 DEFAULT 'T' --bug 16604687
1184 )
1185 IS
1186 l_return_status VARCHAR2(1) ;
1187 l_msg_data VARCHAR2(3000) ;
1188 l_msg_count NUMBER ;
1189 x_lot_rec MTL_LOT_NUMBERS%ROWTYPE; -- for lot api
1190 l_in_lot_rec MTL_LOT_NUMBERS%ROWTYPE; -- for lot api
1191 l_lot_uom_conv_rec mtl_lot_uom_class_conversions%ROWTYPE; -- for uom conv
1192 l_qty_update_tbl MTL_LOT_UOM_CONV_PUB.quantity_update_rec_type; -- for uom conv
1193 l_api_version NUMBER;
1194 l_init_msg_list VARCHAR2(100);
1195 l_commit VARCHAR2(100);
1196 l_validation_level NUMBER;
1197 l_origin_txn_id NUMBER;
1198 l_source NUMBER;
1199 l_create_lot_uom_conv NUMBER;
1200 l_org_id NUMBER;
1201 l_from_uom_code MTL_UNITS_OF_MEASURE.UOM_CODE%TYPE;
1202 l_to_uom_code MTL_UNITS_OF_MEASURE.UOM_CODE%TYPE;
1203 l_from_unit_of_measure MTL_UNITS_OF_MEASURE.UNIT_OF_MEASURE_TL%TYPE;
1204 l_to_unit_of_measure MTL_UNITS_OF_MEASURE.UNIT_OF_MEASURE_TL%TYPE;
1205 l_from_uom_class MTL_UNITS_OF_MEASURE.UOM_CLASS%TYPE;
1206 l_to_uom_class MTL_UNITS_OF_MEASURE.UOM_CLASS%TYPE;
1207 l_conversion_rate MTL_LOT_UOM_CLASS_CONVERSIONS.CONVERSION_RATE%TYPE;
1208 x_conversion_rate MTL_LOT_UOM_CLASS_CONVERSIONS.CONVERSION_RATE%TYPE;
1209 l_go BOOLEAN;
1210 l_response NUMBER;
1211 l_sequence NUMBER;
1212 l_action_type VARCHAR2(1);
1213 l_lot_number mtl_transaction_lots_temp.lot_number%TYPE := p_lot_number;
1214 l_check_existing_parent_lot BOOLEAN;
1215 l_row_id ROWID;
1216 l_exists VARCHAR2(10);
1217 L_LOT_UOM_CONVERSION VARCHAR2(10);
1218 l_sec_qty Number := 0;
1219 l_primary_uom MTL_UNITS_OF_MEASURE.UOM_CODE%TYPE;
1220 l_primary_quantity NUMBER ;
1221 l_ITEM_DUAL_UOM_CONTROL mtl_system_items.tracking_quantity_ind%TYPE ;
1222 l_copy_pnt_lot_att_flag mtl_system_items.copy_lot_attribute_flag%TYPE ;
1223 l_secondary_default_ind mtl_system_items.secondary_default_ind%TYPE ;
1224 l_secondary_uom_code mtl_system_items.secondary_uom_code%TYPE;
1225 /* Cursor definition to get Item attributes*/
1226 cursor c_get_item_attr
1227 IS
1228 SELECT primary_uom_code
1229 , secondary_uom_code
1230 , secondary_default_ind
1231 , copy_lot_attribute_flag
1232 , tracking_quantity_ind
1233 FROM mtl_system_items
1234 WHERE organization_id = p_org_id
1235 AND inventory_item_id = p_inventory_item_id ;
1236
1237 l_get_item_attr c_get_item_attr%ROWTYPE ;
1238
1239 /* Cursor definition to check if Lot UOM Conversion is needed */
1240 CURSOR c_lot_uom_conv IS
1241 SELECT copy_lot_attribute_flag,
1242 lot_number_generation
1243 FROM mtl_parameters
1244 WHERE organization_id = p_org_id;
1245
1246 l_lot_uom_conv c_lot_uom_conv%ROWTYPE ;
1247
1248 /* Cursor to check if a lot already exists*/
1249 CURSOR c_lot_exists IS
1250 SELECT 1
1251 FROM mtl_lot_numbers
1252 WHERE organization_id = p_org_id
1253 AND inventory_item_id = p_inventory_item_id
1254 AND lot_number = p_lot_number ;
1255
1256 l_lot_count NUMBER := 0;
1257
1258 l_caculate_flag VARCHAR2(1) := p_caculate_flag; --bug 16604687
1259
1260 BEGIN
1261 /* Step 1 ...preparing to insert lot in MLN by calling CREATE_INV_LOT
1262 * This will also take care of copying Parent's UOM Conv record for child lot
1263 */
1264 l_primary_uom := NULL; --p_primary_uom ;
1265 l_primary_quantity := NULL; --p_primary_quantity ;
1266 l_ITEM_DUAL_UOM_CONTROL := NULL; --p_item_dual_uom_control ;
1267 l_copy_pnt_lot_att_flag := NULL; --p_copy_pnt_lot_att_flag ;
1268 l_secondary_default_ind := NULL; --p_secondary_default_ind ;
1269 l_secondary_uom_code := NULL;-- p_secondary_uom_code;
1270 -- Spr_Debug('1');
1271 OPEN c_get_item_attr;
1272 FETCH c_get_item_attr INTO l_get_item_attr;
1273 CLOSE c_get_item_attr;
1274
1275 OPEN c_lot_exists;
1276 FETCH c_lot_exists INTO l_lot_count;
1277 CLOSE c_lot_exists;
1278
1279
1280 l_return_status := NULL;
1281 l_msg_data := NULL;
1282 l_msg_count := NULL;
1283 l_source := NULL ;
1284 l_api_version := 1.0;
1285 l_init_msg_list := 'T';
1286 l_commit := 'F';
1287 l_validation_level := 100;
1288 l_in_lot_rec.organization_id := p_org_id ;
1289 l_in_lot_rec.inventory_item_id := p_inventory_item_id ;
1290 l_in_lot_rec.expiration_date := p_expiration_date;
1291 l_in_lot_rec.grade_code := p_grade_code ;
1292 l_in_lot_rec.lot_number := p_lot_number ;
1293 l_in_lot_rec.parent_lot_number := p_parent_lot_number;
1294 l_in_lot_rec.origination_date := p_origination_date;
1295 l_in_lot_rec.retest_date := p_retest_date ;
1296 l_in_lot_rec.maturity_date := p_maturity_date;
1297 l_in_lot_rec.attribute_category := p_attribute_category;
1298 l_in_lot_rec.origination_type := p_origination_type;
1299 l_in_lot_rec.hold_date := p_hold_date;
1300 l_in_lot_rec.expiration_action_code := p_expiration_action_code;
1301 l_in_lot_rec.expiration_action_date := p_expiration_action_date;
1302 l_in_lot_rec.status_id := p_status_id;
1303 l_in_lot_rec.supplier_lot_number := p_supplier_lot_number;
1304 l_in_lot_rec.LOT_ATTRIBUTE_CATEGORY := p_lot_attribute_category;
1305 l_in_lot_rec.ATTRIBUTE1 := P_ATTRIBUTE1;
1306 l_in_lot_rec.ATTRIBUTE2 := P_ATTRIBUTE2;
1307 l_in_lot_rec.ATTRIBUTE3 := P_ATTRIBUTE3;
1308 l_in_lot_rec.ATTRIBUTE4 := P_ATTRIBUTE4;
1309 l_in_lot_rec.ATTRIBUTE5 := P_ATTRIBUTE5;
1310 l_in_lot_rec.ATTRIBUTE6 := P_ATTRIBUTE6;
1311 l_in_lot_rec.ATTRIBUTE7 := P_ATTRIBUTE7;
1312 l_in_lot_rec.ATTRIBUTE8 := P_ATTRIBUTE8;
1313 l_in_lot_rec.ATTRIBUTE9 := P_ATTRIBUTE9;
1314 l_in_lot_rec.ATTRIBUTE10 := P_ATTRIBUTE10;
1315 l_in_lot_rec.ATTRIBUTE11 := P_ATTRIBUTE11;
1316 l_in_lot_rec.ATTRIBUTE12 := P_ATTRIBUTE12;
1317 l_in_lot_rec.ATTRIBUTE13 := P_ATTRIBUTE13;
1318 l_in_lot_rec.ATTRIBUTE14 := P_ATTRIBUTE14;
1319 l_in_lot_rec.ATTRIBUTE15 := P_ATTRIBUTE15;
1320 l_in_lot_rec.C_ATTRIBUTE1 := P_C_ATTRIBUTE1;
1321 l_in_lot_rec.C_ATTRIBUTE2 := P_C_ATTRIBUTE2;
1322 l_in_lot_rec.C_ATTRIBUTE3 := P_C_ATTRIBUTE3;
1323 l_in_lot_rec.C_ATTRIBUTE4 := P_C_ATTRIBUTE4;
1324 l_in_lot_rec.C_ATTRIBUTE5 := P_C_ATTRIBUTE5;
1325 l_in_lot_rec.C_ATTRIBUTE6 := P_C_ATTRIBUTE6;
1326 l_in_lot_rec.C_ATTRIBUTE7 := P_C_ATTRIBUTE7;
1327 l_in_lot_rec.C_ATTRIBUTE8 := P_C_ATTRIBUTE8;
1328 l_in_lot_rec.C_ATTRIBUTE9 := P_C_ATTRIBUTE9;
1329 l_in_lot_rec.C_ATTRIBUTE10 := P_C_ATTRIBUTE10;
1330 l_in_lot_rec.C_ATTRIBUTE11 := P_C_ATTRIBUTE11;
1331 l_in_lot_rec.C_ATTRIBUTE12 := P_C_ATTRIBUTE12;
1332 l_in_lot_rec.C_ATTRIBUTE13 := P_C_ATTRIBUTE13;
1333 l_in_lot_rec.C_ATTRIBUTE14 := P_C_ATTRIBUTE14;
1334 l_in_lot_rec.C_ATTRIBUTE15 := P_C_ATTRIBUTE15;
1335 l_in_lot_rec.C_ATTRIBUTE16 := P_C_ATTRIBUTE16;
1336 l_in_lot_rec.C_ATTRIBUTE17 := P_C_ATTRIBUTE17;
1337 l_in_lot_rec.C_ATTRIBUTE18 := P_C_ATTRIBUTE18;
1338 l_in_lot_rec.C_ATTRIBUTE19 := P_C_ATTRIBUTE19;
1339 l_in_lot_rec.C_ATTRIBUTE20 := P_C_ATTRIBUTE20;
1340 l_in_lot_rec.D_ATTRIBUTE1 := P_D_ATTRIBUTE1;
1341 l_in_lot_rec.D_ATTRIBUTE2 := P_D_ATTRIBUTE2;
1342 l_in_lot_rec.D_ATTRIBUTE3 := P_D_ATTRIBUTE3;
1343 l_in_lot_rec.D_ATTRIBUTE4 := P_D_ATTRIBUTE4;
1344 l_in_lot_rec.D_ATTRIBUTE5 := P_D_ATTRIBUTE5;
1345 l_in_lot_rec.D_ATTRIBUTE6 := P_D_ATTRIBUTE6;
1346 l_in_lot_rec.D_ATTRIBUTE7 := P_D_ATTRIBUTE7;
1347 l_in_lot_rec.D_ATTRIBUTE8 := P_D_ATTRIBUTE8;
1348 l_in_lot_rec.D_ATTRIBUTE9 := P_D_ATTRIBUTE9;
1349 l_in_lot_rec.D_ATTRIBUTE10 := P_D_ATTRIBUTE10;
1350 l_in_lot_rec.N_ATTRIBUTE1 := P_N_ATTRIBUTE1;
1351 l_in_lot_rec.N_ATTRIBUTE2 := P_N_ATTRIBUTE2;
1352 l_in_lot_rec.N_ATTRIBUTE3 := P_N_ATTRIBUTE3;
1353 l_in_lot_rec.N_ATTRIBUTE4 := P_N_ATTRIBUTE4;
1354 l_in_lot_rec.N_ATTRIBUTE5 := P_N_ATTRIBUTE5;
1355 l_in_lot_rec.N_ATTRIBUTE6 := P_N_ATTRIBUTE6;
1356 l_in_lot_rec.N_ATTRIBUTE7 := P_N_ATTRIBUTE7;
1357 l_in_lot_rec.N_ATTRIBUTE8 := P_N_ATTRIBUTE8;
1358 l_in_lot_rec.N_ATTRIBUTE9 := P_N_ATTRIBUTE9;
1359 l_in_lot_rec.N_ATTRIBUTE10 := P_N_ATTRIBUTE10;
1360 l_in_lot_rec.disable_flag := p_disable_flag ; --- Please Verify if any Page requires It
1361 l_in_lot_rec.date_code := p_date_code;
1362 l_in_lot_rec.change_date := p_change_date;
1363 l_in_lot_rec.age := p_age;
1364 l_in_lot_rec.item_size := p_item_size;
1365 l_in_lot_rec.color := p_color;
1366 l_in_lot_rec.volume := p_volume;
1367 l_in_lot_rec.volume_uom := p_volume_uom;
1368 l_in_lot_rec.place_of_origin := p_place_of_origin;
1369 l_in_lot_rec.best_by_date := p_best_by_date;
1370 l_in_lot_rec.length := p_length;
1371 l_in_lot_rec.length_uom := p_length_uom;
1372 l_in_lot_rec.recycled_content := p_recycled_content;
1373 l_in_lot_rec.thickness := p_thickness;
1374 l_in_lot_rec.thickness_uom := p_thickness_uom;
1375 l_in_lot_rec.width := p_width;
1376 l_in_lot_rec.width_uom := p_width_uom;
1377 l_in_lot_rec.territory_code := p_territory_code;
1378 l_in_lot_rec.vendor_name := p_vendor_name; -- Please Verify if any Page Requires it
1379 l_in_lot_rec.vendor_id := p_vendor_id; -- Bug 9762204
1380
1381 l_row_id := NULL;
1382 -- Spr_Debug('2 '|| l_in_lot_rec.lot_number );
1383 -- Spr_Debug('2 .5 '|| l_lot_count ) ;
1384 IF l_lot_count = 0 THEN
1385 INV_LOT_API_PUB.Create_Inv_lot(
1386 x_return_status => l_return_status
1387 , x_msg_count => l_msg_count
1388 , x_msg_data => l_msg_data
1389 , x_lot_rec => x_lot_rec
1390 , p_lot_rec => l_in_lot_rec
1391 , p_source => l_source
1392 , p_api_version => l_api_version
1393 , p_init_msg_list => l_init_msg_list
1394 , p_commit => l_commit
1395 , p_validation_level => l_validation_level
1396 , p_origin_txn_id => NULL
1397 , x_row_id => l_row_id
1398 , p_caculate_flag => l_caculate_flag --bug 16604687
1399 );
1400 -- Spr_Debug('3: '||l_return_status);
1401
1402 IF l_return_status <> 'S' THEN
1403 -- dbms_output.put_line('ERROR');
1404 FND_MSG_PUB.count_and_get
1405 ( p_count => l_msg_count
1406 , p_data => l_msg_data
1407 );
1408 -- Spr_Debug('3i '||x_msg_data);
1409 END IF;
1410 END IF; -- COUNT check
1411 /*
1412 * Step 2..Checking if lot specific UOM conversion are needed or not
1413 */
1414 -- l_ITEM_DUAL_UOM_CONTROL IN VARCHAR2 is a new parameter, hold item's Tracking indicator
1415 -- P_TRANSACTION_QUANTITY IN NUMBER
1416 -- P_SECONDARY_QUANTITY IN NUMBER
1417 -- checking for lots UOM conversion rate
1418 -- Spr_Debug('4: '||l_ITEM_DUAL_UOM_CONTROL );
1419 -- Spr_Debug('4.2: primary_uom_code UOM '||l_primary_uom );
1420
1421 -- Check if item is dual controlled.
1422 -- if not then return from here, no need to create UOM conversion record.
1423 IF l_get_item_attr.tracking_quantity_ind <> 'PS' THEN
1424
1425 x_return_status := l_return_status ;
1426 x_msg_count := l_msg_count ;
1427 x_msg_data := l_msg_data ;
1428
1429 RETURN ;
1430 END IF ;
1431
1432 IF l_primary_uom IS NULL THEN
1433 l_primary_uom := l_get_item_attr.primary_uom_code ;
1434 END IF;
1435 IF l_secondary_uom_code IS NULL THEN
1436 l_secondary_uom_code := l_get_item_attr.secondary_uom_code;
1437 END IF;
1438 -- Spr_Debug('4.5: primary_uom_code UOM '||l_primary_uom );
1439 IF l_ITEM_DUAL_UOM_CONTROL IS NULL THEN
1440 l_ITEM_DUAL_UOM_CONTROL := l_get_item_attr.tracking_quantity_ind ;
1441 END IF;
1442
1443 IF l_secondary_default_ind IS NULL THEN
1444 l_secondary_default_ind := l_get_item_attr.secondary_default_ind ;
1445 END IF;
1446
1447 IF l_copy_pnt_lot_att_flag IS NULL THEN
1448 l_copy_pnt_lot_att_flag := l_get_item_attr.copy_lot_attribute_flag ;
1449 END IF;
1450 /* Check needed for Lot UOM conversion */
1451 -- Spr_Debug('6: ');
1452 OPEN c_lot_uom_conv ;
1453 FETCH c_lot_uom_conv INTO l_lot_uom_conv ;
1454
1455 IF c_lot_uom_conv%FOUND THEN
1456 -- Possible values for mtl_parameters.lot_number_generation are:
1457 -- 1 At organization level
1458 -- 3 User defined
1459 -- 2 At item level
1460 -- Spr_Debug('7: ');
1461 IF l_lot_uom_conv.lot_number_generation = 1 THEN
1462 l_copy_pnt_lot_att_flag := NVL(l_lot_uom_conv.copy_lot_attribute_flag,'N') ;
1463 END IF ;
1464 END IF;
1465 CLOSE c_lot_uom_conv ;
1466
1467 IF l_primary_quantity IS NULL THEN
1468 l_primary_quantity := inv_convert.inv_um_convert(
1469 item_id => p_inventory_item_id
1470 , ORGANIZATION_ID => P_ORG_ID
1471 , LOT_NUMBER => P_LOT_NUMBER
1472 , PRECISION => 5
1473 , from_quantity => P_TRANSACTION_QUANTITY
1474 , from_unit => P_TRANSACTION_UOM
1475 , to_unit => l_primary_uom
1476 , from_name => NULL
1477 , to_name => NULL
1478 );
1479 END IF;
1480 -- Spr_Debug('7: '||l_primary_quantity);
1481
1482 IF l_ITEM_DUAL_UOM_CONTROL = 'PS' THEN
1483 l_conversion_rate := NVL( NVL(P_TRANSACTION_QUANTITY,1) / NVL(P_SECONDARY_QUANTITY,1) ,1);
1484 IF l_conversion_rate <= 0 THEN
1485 l_conversion_rate := 1;
1486 END IF;
1487 END IF;
1488
1489 IF p_parent_lot_number IS NOT NULL THEN
1490 --- Check if Parent lot Already Exists
1491 --Bug#5349912 changed from p_lot_number to p_parent_lot_number in the following query
1492 BEGIN
1493 SELECT count('1')
1494 INTO l_exists
1495 FROM mtl_lot_numbers
1496 WHERE inventory_item_id = P_inventory_item_id
1497 AND organization_id = p_org_id
1498 AND lot_number = p_parent_lot_number
1499 AND ROWNUM = 1;
1500 EXCEPTION
1501 WHEN no_data_found THEN
1502 l_exists := 0;
1503 END;
1504 IF NVL(l_exists,0) > 0 THEN
1505 l_check_existing_parent_lot := TRUE;
1506 ELSE
1507 l_check_existing_parent_lot := FALSE;
1508 END IF;
1509 ELSE
1510 l_check_existing_parent_lot := FALSE;
1511 END IF;
1512 -- Spr_Debug('5: ');
1513 -- calculate l_primary_quantity if its null
1514 -- obtain l_primary_uom from item if its null
1515 /* IF l_primary_uom IS NULL THEN
1516 l_primary_uom := l_get_item_attr.primary_uom_code ;
1517 END IF;
1518
1519 IF l_ITEM_DUAL_UOM_CONTROL IS NULL THEN
1520 l_ITEM_DUAL_UOM_CONTROL := l_get_item_attr.tracking_quantity_ind ;
1521 END IF;
1522
1523 IF l_secondary_default_ind IS NULL THEN
1524 l_secondary_default_ind := l_get_item_attr.secondary_default_ind ;
1525 END IF;
1526
1527 IF l_copy_pnt_lot_att_flag IS NULL THEN
1528 l_copy_pnt_lot_att_flag := l_get_item_attr.copy_lot_attribute_flag ;
1529 END IF;
1530 Check needed for Lot UOM conversion
1531 -- Spr_Debug('6: ');
1532 OPEN c_lot_uom_conv ;
1533 FETCH c_lot_uom_conv INTO l_lot_uom_conv ;
1534
1535 IF c_lot_uom_conv%FOUND THEN
1536 -- Possible values for mtl_parameters.lot_number_generation are:
1537 -- 1 At organization level
1538 -- 3 User defined
1539 -- 2 At item level
1540 -- Spr_Debug('7: ');
1541 IF l_lot_uom_conv.lot_number_generation = 1 THEN
1542 l_copy_pnt_lot_att_flag := NVL(l_lot_uom_conv.copy_lot_attribute_flag,'N') ;
1543 END IF ;
1544 END IF;
1545 CLOSE c_lot_uom_conv ;
1546
1547 IF l_primary_quantity IS NULL THEN
1548 l_primary_quantity := inv_convert.inv_um_convert(
1549 item_id => p_inventory_item_id
1550 , ORGANIZATION_ID => P_ORG_ID
1551 , LOT_NUMBER => P_LOT_NUMBER
1552 , PRECISION => 5
1553 , from_quantity => P_TRANSACTION_QUANTITY
1554 , from_unit => P_TRANSACTION_UOM
1555 , to_unit => l_primary_quantity
1556 , from_name => NULL
1557 , to_name => NULL
1558 );
1559 END IF;
1560 */
1561 -- Spr_Debug('7: '||l_primary_uom);
1562 -- Spr_Debug('7.1: '||l_primary_quantity);
1563 -- Spr_Debug('7.2: '||l_secondary_uom_code);
1564 -- checking for item's UOM conversion rate
1565
1566 l_sec_qty := inv_convert.inv_um_convert(
1567 item_id => p_inventory_item_id
1568 , ORGANIZATION_ID => P_ORG_ID
1569 , LOT_NUMBER => P_LOT_NUMBER
1570 , PRECISION => 5
1571 , from_quantity => l_primary_quantity
1572 , from_unit => l_primary_uom
1573 , to_unit => l_secondary_uom_code
1574 , from_name => NULL
1575 , to_name => NULL
1576 );
1577 -- Spr_Debug('8: '||l_sec_qty);
1578
1579 IF round(NVL(L_SEC_QTY, 0),5) <> round(NVL(P_SECONDARY_QUANTITY,0),5) -- 1 change to 0 on RHS Onyl if Item and Lot Conversion Rates are Different
1580 AND p_parent_lot_number IS NULL -- No Parent Lot
1581 OR l_copy_pnt_lot_att_flag <> 'Y' -- Donot copy from parent
1582 OR NOT l_check_existing_parent_lot THEN --New Parent lot
1583 l_org_id := P_ORG_ID;
1584 BEGIN
1585 SELECT create_lot_uom_conversion
1586 INTO l_create_lot_uom_conv
1587 FROM mtl_parameters
1588 WHERE organization_id = l_org_id;
1589 EXCEPTION
1590 WHEN OTHERS THEN
1591 l_create_lot_uom_conv := 1;
1592 END;
1593 -- Spr_Debug('9: '||l_create_lot_uom_conv);
1594 -- get UOM classes for trxn uOM and sec uom
1595 l_from_uom_code := P_TRANSACTION_UOM;
1596 l_to_uom_code := l_secondary_uom_code ;
1597 BEGIN
1598 SELECT unit_of_measure_tl, uom_class
1599 INTO l_from_unit_of_measure, l_from_uom_class
1600 FROM MTL_UNITS_OF_MEASURE
1601 WHERE UOM_CODE = l_from_uom_Code;
1602 -- Spr_Debug('100: ');
1603
1604 EXCEPTION
1605 WHEN OTHERS THEN
1606 -- Spr_Debug('110: ');
1607
1608 l_from_unit_of_measure := NULL;
1609 l_from_uom_class := NULL;
1610 END;
1611 -- Spr_Debug('120: ');
1612 BEGIN
1613 SELECT unit_of_measure_tl, uom_class
1614 INTO l_to_unit_of_measure, l_to_uom_class
1615 FROM MTL_UNITS_OF_MEASURE
1616 WHERE UOM_CODE = l_to_uom_Code;
1617 -- Spr_Debug('130: ');
1618
1619 EXCEPTION
1620 WHEN OTHERS THEN
1621 -- Spr_Debug('140: ');
1622
1623 l_to_unit_of_measure := NULL;
1624 l_to_uom_class := NULL;
1625 END;
1626 -- Spr_Debug('145: from '|| l_from_uom_class || ',to '|| l_to_uom_class );
1627 -- l_secondary_default_ind local variable
1628 -- Spr_Debug('150: '||l_LOT_UOM_CONVERSION||' , CRT UOM CON'||l_create_lot_uom_conv ||' ,ITM DUAL COTR '||l_ITEM_DUAL_UOM_CONTROL);
1629 l_LOT_UOM_CONVERSION := 'FALSE';
1630
1631 IF NVL(l_create_lot_uom_conv,1 ) = 1 -- for 1 1 Means Yes
1632 AND l_ITEM_DUAL_UOM_CONTROL = 'PS'
1633 AND l_from_uom_class <> l_to_uom_class THEN
1634 L_LOT_UOM_CONVERSION := 'TRUE';
1635 -- Spr_Debug('160: '||l_LOT_UOM_CONVERSION);
1636 ELSIF NVL(l_create_lot_uom_conv, 1 ) = 3 --for 3 Means User Defined
1637 AND l_ITEM_DUAL_UOM_CONTROL = 'PS'
1638 AND l_from_uom_class <> l_to_uom_class THEN
1639 IF p_response = 'Y' Then
1640 l_response := 1;
1641 Else
1642 l_response := 2;
1643 End IF;
1644
1645 -- l_response := Decode(p_response,'Y',1,'N',2) ;
1646 -- Spr_Debug('170: '||l_LOT_UOM_CONVERSION);
1647 IF l_response = 1 THEN
1648 l_LOT_UOM_CONVERSION := 'TRUE';
1649 -- Spr_Debug('180: '||l_LOT_UOM_CONVERSION);
1650 ELSE
1651 l_LOT_UOM_CONVERSION := 'NO';
1652 -- Spr_Debug('190: '||l_LOT_UOM_CONVERSION);
1653 END IF;
1654 ELSE -- for 2 -- 2 Means No
1655 l_LOT_UOM_CONVERSION := 'FALSE';
1656 -- Spr_Debug('200: '||l_LOT_UOM_CONVERSION);
1657 END IF;
1658 -- Spr_Debug('210: ');
1659
1660
1661 /* Bug#5349912 even for the FIXED items, the execution flow is same
1662 so removing the condition for defaulting */
1663 --IF l_secondary_default_ind in ('N','D') AND
1664 IF NVL(l_create_lot_uom_conv, 1 ) IN (1,3) THEN
1665 IF l_LOT_UOM_CONVERSION = 'TRUE' THEN
1666 l_go := TRUE;
1667 -- Spr_Debug('220: '||l_LOT_UOM_CONVERSION);
1668 ELSE
1669 -- always YES
1670 IF NVL(l_create_lot_uom_conv, 1 ) = 1
1671 AND l_ITEM_DUAL_UOM_CONTROL = 'PS'
1672 AND l_from_uom_class <> l_to_uom_class THEN
1673 l_go := TRUE;
1674 -- Spr_Debug('230: '||l_LOT_UOM_CONVERSION);
1675 -- user response
1676 ELSIF NVL(l_create_lot_uom_conv,1 ) = 3
1677 AND l_ITEM_DUAL_UOM_CONTROL = 'PS'
1678 AND l_from_uom_class <> l_to_uom_class THEN
1679 IF NVL(l_LOT_UOM_CONVERSION,'FALSE') = 'TRUE' THEN
1680 l_go := TRUE;
1681 -- copy conversion from parent lot,if exists
1682 ELSIF p_parent_lot_number IS NOT NULL
1683 AND l_copy_pnt_lot_att_flag = 'Y'
1684 AND l_check_existing_parent_lot
1685 AND NVL(l_conversion_rate,0) <> NVL(x_conversion_rate,0) THEN
1686 l_go := TRUE;
1687 ELSE
1688 IF NVL(l_LOT_UOM_CONVERSION,'FALSE') = 'NO' THEN
1689 l_go := FALSE;
1690 -- Spr_Debug('240: '||l_LOT_UOM_CONVERSION);
1691 ELSE
1692 --based on message response
1693 IF p_response = 'Y' Then
1694 l_response := 1;
1695 Else
1696 l_response := 2;
1697 End IF;
1698 -- l_response := Decode(p_response,'Y',1,'N',2); -- 1 is Yes 2 is No
1699 IF l_response = 1 THEN
1700 l_go := TRUE;
1701 ELSE
1702 l_go := FALSE;
1703 END IF;
1704 END IF;
1705 END IF;
1706 ELSE
1707 l_go := FALSE;
1708 END IF;
1709 END IF;
1710 IF l_go THEN
1711 -- Spr_Debug('9: '||'In Seid EXPIRATION_ACTION_CODE; l_go');
1712 l_lot_uom_conv_rec.conversion_id := NULL;
1713 l_lot_uom_conv_rec.lot_number := P_LOT_NUMBER;
1714 l_lot_uom_conv_rec.organization_id := P_ORG_ID;
1715 l_lot_uom_conv_rec.inventory_item_id := P_INVENTORY_ITEM_ID;
1716 l_lot_uom_conv_rec.from_unit_of_measure := l_from_unit_of_measure;
1717 l_lot_uom_conv_rec.from_uom_code := l_from_uom_code;
1718 l_lot_uom_conv_rec.from_uom_class := l_from_uom_class;
1719 l_lot_uom_conv_rec.to_unit_of_measure := l_to_unit_of_measure;
1720 l_lot_uom_conv_rec.to_uom_code := l_to_uom_code;
1721 l_lot_uom_conv_rec.to_uom_class := l_to_uom_class;
1722 l_lot_uom_conv_rec.conversion_rate := l_conversion_rate;
1723 l_lot_uom_conv_rec.disable_date := NULL;
1724 l_lot_uom_conv_rec.event_spec_disp_id := NULL;
1725 l_lot_uom_conv_rec.created_by := FND_GLOBAL.user_id;
1726 l_lot_uom_conv_rec.creation_date := SYSDATE;
1727 l_lot_uom_conv_rec.last_updated_by := FND_GLOBAL.user_id;
1728 l_lot_uom_conv_rec.last_update_date := SYSDATE;
1729 l_lot_uom_conv_rec.last_update_login := FND_GLOBAL.login_id;
1730 l_lot_uom_conv_rec.request_id := NULL;
1731 l_lot_uom_conv_rec.program_application_id := NULL;
1732 l_lot_uom_conv_rec.program_id := NULL;
1733 l_lot_uom_conv_rec.program_update_date := NULL;
1734 IF p_parent_lot_number IS NOT NULL
1735 AND l_copy_pnt_lot_att_flag = 'Y'
1736 AND l_check_existing_parent_lot
1737 AND NVL(l_conversion_rate,0) <> NVL(x_conversion_rate,0) THEN
1738 l_action_type := 'U';
1739 ELSE
1740 l_action_type := 'I';
1741 END IF;
1742 -- P_REASON_ID input parame
1743 /*sunitha ch. bug#5531391 create lot uom conversion only if it is a new lot */
1744 IF l_lot_count = 0 THEN
1745 MTL_LOT_UOM_CONV_PUB.CREATE_LOT_UOM_CONVERSION
1746 (
1747 p_api_version => 1.0
1748 , p_init_msg_list => 'T'
1749 , p_commit => 'F'
1750 , p_validation_level => 100
1751 , p_action_type => l_action_type
1752 , p_update_type_indicator => 5
1753 , p_reason_id => P_REASON_ID
1754 , p_batch_id => 0
1755 , p_process_data => 'Y'
1756 , p_lot_uom_conv_rec => l_lot_uom_conv_rec
1757 , p_qty_update_tbl => l_qty_update_tbl
1758 , x_return_status => l_return_status
1759 , x_msg_count => l_msg_count
1760 , x_msg_data => l_msg_data
1761 , x_sequence => l_sequence
1762 );
1763 -- Spr_Debug('10: Create UOM '||l_return_status);
1764 IF l_return_status <> 'S' THEN
1765 -- dbms_output.put_line('ERROR');
1766 FND_MSG_PUB.count_and_get
1767 ( p_count => x_msg_count
1768 ,p_data => x_msg_data
1769 );
1770 END IF;
1771 END IF;--l_lot_count = 0
1772
1773 /* Bug#5349912 Begin Added the following code to copy all other lot converisons
1774 The below flag will be true only in case of Lot Split. In other cases the default is F */
1775 IF p_copy_other_conversions = fnd_api.g_true THEN
1776 MTL_LOT_UOM_CONV_PVT.copy_lot_uom_conversions (
1777 p_from_organization_id => l_lot_uom_conv_rec.organization_id
1778 , p_to_organization_id => l_lot_uom_conv_rec.organization_id
1779 , p_inventory_item_id => l_lot_uom_conv_rec.inventory_item_id
1780 , p_from_lot_number => p_source_lot
1781 , p_to_lot_number => l_lot_uom_conv_rec.lot_number
1782 , p_user_id => fnd_global.user_id
1783 , p_creation_date => SYSDATE
1784 , p_commit => fnd_api.g_true
1785 , x_return_status => l_return_status
1786 , x_msg_count => l_msg_count
1787 , x_msg_data => l_msg_data );
1788
1789 IF l_return_status <> 'S' THEN
1790 FND_MSG_PUB.count_and_get
1791 ( p_count => x_msg_count
1792 ,p_data => x_msg_data
1793 );
1794 END IF; /* p_copy_other_conversions = fnd_api.g_true */
1795 END IF;
1796 --Bug#5349912 End
1797 END IF;
1798 END IF;
1799 END IF;
1800
1801 -- Spr_Debug('RETURNING');
1802 x_return_status := NVL(l_return_status,'S');
1803 x_msg_data := NVL(l_msg_data,'NO ERROR');
1804 x_msg_count := NVL(l_msg_count,0);
1805 -- Spr_Debug('RETURNED '||x_return_status||' '||x_msg_data||' '||x_msg_count);
1806 END Save_Lot_UOM_Conv;
1807
1808
1809 --Added for bug 7426180 start
1810
1811 PROCEDURE GET_ORG_COPY_LOTATTR_FLAG(
1812 x_return_status OUT NOCOPY VARCHAR2
1813 , x_msg_count OUT NOCOPY NUMBER
1814 , x_msg_data OUT NOCOPY VARCHAR2
1815 , x_copy_lot_attr_flag OUT NOCOPY VARCHAR2
1816 , p_organization_id IN NUMBER
1817 , p_inventory_item_id IN NUMBER
1818 ) IS
1819 l_copy_lot_attribute_flag Varchar2(1):='N';
1820 BEGIN
1821
1822 SELECT NVL(copy_lot_attribute_flag,'N') INTO l_copy_lot_attribute_flag
1823 FROM mtl_parameters
1824 WHERE organization_id = p_organization_id;
1825
1826
1827 IF(l_copy_lot_attribute_flag ='N') THEN
1828
1829 SELECT NVL(copy_lot_attribute_flag,'N') INTO l_copy_lot_attribute_flag
1830 FROM mtl_system_items
1831 WHERE inventory_item_id = p_inventory_item_id
1832 AND organization_id = p_organization_id;
1833 END IF;
1834
1835 x_copy_lot_attr_flag := l_copy_lot_attribute_flag ;
1836 print_debug('GET_ORG_COPY_LOTATTR_FLAG: x_copy_lot_attr_flag '|| x_copy_lot_attr_flag, 9);
1837
1838 EXCEPTION
1839 WHEN OTHERS THEN
1840 x_return_status := fnd_api.G_RET_STS_ERROR;
1841
1842 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1843 p_count => x_msg_count,
1844 p_data => x_msg_data);
1845 IF( x_msg_count > 1 ) THEN
1846 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE);
1847 END IF;
1848
1849 END GET_ORG_COPY_LOTATTR_FLAG;
1850
1851 --Added for bug 7426180 end
1852
1853
1854
1855 END inv_lot_apis;