[Home] [Help]
PACKAGE BODY: APPS.INV_RESERVATION_VALIDATE_PVT
Source
1 PACKAGE BODY inv_reservation_validate_pvt AS
2 /* $Header: INVRSV1B.pls 120.27 2008/05/07 16:10:05 rkatoori ship $ */
3 g_pkg_name CONSTANT VARCHAR2(30) := 'INV_RESERVATION_VALIDATE_PVT';
4
5
6 /*** {{ R12 Enhanced reservations code changes ***/
7 g_debug NUMBER;
8
9 -- procedure to print a message to dbms_output
10 -- disable by default since dbm_s_output.put_line is not allowed
11 PROCEDURE debug_print(p_message IN VARCHAR2, p_level IN NUMBER := 9) IS
12 BEGIN
13 inv_log_util.TRACE(p_message, 'INV_RESERVATION_VALIDATE_PVT', p_level);
14 END debug_print;
15 /*** End R12 }} ***/
16
17 --
18 -- Procedure
19 -- validate_organization
20 -- Description
21 -- is valid if all of the following are satisfied
22 -- 1. p_organization_id is null
23 -- 2. p_organization_id is in mtl_parameters
24 PROCEDURE validate_organization
25 (
26 x_return_status OUT NOCOPY VARCHAR2
27 , p_organization_id IN NUMBER
28 , x_org_cache_index out NOCOPY INTEGER
29 ) IS
30 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
31 l_index NUMBER := NULL;
32 l_rec inv_reservation_global.organization_record;
33
34 BEGIN
35 --
36 IF p_organization_id IS NULL THEN
37 fnd_message.set_name('INV', 'INV_NO ORG INFORMATION');
38 fnd_msg_pub.add;
39 RAISE fnd_api.g_exc_error;
40 END IF;
41 --
42 inv_reservation_util_pvt.search_organization_cache
43 (
44 x_return_status => l_return_status
45 , p_organization_id => p_organization_id
46 , x_index => l_index
47 );
48 --
49 IF l_return_status = fnd_api.g_ret_sts_error THEN
50 RAISE fnd_api.g_exc_error;
51 End IF ;
52 --
53 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
54 RAISE fnd_api.g_exc_unexpected_error;
55 End IF;
56 --
57 IF l_index IS NULL THEN
58 /* BEGIN
59 SELECT
60 organization_id
61 , negative_inv_receipt_code
62 , project_reference_enabled
63 , stock_locator_control_code
64 INTO l_rec
65 FROM mtl_parameters
66 WHERE organization_id = p_organization_id;
67
68 EXCEPTION
69 WHEN no_data_found THEN
70 fnd_message.set_name('INV', 'INVALID ORGANIZATION');
71 fnd_msg_pub.add;
72 RAISE fnd_api.g_exc_error;
73 END;
74 */
75 -- Modified to call common API
76 l_rec.organization_id:=p_organization_id;
77 IF INV_Validate.Organization(
78 p_org => l_rec
79 )=INV_Validate.F THEN
80 fnd_message.set_name('INV', 'INVALID ORGANIZATION');
81 fnd_msg_pub.add;
82 RAISE fnd_api.g_exc_error;
83 END IF;
84
85 --
86 inv_reservation_util_pvt.add_organization_cache
87 (
88 x_return_status => l_return_status
89 , p_organization_record => l_rec
90 , x_index => l_index
91 );
92 --
93 IF l_return_status = fnd_api.g_ret_sts_error THEN
94 RAISE fnd_api.g_exc_error;
95 End IF ;
96
97 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
98 RAISE fnd_api.g_exc_unexpected_error;
99 End IF;
100 --
101 END IF;
102 --
103 x_org_cache_index := l_index;
104 x_return_status := l_return_status;
105 --
106 EXCEPTION
107 WHEN fnd_api.g_exc_error THEN
108 x_return_status := fnd_api.g_ret_sts_error;
109 --
110 WHEN fnd_api.g_exc_unexpected_error THEN
111 x_return_status := fnd_api.g_ret_sts_unexp_error ;
112 --
113 WHEN OTHERS THEN
114 x_return_status := fnd_api.g_ret_sts_unexp_error ;
115 --
116 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
117 THEN
118 fnd_msg_pub.add_exc_msg
119 ( g_pkg_name
120 , 'Validate_Organization'
121 );
122 END IF;
123 --
124 END validate_organization;
125 --
126 -- Procedure
127 -- validate_item
128 -- Description
129 -- is valid if all of the following are satisfied
130 -- 1. p_inventory_item_id is not null
131 -- 2. p_inventory_item_id is in mtl_system_items table
132 PROCEDURE validate_item
133 (
134 x_return_status OUT NOCOPY VARCHAR2
135 , p_inventory_item_id IN NUMBER
136 , p_organization_id IN NUMBER
137 , x_item_cache_index OUT NOCOPY INTEGER
138 ) IS
139 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
140 l_rec inv_reservation_global.item_record;
141 l_index NUMBER := NULL;
142 -- Added to call common API
143 l_rec_org inv_reservation_global.organization_record;
144
145 BEGIN
146 l_rec_org.organization_id:=p_organization_id;
147 --
148 IF p_inventory_item_id IS NULL THEN
149 fnd_message.set_name('INV', 'INV_ENTER_ITEM');
150 fnd_msg_pub.add;
151 RAISE fnd_api.g_exc_error;
152 END IF;
153 --
154 inv_reservation_util_pvt.search_item_cache
155 (
156 x_return_status => l_return_status
157 , p_inventory_item_id => p_inventory_item_id
158 , p_organization_id => p_organization_id
159 , x_index => l_index
160 );
161 --
162 IF l_return_status = fnd_api.g_ret_sts_error THEN
163 RAISE fnd_api.g_exc_error;
164 End IF ;
165 --
166 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
167 RAISE fnd_api.g_exc_unexpected_error;
168 End IF;
169 --
170 IF l_index IS NULL THEN
171 /* BEGIN -- not in cache, load it
172 SELECT
173 inventory_item_id
174 , organization_id
175 , lot_control_code
176 , serial_number_control_code
177 , reservable_type
178 , restrict_subinventories_code
179 , restrict_locators_code
180 , revision_qty_control_code
181 , location_control_code
182 , primary_uom_code
183 INTO l_rec
184 FROM
185 mtl_system_items
186 WHERE
187 inventory_item_id = p_inventory_item_id
188 AND organization_id = p_organization_id ;
189 EXCEPTION
190 WHEN no_data_found THEN
191 fnd_message.set_name('INV', 'INVALID ORGANIZATION');
192 fnd_msg_pub.add;
193 RAISE fnd_api.g_exc_error;
194 END;*/
195 -- Modified to call new common API
196 l_rec.inventory_item_id:=p_inventory_item_id;
197 IF INV_Validate.Inventory_Item(
198 p_item => l_rec,
199 p_org => l_rec_org
200 )=INV_Validate.F THEN
201 fnd_message.set_name('INV', 'INVALID ITEM');
202 fnd_msg_pub.add;
203 RAISE fnd_api.g_exc_error;
204 END IF;
205
206 --
207 IF l_rec.reservable_type = 2 THEN /* non reservable item */
208 fnd_message.set_name('INV','INV-ITEM NOT RESERVABLE');
209 fnd_msg_pub.add;
210 RAISE fnd_api.g_exc_error;
211 END IF;
212 --
213 inv_reservation_util_pvt.add_item_cache
214 (
215 x_return_status => l_return_status
216 , p_item_record => l_rec
217 , x_index => l_index
218 );
219 --
220 IF l_return_status = fnd_api.g_ret_sts_error THEN
221 RAISE fnd_api.g_exc_error;
222 End IF ;
223 --
224 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
225 RAISE fnd_api.g_exc_unexpected_error;
226 END IF;
227 END IF;
228 --
229 x_item_cache_index := l_index;
230 x_return_status := l_return_status;
231 --
232 EXCEPTION
233 WHEN fnd_api.g_exc_error THEN
234 x_return_status := fnd_api.g_ret_sts_error;
235 --
236 WHEN fnd_api.g_exc_unexpected_error THEN
237 x_return_status := fnd_api.g_ret_sts_unexp_error ;
238 --
239 WHEN OTHERS THEN
240 x_return_status := fnd_api.g_ret_sts_unexp_error ;
241 --
242 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
243 THEN
244 fnd_msg_pub.add_exc_msg
245 ( g_pkg_name
246 , 'Validate_Item'
247 );
248 END IF;
249 --
250 END validate_item;
251
252 --Procedure
253 -- validate_supply_source_po
254 -- Description
255 -- Validation for supply source of Purchase Order.
256 -- Currently, only validate for existence of a distribution line id
257 -- with the specified header id. In future, we may also want to
258 -- to validate quantity.
259 -- Added for bug 1947824
260 PROCEDURE validate_supply_source_po
261 (
262 x_return_status OUT NOCOPY VARCHAR2
263 , p_organization_id IN NUMBER /*** {{ R12 Enhanced reservations code changes }}***/
264 , p_inventory_item_id IN NUMBER /*** {{ R12 Enhanced reservations code changes }}***/
265 , p_demand_ship_date IN DATE /*** {{ R12 Enhanced reservations code changes }}***/
266 , p_supply_receipt_date IN DATE /*** {{ R12 Enhanced reservations code changes }}***/
267 , p_supply_source_type_id IN NUMBER /*** {{ R12 Enhanced reservations code changes }}***/
268 , p_supply_source_header_id IN NUMBER
269 , p_supply_source_line_id IN NUMBER
270 , p_supply_source_line_detail IN NUMBER /*** {{ R12 Enhanced reservations code changes }}***/
271 ) IS
272
273 l_valid_supply VARCHAR2(1);
274 /*** {{ R12 Enhanced reservations code changes }}***/
275 l_dropship_count NUMBER := 0;
276 l_debug NUMBER;
277 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
278 l_msg_count NUMBER;
279 l_msg_data VARCHAR2(1000);
280 l_valid_status VARCHAR2(1);
281 /*** End R12 }} ***/
282 /*** {{ R12 Enhanced reservations code changes
283 comment out the cursor, since we already validate the
284 document in the reservation private API before come to
285 this validation.
286 CURSOR c_po_supply IS
287 SELECT 'Y'
288 FROM po_distributions_all
289 WHERE po_distribution_id = p_supply_source_line_id
290 AND po_header_id = p_supply_source_header_id;
291 *** End R12 }} ***/
292 BEGIN
293
294 /*** {{ R12 Enhanced reservations code changes
295 comment out the cursor, since we already validate the
296 document in the reservation private API before come to
297 this validation.
298 OPEN c_po_supply;
299 FETCH c_po_supply INTO l_valid_supply;
300 IF c_po_supply%NOTFOUND OR
301 l_valid_supply IS NULL OR
302 l_valid_supply <> 'Y' THEN
303
304 --error message
305 fnd_message.set_name('INV', 'INV_RSV_INVALID_SUPPLY_PO');
306 fnd_msg_pub.add;
307 RAISE fnd_api.g_exc_error;
308 END IF;
309 *** End R12 }} ***/
310
311 /*** {{ R12 Enhanced reservations code changes ***/
312 IF (g_debug IS NULL) THEN
313 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
314 END IF;
315
316 l_debug := g_debug;
317
318 IF (l_debug = 1) THEN
319 debug_print('In validate_supply_source_po: supply_source_type_id = ' || p_supply_source_type_id);
320 END IF;
321
322 IF (p_supply_source_type_id = INV_RESERVATION_GLOBAL.G_SOURCE_TYPE_PO) THEN
323
324 RCV_AVAILABILITY.validate_supply_demand
325 (
326 x_return_status => l_return_status
327 , x_msg_count => l_msg_count
328 , x_msg_data => l_msg_data
329 , x_valid_status => l_valid_status
330 , p_organization_id => p_organization_id
331 , p_item_id => p_inventory_item_id
332 , p_supply_demand_code => 1
333 , p_supply_demand_type_id => p_supply_source_type_id
334 , p_supply_demand_header_id => p_supply_source_header_id
335 , p_supply_demand_line_id => p_supply_source_line_id
336 , p_supply_demand_line_detail => p_supply_source_line_detail
337 , p_demand_ship_date => p_demand_ship_date
338 , p_expected_receipt_date => p_supply_receipt_date
339 , p_api_version_number => 1.0
340 , p_init_msg_lst => fnd_api.g_false
341 );
342
343 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
344 RAISE fnd_api.g_exc_error;
345 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
346 RAISE fnd_api.g_exc_unexpected_error;
347 END IF;
348
349 IF (l_debug = 1) THEN
350 debug_print('validate supply demand returns valid status: ' || l_valid_status);
351 END IF;
352
353 IF (l_valid_status = 'N') THEN
354 fnd_message.set_name('INV', 'INV_RSV_INVALID_SUPPLY_PO');
355 fnd_msg_pub.ADD;
356 RAISE fnd_api.g_exc_error;
357 END IF;
358
359 select count(1)
360 into l_dropship_count
361 from oe_drop_ship_sources
362 where po_header_id = p_supply_source_header_id
363 and line_location_id = p_supply_source_line_id;
364
365 IF (l_debug = 1) THEN
366 debug_print('l_dropship_count = ' || l_dropship_count);
367 END IF;
368
369 IF (l_dropship_count >= 1) THEN
370 fnd_message.set_name('INV', 'INV_RSV_DS_SO_SUP');
371 fnd_message.set_name('SOURCE', 'PO');
372 fnd_msg_pub.add;
373 RAISE fnd_api.g_exc_error;
374 END IF;
375 ELSE
376 IF (l_debug = 1) THEN
377 debug_print('The transation source type is not PO');
378 END IF;
379 RAISE fnd_api.g_exc_error;
380
381 END IF;
382 /*** End R12 }} ***/
383
384 x_return_status := l_return_status;
385
386 EXCEPTION
387 WHEN fnd_api.g_exc_error THEN
388 x_return_status := fnd_api.g_ret_sts_error;
389 --
390 WHEN fnd_api.g_exc_unexpected_error THEN
391 x_return_status := fnd_api.g_ret_sts_unexp_error ;
392 --
393 WHEN OTHERS THEN
394 x_return_status := fnd_api.g_ret_sts_unexp_error ;
395 --
396 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
397 THEN
398 fnd_msg_pub.add_exc_msg
399 ( g_pkg_name
400 , 'Validate_Supply_Source_PO'
401 );
402 END IF;
403 --
404 END validate_supply_source_po;
405
406
407 --Procedure
408 -- validate_supply_source_req
409 -- Description
410 -- Validation for supply source of Requisition
411 -- Currently, only validate for existence of a requisition line id
412 -- with the specified header id. In future, we may also want to
413 -- to validate quantity.
414 -- Added for bug 1947824
415 PROCEDURE validate_supply_source_req
416 (
417 x_return_status OUT NOCOPY VARCHAR2
418 , p_organization_id IN NUMBER
419 , p_inventory_item_id IN NUMBER
420 , p_demand_ship_date IN DATE
421 , p_supply_receipt_date IN DATE
422 , p_supply_source_type_id IN NUMBER /*** {{ R12 Enhanced reservations code changes }}***/
423 , p_supply_source_header_id IN NUMBER
424 , p_supply_source_line_id IN NUMBER
425 , p_supply_source_line_detail IN NUMBER /*** {{ R12 Enhanced reservations code changes }}***/
426 ) IS
427
428 l_valid_supply VARCHAR2(1);
429 /*** {{ R12 Enhanced reservations code changes }}***/
430 l_dropship_count NUMBER := 0;
431 l_debug NUMBER;
432 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
433 l_msg_count NUMBER;
434 l_msg_data VARCHAR2(1000);
435 l_valid_status VARCHAR2(1);
436 /*** End R12 }} ***/
437
438 CURSOR c_req_supply IS
439 SELECT 'Y'
440 FROM po_requisition_lines_all
441 WHERE requisition_line_id = p_supply_source_line_id
442 AND requisition_header_id = p_supply_source_header_id;
443
444 BEGIN
445
446 /*** {{ R12 Enhanced reservations code changes ***/
447 IF (g_debug IS NULL) THEN
448 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
449 END IF;
450
451 l_debug := g_debug;
452
453 IF (l_debug = 1) THEN
454 debug_print('In validate_supply_source_req, supply_source_type_id = ' || p_supply_source_type_id);
455 END IF;
456
457 IF (p_supply_source_type_id = INV_RESERVATION_GLOBAL.G_SOURCE_TYPE_REQ) THEN
458
459 -- validate document
460 OPEN c_req_supply;
461 FETCH c_req_supply INTO l_valid_supply;
462 IF c_req_supply%NOTFOUND OR
463 l_valid_supply IS NULL OR
464 l_valid_supply <> 'Y' THEN
465
466 --error message
467 fnd_message.set_name('INV', 'INV_RSV_INVALID_SUPPLY_REQ');
468 fnd_msg_pub.add;
469 RAISE fnd_api.g_exc_error;
470 END IF;
471
472 select count(1)
473 into l_dropship_count
474 from oe_drop_ship_sources
475 where requisition_header_id = p_supply_source_header_id
476 and requisition_line_id = p_supply_source_line_id;
477
478 IF (l_debug = 1) THEN
479 debug_print('l_dropship_count = ' || l_dropship_count);
480 END IF;
481
482 IF (l_dropship_count >= 1) THEN
483 fnd_message.set_name('INV', 'INV_RSV_DS_SO_SUP');
484 fnd_message.set_token('SOURCE', 'requisition');
485 fnd_msg_pub.add;
486 RAISE fnd_api.g_exc_error;
487 END IF;
488 ELSE
489 IF (l_debug = 1) THEN
490 debug_print('The transation source type is not requisition');
491 END IF;
492 RAISE fnd_api.g_exc_error;
493
494 END IF;
495 /*** End R12 }} ***/
496
497 x_return_status := l_return_status;
498
499 EXCEPTION
500 WHEN fnd_api.g_exc_error THEN
501 x_return_status := fnd_api.g_ret_sts_error;
502 --
503 WHEN fnd_api.g_exc_unexpected_error THEN
504 x_return_status := fnd_api.g_ret_sts_unexp_error ;
505 --
506 WHEN OTHERS THEN
507 x_return_status := fnd_api.g_ret_sts_unexp_error ;
508 --
509 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
510 THEN
511 fnd_msg_pub.add_exc_msg
512 ( g_pkg_name
513 , 'Validate_Supply_Source_REQ'
514 );
515 END IF;
516 --
517 END validate_supply_source_req;
518
519 /*** {{ R12 Enhanced reservations code changes ***/
520 -- {{Procedure
521 -- validate_supply_source_intreq
522 -- Description
523 -- Validation for supply source of internal requisition
524 -- if the document is invalid, then return error. }}
525 PROCEDURE validate_supply_source_intreq
526 (
527 x_return_status OUT NOCOPY VARCHAR2
528 , p_organization_id IN NUMBER
529 , p_inventory_item_id IN NUMBER
530 , p_demand_ship_date IN DATE
531 , p_supply_receipt_date IN DATE
532 , p_supply_source_type_id IN NUMBER
533 , p_supply_source_header_id IN NUMBER
534 , p_supply_source_line_id IN NUMBER
535 , p_supply_source_line_detail IN NUMBER
536 ) IS
537 l_debug NUMBER;
538 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
539 l_msg_count NUMBER;
540 l_msg_data VARCHAR2(1000);
541 l_valid_status VARCHAR2(1);
542
543 BEGIN
544
545 IF (g_debug IS NULL) THEN
546 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
547 END IF;
548
549 l_debug := g_debug;
550
551 IF (l_debug = 1) THEN
552 debug_print('In validate_supply_source_intreq, supply_source_type_id = ' || p_supply_source_type_id);
553 END IF;
554
555 IF (p_supply_source_type_id = INV_RESERVATION_GLOBAL.G_SOURCE_TYPE_INTERNAL_REQ) THEN
556 -- validate document
557 RCV_AVAILABILITY.validate_supply_demand
558 (
559 x_return_status => l_return_status
560 , x_msg_count => l_msg_count
561 , x_msg_data => l_msg_data
562 , x_valid_status => l_valid_status
563 , p_organization_id => p_organization_id
564 , p_item_id => p_inventory_item_id
565 , p_supply_demand_code => 1
566 , p_supply_demand_type_id => p_supply_source_type_id
567 , p_supply_demand_header_id => p_supply_source_header_id
568 , p_supply_demand_line_id => p_supply_source_line_id
569 , p_supply_demand_line_detail => p_supply_source_line_detail
570 , p_demand_ship_date => p_demand_ship_date
571 , p_expected_receipt_date => p_supply_receipt_date
572 , p_api_version_number => 1.0
573 , p_init_msg_lst => fnd_api.g_false
574 );
575
576 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
577 RAISE fnd_api.g_exc_error;
578 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
579 RAISE fnd_api.g_exc_unexpected_error;
580 END IF;
581
582 IF (l_debug = 1) THEN
583 debug_print('validate supply demand returns valid status: ' || l_valid_status);
584 END IF;
585
586 IF (l_valid_status = 'N') THEN
587 fnd_message.set_name('INV', 'INV_RSV_INVALID_SUPPLY_INTREQ');
588 fnd_msg_pub.ADD;
589 RAISE fnd_api.g_exc_error;
590 END IF;
591 ELSE
592 IF (l_debug = 1) THEN
593 debug_print('The transation source type is not internal requisition');
594 END IF;
595 RAISE fnd_api.g_exc_error;
596
597 END IF;
598 /*** End R12 }} ***/
599
600 x_return_status := l_return_status;
601
602 EXCEPTION
603 WHEN fnd_api.g_exc_error THEN
604 x_return_status := fnd_api.g_ret_sts_error;
605 --
606 WHEN fnd_api.g_exc_unexpected_error THEN
607 x_return_status := fnd_api.g_ret_sts_unexp_error ;
608 --
609 WHEN OTHERS THEN
610 x_return_status := fnd_api.g_ret_sts_unexp_error ;
611 --
612 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
613 THEN
614 fnd_msg_pub.add_exc_msg
615 ( g_pkg_name
616 , 'Validate_Supply_Source_INTREQ'
617 );
618 END IF;
619 --
620 END validate_supply_source_intreq;
621
622 /*** {{ R12 Enhanced reservations code changes ***/
623 -- {{Procedure
624 -- validate_supply_source_asn
625 -- Description
626 -- Validation for supply source of ASN
627 -- if the organization is not WMS enabled org, then return error. }}
628 PROCEDURE validate_supply_source_asn
629 (
630 x_return_status OUT NOCOPY VARCHAR2
631 , p_organization_id IN NUMBER
632 , p_inventory_item_id IN NUMBER
633 , p_demand_ship_date IN DATE
634 , p_supply_receipt_date IN DATE
635 , p_supply_source_type_id IN NUMBER
636 , p_supply_source_header_id IN NUMBER
637 , p_supply_source_line_id IN NUMBER
638 , p_supply_source_line_detail IN NUMBER
639 ) IS
640
641 l_wms_enabled VARCHAR2(1) := 'N';
642 l_debug NUMBER;
643 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
644 l_msg_count NUMBER;
645 l_msg_data VARCHAR2(1000);
646 l_valid_status VARCHAR2(1);
647 BEGIN
648
649 IF (g_debug IS NULL) THEN
650 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
651 END IF;
652
653 l_debug := g_debug;
654
655 IF (l_debug = 1) THEN
656 debug_print('In validate_supply_source_asn, supply_source_type_id = ' || p_supply_source_type_id);
657 END IF;
658
659 IF (p_supply_source_type_id = INV_RESERVATION_GLOBAL.G_SOURCE_TYPE_ASN) THEN
660
661 -- validate the document
662 RCV_AVAILABILITY.validate_supply_demand
663 (
664 x_return_status => l_return_status
665 , x_msg_count => l_msg_count
666 , x_msg_data => l_msg_data
667 , x_valid_status => l_valid_status
668 , p_organization_id => p_organization_id
669 , p_item_id => p_inventory_item_id
670 , p_supply_demand_code => 1
671 , p_supply_demand_type_id => p_supply_source_type_id
672 , p_supply_demand_header_id => p_supply_source_header_id
673 , p_supply_demand_line_id => p_supply_source_line_id
674 , p_supply_demand_line_detail => p_supply_source_line_detail
675 , p_demand_ship_date => p_demand_ship_date
676 , p_expected_receipt_date => p_supply_receipt_date
677 , p_api_version_number => 1.0
678 , p_init_msg_lst => fnd_api.g_false
679 );
680
681 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
682 RAISE fnd_api.g_exc_error;
683 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
684 RAISE fnd_api.g_exc_unexpected_error;
685 END IF;
686
687 IF (l_debug = 1) THEN
688 debug_print('validate supply demand returns valid status: ' || l_valid_status);
689 END IF;
690
691 IF (l_valid_status = 'N') THEN
692 fnd_message.set_name('INV', 'INV_RSV_INVALID_SUPPLY_ASN');
693 fnd_msg_pub.ADD;
694 RAISE fnd_api.g_exc_error;
695 END IF;
696
697 SELECT wms_enabled_flag
698 INTO l_wms_enabled
699 FROM mtl_parameters
700 WHERE organization_id = p_organization_id;
701
702 IF (l_debug = 1) THEN
703 debug_print('l_wms_enabled = ' || l_wms_enabled);
704 END IF;
705
706 IF (l_wms_enabled = 'N') THEN
707 fnd_message.set_name('INV', 'INV_RSV_NON_WMS');
708 fnd_message.set_name('SOURCE', 'ASN');
709 fnd_msg_pub.add;
710 RAISE fnd_api.g_exc_error;
711 END IF;
712 ELSE
713 IF (l_debug = 1) THEN
714 debug_print('The transation source type is not ASN');
715 END IF;
716 RAISE fnd_api.g_exc_error;
717
718 END IF;
719 x_return_status := l_return_status;
720
721 EXCEPTION
722 WHEN fnd_api.g_exc_error THEN
723 x_return_status := fnd_api.g_ret_sts_error;
724 --
725 WHEN fnd_api.g_exc_unexpected_error THEN
726 x_return_status := fnd_api.g_ret_sts_unexp_error ;
727 --
728 WHEN OTHERS THEN
729 x_return_status := fnd_api.g_ret_sts_unexp_error ;
730 --
731 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
732 THEN
733 fnd_msg_pub.add_exc_msg
734 ( g_pkg_name
735 , 'Validate_Supply_Source_ASN'
736 );
737 END IF;
738
739 END validate_supply_source_asn;
740
741 -- {{Procedure
742 -- validate_supply_source_intransit
743 -- Description
744 -- Validation for supply source of Intransit shipment
745 -- if the organization is not WMS enabled org, then return error. }}
746 PROCEDURE validate_supply_source_intran
747 (
748 x_return_status OUT NOCOPY VARCHAR2
749 , p_organization_id IN NUMBER
750 , p_inventory_item_id IN NUMBER
751 , p_demand_ship_date IN DATE
752 , p_supply_receipt_date IN DATE
753 , p_supply_source_type_id IN NUMBER
754 , p_supply_source_header_id IN NUMBER
755 , p_supply_source_line_id IN NUMBER
756 , p_supply_source_line_detail IN NUMBER
757 ) IS
758
759 l_wms_enabled VARCHAR2(1) := 'N';
760 l_replenish_to_order VARCHAR2(1) := 'N';
761 l_debug NUMBER;
762 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
763 l_msg_count NUMBER;
764 l_msg_data VARCHAR2(1000);
765 l_valid_status VARCHAR2(1);
766 BEGIN
767
768 IF (g_debug IS NULL) THEN
769 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
770 END IF;
771
772 l_debug := g_debug;
773
774 IF (l_debug = 1) THEN
775 debug_print('In validate_supply_source_intran, supply_source_type_id = ' || p_supply_source_type_id);
776 END IF;
777
778 IF (p_supply_source_type_id = INV_RESERVATION_GLOBAL.G_SOURCE_TYPE_INTRANSIT) THEN
779
780 -- validate document
781 RCV_AVAILABILITY.validate_supply_demand
782 (
783 x_return_status => l_return_status
784 , x_msg_count => l_msg_count
785 , x_msg_data => l_msg_data
786 , x_valid_status => l_valid_status
787 , p_organization_id => p_organization_id
788 , p_item_id => p_inventory_item_id
789 , p_supply_demand_code => 1
790 , p_supply_demand_type_id => p_supply_source_type_id
791 , p_supply_demand_header_id => p_supply_source_header_id
792 , p_supply_demand_line_id => p_supply_source_line_id
793 , p_supply_demand_line_detail => p_supply_source_line_detail
794 , p_demand_ship_date => p_demand_ship_date
795 , p_expected_receipt_date => p_supply_receipt_date
796 , p_api_version_number => 1.0
797 , p_init_msg_lst => fnd_api.g_false
798 );
799
800 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
801 RAISE fnd_api.g_exc_error;
802 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
803 RAISE fnd_api.g_exc_unexpected_error;
804 END IF;
805
806 IF (l_debug = 1) THEN
807 debug_print('validate supply demand returns valid status: ' || l_valid_status);
808 END IF;
809
810 IF (l_valid_status = 'N') THEN
811 fnd_message.set_name('INV', 'INV_RSV_INVALID_SUPPLY_INTRAN');
812 fnd_msg_pub.ADD;
813 RAISE fnd_api.g_exc_error;
814 END IF;
815
816 SELECT wms_enabled_flag
817 INTO l_wms_enabled
818 FROM mtl_parameters
819 WHERE organization_id = p_organization_id;
820
821 IF (l_debug = 1) THEN
822 debug_print('l_wms_enabled = ' || l_wms_enabled);
823 END IF;
824
825 IF (l_wms_enabled = 'N') THEN
826 fnd_message.set_name('INV', 'INV_RSV_NON_WMS');
827 fnd_message.set_name('SOURCE', 'intransit shipment');
828 fnd_msg_pub.add;
829 RAISE fnd_api.g_exc_error;
830 END IF;
831
832 SELECT replenish_to_order_flag
833 INTO l_replenish_to_order
834 FROM mtl_system_items
835 WHERE organization_id = p_organization_id
836 AND inventory_item_id = p_inventory_item_id;
837
838 IF (l_debug = 1) THEN
839 debug_print('l_replenish_to_order_flag = ' || l_replenish_to_order);
840 END IF;
841
842 IF (l_replenish_to_order = 'Y') THEN
843 fnd_message.set_name('INV', 'INV_RSV_INT_REPLEN');
844 fnd_message.set_token('SOURCE', 'intransit shipment');
845 fnd_msg_pub.add;
846 RAISE fnd_api.g_exc_error;
847 END IF;
848 ELSE
849 IF (l_debug = 1) THEN
850 debug_print('The transation source type is not intransit shipment');
851 END IF;
852 RAISE fnd_api.g_exc_error;
853
854 END IF;
855 x_return_status := l_return_status;
856
857 EXCEPTION
858 WHEN fnd_api.g_exc_error THEN
859 x_return_status := fnd_api.g_ret_sts_error;
860 --
861 WHEN fnd_api.g_exc_unexpected_error THEN
862 x_return_status := fnd_api.g_ret_sts_unexp_error ;
863 --
864 WHEN OTHERS THEN
865 x_return_status := fnd_api.g_ret_sts_unexp_error ;
866 --
867 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
868 THEN
869 fnd_msg_pub.add_exc_msg
870 ( g_pkg_name
871 , 'Validate_Supply_Source_Intran'
872 );
873 END IF;
874
875 END validate_supply_source_intran;
876
877 -- {{Procedure
878 -- validate_supply_source_rcv
879 -- Description
880 -- Validation for supply source of RCV
881 -- if the organization is not WMS enabled org, then return error. }}
882 PROCEDURE validate_supply_source_rcv
883 (
884 x_return_status OUT NOCOPY VARCHAR2
885 , p_organization_id IN NUMBER
886 , p_item_id IN NUMBER
887 , p_demand_ship_date IN DATE
888 , p_supply_receipt_date IN DATE
889 , p_supply_source_type_id IN NUMBER
890 , p_supply_source_header_id IN NUMBER
891 , p_supply_source_line_id IN NUMBER
892 , p_supply_source_line_detail IN NUMBER
893 ) IS
894
895 l_wms_enabled VARCHAR2(1) := 'N';
896 l_debug NUMBER;
897 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
898 l_msg_count NUMBER;
899 l_msg_data VARCHAR2(1000);
900 l_valid_status VARCHAR2(1);
901 BEGIN
902
903 IF (g_debug IS NULL) THEN
904 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
905 END IF;
906
907 l_debug := g_debug;
908
909 IF (l_debug = 1) THEN
910 debug_print('In validate_supply_source_rcv, supply_source_type_id = ' || p_supply_source_type_id);
911 END IF;
912
913 IF (p_supply_source_type_id = INV_RESERVATION_GLOBAL.G_SOURCE_TYPE_RCV) THEN
914
915 -- validate the document
916 /*
917 RCV_package.validate_supply_demand
918 (
919 x_return_status => l_return_status
920 , x_msg_count => l_msg_count
921 , x_msg_data => l_msg_data
922 , x_valid_status => l_valid_status
923 , p_organization_id => p_organization_id
924 , p_item_id => p_inventory_item_id
925 , p_supply_demand_code => 1
926 , p_supply_demand_type_id => p_supply_source_type_id
927 , p_supply_demand_header_id => p_supply_source_header_id
928 , p_supply_demand_line_id => p_supply_source_line_id
929 , p_supply_demand_line_detail => p_supply_source_line_detail
930 , p_demand_ship_date => p_demand_ship_date
931 , p_expected_receipt_date => p_supply_receipt_date
932 , p_api_version_number => 1.0
933 , p_init_msg_lst => fnd_api.g_false
934 );
935 */
936
937 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
938 RAISE fnd_api.g_exc_error;
939 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
940 RAISE fnd_api.g_exc_unexpected_error;
941 END IF;
942
943 IF (l_debug = 1) THEN
944 debug_print('validate supply demand returns valid status: ' || l_valid_status);
945 END IF;
946
947 IF (l_valid_status = 'N') THEN
948 fnd_message.set_name('INV', 'INV_RSV_INVALID_SUPPLY_RCV');
949 fnd_msg_pub.ADD;
950 RAISE fnd_api.g_exc_error;
951 END IF;
952
953 SELECT wms_enabled_flag
954 INTO l_wms_enabled
955 FROM mtl_parameters
956 WHERE organization_id = p_organization_id;
957
958 IF (l_debug = 1) THEN
959 debug_print('l_wms_enabled = ' || l_wms_enabled);
960 END IF;
961
962 IF (l_wms_enabled = 'N') THEN
963 fnd_message.set_name('INV', 'INV_RSV_NON_WMS');
964 fnd_message.set_name('SOURCE', 'receiving');
965 fnd_msg_pub.add;
966 RAISE fnd_api.g_exc_error;
967 END IF;
968 ELSE
969 IF (l_debug = 1) THEN
970 debug_print('The transaction source type is not receiving');
971 END IF;
972 RAISE fnd_api.g_exc_error;
973 END IF;
974 x_return_status := l_return_status;
975
976 EXCEPTION
977 WHEN fnd_api.g_exc_error THEN
978 x_return_status := fnd_api.g_ret_sts_error;
979 --
980 WHEN fnd_api.g_exc_unexpected_error THEN
981 x_return_status := fnd_api.g_ret_sts_unexp_error ;
982 --
983 WHEN OTHERS THEN
984 x_return_status := fnd_api.g_ret_sts_unexp_error ;
985 --
986 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
987 THEN
988 fnd_msg_pub.add_exc_msg
989 ( g_pkg_name
990 , 'Validate_Supply_Source_RCV'
991 );
992 END IF;
993
994 END validate_supply_source_rcv;
995
996 -- {{Procedure
997 -- validate_supply_source_wipdisc
998 -- Description
999 -- Validation for supply source of DISCRETE
1000 PROCEDURE validate_supply_source_wipdisc
1001 (
1002 x_return_status OUT NOCOPY VARCHAR2
1003 , p_organization_id IN NUMBER
1004 , p_inventory_item_id IN NUMBER
1005 , p_demand_ship_date IN DATE
1006 , p_supply_receipt_date IN DATE
1007 , p_supply_source_type_id IN NUMBER
1008 , p_supply_source_header_id IN NUMBER
1009 , p_supply_source_line_id IN NUMBER
1010 , p_supply_source_line_detail IN NUMBER
1011 , p_wip_entity_type IN NUMBER
1012 ) IS
1013
1014 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1015 l_msg_count NUMBER;
1016 l_msg_data VARCHAR2(1000);
1017 l_valid_status VARCHAR2(1);
1018 l_debug NUMBER;
1019 BEGIN
1020
1021 IF (g_debug IS NULL) THEN
1022 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1023 END IF;
1024
1025 l_debug := g_debug;
1026
1027 IF (l_debug = 1) THEN
1028 debug_print('In validate_supply_source_wipdisc, supply_source_type_id = ' || p_supply_source_type_id);
1029 END IF;
1030
1031 IF (p_supply_source_type_id = INV_RESERVATION_GLOBAL.G_SOURCE_TYPE_WIP) THEN
1032 IF (p_wip_entity_type = INV_RESERVATION_GLOBAL.G_WIP_SOURCE_TYPE_DISCRETE) THEN
1033
1034
1035 -- validate document
1036 WIP_RESERVATIONS_GRP.validate_supply_demand
1037 (
1038 x_return_status => l_return_status
1039 , x_msg_count => l_msg_count
1040 , x_msg_data => l_msg_data
1041 , x_valid_status => l_valid_status
1042 , p_organization_id => p_organization_id
1043 , p_item_id => p_inventory_item_id
1044 , p_supply_demand_code => 1
1045 , p_supply_demand_type_id => p_supply_source_type_id
1046 , p_supply_demand_header_id => p_supply_source_header_id
1047 , p_supply_demand_line_id => p_supply_source_line_id
1048 , p_supply_demand_line_detail => p_supply_source_line_detail
1049 , p_demand_ship_date => p_demand_ship_date
1050 , p_expected_receipt_date => p_supply_receipt_date
1051 , p_api_version_number => 1.0
1052 , p_init_msg_lst => fnd_api.g_false
1053 );
1054
1055 IF (l_debug = 1) THEN
1056 debug_print('Return status after calling validate supply demand wipdisc: ' || l_valid_status || ' : ' || l_return_status);
1057 END IF;
1058
1059 IF (l_valid_status = 'N') OR (l_return_status = fnd_api.g_ret_sts_error) THEN
1060 fnd_message.set_name('INV', 'INV_RSV_INVALID_SUPPLY_DISC');
1061 fnd_msg_pub.ADD;
1062 RAISE fnd_api.g_exc_error;
1063 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
1064 RAISE fnd_api.g_exc_unexpected_error;
1065 END IF;
1066 ELSE
1067 IF (l_debug = 1) THEN
1068 debug_print('The wip entity type is not Discrete');
1069 END IF;
1070 RAISE fnd_api.g_exc_error;
1071 END IF;
1072 ELSE
1073 IF (l_debug = 1) THEN
1074 debug_print('The transation source type is not WIP discrete');
1075 END IF;
1076 RAISE fnd_api.g_exc_error;
1077
1078 END IF;
1079
1080 x_return_status := l_return_status;
1081 EXCEPTION
1082 WHEN fnd_api.g_exc_error THEN
1083 x_return_status := fnd_api.g_ret_sts_error;
1084 --
1085 WHEN fnd_api.g_exc_unexpected_error THEN
1086 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1087 --
1088 WHEN OTHERS THEN
1089 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1090 --
1091 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1092 THEN
1093 fnd_msg_pub.add_exc_msg
1094 ( g_pkg_name
1095 , 'Validate_Supply_Source_WIPDISC'
1096 );
1097 END IF;
1098
1099 END validate_supply_source_wipdisc;
1100
1101 -- {{Procedure
1102 -- validate_supply_source_osfm
1103 -- Description
1104 -- Validation for supply source of OSFM
1105 -- if the item is replenish to order, then return error. }}
1106 PROCEDURE validate_supply_source_osfm
1107 (
1108 x_return_status OUT NOCOPY VARCHAR2
1109 , p_organization_id IN NUMBER
1110 , p_inventory_item_id IN NUMBER
1111 , p_demand_ship_date IN DATE
1112 , p_supply_receipt_date IN DATE
1113 , p_supply_source_type_id IN NUMBER
1114 , p_supply_source_header_id IN NUMBER
1115 , p_supply_source_line_id IN NUMBER
1116 , p_supply_source_line_detail IN NUMBER
1117 , p_wip_entity_type IN NUMBER
1118 ) IS
1119
1120 l_replenish_to_order VARCHAR2(1) := 'N';
1121 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1122 l_msg_count NUMBER;
1123 l_msg_data VARCHAR2(1000);
1124 l_valid_status VARCHAR2(1);
1125 l_debug NUMBER;
1126 BEGIN
1127
1128 IF (g_debug IS NULL) THEN
1129 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1130 END IF;
1131
1132 l_debug := g_debug;
1133
1134 IF (l_debug = 1) THEN
1135 debug_print('In validate_supply_source_osfm, supply_source_type_id = ' || p_supply_source_type_id);
1136 END IF;
1137
1138 IF (p_supply_source_type_id = INV_RESERVATION_GLOBAL.G_SOURCE_TYPE_WIP) THEN
1139 IF (p_wip_entity_type = INV_RESERVATION_GLOBAL.G_WIP_SOURCE_TYPE_OSFM) THEN
1140
1141 -- validate document
1142 WSM_RESERVATIONS_GRP.validate_supply_demand
1143 (
1144 x_return_status => l_return_status
1145 , x_msg_count => l_msg_count
1146 , x_msg_data => l_msg_data
1147 , x_valid_status => l_valid_status
1148 , p_organization_id => p_organization_id
1149 , p_item_id => p_inventory_item_id
1150 , p_supply_demand_code => 1
1151 , p_supply_demand_type_id => p_supply_source_type_id
1152 , p_supply_demand_header_id => p_supply_source_header_id
1153 , p_supply_demand_line_id => p_supply_source_line_id
1154 , p_supply_demand_line_detail => p_supply_source_line_detail
1155 , p_demand_ship_date => p_demand_ship_date
1156 , p_expected_receipt_date => p_supply_receipt_date
1157 , p_api_version_number => 1.0
1158 , p_init_msg_lst => fnd_api.g_false
1159 );
1160
1161 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
1162 RAISE fnd_api.g_exc_error;
1163 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
1164 RAISE fnd_api.g_exc_unexpected_error;
1165 END IF;
1166
1167 IF (l_debug = 1) THEN
1168 debug_print('validate supply demand returns valid status: ' || l_valid_status);
1169 END IF;
1170
1171 IF (l_valid_status = 'N') THEN
1172 fnd_message.set_name('INV', 'INV_RSV_INVALID_SUPPLY_OSFM');
1173 fnd_msg_pub.ADD;
1174 RAISE fnd_api.g_exc_error;
1175 END IF;
1176
1177 SELECT replenish_to_order_flag
1178 INTO l_replenish_to_order
1179 FROM mtl_system_items
1180 WHERE organization_id = p_organization_id
1181 AND inventory_item_id = p_inventory_item_id;
1182
1183 IF (l_debug = 1) THEN
1184 debug_print('l_replenish_to_order = ' || l_replenish_to_order);
1185 END IF;
1186
1187 IF (l_replenish_to_order = 'Y') THEN
1188 fnd_message.set_name('INV', 'INV_RSV_REPLEN');
1189 fnd_message.set_token('SOURCE', 'OSFM');
1190 fnd_msg_pub.add;
1191 RAISE fnd_api.g_exc_error;
1192 END IF;
1193 ELSE
1194 IF (l_debug = 1) THEN
1195 debug_print('The wip entity type is not OSFM');
1196 END IF;
1197 RAISE fnd_api.g_exc_error;
1198 END IF;
1199 ELSE
1200 IF (l_debug = 1) THEN
1201 debug_print('The transation source type is not WIP');
1202 END IF;
1203 RAISE fnd_api.g_exc_error;
1204
1205 END IF;
1206
1207 x_return_status := l_return_status;
1208 EXCEPTION
1209 WHEN fnd_api.g_exc_error THEN
1210 x_return_status := fnd_api.g_ret_sts_error;
1211 --
1212 WHEN fnd_api.g_exc_unexpected_error THEN
1213 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1214 --
1215 WHEN OTHERS THEN
1216 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1217 --
1218 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1219 THEN
1220 fnd_msg_pub.add_exc_msg
1221 ( g_pkg_name
1222 , 'Validate_Supply_Source_OSFM'
1223 );
1224 END IF;
1225
1226 END validate_supply_source_osfm;
1227
1228 -- {{Procedure
1229 -- validate_supply_source_fpo
1230 -- Description
1231 -- Validation for supply source of FPO
1232 -- if the item is replenish to order, then return error. }}
1233 PROCEDURE validate_supply_source_fpo
1234 (
1235 x_return_status OUT NOCOPY VARCHAR2
1236 , p_organization_id IN NUMBER
1237 , p_inventory_item_id IN NUMBER
1238 , p_demand_ship_date IN DATE
1239 , p_supply_receipt_date IN DATE
1240 , p_supply_source_type_id IN NUMBER
1241 , p_supply_source_header_id IN NUMBER
1242 , p_supply_source_line_id IN NUMBER
1243 , p_supply_source_line_detail IN NUMBER
1244 , p_wip_entity_type IN NUMBER
1245 ) IS
1246
1247 l_replenish_to_order VARCHAR2(1) := 'N';
1248 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1249 l_msg_count NUMBER;
1250 l_msg_data VARCHAR2(1000);
1251 l_valid_status VARCHAR2(1);
1252 l_debug NUMBER;
1253 BEGIN
1254
1255 IF (g_debug IS NULL) THEN
1256 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1257 END IF;
1258
1259 l_debug := g_debug;
1260
1261 IF (l_debug = 1) THEN
1262 debug_print('In validate_supply_source_fpo, supply_source_type_id = ' || p_supply_source_type_id);
1263 END IF;
1264
1265 IF (p_supply_source_type_id = INV_RESERVATION_GLOBAL.G_SOURCE_TYPE_WIP) THEN
1266 IF (p_wip_entity_type = INV_RESERVATION_GLOBAL.G_WIP_SOURCE_TYPE_FPO) THEN
1267
1268 -- validate document
1269 /*
1270 FPO_package.validate_supply_demand
1271 (
1272 x_return_status => l_return_status
1273 , x_msg_count => l_msg_count
1274 , x_msg_data => l_msg_data
1275 , x_valid_status => l_valid_status
1276 , p_organization_id => p_organization_id
1277 , p_item_id => p_inventory_item_id
1278 , p_supply_demand_code => 1
1279 , p_supply_demand_type_id => p_supply_source_type_id
1280 , p_supply_demand_header_id => p_supply_source_header_id
1281 , p_supply_demand_line_id => p_supply_source_line_id
1282 , p_supply_demand_line_detail => p_supply_source_line_detail
1283 , p_demand_ship_date => p_demand_ship_date
1284 , p_expected_receipt_date => p_supply_receipt_date
1285 , p_api_version_number => 1.0
1286 , p_init_msg_lst => fnd_api.g_false
1287 );
1288 */
1289
1290 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
1291 RAISE fnd_api.g_exc_error;
1292 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
1293 RAISE fnd_api.g_exc_unexpected_error;
1294 END IF;
1295
1296 IF (l_debug = 1) THEN
1297 debug_print('validate supply demand returns valid status: ' || l_valid_status);
1298 END IF;
1299
1300 IF (l_valid_status = 'N') THEN
1301 fnd_message.set_name('INV', 'INV_RSV_INVALID_SUPPLY_FPO');
1302 fnd_msg_pub.ADD;
1303 RAISE fnd_api.g_exc_error;
1304 END IF;
1305
1306 SELECT replenish_to_order_flag
1307 INTO l_replenish_to_order
1308 FROM mtl_system_items
1309 WHERE organization_id = p_organization_id
1310 AND inventory_item_id = p_inventory_item_id;
1311
1312 IF (l_debug = 1) THEN
1313 debug_print('l_replenish_to_order = ' || l_replenish_to_order);
1314 END IF;
1315
1316 IF (l_replenish_to_order = 'Y') THEN
1317 fnd_message.set_name('INV', 'INV_RSV_REPLEN');
1318 fnd_message.set_token('SOURCE', 'FPO');
1319 fnd_msg_pub.add;
1320 RAISE fnd_api.g_exc_error;
1321 END IF;
1322 ELSE
1323 IF (l_debug = 1) THEN
1324 debug_print('The wip entity type is not FPO');
1325 END IF;
1326 RAISE fnd_api.g_exc_error;
1327 END IF;
1328 ELSE
1329 IF (l_debug = 1) THEN
1330 debug_print('The transation source type is not WIP');
1331 END IF;
1332 RAISE fnd_api.g_exc_error;
1333
1334 END IF;
1335
1336 x_return_status := l_return_status;
1337 EXCEPTION
1338 WHEN fnd_api.g_exc_error THEN
1339 x_return_status := fnd_api.g_ret_sts_error;
1340 --
1341 WHEN fnd_api.g_exc_unexpected_error THEN
1342 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1343 --
1344 WHEN OTHERS THEN
1345 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1346 --
1347 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1348 THEN
1349 fnd_msg_pub.add_exc_msg
1350 ( g_pkg_name
1351 , 'Validate_Supply_Source_FPO'
1352 );
1353 END IF;
1354
1355 END validate_supply_source_fpo;
1356
1357 -- {{Procedure
1358 -- validate_supply_source_batch
1359 -- Description
1360 -- Validation for supply source of batch
1361 -- if the item is replenish to order, then return error. }}
1362 PROCEDURE validate_supply_source_batch
1363 (
1364 x_return_status OUT NOCOPY VARCHAR2
1365 , p_organization_id IN NUMBER
1366 , p_inventory_item_id IN NUMBER
1367 , p_demand_ship_date IN DATE
1368 , p_supply_receipt_date IN DATE
1369 , p_supply_source_type_id IN NUMBER
1370 , p_supply_source_header_id IN NUMBER
1371 , p_supply_source_line_id IN NUMBER
1372 , p_supply_source_line_detail IN NUMBER
1373 , p_wip_entity_type IN NUMBER
1374 ) IS
1375
1376 l_replenish_to_order VARCHAR2(1) := 'N';
1377 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1378 l_msg_count NUMBER;
1379 l_msg_data VARCHAR2(1000);
1380 l_valid_status VARCHAR2(1);
1381 l_debug NUMBER;
1382 BEGIN
1383
1384 IF (g_debug IS NULL) THEN
1385 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1386 END IF;
1387
1388 l_debug := g_debug;
1389
1390 IF (l_debug = 1) THEN
1391 debug_print('In validate_supply_source_batch, supply_source_type_id = ' || p_supply_source_type_id);
1392 END IF;
1393
1394 IF (p_supply_source_type_id = INV_RESERVATION_GLOBAL.G_SOURCE_TYPE_WIP) THEN
1395 IF (p_wip_entity_type = INV_RESERVATION_GLOBAL.G_WIP_SOURCE_TYPE_BATCH) THEN
1396
1397 -- validate document
1398 /*
1399 BATCH_package.validate_supply_demand
1400 (
1401 x_return_status => l_return_status
1402 , x_msg_count => l_msg_count
1403 , x_msg_data => l_msg_data
1404 , x_valid_status => l_valid_status
1405 , p_organization_id => p_organization_id
1406 , p_item_id => p_inventory_item_id
1407 , p_supply_demand_code => 1
1408 , p_supply_demand_type_id => p_supply_source_type_id
1409 , p_supply_demand_header_id => p_supply_source_header_id
1410 , p_supply_demand_line_id => p_supply_source_line_id
1411 , p_supply_demand_line_detail => p_supply_source_line_detail
1412 , p_demand_ship_date => p_demand_ship_date
1413 , p_expected_receipt_date => p_supply_receipt_date
1414 , p_api_version_number => 1.0
1415 , p_init_msg_lst => fnd_api.g_false
1416 );
1417 */
1418
1419 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
1420 RAISE fnd_api.g_exc_error;
1421 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
1422 RAISE fnd_api.g_exc_unexpected_error;
1423 END IF;
1424
1425 IF (l_debug = 1) THEN
1426 debug_print('validate supply demand returns valid status: ' || l_valid_status);
1427 END IF;
1428
1429 IF (l_valid_status = 'N') THEN
1430 fnd_message.set_name('INV', 'INV_RSV_INVALID_SUPPLY_BATCH');
1431 fnd_msg_pub.ADD;
1432 RAISE fnd_api.g_exc_error;
1433 END IF;
1434
1435 SELECT replenish_to_order_flag
1436 INTO l_replenish_to_order
1437 FROM mtl_system_items
1438 WHERE organization_id = p_organization_id
1439 AND inventory_item_id = p_inventory_item_id;
1440
1441 IF (l_debug = 1) THEN
1442 debug_print('l_replenish_to_order = ' || l_replenish_to_order);
1443 END IF;
1444 IF (l_replenish_to_order = 'Y') THEN
1445 fnd_message.set_name('INV', 'INV_RSV_REPLEN');
1446 fnd_message.set_token('SOURCE', 'Batch');
1447 fnd_msg_pub.add;
1448 RAISE fnd_api.g_exc_error;
1449 END IF;
1450 ELSE
1451 IF (l_debug = 1) THEN
1452 debug_print('The wip entity type is not Batch');
1453 END IF;
1454 RAISE fnd_api.g_exc_error;
1455 END IF;
1456 ELSE
1457 IF (l_debug = 1) THEN
1458 debug_print('The transation source type is not WIP');
1459 END IF;
1460 RAISE fnd_api.g_exc_error;
1461
1462 END IF;
1463
1464 x_return_status := l_return_status;
1465 EXCEPTION
1466 WHEN fnd_api.g_exc_error THEN
1467 x_return_status := fnd_api.g_ret_sts_error;
1468 --
1469 WHEN fnd_api.g_exc_unexpected_error THEN
1470 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1471 --
1472 WHEN OTHERS THEN
1473 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1474 --
1475 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1476 THEN
1477 fnd_msg_pub.add_exc_msg
1478 ( g_pkg_name
1479 , 'Validate_Supply_Source_Batch'
1480 );
1481 END IF;
1482
1483 END validate_supply_source_batch;
1484
1485 -- {{Procedure
1486 -- validate_demand_source_so
1487 -- Description
1488 -- Validation for demand source of sales order
1489 -- if it is drop ship sales order line, then return error. }}
1490 PROCEDURE validate_demand_source_so
1491 (
1492 x_return_status OUT NOCOPY VARCHAR2
1493 , p_demand_source_type_id IN NUMBER
1494 , p_demand_source_header_id IN NUMBER
1495 , p_demand_source_line_id IN NUMBER
1496 , p_demand_source_line_detail IN NUMBER
1497 ) IS
1498
1499 l_dropship_count NUMBER := 0;
1500 l_debug NUMBER;
1501 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1502
1503 BEGIN
1504
1505 IF (g_debug IS NULL) THEN
1506 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1507 END IF;
1508
1509 l_debug := g_debug;
1510
1511 IF (l_debug = 1) THEN
1512 debug_print('In validate_demand_source_so: demand_source_type_id = ' || p_demand_source_type_id);
1513 END IF;
1514
1515 IF (p_demand_source_type_id = INV_RESERVATION_GLOBAL.G_SOURCE_TYPE_OE) THEN
1516 select count(1)
1517 into l_dropship_count
1518 from oe_drop_ship_sources
1519 where header_id = p_demand_source_header_id
1520 and line_id = p_demand_source_line_id;
1521
1522 IF (l_debug = 1) THEN
1523 debug_print('l_dropship_count = ' || l_dropship_count);
1524 END IF;
1525
1526 IF (l_dropship_count >= 1) THEN
1527 fnd_message.set_name('INV', 'INV_RSV_DS_SO');
1528 fnd_msg_pub.add;
1529 RAISE fnd_api.g_exc_error;
1530 END IF;
1531
1532 ELSE
1533 IF (l_debug = 1) THEN
1534 debug_print('The transation source type is not sales order');
1535 END IF;
1536 RAISE fnd_api.g_exc_error;
1537
1538 END IF;
1539 IF (l_debug = 1) THEN
1540 debug_print('After drop ship check ' || p_demand_source_type_id);
1541 END IF;
1542 x_return_status := l_return_status;
1543
1544 EXCEPTION
1545 WHEN fnd_api.g_exc_error THEN
1546 x_return_status := fnd_api.g_ret_sts_error;
1547 --
1548 WHEN fnd_api.g_exc_unexpected_error THEN
1549 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1550 --
1551 WHEN OTHERS THEN
1552 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1553 --
1554 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1555 THEN
1556 fnd_msg_pub.add_exc_msg
1557 ( g_pkg_name
1558 , 'Validate_Demand_Source_SO'
1559 );
1560 END IF;
1561 --
1562 END validate_demand_source_so;
1563
1564 -- {{Procedure
1565 -- validate_demand_source_cmro
1566 -- Description
1567 -- Validation for demand source of CMRO
1568 -- return error is the document is invalid. }}
1569 PROCEDURE validate_demand_source_cmro
1570 (
1571 x_return_status OUT NOCOPY VARCHAR2
1572 , p_organization_id IN NUMBER
1573 , p_inventory_item_id IN NUMBER
1574 , p_demand_ship_date IN DATE
1575 , p_supply_receipt_date IN DATE
1576 , p_demand_source_type_id IN NUMBER
1577 , p_demand_source_header_id IN NUMBER
1578 , p_demand_source_line_id IN NUMBER
1579 , p_demand_source_line_detail IN NUMBER
1580 , p_wip_entity_type IN NUMBER
1581 ) IS
1582
1583 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1584 l_msg_count NUMBER;
1585 l_msg_data VARCHAR2(1000);
1586 l_valid_status VARCHAR2(1);
1587 l_debug NUMBER;
1588
1589 BEGIN
1590
1591 IF (g_debug IS NULL) THEN
1592 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1593 END IF;
1594
1595 l_debug := g_debug;
1596
1597 IF (l_debug = 1) THEN
1598 debug_print('In validate_demand_source_cmro: demand_source_type_id = ' || p_demand_source_type_id);
1599 debug_print('demand_source_header_id = ' || p_demand_source_header_id);
1600 debug_print('demand_source_line_id = ' || p_demand_source_line_id);
1601 debug_print('demand_source_line_detail = ' || p_demand_source_line_detail);
1602 debug_print('wip_entity_type = ' || p_wip_entity_type);
1603 END IF;
1604
1605 IF (p_demand_source_type_id = inv_reservation_global.g_source_type_wip AND
1606 p_wip_entity_type = inv_reservation_global.g_wip_source_type_cmro) THEN
1607 -- validate document
1608 AHL_INV_RESERVATIONS_GRP.validate_supply_demand
1609 (
1610 x_return_status => l_return_status
1611 , x_msg_count => l_msg_count
1612 , x_msg_data => l_msg_data
1613 , x_valid_status => l_valid_status
1614 , p_organization_id => p_organization_id
1615 , p_item_id => p_inventory_item_id
1616 , p_supply_demand_code => 2
1617 , p_supply_demand_type_id => p_demand_source_type_id
1618 , p_supply_demand_header_id => p_demand_source_header_id
1619 , p_supply_demand_line_id => p_demand_source_line_id
1620 , p_supply_demand_line_detail => p_demand_source_line_detail
1621 , p_demand_ship_date => p_demand_ship_date
1622 , p_expected_receipt_date => p_supply_receipt_date
1623 , p_api_version_number => 1.0
1624 , p_init_msg_lst => fnd_api.g_false
1625 );
1626
1627 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
1628 RAISE fnd_api.g_exc_error;
1629 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
1630 RAISE fnd_api.g_exc_unexpected_error;
1631 END IF;
1632
1633 IF (l_debug = 1) THEN
1634 debug_print('validate supply demand returns valid status: ' || l_valid_status);
1635 END IF;
1636
1637 IF (l_valid_status = 'N') THEN
1638 fnd_message.set_name('INV', 'INV_RSV_INVALID_DEMAND_CMRO');
1639 fnd_msg_pub.ADD;
1640 RAISE fnd_api.g_exc_error;
1641 END IF;
1642 ELSE
1643 -- return error since this is not wip demand source or not CMRO entity type
1644 fnd_message.set_name('INV', 'INV_INVALID_DEMAND_SOURCE');
1645 fnd_msg_pub.ADD;
1646 RAISE fnd_api.g_exc_error;
1647 END IF;
1648
1649 x_return_status := l_return_status;
1650
1651 EXCEPTION
1652 WHEN fnd_api.g_exc_error THEN
1653 x_return_status := fnd_api.g_ret_sts_error;
1654 --
1655 WHEN fnd_api.g_exc_unexpected_error THEN
1656 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1657 --
1658 WHEN OTHERS THEN
1659 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1660 --
1661 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1662 THEN
1663 fnd_msg_pub.add_exc_msg
1664 ( g_pkg_name
1665 , 'Validate_Demand_Source_CMRO'
1666 );
1667 END IF;
1668 --
1669 END validate_demand_source_cmro;
1670
1671 -- {{Procedure
1672 -- validate_demand_source_fpo
1673 -- Description
1674 -- Validation for demand source of FPO
1675 -- return error is the document is invalid. }}
1676 PROCEDURE validate_demand_source_fpo
1677 (
1678 x_return_status OUT NOCOPY VARCHAR2
1679 , p_organization_id IN NUMBER
1680 , p_inventory_item_id IN NUMBER
1681 , p_demand_ship_date IN DATE
1682 , p_supply_receipt_date IN DATE
1683 , p_demand_source_type_id IN NUMBER
1684 , p_demand_source_header_id IN NUMBER
1685 , p_demand_source_line_id IN NUMBER
1686 , p_demand_source_line_detail IN NUMBER
1687 , p_wip_entity_type IN NUMBER
1688 ) IS
1689
1690 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1691 l_msg_count NUMBER;
1692 l_msg_data VARCHAR2(1000);
1693 l_valid_status VARCHAR2(1);
1694 l_debug NUMBER;
1695
1696 BEGIN
1697
1698 IF (g_debug IS NULL) THEN
1699 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1700 END IF;
1701
1702 l_debug := g_debug;
1703
1704 IF (l_debug = 1) THEN
1705 debug_print('In validate_demand_source_cmro: demand_source_type_id = ' || p_demand_source_type_id);
1706 debug_print('demand_source_header_id = ' || p_demand_source_header_id);
1707 debug_print('demand_source_line_id = ' || p_demand_source_line_id);
1708 debug_print('wip_entity_type = ' || p_wip_entity_type);
1709 END IF;
1710
1711 IF (p_demand_source_type_id = inv_reservation_global.g_source_type_wip AND
1712 p_wip_entity_type = inv_reservation_global.g_wip_source_type_fpo) THEN
1713 -- validate document
1714 /*
1715 FPO_package.validate_supply_demand
1716 (
1717 x_return_status => l_return_status
1718 , x_msg_count => l_msg_count
1719 , x_msg_data => l_msg_data
1720 , x_valid_status => l_valid_status
1721 , p_organization_id => p_organization_id
1722 , p_item_id => p_inventory_item_id
1723 , p_supply_demand_code => 2
1724 , p_supply_demand_type_id => p_demand_source_type_id
1725 , p_supply_demand_header_id => p_demand_source_header_id
1726 , p_supply_demand_line_id => p_demand_source_line_id
1727 , p_supply_demand_line_detail => p_demand_source_line_detail
1728 , p_demand_ship_date => p_demand_ship_date
1729 , p_expected_receipt_date => p_supply_receipt_date
1730 , p_api_version_number => 1.0
1731 , p_init_msg_lst => fnd_api.g_false
1732 );
1733 */
1734
1735 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
1736 RAISE fnd_api.g_exc_error;
1737 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
1738 RAISE fnd_api.g_exc_unexpected_error;
1739 END IF;
1740
1741 IF (l_debug = 1) THEN
1742 debug_print('validate supply demand returns valid status: ' || l_valid_status);
1743 END IF;
1744
1745 IF (l_valid_status = 'N') THEN
1746 fnd_message.set_name('INV', 'INV_RSV_INVALID_DEMAND_FPO');
1747 fnd_msg_pub.ADD;
1748 RAISE fnd_api.g_exc_error;
1749 END IF;
1750 ELSE
1751 -- return error since this is not wip demand source or not FPO entity type
1752 fnd_message.set_name('INV', 'INV_INVALID_DEMAND_SOURCE');
1753 fnd_msg_pub.ADD;
1754 RAISE fnd_api.g_exc_error;
1755 END IF;
1756
1757 x_return_status := l_return_status;
1758
1759 EXCEPTION
1760 WHEN fnd_api.g_exc_error THEN
1761 x_return_status := fnd_api.g_ret_sts_error;
1762 --
1763 WHEN fnd_api.g_exc_unexpected_error THEN
1764 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1765 --
1766 WHEN OTHERS THEN
1767 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1768 --
1769 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1770 THEN
1771 fnd_msg_pub.add_exc_msg
1772 ( g_pkg_name
1773 , 'Validate_Demand_Source_FPO'
1774 );
1775 END IF;
1776 --
1777 END validate_demand_source_fpo;
1778
1779 -- {{Procedure
1780 -- validate_demand_source_batch
1781 -- Description
1782 -- Validation for demand source of batch
1783 -- return error is the document is invalid. }}
1784 PROCEDURE validate_demand_source_batch
1785 (
1786 x_return_status OUT NOCOPY VARCHAR2
1787 , p_organization_id IN NUMBER
1788 , p_inventory_item_id IN NUMBER
1789 , p_demand_ship_date IN DATE
1790 , p_supply_receipt_date IN DATE
1791 , p_demand_source_type_id IN NUMBER
1792 , p_demand_source_header_id IN NUMBER
1793 , p_demand_source_line_id IN NUMBER
1794 , p_demand_source_line_detail IN NUMBER
1795 , p_wip_entity_type IN NUMBER
1796 ) IS
1797
1798 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1799 l_msg_count NUMBER;
1800 l_msg_data VARCHAR2(1000);
1801 l_valid_status VARCHAR2(1);
1802 l_debug NUMBER;
1803
1804 BEGIN
1805
1806 IF (g_debug IS NULL) THEN
1807 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1808 END IF;
1809
1810 l_debug := g_debug;
1811
1812 IF (l_debug = 1) THEN
1813 debug_print('In validate_demand_source_cmro: demand_source_type_id = ' || p_demand_source_type_id);
1814 debug_print('demand_source_header_id = ' || p_demand_source_header_id);
1815 debug_print('demand_source_line_id = ' || p_demand_source_line_id);
1816 debug_print('wip_entity_type = ' || p_wip_entity_type);
1817 END IF;
1818
1819 IF (p_demand_source_type_id = inv_reservation_global.g_source_type_wip AND
1820 p_wip_entity_type = inv_reservation_global.g_wip_source_type_batch) THEN
1821 -- validate document
1822 /*
1823 Batch_package.validate_supply_demand
1824 (
1825 x_return_status => l_return_status
1826 , x_msg_count => l_msg_count
1827 , x_msg_data => l_msg_data
1828 , x_valid_status => l_valid_status
1829 , p_organization_id => p_organization_id
1830 , p_item_id => p_inventory_item_id
1831 , p_supply_demand_code => 2
1832 , p_supply_demand_type_id => p_demand_source_type_id
1833 , p_supply_demand_header_id => p_demand_source_header_id
1834 , p_supply_demand_line_id => p_demand_source_line_id
1835 , p_supply_demand_line_detail => p_demand_source_line_detail
1836 , p_demand_ship_date => p_demand_ship_date
1837 , p_expected_receipt_date => p_supply_receipt_date
1838 , p_api_version_number => 1.0
1839 , p_init_msg_lst => fnd_api.g_false
1840 );
1841 */
1842
1843 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
1844 RAISE fnd_api.g_exc_error;
1845 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
1846 RAISE fnd_api.g_exc_unexpected_error;
1847 END IF;
1848
1849 IF (l_debug = 1) THEN
1850 debug_print('validate supply demand returns valid status: ' || l_valid_status);
1851 END IF;
1852
1853 IF (l_valid_status = 'N') THEN
1854 fnd_message.set_name('INV', 'INV_RSV_INVALID_DEMAND_BATCH');
1855 fnd_msg_pub.ADD;
1856 RAISE fnd_api.g_exc_error;
1857 END IF;
1858 ELSE
1859 -- return error since this is not wip demand source or not OPM Batch entity type
1860 fnd_message.set_name('INV', 'INV_INVALID_DEMAND_SOURCE');
1861 fnd_msg_pub.ADD;
1862 RAISE fnd_api.g_exc_error;
1863 END IF;
1864
1865 x_return_status := l_return_status;
1866
1867 EXCEPTION
1868 WHEN fnd_api.g_exc_error THEN
1869 x_return_status := fnd_api.g_ret_sts_error;
1870 --
1871 WHEN fnd_api.g_exc_unexpected_error THEN
1872 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1873 --
1874 WHEN OTHERS THEN
1875 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1876 --
1877 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1878 THEN
1879 fnd_msg_pub.add_exc_msg
1880 ( g_pkg_name
1881 , 'Validate_Demand_Source_Batch'
1882 );
1883 END IF;
1884 --
1885 END validate_demand_source_batch;
1886 /*** End R12 }} ***/
1887
1888 --
1889 --
1890 -- Procedure
1891 -- validate_item_sku
1892 -- Description
1893 -- is valid if all of the following are satisfied
1894 -- 1. if the item is not under predefined serial control, p_serial_array
1895 -- is empty (you can only reserve predefined serial number)
1896 -- 2. if the item is not under lot control, p_lot_number is null
1897 -- 3. if the item is not under revision control, p_revision is null
1898 -- 4. if the item is under revision control, it is not true that
1899 -- p_revision is null and p_subinventory_code or p_locator_id is not null
1900 -- 5. if the item is under lot control, it is not true that p_lot_number
1901 -- is null and p_subinventory_code or p_locator_id is not null
1902 -- 6. if the item is under revision and lot control, it is not true that
1903 -- p_revision is null and p_lot_number is not null
1904 -- 7. p_subinventory_code, if not null, is a valid sub in the organization
1905 -- 8. if p_subinventory_code is not null and locator control is off,
1906 -- p_locator_id is null
1907 -- 9. if p_subinventory_code is null, p_locator_id is null
1908 -- 10. if p_revision is not null, it is a valid revision for the item
1909 -- 11. if p_lot_number is not null, it is a valid lot number for the item
1910 -- and the lot has not expired
1911 -- 12. if p_subiventory_code is not null and the item has restriction on
1912 -- subinventory, p_subiventory_code is a valid sub for the item
1913 -- 13. if p_subiventory_code is not null and the item has no restriction on
1914 -- subinventory, p_subiventory_code is a valid sub (necessary?)
1915 -- 14. if p_subiventory_code is not null, and p_locator_id is not null
1916 -- and the item has restriction on subinventory, p_locator_id
1917 -- is a valid locator for the sub
1918 -- 15. if p_subiventory_code is not null, and p_locator_id is not null
1919 -- and the item has no restriction on subinventory, p_locator_id
1920 -- is a valid locator for the sub
1921 -- 16. if p_serial_array is not empty, all serial number must have
1922 -- valid status
1923 -- INVCONV - Validation added for Inventory Convergence
1924 -- 17. if the item is lot_indivisible (lot_divisible_flag <> 'Y'),
1925 -- the reservation must be detailed to lot level.
1926 --
1927
1928 PROCEDURE validate_item_sku
1929 (
1930 x_return_status OUT NOCOPY VARCHAR2
1931 , p_inventory_item_id IN NUMBER
1932 , p_organization_id IN NUMBER
1933 , p_revision IN VARCHAR2
1934 , p_lot_number IN VARCHAR2
1935 , p_subinventory_code IN VARCHAR2
1936 , p_locator_id IN NUMBER
1937 , p_serial_array IN inv_reservation_global.serial_number_tbl_type
1938 , p_item_cache_index IN INTEGER
1939 , p_org_cache_index IN INTEGER
1940 , x_sub_cache_index OUT NOCOPY INTEGER
1941 ) IS
1942 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1943 l_resultant_locator_control NUMBER := NULL;
1944 l_loop_index NUMBER := NULL;
1945 l_sub_cache_index NUMBER := NULL;
1946 l_rec inv_reservation_global.sub_record;
1947 l_found VARCHAR2(1);
1948 l_lot_expiration_date DATE;
1949 l_debug NUMBER;
1950 l_default_onhand_status_id NUMBER; -- Bug 6870416
1951 -- Added for common API
1952 l_rec_loc inv_reservation_global.locator_record;
1953 l_rec_serial inv_reservation_global.serial_record;
1954 l_rec_lot inv_reservation_global.lot_record;
1955
1956
1957 BEGIN
1958
1959 l_debug := g_debug;
1960 -- Added for common API
1961 l_rec_loc.inventory_location_id:=p_locator_id;
1962 l_rec_lot.lot_number:=p_lot_number;
1963 --
1964 -- important: org and item should be validated before
1965 -- this procedure (validate_supply) is called
1966 -- since here we do not validate them again
1967 --
1968 -- if the item is not under predefined serial number control
1969 -- and the input serial number array is not empty,
1970 -- raise the error
1971 IF (l_debug = 1) THEN
1972 debug_print('Inside validate item sku: ' || l_return_status);
1973 END IF;
1974
1975 IF inv_reservation_global.g_item_record_cache(p_item_cache_index).serial_number_control_code
1976 NOT IN (inv_reservation_global.g_serial_control_predefined,
1977 inv_reservation_global.g_serial_control_dynamic_inv)
1978 AND p_serial_array.COUNT >0 THEN
1979 fnd_message.set_name('INV', 'INV_EXTRA_SERIAL');
1980 fnd_msg_pub.add;
1981 RAISE fnd_api.g_exc_error;
1982 END IF;
1983 --
1984 IF (l_debug = 1) THEN
1985 debug_print('After item cache: ' || l_return_status);
1986 END IF;
1987 -- if the item is not under lot control
1988 -- and the input lot number is not empty,
1989 -- raise the error
1990 IF inv_reservation_global.g_item_record_cache
1991 (p_item_cache_index).lot_control_code =
1992 inv_reservation_global.g_lot_control_no
1993 AND p_lot_number IS NOT NULL THEN
1994 fnd_message.set_name('INV', 'INV_NO_LOT_CONTROL');
1995 fnd_msg_pub.add;
1996 RAISE fnd_api.g_exc_error;
1997 END IF;
1998 IF (l_debug = 1) THEN
1999 debug_print('After lot cache: ' || l_return_status);
2000 END IF;
2001 --
2002 -- if the item is not under revision control
2003 -- and the input revision is not empty,
2004 -- raise the error
2005 IF inv_reservation_global.g_item_record_cache
2006 (p_item_cache_index).revision_qty_control_code =
2007 inv_reservation_global.g_revision_control_no
2008 AND p_revision IS NOT NULL THEN
2009 fnd_message.set_name('INV', 'INV_NO_REVISION_CONTROL');
2010 fnd_msg_pub.add;
2011 RAISE fnd_api.g_exc_error;
2012 END IF;
2013 --
2014 IF (l_debug = 1) THEN
2015 debug_print('After rev cache: ' || l_return_status);
2016 END IF;
2017 -- if the item is under revision control
2018 -- and the input revision is null but subinventory_code or locator_id is
2019 -- not null, raise the error
2020 IF inv_reservation_global.g_item_record_cache
2021 (p_item_cache_index).revision_qty_control_code =
2022 inv_reservation_global.g_revision_control_yes
2023 AND p_revision IS NULL
2024 AND (p_subinventory_code IS NOT NULL
2025 OR
2026 p_locator_id IS NOT NULL
2027 ) THEN
2028 fnd_message.set_name('INV', 'INV_MISSING_REV');
2029 fnd_msg_pub.add;
2030 RAISE fnd_api.g_exc_error;
2031 END IF;
2032
2033 IF (l_debug = 1) THEN
2034 debug_print('After rev/sub/loc check cache: ' || l_return_status);
2035 END IF;
2036 --
2037 -- if the item is under lot control
2038 -- and the input lot_number is null but subinventory_code or locator_id is
2039 -- not null, raise the error
2040 IF inv_reservation_global.g_item_record_cache
2041 (p_item_cache_index).lot_control_code
2042 = inv_reservation_global.g_lot_control_yes
2043 AND p_lot_number IS NULL
2044 AND (p_subinventory_code IS NOT NULL
2045 OR
2046 p_locator_id IS NOT NULL
2047 ) THEN
2048 fnd_message.set_name('INV', 'INV_MISSING_LOT');
2049 fnd_msg_pub.add;
2050 RAISE fnd_api.g_exc_error;
2051 END IF;
2052
2053 IF (l_debug = 1) THEN
2054 debug_print('After lot/sub/loc check cache: ' || l_return_status);
2055 END IF;
2056 --
2057 -- if the item is under revision and lot control
2058 -- and the input revision is null but lot_number is
2059 -- not null, raise the error
2060 IF inv_reservation_global.g_item_record_cache
2061 (p_item_cache_index).revision_qty_control_code =
2062 inv_reservation_global.g_revision_control_yes
2063 AND inv_reservation_global.g_item_record_cache
2064 (p_item_cache_index).lot_control_code
2065 = inv_reservation_global.g_lot_control_yes
2066 AND p_revision IS NULL
2067 AND p_lot_number IS NOT NULL THEN
2068 fnd_message.set_name('INV', 'INV_MISSING_REV');
2069 fnd_msg_pub.add;
2070 RAISE fnd_api.g_exc_error;
2071 END IF;
2072
2073 IF (l_debug = 1) THEN
2074 debug_print('After lot/rev check cache: ' || l_return_status);
2075 END IF;
2076 --
2077 --
2078 -- validate sub if the input is not null
2079 IF p_subinventory_code IS NOT NULL THEN
2080 inv_reservation_util_pvt.search_sub_cache
2081 (
2082 x_return_status => l_return_status
2083 , p_subinventory_code => p_subinventory_code
2084 , p_organization_id => p_organization_id
2085 , x_index => l_sub_cache_index
2086 );
2087 --
2088 IF (l_debug = 1) THEN
2089 debug_print('After search sub cache: ' || l_return_status);
2090 END IF;
2091 --
2092 IF l_return_status = fnd_api.g_ret_sts_error THEN
2093 RAISE fnd_api.g_exc_error;
2094 END IF ;
2095 --
2096 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2097 RAISE fnd_api.g_exc_unexpected_error;
2098 END IF;
2099 --
2100 -- if the sub is not in the cache, load it into the cache
2101 IF l_sub_cache_index IS NULL THEN
2102 /* BEGIN
2103 SELECT
2104 secondary_inventory_name
2105 , organization_id
2106 , locator_type
2107 , quantity_tracked
2108 , asset_inventory
2109 , reservable_type
2110 INTO l_rec
2111 FROM mtl_secondary_inventories
2112 WHERE secondary_inventory_name = p_subinventory_code
2113 AND organization_id = p_organization_id;
2114 --
2115 EXCEPTION
2116 WHEN NO_DATA_FOUND then
2117 fnd_message.set_name('INV','INVALID_SUB');
2118 fnd_msg_pub.add;
2119 RAISE fnd_api.g_exc_error;
2120 END; */
2121 -- Modified to call common API
2122 l_rec.secondary_inventory_name :=p_subinventory_code;
2123 IF INV_Validate.subinventory
2124 (
2125 p_sub => l_rec,
2126 p_org => inv_reservation_global.g_organization_record_cache(p_org_cache_index)
2127 )=INV_Validate.F THEN
2128 fnd_message.set_name('INV','INVALID_SUB');
2129 fnd_msg_pub.add;
2130 RAISE fnd_api.g_exc_error;
2131 END IF;
2132
2133
2134 --Bug 2334171 Check whether the sub is reservable
2135 -- Added the below for Bug 6870416
2136 IF inv_cache.set_org_rec(p_organization_id) THEN
2137 l_default_onhand_status_id := inv_cache.org_rec.default_status_id;
2138 END IF;
2139
2140 IF l_default_onhand_status_id IS NULL THEN
2141 IF l_rec.reservable_type = inv_globals.g_subinventory_non_reservable
2142 THEN /* non reservable Subinventory */
2143 fnd_message.set_name('INV','INV-SUBINV NOT RESERVABLE');
2144 fnd_message.set_token('SUBINV', l_rec.secondary_inventory_name);
2145 fnd_msg_pub.add;
2146 RAISE fnd_api.g_exc_error;
2147 END IF;
2148 END IF;
2149 -- End of changes for Bug 6870416
2150
2151 inv_reservation_util_pvt.add_sub_cache
2152 (
2153 x_return_status => l_return_status
2154 , p_sub_record => l_rec
2155 , x_index => l_sub_cache_index
2156 );
2157 --
2158 IF l_return_status = fnd_api.g_ret_sts_error THEN
2159 RAISE fnd_api.g_exc_error;
2160 END IF ;
2161 --
2162 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2163 RAISE fnd_api.g_exc_unexpected_error;
2164 END IF;
2165 ELSE
2166 l_rec := inv_reservation_global.g_sub_record_cache(l_sub_cache_index);
2167 END IF;
2168 --
2169 -- check lcator control based on settings at org, sub, item levels
2170 l_resultant_locator_control :=
2171 inv_reservation_util_pvt.locator_control
2172 (
2173 p_org_control
2174 => inv_reservation_global.g_organization_record_cache
2175 (p_org_cache_index)
2176 .stock_locator_control_code
2177 , p_sub_control
2178 => inv_reservation_global.g_sub_record_cache
2179 (l_sub_cache_index).locator_type
2180 , p_item_control
2181 => inv_reservation_global.g_item_record_cache
2182 (p_item_cache_index)
2183 .location_control_code
2184 );
2185 --
2186 IF (l_resultant_locator_control = 1
2187 AND p_locator_id IS NOT NULL AND p_locator_id > 0) THEN
2188 fnd_message.set_name('INV', 'INV_NO_LOCATOR_CONTROL');
2189 fnd_msg_pub.add;
2190 RAISE fnd_api.g_exc_error;
2191 END IF;
2192 --
2193 ELSIF p_locator_id IS NOT NULL THEN
2194 -- if the sub is null, but the locator id is not null
2195 -- raise the error
2196 fnd_message.set_name('INV', 'INV_NO_LOCATOR_CONTROL');
2197 fnd_msg_pub.add;
2198 RAISE fnd_api.g_exc_error;
2199 END IF;
2200 --
2201 -- Now we have validated that values are there.
2202 -- Now validate that values are correct
2203 IF p_revision IS NOT NULL THEN
2204 /* BEGIN
2205 SELECT 'Y' INTO l_found
2206 FROM mtl_item_revisions
2207 WHERE inventory_item_id = p_inventory_item_id
2208 AND organization_id = p_organization_id
2209 AND revision = p_revision ;
2210 --
2211 EXCEPTION
2212 WHEN NO_DATA_FOUND THEN
2213 fnd_message.set_name('INV','INVALID_REVISION');
2214 fnd_msg_pub.add;
2215 RAISE fnd_api.g_exc_error;
2216 END;*/
2217 IF INV_Validate.revision
2218 (
2219 p_revision => p_revision,
2220 p_org => inv_reservation_global.g_organization_record_cache
2221 (p_org_cache_index),
2222 p_item => inv_reservation_global.g_item_record_cache(p_item_cache_index) )=INV_Validate.F THEN
2223
2224 fnd_message.set_name('INV','INVALID_REVISION');
2225 fnd_msg_pub.add;
2226 RAISE fnd_api.g_exc_error;
2227 END IF;
2228
2229 END IF;
2230 --
2231 IF p_lot_number IS NOT NULL THEN
2232 BEGIN
2233 SELECT expiration_date INTO l_lot_expiration_date
2234 FROM mtl_lot_numbers
2235 WHERE inventory_item_id = p_inventory_item_id
2236 AND organization_id = p_organization_id
2237 AND lot_number = p_lot_number;
2238 --
2239 IF l_lot_expiration_date IS NOT NULL
2240 AND l_lot_expiration_date < Sysdate THEN
2241 fnd_message.set_name('INV', 'INV_LOT_EXPIRED');
2242 fnd_msg_pub.add;
2243 RAISE fnd_api.g_exc_error;
2244 END IF;
2245 --
2246 EXCEPTION
2247 WHEN NO_DATA_FOUND then
2248 fnd_message.set_name('INV','INV_INVALID_LOT');
2249 fnd_msg_pub.add;
2250 RAISE fnd_api.g_exc_error;
2251 END;
2252 END IF;
2253 --
2254 IF p_subinventory_code IS NOT NULL THEN
2255 --
2256 -- validate the sub is valid in the org
2257
2258 -- Modified for common API. This validation has already been performed above.
2259
2260 /* BEGIN
2261 SELECT 'Y' INTO l_found
2262 FROM mtl_secondary_inventories
2263 WHERE secondary_inventory_name = p_subinventory_code
2264 AND organization_id = p_organization_id;
2265 EXCEPTION
2266 WHEN no_data_found THEN
2267 fnd_message.set_name('INV', 'INVALID_SUB');
2268 fnd_msg_pub.add;
2269 RAISE fnd_api.g_exc_error;
2270
2271 END;*/
2272
2273
2274 --
2275 IF inv_reservation_global.g_item_record_cache
2276 (p_item_cache_index)
2277 .restrict_subinventories_code = 1 THEN
2278 -- for restricted subs, use table mtl_item_subinventories
2279 BEGIN
2280 SELECT 'Y' INTO l_found
2281 FROM mtl_item_sub_trk_all_v
2282 WHERE inventory_item_id = p_inventory_item_id
2283 AND organization_id = p_organization_id
2284 AND secondary_inventory_name = p_subinventory_code;
2285 --
2286 EXCEPTION
2287 WHEN NO_DATA_FOUND THEN
2288 fnd_message.set_name('INV','INVALID_SUB');
2289 fnd_msg_pub.add;
2290 RAISE fnd_api.g_exc_error;
2291 END ;
2292 ELSIF inv_reservation_global.g_item_record_cache
2293 (p_item_cache_index)
2294 .restrict_subinventories_code = 2 THEN
2295 -- item is not restricted to specific subs
2296 BEGIN
2297 SELECT 'Y' INTO l_found
2298 FROM mtl_subinventories_trk_val_v
2299 WHERE organization_id = p_organization_id
2300 AND secondary_inventory_name = p_subinventory_code ;
2301 --
2302 EXCEPTION
2303 WHEN NO_DATA_FOUND THEN
2304 fnd_message.SET_NAME('INV','INVALID_SUB');
2305 fnd_msg_pub.add;
2306 RAISE fnd_api.g_exc_error;
2307 END ;
2308 END IF;
2309 --
2310 -- now if locator id is not null then validate its value
2311
2312
2313 IF (p_locator_id IS NOT NULL AND p_locator_id > 0) THEN
2314 -- check if locator is restricted to subs
2315 IF inv_reservation_global.g_item_record_cache
2316 (p_item_cache_index)
2317 .restrict_locators_code = 1 THEN
2318 BEGIN
2319 -- Modified to call common API
2320 /* SELECT 'Y' INTO l_found
2321 FROM
2322 mtl_secondary_locators msl
2323 , mtl_item_locations mil
2324 WHERE msl.inventory_item_id = p_inventory_item_id
2325 AND msl.organization_id = p_organization_id
2326 AND msl.subinventory_code = p_subinventory_code
2327 AND msl.secondary_locator = p_locator_id
2328 AND msl.secondary_locator = mil.inventory_location_id
2329 AND (mil.disable_date > sysdate
2330 OR mil.disable_date IS NULL
2331 );
2332 --
2333 EXCEPTION
2334 WHEN NO_DATA_FOUND THEN
2335 fnd_message.set_name('INV','INV_LOCATOR_NOT_AVAILABLE');
2336 fnd_msg_pub.add;
2337 RAISE fnd_api.g_exc_error; */
2338
2339
2340 IF INV_Validate.validateLocator(
2341 p_locator => l_rec_loc,
2342 p_org => inv_reservation_global.g_organization_record_cache
2343 (p_org_cache_index),
2344 p_sub => l_rec,
2345 p_item => inv_reservation_global.g_item_record_cache(p_item_cache_index)
2346 )=INV_Validate.F THEN
2347 fnd_message.set_name('INV','INV_LOCATOR_NOT_AVAILABLE');
2348 fnd_msg_pub.add;
2349 RAISE fnd_api.g_exc_error;
2350 END IF;
2351 END;
2352 ELSIF inv_reservation_global.g_item_record_cache
2353 (p_item_cache_index)
2354 .restrict_locators_code = 2 THEN
2355 /* BEGIN
2356 SELECT 'Y' INTO l_found
2357 FROM mtl_item_locations
2358 WHERE organization_id = p_organization_id
2359 AND subinventory_code = p_subinventory_code
2360 AND inventory_location_id = p_locator_id
2361 AND (disable_date > sysdate
2362 OR disable_date IS NULL
2363 );
2364 --
2365 EXCEPTION
2366 WHEN NO_DATA_FOUND THEN
2367 fnd_message.set_name('INV','INV_LOCATOR_NOT_AVAILABLE');
2368 fnd_msg_pub.add;
2369 RAISE fnd_api.g_exc_error;
2370 END;*/
2371
2372 -- Modified for common API
2373
2374 IF INV_Validate.validateLocator(
2375 p_locator => l_rec_loc,
2376 p_org => inv_reservation_global.g_organization_record_cache
2377 (p_org_cache_index),
2378 p_sub => l_rec)=INV_Validate.F THEN
2379 fnd_message.set_name('INV','INV_LOCATOR_NOT_AVAILABLE');
2380 fnd_msg_pub.add;
2381 RAISE fnd_api.g_exc_error;
2382 END IF;
2383 END IF;
2384 END IF; -- if p_locator_id is not null
2385 END IF; -- if p_subinventory_code is not null
2386
2387 /*** {{ R12 Enhanced reservations code changes ***/
2388 -- We dont have to validate serial numbers as the serials are
2389 -- validated as part of validate_serials
2390 --
2391 -- Now validate the serial numbers if there is
2392 -- Check if they exist and have the
2393 -- right status
2394 -- IF p_serial_array.COUNT > 0 THEN
2395 -- l_loop_index := p_serial_array.first ;
2396 -- BEGIN
2397 -- LOOP
2398 -- Modified to call common API
2399 --/*
2400 -- SELECT 'Y' INTO l_found
2401 -- FROM mtl_serial_numbers
2402 -- WHERE serial_number = p_serial_array(l_loop_index).serial_number
2403 -- AND current_status IN (1,3)
2404 -- the next line is commented out as
2405 -- currently serial number table has not been
2406 -- updated to include reservation id as a column
2407 -- AND reservation_id IS NULL ;
2408 -- ;*/
2409 -- l_rec_serial.serial_number:=p_serial_array(l_loop_index).serial_number;
2410 /*** {{ R12 Enhanced reservations code changes ***/
2411 -- IF INV_Validate.check_serial(
2412 -- p_serial => l_rec_serial,
2413 -- p_org => inv_reservation_global.g_organization_record_cache
2414 -- (p_org_cache_index),
2415 -- p_item => inv_reservation_global.g_item_record_cache(p_item_cache_index)
2416 -- ,
2417 -- p_from_sub => l_rec,
2418 -- p_lot => l_rec_lot,
2419 -- p_loc => l_rec_loc,
2420 -- p_revision => p_revision,
2421 -- p_msg => 'RSV'
2422 -- )=INV_Validate.F THEN
2423 --
2424 -- fnd_message.set_name('INV','INVALID_SERIAL_NUMBER');
2425 -- fnd_message.set_token('NUMBER',p_serial_array(l_loop_index).serial_number,FALSE);
2426 /*** {{ R12 Enhanced ----reservations code changes ***/
2427 -- fnd_msg_pub.add;
2428 -- RAISE fnd_api.g_exc_error;
2429 -- END IF;
2430 -- EXIT WHEN l_loop_index = p_serial_array.last ;
2431 -- l_loop_index := p_serial_array.next(l_loop_index);
2432 -- END LOOP;
2433 -- /*EXCEPTION
2434 -- WHEN NO_DATA_FOUND THEN
2435 -- fnd_message.set_name('INV','INVALID_SERIAL_NUMBER');
2436 -- fnd_message.set_token('NUMBER',p_serial_array(l_loop_index),FALSE);
2437 -- fnd_msg_pub.add;
2438 -- RAISE fnd_api.g_exc_error;
2439 -- */
2440 -- END;
2441 -- END IF;
2442 /*** End R12 }} ***/
2443
2444 --
2445 -- INVCONV BEGIN
2446 -- Additional validations for process attributes introduced for inventory convergence
2447 -- ==================================================================================
2448 --
2449 -- if the item is defined as lot_indivisible (lot_divisible_flag <> 'Y')
2450 -- the reservation must be detailed to lot level so ensure that the
2451 -- lot_number is populated
2452 IF inv_reservation_global.g_item_record_cache
2453 (p_item_cache_index).lot_divisible_flag <> 'Y' AND
2454 inv_reservation_global.g_item_record_cache(p_item_cache_index).lot_control_code = inv_reservation_global.g_lot_control_yes AND
2455 p_lot_number IS NULL THEN
2456 fnd_message.set_name('INV', 'INV_INDIVISIBLE_LOT_REQUIRED'); -- INVCONV New Message
2457 fnd_msg_pub.add;
2458 RAISE fnd_api.g_exc_error;
2459 END IF;
2460 -- INVCONV END
2461 --
2462 x_return_status := l_return_status;
2463 x_sub_cache_index:= l_sub_cache_index;
2464 --
2465 EXCEPTION
2466 WHEN fnd_api.g_exc_error THEN
2467 x_return_status := fnd_api.g_ret_sts_error;
2468 --
2469 WHEN fnd_api.g_exc_unexpected_error THEN
2470 x_return_status := fnd_api.g_ret_sts_unexp_error ;
2471 --
2472 WHEN OTHERS THEN
2473 x_return_status := fnd_api.g_ret_sts_unexp_error ;
2474 --
2475 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
2476 THEN
2477 fnd_msg_pub.add_exc_msg
2478 ( g_pkg_name
2479 , 'Validate_Item_SKU'
2480 );
2481 END IF;
2482 --
2483 END validate_item_sku;
2484 --
2485 -- Procedure
2486 -- validate_supply_source
2487 -- Description
2488 -- is valid if all of the following are satisfied
2489 -- 1. p_supply_source_type_id is not null
2490 -- no longer needed 2. p_supply_source_header_id or p_supply_source_name is not null
2491 -- 3. calling validate_item_sku with the sku info and returning success
2492 PROCEDURE validate_supply_source
2493 (
2494 x_return_status OUT NOCOPY VARCHAR2
2495 , p_inventory_item_id IN NUMBER
2496 , p_organization_id IN NUMBER
2497 , p_supply_source_type_id IN NUMBER
2498 , p_supply_source_header_id IN NUMBER
2499 , p_supply_source_line_id IN NUMBER
2500 , p_supply_source_line_detail IN NUMBER
2501 , p_supply_source_name IN VARCHAR2
2502 , p_demand_source_type_id IN NUMBER
2503 , p_revision IN VARCHAR2
2504 , p_lot_number IN VARCHAR2
2505 , p_subinventory_code IN VARCHAR2
2506 , p_locator_id IN NUMBER
2507 , p_serial_array IN inv_reservation_global.serial_number_tbl_type
2508 , p_demand_ship_date IN DATE
2509 , p_supply_receipt_date IN DATE
2510 , p_item_cache_index IN INTEGER
2511 , p_org_cache_index IN INTEGER
2512 , x_supply_cache_index OUT NOCOPY INTEGER
2513 , x_sub_cache_index OUT NOCOPY INTEGER
2514 ) IS
2515 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
2516 l_structure_num NUMBER := NULL;
2517 l_supply_cache_index NUMBER := NULL;
2518 l_sub_cache_index NUMBER := NULL;
2519 l_is_valid NUMBER := NULL;
2520 l_rec inv_reservation_global.supply_record;
2521
2522 /*** {{ R12 Enhanced reservations code changes ***/
2523 l_msg_count NUMBER;
2524 l_msg_data VARCHAR2(1000);
2525 l_wip_entity_type NUMBER;
2526 l_wip_job_type VARCHAR2(15);
2527 l_debug NUMBER;
2528 /*** End R12 }} ***/
2529
2530 BEGIN
2531 --
2532 /*** {{ R12 Enhanced reservations code changes ***/
2533 IF (g_debug IS NULL) THEN
2534 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2535 END IF;
2536
2537 l_debug := g_debug;
2538
2539 IF (l_debug = 1) THEN
2540 debug_print('In validate_supply_source: supply_source_type_id = ' || p_supply_source_type_id);
2541 debug_print('In validate_supply_source: supply_source_header_id = ' || p_supply_source_header_id);
2542 debug_print('In validate_supply_source: supply_source_line_id = ' || p_supply_source_line_id);
2543 debug_print('In validate_supply_source: supply_source_name = ' || p_supply_source_name);
2544 debug_print('In validate_supply_source: supply_source_line detail = ' || p_supply_source_line_detail);
2545 debug_print('In validate_supply_source: demand_source_type_id = ' || p_demand_source_type_id);
2546 END IF;
2547 /*** End R12 }} ***/
2548
2549 IF p_supply_source_type_id IS NULL THEN
2550 fnd_message.set_name('INV', 'MISSING SUPPLY');
2551 fnd_msg_pub.add;
2552 RAISE fnd_api.g_exc_error;
2553 END IF;
2554 --
2555
2556 /*** {{ R12 Enhanced reservations code changes ***/
2557 -- Returns error if we do not support the supply type
2558 IF (p_supply_source_type_id NOT IN
2559 (inv_reservation_global.g_source_type_po,
2560 inv_reservation_global.g_source_type_inv, inv_reservation_global.g_source_type_req,
2561 inv_reservation_global.g_source_type_internal_req, inv_reservation_global.g_source_type_asn,
2562 inv_reservation_global.g_source_type_intransit, inv_reservation_global.g_source_type_wip,
2563 inv_reservation_global.g_source_type_rcv)) THEN
2564
2565 fnd_message.set_name('INV', 'INV_RSV_INVALID_SUPPLY');
2566 fnd_msg_pub.ADD;
2567 RAISE fnd_api.g_exc_error;
2568 END IF;
2569 /*** End R12 }} ***/
2570
2571 IF (l_debug = 1) THEN
2572 debug_print('Before calling suppy cache. return status :' ||
2573 l_return_status);
2574 END IF;
2575 -- search for the supply source in the cache first
2576 inv_reservation_util_pvt.search_supply_cache
2577 (
2578 x_return_status => l_return_status
2579 , p_supply_source_type_id => p_supply_source_type_id
2580 , p_supply_source_header_id => p_supply_source_header_id
2581 , p_supply_source_line_id => p_supply_source_line_id
2582 , p_supply_source_name => p_supply_source_name
2583 , x_index => l_supply_cache_index
2584 );
2585 --
2586 IF l_return_status = fnd_api.g_ret_sts_error THEN
2587 RAISE fnd_api.g_exc_error;
2588 END IF ;
2589 --
2590 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2591 RAISE fnd_api.g_exc_unexpected_error;
2592 END IF;
2593 --
2594 -- for other supply sources (wip, po), call their validation api
2595 -- when available
2596 --
2597 IF (l_debug = 1) THEN
2598 debug_print('After calling supply cache ' || p_supply_source_type_id);
2599 debug_print('Return status :' || l_return_status);
2600 END IF;
2601
2602 IF p_supply_source_type_id = inv_reservation_global.g_source_type_po
2603 THEN
2604 IF (l_debug = 1) THEN
2605 debug_print('Before calling validate po ' || l_return_status);
2606 END IF;
2607 validate_supply_source_po
2608 (
2609 x_return_status => l_return_status
2610 , p_organization_id => p_organization_id
2611 , p_inventory_item_id => p_inventory_item_id
2612 , p_demand_ship_date => p_demand_ship_date
2613 , p_supply_receipt_date => p_supply_receipt_date
2614 , p_supply_source_type_id => p_supply_source_type_id /*** {{ R12 Enhanced reservations code changes }}***/
2615 , p_supply_source_header_id => p_supply_source_header_id
2616 , p_supply_source_line_id => p_supply_source_line_id
2617 , p_supply_source_line_detail => NULL /*** {{ R12 Enhanced reservations code changes }}***/
2618 );
2619
2620 IF (l_debug = 1) THEN
2621 debug_print('After calling validate po ' || l_return_status);
2622 END IF;
2623
2624 IF l_return_status = fnd_api.g_ret_sts_error THEN
2625 RAISE fnd_api.g_exc_error;
2626 END IF ;
2627 --
2628 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2629 RAISE fnd_api.g_exc_unexpected_error;
2630 END IF;
2631 ELSIF p_supply_source_type_id = inv_reservation_global.g_source_type_req
2632 THEN
2633 IF (l_debug = 1) THEN
2634 debug_print('Before calling validate req ' || l_return_status);
2635 END IF;
2636 validate_supply_source_req
2637 (
2638 x_return_status => l_return_status
2639 , p_organization_id => p_organization_id
2640 , p_inventory_item_id => p_inventory_item_id
2641 , p_demand_ship_date => p_demand_ship_date
2642 , p_supply_receipt_date => p_supply_receipt_date
2643 , p_supply_source_type_id => p_supply_source_type_id /*** {{ R12 Enhanced reservations code changes }}***/
2644 , p_supply_source_header_id => p_supply_source_header_id
2645 , p_supply_source_line_id => p_supply_source_line_id
2646 , p_supply_source_line_detail => NULL /*** {{ R12 Enhanced reservations code changes }}***/
2647 );
2648 IF (l_debug = 1) THEN
2649 debug_print('After calling validate req ' || l_return_status);
2650 END IF;
2651 IF l_return_status = fnd_api.g_ret_sts_error THEN
2652 RAISE fnd_api.g_exc_error;
2653 END IF ;
2654 --
2655 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2656 RAISE fnd_api.g_exc_unexpected_error;
2657 END IF;
2658
2659 /*** {{ R12 Enhanced reservations code changes ***/
2660 ELSIF (p_supply_source_type_id = inv_reservation_global.g_source_type_internal_req) THEN
2661 validate_supply_source_intreq
2662 (
2663 x_return_status => l_return_status
2664 , p_organization_id => p_organization_id
2665 , p_inventory_item_id => p_inventory_item_id
2666 , p_demand_ship_date => p_demand_ship_date
2667 , p_supply_receipt_date => p_supply_receipt_date
2668 , p_supply_source_type_id => p_supply_source_type_id
2669 , p_supply_source_header_id => p_supply_source_header_id
2670 , p_supply_source_line_id => p_supply_source_line_id
2671 , p_supply_source_line_detail => NULL
2672 );
2673
2674 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
2675 RAISE fnd_api.g_exc_error;
2676 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
2677 RAISE fnd_api.g_exc_unexpected_error;
2678 END IF;
2679 ELSIF (p_supply_source_type_id = inv_reservation_global.g_source_type_asn) THEN
2680 validate_supply_source_asn
2681 (
2682 x_return_status => l_return_status
2683 , p_organization_id => p_organization_id
2684 , p_inventory_item_id => p_inventory_item_id
2685 , p_demand_ship_date => p_demand_ship_date
2686 , p_supply_receipt_date => p_supply_receipt_date
2687 , p_supply_source_type_id => p_supply_source_type_id
2688 , p_supply_source_header_id => p_supply_source_header_id
2689 , p_supply_source_line_id => p_supply_source_line_id
2690 , p_supply_source_line_detail => p_supply_source_line_detail
2691 );
2692
2693 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
2694 RAISE fnd_api.g_exc_error;
2695 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
2696 RAISE fnd_api.g_exc_unexpected_error;
2697 END IF;
2698 ELSIF (p_supply_source_type_id = inv_reservation_global.g_source_type_intransit) THEN
2699 validate_supply_source_intran
2700 (
2701 x_return_status => l_return_status
2702 , p_organization_id => p_organization_id
2703 , p_inventory_item_id => p_inventory_item_id
2704 , p_demand_ship_date => p_demand_ship_date
2705 , p_supply_receipt_date => p_supply_receipt_date
2706 , p_supply_source_type_id => p_supply_source_type_id
2707 , p_supply_source_header_id => p_supply_source_header_id
2708 , p_supply_source_line_id => p_supply_source_line_id
2709 , p_supply_source_line_detail => NULL
2710 );
2711
2712 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
2713 RAISE fnd_api.g_exc_error;
2714 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
2715 RAISE fnd_api.g_exc_unexpected_error;
2716 END IF;
2717 ELSIF (p_supply_source_type_id = inv_reservation_global.g_source_type_rcv) THEN
2718 validate_supply_source_rcv
2719 (
2720 x_return_status => l_return_status
2721 , p_organization_id => p_organization_id
2722 , p_item_id => p_inventory_item_id
2723 , p_demand_ship_date => p_demand_ship_date
2724 , p_supply_receipt_date => p_supply_receipt_date
2725 , p_supply_source_type_id => p_supply_source_type_id
2726 , p_supply_source_header_id => p_supply_source_header_id
2727 , p_supply_source_line_id => p_supply_source_line_id
2728 , p_supply_source_line_detail => NULL
2729 );
2730
2731 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
2732 RAISE fnd_api.g_exc_error;
2733 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
2734 RAISE fnd_api.g_exc_unexpected_error;
2735 END IF;
2736
2737 ELSIF (p_supply_source_type_id = inv_reservation_global.g_source_type_wip) THEN
2738 -- get wip entity id from wip_record_cache
2739 inv_reservation_util_pvt.get_wip_cache
2740 (
2741 x_return_status => l_return_status
2742 , p_wip_entity_id => p_supply_source_header_id
2743 );
2744
2745 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
2746 RAISE fnd_api.g_exc_error;
2747 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
2748 RAISE fnd_api.g_exc_unexpected_error;
2749 ELSE
2750 l_wip_entity_type := inv_reservation_global.g_wip_record_cache(p_supply_source_header_id).wip_entity_type;
2751 l_wip_job_type := inv_reservation_global.g_wip_record_cache(p_supply_source_header_id).wip_entity_job;
2752 END IF;
2753
2754 IF (l_wip_entity_type NOT IN
2755 (inv_reservation_global.g_wip_source_type_discrete,
2756 inv_reservation_global.g_wip_source_type_osfm, inv_reservation_global.g_wip_source_type_fpo,
2757 inv_reservation_global.g_wip_source_type_batch)) THEN
2758 fnd_message.set_name('INV', 'INV_RSV_WIP_ENT_ERR');
2759 fnd_msg_pub.ADD;
2760 RAISE fnd_api.g_exc_error;
2761 END IF;
2762
2763 -- add validation to check if the supply is wip discrete and osfm, then the
2764 -- demand source needs to be sales order or internal order, otherwise, error out.
2765 IF (l_wip_entity_type = inv_reservation_global.g_wip_source_type_discrete OR
2766 l_wip_entity_type = inv_reservation_global.g_wip_source_type_osfm) THEN
2767 IF (p_demand_source_type_id NOT IN (inv_reservation_global.g_source_type_oe,
2768 inv_reservation_global.g_source_type_internal_ord)) THEN
2769 fnd_message.set_name('INV', 'INV_INVALID_DEMAND_SOURCE');
2770 fnd_msg_pub.ADD;
2771 RAISE fnd_api.g_exc_error;
2772 END IF;
2773 END IF;
2774
2775 IF (l_wip_entity_type = inv_reservation_global.g_wip_source_type_discrete) THEN
2776 validate_supply_source_wipdisc
2777 (
2778 x_return_status => l_return_status
2779 , p_organization_id => p_organization_id
2780 , p_inventory_item_id => p_inventory_item_id
2781 , p_demand_ship_date => p_demand_ship_date
2782 , p_supply_receipt_date => p_supply_receipt_date
2783 , p_supply_source_type_id => p_supply_source_type_id
2784 , p_supply_source_header_id => p_supply_source_header_id
2785 , p_supply_source_line_id => p_supply_source_line_id
2786 , p_supply_source_line_detail => NULL
2787 , p_wip_entity_type => l_wip_entity_type
2788 );
2789
2790 IF (l_debug = 1) THEN
2791 debug_print('Return status from supply source wipdisc :' || l_return_status);
2792 END IF;
2793
2794 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
2795 RAISE fnd_api.g_exc_error;
2796 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
2797 RAISE fnd_api.g_exc_unexpected_error;
2798 END IF;
2799
2800 ELSIF (l_wip_entity_type = inv_reservation_global.g_wip_source_type_osfm) THEN
2801 validate_supply_source_osfm
2802 (
2803 x_return_status => l_return_status
2804 , p_organization_id => p_organization_id
2805 , p_inventory_item_id => p_inventory_item_id
2806 , p_demand_ship_date => p_demand_ship_date
2807 , p_supply_receipt_date => p_supply_receipt_date
2808 , p_supply_source_type_id => p_supply_source_type_id
2809 , p_supply_source_header_id => p_supply_source_header_id
2810 , p_supply_source_line_id => p_supply_source_line_id
2811 , p_supply_source_line_detail => NULL
2812 , p_wip_entity_type => l_wip_entity_type
2813 );
2814
2815 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
2816 RAISE fnd_api.g_exc_error;
2817 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
2818 RAISE fnd_api.g_exc_unexpected_error;
2819 END IF;
2820 ELSIF (l_wip_entity_type = inv_reservation_global.g_wip_source_type_fpo) THEN
2821 validate_supply_source_fpo
2822 (
2823 x_return_status => l_return_status
2824 , p_organization_id => p_organization_id
2825 , p_inventory_item_id => p_inventory_item_id
2826 , p_demand_ship_date => p_demand_ship_date
2827 , p_supply_receipt_date => p_supply_receipt_date
2828 , p_supply_source_type_id => p_supply_source_type_id
2829 , p_supply_source_header_id => p_supply_source_header_id
2830 , p_supply_source_line_id => p_supply_source_line_id
2831 , p_supply_source_line_detail => NULL
2832 , p_wip_entity_type => l_wip_entity_type
2833 );
2834 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
2835 RAISE fnd_api.g_exc_error;
2836 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
2837 RAISE fnd_api.g_exc_unexpected_error;
2838 END IF;
2839 ELSIF (l_wip_entity_type = inv_reservation_global.g_wip_source_type_batch) THEN
2840 validate_supply_source_batch
2841 (
2842 x_return_status => l_return_status
2843 , p_organization_id => p_organization_id
2844 , p_inventory_item_id => p_inventory_item_id
2845 , p_demand_ship_date => p_demand_ship_date
2846 , p_supply_receipt_date => p_supply_receipt_date
2847 , p_supply_source_type_id => p_supply_source_type_id
2848 , p_supply_source_header_id => p_supply_source_header_id
2849 , p_supply_source_line_id => p_supply_source_line_id
2850 , p_supply_source_line_detail => NULL
2851 , p_wip_entity_type => l_wip_entity_type
2852 );
2853 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
2854 RAISE fnd_api.g_exc_error;
2855 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
2856 RAISE fnd_api.g_exc_unexpected_error;
2857 END IF;
2858 END IF;
2859 /*** End R12 }} ***/
2860 END IF;
2861
2862 -- Here we should know that the supply source is valid
2863 -- we can add it to cache if it is not there yet
2864 IF l_supply_cache_index IS NULL THEN
2865 l_rec.supply_source_type_id := p_supply_source_type_id;
2866 l_rec.supply_source_header_id := p_supply_source_header_id;
2867 l_rec.supply_source_line_id := p_supply_source_line_id;
2868 l_rec.supply_source_name := p_supply_source_name;
2869 l_rec.is_valid := 1; -- 1 = true
2870 --
2871 inv_reservation_util_pvt.add_supply_cache
2872 (
2873 x_return_status => l_return_status
2874 , p_supply_record => l_rec
2875 , x_index => l_supply_cache_index
2876 );
2877 --
2878
2879 IF (l_debug = 1) THEN
2880 debug_print('After adding supply cache. Return status :' || l_return_status);
2881 END IF;
2882
2883 IF l_return_status = fnd_api.g_ret_sts_error THEN
2884 RAISE fnd_api.g_exc_error;
2885 END IF ;
2886 --
2887 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2888 RAISE fnd_api.g_exc_unexpected_error;
2889 END IF;
2890 END IF;
2891 --
2892 -- call validate_item_sku
2893 IF p_supply_source_type_id = inv_reservation_global.g_source_type_inv
2894 THEN
2895 validate_item_sku
2896 (
2897 x_return_status => l_return_status
2898 , p_inventory_item_id => p_inventory_item_id
2899 , p_organization_id => p_organization_id
2900 , p_revision => p_revision
2901 , p_lot_number => p_lot_number
2902 , p_subinventory_code => p_subinventory_code
2903 , p_locator_id => p_locator_id
2904 , p_serial_array => p_serial_array
2905 , p_item_cache_index => p_item_cache_index
2906 , p_org_cache_index => p_org_cache_index
2907 , x_sub_cache_index => l_sub_cache_index
2908 );
2909 --
2910 IF (l_debug = 1) THEN
2911 debug_print('After adding validate item sku. Return status :' ||
2912 l_return_status);
2913 END IF;
2914 IF l_return_status = fnd_api.g_ret_sts_error THEN
2915 RAISE fnd_api.g_exc_error;
2916 END IF ;
2917 --
2918 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2919 RAISE fnd_api.g_exc_unexpected_error;
2920 END IF;
2921 ELSIF p_subinventory_code IS NOT NULL
2922 OR p_locator_id IS NOT NULL
2923 THEN
2924 -- if the supply source is not inv, sub, locator should be null and serial number should be empty
2925 fnd_message.set_name('INV', 'EXTRA_SUPPLY_INFO');
2926 fnd_msg_pub.ADD;
2927 RAISE fnd_api.g_exc_error;
2928 END IF;
2929 --
2930 x_sub_cache_index := l_sub_cache_index;
2931 x_supply_cache_index := l_supply_cache_index;
2932 x_return_status := l_return_status;
2933 --
2934 EXCEPTION
2935 WHEN fnd_api.g_exc_error THEN
2936 x_return_status := fnd_api.g_ret_sts_error;
2937 IF (l_debug = 1) THEN
2938 debug_print('Return status from supply source :' || x_return_status);
2939 END IF;
2940 --
2941 WHEN fnd_api.g_exc_unexpected_error THEN
2942 x_return_status := fnd_api.g_ret_sts_unexp_error ;
2943 --
2944 WHEN OTHERS THEN
2945 x_return_status := fnd_api.g_ret_sts_unexp_error ;
2946 --
2947 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
2948 THEN
2949 fnd_msg_pub.add_exc_msg
2950 ( g_pkg_name
2951 , 'Validate_Supply_Source'
2952 );
2953 END IF;
2954 --
2955 END validate_supply_source;
2956 --
2957 -- Procedure
2958 -- validate_quantity
2959 -- Description
2960 -- is valid if all of the following are satisfied
2961 -- 1. p_primary_uom or p_reservation_uom is not null
2962 -- 2. p_primary_quantity or p_reservation_quantity is not null
2963 -- 3. if p_has_serial_number = fnd_api.g_true, p_primary_quantity or
2964 -- if p_primary_quantity is null, p_reservation_quantity is an integer
2965 -- INVCONV
2966 -- Additional validations for single/dual tracking
2967 PROCEDURE validate_quantity
2968 (
2969 x_return_status OUT NOCOPY VARCHAR2
2970 , p_primary_uom IN VARCHAR2
2971 , p_primary_quantity IN NUMBER
2972 , p_secondary_uom IN VARCHAR2 -- INVCONV
2973 , p_secondary_quantity IN NUMBER -- INVCONV
2974 , p_reservation_uom IN VARCHAR2
2975 , p_reservation_quantity IN NUMBER
2976 , p_lot_number IN VARCHAR2 -- INVCONV
2977 , p_has_serial_number IN VARCHAR2
2978 , p_item_cache_index IN NUMBER -- INVCON
2979 ) IS
2980 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
2981 l_quantity NUMBER;
2982 l_error_message VARCHAR2(1000); -- INVCONV
2983 l_qtys_within_dev NUMBER DEFAULT 1; -- INVCONV
2984 BEGIN
2985 --
2986 IF p_primary_uom IS NULL
2987 AND p_reservation_uom IS NULL THEN
2988 fnd_message.set_name('INV', 'MISSING UOM');
2989 fnd_msg_pub.add;
2990 RAISE fnd_api.g_exc_error;
2991 END IF;
2992 --
2993 IF p_primary_quantity IS NULL
2994 AND p_reservation_quantity IS NULL THEN
2995 fnd_message.set_name('INV', 'MISSING RSV QUANTITY');
2996 fnd_msg_pub.add;
2997 RAISE fnd_api.g_exc_error;
2998 END IF;
2999 --
3000 IF p_primary_quantity IS NOT NULL THEN
3001 l_quantity := p_primary_quantity;
3002 ELSE
3003 l_quantity := p_primary_quantity;
3004 END IF;
3005 --
3006 -- the quantity should be an integer
3007 -- if serial number is provided
3008 IF l_quantity <> Trunc(l_quantity)
3009 AND p_has_serial_number = fnd_api.g_true THEN
3010 fnd_message.set_name('INV', 'INV_QTY_EQ_INTEGER');
3011 fnd_msg_pub.add;
3012 RAISE fnd_api.g_exc_error;
3013 END IF;
3014 --
3015
3016 -- if the item is not defined as dual control
3017 -- secondary_uom_code and secondary_reservation_quantity
3018 -- should be empty
3019 IF inv_reservation_global.g_item_record_cache
3020 (p_item_cache_index).tracking_quantity_ind <> 'PS' THEN
3021 -- SINGLE UOM TRACKING
3022 -- ===================
3023 IF p_secondary_uom IS NOT NULL THEN
3024 fnd_message.set_name('INV', 'INV_SECONDARY_UOM_NOT_REQUIRED'); -- INVCONV New Message
3025 fnd_msg_pub.add;
3026 RAISE fnd_api.g_exc_error;
3027 ELSIF p_secondary_quantity IS NOT NULL THEN
3028 fnd_message.set_name('INV', 'INV_SECONDARY_QTY_NOT_REQUIRED');-- INVCONV New Message
3029 fnd_msg_pub.add;
3030 RAISE fnd_api.g_exc_error;
3031 END IF;
3032 ELSIF inv_reservation_global.g_item_record_cache
3033 (p_item_cache_index).tracking_quantity_ind = 'PS' THEN
3034 -- DUAL UOM TRACKING
3035 -- =================
3036 IF p_secondary_uom IS NULL THEN
3037 fnd_message.set_name('INV', 'INV_SECONDARY_UOM_REQUIRED'); -- INVCONV New Message
3038 fnd_msg_pub.add;
3039 RAISE fnd_api.g_exc_error;
3040 ELSIF p_secondary_uom <> inv_reservation_global.g_item_record_cache(p_item_cache_index).secondary_uom_code THEN
3041 fnd_message.set_name('INV', 'INV_INCORRECT_SECONDARY_UOM'); -- INVCONV New Message
3042 fnd_msg_pub.add;
3043 RAISE fnd_api.g_exc_error;
3044 ELSIF p_secondary_quantity IS NULL THEN
3045 fnd_message.set_name('INV', 'INV_SECONDARY_QTY_REQUIRED'); -- INVCONV New Message
3046 fnd_msg_pub.add;
3047 RAISE fnd_api.g_exc_error;
3048 END IF;
3049 -- Ensure that primary/secondary quantities honor the UOM conversion and deviations in place
3050
3051 /* IF the Reservation UOM and Secondary UOM are the same AND the Reservation qty and the Secondary Reservation
3052 Qty are the same and it's a fixed conversion item , there's no need to check deviation
3053 INVCONV Bug#3933849 */
3054 IF( (inv_reservation_global.g_item_record_cache(p_item_cache_index).secondary_default_ind = 'F')
3055 AND (p_reservation_quantity = p_secondary_quantity) AND (p_reservation_uom = p_secondary_uom)) THEN
3056 NULL;
3057 ELSE
3058 l_qtys_within_dev := INV_CONVERT.Within_Deviation
3059 ( p_organization_id =>
3060 inv_reservation_global.g_item_record_cache(p_item_cache_index).organization_id
3061 , p_inventory_item_id =>
3062 inv_reservation_global.g_item_record_cache(p_item_cache_index).inventory_item_id
3063 , p_lot_number => p_lot_number
3064 , p_precision => 5
3065 , p_quantity => p_primary_quantity
3066 , p_uom_code1 => p_primary_uom
3067 , p_quantity2 => p_secondary_quantity
3068 , p_uom_code2 => p_secondary_uom
3069 ) ;
3070
3071 IF (l_qtys_within_dev <> 1) THEN
3072 --fnd_message.set_name('INV', l_error_message);
3073 --fnd_msg_pub.add;
3074 RAISE fnd_api.g_exc_error;
3075 END IF;
3076 END IF; /* IF for Fixed item */
3077 END IF;
3078 -- INVCONV END
3079
3080
3081 x_return_status := l_return_status;
3082 --
3083 EXCEPTION
3084 WHEN fnd_api.g_exc_error THEN
3085 x_return_status := fnd_api.g_ret_sts_error;
3086 --
3087 WHEN OTHERS THEN
3088 x_return_status := fnd_api.g_ret_sts_unexp_error ;
3089 --
3090 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
3091 THEN
3092 fnd_msg_pub.add_exc_msg
3093 ( g_pkg_name
3094 , 'Validate_Quantity'
3095 );
3096 END IF;
3097 --
3098 END validate_quantity;
3099
3100
3101 --
3102 -- Procedure
3103 -- validate_sales_order
3104 -- Description
3105 -- is valid if all of the following are satisfied
3106 -- 1. sales order is open
3107 -- 2. p_reservation_item matches the item in the sales order
3108 -- 3. p_reservation_quantity is greater or equal to the
3109 -- reservable quantity = ordered quantity - already reserved qty
3110 -- Bug 1620576 - To support overpicking for pick wave move orders,
3111 -- we have to remove the restriction that reservation quantity cannot
3112 -- exceed sales order quantity
3113 -- {{ R12 Enhanced reservations code changes, add validation for sales order,
3114 -- for non-inventory supply types, if the sales order has not been booked,
3115 -- return error. }}
3116 PROCEDURE validate_sales_order
3117 (
3118 x_return_status OUT NOCOPY VARCHAR2
3119 , p_rsv_action_name IN VARCHAR2
3120 , p_reservation_id IN NUMBER
3121 , p_demand_type_id IN NUMBER
3122 , p_demand_header_id IN NUMBER
3123 , p_demand_line_id IN NUMBER
3124 , p_orig_demand_type_id IN NUMBER
3125 , p_orig_demand_header_id IN NUMBER
3126 , p_orig_demand_line_id IN NUMBER
3127 , p_reservation_quantity IN NUMBER
3128 , p_reservation_uom_code IN VARCHAR2
3129 , p_reservation_item_id IN NUMBER
3130 , p_reservation_org_id IN NUMBER
3131 , p_supply_type_id IN NUMBER /*** {{ R12 Enhanced reservations code changes }}***/
3132 , p_substitute_flag IN BOOLEAN DEFAULT FALSE /* Bug 6044651 */
3133 ) IS
3134
3135 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
3136
3137 l_org_id NUMBER;
3138
3139 l_line_rec_inventory_item_id oe_order_lines_all.inventory_item_id%TYPE;
3140 l_line_rec_ordered_quantity oe_order_lines_all.ordered_quantity%TYPE;
3141 l_line_rec_order_quantity_uom oe_order_lines_all.order_quantity_uom%TYPE;
3142 l_line_rec_org_id oe_order_lines_all.org_id%TYPE;
3143 l_line_rec_open_flag VARCHAR2(1);
3144
3145 l_ordered_quantity_rsv_uom NUMBER := 0;
3146 l_primary_uom_code VARCHAR2(3);
3147 l_primary_reserved_quantity NUMBER := 0;
3148 l_reserved_quantity NUMBER := 0;
3149 l_source_type_code VARCHAR2(30);
3150 l_flow_status_code VARCHAR2(30); --Bug 3118495
3151 l_booked_flag VARCHAR2(1) := 'N'; /*** {{ R12 Enhanced reservations code changes ***/
3152 l_debug NUMBER := g_debug;
3153 BEGIN
3154 -- Initialize return status
3155 x_return_status := fnd_api.g_ret_sts_success;
3156
3157 IF p_demand_type_id in (inv_reservation_global.g_source_type_oe,
3158 inv_reservation_global.g_source_type_internal_ord,
3159 inv_reservation_global.g_source_type_rma) THEN
3160
3161 -- Fetch row from oe_order_lines
3162
3163 /*l_org_id := OE_GLOBALS.G_ORG_ID;
3164 IF l_org_id IS NULL THEN
3165 OE_GLOBALS.Set_Context;
3166 l_org_id := OE_GLOBALS.G_ORG_ID;
3167 end if;*/
3168
3169 l_org_id := p_reservation_org_id;
3170
3171 SELECT inventory_item_id, ordered_quantity
3172 , order_quantity_uom, ship_from_org_id
3173 , open_flag, source_type_code,flow_status_code
3174 , booked_flag /*** {{ R12 Enhanced reservations code changes ***/
3175 INTO l_line_rec_inventory_item_id,
3176 l_line_rec_ordered_quantity,
3177 l_line_rec_order_quantity_uom,
3178 l_line_rec_org_id,
3179 l_line_rec_open_flag,
3180 l_source_type_code,
3181 l_flow_status_code,
3182 l_booked_flag
3183 FROM oe_order_lines_all
3184 WHERE line_id = p_demand_line_id ;
3185
3186 -- Bug 2366024 -- Do not perform the reservation check
3187 -- for drop ship orders - source_type_code = 'EXTERNAL'
3188
3189 -- Validate 1 -- the sales order has to be open
3190 IF (p_rsv_action_name = 'CREATE') OR
3191 ((p_rsv_action_name IN ('UPDATE','TRANSFER')) AND
3192 (Nvl(p_orig_demand_type_id,-99) <>
3193 Nvl(p_demand_type_id,-99)) OR
3194 (Nvl(p_orig_demand_header_id,-99) <>
3195 Nvl(p_demand_header_id,-99)) OR
3196 (Nvl(p_orig_demand_line_id,-99) <> Nvl(p_demand_line_id,-99))) THEN
3197 IF nvl(l_line_rec_open_flag, 'N') <> 'Y' AND Nvl(l_source_type_code, 'INTERNAL') <> 'EXTERNAL' THEN
3198 FND_MESSAGE.SET_NAME('INV', 'INV_RESERVATION_CLOSED_SO');
3199 FND_MSG_PUB.add;
3200 RAISE fnd_api.g_exc_error;
3201 END IF;
3202 END IF;
3203
3204 /* Bug 3118495 -- Should not allow user to create a reservation against a shipped sales order line */
3205 -- Validate 2 -- the sales order line should not be in 'SHIPPED' status
3206 IF l_flow_status_code = 'SHIPPED' THEN
3207 FND_MESSAGE.SET_NAME('INV', 'INV_RESERVATION_SHIPPED_SO');
3208 FND_MSG_PUB.add;
3209 RAISE fnd_api.g_exc_error;
3210 END IF;
3211 -- Validate 3 -- Item : The item on the reservation has to
3212 -- be the same as the item on the sales order line
3213 /* Bug 6044651 Do not perform this validation if substitue item is being used in a sales order */
3214 IF p_substitute_flag <> TRUE THEN
3215 IF p_reservation_org_id <> l_line_rec_org_id
3216 OR p_reservation_item_id <> l_line_rec_inventory_item_id THEN
3217
3218 FND_MESSAGE.SET_NAME('INV', 'INV_RESERVATION_INVALID_ITEM');
3219 FND_MSG_PUB.add;
3220 RAISE fnd_api.g_exc_error;
3221 END IF;
3222 END IF;
3223 /* End of Bug 6044651 */
3224
3225 -- /*** {{ R12 Enhanced reservations code changes ***/
3226 -- Validate 4 -- booked_flag: If the supply is not Inventory, sales
3227 -- order has to be booked.
3228 IF (nvl(l_booked_flag, 'N') <> 'Y' AND p_supply_type_id <> inv_reservation_global.g_source_type_inv) THEN
3229 FND_MESSAGE.SET_NAME('INV', 'INV_RSV_SO_NOT_BOOKED');
3230 FND_MSG_PUB.ADD;
3231 RAISE fnd_api.g_exc_error;
3232 END IF;
3233
3234
3235 -- Validate 5 -- if the demand type is sales order, call validate_demand_source_so
3236 -- to see if the sales order is dropship order.
3237 IF (p_demand_type_id = inv_reservation_global.g_source_type_oe) THEN
3238 validate_demand_source_so
3239 ( x_return_status => l_return_status
3240 , p_demand_source_type_id => p_demand_type_id
3241 , p_demand_source_header_id => p_demand_header_id
3242 , p_demand_source_line_id => p_demand_line_id
3243 , p_demand_source_line_detail => null
3244 );
3245
3246 IF l_debug = 1 THEN
3247 debug_print ('Inside validate sales order after calling validate so' || l_return_status);
3248 END IF;
3249
3250 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
3251 RAISE fnd_api.g_exc_error;
3252 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
3253 RAISE fnd_api.g_exc_unexpected_error;
3254 END IF;
3255
3256 END IF;
3257 /*** End R12 }} ***/
3258
3259 -- Validate 3: Reservation Qty
3260 -- Bug 1620576 - We can no longer carry out this validation.
3261 -- We allow over-reserving when we do an overpick.
3262 /*
3263 *-- Convert order quantity into reservation uom code
3264 *l_ordered_quantity_rsv_uom := inv_convert.inv_um_convert(
3265 * l_line_rec_inventory_item_id,
3266 * NULL,
3267 * l_line_rec_ordered_quantity,
3268 * l_line_rec_order_quantity_uom,
3269 * p_reservation_uom_code,
3270 * NULL,
3271 * NULL);
3272 *
3273 *
3274 *-- Fetch quantity reserved so far
3275 *SELECT nvl(sum(primary_reservation_quantity),0)
3276 *INTO l_primary_reserved_quantity
3277 *FROM mtl_reservations
3278 *WHERE demand_source_type_id = p_demand_type_id
3279 *AND demand_source_header_id = p_demand_header_id
3280 *AND demand_source_line_id = p_demand_line_id
3281 *AND reservation_id <> nvl(p_reservation_id,-1);
3282 *
3283 *IF l_primary_reserved_quantity > 0 then
3284 *
3285 * -- Get primary UOM
3286 * select primary_uom_code
3287 * into l_primary_uom_code
3288 * from mtl_system_items
3289 * where organization_id = l_line_rec_org_id
3290 * and inventory_item_id = l_line_rec_inventory_item_id;
3291 *
3292 * -- Convert primary reservation quantity into
3293 * -- reservation uom code
3294 * l_reserved_quantity :=
3295 * inv_convert.inv_um_convert
3296 * (
3297 * l_line_rec_inventory_item_id,
3298 * NULL,
3299 * l_primary_reserved_quantity,
3300 * l_primary_uom_code,
3301 * p_reservation_uom_code,
3302 * NULL,
3303 * NULL);
3304 *else
3305 * l_reserved_quantity := 0;
3306 *end if;
3307 **
3308 *-- Quantity that can be still reserved must be no less than the
3309 *-- reservation quantity--- can not over reserve
3310 **
3311 *IF (l_ordered_quantity_rsv_uom - l_reserved_quantity) <
3312 * p_reservation_quantity THEN
3313 * FND_MESSAGE.SET_NAME('INV','INV_RSV_ORDER_QTY_VALID');
3314 * FND_MSG_PUB.ADD;
3315 * RAISE fnd_api.g_exc_error;
3316 *END IF;
3317 */
3318
3319 IF (l_debug = 1) THEN
3320 debug_print ('Inside validate sales order after' || l_return_status);
3321 END IF;
3322
3323 END IF;
3324
3325 EXCEPTION
3326 WHEN fnd_api.g_exc_error THEN
3327 x_return_status := fnd_api.g_ret_sts_error;
3328 --
3329 WHEN OTHERS THEN
3330 x_return_status := fnd_api.g_ret_sts_unexp_error ;
3331 --
3332 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
3333 THEN
3334 fnd_msg_pub.add_exc_msg
3335 ( g_pkg_name
3336 , 'Validate_Sales_Order'
3337 );
3338 END IF;
3339 END validate_sales_order;
3340
3341 --
3342 -- Procedure
3343 -- validate_demand_source
3344 -- Description
3345 -- is valid if all of the following are satisfied
3346 -- 1. p_demand_source_type_id is not null
3347 -- 2. p_demand_source_header_id
3348 -- or p_demand_source_name is not null
3349 -- 3. if p_demand_source_type_id is inventory or the type id > 100
3350 -- (user defined source type), the p_demand_source_name is not null
3351 -- 4. if p_demand_source_type is account, account number is valid
3352 -- 5. if p_demand_source_type is account alias, alias is valid
3353 -- /*** {{ R12 Enhanced reservations code changes ***/
3354 -- 6. if p_demand_source_type is WIP, return errors if entity type
3355 -- is not CMRO, OPM Batch or OPM FPO
3356 -- /*** End R12 }} ***/
3357 PROCEDURE validate_demand_source
3358 (
3359 x_return_status OUT NOCOPY VARCHAR2
3360 , p_rsv_action_name IN VARCHAR2
3361 , p_inventory_item_id IN NUMBER
3362 , p_organization_id IN NUMBER
3363 , p_demand_source_type_id IN NUMBER
3364 , p_demand_source_header_id IN NUMBER
3365 , p_demand_source_line_id IN NUMBER
3366 , p_demand_source_line_detail IN NUMBER
3367 , p_orig_demand_source_type_id IN NUMBER
3368 , p_orig_demand_source_header_id IN NUMBER
3369 , p_orig_demand_source_line_id IN NUMBER
3370 , p_orig_demand_source_detail IN NUMBER
3371 , p_demand_source_name IN VARCHAR2
3372 , p_reservation_id IN NUMBER
3373 , p_reservation_quantity IN NUMBER
3374 , p_reservation_uom_code IN VARCHAR2
3375 , p_supply_type_id IN NUMBER
3376 , p_demand_ship_date IN DATE
3377 , p_supply_receipt_date IN DATE
3378 , x_demand_cache_index OUT NOCOPY INTEGER
3379 , p_substitute_flag IN BOOLEAN DEFAULT FALSE /* Bug 6044651 */
3380 ) IS
3381 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
3382 l_structure_num NUMBER := NULL;
3383 l_index NUMBER := NULL;
3384 l_is_valid NUMBER := NULL;
3385 l_rec inv_reservation_global.demand_record;
3386 /*** {{ R12 Enhanced reservations code changes ***/
3387 l_debug NUMBER;
3388 l_msg_count NUMBER;
3389 l_msg_data VARCHAR2(1000);
3390 l_wip_entity_type NUMBER;
3391 l_wip_job_type VARCHAR2(15);
3392 /*** End R12 }} ***/
3393 BEGIN
3394 --
3395 /*** {{ R12 Enhanced reservations code changes ***/
3396 IF (g_debug IS NULL) THEN
3397 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3398 END IF;
3399
3400 l_debug := g_debug;
3401
3402 IF (l_debug = 1) THEN
3403 debug_print('In validate_demand_source: ' ||
3404 ', rsv_action_name = ' || p_rsv_action_name ||
3405 ', inventory_item_id = ' || p_inventory_item_id ||
3406 ', organization_id = ' || p_organization_id ||
3407 ', demand_source_type_id = ' || p_demand_source_type_id ||
3408 ', demand_source_header_id = ' || p_demand_source_header_id ||
3409 ', demand_source_line_id = ' || p_demand_source_line_id ||
3410 ', demand_source_detail = ' || p_demand_source_line_detail ||
3411 ', orig_demand_source_type_id = ' || p_orig_demand_source_type_id ||
3412 ', orig_demand_source_header_id = ' || p_orig_demand_source_header_id ||
3413 ', orig_demand_source_line_id = ' || p_orig_demand_source_line_id ||
3414 ', orig_demand_source_detail = ' || p_orig_demand_source_detail ||
3415 ', demand_source_name = ' || p_demand_source_name ||
3416 ', reservation_id = ' || p_reservation_id ||
3417 ', reservation_quantity = ' || p_reservation_quantity ||
3418 ', reservation_uom_code = ' || p_reservation_uom_code ||
3419 ', supply_type_id = ' || p_supply_type_id ||
3420 ', demand_ship_date = ' || p_demand_ship_date ||
3421 ', supply_receipt_date = ' || p_supply_receipt_date);
3422 END IF;
3423 /*** End R12 }} ***/
3424
3425 IF p_demand_source_type_id IS NULL
3426 OR p_demand_source_header_id IS NULL
3427 AND p_demand_source_name IS NULL THEN
3428 fnd_message.set_name('INV', 'MISSING DEMAND SOURCE');
3429 fnd_msg_pub.add;
3430 END IF;
3431 --
3432 -- Bug 6124188 Added Demand Source of PO for which reservation gets created
3433 -- in case Return is attemped with profile 'WMS:Express Return' as 'No'
3434
3435 /*** {{ R12 Enhanced reservations code changes ***/
3436 IF (p_demand_source_type_id NOT IN
3437 (inv_reservation_global.g_source_type_inv,inv_reservation_global.g_source_type_po,
3438 inv_reservation_global.g_source_type_oe, inv_reservation_global.g_source_type_account,
3439 inv_reservation_global.g_source_type_account_alias,
3440 inv_reservation_global.g_source_type_cycle_count, inv_reservation_global.g_source_type_physical_inv,
3441 inv_reservation_global.g_source_type_internal_ord,
3442 inv_reservation_global.g_source_type_rma, inv_reservation_global.g_source_type_wip)
3443 AND NOT(p_demand_source_type_id >100)) THEN
3444 fnd_message.set_name('INV','INV_INVALID_DEMAND_SOURCE');
3445 fnd_msg_pub.add;
3446 RAISE fnd_api.g_exc_error;
3447 END IF;
3448 /*** End R12 }} ***/
3449
3450 -- if the demand source type is inventory, or type id > 100
3451 -- the source name should not be null
3452 IF p_demand_source_type_id = inv_reservation_global.g_source_type_inv
3453 OR p_demand_source_type_id > 100 THEN
3454 if p_demand_source_name IS NULL THEN
3455 fnd_message.set_name('INV','INV_INVALID_DEMAND_SOURCE');
3456 fnd_msg_pub.add;
3457 RAISE fnd_api.g_exc_error;
3458 END IF;
3459 END IF;
3460 --
3461 -- search for the demand source in the cache first
3462 inv_reservation_util_pvt.search_demand_cache
3463 (
3464 x_return_status => x_return_status
3465 , p_demand_source_type_id => p_demand_source_type_id
3466 , p_demand_source_header_id => p_demand_source_header_id
3467 , p_demand_source_line_id => p_demand_source_line_id
3468 , p_demand_source_name => p_demand_source_name
3469 , x_index => l_index
3470 );
3471 --
3472 IF l_return_status = fnd_api.g_ret_sts_error THEN
3473 RAISE fnd_api.g_exc_error;
3474 END IF ;
3475 --
3476 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3477 RAISE fnd_api.g_exc_unexpected_error;
3478 END IF;
3479 --
3480 -- I would just put valid demand source in the cache for
3481 -- now. so I do not need to check is_valid
3482 -- if the source is already in the cache, return successful
3483 IF l_index IS NOT NULL THEN
3484 x_demand_cache_index := l_index;
3485 x_return_status := l_return_status;
3486 RETURN;
3487 END IF;
3488 --
3489 -- not in cache goes here
3490 -- if the source type is account, demand header id should not
3491 -- be null, and it should be a valid GL account number
3492 IF p_demand_source_type_id = inv_reservation_global.g_source_type_account
3493 THEN
3494 IF p_demand_source_header_id IS NOT NULL THEN
3495 BEGIN
3496 -- find the flex field structure number
3497 SELECT
3498 id_flex_num
3499 INTO l_structure_num
3500 FROM
3501 org_organization_definitions ood
3502 , fnd_id_flex_structures ffs
3503 WHERE
3504 ood.organization_id = p_organization_id
3505 AND ffs.id_flex_code = 'GL#'
3506 AND ood.chart_of_accounts_id = ffs.id_flex_num;
3507
3508 -- call fnd api to validate the account id
3509 IF NOT fnd_flex_keyval.validate_ccid
3510 (
3511 'SQLGL'
3512 , 'GL#'
3513 , l_structure_num
3514 , p_demand_source_header_id
3515 ) THEN
3516 fnd_message.set_name('INV', 'INVALID_ACCOUNT_NUMBER');
3517 fnd_msg_pub.add;
3518 RAISE fnd_api.g_exc_error;
3519 END IF;
3520 EXCEPTION
3521 WHEN no_data_found THEN
3522 fnd_message.set_name('INV', 'INVALID_ACCOUNT_NUMBER');
3523 fnd_msg_pub.add;
3524 RAISE fnd_api.g_exc_error;
3525 END;
3526 --
3527 ELSE
3528 fnd_message.set_name('INV', 'INVALID_ACCOUNT_NUMBER');
3529 fnd_msg_pub.add;
3530 RAISE fnd_api.g_exc_error;
3531 END IF;
3532 END IF;
3533 --
3534 IF p_demand_source_type_id
3535 = inv_reservation_global.g_source_type_account_alias
3536 THEN
3537 IF p_demand_source_header_id IS NOT NULL THEN
3538 IF NOT fnd_flex_keyval.validate_ccid
3539 (
3540 appl_short_name => 'INV'
3541 , key_flex_code => 'MDSP'
3542 , structure_number => 101
3543 , combination_id => p_demand_source_header_id
3544 , data_set => p_organization_id
3545 ) THEN
3546 fnd_message.set_name('INV', 'INVALID_ACCOUNT_ALIAS');
3547 fnd_msg_pub.add;
3548 RAISE fnd_api.g_exc_error;
3549 END IF;
3550 END IF;
3551 END IF;
3552
3553 /*** {{ R12 Enhanced reservations code changes ***/
3554 IF (p_demand_source_type_id = inv_reservation_global.g_source_type_wip) THEN
3555 -- get wip entity id from wip_record_cache
3556 inv_reservation_util_pvt.get_wip_cache
3557 (
3558 x_return_status => l_return_status
3559 , p_wip_entity_id => p_demand_source_header_id
3560 );
3561
3562 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
3563 RAISE fnd_api.g_exc_error;
3564 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
3565 RAISE fnd_api.g_exc_unexpected_error;
3566 ELSE
3567 l_wip_entity_type := inv_reservation_global.g_wip_record_cache(p_demand_source_header_id).wip_entity_type;
3568 l_wip_job_type := inv_reservation_global.g_wip_record_cache(p_demand_source_header_id).wip_entity_job;
3569 END IF;
3570
3571 IF (l_wip_entity_type = inv_reservation_global.g_wip_source_type_cmro) THEN
3572 validate_demand_source_cmro(
3573 x_return_status => l_return_status
3574 , p_organization_id => p_organization_id
3575 , p_inventory_item_id => p_inventory_item_id
3576 , p_demand_ship_date => p_demand_ship_date
3577 , p_supply_receipt_date => p_supply_receipt_date
3578 , p_demand_source_type_id => p_demand_source_type_id
3579 , p_demand_source_header_id => p_demand_source_header_id
3580 , p_demand_source_line_id => p_demand_source_line_id
3581 , p_demand_source_line_detail => p_demand_source_line_detail
3582 , p_wip_entity_type => l_wip_entity_type
3583 );
3584
3585 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
3586 RAISE fnd_api.g_exc_error;
3587 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
3588 RAISE fnd_api.g_exc_unexpected_error;
3589 END IF;
3590 ELSIF (l_wip_entity_type = inv_reservation_global.g_wip_source_type_fpo) THEN
3591 validate_demand_source_fpo(
3592 x_return_status => l_return_status
3593 , p_organization_id => p_organization_id
3594 , p_inventory_item_id => p_inventory_item_id
3595 , p_demand_ship_date => p_demand_ship_date
3596 , p_supply_receipt_date => p_supply_receipt_date
3597 , p_demand_source_type_id => p_demand_source_type_id
3598 , p_demand_source_header_id => p_demand_source_header_id
3599 , p_demand_source_line_id => p_demand_source_line_id
3600 , p_demand_source_line_detail => null
3601 , p_wip_entity_type => l_wip_entity_type
3602 );
3603
3604 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
3605 RAISE fnd_api.g_exc_error;
3606 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
3607 RAISE fnd_api.g_exc_unexpected_error;
3608 END IF;
3609 ELSIF (l_wip_entity_type = inv_reservation_global.g_wip_source_type_batch) THEN
3610 validate_demand_source_batch(
3611 x_return_status => l_return_status
3612 , p_organization_id => p_organization_id
3613 , p_inventory_item_id => p_inventory_item_id
3614 , p_demand_ship_date => p_demand_ship_date
3615 , p_supply_receipt_date => p_supply_receipt_date
3616 , p_demand_source_type_id => p_demand_source_type_id
3617 , p_demand_source_header_id => p_demand_source_header_id
3618 , p_demand_source_line_id => p_demand_source_line_id
3619 , p_demand_source_line_detail => null
3620 , p_wip_entity_type => l_wip_entity_type
3621 );
3622
3623 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
3624 RAISE fnd_api.g_exc_error;
3625 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
3626 RAISE fnd_api.g_exc_unexpected_error;
3627 END IF;
3628 END IF;
3629 END IF;
3630
3631 IF (p_demand_source_type_id IN (
3632 inv_reservation_global.g_source_type_oe
3633 , inv_reservation_global.g_source_type_internal_ord
3634 , inv_reservation_global.g_source_type_rma)
3635 ) THEN
3636 --Bug #5202033
3637 --If action is UPDATE/TRANSFER and the demand source info has not changed,
3638 --do not call validate_sales_order
3639 IF ( (p_rsv_action_name = 'CREATE')
3640 OR
3641 ( (p_rsv_action_name IN ('UPATE', 'TRANSFER')) AND
3642 ( (p_orig_demand_source_type_id <> p_demand_source_type_id) OR
3643 (p_orig_demand_source_header_id <> p_demand_source_header_id) OR
3644 (p_orig_demand_source_line_id <> p_demand_source_line_id)
3645 )
3646 )
3647 ) THEN
3648 validate_sales_order(
3649 x_return_status => l_return_status
3650 , p_rsv_action_name => p_rsv_action_name
3651 , p_reservation_id => p_reservation_id
3652 , p_demand_type_id => p_demand_source_type_id
3653 , p_demand_header_id => p_demand_source_header_id
3654 , p_demand_line_id => p_demand_source_line_id
3655 , p_orig_demand_type_id => p_orig_demand_source_type_id
3656 , p_orig_demand_header_id => p_orig_demand_source_header_id
3657 , p_orig_demand_line_id => p_orig_demand_source_line_id
3658 , p_reservation_quantity => p_reservation_quantity
3659 , p_reservation_uom_code => p_reservation_uom_code
3660 , p_reservation_item_id => p_inventory_item_id
3661 , p_reservation_org_id => p_organization_id
3662 , p_supply_type_id => p_supply_type_id
3663 , p_substitute_flag => p_substitute_flag); /* Bug 6044651 */
3664
3665
3666 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
3667 RAISE fnd_api.g_exc_error;
3668 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
3669 RAISE fnd_api.g_exc_unexpected_error;
3670 END IF;
3671 END IF; --END IF check p_rsv_action
3672 END IF; --END IF demand source in SO, Internal Order, RMA
3673
3674 IF (l_debug = 1) THEN
3675 debug_print ('After calling validate sales order from within demand source' ||
3676 l_return_status);
3677 END IF;
3678
3679 /*** End R12 }} ***/
3680
3681 --
3682 -- comment out lines below until R11.8 when more demand sources
3683 -- will be considered
3684 -- IF p_demand_source_type_id
3685 -- = inv_reservation_global.g_source_type_wip THEN
3686 -- call wip_validation api()
3687 -- IF p_demand_source_type_id
3688 -- = inv_reservation_global.g_source_type_oe THEN
3689 -- call oe_validation api()
3690 --
3691 l_rec.demand_source_type_id := p_demand_source_type_id;
3692 l_rec.demand_source_header_id := p_demand_source_header_id;
3693 l_rec.demand_source_line_id := p_demand_source_line_id;
3694 l_rec.demand_source_name := p_demand_source_name;
3695 l_rec.is_valid := 1; -- 1 = true
3696 --
3697 inv_reservation_util_pvt.add_demand_cache
3698 (
3699 x_return_status => l_return_status
3700 , p_demand_record => l_rec
3701 , x_index => l_index
3702 );
3703 --
3704 IF l_return_status = fnd_api.g_ret_sts_error THEN
3705 RAISE fnd_api.g_exc_error;
3706 END IF ;
3707 --
3708 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3709 RAISE fnd_api.g_exc_unexpected_error;
3710 END IF;
3711 --
3712 x_demand_cache_index := l_index;
3713 x_return_status := l_return_status;
3714 --
3715 EXCEPTION
3716 WHEN fnd_api.g_exc_error THEN
3717 x_return_status := fnd_api.g_ret_sts_error;
3718 --
3719 WHEN fnd_api.g_exc_unexpected_error THEN
3720 x_return_status := fnd_api.g_ret_sts_unexp_error ;
3721 --
3722 WHEN OTHERS THEN
3723 x_return_status := fnd_api.g_ret_sts_unexp_error ;
3724 --
3725 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
3726 THEN
3727 fnd_msg_pub.add_exc_msg
3728 ( g_pkg_name
3729 , 'Validate_Demand_Source'
3730 );
3731 END IF;
3732 --
3733 END validate_demand_source;
3734
3735 /*** {{ R12 Enhanced reservations code changes ***/
3736 -- Procedure
3737 -- create_crossdock_reservation
3738 -- Description
3739 -- This procedure validates reservations that are crossdocked and
3740 -- indicates whether the intended action can be performed on that
3741 -- reservation record. This is called when a reservation is being
3742 -- created.
3743
3744 PROCEDURE create_crossdock_reservation
3745 (
3746 x_return_status OUT NOCOPY VARCHAR2
3747 , x_msg_count OUT NOCOPY NUMBER
3748 , x_msg_data OUT NOCOPY VARCHAR2
3749 , p_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
3750 ) IS
3751 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
3752 l_msg_count NUMBER;
3753 l_msg_data VARCHAR2(1000);
3754 l_debug NUMBER;
3755
3756 BEGIN
3757
3758 IF (g_debug IS NULL) THEN
3759 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3760 END IF;
3761
3762 l_debug := g_debug;
3763
3764 IF (l_debug = 1) THEN
3765 debug_print('In create_crossdock_reservation');
3766 debug_print('crossdock_criteria_id = ' || p_rsv_rec.crossdock_criteria_id);
3767 END IF;
3768
3769 IF ((p_rsv_rec.crossdock_criteria_id is not null) and
3770 (p_rsv_rec.crossdock_criteria_id <> fnd_api.g_miss_num)) THEN
3771 wms_xdock_utils_pvt.create_crossdock_reservation(
3772 x_return_status => l_return_status
3773 , p_rsv_rec => p_rsv_rec
3774 );
3775
3776 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
3777 IF (l_debug = 1) THEN
3778 debug_print('create_crossdock_reservation returns error');
3779 END IF;
3780 raise fnd_api.g_exc_error;
3781 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
3782 IF (l_debug = 1) THEN
3783 debug_print('create_crossdock_reservation returns unexpected error');
3784 END IF;
3785 raise fnd_api.g_exc_unexpected_error;
3786 END IF;
3787 END IF;
3788
3789 x_return_status := l_return_status;
3790
3791 EXCEPTION
3792 WHEN fnd_api.g_exc_error THEN
3793 x_return_status := fnd_api.g_ret_sts_error;
3794 --
3795 WHEN fnd_api.g_exc_unexpected_error THEN
3796 x_return_status := fnd_api.g_ret_sts_unexp_error ;
3797 --
3798 WHEN OTHERS THEN
3799 x_return_status := fnd_api.g_ret_sts_unexp_error ;
3800 --
3801 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
3802 THEN
3803 fnd_msg_pub.add_exc_msg
3804 ( g_pkg_name
3805 , 'create_crossdock_reservation'
3806 );
3807 END IF;
3808 --
3809 END create_crossdock_reservation;
3810 /*** End R12 }} ***/
3811
3812
3813 /*** {{ R12 Enhanced reservations code changes ***/
3814 -- Procedure
3815 -- update_crossdock_reservation
3816 -- Description
3817 -- This procedure validates reservations that are crossdocked and
3818 -- indicates whether the intended action can be performed on that
3819 -- reservation record. This is called when a reservation is being
3820 -- updated.
3821
3822 PROCEDURE update_crossdock_reservation
3823 (
3824 x_return_status OUT NOCOPY VARCHAR2
3825 , x_msg_count OUT NOCOPY NUMBER
3826 , x_msg_data OUT NOCOPY VARCHAR2
3827 , p_orig_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
3828 , p_to_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
3829 ) IS
3830 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
3831 l_msg_count NUMBER;
3832 l_msg_data VARCHAR2(1000);
3833 l_debug NUMBER;
3834
3835 BEGIN
3836
3837 IF (g_debug IS NULL) THEN
3838 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3839 END IF;
3840
3841 l_debug := g_debug;
3842
3843 IF (l_debug = 1) THEN
3844 debug_print('In update_crossdock_reservation');
3845 debug_print('crossdock_criteria_id = ' || p_to_rsv_rec.crossdock_criteria_id);
3846 END IF;
3847
3848 IF ((p_to_rsv_rec.crossdock_criteria_id is not null) and
3849 (p_to_rsv_rec.crossdock_criteria_id <> fnd_api.g_miss_num)) THEN
3850 wms_xdock_utils_pvt.update_crossdock_reservation(
3851 x_return_status => l_return_status
3852 , p_orig_rsv_rec => p_orig_rsv_rec
3853 , p_new_rsv_rec => p_to_rsv_rec
3854 );
3855
3856 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
3857 IF (l_debug = 1) THEN
3858 debug_print('update_crossdock_reservation returns error');
3859 END IF;
3860 raise fnd_api.g_exc_error;
3861 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
3862 IF (l_debug = 1) THEN
3863 debug_print('update_crossdock_reservation returns unexpected error');
3864 END IF;
3865 raise fnd_api.g_exc_unexpected_error;
3866 END IF;
3867 END IF;
3868
3869 x_return_status := l_return_status;
3870
3871 EXCEPTION
3872 WHEN fnd_api.g_exc_error THEN
3873 x_return_status := fnd_api.g_ret_sts_error;
3874 --
3875 WHEN fnd_api.g_exc_unexpected_error THEN
3876 x_return_status := fnd_api.g_ret_sts_unexp_error ;
3877 --
3878 WHEN OTHERS THEN
3879 x_return_status := fnd_api.g_ret_sts_unexp_error ;
3880 --
3881 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
3882 THEN
3883 fnd_msg_pub.add_exc_msg
3884 ( g_pkg_name
3885 , 'update_crossdock_reservation'
3886 );
3887 END IF;
3888 --
3889 END update_crossdock_reservation;
3890 /*** End R12 }} ***/
3891
3892 /*** {{ R12 Enhanced reservations code changes ***/
3893 -- Procedure
3894 -- transfer_crossdock_reservation
3895 -- Description
3896 -- This procedure validates reservations that are crossdocked and
3897 -- indicates whether the intended action can be performed on that
3898 -- reservation record. This is called when a reservation is being
3899 -- transferred.
3900
3901 PROCEDURE transfer_crossdock_reservation
3902 (
3903 x_return_status OUT NOCOPY VARCHAR2
3904 , x_msg_count OUT NOCOPY NUMBER
3905 , x_msg_data OUT NOCOPY VARCHAR2
3906 , p_orig_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
3907 , p_to_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
3908 ) IS
3909 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
3910 l_msg_count NUMBER;
3911 l_msg_data VARCHAR2(1000);
3912 l_debug NUMBER;
3913
3914 BEGIN
3915
3916 IF (g_debug IS NULL) THEN
3917 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3918 END IF;
3919
3920 l_debug := g_debug;
3921
3922 IF (l_debug = 1) THEN
3923 debug_print('In transfer_crossdock_reservation');
3924 debug_print('crossdock_criteria_id = ' || p_to_rsv_rec.crossdock_criteria_id);
3925 END IF;
3926
3927 IF ((p_to_rsv_rec.crossdock_criteria_id is not null) and
3928 (p_to_rsv_rec.crossdock_criteria_id <> fnd_api.g_miss_num)) THEN
3929 wms_xdock_utils_pvt.transfer_crossdock_reservation(
3930 x_return_status => l_return_status
3931 , p_orig_rsv_rec => p_orig_rsv_rec
3932 , p_new_rsv_rec => p_to_rsv_rec
3933 );
3934 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
3935 IF (l_debug = 1) THEN
3936 debug_print('transfer_crossdock_reservation returns error');
3937 END IF;
3938 raise fnd_api.g_exc_error;
3939 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
3940 IF (l_debug = 1) THEN
3941 debug_print('transfer_crossdock_reservation returns unexpected error');
3942 END IF;
3943 raise fnd_api.g_exc_unexpected_error;
3944 END IF;
3945 END IF;
3946
3947 x_return_status := l_return_status;
3948
3949 EXCEPTION
3950 WHEN fnd_api.g_exc_error THEN
3951 x_return_status := fnd_api.g_ret_sts_error;
3952 --
3953 WHEN fnd_api.g_exc_unexpected_error THEN
3954 x_return_status := fnd_api.g_ret_sts_unexp_error ;
3955 --
3956 WHEN OTHERS THEN
3957 x_return_status := fnd_api.g_ret_sts_unexp_error ;
3958 --
3959 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
3960 THEN
3961 fnd_msg_pub.add_exc_msg
3962 ( g_pkg_name
3963 , 'transfer_crossdock_reservation'
3964 );
3965 END IF;
3966 --
3967 END transfer_crossdock_reservation;
3968 /*** End R12 }} ***/
3969
3970 /*** {{ R12 Enhanced reservations code changes ***/
3971 -- Procedure
3972 -- relieve_crossdock_reservation
3973 -- Description
3974 -- This procedure validates reservations that are crossdocked and
3975 -- indicates whether the intended action can be performed on that
3976 -- reservation record. This is called when a reservation is being
3977 -- relieved.
3978
3979 PROCEDURE relieve_crossdock_reservation
3980 (
3981 x_return_status OUT NOCOPY VARCHAR2
3982 , x_msg_count OUT NOCOPY NUMBER
3983 , x_msg_data OUT NOCOPY VARCHAR2
3984 , p_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
3985 ) IS
3986 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
3987 l_msg_count NUMBER;
3988 l_msg_data VARCHAR2(1000);
3989 l_debug NUMBER;
3990
3991 BEGIN
3992
3993 IF (g_debug IS NULL) THEN
3994 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3995 END IF;
3996
3997 l_debug := g_debug;
3998
3999 IF (l_debug = 1) THEN
4000 debug_print('In relieve_crossdock_reservation');
4001 debug_print('crossdock_criteria_id = ' || p_rsv_rec.crossdock_criteria_id);
4002 END IF;
4003
4004 IF ((p_rsv_rec.crossdock_criteria_id is not null) and
4005 (p_rsv_rec.crossdock_criteria_id <> fnd_api.g_miss_num)) THEN
4006 wms_xdock_utils_pvt.relieve_crossdock_reservation(
4007 x_return_status => l_return_status
4008 , p_rsv_rec => p_rsv_rec
4009 );
4010
4011 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
4012 IF (l_debug = 1) THEN
4013 debug_print('relieve_crossdock_reservation returns error');
4014 END IF;
4015 raise fnd_api.g_exc_error;
4016 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
4017 IF (l_debug = 1) THEN
4018 debug_print('relieve_crossdock_reservation returns unexpected error');
4019 END IF;
4020 raise fnd_api.g_exc_unexpected_error;
4021 END IF;
4022 END IF;
4023
4024 x_return_status := l_return_status;
4025
4026 EXCEPTION
4027 WHEN fnd_api.g_exc_error THEN
4028 x_return_status := fnd_api.g_ret_sts_error;
4029 --
4030 WHEN fnd_api.g_exc_unexpected_error THEN
4031 x_return_status := fnd_api.g_ret_sts_unexp_error ;
4032 --
4033 WHEN OTHERS THEN
4034 x_return_status := fnd_api.g_ret_sts_unexp_error ;
4035 --
4036 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
4037 THEN
4038 fnd_msg_pub.add_exc_msg
4039 ( g_pkg_name
4040 , 'relieve_crossdock_reservation'
4041 );
4042 END IF;
4043 --
4044 END relieve_crossdock_reservation;
4045
4046 /*** {{ R12 Enhanced reservations code changes ***/
4047 -- Procedure
4048 -- delete_crossdock_reservation
4049 -- Description
4050 -- This procedure validates reservations that are crossdocked and
4051 -- indicates whether the intended action can be performed on that
4052 -- reservation record. This is called when a reservation is being
4053 -- deleted.
4054
4055 PROCEDURE delete_crossdock_reservation
4056 (
4057 x_return_status OUT NOCOPY VARCHAR2
4058 , x_msg_count OUT NOCOPY NUMBER
4059 , x_msg_data OUT NOCOPY VARCHAR2
4060 , p_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
4061 ) IS
4062 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
4063 l_msg_count NUMBER;
4064 l_msg_data VARCHAR2(1000);
4065 l_debug NUMBER;
4066
4067 BEGIN
4068
4069 IF (g_debug IS NULL) THEN
4070 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4071 END IF;
4072
4073 l_debug := g_debug;
4074
4075 IF (l_debug = 1) THEN
4076 debug_print('In delete_crossdock_reservation');
4077 debug_print('crossdock_criteria_id = ' || p_rsv_rec.crossdock_criteria_id);
4078 END IF;
4079
4080 IF ((p_rsv_rec.crossdock_criteria_id is not null) and
4081 (p_rsv_rec.crossdock_criteria_id <> fnd_api.g_miss_num)) THEN
4082 wms_xdock_utils_pvt.delete_crossdock_reservation(
4083 x_return_status => l_return_status
4084 , p_rsv_rec => p_rsv_rec
4085 );
4086
4087 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
4088 IF (l_debug = 1) THEN
4089 debug_print('delete_crossdock_reservation returns error');
4090 END IF;
4091 raise fnd_api.g_exc_error;
4092 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
4093 IF (l_debug = 1) THEN
4094 debug_print('delete_crossdock_reservation returns unexpected error');
4095 END IF;
4096 raise fnd_api.g_exc_unexpected_error;
4097 END IF;
4098 END IF;
4099
4100 x_return_status := l_return_status;
4101
4102 EXCEPTION
4103 WHEN fnd_api.g_exc_error THEN
4104 x_return_status := fnd_api.g_ret_sts_error;
4105 --
4106 WHEN fnd_api.g_exc_unexpected_error THEN
4107 x_return_status := fnd_api.g_ret_sts_unexp_error ;
4108 --
4109 WHEN OTHERS THEN
4110 x_return_status := fnd_api.g_ret_sts_unexp_error ;
4111 --
4112 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
4113 THEN
4114 fnd_msg_pub.add_exc_msg
4115 ( g_pkg_name
4116 , 'delete_crossdock_reservation'
4117 );
4118 END IF;
4119 --
4120 END delete_crossdock_reservation;
4121 /*** End R12 }} ***/
4122
4123 /*** {{ R12 Enhanced reservations code changes ***/
4124 -- Procedure
4125 -- validate_pjm_reservations
4126 -- Description
4127 -- This procedure validates reservation in PJM organization.
4128
4129 PROCEDURE validate_pjm_reservations
4130 (
4131 x_return_status OUT NOCOPY VARCHAR2
4132 , p_organization_id IN NUMBER
4133 , p_inventory_item_id IN NUMBER
4134 , p_supply_source_type_id IN NUMBER
4135 , p_supply_source_header_id IN NUMBER
4136 , p_supply_source_line_id IN NUMBER
4137 , p_supply_source_line_detail IN NUMBER
4138 , p_project_id IN NUMBER
4139 , p_task_id IN NUMBER
4140 ) IS
4141 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
4142 l_msg_count NUMBER;
4143 l_msg_data VARCHAR2(1000);
4144 l_wms_enabled VARCHAR2(1) := 'N';
4145 l_pjm_enabled NUMBER := 1;
4146 l_project_count NUMBER;
4147 l_project_id NUMBER;
4148 l_task_id NUMBER;
4149 l_wip_entity_type NUMBER;
4150 l_wip_job_type VARCHAR2(15);
4151 l_debug NUMBER;
4152 p_mtl_maintain_rsv_rec inv_reservation_global.mtl_maintain_rsv_rec_type;
4153 l_delete_flag VARCHAR2(1) := 'N';
4154 l_sort_by_criteria Number;
4155 l_qty_modified NUMBER := 0;
4156 BEGIN
4157
4158 IF (g_debug IS NULL) THEN
4159 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4160 END IF;
4161
4162 l_debug := g_debug;
4163
4164 IF (l_debug = 1) THEN
4165 debug_print('In validate_pjm_reservations');
4166 debug_print('organization_id = ' || p_organization_id || ' , supply type = ' || p_supply_source_type_id);
4167 END IF;
4168
4169 SELECT wms_enabled_flag, project_reference_enabled
4170 INTO l_wms_enabled, l_pjm_enabled
4171 FROM mtl_parameters
4172 WHERE organization_id = p_organization_id;
4173
4174 IF (l_pjm_enabled = 1 and l_wms_enabled = 'Y') THEN
4175 IF (p_supply_source_type_id = inv_reservation_global.g_source_type_intransit) THEN
4176 IF (l_debug = 1) THEN
4177 debug_print('Reservation of intransit shipment supply cannot be created in PJM and WMS organization');
4178 END IF;
4179
4180 fnd_message.set_name('INV', 'INV_RSV_PJM_WMS_INTRAN');
4181 fnd_msg_pub.ADD;
4182
4183 RAISE fnd_api.g_exc_error;
4184 ELSIF (p_supply_source_type_id = inv_reservation_global.g_source_type_po OR
4185 p_supply_source_type_id = inv_reservation_global.g_source_type_asn) THEN
4186
4187 SELECT count(min(po_distribution_id))
4188 INTO l_project_count
4189 FROM po_distributions_all
4190 WHERE po_header_id = p_supply_source_header_id
4191 AND line_location_id = p_supply_source_line_id
4192 group by project_id, task_id;
4193
4194 IF (l_project_count > 1) THEN
4195 IF (l_debug = 1) THEN
4196 debug_print('Multiple project and task combinations exists for the supply line');
4197 debug_print('We need to delete the reservations for this supply');
4198 END IF;
4199 -- Call the reduce reservations API by setting the
4200 -- delete_flag to yes. delete all reservations for that
4201 -- supply line.
4202 l_delete_flag := 'Y';
4203 l_sort_by_criteria := inv_reservation_global.g_query_demand_ship_date_desc;
4204 p_mtl_maintain_rsv_rec.organization_id := p_organization_id;
4205 p_mtl_maintain_rsv_rec.inventory_item_id := p_inventory_item_id;
4206 p_mtl_maintain_rsv_rec.supply_source_type_id := p_supply_source_type_id;
4207 p_mtl_maintain_rsv_rec.supply_source_header_id := p_supply_source_header_id;
4208 p_mtl_maintain_rsv_rec.supply_source_line_id := p_supply_source_line_id;
4209
4210 inv_maintain_reservation_pub.reduce_reservation
4211 (p_api_version_number => 1.0,
4212 p_init_msg_lst => fnd_api.g_false,
4213 x_return_status => l_return_status,
4214 x_msg_count => l_msg_count,
4215 x_msg_data => l_msg_data,
4216 p_mtl_maintain_rsv_rec => p_mtl_maintain_rsv_rec,
4217 p_delete_flag => l_delete_flag,
4218 p_sort_by_criteria => l_sort_by_criteria,
4219 x_quantity_modified => l_qty_modified
4220 );
4221
4222 IF l_debug=1 THEN
4223 debug_print ('Return Status after calling reduce reservations: '|| l_return_status);
4224 END IF;
4225
4226 IF l_return_status = fnd_api.g_ret_sts_error THEN
4227
4228 IF l_debug=1 THEN
4229 debug_print('Raising expected error'||l_return_status);
4230 END IF;
4231 RAISE fnd_api.g_exc_error;
4232
4233 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
4234
4235 IF l_debug=1 THEN
4236 debug_print('Rasing Unexpected error'||l_return_status);
4237 END IF;
4238 RAISE fnd_api.g_exc_unexpected_error;
4239
4240 END IF;
4241 END IF; -- project count > 1
4242
4243 IF ((p_project_id is not null) AND (l_project_count = 1)) THEN
4244 SELECT MIN(project_id), MIN(task_id)
4245 INTO l_project_id, l_task_id
4246 FROM po_distributions_all
4247 WHERE po_header_id = p_supply_source_header_id
4248 AND line_location_id = p_supply_source_line_id;
4249
4250 IF (l_project_id <> p_project_id or l_task_id <> p_task_id) THEN
4251 IF (l_debug = 1) THEN
4252 debug_print('The project and task of reservation record does not match with the supply line');
4253 END IF;
4254
4255 IF (l_debug = 1) THEN
4256 debug_print('Multiple project and task combinations exists for the supply line');
4257 debug_print('We need to delete the reservations for this supply');
4258 END IF;
4259 -- Call the reduce reservations API by setting the
4260 -- delete_flag to yes. delete all reservations for that
4261 -- supply line.
4262 l_delete_flag := 'Y';
4263 l_sort_by_criteria := inv_reservation_global.g_query_demand_ship_date_desc;
4264 p_mtl_maintain_rsv_rec.organization_id := p_organization_id;
4265 p_mtl_maintain_rsv_rec.inventory_item_id := p_inventory_item_id;
4266 p_mtl_maintain_rsv_rec.supply_source_type_id := p_supply_source_type_id;
4267 p_mtl_maintain_rsv_rec.supply_source_header_id := p_supply_source_header_id;
4268 p_mtl_maintain_rsv_rec.supply_source_line_id := p_supply_source_line_id;
4269
4270 inv_maintain_reservation_pub.reduce_reservation
4271 (p_api_version_number => 1.0,
4272 p_init_msg_lst => fnd_api.g_false,
4273 x_return_status => l_return_status,
4274 x_msg_count => l_msg_count,
4275 x_msg_data => l_msg_data,
4276 p_mtl_maintain_rsv_rec => p_mtl_maintain_rsv_rec,
4277 p_delete_flag => l_delete_flag,
4278 p_sort_by_criteria => l_sort_by_criteria,
4279 x_quantity_modified => l_qty_modified
4280 );
4281
4282 IF l_debug=1 THEN
4283 debug_print ('Return Status after calling reduce reservations: '|| l_return_status);
4284 END IF;
4285
4286 IF l_return_status = fnd_api.g_ret_sts_error THEN
4287
4288 IF l_debug=1 THEN
4289 debug_print('Raising expected error'||l_return_status);
4290 END IF;
4291 RAISE fnd_api.g_exc_error;
4292
4293 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
4294
4295 IF l_debug=1 THEN
4296 debug_print('Rasing Unexpected error'||l_return_status);
4297 END IF;
4298 RAISE fnd_api.g_exc_unexpected_error;
4299
4300 END IF;
4301 END IF;
4302 END IF;
4303 ELSIF (p_supply_source_type_id = inv_reservation_global.g_source_type_internal_req OR
4304 p_supply_source_type_id = inv_reservation_global.g_source_type_req) THEN
4305
4306 SELECT count(1)
4307 INTO l_project_count
4308 FROM po_requisition_lines_all prl, po_req_distributions_all prd
4309 WHERE prl.requisition_header_id = p_supply_source_header_id
4310 AND prl.requisition_line_id = p_supply_source_line_id
4311 AND prl.requisition_line_id = prd.requisition_line_id
4312 group by prd.project_id, prd.task_id;
4313
4314 IF (l_project_count > 1) THEN
4315 IF (l_debug = 1) THEN
4316 debug_print('Multiple project and task combinations exists for the supply line');
4317 END IF;
4318
4319 fnd_message.set_name('INV', 'INV_RSV_SUP_MUL_PROJ');
4320 fnd_msg_pub.ADD;
4321 RAISE fnd_api.g_exc_error;
4322 END IF;
4323
4324 IF((p_project_id is not null) AND (l_project_count = 1)) THEN
4325 SELECT MIN(prd.project_id), MIN(prd.task_id)
4326 INTO l_project_id, l_task_id
4327 FROM po_requisition_lines_all prl, po_req_distributions_all prd
4328 WHERE prl.requisition_header_id = p_supply_source_header_id
4329 AND prl.requisition_line_id = p_supply_source_line_id
4330 AND prl.requisition_line_id = prd.requisition_line_id;
4331
4332 IF (l_project_id <> p_project_id or l_task_id <> p_task_id) THEN
4333 IF (l_debug = 1) THEN
4334 debug_print('The project and task of reservation record does not match with the supply line');
4335 END IF;
4336
4337 fnd_message.set_name('INV', 'INV_RSV_SUP_DIFF_PROJ');
4338 fnd_msg_pub.ADD;
4339 RAISE fnd_api.g_exc_error;
4340 END IF;
4341 END IF;
4342 ELSIF (p_supply_source_type_id = inv_reservation_global.g_source_type_wip) THEN
4343
4344 -- get wip entity id from wip_record_cache
4345 inv_reservation_util_pvt.get_wip_cache
4346 (
4347 x_return_status => l_return_status
4348 , p_wip_entity_id => p_supply_source_header_id
4349 );
4350
4351 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
4352 RAISE fnd_api.g_exc_error;
4353 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
4354 RAISE fnd_api.g_exc_unexpected_error;
4355 ELSE
4356 l_wip_entity_type := inv_reservation_global.g_wip_record_cache(p_supply_source_header_id).wip_entity_type;
4357 l_wip_job_type := inv_reservation_global.g_wip_record_cache(p_supply_source_header_id).wip_entity_job;
4358 END IF;
4359
4360 -- Commenting out the code as we dont validate for these supply
4361 --types
4362 /************************************
4363 IF (l_wip_entity_type IN (inv_reservation_global.g_wip_source_type_discrete,
4364 inv_reservation_global.g_wip_source_type_osfm,
4365 inv_reservation_global.g_wip_source_type_fpo,
4366 inv_reservation_global.g_wip_source_type_batch)) THEN
4367
4368 SELECT count(1)
4369 INTO l_project_count
4370 FROM wip_discrete_jobs
4371 WHERE wip_entity_id = p_supply_source_header_id
4372 group by project_id, task_id;
4373
4374 IF (l_project_count > 1) THEN
4375 IF (l_debug = 1) THEN
4376 debug_print('Multiple project and task combinations exists for the supply line');
4377 END IF;
4378
4379 fnd_message.set_name('INV', 'INV_RSV_SUP_MUL_PROJ');
4380 fnd_msg_pub.ADD;
4381 RAISE fnd_api.g_exc_error;
4382 END IF;
4383
4384 IF (p_project_id is not null) THEN
4385 SELECT project_id, task_id
4386 INTO l_project_id, l_task_id
4387 FROM wip_discrete_jobs
4388 WHERE wip_entity_id = p_supply_source_header_id;
4389
4390 IF (l_project_id <> p_project_id or l_task_id <> p_task_id) THEN
4391 IF (l_debug = 1) THEN
4392 debug_print('The project and task of reservation record does not match with the supply line');
4393 END IF;
4394
4395 fnd_message.set_name('INV', 'INV_RSV_SUP_DIFF_PROJ');
4396 fnd_msg_pub.ADD;
4397 RAISE fnd_api.g_exc_error;
4398 END IF;
4399 END IF;
4400 END IF;
4401 --commention code for pjm validations for certain supplies
4402 *********************************/
4403 END IF;
4404 END IF;
4405
4406 x_return_status := l_return_status;
4407
4408 EXCEPTION
4409 WHEN fnd_api.g_exc_error THEN
4410 x_return_status := fnd_api.g_ret_sts_error;
4411 --
4412 WHEN fnd_api.g_exc_unexpected_error THEN
4413 x_return_status := fnd_api.g_ret_sts_unexp_error ;
4414 --
4415 WHEN OTHERS THEN
4416 x_return_status := fnd_api.g_ret_sts_unexp_error ;
4417 --
4418 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
4419 THEN
4420 fnd_msg_pub.add_exc_msg
4421 ( g_pkg_name
4422 , 'validate_pjm_reservations'
4423 );
4424 END IF;
4425 --
4426 END validate_pjm_reservations;
4427 /*** End R12 }} ***/
4428
4429 /*** {{ R12 Enhanced reservations code changes ***/
4430 -- Procedure
4431 -- validate_serials
4432 -- Description
4433 -- 1. validate the supply and demand source for serial reservation
4434 -- returns error if the supply is not INV or demand is not
4435 -- CMRO, SO or INV.
4436 -- 2. validate if the reservation record is detailed for serial
4437 -- reservation
4438 -- 3. validate the serial controls with the (org, item, rev, lot, sub, loc)
4439 -- controls on the reservation record.
4440 -- returns error if they don't match.
4441
4442 PROCEDURE validate_serials
4443 (
4444 x_return_status OUT NOCOPY VARCHAR2
4445 , p_orig_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
4446 , p_to_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
4447 , p_orig_serial_array IN inv_reservation_global.serial_number_tbl_type
4448 , p_to_serial_array IN inv_reservation_global.serial_number_tbl_type
4449 , p_rsv_action_name IN VARCHAR2
4450 ) IS
4451 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
4452 l_msg_count NUMBER;
4453 l_msg_data VARCHAR2(1000);
4454 l_wip_entity_type NUMBER;
4455 l_wip_job_type VARCHAR2(15);
4456 l_debug NUMBER;
4457 l_current_status NUMBER;
4458 l_organization_id NUMBER;
4459 l_revision VARCHAR2(3);
4460 l_subinventory VARCHAR2(10);
4461 l_locator_id NUMBER;
4462 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
4463 l_lot_number VARCHAR2(80);
4464 l_lpn_id NUMBER;
4465 l_reservation_id NUMBER;
4466 l_sub_cache_index NUMBER;
4467 l_item_cache_index NUMBER;
4468 l_org_cache_index NUMBER;
4469 l_result_locator_control NUMBER;
4470 l_orig_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
4471 l_to_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
4472 l_sub_rec inv_reservation_global.sub_record;
4473 l_org_rec inv_reservation_global.organization_record;
4474 l_item_rec inv_reservation_global.item_record;
4475
4476 CURSOR c_item(p_inventory_item_id NUMBER) IS
4477 SELECT *
4478 FROM mtl_system_items
4479 WHERE inventory_Item_Id = p_inventory_item_id;
4480
4481 BEGIN
4482
4483 IF (g_debug IS NULL) THEN
4484 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4485 END IF;
4486
4487 l_debug := g_debug;
4488
4489 IF (l_debug = 1) THEN
4490 debug_print('In validate_serials');
4491 debug_print('Supply type = ' || p_orig_rsv_rec.supply_source_type_id ||
4492 ' ,Demand type = ' || p_orig_rsv_rec.demand_source_type_id);
4493 debug_print('count of p_orig_serial_array: ' || p_orig_serial_array.COUNT);
4494 debug_print('count of p_to_serial_array: ' || p_to_serial_array.COUNT);
4495 END IF;
4496
4497 IF (p_orig_serial_array.COUNT > 0 or p_to_serial_array.COUNT > 0) THEN
4498
4499 IF (p_orig_serial_array.COUNT > 0) THEN
4500 IF (l_debug = 1) THEN
4501 debug_print('Inside from count > 0');
4502 END IF;
4503 -- return error if the p_orig_rsv_rec is null
4504 IF (p_orig_rsv_rec.organization_id is null OR p_orig_rsv_rec.organization_id = fnd_api.g_miss_num) THEN
4505 IF (l_debug = 1) THEN
4506 debug_print('The reservation record is null');
4507 END IF;
4508
4509 fnd_message.set_name('INV', 'INV_RSV_NULL_REC');
4510 fnd_msg_pub.ADD;
4511 RAISE fnd_api.g_exc_error;
4512 END IF;
4513
4514 inv_reservation_util_pvt.search_organization_cache
4515 (
4516 x_return_status => l_return_status
4517 , p_organization_id => p_orig_rsv_rec.organization_id
4518 , x_index => l_org_cache_index
4519 );
4520 --
4521 IF l_return_status = fnd_api.g_ret_sts_error THEN
4522 RAISE fnd_api.g_exc_error;
4523 End IF ;
4524 --
4525 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
4526 RAISE fnd_api.g_exc_unexpected_error;
4527 End IF;
4528 --
4529 IF l_org_cache_index IS NULL THEN
4530 l_org_rec.organization_id:= p_orig_rsv_rec.organization_id;
4531 IF INV_Validate.Organization(
4532 p_org => l_org_rec
4533 )=INV_Validate.F THEN
4534 fnd_message.set_name('INV', 'INVALID ORGANIZATION');
4535 fnd_msg_pub.add;
4536 RAISE fnd_api.g_exc_error;
4537 END IF;
4538
4539 --
4540 inv_reservation_util_pvt.add_organization_cache
4541 (
4542 x_return_status => l_return_status
4543 , p_organization_record => l_org_rec
4544 , x_index => l_org_cache_index
4545 );
4546 --
4547 IF l_return_status = fnd_api.g_ret_sts_error THEN
4548 RAISE fnd_api.g_exc_error;
4549 End IF ;
4550
4551 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
4552 RAISE fnd_api.g_exc_unexpected_error;
4553 End IF;
4554
4555 END IF;
4556
4557 -- validate the supply source for serial reservation of original reservation record
4558 IF (p_orig_rsv_rec.supply_source_type_id <> inv_reservation_global.g_source_type_inv) THEN
4559 IF (l_debug = 1) THEN
4560 debug_print('Serial reservation can be created with Inventory supply only');
4561 END IF;
4562
4563 fnd_message.set_name('INV', 'INV_RSV_SR_SUP_ERR');
4564 fnd_msg_pub.ADD;
4565 RAISE fnd_api.g_exc_error;
4566 END IF;
4567
4568 IF (l_debug = 1) THEN
4569 debug_print('Before calling WIP cache');
4570 END IF;
4571 -- validate the demand source for serial reservation of original reservation record
4572 IF (p_orig_rsv_rec.demand_source_type_id = inv_reservation_global.g_source_type_wip) THEN
4573 -- get wip entity id from wip_record_cache
4574 inv_reservation_util_pvt.get_wip_cache
4575 (
4576 x_return_status => l_return_status
4577 , p_wip_entity_id => p_orig_rsv_rec.demand_source_header_id
4578 );
4579
4580 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
4581 RAISE fnd_api.g_exc_error;
4582 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
4583 RAISE fnd_api.g_exc_unexpected_error;
4584 ELSE
4585 l_wip_entity_type := inv_reservation_global.g_wip_record_cache(p_orig_rsv_rec.demand_source_header_id).wip_entity_type;
4586 l_wip_job_type := inv_reservation_global.g_wip_record_cache(p_orig_rsv_rec.demand_source_header_id).wip_entity_job;
4587 END IF;
4588 END IF;
4589
4590 IF (l_debug = 1) THEN
4591 debug_print('After calling WIP cache');
4592 END IF;
4593
4594 IF ((p_orig_rsv_rec.demand_source_type_id NOT IN (inv_reservation_global.g_source_type_oe,
4595 inv_reservation_global.g_source_type_internal_ord,inv_reservation_global.g_source_type_rma,
4596 inv_reservation_global.g_source_type_inv)) AND
4597 (p_orig_rsv_rec.demand_source_type_id <> inv_reservation_global.g_source_type_wip AND
4598 l_wip_entity_type <> inv_reservation_global.g_wip_source_type_cmro)) THEN
4599
4600 IF (l_debug = 1) THEN
4601 debug_print('Serial reservation can be created with Inventory, sales order or CMRO demand only');
4602 END IF;
4603
4604 fnd_message.set_name('INV', 'INV_RSV_SR_DEM_ERR');
4605 fnd_msg_pub.ADD;
4606 RAISE fnd_api.g_exc_error;
4607 END IF;
4608
4609 IF (l_debug = 1) THEN
4610 debug_print('Before calling convert missing to null');
4611 END IF;
4612 -- convert the missing value in the reservation record to null
4613 inv_reservation_pvt.convert_missing_to_null
4614 (
4615 p_rsv_rec => p_orig_rsv_rec
4616 , x_rsv_rec => l_orig_rsv_rec
4617 );
4618
4619 IF (l_debug = 1) THEN
4620 debug_print('After convert missing to null');
4621 END IF;
4622
4623 -- get the revision control from item cache, first see if the item cache exists
4624 inv_reservation_util_pvt.search_item_cache
4625 (
4626 x_return_status => l_return_status
4627 ,p_inventory_item_id => l_orig_rsv_rec.inventory_item_id
4628 ,p_organization_id => l_orig_rsv_rec.organization_id
4629 ,x_index => l_item_cache_index
4630 );
4631 --
4632 If l_return_status = fnd_api.g_ret_sts_error Then
4633 RAISE fnd_api.g_exc_error;
4634 End If;
4635 --
4636 If l_return_status = fnd_api.g_ret_sts_unexp_error Then
4637 RAISE fnd_api.g_exc_unexpected_error;
4638 End If;
4639 --
4640 --if item isn't in cache, need to add it
4641 If l_item_cache_index IS NULL Then
4642 OPEN c_item(l_orig_rsv_rec.inventory_item_id);
4643 FETCH c_item into l_item_rec;
4644 CLOSE c_item;
4645
4646 inv_reservation_util_pvt.add_item_cache
4647 (
4648 x_return_status => l_return_status
4649 ,p_item_record => l_item_rec
4650 ,x_index => l_item_cache_index
4651 );
4652 --
4653 if l_return_status = fnd_api.g_ret_sts_error then
4654 RAISE fnd_api.g_exc_error;
4655 end if;
4656 --
4657 if l_return_status = fnd_api.g_ret_sts_unexp_error then
4658 RAISE fnd_api.g_exc_unexpected_error;
4659 end if;
4660 End If;
4661
4662 -- if revision controlled and revision in reservation record is null, return errors
4663 IF (inv_reservation_global.g_item_record_cache(l_orig_rsv_rec.inventory_item_id).revision_qty_control_code =
4664 inv_reservation_global.g_revision_control_yes AND l_orig_rsv_rec.revision is null) THEN
4665 IF (l_debug = 1) THEN
4666 debug_print('Serial reservation needs to be detailed, revision is null');
4667 END IF;
4668
4669 fnd_message.set_name('INV', 'INV_RSV_SR_DETAIL');
4670 fnd_msg_pub.ADD;
4671 END IF;
4672
4673 IF (l_debug = 1) THEN
4674 debug_print('After revision check');
4675 END IF;
4676
4677 -- if lot controlled and lot number is null, return errors
4678 IF (inv_reservation_global.g_item_record_cache(l_orig_rsv_rec.inventory_item_id).lot_control_code =
4679 inv_reservation_global.g_lot_control_yes AND l_orig_rsv_rec.lot_number is null) THEN
4680 IF (l_debug = 1) THEN
4681 debug_print('Serial reservation needs to be detailed, lot number is null');
4682 END IF;
4683
4684 fnd_message.set_name('INV', 'INV_RSV_SR_DETAIL');
4685 fnd_msg_pub.ADD;
4686 END IF;
4687
4688 IF (l_debug = 1) THEN
4689 debug_print('After lot check');
4690 END IF;
4691
4692 -- if subinventory is null, return errors
4693 IF (l_orig_rsv_rec.subinventory_code is null) THEN
4694 IF (l_debug = 1) THEN
4695 debug_print('Serial reservation needs to be detailed, subinventory is null');
4696 END IF;
4697
4698 fnd_message.set_name('INV', 'INV_RSV_SR_DETAIL');
4699 fnd_msg_pub.ADD;
4700 ELSE
4701 -- if subinventory is locator controlled and locator is null,
4702 --returns error
4703
4704 IF (l_debug = 1) THEN
4705 debug_print('Before sub cache search');
4706 END IF;
4707 inv_reservation_util_pvt.search_sub_cache
4708 (
4709 x_return_status => l_return_status
4710 , p_subinventory_code => l_orig_rsv_rec.subinventory_code
4711 , p_organization_id => l_orig_rsv_rec.organization_id
4712 , x_index => l_sub_cache_index
4713 );
4714 IF (l_debug = 1) THEN
4715 debug_print('After sub cache search');
4716 END IF;
4717
4718
4719 IF l_sub_cache_index IS NULL THEN
4720
4721 -- Modified to call common API
4722 l_sub_rec.secondary_inventory_name := l_orig_rsv_rec.subinventory_code;
4723 l_org_rec.organization_id := l_orig_rsv_rec.organization_id;
4724 IF INV_Validate.subinventory
4725 (
4726 p_sub => l_sub_rec,
4727 p_org => l_org_rec
4728 )=INV_Validate.F THEN
4729 fnd_message.set_name('INV','INVALID_SUB');
4730 fnd_msg_pub.add;
4731 RAISE fnd_api.g_exc_error;
4732 END IF;
4733
4734 --
4735 inv_reservation_util_pvt.add_sub_cache
4736 (
4737 x_return_status => l_return_status
4738 , p_sub_record => l_sub_rec
4739 , x_index => l_sub_cache_index
4740 );
4741 --
4742 IF l_return_status = fnd_api.g_ret_sts_error THEN
4743 RAISE fnd_api.g_exc_error;
4744 END IF ;
4745 --
4746 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
4747 RAISE fnd_api.g_exc_unexpected_error;
4748 END IF;
4749 END IF;
4750
4751
4752 IF (l_debug = 1) THEN
4753 debug_print('Inside checking the locator controls');
4754 debug_print('l_orig_rsv_rec.organization_id' ||
4755 l_orig_rsv_rec.organization_id);
4756 debug_print('sub index' ||l_sub_cache_index);
4757 debug_print('sub ' || l_orig_rsv_rec.subinventory_code);
4758 debug_print('item id' ||
4759 l_orig_rsv_rec.inventory_item_id);
4760
4761 debug_print('org control' || inv_reservation_global.g_organization_record_cache
4762 (l_orig_rsv_rec.organization_id).stock_locator_control_code);
4763 debug_print('sub control' || inv_reservation_global.g_sub_record_cache
4764 (l_sub_cache_index).locator_type);
4765 debug_print('item control' || inv_reservation_global.g_item_record_cache
4766 (l_orig_rsv_rec.inventory_item_id).location_control_code);
4767 END IF;
4768
4769 l_result_locator_control := inv_reservation_util_pvt.locator_control
4770 ( p_org_control => inv_reservation_global.g_organization_record_cache
4771 (l_orig_rsv_rec.organization_id).stock_locator_control_code
4772 , p_sub_control => inv_reservation_global.g_sub_record_cache
4773 (l_sub_cache_index).locator_type
4774 , p_item_control => inv_reservation_global.g_item_record_cache
4775 (l_orig_rsv_rec.inventory_item_id).location_control_code
4776 );
4777
4778 IF (l_debug = 1) THEN
4779 debug_print('l_result_locator_control' || l_result_locator_control);
4780 END IF;
4781
4782 IF (l_result_locator_control <> 1 AND l_orig_rsv_rec.locator_id is null) THEN
4783 IF (l_debug = 1) THEN
4784 debug_print('Serial reservation needs to be detailed, locator is null');
4785 END IF;
4786
4787 fnd_message.set_name('INV', 'INV_RSV_SR_DETAIL');
4788 fnd_msg_pub.ADD;
4789 END IF;
4790 IF (l_debug = 1) THEN
4791 debug_print('After loc check');
4792 END IF;
4793
4794 END IF;
4795
4796 IF (l_debug = 1) THEN
4797 debug_print('After sub/loc check');
4798 END IF;
4799
4800 IF (l_debug = 1) THEN
4801 debug_print('Before loop');
4802 END IF;
4803 -- Get all information for the serial number
4804 FOR i in 1..p_orig_serial_array.COUNT LOOP
4805
4806 IF (l_debug = 1) THEN
4807 debug_print('index = ' || i);
4808 debug_print('serial number = ' || p_orig_serial_array(i).serial_number);
4809 debug_print('inventory item id = ' || p_orig_serial_array(i).inventory_item_id);
4810 END IF;
4811
4812 BEGIN
4813 SELECT current_status,
4814 reservation_id,
4815 current_organization_id,
4816 revision,
4817 current_subinventory_code,
4818 current_locator_id,
4819 lot_number,
4820 lpn_id
4821 INTO l_current_status,
4822 l_reservation_id,
4823 l_organization_id,
4824 l_revision,
4825 l_subinventory,
4826 l_locator_id,
4827 l_lot_number,
4828 l_lpn_id
4829 FROM mtl_serial_numbers
4830 WHERE serial_number = p_orig_serial_array(i).serial_number
4831 AND inventory_item_id =
4832 p_orig_serial_array(i).inventory_item_id;
4833 EXCEPTION
4834 WHEN no_data_found THEN
4835 IF (l_debug = 1) THEN
4836 debug_print('did not find any records for the passed
4837 information' || SQLERRM);
4838 END IF;
4839 fnd_message.set_name('INV', 'INV_INVALID_SERIAL');
4840 fnd_msg_pub.ADD;
4841 RAISE fnd_api.g_exc_error;
4842 END;
4843 IF (l_debug = 1) THEN
4844 debug_print('current_status = ' || l_current_status);
4845 debug_print('reservation_id = ' || l_reservation_id);
4846 debug_print('organization_id = ' || l_organization_id);
4847 debug_print('revision = ' || l_revision);
4848 debug_print('subinventory = ' || l_subinventory);
4849 debug_print('locator_id = ' || l_locator_id);
4850 debug_print('lot_number = ' || l_lot_number);
4851 debug_print('l_lpn_id = ' || l_lpn_id);
4852 END IF;
4853
4854 -- validate the current status of serial number of original serial number records
4855 -- return errors if the serial number is not in inventory.
4856
4857 -- For relieving serials through the TM, the serials may
4858 -- have been issued out before calling relieve. In such a
4859 -- case, we should not check for status in inventory.
4860 -- IF (not(cmro and relieve) and status <> 3) or
4861 -- if ((cmro and relieve) and status not in (3,4))
4862 -- then error.
4863
4864 IF ((NOT(p_orig_rsv_rec.demand_source_type_id =
4865 inv_reservation_global.g_source_type_wip AND
4866 l_wip_entity_type =
4867 inv_reservation_global.g_wip_source_type_cmro) AND
4868 ( p_rsv_action_name = 'RELIEVE')) AND
4869 (l_current_status <> 3)) OR
4870 (((p_orig_rsv_rec.demand_source_type_id =
4871 inv_reservation_global.g_source_type_wip AND
4872 l_wip_entity_type =
4873 inv_reservation_global.g_wip_source_type_cmro) AND
4874 ( p_rsv_action_name = 'RELIEVE')) AND (l_current_status NOT IN (3,4)))
4875 THEN
4876 IF (l_debug = 1) THEN
4877 debug_print('The serial number is not in inventory for serial reservation');
4878 END IF;
4879
4880 fnd_message.set_name('INV', 'INV_RSV_SR_STS_ERR');
4881 fnd_msg_pub.ADD;
4882
4883 RAISE fnd_api.g_exc_error;
4884 END IF;
4885
4886 -- for the form record, validate the serial controls with the (org, item, rev, lot, sub, loc)
4887 -- controls on the reservation record if we create/delete/relieve reservation
4888 -- for transfer/update reservation, we validate the serial information with the
4889 -- reservation_id on the reservation record only because the serial control has
4890 -- already changed when calling reservation API, we only need to validate the
4891 -- serial controls with to record.
4892
4893 IF (p_rsv_action_name = 'CREATE' OR p_rsv_action_name = 'DELETE' OR p_rsv_action_name = 'RELIEVE') THEN
4894
4895 IF (l_reservation_id <> nvl(l_orig_rsv_rec.reservation_id, l_reservation_id) OR
4896 l_organization_id <> nvl(l_orig_rsv_rec.organization_id, l_organization_id) OR
4897 p_orig_serial_array(i).inventory_item_id <>
4898 nvl(l_orig_rsv_rec.inventory_item_id, p_orig_serial_array(i).inventory_item_id) OR
4899 l_revision <> nvl(l_orig_rsv_rec.revision, l_revision) OR
4900 l_subinventory <> nvl(l_orig_rsv_rec.subinventory_code, l_subinventory) OR
4901 l_locator_id <> nvl(l_orig_rsv_rec.locator_id, l_locator_id) OR
4902 l_lot_number <> nvl(l_orig_rsv_rec.lot_number, l_lot_number) OR
4903 l_lpn_id <> nvl(l_orig_rsv_rec.lpn_id, l_lpn_id)) THEN
4904
4905 IF (l_debug = 1) THEN
4906 debug_print('The serial controls is not same as the reservation controls');
4907 debug_print('inventory item id = ' || l_orig_rsv_rec.inventory_item_id);
4908 debug_print('reservation_id = ' || l_orig_rsv_rec.reservation_id);
4909 debug_print('organization_id = ' || l_orig_rsv_rec.organization_id);
4910 debug_print('revision = ' || l_orig_rsv_rec.revision);
4911 debug_print('subinventory = ' || l_orig_rsv_rec.subinventory_code);
4912 debug_print('locator_id = ' || l_orig_rsv_rec.locator_id);
4913 debug_print('lot_number = ' || l_orig_rsv_rec.lot_number);
4914 END IF;
4915
4916 fnd_message.set_name('INV', 'INV_RSV_SR_NOT_MATCH');
4917 fnd_msg_pub.ADD;
4918
4919 RAISE fnd_api.g_exc_error;
4920 END IF;
4921 ELSE
4922 IF (l_reservation_id <> nvl(l_orig_rsv_rec.reservation_id, l_reservation_id)) THEN
4923
4924 IF (l_debug = 1) THEN
4925 debug_print('reservation_id = ' || l_orig_rsv_rec.reservation_id);
4926 END IF;
4927
4928 fnd_message.set_name('INV', 'INV_RSV_SR_NOT_MATCH');
4929 fnd_msg_pub.ADD;
4930
4931 RAISE fnd_api.g_exc_error;
4932 END IF;
4933 END IF;
4934 END LOOP;
4935
4936 END IF; -- end if p_orig_serial_array is not null
4937
4938 IF (p_to_serial_array.COUNT > 0) THEN
4939 IF (l_debug = 1) THEN
4940 debug_print('Inside to count > 0');
4941 END IF;
4942 -- return error if the p_to_rsv_rec is null
4943 IF (p_to_rsv_rec.organization_id is null OR p_to_rsv_rec.organization_id = fnd_api.g_miss_num) THEN
4944 IF (l_debug = 1) THEN
4945 debug_print('The reservation record is null');
4946 END IF;
4947
4948 fnd_message.set_name('INV', 'INV_RSV_NULL_REC');
4949 fnd_msg_pub.ADD;
4950 RAISE fnd_api.g_exc_error;
4951 END IF;
4952
4953 inv_reservation_util_pvt.search_organization_cache
4954 (
4955 x_return_status => l_return_status
4956 , p_organization_id => p_to_rsv_rec.organization_id
4957 , x_index => l_org_cache_index
4958 );
4959 --
4960 IF l_return_status = fnd_api.g_ret_sts_error THEN
4961 RAISE fnd_api.g_exc_error;
4962 End IF ;
4963 --
4964 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
4965 RAISE fnd_api.g_exc_unexpected_error;
4966 End IF;
4967 --
4968 IF l_org_cache_index IS NULL THEN
4969 l_org_rec.organization_id:= p_to_rsv_rec.organization_id;
4970 IF INV_Validate.Organization(
4971 p_org => l_org_rec
4972 )=INV_Validate.F THEN
4973 fnd_message.set_name('INV', 'INVALID ORGANIZATION');
4974 fnd_msg_pub.add;
4975 RAISE fnd_api.g_exc_error;
4976 END IF;
4977
4978 --
4979 inv_reservation_util_pvt.add_organization_cache
4980 (
4981 x_return_status => l_return_status
4982 , p_organization_record => l_org_rec
4983 , x_index => l_org_cache_index
4984 );
4985 --
4986 IF l_return_status = fnd_api.g_ret_sts_error THEN
4987 RAISE fnd_api.g_exc_error;
4988 End IF ;
4989
4990 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
4991 RAISE fnd_api.g_exc_unexpected_error;
4992 End IF;
4993
4994 END IF;
4995
4996 IF (l_debug = 1) THEN
4997 debug_print('After org check: to record: ');
4998 END IF;
4999
5000 -- validate the supply source for serial reservation of to reservation record
5001 IF (p_to_rsv_rec.supply_source_type_id <> inv_reservation_global.g_source_type_inv) THEN
5002 IF (l_debug = 1) THEN
5003 debug_print('Serial reservation can be created with Inventory supply only');
5004 END IF;
5005
5006 fnd_message.set_name('INV', 'INV_RSV_SR_SUP_ERR');
5007 fnd_msg_pub.ADD;
5008 RAISE fnd_api.g_exc_error;
5009 END IF;
5010
5011 -- validate the demand source for serial reservation of to reservation record
5012 IF (p_to_rsv_rec.demand_source_type_id = inv_reservation_global.g_source_type_wip) THEN
5013 -- get wip entity id from wip_record_cache
5014 inv_reservation_util_pvt.get_wip_cache
5015 (
5016 x_return_status => l_return_status
5017 , p_wip_entity_id => p_to_rsv_rec.demand_source_header_id
5018 );
5019
5020 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
5021 RAISE fnd_api.g_exc_error;
5022 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
5023 RAISE fnd_api.g_exc_unexpected_error;
5024 ELSE
5025 l_wip_entity_type := inv_reservation_global.g_wip_record_cache(p_to_rsv_rec.demand_source_header_id).wip_entity_type;
5026 l_wip_job_type := inv_reservation_global.g_wip_record_cache(p_to_rsv_rec.demand_source_header_id).wip_entity_job;
5027 END IF;
5028 END IF;
5029
5030 IF (l_debug = 1) THEN
5031 debug_print('After wip check: to record: ');
5032 END IF;
5033
5034 IF ((p_to_rsv_rec.demand_source_type_id NOT IN (inv_reservation_global.g_source_type_oe,
5035 inv_reservation_global.g_source_type_inv)) AND
5036 (p_to_rsv_rec. demand_source_type_id <> inv_reservation_global.g_source_type_wip AND
5037 l_wip_entity_type <> inv_reservation_global.g_wip_source_type_cmro)) THEN
5038
5039 IF (l_debug = 1) THEN
5040 debug_print('Serial reservation can be created with Inventory, sales order or CMRO demand only');
5041 END IF;
5042
5043 fnd_message.set_name('INV', 'INV_RSV_SR_DEM_ERR');
5044 fnd_msg_pub.ADD;
5045 RAISE fnd_api.g_exc_error;
5046 END IF;
5047
5048 IF (l_debug = 1) THEN
5049 debug_print('After demand check: to record:');
5050 END IF;
5051
5052 -- convert the missing value in the reservation record to null
5053 inv_reservation_pvt.convert_missing_to_null
5054 (
5055 p_rsv_rec => p_to_rsv_rec
5056 , x_rsv_rec => l_to_rsv_rec
5057 );
5058
5059 IF (l_debug = 1) THEN
5060 debug_print('After convert missing to null');
5061 END IF;
5062
5063 -- get the revision control from item cache, first see if the item cache exists
5064 inv_reservation_util_pvt.search_item_cache
5065 (
5066 x_return_status => l_return_status
5067 ,p_inventory_item_id => l_to_rsv_rec.inventory_item_id
5068 ,p_organization_id => l_to_rsv_rec.organization_id
5069 ,x_index => l_item_cache_index
5070 );
5071 --
5072 If l_return_status = fnd_api.g_ret_sts_error Then
5073 RAISE fnd_api.g_exc_error;
5074 End If;
5075 --
5076 If l_return_status = fnd_api.g_ret_sts_unexp_error Then
5077 RAISE fnd_api.g_exc_unexpected_error;
5078 End If;
5079 --
5080 --if item isn't in cache, need to add it
5081 If l_item_cache_index IS NULL Then
5082 OPEN c_item(l_to_rsv_rec.inventory_item_id);
5083 FETCH c_item into l_item_rec;
5084 CLOSE c_item;
5085
5086 inv_reservation_util_pvt.add_item_cache
5087 (
5088 x_return_status => l_return_status
5089 ,p_item_record => l_item_rec
5090 ,x_index => l_item_cache_index
5091 );
5092 --
5093 if l_return_status = fnd_api.g_ret_sts_error then
5094 RAISE fnd_api.g_exc_error;
5095 end if;
5096 --
5097 if l_return_status = fnd_api.g_ret_sts_unexp_error then
5098 RAISE fnd_api.g_exc_unexpected_error;
5099 end if;
5100 End If;
5101
5102 -- if revision controlled and revision in reservation record is null, return errors
5103 IF (inv_reservation_global.g_item_record_cache(l_to_rsv_rec.inventory_item_id).revision_qty_control_code =
5104 inv_reservation_global.g_revision_control_yes AND l_to_rsv_rec.revision is null) THEN
5105 IF (l_debug = 1) THEN
5106 debug_print('Serial reservation needs to be detailed, revision is null');
5107 END IF;
5108
5109 fnd_message.set_name('INV', 'INV_RSV_SR_DETAIL');
5110 fnd_msg_pub.ADD;
5111 END IF;
5112
5113 -- if lot controlled and lot number is null, return errors
5114 IF (inv_reservation_global.g_item_record_cache(l_to_rsv_rec.inventory_item_id).lot_control_code =
5115 inv_reservation_global.g_lot_control_yes AND l_to_rsv_rec.lot_number is null) THEN
5116 IF (l_debug = 1) THEN
5117 debug_print('Serial reservation needs to be detailed, lot number is null');
5118 END IF;
5119
5120 fnd_message.set_name('INV', 'INV_RSV_SR_DETAIL');
5121 fnd_msg_pub.ADD;
5122 END IF;
5123
5124 -- if subinventory is null, return errors
5125 IF (l_to_rsv_rec.subinventory_code is null) THEN
5126 IF (l_debug = 1) THEN
5127 debug_print('Serial reservation needs to be detailed, subinventory is null');
5128 END IF;
5129
5130 fnd_message.set_name('INV', 'INV_RSV_SR_DETAIL');
5131 fnd_msg_pub.ADD;
5132 ELSE
5133 -- if subinventory is locator controlled and locator is null, returns error
5134 inv_reservation_util_pvt.search_sub_cache
5135 (
5136 x_return_status => l_return_status
5137 , p_subinventory_code => l_to_rsv_rec.subinventory_code
5138 , p_organization_id => l_to_rsv_rec.organization_id
5139 , x_index => l_sub_cache_index
5140 );
5141
5142 IF (l_debug = 1) THEN
5143 debug_print('After sub cache search');
5144 END IF;
5145
5146
5147 IF l_sub_cache_index IS NULL THEN
5148
5149 -- Modified to call common API
5150 l_sub_rec.secondary_inventory_name := l_to_rsv_rec.subinventory_code;
5151 l_org_rec.organization_id := l_to_rsv_rec.organization_id;
5152
5153 IF (l_debug = 1) THEN
5154 debug_print('l_to_rsv_rec.subinventory_code = ' || l_to_rsv_rec.subinventory_code);
5155 debug_print('l_orig_rsv_rec.organization_id = ' || l_to_rsv_rec.organization_id);
5156 END IF;
5157
5158 IF INV_Validate.subinventory
5159 (
5160 p_sub => l_sub_rec,
5161 p_org => l_org_rec
5162 )=INV_Validate.F THEN
5163 fnd_message.set_name('INV','INVALID_SUB');
5164 fnd_msg_pub.add;
5165 RAISE fnd_api.g_exc_error;
5166 END IF;
5167
5168 --
5169 inv_reservation_util_pvt.add_sub_cache
5170 (
5171 x_return_status => l_return_status
5172 , p_sub_record => l_sub_rec
5173 , x_index => l_sub_cache_index
5174 );
5175 --
5176 IF l_return_status = fnd_api.g_ret_sts_error THEN
5177 RAISE fnd_api.g_exc_error;
5178 END IF ;
5179 --
5180 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
5181 RAISE fnd_api.g_exc_unexpected_error;
5182 END IF;
5183 END IF;
5184
5185 IF (l_debug = 1) THEN
5186 debug_print('After sub cache check: to record:');
5187 END IF;
5188
5189 IF (l_debug = 1) THEN
5190 debug_print('Inside checking the locator controls');
5191 debug_print('l_to_rsv_rec.organization_id' ||
5192 l_to_rsv_rec.organization_id);
5193 debug_print('sub index' || l_sub_cache_index);
5194 debug_print('sub ' || l_to_rsv_rec.subinventory_code);
5195 debug_print('item id' ||
5196 l_to_rsv_rec.inventory_item_id);
5197
5198 debug_print('org control' || inv_reservation_global.g_organization_record_cache
5199 (l_to_rsv_rec.organization_id).stock_locator_control_code);
5200 debug_print('sub control' || inv_reservation_global.g_sub_record_cache
5201 (l_sub_cache_index).locator_type);
5202 debug_print('item control' || inv_reservation_global.g_item_record_cache
5203 (l_to_rsv_rec.inventory_item_id).location_control_code);
5204 END IF;
5205
5206 l_result_locator_control := inv_reservation_util_pvt.locator_control
5207 ( p_org_control
5208 => inv_reservation_global.g_organization_record_cache
5209 (l_to_rsv_rec.organization_id).stock_locator_control_code
5210 , p_sub_control
5211 => inv_reservation_global.g_sub_record_cache
5212 (l_sub_cache_index).locator_type
5213 , p_item_control
5214 => inv_reservation_global.g_item_record_cache
5215 (l_to_rsv_rec.inventory_item_id).location_control_code
5216 );
5217 IF (l_result_locator_control <> 1 AND l_to_rsv_rec.locator_id is null) THEN
5218 IF (l_debug = 1) THEN
5219 debug_print('Serial reservation needs to be detailed, locator is null');
5220 END IF;
5221
5222 fnd_message.set_name('INV', 'INV_RSV_SR_DETAIL');
5223 fnd_msg_pub.ADD;
5224 END IF;
5225 END IF;
5226
5227 IF (l_debug = 1) THEN
5228 debug_print('After loc check: to record:');
5229 END IF;
5230 -- Get all information for the serial number
5231 FOR i in 1..p_to_serial_array.COUNT LOOP
5232 SELECT current_status,
5233 reservation_id,
5234 current_organization_id,
5235 revision,
5236 current_subinventory_code,
5237 current_locator_id,
5238 lot_number,
5239 lpn_id
5240 INTO l_current_status,
5241 l_reservation_id,
5242 l_organization_id,
5243 l_revision,
5244 l_subinventory,
5245 l_locator_id,
5246 l_lot_number,
5247 l_lpn_id
5248 FROM mtl_serial_numbers
5249 WHERE serial_number = p_to_serial_array(i).serial_number
5250 AND inventory_item_id = p_to_serial_array(i).inventory_item_id;
5251
5252 IF (l_debug = 1) THEN
5253 debug_print('IInside serial loop. Serial number: ' || p_to_serial_array(i).serial_number);
5254 END IF;
5255
5256 IF (l_debug = 1) THEN
5257 debug_print('index = ' || i);
5258 debug_print('serial number = ' || p_to_serial_array(i).serial_number);
5259 debug_print('inventory item id = ' || p_to_serial_array(i).inventory_item_id);
5260 debug_print('current_status = ' || l_current_status);
5261 debug_print('reservation_id = ' || l_reservation_id);
5262 debug_print('organization_id = ' || l_organization_id);
5263 debug_print('revision = ' || l_revision);
5264 debug_print('subinventory = ' || l_subinventory);
5265 debug_print('locator_id = ' || l_locator_id);
5266 debug_print('lot_number = ' || l_lot_number);
5267 debug_print('lpn_id = ' || l_lpn_id);
5268 END IF;
5269
5270 -- validate the current status of serial number of original serial number records
5271 -- return errors if the serial number is not in inventory.
5272 IF (l_current_status <> 3) THEN
5273 IF (l_debug = 1) THEN
5274 debug_print('The serial number is not in inventory for serial reservation');
5275 END IF;
5276
5277 fnd_message.set_name('INV', 'INV_RSV_SR_STS_ERR');
5278 fnd_msg_pub.ADD;
5279
5280 RAISE fnd_api.g_exc_error;
5281 END IF;
5282
5283 -- validate the serial controls with the (org, item, rev, lot, sub, loc) controls
5284 -- on the reservation record, return errors if they don't match
5285 IF ((l_to_rsv_rec.reservation_id IS NOT NULL AND p_orig_rsv_rec.reservation_id <> fnd_api.g_miss_num AND
5286 l_reservation_id NOT IN (nvl(l_to_rsv_rec.reservation_id, l_reservation_id),
5287 nvl(p_orig_rsv_rec.reservation_id, l_reservation_id))) OR l_organization_id <> nvl(l_to_rsv_rec.organization_id, l_organization_id) OR
5288 p_to_serial_array(i).inventory_item_id <>
5289 nvl(l_to_rsv_rec.inventory_item_id, p_to_serial_array(i).inventory_item_id) OR
5290 l_revision <> nvl(l_to_rsv_rec.revision, l_revision) OR
5291 l_subinventory <> nvl(l_to_rsv_rec.subinventory_code, l_subinventory) OR
5292 l_locator_id <> nvl(l_to_rsv_rec.locator_id, l_locator_id) OR
5293 l_lot_number <> nvl(l_to_rsv_rec.lot_number, l_lot_number) OR
5294 l_lpn_id <> nvl(l_to_rsv_rec.lpn_id, l_lpn_id)) THEN
5295
5296 IF (l_debug = 1) THEN
5297 debug_print('The serial controls is not same as the reservation controls');
5298 debug_print('inventory item id = ' || l_to_rsv_rec.inventory_item_id);
5299 debug_print('orig reservation_id = ' || p_orig_rsv_rec.reservation_id);
5300 debug_print('to reservation_id = ' || l_to_rsv_rec.reservation_id);
5301 debug_print('l_reservation_id = ' || l_reservation_id);
5302 debug_print('organization_id = ' || l_to_rsv_rec.organization_id);
5303 debug_print('revision = ' || l_to_rsv_rec.revision);
5304 debug_print('subinventory = ' || l_to_rsv_rec.subinventory_code);
5305 debug_print('locator_id = ' || l_to_rsv_rec.locator_id);
5306 debug_print('lot_number = ' || l_to_rsv_rec.lot_number);
5307 END IF;
5308
5309 fnd_message.set_name('INV', 'INV_RSV_SR_NOT_MATCH');
5310 fnd_msg_pub.ADD;
5311
5312 RAISE fnd_api.g_exc_error;
5313 END IF;
5314 END LOOP;
5315
5316 END IF; -- end if p_to_serial_array is not null
5317 END IF;
5318
5319 x_return_status := l_return_status;
5320 EXCEPTION
5321 WHEN fnd_api.g_exc_error THEN
5322 x_return_status := fnd_api.g_ret_sts_error;
5323 --
5324 WHEN fnd_api.g_exc_unexpected_error THEN
5325 x_return_status := fnd_api.g_ret_sts_unexp_error ;
5326 --
5327 WHEN OTHERS THEN
5328 x_return_status := fnd_api.g_ret_sts_unexp_error ;
5329 --
5330 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
5331 THEN
5332 fnd_msg_pub.add_exc_msg
5333 ( g_pkg_name
5334 , 'Validate_Serials'
5335 );
5336 END IF;
5337 --
5338 END validate_serials;
5339 /*** End R12 }} ***/
5340
5341 --
5342 -- Procedure
5343 -- validate_input_parameters
5344 -- Description
5345 -- is valid if all of the following are satisfied
5346 -- 1. if p_rsv_action_name is CREATE, or UPDATE, or TRANSFER, or DELETE
5347 -- validate_organization, validate_item, validate_demand_source,
5348 -- validate_supply_source, validate_quantity, validate_sales_order
5349 -- with the p_orig_rsv_rec
5350 -- (the original reservation record) return success
5351 -- 2. if p_rsv_action_name is UPDATE, or TRANSFER
5352 -- validate_organization, validate_item, validate_demand_source,
5353 -- validate_supply_source, validate_quantity with the p_to_rsv_rec
5354 -- (the new reservation record) return success
5355 -- Bug 1937201 - Changed validations so that original_rsv is only
5356 -- validated for the CREATE actions, and that we validate
5357 -- to_rsv for sales order during transfer or update.
5358
5359 PROCEDURE validate_input_parameters
5360 (
5361 x_return_status OUT NOCOPY VARCHAR2
5362 , p_orig_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
5363 , p_to_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
5364 , p_orig_serial_array IN inv_reservation_global.serial_number_tbl_type
5365 , p_to_serial_array IN inv_reservation_global.serial_number_tbl_type
5366 , p_rsv_action_name IN VARCHAR2
5367 , x_orig_item_cache_index OUT NOCOPY INTEGER
5368 , x_orig_org_cache_index OUT NOCOPY INTEGER
5369 , x_orig_demand_cache_index OUT NOCOPY INTEGER
5370 , x_orig_supply_cache_index OUT NOCOPY INTEGER
5371 , x_orig_sub_cache_index OUT NOCOPY INTEGER
5372 , x_to_item_cache_index OUT NOCOPY INTEGER
5373 , x_to_org_cache_index OUT NOCOPY INTEGER
5374 , x_to_demand_cache_index OUT NOCOPY INTEGER
5375 , x_to_supply_cache_index OUT NOCOPY INTEGER
5376 , x_to_sub_cache_index OUT NOCOPY INTEGER
5377 , p_substitute_flag IN BOOLEAN DEFAULT FALSE /* Bug 6044651 */
5378 ) IS
5379 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
5380 l_has_serial_number VARCHAR2(1);
5381 l_orig_item_cache_index INTEGER := NULL;
5382 l_orig_org_cache_index INTEGER := NULL;
5383 l_orig_demand_cache_index INTEGER := NULL;
5384 l_orig_supply_cache_index INTEGER := NULL;
5385 l_orig_sub_cache_index INTEGER := NULL;
5386 l_to_item_cache_index INTEGER := NULL;
5387 l_to_org_cache_index INTEGER := NULL;
5388 l_to_demand_cache_index INTEGER := NULL;
5389 l_to_supply_cache_index INTEGER := NULL;
5390 l_to_sub_cache_index INTEGER := NULL;
5391 l_item_rec inv_reservation_global.item_record;
5392 /*** {{ R12 Enhanced reservations code changes ***/
5393 l_msg_count NUMBER;
5394 l_msg_data VARCHAR2(1000);
5395 l_debug NUMBER;
5396 l_demand_source_header_id NUMBER;
5397 l_demand_source_line_id NUMBER;
5398 l_demand_ship_date DATE;
5399 -- Bug 4608452: Added this to check for existing crossdock reservations
5400 l_wip_entity_type NUMBER;
5401 l_wip_job_type VARCHAR2(15);
5402 /*** End R12 }} ***/
5403 CURSOR c_item IS
5404 SELECT *
5405 FROM mtl_system_items
5406 WHERE inventory_Item_Id = p_orig_rsv_rec.inventory_item_id;
5407 BEGIN
5408 --
5409 -- First validate whether minimum number of arguments are provided for the
5410 -- listed reservation action. If not error out right away.
5411 --
5412
5413 IF (g_debug IS NULL) THEN
5414 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
5415 END IF;
5416
5417 l_debug := g_debug;
5418
5419 IF p_rsv_action_name = 'CREATE' THEN
5420 -- validate item and organization information
5421 validate_organization
5422 (
5423 x_return_status => l_return_status
5424 , p_organization_id => p_orig_rsv_rec.organization_id
5425 , x_org_cache_index => l_orig_org_cache_index
5426 );
5427 --
5428 IF l_return_status = fnd_api.g_ret_sts_error THEN
5429 RAISE fnd_api.g_exc_error;
5430 END IF ;
5431 --
5432 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
5433 RAISE fnd_api.g_exc_unexpected_error;
5434 END IF;
5435 --
5436 validate_item
5437 (
5438 x_return_status => l_return_status
5439 , p_inventory_item_id => p_orig_rsv_rec.inventory_item_id
5440 , p_organization_id => p_orig_rsv_rec.organization_id
5441 , x_item_cache_index => l_orig_item_cache_index
5442 );
5443 --
5444 IF l_return_status = fnd_api.g_ret_sts_error THEN
5445 RAISE fnd_api.g_exc_error;
5446 END IF ;
5447 --
5448 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
5449 RAISE fnd_api.g_exc_unexpected_error;
5450 END IF;
5451 --
5452 validate_demand_source
5453 (
5454 x_return_status => l_return_status
5455 , p_rsv_action_name => p_rsv_action_name
5456 , p_inventory_item_id => p_orig_rsv_rec.inventory_item_id
5457 , p_organization_id => p_orig_rsv_rec.organization_id
5458 , p_demand_source_type_id => p_orig_rsv_rec.demand_source_type_id
5459 , p_demand_source_header_id => p_orig_rsv_rec.demand_source_header_id
5460 , p_demand_source_line_id => p_orig_rsv_rec.demand_source_line_id
5461 , p_demand_source_line_detail => p_orig_rsv_rec.demand_source_line_detail
5462 , p_orig_demand_source_type_id => NULL
5463 , p_orig_demand_source_header_id => NULL
5464 , p_orig_demand_source_line_id => NULL
5465 , p_orig_demand_source_detail => NULL
5466 , p_demand_source_name => p_orig_rsv_rec.demand_source_name
5467 , p_reservation_id => p_orig_rsv_rec.reservation_id /*** {{ R12 Enhanced reservations code changes ***/
5468 , p_reservation_quantity => p_orig_rsv_rec.reservation_quantity
5469 , p_reservation_uom_code => p_orig_rsv_rec.reservation_uom_code
5470 , p_supply_type_id => p_orig_rsv_rec.supply_source_type_id
5471 , p_demand_ship_date => p_orig_rsv_rec.demand_ship_date
5472 , p_supply_receipt_date => p_orig_rsv_rec.supply_receipt_date /*** End R12 }} ***/
5473 , x_demand_cache_index => l_orig_demand_cache_index
5474 , p_substitute_flag => p_substitute_flag /* Bug 6044651 */
5475 );
5476 IF l_return_status = fnd_api.g_ret_sts_error THEN
5477 RAISE fnd_api.g_exc_error;
5478 END IF ;
5479 --
5480 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
5481 RAISE fnd_api.g_exc_unexpected_error;
5482 END IF;
5483 --
5484
5485 -- Bug: 4661026: Passing the requirement date if the demand ship date
5486 -- is null
5487 IF (p_orig_rsv_rec.supply_source_type_id = inv_reservation_global.g_source_type_wip)
5488 THEN
5489 l_demand_ship_date := Nvl(p_orig_rsv_rec.demand_ship_date,p_orig_rsv_rec.requirement_date);
5490
5491 END IF;
5492
5493 validate_supply_source
5494 (
5495 x_return_status => l_return_status
5496 , p_inventory_item_id => p_orig_rsv_rec.inventory_item_id
5497 , p_organization_id => p_orig_rsv_rec.organization_id
5498 , p_supply_source_type_id => p_orig_rsv_rec.supply_source_type_id
5499 , p_supply_source_header_id => p_orig_rsv_rec.supply_source_header_id
5500 , p_supply_source_line_id => p_orig_rsv_rec.supply_source_line_id
5501 , p_supply_source_line_detail => p_orig_rsv_rec.supply_source_line_detail
5502 , p_supply_source_name => p_orig_rsv_rec.supply_source_name
5503 , p_demand_source_type_id => p_orig_rsv_rec.demand_source_type_id
5504 , p_revision => p_orig_rsv_rec.revision
5505 , p_lot_number => p_orig_rsv_rec.lot_number
5506 , p_subinventory_code => p_orig_rsv_rec.subinventory_code
5507 , p_locator_id => p_orig_rsv_rec.locator_id
5508 , p_serial_array => p_orig_serial_array
5509 , p_demand_ship_date => l_demand_ship_date
5510 , p_supply_receipt_date => p_orig_rsv_rec.supply_receipt_date
5511 , p_item_cache_index => l_orig_item_cache_index
5512 , p_org_cache_index => l_orig_org_cache_index
5513 , x_supply_cache_index => l_orig_supply_cache_index
5514 , x_sub_cache_index => l_orig_sub_cache_index
5515 );
5516 IF l_return_status = fnd_api.g_ret_sts_error THEN
5517 RAISE fnd_api.g_exc_error;
5518 END IF ;
5519 --
5520 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
5521 RAISE fnd_api.g_exc_unexpected_error;
5522 END IF;
5523 --
5524 IF p_orig_serial_array.COUNT > 0 THEN
5525 l_has_serial_number := fnd_api.g_true;
5526 ELSE
5527 l_has_serial_number := fnd_api.g_false;
5528 END IF;
5529 --
5530 -- INVCONV BEGIN
5531 -- Extend validations to cover secondary quantity
5532 validate_quantity
5533 (
5534 x_return_status => l_return_status
5535 , p_primary_uom => p_orig_rsv_rec.primary_uom_code
5536 , p_primary_quantity
5537 => p_orig_rsv_rec.primary_reservation_quantity
5538 , p_secondary_uom => p_orig_rsv_rec.secondary_uom_code -- INVCONV
5539 , p_secondary_quantity
5540 => p_orig_rsv_rec.secondary_reservation_quantity -- INVCONV
5541 , p_reservation_uom => p_orig_rsv_rec.reservation_uom_code
5542 , p_reservation_quantity => p_orig_rsv_rec.reservation_quantity
5543 , p_lot_number => p_orig_rsv_rec.lot_number -- INVCONV
5544 , p_has_serial_number => l_has_serial_number
5545 , p_item_cache_index => l_orig_item_cache_index -- INVCONV
5546 );
5547 -- INVCONV END
5548 IF l_return_status = fnd_api.g_ret_sts_error THEN
5549 RAISE fnd_api.g_exc_error;
5550 END IF ;
5551 --
5552
5553 /*** {{ R12 Enhanced reservations code changes ***/
5554 create_crossdock_reservation
5555 (
5556 x_return_status => l_return_status
5557 , x_msg_count => l_msg_count
5558 , x_msg_data => l_msg_data
5559 , p_rsv_rec => p_orig_rsv_rec
5560 );
5561
5562 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
5563 RAISE fnd_api.g_exc_error;
5564 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
5565 RAISE fnd_api.g_exc_unexpected_error;
5566 END IF;
5567
5568 validate_pjm_reservations
5569 (
5570 x_return_status => l_return_status
5571 , p_organization_id => p_orig_rsv_rec.organization_id
5572 , p_inventory_item_id => p_orig_rsv_rec.inventory_item_id
5573 , p_supply_source_type_id => p_orig_rsv_rec.supply_source_type_id
5574 , p_supply_source_header_id => p_orig_rsv_rec.supply_source_header_id
5575 , p_supply_source_line_id => p_orig_rsv_rec.supply_source_line_id
5576 , p_supply_source_line_detail => p_orig_rsv_rec.supply_source_line_detail
5577 , p_project_id => p_orig_rsv_rec.project_id
5578 , p_task_id => p_orig_rsv_rec.task_id
5579 );
5580
5581 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
5582 RAISE fnd_api.g_exc_error;
5583 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
5584 RAISE fnd_api.g_exc_unexpected_error;
5585 END IF;
5586
5587 validate_serials
5588 (
5589 x_return_status => l_return_status
5590 , p_orig_rsv_rec => p_orig_rsv_rec
5591 , p_to_rsv_rec => p_to_rsv_rec
5592 , p_orig_serial_array => p_orig_serial_array
5593 , p_to_serial_array => p_to_serial_array
5594 , p_rsv_action_name => p_rsv_action_name
5595 );
5596
5597 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
5598 RAISE fnd_api.g_exc_error;
5599 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
5600 RAISE fnd_api.g_exc_unexpected_error;
5601 END IF;
5602 /*** End R12 }} ***/
5603
5604 /*** {{ R12 Enhanced reservations code changes
5605 -- call from validate_demand_source
5606 validate_sales_order
5607 (
5608 x_return_status => l_return_status
5609 , p_reservation_id => p_orig_rsv_rec.reservation_id
5610 , p_demand_type_id => p_orig_rsv_rec.demand_source_type_id
5611 , p_demand_header_id => p_orig_rsv_rec.demand_source_header_id
5612 , p_demand_line_id => p_orig_rsv_rec.demand_source_line_id
5613 , p_reservation_quantity => p_orig_rsv_rec.reservation_quantity
5614 , p_reservation_uom_code => p_orig_rsv_rec.reservation_uom_code
5615 , p_reservation_item_id => p_orig_rsv_rec.inventory_item_id
5616 , p_reservation_org_id => p_orig_rsv_rec.organization_id
5617 );
5618 IF l_return_status = fnd_api.g_ret_sts_error THEN
5619 RAISE fnd_api.g_exc_error;
5620 END IF;
5621 --
5622 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
5623 RAISE fnd_api.g_exc_unexpected_error;
5624 END IF;
5625 *** End R12 }} ***/
5626
5627 -- check to see if there are existing crossdock reservations against this
5628 --wip job for a different demand. If so, fail.
5629 IF (p_orig_rsv_rec.supply_source_type_id =
5630 INV_RESERVATION_GLOBAL.G_SOURCE_TYPE_WIP) THEN
5631 IF (l_debug = 1) THEN
5632 debug_print('checked wip');
5633 END IF;
5634 -- Bug 4608452: Get the wip entity type to check for existing reservations
5635 IF (l_debug = 1) THEN
5636 debug_print('Before wip job validation');
5637 END IF;
5638
5639 /*** Get the wip entity type ***/
5640 -- get wip entity id from wip_record_cache
5641 inv_reservation_util_pvt.get_wip_cache
5642 (
5643 x_return_status => l_return_status
5644 , p_wip_entity_id => p_orig_rsv_rec.supply_source_header_id
5645 );
5646
5647 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
5648 RAISE fnd_api.g_exc_error;
5649 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
5650 RAISE fnd_api.g_exc_unexpected_error;
5651 ELSE
5652 l_wip_entity_type := inv_reservation_global.g_wip_record_cache(p_orig_rsv_rec.supply_source_header_id).wip_entity_type;
5653 l_wip_job_type := inv_reservation_global.g_wip_record_cache(p_orig_rsv_rec.supply_source_header_id).wip_entity_job;
5654 END IF;
5655
5656 IF (l_wip_entity_type = inv_reservation_global.g_wip_source_type_discrete) THEN
5657 IF (l_debug = 1) THEN
5658 debug_print('inside wip');
5659 END IF;
5660 BEGIN
5661 SELECT distinct
5662 inv_salesorder.get_salesorder_for_oeheader(wdd.source_header_id),
5663 wdd.source_line_id INTO l_demand_source_header_id, l_demand_source_line_id
5664 FROM mtl_txn_request_lines mtrl, wms_license_plate_numbers wlpn,
5665 wsh_delivery_details wdd
5666 WHERE mtrl.organization_id = p_orig_rsv_rec.organization_id
5667 AND mtrl.inventory_item_id = p_orig_rsv_rec.inventory_item_id
5668 AND mtrl.line_status <> 5 -- not closed move order lines
5669 AND NVL(mtrl.quantity_delivered, 0) = 0
5670 AND mtrl.txn_source_id = p_orig_rsv_rec.supply_source_header_id
5671 AND mtrl.lpn_id = wlpn.lpn_id
5672 AND wlpn.lpn_context = 2 -- WIP LPN
5673 AND mtrl.crossdock_type = 1 -- Crossdocked to OE demand
5674 AND mtrl.backorder_delivery_detail_id IS NOT NULL
5675 AND mtrl.backorder_delivery_detail_id =
5676 wdd.delivery_detail_id;
5677 EXCEPTION
5678 WHEN no_data_found THEN
5679 IF (l_debug = 1) THEN
5680 debug_print('No records found for this WIP job that has been crossdocked');
5681 END IF;
5682 END;
5683
5684 IF (l_demand_source_header_id <>
5685 p_orig_rsv_rec.demand_source_header_id) OR
5686 (l_demand_source_line_id <>
5687 p_orig_rsv_rec.demand_source_line_id) THEN
5688 IF (l_debug = 1) THEN
5689 debug_print('Job already has a crossdocked reservation for a different demand');
5690 fnd_message.set_name('INV', 'INV_INVALID_DEMAND_SOURCE');
5691 fnd_msg_pub.add;
5692 RAISE fnd_api.g_exc_error;
5693 END IF;
5694 END IF;
5695 END IF;
5696 END IF;
5697 --
5698 ELSE -- if we don't do validation, still need to populate item cache
5699 --the item cache info is used to created the quantity tree
5700 inv_reservation_util_pvt.search_item_cache
5701 (
5702 x_return_status => l_return_status
5703 ,p_inventory_item_id => p_orig_rsv_rec.inventory_item_id
5704 ,p_organization_id => p_orig_rsv_rec.organization_id
5705 ,x_index => l_orig_item_cache_index
5706 );
5707 --
5708 If l_return_status = fnd_api.g_ret_sts_error Then
5709 RAISE fnd_api.g_exc_error;
5710 End If;
5711 --
5712 If l_return_status = fnd_api.g_ret_sts_unexp_error Then
5713 RAISE fnd_api.g_exc_unexpected_error;
5714 End If;
5715 --
5716 --if item isn't in cache, need to add it
5717 If l_orig_item_cache_index IS NULL Then
5718 OPEN c_item;
5719 FETCH c_item into l_item_rec;
5720 CLOSE c_item;
5721
5722 inv_reservation_util_pvt.add_item_cache
5723 (
5724 x_return_status => l_return_status
5725 ,p_item_record => l_item_rec
5726 ,x_index => l_orig_item_cache_index
5727 );
5728 --
5729 if l_return_status = fnd_api.g_ret_sts_error then
5730 RAISE fnd_api.g_exc_error;
5731 end if;
5732 --
5733 if l_return_status = fnd_api.g_ret_sts_unexp_error then
5734 RAISE fnd_api.g_exc_unexpected_error;
5735 end if;
5736 End If;
5737 END IF;
5738 --
5739 IF p_rsv_action_name IN ('UPDATE', 'TRANSFER') THEN
5740 -- validate item and organization information
5741 validate_organization
5742 (
5743 x_return_status => l_return_status
5744 , p_organization_id => p_to_rsv_rec.organization_id
5745 , x_org_cache_index => l_to_org_cache_index
5746 );
5747 --
5748 IF l_return_status = fnd_api.g_ret_sts_error THEN
5749 RAISE fnd_api.g_exc_error;
5750 END IF ;
5751 --
5752 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
5753 RAISE fnd_api.g_exc_unexpected_error;
5754 END IF;
5755 --
5756 validate_item
5757 (
5758 x_return_status => l_return_status
5759 , p_inventory_item_id => p_to_rsv_rec.inventory_item_id
5760 , p_organization_id => p_to_rsv_rec.organization_id
5761 , x_item_cache_index => l_to_item_cache_index
5762 );
5763 --
5764 IF l_return_status = fnd_api.g_ret_sts_error THEN
5765 RAISE fnd_api.g_exc_error;
5766 END IF ;
5767 --
5768 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
5769 RAISE fnd_api.g_exc_unexpected_error;
5770 END IF;
5771 --
5772 validate_demand_source
5773 (
5774 x_return_status => l_return_status
5775 , p_rsv_action_name => p_rsv_action_name
5776 , p_inventory_item_id => p_to_rsv_rec.inventory_item_id
5777 , p_organization_id => p_to_rsv_rec.organization_id
5778 , p_demand_source_type_id => p_to_rsv_rec.demand_source_type_id
5779 , p_demand_source_header_id => p_to_rsv_rec.demand_source_header_id
5780 , p_demand_source_line_id => p_to_rsv_rec.demand_source_line_id
5781 , p_demand_source_line_detail =>
5782 p_to_rsv_rec.demand_source_line_detail
5783 , p_orig_demand_source_type_id => p_orig_rsv_rec.demand_source_type_id
5784 , p_orig_demand_source_header_id => p_orig_rsv_rec.demand_source_header_id
5785 , p_orig_demand_source_line_id => p_orig_rsv_rec.demand_source_line_id
5786 , p_orig_demand_source_detail => p_orig_rsv_rec.demand_source_line_detail
5787 , p_demand_source_name => p_to_rsv_rec.demand_source_name
5788 , p_reservation_id => p_to_rsv_rec.reservation_id /*** {{ R12 Enhanced reservations code changes ***/
5789 , p_reservation_quantity => p_to_rsv_rec.reservation_quantity
5790 , p_reservation_uom_code => p_to_rsv_rec.reservation_uom_code
5791 , p_supply_type_id => p_to_rsv_rec.supply_source_type_id
5792 , p_demand_ship_date => p_orig_rsv_rec.demand_ship_date
5793 , p_supply_receipt_date => p_orig_rsv_rec.supply_receipt_date /*** End R12 }} ***/
5794 , x_demand_cache_index => l_to_demand_cache_index
5795 );
5796 IF l_return_status = fnd_api.g_ret_sts_error THEN
5797 RAISE fnd_api.g_exc_error;
5798 END IF ;
5799 --
5800 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
5801 RAISE fnd_api.g_exc_unexpected_error;
5802 END IF;
5803 --
5804
5805 -- Bug: 4661026: Passing the requirement date if the demand ship date
5806 -- is null
5807 IF (p_to_rsv_rec.supply_source_type_id = inv_reservation_global.g_source_type_wip)
5808 THEN
5809 l_demand_ship_date := Nvl(p_to_rsv_rec.demand_ship_date,p_to_rsv_rec.requirement_date);
5810
5811 END IF;
5812
5813 validate_supply_source
5814 (
5815 x_return_status => l_return_status
5816 , p_inventory_item_id => p_to_rsv_rec.inventory_item_id
5817 , p_organization_id => p_to_rsv_rec.organization_id
5818 , p_supply_source_type_id => p_to_rsv_rec.supply_source_type_id
5819 , p_supply_source_header_id => p_to_rsv_rec.supply_source_header_id
5820 , p_supply_source_line_id => p_to_rsv_rec.supply_source_line_id
5821 , p_supply_source_line_detail => p_to_rsv_rec.supply_source_line_detail
5822 , p_supply_source_name => p_to_rsv_rec.supply_source_name
5823 , p_demand_source_type_id => p_to_rsv_rec.demand_source_type_id
5824 , p_revision => p_to_rsv_rec.revision
5825 , p_lot_number => p_to_rsv_rec.lot_number
5826 , p_subinventory_code => p_to_rsv_rec.subinventory_code
5827 , p_locator_id => p_to_rsv_rec.locator_id
5828 , p_serial_array => p_to_serial_array
5829 , p_demand_ship_date => l_demand_ship_date
5830 , p_supply_receipt_date => p_to_rsv_rec.supply_receipt_date
5831 , p_item_cache_index => l_to_item_cache_index
5832 , p_org_cache_index => l_to_org_cache_index
5833 , x_supply_cache_index => l_to_supply_cache_index
5834 , x_sub_cache_index => l_to_sub_cache_index
5835 );
5836
5837 IF (l_debug = 1) THEN
5838 debug_print(' After calling validate supply source ' || l_return_status);
5839 END IF;
5840
5841 IF l_return_status = fnd_api.g_ret_sts_error THEN
5842 RAISE fnd_api.g_exc_error;
5843 END IF ;
5844 --
5845 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
5846 RAISE fnd_api.g_exc_unexpected_error;
5847 END IF;
5848 --
5849 IF p_to_serial_array.COUNT > 0 THEN
5850 l_has_serial_number := fnd_api.g_true;
5851 ELSE
5852 l_has_serial_number := fnd_api.g_false;
5853 END IF;
5854 --
5855 -- INVCONV BEGIN
5856 -- Extend validations to cover secondary quantity
5857 validate_quantity
5858 (
5859 x_return_status => l_return_status
5860 , p_primary_uom => p_to_rsv_rec.primary_uom_code
5861 , p_primary_quantity => p_to_rsv_rec.primary_reservation_quantity
5862 , p_secondary_uom => p_to_rsv_rec.secondary_uom_code -- INVCONV
5863 , p_secondary_quantity => p_to_rsv_rec.secondary_reservation_quantity -- INVCONV
5864 , p_reservation_uom => p_to_rsv_rec.reservation_uom_code
5865 , p_reservation_quantity => p_to_rsv_rec.reservation_quantity
5866 , p_lot_number => p_to_rsv_rec.lot_number -- INVCONV
5867 , p_has_serial_number => l_has_serial_number
5868 , p_item_cache_index => l_orig_item_cache_index -- INVCONV
5869 );
5870 -- INVCONV END
5871
5872 IF (l_debug = 1) THEN
5873 debug_print(' After calling validate quantity ' || l_return_status);
5874 END IF;
5875
5876 IF l_return_status = fnd_api.g_ret_sts_error THEN
5877 RAISE fnd_api.g_exc_error;
5878 END IF ;
5879 --
5880 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
5881 RAISE fnd_api.g_exc_unexpected_error;
5882 END IF;
5883
5884 IF p_orig_rsv_rec.inventory_item_id
5885 <> p_to_rsv_rec.inventory_item_id THEN
5886 fnd_message.set_name('INV', 'INVENTORY_ITEM_ID_NOT_THE_SAME');
5887 fnd_msg_pub.add;
5888 RAISE fnd_api.g_exc_error;
5889 END IF;
5890
5891 /*** {{ R12 Enhanced reservations code changes ***/
5892 IF (p_rsv_action_name = 'UPDATE') THEN
5893 update_crossdock_reservation
5894 (
5895 x_return_status => l_return_status
5896 , x_msg_count => l_msg_count
5897 , x_msg_data => l_msg_data
5898 , p_orig_rsv_rec => p_orig_rsv_rec
5899 , p_to_rsv_rec => p_to_rsv_rec
5900 );
5901
5902 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
5903 RAISE fnd_api.g_exc_error;
5904 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
5905 RAISE fnd_api.g_exc_unexpected_error;
5906 END IF;
5907
5908 ELSIF (p_rsv_action_name = 'TRANSFER') THEN
5909 transfer_crossdock_reservation
5910 (
5911 x_return_status => l_return_status
5912 , x_msg_count => l_msg_count
5913 , x_msg_data => l_msg_data
5914 , p_orig_rsv_rec => p_orig_rsv_rec
5915 , p_to_rsv_rec => p_to_rsv_rec
5916 );
5917
5918 IF (l_debug = 1) THEN
5919 debug_print(' After calling validate cossdock xfer ' ||
5920 l_return_status);
5921 END IF;
5922 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
5923 RAISE fnd_api.g_exc_error;
5924 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
5925 RAISE fnd_api.g_exc_unexpected_error;
5926 END IF;
5927 END IF;
5928
5929 validate_pjm_reservations
5930 (
5931 x_return_status => l_return_status
5932 , p_organization_id => p_to_rsv_rec.organization_id
5933 , p_inventory_item_id => p_to_rsv_rec.inventory_item_id
5934 , p_supply_source_type_id => p_to_rsv_rec.supply_source_type_id
5935 , p_supply_source_header_id => p_to_rsv_rec.supply_source_header_id
5936 , p_supply_source_line_id => p_to_rsv_rec.supply_source_line_id
5937 , p_supply_source_line_detail => p_to_rsv_rec.supply_source_line_detail
5938 , p_project_id => p_to_rsv_rec.project_id
5939 , p_task_id => p_to_rsv_rec.task_id
5940 );
5941
5942 IF (l_debug = 1) THEN
5943 debug_print(' After calling validate pjm ' || l_return_status);
5944 END IF;
5945
5946 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
5947 RAISE fnd_api.g_exc_error;
5948 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
5949 RAISE fnd_api.g_exc_unexpected_error;
5950 END IF;
5951
5952 validate_serials
5953 (
5954 x_return_status => l_return_status
5955 , p_orig_rsv_rec => p_orig_rsv_rec
5956 , p_to_rsv_rec => p_to_rsv_rec
5957 , p_orig_serial_array => p_orig_serial_array
5958 , p_to_serial_array => p_to_serial_array
5959 , p_rsv_action_name => p_rsv_action_name
5960 );
5961
5962 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
5963 RAISE fnd_api.g_exc_error;
5964 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
5965 RAISE fnd_api.g_exc_unexpected_error;
5966 END IF;
5967 /*** End R12 }} ***/
5968
5969 --
5970 -- Bug 2025212
5971 -- With Change Management and the way Shipping calls the INV APIs,
5972 -- it becomes necessary in certain situations to update quantity
5973 -- information on reservations against orders that are cancelled.
5974 -- These reservations will soon be canceled or transferred, but
5975 -- the quantity update must happen first.
5976 -- To solve this problem, only validate the sales order on
5977 -- update or transfer if the sales order info has changed.
5978 -- This change will also yield performance improvements.
5979
5980 /*** {{ R12 Enhanced reservations code changes
5981 -- comment out the call to validate_sales_order
5982 -- call from validate_demand_source
5983 IF (p_orig_rsv_rec.demand_source_type_id <>
5984 p_to_rsv_rec.demand_source_type_id) OR
5985 (p_orig_rsv_rec.demand_source_header_id <>
5986 p_to_rsv_rec.demand_source_header_id) OR
5987 (p_orig_rsv_rec.demand_source_line_id <>
5988 p_to_rsv_rec.demand_source_line_id)
5989 THEN
5990
5991 validate_sales_order
5992 (
5993 x_return_status => l_return_status
5994 , p_reservation_id => p_to_rsv_rec.reservation_id
5995 , p_demand_type_id => p_to_rsv_rec.demand_source_type_id
5996 , p_demand_header_id => p_to_rsv_rec.demand_source_header_id
5997 , p_demand_line_id => p_to_rsv_rec.demand_source_line_id
5998 , p_reservation_quantity => p_to_rsv_rec.reservation_quantity
5999 , p_reservation_uom_code => p_to_rsv_rec.reservation_uom_code
6000 , p_reservation_item_id => p_to_rsv_rec.inventory_item_id
6001 , p_reservation_org_id => p_to_rsv_rec.organization_id
6002 );
6003 IF l_return_status = fnd_api.g_ret_sts_error THEN
6004 RAISE fnd_api.g_exc_error;
6005 END IF;
6006 --
6007 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
6008 RAISE fnd_api.g_exc_unexpected_error;
6009 END IF;
6010 --
6011 END IF;
6012 *** End R12 }} ***/
6013 IF (l_debug = 1) THEN
6014 debug_print(' Before checking for existing xdock ' ||
6015 l_return_status);
6016 END IF;
6017
6018 -- check to see if there are existing crossdock reservations against this
6019 --wip job for a different demand. If so, fail.
6020 IF (p_to_rsv_rec.supply_source_type_id = INV_RESERVATION_GLOBAL.G_SOURCE_TYPE_WIP) THEN
6021 -- Bug 4608452: Get the wip entity type to check for existing reservations
6022 /*** Get the wip entity type ***/
6023 -- get wip entity id from wip_record_cache
6024 inv_reservation_util_pvt.get_wip_cache
6025 (
6026 x_return_status => l_return_status
6027 , p_wip_entity_id => p_to_rsv_rec.supply_source_header_id
6028 );
6029
6030 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
6031 RAISE fnd_api.g_exc_error;
6032 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
6033 RAISE fnd_api.g_exc_unexpected_error;
6034 ELSE
6035 l_wip_entity_type := inv_reservation_global.g_wip_record_cache(p_to_rsv_rec.supply_source_header_id).wip_entity_type;
6036 l_wip_job_type := inv_reservation_global.g_wip_record_cache(p_to_rsv_rec.supply_source_header_id).wip_entity_job;
6037 END IF;
6038
6039 IF (l_wip_entity_type = inv_reservation_global.g_wip_source_type_discrete) THEN
6040 BEGIN
6041 SELECT distinct
6042 inv_salesorder.get_salesorder_for_oeheader(wdd.source_header_id),
6043 wdd.source_line_id INTO l_demand_source_header_id, l_demand_source_line_id
6044 FROM mtl_txn_request_lines mtrl, wms_license_plate_numbers wlpn,
6045 wsh_delivery_details wdd
6046 WHERE mtrl.organization_id = p_to_rsv_rec.organization_id
6047 AND mtrl.inventory_item_id = p_to_rsv_rec.inventory_item_id
6048 AND mtrl.line_status <> 5 -- not closed move order lines
6049 AND NVL(mtrl.quantity_delivered, 0) = 0
6050 AND mtrl.txn_source_id = p_to_rsv_rec.supply_source_header_id
6051 AND mtrl.lpn_id = wlpn.lpn_id
6052 AND wlpn.lpn_context = 2 -- WIP LPN
6053 AND mtrl.crossdock_type = 1 -- Crossdocked to OE demand
6054 AND mtrl.backorder_delivery_detail_id IS NOT NULL
6055 AND mtrl.backorder_delivery_detail_id =
6056 wdd.delivery_detail_id;
6057 EXCEPTION
6058 WHEN no_data_found THEN
6059 IF (l_debug = 1) THEN
6060 debug_print('No records found for this WIP job that has been crossdocked');
6061 END IF;
6062 END;
6063
6064 IF (l_demand_source_header_id <>
6065 p_to_rsv_rec.demand_source_header_id) OR
6066 (l_demand_source_line_id <>
6067 p_to_rsv_rec.demand_source_line_id) THEN
6068 IF (l_debug = 1) THEN
6069 debug_print('Job already has a crossdocked reservation for a different demand');
6070 debug_print(' Reservations exist for sales order header'
6071 || l_demand_source_header_id);
6072 debug_print(' Reservations exist for sales order line'
6073 || l_demand_source_line_id);
6074 fnd_message.set_name('INV', 'INV_INVALID_DEMAND_SOURCE');
6075 fnd_msg_pub.add;
6076 RAISE fnd_api.g_exc_error;
6077 END IF;
6078 END IF;
6079 END IF;
6080 END IF;
6081 IF (l_debug = 1) THEN
6082 debug_print(' end of update/ transfer ' || l_return_status);
6083 END IF;
6084 END IF;
6085 --
6086
6087 /*** {{ R12 Enhanced reservations code changes ***/
6088 IF (p_rsv_action_name = 'RELIEVE') THEN
6089 IF (l_debug = 1) THEN
6090 debug_print('Inside validate when relieve');
6091 END IF;
6092
6093 validate_organization
6094 (
6095 x_return_status => l_return_status
6096 , p_organization_id => p_orig_rsv_rec.organization_id
6097 , x_org_cache_index => l_orig_org_cache_index
6098 );
6099 --
6100 IF l_return_status = fnd_api.g_ret_sts_error THEN
6101 RAISE fnd_api.g_exc_error;
6102 END IF ;
6103 --
6104 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
6105 RAISE fnd_api.g_exc_unexpected_error;
6106 END IF;
6107
6108 validate_serials
6109 (
6110 x_return_status => l_return_status
6111 , p_orig_rsv_rec => p_orig_rsv_rec
6112 , p_to_rsv_rec => p_to_rsv_rec
6113 , p_orig_serial_array => p_orig_serial_array
6114 , p_to_serial_array => p_to_serial_array
6115 , p_rsv_action_name => p_rsv_action_name
6116 );
6117
6118 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
6119 RAISE fnd_api.g_exc_error;
6120 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
6121 RAISE fnd_api.g_exc_unexpected_error;
6122 END IF;
6123
6124 /*** for relieve crossdock reservation, call
6125 --- update_crossdock_reservation instead of relieve
6126 --- with to reservation record populated
6127 relieve_crossdock_reservation
6128 (
6129 x_return_status => l_return_status
6130 , x_msg_count => l_msg_count
6131 , x_msg_data => l_msg_data
6132 , p_rsv_rec => p_orig_rsv_rec
6133 );
6134 ****/
6135
6136 update_crossdock_reservation
6137 (
6138 x_return_status => l_return_status
6139 , x_msg_count => l_msg_count
6140 , x_msg_data => l_msg_data
6141 , p_orig_rsv_rec => p_orig_rsv_rec
6142 , p_to_rsv_rec => p_to_rsv_rec
6143 );
6144
6145 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
6146 RAISE fnd_api.g_exc_error;
6147 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
6148 RAISE fnd_api.g_exc_unexpected_error;
6149 END IF;
6150 END IF;
6151 /*** End R12 }} ***/
6152
6153 /*** {{ R12 Enhanced reservations code changes ***/
6154 IF (p_rsv_action_name = 'DELETE') THEN
6155
6156 validate_organization
6157 (
6158 x_return_status => l_return_status
6159 , p_organization_id => p_orig_rsv_rec.organization_id
6160 , x_org_cache_index => l_orig_org_cache_index
6161 );
6162 --
6163 IF l_return_status = fnd_api.g_ret_sts_error THEN
6164 RAISE fnd_api.g_exc_error;
6165 END IF ;
6166 --
6167 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
6168 RAISE fnd_api.g_exc_unexpected_error;
6169 END IF;
6170
6171 validate_serials
6172 (
6173 x_return_status => l_return_status
6174 , p_orig_rsv_rec => p_orig_rsv_rec
6175 , p_to_rsv_rec => p_to_rsv_rec
6176 , p_orig_serial_array => p_orig_serial_array
6177 , p_to_serial_array => p_to_serial_array
6178 , p_rsv_action_name => p_rsv_action_name
6179 );
6180
6181 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
6182 RAISE fnd_api.g_exc_error;
6183 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
6184 RAISE fnd_api.g_exc_unexpected_error;
6185 END IF;
6186
6187 delete_crossdock_reservation
6188 (
6189 x_return_status => l_return_status
6190 , x_msg_count => l_msg_count
6191 , x_msg_data => l_msg_data
6192 , p_rsv_rec => p_orig_rsv_rec
6193 );
6194
6195 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
6196 RAISE fnd_api.g_exc_error;
6197 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
6198 RAISE fnd_api.g_exc_unexpected_error;
6199 END IF;
6200 END IF;
6201 /*** End R12 }} ***/
6202 IF (l_debug = 1) THEN
6203 debug_print(' end validate input ' || l_return_status);
6204 END IF;
6205 x_orig_item_cache_index := l_orig_item_cache_index;
6206 x_orig_org_cache_index := l_orig_org_cache_index;
6207 x_orig_demand_cache_index := l_orig_demand_cache_index;
6208 x_orig_supply_cache_index := l_orig_supply_cache_index;
6209 x_orig_sub_cache_index := l_orig_sub_cache_index;
6210 x_to_item_cache_index := l_to_item_cache_index;
6211 x_to_org_cache_index := l_to_org_cache_index;
6212 x_to_demand_cache_index := l_to_demand_cache_index;
6213 x_to_supply_cache_index := l_to_supply_cache_index;
6214 x_to_sub_cache_index := l_to_sub_cache_index;
6215 --
6216 x_return_status := l_return_status;
6217 --
6218 EXCEPTION
6219 WHEN fnd_api.g_exc_error THEN
6220 x_return_status := fnd_api.g_ret_sts_error;
6221 --
6222 WHEN fnd_api.g_exc_unexpected_error THEN
6223 x_return_status := fnd_api.g_ret_sts_unexp_error ;
6224 --
6225 WHEN OTHERS THEN
6226 x_return_status := fnd_api.g_ret_sts_unexp_error ;
6227 --
6228 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
6229 THEN
6230 fnd_msg_pub.add_exc_msg
6231 ( g_pkg_name
6232 , 'Validate_Input_Parameters'
6233 );
6234 END IF;
6235 --
6236 END validate_input_parameters;
6237 END inv_reservation_validate_pvt;