[Home] [Help]
PACKAGE BODY: APPS.FTE_DELIVERY_LEGS_PVT
Source
1 PACKAGE BODY FTE_DELIVERY_LEGS_PVT as
2 /* $Header: FTEVDLGB.pls 120.3 2005/07/28 12:34:44 nltan ship $ */
3 --{
4 PROCEDURE search_segment_save
5 (
6 P_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
7 X_return_status OUT NOCOPY VARCHAR2,
8 X_msg_count OUT NOCOPY NUMBER,
9 X_msg_data OUT NOCOPY VARCHAR2,
10 p_delivery_id IN NUMBER,
11 p_delivery_name IN VARCHAR2 DEFAULT NULL,
12 p_wsh_trip_id IN NUMBER,
13 p_wsh_trip_name IN VARCHAR2,
14 p_pickup_stop_id IN NUMBER,
15 p_pickup_location_id IN NUMBER,
16 p_pickup_stop_seq IN NUMBER,
17 p_pickup_departure_date IN DATE,
18 p_pickup_arrival_date IN DATE,
19 p_dropoff_stop_id IN NUMBER,
20 p_dropoff_location_id IN NUMBER,
21 p_dropoff_stop_seq IN NUMBER,
25 p_move_stop_seq_to IN NUMBER,
22 p_dropoff_departure_date IN DATE,
23 p_dropoff_arrival_date IN DATE,
24 p_move_stop_seq_start IN NUMBER,
26 p_fte_trip_id IN NUMBER,
27 p_pricing_request_id IN NUMBER,
28 p_lane_id IN NUMBER,
29 p_schedule_id IN NUMBER,
30 p_ignore_for_planning IN VARCHAR2 DEFAULT NULL,
31 x_pickup_stop_id OUT NOCOPY NUMBER,
32 x_dropoff_stop_id OUT NOCOPY NUMBER,
33 x_delivery_leg_id OUT NOCOPY NUMBER,
34 x_delivery_leg_seq OUT NOCOPY NUMBER,
35 x_pickup_stop_seq OUT NOCOPY NUMBER,
36 x_dropoff_stop_seq OUT NOCOPY NUMBER
37 )
38 IS
39 --{
40 l_api_name CONSTANT VARCHAR2(30) := 'search_segment_save';
41 l_api_version CONSTANT NUMBER := 1.0;
42 --
43 --
44 l_return_status VARCHAR2(32767);
45 l_msg_count NUMBER;
46 l_msg_data VARCHAR2(32767);
47 l_program_name VARCHAR2(32767);
48 p_action_type VARCHAR2(32767);
49 --
50 --
51 l_pickup_stop_id NUMBER;
52 l_pickup_stop_seq NUMBER;
53 l_stop_id NUMBER;
54 l_dropoff_stop_id NUMBER;
55 l_dropoff_stop_seq NUMBER;
56 l_index NUMBER;
57 l_stop_new_seq NUMBER;
58 l_delivery_leg_id NUMBER;
59 l_delivery_leg_seq NUMBER;
60 l_wsh_trip_id NUMBER;
61 l_fte_trip_id NUMBER;
62 l_wsh_trip_name VARCHAR2(32767);
63 l_fte_trip_name VARCHAR2(32767);
64 l_fte_wsh_trip_seq NUMBER;
65 l_trip_id NUMBER;
66 l_trip_name VARCHAR2(32767);
67 l_ship_method_code VARCHAR2(32767);
68 --
69 --
70 --
71 l_number_of_errors NUMBER := 0;
72 l_number_of_warnings NUMBER := 0;
73 --
74 --}
75 BEGIN
76 --{
77 --
78 -- Standard Start of API savepoint
79 SAVEPOINT SEARCH_SEGMENT_SAVE_PUB;
80 --
81 --
82 -- Initialize message list if p_init_msg_list is set to TRUE.
83 --
84 --
85 IF FND_API.to_Boolean( p_init_msg_list )
86 THEN
87 FND_MSG_PUB.initialize;
88 END IF;
89 --
90 --
91 -- Initialize API return status to success
92 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
93 l_number_of_errors := 0;
94 l_number_of_warnings := 0;
95 --
96
97 --
98 process_delivery_leg
99 (
100 X_return_status => x_return_status,
101 X_msg_count => x_msg_count,
102 X_msg_data => x_msg_data,
103 p_ui_page_name => GK_SEARCH_SEGMENTS_PAGE,
104 p_delivery_id => p_delivery_id,
105 p_delivery_name => p_delivery_name,
106 p_wsh_trip_id => p_wsh_trip_id,
107 p_wsh_trip_name => p_wsh_trip_name,
108 p_lane_id => p_lane_id,
109 p_schedule_id => p_schedule_id,
110 p_pickup_stop_id => p_pickup_stop_id,
111 p_pickup_stop_seq => p_pickup_stop_seq,
112 p_pickup_location_id => p_pickup_location_id,
113 p_pickup_departure_date => p_pickup_departure_date,
114 p_pickup_arrival_date => p_pickup_arrival_date,
115 p_dropoff_stop_id => p_dropoff_stop_id,
116 p_dropoff_stop_seq => p_dropoff_stop_seq,
117 p_dropoff_location_id => p_dropoff_location_id,
118 p_dropoff_departure_date => p_dropoff_departure_date,
119 p_dropoff_arrival_date => p_dropoff_arrival_date,
120 p_fte_trip_id => p_fte_trip_id,
121 p_pricing_request_id => p_pricing_request_id,
122 p_move_stop_seq_start => p_move_stop_seq_start,
123 p_move_stop_seq_to => p_move_stop_seq_to,
124 p_ignore_for_planning => p_ignore_for_planning,
125 x_wsh_trip_id => l_wsh_trip_id,
126 x_wsh_trip_name => l_wsh_trip_name,
127 x_ship_method_code => l_ship_method_code,
128 x_fte_trip_id => l_fte_trip_id,
129 x_fte_trip_name => l_fte_trip_name,
130 x_pickup_stop_id => x_pickup_stop_id ,
131 x_dropoff_stop_id => x_dropoff_stop_id,
132 x_delivery_leg_id => x_delivery_leg_id,
133 x_delivery_leg_seq => x_delivery_leg_seq,
134 x_pickup_stop_seq => x_pickup_stop_seq,
135 x_dropoff_stop_seq => x_dropoff_stop_seq
136 );
137 --}
138 EXCEPTION
139 --{
140 WHEN FND_API.G_EXC_ERROR THEN
141 ROLLBACK TO SEARCH_SEGMENT_SAVE_PUB;
142 x_return_status := FND_API.G_RET_STS_ERROR ;
143 FND_MSG_PUB.Count_And_Get
144 (
145 p_count => x_msg_count,
146 p_data => x_msg_data,
147 p_encoded => FND_API.G_FALSE
148 );
149 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
150 ROLLBACK TO SEARCH_SEGMENT_SAVE_PUB;
151 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
152 FND_MSG_PUB.Count_And_Get
153 (
154 p_count => x_msg_count,
155 p_data => x_msg_data,
156 p_encoded => FND_API.G_FALSE
157 );
158 WHEN OTHERS THEN
159 ROLLBACK TO SEARCH_SEGMENT_SAVE_PUB;
160 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
161 --
162 --
163 wsh_util_core.default_handler('FTE_DELIVERY_LEGS_PVT.search_segment_save');
164 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
165 --
166 --
167 FND_MSG_PUB.Count_And_Get
168 (
169 p_count => x_msg_count,
170 p_data => x_msg_data,
171 p_encoded => FND_API.G_FALSE
172 );
173 --}
174 END search_segment_save;
175 --
176 --
177 PROCEDURE process_delivery_leg
178 (
179 P_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
180 X_return_status OUT NOCOPY VARCHAR2,
181 X_msg_count OUT NOCOPY NUMBER,
182 X_msg_data OUT NOCOPY VARCHAR2,
183 p_ui_page_name IN VARCHAR2 DEFAULT GK_DLEG_WB_PAGE,
184 p_delivery_id IN NUMBER,
185 p_delivery_name IN VARCHAR2 DEFAULT NULL,
186 p_delivery_leg_id IN NUMBER DEFAULT NULL,
187 p_delivery_leg_seq IN NUMBER DEFAULT NULL,
188 p_wsh_trip_id IN NUMBER DEFAULT NULL,
189 p_wsh_trip_name IN VARCHAR2 DEFAULT NULL,
190 p_lane_id IN NUMBER DEFAULT NULL,
191 p_schedule_id IN NUMBER DEFAULT NULL,
192 p_carrier_id IN NUMBER DEFAULT NULL,
193 p_mode_of_transport IN VARCHAR2 DEFAULT NULL,
194 p_service_level IN VARCHAR2 DEFAULT NULL,
195 p_carrier_name IN VARCHAR2 DEFAULT NULL,
196 p_mode_of_transport_meaning IN VARCHAR2 DEFAULT NULL,
197 p_service_level_meaning IN VARCHAR2 DEFAULT NULL,
198 p_pickup_stop_id IN NUMBER DEFAULT NULL,
199 p_pickup_stop_seq IN NUMBER DEFAULT NULL,
200 p_pickup_location_id IN NUMBER DEFAULT NULL,
201 p_pickup_departure_date IN DATE DEFAULT NULL,
202 p_pickup_arrival_date IN DATE DEFAULT NULL,
203 p_dropoff_stop_id IN NUMBER DEFAULT NULL,
204 p_dropoff_stop_seq IN NUMBER DEFAULT NULL,
205 p_dropoff_location_id IN NUMBER DEFAULT NULL,
206 p_dropoff_departure_date IN DATE DEFAULT NULL,
207 p_dropoff_arrival_date IN DATE DEFAULT NULL,
211 p_move_stop_seq_start IN NUMBER DEFAULT NULL,
208 p_fte_trip_id IN NUMBER DEFAULT NULL,
209 p_fte_trip_name IN VARCHAR2 DEFAULT NULL,
210 p_pricing_request_id IN NUMBER DEFAULT NULL,
212 p_move_stop_seq_to IN NUMBER DEFAULT NULL,
213 p_first_stop_id IN NUMBER DEFAULT NULL,
214 p_first_stop_location_id IN NUMBER DEFAULT NULL,
215 p_first_stop_seq IN NUMBER DEFAULT NULL,
216 p_first_stop_departure_date IN DATE DEFAULT NULL,
217 p_first_stop_arrival_date IN DATE DEFAULT NULL,
218 p_last_stop_id IN NUMBER DEFAULT NULL,
219 p_last_stop_location_id IN NUMBER DEFAULT NULL,
220 p_last_stop_seq IN NUMBER DEFAULT NULL,
221 p_last_stop_departure_date IN DATE DEFAULT NULL,
222 p_last_stop_arrival_date IN DATE DEFAULT NULL,
223 p_veh_org_id IN NUMBER DEFAULT NULL,
224 p_veh_num IN NUMBER DEFAULT NULL,
225 p_veh_num_pre IN NUMBER DEFAULT NULL,
226 p_ignore_for_planning IN VARCHAR2 DEFAULT NULL,
227 p_veh_item_id IN NUMBER DEFAULT NULL,
228 x_wsh_trip_id OUT NOCOPY NUMBER,
229 x_wsh_trip_name OUT NOCOPY VARCHAR2,
230 x_ship_method_code OUT NOCOPY VARCHAR2,
231 x_fte_trip_id OUT NOCOPY NUMBER,
232 x_fte_trip_name OUT NOCOPY VARCHAR2,
233 x_pickup_stop_id OUT NOCOPY NUMBER,
234 x_dropoff_stop_id OUT NOCOPY NUMBER,
235 x_delivery_leg_id OUT NOCOPY NUMBER,
236 x_delivery_leg_seq OUT NOCOPY NUMBER,
237 x_pickup_stop_seq OUT NOCOPY NUMBER,
238 x_dropoff_stop_seq OUT NOCOPY NUMBER
239 )
240 IS
241 --{
242 l_api_name CONSTANT VARCHAR2(30) := 'process_delivery_leg';
243 l_api_version CONSTANT NUMBER := 1.0;
244 --
245 --
246 K_UPDATE CONSTANT VARCHAR2(30) := 'UPDATE';
247 K_CREATE CONSTANT VARCHAR2(30) := 'CREATE';
248 K_NO_ACTION CONSTANT VARCHAR2(30) := 'NO_ACTION';
249 --
250 --
251 l_return_status VARCHAR2(32767);
252 l_msg_count NUMBER;
253 l_msg_data VARCHAR2(32767);
254 l_program_name VARCHAR2(32767);
255 p_action_type VARCHAR2(32767);
256 --
257 --
258 l_number_of_errors NUMBER := 0;
259 l_number_of_warnings NUMBER := 0;
260 --
261 l_pickup_stop_id NUMBER;
262 l_pickup_stop_seq NUMBER;
263 l_stop_id NUMBER;
264 l_dropoff_stop_id NUMBER;
265 l_dropoff_stop_seq NUMBER;
266 l_index NUMBER;
267 l_stop_new_seq NUMBER;
268 l_delivery_leg_id NUMBER;
269 l_delivery_leg_seq NUMBER;
270 l_wsh_trip_id NUMBER;
271 l_fte_trip_id NUMBER;
272 l_wsh_trip_name VARCHAR2(32767);
273 l_fte_trip_name VARCHAR2(32767);
274 l_fte_wsh_trip_seq NUMBER;
275 l_trip_id NUMBER;
276 l_trip_name VARCHAR2(32767);
277 l_ship_method_code VARCHAR2(32767);
278 l_first_stop_new_location_id NUMBER;
279 l_last_stop_new_location_id NUMBER;
280 --
281 --
282 l_wsh_trip_action VARCHAR2(32767);
283 l_fte_trip_action VARCHAR2(32767);
284 l_pickup_stop_action VARCHAR2(32767);
285 l_delivery_leg_action VARCHAR2(32767);
286 l_dropoff_stop_action VARCHAR2(32767);
287 --
288 --
289 l_old_lane_id NUMBER;
290 l_old_schedule_id NUMBER;
291 l_old_carrier_id NUMBER;
292 l_old_mode_of_transport VARCHAR2(32767);
293 l_old_service_level VARCHAR2(32767);
294 l_old_stop_location_id NUMBER;
295 --
296 --
297 l_reprice_required VARCHAR2(32767);
298 l_segment_has_other_deliveries BOOLEAN;
299 --
300 return_dropoff_stop_id NUMBER;
301 return_pickup_stop_id NUMBER;
302
303
304 --PACK I
305 l_dlvy_weight_uom VARCHAR2(10);
306 l_dlvy_volume_uom VARCHAR2(10);
307 --
308 CURSOR wsh_trip_cur (p_trip_id NUMBER)
309 IS
310 SELECT lane_id, schedule_id,
311 carrier_id, mode_of_transport, service_level,
312 NVL(consolidation_allowed,'N') consolidation_allowed
313 FROM wsh_trips
314 WHERE trip_id = p_trip_id;
315 --
316 --
317 CURSOR stop_location_cur (p_stop_id NUMBER)
318 IS
319 SELECT stop_location_id
320 FROM wsh_trip_stops
321 WHERE stop_id = p_stop_id;
322 --
323 --
324 CURSOR stop_cur (p_trip_id NUMBER, p_stop_seq NUMBER)
325 IS
326 SELECT stop_id, stop_sequence_number, stop_location_id
327 FROM wsh_trip_stops
328 WHERE trip_id = p_trip_id
329 AND stop_sequence_number >= p_stop_seq
330 order by stop_sequence_number desc;
331
332 CURSOR dlvy_weight_volume_cur (p_dlvy_id NUMBER)
333 IS
334 SELECT weight_uom_code,volume_uom_code
335 FROM wsh_new_deliveries
336 WHERE delivery_id = p_dlvy_id;
337
338
339 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
340
341 --}
342 BEGIN
343 --{
344
345 IF l_debug_on THEN
346 wsh_debug_sv.push(l_api_name);
347 END IF;
348 --
349 -- Standard Start of API savepoint
353 -- Initialize message list if p_init_msg_list is set to TRUE.
350 SAVEPOINT PROCESS_DELIVERY_LEG_PUB;
351 --
352 --
354 --
355 --
356 IF FND_API.to_Boolean( p_init_msg_list )
357 THEN
358 FND_MSG_PUB.initialize;
359 END IF;
360 --
361 --
362 -- Initialize API return status to success
363 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
364 l_number_of_errors := 0;
365 l_number_of_warnings := 0;
366 --
367 IF p_ui_page_name = GK_DLEG_WB_PAGE
368 THEN
369 null; --RETURN;
370 END IF;
371 --
372 l_pickup_stop_id := p_pickup_stop_id;
373 l_dropoff_stop_id := p_dropoff_stop_id;
374 l_pickup_stop_seq := p_pickup_stop_seq;
375 l_dropoff_stop_seq := p_dropoff_stop_seq;
376 l_wsh_trip_id := p_wsh_trip_id;
377 l_wsh_trip_name := p_wsh_trip_name;
378 l_fte_trip_id := p_fte_trip_id;
379 l_fte_trip_name := p_fte_trip_name;
380 l_delivery_leg_id := p_delivery_leg_id;
381 l_delivery_leg_seq := p_delivery_leg_seq;
382 l_wsh_trip_action := K_NO_ACTION;
383 l_fte_trip_action := K_NO_ACTION;
384 l_pickup_stop_action := K_NO_ACTION;
385 l_dropoff_stop_action := K_NO_ACTION;
386 l_delivery_leg_action := K_NO_ACTION;
387 l_first_stop_new_location_id := NULL;
388 l_last_stop_new_location_id := NULL;
389 --
390 --
391 -- Null implies that this flag is not required to be updated.
392 l_reprice_required := NULL;
393 --
394 --
395 IF l_debug_on THEN
396 WSH_DEBUG_SV.logmsg(l_api_name, 'BEFORE CALLING FREIGHT RATE:ReqId:laneId:Veh:VOrgId:'||
397 p_pricing_request_id||':'||p_lane_id||':'||p_veh_item_id||':'||p_veh_org_id,
398 WSH_DEBUG_SV.C_PROC_LEVEL);
399 END IF;
400 --
401 IF l_debug_on THEN
402 WSH_DEBUG_SV.logmsg(l_api_name, ' 1 p_delivery_leg_id ' || p_delivery_leg_id,
403 WSH_DEBUG_SV.C_PROC_LEVEL);
404 END IF;
405
406 IF p_delivery_leg_id IS NULL
407 THEN
408 --{
409 l_delivery_leg_action := K_CREATE;
410 --
411 --
412 IF p_wsh_trip_id IS NULL -- Auto-create segment
413 THEN
414 --{
415 l_wsh_trip_action := K_CREATE;
416 l_pickup_stop_seq := 10;
417 l_dropoff_stop_seq := 20;
418 l_pickup_stop_action := K_CREATE;
419 l_dropoff_stop_action := K_CREATE;
420 --}
421 ELSE --- coming from search segments
422 --{
423 IF l_pickup_stop_id IS NULL
424 THEN
425 --{
426 l_pickup_stop_action := K_CREATE;
427 --}
428 END IF;
429 --
430 --
431 IF l_dropoff_stop_id IS NULL
432 THEN
433 --{
434 l_dropoff_stop_action := K_CREATE;
435 --}
436 END IF;
437 --}
438 END IF;
439 --}
440 END IF;
441 --
442 IF l_debug_on THEN
443 WSH_DEBUG_SV.logmsg(l_api_name, ' 2 p_wsh_trip_id ' || p_wsh_trip_id,
444 WSH_DEBUG_SV.C_PROC_LEVEL);
445 END IF;
446 --
447 IF p_wsh_trip_id IS NOT NULL
448 THEN
449 --{
450 IF
451 (
452 p_ui_page_name = GK_DLEG_WB_PAGE
453 AND (
454 p_lane_id IS NOT NULL
455 OR p_carrier_id IS NOT NULL
456 OR p_mode_of_transport IS NOT NULL
457 OR p_service_level IS NOT NULL
458 ) -- Need to check if ship method is to be updated.
459 )
460 OR p_ui_page_name = GK_SEARCH_SEGMENTS_PAGE
461 THEN
462 --{
463 l_old_lane_id := FND_API.G_MISS_NUM;
464 l_old_schedule_id := FND_API.G_MISS_NUM;
465 l_old_carrier_id := FND_API.G_MISS_NUM;
466 l_old_mode_of_transport := FND_API.G_MISS_CHAR;
467 l_old_service_level := FND_API.G_MISS_CHAR;
468 --
469 --
470 FOR wsh_trip_rec IN wsh_trip_cur
471 (
472 p_trip_id => p_wsh_trip_id
473 )
474 LOOP
475 --{
476 l_old_lane_id
477 := NVL(wsh_trip_rec.lane_id,FND_API.G_MISS_NUM);
478 --
479 l_old_schedule_id
480 := NVL(wsh_trip_rec.schedule_id,FND_API.G_MISS_NUM);
481 --
482 l_old_carrier_id
483 := NVL(wsh_trip_rec.carrier_id,FND_API.G_MISS_NUM);
484 --
485 l_old_mode_of_transport
486 := NVL(wsh_trip_rec.mode_of_transport,FND_API.G_MISS_CHAR);
487 --
488 l_old_service_level
489 := NVL(wsh_trip_rec.service_level,FND_API.G_MISS_CHAR);
490 --
491 --
492 IF wsh_trip_rec.consolidation_allowed = 'Y'
493 AND p_pricing_request_id IS NOT NULL
494 AND p_pricing_request_id > 0
495 THEN
496 --{
497 l_segment_has_other_deliveries := FALSE;
498 --
499 --
500 l_segment_has_other_deliveries
501 := FTE_MLS_UTIL.segment_has_other_deliveries
502 (
503 p_trip_segment_id => p_wsh_trip_id,
504 p_delivery_id => p_delivery_id
505 );
506 --
507 --
508 IF (l_segment_has_other_deliveries)
509 THEN
510 l_reprice_required := 'Y';
511 END IF;
515 --}
512 --}
513 END IF;
514 --
516 END LOOP;
517 --
518 --
519 IF (
520 p_lane_id <> l_old_lane_id
521 OR p_schedule_id <> l_old_schedule_id
522 OR p_carrier_id <> l_old_carrier_id
523 OR p_mode_of_transport <> l_old_mode_of_transport
524 OR p_service_level <> l_old_service_level
525 )
526 AND p_ui_page_name = GK_DLEG_WB_PAGE
527 THEN
528 l_wsh_trip_action := K_UPDATE;
529 --
530 --
531 END IF;
532 --}
533 END IF;
534 --
535 --
536 IF l_pickup_stop_id IS NOT NULL
537 THEN
538 --{
539 l_old_stop_location_id := FND_API.G_MISS_NUM;
540 --
541 --
542 FOR stop_location_rec IN stop_location_cur
543 (
544 p_stop_id => l_pickup_stop_id
545 )
546 LOOP
547 --{
548 l_old_stop_location_id := stop_location_rec.stop_location_id;
549 --}
550 END LOOP;
551 --
552 --
553 IF p_pickup_location_id <> l_old_stop_location_id
554 THEN
555 IF l_pickup_stop_id = p_first_stop_id
556 AND l_wsh_trip_action = K_UPDATE
557 THEN
558 l_first_stop_new_location_id := p_pickup_location_id;
559 ELSE
560 l_pickup_stop_action := K_UPDATE;
561 END IF;
562 END IF;
563 --}
564 END IF;
565 --
566 --
567 IF l_dropoff_stop_id IS NOT NULL
568 THEN
569 --{
570 l_old_stop_location_id := FND_API.G_MISS_NUM;
571 --
572 --
573 FOR stop_location_rec IN stop_location_cur
574 (
575 p_stop_id => l_dropoff_stop_id
576 )
577 LOOP
578 --{
579 l_old_stop_location_id := stop_location_rec.stop_location_id;
580 --}
581 END LOOP;
582 --
583 --
584 IF p_dropoff_location_id <> l_old_stop_location_id
585 THEN
586 IF l_dropoff_stop_id = p_last_stop_id
587 AND l_wsh_trip_action = K_UPDATE
588 THEN
589 l_last_stop_new_location_id := p_dropoff_location_id;
590 ELSE
591 l_dropoff_stop_action := K_UPDATE;
592 END IF;
593 END IF;
594 --}
595 END IF;
596 --}
597 END IF;
598 --
599 --
600 IF l_fte_trip_id IS NULL
601 AND l_fte_trip_name IS NOT NULL
602 THEN
603 --{
604 l_fte_trip_action := K_CREATE;
605 l_fte_wsh_trip_seq := 10;
606 --}
607 END IF;
608 --
609 IF l_debug_on THEN
610 WSH_DEBUG_SV.logmsg(l_api_name, ' 3 l_wsh_trip_action ' || l_wsh_trip_action,
611 WSH_DEBUG_SV.C_PROC_LEVEL);
612 END IF;
613 --
614 IF l_wsh_trip_action IN ( K_CREATE, K_UPDATE )
615 THEN
616 --{
617 fte_mls_util.derive_ship_method
618 (
619 p_carrier_id => p_carrier_id,
620 p_mode_of_transport => p_mode_of_transport,
621 p_service_level => p_service_level,
622 p_carrier_name => p_carrier_name,
623 p_mode_of_transport_meaning => p_mode_of_transport_meaning,
624 p_service_level_meaning => p_service_level_meaning,
625 x_ship_method_code => l_ship_method_code,
626 x_return_status => l_return_status
627 );
628 --
629 --
630 FTE_MLS_UTIL.api_post_call
631 (
632 p_api_name => 'FTE_MLS_UTIL.DERIVE_SHIP_METHOD',
633 p_api_return_status => l_return_status,
634 p_message_name => 'FTE_DELIVERY_LEG_UNEXP_ERROR',
635 p_delivery_id => p_delivery_id,
636 p_delivery_name => p_delivery_name,
637 x_number_of_errors => l_number_of_errors,
638 x_number_of_warnings => l_number_of_warnings,
639 x_return_status => x_return_status
640 );
641 --
642 --
643 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
644 OR l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR
645 THEN
646 --{
647 RAISE FND_API.G_EXC_ERROR;
648 --RETURN;
649 --}
650 END IF;
651 --}
652 END IF;
653 --
654 IF l_debug_on THEN
655 WSH_DEBUG_SV.logmsg(l_api_name, ' 4 l_pickup_stop_id ' || l_pickup_stop_id
656 ||'/ l_dropoff_stopId'|| l_dropoff_stop_id, WSH_DEBUG_SV.C_PROC_LEVEL);
657 END IF;
658 --
659 IF l_pickup_stop_id IS NULL
660 OR l_dropoff_stop_id IS NULL
661 THEN
662 --{
663 IF p_move_stop_seq_start IS NOT NULL
664 AND p_move_stop_seq_to IS NOT NULL
665 THEN
666 --{
667 IF l_debug_on THEN
668 WSH_DEBUG_SV.logmsg(l_api_name, ' 5 p_move_stop_seq_start ' || p_move_stop_seq_start,
669 WSH_DEBUG_SV.C_PROC_LEVEL);
670 END IF;
671
672 FOR stop_rec IN stop_cur
673 (
674 p_trip_id => p_wsh_trip_id,
675 p_stop_seq => p_move_stop_seq_start
676 )
677 LOOP
678 --{
679 l_stop_new_seq := stop_rec.stop_sequence_number
680 + p_move_stop_seq_to
681 - p_move_stop_seq_start;
682 --
683 --
684 IF l_debug_on THEN
685 WSH_DEBUG_SV.logmsg(l_api_name, ' 6 l_stop_new_seq ' || l_stop_new_seq
686 ||'/ l_stop_id'|| l_stop_id, WSH_DEBUG_SV.C_PROC_LEVEL);
687 END IF;
691 p_init_msg_list => FND_API.G_FALSE,
688 fte_mls_wrapper.create_update_stop
689 (
690 p_api_version_number => 1.0,
692 x_return_status => l_return_status,
693 x_msg_count => l_msg_count,
694 x_msg_data => l_msg_data,
695 p_action_code => K_UPDATE,
696 p_trip_id => p_wsh_trip_id,
697 p_stop_location_id => stop_rec.stop_location_id,
698 pp_stop_location_id => stop_rec.stop_location_id,
699 pp_STOP_ID => stop_rec.stop_id,
700 pp_TRIP_ID => p_wsh_trip_id,
701 pp_STOP_SEQUENCE_NUMBER => FND_API.G_MISS_NUM,
702 x_stop_id => l_stop_id
703 );
704 --
705 --
706 FTE_MLS_UTIL.api_post_call
707 (
708 p_api_name => 'FTE_MLS_WRAPPER.UPDATE_STOP',
709 p_api_return_status => l_return_status,
710 p_message_name => 'FTE_SEGMENT_STOP_UNEXP_ERROR',
711 p_trip_segment_id => p_wsh_trip_id,
712 p_trip_segment_name => p_wsh_trip_name,
713 p_trip_stop_id => stop_rec.stop_id,
714 p_stop_seq_number => stop_rec.stop_sequence_number,
715 x_number_of_errors => l_number_of_errors,
716 x_number_of_warnings => l_number_of_warnings,
717 x_return_status => x_return_status
718 );
719 --
720 --
721 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
722 OR l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR
723 THEN
724 --{
725 RAISE FND_API.G_EXC_ERROR;
726 --RETURN;
727 --}
728 END IF;
729 --
730 --
731 IF stop_rec.stop_id = l_pickup_stop_id
732 THEN
733 --{
734 l_pickup_stop_seq := l_stop_new_seq;
735 --}
736 END IF;
737 --
738 --
739 IF stop_rec.stop_id = l_dropoff_stop_id
740 THEN
741 --{
742 l_dropoff_stop_seq := l_stop_new_seq;
743 --}
744 END IF;
745 --}
746 END LOOP;
747 --}
748 END IF;
749 --}
750 END IF;
751 --
752 --
753 IF l_debug_on THEN
754 WSH_DEBUG_SV.logmsg(l_api_name, ' 7 l_wsh_trip_action ' || l_wsh_trip_action,
755 WSH_DEBUG_SV.C_PROC_LEVEL);
756 END IF;
757 IF l_wsh_trip_action = K_CREATE
758 THEN
759 --{
760 fte_mls_wrapper.create_update_trip
761 (
762 p_api_version_number => 1.0,
763 p_init_msg_list => FND_API.G_FALSE,
764 x_return_status => l_return_status,
765 x_msg_count => l_msg_count,
766 x_msg_data => l_msg_data,
767 p_action_code => l_wsh_trip_action,
768 p_rec_TRIP_ID => l_wsh_trip_id,
769 p_rec_NAME => p_wsh_trip_name,
770 -- adding vehicle info
771 p_rec_VEHICLE_ORGANIZATION_ID => p_veh_org_id,
772 p_rec_VEHICLE_NUMBER => p_veh_num,
773 p_rec_VEHICLE_NUM_PREFIX => p_veh_num_pre,
774 p_rec_VEHICLE_ITEM_ID => p_veh_item_id,
775 -- end adding vehicle info
776 p_rec_CARRIER_ID => p_carrier_id,
777 p_rec_SHIP_METHOD_CODE => l_ship_method_code,
778 p_rec_SERVICE_LEVEL => p_service_level,
779 p_rec_MODE_OF_TRANSPORT => p_mode_of_transport,
780 p_rec_LANE_ID => p_lane_id,
781 p_rec_SCHEDULE_ID => p_schedule_id,
782 p_rec_CONSOLIDATION_ALLOWED => 'N',
783 p_rec_APPEND_FLAG => 'N',
784 p_trip_name => l_wsh_trip_name,
785 x_trip_id => l_wsh_trip_id,
786 x_trip_name => l_wsh_trip_name,
787 p_rec_IGNORE_FOR_PLANNING => p_ignore_for_planning
788 );
789 --
790 --
791 FTE_MLS_UTIL.api_post_call
792 (
793 p_api_name => 'FTE_MLS_WRAPPER.CREATE_UPDATE_TRIP',
794 p_api_return_status => l_return_status,
795 p_message_name => 'FTE_DELIVERY_LEG_UNEXP_ERROR',
796 p_delivery_id => p_delivery_id,
797 p_delivery_name => p_delivery_name,
798 x_number_of_errors => l_number_of_errors,
799 x_number_of_warnings => l_number_of_warnings,
800 x_return_status => x_return_status
801 );
802 --
803 --
804 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
805 OR l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR
806 THEN
807 --{
808 RAISE FND_API.G_EXC_ERROR;
809 --RETURN;
810 --}
811 END IF;
812 --}
813 ELSIF l_wsh_trip_action = K_UPDATE
814 THEN
815 --{
816 assign_service_to_segment
817 (
818 p_init_msg_list => FND_API.G_FALSE,
819 x_return_status => l_return_status,
820 x_msg_count => l_msg_count,
821 x_msg_data => l_msg_data,
822 p_wsh_trip_id => l_wsh_trip_id,
823 p_wsh_trip_name => l_wsh_trip_name,
824 p_lane_id => p_lane_id,
825 p_schedule_id => p_schedule_id,
829 p_carrier_name => p_carrier_name,
826 p_carrier_id => p_carrier_id,
827 p_mode_of_transport => p_mode_of_transport,
828 p_service_level => p_service_level,
830 p_mode_of_transport_meaning => p_mode_of_transport_meaning,
831 p_service_level_meaning => p_service_level_meaning,
832 p_ship_method_code => l_ship_method_code,
833 p_first_stop_id => p_first_stop_id,
834 p_first_stop_seq => p_first_stop_seq,
835 p_first_stop_location_id => p_first_stop_location_id,
836 p_first_stop_new_location_id => l_first_stop_new_location_id,
837 p_first_stop_departure_date => p_first_stop_departure_date,
838 p_first_stop_arrival_date => p_first_stop_arrival_date,
839 p_last_stop_id => p_last_stop_id,
840 p_last_stop_seq => p_last_stop_seq,
841 p_last_stop_location_id => p_last_stop_location_id,
842 p_last_stop_new_location_id => l_last_stop_new_location_id,
843 p_last_stop_departure_date => p_last_stop_departure_date,
844 p_last_stop_arrival_date => p_last_stop_arrival_date,
845 p_veh_org_id => p_veh_org_id,
846 p_veh_item_id => p_veh_item_id
847 );
848 --
849 --
850 FTE_MLS_UTIL.api_post_call
851 (
852 p_api_name => 'FTE_DELIVERY_LEGS_PVT.ASSIGN_SERVICE_TO_SEGMENT',
853 p_api_return_status => l_return_status,
854 p_message_name => 'FTE_DELIVERY_LEG_UNEXP_ERROR',
855 p_delivery_id => p_delivery_id,
856 p_delivery_name => p_delivery_name,
857 x_number_of_errors => l_number_of_errors,
858 x_number_of_warnings => l_number_of_warnings,
859 x_return_status => x_return_status
860 );
861 --
862 --
863 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
864 OR l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR
865 THEN
866 --{
867 RAISE FND_API.G_EXC_ERROR;
868 --RETURN;
869 --}
870 END IF;
871 --}
872 END IF;
873 --
874 --
875 IF l_debug_on THEN
876 WSH_DEBUG_SV.logmsg(l_api_name, '8 l_pickup_stop_id ' || l_pickup_stop_id
877 || '/l_pickup_stop_action '||l_pickup_stop_action,
878 WSH_DEBUG_SV.C_PROC_LEVEL);
879 END IF;
880 --IF l_pickup_stop_id IS NULL
881 IF l_pickup_stop_action IN ( K_CREATE, K_UPDATE )
882 THEN
883 --{
884 -- PACK I : HBHAGAVA
885 -- Check if stop is getting created. If yes the
886 -- get the weight volume uom of delivery and
887 -- assign that to stop.
888 IF (l_pickup_stop_action = K_CREATE)
889 THEN
890 OPEN dlvy_weight_volume_cur(p_delivery_id);
891 FETCH dlvy_weight_volume_cur
892 INTO l_dlvy_weight_uom,
893 l_dlvy_volume_uom;
894 CLOSE dlvy_weight_volume_cur;
895 IF l_debug_on THEN
896 WSH_DEBUG_SV.logmsg(l_api_name, '9 l_pickup_stop_seq ' || l_pickup_stop_seq
897 || '/l_pickup_stop_id '||l_pickup_stop_id,
898 WSH_DEBUG_SV.C_PROC_LEVEL);
899 END IF;
900 fte_mls_wrapper.create_update_stop
901 (
902 p_api_version_number => 1.0,
903 p_init_msg_list => FND_API.G_FALSE,
904 x_return_status => l_return_status,
905 x_msg_count => l_msg_count,
906 x_msg_data => l_msg_data,
907 p_action_code => l_pickup_stop_action,
908 p_trip_id => l_wsh_trip_id,
909 p_stop_location_id => p_pickup_location_id,
910 p_planned_dep_date => p_pickup_departure_date,
911 pp_TRIP_ID => l_wsh_trip_id,
912 pp_STOP_SEQUENCE_NUMBER => FND_API.G_MISS_NUM,
913 pp_STOP_LOCATION_ID => p_pickup_location_id,
914 pp_PLANNED_ARRIVAL_DATE => p_pickup_arrival_date,
915 pp_PLANNED_DEPARTURE_DATE => p_pickup_departure_date,
916 pp_WEIGHT_UOM_CODE => l_dlvy_weight_uom,
917 pp_VOLUME_UOM_CODE => l_dlvy_volume_uom,
918 x_stop_id => l_pickup_stop_id
919 );
920
921 ELSE
922 fte_mls_wrapper.create_update_stop
923 (
924 p_api_version_number => 1.0,
925 p_init_msg_list => FND_API.G_FALSE,
926 x_return_status => l_return_status,
927 x_msg_count => l_msg_count,
928 x_msg_data => l_msg_data,
929 p_action_code => l_pickup_stop_action,
930 p_trip_id => l_wsh_trip_id,
931 p_stop_location_id => p_pickup_location_id,
932 p_planned_dep_date => p_pickup_departure_date,
933 pp_TRIP_ID => l_wsh_trip_id,
934 pp_STOP_ID => l_pickup_stop_id,
935 pp_STOP_SEQUENCE_NUMBER => l_pickup_stop_seq,
936 pp_STOP_LOCATION_ID => p_pickup_location_id,
937 pp_PLANNED_ARRIVAL_DATE => p_pickup_arrival_date,
938 pp_PLANNED_DEPARTURE_DATE => p_pickup_departure_date,
939 x_stop_id => return_pickup_stop_id
940 );
941 END IF;
942
943 --
944 --
945 FTE_MLS_UTIL.api_post_call
946 (
947 p_api_name => 'FTE_MLS_WRAPPER.CREATE_UPDATE_STOP',
948 p_api_return_status => l_return_status,
949 p_message_name => 'FTE_SEGMENT_STOP_UNEXP_ERROR',
950 p_trip_segment_id => l_wsh_trip_id,
951 p_trip_segment_name => l_wsh_trip_name,
952 p_trip_stop_id => l_pickup_stop_id,
953 p_stop_seq_number => l_pickup_stop_seq,
954 x_number_of_errors => l_number_of_errors,
955 x_number_of_warnings => l_number_of_warnings,
956 x_return_status => x_return_status
960 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
957 );
958 --
959 --
961 OR l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR
962 THEN
963 --{
964 RAISE FND_API.G_EXC_ERROR;
965 --RETURN;
966 --}
967 END IF;
968 --}
969 END IF;
970 --
971 IF l_debug_on THEN
972 WSH_DEBUG_SV.logmsg(l_api_name, '10 l_dropoff_stop_id ' || l_dropoff_stop_id
973 || '/l_dropoff_stop_action '||l_dropoff_stop_action,
974 WSH_DEBUG_SV.C_PROC_LEVEL);
975 END IF;
976 --
977 --IF l_dropoff_stop_id IS NULL
978 IF l_dropoff_stop_action IN ( K_CREATE, K_UPDATE )
979 THEN
980 --{
981
982 IF (l_dropoff_stop_action = K_CREATE)
983 THEN
984 OPEN dlvy_weight_volume_cur(p_delivery_id);
985 FETCH dlvy_weight_volume_cur
986 INTO l_dlvy_weight_uom,
987 l_dlvy_volume_uom;
988 CLOSE dlvy_weight_volume_cur;
989 fte_mls_wrapper.create_update_stop
990 (
991 p_api_version_number => 1.0,
992 p_init_msg_list => FND_API.G_FALSE,
993 x_return_status => l_return_status,
994 x_msg_count => l_msg_count,
995 x_msg_data => l_msg_data,
996 p_action_code => l_dropoff_stop_action,
997 p_trip_id => l_wsh_trip_id,
998 p_stop_location_id => p_dropoff_location_id,
999 p_planned_dep_date => p_dropoff_departure_date,
1000 pp_TRIP_ID => l_wsh_trip_id,
1001 pp_STOP_SEQUENCE_NUMBER => FND_API.G_MISS_NUM,
1002 pp_STOP_LOCATION_ID => p_dropoff_location_id,
1003 pp_PLANNED_ARRIVAL_DATE => p_dropoff_arrival_date,
1004 pp_PLANNED_DEPARTURE_DATE => p_dropoff_departure_date,
1005 pp_WEIGHT_UOM_CODE => l_dlvy_weight_uom,
1006 pp_VOLUME_UOM_CODE => l_dlvy_volume_uom,
1007 x_stop_id => l_dropoff_stop_id
1008 );
1009
1010 ELSE
1011 fte_mls_wrapper.create_update_stop
1012 (
1013 p_api_version_number => 1.0,
1014 p_init_msg_list => FND_API.G_FALSE,
1015 x_return_status => l_return_status,
1016 x_msg_count => l_msg_count,
1017 x_msg_data => l_msg_data,
1018 p_action_code => l_dropoff_stop_action,
1019 p_trip_id => l_wsh_trip_id,
1020 p_stop_location_id => p_dropoff_location_id,
1021 p_planned_dep_date => p_dropoff_departure_date,
1022 pp_TRIP_ID => l_wsh_trip_id,
1023 pp_STOP_ID => l_dropoff_stop_id,
1024 pp_STOP_SEQUENCE_NUMBER => l_dropoff_stop_seq,
1025 pp_STOP_LOCATION_ID => p_dropoff_location_id,
1026 pp_PLANNED_ARRIVAL_DATE => p_dropoff_arrival_date,
1027 pp_PLANNED_DEPARTURE_DATE => p_dropoff_departure_date,
1028 pp_WEIGHT_UOM_CODE => l_dlvy_weight_uom,
1029 pp_VOLUME_UOM_CODE => l_dlvy_volume_uom,
1030 x_stop_id => return_dropoff_stop_id
1031 );
1032
1033 END IF;
1034
1035
1036 --
1037 --
1038 FTE_MLS_UTIL.api_post_call
1039 (
1040 p_api_name => 'FTE_MLS_WRAPPER.CREATE_UPDATE_STOP',
1041 p_api_return_status => l_return_status,
1042 p_message_name => 'FTE_SEGMENT_STOP_UNEXP_ERROR',
1043 p_trip_segment_id => l_wsh_trip_id,
1044 p_trip_segment_name => l_wsh_trip_name,
1045 p_trip_stop_id => l_dropoff_stop_id,
1046 p_stop_seq_number => l_dropoff_stop_seq,
1047 x_number_of_errors => l_number_of_errors,
1048 x_number_of_warnings => l_number_of_warnings,
1049 x_return_status => x_return_status
1050 );
1051 --
1052 --
1053 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
1054 OR l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR
1055 THEN
1056 --{
1057 RAISE FND_API.G_EXC_ERROR;
1058 --RETURN;
1059 --}
1060 END IF;
1061 --}
1062 END IF;
1063 --
1064 --
1065 IF l_delivery_leg_action IN ( K_CREATE )
1066 THEN
1067 --{
1068 fte_mls_wrapper.Delivery_Action
1069 (
1070 p_api_version_number => 1.0,
1071 p_init_msg_list => FND_API.G_FALSE,
1072 x_return_status => l_return_status,
1073 x_msg_count => l_msg_count,
1074 x_msg_data => l_msg_data,
1075 p_action_code => 'ASSIGN-TRIP',
1076 p_delivery_id => p_delivery_id,
1077 p_asg_trip_id => l_wsh_trip_id,
1078 p_asg_trip_name => l_wsh_trip_name,
1079 p_asg_pickup_stop_id => l_pickup_stop_id,
1080 p_asg_pickup_loc_id => p_pickup_location_id,
1081 p_asg_pickup_arr_date => p_pickup_arrival_date,
1082 p_asg_pickup_dep_date => p_pickup_departure_date,
1083 p_asg_dropoff_stop_id => l_dropoff_stop_id,
1084 p_asg_dropoff_loc_id => p_dropoff_location_id,
1085 p_asg_dropoff_arr_date => p_dropoff_arrival_date,
1086 p_asg_dropoff_dep_date => p_dropoff_departure_date,
1087 x_trip_id => l_trip_id,
1088 x_trip_name => l_trip_name,
1089 x_delivery_leg_id => l_delivery_leg_id,
1093 --
1090 x_delivery_leg_seq => l_delivery_leg_seq
1091 ) ;
1092 --
1094 IF l_debug_on THEN
1095 WSH_DEBUG_SV.logmsg(l_api_name, ' DELIVERY Action RETURN VALUE ' || l_return_status,
1096 WSH_DEBUG_SV.C_PROC_LEVEL);
1097 END IF;
1098
1099 FTE_MLS_UTIL.api_post_call
1100 (
1101 p_api_name => 'FTE_MLS_WRAPPER.DELIVERY_ACTION',
1102 p_api_return_status => l_return_status,
1103 p_message_name => 'FTE_SEGMENT_STOP_UNEXP_ERROR',
1104 p_trip_segment_id => p_wsh_trip_id,
1105 p_trip_segment_name => p_wsh_trip_name,
1106 p_trip_stop_id => l_pickup_stop_id,
1107 p_stop_seq_number => p_pickup_stop_seq,
1108 x_number_of_errors => l_number_of_errors,
1109 x_number_of_warnings => l_number_of_warnings,
1110 x_return_status => x_return_status
1111 );
1112 --
1113 --
1114 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
1115 OR l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR
1116 THEN
1117 --{
1118 RAISE FND_API.G_EXC_ERROR;
1119 --RETURN;
1120 --}
1121 END IF;
1122 --}
1123 END IF;
1124 --
1125 --
1126 IF l_fte_trip_action = K_CREATE
1127 THEN
1128 --{
1129 fte_trips_pvt.Create_Update_Delete_Fte_Trip
1130 (
1131 p_api_version_number => 1.0,
1132 p_init_msg_list => FND_API.G_FALSE,
1133 x_return_status => l_return_status,
1134 x_msg_count => l_msg_count,
1135 x_msg_data => l_msg_data,
1136 p_action_code => l_fte_trip_action,
1137 pp_fte_trip_id => l_fte_trip_id,
1138 pp_NAME => l_fte_trip_name,
1139 pp_private_trip => 'N',
1140 pp_validation_required => 'Y',
1141 x_trip_id => l_fte_trip_id,
1142 x_name => l_fte_trip_name
1143 );
1144 --
1145 --
1146 FTE_MLS_UTIL.api_post_call
1147 (
1148 p_api_name => 'FTE_TRIPS_PVT.CREATE_UPDATE_DELETE_FTE_TRIP',
1149 p_api_return_status => l_return_status,
1150 p_message_name => 'FTE_DELIVERY_LEG_UNEXP_ERROR',
1151 p_delivery_id => p_delivery_id,
1152 p_delivery_name => p_delivery_name,
1153 x_number_of_errors => l_number_of_errors,
1154 x_number_of_warnings => l_number_of_warnings,
1155 x_return_status => x_return_status
1156 );
1157 --
1158 --
1159 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
1160 OR l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR
1161 THEN
1162 --{
1163 RAISE FND_API.G_EXC_ERROR;
1164 --RETURN;
1165 --}
1166 END IF;
1167 --
1168 --
1169 fte_wsh_trips_pvt.Create_Update_Trip_Wrapper
1170 (
1171 p_init_msg_list => FND_API.G_FALSE,
1172 x_return_status => l_return_status,
1173 x_msg_count => l_msg_count,
1174 x_msg_data => l_msg_data,
1175 p_action_code => l_fte_trip_action,
1176 pp_fte_trip_id => l_fte_trip_id,
1177 pp_wsh_trip_id => l_wsh_trip_id,
1178 pp_sequence_number => l_fte_wsh_trip_seq,
1179 p_validate_flag => 'N'
1180 );
1181 --
1182 --
1183 FTE_MLS_UTIL.api_post_call
1184 (
1185 p_api_name => 'FTE_WSH_TRIPS_PVT.CREATE_UPDATE_TRIP_WRAPPER',
1186 p_api_return_status => l_return_status,
1187 p_message_name => 'FTE_DELIVERY_LEG_UNEXP_ERROR',
1188 p_delivery_id => p_delivery_id,
1189 p_delivery_name => p_delivery_name,
1190 x_number_of_errors => l_number_of_errors,
1191 x_number_of_warnings => l_number_of_warnings,
1192 x_return_status => x_return_status
1193 );
1194 --
1195 --
1196 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
1197 OR l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR
1198 THEN
1199 --{
1200 RAISE FND_API.G_EXC_ERROR;
1201 --RETURN;
1202 --}
1203 END IF;
1204 --}
1205 END IF;
1206 --
1207 --
1208 --IF p_fte_trip_id IS NOT NULL
1209 IF l_fte_trip_action = K_CREATE
1210 OR (
1211 p_ui_page_name = GK_SEARCH_SEGMENTS_PAGE
1212 AND l_fte_trip_id IS NOT NULL
1213 )
1214 THEN
1215 --{
1216 UPDATE wsh_delivery_legs
1217 SET fte_trip_id = l_fte_trip_id,
1218 LAST_UPDATE_DATE = SYSDATE,
1219 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1220 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
1221 WHERE delivery_leg_id = l_delivery_leg_id;
1222 --
1223 --
1224 IF SQL%NOTFOUND
1225 THEN
1226 --{
1227 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1228 --
1229 --
1230 FTE_MLS_UTIL.api_post_call
1231 (
1232 p_api_name => 'FTE_DELIVERY_LEGS_PVT.UPDATE_DLEG_FTE_TRIP',
1233 p_api_return_status => l_return_status,
1234 p_message_name => 'FTE_DELIVERY_LEG_UNEXP_ERROR',
1235 p_delivery_id => p_delivery_id,
1236 p_delivery_name => p_delivery_name,
1237 x_number_of_errors => l_number_of_errors,
1238 x_number_of_warnings => l_number_of_warnings,
1239 x_return_status => x_return_status
1240 );
1244 OR l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR
1241 --
1242 --
1243 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
1245 THEN
1246 --{
1247 RAISE FND_API.G_EXC_ERROR;
1248 --RETURN;
1249 --}
1250 END IF;
1251 --}
1252 END IF;
1253 --}
1254 END IF;
1255 --
1256 --
1257 IF l_reprice_required = 'Y'
1258 THEN
1259 --{
1260 UPDATE wsh_delivery_legs
1261 SET reprice_required = l_reprice_required,
1262 LAST_UPDATE_DATE = SYSDATE,
1263 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1264 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
1265 WHERE delivery_leg_id IN
1266 ( SELECT delivery_leg_id
1267 FROM wsh_trip_stops wts,
1268 wsh_delivery_legs wdl
1269 WHERE wts.trip_id = p_wsh_trip_id
1270 AND wdl.pick_up_stop_id = wts.stop_id
1271 );
1272 --
1273 --
1274 IF SQL%NOTFOUND
1275 THEN
1276 --{
1277 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1278 --
1279 --
1280 FTE_MLS_UTIL.api_post_call
1281 (
1282 p_api_name => 'FTE_DELIVERY_LEGS_PVT.UPDATE_DLEG_REPRICE',
1283 p_api_return_status => l_return_status,
1284 p_message_name => 'FTE_DELIVERY_LEG_UNEXP_ERROR',
1285 p_delivery_id => p_delivery_id,
1286 p_delivery_name => p_delivery_name,
1287 x_number_of_errors => l_number_of_errors,
1288 x_number_of_warnings => l_number_of_warnings,
1289 x_return_status => x_return_status
1290 );
1291 --
1292 --
1293 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
1294 OR l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR
1295 THEN
1296 --{
1297 RAISE FND_API.G_EXC_ERROR;
1298 --RETURN;
1299 --}
1300 END IF;
1301 --}
1302 END IF;
1303 --}
1304 END IF;
1305 --
1306 IF l_debug_on THEN
1307 WSH_DEBUG_SV.logmsg(l_api_name,'BEFORE CALLING FREIGHT RATE:PriReqId:laneId:'||
1308 p_pricing_request_id||':'||p_lane_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1309 END IF;
1310 --
1311 IF p_pricing_request_id IS NOT NULL
1312 AND p_pricing_request_id > 0
1313 AND p_lane_id IS NOT NULL
1314 THEN
1315 --{
1316 FTE_FREIGHT_PRICING.MOVE_FC_TEMP_TO_MAIN
1317 (
1318 p_init_msg_list => FND_API.G_FALSE,
1319 x_return_status => l_return_status,
1320 p_delivery_leg_id => l_delivery_leg_id,
1321 p_lane_id => p_lane_id,
1322 p_schedule_id => p_schedule_id,
1323 p_request_id => p_pricing_request_id,
1324 p_service_type_code => p_service_level
1325 );
1326 --
1327 --
1328 FTE_MLS_UTIL.api_post_call
1329 (
1330 p_api_name => 'FTE_FREIGHT_PRICING.MOVE_FC_TEMP_TO_MAIN',
1331 p_api_return_status => l_return_status,
1332 p_message_name => 'FTE_SEGMENT_STOP_UNEXP_ERROR',
1333 p_trip_segment_id => p_wsh_trip_id,
1334 p_trip_segment_name => p_wsh_trip_name,
1335 p_trip_stop_id => l_pickup_stop_id,
1336 p_stop_seq_number => p_pickup_stop_seq,
1337 x_number_of_errors => l_number_of_errors,
1338 x_number_of_warnings => l_number_of_warnings,
1339 x_return_status => x_return_status
1340 );
1341 --
1342 --
1343 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
1344 OR l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR
1345 THEN
1346 --{
1347 RAISE FND_API.G_EXC_ERROR;
1348 --RETURN;
1349 --}
1350 END IF;
1351 --}
1352 END IF;
1353 --
1354 --
1355 x_ship_method_code := l_ship_method_code;
1356 x_delivery_leg_id := l_delivery_leg_id;
1357 x_delivery_leg_seq := l_delivery_leg_seq;
1358 x_pickup_stop_id := l_pickup_stop_id;
1359 x_dropoff_stop_id := l_dropoff_stop_id;
1360 x_pickup_stop_seq := l_pickup_stop_seq;
1361 x_dropoff_stop_seq := l_dropoff_stop_seq;
1362 x_wsh_trip_id := l_wsh_trip_id;
1363 x_wsh_trip_name := l_wsh_trip_name;
1364 x_fte_trip_id := l_fte_trip_id;
1365 x_fte_trip_name := l_fte_trip_name;
1366 --
1367 --
1368 IF l_debug_on THEN
1369 WSH_DEBUG_SV.logmsg(l_api_name, ' RETURN VALUE ' || l_number_of_errors,
1370 WSH_DEBUG_SV.C_PROC_LEVEL);
1371 END IF;
1372
1373 IF l_number_of_errors > 0
1374 THEN
1375 ROLLBACK TO PROCESS_DELIVERY_LEG_PUB;
1376 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1377 ELSIF l_number_of_warnings > 0 THEN
1378 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1379 ELSE
1380 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1381 END IF;
1382 --
1383 --
1384 -- Standard call to get message count and if count is 1,get message info.
1385 --
1386 FND_MSG_PUB.Count_And_Get
1387 (
1388 p_count => x_msg_count,
1389 p_data => x_msg_data,
1390 p_encoded => FND_API.G_FALSE
1391 );
1392 --
1393 --
1394 --}
1395 EXCEPTION
1396 --{
1397 WHEN FND_API.G_EXC_ERROR THEN
1398 ROLLBACK TO PROCESS_DELIVERY_LEG_PUB;
1399 x_return_status := FND_API.G_RET_STS_ERROR ;
1400 FND_MSG_PUB.Count_And_Get
1401 (
1402 p_count => x_msg_count,
1403 p_data => x_msg_data,
1407 ROLLBACK TO PROCESS_DELIVERY_LEG_PUB;
1404 p_encoded => FND_API.G_FALSE
1405 );
1406 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1408 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1409 FND_MSG_PUB.Count_And_Get
1410 (
1411 p_count => x_msg_count,
1412 p_data => x_msg_data,
1413 p_encoded => FND_API.G_FALSE
1414 );
1415 WHEN OTHERS THEN
1416 ROLLBACK TO PROCESS_DELIVERY_LEG_PUB;
1417 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1418 --
1419 --
1420 /* commented to be consistent with error handling as in WSH.
1421 IF FND_MSG_PUB.Check_Msg_Level
1422 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1423 THEN
1424 FND_MSG_PUB.Add_Exc_Msg
1425 ( G_PKG_NAME ,
1426 l_api_name
1427 );
1428 END IF;
1429 */
1430 wsh_util_core.default_handler('FTE_DELIVERY_LEGS_PVT.PROCESS_DELIVERY_LEG');
1431 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1432 --
1433 --
1434 FND_MSG_PUB.Count_And_Get
1435 (
1436 p_count => x_msg_count,
1437 p_data => x_msg_data,
1438 p_encoded => FND_API.G_FALSE
1439 );
1440 --}
1441 END process_delivery_leg;
1442 --
1443 --
1444 PROCEDURE assign_service_to_segment
1445 (
1446 P_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1447 X_return_status OUT NOCOPY VARCHAR2,
1448 X_msg_count OUT NOCOPY NUMBER,
1449 X_msg_data OUT NOCOPY VARCHAR2,
1450 p_ui_page_name IN VARCHAR2 DEFAULT GK_DLEG_WB_PAGE,
1451 p_wsh_trip_id IN NUMBER,
1452 p_wsh_trip_name IN VARCHAR2,
1453 p_lane_id IN NUMBER,
1454 p_carrier_id IN NUMBER,
1455 p_mode_of_transport IN VARCHAR2,
1456 p_service_level IN VARCHAR2,
1457 p_carrier_name IN VARCHAR2,
1458 p_mode_of_transport_meaning IN VARCHAR2,
1459 p_service_level_meaning IN VARCHAR2,
1460 p_ship_method_code IN VARCHAR2 DEFAULT NULL,
1461 p_schedule_id IN NUMBER DEFAULT NULL,
1462 p_first_stop_id IN NUMBER DEFAULT NULL,
1463 p_first_stop_seq IN NUMBER DEFAULT NULL,
1464 p_first_stop_location_id IN NUMBER DEFAULT NULL,
1465 p_first_stop_new_location_id IN NUMBER DEFAULT NULL,
1466 p_first_stop_departure_date IN DATE DEFAULT NULL,
1467 p_first_stop_arrival_date IN DATE DEFAULT NULL,
1468 p_last_stop_id IN NUMBER DEFAULT NULL,
1469 p_last_stop_seq IN NUMBER DEFAULT NULL,
1470 p_last_stop_location_id IN NUMBER DEFAULT NULL,
1471 p_last_stop_new_location_id IN NUMBER DEFAULT NULL,
1472 p_last_stop_departure_date IN DATE DEFAULT NULL,
1473 p_last_stop_arrival_date IN DATE DEFAULT NULL,
1474 p_veh_org_id IN NUMBER DEFAULT NULL,
1475 p_veh_item_id IN NUMBER DEFAULT NULL
1476 )
1477 IS
1478 --{
1479 l_api_name CONSTANT VARCHAR2(30) := 'assign_service_to_segment';
1480 l_api_version CONSTANT NUMBER := 1.0;
1481 --
1482 --
1483 l_return_status VARCHAR2(32767);
1484 l_msg_count NUMBER;
1485 l_msg_data VARCHAR2(32767);
1486 l_program_name VARCHAR2(32767);
1487 p_action_type VARCHAR2(32767);
1488 l_stop_id NUMBER;
1489 l_trip_id NUMBER;
1490 l_wsh_trip_name VARCHAR2(32767);
1491 --
1492 --
1493 l_number_of_errors NUMBER := 0;
1494 l_number_of_warnings NUMBER := 0;
1495 --
1496 l_first_stop_id NUMBER;
1497 l_first_stop_location_id NUMBER;
1498 l_first_stop_seq NUMBER;
1499 l_first_stop_dep_date DATE;
1500 l_first_stop_arr_date DATE;
1501 --
1502 l_last_stop_id NUMBER;
1503 l_last_stop_location_id NUMBER;
1504 l_last_stop_seq NUMBER;
1505 l_last_stop_dep_date DATE;
1506 l_last_stop_arr_date DATE;
1507 --
1508 --
1509 l_stop_location_id NUMBER;
1510 --
1511 --
1512 CURSOR stop_cur (p_trip_id NUMBER)
1513 IS
1514 SELECT stop_id, stop_location_id, stop_sequence_number
1515 FROM wsh_trip_stops
1516 WHERE trip_id = p_trip_id
1517 order by stop_sequence_number;
1518
1519 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
1520 --
1521 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || 'FTEVDLGB' || '.' || 'assign_service_to_segment';
1522
1523 --}
1524 BEGIN
1525 --{
1526 --
1527 -- Standard Start of API savepoint
1528 SAVEPOINT ASSIGN_SERVICE_TO_SEGMENT_PUB;
1529 --
1530 --
1531 -- Initialize message list if p_init_msg_list is set to TRUE.
1532 --
1533 --
1534 IF FND_API.to_Boolean( p_init_msg_list )
1535 THEN
1536 FND_MSG_PUB.initialize;
1537 END IF;
1538 --
1539 IF l_debug_on THEN
1543 p_wsh_trip_name ,WSH_DEBUG_SV.C_PROC_LEVEL);
1540 wsh_debug_sv.push(l_module_name);
1541
1542 WSH_DEBUG_SV.logmsg(l_module_name,' Trip Name => ' ||
1544 END IF;
1545
1546
1547 --
1548 IF p_schedule_id IS NOT NULL
1549 THEN
1550 --{
1551 IF (
1552 p_first_stop_id IS NULL
1553 OR p_first_stop_location_id IS NULL
1554 OR p_first_stop_departure_date IS NULL
1555 --OR p_first_stop_arrival_date IS NULL
1556 OR p_last_stop_id IS NULL
1557 OR p_last_stop_location_id IS NULL
1558 --OR p_last_stop_departure_date IS NULL
1559 OR p_last_stop_arrival_date IS NULL
1560 )
1561 THEN
1562 --{
1563 FTE_MLS_UTIL.api_post_call
1564 (
1565 p_api_name => 'FIRST_LAST_ID_DATES_NULL',
1566 p_api_return_status => WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR,
1567 p_message_name => 'FTE_SEGMENT_UNEXP_ERROR',
1568 p_trip_segment_id => p_wsh_trip_id,
1569 p_trip_segment_name => p_wsh_trip_name,
1570 x_number_of_errors => l_number_of_errors,
1571 x_number_of_warnings => l_number_of_warnings,
1572 x_return_status => x_return_status
1573 );
1574 --
1575 --
1576 RAISE FND_API.G_EXC_ERROR;
1577 --}
1578 END IF;
1579 --}
1580 END IF;
1581 --
1582 --
1583 -- Initialize API return status to success
1584 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1585 l_number_of_errors := 0;
1586 l_number_of_warnings := 0;
1587 l_first_stop_id := NULL;
1588 l_last_stop_id := NULL;
1589 --
1590 --
1591 IF p_schedule_id IS NOT NULL
1592 OR p_first_stop_new_location_id IS NOT NULL
1593 OR p_last_stop_new_location_id IS NOT NULL
1594 THEN
1595 --{
1596 /*
1597 FOR stop_rec IN stop_cur (p_trip_id => p_wsh_trip_id)
1598 LOOP
1599 --{
1600 IF l_first_stop_id IS NULL
1601 THEN
1602 --{
1603 l_first_stop_id := stop_rec.stop_id;
1604 l_first_stop_location_id := NVL(
1605 p_first_stop_location_id,
1606 stop_rec.stop_location_id
1607 );
1608 l_first_stop_seq := stop_rec.stop_sequence_number;
1609 l_first_stop_dep_date := NVL(
1610 l_schedule_departure_date,
1611 stop_rec.planned_departure_date;
1612 );
1613 l_first_stop_arr_date := LEAST
1614 (
1615 NVL
1616 (
1617 l_schedule_departure_date,
1618 stop_rec.planned_arrival_date
1619 ),
1620
1621 stop_rec.planned_arrival_date;
1622
1623 );
1624 --}
1625 END IF;
1626 --
1627 --
1628 l_last_stop_id := stop_rec.stop_id;
1629 l_last_stop_location_id := NVL(
1630 p_last_stop_location_id,
1631 stop_rec.stop_location_id
1632 );
1633 l_last_stop_seq := stop_rec.stop_sequence_number;
1634 l_last_stop_dep_date := GREATEST
1635 (
1636 NVL
1637 (
1638 l_schedule_arrival_date,
1639 stop_rec.planned_departure_date;
1640 ),
1641 stop_rec.planned_departure_date;
1642 );
1643 l_last_stop_arr_date := NVL(
1644 l_schedule_arrival_date,
1645 stop_rec.planned_arrival_date;
1646 );
1647
1648 --}
1649 END LOOP;
1650 */
1651 --
1652 --
1653 --IF l_first_stop_id IS NOT NULL
1654 IF p_schedule_id IS NOT NULL
1655 OR p_first_stop_new_location_id IS NOT NULL
1656 THEN
1657 --{
1658 l_stop_location_id := NVL(p_first_stop_new_location_id,p_first_stop_location_id);
1659 --
1660 --
1661 fte_mls_wrapper.create_update_stop
1662 (
1663 p_api_version_number => 1.0,
1664 p_init_msg_list => FND_API.G_FALSE,
1665 x_return_status => l_return_status,
1666 x_msg_count => l_msg_count,
1667 x_msg_data => l_msg_data,
1668 p_action_code => 'UPDATE',
1669 p_trip_id => p_wsh_trip_id,
1670 p_stop_location_id => l_stop_location_id,
1671 p_planned_dep_date => p_first_stop_departure_date,
1672 pp_stop_location_id => l_stop_location_id,
1673 pp_STOP_ID => p_first_stop_id,
1674 pp_TRIP_ID => p_wsh_trip_id,
1675 pp_STOP_SEQUENCE_NUMBER => p_first_stop_seq,
1676 pp_PLANNED_ARRIVAL_DATE => p_first_stop_arrival_date,
1677 pp_PLANNED_DEPARTURE_DATE => p_first_stop_departure_date,
1678 x_stop_id => l_stop_id
1679 );
1680 --
1681 --
1682 FTE_MLS_UTIL.api_post_call
1683 (
1684 p_api_name => 'FTE_MLS_WRAPPER.UPDATE_STOP',
1685 p_api_return_status => l_return_status,
1686 p_message_name => 'FTE_SEGMENT_STOP_UNEXP_ERROR',
1690 p_stop_seq_number => p_first_stop_seq,
1687 p_trip_segment_id => p_wsh_trip_id,
1688 p_trip_segment_name => p_wsh_trip_name,
1689 p_trip_stop_id => p_first_stop_id,
1691 x_number_of_errors => l_number_of_errors,
1692 x_number_of_warnings => l_number_of_warnings,
1693 x_return_status => x_return_status
1694 );
1695 --
1696 --
1697 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
1698 OR l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR
1699 THEN
1700 --{
1701 RAISE FND_API.G_EXC_ERROR;
1702 --RETURN;
1703 --}
1704 END IF;
1705 --}
1706 END IF;
1707 --
1708 --
1709 --IF l_last_stop_id IS NOT NULL
1710 IF p_schedule_id IS NOT NULL
1711 OR p_last_stop_new_location_id IS NOT NULL
1712 THEN
1713 --{
1714 l_stop_location_id := NVL(p_last_stop_new_location_id,p_last_stop_location_id);
1715 --
1716 --
1717 fte_mls_wrapper.create_update_stop
1718 (
1719 p_api_version_number => 1.0,
1720 p_init_msg_list => FND_API.G_FALSE,
1721 x_return_status => l_return_status,
1722 x_msg_count => l_msg_count,
1723 x_msg_data => l_msg_data,
1724 p_action_code => 'UPDATE',
1725 p_trip_id => p_wsh_trip_id,
1726 p_stop_location_id => l_stop_location_id,
1727 p_planned_dep_date => p_last_stop_departure_date,
1728 pp_stop_location_id => l_stop_location_id,
1729 pp_STOP_ID => p_last_stop_id,
1730 pp_TRIP_ID => p_wsh_trip_id,
1731 pp_STOP_SEQUENCE_NUMBER => p_last_stop_seq,
1732 pp_PLANNED_ARRIVAL_DATE => p_last_stop_arrival_date,
1733 pp_PLANNED_DEPARTURE_DATE => p_last_stop_departure_date,
1734 x_stop_id => l_stop_id
1735 );
1736 --
1737 --
1738 FTE_MLS_UTIL.api_post_call
1739 (
1740 p_api_name => 'FTE_MLS_WRAPPER.UPDATE_STOP',
1741 p_api_return_status => l_return_status,
1742 p_message_name => 'FTE_SEGMENT_STOP_UNEXP_ERROR',
1743 p_trip_segment_id => p_wsh_trip_id,
1744 p_trip_segment_name => p_wsh_trip_name,
1745 p_trip_stop_id => p_last_stop_id,
1746 p_stop_seq_number => p_last_stop_seq,
1747 x_number_of_errors => l_number_of_errors,
1748 x_number_of_warnings => l_number_of_warnings,
1749 x_return_status => x_return_status
1750 );
1751 --
1752 --
1753 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
1754 OR l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR
1755 THEN
1756 --{
1757 RAISE FND_API.G_EXC_ERROR;
1758 --RETURN;
1759 --}
1760 END IF;
1761 --}
1762 END IF;
1763 --}
1764 END IF;
1765 --
1766 --
1767 --{
1768 fte_mls_wrapper.create_update_trip
1769 (
1770 p_api_version_number => 1.0,
1771 p_init_msg_list => FND_API.G_FALSE,
1772 x_return_status => l_return_status,
1773 x_msg_count => l_msg_count,
1774 x_msg_data => l_msg_data,
1775 p_action_code => 'UPDATE',
1776 p_rec_TRIP_ID => p_wsh_trip_id,
1777 p_rec_NAME => p_wsh_trip_name,
1778 p_rec_CARRIER_ID => p_carrier_id,
1779 p_rec_SHIP_METHOD_CODE => p_ship_method_code,
1780 p_rec_SERVICE_LEVEL => p_service_level,
1781 p_rec_MODE_OF_TRANSPORT => p_mode_of_transport,
1782 p_rec_LANE_ID => p_lane_id,
1783 p_rec_SCHEDULE_ID => p_schedule_id,
1784 p_trip_name => p_wsh_trip_name,
1785 p_rec_VEHICLE_ORGANIZATION_ID => p_veh_org_id,
1786 p_rec_VEHICLE_ITEM_ID => p_veh_item_id,
1787 x_trip_id => l_trip_id,
1788 x_trip_name => l_wsh_trip_name
1789 );
1790 --
1791 /*
1792 -- Need to add action code CREATE, leg/delivery/vehicle/vehOrg
1793 FTE_MLS_TEST_NT.UPDATE_SERVICE_ON_TRIP(
1794 p_API_VERSION_NUMBER => 1.0,
1795 p_INIT_MSG_LIST => FND_API.G_TRUE,
1796 p_COMMIT => FND_API.G_FALSE,
1797 p_CALLER => 'FTE',
1798 p_SERVICE_ACTION => 'UPDATE',
1799 p_DELIVERY_ID => p_delivery_id,
1800 p_DELIVERY_LEG_ID => p_delivery_leg_id,
1801 p_TRIP_ID => p_wsh_trip_id,
1802 p_LANE_ID => p_lane_id,
1803 p_SCHEDULE_ID => p_schedule_id,
1804 p_CARRIER_ID => p_carrier_id,
1805 p_SERVICE_LEVEL => p_service_level,
1806 p_MODE_OF_TRANSPORT => p_mode_of_transport,
1807 p_VEHICLE_ITEM_ID => p_vehicle_item_id,
1808 p_VEHICLE_ORG_ID => p_vehicle_org_id,
1809 p_RANK_ID => null,
1810 x_RETURN_STATUS => l_return_status,
1811 x_MSG_COUNT => l_msg_count,
1812 x_MSG_DATA => l_msg_data);
1813 --
1814 */
1815 FTE_MLS_UTIL.api_post_call
1816 (
1817 p_api_name => 'FTE_MLS_WRAPPER.CREATE_UPDATE_TRIP',
1821 p_trip_segment_name => p_wsh_trip_name,
1818 p_api_return_status => l_return_status,
1819 p_message_name => 'FTE_SEGMENT_UNEXP_ERROR',
1820 p_trip_segment_id => p_wsh_trip_id,
1822 x_number_of_errors => l_number_of_errors,
1823 x_number_of_warnings => l_number_of_warnings,
1824 x_return_status => x_return_status
1825 );
1826 --
1827 --
1828 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
1829 OR l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR
1830 THEN
1831 --{
1832 RAISE FND_API.G_EXC_ERROR;
1833 --RETURN;
1834 --}
1835 END IF;
1836 --}
1837 --
1838 --
1839 IF l_number_of_errors > 0
1840 THEN
1841 ROLLBACK TO ASSIGN_SERVICE_TO_SEGMENT_PUB;
1842 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1843 ELSIF l_number_of_warnings > 0
1844 THEN
1845 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1846 ELSE
1847 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1848 END IF;
1849 --
1850 --
1851 -- Standard call to get message count and if count is 1,get message info.
1852 --
1853 FND_MSG_PUB.Count_And_Get
1854 (
1855 p_count => x_msg_count,
1856 p_data => x_msg_data,
1857 p_encoded => FND_API.G_FALSE
1858 );
1859 --
1860 --
1861 --}
1862 EXCEPTION
1863 --{
1864 WHEN FND_API.G_EXC_ERROR THEN
1865 ROLLBACK TO ASSIGN_SERVICE_TO_SEGMENT_PUB;
1866 x_return_status := FND_API.G_RET_STS_ERROR ;
1867 FND_MSG_PUB.Count_And_Get
1868 (
1869 p_count => x_msg_count,
1870 p_data => x_msg_data,
1871 p_encoded => FND_API.G_FALSE
1872 );
1873 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1874 ROLLBACK TO ASSIGN_SERVICE_TO_SEGMENT_PUB;
1875 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1876 FND_MSG_PUB.Count_And_Get
1877 (
1878 p_count => x_msg_count,
1879 p_data => x_msg_data,
1880 p_encoded => FND_API.G_FALSE
1881 );
1882 WHEN OTHERS THEN
1883 ROLLBACK TO ASSIGN_SERVICE_TO_SEGMENT_PUB;
1884 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1885 --
1886 --
1887 wsh_util_core.default_handler('FTE_DELIVERY_LEGS_PVT.assign_service_to_segment');
1888 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1889 --
1890 --
1891 FND_MSG_PUB.Count_And_Get
1892 (
1893 p_count => x_msg_count,
1894 p_data => x_msg_data,
1895 p_encoded => FND_API.G_FALSE
1896 );
1897 --}
1898 END assign_service_to_segment;
1899 --
1900 --
1901 PROCEDURE build_delivery_leg_info
1902 (
1903 P_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1904 X_return_status OUT NOCOPY VARCHAR2,
1905 X_msg_count OUT NOCOPY NUMBER,
1906 X_msg_data OUT NOCOPY VARCHAR2,
1907 p_dleg_id IN NUMBER,
1908 x_PUStopId OUT NOCOPY NUMBER,
1909 x_PUStopLocationId OUT NOCOPY NUMBER,
1910 x_PUStopLocation OUT NOCOPY VARCHAR2,
1911 x_PUStopCSZ OUT NOCOPY VARCHAR2,
1912 x_PUStopCountry OUT NOCOPY VARCHAR2,
1913 x_PUStopActualArrivalDate OUT NOCOPY DATE,
1914 x_PUStopActualDepartureDate OUT NOCOPY DATE,
1915 x_PUStopPlannedArrivalDate OUT NOCOPY DATE,
1916 x_PUStopPlannedDepartureDate OUT NOCOPY DATE,
1917 x_PUStopSequenceNumber OUT NOCOPY NUMBER,
1918 x_PUStopStatusCode OUT NOCOPY VARCHAR2,
1919 x_PUStopTripId OUT NOCOPY NUMBER,
1920 x_DOStopId OUT NOCOPY NUMBER,
1921 x_DOStopLocationId OUT NOCOPY NUMBER,
1922 x_DOStopLocation OUT NOCOPY VARCHAR2,
1923 x_DOStopCSZ OUT NOCOPY VARCHAR2,
1924 x_DOStopCountry OUT NOCOPY VARCHAR2,
1925 x_DOStopActualArrivalDate OUT NOCOPY DATE,
1926 x_DOStopActualDepartureDate OUT NOCOPY DATE,
1927 x_DOStopPlannedArrivalDate OUT NOCOPY DATE,
1928 x_DOStopPlannedDepartureDate OUT NOCOPY DATE,
1929 x_DOStopSequenceNumber OUT NOCOPY NUMBER,
1930 x_DOStopStatusCode OUT NOCOPY VARCHAR2,
1931 x_DOStopTripId OUT NOCOPY NUMBER,
1932 x_CarrierId OUT NOCOPY NUMBER,
1933 x_CarrierName OUT NOCOPY VARCHAR2,
1934 x_LaneId OUT NOCOPY NUMBER,
1935 x_LaneNumber OUT NOCOPY VARCHAR2,
1936 x_ScheduleId OUT NOCOPY NUMBER,
1937 x_ModeOfTransport OUT NOCOPY VARCHAR2,
1938 x_ModeOfTransportMeaning OUT NOCOPY VARCHAR2,
1939 x_ServiceLevel OUT NOCOPY VARCHAR2,
1940 x_ServiceLevelMeaning OUT NOCOPY VARCHAR2,
1941 x_ShipMethodCode OUT NOCOPY VARCHAR2,
1942 x_TripSegmentId OUT NOCOPY NUMBER,
1943 x_TripSegmentName OUT NOCOPY VARCHAR2,
1944 x_TripSegmentStatusCode OUT NOCOPY VARCHAR2,
1945 x_Price OUT NOCOPY NUMBER,
1946 x_Currency OUT NOCOPY VARCHAR2,
1947 x_OriginStopId OUT NOCOPY NUMBER,
1948 x_OriginStopStatusCode OUT NOCOPY VARCHAR2,
1949 x_OriginStopSequenceNumber OUT NOCOPY NUMBER,
1950 x_OriginStopLocationId OUT NOCOPY NUMBER,
1951 x_OriginLocation OUT NOCOPY VARCHAR2,
1952 x_OriginCSZ OUT NOCOPY VARCHAR2,
1953 x_OriginCountry OUT NOCOPY VARCHAR2,
1954 x_OriginDepartureDate OUT NOCOPY DATE,
1955 x_OriginArrivalDate OUT NOCOPY DATE,
1956 x_DestStopId OUT NOCOPY NUMBER,
1957 x_DestStopStatusCode OUT NOCOPY VARCHAR2,
1961 x_DestCSZ OUT NOCOPY VARCHAR2,
1958 x_DestStopSequenceNumber OUT NOCOPY NUMBER,
1959 x_DestStopLocationId OUT NOCOPY NUMBER,
1960 x_DestLocation OUT NOCOPY VARCHAR2,
1962 x_DestCountry OUT NOCOPY VARCHAR2,
1963 x_DestDepartureDate OUT NOCOPY DATE,
1964 x_DestArrivalDate OUT NOCOPY DATE,
1965 x_TenderStatus OUT NOCOPY VARCHAR2,
1966 x_TripPlannedFlag OUT NOCOPY VARCHAR2,
1967 x_TripShipmentsTypeFlag OUT NOCOPY VARCHAR2,
1968 x_DOStopPhysLocationId OUT NOCOPY NUMBER,
1969 x_DestStopPhysLocationId OUT NOCOPY NUMBER,
1970 x_BolNumber OUT NOCOPY VARCHAR2,
1971 x_VehicleOrgId OUT NOCOPY NUMBER,
1972 x_VehicleItemId OUT NOCOPY NUMBER,
1973 x_ParentDLegId OUT NOCOPY NUMBER,
1974 x_RankId OUT NOCOPY NUMBER,
1975 x_RoutingRuleId OUT NOCOPY NUMBER,
1976 x_AppendFlag OUT NOCOPY VARCHAR2,
1977 x_ParentDlvyName OUT NOCOPY VARCHAR2
1978 )
1979 IS
1980 --{
1981 --
1982 --
1983 l_api_name CONSTANT VARCHAR2(30) := 'build_delivery_leg_info';
1984 l_api_version CONSTANT NUMBER := 1.0;
1985
1986 --
1987 --
1988
1989 l_return_status VARCHAR2(32767);
1990
1991 -- Added l_location_id for 11.5.10+ TP ER Locations
1992 l_location_id NUMBER;
1993
1994 l_location VARCHAR2(1000);
1995 l_csz VARCHAR2(1000);
1996 l_country VARCHAR2(100);
1997 --
1998 --PICKUP CURSOR
1999 CURSOR get_pickup_info_cur (dLegId NUMBER)
2000 IS
2001 SELECT st.actual_arrival_date,st.actual_departure_date,
2002 st.planned_arrival_date,st.planned_departure_date,
2003 st.stop_sequence_number, st.status_code,
2004 st.stop_location_id, st.trip_id, st.stop_id,
2005 t.carrier_id carrier_id, t.lane_id lane_id,
2006 t.schedule_id schedule_id, t.mode_of_transport mode_of_transport,
2007 t.service_level service_level, t.ship_method_code ship_method_code,
2008 t.name name, t.status_code trip_status,
2009 t.load_tender_status, -- new 11/6 added by dmlewis
2010 t.planned_flag, t.shipments_type_flag, t.vehicle_item_id,
2011 t.vehicle_organization_id, dl.parent_delivery_leg_id,
2012 t.rank_id, t.routing_rule_id, t.append_flag
2013 FROM wsh_delivery_legs dl, wsh_trip_stops st, wsh_trips t
2014 WHERE dl.delivery_leg_id = dLegId
2015 AND dl.pick_up_stop_id = st.stop_id
2016 AND t.trip_id = st.trip_id;
2017 --
2018 --
2019 --DROPOFF CURSOR
2020 --Modified for 11.5.10+ TP ER Locations
2021 --
2022 CURSOR get_dropoff_info_cur (dLegId NUMBER)
2023 IS
2024 SELECT st.stop_id, st.physical_stop_id, st.trip_id,
2025 st.stop_location_id, st.physical_location_id,
2026 st.actual_arrival_date, st.actual_departure_date,
2027 st.planned_arrival_date, st.planned_departure_date,
2028 st.stop_sequence_number, st.status_code
2029 FROM wsh_trip_stops st
2030 WHERE physical_stop_id IS NULL
2031 START WITH stop_id IN
2032 (SELECT stop_id FROM wsh_trip_stops, wsh_delivery_legs
2033 WHERE drop_off_stop_id = stop_id
2034 AND delivery_leg_id = dlegId)
2035 CONNECT BY PRIOR physical_stop_id = stop_id;
2036 --
2037 -- GET PRICE CURSOR
2038 CURSOR get_price_info_cur (dLegId NUMBER)
2039 IS
2040 SELECT TOTAL_AMOUNT,CURRENCY_CODE FROM WSH_FREIGHT_COSTS
2041 WHERE DELIVERY_LEG_ID = dLegId
2042 AND LINE_TYPE_CODE = 'SUMMARY'
2043 AND DELIVERY_DETAIL_ID IS NULL;
2044 --
2045 --
2046 --
2047 -- GET TRIP SEGMENT ORIGIN DESTINATION CURSOR
2048 CURSOR get_trip_seg_origin_cur (trip_seg_Id NUMBER)
2049 IS
2050 SELECT stop_id, status_code, stop_sequence_number,
2051 stop_location_id,planned_departure_date,
2052 planned_arrival_date
2053 FROM wsh_trip_stops where trip_id = trip_seg_id
2054 AND stop_sequence_number = (
2055 SELECT min(stop_sequence_number)
2056 FROM wsh_trip_stops where trip_id = trip_seg_id);
2057 --
2058 --
2059 -- GET TRIP SEGMENT ORIGIN DESTINATION CURSOR
2060 CURSOR get_trip_seg_dest_cur (trip_seg_Id NUMBER)
2061 IS
2062 SELECT stop_id, status_code, stop_sequence_number,
2063 stop_location_id,planned_departure_date,
2064 planned_arrival_date, physical_location_id
2065 FROM wsh_trip_stops where trip_id = trip_seg_id
2066 AND stop_sequence_number = (
2067 SELECT max(stop_sequence_number)
2068 FROM wsh_trip_stops where trip_id = trip_seg_id);
2069 --
2070 -- GET Mode Of Transport
2071 --
2072 CURSOR get_mode_of_transport_cur (c_mode_code VARCHAR2)
2073 IS
2074 SELECT meaning mode_of_transport_meaning
2075 FROM fnd_lookup_values_vl
2076 WHERE lookup_type = 'WSH_MODE_OF_TRANSPORT'
2077 AND lookup_code = c_mode_code;
2078 --
2079 --
2080 -- GET Service Level
2081 --
2082 CURSOR get_service_level_cur (c_service_code VARCHAR2)
2083 IS
2084 SELECT meaning service_type_meaning
2085 FROM fnd_lookup_values_vl
2086 WHERE lookup_type = 'WSH_SERVICE_LEVELS'
2087 AND lookup_code = c_service_code;
2088 --
2089 CURSOR get_lane_cur (c_lane_id VARCHAR2)
2090 IS
2091 SELECT lane_number
2092 FROM fte_lanes
2093 WHERE lane_id = c_lane_id;
2094 --
2095 -- GET BOL Number
2096 CURSOR get_bol_number_cur (dLegId NUMBER)
2097 IS
2098 SELECT sequence_number
2099 FROM wsh_document_instances
2100 WHERE entity_id = dLegId
2101 AND entity_name = 'WSH_DELIVERY_LEGS'
2102 AND document_type = 'BOL'
2103 AND status = 'PLANNED';
2104 --
2105 -- GET PARENT DELIVERY INFO
2106 CURSOR get_parent_dlvy_info_cur (c_parentDLegId NUMBER)
2107 IS
2108 SELECT wnd.delivery_id, wnd.name
2112 --
2109 FROM wsh_new_deliveries wnd, wsh_delivery_legs wdl
2110 WHERE wdl.delivery_leg_id = c_parentDLegId
2111 AND wdl.delivery_id = wnd.delivery_id;
2113 --
2114 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
2115 --
2116 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || 'FTE_DELIVERY_LEGS_PVT' || '.' || l_api_name;
2117
2118
2119
2120 --}
2121 BEGIN
2122 --{
2123 X_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2124 --
2125 -- INITIALIZATION
2126
2127 IF l_debug_on THEN
2128 wsh_debug_sv.push(l_module_name);
2129 END IF;
2130
2131 --
2132 IF FND_API.to_Boolean( p_init_msg_list )
2133 THEN
2134 FND_MSG_PUB.initialize;
2135 END IF;
2136 --
2137 --
2138 -- PICKUP STOP INFORMATION
2139 x_PUStopId := NULL;
2140 x_PUStopLocationId := NULL;
2141 x_PUStopLocation := NULL;
2142 x_PUStopCSZ := NULL;
2143 x_PUStopCountry := NULL;
2144 x_PUStopActualArrivalDate := NULL;
2145 x_PUStopActualDepartureDate := NULL;
2146 x_PUStopPlannedArrivalDate := NULL;
2147 x_PUStopPlannedDepartureDate := NULL;
2148 x_PUStopSequenceNumber := NULL;
2149 x_PUStopStatusCode := NULL;
2150 x_PUStopTripId := NULL;
2151 x_CarrierId := NULL;
2152 x_LaneId := NULL;
2153 x_ScheduleId := NULL;
2154 x_ModeOfTransport := NULL;
2155 x_ServiceLevel := NULL;
2156 x_ShipMethodCode := NULL;
2157 x_TripSegmentId := NULL;
2158 x_TripSegmentName := NULL;
2159 x_TripSegmentStatusCode := NULL;
2160 x_TenderStatus := NULL;
2161 x_TripPlannedFlag := NULL;
2162 x_TripShipmentsTypeFlag := NULL;
2163 x_VehicleOrgId := NULL;
2164 x_VehicleItemId := NULL;
2165 x_ParentDLegId := NULL;
2166 x_RankId := NULL;
2167 x_RoutingRuleId := NULL;
2168 x_AppendFlag := NULL;
2169 --
2170
2171 IF l_debug_on THEN
2172 WSH_DEBUG_SV.logmsg(l_module_name,
2173 ' Exiting out of dleg info 1 ',
2174 WSH_DEBUG_SV.C_PROC_LEVEL);
2175 END IF;
2176
2177
2178 --
2179 FOR get_pickup_info_rec IN get_pickup_info_cur(p_dLeg_id)
2180 LOOP
2181 --{
2182 l_location :=null;
2183 l_csz :=null;
2184 l_country :=null;
2185 l_return_status :=null;
2186 fte_mls_util.get_location_info(
2187 p_location_id => get_pickup_info_rec.stop_location_id,
2188 x_location => l_location,
2189 x_csz => l_csz,
2190 x_country => l_country,
2191 x_return_status => l_return_status);
2192
2193 x_PUStopId := get_pickup_info_rec.stop_id;
2194 x_PUStopLocationId := get_pickup_info_rec.stop_location_id;
2195 x_PUStopLocation := l_location;
2196 x_PUStopCSZ := l_csz;
2197 x_PUStopCountry := l_country;
2198 x_PUStopActualArrivalDate := get_pickup_info_rec.actual_arrival_date;
2199 x_PUStopActualDepartureDate := get_pickup_info_rec.actual_departure_date;
2200 x_PUStopPlannedArrivalDate := get_pickup_info_rec.planned_arrival_date;
2201 x_PUStopPlannedDepartureDate := get_pickup_info_rec.planned_departure_date;
2202 x_PUStopSequenceNumber := get_pickup_info_rec.stop_sequence_number;
2203 x_PUStopStatusCode := get_pickup_info_rec.status_code;
2204 x_PUStopTripId := get_pickup_info_rec.trip_id;
2205 x_CarrierId := get_pickup_info_rec.carrier_id;
2206 x_LaneId := get_pickup_info_rec.lane_id;
2207 x_ScheduleId := get_pickup_info_rec.schedule_id;
2208 x_ModeOfTransport := get_pickup_info_rec.mode_of_transport;
2209 x_ServiceLevel := get_pickup_info_rec.service_level;
2210 x_ShipMethodCode := get_pickup_info_rec.ship_method_code;
2211 x_TripSegmentId := get_pickup_info_rec.trip_id;
2212 x_TripSegmentName := get_pickup_info_rec.name;
2213 x_TripSegmentStatusCode := get_pickup_info_rec.trip_status;
2214 x_TenderStatus := get_pickup_info_rec.load_tender_status;
2215 x_TripPlannedFlag := get_pickup_info_rec.planned_flag;
2216 x_TripShipmentsTypeFlag := get_pickup_info_rec.shipments_type_flag;
2217 x_VehicleOrgId := get_pickup_info_rec.vehicle_organization_id;
2218 x_VehicleItemId := get_pickup_info_rec.vehicle_item_id;
2219 x_ParentDLegId := get_pickup_info_rec.parent_delivery_leg_id;
2220 x_RankId := get_pickup_info_rec.rank_id;
2221 x_RoutingRuleId := get_pickup_info_rec.routing_rule_id;
2222 x_AppendFlag := get_pickup_info_rec.append_flag;
2223
2224 --}
2225 END LOOP;
2226 -- END OF PICKUP STOP INFORMATION
2227 --
2228 IF l_debug_on THEN
2229 WSH_DEBUG_SV.logmsg(l_module_name,
2230 ' Exiting out of dleg info 2 ',
2231 WSH_DEBUG_SV.C_PROC_LEVEL);
2232 END IF;
2233
2234
2235 --
2236 IF get_pickup_info_cur%ISOPEN THEN
2237 CLOSE get_pickup_info_cur;
2238 END IF;
2239 --
2240 --
2241 -- DROPOFF STOP INFORMATION
2242 x_DOStopId := NULL;
2243 x_DOStopLocationId := NULL;
2244 x_DOStopLocation := NULL;
2245 x_DOStopCSZ := NULL;
2246 x_DOStopCountry := NULL;
2247 x_DOStopActualArrivalDate := NULL;
2248 x_DOStopActualDepartureDate := NULL;
2249 x_DOStopPlannedArrivalDate := NULL;
2250 x_DOStopPlannedDepartureDate := NULL;
2251 x_DOStopSequenceNumber := NULL;
2252 x_DOStopStatusCode := NULL;
2253 x_DOStopTripId := NULL;
2254 x_DOStopPhysLocationId := NULL;
2255 --
2256 --
2257 --
2258 -- Modified for 11.5.10+ Locations: Check if the stop has a physical location id.
2259 -- If yes, use physical_location_id; if no, use stop_location_id.
2260 --
2261 FOR get_dropoff_info_rec IN get_dropoff_info_cur(p_dLeg_id)
2262 LOOP
2263 --{
2264 l_location_id :=null;
2268 l_return_status :=null;
2265 l_location :=null;
2266 l_csz :=null;
2267 l_country :=null;
2269
2270 IF (get_dropoff_info_rec.physical_location_id IS NULL)
2271 THEN
2272 l_location_id := get_dropoff_info_rec.stop_location_id;
2273 ELSE
2274 l_location_id := get_dropoff_info_rec.physical_location_id;
2275 END IF;
2276
2277 fte_mls_util.get_location_info(
2278 p_location_id => l_location_id,
2279 x_location => l_location,
2280 x_csz => l_csz,
2281 x_country => l_country,
2282 x_return_status => l_return_status);
2283
2284 x_DOStopId := get_dropoff_info_rec.stop_id;
2285 x_DOStopLocationId := get_dropoff_info_rec.stop_location_id;
2286 x_DOStopLocation := l_location;
2287 x_DOStopCSZ := l_csz;
2288 x_DOStopCountry := l_country;
2289 x_DOStopActualArrivalDate := get_dropoff_info_rec.actual_arrival_date;
2290 x_DOStopActualDepartureDate := get_dropoff_info_rec.actual_departure_date;
2291 x_DOStopPlannedArrivalDate := get_dropoff_info_rec.planned_arrival_date;
2292 x_DOStopPlannedDepartureDate := get_dropoff_info_rec.planned_departure_date;
2293 x_DOStopSequenceNumber := get_dropoff_info_rec.stop_sequence_number;
2294 x_DOStopStatusCode := get_dropoff_info_rec.status_code;
2295 x_DOStopTripId := get_dropoff_info_rec.trip_id;
2296 x_DOStopPhysLocationId := get_dropoff_info_rec.physical_location_id;
2297 --}
2298 END LOOP;
2299 -- END OF DROPOFF STOP INFORMATION
2300 --
2301 IF l_debug_on THEN
2302 WSH_DEBUG_SV.logmsg(l_module_name,
2303 ' Exiting out of dleg info 3 ',
2304 WSH_DEBUG_SV.C_PROC_LEVEL);
2305 END IF;
2306
2307 --
2308 IF get_dropoff_info_cur%ISOPEN THEN
2309 CLOSE get_dropoff_info_cur;
2310 END IF;
2311 --
2312 --
2313 -- PRICE INFORMATION
2314 x_Price := NULL;
2315 x_Currency := NULL;
2316 --
2317 --
2318 FOR get_price_info_rec IN get_price_info_cur(p_dLeg_id)
2319 LOOP
2320 --{
2321 x_price := get_price_info_rec.total_amount;
2322 x_currency := get_price_info_rec.currency_code;
2323 --}
2324 END LOOP;
2325 -- END OF PRICE INFORMATION
2326 --
2327 --
2328 IF get_price_info_cur%ISOPEN THEN
2329 CLOSE get_price_info_cur;
2330 END IF;
2331 --
2332 --
2333 -- Trip Segment Origin information
2334 --
2335 x_OriginStopId :=NULL;
2336 x_OriginStopStatusCode :=NULL;
2337 x_OriginStopSequenceNumber :=NULL;
2338 x_OriginStopLocationId :=NULL;
2339 x_OriginLocation :=NULL;
2340 x_OriginCSZ :=NULL;
2341 x_OriginDepartureDate :=NULL;
2342 x_OriginArrivalDate :=NULL;
2343 --
2344 IF l_debug_on THEN
2345 WSH_DEBUG_SV.logmsg(l_module_name,
2346 ' Exiting out of dleg info 4 ',
2347 WSH_DEBUG_SV.C_PROC_LEVEL);
2348 END IF;
2349
2350 --
2351 FOR get_trip_seg_origin_rec IN get_trip_seg_origin_cur(x_TripSegmentId)
2352 LOOP
2353 --{
2354 l_location :=null;
2355 l_csz :=null;
2356 l_country :=null;
2357 l_return_status :=null;
2358
2359 fte_mls_util.get_location_info(
2360 p_location_id => get_trip_seg_origin_rec.stop_location_id,
2361 x_location => l_location,
2362 x_csz => l_csz,
2363 x_country => l_country,
2364 x_return_status => l_return_status);
2365
2366
2367 x_OriginStopId := get_trip_seg_origin_rec.stop_id;
2368 x_OriginStopStatusCode := get_trip_seg_origin_rec.status_code;
2369 x_OriginStopSequenceNumber := get_trip_seg_origin_rec.stop_sequence_number;
2370 x_OriginStopLocationId := get_trip_seg_origin_rec.stop_location_id;
2371 x_OriginLocation := l_location;
2372 x_OriginCSZ := l_csz;
2373 x_OriginCountry := l_country;
2374 x_OriginDepartureDate := get_trip_seg_origin_rec.planned_departure_date;
2375 x_OriginArrivalDate := get_trip_seg_origin_rec.planned_arrival_date;
2376 --}
2377 END LOOP;
2378 -- END OF Trip Segment Origin INFORMATION
2379 --
2380 IF l_debug_on THEN
2381 WSH_DEBUG_SV.logmsg(l_module_name,
2382 ' Exiting out of dleg info 5 ',
2383 WSH_DEBUG_SV.C_PROC_LEVEL);
2384 END IF;
2385
2386 --
2387 -- Trip Segment Dest information
2388 --
2389 x_DestStopId :=NULL;
2390 x_DestStopStatusCode :=NULL;
2391 x_DestStopSequenceNumber :=NULL;
2392 x_DestStopLocationId :=NULL;
2393 x_DestLocation :=NULL;
2394 x_DestCSZ :=NULL;
2395 x_DestDepartureDate :=NULL;
2396 x_DestArrivalDate :=NULL;
2397 x_DestStopPhysLocationId :=NULL;
2398 x_BolNumber :=NULL;
2399 --
2400 --
2401 FOR get_trip_seg_dest_rec IN get_trip_seg_dest_cur(x_TripSegmentId)
2402 LOOP
2403 --{
2404 l_location_id :=null;
2405 l_location :=null;
2406 l_csz :=null;
2407 l_country :=null;
2408 l_return_status :=null;
2409
2410 IF (get_trip_seg_dest_rec.physical_location_id IS NULL)
2411 THEN
2412 l_location_id := get_trip_seg_dest_rec.stop_location_id;
2413 ELSE
2414 l_location_id := get_trip_seg_dest_rec.physical_location_id;
2415 END IF;
2416
2417
2418 fte_mls_util.get_location_info(
2419 p_location_id => l_location_id,
2420 x_location => l_location,
2421 x_csz => l_csz,
2422 x_country => l_country,
2423 x_return_status => l_return_status);
2424
2425
2426 x_DestStopId := get_trip_seg_dest_rec.stop_id;
2427 x_DestStopStatusCode := get_trip_seg_dest_rec.status_code;
2428 x_DestStopSequenceNumber := get_trip_seg_dest_rec.stop_sequence_number;
2429 x_DestStopLocationId := get_trip_seg_dest_rec.stop_location_id;
2430 x_DestLocation := l_location;
2431 x_DestCSZ := l_csz;
2432 x_DestCountry := l_country;
2433 x_DestDepartureDate := get_trip_seg_dest_rec.planned_departure_date;
2434 x_DestArrivalDate := get_trip_seg_dest_rec.planned_arrival_date;
2435 x_DestStopPhysLocationId := get_trip_seg_dest_rec.physical_location_id;
2436 --}
2437 END LOOP;
2438 -- END OF Trip Segment Dest INFORMATION
2439 --
2440 IF l_debug_on THEN
2441 WSH_DEBUG_SV.logmsg(l_module_name,
2442 ' Exiting out of dleg info 6 ',
2443 WSH_DEBUG_SV.C_PROC_LEVEL);
2444 END IF;
2445
2446 --
2447 IF get_trip_seg_dest_cur%ISOPEN THEN
2448 CLOSE get_trip_seg_dest_cur;
2449 END IF;
2450 --
2451 --
2452 --GET CARRIER INFO
2453 --
2454 IF (x_CarrierId IS NOT NULL)
2455 THEN
2456 x_CarrierName := fte_mls_util.get_carrier_name(x_CarrierId);
2457 END IF;
2458 --
2459 -- END OF GET CARRIER NAME
2460 --
2461
2462 --
2463 --GET MODE OF TRANSPORT CODE
2464 --
2465 IF (x_ModeOfTransport IS NOT NULL)
2466 THEN
2467 FOR get_mode_of_transport_rec IN get_mode_of_transport_cur(x_ModeOfTransport)
2468 LOOP
2469 --{
2470 x_ModeOfTransportMeaning := get_mode_of_transport_rec.mode_of_transport_meaning;
2471 --}
2472 END LOOP;
2473 -- END OF MODE OF TRANSPORT
2474 END IF;
2475 --
2476 -- END OF MODE OF TRANSPORT
2477 --
2478 --
2479 -- GET SERVICE LEVEL
2480 --
2481 IF (x_ServiceLevel IS NOT NULL)
2482 THEN
2483 FOR get_service_level_rec IN get_service_level_cur(x_ServiceLevel)
2484 LOOP
2485 --{
2486 x_ServiceLevelMeaning := get_service_level_rec.service_type_meaning;
2487 --}
2488 END LOOP;
2489 -- END OF SERVICE LEVEL
2490 END IF;
2491 --
2492 -- END OF SERVICE LEVEL
2493 --
2494 IF get_service_level_cur%ISOPEN THEN
2495 CLOSE get_service_level_cur;
2496 END IF;
2497 --
2498 IF (x_LaneId IS NOT NULL)
2499 THEN
2500 FOR get_lane_rec IN get_lane_cur(x_LaneId)
2501 LOOP
2502 --{
2503 x_LaneNumber := get_lane_rec.lane_number;
2504 --}
2505 END LOOP;
2506 -- END OF LANE INFO
2507 END IF;
2508 --
2509 -- END OF LANE_INFO
2510 --
2511 IF get_lane_cur%ISOPEN THEN
2512 CLOSE get_lane_cur;
2513 END IF;
2514 --
2515 --
2516 IF (p_dLeg_Id IS NOT NULL)
2517 THEN
2518 FOR get_bol_number_rec IN get_bol_number_cur(p_dLeg_Id)
2519 LOOP
2520 --{
2521 x_BolNumber := get_bol_number_rec.sequence_number;
2522 --}
2523 END LOOP;
2524 -- END OF BOL NUMBER INFO
2525 END IF;
2526 --
2527 -- END OF BOL NUMBER INFO
2528 --
2529 IF get_bol_number_cur%ISOPEN THEN
2530 CLOSE get_bol_number_cur;
2531 END IF;
2532 --
2533 --
2534 IF (x_ParentDLegId IS NOT NULL)
2535 THEN
2536 FOR get_parent_dlvy_info_rec IN get_parent_dlvy_info_cur(x_ParentDLegId)
2537 LOOP
2538 --{
2539 x_ParentDlvyName := get_parent_dlvy_info_rec.name;
2540 --}
2541 END LOOP;
2542 -- END OF PARENT DLVY INFO
2543 END IF;
2544 --
2545 -- END OF PARENT DLVY INFO
2546 --
2547 IF get_parent_dlvy_info_cur%ISOPEN THEN
2548 CLOSE get_parent_dlvy_info_cur;
2549 END IF;
2550 --
2551 FND_MSG_PUB.Count_And_Get
2552 (
2553 p_count => x_msg_count,
2554 p_data => x_msg_data,
2555 p_encoded => FND_API.G_FALSE
2556 );
2557
2558
2559 IF l_debug_on THEN
2560 WSH_DEBUG_SV.logmsg(l_module_name,
2561 ' Before existing ' || x_return_status,
2562 WSH_DEBUG_SV.C_PROC_LEVEL);
2563 END IF;
2564
2565
2566 IF l_debug_on THEN
2567 WSH_DEBUG_SV.pop(l_module_name);
2568 END IF;
2569
2570
2571 --}
2572 EXCEPTION
2573 --{
2574 WHEN OTHERS THEN
2575 wsh_util_core.default_handler('FTE_DELIVERY_LEGS.build_delivery_leg_info');
2576 FND_MESSAGE.SET_NAME('FTE','FTE_BLD_DLEG_UNEXP_ERROR');
2577 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR);
2578 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2579 FND_MSG_PUB.Count_And_Get
2580 (
2581 p_count => x_msg_count,
2582 p_data => x_msg_data,
2583 p_encoded => FND_API.G_FALSE
2584 );
2585
2586 IF l_debug_on THEN
2587 WSH_DEBUG_SV.logmsg(l_module_name,
2588 ' In Exception ' || x_return_status,
2589 WSH_DEBUG_SV.C_PROC_LEVEL);
2590 WSH_DEBUG_SV.logmsg(l_module_name,
2591 ' In Exception ' || SQLERRM,WSH_DEBUG_SV.C_PROC_LEVEL);
2592 END IF;
2593
2594 IF l_debug_on THEN
2595 WSH_DEBUG_SV.pop(l_module_name);
2596 END IF;
2597 --}
2598 END build_delivery_leg_info;
2599 --}
2600 END FTE_DELIVERY_LEGS_PVT;