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