[Home] [Help]
PACKAGE BODY: APPS.WSH_FTE_INTEGRATION
Source
1 PACKAGE BODY WSH_FTE_INTEGRATION as
2 /* $Header: WSHFTEIB.pls 120.8.12000000.2 2007/02/15 00:54:23 parkhj ship $ */
3
4 --
5 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_FTE_INTEGRATION';
6 --
7
8 PROCEDURE Rate_Delivery (
9 p_api_version IN NUMBER DEFAULT 1.0,
10 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
11 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
12 p_in_param_rec IN rate_del_in_param_rec,
13 x_out_param_rec OUT NOCOPY rate_del_out_param_rec,
14 x_return_status OUT NOCOPY VARCHAR2,
15 x_msg_count OUT NOCOPY NUMBER,
16 x_msg_data OUT NOCOPY VARCHAR2)
17 IS
18
19 l_return_status VARCHAR2(1);
20 l_msg_count NUMBER := 0;
21 l_msg_data VARCHAR2(2000);
22
23 l_in_param_rec FTE_FREIGHT_RATING_DLVY_GRP.rate_del_in_param_rec;
24 l_out_param_rec FTE_FREIGHT_RATING_DLVY_GRP.rate_del_out_param_rec;
25 --
26 l_debug_on BOOLEAN;
27
28 --
29 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'RATE_DELIVERY';
30 --
31 BEGIN
32 --
33 --
34 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
35 --
36 IF l_debug_on IS NULL
37 THEN
38 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
39 END IF;
40 --
41 IF l_debug_on THEN
42 WSH_DEBUG_SV.push(l_module_name);
43 --
44 WSH_DEBUG_SV.log(l_module_name,'P_API_VERION',p_api_version);
45 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',p_init_msg_list);
46 WSH_DEBUG_SV.log(l_module_name,'P_COMMIT',p_commit);
47 END IF;
48 --
49 x_return_status := FND_API.G_RET_STS_SUCCESS;
50 SAVEPOINT WSH_Rate_Delivery;
51
52 IF WSH_UTIL_CORE.FTE_Is_Installed = 'Y' AND
53 ((p_in_param_rec.delivery_id_list.COUNT > 0) AND p_in_param_rec.action is NOT null) THEN
54
55 --
56 IF l_debug_on THEN
57 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit FTE_FREIGHT_RATING_DLVY_GRP.RATE_DELIVERY',WSH_DEBUG_SV.C_PROC_LEVEL);
58 END IF;
59 --
60
61 l_in_param_rec.delivery_id_list := p_in_param_rec.delivery_id_list;
62 l_in_param_rec.action := p_in_param_rec.action;
63 l_in_param_rec.seq_tender_flag := p_in_param_rec.seq_tender_flag;
64
65 FTE_FREIGHT_RATING_DLVY_GRP.Rate_Delivery (
66 p_api_version => p_api_version,
67 p_init_msg_list => p_init_msg_list,
68 p_commit => p_commit,
69 p_in_param_rec => l_in_param_rec,
70 x_out_param_rec => l_out_param_rec,
71 x_return_status => l_return_status,
72 x_msg_count => l_msg_count,
73 x_msg_data => l_msg_data);
74
75 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
76 x_return_status := l_return_status;
77 END IF;
78 x_msg_count := l_msg_count;
79 x_msg_data := l_msg_data;
80 x_out_param_rec.failed_delivery_id_list := l_out_param_rec.failed_delivery_id_list;
81 END IF;
82 --
83 IF l_debug_on THEN
84 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
85 WSH_DEBUG_SV.pop(l_module_name);
86 END IF;
87 --
88 EXCEPTION
89 WHEN others THEN
90 ROLLBACK TO WSH_Rate_Delivery;
91 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
92 wsh_util_core.default_handler('WSH_FTE_INTEGRATION.Rate_Delivery',l_module_name);
93 --
94 IF l_debug_on THEN
95 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
96 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
97 END IF;
98 --
99 END Rate_Delivery;
100
101 -- WSH get_rate_from_FTE demo flow (multiple deliveries)
102 PROCEDURE Cancel_Service (
103 p_api_version IN NUMBER DEFAULT 1.0,
104 p_init_msg_list VARCHAR2 DEFAULT FND_API.G_FALSE,
105 p_delivery_list IN WSH_UTIL_CORE.id_tab_type,
106 p_action IN VARCHAR2 DEFAULT 'CANCEL',
107 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
108 x_return_status OUT NOCOPY VARCHAR2,
109 x_msg_count OUT NOCOPY NUMBER,
110 x_msg_data OUT NOCOPY VARCHAR2)
111 IS
112
113 l_return_status VARCHAR2(1);
114 l_msg_count NUMBER := 0;
115 l_msg_data VARCHAR2(2000);
116
117 --
118 l_debug_on BOOLEAN;
119
120 --
121 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CANCEL_SERVICE';
122 --
123 BEGIN
124 --
125 --
126 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
127 --
128 IF l_debug_on IS NULL
129 THEN
130 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
131 END IF;
132 --
133 IF l_debug_on THEN
134 WSH_DEBUG_SV.push(l_module_name);
135 --
136 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_LIST.COUNT',p_delivery_list.COUNT);
137 WSH_DEBUG_SV.log(l_module_name,'P_ACTION',p_action);
138 WSH_DEBUG_SV.log(l_module_name,'P_API_VERION',p_api_version);
139 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',p_init_msg_list);
140 WSH_DEBUG_SV.log(l_module_name,'P_COMMIT',p_commit);
141 END IF;
142 --
143 x_return_status := FND_API.G_RET_STS_SUCCESS;
144 SAVEPOINT WSH_Cancel_Service;
145
146 IF WSH_UTIL_CORE.FTE_Is_Installed = 'Y' AND
147 (p_delivery_list.COUNT > 0 AND p_action is NOT null) THEN
148
149 --
150 IF l_debug_on THEN
151 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit FTE_FREIGHT_RATING_DLVY_GRP.CANCEL_SERVICE',WSH_DEBUG_SV.C_PROC_LEVEL);
152 END IF;
153 --
154
155 FTE_FREIGHT_RATING_DLVY_GRP.Cancel_Service (
156 p_api_version => p_api_version,
157 p_init_msg_list => p_init_msg_list,
158 p_delivery_list => p_delivery_list,
159 p_action => p_action,
160 p_commit => p_commit,
161 x_return_status => l_return_status,
162 x_msg_count => l_msg_count,
163 x_msg_data => l_msg_data);
164
165 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
166 x_return_status := l_return_status;
167 END IF;
168 x_msg_count := l_msg_count;
169 x_msg_data := l_msg_data;
170 END IF;
171 --
172 IF l_debug_on THEN
173 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
174 WSH_DEBUG_SV.pop(l_module_name);
175 END IF;
176 --
177 EXCEPTION
178 WHEN others THEN
179 ROLLBACK TO WSH_Cancel_Service;
180 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
181 wsh_util_core.default_handler('WSH_FTE_INTEGRATION.Cancel_Service',l_module_name);
182 --
183 IF l_debug_on THEN
184 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
185 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
186 END IF;
187 --
188 END Cancel_Service;
189
190 PROCEDURE Cancel_Service (
191 p_api_version IN NUMBER DEFAULT 1.0,
192 p_init_msg_list VARCHAR2 DEFAULT FND_API.G_FALSE,
193 p_delivery_id IN NUMBER,
194 p_action IN VARCHAR2 DEFAULT 'CANCEL',
195 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
196 x_return_status OUT NOCOPY VARCHAR2,
197 x_msg_count OUT NOCOPY NUMBER,
198 x_msg_data OUT NOCOPY VARCHAR2)
199 IS
200
201 l_return_status VARCHAR2(1);
202 l_msg_count NUMBER := 0;
203 l_msg_data VARCHAR2(2000);
204
205 --
206 l_debug_on BOOLEAN;
207
208 --
209 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CANCEL_SERVICE';
210 --
211 BEGIN
212 --
213 --
214 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
215 --
216 IF l_debug_on IS NULL
217 THEN
218 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
219 END IF;
220 --
221 IF l_debug_on THEN
222 WSH_DEBUG_SV.push(l_module_name);
223 --
224 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_ID',p_delivery_id);
225 WSH_DEBUG_SV.log(l_module_name,'P_ACTION',p_action);
226 WSH_DEBUG_SV.log(l_module_name,'P_API_VERION',p_api_version);
227 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',p_init_msg_list);
228 WSH_DEBUG_SV.log(l_module_name,'P_COMMIT',p_commit);
229 END IF;
230 --
231 x_return_status := FND_API.G_RET_STS_SUCCESS;
232 SAVEPOINT WSH_Cancel_Service_2;
233
234 IF WSH_UTIL_CORE.FTE_Is_Installed = 'Y' AND
235 (p_delivery_id is NOT null AND p_action is NOT null) THEN
236
237 --
238 IF l_debug_on THEN
239 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit FTE_FREIGHT_RATING_DLVY_GRP.CANCEL_SERVICE',WSH_DEBUG_SV.C_PROC_LEVEL);
240 END IF;
241 --
242
243 FTE_FREIGHT_RATING_DLVY_GRP.Cancel_Service (
244 p_api_version => p_api_version,
245 p_init_msg_list => p_init_msg_list,
246 p_delivery_id => p_delivery_id,
247 p_action => p_action,
248 p_commit => p_commit,
249 x_return_status => l_return_status,
250 x_msg_count => l_msg_count,
251 x_msg_data => l_msg_data);
252
253 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
254 x_return_status := l_return_status;
255 END IF;
256 x_msg_count := l_msg_count;
257 x_msg_data := l_msg_data;
258 END IF;
259 --
260 IF l_debug_on THEN
261 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
262 WSH_DEBUG_SV.pop(l_module_name);
263 END IF;
264 --
265 EXCEPTION
266 WHEN others THEN
267 ROLLBACK TO WSH_Cancel_Service_2;
268 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
269 wsh_util_core.default_handler('WSH_FTE_INTEGRATION.Cancel_Service',l_module_name);
270 --
271 IF l_debug_on THEN
272 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
273 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
274 END IF;
275 --
276 END Cancel_Service;
277
278 PROCEDURE Shipment_Price_Consolidate (
279 p_delivery_leg_id IN NUMBER DEFAULT NULL,
280 p_trip_id IN NUMBER DEFAULT NULL,
281 x_return_status OUT NOCOPY VARCHAR2 ) IS
282 l_return_status VARCHAR2(1);
283 l_in_attributes FTE_FREIGHT_PRICING.FtePricingInRecType;
284 l_msg_count NUMBER := 0;
285 l_msg_data VARCHAR2(2000);
286
287 --
288 l_debug_on BOOLEAN;
289
290
291 --
292 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'SHIPMENT_PRICE_CONSOLIDATE';
293 --
294 BEGIN
295 --
296 --
297 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
298 --
299 IF l_debug_on IS NULL
300 THEN
301 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
302 END IF;
303 --
304 IF l_debug_on THEN
305 WSH_DEBUG_SV.push(l_module_name);
306 --
307 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_LEG_ID',P_DELIVERY_LEG_ID);
308 WSH_DEBUG_SV.log(l_module_name,'P_TRIP_ID',P_TRIP_ID);
309 END IF;
310 --
311 x_return_status := FND_API.G_RET_STS_SUCCESS;
312 SAVEPOINT before_changes;
313
314 IF WSH_UTIL_CORE.FTE_Is_Installed = 'Y' AND
315 (p_delivery_leg_id is NOT null OR p_trip_id is NOT null) THEN
316
317 l_in_attributes.api_version_number := 1.0;
318 l_in_attributes.delivery_leg_id := p_delivery_leg_id;
319 l_in_attributes.segment_id := p_trip_id;
320 --
321 IF l_debug_on THEN
322 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit FTE_FREIGHT_PRICING.SHIPMENT_PRICE_CONSOLIDATE',WSH_DEBUG_SV.C_PROC_LEVEL);
323 END IF;
324 --
325 FTE_FREIGHT_PRICING.shipment_price_consolidate (
326 p_init_msg_list => fnd_api.g_false,
327 p_in_attributes => l_in_attributes,
328 x_return_status => l_return_status,
329 x_msg_count => l_msg_count,
330 x_msg_data => l_msg_data );
331 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
332 x_return_status := l_return_status;
333 END IF;
334 END IF;
335 --
336 IF l_debug_on THEN
337 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
338 WSH_DEBUG_SV.pop(l_module_name);
339 END IF;
340 --
341 EXCEPTION
342 WHEN others THEN
343 ROLLBACK TO before_changes;
344 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
345 wsh_util_core.default_handler('WSH_FTE_INTEGRATION.Shipment_Price_Consolidate',l_module_name);
346 --
347 IF l_debug_on THEN
348 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
349 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
350 END IF;
351 --
352 END Shipment_Price_Consolidate;
353
354 /*
355 Parameters :
356 p_stop_rec - record to be inserted/updated/deleted from wsh_trip_stops
357 p_trip_rec - record to be inserted/updated/deleted from wsh_trips
358 p_action - Action called
359 Values of p_action are
360 ADD
361 UPDATE
362 DELETE
363 TRIP_SEGMENT_DELETE
364
365 */
366
367 PROCEDURE trip_stop_validations
368 ( p_stop_rec IN WSH_TRIP_STOPS_PVT.trip_stop_rec_type,
369 p_trip_rec IN WSH_TRIPS_PVT.trip_rec_type,
370 p_action IN VARCHAR2,
371 x_return_status OUT NOCOPY VARCHAR2
372 ) IS
373
374 l_stop_rec_old WSH_TRIP_STOPS_PVT.trip_stop_rec_type;
375 l_stop_rec_new WSH_TRIP_STOPS_PVT.trip_stop_rec_type;
376 l_stop_rec_null WSH_TRIP_STOPS_PVT.trip_stop_rec_type;
377
378 l_trip_rec_old WSH_TRIPS_PVT.trip_rec_type;
379 l_trip_rec_new WSH_TRIPS_PVT.trip_rec_type;
380 l_trip_rec_null WSH_TRIPS_PVT.trip_rec_type;
381 l_stops_trip_rec WSH_TRIPS_PVT.trip_rec_type;
382
383 l_stop_seg_IN FTE_WSH_INTERFACE_PKG.segmentStopChangeInRecType;
384 l_stop_seg_OUT FTE_WSH_INTERFACE_PKG.segmentStopChangeOutRecType;
385 l_trip_seg_IN FTE_WSH_INTERFACE_PKG.tripSegmentChangeInRecType;
386 l_trip_seg_OUT FTE_WSH_INTERFACE_PKG.tripSegmentChangeOutRecType;
387
388
389 l_msg_count NUMBER;
390 l_msg_data VARCHAR2(4000);
391 l_return_status VARCHAR2(30):= WSH_UTIL_CORE.G_RET_STS_SUCCESS;
392
393
394 --
395 l_debug_on BOOLEAN;
396 --
397 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'TRIP_STOP_VALIDATIONS';
398 --
399 BEGIN
400
401 /* Input is of type WSH_TRIPS_PVT or WSH_TRIP_STOPS_PVT api */
402 /* l_stop_rec and l_trip_rec are of type as per WSH_TRIPS_GRP api */
403 --
404 --
405 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
406 --
407 IF l_debug_on IS NULL
408 THEN
409 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
410 END IF;
411 --
412 IF l_debug_on THEN
413 WSH_DEBUG_SV.push(l_module_name);
414 --
415 WSH_DEBUG_SV.log(l_module_name,'P_ACTION',P_ACTION);
416 END IF;
417 --
418 l_stop_rec_old := l_stop_rec_null;
419 l_stop_rec_new := l_stop_rec_null;
420
421 l_trip_rec_old := l_trip_rec_null;
422 l_trip_rec_new := l_trip_rec_null;
423 l_stops_trip_rec := l_trip_rec_null;
424 /* NEW MESSAGE here */
425 IF (WSH_UTIL_CORE.FTE_IS_INSTALLED <> 'Y') THEN
426 FND_MESSAGE.SET_NAME('WSH','FTE NOT INSTALLED');
427 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
428 --
429 IF l_debug_on THEN
430 WSH_DEBUG_SV.log(l_module_name,'FTE NOT INSTALLE');
431 WSH_DEBUG_SV.pop(l_module_name);
432 END IF;
433 --
434 RETURN;
435 END IF;
436
437
438 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
439
440 --1. Creating Stop
441 IF (p_action = 'ADD'
442 AND (p_stop_rec.stop_location_id <> FND_API.G_MISS_NUM)
443 ) THEN
444 -- Group API Call
445 l_stop_seg_IN.action_type := p_action;
446 IF (p_stop_rec.stop_location_id <> FND_API.G_MISS_NUM
447 AND p_stop_rec.stop_location_id IS NOT NULL
448 ) THEN
449
450 -- old record
451 -- New values are passed, so map them into l_stop_rec_new
452 l_stop_rec_new := p_stop_rec;
453 -- Get trip details into l_stops_trip_rec FOR GROUP
454 --
455 IF l_debug_on THEN
456 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_GRP.GET_TRIP_DETAILS_PVT',WSH_DEBUG_SV.C_PROC_LEVEL);
457 END IF;
458 --
459 wsh_trips_grp.get_trip_details_pvt(p_trip_id => p_stop_rec.trip_id,
460 x_trip_rec => l_stops_trip_rec,
461 x_return_status => l_return_status);
462
463 --
464 IF l_debug_on THEN
465 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit FTE_WSH_INTERFACE_PKG.SEGMENT_STOP_CHANGE',WSH_DEBUG_SV.C_PROC_LEVEL);
466 END IF;
467 --
468 FTE_WSH_INTERFACE_PKG.segment_stop_change
469 ( p_api_version => 1.0,
470 p_init_msg_list => FND_API.G_FALSE,
471 p_commit => FND_API.G_FALSE,
472 x_return_status => l_return_status,
473 x_msg_count => l_msg_count,
474 x_msg_data => l_msg_data,
475 p_trip_segment_rec => l_stops_trip_rec,
476 p_old_segment_stop_rec => l_stop_rec_null,
477 p_new_segment_stop_rec => l_stop_rec_new,
478 p_segmentStopChangeInRec => l_stop_seg_IN,
479 p_segmentStopChangeOutRec => l_stop_seg_OUT
480 );
481 IF l_debug_on THEN
482 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
483 END IF;
484 END IF;
485
486 --2. Updating Stop
487 ELSIF (p_action = 'UPDATE'
488 AND (p_stop_rec.stop_location_id <> FND_API.G_MISS_NUM)
489 ) THEN
490
491 l_stop_seg_IN.action_type := p_action;
492 -- Group API Call
493 IF (p_stop_rec.stop_location_id <> FND_API.G_MISS_NUM
494 AND p_stop_rec.stop_location_id IS NOT NULL
495 )THEN
496
497 -- Get details of existing stop from database into l_stop_rec_old
498 wsh_trip_stops_grp.get_stop_details_pvt(
499 p_stop_id => p_stop_rec.stop_id,
500 x_stop_rec => l_stop_rec_old,
501 x_return_status => l_return_status
502 );
503 l_stop_rec_new := p_stop_rec;
504
505 -- Get trip details into l_stops_trip_rec FOR GROUP
506 wsh_trips_grp.get_trip_details_pvt(p_trip_id => p_stop_rec.trip_id,
507 x_trip_rec => l_stops_trip_rec,
508 x_return_status => l_return_status);
509
510 --
511 IF l_debug_on THEN
512 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit FTE_WSH_INTERFACE_PKG.SEGMENT_STOP_CHANGE',WSH_DEBUG_SV.C_PROC_LEVEL);
513 END IF;
514 --
515 FTE_WSH_INTERFACE_PKG.segment_stop_change
516 ( p_api_version => 1.0,
517 p_init_msg_list => FND_API.G_FALSE,
518 p_commit => FND_API.G_FALSE,
519 x_return_status => l_return_status,
520 x_msg_count => l_msg_count,
521 x_msg_data => l_msg_data,
522 p_trip_segment_rec => l_stops_trip_rec,
523 p_old_segment_stop_rec => l_stop_rec_old,
524 p_new_segment_stop_rec => l_stop_rec_new,
525 p_segmentStopChangeInRec => l_stop_seg_IN,
526 p_segmentStopChangeOutRec => l_stop_seg_OUT
527 );
528 IF l_debug_on THEN
529 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
530 END IF;
531 END IF;
532 --3. Deleting Stop
533 ELSIF (p_action = 'DELETE'
534 AND (p_stop_rec.stop_location_id <> FND_API.G_MISS_NUM)
535 ) THEN
536
537 l_stop_seg_IN.action_type := p_action;
538 -- Group API Call
539 IF (p_stop_rec.stop_location_id <> FND_API.G_MISS_NUM
540 AND p_stop_rec.stop_location_id IS NOT NULL
541 ) THEN
542 l_stop_rec_old := p_stop_rec;
543 -- Get trip details into l_stops_trip_rec FOR GROUP
544 --
545 wsh_trips_grp.get_trip_details_pvt(
546 p_trip_id => p_stop_rec.trip_id,
547 x_trip_rec => l_stops_trip_rec,
548 x_return_status => l_return_status);
549 IF l_debug_on THEN
550 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit FTE_WSH_INTERFACE_PKG.SEGMENT_STOP_CHANGE',WSH_DEBUG_SV.C_PROC_LEVEL);
551 END IF;
552 --
553 FTE_WSH_INTERFACE_PKG.segment_stop_change
554 ( p_api_version => 1.0,
555 p_init_msg_list => FND_API.G_FALSE,
556 p_commit => FND_API.G_FALSE,
557 x_return_status => l_return_status,
558 x_msg_count => l_msg_count,
559 x_msg_data => l_msg_data,
560 p_trip_segment_rec => l_stops_trip_rec,
561 p_old_segment_stop_rec => l_stop_rec_old,
562 p_new_segment_stop_rec => l_stop_rec_null,
563 p_segmentStopChangeInRec => l_stop_seg_IN,
564 p_segmentStopChangeOutRec => l_stop_seg_OUT
565 );
566 IF l_debug_on THEN
567 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
568 END IF;
569 END IF;
570 --4. Updating Trip
571 ELSIF (p_action = 'UPDATE'
572 AND (p_trip_rec.trip_id <> FND_API.G_MISS_NUM)
573 ) THEN
574
575 l_trip_seg_IN.action_type := p_action;
576
577 IF (p_trip_rec.trip_id <> FND_API.G_MISS_NUM
578 AND p_trip_rec.trip_id IS NOT NULL
579 )THEN
580
581 -- Get the details of the trip as in the database in l_trip_rec_old
582 -- Get trip details into l_stops_trip_rec FOR GROUP
583 wsh_trips_grp.get_trip_details_pvt(
584 p_trip_id => p_trip_rec.trip_id,
585 x_trip_rec => l_trip_rec_old,
586 x_return_status => l_return_status);
587
588 -- Map the new values to the record l_trip_rec_new
589 l_trip_rec_new := p_trip_rec;
590 --
591 IF l_debug_on THEN
592 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit FTE_WSH_INTERFACE_PKG.TRIP_SEGMENT_CHANGE',WSH_DEBUG_SV.C_PROC_LEVEL);
593 END IF;
594 --
595 FTE_WSH_INTERFACE_PKG.trip_segment_change
596 ( p_api_version => 1.0,
597 p_init_msg_list => FND_API.G_FALSE,
598 p_commit => FND_API.G_FALSE,
599 x_return_status => l_return_status,
600 x_msg_count => l_msg_count,
601 x_msg_data => l_msg_data,
602 p_old_trip_segment_rec => l_trip_rec_old,
603 p_new_trip_segment_rec => l_trip_rec_new,
604 p_tripSegmentChangeInRec => l_trip_seg_IN,
605 p_tripSegmentChangeOutRec => l_trip_seg_OUT
606 );
607 IF l_debug_on THEN
608 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
609 END IF;
610 END IF;
611
612 --5. Deleting Trip
613 /* anxsharm - as per talk with Nikhil ,WSH will not call FTE for Deletion
614 of Stops within a trip.WSH will call FTE for individual stop deletion
615 and individual trip deletion */
616 ELSIF (p_action = 'DELETE'
617 AND (p_trip_rec.trip_id <> FND_API.G_MISS_NUM)
618 ) THEN
619 l_trip_seg_IN.action_type := p_action;
620 -- Group API Call
621 IF (p_trip_rec.trip_id <> FND_API.G_MISS_NUM
622 AND p_trip_rec.trip_id IS NOT NULL
623 ) THEN
624
625 l_trip_rec_old := p_trip_rec;
626 --
627 IF l_debug_on THEN
628 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit FTE_WSH_INTERFACE_PKG.TRIP_SEGMENT_CHANGE',WSH_DEBUG_SV.C_PROC_LEVEL);
629 END IF;
630 --
631 FTE_WSH_INTERFACE_PKG.trip_segment_change
632 ( p_api_version => 1.0,
633 p_init_msg_list => FND_API.G_FALSE,
634 p_commit => FND_API.G_FALSE,
635 x_return_status => l_return_status,
636 x_msg_count => l_msg_count,
637 x_msg_data => l_msg_data,
638 p_old_trip_segment_rec => l_trip_rec_old,
639 p_new_trip_segment_rec => l_trip_rec_null,
640 p_tripSegmentChangeInRec => l_trip_seg_IN,
641 p_tripSegmentChangeOutRec => l_trip_seg_OUT
642 );
643 IF l_debug_on THEN
644 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
645 END IF;
646 END IF;
647
648 ELSE
649 IF p_action NOT IN ('ADD','UPDATE','DELETE') THEN
650 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_CALL_TO_FTE');
651 wsh_util_core.add_message(wsh_util_core.g_ret_sts_error,l_module_name);
652 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
653 ELSE
654 IF l_debug_on THEN
655 WSH_DEBUG_SV.log(l_module_name,'WSH_INVALID_CALL_TO_FTE');
656 END IF;
657 END IF;
658 END IF;
659
660 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
661 x_return_status := l_return_status;
662 END IF;
663
664 --
665 IF l_debug_on THEN
666 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
667 WSH_DEBUG_SV.pop(l_module_name);
668 END IF;
669 --
670 EXCEPTION
671 WHEN OTHERS THEN
672 WSH_UTIL_CORE.DEFAULT_HANDLER('WSH_FTE_INTEGRATION.trip_stop_validations',l_module_name);
673 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
674 --
675 IF l_debug_on THEN
676 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
677 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
678 END IF;
679 --
680 END trip_stop_validations;
681
682 -- To be called only by the constraints private API
683 -- Not to be called by anyone else
684
685 FUNCTION get_cc_object_name(
686 p_object_type IN VARCHAR2,
687 p_object_value_num IN NUMBER DEFAULT NULL,
688 p_object_parent_id IN NUMBER DEFAULT NULL,
689 p_object_value_char IN VARCHAR2 DEFAULT NULL,
690 x_fac_company_name OUT NOCOPY VARCHAR2,
691 x_fac_company_type OUT NOCOPY VARCHAR2 ) RETURN VARCHAR2
692
693 IS
694
695 --
696 -- Local Variable Declarations
697 --
698 l_result VARCHAR2(2000);
699 l_unexp_char VARCHAR2(30) := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
700
701
702 --
703 -- Variables used for error handling
704 --
705 l_error_code NUMBER; -- Oracle SQL Error Number
706 l_error_text VARCHAR2(2000); -- Oracle SQL Error Text
707
708
709 --
710 -- Variables used for debugging
711 --
712 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
713 --
714 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'get_cc_object_name';
715 --
716
717 others EXCEPTION;
718
719 BEGIN
720
721 --
722 IF l_debug_on THEN
723 WSH_DEBUG_SV.push(l_module_name);
724 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit FTE_COMP_CONSTRAINT_UTIL.get_object_name',WSH_DEBUG_SV.C_PROC_LEVEL);
725 END IF;
726 --
727
728 l_result := FTE_COMP_CONSTRAINT_UTIL.get_object_name(
729 p_object_type => p_object_type,
730 p_object_value_num => p_object_value_num,
731 p_object_parent_id => p_object_parent_id,
732 p_object_value_char => p_object_value_char,
733 x_fac_company_name => x_fac_company_name,
734 x_fac_company_type => x_fac_company_type );
735
736 IF l_result = l_unexp_char THEN
737 raise others;
738 END IF;
739
740 --
741 IF l_debug_on THEN
742 WSH_DEBUG_SV.log(l_module_name,'Returning object_name : ',l_result);
743 WSH_DEBUG_SV.pop(l_module_name);
744 END IF;
745 --
746 RETURN l_result;
747
748 EXCEPTION
749 WHEN OTHERS THEN
750 l_error_code := SQLCODE;
751 l_error_text := SQLERRM;
752 --WSH_UTIL_CORE.default_handler('WSH_FTE_INTEGRATION.get_cc_object_name');
753 --
754 IF l_debug_on THEN
755 WSH_DEBUG_SV.logmsg(l_module_name, 'THE UNEXPECTED ERROR FROM WSH_FTE_INTEGRATION.get_cc_object_name IS ' ||L_ERROR_TEXT );
756 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
757 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
758 END IF;
759 --
760 RETURN l_unexp_char;
761
762 END get_cc_object_name ;
763
764
765 -- Procedure : Get_Vehicle_Type
766 -- Purpose : Gets vehicle type ID from FTE_VEHICLE_TYPES
767
768 PROCEDURE Get_Vehicle_Type(
769 p_vehicle_item_id IN NUMBER,
770 p_vehicle_org_id IN NUMBER,
771 x_vehicle_type_id OUT NOCOPY NUMBER,
772 x_return_status OUT NOCOPY VARCHAR2) IS
773
774 CURSOR get_vehicle_type IS
775 SELECT vehicle_type_id
776 FROM fte_vehicle_types
777 WHERE inventory_item_id = p_vehicle_item_id AND
778 organization_id = p_vehicle_org_id;
779
780 BEGIN
781
782 OPEN get_vehicle_type;
783 FETCH get_vehicle_type INTO x_vehicle_type_id;
784 CLOSE get_vehicle_type;
785
786 IF (x_vehicle_type_id IS NOT NULL) THEN
787 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
788 ELSE
789 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
790 END IF;
791
792 EXCEPTION WHEN OTHERS THEN
793 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
794
795 END;
796
797 PROCEDURE GET_VEHICLE_ORG_ID
798 (p_inventory_item_id IN NUMBER,
799 x_vehicle_org_id OUT NOCOPY NUMBER,
800 x_return_status OUT NOCOPY VARCHAR2) IS
801
802 BEGIN
803
804 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
805
806 x_vehicle_org_id:=FTE_VEHICLE_PKG.GET_VEHICLE_ORG_ID
807 (p_inventory_item_id => p_inventory_item_id);
808
809 IF (x_vehicle_org_id IS NULL OR x_vehicle_org_id=-1) THEN
810 x_return_status:= WSH_UTIL_CORE.G_RET_STS_ERROR;
811 END IF;
812
813 EXCEPTION WHEN OTHERS THEN
814 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
815 END;
816
817 PROCEDURE Rate_Trip (
818 p_api_version IN NUMBER DEFAULT 1.0,
819 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
820 p_action_params IN rating_action_param_rec,
821 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
822 x_return_status OUT NOCOPY VARCHAR2,
823 x_msg_count OUT NOCOPY NUMBER,
824 x_msg_data OUT NOCOPY VARCHAR2)
825 IS
826 l_return_status VARCHAR2(1);
827 l_msg_count NUMBER := 0;
828 l_msg_data VARCHAR2(4000);
829 i NUMBER;
830 --
831 l_debug_on BOOLEAN;
832
833 l_action_param_rec FTE_TRIP_RATING_GRP.action_param_rec;
834
835
836 --
837 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'RATE_TRIP';
838 --
839 BEGIN
840 --
841 --
842 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
843 --
844 IF l_debug_on IS NULL
845 THEN
846 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
847 END IF;
848 --
849 IF l_debug_on THEN
850 WSH_DEBUG_SV.push(l_module_name);
851 END IF;
852 --
853 IF l_debug_on THEN
854 WSH_DEBUG_SV.logmsg(
855 l_module_name,
856 'p_action_params.trip_id_list.COUNT '|| p_action_params.trip_id_list.COUNT,
857 WSH_DEBUG_SV.C_PROC_LEVEL);
858 END IF;
859 IF l_debug_on THEN
860 WSH_DEBUG_SV.logmsg(
861 l_module_name,
862 'p_action_params.caller '|| p_action_params.caller,
863 WSH_DEBUG_SV.C_PROC_LEVEL);
864 END IF;
865 IF l_debug_on THEN
866 WSH_DEBUG_SV.logmsg(
867 l_module_name,
868 'p_action_params.event '|| p_action_params.event,
869 WSH_DEBUG_SV.C_PROC_LEVEL);
870 END IF;
871 IF l_debug_on THEN
872 WSH_DEBUG_SV.logmsg(
873 l_module_name,
874 'p_action_params.action '|| p_action_params.action,
875 WSH_DEBUG_SV.C_PROC_LEVEL);
876 END IF;
877 IF l_debug_on THEN
878 WSH_DEBUG_SV.logmsg(
879 l_module_name,
880 'p_commit '|| p_commit,
881 WSH_DEBUG_SV.C_PROC_LEVEL);
882 END IF;
883
884 x_return_status := FND_API.G_RET_STS_SUCCESS;
885
886 SAVEPOINT WSH_Rate_Trip;
887
888 IF WSH_UTIL_CORE.FTE_Is_Installed = 'Y' AND
889 (p_action_params.event is not null AND p_action_params.caller ='WSH'
890 AND p_action_params.action is not null) THEN
891
892 --
893 IF l_debug_on THEN
894 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit FTE_TRIP_RATING_GRP.RATE_TRIP',WSH_DEBUG_SV.C_PROC_LEVEL);
895 END IF;
896 --
897 l_action_param_rec.caller := p_action_params.caller;
898 l_action_param_rec.event := p_action_params.event;
899 l_action_param_rec.action := p_action_params.action;
900 l_action_param_rec.trip_id_list := p_action_params.trip_id_list;
901
902 --if the event is TP release and lane_id is not null, mark legs for reprice
903 IF l_action_param_rec.event='TP-RELEASE' THEN
904
905 --bug 3413328 update all legs reprice_required to Y if lane_id is present
906 --in trip this needs to be done only for TP release as for other cases,
907 --there will be no scenario in which a lane id will exist for a trip
908 --which has not been priced
909
910 FORALL i IN l_action_param_rec.trip_id_list.FIRST..l_action_param_rec.trip_id_list.LAST
911 UPDATE wsh_delivery_legs
912 SET reprice_required='Y',
913 last_update_date = SYSDATE,
914 last_updated_by = FND_GLOBAL.USER_ID,
915 last_update_login = FND_GLOBAL.LOGIN_ID
916 WHERE NVL(reprice_required, 'N') ='N'
917 and pick_up_stop_id IN
918 (select stop_id
919 from wsh_trip_stops wts, wsh_trips wt
920 where wts.trip_id=wt.trip_id
921 and wt.trip_id=l_action_param_rec.trip_id_list(i)
922 and wt.lane_id is not null
923 );
924
925 END IF;
926
927 FTE_TRIP_RATING_GRP.Rate_Trip (
928 p_api_version => p_api_version,
929 p_init_msg_list => p_init_msg_list,
930 p_action_params => l_action_param_rec,
931 p_commit => p_commit,
932 x_return_status => l_return_status,
933 x_msg_count => l_msg_count,
934 x_msg_data => l_msg_data);
935
936
937 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
938 x_return_status := l_return_status;
939 END IF;
940 x_msg_count := l_msg_count;
941 x_msg_data := l_msg_data;
942 END IF;
943 --
944 IF l_debug_on THEN
945 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
946 WSH_DEBUG_SV.pop(l_module_name);
947 END IF;
948 --
949 EXCEPTION
950 WHEN others THEN
951 ROLLBACK TO WSH_Rate_Trip;
952 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
953 wsh_util_core.default_handler('WSH_FTE_INTEGRATION.RATE_TRIP',l_module_name);
954 --
955 IF l_debug_on THEN
956 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
957 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
958 END IF;
959 --
960 END Rate_Trip;
961
962 -- +====================================================+
963 -- Name - Trip_Action
964 -- Parameters - (as required by FTE API)
965 --
966 -- Trip_Action API added for J+ project to handle action
967 -- code = Auto Tender
968 -- This API should be called only when p_trip_id_tab.count
969 -- is greater than zero
970 --
971 -- +====================================================+
972 PROCEDURE Trip_Action (
973 p_api_version IN NUMBER DEFAULT 1.0,
974 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
975 p_trip_id_tab IN WSH_UTIL_CORE.id_tab_type,
976 p_action_params IN wsh_trip_action_param_rec,
977 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
978 x_action_out_rec OUT NOCOPY wsh_trip_action_out_rec,
979 x_return_status OUT NOCOPY VARCHAR2,
980 x_msg_count OUT NOCOPY NUMBER,
981 x_msg_data OUT NOCOPY VARCHAR2) IS
982
983 -- FTE Datatypes which need to be passed to FTE API
984 l_fte_trip_action_param_rec FTE_TRIP_ACTION_PARAM_REC;
985 l_fte_action_out_rec FTE_ACTION_OUT_REC;
986 l_fte_trip_id_tab FTE_ID_TAB_TYPE; -- := FTE_ID_TAB_TYPE(41343);
987
988 l_wsh_result_id_tab WSH_UTIL_CORE.ID_TAB_TYPE;
989 l_wsh_valid_id_tab WSH_UTIL_CORE.ID_TAB_TYPE;
990
991 l_return_status VARCHAR2(1);
992 l_msg_count NUMBER := 0;
993 l_msg_data VARCHAR2(4000);
994 --
995 l_debug_on BOOLEAN;
996
997 --
998 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'TRIP_ACTION';
999 --
1000 BEGIN
1001 --
1002 --
1003 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1004 --
1005 IF l_debug_on IS NULL
1006 THEN
1007 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1008 END IF;
1009 --
1010 IF l_debug_on THEN
1011 WSH_DEBUG_SV.push(l_module_name);
1012 END IF;
1013 --
1014 IF l_debug_on THEN
1015 WSH_DEBUG_SV.logmsg(
1016 l_module_name,
1017 'p_trip_id_tab.COUNT '|| p_trip_id_tab.COUNT,
1018 WSH_DEBUG_SV.C_PROC_LEVEL);
1019 WSH_DEBUG_SV.logmsg(
1020 l_module_name,
1021 'p_action_params.action_code '|| p_action_params.action_code,
1022 WSH_DEBUG_SV.C_PROC_LEVEL);
1023 END IF;
1024
1025 x_return_status := FND_API.G_RET_STS_SUCCESS;
1026
1027 SAVEPOINT WSH_Trip_Action;
1028
1029 -- Check if FTE is installed
1030 -- J+ project for Auto Tender
1031 IF (WSH_UTIL_CORE.FTE_Is_Installed = 'Y' AND
1032 p_action_params.action_code = 'TENDER') THEN
1033
1034 --
1035 IF l_debug_on THEN
1036 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit FTE_MLS_UTIL.COPY_WSH_ID_TO_FTE_ID',WSH_DEBUG_SV.C_PROC_LEVEL);
1037 END IF;
1038 --
1039 l_fte_trip_id_tab := FTE_ID_TAB_TYPE();
1040 -- Convert Variables required as Input for FTE API
1041 FTE_MLS_UTIL.COPY_WSH_ID_TO_FTE_ID (p_wsh_id_tab => p_trip_id_tab,
1042 x_fte_id_tab => l_fte_trip_id_tab);
1043
1044 --
1045 IF l_debug_on THEN
1046 WSH_DEBUG_SV.logmsg(l_module_name,'After Calling program unit FTE_MLS_UTIL.COPY_WSH_ID_TO_FTE_ID',WSH_DEBUG_SV.C_PROC_LEVEL);
1047 END IF;
1048 --
1049 IF l_debug_on THEN
1050 WSH_DEBUG_SV.logmsg( l_module_name, 'trip_id_tab.COUNT '|| l_fte_trip_id_tab.COUNT,
1051 WSH_DEBUG_SV.C_PROC_LEVEL);
1052 END IF;
1053 -- As agreed with Harish, only populating the action code 'TENDER'
1054 -- other parameters are left null
1055 l_fte_trip_action_param_rec := FTE_TRIP_ACTION_PARAM_REC(null,'TENDER',
1056 null,null,null,null,null,null,
1057 null,null,null,null,null,null,
1058 null,null);
1059 l_fte_trip_action_param_rec.action_code := p_action_params.action_code;
1060 --
1061 IF l_debug_on THEN
1062 WSH_DEBUG_SV.logmsg(l_module_name,'After populating FTE_TRIP_ACTION_PARAM_REC',WSH_DEBUG_SV.C_PROC_LEVEL);
1063 END IF;
1064 --
1065 --
1066 IF l_debug_on THEN
1067 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit FTE_MLS_WRAPPER.TRIP_ACTION',WSH_DEBUG_SV.C_PROC_LEVEL);
1068 END IF;
1069 --
1070
1071 -- Call FTE API
1072 FTE_MLS_WRAPPER.Trip_Action
1073 (p_api_version_number => p_api_version,
1074 p_init_msg_list => p_init_msg_list,
1075 p_trip_id_tab => l_fte_trip_id_tab,
1076 p_action_prms => l_fte_trip_action_param_rec,
1077 x_action_out_rec => l_fte_action_out_rec,
1078 x_return_status => l_return_status,
1079 x_msg_count => l_msg_count,
1080 x_msg_data => l_msg_data
1081 );
1082
1083 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1084 x_return_status := l_return_status;
1085 END IF;
1086
1087 x_msg_count := l_msg_count;
1088 x_msg_data := l_msg_data;
1089
1090 --
1091 IF l_debug_on THEN
1092 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit FTE_MLS_UTIL.COPY_FTE_ID_TO_WSH_ID',WSH_DEBUG_SV.C_PROC_LEVEL);
1093 END IF;
1094 --
1095 -- Convert Variables which are Output from FTE API
1096 FTE_MLS_UTIL.COPY_FTE_ID_TO_WSH_ID (p_fte_id_tab => l_fte_action_out_rec.result_id_tab,
1097 x_wsh_id_tab => l_wsh_result_id_tab);
1098
1099 FTE_MLS_UTIL.COPY_FTE_ID_TO_WSH_ID (p_fte_id_tab => l_fte_action_out_rec.valid_ids_tab,
1100 x_wsh_id_tab => l_wsh_valid_id_tab);
1101 --
1102 IF l_debug_on THEN
1103 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1104 WSH_DEBUG_SV.log(l_module_name,'Result id tab Count',l_wsh_result_id_tab.count);
1105 WSH_DEBUG_SV.log(l_module_name,'Valid id tab Count',l_wsh_valid_id_tab.count);
1106 END IF;
1107 --
1108
1109 -- Transfer the ids to Output Data Structure
1110 x_action_out_rec.result_id_tab := l_wsh_result_id_tab;
1111 x_action_out_rec.valid_ids_tab := l_wsh_valid_id_tab; -- Success ids
1112
1113 -- LOGIC TO DETERMINE FAILED TRIPS to reside in FTE
1114
1115 END IF;
1116
1117 --
1118 IF l_debug_on THEN
1119 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
1120 WSH_DEBUG_SV.pop(l_module_name);
1121 END IF;
1122 --
1123 EXCEPTION
1124 WHEN others THEN
1125 ROLLBACK TO WSH_Trip_Action;
1126 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1127 wsh_util_core.default_handler('WSH_FTE_INTEGRATION.TRIP_ACTION',l_module_name);
1128 --
1129 IF l_debug_on THEN
1130 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1131 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1132 END IF;
1133
1134 END Trip_Action;
1135
1136 --
1137 -- SBAKSHI
1138 --
1139
1140 PROCEDURE CARRIER_SELECTION( p_format_cs_tab IN OUT NOCOPY WSH_FTE_INTEGRATION.wsh_cs_entity_tab_type,
1141 p_messaging_yn IN VARCHAR2,
1142 p_caller IN VARCHAR2,
1143 p_entity IN VARCHAR2,
1144 x_cs_output_tab OUT NOCOPY WSH_FTE_INTEGRATION.wsh_cs_result_tab_type,
1145 x_cs_output_message_tab OUT NOCOPY WSH_FTE_INTEGRATION.wsh_cs_output_message_tab,
1146 x_return_message OUT NOCOPY VARCHAR2,
1147 x_return_status OUT NOCOPY VARCHAR2) IS
1148
1149 --
1150 -- Local Variable Declarations
1151 --
1152 l_fte_format_cs_tab FTE_ACS_PKG.fte_cs_entity_tab_type;
1153 l_fte_cs_result_tab FTE_ACS_PKG.fte_cs_result_tab_type;
1154 l_fte_cs_output_message_tab FTE_ACS_PKG.fte_cs_output_message_tab;
1155
1156 l_start_search_level VARCHAR2(10) := 'SCOE';
1157 l_return_message VARCHAR2(2000); -- output result message
1158 l_return_status VARCHAR2(1); -- output status
1159
1160 --
1161 -- Variables used for error handling
1162 --
1163 l_error_code NUMBER; -- Oracle SQL Error Number
1164 l_error_text VARCHAR2(2000); -- Oracle SQL Error Text
1165
1166 j NUMBER;
1167
1168 --
1169 -- Variables used for debugging
1170 --
1171 l_debug_on BOOLEAN;
1172
1173 --
1174 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CARRIER_SELECTION';
1175 --
1176
1177 BEGIN
1178 --
1179 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1180 --
1181
1182 IF l_debug_on IS NULL
1183 THEN
1184 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1185 END IF;
1186 --
1187
1188 IF l_debug_on THEN
1189 WSH_DEBUG_SV.push(l_module_name);
1190 END IF;
1191
1192 --
1193 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1194 x_return_message := null;
1195
1196 IF WSH_UTIL_CORE.FTE_Is_Installed <> 'Y' THEN
1197 return;
1198 END IF;
1199
1200 --
1201 -- Map input WSH record/tables to FTE record/tables
1202 --
1203 IF (p_format_cs_tab.COUNT > 0 ) THEN
1204
1205 FOR i IN p_format_cs_tab.FIRST..p_format_cs_tab.LAST LOOP
1206 j := l_fte_format_cs_tab.COUNT;
1207
1208 l_fte_format_cs_tab(j).delivery_id := p_format_cs_tab(i).delivery_id;
1209 l_fte_format_cs_tab(j).delivery_name := p_format_cs_tab(i).delivery_name;
1210 l_fte_format_cs_tab(j).trip_id := p_format_cs_tab(i).trip_id;
1211 l_fte_format_cs_tab(j).trip_name := p_format_cs_tab(i).trip_name;
1212 l_fte_format_cs_tab(j).organization_id := p_format_cs_tab(i).organization_id;
1213 l_fte_format_cs_tab(j).triporigin_internalorg_id := p_format_cs_tab(i).triporigin_internalorg_id;
1214 l_fte_format_cs_tab(j).gross_weight := p_format_cs_tab(i).gross_weight;
1215 l_fte_format_cs_tab(j).weight_uom_code := p_format_cs_tab(i).weight_uom_code;
1216 l_fte_format_cs_tab(j).volume := p_format_cs_tab(i).volume;
1217 l_fte_format_cs_tab(j).volume_uom_code := p_format_cs_tab(i).volume_uom_code;
1218 l_fte_format_cs_tab(j).initial_pickup_loc_id := p_format_cs_tab(i).initial_pickup_loc_id;
1219 l_fte_format_cs_tab(j).ultimate_dropoff_loc_id := p_format_cs_tab(i).ultimate_dropoff_loc_id;
1220 l_fte_format_cs_tab(j).customer_id := p_format_cs_tab(i).customer_id;
1221 l_fte_format_cs_tab(j).customer_site_id := p_format_cs_tab(i).customer_site_id;
1222 l_fte_format_cs_tab(j).freight_terms_code := p_format_cs_tab(i).freight_terms_code;
1223 l_fte_format_cs_tab(j).initial_pickup_date := p_format_cs_tab(i).initial_pickup_date;
1224 l_fte_format_cs_tab(j).ultimate_dropoff_date := p_format_cs_tab(i).ultimate_dropoff_date;
1225 l_fte_format_cs_tab(j).fob_code := p_format_cs_tab(i).fob_code;
1226 -- These fields are just placeholders
1227 -- l_fte_format_cs_tab(j).start_search_level := p_format_cs_tab(i).start_search_level;
1228 l_fte_format_cs_tab(j).transit_time := p_format_cs_tab(i).transit_time;
1229 -- l_fte_fromat_cs_tab(j).rule_id := p_fromat_cs_tab(i).rule_id;
1230 -- l_fte_format_cs_tab(j).result_found_flag := p_format_cs_tab(i).result_found_flag;
1231
1232 END LOOP;
1233 END IF;
1234 --
1235 -- Call carrier selection module
1236 --
1237 IF l_debug_on THEN
1238 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit FTE_ACS_PKG.START_ACS',WSH_DEBUG_SV.C_PROC_LEVEL);
1239 END IF;
1240 --
1241
1242 FTE_ACS_PKG.GET_ROUTING_RESULTS( --p_start_search_level_flag => p_start_search_level_flag,
1243 p_format_cs_tab => l_fte_format_cs_tab,
1244 p_messaging_yn => p_messaging_yn,
1245 p_caller => p_caller,
1246 p_entity => p_entity,
1247 x_cs_output_tab => l_fte_cs_result_tab,
1248 x_cs_output_message_tab => l_fte_cs_output_message_tab,
1249 x_return_message => l_return_message,
1250 x_return_status => l_return_status);
1251
1252 --
1253 -- Map the result FTE tables back to WSH tables
1254 --
1255
1256 IF (l_fte_cs_result_tab.COUNT > 0) THEN
1257
1258 FOR i IN l_fte_cs_result_tab.FIRST..l_fte_cs_result_tab.LAST LOOP
1259
1260 j:= x_cs_output_tab.COUNT;
1261 x_cs_output_tab(j).rule_id := l_fte_cs_result_tab(i).rule_id;
1262 x_cs_output_tab(j).rule_name := l_fte_cs_result_tab(i).rule_name;
1263 x_cs_output_tab(j).delivery_id := l_fte_cs_result_tab(i).delivery_id;
1264 x_cs_output_tab(j).organization_id := l_fte_cs_result_tab(i).organization_id;
1265 x_cs_output_tab(j).initial_pickup_location_id := l_fte_cs_result_tab(i).initial_pickup_location_id;
1266 x_cs_output_tab(j).ultimate_dropoff_location_id := l_fte_cs_result_tab(i).ultimate_dropoff_location_id;
1267 x_cs_output_tab(j).trip_id := l_fte_cs_result_tab(i).trip_id;
1268 x_cs_output_tab(j).result_type := l_fte_cs_result_tab(i).result_type;
1269 x_cs_output_tab(j).rank := l_fte_cs_result_tab(i).rank;
1270 x_cs_output_tab(j).leg_destination := l_fte_cs_result_tab(i).leg_destination;
1271 x_cs_output_tab(j).leg_sequence := l_fte_cs_result_tab(i).leg_sequence;
1272 -- x_cs_output_tab(j).itinerary_id := l_fte_cs_result_tab(i).itinerary_id;
1273 x_cs_output_tab(j).carrier_id := l_fte_cs_result_tab(i).carrier_id;
1274 x_cs_output_tab(j).mode_of_transport := l_fte_cs_result_tab(i).mode_of_transport;
1275 x_cs_output_tab(j).service_level := l_fte_cs_result_tab(i).service_level;
1276 x_cs_output_tab(j).ship_method_code := l_fte_cs_result_tab(i).ship_method_code;
1277 x_cs_output_tab(j).freight_terms_code := l_fte_cs_result_tab(i).freight_terms_code;
1278 x_cs_output_tab(j).consignee_carrier_ac_no := l_fte_cs_result_tab(i).consignee_carrier_ac_no;
1279 -- x_cs_output_tab(j).track_only_flag := l_fte_cs_result_tab(i).track_only_flag;
1280 x_cs_output_tab(j).result_level := l_fte_cs_result_tab(i).result_level;
1281 x_cs_output_tab(j).pickup_date := l_fte_cs_result_tab(i).pickup_date;
1282 x_cs_output_tab(j).dropoff_date := l_fte_cs_result_tab(i).dropoff_date;
1283 x_cs_output_tab(j).min_transit_time := l_fte_cs_result_tab(i).min_transit_time;
1284 x_cs_output_tab(j).max_transit_time := l_fte_cs_result_tab(i).max_transit_time;
1285 x_cs_output_tab(j).append_flag := l_fte_cs_result_tab(i).append_flag;
1286 --x_cs_output_tab(j).routing_rule_id := l_fte_cs_result_tab(i).routing_rule_id;
1287
1288 END LOOP;
1289 END IF;
1290
1291
1292 IF (l_fte_cs_output_message_tab.COUNT > 0) THEN
1293 FOR i IN l_fte_cs_output_message_tab.FIRST..l_fte_cs_output_message_tab.LAST LOOP
1294 j := x_cs_output_message_tab.COUNT;
1295
1296 x_cs_output_message_tab(j).sequence_number := l_fte_cs_output_message_tab(i).sequence_number;
1297 x_cs_output_message_tab(j).message_type := l_fte_cs_output_message_tab(i).message_type;
1298 x_cs_output_message_tab(j).message_code := l_fte_cs_output_message_tab(i).message_code;
1299 x_cs_output_message_tab(j).message_text := l_fte_cs_output_message_tab(i).message_text;
1300 x_cs_output_message_tab(j).level := l_fte_cs_output_message_tab(i).level;
1301 x_cs_output_message_tab(j).query_id := l_fte_cs_output_message_tab(i).query_id;
1302 x_cs_output_message_tab(j).group_id := l_fte_cs_output_message_tab(i).group_id;
1303
1304 END LOOP;
1305 END IF;
1306
1307 x_return_message := l_return_message;
1308 x_return_status := l_return_status;
1309
1310 --
1311 IF l_debug_on THEN
1312 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
1313 WSH_DEBUG_SV.pop(l_module_name);
1314 END IF;
1315 --
1316 RETURN;
1317
1318 EXCEPTION
1319 WHEN OTHERS THEN
1320 l_error_code := SQLCODE;
1321 l_error_text := SQLERRM;
1322 --
1323 IF l_debug_on THEN
1324 WSH_DEBUG_SV.logmsg(l_module_name, 'THE UNEXPECTED ERROR FROM WSH_FTE_INTEGRATION.CARRIER_SELECTION IS ' ||L_ERROR_TEXT );
1325 END IF;
1326 --
1327 WSH_UTIL_CORE.default_handler('WSH_FTE_INTEGRATION.CARRIER_SELECTION');
1328 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1329 x_return_message := l_error_text;
1330 --
1331 IF l_debug_on THEN
1332 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1333 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1334 END IF;
1335 --
1336 RETURN;
1337 END CARRIER_SELECTION;
1338
1339
1340 PROCEDURE RANK_LIST_ACTION(
1341 p_api_version IN NUMBER DEFAULT 1.0,
1342 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1343 x_return_status OUT NOCOPY VARCHAR2,
1344 x_msg_count OUT NOCOPY NUMBER,
1345 x_msg_data OUT NOCOPY VARCHAR2,
1346 p_action_code IN VARCHAR2,
1347 p_ranklist IN OUT NOCOPY CARRIER_RANK_LIST_TBL_TYPE,
1348 p_trip_id IN NUMBER,
1349 p_rank_id IN NUMBER
1350 --x_ranklist OUT NOCOPY CARRIER_RANK_LIST_TBL_TYPE
1351 )
1352 IS
1353
1354 l_ranked_list FTE_CARRIER_RANK_LIST_PVT.CARRIER_RANK_LIST_TBL_TYPE;
1355 l_rank_id NUMBER := NULL;
1356 list_cnt NUMBER := 0;
1357
1358 l_error_code NUMBER; -- Oracle SQL Error Number
1359 l_error_text VARCHAR2(2000); -- Oracle SQL Error Text
1360
1361 l_return_status VARCHAR2(1);
1362 l_msg_count NUMBER := 0;
1363 l_msg_data VARCHAR2(4000);
1364 --
1365 l_debug_on BOOLEAN;
1366
1367 --
1368 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'RANK_LIST_ACTION';
1369 --
1370 BEGIN
1371 --
1372 --
1373 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1374 --
1375 IF l_debug_on IS NULL
1376 THEN
1377 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1378 END IF;
1379 --
1380 IF l_debug_on THEN
1381 WSH_DEBUG_SV.push(l_module_name);
1382 END IF;
1383 --
1384
1385 x_return_status := FND_API.G_RET_STS_SUCCESS;
1386
1387 IF (WSH_UTIL_CORE.FTE_Is_Installed = 'Y') THEN
1388
1389 -- Copy p_ranked_list to FTE_CARRIER_RANK_LIST_PVT.CARRIER_RANK_LIST_TBL_TYPE
1390
1391 list_cnt := p_ranklist.FIRST;
1392 IF list_cnt IS NOT NULL THEN
1393 LOOP
1394
1395 l_ranked_list(list_cnt).TRIP_ID := p_ranklist(list_cnt).TRIP_ID;
1396 l_ranked_list(list_cnt).RANK_SEQUENCE := p_ranklist(list_cnt).RANK_SEQUENCE;
1397 l_ranked_list(list_cnt).CARRIER_ID := p_ranklist(list_cnt).CARRIER_ID;
1398 l_ranked_list(list_cnt).SERVICE_LEVEL := p_ranklist(list_cnt).SERVICE_LEVEL;
1399 l_ranked_list(list_cnt).MODE_OF_TRANSPORT := p_ranklist(list_cnt).MODE_OF_TRANSPORT;
1400 l_ranked_list(list_cnt).consignee_carrier_ac_no := p_ranklist(list_cnt).consignee_carrier_ac_no;
1401 l_ranked_list(list_cnt).freight_terms_code := p_ranklist(list_cnt).freight_terms_code;
1402 l_ranked_list(list_cnt).IS_CURRENT := p_ranklist(list_cnt).IS_CURRENT;
1403 l_ranked_list(list_cnt).CALL_RG_FLAG := p_ranklist(list_cnt).CALL_RG_FLAG;
1404 l_ranked_list(list_cnt).SOURCE := p_ranklist(list_cnt).SOURCE;
1405
1406 EXIT WHEN list_cnt = p_ranklist.LAST;
1407 list_cnt := p_ranklist.NEXT(list_cnt);
1408
1409 END LOOP;
1410 END IF;
1411
1412 FTE_CARRIER_RANK_LIST_PVT.RANK_LIST_ACTION(
1413 p_api_version_number => 1.0,
1414 p_init_msg_list => p_init_msg_list,
1415 x_return_status => l_return_status,
1416 x_msg_count => x_msg_count,
1417 x_msg_data => x_msg_data,
1418 --x_ranklist => l_ranked_list,
1419 p_action_code => p_action_code,
1420 p_ranklist => l_ranked_list,
1421 p_trip_id => p_trip_id,
1422 p_rank_id => l_rank_id);
1423
1424 -- Copy l_ranked_list to WSH_FTE_INTEGRATION.CARRIER_RANK_LIST_TBL_TYPE
1425
1426 list_cnt := l_ranked_list.FIRST;
1427 IF list_cnt IS NOT NULL THEN
1428 LOOP
1429
1430 p_ranklist(list_cnt).TRIP_ID := l_ranked_list(list_cnt).TRIP_ID;
1431 p_ranklist(list_cnt).RANK_SEQUENCE := l_ranked_list(list_cnt).RANK_SEQUENCE;
1432 p_ranklist(list_cnt).CARRIER_ID := l_ranked_list(list_cnt).CARRIER_ID;
1433 p_ranklist(list_cnt).SERVICE_LEVEL := l_ranked_list(list_cnt).SERVICE_LEVEL;
1434 p_ranklist(list_cnt).MODE_OF_TRANSPORT := l_ranked_list(list_cnt).MODE_OF_TRANSPORT;
1435 p_ranklist(list_cnt).consignee_carrier_ac_no := l_ranked_list(list_cnt).consignee_carrier_ac_no;
1436 p_ranklist(list_cnt).freight_terms_code := l_ranked_list(list_cnt).freight_terms_code;
1437 p_ranklist(list_cnt).IS_CURRENT := l_ranked_list(list_cnt).IS_CURRENT;
1438 p_ranklist(list_cnt).CALL_RG_FLAG := l_ranked_list(list_cnt).CALL_RG_FLAG;
1439 p_ranklist(list_cnt).SOURCE := l_ranked_list(list_cnt).SOURCE;
1440 p_ranklist(list_cnt).rank_id := l_ranked_list(list_cnt).rank_id;
1441
1442 EXIT WHEN list_cnt = l_ranked_list.LAST;
1443 list_cnt := l_ranked_list.NEXT(list_cnt);
1444
1445 END LOOP;
1446 END IF;
1447
1448
1449 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1450 x_return_status := l_return_status;
1451 END IF;
1452
1453 x_msg_count := l_msg_count;
1454 x_msg_data := l_msg_data;
1455
1456 END IF;
1457
1458 --
1459 IF l_debug_on THEN
1460 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
1461 WSH_DEBUG_SV.pop(l_module_name);
1462 END IF;
1463 --
1464 EXCEPTION
1465 WHEN OTHERS THEN
1466 l_error_code := SQLCODE;
1467 l_error_text := SQLERRM;
1468 --
1469 IF l_debug_on THEN
1470 WSH_DEBUG_SV.logmsg(l_module_name, 'THE UNEXPECTED ERROR FROM WSH_FTE_INTEGRATION.RANK_LIST_ACTION IS ' ||L_ERROR_TEXT );
1471 END IF;
1472 --
1473 WSH_UTIL_CORE.default_handler('WSH_FTE_INTEGRATION.RANK_LIST_ACTION');
1474 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1475 x_msg_data := l_error_text;
1476 --
1477 IF l_debug_on THEN
1478 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1479 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1480 END IF;
1481 --
1482 END RANK_LIST_ACTION;
1483
1484 FUNCTION GET_TRIP_MOVE(
1485 p_trip_id IN NUMBER) RETURN NUMBER
1486
1487 IS
1488 CURSOR c_get_trip_cmove(c_trip_id IN NUMBER) IS
1489 SELECT MOVE_ID
1490 FROM FTE_TRIP_MOVES
1491 WHERE TRIP_ID = c_trip_id;
1492
1493 l_move_id NUMBER := -1;
1494
1495 l_error_code NUMBER; -- Oracle SQL Error Number
1496 l_error_text VARCHAR2(2000); -- Oracle SQL Error Text
1497
1498 --
1499 l_debug_on BOOLEAN;
1500
1501 --
1502 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_TRIP_MOVE';
1503 --
1504 BEGIN
1505
1506 --
1507 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1508 --
1509 IF l_debug_on IS NULL
1510 THEN
1511 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1512 END IF;
1513 --
1514 IF l_debug_on THEN
1515 WSH_DEBUG_SV.push(l_module_name);
1516 WSH_DEBUG_SV.logmsg(l_module_name, 'trip id : ' ||p_trip_id );
1517 END IF;
1518 --
1519
1520 IF (WSH_UTIL_CORE.FTE_Is_Installed = 'Y') THEN
1521
1522 OPEN c_get_trip_cmove(p_trip_id);
1523 --LOOP
1524 FETCH c_get_trip_cmove INTO l_move_id;
1525 --EXIT WHEN c_get_trip_cmove%NOTFOUND;
1526 --END LOOP;
1527 IF c_get_trip_cmove%NOTFOUND THEN
1528 l_move_id := -1;
1529 END IF;
1530 CLOSE c_get_trip_cmove;
1531
1532 /*
1533 IF c_get_trip_cmove%ROWCOUNT = 0 THEN
1534 l_move_id := -1;
1535 END IF;
1536 */
1537 END IF;
1538
1539 --
1540 IF l_debug_on THEN
1541 WSH_DEBUG_SV.pop(l_module_name);
1542 END IF;
1543 --
1544 RETURN l_move_id;
1545
1546 EXCEPTION
1547 WHEN OTHERS THEN
1548 IF c_get_trip_cmove%ISOPEN THEN
1549 CLOSE c_get_trip_cmove;
1550 END IF;
1551 l_error_code := SQLCODE;
1552 l_error_text := SQLERRM;
1553 --
1554 IF l_debug_on THEN
1555 WSH_DEBUG_SV.logmsg(l_module_name, 'THE UNEXPECTED ERROR FROM WSH_FTE_INTEGRATION.GET_TRIP_MOVE IS ' ||L_ERROR_TEXT );
1556 END IF;
1557 --
1558 WSH_UTIL_CORE.default_handler('WSH_FTE_INTEGRATION.GET_TRIP_MOVE');
1559 --
1560 IF l_debug_on THEN
1561 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1562 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1563 END IF;
1564 --
1565 RETURN -1;
1566 END GET_TRIP_MOVE;
1567
1568
1569 -- ----------------------------------------------------------------------
1570 -- Procedure: CARRIER_SEL_CREATE_TRIP
1571 --
1572 -- Parameters: p_delivery_id Delivery ID
1573 -- p_carrier_sel_result_rec WSH_FTE_INTEGRATION.WSH_CS_RESULT_REC_TYPE
1574 -- x_trip_id Trip Id
1575 -- x_trip_name Trip Name
1576 -- x_return_message Return Message
1577 -- x_return_status Return Status
1578 --
1579 -- COMMENT : This procedure is called from Process Carrier Selection API
1580 -- in order to create trip for deliveries not assigned to trips
1581 --
1582 -- This procedure is a wrapper for FTE_ACS_TRIP_PKG.CARRIER_SEL_CREATE_TRIP
1583 --
1584 -- ----------------------------------------------------------------------
1585 PROCEDURE CARRIER_SEL_CREATE_TRIP( p_delivery_id IN NUMBER,
1586 --p_initial_pickup_loc_id IN NUMBER,
1587 --p_ultimate_dropoff_loc_id IN NUMBER,
1588 --p_initial_pickup_date IN DATE,
1589 --p_ultimate_dropoff_date IN DATE,
1590 p_carrier_sel_result_rec IN WSH_FTE_INTEGRATION.WSH_CS_RESULT_REC_TYPE,
1591 x_trip_id OUT NOCOPY NUMBER,
1592 x_trip_name OUT NOCOPY VARCHAR2,
1593 x_return_message OUT NOCOPY VARCHAR2,
1594 x_return_status OUT NOCOPY VARCHAR2
1595 )IS
1596
1597 l_debug_on BOOLEAN;
1598
1599 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CARRIER_SEL_CREATE_TRIP';
1600
1601
1602 BEGIN
1603 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1604 --
1605 IF l_debug_on IS NULL THEN
1606 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1607 END IF;
1608 --
1609 IF l_debug_on THEN
1610 WSH_DEBUG_SV.push(l_module_name);
1611 END IF;
1612
1613 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1614
1615 FTE_ACS_TRIP_PKG.CARRIER_SEL_CREATE_TRIP(
1616 p_delivery_id => p_delivery_id,
1617 p_carrier_sel_result_rec => p_carrier_sel_result_rec,
1618 x_trip_id => x_trip_id,
1619 x_trip_name => x_trip_name,
1620 x_return_message => x_return_message,
1621 x_return_status => x_return_status);
1622
1623 IF x_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
1624 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1625 END IF;
1626
1627 --
1628 -- Debug Statements
1629 --
1630 IF l_debug_on THEN
1631 WSH_DEBUG_SV.pop(l_module_name);
1632 END IF;
1633
1634 EXCEPTION
1635
1636 WHEN OTHERS THEN
1637 wsh_util_core.default_handler('WSH_FTE_INTEGRATION.CARRIER_SEL_CREATE_TRIP',l_module_name);
1638
1639 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1640
1641 IF l_debug_on THEN
1642 WSH_DEBUG_SV.pop(l_module_name,'When Others');
1643 END IF;
1644
1645 END CARRIER_SEL_CREATE_TRIP;
1646
1647
1648
1649
1650
1651 -- ----------------------------------------------------------------------
1652 -- Procedure: GET_ORG_ORGANIZATION_INFO
1653 --
1654 -- Parameters:
1655 -- p_init_msg_list Flag to initialize message stack
1656 -- x_return_message Return Message
1657 -- x_msg_count count of messages
1658 -- p_msg_data message text
1659 -- x_organization_id inventory organization identifier
1660 -- x_org_id operating unit identifier
1661 -- p_entity_id entity identifier
1662 -- p_entity_type 'TRIP' or 'DELIVERY'
1663 -- p_org_id_flag flag to optionally get x_org_id
1664 -- FND_API.G_TRUE -> yes
1665 -- FND_API.G_FALSE -> no
1666 --
1667 --
1668 -- COMMENT : This procedure calls FTE to associate a trip with
1669 -- inventory organization and optionally the operating unit.
1670 --
1671 -- This procedure is a wrapper for
1672 -- FTE_WSH_INTEGRATION_PKG.GET_ORG_ORGANIZATION_INFO
1673 --
1674 -- FTE will always be called regardless of
1675 -- WSH_UTIL_CORE.FTE_Is_Installed value.
1676 --
1677 -- ----------------------------------------------------------------------
1678 PROCEDURE GET_ORG_ORGANIZATION_INFO(
1679 p_init_msg_list IN VARCHAR2,
1680 x_return_status OUT NOCOPY VARCHAR2,
1681 x_msg_count OUT NOCOPY NUMBER,
1682 x_msg_data OUT NOCOPY VARCHAR2,
1683 x_organization_id OUT NOCOPY NUMBER,
1684 x_org_id OUT NOCOPY NUMBER,
1685 p_entity_id IN NUMBER,
1686 p_entity_type IN VARCHAR2,
1687 p_org_id_flag IN VARCHAR2)
1688 IS
1689
1690 l_debug_on BOOLEAN;
1691 l_module_name CONSTANT VARCHAR2(100) :=
1692 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_ORG_ORGANIZATION_INFO';
1693 BEGIN
1694 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1695 --
1696 IF l_debug_on IS NULL THEN
1697 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1698 END IF;
1699 --
1700 IF l_debug_on THEN
1701 WSH_DEBUG_SV.push(l_module_name);
1702 WSH_DEBUG_SV.log(l_module_name, 'p_init_msg_list', p_init_msg_list);
1703 WSH_DEBUG_SV.log(l_module_name, 'p_entity_id', p_entity_id);
1704 WSH_DEBUG_SV.log(l_module_name, 'p_entity_type', p_entity_type);
1705 WSH_DEBUG_SV.log(l_module_name, 'p_org_id_flag', p_org_id_flag);
1706 WSH_DEBUG_SV.logmsg(l_module_name,
1707 'Calling FTE_WSH_INTEGRATION_PKG.GET_ORG_ORGANIZATION_INFO',
1708 WSH_DEBUG_SV.C_PROC_LEVEL);
1709 END IF;
1710
1711 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1712
1713 FTE_WSH_INTERFACE_PKG.GET_ORG_ORGANIZATION_INFO(
1714 p_init_msg_list => p_init_msg_list,
1715 x_return_status => x_return_status,
1716 x_msg_count => x_msg_count,
1717 x_msg_data => x_msg_data,
1718 x_organization_id => x_organization_id,
1719 x_org_id => x_org_id,
1720 p_entity_id => p_entity_id,
1721 p_entity_type => p_entity_type,
1722 p_org_id_flag => p_org_id_flag
1723 );
1724
1725 IF l_debug_on THEN
1726 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
1727 WSH_DEBUG_SV.log(l_module_name, 'x_msg_count', x_msg_count);
1728 WSH_DEBUG_SV.log(l_module_name, 'x_msg_data', x_msg_data);
1729 WSH_DEBUG_SV.log(l_module_name, 'x_organization_id', x_organization_id);
1730 WSH_DEBUG_SV.log(l_module_name, 'x_org_id', x_org_id);
1731 WSH_DEBUG_SV.pop(l_module_name);
1732 END IF;
1733
1734 EXCEPTION
1735
1736 WHEN OTHERS THEN
1737
1738 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1739 wsh_util_core.default_handler(
1740 'WSH_FTE_INTEGRATION.GET_ORG_ORGANIZATION_INFO',
1741 l_module_name);
1742
1743 IF l_debug_on THEN
1744 WSH_DEBUG_SV.logmsg(l_module_name,
1745 'Unexpected error has occured. Oracle error message is '
1746 || SQLERRM,
1747 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1748 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1749 END IF;
1750
1751 END GET_ORG_ORGANIZATION_INFO;
1752
1753
1754
1755 -- ----------------------------------------------------------------------
1756 -- Procedure: CREATE_RANK_LIST_BULK
1757 --
1758 -- Parameters:
1759 -- p_api_version_number API version number (1)
1760 -- p_init_msg_list Flag to initialize message stack
1761 -- x_return_message Return Message
1762 -- x_msg_count count of messages
1763 -- p_msg_data message text
1764 -- p_source source of call; valid values:
1765 -- C_RANKLIST_SOURCE_%
1766 -- p_trip_id_tab table of trip identifiers
1767 --
1768 --
1769 -- COMMENT : This procedure calls FTE to perform a bulk operation
1770 -- on ranking carriers in trips.
1771 --
1772 -- This procedure is a wrapper for
1773 -- FTE_CARRIER_RANK_LIST_PVT.CREATE_RANK_LIST_BULK
1774 --
1775 -- It will pull the required values from WSH_TRIPS to
1776 -- build the rank list for the FTE API.
1777 --
1778 -- ----------------------------------------------------------------------
1779 PROCEDURE CREATE_RANK_LIST_BULK(
1780 p_api_version_number IN NUMBER,
1781 p_init_msg_list IN VARCHAR2,
1782 x_return_status OUT NOCOPY VARCHAR2,
1783 x_msg_count OUT NOCOPY NUMBER,
1784 x_msg_data OUT NOCOPY VARCHAR2,
1785 p_source IN VARCHAR2,
1786 p_trip_id_tab IN WSH_UTIL_CORE.ID_TAB_TYPE)
1787 IS
1788
1789 l_debug_on BOOLEAN;
1790 l_module_name CONSTANT VARCHAR2(100) :=
1791 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_RANK_LIST_BULK';
1792 --
1793 l_index NUMBER;
1794 i NUMBER;
1795 l_ranklist FTE_CARRIER_RANK_LIST_PVT.carrier_rank_list_bulk_rec;
1796 BEGIN
1797 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1798 --
1799 IF l_debug_on IS NULL THEN
1800 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1801 END IF;
1802 --
1803 IF l_debug_on THEN
1804 WSH_DEBUG_SV.push(l_module_name);
1805 WSH_DEBUG_SV.log(l_module_name, 'p_api_version_number',
1806 p_api_version_number);
1807 WSH_DEBUG_SV.log(l_module_name, 'p_init_msg_list',
1808 p_init_msg_list);
1809 WSH_DEBUG_SV.log(l_module_name, 'p_source',
1810 p_source);
1811 WSH_DEBUG_SV.log(l_module_name, 'p_trip_id_tab.COUNT',
1812 p_trip_id_tab.COUNT);
1813 END IF;
1814
1815 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1816
1817 IF p_trip_id_tab.COUNT > 0
1818 AND (WSH_UTIL_CORE.FTE_Is_Installed = 'Y') THEN
1819
1820 IF l_debug_on THEN
1821 WSH_DEBUG_SV.logmsg(l_module_name,
1822 'Selecting WSH_TRIPS into rank list');
1823 END IF;
1824
1825 FOR i IN p_trip_id_tab.FIRST .. p_trip_id_tab.LAST LOOP
1826 SELECT
1827 p_source SOURCE,
1828 1 RANK_SEQUENCE,
1829 TO_CHAR(NULL) CALL_RG_FLAG,
1830 TO_DATE(NULL) SCHEDULE_FROM,
1831 TO_DATE(NULL) SCHEDULE_TO,
1832 TO_NUMBER(NULL) ESTIMATED_RATE,
1833 TO_CHAR(NULL) CURRENCY_CODE,
1834 TO_NUMBER(NULL) ESTIMATED_TRANSIT_TIME,
1835 TO_CHAR(NULL) TRANSIT_TIME_UOM,
1836 wt.TRIP_ID,
1837 wt.RANK_ID,
1838 wt.LANE_ID,
1839 wt.CARRIER_ID,
1840 wt.SERVICE_LEVEL,
1841 wt.MODE_OF_TRANSPORT,
1842 wt.VEHICLE_ORGANIZATION_ID VEHICLE_ORG_ID,
1843 wt.VEHICLE_ITEM_ID,
1844 wt.CONSIGNEE_CARRIER_AC_NO,
1845 wt.FREIGHT_TERMS_CODE,
1846 wt.SCHEDULE_ID,
1847 wt.ATTRIBUTE_CATEGORY,
1848 wt.ATTRIBUTE1,
1849 wt.ATTRIBUTE2,
1850 wt.ATTRIBUTE3,
1851 wt.ATTRIBUTE4,
1852 wt.ATTRIBUTE5,
1853 wt.ATTRIBUTE6,
1854 wt.ATTRIBUTE7,
1855 wt.ATTRIBUTE8,
1856 wt.ATTRIBUTE9,
1857 wt.ATTRIBUTE10,
1858 wt.ATTRIBUTE11,
1859 wt.ATTRIBUTE12,
1860 wt.ATTRIBUTE13,
1861 wt.ATTRIBUTE14,
1862 wt.ATTRIBUTE15
1863 INTO
1864 l_ranklist.SOURCE(i),
1865 l_ranklist.RANK_SEQUENCE(i),
1866 l_ranklist.CALL_RG_FLAG(i),
1867 l_ranklist.SCHEDULE_FROM(i),
1868 l_ranklist.SCHEDULE_TO(i),
1869 l_ranklist.ESTIMATED_RATE(i),
1870 l_ranklist.CURRENCY_CODE(i),
1871 l_ranklist.ESTIMATED_TRANSIT_TIME(i),
1872 l_ranklist.TRANSIT_TIME_UOM(i),
1873 l_ranklist.TRIP_ID(i),
1874 l_ranklist.RANK_ID(i),
1875 l_ranklist.LANE_ID(i),
1876 l_ranklist.CARRIER_ID(i),
1877 l_ranklist.SERVICE_LEVEL(i),
1878 l_ranklist.MODE_OF_TRANSPORT(i),
1879 l_ranklist.VEHICLE_ORG_ID(i),
1880 l_ranklist.VEHICLE_ITEM_ID(i),
1881 l_ranklist.CONSIGNEE_CARRIER_AC_NO(i),
1882 l_ranklist.FREIGHT_TERMS_CODE(i),
1883 l_ranklist.SCHEDULE_ID(i),
1884 l_ranklist.ATTRIBUTE_CATEGORY(i),
1885 l_ranklist.ATTRIBUTE1(i),
1886 l_ranklist.ATTRIBUTE2(i),
1887 l_ranklist.ATTRIBUTE3(i),
1888 l_ranklist.ATTRIBUTE4(i),
1889 l_ranklist.ATTRIBUTE5(i),
1890 l_ranklist.ATTRIBUTE6(i),
1891 l_ranklist.ATTRIBUTE7(i),
1892 l_ranklist.ATTRIBUTE8(i),
1893 l_ranklist.ATTRIBUTE9(i),
1894 l_ranklist.ATTRIBUTE10(i),
1895 l_ranklist.ATTRIBUTE11(i),
1896 l_ranklist.ATTRIBUTE12(i),
1897 l_ranklist.ATTRIBUTE13(i),
1898 l_ranklist.ATTRIBUTE14(i),
1899 l_ranklist.ATTRIBUTE15(i)
1900 FROM wsh_trips wt
1901 WHERE trip_id = p_trip_id_tab(i);
1902 END LOOP;
1903
1904 IF l_debug_on THEN
1905 WSH_DEBUG_SV.log(l_module_name, 'l_ranklist.trip_id.COUNT',
1906 l_ranklist.trip_id.COUNT);
1907 WSH_DEBUG_SV.logmsg(l_module_name,
1908 'Calling FTE_CARRIER_RANK_LIST_PVT.CREATE_RANK_LIST_BULK',
1909 WSH_DEBUG_SV.C_PROC_LEVEL);
1910 END IF;
1911
1912 FTE_CARRIER_RANK_LIST_PVT.CREATE_RANK_LIST_BULK(
1913 p_api_version_number => p_api_version_number,
1914 p_init_msg_list => p_init_msg_list,
1915 x_return_status => x_return_status,
1916 x_msg_count => x_msg_count,
1917 x_msg_data => x_msg_data,
1918 p_ranklist => l_ranklist
1919 );
1920
1921 END IF;
1922
1923 IF l_debug_on THEN
1924 WSH_DEBUG_SV.log(l_module_name, 'x_return_status',
1925 x_return_status);
1926 WSH_DEBUG_SV.log(l_module_name, 'l_ranklist.trip_id.COUNT',
1927 l_ranklist.trip_id.count);
1928 WSH_DEBUG_SV.log(l_module_name, 'x_msg_count', x_msg_count);
1929 WSH_DEBUG_SV.log(l_module_name, 'x_msg_data', x_msg_data);
1930 WSH_DEBUG_SV.pop(l_module_name);
1931 END IF;
1932
1933 EXCEPTION
1934
1935 WHEN OTHERS THEN
1936
1937 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1938 wsh_util_core.default_handler(
1939 'WSH_FTE_INTEGRATION.CREATE_RANK_LIST_BULK',
1940 l_module_name);
1941
1942 IF l_debug_on THEN
1943 WSH_DEBUG_SV.logmsg(l_module_name,
1944 'Unexpected error has occured. Oracle error message is '
1945 || SQLERRM,
1946 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1947 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1948 END IF;
1949
1950
1951 END CREATE_RANK_LIST_BULK;
1952
1953
1954 END WSH_FTE_INTEGRATION;