[Home] [Help]
PACKAGE BODY: APPS.INV_LOT_ATTR_PUB
Source
1 PACKAGE BODY INV_LOT_ATTR_PUB AS
2 /* $Header: INVVLOTB.pls 120.0 2005/05/25 06:48:51 appldev noship $ */
3
4 -----------------------------------------------------------------------
5 -- Name : validate_grade_code
6 -- Desc :
7 -- Generic routine to validates a grade code , item
8 -- must be grade controlled and grade must exist in
9 -- mtl_grades table
10 -- This function assumes that validation for itemid and orgin
11 -- have already taken place
12 -- I/P params :
13 -- p_grade_code, p_org_id and p_inventory_item_id (Mandatory)
14 -- p_grade_control_flag (optional, Derived if not given )
15 -----------------------------------------------------------------------
16
17 FUNCTION validate_grade_code(
18 p_grade_code IN VARCHAR
19 , p_org_id IN NUMBER
20 , p_inventory_item_id IN NUMBER
21 , p_grade_control_flag IN VARCHAR2
22 , x_return_status OUT NOCOPY VARCHAR2
23 , x_msg_count OUT NOCOPY NUMBER
24 , x_msg_data OUT NOCOPY VARCHAR2)
25 RETURN BOOLEAN
26 IS
27 l_grade_code VARCHAR2(150);
28 l_grade_control_flag VARCHAR2(1);
29
30 CURSOR c_get_grade_code IS
31 SELECT grade_code
32 FROM mtl_grades
33 WHERE Grade_code = p_grade_code;
34
35 CURSOR c_get_grade_flag IS
36 SELECT grade_control_flag
37 FROM mtl_system_items
38 WHERE inventory_item_id = p_inventory_item_id
39 AND organization_id = p_org_id;
40
41
42 BEGIN
43 x_return_status := fnd_api.g_ret_sts_success;
44 l_grade_control_flag := p_grade_control_flag;
45 /* get grade controlled flag */
46 IF p_grade_control_flag IS NULL
47 THEN
48 OPEN c_get_grade_flag;
49 FETCH c_get_grade_flag INTO l_grade_control_flag;
50 close c_get_grade_flag;
51 END IF;
52
53 IF l_grade_control_flag = 'Y' AND p_grade_code IS NULL
54 THEN
55 fnd_message.set_name('INV', 'INV_MISSING_GRADE');
56 fnd_msg_pub.ADD;
57 RAISE fnd_api.g_exc_error;
58 RETURN FALSE;
59 END IF;
60 /* Do validation only if item is grade controlled */
61 IF l_grade_control_flag = 'Y'
62 THEN
63 OPEN c_get_grade_code;
64 FETCH c_get_grade_code INTO l_grade_code;
65
66 IF c_get_grade_code%NOTFOUND THEN
67 CLOSE c_get_grade_code;
68 fnd_message.set_name('INV', 'INV_INVALID_GRADE_CODE_EXP');
69 fnd_message.set_token('GRADE_CODE', p_grade_code);
70 fnd_msg_pub.ADD;
71 RAISE fnd_api.g_exc_error;
72 RETURN FALSE;
73 END IF;
74 CLOSE c_get_grade_code;
75 END IF;
76
77 RETURN TRUE;
78 EXCEPTION
79 WHEN NO_DATA_FOUND THEN
80 x_return_status := fnd_api.g_ret_sts_error;
81
82 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
83 p_count => x_msg_count,
84 p_data => x_msg_data);
85 if( x_msg_count > 1 ) then
86 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE);
87 end if;
88 RETURN FALSE;
89
90 WHEN OTHERS THEN
91 x_return_status := fnd_api.g_ret_sts_error;
92
93 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
94 p_count => x_msg_count,
95 p_data => x_msg_data);
96 if( x_msg_count > 1 ) then
97 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE);
98 end if;
99 RETURN FALSE;
100 END validate_grade_code;
101
102
103 -----------------------------------------------------------------------
104 -- Name : validate_maturity_date
105 -- Desc :
106 -- Generic routine to validates maturity date.
107 -- Maturity Date must be greater than the Origination Date
108 --
109 -- This function assumes that validation for origination date
110 -- have already taken place
111 -- I/P params :
112 -- p_maturity_date, p_origination_date (Mandatory)
113
114 -- Modified the procedure to replace condition '<=' to '<'
115 -- in order to allow these dates to be eqaual to origination date.
116
117 -----------------------------------------------------------------------
118
119 FUNCTION validate_maturity_date(
120 p_maturity_date IN DATE
121 , p_origination_date IN DATE
122 , x_return_status OUT NOCOPY VARCHAR2
123 , x_msg_count OUT NOCOPY NUMBER
124 , x_msg_data OUT NOCOPY VARCHAR2)
125 RETURN BOOLEAN
126 IS
127 BEGIN
128 x_return_status := fnd_api.g_ret_sts_success;
129 IF (p_maturity_date IS NOT NULL AND p_origination_date IS NOT NULL)
130 THEN
131 IF (p_maturity_date < p_origination_date) THEN
132 fnd_message.set_name ('INV','INV_LOT_MATURITY_DATE_INVALID');
133 fnd_msg_pub.ADD;
134 RAISE fnd_api.g_exc_error;
135 RETURN TRUE;
136 END IF;
137 END IF;
138 RETURN TRUE;
139 EXCEPTION
140 WHEN OTHERS THEN
141 x_return_status := fnd_api.g_ret_sts_error;
142
143 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
144 p_count => x_msg_count,
145 p_data => x_msg_data);
146 if( x_msg_count > 1 ) then
147 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE);
148 end if;
149 RETURN FALSE;
150 END validate_maturity_date;
151
152
153 -----------------------------------------------------------------------
154 -- Name : validate_hold_date
155 -- Desc :
156 -- Generic routine to validates maturity date.
157 -- Maturity Date must be greater than the Origination Date
158 --
159 -- This function assumes that validation for origination date
160 -- have already taken place
161 -- I/P params :
162 -- p_maturity_date, p_origination_date (Mandatory)
163
164 -- Modified the procedure to replace condition '<=' to '<'
165 -- in order to allow these dates to be eqaual to origination date.
166
167 -----------------------------------------------------------------------
168
169 FUNCTION validate_hold_date(
170 p_hold_date IN DATE
171 , p_origination_date IN DATE
172 , x_return_status OUT NOCOPY VARCHAR2
173 , x_msg_count OUT NOCOPY NUMBER
174 , x_msg_data OUT NOCOPY VARCHAR2)
175 RETURN BOOLEAN
176 IS
177 BEGIN
178 x_return_status := fnd_api.g_ret_sts_success;
179 IF (p_hold_date IS NOT NULL AND p_origination_date IS NOT NULL)
180 THEN
181 IF (p_hold_date < p_origination_date) THEN
182 fnd_message.set_name ('INV','INV_LOT_HOLD_DATE_INVALID');
183 fnd_msg_pub.ADD;
184 RAISE fnd_api.g_exc_error;
185 RETURN TRUE;
186 END IF;
187 END IF;
188 RETURN TRUE;
189 EXCEPTION
190 WHEN OTHERS THEN
191 x_return_status := fnd_api.g_ret_sts_error;
192
193 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
194 p_count => x_msg_count,
195 p_data => x_msg_data);
196 if( x_msg_count > 1 ) then
197 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE);
198 end if;
199 RETURN FALSE;
200 END validate_hold_date;
201
202 -----------------------------------------------------------------------
203 -- Name : validate_expiration_action_date
204 -- Desc :
205 -- Generic routine to validate expiration_action_date
206 --
207 --
208 -- This function assumes that validation for expiration date
209 -- have already taken place
210 -- I/P params :
211 -- p_expiration_action_date, p_expiration_date (Mandatory)
212 -----------------------------------------------------------------------
213 FUNCTION validate_exp_action_date(
214 p_expiration_action_date IN DATE
215 , p_expiration_date IN DATE
216 , x_return_status OUT NOCOPY VARCHAR2
217 , x_msg_count OUT NOCOPY NUMBER
218 , x_msg_data OUT NOCOPY VARCHAR2)
219 RETURN BOOLEAN
220 IS
221 BEGIN
222 x_return_status := fnd_api.g_ret_sts_success;
223 IF (p_expiration_action_date IS NOT NULL) THEN
224 IF (p_expiration_date IS NOT NULL) THEN
225 -- fabdi bug 4168662, removed validations
226 /*
227 IF (p_expiration_action_date <= p_expiration_date) THEN
228 fnd_message.set_name ('INV','INV_LOT_EXP_ACT_DATE_INVALID');
229 fnd_msg_pub.ADD;
230 RAISE fnd_api.g_exc_error;
231 RETURN FALSE;
232 END IF;
233
234 */
235 RETURN TRUE;
236 ELSE
237 fnd_message.set_name ('INV', 'INV_LOT_EXPIRE_DATE_REQ_EXP');
238 fnd_msg_pub.ADD;
239 RAISE fnd_api.g_exc_error;
240 RETURN FALSE;
241 END IF;
242 END IF;
243 RETURN TRUE;
244 EXCEPTION
245 WHEN OTHERS THEN
246 x_return_status := fnd_api.g_ret_sts_error;
247
248 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
249 p_count => x_msg_count,
250 p_data => x_msg_data);
251 if( x_msg_count > 1 ) then
252 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE);
253 end if;
254 RETURN FALSE;
255 END validate_exp_action_date;
256
257 -----------------------------------------------------------------------
258 -- Name : validate_retest_date
259 -- Desc :
260 -- Generic routine to validates retest date.
261 -- Retest Date must be greater than the Origination Date
262 --
263 -- This function assumes that validation for origination date
264 -- have already taken place
265 -- I/P params :
266 -- p_maturity_date, p_origination_date (Mandatory)
267
268 -- Modified the procedure to replace condition '<=' to '<'
269 -- in order to allow these dates to be eqaual to origination date.
270 -----------------------------------------------------------------------
271
272 FUNCTION validate_retest_date(
273 p_retest_date IN DATE
274 , p_origination_date IN DATE
275 , x_return_status OUT NOCOPY VARCHAR2
276 , x_msg_count OUT NOCOPY NUMBER
277 , x_msg_data OUT NOCOPY VARCHAR2)
278 RETURN BOOLEAN
279 IS
280 BEGIN
281 x_return_status := fnd_api.g_ret_sts_success;
282 IF (p_retest_date IS NOT NULL AND p_origination_date IS NOT NULL)
283 THEN
284 IF (p_retest_date < p_origination_date) THEN
285 fnd_message.set_name ('INV','INV_LOT_RETEST_DATE_INVALID');
286 fnd_msg_pub.ADD;
287 RAISE fnd_api.g_exc_error;
288 RETURN TRUE;
289 END IF;
290 END IF;
291 RETURN TRUE;
292 EXCEPTION
293 WHEN OTHERS THEN
294 x_return_status := fnd_api.g_ret_sts_error;
295
296 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
297 p_count => x_msg_count,
298 p_data => x_msg_data);
299 if( x_msg_count > 1 ) then
300 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE);
301 end if;
302 RETURN FALSE;
303 END validate_retest_date;
304
305 -----------------------------------------------------------------------
306 -- Name : validate_exp_action_code
307 -- Desc :
308 -- Generic routine to validates Expiration Action Code , item
309 -- must be shlef life controlled, and Action Code must exist in
310 -- mtl_actions table
311 -- I/P params :
312 -- p_expiration_action_code , item ID and Org ID (Mandatory)
313 -- p_shelf_life_code (optional..)
314 -----------------------------------------------------------------------
315 FUNCTION validate_exp_action_code(
316 p_expiration_action_code IN VARCHAR
317 , p_org_id IN NUMBER
318 , p_inventory_item_id IN NUMBER
319 , p_shelf_life_code IN VARCHAR2
320 , x_return_status OUT NOCOPY VARCHAR2
321 , x_msg_count OUT NOCOPY NUMBER
322 , x_msg_data OUT NOCOPY VARCHAR2)
323 RETURN BOOLEAN
324 IS
325 l_expiration_action_code VARCHAR2(32);
326 l_shelf_life_code NUMBER;
327
328 /* get expiration action code */
329 CURSOR c_get_exp_action_code IS
330 SELECT action_code
331 FROM mtl_actions
332 WHERE action_code = p_expiration_action_code
333 AND NVL(disable_flag,'N') = 'N';
334
335 /* get shelf life code */
336 CURSOR c_get_shelf_life_code IS
337 SELECT shelf_life_code
338 FROM mtl_system_items
339 WHERE inventory_item_id = p_inventory_item_id
340 AND organization_id = p_org_id;
341
342
343 BEGIN
344 x_return_status := fnd_api.g_ret_sts_success;
345 l_shelf_life_code := p_shelf_life_code;
346 /* get shelf life code flag */
347 IF p_shelf_life_code IS NULL
348 THEN
349 OPEN c_get_shelf_life_code;
350 FETCH c_get_shelf_life_code INTO l_shelf_life_code;
351 close c_get_shelf_life_code;
352 END IF;
353
354 /* Do validation only if item is shelf life ctl*/
355 IF ((l_shelf_life_code <> 1) AND (p_expiration_action_code IS NOT NULL))
356 THEN
357 OPEN c_get_exp_action_code;
358 FETCH c_get_exp_action_code INTO l_expiration_action_code;
359
360 IF c_get_exp_action_code%NOTFOUND THEN
361 CLOSE c_get_exp_action_code;
362 fnd_message.set_name('INV', 'INV_EXP_ACTION_CD_EXP');
363 fnd_message.set_token('EXP_ACTION_CD', p_expiration_action_code);
364 fnd_msg_pub.ADD;
365 RAISE fnd_api.g_exc_error;
366 RETURN FALSE;
367 END IF;
368 CLOSE c_get_exp_action_code;
369 END IF;
370
371 RETURN TRUE;
372 EXCEPTION
373 WHEN NO_DATA_FOUND THEN
374 x_return_status := fnd_api.g_ret_sts_error;
375
376 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
377 p_count => x_msg_count,
378 p_data => x_msg_data);
379 if( x_msg_count > 1 ) then
380 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE);
381 end if;
382 RETURN FALSE;
383
384 WHEN OTHERS THEN
385 x_return_status := fnd_api.g_ret_sts_error;
386
387 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
388 p_count => x_msg_count,
389 p_data => x_msg_data);
390 if( x_msg_count > 1 ) then
391 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE);
392 end if;
393 RETURN FALSE;
394 END validate_exp_action_code;
395
396 -----------------------------------------------------------------------
397 -- Name : validate_reason_code
398 -- Desc :
399 -- Generic routine to validate reason code/ reason id
400 -- Must exist in MTL_TRANSACTION_REASONS
401 --
402 -- I/P params :
403 -- p_reason_code OR p_resson_id (Mandatory)
404 -----------------------------------------------------------------------
405 FUNCTION validate_reason_code(
406 p_reason_code IN VARCHAR2
407 , p_reason_id IN NUMBER
408 , x_return_status OUT NOCOPY VARCHAR2
409 , x_msg_count OUT NOCOPY NUMBER
410 , x_msg_data OUT NOCOPY VARCHAR2)
411 RETURN BOOLEAN
412 IS
413 l_reason_code VARCHAR2(32);
414 l_reason_id NUMBER;
415
416 /* Get reason code info */
417 CURSOR c_get_reason_id IS
418 SELECT MTR.REASON_ID
419 FROM MTL_TRANSACTION_REASONS MTR
420 WHERE MTR.REASON_ID = p_reason_id
421 AND NVL(MTR.DISABLE_DATE, SYSDATE + 1) > SYSDATE;
422
423 /* Get reason code info */
424 CURSOR c_get_reason_code IS
425 SELECT MTR.REASON_ID
426 FROM MTL_TRANSACTION_REASONS MTR
427 WHERE MTR.REASON_NAME = p_reason_code
428 AND NVL(MTR.DISABLE_DATE, SYSDATE + 1) > SYSDATE;
429
430 BEGIN
431 x_return_status := fnd_api.g_ret_sts_success;
432 l_reason_code := p_reason_code;
433 l_reason_id := p_reason_id;
434
435 IF p_reason_code IS NOT NULL
436 THEN
437 OPEN c_get_reason_code;
438 FETCH c_get_reason_code INTO l_reason_code;
439 IF c_get_reason_code%NOTFOUND THEN
440 CLOSE c_get_reason_code;
441 fnd_message.set_name('INV', 'INV_INT_REAEXP');
442 fnd_msg_pub.ADD;
443 RAISE fnd_api.g_exc_error;
444 RETURN FALSE;
445 END IF;
446 CLOSE c_get_reason_code;
447 Else
448
449 IF p_reason_id IS NOT NULL
450 THEN
451 OPEN c_get_reason_id;
452 FETCH c_get_reason_id INTO l_reason_code;
453 IF c_get_reason_id%NOTFOUND THEN
454 CLOSE c_get_reason_id;
455 fnd_message.set_name('INV', 'INV_INT_REAEXP');
456 fnd_msg_pub.ADD;
457 RAISE fnd_api.g_exc_error;
458 RETURN FALSE;
462 END IF;
459 END IF;
460 CLOSE c_get_reason_id;
461 END IF;
463
464 RETURN TRUE;
465 EXCEPTION
466 WHEN NO_DATA_FOUND THEN
467 x_return_status := fnd_api.g_ret_sts_error;
468
469 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
470 p_count => x_msg_count,
471 p_data => x_msg_data);
472 if( x_msg_count > 1 ) then
473 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE);
474 end if;
475 RETURN FALSE;
476
477 WHEN OTHERS THEN
478 x_return_status := fnd_api.g_ret_sts_error;
479
480 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
481 p_count => x_msg_count,
482 p_data => x_msg_data);
483 if( x_msg_count > 1 ) then
484 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE);
485 end if;
486 RETURN FALSE;
487 END validate_reason_code;
488
489 -----------------------------------------------------------------------
490 -- Name : validate_origination_type
491 -- Desc :
492 -- Generic routine to validate origination type
493 -- Must exist in mfg_lookups (lookup_type = 'ORIGINATION_TYPE')
494 --
495 -- I/P params :
496 -- p_origination_id (Mandatory)
497 -----------------------------------------------------------------------
498 FUNCTION validate_origination_type(
499 p_origination_type IN NUMBER
500 , x_return_status OUT NOCOPY VARCHAR2
501 , x_msg_count OUT NOCOPY NUMBER
502 , x_msg_data OUT NOCOPY VARCHAR2)
503 RETURN BOOLEAN
504 IS
505 l_origination_type NUMBER;
506
507 /* Origination Type validation logic */
508 CURSOR c_get_origination_type IS
509 SELECT lookup_code
510 FROM mfg_lookups
511 WHERE lookup_type = 'MTL_LOT_ORIGINATION_TYPE'
512 AND lookup_code = p_origination_type;
513
514 BEGIN
515 x_return_status := fnd_api.g_ret_sts_success;
516 IF p_origination_type IS NOT NULL
517 THEN
518 OPEN c_get_origination_type;
519 FETCH c_get_origination_type into l_origination_type;
520 IF c_get_origination_type%NOTFOUND THEN
521 CLOSE c_get_origination_type;
522 fnd_message.set_name('INV', 'INV_ORIGINATION_TYPE_EXP');
523 fnd_message.set_token('ORIGINATION_TYPE', p_origination_type);
524 fnd_msg_pub.ADD;
525 RAISE fnd_api.g_exc_error;
526 RETURN FALSE;
527 END IF;
528 CLOSE c_get_origination_type;
529 END IF;
530
531 RETURN TRUE;
532 EXCEPTION
533 WHEN NO_DATA_FOUND THEN
534 x_return_status := fnd_api.g_ret_sts_error;
535
536 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
537 p_count => x_msg_count,
538 p_data => x_msg_data);
539 if( x_msg_count > 1 ) then
540 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE);
541 end if;
542 RETURN FALSE;
543
544 WHEN OTHERS THEN
545 x_return_status := fnd_api.g_ret_sts_error;
546
547 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
548 p_count => x_msg_count,
549 p_data => x_msg_data);
550 if( x_msg_count > 1 ) then
551 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE);
552 end if;
553 RETURN FALSE;
554 END validate_origination_type;
555 -----------------------------------------------------------------------
556 -- Name : validate_child_lot
557 -- Desc :
558 -- Generic routine to validate lot number
559 -- Validation conditions
560 -- # child lot is new
561 -- # Item must be child lot enable
562 -- # Validate naming conventions if child Lot has an associated parent lot
563 -- # relations ship between parent/child lot is valid
564 --
565 -- I/P params :
566 -- p_parent_lot_number, p_lot_number , itemid, orgid (Mandatory)
567 -- p_child_lot_flag (optional)
568 -----------------------------------------------------------------------
569
570 FUNCTION validate_child_lot (
571 p_parent_lot_number IN VARCHAR2
572 , p_lot_number IN VARCHAR2
573 , p_org_id IN NUMBER
574 , p_inventory_item_id IN NUMBER
575 , p_child_lot_flag IN VARCHAR2
576 , x_return_status OUT NOCOPY VARCHAR2
577 , x_msg_count OUT NOCOPY NUMBER
578 , x_msg_data OUT NOCOPY VARCHAR2)
579 RETURN BOOLEAN
580 IS
581 l_parent_lot_number VARCHAR2(80);
582 l_lot_number VARCHAR2(80);
583 l_child_lot_flag VARCHAR2(1);
584
585 /* Get Lot record */
586 CURSOR c_get_lot_record IS
587 SELECT *
588 FROM mtl_lot_numbers
589 WHERE lot_number = p_lot_number
590 AND inventory_item_id = p_inventory_item_id
591 AND organization_id = p_org_id;
592
593 /* get child lot enabled flag */
594 CURSOR c_child_lot_flag IS
595 SELECT p_child_lot_flag
596 FROM mtl_system_items
597 WHERE inventory_item_id = p_inventory_item_id
598 AND organization_id = p_org_id;
599
600 l_api_version NUMBER;
601 l_init_msg_list VARCHAR2(100);
602 l_commit VARCHAR2(100);
603
604 l_return_status VARCHAR2(1);
605 l_msg_count NUMBER;
606 l_msg_data VARCHAR2(3000);
607
608 l_lot_record c_get_lot_record%ROWTYPE;
609 BEGIN
610 x_return_status := fnd_api.g_ret_sts_success;
611 l_api_version := 1.0;
615 IF l_child_lot_flag IS NULL
612 l_init_msg_list := fnd_api.g_false;
613 l_commit := fnd_api.g_false;
614
616 THEN
617 OPEN c_child_lot_flag;
618 FETCH c_child_lot_flag into l_child_lot_flag;
619 CLOSE c_child_lot_flag;
620 END IF;
621
622 IF (l_child_lot_flag = 'Y' and p_parent_lot_number IS NOT NULL)
623 THEN
624 OPEN c_get_lot_record;
625 FETCH c_get_lot_record INTO l_lot_record;
626 IF c_get_lot_record%NOTFOUND THEN
627 CLOSE c_get_lot_record;
628 INV_LOT_API_PUB.validate_child_lot (
629 x_return_status => l_return_status
630 , x_msg_count => l_msg_count
631 , x_msg_data => l_msg_data
632 , p_api_version => l_api_version
633 , p_init_msg_list => l_init_msg_list
634 , p_commit => l_commit
635 , p_organization_id => p_org_id
636 , p_inventory_item_id => p_inventory_item_id
637 , p_parent_lot_number => p_parent_lot_number
638 , p_child_lot_number => p_lot_number
639 ) ;
640 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
641 FND_MESSAGE.SET_NAME('INV','INV_PROGRAM_ERROR');
642 FND_MESSAGE.SET_TOKEN('PGM_NAME','INV_CHILD_LOT_GRP.VALIDATE_CHILD_LOT');
643 fnd_msg_pub.ADD;
644 RAISE fnd_api.g_exc_unexpected_error;
645 RETURN FALSE;
646 END IF;
647 IF l_return_status = fnd_api.g_ret_sts_error THEN
648 fnd_message.set_name('INV', 'INV_INVALID_CHILD_LOT_EXP') ;
649 fnd_msg_pub.ADD;
650 RAISE fnd_api.g_exc_error;
651 RETURN FALSE;
652 END IF;
653 ELSE
654 /* existing lot */
655 CLOSE c_get_lot_record;
656 /* Check Parent Lot is correct */
657 IF l_lot_record.parent_lot_number IS NOT NULL THEN
658 IF l_lot_record.parent_lot_number <> p_parent_lot_number THEN
659 fnd_message.set_name('INV', 'INV_INVALID_PARENT_LOT_EXP') ;
660 fnd_msg_pub.ADD;
661 RAISE fnd_api.g_exc_error;
662 RETURN FALSE;
663 END IF;
664 END IF;
665
666 END IF; -- end cursor check
667 END IF; -- main if
668
669
670 RETURN TRUE;
671 EXCEPTION
672 WHEN NO_DATA_FOUND THEN
673 x_return_status := fnd_api.g_ret_sts_error;
674
675 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
676 p_count => x_msg_count,
677 p_data => x_msg_data);
678 if( x_msg_count > 1 ) then
679 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE);
680 end if;
681 RETURN FALSE;
682
683 WHEN OTHERS THEN
684 x_return_status := fnd_api.g_ret_sts_error;
685
686 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
687 p_count => x_msg_count,
688 p_data => x_msg_data);
689 if( x_msg_count > 1 ) then
690 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE);
691 end if;
692 RETURN FALSE;
693 END validate_child_lot;
694
695 PROCEDURE create_lot_uom_conv_wrapper
696 ( p_commit IN VARCHAR2
697 , p_action_type IN VARCHAR2
698 , p_reason_id IN NUMBER
699 , p_lot_number IN VARCHAR2
700 , p_organization_id IN NUMBER
701 , p_inventory_item_id IN NUMBER
702 , p_from_unit_of_measure IN VARCHAR2
703 , p_from_uom_code IN VARCHAR2
704 , p_from_uom_class IN VARCHAR2
705 , p_to_unit_of_measure IN VARCHAR2
706 , p_to_uom_code IN VARCHAR2
707 , p_to_uom_class IN VARCHAR2
708 , p_conversion_rate IN NUMBER
709 , p_disable_date IN DATE
710 , p_event_spec_disp_id IN NUMBER
711 , p_created_by IN NUMBER
712 , p_creation_date IN DATE
713 , p_last_updated_by IN NUMBER
714 , p_last_update_date IN DATE
715 , p_last_update_login IN NUMBER
716 , p_request_id IN NUMBER
717 , p_program_application_id IN NUMBER
718 , p_program_id IN NUMBER
719 , p_program_update_date IN DATE
720 , x_return_status OUT NOCOPY VARCHAR2
721 , x_msg_count OUT NOCOPY NUMBER
722 , x_msg_data OUT NOCOPY VARCHAR2
723 ) IS
724
725 l_return_status VARCHAR2(2);
726 l_msg_count NUMBER;
727 l_msg_data VARCHAR2(2000);
728
729 l_qty_tbl MTL_LOT_UOM_CONV_PUB.quantity_update_rec_type;
730 l_conv_rec MTL_LOT_UOM_CLASS_CONVERSIONS%ROWTYPE;
731 l_action_type VARCHAR2(1);
732 l_rtn NUMBER;
733 l_sequence NUMBER := 1;
734 BEGIN
735
736 /* Call Business rule level validation */
737 l_rtn := MTL_LOT_UOM_CONV_PVT.validate_lot_conversion_rules
738 ( p_organization_id => p_organization_id
739 , p_inventory_item_id => p_inventory_item_id
740 , p_lot_number => p_lot_number
741 , p_from_uom_code => p_from_uom_code
742 , p_to_uom_code => p_to_uom_code
743 , p_quantity_updates => 'F'
744 , p_update_type => p_action_type
745 );
746
747 IF ( l_rtn = 0) THEN
748 -- dbms_output.put_line('Error: validate_lot_conversion_rules ' || l_msg_data);
749 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
753 l_conv_rec.lot_number := p_lot_number;
750 END IF;
751 --dbms_output.put_line('Ok: validate_lot_conversion_rules ');
752
754 l_conv_rec.organization_id := p_organization_id ;
755 l_conv_rec.inventory_item_id := p_inventory_item_id;
756 -- FROM
757 l_conv_rec.from_unit_of_measure := p_from_unit_of_measure;
758 l_conv_rec.from_uom_code := p_from_uom_code;
759 l_conv_rec.from_uom_class := p_from_uom_class;
760
761 -- TO
762 l_conv_rec.to_unit_of_measure := p_to_unit_of_measure;
763 l_conv_rec.to_uom_code := p_to_uom_code;
764 l_conv_rec.to_uom_class := p_to_uom_class;
765 l_conv_rec.conversion_rate := p_conversion_rate;
766
767 l_conv_rec.disable_date := p_disable_date;
768 l_conv_rec.event_spec_disp_id := p_event_spec_disp_id ;
769 l_conv_rec.created_by := p_created_by ;
770 l_conv_rec.creation_date := p_creation_date;
771
772 l_conv_rec.last_updated_by := p_last_updated_by ;
773 l_conv_rec.last_update_date := p_last_update_date;
774
775 l_conv_rec.last_update_login := p_last_update_login;
776 l_conv_rec.request_id := p_request_id;
777 l_conv_rec.program_application_id := p_program_application_id ;
778 l_conv_rec.program_id := p_program_id;
779 l_conv_rec.program_update_date := p_program_update_date;
780
781 l_action_type := p_action_type;-- 'I'
782
783 /*===================================
784 Insert/update conversion record.
785 ==================================*/
786
787
788 MTL_LOT_UOM_CONV_PUB.create_lot_uom_conversion
789 ( p_api_version => 1.0
790 , p_init_msg_list => FND_API.G_TRUE
791 , p_commit => FND_API.G_FALSE
792 , p_validation_level => FND_API.G_VALID_LEVEL_NONE
793 , p_action_type => l_action_type
794 , p_update_type_indicator => '5'
795 , p_reason_id => NULL
796 , p_batch_id => NULL
797 , p_process_data => FND_API.G_TRUE
798 , p_lot_uom_conv_rec => l_conv_rec
799 , p_qty_update_tbl => l_qty_tbl
800 , x_return_status => l_return_status
801 , x_msg_count => l_msg_count
802 , x_msg_data => l_msg_data
803 , x_sequence => l_sequence);
804
805 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
806 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
807 END IF;
808
809
810 -- dbms_output.put_line('Every thing is OK : create_lot_uom_conv_wrapper ');
811
812
813 EXCEPTION
814
815 WHEN FND_API.G_EXC_ERROR THEN
816 x_return_status := FND_API.G_RET_STS_ERROR;
817 FND_MSG_PUB.Count_AND_GET
818 (p_count => x_msg_count, p_data => x_msg_data);
819 -- dbms_output.put_line('Error : '|| l_msg_data );
820
821 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
822 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
823 FND_MSG_PUB.Count_AND_GET
824 (p_count => x_msg_count, p_data => x_msg_data);
825 -- dbms_output.put_line('Error : '|| l_msg_data );
826
827 WHEN OTHERS THEN
828 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
829 FND_MSG_PUB.Count_AND_GET
830 (p_count => x_msg_count, p_data => x_msg_data);
831 -- dbms_output.put_line('Error : '|| l_msg_data );
832
833 END create_lot_uom_conv_wrapper;
834
835 END INV_LOT_ATTR_PUB;