[Home] [Help]
PACKAGE BODY: APPS.WMS_XDOCK_UTILS_PVT
Source
1 PACKAGE BODY wms_xdock_utils_pvt AS
2 /* $Header: WMSXDUTB.pls 120.7 2005/10/06 17:01:42 stdavid noship $ */
3
4 g_pkg_body_ver CONSTANT VARCHAR2(100) := '$Header: WMSXDUTB.pls 120.7 2005/10/06 17:01:42 stdavid noship $';
5 g_newline CONSTANT VARCHAR2(10) := fnd_global.newline;
6 g_conv_precision CONSTANT NUMBER := 5;
7
8
9 PROCEDURE print_debug
10 ( p_msg IN VARCHAR2
11 , p_api_name IN VARCHAR2
12 ) IS
13 BEGIN
14 inv_log_util.trace
15 ( p_message => p_msg
16 , p_module => g_pkg_name || '.' || p_api_name
17 , p_level => 4
18 );
19 END print_debug;
20
21
22
23 PROCEDURE print_version_info
24 IS
25 BEGIN
26 print_debug('Spec:: ' || g_pkg_spec_ver, 'print_version_info');
27 print_debug('Body:: ' || g_pkg_body_ver, 'print_version_info');
28 END print_version_info;
29
30
31
32 FUNCTION is_eligible_source
33 ( p_criterion_id IN NUMBER
34 , p_source_code IN NUMBER
35 , p_source_type IN NUMBER
36 ) RETURN BOOLEAN IS
37
38 CURSOR c_source_exists
39 ( p_crt_id IN NUMBER
40 , p_src_cd IN NUMBER
41 , p_src_tp IN NUMBER
42 ) IS
43 SELECT 'x'
44 FROM wms_xdock_source_assignments wxsa
45 WHERE wxsa.criterion_id = p_crt_id
46 AND wxsa.source_code = p_src_cd
47 AND wxsa.source_type = p_src_tp;
48
49 l_dummy VARCHAR2(1);
50 l_src_found BOOLEAN;
51
52 BEGIN
53 l_src_found := FALSE;
54
55 OPEN c_source_exists (p_criterion_id, p_source_code, p_source_type);
56 FETCH c_source_exists INTO l_dummy;
57 IF c_source_exists%FOUND
58 THEN
59 l_src_found := TRUE;
60 END IF;
61 CLOSE c_source_exists;
62
63 RETURN l_src_found;
64
65 EXCEPTION
66 WHEN OTHERS THEN
67 IF c_source_exists%ISOPEN
68 THEN
69 CLOSE c_source_exists;
70 END IF;
71 RETURN FALSE;
72 END is_eligible_source;
73
74
75
76 FUNCTION is_eligible_supply_source
77 ( p_criterion_id IN NUMBER
78 , p_source_code IN NUMBER
79 ) RETURN BOOLEAN IS
80 BEGIN
81 RETURN is_eligible_source (p_criterion_id, p_source_code, G_SRC_TYPE_SUP);
82 EXCEPTION
83 WHEN OTHERS THEN
84 RETURN FALSE;
85 END is_eligible_supply_source;
86
87
88
89 FUNCTION is_eligible_demand_source
90 ( p_criterion_id IN NUMBER
91 , p_source_code IN NUMBER
92 ) RETURN BOOLEAN IS
93 BEGIN
94 RETURN is_eligible_source (p_criterion_id, p_source_code, G_SRC_TYPE_DEM);
95 EXCEPTION
96 WHEN OTHERS THEN
97 RETURN FALSE;
98 END is_eligible_demand_source;
99
100
101 -- Returns TRUE if RSV record is valid, FALSE otherwise
102 FUNCTION rsv_record_valid
103 ( p_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
104 ) RETURN BOOLEAN IS
105
106 l_api_name VARCHAR2(30);
107 l_debug NUMBER;
108
109 l_dummy VARCHAR2(1);
110
111 CURSOR c_chk_order_stat
112 ( p_line_id IN NUMBER
113 ) IS
114 SELECT 'x'
115 FROM oe_order_lines_all oola
116 WHERE oola.line_id = p_line_id
117 AND oola.booked_flag = 'Y'
118 AND oola.open_flag = 'Y';
119
120 BEGIN
121 l_api_name := 'rsv_record_valid';
122 l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
123
124 -- {{
125 -- BEGIN rsv_record_valid }}
126 --
127 IF l_debug = 1 THEN
128 print_debug
129 ( 'Entered with reservation rec: '
130 || 'reservation ID: ' || to_char(p_rsv_rec.reservation_id)
131 || ', for item ID: ' || to_char(p_rsv_rec.inventory_item_id)
132 || ', xdock flag: ' || p_rsv_rec.crossdock_flag
133 , l_api_name
134 );
135 END IF;
136
137 -- Return if not a crossdock reservation
138 IF NVL(p_rsv_rec.crossdock_flag,'N') <> 'Y'
139 THEN
140 RETURN TRUE;
141 END IF;
142
143 -- Check if delivery detail is stamped
144 IF ( (p_rsv_rec.demand_source_line_detail IS NULL
145 AND
146 NVL(p_rsv_rec.crossdock_flag,'N') = 'Y')
147 OR
148 (p_rsv_rec.demand_source_line_detail IS NOT NULL
149 AND
150 NVL(p_rsv_rec.crossdock_flag,'N') = 'N')
151 )
152 THEN
153 IF l_debug = 1 THEN
154 print_debug
155 ('Mismatch between crossdock_flag and ' ||
156 'and demand_source_line_detail: ' || p_rsv_rec.crossdock_flag ||
157 ', ' || to_char(p_rsv_rec.demand_source_line_detail)
158 , l_api_name);
159 END IF;
160
161 fnd_message.set_name('WMS', 'WMS_RSV_WDD_MISSING');
162 fnd_msg_pub.ADD;
163 RETURN FALSE;
164 END IF;
165
166 -- {{
167 -- Test with sales order / internal order that is not Booked }}
168 --
169 -- Ensure that sales order / internal order is booked
170 IF p_rsv_rec.demand_source_type_id
171 = inv_reservation_global.g_source_type_oe
172 OR
173 p_rsv_rec.demand_source_type_id
174 = inv_reservation_global.g_source_type_internal_ord
175 THEN
176 OPEN c_chk_order_stat (p_rsv_rec.demand_source_line_id);
177 FETCH c_chk_order_stat INTO l_dummy;
178
179 IF c_chk_order_stat%NOTFOUND
180 THEN
181 CLOSE c_chk_order_stat;
182 IF l_debug = 1 THEN
183 print_debug
184 ( 'Order line '
185 || to_char(p_rsv_rec.demand_source_line_id) ||
186 ' is not booked'
187 , l_api_name
188 );
189 END IF;
190
191 fnd_message.set_name('WMS', 'WMS_RSV_ORD_STAT_INVLD');
192 fnd_msg_pub.ADD;
193 RETURN FALSE;
194 END IF;
195
196 IF c_chk_order_stat%ISOPEN
197 THEN
198 CLOSE c_chk_order_stat;
199 END IF;
200 END IF;
201
202 -- {{
203 -- Test with demand source types: internal/sales order
204 -- and supply source types: PO,WIP,Req,ASN,Intransit Shipment,
205 -- Material in Receiving }}
206 --
207 -- Check demand and supply source types
208 IF (p_rsv_rec.demand_source_type_id
209 NOT IN ( inv_reservation_global.g_source_type_oe
210 , inv_reservation_global.g_source_type_internal_ord
211 )
212 OR
213 p_rsv_rec.supply_source_type_id
214 NOT IN ( inv_reservation_global.g_source_type_po
215 , inv_reservation_global.g_source_type_wip
216 , inv_reservation_global.g_source_type_internal_req
217 , inv_reservation_global.g_source_type_asn
218 , inv_reservation_global.g_source_type_intransit
219 , inv_reservation_global.g_source_type_rcv
220 )
221 )
222 THEN
223 IF l_debug = 1 THEN
224 print_debug
225 ( 'Invalid demand source type: '
226 || to_char(p_rsv_rec.demand_source_type_id) ||
227 ' or supply source type: '
228 || to_char(p_rsv_rec.supply_source_type_id)
229 , l_api_name
230 );
231 END IF;
232
233 fnd_message.set_name('WMS', 'WMS_RSV_SRC_INVLD');
234 fnd_msg_pub.ADD;
235 RETURN FALSE;
236 END IF;
237
238 RETURN TRUE;
239
240 -- {{
241 -- END rsv_record_valid }}
242 --
243
244 EXCEPTION
245 WHEN OTHERS THEN
246 IF l_debug = 1 THEN
247 print_debug ('Other error: ' || sqlerrm, l_api_name);
248 END IF;
249
250 RETURN FALSE;
251
252 END rsv_record_valid;
253
254
255
256 PROCEDURE process_delivery_detail
257 ( x_return_status OUT NOCOPY VARCHAR2
258 , p_orig_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
259 , p_new_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
260 , p_action_code IN VARCHAR2
261 ) IS
262
263 l_api_name VARCHAR2(30);
264 l_debug NUMBER;
265 l_msg_count NUMBER;
266 l_msg_data VARCHAR2(2000);
267 l_api_return_status VARCHAR2(1);
268
269 l_delivery_detail_id NUMBER;
270 l_new_wdd_qty NUMBER;
271 l_new_wdd_qty2 NUMBER;
272 l_conv_rate NUMBER;
273 l_index NUMBER;
274 l_new_wdd_id NUMBER;
275
276 record_locked EXCEPTION;
277 PRAGMA EXCEPTION_INIT (record_locked, -54);
278
279 -- Variables for WDD split
280 l_detail_id_tab WSH_UTIL_CORE.id_tab_type;
281 l_action_prms WSH_GLBL_VAR_STRCT_GRP.dd_action_parameters_rec_type;
282 l_action_out_rec WSH_GLBL_VAR_STRCT_GRP.dd_action_out_rec_type;
283
284 -- Variables for WDD update
285 l_detail_info_tab WSH_INTERFACE_EXT_GRP.delivery_details_Attr_tbl_Type;
286 l_in_rec WSH_INTERFACE_EXT_GRP.detailInRecType;
287 l_out_rec WSH_INTERFACE_EXT_GRP.detailOutRecType;
288
289 CURSOR c_lock_wdd
290 ( p_delivery_detail_id IN NUMBER
291 ) IS
292 SELECT wdd.delivery_detail_id
293 , wdd.released_status
294 , wdd.requested_quantity
295 , wdd.requested_quantity_uom
296 , wdd.requested_quantity2
297 , wdd.requested_quantity_uom2
298 FROM wsh_delivery_details wdd
299 WHERE wdd.delivery_detail_id = p_delivery_detail_id
300 FOR UPDATE NOWAIT;
301
302 l_wdd_rec c_lock_wdd%ROWTYPE;
303 l_wdd2_rec c_lock_wdd%ROWTYPE;
304
305 BEGIN
306 x_return_status := fnd_api.g_ret_sts_success;
307 l_api_name := 'process_delivery_detail';
308 l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
309
310 -- {{
311 -- BEGIN process_delivery_detail }}
312 --
313 IF l_debug = 1
314 THEN
315 print_debug
316 ( 'Entered with action code: ' || p_action_code
317 , l_api_name
318 );
319 END IF;
320
321 SAVEPOINT wmsxdutb_proc_wdd_sp;
322
323 l_delivery_detail_id := p_orig_rsv_rec.demand_source_line_detail;
324
325 -- Lock the WDD record
326 BEGIN
327 OPEN c_lock_wdd (l_delivery_detail_id);
328 FETCH c_lock_wdd INTO l_wdd_rec;
329 CLOSE c_lock_wdd;
330 EXCEPTION
331 WHEN record_locked THEN
332 IF (l_debug = 1)
333 THEN
334 print_debug
335 ( 'Unable to lock WDD: ' || to_char(l_delivery_detail_id)
336 , l_api_name
337 );
338 END IF;
339
340 IF c_lock_wdd%ISOPEN
341 THEN
342 CLOSE c_lock_wdd;
343 END IF;
344
345 fnd_message.set_name('WMS', 'WMS_RSV_WDD_LOCK_FAIL');
346 fnd_msg_pub.ADD;
347 RAISE fnd_api.g_exc_error;
348 END;
349
350 IF (l_debug = 1)
351 THEN
352 print_debug
353 ( 'Locked WDD: ' || to_char(l_delivery_detail_id)
354 , l_api_name
355 );
356 END IF;
357
358 -- {{
359 -- Update of Xdock rsv due to reduction in qty of supply:
360 -- Ensure that WDD is split. Verify qty and secondary qty
361 -- for the split line are calculated correctly. Test with
362 -- rsv UOM different from WDD UOM }}
363 --
364 -- For the update case, call shipping API to split the WDD.
365 -- The quantity on the original WDD should be reduced,
366 -- the new WDD should have remaining quantity with status
367 -- 'Ready to Release'
368 --
369 IF p_action_code = 'UPDATE'
370 THEN
371 -- {
372 -- Calculate the WDD quantity to split off
373 l_new_wdd_qty := l_wdd_rec.requested_quantity
374 * (p_orig_rsv_rec.primary_reservation_quantity
375 - p_new_rsv_rec.primary_reservation_quantity)
376 / p_orig_rsv_rec.primary_reservation_quantity;
377
378 IF (l_debug = 1)
379 THEN
380 print_debug
381 ( 'New WDD qty: ' || to_char(l_new_wdd_qty)
382 , l_api_name
383 );
384 END IF;
385
386 -- Convert secondary qty if required
387 IF l_wdd_rec.requested_quantity_uom2 IS NOT NULL
388 THEN
389 l_new_wdd_qty2 := l_wdd_rec.requested_quantity2
390 * (p_orig_rsv_rec.primary_reservation_quantity
391 - p_new_rsv_rec.primary_reservation_quantity)
392 / p_orig_rsv_rec.primary_reservation_quantity;
393 END IF;
394
395 IF (l_debug = 1)
396 THEN
397 print_debug
398 ( 'New WDD secondary qty: ' || to_char(l_new_wdd_qty2)
399 , l_api_name
400 );
401 END IF;
402
403 -- Call WSH API to split the WDD
404 l_detail_id_tab(1) := l_delivery_detail_id;
405 l_action_prms.caller := 'WMS_XDOCK_UTILS_PVT';
406 l_action_prms.action_code := 'SPLIT-LINE';
407 l_action_prms.split_quantity := l_new_wdd_qty;
408 l_action_prms.split_quantity2 := l_new_wdd_qty2;
409
410 l_api_return_status := fnd_api.g_ret_sts_success;
411 WSH_INTERFACE_GRP.Delivery_Detail_Action
412 ( p_api_version_number => 1.0
413 , p_init_msg_list => fnd_api.g_false
414 , p_commit => fnd_api.g_false
415 , x_return_status => l_api_return_status
416 , x_msg_count => l_msg_count
417 , x_msg_data => l_msg_data
418 , p_detail_id_tab => l_detail_id_tab
419 , p_action_prms => l_action_prms
420 , x_action_out_rec => l_action_out_rec
421 );
422
423 IF l_api_return_status <> fnd_api.g_ret_sts_success
424 THEN
425 IF (l_debug = 1)
426 THEN
427 print_debug
428 ( 'Error status from WSH_INTERFACE_GRP.Delivery_Detail_Action: '
429 || l_api_return_status
430 , l_api_name
431 );
432 END IF;
433
434 IF x_return_status = fnd_api.g_ret_sts_error
435 THEN
436 RAISE fnd_api.g_exc_error;
437 ELSE
438 RAISE fnd_api.g_exc_unexpected_error;
439 END IF;
440
441 ELSE
442 IF (l_debug = 1)
443 THEN
444 print_debug('Successfully split the WDD record', l_api_name);
445 END IF;
446 END IF;
447
448 l_index := l_action_out_rec.result_id_tab.FIRST;
449 l_new_wdd_id := l_action_out_rec.result_id_tab(l_index);
450
451 IF (l_debug = 1)
452 THEN
453 print_debug
454 ( 'New delivery detail ID: ' || to_char (l_new_wdd_id)
455 , l_api_name
456 );
457 END IF;
458
459 -- New WDD's released status is to be updated
460 l_delivery_detail_id := l_new_wdd_id;
461
462 -- Lock the new WDD record
463 BEGIN
464 OPEN c_lock_wdd (l_delivery_detail_id);
465 FETCH c_lock_wdd INTO l_wdd2_rec;
466 CLOSE c_lock_wdd;
467 EXCEPTION
468 WHEN record_locked THEN
469 IF (l_debug = 1)
470 THEN
471 print_debug
472 ( 'Unable to lock WDD: ' || to_char(l_delivery_detail_id)
473 , l_api_name
474 );
475 END IF;
476
477 IF c_lock_wdd%ISOPEN
478 THEN
479 CLOSE c_lock_wdd;
480 END IF;
481
482 fnd_message.set_name('WMS', 'WMS_RSV_WDD_LOCK_FAIL');
483 fnd_msg_pub.ADD;
484 RAISE fnd_api.g_exc_error;
485 END;
486
487 IF (l_debug = 1)
488 THEN
489 print_debug
490 ( 'Locked new WDD: ' || to_char(l_delivery_detail_id)
491 , l_api_name
492 );
493 END IF;
494 -- }
495 END IF; -- end IF action is UPDATE
496
497 -- {{
498 -- For update of xdock rsv, ensure that the split WDD is
499 -- set to 'Ready to Release' status. For delete/relieve
500 -- original WDD is set to ready to release. Relieve rsv
501 -- should fail if WDD is shipped or staged }}
502 --
503 -- Update WDD status to Ready to Release if ok to do so
504 IF p_action_code IN ('UPDATE','DELETE')
505 THEN
506 -- {
507 -- Update the released_status to 'R' for the new WDD record
508 l_detail_info_tab(1).delivery_detail_id := l_delivery_detail_id;
509 l_detail_info_tab(1).released_status := 'R';
510 l_detail_info_tab(1).move_order_line_id := NULL;
511
512 l_in_rec.caller := 'WMS_XDOCK_UTILS_PVT';
513 l_in_rec.action_code := 'UPDATE';
514
515 l_api_return_status := fnd_api.g_ret_sts_success;
516 WSH_INTERFACE_EXT_GRP.Create_Update_Delivery_Detail
517 ( p_api_version_number => 1.0
518 , p_init_msg_list => fnd_api.g_false
519 , p_commit => fnd_api.g_false
520 , x_return_status => l_api_return_status
521 , x_msg_count => l_msg_count
522 , x_msg_data => l_msg_data
523 , p_detail_info_tab => l_detail_info_tab
524 , p_in_rec => l_in_rec
525 , x_out_rec => l_out_rec
526 );
527
528 IF l_api_return_status <> fnd_api.g_ret_sts_success
529 THEN
530 IF (l_debug = 1)
531 THEN
532 print_debug
533 ( 'Error status from WSH_INTERFACE_GRP.Create_Update_Delivery_Detail: '
534 || l_api_return_status
535 , l_api_name
536 );
537 END IF;
538
539 IF x_return_status = fnd_api.g_ret_sts_error
540 THEN
541 RAISE fnd_api.g_exc_error;
542 ELSE
543 RAISE fnd_api.g_exc_unexpected_error;
544 END IF;
545
546 ELSE
547 IF (l_debug = 1)
548 THEN
549 print_debug('Successfully updated the WDD record to status ''R''', l_api_name);
550 END IF;
551 END IF;
552 -- }
553 END IF; -- end IF ok to update WDD status to 'R'
554
555 -- {{
556 -- END process_delivery_detail }}
557 --
558
559 EXCEPTION
560 WHEN fnd_api.g_exc_error THEN
561 ROLLBACK TO wmsxdutb_proc_wdd_sp;
562
563 x_return_status := fnd_api.g_ret_sts_error;
564
565 fnd_msg_pub.count_and_get
566 ( p_count => l_msg_count
567 , p_data => l_msg_data
568 , p_encoded => fnd_api.g_false
569 );
570
571 IF l_debug = 1 THEN
572 print_debug (l_msg_data, l_api_name);
573 END IF;
574
575 WHEN OTHERS THEN
576 ROLLBACK TO wmsxdutb_proc_wdd_sp;
577
578 IF l_debug = 1
579 THEN
580 print_debug('Other error: ' || sqlerrm, l_api_name);
581 END IF;
582 x_return_status := fnd_api.g_ret_sts_unexp_error;
583
584 END process_delivery_detail;
585
586
587
588 PROCEDURE create_crossdock_reservation
589 ( x_return_status OUT NOCOPY VARCHAR2
590 , p_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
591 ) IS
592
593 l_api_name VARCHAR2(30);
594 l_debug NUMBER;
595
596 BEGIN
597 x_return_status := fnd_api.g_ret_sts_success;
598 l_api_name := 'create_crossdock_reservation';
599 l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
600
601 print_version_info;
602
603 IF l_debug = 1
604 THEN
605 print_debug
606 ( 'Entered with reservation rec: '
607 || 'reservation ID: ' || to_char(p_rsv_rec.reservation_id)
608 || ', for item ID: ' || to_char(p_rsv_rec.inventory_item_id)
609 , l_api_name
610 );
611 END IF;
612
613 IF (NOT rsv_record_valid(p_rsv_rec))
614 THEN
615 x_return_status := fnd_api.g_ret_sts_error;
616 END IF;
617
618 EXCEPTION
619 WHEN OTHERS THEN
620 IF l_debug = 1
621 THEN
622 print_debug('Other error: ' || sqlerrm, l_api_name);
623 END IF;
624 x_return_status := fnd_api.g_ret_sts_unexp_error;
625
626 END create_crossdock_reservation;
627
628
629
630 PROCEDURE update_crossdock_reservation
631 ( x_return_status OUT NOCOPY VARCHAR2
632 , p_orig_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
633 , p_new_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
634 ) IS
635
636 l_api_name VARCHAR2(30);
637 l_debug NUMBER;
638 l_msg_count NUMBER;
639 l_msg_data VARCHAR2(2000);
640 l_api_return_status VARCHAR2(1);
641
642 BEGIN
643 x_return_status := fnd_api.g_ret_sts_success;
644 l_api_name := 'update_crossdock_reservation';
645 l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
646
647 -- {{
648 -- BEGIN update_crossdock_reservation }}
649 --
650
651 print_version_info;
652
653 IF l_debug = 1
654 THEN
655 print_debug
656 ( 'Entered with parameters: '
657 || 'original rsv ID: ' || to_char(p_orig_rsv_rec.reservation_id)
658 || ', for item ID: ' || to_char(p_orig_rsv_rec.inventory_item_id)
659 || ', xdock flag: ' || p_orig_rsv_rec.crossdock_flag
660 || 'changed rsv ID: ' || to_char(p_new_rsv_rec.reservation_id)
661 || ', for item ID: ' || to_char(p_new_rsv_rec.inventory_item_id)
662 || ', xdock flag: ' || p_new_rsv_rec.crossdock_flag
663 , l_api_name
664 );
665 IF g_demand_triggered
666 THEN
667 print_debug('Demand triggered', l_api_name);
668 ELSE
669 print_debug('NOT demand triggered', l_api_name);
670 END IF;
671 END IF;
672
673 -- Return if not crossdock reservations
674 IF NVL(p_orig_rsv_rec.crossdock_flag,'N') <> 'Y'
675 OR
676 NVL(p_new_rsv_rec.crossdock_flag,'N') <> 'Y'
677 THEN
678 RETURN;
679 END IF;
680
681 SAVEPOINT wmsxdutb_update_sp;
682
683 IF p_new_rsv_rec.primary_reservation_quantity = 0
684 THEN
685 -- {
686 l_api_return_status := fnd_api.g_ret_sts_success;
687 delete_crossdock_reservation
688 ( x_return_status => l_api_return_status
689 , p_rsv_rec => p_orig_rsv_rec
690 );
691
692 IF l_api_return_status <> fnd_api.g_ret_sts_success
693 THEN
694 IF (l_debug = 1)
695 THEN
696 print_debug
697 ( 'Error status from delete_crossdock_reservation: '
698 || l_api_return_status
699 , l_api_name
700 );
701 END IF;
702
703 IF x_return_status = fnd_api.g_ret_sts_error
704 THEN
705 RAISE fnd_api.g_exc_error;
706 ELSE
707 RAISE fnd_api.g_exc_unexpected_error;
708 END IF;
709 ELSE
710 IF (l_debug = 1)
711 THEN
712 print_debug('Successfully processed delete rsv', l_api_name);
713 END IF;
714 END IF;
715 -- }
716 ELSIF (NOT rsv_record_valid(p_new_rsv_rec))
717 THEN
718 IF (l_debug = 1)
719 THEN
720 print_debug('RSV record failed validation', l_api_name);
721 END IF;
722 RAISE fnd_api.g_exc_error;
723 ELSIF (p_new_rsv_rec.primary_reservation_quantity
724 < p_orig_rsv_rec.primary_reservation_quantity)
725 AND
726 NOT g_demand_triggered
727 THEN
728 -- {
729 -- {{
730 -- Ensure that WDD is not split if update of xdock rsv
731 -- is triggered from the demand side }}
732 --
733 IF (l_debug = 1)
734 THEN
735 print_debug('Qty reduced, need to split WDD', l_api_name);
736 END IF;
737
738 l_api_return_status := fnd_api.g_ret_sts_success;
739 process_delivery_detail
740 ( x_return_status => l_api_return_status
741 , p_orig_rsv_rec => p_orig_rsv_rec
742 , p_new_rsv_rec => p_new_rsv_rec
743 , p_action_code => 'UPDATE'
744 );
745
746 IF l_api_return_status <> fnd_api.g_ret_sts_success
747 THEN
748 IF (l_debug = 1)
749 THEN
750 print_debug
751 ( 'Error status from process_delivery_detail: '
752 || l_api_return_status
753 , l_api_name
754 );
755 END IF;
756
757 IF x_return_status = fnd_api.g_ret_sts_error
758 THEN
759 RAISE fnd_api.g_exc_error;
760 ELSE
761 RAISE fnd_api.g_exc_unexpected_error;
762 END IF;
763 ELSE
764 IF (l_debug = 1)
765 THEN
766 print_debug('Successfully processed WDD record', l_api_name);
767 END IF;
768 END IF;
769 -- }
770 END IF; -- end IF rsv qty reduced, and not triggered from demand side
771
772 -- {{
773 -- END update_crossdock_reservation }}
774 --
775
776 EXCEPTION
777 WHEN fnd_api.g_exc_error THEN
778 ROLLBACK TO wmsxdutb_update_sp;
779
780 x_return_status := fnd_api.g_ret_sts_error;
781
782 fnd_msg_pub.count_and_get
783 ( p_count => l_msg_count
784 , p_data => l_msg_data
785 , p_encoded => fnd_api.g_false
786 );
787
788 IF l_debug = 1 THEN
789 print_debug (l_msg_data, l_api_name);
790 END IF;
791
792 WHEN OTHERS THEN
793 ROLLBACK TO wmsxdutb_update_sp;
794
795 IF l_debug = 1 THEN
796 print_debug ('Other error: ' || sqlerrm, l_api_name);
797 END IF;
798 x_return_status := fnd_api.g_ret_sts_unexp_error;
799
800 END update_crossdock_reservation;
801
802
803
804 PROCEDURE transfer_crossdock_reservation
805 ( x_return_status OUT NOCOPY VARCHAR2
806 , p_orig_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
807 , p_new_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
808 ) IS
809
810 l_api_name VARCHAR2(30);
811 l_debug NUMBER;
812
813 BEGIN
814 x_return_status := fnd_api.g_ret_sts_success;
815 l_api_name := 'transfer_crossdock_reservation';
816 l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
817
818 print_version_info;
819
820 IF l_debug = 1 THEN
821 print_debug
822 ( 'Entered with parameters: '
823 || 'original rsv ID: ' || to_char(p_orig_rsv_rec.reservation_id)
824 || ', for item ID: ' || to_char(p_orig_rsv_rec.inventory_item_id)
825 || 'changed rsv ID: ' || to_char(p_new_rsv_rec.reservation_id)
826 || ', for item ID: ' || to_char(p_new_rsv_rec.inventory_item_id)
827 , l_api_name
828 );
829 END IF;
830
831 IF ((NOT rsv_record_valid(p_new_rsv_rec))
832 OR
833 (NOT rsv_record_valid(p_orig_rsv_rec))
834 )
835 THEN
836 x_return_status := fnd_api.g_ret_sts_error;
837 END IF;
838
839 EXCEPTION
840 WHEN OTHERS THEN
841 IF l_debug = 1 THEN
842 print_debug ('Other error: ' || sqlerrm, l_api_name);
843 END IF;
844 x_return_status := fnd_api.g_ret_sts_unexp_error;
845
846 END transfer_crossdock_reservation;
847
848
849
850 PROCEDURE delete_crossdock_reservation
851 ( x_return_status OUT NOCOPY VARCHAR2
852 , p_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
853 ) IS
854
855 l_api_name VARCHAR2(30);
856 l_debug NUMBER;
857 l_msg_count NUMBER;
858 l_msg_data VARCHAR2(2000);
859 l_api_return_status VARCHAR2(1);
860
861 l_dummy_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
862
863 BEGIN
864 x_return_status := fnd_api.g_ret_sts_success;
865 l_api_name := 'delete_crossdock_reservation';
866 l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
867
868 -- {{
869 -- BEGIN delete_crossdock_reservation }}
870 --
871
872 print_version_info;
873
874 IF l_debug = 1 THEN
875 print_debug
876 ( 'Entered with reservation rec: '
877 || 'reservation ID: ' || to_char(p_rsv_rec.reservation_id)
878 || ', for item ID: ' || to_char(p_rsv_rec.inventory_item_id)
879 || ', xdock flag: ' || p_rsv_rec.crossdock_flag
880 , l_api_name
881 );
882 IF g_demand_triggered
883 THEN
884 print_debug('Demand triggered', l_api_name);
885 ELSE
886 print_debug('NOT demand triggered', l_api_name);
887 END IF;
888 END IF;
889
890 -- Return if not a crossdock reservation
891 IF NVL(p_rsv_rec.crossdock_flag,'N') <> 'Y'
892 THEN
893 RETURN;
894 END IF;
895
896 SAVEPOINT wmsxdutb_delete_sp;
897
898 -- {{
899 -- Cancel a xdock rsv (from RSV UI) and ensure that
900 -- WDD is set to Ready to Release status }}
901 --
902
903 IF NOT g_demand_triggered
904 THEN
905 l_api_return_status := fnd_api.g_ret_sts_success;
906 process_delivery_detail
907 ( x_return_status => l_api_return_status
908 , p_orig_rsv_rec => p_rsv_rec
909 , p_new_rsv_rec => l_dummy_rsv_rec
910 , p_action_code => 'DELETE'
911 );
912
913 IF l_api_return_status <> fnd_api.g_ret_sts_success
914 THEN
915 IF (l_debug = 1)
916 THEN
917 print_debug
918 ( 'Error status from process_delivery_detail: '
919 || l_api_return_status
920 , l_api_name
921 );
922 END IF;
923
924 IF x_return_status = fnd_api.g_ret_sts_error
925 THEN
926 RAISE fnd_api.g_exc_error;
927 ELSE
928 RAISE fnd_api.g_exc_unexpected_error;
929 END IF;
930 ELSE
931 IF (l_debug = 1)
932 THEN
933 print_debug('Successfully processed WDD record', l_api_name);
934 END IF;
935 END IF;
936 END IF;
937
938 -- {{
939 -- END delete_crossdock_reservation }}
940 --
941 EXCEPTION
942 WHEN fnd_api.g_exc_error THEN
943 ROLLBACK TO wmsxdutb_delete_sp;
944
945 x_return_status := fnd_api.g_ret_sts_error;
946
947 fnd_msg_pub.count_and_get
948 ( p_count => l_msg_count
949 , p_data => l_msg_data
950 , p_encoded => fnd_api.g_false
951 );
952
953 IF l_debug = 1 THEN
954 print_debug (l_msg_data, l_api_name);
955 END IF;
956
957 WHEN OTHERS THEN
958 ROLLBACK TO wmsxdutb_delete_sp;
959
960 IF l_debug = 1 THEN
961 print_debug ('Other error: ' || sqlerrm, l_api_name);
962 END IF;
963 x_return_status := fnd_api.g_ret_sts_unexp_error;
964
965 END delete_crossdock_reservation;
966
967
968
969 PROCEDURE relieve_crossdock_reservation
970 ( x_return_status OUT NOCOPY VARCHAR2
971 , p_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
972 ) IS
973
974 l_api_name VARCHAR2(30);
975 l_debug NUMBER;
976 l_msg_count NUMBER;
977 l_msg_data VARCHAR2(2000);
978 l_api_return_status VARCHAR2(1);
979
980 l_dummy_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
981
982 BEGIN
983 x_return_status := fnd_api.g_ret_sts_success;
984 l_api_name := 'relieve_crossdock_reservation';
985 l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
986
987 -- {{
988 -- BEGIN relieve_crossdock_reservation }}
989 --
990
991 print_version_info;
992
993 IF l_debug = 1 THEN
994 print_debug
995 ( 'Entered with reservation rec: '
996 || 'reservation ID: ' || to_char(p_rsv_rec.reservation_id)
997 || ', for item ID: ' || to_char(p_rsv_rec.inventory_item_id)
998 || ', xdock flag: ' || p_rsv_rec.crossdock_flag
999 , l_api_name
1000 );
1001 IF g_demand_triggered
1002 THEN
1003 print_debug('Demand triggered', l_api_name);
1004 ELSE
1005 print_debug('NOT demand triggered', l_api_name);
1006 END IF;
1007 END IF;
1008
1009 --
1010 -- Always raise an exception. Reservations should not be
1011 -- calling this API anymore.
1012 --
1013 RAISE fnd_api.g_exc_error;
1014
1015 -- {{
1016 -- END relieve_crossdock_reservation }}
1017 --
1018 EXCEPTION
1019 WHEN fnd_api.g_exc_error THEN
1020 ROLLBACK TO wmsxdutb_relieve_sp;
1021
1022 x_return_status := fnd_api.g_ret_sts_error;
1023
1024 fnd_msg_pub.count_and_get
1025 ( p_count => l_msg_count
1026 , p_data => l_msg_data
1027 , p_encoded => fnd_api.g_false
1028 );
1029
1030 IF l_debug = 1 THEN
1031 print_debug (l_msg_data, l_api_name);
1032 END IF;
1033
1034 WHEN OTHERS THEN
1035 ROLLBACK TO wmsxdutb_relieve_sp;
1036
1037 IF l_debug = 1
1038 THEN
1039 print_debug('Other error: ' || sqlerrm, l_api_name);
1040 END IF;
1041 x_return_status := fnd_api.g_ret_sts_unexp_error;
1042
1043 END relieve_crossdock_reservation;
1044
1045
1046
1047 END wms_xdock_utils_pvt;