[Home] [Help]
PACKAGE BODY: APPS.INV_RESERVATION_VALIDATE_PVT
Source
1 PACKAGE BODY inv_reservation_validate_pvt AS
2 /* $Header: INVRSV1B.pls 120.32.12020000.6 2013/04/03 06:46:29 brana 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 /* Added following procedure for bug 13524480 */
1486
1487 -- {{Procedure
1488 -- validate_supply_source_cmro
1489 -- Description
1490 -- Validation for supply source of cmro
1491 -- if the item is replenish to order, then return error. }}
1492 PROCEDURE validate_supply_source_cmro
1493 (
1494 x_return_status OUT NOCOPY VARCHAR2
1495 , p_organization_id IN NUMBER
1496 , p_inventory_item_id IN NUMBER
1497 , p_demand_ship_date IN DATE
1498 , p_supply_receipt_date IN DATE
1499 , p_supply_source_type_id IN NUMBER
1500 , p_supply_source_header_id IN NUMBER
1501 , p_supply_source_line_id IN NUMBER
1502 , p_supply_source_line_detail IN NUMBER
1503 , p_wip_entity_type IN NUMBER
1504 ) IS
1505
1506 l_replenish_to_order VARCHAR2(1) := 'N';
1507 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1508 l_msg_count NUMBER;
1509 l_msg_data VARCHAR2(1000);
1510 l_valid_status VARCHAR2(1);
1511 l_debug NUMBER;
1512 BEGIN
1513
1514 IF (g_debug IS NULL) THEN
1515 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1516 END IF;
1517
1518 l_debug := g_debug;
1519
1520 IF (l_debug = 1) THEN
1521 debug_print('In validate_supply_source_cmro, supply_source_type_id = ' || p_supply_source_type_id);
1522 END IF;
1523
1524 IF (p_supply_source_type_id = INV_RESERVATION_GLOBAL.G_SOURCE_TYPE_WIP) THEN
1525 IF (p_wip_entity_type = INV_RESERVATION_GLOBAL.G_WIP_SOURCE_TYPE_CMRO) THEN
1526
1527 AHL_INV_RESERVATIONS_GRP.validate_supply_demand
1528 (
1529 x_return_status => l_return_status
1530 , x_msg_count => l_msg_count
1531 , x_msg_data => l_msg_data
1532 , x_valid_status => l_valid_status
1533 , p_organization_id => p_organization_id
1534 , p_item_id => p_inventory_item_id
1535 , p_supply_demand_code => 1 -- Supply source
1536 , p_supply_demand_type_id => p_supply_source_type_id
1537 , p_supply_demand_header_id => p_supply_source_header_id
1538 , p_supply_demand_line_id => p_supply_source_line_id
1539 , p_supply_demand_line_detail => NULL
1540 , p_demand_ship_date => p_demand_ship_date
1541 , p_expected_receipt_date => p_supply_receipt_date
1542 , p_api_version_number => 1.0
1543 , p_init_msg_lst => fnd_api.g_false
1544 );
1545
1546
1547 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
1548 RAISE fnd_api.g_exc_error;
1549 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
1550 RAISE fnd_api.g_exc_unexpected_error;
1551 END IF;
1552
1553 IF (l_debug = 1) THEN
1554 debug_print('validate supply demand returns valid status: ' || l_valid_status);
1555 END IF;
1556
1557 IF (l_valid_status = 'N') THEN
1558 fnd_message.set_name('INV', 'INV_RSV_INVALID_SUPPLY_CMRO');
1559 fnd_msg_pub.ADD;
1560 RAISE fnd_api.g_exc_error;
1561 END IF;
1562
1563 ELSE
1564 IF (l_debug = 1) THEN
1565 debug_print('The wip entity type is not CMRO');
1566 END IF;
1567 RAISE fnd_api.g_exc_error;
1568 END IF;
1569 ELSE
1570 IF (l_debug = 1) THEN
1571 debug_print('The transation source type is not WIP');
1572 END IF;
1573 RAISE fnd_api.g_exc_error;
1574
1575 END IF;
1576
1577 x_return_status := l_return_status;
1578 EXCEPTION
1579 WHEN fnd_api.g_exc_error THEN
1580 x_return_status := fnd_api.g_ret_sts_error;
1581 --
1582 WHEN fnd_api.g_exc_unexpected_error THEN
1583 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1584 --
1585 WHEN OTHERS THEN
1586 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1587 --
1588 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1589 THEN
1590 fnd_msg_pub.add_exc_msg
1591 ( g_pkg_name
1592 , 'validate_supply_source_cmro'
1593 );
1594 END IF;
1595
1596 END validate_supply_source_cmro;
1597
1598 -- {{Procedure
1599 -- validate_demand_source_so
1600 -- Description
1601 -- Validation for demand source of sales order
1602 -- if it is drop ship sales order line, then return error. }}
1603 PROCEDURE validate_demand_source_so
1604 (
1605 x_return_status OUT NOCOPY VARCHAR2
1606 , p_demand_source_type_id IN NUMBER
1607 , p_demand_source_header_id IN NUMBER
1608 , p_demand_source_line_id IN NUMBER
1609 , p_demand_source_line_detail IN NUMBER
1610 ) IS
1611
1612 l_dropship_count NUMBER := 0;
1613 l_debug NUMBER;
1614 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1615
1616 BEGIN
1617
1618 IF (g_debug IS NULL) THEN
1619 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1620 END IF;
1621
1622 l_debug := g_debug;
1623
1624 IF (l_debug = 1) THEN
1625 debug_print('In validate_demand_source_so: demand_source_type_id = ' || p_demand_source_type_id);
1626 debug_print('In validate_demand_source_so: oe_ds_pvt.g_ds_soissue_flag = ' || OE_DS_PVT.G_DS_SOISSUE_FLAG );
1627 END IF;
1628
1629 IF (p_demand_source_type_id = INV_RESERVATION_GLOBAL.G_SOURCE_TYPE_OE and OE_DS_PVT.G_DS_SOISSUE_FLAG <> 'Y') THEN
1630 select count(1)
1631 into l_dropship_count
1632 from oe_drop_ship_sources
1633 where header_id = p_demand_source_header_id
1634 and line_id = p_demand_source_line_id;
1635
1636 IF (l_debug = 1) THEN
1637 debug_print('l_dropship_count = ' || l_dropship_count);
1638 END IF;
1639
1640 IF (l_dropship_count >= 1) THEN
1641 fnd_message.set_name('INV', 'INV_RSV_DS_SO');
1642 fnd_msg_pub.add;
1643 RAISE fnd_api.g_exc_error;
1644 END IF;
1645
1646 ELSE
1647 IF (l_debug = 1) THEN
1648 debug_print('The transation source type is not sales order');
1649 END IF;
1650 RAISE fnd_api.g_exc_error;
1651
1652 END IF;
1653 IF (l_debug = 1) THEN
1654 debug_print('After drop ship check ' || p_demand_source_type_id);
1655 END IF;
1656 x_return_status := l_return_status;
1657
1658 EXCEPTION
1659 WHEN fnd_api.g_exc_error THEN
1660 x_return_status := fnd_api.g_ret_sts_error;
1661 --
1662 WHEN fnd_api.g_exc_unexpected_error THEN
1663 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1664 --
1665 WHEN OTHERS THEN
1666 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1667 --
1668 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1669 THEN
1670 fnd_msg_pub.add_exc_msg
1671 ( g_pkg_name
1672 , 'Validate_Demand_Source_SO'
1673 );
1674 END IF;
1675 --
1676 END validate_demand_source_so;
1677
1678 -- {{Procedure
1679 -- validate_demand_source_cmro
1680 -- Description
1681 -- Validation for demand source of CMRO
1682 -- return error is the document is invalid. }}
1683 PROCEDURE validate_demand_source_cmro
1684 (
1685 x_return_status OUT NOCOPY VARCHAR2
1686 , p_organization_id IN NUMBER
1687 , p_inventory_item_id IN NUMBER
1688 , p_demand_ship_date IN DATE
1689 , p_supply_receipt_date IN DATE
1690 , p_demand_source_type_id IN NUMBER
1691 , p_demand_source_header_id IN NUMBER
1692 , p_demand_source_line_id IN NUMBER
1693 , p_demand_source_line_detail IN NUMBER
1694 , p_wip_entity_type IN NUMBER
1695 ) IS
1696
1697 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1698 l_msg_count NUMBER;
1699 l_msg_data VARCHAR2(1000);
1700 l_valid_status VARCHAR2(1);
1701 l_debug NUMBER;
1702
1703 BEGIN
1704
1705 IF (g_debug IS NULL) THEN
1706 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1707 END IF;
1708
1709 l_debug := g_debug;
1710
1711 IF (l_debug = 1) THEN
1712 debug_print('In validate_demand_source_cmro: demand_source_type_id = ' || p_demand_source_type_id);
1713 debug_print('demand_source_header_id = ' || p_demand_source_header_id);
1714 debug_print('demand_source_line_id = ' || p_demand_source_line_id);
1715 debug_print('demand_source_line_detail = ' || p_demand_source_line_detail);
1716 debug_print('wip_entity_type = ' || p_wip_entity_type);
1717 END IF;
1718
1719 IF (p_demand_source_type_id = inv_reservation_global.g_source_type_wip AND
1720 p_wip_entity_type = inv_reservation_global.g_wip_source_type_cmro) THEN
1721 -- validate document
1722 AHL_INV_RESERVATIONS_GRP.validate_supply_demand
1723 (
1724 x_return_status => l_return_status
1725 , x_msg_count => l_msg_count
1726 , x_msg_data => l_msg_data
1727 , x_valid_status => l_valid_status
1728 , p_organization_id => p_organization_id
1729 , p_item_id => p_inventory_item_id
1730 , p_supply_demand_code => 2
1731 , p_supply_demand_type_id => p_demand_source_type_id
1732 , p_supply_demand_header_id => p_demand_source_header_id
1733 , p_supply_demand_line_id => p_demand_source_line_id
1734 , p_supply_demand_line_detail => p_demand_source_line_detail
1735 , p_demand_ship_date => p_demand_ship_date
1736 , p_expected_receipt_date => p_supply_receipt_date
1737 , p_api_version_number => 1.0
1738 , p_init_msg_lst => fnd_api.g_false
1739 );
1740
1741 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
1742 RAISE fnd_api.g_exc_error;
1743 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
1744 RAISE fnd_api.g_exc_unexpected_error;
1745 END IF;
1746
1747 IF (l_debug = 1) THEN
1748 debug_print('validate supply demand returns valid status: ' || l_valid_status);
1749 END IF;
1750
1751 IF (l_valid_status = 'N') THEN
1752 fnd_message.set_name('INV', 'INV_RSV_INVALID_DEMAND_CMRO');
1753 fnd_msg_pub.ADD;
1754 RAISE fnd_api.g_exc_error;
1755 END IF;
1756 ELSE
1757 -- return error since this is not wip demand source or not CMRO entity type
1758 fnd_message.set_name('INV', 'INV_INVALID_DEMAND_SOURCE');
1759 fnd_msg_pub.ADD;
1760 RAISE fnd_api.g_exc_error;
1761 END IF;
1762
1763 x_return_status := l_return_status;
1764
1765 EXCEPTION
1766 WHEN fnd_api.g_exc_error THEN
1767 x_return_status := fnd_api.g_ret_sts_error;
1768 --
1769 WHEN fnd_api.g_exc_unexpected_error THEN
1770 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1771 --
1772 WHEN OTHERS THEN
1773 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1774 --
1775 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1776 THEN
1777 fnd_msg_pub.add_exc_msg
1778 ( g_pkg_name
1779 , 'Validate_Demand_Source_CMRO'
1780 );
1781 END IF;
1782 --
1783 END validate_demand_source_cmro;
1784
1785 -- {{Procedure
1786 -- validate_demand_source_fpo
1787 -- Description
1788 -- Validation for demand source of FPO
1789 -- return error is the document is invalid. }}
1790 PROCEDURE validate_demand_source_fpo
1791 (
1792 x_return_status OUT NOCOPY VARCHAR2
1793 , p_organization_id IN NUMBER
1794 , p_inventory_item_id IN NUMBER
1795 , p_demand_ship_date IN DATE
1796 , p_supply_receipt_date IN DATE
1797 , p_demand_source_type_id IN NUMBER
1798 , p_demand_source_header_id IN NUMBER
1799 , p_demand_source_line_id IN NUMBER
1800 , p_demand_source_line_detail IN NUMBER
1801 , p_wip_entity_type IN NUMBER
1802 ) IS
1803
1804 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1805 l_msg_count NUMBER;
1806 l_msg_data VARCHAR2(1000);
1807 l_valid_status VARCHAR2(1);
1808 l_debug NUMBER;
1809
1810 BEGIN
1811
1812 IF (g_debug IS NULL) THEN
1813 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1814 END IF;
1815
1816 l_debug := g_debug;
1817
1818 IF (l_debug = 1) THEN
1819 debug_print('In validate_demand_source_cmro: demand_source_type_id = ' || p_demand_source_type_id);
1820 debug_print('demand_source_header_id = ' || p_demand_source_header_id);
1821 debug_print('demand_source_line_id = ' || p_demand_source_line_id);
1822 debug_print('wip_entity_type = ' || p_wip_entity_type);
1823 END IF;
1824
1825 IF (p_demand_source_type_id = inv_reservation_global.g_source_type_wip AND
1826 p_wip_entity_type = inv_reservation_global.g_wip_source_type_fpo) THEN
1827 -- validate document
1828 /*
1829 FPO_package.validate_supply_demand
1830 (
1831 x_return_status => l_return_status
1832 , x_msg_count => l_msg_count
1833 , x_msg_data => l_msg_data
1834 , x_valid_status => l_valid_status
1835 , p_organization_id => p_organization_id
1836 , p_item_id => p_inventory_item_id
1837 , p_supply_demand_code => 2
1838 , p_supply_demand_type_id => p_demand_source_type_id
1839 , p_supply_demand_header_id => p_demand_source_header_id
1840 , p_supply_demand_line_id => p_demand_source_line_id
1841 , p_supply_demand_line_detail => p_demand_source_line_detail
1842 , p_demand_ship_date => p_demand_ship_date
1843 , p_expected_receipt_date => p_supply_receipt_date
1844 , p_api_version_number => 1.0
1845 , p_init_msg_lst => fnd_api.g_false
1846 );
1847 */
1848
1849 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
1850 RAISE fnd_api.g_exc_error;
1851 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
1852 RAISE fnd_api.g_exc_unexpected_error;
1853 END IF;
1854
1855 IF (l_debug = 1) THEN
1856 debug_print('validate supply demand returns valid status: ' || l_valid_status);
1857 END IF;
1858
1859 IF (l_valid_status = 'N') THEN
1860 fnd_message.set_name('INV', 'INV_RSV_INVALID_DEMAND_FPO');
1861 fnd_msg_pub.ADD;
1862 RAISE fnd_api.g_exc_error;
1863 END IF;
1864 ELSE
1865 -- return error since this is not wip demand source or not FPO entity type
1866 fnd_message.set_name('INV', 'INV_INVALID_DEMAND_SOURCE');
1867 fnd_msg_pub.ADD;
1868 RAISE fnd_api.g_exc_error;
1869 END IF;
1870
1871 x_return_status := l_return_status;
1872
1873 EXCEPTION
1874 WHEN fnd_api.g_exc_error THEN
1875 x_return_status := fnd_api.g_ret_sts_error;
1876 --
1877 WHEN fnd_api.g_exc_unexpected_error THEN
1878 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1879 --
1880 WHEN OTHERS THEN
1881 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1882 --
1883 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1884 THEN
1885 fnd_msg_pub.add_exc_msg
1886 ( g_pkg_name
1887 , 'Validate_Demand_Source_FPO'
1888 );
1889 END IF;
1890 --
1891 END validate_demand_source_fpo;
1892
1893 -- {{Procedure
1894 -- validate_demand_source_batch
1895 -- Description
1896 -- Validation for demand source of batch
1897 -- return error is the document is invalid. }}
1898 PROCEDURE validate_demand_source_batch
1899 (
1900 x_return_status OUT NOCOPY VARCHAR2
1901 , p_organization_id IN NUMBER
1902 , p_inventory_item_id IN NUMBER
1903 , p_demand_ship_date IN DATE
1904 , p_supply_receipt_date IN DATE
1905 , p_demand_source_type_id IN NUMBER
1906 , p_demand_source_header_id IN NUMBER
1907 , p_demand_source_line_id IN NUMBER
1908 , p_demand_source_line_detail IN NUMBER
1909 , p_wip_entity_type IN NUMBER
1910 ) IS
1911
1912 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1913 l_msg_count NUMBER;
1914 l_msg_data VARCHAR2(1000);
1915 l_valid_status VARCHAR2(1);
1916 l_debug NUMBER;
1917
1918 BEGIN
1919
1920 IF (g_debug IS NULL) THEN
1921 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1922 END IF;
1923
1924 l_debug := g_debug;
1925
1926 IF (l_debug = 1) THEN
1927 debug_print('In validate_demand_source_cmro: demand_source_type_id = ' || p_demand_source_type_id);
1928 debug_print('demand_source_header_id = ' || p_demand_source_header_id);
1929 debug_print('demand_source_line_id = ' || p_demand_source_line_id);
1930 debug_print('wip_entity_type = ' || p_wip_entity_type);
1931 END IF;
1932
1933 IF (p_demand_source_type_id = inv_reservation_global.g_source_type_wip AND
1934 p_wip_entity_type = inv_reservation_global.g_wip_source_type_batch) THEN
1935 -- validate document
1936 /*
1937 Batch_package.validate_supply_demand
1938 (
1939 x_return_status => l_return_status
1940 , x_msg_count => l_msg_count
1941 , x_msg_data => l_msg_data
1942 , x_valid_status => l_valid_status
1943 , p_organization_id => p_organization_id
1944 , p_item_id => p_inventory_item_id
1945 , p_supply_demand_code => 2
1946 , p_supply_demand_type_id => p_demand_source_type_id
1947 , p_supply_demand_header_id => p_demand_source_header_id
1948 , p_supply_demand_line_id => p_demand_source_line_id
1949 , p_supply_demand_line_detail => p_demand_source_line_detail
1950 , p_demand_ship_date => p_demand_ship_date
1951 , p_expected_receipt_date => p_supply_receipt_date
1952 , p_api_version_number => 1.0
1953 , p_init_msg_lst => fnd_api.g_false
1954 );
1955 */
1956
1957 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
1958 RAISE fnd_api.g_exc_error;
1959 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
1960 RAISE fnd_api.g_exc_unexpected_error;
1961 END IF;
1962
1963 IF (l_debug = 1) THEN
1964 debug_print('validate supply demand returns valid status: ' || l_valid_status);
1965 END IF;
1966
1967 IF (l_valid_status = 'N') THEN
1968 fnd_message.set_name('INV', 'INV_RSV_INVALID_DEMAND_BATCH');
1969 fnd_msg_pub.ADD;
1970 RAISE fnd_api.g_exc_error;
1971 END IF;
1972 ELSE
1973 -- return error since this is not wip demand source or not OPM Batch entity type
1974 fnd_message.set_name('INV', 'INV_INVALID_DEMAND_SOURCE');
1975 fnd_msg_pub.ADD;
1976 RAISE fnd_api.g_exc_error;
1977 END IF;
1978
1979 x_return_status := l_return_status;
1980
1981 EXCEPTION
1982 WHEN fnd_api.g_exc_error THEN
1983 x_return_status := fnd_api.g_ret_sts_error;
1984 --
1985 WHEN fnd_api.g_exc_unexpected_error THEN
1986 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1987 --
1988 WHEN OTHERS THEN
1989 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1990 --
1991 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1992 THEN
1993 fnd_msg_pub.add_exc_msg
1994 ( g_pkg_name
1995 , 'Validate_Demand_Source_Batch'
1996 );
1997 END IF;
1998 --
1999 END validate_demand_source_batch;
2000 /*** End R12 }} ***/
2001
2002 --
2003 --
2004 -- Procedure
2005 -- validate_item_sku
2006 -- Description
2007 -- is valid if all of the following are satisfied
2008 -- 1. if the item is not under predefined serial control, p_serial_array
2009 -- is empty (you can only reserve predefined serial number)
2010 -- 2. if the item is not under lot control, p_lot_number is null
2011 -- 3. if the item is not under revision control, p_revision is null
2012 -- 4. if the item is under revision control, it is not true that
2013 -- p_revision is null and p_subinventory_code or p_locator_id is not null
2014 -- 5. if the item is under lot control, it is not true that p_lot_number
2015 -- is null and p_subinventory_code or p_locator_id is not null
2016 -- 6. if the item is under revision and lot control, it is not true that
2017 -- p_revision is null and p_lot_number is not null
2018 -- 7. p_subinventory_code, if not null, is a valid sub in the organization
2019 -- 8. if p_subinventory_code is not null and locator control is off,
2020 -- p_locator_id is null
2021 -- 9. if p_subinventory_code is null, p_locator_id is null
2022 -- 10. if p_revision is not null, it is a valid revision for the item
2023 -- 11. if p_lot_number is not null, it is a valid lot number for the item
2024 -- and the lot has not expired
2025 -- 12. if p_subiventory_code is not null and the item has restriction on
2026 -- subinventory, p_subiventory_code is a valid sub for the item
2027 -- 13. if p_subiventory_code is not null and the item has no restriction on
2028 -- subinventory, p_subiventory_code is a valid sub (necessary?)
2029 -- 14. if p_subiventory_code is not null, and p_locator_id is not null
2030 -- and the item has restriction on subinventory, p_locator_id
2031 -- is a valid locator for the sub
2032 -- 15. if p_subiventory_code is not null, and p_locator_id is not null
2033 -- and the item has no restriction on subinventory, p_locator_id
2034 -- is a valid locator for the sub
2035 -- 16. if p_serial_array is not empty, all serial number must have
2036 -- valid status
2037 -- INVCONV - Validation added for Inventory Convergence
2038 -- 17. if the item is lot_indivisible (lot_divisible_flag <> 'Y'),
2039 -- the reservation must be detailed to lot level.
2040 --
2041
2042 PROCEDURE validate_item_sku
2043 (
2044 x_return_status OUT NOCOPY VARCHAR2
2045 , p_inventory_item_id IN NUMBER
2046 , p_organization_id IN NUMBER
2047 , p_revision IN VARCHAR2
2048 , p_lot_number IN VARCHAR2
2049 , p_subinventory_code IN VARCHAR2
2050 , p_locator_id IN NUMBER
2051 , p_serial_array IN inv_reservation_global.serial_number_tbl_type
2052 , p_item_cache_index IN INTEGER
2053 , p_org_cache_index IN INTEGER
2054 , x_sub_cache_index OUT NOCOPY INTEGER
2055 ) IS
2056 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
2057 l_resultant_locator_control NUMBER := NULL;
2058 l_loop_index NUMBER := NULL;
2059 l_sub_cache_index NUMBER := NULL;
2060 l_rec inv_reservation_global.sub_record;
2061 l_found VARCHAR2(1);
2062 l_lot_expiration_date DATE;
2063 l_debug NUMBER;
2064 l_default_onhand_status_id NUMBER; -- Bug 6870416
2065 -- Added for common API
2066 l_rec_loc inv_reservation_global.locator_record;
2067 l_rec_serial inv_reservation_global.serial_record;
2068 l_rec_lot inv_reservation_global.lot_record;
2069
2070
2071 BEGIN
2072
2073 l_debug := g_debug;
2074 -- Added for common API
2075 l_rec_loc.inventory_location_id:=p_locator_id;
2076 l_rec_lot.lot_number:=p_lot_number;
2077 --
2078 -- important: org and item should be validated before
2079 -- this procedure (validate_supply) is called
2080 -- since here we do not validate them again
2081 --
2082 -- if the item is not under predefined serial number control
2083 -- and the input serial number array is not empty,
2084 -- raise the error
2085 IF (l_debug = 1) THEN
2086 debug_print('Inside validate item sku: ' || l_return_status);
2087 END IF;
2088
2089 IF inv_reservation_global.g_item_record_cache(p_item_cache_index).serial_number_control_code
2090 NOT IN (inv_reservation_global.g_serial_control_predefined,
2091 inv_reservation_global.g_serial_control_dynamic_inv)
2092 AND p_serial_array.COUNT >0 THEN
2093 fnd_message.set_name('INV', 'INV_EXTRA_SERIAL');
2094 fnd_msg_pub.add;
2095 RAISE fnd_api.g_exc_error;
2096 END IF;
2097 --
2098 IF (l_debug = 1) THEN
2099 debug_print('After item cache: ' || l_return_status);
2100 END IF;
2101 -- if the item is not under lot control
2102 -- and the input lot number is not empty,
2103 -- raise the error
2104 IF inv_reservation_global.g_item_record_cache
2105 (p_item_cache_index).lot_control_code =
2106 inv_reservation_global.g_lot_control_no
2107 AND p_lot_number IS NOT NULL THEN
2108 fnd_message.set_name('INV', 'INV_NO_LOT_CONTROL');
2109 fnd_msg_pub.add;
2110 RAISE fnd_api.g_exc_error;
2111 END IF;
2112 IF (l_debug = 1) THEN
2113 debug_print('After lot cache: ' || l_return_status);
2114 END IF;
2115 --
2116 -- if the item is not under revision control
2117 -- and the input revision is not empty,
2118 -- raise the error
2119 IF inv_reservation_global.g_item_record_cache
2120 (p_item_cache_index).revision_qty_control_code =
2121 inv_reservation_global.g_revision_control_no
2122 AND p_revision IS NOT NULL THEN
2123 fnd_message.set_name('INV', 'INV_NO_REVISION_CONTROL');
2124 fnd_msg_pub.add;
2125 RAISE fnd_api.g_exc_error;
2126 END IF;
2127 --
2128 IF (l_debug = 1) THEN
2129 debug_print('After rev cache: ' || l_return_status);
2130 END IF;
2131 -- if the item is under revision control
2132 -- and the input revision is null but subinventory_code or locator_id is
2133 -- not null, raise the error
2134 IF inv_reservation_global.g_item_record_cache
2135 (p_item_cache_index).revision_qty_control_code =
2136 inv_reservation_global.g_revision_control_yes
2137 AND p_revision IS NULL
2138 AND (p_subinventory_code IS NOT NULL
2139 OR
2140 p_locator_id IS NOT NULL
2141 ) THEN
2142 fnd_message.set_name('INV', 'INV_MISSING_REV');
2143 fnd_msg_pub.add;
2144 RAISE fnd_api.g_exc_error;
2145 END IF;
2146
2147 IF (l_debug = 1) THEN
2148 debug_print('After rev/sub/loc check cache: ' || l_return_status);
2149 END IF;
2150 --
2151 -- if the item is under lot control
2152 -- and the input lot_number is null but subinventory_code or locator_id is
2153 -- not null, raise the error
2154 IF inv_reservation_global.g_item_record_cache
2155 (p_item_cache_index).lot_control_code
2156 = inv_reservation_global.g_lot_control_yes
2157 AND p_lot_number IS NULL
2158 AND (p_subinventory_code IS NOT NULL
2159 OR
2160 p_locator_id IS NOT NULL
2161 ) THEN
2162
2163 --FlexiLotAlloc
2164 IF NVL(INV_FLEX_LOT_ALLOCATION_PUB.G_LOT_VALIDATION,'Y') = 'N' THEN
2165 IF (l_debug = 1) THEN
2166 debug_print('FlexiLotAlloc:INV_FLEX_LOT_ALLOCATION_PUB.G_LOT_VALIDATION=N,By-Pass INV_MISSING_LOT Validation.' );
2167 END IF;
2168 ELSE
2169 fnd_message.set_name('INV', 'INV_MISSING_LOT');
2170 fnd_msg_pub.add;
2171 RAISE fnd_api.g_exc_error;
2172 END IF;
2173
2174 END IF;
2175
2176 IF (l_debug = 1) THEN
2177 debug_print('After lot/sub/loc check cache: ' || l_return_status);
2178 END IF;
2179 --
2180 -- if the item is under revision and lot control
2181 -- and the input revision is null but lot_number is
2182 -- not null, raise the error
2183 IF inv_reservation_global.g_item_record_cache
2184 (p_item_cache_index).revision_qty_control_code =
2185 inv_reservation_global.g_revision_control_yes
2186 AND inv_reservation_global.g_item_record_cache
2187 (p_item_cache_index).lot_control_code
2188 = inv_reservation_global.g_lot_control_yes
2189 AND p_revision IS NULL
2190 AND p_lot_number IS NOT NULL THEN
2191 fnd_message.set_name('INV', 'INV_MISSING_REV');
2192 fnd_msg_pub.add;
2193 RAISE fnd_api.g_exc_error;
2194 END IF;
2195
2196 IF (l_debug = 1) THEN
2197 debug_print('After lot/rev check cache: ' || l_return_status);
2198 END IF;
2199 --
2200 --
2201 -- validate sub if the input is not null
2202 IF p_subinventory_code IS NOT NULL THEN
2203 inv_reservation_util_pvt.search_sub_cache
2204 (
2205 x_return_status => l_return_status
2206 , p_subinventory_code => p_subinventory_code
2207 , p_organization_id => p_organization_id
2208 , x_index => l_sub_cache_index
2209 );
2210 --
2211 IF (l_debug = 1) THEN
2212 debug_print('After search sub cache: ' || l_return_status);
2213 END IF;
2214 --
2215 IF l_return_status = fnd_api.g_ret_sts_error THEN
2216 RAISE fnd_api.g_exc_error;
2217 END IF ;
2218 --
2219 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2220 RAISE fnd_api.g_exc_unexpected_error;
2221 END IF;
2222 --
2223 -- if the sub is not in the cache, load it into the cache
2224 IF l_sub_cache_index IS NULL THEN
2225 /* BEGIN
2226 SELECT
2227 secondary_inventory_name
2228 , organization_id
2229 , locator_type
2230 , quantity_tracked
2231 , asset_inventory
2232 , reservable_type
2233 INTO l_rec
2234 FROM mtl_secondary_inventories
2235 WHERE secondary_inventory_name = p_subinventory_code
2236 AND organization_id = p_organization_id;
2237 --
2238 EXCEPTION
2239 WHEN NO_DATA_FOUND then
2240 fnd_message.set_name('INV','INVALID_SUB');
2241 fnd_msg_pub.add;
2242 RAISE fnd_api.g_exc_error;
2243 END; */
2244 -- Modified to call common API
2245 l_rec.secondary_inventory_name :=p_subinventory_code;
2246 IF INV_Validate.subinventory
2247 (
2248 p_sub => l_rec,
2249 p_org => inv_reservation_global.g_organization_record_cache(p_org_cache_index)
2250 )=INV_Validate.F THEN
2251 fnd_message.set_name('INV','INVALID_SUB');
2252 fnd_msg_pub.add;
2253 RAISE fnd_api.g_exc_error;
2254 END IF;
2255
2256
2257 --Bug 2334171 Check whether the sub is reservable
2258 -- Added the below for Bug 6870416
2259 IF inv_cache.set_org_rec(p_organization_id) THEN
2260 l_default_onhand_status_id := inv_cache.org_rec.default_status_id;
2261 END IF;
2262
2263 IF l_default_onhand_status_id IS NULL THEN
2264 IF l_rec.reservable_type = inv_globals.g_subinventory_non_reservable
2265 THEN /* non reservable Subinventory */
2266 fnd_message.set_name('INV','INV-SUBINV NOT RESERVABLE');
2267 fnd_message.set_token('SUBINV', l_rec.secondary_inventory_name);
2268 fnd_msg_pub.add;
2269 RAISE fnd_api.g_exc_error;
2270 END IF;
2271 END IF;
2272 -- End of changes for Bug 6870416
2273
2274 inv_reservation_util_pvt.add_sub_cache
2275 (
2276 x_return_status => l_return_status
2277 , p_sub_record => l_rec
2278 , x_index => l_sub_cache_index
2279 );
2280 --
2281 IF l_return_status = fnd_api.g_ret_sts_error THEN
2282 RAISE fnd_api.g_exc_error;
2283 END IF ;
2284 --
2285 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2286 RAISE fnd_api.g_exc_unexpected_error;
2287 END IF;
2288 ELSE
2289 l_rec := inv_reservation_global.g_sub_record_cache(l_sub_cache_index);
2290 END IF;
2291 --
2292 -- check lcator control based on settings at org, sub, item levels
2293 l_resultant_locator_control :=
2294 inv_reservation_util_pvt.locator_control
2295 (
2296 p_org_control
2297 => inv_reservation_global.g_organization_record_cache
2298 (p_org_cache_index)
2299 .stock_locator_control_code
2300 , p_sub_control
2301 => inv_reservation_global.g_sub_record_cache
2302 (l_sub_cache_index).locator_type
2303 , p_item_control
2304 => inv_reservation_global.g_item_record_cache
2305 (p_item_cache_index)
2306 .location_control_code
2307 );
2308 --
2309 IF (l_resultant_locator_control = 1
2310 AND p_locator_id IS NOT NULL AND p_locator_id > 0) THEN
2311 fnd_message.set_name('INV', 'INV_NO_LOCATOR_CONTROL');
2312 fnd_msg_pub.add;
2313 RAISE fnd_api.g_exc_error;
2314 END IF;
2315 --
2316 ELSIF p_locator_id IS NOT NULL THEN
2317 -- if the sub is null, but the locator id is not null
2318 -- raise the error
2319 fnd_message.set_name('INV', 'INV_NO_LOCATOR_CONTROL');
2320 fnd_msg_pub.add;
2321 RAISE fnd_api.g_exc_error;
2322 END IF;
2323 --
2324 -- Now we have validated that values are there.
2325 -- Now validate that values are correct
2326 IF p_revision IS NOT NULL THEN
2327 /* BEGIN
2328 SELECT 'Y' INTO l_found
2329 FROM mtl_item_revisions
2330 WHERE inventory_item_id = p_inventory_item_id
2331 AND organization_id = p_organization_id
2332 AND revision = p_revision ;
2333 --
2334 EXCEPTION
2335 WHEN NO_DATA_FOUND THEN
2336 fnd_message.set_name('INV','INVALID_REVISION');
2337 fnd_msg_pub.add;
2338 RAISE fnd_api.g_exc_error;
2339 END;*/
2340 IF INV_Validate.revision
2341 (
2342 p_revision => p_revision,
2343 p_org => inv_reservation_global.g_organization_record_cache
2344 (p_org_cache_index),
2345 p_item => inv_reservation_global.g_item_record_cache(p_item_cache_index) )=INV_Validate.F THEN
2346
2347 fnd_message.set_name('INV','INVALID_REVISION');
2348 fnd_msg_pub.add;
2349 RAISE fnd_api.g_exc_error;
2350 END IF;
2351
2352 END IF;
2353 --
2354 -- Expired lots custom hook
2355 IF p_lot_number IS NOT NULL AND NOT inv_pick_release_pub.g_pick_expired_lots THEN
2356 BEGIN
2357 SELECT expiration_date INTO l_lot_expiration_date
2358 FROM mtl_lot_numbers
2359 WHERE inventory_item_id = p_inventory_item_id
2360 AND organization_id = p_organization_id
2361 AND lot_number = p_lot_number;
2362 --
2363 IF l_lot_expiration_date IS NOT NULL
2364 AND l_lot_expiration_date < Sysdate THEN
2365 fnd_message.set_name('INV', 'INV_LOT_EXPIRED');
2366 fnd_msg_pub.add;
2367 RAISE fnd_api.g_exc_error;
2368 END IF;
2369 --
2370 EXCEPTION
2371 WHEN NO_DATA_FOUND then
2372 fnd_message.set_name('INV','INV_INVALID_LOT');
2373 fnd_msg_pub.add;
2374 RAISE fnd_api.g_exc_error;
2375 END;
2376 END IF;
2377 --
2378 IF p_subinventory_code IS NOT NULL THEN
2379 --
2380 -- validate the sub is valid in the org
2381
2382 -- Modified for common API. This validation has already been performed above.
2383
2384 /* BEGIN
2385 SELECT 'Y' INTO l_found
2386 FROM mtl_secondary_inventories
2387 WHERE secondary_inventory_name = p_subinventory_code
2388 AND organization_id = p_organization_id;
2389 EXCEPTION
2390 WHEN no_data_found THEN
2391 fnd_message.set_name('INV', 'INVALID_SUB');
2392 fnd_msg_pub.add;
2393 RAISE fnd_api.g_exc_error;
2394
2395 END;*/
2396
2397
2398 --
2399 IF inv_reservation_global.g_item_record_cache
2400 (p_item_cache_index)
2401 .restrict_subinventories_code = 1 THEN
2402 -- for restricted subs, use table mtl_item_subinventories
2403 BEGIN
2404 SELECT 'Y' INTO l_found
2405 FROM mtl_item_sub_trk_all_v
2406 WHERE inventory_item_id = p_inventory_item_id
2407 AND organization_id = p_organization_id
2408 AND secondary_inventory_name = p_subinventory_code;
2409 --
2410 EXCEPTION
2411 WHEN NO_DATA_FOUND THEN
2412 fnd_message.set_name('INV','INVALID_SUB');
2413 fnd_msg_pub.add;
2414 RAISE fnd_api.g_exc_error;
2415 END ;
2416 ELSIF inv_reservation_global.g_item_record_cache
2417 (p_item_cache_index)
2418 .restrict_subinventories_code = 2 THEN
2419 -- item is not restricted to specific subs
2420 BEGIN
2421 SELECT 'Y' INTO l_found
2422 FROM mtl_subinventories_trk_val_v
2423 WHERE organization_id = p_organization_id
2424 AND secondary_inventory_name = p_subinventory_code ;
2425 --
2426 EXCEPTION
2427 WHEN NO_DATA_FOUND THEN
2428 fnd_message.SET_NAME('INV','INVALID_SUB');
2429 fnd_msg_pub.add;
2430 RAISE fnd_api.g_exc_error;
2431 END ;
2432 END IF;
2433 --
2434 -- now if locator id is not null then validate its value
2435
2436
2437 IF (p_locator_id IS NOT NULL AND p_locator_id > 0) THEN
2438 -- check if locator is restricted to subs
2439 IF inv_reservation_global.g_item_record_cache
2440 (p_item_cache_index)
2441 .restrict_locators_code = 1 THEN
2442 BEGIN
2443 -- Modified to call common API
2444 /* SELECT 'Y' INTO l_found
2445 FROM
2446 mtl_secondary_locators msl
2447 , mtl_item_locations mil
2448 WHERE msl.inventory_item_id = p_inventory_item_id
2449 AND msl.organization_id = p_organization_id
2450 AND msl.subinventory_code = p_subinventory_code
2451 AND msl.secondary_locator = p_locator_id
2452 AND msl.secondary_locator = mil.inventory_location_id
2453 AND (mil.disable_date > sysdate
2454 OR mil.disable_date IS NULL
2455 );
2456 --
2457 EXCEPTION
2458 WHEN NO_DATA_FOUND THEN
2459 fnd_message.set_name('INV','INV_LOCATOR_NOT_AVAILABLE');
2460 fnd_msg_pub.add;
2461 RAISE fnd_api.g_exc_error; */
2462
2463
2464 IF INV_Validate.validateLocator(
2465 p_locator => l_rec_loc,
2466 p_org => inv_reservation_global.g_organization_record_cache
2467 (p_org_cache_index),
2468 p_sub => l_rec,
2469 p_item => inv_reservation_global.g_item_record_cache(p_item_cache_index)
2470 )=INV_Validate.F THEN
2471 fnd_message.set_name('INV','INV_LOCATOR_NOT_AVAILABLE');
2472 fnd_msg_pub.add;
2473 RAISE fnd_api.g_exc_error;
2474 END IF;
2475 END;
2476 ELSIF inv_reservation_global.g_item_record_cache
2477 (p_item_cache_index)
2478 .restrict_locators_code = 2 THEN
2479 /* BEGIN
2480 SELECT 'Y' INTO l_found
2481 FROM mtl_item_locations
2482 WHERE organization_id = p_organization_id
2483 AND subinventory_code = p_subinventory_code
2484 AND inventory_location_id = p_locator_id
2485 AND (disable_date > sysdate
2486 OR disable_date IS NULL
2487 );
2488 --
2489 EXCEPTION
2490 WHEN NO_DATA_FOUND THEN
2491 fnd_message.set_name('INV','INV_LOCATOR_NOT_AVAILABLE');
2492 fnd_msg_pub.add;
2493 RAISE fnd_api.g_exc_error;
2494 END;*/
2495
2496 -- Modified for common API
2497
2498 IF INV_Validate.validateLocator(
2499 p_locator => l_rec_loc,
2500 p_org => inv_reservation_global.g_organization_record_cache
2501 (p_org_cache_index),
2502 p_sub => l_rec)=INV_Validate.F THEN
2503 fnd_message.set_name('INV','INV_LOCATOR_NOT_AVAILABLE');
2504 fnd_msg_pub.add;
2505 RAISE fnd_api.g_exc_error;
2506 END IF;
2507 END IF;
2508 END IF; -- if p_locator_id is not null
2509 END IF; -- if p_subinventory_code is not null
2510
2511 /*** {{ R12 Enhanced reservations code changes ***/
2512 -- We dont have to validate serial numbers as the serials are
2513 -- validated as part of validate_serials
2514 --
2515 -- Now validate the serial numbers if there is
2516 -- Check if they exist and have the
2517 -- right status
2518 -- IF p_serial_array.COUNT > 0 THEN
2519 -- l_loop_index := p_serial_array.first ;
2520 -- BEGIN
2521 -- LOOP
2522 -- Modified to call common API
2523 --/*
2524 -- SELECT 'Y' INTO l_found
2525 -- FROM mtl_serial_numbers
2526 -- WHERE serial_number = p_serial_array(l_loop_index).serial_number
2527 -- AND current_status IN (1,3)
2528 -- the next line is commented out as
2529 -- currently serial number table has not been
2530 -- updated to include reservation id as a column
2531 -- AND reservation_id IS NULL ;
2532 -- ;*/
2533 -- l_rec_serial.serial_number:=p_serial_array(l_loop_index).serial_number;
2534 /*** {{ R12 Enhanced reservations code changes ***/
2535 -- IF INV_Validate.check_serial(
2536 -- p_serial => l_rec_serial,
2537 -- p_org => inv_reservation_global.g_organization_record_cache
2538 -- (p_org_cache_index),
2539 -- p_item => inv_reservation_global.g_item_record_cache(p_item_cache_index)
2540 -- ,
2541 -- p_from_sub => l_rec,
2542 -- p_lot => l_rec_lot,
2543 -- p_loc => l_rec_loc,
2544 -- p_revision => p_revision,
2545 -- p_msg => 'RSV'
2546 -- )=INV_Validate.F THEN
2547 --
2548 -- fnd_message.set_name('INV','INVALID_SERIAL_NUMBER');
2549 -- fnd_message.set_token('NUMBER',p_serial_array(l_loop_index).serial_number,FALSE);
2550 /*** {{ R12 Enhanced ----reservations code changes ***/
2551 -- fnd_msg_pub.add;
2552 -- RAISE fnd_api.g_exc_error;
2553 -- END IF;
2554 -- EXIT WHEN l_loop_index = p_serial_array.last ;
2555 -- l_loop_index := p_serial_array.next(l_loop_index);
2556 -- END LOOP;
2557 -- /*EXCEPTION
2558 -- WHEN NO_DATA_FOUND THEN
2559 -- fnd_message.set_name('INV','INVALID_SERIAL_NUMBER');
2560 -- fnd_message.set_token('NUMBER',p_serial_array(l_loop_index),FALSE);
2561 -- fnd_msg_pub.add;
2562 -- RAISE fnd_api.g_exc_error;
2563 -- */
2564 -- END;
2565 -- END IF;
2566 /*** End R12 }} ***/
2567
2568 --
2569 -- INVCONV BEGIN
2570 -- Additional validations for process attributes introduced for inventory convergence
2571 -- ==================================================================================
2572 --
2573 -- if the item is defined as lot_indivisible (lot_divisible_flag <> 'Y')
2574 -- the reservation must be detailed to lot level so ensure that the
2575 -- lot_number is populated
2576 IF inv_reservation_global.g_item_record_cache
2577 (p_item_cache_index).lot_divisible_flag <> 'Y' AND
2578 inv_reservation_global.g_item_record_cache(p_item_cache_index).lot_control_code = inv_reservation_global.g_lot_control_yes AND
2579 p_lot_number IS NULL THEN
2580 --FlexiLotAlloc
2581 IF NVL(INV_FLEX_LOT_ALLOCATION_PUB.G_LOT_VALIDATION,'Y') = 'N' THEN
2582 IF (l_debug = 1) THEN
2583 debug_print('FlexiLotAlloc:INV_FLEX_LOT_ALLOCATION_PUB.G_LOT_VALIDATION=N,By-Pass INV_INDIVISIBLE_LOT_REQUIRED Validation.' );
2584 END IF;
2585 ELSE
2586 fnd_message.set_name('INV', 'INV_INDIVISIBLE_LOT_REQUIRED'); -- INVCONV New Message
2587 fnd_msg_pub.add;
2588 RAISE fnd_api.g_exc_error;
2589 END IF;
2590 END IF;
2591 -- INVCONV END
2592 --
2593 x_return_status := l_return_status;
2594 x_sub_cache_index:= l_sub_cache_index;
2595 --
2596 EXCEPTION
2597 WHEN fnd_api.g_exc_error THEN
2598 x_return_status := fnd_api.g_ret_sts_error;
2599 --
2600 WHEN fnd_api.g_exc_unexpected_error THEN
2601 x_return_status := fnd_api.g_ret_sts_unexp_error ;
2602 --
2603 WHEN OTHERS THEN
2604 x_return_status := fnd_api.g_ret_sts_unexp_error ;
2605 --
2606 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
2607 THEN
2608 fnd_msg_pub.add_exc_msg
2609 ( g_pkg_name
2610 , 'Validate_Item_SKU'
2611 );
2612 END IF;
2613 --
2614 END validate_item_sku;
2615 --
2616 -- Procedure
2617 -- validate_supply_source
2618 -- Description
2619 -- is valid if all of the following are satisfied
2620 -- 1. p_supply_source_type_id is not null
2621 -- no longer needed 2. p_supply_source_header_id or p_supply_source_name is not null
2622 -- 3. calling validate_item_sku with the sku info and returning success
2623 PROCEDURE validate_supply_source
2624 (
2625 x_return_status OUT NOCOPY VARCHAR2
2626 , p_inventory_item_id IN NUMBER
2627 , p_organization_id IN NUMBER
2628 , p_supply_source_type_id IN NUMBER
2629 , p_supply_source_header_id IN NUMBER
2630 , p_supply_source_line_id IN NUMBER
2631 , p_supply_source_line_detail IN NUMBER
2632 , p_supply_source_name IN VARCHAR2
2633 , p_demand_source_type_id IN NUMBER
2634 , p_revision IN VARCHAR2
2635 , p_lot_number IN VARCHAR2
2636 , p_subinventory_code IN VARCHAR2
2637 , p_locator_id IN NUMBER
2638 , p_serial_array IN inv_reservation_global.serial_number_tbl_type
2639 , p_demand_ship_date IN DATE
2640 , p_supply_receipt_date IN DATE
2641 , p_item_cache_index IN INTEGER
2642 , p_org_cache_index IN INTEGER
2643 , x_supply_cache_index OUT NOCOPY INTEGER
2644 , x_sub_cache_index OUT NOCOPY INTEGER
2645 ) IS
2646 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
2647 l_structure_num NUMBER := NULL;
2648 l_supply_cache_index NUMBER := NULL;
2649 l_sub_cache_index NUMBER := NULL;
2650 l_is_valid NUMBER := NULL;
2651 l_rec inv_reservation_global.supply_record;
2652
2653 /*** {{ R12 Enhanced reservations code changes ***/
2654 l_msg_count NUMBER;
2655 l_msg_data VARCHAR2(1000);
2656 l_wip_entity_type NUMBER;
2657 l_wip_job_type VARCHAR2(15);
2658 l_debug NUMBER;
2659 /*** End R12 }} ***/
2660
2661 BEGIN
2662 --
2663 /*** {{ R12 Enhanced reservations code changes ***/
2664 IF (g_debug IS NULL) THEN
2665 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2666 END IF;
2667
2668 l_debug := g_debug;
2669
2670 IF (l_debug = 1) THEN
2671 debug_print('In validate_supply_source: supply_source_type_id = ' || p_supply_source_type_id);
2672 debug_print('In validate_supply_source: supply_source_header_id = ' || p_supply_source_header_id);
2673 debug_print('In validate_supply_source: supply_source_line_id = ' || p_supply_source_line_id);
2674 debug_print('In validate_supply_source: supply_source_name = ' || p_supply_source_name);
2675 debug_print('In validate_supply_source: supply_source_line detail = ' || p_supply_source_line_detail);
2676 debug_print('In validate_supply_source: demand_source_type_id = ' || p_demand_source_type_id);
2677 END IF;
2678 /*** End R12 }} ***/
2679
2680 IF p_supply_source_type_id IS NULL THEN
2681 fnd_message.set_name('INV', 'MISSING SUPPLY');
2682 fnd_msg_pub.add;
2683 RAISE fnd_api.g_exc_error;
2684 END IF;
2685 --
2686
2687 /*** {{ R12 Enhanced reservations code changes ***/
2688 -- Returns error if we do not support the supply type
2689 IF (p_supply_source_type_id NOT IN
2690 (inv_reservation_global.g_source_type_po,
2691 inv_reservation_global.g_source_type_inv, inv_reservation_global.g_source_type_req,
2692 inv_reservation_global.g_source_type_internal_req, inv_reservation_global.g_source_type_asn,
2693 inv_reservation_global.g_source_type_intransit, inv_reservation_global.g_source_type_wip,
2694 inv_reservation_global.g_source_type_rcv)) THEN
2695
2696 fnd_message.set_name('INV', 'INV_RSV_INVALID_SUPPLY');
2697 fnd_msg_pub.ADD;
2698 RAISE fnd_api.g_exc_error;
2699 END IF;
2700 /*** End R12 }} ***/
2701
2702 IF (l_debug = 1) THEN
2703 debug_print('Before calling suppy cache. return status :' ||
2704 l_return_status);
2705 END IF;
2706 -- search for the supply source in the cache first
2707 inv_reservation_util_pvt.search_supply_cache
2708 (
2709 x_return_status => l_return_status
2710 , p_supply_source_type_id => p_supply_source_type_id
2711 , p_supply_source_header_id => p_supply_source_header_id
2712 , p_supply_source_line_id => p_supply_source_line_id
2713 , p_supply_source_name => p_supply_source_name
2714 , x_index => l_supply_cache_index
2715 );
2716 --
2717 IF l_return_status = fnd_api.g_ret_sts_error THEN
2718 RAISE fnd_api.g_exc_error;
2719 END IF ;
2720 --
2721 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2722 RAISE fnd_api.g_exc_unexpected_error;
2723 END IF;
2724 --
2725 -- for other supply sources (wip, po), call their validation api
2726 -- when available
2727 --
2728 IF (l_debug = 1) THEN
2729 debug_print('After calling supply cache ' || p_supply_source_type_id);
2730 debug_print('Return status :' || l_return_status);
2731 END IF;
2732
2733 IF p_supply_source_type_id = inv_reservation_global.g_source_type_po
2734 THEN
2735 IF (l_debug = 1) THEN
2736 debug_print('Before calling validate po ' || l_return_status);
2737 END IF;
2738 validate_supply_source_po
2739 (
2740 x_return_status => l_return_status
2741 , p_organization_id => p_organization_id
2742 , p_inventory_item_id => p_inventory_item_id
2743 , p_demand_ship_date => p_demand_ship_date
2744 , p_supply_receipt_date => p_supply_receipt_date
2745 , p_supply_source_type_id => p_supply_source_type_id /*** {{ R12 Enhanced reservations code changes }}***/
2746 , p_supply_source_header_id => p_supply_source_header_id
2747 , p_supply_source_line_id => p_supply_source_line_id
2748 , p_supply_source_line_detail => NULL /*** {{ R12 Enhanced reservations code changes }}***/
2749 );
2750
2751 IF (l_debug = 1) THEN
2752 debug_print('After calling validate po ' || l_return_status);
2753 END IF;
2754
2755 IF l_return_status = fnd_api.g_ret_sts_error THEN
2756 RAISE fnd_api.g_exc_error;
2757 END IF ;
2758 --
2759 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2760 RAISE fnd_api.g_exc_unexpected_error;
2761 END IF;
2762 ELSIF p_supply_source_type_id = inv_reservation_global.g_source_type_req
2763 THEN
2764 IF (l_debug = 1) THEN
2765 debug_print('Before calling validate req ' || l_return_status);
2766 END IF;
2767 validate_supply_source_req
2768 (
2769 x_return_status => l_return_status
2770 , p_organization_id => p_organization_id
2771 , p_inventory_item_id => p_inventory_item_id
2772 , p_demand_ship_date => p_demand_ship_date
2773 , p_supply_receipt_date => p_supply_receipt_date
2774 , p_supply_source_type_id => p_supply_source_type_id /*** {{ R12 Enhanced reservations code changes }}***/
2775 , p_supply_source_header_id => p_supply_source_header_id
2776 , p_supply_source_line_id => p_supply_source_line_id
2777 , p_supply_source_line_detail => NULL /*** {{ R12 Enhanced reservations code changes }}***/
2778 );
2779 IF (l_debug = 1) THEN
2780 debug_print('After calling validate req ' || l_return_status);
2781 END IF;
2782 IF l_return_status = fnd_api.g_ret_sts_error THEN
2783 RAISE fnd_api.g_exc_error;
2784 END IF ;
2785 --
2786 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2787 RAISE fnd_api.g_exc_unexpected_error;
2788 END IF;
2789
2790 /*** {{ R12 Enhanced reservations code changes ***/
2791 ELSIF (p_supply_source_type_id = inv_reservation_global.g_source_type_internal_req) THEN
2792 validate_supply_source_intreq
2793 (
2794 x_return_status => l_return_status
2795 , p_organization_id => p_organization_id
2796 , p_inventory_item_id => p_inventory_item_id
2797 , p_demand_ship_date => p_demand_ship_date
2798 , p_supply_receipt_date => p_supply_receipt_date
2799 , p_supply_source_type_id => p_supply_source_type_id
2800 , p_supply_source_header_id => p_supply_source_header_id
2801 , p_supply_source_line_id => p_supply_source_line_id
2802 , p_supply_source_line_detail => NULL
2803 );
2804
2805 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
2806 RAISE fnd_api.g_exc_error;
2807 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
2808 RAISE fnd_api.g_exc_unexpected_error;
2809 END IF;
2810 ELSIF (p_supply_source_type_id = inv_reservation_global.g_source_type_asn) THEN
2811 validate_supply_source_asn
2812 (
2813 x_return_status => l_return_status
2814 , p_organization_id => p_organization_id
2815 , p_inventory_item_id => p_inventory_item_id
2816 , p_demand_ship_date => p_demand_ship_date
2817 , p_supply_receipt_date => p_supply_receipt_date
2818 , p_supply_source_type_id => p_supply_source_type_id
2819 , p_supply_source_header_id => p_supply_source_header_id
2820 , p_supply_source_line_id => p_supply_source_line_id
2821 , p_supply_source_line_detail => p_supply_source_line_detail
2822 );
2823
2824 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
2825 RAISE fnd_api.g_exc_error;
2826 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
2827 RAISE fnd_api.g_exc_unexpected_error;
2828 END IF;
2829 ELSIF (p_supply_source_type_id = inv_reservation_global.g_source_type_intransit) THEN
2830 validate_supply_source_intran
2831 (
2832 x_return_status => l_return_status
2833 , p_organization_id => p_organization_id
2834 , p_inventory_item_id => p_inventory_item_id
2835 , p_demand_ship_date => p_demand_ship_date
2836 , p_supply_receipt_date => p_supply_receipt_date
2837 , p_supply_source_type_id => p_supply_source_type_id
2838 , p_supply_source_header_id => p_supply_source_header_id
2839 , p_supply_source_line_id => p_supply_source_line_id
2840 , p_supply_source_line_detail => NULL
2841 );
2842
2843 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
2844 RAISE fnd_api.g_exc_error;
2845 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
2846 RAISE fnd_api.g_exc_unexpected_error;
2847 END IF;
2848 ELSIF (p_supply_source_type_id = inv_reservation_global.g_source_type_rcv) THEN
2849 validate_supply_source_rcv
2850 (
2851 x_return_status => l_return_status
2852 , p_organization_id => p_organization_id
2853 , p_item_id => p_inventory_item_id
2854 , p_demand_ship_date => p_demand_ship_date
2855 , p_supply_receipt_date => p_supply_receipt_date
2856 , p_supply_source_type_id => p_supply_source_type_id
2857 , p_supply_source_header_id => p_supply_source_header_id
2858 , p_supply_source_line_id => p_supply_source_line_id
2859 , p_supply_source_line_detail => NULL
2860 );
2861
2862 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
2863 RAISE fnd_api.g_exc_error;
2864 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
2865 RAISE fnd_api.g_exc_unexpected_error;
2866 END IF;
2867
2868 ELSIF (p_supply_source_type_id = inv_reservation_global.g_source_type_wip) THEN
2869 -- get wip entity id from wip_record_cache
2870 inv_reservation_util_pvt.get_wip_cache
2871 (
2872 x_return_status => l_return_status
2873 , p_wip_entity_id => p_supply_source_header_id
2874 );
2875
2876 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
2877 RAISE fnd_api.g_exc_error;
2878 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
2879 RAISE fnd_api.g_exc_unexpected_error;
2880 ELSE
2881 l_wip_entity_type := inv_reservation_global.g_wip_record_cache(p_supply_source_header_id).wip_entity_type;
2882 l_wip_job_type := inv_reservation_global.g_wip_record_cache(p_supply_source_header_id).wip_entity_job;
2883 END IF;
2884
2885 /* Added inv_reservation_global.g_wip_source_type_cmro for bug 13524480 */
2886
2887 IF (l_wip_entity_type NOT IN
2888 (inv_reservation_global.g_wip_source_type_discrete,
2889 inv_reservation_global.g_wip_source_type_osfm, inv_reservation_global.g_wip_source_type_fpo,
2890 inv_reservation_global.g_wip_source_type_batch, inv_reservation_global.g_wip_source_type_cmro)) THEN
2891 fnd_message.set_name('INV', 'INV_RSV_WIP_ENT_ERR');
2892 fnd_msg_pub.ADD;
2893 RAISE fnd_api.g_exc_error;
2894 END IF;
2895
2896 -- add validation to check if the supply is wip discrete and osfm, then the
2897 -- demand source needs to be sales order or internal order, otherwise, error out.
2898 IF (l_wip_entity_type = inv_reservation_global.g_wip_source_type_discrete OR
2899 l_wip_entity_type = inv_reservation_global.g_wip_source_type_osfm) THEN
2900 IF (p_demand_source_type_id NOT IN (inv_reservation_global.g_source_type_oe,
2901 inv_reservation_global.g_source_type_internal_ord)) THEN
2902 fnd_message.set_name('INV', 'INV_INVALID_DEMAND_SOURCE');
2903 fnd_msg_pub.ADD;
2904 RAISE fnd_api.g_exc_error;
2905 END IF;
2906 END IF;
2907
2908 IF (l_wip_entity_type = inv_reservation_global.g_wip_source_type_discrete) THEN
2909 validate_supply_source_wipdisc
2910 (
2911 x_return_status => l_return_status
2912 , p_organization_id => p_organization_id
2913 , p_inventory_item_id => p_inventory_item_id
2914 , p_demand_ship_date => p_demand_ship_date
2915 , p_supply_receipt_date => p_supply_receipt_date
2916 , p_supply_source_type_id => p_supply_source_type_id
2917 , p_supply_source_header_id => p_supply_source_header_id
2918 , p_supply_source_line_id => p_supply_source_line_id
2919 , p_supply_source_line_detail => NULL
2920 , p_wip_entity_type => l_wip_entity_type
2921 );
2922
2923 IF (l_debug = 1) THEN
2924 debug_print('Return status from supply source wipdisc :' || l_return_status);
2925 END IF;
2926
2927 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
2928 RAISE fnd_api.g_exc_error;
2929 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
2930 RAISE fnd_api.g_exc_unexpected_error;
2931 END IF;
2932
2933 ELSIF (l_wip_entity_type = inv_reservation_global.g_wip_source_type_osfm) THEN
2934 validate_supply_source_osfm
2935 (
2936 x_return_status => l_return_status
2937 , p_organization_id => p_organization_id
2938 , p_inventory_item_id => p_inventory_item_id
2939 , p_demand_ship_date => p_demand_ship_date
2940 , p_supply_receipt_date => p_supply_receipt_date
2941 , p_supply_source_type_id => p_supply_source_type_id
2942 , p_supply_source_header_id => p_supply_source_header_id
2943 , p_supply_source_line_id => p_supply_source_line_id
2944 , p_supply_source_line_detail => NULL
2945 , p_wip_entity_type => l_wip_entity_type
2946 );
2947
2948 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
2949 RAISE fnd_api.g_exc_error;
2950 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
2951 RAISE fnd_api.g_exc_unexpected_error;
2952 END IF;
2953
2954 ELSIF (l_wip_entity_type = inv_reservation_global.g_wip_source_type_fpo) THEN
2955 validate_supply_source_fpo
2956 (
2957 x_return_status => l_return_status
2958 , p_organization_id => p_organization_id
2959 , p_inventory_item_id => p_inventory_item_id
2960 , p_demand_ship_date => p_demand_ship_date
2961 , p_supply_receipt_date => p_supply_receipt_date
2962 , p_supply_source_type_id => p_supply_source_type_id
2963 , p_supply_source_header_id => p_supply_source_header_id
2964 , p_supply_source_line_id => p_supply_source_line_id
2965 , p_supply_source_line_detail => NULL
2966 , p_wip_entity_type => l_wip_entity_type
2967 );
2968 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
2969 RAISE fnd_api.g_exc_error;
2970 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
2971 RAISE fnd_api.g_exc_unexpected_error;
2972 END IF;
2973 ELSIF (l_wip_entity_type = inv_reservation_global.g_wip_source_type_batch) THEN
2974 validate_supply_source_batch
2975 (
2976 x_return_status => l_return_status
2977 , p_organization_id => p_organization_id
2978 , p_inventory_item_id => p_inventory_item_id
2979 , p_demand_ship_date => p_demand_ship_date
2980 , p_supply_receipt_date => p_supply_receipt_date
2981 , p_supply_source_type_id => p_supply_source_type_id
2982 , p_supply_source_header_id => p_supply_source_header_id
2983 , p_supply_source_line_id => p_supply_source_line_id
2984 , p_supply_source_line_detail => NULL
2985 , p_wip_entity_type => l_wip_entity_type
2986 );
2987 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
2988 RAISE fnd_api.g_exc_error;
2989 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
2990 RAISE fnd_api.g_exc_unexpected_error;
2991 END IF;
2992 ELSIF (l_wip_entity_type = inv_reservation_global.g_wip_source_type_cmro) THEN /* Added for bug 13524480 */
2993 debug_print('Calling validate_supply_source_cmro :' || l_return_status);
2994
2995 validate_supply_source_cmro
2996 (
2997 x_return_status => l_return_status
2998 , p_organization_id => p_organization_id
2999 , p_inventory_item_id => p_inventory_item_id
3000 , p_demand_ship_date => p_demand_ship_date
3001 , p_supply_receipt_date => p_supply_receipt_date
3002 , p_supply_source_type_id => p_supply_source_type_id
3003 , p_supply_source_header_id => p_supply_source_header_id
3004 , p_supply_source_line_id => p_supply_source_line_id
3005 , p_supply_source_line_detail => NULL
3006 , p_wip_entity_type => l_wip_entity_type
3007 );
3008 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
3009 RAISE fnd_api.g_exc_error;
3010 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
3011 RAISE fnd_api.g_exc_unexpected_error;
3012 END IF;
3013 END IF;
3014 /*** End R12 }} ***/
3015 END IF;
3016
3017 -- Here we should know that the supply source is valid
3018 -- we can add it to cache if it is not there yet
3019 IF l_supply_cache_index IS NULL THEN
3020 l_rec.supply_source_type_id := p_supply_source_type_id;
3021 l_rec.supply_source_header_id := p_supply_source_header_id;
3022 l_rec.supply_source_line_id := p_supply_source_line_id;
3023 l_rec.supply_source_name := p_supply_source_name;
3024 l_rec.is_valid := 1; -- 1 = true
3025 --
3026 inv_reservation_util_pvt.add_supply_cache
3027 (
3028 x_return_status => l_return_status
3029 , p_supply_record => l_rec
3030 , x_index => l_supply_cache_index
3031 );
3032 --
3033
3034 IF (l_debug = 1) THEN
3035 debug_print('After adding supply cache. Return status :' || l_return_status);
3036 END IF;
3037
3038 IF l_return_status = fnd_api.g_ret_sts_error THEN
3039 RAISE fnd_api.g_exc_error;
3040 END IF ;
3041 --
3042 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3043 RAISE fnd_api.g_exc_unexpected_error;
3044 END IF;
3045 END IF;
3046 --
3047 -- call validate_item_sku
3048 IF p_supply_source_type_id = inv_reservation_global.g_source_type_inv
3049 THEN
3050 validate_item_sku
3051 (
3052 x_return_status => l_return_status
3053 , p_inventory_item_id => p_inventory_item_id
3054 , p_organization_id => p_organization_id
3055 , p_revision => p_revision
3056 , p_lot_number => p_lot_number
3057 , p_subinventory_code => p_subinventory_code
3058 , p_locator_id => p_locator_id
3059 , p_serial_array => p_serial_array
3060 , p_item_cache_index => p_item_cache_index
3061 , p_org_cache_index => p_org_cache_index
3062 , x_sub_cache_index => l_sub_cache_index
3063 );
3064 --
3065 IF (l_debug = 1) THEN
3066 debug_print('After adding validate item sku. Return status :' ||
3067 l_return_status);
3068 END IF;
3069 IF l_return_status = fnd_api.g_ret_sts_error THEN
3070 RAISE fnd_api.g_exc_error;
3071 END IF ;
3072 --
3073 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3074 RAISE fnd_api.g_exc_unexpected_error;
3075 END IF;
3076 ELSIF p_subinventory_code IS NOT NULL
3077 OR p_locator_id IS NOT NULL
3078 THEN
3079 -- if the supply source is not inv, sub, locator should be null and serial number should be empty
3080 fnd_message.set_name('INV', 'EXTRA_SUPPLY_INFO');
3081 fnd_msg_pub.ADD;
3082 RAISE fnd_api.g_exc_error;
3083 END IF;
3084 --
3085 x_sub_cache_index := l_sub_cache_index;
3086 x_supply_cache_index := l_supply_cache_index;
3087 x_return_status := l_return_status;
3088 --
3089 EXCEPTION
3090 WHEN fnd_api.g_exc_error THEN
3091 x_return_status := fnd_api.g_ret_sts_error;
3092 IF (l_debug = 1) THEN
3093 debug_print('Return status from supply source :' || x_return_status);
3094 END IF;
3095 --
3096 WHEN fnd_api.g_exc_unexpected_error THEN
3097 x_return_status := fnd_api.g_ret_sts_unexp_error ;
3098 --
3099 WHEN OTHERS THEN
3100 x_return_status := fnd_api.g_ret_sts_unexp_error ;
3101 --
3102 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
3103 THEN
3104 fnd_msg_pub.add_exc_msg
3105 ( g_pkg_name
3106 , 'Validate_Supply_Source'
3107 );
3108 END IF;
3109 --
3110 END validate_supply_source;
3111 --
3112 -- Procedure
3113 -- validate_quantity
3114 -- Description
3115 -- is valid if all of the following are satisfied
3116 -- 1. p_primary_uom or p_reservation_uom is not null
3117 -- 2. p_primary_quantity or p_reservation_quantity is not null
3118 -- 3. if p_has_serial_number = fnd_api.g_true, p_primary_quantity or
3119 -- if p_primary_quantity is null, p_reservation_quantity is an integer
3120 -- INVCONV
3121 -- Additional validations for single/dual tracking
3122 PROCEDURE validate_quantity
3123 (
3124 x_return_status OUT NOCOPY VARCHAR2
3125 , p_primary_uom IN VARCHAR2
3126 , p_primary_quantity IN NUMBER
3127 , p_secondary_uom IN VARCHAR2 -- INVCONV
3128 , p_secondary_quantity IN NUMBER -- INVCONV
3129 , p_reservation_uom IN VARCHAR2
3130 , p_reservation_quantity IN NUMBER
3131 , p_lot_number IN VARCHAR2 -- INVCONV
3132 , p_has_serial_number IN VARCHAR2
3133 , p_item_cache_index IN NUMBER -- INVCON
3134 ) IS
3135 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
3136 l_quantity NUMBER;
3137 l_error_message VARCHAR2(1000); -- INVCONV
3138 l_qtys_within_dev NUMBER DEFAULT 1; -- INVCONV
3139 BEGIN
3140 --
3141 IF p_primary_uom IS NULL
3142 AND p_reservation_uom IS NULL THEN
3143 fnd_message.set_name('INV', 'MISSING UOM');
3144 fnd_msg_pub.add;
3145 RAISE fnd_api.g_exc_error;
3146 END IF;
3147 --
3148 IF p_primary_quantity IS NULL
3149 AND p_reservation_quantity IS NULL THEN
3150 fnd_message.set_name('INV', 'MISSING RSV QUANTITY');
3151 fnd_msg_pub.add;
3152 RAISE fnd_api.g_exc_error;
3153 END IF;
3154 --
3155 IF p_primary_quantity IS NOT NULL THEN
3156 l_quantity := p_primary_quantity;
3157 ELSE
3158 l_quantity := p_primary_quantity;
3159 END IF;
3160 --
3161 -- the quantity should be an integer
3162 -- if serial number is provided
3163 IF l_quantity <> Trunc(l_quantity)
3164 AND p_has_serial_number = fnd_api.g_true THEN
3165 fnd_message.set_name('INV', 'INV_QTY_EQ_INTEGER');
3166 fnd_msg_pub.add;
3167 RAISE fnd_api.g_exc_error;
3168 END IF;
3169 --
3170
3171 -- if the item is not defined as dual control
3172 -- secondary_uom_code and secondary_reservation_quantity
3173 -- should be empty
3174 IF inv_reservation_global.g_item_record_cache
3175 (p_item_cache_index).tracking_quantity_ind <> 'PS' THEN
3176 -- SINGLE UOM TRACKING
3177 -- ===================
3178 IF p_secondary_uom IS NOT NULL THEN
3179 fnd_message.set_name('INV', 'INV_SECONDARY_UOM_NOT_REQUIRED'); -- INVCONV New Message
3180 fnd_msg_pub.add;
3181 RAISE fnd_api.g_exc_error;
3182 ELSIF p_secondary_quantity IS NOT NULL THEN
3183 fnd_message.set_name('INV', 'INV_SECONDARY_QTY_NOT_REQUIRED');-- INVCONV New Message
3184 fnd_msg_pub.add;
3185 RAISE fnd_api.g_exc_error;
3186 END IF;
3187 ELSIF inv_reservation_global.g_item_record_cache
3188 (p_item_cache_index).tracking_quantity_ind = 'PS' THEN
3189 -- DUAL UOM TRACKING
3190 -- =================
3191 IF p_secondary_uom IS NULL THEN
3192 fnd_message.set_name('INV', 'INV_SECONDARY_UOM_REQUIRED'); -- INVCONV New Message
3193 fnd_msg_pub.add;
3194 RAISE fnd_api.g_exc_error;
3195 ELSIF p_secondary_uom <> inv_reservation_global.g_item_record_cache(p_item_cache_index).secondary_uom_code THEN
3196 fnd_message.set_name('INV', 'INV_INCORRECT_SECONDARY_UOM'); -- INVCONV New Message
3197 fnd_msg_pub.add;
3198 RAISE fnd_api.g_exc_error;
3199 ELSIF p_secondary_quantity IS NULL THEN
3200 fnd_message.set_name('INV', 'INV_SECONDARY_QTY_REQUIRED'); -- INVCONV New Message
3201 fnd_msg_pub.add;
3202 RAISE fnd_api.g_exc_error;
3203 END IF;
3204 -- Ensure that primary/secondary quantities honor the UOM conversion and deviations in place
3205
3206 /* IF the Reservation UOM and Secondary UOM are the same AND the Reservation qty and the Secondary Reservation
3207 Qty are the same and it's a fixed conversion item , there's no need to check deviation
3208 INVCONV Bug#3933849 */
3209 IF( (inv_reservation_global.g_item_record_cache(p_item_cache_index).secondary_default_ind = 'F')
3210 AND (p_reservation_quantity = p_secondary_quantity) AND (p_reservation_uom = p_secondary_uom)) THEN
3211 NULL;
3212 ELSE
3213 l_qtys_within_dev := INV_CONVERT.Within_Deviation
3214 ( p_organization_id =>
3215 inv_reservation_global.g_item_record_cache(p_item_cache_index).organization_id
3216 , p_inventory_item_id =>
3217 inv_reservation_global.g_item_record_cache(p_item_cache_index).inventory_item_id
3218 , p_lot_number => p_lot_number
3219 , p_precision => 5
3220 , p_quantity => p_primary_quantity
3221 , p_uom_code1 => p_primary_uom
3222 , p_quantity2 => p_secondary_quantity
3223 , p_uom_code2 => p_secondary_uom
3224 ) ;
3225
3226 IF (l_qtys_within_dev <> 1) THEN
3227 --fnd_message.set_name('INV', l_error_message);
3228 --fnd_msg_pub.add;
3229 RAISE fnd_api.g_exc_error;
3230 END IF;
3231 END IF; /* IF for Fixed item */
3232 END IF;
3233 -- INVCONV END
3234
3235
3236 x_return_status := l_return_status;
3237 --
3238 EXCEPTION
3239 WHEN fnd_api.g_exc_error THEN
3240 x_return_status := fnd_api.g_ret_sts_error;
3241 --
3242 WHEN OTHERS THEN
3243 x_return_status := fnd_api.g_ret_sts_unexp_error ;
3244 --
3245 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
3246 THEN
3247 fnd_msg_pub.add_exc_msg
3248 ( g_pkg_name
3249 , 'Validate_Quantity'
3250 );
3251 END IF;
3252 --
3253 END validate_quantity;
3254
3255
3256 --
3257 -- Procedure
3258 -- validate_sales_order
3259 -- Description
3260 -- is valid if all of the following are satisfied
3261 -- 1. sales order is open
3262 -- 2. p_reservation_item matches the item in the sales order
3263 -- 3. p_reservation_quantity is greater or equal to the
3264 -- reservable quantity = ordered quantity - already reserved qty
3265 -- Bug 1620576 - To support overpicking for pick wave move orders,
3266 -- we have to remove the restriction that reservation quantity cannot
3267 -- exceed sales order quantity
3268 -- {{ R12 Enhanced reservations code changes, add validation for sales order,
3269 -- for non-inventory supply types, if the sales order has not been booked,
3270 -- return error. }}
3271 PROCEDURE validate_sales_order
3272 (
3273 x_return_status OUT NOCOPY VARCHAR2
3274 , p_rsv_action_name IN VARCHAR2
3275 , p_reservation_id IN NUMBER
3276 , p_demand_type_id IN NUMBER
3277 , p_demand_header_id IN NUMBER
3278 , p_demand_line_id IN NUMBER
3279 , p_orig_demand_type_id IN NUMBER
3280 , p_orig_demand_header_id IN NUMBER
3281 , p_orig_demand_line_id IN NUMBER
3282 , p_reservation_quantity IN NUMBER
3283 , p_reservation_uom_code IN VARCHAR2
3284 , p_reservation_item_id IN NUMBER
3285 , p_reservation_org_id IN NUMBER
3286 , p_supply_type_id IN NUMBER /*** {{ R12 Enhanced reservations code changes }}***/
3287 , p_substitute_flag IN BOOLEAN DEFAULT FALSE /* Bug 6044651 */
3288 ) IS
3289
3290 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
3291
3292 l_org_id NUMBER;
3293
3294 l_line_rec_inventory_item_id oe_order_lines_all.inventory_item_id%TYPE;
3295 l_line_rec_ordered_quantity oe_order_lines_all.ordered_quantity%TYPE;
3296 l_line_rec_order_quantity_uom oe_order_lines_all.order_quantity_uom%TYPE;
3297 l_line_rec_org_id oe_order_lines_all.org_id%TYPE;
3298 l_line_rec_open_flag VARCHAR2(1);
3299
3300 l_ordered_quantity_rsv_uom NUMBER := 0;
3301 l_primary_uom_code VARCHAR2(3);
3302 l_primary_reserved_quantity NUMBER := 0;
3303 l_reserved_quantity NUMBER := 0;
3304 l_source_type_code VARCHAR2(30);
3305 l_flow_status_code VARCHAR2(30); --Bug 3118495
3306 l_order_header_id NUMBER; --bug 16104833
3307 l_booked_flag VARCHAR2(1) := 'N'; /*** {{ R12 Enhanced reservations code changes ***/
3308 l_debug NUMBER := g_debug;
3309 BEGIN
3310 -- Initialize return status
3311 x_return_status := fnd_api.g_ret_sts_success;
3312
3313 IF p_demand_type_id in (inv_reservation_global.g_source_type_oe,
3314 inv_reservation_global.g_source_type_internal_ord,
3315 inv_reservation_global.g_source_type_rma) THEN
3316
3317 -- Fetch row from oe_order_lines
3318
3319 /*l_org_id := OE_GLOBALS.G_ORG_ID;
3320 IF l_org_id IS NULL THEN
3321 OE_GLOBALS.Set_Context;
3322 l_org_id := OE_GLOBALS.G_ORG_ID;
3323 end if;*/
3324
3325 l_org_id := p_reservation_org_id;
3326
3327 SELECT inventory_item_id, ordered_quantity
3328 , order_quantity_uom, ship_from_org_id
3329 , open_flag, source_type_code,flow_status_code
3330 , booked_flag /*** {{ R12 Enhanced reservations code changes ***/
3331 , header_id
3332 INTO l_line_rec_inventory_item_id,
3333 l_line_rec_ordered_quantity,
3334 l_line_rec_order_quantity_uom,
3335 l_line_rec_org_id,
3336 l_line_rec_open_flag,
3337 l_source_type_code,
3338 l_flow_status_code,
3339 l_booked_flag,
3340 l_order_header_id
3341 FROM oe_order_lines_all
3342 WHERE line_id = p_demand_line_id ;
3343
3344 -- Bug 2366024 -- Do not perform the reservation check
3345 -- for drop ship orders - source_type_code = 'EXTERNAL'
3346
3347 -- Validate 1 -- the sales order has to be open
3348 IF (p_rsv_action_name = 'CREATE') OR
3349 ((p_rsv_action_name IN ('UPDATE','TRANSFER')) AND
3350 (Nvl(p_orig_demand_type_id,-99) <>
3351 Nvl(p_demand_type_id,-99)) OR
3352 (Nvl(p_orig_demand_header_id,-99) <>
3353 Nvl(p_demand_header_id,-99)) OR
3354 (Nvl(p_orig_demand_line_id,-99) <> Nvl(p_demand_line_id,-99))) THEN
3355 IF nvl(l_line_rec_open_flag, 'N') <> 'Y' AND Nvl(l_source_type_code, 'INTERNAL') <> 'EXTERNAL' THEN
3356 FND_MESSAGE.SET_NAME('INV', 'INV_RESERVATION_CLOSED_SO');
3357 FND_MSG_PUB.add;
3358 RAISE fnd_api.g_exc_error;
3359 END IF;
3360 END IF;
3361
3362 /* Bug 3118495 -- Should not allow user to create a reservation against a shipped sales order line */
3363 -- Validate 2 -- the sales order line should not be in 'SHIPPED' status
3364 IF l_flow_status_code = 'SHIPPED' THEN
3365 FND_MESSAGE.SET_NAME('INV', 'INV_RESERVATION_SHIPPED_SO');
3366 FND_MSG_PUB.add;
3367 RAISE fnd_api.g_exc_error;
3368 END IF;
3369 -- Validate 3 -- Item : The item on the reservation has to
3370 -- be the same as the item on the sales order line
3371 /* Bug 6044651 Do not perform this validation if substitue item is being used in a sales order */
3372 IF p_substitute_flag <> TRUE THEN
3373 IF p_reservation_org_id <> l_line_rec_org_id
3374 OR p_reservation_item_id <> l_line_rec_inventory_item_id THEN
3375
3376 FND_MESSAGE.SET_NAME('INV', 'INV_RESERVATION_INVALID_ITEM');
3377 FND_MSG_PUB.add;
3378 RAISE fnd_api.g_exc_error;
3379 END IF;
3380 END IF;
3381 /* End of Bug 6044651 */
3382
3383 -- /*** {{ R12 Enhanced reservations code changes ***/
3384 -- Validate 4 -- booked_flag: If the supply is not Inventory, sales
3385 -- order has to be booked.
3386 IF (nvl(l_booked_flag, 'N') <> 'Y' AND p_supply_type_id <> inv_reservation_global.g_source_type_inv) THEN
3387 FND_MESSAGE.SET_NAME('INV', 'INV_RSV_SO_NOT_BOOKED');
3388 FND_MSG_PUB.ADD;
3389 RAISE fnd_api.g_exc_error;
3390 END IF;
3391
3392 -- bugfix 16104833 --added the check for Drop Ship
3393 --
3394 IF l_debug = 1 THEN
3395 debug_print ('Inside validate sales order :l_source_type_code ' || l_source_type_code);
3396 debug_print ('Inside validate sales order :oe_ds_pvt.g_ds_soissue_flag ' || OE_DS_PVT.G_DS_SOISSUE_FLAG);
3397 END IF;
3398 --
3399 -- Validate 5 -- if the demand type is sales order, call validate_demand_source_so
3400 -- to see if the sales order is dropship order.
3401 IF (p_demand_type_id = inv_reservation_global.g_source_type_oe and NVL(l_source_type_code, 'INTERNAL') = 'EXTERNAL' and OE_DS_PVT.G_DS_SOISSUE_FLAG <> 'Y') THEN
3402 validate_demand_source_so
3403 ( x_return_status => l_return_status
3404 , p_demand_source_type_id => p_demand_type_id
3405 , p_demand_source_header_id => l_order_header_id
3406 , p_demand_source_line_id => p_demand_line_id
3407 , p_demand_source_line_detail => null
3408 );
3409
3410 IF l_debug = 1 THEN
3411 debug_print ('Inside validate sales order after calling validate so' || l_return_status);
3412 END IF;
3413
3414 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
3415 RAISE fnd_api.g_exc_error;
3416 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
3417 RAISE fnd_api.g_exc_unexpected_error;
3418 END IF;
3419
3420 END IF;
3421 /*** End R12 }} ***/
3422
3423 -- Validate 3: Reservation Qty
3424 -- Bug 1620576 - We can no longer carry out this validation.
3425 -- We allow over-reserving when we do an overpick.
3426 /*
3427 *-- Convert order quantity into reservation uom code
3428 *l_ordered_quantity_rsv_uom := inv_convert.inv_um_convert(
3429 * l_line_rec_inventory_item_id,
3430 * NULL,
3431 * l_line_rec_ordered_quantity,
3432 * l_line_rec_order_quantity_uom,
3433 * p_reservation_uom_code,
3434 * NULL,
3435 * NULL);
3436 *
3437 *
3438 *-- Fetch quantity reserved so far
3439 *SELECT nvl(sum(primary_reservation_quantity),0)
3440 *INTO l_primary_reserved_quantity
3441 *FROM mtl_reservations
3442 *WHERE demand_source_type_id = p_demand_type_id
3443 *AND demand_source_header_id = p_demand_header_id
3444 *AND demand_source_line_id = p_demand_line_id
3445 *AND reservation_id <> nvl(p_reservation_id,-1);
3446 *
3447 *IF l_primary_reserved_quantity > 0 then
3448 *
3449 * -- Get primary UOM
3450 * select primary_uom_code
3451 * into l_primary_uom_code
3452 * from mtl_system_items
3453 * where organization_id = l_line_rec_org_id
3454 * and inventory_item_id = l_line_rec_inventory_item_id;
3455 *
3456 * -- Convert primary reservation quantity into
3457 * -- reservation uom code
3458 * l_reserved_quantity :=
3459 * inv_convert.inv_um_convert
3460 * (
3461 * l_line_rec_inventory_item_id,
3462 * NULL,
3463 * l_primary_reserved_quantity,
3464 * l_primary_uom_code,
3465 * p_reservation_uom_code,
3466 * NULL,
3467 * NULL);
3468 *else
3469 * l_reserved_quantity := 0;
3470 *end if;
3471 **
3472 *-- Quantity that can be still reserved must be no less than the
3473 *-- reservation quantity--- can not over reserve
3474 **
3475 *IF (l_ordered_quantity_rsv_uom - l_reserved_quantity) <
3476 * p_reservation_quantity THEN
3477 * FND_MESSAGE.SET_NAME('INV','INV_RSV_ORDER_QTY_VALID');
3478 * FND_MSG_PUB.ADD;
3479 * RAISE fnd_api.g_exc_error;
3480 *END IF;
3481 */
3482
3483 IF (l_debug = 1) THEN
3484 debug_print ('Inside validate sales order after' || l_return_status);
3485 END IF;
3486
3487 END IF;
3488
3489 EXCEPTION
3490 WHEN fnd_api.g_exc_error THEN
3491 x_return_status := fnd_api.g_ret_sts_error;
3492 --
3493 WHEN OTHERS THEN
3494 x_return_status := fnd_api.g_ret_sts_unexp_error ;
3495 --
3496 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
3497 THEN
3498 fnd_msg_pub.add_exc_msg
3499 ( g_pkg_name
3500 , 'Validate_Sales_Order'
3501 );
3502 END IF;
3503 END validate_sales_order;
3504
3505 --
3506 -- Procedure
3507 -- validate_demand_source
3508 -- Description
3509 -- is valid if all of the following are satisfied
3510 -- 1. p_demand_source_type_id is not null
3511 -- 2. p_demand_source_header_id
3512 -- or p_demand_source_name is not null
3513 -- 3. if p_demand_source_type_id is inventory or the type id > 100
3514 -- (user defined source type), the p_demand_source_name is not null
3515 -- 4. if p_demand_source_type is account, account number is valid
3516 -- 5. if p_demand_source_type is account alias, alias is valid
3517 -- /*** {{ R12 Enhanced reservations code changes ***/
3518 -- 6. if p_demand_source_type is WIP, return errors if entity type
3519 -- is not CMRO, OPM Batch or OPM FPO
3520 -- /*** End R12 }} ***/
3521 PROCEDURE validate_demand_source
3522 (
3523 x_return_status OUT NOCOPY VARCHAR2
3524 , p_rsv_action_name IN VARCHAR2
3525 , p_inventory_item_id IN NUMBER
3526 , p_organization_id IN NUMBER
3527 , p_demand_source_type_id IN NUMBER
3528 , p_demand_source_header_id IN NUMBER
3529 , p_demand_source_line_id IN NUMBER
3530 , p_demand_source_line_detail IN NUMBER
3531 , p_orig_demand_source_type_id IN NUMBER
3532 , p_orig_demand_source_header_id IN NUMBER
3533 , p_orig_demand_source_line_id IN NUMBER
3534 , p_orig_demand_source_detail IN NUMBER
3535 , p_demand_source_name IN VARCHAR2
3536 , p_reservation_id IN NUMBER
3537 , p_reservation_quantity IN NUMBER
3538 , p_reservation_uom_code IN VARCHAR2
3539 , p_supply_type_id IN NUMBER
3540 , p_demand_ship_date IN DATE
3541 , p_supply_receipt_date IN DATE
3542 , x_demand_cache_index OUT NOCOPY INTEGER
3543 , p_substitute_flag IN BOOLEAN DEFAULT FALSE /* Bug 6044651 */
3544 ) IS
3545 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
3546 l_structure_num NUMBER := NULL;
3547 l_index NUMBER := NULL;
3548 l_is_valid NUMBER := NULL;
3549 l_rec inv_reservation_global.demand_record;
3550 /*** {{ R12 Enhanced reservations code changes ***/
3551 l_debug NUMBER;
3552 l_msg_count NUMBER;
3553 l_msg_data VARCHAR2(1000);
3554 l_wip_entity_type NUMBER;
3555 l_wip_job_type VARCHAR2(15);
3556 /*** End R12 }} ***/
3557 BEGIN
3558 --
3559 /*** {{ R12 Enhanced reservations code changes ***/
3560 IF (g_debug IS NULL) THEN
3561 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3562 END IF;
3563
3564 l_debug := g_debug;
3565
3566 IF (l_debug = 1) THEN
3567 debug_print('In validate_demand_source: ' ||
3568 ', rsv_action_name = ' || p_rsv_action_name ||
3569 ', inventory_item_id = ' || p_inventory_item_id ||
3570 ', organization_id = ' || p_organization_id ||
3571 ', demand_source_type_id = ' || p_demand_source_type_id ||
3572 ', demand_source_header_id = ' || p_demand_source_header_id ||
3573 ', demand_source_line_id = ' || p_demand_source_line_id ||
3574 ', demand_source_detail = ' || p_demand_source_line_detail ||
3575 ', orig_demand_source_type_id = ' || p_orig_demand_source_type_id ||
3576 ', orig_demand_source_header_id = ' || p_orig_demand_source_header_id ||
3577 ', orig_demand_source_line_id = ' || p_orig_demand_source_line_id ||
3578 ', orig_demand_source_detail = ' || p_orig_demand_source_detail ||
3579 ', demand_source_name = ' || p_demand_source_name ||
3580 ', reservation_id = ' || p_reservation_id ||
3581 ', reservation_quantity = ' || p_reservation_quantity ||
3582 ', reservation_uom_code = ' || p_reservation_uom_code ||
3583 ', supply_type_id = ' || p_supply_type_id ||
3584 ', demand_ship_date = ' || p_demand_ship_date ||
3585 ', supply_receipt_date = ' || p_supply_receipt_date);
3586 END IF;
3587 /*** End R12 }} ***/
3588
3589 IF p_demand_source_type_id IS NULL
3590 OR p_demand_source_header_id IS NULL
3591 AND p_demand_source_name IS NULL THEN
3592 fnd_message.set_name('INV', 'MISSING DEMAND SOURCE');
3593 fnd_msg_pub.add;
3594 END IF;
3595 --
3596 -- Bug 6124188 Added Demand Source of PO for which reservation gets created
3597 -- in case Return is attemped with profile 'WMS:Express Return' as 'No'
3598
3599 /*** {{ R12 Enhanced reservations code changes ***/
3600 IF (p_demand_source_type_id NOT IN
3601 (inv_reservation_global.g_source_type_inv,inv_reservation_global.g_source_type_po,
3602 inv_reservation_global.g_source_type_oe, inv_reservation_global.g_source_type_account,
3603 inv_reservation_global.g_source_type_account_alias,
3604 inv_reservation_global.g_source_type_cycle_count, inv_reservation_global.g_source_type_physical_inv,
3605 inv_reservation_global.g_source_type_internal_ord,
3606 inv_reservation_global.g_source_type_rma, inv_reservation_global.g_source_type_wip)
3607 AND NOT(p_demand_source_type_id >= 100)) THEN --Bug# 10194255
3608 fnd_message.set_name('INV','INV_INVALID_DEMAND_SOURCE');
3609 fnd_msg_pub.add;
3610 RAISE fnd_api.g_exc_error;
3611 END IF;
3612 /*** End R12 }} ***/
3613
3614 -- if the demand source type is inventory, or type id > 100
3615 -- the source name should not be null
3616 IF p_demand_source_type_id = inv_reservation_global.g_source_type_inv
3617 OR p_demand_source_type_id >= 100 THEN --Bug# 10194255
3618 if p_demand_source_name IS NULL THEN
3619 fnd_message.set_name('INV','INV_INVALID_DEMAND_SOURCE');
3620 fnd_msg_pub.add;
3621 RAISE fnd_api.g_exc_error;
3622 END IF;
3623 END IF;
3624 --
3625 -- search for the demand source in the cache first
3626 inv_reservation_util_pvt.search_demand_cache
3627 (
3628 x_return_status => x_return_status
3629 , p_demand_source_type_id => p_demand_source_type_id
3630 , p_demand_source_header_id => p_demand_source_header_id
3631 , p_demand_source_line_id => p_demand_source_line_id
3632 , p_demand_source_name => p_demand_source_name
3633 , x_index => l_index
3634 );
3635 --
3636 IF l_return_status = fnd_api.g_ret_sts_error THEN
3637 RAISE fnd_api.g_exc_error;
3638 END IF ;
3639 --
3640 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3641 RAISE fnd_api.g_exc_unexpected_error;
3642 END IF;
3643 --
3644 -- I would just put valid demand source in the cache for
3645 -- now. so I do not need to check is_valid
3646 -- if the source is already in the cache, return successful
3647 IF l_index IS NOT NULL THEN
3648 x_demand_cache_index := l_index;
3649 x_return_status := l_return_status;
3650 RETURN;
3651 END IF;
3652 --
3653 -- not in cache goes here
3654 -- if the source type is account, demand header id should not
3655 -- be null, and it should be a valid GL account number
3656 IF p_demand_source_type_id = inv_reservation_global.g_source_type_account
3657 THEN
3658 IF p_demand_source_header_id IS NOT NULL THEN
3659 BEGIN
3660 -- find the flex field structure number
3661 SELECT
3662 id_flex_num
3663 INTO l_structure_num
3664 FROM
3665 org_organization_definitions ood
3666 , fnd_id_flex_structures ffs
3667 WHERE
3668 ood.organization_id = p_organization_id
3669 AND ffs.id_flex_code = 'GL#'
3670 AND ood.chart_of_accounts_id = ffs.id_flex_num;
3671
3672 -- call fnd api to validate the account id
3673 IF NOT fnd_flex_keyval.validate_ccid
3674 (
3675 'SQLGL'
3676 , 'GL#'
3677 , l_structure_num
3678 , p_demand_source_header_id
3679 ) THEN
3680 fnd_message.set_name('INV', 'INVALID_ACCOUNT_NUMBER');
3681 fnd_msg_pub.add;
3682 RAISE fnd_api.g_exc_error;
3683 END IF;
3684 EXCEPTION
3685 WHEN no_data_found THEN
3686 fnd_message.set_name('INV', 'INVALID_ACCOUNT_NUMBER');
3687 fnd_msg_pub.add;
3688 RAISE fnd_api.g_exc_error;
3689 END;
3690 --
3691 ELSE
3692 fnd_message.set_name('INV', 'INVALID_ACCOUNT_NUMBER');
3693 fnd_msg_pub.add;
3694 RAISE fnd_api.g_exc_error;
3695 END IF;
3696 END IF;
3697 --
3698 IF p_demand_source_type_id
3699 = inv_reservation_global.g_source_type_account_alias
3700 THEN
3701 IF p_demand_source_header_id IS NOT NULL THEN
3702 IF NOT fnd_flex_keyval.validate_ccid
3703 (
3704 appl_short_name => 'INV'
3705 , key_flex_code => 'MDSP'
3706 , structure_number => 101
3707 , combination_id => p_demand_source_header_id
3708 , data_set => p_organization_id
3709 ) THEN
3710 fnd_message.set_name('INV', 'INVALID_ACCOUNT_ALIAS');
3711 fnd_msg_pub.add;
3712 RAISE fnd_api.g_exc_error;
3713 END IF;
3714 END IF;
3715 END IF;
3716
3717 /*** {{ R12 Enhanced reservations code changes ***/
3718 IF (p_demand_source_type_id = inv_reservation_global.g_source_type_wip) THEN
3719 -- get wip entity id from wip_record_cache
3720 inv_reservation_util_pvt.get_wip_cache
3721 (
3722 x_return_status => l_return_status
3723 , p_wip_entity_id => p_demand_source_header_id
3724 );
3725
3726 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
3727 RAISE fnd_api.g_exc_error;
3728 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
3729 RAISE fnd_api.g_exc_unexpected_error;
3730 ELSE
3731 l_wip_entity_type := inv_reservation_global.g_wip_record_cache(p_demand_source_header_id).wip_entity_type;
3732 l_wip_job_type := inv_reservation_global.g_wip_record_cache(p_demand_source_header_id).wip_entity_job;
3733 END IF;
3734
3735 IF (l_wip_entity_type = inv_reservation_global.g_wip_source_type_cmro) THEN
3736 validate_demand_source_cmro(
3737 x_return_status => l_return_status
3738 , p_organization_id => p_organization_id
3739 , p_inventory_item_id => p_inventory_item_id
3740 , p_demand_ship_date => p_demand_ship_date
3741 , p_supply_receipt_date => p_supply_receipt_date
3742 , p_demand_source_type_id => p_demand_source_type_id
3743 , p_demand_source_header_id => p_demand_source_header_id
3744 , p_demand_source_line_id => p_demand_source_line_id
3745 , p_demand_source_line_detail => p_demand_source_line_detail
3746 , p_wip_entity_type => l_wip_entity_type
3747 );
3748
3749 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
3750 RAISE fnd_api.g_exc_error;
3751 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
3752 RAISE fnd_api.g_exc_unexpected_error;
3753 END IF;
3754 ELSIF (l_wip_entity_type = inv_reservation_global.g_wip_source_type_fpo) THEN
3755 validate_demand_source_fpo(
3756 x_return_status => l_return_status
3757 , p_organization_id => p_organization_id
3758 , p_inventory_item_id => p_inventory_item_id
3759 , p_demand_ship_date => p_demand_ship_date
3760 , p_supply_receipt_date => p_supply_receipt_date
3761 , p_demand_source_type_id => p_demand_source_type_id
3762 , p_demand_source_header_id => p_demand_source_header_id
3763 , p_demand_source_line_id => p_demand_source_line_id
3764 , p_demand_source_line_detail => null
3765 , p_wip_entity_type => l_wip_entity_type
3766 );
3767
3768 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
3769 RAISE fnd_api.g_exc_error;
3770 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
3771 RAISE fnd_api.g_exc_unexpected_error;
3772 END IF;
3773 ELSIF (l_wip_entity_type = inv_reservation_global.g_wip_source_type_batch) THEN
3774 validate_demand_source_batch(
3775 x_return_status => l_return_status
3776 , p_organization_id => p_organization_id
3777 , p_inventory_item_id => p_inventory_item_id
3778 , p_demand_ship_date => p_demand_ship_date
3779 , p_supply_receipt_date => p_supply_receipt_date
3780 , p_demand_source_type_id => p_demand_source_type_id
3781 , p_demand_source_header_id => p_demand_source_header_id
3782 , p_demand_source_line_id => p_demand_source_line_id
3783 , p_demand_source_line_detail => null
3784 , p_wip_entity_type => l_wip_entity_type
3785 );
3786
3787 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
3788 RAISE fnd_api.g_exc_error;
3789 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
3790 RAISE fnd_api.g_exc_unexpected_error;
3791 END IF;
3792 END IF;
3793 END IF;
3794
3795 IF (p_demand_source_type_id IN (
3796 inv_reservation_global.g_source_type_oe
3797 , inv_reservation_global.g_source_type_internal_ord
3798 , inv_reservation_global.g_source_type_rma)
3799 ) THEN
3800 --Bug #5202033
3801 --If action is UPDATE/TRANSFER and the demand source info has not changed,
3802 --do not call validate_sales_order
3803 IF ( (p_rsv_action_name = 'CREATE')
3804 OR
3805 ( (p_rsv_action_name IN ('UPATE', 'TRANSFER')) AND
3806 ( (p_orig_demand_source_type_id <> p_demand_source_type_id) OR
3807 (p_orig_demand_source_header_id <> p_demand_source_header_id) OR
3808 (p_orig_demand_source_line_id <> p_demand_source_line_id)
3809 )
3810 )
3811 ) THEN
3812 validate_sales_order(
3813 x_return_status => l_return_status
3814 , p_rsv_action_name => p_rsv_action_name
3815 , p_reservation_id => p_reservation_id
3816 , p_demand_type_id => p_demand_source_type_id
3817 , p_demand_header_id => p_demand_source_header_id
3818 , p_demand_line_id => p_demand_source_line_id
3819 , p_orig_demand_type_id => p_orig_demand_source_type_id
3820 , p_orig_demand_header_id => p_orig_demand_source_header_id
3821 , p_orig_demand_line_id => p_orig_demand_source_line_id
3822 , p_reservation_quantity => p_reservation_quantity
3823 , p_reservation_uom_code => p_reservation_uom_code
3824 , p_reservation_item_id => p_inventory_item_id
3825 , p_reservation_org_id => p_organization_id
3826 , p_supply_type_id => p_supply_type_id
3827 , p_substitute_flag => p_substitute_flag); /* Bug 6044651 */
3828
3829
3830 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
3831 RAISE fnd_api.g_exc_error;
3832 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
3833 RAISE fnd_api.g_exc_unexpected_error;
3834 END IF;
3835 END IF; --END IF check p_rsv_action
3836 END IF; --END IF demand source in SO, Internal Order, RMA
3837
3838 IF (l_debug = 1) THEN
3839 debug_print ('After calling validate sales order from within demand source' ||
3840 l_return_status);
3841 END IF;
3842
3843 /*** End R12 }} ***/
3844
3845 --
3846 -- comment out lines below until R11.8 when more demand sources
3847 -- will be considered
3848 -- IF p_demand_source_type_id
3849 -- = inv_reservation_global.g_source_type_wip THEN
3850 -- call wip_validation api()
3851 -- IF p_demand_source_type_id
3852 -- = inv_reservation_global.g_source_type_oe THEN
3853 -- call oe_validation api()
3854 --
3855 l_rec.demand_source_type_id := p_demand_source_type_id;
3856 l_rec.demand_source_header_id := p_demand_source_header_id;
3857 l_rec.demand_source_line_id := p_demand_source_line_id;
3858 l_rec.demand_source_name := p_demand_source_name;
3859 l_rec.is_valid := 1; -- 1 = true
3860 --
3861 inv_reservation_util_pvt.add_demand_cache
3862 (
3863 x_return_status => l_return_status
3864 , p_demand_record => l_rec
3865 , x_index => l_index
3866 );
3867 --
3868 IF l_return_status = fnd_api.g_ret_sts_error THEN
3869 RAISE fnd_api.g_exc_error;
3870 END IF ;
3871 --
3872 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3873 RAISE fnd_api.g_exc_unexpected_error;
3874 END IF;
3875 --
3876 x_demand_cache_index := l_index;
3877 x_return_status := l_return_status;
3878 --
3879 EXCEPTION
3880 WHEN fnd_api.g_exc_error THEN
3881 x_return_status := fnd_api.g_ret_sts_error;
3882 --
3883 WHEN fnd_api.g_exc_unexpected_error THEN
3884 x_return_status := fnd_api.g_ret_sts_unexp_error ;
3885 --
3886 WHEN OTHERS THEN
3887 x_return_status := fnd_api.g_ret_sts_unexp_error ;
3888 --
3889 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
3890 THEN
3891 fnd_msg_pub.add_exc_msg
3892 ( g_pkg_name
3893 , 'Validate_Demand_Source'
3894 );
3895 END IF;
3896 --
3897 END validate_demand_source;
3898
3899 /*** {{ R12 Enhanced reservations code changes ***/
3900 -- Procedure
3901 -- create_crossdock_reservation
3902 -- Description
3903 -- This procedure validates reservations that are crossdocked and
3904 -- indicates whether the intended action can be performed on that
3905 -- reservation record. This is called when a reservation is being
3906 -- created.
3907
3908 PROCEDURE create_crossdock_reservation
3909 (
3910 x_return_status OUT NOCOPY VARCHAR2
3911 , x_msg_count OUT NOCOPY NUMBER
3912 , x_msg_data OUT NOCOPY VARCHAR2
3913 , p_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
3914 ) IS
3915 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
3916 l_msg_count NUMBER;
3917 l_msg_data VARCHAR2(1000);
3918 l_debug NUMBER;
3919
3920 BEGIN
3921
3922 IF (g_debug IS NULL) THEN
3923 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3924 END IF;
3925
3926 l_debug := g_debug;
3927
3928 IF (l_debug = 1) THEN
3929 debug_print('In create_crossdock_reservation');
3930 debug_print('crossdock_criteria_id = ' || p_rsv_rec.crossdock_criteria_id);
3931 END IF;
3932
3933 IF ((p_rsv_rec.crossdock_criteria_id is not null) and
3934 (p_rsv_rec.crossdock_criteria_id <> fnd_api.g_miss_num)) THEN
3935 wms_xdock_utils_pvt.create_crossdock_reservation(
3936 x_return_status => l_return_status
3937 , p_rsv_rec => p_rsv_rec
3938 );
3939
3940 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
3941 IF (l_debug = 1) THEN
3942 debug_print('create_crossdock_reservation returns error');
3943 END IF;
3944 raise fnd_api.g_exc_error;
3945 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
3946 IF (l_debug = 1) THEN
3947 debug_print('create_crossdock_reservation returns unexpected error');
3948 END IF;
3949 raise fnd_api.g_exc_unexpected_error;
3950 END IF;
3951 END IF;
3952
3953 x_return_status := l_return_status;
3954
3955 EXCEPTION
3956 WHEN fnd_api.g_exc_error THEN
3957 x_return_status := fnd_api.g_ret_sts_error;
3958 --
3959 WHEN fnd_api.g_exc_unexpected_error THEN
3960 x_return_status := fnd_api.g_ret_sts_unexp_error ;
3961 --
3962 WHEN OTHERS THEN
3963 x_return_status := fnd_api.g_ret_sts_unexp_error ;
3964 --
3965 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
3966 THEN
3967 fnd_msg_pub.add_exc_msg
3968 ( g_pkg_name
3969 , 'create_crossdock_reservation'
3970 );
3971 END IF;
3972 --
3973 END create_crossdock_reservation;
3974 /*** End R12 }} ***/
3975
3976
3977 /*** {{ R12 Enhanced reservations code changes ***/
3978 -- Procedure
3979 -- update_crossdock_reservation
3980 -- Description
3981 -- This procedure validates reservations that are crossdocked and
3982 -- indicates whether the intended action can be performed on that
3983 -- reservation record. This is called when a reservation is being
3984 -- updated.
3985
3986 PROCEDURE update_crossdock_reservation
3987 (
3988 x_return_status OUT NOCOPY VARCHAR2
3989 , x_msg_count OUT NOCOPY NUMBER
3990 , x_msg_data OUT NOCOPY VARCHAR2
3991 , p_orig_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
3992 , p_to_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
3993 ) IS
3994 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
3995 l_msg_count NUMBER;
3996 l_msg_data VARCHAR2(1000);
3997 l_debug NUMBER;
3998
3999 BEGIN
4000
4001 IF (g_debug IS NULL) THEN
4002 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4003 END IF;
4004
4005 l_debug := g_debug;
4006
4007 IF (l_debug = 1) THEN
4008 debug_print('In update_crossdock_reservation');
4009 debug_print('crossdock_criteria_id = ' || p_to_rsv_rec.crossdock_criteria_id);
4010 END IF;
4011
4012 IF ((p_to_rsv_rec.crossdock_criteria_id is not null) and
4013 (p_to_rsv_rec.crossdock_criteria_id <> fnd_api.g_miss_num)) THEN
4014 wms_xdock_utils_pvt.update_crossdock_reservation(
4015 x_return_status => l_return_status
4016 , p_orig_rsv_rec => p_orig_rsv_rec
4017 , p_new_rsv_rec => p_to_rsv_rec
4018 );
4019
4020 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
4021 IF (l_debug = 1) THEN
4022 debug_print('update_crossdock_reservation returns error');
4023 END IF;
4024 raise fnd_api.g_exc_error;
4025 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
4026 IF (l_debug = 1) THEN
4027 debug_print('update_crossdock_reservation returns unexpected error');
4028 END IF;
4029 raise fnd_api.g_exc_unexpected_error;
4030 END IF;
4031 END IF;
4032
4033 x_return_status := l_return_status;
4034
4035 EXCEPTION
4036 WHEN fnd_api.g_exc_error THEN
4037 x_return_status := fnd_api.g_ret_sts_error;
4038 --
4039 WHEN fnd_api.g_exc_unexpected_error THEN
4040 x_return_status := fnd_api.g_ret_sts_unexp_error ;
4041 --
4042 WHEN OTHERS THEN
4043 x_return_status := fnd_api.g_ret_sts_unexp_error ;
4044 --
4045 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
4046 THEN
4047 fnd_msg_pub.add_exc_msg
4048 ( g_pkg_name
4049 , 'update_crossdock_reservation'
4050 );
4051 END IF;
4052 --
4053 END update_crossdock_reservation;
4054 /*** End R12 }} ***/
4055
4056 /*** {{ R12 Enhanced reservations code changes ***/
4057 -- Procedure
4058 -- transfer_crossdock_reservation
4059 -- Description
4060 -- This procedure validates reservations that are crossdocked and
4061 -- indicates whether the intended action can be performed on that
4062 -- reservation record. This is called when a reservation is being
4063 -- transferred.
4064
4065 PROCEDURE transfer_crossdock_reservation
4066 (
4067 x_return_status OUT NOCOPY VARCHAR2
4068 , x_msg_count OUT NOCOPY NUMBER
4069 , x_msg_data OUT NOCOPY VARCHAR2
4070 , p_orig_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
4071 , p_to_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
4072 ) IS
4073 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
4074 l_msg_count NUMBER;
4075 l_msg_data VARCHAR2(1000);
4076 l_debug NUMBER;
4077
4078 BEGIN
4079
4080 IF (g_debug IS NULL) THEN
4081 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4082 END IF;
4083
4084 l_debug := g_debug;
4085
4086 IF (l_debug = 1) THEN
4087 debug_print('In transfer_crossdock_reservation');
4088 debug_print('crossdock_criteria_id = ' || p_to_rsv_rec.crossdock_criteria_id);
4089 END IF;
4090
4091 IF ((p_to_rsv_rec.crossdock_criteria_id is not null) and
4092 (p_to_rsv_rec.crossdock_criteria_id <> fnd_api.g_miss_num)) THEN
4093 wms_xdock_utils_pvt.transfer_crossdock_reservation(
4094 x_return_status => l_return_status
4095 , p_orig_rsv_rec => p_orig_rsv_rec
4096 , p_new_rsv_rec => p_to_rsv_rec
4097 );
4098 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
4099 IF (l_debug = 1) THEN
4100 debug_print('transfer_crossdock_reservation returns error');
4101 END IF;
4102 raise fnd_api.g_exc_error;
4103 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
4104 IF (l_debug = 1) THEN
4105 debug_print('transfer_crossdock_reservation returns unexpected error');
4106 END IF;
4107 raise fnd_api.g_exc_unexpected_error;
4108 END IF;
4109 END IF;
4110
4111 x_return_status := l_return_status;
4112
4113 EXCEPTION
4114 WHEN fnd_api.g_exc_error THEN
4115 x_return_status := fnd_api.g_ret_sts_error;
4116 --
4117 WHEN fnd_api.g_exc_unexpected_error THEN
4118 x_return_status := fnd_api.g_ret_sts_unexp_error ;
4119 --
4120 WHEN OTHERS THEN
4121 x_return_status := fnd_api.g_ret_sts_unexp_error ;
4122 --
4123 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
4124 THEN
4125 fnd_msg_pub.add_exc_msg
4126 ( g_pkg_name
4127 , 'transfer_crossdock_reservation'
4128 );
4129 END IF;
4130 --
4131 END transfer_crossdock_reservation;
4132 /*** End R12 }} ***/
4133
4134 /*** {{ R12 Enhanced reservations code changes ***/
4135 -- Procedure
4136 -- relieve_crossdock_reservation
4137 -- Description
4138 -- This procedure validates reservations that are crossdocked and
4139 -- indicates whether the intended action can be performed on that
4140 -- reservation record. This is called when a reservation is being
4141 -- relieved.
4142
4143 PROCEDURE relieve_crossdock_reservation
4144 (
4145 x_return_status OUT NOCOPY VARCHAR2
4146 , x_msg_count OUT NOCOPY NUMBER
4147 , x_msg_data OUT NOCOPY VARCHAR2
4148 , p_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
4149 ) IS
4150 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
4151 l_msg_count NUMBER;
4152 l_msg_data VARCHAR2(1000);
4153 l_debug NUMBER;
4154
4155 BEGIN
4156
4157 IF (g_debug IS NULL) THEN
4158 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4159 END IF;
4160
4161 l_debug := g_debug;
4162
4163 IF (l_debug = 1) THEN
4164 debug_print('In relieve_crossdock_reservation');
4165 debug_print('crossdock_criteria_id = ' || p_rsv_rec.crossdock_criteria_id);
4166 END IF;
4167
4168 IF ((p_rsv_rec.crossdock_criteria_id is not null) and
4169 (p_rsv_rec.crossdock_criteria_id <> fnd_api.g_miss_num)) THEN
4170 wms_xdock_utils_pvt.relieve_crossdock_reservation(
4171 x_return_status => l_return_status
4172 , p_rsv_rec => p_rsv_rec
4173 );
4174
4175 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
4176 IF (l_debug = 1) THEN
4177 debug_print('relieve_crossdock_reservation returns error');
4178 END IF;
4179 raise fnd_api.g_exc_error;
4180 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
4181 IF (l_debug = 1) THEN
4182 debug_print('relieve_crossdock_reservation returns unexpected error');
4183 END IF;
4184 raise fnd_api.g_exc_unexpected_error;
4185 END IF;
4186 END IF;
4187
4188 x_return_status := l_return_status;
4189
4190 EXCEPTION
4191 WHEN fnd_api.g_exc_error THEN
4192 x_return_status := fnd_api.g_ret_sts_error;
4193 --
4194 WHEN fnd_api.g_exc_unexpected_error THEN
4195 x_return_status := fnd_api.g_ret_sts_unexp_error ;
4196 --
4197 WHEN OTHERS THEN
4198 x_return_status := fnd_api.g_ret_sts_unexp_error ;
4199 --
4200 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
4201 THEN
4202 fnd_msg_pub.add_exc_msg
4203 ( g_pkg_name
4204 , 'relieve_crossdock_reservation'
4205 );
4206 END IF;
4207 --
4208 END relieve_crossdock_reservation;
4209
4210 /*** {{ R12 Enhanced reservations code changes ***/
4211 -- Procedure
4212 -- delete_crossdock_reservation
4213 -- Description
4214 -- This procedure validates reservations that are crossdocked and
4215 -- indicates whether the intended action can be performed on that
4216 -- reservation record. This is called when a reservation is being
4217 -- deleted.
4218
4219 PROCEDURE delete_crossdock_reservation
4220 (
4221 x_return_status OUT NOCOPY VARCHAR2
4222 , x_msg_count OUT NOCOPY NUMBER
4223 , x_msg_data OUT NOCOPY VARCHAR2
4224 , p_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
4225 ) IS
4226 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
4227 l_msg_count NUMBER;
4228 l_msg_data VARCHAR2(1000);
4229 l_debug NUMBER;
4230
4231 BEGIN
4232
4233 IF (g_debug IS NULL) THEN
4234 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4235 END IF;
4236
4237 l_debug := g_debug;
4238
4239 IF (l_debug = 1) THEN
4240 debug_print('In delete_crossdock_reservation');
4241 debug_print('crossdock_criteria_id = ' || p_rsv_rec.crossdock_criteria_id);
4242 END IF;
4243
4244 IF ((p_rsv_rec.crossdock_criteria_id is not null) and
4245 (p_rsv_rec.crossdock_criteria_id <> fnd_api.g_miss_num)) THEN
4246 wms_xdock_utils_pvt.delete_crossdock_reservation(
4247 x_return_status => l_return_status
4248 , p_rsv_rec => p_rsv_rec
4249 );
4250
4251 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
4252 IF (l_debug = 1) THEN
4253 debug_print('delete_crossdock_reservation returns error');
4254 END IF;
4255 raise fnd_api.g_exc_error;
4256 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
4257 IF (l_debug = 1) THEN
4258 debug_print('delete_crossdock_reservation returns unexpected error');
4259 END IF;
4260 raise fnd_api.g_exc_unexpected_error;
4261 END IF;
4262 END IF;
4263
4264 x_return_status := l_return_status;
4265
4266 EXCEPTION
4267 WHEN fnd_api.g_exc_error THEN
4268 x_return_status := fnd_api.g_ret_sts_error;
4269 --
4270 WHEN fnd_api.g_exc_unexpected_error THEN
4271 x_return_status := fnd_api.g_ret_sts_unexp_error ;
4272 --
4273 WHEN OTHERS THEN
4274 x_return_status := fnd_api.g_ret_sts_unexp_error ;
4275 --
4276 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
4277 THEN
4278 fnd_msg_pub.add_exc_msg
4279 ( g_pkg_name
4280 , 'delete_crossdock_reservation'
4281 );
4282 END IF;
4283 --
4284 END delete_crossdock_reservation;
4285 /*** End R12 }} ***/
4286
4287 /*** {{ R12 Enhanced reservations code changes ***/
4288 -- Procedure
4289 -- validate_pjm_reservations
4290 -- Description
4291 -- This procedure validates reservation in PJM organization.
4292
4293 PROCEDURE validate_pjm_reservations
4294 (
4295 x_return_status OUT NOCOPY VARCHAR2
4296 , p_organization_id IN NUMBER
4297 , p_inventory_item_id IN NUMBER
4298 , p_supply_source_type_id IN NUMBER
4299 , p_supply_source_header_id IN NUMBER
4300 , p_supply_source_line_id IN NUMBER
4301 , p_supply_source_line_detail IN NUMBER
4302 , p_project_id IN NUMBER
4303 , p_task_id IN NUMBER
4304 ) IS
4305 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
4306 l_msg_count NUMBER;
4307 l_msg_data VARCHAR2(1000);
4308 l_wms_enabled VARCHAR2(1) := 'N';
4309 l_pjm_enabled NUMBER := 1;
4310 l_project_count NUMBER;
4311 l_project_id NUMBER;
4312 l_task_id NUMBER;
4313 l_wip_entity_type NUMBER;
4314 l_wip_job_type VARCHAR2(15);
4315 l_debug NUMBER;
4316 p_mtl_maintain_rsv_rec inv_reservation_global.mtl_maintain_rsv_rec_type;
4317 l_delete_flag VARCHAR2(1) := 'N';
4318 l_sort_by_criteria Number;
4319 l_qty_modified NUMBER := 0;
4320 BEGIN
4321
4322 IF (g_debug IS NULL) THEN
4323 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4324 END IF;
4325
4326 l_debug := g_debug;
4327
4328 IF (l_debug = 1) THEN
4329 debug_print('In validate_pjm_reservations');
4330 debug_print('organization_id = ' || p_organization_id || ' , supply type = ' || p_supply_source_type_id);
4331 END IF;
4332
4333 SELECT wms_enabled_flag, project_reference_enabled
4334 INTO l_wms_enabled, l_pjm_enabled
4335 FROM mtl_parameters
4336 WHERE organization_id = p_organization_id;
4337
4338 IF (l_pjm_enabled = 1 and l_wms_enabled = 'Y') THEN
4339 IF (p_supply_source_type_id = inv_reservation_global.g_source_type_intransit) THEN
4340 IF (l_debug = 1) THEN
4341 debug_print('Reservation of intransit shipment supply cannot be created in PJM and WMS organization');
4342 END IF;
4343
4344 fnd_message.set_name('INV', 'INV_RSV_PJM_WMS_INTRAN');
4345 fnd_msg_pub.ADD;
4346
4347 RAISE fnd_api.g_exc_error;
4348 ELSIF (p_supply_source_type_id = inv_reservation_global.g_source_type_po OR
4349 p_supply_source_type_id = inv_reservation_global.g_source_type_asn) THEN
4350
4351 SELECT count(min(po_distribution_id))
4352 INTO l_project_count
4353 FROM po_distributions_all
4354 WHERE po_header_id = p_supply_source_header_id
4355 AND line_location_id = p_supply_source_line_id
4356 group by project_id, task_id;
4357
4358 IF (l_project_count > 1) THEN
4359 IF (l_debug = 1) THEN
4360 debug_print('Multiple project and task combinations exists for the supply line');
4361 debug_print('We need to delete the reservations for this supply');
4362 END IF;
4363 -- Call the reduce reservations API by setting the
4364 -- delete_flag to yes. delete all reservations for that
4365 -- supply line.
4366 l_delete_flag := 'Y';
4367 l_sort_by_criteria := inv_reservation_global.g_query_demand_ship_date_desc;
4368 p_mtl_maintain_rsv_rec.organization_id := p_organization_id;
4369 p_mtl_maintain_rsv_rec.inventory_item_id := p_inventory_item_id;
4370 p_mtl_maintain_rsv_rec.supply_source_type_id := p_supply_source_type_id;
4371 p_mtl_maintain_rsv_rec.supply_source_header_id := p_supply_source_header_id;
4372 p_mtl_maintain_rsv_rec.supply_source_line_id := p_supply_source_line_id;
4373
4374 inv_maintain_reservation_pub.reduce_reservation
4375 (p_api_version_number => 1.0,
4376 p_init_msg_lst => fnd_api.g_false,
4377 x_return_status => l_return_status,
4378 x_msg_count => l_msg_count,
4379 x_msg_data => l_msg_data,
4380 p_mtl_maintain_rsv_rec => p_mtl_maintain_rsv_rec,
4381 p_delete_flag => l_delete_flag,
4382 p_sort_by_criteria => l_sort_by_criteria,
4383 x_quantity_modified => l_qty_modified
4384 );
4385
4386 IF l_debug=1 THEN
4387 debug_print ('Return Status after calling reduce reservations: '|| l_return_status);
4388 END IF;
4389
4390 IF l_return_status = fnd_api.g_ret_sts_error THEN
4391
4392 IF l_debug=1 THEN
4393 debug_print('Raising expected error'||l_return_status);
4394 END IF;
4395 RAISE fnd_api.g_exc_error;
4396
4397 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
4398
4399 IF l_debug=1 THEN
4400 debug_print('Rasing Unexpected error'||l_return_status);
4401 END IF;
4402 RAISE fnd_api.g_exc_unexpected_error;
4403
4404 END IF;
4405 END IF; -- project count > 1
4406
4407 IF ((p_project_id is not null) AND (l_project_count = 1)) THEN
4408 SELECT MIN(project_id), MIN(task_id)
4409 INTO l_project_id, l_task_id
4410 FROM po_distributions_all
4411 WHERE po_header_id = p_supply_source_header_id
4412 AND line_location_id = p_supply_source_line_id;
4413
4414 IF (l_project_id <> p_project_id or l_task_id <> p_task_id) THEN
4415 IF (l_debug = 1) THEN
4416 debug_print('The project and task of reservation record does not match with the supply line');
4417 END IF;
4418
4419 IF (l_debug = 1) THEN
4420 debug_print('Multiple project and task combinations exists for the supply line');
4421 debug_print('We need to delete the reservations for this supply');
4422 END IF;
4423 -- Call the reduce reservations API by setting the
4424 -- delete_flag to yes. delete all reservations for that
4425 -- supply line.
4426 l_delete_flag := 'Y';
4427 l_sort_by_criteria := inv_reservation_global.g_query_demand_ship_date_desc;
4428 p_mtl_maintain_rsv_rec.organization_id := p_organization_id;
4429 p_mtl_maintain_rsv_rec.inventory_item_id := p_inventory_item_id;
4430 p_mtl_maintain_rsv_rec.supply_source_type_id := p_supply_source_type_id;
4431 p_mtl_maintain_rsv_rec.supply_source_header_id := p_supply_source_header_id;
4432 p_mtl_maintain_rsv_rec.supply_source_line_id := p_supply_source_line_id;
4433
4434 inv_maintain_reservation_pub.reduce_reservation
4435 (p_api_version_number => 1.0,
4436 p_init_msg_lst => fnd_api.g_false,
4437 x_return_status => l_return_status,
4438 x_msg_count => l_msg_count,
4439 x_msg_data => l_msg_data,
4440 p_mtl_maintain_rsv_rec => p_mtl_maintain_rsv_rec,
4441 p_delete_flag => l_delete_flag,
4442 p_sort_by_criteria => l_sort_by_criteria,
4443 x_quantity_modified => l_qty_modified
4444 );
4445
4446 IF l_debug=1 THEN
4447 debug_print ('Return Status after calling reduce reservations: '|| l_return_status);
4448 END IF;
4449
4450 IF l_return_status = fnd_api.g_ret_sts_error THEN
4451
4452 IF l_debug=1 THEN
4453 debug_print('Raising expected error'||l_return_status);
4454 END IF;
4455 RAISE fnd_api.g_exc_error;
4456
4457 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
4458
4459 IF l_debug=1 THEN
4460 debug_print('Rasing Unexpected error'||l_return_status);
4461 END IF;
4462 RAISE fnd_api.g_exc_unexpected_error;
4463
4464 END IF;
4465 END IF;
4466 END IF;
4467 ELSIF (p_supply_source_type_id = inv_reservation_global.g_source_type_internal_req OR
4468 p_supply_source_type_id = inv_reservation_global.g_source_type_req) THEN
4469
4470 SELECT count(1)
4471 INTO l_project_count
4472 FROM po_requisition_lines_all prl, po_req_distributions_all prd
4473 WHERE prl.requisition_header_id = p_supply_source_header_id
4474 AND prl.requisition_line_id = p_supply_source_line_id
4475 AND prl.requisition_line_id = prd.requisition_line_id
4476 group by prd.project_id, prd.task_id;
4477
4478 IF (l_project_count > 1) THEN
4479 IF (l_debug = 1) THEN
4480 debug_print('Multiple project and task combinations exists for the supply line');
4481 END IF;
4482
4483 fnd_message.set_name('INV', 'INV_RSV_SUP_MUL_PROJ');
4484 fnd_msg_pub.ADD;
4485 RAISE fnd_api.g_exc_error;
4486 END IF;
4487
4488 IF((p_project_id is not null) AND (l_project_count = 1)) THEN
4489 SELECT MIN(prd.project_id), MIN(prd.task_id)
4490 INTO l_project_id, l_task_id
4491 FROM po_requisition_lines_all prl, po_req_distributions_all prd
4492 WHERE prl.requisition_header_id = p_supply_source_header_id
4493 AND prl.requisition_line_id = p_supply_source_line_id
4494 AND prl.requisition_line_id = prd.requisition_line_id;
4495
4496 IF (l_project_id <> p_project_id or l_task_id <> p_task_id) THEN
4497 IF (l_debug = 1) THEN
4498 debug_print('The project and task of reservation record does not match with the supply line');
4499 END IF;
4500
4501 fnd_message.set_name('INV', 'INV_RSV_SUP_DIFF_PROJ');
4502 fnd_msg_pub.ADD;
4503 RAISE fnd_api.g_exc_error;
4504 END IF;
4505 END IF;
4506 ELSIF (p_supply_source_type_id = inv_reservation_global.g_source_type_wip) THEN
4507
4508 -- get wip entity id from wip_record_cache
4509 inv_reservation_util_pvt.get_wip_cache
4510 (
4511 x_return_status => l_return_status
4512 , p_wip_entity_id => p_supply_source_header_id
4513 );
4514
4515 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
4516 RAISE fnd_api.g_exc_error;
4517 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
4518 RAISE fnd_api.g_exc_unexpected_error;
4519 ELSE
4520 l_wip_entity_type := inv_reservation_global.g_wip_record_cache(p_supply_source_header_id).wip_entity_type;
4521 l_wip_job_type := inv_reservation_global.g_wip_record_cache(p_supply_source_header_id).wip_entity_job;
4522 END IF;
4523
4524 -- Commenting out the code as we dont validate for these supply
4525 --types
4526 /************************************
4527 IF (l_wip_entity_type IN (inv_reservation_global.g_wip_source_type_discrete,
4528 inv_reservation_global.g_wip_source_type_osfm,
4529 inv_reservation_global.g_wip_source_type_fpo,
4530 inv_reservation_global.g_wip_source_type_batch)) THEN
4531
4532 SELECT count(1)
4533 INTO l_project_count
4534 FROM wip_discrete_jobs
4535 WHERE wip_entity_id = p_supply_source_header_id
4536 group by project_id, task_id;
4537
4538 IF (l_project_count > 1) THEN
4539 IF (l_debug = 1) THEN
4540 debug_print('Multiple project and task combinations exists for the supply line');
4541 END IF;
4542
4543 fnd_message.set_name('INV', 'INV_RSV_SUP_MUL_PROJ');
4544 fnd_msg_pub.ADD;
4545 RAISE fnd_api.g_exc_error;
4546 END IF;
4547
4548 IF (p_project_id is not null) THEN
4549 SELECT project_id, task_id
4550 INTO l_project_id, l_task_id
4551 FROM wip_discrete_jobs
4552 WHERE wip_entity_id = p_supply_source_header_id;
4553
4554 IF (l_project_id <> p_project_id or l_task_id <> p_task_id) THEN
4555 IF (l_debug = 1) THEN
4556 debug_print('The project and task of reservation record does not match with the supply line');
4557 END IF;
4558
4559 fnd_message.set_name('INV', 'INV_RSV_SUP_DIFF_PROJ');
4560 fnd_msg_pub.ADD;
4561 RAISE fnd_api.g_exc_error;
4562 END IF;
4563 END IF;
4564 END IF;
4565 --commention code for pjm validations for certain supplies
4566 *********************************/
4567 END IF;
4568 END IF;
4569
4570 x_return_status := l_return_status;
4571
4572 EXCEPTION
4573 WHEN fnd_api.g_exc_error THEN
4574 x_return_status := fnd_api.g_ret_sts_error;
4575 --
4576 WHEN fnd_api.g_exc_unexpected_error THEN
4577 x_return_status := fnd_api.g_ret_sts_unexp_error ;
4578 --
4579 WHEN OTHERS THEN
4580 x_return_status := fnd_api.g_ret_sts_unexp_error ;
4581 --
4582 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
4583 THEN
4584 fnd_msg_pub.add_exc_msg
4585 ( g_pkg_name
4586 , 'validate_pjm_reservations'
4587 );
4588 END IF;
4589 --
4590 END validate_pjm_reservations;
4591 /*** End R12 }} ***/
4592
4593 /*** {{ R12 Enhanced reservations code changes ***/
4594 -- Procedure
4595 -- validate_serials
4596 -- Description
4597 -- 1. validate the supply and demand source for serial reservation
4598 -- returns error if the supply is not INV or demand is not
4599 -- CMRO, SO or INV.
4600 -- 2. validate if the reservation record is detailed for serial
4601 -- reservation
4602 -- 3. validate the serial controls with the (org, item, rev, lot, sub, loc)
4603 -- controls on the reservation record.
4604 -- returns error if they don't match.
4605
4606 PROCEDURE validate_serials
4607 (
4608 x_return_status OUT NOCOPY VARCHAR2
4609 , p_orig_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
4610 , p_to_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
4611 , p_orig_serial_array IN inv_reservation_global.serial_number_tbl_type
4612 , p_to_serial_array IN inv_reservation_global.serial_number_tbl_type
4613 , p_rsv_action_name IN VARCHAR2
4614 ) IS
4615 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
4616 l_msg_count NUMBER;
4617 l_msg_data VARCHAR2(1000);
4618 l_wip_entity_type NUMBER;
4619 l_wip_job_type VARCHAR2(15);
4620 l_debug NUMBER;
4621 l_current_status NUMBER;
4622 l_organization_id NUMBER;
4623 l_revision VARCHAR2(3);
4624 l_subinventory VARCHAR2(10);
4625 l_locator_id NUMBER;
4626 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
4627 l_lot_number VARCHAR2(80);
4628 l_lpn_id NUMBER;
4629 l_reservation_id NUMBER;
4630 l_sub_cache_index NUMBER;
4631 l_item_cache_index NUMBER;
4632 l_org_cache_index NUMBER;
4633 l_result_locator_control NUMBER;
4634 l_orig_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
4635 l_to_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
4636 l_sub_rec inv_reservation_global.sub_record;
4637 l_org_rec inv_reservation_global.organization_record;
4638 l_item_rec inv_reservation_global.item_record;
4639
4640 CURSOR c_item(p_inventory_item_id NUMBER) IS
4641 SELECT *
4642 FROM mtl_system_items
4643 WHERE inventory_Item_Id = p_inventory_item_id;
4644
4645 BEGIN
4646
4647 IF (g_debug IS NULL) THEN
4648 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4649 END IF;
4650
4651 l_debug := g_debug;
4652
4653 IF (l_debug = 1) THEN
4654 debug_print('In validate_serials');
4655 debug_print('Supply type = ' || p_orig_rsv_rec.supply_source_type_id ||
4656 ' ,Demand type = ' || p_orig_rsv_rec.demand_source_type_id);
4657 debug_print('count of p_orig_serial_array: ' || p_orig_serial_array.COUNT);
4658 debug_print('count of p_to_serial_array: ' || p_to_serial_array.COUNT);
4659 END IF;
4660
4661 IF (p_orig_serial_array.COUNT > 0 or p_to_serial_array.COUNT > 0) THEN
4662
4663 IF (p_orig_serial_array.COUNT > 0) THEN
4664 IF (l_debug = 1) THEN
4665 debug_print('Inside from count > 0');
4666 END IF;
4667 -- return error if the p_orig_rsv_rec is null
4668 IF (p_orig_rsv_rec.organization_id is null OR p_orig_rsv_rec.organization_id = fnd_api.g_miss_num) THEN
4669 IF (l_debug = 1) THEN
4670 debug_print('The reservation record is null');
4671 END IF;
4672
4673 fnd_message.set_name('INV', 'INV_RSV_NULL_REC');
4674 fnd_msg_pub.ADD;
4675 RAISE fnd_api.g_exc_error;
4676 END IF;
4677
4678 inv_reservation_util_pvt.search_organization_cache
4679 (
4680 x_return_status => l_return_status
4681 , p_organization_id => p_orig_rsv_rec.organization_id
4682 , x_index => l_org_cache_index
4683 );
4684 --
4685 IF l_return_status = fnd_api.g_ret_sts_error THEN
4686 RAISE fnd_api.g_exc_error;
4687 End IF ;
4688 --
4689 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
4690 RAISE fnd_api.g_exc_unexpected_error;
4691 End IF;
4692 --
4693 IF l_org_cache_index IS NULL THEN
4694 l_org_rec.organization_id:= p_orig_rsv_rec.organization_id;
4695 IF INV_Validate.Organization(
4696 p_org => l_org_rec
4697 )=INV_Validate.F THEN
4698 fnd_message.set_name('INV', 'INVALID ORGANIZATION');
4699 fnd_msg_pub.add;
4700 RAISE fnd_api.g_exc_error;
4701 END IF;
4702
4703 --
4704 inv_reservation_util_pvt.add_organization_cache
4705 (
4706 x_return_status => l_return_status
4707 , p_organization_record => l_org_rec
4708 , x_index => l_org_cache_index
4709 );
4710 --
4711 IF l_return_status = fnd_api.g_ret_sts_error THEN
4712 RAISE fnd_api.g_exc_error;
4713 End IF ;
4714
4715 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
4716 RAISE fnd_api.g_exc_unexpected_error;
4717 End IF;
4718
4719 END IF;
4720
4721 -- validate the supply source for serial reservation of original reservation record
4722 IF (p_orig_rsv_rec.supply_source_type_id <> inv_reservation_global.g_source_type_inv) THEN
4723 IF (l_debug = 1) THEN
4724 debug_print('Serial reservation can be created with Inventory supply only');
4725 END IF;
4726
4727 fnd_message.set_name('INV', 'INV_RSV_SR_SUP_ERR');
4728 fnd_msg_pub.ADD;
4729 RAISE fnd_api.g_exc_error;
4730 END IF;
4731
4732 IF (l_debug = 1) THEN
4733 debug_print('Before calling WIP cache');
4734 END IF;
4735 -- validate the demand source for serial reservation of original reservation record
4736 IF (p_orig_rsv_rec.demand_source_type_id = inv_reservation_global.g_source_type_wip) THEN
4737 -- get wip entity id from wip_record_cache
4738 inv_reservation_util_pvt.get_wip_cache
4739 (
4740 x_return_status => l_return_status
4741 , p_wip_entity_id => p_orig_rsv_rec.demand_source_header_id
4742 );
4743
4744 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
4745 RAISE fnd_api.g_exc_error;
4746 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
4747 RAISE fnd_api.g_exc_unexpected_error;
4748 ELSE
4749 l_wip_entity_type := inv_reservation_global.g_wip_record_cache(p_orig_rsv_rec.demand_source_header_id).wip_entity_type;
4750 l_wip_job_type := inv_reservation_global.g_wip_record_cache(p_orig_rsv_rec.demand_source_header_id).wip_entity_job;
4751 END IF;
4752 END IF;
4753
4754 IF (l_debug = 1) THEN
4755 debug_print('After calling WIP cache');
4756 END IF;
4757
4758 IF ((p_orig_rsv_rec.demand_source_type_id NOT IN (inv_reservation_global.g_source_type_oe,
4759 inv_reservation_global.g_source_type_internal_ord,inv_reservation_global.g_source_type_rma,
4760 inv_reservation_global.g_source_type_inv)) AND
4761 (p_orig_rsv_rec.demand_source_type_id <> inv_reservation_global.g_source_type_wip AND
4762 l_wip_entity_type <> inv_reservation_global.g_wip_source_type_cmro)) THEN
4763
4764 IF (l_debug = 1) THEN
4765 debug_print('Serial reservation can be created with Inventory, sales order or CMRO demand only');
4766 END IF;
4767
4768 fnd_message.set_name('INV', 'INV_RSV_SR_DEM_ERR');
4769 fnd_msg_pub.ADD;
4770 RAISE fnd_api.g_exc_error;
4771 END IF;
4772
4773 IF (l_debug = 1) THEN
4774 debug_print('Before calling convert missing to null');
4775 END IF;
4776 -- convert the missing value in the reservation record to null
4777 inv_reservation_pvt.convert_missing_to_null
4778 (
4779 p_rsv_rec => p_orig_rsv_rec
4780 , x_rsv_rec => l_orig_rsv_rec
4781 );
4782
4783 IF (l_debug = 1) THEN
4784 debug_print('After convert missing to null');
4785 END IF;
4786
4787 -- get the revision control from item cache, first see if the item cache exists
4788 inv_reservation_util_pvt.search_item_cache
4789 (
4790 x_return_status => l_return_status
4791 ,p_inventory_item_id => l_orig_rsv_rec.inventory_item_id
4792 ,p_organization_id => l_orig_rsv_rec.organization_id
4793 ,x_index => l_item_cache_index
4794 );
4795 --
4796 If l_return_status = fnd_api.g_ret_sts_error Then
4797 RAISE fnd_api.g_exc_error;
4798 End If;
4799 --
4800 If l_return_status = fnd_api.g_ret_sts_unexp_error Then
4801 RAISE fnd_api.g_exc_unexpected_error;
4802 End If;
4803 --
4804 --if item isn't in cache, need to add it
4805 If l_item_cache_index IS NULL Then
4806 OPEN c_item(l_orig_rsv_rec.inventory_item_id);
4807 FETCH c_item into l_item_rec;
4808 CLOSE c_item;
4809
4810 inv_reservation_util_pvt.add_item_cache
4811 (
4812 x_return_status => l_return_status
4813 ,p_item_record => l_item_rec
4814 ,x_index => l_item_cache_index
4815 );
4816 --
4817 if l_return_status = fnd_api.g_ret_sts_error then
4818 RAISE fnd_api.g_exc_error;
4819 end if;
4820 --
4821 if l_return_status = fnd_api.g_ret_sts_unexp_error then
4822 RAISE fnd_api.g_exc_unexpected_error;
4823 end if;
4824 End If;
4825
4826 -- if revision controlled and revision in reservation record is null, return errors
4827 IF (inv_reservation_global.g_item_record_cache(l_orig_rsv_rec.inventory_item_id).revision_qty_control_code =
4828 inv_reservation_global.g_revision_control_yes AND l_orig_rsv_rec.revision is null) THEN
4829 IF (l_debug = 1) THEN
4830 debug_print('Serial reservation needs to be detailed, revision is null');
4831 END IF;
4832
4833 fnd_message.set_name('INV', 'INV_RSV_SR_DETAIL');
4834 fnd_msg_pub.ADD;
4835 END IF;
4836
4837 IF (l_debug = 1) THEN
4838 debug_print('After revision check');
4839 END IF;
4840
4841 -- if lot controlled and lot number is null, return errors
4842 IF (inv_reservation_global.g_item_record_cache(l_orig_rsv_rec.inventory_item_id).lot_control_code =
4843 inv_reservation_global.g_lot_control_yes AND l_orig_rsv_rec.lot_number is null) THEN
4844 IF (l_debug = 1) THEN
4845 debug_print('Serial reservation needs to be detailed, lot number is null');
4846 END IF;
4847
4848 fnd_message.set_name('INV', 'INV_RSV_SR_DETAIL');
4849 fnd_msg_pub.ADD;
4850 END IF;
4851
4852 IF (l_debug = 1) THEN
4853 debug_print('After lot check');
4854 END IF;
4855
4856 -- if subinventory is null, return errors
4857 IF (l_orig_rsv_rec.subinventory_code is null) THEN
4858 IF (l_debug = 1) THEN
4859 debug_print('Serial reservation needs to be detailed, subinventory is null');
4860 END IF;
4861
4862 fnd_message.set_name('INV', 'INV_RSV_SR_DETAIL');
4863 fnd_msg_pub.ADD;
4864 ELSE
4865 -- if subinventory is locator controlled and locator is null,
4866 --returns error
4867
4868 IF (l_debug = 1) THEN
4869 debug_print('Before sub cache search');
4870 END IF;
4871 inv_reservation_util_pvt.search_sub_cache
4872 (
4873 x_return_status => l_return_status
4874 , p_subinventory_code => l_orig_rsv_rec.subinventory_code
4875 , p_organization_id => l_orig_rsv_rec.organization_id
4876 , x_index => l_sub_cache_index
4877 );
4878 IF (l_debug = 1) THEN
4879 debug_print('After sub cache search');
4880 END IF;
4881
4882
4883 IF l_sub_cache_index IS NULL THEN
4884
4885 -- Modified to call common API
4886 l_sub_rec.secondary_inventory_name := l_orig_rsv_rec.subinventory_code;
4887 l_org_rec.organization_id := l_orig_rsv_rec.organization_id;
4888 IF INV_Validate.subinventory
4889 (
4890 p_sub => l_sub_rec,
4891 p_org => l_org_rec
4892 )=INV_Validate.F THEN
4893 fnd_message.set_name('INV','INVALID_SUB');
4894 fnd_msg_pub.add;
4895 RAISE fnd_api.g_exc_error;
4896 END IF;
4897
4898 --
4899 inv_reservation_util_pvt.add_sub_cache
4900 (
4901 x_return_status => l_return_status
4902 , p_sub_record => l_sub_rec
4903 , x_index => l_sub_cache_index
4904 );
4905 --
4906 IF l_return_status = fnd_api.g_ret_sts_error THEN
4907 RAISE fnd_api.g_exc_error;
4908 END IF ;
4909 --
4910 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
4911 RAISE fnd_api.g_exc_unexpected_error;
4912 END IF;
4913 END IF;
4914
4915
4916 IF (l_debug = 1) THEN
4917 debug_print('Inside checking the locator controls');
4918 debug_print('l_orig_rsv_rec.organization_id' ||
4919 l_orig_rsv_rec.organization_id);
4920 debug_print('sub index' ||l_sub_cache_index);
4921 debug_print('sub ' || l_orig_rsv_rec.subinventory_code);
4922 debug_print('item id' ||
4923 l_orig_rsv_rec.inventory_item_id);
4924
4925 debug_print('org control' || inv_reservation_global.g_organization_record_cache
4926 (l_orig_rsv_rec.organization_id).stock_locator_control_code);
4927 debug_print('sub control' || inv_reservation_global.g_sub_record_cache
4928 (l_sub_cache_index).locator_type);
4929 debug_print('item control' || inv_reservation_global.g_item_record_cache
4930 (l_orig_rsv_rec.inventory_item_id).location_control_code);
4931 END IF;
4932
4933 l_result_locator_control := inv_reservation_util_pvt.locator_control
4934 ( p_org_control => inv_reservation_global.g_organization_record_cache
4935 (l_orig_rsv_rec.organization_id).stock_locator_control_code
4936 , p_sub_control => inv_reservation_global.g_sub_record_cache
4937 (l_sub_cache_index).locator_type
4938 , p_item_control => inv_reservation_global.g_item_record_cache
4939 (l_orig_rsv_rec.inventory_item_id).location_control_code
4940 );
4941
4942 IF (l_debug = 1) THEN
4943 debug_print('l_result_locator_control' || l_result_locator_control);
4944 END IF;
4945
4946 IF (l_result_locator_control <> 1 AND l_orig_rsv_rec.locator_id is null) THEN
4947 IF (l_debug = 1) THEN
4948 debug_print('Serial reservation needs to be detailed, locator is null');
4949 END IF;
4950
4951 fnd_message.set_name('INV', 'INV_RSV_SR_DETAIL');
4952 fnd_msg_pub.ADD;
4953 END IF;
4954 IF (l_debug = 1) THEN
4955 debug_print('After loc check');
4956 END IF;
4957
4958 END IF;
4959
4960 IF (l_debug = 1) THEN
4961 debug_print('After sub/loc check');
4962 END IF;
4963
4964 IF (l_debug = 1) THEN
4965 debug_print('Before loop');
4966 END IF;
4967 -- Get all information for the serial number
4968 FOR i in 1..p_orig_serial_array.COUNT LOOP
4969
4970 IF (l_debug = 1) THEN
4971 debug_print('index = ' || i);
4972 debug_print('serial number = ' || p_orig_serial_array(i).serial_number);
4973 debug_print('inventory item id = ' || p_orig_serial_array(i).inventory_item_id);
4974 END IF;
4975
4976 BEGIN
4977 SELECT current_status,
4978 reservation_id,
4979 current_organization_id,
4980 revision,
4981 current_subinventory_code,
4982 current_locator_id,
4983 lot_number,
4984 lpn_id
4985 INTO l_current_status,
4986 l_reservation_id,
4987 l_organization_id,
4988 l_revision,
4989 l_subinventory,
4990 l_locator_id,
4991 l_lot_number,
4992 l_lpn_id
4993 FROM mtl_serial_numbers
4994 WHERE serial_number = p_orig_serial_array(i).serial_number
4995 AND inventory_item_id =
4996 p_orig_serial_array(i).inventory_item_id;
4997 EXCEPTION
4998 WHEN no_data_found THEN
4999 IF (l_debug = 1) THEN
5000 debug_print('did not find any records for the passed
5001 information' || SQLERRM);
5002 END IF;
5003 fnd_message.set_name('INV', 'INV_INVALID_SERIAL');
5004 fnd_msg_pub.ADD;
5005 RAISE fnd_api.g_exc_error;
5006 END;
5007 IF (l_debug = 1) THEN
5008 debug_print('current_status = ' || l_current_status);
5009 debug_print('reservation_id = ' || l_reservation_id);
5010 debug_print('organization_id = ' || l_organization_id);
5011 debug_print('revision = ' || l_revision);
5012 debug_print('subinventory = ' || l_subinventory);
5013 debug_print('locator_id = ' || l_locator_id);
5014 debug_print('lot_number = ' || l_lot_number);
5015 debug_print('l_lpn_id = ' || l_lpn_id);
5016 END IF;
5017
5018 -- validate the current status of serial number of original serial number records
5019 -- return errors if the serial number is not in inventory.
5020
5021 -- For relieving serials through the TM, the serials may
5022 -- have been issued out before calling relieve. In such a
5023 -- case, we should not check for status in inventory.
5024 -- IF (not(cmro and relieve) and status <> 3) or
5025 -- if ((cmro and relieve) and status not in (3,4))
5026 -- then error.
5027
5028 IF ((NOT(p_orig_rsv_rec.demand_source_type_id =
5029 inv_reservation_global.g_source_type_wip AND
5030 l_wip_entity_type =
5031 inv_reservation_global.g_wip_source_type_cmro) AND
5032 ( p_rsv_action_name = 'RELIEVE')) AND
5033 (l_current_status <> 3)) OR
5034 (((p_orig_rsv_rec.demand_source_type_id =
5035 inv_reservation_global.g_source_type_wip AND
5036 l_wip_entity_type =
5037 inv_reservation_global.g_wip_source_type_cmro) AND
5038 ( p_rsv_action_name = 'RELIEVE')) AND (l_current_status NOT IN (3,4)))
5039 THEN
5040 IF (l_debug = 1) THEN
5041 debug_print('The serial number is not in inventory for serial reservation');
5042 END IF;
5043
5044 fnd_message.set_name('INV', 'INV_RSV_SR_STS_ERR');
5045 fnd_msg_pub.ADD;
5046
5047 RAISE fnd_api.g_exc_error;
5048 END IF;
5049
5050 -- for the form record, validate the serial controls with the (org, item, rev, lot, sub, loc)
5051 -- controls on the reservation record if we create/delete/relieve reservation
5052 -- for transfer/update reservation, we validate the serial information with the
5053 -- reservation_id on the reservation record only because the serial control has
5054 -- already changed when calling reservation API, we only need to validate the
5055 -- serial controls with to record.
5056
5057 IF (p_rsv_action_name = 'CREATE' OR p_rsv_action_name = 'DELETE' OR p_rsv_action_name = 'RELIEVE') THEN
5058
5059 IF (l_reservation_id <> nvl(l_orig_rsv_rec.reservation_id, l_reservation_id) OR
5060 l_organization_id <> nvl(l_orig_rsv_rec.organization_id, l_organization_id) OR
5061 p_orig_serial_array(i).inventory_item_id <>
5062 nvl(l_orig_rsv_rec.inventory_item_id, p_orig_serial_array(i).inventory_item_id) OR
5063 l_revision <> nvl(l_orig_rsv_rec.revision, l_revision) OR
5064 l_subinventory <> nvl(l_orig_rsv_rec.subinventory_code, l_subinventory) OR
5065 l_locator_id <> nvl(l_orig_rsv_rec.locator_id, l_locator_id) OR
5066 l_lot_number <> nvl(l_orig_rsv_rec.lot_number, l_lot_number) OR
5067 l_lpn_id <> nvl(l_orig_rsv_rec.lpn_id, l_lpn_id)) THEN
5068
5069 IF (l_debug = 1) THEN
5070 debug_print('The serial controls is not same as the reservation controls');
5071 debug_print('inventory item id = ' || l_orig_rsv_rec.inventory_item_id);
5072 debug_print('reservation_id = ' || l_orig_rsv_rec.reservation_id);
5073 debug_print('organization_id = ' || l_orig_rsv_rec.organization_id);
5074 debug_print('revision = ' || l_orig_rsv_rec.revision);
5075 debug_print('subinventory = ' || l_orig_rsv_rec.subinventory_code);
5076 debug_print('locator_id = ' || l_orig_rsv_rec.locator_id);
5077 debug_print('lot_number = ' || l_orig_rsv_rec.lot_number);
5078 END IF;
5079
5080 fnd_message.set_name('INV', 'INV_RSV_SR_NOT_MATCH');
5081 fnd_msg_pub.ADD;
5082
5083 RAISE fnd_api.g_exc_error;
5084 END IF;
5085 ELSE
5086 IF (l_reservation_id <> nvl(l_orig_rsv_rec.reservation_id, l_reservation_id)) THEN
5087
5088 IF (l_debug = 1) THEN
5089 debug_print('reservation_id = ' || l_orig_rsv_rec.reservation_id);
5090 END IF;
5091
5092 fnd_message.set_name('INV', 'INV_RSV_SR_NOT_MATCH');
5093 fnd_msg_pub.ADD;
5094
5095 RAISE fnd_api.g_exc_error;
5096 END IF;
5097 END IF;
5098 END LOOP;
5099
5100 END IF; -- end if p_orig_serial_array is not null
5101
5102 IF (p_to_serial_array.COUNT > 0) THEN
5103 IF (l_debug = 1) THEN
5104 debug_print('Inside to count > 0');
5105 END IF;
5106 -- return error if the p_to_rsv_rec is null
5107 IF (p_to_rsv_rec.organization_id is null OR p_to_rsv_rec.organization_id = fnd_api.g_miss_num) THEN
5108 IF (l_debug = 1) THEN
5109 debug_print('The reservation record is null');
5110 END IF;
5111
5112 fnd_message.set_name('INV', 'INV_RSV_NULL_REC');
5113 fnd_msg_pub.ADD;
5114 RAISE fnd_api.g_exc_error;
5115 END IF;
5116
5117 inv_reservation_util_pvt.search_organization_cache
5118 (
5119 x_return_status => l_return_status
5120 , p_organization_id => p_to_rsv_rec.organization_id
5121 , x_index => l_org_cache_index
5122 );
5123 --
5124 IF l_return_status = fnd_api.g_ret_sts_error THEN
5125 RAISE fnd_api.g_exc_error;
5126 End IF ;
5127 --
5128 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
5129 RAISE fnd_api.g_exc_unexpected_error;
5130 End IF;
5131 --
5132 IF l_org_cache_index IS NULL THEN
5133 l_org_rec.organization_id:= p_to_rsv_rec.organization_id;
5134 IF INV_Validate.Organization(
5135 p_org => l_org_rec
5136 )=INV_Validate.F THEN
5137 fnd_message.set_name('INV', 'INVALID ORGANIZATION');
5138 fnd_msg_pub.add;
5139 RAISE fnd_api.g_exc_error;
5140 END IF;
5141
5142 --
5143 inv_reservation_util_pvt.add_organization_cache
5144 (
5145 x_return_status => l_return_status
5146 , p_organization_record => l_org_rec
5147 , x_index => l_org_cache_index
5148 );
5149 --
5150 IF l_return_status = fnd_api.g_ret_sts_error THEN
5151 RAISE fnd_api.g_exc_error;
5152 End IF ;
5153
5154 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
5155 RAISE fnd_api.g_exc_unexpected_error;
5156 End IF;
5157
5158 END IF;
5159
5160 IF (l_debug = 1) THEN
5161 debug_print('After org check: to record: ');
5162 END IF;
5163
5164 -- validate the supply source for serial reservation of to reservation record
5165 IF (p_to_rsv_rec.supply_source_type_id <> inv_reservation_global.g_source_type_inv) THEN
5166 IF (l_debug = 1) THEN
5167 debug_print('Serial reservation can be created with Inventory supply only');
5168 END IF;
5169
5170 fnd_message.set_name('INV', 'INV_RSV_SR_SUP_ERR');
5171 fnd_msg_pub.ADD;
5172 RAISE fnd_api.g_exc_error;
5173 END IF;
5174
5175 -- validate the demand source for serial reservation of to reservation record
5176 IF (p_to_rsv_rec.demand_source_type_id = inv_reservation_global.g_source_type_wip) THEN
5177 -- get wip entity id from wip_record_cache
5178 inv_reservation_util_pvt.get_wip_cache
5179 (
5180 x_return_status => l_return_status
5181 , p_wip_entity_id => p_to_rsv_rec.demand_source_header_id
5182 );
5183
5184 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
5185 RAISE fnd_api.g_exc_error;
5186 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
5187 RAISE fnd_api.g_exc_unexpected_error;
5188 ELSE
5189 l_wip_entity_type := inv_reservation_global.g_wip_record_cache(p_to_rsv_rec.demand_source_header_id).wip_entity_type;
5190 l_wip_job_type := inv_reservation_global.g_wip_record_cache(p_to_rsv_rec.demand_source_header_id).wip_entity_job;
5191 END IF;
5192 END IF;
5193
5194 IF (l_debug = 1) THEN
5195 debug_print('After wip check: to record: ');
5196 END IF;
5197
5198 IF ((p_to_rsv_rec.demand_source_type_id NOT IN (inv_reservation_global.g_source_type_oe,
5199 inv_reservation_global.g_source_type_inv)) AND
5200 (p_to_rsv_rec. demand_source_type_id <> inv_reservation_global.g_source_type_wip AND
5201 l_wip_entity_type <> inv_reservation_global.g_wip_source_type_cmro)) THEN
5202
5203 IF (l_debug = 1) THEN
5204 debug_print('Serial reservation can be created with Inventory, sales order or CMRO demand only');
5205 END IF;
5206
5207 fnd_message.set_name('INV', 'INV_RSV_SR_DEM_ERR');
5208 fnd_msg_pub.ADD;
5209 RAISE fnd_api.g_exc_error;
5210 END IF;
5211
5212 IF (l_debug = 1) THEN
5213 debug_print('After demand check: to record:');
5214 END IF;
5215
5216 -- convert the missing value in the reservation record to null
5217 inv_reservation_pvt.convert_missing_to_null
5218 (
5219 p_rsv_rec => p_to_rsv_rec
5220 , x_rsv_rec => l_to_rsv_rec
5221 );
5222
5223 IF (l_debug = 1) THEN
5224 debug_print('After convert missing to null');
5225 END IF;
5226
5227 -- get the revision control from item cache, first see if the item cache exists
5228 inv_reservation_util_pvt.search_item_cache
5229 (
5230 x_return_status => l_return_status
5231 ,p_inventory_item_id => l_to_rsv_rec.inventory_item_id
5232 ,p_organization_id => l_to_rsv_rec.organization_id
5233 ,x_index => l_item_cache_index
5234 );
5235 --
5236 If l_return_status = fnd_api.g_ret_sts_error Then
5237 RAISE fnd_api.g_exc_error;
5238 End If;
5239 --
5240 If l_return_status = fnd_api.g_ret_sts_unexp_error Then
5241 RAISE fnd_api.g_exc_unexpected_error;
5242 End If;
5243 --
5244 --if item isn't in cache, need to add it
5245 If l_item_cache_index IS NULL Then
5246 OPEN c_item(l_to_rsv_rec.inventory_item_id);
5247 FETCH c_item into l_item_rec;
5248 CLOSE c_item;
5249
5250 inv_reservation_util_pvt.add_item_cache
5251 (
5252 x_return_status => l_return_status
5253 ,p_item_record => l_item_rec
5254 ,x_index => l_item_cache_index
5255 );
5256 --
5257 if l_return_status = fnd_api.g_ret_sts_error then
5258 RAISE fnd_api.g_exc_error;
5259 end if;
5260 --
5261 if l_return_status = fnd_api.g_ret_sts_unexp_error then
5262 RAISE fnd_api.g_exc_unexpected_error;
5263 end if;
5264 End If;
5265
5266 -- if revision controlled and revision in reservation record is null, return errors
5267 IF (inv_reservation_global.g_item_record_cache(l_to_rsv_rec.inventory_item_id).revision_qty_control_code =
5268 inv_reservation_global.g_revision_control_yes AND l_to_rsv_rec.revision is null) THEN
5269 IF (l_debug = 1) THEN
5270 debug_print('Serial reservation needs to be detailed, revision is null');
5271 END IF;
5272
5273 fnd_message.set_name('INV', 'INV_RSV_SR_DETAIL');
5274 fnd_msg_pub.ADD;
5275 END IF;
5276
5277 -- if lot controlled and lot number is null, return errors
5278 IF (inv_reservation_global.g_item_record_cache(l_to_rsv_rec.inventory_item_id).lot_control_code =
5279 inv_reservation_global.g_lot_control_yes AND l_to_rsv_rec.lot_number is null) THEN
5280 IF (l_debug = 1) THEN
5281 debug_print('Serial reservation needs to be detailed, lot number is null');
5282 END IF;
5283
5284 fnd_message.set_name('INV', 'INV_RSV_SR_DETAIL');
5285 fnd_msg_pub.ADD;
5286 END IF;
5287
5288 -- if subinventory is null, return errors
5289 IF (l_to_rsv_rec.subinventory_code is null) THEN
5290 IF (l_debug = 1) THEN
5291 debug_print('Serial reservation needs to be detailed, subinventory is null');
5292 END IF;
5293
5294 fnd_message.set_name('INV', 'INV_RSV_SR_DETAIL');
5295 fnd_msg_pub.ADD;
5296 ELSE
5297 -- if subinventory is locator controlled and locator is null, returns error
5298 inv_reservation_util_pvt.search_sub_cache
5299 (
5300 x_return_status => l_return_status
5301 , p_subinventory_code => l_to_rsv_rec.subinventory_code
5302 , p_organization_id => l_to_rsv_rec.organization_id
5303 , x_index => l_sub_cache_index
5304 );
5305
5306 IF (l_debug = 1) THEN
5307 debug_print('After sub cache search');
5308 END IF;
5309
5310
5311 IF l_sub_cache_index IS NULL THEN
5312
5313 -- Modified to call common API
5314 l_sub_rec.secondary_inventory_name := l_to_rsv_rec.subinventory_code;
5315 l_org_rec.organization_id := l_to_rsv_rec.organization_id;
5316
5317 IF (l_debug = 1) THEN
5318 debug_print('l_to_rsv_rec.subinventory_code = ' || l_to_rsv_rec.subinventory_code);
5319 debug_print('l_orig_rsv_rec.organization_id = ' || l_to_rsv_rec.organization_id);
5320 END IF;
5321
5322 IF INV_Validate.subinventory
5323 (
5324 p_sub => l_sub_rec,
5325 p_org => l_org_rec
5326 )=INV_Validate.F THEN
5327 fnd_message.set_name('INV','INVALID_SUB');
5328 fnd_msg_pub.add;
5329 RAISE fnd_api.g_exc_error;
5330 END IF;
5331
5332 --
5333 inv_reservation_util_pvt.add_sub_cache
5334 (
5335 x_return_status => l_return_status
5336 , p_sub_record => l_sub_rec
5337 , x_index => l_sub_cache_index
5338 );
5339 --
5340 IF l_return_status = fnd_api.g_ret_sts_error THEN
5341 RAISE fnd_api.g_exc_error;
5342 END IF ;
5343 --
5344 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
5345 RAISE fnd_api.g_exc_unexpected_error;
5346 END IF;
5347 END IF;
5348
5349 IF (l_debug = 1) THEN
5350 debug_print('After sub cache check: to record:');
5351 END IF;
5352
5353 IF (l_debug = 1) THEN
5354 debug_print('Inside checking the locator controls');
5355 debug_print('l_to_rsv_rec.organization_id' ||
5356 l_to_rsv_rec.organization_id);
5357 debug_print('sub index' || l_sub_cache_index);
5358 debug_print('sub ' || l_to_rsv_rec.subinventory_code);
5359 debug_print('item id' ||
5360 l_to_rsv_rec.inventory_item_id);
5361
5362 debug_print('org control' || inv_reservation_global.g_organization_record_cache
5363 (l_to_rsv_rec.organization_id).stock_locator_control_code);
5364 debug_print('sub control' || inv_reservation_global.g_sub_record_cache
5365 (l_sub_cache_index).locator_type);
5366 debug_print('item control' || inv_reservation_global.g_item_record_cache
5367 (l_to_rsv_rec.inventory_item_id).location_control_code);
5368 END IF;
5369
5370 l_result_locator_control := inv_reservation_util_pvt.locator_control
5371 ( p_org_control
5372 => inv_reservation_global.g_organization_record_cache
5373 (l_to_rsv_rec.organization_id).stock_locator_control_code
5374 , p_sub_control
5375 => inv_reservation_global.g_sub_record_cache
5376 (l_sub_cache_index).locator_type
5377 , p_item_control
5378 => inv_reservation_global.g_item_record_cache
5379 (l_to_rsv_rec.inventory_item_id).location_control_code
5380 );
5381 IF (l_result_locator_control <> 1 AND l_to_rsv_rec.locator_id is null) THEN
5382 IF (l_debug = 1) THEN
5383 debug_print('Serial reservation needs to be detailed, locator is null');
5384 END IF;
5385
5386 fnd_message.set_name('INV', 'INV_RSV_SR_DETAIL');
5387 fnd_msg_pub.ADD;
5388 END IF;
5389 END IF;
5390
5391 IF (l_debug = 1) THEN
5392 debug_print('After loc check: to record:');
5393 END IF;
5394 -- Get all information for the serial number
5395 FOR i in 1..p_to_serial_array.COUNT LOOP
5396 SELECT current_status,
5397 reservation_id,
5398 current_organization_id,
5399 revision,
5400 current_subinventory_code,
5401 current_locator_id,
5402 lot_number,
5403 lpn_id
5404 INTO l_current_status,
5405 l_reservation_id,
5406 l_organization_id,
5407 l_revision,
5408 l_subinventory,
5409 l_locator_id,
5410 l_lot_number,
5411 l_lpn_id
5412 FROM mtl_serial_numbers
5413 WHERE serial_number = p_to_serial_array(i).serial_number
5414 AND inventory_item_id = p_to_serial_array(i).inventory_item_id;
5415
5416 IF (l_debug = 1) THEN
5417 debug_print('IInside serial loop. Serial number: ' || p_to_serial_array(i).serial_number);
5418 END IF;
5419
5420 IF (l_debug = 1) THEN
5421 debug_print('index = ' || i);
5422 debug_print('serial number = ' || p_to_serial_array(i).serial_number);
5423 debug_print('inventory item id = ' || p_to_serial_array(i).inventory_item_id);
5424 debug_print('current_status = ' || l_current_status);
5425 debug_print('reservation_id = ' || l_reservation_id);
5426 debug_print('organization_id = ' || l_organization_id);
5427 debug_print('revision = ' || l_revision);
5428 debug_print('subinventory = ' || l_subinventory);
5429 debug_print('locator_id = ' || l_locator_id);
5430 debug_print('lot_number = ' || l_lot_number);
5431 debug_print('lpn_id = ' || l_lpn_id);
5432 END IF;
5433
5434 -- validate the current status of serial number of original serial number records
5435 -- return errors if the serial number is not in inventory.
5436 IF (l_current_status <> 3) THEN
5437 IF (l_debug = 1) THEN
5438 debug_print('The serial number is not in inventory for serial reservation');
5439 END IF;
5440
5441 fnd_message.set_name('INV', 'INV_RSV_SR_STS_ERR');
5442 fnd_msg_pub.ADD;
5443
5444 RAISE fnd_api.g_exc_error;
5445 END IF;
5446
5447 -- validate the serial controls with the (org, item, rev, lot, sub, loc) controls
5448 -- on the reservation record, return errors if they don't match
5449 IF ((l_to_rsv_rec.reservation_id IS NOT NULL AND p_orig_rsv_rec.reservation_id <> fnd_api.g_miss_num AND
5450 l_reservation_id NOT IN (nvl(l_to_rsv_rec.reservation_id, l_reservation_id),
5451 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
5452 p_to_serial_array(i).inventory_item_id <>
5453 nvl(l_to_rsv_rec.inventory_item_id, p_to_serial_array(i).inventory_item_id) OR
5454 l_revision <> nvl(l_to_rsv_rec.revision, l_revision) OR
5455 l_subinventory <> nvl(l_to_rsv_rec.subinventory_code, l_subinventory) OR
5456 l_locator_id <> nvl(l_to_rsv_rec.locator_id, l_locator_id) OR
5457 l_lot_number <> nvl(l_to_rsv_rec.lot_number, l_lot_number) OR
5458 l_lpn_id <> nvl(l_to_rsv_rec.lpn_id, l_lpn_id)) THEN
5459
5460 IF (l_debug = 1) THEN
5461 debug_print('The serial controls is not same as the reservation controls');
5462 debug_print('inventory item id = ' || l_to_rsv_rec.inventory_item_id);
5463 debug_print('orig reservation_id = ' || p_orig_rsv_rec.reservation_id);
5464 debug_print('to reservation_id = ' || l_to_rsv_rec.reservation_id);
5465 debug_print('l_reservation_id = ' || l_reservation_id);
5466 debug_print('organization_id = ' || l_to_rsv_rec.organization_id);
5467 debug_print('revision = ' || l_to_rsv_rec.revision);
5468 debug_print('subinventory = ' || l_to_rsv_rec.subinventory_code);
5469 debug_print('locator_id = ' || l_to_rsv_rec.locator_id);
5470 debug_print('lot_number = ' || l_to_rsv_rec.lot_number);
5471 END IF;
5472
5473 fnd_message.set_name('INV', 'INV_RSV_SR_NOT_MATCH');
5474 fnd_msg_pub.ADD;
5475
5476 RAISE fnd_api.g_exc_error;
5477 END IF;
5478 END LOOP;
5479
5480 END IF; -- end if p_to_serial_array is not null
5481 END IF;
5482
5483 x_return_status := l_return_status;
5484 EXCEPTION
5485 WHEN fnd_api.g_exc_error THEN
5486 x_return_status := fnd_api.g_ret_sts_error;
5487 --
5488 WHEN fnd_api.g_exc_unexpected_error THEN
5489 x_return_status := fnd_api.g_ret_sts_unexp_error ;
5490 --
5491 WHEN OTHERS THEN
5492 x_return_status := fnd_api.g_ret_sts_unexp_error ;
5493 --
5494 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
5495 THEN
5496 fnd_msg_pub.add_exc_msg
5497 ( g_pkg_name
5498 , 'Validate_Serials'
5499 );
5500 END IF;
5501 --
5502 END validate_serials;
5503 /*** End R12 }} ***/
5504
5505 --
5506 -- Procedure
5507 -- validate_input_parameters
5508 -- Description
5509 -- is valid if all of the following are satisfied
5510 -- 1. if p_rsv_action_name is CREATE, or UPDATE, or TRANSFER, or DELETE
5511 -- validate_organization, validate_item, validate_demand_source,
5512 -- validate_supply_source, validate_quantity, validate_sales_order
5513 -- with the p_orig_rsv_rec
5514 -- (the original reservation record) return success
5515 -- 2. if p_rsv_action_name is UPDATE, or TRANSFER
5516 -- validate_organization, validate_item, validate_demand_source,
5517 -- validate_supply_source, validate_quantity with the p_to_rsv_rec
5518 -- (the new reservation record) return success
5519 -- Bug 1937201 - Changed validations so that original_rsv is only
5520 -- validated for the CREATE actions, and that we validate
5521 -- to_rsv for sales order during transfer or update.
5522
5523 PROCEDURE validate_input_parameters
5524 (
5525 x_return_status OUT NOCOPY VARCHAR2
5526 , p_orig_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
5527 , p_to_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
5528 , p_orig_serial_array IN inv_reservation_global.serial_number_tbl_type
5529 , p_to_serial_array IN inv_reservation_global.serial_number_tbl_type
5530 , p_rsv_action_name IN VARCHAR2
5531 , x_orig_item_cache_index OUT NOCOPY INTEGER
5532 , x_orig_org_cache_index OUT NOCOPY INTEGER
5533 , x_orig_demand_cache_index OUT NOCOPY INTEGER
5534 , x_orig_supply_cache_index OUT NOCOPY INTEGER
5535 , x_orig_sub_cache_index OUT NOCOPY INTEGER
5536 , x_to_item_cache_index OUT NOCOPY INTEGER
5537 , x_to_org_cache_index OUT NOCOPY INTEGER
5538 , x_to_demand_cache_index OUT NOCOPY INTEGER
5539 , x_to_supply_cache_index OUT NOCOPY INTEGER
5540 , x_to_sub_cache_index OUT NOCOPY INTEGER
5541 , p_substitute_flag IN BOOLEAN DEFAULT FALSE /* Bug 6044651 */
5542 ) IS
5543 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
5544 l_has_serial_number VARCHAR2(1);
5545 l_orig_item_cache_index INTEGER := NULL;
5546 l_orig_org_cache_index INTEGER := NULL;
5547 l_orig_demand_cache_index INTEGER := NULL;
5548 l_orig_supply_cache_index INTEGER := NULL;
5549 l_orig_sub_cache_index INTEGER := NULL;
5550 l_to_item_cache_index INTEGER := NULL;
5551 l_to_org_cache_index INTEGER := NULL;
5552 l_to_demand_cache_index INTEGER := NULL;
5553 l_to_supply_cache_index INTEGER := NULL;
5554 l_to_sub_cache_index INTEGER := NULL;
5555 l_item_rec inv_reservation_global.item_record;
5556 /*** {{ R12 Enhanced reservations code changes ***/
5557 l_msg_count NUMBER;
5558 l_msg_data VARCHAR2(1000);
5559 l_debug NUMBER;
5560 l_demand_source_header_id NUMBER;
5561 l_demand_source_line_id NUMBER;
5562 l_demand_ship_date DATE;
5563 -- Bug 4608452: Added this to check for existing crossdock reservations
5564 l_wip_entity_type NUMBER;
5565 l_wip_job_type VARCHAR2(15);
5566 /*** End R12 }} ***/
5567 CURSOR c_item IS
5568 SELECT *
5569 FROM mtl_system_items
5570 WHERE inventory_Item_Id = p_orig_rsv_rec.inventory_item_id;
5571 BEGIN
5572 --
5573 -- First validate whether minimum number of arguments are provided for the
5574 -- listed reservation action. If not error out right away.
5575 --
5576
5577 IF (g_debug IS NULL) THEN
5578 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
5579 END IF;
5580
5581 l_debug := g_debug;
5582
5583 IF p_rsv_action_name = 'CREATE' THEN
5584 -- validate item and organization information
5585 validate_organization
5586 (
5587 x_return_status => l_return_status
5588 , p_organization_id => p_orig_rsv_rec.organization_id
5589 , x_org_cache_index => l_orig_org_cache_index
5590 );
5591 --
5592 IF l_return_status = fnd_api.g_ret_sts_error THEN
5593 RAISE fnd_api.g_exc_error;
5594 END IF ;
5595 --
5596 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
5597 RAISE fnd_api.g_exc_unexpected_error;
5598 END IF;
5599 --
5600 validate_item
5601 (
5602 x_return_status => l_return_status
5603 , p_inventory_item_id => p_orig_rsv_rec.inventory_item_id
5604 , p_organization_id => p_orig_rsv_rec.organization_id
5605 , x_item_cache_index => l_orig_item_cache_index
5606 );
5607 --
5608 IF l_return_status = fnd_api.g_ret_sts_error THEN
5609 RAISE fnd_api.g_exc_error;
5610 END IF ;
5611 --
5612 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
5613 RAISE fnd_api.g_exc_unexpected_error;
5614 END IF;
5615 --
5616 validate_demand_source
5617 (
5618 x_return_status => l_return_status
5619 , p_rsv_action_name => p_rsv_action_name
5620 , p_inventory_item_id => p_orig_rsv_rec.inventory_item_id
5621 , p_organization_id => p_orig_rsv_rec.organization_id
5622 , p_demand_source_type_id => p_orig_rsv_rec.demand_source_type_id
5623 , p_demand_source_header_id => p_orig_rsv_rec.demand_source_header_id
5624 , p_demand_source_line_id => p_orig_rsv_rec.demand_source_line_id
5625 , p_demand_source_line_detail => p_orig_rsv_rec.demand_source_line_detail
5626 , p_orig_demand_source_type_id => NULL
5627 , p_orig_demand_source_header_id => NULL
5628 , p_orig_demand_source_line_id => NULL
5629 , p_orig_demand_source_detail => NULL
5630 , p_demand_source_name => p_orig_rsv_rec.demand_source_name
5631 , p_reservation_id => p_orig_rsv_rec.reservation_id /*** {{ R12 Enhanced reservations code changes ***/
5632 , p_reservation_quantity => p_orig_rsv_rec.reservation_quantity
5633 , p_reservation_uom_code => p_orig_rsv_rec.reservation_uom_code
5634 , p_supply_type_id => p_orig_rsv_rec.supply_source_type_id
5635 , p_demand_ship_date => p_orig_rsv_rec.demand_ship_date
5636 , p_supply_receipt_date => p_orig_rsv_rec.supply_receipt_date /*** End R12 }} ***/
5637 , x_demand_cache_index => l_orig_demand_cache_index
5638 , p_substitute_flag => p_substitute_flag /* Bug 6044651 */
5639 );
5640 IF l_return_status = fnd_api.g_ret_sts_error THEN
5641 RAISE fnd_api.g_exc_error;
5642 END IF ;
5643 --
5644 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
5645 RAISE fnd_api.g_exc_unexpected_error;
5646 END IF;
5647 --
5648
5649 -- Bug: 4661026: Passing the requirement date if the demand ship date
5650 -- is null
5651 IF (p_orig_rsv_rec.supply_source_type_id = inv_reservation_global.g_source_type_wip)
5652 THEN
5653 l_demand_ship_date := Nvl(p_orig_rsv_rec.demand_ship_date,p_orig_rsv_rec.requirement_date);
5654
5655 END IF;
5656
5657 validate_supply_source
5658 (
5659 x_return_status => l_return_status
5660 , p_inventory_item_id => p_orig_rsv_rec.inventory_item_id
5661 , p_organization_id => p_orig_rsv_rec.organization_id
5662 , p_supply_source_type_id => p_orig_rsv_rec.supply_source_type_id
5663 , p_supply_source_header_id => p_orig_rsv_rec.supply_source_header_id
5664 , p_supply_source_line_id => p_orig_rsv_rec.supply_source_line_id
5665 , p_supply_source_line_detail => p_orig_rsv_rec.supply_source_line_detail
5666 , p_supply_source_name => p_orig_rsv_rec.supply_source_name
5667 , p_demand_source_type_id => p_orig_rsv_rec.demand_source_type_id
5668 , p_revision => p_orig_rsv_rec.revision
5669 , p_lot_number => p_orig_rsv_rec.lot_number
5670 , p_subinventory_code => p_orig_rsv_rec.subinventory_code
5671 , p_locator_id => p_orig_rsv_rec.locator_id
5672 , p_serial_array => p_orig_serial_array
5673 , p_demand_ship_date => l_demand_ship_date
5674 , p_supply_receipt_date => p_orig_rsv_rec.supply_receipt_date
5675 , p_item_cache_index => l_orig_item_cache_index
5676 , p_org_cache_index => l_orig_org_cache_index
5677 , x_supply_cache_index => l_orig_supply_cache_index
5678 , x_sub_cache_index => l_orig_sub_cache_index
5679 );
5680 IF l_return_status = fnd_api.g_ret_sts_error THEN
5681 RAISE fnd_api.g_exc_error;
5682 END IF ;
5683 --
5684 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
5685 RAISE fnd_api.g_exc_unexpected_error;
5686 END IF;
5687 --
5688 IF p_orig_serial_array.COUNT > 0 THEN
5689 l_has_serial_number := fnd_api.g_true;
5690 ELSE
5691 l_has_serial_number := fnd_api.g_false;
5692 END IF;
5693 --
5694 -- INVCONV BEGIN
5695 -- Extend validations to cover secondary quantity
5696 validate_quantity
5697 (
5698 x_return_status => l_return_status
5699 , p_primary_uom => p_orig_rsv_rec.primary_uom_code
5700 , p_primary_quantity
5701 => p_orig_rsv_rec.primary_reservation_quantity
5702 , p_secondary_uom => p_orig_rsv_rec.secondary_uom_code -- INVCONV
5703 , p_secondary_quantity
5704 => p_orig_rsv_rec.secondary_reservation_quantity -- INVCONV
5705 , p_reservation_uom => p_orig_rsv_rec.reservation_uom_code
5706 , p_reservation_quantity => p_orig_rsv_rec.reservation_quantity
5707 , p_lot_number => p_orig_rsv_rec.lot_number -- INVCONV
5708 , p_has_serial_number => l_has_serial_number
5709 , p_item_cache_index => l_orig_item_cache_index -- INVCONV
5710 );
5711 -- INVCONV END
5712 IF l_return_status = fnd_api.g_ret_sts_error THEN
5713 RAISE fnd_api.g_exc_error;
5714 END IF ;
5715 --
5716
5717 /*** {{ R12 Enhanced reservations code changes ***/
5718 create_crossdock_reservation
5719 (
5720 x_return_status => l_return_status
5721 , x_msg_count => l_msg_count
5722 , x_msg_data => l_msg_data
5723 , p_rsv_rec => p_orig_rsv_rec
5724 );
5725
5726 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
5727 RAISE fnd_api.g_exc_error;
5728 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
5729 RAISE fnd_api.g_exc_unexpected_error;
5730 END IF;
5731
5732 validate_pjm_reservations
5733 (
5734 x_return_status => l_return_status
5735 , p_organization_id => p_orig_rsv_rec.organization_id
5736 , p_inventory_item_id => p_orig_rsv_rec.inventory_item_id
5737 , p_supply_source_type_id => p_orig_rsv_rec.supply_source_type_id
5738 , p_supply_source_header_id => p_orig_rsv_rec.supply_source_header_id
5739 , p_supply_source_line_id => p_orig_rsv_rec.supply_source_line_id
5740 , p_supply_source_line_detail => p_orig_rsv_rec.supply_source_line_detail
5741 , p_project_id => p_orig_rsv_rec.project_id
5742 , p_task_id => p_orig_rsv_rec.task_id
5743 );
5744
5745 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
5746 RAISE fnd_api.g_exc_error;
5747 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
5748 RAISE fnd_api.g_exc_unexpected_error;
5749 END IF;
5750
5751 validate_serials
5752 (
5753 x_return_status => l_return_status
5754 , p_orig_rsv_rec => p_orig_rsv_rec
5755 , p_to_rsv_rec => p_to_rsv_rec
5756 , p_orig_serial_array => p_orig_serial_array
5757 , p_to_serial_array => p_to_serial_array
5758 , p_rsv_action_name => p_rsv_action_name
5759 );
5760
5761 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
5762 RAISE fnd_api.g_exc_error;
5763 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
5764 RAISE fnd_api.g_exc_unexpected_error;
5765 END IF;
5766 /*** End R12 }} ***/
5767
5768 /*** {{ R12 Enhanced reservations code changes
5769 -- call from validate_demand_source
5770 validate_sales_order
5771 (
5772 x_return_status => l_return_status
5773 , p_reservation_id => p_orig_rsv_rec.reservation_id
5774 , p_demand_type_id => p_orig_rsv_rec.demand_source_type_id
5775 , p_demand_header_id => p_orig_rsv_rec.demand_source_header_id
5776 , p_demand_line_id => p_orig_rsv_rec.demand_source_line_id
5777 , p_reservation_quantity => p_orig_rsv_rec.reservation_quantity
5778 , p_reservation_uom_code => p_orig_rsv_rec.reservation_uom_code
5779 , p_reservation_item_id => p_orig_rsv_rec.inventory_item_id
5780 , p_reservation_org_id => p_orig_rsv_rec.organization_id
5781 );
5782 IF l_return_status = fnd_api.g_ret_sts_error THEN
5783 RAISE fnd_api.g_exc_error;
5784 END IF;
5785 --
5786 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
5787 RAISE fnd_api.g_exc_unexpected_error;
5788 END IF;
5789 *** End R12 }} ***/
5790
5791 -- check to see if there are existing crossdock reservations against this
5792 --wip job for a different demand. If so, fail.
5793 IF (p_orig_rsv_rec.supply_source_type_id =
5794 INV_RESERVATION_GLOBAL.G_SOURCE_TYPE_WIP) THEN
5795 IF (l_debug = 1) THEN
5796 debug_print('checked wip');
5797 END IF;
5798 -- Bug 4608452: Get the wip entity type to check for existing reservations
5799 IF (l_debug = 1) THEN
5800 debug_print('Before wip job validation');
5801 END IF;
5802
5803 /*** Get the wip entity type ***/
5804 -- get wip entity id from wip_record_cache
5805 inv_reservation_util_pvt.get_wip_cache
5806 (
5807 x_return_status => l_return_status
5808 , p_wip_entity_id => p_orig_rsv_rec.supply_source_header_id
5809 );
5810
5811 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
5812 RAISE fnd_api.g_exc_error;
5813 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
5814 RAISE fnd_api.g_exc_unexpected_error;
5815 ELSE
5816 l_wip_entity_type := inv_reservation_global.g_wip_record_cache(p_orig_rsv_rec.supply_source_header_id).wip_entity_type;
5817 l_wip_job_type := inv_reservation_global.g_wip_record_cache(p_orig_rsv_rec.supply_source_header_id).wip_entity_job;
5818 END IF;
5819
5820 /* 9695544- commented the following. Now we allow multiple demand lines for same WIP LPN
5821 IF (l_wip_entity_type = inv_reservation_global.g_wip_source_type_discrete) THEN
5822 IF (l_debug = 1) THEN
5823 debug_print('inside wip');
5824 END IF;
5825 BEGIN
5826 SELECT distinct
5827 inv_salesorder.get_salesorder_for_oeheader(wdd.source_header_id),
5828 wdd.source_line_id INTO l_demand_source_header_id, l_demand_source_line_id
5829 FROM mtl_txn_request_lines mtrl, wms_license_plate_numbers wlpn,
5830 wsh_delivery_details wdd
5831 WHERE mtrl.organization_id = p_orig_rsv_rec.organization_id
5832 AND mtrl.inventory_item_id = p_orig_rsv_rec.inventory_item_id
5833 AND mtrl.line_status <> 5 -- not closed move order lines
5834 AND NVL(mtrl.quantity_delivered, 0) = 0
5835 AND mtrl.txn_source_id = p_orig_rsv_rec.supply_source_header_id
5836 AND mtrl.lpn_id = wlpn.lpn_id
5837 AND wlpn.lpn_context = 2 -- WIP LPN
5838 AND mtrl.crossdock_type = 1 -- Crossdocked to OE demand
5839 AND mtrl.backorder_delivery_detail_id IS NOT NULL
5840 AND mtrl.backorder_delivery_detail_id =
5841 wdd.delivery_detail_id;
5842 EXCEPTION
5843 WHEN no_data_found THEN
5844 IF (l_debug = 1) THEN
5845 debug_print('No records found for this WIP job that has been crossdocked');
5846 END IF;
5847 END;
5848
5849 IF (l_demand_source_header_id <>
5850 p_orig_rsv_rec.demand_source_header_id) OR
5851 (l_demand_source_line_id <>
5852 p_orig_rsv_rec.demand_source_line_id) THEN
5853 IF (l_debug = 1) THEN
5854 debug_print('Job already has a crossdocked reservation for a different demand');
5855 fnd_message.set_name('INV', 'INV_INVALID_DEMAND_SOURCE');
5856 fnd_msg_pub.add;
5857 RAISE fnd_api.g_exc_error;
5858 END IF;
5859 END IF;
5860 END IF; */
5861 END IF;
5862 --
5863 ELSE -- if we don't do validation, still need to populate item cache
5864 --the item cache info is used to created the quantity tree
5865 inv_reservation_util_pvt.search_item_cache
5866 (
5867 x_return_status => l_return_status
5868 ,p_inventory_item_id => p_orig_rsv_rec.inventory_item_id
5869 ,p_organization_id => p_orig_rsv_rec.organization_id
5870 ,x_index => l_orig_item_cache_index
5871 );
5872 --
5873 If l_return_status = fnd_api.g_ret_sts_error Then
5874 RAISE fnd_api.g_exc_error;
5875 End If;
5876 --
5877 If l_return_status = fnd_api.g_ret_sts_unexp_error Then
5878 RAISE fnd_api.g_exc_unexpected_error;
5879 End If;
5880 --
5881 --if item isn't in cache, need to add it
5882 If l_orig_item_cache_index IS NULL Then
5883 OPEN c_item;
5884 FETCH c_item into l_item_rec;
5885 CLOSE c_item;
5886
5887 inv_reservation_util_pvt.add_item_cache
5888 (
5889 x_return_status => l_return_status
5890 ,p_item_record => l_item_rec
5891 ,x_index => l_orig_item_cache_index
5892 );
5893 --
5894 if l_return_status = fnd_api.g_ret_sts_error then
5895 RAISE fnd_api.g_exc_error;
5896 end if;
5897 --
5898 if l_return_status = fnd_api.g_ret_sts_unexp_error then
5899 RAISE fnd_api.g_exc_unexpected_error;
5900 end if;
5901 End If;
5902 END IF;
5903 --
5904 IF p_rsv_action_name IN ('UPDATE', 'TRANSFER') THEN
5905 -- validate item and organization information
5906 validate_organization
5907 (
5908 x_return_status => l_return_status
5909 , p_organization_id => p_to_rsv_rec.organization_id
5910 , x_org_cache_index => l_to_org_cache_index
5911 );
5912 --
5913 IF l_return_status = fnd_api.g_ret_sts_error THEN
5914 RAISE fnd_api.g_exc_error;
5915 END IF ;
5916 --
5917 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
5918 RAISE fnd_api.g_exc_unexpected_error;
5919 END IF;
5920 --
5921 validate_item
5922 (
5923 x_return_status => l_return_status
5924 , p_inventory_item_id => p_to_rsv_rec.inventory_item_id
5925 , p_organization_id => p_to_rsv_rec.organization_id
5926 , x_item_cache_index => l_to_item_cache_index
5927 );
5928 --
5929 IF l_return_status = fnd_api.g_ret_sts_error THEN
5930 RAISE fnd_api.g_exc_error;
5931 END IF ;
5932 --
5933 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
5934 RAISE fnd_api.g_exc_unexpected_error;
5935 END IF;
5936 --
5937 validate_demand_source
5938 (
5939 x_return_status => l_return_status
5940 , p_rsv_action_name => p_rsv_action_name
5941 , p_inventory_item_id => p_to_rsv_rec.inventory_item_id
5942 , p_organization_id => p_to_rsv_rec.organization_id
5943 , p_demand_source_type_id => p_to_rsv_rec.demand_source_type_id
5944 , p_demand_source_header_id => p_to_rsv_rec.demand_source_header_id
5945 , p_demand_source_line_id => p_to_rsv_rec.demand_source_line_id
5946 , p_demand_source_line_detail =>
5947 p_to_rsv_rec.demand_source_line_detail
5948 , p_orig_demand_source_type_id => p_orig_rsv_rec.demand_source_type_id
5949 , p_orig_demand_source_header_id => p_orig_rsv_rec.demand_source_header_id
5950 , p_orig_demand_source_line_id => p_orig_rsv_rec.demand_source_line_id
5951 , p_orig_demand_source_detail => p_orig_rsv_rec.demand_source_line_detail
5952 , p_demand_source_name => p_to_rsv_rec.demand_source_name
5953 , p_reservation_id => p_to_rsv_rec.reservation_id /*** {{ R12 Enhanced reservations code changes ***/
5954 , p_reservation_quantity => p_to_rsv_rec.reservation_quantity
5955 , p_reservation_uom_code => p_to_rsv_rec.reservation_uom_code
5956 , p_supply_type_id => p_to_rsv_rec.supply_source_type_id
5957 , p_demand_ship_date => p_orig_rsv_rec.demand_ship_date
5958 , p_supply_receipt_date => p_orig_rsv_rec.supply_receipt_date /*** End R12 }} ***/
5959 , x_demand_cache_index => l_to_demand_cache_index
5960 );
5961 IF l_return_status = fnd_api.g_ret_sts_error THEN
5962 RAISE fnd_api.g_exc_error;
5963 END IF ;
5964 --
5965 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
5966 RAISE fnd_api.g_exc_unexpected_error;
5967 END IF;
5968 --
5969
5970 -- Bug: 4661026: Passing the requirement date if the demand ship date
5971 -- is null
5972 IF (p_to_rsv_rec.supply_source_type_id = inv_reservation_global.g_source_type_wip)
5973 THEN
5974 l_demand_ship_date := Nvl(p_to_rsv_rec.demand_ship_date,p_to_rsv_rec.requirement_date);
5975
5976 END IF;
5977
5978 validate_supply_source
5979 (
5980 x_return_status => l_return_status
5981 , p_inventory_item_id => p_to_rsv_rec.inventory_item_id
5982 , p_organization_id => p_to_rsv_rec.organization_id
5983 , p_supply_source_type_id => p_to_rsv_rec.supply_source_type_id
5984 , p_supply_source_header_id => p_to_rsv_rec.supply_source_header_id
5985 , p_supply_source_line_id => p_to_rsv_rec.supply_source_line_id
5986 , p_supply_source_line_detail => p_to_rsv_rec.supply_source_line_detail
5987 , p_supply_source_name => p_to_rsv_rec.supply_source_name
5988 , p_demand_source_type_id => p_to_rsv_rec.demand_source_type_id
5989 , p_revision => p_to_rsv_rec.revision
5990 , p_lot_number => p_to_rsv_rec.lot_number
5991 , p_subinventory_code => p_to_rsv_rec.subinventory_code
5992 , p_locator_id => p_to_rsv_rec.locator_id
5993 , p_serial_array => p_to_serial_array
5994 , p_demand_ship_date => l_demand_ship_date
5995 , p_supply_receipt_date => p_to_rsv_rec.supply_receipt_date
5996 , p_item_cache_index => l_to_item_cache_index
5997 , p_org_cache_index => l_to_org_cache_index
5998 , x_supply_cache_index => l_to_supply_cache_index
5999 , x_sub_cache_index => l_to_sub_cache_index
6000 );
6001
6002 IF (l_debug = 1) THEN
6003 debug_print(' After calling validate supply source ' || l_return_status);
6004 END IF;
6005
6006 IF l_return_status = fnd_api.g_ret_sts_error THEN
6007 RAISE fnd_api.g_exc_error;
6008 END IF ;
6009 --
6010 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
6011 RAISE fnd_api.g_exc_unexpected_error;
6012 END IF;
6013 --
6014 IF p_to_serial_array.COUNT > 0 THEN
6015 l_has_serial_number := fnd_api.g_true;
6016 ELSE
6017 l_has_serial_number := fnd_api.g_false;
6018 END IF;
6019 --
6020 -- INVCONV BEGIN
6021 -- Extend validations to cover secondary quantity
6022 validate_quantity
6023 (
6024 x_return_status => l_return_status
6025 , p_primary_uom => p_to_rsv_rec.primary_uom_code
6026 , p_primary_quantity => p_to_rsv_rec.primary_reservation_quantity
6027 , p_secondary_uom => p_to_rsv_rec.secondary_uom_code -- INVCONV
6028 , p_secondary_quantity => p_to_rsv_rec.secondary_reservation_quantity -- INVCONV
6029 , p_reservation_uom => p_to_rsv_rec.reservation_uom_code
6030 , p_reservation_quantity => p_to_rsv_rec.reservation_quantity
6031 , p_lot_number => p_to_rsv_rec.lot_number -- INVCONV
6032 , p_has_serial_number => l_has_serial_number
6033 , p_item_cache_index => l_orig_item_cache_index -- INVCONV
6034 );
6035 -- INVCONV END
6036
6037 IF (l_debug = 1) THEN
6038 debug_print(' After calling validate quantity ' || l_return_status);
6039 END IF;
6040
6041 IF l_return_status = fnd_api.g_ret_sts_error THEN
6042 RAISE fnd_api.g_exc_error;
6043 END IF ;
6044 --
6045 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
6046 RAISE fnd_api.g_exc_unexpected_error;
6047 END IF;
6048
6049 IF p_orig_rsv_rec.inventory_item_id
6050 <> p_to_rsv_rec.inventory_item_id THEN
6051 fnd_message.set_name('INV', 'INVENTORY_ITEM_ID_NOT_THE_SAME');
6052 fnd_msg_pub.add;
6053 RAISE fnd_api.g_exc_error;
6054 END IF;
6055
6056 /*** {{ R12 Enhanced reservations code changes ***/
6057 IF (p_rsv_action_name = 'UPDATE') THEN
6058 update_crossdock_reservation
6059 (
6060 x_return_status => l_return_status
6061 , x_msg_count => l_msg_count
6062 , x_msg_data => l_msg_data
6063 , p_orig_rsv_rec => p_orig_rsv_rec
6064 , p_to_rsv_rec => p_to_rsv_rec
6065 );
6066
6067 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
6068 RAISE fnd_api.g_exc_error;
6069 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
6070 RAISE fnd_api.g_exc_unexpected_error;
6071 END IF;
6072
6073 ELSIF (p_rsv_action_name = 'TRANSFER') THEN
6074 transfer_crossdock_reservation
6075 (
6076 x_return_status => l_return_status
6077 , x_msg_count => l_msg_count
6078 , x_msg_data => l_msg_data
6079 , p_orig_rsv_rec => p_orig_rsv_rec
6080 , p_to_rsv_rec => p_to_rsv_rec
6081 );
6082
6083 IF (l_debug = 1) THEN
6084 debug_print(' After calling validate cossdock xfer ' ||
6085 l_return_status);
6086 END IF;
6087 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
6088 RAISE fnd_api.g_exc_error;
6089 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
6090 RAISE fnd_api.g_exc_unexpected_error;
6091 END IF;
6092 END IF;
6093
6094 validate_pjm_reservations
6095 (
6096 x_return_status => l_return_status
6097 , p_organization_id => p_to_rsv_rec.organization_id
6098 , p_inventory_item_id => p_to_rsv_rec.inventory_item_id
6099 , p_supply_source_type_id => p_to_rsv_rec.supply_source_type_id
6100 , p_supply_source_header_id => p_to_rsv_rec.supply_source_header_id
6101 , p_supply_source_line_id => p_to_rsv_rec.supply_source_line_id
6102 , p_supply_source_line_detail => p_to_rsv_rec.supply_source_line_detail
6103 , p_project_id => p_to_rsv_rec.project_id
6104 , p_task_id => p_to_rsv_rec.task_id
6105 );
6106
6107 IF (l_debug = 1) THEN
6108 debug_print(' After calling validate pjm ' || l_return_status);
6109 END IF;
6110
6111 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
6112 RAISE fnd_api.g_exc_error;
6113 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
6114 RAISE fnd_api.g_exc_unexpected_error;
6115 END IF;
6116
6117 validate_serials
6118 (
6119 x_return_status => l_return_status
6120 , p_orig_rsv_rec => p_orig_rsv_rec
6121 , p_to_rsv_rec => p_to_rsv_rec
6122 , p_orig_serial_array => p_orig_serial_array
6123 , p_to_serial_array => p_to_serial_array
6124 , p_rsv_action_name => p_rsv_action_name
6125 );
6126
6127 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
6128 RAISE fnd_api.g_exc_error;
6129 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
6130 RAISE fnd_api.g_exc_unexpected_error;
6131 END IF;
6132 /*** End R12 }} ***/
6133
6134 --
6135 -- Bug 2025212
6136 -- With Change Management and the way Shipping calls the INV APIs,
6137 -- it becomes necessary in certain situations to update quantity
6138 -- information on reservations against orders that are cancelled.
6139 -- These reservations will soon be canceled or transferred, but
6140 -- the quantity update must happen first.
6141 -- To solve this problem, only validate the sales order on
6142 -- update or transfer if the sales order info has changed.
6143 -- This change will also yield performance improvements.
6144
6145 /*** {{ R12 Enhanced reservations code changes
6146 -- comment out the call to validate_sales_order
6147 -- call from validate_demand_source
6148 IF (p_orig_rsv_rec.demand_source_type_id <>
6149 p_to_rsv_rec.demand_source_type_id) OR
6150 (p_orig_rsv_rec.demand_source_header_id <>
6151 p_to_rsv_rec.demand_source_header_id) OR
6152 (p_orig_rsv_rec.demand_source_line_id <>
6153 p_to_rsv_rec.demand_source_line_id)
6154 THEN
6155
6156 validate_sales_order
6157 (
6158 x_return_status => l_return_status
6159 , p_reservation_id => p_to_rsv_rec.reservation_id
6160 , p_demand_type_id => p_to_rsv_rec.demand_source_type_id
6161 , p_demand_header_id => p_to_rsv_rec.demand_source_header_id
6162 , p_demand_line_id => p_to_rsv_rec.demand_source_line_id
6163 , p_reservation_quantity => p_to_rsv_rec.reservation_quantity
6164 , p_reservation_uom_code => p_to_rsv_rec.reservation_uom_code
6165 , p_reservation_item_id => p_to_rsv_rec.inventory_item_id
6166 , p_reservation_org_id => p_to_rsv_rec.organization_id
6167 );
6168 IF l_return_status = fnd_api.g_ret_sts_error THEN
6169 RAISE fnd_api.g_exc_error;
6170 END IF;
6171 --
6172 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
6173 RAISE fnd_api.g_exc_unexpected_error;
6174 END IF;
6175 --
6176 END IF;
6177 *** End R12 }} ***/
6178 IF (l_debug = 1) THEN
6179 debug_print(' Before checking for existing xdock ' ||
6180 l_return_status);
6181 END IF;
6182
6183 -- check to see if there are existing crossdock reservations against this
6184 --wip job for a different demand. If so, fail.
6185 IF (p_to_rsv_rec.supply_source_type_id = INV_RESERVATION_GLOBAL.G_SOURCE_TYPE_WIP) THEN
6186 -- Bug 4608452: Get the wip entity type to check for existing reservations
6187 /*** Get the wip entity type ***/
6188 -- get wip entity id from wip_record_cache
6189 inv_reservation_util_pvt.get_wip_cache
6190 (
6191 x_return_status => l_return_status
6192 , p_wip_entity_id => p_to_rsv_rec.supply_source_header_id
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 ELSE
6200 l_wip_entity_type := inv_reservation_global.g_wip_record_cache(p_to_rsv_rec.supply_source_header_id).wip_entity_type;
6201 l_wip_job_type := inv_reservation_global.g_wip_record_cache(p_to_rsv_rec.supply_source_header_id).wip_entity_job;
6202 END IF;
6203
6204 /* 9695544-commented following since we dont need this restriction any more
6205 IF (l_wip_entity_type = inv_reservation_global.g_wip_source_type_discrete) THEN
6206 BEGIN
6207 SELECT distinct
6208 inv_salesorder.get_salesorder_for_oeheader(wdd.source_header_id),
6209 wdd.source_line_id INTO l_demand_source_header_id, l_demand_source_line_id
6210 FROM mtl_txn_request_lines mtrl, wms_license_plate_numbers wlpn,
6211 wsh_delivery_details wdd
6212 WHERE mtrl.organization_id = p_to_rsv_rec.organization_id
6213 AND mtrl.inventory_item_id = p_to_rsv_rec.inventory_item_id
6214 AND mtrl.line_status <> 5 -- not closed move order lines
6215 AND NVL(mtrl.quantity_delivered, 0) = 0
6216 AND mtrl.txn_source_id = p_to_rsv_rec.supply_source_header_id
6217 AND mtrl.lpn_id = wlpn.lpn_id
6218 AND wlpn.lpn_context = 2 -- WIP LPN
6219 AND mtrl.crossdock_type = 1 -- Crossdocked to OE demand
6220 AND mtrl.backorder_delivery_detail_id IS NOT NULL
6221 AND mtrl.backorder_delivery_detail_id =
6222 wdd.delivery_detail_id;
6223 EXCEPTION
6224 WHEN no_data_found THEN
6225 IF (l_debug = 1) THEN
6226 debug_print('No records found for this WIP job that has been crossdocked');
6227 END IF;
6228 END;
6229
6230 IF (l_demand_source_header_id <>
6231 p_to_rsv_rec.demand_source_header_id) OR
6232 (l_demand_source_line_id <>
6233 p_to_rsv_rec.demand_source_line_id) THEN
6234 IF (l_debug = 1) THEN
6235 debug_print('Job already has a crossdocked reservation for a different demand');
6236 debug_print(' Reservations exist for sales order header'
6237 || l_demand_source_header_id);
6238 debug_print(' Reservations exist for sales order line'
6239 || l_demand_source_line_id);
6240 fnd_message.set_name('INV', 'INV_INVALID_DEMAND_SOURCE');
6241 fnd_msg_pub.add;
6242 RAISE fnd_api.g_exc_error;
6243 END IF;
6244 END IF;
6245 END IF;*/
6246 END IF;
6247 IF (l_debug = 1) THEN
6248 debug_print(' end of update/ transfer ' || l_return_status);
6249 END IF;
6250 END IF;
6251 --
6252
6253 /*** {{ R12 Enhanced reservations code changes ***/
6254 IF (p_rsv_action_name = 'RELIEVE') THEN
6255 IF (l_debug = 1) THEN
6256 debug_print('Inside validate when relieve');
6257 END IF;
6258
6259 validate_organization
6260 (
6261 x_return_status => l_return_status
6262 , p_organization_id => p_orig_rsv_rec.organization_id
6263 , x_org_cache_index => l_orig_org_cache_index
6264 );
6265 --
6266 IF l_return_status = fnd_api.g_ret_sts_error THEN
6267 RAISE fnd_api.g_exc_error;
6268 END IF ;
6269 --
6270 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
6271 RAISE fnd_api.g_exc_unexpected_error;
6272 END IF;
6273
6274 validate_serials
6275 (
6276 x_return_status => l_return_status
6277 , p_orig_rsv_rec => p_orig_rsv_rec
6278 , p_to_rsv_rec => p_to_rsv_rec
6279 , p_orig_serial_array => p_orig_serial_array
6280 , p_to_serial_array => p_to_serial_array
6281 , p_rsv_action_name => p_rsv_action_name
6282 );
6283
6284 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
6285 RAISE fnd_api.g_exc_error;
6286 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
6287 RAISE fnd_api.g_exc_unexpected_error;
6288 END IF;
6289
6290 /*** for relieve crossdock reservation, call
6291 --- update_crossdock_reservation instead of relieve
6292 --- with to reservation record populated
6293 relieve_crossdock_reservation
6294 (
6295 x_return_status => l_return_status
6296 , x_msg_count => l_msg_count
6297 , x_msg_data => l_msg_data
6298 , p_rsv_rec => p_orig_rsv_rec
6299 );
6300 ****/
6301
6302 update_crossdock_reservation
6303 (
6304 x_return_status => l_return_status
6305 , x_msg_count => l_msg_count
6306 , x_msg_data => l_msg_data
6307 , p_orig_rsv_rec => p_orig_rsv_rec
6308 , p_to_rsv_rec => p_to_rsv_rec
6309 );
6310
6311 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
6312 RAISE fnd_api.g_exc_error;
6313 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
6314 RAISE fnd_api.g_exc_unexpected_error;
6315 END IF;
6316 END IF;
6317 /*** End R12 }} ***/
6318
6319 /*** {{ R12 Enhanced reservations code changes ***/
6320 IF (p_rsv_action_name = 'DELETE') THEN
6321
6322 validate_organization
6323 (
6324 x_return_status => l_return_status
6325 , p_organization_id => p_orig_rsv_rec.organization_id
6326 , x_org_cache_index => l_orig_org_cache_index
6327 );
6328 --
6329 IF l_return_status = fnd_api.g_ret_sts_error THEN
6330 RAISE fnd_api.g_exc_error;
6331 END IF ;
6332 --
6333 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
6334 RAISE fnd_api.g_exc_unexpected_error;
6335 END IF;
6336
6337 validate_serials
6338 (
6339 x_return_status => l_return_status
6340 , p_orig_rsv_rec => p_orig_rsv_rec
6341 , p_to_rsv_rec => p_to_rsv_rec
6342 , p_orig_serial_array => p_orig_serial_array
6343 , p_to_serial_array => p_to_serial_array
6344 , p_rsv_action_name => p_rsv_action_name
6345 );
6346
6347 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
6348 RAISE fnd_api.g_exc_error;
6349 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
6350 RAISE fnd_api.g_exc_unexpected_error;
6351 END IF;
6352
6353 delete_crossdock_reservation
6354 (
6355 x_return_status => l_return_status
6356 , x_msg_count => l_msg_count
6357 , x_msg_data => l_msg_data
6358 , p_rsv_rec => p_orig_rsv_rec
6359 );
6360
6361 IF (l_return_status = fnd_api.g_ret_sts_error) THEN
6362 RAISE fnd_api.g_exc_error;
6363 ELSIF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
6364 RAISE fnd_api.g_exc_unexpected_error;
6365 END IF;
6366 END IF;
6367 /*** End R12 }} ***/
6368 IF (l_debug = 1) THEN
6369 debug_print(' end validate input ' || l_return_status);
6370 END IF;
6371 x_orig_item_cache_index := l_orig_item_cache_index;
6372 x_orig_org_cache_index := l_orig_org_cache_index;
6373 x_orig_demand_cache_index := l_orig_demand_cache_index;
6374 x_orig_supply_cache_index := l_orig_supply_cache_index;
6375 x_orig_sub_cache_index := l_orig_sub_cache_index;
6376 x_to_item_cache_index := l_to_item_cache_index;
6377 x_to_org_cache_index := l_to_org_cache_index;
6378 x_to_demand_cache_index := l_to_demand_cache_index;
6379 x_to_supply_cache_index := l_to_supply_cache_index;
6380 x_to_sub_cache_index := l_to_sub_cache_index;
6381 --
6382 x_return_status := l_return_status;
6383 --
6384 EXCEPTION
6385 WHEN fnd_api.g_exc_error THEN
6386 x_return_status := fnd_api.g_ret_sts_error;
6387 --
6388 WHEN fnd_api.g_exc_unexpected_error THEN
6389 x_return_status := fnd_api.g_ret_sts_unexp_error ;
6390 --
6391 WHEN OTHERS THEN
6392 x_return_status := fnd_api.g_ret_sts_unexp_error ;
6393 --
6394 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
6395 THEN
6396 fnd_msg_pub.add_exc_msg
6397 ( g_pkg_name
6398 , 'Validate_Input_Parameters'
6399 );
6400 END IF;
6401 --
6402 END validate_input_parameters;
6403 END inv_reservation_validate_pvt;