[Home] [Help]
PACKAGE BODY: APPS.INV_LOT_APIS
Source
1 PACKAGE BODY INV_LOT_APIS AS
2 /* $Header: INVLOTAB.pls 120.5 2008/01/21 14:50:27 aambulka 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 )
1183 IS
1184 l_return_status VARCHAR2(1) ;
1185 l_msg_data VARCHAR2(3000) ;
1186 l_msg_count NUMBER ;
1187 x_lot_rec MTL_LOT_NUMBERS%ROWTYPE; -- for lot api
1188 l_in_lot_rec MTL_LOT_NUMBERS%ROWTYPE; -- for lot api
1189 l_lot_uom_conv_rec mtl_lot_uom_class_conversions%ROWTYPE; -- for uom conv
1190 l_qty_update_tbl MTL_LOT_UOM_CONV_PUB.quantity_update_rec_type; -- for uom conv
1191 l_api_version NUMBER;
1192 l_init_msg_list VARCHAR2(100);
1193 l_commit VARCHAR2(100);
1194 l_validation_level NUMBER;
1195 l_origin_txn_id NUMBER;
1196 l_source NUMBER;
1197 l_create_lot_uom_conv NUMBER;
1198 l_org_id NUMBER;
1199 l_from_uom_code MTL_UNITS_OF_MEASURE.UOM_CODE%TYPE;
1200 l_to_uom_code MTL_UNITS_OF_MEASURE.UOM_CODE%TYPE;
1201 l_from_unit_of_measure MTL_UNITS_OF_MEASURE.UNIT_OF_MEASURE_TL%TYPE;
1202 l_to_unit_of_measure MTL_UNITS_OF_MEASURE.UNIT_OF_MEASURE_TL%TYPE;
1203 l_from_uom_class MTL_UNITS_OF_MEASURE.UOM_CLASS%TYPE;
1204 l_to_uom_class MTL_UNITS_OF_MEASURE.UOM_CLASS%TYPE;
1205 l_conversion_rate MTL_LOT_UOM_CLASS_CONVERSIONS.CONVERSION_RATE%TYPE;
1206 x_conversion_rate MTL_LOT_UOM_CLASS_CONVERSIONS.CONVERSION_RATE%TYPE;
1207 l_go BOOLEAN;
1208 l_response NUMBER;
1209 l_sequence NUMBER;
1210 l_action_type VARCHAR2(1);
1211 l_lot_number mtl_transaction_lots_temp.lot_number%TYPE := p_lot_number;
1212 l_check_existing_parent_lot BOOLEAN;
1213 l_row_id ROWID;
1214 l_exists VARCHAR2(10);
1215 L_LOT_UOM_CONVERSION VARCHAR2(10);
1216 l_sec_qty Number := 0;
1217 l_primary_uom MTL_UNITS_OF_MEASURE.UOM_CODE%TYPE;
1218 l_primary_quantity NUMBER ;
1219 l_ITEM_DUAL_UOM_CONTROL mtl_system_items.tracking_quantity_ind%TYPE ;
1220 l_copy_pnt_lot_att_flag mtl_system_items.copy_lot_attribute_flag%TYPE ;
1221 l_secondary_default_ind mtl_system_items.secondary_default_ind%TYPE ;
1222 l_secondary_uom_code mtl_system_items.secondary_uom_code%TYPE;
1223 /* Cursor definition to get Item attributes*/
1224 cursor c_get_item_attr
1225 IS
1226 SELECT primary_uom_code
1227 , secondary_uom_code
1228 , secondary_default_ind
1229 , copy_lot_attribute_flag
1230 , tracking_quantity_ind
1231 FROM mtl_system_items
1232 WHERE organization_id = p_org_id
1233 AND inventory_item_id = p_inventory_item_id ;
1234
1235 l_get_item_attr c_get_item_attr%ROWTYPE ;
1236
1237 /* Cursor definition to check if Lot UOM Conversion is needed */
1238 CURSOR c_lot_uom_conv IS
1239 SELECT copy_lot_attribute_flag,
1240 lot_number_generation
1241 FROM mtl_parameters
1242 WHERE organization_id = p_org_id;
1243
1244 l_lot_uom_conv c_lot_uom_conv%ROWTYPE ;
1245
1246 /* Cursor to check if a lot already exists*/
1247 CURSOR c_lot_exists IS
1248 SELECT 1
1249 FROM mtl_lot_numbers
1250 WHERE organization_id = p_org_id
1251 AND inventory_item_id = p_inventory_item_id
1252 AND lot_number = p_lot_number ;
1253
1254 l_lot_count NUMBER := 0;
1255
1256 BEGIN
1257 /* Step 1 ...preparing to insert lot in MLN by calling CREATE_INV_LOT
1258 * This will also take care of copying Parent's UOM Conv record for child lot
1259 */
1260 l_primary_uom := NULL; --p_primary_uom ;
1261 l_primary_quantity := NULL; --p_primary_quantity ;
1262 l_ITEM_DUAL_UOM_CONTROL := NULL; --p_item_dual_uom_control ;
1263 l_copy_pnt_lot_att_flag := NULL; --p_copy_pnt_lot_att_flag ;
1264 l_secondary_default_ind := NULL; --p_secondary_default_ind ;
1265 l_secondary_uom_code := NULL;-- p_secondary_uom_code;
1266 -- Spr_Debug('1');
1267 OPEN c_get_item_attr;
1268 FETCH c_get_item_attr INTO l_get_item_attr;
1269 CLOSE c_get_item_attr;
1270
1271 OPEN c_lot_exists;
1272 FETCH c_lot_exists INTO l_lot_count;
1273 CLOSE c_lot_exists;
1274
1275
1276 l_return_status := NULL;
1277 l_msg_data := NULL;
1278 l_msg_count := NULL;
1279 l_source := NULL ;
1280 l_api_version := 1.0;
1281 l_init_msg_list := 'T';
1282 l_commit := 'F';
1283 l_validation_level := 100;
1284 l_in_lot_rec.organization_id := p_org_id ;
1285 l_in_lot_rec.inventory_item_id := p_inventory_item_id ;
1286 l_in_lot_rec.expiration_date := p_expiration_date;
1287 l_in_lot_rec.grade_code := p_grade_code ;
1288 l_in_lot_rec.lot_number := p_lot_number ;
1289 l_in_lot_rec.parent_lot_number := p_parent_lot_number;
1290 l_in_lot_rec.origination_date := p_ORIGINATION_DATE;
1291 l_in_lot_rec.retest_date := p_RETEST_DATE ;
1292 l_in_lot_rec.maturity_date := P_MATURITY_DATE;
1293 l_in_lot_rec.attribute_category := P_ATTRIBUTE_CATEGORY;
1294 l_in_lot_rec.origination_type := P_ORIGINATION_TYPE;
1295 l_in_lot_rec.hold_date := P_HOLD_DATE;
1296 l_in_lot_rec.expiration_action_code := P_EXPIRATION_ACTION_CODE;
1297 l_in_lot_rec.expiration_action_date := P_EXPIRATION_ACTION_DATE;
1298 l_in_lot_rec.status_id := P_STATUS_ID;
1299 l_in_lot_rec.supplier_lot_number := P_SUPPLIER_LOT_NUMBER;
1300 l_in_lot_rec.LOT_ATTRIBUTE_CATEGORY := P_LOT_ATTRIBUTE_CATEGORY;
1301 l_in_lot_rec.ATTRIBUTE1:= P_ATTRIBUTE1;
1302 l_in_lot_rec.ATTRIBUTE2:= P_ATTRIBUTE2;
1303 l_in_lot_rec.ATTRIBUTE3:= P_ATTRIBUTE3;
1304 l_in_lot_rec.ATTRIBUTE4:= P_ATTRIBUTE4;
1305 l_in_lot_rec.ATTRIBUTE5:= P_ATTRIBUTE5;
1306 l_in_lot_rec.ATTRIBUTE6:= P_ATTRIBUTE6;
1307 l_in_lot_rec.ATTRIBUTE7:= P_ATTRIBUTE7;
1308 l_in_lot_rec.ATTRIBUTE8:= P_ATTRIBUTE8;
1309 l_in_lot_rec.ATTRIBUTE9:= P_ATTRIBUTE9;
1310 l_in_lot_rec.ATTRIBUTE10:= P_ATTRIBUTE10;
1311 l_in_lot_rec.ATTRIBUTE11:= P_ATTRIBUTE11;
1312 l_in_lot_rec.ATTRIBUTE12:= P_ATTRIBUTE12;
1313 l_in_lot_rec.ATTRIBUTE13:= P_ATTRIBUTE13;
1314 l_in_lot_rec.ATTRIBUTE14:= P_ATTRIBUTE14;
1315 l_in_lot_rec.ATTRIBUTE15:= P_ATTRIBUTE15;
1316 l_in_lot_rec.C_ATTRIBUTE1:= P_C_ATTRIBUTE1;
1317 l_in_lot_rec.C_ATTRIBUTE2:= P_C_ATTRIBUTE2;
1318 l_in_lot_rec.C_ATTRIBUTE3:= P_C_ATTRIBUTE3;
1319 l_in_lot_rec.C_ATTRIBUTE4:= P_C_ATTRIBUTE4;
1320 l_in_lot_rec.C_ATTRIBUTE5:= P_C_ATTRIBUTE5;
1321 l_in_lot_rec.C_ATTRIBUTE6:= P_C_ATTRIBUTE6;
1322 l_in_lot_rec.C_ATTRIBUTE7:= P_C_ATTRIBUTE7;
1323 l_in_lot_rec.C_ATTRIBUTE8:= P_C_ATTRIBUTE8;
1324 l_in_lot_rec.C_ATTRIBUTE9:= P_C_ATTRIBUTE9;
1325 l_in_lot_rec.C_ATTRIBUTE10:= P_C_ATTRIBUTE10;
1326 l_in_lot_rec.C_ATTRIBUTE11:= P_C_ATTRIBUTE11;
1327 l_in_lot_rec.C_ATTRIBUTE12:= P_C_ATTRIBUTE12;
1328 l_in_lot_rec.C_ATTRIBUTE13:= P_C_ATTRIBUTE13;
1329 l_in_lot_rec.C_ATTRIBUTE14:= P_C_ATTRIBUTE14;
1330 l_in_lot_rec.C_ATTRIBUTE15:= P_C_ATTRIBUTE15;
1331 l_in_lot_rec.C_ATTRIBUTE16:= P_C_ATTRIBUTE16;
1332 l_in_lot_rec.C_ATTRIBUTE17:= P_C_ATTRIBUTE17;
1333 l_in_lot_rec.C_ATTRIBUTE18:= P_C_ATTRIBUTE18;
1334 l_in_lot_rec.C_ATTRIBUTE19:= P_C_ATTRIBUTE19;
1335 l_in_lot_rec.C_ATTRIBUTE20:= P_C_ATTRIBUTE20;
1336 l_in_lot_rec.D_ATTRIBUTE1:= P_D_ATTRIBUTE1;
1337 l_in_lot_rec.D_ATTRIBUTE2:= P_D_ATTRIBUTE2;
1338 l_in_lot_rec.D_ATTRIBUTE3:= P_D_ATTRIBUTE3;
1339 l_in_lot_rec.D_ATTRIBUTE4:= P_D_ATTRIBUTE4;
1340 l_in_lot_rec.D_ATTRIBUTE5:= P_D_ATTRIBUTE5;
1341 l_in_lot_rec.D_ATTRIBUTE6:= P_D_ATTRIBUTE6;
1342 l_in_lot_rec.D_ATTRIBUTE7:= P_D_ATTRIBUTE7;
1343 l_in_lot_rec.D_ATTRIBUTE8:= P_D_ATTRIBUTE8;
1344 l_in_lot_rec.D_ATTRIBUTE9:= P_D_ATTRIBUTE9;
1345 l_in_lot_rec.D_ATTRIBUTE10:= P_D_ATTRIBUTE10;
1346 l_in_lot_rec.N_ATTRIBUTE1:= P_N_ATTRIBUTE1;
1347 l_in_lot_rec.N_ATTRIBUTE2:= P_N_ATTRIBUTE2;
1348 l_in_lot_rec.N_ATTRIBUTE3:= P_N_ATTRIBUTE3;
1349 l_in_lot_rec.N_ATTRIBUTE4:= P_N_ATTRIBUTE4;
1350 l_in_lot_rec.N_ATTRIBUTE5:= P_N_ATTRIBUTE5;
1351 l_in_lot_rec.N_ATTRIBUTE6:= P_N_ATTRIBUTE6;
1352 l_in_lot_rec.N_ATTRIBUTE7:= P_N_ATTRIBUTE7;
1353 l_in_lot_rec.N_ATTRIBUTE8:= P_N_ATTRIBUTE8;
1354 l_in_lot_rec.N_ATTRIBUTE9:= P_N_ATTRIBUTE9;
1355 l_in_lot_rec.N_ATTRIBUTE10:= P_N_ATTRIBUTE10;
1356 l_in_lot_rec.disable_flag := p_disable_flag ; --- Please Verify if any Page requires It
1357 l_in_lot_rec.date_code := p_date_code;
1358 l_in_lot_rec.change_date := p_change_date ;
1359 l_in_lot_rec.age := p_age ;
1360 l_in_lot_rec.item_size := p_item_size ;
1361 l_in_lot_rec.color := p_color ;
1362 l_in_lot_rec.volume := p_volume ;
1363 l_in_lot_rec.volume_uom := p_volume_uom ;
1364 l_in_lot_rec.place_of_origin := p_place_of_origin ;
1365 l_in_lot_rec.best_by_date := p_best_by_date ;
1366 l_in_lot_rec.length := p_length ;
1367 l_in_lot_rec.length_uom := p_length_uom ;
1368 l_in_lot_rec.recycled_content := p_recycled_content ;
1369 l_in_lot_rec.thickness := p_thickness ;
1370 l_in_lot_rec.thickness_uom := p_thickness_uom ;
1371 l_in_lot_rec.width := p_width ;
1372 l_in_lot_rec.width_uom := p_width_uom ;
1373 l_in_lot_rec.territory_code := p_territory_code ;
1374 l_in_lot_rec.vendor_name := p_vendor_name ; -- Please Verify if any Page Requires it
1375
1376 l_row_id := NULL;
1377 -- Spr_Debug('2 '|| l_in_lot_rec.lot_number );
1378 -- Spr_Debug('2 .5 '|| l_lot_count ) ;
1379 IF l_lot_count = 0 THEN
1380 INV_LOT_API_PUB.Create_Inv_lot(
1381 x_return_status => l_return_status
1382 , x_msg_count => l_msg_count
1383 , x_msg_data => l_msg_data
1384 , x_lot_rec => x_lot_rec
1385 , p_lot_rec => l_in_lot_rec
1386 , p_source => l_source
1387 , p_api_version => l_api_version
1388 , p_init_msg_list => l_init_msg_list
1389 , p_commit => l_commit
1390 , p_validation_level => l_validation_level
1391 , p_origin_txn_id => NULL
1392 , x_row_id => l_row_id
1393 );
1394 -- Spr_Debug('3: '||l_return_status);
1395
1396 IF l_return_status <> 'S' THEN
1397 -- dbms_output.put_line('ERROR');
1398 FND_MSG_PUB.count_and_get
1399 ( p_count => l_msg_count
1400 , p_data => l_msg_data
1401 );
1402 -- Spr_Debug('3i '||x_msg_data);
1403 END IF;
1404 END IF; -- COUNT check
1405 /*
1406 * Step 2..Checking if lot specific UOM conversion are needed or not
1407 */
1408 -- l_ITEM_DUAL_UOM_CONTROL IN VARCHAR2 is a new parameter, hold item's Tracking indicator
1409 -- P_TRANSACTION_QUANTITY IN NUMBER
1410 -- P_SECONDARY_QUANTITY IN NUMBER
1411 -- checking for lots UOM conversion rate
1412 -- Spr_Debug('4: '||l_ITEM_DUAL_UOM_CONTROL );
1413 -- Spr_Debug('4.2: primary_uom_code UOM '||l_primary_uom );
1414
1415 -- Check if item is dual controlled.
1416 -- if not then return from here, no need to create UOM conversion record.
1417 IF l_get_item_attr.tracking_quantity_ind <> 'PS' THEN
1418
1419 x_return_status := l_return_status ;
1420 x_msg_count := l_msg_count ;
1421 x_msg_data := l_msg_data ;
1422
1423 RETURN ;
1424 END IF ;
1425
1426 IF l_primary_uom IS NULL THEN
1427 l_primary_uom := l_get_item_attr.primary_uom_code ;
1428 END IF;
1429 IF l_secondary_uom_code IS NULL THEN
1430 l_secondary_uom_code := l_get_item_attr.secondary_uom_code;
1431 END IF;
1432 -- Spr_Debug('4.5: primary_uom_code UOM '||l_primary_uom );
1433 IF l_ITEM_DUAL_UOM_CONTROL IS NULL THEN
1434 l_ITEM_DUAL_UOM_CONTROL := l_get_item_attr.tracking_quantity_ind ;
1435 END IF;
1436
1437 IF l_secondary_default_ind IS NULL THEN
1438 l_secondary_default_ind := l_get_item_attr.secondary_default_ind ;
1439 END IF;
1440
1441 IF l_copy_pnt_lot_att_flag IS NULL THEN
1442 l_copy_pnt_lot_att_flag := l_get_item_attr.copy_lot_attribute_flag ;
1443 END IF;
1444 /* Check needed for Lot UOM conversion */
1445 -- Spr_Debug('6: ');
1446 OPEN c_lot_uom_conv ;
1447 FETCH c_lot_uom_conv INTO l_lot_uom_conv ;
1448
1449 IF c_lot_uom_conv%FOUND THEN
1450 -- Possible values for mtl_parameters.lot_number_generation are:
1451 -- 1 At organization level
1452 -- 3 User defined
1453 -- 2 At item level
1454 -- Spr_Debug('7: ');
1455 IF l_lot_uom_conv.lot_number_generation = 1 THEN
1456 l_copy_pnt_lot_att_flag := NVL(l_lot_uom_conv.copy_lot_attribute_flag,'N') ;
1457 END IF ;
1458 END IF;
1459 CLOSE c_lot_uom_conv ;
1460
1461 IF l_primary_quantity IS NULL THEN
1462 l_primary_quantity := inv_convert.inv_um_convert(
1463 item_id => p_inventory_item_id
1464 , ORGANIZATION_ID => P_ORG_ID
1465 , LOT_NUMBER => P_LOT_NUMBER
1466 , PRECISION => 5
1467 , from_quantity => P_TRANSACTION_QUANTITY
1468 , from_unit => P_TRANSACTION_UOM
1469 , to_unit => l_primary_uom
1470 , from_name => NULL
1471 , to_name => NULL
1472 );
1473 END IF;
1474 -- Spr_Debug('7: '||l_primary_quantity);
1475
1476 IF l_ITEM_DUAL_UOM_CONTROL = 'PS' THEN
1477 l_conversion_rate := NVL( NVL(P_TRANSACTION_QUANTITY,1) / NVL(P_SECONDARY_QUANTITY,1) ,1);
1478 IF l_conversion_rate <= 0 THEN
1479 l_conversion_rate := 1;
1480 END IF;
1481 END IF;
1482
1483 IF p_parent_lot_number IS NOT NULL THEN
1484 --- Check if Parent lot Already Exists
1485 --Bug#5349912 changed from p_lot_number to p_parent_lot_number in the following query
1486 BEGIN
1487 SELECT count('1')
1488 INTO l_exists
1489 FROM mtl_lot_numbers
1490 WHERE inventory_item_id = P_inventory_item_id
1491 AND organization_id = p_org_id
1492 AND lot_number = p_parent_lot_number
1493 AND ROWNUM = 1;
1494 EXCEPTION
1495 WHEN no_data_found THEN
1496 l_exists := 0;
1497 END;
1498 IF NVL(l_exists,0) > 0 THEN
1499 l_check_existing_parent_lot := TRUE;
1500 ELSE
1501 l_check_existing_parent_lot := FALSE;
1502 END IF;
1503 ELSE
1504 l_check_existing_parent_lot := FALSE;
1505 END IF;
1506 -- Spr_Debug('5: ');
1507 -- calculate l_primary_quantity if its null
1508 -- obtain l_primary_uom from item if its null
1509 /* IF l_primary_uom IS NULL THEN
1510 l_primary_uom := l_get_item_attr.primary_uom_code ;
1511 END IF;
1512
1513 IF l_ITEM_DUAL_UOM_CONTROL IS NULL THEN
1514 l_ITEM_DUAL_UOM_CONTROL := l_get_item_attr.tracking_quantity_ind ;
1515 END IF;
1516
1517 IF l_secondary_default_ind IS NULL THEN
1518 l_secondary_default_ind := l_get_item_attr.secondary_default_ind ;
1519 END IF;
1520
1521 IF l_copy_pnt_lot_att_flag IS NULL THEN
1522 l_copy_pnt_lot_att_flag := l_get_item_attr.copy_lot_attribute_flag ;
1523 END IF;
1524 Check needed for Lot UOM conversion
1525 -- Spr_Debug('6: ');
1526 OPEN c_lot_uom_conv ;
1527 FETCH c_lot_uom_conv INTO l_lot_uom_conv ;
1528
1529 IF c_lot_uom_conv%FOUND THEN
1530 -- Possible values for mtl_parameters.lot_number_generation are:
1531 -- 1 At organization level
1532 -- 3 User defined
1533 -- 2 At item level
1534 -- Spr_Debug('7: ');
1535 IF l_lot_uom_conv.lot_number_generation = 1 THEN
1536 l_copy_pnt_lot_att_flag := NVL(l_lot_uom_conv.copy_lot_attribute_flag,'N') ;
1537 END IF ;
1538 END IF;
1539 CLOSE c_lot_uom_conv ;
1540
1541 IF l_primary_quantity IS NULL THEN
1542 l_primary_quantity := inv_convert.inv_um_convert(
1543 item_id => p_inventory_item_id
1544 , ORGANIZATION_ID => P_ORG_ID
1545 , LOT_NUMBER => P_LOT_NUMBER
1546 , PRECISION => 5
1547 , from_quantity => P_TRANSACTION_QUANTITY
1548 , from_unit => P_TRANSACTION_UOM
1549 , to_unit => l_primary_quantity
1550 , from_name => NULL
1551 , to_name => NULL
1552 );
1553 END IF;
1554 */
1555 -- Spr_Debug('7: '||l_primary_uom);
1556 -- Spr_Debug('7.1: '||l_primary_quantity);
1557 -- Spr_Debug('7.2: '||l_secondary_uom_code);
1558 -- checking for item's UOM conversion rate
1559
1560 l_sec_qty := inv_convert.inv_um_convert(
1561 item_id => p_inventory_item_id
1562 , ORGANIZATION_ID => P_ORG_ID
1563 , LOT_NUMBER => P_LOT_NUMBER
1564 , PRECISION => 5
1565 , from_quantity => l_primary_quantity
1566 , from_unit => l_primary_uom
1567 , to_unit => l_secondary_uom_code
1568 , from_name => NULL
1569 , to_name => NULL
1570 );
1571 -- Spr_Debug('8: '||l_sec_qty);
1572
1573 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
1574 AND p_parent_lot_number IS NULL -- No Parent Lot
1575 OR l_copy_pnt_lot_att_flag <> 'Y' -- Donot copy from parent
1576 OR NOT l_check_existing_parent_lot THEN --New Parent lot
1577 l_org_id := P_ORG_ID;
1578 BEGIN
1579 SELECT create_lot_uom_conversion
1580 INTO l_create_lot_uom_conv
1581 FROM mtl_parameters
1582 WHERE organization_id = l_org_id;
1583 EXCEPTION
1584 WHEN OTHERS THEN
1585 l_create_lot_uom_conv := 1;
1586 END;
1587 -- Spr_Debug('9: '||l_create_lot_uom_conv);
1588 -- get UOM classes for trxn uOM and sec uom
1589 l_from_uom_code := P_TRANSACTION_UOM;
1590 l_to_uom_code := l_secondary_uom_code ;
1591 BEGIN
1592 SELECT unit_of_measure_tl, uom_class
1593 INTO l_from_unit_of_measure, l_from_uom_class
1594 FROM MTL_UNITS_OF_MEASURE
1595 WHERE UOM_CODE = l_from_uom_Code;
1596 -- Spr_Debug('100: ');
1597
1598 EXCEPTION
1599 WHEN OTHERS THEN
1600 -- Spr_Debug('110: ');
1601
1602 l_from_unit_of_measure := NULL;
1603 l_from_uom_class := NULL;
1604 END;
1605 -- Spr_Debug('120: ');
1606 BEGIN
1607 SELECT unit_of_measure_tl, uom_class
1608 INTO l_to_unit_of_measure, l_to_uom_class
1609 FROM MTL_UNITS_OF_MEASURE
1610 WHERE UOM_CODE = l_to_uom_Code;
1611 -- Spr_Debug('130: ');
1612
1613 EXCEPTION
1614 WHEN OTHERS THEN
1615 -- Spr_Debug('140: ');
1616
1617 l_to_unit_of_measure := NULL;
1618 l_to_uom_class := NULL;
1619 END;
1620 -- Spr_Debug('145: from '|| l_from_uom_class || ',to '|| l_to_uom_class );
1621 -- l_secondary_default_ind local variable
1622 -- Spr_Debug('150: '||l_LOT_UOM_CONVERSION||' , CRT UOM CON'||l_create_lot_uom_conv ||' ,ITM DUAL COTR '||l_ITEM_DUAL_UOM_CONTROL);
1623 l_LOT_UOM_CONVERSION := 'FALSE';
1624
1625 IF NVL(l_create_lot_uom_conv,1 ) = 1 -- for 1 1 Means Yes
1626 AND l_ITEM_DUAL_UOM_CONTROL = 'PS'
1627 AND l_from_uom_class <> l_to_uom_class THEN
1628 L_LOT_UOM_CONVERSION := 'TRUE';
1629 -- Spr_Debug('160: '||l_LOT_UOM_CONVERSION);
1630 ELSIF NVL(l_create_lot_uom_conv, 1 ) = 3 --for 3 Means User Defined
1631 AND l_ITEM_DUAL_UOM_CONTROL = 'PS'
1632 AND l_from_uom_class <> l_to_uom_class THEN
1633 IF p_response = 'Y' Then
1634 l_response := 1;
1635 Else
1636 l_response := 2;
1637 End IF;
1638
1639 -- l_response := Decode(p_response,'Y',1,'N',2) ;
1640 -- Spr_Debug('170: '||l_LOT_UOM_CONVERSION);
1641 IF l_response = 1 THEN
1642 l_LOT_UOM_CONVERSION := 'TRUE';
1643 -- Spr_Debug('180: '||l_LOT_UOM_CONVERSION);
1644 ELSE
1645 l_LOT_UOM_CONVERSION := 'NO';
1646 -- Spr_Debug('190: '||l_LOT_UOM_CONVERSION);
1647 END IF;
1648 ELSE -- for 2 -- 2 Means No
1649 l_LOT_UOM_CONVERSION := 'FALSE';
1650 -- Spr_Debug('200: '||l_LOT_UOM_CONVERSION);
1651 END IF;
1652 -- Spr_Debug('210: ');
1653
1654
1655 /* Bug#5349912 even for the FIXED items, the execution flow is same
1656 so removing the condition for defaulting */
1657 --IF l_secondary_default_ind in ('N','D') AND
1658 IF NVL(l_create_lot_uom_conv, 1 ) IN (1,3) THEN
1659 IF l_LOT_UOM_CONVERSION = 'TRUE' THEN
1660 l_go := TRUE;
1661 -- Spr_Debug('220: '||l_LOT_UOM_CONVERSION);
1662 ELSE
1663 -- always YES
1664 IF NVL(l_create_lot_uom_conv, 1 ) = 1
1665 AND l_ITEM_DUAL_UOM_CONTROL = 'PS'
1666 AND l_from_uom_class <> l_to_uom_class THEN
1667 l_go := TRUE;
1668 -- Spr_Debug('230: '||l_LOT_UOM_CONVERSION);
1669 -- user response
1670 ELSIF NVL(l_create_lot_uom_conv,1 ) = 3
1671 AND l_ITEM_DUAL_UOM_CONTROL = 'PS'
1672 AND l_from_uom_class <> l_to_uom_class THEN
1673 IF NVL(l_LOT_UOM_CONVERSION,'FALSE') = 'TRUE' THEN
1674 l_go := TRUE;
1675 -- copy conversion from parent lot,if exists
1676 ELSIF p_parent_lot_number IS NOT NULL
1677 AND l_copy_pnt_lot_att_flag = 'Y'
1678 AND l_check_existing_parent_lot
1679 AND NVL(l_conversion_rate,0) <> NVL(x_conversion_rate,0) THEN
1680 l_go := TRUE;
1681 ELSE
1682 IF NVL(l_LOT_UOM_CONVERSION,'FALSE') = 'NO' THEN
1683 l_go := FALSE;
1684 -- Spr_Debug('240: '||l_LOT_UOM_CONVERSION);
1685 ELSE
1686 --based on message response
1687 IF p_response = 'Y' Then
1688 l_response := 1;
1689 Else
1690 l_response := 2;
1691 End IF;
1692 -- l_response := Decode(p_response,'Y',1,'N',2); -- 1 is Yes 2 is No
1693 IF l_response = 1 THEN
1694 l_go := TRUE;
1695 ELSE
1696 l_go := FALSE;
1697 END IF;
1698 END IF;
1699 END IF;
1700 ELSE
1701 l_go := FALSE;
1702 END IF;
1703 END IF;
1704 IF l_go THEN
1705 -- Spr_Debug('9: '||'In Seid EXPIRATION_ACTION_CODE; l_go');
1706 l_lot_uom_conv_rec.conversion_id := NULL;
1707 l_lot_uom_conv_rec.lot_number := P_LOT_NUMBER;
1708 l_lot_uom_conv_rec.organization_id := P_ORG_ID;
1709 l_lot_uom_conv_rec.inventory_item_id := P_INVENTORY_ITEM_ID;
1710 l_lot_uom_conv_rec.from_unit_of_measure := l_from_unit_of_measure;
1711 l_lot_uom_conv_rec.from_uom_code := l_from_uom_code;
1712 l_lot_uom_conv_rec.from_uom_class := l_from_uom_class;
1713 l_lot_uom_conv_rec.to_unit_of_measure := l_to_unit_of_measure;
1714 l_lot_uom_conv_rec.to_uom_code := l_to_uom_code;
1715 l_lot_uom_conv_rec.to_uom_class := l_to_uom_class;
1716 l_lot_uom_conv_rec.conversion_rate := l_conversion_rate;
1717 l_lot_uom_conv_rec.disable_date := NULL;
1718 l_lot_uom_conv_rec.event_spec_disp_id := NULL;
1719 l_lot_uom_conv_rec.created_by := FND_GLOBAL.user_id;
1720 l_lot_uom_conv_rec.creation_date := SYSDATE;
1721 l_lot_uom_conv_rec.last_updated_by := FND_GLOBAL.user_id;
1722 l_lot_uom_conv_rec.last_update_date := SYSDATE;
1723 l_lot_uom_conv_rec.last_update_login := FND_GLOBAL.login_id;
1724 l_lot_uom_conv_rec.request_id := NULL;
1725 l_lot_uom_conv_rec.program_application_id := NULL;
1726 l_lot_uom_conv_rec.program_id := NULL;
1727 l_lot_uom_conv_rec.program_update_date := NULL;
1728 IF p_parent_lot_number IS NOT NULL
1729 AND l_copy_pnt_lot_att_flag = 'Y'
1730 AND l_check_existing_parent_lot
1731 AND NVL(l_conversion_rate,0) <> NVL(x_conversion_rate,0) THEN
1732 l_action_type := 'U';
1733 ELSE
1734 l_action_type := 'I';
1735 END IF;
1736 -- P_REASON_ID input parame
1737 /*sunitha ch. bug#5531391 create lot uom conversion only if it is a new lot */
1738 IF l_lot_count = 0 THEN
1739 MTL_LOT_UOM_CONV_PUB.CREATE_LOT_UOM_CONVERSION
1740 (
1741 p_api_version => 1.0
1742 , p_init_msg_list => 'T'
1743 , p_commit => 'F'
1744 , p_validation_level => 100
1745 , p_action_type => l_action_type
1746 , p_update_type_indicator => 5
1747 , p_reason_id => P_REASON_ID
1748 , p_batch_id => 0
1749 , p_process_data => 'Y'
1750 , p_lot_uom_conv_rec => l_lot_uom_conv_rec
1751 , p_qty_update_tbl => l_qty_update_tbl
1752 , x_return_status => l_return_status
1753 , x_msg_count => l_msg_count
1754 , x_msg_data => l_msg_data
1755 , x_sequence => l_sequence
1756 );
1757 -- Spr_Debug('10: Create UOM '||l_return_status);
1758 IF l_return_status <> 'S' THEN
1759 -- dbms_output.put_line('ERROR');
1760 FND_MSG_PUB.count_and_get
1761 ( p_count => x_msg_count
1762 ,p_data => x_msg_data
1763 );
1764 END IF;
1765 END IF;--l_lot_count = 0
1766
1767 /* Bug#5349912 Begin Added the following code to copy all other lot converisons
1768 The below flag will be true only in case of Lot Split. In other cases the default is F */
1769 IF p_copy_other_conversions = fnd_api.g_true THEN
1770 MTL_LOT_UOM_CONV_PVT.copy_lot_uom_conversions (
1771 p_from_organization_id => l_lot_uom_conv_rec.organization_id
1772 , p_to_organization_id => l_lot_uom_conv_rec.organization_id
1773 , p_inventory_item_id => l_lot_uom_conv_rec.inventory_item_id
1774 , p_from_lot_number => p_source_lot
1775 , p_to_lot_number => l_lot_uom_conv_rec.lot_number
1776 , p_user_id => fnd_global.user_id
1777 , p_creation_date => SYSDATE
1778 , p_commit => fnd_api.g_true
1779 , x_return_status => l_return_status
1780 , x_msg_count => l_msg_count
1781 , x_msg_data => l_msg_data );
1782
1783 IF l_return_status <> 'S' THEN
1784 FND_MSG_PUB.count_and_get
1785 ( p_count => x_msg_count
1786 ,p_data => x_msg_data
1787 );
1788 END IF; /* p_copy_other_conversions = fnd_api.g_true */
1789 END IF;
1790 --Bug#5349912 End
1791 END IF;
1792 END IF;
1793 END IF;
1794
1795 -- Spr_Debug('RETURNING');
1796 x_return_status := NVL(l_return_status,'S');
1797 x_msg_data := NVL(l_msg_data,'NO ERROR');
1798 x_msg_count := NVL(l_msg_count,0);
1799 -- Spr_Debug('RETURNED '||x_return_status||' '||x_msg_data||' '||x_msg_count);
1800 END Save_Lot_UOM_Conv;
1801
1802
1803 END inv_lot_apis;