[Home] [Help]
PACKAGE BODY: APPS.FTE_ACS_TRIP_PKG
Source
1 PACKAGE BODY FTE_ACS_TRIP_PKG as
2 /* $Header: FTEACSTB.pls 120.4 2005/12/12 02:59:41 alksharm noship $ */
3
4 --
5 G_PKG_NAME CONSTANT VARCHAR2(50) := 'FTE_ACS_TRIP_PKG';
6 --
7
8 --
9 -- ----------------------------------------------------------------------
10 -- Procedure: CARRIER_SEL_CREATE_TRIP
11 --
12 -- Parameters: p_delivery_id Delivery ID
13 -- p_carrier_sel_result_rec WSH_FTE_INTEGRATION.WSH_CS_RESULT_REC_TYPE
14 -- x_trip_id Trip Id
15 -- x_trip_name Trip Name
16 -- x_return_message Return Message
17 -- x_return_status Return Status
18 --
19 -- COMMENT : This procedure is called from Process Carrier Selection API
20 -- in order to create trip for deliveries not assigned to trips
21 --
22 -- It performs the following steps:
23 -- 01. Create trip.
24 -- 02. Create Pick Up and Drop Off Stops for trip created above
25 -- 03. Assign delivery to trip
26 --
27 -- ----------------------------------------------------------------------
28
29 PROCEDURE CARRIER_SEL_CREATE_TRIP( p_delivery_id IN NUMBER,
30 --p_initial_pickup_loc_id IN NUMBER,
31 --p_ultimate_dropoff_loc_id IN NUMBER,
32 --p_initial_pickup_date IN DATE,
33 --p_ultimate_dropoff_date IN DATE,
34 p_carrier_sel_result_rec IN WSH_FTE_INTEGRATION.WSH_CS_RESULT_REC_TYPE,
35 x_trip_id OUT NOCOPY NUMBER,
36 x_trip_name OUT NOCOPY VARCHAR2,
37 x_return_message OUT NOCOPY VARCHAR2,
38 x_return_status OUT NOCOPY VARCHAR2
39 )IS
40 l_trip_id NUMBER;
41 l_trip_name VARCHAR2(30);
42 l_return_status VARCHAR2(1);
43 l_msg_data VARCHAR2(2000);
44 l_msg_count NUMBER;
45 p_api_version_number NUMBER;
46 p_action_code VARCHAR2(10);
47 l_trip_in_rec WSH_TRIPS_GRP.tripInRecType;
48 l_stop_in_rec WSH_TRIP_STOPS_GRP.stopInRecType;
49 l_trip_info_tab WSH_TRIPS_PVT.Trip_Attr_Tbl_Type;
50 l_trip_out_rec_tab WSH_TRIPS_GRP.Trip_Out_Tab_Type;
51 l_pickup_stop_out_tab WSH_TRIP_STOPS_GRP.stop_out_tab_type;
52 l_pickup_rec_attr_tab WSH_TRIP_STOPS_PVT.Stop_Attr_Tbl_Type;
53 l_dropoff_stop_out_tab WSH_TRIP_STOPS_GRP.stop_out_tab_type;
54 l_dropoff_rec_attr_tab WSH_TRIP_STOPS_PVT.Stop_Attr_Tbl_Type;
55 l_pickup_stop_info WSH_TRIP_STOPS_PVT.Trip_Stop_Rec_Type;
56 l_dropoff_stop_info WSH_TRIP_STOPS_PVT.Trip_Stop_Rec_Type;
57 x_delivery_out_rec WSH_DELIVERIES_GRP.Delivery_Action_Out_Rec_Type;
58 l_defaults_rec WSH_DELIVERIES_GRP.default_parameters_rectype;
59 l_stop_wt_vol_out_tab WSH_TRIP_STOPS_GRP.Stop_Wt_Vol_tab_type;
60 l_action_prms WSH_DELIVERIES_GRP.action_parameters_rectype;
61 l_rec_attr_tab WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
62 K_CREATE CONSTANT VARCHAR2(30) := 'CREATE';
63 l_commit VARCHAR2(100) := FND_API.G_FALSE;
64 l_init_msg_list VARCHAR2(100) := FND_API.G_FALSE;
65 l_api_version_number NUMBER := 1.0;
66 --l_delivery_id_tab WSH_NEW_DELIVERY_ACTIONS.TableNumbers;
67 l_index NUMBER;
68 l_pickup_stop_seq NUMBER;
69 l_dropoff_stop_seq NUMBER;
70 l_initial_pickup_loc_id NUMBER;
71 l_ultimate_dropoff_loc_id NUMBER;
72 l_initial_pickup_date DATE;
73 l_ultimate_dropoff_date DATE;
74 l_dlvy_weight_uom VARCHAR2(10);
75 l_dlvy_volume_uom VARCHAR2(10);
76 l_ignore_for_planning wsh_trips.ignore_for_planning%TYPE;
77 l_caller VARCHAR2(30) := 'FTE_ROUTING_GUIDE';
78 l_debug_on BOOLEAN;
79 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CARRIER_SEL_CREATE_TRIP';
80
81 CURSOR c_dlvy_attr_csr( p_delivery_id IN NUMBER ) IS
82 SELECT
83 DELIVERY_ID
84 , ORGANIZATION_ID
85 , STATUS_CODE
86 , PLANNED_FLAG
87 , NAME
88 , INITIAL_PICKUP_DATE
89 , INITIAL_PICKUP_LOCATION_ID
90 , ULTIMATE_DROPOFF_LOCATION_ID
91 , ULTIMATE_DROPOFF_DATE
92 , CUSTOMER_ID
93 , INTMED_SHIP_TO_LOCATION_ID
94 , SHIP_METHOD_CODE
95 , DELIVERY_TYPE
96 , CARRIER_ID
97 , SERVICE_LEVEL
98 , MODE_OF_TRANSPORT
99 , shipment_direction
100 , party_id
101 , shipping_control
102 , weight_uom_code
103 , volume_uom_code
104 , nvl(ignore_for_planning,'N') ignore_for_planning
105 FROM WSH_NEW_DELIVERIES
106 WHERE delivery_id = p_delivery_id;
107
108 /*CURSOR c_dlvy_weight_volume_csr (p_delivery_id IN NUMBER) IS
109 SELECT weight_uom_code
110 ,volume_uom_code
111 FROM wsh_new_deliveries
112 WHERE delivery_id = p_delivery_id;*/
113 BEGIN
114 --{
115 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
116
117 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
118 --
119 IF l_debug_on IS NULL
120 THEN
121 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
122 END IF;
123 --
124 IF l_debug_on THEN
125 WSH_DEBUG_SV.push(l_module_name);
126 END IF;
127
128 l_initial_pickup_loc_id := p_carrier_sel_result_rec.initial_pickup_location_id;
129 l_ultimate_dropoff_loc_id := p_carrier_sel_result_rec.ultimate_dropoff_location_id;
130 l_initial_pickup_date := p_carrier_sel_result_rec.pickup_date;
131 l_ultimate_dropoff_date := p_carrier_sel_result_rec.dropoff_date;
132
133 IF l_debug_on THEN
134 WSH_DEBUG_SV.logmsg(l_module_name,'p_delivery_id :'||p_delivery_id);
135 WSH_DEBUG_SV.logmsg(l_module_name,'l_initial_pickup_loc_id :'||l_initial_pickup_loc_id);
136 WSH_DEBUG_SV.logmsg(l_module_name,'l_ultimate_dropoff_loc_id :'||l_ultimate_dropoff_loc_id);
137 WSH_DEBUG_SV.logmsg(l_module_name,'l_initial_pickup_date :'||l_initial_pickup_date);
138 WSH_DEBUG_SV.logmsg(l_module_name,'l_ultimate_dropoff_date :'||l_ultimate_dropoff_date);
139 END IF;
140
141 l_index := 1;
142
143 OPEN c_dlvy_attr_csr(p_delivery_id);
144
145 FETCH c_dlvy_attr_csr
146 INTO l_rec_attr_tab(l_index).delivery_id,
147 l_rec_attr_tab(l_index).organization_id,
148 l_rec_attr_tab(l_index).status_code,
149 l_rec_attr_tab(l_index).planned_flag,
150 l_rec_attr_tab(l_index).NAME,
151 l_rec_attr_tab(l_index).INITIAL_PICKUP_DATE,
152 l_rec_attr_tab(l_index).INITIAL_PICKUP_LOCATION_ID,
153 l_rec_attr_tab(l_index).ULTIMATE_DROPOFF_LOCATION_ID,
154 l_rec_attr_tab(l_index).ULTIMATE_DROPOFF_DATE,
155 l_rec_attr_tab(l_index).CUSTOMER_ID,
156 l_rec_attr_tab(l_index).INTMED_SHIP_TO_LOCATION_ID,
157 l_rec_attr_tab(l_index).SHIP_METHOD_CODE,
158 l_rec_attr_tab(l_index).DELIVERY_TYPE,
159 l_rec_attr_tab(l_index).CARRIER_ID,
160 l_rec_attr_tab(l_index).SERVICE_LEVEL,
161 l_rec_attr_tab(l_index).MODE_OF_TRANSPORT,
162 l_rec_attr_tab(l_index).shipment_direction,
163 l_rec_attr_tab(l_index).party_id,
164 l_rec_attr_tab(l_index).shipping_control,
165 l_dlvy_weight_uom,
166 l_dlvy_volume_uom,
167 l_ignore_for_planning;
168
169 CLOSE c_dlvy_attr_csr;
170
171 p_action_code := K_CREATE;
172 l_trip_in_rec.caller := l_caller;
173 l_pickup_stop_seq := 10;
174 l_dropoff_stop_seq := 20;
175 l_trip_in_rec.phase := NULL;
176 l_trip_in_rec.action_code := p_action_code;
177
178 -- 01. Create Trip
179
180 l_trip_info_tab(1).NAME := FND_API.G_MISS_CHAR;
181 l_trip_info_tab(1).PLANNED_FLAG := 'N';
182 l_trip_info_tab(1).ARRIVE_AFTER_TRIP_ID := FND_API.G_MISS_NUM;
183 l_trip_info_tab(1).STATUS_CODE := 'OP';
184 l_trip_info_tab(1).VEHICLE_ITEM_ID := FND_API.G_MISS_NUM;
185 l_trip_info_tab(1).VEHICLE_ORGANIZATION_ID := FND_API.G_MISS_NUM;
186 l_trip_info_tab(1).VEHICLE_NUMBER := FND_API.G_MISS_CHAR;
187 l_trip_info_tab(1).VEHICLE_NUM_PREFIX := FND_API.G_MISS_CHAR;
188
189 IF (p_carrier_sel_result_rec.carrier_id is null) THEN
190 l_trip_info_tab(1).CARRIER_ID := FND_API.G_MISS_NUM;
191 ELSE
192 l_trip_info_tab(1).CARRIER_ID := p_carrier_sel_result_rec.carrier_id;
193 END IF;
194
195 IF (p_carrier_sel_result_rec.ship_method_code is null) THEN
196 l_trip_info_tab(1).SHIP_METHOD_CODE := FND_API.G_MISS_CHAR;
197 ELSE
198 l_trip_info_tab(1).SHIP_METHOD_CODE := p_carrier_sel_result_rec.ship_method_code;
199 END IF;
200
201
202 IF (p_carrier_sel_result_rec.consignee_carrier_ac_no is null) THEN
203 l_trip_info_tab(1).CONSIGNEE_CARRIER_AC_NO := FND_API.G_MISS_CHAR;
204 ELSE
205 l_trip_info_tab(1).CONSIGNEE_CARRIER_AC_NO := p_carrier_sel_result_rec.consignee_carrier_ac_no;
206 END IF;
207
208 -- AG
209 -- cs_result_tab.rank stores rank_sequence
210 -- It is not same as trip's rank_id which has already been updated by FTE
211 -- rank_list_action API
212 l_trip_info_tab(1).RANK_ID := FND_API.G_MISS_NUM;
213
214 IF (p_carrier_sel_result_rec.append_flag is null) THEN
215 l_trip_info_tab(1).APPEND_FLAG := FND_API.G_MISS_CHAR;
216 ELSE
217 l_trip_info_tab(1).APPEND_FLAG := p_carrier_sel_result_rec.append_flag;
218 END IF;
219
220 -- AG Use rule_id from p_cs_result_tab and not routing_rule_id
221
222 IF (p_carrier_sel_result_rec.rule_id is null) THEN
223 l_trip_info_tab(1).ROUTING_RULE_ID := FND_API.G_MISS_NUM;
224 ELSE
225 l_trip_info_tab(1).ROUTING_RULE_ID := p_carrier_sel_result_rec.rule_id ;
226 END IF;
227
228 l_trip_info_tab(1).ROUTE_ID := FND_API.G_MISS_NUM;
229 l_trip_info_tab(1).ROUTING_INSTRUCTIONS := FND_API.G_MISS_CHAR;
230 l_trip_info_tab(1).ATTRIBUTE_CATEGORY := FND_API.G_MISS_CHAR;
231 l_trip_info_tab(1).ATTRIBUTE1 := FND_API.G_MISS_CHAR;
232 l_trip_info_tab(1).ATTRIBUTE2 := FND_API.G_MISS_CHAR;
233 l_trip_info_tab(1).ATTRIBUTE3 := FND_API.G_MISS_CHAR;
234 l_trip_info_tab(1).ATTRIBUTE4 := FND_API.G_MISS_CHAR;
235 l_trip_info_tab(1).ATTRIBUTE5 := FND_API.G_MISS_CHAR;
236 l_trip_info_tab(1).ATTRIBUTE6 := FND_API.G_MISS_CHAR;
237 l_trip_info_tab(1).ATTRIBUTE7 := FND_API.G_MISS_CHAR;
238 l_trip_info_tab(1).ATTRIBUTE8 := FND_API.G_MISS_CHAR;
239 l_trip_info_tab(1).ATTRIBUTE9 := FND_API.G_MISS_CHAR;
240 l_trip_info_tab(1).ATTRIBUTE10 := FND_API.G_MISS_CHAR;
241 l_trip_info_tab(1).ATTRIBUTE11 := FND_API.G_MISS_CHAR;
242 l_trip_info_tab(1).ATTRIBUTE12 := FND_API.G_MISS_CHAR;
243 l_trip_info_tab(1).ATTRIBUTE13 := FND_API.G_MISS_CHAR;
244 l_trip_info_tab(1).ATTRIBUTE14 := FND_API.G_MISS_CHAR;
245 l_trip_info_tab(1).ATTRIBUTE15 := FND_API.G_MISS_CHAR;
246 l_trip_info_tab(1).IGNORE_FOR_PLANNING := l_ignore_for_planning;
247 l_trip_info_tab(1).CREATION_DATE := SYSDATE;
248 l_trip_info_tab(1).CREATED_BY := fnd_global.user_id;
249 l_trip_info_tab(1).LAST_UPDATE_DATE := SYSDATE;
250 l_trip_info_tab(1).LAST_UPDATED_BY := fnd_global.user_id;
251 l_trip_info_tab(1).LAST_UPDATE_LOGIN := fnd_global.login_id;
252 l_trip_info_tab(1).PROGRAM_APPLICATION_ID := FND_API.G_MISS_NUM;
253 l_trip_info_tab(1).PROGRAM_ID := FND_API.G_MISS_NUM;
254 l_trip_info_tab(1).PROGRAM_UPDATE_DATE := FND_API.G_MISS_DATE;
255 l_trip_info_tab(1).REQUEST_ID := FND_API.G_MISS_NUM;
256
257 IF (p_carrier_sel_result_rec.service_level is null) THEN
258 l_trip_info_tab(1).SERVICE_LEVEL := FND_API.G_MISS_CHAR;
259 ELSE
260 l_trip_info_tab(1).SERVICE_LEVEL := p_carrier_sel_result_rec.service_level;
261 END IF;
262
263 IF (p_carrier_sel_result_rec.mode_of_transport is null) THEN
264 l_trip_info_tab(1).MODE_OF_TRANSPORT := FND_API.G_MISS_CHAR;
265 ELSE
266 l_trip_info_tab(1).MODE_OF_TRANSPORT := p_carrier_sel_result_rec.mode_of_transport;
267 END IF;
268
269 IF (p_carrier_sel_result_rec.freight_terms_code is null) THEN
270 l_trip_info_tab(1).FREIGHT_TERMS_CODE := FND_API.G_MISS_CHAR;
271 ELSE
272 l_trip_info_tab(1).FREIGHT_TERMS_CODE := p_carrier_sel_result_rec.freight_terms_code;
273 END IF;
274
275 l_trip_info_tab(1).CONSOLIDATION_ALLOWED := FND_API.G_MISS_CHAR;
276 l_trip_info_tab(1).LOAD_TENDER_STATUS := FND_API.G_MISS_CHAR;
277 l_trip_info_tab(1).ROUTE_LANE_ID := FND_API.G_MISS_NUM;
278 l_trip_info_tab(1).LANE_ID := FND_API.G_MISS_NUM;
279 l_trip_info_tab(1).SCHEDULE_ID := FND_API.G_MISS_NUM;
280 l_trip_info_tab(1).BOOKING_NUMBER := FND_API.G_MISS_CHAR;
281 l_trip_info_tab(1).ARRIVE_AFTER_TRIP_NAME := FND_API.G_MISS_CHAR;
282 l_trip_info_tab(1).SHIP_METHOD_NAME := FND_API.G_MISS_CHAR;
283 l_trip_info_tab(1).VEHICLE_ITEM_DESC := FND_API.G_MISS_CHAR;
284 l_trip_info_tab(1).VEHICLE_ORGANIZATION_CODE := FND_API.G_MISS_CHAR;
285
286 WSH_TRIPS_GRP.Create_Update_Trip(
287 p_api_version_number => l_api_version_number,
288 p_init_msg_list => l_init_msg_list,
289 p_commit => l_commit,
290 x_return_status => l_return_status,
291 x_msg_count => l_msg_count,
292 x_msg_data => l_msg_data,
293 p_trip_info_tab => l_trip_info_tab,
294 p_in_rec => l_trip_in_rec,
295 x_out_tab => l_trip_out_rec_tab);
296
297 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
298 raise FND_API.G_EXC_UNEXPECTED_ERROR;
299 END IF;
300
301 l_trip_id := l_trip_out_rec_tab(1).trip_id;
302 l_trip_name := l_trip_out_rec_tab(1).trip_name;
303
304 IF l_debug_on THEN
305 WSH_DEBUG_SV.logmsg(l_module_name,'Trip Created: l_trip_id :'||l_trip_id);
306 WSH_DEBUG_SV.logmsg(l_module_name,'Trip Created: l_trip_name :'||l_trip_name);
307 END IF;
308
309 IF l_initial_pickup_date IS NULL AND l_ultimate_dropoff_date IS NULL THEN
310 l_initial_pickup_date := sysdate;
311 l_ultimate_dropoff_date := l_initial_pickup_date + WSH_TRIPS_ACTIONS.C_TEN_MINUTES;
312 ELSIF l_initial_pickup_date IS NULL AND l_ultimate_dropoff_date IS NOT NULL THEN
313 l_initial_pickup_date := l_ultimate_dropoff_date - WSH_TRIPS_ACTIONS.C_TEN_MINUTES;
314 ELSIF l_initial_pickup_date IS NOT NULL AND l_ultimate_dropoff_date IS NULL THEN
315 l_ultimate_dropoff_date := l_initial_pickup_date + WSH_TRIPS_ACTIONS.C_TEN_MINUTES;
316 END IF;
317
318 IF l_debug_on THEN
319 WSH_DEBUG_SV.logmsg(l_module_name,'l_initial_pickup_date :'||l_initial_pickup_date);
320 WSH_DEBUG_SV.logmsg(l_module_name,'l_ultimate_dropoff_date :'||l_ultimate_dropoff_date);
321 END IF;
322
323 l_pickup_stop_info.TRIP_ID := l_trip_id;
324 l_pickup_stop_info.STOP_LOCATION_ID := l_initial_pickup_loc_id;
325 l_pickup_stop_info.planned_arrival_date := l_initial_pickup_date;
326 l_pickup_stop_info.planned_departure_date := l_initial_pickup_date;
327 l_pickup_stop_info.weight_uom_code := l_dlvy_weight_uom;
328 l_pickup_stop_info.volume_uom_code := l_dlvy_volume_uom;
329
330 l_pickup_rec_attr_tab(1):= l_pickup_stop_info;
331
332 l_stop_in_rec.action_code := p_action_code;
333 l_stop_in_rec.caller := l_caller;
334
335 -- Create Pick Up Stop
336
337 WSH_TRIP_STOPS_GRP.CREATE_UPDATE_STOP(
338 p_api_version_number => l_api_version_number,
339 p_init_msg_list => l_init_msg_list,
340 p_commit => l_commit,
341 p_in_rec => l_stop_in_rec,
342 p_rec_attr_tab => l_pickup_rec_attr_tab,
343 x_stop_out_tab => l_pickup_stop_out_tab,
344 x_return_status => l_return_status,
345 x_msg_count => l_msg_count,
346 x_msg_data => l_msg_data,
347 x_stop_wt_vol_out_tab => l_stop_wt_vol_out_tab);
348
349 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
350 raise FND_API.G_EXC_UNEXPECTED_ERROR;
351 END IF;
352
353 IF l_debug_on THEN
354 WSH_DEBUG_SV.logmsg(l_module_name,'Stop1: stop_id :'||l_pickup_stop_out_tab(1).stop_id);
355 END IF;
356
357 l_dropoff_stop_info.TRIP_ID := l_trip_id;
358 l_dropoff_stop_info.STOP_LOCATION_ID := l_ultimate_dropoff_loc_id ;
359 l_dropoff_stop_info.planned_arrival_date := l_ultimate_dropoff_date;
360 l_dropoff_stop_info.planned_departure_date := l_ultimate_dropoff_date;
361 l_dropoff_stop_info.weight_uom_code := l_dlvy_weight_uom;
362 l_dropoff_stop_info.volume_uom_code := l_dlvy_volume_uom;
363
364 l_dropoff_rec_attr_tab(1) := l_dropoff_stop_info;
365
366 -- Create Drop Off Stop
367
368 WSH_TRIP_STOPS_GRP.CREATE_UPDATE_STOP(
369 p_api_version_number => l_api_version_number,
370 p_init_msg_list => l_init_msg_list,
371 p_commit => l_commit,
372 p_in_rec => l_stop_in_rec,
373 p_rec_attr_tab => l_dropoff_rec_attr_tab,
374 x_stop_out_tab => l_dropoff_stop_out_tab,
375 x_return_status => l_return_status,
376 x_msg_count => l_msg_count,
377 x_msg_data => l_msg_data,
378 x_stop_wt_vol_out_tab => l_stop_wt_vol_out_tab);
379
380 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
381 raise FND_API.G_EXC_UNEXPECTED_ERROR;
382 END IF;
383
384 IF l_debug_on THEN
385 WSH_DEBUG_SV.logmsg(l_module_name,'Stop2: stop_id :'||l_dropoff_stop_out_tab(1).stop_id);
386 END IF;
387
388 l_action_prms.caller := l_caller;
389 l_action_prms.phase :=NULL ;
390
391 l_action_prms.action_code := 'ASSIGN-TRIP';
392 l_action_prms.trip_id := l_trip_id;
393 l_action_prms.trip_name := l_trip_name;
394 l_action_prms.pickup_stop_id := l_pickup_stop_out_tab(1).stop_id;
395 l_action_prms.pickup_loc_id := l_initial_pickup_loc_id;
396
397 l_action_prms.pickup_arr_date := l_pickup_stop_info.planned_arrival_date;
398 l_action_prms.pickup_dep_date := l_pickup_stop_info.planned_departure_date;
399
400
401 l_action_prms.dropoff_stop_id := l_dropoff_stop_out_tab(1).stop_id;
402 l_action_prms.dropoff_loc_id := l_ultimate_dropoff_loc_id;
403
404 l_action_prms.dropoff_arr_date := l_dropoff_stop_info.planned_arrival_date;
405 l_action_prms.dropoff_dep_date := l_dropoff_stop_info.planned_departure_date;
406 --l_delivery_id_tab(1) := p_delivery_id;
407
408 /*l_index := l_delivery_id_tab.FIRST;
409
410 while l_index is not null LOOP
411 --{
412 open c_dlvy_attr_csr(l_delivery_id_tab(l_index));
413
414 fetch c_dlvy_attr_csr
415 into l_rec_attr_tab(l_index).delivery_id,
416 l_rec_attr_tab(l_index).organization_id,
417 l_rec_attr_tab(l_index).status_code,
418 l_rec_attr_tab(l_index).planned_flag,
419 l_rec_attr_tab(l_index).NAME,
420 l_rec_attr_tab(l_index).INITIAL_PICKUP_DATE,
421 l_rec_attr_tab(l_index).INITIAL_PICKUP_LOCATION_ID,
422 l_rec_attr_tab(l_index).ULTIMATE_DROPOFF_LOCATION_ID,
423 l_rec_attr_tab(l_index).ULTIMATE_DROPOFF_DATE,
424 l_rec_attr_tab(l_index).CUSTOMER_ID,
425 l_rec_attr_tab(l_index).INTMED_SHIP_TO_LOCATION_ID,
426 l_rec_attr_tab(l_index).SHIP_METHOD_CODE,
427 l_rec_attr_tab(l_index).DELIVERY_TYPE,
428 l_rec_attr_tab(l_index).CARRIER_ID,
429 l_rec_attr_tab(l_index).SERVICE_LEVEL,
430 l_rec_attr_tab(l_index).MODE_OF_TRANSPORT,
431 l_rec_attr_tab(l_index).shipment_direction,
432 l_rec_attr_tab(l_index).party_id,
433 l_rec_attr_tab(l_index).shipping_control;
434
435 close c_dlvy_attr_csr;
436 l_index := l_delivery_id_tab.NEXT(l_index);
437 --}
438 END LOOP;*/
439
440 -- Assign Delivery to trip
441
442 WSH_DELIVERIES_GRP.Delivery_Action(
443 p_api_version_number => l_api_version_number,
444 p_init_msg_list => l_init_msg_list,
445 p_commit => l_commit,
446 p_action_prms => l_action_prms,
447 p_rec_attr_tab => l_rec_attr_tab,
448 x_delivery_out_rec => x_delivery_out_rec,
449 x_defaults_rec => l_defaults_rec,
450 x_return_status => l_return_status,
451 x_msg_count => l_msg_count,
452 x_msg_data => l_msg_data);
453
454 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
455 raise FND_API.G_EXC_UNEXPECTED_ERROR;
456 END IF;
457
458 x_trip_id := l_trip_id;
459 x_trip_name := l_trip_name;
460
461 IF l_debug_on THEN
462 WSH_DEBUG_SV.pop(l_module_name);
463 END IF;
464 --}
465 EXCEPTION
466
467 WHEN OTHERS THEN
468 wsh_util_core.default_handler('FTE_ACS_TRIP_PKG.CARRIER_SEL_CREATE_TRIP',l_module_name);
469
470 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
471
472 IF c_dlvy_attr_csr%ISOPEN THEN
473 CLOSE c_dlvy_attr_csr;
474 END IF;
475
476 IF l_debug_on THEN
477 WSH_DEBUG_SV.pop(l_module_name,'When Others');
478 END IF;
479
480 END CARRIER_SEL_CREATE_TRIP;
481
482 --
483 -- ----------------------------------------------------------------------
484 -- Procedure: GET_RANKED_RESULTS
485 --
486 -- Parameters: p_rule_id Rule ID
487 -- x_routing_results Ranked list of carriers,mode and service levels
488 -- x_return_status Return Status
489 --
490 -- COMMENT : The procedure queries FTE_SEL_RESULT_ASSIGNMENTS to return results for the given
491 -- rule id. The API returns does not return multileg results.
492 -- ----------------------------------------------------------------------
493 PROCEDURE GET_RANKED_RESULTS( p_rule_id IN NUMBER,
494 x_routing_results OUT NOCOPY FTE_CARRIER_RANK_LIST_PVT.carrier_rank_list_tbl_type,
495 x_return_status OUT NOCOPY VARCHAR2)
496 IS
497
498
499 CURSOR get_rule_name IS
500 SELECT name
501 FROM FTE_SEL_RULES
502 WHERE RULE_ID = p_rule_id;
503
504 l_result_tab FTE_ACS_CACHE_PKG.fte_cs_result_attr_tab;
505 itr NUMBER;
506 l_debug_on BOOLEAN;
507 l_rule_name VARCHAR2(30);
508 l_count NUMBER;
509
510 CS_MULTILEG_RESULT EXCEPTION;
511 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.'||G_PKG_NAME||'.'||'GET_RANKED_RESULTS';
512
513 BEGIN
514 --
515 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
516 --
517 IF l_debug_on IS NULL
518 THEN
519 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
520 END IF;
521
522 IF l_debug_on THEN
523 WSH_DEBUG_SV.push(l_module_name);
524 --
525 WSH_DEBUG_SV.log(l_module_name,'P_RULE_ID',P_RULE_ID);
526 END IF;
527
528 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
529
530 FTE_ACS_CACHE_PKG.GET_RESULTS_FOR_RULE( p_rule_id => p_rule_id,
531 x_result_tab => l_result_tab,
532 x_return_status => x_return_status);
533
534
535 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
536 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
537 raise FND_API.G_EXC_UNEXPECTED_ERROR;
538 END IF;
539 END IF;
540
541 itr := l_result_tab.FIRST;
542
543 IF (itr IS NOT NULL)THEN
544
545 IF (l_result_tab(itr).result_type='RANK') THEN
546 LOOP
547 l_count := x_routing_results.COUNT;
548 x_routing_results(l_count).rank_sequence := l_result_tab(itr).rank;
549 x_routing_results(l_count).carrier_id := l_result_tab(itr).carrier_id;
550 x_routing_results(l_count).service_level := l_result_tab(itr).service_level;
551 x_routing_results(l_count).mode_of_transport := l_result_tab(itr).mode_of_transport;
552 x_routing_results(l_count).consignee_carrier_ac_no := l_result_tab(itr).consignee_carrier_ac_no;
553 x_routing_results(l_count).freight_terms_code := l_result_tab(itr).freight_terms_code;
554
555 EXIT WHEN itr = l_result_tab.LAST;
556 itr := l_result_tab.NEXT(itr);
557 END LOOP;
558 ELSE
559 RAISE CS_MULTILEG_RESULT;
560 END IF ;
561 END IF;
562
563 IF l_debug_on THEN
564 WSH_DEBUG_SV.POP (l_module_name);
565 END IF;
566
567 EXCEPTION
568
569 WHEN CS_MULTILEG_RESULT THEN
570
571 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
572
573 OPEN get_rule_name;
574 FETCH get_rule_name INTO l_rule_name;
575 CLOSE get_rule_name;
576
577 --Routing Gide Rule RULE_NAME has changed, and the given Rule Detail cant be found.
578
579 FND_MESSAGE.SET_NAME('FTE','FTE_CS_INVALID_RULE');
580 FND_MESSAGE.SET_TOKEN('RULE_NAME',l_rule_name);
581 wsh_util_core.add_message(x_return_status);
582
583 IF l_debug_on THEN
584 WSH_DEBUG_SV.pop(l_module_name);
585 END IF;
586
587 WHEN others THEN
588
589 WSH_UTIL_CORE.default_handler('FTE_ACS_TRIP_PKG.GET_RANKED_RESULTS');
590 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
591
592 IF l_debug_on THEN
593 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
594 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
595 END IF;
596 --
597
598 END GET_RANKED_RESULTS;
599
600 END FTE_ACS_TRIP_PKG;