[Home] [Help]
PACKAGE BODY: APPS.FTE_WSH_INTERFACE_PKG
Source
1 PACKAGE BODY FTE_WSH_INTERFACE_PKG AS
2 /* $Header: FTEWSHIB.pls 120.5 2005/07/28 17:07:24 nltan ship $ */
3 --{
4 G_PREVIOUS CONSTANT VARCHAR2(30) := 'PREVIOUS';
5 G_NEXT CONSTANT VARCHAR2(30) := 'NEXT';
6 G_BOTH CONSTANT VARCHAR2(30) := 'BOTH';
7 --
8 -- These constants indicate position of a stop before insert/update/delete.
9 --
10 G_FIRST CONSTANT VARCHAR2(30) := 'FIRST';
11 G_LAST CONSTANT VARCHAR2(30) := 'LAST';
12 G_INTERMEDIATE CONSTANT VARCHAR2(30) := 'INTERMEDIATE';
13 G_NONE CONSTANT VARCHAR2(30) := 'NONE'; -- meaningful for insert opeation
14 --
15 --
16 TYPE stop_validation_ctrl_rec_type
17 IS
18 RECORD
19 (
20 LOCATION_LANE_CHECK BOOLEAN DEFAULT FALSE,
21 DATETIME_SCHEDULE_CHECK BOOLEAN DEFAULT FALSE,
22 SEGMENT_CONNECTED_CHECK BOOLEAN DEFAULT FALSE,
23 DLVY_IN_TRANSIT_CHECK BOOLEAN DEFAULT FALSE
24 );
25 --
26 --
27 --
28 --
29 TYPE Trip_Stop_Rec_Type
30 IS
31 RECORD
32 (
33 STOP_ID NUMBER DEFAULT NULL,
34 STOP_LOCATION_ID NUMBER DEFAULT NULL,
35 STATUS_CODE VARCHAR2(30) DEFAULT NULL,
36 STOP_SEQUENCE_NUMBER NUMBER DEFAULT NULL,
37 PLANNED_ARRIVAL_DATE DATE DEFAULT NULL,
38 PLANNED_DEPARTURE_DATE DATE DEFAULT NULL,
39 ACTION_TYPE VARCHAR2(30) DEFAULT G_ADD
40 );
41 --
42 --
43 TYPE Trip_Stop_tab_Type
44 IS
45 TABLE OF trip_stop_rec_type INDEX BY BINARY_INTEGER;
46 --
47 --
48 FUNCTION get_stop_full_validn_ctrl_rec
49 RETURN stop_validation_ctrl_rec_type
50 IS
51 l_stop_validation_ctrl_rec stop_validation_ctrl_rec_type;
52 BEGIN
53 --{
54 l_stop_validation_ctrl_rec.LOCATION_LANE_CHECK := TRUE;
55 l_stop_validation_ctrl_rec.DATETIME_SCHEDULE_CHECK := TRUE;
56 l_stop_validation_ctrl_rec.SEGMENT_CONNECTED_CHECK := TRUE;
57 l_stop_validation_ctrl_rec.DLVY_IN_TRANSIT_CHECK := TRUE;
58 --
59 --
60 RETURN(l_stop_validation_ctrl_rec);
61 --}
62 END get_stop_full_validn_ctrl_rec;
63 --
64 --
65 PROCEDURE get_segment_stops
66 (
67 P_trip_segment_rec IN WSH_TRIPS_PVT.Trip_Rec_Type,
68 p_current_stop_rec IN WSH_TRIP_STOPS_PVT.trip_stop_rec_type,
69 p_action_type IN VARCHAR2,
70 x_trip_stop_tab OUT NOCOPY trip_stop_tab_type,
71 x_current_stop_index OUT NOCOPY NUMBER,
72 x_curr_stop_old_position OUT NOCOPY VARCHAR2,
73 x_schedule_start_datetime OUT NOCOPY DATE,
74 x_schedule_end_datetime OUT NOCOPY DATE,
75 X_return_status OUT NOCOPY VARCHAR2
76 )
77 IS
78 --{
79 l_trip_stop_tab Trip_Stop_tab_Type;
80 l_trip_stop_rec Trip_Stop_rec_Type;
81 l_current_trip_stop_rec Trip_Stop_rec_Type;
82 --
83 --
84 l_current_stop_id NUMBER := 0;
85 l_old_first_stop_id NUMBER := 0;
86 l_old_last_stop_id NUMBER := 0;
87 l_trip_stop_tab_count NUMBER := 0;
88 l_curr_stop_old_position VARCHAR2(30);
89 --
90 --
91 CURSOR get_segment_stops_cur
92 (
93 p_segment_id IN NUMBER
94 )
95 IS
96 SELECT
97 STOP_ID,
98 STOP_LOCATION_ID,
99 STOP_SEQUENCE_NUMBER,
100 PLANNED_ARRIVAL_DATE,
101 PLANNED_DEPARTURE_DATE,
102 G_NO_CHANGE ACTION_TYPE
103 FROM wsh_trip_stops
104 WHERE trip_id = p_segment_id
105 UNION ALL
106 SELECT
107 l_current_stop_id stop_id,
108 p_current_stop_rec.STOP_LOCATION_ID stop_location_id,
109 p_current_stop_rec.STOP_SEQUENCE_NUMBER stop_sequencE_number,
110 p_current_stop_rec.PLANNED_ARRIVAL_DATE planned_arrival_date,
111 p_current_stop_rec.PLANNED_DEPARTURE_DATE planned_departure_date,
112 p_action_type action_type
113 FROM DUAL
114 order by 3;
115 --}
116 BEGIN
117 --{
118 l_current_stop_id := NVL(p_current_stop_rec.STOP_ID,FND_API.G_MISS_NUM);
119 l_curr_stop_old_position := G_NONE;
120 l_trip_stop_tab_count := 0;
121 l_old_first_stop_id := NULL;
122 l_old_last_stop_id := NULL;
123 --
124 --
125 FOR get_segment_stops_rec IN get_segment_stops_cur
126 (
127 p_segment_id => p_trip_segment_rec.trip_id
128 )
129 LOOP
130 --{
131 l_trip_stop_Rec.stop_id := get_segment_stops_rec.stop_id;
132 l_trip_stop_Rec.stop_location_id := get_segment_stops_rec.stop_location_id;
133 l_trip_stop_Rec.stop_sequence_number := get_segment_stops_rec.stop_sequence_number;
134 l_trip_stop_Rec.planned_departure_date := get_segment_stops_rec.planned_departure_date;
135 l_trip_stop_Rec.planned_arrival_date := get_segment_stops_rec.planned_arrival_date;
136 l_trip_stop_Rec.action_type := get_segment_stops_rec.action_type;
137 --
138 --
139 IF l_trip_stop_rec.stop_id = l_current_stop_id
140 AND l_trip_stop_rec.action_type = G_NO_CHANGE
141 THEN
142 --{
143 l_curr_stop_old_position := G_INTERMEDIATE;
144 --}
145 ELSE
146 --{
147 l_trip_stop_tab_count := l_trip_stop_tab_count + 1;
148 l_trip_stop_tab(l_trip_stop_tab_count) := l_trip_stop_rec;
149 --}
150 END IF;
151 --
152 --
153 IF l_trip_stop_rec.action_type = G_NO_CHANGE
154 THEN
155 --{
156 l_old_last_stop_id := l_trip_stop_rec.stop_id;
157 x_schedule_end_datetime := l_trip_stop_rec.planned_arrival_date;
158 --
159 --
160 IF l_old_first_stop_id IS NULL
161 THEN
162 l_old_first_stop_id := l_trip_stop_rec.stop_id;
163 x_schedule_start_datetime := l_trip_stop_rec.planned_departure_date;
164 END IF;
165 --}
166 END IF;
167 --
168 --
169 IF l_trip_stop_rec.stop_id = l_current_stop_id
170 AND l_trip_stop_rec.action_type <> G_NO_CHANGE
171 THEN
172 --{
173 x_current_stop_index := l_trip_stop_tab_count;
174 --}
175 END IF;
176 --}
177 END LOOP;
178 --
179 --
180 IF l_current_stop_id = l_old_first_stop_id
181 THEN
182 l_curr_stop_old_position := G_FIRST;
183 ELSIF l_current_stop_id = l_old_last_stop_id
184 THEN
185 l_curr_stop_old_position := G_LAST;
186 END IF;
187 --
188 --
189 x_curr_stop_old_position := l_curr_stop_old_position;
190 x_trip_stop_tab := l_trip_stop_tab;
191 --
192 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
193 --}
194 EXCEPTION
195 --{
196 WHEN OTHERS THEN
197 wsh_util_core.default_handler('FTE_WSH_INTERFACE_PKG.GET_SEGMENT_STOPS');
198 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
199 --}
200 END get_segment_stops;
201 --
202 --
203 --
204 PROCEDURE validate_first_stop
205 (
206 P_trip_segment_rec IN WSH_TRIPS_PVT.Trip_Rec_Type,
207 P_current_stop_rec IN trip_stop_rec_type,
208 p_stop_validation_ctrl_rec IN stop_validation_ctrl_rec_type,
209 p_schedule_start_datetime IN DATE,
210 p_schedule_end_datetime IN DATE,
211 X_return_status OUT NOCOPY VARCHAR2
212 )
213 IS
214 --{
215 l_return_status VARCHAR2(32767);
216 l_msg_count NUMBER;
217 l_msg_data VARCHAR2(32767);
218 --
219 --
220 l_trip_count NUMBER;
221 l_dep_match_flag NUMBER;
222 l_arr_match_flag NUMBER;
223 l_lane_valid_flag VARCHAR2(32767);
224 l_connected BOOLEAN;
225 --
226 --
227 CURSOR get_segment_trips_cur
228 IS
229 SELECT b.fte_trip_id, sequence_number, b.name fte_trip_name
230 FROM fte_wsh_trips a, fte_trips b
231 WHERE a.wsh_trip_id = p_trip_segment_rec.trip_id
232 AND a.fte_trip_id = b.fte_trip_id;
233 --
234 --
235 l_number_of_errors NUMBER := 0;
236 l_number_of_warnings NUMBER := 0;
237 --
238 --
239 --}
240 BEGIN
241 --{
242 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
243 l_number_of_errors := 0;
244 l_number_of_warnings := 0;
245 --
246 --
247 IF p_trip_segment_rec.schedule_id IS NOT NULL
248 AND p_stop_validation_ctrl_rec.datetime_schedule_check
249 THEN
250 --{
251 /*
252 FTE_CAT_VALIDATE_PKG.Validate_Schedule_Date
253 (
254 p_lane_id => p_trip_segment_rec.lane_id,
255 p_schedule_id => p_trip_segment_rec.schedule_id,
256 p_departure_date => p_current_stop_rec.planned_departure_date,
257 p_arrival_date => p_current_stop_rec.planned_arrival_date,
258 x_return_status => l_return_status,
259 x_dep_match_flag => l_dep_match_flag,
260 x_arr_match_flag => l_arr_match_flag
261 );
262 --
263 --
264 FTE_MLS_UTIL.api_post_call
265 (
266 p_api_name => 'FTE_CAT_VALIDATE_PKG.Validate_Schedule_Date',
267 p_api_return_status => l_return_status,
268 p_message_name => 'FTE_SEGMENT_STOP_UNEXP_ERROR',
269 p_trip_segment_id => p_trip_segment_rec.trip_id,
270 p_trip_segment_name => p_trip_segment_rec.name,
271 p_trip_stop_id => p_current_stop_rec.stop_id,
272 p_stop_seq_number => p_current_stop_rec.stop_sequence_number,
273 x_number_of_errors => l_number_of_errors,
274 x_number_of_warnings => l_number_of_warnings,
275 x_return_status => x_return_status
276 );
277 --
278 --
279 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
280 THEN
281 --{
282 RETURN;
283 --}
284 END IF;
285 */
286 --
287 --
288 --IF l_dep_match_flag <> FTE_CAT_VALIDATE_PKG.G_MATCH_WITH_DEP_DATE
289 IF p_current_stop_rec.planned_departure_date
290 <> p_schedule_start_datetime
291 THEN
292 --{
293 FND_MESSAGE.SET_NAME('FTE', 'FTE_FIRST_STOP_DATE_MATCH_ERR');
294 FND_MESSAGE.SET_TOKEN('TRIP_SEGMENT_NAME', p_trip_segment_rec.name);
295 FND_MESSAGE.SET_TOKEN('SCHED_DEPARTURE_DATE', fnd_date.date_to_displayDT(p_schedule_start_datetime));
296 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
297 l_number_of_errors := l_number_of_errors + 1;
298 --}
299 END IF;
300 --}
301 END IF;
302 --
303 --
304 IF p_trip_segment_rec.lane_id IS NOT NULL
305 AND p_stop_validation_ctrl_rec.location_lane_check
306 THEN
307 --{
308 FTE_CAT_VALIDATE_PKG.Validate_Loc_To_Region
309 (
310 p_lane_id => p_trip_segment_rec.lane_id,
311 p_location_id => p_current_stop_rec.stop_location_id,
312 p_search_criteria => 'O',
313 x_return_status => l_return_status,
314 x_valid_flag => l_lane_valid_flag
315 );
316 --
317 --
318 FTE_MLS_UTIL.api_post_call
319 (
320 p_api_name => 'FTE_CAT_VALIDATE_PKG.Validate_Loc_to_Region',
321 p_api_return_status => l_return_status,
322 p_message_name => 'FTE_SEGMENT_STOP_UNEXP_ERROR',
323 p_trip_segment_id => p_trip_segment_rec.trip_id,
324 p_trip_segment_name => p_trip_segment_rec.name,
325 p_trip_stop_id => p_current_stop_rec.stop_id,
326 p_stop_seq_number => p_current_stop_rec.stop_sequence_number,
327 x_number_of_errors => l_number_of_errors,
328 x_number_of_warnings => l_number_of_warnings,
329 x_return_status => x_return_status
330 );
331 --
332 --
333 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
334 THEN
335 --{
336 RETURN;
337 --}
338 END IF;
339 --
340 --
341 IF l_lane_valid_flag = 'N'
342 THEN
343 --{
344 FND_MESSAGE.SET_NAME('FTE', 'FTE_STOP_LOCN_LANE_ORIG_ERR');
345 FND_MESSAGE.SET_TOKEN('TRIP_SEGMENT_NAME', p_trip_segment_rec.name);
346 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
347 l_number_of_errors := l_number_of_errors + 1;
348 --}
349 END IF;
350 --}
351 END IF;
352 --
353 --
354 --{
355 l_trip_count := 0;
356 --
357 --
358 --
359 -- FOLLOWING IF CLAUSE SHOULD BE COMMENTED IF
360 -- CODE TO CHECK SEGMENT_HAS_INTRANSIT_DELIVERIES IS UNCOMMENTED.
361 -- ONCE, THIS IF CLAUSE IS COMMENTED, ANOTHER IF INSIDE THE LOOP,
362 -- WILL PERFORM THE NECESSARY CHECK.
363 --
364 --
365 IF p_stop_validation_ctrl_rec.segment_connected_check
366 THEN
367 --{
368 FOR get_segment_trips_rec IN get_segment_trips_cur
369 LOOP
370 --{
371 l_trip_count := l_trip_count + 1;
372 --
373 --
374 IF p_stop_validation_ctrl_rec.segment_connected_check
375 THEN
376 --{
377 fte_mls_util.check_previous_segment
378 (
379 p_trip_id => get_segment_trips_rec.fte_trip_id,
380 P_trip_segment_rec => p_trip_segment_rec,
381 p_sequence_number => get_segment_trips_rec.sequence_number,
382 p_first_stop_location_id => p_current_stop_rec.stop_location_id,
383 x_trip_name => get_segment_trips_rec.fte_trip_name,
384 x_connected => l_connected,
385 x_return_status => l_return_status
386 );
387 --
388 --
389 FTE_MLS_UTIL.api_post_call
390 (
391 p_api_name => 'FTE_MLS_UTIL.CHECK_PREVIOUS_SEGMENT',
392 p_api_return_status => l_return_status,
393 p_message_name => 'FTE_TRIP_SEG_STOP_UNEXP_ERROR',
394 p_trip_segment_id => p_trip_segment_rec.trip_id,
395 p_trip_segment_name => p_trip_segment_rec.name,
396 p_trip_stop_id => p_current_stop_rec.stop_id,
397 p_stop_seq_number => p_current_stop_rec.stop_sequence_number,
398 p_trip_id => get_segment_trips_rec.fte_trip_id,
399 p_trip_name => get_segment_trips_rec.fte_trip_name,
400 x_number_of_errors => l_number_of_errors,
401 x_number_of_warnings => l_number_of_warnings,
402 x_return_status => x_return_status
403 );
404 --
405 --
406 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
407 THEN
408 --{
409 RETURN;
410 --}
411 END IF;
412 --
413 --
414 IF NOT(l_connected)
415 THEN
416 --{
417 FND_MESSAGE.SET_NAME('FTE', 'FTE_SEGMENT_PREV_CONNECT_ERROR');
418 FND_MESSAGE.SET_TOKEN('TRIP_SEGMENT_NAME', p_trip_segment_rec.name);
419 FND_MESSAGE.SET_TOKEN('TRIP_NAME', get_segment_trips_rec.fte_trip_name);
420 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_WARNING);
421 l_number_of_warnings := l_number_of_warnings + 1;
422 --
423 --
424 UPDATE fte_trips
425 SET validation_required = 'Y'
426 WHERE fte_trip_id = get_segment_trips_rec.fte_trip_id;
427 --}
428 END IF;
429 --}
430 END IF;
431 --}
432 END LOOP;
433 --}
434 END IF;
435 --
436 --
437 /* Please see the comment above the previous IF Clause.
438 --
439 IF l_trip_count > 0
440 AND p_stop_validation_ctrl_rec.dlvy_in_transit_check
441 THEN
442 --{
443 IF segment_has_intransit_dlvy( P_trip_segment_rec => p_trip_segment_rec )
444 THEN
445 FND_MESSAGE.SET_NAME('FTE', 'FTE_SEGMENT_DLVY_INTRANSIT_ERR');
446 FND_MESSAGE.SET_TOKEN('TRIP_SEGMENT_NAME', p_trip_segment_rec.name);
447 FND_MESSAGE.SET_TOKEN('TRIP_NAME', get_segment_trips_rec.fte_trip_name);
448 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_WARNING);
449 l_number_of_warnings := l_number_of_warnings + 1;
450 END IF;
451 --}
452 END IF;
453 */
454 --
455 --
456 --}
457 --END IF;
458 --
459 --
460 IF l_number_of_errors > 0
461 THEN
462 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
463 ELSIF l_number_of_warnings > 0
464 THEN
465 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
466 ELSE
467 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
468 END IF;
469 --}
470 EXCEPTION
471 --{
472 WHEN OTHERS THEN
473 wsh_util_core.default_handler('FTE_WSH_INTERFACE_PKG.VALIDATE_FIRST_STOP');
474 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
475 --}
476 END validate_first_stop;
477 --
478 --
479 PROCEDURE validate_last_stop
480 (
481 P_trip_segment_rec IN WSH_TRIPS_PVT.Trip_Rec_Type,
482 P_current_stop_rec IN trip_stop_rec_type,
483 p_stop_validation_ctrl_rec IN stop_validation_ctrl_rec_type,
484 p_schedule_start_datetime IN DATE,
485 p_schedule_end_datetime IN DATE,
486 X_return_status OUT NOCOPY VARCHAR2
487 )
488 IS
489 --{
490 l_return_status VARCHAR2(32767);
491 l_msg_count NUMBER;
492 l_msg_data VARCHAR2(32767);
493 --
494 --
495 l_trip_count NUMBER;
496 l_dep_match_flag NUMBER;
497 l_arr_match_flag NUMBER;
498 l_lane_valid_flag VARCHAR2(32767);
499 l_connected BOOLEAN;
500 --
501 --
502 CURSOR get_segment_trips_cur
503 IS
504 SELECT b.fte_trip_id, sequence_number, b.name fte_trip_name
505 FROM fte_wsh_trips a, fte_trips b
506 WHERE a.wsh_trip_id = p_trip_segment_rec.trip_id
507 AND a.fte_trip_id = b.fte_trip_id;
508 --
509 --
510 l_number_of_errors NUMBER := 0;
511 l_number_of_warnings NUMBER := 0;
512 --
513 --
514 --}
515 BEGIN
516 --{
517 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
518 l_number_of_errors := 0;
519 l_number_of_warnings := 0;
520 --
521 --
522 IF p_trip_segment_rec.schedule_id IS NOT NULL
523 AND p_stop_validation_ctrl_rec.datetime_schedule_check
524 THEN
525 --{
526 /*
527 FTE_CAT_VALIDATE_PKG.Validate_Schedule_Date
528 (
529 p_lane_id => p_trip_segment_rec.lane_id,
530 p_schedule_id => p_trip_segment_rec.schedule_id,
531 p_departure_date => p_current_stop_rec.planned_departure_date,
532 p_arrival_date => p_current_stop_rec.planned_arrival_date,
533 x_return_status => l_return_status,
534 x_dep_match_flag => l_dep_match_flag,
535 x_arr_match_flag => l_arr_match_flag
536 );
537 --
538 --
539 FTE_MLS_UTIL.api_post_call
540 (
541 p_api_name => 'FTE_CAT_VALIDATE_PKG.Validate_Schedule_Date',
542 p_api_return_status => l_return_status,
543 p_message_name => 'FTE_SEGMENT_STOP_UNEXP_ERROR',
544 p_trip_segment_id => p_trip_segment_rec.trip_id,
545 p_trip_segment_name => p_trip_segment_rec.name,
546 p_trip_stop_id => p_current_stop_rec.stop_id,
547 p_stop_seq_number => p_current_stop_rec.stop_sequence_number,
548 x_number_of_errors => l_number_of_errors,
549 x_number_of_warnings => l_number_of_warnings,
550 x_return_status => x_return_status
551 );
552 --
553 --
554 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
555 THEN
556 --{
557 RETURN;
558 --}
559 END IF;
560 */
561 --
562 --
563 --IF l_arr_match_flag <> FTE_CAT_VALIDATE_PKG.G_MATCH_WITH_ARR_DATE
564 IF p_current_stop_rec.planned_arrival_date
565 <> p_schedule_end_datetime
566 THEN
567 --{
568 FND_MESSAGE.SET_NAME('FTE', 'FTE_LAST_STOP_DATE_MATCH_ERR');
569 FND_MESSAGE.SET_TOKEN('TRIP_SEGMENT_NAME', p_trip_segment_rec.name);
570 FND_MESSAGE.SET_TOKEN('SCHED_ARRIVAL_DATE', fnd_date.date_to_displayDT(p_schedule_end_datetime));
571 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
572 l_number_of_errors := l_number_of_errors + 1;
573 --}
574 END IF;
575 --}
576 END IF;
577 --
578 --
579 IF p_trip_segment_rec.lane_id IS NOT NULL
580 AND p_stop_validation_ctrl_rec.location_lane_check
581 THEN
582 --{
583 FTE_CAT_VALIDATE_PKG.Validate_Loc_To_Region
584 (
585 p_lane_id => p_trip_segment_rec.lane_id,
586 p_location_id => p_current_stop_rec.stop_location_id,
587 p_search_criteria => 'D',
588 x_return_status => l_return_status,
589 x_valid_flag => l_lane_valid_flag
590 );
591 --
592 --
593 FTE_MLS_UTIL.api_post_call
594 (
595 p_api_name => 'FTE_CAT_VALIDATE_PKG.Validate_Loc_to_Region',
596 p_api_return_status => l_return_status,
597 p_message_name => 'FTE_SEGMENT_STOP_UNEXP_ERROR',
598 p_trip_segment_id => p_trip_segment_rec.trip_id,
599 p_trip_segment_name => p_trip_segment_rec.name,
600 p_trip_stop_id => p_current_stop_rec.stop_id,
601 p_stop_seq_number => p_current_stop_rec.stop_sequence_number,
602 x_number_of_errors => l_number_of_errors,
603 x_number_of_warnings => l_number_of_warnings,
604 x_return_status => x_return_status
605 );
606 --
607 --
608 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
609 THEN
610 --{
611 RETURN;
612 --}
613 END IF;
614 --
615 --
616 l_lane_valid_flag := 'Y';
617 IF l_lane_valid_flag = 'N'
618 THEN
619 --{
620 FND_MESSAGE.SET_NAME('FTE', 'FTE_STOP_LOCN_LANE_DEST_ERR');
621 FND_MESSAGE.SET_TOKEN('TRIP_SEGMENT_NAME', p_trip_segment_rec.name);
622 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
623 l_number_of_errors := l_number_of_errors + 1;
624 --}
625 END IF;
626 --}
627 END IF;
628 --
629 --
630 --{
631 l_trip_count := 0;
632 --
633 --
634 -- FOLLOWING IF CLAUSE SHOULD BE COMMENTED IF
635 -- CODE TO CHECK SEGMENT_HAS_INTRANSIT_DELIVERIES IS UNCOMMENTED.
636 -- ONCE, THIS IF CLAUSE IS COMMENTED, ANOTHER IF INSIDE THE LOOP,
637 -- WILL PERFORM THE NECESSARY CHECK.
638 --
639 --
640 IF p_stop_validation_ctrl_rec.segment_connected_check
641 THEN
642 --{
643 FOR get_segment_trips_rec IN get_segment_trips_cur
644 LOOP
645 --{
646 l_trip_count := l_trip_count + 1;
647 --
648 --
649 IF p_stop_validation_ctrl_rec.segment_connected_check
650 THEN
651 --{
652 fte_mls_util.check_next_segment
653 (
654 p_trip_id => get_segment_trips_rec.fte_trip_id,
655 P_trip_segment_rec => p_trip_segment_rec,
656 p_sequence_number => get_segment_trips_rec.sequence_number,
657 p_last_stop_location_id => p_current_stop_rec.stop_location_id,
658 x_trip_name => get_segment_trips_rec.fte_trip_name,
659 x_connected => l_connected,
660 x_return_status => l_return_status
661 );
662 --
663 --
664 FTE_MLS_UTIL.api_post_call
665 (
666 p_api_name => 'FTE_MLS_UTIL.CHECK_NEXT_SEGMENT',
667 p_api_return_status => l_return_status,
668 p_message_name => 'FTE_TRIP_SEG_STOP_UNEXP_ERROR',
669 p_trip_segment_id => p_trip_segment_rec.trip_id,
670 p_trip_segment_name => p_trip_segment_rec.name,
671 p_trip_stop_id => p_current_stop_rec.stop_id,
672 p_stop_seq_number => p_current_stop_rec.stop_sequence_number,
673 p_trip_id => get_segment_trips_rec.fte_trip_id,
674 p_trip_name => get_segment_trips_rec.fte_trip_name,
675 x_number_of_errors => l_number_of_errors,
676 x_number_of_warnings => l_number_of_warnings,
677 x_return_status => x_return_status
678 );
679 --
680 --
681 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
682 THEN
683 --{
684 RETURN;
685 --}
686 END IF;
687 --
688 --
689 IF NOT(l_connected)
690 THEN
691 --{
692 FND_MESSAGE.SET_NAME('FTE', 'FTE_SEGMENT_NEXT_CONNECT_ERROR');
693 FND_MESSAGE.SET_TOKEN('TRIP_SEGMENT_NAME', p_trip_segment_rec.name);
694 FND_MESSAGE.SET_TOKEN('TRIP_NAME', get_segment_trips_rec.fte_trip_name);
695 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_WARNING);
696 l_number_of_warnings := l_number_of_warnings + 1;
697 --
698 --
699 UPDATE fte_trips
700 SET validation_required = 'Y'
701 WHERE fte_trip_id = get_segment_trips_rec.fte_trip_id;
702 --}
703 END IF;
704 --}
705 END IF;
706 --}
707 END LOOP;
708 --}
709 END IF;
710 --
711 --
712 /* Please see the comment above the previous IF Clause.
713 --
714 IF l_trip_count > 0
715 AND p_stop_validation_ctrl_rec.dlvy_in_transit_check
716 THEN
717 --{
718 IF segment_has_intransit_dlvy( P_trip_segment_rec => p_trip_segment_rec )
719 THEN
720 FND_MESSAGE.SET_NAME('FTE', 'FTE_SEGMENT_DLVY_INTRANSIT_ERR');
721 FND_MESSAGE.SET_TOKEN('TRIP_SEGMENT_NAME', p_trip_segment_rec.name);
722 FND_MESSAGE.SET_TOKEN('TRIP_NAME', get_segment_trips_rec.fte_trip_name);
723 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_WARNING);
724 l_number_of_warnings := l_number_of_warnings + 1;
725 END IF;
726 --}
727 END IF;
728 */
729 --
730 --
731 --}
732 --END IF;
733 --
734 --
735 IF l_number_of_errors > 0
736 THEN
737 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
738 ELSIF l_number_of_warnings > 0
739 THEN
740 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
741 ELSE
742 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
743 END IF;
744 --}
745 EXCEPTION
746 --{
747 WHEN OTHERS THEN
748 wsh_util_core.default_handler('FTE_WSH_INTERFACE_PKG.VALIDATE_LAST_STOP');
749 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
750 --}
751 END validate_last_stop;
752 --
753 --
754 PROCEDURE validate_intermediate_stop
755 (
756 P_trip_segment_rec IN WSH_TRIPS_PVT.Trip_Rec_Type,
757 P_current_stop_rec IN Trip_Stop_Rec_Type,
758 p_stop_validation_ctrl_rec IN stop_validation_ctrl_rec_type,
759 p_schedule_start_datetime IN DATE,
760 p_schedule_end_datetime IN DATE,
761 X_return_status OUT NOCOPY VARCHAR2
762 )
763 IS
764 --{
765 l_return_status VARCHAR2(32767);
766 l_msg_count NUMBER;
767 l_msg_data VARCHAR2(32767);
768 --
769 --
770 l_dep_match_flag NUMBER;
771 l_arr_match_flag NUMBER;
772 l_lane_valid_flag VARCHAR2(32767);
773 --
774 --
775 l_number_of_errors NUMBER := 0;
776 l_number_of_warnings NUMBER := 0;
777 --
778 --
779 --}
780 BEGIN
781 --{
782 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
783 l_number_of_errors := 0;
784 l_number_of_warnings := 0;
785 --
786 --
787 IF p_trip_segment_rec.schedule_id IS NOT NULL
788 AND p_stop_validation_ctrl_rec.datetime_schedule_check
789 THEN
790 --{
791 /*
792 FTE_CAT_VALIDATE_PKG.Validate_Schedule_Date
793 (
794 p_lane_id => p_trip_segment_rec.lane_id,
795 p_schedule_id => p_trip_segment_rec.schedule_id,
796 p_departure_date => p_current_stop_rec.planned_departure_date,
797 p_arrival_date => p_current_stop_rec.planned_arrival_date,
798 x_return_status => l_return_status,
799 x_dep_match_flag => l_dep_match_flag,
800 x_arr_match_flag => l_arr_match_flag
801 );
802 --
803 --
804 FTE_MLS_UTIL.api_post_call
805 (
806 p_api_name => 'FTE_CAT_VALIDATE_PKG.Validate_Schedule_Date',
807 p_api_return_status => l_return_status,
808 p_message_name => 'FTE_SEGMENT_STOP_UNEXP_ERROR',
809 p_trip_segment_id => p_trip_segment_rec.trip_id,
810 p_trip_segment_name => p_trip_segment_rec.name,
811 p_trip_stop_id => p_current_stop_rec.stop_id,
812 p_stop_seq_number => p_current_stop_rec.stop_sequence_number,
813 x_number_of_errors => l_number_of_errors,
814 x_number_of_warnings => l_number_of_warnings,
815 x_return_status => x_return_status
816 );
817 --
818 --
819 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
820 THEN
821 --{
822 RETURN;
823 --}
824 END IF;
825 */
826 --
827 --
828 --IF l_dep_match_flag <> FTE_CAT_VALIDATE_PKG.G_BETWEEN_DATES
829 IF p_current_stop_rec.planned_departure_date
830 NOT BETWEEN p_schedule_start_datetime
831 AND p_schedule_end_datetime
832 THEN
833 --{
834 FND_MESSAGE.SET_NAME('FTE', 'FTE_STOP_DEP_DATE_ERR');
835 FND_MESSAGE.SET_TOKEN('TRIP_SEGMENT_NAME', p_trip_segment_rec.name);
836 FND_MESSAGE.SET_TOKEN('STOP_SEQUENCE_NUMBER', p_current_stop_rec.stop_sequence_number);
837 FND_MESSAGE.SET_TOKEN('SCHED_DEPARTURE_DATE', fnd_date.date_to_displayDT(p_schedule_start_datetime));
838 FND_MESSAGE.SET_TOKEN('SCHED_ARRIVAL_DATE', fnd_date.date_to_displayDT(p_schedule_end_datetime));
839 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_WARNING);
840 l_number_of_warnings := l_number_of_warnings + 1;
841 --
842 -- Not setting as error due to timezone issues.
843 -- It will be a warning until timezone issues are resolved.
844 --
845 --WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
846 --l_number_of_errors := l_number_of_errors + 1;
847 --}
848 END IF;
849 --
850 --
851 --IF l_arr_match_flag <> FTE_CAT_VALIDATE_PKG.G_BETWEEN_DATES
852 IF p_current_stop_rec.planned_arrival_date
853 NOT BETWEEN p_schedule_start_datetime
854 AND p_schedule_end_datetime
855 THEN
856 --{
857 FND_MESSAGE.SET_NAME('FTE', 'FTE_STOP_ARR_DATE_ERR');
858 FND_MESSAGE.SET_TOKEN('TRIP_SEGMENT_NAME', p_trip_segment_rec.name);
859 FND_MESSAGE.SET_TOKEN('STOP_SEQUENCE_NUMBER', p_current_stop_rec.stop_sequence_number);
860 FND_MESSAGE.SET_TOKEN('SCHED_DEPARTURE_DATE', fnd_date.date_to_displayDT(p_schedule_start_datetime));
861 FND_MESSAGE.SET_TOKEN('SCHED_ARRIVAL_DATE', fnd_date.date_to_displayDT(p_schedule_end_datetime));
862 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_WARNING);
863 l_number_of_warnings := l_number_of_warnings + 1;
864 --
865 -- Not setting as error due to timezone issues.
866 -- It will be a warning until timezone issues are resolved.
867 --
868 --WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
869 --l_number_of_errors := l_number_of_errors + 1;
870 --}
871 END IF;
872 --}
873 END IF;
874 --
875 --
876 IF p_trip_segment_rec.lane_id IS NOT NULL
877 AND p_stop_validation_ctrl_rec.location_lane_check
878 THEN
879 --{
880 FTE_CAT_VALIDATE_PKG.Validate_Loc_To_Region
881 (
882 p_lane_id => p_trip_segment_rec.lane_id,
883 p_location_id => p_current_stop_rec.stop_location_id,
884 p_search_criteria => 'A',
885 x_return_status => l_return_status,
886 x_valid_flag => l_lane_valid_flag
887 );
888 --
889 --
890 FTE_MLS_UTIL.api_post_call
891 (
892 p_api_name => 'FTE_CAT_VALIDATE_PKG.Validate_loc_to_region',
893 p_api_return_status => l_return_status,
894 p_message_name => 'FTE_SEGMENT_STOP_UNEXP_ERROR',
895 p_trip_segment_id => p_trip_segment_rec.trip_id,
896 p_trip_segment_name => p_trip_segment_rec.name,
897 p_trip_stop_id => p_current_stop_rec.stop_id,
898 p_stop_seq_number => p_current_stop_rec.stop_sequence_number,
899 x_number_of_errors => l_number_of_errors,
900 x_number_of_warnings => l_number_of_warnings,
901 x_return_status => x_return_status
902 );
903 --
904 --
905 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
906 THEN
907 --{
908 RETURN;
909 --}
910 END IF;
911 --
912 --
913 IF l_lane_valid_flag = 'N'
914 THEN
915 --{
916 FND_MESSAGE.SET_NAME('FTE', 'FTE_STOP_LOCN_LANE_WARN');
917 FND_MESSAGE.SET_TOKEN('TRIP_SEGMENT_NAME', p_trip_segment_rec.name);
918 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_WARNING);
919 l_number_of_warnings := l_number_of_warnings + 1;
920 --}
921 END IF;
922 --}
923 END IF;
924 --
925 --
926 IF l_number_of_errors > 0
927 THEN
928 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
929 ELSIF l_number_of_warnings > 0
930 THEN
931 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
932 ELSE
933 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
934 END IF;
935 --}
936 EXCEPTION
937 --{
938 WHEN OTHERS THEN
939 wsh_util_core.default_handler('FTE_WSH_INTERFACE_PKG.VALIDATE_INTERMEDIATE_STOP');
940 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
941 --}
942 END validate_intermediate_stop;
943 --
944 --
945 PROCEDURE validate_stop_add
946 (
947 P_trip_segment_rec IN WSH_TRIPS_PVT.Trip_Rec_Type,
948 P_trip_stop_tab IN Trip_Stop_Tab_type,
949 p_current_stop_index IN NUMBER,
950 p_stop_validation_ctrl_rec IN stop_validation_ctrl_rec_type,
951 p_schedule_start_datetime IN DATE,
952 p_schedule_end_datetime IN DATE,
953 X_return_status OUT NOCOPY VARCHAR2
954 )
955 IS
956 --{
957 --
958 --
959 l_trip_stop_rec trip_stop_rec_type;
960 --l_stop_validation_ctrl_rec stop_validation_ctrl_rec_type;
961 --
962 l_trip_count NUMBER;
963 l_return_status VARCHAR2(32767);
964 l_program_name VARCHAR2(32767);
965 --
966 --
967 l_first BOOLEAN := FALSE;
968 l_last BOOLEAN := FALSE;
969 --
970 --
971 l_number_of_errors NUMBER := 0;
972 l_number_of_warnings NUMBER := 0;
973 --
974 --
975 CURSOR get_segment_trips_cur
976 IS
977 SELECT b.fte_trip_id, sequence_number, b.name fte_trip_name
978 FROM fte_wsh_trips a, fte_trips b
979 WHERE a.wsh_trip_id = p_trip_segment_rec.trip_id
980 AND a.fte_trip_id = b.fte_trip_id;
981 --
982 --
983
984 --}
985 BEGIN
986 --{
987 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
988 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
989 l_number_of_errors := 0;
990 l_number_of_warnings := 0;
991 l_first := FALSE;
992 l_last := FALSE;
993 --
994 --
995 l_trip_stop_rec := p_trip_stop_tab(p_current_stop_index);
996 --
997 --
998 --l_stop_validation_ctrl_rec := get_stop_full_validation_ctrl_rec;
999 --
1000 --
1001 IF p_current_stop_index = p_trip_stop_tab.FIRST
1002 THEN
1003 --{
1004 l_first := TRUE;
1005 --
1006 validate_first_stop
1007 (
1008 P_trip_segment_rec => p_trip_segment_rec,
1009 P_current_stop_rec => l_trip_stop_rec,
1010 p_stop_validation_ctrl_rec => p_stop_validation_ctrl_rec,
1011 p_schedule_start_datetime => p_schedule_start_datetime,
1012 p_schedule_end_datetime => p_schedule_end_datetime,
1013 X_return_status => l_return_status
1014 );
1015 --
1016 --
1017 FTE_MLS_UTIL.api_post_call
1018 (
1019 p_api_name => 'FTE_WSH_INTERFACE_PKG.Validate_first_stop',
1020 p_api_return_status => l_return_status,
1021 p_message_name => 'FTE_SEGMENT_STOP_UNEXP_ERROR',
1022 p_trip_segment_id => p_trip_segment_rec.trip_id,
1023 p_trip_segment_name => p_trip_segment_rec.name,
1024 p_trip_stop_id => l_trip_stop_rec.stop_id,
1025 p_stop_seq_number => l_trip_stop_rec.stop_sequence_number,
1026 x_number_of_errors => l_number_of_errors,
1027 x_number_of_warnings => l_number_of_warnings,
1028 x_return_status => x_return_status
1029 );
1030 --
1031 --
1032 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
1033 THEN
1034 --{
1035 RETURN;
1036 --}
1037 END IF;
1038 --}
1039 END IF;
1040 --
1041 --
1042 IF p_current_stop_index = p_trip_stop_tab.LAST
1043 AND p_current_stop_index <> p_trip_stop_tab.FIRST
1044 THEN
1045 --{
1046 l_last := TRUE;
1047 --
1048 validate_last_stop
1049 (
1050 P_trip_segment_rec => p_trip_segment_rec,
1051 P_current_stop_rec => l_trip_stop_rec,
1052 p_stop_validation_ctrl_rec => p_stop_validation_ctrl_rec,
1053 p_schedule_start_datetime => p_schedule_start_datetime,
1054 p_schedule_end_datetime => p_schedule_end_datetime,
1055 X_return_status => l_return_status
1056 );
1057 --
1058 --
1059 FTE_MLS_UTIL.api_post_call
1060 (
1061 p_api_name => 'FTE_WSH_INTERFACE_PKG.Validate_last_stop',
1062 p_api_return_status => l_return_status,
1063 p_message_name => 'FTE_SEGMENT_STOP_UNEXP_ERROR',
1064 p_trip_segment_id => p_trip_segment_rec.trip_id,
1065 p_trip_segment_name => p_trip_segment_rec.name,
1066 p_trip_stop_id => l_trip_stop_rec.stop_id,
1067 p_stop_seq_number => l_trip_stop_rec.stop_sequence_number,
1068 x_number_of_errors => l_number_of_errors,
1069 x_number_of_warnings => l_number_of_warnings,
1070 x_return_status => x_return_status
1071 );
1072 --
1073 --
1074 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
1075 THEN
1076 --{
1077 RETURN;
1078 --}
1079 END IF;
1080 --}
1081 END IF;
1082 --
1083 --
1084 IF NOT(l_first) AND NOT(l_last)
1085 THEN
1086 --{
1087 validate_intermediate_stop
1088 (
1089 P_trip_segment_rec => p_trip_segment_rec,
1090 P_current_stop_rec => l_trip_stop_rec,
1091 p_stop_validation_ctrl_rec => p_stop_validation_ctrl_rec,
1092 p_schedule_start_datetime => p_schedule_start_datetime,
1093 p_schedule_end_datetime => p_schedule_end_datetime,
1094 X_return_status => l_return_status
1095 );
1096 --
1097 --
1098 FTE_MLS_UTIL.api_post_call
1099 (
1100 p_api_name => 'FTE_WSH_INTERFACE_PKG.Validate_intermediate_stop',
1101 p_api_return_status => l_return_status,
1102 p_message_name => 'FTE_SEGMENT_STOP_UNEXP_ERROR',
1103 p_trip_segment_id => p_trip_segment_rec.trip_id,
1104 p_trip_segment_name => p_trip_segment_rec.name,
1105 p_trip_stop_id => l_trip_stop_rec.stop_id,
1106 p_stop_seq_number => l_trip_stop_rec.stop_sequence_number,
1107 x_number_of_errors => l_number_of_errors,
1108 x_number_of_warnings => l_number_of_warnings,
1109 x_return_status => x_return_status
1110 );
1111 --
1112 --
1113 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
1114 THEN
1115 --{
1116 RETURN;
1117 --}
1118 END IF;
1119 --}
1120 END IF;
1121 --
1122 --
1123 IF l_number_of_errors > 0
1124 THEN
1125 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1126 ELSIF l_number_of_warnings > 0
1127 THEN
1128 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1129 ELSE
1130 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1131 END IF;
1132 --}
1133 EXCEPTION
1134 --{
1135 WHEN OTHERS THEN
1136 wsh_util_core.default_handler('FTE_WSH_INTERFACE_PKG.VALIDATE_STOP_ADD');
1137 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1138 --}
1139 END validate_stop_add;
1140 --
1141 --
1142 PROCEDURE validate_stop_update
1143 (
1144 P_trip_segment_rec IN WSH_TRIPS_PVT.Trip_Rec_Type,
1145 P_trip_stop_tab IN Trip_Stop_Tab_type,
1146 p_current_stop_index IN NUMBER,
1147 p_curr_stop_old_position IN VARCHAR2,
1148 p_old_stop_rec IN WSH_TRIP_STOPS_PVT.trip_stop_rec_type,
1149 p_schedule_start_datetime IN DATE,
1150 p_schedule_end_datetime IN DATE,
1151 x_return_status OUT NOCOPY VARCHAR2
1152 )
1153 IS
1154 --{
1155 --
1156 --
1157 l_new_stop_rec trip_stop_rec_type;
1158 l_validate_stop_rec trip_stop_rec_type;
1159 l_stop_validation_ctrl_rec stop_validation_ctrl_rec_type;
1160 l_stop_full_valid_ctrl_rec stop_validation_ctrl_rec_type;
1161 --
1162 l_validate_stop_index NUMBER;
1163 l_trip_count NUMBER;
1164 l_return_status VARCHAR2(32767);
1165 l_validation_required BOOLEAN := FALSE;
1166 --
1167 --
1168 l_number_of_errors NUMBER := 0;
1169 l_number_of_warnings NUMBER := 0;
1170 --
1171 --
1172 CURSOR pickup_deliveries_cur (p_stop_id NUMBER) IS
1173 SELECT
1174 dg.delivery_id, dl.name
1175 FROM wsh_new_deliveries dl,
1176 wsh_delivery_legs dg,
1177 wsh_trip_stops st
1178 WHERE dg.delivery_id = dl.delivery_id
1179 AND st.stop_location_id = dl.initial_pickup_location_id
1180 AND st.stop_id = dg.pick_up_stop_id
1181 AND st.stop_id = p_stop_id;
1182 --
1183 --
1184 CURSOR get_segment_trips_cur
1185 IS
1186 SELECT b.fte_trip_id, sequence_number, b.name fte_trip_name
1187 FROM fte_wsh_trips a, fte_trips b
1188 WHERE a.wsh_trip_id = p_trip_segment_rec.trip_id
1189 AND a.fte_trip_id = b.fte_trip_id;
1190 --
1191 --}
1192 BEGIN
1193 --{
1194 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1195 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1196 l_number_of_errors := 0;
1197 l_number_of_warnings := 0;
1198 --
1199 --
1200 l_new_stop_rec := p_trip_stop_tab(p_current_stop_index);
1201 --
1202 --
1203 --l_stop_full_valid_ctrl_rec := get_stop_full_validation_ctrl_rec;
1204 --
1205 --
1206 l_validation_required := FALSE;
1207 --
1208 --
1209 IF p_old_stop_rec.stop_sequence_number <> l_new_stop_rec.stop_sequence_number
1210 THEN
1211 --{
1212 l_stop_validation_ctrl_rec.LOCATION_LANE_CHECK := TRUE;
1213 l_stop_validation_ctrl_rec.DATETIME_SCHEDULE_CHECK := TRUE;
1214 l_stop_validation_ctrl_rec.SEGMENT_CONNECTED_CHECK := TRUE;
1215 l_stop_validation_ctrl_rec.DLVY_IN_TRANSIT_CHECK := TRUE;
1216 l_validation_required := TRUE;
1217 --}
1218 ELSIF p_old_stop_rec.stop_location_id <> l_new_stop_rec.stop_location_id
1219 THEN
1220 --{
1221 l_stop_validation_ctrl_rec.LOCATION_LANE_CHECK := TRUE;
1222 l_stop_validation_ctrl_rec.SEGMENT_CONNECTED_CHECK := TRUE;
1223 l_stop_validation_ctrl_rec.DLVY_IN_TRANSIT_CHECK := FALSE;
1224
1225 IF p_old_stop_rec.planned_departure_date <> l_new_stop_rec.planned_departure_date
1226 OR p_old_stop_rec.planned_arrival_date <> l_new_stop_rec.planned_arrival_date
1227 THEN
1228 l_stop_validation_ctrl_rec.DATETIME_SCHEDULE_CHECK := TRUE;
1229 END IF;
1230 --
1231 --
1232 l_validation_required := TRUE;
1233 --}
1234 ELSIF p_old_stop_rec.planned_departure_date <> l_new_stop_rec.planned_departure_date
1235 OR p_old_stop_rec.planned_arrival_date <> l_new_stop_rec.planned_arrival_date
1236 THEN
1237 --{
1238 l_stop_validation_ctrl_rec.LOCATION_LANE_CHECK := FALSE;
1239 l_stop_validation_ctrl_rec.SEGMENT_CONNECTED_CHECK := FALSE;
1240 l_stop_validation_ctrl_rec.DLVY_IN_TRANSIT_CHECK := FALSE;
1241 l_stop_validation_ctrl_rec.DATETIME_SCHEDULE_CHECK := TRUE;
1242 l_validation_required := TRUE;
1243 --}
1244 END IF;
1245 --
1246 IF l_validation_required
1247 THEN
1248 --{
1249 validate_stop_add
1250 (
1251 P_trip_segment_rec => p_trip_segment_rec,
1252 P_trip_stop_tab => p_trip_stop_tab,
1253 p_current_stop_index => p_current_stop_index,
1254 p_stop_validation_ctrl_rec => l_stop_validation_ctrl_rec,
1255 p_schedule_start_datetime => p_schedule_start_datetime,
1256 p_schedule_end_datetime => p_schedule_end_datetime,
1257 X_return_status => l_return_status
1258 );
1259 --
1260 --
1261 FTE_MLS_UTIL.api_post_call
1262 (
1263 p_api_name => 'FTE_WSH_INTERFACE_PKG.VALIDATE_STOP_ADD',
1264 p_api_return_status => l_return_status,
1265 p_message_name => 'FTE_SEGMENT_STOP_UNEXP_ERROR',
1266 p_trip_segment_id => p_trip_segment_rec.trip_id,
1267 p_trip_segment_name => p_trip_segment_rec.name,
1268 p_trip_stop_id => l_new_stop_rec.stop_id,
1269 p_stop_seq_number => l_new_stop_rec.stop_sequence_number,
1270 x_number_of_errors => l_number_of_errors,
1271 x_number_of_warnings => l_number_of_warnings,
1272 x_return_status => x_return_status
1273 );
1274 --
1275 --
1276 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
1277 THEN
1278 --{
1279 RETURN;
1280 --}
1281 END IF;
1282 --}
1283 END IF;
1284 --
1285 --
1286 IF p_old_stop_rec.stop_sequence_number <> l_new_stop_rec.stop_sequence_number
1287 THEN
1288 --{
1289 --If user increased the sequence number, validate the first stop again.
1290 --
1291 --
1292 IF l_new_stop_rec.stop_sequence_number > p_old_stop_rec.stop_sequence_number
1293 AND p_curr_stop_old_position = G_FIRST
1294 THEN
1295 --{
1296 l_stop_validation_ctrl_rec.LOCATION_LANE_CHECK := TRUE;
1297 l_stop_validation_ctrl_rec.DATETIME_SCHEDULE_CHECK := TRUE;
1298 l_stop_validation_ctrl_rec.SEGMENT_CONNECTED_CHECK := TRUE;
1299 l_stop_validation_ctrl_rec.DLVY_IN_TRANSIT_CHECK := FALSE;
1300 --
1301 --
1302 l_validate_stop_index := p_trip_stop_tab.FIRST;
1303 l_validate_stop_rec := p_trip_stop_tab(l_validate_stop_index);
1304 --
1305 --
1306 validate_stop_add
1307 (
1308 P_trip_segment_rec => p_trip_segment_rec,
1309 P_trip_stop_tab => p_trip_stop_tab,
1310 p_current_stop_index => p_trip_stop_tab.FIRST,
1311 p_stop_validation_ctrl_rec => l_stop_validation_ctrl_rec,
1312 p_schedule_start_datetime => p_schedule_start_datetime,
1313 p_schedule_end_datetime => p_schedule_end_datetime,
1314 X_return_status => l_return_status
1315 );
1316 --
1317 --
1318 FTE_MLS_UTIL.api_post_call
1319 (
1320 p_api_name => 'FTE_WSH_INTERFACE_PKG.VALIDATE_STOP_ADD',
1321 p_api_return_status => l_return_status,
1322 p_message_name => 'FTE_SEGMENT_STOP_UNEXP_ERROR',
1323 p_trip_segment_id => p_trip_segment_rec.trip_id,
1324 p_trip_segment_name => p_trip_segment_rec.name,
1325 p_trip_stop_id => l_validate_stop_rec.stop_id,
1326 p_stop_seq_number => l_validate_stop_rec.stop_sequence_number,
1327 x_number_of_errors => l_number_of_errors,
1328 x_number_of_warnings => l_number_of_warnings,
1329 x_return_status => x_return_status
1330 );
1331 --
1332 --
1333 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
1334 THEN
1335 --{
1336 RETURN;
1337 --}
1338 END IF;
1339 --}
1340 END IF;
1341 --
1342 --
1343 --If user decreased the sequence number, validate the last stop again.
1344 --
1345 --
1346 IF l_new_stop_rec.stop_sequence_number < p_old_stop_rec.stop_sequence_number
1347 AND p_curr_stop_old_position = G_LAST
1348 THEN
1349 --{
1350 l_stop_validation_ctrl_rec.LOCATION_LANE_CHECK := TRUE;
1351 l_stop_validation_ctrl_rec.DATETIME_SCHEDULE_CHECK := TRUE;
1352 l_stop_validation_ctrl_rec.SEGMENT_CONNECTED_CHECK := TRUE;
1353 l_stop_validation_ctrl_rec.DLVY_IN_TRANSIT_CHECK := FALSE;
1354 --
1355 --
1356 l_validate_stop_index := p_trip_stop_tab.LAST;
1357 l_validate_stop_rec := p_trip_stop_tab(l_validate_stop_index);
1358 --
1359 --
1360 validate_stop_add
1361 (
1362 P_trip_segment_rec => p_trip_segment_rec,
1363 P_trip_stop_tab => p_trip_stop_tab,
1364 p_current_stop_index => p_trip_stop_tab.LAST,
1365 p_stop_validation_ctrl_rec => l_stop_validation_ctrl_rec,
1366 p_schedule_start_datetime => p_schedule_start_datetime,
1367 p_schedule_end_datetime => p_schedule_end_datetime,
1368 X_return_status => l_return_status
1369 );
1370 --
1371 --
1372 FTE_MLS_UTIL.api_post_call
1373 (
1374 p_api_name => 'FTE_WSH_INTERFACE_PKG.VALIDATE_STOP_ADD',
1375 p_api_return_status => l_return_status,
1376 p_message_name => 'FTE_SEGMENT_STOP_UNEXP_ERROR',
1377 p_trip_segment_id => p_trip_segment_rec.trip_id,
1378 p_trip_segment_name => p_trip_segment_rec.name,
1379 p_trip_stop_id => l_validate_stop_rec.stop_id,
1380 p_stop_seq_number => l_validate_stop_rec.stop_sequence_number,
1381 x_number_of_errors => l_number_of_errors,
1382 x_number_of_warnings => l_number_of_warnings,
1383 x_return_status => x_return_status
1384 );
1385 --
1386 --
1387 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
1388 THEN
1389 --{
1390 RETURN;
1391 --}
1392 END IF;
1393 --}
1394 END IF;
1395 --}
1396 END IF;
1397 --
1398 --
1399 IF p_old_stop_rec.status_code <> l_new_stop_rec.status_code
1400 AND l_new_stop_rec.status_code = 'CL'
1401 THEN
1402 --{
1403 NULL;
1404 FOR pickup_deliveries_rec IN pickup_deliveries_cur (p_stop_id => l_new_stop_rec.stop_id)
1405 LOOP
1406 --{
1407 fte_freight_pricing.shipment_reprice2
1408 (
1409 p_delivery_id => pickup_deliveries_rec.delivery_id,
1410 x_return_status => l_return_status
1411 );
1412 --
1413 --
1414 FTE_MLS_UTIL.api_post_call
1415 (
1416 p_api_name => 'FTE_WSH_INTERFACE_PKG.VALIDATE_STOP_ADD',
1417 p_api_return_status => l_return_status,
1418 p_message_name => 'FTE_STOP_REPRICING_UNEXP_ERROR',
1419 p_trip_segment_id => p_trip_segment_rec.trip_id,
1420 p_trip_segment_name => p_trip_segment_rec.name,
1421 p_trip_stop_id => l_new_stop_rec.stop_id,
1422 p_stop_seq_number => l_new_stop_rec.stop_sequence_number,
1423 p_delivery_id => pickup_deliveries_rec.delivery_id,
1424 p_delivery_name => pickup_deliveries_rec.name,
1425 x_number_of_errors => l_number_of_errors,
1426 x_number_of_warnings => l_number_of_warnings,
1427 x_return_status => x_return_status
1428 );
1429 --
1430 --
1431 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
1432 THEN
1433 --{
1434 RETURN;
1435 --}
1436 END IF;
1437 --}
1438 END LOOP;
1439 --
1440 --
1441 /*
1442 FOR get_segment_trips_rec IN get_segment_trips_cur
1443 LOOP
1444 --{
1445 fte_trips_pvt.validate_trip
1446 (
1447 p_trip_id => get_segment_trips_rec.fte_trip_id,
1448 x_return_status => l_return_status
1449 );
1450 --
1451 --
1452 FTE_MLS_UTIL.api_post_call
1453 (
1454 p_api_name => 'FTE_WSH_INTERFACE_PKG.VALIDATE_STOP_ADD',
1455 p_api_return_status => l_return_status,
1456 p_message_name => 'FTE_TRIP_SEG_STOP_UNEXP_ERROR',
1457 p_trip_segment_id => p_trip_segment_rec.trip_id,
1458 p_trip_segment_name => p_trip_segment_rec.name,
1459 p_trip_stop_id => l_validate_stop_rec.stop_id,
1460 p_stop_seq_number => l_validate_stop_rec.stop_sequence_number,
1461 p_trip_id => get_segment_trips_rec.fte_trip_id,
1462 p_trip_name => get_segment_trips_rec.fte_trip_name,
1463 x_number_of_errors => l_number_of_errors,
1464 x_number_of_warnings => l_number_of_warnings,
1465 x_return_status => x_return_status
1466 );
1467 --
1468 --
1469 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
1470 THEN
1471 --{
1472 RETURN;
1473 --}
1474 END IF;
1475 --}
1476 END LOOP;
1477 */
1478 --}
1479 END IF;
1480 --
1481 --
1482 IF l_number_of_errors > 0
1483 THEN
1484 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1485 ELSIF l_number_of_warnings > 0
1486 THEN
1487 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1488 ELSE
1489 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1490 END IF;
1491 --}
1492 EXCEPTION
1493 --{
1494 WHEN OTHERS THEN
1495 wsh_util_core.default_handler('FTE_WSH_INTERFACE_PKG.VALIDATE_STOP_UPDATE');
1496 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1497 --}
1498 END validate_stop_update;
1499 --
1500 --
1501 PROCEDURE validate_stop_delete
1502 (
1503 P_trip_segment_rec IN WSH_TRIPS_PVT.Trip_Rec_Type,
1504 P_trip_stop_tab IN Trip_Stop_Tab_type,
1505 p_current_stop_index IN NUMBER,
1506 p_schedule_start_datetime IN DATE,
1507 p_schedule_end_datetime IN DATE,
1508 x_return_status OUT NOCOPY VARCHAR2
1509 )
1510 IS
1511 --{
1512 l_deleted_stop_rec trip_stop_rec_type;
1513 l_validate_stop_rec trip_stop_rec_type;
1514 l_stop_validation_ctrl_rec stop_validation_ctrl_rec_type;
1515 l_stop_full_valid_ctrl_rec stop_validation_ctrl_rec_type;
1516 --
1517 l_validate_stop_index NUMBER;
1518 l_trip_count NUMBER;
1519 l_return_status VARCHAR2(32767);
1520 l_validation_required BOOLEAN := FALSE;
1521 l_previous_segment_id NUMBER;
1522 l_next_segment_id NUMBER;
1523 l_trip_name VARCHAR2(32767);
1524 l_trip_segment_name VARCHAR2(32767);
1525 --
1526 --
1527 l_number_of_errors NUMBER := 0;
1528 l_number_of_warnings NUMBER := 0;
1529 --
1530 --
1531 l_first BOOLEAN := FALSE;
1532 l_last BOOLEAN := FALSE;
1533 --
1534 --
1535 CURSOR get_segment_trips_cur
1536 IS
1537 SELECT b.fte_trip_id, sequence_number, b.name fte_trip_name
1538 FROM fte_wsh_trips a, fte_trips b
1539 WHERE a.wsh_trip_id = p_trip_segment_rec.trip_id
1540 AND a.fte_trip_id = b.fte_trip_id;
1541 --
1542 --}
1543 BEGIN
1544 --{
1545 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1546 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1547 l_number_of_errors := 0;
1548 l_number_of_warnings := 0;
1549 l_trip_count := 0;
1550 l_first := FALSE;
1551 l_last := FALSE;
1552 l_trip_segment_name := p_trip_segment_rec.name;
1553 --
1554 --
1555 l_deleted_stop_rec := p_trip_stop_tab(p_current_stop_index);
1556 --
1557 --
1558 --l_stop_full_valid_ctrl_rec := get_stop_full_validation_ctrl_rec;
1559 --
1560 --
1561 IF p_trip_stop_tab.FIRST = p_current_stop_index
1562 THEN
1563 --{
1564 l_first := TRUE;
1565 --}
1566 END IF;
1567 --
1568 --
1569 IF p_trip_stop_tab.LAST = p_current_stop_index
1570 THEN
1571 --{
1572 l_last := TRUE;
1573 --}
1574 END IF;
1575 --
1576 --
1577 FOR get_segment_trips_rec IN get_segment_trips_cur
1578 LOOP
1579 --{
1580 l_trip_count := l_trip_count + 1;
1581 EXIT;
1582 -- rest of the code in this for loop is redundant
1583 --
1584 --
1585 l_trip_name := get_segment_trips_rec.fte_trip_name;
1586 --
1587 --
1588 IF l_first
1589 THEN
1590 --{
1591 fte_mls_util.get_previous_segment_id
1592 (
1593 p_trip_id => get_segment_trips_rec.fte_trip_id,
1594 P_trip_segment_id => p_trip_segment_rec.trip_id,
1595 p_sequence_number => get_segment_trips_rec.sequence_number,
1596 x_trip_name => l_trip_name,
1597 x_trip_segment_name => l_trip_segment_name,
1598 x_previous_segment_id => l_previous_segment_id,
1599 x_return_status => l_return_status
1600 );
1601 --
1602 --
1603 FTE_MLS_UTIL.api_post_call
1604 (
1605 p_api_name => 'FTE_MLS_UTIL.GET_PREVIOUS_SEGMENT_ID',
1606 p_api_return_status => l_return_status,
1607 p_message_name => 'FTE_TRIP_SEGMENT_UNEXP_ERROR',
1608 p_trip_segment_id => p_trip_segment_rec.trip_id,
1609 p_trip_segment_name => p_trip_segment_rec.name,
1610 p_trip_id => get_segment_trips_rec.fte_trip_id,
1611 p_trip_name => get_segment_trips_rec.fte_trip_name,
1612 x_number_of_errors => l_number_of_errors,
1613 x_number_of_warnings => l_number_of_warnings,
1614 x_return_status => x_return_status
1615 );
1616 --
1617 --
1618 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
1619 THEN
1620 --{
1621 RETURN;
1622 --}
1623 END IF;
1624 --
1625 --
1626 IF l_previous_segment_id IS NOT NULL
1627 THEN
1628 --{
1629 FND_MESSAGE.SET_NAME('FTE', 'FTE_SEGMENT_PREV_CONNECT_ERROR');
1630 FND_MESSAGE.SET_TOKEN('TRIP_SEGMENT_NAME', p_trip_segment_rec.name);
1631 FND_MESSAGE.SET_TOKEN('TRIP_NAME', get_segment_trips_rec.fte_trip_name);
1632 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_WARNING);
1633 l_number_of_warnings := l_number_of_warnings + 1;
1634 --
1635 --
1636 UPDATE fte_trips
1637 SET validation_required = 'Y'
1638 WHERE fte_trip_id = get_segment_trips_rec.fte_trip_id;
1639 --}
1640 END IF;
1641 --}
1642 END IF;
1643 --
1644 --
1645 IF l_last
1646 THEN
1647 --{
1648 fte_mls_util.get_next_segment_id
1649 (
1650 p_trip_id => get_segment_trips_rec.fte_trip_id,
1651 P_trip_segment_id => p_trip_segment_rec.trip_id,
1652 p_sequence_number => get_segment_trips_rec.sequence_number,
1653 x_trip_name => l_trip_name,
1654 x_trip_segment_name => l_trip_segment_name,
1655 x_next_segment_id => l_next_segment_id,
1656 x_return_status => l_return_status
1657 );
1658 --
1659 --
1660 FTE_MLS_UTIL.api_post_call
1661 (
1662 p_api_name => 'FTE_MLS_UTIL.GET_NEXT_SEGMENT_ID',
1663 p_api_return_status => l_return_status,
1664 p_message_name => 'FTE_TRIP_SEGMENT_UNEXP_ERROR',
1665 p_trip_segment_id => p_trip_segment_rec.trip_id,
1666 p_trip_segment_name => p_trip_segment_rec.name,
1667 p_trip_id => get_segment_trips_rec.fte_trip_id,
1668 p_trip_name => get_segment_trips_rec.fte_trip_name,
1669 x_number_of_errors => l_number_of_errors,
1670 x_number_of_warnings => l_number_of_warnings,
1671 x_return_status => x_return_status
1672 );
1673 --
1674 --
1675 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
1676 THEN
1677 --{
1678 RETURN;
1679 --}
1680 END IF;
1681 --
1682 --
1683 IF l_next_segment_id IS NOT NULL
1684 THEN
1685 --{
1686 FND_MESSAGE.SET_NAME('FTE', 'FTE_SEGMENT_NEXT_CONNECT_ERROR');
1687 FND_MESSAGE.SET_TOKEN('TRIP_SEGMENT_NAME', p_trip_segment_rec.name);
1688 FND_MESSAGE.SET_TOKEN('TRIP_NAME', get_segment_trips_rec.fte_trip_name);
1689 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_WARNING);
1690 l_number_of_warnings := l_number_of_warnings + 1;
1691 --
1692 --
1693 UPDATE fte_trips
1694 SET validation_required = 'Y'
1695 WHERE fte_trip_id = get_segment_trips_rec.fte_trip_id;
1696 --}
1697 END IF;
1698 --}
1699 END IF;
1700 --}
1701 END LOOP;
1702 --
1703 --
1704 IF p_trip_stop_tab.COUNT <= 2
1705 AND (
1706 p_trip_segment_rec.lane_id IS NOT NULL
1707 OR
1708 l_trip_count > 0
1709 )
1710 THEN
1711 --{
1712 FND_MESSAGE.SET_NAME('FTE', 'FTE_STOP_DELETE_ERROR');
1713 FND_MESSAGE.SET_TOKEN('TRIP_SEGMENT_NAME', p_trip_segment_rec.name);
1714 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
1715 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1716 RETURN;
1717 --}
1718 END IF;
1719 --
1720 --
1721 l_validation_required := FALSE;
1722 --
1723 --
1724 IF l_first
1725 AND p_trip_stop_tab.COUNT >= 2
1726 THEN
1727 --{
1728 l_stop_validation_ctrl_rec.LOCATION_LANE_CHECK := TRUE;
1729 l_stop_validation_ctrl_rec.DATETIME_SCHEDULE_CHECK := TRUE;
1730 l_stop_validation_ctrl_rec.SEGMENT_CONNECTED_CHECK := TRUE;
1731 l_stop_validation_ctrl_rec.DLVY_IN_TRANSIT_CHECK := FALSE;
1732 l_validation_required := TRUE;
1733 --
1734 --
1735 l_validate_stop_index := p_current_stop_index+1;
1736 l_validate_stop_rec := p_trip_stop_tab(l_validate_stop_index);
1737 --
1738 --
1739 validate_first_stop
1740 (
1741 P_trip_segment_rec => p_trip_segment_rec,
1742 p_current_stop_rec => l_validate_stop_rec,
1743 p_stop_validation_ctrl_rec => l_stop_validation_ctrl_rec,
1744 p_schedule_start_datetime => p_schedule_start_datetime,
1745 p_schedule_end_datetime => p_schedule_end_datetime,
1746 X_return_status => l_return_status
1747 );
1748 --}
1749 END IF;
1750 --
1751 --
1752 IF l_last
1753 AND p_trip_stop_tab.COUNT >= 2
1754 THEN
1755 --{
1756 l_stop_validation_ctrl_rec.LOCATION_LANE_CHECK := TRUE;
1757 l_stop_validation_ctrl_rec.DATETIME_SCHEDULE_CHECK := TRUE;
1758 l_stop_validation_ctrl_rec.SEGMENT_CONNECTED_CHECK := TRUE;
1759 l_stop_validation_ctrl_rec.DLVY_IN_TRANSIT_CHECK := FALSE;
1760 l_validation_required := TRUE;
1761 --
1762 --
1763 l_validate_stop_index := p_current_stop_index-1;
1764 l_validate_stop_rec := p_trip_stop_tab(l_validate_stop_index);
1765 --
1766 --
1767 validate_last_stop
1768 (
1769 P_trip_segment_rec => p_trip_segment_rec,
1770 p_current_stop_rec => l_validate_stop_rec,
1771 p_stop_validation_ctrl_rec => l_stop_validation_ctrl_rec,
1772 p_schedule_start_datetime => p_schedule_start_datetime,
1773 p_schedule_end_datetime => p_schedule_end_datetime,
1774 X_return_status => l_return_status
1775 );
1776 --}
1777 END IF;
1778 --
1779 --
1780 IF l_validation_required
1781 THEN
1782 --{
1783 FTE_MLS_UTIL.api_post_call
1784 (
1785 p_api_name => 'FTE_WSH_INTERFACE_PKG.VALIDATE_STOP_ADD',
1786 p_api_return_status => l_return_status,
1787 p_message_name => 'FTE_SEGMENT_STOP_UNEXP_ERROR',
1788 p_trip_segment_id => p_trip_segment_rec.trip_id,
1789 p_trip_segment_name => p_trip_segment_rec.name,
1790 p_trip_stop_id => l_validate_stop_rec.stop_id,
1791 p_stop_seq_number => l_validate_stop_rec.stop_sequence_number,
1792 x_number_of_errors => l_number_of_errors,
1793 x_number_of_warnings => l_number_of_warnings,
1794 x_return_status => x_return_status
1795 );
1796 --
1797 --
1798 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
1799 THEN
1800 --{
1801 RETURN;
1802 --}
1803 END IF;
1804 --}
1805 END IF;
1806 --
1807 --
1808 IF l_number_of_errors > 0
1809 THEN
1810 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1811 ELSIF l_number_of_warnings > 0
1812 THEN
1813 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1814 ELSE
1815 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1816 END IF;
1817 --}
1818 EXCEPTION
1819 --{
1820 WHEN OTHERS THEN
1821 wsh_util_core.default_handler('FTE_WSH_INTERFACE_PKG.VALIDATE_STOP_DELETE');
1822 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1823 --}
1824 END validate_stop_delete;
1825 --
1826 --
1827
1828 -------------
1829 --========================================================================
1830 -- PROCEDURE : SEGMENT_STOP_CHANGE
1831 --
1832 -- COMMENT : When ever a stop is added, updated or delete, this procedure is
1833 -- called to validate if the stop can be inserted
1834 -- CREATED BY: NPARIKH
1835 -- MODIFIED : HBHAGAVA 11/24/2002
1836 -- DESC: Additional check to see if adding a new stop, or updating stop
1837 -- has changed the weight/volume. If it is changed over the carrier threshold
1838 -- value then an update notification will be send to carrier.
1839 --
1840 --========================================================================
1841
1842
1843 PROCEDURE segment_stop_change
1844 (
1845 P_api_version IN NUMBER,
1846 P_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1847 P_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1848 X_return_status OUT NOCOPY VARCHAR2,
1849 X_msg_count OUT NOCOPY NUMBER,
1850 X_msg_data OUT NOCOPY VARCHAR2,
1851 P_trip_segment_rec IN WSH_TRIPS_PVT.Trip_Rec_Type,
1852 P_old_segment_stop_rec IN WSH_TRIP_STOPS_PVT.trip_stop_rec_type,
1853 P_new_segment_stop_rec IN WSH_TRIP_STOPS_PVT.trip_stop_rec_type,
1854 p_segmentStopChangeInRec IN segmentStopChangeInRecType,
1855 p_segmentStopChangeOutRec OUT NOCOPY segmentStopChangeOutRecType
1856 )
1857 IS
1858 --{
1859 l_api_name CONSTANT VARCHAR2(30) := 'SEGMENT_STOP_CHANGE';
1860 l_api_version CONSTANT NUMBER := 1.0;
1861 --
1862 --
1863 l_trip_stop_tab Trip_Stop_tab_Type;
1864 l_current_stop_rec WSH_TRIP_STOPS_PVT.trip_stop_rec_type;
1865 l_stop_full_valid_ctrl_rec stop_validation_ctrl_rec_type;
1866 --
1867 --
1868 l_current_stop_index NUMBER;
1869 l_curr_stop_old_position VARCHAR2(32767);
1870 l_schedule_start_datetime DATE;
1871 l_schedule_end_datetime DATE;
1872 --
1873 --
1874 l_return_status VARCHAR2(32767);
1875 l_msg_count NUMBER;
1876 l_index NUMBER;
1877 l_msg_data VARCHAR2(32767);
1878 l_program_name VARCHAR2(32767);
1879 p_action_type VARCHAR2(32767);
1880 --
1881 --
1882 l_number_of_errors NUMBER := 0;
1883 l_number_of_warnings NUMBER := 0;
1884 --
1885 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
1886
1887 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || 'FTE_WSH_INTERFACE_PKG' || '.' || 'segment_stop_change';
1888
1889 --
1890 --}
1891 BEGIN
1892 --{
1893 --
1894 --
1895 -- Standard Start of API savepoint
1896 SAVEPOINT SEGMENT_STOP_CHANGE_PUB;
1897 --
1898 -- Standard call to check for call compatibility.
1899 IF NOT FND_API.Compatible_API_Call
1900 (
1901 l_api_version,
1902 p_api_version,
1903 l_api_name,
1904 G_PKG_NAME
1905 )
1906 THEN
1907 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1908 END IF;
1909 --
1910 --
1911 -- Initialize message list if p_init_msg_list is set to TRUE.
1912 --
1913 --
1914 IF l_debug_on THEN
1915 wsh_debug_sv.push(l_module_name);
1916 END IF;
1917
1918
1919 IF FND_API.to_Boolean( p_init_msg_list )
1920 THEN
1921 FND_MSG_PUB.initialize;
1922 END IF;
1923 --
1924 --
1925 -- Initialize API return status to success
1926 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1927 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1928 l_number_of_errors := 0;
1929 l_number_of_warnings := 0;
1930 p_action_type := p_segmentStopChangeInRec.action_type;
1931 --
1932 --
1933 -- Start of API body.
1934 --
1935 IF p_action_type IN ( G_ADD, G_UPDATE )
1936 THEN
1937 l_current_stop_rec := p_new_segment_stop_rec;
1938 ELSIF p_action_type = G_DELETE
1939 THEN
1940 l_current_stop_rec := p_old_segment_stop_rec;
1941 ELSIF p_action_type = G_TRIP_SEGMENT_DELETE
1942 THEN
1943 NULL;
1944 ELSE
1945 --{
1946 FND_MESSAGE.SET_NAME('FTE', 'FTE_WSH_IF_INVALID_ACTION');
1947 FND_MESSAGE.SET_TOKEN('ACTION_TYPE', p_action_type);
1948 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
1949 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1950 RAISE FND_API.G_EXC_ERROR;
1951 --RETURN;
1952 --}
1953 END IF;
1954 --
1955 --
1956 get_segment_stops
1957 (
1958 P_trip_segment_rec => p_trip_segment_rec,
1959 P_current_stop_rec => l_current_stop_rec,
1960 p_action_type => p_action_type,
1961 x_trip_stop_tab => l_trip_stop_tab,
1962 x_current_stop_index => l_current_stop_index,
1963 x_curr_stop_old_position => l_curr_stop_old_position,
1964 x_schedule_start_datetime => l_schedule_start_datetime,
1965 x_schedule_end_datetime => l_schedule_end_datetime,
1966 X_return_status => l_return_status
1967 );
1968 --
1969 --
1970 FTE_MLS_UTIL.api_post_call
1971 (
1972 p_api_name => 'FTE_WSH_INTERFACE_PKG.VALIDATE_STOP_ADD',
1973 p_api_return_status => l_return_status,
1974 p_message_name => 'FTE_SEGMENT_STOP_UNEXP_ERROR',
1975 p_trip_segment_id => p_trip_segment_rec.trip_id,
1976 p_trip_segment_name => p_trip_segment_rec.name,
1977 p_trip_stop_id => l_current_stop_rec.stop_id,
1978 p_stop_seq_number => l_current_stop_rec.stop_sequence_number,
1979 x_number_of_errors => l_number_of_errors,
1980 x_number_of_warnings => l_number_of_warnings,
1981 x_return_status => x_return_status
1982 );
1983 --
1984 --
1985 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
1986 THEN
1987 --{
1988 RAISE FND_API.G_EXC_ERROR;
1989 --RETURN;
1990 --}
1991 END IF;
1992 --
1993 --
1994 -- debugging statments
1995 --
1996 --
1997 --
1998 IF p_action_type = G_ADD
1999 THEN
2000 --{
2001
2002 IF l_debug_on THEN
2003
2004 WSH_DEBUG_SV.logmsg(l_module_name,'Adding Stop',
2005 WSH_DEBUG_SV.C_PROC_LEVEL);
2006 END IF;
2007
2008 l_program_name := 'FTE_WSH_INTERFACE_PKG.VALIDATE_STOP_ADD';
2009 --
2010 --
2011 l_stop_full_valid_ctrl_rec := get_stop_full_validn_ctrl_rec;
2012 --
2013 --
2014 IF l_trip_stop_tab.COUNT <= 2
2015 THEN
2016 --{
2017 l_stop_full_valid_ctrl_rec.DATETIME_SCHEDULE_CHECK := FALSE;
2018 l_stop_full_valid_ctrl_rec.LOCATION_LANE_CHECK := FALSE;
2019 --}
2020 END IF;
2021 --
2022 --
2023 validate_stop_add
2024 (
2025 P_trip_segment_rec => p_trip_segment_rec,
2026 P_trip_stop_tab => l_trip_stop_tab,
2027 p_current_stop_index => l_current_stop_index,
2028 p_stop_validation_ctrl_rec => l_stop_full_valid_ctrl_rec,
2029 p_schedule_start_datetime => l_schedule_start_datetime,
2030 p_schedule_end_datetime => l_schedule_end_datetime,
2031 X_return_status => l_return_status
2032 );
2033
2034
2035 wsh_util_core.api_post_call(
2036 p_return_status =>l_return_status,
2037 x_num_warnings =>l_number_of_warnings,
2038 x_num_errors =>l_number_of_errors);
2039
2040 --
2041 -- Check for the threshold value if this stop belongs to a trip
2042 -- which is TENDERED or ACCEPTED PACK I
2043 --
2044 IF l_debug_on THEN
2045
2046 WSH_DEBUG_SV.logmsg(l_module_name,'Calling FTE_TENDER_PVT.CHECK_THRESHOLD_FOR_STOP',
2047 WSH_DEBUG_SV.C_PROC_LEVEL);
2048 END IF;
2049
2050 FTE_TENDER_PVT.CHECK_THRESHOLD_FOR_STOP(
2051 P_api_version => 1.0,
2052 P_init_msg_list => FND_API.G_FALSE,
2053 X_return_status => l_return_status,
2054 X_msg_count => l_msg_count,
2055 X_msg_data => l_msg_data,
2056 P_trip_segment_rec => p_trip_segment_rec,
2057 P_new_segment_stop_rec => l_current_stop_rec);
2058
2059 wsh_util_core.api_post_call(
2060 p_return_status =>l_return_status,
2061 x_num_warnings =>l_number_of_warnings,
2062 x_num_errors =>l_number_of_errors,
2063 p_msg_data =>l_msg_data);
2064
2065 --}
2066 ELSIF p_action_type = G_UPDATE
2067 THEN
2068 --{
2069 l_program_name := 'FTE_WSH_INTERFACE_PKG.VALIDATE_STOP_UPDATE';
2070
2071 IF l_debug_on THEN
2072
2073 WSH_DEBUG_SV.logmsg(l_module_name,'Updating Stop',
2074 WSH_DEBUG_SV.C_PROC_LEVEL);
2075 END IF;
2076 --
2077 --
2078 validate_stop_update
2079 (
2080 P_trip_segment_rec => p_trip_segment_rec,
2081 P_trip_stop_tab => l_trip_stop_tab,
2082 p_current_stop_index => l_current_stop_index,
2083 p_curr_stop_old_position => l_curr_stop_old_position,
2084 p_old_stop_rec => p_old_segment_stop_rec,
2085 p_schedule_start_datetime => l_schedule_start_datetime,
2086 p_schedule_end_datetime => l_schedule_end_datetime,
2087 X_return_status => l_return_status
2088 );
2089
2090
2091 wsh_util_core.api_post_call(
2092 p_return_status => l_return_status,
2093 x_num_warnings =>l_number_of_warnings,
2094 x_num_errors =>l_number_of_errors);
2095
2096
2097 IF l_debug_on THEN
2098 wsh_debug_sv.log (l_module_name,' After validate stop update');
2099 wsh_debug_sv.log (l_module_name,' l_return_status ' || l_return_status);
2100 END IF;
2101
2102 --
2103 -- Check for the threshold value if this stop belongs to a trip
2104 -- which is TENDERED or ACCEPTED PACK I
2105 --
2106 IF l_debug_on THEN
2107
2108 WSH_DEBUG_SV.logmsg(l_module_name,'Calling FTE_TENDER_PVT.CHECK_THRESHOLD_FOR_STOP',
2109 WSH_DEBUG_SV.C_PROC_LEVEL);
2110 END IF;
2111
2112
2113 FTE_TENDER_PVT.CHECK_THRESHOLD_FOR_STOP(
2114 P_api_version => 1.0,
2115 P_init_msg_list => FND_API.G_FALSE,
2116 X_return_status => l_return_status,
2117 X_msg_count => l_msg_count,
2118 X_msg_data => l_msg_data,
2119 P_trip_segment_rec => p_trip_segment_rec,
2120 P_new_segment_stop_rec => l_current_stop_rec);
2121
2122 IF l_debug_on THEN
2123 WSH_DEBUG_SV.logmsg(l_module_name,' return status from CHECK_THRESHOLD_FOR_STOP ' ||
2124 l_return_status,
2125 WSH_DEBUG_SV.C_PROC_LEVEL);
2126 WSH_DEBUG_SV.logmsg(l_module_name,' message count ' || l_msg_count,
2127 WSH_DEBUG_SV.C_PROC_LEVEL);
2128 WSH_DEBUG_SV.logmsg(l_module_name,' message data ' || l_msg_data,
2129 WSH_DEBUG_SV.C_PROC_LEVEL);
2130 END IF;
2131
2132 wsh_util_core.api_post_call(
2133 p_return_status =>l_return_status,
2134 x_num_warnings =>l_number_of_warnings,
2135 x_num_errors =>l_number_of_errors,
2136 p_msg_data =>l_msg_data);
2137
2138
2139 --}
2140 ELSIF p_action_type = G_DELETE
2141 THEN
2142 --{
2143 l_program_name := 'FTE_WSH_INTERFACE_PKG.VALIDATE_STOP_DELETE';
2144 --
2145 --
2146 validate_stop_delete
2147 (
2148 P_trip_segment_rec => p_trip_segment_rec,
2149 P_trip_stop_tab => l_trip_stop_tab,
2150 p_current_stop_index => l_current_stop_index,
2151 p_schedule_start_datetime => l_schedule_start_datetime,
2152 p_schedule_end_datetime => l_schedule_end_datetime,
2153 X_return_status => l_return_status
2154 );
2155
2156 --}
2157 END IF;
2158 --
2159 --
2160 FTE_MLS_UTIL.api_post_call
2161 (
2162 p_api_name => l_program_name,
2163 p_api_return_status => l_return_status,
2164 p_message_name => 'FTE_SEGMENT_STOP_UNEXP_ERROR',
2165 p_trip_segment_id => p_trip_segment_rec.trip_id,
2166 p_trip_segment_name => p_trip_segment_rec.name,
2167 p_trip_stop_id => l_trip_stop_tab(l_current_stop_index).stop_id,
2168 p_stop_seq_number => l_trip_stop_tab(l_current_stop_index).stop_sequence_number,
2169 x_number_of_errors => l_number_of_errors,
2170 x_number_of_warnings => l_number_of_warnings,
2171 x_return_status => x_return_status
2172 );
2173 --
2174 --
2175 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
2176 THEN
2177 --{
2178 RAISE FND_API.G_EXC_ERROR;
2179 --RETURN;
2180 --}
2181 END IF;
2182 --
2183 --
2184 --
2185 --
2186 --
2187 --
2188 IF l_number_of_errors > 0
2189 THEN
2190 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2191 ELSIF l_number_of_warnings > 0
2192 THEN
2193 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2194 ELSE
2195 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2196 END IF;
2197 --
2198 --
2199 --
2200 --
2201 -- End of API body.
2202 --
2203 --
2204 -- Standard check of p_commit.
2205 --
2206 IF FND_API.To_Boolean( p_commit )
2207 THEN
2208 COMMIT WORK;
2209 END IF;
2210 --
2211 --
2212 -- Standard call to get message count and if count is 1,get message info.
2213 --
2214
2215 FND_MSG_PUB.Count_And_Get
2216 (
2217 p_count => x_msg_count,
2218 p_data => x_msg_data,
2219 p_encoded => FND_API.G_FALSE
2220 );
2221 --fnd_message.set_name('WSH','END');
2222 --
2223
2224 IF l_debug_on THEN
2225 WSH_DEBUG_SV.pop(l_module_name);
2226 END IF;
2227
2228 --
2229 --}
2230 EXCEPTION
2231 --{
2232 WHEN FND_API.G_EXC_ERROR THEN
2233 ROLLBACK TO SEGMENT_STOP_CHANGE_PUB;
2234 x_return_status := FND_API.G_RET_STS_ERROR ;
2235 FND_MSG_PUB.Count_And_Get
2236 (
2237 p_count => x_msg_count,
2238 p_data => x_msg_data,
2239 p_encoded => FND_API.G_FALSE
2240 );
2241 IF l_debug_on THEN
2242 wsh_debug_sv.log (l_module_name,'Error',substr(sqlerrm,1,200));
2243 WSH_DEBUG_SV.pop(l_module_name);
2244 END IF;
2245 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2246 ROLLBACK TO SEGMENT_STOP_CHANGE_PUB;
2247 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2248 FND_MSG_PUB.Count_And_Get
2249 (
2250 p_count => x_msg_count,
2251 p_data => x_msg_data,
2252 p_encoded => FND_API.G_FALSE
2253 );
2254 IF l_debug_on THEN
2255 WSH_DEBUG_SV.pop(l_module_name);
2256 END IF;
2257 WHEN OTHERS THEN
2258 ROLLBACK TO SEGMENT_STOP_CHANGE_PUB;
2259 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2260 IF l_debug_on THEN
2261 wsh_debug_sv.log (l_module_name,' Unexpected error ');
2262 wsh_debug_sv.log (l_module_name,'Error',substr(sqlerrm,1,200));
2263 WSH_DEBUG_SV.pop(l_module_name);
2264 END IF;
2265 wsh_util_core.default_handler('FTE_WSH_INTERFACE_PKG.segment_stop_change SEGMENT');
2266 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2267 --
2268 --
2269 FND_MSG_PUB.Count_And_Get
2270 (
2271 p_count => x_msg_count,
2272 p_data => x_msg_data,
2273 p_encoded => FND_API.G_FALSE
2274 );
2275 --}
2276 END segment_stop_change;
2277 --
2278 --
2279 PROCEDURE validate_segment_update
2280 (
2281 P_new_trip_segment_rec IN WSH_TRIPS_PVT.Trip_Rec_Type,
2282 P_old_trip_segment_rec IN WSH_TRIPS_PVT.Trip_Rec_Type,
2283 x_return_status OUT NOCOPY VARCHAR2
2284 )
2285 IS
2286 --{
2287 --
2288 --
2289 l_next_segment_id NUMBER;
2290 l_previous_segment_id NUMBER;
2291 l_trip_name VARCHAR2(32767);
2292 l_trip_segment_name VARCHAR2(32767);
2293 --
2294 l_validate_stop_index NUMBER;
2295 l_msg_count NUMBER;
2296 l_msg_data VARCHAR2(32767);
2297 l_trip_count NUMBER;
2298 l_return_status VARCHAR2(32767);
2299 l_validation_required BOOLEAN := FALSE;
2300 --
2301 --
2302 l_number_of_errors NUMBER := 0;
2303 l_number_of_warnings NUMBER := 0;
2304 --
2305 --
2306 CURSOR get_segment_trips_cur
2307 IS
2308 SELECT b.fte_trip_id, sequence_number, b.name fte_trip_name
2309 FROM fte_wsh_trips a, fte_trips b
2310 WHERE a.wsh_trip_id = p_new_trip_segment_rec.trip_id
2311 AND a.fte_trip_id = b.fte_trip_id;
2312 --
2313 --
2314 CURSOR get_prev_segments_cur
2315 (
2316 p_trip_id IN NUMBER,
2317 p_sequencE_number IN NUMBER
2318 )
2319 IS
2320 SELECT b.status_code, b.trip_id, b.name wsh_trip_name
2321 FROM fte_wsh_trips a, wsh_trips b
2322 WHERE a.fte_trip_id = p_trip_id
2323 AND a.sequence_number < p_sequence_number
2324 AND a.wsh_trip_id = b.trip_id;
2325 --
2326 --
2327 --
2328 CURSOR get_segment_info_cur
2329 (
2330 p_segment_id IN NUMBER
2331 )
2332 IS
2333 SELECT status_code, name
2334 FROM wsh_trips
2335 WHERE trip_id = p_segment_id;
2336 --
2337 --}
2338 BEGIN
2339 --{
2340 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2341 l_number_of_errors := 0;
2342 l_number_of_warnings := 0;
2343 l_trip_segment_name := p_new_trip_segment_rec.name;
2344 --
2345 --
2346 --
2347 IF
2348 (
2349 (
2350 p_old_trip_segment_rec.carrier_id IS NOT NULL
2351 AND p_old_trip_segment_rec.carrier_id <> NVL(p_new_trip_segment_rec.carrier_id,-999)
2352 )
2353 OR
2354 (
2355 p_old_trip_segment_rec.mode_of_transport IS NOT NULL
2356 AND p_old_trip_segment_rec.mode_of_transport <> NVL(p_new_trip_segment_rec.mode_of_transport,'!-')
2357 )
2358 OR
2359 (
2360 p_old_trip_segment_rec.service_level IS NOT NULL
2361 AND p_old_trip_segment_rec.service_level <> NVL(p_new_trip_segment_rec.service_level,'!-')
2362 )
2363 )
2364 THEN
2365 --{
2366 IF p_new_trip_segment_rec.lane_id IS NOT NULL
2367 AND p_old_trip_segment_rec.lane_id IS NOT NULL
2368 THEN
2369 --{
2370 -- Release 12: Added so that FTE can update lane_id without first having to null out lane_id
2371 IF p_new_trip_segment_rec.lane_id = p_old_trip_segment_rec.lane_id
2372 AND NVL(p_new_trip_segment_rec.ship_method_code, '!-') <> NVL(p_old_trip_segment_rec.ship_method_code,'!-')
2373 THEN
2374 FND_MESSAGE.SET_NAME('FTE', 'FTE_SEGMENT_CSM_CHANGE_ERROR');
2375 FND_MESSAGE.SET_TOKEN('TRIP_SEGMENT_NAME', p_new_trip_segment_rec.name);
2376 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
2377 l_number_of_errors := l_number_of_errors + 1;
2378 END IF;
2379 --}
2380 END IF;
2381 --}
2382 END IF;
2383 --
2384 --
2385 --
2386 IF p_old_trip_segment_rec.status_code <> p_new_trip_segment_rec.status_code
2387 THEN
2388 --{
2389 FOR get_segment_trips_rec IN get_segment_trips_cur
2390 LOOP
2391 --{
2392 l_trip_name := get_segment_trips_rec.fte_trip_name;
2393 --
2394 --
2395 IF p_new_trip_segment_rec.status_code = 'IT'
2396 THEN
2397 --{
2398 FOR get_prev_segments_rec
2399 IN get_prev_segments_cur
2400 (
2401 p_trip_id => get_segment_trips_rec.fte_trip_id,
2402 p_sequence_number => get_segment_trips_rec.sequence_number
2403 )
2404 LOOP
2405 --{
2406 IF get_prev_segments_rec.status_code <> 'CL'
2407 THEN
2408 --{
2409 FND_MESSAGE.SET_NAME('FTE', 'FTE_SEGMENT_PREV_CLOSE_ERROR');
2410 FND_MESSAGE.SET_TOKEN('PREV_TRIP_SEG_NAME', get_prev_segments_rec.wsh_trip_name);
2411 FND_MESSAGE.SET_TOKEN('TRIP_SEGMENT_NAME', p_new_trip_segment_rec.name);
2412 FND_MESSAGE.SET_TOKEN('TRIP_NAME', get_segment_trips_rec.fte_trip_name);
2413 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_WARNING);
2414 l_number_of_warnings := l_number_of_warnings + 1;
2415 --}
2416 END IF;
2417 --}
2418 END LOOP;
2419 --
2420 --
2421 UPDATE fte_trips
2422 SET status_code = 'IT',
2423 last_update_date = sysdate,
2424 last_updated_by = FND_GLOBAL.USER_ID,
2425 last_update_login = FND_GLOBAL.LOGIN_ID
2426 WHERE fte_trip_id = get_segment_trips_rec.fte_trip_id
2427 AND status_code = 'OP';
2428 --
2429 --
2430 IF SQL%ROWCOUNT > 0
2431 THEN
2432 --{
2433 --
2434 --
2435 fte_trips_pvt.validate_trip
2436 (
2437 p_trip_id => get_segment_trips_rec.fte_trip_id,
2438 x_return_status => l_return_status,
2439 x_msg_count => l_msg_count,
2440 x_msg_data => l_msg_data
2441 );
2442 --
2443 --
2444 FTE_MLS_UTIL.api_post_call
2445 (
2446 p_api_name => 'FTE_TRIPS_PVT.VALIDATE_TRIP',
2447 p_api_return_status => l_return_status,
2448 p_message_name => 'FTE_TRIP_SEGMENT_UNEXP_ERROR',
2449 p_trip_segment_id => p_new_trip_segment_rec.trip_id,
2450 p_trip_segment_name => p_new_trip_segment_rec.name,
2451 p_trip_id => get_segment_trips_rec.fte_trip_id,
2452 p_trip_name => get_segment_trips_rec.fte_trip_name,
2453 x_number_of_errors => l_number_of_errors,
2454 x_number_of_warnings => l_number_of_warnings,
2455 x_return_status => x_return_status
2456 );
2457 --
2458 --
2459 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
2460 THEN
2461 --{
2462 RETURN;
2463 --}
2464 END IF;
2465 --}
2466 END IF;
2467 --}
2468 ELSIF p_new_trip_segment_rec.status_code = 'CL'
2469 THEN
2470 --{
2471 IF
2472 (
2473 fte_mls_util.all_other_segments_closed
2474 (
2475 p_trip_id => get_segment_trips_rec.fte_trip_id,
2476 P_trip_segment_id => p_new_trip_segment_rec.trip_id
2477 )
2478 )
2479 THEN
2480 --{
2481 UPDATE fte_trips
2482 SET status_code = 'CL',
2483 last_update_date = sysdate,
2484 last_updated_by = FND_GLOBAL.USER_ID,
2485 last_update_login = FND_GLOBAL.LOGIN_ID
2486 WHERE fte_trip_id = get_segment_trips_rec.fte_trip_id;
2487 --}
2488 END IF;
2489 --}
2490 END IF;
2491 --}
2492 END LOOP;
2493 --}
2494 END IF;
2495 --
2496 --
2497 IF l_number_of_errors > 0
2498 THEN
2499 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2500 ELSIF l_number_of_warnings > 0
2501 THEN
2502 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2503 ELSE
2504 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2505 END IF;
2506 --}
2507 EXCEPTION
2508 --{
2509 WHEN OTHERS THEN
2510 wsh_util_core.default_handler('FTE_WSH_INTERFACE_PKG.VALIDATE_SEGMENT_UPDATE');
2511 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2512 --}
2513 END validate_segment_update;
2514 --
2515 --
2516 PROCEDURE validate_segment_delete
2517 (
2518 P_trip_segment_rec IN WSH_TRIPS_PVT.Trip_Rec_Type,
2519 x_return_status OUT NOCOPY VARCHAR2
2520 )
2521 IS
2522 --{
2523 --
2524 l_next_segment_id NUMBER;
2525 l_previous_segment_id NUMBER;
2526 l_trip_name VARCHAR2(32767);
2527 l_trip_segment_name VARCHAR2(32767);
2528 --
2529 --
2530 l_trip_count NUMBER;
2531 l_msg_count NUMBER;
2532 l_msg_data varchar2(32767);
2533 l_return_status VARCHAR2(32767);
2534 l_validation_required BOOLEAN := FALSE;
2535 --
2536 --
2537 l_number_of_errors NUMBER := 0;
2538 l_number_of_warnings NUMBER := 0;
2539 --
2540 --
2541 CURSOR get_segment_trips_cur
2542 IS
2543 SELECT b.fte_trip_id, sequence_number, b.name fte_trip_name
2544 FROM fte_wsh_trips a, fte_trips b
2545 WHERE a.wsh_trip_id = p_trip_segment_rec.trip_id
2546 AND a.fte_trip_id = b.fte_trip_id;
2547 --
2548 --
2549 --
2550 CURSOR get_segment_info_cur
2551 (
2552 p_segment_id IN NUMBER
2553 )
2554 IS
2555 SELECT status_code, name
2556 FROM wsh_trips
2557 WHERE trip_id = p_segment_id;
2558 --
2559 --}
2560 BEGIN
2561 --{
2562 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2563 l_number_of_errors := 0;
2564 l_number_of_warnings := 0;
2565 l_trip_segment_name := p_trip_segment_rec.name;
2566 --
2567 --
2568 --
2569 FOR get_segment_trips_rec IN get_segment_trips_cur
2570 LOOP
2571 --{
2572 l_trip_name := get_segment_trips_rec.fte_trip_name;
2573 --
2574 --
2575 --{
2576 fte_wsh_trips_pvt.delete_trip
2577 (
2578 p_fte_trip_id => get_segment_trips_rec.fte_trip_id,
2579 P_wsh_trip_id => p_trip_segment_rec.trip_id,
2580 x_return_status => l_return_status,
2581 x_msg_count => l_msg_count,
2582 x_msg_data => l_msg_data
2583 );
2584 --
2585 --
2586 FTE_MLS_UTIL.api_post_call
2587 (
2588 p_api_name => 'FTE_WSH_TRIPS_PUB.DELETE_TRIP',
2589 p_api_return_status => l_return_status,
2590 p_message_name => 'FTE_TRIP_SEGMENT_UNEXP_ERROR',
2591 p_trip_segment_id => p_trip_segment_rec.trip_id,
2592 p_trip_segment_name => p_trip_segment_rec.name,
2593 p_trip_id => get_segment_trips_rec.fte_trip_id,
2594 p_trip_name => get_segment_trips_rec.fte_trip_name,
2595 x_number_of_errors => l_number_of_errors,
2596 x_number_of_warnings => l_number_of_warnings,
2597 x_return_status => x_return_status
2598 );
2599 --
2600 --
2601 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
2602 THEN
2603 --{
2604 RETURN;
2605 --}
2606 END IF;
2607 --
2608 --
2609 IF
2610 (
2611 fte_mls_util.all_other_segments_closed
2612 (
2613 p_trip_id => get_segment_trips_rec.fte_trip_id,
2614 P_trip_segment_id => p_trip_segment_rec.trip_id
2615 )
2616 )
2617 THEN
2618 --{
2619 UPDATE fte_trips
2620 SET status_code = 'CL',
2621 last_update_date = sysdate,
2622 last_updated_by = FND_GLOBAL.USER_ID,
2623 last_update_login = FND_GLOBAL.LOGIN_ID
2624 WHERE fte_trip_id = get_segment_trips_rec.fte_trip_id;
2625 --}
2626 END IF;
2627 --}
2628 --}
2629 END LOOP;
2630 --
2631 --
2632 IF l_number_of_errors > 0
2633 THEN
2634 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2635 ELSIF l_number_of_warnings > 0
2636 THEN
2637 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2638 ELSE
2639 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2640 END IF;
2641 --}
2642 EXCEPTION
2643 --{
2644 WHEN OTHERS THEN
2645 wsh_util_core.default_handler('FTE_WSH_INTERFACE_PKG.VALIDATE_SEGMENT_DELETE');
2646 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2647 --}
2648 END validate_segment_delete;
2649 --
2650 --
2651 PROCEDURE trip_segment_change
2652 (
2653 P_api_version IN NUMBER,
2654 P_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2655 P_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2656 X_return_status OUT NOCOPY VARCHAR2,
2657 X_msg_count OUT NOCOPY NUMBER,
2658 X_msg_data OUT NOCOPY VARCHAR2,
2659 P_old_trip_segment_rec IN WSH_TRIPS_PVT.Trip_Rec_Type,
2660 P_new_trip_segment_rec IN WSH_TRIPS_PVT.Trip_Rec_Type,
2661 p_tripSegmentChangeInRec IN tripSegmentChangeInRecType,
2662 p_tripSegmentChangeOutRec OUT NOCOPY tripSegmentChangeOutRecType
2663 )
2664 IS
2665 --{
2666 l_api_name CONSTANT VARCHAR2(30) := 'TRIP_SEGMENT_CHANGE';
2667 l_api_version CONSTANT NUMBER := 1.0;
2668 --
2669 --
2670 --
2671 l_return_status VARCHAR2(32767);
2672 l_msg_count NUMBER;
2673 l_msg_data VARCHAR2(32767);
2674 l_program_name VARCHAR2(32767);
2675 p_action_type VARCHAR2(32767);
2676 --
2677 --
2678 l_number_of_errors NUMBER := 0;
2679 l_number_of_warnings NUMBER := 0;
2680 --
2681 --
2682 --}
2683 BEGIN
2684 --{
2685 --
2686 --
2687 -- Standard Start of API savepoint
2688 SAVEPOINT TRIP_SEGMENT_CHANGE_PUB;
2689 --
2690 -- Standard call to check for call compatibility.
2691 IF NOT FND_API.Compatible_API_Call
2692 (
2693 l_api_version,
2694 p_api_version,
2695 l_api_name,
2696 G_PKG_NAME
2697 )
2698 THEN
2699 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2700 END IF;
2701 --
2702 --
2703 -- Initialize message list if p_init_msg_list is set to TRUE.
2704 --
2705 --
2706 IF FND_API.to_Boolean( p_init_msg_list )
2707 THEN
2708 FND_MSG_PUB.initialize;
2709 END IF;
2710 --
2711 --
2712 -- Initialize API return status to success
2713 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2714 l_number_of_errors := 0;
2715 l_number_of_warnings := 0;
2716 p_action_type := p_tripSegmentChangeInRec.action_type;
2717 --
2718 --
2719 -- Start of API body.
2720 --
2721 --
2722 --
2723 IF p_action_type = G_ADD
2724 THEN
2725 --{
2726 NULL;
2727 --}
2728 ELSIF p_action_type = G_UPDATE
2729 THEN
2730 --{
2731 l_program_name := 'FTE_WSH_INTERFACE_PKG.VALIDATE_SEGMENT_UPDATE';
2732 --
2733 --
2734 validate_segment_update
2735 (
2736 P_old_trip_segment_rec => p_old_trip_segment_rec,
2737 P_new_trip_segment_rec => p_new_trip_segment_rec,
2738 X_return_status => l_return_status
2739 );
2740 --}
2741 ELSIF p_action_type = G_DELETE
2742 THEN
2743 --{
2744 l_program_name := 'FTE_WSH_INTERFACE_PKG.VALIDATE_SEGMENT_DELETE';
2745 --
2746 --
2747 validate_segment_delete
2748 (
2749 P_trip_segment_rec => p_old_trip_segment_rec,
2750 X_return_status => l_return_status
2751 );
2752 --}
2753 ELSE
2754 --{
2755 FND_MESSAGE.SET_NAME('FTE', 'FTE_WSH_IF_INVALID_ACTION');
2756 FND_MESSAGE.SET_TOKEN('ACTION_TYPE', p_action_type);
2757 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
2758 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2759 RAISE FND_API.G_EXC_ERROR;
2760 --RETURN;
2761 --}
2762 END IF;
2763 --
2764 --
2765 FTE_MLS_UTIL.api_post_call
2766 (
2767 p_api_name => l_program_name,
2768 p_api_return_status => l_return_status,
2769 p_message_name => 'FTE_SEGMENT_UNEXP_ERROR',
2770 p_trip_segment_id => p_old_trip_segment_rec.trip_id,
2771 p_trip_segment_name => p_old_trip_segment_rec.name,
2772 x_number_of_errors => l_number_of_errors,
2773 x_number_of_warnings => l_number_of_warnings,
2774 x_return_status => x_return_status
2775 );
2776 --
2777 --
2778 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
2779 THEN
2780 --{
2781 RAISE FND_API.G_EXC_ERROR;
2782 --RETURN;
2783 --}
2784 END IF;
2785 --
2786 --
2787 --
2788 --
2789 --
2790 --
2791 IF l_number_of_errors > 0
2792 THEN
2793 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2794 ELSIF l_number_of_warnings > 0
2795 THEN
2796 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2797 ELSE
2798 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2799 END IF;
2800 --
2801 --
2802 --
2803 --
2804 -- End of API body.
2805 --
2806 --
2807 -- Standard check of p_commit.
2808 --
2809 IF FND_API.To_Boolean( p_commit )
2810 THEN
2811 COMMIT WORK;
2812 END IF;
2813 --
2814 --
2815 -- Standard call to get message count and if count is 1,get message info.
2816 --
2817 FND_MSG_PUB.Count_And_Get
2818 (
2819 p_count => x_msg_count,
2820 p_data => x_msg_data,
2821 p_encoded => FND_API.G_FALSE
2822 );
2823 --
2824 --
2825 --}
2826 EXCEPTION
2827 --{
2828 WHEN FND_API.G_EXC_ERROR THEN
2829 ROLLBACK TO TRIP_SEGMENT_CHANGE_PUB;
2830 x_return_status := FND_API.G_RET_STS_ERROR ;
2831 FND_MSG_PUB.Count_And_Get
2832 (
2833 p_count => x_msg_count,
2834 p_data => x_msg_data,
2835 p_encoded => FND_API.G_FALSE
2836 );
2837 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2838 ROLLBACK TO TRIP_SEGMENT_CHANGE_PUB;
2839 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2840 FND_MSG_PUB.Count_And_Get
2841 (
2842 p_count => x_msg_count,
2843 p_data => x_msg_data,
2844 p_encoded => FND_API.G_FALSE
2845 );
2846 WHEN OTHERS THEN
2847 ROLLBACK TO TRIP_SEGMENT_CHANGE_PUB;
2848 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2849 wsh_util_core.default_handler('FTE_WSH_INTERFACE_PKG.TRIP_SEGMENT_CHANGE');
2850 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2851 --
2852 --
2853 FND_MSG_PUB.Count_And_Get
2854 (
2855 p_count => x_msg_count,
2856 p_data => x_msg_data,
2857 p_encoded => FND_API.G_FALSE
2858 );
2859 --}
2860 END trip_segment_change;
2861 --
2862 --
2863 --}
2864 --{
2865 -- Rel12 HBHAGAVA
2866
2867 -------------
2868 --========================================================================
2869 -- PROCEDURE : GET_ORG_ORGANIZATION_INFO
2870 --
2871 -- COMMENT : Return back org id and organization id associated with an
2872 -- entity: TRIP, STOP, DELIVERY, or DETAIL.
2873 -- CREATED BY: HBHAGAVA
2874 -- MODIFIED :
2875 -- DESC: This procedure returns back org and organization id for the
2876 -- entity.
2877 -- To get org id, calling API has to set P_ORG_ID_FLAG
2878 -- to FND_API.G_TRUE
2879 --========================================================================
2880
2881
2882 PROCEDURE GET_ORG_ORGANIZATION_INFO(
2883 p_init_msg_list IN VARCHAR2,
2884 x_return_status OUT NOCOPY VARCHAR2,
2885 x_msg_count OUT NOCOPY NUMBER,
2886 x_msg_data OUT NOCOPY VARCHAR2,
2887 x_organization_id OUT NOCOPY NUMBER,
2888 x_org_id OUT NOCOPY NUMBER,
2889 p_entity_id IN NUMBER,
2890 p_entity_type IN VARCHAR2,
2891 p_org_id_flag IN VARCHAR2 DEFAULT FND_API.G_FALSE)
2892 IS
2893
2894 l_api_name CONSTANT VARCHAR2(30) := 'GET_TRIP_ORG_ORGANIZATION';
2895 l_api_version CONSTANT NUMBER := 1.0;
2896 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
2897 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || l_api_name;
2898
2899
2900 l_return_status VARCHAR2(32767);
2901 l_msg_count NUMBER;
2902 l_msg_data VARCHAR2(32767);
2903 l_number_of_warnings NUMBER;
2904 l_number_of_errors NUMBER;
2905
2906 l_trip_id NUMBER;
2907 l_org_id NUMBER;
2908
2909
2910
2911 CURSOR GET_ORG_ID(c_organization_id NUMBER) IS
2912 SELECT DECODE(FPG.MULTI_ORG_FLAG,'Y',
2913 DECODE(HOI2.ORG_INFORMATION_CONTEXT,'Accounting Information', TO_NUMBER(HOI2.ORG_INFORMATION3),
2914 TO_NUMBER(NULL)),
2915 TO_NUMBER(NULL)) OPERATING_UNIT
2916 FROM HR_ORGANIZATION_UNITS HOU,
2917 HR_ORGANIZATION_INFORMATION HOI2,
2918 FND_PRODUCT_GROUPS FPG
2919 WHERE HOU.ORGANIZATION_ID = HOI2.ORGANIZATION_ID
2920 AND( HOI2.ORG_INFORMATION_CONTEXT || '')='Accounting Information'
2921 AND HOU.ORGANIZATION_ID =c_organization_id;
2922
2923 BEGIN
2924
2925
2926 IF l_debug_on THEN
2927 WSH_DEBUG_SV.push(l_module_name);
2928 END IF;
2929
2930 IF FND_API.to_Boolean( p_init_msg_list )
2931 THEN
2932 FND_MSG_PUB.initialize;
2933 END IF;
2934
2935 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2936 x_msg_count := 0;
2937 x_msg_data := 0;
2938 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2939 l_number_of_warnings := 0;
2940 l_number_of_errors := 0;
2941 l_trip_id := NULL;
2942 l_org_id := NULL;
2943
2944 IF l_debug_on
2945 THEN
2946 WSH_DEBUG_SV.logmsg(l_module_name,' FTE_MLS_UTIL.GET_TRIP_ORGANIZATION_ID for entity ' ||
2947 p_entity_id,
2948 WSH_DEBUG_SV.C_PROC_LEVEL);
2949 END IF;
2950
2951 IF (p_entity_type IN ('TRIP', 'STOP'))
2952 THEN
2953 --{
2954 IF (p_entity_type = 'TRIP') THEN
2955 l_trip_id := p_entity_id;
2956 ELSIF (p_entity_type = 'STOP')
2957 THEN
2958 BEGIN
2959 SELECT TRIP_ID INTO l_trip_id
2960 FROM WSH_TRIP_STOPS
2961 WHERE STOP_ID = p_entity_id;
2962
2963 IF (SQL%NOTFOUND) THEN
2964 RAISE NO_DATA_FOUND;
2965 END IF;
2966 EXCEPTION
2967 WHEN NO_DATA_FOUND THEN
2968 FND_MESSAGE.SET_NAME('FTE','FTE_INVALID_STOP');
2969 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2970 wsh_util_core.add_message(x_return_status);
2971 RAISE FND_API.G_EXC_ERROR;
2972 END;
2973 END IF;
2974
2975 x_organization_id := FTE_MLS_UTIL.GET_TRIP_ORGANIZATION_ID(l_trip_id);
2976
2977 --}
2978 ELSIF (p_entity_type = 'DELIVERY')
2979 THEN
2980 BEGIN
2981
2982 SELECT ORGANIZATION_ID INTO X_ORGANIZATION_ID
2983 FROM WSH_NEW_DELIVERIES
2984 WHERE DELIVERY_ID = p_entity_id;
2985
2986 IF (SQL%NOTFOUND) THEN
2987 RAISE NO_DATA_FOUND;
2988 END IF;
2989 EXCEPTION
2990 WHEN NO_DATA_FOUND THEN
2991 FND_MESSAGE.SET_NAME('FTE','FTE_INVALID_DLVY');
2992 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2993 wsh_util_core.add_message(x_return_status);
2994 RAISE FND_API.G_EXC_ERROR;
2995 END;
2996 ELSIF (p_entity_type = 'DETAIL')
2997 THEN
2998 BEGIN
2999
3000 SELECT ORGANIZATION_ID,
3001 ORG_ID INTO X_ORGANIZATION_ID, L_ORG_ID
3002 FROM WSH_DELIVERY_DETAILS
3003 WHERE DELIVERY_DETAIL_ID = p_entity_id;
3004
3005 IF (SQL%NOTFOUND) THEN
3006 RAISE NO_DATA_FOUND;
3007 END IF;
3008 EXCEPTION
3009 WHEN NO_DATA_FOUND THEN
3010 FND_MESSAGE.SET_NAME('FTE','FTE_INVALID_DETAIL');
3011 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3012 wsh_util_core.add_message(x_return_status);
3013 RAISE FND_API.G_EXC_ERROR;
3014 END;
3015 END IF;
3016
3017
3018 IF ( (X_ORGANIZATION_ID IS NOT NULL)
3019 AND (p_org_id_flag = FND_API.G_TRUE))
3020 THEN
3021 IF (l_org_id IS NOT NULL) THEN
3022 x_org_id := l_org_id;
3023 ELSE
3024 OPEN GET_ORG_ID(x_organization_id);
3025 FETCH GET_ORG_ID INTO x_org_id;
3026
3027 IF (GET_ORG_ID%NOTFOUND) THEN
3028 FND_MESSAGE.SET_NAME('FTE','FTE_ORG_ID_NOTFOUND');
3029 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3030 wsh_util_core.add_message(x_return_status);
3031 END IF;
3032
3033 CLOSE GET_ORG_ID;
3034 END IF;
3035
3036 END IF;
3037
3038 -- Standard call to get message count and if count is 1,get message info.
3039 --
3040 FND_MSG_PUB.Count_And_Get
3041 (
3042 p_count => x_msg_count,
3043 p_data => x_msg_data,
3044 p_encoded => FND_API.G_FALSE
3045 );
3046
3047
3048 --
3049 --
3050
3051 IF l_debug_on THEN
3052 WSH_DEBUG_SV.pop(l_module_name);
3053 END IF;
3054
3055 --}
3056 EXCEPTION
3057 --{
3058 WHEN FND_API.G_EXC_ERROR THEN
3059 x_return_status := FND_API.G_RET_STS_ERROR;
3060 FND_MSG_PUB.Count_And_Get
3061 (
3062 p_count => x_msg_count,
3063 p_data => x_msg_data,
3064 p_encoded => FND_API.G_FALSE
3065 );
3066 IF l_debug_on THEN
3067 WSH_DEBUG_SV.pop(l_module_name);
3068 END IF;
3069
3070 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3071 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3072 FND_MSG_PUB.Count_And_Get
3073 (
3074 p_count => x_msg_count,
3075 p_data => x_msg_data,
3076 p_encoded => FND_API.G_FALSE
3077 );
3078 IF l_debug_on THEN
3079 WSH_DEBUG_SV.pop(l_module_name);
3080 END IF;
3081 WHEN OTHERS THEN
3082 wsh_util_core.default_handler('FTE_WSH_INTERFACE_PKG.GET_TRIP_ORG_ORGANIZATION');
3083 x_return_status := FND_API.G_RET_STS_ERROR;
3084 FND_MSG_PUB.Count_And_Get
3085 (
3086 p_count => x_msg_count,
3087 p_data => x_msg_data,
3088 p_encoded => FND_API.G_FALSE
3089 );
3090 IF l_debug_on THEN
3091 WSH_DEBUG_SV.pop(l_module_name);
3092 END IF;
3093
3094 END GET_ORG_ORGANIZATION_INFO;
3095
3096 --}
3097
3098 END FTE_WSH_INTERFACE_PKG;