[Home] [Help]
PACKAGE BODY: APPS.INV_LOT_TRX_VALIDATION_PUB
Source
1 PACKAGE BODY inv_lot_trx_validation_pub AS
2 /* $Header: INVPLTVB.pls 120.14 2006/09/14 14:12:14 ramarava noship $ */
3 PROCEDURE print_debug (p_message IN VARCHAR2, p_module IN VARCHAR2)
4 IS
5 l_debug NUMBER;
6 BEGIN
7
8 l_debug := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
9
10 --dbms_output.put_line(g_pkg_name||'.'||p_module||': ' || p_message);
11 IF (l_debug = 1)
12 THEN
13 inv_log_util.TRACE (p_message, g_pkg_name || '.' || p_module, 9);
14 --dbms_output.put_line(substr(p_message,1,200));
15 END IF;
16 END print_debug;
17
18 /** This procedure gets the wms_installed_flag, wsm_enabled flag and wms_enabled flag **/
19 PROCEDURE get_org_info (
20 x_wms_installed OUT NOCOPY VARCHAR2
21 , x_wsm_enabled OUT NOCOPY VARCHAR2
22 , x_wms_enabled OUT NOCOPY VARCHAR2
23 , x_return_status OUT NOCOPY VARCHAR2
24 , x_msg_count OUT NOCOPY NUMBER
25 , x_msg_data OUT NOCOPY VARCHAR2
26 , p_organization_id IN NUMBER
27 )
28 IS
29 l_wms_installed VARCHAR2 (1);
30 l_debug NUMBER
31 := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
32 BEGIN
33 BEGIN
34 inv_txn_validations.check_wms_install
35 (x_return_status => x_wms_installed
36 , p_msg_count => x_msg_count
37 , p_msg_data => x_msg_data
38 , p_org => NULL
39 );
40 EXCEPTION
41 WHEN OTHERS
42 THEN
43 fnd_message.set_name ('WMS', 'WMS_INSTALL_CHK_ERROR');
44 fnd_msg_pub.ADD;
45 RAISE fnd_api.g_exc_unexpected_error;
46 END;
47
48 SELECT wsm_enabled_flag
49 INTO x_wsm_enabled
50 FROM mtl_parameters
51 WHERE organization_id = p_organization_id;
52
53 BEGIN
54 inv_txn_validations.check_wms_install
55 (x_return_status => x_wms_enabled
56 , p_msg_count => x_msg_count
57 , p_msg_data => x_msg_data
58 , p_org => p_organization_id
59 );
60 EXCEPTION
61 WHEN OTHERS
62 THEN
63 fnd_message.set_name ('WMS', 'WMS_INSTALL_CHK_ERROR');
64 fnd_msg_pub.ADD;
65 RAISE fnd_api.g_exc_unexpected_error;
66 END;
67
68 x_return_status := fnd_api.g_ret_sts_success;
69 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
70 EXCEPTION
71 WHEN NO_DATA_FOUND
72 THEN
73 fnd_message.set_name ('INV', 'INV_INVALID_ORG');
74 fnd_msg_pub.ADD;
75 x_return_status := fnd_api.g_ret_sts_error;
76 fnd_msg_pub.count_and_get (p_count => x_msg_count
77 , p_data => x_msg_data);
78 WHEN fnd_api.g_exc_error
79 THEN
80 x_return_status := fnd_api.g_ret_sts_error;
81 fnd_msg_pub.count_and_get (p_count => x_msg_count
82 , p_data => x_msg_data);
83 WHEN fnd_api.g_exc_unexpected_error
84 THEN
85 x_return_status := fnd_api.g_ret_sts_error;
86 fnd_msg_pub.count_and_get (p_count => x_msg_count
87 , p_data => x_msg_data);
88 WHEN OTHERS
89 THEN
90 x_return_status := fnd_api.g_ret_sts_unexp_error;
91
92 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
93 THEN
94 fnd_msg_pub.add_exc_msg (g_pkg_name, 'Get_Org_info');
95 END IF;
96
97 fnd_msg_pub.count_and_get (p_count => x_msg_count
98 , p_data => x_msg_data);
99 END get_org_info;
100
101 /*********************************************************************************************
102 * Validate Lots -- See documentation on the package specification. *
103 * Pseudo-code: *
104 * if( p_transaction_Type_id is NULL or p_st_lot_num_tbl.COUNT = 0 OR *
105 * p_rs_lot_num_tbl.COUNT = 0 or p_st_org_id_tbl.COUNT = 0 OR p_rs_org_id_tbl.COUNT=0 *
106 * OR p_st_item_id_tbl.COUNT=0 OR p_rs_item_id_tbl.COUNT=0 ) Then *
107 * Return error, missing required parameter *
108 * end if; *
109 * *
110 * l_start_count := p_st_lot_num_tbl.COUNT; *
111 * l_result_count := p_rs_lot_num_Tbl.COUNT; *
112 * *
113 * if( this is a lot split transactions ) THEN *
114 * if l_start_count > 1 then *
115 * return too many starting lot error *
116 * end if; *
117 * if l_result_Count < 2 then *
118 * return too few resulting lot error *
119 * end if; *
120 * For each resulting lots LOOP *
121 * If result lot org Id <> starting lot org id then *
122 * Return error different org id *
123 * End if; *
124 * If item id of the result lot <> item id of the start lot then *
125 * Return different item error *
126 * End if; *
127 * End loop; *
128 * Else if this is a lot merge transactions then *
129 * If( l_start_count < 2 ) then *
130 * Return too few starting lot error *
131 * End if; *
132 * If l_result_count > 2 ) then *
133 * Return too many result lot error *
134 * End if; *
135 * For each start lot loop *
136 * If org_id is current start lot different from org id of *
137 * the previous start lot then *
138 * Return different org id error *
139 * End if; *
140 * If org id of current start lot different from org id of result lot then *
141 * Return different org id error. *
142 * End if; *
143 * If item id of current start lot different from item id of the previous *
144 * Start lot then *
145 * Return different item id error *
146 * End if; *
147 * If item id of current start lot different from item id of result lot then *
148 * Return different item id error *
149 * End if; *
150 * End loop; *
151 * Else if this is a lot translate transaction then *
152 * If l_start_count > 1 then *
153 * Return too many starting lot error *
154 * End if; *
155 * If l_result_Count > 1 then *
156 * Return too many result lot error *
157 * End if; *
158 * If org id of start lot different from org id of result lot then *
159 * Return different org id error *
160 * End if; *
161 * Call validate_lot_translate *
162 * Return error if validate_lot_translate errored out. *
163 * End if; *
164 *********************************************************************************************/
165 PROCEDURE validate_lots (
166 x_return_status OUT NOCOPY VARCHAR2
167 , x_msg_count OUT NOCOPY NUMBER
168 , x_msg_data OUT NOCOPY VARCHAR2
169 , x_validation_status OUT NOCOPY VARCHAR2
170 , p_transaction_type_id IN NUMBER
171 , p_st_org_id_tbl IN number_table
172 , p_rs_org_id_tbl IN number_table
173 , p_st_item_id_tbl IN number_table
174 , p_rs_item_id_tbl IN number_table
175 , p_st_lot_num_tbl IN lot_number_table
176 , p_rs_lot_num_tbl IN lot_number_table
177 , p_st_revision_tbl IN revision_table
178 , p_rs_revision_tbl IN revision_table
179 , p_st_quantity_tbl IN number_table
180 , p_rs_quantity_tbl IN number_table
181 , p_st_lot_exp_tbl IN date_table
182 , p_rs_lot_exp_tbl IN date_table
183 )
184 IS
185 l_start_count NUMBER;
186 l_result_count NUMBER;
187 l_st_lot_control_code NUMBER;
188 l_st_serial_control_code NUMBER;
189 l_rs_lot_control_code NUMBER;
190 l_rs_serial_control_code NUMBER;
191 l_debug NUMBER
192 := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
193 BEGIN
194 IF (l_debug = 1)
195 THEN
196 print_debug ('Inside Validate_Lots', 'Validate_Lots');
197 print_debug ('p_transaction_type_id is ' || p_transaction_type_id
198 , 'Validate_lots'
199 );
200 print_debug ('p_st_org_id_tbl.COUNT is ' || p_st_org_id_tbl.COUNT
201 , 'Validate_lots'
202 );
203 print_debug ('p_rs_org_id_tbl.COUNT is ' || p_rs_org_id_tbl.COUNT
204 , 'Validate_lots'
205 );
206 print_debug ('p_st_item_id_tbl.COUNT is ' || p_st_item_id_tbl.COUNT
207 , 'Validate_lots'
208 );
209 print_debug ('p_rs_item_id_tbl.COUNT is ' || p_rs_item_id_tbl.COUNT
210 , 'Validate_lots'
211 );
212 print_debug ('p_st_lot_num_tbl.COUNT is ' || p_st_lot_num_tbl.COUNT
213 , 'Validate_lots'
214 );
215 print_debug ('p_rs_lot_num_tbl.COUNT is ' || p_rs_lot_num_tbl.COUNT
216 , 'Validate_lots'
217 );
218 END IF;
219
220 IF ( p_transaction_type_id IS NULL
221 OR p_st_lot_num_tbl.COUNT = 0
222 OR p_st_lot_num_tbl IS NULL
223 OR p_rs_lot_num_tbl.COUNT = 0
224 OR p_rs_lot_num_tbl IS NULL
225 )
226 THEN
227 x_validation_status := 'N';
228 fnd_message.set_name ('INV', 'INV_MISSING_REQUIRED_PARAMETER');
229 fnd_msg_pub.ADD;
230 RAISE fnd_api.g_exc_error;
231 END IF;
232
233 l_start_count := p_st_lot_num_tbl.COUNT;
234 l_result_count := p_rs_lot_num_tbl.COUNT;
235
236 IF (l_debug = 1)
237 THEN
238 print_debug ('l_start_count is ' || l_start_count, 'Validate_Lots');
239 print_debug ('l_result_count is ' || l_result_count, 'Validate_Lots');
240 END IF;
241
242 IF (p_transaction_type_id = inv_globals.g_type_inv_lot_split)
243 THEN
244 IF (l_debug = 1)
245 THEN
246 print_debug ('validate for lot split', 'Validate_Lots');
247 END IF;
248
249 IF (l_start_count > 1)
250 THEN
251 IF (l_debug = 1)
252 THEN
253 print_debug ('INV_TOO_MANY_LOT_SPLIT', 'Validate_Lots');
254 END IF;
255
256 fnd_message.set_name ('INV', 'INV_TOO_MANY_LOT_SPLIT');
257 fnd_msg_pub.ADD;
258 x_validation_status := 'N';
259 RAISE fnd_api.g_exc_error;
260 END IF;
261
262 IF (l_result_count < 2)
263 THEN
264 IF (p_st_quantity_tbl (1) <= p_rs_quantity_tbl (1))
265 THEN
266 -- means this is not a partial split.
267 fnd_message.set_name ('INV', 'INV_MIN_LOT_SPLIT');
268 fnd_msg_pub.ADD;
269 x_validation_status := 'N';
270 RAISE fnd_api.g_exc_error;
271 END IF;
272 END IF;
273
274 FOR i IN 1 .. l_result_count
275 LOOP
276 IF (p_rs_org_id_tbl (i) <> p_st_org_id_tbl (1))
277 THEN
278 fnd_message.set_name ('INV', 'INV_LOT_TRX_DIFF_ORG');
279 fnd_msg_pub.ADD;
280 x_validation_status := 'N';
281 RAISE fnd_api.g_exc_error;
282 END IF;
283
284 IF (p_rs_item_id_tbl (i) <> p_st_item_id_tbl (1))
285 THEN
286 fnd_message.set_name ('INV', 'INV_LOT_TRX_DIFF_ITEM');
287 fnd_msg_pub.ADD;
288 x_validation_status := 'N';
289 RAISE fnd_api.g_exc_error;
290 END IF;
291
292 IF (NVL (p_rs_revision_tbl (i), 'NULL') <>
293 NVL (p_rs_revision_tbl (1), 'NULL')
294 )
295 THEN
296 fnd_message.set_name ('INV', 'INV_LOT_TRX_DIFF_REVISION');
297 fnd_msg_pub.ADD;
298 x_validation_status := 'N';
299 RAISE fnd_api.g_exc_error;
300 END IF;
301
302 IF (NVL (TO_CHAR (p_rs_lot_exp_tbl (i), 'DD-MON-RRRR')
303 , TO_CHAR (SYSDATE, 'DD-MON-RRRR')
304 ) <>
305 NVL (TO_CHAR (p_st_lot_exp_tbl (1), 'DD-MON-RRRR')
306 , TO_CHAR (SYSDATE, 'DD-MON-RRRR')
307 )
308 )
309 THEN
310 fnd_message.set_name ('INV', 'INV_LOT_TRX_DIFF_LOT_EXP_DATE');
311 fnd_msg_pub.ADD;
312 x_validation_status := 'N';
313 RAISE fnd_api.g_exc_error;
314 END IF;
315 END LOOP;
316
317 --basically checks if lot split is allowed or not
318 validate_start_lot (x_return_status => x_return_status
319 , x_msg_count => x_msg_count
320 , x_msg_data => x_msg_data
321 , x_validation_status => x_validation_status
322 , p_transaction_type_id => p_transaction_type_id
323 , p_lot_number => p_st_lot_num_tbl (1)
324 , p_inventory_item_id => p_st_item_id_tbl (1)
325 , p_organization_id => p_st_org_id_tbl (1)
326 );
327
328 IF (x_return_status = fnd_api.g_ret_sts_error)
329 THEN
330 RAISE fnd_api.g_exc_error;
331 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
332 THEN
333 RAISE fnd_api.g_exc_unexpected_error;
334 END IF;
335
336 --for lot split checks for lot uniqueness....if across items then no other item
337 --should have this lot number
338 --if not across items then no lot should exists for this item also....
339 validate_result_lot (x_return_status => x_return_status
340 , x_msg_count => x_msg_count
341 , x_msg_data => x_msg_data
342 , x_validation_status => x_validation_status
343 , p_transaction_type_id => p_transaction_type_id
344 , p_st_lot_num_tbl => p_st_lot_num_tbl
345 , p_rs_lot_num_tbl => p_rs_lot_num_tbl
346 , p_inventory_item_id => p_rs_item_id_tbl (1)
347 , p_organization_id => p_rs_org_id_tbl (1)
348 );
349
350 IF (x_return_status = fnd_api.g_ret_sts_error)
351 THEN
352 RAISE fnd_api.g_exc_error;
353 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
354 THEN
355 RAISE fnd_api.g_exc_unexpected_error;
356 END IF;
357 ELSIF (p_transaction_type_id = inv_globals.g_type_inv_lot_translate)
358 THEN
359 IF (l_start_count > 1)
360 THEN
361 fnd_message.set_name ('INV', 'INV_MIN_START_LOT_TRANSLATE');
362 fnd_msg_pub.ADD;
363 x_validation_status := 'N';
364 RAISE fnd_api.g_exc_error;
365 END IF;
366
367 IF (l_result_count > 1)
368 THEN
369 fnd_message.set_name ('INV', 'INV_MIN_RESULT_LOT_TRANSLATE');
370 fnd_msg_pub.ADD;
371 x_validation_status := 'N';
372 RAISE fnd_api.g_exc_error;
373 END IF;
374
375 IF (p_rs_org_id_tbl (1) <> p_st_org_id_tbl (1))
376 THEN
377 fnd_message.set_name ('INV', 'INV_LOT_TRX_DIFF_ORG');
378 fnd_msg_pub.ADD;
379 x_validation_status := 'N';
380 RAISE fnd_api.g_exc_error;
381 END IF;
382
383 /*Changes for OSFM Support for Serialized Lot Items*/
384 IF (p_st_item_id_tbl (1) <> p_rs_item_id_tbl (1))
385 THEN
386 SELECT lot_control_code
387 , serial_number_control_code
388 INTO l_st_lot_control_code
389 , l_st_serial_control_code
390 FROM mtl_system_items
391 WHERE inventory_item_id = p_st_item_id_tbl (1)
392 AND organization_id = p_st_org_id_tbl (1);
393
394 SELECT lot_control_code
395 , serial_number_control_code
396 INTO l_rs_lot_control_code
397 , l_rs_serial_control_code
398 FROM mtl_system_items
399 WHERE inventory_item_id = p_rs_item_id_tbl (1)
400 AND organization_id = p_rs_org_id_tbl (1);
401
402 IF ( l_st_lot_control_code <> l_rs_lot_control_code
403 OR l_st_serial_control_code <> l_rs_serial_control_code
404 )
405 THEN
406 fnd_message.set_name ('INV', 'INV_LOT_SERIAL_CODE_DIFF');
407 fnd_msg_pub.ADD;
408 x_validation_status := 'N';
409 RAISE fnd_api.g_exc_error;
410 END IF;
411 END IF;
412
413
414 IF (l_debug = 1)
415 THEN
416 print_debug ('calling validate_lot_translate', 'Validate_lot');
417 END IF;
418
419 validate_lot_translate (x_return_status => x_return_status
420 , x_msg_count => x_msg_count
421 , x_msg_data => x_msg_data
422 , x_validation_status => x_validation_status
423 , p_start_lot_number => p_st_lot_num_tbl (1)
424 , p_start_inv_item_id => p_st_item_id_tbl (1)
425 , p_result_lot_number => p_rs_lot_num_tbl (1)
426 , p_result_inv_item_id => p_rs_item_id_tbl (1)
427 );
428
429 IF (l_debug = 1)
430 THEN
431 print_debug ('after calling validate_lot_translate '
432 || x_return_status
433 , 'Validate_lots'
434 );
435 END IF;
436
437 IF (x_return_status = fnd_api.g_ret_sts_error)
438 THEN
439 RAISE fnd_api.g_exc_error;
440 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
441 THEN
442 RAISE fnd_api.g_exc_unexpected_error;
443 END IF;
444
445 validate_start_lot (x_return_status => x_return_status
446 , x_msg_count => x_msg_count
447 , x_msg_data => x_msg_data
448 , x_validation_status => x_validation_status
449 , p_transaction_type_id => p_transaction_type_id
450 , p_lot_number => p_st_lot_num_tbl (1)
451 , p_inventory_item_id => p_st_item_id_tbl (1)
452 , p_organization_id => p_st_org_id_tbl (1)
453 );
454
455 IF (x_return_status = fnd_api.g_ret_sts_error)
456 THEN
457 RAISE fnd_api.g_exc_error;
458 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
459 THEN
460 RAISE fnd_api.g_exc_unexpected_error;
461 END IF;
462
463 validate_result_lot (x_return_status => x_return_status
464 , x_msg_count => x_msg_count
465 , x_msg_data => x_msg_data
466 , x_validation_status => x_validation_status
467 , p_transaction_type_id => p_transaction_type_id
468 , p_st_lot_num_tbl => p_st_lot_num_tbl
469 , p_rs_lot_num_tbl => p_rs_lot_num_tbl
470 , p_inventory_item_id => p_rs_item_id_tbl (1)
471 , p_organization_id => p_rs_org_id_tbl (1)
472 );
473
474 IF (x_return_status = fnd_api.g_ret_sts_error)
475 THEN
476 RAISE fnd_api.g_exc_error;
477 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
478 THEN
479 RAISE fnd_api.g_exc_unexpected_error;
480 END IF;
481 ELSIF (p_transaction_type_id = inv_globals.g_type_inv_lot_merge)
482 THEN
483 IF (l_start_count < 2)
484 THEN
485 fnd_message.set_name ('INV', 'INV_MIN_START_LOT_MERGE');
486 fnd_msg_pub.ADD;
487 x_validation_status := 'N';
488 RAISE fnd_api.g_exc_error;
489 END IF;
490
491 IF (l_result_count > 1)
492 THEN
493 fnd_message.set_name ('INV', 'INV_MAX_RESULT_LOT_MERGE');
494 fnd_msg_pub.ADD;
495 x_validation_status := 'N';
496 RAISE fnd_api.g_exc_error;
497 END IF;
498
499 FOR i IN 1 .. l_start_count
500 LOOP
501 IF (i < l_start_count)
502 THEN
503 IF (p_st_org_id_tbl (i) <> p_st_org_id_tbl (i + 1))
504 THEN
505 fnd_message.set_name ('INV', 'INV_LOT_TRX_DIFF_ORG');
506 fnd_msg_pub.ADD;
507 x_validation_status := 'N';
508 RAISE fnd_api.g_exc_error;
509 END IF;
510 END IF;
511
512 IF (p_st_org_id_tbl (i) <> p_rs_org_id_tbl (1))
513 THEN
514 fnd_message.set_name ('INV', 'INV_LOT_TRX_DIFF_ORG');
515 fnd_msg_pub.ADD;
516 x_validation_status := 'N';
517 RAISE fnd_api.g_exc_error;
518 END IF;
519
520 IF (i < l_start_count)
521 THEN
522 IF (p_st_item_id_tbl (i) <> p_st_item_id_tbl (i + 1))
523 THEN
524 fnd_message.set_name ('INV', 'INV_LOT_TRX_DIFF_ITEM');
525 fnd_msg_pub.ADD;
526 x_validation_status := 'N';
527 RAISE fnd_api.g_exc_error;
528 END IF;
529 END IF;
530
531 IF (p_st_item_id_tbl (i) <> p_rs_item_id_tbl (1))
532 THEN
533 fnd_message.set_name ('INV', 'INV_LOT_TRX_DIFF_ITEM');
534 fnd_msg_pub.ADD;
535 x_validation_status := 'N';
536 RAISE fnd_api.g_exc_error;
537 END IF;
538
539 FOR j IN 1 .. l_start_count
540 LOOP
541 IF (i <> j)
542 THEN
543 IF (p_st_lot_num_tbl (i) = p_st_lot_num_tbl (j))
544 THEN
545 IF (l_debug = 1)
546 THEN
547 print_debug ('Duplicate Lot', 'Validate_lots');
548 END IF;
549
550 fnd_message.set_name ('INV', 'INV_DUPLICATE_LOT');
551 fnd_msg_pub.ADD;
552 x_validation_status := 'N';
553 RAISE fnd_api.g_exc_error;
554 END IF;
555 END IF;
556 END LOOP;
557
558 validate_start_lot (x_return_status => x_return_status
559 , x_msg_count => x_msg_count
560 , x_msg_data => x_msg_data
561 , x_validation_status => x_validation_status
562 , p_transaction_type_id => p_transaction_type_id
563 , p_lot_number => p_st_lot_num_tbl (i)
564 , p_inventory_item_id => p_st_item_id_tbl (i)
565 , p_organization_id => p_st_org_id_tbl (i)
566 );
567
568 IF (x_return_status = fnd_api.g_ret_sts_error)
569 THEN
570 RAISE fnd_api.g_exc_error;
571 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
572 THEN
573 RAISE fnd_api.g_exc_unexpected_error;
574 END IF;
575 END LOOP;
576
577 validate_result_lot (x_return_status => x_return_status
578 , x_msg_count => x_msg_count
579 , x_msg_data => x_msg_data
580 , x_validation_status => x_validation_status
581 , p_transaction_type_id => p_transaction_type_id
582 , p_st_lot_num_tbl => p_st_lot_num_tbl
583 , p_rs_lot_num_tbl => p_rs_lot_num_tbl
584 , p_inventory_item_id => p_rs_item_id_tbl (1)
585 , p_organization_id => p_rs_org_id_tbl (1)
586 );
587
588 IF (x_return_status = fnd_api.g_ret_sts_error)
589 THEN
590 RAISE fnd_api.g_exc_error;
591 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
592 THEN
593 RAISE fnd_api.g_exc_unexpected_error;
594 END IF;
595 END IF;
596
597 x_validation_status := 'Y';
598 x_return_status := fnd_api.g_ret_sts_success;
599 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
600 EXCEPTION
601 WHEN fnd_api.g_exc_error
602 THEN
603 x_validation_status := 'N';
604 x_return_status := fnd_api.g_ret_sts_error;
605 fnd_msg_pub.count_and_get (p_count => x_msg_count
606 , p_data => x_msg_data);
607 WHEN fnd_api.g_exc_unexpected_error
608 THEN
609 x_validation_status := 'N';
610 x_return_status := fnd_api.g_ret_sts_unexp_error;
611 fnd_msg_pub.count_and_get (p_count => x_msg_count
612 , p_data => x_msg_data);
613 WHEN OTHERS
614 THEN
615 x_validation_status := 'N';
616 x_return_status := fnd_api.g_ret_sts_unexp_error;
617
618 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
619 THEN
620 fnd_msg_pub.add_exc_msg (g_pkg_name, 'Validate_Lots');
621 END IF;
622
623 fnd_msg_pub.count_and_get (p_count => x_msg_count
624 , p_data => x_msg_data);
625 END validate_lots;
626
627 /*********************************************************************************************
628 * Pseudo-code: *
629 * -- check if all the required parameter is there *
630 * if( p_transaction_Type_id is NULL or p_lot_number IS NULL OR p_organization_ID is NULL *
631 * OR p_inventory_item_id IS NULL ) Then *
632 * Return error, missing required parameter *
633 * end if; *
634 * *
635 * if( this is a lot split transactions ) THEN *
636 * Retrieve the lot_split_enabled flag for the item and lot *
637 * If no data found then *
638 * Return invalid item error *
639 * End if; *
640 * Else if this is a lot merge transactions then *
641 * Retrieve the lot_merge_enabled flag for the item and lot *
642 * If no data found then *
643 * Return invalid item error *
644 * End if; *
645 * Else if this is a lot translate transaction then *
646 * Retrieve the lot_control_code of the item and lot. *
647 * If the item is lot control then *
648 * Return 'Y' *
649 * Else *
650 * Return 'N' *
651 * End if; *
652 * End if; *
653 *********************************************************************************************/
654 PROCEDURE validate_start_lot (
655 x_return_status OUT NOCOPY VARCHAR2
656 , x_msg_count OUT NOCOPY NUMBER
657 , x_msg_data OUT NOCOPY VARCHAR2
658 , x_validation_status OUT NOCOPY VARCHAR2
659 , p_transaction_type_id IN NUMBER
660 , p_lot_number IN VARCHAR2
661 , p_inventory_item_id IN NUMBER
662 , p_organization_id IN NUMBER
663 )
664 IS
665 l_validation_status VARCHAR2 (1);
666 l_debug NUMBER
667 := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
668 BEGIN
669 IF (l_debug = 1)
670 THEN
671 print_debug ('Inside Validate Start Lot ', 'Validate_Start_Lot');
672 print_debug ('p_organization_id is ' || p_organization_id
673 , 'Validate_Start_lot'
674 );
675 print_debug ('p_inventory_item_id is ' || p_inventory_item_id
676 , 'Validate_Start_lot'
677 );
678 print_debug ('p_lot_number is ' || p_lot_number, 'Validate_Start_Lot');
679 print_debug ('p_transaction_Type_id is ' || p_transaction_type_id
680 , 'Validate_Start_Lot'
681 );
682 END IF;
683
684 IF ( p_transaction_type_id IS NULL
685 OR p_lot_number IS NULL
686 OR p_organization_id IS NULL
687 OR p_inventory_item_id IS NULL
688 )
689 THEN
690 fnd_message.set_name ('INV', 'INV_MISSING_REQUIRED_PARAMETER');
691 fnd_msg_pub.ADD;
692 RAISE fnd_api.g_exc_error;
693 END IF;
694
695 IF (p_transaction_type_id = inv_globals.g_type_inv_lot_split)
696 THEN
697 IF (l_debug = 1)
698 THEN
699 print_debug ('validate Start Lot for lot split'
700 , 'Validate_Start_Lot');
701 END IF;
702
703 BEGIN
704 SELECT msik.lot_split_enabled
705 INTO l_validation_status
706 FROM mtl_system_items_b msik, mtl_lot_numbers mln
707 WHERE mln.organization_id = p_organization_id
708 AND mln.inventory_item_id = p_inventory_item_id
709 AND mln.lot_number = p_lot_number
710 AND mln.organization_id = msik.organization_id
711 AND mln.inventory_item_id = msik.inventory_item_id;
712 EXCEPTION
713 WHEN NO_DATA_FOUND
714 THEN
715 BEGIN
716 SELECT msik.lot_split_enabled
717 INTO l_validation_status
718 FROM mtl_system_items_b msik
719 , mtl_transaction_lots_temp mtlt
720 , mtl_material_transactions_temp mmtt
721 WHERE mmtt.organization_id = p_organization_id
722 AND mmtt.inventory_item_id = p_inventory_item_id
723 AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
724 AND mtlt.lot_number = p_lot_number
725 AND mmtt.organization_id = msik.organization_id
726 AND mmtt.inventory_item_id = msik.inventory_item_id;
727 EXCEPTION
728 WHEN NO_DATA_FOUND
729 THEN
730 IF (l_debug = 1)
731 THEN
732 print_debug ('no data found in validate lot split'
733 , 'Validate_Start_lot'
734 );
735 END IF;
736
737 /* Bug:4405157. Modified the following message to be more specific to
738 Split transaction W.R.T Issue 15 of the bug*/
739 fnd_message.set_name ('INV', 'INV_INVALID_LOT_ITEM_LOT_SPLIT');
740 fnd_msg_pub.ADD;
741 RAISE fnd_api.g_exc_unexpected_error;
742 END;
743 END;
744 ELSIF (p_transaction_type_id = inv_globals.g_type_inv_lot_merge)
745 THEN
746 IF (l_debug = 1)
747 THEN
748 print_debug ('validate Start Lot for lot merge'
749 , 'Validate_Start_Lot');
750 END IF;
751
752 BEGIN
753 SELECT msik.lot_merge_enabled
754 INTO l_validation_status
755 FROM mtl_system_items_b msik, mtl_lot_numbers mln
756 WHERE mln.organization_id = p_organization_id
757 AND mln.inventory_item_id = p_inventory_item_id
758 AND mln.lot_number = p_lot_number
759 AND mln.organization_id = msik.organization_id
760 AND mln.inventory_item_id = msik.inventory_item_id;
761 EXCEPTION
762 WHEN NO_DATA_FOUND
763 THEN
764 BEGIN
765 SELECT msik.lot_merge_enabled
766 INTO l_validation_status
767 FROM mtl_system_items_b msik
768 , mtl_transaction_lots_temp mln
769 , mtl_material_transactions_temp mmtt
770 WHERE mmtt.organization_id = p_organization_id
771 AND mmtt.inventory_item_id = p_inventory_item_id
772 AND mmtt.transaction_temp_id = mln.transaction_temp_id
773 AND mln.lot_number = p_lot_number
774 AND mmtt.organization_id = msik.organization_id
775 AND mmtt.inventory_item_id = msik.inventory_item_id;
776 EXCEPTION
777 WHEN NO_DATA_FOUND
778 THEN
779 IF (l_debug = 1)
780 THEN
781 print_debug ('no data found in validate lot merge'
782 , 'Validate_Start_lot'
783 );
784 END IF;
785
786 /* Bug:4405157. Modified the following message to be more specific to
787 Merge transaction W.R.T Issue 15 of the bug*/
788 fnd_message.set_name ('INV', 'INV_INVALID_LOT_ITEM_LOT_MERGE');
789 fnd_msg_pub.ADD;
790 RAISE fnd_api.g_exc_unexpected_error;
791 END;
792 END;
793 ELSIF (p_transaction_type_id = inv_globals.g_type_inv_lot_translate)
794 THEN
795 IF (l_debug = 1)
796 THEN
797 print_debug ('validate Start Lot for lot translate'
798 , 'Validate_Start_Lot'
799 );
800 END IF;
801
802 BEGIN
803 SELECT DECODE (msik.lot_control_code, 2, 'Y', 'N')
804 INTO l_validation_status
805 FROM mtl_system_items_b msik, mtl_lot_numbers mln
806 WHERE mln.organization_id = p_organization_id
807 AND mln.inventory_item_id = p_inventory_item_id
808 AND mln.lot_number = p_lot_number
809 AND mln.organization_id = msik.organization_id
810 AND mln.inventory_item_id = msik.inventory_item_id;
811 EXCEPTION
812 WHEN NO_DATA_FOUND
813 THEN
814 BEGIN
815 SELECT DECODE (msik.lot_control_code, 2, 'Y', 'N')
816 INTO l_validation_status
817 FROM mtl_system_items_b msik
818 , mtl_transaction_lots_temp mln
819 , mtl_material_transactions_temp mmtt
820 WHERE mmtt.organization_id = p_organization_id
821 AND mmtt.inventory_item_id = p_inventory_item_id
822 AND mmtt.transaction_temp_id = mln.transaction_temp_id
823 AND mln.lot_number = p_lot_number
824 AND mmtt.organization_id = msik.organization_id
825 AND mmtt.inventory_item_id = msik.inventory_item_id;
826 EXCEPTION
827 WHEN NO_DATA_FOUND
828 THEN
829 IF (l_debug = 1)
830 THEN
831 print_debug ('no data found in validate lot translate'
832 , 'Validate_Start_lot'
833 );
834 END IF;
835 /* Bug:4405157. Modified the following message to be more specific to
836 Translate transaction W.R.T Issue 15 of the bug*/
837 fnd_message.set_name ('INV', 'INV_INVALID_LOT_ITEM_LOT_XLATE');
838 fnd_msg_pub.ADD;
839 RAISE fnd_api.g_exc_unexpected_error;
840 END;
841 END;
842 END IF;
843
844 x_return_status := fnd_api.g_ret_sts_success;
845 x_validation_status := l_validation_status;
846
847 /*Bug:4405157
848 /*Added new messages specific to lot split,merge and translate transactions
849 W.R.T to issue 15 of the bug*/
850 IF (x_validation_status <> 'Y')
851 THEN
852 IF (p_transaction_type_id = inv_globals.g_type_inv_lot_split)
853 THEN
854
855 fnd_message.set_name ('INV', 'INV_INVALID_LOT_ITEM_LOT_SPLIT');
856
857 ELSIF (p_transaction_type_id = inv_globals.g_type_inv_lot_merge)
858 THEN
859
860 fnd_message.set_name ('INV', 'INV_INVALID_LOT_ITEM_LOT_MERGE');
861
862 ELSIF (p_transaction_type_id = inv_globals.g_type_inv_lot_translate)
863 THEN
864
865 fnd_message.set_name ('INV', 'INV_INVALID_LOT_ITEM_LOT_XLATE');
866
867 ELSE
868 fnd_message.set_name ('INV', 'INV_INVALID_LOT');
869 END IF;
870 fnd_msg_pub.ADD;
871 RAISE fnd_api.g_exc_error;
872 END IF;
873
874 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
875 EXCEPTION
876 WHEN fnd_api.g_exc_error
877 THEN
878 x_validation_status := 'N';
879 x_return_status := fnd_api.g_ret_sts_error;
880 fnd_msg_pub.count_and_get (p_count => x_msg_count
881 , p_data => x_msg_data);
882 WHEN fnd_api.g_exc_unexpected_error
883 THEN
884 x_validation_status := 'N';
885 x_return_status := fnd_api.g_ret_sts_unexp_error;
886 fnd_msg_pub.count_and_get (p_count => x_msg_count
887 , p_data => x_msg_data);
888 WHEN OTHERS
889 THEN
890 x_validation_status := 'N';
891 x_return_status := fnd_api.g_ret_sts_unexp_error;
892
893 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
894 THEN
895 fnd_msg_pub.add_exc_msg (g_pkg_name, 'Validate_Start_Lot');
896 END IF;
897
898 fnd_msg_pub.count_and_get (p_count => x_msg_count
899 , p_data => x_msg_data);
900 END validate_start_lot;
901
902 /*********************************************************************************************
903 * Pseudo-code: *
904 * -- check if all the required parameter is there *
905 * if( p_transaction_Type_id is NULL or p_lot_number IS NULL OR p_organization_ID is NULL *
906 * OR p_inventory_item_id IS NULL ) Then *
907 * Return error, missing required parameter *
908 * end if; *
909 * *
910 * if( this is a lot split transactions ) THEN *
911 * Retrieve the lot_split_enabled flag for the item and lot *
912 * If no data found then *
913 * Return invalid item error *
914 * End if; *
915 * Else if this is a lot merge transactions then *
916 * Retrieve the lot_merge_enabled flag for the item and lot *
917 * If no data found then *
918 * Return invalid item error *
919 * End if; *
920 * Else if this is a lot translate transaction then *
921 * Retrieve the lot_control_code of the item and lot. *
922 * If the item is lot control then *
923 * Return 'Y' *
924 * Else *
925 * Return 'N' *
926 * End if; *
927 * End if; *
928 *********************************************************************************************/
929 PROCEDURE validate_result_lot (
930 x_return_status OUT NOCOPY VARCHAR2
931 , x_msg_count OUT NOCOPY NUMBER
932 , x_msg_data OUT NOCOPY VARCHAR2
933 , x_validation_status OUT NOCOPY VARCHAR2
934 , p_transaction_type_id IN NUMBER
935 , p_st_lot_num_tbl IN lot_number_table
936 , p_rs_lot_num_tbl IN lot_number_table
937 , p_inventory_item_id IN NUMBER
938 , p_organization_id IN NUMBER
939 )
940 IS
941 l_validation_status VARCHAR2 (1);
942 l_lot_uniqueness NUMBER;
943 l_lot_count NUMBER;
944 l_inventory_item_id NUMBER;
945 l_debug NUMBER
946 := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
947 BEGIN
948 IF (l_debug = 1)
949 THEN
950 print_debug ('Inside Validate_Result_lot', 'Validate_Result_Lot');
951 print_debug ('P_organization_id is ' || p_organization_id
952 , 'Validate_Result_Lot'
953 );
954 print_debug ('P_inventory_item_id is ' || p_inventory_item_id
955 , 'Validate_Result_Lot'
956 );
957 print_debug ('P_transaction_Type_id is ' || p_transaction_type_id
958 , 'Validate_Result_Lot'
959 );
960 print_debug ('p_st_lot_num_tbl.count is ' || p_st_lot_num_tbl.COUNT
961 , 'Validate_Result_Lot'
962 );
963 print_debug ('p_rs_lot_num_tbl.count is ' || p_rs_lot_num_tbl.COUNT
964 , 'Validate_Result_Lot'
965 );
966 END IF;
967
968 IF ( p_transaction_type_id IS NULL
969 OR p_st_lot_num_tbl.COUNT = 0
970 OR p_rs_lot_num_tbl.COUNT = 0
971 OR p_organization_id IS NULL
972 OR p_inventory_item_id IS NULL
973 )
974 THEN
975 IF (l_debug = 1)
976 THEN
977 print_debug ('Missing Required Parameter', 'Validate_Result_Lot');
978 END IF;
979
980 fnd_message.set_name ('INV', 'INV_MISSING_REQUIRED_PARAMETER');
981 fnd_msg_pub.ADD;
982 RAISE fnd_api.g_exc_error;
983 END IF;
984 /* Bug#4363274. This check is not required.
985 IF (p_transaction_type_id = inv_globals.g_type_inv_lot_merge)
986 THEN
987 IF (l_debug = 1)
988 THEN
989 print_debug ('result lot_num is ' || p_rs_lot_num_tbl (1)
990 , 'Validate_Result_Lot'
991 );
992 END IF;
993
994 FOR i IN 1 .. p_st_lot_num_tbl.COUNT
995 LOOP
996 IF (l_debug = 1)
997 THEN
998 print_debug ('lot_num ' || i || ' is ' || p_st_lot_num_tbl (i)
999 , 'Validate_Result_Lot'
1000 );
1001 END IF;
1002
1003 IF (p_st_lot_num_tbl (i) = p_rs_lot_num_tbl (1))
1004 THEN
1005 fnd_message.set_name ('INV', 'INV_MERGELOT_USED');
1006 fnd_msg_pub.ADD;
1007 l_validation_status := 'N';
1008 RAISE fnd_api.g_exc_error;
1009 END IF;
1010 END LOOP;
1011 END IF;
1012 */
1013
1014 BEGIN
1015 SELECT lot_number_uniqueness
1016 INTO l_lot_uniqueness
1017 FROM mtl_parameters
1018 WHERE organization_id = p_organization_id;
1019 EXCEPTION
1020 WHEN NO_DATA_FOUND
1021 THEN
1022 fnd_message.set_name ('INV', 'INV_INT_ORG_CODE');
1023 fnd_msg_pub.ADD;
1024 x_validation_status := 'N';
1025 RAISE fnd_api.g_exc_unexpected_error;
1026 END;
1027
1028 IF (l_debug = 1)
1029 THEN
1030 print_debug ('l_lot_uniqueness is ' || l_lot_uniqueness
1031 , 'Validate_Result_Lot'
1032 );
1033 END IF;
1034
1035 IF (l_lot_uniqueness = 1)
1036 THEN
1037 -- lot number is unique accross items
1038 IF (p_transaction_type_id = inv_globals.g_type_inv_lot_split)
1039 THEN
1040 FOR i IN 1 .. p_rs_lot_num_tbl.COUNT
1041 LOOP
1042 SELECT COUNT (1)
1043 INTO l_lot_count
1044 FROM mtl_lot_numbers
1045 WHERE inventory_item_id <> p_inventory_item_id
1046 AND organization_id = p_organization_id
1047 AND lot_number = p_rs_lot_num_tbl (i);
1048
1049 IF (l_lot_count > 0)
1050 THEN
1051 l_validation_status := 'N';
1052 fnd_message.set_name ('INV', 'INV_INT_LOTUNIQEXP');
1053 fnd_msg_pub.ADD;
1054 RAISE fnd_api.g_exc_error;
1055 ELSE
1056 l_validation_status := 'Y';
1057 END IF;
1058
1059 SELECT COUNT (1)
1060 INTO l_lot_count
1061 FROM mtl_transaction_lots_temp mtlt
1062 , mtl_material_transactions_temp mmtt
1063 WHERE mmtt.inventory_item_id <> p_inventory_item_id
1064 AND mmtt.organization_id = p_organization_id
1065 AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
1066 AND mtlt.lot_number = p_rs_lot_num_tbl (i);
1067
1068 IF (l_lot_count > 0)
1069 THEN
1070 l_validation_status := 'N';
1071 fnd_message.set_name ('INV', 'INV_INT_LOTUNIQEXP');
1072 fnd_msg_pub.ADD;
1073 RAISE fnd_api.g_exc_error;
1074 ELSE
1075 l_validation_status := 'Y';
1076 END IF;
1077
1078 FOR j IN 1 .. p_rs_lot_num_tbl.COUNT
1079 LOOP
1080 IF (i <> j)
1081 THEN
1082 IF (p_rs_lot_num_tbl (i) = p_rs_lot_num_tbl (j))
1083 THEN
1084 IF (l_debug = 1)
1085 THEN
1086 print_debug ('Duplicate Lot', 'Validate_Result_Lot');
1087 END IF;
1088
1089 l_validation_status := 'N';
1090 fnd_message.set_name ('INV', 'INV_DUPLICATE_LOT');
1091 fnd_msg_pub.ADD;
1092 RAISE fnd_api.g_exc_error;
1093 END IF;
1094 END IF;
1095 END LOOP;
1096 END LOOP;
1097 ELSIF (p_transaction_type_id = inv_globals.g_type_inv_lot_translate)
1098 THEN
1099 IF (p_st_lot_num_tbl (1) <> p_rs_lot_num_tbl (1))
1100 THEN
1101 SELECT COUNT (1)
1102 INTO l_lot_count
1103 FROM mtl_lot_numbers
1104 WHERE inventory_item_id <> p_inventory_item_id
1105 AND organization_id = p_organization_id
1106 AND lot_number = p_rs_lot_num_tbl (1);
1107
1108 IF (l_lot_count > 0)
1109 THEN
1110 l_validation_status := 'N';
1111 fnd_message.set_name ('INV', 'INV_INT_LOTUNIQEXP');
1112 fnd_msg_pub.ADD;
1113 RAISE fnd_api.g_exc_error;
1114 ELSE
1115 l_validation_status := 'Y';
1116 END IF;
1117
1118 SELECT COUNT (1)
1119 INTO l_lot_count
1120 FROM mtl_transaction_lots_temp mtlt
1121 , mtl_material_transactions_temp mmtt
1122 WHERE mmtt.inventory_item_id <> p_inventory_item_id
1123 AND mmtt.organization_id = p_organization_id
1124 AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
1125 AND mtlt.lot_number = p_rs_lot_num_tbl (1);
1126
1127 IF (l_lot_count > 0)
1128 THEN
1129 l_validation_status := 'N';
1130 fnd_message.set_name ('INV', 'INV_INT_LOTUNIQEXP');
1131 fnd_msg_pub.ADD;
1132 RAISE fnd_api.g_exc_error;
1133 ELSE
1134 l_validation_status := 'Y';
1135 END IF;
1136 END IF;
1137 ELSIF (p_transaction_type_id = inv_globals.g_type_inv_lot_merge)
1138 THEN
1139 SELECT COUNT (1)
1140 INTO l_lot_count
1141 FROM mtl_lot_numbers
1142 WHERE inventory_item_id <> p_inventory_item_id
1143 AND organization_id = p_organization_id
1144 AND lot_number = p_rs_lot_num_tbl (1);
1145
1146 IF (l_lot_count > 0)
1147 THEN
1148 -- this means the lot number exists for different item.
1149 -- for lot merge, the resultant lot can be an existing item, but of the same item,
1150 -- cannot be from different items.
1151 l_validation_status := 'N';
1152 fnd_message.set_name ('INV', 'INV_INT_LOTUNIQEXP');
1153 fnd_msg_pub.ADD;
1154 RAISE fnd_api.g_exc_error;
1155 ELSE
1156 l_validation_status := 'Y';
1157 END IF;
1158
1159 SELECT COUNT (1)
1160 INTO l_lot_count
1161 FROM mtl_transaction_lots_temp mtlt
1162 , mtl_material_transactions_temp mmtt
1163 WHERE mmtt.inventory_item_id <> p_inventory_item_id
1164 AND mmtt.organization_id = p_organization_id
1165 AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
1166 AND mtlt.lot_number = p_rs_lot_num_tbl (1);
1167
1168 IF (l_lot_count > 0)
1169 THEN
1170 l_validation_status := 'N';
1171 fnd_message.set_name ('INV', 'INV_INT_LOTUNIQEXP');
1172 fnd_msg_pub.ADD;
1173 RAISE fnd_api.g_exc_error;
1174 ELSE
1175 l_validation_status := 'Y';
1176 END IF;
1177 END IF;
1178 END IF;
1179
1180 -- here is lot number uniqueness is none.
1181 IF (p_transaction_type_id = inv_globals.g_type_inv_lot_split)
1182 THEN
1183 FOR i IN 1 .. p_rs_lot_num_tbl.COUNT
1184 LOOP
1185 SELECT COUNT (1)
1186 INTO l_lot_count
1187 FROM mtl_lot_numbers
1188 WHERE inventory_item_id = p_inventory_item_id
1189 AND organization_id = p_organization_id
1190 AND lot_number = p_rs_lot_num_tbl (i);
1191
1192 IF (l_lot_count > 0)
1193 THEN
1194 l_validation_status := 'N';
1195 fnd_message.set_name ('INV', 'INV_LOT_EXISTS');
1196 fnd_msg_pub.ADD;
1197 RAISE fnd_api.g_exc_error;
1198 ELSE
1199 l_validation_status := 'Y';
1200 END IF;
1201
1202 -- this is for specific OSFM validation
1203 SELECT COUNT (1)
1204 INTO l_lot_count
1205 FROM wip_entities
1206 WHERE wip_entity_name = p_rs_lot_num_tbl (i)
1207 AND organization_id = p_organization_id;
1208
1209 IF l_lot_count > 0
1210 THEN
1211 l_validation_status := 'N';
1212 fnd_message.set_name ('INV', 'INV_LOT_EXISTS');
1213 fnd_msg_pub.ADD;
1214 RAISE fnd_api.g_exc_error;
1215 ELSE
1216 l_validation_status := 'Y';
1217 END IF;
1218
1219 SELECT COUNT (1)
1220 INTO l_lot_count
1221 FROM mtl_transaction_lots_temp mtlt
1222 , mtl_material_transactions_temp mmtt
1223 WHERE mmtt.inventory_item_id = p_inventory_item_id
1224 AND mmtt.organization_id = p_organization_id
1225 AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
1226 AND mtlt.lot_number = p_rs_lot_num_tbl (i);
1227
1228 IF l_lot_count > 0
1229 THEN
1230 l_validation_status := 'N';
1231 fnd_message.set_name ('INV', 'INV_LOT_EXISTS');
1232 fnd_msg_pub.ADD;
1233 RAISE fnd_api.g_exc_error;
1234 ELSE
1235 l_validation_status := 'Y';
1236 END IF;
1237
1238 FOR j IN 1 .. p_rs_lot_num_tbl.COUNT
1239 LOOP
1240 IF (i <> j)
1241 THEN
1242 IF (p_rs_lot_num_tbl (i) = p_rs_lot_num_tbl (j))
1243 THEN
1244 IF (l_debug = 1)
1245 THEN
1246 print_debug ('Duplicate Lot', 'Validate_Result_Lot');
1247 END IF;
1248
1249 l_validation_status := 'N';
1250 fnd_message.set_name ('INV', 'INV_DUPLICATE_LOT');
1251 fnd_msg_pub.ADD;
1252 RAISE fnd_api.g_exc_error;
1253 END IF;
1254 END IF;
1255 END LOOP;
1256 END LOOP;
1257 ELSIF (p_transaction_type_id = inv_globals.g_type_inv_lot_translate)
1258 THEN
1259 IF (l_debug = 1)
1260 THEN
1261 print_debug ('start lot = ' || p_st_lot_num_tbl (1)
1262 , 'Validate_Result_Lot'
1263 );
1264 print_debug ('result lot = ' || p_rs_lot_num_tbl (1)
1265 , 'Validate_Result_Lot'
1266 );
1267 END IF;
1268
1269 IF (p_st_lot_num_tbl (1) <> p_rs_lot_num_tbl (1))
1270 THEN
1271 SELECT COUNT (1)
1272 INTO l_lot_count
1273 FROM mtl_lot_numbers
1274 WHERE inventory_item_id = p_inventory_item_id
1275 AND organization_id = p_organization_id
1276 AND lot_number = p_rs_lot_num_tbl (1);
1277
1278 IF (l_lot_count > 0)
1279 THEN
1280 l_validation_status := 'N';
1281 fnd_message.set_name ('INV', 'INV_LOT_EXISTS');
1282 fnd_msg_pub.ADD;
1283 RAISE fnd_api.g_exc_error;
1284 ELSE
1285 l_validation_status := 'Y';
1286 END IF;
1287
1288 SELECT COUNT (1)
1289 INTO l_lot_count
1290 FROM wip_entities
1291 WHERE organization_id = p_organization_id
1292 AND wip_entity_name = p_rs_lot_num_tbl (1);
1293
1294 IF l_lot_count > 0
1295 THEN
1296 l_validation_status := 'N';
1297 fnd_message.set_name ('INV', 'INV_LOT_EXISTS');
1298 fnd_msg_pub.ADD;
1299 RAISE fnd_api.g_exc_error;
1300 ELSE
1301 l_validation_status := 'Y';
1302 END IF;
1303
1304 SELECT COUNT (1)
1305 INTO l_lot_count
1306 FROM mtl_transaction_lots_temp mtlt
1307 , mtl_material_transactions_temp mmtt
1308 WHERE mmtt.inventory_item_id = p_inventory_item_id
1309 AND mmtt.organization_id = p_organization_id
1310 AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
1311 AND mtlt.lot_number = p_rs_lot_num_tbl (1);
1312
1313 IF l_lot_count > 0
1314 THEN
1315 l_validation_status := 'N';
1316 fnd_message.set_name ('INV', 'INV_LOT_EXISTS');
1317 fnd_msg_pub.ADD;
1318 RAISE fnd_api.g_exc_error;
1319 ELSE
1320 l_validation_status := 'Y';
1321 END IF;
1322 END IF;
1323 ELSIF (p_transaction_type_id = inv_globals.g_type_inv_lot_merge)
1324 THEN
1325 IF (l_debug = 1)
1326 THEN
1327 print_debug
1328 ('validate result lot for lot uniqueness is 2 for lot merge'
1329 , 'validate_result_lot'
1330 );
1331 END IF;
1332 /*
1333 for l_lot_csr in lot_num_csr(p_rs_lot_num_tbl(1), p_organization_id) LOOP
1334 IF (l_debug = 1) THEN
1335 print_debug('l_inventory_item_id is ' || l_lot_csr.inventory_item_id, 'validate_result_lot');
1336 END IF;
1337 if( l_lot_csr.inventory_item_id = p_inventory_item_id ) then
1338 l_found := true;
1339 end if;
1340 end loop;
1341
1342 if( l_found = false ) then
1343 FND_MESSAGE.SET_NAME('INV', 'INV_DIFF_MERGE_ITEM');
1344 FND_MSG_PUB.ADD;
1345 l_validation_status := 'N';
1346 raise FND_API.G_EXC_ERROR;
1347 end if;
1348 IF (l_debug = 1) THEN
1349 print_Debug('after validating the item', 'validate_result_lot');
1350 END IF;
1351
1352 SELECT count(1)
1353 INTO l_lot_count
1354 FROM mtl_lot_numbers
1355 WHERE inventory_item_id = p_inventory_item_id
1356 AND organization_id = p_organization_id
1357 AND lot_number = p_rs_lot_num_tbl(1);
1358
1359 if( l_lot_count = 0 ) then
1360 IF (l_debug = 1) THEN
1361 print_debug('after validating against mtl_lot_numbers', 'validate_result_lot');
1362 END IF;
1363 SELECT count(1)
1364 INTO l_lot_count
1365 FROM WIP_ENTITIES
1366 WHERE organization_id = p_organization_id
1367 AND wip_entity_name = p_rs_lot_num_tbl(1);
1368
1369 if( l_lot_count = 0 ) then
1370 IF (l_debug = 1) THEN
1371 print_debug('after validating against wip_entities', 'validate_result_lot');
1372 END IF;
1373 SELECT COUNT(1)
1374 INTO l_lot_count
1375 FROM MTL_TRANSACTION_LOTS_TEMP MTLT, MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
1376 WHERE mmtt.inventory_item_id = p_inventory_item_id
1377 AND mmtt.organization_id = p_organization_id
1378 AND Mmtt.transaction_temp_id = MTLT.transaction_temp_id
1379 AND mtlt.lot_number = p_rs_lot_num_tbl(1);
1380
1381 IF l_lot_count = 0 Then
1382 l_validation_status := 'N';
1383 FND_MESSAGE.SET_NAME('INV', 'INV_INVALID_LOT');
1384 FND_MSG_PUB.ADD;
1385 raise FND_API.G_EXC_ERROR;
1386 else
1387 l_validation_status := 'Y';
1388 end if;
1389 IF (l_debug = 1) THEN
1390 print_debug('after validating against mtl_transaction_lots_temp', 'validate_result_lot');
1391 END IF;
1392 else
1393 l_validation_status := 'Y';
1394 end if;
1395 else
1396 l_validation_status := 'Y';
1397 end if;
1398 */
1399 END IF;
1400
1401 x_return_status := fnd_api.g_ret_sts_success;
1402 x_validation_status := l_validation_status;
1403
1404 IF (x_validation_status <> 'Y')
1405 THEN
1406 fnd_message.set_name ('INV', 'INV_INVALID_LOT');
1407 fnd_msg_pub.ADD;
1408 RAISE fnd_api.g_exc_error;
1409 END IF;
1410
1411 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1412 EXCEPTION
1413 WHEN fnd_api.g_exc_error
1414 THEN
1415 x_validation_status := l_validation_status;
1416 x_return_status := fnd_api.g_ret_sts_error;
1417 fnd_msg_pub.count_and_get (p_count => x_msg_count
1418 , p_data => x_msg_data);
1419 WHEN fnd_api.g_exc_unexpected_error
1420 THEN
1421 x_validation_status := l_validation_status;
1422 x_return_status := fnd_api.g_ret_sts_unexp_error;
1423 fnd_msg_pub.count_and_get (p_count => x_msg_count
1424 , p_data => x_msg_data);
1425 WHEN OTHERS
1426 THEN
1427 x_validation_status := l_validation_status;
1428 x_return_status := fnd_api.g_ret_sts_unexp_error;
1429
1430 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
1431 THEN
1432 fnd_msg_pub.add_exc_msg (g_pkg_name
1433 , 'Validate_Result_Lot_Uniqueness');
1434 END IF;
1435
1436 fnd_msg_pub.count_and_get (p_count => x_msg_count
1437 , p_data => x_msg_data);
1438 END validate_result_lot;
1439
1440 /*********************************************************************************************
1441 * Pseudo - code: *
1442 * if( p_start_lot_number IS NULL OR p_result_lot_number IS NULL OR *
1443 * p_start_inv_item_id IS NULL OR p_result_inv_item_id IS NULL ) THEN *
1444 * return missing required parameter error *
1445 * end if; *
1446 * *
1447 * if( p_start_lot_number = p_result_lot_number ) then *
1448 * if( p_start_inv_item_id = p_result_inv_item_id ) then *
1449 * return 'N'; *
1450 * else *
1451 * return 'Y'; *
1452 * end if; *
1453 * else *
1454 * if( p_start_inv_item_id = p_result_inv_item_id ) then *
1455 * return 'Y'; *
1456 * else *
1457 * return 'N'; *
1458 * end if; *
1459 * end if; *
1460 *********************************************************************************************/
1461 PROCEDURE validate_lot_translate (
1462 x_return_status OUT NOCOPY VARCHAR2
1463 , x_msg_count OUT NOCOPY NUMBER
1464 , x_msg_data OUT NOCOPY VARCHAR2
1465 , x_validation_status OUT NOCOPY VARCHAR2
1466 , p_start_lot_number IN VARCHAR2
1467 , p_start_inv_item_id IN NUMBER
1468 , p_result_lot_number IN VARCHAR2
1469 , p_result_inv_item_id IN NUMBER
1470 )
1471 IS
1472 l_debug NUMBER := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
1473 BEGIN
1474 IF (l_debug = 1)
1475 THEN
1476 print_debug ('Inside Validate_Lot_Translate', 'Validate_Lot_Translate');
1477 print_debug ('p_start_lot_number is ' || p_start_lot_number
1478 , 'Validate_Lot_Translate'
1479 );
1480 print_debug ('p_result_lot_number is ' || p_result_lot_number
1481 , 'Validate_Lot_Translate'
1482 );
1483 print_debug ('p_start_inv_item_id is ' || p_start_inv_item_id
1484 , 'Validate_Lot_Translate'
1485 );
1486 print_debug ('p_result_inv_item_id is ' || p_result_inv_item_id
1487 , 'Validate_Lot_Translate'
1488 );
1489 END IF;
1490
1491 IF ( p_start_lot_number IS NULL
1492 OR p_result_lot_number IS NULL
1493 OR p_start_inv_item_id IS NULL
1494 OR p_result_inv_item_id IS NULL
1495 )
1496 THEN
1497 fnd_message.set_name ('INV', 'INV_MISSING_REQUIRED_PARAMETER');
1498 fnd_msg_pub.ADD;
1499 x_validation_status := 'N';
1500 RAISE fnd_api.g_exc_unexpected_error;
1501 END IF;
1502
1503 IF (p_start_lot_number = p_result_lot_number)
1504 THEN
1505 IF (p_start_inv_item_id = p_result_inv_item_id)
1506 THEN
1507 IF (l_debug = 1)
1508 THEN
1509 print_debug ('I am here, x_validation_status is N'
1510 , 'Validate_Lot_Translate'
1511 );
1512 END IF;
1513
1514 x_validation_status := 'N';
1515 fnd_message.set_name ('INV', 'INV_ALREADY_EXISTS');
1516 fnd_message.set_token ('ENTITY'
1517 , fnd_message.get_string ('INV', 'LOT_NUMBER')
1518 );
1519 fnd_msg_pub.ADD;
1520 RAISE fnd_api.g_exc_error;
1521 ELSE
1522 x_validation_status := 'Y';
1523 END IF;
1524 ELSE
1525 /*if( p_start_inv_item_id = p_result_inv_item_id ) then
1526 x_validation_status := 'Y';
1527 else
1528 x_validation_status := 'N';
1529 FND_MESSAGE.SET_NAME('INV', 'INV_ALREADY_EXISTS');
1530 FND_MESSAGE.SET_TOKEN('ENTITY', FND_MESSAGE.get_String('INV', 'LOT_NUMBER'));
1531 FND_MSG_PUB.ADD;
1532 raise FND_API.G_EXC_ERROR;
1533 end if;*/
1534 x_validation_status := 'Y';
1535 END IF;
1536
1537 x_return_status := fnd_api.g_ret_sts_success;
1538 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1539 EXCEPTION
1540 WHEN fnd_api.g_exc_error
1541 THEN
1542 x_return_status := fnd_api.g_ret_sts_error;
1543 fnd_msg_pub.count_and_get (p_count => x_msg_count
1544 , p_data => x_msg_data);
1545 WHEN fnd_api.g_exc_unexpected_error
1546 THEN
1547 x_return_status := fnd_api.g_ret_sts_unexp_error;
1548 fnd_msg_pub.count_and_get (p_count => x_msg_count
1549 , p_data => x_msg_data);
1550 WHEN OTHERS
1551 THEN
1552 x_return_status := fnd_api.g_ret_sts_unexp_error;
1553
1554 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
1555 THEN
1556 fnd_msg_pub.add_exc_msg (g_pkg_name, 'Validate_Lot_Translate');
1557 END IF;
1558
1559 fnd_msg_pub.count_and_get (p_count => x_msg_count
1560 , p_data => x_msg_data);
1561 END validate_lot_translate;
1562
1563
1564
1565 /***********************************Validate_LPN_Info*************************
1566 Perform basic validations for the LPNs present in the Lot transactions.
1567 -> From LPN should always be in context "Resides in Inventory"
1568 -> To LPN can be in status 'Resides in Inventory' OR 'Defined but not used'
1569 -> Validate the org, sub and locator for To LPN
1570 ****************************************************************************/
1571 PROCEDURE validate_lpn_info (
1572 x_return_status OUT NOCOPY VARCHAR2
1573 , x_msg_count OUT NOCOPY NUMBER
1574 , x_msg_data OUT NOCOPY VARCHAR2
1575 , x_validation_status OUT NOCOPY VARCHAR2
1576 , p_st_lpn_id_tbl IN number_table
1577 , p_rs_lpn_id_tbl IN number_table
1578 , p_st_org_id_tbl IN number_table
1579 , p_rs_org_id_tbl IN number_table
1580 , p_rs_sub_code_tbl IN sub_code_table
1581 , p_rs_locator_id_tbl IN number_table
1582 )
1583 IS
1584 l_lpn_context NUMBER;
1585 l_org_id NUMBER;
1586 l_sub_code mtl_secondary_inventories.secondary_inventory_name%TYPE;
1587 l_locator_id NUMBER;
1588 l_validation_status VARCHAR2(1);
1589 l_debug NUMBER;
1590 BEGIN
1591 l_debug := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
1592 x_return_status := fnd_api.g_ret_sts_success;
1593 l_validation_status := 'Y';
1594
1595 FOR i IN 1..p_st_lpn_id_tbl.COUNT LOOP
1596 IF(p_st_lpn_id_tbl(i) IS NOT NULL) THEN
1597 BEGIN
1598 SELECT lpn_context
1599 INTO l_lpn_context
1600 FROM wms_license_plate_numbers
1601 WHERE lpn_id = p_st_lpn_id_tbl(i);
1602 EXCEPTION
1603 WHEN OTHERS THEN
1604 l_validation_status := 'N';
1605 RAISE fnd_api.g_exc_unexpected_error;
1606 END;
1607 IF(l_lpn_context <> 1) --does not resides in inventory
1608 THEN
1609 fnd_message.set_name('INV', 'INV_INVALID_LPN_CONTEXT');
1610 fnd_msg_pub.ADD;
1611 IF (l_debug = 1) THEN
1612 print_debug('validate_lpn_info: Invalid LPN Context for FROM LPN' , 'validate_lpn_info');
1613 END IF;
1614 l_validation_status := 'N';
1615 RAISE fnd_api.g_exc_error;
1616 END IF;
1617 END IF;
1618 END LOOP;
1619
1620 FOR i IN 1..p_rs_lpn_id_tbl.COUNT LOOP
1621 IF(p_rs_lpn_id_tbl(i) IS NOT NULL) THEN
1622 BEGIN
1623 SELECT lpn_context
1624 ,subinventory_code
1625 ,locator_id
1626 ,organization_id
1627 INTO l_lpn_context
1628 ,l_sub_code
1629 ,l_locator_id
1630 ,l_org_id
1631 FROM wms_license_plate_numbers
1632 WHERE lpn_id = p_rs_lpn_id_tbl(i);
1633 EXCEPTION
1634 WHEN OTHERS THEN
1635 l_validation_status := 'N';
1636 RAISE fnd_api.g_exc_unexpected_error;
1637 END;
1638 IF(l_lpn_context NOT IN (1,5)) --does not 'resides in inventory' and not 'defined but not used'
1639 THEN
1640 fnd_message.set_name('INV', 'INV_INVALID_LPN_CONTEXT');
1641 fnd_msg_pub.ADD;
1642 IF (l_debug = 1) THEN
1643 print_debug('validate_lpn_info: Invalid LPN Context for TO LPN', 'validate_lpn_info');
1644 END IF;
1645 l_validation_status := 'N';
1646 RAISE fnd_api.g_exc_error;
1647 ELSIF(l_lpn_context = 1) THEN
1648 IF ( (l_org_id <> p_st_org_id_tbl(1) )
1649 OR
1650 (NVL(l_sub_code, '@#$%') <> p_rs_sub_code_tbl(i))
1651 OR
1652 (NVL(l_locator_id, -9999) <> NVL(p_rs_locator_id_tbl(i), -9999))
1653 ) THEN
1654 fnd_message.set_name('INV', 'INV_INT_LPN');
1655 fnd_msg_pub.ADD;
1656 IF (l_debug = 1) THEN
1657 print_debug('validate_lpn_info: Org/Sub/Loc of LPN does not match', 'validate_lpn_info');
1658 END IF;
1659 l_validation_status := 'N';
1660 RAISE fnd_api.g_exc_error;
1661 END IF;
1662 END IF;
1663 END IF;
1664 END LOOP;
1665
1666 x_validation_status := 'Y';
1667 x_return_status := fnd_api.g_ret_sts_success;
1668 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1669 EXCEPTION
1670 WHEN fnd_api.g_exc_error
1671 THEN
1672 x_validation_status := l_validation_status;
1673 x_return_status := fnd_api.g_ret_sts_error;
1674 fnd_msg_pub.count_and_get (p_count => x_msg_count
1675 , p_data => x_msg_data);
1676 WHEN fnd_api.g_exc_unexpected_error
1677 THEN
1678 x_validation_status := l_validation_status;
1679 x_return_status := fnd_api.g_ret_sts_unexp_error;
1680 fnd_msg_pub.count_and_get (p_count => x_msg_count
1681 , p_data => x_msg_data);
1682 WHEN OTHERS
1683 THEN
1684 x_validation_status := 'E';
1685 x_return_status := fnd_api.g_ret_sts_unexp_error;
1686
1687 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
1688 THEN
1689 fnd_msg_pub.add_exc_msg (g_pkg_name, 'Validate_Lpn_Info');
1690 END IF;
1691
1692 fnd_msg_pub.count_and_get (p_count => x_msg_count
1693 , p_data => x_msg_data);
1694
1695 END validate_lpn_info;
1696
1697
1698 /*********************************************************************************************
1699 * Pseudo - code: *
1700 * if( p_transaction_type_id IS NULL OR p_lot_number is NULL *
1701 * OR p_organization_id IS NULL OR p_inventory_item_id is NULL ) then *
1702 * return missing required parameter error *
1703 * end if; *
1704 * *
1705 * if( p_status_id is null ) then *
1706 * retrieve the status from mtl_lot_numbers for the lot number into l_status_id *
1707 * If not found then *
1708 * Return invalid lot number error *
1709 * End if; *
1710 * Else *
1711 * l_status_id := p_status_id; *
1712 * end if; *
1713 * *
1714 * call inv_material_status_grp.get_lot_serial_status_control to get the *
1715 * lot_status_enabled and default_lot_status_id for the org, item and lot. *
1716 * *
1717 * if( return status Is not success ) *
1718 * return validation_status = 'N' *
1719 * end if; *
1720 * *
1721 * Call Get_Org_info (to get the wms_intalled, wsm_enabled and wms_enabled flag) *
1722 * *
1723 * if( l_status_id IS NULL OR l_wsm_enabled = 'Y' ) then *
1724 * -- no status is assigned and this is an WSM organization, we don't care *
1725 * -- about status *
1726 * return validation_status = 'Y'; *
1727 * else *
1728 * call inv_material_status_grp.is_status_applicable to see if the lot *
1729 * split or lot merge or lot translate is enable or not by the status on *
1730 * the subinventory, locator, organization and lot number *
1731 * if status is applicable then *
1732 * return 'Y' *
1733 * else *
1734 * return 'N' *
1735 * end if; *
1736 * end if; *
1737 **********************************************************************************************/
1738 PROCEDURE validate_material_status (
1739 x_return_status OUT NOCOPY VARCHAR2
1740 , x_msg_count OUT NOCOPY NUMBER
1741 , x_msg_data OUT NOCOPY VARCHAR2
1742 , x_validation_status OUT NOCOPY VARCHAR2
1743 , p_transaction_type_id IN NUMBER
1744 , p_organization_id IN NUMBER
1745 , p_inventory_item_id IN NUMBER
1746 , p_lot_number IN VARCHAR2
1747 , p_subinventory_code IN VARCHAR2
1748 , p_locator_id IN NUMBER
1749 , p_status_id IN NUMBER
1750 )
1751 IS
1752 l_validation_status VARCHAR2 (1);
1753 l_wms_installed VARCHAR2 (30);
1754 l_wms_enabled VARCHAR2 (1);
1755 l_wsm_enabled VARCHAR2 (1);
1756 l_status_id NUMBER;
1757 l_default_lot_status_id NUMBER;
1758 l_lot_status_enabled VARCHAR2 (10);
1759 l_serial_status_enabled VARCHAR2 (10);
1760 l_default_serial_status_id NUMBER;
1761 l_debug NUMBER
1762 := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
1763 BEGIN
1764 IF ( p_transaction_type_id IS NULL
1765 OR p_lot_number IS NULL
1766 OR p_organization_id IS NULL
1767 OR p_inventory_item_id IS NULL
1768 )
1769 THEN
1770 fnd_message.set_name ('INV', 'INV_MISSING_REQUIRED_PARAMETER');
1771 fnd_msg_pub.ADD;
1772 x_validation_status := 'N';
1773 RAISE fnd_api.g_exc_error;
1774 END IF;
1775
1776 IF (l_debug = 1)
1777 THEN
1778 print_debug ('Inside Validate_Material_Status'
1779 , 'Validate_Material_status'
1780 );
1781 print_debug ('p_transaction_Type_id is ' || p_transaction_type_id
1782 , 'Validate_Material_status'
1783 );
1784 print_debug ('p_organization_id is ' || p_organization_id
1785 , 'Validate_Material_status'
1786 );
1787 print_debug ('p_inventory_item_id is ' || p_inventory_item_id
1788 , 'Validate_Material_status'
1789 );
1790 print_debug ('p_lot_number is ' || p_lot_number
1791 , 'Validate_Material_status'
1792 );
1793 print_debug ('p_subinventory_code is ' || p_subinventory_code
1794 , 'Validate_Material_status'
1795 );
1796 print_debug ('p_status_id is ' || p_status_id
1797 , 'Validate_Material_status'
1798 );
1799 END IF;
1800
1801 IF (p_status_id IS NULL)
1802 THEN
1803 BEGIN
1804 SELECT status_id
1805 INTO l_status_id
1806 FROM mtl_lot_numbers
1807 WHERE organization_id = p_organization_id
1808 AND inventory_item_id = p_inventory_item_id
1809 AND lot_number = p_lot_number;
1810 EXCEPTION
1811 WHEN NO_DATA_FOUND
1812 THEN
1813 fnd_message.set_name ('INV', 'INV_INVALID_ATTRIBUTE');
1814 fnd_message.set_token ('ATTRIBUTE'
1815 , fnd_message.get_string ('INV'
1816 , 'CAPS_LOT_NUMBER'
1817 )
1818 , FALSE
1819 );
1820 fnd_msg_pub.ADD;
1821 x_validation_status := 'N';
1822 END;
1823 ELSE
1824 l_status_id := p_status_id;
1825 END IF;
1826
1827 inv_material_status_grp.get_lot_serial_status_control
1828 (p_organization_id => p_organization_id
1829 , p_inventory_item_id => p_inventory_item_id
1830 , x_return_status => x_return_status
1831 , x_msg_data => x_msg_data
1832 , x_msg_count => x_msg_count
1833 , x_lot_status_enabled => l_lot_status_enabled
1834 , x_default_lot_status_id => l_default_lot_status_id
1835 , x_serial_status_enabled => l_serial_status_enabled
1836 , x_default_serial_status_id => l_default_serial_status_id
1837 );
1838
1839 IF (x_return_status <> fnd_api.g_ret_sts_success)
1840 THEN
1841 x_validation_status := 'N';
1842 RAISE fnd_api.g_exc_unexpected_error;
1843 END IF;
1844
1845 IF (l_status_id IS NULL)
1846 THEN
1847 l_validation_status := 'Y';
1848 ELSE
1849 IF (l_debug = 1)
1850 THEN
1851 print_debug ('validate subinventory ', 'Validate_Material_status');
1852 END IF;
1853
1854 l_validation_status :=
1855 inv_material_status_grp.is_status_applicable
1856 (p_wms_installed => l_wms_installed
1857 , p_trx_status_enabled => NULL
1858 , p_trx_type_id => p_transaction_type_id
1859 , p_lot_status_enabled => l_lot_status_enabled
1860 , p_serial_status_enabled => l_serial_status_enabled
1861 , p_organization_id => p_organization_id
1862 , p_inventory_item_id => p_inventory_item_id
1863 , p_sub_code => p_subinventory_code
1864 , p_locator_id => p_locator_id
1865 , p_lot_number => p_lot_number
1866 , p_serial_number => NULL
1867 , p_object_type => 'A'
1868 );
1869 END IF;
1870
1871 x_validation_status := l_validation_status;
1872 x_return_status := fnd_api.g_ret_sts_success;
1873 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1874 EXCEPTION
1875 WHEN fnd_api.g_exc_error
1876 THEN
1877 x_return_status := fnd_api.g_ret_sts_error;
1878 fnd_msg_pub.count_and_get (p_count => x_msg_count
1879 , p_data => x_msg_data);
1880 WHEN fnd_api.g_exc_unexpected_error
1881 THEN
1882 x_return_status := fnd_api.g_ret_sts_unexp_error;
1883 fnd_msg_pub.count_and_get (p_count => x_msg_count
1884 , p_data => x_msg_data);
1885 WHEN OTHERS
1886 THEN
1887 x_return_status := fnd_api.g_ret_sts_unexp_error;
1888
1889 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
1890 THEN
1891 fnd_msg_pub.add_exc_msg (g_pkg_name, 'Validate_Material_Status');
1892 END IF;
1893
1894 fnd_msg_pub.count_and_get (p_count => x_msg_count
1895 , p_data => x_msg_data);
1896 END validate_material_status;
1897
1898 /****************************************************************************
1899 * Added For OSFM support for Serialized Lot Items. Mostly the same logic as *
1900 * the validate_material_status procedure but specific to serial Items *
1901 ******************************************************************************/
1902 PROCEDURE validate_serial_status (
1903 x_return_status OUT NOCOPY VARCHAR2
1904 , x_msg_count OUT NOCOPY NUMBER
1905 , x_msg_data OUT NOCOPY VARCHAR2
1906 , x_validation_status OUT NOCOPY VARCHAR2
1907 , p_transaction_type_id IN NUMBER
1908 , p_organization_id IN NUMBER
1909 , p_inventory_item_id IN NUMBER
1910 , p_serial_number IN VARCHAR2
1911 , p_subinventory_code IN VARCHAR2
1912 , p_locator_id IN NUMBER
1913 , p_status_id IN NUMBER
1914 )
1915 IS
1916 l_validation_status VARCHAR2 (1);
1917 l_wms_installed VARCHAR2 (30);
1918 l_wms_enabled VARCHAR2 (1);
1919 l_wsm_enabled VARCHAR2 (1);
1920 l_status_id NUMBER;
1921 l_default_lot_status_id NUMBER;
1922 l_lot_status_enabled VARCHAR2 (10);
1923 l_serial_status_enabled VARCHAR2 (10);
1924 l_default_serial_status_id NUMBER;
1925 l_debug NUMBER
1926 := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
1927 BEGIN
1928 IF ( p_transaction_type_id IS NULL
1929 OR p_serial_number IS NULL
1930 OR p_organization_id IS NULL
1931 OR p_inventory_item_id IS NULL
1932 )
1933 THEN
1934 fnd_message.set_name ('INV', 'INV_MISSING_REQUIRED_PARAMETER');
1935 fnd_msg_pub.ADD;
1936 x_validation_status := 'N';
1937 RAISE fnd_api.g_exc_error;
1938 END IF;
1939
1940 IF (l_debug = 1)
1941 THEN
1942 print_debug ('Inside Validate_Serial_Status'
1943 , 'Validate_serial_status'
1944 );
1945 print_debug ('p_transaction_Type_id is ' || p_transaction_type_id
1946 , 'Validate_serial_status'
1947 );
1948 print_debug ('p_organization_id is ' || p_organization_id
1949 , 'Validate_serial_status'
1950 );
1951 print_debug ('p_inventory_item_id is ' || p_inventory_item_id
1952 , 'Validate_serial_status'
1953 );
1954 print_debug ('p_subinventory_code is ' || p_subinventory_code
1955 , 'Validate_serial_status'
1956 );
1957 print_debug ('p_status_id is ' || p_status_id, 'Validate_serial_status');
1958 END IF;
1959
1960 IF (p_status_id IS NULL)
1961 THEN
1962 BEGIN
1963 IF (l_debug = 1)
1964 THEN
1965 print_debug ('get status_id from MSN', 'Validate_serial_status');
1966 END IF;
1967
1968 SELECT status_id
1969 INTO l_status_id
1970 FROM mtl_serial_numbers
1971 WHERE current_organization_id = p_organization_id
1972 AND inventory_item_id = p_inventory_item_id
1973 AND serial_number = p_serial_number;
1974 EXCEPTION
1975 WHEN NO_DATA_FOUND
1976 THEN
1977 IF (l_debug = 1)
1978 THEN
1979 print_debug ('exception fetching status_id from MSN'
1980 , 'Validate_serial_status'
1981 );
1982 END IF;
1983
1984 fnd_message.set_name ('INV', 'INV_INVALID_ATTRIBUTE');
1985 fnd_message.set_token ('ATTRIBUTE'
1986 , fnd_message.get_string ('INV'
1987 , 'CAPS_SERIAL_NUMBERS'
1988 )
1989 , FALSE
1990 );
1991 fnd_msg_pub.ADD;
1992 x_validation_status := 'N';
1993 END;
1994 ELSE
1995 l_status_id := p_status_id;
1996 END IF;
1997
1998 IF (l_debug = 1)
1999 THEN
2000 print_debug ('calling get_lot_serial_status_control'
2001 , 'Validate_serial_status'
2002 );
2003 END IF;
2004
2005 inv_material_status_grp.get_lot_serial_status_control
2006 (p_organization_id => p_organization_id
2007 , p_inventory_item_id => p_inventory_item_id
2008 , x_return_status => x_return_status
2009 , x_msg_data => x_msg_data
2010 , x_msg_count => x_msg_count
2011 , x_lot_status_enabled => l_lot_status_enabled
2012 , x_default_lot_status_id => l_default_lot_status_id
2013 , x_serial_status_enabled => l_serial_status_enabled
2014 , x_default_serial_status_id => l_default_serial_status_id
2015 );
2016
2017 IF (x_return_status <> fnd_api.g_ret_sts_success)
2018 THEN
2019 IF (l_debug = 1)
2020 THEN
2021 print_debug (' get_lot_serial_status_control returned with error'
2022 , 'Validate_serial_status'
2023 );
2024 END IF;
2025
2026 x_validation_status := 'N';
2027 RAISE fnd_api.g_exc_unexpected_error;
2028 END IF;
2029
2030 IF (l_status_id IS NULL)
2031 THEN
2032 l_validation_status := 'Y';
2033 ELSE
2034 IF (l_debug = 1)
2035 THEN
2036 print_debug ('validate subinventory ', 'Validate_Serial_status');
2037 END IF;
2038
2039 IF (l_debug = 1)
2040 THEN
2041 print_debug ('calling is_status_applicable'
2042 , 'Validate_serial_status');
2043 END IF;
2044
2045 l_validation_status :=
2046 inv_material_status_grp.is_status_applicable
2047 (p_wms_installed => l_wms_installed
2048 , p_trx_status_enabled => NULL
2049 , p_trx_type_id => p_transaction_type_id
2050 , p_lot_status_enabled => l_lot_status_enabled
2051 , p_serial_status_enabled => l_serial_status_enabled
2052 , p_organization_id => p_organization_id
2053 , p_inventory_item_id => p_inventory_item_id
2054 , p_sub_code => p_subinventory_code
2055 , p_locator_id => p_locator_id
2056 , p_lot_number => NULL
2057 , p_serial_number => p_serial_number
2058 , p_object_type => 'A'
2059 );
2060
2061 IF (l_validation_status <> 'Y')
2062 THEN
2063 IF (l_debug = 1)
2064 THEN
2065 print_debug ('calling is_status_applicable returned with error'
2066 , 'Validate_serial_status'
2067 );
2068 END IF;
2069
2070 x_validation_status := l_validation_status;
2071 RAISE fnd_api.g_exc_error;
2072 END IF;
2073 END IF;
2074
2075 x_validation_status := l_validation_status;
2076 x_return_status := fnd_api.g_ret_sts_success;
2077 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2078 EXCEPTION
2079 WHEN fnd_api.g_exc_error
2080 THEN
2081 x_return_status := fnd_api.g_ret_sts_error;
2082 fnd_msg_pub.count_and_get (p_count => x_msg_count
2083 , p_data => x_msg_data);
2084 WHEN fnd_api.g_exc_unexpected_error
2085 THEN
2086 x_return_status := fnd_api.g_ret_sts_unexp_error;
2087 fnd_msg_pub.count_and_get (p_count => x_msg_count
2088 , p_data => x_msg_data);
2089 WHEN OTHERS
2090 THEN
2091 x_return_status := fnd_api.g_ret_sts_unexp_error;
2092
2093 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
2094 THEN
2095 fnd_msg_pub.add_exc_msg (g_pkg_name, 'Validate_Serial_Status');
2096 END IF;
2097
2098 fnd_msg_pub.count_and_get (p_count => x_msg_count
2099 , p_data => x_msg_data);
2100 END validate_serial_status;
2101
2102 /******************************************************************************************
2103 * populate the global variable g_lot_attributes_tbl with the column name and column type *
2104 * for the lot attributes *
2105 ******************************************************************************************/
2106 PROCEDURE populatelotattributes
2107 IS
2108 CURSOR column_csr (p_table_name VARCHAR2, p_owner VARCHAR2)
2109 IS
2110 SELECT column_name
2111 , data_type
2112 FROM all_tab_columns
2113 WHERE table_name = p_table_name AND owner = p_owner
2114 AND column_id > 22
2115 ORDER BY column_id;
2116
2117 l_column_idx BINARY_INTEGER := 0;
2118 l_ret BOOLEAN;
2119 l_status VARCHAR2 (1);
2120 l_industry VARCHAR2 (1);
2121 l_oracle_schema VARCHAR2 (30);
2122 l_debug NUMBER := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
2123 BEGIN
2124 l_ret :=
2125 fnd_installation.get_app_info ('INV'
2126 , l_status
2127 , l_industry
2128 , l_oracle_schema
2129 );
2130
2131 FOR l_column_csr IN column_csr ('MTL_TRANSACTION_LOTS_INTERFACE'
2132 , l_oracle_schema
2133 )
2134 LOOP
2135 l_column_idx := l_column_idx + 1;
2136 g_lot_attributes_tbl (l_column_idx).column_name :=
2137 l_column_csr.column_name;
2138 g_lot_attributes_tbl (l_column_idx).column_type :=
2139 l_column_csr.data_type;
2140 END LOOP;
2141 END;
2142
2143 /*********************************************************************************************
2144 * Pseudo-codes: *
2145 * x_return_status := FND_API.G_RET_STS_SUCCESS;
2146 * FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
2147 *
2148 * call INV_LOT_SEL_ATTR.is_enabled to see if the lot attributes is enabled for this
2149 * item/org/category combination
2150 *
2151 * if no lot attributes is enabled then
2152 * return validation status 'Y', we don't need to validate any attributes.
2153 * End if;
2154 *
2155 * -- if we are here, means there are some enabled segment, some can be required.
2156 * Initialize g_lot_attributes_tbl by calling populateLotAttributes;
2157 *
2158 *
2159 * if( p_result_lot_attr_tbl.COUNT <> 0 ) then
2160 * -- user populate the lot attributes data for the resulting lots
2161 * for each record in p_result_lot_attr_tbl.COUNT LOOP
2162 * for each record in g_lot_attributes_tbl.COUNT LOOP
2163 * if( UPPER(g_lot_attributes_tbl(j).COLUMN_NAME) match
2164 * UPPER(p_result_lot_attr_tbl(i).COLUMN_NAME) ) then
2165 * g_lot_attributes_Tbl(j).COLUMN_VALUE :=
2166 * p_result_lot_attr_tbl(i).COLUMN_VALUE;
2167 * end if;
2168 * exit when (UPPER(g_lot_attributes_tbl(j).COLUMN_NAME) =
2169 * UPPER(p_result_lot_attr_tbl(i).COLUMN_NAME));
2170 * end loop;
2171 * end loop;
2172 * else
2173 * -- user does not supply attributes for the result lots
2174 * -- use parent lot attributes
2175 * if( p_parent_lot_attr_tbl.COUNT <> 0 ) then
2176 * -- parent lots has attributes
2177 * -- derived from the start lot attributes
2178 * for i in 1..p_parent_lot_attr_tbl.COUNT LOOP
2179 * for j in 1..g_lot_attributes_tbl.COUNT LOOP
2180 * if( UPPER(g_lot_attributes_tbl(j).COLUMN_NAME) =
2181 * UPPER(p_parent_lot_attr_tbl(i).COLUMN_NAME) ) then
2182 * g_lot_attributes_Tbl(j).COLUMN_VALUE :=
2183 * p_parent_lot_attr_tbl(i).COLUMN_VALUE;
2184 * end if;
2185 * exit when (UPPER(g_lot_attributes_tbl(j).COLUMN_NAME) =
2186 * UPPER(p_parent_lot_attr_tbl(i).COLUMN_NAME));
2187 * end loop;
2188 * end loop;
2189 * end if;
2190 * end if;
2191 * -- parent lot does not have attributes and user does not supply attributes
2192 * -- for resulting lots.
2193 * -- use default lot attributes.
2194 * Call inv_lot_sel_attr.get_default to get the default lot attributes
2195 *
2196 * if( l_attributes_default_count > 0 ) then
2197 * for i in 1..l_attributes_default_count LOOP
2198 * for j in 1..g_lot_attributes_tbl.count LOOP
2199 * if( upper(l_attributes_default(i).COLUMN_NAME) =
2200 * upper(g_lot_attributes_tbl(j).COLUMN_NAME) ) then
2201 * g_lot_attributes_tbl(j).COLUMN_VALUE :=
2202 * l_attributes_default(i).COLUMN_VALUE;
2203 * g_lot_attributes_Tbl(j).REQUIRED := l_attributes_default(i).REQUIRED;
2204 * end if;
2205 * exit when (upper(l_attributes_default(i).COLUMN_NAME) =
2206 * upper(g_lot_attributes_tbl(j).COLUMN_NAME));
2207 * end loop;
2208 * end loop;
2209 * end if;
2210 *
2211 * -- Get flexfield
2212 * fnd_dflex.get_flexfield('INV', l_attributes_name, v_flexfield, v_flexinfo);
2213 *
2214 * -- Get Contexts
2215 * fnd_dflex.get_contexts(v_flexfield, v_contexts);
2216 * -- Get Context Value.
2217 * if g_lot_attributes_tbl(9).column_value is null then
2218 * inv_lot_sel_attr.get_context_code(l_context_value,
2219 * p_organization_id,p_inventory_item_id,l_attributes_name);
2220 * g_lot_attributes_tbl(9).column_value := l_context_value;
2221 * else
2222 * l_context_value := g_lot_attributes_tbl(9).column_value;
2223 * end if;
2224 *
2225 * if l_context_value is not null then
2226 * fnd_flex_descval.set_context_value(l_context_value);
2227 * fnd_flex_descval.clear_column_values;
2228 * fnd_flex_descval.set_column_value('LOT_ATTRIBUTE_CATEGORY',
2229 * g_lot_attributes_tbl(9).column_value);
2230 * -- Setting the Values for Validating
2231 * FOR i IN 1..v_contexts.ncontexts LOOP
2232 * IF(v_contexts.is_enabled(i) AND ((UPPER(v_contexts.context_code(i)) =
2233 * UPPER(l_context_value)) OR
2234 * v_contexts.is_global(i))) THEN
2235 * -- Get segments
2236 * fnd_dflex.get_segments(fnd_dflex.make_context(v_flexfield,
2237 * v_contexts.context_code(i)), v_segments, TRUE);
2238 * <<segmentLoop>>
2239 * FOR j IN 1..v_segments.nsegments LOOP
2240 * IF v_segments.is_enabled(j) THEN
2241 * v_colName := v_segments.application_column_name(j);
2242 * <<columnLoop>>
2243 * FOR k IN 1..g_lot_attributes_tbl.count() LOOP
2244 * IF UPPER(v_colName) =
2245 * UPPER(g_lot_attributes_tbl(k).column_name) THEN
2246 * -- Sets the Values for Validation
2247 * -- Setting the column data type for validation
2248 * set the column value to the value in g_lot_attributes
2249 * if segment is required and the column value is NULL then
2250 * return a warning column value required
2251 * end if;
2252 * EXIT ColumnLoop;
2253 * END LOOP columnLoop;
2254 * END IF;
2255 * END LOOP segmentLoop;
2256 * END IF;
2257 * END LOOP contextLoop;
2258 * -- Call the validating routine for Lot Attributes.
2259 * l_status := fnd_flex_descval.validate_desccols(
2260 * appl_short_name => 'INV',
2261 * desc_flex_name => l_attributes_name);
2262 * if l_status = TRUE then
2263 * return l_validation_status := 'Y';
2264 * else
2265 * return l_validation_status := 'N';
2266 * end if;
2267 * else
2268 * -- no context found;
2269 * return l_validation_status := 'Y'
2270 * end if; -- if l_context_value is not null
2271 * x_lot_attr_tbl := g_lot_attributes_tbl;
2272 * x_validation_status := l_validation_status;
2273 *********************************************************************************************/
2274 PROCEDURE validate_attributes (
2275 x_return_status OUT NOCOPY VARCHAR2
2276 , x_msg_count OUT NOCOPY NUMBER
2277 , x_msg_data OUT NOCOPY VARCHAR2
2278 , x_validation_status OUT NOCOPY VARCHAR2
2279 , x_lot_attr_tbl OUT NOCOPY inv_lot_sel_attr.lot_sel_attributes_tbl_type
2280 , p_lot_number IN VARCHAR2 --parent lot
2281 , p_organization_id IN NUMBER
2282 , p_inventory_item_id IN NUMBER
2283 , p_parent_lot_attr_tbl IN inv_lot_sel_attr.lot_sel_attributes_tbl_type
2284 , p_result_lot_attr_tbl IN inv_lot_sel_attr.lot_sel_attributes_tbl_type
2285 , p_transaction_type_id IN NUMBER
2286 )
2287 IS
2288 l_attributes_name VARCHAR2 (50) := 'Lot Attributes';
2289 v_flexfield fnd_dflex.dflex_r;
2290 v_flexinfo fnd_dflex.dflex_dr;
2291 v_contexts fnd_dflex.contexts_dr;
2292 v_segments fnd_dflex.segments_dr;
2293 l_attributes_default_count NUMBER;
2294 l_enabled_attributes NUMBER;
2295 l_attributes_default inv_lot_sel_attr.lot_sel_attributes_tbl_type;
2296 v_context_value mtl_flex_context.descriptive_flex_context_code%TYPE;
2297 v_colname VARCHAR2 (50);
2298 l_context_value VARCHAR2 (150);
2299 l_return_status VARCHAR2 (1);
2300 l_msg_data VARCHAR2 (255);
2301 l_msg_count NUMBER;
2302 l_validation_status VARCHAR2 (1);
2303 l_status BOOLEAN;
2304 l_count NUMBER := 0;
2305 l_rs_lot_attr_category VARCHAR2 (30);
2306 l_st_lot_attr_category VARCHAR2 (30);
2307 l_debug NUMBER
2308 := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
2309 BEGIN
2310 -- call to see if the lot attributes is enabled for this item/org/category combination
2311 IF (l_debug = 1)
2312 THEN
2313 print_debug ('Validate Attributes', 'Validate_Attributes');
2314 END IF;
2315
2316 x_return_status := fnd_api.g_ret_sts_success;
2317 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
2318
2319 IF (l_debug = 1)
2320 THEN
2321 print_debug ('p_inventory_item_id is ' || p_inventory_item_id
2322 , 'Validate_attributes'
2323 );
2324 print_debug ('p_organization_id is ' || p_organization_id
2325 , 'Validate_attributes'
2326 );
2327 END IF;
2328
2329 l_enabled_attributes :=
2330 inv_lot_sel_attr.is_enabled (p_flex_name => l_attributes_name
2331 , p_organization_id => p_organization_id
2332 , p_inventory_item_id => p_inventory_item_id
2333 );
2334
2335 IF (l_debug = 1)
2336 THEN
2337 print_debug ('l_enabled_attributes is ' || l_enabled_attributes
2338 , 'Validate_Attributes'
2339 );
2340 END IF;
2341
2342 IF (l_debug = 1)
2343 THEN
2344 print_debug ('calling populateLotAttributes', 'Validate_Attributes');
2345 END IF;
2346
2347 -- if we are here, means there are some enabled segment, some can be required.
2348 populatelotattributes;
2349
2350 IF (l_debug = 1)
2351 THEN
2352 print_debug ( 'p_result_lot_attr_tbl.COUNT is '
2353 || p_result_lot_attr_tbl.COUNT
2354 , 'Validate_Attributes'
2355 );
2356 print_debug ( 'p_parent_lot_attr_tbl.COUNT is '
2357 || p_parent_lot_attr_tbl.COUNT
2358 , 'Validate_Attributes'
2359 );
2360 END IF;
2361
2362 -- Check to see if the values have been passed for the resultant
2363 -- lot - else pass the values for the parent lot.
2364 FOR i IN 1 .. p_result_lot_attr_tbl.COUNT
2365 LOOP
2366 IF (p_result_lot_attr_tbl (i).column_value IS NOT NULL)
2367 THEN
2368 IF (l_debug = 1)
2369 THEN
2370 print_debug ( 'Column_NAME is '
2371 || p_result_lot_attr_tbl (i).column_name
2372 , 'get_lot_attr_record'
2373 );
2374 print_debug ( 'Column Value is '
2375 || p_result_lot_attr_tbl (i).column_value
2376 , 'get_lot_attr_record'
2377 );
2378 END IF;
2379
2380 l_count := l_count + 1;
2381 END IF;
2382
2383 IF (UPPER (p_result_lot_attr_tbl (i).column_name) =
2384 'LOT_ATTRIBUTE_CATEGORY'
2385 )
2386 THEN
2387 l_rs_lot_attr_category := p_result_lot_attr_tbl (i).column_value;
2388 --contains what is the attr category for this
2389 END IF;
2390 END LOOP;
2391
2392 IF (l_debug = 1)
2393 THEN
2394 print_debug ('Count is : ' || l_count, 'Validate_Attributes');
2395 END IF;
2396
2397 IF (l_count > 0)
2398 --means some of the attributes are populated in the result lot which culd habe
2399 --been either from the MTLI or MLN
2400 THEN
2401 FOR i IN 1 .. p_result_lot_attr_tbl.COUNT
2402 LOOP
2403 FOR j IN 1 .. g_lot_attributes_tbl.COUNT --These are from MTLI
2404 LOOP
2405 IF (UPPER (g_lot_attributes_tbl (j).column_name) =
2406 UPPER (p_result_lot_attr_tbl (i).column_name)
2407 )
2408 THEN
2409 g_lot_attributes_tbl (j).column_value :=
2410 p_result_lot_attr_tbl (i).column_value;
2411
2412 IF (l_debug = 1)
2413 THEN
2414 print_debug ( g_lot_attributes_tbl (j).column_name
2415 || ' '
2416 || g_lot_attributes_tbl (j).column_value
2417 , 'Validate_Attributes'
2418 );
2419 END IF;
2420 END IF;
2421
2422 EXIT WHEN (UPPER (g_lot_attributes_tbl (j).column_name) =
2423 UPPER (p_result_lot_attr_tbl (i).column_name)
2424 );
2425 END LOOP;
2426 END LOOP;
2427 --g_lot_attriburtes_tbl now conatins al the lot attributes for the resultant lot
2428 ELSE
2429 -- user does not supply attributes for the result lots
2430 -- use parent lot attributes
2431 IF (p_parent_lot_attr_tbl.COUNT <> 0)
2432 THEN
2433 -- derived from the start lot attributes
2434 FOR i IN 1 .. p_parent_lot_attr_tbl.COUNT
2435 LOOP
2436 FOR j IN 1 .. g_lot_attributes_tbl.COUNT
2437 LOOP
2438 IF (UPPER (g_lot_attributes_tbl (j).column_name) =
2439 UPPER (p_parent_lot_attr_tbl (i).column_name)
2440 )
2441 THEN
2442 IF (l_debug = 1)
2443 THEN
2444 print_debug (g_lot_attributes_tbl (j).column_name
2445 , 'Validate_Attributes'
2446 );
2447 END IF;
2448
2449 IF (g_lot_attributes_tbl (j).column_value IS NULL)
2450 THEN
2451 g_lot_attributes_tbl (j).column_value :=
2452 p_parent_lot_attr_tbl (i).column_value;
2453
2454 IF (l_debug = 1)
2455 THEN
2456 print_debug ( g_lot_attributes_tbl (j).column_name
2457 || ' '
2458 || g_lot_attributes_tbl (j).column_value
2459 , 'Validate_Attributes'
2460 );
2461 END IF;
2462 END IF;
2463 END IF;
2464
2465 EXIT WHEN (UPPER (g_lot_attributes_tbl (j).column_name) =
2466 UPPER (p_parent_lot_attr_tbl (i).column_name)
2467 );
2468 END LOOP;
2469 END LOOP;
2470 END IF;
2471 END IF;
2472
2473 -- Check to see if the passed value for the lot attribute context for
2474 -- the resultant lot is different than the one for the parent lot.
2475 -- If so, raise an error if it is a lot split or a merge transaction.
2476
2477 /*** Check to see if the segments are filled in and the context is
2478 null ****/
2479 /**** Do not need this check since some of the attributes fill in
2480 as zero and the count will be more than 1 even if the attributes
2481 arent filled in
2482 IF (l_rs_lot_attr_category IS NULL AND l_count > 0) THEN
2483 print_debug('Resultant lot category is null', 'Validate_Attributes');
2484 fnd_message.set_name('INV', 'INV_VALID_CAT');
2485 fnd_msg_pub.add;
2486 raise FND_API.G_EXC_ERROR;
2487 end if;
2488
2489 ******/
2490 FOR i IN 1 .. p_parent_lot_attr_tbl.COUNT
2491 LOOP
2492 IF (UPPER (p_parent_lot_attr_tbl (i).column_name) =
2493 'LOT_ATTRIBUTE_CATEGORY'
2494 )
2495 THEN
2496 l_st_lot_attr_category := p_parent_lot_attr_tbl (i).column_value;
2497 END IF;
2498
2499 EXIT WHEN (UPPER (p_parent_lot_attr_tbl (i).column_name) =
2500 'LOT_ATTRIBUTE_CATEGORY'
2501 );
2502 END LOOP;
2503
2504 IF ( (p_transaction_type_id = inv_globals.g_type_inv_lot_merge)
2505 OR (p_transaction_type_id = inv_globals.g_type_inv_lot_split)
2506 )
2507 AND (l_st_lot_attr_category <> l_rs_lot_attr_category)
2508 THEN
2509 print_debug ( 'Lot categories mismatch: '
2510 || l_st_lot_attr_category
2511 || ','
2512 || l_rs_lot_attr_category
2513 , 'Validate_Attributes'
2514 );
2515 fnd_message.set_name ('INV', 'INV_VALID_CAT');
2516 fnd_msg_pub.ADD;
2517 RAISE fnd_api.g_exc_error;
2518 END IF;
2519
2520 -- parent lot does not have attributes.
2521 -- use default lot attributes.
2522 IF (l_debug = 1)
2523 THEN
2524 print_debug ('Calling inv_lot_sel_attr.get_default'
2525 , 'Validate_Attributes'
2526 );
2527 END IF;
2528
2529 inv_lot_sel_attr.get_default
2530 (x_attributes_default => l_attributes_default
2531 , x_attributes_default_count => l_attributes_default_count
2532 , x_return_status => l_return_status
2533 , x_msg_count => l_msg_count
2534 , x_msg_data => x_msg_data
2535 , p_table_name => 'MTL_LOT_NUMBERS'
2536 , p_attributes_name => 'Lot Attributes'
2537 , p_inventory_item_id => p_inventory_item_id
2538 , p_organization_id => p_organization_id
2539 , p_lot_serial_number => p_lot_number
2540 , p_attributes => g_lot_attributes_tbl
2541 );
2542
2543 IF (l_return_status <> fnd_api.g_ret_sts_success)
2544 THEN
2545 x_validation_status := 'N';
2546 x_return_status := l_return_status;
2547 RAISE fnd_api.g_exc_unexpected_error;
2548 END IF;
2549
2550 IF (l_debug = 1)
2551 THEN
2552 print_debug ( 'l_attributes_default_count is '
2553 || l_attributes_default_count
2554 , 'Validate_Attributes'
2555 );
2556 END IF;
2557
2558 IF (l_attributes_default_count > 0)
2559 THEN
2560 FOR i IN 1 .. l_attributes_default_count
2561 LOOP
2562 FOR j IN 1 .. g_lot_attributes_tbl.COUNT
2563 LOOP
2564 IF ( UPPER (l_attributes_default (i).column_name) =
2565 UPPER (g_lot_attributes_tbl (j).column_name)
2566 AND l_attributes_default (i).column_value IS NOT NULL
2567 )
2568 THEN
2569 IF (l_debug = 1)
2570 THEN
2571 print_debug ( 'g_lot_attributes_tbl(j).COLUMN_VALUE is '
2572 || g_lot_attributes_tbl (j).column_value
2573 , 'Validate_attributes'
2574 );
2575 print_debug ( 'l_attributes_default(i).COLUMN_VALUE is '
2576 || l_attributes_default (i).column_value
2577 , 'Validate_attributes'
2578 );
2579 END IF;
2580
2581 IF (g_lot_attributes_tbl (j).column_value IS NULL)
2582 THEN
2583 g_lot_attributes_tbl (j).column_value :=
2584 l_attributes_default (i).column_value;
2585 END IF;
2586
2587 g_lot_attributes_tbl (j).required :=
2588 l_attributes_default (i).required;
2589
2590 IF (l_debug = 1)
2591 THEN
2592 print_debug ( 'g_lot_attributes_tbl(j).COLUMN_VALUE is '
2593 || g_lot_attributes_tbl (j).column_value
2594 , 'Validate_attributes'
2595 );
2596 END IF;
2597 END IF;
2598
2599 EXIT WHEN (UPPER (l_attributes_default (i).column_name) =
2600 UPPER (g_lot_attributes_tbl (j).column_name)
2601 );
2602 END LOOP;
2603 END LOOP;
2604 END IF;
2605
2606 IF (l_debug = 1)
2607 THEN
2608 print_debug ('calling fnd_dflex.get_flexfield', 'Validate_Attributes');
2609 END IF;
2610
2611 -- Get flexfield
2612 fnd_dflex.get_flexfield ('INV', l_attributes_name, v_flexfield
2613 , v_flexinfo);
2614
2615 IF (l_debug = 1)
2616 THEN
2617 print_debug ('calling fnd_dflex.get_context', 'Validate_Attributes');
2618 END IF;
2619
2620 -- Get Contexts
2621 l_context_value := NULL;
2622 fnd_dflex.get_contexts (v_flexfield, v_contexts);
2623
2624 --will get the number of contexts, their name etc
2625
2626 --till now we have populated the attributes in the g_lot_attributes table...now we
2627 --need to validate these values
2628
2629 --loop to get the context value for the context lot_attribute_category and poplate
2630 --the right column in g_lot_attributes table
2631 FOR i IN 1 .. g_lot_attributes_tbl.COUNT
2632 LOOP
2633 IF ( UPPER (g_lot_attributes_tbl (i).column_name) =
2634 'LOT_ATTRIBUTE_CATEGORY'
2635 AND g_lot_attributes_tbl (i).column_value IS NULL
2636 )
2637 THEN
2638 inv_lot_sel_attr.get_context_code (l_context_value
2639 , p_organization_id
2640 , p_inventory_item_id
2641 , l_attributes_name
2642 );
2643 g_lot_attributes_tbl (i).column_value := l_context_value;
2644 ELSE
2645 l_context_value := g_lot_attributes_tbl (i).column_value;
2646 END IF;
2647
2648 EXIT WHEN (UPPER (g_lot_attributes_tbl (i).column_name) =
2649 'LOT_ATTRIBUTE_CATEGORY'
2650 );
2651 END LOOP;
2652
2653 IF (l_debug = 1)
2654 THEN
2655 print_debug ('l_context_value is ' || l_context_value
2656 , 'Validate_Attributes'
2657 );
2658 END IF;
2659
2660 /* 2725380 */
2661 IF ((l_enabled_attributes = 0) AND (l_context_value IS NULL))
2662 THEN
2663 -- return no lot attributes segment is enabled
2664 IF (l_debug = 1)
2665 THEN
2666 print_debug ('l_context is null , attr enabaled = 0'
2667 , 'Validate_Attributes'
2668 );
2669 END IF;
2670
2671 x_validation_status := 'Y';
2672 x_return_status := fnd_api.g_ret_sts_success;
2673 x_msg_count := 0;
2674 x_msg_data := NULL;
2675 --x_lot_attr_tbl := p_result_lot_attr_tbl;
2676 RETURN;
2677 END IF;
2678
2679 IF l_context_value IS NOT NULL
2680 THEN
2681 IF (l_debug = 1)
2682 THEN
2683 print_debug ('calling fnd_flex_descval.set_context_value'
2684 , 'Validate_Attributes'
2685 );
2686 END IF;
2687
2688 fnd_flex_descval.set_context_value (l_context_value);
2689
2690 IF (l_debug = 1)
2691 THEN
2692 print_debug ('calling fnd_flex_descval.clear_column_values'
2693 , 'Validate_Attributes'
2694 );
2695 END IF;
2696
2697 fnd_flex_descval.clear_column_values;
2698
2699 IF (l_debug = 1)
2700 THEN
2701 print_debug
2702 ( 'calling fnd_flex_descval.clear_column_values LOT_ATTRIBUTE_CATEGORY = '
2703 || l_context_value
2704 , 'Validate_Attributes'
2705 );
2706 END IF;
2707
2708 fnd_flex_descval.set_column_value ('LOT_ATTRIBUTE_CATEGORY'
2709 , l_context_value
2710 );
2711
2712 -- Setting the Values for Validating
2713 IF (l_debug = 1)
2714 THEN
2715 print_debug ( 'g_lot_attributes_tbl.COUNT is '
2716 || g_lot_attributes_tbl.COUNT
2717 , 'Validate_Attributes'
2718 );
2719 END IF;
2720
2721 FOR i IN 1 .. v_contexts.ncontexts
2722 LOOP
2723 IF ( v_contexts.is_enabled (i)
2724 AND ( (UPPER (v_contexts.context_code (i)) =
2725 UPPER (l_context_value)
2726 )
2727 OR v_contexts.is_global (i)
2728 )
2729 )
2730 THEN
2731 --get the segments that have been enabled for this context
2732 -- Get segments
2733 IF (l_debug = 1)
2734 THEN
2735 print_debug ('calling fnd_dflex.get_segments'
2736 , 'Validate_Attributes'
2737 );
2738 END IF;
2739 fnd_dflex.get_segments
2740 (fnd_dflex.make_context (v_flexfield
2741 , v_contexts.context_code (i)
2742 )
2743 , v_segments
2744 , TRUE
2745 );
2746
2747 <<segmentloop>>
2748 FOR j IN 1 .. v_segments.nsegments
2749 LOOP
2750 IF v_segments.is_enabled (j)
2751 THEN
2752 v_colname := v_segments.application_column_name (j);
2753
2754 IF (l_debug = 1)
2755 THEN
2756 print_debug ('v_colName is ' || v_colname
2757 , 'Validate_Attributes'
2758 );
2759 END IF;
2760
2761 <<columnloop>>
2762 FOR k IN 1 .. g_lot_attributes_tbl.COUNT
2763 LOOP
2764 IF UPPER (v_colname) =
2765 UPPER (g_lot_attributes_tbl (k).column_name)
2766 THEN
2767 IF (l_debug = 1)
2768 THEN
2769 print_debug (g_lot_attributes_tbl (k).column_name
2770 , 'Validate_attributes'
2771 );
2772 END IF;
2773
2774 -- Sets the Values for Validation
2775 -- Setting the column data type for validation
2776 IF g_lot_attributes_tbl (k).column_type = 'DATE'
2777 THEN
2778 IF (l_debug = 1)
2779 THEN
2780 print_debug ( 'set_column_value '
2781 || g_lot_attributes_tbl (k).column_value
2782 , 'Validate_Attributes'
2783 );
2784 END IF;
2785
2786 fnd_flex_descval.set_column_value
2787 (g_lot_attributes_tbl (k).column_name
2788 , fnd_date.canonical_to_date
2789 (g_lot_attributes_tbl (k).column_value
2790 )
2791 );
2792 END IF;
2793
2794 IF g_lot_attributes_tbl (k).column_type = 'NUMBER'
2795 THEN
2796 IF (l_debug = 1)
2797 THEN
2798 print_debug ( 'set_column_value '
2799 || g_lot_attributes_tbl (k).column_value
2800 , 'Validate_Attributes'
2801 );
2802 END IF;
2803
2804 fnd_flex_descval.set_column_value
2805 (g_lot_attributes_tbl (k).column_name
2806 , TO_NUMBER (g_lot_attributes_tbl (k).column_value
2807 )
2808 );
2809 END IF;
2810
2811 IF g_lot_attributes_tbl (k).column_type = 'VARCHAR2'
2812 THEN
2813 IF (l_debug = 1)
2814 THEN
2815 print_debug ( 'set_column_value '
2816 || g_lot_attributes_tbl (k).column_value
2817 , 'Validate_Attributes'
2818 );
2819 END IF;
2820
2821 fnd_flex_descval.set_column_value
2822 (g_lot_attributes_tbl (k).column_name
2823 , g_lot_attributes_tbl (k).column_value
2824 );
2825 END IF;
2826
2827 IF (v_segments.is_required (j))
2828 THEN
2829 IF (g_lot_attributes_tbl (k).column_value IS NULL)
2830 THEN
2831 IF (l_debug = 1)
2832 THEN
2833 print_debug ( g_lot_attributes_tbl (k).column_name
2834 || ' '
2835 || g_lot_attributes_tbl (k).column_value
2836 , 'Validate_Attributes'
2837 );
2838 END IF;
2839
2840 fnd_message.set_name ('INV'
2841 , 'INV_LOT_SEL_DEFAULT_REQUIRED'
2842 );
2843 fnd_message.set_token ('ATTRNAME', l_attributes_name);
2844 fnd_message.set_token ('CONTEXTCODE'
2845 , v_contexts.context_code (i)
2846 );
2847 fnd_message.set_token
2848 ('SEGMENT'
2849 , v_segments.application_column_name
2850 (j)
2851 );
2852 fnd_msg_pub.ADD;
2853 END IF;
2854 END IF;
2855 END IF;
2856
2857 EXIT WHEN (UPPER (v_colname) =
2858 UPPER (g_lot_attributes_tbl (k).column_name)
2859 );
2860 END LOOP;
2861 END IF;
2862 END LOOP;
2863 END IF;
2864 END LOOP;
2865
2866 --now all the values have been set for the global variables
2867 -- Call the validating routine for Lot Attributes.
2868 IF (l_debug = 1)
2869 THEN
2870 print_debug ('calling fnd_flex_descval.validate_desccols'
2871 , 'Validate_Attributes'
2872 );
2873 END IF;
2874
2875 l_status :=
2876 fnd_flex_descval.validate_desccols
2877 (appl_short_name => 'INV'
2878 , desc_flex_name => l_attributes_name
2879 );
2880
2881 IF l_status = TRUE
2882 THEN
2883 IF (l_debug = 1)
2884 THEN
2885 print_debug ('l_status is true', 'Validate_Attributes');
2886 END IF;
2887
2888 l_validation_status := 'Y';
2889 ELSE
2890 IF (l_debug = 1)
2891 THEN
2892 print_debug ('l_status is false', 'Validate_Attributes');
2893 END IF;
2894
2895 l_validation_status := 'N';
2896 x_return_status := fnd_api.g_ret_sts_error;
2897 x_msg_data := fnd_flex_descval.error_message;
2898 fnd_message.set_name ('INV', 'GENERIC');
2899 fnd_message.set_token ('MSGBODY', x_msg_data);
2900 fnd_msg_pub.ADD;
2901 x_msg_count := NVL (x_msg_count, 0) + 1;
2902 RAISE fnd_api.g_exc_error;
2903 END IF;
2904 ELSE
2905 -- no context found;
2906 l_validation_status := 'Y';
2907 x_return_status := fnd_api.g_ret_sts_success;
2908 fnd_msg_pub.count_and_get (p_count => x_msg_count
2909 , p_data => x_msg_data);
2910 END IF; -- if l_context_value is not null
2911
2912 x_lot_attr_tbl := g_lot_attributes_tbl;
2913 x_validation_status := l_validation_status;
2914 EXCEPTION
2915 WHEN fnd_api.g_exc_error
2916 THEN
2917 x_validation_status := l_validation_status;
2918 x_return_status := fnd_api.g_ret_sts_error;
2919 fnd_msg_pub.count_and_get (p_count => x_msg_count
2920 , p_data => x_msg_data);
2921 WHEN fnd_api.g_exc_unexpected_error
2922 THEN
2923 x_validation_status := l_validation_status;
2924 x_return_status := fnd_api.g_ret_sts_unexp_error;
2925 fnd_msg_pub.count_and_get (p_count => x_msg_count
2926 , p_data => x_msg_data);
2927 WHEN OTHERS
2928 THEN
2929 x_validation_status := l_validation_status;
2930 x_return_status := fnd_api.g_ret_sts_unexp_error;
2931
2932 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
2933 THEN
2934 fnd_msg_pub.add_exc_msg (g_pkg_name, 'Validate_Attributes');
2935 END IF;
2936
2937 fnd_msg_pub.count_and_get (p_count => x_msg_count
2938 , p_data => x_msg_data);
2939 END validate_attributes;
2940
2941 /*********************************************************************************
2942 *Added for OSFM support to Serialized Lot Items. Populates the *
2943 *g_lot_ser_attributes_tbl with the attribute columns present in the mtl_serial_ *
2944 *numbers_interface *
2945 *********************************************************************************/
2946 PROCEDURE populate_serial_attributes
2947 IS
2948 CURSOR column_csr (p_table_name VARCHAR2, p_owner VARCHAR2)
2949 IS
2950 SELECT column_name
2951 , data_type
2952 FROM all_tab_columns
2953 WHERE table_name = p_table_name AND owner = p_owner
2954 /*Bug:4724150. Commented the following condition 1 as the attribute
2955 columns becomes out of range of 20 to 91 when some extraneous attributes are added*/
2956 --AND column_id BETWEEN 20 AND 91
2957 ORDER BY column_id;
2958
2959 l_column_idx BINARY_INTEGER := 0;
2960 l_ret BOOLEAN;
2961 l_status VARCHAR2 (1);
2962 l_industry VARCHAR2 (1);
2963 l_oracle_schema VARCHAR2 (30);
2964 l_debug NUMBER := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
2965 BEGIN
2966 IF (l_debug = 1)
2967 THEN
2968 print_debug ('IN populate_serial_attributes'
2969 , 'populate_serial_Attributes'
2970 );
2971 END IF;
2972
2973 l_ret :=
2974 fnd_installation.get_app_info ('INV'
2975 , l_status
2976 , l_industry
2977 , l_oracle_schema
2978 );
2979
2980 FOR l_column_csr IN column_csr ('MTL_SERIAL_NUMBERS_INTERFACE'
2981 , l_oracle_schema
2982 )
2983 LOOP
2984 l_column_idx := l_column_idx + 1;
2985 g_lot_ser_attributes_tbl (l_column_idx).column_name :=
2986 l_column_csr.column_name;
2987 g_lot_ser_attributes_tbl (l_column_idx).column_type :=
2988 l_column_csr.data_type;
2989 END LOOP;
2990
2991 IF (l_debug = 1)
2992 THEN
2993 print_debug ('Done with populate_serial_attributes'
2994 , 'populate_serial_Attributes'
2995 );
2996 END IF;
2997 END;
2998
2999
3000 /*********************************************************************************
3001 *Added for OSFM support to Serialized Lot Items. *
3002 *Validates the resulting serials attributes. If the attributes are not present *
3003 *for the resulting serials then the default serial attributes are taken *
3004 *These attributes are then validated using the descriptive flexfield validation *
3005 *APIs *
3006 *********************************************************************************/
3007 PROCEDURE validate_serial_attributes (
3008 x_return_status OUT NOCOPY VARCHAR2
3009 , x_msg_count OUT NOCOPY NUMBER
3010 , x_msg_data OUT NOCOPY VARCHAR2
3011 , x_validation_status OUT NOCOPY VARCHAR2
3012 , x_ser_attr_tbl OUT NOCOPY inv_lot_sel_attr.lot_sel_attributes_tbl_type
3013 , p_ser_number IN VARCHAR2
3014 , p_organization_id IN NUMBER
3015 , p_inventory_item_id IN NUMBER
3016 , p_result_ser_attr_tbl IN inv_lot_sel_attr.lot_sel_attributes_tbl_type
3017 )
3018 IS
3019 l_attributes_name VARCHAR2 (50) := 'Serial Attributes';
3020 v_flexfield fnd_dflex.dflex_r;
3021 v_flexinfo fnd_dflex.dflex_dr;
3022 v_contexts fnd_dflex.contexts_dr;
3023 v_segments fnd_dflex.segments_dr;
3024 l_attributes_default_count NUMBER;
3025 l_enabled_attributes NUMBER;
3026 l_attributes_default inv_lot_sel_attr.lot_sel_attributes_tbl_type;
3027 v_context_value mtl_flex_context.descriptive_flex_context_code%TYPE;
3028 v_colname VARCHAR2 (50);
3029 l_context_value VARCHAR2 (150);
3030 l_return_status VARCHAR2 (1);
3031 l_msg_data VARCHAR2 (255);
3032 l_msg_count NUMBER;
3033 l_validation_status VARCHAR2 (1);
3034 l_status BOOLEAN;
3035 l_count NUMBER := 0;
3036 l_rs_ser_attr_category VARCHAR2 (30);
3037 l_st_ser_attr_category VARCHAR2 (30);
3038 l_debug NUMBER
3039 := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
3040 BEGIN
3041 -- call to see if the serial attributes is enabled for this item/org/category combination
3042 IF (l_debug = 1)
3043 THEN
3044 print_debug ('In Validate_serial_Attributes'
3045 , 'Validate_serial_Attributes'
3046 );
3047 END IF;
3048
3049 l_validation_status := 'Y';
3050 x_return_status := fnd_api.g_ret_sts_success;
3051 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3052 l_enabled_attributes :=
3053 inv_lot_sel_attr.is_enabled (p_flex_name => l_attributes_name
3054 , p_organization_id => p_organization_id
3055 , p_inventory_item_id => p_inventory_item_id
3056 );
3057
3058 -- if we are here, means there are some enabled segment, some can be required.
3059 IF (l_debug = 1)
3060 THEN
3061 print_debug ('calling populate_serila_attributes'
3062 , 'validate_serial_attributes'
3063 );
3064 END IF;
3065
3066 populate_serial_attributes;
3067
3068 -- Check to see if the values have been passed for the resultant
3069 -- serials - else pass the values for the parent serial.
3070 FOR i IN 1 .. p_result_ser_attr_tbl.COUNT
3071 LOOP
3072 IF (p_result_ser_attr_tbl (i).column_value IS NOT NULL)
3073 THEN
3074 IF (l_debug = 1)
3075 THEN
3076 print_debug ( 'Column_NAME is '
3077 || p_result_ser_attr_tbl (i).column_name
3078 , 'validate_serial_attributes'
3079 );
3080 print_debug ( 'Column Value is '
3081 || p_result_ser_attr_tbl (i).column_value
3082 , 'validate_serial_attributes'
3083 );
3084 END IF;
3085
3086 l_count := l_count + 1;
3087 END IF;
3088
3089 IF (UPPER (p_result_ser_attr_tbl (i).column_name) =
3090 'SERIAL_ATTRIBUTE_CATEGORY'
3091 )
3092 THEN
3093 l_rs_ser_attr_category := p_result_ser_attr_tbl (i).column_value;
3094 --contains what is the attr category for this
3095 END IF;
3096 END LOOP;
3097
3098 IF (l_count > 0)
3099 THEN
3100 FOR i IN 1 .. p_result_ser_attr_tbl.COUNT
3101 LOOP
3102 FOR j IN 1 .. g_lot_ser_attributes_tbl.COUNT
3103 LOOP
3104 IF (UPPER (g_lot_ser_attributes_tbl (j).column_name) =
3105 UPPER (p_result_ser_attr_tbl (i).column_name)
3106 )
3107 THEN
3108 g_lot_ser_attributes_tbl (j).column_value :=
3109 p_result_ser_attr_tbl (i).column_value;
3110 END IF;
3111
3112 EXIT WHEN (UPPER (g_lot_ser_attributes_tbl (j).column_name) =
3113 UPPER (p_result_ser_attr_tbl (i).column_name)
3114 );
3115 END LOOP;
3116 END LOOP;
3117 --for serials we do not care abt the parent serials
3118 END IF;
3119
3120
3121
3122 /*Removing the check to see if the parent serial attribute category meets
3123 child serial attribute category becoz we are not copying from parent
3124 */
3125
3126
3127 -- use default serial attributes.
3128 BEGIN
3129 inv_lot_sel_attr.get_default
3130 (x_attributes_default => l_attributes_default
3131 , x_attributes_default_count => l_attributes_default_count
3132 , x_return_status => x_return_status
3133 , x_msg_count => x_msg_count
3134 , x_msg_data => x_msg_data
3135 , p_table_name => 'MTL_SERIAL_NUMBERS'
3136 , p_attributes_name => 'Serial Attributes'
3137 , p_inventory_item_id => p_inventory_item_id
3138 , p_organization_id => p_organization_id
3139 , p_lot_serial_number => p_ser_number
3140 , p_attributes => g_lot_ser_attributes_tbl
3141 );
3142 EXCEPTION
3143 WHEN OTHERS
3144 THEN
3145 l_validation_status := 'N';
3146 RAISE fnd_api.g_exc_unexpected_error;
3147 END;
3148
3149 IF (x_return_status <> fnd_api.g_ret_sts_success)
3150 THEN
3151 l_validation_status := 'N';
3152 RAISE fnd_api.g_exc_unexpected_error;
3153 END IF;
3154
3155 IF (l_attributes_default_count > 0)
3156 THEN
3157 FOR i IN 1 .. l_attributes_default_count
3158 LOOP
3159 FOR j IN 1 .. g_lot_ser_attributes_tbl.COUNT
3160 LOOP
3161 IF ( UPPER (l_attributes_default (i).column_name) =
3162 UPPER (g_lot_ser_attributes_tbl (j).column_name)
3163 AND l_attributes_default (i).column_value IS NOT NULL
3164 )
3165 THEN
3166 IF (l_debug = 1)
3167 THEN
3168 print_debug ( 'g_lot_ser_attributes_tbl(j).COLUMN_VALUE is '
3169 || g_lot_ser_attributes_tbl (j).column_value
3170 , 'validate_serial_attributes'
3171 );
3172 print_debug ( 'l_attributes_default(i).COLUMN_VALUE is '
3173 || l_attributes_default (i).column_value
3174 , 'validate_serial_attributes'
3175 );
3176 END IF;
3177
3178 IF (g_lot_ser_attributes_tbl (j).column_value IS NULL)
3179 THEN
3180 g_lot_ser_attributes_tbl (j).column_value :=
3181 l_attributes_default (i).column_value;
3182 END IF;
3183
3184 g_lot_ser_attributes_tbl (j).required :=
3185 l_attributes_default (i).required;
3186
3187 IF (l_debug = 1)
3188 THEN
3189 print_debug ( 'g_lot_ser_attributes_tbl(j).COLUMN_VALUE is '
3190 || g_lot_ser_attributes_tbl (j).column_value
3191 , 'validate_serial_attributes'
3192 );
3193 END IF;
3194 END IF;
3195
3196 EXIT WHEN (UPPER (l_attributes_default (i).column_name) =
3197 UPPER (g_lot_ser_attributes_tbl (j).column_name)
3198 );
3199 END LOOP;
3200 END LOOP;
3201 END IF;
3202
3203 IF (l_debug = 1)
3204 THEN
3205 print_debug ('calling fnd_dflex.get_flexfield'
3206 , 'validate_serial_attributes'
3207 );
3208 END IF;
3209
3210 -- Get flexfield
3211 fnd_dflex.get_flexfield ('INV', l_attributes_name, v_flexfield
3212 , v_flexinfo);
3213
3214 IF (l_debug = 1)
3215 THEN
3216 print_debug ('calling fnd_dflex.get_context'
3217 , 'validate_serial_attributes'
3218 );
3219 END IF;
3220
3221 -- Get Contexts
3222 l_context_value := NULL;
3223 fnd_dflex.get_contexts (v_flexfield, v_contexts);
3224
3225 --will get the number of contexts, their name etc
3226
3227 --till now we have populated the attributes in the g_lot_attributes table...now we
3228 --need to validate these values
3229
3230 --loop to get the context value for the context lot_attribute_category and poplate
3231 --the right column in g_lot_attributes table
3232 FOR i IN 1 .. g_lot_ser_attributes_tbl.COUNT
3233 LOOP
3234 IF ( UPPER (g_lot_ser_attributes_tbl (i).column_name) =
3235 'SERIAL_ATTRIBUTE_CATEGORY'
3236 AND g_lot_ser_attributes_tbl (i).column_value IS NULL
3237 )
3238 THEN
3239 inv_lot_sel_attr.get_context_code (l_context_value
3240 , p_organization_id
3241 , p_inventory_item_id
3242 , l_attributes_name
3243 );
3244 g_lot_ser_attributes_tbl (i).column_value := l_context_value;
3245 ELSE
3246 l_context_value := g_lot_ser_attributes_tbl (i).column_value;
3247 END IF;
3248
3249 EXIT WHEN (UPPER (g_lot_ser_attributes_tbl (i).column_name) =
3250 'SERIAL_ATTRIBUTE_CATEGORY'
3251 );
3252 END LOOP;
3253
3254 IF (l_debug = 1)
3255 THEN
3256 print_debug ('l_context_value is ' || l_context_value
3257 , 'validate_serial_attributes'
3258 );
3259 END IF;
3260
3261 IF ((l_enabled_attributes = 0) AND (l_context_value IS NULL))
3262 THEN
3263 -- return no lot attributes segment is enabled
3264 IF (l_debug = 1)
3265 THEN
3266 print_debug ('l_context is null , attr enabaled = 0'
3267 , 'validate_serial_attributes'
3268 );
3269 END IF;
3270
3271 x_validation_status := 'Y';
3272 x_return_status := fnd_api.g_ret_sts_success;
3273 x_msg_count := 0;
3274 x_msg_data := NULL;
3275 x_ser_attr_tbl := g_lot_ser_attributes_tbl;
3276 RETURN;
3277 END IF;
3278
3279 IF l_context_value IS NOT NULL
3280 THEN
3281 IF (l_debug = 1)
3282 THEN
3283 print_debug ('calling fnd_flex_descval.set_context_value'
3284 , 'validate_serial_attributes'
3285 );
3286 END IF;
3287
3288 fnd_flex_descval.set_context_value (l_context_value);
3289
3290 IF (l_debug = 1)
3291 THEN
3292 print_debug ('calling fnd_flex_descval.clear_column_values'
3293 , 'validate_serial_attributes'
3294 );
3295 END IF;
3296
3297 fnd_flex_descval.clear_column_values;
3298
3299 IF (l_debug = 1)
3300 THEN
3301 print_debug
3302 ( 'calling fnd_flex_descval.clear_column_values SERIAL_ATTRIBUTE_CATEGORY = '
3303 || l_context_value
3304 , 'validate_serial_attributes'
3305 );
3306 END IF;
3307
3308 fnd_flex_descval.set_column_value ('SERIAL_ATTRIBUTE_CATEGORY'
3309 , l_context_value
3310 );
3311
3312 -- Setting the Values for Validating
3313 IF (l_debug = 1)
3314 THEN
3315 print_debug ( 'g_lot_ser_attributes_tbl.COUNT is '
3316 || g_lot_ser_attributes_tbl.COUNT
3317 , 'validate_serial_attributes'
3318 );
3319 END IF;
3320
3321 /*contenets of the v_contexts : -
3322 (ncontexts BINARY_INTEGER,
3323 global_context BINARY_INTEGER,
3324 context_code context_code_a,
3325 context_name context_name_a,
3326 context_description context_description_a,
3327 is_enabled boolean_a,
3328 is_global boolean_a)
3329 */
3330 FOR i IN 1 .. v_contexts.ncontexts
3331 LOOP
3332 IF ( v_contexts.is_enabled (i)
3333 AND ( (UPPER (v_contexts.context_code (i)) =
3334 UPPER (l_context_value)
3335 )
3336 OR v_contexts.is_global (i)
3337 )
3338 )
3339 THEN
3340 --get the segments that have been enabled for this context
3341 -- Get segments
3342 IF (l_debug = 1)
3343 THEN
3344 print_debug ('calling fnd_dflex.get_segments'
3345 , 'validate_serial_attributes'
3346 );
3347 END IF;
3348
3349 /* v_segmenst contains following :-
3350 (nsegments BINARY_INTEGER,
3351 application_column_name application_column_name_a,
3352 segment_name segment_name_a,
3353 sequence sequence_a,
3354 is_displayed boolean_a,
3355 display_size display_size_a,
3356 row_prompt row_prompt_a,
3357 column_prompt column_prompt_a,
3358 is_enabled boolean_a,
3359 is_required boolean_a,
3360 description segment_description_a,
3361 value_set value_set_a,
3362 default_type default_type_a,
3363 default_value default_value_a)
3364 */
3365 fnd_dflex.get_segments
3366 (fnd_dflex.make_context (v_flexfield
3367 , v_contexts.context_code (i)
3368 )
3369 , v_segments
3370 , TRUE
3371 );
3372
3373 <<segmentloop>>
3374 FOR j IN 1 .. v_segments.nsegments
3375 LOOP
3376 IF v_segments.is_enabled (j)
3377 THEN
3378 v_colname := v_segments.application_column_name (j);
3379
3380 IF (l_debug = 1)
3381 THEN
3382 print_debug ('v_colName is ' || v_colname
3383 , 'validate_serial_attributes'
3384 );
3385 END IF;
3386
3387 <<columnloop>>
3388 FOR k IN 1 .. g_lot_ser_attributes_tbl.COUNT
3389 LOOP
3390 IF UPPER (v_colname) =
3391 UPPER (g_lot_ser_attributes_tbl (k).column_name)
3392 THEN
3393 IF (l_debug = 1)
3394 THEN
3395 print_debug (g_lot_ser_attributes_tbl (k).column_name
3396 , 'validate_serial_attributes'
3397 );
3398 END IF;
3399
3400 -- Sets the Values for Validation
3401 -- Setting the column data type for validation
3402 IF g_lot_ser_attributes_tbl (k).column_type = 'DATE'
3403 THEN
3404 IF (l_debug = 1)
3405 THEN
3406 print_debug ( 'set_column_value '
3407 || g_lot_ser_attributes_tbl (k).column_value
3408 , 'validate_serial_attributes'
3409 );
3410 END IF;
3411
3412 fnd_flex_descval.set_column_value
3413 (g_lot_ser_attributes_tbl (k).column_name
3414 , fnd_date.canonical_to_date
3415 (g_lot_ser_attributes_tbl (k).column_value
3416 )
3417 );
3418 END IF;
3419
3420 IF g_lot_ser_attributes_tbl (k).column_type = 'NUMBER'
3421 THEN
3422 IF (l_debug = 1)
3423 THEN
3424 print_debug ( 'set_column_value '
3425 || g_lot_ser_attributes_tbl (k).column_value
3426 , 'validate_serial_attributes'
3427 );
3428 END IF;
3429
3430 fnd_flex_descval.set_column_value
3431 (g_lot_ser_attributes_tbl (k).column_name
3432 , TO_NUMBER (g_lot_ser_attributes_tbl (k).column_value
3433 )
3434 );
3435 END IF;
3436
3437 IF g_lot_ser_attributes_tbl (k).column_type = 'VARCHAR2'
3438 THEN
3439 IF (l_debug = 1)
3440 THEN
3441 print_debug ( 'set_column_value '
3442 || g_lot_ser_attributes_tbl (k).column_value
3443 , 'validate_serial_attributes'
3444 );
3445 END IF;
3446
3447 fnd_flex_descval.set_column_value
3448 (g_lot_ser_attributes_tbl (k).column_name
3449 , g_lot_ser_attributes_tbl (k).column_value
3450 );
3451 END IF;
3452
3453 IF (v_segments.is_required (j))
3454 THEN
3455 IF (g_lot_ser_attributes_tbl (k).column_value IS NULL)
3456 THEN
3457 IF (l_debug = 1)
3458 THEN
3459 print_debug
3460 ( g_lot_ser_attributes_tbl (k).column_name
3461 || ' '
3462 || g_lot_ser_attributes_tbl (k).column_value
3463 , 'validate_serial_attributes'
3464 );
3465 END IF;
3466
3467 fnd_message.set_name ('INV'
3468 , 'INV_LOT_SEL_DEFAULT_REQUIRED'
3469 );
3470 fnd_message.set_token ('ATTRNAME', l_attributes_name);
3471 fnd_message.set_token ('CONTEXTCODE'
3472 , v_contexts.context_code (i)
3473 );
3474 fnd_message.set_token
3475 ('SEGMENT'
3476 , v_segments.application_column_name
3477 (j)
3478 );
3479 fnd_msg_pub.ADD;
3480 END IF;
3481 END IF;
3482 END IF;
3483
3484 EXIT WHEN (UPPER (v_colname) =
3485 UPPER (g_lot_ser_attributes_tbl (k).column_name)
3486 );
3487 END LOOP;
3488 END IF;
3489 END LOOP;
3490 END IF;
3491 END LOOP;
3492
3493 --now all the values have been set for the global variables
3494 -- Call the validating routine for Lot Attributes.
3495 IF (l_debug = 1)
3496 THEN
3497 print_debug ('calling fnd_flex_descval.validate_desccols'
3498 , 'validate_serial_attributes'
3499 );
3500 END IF;
3501
3502 l_status :=
3503 fnd_flex_descval.validate_desccols
3504 (appl_short_name => 'INV'
3505 , desc_flex_name => l_attributes_name
3506 );
3507
3508 IF l_status = TRUE
3509 THEN
3510 IF (l_debug = 1)
3511 THEN
3512 print_debug ('l_status is true', 'validate_serial_attributes');
3513 END IF;
3514
3515 l_validation_status := 'Y';
3516 ELSE
3517 IF (l_debug = 1)
3518 THEN
3519 print_debug ('l_status is false', 'validate_serial_attributes');
3520 END IF;
3521
3522 l_validation_status := 'N';
3523 x_return_status := fnd_api.g_ret_sts_error;
3524 x_msg_data := fnd_flex_descval.error_message;
3525 fnd_message.set_name ('INV', 'GENERIC');
3526 fnd_message.set_token ('MSGBODY', x_msg_data);
3527 fnd_msg_pub.ADD;
3528 x_msg_count := NVL (x_msg_count, 0) + 1;
3529 RAISE fnd_api.g_exc_error;
3530 END IF;
3531 ELSE
3532 -- no context found;
3533 l_validation_status := 'Y';
3534 x_return_status := fnd_api.g_ret_sts_success;
3535 fnd_msg_pub.count_and_get (p_count => x_msg_count
3536 , p_data => x_msg_data);
3537 END IF; -- if l_context_value is not null
3538
3539 x_ser_attr_tbl := g_lot_ser_attributes_tbl;
3540 x_validation_status := 'Y';
3541 x_return_status := fnd_api.g_ret_sts_success;
3542 EXCEPTION
3543 WHEN fnd_api.g_exc_error
3544 THEN
3545 x_validation_status := l_validation_status;
3546 x_return_status := fnd_api.g_ret_sts_error;
3547 fnd_msg_pub.count_and_get (p_count => x_msg_count
3548 , p_data => x_msg_data);
3549 WHEN fnd_api.g_exc_unexpected_error
3550 THEN
3551 x_validation_status := l_validation_status;
3552 x_return_status := fnd_api.g_ret_sts_unexp_error;
3553 fnd_msg_pub.count_and_get (p_count => x_msg_count
3554 , p_data => x_msg_data);
3555 WHEN OTHERS
3556 THEN
3557 x_validation_status := 'E';
3558 x_return_status := fnd_api.g_ret_sts_unexp_error;
3559
3560 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
3561 THEN
3562 fnd_msg_pub.add_exc_msg (g_pkg_name, 'validate_serial_attributes');
3563 END IF;
3564
3565 fnd_msg_pub.count_and_get (p_count => x_msg_count
3566 , p_data => x_msg_data);
3567 END validate_serial_attributes;
3568
3569 /*********************************************************************************************
3570 * Pseudo-code: *
3571 * l_organization_id := p_st_org_id_tbl(1);
3572 * l_inventory_item_id := p_st_item_id_tbl(1);
3573 * l_subinventory_code := p_St_sub_code_tbl(1);
3574 * l_locator_id := p_st_loc_id_tbl(1);
3575 * l_lot_number := p_st_lot_num_tbl(1);
3576 * l_cost_group_id := p_st_cost_group_tbl(1);
3577 * l_lpn_id := p_st_lpn_id_tbl(1);
3578 * l_revision := p_st_revision_tbl(1);
3579 *
3580 * if( l_lpn_id IS NULL ) then
3581 * l_containerized_flag := 2;
3582 * else
3583 * l_containerized_flag := 1;
3584 * end if;
3585 *
3586 * get cost group by calling inv_cost_group_update.proc_get_costgroup;
3587 *
3588 * if( no cost groups is stamped for the resulting lots ) then
3589 * assign the parent lot cost groups to the resulting lots
3590 * return validation status = 'Y'
3591 * else
3592 * for each resulting lots cost group LOOP
3593 * if resulting lot cost group <> parent lot cost group then
3594 * assign the parent lot cost group to the resulting lot cost group
3595 * end if
3596 * end loop;
3597 * return l_validation_status := 'Y';
3598 * end if;
3599 * if( transactions is lot merge ) then
3600 * for i in 2..l_start_count LOOP
3601 * l_organization_id := p_st_org_id_tbl(i);
3602 * l_inventory_item_id := p_st_item_id_tbl(i);
3603 * l_subinventory_code := p_St_sub_code_tbl(i);
3604 * l_locator_id := p_st_loc_id_tbl(i);
3605 * l_lot_number := p_st_lot_num_tbl(i);
3606 * l_current_cost_group_id := p_st_cost_group_tbl(i);
3607 * l_lpn_id := p_st_lpn_id_tbl(i);
3608 * l_revision := p_st_revision_tbl(i);
3609 * if( l_current_cost_group_id IS NULL OR l_current_cost_group_id = -1) then
3610 * -- get cost group for the parent lot
3611 * if( l_lpn_id IS NULL ) then
3612 * l_containerized_flag := 2;
3613 * else
3614 * l_containerized_flag := 1;
3615 * end if;
3616 *
3617 * call INV_COST_GROUP_UPDATE.PROC_GET_COSTGROUP to get cost group
3618 * end if;
3619 * if( l_current_cost_group_id <> l_cost_group_id ) THEN
3620 * return error different cost group for lot merge error
3621 * end if;
3622 *
3623 * END LOOP;
3624 * if( number of resulting lots > 1 ) then
3625 * return too many resulting lots error
3626 * end if;
3627 *
3628 * elsif( transaction is lot translate ) then
3629 * -- do not assign cost group if the lot changed item.
3630 * if( no of starting lot > 1 OR no of resulting lot > 1 ) then
3631 * return too many start lot and result lot error
3632 * end if;
3633 *
3634 * if( the item is changed ) then
3635 * --do nothing. let the trx manager assign the cost group;
3636 * return validation status = 'Y'
3637 * end if;
3638 * end if;
3639 *************************************************************************************************/
3640 PROCEDURE validate_cost_groups (
3641 x_rs_cost_group_tbl IN OUT NOCOPY number_table
3642 , x_return_status OUT NOCOPY VARCHAR2
3643 , x_msg_count OUT NOCOPY NUMBER
3644 , x_msg_data OUT NOCOPY VARCHAR2
3645 , x_validation_status OUT NOCOPY VARCHAR2
3646 , p_transaction_type_id IN NUMBER
3647 , p_transaction_action_id IN NUMBER
3648 , p_st_org_id_tbl IN number_table
3649 , p_st_item_id_tbl IN number_table
3650 , p_st_sub_code_tbl IN sub_code_table
3651 , p_st_loc_id_tbl IN number_table
3652 , p_st_lot_num_tbl IN lot_number_table
3653 , p_st_cost_group_tbl IN number_table
3654 , p_st_revision_tbl IN revision_table
3655 , p_st_lpn_id_tbl IN number_table
3656 , p_rs_org_id_tbl IN number_table
3657 , p_rs_item_id_tbl IN number_table
3658 , p_rs_sub_code_tbl IN sub_code_table
3659 , p_rs_loc_id_tbl IN number_table
3660 , p_rs_lot_num_tbl IN lot_number_table
3661 , p_rs_revision_tbl IN revision_table
3662 , p_rs_lpn_id_tbl IN number_table
3663 )
3664 IS
3665 l_validation_status VARCHAR2 (1);
3666 l_start_count NUMBER;
3667 l_result_count NUMBER;
3668 l_organization_id NUMBER;
3669 l_inventory_item_id NUMBER;
3670 l_subinventory_code VARCHAR2 (30);
3671 l_lot_number VARCHAR2 (30);
3672 l_cost_group_id NUMBER;
3673 l_current_cost_group_id NUMBER;
3674 l_result_cost_group_id NUMBER;
3675 l_locator_id NUMBER;
3676 l_lpn_id NUMBER;
3677 l_revision VARCHAR2 (30);
3678 l_containerized_flag NUMBER;
3679 l_return_status VARCHAR2 (1);
3680 v_cost_group_id NUMBER;
3681 l_debug NUMBER
3682 := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
3683 BEGIN
3684 -- assign return value first
3685 IF (l_debug = 1)
3686 THEN
3687 print_debug ('in validate cost group', 'Validate_Cost_Group');
3688 END IF;
3689
3690 x_return_status := fnd_api.g_ret_sts_success;
3691 l_start_count := p_st_lot_num_tbl.COUNT;
3692 l_result_count := p_rs_lot_num_tbl.COUNT;
3693
3694 IF (l_debug = 1)
3695 THEN
3696 print_debug ('l_start_count is ' || l_start_count
3697 , 'Validate_Cost_Group'
3698 );
3699 print_debug ('l_result_count is ' || l_result_count
3700 , 'Validate_Cost_Group'
3701 );
3702 END IF;
3703
3704 l_organization_id := p_st_org_id_tbl (1);
3705 l_inventory_item_id := p_st_item_id_tbl (1);
3706 l_subinventory_code := p_st_sub_code_tbl (1);
3707 l_locator_id := p_st_loc_id_tbl (1);
3708 l_lot_number := p_st_lot_num_tbl (1);
3709 l_cost_group_id := p_st_cost_group_tbl (1);
3710 l_lpn_id := p_st_lpn_id_tbl (1);
3711 l_revision := p_st_revision_tbl (1);
3712
3713 IF (l_debug = 1)
3714 THEN
3715 print_debug ('l_organization_id is ' || l_organization_id
3716 , 'Validate_Cost_Group'
3717 );
3718 print_debug ('l_inventory_item_id is ' || l_inventory_item_id
3719 , 'Validate_Cost_Group'
3720 );
3721 print_debug ('l_subinventory_code is ' || l_subinventory_code
3722 , 'Validate_Cost_Group'
3723 );
3724 print_debug ('l_locator_id is ' || l_locator_id, 'Validate_Cost_Group');
3725 print_debug ('l_lot_number is ' || l_lot_number, 'Validate_Cost_Group');
3726 print_debug ('l_cost_group_id is ' || l_cost_group_id
3727 , 'Validate_Cost_Group'
3728 );
3729 print_debug ('l_lpn_id is ' || l_lpn_id, 'Validate_Cost_Group');
3730 print_debug ('l_revision is ' || l_revision, 'Validate_Cost_Group');
3731 END IF;
3732
3733 --if( l_cost_group_id IS NULL or l_cost_group_id = -1 ) then
3734 -- get cost group for the parent lot
3735 IF (l_lpn_id IS NULL)
3736 THEN
3737 l_containerized_flag := 2;
3738 ELSE
3739 l_containerized_flag := 1;
3740 END IF;
3741
3742 IF (l_debug = 1)
3743 THEN
3744 print_debug ('l_containerized_flag is ' || l_containerized_flag
3745 , 'Validate_Cost_Group'
3746 );
3747 print_debug ('calling inv_cost_group_update.proc_get_costgroup'
3748 , 'Validate_Cost_Group'
3749 );
3750 END IF;
3751
3752 inv_cost_group_update.proc_get_costgroup
3753 (p_organization_id => l_organization_id
3754 , p_inventory_item_id => l_inventory_item_id
3755 , p_subinventory_code => l_subinventory_code
3756 , p_locator_id => l_locator_id
3757 , p_revision => l_revision
3758 , p_lot_number => l_lot_number
3759 , p_serial_number => NULL
3760 , p_containerized_flag => l_containerized_flag
3761 , p_lpn_id => l_lpn_id
3762 , p_transaction_action_id => p_transaction_action_id
3763 , x_cost_group_id => v_cost_group_id
3764 , x_return_status => l_return_status
3765 );
3766
3767 IF (l_return_status <> fnd_api.g_ret_sts_success)
3768 THEN
3769 IF (l_debug = 1)
3770 THEN
3771 print_debug ('error from inv_cost_group_update.proc_get_costgroup'
3772 , 'Validate_cost_group'
3773 );
3774 END IF;
3775
3776 fnd_message.set_name ('INV', 'INV_ERROR_GET_COST_GROUP');
3777 fnd_msg_pub.ADD;
3778 x_validation_status := 'N';
3779 RAISE fnd_api.g_exc_error;
3780 END IF;
3781
3782 IF (l_cost_group_id IS NULL OR l_cost_group_id = -1)
3783 THEN
3784 l_cost_group_id := v_cost_group_id;
3785 ELSIF (l_cost_group_id <> v_cost_group_id)
3786 THEN
3787 fnd_message.set_name ('INV', 'INV_INT_CSTGRP');
3788 fnd_msg_pub.ADD;
3789 x_validation_status := 'N';
3790 RAISE fnd_api.g_exc_error;
3791 END IF;
3792
3793 IF (x_rs_cost_group_tbl.COUNT = 0 OR x_rs_cost_group_tbl IS NULL)
3794 THEN
3795 IF (l_debug = 1)
3796 THEN
3797 print_debug ('x_rs_cost_group_tbl is null', 'Validate_Cost_Group');
3798 END IF;
3799
3800 -- user does not stamp the cost group in the interface table
3801 -- assign the parent lot cost group
3802 x_rs_cost_group_tbl := number_table ();
3803 x_rs_cost_group_tbl.EXTEND (l_result_count);
3804
3805 FOR i IN 1 .. l_result_count
3806 LOOP
3807 x_rs_cost_group_tbl (i) := l_cost_group_id;
3808 END LOOP;
3809
3810 l_validation_status := 'Y';
3811 ELSE
3812 -- user stamp the cost group. Check if not same as parent lot cost group, throw error.
3813 FOR i IN 1 .. l_result_count
3814 LOOP
3815 IF (x_rs_cost_group_tbl (i) <> l_cost_group_id)
3816 THEN
3817 fnd_message.set_name ('INV', 'INV_LOT_DIFF_COSTGROUP');
3818 fnd_msg_pub.ADD;
3819 l_validation_status := 'N';
3820 RAISE fnd_api.g_exc_error;
3821 END IF;
3822 END LOOP;
3823
3824 l_validation_status := 'Y';
3825 END IF;
3826
3827 IF (p_transaction_type_id = inv_globals.g_type_inv_lot_merge)
3828 THEN
3829 FOR i IN 2 .. l_start_count
3830 LOOP
3831 l_organization_id := p_st_org_id_tbl (i);
3832 l_inventory_item_id := p_st_item_id_tbl (i);
3833 l_subinventory_code := p_st_sub_code_tbl (i);
3834 l_locator_id := p_st_loc_id_tbl (i);
3835 l_lot_number := p_st_lot_num_tbl (i);
3836 l_current_cost_group_id := p_st_cost_group_tbl (i);
3837 l_lpn_id := p_st_lpn_id_tbl (i);
3838 l_revision := p_st_revision_tbl (i);
3839 --Bug #5501030
3840 IF (l_debug = 1) THEN
3841 print_debug ('l_organization_id is ' || l_organization_id
3842 , 'Validate_Cost_Group'
3843 );
3844 print_debug ('l_inventory_item_id is ' || l_inventory_item_id
3845 , 'Validate_Cost_Group'
3846 );
3847 print_debug ('l_subinventory_code is ' || l_subinventory_code
3848 , 'Validate_Cost_Group'
3849 );
3850 print_debug ('l_locator_id is ' || l_locator_id, 'Validate_Cost_Group');
3851 print_debug ('l_lot_number is ' || l_lot_number, 'Validate_Cost_Group');
3852 print_debug ('l_cost_group_id is ' || l_cost_group_id
3853 , 'Validate_Cost_Group'
3854 );
3855 print_debug ('l_lpn_id is ' || l_lpn_id, 'Validate_Cost_Group');
3856 print_debug ('l_revision is ' || l_revision, 'Validate_Cost_Group');
3857 END IF;
3858
3859 IF (l_current_cost_group_id IS NULL OR l_current_cost_group_id = -1)
3860 THEN
3861 -- get cost group for the parent lot
3862 IF (l_lpn_id IS NULL)
3863 THEN
3864 l_containerized_flag := 2;
3865 ELSE
3866 l_containerized_flag := 1;
3867 END IF;
3868
3869 inv_cost_group_update.proc_get_costgroup
3870 (p_organization_id => l_organization_id
3871 , p_inventory_item_id => l_inventory_item_id
3872 , p_subinventory_code => l_subinventory_code
3873 , p_locator_id => l_locator_id
3874 , p_revision => l_revision
3875 , p_lot_number => l_lot_number
3876 , p_serial_number => NULL
3877 , p_containerized_flag => l_containerized_flag
3878 , p_lpn_id => l_lpn_id
3879 , p_transaction_action_id => p_transaction_action_id
3880 , x_cost_group_id => l_current_cost_group_id
3881 , x_return_status => l_return_status
3882 );
3883
3884 IF (l_return_status <> fnd_api.g_ret_sts_success)
3885 THEN
3886 fnd_message.set_name ('INV', 'INV_ERROR_GET_COST_GROUP');
3887 fnd_msg_pub.ADD;
3888 x_validation_status := 'N';
3889 RAISE fnd_api.g_exc_error;
3890 END IF;
3891 END IF;
3892
3893 IF (l_current_cost_group_id <> l_cost_group_id)
3894 THEN
3895 fnd_message.set_name ('INV', 'INV_DIFF_MERGE_COST_GROUP');
3896 fnd_message.set_token ('ENTITY1', l_lot_number);
3897 fnd_msg_pub.ADD;
3898 x_validation_status := 'N';
3899 RAISE fnd_api.g_exc_error;
3900 END IF;
3901 END LOOP;
3902 ELSIF (p_transaction_type_id = inv_globals.g_type_inv_lot_translate)
3903 THEN
3904 -- do not assign cost group if the lot changed item.
3905 IF (l_inventory_item_id <> p_rs_item_id_tbl (1))
3906 THEN
3907 -- do nothing. let the trx manager assign the cost group;
3908 x_validation_status := 'Y';
3909 x_return_status := fnd_api.g_ret_sts_success;
3910 fnd_msg_pub.count_and_get (p_count => x_msg_count
3911 , p_data => x_msg_data
3912 );
3913 RETURN;
3914 END IF;
3915 END IF;
3916
3917 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3918 EXCEPTION
3919 WHEN fnd_api.g_exc_error
3920 THEN
3921 x_return_status := fnd_api.g_ret_sts_error;
3922 fnd_msg_pub.count_and_get (p_count => x_msg_count
3923 , p_data => x_msg_data);
3924 WHEN fnd_api.g_exc_unexpected_error
3925 THEN
3926 x_return_status := fnd_api.g_ret_sts_unexp_error;
3927 fnd_msg_pub.count_and_get (p_count => x_msg_count
3928 , p_data => x_msg_data);
3929 WHEN OTHERS
3930 THEN
3931 x_return_status := fnd_api.g_ret_sts_unexp_error;
3932
3933 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
3934 THEN
3935 fnd_msg_pub.add_exc_msg (g_pkg_name, 'Validate_Material_Status');
3936 END IF;
3937
3938 fnd_msg_pub.count_and_get (p_count => x_msg_count
3939 , p_data => x_msg_data);
3940 END validate_cost_groups;
3941
3942 /*********************************************************************************************
3943 * Pseudo-codes: *
3944 * Call Get_Org_info to get wms_installed, wsm_enabled and wms_enabled flag for *
3945 * The organization *
3946 * *
3947 * l_start_count := p_st_lot_num_tbl.COUNT; *
3948 * l_result_count := p_rs_lot_num_tbl.COUNT; *
3949 * *
3950 * Retrieve the primary_uom_code and revision_control for the item and org. *
3951 * *
3952 * l_organization_id := p_st_org_id_tbl(1); *
3953 * l_inventory_item_id := p_st_item_id_tbl(1); *
3954 * l_subinventory_code := p_St_sub_code_tbl(1); *
3955 * l_locator_id := p_st_loc_id_tbl(1); *
3956 * l_lot_number := p_st_lot_num_tbl(1); *
3957 * l_cost_group_id := p_st_cost_group_tbl(1); *
3958 * l_lpn_id := p_st_lpn_id_tbl(1); *
3959 * l_revision := p_st_revision_tbl(1); *
3960 * l_start_uom_code := p_st_uom_tbl(1); *
3961 * l_start_qty := p_st_quantity_tbl(1); *
3962 * *
3963 * if( this is a lot split or lot translate transaction ) then *
3964 * -- check if the total result qty do not exceed the parent lot quantity *
3965 * *
3966 * if( primary uom is different from the uom of the parent lot ) then *
3967 * -- call inv_um.convert *
3968 * calculate the primary qty of the parent lot by calling *
3969 * inv_convert.inv_um_convert *
3970 * end if; *
3971 * *
3972 * for i in 1..l_result_count LOOP *
3973 * if( result lot uom <> primary uom of parent lot ) then *
3974 * convert to result qty to the primary uom of starting lot. *
3975 * end if; *
3976 * l_total_qty := l_total_qty + l_result_qty; *
3977 * end loop; *
3978 * if( l_total_qty = 0 ) then *
3979 * return incorrect transaction qty *
3980 * end if; *
3981 * *
3982 * if( l_total_qty > l_start_primary_qty ) then *
3983 * return total quantity exceed quantity to split error *
3984 * end if; *
3985 * else if( transaction is lot merge ) THEN *
3986 * for each parent lots record LOOP *
3987 * if( l_start_primary_uom <> p_st_uom_tbl(i) ) then *
3988 * convert qty to primary uom *
3989 * end if; *
3990 * l_total_qty := l_total_qty + l_start_primary_qty; *
3991 * *
3992 * if( l_total_qty = 0 ) then *
3993 * return incorrect transaction qty error *
3994 * end if; *
3995 * end Loop; *
3996 * *
3997 * if( result uom <> l_start_primary_uom ) then *
3998 * -- convert result qty to primary start uom *
3999 * end if; *
4000 * if( l_result_qty > l_total_qty ) then *
4001 * return result qty does not match total qty to merge error *
4002 * end if; *
4003 * end if; *
4004 *********************************************************************************************/
4005 PROCEDURE validate_quantity (
4006 x_return_status OUT NOCOPY VARCHAR2
4007 , x_msg_count OUT NOCOPY NUMBER
4008 , x_msg_data OUT NOCOPY VARCHAR2
4009 , x_validation_status OUT NOCOPY VARCHAR2
4010 , p_transaction_type_id IN NUMBER
4011 , p_st_org_id_tbl IN number_table
4012 , p_st_item_id_tbl IN number_table
4013 , p_st_sub_code_tbl IN sub_code_table
4014 , p_st_loc_id_tbl IN number_table
4015 , p_st_lot_num_tbl IN lot_number_table
4016 , p_st_cost_group_tbl IN number_table
4017 , p_st_revision_tbl IN revision_table
4018 , p_st_lpn_id_tbl IN number_table
4019 , p_st_quantity_tbl IN number_table
4020 , p_st_uom_tbl IN uom_table
4021 , p_st_ser_number_tbl IN serial_number_table
4022 , p_st_ser_parent_lot_tbl IN parent_lot_table
4023 , p_rs_org_id_tbl IN number_table
4024 , p_rs_item_id_tbl IN number_table
4025 , p_rs_sub_code_tbl IN sub_code_table
4026 , p_rs_loc_id_tbl IN number_table
4027 , p_rs_lot_num_tbl IN lot_number_table
4028 , p_rs_cost_group_tbl IN number_table
4029 , p_rs_revision_tbl IN revision_table
4030 , p_rs_lpn_id_tbl IN number_table
4031 , p_rs_quantity_tbl IN number_table
4032 , p_rs_uom_tbl IN uom_table
4033 , p_rs_ser_number_tbl IN serial_number_table
4034 , p_rs_ser_parent_lot_tbl IN parent_lot_table
4035 )
4036 IS
4037 l_wms_installed VARCHAR2 (1);
4038 l_wsm_enabled VARCHAR2 (1);
4039 l_wms_enabled VARCHAR2 (1);
4040 l_start_count NUMBER;
4041 l_result_count NUMBER;
4042 l_organization_id NUMBER;
4043 l_inventory_item_id NUMBER;
4044 l_subinventory_code VARCHAR2 (30);
4045 l_lot_number VARCHAR2 (30);
4046 l_cost_group_id NUMBER;
4047 l_current_cost_group_id NUMBER;
4048 l_result_cost_group_id NUMBER;
4049 l_locator_id NUMBER;
4050 l_lpn_id NUMBER;
4051 l_revision VARCHAR2 (30);
4052 l_containerized_flag NUMBER;
4053 l_start_uom_code VARCHAR2 (3);
4054 l_result_uom_code VARCHAR2 (3);
4055 l_start_primary_uom VARCHAR2 (3);
4056 l_result_primary_uom VARCHAR2 (3);
4057 l_start_primary_qty NUMBER;
4058 l_start_qty NUMBER;
4059 l_result_primary_qty NUMBER;
4060 l_result_qty NUMBER;
4061 l_total_qty NUMBER := 0;
4062 l_temp_qty NUMBER;
4063 l_att_qty NUMBER;
4064 l_qoh_qty NUMBER;
4065 l_lpn_qty NUMBER;
4066 l_st_var_index mtl_serial_numbers.serial_number%TYPE;
4067 l_rs_var_index mtl_serial_numbers.serial_number%TYPE;
4068 l_lot_serial_count NUMBER;
4069 l_serial_code NUMBER;
4070 l_is_serial_control VARCHAR2 (10);
4071 l_return_values VARCHAR2 (1);
4072 l_return_msg VARCHAR2 (200);
4073 l_revision_control VARCHAR2 (5);
4074 l_debug NUMBER := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
4075 BEGIN
4076 IF (l_debug = 1)
4077 THEN
4078 print_debug ('Inside Validate_Quantity', 'Validate_Quantity');
4079 END IF;
4080
4081 x_return_status := fnd_api.g_ret_sts_success;
4082 x_validation_status := 'Y';
4083
4084 IF (l_debug = 1)
4085 THEN
4086 print_debug ('calling get_org_info', 'Validate_Quantity');
4087 END IF;
4088
4089 get_org_info (p_organization_id => p_st_org_id_tbl (1)
4090 , x_wms_installed => l_wms_installed
4091 , x_wsm_enabled => l_wsm_enabled
4092 , x_wms_enabled => l_wms_enabled
4093 , x_return_status => x_return_status
4094 , x_msg_count => x_msg_count
4095 , x_msg_data => x_msg_data
4096 );
4097
4098 IF (x_return_status = fnd_api.g_ret_sts_error)
4099 THEN
4100 RAISE fnd_api.g_exc_error;
4101 ELSIF (x_return_status = fnd_api.g_ret_sts_unexp_error)
4102 THEN
4103 RAISE fnd_api.g_exc_unexpected_error;
4104 END IF;
4105
4106 IF (l_debug = 1)
4107 THEN
4108 print_debug ('l_wms_installed is ' || l_wms_installed
4109 , 'Validate_Quantity'
4110 );
4111 print_debug ('l_wsm_enabled is ' || l_wsm_enabled, 'Validate_Quantity');
4112 print_debug ('l_wms_enabled is ' || l_wms_enabled, 'Validate_Quantity');
4113 END IF;
4114
4115 l_start_count := p_st_lot_num_tbl.COUNT;
4116 l_result_count := p_rs_lot_num_tbl.COUNT;
4117 l_organization_id := p_st_org_id_tbl (1);
4118 l_inventory_item_id := p_st_item_id_tbl (1);
4119 l_subinventory_code := p_st_sub_code_tbl (1);
4120 l_locator_id := p_st_loc_id_tbl (1);
4121 l_lot_number := p_st_lot_num_tbl (1);
4122 l_cost_group_id := p_st_cost_group_tbl (1);
4123 l_lpn_id := p_st_lpn_id_tbl (1);
4124 l_revision := p_st_revision_tbl (1);
4125 l_start_uom_code := p_st_uom_tbl (1);
4126 l_start_qty := p_st_quantity_tbl (1);
4127
4128 -- get primary uom
4129 BEGIN
4130 SELECT primary_uom_code
4131 , DECODE (revision_qty_control_code, 1, 'FALSE', 'TRUE')
4132 INTO l_start_primary_uom
4133 , l_revision_control
4134 FROM mtl_system_items
4135 WHERE organization_id = l_organization_id
4136 AND inventory_item_id = l_inventory_item_id;
4137 EXCEPTION
4138 WHEN NO_DATA_FOUND
4139 THEN
4140 fnd_message.set_name ('INV', 'INV_INT_ITEM_CODE');
4141 fnd_msg_pub.ADD;
4142 RAISE fnd_api.g_exc_error;
4143 END;
4144
4145 IF (l_debug = 1) THEN
4146 print_debug ('l_start_primary_uom is ' || l_start_primary_uom
4147 , 'Validate_Quantity'
4148 );
4149 print_debug ('l_start_count is ' || l_start_count, 'Validate_Quantity');
4150 print_debug ('l_result_count is ' || l_result_count
4151 , 'Validate_Quantity');
4152 END IF;
4153
4154 /*Added for OSFM support for Serialized Lot Items.*/
4155 BEGIN
4156 SELECT serial_number_control_code
4157 INTO l_serial_code
4158 FROM mtl_system_items
4159 WHERE organization_id = l_organization_id
4160 AND inventory_item_id = l_inventory_item_id;
4161 EXCEPTION
4162 WHEN OTHERS
4163 THEN
4164 IF (l_debug = 1) THEN
4165 print_debug ('Error in getting serial_number control code', 'Validate_Quantity');
4166 END IF;
4167 RAISE fnd_api.g_exc_unexpected_error;
4168 END;
4169 IF (l_debug = 1) THEN
4170 print_debug ('l_serial_code ' || l_serial_code, 'Validate_Quantity');
4171 END IF;
4172
4173 IF (l_serial_code IN (2, 5))
4174 THEN
4175 l_is_serial_control := 'TRUE';
4176 ELSE
4177 l_is_serial_control := 'FALSE';
4178 END IF;
4179
4180 IF ( p_transaction_type_id = inv_globals.g_type_inv_lot_split
4181 OR p_transaction_type_id = inv_globals.g_type_inv_lot_translate
4182 )
4183 THEN
4184 -- check if the total result qty do not exceed the parent lot quantity
4185 IF (l_start_primary_uom <> p_st_uom_tbl (1))
4186 THEN
4187 -- call inv_um.convert
4188 l_start_primary_qty :=
4189 inv_convert.inv_um_convert (item_id => l_inventory_item_id
4190 , PRECISION => 5
4191 , from_quantity => l_start_qty
4192 , from_unit => l_start_uom_code
4193 , to_unit => l_start_primary_uom
4194 , from_name => NULL
4195 , to_name => NULL
4196 );
4197
4198 IF (l_start_primary_qty = -99999)
4199 THEN
4200 fnd_message.set_name ('INV', 'INV-CANNOT CONVERT');
4201 fnd_message.set_token ('UOM', l_start_uom_code);
4202 fnd_message.set_token ('ROUTINE'
4203 , g_pkg_name || 'Validate_Quantity');
4204 fnd_msg_pub.ADD;
4205 x_validation_status := 'N';
4206 RAISE fnd_api.g_exc_unexpected_error;
4207 END IF;
4208 ELSE
4209 l_start_primary_qty := l_start_qty;
4210 END IF;
4211
4212 IF (l_debug = 1) THEN
4213 print_debug ('l_start_primary_qty is ' || l_start_primary_qty, 'Validate_Quantity');
4214 END IF;
4215
4216 /*Get the immediate qty of an item in an LPN...
4217 *this api also validates the loose quantities if lpn_id is NULL
4218 */
4219 l_return_values :=
4220 inv_txn_validations.get_immediate_lpn_item_qty
4221 (p_lpn_id => l_lpn_id
4222 , p_organization_id => l_organization_id
4223 , p_source_type_id => -9999
4224 , p_inventory_item_id => l_inventory_item_id
4225 , p_revision => l_revision
4226 , p_locator_id => l_locator_id
4227 , p_subinventory_code => l_subinventory_code
4228 , p_lot_number => l_lot_number
4229 , p_is_revision_control => l_revision_control
4230 , p_is_serial_control => l_is_serial_control
4231 , p_is_lot_control => 'TRUE'
4232 , x_transactable_qty => l_att_qty
4233 , x_qoh => l_qoh_qty
4234 , x_lpn_onhand => l_lpn_qty
4235 , x_return_msg => l_return_msg
4236 );
4237
4238 IF (l_return_values <> 'Y') THEN
4239 IF (l_debug = 1) THEN
4240 print_debug ('get_immediate_lpn_item_qty has returned error', 'Validate_Quantity');
4241 END IF;
4242 fnd_message.set_name ('INV', 'INV_NOT_ENOUGH_ATT');
4243 fnd_msg_pub.ADD;
4244 RAISE fnd_api.g_exc_error;
4245 END IF;
4246
4247 IF (l_debug = 1) THEN
4248 print_debug ('l_att_qty is ' || l_att_qty, 'Validate_Quantity');
4249 print_debug ('l_qoh_qty is ' || l_qoh_qty, 'Validate_Quantity');
4250 print_debug ('l_lpn_qty is ' || l_lpn_qty, 'Validate_quantity');
4251 END IF;
4252
4253 IF (l_att_qty < l_start_primary_qty) THEN
4254 fnd_message.set_name ('INV', 'INV_NOT_ENOUGH_ATT');
4255 fnd_msg_pub.ADD;
4256 RAISE fnd_api.g_exc_error;
4257 END IF;
4258
4259 IF (p_transaction_type_id = inv_globals.g_type_inv_lot_translate) THEN
4260 IF (l_att_qty <> l_start_primary_qty) THEN
4261 fnd_message.set_name ('INV', 'INV_LOT_TRANSLATE_QTY_ERR');
4262 fnd_msg_pub.ADD;
4263 RAISE fnd_api.g_exc_error;
4264 END IF;
4265 END IF;
4266
4267 FOR i IN 1 .. l_result_count
4268 LOOP
4269 IF (l_debug = 1) THEN
4270 print_debug ('l_rs_uom_tbl is ' || p_rs_uom_tbl (i), 'Validate_Quantity');
4271 END IF;
4272
4273 IF (p_rs_uom_tbl (i) <> l_start_primary_uom) THEN
4274 -- convert to start uom
4275 l_result_qty :=
4276 inv_convert.inv_um_convert
4277 (item_id => l_inventory_item_id
4278 , PRECISION => 5
4279 , from_quantity => p_rs_quantity_tbl(i)
4280 , from_unit => p_rs_uom_tbl (i)
4281 , to_unit => l_start_primary_uom
4282 , from_name => NULL
4283 , to_name => NULL
4284 );
4285
4286 IF (l_result_qty = -99999) THEN
4287 fnd_message.set_name ('INV', 'INV-CANNOT CONVERT');
4288 fnd_message.set_token ('UOM', l_start_uom_code);
4289 fnd_message.set_token ('ROUTINE', g_pkg_name || 'Validate_Quantity');
4290 fnd_msg_pub.ADD;
4291 x_validation_status := 'N';
4292 RAISE fnd_api.g_exc_unexpected_error;
4293 END IF;
4294 ELSE
4295 l_result_qty := p_rs_quantity_tbl (i);
4296 END IF;
4297
4298 IF ( i = 1
4299 AND l_result_qty = l_att_qty
4300 AND p_transaction_type_id = inv_globals.g_type_inv_lot_split
4301 ) THEN
4302 fnd_message.set_name ('INV', 'INV_MIN_LOT_SPLIT');
4303 fnd_msg_pub.ADD;
4304 RAISE fnd_api.g_exc_error;
4305 END IF;
4306
4307 l_total_qty := l_total_qty + l_result_qty;
4308
4309 IF (l_debug = 1) THEN
4310 print_debug ('l_total_qty is ' || l_total_qty, 'Validate_Quantity');
4311 END IF;
4312
4313 /*Check to see wether individual lot quantities also match*/
4314
4315 IF(l_is_serial_control = 'TRUE'
4316 AND p_transaction_type_id = inv_globals.g_type_inv_lot_split) THEN
4317 l_rs_var_index := p_rs_ser_parent_lot_tbl.FIRST;
4318 l_lot_serial_count := 0;
4319 FOR j IN 1 .. p_rs_ser_number_tbl.COUNT
4320 LOOP
4321 IF (p_rs_ser_parent_lot_tbl (l_rs_var_index) = p_rs_lot_num_tbl (i)) THEN
4322 l_lot_serial_count := l_lot_serial_count + 1;
4323 END IF;
4324 l_rs_var_index := p_rs_ser_parent_lot_tbl.NEXT (l_rs_var_index);
4325 END LOOP;
4326
4327 IF (l_lot_serial_count <> l_result_qty) THEN
4328 IF (l_debug = 1) THEN
4329 print_debug ('Lot qty does not match the serial qty for lot split ', 'Validate_Quantity');
4330 print_debug ('Lot = > ' || p_rs_lot_num_tbl (i), 'Validate_Quantity');
4331 print_debug ('l_lot_serial_count = > ' || l_lot_serial_count, 'Validate_Quantity');
4332 END IF;
4333 fnd_message.set_name ('INV', 'INV_INVLTPU_LOTTRX_QTY');
4334 fnd_msg_pub.ADD;
4335 RAISE fnd_api.g_exc_error;
4336 END IF;
4337 END IF;
4338 END LOOP;
4339
4340 IF (l_total_qty = 0) THEN
4341 fnd_message.set_name ('INV', 'INV_INLTPU_QTY');
4342 fnd_msg_pub.ADD;
4343 RAISE fnd_api.g_exc_error;
4344 END IF;
4345
4346 IF (l_total_qty <> l_start_primary_qty) THEN
4347 IF ( p_transaction_type_id = inv_globals.g_type_inv_lot_split
4348 AND l_total_qty > l_start_primary_qty) THEN
4349 x_validation_status := 'N';
4350 fnd_message.set_name ('INV', 'INV_TOTAL_EXCEED_SPLIT');
4351 fnd_msg_pub.ADD;
4352 RAISE fnd_api.g_exc_error;
4353 ELSIF (p_transaction_type_id = inv_globals.g_type_inv_lot_translate) THEN
4354 x_validation_status := 'N';
4355 fnd_message.set_name ('INV', 'INV_TOTAL_EXCEED_TRANSLATE');
4356 fnd_msg_pub.ADD;
4357 RAISE fnd_api.g_exc_error;
4358 END IF;
4359 END IF;
4360
4361 /*Check for qty matching in case of lot serial items...
4362 *Serial qty should match the MTLI.primary_quantity
4363 */
4364 IF (l_serial_code IN (2, 5)) THEN
4365 IF ( TRUNC (l_start_primary_qty) <> TRUNC (l_start_primary_qty, 6)
4366 OR TRUNC (l_total_qty) <> TRUNC (l_total_qty, 6) ) THEN
4367 IF (l_debug = 1) THEN
4368 print_debug('Fractional qty is present for a lot serial controlled item'
4369 , 'Validate_Quantity');
4370 END IF;
4371 fnd_message.set_name ('INV', 'INV_LOT_SER_QTY_VIOLATION');
4372 fnd_msg_pub.ADD;
4373 RAISE fnd_api.g_exc_error;
4374 END IF;
4375
4376 IF ( p_st_ser_number_tbl.COUNT <> l_start_primary_qty
4377 OR p_rs_ser_number_tbl.COUNT <> l_total_qty) THEN
4378 IF (l_debug = 1) THEN
4379 print_debug('Start/result lot qty does not match the start/result serial records qty'
4380 , 'Validate_Quantity');
4381 print_debug('p_st_ser_number_tbl.COUNT => '|| p_st_ser_number_tbl.COUNT
4382 , 'Validate_Quantity');
4383 print_debug('l_start_primary_qty => ' || l_start_primary_qty
4384 , 'Validate_Quantity');
4385 print_debug(' p_rs_ser_number_tbl.COUNT => ' || p_rs_ser_number_tbl.COUNT
4386 , 'Validate_Quantity');
4387 print_debug('l_total_qty => ' || l_total_qty, 'Validate_Quantity');
4388 END IF;
4389 x_validation_status := 'N';
4390 fnd_message.set_name ('INV', 'INV_INVLTPU_LOTTRX_QTY');
4391 fnd_msg_pub.ADD;
4392 RAISE fnd_api.g_exc_error;
4393 END IF;
4394 END IF;
4395 /* for lot merge, the check if the starting lot have enough qty to transact in
4396 * the transaction manager we don't check it here.
4397 */
4398 ELSIF (p_transaction_type_id = inv_globals.g_type_inv_lot_merge) THEN
4399 FOR i IN 1 .. l_start_count
4400 LOOP
4401 IF (l_start_primary_uom <> p_st_uom_tbl (i)) THEN
4402 l_start_primary_qty :=
4403 inv_convert.inv_um_convert(item_id => p_st_item_id_tbl(i)
4404 , PRECISION => 5
4405 , from_quantity => p_st_quantity_tbl(i)
4406 , from_unit => p_st_uom_tbl (i)
4407 , to_unit => l_start_primary_uom
4408 , from_name => NULL
4409 , to_name => NULL
4410 );
4411
4412 IF (l_start_primary_qty = -99999)
4413 THEN
4414 fnd_message.set_name ('INV', 'INV-CANNOT CONVERT');
4415 fnd_message.set_token ('UOM', l_start_uom_code);
4416 fnd_message.set_token ('ROUTINE'
4417 , g_pkg_name || 'Validate_Quantity'
4418 );
4419 fnd_msg_pub.ADD;
4420 x_validation_status := 'N';
4421 RAISE fnd_api.g_exc_unexpected_error;
4422 END IF;
4423 ELSE
4424 l_start_primary_qty := p_st_quantity_tbl (i);
4425 END IF;
4426
4427 IF (l_debug = 1) THEN
4428 print_debug('l_start_primary_qty is ' || l_start_primary_qty, 'Validate_Quantity');
4429 print_debug('p_st_lpn_id_tbl(i) is ' || p_st_lpn_id_tbl(i), 'Validate_Quantity');
4430 print_debug ('p_st_revision_tbl(i) is ' || p_st_revision_tbl(i), 'Validate_Quantity');
4431 print_debug ('p_st_sub_code_tbl(i) is ' || p_st_sub_code_tbl(i), 'Validate_Quantity');
4432 print_debug ('p_st_loc_id_tbl(i) is ' || p_st_loc_id_tbl(i), 'Validate_Quantity');
4433 END IF;
4434
4435 --Bug #5501030
4436 --Pass the revision to quantity tree by reading from table
4437 l_return_values :=
4438 inv_txn_validations.get_immediate_lpn_item_qty
4439 (p_lpn_id => p_st_lpn_id_tbl(i)
4440 , p_organization_id => l_organization_id
4441 , p_source_type_id => -9999
4442 , p_inventory_item_id => l_inventory_item_id
4443 , p_revision => p_st_revision_tbl(i)
4444 , p_locator_id => p_st_loc_id_tbl(i)
4445 , p_subinventory_code => p_st_sub_code_tbl(i)
4446 , p_lot_number => p_st_lot_num_tbl(i)
4447 , p_is_revision_control => l_revision_control
4448 , p_is_serial_control => l_is_serial_control
4449 , p_is_lot_control => 'TRUE'
4450 , x_transactable_qty => l_att_qty
4451 , x_qoh => l_qoh_qty
4452 , x_lpn_onhand => l_lpn_qty
4453 , x_return_msg => l_return_msg
4454 );
4455
4456 IF (l_return_values <> 'Y') THEN
4457 IF (l_debug = 1) THEN
4458 print_debug ('get_immediates_lpn_qty returned error', 'Validate_Quantity');
4459 END IF;
4460 fnd_message.set_name ('INV', 'INV_NOT_ENOUGH_ATT');
4461 fnd_msg_pub.ADD;
4462 RAISE fnd_api.g_exc_error;
4463 END IF;
4464
4465 IF (l_debug = 1) THEN
4466 print_debug ('l_att_qty is ' || l_att_qty, 'Validate_Quantity');
4467 print_debug ('l_qoh_qty is ' || l_qoh_qty, 'Validate_Quantity');
4468 print_debug ('l_lpn_qty is ' || l_lpn_qty, 'Validate_quantity');
4469 END IF;
4470
4471 IF (l_att_qty < l_start_primary_qty) THEN
4472 fnd_message.set_name ('INV', 'INV_NOT_ENOUGH_ATT');
4473 fnd_msg_pub.ADD;
4474 RAISE fnd_api.g_exc_error;
4475 END IF;
4476
4477 /*OSFM support for Serialized Lot Items
4478 *Need to calculate the serial numbers for each lot ..it should match with the
4479 *lot quantity..
4480 */
4481 IF (l_serial_code IN (2, 5)) THEN
4482 l_st_var_index := p_st_ser_parent_lot_tbl.FIRST;
4483 l_lot_serial_count := 0;
4484
4485 FOR j IN 1 .. p_st_ser_number_tbl.COUNT LOOP
4486 IF (p_st_ser_parent_lot_tbl (l_st_var_index) = p_st_lot_num_tbl (i))
4487 THEN
4488 l_lot_serial_count := l_lot_serial_count + 1;
4489 END IF;
4490 l_st_var_index := p_st_ser_parent_lot_tbl.NEXT (l_st_var_index);
4491 END LOOP;
4492
4493 IF (l_lot_serial_count <> l_start_primary_qty) THEN
4494 IF (l_debug = 1) THEN
4495 print_debug ('Lot qty does not match the serial qty ', 'Validate_Quantity');
4496 print_debug ('Lot = > ' || p_st_lot_num_tbl (i), 'Validate_Quantity');
4497 print_debug ('l_lot_serial_count = > ' || l_lot_serial_count, 'Validate_Quantity');
4498 END IF;
4499 fnd_message.set_name ('INV', 'INV_INVLTPU_LOTTRX_QTY');
4500 fnd_msg_pub.ADD;
4501 RAISE fnd_api.g_exc_error;
4502 END IF;
4503 END IF;
4504
4505 l_total_qty := l_total_qty + l_start_primary_qty;
4506 IF (l_debug = 1) THEN
4507 print_debug ('l_total_qty is ' || l_total_qty, 'Validate_Quantity');
4508 END IF;
4509 END LOOP;
4510
4511 IF (p_rs_uom_tbl (1) <> l_start_primary_uom) THEN
4512 -- convert to start uom
4513 l_result_qty :=
4514 inv_convert.inv_um_convert (item_id => l_inventory_item_id
4515 , PRECISION => 5
4516 , from_quantity => p_rs_quantity_tbl(1)
4517 , from_unit => p_rs_uom_tbl (1)
4518 , to_unit => l_start_primary_uom
4519 , from_name => NULL
4520 , to_name => NULL
4521 );
4522
4523 IF (l_result_qty = -99999)
4524 THEN
4525 fnd_message.set_name ('INV', 'INV-CANNOT CONVERT');
4526 fnd_message.set_token ('UOM', l_start_uom_code);
4527 fnd_message.set_token ('ROUTINE', g_pkg_name || 'Validate_Quantity');
4528 fnd_msg_pub.ADD;
4529 x_validation_status := 'N';
4530 RAISE fnd_api.g_exc_unexpected_error;
4531 END IF;
4532 ELSE
4533 l_result_qty := p_rs_quantity_tbl (1);
4534 END IF;
4535
4536 IF (l_debug = 1) THEN
4537 print_debug ('l_result_qty is ' || l_result_qty, 'Validate_Quantity');
4538 END IF;
4539
4540
4541 IF (l_result_qty <> l_total_qty) THEN
4542 x_validation_status := 'N';
4543 fnd_message.set_name ('INV', 'INV_QTY_NOT_MATCHED');
4544 fnd_msg_pub.ADD;
4545 RAISE fnd_api.g_exc_error;
4546 END IF;
4547
4548 /*For lot - serial items*/
4549 IF (l_serial_code IN (2, 5)) THEN
4550 IF ( TRUNC (l_start_primary_qty) <> TRUNC (l_start_primary_qty, 6)
4551 OR TRUNC (l_total_qty) <> TRUNC (l_total_qty, 6) ) THEN
4552 fnd_message.set_name ('INV', 'INV_LOT_SER_QTY_VIOLATION');
4553 fnd_msg_pub.ADD;
4554 RAISE fnd_api.g_exc_error;
4555 END IF;
4556
4557 IF ( p_st_ser_number_tbl.COUNT <> l_total_qty
4558 OR p_rs_ser_number_tbl.COUNT <> l_result_qty ) THEN
4559 x_validation_status := 'N';
4560 fnd_message.set_name ('INV', 'INV_INVLTPU_LOTTRX_QTY');
4561 fnd_msg_pub.ADD;
4562 RAISE fnd_api.g_exc_error;
4563 END IF;
4564 END IF;
4565 END IF;
4566
4567 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
4568 EXCEPTION
4569 WHEN fnd_api.g_exc_error
4570 THEN
4571 x_return_status := fnd_api.g_ret_sts_error;
4572 fnd_msg_pub.count_and_get (p_count => x_msg_count
4573 , p_data => x_msg_data);
4574 WHEN fnd_api.g_exc_unexpected_error
4575 THEN
4576 x_return_status := fnd_api.g_ret_sts_unexp_error;
4577 fnd_msg_pub.count_and_get (p_count => x_msg_count
4578 , p_data => x_msg_data);
4579 WHEN OTHERS
4580 THEN
4581 x_return_status := fnd_api.g_ret_sts_unexp_error;
4582
4583 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
4584 THEN
4585 fnd_msg_pub.add_exc_msg (g_pkg_name, 'Validate_Quantity');
4586 END IF;
4587
4588 fnd_msg_pub.count_and_get (p_count => x_msg_count
4589 , p_data => x_msg_data);
4590 END validate_quantity;
4591
4592 /*********************************************************************************************
4593 * This procedure will validate the organization, checks if the Organization chosen
4594 * has a open period and also check if the acct_period_id pass is valid.
4595 *********************************************************************************************/
4596 PROCEDURE validate_organization (
4597 x_return_status OUT NOCOPY VARCHAR2
4598 , x_msg_count OUT NOCOPY NUMBER
4599 , x_msg_data OUT NOCOPY VARCHAR2
4600 , x_validation_status OUT NOCOPY VARCHAR2
4601 , p_organization_id IN NUMBER
4602 , p_period_tbl IN number_table
4603 )
4604 IS
4605 l_period_tbl_id NUMBER;
4606 l_period_id NUMBER;
4607 BEGIN
4608 IF (p_organization_id IS NULL)
4609 THEN
4610 fnd_message.set_name ('INV', 'INV_MISSING_REQUIRED_PARAMETER');
4611 fnd_msg_pub.ADD;
4612 x_validation_status := 'N';
4613 RAISE fnd_api.g_exc_error;
4614 END IF;
4615
4616 print_debug ('Inside Validate_Organization', 'Validate_Organization');
4617 print_debug ('p_organization_id is ' || p_organization_id
4618 , 'Validate_Organization'
4619 );
4620 inv_inv_lovs.tdatechk (p_organization_id, SYSDATE, l_period_id);
4621 print_debug ('l_period_id is ' || l_period_id, 'Validate_Organization');
4622
4623 FOR i IN 1 .. p_period_tbl.COUNT
4624 LOOP
4625 l_period_tbl_id := p_period_tbl (i);
4626 print_debug ('p_period_tbl_id is ' || l_period_tbl_id
4627 , 'Validate_Organization'
4628 );
4629
4630 IF ( l_period_tbl_id <> l_period_id
4631 OR l_period_tbl_id = 0
4632 OR l_period_tbl_id = -1
4633 )
4634 THEN
4635 fnd_message.set_name ('INV', 'INV_NO_OPEN_PERIOD');
4636 fnd_msg_pub.ADD;
4637 x_validation_status := 'N';
4638 RAISE fnd_api.g_exc_error;
4639 END IF;
4640 END LOOP;
4641
4642 IF (l_period_id = 0 OR l_period_id = -1)
4643 THEN
4644 fnd_message.set_name ('INV', 'INV_NO_OPEN_PERIOD');
4645 fnd_msg_pub.ADD;
4646 x_validation_status := 'N';
4647 RAISE fnd_api.g_exc_error;
4648 ELSE
4649 x_validation_status := 'Y';
4650 x_return_status := fnd_api.g_ret_sts_success;
4651 fnd_msg_pub.count_and_get (p_count => x_msg_count
4652 , p_data => x_msg_data);
4653 END IF;
4654 EXCEPTION
4655 WHEN fnd_api.g_exc_error
4656 THEN
4657 x_return_status := fnd_api.g_ret_sts_error;
4658 fnd_msg_pub.count_and_get (p_count => x_msg_count
4659 , p_data => x_msg_data);
4660 WHEN fnd_api.g_exc_unexpected_error
4661 THEN
4662 x_return_status := fnd_api.g_ret_sts_unexp_error;
4663 fnd_msg_pub.count_and_get (p_count => x_msg_count
4664 , p_data => x_msg_data);
4665 WHEN OTHERS
4666 THEN
4667 x_return_status := fnd_api.g_ret_sts_unexp_error;
4668
4669 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
4670 THEN
4671 fnd_msg_pub.add_exc_msg (g_pkg_name, 'Validate_Organization');
4672 END IF;
4673
4674 fnd_msg_pub.count_and_get (p_count => x_msg_count
4675 , p_data => x_msg_data);
4676 END validate_organization;
4677
4678 /***********************************Validate_Serials*************************
4679 Perform basic validations for the serials present in the Lot transactions.
4680 -> Source Serials should match Resulting Serials in Count and Serial Number
4681 -> Source Serials should be available for transactions. (GM ID validation)
4682 -> Serial Material status validation for the source Serials.
4683 -> If Lot Translate and Item Id changed Then
4684 Call INV_SERIAL_NUMBER_PUB.validate_serials.
4685 This will perform uniqueness
4686 check and if possible create the new serial for the Resulting Item.
4687 End IF
4688 ****************************************************************************/
4689 PROCEDURE validate_serials (
4690 x_return_status OUT NOCOPY VARCHAR2
4691 , x_msg_count OUT NOCOPY NUMBER
4692 , x_msg_data OUT NOCOPY VARCHAR2
4693 , x_validation_status OUT NOCOPY VARCHAR2
4694 , p_transaction_type_id IN NUMBER
4695 , p_st_org_id_tbl IN number_table
4696 , p_rs_org_id_tbl IN number_table
4697 , p_st_item_id_tbl IN number_table
4698 , p_rs_item_id_tbl IN number_table
4699 , p_rs_lot_num_tbl IN lot_number_table
4700 , p_st_quantity_tbl IN number_table
4701 , p_st_sub_code_tbl IN sub_code_table
4702 , p_st_locator_id_tbl IN number_table
4703 , p_st_ser_number_tbl IN serial_number_table
4704 , p_st_ser_parent_lot_tbl IN parent_lot_table
4705 , p_rs_ser_number_tbl IN serial_number_table
4706 , p_st_ser_status_tbl IN number_table
4707 , p_st_ser_grp_mark_id_tbl IN number_table
4708 , p_st_ser_parent_sub_tbl IN parent_sub_table
4709 , p_st_ser_parent_loc_tbl IN parent_loc_table
4710 )
4711 IS
4712 l_proc_msg VARCHAR2 (255);
4713 l_end_ser mtl_serial_numbers.serial_number%TYPE;
4714 l_qty NUMBER;
4715 l_st_var_index mtl_serial_numbers.serial_number%TYPE;
4716 l_rs_var_index mtl_serial_numbers.serial_number%TYPE;
4717 l_debug NUMBER;
4718 l_validation_status VARCHAR2 (1);
4719 l_primary_uom VARCHAR2(10);
4720
4721 BEGIN
4722 l_debug := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
4723 l_validation_status := 'Y';
4724 x_return_status := fnd_api.g_ret_sts_success;
4725
4726 IF (p_transaction_type_id IS NULL)
4727 THEN
4728 IF (l_debug = 1)
4729 THEN
4730 print_debug ('breadcrumb 10', 'Validate_serials');
4731 print_debug ('p_transaction_type_id is NULL', 'Validate_serials');
4732 END IF;
4733
4734 l_validation_status := 'N';
4735 fnd_message.set_name ('INV', 'INV_MISSING_REQUIRED_PARAMETER');
4736 fnd_msg_pub.ADD;
4737 RAISE fnd_api.g_exc_error;
4738 END IF;
4739
4740 l_st_var_index := p_st_ser_number_tbl.FIRST;
4741 l_rs_var_index := p_rs_ser_number_tbl.FIRST;
4742
4743 FOR i IN 1 .. p_st_ser_number_tbl.COUNT
4744 LOOP
4745 IF (l_debug = 1)
4746 THEN
4747 print_debug ('breadcrumb 20', 'Validate_serials');
4748 END IF;
4749
4750 IF ((p_transaction_type_id <> inv_globals.g_type_inv_lot_split
4751 OR (p_transaction_type_id = inv_globals.g_type_inv_lot_split
4752 AND i <= p_rs_ser_number_tbl.COUNT))
4753 AND
4754 p_st_ser_number_tbl (l_st_var_index) <>
4755 p_rs_ser_number_tbl (l_rs_var_index)
4756 )
4757 THEN
4758 IF (l_debug = 1)
4759 THEN
4760 print_debug ('breadcrumb 30', 'Validate_serials');
4761 print_debug ('Mismtach between start and result serials'
4762 , 'Validate_serials'
4763 );
4764 END IF;
4765
4766 l_validation_status := 'N';
4767 fnd_message.set_name ('INV', 'INV_SERIAL_MATCH_ERROR');
4768 fnd_msg_pub.ADD;
4769 RAISE fnd_api.g_exc_error;
4770 /*Bug:5147899. Modified the following condition to throw error
4771 only when the group_mark_id holds a not null value other than -1 */
4772 ELSIF ( p_st_ser_grp_mark_id_tbl (i) IS NOT NULL
4773 AND p_st_ser_grp_mark_id_tbl (i) <> -1
4774 )
4775 THEN
4776 IF (l_debug = 1)
4777 THEN
4778 print_debug ('breadcrumb 40', 'Validate_serials');
4779 print_debug ( 'Group mark Id validation failed for serial => '
4780 || p_st_ser_grp_mark_id_tbl (i)
4781 , 'Validate_serials'
4782 );
4783 END IF;
4784
4785 l_validation_status := 'N';
4786 fnd_message.set_name ('INV', 'INV_SERIAL_IN_USE');
4787 fnd_msg_pub.ADD;
4788 RAISE fnd_api.g_exc_error;
4789 ELSE
4790 /*Lot status validations are done in lot_trx_split_validations seperately.
4791 *Here we are only concerned with serial status validations.
4792 *For Lot Split/Translation/Merge transactions we will only validate the status control for the
4793 *source serials
4794 */
4795 BEGIN
4796 IF (p_transaction_type_id IN
4797 (inv_globals.g_type_inv_lot_translate
4798 , inv_globals.g_type_inv_lot_split
4799 )
4800 )
4801 THEN
4802 IF (l_debug = 1)
4803 THEN
4804 print_debug ('breadcrumb 50', 'Validate_serials');
4805 print_debug
4806 ('Calling validate_serial_status for translate OR split'
4807 , 'Validate_serials'
4808 );
4809 END IF;
4810
4811 validate_serial_status
4812 (x_return_status => x_return_status
4813 , x_msg_count => x_msg_count
4814 , x_msg_data => x_msg_data
4815 , x_validation_status => l_validation_status
4816 , p_transaction_type_id => p_transaction_type_id
4817 , p_organization_id => p_st_org_id_tbl (1)
4818 , p_inventory_item_id => p_st_item_id_tbl (1)
4819 , p_serial_number => p_st_ser_number_tbl
4820 (l_st_var_index)
4821 , p_subinventory_code => p_st_sub_code_tbl (1)
4822 , p_locator_id => p_st_locator_id_tbl (1)
4823 , p_status_id => p_st_ser_status_tbl (i)
4824 );
4825
4826 IF (l_debug = 1)
4827 THEN
4828 print_debug ('breadcrumb 60', 'Validate_serials');
4829 END IF;
4830 ELSIF (p_transaction_type_id = inv_globals.g_type_inv_lot_merge)
4831 THEN
4832 IF (l_debug = 1)
4833 THEN
4834 print_debug ('breadcrumb 70', 'Validate_serials');
4835 print_debug ('Calling validate_serial_status for lot merge'
4836 , 'Validate_serials'
4837 );
4838 print_debug ('p_transaction_type_id ' || p_transaction_type_id
4839 , 'Validate_serials'
4840 );
4841 print_debug ('p_st_org_id_tbl (1) ' || p_st_org_id_tbl (1)
4842 , 'Validate_serials'
4843 );
4844 print_debug ('p_st_item_id_tbl (1) ' || p_st_item_id_tbl (1)
4845 , 'Validate_serials'
4846 );
4847 print_debug ('p_st_ser_number_tbl(l_st_var_index) ' || p_st_ser_number_tbl(l_st_var_index)
4848 , 'Validate_serials'
4849 );
4850 print_debug ('p_st_ser_parent_sub_tbl (l_st_var_index) ' || p_st_ser_parent_sub_tbl (l_st_var_index)
4851 , 'Validate_serials'
4852 );
4853 print_debug ('p_st_ser_parent_loc_tbl (l_st_var_index)' ||p_st_ser_parent_loc_tbl (l_st_var_index)
4854 , 'Validate_serials'
4855 );
4856 print_debug ('p_st_ser_status_tbl (i) ' || p_st_ser_status_tbl (i)
4857 , 'Validate_serials'
4858 );
4859
4860 END IF;
4861
4862 inv_lot_trx_validation_pub.validate_serial_status
4863 (x_return_status => x_return_status
4864 , x_msg_count => x_msg_count
4865 , x_msg_data => x_msg_data
4866 , x_validation_status => l_validation_status
4867 , p_transaction_type_id => p_transaction_type_id
4868 , p_organization_id => p_st_org_id_tbl (1)
4869 , p_inventory_item_id => p_st_item_id_tbl (1)
4870 , p_serial_number => p_st_ser_number_tbl(l_st_var_index)
4871 , p_subinventory_code => p_st_ser_parent_sub_tbl(l_st_var_index)
4872 , p_locator_id => p_st_ser_parent_loc_tbl(l_st_var_index)
4873 , p_status_id => p_st_ser_status_tbl (i)
4874 );
4875 IF (l_debug = 1)
4876 THEN
4877 print_debug ('breadcrumb 80', 'Validate_serials');
4878 END IF;
4879 END IF;
4880 EXCEPTION
4881 WHEN OTHERS
4882 THEN
4883 IF (l_debug = 1)
4884 THEN
4885 print_debug ('breadcrumb 90', 'Validate_serials');
4886 print_debug ('validate_serial_status rasied exception'
4887 , 'Validate_serials'
4888 );
4889 END IF;
4890
4891 fnd_message.set_name ('WMS', 'WMS_VALIDATE_STATUS_ERROR');
4892 fnd_msg_pub.ADD;
4893 fnd_msg_pub.count_and_get (p_count => x_msg_count
4894 , p_data => x_msg_data
4895 );
4896 l_validation_status := 'N';
4897 RAISE fnd_api.g_exc_unexpected_error;
4898 END;
4899
4900 IF (x_return_status = fnd_api.g_ret_sts_error)
4901 THEN
4902 IF (l_debug = 1)
4903 THEN
4904 print_debug ('breadcrumb 100', 'Validate_serials');
4905 print_debug ('validate_serial_status returned with error'
4906 , 'Validate_serials'
4907 );
4908 END IF;
4909
4910 l_validation_status := 'N';
4911 RAISE fnd_api.g_exc_error;
4912 ELSIF ( x_return_status = fnd_api.g_ret_sts_unexp_error
4913 OR l_validation_status <> 'Y'
4914 )
4915 THEN
4916 IF (l_debug = 1)
4917 THEN
4918 print_debug ('breadcrumb 110', 'Validate_serials');
4919 print_debug ('validate_serial_status returned with error (2)'
4920 , 'Validate_serials'
4921 );
4922 END IF;
4923
4924 RAISE fnd_api.g_exc_unexpected_error;
4925 END IF;
4926 END IF;
4927
4928 l_st_var_index := p_st_ser_number_tbl.NEXT (l_st_var_index);
4929 l_rs_var_index := p_rs_ser_number_tbl.NEXT (l_rs_var_index);
4930 END LOOP;
4931
4932 /*Not calling validate_serials for lot split and merge transactions as most
4933 *of the validations have already been done.
4934 */
4935 IF ( p_transaction_type_id = inv_globals.g_type_inv_lot_translate
4936 AND p_st_item_id_tbl (1) <> p_rs_item_id_tbl (1)
4937 )
4938 THEN
4939 IF (l_debug = 1)
4940 THEN
4941 print_debug ('breadcrumb 120', 'Validate_serials');
4942 END IF;
4943
4944 l_rs_var_index := p_rs_ser_number_tbl.FIRST;
4945
4946 FOR i IN 1 .. p_rs_ser_number_tbl.COUNT
4947 LOOP
4948 /*We are calling validate_serials so that if the item does not have this serial
4949 *then validate_serials will create a new serial. If the item has the serial in status
4950 *IN_STORES this validation should fail but not if it is in status UNDEFINED
4951 */
4952 IF (l_debug = 1)
4953 THEN
4954 print_debug ('breadcrumb 130', 'Validate_serials');
4955 print_debug ('Calling INV_SERIAL_NUMBER_PUB.VALIDATE_SERIALS', 'Validate_serials');
4956 END IF;
4957 l_qty := 0;
4958 l_end_ser := p_rs_ser_number_tbl(l_rs_var_index);
4959 IF (inv_serial_number_pub.validate_serials
4960 (p_org_id => p_rs_org_id_tbl (1)
4961 , p_item_id => p_rs_item_id_tbl (1)
4962 , p_qty => l_qty
4963 , p_lot => p_rs_lot_num_tbl (1)
4964 , p_start_ser => p_rs_ser_number_tbl(l_rs_var_index)
4965 , p_trx_src_id => inv_globals.g_sourcetype_inventory
4966 , p_trx_action_id => inv_globals.g_action_inv_lot_translate
4967 , p_issue_receipt => 'R'
4968 , p_check_for_grp_mark_id => 'Y'
4969 , x_end_ser => l_end_ser
4970 , x_proc_msg => l_proc_msg
4971 ) = 1
4972 )
4973 THEN
4974 IF (l_debug = 1)
4975 THEN
4976 print_debug ('breadcrumb 140', 'Validate_serials');
4977 END IF;
4978
4979 l_validation_status := 'N';
4980 fnd_message.set_name ('INV', 'INV_FAIL_VALIDATE_SERIAL');
4981 fnd_msg_pub.ADD;
4982 RAISE fnd_api.g_exc_error;
4983 END IF;
4984
4985 l_rs_var_index := p_rs_ser_number_tbl.NEXT (l_rs_var_index);
4986 END LOOP;
4987 END IF;
4988
4989 x_validation_status := 'Y';
4990 x_return_status := fnd_api.g_ret_sts_success;
4991 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
4992
4993 IF (l_debug = 1)
4994 THEN
4995 print_debug ('breadcrumb 150', 'Validate_serials');
4996 print_debug ('Serial Validations passed', 'Validate_serials');
4997 END IF;
4998 EXCEPTION
4999 WHEN fnd_api.g_exc_error
5000 THEN
5001 x_validation_status := l_validation_status;
5002 x_return_status := fnd_api.g_ret_sts_error;
5003 fnd_msg_pub.count_and_get (p_count => x_msg_count
5004 , p_data => x_msg_data);
5005 WHEN fnd_api.g_exc_unexpected_error
5006 THEN
5007 x_validation_status := l_validation_status;
5008 x_return_status := fnd_api.g_ret_sts_unexp_error;
5009 fnd_msg_pub.count_and_get (p_count => x_msg_count
5010 , p_data => x_msg_data);
5011 WHEN OTHERS
5012 THEN
5013 x_validation_status := 'E';
5014 x_return_status := fnd_api.g_ret_sts_unexp_error;
5015
5016 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
5017 THEN
5018 fnd_msg_pub.add_exc_msg (g_pkg_name, 'Validate_serials');
5019 END IF;
5020
5021 fnd_msg_pub.count_and_get (p_count => x_msg_count
5022 , p_data => x_msg_data);
5023 END validate_serials;
5024
5025 /*********************************************************************************************
5026 * This procedure will validate the lot expiration dates based on the
5027 shelf life code and depending on the type of lot transaction update
5028 the interface table with the correct shelf life code and shelf life dates
5029 *********************************************************************************************/
5030 PROCEDURE compute_lot_expiration (
5031 x_return_status OUT NOCOPY VARCHAR2
5032 , x_msg_count OUT NOCOPY NUMBER
5033 , x_msg_data OUT NOCOPY VARCHAR2
5034 , p_parent_id IN NUMBER
5035 , p_transaction_type_id IN NUMBER
5036 , p_item_id IN NUMBER
5037 , p_organization_id IN NUMBER
5038 , p_st_lot_num IN VARCHAR2
5039 , p_rs_lot_num_tbl IN lot_number_table
5040 , p_rs_lot_exp_tbl IN OUT NOCOPY date_table
5041 )
5042 IS
5043 l_shelf_life_code NUMBER;
5044 l_shelf_life_days NUMBER;
5045 l_lotexpdate VARCHAR2 (22);
5046 l_update BOOLEAN;
5047 BEGIN
5048 BEGIN
5049 SELECT shelf_life_code
5050 , shelf_life_days
5051 INTO l_shelf_life_code
5052 , l_shelf_life_days
5053 FROM mtl_system_items
5054 WHERE inventory_item_id = p_item_id
5055 AND organization_id = p_organization_id;
5056 EXCEPTION
5057 WHEN NO_DATA_FOUND
5058 THEN
5059 fnd_message.set_name ('INV', 'INV_INVALID_ITEM');
5060 fnd_msg_pub.ADD;
5061 RAISE fnd_api.g_exc_unexpected_error;
5062 END;
5063
5064 print_debug ('Shelf Life Period ' || l_shelf_life_code, 'Compute Lot Exp');
5065 print_debug ('Item ' || p_item_id, 'Compute Lot Exp');
5066 print_debug ('Org ' || p_organization_id, 'Compute Lot Exp');
5067 print_debug ('Parent _id ' || p_parent_id, 'Compute Lot Exp');
5068 print_debug ('Transaction Type id ' || p_transaction_type_id
5069 , 'Compute lot Exp'
5070 );
5071 print_debug ('Start Lot ' || p_st_lot_num, 'Compute Lot Exp');
5072
5073 IF (l_shelf_life_code = 2)
5074 THEN -- It is shelf life controlled. Get the
5075 --lot exp. date from the parent lot MTLN - pass the starting lot number.
5076 IF (p_transaction_type_id = inv_globals.g_type_inv_lot_split)
5077 THEN
5078 BEGIN
5079 SELECT fnd_date.date_to_canonical (expiration_date)
5080 INTO l_lotexpdate
5081 FROM mtl_lot_numbers
5082 WHERE inventory_item_id = p_item_id
5083 AND organization_id = p_organization_id
5084 AND lot_number = p_st_lot_num;
5085 EXCEPTION
5086 WHEN NO_DATA_FOUND
5087 THEN
5088 fnd_message.set_name ('INV', 'INV_INVALID_LOT');
5089 fnd_msg_pub.ADD;
5090 RAISE fnd_api.g_exc_unexpected_error;
5091 END;
5092
5093 print_debug ('Lot exp date after split1 ' || l_lotexpdate
5094 , 'Compute Lot Exp'
5095 );
5096
5097 IF (l_lotexpdate IS NULL)
5098 THEN
5099 fnd_message.set_name ('INV', 'INV_INVALID_LOT_EXP');
5100 fnd_msg_pub.ADD;
5101 RAISE fnd_api.g_exc_unexpected_error;
5102 END IF;
5103
5104 -- Update all the resulting lots with this exp date
5105 FOR i IN 1 .. p_rs_lot_exp_tbl.COUNT
5106 LOOP
5107 p_rs_lot_exp_tbl (i) := fnd_date.canonical_to_date (l_lotexpdate);
5108 END LOOP;
5109
5110 print_debug ('Lot exp date after split2 ' || l_lotexpdate
5111 , 'Compute Lot Exp'
5112 );
5113
5114 BEGIN
5115 UPDATE mtl_transaction_lots_interface mtli
5116 SET lot_expiration_date =
5117 fnd_date.canonical_to_date (l_lotexpdate)
5118 WHERE transaction_interface_id IN (
5119 SELECT transaction_interface_id
5120 FROM mtl_transactions_interface mti
5121 WHERE mti.parent_id = p_parent_id
5122 AND mti.transaction_interface_id =
5123 mtli.transaction_interface_id);
5124 EXCEPTION
5125 WHEN OTHERS
5126 THEN
5127 fnd_message.set_name ('INV', 'INV_UPDATE_ERROR');
5128 fnd_message.set_token ('ENTITY1'
5129 , 'MTL_TRANSACTION_LOTS_INTERFACE'
5130 );
5131 fnd_msg_pub.ADD;
5132 RAISE fnd_api.g_exc_unexpected_error;
5133 END;
5134
5135 print_debug ('Lot exp date after split3 ' || l_lotexpdate
5136 , 'Compute Lot Exp'
5137 );
5138 END IF;
5139
5140 IF (p_transaction_type_id = inv_globals.g_type_inv_lot_merge)
5141 THEN
5142 -- pass the resulting lot. if the resulting lot doesnt exist, then
5143 -- get the starting lot with the highest/rep lot and get the
5144 -- expiration DATE of that lot from the table. pass both in this case.
5145 BEGIN
5146 SELECT fnd_date.date_to_canonical (expiration_date)
5147 INTO l_lotexpdate
5148 FROM mtl_lot_numbers
5149 WHERE inventory_item_id = p_item_id
5150 AND organization_id = p_organization_id
5151 AND lot_number = p_rs_lot_num_tbl (1);
5152 EXCEPTION
5153 WHEN NO_DATA_FOUND
5154 THEN -- then get the exp date of the lot
5155 --either WITH the highet qty OR the rep. lot
5156 SELECT fnd_date.date_to_canonical (expiration_date)
5157 INTO l_lotexpdate
5158 FROM mtl_lot_numbers
5159 WHERE inventory_item_id = p_item_id
5160 AND organization_id = p_organization_id
5161 AND lot_number = p_st_lot_num; -- We only pass one
5162 -- lot here based on the highest qty or the rep. lot.
5163 END;
5164
5165 print_debug ('Lot exp date after merge1 ' || l_lotexpdate
5166 , 'Compute Lot Exp'
5167 );
5168
5169 IF (l_lotexpdate IS NULL)
5170 THEN
5171 fnd_message.set_name ('INV', 'INV_INVALID_LOT_EXP');
5172 fnd_msg_pub.ADD;
5173 RAISE fnd_api.g_exc_unexpected_error;
5174 END IF;
5175
5176 p_rs_lot_exp_tbl (1) := fnd_date.canonical_to_date (l_lotexpdate);
5177
5178 -- update the resulting lot with the exp. date.
5179 BEGIN
5180 UPDATE mtl_transaction_lots_interface mtli
5181 SET lot_expiration_date =
5182 fnd_date.canonical_to_date (l_lotexpdate)
5183 WHERE transaction_interface_id IN (
5184 SELECT transaction_interface_id
5185 FROM mtl_transactions_interface mti
5186 WHERE mti.parent_id = p_parent_id
5187 AND mti.parent_id = mti.transaction_interface_id
5188 AND mti.transaction_interface_id =
5189 mtli.transaction_interface_id);
5190 EXCEPTION
5191 WHEN OTHERS
5192 THEN
5193 fnd_message.set_name ('INV', 'INV_UPDATE_ERROR');
5194 fnd_message.set_token ('ENTITY1'
5195 , 'MTL_TRANSACTION_LOTS_INTERFACE'
5196 );
5197 fnd_msg_pub.ADD;
5198 RAISE fnd_api.g_exc_unexpected_error;
5199 END;
5200
5201 print_debug ('Lot exp date update merge2 ' || l_lotexpdate
5202 , 'Compute Lot Exp'
5203 );
5204 END IF;
5205
5206 IF (p_transaction_type_id = inv_globals.g_type_inv_lot_translate)
5207 THEN
5208 BEGIN
5209 SELECT fnd_date.date_to_canonical (expiration_date)
5210 INTO l_lotexpdate
5211 FROM mtl_lot_numbers
5212 WHERE inventory_item_id = p_item_id
5213 AND organization_id = p_organization_id
5214 AND lot_number = p_st_lot_num;
5215 EXCEPTION
5216 WHEN NO_DATA_FOUND
5217 THEN
5218 fnd_message.set_name ('INV', 'INV_INVALID_LOT');
5219 fnd_msg_pub.ADD;
5220 RAISE fnd_api.g_exc_unexpected_error;
5221 END;
5222
5223 print_debug ('Lot exp date after translate1 ' || l_lotexpdate
5224 , 'Compute Lot Exp'
5225 );
5226
5227 IF (l_lotexpdate IS NULL)
5228 THEN
5229 fnd_message.set_name ('INV', 'INV_INVALID_LOT_EXP');
5230 fnd_msg_pub.ADD;
5231 RAISE fnd_api.g_exc_unexpected_error;
5232 END IF;
5233
5234 BEGIN
5235 UPDATE mtl_transaction_lots_interface mtli
5236 SET lot_expiration_date =
5237 fnd_date.canonical_to_date (l_lotexpdate)
5238 WHERE transaction_interface_id IN (
5239 SELECT transaction_interface_id
5240 FROM mtl_transactions_interface mti
5241 WHERE mti.parent_id = p_parent_id
5242 AND mti.transaction_interface_id =
5243 mtli.transaction_interface_id);
5244 EXCEPTION
5245 WHEN OTHERS
5246 THEN
5247 fnd_message.set_name ('INV', 'INV_UPDATE_ERROR');
5248 fnd_message.set_token ('ENTITY1'
5249 , 'MTL_TRANSACTION_LOTS_INTERFACE'
5250 );
5251 fnd_msg_pub.ADD;
5252 RAISE fnd_api.g_exc_unexpected_error;
5253 END;
5254
5255 print_debug ('Lot exp date update translate2 ' || l_lotexpdate
5256 , 'Compute Lot Exp'
5257 );
5258 END IF;
5259 ELSIF (l_shelf_life_code = 4)
5260 THEN
5261 IF (p_transaction_type_id = inv_globals.g_type_inv_lot_split)
5262 THEN
5263 -- get all the child records and check to see if the lot is
5264 -- specified. If it is, then use it. - else get the exp. date
5265 -- from the starting lot.
5266 BEGIN
5267 SELECT fnd_date.date_to_canonical (expiration_date)
5268 INTO l_lotexpdate
5269 FROM mtl_lot_numbers
5270 WHERE inventory_item_id = p_item_id
5271 AND organization_id = p_organization_id
5272 AND lot_number = p_st_lot_num;
5273 EXCEPTION
5274 WHEN NO_DATA_FOUND
5275 THEN
5276 fnd_message.set_name ('INV', 'INV_INVALID_LOT');
5277 fnd_msg_pub.ADD;
5278 RAISE fnd_api.g_exc_unexpected_error;
5279 END;
5280
5281 print_debug ('Lot exp date user defined :split1 ' || l_lotexpdate
5282 , 'Compute Lot Exp'
5283 );
5284
5285 -- Update all the resulting lots with this exp date
5286 FOR i IN 1 .. p_rs_lot_exp_tbl.COUNT
5287 LOOP
5288 IF (p_rs_lot_exp_tbl (i) IS NULL)
5289 THEN
5290 p_rs_lot_exp_tbl (i) := fnd_date.canonical_to_date (l_lotexpdate);
5291 END IF;
5292 END LOOP;
5293
5294 print_debug ('Lot exp date user defined :split2 ' || l_lotexpdate
5295 , 'Compute Lot Exp'
5296 );
5297
5298 IF (l_lotexpdate IS NULL)
5299 THEN
5300 fnd_message.set_name ('INV', 'INV_INVALID_LOT_EXP');
5301 fnd_msg_pub.ADD;
5302 RAISE fnd_api.g_exc_unexpected_error;
5303 END IF;
5304
5305 BEGIN
5306 UPDATE mtl_transaction_lots_interface mtli
5307 SET lot_expiration_date =
5308 fnd_date.canonical_to_date (l_lotexpdate)
5309 WHERE transaction_interface_id IN (
5310 SELECT transaction_interface_id
5311 FROM mtl_transactions_interface mti
5312 WHERE mti.parent_id = p_parent_id
5313 AND mti.transaction_interface_id =
5314 mtli.transaction_interface_id
5315 AND mtli.lot_expiration_date IS NULL);
5316 EXCEPTION
5317 WHEN OTHERS
5318 THEN
5319 fnd_message.set_name ('INV', 'INV_UPDATE_ERROR');
5320 fnd_message.set_token ('ENTITY1'
5321 , 'MTL_TRANSACTION_LOTS_INTERFACE'
5322 );
5323 fnd_msg_pub.ADD;
5324 RAISE fnd_api.g_exc_unexpected_error;
5325 END;
5326
5327 print_debug ( 'Lot exp date user defined : after update split3 '
5328 || l_lotexpdate
5329 , 'Compute Lot Exp'
5330 );
5331 END IF;
5332
5333 IF (p_transaction_type_id = inv_globals.g_type_inv_lot_merge)
5334 THEN
5335 l_update := TRUE;
5336
5337 BEGIN
5338 SELECT fnd_date.date_to_canonical (expiration_date)
5339 INTO l_lotexpdate
5340 FROM mtl_lot_numbers
5341 WHERE inventory_item_id = p_item_id
5342 AND organization_id = p_organization_id
5343 AND lot_number = p_rs_lot_num_tbl (1);
5344 EXCEPTION
5345 WHEN NO_DATA_FOUND
5346 THEN
5347 print_debug ('Lot exp date user defined : Merge1' || l_lotexpdate
5348 , 'Compute Lot Exp'
5349 );
5350
5351 IF (p_rs_lot_exp_tbl (1) IS NULL)
5352 THEN
5353 BEGIN
5354 SELECT fnd_date.date_to_canonical (expiration_date)
5355 INTO l_lotexpdate
5356 FROM mtl_lot_numbers
5357 WHERE inventory_item_id = p_item_id
5358 AND organization_id = p_organization_id
5359 AND lot_number = p_st_lot_num;
5360 EXCEPTION
5361 WHEN NO_DATA_FOUND
5362 THEN
5363 print_debug ( 'Lot exp date user defined : Merge2 '
5364 || l_lotexpdate
5365 , 'Compute Lot Exp'
5366 );
5367 fnd_message.set_name ('INV', 'INV_INVALID_LOT');
5368 fnd_msg_pub.ADD;
5369 RAISE fnd_api.g_exc_unexpected_error;
5370 END;
5371
5372 print_debug ( 'Lot exp date user defined : Merge3 '
5373 || l_lotexpdate
5374 , 'Compute Lot Exp'
5375 );
5376 ELSE
5377 l_update := FALSE;
5378 print_debug ('Lot exp date user defined : Merge5'
5379 || l_lotexpdate
5380 , 'Compute Lot Exp'
5381 );
5382 END IF;
5383 END;
5384
5385 IF l_update
5386 THEN
5387 IF (l_lotexpdate IS NULL)
5388 THEN
5389 fnd_message.set_name ('INV', 'INV_INVALID_LOT_EXP');
5390 fnd_msg_pub.ADD;
5391 RAISE fnd_api.g_exc_unexpected_error;
5392 END IF;
5393
5394 BEGIN
5395 UPDATE mtl_transaction_lots_interface mtli
5396 SET lot_expiration_date =
5397 fnd_date.canonical_to_date (l_lotexpdate)
5398 WHERE transaction_interface_id IN (
5399 SELECT transaction_interface_id
5400 FROM mtl_transactions_interface mti
5401 WHERE mti.parent_id = p_parent_id
5402 AND mti.parent_id = mti.transaction_interface_id
5403 AND mti.transaction_interface_id =
5404 mtli.transaction_interface_id);
5405 EXCEPTION
5406 WHEN OTHERS
5407 THEN
5408 fnd_message.set_name ('INV', 'INV_UPDATE_ERROR');
5409 fnd_message.set_token ('ENTITY1'
5410 , 'MTL_TRANSACTION_LOTS_INTERFACE'
5411 );
5412 fnd_msg_pub.ADD;
5413 RAISE fnd_api.g_exc_unexpected_error;
5414 END;
5415
5416 print_debug ( 'Lot exp date user defined : after update Merge6 '
5417 || l_lotexpdate
5418 , 'Compute Lot Exp'
5419 );
5420 END IF;
5421 END IF;
5422
5423 IF (p_transaction_type_id = inv_globals.g_type_inv_lot_translate)
5424 THEN
5425 IF (p_rs_lot_exp_tbl (1) IS NULL)
5426 THEN
5427 BEGIN
5428 SELECT fnd_date.date_to_canonical (expiration_date)
5429 INTO l_lotexpdate
5430 FROM mtl_lot_numbers
5431 WHERE inventory_item_id = p_item_id
5432 AND organization_id = p_organization_id
5433 AND lot_number = p_st_lot_num;
5434 EXCEPTION
5435 WHEN NO_DATA_FOUND
5436 THEN
5437 fnd_message.set_name ('INV', 'INV_INVALID_LOT');
5438 fnd_msg_pub.ADD;
5439 RAISE fnd_api.g_exc_unexpected_error;
5440 END;
5441
5442 print_debug ( 'Lot exp date user defined : Translate1 '
5443 || l_lotexpdate
5444 , 'Compute Lot Exp'
5445 );
5446
5447 IF (l_lotexpdate IS NULL)
5448 THEN
5449 fnd_message.set_name ('INV', 'INV_INVALID_LOT_EXP');
5450 fnd_msg_pub.ADD;
5451 RAISE fnd_api.g_exc_unexpected_error;
5452 END IF;
5453
5454 BEGIN
5455 UPDATE mtl_transaction_lots_interface mtli
5456 SET lot_expiration_date =
5457 fnd_date.canonical_to_date (l_lotexpdate)
5458 WHERE transaction_interface_id IN (
5459 SELECT transaction_interface_id
5460 FROM mtl_transactions_interface mti
5461 WHERE mti.parent_id = p_parent_id
5462 AND mti.transaction_interface_id =
5463 mtli.transaction_interface_id
5464 AND mtli.lot_expiration_date IS NULL);
5465 EXCEPTION
5466 WHEN OTHERS
5467 THEN
5468 fnd_message.set_name ('INV', 'INV_UPDATE_ERROR');
5469 fnd_message.set_token ('ENTITY1'
5470 , 'MTL_TRANSACTION_LOTS_INTERFACE'
5471 );
5472 fnd_msg_pub.ADD;
5473 RAISE fnd_api.g_exc_unexpected_error;
5474 END;
5475
5476 print_debug
5477 ( 'Lot exp date user defined : after update Translate'
5478 || l_lotexpdate
5479 , 'Compute Lot Exp'
5480 );
5481 END IF;
5482 END IF;
5483 END IF;
5484
5485 x_return_status := fnd_api.g_ret_sts_success;
5486 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
5487 EXCEPTION
5488 WHEN fnd_api.g_exc_error
5489 THEN
5490 x_return_status := fnd_api.g_ret_sts_error;
5491 fnd_msg_pub.count_and_get (p_count => x_msg_count
5492 , p_data => x_msg_data);
5493 WHEN fnd_api.g_exc_unexpected_error
5494 THEN
5495 x_return_status := fnd_api.g_ret_sts_unexp_error;
5496 fnd_msg_pub.count_and_get (p_count => x_msg_count
5497 , p_data => x_msg_data);
5498 WHEN OTHERS
5499 THEN
5500 x_return_status := fnd_api.g_ret_sts_unexp_error;
5501
5502 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
5503 THEN
5504 fnd_msg_pub.add_exc_msg (g_pkg_name, 'compute_lot_expiration');
5505 END IF;
5506
5507 fnd_msg_pub.count_and_get (p_count => x_msg_count
5508 , p_data => x_msg_data);
5509 END compute_lot_expiration;
5510
5511 PROCEDURE update_item_serial (
5512 x_msg_count OUT NOCOPY VARCHAR2
5513 , x_return_status OUT NOCOPY VARCHAR2
5514 , x_msg_data OUT NOCOPY VARCHAR2
5515 , x_validation_status OUT NOCOPY VARCHAR2
5516 , p_org_id IN NUMBER
5517 , p_item_id IN NUMBER
5518 , p_to_item_id IN NUMBER DEFAULT NULL
5519 , p_wip_entity_id IN NUMBER
5520 , p_to_wip_entity_id IN NUMBER DEFAULT NULL
5521 , p_to_operation_sequence IN NUMBER DEFAULT NULL
5522 , p_intraoperation_step_type IN NUMBER DEFAULT NULL
5523 )
5524 IS
5525 l_restrict_serial_rcpt NUMBER;
5526 rollback_serial_update EXCEPTION;
5527
5528 TYPE osfm_ser_tbl IS TABLE OF mtl_serial_numbers.serial_number%TYPE;
5529
5530 l_ser_number_tbl osfm_ser_tbl;
5531 l_attributes_default_count NUMBER;
5532 l_ret NUMBER;
5533 l_context_value_item VARCHAR2 (30) := NULL;
5534 l_context_value_to_item VARCHAR2 (30) := NULL;
5535 l_update_attr BOOLEAN := FALSE;
5536 l_debug BOOLEAN := TRUE;
5537 BEGIN
5538 x_validation_status := 'Y';
5539 x_return_status := fnd_api.g_ret_sts_success;
5540
5541 IF (NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0) = 0)
5542 THEN
5543 l_debug := FALSE;
5544 END IF;
5545
5546 IF (l_debug)
5547 THEN
5548 print_debug ('p_inventory_item_id is ' || p_item_id
5549 , 'update_item_serial'
5550 );
5551 print_debug ('p_wip_entity_id is ' || p_wip_entity_id
5552 , 'update_item_serial'
5553 );
5554 print_debug ('p_to_inventory_item_id is ' || p_to_item_id
5555 , 'update_item_serial'
5556 );
5557 print_debug ('p_to_wip_entity_id is ' || p_to_wip_entity_id
5558 , 'update_item_serial'
5559 );
5560 print_debug ('p_to_operation_sequence is ' || p_to_operation_sequence
5561 , 'update_item_serial'
5562 );
5563 print_debug ( 'p_intra_operation_step_type is '
5564 || p_intraoperation_step_type
5565 , 'update_item_serial'
5566 );
5567 END IF;
5568
5569 IF (p_item_id IS NULL OR p_wip_entity_id IS NULL OR p_org_id IS NULL)
5570 THEN
5571 IF (l_debug)
5572 THEN
5573 print_debug ('Either item id or wip entity id or org id is NULL'
5574 , 'update_item_serial'
5575 );
5576 END IF;
5577
5578 x_validation_status := 'N';
5579 RAISE fnd_api.g_exc_unexpected_error;
5580 END IF;
5581
5582 SAVEPOINT initial_state_svpt;
5583 l_restrict_serial_rcpt :=
5584 NVL (fnd_profile.VALUE ('INV_RESTRICT_RCPT_SER'), 2);
5585
5586 IF (l_debug)
5587 THEN
5588 print_debug ('l_restrict_serial_rcpt ' || l_restrict_serial_rcpt
5589 , 'update_item_serial'
5590 );
5591 END IF;
5592
5593 SELECT serial_number
5594 BULK COLLECT INTO l_ser_number_tbl
5595 FROM mtl_serial_numbers msn
5596 WHERE msn.inventory_item_id = p_item_id
5597 AND msn.wip_entity_id = p_wip_entity_id
5598 AND ( msn.intraoperation_step_type IS NULL
5599 OR msn.intraoperation_step_type <> 5
5600 )
5601 AND ( (msn.current_status IN (1, 6))
5602 OR ( l_restrict_serial_rcpt = 2
5603 AND msn.current_status = 4
5604 AND msn.last_txn_source_id = p_wip_entity_id
5605 AND NVL (msn.last_txn_source_type_id, -9999) = 5
5606 )
5607 );
5608
5609 IF (l_ser_number_tbl.COUNT = 0)
5610 THEN
5611 IF (l_debug)
5612 THEN
5613 print_debug ('l_ser_number_tbl.COUNT' || l_ser_number_tbl.COUNT
5614 , 'update_item_serial'
5615 );
5616 print_debug ('returning..', 'update_item_serial');
5617 END IF;
5618
5619 x_validation_status := 'Y';
5620 x_return_status := fnd_api.g_ret_sts_success;
5621 RETURN;
5622 END IF;
5623
5624 IF ((p_to_item_id IS NOT NULL) AND (p_item_id <> p_to_item_id))
5625 THEN
5626 BEGIN
5627 BEGIN
5628
5629 SELECT descriptive_flex_context_code
5630 INTO l_context_value_item
5631 FROM mtl_flex_context
5632 WHERE organization_id = p_org_id
5633 AND context_column_name = 'ITEM'
5634 AND descriptive_flexfield_name = 'Serial Attributes'
5635 AND context_column_value_id = p_item_id;
5636 EXCEPTION
5637 WHEN NO_DATA_FOUND
5638 THEN
5639 l_context_value_item := NULL;
5640 END;
5641
5642 BEGIN
5643 SELECT descriptive_flex_context_code
5644 INTO l_context_value_to_item
5645 FROM mtl_flex_context
5646 WHERE organization_id = p_org_id
5647 AND context_column_name = 'ITEM'
5648 AND descriptive_flexfield_name = 'Serial Attributes'
5649 AND context_column_value_id = p_to_item_id;
5650 EXCEPTION
5651 WHEN NO_DATA_FOUND
5652 THEN
5653 l_context_value_to_item := NULL;
5654 END;
5655 EXCEPTION
5656 WHEN OTHERS
5657 THEN
5658 l_context_value_item := NULL;
5659 l_context_value_to_item := NULL;
5660 END;
5661
5662 IF ( l_context_value_item IS NULL
5663 OR l_context_value_to_item IS NULL
5664 OR (l_context_value_item <> l_context_value_to_item)
5665 )
5666 THEN
5667 IF (l_debug)
5668 THEN
5669 print_debug
5670 ('Mismatch between source and dest Item attributes. Need to null out'
5671 , 'update_item_serial'
5672 );
5673 END IF;
5674
5675 l_update_attr := TRUE;
5676 END IF;
5677 END IF;
5678
5679 FOR i IN l_ser_number_tbl.FIRST .. l_ser_number_tbl.LAST
5680 LOOP
5681 IF (p_to_item_id IS NOT NULL AND p_to_item_id <> p_item_id)
5682 THEN
5683 IF (l_debug)
5684 THEN
5685 print_debug ('Calling is_serial_unique to check serial uniqueness'
5686 , 'update_item_serial'
5687 );
5688 END IF;
5689
5690 l_ret :=
5691 inv_serial_number_pub.is_serial_unique (p_org_id
5692 , p_to_item_id
5693 , l_ser_number_tbl (i)
5694 , x_msg_data
5695 );
5696
5697 IF (l_debug)
5698 THEN
5699 print_debug ('is_serial_unique returned with l_ret ' || l_ret
5700 , 'update_item_serial'
5701 );
5702 END IF;
5703
5704 IF (l_ret = 1)
5705 THEN
5706 IF (l_debug)
5707 THEN
5708 print_debug ( 'serial_uniqueness failed for serial=> '
5709 || l_ser_number_tbl (i)
5710 , 'update_item_serial'
5711 );
5712 END IF;
5713
5714 x_validation_status := 'N';
5715 /* Bug:5162705.Modified the message name from INV_SERIAL_NOT_UNIQUE
5716 to INV_SERIAL_UNIQUENESS */
5717 /*Bug:5397573. Modified the following message from INV_SERIAL_UNIQUENESS
5718 to INV_JOB_SERIAL_UNIQUENESS. */
5719 fnd_message.set_name ('INV', 'INV_JOB_SERIAL_UNIQUENESS');
5720 fnd_msg_pub.ADD;
5721 RAISE fnd_api.g_exc_error;
5722 END IF;
5723
5724 IF (l_update_attr) THEN
5725 BEGIN
5726 IF (l_debug)
5727 THEN
5728 print_debug ('Null out the attributes and update the MSN'
5729 , 'update_item_serial'
5730 );
5731 END IF;
5732
5733 UPDATE mtl_serial_numbers
5734 SET inventory_item_id = NVL (p_to_item_id, inventory_item_id)
5735 , wip_entity_id = NVL (p_to_wip_entity_id, wip_entity_id)
5736 , operation_seq_num = p_to_operation_sequence
5737 , intraoperation_step_type = p_intraoperation_step_type
5738 , serial_attribute_category = NULL
5739 , c_attribute1 = NULL
5740 , c_attribute2 = NULL
5741 , c_attribute3 = NULL
5742 , c_attribute4 = NULL
5743 , c_attribute5 = NULL
5744 , c_attribute6 = NULL
5745 , c_attribute7 = NULL
5746 , c_attribute8 = NULL
5747 , c_attribute9 = NULL
5748 , c_attribute10 = NULL
5749 , c_attribute11 = NULL
5750 , c_attribute12 = NULL
5751 , c_attribute13 = NULL
5752 , c_attribute14 = NULL
5753 , c_attribute15 = NULL
5754 , c_attribute16 = NULL
5755 , c_attribute17 = NULL
5756 , c_attribute18 = NULL
5757 , c_attribute19 = NULL
5758 , c_attribute20 = NULL
5759 , d_attribute1 = NULL
5760 , d_attribute2 = NULL
5761 , d_attribute3 = NULL
5762 , d_attribute4 = NULL
5763 , d_attribute5 = NULL
5764 , d_attribute6 = NULL
5765 , d_attribute7 = NULL
5766 , d_attribute8 = NULL
5767 , d_attribute9 = NULL
5768 , d_attribute10 = NULL
5769 , n_attribute1 = NULL
5770 , n_attribute2 = NULL
5771 , n_attribute3 = NULL
5772 , n_attribute4 = NULL
5773 , n_attribute5 = NULL
5774 , n_attribute6 = NULL
5775 , n_attribute7 = NULL
5776 , n_attribute8 = NULL
5777 , n_attribute9 = NULL
5778 , n_attribute10 = NULL
5779 , attribute_category = NULL
5780 , attribute1 = NULL
5781 , attribute2 = NULL
5782 , attribute3 = NULL
5783 , attribute4 = NULL
5784 , attribute5 = NULL
5785 , attribute6 = NULL
5786 , attribute7 = NULL
5787 , attribute8 = NULL
5788 , attribute9 = NULL
5789 , attribute10 = NULL
5790 , attribute11 = NULL
5791 , attribute12 = NULL
5792 , attribute13 = NULL
5793 , attribute14 = NULL
5794 , attribute15 = NULL
5795 , territory_code = NULL
5796 , time_since_new = NULL
5797 , cycles_since_new = NULL
5798 , time_since_overhaul = NULL
5799 , cycles_since_overhaul = NULL
5800 , time_since_repair = NULL
5801 , cycles_since_repair = NULL
5802 , time_since_visit = NULL
5803 , cycles_since_visit = NULL
5804 , time_since_mark = NULL
5805 , cycles_since_mark = NULL
5806 , number_of_repairs = NULL
5807 WHERE inventory_item_id = p_item_id
5808 AND current_organization_id = p_org_id
5809 AND wip_entity_id = p_wip_entity_id
5810 AND serial_number = l_ser_number_tbl (i);
5811
5812 EXCEPTION
5813 WHEN OTHERS
5814 THEN
5815 x_validation_status := 'N';
5816 RAISE rollback_serial_update;
5817 END;
5818 ELSE
5819 BEGIN
5820 IF (l_debug) THEN
5821 print_debug ('Update MSN when p_to_item_id <> p_item_id', 'update_item_serial');
5822 END IF;
5823
5824 UPDATE mtl_serial_numbers
5825 SET inventory_item_id = NVL (p_to_item_id, inventory_item_id)
5826 , wip_entity_id = NVL (p_to_wip_entity_id, wip_entity_id)
5827 , operation_seq_num = p_to_operation_sequence
5828 , intraoperation_step_type = p_intraoperation_step_type
5829 WHERE inventory_item_id = p_item_id
5830 AND current_organization_id = p_org_id
5831 AND wip_entity_id = p_wip_entity_id
5832 AND serial_number = l_ser_number_tbl (i);
5833 EXCEPTION
5834 WHEN OTHERS THEN
5835 x_validation_status := 'N';
5836 RAISE rollback_serial_update;
5837 END;
5838 END IF; --END IF (l_update_attr)
5839 --Bug #5364039
5840 --Should update if p_to_item_id = p_item_id and other parameters change
5841 ELSE
5842 BEGIN
5843 IF (l_debug) THEN
5844 print_debug ('Update MSN when p_to_item_id = p_item_id', 'update_item_serial');
5845 END IF;
5846
5847 UPDATE mtl_serial_numbers
5848 SET inventory_item_id = NVL (p_to_item_id, inventory_item_id)
5849 , wip_entity_id = NVL (p_to_wip_entity_id, wip_entity_id)
5850 , operation_seq_num = p_to_operation_sequence
5851 , intraoperation_step_type = p_intraoperation_step_type
5852 WHERE inventory_item_id = p_item_id
5853 AND current_organization_id = p_org_id
5854 AND wip_entity_id = p_wip_entity_id
5855 AND serial_number = l_ser_number_tbl (i);
5856 EXCEPTION
5857 WHEN OTHERS THEN
5858 x_validation_status := 'N';
5859 RAISE rollback_serial_update;
5860 END;
5861 END IF; --END IF (p_to_item_id IS NOT NULL AND p_to_item_id <> p_item_id)
5862 END LOOP; --END FOR i IN l_ser_number_tbl.FIRST .. l_ser_number_tbl.LAST
5863
5864 IF (l_debug) THEN
5865 print_debug ('All updations done, Exitting the procedure', 'update_item_serial');
5866 END IF;
5867
5868 EXCEPTION
5869 WHEN fnd_api.g_exc_error
5870 THEN
5871 x_return_status := fnd_api.g_ret_sts_error;
5872 fnd_msg_pub.count_and_get (p_count => x_msg_count
5873 , p_data => x_msg_data);
5874 WHEN fnd_api.g_exc_unexpected_error
5875 THEN
5876 x_return_status := fnd_api.g_ret_sts_unexp_error;
5877 fnd_msg_pub.count_and_get (p_count => x_msg_count
5878 , p_data => x_msg_data);
5879 WHEN rollback_serial_update
5880 THEN
5881 ROLLBACK TO initial_state_svpt;
5882 WHEN OTHERS
5883 THEN
5884 x_return_status := fnd_api.g_ret_sts_unexp_error;
5885
5886 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
5887 THEN
5888 fnd_msg_pub.add_exc_msg (g_pkg_name, 'update_item_serial');
5889 END IF;
5890
5891 fnd_msg_pub.count_and_get (p_count => x_msg_count
5892 , p_data => x_msg_data);
5893 ROLLBACK TO initial_state_svpt;
5894 END;
5895 END inv_lot_trx_validation_pub;