[Home] [Help]
PACKAGE BODY: APPS.FTE_WSH_TRIPS_PVT
Source
1 PACKAGE BODY FTE_WSH_TRIPS_PVT AS
2 /* $Header: FTEFWTHB.pls 115.11 2002/12/03 21:48:40 hbhagava noship $ */
3
4
5 procedure set_return_status
6 (
7 p_return_status IN VARCHAR2,
8 x_return_status IN OUT NOCOPY VARCHAR2
9 ) is
10 begin
11
12 if (p_return_status = null or p_return_status = FND_API.G_MISS_CHAR) then
13 return;
14 end if;
15
16 if (p_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
17 and p_return_status <> WSH_UTIL_CORE.G_RET_STS_ERROR
18 and p_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
19 and p_return_status <> WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) then
20 return;
21 end if;
22
23 if (x_return_status = null or x_return_status = FND_API.G_MISS_CHAR) then
24 x_return_status := p_return_status;
25 return;
26 end if;
27
28 if (x_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR
29 or x_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) then
30 return;
31 end if;
32
33 if ((x_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS
34 or x_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING)
35 and (p_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR
36 or p_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) then
37 x_return_status := p_return_status;
38 return;
39 end if;
40
41 if (x_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS
42 and p_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) then
43 x_return_status := p_return_status;
44 return;
45 end if;
46 end set_return_status;
47
48 --
49 -- Procedure: Validate_PK Private
50 -- Parameters: p_fte_trip_id
51 -- p_wsh_trip_id
52 -- x_return_status return_status
53 -- Description: This procedure will validate the primary key
54 --
55
56 PROCEDURE Validate_PK
57 (
58 p_fte_trip_id IN NUMBER,
59 p_wsh_trip_id IN NUMBER,
60 x_return_status OUT NOCOPY VARCHAR2
61 ) IS
62
63 CURSOR check_fte_trip_id (v_trip_id NUMBER) IS
64 SELECT fte_trip_id FROM fte_trips
65 WHERE fte_trip_id = v_trip_id;
66
67 CURSOR check_wsh_trip_id (v_trip_id NUMBER) IS
68 SELECT trip_id FROM wsh_trips
69 WHERE trip_id = v_trip_id;
70
71 invalid_fte_trip_id EXCEPTION;
72 invalid_wsh_trip_id EXCEPTION;
73
74 l_number NUMBER;
75
76 BEGIN
77
78 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
79
80 --validate fte_trip_id
81
82 OPEN check_fte_trip_id(p_fte_trip_id);
83 FETCH check_fte_trip_id INTO l_number;
84
85 IF (check_fte_trip_id%NOTFOUND) THEN
86 CLOSE check_fte_trip_id;
87 RAISE invalid_fte_trip_id;
88 END IF;
89
90 CLOSE check_fte_trip_id;
91
92 --validate wsh_trip_id
93
94 OPEN check_wsh_trip_id(p_wsh_trip_id);
95 FETCH check_wsh_trip_id INTO l_number;
96
97 IF (check_wsh_trip_id%NOTFOUND) THEN
98 CLOSE check_wsh_trip_id;
99 RAISE invalid_wsh_trip_id;
100 END IF;
101
102 CLOSE check_wsh_trip_id;
103
104 EXCEPTION
105 WHEN invalid_fte_trip_id THEN
106 FND_MESSAGE.SET_NAME('FTE', 'FTE_NO_TRIP_ID');
107 FND_MESSAGE.SET_TOKEN('TRIP_ID',p_fte_trip_id);
108 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
109 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
110 WHEN invalid_wsh_trip_id THEN
111 FND_MESSAGE.SET_NAME('FTE', 'FTE_NO_TRIP_SEGMENT_ID');
112 FND_MESSAGE.SET_TOKEN('TRIP_SEGMENT_ID',p_wsh_trip_id);
113 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
114 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
115 WHEN others THEN
116 wsh_util_core.default_handler('FTE_WSH_TRIPS_PUB.Validate_PK');
117 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
118 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
119
120 END Validate_PK;
121
122 --
123 -- Procedure: Validate_Sequence Private
124 -- Parameters: p_fte_trip_id
125 -- p_wsh_trip_id
126 -- p_sequence
127 -- x_return_status return_status
128 -- Description: This procedure will validate the sequence is unique
129 -- for the same fte_trip_id and wsh_trip_id
130 -- assumes PK is valid.
131 --
132
133 PROCEDURE Validate_Sequence
134 (
135 p_fte_trip_id IN NUMBER,
136 p_sequence IN NUMBER,
137 x_return_status OUT NOCOPY VARCHAR2
138 ) IS
139
140 CURSOR check_sequence (v_fte_trip_id NUMBER, v_sequence NUMBER) IS
141 SELECT fte_trip_id FROM fte_wsh_trips
142 WHERE fte_trip_id = v_fte_trip_id and sequence_number = v_sequence;
143
144 empty_sequence EXCEPTION;
145 duplicate_sequence EXCEPTION;
146
147 l_number NUMBER;
148
149 BEGIN
150
151 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
152
153 --validate sequence
154
155 if (p_sequence = null or p_sequence = FND_API.G_MISS_NUM) then
156 RAISE empty_sequence;
157 end if;
158
159 OPEN check_sequence(p_fte_trip_id, p_sequence);
160 FETCH check_sequence INTO l_number;
161
162 IF (check_sequence%FOUND) THEN
163 CLOSE check_sequence;
164 RAISE duplicate_sequence;
165 END IF;
166
167 CLOSE check_sequence;
168
169 EXCEPTION
170 WHEN empty_sequence THEN
171 FND_MESSAGE.SET_NAME('FTE', 'FTE_SEGMENT_SEQUENCE_MISSING');
172 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
173 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
174 WHEN duplicate_sequence THEN
175 FND_MESSAGE.SET_NAME('FTE', 'FTE_SEGMENT_SEQUENCE_DUP');
176 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
177 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
178 WHEN others THEN
179 wsh_util_core.default_handler('FTE_WSH_TRIPS_PUB.Validate_Sequence');
180 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
181 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
182
183 END Validate_Sequence;
184
185 FUNCTION Get_Fte_Trip_Status
186 (
187 p_trip_id IN NUMBER
188 )
189 RETURN VARCHAR2
190 IS
191
192 CURSOR get_status IS
193 SELECT status_code
194 FROM fte_trips
195 WHERE fte_trip_id = p_trip_id;
196
197 x_status VARCHAR2(30);
198
199 others EXCEPTION;
200
201 BEGIN
202
203 IF (p_trip_id IS NULL) THEN
204 raise others;
205 END IF;
206
207 OPEN get_status;
208 FETCH get_status INTO x_status;
209 CLOSE get_status;
210
211 IF (x_status IS NULL) THEN
212 FND_MESSAGE.SET_NAME('FTE','FTE_NO_TRIP_ID');
213 FND_MESSAGE.SET_TOKEN('TRIP_ID',p_trip_id);
214 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR);
215 RETURN null;
216 END IF;
217
218 RETURN x_status;
219
220 EXCEPTION
221
222 WHEN others THEN
223 wsh_util_core.default_handler('FTE_WSH_TRIPS_PUB.GET_FTE_TRIP_STATUS');
224 RETURN null;
225
226 END Get_Fte_Trip_Status;
227
228 --
229 -- Procedure: Validate_Trip
230 -- Parameters: p_trip_info Trip Record info
231 -- p_action_code 'CREATE' or 'UPDATE'
232 -- x_return_status return_status
233 -- Description: This procedure will validate a fte_wsh_trip.
234 --
235
236 PROCEDURE Validate_Trip
237 (
238 p_trip_info IN fte_wsh_trip_rec_type,
239 p_action_code IN VARCHAR2,
240 x_return_status OUT NOCOPY VARCHAR2
241 ) IS
242
243 l_status VARCHAR2(30);
244 l_previous_segment_id NUMBER;
245 l_previous_sequence_number NUMBER;
246 l_next_segment_id NUMBER;
247 l_next_sequence_number NUMBER;
248 l_trip_name VARCHAR2(30);
249 l_trip_segment_name VARCHAR2(30);
250 l_first_stop_location_id NUMBER;
251 l_last_stop_location_id NUMBER;
252 l_stop_count NUMBER;
253 l_connected BOOLEAN;
254 l_fte_trip_name VARCHAR2(30);
255 l_wsh_trip_name VARCHAR2(30);
256 l_return_status VARCHAR2(1);
257
258 --l_msg_count NUMBER;
259 --l_msg_data VARCHAR2(32767);
260
261 add_segment_to_closed_trip EXCEPTION;
262 add_segment_to_in_transit_trip EXCEPTION;
263 update_sequence_for_non_open EXCEPTION;
264 segment_less_than_2_stops EXCEPTION;
265
266 BEGIN
267
268 wsh_debug_sv.dpush (c_sdebug, 'Validate_Trip');
269
270 wsh_debug_sv.dlog (c_debug,'FteTripId ',p_trip_info.fte_trip_id);
271 wsh_debug_sv.dlog (c_debug,'WshTripId ',p_trip_info.wsh_trip_id);
272 wsh_debug_sv.dlog (c_debug,'SequenceNumber ',p_trip_info.sequence_number);
273 wsh_debug_sv.dlog (c_debug,'ActionCode ',p_action_code);
274
275 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
276
277 wsh_debug_sv.dlog (c_debug,'about to get segment name...');
278
279 fte_mls_util.get_trip_segment_name
280 (
281 p_trip_segment_id => p_trip_info.WSH_TRIP_ID,
282 x_trip_segment_name => l_wsh_trip_name,
283 x_return_status => x_return_status
284 );
285
286 wsh_debug_sv.dlog (c_debug,'segment name',l_wsh_trip_name);
287 wsh_debug_sv.dlog (c_debug,'x_return_status',x_return_status);
288
289 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
290 RETURN;
291 END IF;
292
293 wsh_debug_sv.dlog (c_debug,'about to get trip name...');
294
295 fte_trips_pvt.get_trip_name
296 (
297 p_trip_id => p_trip_info.FTE_TRIP_ID,
298 x_trip_name => l_fte_trip_name,
299 x_return_status => x_return_status
300 );
301
302 wsh_debug_sv.dlog (c_debug,'trip name',l_fte_trip_name);
303 wsh_debug_sv.dlog (c_debug,'x_return_status',x_return_status);
304
305 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
306 RETURN;
307 END IF;
308
309 wsh_debug_sv.dlog (c_debug,'about to validate PK...');
310
311 Validate_PK
312 (
313 p_trip_info.FTE_TRIP_ID,
314 p_trip_info.WSH_TRIP_ID,
315 x_return_status
316 );
317
318 wsh_debug_sv.dlog (c_debug,'x_return_status',x_return_status);
319
320 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
321 RETURN;
322 END IF;
323
324 wsh_debug_sv.dlog (c_debug,'about to validate sequence...');
325
326 Validate_Sequence
327 (
328 p_trip_info.FTE_TRIP_ID,
329 p_trip_info.SEQUENCE_NUMBER,
330 x_return_status
331 );
332
333 wsh_debug_sv.dlog (c_debug,'x_return_status',x_return_status);
334
335 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
336 RETURN;
337 END IF;
338
339 wsh_debug_sv.dlog (c_debug,'about to validate status...');
340
341 -- Segment can only be added to open trip, (insert)
342 -- Segment's sequence can only be added to open trip (upadte)
343 l_status := Get_Fte_Trip_Status(p_trip_info.FTE_TRIP_ID);
344
345 if (p_action_code = 'CREATE') then
346
347 -- segment can not be added to closed trip
348 if (l_status = 'CL') then
349 RAISE add_segment_to_closed_trip;
350 end if;
351
352 -- segment can only be added to the end of in-transit trip
353 if (l_status = 'IT') then
354 fte_mls_util.get_next_segment_id
355 (
356 p_trip_segment_id => p_trip_info.WSH_TRIP_ID,
357 p_sequence_number => p_trip_info.SEQUENCE_NUMBER,
358 p_trip_id => p_trip_info.FTE_TRIP_ID,
359 x_trip_name => l_trip_name,
360 x_trip_segment_name => l_trip_segment_name,
361 x_next_segment_id => l_next_segment_id,
362 x_return_status => l_return_status
363 );
364
365 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
366 AND l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
367 set_return_status
368 (
369 l_return_status,
370 x_return_status
371 );
372 RETURN;
373 END IF;
374
375 if (l_next_segment_id is NOT null) then
376 RAISE add_segment_to_in_transit_trip;
377 end if;
378
379 end if;
380
381 elsif (p_action_code = 'UPDATE') then
382
383 -- sequence can only be updated for open trip
384 if (l_status <> 'OP') then
385 RAISE update_sequence_for_non_open;
386 end if;
387
388 end if; -- p_action_code = 'CREATE' or 'UPDATE'
389
390 wsh_debug_sv.dlog (c_debug,'x_return_status',x_return_status);
391
392 wsh_debug_sv.dlog (c_debug,'about to validate 2 stops...');
393
394 --check at least two stops for the segment
395 select count(*) into l_stop_count
396 from wsh_trip_stops
397 where trip_id = p_trip_info.WSH_TRIP_ID;
398
399 wsh_debug_sv.dlog (c_debug,'wsh_trip_id',p_trip_info.WSH_TRIP_ID);
400 wsh_debug_sv.dlog (c_debug,'stop count',l_stop_count);
401
402 if (l_stop_count < 2) then
403 update fte_trips
404 set validation_required = 'Y'
405 where fte_trip_id = p_trip_info.FTE_TRIP_ID;
406
407 -- give warning
408 wsh_debug_sv.dlog (c_debug,'about to set the warning message...');
409
410 FND_MESSAGE.SET_NAME('FTE', 'FTE_SEGMENT_NO_TWO_STOPS');
411 FND_MESSAGE.SET_TOKEN('WSH_TRIP_NAME',l_wsh_trip_name);
412 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
413 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_WARNING);
414
415 return;
416 end if;
417
418 wsh_debug_sv.dlog (c_debug,'about to validate connections...');
419
420 --check connections
421
422 l_connected := true;
423
424 if (p_action_code = 'CREATE') then
425
426 wsh_debug_sv.dlog (c_debug,'about to get_previous_segment_id...');
427
428 fte_mls_util.get_previous_segment_id
429 (
430 p_trip_segment_id => p_trip_info.WSH_TRIP_ID,
431 p_sequence_number => p_trip_info.SEQUENCE_NUMBER,
432 p_trip_id => p_trip_info.FTE_TRIP_ID,
433 x_trip_name => l_trip_name,
434 x_trip_segment_name => l_trip_segment_name,
435 x_previous_segment_id => l_previous_segment_id,
436 x_return_status => l_return_status
437 );
438
439 wsh_debug_sv.dlog (c_debug,'previous segment id', l_previous_segment_id);
440 wsh_debug_sv.dlog (c_debug,'return status from get_previous_segment_id', l_return_status);
441
442 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
443 AND l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
444 set_return_status
445 (
446 l_return_status,
447 x_return_status
448 );
449 RETURN;
450 END IF;
451
452 wsh_debug_sv.dlog (c_debug,'return status', x_return_status);
453
454 if (l_previous_segment_id is NOT null) then
455
456 select sequence_number into l_previous_sequence_number
457 from fte_wsh_trips
458 where fte_trip_id = p_trip_info.FTE_TRIP_ID
459 and wsh_trip_id = l_previous_segment_id;
460
461 wsh_debug_sv.dlog (c_debug,'about to get_first_stop_location_id...');
462
463 fte_mls_util.get_first_stop_location_id
464 (
465 p_trip_segment_id => p_trip_info.WSH_TRIP_ID,
466 x_trip_segment_name => l_trip_segment_name,
467 x_first_stop_location_id => l_first_stop_location_id,
468 x_return_status => l_return_status
469 );
470
471 wsh_debug_sv.dlog (c_debug,'first stop loc id',l_first_stop_location_id);
472 wsh_debug_sv.dlog (c_debug,'return status from get_first_stop_location_id',l_return_status);
473
474 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
475 AND l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
476 set_return_status
477 (
478 l_return_status,
479 x_return_status
480 );
481 RETURN;
482 END IF;
483
484 wsh_debug_sv.dlog (c_debug,'return status', x_return_status);
485
486 wsh_debug_sv.dlog (c_debug,'about to check_previous_segment...');
487
488 fte_mls_util.check_previous_segment
489 (
490 p_trip_id => p_trip_info.FTE_TRIP_ID,
491 p_trip_segment_id => l_previous_segment_id,
492 p_sequence_number => l_previous_sequence_number,
493 p_first_stop_location_id => l_first_stop_location_id,
494 x_trip_name => l_trip_name,
495 x_trip_segment_name => l_trip_segment_name,
496 x_connected => l_connected,
497 x_return_status => l_return_status
498 );
499
500 wsh_debug_sv.dlog (c_debug,'l_connected',l_connected);
501 wsh_debug_sv.dlog (c_debug,'return_status from check_previous_segment',l_return_status);
502
503 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
504 AND l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
505 set_return_status
506 (
507 l_return_status,
508 x_return_status
509 );
510 RETURN;
511 END IF;
512
513 wsh_debug_sv.dlog (c_debug,'return status', x_return_status);
514
515 if (l_connected is null or l_connected = false) then
516
517 wsh_debug_sv.dlog (c_debug,'about to mark trip invalid...');
518
519 update fte_trips
520 set validation_required = 'Y'
521 where fte_trip_id = p_trip_info.FTE_TRIP_ID;
522
523 wsh_debug_sv.dlog (c_debug,'about to set warning message...');
524
525 -- give warning
526 FND_MESSAGE.SET_NAME('FTE', 'FTE_SEGMENT_PREV_CONNECT_ERROR');
527 FND_MESSAGE.SET_TOKEN('TRIP_NAME',l_fte_trip_name);
528 FND_MESSAGE.SET_TOKEN('TRIP_SEGMENT_NAME',l_wsh_trip_name);
529 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_WARNING);
530 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
531 end if;
532
533 end if; -- l_previous_segment_id is NOT null
534
535 wsh_debug_sv.dlog (c_debug,'about to get_next_segment_id...');
536
537 fte_mls_util.get_next_segment_id
538 (
539 p_trip_segment_id => p_trip_info.WSH_TRIP_ID,
540 p_sequence_number => p_trip_info.SEQUENCE_NUMBER,
541 p_trip_id => p_trip_info.FTE_TRIP_ID,
542 x_trip_name => l_trip_name,
543 x_trip_segment_name => l_trip_segment_name,
544 x_next_segment_id => l_next_segment_id,
545 x_return_status => l_return_status
546 );
547
548 wsh_debug_sv.dlog (c_debug,'next segment id', l_next_segment_id);
549 wsh_debug_sv.dlog (c_debug,'return status from get_next_segment_id', l_return_status);
550
551 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
552 AND l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
553 set_return_status
554 (
555 l_return_status,
556 x_return_status
557 );
558 RETURN;
559 END IF;
560
561 wsh_debug_sv.dlog (c_debug,'return status', x_return_status);
562
563 if (l_next_segment_id is NOT null) then
564
565 select sequence_number into l_next_sequence_number
566 from fte_wsh_trips
567 where fte_trip_id = p_trip_info.FTE_TRIP_ID
568 and wsh_trip_id = l_next_segment_id;
569
570 wsh_debug_sv.dlog (c_debug,'about to get_last_stop_location_id...');
571
572 fte_mls_util.get_last_stop_location_id
573 (
574 p_trip_segment_id => p_trip_info.WSH_TRIP_ID,
575 x_trip_segment_name => l_trip_segment_name,
576 x_last_stop_location_id => l_last_stop_location_id,
577 x_return_status => l_return_status
578 );
579
580 wsh_debug_sv.dlog (c_debug,'last stop loc id',l_last_stop_location_id);
581 wsh_debug_sv.dlog (c_debug,'return status from get_last_stop_location_id',l_return_status);
582
583 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
584 AND l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
585 set_return_status
586 (
587 l_return_status,
588 x_return_status
589 );
590 RETURN;
591 END IF;
592
593 wsh_debug_sv.dlog (c_debug,'return status', x_return_status);
594
595 wsh_debug_sv.dlog (c_debug,'about to check_next_segment...');
596
597 fte_mls_util.check_next_segment
598 (
599 p_trip_id => p_trip_info.FTE_TRIP_ID,
600 p_trip_segment_id => l_next_segment_id,
601 p_sequence_number => l_next_sequence_number,
602 p_last_stop_location_id => l_last_stop_location_id,
603 x_trip_name => l_trip_name,
604 x_trip_segment_name => l_trip_segment_name,
605 x_connected => l_connected,
606 x_return_status => l_return_status
607 );
608
609 wsh_debug_sv.dlog (c_debug,'l_connected',l_connected);
610 wsh_debug_sv.dlog (c_debug,'return_status from check_next_segment',l_return_status);
611
612 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
613 AND l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
614 set_return_status
615 (
616 l_return_status,
617 x_return_status
618 );
619 RETURN;
620 END IF;
621
622 wsh_debug_sv.dlog (c_debug,'return status', x_return_status);
623
624 if (l_connected is null or l_connected = false) then
625
626 wsh_debug_sv.dlog (c_debug,'about to mark trip invalid...');
627
628 update fte_trips
629 set validation_required = 'Y'
630 where fte_trip_id = p_trip_info.FTE_TRIP_ID;
631
632 wsh_debug_sv.dlog (c_debug,'about to set warning message...');
633
634 -- give warning
635 FND_MESSAGE.SET_NAME('FTE', 'FTE_SEGMENT_NEXT_CONNECT_ERROR');
636 FND_MESSAGE.SET_TOKEN('TRIP_NAME',l_fte_trip_name);
637 FND_MESSAGE.SET_TOKEN('TRIP_SEGMENT_NAME',l_wsh_trip_name);
638 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
639 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
640
641 end if;
642
643 end if; -- l_next_segment_id is NOT null
644
645 end if; -- p_action_code = 'CREATE'
646
647 wsh_debug_sv.dlog (c_debug,'validate trip done');
648
649 wsh_debug_sv.dlog (c_debug,'Return Status',x_Return_Status);
650 wsh_debug_sv.dpop (c_sdebug);
651 wsh_debug_sv.stop_debug;
652
653 EXCEPTION
654 WHEN add_segment_to_closed_trip THEN
655 FND_MESSAGE.SET_NAME('FTE', 'FTE_INVALID_TRIP_STATUS');
656 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
657 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
658
659 wsh_debug_sv.dlog (c_debug,'Return Status',x_Return_Status);
660 wsh_debug_sv.dpop (c_sdebug);
661 wsh_debug_sv.stop_debug;
662
663 WHEN add_segment_to_in_transit_trip THEN
664 FND_MESSAGE.SET_NAME('FTE', 'FTE_INVALID_TRIP_STATUS2');
665 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
666 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
667
668 wsh_debug_sv.dlog (c_debug,'Return Status',x_Return_Status);
669 wsh_debug_sv.dpop (c_sdebug);
670 wsh_debug_sv.stop_debug;
671
672 WHEN update_sequence_for_non_open THEN
673 FND_MESSAGE.SET_NAME('FTE', 'FTE_INVALID_TRIP_STATUS3');
674 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
675 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
676
677 wsh_debug_sv.dlog (c_debug,'Return Status',x_Return_Status);
678 wsh_debug_sv.dpop (c_sdebug);
679 wsh_debug_sv.stop_debug;
680
681 WHEN others THEN
682 wsh_util_core.default_handler('FTE_WSH_TRIPS_PUB.Validate_Trip');
683 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
684 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
685
686 wsh_debug_sv.dlog (c_debug,'Return Status',x_Return_Status);
687 wsh_debug_sv.dpop (c_sdebug);
688 wsh_debug_sv.stop_debug;
689
690 END Validate_Trip;
691
692 --
693 -- Procedure: Create_Trip
694 -- Parameters: p_trip_info Trip Record info
695 -- x_return_status return_status
696 -- Description: This procedure will create a fte_wsh_trip.
697 --
698
699 PROCEDURE Create_Trip
700 (
701 p_trip_info IN fte_wsh_trip_rec_type,
702 x_return_status OUT NOCOPY VARCHAR2
703 ) IS
704 BEGIN
705
706 wsh_debug_sv.dpush (c_sdebug, 'Create_Trip');
707
708 wsh_debug_sv.dlog (c_debug,'FteTripId ',p_trip_info.fte_trip_id);
709 wsh_debug_sv.dlog (c_debug,'WshTripId ',p_trip_info.wsh_trip_id);
710 wsh_debug_sv.dlog (c_debug,'SequenceNumber ',p_trip_info.sequence_number);
711
712 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
713
714 wsh_debug_sv.dlog (c_debug,'about to validate trip...');
715
716 Validate_Trip(p_trip_info, 'CREATE', x_return_status);
717 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
718 AND x_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
719 RETURN;
720 END IF;
721
722 wsh_debug_sv.dlog (c_debug,'about to insert into fte_wsh_trips table...');
723
724 insert into fte_wsh_trips
725 (
726 FTE_TRIP_ID ,
727 WSH_TRIP_ID ,
728 SEQUENCE_NUMBER ,
729 CREATION_DATE ,
730 CREATED_BY ,
731 LAST_UPDATE_DATE ,
732 LAST_UPDATED_BY ,
733 LAST_UPDATE_LOGIN ,
734 PROGRAM_APPLICATION_ID ,
735 PROGRAM_ID ,
736 PROGRAM_UPDATE_DATE ,
737 REQUEST_ID ,
738 ATTRIBUTE_CATEGORY ,
739 ATTRIBUTE1 ,
740 ATTRIBUTE2 ,
741 ATTRIBUTE3 ,
742 ATTRIBUTE4 ,
743 ATTRIBUTE5 ,
744 ATTRIBUTE6 ,
745 ATTRIBUTE7 ,
746 ATTRIBUTE8 ,
747 ATTRIBUTE9 ,
748 ATTRIBUTE10 ,
749 ATTRIBUTE11 ,
750 ATTRIBUTE12 ,
751 ATTRIBUTE13 ,
752 ATTRIBUTE14 ,
753 ATTRIBUTE15
754 )
755 values
756 (
757 decode(p_trip_info.FTE_TRIP_ID, FND_API.G_MISS_NUM, NULL, p_trip_info.FTE_TRIP_ID),
758 decode(p_trip_info.WSH_TRIP_ID, FND_API.G_MISS_NUM, NULL, p_trip_info.WSH_TRIP_ID),
759 decode(p_trip_info.SEQUENCE_NUMBER, FND_API.G_MISS_NUM, NULL, p_trip_info.SEQUENCE_NUMBER),
760 decode(p_trip_info.creation_date, FND_API.G_MISS_DATE, SYSDATE, NULL, SYSDATE, p_trip_info.creation_date),
761 decode(p_trip_info.created_by,FND_API.G_MISS_NUM, FND_GLOBAL.USER_ID, NULL, FND_GLOBAL.USER_ID, p_trip_info.created_by),
762 decode(p_trip_info.last_update_date,FND_API.G_MISS_DATE, SYSDATE, NULL, SYSDATE, p_trip_info.creation_date),
763 decode(p_trip_info.last_updated_by,FND_API.G_MISS_NUM, FND_GLOBAL.USER_ID, NULL, FND_GLOBAL.USER_ID, p_trip_info.last_updated_by),
764 decode(p_trip_info.last_update_login,FND_API.G_MISS_NUM, FND_GLOBAL.LOGIN_ID, NULL, FND_GLOBAL.LOGIN_ID, p_trip_info.last_update_login),
765 decode(p_trip_info.program_application_id, FND_API.G_MISS_NUM, NULL, p_trip_info.program_application_id),
766 decode(p_trip_info.program_id, FND_API.G_MISS_NUM, NULL, p_trip_info.program_id),
767 decode(p_trip_info.program_update_date, FND_API.G_MISS_DATE, NULL, p_trip_info.program_update_date),
768 decode(p_trip_info.request_id, FND_API.G_MISS_NUM, NULL, p_trip_info.request_id),
769 decode(p_trip_info.attribute_category, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute_category),
770 decode(p_trip_info.attribute1, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute1),
771 decode(p_trip_info.attribute2, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute2),
772 decode(p_trip_info.attribute3, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute3),
773 decode(p_trip_info.attribute4, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute4),
774 decode(p_trip_info.attribute5, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute5),
775 decode(p_trip_info.attribute6, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute6),
776 decode(p_trip_info.attribute7, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute7),
777 decode(p_trip_info.attribute8, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute8),
778 decode(p_trip_info.attribute9, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute9),
779 decode(p_trip_info.attribute10, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute10),
780 decode(p_trip_info.attribute11, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute11),
781 decode(p_trip_info.attribute12, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute12),
782 decode(p_trip_info.attribute13, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute13),
783 decode(p_trip_info.attribute14, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute14),
784 decode(p_trip_info.attribute15, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute15)
785 );
786
787 IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
788
789 wsh_debug_sv.dlog (c_debug,'about to mark trip invalid...');
790
791 --mark trip as invalid
792 update fte_trips
793 set validation_required = 'Y'
794 where fte_trip_id = p_trip_info.FTE_TRIP_ID;
795 END IF;
796
797 wsh_debug_sv.dlog (c_debug,'Return Status',x_Return_Status);
798 wsh_debug_sv.dpop (c_sdebug);
799 wsh_debug_sv.stop_debug;
800
801 EXCEPTION
802 WHEN others THEN
803 wsh_util_core.default_handler('FTE_WSH_TRIPS_PUB.Create_Trip');
804 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
805 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
806
807 wsh_debug_sv.dlog (c_debug,'Return Status',x_Return_Status);
808 wsh_debug_sv.dpop (c_sdebug);
809 wsh_debug_sv.stop_debug;
810
811 END Create_Trip;
812
813 --
814 -- Procedure: Update_Trip
815 -- Parameters: p_trip_info Trip Record info
816 -- p_validate_flag 'Y' validate before update
817 -- x_return_status return_status
818 -- Description: This procedure will update a fte_wsh_trip.
819 --
820
821 PROCEDURE Update_Trip
822 (
823 p_trip_info IN fte_wsh_trip_rec_type,
824 p_validate_flag IN VARCHAR2 DEFAULT 'Y',
825 x_return_status OUT NOCOPY VARCHAR2
826 ) IS
827
828 no_trip_found EXCEPTION;
829
830 BEGIN
831
832 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
833
834 if (p_validate_flag = 'Y') then
835
836 Validate_Trip(p_trip_info, 'UPDATE', x_return_status);
837 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
838 AND x_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
839 RETURN;
840 END IF;
841
842 end if;
843
844 update fte_wsh_trips set
845 SEQUENCE_NUMBER = decode(p_trip_info.SEQUENCE_NUMBER, FND_API.G_MISS_NUM, NULL, p_trip_info.SEQUENCE_NUMBER),
846 creation_date = decode(p_trip_info.creation_date, FND_API.G_MISS_DATE, SYSDATE, NULL, SYSDATE, p_trip_info.creation_date),
847 created_by = decode(p_trip_info.created_by,FND_API.G_MISS_NUM, FND_GLOBAL.USER_ID, NULL, FND_GLOBAL.USER_ID, p_trip_info.created_by),
848 last_update_date = decode(p_trip_info.last_update_date,FND_API.G_MISS_DATE, SYSDATE, NULL, SYSDATE, p_trip_info.creation_date),
849 last_updated_by = decode(p_trip_info.last_updated_by,FND_API.G_MISS_NUM, FND_GLOBAL.USER_ID, NULL, FND_GLOBAL.USER_ID, p_trip_info.last_updated_by),
850 last_update_login = decode(p_trip_info.last_update_login,FND_API.G_MISS_NUM, FND_GLOBAL.LOGIN_ID, NULL, FND_GLOBAL.LOGIN_ID, p_trip_info.last_update_login),
851 program_application_id = decode(p_trip_info.program_application_id, FND_API.G_MISS_NUM, NULL, p_trip_info.program_application_id),
852 program_id = decode(p_trip_info.program_id, FND_API.G_MISS_NUM, NULL, p_trip_info.program_id),
853 program_update_date = decode(p_trip_info.program_update_date, FND_API.G_MISS_DATE, NULL, p_trip_info.program_update_date),
854 request_id = decode(p_trip_info.request_id, FND_API.G_MISS_NUM, NULL, p_trip_info.request_id),
855 attribute_category = decode(p_trip_info.attribute_category, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute_category),
856 attribute1 = decode(p_trip_info.attribute1, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute1),
857 attribute2 = decode(p_trip_info.attribute2, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute2),
858 attribute3 = decode(p_trip_info.attribute3, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute3),
859 attribute4 = decode(p_trip_info.attribute4, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute4),
860 attribute5 = decode(p_trip_info.attribute5, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute5),
861 attribute6 = decode(p_trip_info.attribute6, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute6),
862 attribute7 = decode(p_trip_info.attribute7, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute7),
863 attribute8 = decode(p_trip_info.attribute8, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute8),
864 attribute9 = decode(p_trip_info.attribute9, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute9),
865 attribute10 = decode(p_trip_info.attribute10, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute10),
866 attribute11 = decode(p_trip_info.attribute11, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute11),
867 attribute12 = decode(p_trip_info.attribute12, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute12),
868 attribute13 = decode(p_trip_info.attribute13, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute13),
869 attribute14 = decode(p_trip_info.attribute14, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute14),
870 attribute15 = decode(p_trip_info.attribute15, FND_API.G_MISS_CHAR, NULL, p_trip_info.attribute15)
871 where fte_trip_id = p_trip_info.fte_trip_id and wsh_trip_id = p_trip_info.wsh_trip_id;
872
873 if (SQL%NOTFOUND) then
874 RAISE no_trip_found;
875 end if;
876
877 EXCEPTION
878 WHEN no_trip_found THEN
879 FND_MESSAGE.SET_NAME('FTE','FTE_NO_TRIP_SEGMENT_ID');
880 FND_MESSAGE.SET_TOKEN('TRIP_SEGMENT_ID',p_trip_info.WSH_TRIP_ID);
881 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
882 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
883 WHEN others THEN
884 wsh_util_core.default_handler('FTE_WSH_TRIPS_PUB.Update_Trip');
885 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
886 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
887
888 END Update_Trip;
889
890 --
891 -- Procedure: Delete_Trip
892 -- Parameters: p_fte_trip_id
893 -- p_wsh_trip_id
894 -- x_return_status return_status
895 -- Description: This procedure will create a fte_wsh_trip.
896 --
897
898 PROCEDURE Validate_Trip_For_Delete
899 (
900 P_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
901 p_fte_trip_id IN NUMBER,
902 p_wsh_trip_id IN NUMBER,
903 x_msg_count OUT NOCOPY NUMBER,
904 x_msg_data OUT NOCOPY VARCHAR2,
905 x_return_status OUT NOCOPY VARCHAR2
906 ) IS
907
908 l_status VARCHAR2(30);
909 l_previous_segment_id NUMBER;
910 l_next_segment_id NUMBER;
911 l_sequence_number NUMBER;
912 l_trip_name VARCHAR2(30);
913 l_trip_segment_name VARCHAR2(30);
914 l_fte_trip_name VARCHAR2(30);
915 l_wsh_trip_name VARCHAR2(30);
916 l_return_status VARCHAR2(1);
917
918 invalid_trip_status EXCEPTION;
919
920 BEGIN
921
922 --
923 --
924 -- Initialize message list if p_init_msg_list is set to TRUE.
925 --
926 --
927 IF FND_API.to_Boolean( p_init_msg_list )
928 THEN
929 FND_MSG_PUB.initialize;
930 END IF;
931
932 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
933
934 fte_mls_util.get_trip_segment_name
935 (
936 p_trip_segment_id => p_wsh_trip_id,
937 x_trip_segment_name => l_wsh_trip_name,
938 x_return_status => x_return_status
939 );
940 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
941 RETURN;
942 END IF;
943
944 fte_trips_pvt.get_trip_name
945 (
946 p_trip_id => p_fte_trip_id,
947 x_trip_name => l_fte_trip_name,
948 x_return_status => x_return_status
949 );
950 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
951 RETURN;
952 END IF;
953
954 Validate_PK
955 (
956 p_fte_trip_id,
957 p_wsh_trip_id,
958 x_return_status
959 );
960
961 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
962 FND_MSG_PUB.Count_And_Get
963 ( p_count => x_msg_count
964 , p_data => x_msg_data
965 , p_encoded => FND_API.G_FALSE
966 );
967 RETURN;
968 END IF;
969
970 -- Segment can only be removed from open trip, (delete)
971 l_status := Get_Fte_Trip_Status(p_fte_trip_id);
972 if (l_status <> 'OP') then
973 RAISE invalid_trip_status;
974 end if;
975
976 -- if removing from middle, giving Warning
977
978 select sequence_number into l_sequence_number
979 from fte_wsh_trips
980 where fte_trip_id = p_fte_trip_id and wsh_trip_id = p_wsh_trip_id;
981
982 fte_mls_util.get_previous_segment_id
983 (
984 p_trip_segment_id => p_wsh_trip_id,
985 p_sequence_number => l_sequence_number,
986 p_trip_id => p_fte_trip_id,
987 x_trip_name => l_trip_name,
988 x_trip_segment_name => l_trip_segment_name,
989 x_previous_segment_id => l_previous_segment_id,
990 x_return_status => l_return_status
991 );
992
993 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
994 AND l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
995 set_return_status
996 (
997 l_return_status,
998 x_return_status
999 );
1000 FND_MSG_PUB.Count_And_Get
1001 ( p_count => x_msg_count
1002 , p_data => x_msg_data
1003 , p_encoded => FND_API.G_FALSE
1004 );
1005 RETURN;
1006 END IF;
1007
1008 fte_mls_util.get_next_segment_id
1009 (
1010 p_trip_segment_id => p_wsh_trip_id,
1011 p_sequence_number => l_sequence_number,
1012 p_trip_id => p_fte_trip_id,
1013 x_trip_name => l_trip_name,
1014 x_trip_segment_name => l_trip_segment_name,
1015 x_next_segment_id => l_next_segment_id,
1016 x_return_status => l_return_status
1017 );
1018
1019 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
1020 AND l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
1021 set_return_status
1022 (
1023 l_return_status,
1024 x_return_status
1025 );
1026 FND_MSG_PUB.Count_And_Get
1027 ( p_count => x_msg_count
1028 , p_data => x_msg_data
1029 , p_encoded => FND_API.G_FALSE
1030 );
1031 RETURN;
1032 END IF;
1033
1034 if ((l_next_segment_id is NOT null) AND
1035 (l_previous_segment_id is NOT null)) then
1036
1037 update fte_trips
1038 set validation_required = 'Y'
1039 where fte_trip_id = p_fte_trip_id;
1040
1041 -- give warning
1042 FND_MESSAGE.SET_NAME('FTE', 'FTE_INVALID_CONNECT_SEGMENT');
1043 FND_MESSAGE.SET_TOKEN('TRIP_NAME',l_fte_trip_name);
1044 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1045 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
1046
1047 end if;
1048
1049 FND_MSG_PUB.Count_And_Get
1050 ( p_count => x_msg_count
1051 , p_data => x_msg_data
1052 , p_encoded => FND_API.G_FALSE
1053 );
1054
1055 EXCEPTION
1056 WHEN invalid_trip_status THEN
1057 -- cannot add/remove segments from trip with this status
1058 FND_MESSAGE.SET_NAME('FTE', 'FTE_INVALID_TRIP_STATUS3');
1059 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1060 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
1061 FND_MSG_PUB.Count_And_Get
1062 ( p_count => x_msg_count
1063 , p_data => x_msg_data
1064 , p_encoded => FND_API.G_FALSE
1065 );
1066 WHEN others THEN
1067 wsh_util_core.default_handler('FTE_WSH_TRIPS_PUB.Validate_Trip_For_Delete');
1068 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1069 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
1070 FND_MSG_PUB.Count_And_Get
1071 ( p_count => x_msg_count
1072 , p_data => x_msg_data
1073 , p_encoded => FND_API.G_FALSE
1074 );
1075
1076 END Validate_Trip_For_Delete;
1077
1078 --
1079 -- Procedure: Delete_Trip
1080 -- Parameters: p_fte_trip_id
1081 -- p_wsh_trip_id
1082 -- x_return_status return_status
1083 -- Description: This procedure will create a fte_wsh_trip.
1084 --
1085
1086 PROCEDURE Delete_Trip
1087 (
1088 P_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1089 p_fte_trip_id IN NUMBER,
1090 p_wsh_trip_id IN NUMBER,
1091 x_msg_count OUT NOCOPY NUMBER,
1092 x_msg_data OUT NOCOPY VARCHAR2,
1093 x_return_status OUT NOCOPY VARCHAR2
1094 ) IS
1095
1096 no_trip_found EXCEPTION;
1097
1098 BEGIN
1099
1100 wsh_debug_sv.start_debug ('FteWshTrip-' || p_fte_trip_id || '-' || p_wsh_trip_id);
1101 wsh_debug_sv.dpush (c_sdebug, 'Delete_Trip');
1102
1103 --
1104 --
1105 -- Initialize message list if p_init_msg_list is set to TRUE.
1106 --
1107 --
1108 IF FND_API.to_Boolean( p_init_msg_list )
1109 THEN
1110 FND_MSG_PUB.initialize;
1111 END IF;
1112
1113 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1114
1115 Validate_Trip_For_Delete
1116 (
1117 p_fte_trip_id => p_fte_trip_id,
1118 p_wsh_trip_id => p_wsh_trip_id,
1119 x_msg_count => x_msg_count,
1120 x_msg_data => x_msg_data,
1121 x_return_status => x_return_status);
1122
1123 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
1124 AND x_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
1125 FND_MSG_PUB.Count_And_Get
1126 ( p_count => x_msg_count
1127 , p_data => x_msg_data
1128 , p_encoded => FND_API.G_FALSE
1129 );
1130 RETURN;
1131 END IF;
1132
1133 delete from fte_wsh_trips
1134 where fte_trip_id = p_fte_trip_id and wsh_trip_id = p_wsh_trip_id;
1135
1136 if (SQL%NOTFOUND) then
1137 RAISE no_trip_found;
1138 end if;
1139
1140 IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
1141 --mark trip as invalid
1142 update fte_trips
1143 set validation_required = 'Y'
1144 where fte_trip_id = p_fte_trip_id;
1145 END IF;
1146
1147 FND_MSG_PUB.Count_And_Get
1148 ( p_count => x_msg_count
1149 , p_data => x_msg_data
1150 , p_encoded => FND_API.G_FALSE
1151 );
1152
1153 wsh_debug_sv.dlog (c_debug,'Return Status',x_Return_Status);
1154 wsh_debug_sv.dpop (c_sdebug);
1155 wsh_debug_sv.stop_debug;
1156
1157 EXCEPTION
1158 WHEN no_trip_found THEN
1159 FND_MESSAGE.SET_NAME('FTE','FTE_NO_TRIP_SEGMENT_ID');
1160 FND_MESSAGE.SET_TOKEN('TRIP_SEGMENT_ID',p_wsh_trip_id);
1161 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1162 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
1163 FND_MSG_PUB.Count_And_Get
1164 ( p_count => x_msg_count
1165 , p_data => x_msg_data
1166 , p_encoded => FND_API.G_FALSE
1167 );
1168 WHEN others THEN
1169 wsh_util_core.default_handler('FTE_WSH_TRIPS_PUB.Delete_Trip');
1170 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1171 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
1172 FND_MSG_PUB.Count_And_Get
1173 ( p_count => x_msg_count
1174 , p_data => x_msg_data
1175 , p_encoded => FND_API.G_FALSE
1176 );
1177
1178 END Delete_Trip;
1179
1180 --
1181 -- Procedure: Create_Update_Trip
1182 -- Description: Wrapper around Create_Trip and Update_Trip
1183 -- depends on the p_action_code 'CREATE' or 'UPDATE'
1184 --
1185
1186 PROCEDURE Validate_Trip_Wrapper
1187 (
1188 pp_FTE_TRIP_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM,
1189 pp_WSH_TRIP_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM,
1190 pp_SEQUENCE_NUMBER IN NUMBER DEFAULT FND_API.G_MISS_NUM,
1191 pp_CREATION_DATE IN DATE DEFAULT FND_API.G_MISS_DATE,
1192 pp_CREATED_BY IN NUMBER DEFAULT FND_API.G_MISS_NUM,
1193 pp_LAST_UPDATE_DATE IN DATE DEFAULT FND_API.G_MISS_DATE,
1194 pp_LAST_UPDATED_BY IN NUMBER DEFAULT FND_API.G_MISS_NUM,
1195 pp_LAST_UPDATE_LOGIN IN NUMBER DEFAULT FND_API.G_MISS_NUM,
1196 pp_PROGRAM_APPLICATION_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM,
1197 pp_PROGRAM_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM,
1198 pp_PROGRAM_UPDATE_DATE IN DATE DEFAULT FND_API.G_MISS_DATE,
1199 pp_REQUEST_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM,
1200 pp_ATTRIBUTE_CATEGORY IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
1201 pp_ATTRIBUTE1 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
1202 pp_ATTRIBUTE2 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
1203 pp_ATTRIBUTE3 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
1204 pp_ATTRIBUTE4 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
1205 pp_ATTRIBUTE5 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
1206 pp_ATTRIBUTE6 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
1207 pp_ATTRIBUTE7 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
1208 pp_ATTRIBUTE8 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
1209 pp_ATTRIBUTE9 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
1210 pp_ATTRIBUTE10 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
1211 pp_ATTRIBUTE11 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
1212 pp_ATTRIBUTE12 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
1213 pp_ATTRIBUTE13 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
1214 pp_ATTRIBUTE14 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
1215 pp_ATTRIBUTE15 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
1216 P_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1217 p_action_code IN VARCHAR2,
1218 x_msg_count OUT NOCOPY NUMBER,
1219 x_msg_data OUT NOCOPY VARCHAR2,
1220 x_return_status OUT NOCOPY VARCHAR2
1221 ) IS
1222
1223 p_trip_info fte_wsh_trip_rec_type;
1224
1225 invalid_action EXCEPTION;
1226
1227 BEGIN
1228
1229 --
1230 --
1231 -- Initialize message list if p_init_msg_list is set to TRUE.
1232 --
1233 --
1234 IF FND_API.to_Boolean( p_init_msg_list )
1235 THEN
1236 FND_MSG_PUB.initialize;
1237 END IF;
1238
1239 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1240
1241 -- populate trip record
1242 p_trip_info.FTE_TRIP_ID :=pp_FTE_TRIP_ID;
1243 p_trip_info.WSH_TRIP_ID :=pp_WSH_TRIP_ID;
1244 p_trip_info.SEQUENCE_NUMBER :=pp_SEQUENCE_NUMBER;
1245 p_trip_info.CREATION_DATE :=pp_CREATION_DATE;
1246 p_trip_info.CREATED_BY :=pp_CREATED_BY;
1247 p_trip_info.LAST_UPDATE_DATE :=pp_LAST_UPDATE_DATE;
1248 p_trip_info.LAST_UPDATED_BY :=pp_LAST_UPDATED_BY;
1249 p_trip_info.LAST_UPDATE_LOGIN :=pp_LAST_UPDATE_LOGIN;
1250 p_trip_info.PROGRAM_APPLICATION_ID :=pp_PROGRAM_APPLICATION_ID;
1251 p_trip_info.PROGRAM_ID :=pp_PROGRAM_ID;
1252 p_trip_info.PROGRAM_UPDATE_DATE :=pp_PROGRAM_UPDATE_DATE;
1253 p_trip_info.REQUEST_ID :=pp_REQUEST_ID;
1254 p_trip_info.ATTRIBUTE_CATEGORY :=pp_ATTRIBUTE_CATEGORY;
1255 p_trip_info.ATTRIBUTE1 :=pp_ATTRIBUTE1;
1256 p_trip_info.ATTRIBUTE2 :=pp_ATTRIBUTE2;
1257 p_trip_info.ATTRIBUTE3 :=pp_ATTRIBUTE3;
1258 p_trip_info.ATTRIBUTE4 :=pp_ATTRIBUTE4;
1259 p_trip_info.ATTRIBUTE5 :=pp_ATTRIBUTE5;
1260 p_trip_info.ATTRIBUTE6 :=pp_ATTRIBUTE6;
1261 p_trip_info.ATTRIBUTE7 :=pp_ATTRIBUTE7;
1262 p_trip_info.ATTRIBUTE8 :=pp_ATTRIBUTE8;
1263 p_trip_info.ATTRIBUTE9 :=pp_ATTRIBUTE9;
1264 p_trip_info.ATTRIBUTE10 :=pp_ATTRIBUTE10;
1265 p_trip_info.ATTRIBUTE11 :=pp_ATTRIBUTE11;
1266 p_trip_info.ATTRIBUTE12 :=pp_ATTRIBUTE12;
1267 p_trip_info.ATTRIBUTE13 :=pp_ATTRIBUTE13;
1268 p_trip_info.ATTRIBUTE14 :=pp_ATTRIBUTE14;
1269 p_trip_info.ATTRIBUTE15 :=pp_ATTRIBUTE15;
1270
1271 -- call public API
1272 if (p_action_code = 'CREATE') then
1273 Validate_Trip
1274 (
1275 p_trip_info => p_trip_info,
1276 p_action_code => p_action_code,
1277 x_return_status => x_return_status
1278 );
1279 elsif (p_action_code = 'UPDATE') then
1280 Validate_Trip
1281 (
1282 p_trip_info => p_trip_info,
1283 p_action_code => p_action_code,
1284 x_return_status => x_return_status
1285 );
1286 else
1287 RAISE invalid_action;
1288 end if;
1289
1290 FND_MSG_PUB.Count_And_Get
1291 ( p_count => x_msg_count
1292 , p_data => x_msg_data
1293 , p_encoded => FND_API.G_FALSE
1294 );
1295
1296 EXCEPTION
1297 WHEN invalid_action THEN
1298 FND_MESSAGE.SET_NAME('FTE','FTE_WSH_TRIPS_WRAPPER_ERR1');
1299 FND_MESSAGE.SET_TOKEN('ACTION',p_action_code);
1300 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1301 wsh_util_core.add_message(x_return_status);
1302 FND_MSG_PUB.Count_And_Get
1303 ( p_count => x_msg_count
1304 , p_data => x_msg_data
1305 , p_encoded => FND_API.G_FALSE
1306 );
1307 WHEN others THEN
1308 wsh_util_core.default_handler('FTE_WSH_TRIPS_PUB.Validate_Trip_Wrapper');
1309 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1310 wsh_util_core.add_message(x_return_status);
1311 FND_MSG_PUB.Count_And_Get
1312 ( p_count => x_msg_count
1313 , p_data => x_msg_data
1314 , p_encoded => FND_API.G_FALSE
1315 );
1316
1317 END Validate_Trip_Wrapper;
1318
1319 --
1320 -- Procedure: Create_Update_Trip_Wrapper
1321 -- Description: Wrapper around Create_Trip and Update_Trip
1322 -- depends on the p_action_code 'CREATE' or 'UPDATE'
1323 --
1324
1325 PROCEDURE Create_Update_Trip_Wrapper
1326 (
1327 pp_FTE_TRIP_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM,
1328 pp_WSH_TRIP_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM,
1329 pp_SEQUENCE_NUMBER IN NUMBER DEFAULT FND_API.G_MISS_NUM,
1330 pp_CREATION_DATE IN DATE DEFAULT FND_API.G_MISS_DATE,
1331 pp_CREATED_BY IN NUMBER DEFAULT FND_API.G_MISS_NUM,
1332 pp_LAST_UPDATE_DATE IN DATE DEFAULT FND_API.G_MISS_DATE,
1333 pp_LAST_UPDATED_BY IN NUMBER DEFAULT FND_API.G_MISS_NUM,
1334 pp_LAST_UPDATE_LOGIN IN NUMBER DEFAULT FND_API.G_MISS_NUM,
1335 pp_PROGRAM_APPLICATION_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM,
1336 pp_PROGRAM_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM,
1337 pp_PROGRAM_UPDATE_DATE IN DATE DEFAULT FND_API.G_MISS_DATE,
1338 pp_REQUEST_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM,
1339 pp_ATTRIBUTE_CATEGORY IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
1340 pp_ATTRIBUTE1 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
1341 pp_ATTRIBUTE2 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
1342 pp_ATTRIBUTE3 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
1343 pp_ATTRIBUTE4 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
1344 pp_ATTRIBUTE5 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
1345 pp_ATTRIBUTE6 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
1346 pp_ATTRIBUTE7 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
1347 pp_ATTRIBUTE8 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
1348 pp_ATTRIBUTE9 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
1349 pp_ATTRIBUTE10 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
1350 pp_ATTRIBUTE11 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
1351 pp_ATTRIBUTE12 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
1352 pp_ATTRIBUTE13 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
1353 pp_ATTRIBUTE14 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
1354 pp_ATTRIBUTE15 IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
1355 P_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1356 p_action_code IN VARCHAR2,
1357 p_validate_flag IN VARCHAR2,
1358 x_msg_count OUT NOCOPY NUMBER,
1359 x_msg_data OUT NOCOPY VARCHAR2,
1360 x_return_status OUT NOCOPY VARCHAR2
1361 ) IS
1362
1363 p_trip_info fte_wsh_trip_rec_type;
1364
1365 invalid_action EXCEPTION;
1366
1367 BEGIN
1368
1369 wsh_debug_sv.start_debug ('FteWshTrip-' || pp_fte_trip_id || '-' || pp_wsh_trip_id);
1370 wsh_debug_sv.dpush (c_sdebug, 'Create_Update_Trip_Wrapper');
1371
1372 wsh_debug_sv.dlog (c_debug,'FteTripId ',pp_fte_trip_id);
1373 wsh_debug_sv.dlog (c_debug,'WshTripId ',pp_wsh_trip_id);
1374 wsh_debug_sv.dlog (c_debug,'SequenceNumber ',pp_sequence_number);
1375 wsh_debug_sv.dlog (c_debug,'ActionCode ',p_action_code);
1376 wsh_debug_sv.dlog (c_debug,'ValidateFlag ',p_validate_flag);
1377 --
1378 --
1379 -- Initialize message list if p_init_msg_list is set to TRUE.
1380 --
1381 --
1382 IF FND_API.to_Boolean( p_init_msg_list )
1383 THEN
1384 FND_MSG_PUB.initialize;
1385 END IF;
1386
1387 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1388
1389 -- populate trip record
1390 p_trip_info.FTE_TRIP_ID :=pp_FTE_TRIP_ID;
1391 p_trip_info.WSH_TRIP_ID :=pp_WSH_TRIP_ID;
1392 p_trip_info.SEQUENCE_NUMBER :=pp_SEQUENCE_NUMBER;
1393 p_trip_info.CREATION_DATE :=pp_CREATION_DATE;
1394 p_trip_info.CREATED_BY :=pp_CREATED_BY;
1395 p_trip_info.LAST_UPDATE_DATE :=pp_LAST_UPDATE_DATE;
1396 p_trip_info.LAST_UPDATED_BY :=pp_LAST_UPDATED_BY;
1397 p_trip_info.LAST_UPDATE_LOGIN :=pp_LAST_UPDATE_LOGIN;
1398 p_trip_info.PROGRAM_APPLICATION_ID :=pp_PROGRAM_APPLICATION_ID;
1399 p_trip_info.PROGRAM_ID :=pp_PROGRAM_ID;
1400 p_trip_info.PROGRAM_UPDATE_DATE :=pp_PROGRAM_UPDATE_DATE;
1401 p_trip_info.REQUEST_ID :=pp_REQUEST_ID;
1402 p_trip_info.ATTRIBUTE_CATEGORY :=pp_ATTRIBUTE_CATEGORY;
1403 p_trip_info.ATTRIBUTE1 :=pp_ATTRIBUTE1;
1404 p_trip_info.ATTRIBUTE2 :=pp_ATTRIBUTE2;
1405 p_trip_info.ATTRIBUTE3 :=pp_ATTRIBUTE3;
1406 p_trip_info.ATTRIBUTE4 :=pp_ATTRIBUTE4;
1407 p_trip_info.ATTRIBUTE5 :=pp_ATTRIBUTE5;
1408 p_trip_info.ATTRIBUTE6 :=pp_ATTRIBUTE6;
1409 p_trip_info.ATTRIBUTE7 :=pp_ATTRIBUTE7;
1410 p_trip_info.ATTRIBUTE8 :=pp_ATTRIBUTE8;
1411 p_trip_info.ATTRIBUTE9 :=pp_ATTRIBUTE9;
1412 p_trip_info.ATTRIBUTE10 :=pp_ATTRIBUTE10;
1413 p_trip_info.ATTRIBUTE11 :=pp_ATTRIBUTE11;
1414 p_trip_info.ATTRIBUTE12 :=pp_ATTRIBUTE12;
1415 p_trip_info.ATTRIBUTE13 :=pp_ATTRIBUTE13;
1416 p_trip_info.ATTRIBUTE14 :=pp_ATTRIBUTE14;
1417 p_trip_info.ATTRIBUTE15 :=pp_ATTRIBUTE15;
1418
1419 -- call public API
1420 if (p_action_code = 'CREATE') then
1421 Create_Trip
1422 (
1423 p_trip_info => p_trip_info,
1424 x_return_status => x_return_status
1425 );
1426 elsif (p_action_code = 'UPDATE') then
1427 Update_Trip
1428 (
1429 p_trip_info => p_trip_info,
1430 p_validate_flag => p_validate_flag,
1431 x_return_status => x_return_status
1432 );
1433 else
1434 RAISE invalid_action;
1435 end if;
1436
1437 FND_MSG_PUB.Count_And_Get
1438 ( p_count => x_msg_count
1439 , p_data => x_msg_data
1440 , p_encoded => FND_API.G_FALSE
1441 );
1442
1443 wsh_debug_sv.dlog (c_debug,'Return Status',x_Return_Status);
1444 wsh_debug_sv.dpop (c_sdebug);
1445 wsh_debug_sv.stop_debug;
1446
1447 EXCEPTION
1448 WHEN invalid_action THEN
1449 FND_MESSAGE.SET_NAME('FTE','FTE_WSH_TRIPS_WRAPPER_ERR1');
1450 FND_MESSAGE.SET_TOKEN('ACTION',p_action_code);
1451 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1452 wsh_util_core.add_message(x_return_status);
1453 FND_MSG_PUB.Count_And_Get
1454 ( p_count => x_msg_count
1455 , p_data => x_msg_data
1456 , p_encoded => FND_API.G_FALSE
1457 );
1458
1459 wsh_debug_sv.dlog (c_debug,'Return Status',x_Return_Status);
1460 wsh_debug_sv.dpop (c_sdebug);
1461 wsh_debug_sv.stop_debug;
1462
1463 WHEN others THEN
1464 wsh_util_core.default_handler('FTE_WSH_TRIPS_PUB.Create_Update_Trip_Wrapper');
1465 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1466 wsh_util_core.add_message(x_return_status);
1467 FND_MSG_PUB.Count_And_Get
1468 ( p_count => x_msg_count
1469 , p_data => x_msg_data
1470 , p_encoded => FND_API.G_FALSE
1471 );
1472
1473 wsh_debug_sv.dlog (c_debug,'Return Status',x_Return_Status);
1474 wsh_debug_sv.dpop (c_sdebug);
1475 wsh_debug_sv.stop_debug;
1476
1477 END Create_Update_Trip_Wrapper;
1478
1479 END FTE_WSH_TRIPS_PVT;