[Home] [Help]
PACKAGE BODY: APPS.FTE_MLS_UTIL
Source
1 PACKAGE BODY FTE_MLS_UTIL AS
2 /* $Header: FTEMLUTB.pls 120.6 2006/05/11 17:10:51 nltan ship $ */
3 --{
4
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'FTE_MLS_UTIL';
6
7 --
8 --
9 PROCEDURE api_post_call
10 (
11 p_api_name IN VARCHAR2,
12 p_api_return_status IN VARCHAR2,
13 p_message_name IN VARCHAR2,
14 p_trip_segment_id IN VARCHAR2 DEFAULT NULL,
15 p_trip_segment_name IN VARCHAR2 DEFAULT NULL,
16 p_trip_stop_id IN NUMBER DEFAULT NULL,
17 p_stop_seq_number IN NUMBER DEFAULT NULL,
18 p_trip_id IN VARCHAR2 DEFAULT NULL,
19 p_trip_name IN VARCHAR2 DEFAULT NULL,
20 p_delivery_id IN VARCHAR2 DEFAULT NULL,
21 p_delivery_name IN VARCHAR2 DEFAULT NULL,
22 x_number_of_errors IN OUT NOCOPY NUMBER,
23 x_number_of_warnings IN OUT NOCOPY NUMBER,
24 x_return_status OUT NOCOPY VARCHAR2
25 )
26 IS
27 BEGIN
28 --{
29 IF p_api_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
30 THEN
31 --{
32 IF p_api_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING
33 THEN
34 --{
35 x_number_of_warnings := x_number_of_warnings + 1;
36 --}
37 ELSE
38 --{
39 IF p_api_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
40 THEN
41 --{
42 FND_MESSAGE.SET_NAME('FTE', p_message_name );
43 FND_MESSAGE.SET_TOKEN('PROGRAM_UNIT_NAME', p_api_name);
44 --
45 --
46 IF p_trip_segment_id IS NOT NULL
47 THEN
48 FND_MESSAGE.SET_TOKEN('TRIP_SEGMENT_ID', p_trip_segment_id);
49 END IF;
50 --
51 --
52 IF p_trip_segment_name IS NOT NULL
53 THEN
54 FND_MESSAGE.SET_TOKEN('TRIP_SEGMENT_NAME', p_trip_segment_name);
55 END IF;
56 --
57 --
58 IF p_trip_stop_id IS NOT NULL
59 THEN
60 FND_MESSAGE.SET_TOKEN('STOP_ID', p_trip_stop_id);
61 END IF;
62 --
63 --
64 IF p_stop_seq_number IS NOT NULL
65 THEN
66 FND_MESSAGE.SET_TOKEN('STOP_SEQUENCE_NUMBER', p_stop_seq_number);
67 END IF;
68 --
69 --
70 IF p_trip_id IS NOT NULL
71 THEN
72 FND_MESSAGE.SET_TOKEN('TRIP_ID', p_trip_id);
73 END IF;
74 --
75 --
76 IF p_trip_name IS NOT NULL
77 THEN
78 FND_MESSAGE.SET_TOKEN('TRIP_NAME', p_trip_name);
79 END IF;
80 --
81 --
82 IF p_delivery_id IS NOT NULL
83 THEN
84 FND_MESSAGE.SET_TOKEN('DELIVERY_ID', p_delivery_id);
85 END IF;
86 --
87 --
88 IF p_delivery_name IS NOT NULL
89 THEN
90 FND_MESSAGE.SET_TOKEN('DELIVERY_NAME', p_delivery_name);
91 END IF;
92 --
93 --
94 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR);
95 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
96 RETURN;
97 --}
98 ELSE
99 x_number_of_errors := x_number_of_errors + 1;
100 END IF;
101 --}
102 END IF;
103 --}
104 END IF;
105 --}
106 EXCEPTION
107 WHEN OTHERS THEN
108 wsh_util_core.default_handler('FTE_MLS_UTIL.API_POST_CALL');
109 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
110 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
111 END api_post_call;
112 --
113 --
114 PROCEDURE get_trip_segment_name
115 (
116 p_trip_segment_id IN NUMBER,
117 x_trip_segment_name OUT NOCOPY VARCHAR2,
118 x_return_status OUT NOCOPY VARCHAR2
119 )
120 IS
121 --{
122 l_trip_segment_name VARCHAR2(32767);
123 --
124 --
125 CURSOR get_trip_segment_cur
126 IS
127 SELECT name
128 FROM wsh_trips
129 WHERE trip_id = p_trip_segment_id;
130 --}
131 BEGIN
132 --{
133 l_trip_segment_name := NULL;
134 --
135 FOR get_trip_segment_rec IN get_trip_segment_cur
136 LOOP
137 --{
138 l_trip_segment_name := get_trip_segment_rec.name;
139 --}
140 END LOOP;
141 --
142 --
143 IF l_trip_segment_name IS NULL
144 THEN
145 RAISE NO_DATA_FOUND;
146 END IF;
147 --
148 --
149 x_trip_segment_name := l_trip_segment_name;
150 --
151 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
152 --}
153 EXCEPTION
154 --{
155 WHEN OTHERS THEN
156 wsh_util_core.default_handler('FTE_MLS_UTIL.GET_TRIP_SEGMENT_NAME');
157 FND_MESSAGE.SET_NAME('FTE','FTE_GET_TRIP_SEG_NAME_ERROR');
158 FND_MESSAGE.SET_TOKEN('TRIP_SEGMENT_ID',p_trip_segment_id);
159 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
160 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
161 --}
162 END get_trip_segment_name;
163 --
164 --
165 FUNCTION all_other_segments_closed
166 (
167 P_trip_segment_id IN NUMBER,
168 p_trip_id IN NUMBER
169 )
170 RETURN BOOLEAN
171 IS
172 --{
173 CURSOR check_other_segments_cur
174 IS
175 SELECT 1
176 FROM fte_wsh_trips a, wsh_trips b
177 WHERE a.fte_trip_id = p_trip_id
178 AND a.wsh_trip_id <> p_trip_segment_id
179 AND a.wsh_trip_id = b.trip_id
180 AND b.status_code IN ('OP','IT');
181 --}
182 BEGIN
183 --{
184 FOR check_other_segments_rec IN check_other_segments_cur
185 LOOP
186 --{
187 RETURN(FALSE);
188 --}
189 END LOOP;
190 --
191 --
192 RETURN(TRUE);
193 --}
194 EXCEPTION
195 --{
196 WHEN OTHERS THEN
197 RAISE;
198 --}
199 END all_other_segments_closed;
200 --
201 --
202 --IF x_next_segment_id IS NULL, it implies there is no next segment
203 --
204 --
205
206
207 PROCEDURE get_next_segment_id
208 (
209 P_trip_segment_id IN NUMBER,
210 p_sequence_number IN NUMBER,
211 p_trip_id IN NUMBER,
212 x_trip_name IN OUT NOCOPY VARCHAR2,
213 x_trip_segment_name IN OUT NOCOPY VARCHAR2,
214 x_next_segment_id OUT NOCOPY NUMBER,
215 x_return_status OUT NOCOPY VARCHAR2
216 )
217 IS
218 --{
219 l_return_status VARCHAR2(32767);
220 --
221 --
222 CURSOR get_next_segment_cur
223 IS
224 SELECT wsh_trip_id
225 FROM fte_wsh_trips
226 WHERE fte_trip_id = p_trip_id
227 AND sequence_number = ( select min(sequence_number)
228 from fte_wsh_trips
229 where fte_trip_id = p_trip_id
230 and sequence_number > p_sequence_number );
231 --}
232 BEGIN
233 --{
234 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
235 --
236 --
237 IF x_trip_name IS NULL
238 THEN
239 --{
240 fte_trips_pvt.get_trip_name
241 (
242 p_trip_id => p_trip_id,
243 x_trip_name => x_trip_name,
244 x_return_status => l_return_status
245 );
246 --
247 --
248 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
249 THEN
250 --{
251 x_return_status := l_return_status;
252 --
253 --
254 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
255 THEN
256 --{
257 RETURN;
258 --}
259 END IF;
260 --}
261 END IF;
262 --}
263 END IF;
264 --
265 --
266 IF x_trip_segment_name IS NULL
267 THEN
268 --{
269 get_trip_segment_name
270 (
271 p_trip_segment_id => p_trip_segment_id,
272 x_trip_segment_name => x_trip_segment_name,
273 x_return_status => l_return_status
274 );
275 --
276 --
277 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
278 THEN
279 --{
280 x_return_status := l_return_status;
281 --
282 --
283 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
284 THEN
285 --{
286 RETURN;
287 --}
288 END IF;
289 --}
290 END IF;
291 --}
292 END IF;
293 --
294 --
295 x_next_segment_id := NULL;
296 --
297 FOR get_next_segment_rec IN get_next_segment_cur
298 LOOP
299 --{
300 x_next_segment_id := get_next_segment_rec.wsh_trip_id;
301 --}
302 END LOOP;
303 --}
304 EXCEPTION
305 --{
306 WHEN OTHERS THEN
307 wsh_util_core.default_handler('WSH_MLS_UTIL.GET_NEXT_SEGMENT_ID');
308 FND_MESSAGE.SET_NAME('FTE','FTE_GET_NEXT_SEGMENT_ERROR');
309 FND_MESSAGE.SET_TOKEN('TRIP_NAME',x_trip_name);
310 FND_MESSAGE.SET_TOKEN('TRIP_SEGMENT_NAME',x_trip_segment_name);
311 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
312 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
313 --}
314 END get_next_segment_id;
315 --
316 --
317 --IF x_previous_segment_id IS NULL, it implies there is no previous segment
318 --
319 --
320 PROCEDURE get_previous_segment_id
321 (
322 P_trip_segment_id IN NUMBER,
323 p_sequence_number IN NUMBER,
324 p_trip_id IN NUMBER,
325 x_trip_name IN OUT NOCOPY VARCHAR2,
326 x_trip_segment_name IN OUT NOCOPY VARCHAR2,
327 x_previous_segment_id OUT NOCOPY NUMBER,
328 x_return_status OUT NOCOPY VARCHAR2
329 )
330 IS
331 --{
332 l_return_status VARCHAR2(32767);
333 --
334 --
335 CURSOR get_previous_segment_cur
336 IS
337 SELECT wsh_trip_id
338 FROM fte_wsh_trips
339 WHERE fte_trip_id = p_trip_id
340 AND sequence_number = ( select max(sequence_number)
341 from fte_wsh_trips
342 where fte_trip_id = p_trip_id
343 and sequence_number < p_sequence_number );
344 --}
345 BEGIN
346 --{
347 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
348 --
349 --
350 IF x_trip_name IS NULL
351 THEN
352 --{
353 fte_trips_pvt.get_trip_name
354 (
355 p_trip_id => p_trip_id,
356 x_trip_name => x_trip_name,
357 x_return_status => l_return_status
358 );
359 --
360 --
361 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
362 THEN
363 --{
364 x_return_status := l_return_status;
365 --
366 --
367 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
368 THEN
369 --{
370 RETURN;
371 --}
372 END IF;
373 --}
374 END IF;
375 --}
376 END IF;
377 --
378 --
379 IF x_trip_segment_name IS NULL
380 THEN
381 --{
382 get_trip_segment_name
383 (
384 p_trip_segment_id => p_trip_segment_id,
385 x_trip_segment_name => x_trip_segment_name,
386 x_return_status => l_return_status
387 );
388 --
389 --
390 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
391 THEN
392 --{
393 x_return_status := l_return_status;
394 --
395 --
396 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
397 THEN
398 --{
399 RETURN;
400 --}
401 END IF;
402 --}
403 END IF;
404 --}
405 END IF;
406 --
407 --
408 x_previous_segment_id := NULL;
409 --
410 --
411 FOR get_previous_segment_rec IN get_previous_segment_cur
412 LOOP
413 --{
414 x_previous_segment_id := get_previous_segment_rec.wsh_trip_id;
415 --}
416 END LOOP;
417 --}
418 EXCEPTION
419 --{
420 WHEN OTHERS THEN
421 wsh_util_core.default_handler('FTE_MLS_UTIL.GET_PREVIOUS_SEGMENT_ID');
422 FND_MESSAGE.SET_NAME('FTE','FTE_GET_NEXT_SEGMENT_ERROR');
423 FND_MESSAGE.SET_TOKEN('TRIP_NAME',x_trip_name);
424 FND_MESSAGE.SET_TOKEN('TRIP_SEGMENT_NAME',x_trip_segment_name);
425 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
426 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
427 --}
428 END get_previous_segment_id;
429 --
430 --
431 --IF x_first_stop_location_id IS NULL, it implies there are no stops
432 --
433 --
434 PROCEDURE get_first_stop_location_id
435 (
436 P_trip_segment_id IN NUMBER,
437 x_trip_segment_name IN OUT NOCOPY VARCHAR2,
438 x_first_stop_location_id OUT NOCOPY NUMBER,
439 x_return_status OUT NOCOPY VARCHAR2
440 )
441 IS
442 --{
443 l_return_status VARCHAR2(32767);
444 --
445 --
446 CURSOR get_first_stop_cur
447 (
448 p_segment_id IN NUMBER
449 )
450 IS
451 SELECT stop_id, stop_location_id
452 FROM wsh_trip_stops
453 WHERE trip_id = p_segment_id
454 AND stop_sequence_number = ( select min(stop_sequence_number)
455 from wsh_trip_stops
456 where trip_id = p_segment_id );
457 --}
458 BEGIN
459 --{
460 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
461 --
462 --
463 IF x_trip_segment_name IS NULL
464 THEN
465 --{
466 get_trip_segment_name
467 (
468 p_trip_segment_id => p_trip_segment_id,
469 x_trip_segment_name => x_trip_segment_name,
470 x_return_status => l_return_status
471 );
472 --
473 --
474 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
475 THEN
476 --{
477 x_return_status := l_return_status;
478 --
479 --
480 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
481 THEN
482 --{
483 RETURN;
484 --}
485 END IF;
486 --}
487 END IF;
488 --}
489 END IF;
490 --
491 --
492 x_first_stop_location_id := NULL;
493 --
494 --
495 FOR get_first_stop_rec IN get_first_stop_cur ( p_trip_segment_id )
496 LOOP
497 --{
498 x_first_stop_location_id := get_first_stop_rec.stop_location_id;
499 --}
500 END LOOP;
501 --}
502 EXCEPTION
503 --{
504 WHEN OTHERS THEN
505 wsh_util_core.default_handler('FTE_MLS_UTIL.GET_FIRST_STOP_LOCATION_ID');
506 FND_MESSAGE.SET_NAME('FTE','FTE_GET_FIRST_STOP_ERROR');
507 FND_MESSAGE.SET_TOKEN('TRIP_SEGMENT_NAME',x_trip_segment_name);
508 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
509 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
510 --}
511 END get_first_stop_location_id;
512 --
513 --
514 --IF x_last_stop_location_id IS NULL, it implies there are no stops
515 --
516 --
517 PROCEDURE get_last_stop_location_id
518 (
519 P_trip_segment_id IN NUMBER,
520 x_trip_segment_name IN OUT NOCOPY VARCHAR2,
521 x_last_stop_location_id OUT NOCOPY NUMBER,
522 x_return_status OUT NOCOPY VARCHAR2
523 )
524 IS
525 --{
526 l_return_status VARCHAR2(32767);
527 --
528 --
529 CURSOR get_last_stop_cur
530 (
531 p_segment_id IN NUMBER
532 )
533 IS
534 SELECT stop_id, stop_location_id
535 FROM wsh_trip_stops
536 WHERE trip_id = p_segment_id
537 AND stop_sequence_number = ( select max(stop_sequence_number)
538 from wsh_trip_stops
539 where trip_id = p_segment_id );
540
541 --}
542 BEGIN
543 --{
544 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
545 --
546 --
547 IF x_trip_segment_name IS NULL
548 THEN
549 --{
550 get_trip_segment_name
551 (
552 p_trip_segment_id => p_trip_segment_id,
553 x_trip_segment_name => x_trip_segment_name,
554 x_return_status => l_return_status
555 );
556 --
557 --
558 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
559 THEN
560 --{
561 x_return_status := l_return_status;
562 --
563 --
564 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
565 THEN
566 --{
567 RETURN;
568 --}
569 END IF;
570 --}
571 END IF;
572 --}
573 END IF;
574 --
575 --
576 x_last_stop_location_id := NULL;
577 --
578 FOR get_last_stop_rec IN get_last_stop_cur ( p_trip_Segment_id )
579 LOOP
580 --{
581 x_last_stop_location_id := get_last_stop_rec.stop_location_id;
582 --}
583 END LOOP;
584 --}
585 EXCEPTION
586 --{
587 WHEN OTHERS THEN
588 wsh_util_core.default_handler('FTE_MLS_UTIL.GET_LAST_STOP_LOCATION_ID');
589 FND_MESSAGE.SET_NAME('FTE','FTE_GET_LAST_STOP_ERROR');
590 FND_MESSAGE.SET_TOKEN('TRIP_SEGMENT_NAME',x_trip_segment_name);
591 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
592 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
593 --}
594 END get_last_stop_location_id;
595 --
596 --
597 --
598 -- p_trip_id : FTE Trip ID
599 -- P_trip_segment_id : WSH Trip ID
600 -- p_sequence_number : Sequence of WSH Trip within FTE Trip
601 -- p_last_stop_location_id : Last Stop location for WSH Trip
602 -- x_trip_name : Name of FTE Trip
603 -- x_trip_segment_name : Name of WSH Trip
604 --
605 --
606 --
607 PROCEDURE check_next_segment
608 (
609 p_trip_id IN NUMBER,
610 P_trip_segment_id IN NUMBER,
611 p_sequence_number IN NUMBER,
612 p_last_stop_location_id IN NUMBER DEFAULT NULL,
613 x_trip_name IN OUT NOCOPY VARCHAR2,
614 x_trip_segment_name IN OUT NOCOPY VARCHAR2,
615 x_connected OUT NOCOPY BOOLEAN,
616 x_return_status OUT NOCOPY VARCHAR2
617 )
618 IS
619 --{
620 l_return_status VARCHAR2(32767);
621 l_next_segment_id NUMBER;
622 l_next_segment_name VARCHAR2(32767);
623 l_first_stop_location_id NUMBER;
624 l_last_stop_location_id NUMBER;
625 --}
626 BEGIN
627 --{
628 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
629 --
630 --
631 IF x_trip_name IS NULL
632 THEN
633 --{
634 fte_trips_pvt.get_trip_name
635 (
636 p_trip_id => p_trip_id,
637 x_trip_name => x_trip_name,
638 x_return_status => l_return_status
639 );
640 --
641 --
642 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
643 THEN
644 --{
645 x_return_status := l_return_status;
646 --
647 --
648 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
649 THEN
650 --{
651 RETURN;
652 --}
653 END IF;
654 --}
655 END IF;
656 --}
657 END IF;
658 --
659 --
660 IF x_trip_segment_name IS NULL
661 THEN
662 --{
663 get_trip_segment_name
664 (
665 p_trip_segment_id => p_trip_segment_id,
666 x_trip_segment_name => x_trip_segment_name,
667 x_return_status => l_return_status
668 );
669 --
670 --
671 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
672 THEN
673 --{
674 x_return_status := l_return_status;
675 --
676 --
677 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
678 THEN
679 --{
680 RETURN;
681 --}
682 END IF;
683 --}
684 END IF;
685 --}
686 END IF;
687 --
688 --
689 -- Get Next Segment in the Trip
690 --
691 get_next_segment_id
692 (
693 P_trip_segment_id => p_trip_segment_id ,
694 p_sequence_number => p_sequence_number,
695 p_trip_id => p_trip_id,
696 x_trip_name => x_trip_name,
697 x_trip_segment_name => x_trip_segment_name,
698 x_next_segment_id => l_next_segment_id,
699 x_return_status => l_return_status
700 );
701 --
702 --
703 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
704 THEN
705 --{
706 x_return_status := l_return_status;
707 --
708 --
709 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
710 THEN
711 --{
712 RETURN;
713 --}
714 END IF;
715 --}
716 END IF;
717 --
718 --dbms_output.put_line('l_next_segment_id='||l_next_segment_id);
719 --
720 IF l_next_segment_id IS NOT NULL
721 THEN
722 --{
723 --
724 -- Get First stop of the Next Segment in the Trip
725 --
726 get_first_stop_location_id
727 (
728 P_trip_segment_id => l_next_segment_id,
729 x_trip_segment_name => l_next_segment_name,
730 x_first_stop_location_id => l_first_stop_location_id,
731 x_return_status => l_return_status
732 );
733 --
734 --
735 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
736 THEN
737 --{
738 x_return_status := l_return_status;
739 --
740 --
741 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
742 THEN
743 --{
744 RETURN;
745 --}
746 END IF;
747 --}
748 END IF;
749 --
750 --dbms_output.put_line('l_first_stop_location_id='||l_first_stop_location_id);
751 --
752 IF l_first_stop_location_id IS NULL
753 THEN
754 x_connected := FALSE;
755 RETURN;
756 END IF;
757 --
758 --
759 IF p_last_stop_location_id IS NULL
760 THEN
761 --{
762 -- Get Last stop of the current Segment
763 --
764 get_last_stop_location_id
765 (
766 P_trip_segment_id => p_trip_segment_id,
767 x_trip_segment_name => x_trip_segment_name,
768 x_last_stop_location_id => l_last_stop_location_id,
769 x_return_status => l_return_status
770 );
771 --}
772 ELSE
773 --{
774 l_last_stop_location_id := p_last_stop_location_id;
775 --}
776 END IF;
777 --
778 --
779 IF l_last_stop_location_id IS NULL
780 THEN
781 x_connected := FALSE;
782 RETURN;
783 END IF;
784 --
785 --
786 IF l_first_stop_location_id <> l_last_stop_location_id
787 THEN
788 --{
789 x_connected := FALSE;
790 RETURN;
791 --}
792 END IF;
793 --}
794 END IF;
795 --}
796 EXCEPTION
797 --{
798 WHEN OTHERS THEN
799 wsh_util_core.default_handler('FTE_MLS_UTIL.CHECK_NEXT_SEGMENT');
800 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
801 --}
802 END check_next_segment;
803 --
804 --
805 -- p_trip_id : FTE Trip ID
806 -- P_trip_segment_rec : WSH Trip REcord
807 -- p_sequence_number : Sequence of WSH Trip within FTE Trip
808 -- p_last_stop_location_id : Last Stop location for WSH Trip
809 -- x_trip_name : Name of FTE Trip
810 -- x_trip_segment_name : Name of WSH Trip
811 --
812 --
813 PROCEDURE check_next_segment
814 (
815 p_trip_id IN NUMBER,
816 p_trip_segment_rec IN WSH_TRIPS_PVT.Trip_Rec_Type,
817 p_sequence_number IN NUMBER,
818 p_last_stop_location_id IN NUMBER DEFAULT NULL,
819 x_trip_name IN OUT NOCOPY VARCHAR2,
820 x_connected OUT NOCOPY BOOLEAN,
821 x_return_status OUT NOCOPY VARCHAR2
822 )
823 IS
824 --{
825 l_trip_segment_name VARCHAR2(32767);
826 --}
827 BEGIN
828 --{
829 l_trip_segment_name := p_trip_segment_rec.name;
830 --
831 --
832 check_next_segment
833 (
834 p_trip_id => p_trip_id,
835 P_trip_segment_id => p_trip_segment_rec.trip_id ,
836 p_sequence_number => p_sequence_number,
837 p_last_stop_location_id => p_last_stop_location_id,
838 x_trip_name => x_trip_name,
839 x_trip_segment_name => l_trip_segment_name,
840 x_connected => x_connected,
841 x_return_status => x_return_status
842 );
843 --}
844 EXCEPTION
845 --{
846 WHEN OTHERS THEN
847 wsh_util_core.default_handler('FTE_MLS_UTIL.CHECK_NEXT_SEGMENT');
848 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
849 --}
850 END check_next_segment;
851 --
852 --
853 -- p_trip_id : FTE Trip ID
854 -- P_trip_segment_id : WSH Trip ID
855 -- p_sequence_number : Sequence of WSH Trip within FTE Trip
856 -- p_first_stop_location_id : First Stop location for WSH Trip
857 -- x_trip_name : Name of FTE Trip
858 -- x_trip_segment_name : Name of WSH Trip
859 --
860 --
861 PROCEDURE check_previous_segment
862 (
863 p_trip_id IN NUMBER,
864 P_trip_segment_id IN NUMBER,
865 p_sequence_number IN NUMBER,
866 p_first_stop_location_id IN NUMBER DEFAULT NULL,
867 x_trip_name IN OUT NOCOPY VARCHAR2,
868 x_trip_segment_name IN OUT NOCOPY VARCHAR2,
869 x_connected OUT NOCOPY BOOLEAN,
870 x_return_status OUT NOCOPY VARCHAR2
871 )
872 IS
873 --{
874 l_return_status VARCHAR2(32767);
875 l_previous_segment_id NUMBER;
876 l_prev_segment_name VARCHAR2(32767);
877 l_first_stop_location_id NUMBER;
878 l_last_stop_location_id NUMBER;
879 --}
880 BEGIN
881 --{
882 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
883 --
884 --
885 IF x_trip_name IS NULL
886 THEN
887 --{
888 fte_trips_pvt.get_trip_name
889 (
890 p_trip_id => p_trip_id,
891 x_trip_name => x_trip_name,
892 x_return_status => l_return_status
893 );
894 --
895 --
896 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
897 THEN
898 --{
899 x_return_status := l_return_status;
900 --
901 --
902 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
903 THEN
904 --{
905 RETURN;
906 --}
907 END IF;
908 --}
909 END IF;
910 --}
911 END IF;
912 --
913 --
914 IF x_trip_segment_name IS NULL
915 THEN
916 --{
917 get_trip_segment_name
918 (
919 p_trip_segment_id => p_trip_segment_id,
920 x_trip_segment_name => x_trip_segment_name,
921 x_return_status => l_return_status
922 );
923 --
924 --
925 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
926 THEN
927 --{
928 x_return_status := l_return_status;
929 --
930 --
931 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
932 THEN
933 --{
934 RETURN;
935 --}
936 END IF;
937 --}
938 END IF;
939 --}
940 END IF;
941 --
942 --
943 -- Get previous Segment in the Trip
944 --
945 get_previous_segment_id
946 (
947 P_trip_segment_id => p_trip_segment_id ,
948 p_sequence_number => p_sequence_number,
949 p_trip_id => p_trip_id,
950 x_trip_name => x_trip_name,
951 x_trip_segment_name => x_trip_segment_name,
952 x_previous_segment_id => l_previous_segment_id,
953 x_return_status => l_return_status
954 );
955 --
956 --
957 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
958 THEN
959 --{
960 x_return_status := l_return_status;
961 --
962 --
963 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
964 THEN
965 --{
966 RETURN;
967 --}
968 END IF;
969 --}
970 END IF;
971 --
972 --
973 IF l_previous_segment_id IS NOT NULL
974 THEN
975 --{
976 --
977 -- Get last stop of the previous Segment in the Trip
978 --
979 get_last_stop_location_id
980 (
981 P_trip_segment_id => l_previous_segment_id,
982 x_trip_segment_name => l_prev_segment_name,
983 x_last_stop_location_id => l_last_stop_location_id,
984 x_return_status => l_return_status
985 );
986 --
987 --
988 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
989 THEN
990 --{
991 x_return_status := l_return_status;
992 --
993 --
994 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
995 THEN
996 --{
997 RETURN;
998 --}
999 END IF;
1000 --}
1001 END IF;
1002 --
1003 --
1004 IF l_last_stop_location_id IS NULL
1005 THEN
1006 x_connected := FALSE;
1007 RETURN;
1008 END IF;
1009 --
1010 --
1011 IF p_first_stop_location_id IS NULL
1012 THEN
1013 --{
1014 -- Get first stop of the current Segment
1015 --
1016 get_first_stop_location_id
1017 (
1018 P_trip_segment_id => p_trip_segment_id,
1019 x_trip_segment_name => x_trip_segment_name,
1020 x_first_stop_location_id => l_first_stop_location_id,
1021 x_return_status => l_return_status
1022 );
1023 --}
1024 ELSE
1025 --{
1026 l_first_stop_location_id := p_first_stop_location_id;
1027 --}
1028 END IF;
1029 --
1030 --
1031 IF l_first_stop_location_id IS NULL
1032 THEN
1033 x_connected := FALSE;
1034 RETURN;
1035 END IF;
1036 --
1037 --
1038 IF l_first_stop_location_id <> l_last_stop_location_id
1039 THEN
1040 --{
1041 x_connected := FALSE;
1042 RETURN;
1043 --}
1044 END IF;
1045 --}
1046 END IF;
1047 --}
1048 EXCEPTION
1049 --{
1050 WHEN OTHERS THEN
1051 wsh_util_core.default_handler('FTE_MLS_UTIL.CHECK_PREVIOUS_SEGMENT');
1052 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1053 --}
1054 END check_previous_segment;
1055 --
1056 --
1057 PROCEDURE check_previous_segment
1058 (
1059 p_trip_id IN NUMBER,
1060 p_trip_segment_rec IN WSH_TRIPS_PVT.Trip_Rec_Type,
1061 p_sequence_number IN NUMBER,
1062 p_first_stop_location_id IN NUMBER DEFAULT NULL,
1063 x_trip_name IN OUT NOCOPY VARCHAR2,
1064 x_connected OUT NOCOPY BOOLEAN,
1065 x_return_status OUT NOCOPY VARCHAR2
1066 )
1067 IS
1068 --{
1069 l_trip_segment_name VARCHAR2(32767);
1070 --}
1071 BEGIN
1072 --{
1073 l_trip_segment_name := p_trip_segment_rec.name;
1074 --
1075 --
1076 check_previous_segment
1077 (
1078 p_trip_id => p_trip_id,
1079 P_trip_segment_id => p_trip_segment_rec.trip_id ,
1080 p_sequence_number => p_sequence_number,
1081 p_first_stop_location_id => p_first_stop_location_id,
1082 x_trip_name => x_trip_name,
1083 x_trip_segment_name => l_trip_segment_name,
1084 x_connected => x_connected,
1085 x_return_status => x_return_status
1086 );
1087 --}
1088 EXCEPTION
1089 --{
1090 WHEN OTHERS THEN
1091 wsh_util_core.default_handler('FTE_MLS_UTIL.CHECK_PREVIOUS_SEGMENT');
1092 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1093 --}
1094 END check_previous_segment;
1095 --
1096 --
1097 FUNCTION segment_has_intransit_dlvy
1098 (
1099 P_trip_segment_rec IN WSH_TRIPS_GRP.Trip_Pub_Rec_Type
1100 )
1101 RETURN BOOLEAN
1102 IS
1103 --{
1104 CURSOR intransit_deliveries_cur
1105 IS
1106 SELECT 'x'
1107 FROM wsh_trip_stops WTS,
1108 wsh_delivery_legs WDL,
1109 wsh_new_deliveries WND
1110 WHERE WTS.trip_id = p_trip_segment_rec.trip_id
1111 AND WND.delivery_id = WDL.delivery_id
1112 AND WND.status_code IN ( 'IT', 'CL' )
1113 AND (
1114 WDL.pick_up_stop_id = WTS.STOP_ID
1115 OR WDL.drop_off_stop_id = WTS.STOP_ID
1116 );
1117 --}
1118 BEGIN
1119 --{
1120 FOR intransit_deliveries_rec IN intransit_deliveries_cur
1121 LOOP
1122 --{
1123 RETURN TRUE;
1124 --}
1125 END LOOP;
1126 --
1127 --
1128 RETURN FALSE;
1129 EXCEPTION
1130 --{
1131 WHEN OTHERS THEN
1132 wsh_util_core.default_handler('FTE_MLS_UTIL.SEGMENT_HAS_INTRANSIT_DLVY');
1133 RAISE;
1134 --}
1135 END segment_has_intransit_dlvy;
1136 --
1137 --
1138 FUNCTION segment_has_other_deliveries
1139 (
1140 P_trip_segment_id IN NUMBER,
1141 p_delivery_id IN NUMBER
1142 )
1143 RETURN BOOLEAN
1144 IS
1145 --{
1146 CURSOR deliveries_cur
1147 IS
1148 SELECT 'x'
1149 FROM wsh_trip_stops WTS,
1150 wsh_delivery_legs WDL
1151 WHERE WTS.trip_id = p_trip_segment_id
1152 AND WDL.delivery_id <> p_delivery_id
1153 AND (
1154 WDL.pick_up_stop_id = WTS.STOP_ID
1155 OR WDL.drop_off_stop_id = WTS.STOP_ID
1156 );
1157 --}
1158 BEGIN
1159 --{
1160 FOR deliveries_rec IN deliveries_cur
1161 LOOP
1162 --{
1163 RETURN TRUE;
1164 --}
1165 END LOOP;
1166 --
1167 --
1168 RETURN FALSE;
1169 EXCEPTION
1170 --{
1171 WHEN OTHERS THEN
1172 wsh_util_core.default_handler('FTE_MLS_UTIL.SEGMENT_HAS_OTHER_DELIVERIES');
1173 RAISE;
1174 --}
1175 END segment_has_other_deliveries;
1176 --
1177 --
1178 FUNCTION stop_has_intransit_dlvy
1179 (
1180 P_trip_stop_rec IN WSH_TRIP_STOPS_GRP.Trip_stop_Pub_Rec_Type
1181 )
1182 RETURN BOOLEAN
1183 IS
1184 --{
1185 --}
1186 BEGIN
1187 --{
1188 RETURN
1189 (
1190 stop_has_intransit_dlvy
1191 (
1192 p_trip_stop_id => p_trip_stop_rec.stop_id
1193 )
1194 );
1195 EXCEPTION
1196 --{
1197 WHEN OTHERS THEN
1198 wsh_util_core.default_handler('FTE_MLS_UTIL.STOP_HAS_INTRANSIT_DLVY');
1199 RAISE;
1200 --}
1201 END stop_has_intransit_dlvy;
1202 --
1203 --
1204 FUNCTION stop_has_intransit_dlvy
1205 (
1206 P_trip_stop_id IN NUMBER
1207 )
1208 RETURN BOOLEAN
1209 IS
1210 --{
1211 CURSOR intransit_deliveries_cur
1212 IS
1213 SELECT 'x'
1214 FROM wsh_delivery_legs WDL,
1215 wsh_new_deliveries WND
1216 WHERE WND.delivery_id = WDL.delivery_id
1217 AND WND.status_code IN ( 'IT', 'CL' )
1218 AND (
1219 WDL.pick_up_stop_id = p_trip_stop_id
1220 OR WDL.drop_off_stop_id = p_trip_stop_id
1221 );
1222 --}
1223 BEGIN
1224 --{
1225 FOR intransit_deliveries_rec IN intransit_deliveries_cur
1226 LOOP
1227 --{
1228 RETURN TRUE;
1229 --}
1230 END LOOP;
1231 --
1232 --
1233 RETURN FALSE;
1234 EXCEPTION
1235 --{
1236 WHEN OTHERS THEN
1237 wsh_util_core.default_handler('FTE_MLS_UTIL.STOP_HAS_INTRANSIT_DLVY');
1238 RAISE;
1239 --}
1240 END stop_has_intransit_dlvy;
1241 --
1242 --
1243 PROCEDURE derive_ship_method
1244 (
1245 p_carrier_id IN NUMBER,
1246 p_mode_of_transport IN VARCHAR2,
1247 p_service_level IN VARCHAR2,
1248 p_carrier_name IN VARCHAR2,
1249 p_mode_of_transport_meaning IN VARCHAR2,
1250 p_service_level_meaning IN VARCHAR2,
1251 x_ship_method_code OUT NOCOPY VARCHAR2,
1252 x_return_status OUT NOCOPY VARCHAR2
1253 )
1254 IS
1255 --{
1256 l_return_status VARCHAR2(32767);
1257 --
1258 --
1259 CURSOR get_ship_method_cur
1260 IS
1261 SELECT ship_method_code
1262 FROM wsh_carrier_services
1263 WHERE carrier_id = p_carrier_id
1264 AND enabled_flag = 'Y'
1265 AND (
1266 (
1267 p_mode_of_transport IS NULL
1268 AND mode_of_transport IS NULL
1269 )
1270 OR
1271 (
1272 p_mode_of_transport IS NOT NULL
1273 AND mode_of_transport = p_mode_of_transport
1274 )
1275 )
1276 AND (
1277 (
1278 p_service_level IS NULL
1279 AND service_level IS NULL
1280 )
1281 OR
1282 (
1283 p_service_level IS NOT NULL
1284 AND service_level = p_service_level
1285 )
1286 );
1287 --}
1288 BEGIN
1289 --{
1290 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1291 --
1292 --
1293 x_ship_method_code := NULL;
1294 --
1295 --
1296 IF p_carrier_id IS NULL
1297 AND p_mode_of_transport IS NULL
1298 AND p_service_level IS NULL
1299 THEN
1300 RETURN;
1301 END IF;
1302 --
1303 --
1304 FOR get_ship_method_rec IN get_ship_method_cur
1305 LOOP
1306 --{
1307 x_ship_method_code := get_ship_method_rec.ship_method_code;
1308 --}
1309 END LOOP;
1310 --}
1311 EXCEPTION
1312 --{
1313 WHEN OTHERS THEN
1314 wsh_util_core.default_handler('FTE_MLS_UTIL.DERIVE_SHIP_METHOD_CODE');
1315 FND_MESSAGE.SET_NAME('FTE','FTE_GET_SHIPMETHOD_UNEXP_ERROR');
1316 FND_MESSAGE.SET_TOKEN('CARRIER_NAME',p_carrier_name);
1317 FND_MESSAGE.SET_TOKEN('MODE_OF_TRANSPORT',NVL(p_mode_of_transport_meaning,'NULL'));
1318 FND_MESSAGE.SET_TOKEN('SERVICE_LEVEL',NVL(p_service_level_meaning,'NULL'));
1319 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
1320 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1321 --}
1322 END derive_ship_method;
1323 --
1324 --
1325 --
1326 PROCEDURE get_location_info
1327 (
1328 p_location_id IN NUMBER,
1329 x_location OUT NOCOPY VARCHAR2,
1330 x_csz OUT NOCOPY VARCHAR2,
1331 x_country OUT NOCOPY VARCHAR2,
1332 x_return_status OUT NOCOPY VARCHAR2
1333 )
1334 AS
1335 --{
1336
1337 CURSOR get_location_cur (c_location_id NUMBER)
1338 IS
1339 SELECT ui_location_code, address1,address2,
1340 address3,city,state,country,postal_code
1341 FROM wsh_locations
1342 WHERE wsh_location_id = c_location_id;
1343
1344 --}
1345 BEGIN
1346 x_location := null;
1347 x_csz := null;
1348 x_country := null;
1349 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1350
1351 --{
1352 FOR get_location_rec IN get_location_cur(p_location_id)
1353 LOOP
1354 --{
1355 IF ((get_location_rec.ui_location_code = '')
1356 OR (get_location_rec.ui_location_code IS NULL)) THEN
1357 x_location := nvl(get_location_rec.address1,'') ||
1358 nvl(', ' || get_location_rec.city, '') ||
1359 nvl(', ' || get_location_rec.state, '') ||
1360 nvl(', ' || get_location_rec.postal_code, '') ||
1361 nvl(', ' || get_location_rec.country,'');
1362 ELSE
1363 x_location := get_location_rec.ui_location_code;
1364 END IF;
1365
1366 x_csz := nvl(get_location_rec.city, '') ||
1367 nvl(', ' || get_location_rec.state, '') ||
1368 nvl(', ' || get_location_rec.postal_code, '');
1369
1370 x_country := get_location_rec.country;
1371 --}
1372 END LOOP;
1373 --
1374 --
1375 IF get_location_cur%ISOPEN THEN
1376 CLOSE get_location_cur;
1377 END IF;
1378 --
1379 --
1380 --}
1381 EXCEPTION
1382 --{
1383 WHEN OTHERS THEN
1384 wsh_util_core.default_handler('FTE_MLS_UTIL.GET_LOCATION');
1385 FND_MESSAGE.SET_NAME('FTE','FTE_GET_LOCATION_UNEXP_ERROR');
1386 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
1387 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1388 --}
1389 END get_location_info;
1390 --
1391 --
1392 FUNCTION get_carrier_name
1393 (
1394 p_carrier_id IN NUMBER
1395 )
1396 RETURN VARCHAR2
1397 IS
1398 l_carrier_name VARCHAR(200) := NULL;
1399 --{
1400 CURSOR get_carrier_name_cur(c_carrier_id NUMBER)
1401 IS
1402 SELECT hz.party_name name
1403 FROM hz_parties hz, wsh_carriers wc
1404 WHERE hz.party_id = wc.carrier_id
1405 AND wc.carrier_id = c_carrier_id;
1406 --}
1407 BEGIN
1408 --{
1409 FOR get_carrier_name_rec IN get_carrier_name_cur(p_carrier_id)
1410 LOOP
1411 --{
1412 l_carrier_name := get_carrier_name_rec.name;
1413 --}
1414 END LOOP;
1415 --
1416 IF get_carrier_name_cur%ISOPEN THEN
1417 CLOSE get_carrier_name_cur;
1418 END IF;
1419 --
1420 RETURN l_carrier_name;
1421 EXCEPTION
1422 --{
1423 WHEN OTHERS THEN
1424 wsh_util_core.default_handler('FTE_MLS_UTIL.GET_CARRIER_NAME');
1425 RAISE;
1426 --}
1427 END get_carrier_name;
1428 --
1429 --
1430 FUNCTION get_delivery_legs
1431 (
1432 P_trip_segment_id IN NUMBER
1433 )
1434 RETURN VARCHAR2
1435 IS
1436 --{
1437 x_return VARCHAR2(32767);
1438
1439 CURSOR get_deliveries_cur(c_trip_segment_id NUMBER)
1440 IS
1441 Select delivery_leg_id
1442 from wsh_delivery_legs wdl, wsh_trip_stops wts1, wsh_trip_stops wts2,wsh_trips wt
1443 where wdl.pick_up_stop_id = wts1.stop_id
1444 and wdl.drop_off_stop_id = wts2.stop_id
1445 and wts1.trip_id = wt.trip_id
1446 and wts2.trip_id = wt.trip_id
1447 and wt.trip_id = c_trip_segment_id;
1448 --}
1449 BEGIN
1450 --{
1451 x_return := null;
1452
1453 FOR get_deliveries_rec IN get_deliveries_cur(P_trip_segment_id)
1454 LOOP
1455 --{
1456 x_return := ''' || get_deliveries_rec.delivery_leg_id || '',' || x_return;
1457 --}
1458 END LOOP;
1459 --
1460 IF get_deliveries_cur%ISOPEN THEN
1461 CLOSE get_deliveries_cur;
1462 END IF;
1463 --
1464 RETURN x_return;
1465 --}
1466 EXCEPTION
1467 --{
1468 WHEN OTHERS THEN
1469 RAISE;
1470 --}
1471 END get_delivery_legs;
1472 --
1473 --
1474 FUNCTION get_message
1475 (
1476 p_msg_count IN NUMBER,
1477 p_msg_data IN VARCHAR2
1478 )
1479 RETURN VARCHAR2
1480 IS
1481
1482 l_msg VARCHAR2(32767);
1483 l_msg_string VARCHAR(32767);
1484 --{
1485 BEGIN
1486
1487 IF ( p_msg_count > 1)
1488 THEN
1489
1490 FOR l_count IN 1..NVL(p_msg_count,0)
1491 LOOP
1492 --{
1493 l_msg := substrb(FND_MSG_PUB.GET( p_encoded => FND_API.G_FALSE ),1,240);
1494 l_msg_string := l_msg_string || ' : ' || l_msg;
1495 --}
1496 END LOOP;
1497 ELSE
1498 l_msg_string := p_msg_data;
1499 END IF;
1500
1501 return l_msg_string;
1502 --}
1503 EXCEPTION
1504 --{
1505 WHEN OTHERS THEN
1506 RAISE;
1507 --}
1508 END get_message;
1509 --
1510 --
1511 FUNCTION GET_MODE_OF_TRANSPORT
1512 (
1513 p_mode_code IN VARCHAR2
1514 )
1515 RETURN VARCHAR2
1516 IS
1517
1518 l_mode_meaning VARCHAR(80) := NULL;
1519
1520 CURSOR get_mode_of_transport_cur (c_mode_code VARCHAR2)
1521 IS
1522 SELECT meaning mode_of_transport_meaning
1523 FROM fnd_lookup_values_vl
1524 WHERE lookup_type = 'WSH_MODE_OF_TRANSPORT'
1525 AND lookup_code = c_mode_code;
1526
1527 --{
1528 BEGIN
1529
1530 --GET MODE OF TRANSPORT CODE
1531 --
1532 FOR get_mode_of_transport_rec IN get_mode_of_transport_cur(p_mode_code)
1533 LOOP
1534 --{
1535 l_mode_meaning := get_mode_of_transport_rec.mode_of_transport_meaning;
1536 --}
1537 END LOOP;
1538 --
1539 -- END OF MODE OF TRANSPORT
1540
1541 IF get_mode_of_transport_cur%ISOPEN THEN
1542 CLOSE get_mode_of_transport_cur;
1543 END IF;
1544
1545 return l_mode_meaning;
1546 --}
1547 EXCEPTION
1548 --{
1549 WHEN OTHERS THEN
1550 RAISE;
1551 --}
1552 END GET_MODE_OF_TRANSPORT;
1553 --
1554 --
1555 FUNCTION GET_SERVICE_LEVEL
1556 (
1557 p_service_level IN VARCHAR2
1558 )
1559 RETURN VARCHAR2
1560 IS
1561
1562 l_service_level VARCHAR(80) := NULL;
1563
1564 -- GET Service Level
1565 --
1566 CURSOR get_service_level_cur (c_service_code VARCHAR2)
1567 IS
1568 SELECT meaning service_type_meaning
1569 FROM fnd_lookup_values_vl
1570 WHERE lookup_type = 'WSH_SERVICE_LEVELS'
1571 AND lookup_code = c_service_code;
1572 --
1573
1574 --{
1575 BEGIN
1576
1577 -- GET SERVICE LEVEL
1578 --
1579 FOR get_service_level_rec IN get_service_level_cur(p_service_level)
1580 LOOP
1581 --{
1582 l_service_level := get_service_level_rec.service_type_meaning;
1583 --}
1584 END LOOP;
1585 --
1586 -- END OF SERVICE LEVEL
1587
1588 IF get_service_level_cur%ISOPEN THEN
1589 CLOSE get_service_level_cur;
1590 END IF;
1591
1592 return l_service_level;
1593 --}
1594 EXCEPTION
1595 --{
1596 WHEN OTHERS THEN
1597 RAISE;
1598 --}
1599 END GET_SERVICE_LEVEL;
1600 --
1601 --
1602 --
1603 PROCEDURE get_location_info
1604 (
1605 p_location_id IN NUMBER,
1606 x_location OUT NOCOPY VARCHAR2,
1607 x_return_status OUT NOCOPY VARCHAR2
1608 )
1609 AS
1610 --{
1611
1612 /**
1613 PACK I -WSH_HR_LOCATION_V SHOULD NOT BE USED
1614 USE WSH_LOCATIONS
1615 CURSOR get_location_cur (c_location_id NUMBER)
1616 IS
1617 SELECT address_line_1,address_line_2,
1618 address_line_3,town_or_city,
1619 region_1,region_2,region_3,
1620 country,postal_code
1621 FROM wsh_hr_locations_v
1622 WHERE location_id = c_location_id;
1623 **/
1624 CURSOR get_location_cur (c_location_id NUMBER)
1625 IS
1626 SELECT address1,address2,
1627 address3,city,state,country,postal_code
1628 FROM wsh_locations
1629 WHERE wsh_location_id = c_location_id;
1630
1631
1632 --}
1633 BEGIN
1634 x_location := null;
1635 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1636
1637 --{
1638 FOR get_location_rec IN get_location_cur(p_location_id)
1639 LOOP
1640 --{
1641 x_location := nvl(get_location_rec.address1,'') ||
1642 nvl(', ' || get_location_rec.city, '') ||
1643 nvl(', ' || get_location_rec.state, '') ||
1644 nvl(', ' || get_location_rec.postal_code, '') ||
1645 nvl(', ' || get_location_rec.country,'');
1646 --}
1647 END LOOP;
1648 --
1649 --
1650 IF get_location_cur%ISOPEN THEN
1651 CLOSE get_location_cur;
1652 END IF;
1653 --
1654 --
1655 --}
1656 EXCEPTION
1657 --{
1658 WHEN OTHERS THEN
1659 wsh_util_core.default_handler('FTE_MLS_UTIL.GET_LOCATION');
1660 FND_MESSAGE.SET_NAME('FTE','FTE_GET_LOCATION_UNEXP_ERROR');
1661 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
1662 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1663 --}
1664 END get_location_info;
1665 --
1666
1667
1668 -- get the carrier information
1669 PROCEDURE GET_CARRIER_CONTACT_INFO
1670 (p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1671 p_tender_number IN NUMBER,
1672 x_return_status OUT NOCOPY VARCHAR2,
1673 x_msg_count OUT NOCOPY NUMBER,
1674 x_msg_data OUT NOCOPY VARCHAR2,
1675 x_contact_email OUT NOCOPY VARCHAR2,
1676 x_contact_fax OUT NOCOPY VARCHAR2,
1677 x_contact_phone OUT NOCOPY VARCHAR2,
1678 x_contact_name OUT NOCOPY VARCHAR2)
1679 IS
1680 --{
1681
1682 l_api_name CONSTANT VARCHAR2(30) := 'GET_CARRIER_CONTACT_INFO';
1683 l_api_version CONSTANT NUMBER := 1.0;
1684 l_na_mssg VARCHAR2(200);
1685
1686 --}
1687
1688 l_contact_id NUMBER;
1689
1690 -- cursor to get the contact id from the trip table
1691 CURSOR get_contact_id_cur (c_tender_number NUMBER)
1692 IS
1693 SELECT carrier_contact_id
1694 FROM wsh_trips
1695 WHERE load_tender_number = c_tender_number;
1696 ---
1697 --Cursor to get the contact information
1698 CURSOR get_contact_info_cur (c_contact_id NUMBER)
1699 IS
1700 SELECT party_rel.party_name ContactName,
1701 hcp.email_address ContactEmail,
1702 hcp.contact_point_type ContactPointType
1703 FROM
1704 hz_relationships rel,
1705 hz_party_sites hps,
1706 hz_org_contacts hoc,
1707 hz_contact_points hcp,
1708 hz_parties party_rel
1709 WHERE
1710 hps.party_id = rel.object_id and
1711 hps.party_site_id = hoc.party_site_id and
1712 hoc.party_relationship_id = rel.relationship_id and
1713 party_rel.party_id = rel.subject_id and
1714 hcp.owner_table_id = rel.party_id and
1715 hcp.owner_table_name = 'HZ_PARTIES' and
1716 rel.party_id = c_contact_id;
1717
1718 --{
1719 BEGIN
1720 --
1721 -- Standard Start of API savepoint
1722 SAVEPOINT GET_CARRIER_CONTACT_INFO_PUB;
1723 --
1724 --
1725 -- Initialize message list if p_init_msg_list is set to TRUE.
1726 --
1727 --
1728 IF FND_API.to_Boolean( p_init_msg_list )
1729 THEN
1730 FND_MSG_PUB.initialize;
1731 END IF;
1732 --
1733 --
1734 -- Initialize API return status to success
1735 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1736 x_msg_count := 0;
1737 x_msg_data := 0;
1738
1739 -- get default no contact info message
1740 FND_MESSAGE.SET_NAME('FTE', 'FTE_DELIVERIES_MULTIPLE_NA');
1741 l_na_mssg := FND_MESSAGE.GET;
1742
1743 -- get contact id
1744 FOR get_contact_id_rec IN get_contact_id_cur(p_tender_number)
1745 LOOP
1746 --{
1747 l_contact_id := get_contact_id_rec.carrier_contact_id;
1748 --}
1749 END LOOP;
1750
1751 -- END OF get_contact_id_cur
1752 IF get_contact_id_cur%ISOPEN THEN
1753 CLOSE get_contact_id_cur;
1754 END IF;
1755 --
1756 IF (l_contact_id IS NULL)
1757 THEN
1758 FND_MESSAGE.SET_NAME('FTE','FTE_INVLD_CARRIER_CONTACT');
1759 FND_MESSAGE.SET_TOKEN('TENDER_NUMBER',p_tender_number);
1760 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
1761 RAISE FND_API.G_EXC_ERROR;
1762 END IF;
1763
1764 ---
1765 -- get the contact info
1766 FOR get_contact_info_rec IN get_contact_info_cur(l_contact_id)
1767 LOOP
1768 --{
1769 x_contact_email := get_contact_info_rec.ContactEmail;
1770 x_contact_fax := l_na_mssg;
1771 x_contact_phone := l_na_mssg;
1772 x_contact_name := get_contact_info_rec.ContactName;
1773 --}
1774 END LOOP;
1775 -- END OF get_contact_info_cur
1776
1777
1778 IF get_contact_info_cur%ISOPEN THEN
1779 CLOSE get_contact_info_cur;
1780 END IF;
1781 -- Standard call to get message count and if count is 1,get message info.
1782 --
1783 FND_MSG_PUB.Count_And_Get
1784 (
1785 p_count => x_msg_count,
1786 p_data => x_msg_data,
1787 p_encoded => FND_API.G_FALSE
1788 );
1789 --
1790 --
1791
1792
1793
1794
1795 --}
1796 EXCEPTION
1797 --{
1798 WHEN FND_API.G_EXC_ERROR THEN
1799 ROLLBACK TO GET_CARRIER_CONTACT_INFO_PUB;
1800 x_return_status := FND_API.G_RET_STS_ERROR ;
1801 FND_MSG_PUB.Count_And_Get
1802 (
1803 p_count => x_msg_count,
1804 p_data => x_msg_data,
1805 p_encoded => FND_API.G_FALSE
1806 );
1807 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1808 ROLLBACK TO GET_CARRIER_CONTACT_INFO_PUB;
1809 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1810 FND_MSG_PUB.Count_And_Get
1811 (
1812 p_count => x_msg_count,
1813 p_data => x_msg_data,
1814 p_encoded => FND_API.G_FALSE
1815 );
1816 WHEN OTHERS THEN
1817 ROLLBACK TO GET_CARRIER_CONTACT_INFO_PUB;
1818 wsh_util_core.default_handler('FTE_MLS_UTIL.GET_CARRIER_CONTACT_INFO');
1819 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1820 FND_MSG_PUB.Count_And_Get
1821 (
1822 p_count => x_msg_count,
1823 p_data => x_msg_data,
1824 p_encoded => FND_API.G_FALSE
1825 );
1826
1827 --}
1828
1829 END GET_CARRIER_CONTACT_INFO;
1830
1831
1832 -- get the carrier information
1833 FUNCTION GET_ORG_NAME_BY_FIRSTSTOP
1834 (p_stop_id IN NUMBER)
1835 RETURN VARCHAR2
1836 IS
1837 --{
1838 --}
1839
1840 CURSOR get_org_info_cur (c_stop_id IN NUMBER)
1841 IS
1842 SELECT distinct(org.name) org_name
1843 FROM wsh_delivery_legs dlegs, wsh_new_deliveries dlvy,
1844 wsh_trip_stops stops, hr_organization_units org
1845 WHERE dlegs.delivery_id = dlvy.delivery_id
1846 AND dlegs.pick_up_stop_id = stops.stop_id
1847 AND org.organization_id = dlvy.organization_id
1848 AND stops.stop_id = c_stop_id;
1849
1850 l_org_name VARCHAR2(1000);
1851
1852 --{
1853 BEGIN
1854 --
1855
1856 OPEN get_org_info_cur(p_stop_id);
1857 FETCH get_org_info_cur
1858 INTO l_org_name;
1859
1860 RETURN l_org_name;
1861
1862 --}
1863 EXCEPTION
1864 WHEN OTHERS THEN
1865 wsh_util_core.default_handler('FTE_MLS_UTIL.GET_ORG_NAME_BY_FIRSTSTOP');
1866 RAISE;
1867 --}
1868
1869 END GET_ORG_NAME_BY_FIRSTSTOP;
1870
1871
1872 -- get the carrier information
1873 FUNCTION GET_PICKUP_DLVY_ORG_BY_TRIP
1874 (p_trip_id IN NUMBER)
1875 RETURN VARCHAR2
1876 IS
1877 --{
1878 --}
1879
1880 CURSOR get_org_info_cur (c_trip_id IN NUMBER)
1881 IS
1882 SELECT distinct(org.name) org_name
1883 FROM wsh_delivery_legs dlegs, wsh_new_deliveries dlvy,
1884 wsh_trip_stops stops, hr_organization_units org,
1885 wsh_trips trips
1886 WHERE dlegs.delivery_id = dlvy.delivery_id
1887 AND dlegs.pick_up_stop_id = stops.stop_id
1888 AND org.organization_id = dlvy.organization_id
1889 AND stops.trip_id = trips.trip_id
1890 AND trips.trip_id = c_trip_id;
1891
1892 l_org_name VARCHAR2(1000);
1893
1894 --{
1895 BEGIN
1896 --
1897
1898 OPEN get_org_info_cur(p_trip_id);
1899 FETCH get_org_info_cur
1900 INTO l_org_name;
1901
1902 RETURN l_org_name;
1903
1904 --}
1905 EXCEPTION
1906 WHEN OTHERS THEN
1907 wsh_util_core.default_handler('FTE_MLS_UTIL.GET_PICKUP_DLVY_ORG_BY_TRIP');
1908 RAISE;
1909 --}
1910
1911 END GET_PICKUP_DLVY_ORG_BY_TRIP;
1912
1913
1914 -- get the carrier information
1915 FUNCTION GET_PICKUP_DLVY_ORGID_BY_TRIP
1916 (p_trip_id IN NUMBER)
1917 RETURN NUMBER
1918 IS
1919 --{
1920 --}
1921
1922 CURSOR get_org_info_cur (c_trip_id IN NUMBER)
1923 IS
1924 SELECT distinct(dlvy.organization_id) org_id
1925 FROM wsh_delivery_legs dlegs, wsh_new_deliveries dlvy,
1926 wsh_trip_stops stops, wsh_trips trips
1927 WHERE dlegs.delivery_id = dlvy.delivery_id
1928 AND dlegs.pick_up_stop_id = stops.stop_id
1929 AND stops.trip_id = trips.trip_id
1930 AND trips.trip_id = c_trip_id;
1931
1932 l_org_id NUMBER;
1933
1934 --{
1935 BEGIN
1936 --
1937
1938 OPEN get_org_info_cur(p_trip_id);
1939 FETCH get_org_info_cur
1940 INTO l_org_id;
1941
1942 RETURN l_org_id;
1943
1944 --}
1945 EXCEPTION
1946 WHEN OTHERS THEN
1947 wsh_util_core.default_handler('FTE_MLS_UTIL.GET_PICKUP_DLVY_ORGID_BY_TRIP');
1948 RAISE;
1949 --}
1950
1951 END GET_PICKUP_DLVY_ORGID_BY_TRIP;
1952
1953 PROCEDURE GET_SHIPPER_CONTACT_INFO
1954 (p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1955 p_shipper_name IN VARCHAR2,
1956 x_return_status OUT NOCOPY VARCHAR2,
1957 x_msg_count OUT NOCOPY NUMBER,
1958 x_msg_data OUT NOCOPY VARCHAR2,
1959 x_shipper_name OUT NOCOPY VARCHAR2,
1960 x_contact_email OUT NOCOPY VARCHAR2,
1961 x_contact_phone OUT NOCOPY VARCHAR2,
1962 x_contact_fax OUT NOCOPY VARCHAR2)
1963 IS
1964 --{
1965
1966 l_api_name CONSTANT VARCHAR2(30) := 'GET_SHIPPER_CONTACT_INFO';
1967 l_api_version CONSTANT NUMBER := 1.0;
1968 l_na_mssg VARCHAR2(200);
1969 l_shipper_email VARCHAR2(200);
1970 l_shipper_fax VARCHAR2(200);
1971 l_shipper_name VARCHAR2(200);
1972 l_shipper_phone VARCHAR2(40);
1973
1974 --}
1975
1976 -- cursor to get the shipper email address and fax number
1977 CURSOR get_shipper_contact_cur (c_shipper_username VARCHAR2)
1978 IS
1979 SELECT fu.email_address as email,
1980 fu.fax as fax,
1981 nvl(hp.party_name,fu.user_name) as name,
1982 hp.primary_phone_number as phone
1983 FROM fnd_user fu,
1984 hz_parties hp
1985 WHERE fu.person_party_id = hp.party_id(+)
1986 and fu.user_name = c_shipper_username;
1987
1988 --{
1989 BEGIN
1990 --
1991 -- Standard Start of API savepoint
1992 SAVEPOINT GET_SHIPPER_CONTACT_INFO_PUB;
1993 --
1994 --
1995 -- Initialize message list if p_init_msg_list is set to TRUE.
1996 --
1997 --
1998 IF FND_API.to_Boolean( p_init_msg_list )
1999 THEN
2000 FND_MSG_PUB.initialize;
2001 END IF;
2002 --
2003 --
2004 -- Initialize API return status to success
2005 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2006 x_msg_count := 0;
2007 x_msg_data := 0;
2008
2009 -- get default no contact info message
2010 FND_MESSAGE.SET_NAME('FTE', 'FTE_DELIVERIES_MULTIPLE_NA');
2011 l_na_mssg := FND_MESSAGE.GET;
2012
2013 -- get contact inf
2014
2015 FOR get_shipper_contact_rec IN get_shipper_contact_cur(p_shipper_name)
2016 LOOP
2017 --{
2018 l_shipper_email := get_shipper_contact_rec.email;
2019 l_shipper_fax := get_shipper_contact_rec.fax;
2020 l_shipper_phone := get_shipper_contact_rec.phone;
2021 l_shipper_name := get_shipper_contact_rec.name;
2022 --}
2023 END LOOP;
2024
2025 -- END OF get_shipper_contact_cur
2026
2027 IF get_shipper_contact_cur%ISOPEN THEN
2028 CLOSE get_shipper_contact_cur;
2029 END IF;
2030
2031 IF (l_shipper_email IS NULL)
2032 THEN
2033 l_shipper_email := l_na_mssg;
2034 END IF;
2035
2036 IF (l_shipper_fax IS NULL)
2037 THEN
2038 l_shipper_fax := l_na_mssg;
2039 END IF;
2040
2041
2042 x_shipper_name := l_shipper_name;
2043 x_contact_email := l_shipper_email;
2044 x_contact_fax := l_shipper_fax;
2045 x_contact_phone := l_shipper_phone;
2046
2047
2048
2049 -- x_shipper_contact := p_shipper_name||', '||l_shipper_email||', '||l_shipper_fax;
2050
2051 -- Standard call to get message count and if count is 1,get message info.
2052 --
2053 FND_MSG_PUB.Count_And_Get
2054 (
2055 p_count => x_msg_count,
2056 p_data => x_msg_data,
2057 p_encoded => FND_API.G_FALSE
2058 );
2059 --
2060 --
2061
2062
2063 --}
2064 EXCEPTION
2065 --{
2066 WHEN FND_API.G_EXC_ERROR THEN
2067 ROLLBACK TO GET_SHIPPER_CONTACT_INFO_PUB;
2068 x_return_status := FND_API.G_RET_STS_ERROR ;
2069 FND_MSG_PUB.Count_And_Get
2070 (
2071 p_count => x_msg_count,
2072 p_data => x_msg_data,
2073 p_encoded => FND_API.G_FALSE
2074 );
2075 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2076 ROLLBACK TO GET_SHIPPER_CONTACT_INFO_PUB;
2077 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2078 FND_MSG_PUB.Count_And_Get
2079 (
2080 p_count => x_msg_count,
2081 p_data => x_msg_data,
2082 p_encoded => FND_API.G_FALSE
2083 );
2084 WHEN OTHERS THEN
2085 ROLLBACK TO GET_SHIPPER_CONTACT_INFO_PUB;
2086 wsh_util_core.default_handler('FTE_MLS_UTIL.GET_SHIPPER_CONTACT_INFO');
2087 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2088 FND_MSG_PUB.Count_And_Get
2089 (
2090 p_count => x_msg_count,
2091 p_data => x_msg_data,
2092 p_encoded => FND_API.G_FALSE
2093 );
2094
2095 --}
2096
2097 END GET_SHIPPER_CONTACT_INFO;
2098
2099
2100
2101 --*******************************************************
2102 -- ------------------------------------------------------------------------------- --
2103 -- --
2104 -- NAME: GET_CARRIER_ID --
2105 -- TYPE: FUNCTION --
2106 -- PARAMETERS (IN): p_tender_id NUMBER --
2107 -- --
2108 -- PARAMETERS (OUT): none --
2109 -- PARAMETERS (IN OUT): none --
2110 -- RETURN: x_return NUMBER --
2111 -- DESCRIPTION: This procedure will fetch the carrier_id for the --
2112 -- corresponding load tender. --
2113 -- --
2114 -- CHANGE CONTROL LOG --
2115 -- ------------------ --
2116 -- --
2117 -- DATE VERSION BY BUG DESCRIPTION --
2118 -- ---------- ------- -------- ------- --------------------------------------- --
2119 -- 2003 11.5.9 SAMUTHUK Created --
2120 -- --
2121 -- ------------------------------------------------------------------------------- --
2122
2123 FUNCTION GET_CARRIER_ID (
2124 p_tender_id IN NUMBER
2125 )
2126 RETURN NUMBER
2127 IS
2128 --{
2129 --{
2130 CURSOR get_carrier_id_cur(tender_id NUMBER) IS
2131 SELECT carrier_id
2132 FROM
2133 wsh_trips
2134 where load_tender_number = tender_id;
2135
2136 l_carrier_id NUMBER;
2137
2138 --}
2139 BEGIN
2140 --{
2141 OPEN get_carrier_id_cur(p_tender_id);
2142 FETCH get_carrier_id_cur into l_carrier_id;
2143 CLOSE get_carrier_id_cur;
2144
2145 RETURN l_carrier_id;
2146
2147 --}
2148
2149 EXCEPTION
2150 --{
2151 WHEN OTHERS THEN
2152 wsh_util_core.default_handler('FTE_MLS_UTIL.GET_CARRIER_ID');
2153 RAISE;
2154 --}
2155
2156 END GET_CARRIER_ID;
2157
2158 --}
2159
2160
2161 --*******************************************************
2162 -- ------------------------------------------------------------------------------- --
2163 -- --
2164 -- NAME: FTE_UOM_CONV --
2165 -- TYPE: FUNCTION --
2166 -- PARAMETERS (IN): p_from_quantity NUMBER --
2167 -- p_from_uom VARCHAR2 --
2168 -- p_to_uom VARCHAR2
2169 -- --
2170 -- PARAMETERS (OUT): none --
2171 -- PARAMETERS (IN OUT): none --
2172 -- RETURN: x_return NUMBER --
2173 -- DESCRIPTION: This procedure will convert from_uom to milliseconds --
2174 -- as a medium, then convert it to to_uom. --
2175 -- --
2176 -- CHANGE CONTROL LOG --
2177 -- ------------------ --
2178 -- --
2179 -- DATE VERSION BY BUG DESCRIPTION --
2180 -- ---------- ------- -------- ------- --------------------------------------- --
2181 -- 2003 11.5.9 NLTAN Created --
2182 -- --
2183 -- ------------------------------------------------------------------------------- --
2184 FUNCTION FTE_UOM_CONV
2185 (
2186 p_from_quantity IN NUMBER,
2187 p_from_uom IN VARCHAR2,
2188 p_to_uom IN VARCHAR2
2189 )
2190 RETURN NUMBER
2191 IS
2192 --{
2193 x_return NUMBER;
2194 l_time NUMBER; -- medium converted value
2195 l_upper_from_uom VARCHAR2(200); -- change to upper
2196 l_upper_to_uom VARCHAR2(200); -- change to upper
2197 --}
2198 BEGIN
2199 --{
2200 x_return := p_from_quantity;
2201
2202 -- If zero quantity, then return zero
2203 IF (p_from_quantity = 0)
2204 THEN
2205 RETURN x_return;
2206 ELSE
2207 --{
2208 -- Convert p_from_quantity to milliseconds
2209 l_upper_from_uom := upper(p_from_uom);
2210 IF (l_upper_from_uom = 'SEC') THEN
2211 l_time := p_from_quantity*1000;
2212 ELSIF (l_upper_from_uom = 'MIN') THEN
2213 l_time := p_from_quantity*60*1000;
2214 ELSIF (l_upper_from_uom = 'HR') THEN
2215 l_time := p_from_quantity*60*60*1000;
2216 ELSIF (l_upper_from_uom = 'DAY') THEN
2217 l_time := p_from_quantity*60*60*24*1000;
2218 ELSIF (l_upper_from_uom = 'WK') THEN
2219 l_time := p_from_quantity*60*60*24*7*1000;
2220 ELSIF (l_upper_from_uom = 'MTH') THEN
2221 l_time := p_from_quantity*60*60*24*(365/12)*1000;
2222 ELSIF (l_upper_from_uom = 'QRT') THEN
2223 l_time := p_from_quantity*60*60*24*(365/4)*1000;
2224 ELSIF (l_upper_from_uom = 'YR') THEN
2225 l_time := p_from_quantity*60*60*24*365*1000;
2226 ELSE
2227 x_return := -9999;
2228 END IF;
2229
2230 -- Convert time to p_to_uom value
2231 l_upper_to_uom := upper(p_to_uom);
2232 IF (l_upper_to_uom = 'SEC') THEN
2233 x_return := l_time/1000;
2234 ELSIF (l_upper_to_uom = 'MIN') THEN
2235 x_return := l_time/(60*1000);
2236 ELSIF (l_upper_to_uom = 'HR') THEN
2237 x_return := l_time/(60*60*1000);
2238 ELSIF (l_upper_to_uom = 'DAY') THEN
2239 x_return := l_time/(60*60*24*1000);
2240 ELSIF (l_upper_to_uom = 'WK') THEN
2241 x_return := l_time/(60*60*24*7*1000);
2242 ELSIF (l_upper_to_uom = 'MTH') THEN
2243 x_return := l_time/(60*60*24*(365/12)*1000);
2244 ELSIF (l_upper_to_uom = 'QRT') THEN
2245 x_return := l_time/(60*60*24*(365/4)*1000);
2246 ELSIF (l_upper_to_uom = 'YR') THEN
2247 x_return := l_time/(60*60*24*365*1000);
2248 ELSE
2249 x_return := -9999;
2250 END IF;
2251 --}
2252 END IF;
2253
2254 RETURN x_return;
2255 --}
2256
2257 EXCEPTION
2258 --{
2259 WHEN OTHERS THEN
2260 wsh_util_core.default_handler('FTE_MLS_UTIL.FTE_UOM_CONV');
2261 RAISE;
2262 --}
2263
2264 END FTE_UOM_CONV;
2265
2266 --
2267 --========================================================================
2268 -- PROCEDURE : COPY_FTE_ID_TO_WSH_ID
2269 --
2270 -- PARAMETERS: p_fte_id_tab IN FTE_ID_TAB_TYPE
2271 -- x_wsh_id_tab OUT NOCOPY WSH_UTIL_CORE.id_tab_type
2272 -- VERSION : current version 1.0
2273 -- initial version 1.0
2274 --========================================================================
2275
2276 PROCEDURE COPY_FTE_ID_TO_WSH_ID (p_fte_id_tab IN FTE_ID_TAB_TYPE,
2277 x_wsh_id_tab OUT NOCOPY WSH_UTIL_CORE.ID_TAB_TYPE)
2278 AS
2279
2280 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
2281 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || 'COPY_FTE_ID_TO_WSH_ID';
2282
2283
2284 BEGIN
2285
2286 IF l_debug_on THEN
2287 wsh_debug_sv.push(l_module_name);
2288 END IF;
2289
2290
2291 IF (p_fte_id_tab.count > 0) THEN
2292 FOR i in 1..p_fte_id_tab.count LOOP
2293 x_wsh_id_tab(i) := p_fte_id_tab(i);
2294 IF l_debug_on THEN
2295 WSH_DEBUG_SV.logmsg(l_module_name,' Copying Id ' || x_wsh_id_tab(i));
2296 END IF;
2297 END LOOP;
2298 END IF;
2299
2300 IF l_debug_on THEN
2301 wsh_debug_sv.pop(l_module_name);
2302 END IF;
2303
2304
2305 EXCEPTION
2306 WHEN OTHERS THEN
2307 IF l_debug_on THEN
2308 wsh_debug_sv.log (l_module_name,'UTIL ERROR',substr(sqlerrm,1,200));
2309 wsh_debug_sv.pop(l_module_name);
2310 END IF;
2311 RAISE;
2312 END COPY_FTE_ID_TO_WSH_ID;
2313 --}
2314 --{
2315
2316
2317 --
2318 --========================================================================
2319 -- PROCEDURE : COPY_WSH_ID_TO_FTE_ID
2320 --
2321 -- PARAMETERS: p_wsh_id_tab IN WSH_UTIL_CORE.id_tab_type
2322 -- x_fte_id_tab OUT NOCOPY FTE_ID_TAB_TYPE
2323 --
2324 -- VERSION : current version 1.0
2325 -- initial version 1.0
2326 --========================================================================
2327
2328 PROCEDURE COPY_WSH_ID_TO_FTE_ID (p_wsh_id_tab IN WSH_UTIL_CORE.ID_TAB_TYPE,
2329 x_fte_id_tab IN OUT NOCOPY FTE_ID_TAB_TYPE)
2330 AS
2331
2332 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
2333 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || 'COPY_FTE_ID_TO_WSH_ID';
2334
2335
2336 BEGIN
2337
2338 IF l_debug_on THEN
2339 wsh_debug_sv.push(l_module_name);
2340 END IF;
2341
2342
2343 IF (p_wsh_id_tab.count > 0) THEN
2344 FOR i in 1..p_wsh_id_tab.count LOOP
2345 x_fte_id_tab.EXTEND;
2346 IF l_debug_on THEN
2347 WSH_DEBUG_SV.logmsg(l_module_name,' Copying Id ' || p_wsh_id_tab(i));
2348 END IF;
2349 x_fte_id_tab(i) := p_wsh_id_tab(i);
2350 END LOOP;
2351 END IF;
2352
2353 IF l_debug_on THEN
2354 wsh_debug_sv.pop(l_module_name);
2355 END IF;
2356
2357 EXCEPTION
2358 WHEN OTHERS THEN
2359 IF l_debug_on THEN
2360 wsh_debug_sv.log (l_module_name,'UTIL ERROR',substr(sqlerrm,1,200));
2361 wsh_debug_sv.pop(l_module_name);
2362 END IF;
2363 RAISE;
2364 END COPY_WSH_ID_TO_FTE_ID;
2365
2366
2367
2368 --{Rel 12 HBHAGAVA
2369
2370
2371 --
2372
2373 PROCEDURE GET_MESSAGE_MEANING(p_message_name IN VARCHAR2,
2374 x_message_text OUT NOCOPY VARCHAR2)
2375 AS
2376
2377 BEGIN
2378
2379 SELECT message_text INTO x_message_text
2380 FROM FND_NEW_MESSAGES
2381 WHERE message_name = p_message_name
2382 AND LANGUAGE_CODE = userenv('LANG')
2383 AND APPLICATION_ID = 716;
2384
2385 IF (SQL%NOTFOUND) THEN
2386 x_message_text := 'Message Not seeded ' || p_message_name;
2387 END IF;
2388 EXCEPTION
2389 WHEN OTHERS THEN
2390 x_message_text := 'Message Not seeded ' || p_message_name;
2391 -- RAISE;
2392 END GET_MESSAGE_MEANING;
2393
2394
2395 -- Rel 12
2396 -- Start of comments
2397 -- API name : Get_Lookup_Meaning
2398 -- Type : Public
2399 -- Pre-reqs : None.
2400 -- Function : API to get meaning for lookup code and type.
2401 -- Parameters :
2402 -- IN:
2403 -- p_lookup_type IN Lookup Type.
2404 -- P_lookup_code IN Lookup Code.
2405 -- OUT:
2406 -- Api return meaning for lookup code and type.
2407 -- End of comments
2408 FUNCTION Get_Lookup_Meaning(p_lookup_type IN VARCHAR2,
2409 P_lookup_code IN VARCHAR2)
2410 RETURN VARCHAR2
2411 IS
2412
2413 CURSOR get_meaning IS
2414 SELECT meaning
2415 FROM Fnd_lookup_values_vl
2416 WHERE LOOKUP_TYPE = p_lookup_type
2417 AND LOOKUP_CODE = P_lookup_code;
2418
2419 l_meaning VARCHAR2(80);
2420
2421 l_debug_on BOOLEAN;
2422 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || 'FTE_MLS_UTIL' || '.' || 'GET_LOOKUP_MEANING';
2423 --
2424 BEGIN
2425 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2426
2427 IF l_debug_on IS NULL THEN
2428 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2429 END IF;
2430
2431 IF l_debug_on THEN
2432 WSH_DEBUG_SV.push(l_module_name);
2433 WSH_DEBUG_SV.log(l_module_name,'p_lookup_type',p_lookup_type);
2434 WSH_DEBUG_SV.log(l_module_name,'P_lookup_code',P_lookup_code);
2435 END IF;
2436
2437 OPEN get_meaning;
2438 FETCH get_meaning INTO l_meaning;
2439 CLOSE get_meaning;
2440
2441 IF l_debug_on THEN
2442 WSH_DEBUG_SV.log(l_module_name,'l_meaning',l_meaning);
2443 WSH_DEBUG_SV.pop(l_module_name);
2444 END IF;
2445
2446 IF (l_meaning IS NULL) THEN
2447 l_meaning := P_lookup_code;
2448 ELSE
2449 l_meaning := l_meaning;
2450 END IF;
2451
2452 RETURN l_meaning;
2453
2454 EXCEPTION
2455 WHEN others THEN
2456 IF l_debug_on THEN
2457 WSH_DEBUG_SV.pop(l_module_name);
2458 END IF;
2459 RETURN P_lookup_code;
2460 END Get_Lookup_Meaning;
2461
2462
2463
2464 --{
2465 --========================================================================
2466 -- PROCEDURE : GET_CURRENCY_CODE
2467 --
2468 -- COMMENT : Return back carrier currency code for a give trip id.
2469 -- CREATED BY: HBHAGAVA
2470 -- MODIFIED :
2471 -- DESC: This procedure returns back carrier currency code for a given trip id.
2472 -- Carrier on the trip will be used.
2473 -- Steps
2474 -- First get the organization associated with the trip.
2475 -- Then get the vendor site that is associated with this orgnization
2476 -- for the carrier on the trip.
2477 -- If there is no supplier link then use carrier default currency.
2478 --========================================================================
2479
2480
2481 PROCEDURE GET_CURRENCY_CODE(
2482 p_init_msg_list IN VARCHAR2,
2483 x_return_status OUT NOCOPY VARCHAR2,
2484 x_msg_count OUT NOCOPY NUMBER,
2485 x_msg_data OUT NOCOPY VARCHAR2,
2486 x_currency_code OUT NOCOPY VARCHAR2,
2487 p_entity_type IN VARCHAR2,
2488 p_entity_id IN NUMBER,
2489 p_carrier_id IN NUMBER)
2490 IS
2491
2492 l_api_name CONSTANT VARCHAR2(30) := 'GET_CURRENCY_CODE';
2493 l_api_version CONSTANT NUMBER := 1.0;
2494 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
2495 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || l_api_name;
2496
2497
2498 l_return_status VARCHAR2(32767);
2499 l_msg_count NUMBER;
2500 l_msg_data VARCHAR2(32767);
2501 l_number_of_warnings NUMBER;
2502 l_number_of_errors NUMBER;
2503
2504 l_vendor_site_id NUMBER;
2505 l_vendor_id NUMBER;
2506 l_carrier_currency VARCHAR2(15);
2507
2508 l_organization_id NUMBER;
2509 l_currency_code VARCHAR2(15);
2510 l_carrier_site_id NUMBER;
2511 l_carrier_id NUMBER;
2512
2513
2514 CURSOR GET_VENDOR_SITE_ID_FOR_TRIP(l_organization_id NUMBER) IS
2515 SELECT decode(sites.supplier_site_id,null,car.supplier_site_id,
2516 sites.supplier_site_id), car.currency_code
2517 FROM WSH_ORG_CARRIER_SITES org_sites,
2518 WSH_CARRIER_SITES sites,
2519 WSH_CARRIERS car, wsh_trips trips
2520 WHERE org_sites.ORGANIZATION_ID = l_organization_id
2521 AND sites.carrier_site_id = org_sites.carrier_site_id
2522 AND sites.carrier_id = car.carrier_id
2523 AND car.CARRIER_ID = trips.carrier_id
2524 AND org_sites.ENABLED_FLAG = 'Y'
2525 and trips.trip_id = P_ENTITY_ID;
2526
2527
2528 CURSOR GET_VENDOR_SITE_ID_FOR_DLVY IS
2529 SELECT decode(sites.supplier_site_id,null,car.supplier_site_id,
2530 sites.supplier_site_id), car.currency_code FROM WSH_ORG_CARRIER_SITES org_sites,
2531 WSH_CARRIER_SITES sites,
2532 WSH_CARRIERS car, wsh_new_deliveries del
2533 WHERE org_sites.ORGANIZATION_ID = del.organization_id
2534 AND sites.carrier_site_id = org_sites.carrier_site_id
2535 AND sites.carrier_id = car.carrier_id
2536 AND car.CARRIER_ID = del.carrier_id
2537 AND org_sites.ENABLED_FLAG = 'Y'
2538 AND del.delivery_id = P_ENTITY_ID;
2539
2540
2541 CURSOR GET_VENDOR_SITE_ID_FOR_CARRIER(l_organization_id NUMBER, l_carrier_id NUMBER) IS
2542 SELECT decode(sites.supplier_site_id,null,car.supplier_site_id,
2543 sites.supplier_site_id), car.currency_code
2544 FROM WSH_ORG_CARRIER_SITES org_sites,
2545 WSH_CARRIER_SITES sites,
2546 WSH_CARRIERS car
2547 WHERE org_sites.ORGANIZATION_ID = l_organization_id
2548 AND sites.carrier_site_id = org_sites.carrier_site_id
2549 AND sites.carrier_id = car.carrier_id
2550 AND org_sites.ENABLED_FLAG = 'Y'
2551 AND car.carrier_id = l_carrier_id;
2552
2553
2554 CURSOR GET_CARRIER_SITE(l_organization_id NUMBER, l_carrier_id NUMBER) IS
2555 SELECT sites.carrier_site_id, sites.supplier_site_id
2556 FROM WSH_CARRIER_SITES sites,
2557 WSH_ORG_CARRIER_SITES org_sites,
2558 WSH_CARRIERS car
2559 WHERE org_sites.ORGANIZATION_ID = l_organization_id
2560 AND sites.carrier_site_id = org_sites.carrier_site_id
2561 AND sites.carrier_id = car.carrier_id
2562 AND org_sites.ENABLED_FLAG = 'Y'
2563 AND car.carrier_id = l_carrier_id;
2564
2565 BEGIN
2566
2567
2568 IF l_debug_on THEN
2569 WSH_DEBUG_SV.push(l_module_name);
2570 END IF;
2571
2572 IF FND_API.to_Boolean( p_init_msg_list )
2573 THEN
2574 FND_MSG_PUB.initialize;
2575 END IF;
2576
2577 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2578 x_msg_count := 0;
2579 x_msg_data := 0;
2580 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2581 l_number_of_warnings := 0;
2582 l_number_of_errors := 0;
2583
2584 IF l_debug_on
2585 THEN
2586 WSH_DEBUG_SV.logmsg(l_module_name,' ENTITY TYPE ' || P_ENTITY_TYPE,
2587 WSH_DEBUG_SV.C_PROC_LEVEL);
2588 WSH_DEBUG_SV.logmsg(l_module_name,' ENTITY ID ' || P_ENTITY_ID,
2589 WSH_DEBUG_SV.C_PROC_LEVEL);
2590 END IF;
2591
2592
2593
2594 IF (UPPER(P_ENTITY_TYPE) = 'TRIP')
2595 THEN
2596 --{
2597
2598 -- first get the organization id for the trip
2599 l_organization_id := GET_TRIP_ORGANIZATION_ID(P_ENTITY_ID);
2600
2601 IF l_debug_on
2602 THEN
2603 WSH_DEBUG_SV.logmsg(l_module_name,' Organization Id after calling GET_TRIP_ORGANIZATION_ID ' ||
2604 l_organization_id,
2605 WSH_DEBUG_SV.C_PROC_LEVEL);
2606 END IF;
2607
2608
2609 IF (l_organization_id IS NOT NULL)
2610 THEN
2611 IF (p_carrier_id IS NOT NULL)
2612 THEN
2613 l_carrier_id := p_carrier_id;
2614 ELSE
2615 SELECT CARRIER_ID INTO l_carrier_id
2616 FROM WSH_TRIPS
2617 WHERE TRIP_ID = p_entity_id;
2618 END IF;
2619
2620 END IF;
2621
2622
2623 --}
2624 ELSIF (UPPER(P_ENTITY_TYPE) = 'DELIVERY')
2625 THEN
2626 --{
2627
2628 IF (p_carrier_id IS NOT NULL)
2629 THEN
2630 --{
2631 l_carrier_id := p_carrier_id;
2632 --}
2633 ELSE
2634 SELECT CARRIER_ID INTO l_carrier_id
2635 FROM WSH_NEW_DELIVERIES
2636 WHERE DELIVERY_ID = p_entity_id;
2637 END IF;
2638
2639
2640 SELECT ORGANIZATION_ID INTO l_organization_id FROM WSH_NEW_DELIVERIES
2641 WHERE DELIVERY_ID = p_entity_id;
2642
2643 --}
2644 ELSIF (UPPER(P_ENTITY_TYPE) = 'LOCATION')
2645 THEN
2646 --{
2647 -- first get the organization id for the location id
2648 BEGIN
2649
2650 SELECT mp. organization_id
2651 INTO l_organization_id
2652 FROM hr_organization_units hou,mtl_parameters mp
2653 WHERE hou.organization_id = mp.organization_id
2654 AND hou.location_id = p_entity_id
2655 AND trunc(sysdate) <= nvl( hou.date_to, trunc(sysdate));
2656
2657
2658 IF (SQL%NOTFOUND) THEN
2659 RAISE NO_DATA_FOUND;
2660 END IF;
2661
2662 EXCEPTION
2663 WHEN NO_DATA_FOUND THEN
2664 FND_MESSAGE.SET_NAME('FTE','FTE_ORG_NOTFOUND');
2665 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2666 wsh_util_core.add_message(x_return_status);
2667 END;
2668
2669 l_carrier_id := p_carrier_id;
2670
2671 --}
2672 END IF;
2673
2674
2675 IF l_debug_on
2676 THEN
2677 WSH_DEBUG_SV.logmsg(l_module_name,' Organization Id ' ||
2678 l_organization_id,
2679 WSH_DEBUG_SV.C_PROC_LEVEL);
2680 END IF;
2681
2682 IF (l_organization_id IS NOT NULL AND
2683 l_carrier_id IS NOT NULL)
2684 THEN
2685
2686
2687 OPEN GET_CARRIER_SITE(l_organization_id,l_carrier_id);
2688 FETCH GET_CARRIER_SITE INTO l_carrier_site_id, l_vendor_site_id;
2689
2690 IF (GET_CARRIER_SITE%NOTFOUND)
2691 THEN
2692 IF l_debug_on
2693 THEN
2694 WSH_DEBUG_SV.logmsg(l_module_name,' Carrier site not found ',
2695 WSH_DEBUG_SV.C_PROC_LEVEL);
2696 END IF;
2697
2698
2699 ELSE
2700 IF l_debug_on
2701 THEN
2702 WSH_DEBUG_SV.logmsg(l_module_name,' Found carrier site id ' || l_carrier_site_id,
2703 WSH_DEBUG_SV.C_PROC_LEVEL);
2704 WSH_DEBUG_SV.logmsg(l_module_name,' Found Supplier site id ' || l_vendor_site_id,
2705 WSH_DEBUG_SV.C_PROC_LEVEL);
2706 END IF;
2707 END IF;
2708
2709 IF (l_vendor_site_id IS NULL)
2710 THEN
2711 -- Get the default supplier and supplier site
2712 SELECT SUPPLIER_ID,SUPPLIER_SITE_ID,CURRENCY_CODE
2713 INTO l_vendor_id,l_vendor_site_id,l_carrier_currency
2714 FROM WSH_CARRIERS WHERE CARRIER_ID = l_carrier_id;
2715 END IF;
2716
2717
2718 IF (l_vendor_site_id IS NOT NULL)
2719 THEN
2720
2721 IF l_debug_on
2722 THEN
2723 WSH_DEBUG_SV.logmsg(l_module_name,'Using Vendor Site to get currency Vendor Site Id ' ||
2724 l_vendor_site_id,WSH_DEBUG_SV.C_PROC_LEVEL);
2725 END IF;
2726
2727 -- Now get teh currency code
2728
2729 SELECT PAYMENT_CURRENCY_CODE INTO X_CURRENCY_CODE
2730 FROM po_vendor_sites_all
2731 WHERE vendor_site_id = l_vendor_site_id
2732 AND PAY_SITE_FLAG = 'Y';
2733 ELSIF (l_vendor_id IS NOT NULL)
2734 THEN
2735 IF l_debug_on
2736 THEN
2737 WSH_DEBUG_SV.logmsg(l_module_name,' Using Vendor Id to get currency Vendor Id ' || l_vendor_id,
2738 WSH_DEBUG_SV.C_PROC_LEVEL);
2739 END IF;
2740
2741 -- Now get teh currency code
2742
2743 SELECT PAYMENT_CURRENCY_CODE INTO X_CURRENCY_CODE
2744 FROM po_vendors
2745 WHERE vendor_id = l_vendor_id;
2746 ELSE
2747 X_CURRENCY_CODE := l_carrier_currency;
2748
2749 END IF;
2750 END IF;
2751
2752 -- Standard call to get message count and if count is 1,get message info.
2753 --
2754 FND_MSG_PUB.Count_And_Get
2755 (
2756 p_count => x_msg_count,
2757 p_data => x_msg_data,
2758 p_encoded => FND_API.G_FALSE
2759 );
2760
2761
2762 --
2763 --
2764
2765 IF l_debug_on THEN
2766 WSH_DEBUG_SV.pop(l_module_name);
2767 END IF;
2768
2769 --}
2770 EXCEPTION
2771 --{
2772 WHEN FND_API.G_EXC_ERROR THEN
2773 x_return_status := FND_API.G_RET_STS_ERROR;
2774 FND_MSG_PUB.Count_And_Get
2775 (
2776 p_count => x_msg_count,
2777 p_data => x_msg_data,
2778 p_encoded => FND_API.G_FALSE
2779 );
2780 IF l_debug_on THEN
2781 WSH_DEBUG_SV.pop(l_module_name);
2782 END IF;
2783
2784 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2785 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2786 FND_MSG_PUB.Count_And_Get
2787 (
2788 p_count => x_msg_count,
2789 p_data => x_msg_data,
2790 p_encoded => FND_API.G_FALSE
2791 );
2792 IF l_debug_on THEN
2793 WSH_DEBUG_SV.pop(l_module_name);
2794 END IF;
2795 WHEN OTHERS THEN
2796 wsh_util_core.default_handler('FTE_MLS_UTIL.GET_CURRENCY_CODE');
2797 x_return_status := FND_API.G_RET_STS_ERROR;
2798 FND_MSG_PUB.Count_And_Get
2799 (
2800 p_count => x_msg_count,
2801 p_data => x_msg_data,
2802 p_encoded => FND_API.G_FALSE
2803 );
2804 IF l_debug_on THEN
2805 WSH_DEBUG_SV.pop(l_module_name);
2806 END IF;
2807
2808 END GET_CURRENCY_CODE;
2809
2810
2811
2812 --}
2813
2814 PROCEDURE GET_SUPPLIER_INFO(
2815 p_init_msg_list IN VARCHAR2,
2816 x_return_status OUT NOCOPY VARCHAR2,
2817 x_msg_count OUT NOCOPY NUMBER,
2818 x_msg_data OUT NOCOPY VARCHAR2,
2819 x_currency_code OUT NOCOPY VARCHAR2,
2820 x_supplier_id OUT NOCOPY NUMBER,
2821 x_supplier_site_id OUT NOCOPY NUMBER,
2822 x_carrier_site_id OUT NOCOPY NUMBER,
2823 p_entity_type IN VARCHAR2,
2824 p_entity_id IN NUMBER)
2825 IS
2826
2827 l_api_name CONSTANT VARCHAR2(30) := 'GET_SUPPLIER_INFO';
2828 l_api_version CONSTANT NUMBER := 1.0;
2829 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
2830 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || l_api_name;
2831
2832
2833 l_return_status VARCHAR2(32767);
2834 l_msg_count NUMBER;
2835 l_msg_data VARCHAR2(32767);
2836 l_number_of_warnings NUMBER;
2837 l_number_of_errors NUMBER;
2838
2839 l_vendor_site_id NUMBER;
2840 l_vendor_id NUMBER;
2841 l_carrier_site_id NUMBER;
2842 l_organization_id NUMBER;
2843 l_currency_code VARCHAR2(15);
2844
2845 CURSOR GET_VENDOR_SITE_ID_FOR_TRIP(l_organization_id NUMBER,l_entity_id NUMBER) IS
2846 SELECT decode(sites.supplier_site_id,null,car.supplier_site_id,
2847 sites.supplier_site_id), car.currency_code,
2848 car.supplier_id,sites.carrier_site_id
2849 FROM WSH_ORG_CARRIER_SITES org_sites,
2850 WSH_CARRIER_SITES sites,
2851 WSH_CARRIERS car, wsh_trips trips
2852 WHERE org_sites.ORGANIZATION_ID = l_organization_id
2853 AND sites.carrier_site_id = org_sites.carrier_site_id
2854 AND sites.carrier_id = car.carrier_id
2855 AND car.CARRIER_ID = trips.carrier_id
2856 AND org_sites.ENABLED_FLAG = 'Y'
2857 and trips.trip_id = l_entity_id;
2858
2859
2860 CURSOR GET_VENDOR_SITE_ID_FOR_DLVY(l_entity_id NUMBER) IS
2861 SELECT decode(sites.supplier_site_id,null,car.supplier_site_id,
2862 sites.supplier_site_id), car.currency_code,car.supplier_id,
2863 sites.carrier_site_id
2864 FROM WSH_ORG_CARRIER_SITES org_sites,
2865 WSH_CARRIER_SITES sites,
2866 WSH_CARRIERS car, wsh_new_deliveries del
2867 WHERE org_sites.ORGANIZATION_ID = del.organization_id
2868 AND sites.carrier_site_id = org_sites.carrier_site_id
2869 AND sites.carrier_id = car.carrier_id
2870 AND car.CARRIER_ID = del.carrier_id
2871 AND org_sites.ENABLED_FLAG = 'Y'
2872 AND del.delivery_id = l_entity_id;
2873
2874
2875 BEGIN
2876
2877
2878
2879 IF l_debug_on THEN
2880 WSH_DEBUG_SV.push(l_module_name);
2881 END IF;
2882
2883 IF FND_API.to_Boolean( p_init_msg_list )
2884 THEN
2885 FND_MSG_PUB.initialize;
2886 END IF;
2887
2888
2889
2890 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2891 x_msg_count := 0;
2892 x_msg_data := 0;
2893 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2894 l_number_of_warnings := 0;
2895 l_number_of_errors := 0;
2896
2897
2898
2899 IF l_debug_on
2900 THEN
2901 WSH_DEBUG_SV.logmsg(l_module_name,' ENTITY TYPE ' || P_ENTITY_TYPE,
2902 WSH_DEBUG_SV.C_PROC_LEVEL);
2903 WSH_DEBUG_SV.logmsg(l_module_name,' ENTITY ID ' || P_ENTITY_ID,
2904 WSH_DEBUG_SV.C_PROC_LEVEL);
2905 END IF;
2906
2907
2908
2909 IF (UPPER(P_ENTITY_TYPE) = 'TRIP')
2910 THEN
2911 --{
2912
2913 -- first get the organization id for the trip
2914 l_organization_id := GET_TRIP_ORGANIZATION_ID(P_ENTITY_ID);
2915
2916 IF l_debug_on
2917 THEN
2918 WSH_DEBUG_SV.logmsg(l_module_name,' Organization Id after calling GET_TRIP_ORGANIZATION_ID ' ||
2919 l_organization_id,
2920 WSH_DEBUG_SV.C_PROC_LEVEL);
2921 END IF;
2922
2923
2924 IF (l_organization_id IS NOT NULL)
2925 THEN
2926
2927 -- Since user did not pass in carrier id we can use
2928 -- carrier id on the trip
2929
2930 OPEN GET_VENDOR_SITE_ID_FOR_TRIP(l_organization_id,p_entity_id);
2931 FETCH GET_VENDOR_SITE_ID_FOR_TRIP INTO l_vendor_site_id, l_currency_code,
2932 l_vendor_id,l_carrier_site_id;
2933
2934 IF (GET_VENDOR_SITE_ID_FOR_TRIP%NOTFOUND) THEN
2935 FND_MESSAGE.SET_NAME('FTE','FTE_VENDORSITE_NOT_FOUND');
2936 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2937 wsh_util_core.add_message(x_return_status);
2938 END IF;
2939
2940 CLOSE GET_VENDOR_SITE_ID_FOR_TRIP;
2941
2942 END IF;
2943
2944
2945 --}
2946 ELSIF (UPPER(P_ENTITY_TYPE) = 'DELIVERY')
2947 THEN
2948 --{
2949 -- Since user did not pass in carrier id, we are going to use carrier
2950 -- id from delivery.
2951
2952
2953
2954 OPEN GET_VENDOR_SITE_ID_FOR_DLVY(p_entity_id);
2955 FETCH GET_VENDOR_SITE_ID_FOR_DLVY INTO l_vendor_site_id, l_currency_code,
2956 l_vendor_id,l_carrier_site_id;
2957
2958
2959
2960 IF (GET_VENDOR_SITE_ID_FOR_DLVY%NOTFOUND) THEN
2961 FND_MESSAGE.SET_NAME('FTE','FTE_VENDORSITE_NOT_FOUND');
2962 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2963 wsh_util_core.add_message(x_return_status);
2964 END IF;
2965
2966 CLOSE GET_VENDOR_SITE_ID_FOR_DLVY;
2967
2968 --}
2969 END IF;
2970
2971 IF (l_vendor_site_id IS NOT NULL)
2972 THEN
2973
2974 IF l_debug_on
2975 THEN
2976 WSH_DEBUG_SV.logmsg(l_module_name,' Vendor Site Id ' || l_vendor_site_id,
2977 WSH_DEBUG_SV.C_PROC_LEVEL);
2978 END IF;
2979
2980 -- Now get teh currency code
2981
2982 SELECT PAYMENT_CURRENCY_CODE INTO X_CURRENCY_CODE
2983 FROM po_vendor_sites_all
2984 WHERE vendor_site_id = l_vendor_site_id
2985 AND PAY_SITE_FLAG = 'Y';
2986
2987 X_SUPPLIER_ID := l_vendor_id;
2988 X_SUPPLIER_SITE_ID := l_vendor_site_id;
2989 X_CARRIER_SITE_ID := l_carrier_site_id;
2990
2991 END IF;
2992
2993 IF (X_CURRENCY_CODE IS NULL)
2994 THEN
2995 X_CURRENCY_CODE := l_currency_code;
2996 END IF;
2997
2998
2999
3000 -- Standard call to get message count and if count is 1,get message info.
3001 --
3002 FND_MSG_PUB.Count_And_Get
3003 (
3004 p_count => x_msg_count,
3005 p_data => x_msg_data,
3006 p_encoded => FND_API.G_FALSE
3007 );
3008
3009
3010 --
3011 --
3012
3013 IF l_debug_on THEN
3014 WSH_DEBUG_SV.pop(l_module_name);
3015 END IF;
3016
3017 --}
3018 EXCEPTION
3019 --{
3020 WHEN FND_API.G_EXC_ERROR THEN
3021 x_return_status := FND_API.G_RET_STS_ERROR;
3022 FND_MSG_PUB.Count_And_Get
3023 (
3024 p_count => x_msg_count,
3025 p_data => x_msg_data,
3026 p_encoded => FND_API.G_FALSE
3027 );
3028 IF l_debug_on THEN
3029 WSH_DEBUG_SV.pop(l_module_name);
3030 END IF;
3031
3032 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3033 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3034 FND_MSG_PUB.Count_And_Get
3035 (
3036 p_count => x_msg_count,
3037 p_data => x_msg_data,
3038 p_encoded => FND_API.G_FALSE
3039 );
3040 IF l_debug_on THEN
3041 WSH_DEBUG_SV.pop(l_module_name);
3042 END IF;
3043 WHEN OTHERS THEN
3044 wsh_util_core.default_handler('FTE_MLS_UTIL.GET_SUPPLIER_INFO');
3045 x_return_status := FND_API.G_RET_STS_ERROR;
3046 FND_MSG_PUB.Count_And_Get
3047 (
3048 p_count => x_msg_count,
3049 p_data => x_msg_data,
3050 p_encoded => FND_API.G_FALSE
3051 );
3052 IF l_debug_on THEN
3053 WSH_DEBUG_SV.pop(l_module_name);
3054 END IF;
3055
3056 END GET_SUPPLIER_INFO;
3057
3058
3059 --{
3060 --========================================================================
3061 -- PROCEDURE : GET_TRIP_ORGANIZATION_ID
3062 --
3063 -- COMMENT : Return back organization id that is associated with the trip..
3064 -- CREATED BY: HBHAGAVA
3065 -- MODIFIED :
3066 -- DESC: This procedure returns back organiation id that is associated with the trip.
3067 -- Steps
3068 -- For Outbound and Mixed trip's see if there is a organization at the location of first stop
3069 -- For inbound see if there is a organization at the location of the last stop.
3070 -- If there are no organizations associated then get the organization id of the delivery with
3071 -- least delivery id
3072 --========================================================================
3073
3074 FUNCTION GET_TRIP_ORGANIZATION_ID (p_trip_id NUMBER)
3075 RETURN NUMBER
3076 IS
3077
3078 --{
3079
3080 l_api_name CONSTANT VARCHAR2(30) := 'GET_TRIP_ORGANIZATION_ID';
3081 l_api_version CONSTANT NUMBER := 1.0;
3082 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
3083 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || l_api_name;
3084
3085
3086 l_first_stop_loc_id NUMBER;
3087 l_last_stop_loc_id NUMBER;
3088 l_first_stop_id NUMBER;
3089 l_last_stop_id NUMBER;
3090 l_arrival_date DATE;
3091 l_dept_date DATE;
3092 l_sel_stop_loc_id NUMBER;
3093
3094 l_typeflag VARCHAR2(1);
3095 l_organization_id NUMBER;
3096
3097 l_return_status VARCHAR2(1);
3098
3099 l_msg_count NUMBER;
3100 l_msg_data VARCHAR2(30000);
3101 l_number_of_warnings NUMBER;
3102 l_number_of_errors NUMBER;
3103
3104 CURSOR GET_ORG_ID_BY_LOCATION (l_stop_loc_id NUMBER) IS
3105 SELECT mp. organization_id
3106 FROM hr_organization_units hou,mtl_parameters mp
3107 WHERE hou.organization_id = mp.organization_id
3108 AND hou.location_id = l_stop_loc_id
3109 AND trunc(sysdate) <= nvl( hou.date_to, trunc(sysdate));
3110
3111 CURSOR GET_ORG_ID_BY_DELIVERY (l_stop_id NUMBER) IS
3112 SELECT dlvy.ORGANIZATION_ID
3113 FROM WSH_TRIP_STOPS stops, WSH_DELIVERY_LEGS leg,
3114 WSH_NEW_DELIVERIES dlvy
3115 WHERE stops.stop_id = leg.pick_up_stop_id
3116 AND leg.delivery_id = dlvy.delivery_id
3117 AND stops.stop_id = l_stop_id;
3118
3119 BEGIN
3120
3121 IF l_debug_on THEN
3122 WSH_DEBUG_SV.push(l_module_name);
3123 END IF;
3124
3125 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3126 l_number_of_warnings := 0;
3127 l_number_of_errors := 0;
3128
3129 l_sel_stop_loc_id := null;
3130 l_organization_id := null;
3131
3132
3133 -- First get the type of trip. Depending on this we can get the
3134 -- location, Org Id and the there by carrier site.
3135
3136 IF l_debug_on
3137 THEN
3138 WSH_DEBUG_SV.logmsg(l_module_name,' Getting trip stop information ',
3139 WSH_DEBUG_SV.C_PROC_LEVEL);
3140 END IF;
3141
3142 GET_FIRST_LAST_STOP_INFO(x_return_status => l_return_status,
3143 x_arrival_date => l_arrival_date,
3144 x_departure_date => l_dept_date,
3145 x_first_stop_id => l_first_stop_id,
3146 x_last_stop_id => l_last_stop_id,
3147 x_first_stop_loc_id => l_first_stop_loc_id,
3148 x_last_stop_loc_id => l_last_stop_loc_id,
3149 p_trip_id => p_trip_id);
3150
3151
3152 wsh_util_core.api_post_call(
3153 p_return_status =>l_return_status,
3154 x_num_warnings =>l_number_of_warnings,
3155 x_num_errors =>l_number_of_errors,
3156 p_msg_data =>l_msg_data);
3157
3158 IF ( (l_return_status = 'E')
3159 OR (l_return_status = 'U') )
3160 THEN
3161 RAISE FND_API.G_EXC_ERROR;
3162 END IF;
3163
3164 SELECT shipments_type_flag INTO l_typeflag
3165 FROM WSH_TRIPS
3166 WHERE TRIP_ID = p_trip_id;
3167
3168
3169 IF (l_typeflag = 'O' OR l_typeflag = 'M')
3170 THEN
3171 -- outbound or mixed use first stop location id
3172 l_sel_stop_loc_id := l_first_stop_loc_id;
3173
3174 ELSE
3175 -- inbound so use last stop
3176 l_sel_stop_loc_id := l_last_stop_loc_id;
3177
3178 END IF;
3179
3180 IF (l_sel_stop_loc_id IS NOT NULL)
3181 THEN
3182 OPEN GET_ORG_ID_BY_LOCATION(l_sel_stop_loc_id);
3183 FETCH GET_ORG_ID_BY_LOCATION INTO l_organization_id;
3184
3185 IF (GET_ORG_ID_BY_LOCATION%NOTFOUND) THEN
3186 IF l_debug_on
3187 THEN
3188 WSH_DEBUG_SV.logmsg(l_module_name,' Stop Loc Id ' || l_sel_stop_loc_id
3189 || ' has no organization. Checking Delivery ',
3190 WSH_DEBUG_SV.C_PROC_LEVEL);
3191 END IF;
3192 END IF;
3193 CLOSE GET_ORG_ID_BY_LOCATION;
3194 END IF;
3195 --
3196 --
3197
3198 -- if organization id is null then we should get org id from the
3199 -- delivery that is getting picked up at the first stop
3200
3201 IF l_debug_on
3202 THEN
3203 WSH_DEBUG_SV.logmsg(l_module_name,' Before Checking Delivery Org: Organizaton Id ' || l_organization_id ||
3204 ' First Stop Id '||l_first_stop_id,
3205 WSH_DEBUG_SV.C_PROC_LEVEL);
3206 END IF;
3207
3208 IF (l_organization_id IS NULL
3209 AND l_first_stop_id IS NOT NULL)
3210 THEN
3211 OPEN GET_ORG_ID_BY_DELIVERY(l_first_stop_id);
3212 FETCH GET_ORG_ID_BY_DELIVERY INTO l_organization_id;
3213
3214 IF (GET_ORG_ID_BY_DELIVERY%NOTFOUND) THEN
3215 IF l_debug_on
3216 THEN
3217 WSH_DEBUG_SV.logmsg(l_module_name,' Delivery has no OrgId!',
3218 WSH_DEBUG_SV.C_PROC_LEVEL);
3219 END IF;
3220 END IF;
3221 CLOSE GET_ORG_ID_BY_DELIVERY;
3222
3223 END IF;
3224
3225 IF l_debug_on
3226 THEN
3227 WSH_DEBUG_SV.logmsg(l_module_name,' After Checking Delivery Org Organizaton Id ' || l_organization_id ||
3228 ' First Stop Id '||l_first_stop_id,
3229 WSH_DEBUG_SV.C_PROC_LEVEL);
3230 END IF;
3231
3232 IF l_debug_on THEN
3233 WSH_DEBUG_SV.pop(l_module_name);
3234 END IF;
3235
3236 return l_organization_id;
3237
3238 --}
3239 EXCEPTION
3240 --{
3241 WHEN FND_API.G_EXC_ERROR THEN
3242 IF l_debug_on THEN
3243 WSH_DEBUG_SV.pop(l_module_name);
3244 END IF;
3245 return null;
3246
3247 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3248 IF l_debug_on THEN
3249 WSH_DEBUG_SV.pop(l_module_name);
3250 END IF;
3251 return null;
3252
3253 WHEN OTHERS THEN
3254 IF l_debug_on THEN
3255 WSH_DEBUG_SV.pop(l_module_name);
3256 END IF;
3257 return null;
3258
3259 END GET_TRIP_ORGANIZATION_ID;
3260
3261 --{
3262 --========================================================================
3263 -- PROCEDURE : GET_FIRST_LAST_STOP_INFO
3264 --
3265 -- COMMENT : Return back first stop and last stop information..
3266 -- CREATED BY: HBHAGAVA
3267 -- MODIFIED :
3268 --========================================================================
3269
3270 PROCEDURE GET_FIRST_LAST_STOP_INFO(x_return_status OUT NOCOPY VARCHAR2,
3271 x_arrival_date OUT NOCOPY DATE,
3272 x_departure_date OUT NOCOPY DATE,
3273 x_first_stop_id OUT NOCOPY NUMBER,
3274 x_last_stop_id OUT NOCOPY NUMBER,
3275 x_first_stop_loc_id OUT NOCOPY NUMBER,
3276 x_last_stop_loc_id OUT NOCOPY NUMBER,
3277 p_trip_id NUMBER)
3278 IS
3279 --{
3280 CURSOR GET_TRIP_STOPS IS
3281 SELECT stop_location_id, planned_arrival_date, planned_departure_date ,
3282 stops.stop_id
3283 FROM wsh_trip_stops stops, wsh_trips trips
3284 WHERE trips.trip_id = p_trip_id
3285 and trips.trip_id = stops.trip_id
3286 ORDER BY PLANNED_ARRIVAL_DATE,
3287 STOP_SEQUENCE_NUMBER;
3288 --}
3289
3290 --{
3291
3292 l_api_name CONSTANT VARCHAR2(30) := 'GET_FIRST_LAST_STOP_INFO';
3293 l_api_version CONSTANT NUMBER := 1.0;
3294 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
3295 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || l_api_name;
3296
3297 l_stop_loc_id_tbl FTE_ID_TAB_TYPE;
3298 l_stop_id_tbl FTE_ID_TAB_TYPE;
3299
3300 l_typeflag VARCHAR2(1);
3301 l_first_stop NUMBER;
3302 l_idx NUMBER;
3303
3304 BEGIN
3305
3306
3307 IF l_debug_on THEN
3308 WSH_DEBUG_SV.push(l_module_name);
3309 END IF;
3310
3311 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3312
3313 IF l_debug_on
3314 THEN
3315 WSH_DEBUG_SV.logmsg(l_module_name,' Getting trip stop information ',
3316 WSH_DEBUG_SV.C_PROC_LEVEL);
3317 END IF;
3318
3319 l_idx := 0;
3320
3321 FOR GET_TRIP_STOPS_REC IN GET_TRIP_STOPS
3322 LOOP
3323 IF (l_idx = 0)
3324 THEN
3325 -- This is first stop
3326 x_first_stop_id := GET_TRIP_STOPS_REC.STOP_ID;
3327 x_first_stop_loc_id := GET_TRIP_STOPS_REC.STOP_LOCATION_ID;
3328 x_departure_date := GET_TRIP_STOPS_REC.PLANNED_DEPARTURE_DATE;
3329 ELSE
3330 -- Need to find out if there is a way to go to last stop directly
3331 x_last_stop_id := GET_TRIP_STOPS_REC.STOP_ID;
3332 x_last_stop_loc_id := GET_TRIP_STOPS_REC.STOP_LOCATION_ID;
3333 x_arrival_date := GET_TRIP_STOPS_REC.PLANNED_ARRIVAL_DATE;
3334 END IF;
3335 l_idx := l_idx+1;
3336
3337 END LOOP;
3338
3339 IF l_debug_on
3340 THEN
3341 WSH_DEBUG_SV.logmsg(l_module_name,' First stop STOP_ID ' || x_first_stop_id,
3342 WSH_DEBUG_SV.C_PROC_LEVEL);
3343 WSH_DEBUG_SV.logmsg(l_module_name,' First stop Stop Loc Id ' || x_first_stop_loc_id,
3344 WSH_DEBUG_SV.C_PROC_LEVEL);
3345 WSH_DEBUG_SV.logmsg(l_module_name,' First stop departure date ' || x_departure_date,
3346 WSH_DEBUG_SV.C_PROC_LEVEL);
3347 WSH_DEBUG_SV.logmsg(l_module_name,' Last stop STOP_ID ' || x_last_stop_id,
3348 WSH_DEBUG_SV.C_PROC_LEVEL);
3349 WSH_DEBUG_SV.logmsg(l_module_name,' Last stop Stop loc id ' || x_last_stop_loc_id,
3350 WSH_DEBUG_SV.C_PROC_LEVEL);
3351 WSH_DEBUG_SV.logmsg(l_module_name,' Last stop arrival date ' || x_arrival_date,
3352 WSH_DEBUG_SV.C_PROC_LEVEL);
3353 END IF;
3354
3355
3356
3357 IF GET_TRIP_STOPS%ISOPEN THEN
3358 CLOSE GET_TRIP_STOPS;
3359 END IF;
3360
3361
3362 IF l_debug_on THEN
3363 WSH_DEBUG_SV.pop(l_module_name);
3364 END IF;
3365
3366 --}
3367 EXCEPTION
3368 --{
3369 WHEN FND_API.G_EXC_ERROR THEN
3370 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3371 IF l_debug_on THEN
3372 WSH_DEBUG_SV.pop(l_module_name);
3373 END IF;
3374
3375 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3376 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3377 IF l_debug_on THEN
3378 WSH_DEBUG_SV.pop(l_module_name);
3379 END IF;
3380 WHEN OTHERS THEN
3381 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3382
3383 IF l_debug_on THEN
3384 WSH_DEBUG_SV.pop(l_module_name);
3385 END IF;
3386
3387
3388
3389 END GET_FIRST_LAST_STOP_INFO;
3390
3391
3392 END FTE_MLS_UTIL;