[Home] [Help]
PACKAGE BODY: APPS.FTE_CARRIER_RANK_LIST_PVT
Source
1 PACKAGE BODY FTE_CARRIER_RANK_LIST_PVT AS
2 /* $Header: FTECLTHB.pls 120.7 2005/08/05 16:05:30 hbhagava noship $ */
3
4 --===================
5 -- CONSTANTS
6 --===================
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'FTE_CARRIER_RANK_LIST_PVT';
8
9
10
11
12
13 PROCEDURE LOG(p_module_name VARCHAR2,
14 p_text VARCHAR2,
15 p_level VARCHAR2)
16 IS
17
18
19
20 BEGIN
21
22 WSH_DEBUG_SV.logmsg(p_module_name,p_text,p_level);
23
24 END LOG;
25
26
27 PROCEDURE UPDATE_RANK(
28 p_rank_info_rec IN carrier_rank_list_rec,
29 x_return_status OUT NOCOPY VARCHAR2
30 ) IS
31
32 BEGIN
33
34 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
35
36
37 UPDATE FTE_CARRIER_RANK_LIST SET
38 CARRIER_ID = decode(p_rank_info_rec.CARRIER_ID,NULL,CARRIER_ID,
39 FND_API.G_MISS_NUM,NULL,
40 p_rank_info_rec.CARRIER_ID),
41 SERVICE_LEVEL =decode(p_rank_info_rec.SERVICE_LEVEL,NULL,SERVICE_LEVEL,
42 FND_API.G_MISS_CHAR,NULL,
43 p_rank_info_rec.SERVICE_LEVEL),
44 MODE_OF_TRANSPORT = decode(p_rank_info_rec.MODE_OF_TRANSPORT,NULL,MODE_OF_TRANSPORT,
45 FND_API.G_MISS_CHAR,NULL,
46 p_rank_info_rec.MODE_OF_TRANSPORT),
47 LANE_ID = decode(p_rank_info_rec.LANE_ID,NULL,LANE_ID,
48 FND_API.G_MISS_NUM,NULL,
49 p_rank_info_rec.LANE_ID),
50 --SOURCE = decode(p_rank_info_rec.SOURCE,NULL,SOURCE,
51 -- FND_API.G_MISS_CHAR,NULL,
52 -- p_rank_info_rec.SOURCE),
53 ENABLED = decode(p_rank_info_rec.ENABLED,NULL,ENABLED,
54 FND_API.G_MISS_CHAR,NULL,
55 p_rank_info_rec.ENABLED),
56 ESTIMATED_RATE = decode(p_rank_info_rec.ESTIMATED_RATE,NULL,ESTIMATED_RATE,
57 FND_API.G_MISS_NUM,NULL,
58 p_rank_info_rec.ESTIMATED_RATE),
59 CURRENCY_CODE = decode(p_rank_info_rec.CURRENCY_CODE,NULL,CURRENCY_CODE,
60 FND_API.G_MISS_CHAR,NULL,
61 p_rank_info_rec.CURRENCY_CODE),
62 VEHICLE_ITEM_ID = decode(p_rank_info_rec.VEHICLE_ITEM_ID,NULL,VEHICLE_ITEM_ID,
63 FND_API.G_MISS_NUM,NULL,
64 p_rank_info_rec.VEHICLE_ITEM_ID),
65 VEHICLE_ORG_ID = decode(p_rank_info_rec.VEHICLE_ORG_ID,NULL,VEHICLE_ORG_ID,
66 FND_API.G_MISS_NUM,NULL,
67 p_rank_info_rec.VEHICLE_ORG_ID),
68 ESTIMATED_TRANSIT_TIME = decode(p_rank_info_rec.ESTIMATED_TRANSIT_TIME,NULL,ESTIMATED_TRANSIT_TIME,
69 FND_API.G_MISS_NUM,NULL,
70 p_rank_info_rec.ESTIMATED_TRANSIT_TIME),
71 TRANSIT_TIME_UOM = decode(p_rank_info_rec.TRANSIT_TIME_UOM,NULL,TRANSIT_TIME_UOM,
72 FND_API.G_MISS_CHAR,NULL,
73 p_rank_info_rec.TRANSIT_TIME_UOM),
74 VERSION = decode(p_rank_info_rec.VERSION,NULL,VERSION,
75 FND_API.G_MISS_CHAR,NULL,
76 p_rank_info_rec.VERSION),
77 CONSIGNEE_CARRIER_AC_NO = decode(p_rank_info_rec.CONSIGNEE_CARRIER_AC_NO,NULL,CONSIGNEE_CARRIER_AC_NO,
78 FND_API.G_MISS_CHAR,NULL,
79 p_rank_info_rec.CONSIGNEE_CARRIER_AC_NO),
80 FREIGHT_TERMS_CODE = decode(p_rank_info_rec.FREIGHT_TERMS_CODE,NULL,FREIGHT_TERMS_CODE,
81 FND_API.G_MISS_CHAR,NULL,
82 p_rank_info_rec.FREIGHT_TERMS_CODE),
83 CALL_RG_FLAG = decode(p_rank_info_rec.CALL_RG_FLAG,NULL,CALL_RG_FLAG,
84 FND_API.G_MISS_CHAR,NULL,
85 p_rank_info_rec.CALL_RG_FLAG),
86 ATTRIBUTE_CATEGORY = decode(p_rank_info_rec.ATTRIBUTE_CATEGORY,NULL,ATTRIBUTE_CATEGORY,
87 FND_API.G_MISS_CHAR,NULL,
88 p_rank_info_rec.ATTRIBUTE_CATEGORY),
89 ATTRIBUTE1 = decode(p_rank_info_rec.ATTRIBUTE1,NULL,ATTRIBUTE1,
90 FND_API.G_MISS_CHAR,NULL,
91 p_rank_info_rec.ATTRIBUTE1),
92 ATTRIBUTE2 = decode(p_rank_info_rec.ATTRIBUTE2,NULL,ATTRIBUTE2,
93 FND_API.G_MISS_CHAR,NULL,
94 p_rank_info_rec.ATTRIBUTE2),
95 ATTRIBUTE3 = decode(p_rank_info_rec.ATTRIBUTE3,NULL,ATTRIBUTE3,
96 FND_API.G_MISS_CHAR,NULL,
97 p_rank_info_rec.ATTRIBUTE3),
98 ATTRIBUTE4 = decode(p_rank_info_rec.ATTRIBUTE4,NULL,ATTRIBUTE4,
99 FND_API.G_MISS_CHAR,NULL,
100 p_rank_info_rec.ATTRIBUTE4),
101 ATTRIBUTE5 = decode(p_rank_info_rec.ATTRIBUTE5,NULL,ATTRIBUTE5,
102 FND_API.G_MISS_CHAR,NULL,
103 p_rank_info_rec.ATTRIBUTE5),
104 ATTRIBUTE6 = decode(p_rank_info_rec.ATTRIBUTE6,NULL,ATTRIBUTE6,
105 FND_API.G_MISS_CHAR,NULL,
106 p_rank_info_rec.ATTRIBUTE6),
107 ATTRIBUTE7 = decode(p_rank_info_rec.ATTRIBUTE7,NULL,ATTRIBUTE7,
108 FND_API.G_MISS_CHAR,NULL,
109 p_rank_info_rec.ATTRIBUTE7),
110 ATTRIBUTE8 = decode(p_rank_info_rec.ATTRIBUTE8,NULL,ATTRIBUTE8,
111 FND_API.G_MISS_CHAR,NULL,
112 p_rank_info_rec.ATTRIBUTE8),
113 ATTRIBUTE9 = decode(p_rank_info_rec.ATTRIBUTE9,NULL,ATTRIBUTE9,
114 FND_API.G_MISS_CHAR,NULL,
115 p_rank_info_rec.ATTRIBUTE9),
116 ATTRIBUTE10 = decode(p_rank_info_rec.ATTRIBUTE10,NULL,ATTRIBUTE10,
117 FND_API.G_MISS_CHAR,NULL,
118 p_rank_info_rec.ATTRIBUTE10),
119 ATTRIBUTE11 = decode(p_rank_info_rec.ATTRIBUTE11,NULL,ATTRIBUTE11,
120 FND_API.G_MISS_CHAR,NULL,
121 p_rank_info_rec.ATTRIBUTE11),
122 ATTRIBUTE12 = decode(p_rank_info_rec.ATTRIBUTE12,NULL,ATTRIBUTE12,
123 FND_API.G_MISS_CHAR,NULL,
124 p_rank_info_rec.ATTRIBUTE12),
125 ATTRIBUTE13 = decode(p_rank_info_rec.ATTRIBUTE13,NULL,ATTRIBUTE13,
126 FND_API.G_MISS_CHAR,NULL,
127 p_rank_info_rec.ATTRIBUTE13),
128 ATTRIBUTE14 = decode(p_rank_info_rec.ATTRIBUTE14,NULL,ATTRIBUTE14,
129 FND_API.G_MISS_CHAR,NULL,
130 p_rank_info_rec.ATTRIBUTE14),
131 ATTRIBUTE15 = decode(p_rank_info_rec.ATTRIBUTE15,NULL,ATTRIBUTE15,
132 FND_API.G_MISS_CHAR,NULL,
133 p_rank_info_rec.ATTRIBUTE15),
134 last_update_date = SYSDATE,
135 last_updated_by = FND_GLOBAL.USER_ID,
136 last_update_login = FND_GLOBAL.USER_ID
137 WHERE rank_id = p_rank_info_rec.rank_id;
138
139 IF (SQL%NOTFOUND) THEN
140 RAISE no_data_found;
141 END IF;
142
143 EXCEPTION
144 WHEN no_data_found THEN
145 FND_MESSAGE.SET_NAME('FTE','FTE_RANK_NOT_FOUND');
146 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
147 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
148 WHEN others THEN
149 wsh_util_core.default_handler('FTE_CARRIER_RANK_LIST_PVT.UPDATE');
150 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
151
152 END UPDATE_RANK;
153
154 PROCEDURE SET_RANK_CURRENT(
155 p_rank_info_rec IN carrier_rank_list_rec,
156 p_trip_id IN NUMBER,
157 x_return_status OUT NOCOPY VARCHAR2)
158 IS
159
160 l_api_name CONSTANT VARCHAR2(30) := 'SET_RANK_CURRENT';
161 l_api_version CONSTANT NUMBER := 1.0;
162 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
163 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || l_api_name;
164
165
166 l_return_status VARCHAR2(32767);
167 l_msg_count NUMBER;
168 l_msg_data VARCHAR2(32767);
169 l_number_of_warnings NUMBER;
170 l_number_of_errors NUMBER;
171
172 l_result_code VARCHAR2(32767);
173 l_rank_id NUMBER;
174
175 x_rank_list_rec carrier_rank_list_rec;
176 l_rank_list_rec carrier_rank_list_rec;
177
178 l_currentSet VARCHAR2(32767);
179 l_trip_id NUMBER;
180
181 l_trip_name VARCHAR2(32767);
182
183 --{Trip update parameters
184 p_trip_info_tab WSH_TRIPS_PVT.Trip_Attr_Tbl_Type;
185 p_trip_info WSH_TRIPS_PVT.Trip_Rec_Type;
186 p_trip_in_rec WSH_TRIPS_GRP.TripInRecType;
187 x_out_tab WSH_TRIPS_GRP.trip_Out_tab_type;
188 --}
189
190
191 CURSOR get_trip IS
192 SELECT trip_id INTO l_trip_id FROM FTE_CARRIER_RANK_LIST
193 WHERE TRIP_ID = p_trip_id
194 AND ROWNUM = 1;
195
196
197 BEGIN
198
199 SAVEPOINT SET_RANK_CURRENT_PUB;
200
201 IF l_debug_on THEN
202 WSH_DEBUG_SV.push(l_module_name);
203 END IF;
204
205 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
206
207
208 IF l_debug_on
209 THEN
210 Log(l_module_name,' Calling Update Rank to update version ' ,
211 WSH_DEBUG_SV.C_PROC_LEVEL);
212 END IF;
213
214 UPDATE_RANK(p_rank_info_rec => p_rank_info_rec,
215 x_return_status => l_return_status);
216 -- Update trip with this rank id
217
218 IF (l_return_status = 'E')
219 THEN
220 RAISE FND_API.G_EXC_ERROR;
221 ELSIF (l_return_status = 'U')
222 THEN
223 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
224 END IF;
225
226 -- Change this call with Shipping API
227
228 IF l_debug_on
229 THEN
230 Log(l_module_name,' Update trip with rank id ' || p_rank_info_rec.rank_id,
231 WSH_DEBUG_SV.C_PROC_LEVEL);
232 END IF;
233
234
235 FTE_MLS_WRAPPER.INITIALIZE_TRIP_REC(x_trip_info => p_trip_info);
236
237 -- Update trip information
238 p_trip_info.RANK_ID := p_rank_info_rec.rank_id;
239 p_trip_info.TRIP_ID := p_trip_id;
240
241 p_trip_info_tab(1) :=p_trip_info;
242 p_trip_in_rec.caller :='FTE_LOAD_TENDER';
243 p_trip_in_rec.phase :=NULL;
244 p_trip_in_rec.action_code :='UPDATE';
245
246 IF l_debug_on
247 THEN
248 WSH_DEBUG_SV.logmsg(l_module_name,' Before calling CREATE_UPDATE_TRIP ',
249 WSH_DEBUG_SV.C_PROC_LEVEL);
250 END IF;
251
252
253 WSH_INTERFACE_GRP.Create_Update_Trip
254 (
255 p_api_version_number =>1.0,
256 p_init_msg_list =>FND_API.G_FALSE,
257 p_commit =>FND_API.G_FALSE,
258 x_return_status =>l_return_status,
259 x_msg_count =>l_msg_count,
260 x_msg_data =>l_msg_data,
261 p_trip_info_tab =>p_trip_info_tab,
262 p_in_rec =>p_trip_in_rec,
263 x_out_tab =>x_out_tab
264 );
265
266 IF l_debug_on
267 THEN
268 WSH_DEBUG_SV.logmsg(l_module_name,' REturn value from Create update trip ' ||
269 l_return_status,
270 WSH_DEBUG_SV.C_PROC_LEVEL);
271 END IF;
272
273 wsh_util_core.api_post_call(
274 p_return_status =>l_return_status,
275 x_num_warnings =>l_number_of_warnings,
276 x_num_errors =>l_number_of_errors,
277 p_msg_data =>l_msg_data);
278
279
280 IF l_number_of_errors > 0
281 THEN
282 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
283 RAISE FND_API.G_EXC_ERROR;
284 ELSIF l_number_of_warnings > 0
285 THEN
286 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
287 ELSE
288 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
289 END IF;
290
291
292 IF l_debug_on THEN
293 WSH_DEBUG_SV.pop(l_module_name);
294 END IF;
295
296 EXCEPTION
297 WHEN FND_API.G_EXC_ERROR THEN
298 ROLLBACK TO SET_RANK_CURRENT_PUB;
299 x_return_status := FND_API.G_RET_STS_ERROR;
300 IF l_debug_on THEN
301 WSH_DEBUG_SV.pop(l_module_name);
302 END IF;
303
304 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
305 ROLLBACK TO SET_RANK_CURRENT_PUB;
306 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
307 IF l_debug_on THEN
308 WSH_DEBUG_SV.pop(l_module_name);
309 END IF;
310
311 WHEN OTHERS THEN
312 ROLLBACK TO SET_RANK_CURRENT_PUB;
313 WSH_UTIL_CORE.DEFAULT_HANDLER('FTE_CARRIER_RANK_LIST_PVT.SET_RANK_CURRENT');
314 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
315 IF l_debug_on THEN
316 WSH_DEBUG_SV.pop(l_module_name);
317 END IF;
318
319 END SET_RANK_CURRENT;
320
321
322
323 PROCEDURE DELETE_RANK_LIST(
324 p_trip_id IN NUMBER,
325 x_return_status OUT NOCOPY VARCHAR2
326 ) IS
327
328 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_RANK';
329 l_api_version CONSTANT NUMBER := 1.0;
330 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
331 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || l_api_name;
332
333
334 l_tender_status VARCHAR2(30);
335
336 l_trip_name VARCHAR2(32767);
337
338 l_return_status VARCHAR2(1);
339 l_msg_count NUMBER;
340 l_msg_data VARCHAR2(32767);
341 l_number_of_warnings NUMBER;
342 l_number_of_errors NUMBER;
343
344 --{Trip update parameters
345 p_trip_info_tab WSH_TRIPS_PVT.Trip_Attr_Tbl_Type;
346 p_trip_info WSH_TRIPS_PVT.Trip_Rec_Type;
347 p_trip_in_rec WSH_TRIPS_GRP.TripInRecType;
348 x_out_tab WSH_TRIPS_GRP.trip_Out_tab_type;
349 --}
350
351
352 CURSOR GET_TENDER_STATUS IS
353 SELECT LOAD_TENDER_STATUS FROM WSH_TRIPS
354 WHERE TRIP_ID = p_trip_id;
355
356 BEGIN
357
358 IF l_debug_on THEN
359 WSH_DEBUG_SV.push(l_module_name);
360 END IF;
361
362
363 x_return_Status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
364
365 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
366 l_number_of_warnings := 0;
367 l_number_of_errors := 0;
368
369
370 -- Check tender status of trip if it is tendered / accepted we cannot delete rank list
371
372 OPEN GET_TENDER_STATUS;
373 FETCH GET_TENDER_STATUS INTO l_tender_status;
374
375
376 IF (GET_TENDER_STATUS%NOTFOUND)
377 THEN
378 CLOSE GET_TENDER_STATUS;
379
380 FND_MESSAGE.SET_NAME('FTE','FTE_INVALID_TRIP');
381 FND_MESSAGE.SET_TOKEN('TRIP_ID', p_trip_id);
382 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
383 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
384 RAISE FND_API.G_EXC_ERROR;
385
386 END IF;
387
388 CLOSE GET_TENDER_STATUS;
389
390 IF (l_tender_status = FTE_TENDER_PVT.S_TENDERED OR
391 l_tender_status = FTE_TENDER_PVT.S_ACCEPTED OR
392 l_tender_status = FTE_TENDER_PVT.S_AUTO_ACCEPTED)
393 THEN
394
395 FND_MESSAGE.SET_NAME('FTE','FTE_CNT_DELETE_TENDER_CHECK');
396 FND_MESSAGE.SET_TOKEN('TENDER_STATUS',
397 WSH_UTIL_CORE.Get_Lookup_Meaning('WSH_TENDER_STATUS',
398 l_tender_status));
399 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
400 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
401 RAISE FND_API.G_EXC_ERROR;
402 END IF;
403
404
405 BEGIN
406
407 DELETE FROM fte_carrier_rank_list
408 WHERE trip_id = p_trip_id;
409
410
411 IF (SQL%NOTFOUND) THEN
412 RAISE NO_DATA_FOUND;
413 END IF;
414 EXCEPTION
415 WHEN no_data_found THEN
416 SELECT NAME INTO l_trip_name FROM WSH_TRIPS WHERE TRIP_ID = p_trip_id;
417 FND_MESSAGE.SET_NAME('FTE','FTE_RANK_TRIP_NOT_FOUND');
418 FND_MESSAGE.SET_TOKEN('TRIP_NAME', l_trip_name);
419 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
420 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
421 RAISE FND_API.G_EXC_ERROR;
422 END;
423
424
425 FTE_MLS_WRAPPER.INITIALIZE_TRIP_REC(x_trip_info => p_trip_info);
426
427 -- Update trip information
428 p_trip_info.RANK_ID := NULL;
429 --p_trip_info.APPEND_FLAG := 'N';
430 p_trip_info.TRIP_ID := p_trip_id;
431
432 p_trip_info_tab(1) :=p_trip_info;
433 p_trip_in_rec.caller :='FTE_LOAD_TENDER';
434 p_trip_in_rec.phase :=NULL;
435 p_trip_in_rec.action_code :='UPDATE';
436
437 IF l_debug_on
438 THEN
439 WSH_DEBUG_SV.logmsg(l_module_name,' Before calling CREATE_UPDATE_TRIP ',
440 WSH_DEBUG_SV.C_PROC_LEVEL);
441 END IF;
442
443
444 WSH_INTERFACE_GRP.Create_Update_Trip
445 (
446 p_api_version_number =>1.0,
447 p_init_msg_list =>FND_API.G_FALSE,
448 p_commit =>FND_API.G_FALSE,
449 x_return_status =>l_return_status,
450 x_msg_count =>l_msg_count,
451 x_msg_data =>l_msg_data,
452 p_trip_info_tab =>p_trip_info_tab,
453 p_in_rec =>p_trip_in_rec,
454 x_out_tab =>x_out_tab
455 );
456
457 IF l_debug_on
458 THEN
459 WSH_DEBUG_SV.logmsg(l_module_name,' REturn value from Create update trip ' ||
460 l_return_status,
461 WSH_DEBUG_SV.C_PROC_LEVEL);
462 END IF;
463
464 wsh_util_core.api_post_call(
465 p_return_status =>l_return_status,
466 x_num_warnings =>l_number_of_warnings,
467 x_num_errors =>l_number_of_errors,
468 p_msg_data =>l_msg_data);
469
470 IF l_number_of_errors > 0
471 THEN
472 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
473 RAISE FND_API.G_EXC_ERROR;
474 ELSIF l_number_of_warnings > 0
475 THEN
476 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
477 ELSE
478 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
479 END IF;
480
481 IF l_debug_on
482 THEN
483 Log(l_module_name,' Return value after deleting RANK LIST ' || x_return_status,
484 WSH_DEBUG_SV.C_PROC_LEVEL);
485 END IF;
486
487
488 IF l_debug_on THEN
489 WSH_DEBUG_SV.pop(l_module_name);
490 END IF;
491
492
493 EXCEPTION
494 WHEN FND_API.G_EXC_ERROR THEN
495 x_return_status := FND_API.G_RET_STS_ERROR;
496 IF l_debug_on
497 THEN
498 Log(l_module_name,' Return value from DELETE_RANK_LIST ' || x_return_status,
499 WSH_DEBUG_SV.C_PROC_LEVEL);
500 END IF;
501
502 IF l_debug_on THEN
503 WSH_DEBUG_SV.pop(l_module_name);
504 END IF;
505 WHEN others THEN
506 wsh_util_core.default_handler('FTE_CARRIER_RANK_LIST_PVT.DELETE_RANK_LIST');
507 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
508 IF l_debug_on THEN
509 WSH_DEBUG_SV.pop(l_module_name);
510 END IF;
511
512 END DELETE_RANK_LIST;
513
514 PROCEDURE CREATE_RANK(
515 p_rank_info_rec IN carrier_rank_list_rec,
516 p_derive_init_sm_config IN VARCHAR2,
517 x_rank_id OUT NOCOPY NUMBER,
518 x_return_status OUT NOCOPY VARCHAR2
519 ) IS
520
521 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_RANK';
522 l_api_version CONSTANT NUMBER := 1.0;
523 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
524 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || l_api_name;
525
526
527 CURSOR get_next_rank_id IS
528 SELECT fte_carrier_rank_list_s.nextval
529 FROM sys.dual;
530
531 l_temp_id NUMBER;
532 EMPTY_TRIP_ID EXCEPTION;
533 EMPTY_RANK_SEQUENCE EXCEPTION;
534
535 l_config VARCHAR(3);
536
537
538 BEGIN
539
540 IF l_debug_on THEN
541 WSH_DEBUG_SV.push(l_module_name);
542 END IF;
543
544
545 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
546
547
548 if (p_rank_info_rec.trip_id IS NULL or
549 p_rank_info_rec.trip_id = FND_API.G_MISS_NUM) then
550 RAISE EMPTY_TRIP_ID;
551 end if;
552
553
554 if (p_rank_info_rec.RANK_SEQUENCE IS null or
555 p_rank_info_rec.RANK_SEQUENCE = FND_API.G_MISS_NUM) then
556 RAISE EMPTY_RANK_SEQUENCE;
557 end if;
558
559 OPEN get_next_rank_id;
560 FETCH get_next_rank_id INTO x_rank_id;
561 CLOSE get_next_rank_id;
562
563
564 l_config := '';
565 Log(l_module_name,' Derive init sm config ' || p_derive_init_sm_config,
566 WSH_DEBUG_SV.C_PROC_LEVEL);
567
568
569 IF (p_derive_init_sm_config = FND_API.G_FALSE)
570 THEN
571 l_config := p_rank_info_rec.INITSMCONFIG;
572 Log(l_module_name,' l_config ' || l_config,
573 WSH_DEBUG_SV.C_PROC_LEVEL);
574
575 ELSE
576
577 IF (p_rank_info_rec.CARRIER_ID IS NOT NULL) THEN
578 l_config := 'C';
579 END IF;
580
581 IF (p_rank_info_rec.SERVICE_LEVEL IS NOT NULL) THEN
582 l_config := l_config || 'S';
583 END IF;
584
585 IF (p_rank_info_rec.MODE_OF_TRANSPORT IS NOT NULL) THEN
586 l_config := l_config || 'M';
587 END IF;
588 END IF;
589
590 IF l_debug_on
591 THEN
592
593 Log(l_module_name,' Creating Rank list Entry ' , WSH_DEBUG_SV.C_PROC_LEVEL);
594 Log(l_module_name,' p_rank_info_rec.TRIP_ID ' || p_rank_info_rec.TRIP_ID ,WSH_DEBUG_SV.C_PROC_LEVEL);
595 Log(l_module_name,' p_rank_info_rec.RANK_SEQUENCE ' || p_rank_info_rec.RANK_SEQUENCE,WSH_DEBUG_SV.C_PROC_LEVEL);
596 Log(l_module_name,' p_rank_info_rec.CARRIER_ID ' || p_rank_info_rec.CARRIER_ID,WSH_DEBUG_SV.C_PROC_LEVEL);
597 Log(l_module_name,' p_rank_info_rec.SERVICE_LEVEL ' || p_rank_info_rec.SERVICE_LEVEL,WSH_DEBUG_SV.C_PROC_LEVEL);
598 Log(l_module_name,' p_rank_info_rec.MODE_OF_TRANSPORT ' || p_rank_info_rec.MODE_OF_TRANSPORT,WSH_DEBUG_SV.C_PROC_LEVEL);
599 Log(l_module_name,' p_rank_info_rec.LANE_ID ' || p_rank_info_rec.LANE_ID,WSH_DEBUG_SV.C_PROC_LEVEL);
600 Log(l_module_name,' p_rank_info_rec.SOURCE ' || p_rank_info_rec.SOURCE,WSH_DEBUG_SV.C_PROC_LEVEL);
601 Log(l_module_name,' p_rank_info_rec.ESTIMATED_RATE ' || p_rank_info_rec.ESTIMATED_RATE,WSH_DEBUG_SV.C_PROC_LEVEL);
602 Log(l_module_name,' p_rank_info_rec.CURRENCY_CODE ' || p_rank_info_rec.CURRENCY_CODE,WSH_DEBUG_SV.C_PROC_LEVEL);
603 Log(l_module_name,' p_rank_info_rec.VEHICLE_ITEM_ID ' || p_rank_info_rec.VEHICLE_ITEM_ID,WSH_DEBUG_SV.C_PROC_LEVEL);
604 Log(l_module_name,' p_rank_info_rec.VEHICLE_ORG_ID ' || p_rank_info_rec.VEHICLE_ORG_ID,WSH_DEBUG_SV.C_PROC_LEVEL);
605 Log(l_module_name,' p_rank_info_rec.ESTIMATED_TRANSIT_TIME ' || p_rank_info_rec.ESTIMATED_TRANSIT_TIME,WSH_DEBUG_SV.C_PROC_LEVEL);
606 Log(l_module_name,' p_rank_info_rec.TRANSIT_TIME_UOM ' || p_rank_info_rec.TRANSIT_TIME_UOM,WSH_DEBUG_SV.C_PROC_LEVEL);
607 Log(l_module_name,' p_rank_info_rec.CONSIGNEE_CARRIER_AC_NO ' || p_rank_info_rec.CONSIGNEE_CARRIER_AC_NO,WSH_DEBUG_SV.C_PROC_LEVEL);
608 Log(l_module_name,' p_rank_info_rec.FREIGHT_TERMS_CODE ' || p_rank_info_rec.FREIGHT_TERMS_CODE,WSH_DEBUG_SV.C_PROC_LEVEL);
609 Log(l_module_name,' l_config ' || l_config ,WSH_DEBUG_SV.C_PROC_LEVEL);
610 Log(l_module_name,' p_rank_info_rec.CALL_RG_FLAG ' || p_rank_info_rec.CALL_RG_FLAG,WSH_DEBUG_SV.C_PROC_LEVEL);
611 END IF;
612
613
614 insert into FTE_CARRIER_RANK_LIST
615 (
616 RANK_ID ,
617 TRIP_ID ,
618 RANK_SEQUENCE ,
619 CARRIER_ID ,
620 SERVICE_LEVEL ,
621 MODE_OF_TRANSPORT ,
622 LANE_ID ,
623 SOURCE ,
624 ENABLED ,
625 ESTIMATED_RATE ,
626 CURRENCY_CODE ,
627 VEHICLE_ITEM_ID ,
628 VEHICLE_ORG_ID ,
629 ESTIMATED_TRANSIT_TIME ,
630 TRANSIT_TIME_UOM ,
631 VERSION ,
632 CONSIGNEE_CARRIER_AC_NO ,
633 FREIGHT_TERMS_CODE ,
634 INITSMCONFIG ,
635 CALL_RG_FLAG ,
636 ATTRIBUTE_CATEGORY ,
637 ATTRIBUTE1 ,
638 ATTRIBUTE2 ,
639 ATTRIBUTE3 ,
640 ATTRIBUTE4 ,
641 ATTRIBUTE5 ,
642 ATTRIBUTE6 ,
643 ATTRIBUTE7 ,
644 ATTRIBUTE8 ,
645 ATTRIBUTE9 ,
646 ATTRIBUTE10 ,
647 ATTRIBUTE11 ,
648 ATTRIBUTE12 ,
649 ATTRIBUTE13 ,
650 ATTRIBUTE14 ,
651 ATTRIBUTE15 ,
652 CREATION_DATE ,
653 CREATED_BY ,
654 LAST_UPDATE_DATE ,
655 LAST_UPDATED_BY ,
656 LAST_UPDATE_LOGIN
657 )
658 values
659 (
660 x_rank_id,
661 p_rank_info_rec.TRIP_ID,
662 p_rank_info_rec.RANK_SEQUENCE,
663 decode(p_rank_info_rec.CARRIER_ID,FND_API.G_MISS_NUM,NULL,p_rank_info_rec.CARRIER_ID),
664 decode(p_rank_info_rec.SERVICE_LEVEL,FND_API.G_MISS_CHAR,NULL,p_rank_info_rec.SERVICE_LEVEL),
665 decode(p_rank_info_rec.MODE_OF_TRANSPORT,FND_API.G_MISS_CHAR,NULL,p_rank_info_rec.MODE_OF_TRANSPORT),
666 decode(p_rank_info_rec.LANE_ID,FND_API.G_MISS_NUM,NULL,p_rank_info_rec.LANE_ID),
667 decode(p_rank_info_rec.SOURCE,FND_API.G_MISS_CHAR,NULL,p_rank_info_rec.SOURCE),
668 'Y',
669 decode(p_rank_info_rec.ESTIMATED_RATE,FND_API.G_MISS_NUM,NULL,p_rank_info_rec.ESTIMATED_RATE),
670 decode(p_rank_info_rec.CURRENCY_CODE,FND_API.G_MISS_CHAR,NULL,p_rank_info_rec.CURRENCY_CODE),
671 decode(p_rank_info_rec.VEHICLE_ITEM_ID,FND_API.G_MISS_NUM,NULL,p_rank_info_rec.VEHICLE_ITEM_ID),
672 decode(p_rank_info_rec.VEHICLE_ORG_ID,FND_API.G_MISS_NUM,NULL,p_rank_info_rec.VEHICLE_ORG_ID),
673 decode(p_rank_info_rec.ESTIMATED_TRANSIT_TIME,FND_API.G_MISS_NUM,NULL,p_rank_info_rec.ESTIMATED_TRANSIT_TIME),
674
675 decode(p_rank_info_rec.TRANSIT_TIME_UOM,FND_API.G_MISS_CHAR,NULL,p_rank_info_rec.TRANSIT_TIME_UOM),
676 0,
677
678 decode(p_rank_info_rec.CONSIGNEE_CARRIER_AC_NO,FND_API.G_MISS_CHAR,NULL,p_rank_info_rec.CONSIGNEE_CARRIER_AC_NO),
679 decode(p_rank_info_rec.FREIGHT_TERMS_CODE,FND_API.G_MISS_CHAR,NULL,p_rank_info_rec.FREIGHT_TERMS_CODE),
680 l_config,
681 decode(p_rank_info_rec.CALL_RG_FLAG,FND_API.G_MISS_CHAR,NULL,p_rank_info_rec.CALL_RG_FLAG),
682 decode(p_rank_info_rec.ATTRIBUTE_CATEGORY,FND_API.G_MISS_CHAR,NULL,p_rank_info_rec.ATTRIBUTE_CATEGORY),
683 decode(p_rank_info_rec.ATTRIBUTE1,FND_API.G_MISS_CHAR,NULL,p_rank_info_rec.ATTRIBUTE1),
684 decode(p_rank_info_rec.ATTRIBUTE2,FND_API.G_MISS_CHAR,NULL,p_rank_info_rec.ATTRIBUTE2),
685 decode(p_rank_info_rec.ATTRIBUTE3,FND_API.G_MISS_CHAR,NULL,p_rank_info_rec.ATTRIBUTE3),
686 decode(p_rank_info_rec.ATTRIBUTE4,FND_API.G_MISS_CHAR,NULL,p_rank_info_rec.ATTRIBUTE4),
687 decode(p_rank_info_rec.ATTRIBUTE5,FND_API.G_MISS_CHAR,NULL,p_rank_info_rec.ATTRIBUTE5),
688 decode(p_rank_info_rec.ATTRIBUTE6,FND_API.G_MISS_CHAR,NULL,p_rank_info_rec.ATTRIBUTE6),
689 decode(p_rank_info_rec.ATTRIBUTE7,FND_API.G_MISS_CHAR,NULL,p_rank_info_rec.ATTRIBUTE7),
690 decode(p_rank_info_rec.ATTRIBUTE8,FND_API.G_MISS_CHAR,NULL,p_rank_info_rec.ATTRIBUTE8),
691 decode(p_rank_info_rec.ATTRIBUTE9,FND_API.G_MISS_CHAR,NULL,p_rank_info_rec.ATTRIBUTE9),
692 decode(p_rank_info_rec.ATTRIBUTE10,FND_API.G_MISS_CHAR,NULL,p_rank_info_rec.ATTRIBUTE10),
693 decode(p_rank_info_rec.ATTRIBUTE11,FND_API.G_MISS_CHAR,NULL,p_rank_info_rec.ATTRIBUTE11),
694 decode(p_rank_info_rec.ATTRIBUTE12,FND_API.G_MISS_CHAR,NULL,p_rank_info_rec.ATTRIBUTE12),
695 decode(p_rank_info_rec.ATTRIBUTE13,FND_API.G_MISS_CHAR,NULL,p_rank_info_rec.ATTRIBUTE13),
696 decode(p_rank_info_rec.ATTRIBUTE14,FND_API.G_MISS_CHAR,NULL,p_rank_info_rec.ATTRIBUTE14),
697 decode(p_rank_info_rec.ATTRIBUTE15,FND_API.G_MISS_CHAR,NULL,p_rank_info_rec.ATTRIBUTE15),
698
699 decode(p_rank_info_rec.creation_date,NULL,SYSDATE,FND_API.G_MISS_DATE,SYSDATE,p_rank_info_rec.creation_date),
700 decode(p_rank_info_rec.created_by,NULL,FND_GLOBAL.USER_ID,FND_API.G_MISS_NUM,FND_GLOBAL.USER_ID,p_rank_info_rec.created_by),
701 decode(p_rank_info_rec.last_update_date,NULL,SYSDATE,FND_API.G_MISS_DATE,SYSDATE, p_rank_info_rec.last_update_date),
702 decode(p_rank_info_rec.last_updated_by,NULL,FND_GLOBAL.USER_ID,FND_API.G_MISS_NUM,FND_GLOBAL.USER_ID,p_rank_info_rec.last_updated_by),
703 decode(p_rank_info_rec.last_update_login,NULL,FND_GLOBAL.USER_ID,FND_API.G_MISS_NUM,FND_GLOBAL.LOGIN_ID,p_rank_info_rec.last_update_login)
704 );
705
706 IF l_debug_on THEN
707 WSH_DEBUG_SV.pop(l_module_name);
708 END IF;
709
710
711 EXCEPTION
712 WHEN EMPTY_TRIP_ID THEN
713 FND_MESSAGE.SET_NAME('FTE', 'FTE_RANK_TRIP_ID_MISSING');
714 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
715 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
716 WHEN EMPTY_RANK_SEQUENCE THEN
717 FND_MESSAGE.SET_NAME('FTE', 'FTE_RANK_SEQUENCE_MISSING');
718 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
719 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
720 WHEN others THEN
721 wsh_util_core.default_handler('FTE_CARRIER_RANK_LIST_PVT.CREATE');
722 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
723 END CREATE_RANK;
724
725
726 PROCEDURE CREATE_RANK_LIST(
727 p_ranklist IN OUT NOCOPY carrier_rank_list_tbl_type,
728 p_trip_id IN NUMBER,
729 x_return_status OUT NOCOPY VARCHAR2
730 ) IS
731
732 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_RANK_LIST';
733 l_api_version CONSTANT NUMBER := 1.0;
734 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
735 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || l_api_name;
736
737
738 l_return_status VARCHAR2(32767);
739 l_msg_count NUMBER;
740 l_msg_data VARCHAR2(32767);
741 l_number_of_warnings NUMBER;
742 l_number_of_errors NUMBER;
743
744 l_result_code VARCHAR2(32767);
745 l_rank_id NUMBER;
746
747 x_rank_list_rec carrier_rank_list_rec;
748 l_rank_list_rec carrier_rank_list_rec;
749
750 l_currentSet VARCHAR2(32767);
751 l_trip_id NUMBER;
752
753 l_trip_name VARCHAR2(32767);
754
755 --{Trip update parameters
756 p_trip_info_tab WSH_TRIPS_PVT.Trip_Attr_Tbl_Type;
757 p_trip_info WSH_TRIPS_PVT.Trip_Rec_Type;
758 p_trip_in_rec WSH_TRIPS_GRP.TripInRecType;
759 x_out_tab WSH_TRIPS_GRP.trip_Out_tab_type;
760 --}
761
762
763 CURSOR get_trip IS
764 SELECT trip_id INTO l_trip_id FROM FTE_CARRIER_RANK_LIST
765 WHERE TRIP_ID = p_trip_id
766 AND ROWNUM = 1;
767
768
769 BEGIN
770
771 SAVEPOINT CREATE_RANK_LIST_PUB;
772
773 IF l_debug_on THEN
774 WSH_DEBUG_SV.push(l_module_name);
775 END IF;
776
777 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
778
779 --{
780 IF l_debug_on
781 THEN
782 Log(l_module_name,' Creating Rank List for trip ' || p_trip_id,
783 WSH_DEBUG_SV.C_PROC_LEVEL);
784 END IF;
785
786 -- Check if rank list exists for tis trip
787
788 OPEN get_trip;
789 FETCH get_trip INTO l_trip_id;
790
791 IF (get_trip%NOTFOUND) THEN
792 CLOSE get_trip;
793 ELSE
794 CLOSE get_trip;
795
796 SELECT NAME INTO l_trip_name FROM WSH_TRIPS WHERE TRIP_ID = p_trip_id;
797
798 FND_MESSAGE.SET_NAME('FTE','FTE_TRIP_LIST_EXISTS');
799 FND_MESSAGE.SET_TOKEN('TRIP_NAME',l_trip_name);
800 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
801 RAISE FND_API.G_EXC_ERROR;
802 END IF;
803
804 l_currentSet := FND_API.G_FALSE;
805
806 FOR i IN p_ranklist.FIRST..p_ranklist.LAST LOOP
807
808 l_rank_list_rec := p_ranklist(i);
809 l_rank_list_rec.trip_id := p_trip_id;
810
811 CREATE_RANK(p_rank_info_rec => l_rank_list_rec,
812 p_derive_init_sm_config => FND_API.G_TRUE,
813 x_rank_id => l_rank_id,
814 x_return_status => l_return_status);
815
816
817 IF (l_return_status = 'E')
818 THEN
819 RAISE FND_API.G_EXC_ERROR;
820 ELSIF (l_return_status = 'U')
821 THEN
822 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
823 END IF;
824
825 p_ranklist(i).rank_id := l_rank_id;
826
827 IF l_debug_on
828 THEN
829 Log(l_module_name,' Rank Id ' || l_rank_id,
830 WSH_DEBUG_SV.C_PROC_LEVEL);
831 Log(l_module_name,' IS CURRENT ' || p_ranklist(i).IS_CURRENT,
832 WSH_DEBUG_SV.C_PROC_LEVEL);
833 Log(l_module_name,' Return value ' || p_ranklist(i).IS_CURRENT,
834 WSH_DEBUG_SV.C_PROC_LEVEL);
835
836 END IF;
837
838
839 IF (p_ranklist(i).IS_CURRENT = 'Y') THEN
840
841 IF (l_currentSet = FND_API.G_TRUE) THEN
842 SELECT NAME INTO l_trip_name FROM WSH_TRIPS WHERE TRIP_ID = p_trip_id;
843
844 FND_MESSAGE.SET_NAME('FTE','FTE_CANNOT_SET_MORE_CURR');
845 FND_MESSAGE.SET_TOKEN('TRIP_NAME',l_trip_name);
846 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
847 RAISE FND_API.G_EXC_ERROR;
848 END IF;
849
850 l_currentSet := FND_API.G_TRUE;
851 p_ranklist(i).VERSION := 1;
852
853
854 IF l_debug_on
855 THEN
856 Log(l_module_name,' Calling SET RANK CURRENT API ',
857 WSH_DEBUG_SV.C_PROC_LEVEL);
858 END IF;
859
860
861 SET_RANK_CURRENT(p_rank_info_rec => p_ranklist(i),
862 p_trip_id => p_trip_id,
863 x_return_status => l_return_status);
864
865 IF l_debug_on
866 THEN
867 Log(l_module_name,' After calling SET_RANK_CURRENT ' || l_return_status,
868 WSH_DEBUG_SV.C_PROC_LEVEL);
869 END IF;
870
871
872
873 IF (l_return_status = 'E')
874 THEN
875 RAISE FND_API.G_EXC_ERROR;
876 ELSIF (l_return_status = 'U')
877 THEN
878 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
879 END IF;
880
881 -- Change this call with Shipping API
882
883 wsh_util_core.api_post_call(
884 p_return_status =>l_return_status,
885 x_num_warnings =>l_number_of_warnings,
886 x_num_errors =>l_number_of_errors,
887 p_msg_data =>l_msg_data);
888
889
890 IF l_number_of_errors > 0
891 THEN
892 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
893 RAISE FND_API.G_EXC_ERROR;
894 ELSIF l_number_of_warnings > 0
895 THEN
896 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
897 ELSE
898 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
899 END IF;
900
901
902 END IF;
903
904 END LOOP;
905
906 IF l_debug_on THEN
907 WSH_DEBUG_SV.pop(l_module_name);
908 END IF;
909
910 EXCEPTION
911 WHEN FND_API.G_EXC_ERROR THEN
912 ROLLBACK TO CREATE_RANK_LIST_PUB;
913 x_return_status := FND_API.G_RET_STS_ERROR;
914 IF l_debug_on THEN
915 WSH_DEBUG_SV.pop(l_module_name);
916 END IF;
917
918 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
919 ROLLBACK TO CREATE_RANK_LIST_PUB;
920 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
921 IF l_debug_on THEN
922 WSH_DEBUG_SV.pop(l_module_name);
923 END IF;
924
925 WHEN OTHERS THEN
926 ROLLBACK TO CREATE_RANK_LIST_PUB;
927 WSH_UTIL_CORE.DEFAULT_HANDLER('FTE_CARRIER_RANK_LIST_PVT.CREATE_RANK_LIST');
928 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
929 IF l_debug_on THEN
930 WSH_DEBUG_SV.pop(l_module_name);
931 END IF;
932
933 END CREATE_RANK_LIST;
934
935
936 PROCEDURE UPDATE_RANK_LIST(
937 p_ranklist IN OUT NOCOPY carrier_rank_list_tbl_type,
938 p_trip_id IN NUMBER,
939 x_return_status OUT NOCOPY VARCHAR2
940 ) IS
941
942 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RANK_LIST';
943 l_api_version CONSTANT NUMBER := 1.0;
944 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
945 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || l_api_name;
946
947
948 l_return_status VARCHAR2(32767);
949 l_msg_count NUMBER;
950 l_msg_data VARCHAR2(32767);
951 l_number_of_warnings NUMBER;
952 l_number_of_errors NUMBER;
953
954 l_result_code VARCHAR2(32767);
955 l_rank_id NUMBER;
956
957 x_rank_list_rec carrier_rank_list_rec;
958
959 l_currentSet VARCHAR2(32767);
960 l_trip_id NUMBER;
961 l_trip_name VARCHAR2(32767);
962
963 CURSOR get_trip IS
964 SELECT trip_id INTO l_trip_id FROM FTE_CARRIER_RANK_LIST
965 WHERE TRIP_ID = p_trip_id
966 AND ROWNUM = 1;
967
968
969 BEGIN
970
971 SAVEPOINT UPDATE_RANK_LIST_PUB;
972
973 IF l_debug_on THEN
974 WSH_DEBUG_SV.push(l_module_name);
975 END IF;
976
977 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
978
979 --{
980 IF l_debug_on
981 THEN
982 Log(l_module_name,' Updating Rank List for trip ' || p_trip_id,
983 WSH_DEBUG_SV.C_PROC_LEVEL);
984 END IF;
985
986 -- Check if rank list exists for tis trip
987
988 OPEN get_trip;
989 FETCH get_trip INTO l_trip_id;
990
991 IF (get_trip%NOTFOUND) THEN
992 CLOSE get_trip;
993
994 SELECT NAME INTO l_trip_name FROM WSH_TRIPS WHERE TRIP_ID = p_trip_id;
995
996 FND_MESSAGE.SET_NAME('FTE','FTE_TRIP_LIST_NOTEXISTS');
997 FND_MESSAGE.SET_TOKEN('TRIP_NAME',l_trip_name);
998 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
999 RAISE FND_API.G_EXC_ERROR;
1000 END IF;
1001
1002 CLOSE get_trip;
1003
1004 IF (p_ranklist.COUNT > 0)
1005 THEN
1006 --{
1007
1008 FOR i IN p_ranklist.FIRST..p_ranklist.LAST LOOP
1009
1010 IF l_debug_on
1011 THEN
1012 Log(l_module_name,' Updating Rank id ' || p_ranklist(i).RANK_ID,
1013 WSH_DEBUG_SV.C_PROC_LEVEL);
1014 END IF;
1015
1016
1017 UPDATE_RANK(p_rank_info_rec => p_ranklist(i),
1018 x_return_status => l_return_status);
1019
1020 IF (x_return_status = 'E')
1021 THEN
1022 RAISE FND_API.G_EXC_ERROR;
1023 ELSIF (x_return_status = 'U')
1024 THEN
1025 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1026 END IF;
1027
1028 --x_rank_list_rec := p_ranklist(i);
1029
1030 --x_ranklist(i) := x_rank_list_rec;
1031
1032 END LOOP;
1033 --}
1034 END IF;
1035
1036 IF l_debug_on THEN
1037 WSH_DEBUG_SV.pop(l_module_name);
1038 END IF;
1039
1040 EXCEPTION
1041 WHEN FND_API.G_EXC_ERROR THEN
1042 ROLLBACK TO UPDATE_RANK_LIST_PUB;
1043 x_return_status := FND_API.G_RET_STS_ERROR;
1044 IF l_debug_on THEN
1045 WSH_DEBUG_SV.pop(l_module_name);
1046 END IF;
1047
1048 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1049 ROLLBACK TO UPDATE_RANK_LIST_PUB;
1050 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1051 IF l_debug_on THEN
1052 WSH_DEBUG_SV.pop(l_module_name);
1053 END IF;
1054
1055 WHEN OTHERS THEN
1056 ROLLBACK TO UPDATE_RANK_LIST_PUB;
1057 WSH_UTIL_CORE.DEFAULT_HANDLER('FTE_CARRIER_RANK_LIST_PVT.UPDATE_RANK_LIST');
1058 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1059 IF l_debug_on THEN
1060 WSH_DEBUG_SV.pop(l_module_name);
1061 END IF;
1062
1063 END UPDATE_RANK_LIST;
1064
1065
1066
1067
1068 PROCEDURE APPEND_RANK_LIST(
1069 p_ranklist IN OUT NOCOPY carrier_rank_list_tbl_type,
1070 p_trip_id IN NUMBER,
1071 x_return_status OUT NOCOPY VARCHAR2
1072 ) IS
1073
1074 l_api_name CONSTANT VARCHAR2(30) := 'APPEND_RANK_LIST';
1075 l_api_version CONSTANT NUMBER := 1.0;
1076 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
1077 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || l_api_name;
1078
1079
1080 l_return_status VARCHAR2(32767);
1081 l_msg_count NUMBER;
1082 l_msg_data VARCHAR2(32767);
1083 l_number_of_warnings NUMBER;
1084 l_number_of_errors NUMBER;
1085
1086 l_result_code VARCHAR2(32767);
1087 l_rank_id NUMBER;
1088
1089
1090 l_rank_list_rec carrier_rank_list_rec;
1091
1092 l_max_rank_seq NUMBER;
1093 idx NUMBER;
1094
1095 l_currentSet VARCHAR2(32767);
1096 l_trip_name VARCHAR2(32767);
1097
1098
1099 CURSOR get_max_seq IS
1100 SELECT MAX(RANK_SEQUENCE)
1101 FROM FTE_CARRIER_RANK_LIST
1102 WHERE TRIP_ID = p_trip_id;
1103
1104
1105 BEGIN
1106
1107 SAVEPOINT APPEND_RANK_LIST_PUB;
1108 IF l_debug_on THEN
1109 WSH_DEBUG_SV.push(l_module_name);
1110 END IF;
1111
1112
1113 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1114 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1115 l_number_of_warnings := 0;
1116 l_number_of_errors := 0;
1117
1118 --{
1119 IF l_debug_on
1120 THEN
1121 Log(l_module_name,' Append Rank List for trip ' || p_trip_id,
1122 WSH_DEBUG_SV.C_PROC_LEVEL);
1123 END IF;
1124
1125 SELECT MAX(RANK_SEQUENCE) INTO l_max_rank_seq
1126 FROM FTE_CARRIER_RANK_LIST
1127 WHERE TRIP_ID = p_trip_id;
1128
1129 IF (l_max_rank_seq IS NULL) THEN
1130 FND_MESSAGE.SET_NAME('FTE','FTE_TRIP_LIST_NOTEXISTS');
1131 FND_MESSAGE.SET_TOKEN('TRIP_ID',p_trip_id);
1132 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
1133 RAISE FND_API.G_EXC_ERROR;
1134 END IF;
1135
1136
1137 IF l_debug_on
1138 THEN
1139 Log(l_module_name,' Found max seq ' || l_max_rank_seq,
1140 WSH_DEBUG_SV.C_PROC_LEVEL);
1141 Log(l_module_name,' Appending to Rank List ',
1142 WSH_DEBUG_SV.C_PROC_LEVEL);
1143 END IF;
1144
1145
1146 idx :=1;
1147 FOR i IN p_ranklist.FIRST..p_ranklist.LAST LOOP
1148
1149
1150 p_ranklist(i).RANK_SEQUENCE := l_max_rank_seq+idx;
1151 p_ranklist(i).TRIP_ID := p_trip_id;
1152
1153 CREATE_RANK(p_rank_info_rec => p_ranklist(i),
1154 p_derive_init_sm_config => FND_API.G_FALSE,
1155 x_rank_id => l_rank_id,
1156 x_return_status => l_return_status);
1157
1158
1159 IF l_debug_on
1160 THEN
1161 Log(l_module_name,' After calling CREATE_RANK l_return_status ' ||
1162 l_return_status,
1163 WSH_DEBUG_SV.C_PROC_LEVEL);
1164 END IF;
1165
1166 wsh_util_core.api_post_call(
1167 p_return_status =>l_return_status,
1168 x_num_warnings =>l_number_of_warnings,
1169 x_num_errors =>l_number_of_errors,
1170 p_msg_data =>l_msg_data);
1171
1172
1173
1174 IF (p_ranklist(i).IS_CURRENT = 'Y') THEN
1175
1176 IF (l_currentSet = FND_API.G_TRUE) THEN
1177 SELECT NAME INTO l_trip_name FROM WSH_TRIPS WHERE TRIP_ID = p_trip_id;
1178
1179 FND_MESSAGE.SET_NAME('FTE','FTE_CANNOT_SET_MORE_CURR');
1180 FND_MESSAGE.SET_TOKEN('TRIP_NAME',l_trip_name);
1181 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
1182 RAISE FND_API.G_EXC_ERROR;
1183 END IF;
1184
1185 l_currentSet := FND_API.G_TRUE;
1186 p_ranklist(i).VERSION := 1;
1187 p_ranklist(i).RANK_ID := l_rank_id;
1188
1189
1190 IF l_debug_on
1191 THEN
1192 Log(l_module_name,' Calling SET RANK CURRENT API ',
1193 WSH_DEBUG_SV.C_PROC_LEVEL);
1194 END IF;
1195
1196
1197 SET_RANK_CURRENT(p_rank_info_rec => p_ranklist(i),
1198 p_trip_id => p_trip_id,
1199 x_return_status => l_return_status);
1200
1201 IF l_debug_on
1202 THEN
1203 Log(l_module_name,' After calling SET_RANK_CURRENT ' || l_return_status,
1204 WSH_DEBUG_SV.C_PROC_LEVEL);
1205 END IF;
1206
1207
1208
1209 IF (l_return_status = 'E')
1210 THEN
1211 RAISE FND_API.G_EXC_ERROR;
1212 ELSIF (l_return_status = 'U')
1213 THEN
1214 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1215 END IF;
1216
1217 -- Change this call with Shipping API
1218
1219 wsh_util_core.api_post_call(
1220 p_return_status =>l_return_status,
1221 x_num_warnings =>l_number_of_warnings,
1222 x_num_errors =>l_number_of_errors,
1223 p_msg_data =>l_msg_data);
1224
1225
1226
1227 END IF;
1228
1229
1230 IF l_number_of_errors > 0
1231 THEN
1232 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1233 RAISE FND_API.G_EXC_ERROR;
1234 ELSIF l_number_of_warnings > 0
1235 THEN
1236 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1237 ELSE
1238 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1239 END IF;
1240
1241 p_ranklist(i).RANK_ID := l_rank_id;
1242
1243 idx := idx+1;
1244
1245 END LOOP;
1246 IF l_debug_on THEN
1247 WSH_DEBUG_SV.pop(l_module_name);
1248 END IF;
1249
1250
1251 EXCEPTION
1252 WHEN FND_API.G_EXC_ERROR THEN
1253 ROLLBACK TO APPEND_RANK_LIST_PUB;
1254 x_return_status := FND_API.G_RET_STS_ERROR;
1255 IF l_debug_on THEN
1256 WSH_DEBUG_SV.pop(l_module_name);
1257 END IF;
1258
1259 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1260 ROLLBACK TO APPEND_RANK_LIST_PUB;
1261 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1262 IF l_debug_on THEN
1263 WSH_DEBUG_SV.pop(l_module_name);
1264 END IF;
1265
1266 WHEN OTHERS THEN
1267 ROLLBACK TO APPEND_RANK_LIST_PUB;
1268 WSH_UTIL_CORE.DEFAULT_HANDLER('FTE_CARRIER_RANK_LIST_PVT.APPEND_RANK_LIST');
1269 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1270 IF l_debug_on THEN
1271 WSH_DEBUG_SV.pop(l_module_name);
1272 END IF;
1273 END APPEND_RANK_LIST;
1274
1275
1276
1277 PROCEDURE RANK_LIST_ACTION(
1278 p_api_version_number IN NUMBER,
1279 p_init_msg_list IN VARCHAR2,
1280 x_return_status OUT NOCOPY VARCHAR2,
1281 x_msg_count OUT NOCOPY NUMBER,
1282 x_msg_data OUT NOCOPY VARCHAR2,
1283 p_action_code IN VARCHAR2,
1284 p_ranklist IN OUT NOCOPY carrier_rank_list_tbl_type,
1285 p_trip_id IN NUMBER,
1286 p_rank_id IN NUMBER)
1287 IS
1288
1289
1290 l_api_name CONSTANT VARCHAR2(30) := 'RANK_LIST_ACTION';
1291 l_api_version CONSTANT NUMBER := 1.0;
1292 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
1293 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || l_api_name;
1294
1295
1296 l_return_status VARCHAR2(32767);
1297 l_msg_count NUMBER;
1298 l_msg_data VARCHAR2(32767);
1299 l_number_of_warnings NUMBER;
1300 l_number_of_errors NUMBER;
1301
1302 l_result_code VARCHAR2(32767);
1303 l_rank_id NUMBER;
1304
1305 l_carrier_rank_list_rec CARRIER_RANK_LIST_REC;
1306 --}
1307
1308 l_current_version NUMBER;
1309 l_temp_id NUMBER;
1310
1311 --{Trip update parameters
1312 p_trip_info_tab WSH_TRIPS_PVT.Trip_Attr_Tbl_Type;
1313 p_trip_info WSH_TRIPS_PVT.Trip_Rec_Type;
1314 p_trip_in_rec WSH_TRIPS_GRP.TripInRecType;
1315 x_out_tab WSH_TRIPS_GRP.trip_Out_tab_type;
1316 --}
1317
1318
1319 BEGIN
1320
1321
1322 SAVEPOINT RANK_LIST_ACTION_PUB;
1323 IF l_debug_on THEN
1324 WSH_DEBUG_SV.push(l_module_name);
1325 END IF;
1326
1327 IF FND_API.to_Boolean( p_init_msg_list )
1328 THEN
1329 FND_MSG_PUB.initialize;
1330 END IF;
1331
1332 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1333 x_msg_count := 0;
1334 x_msg_data := 0;
1335 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1336 l_number_of_warnings := 0;
1337 l_number_of_errors := 0;
1338
1339 --{
1340 IF (p_action_code = S_CREATE) THEN
1341 IF l_debug_on
1342 THEN
1343 Log(l_module_name,' Creating Rank List for trip ' || p_trip_id,
1344 WSH_DEBUG_SV.C_PROC_LEVEL);
1345 END IF;
1346
1347 CREATE_RANK_LIST(
1348 p_ranklist => p_ranklist,
1349 p_trip_id => p_trip_id,
1350 x_return_status => l_return_status);
1351
1352 IF l_debug_on
1353 THEN
1354 Log(l_module_name,' l_return_status after CREATE_RANK_LIST ' ||
1355 l_return_status,
1356 WSH_DEBUG_SV.C_PROC_LEVEL);
1357 END IF;
1358
1359
1360 ELSIF (p_action_code = S_UPDATE) THEN
1361 IF l_debug_on
1362 THEN
1363 Log(l_module_name,' Updating Rank List for trip. TBD ' || p_trip_id,
1364 WSH_DEBUG_SV.C_PROC_LEVEL);
1365 END IF;
1366
1367 UPDATE_RANK_LIST(
1368 p_ranklist => p_ranklist,
1369 p_trip_id => p_trip_id,
1370 x_return_status => l_return_status);
1371
1372
1373 IF l_debug_on
1374 THEN
1375 Log(l_module_name,' l_return_status after UPDATE_RANK_LIST ' ||
1376 l_return_status,
1377 WSH_DEBUG_SV.C_PROC_LEVEL);
1378 END IF;
1379
1380 ELSIF (p_action_code = S_DELETE) THEN
1381 IF l_debug_on
1382 THEN
1383 Log(l_module_name,' Delete Rank List for trip ' || p_trip_id,
1384 WSH_DEBUG_SV.C_PROC_LEVEL);
1385 END IF;
1386
1387 DELETE_RANK_LIST(p_trip_id => p_trip_id,
1388 x_return_status => l_return_status);
1389
1390 IF l_debug_on
1391 THEN
1392 Log(l_module_name,' l_return_status after DELETE_RANK_LIST ' ||
1393 l_return_status,
1394 WSH_DEBUG_SV.C_PROC_LEVEL);
1395 END IF;
1396
1397
1398 ELSIF (p_action_code = S_SET_CURRENT) THEN
1399 IF l_debug_on
1400 THEN
1401 Log(l_module_name,' Setting Current Rank ' || p_trip_id,
1402 WSH_DEBUG_SV.C_PROC_LEVEL);
1403 END IF;
1404
1405 BEGIN
1406
1407
1408 SELECT VERSION INTO l_current_version
1409 FROM FTE_CARRIER_RANK_LIST
1410 WHERE RANK_ID = p_rank_id;
1411
1412 IF (SQL%NOTFOUND) THEN
1413 RAISE NO_DATA_FOUND;
1414 END IF;
1415
1416 EXCEPTION
1417 WHEN NO_DATA_FOUND THEN
1418 FND_MESSAGE.SET_NAME('FTE','FTE_RANK_NOT_FOUND');
1419 FND_MESSAGE.SET_TOKEN('RANK_ID', p_rank_id);
1420 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1421 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
1422 RAISE FND_API.G_EXC_ERROR;
1423
1424 END;
1425
1426
1427
1428 l_carrier_rank_list_rec.VERSION := l_current_version+1;
1429 l_carrier_rank_list_rec.rank_id := p_rank_id;
1430 UPDATE_RANK(p_rank_info_rec => l_carrier_rank_list_rec,
1431 x_return_status => l_return_status);
1432
1433
1434 IF l_debug_on
1435 THEN
1436 Log(l_module_name,' After calling UPDATE_RANK ' || l_return_status,
1437 WSH_DEBUG_SV.C_PROC_LEVEL);
1438 END IF;
1439
1440 IF (l_return_status = 'E')
1441 THEN
1442 RAISE FND_API.G_EXC_ERROR;
1443 ELSIF (l_return_status = 'U')
1444 THEN
1445 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1446 END IF;
1447
1448
1449
1450 -- Change this call with Shipping API
1451
1452 IF l_debug_on
1453 THEN
1454 Log(l_module_name,' Update trip with rank id ' ,
1455 WSH_DEBUG_SV.C_PROC_LEVEL);
1456 END IF;
1457
1458 FTE_MLS_WRAPPER.INITIALIZE_TRIP_REC(x_trip_info => p_trip_info);
1459
1460 -- Update trip information
1461 p_trip_info.RANK_ID := p_rank_id;
1462 p_trip_info.TRIP_ID := p_trip_id;
1463
1464 p_trip_info_tab(1) :=p_trip_info;
1465 p_trip_in_rec.caller :='FTE_LOAD_TENDER';
1466 p_trip_in_rec.phase :=NULL;
1467 p_trip_in_rec.action_code :='UPDATE';
1468
1469 IF l_debug_on
1470 THEN
1471 WSH_DEBUG_SV.logmsg(l_module_name,' Before calling CREATE_UPDATE_TRIP ' ||
1472 ' Rank list Action ',
1473 WSH_DEBUG_SV.C_PROC_LEVEL);
1474 END IF;
1475
1476
1477 WSH_INTERFACE_GRP.Create_Update_Trip
1478 (
1479 p_api_version_number =>1.0,
1480 p_init_msg_list =>FND_API.G_FALSE,
1481 p_commit =>FND_API.G_FALSE,
1482 x_return_status =>l_return_status,
1483 x_msg_count =>l_msg_count,
1484 x_msg_data =>l_msg_data,
1485 p_trip_info_tab =>p_trip_info_tab,
1486 p_in_rec =>p_trip_in_rec,
1487 x_out_tab =>x_out_tab
1488 );
1489
1490 IF l_debug_on
1491 THEN
1492 WSH_DEBUG_SV.logmsg(l_module_name,' REturn value from Create update trip ' ||
1493 l_return_status,
1494 WSH_DEBUG_SV.C_PROC_LEVEL);
1495 END IF;
1496
1497
1498 ELSIF (p_action_code = S_APPEND) THEN
1499 IF l_debug_on
1500 THEN
1501 Log(l_module_name,' Append Rank List for trip ' || p_trip_id,
1502 WSH_DEBUG_SV.C_PROC_LEVEL);
1503 END IF;
1504
1505 APPEND_RANK_LIST(
1506 p_ranklist => p_ranklist,
1507 p_trip_id => p_trip_id,
1508 x_return_status => l_return_status);
1509
1510 IF l_debug_on
1511 THEN
1512 Log(l_module_name,' l_return_status after APPEND_RANK_LIST ' ||
1513 l_return_status,
1514 WSH_DEBUG_SV.C_PROC_LEVEL);
1515 END IF;
1516
1517 ELSIF (p_action_code = S_REPLACE) THEN
1518 -- Request from Arindam
1519
1520 IF l_debug_on
1521 THEN
1522 Log(l_module_name,' Replace existing rank list. Calling Delete first ' || p_trip_id,
1523 WSH_DEBUG_SV.C_PROC_LEVEL);
1524 END IF;
1525
1526 SELECT count(*) INTO l_temp_id FROM FTE_CARRIER_RANK_LIST
1527 WHERE TRIP_ID = p_trip_id and rownum = 1;
1528
1529 IF (l_temp_id > 0) THEN
1530
1531 -- First call delete on rank list
1532 DELETE_RANK_LIST(p_trip_id => p_trip_id,
1533 x_return_status => l_return_status);
1534
1535 IF l_debug_on
1536 THEN
1537 Log(l_module_name,' l_return_status after DELETE_RANK_LIST ' ||
1538 l_return_status,
1539 WSH_DEBUG_SV.C_PROC_LEVEL);
1540 END IF;
1541
1542 wsh_util_core.api_post_call(
1543 p_return_status =>l_return_status,
1544 x_num_warnings =>l_number_of_warnings,
1545 x_num_errors =>l_number_of_errors,
1546 p_msg_data =>l_msg_data);
1547
1548 IF l_number_of_errors > 0
1549 THEN
1550 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1551 RAISE FND_API.G_EXC_ERROR;
1552 ELSIF l_number_of_warnings > 0
1553 THEN
1554 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1555 ELSE
1556 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1557 END IF;
1558
1559 END IF;
1560
1561
1562 -- now call create
1563 IF l_debug_on
1564 THEN
1565 Log(l_module_name,' Creating Rank for replace action ' || p_trip_id,
1566 WSH_DEBUG_SV.C_PROC_LEVEL);
1567 END IF;
1568
1569 CREATE_RANK_LIST(
1570 p_ranklist => p_ranklist,
1571 p_trip_id => p_trip_id,
1572 x_return_status => l_return_status);
1573
1574 IF l_debug_on
1575 THEN
1576 Log(l_module_name,' l_return_status after CREATE_RANK_LIST for replace action ' ||
1577 l_return_status,
1578 WSH_DEBUG_SV.C_PROC_LEVEL);
1579 END IF;
1580
1581 SELECT RANK_ID INTO l_temp_id
1582 FROM WSH_TRIPS
1583 where TRIP_ID = p_trip_id;
1584
1585 IF l_debug_on
1586 THEN
1587 Log(l_module_name,' rank id after CREATE_RANK_LIST for replace action ' ||
1588 l_temp_id,
1589 WSH_DEBUG_SV.C_PROC_LEVEL);
1590 END IF;
1591
1592
1593 END IF;
1594
1595
1596
1597 wsh_util_core.api_post_call(
1598 p_return_status =>l_return_status,
1599 x_num_warnings =>l_number_of_warnings,
1600 x_num_errors =>l_number_of_errors,
1601 p_msg_data =>l_msg_data);
1602
1603
1604 IF l_number_of_errors > 0
1605 THEN
1606 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1607 RAISE FND_API.G_EXC_ERROR;
1608 ELSIF l_number_of_warnings > 0
1609 THEN
1610 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1611 ELSE
1612 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1613 END IF;
1614
1615
1616 -- Standard call to get message count and if count is 1,get message info.
1617 --
1618 FND_MSG_PUB.Count_And_Get
1619 (
1620 p_count => x_msg_count,
1621 p_data => x_msg_data,
1622 p_encoded => FND_API.G_FALSE
1623 );
1624
1625
1626 --
1627 --
1628
1629 IF l_debug_on THEN
1630 WSH_DEBUG_SV.pop(l_module_name);
1631 END IF;
1632
1633
1634
1635 EXCEPTION
1636
1637 WHEN FND_API.G_EXC_ERROR THEN
1638 ROLLBACK TO RANK_LIST_ACTION_PUB;
1639 x_return_status := FND_API.G_RET_STS_ERROR ;
1640 FND_MSG_PUB.Count_And_Get
1641 (
1642 p_count => x_msg_count,
1643 p_data => x_msg_data,
1644 p_encoded => FND_API.G_FALSE
1645 );
1646 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1647 ROLLBACK TO RANK_LIST_ACTION_PUB;
1648 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1649 FND_MSG_PUB.Count_And_Get
1650 (
1651 p_count => x_msg_count,
1652 p_data => x_msg_data,
1653 p_encoded => FND_API.G_FALSE
1654 );
1655
1656 WHEN OTHERS THEN
1657 ROLLBACK TO RANK_LIST_ACTION_PUB;
1658 wsh_util_core.default_handler('FTE_CARRIER_RANK_LIST_PVT.RANK_LIST_ACTION');
1659 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1660 FND_MSG_PUB.Count_And_Get
1661 (
1662 p_count => x_msg_count,
1663 p_data => x_msg_data,
1664 p_encoded => FND_API.G_FALSE
1665 );
1666
1667
1668 END RANK_LIST_ACTION;
1669
1670
1671 PROCEDURE RANK_LIST_ACTION_UIWRAPPER(
1672 p_api_version_number IN NUMBER,
1673 p_init_msg_list IN VARCHAR2,
1674 x_return_status OUT NOCOPY VARCHAR2,
1675 x_msg_count OUT NOCOPY NUMBER,
1676 x_msg_data OUT NOCOPY VARCHAR2,
1677 p_action_code IN VARCHAR2,
1678 p_ranklist IN OUT NOCOPY FTE_SS_RATE_SORT_TAB_TYPE,
1679 p_trip_id IN NUMBER,
1680 p_rank_id IN NUMBER)
1681 IS
1682
1683
1684 l_api_name CONSTANT VARCHAR2(30) := 'RANK_LIST_ACTION_UIWRAPPER';
1685 l_api_version CONSTANT NUMBER := 1.0;
1686 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
1687 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || l_api_name;
1688
1689
1690 l_return_status VARCHAR2(32767);
1691 l_msg_count NUMBER;
1692 l_msg_data VARCHAR2(32767);
1693 l_number_of_warnings NUMBER;
1694 l_number_of_errors NUMBER;
1695
1696 l_result_code VARCHAR2(32767);
1697 l_rank_id NUMBER;
1698
1699 l_carrier_rank_list_rec CARRIER_RANK_LIST_REC;
1700 l_carrier_rank_list_tbl carrier_rank_list_tbl_type;
1701 l_return_rank_list_tbl CARRIER_RANK_LIST_TBL_TYPE;
1702 l_return_ss_rate_sort_rec FTE_SS_RATE_SORT_REC;
1703
1704 x_ranklist FTE_SS_RATE_SORT_TAB_TYPE;
1705
1706 --}
1707
1708 l_current_version NUMBER;
1709
1710
1711 BEGIN
1712
1713
1714 SAVEPOINT RANK_LIST_ACTION_UIWRAPPER_PUB;
1715 IF l_debug_on THEN
1716 WSH_DEBUG_SV.push(l_module_name);
1717 END IF;
1718
1719 IF FND_API.to_Boolean( p_init_msg_list )
1720 THEN
1721 FND_MSG_PUB.initialize;
1722 END IF;
1723
1724 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1725 x_msg_count := 0;
1726 x_msg_data := 0;
1727 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1728 l_number_of_warnings := 0;
1729 l_number_of_errors := 0;
1730
1731 --{
1732
1733 IF (p_ranklist IS NOT NULL
1734 AND p_ranklist.COUNT > 0)
1735 THEN
1736 IF l_debug_on
1737 THEN
1738 Log(l_module_name,' Transfering from UI list to PLSQL List ',
1739 WSH_DEBUG_SV.C_PROC_LEVEL);
1740 END IF;
1741
1742
1743 FOR i IN p_ranklist.FIRST..p_ranklist.LAST LOOP
1744
1745
1746 l_carrier_rank_list_rec.RANK_ID := p_ranklist(i).RANK_ID;
1747 l_carrier_rank_list_rec.TRIP_ID := p_trip_id;
1748 l_carrier_rank_list_rec.RANK_SEQUENCE := p_ranklist(i).RANK_SEQUENCE;
1749 l_carrier_rank_list_rec.LANE_ID := p_ranklist(i).LANE_ID;
1750 --l_carrier_rank_list_rec.SCHEDULE_ID := p_ranklist(i).SCHEDULE_ID;
1751 l_carrier_rank_list_rec.CARRIER_ID := p_ranklist(i).CARRIER_ID;
1752 l_carrier_rank_list_rec.MODE_OF_TRANSPORT := p_ranklist(i).MODE_OF_TRANSPORT;
1753 l_carrier_rank_list_rec.SERVICE_LEVEL := p_ranklist(i).SERVICE_LEVEL ;
1754 l_carrier_rank_list_rec.VEHICLE_ITEM_ID := p_ranklist(i).VEHICLE_ITEM_ID;
1755 l_carrier_rank_list_rec.VEHICLE_ORG_ID := p_ranklist(i).VEHICLE_ORG_ID ;
1756 l_carrier_rank_list_rec.SOURCE := p_ranklist(i).SOURCE ;
1757 l_carrier_rank_list_rec.ESTIMATED_RATE := p_ranklist(i).RATE;
1758 l_carrier_rank_list_rec.CURRENCY_CODE := p_ranklist(i).CURRENCY;
1759 l_carrier_rank_list_rec.ESTIMATED_TRANSIT_TIME := p_ranklist(i).EST_TRANSIT_TIME ;
1760 l_carrier_rank_list_rec.TRANSIT_TIME_UOM := p_ranklist(i).EST_TRANSIT_TIME_UOM ;
1761 --l_carrier_rank_list_rec.SCHEDULE_FROM := p_ranklist(i).SCHEDULE_FROM ;
1762 --l_carrier_rank_list_rec.SCHEDULE_TO := p_ranklist(i).SCHEDULE_TO ;
1763 l_carrier_rank_list_rec.IS_CURRENT := p_ranklist(i).IS_CURRENT ;
1764 l_carrier_rank_list_rec.CONSIGNEE_CARRIER_AC_NO := p_ranklist(i).CONSIGNEE_CARRIER_AC_NO;
1765 l_carrier_rank_list_rec.FREIGHT_TERMS_CODE := p_ranklist(i).FREIGHT_TERMS_CODE ;
1766
1767 l_carrier_rank_list_tbl(i) := l_carrier_rank_list_rec;
1768 END LOOP;
1769 END IF;
1770
1771
1772 RANK_LIST_ACTION(
1773 p_api_version_number => p_api_version_number,
1774 p_init_msg_list => FND_API.G_FALSE,
1775 x_return_status => l_return_status,
1776 x_msg_count => l_msg_count,
1777 x_msg_data => l_msg_data,
1778 p_action_code => p_action_code,
1779 p_ranklist => l_carrier_rank_list_tbl,
1780 p_trip_id => p_trip_id,
1781 p_rank_id => p_rank_id);
1782
1783 wsh_util_core.api_post_call(
1784 p_return_status =>l_return_status,
1785 x_num_warnings =>l_number_of_warnings,
1786 x_num_errors =>l_number_of_errors,
1787 p_msg_data =>l_msg_data);
1788
1789
1790 IF l_number_of_errors > 0
1791 THEN
1792 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1793 RAISE FND_API.G_EXC_ERROR;
1794 ELSIF l_number_of_warnings > 0
1795 THEN
1796 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1797 ELSE
1798 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1799 END IF;
1800
1801 -- now send back all the information back through x_ranklist
1802
1803 IF (l_carrier_rank_list_tbl IS NOT NULL
1804 AND l_carrier_rank_list_tbl.COUNT > 0)
1805 THEN
1806
1807 x_ranklist := FTE_SS_RATE_SORT_TAB_TYPE();
1808
1809 IF l_debug_on
1810 THEN
1811 Log(l_module_name,' Transfering from PLSQL list to UI List ',
1812 WSH_DEBUG_SV.C_PROC_LEVEL);
1813 END IF;
1814
1815 FOR i IN l_carrier_rank_list_tbl.FIRST..l_carrier_rank_list_tbl.LAST LOOP
1816
1817
1818 l_carrier_rank_list_rec := l_carrier_rank_list_tbl(i);
1819 l_return_ss_rate_sort_rec := FTE_SS_RATE_SORT_REC(
1820 l_carrier_rank_list_rec.RANK_ID,
1821 l_carrier_rank_list_rec.RANK_SEQUENCE,
1822 l_carrier_rank_list_rec.LANE_ID ,
1823 NULL,--l_carrier_rank_list_rec.SCHEDULE_ID,
1824 l_carrier_rank_list_rec.CARRIER_ID ,
1825 l_carrier_rank_list_rec.MODE_OF_TRANSPORT,
1826 l_carrier_rank_list_rec.SERVICE_LEVEL ,
1827 l_carrier_rank_list_rec.VEHICLE_ITEM_ID ,
1828 NULL,--l_carrier_rank_list_rec.VEHICLE_ORG_ID ,
1829 NULL,
1830 l_carrier_rank_list_rec.SOURCE ,
1831 l_carrier_rank_list_rec.ESTIMATED_RATE ,
1832 l_carrier_rank_list_rec.CURRENCY_CODE,
1833 l_carrier_rank_list_rec.ESTIMATED_TRANSIT_TIME,
1834 l_carrier_rank_list_rec.TRANSIT_TIME_UOM,
1835 NULL,--l_carrier_rank_list_rec.SCHEDULE_FROM ,
1836 NULL,--l_carrier_rank_list_rec.SCHEDULE_TO ,
1837 l_carrier_rank_list_rec.IS_CURRENT ,
1838 l_carrier_rank_list_rec.VERSION,
1839 NULL,
1840 l_carrier_rank_list_rec.CONSIGNEE_CARRIER_AC_NO ,
1841 l_carrier_rank_list_rec.FREIGHT_TERMS_CODE );
1842 x_ranklist.EXTEND;
1843 x_ranklist(x_ranklist.COUNT) := l_return_ss_rate_sort_rec;
1844
1845 END LOOP;
1846 END IF;
1847
1848 p_ranklist := x_ranklist;
1849
1850
1851 x_return_status := l_return_status;
1852
1853 -- Standard call to get message count and if count is 1,get message info.
1854 --
1855 FND_MSG_PUB.Count_And_Get
1856 (
1857 p_count => x_msg_count,
1858 p_data => x_msg_data,
1859 p_encoded => FND_API.G_FALSE
1860 );
1861
1862
1863 --
1864 --
1865
1866 IF l_debug_on THEN
1867 WSH_DEBUG_SV.pop(l_module_name);
1868 END IF;
1869
1870
1871
1872 EXCEPTION
1873
1874 WHEN FND_API.G_EXC_ERROR THEN
1875 ROLLBACK TO RANK_LIST_ACTION_UIWRAPPER_PUB;
1876 x_return_status := FND_API.G_RET_STS_ERROR ;
1877 FND_MSG_PUB.Count_And_Get
1878 (
1879 p_count => x_msg_count,
1880 p_data => x_msg_data,
1881 p_encoded => FND_API.G_FALSE
1882 );
1883 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1884 ROLLBACK TO RANK_LIST_ACTION_UIWRAPPER_PUB;
1885 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1886 FND_MSG_PUB.Count_And_Get
1887 (
1888 p_count => x_msg_count,
1889 p_data => x_msg_data,
1890 p_encoded => FND_API.G_FALSE
1891 );
1892
1893 WHEN OTHERS THEN
1894 ROLLBACK TO RANK_LIST_ACTION_UIWRAPPER_PUB;
1895 wsh_util_core.default_handler('FTE_CARRIER_RANK_LIST_PVT.RANK_LIST_ACTION_UIWRAPPER_PUB');
1896 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1897 FND_MSG_PUB.Count_And_Get
1898 (
1899 p_count => x_msg_count,
1900 p_data => x_msg_data,
1901 p_encoded => FND_API.G_FALSE
1902 );
1903
1904
1905 END RANK_LIST_ACTION_UIWRAPPER;
1906
1907
1908 PROCEDURE GET_RANK_DETAILS(
1909 p_init_msg_list IN VARCHAR2,
1910 x_return_status OUT NOCOPY VARCHAR2,
1911 x_msg_count OUT NOCOPY NUMBER,
1912 x_msg_data OUT NOCOPY VARCHAR2,
1913 x_rankdetails OUT NOCOPY carrier_rank_list_rec,
1914 p_rank_id IN NUMBER)
1915 IS
1916
1917
1918 l_api_name CONSTANT VARCHAR2(30) := 'GET_RANK_DETAILS';
1919 l_api_version CONSTANT NUMBER := 1.0;
1920 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
1921 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || l_api_name;
1922
1923
1924 l_return_status VARCHAR2(32767);
1925 l_msg_count NUMBER;
1926 l_msg_data VARCHAR2(32767);
1927 l_number_of_warnings NUMBER;
1928 l_number_of_errors NUMBER;
1929
1930 l_result_code VARCHAR2(32767);
1931 l_rank_id NUMBER;
1932
1933 --}
1934
1935 l_current_version NUMBER;
1936
1937 CURSOR get_rank_details_c IS
1938 SELECT RANK_ID ,
1939 TRIP_ID ,
1940 RANK_SEQUENCE ,
1941 CARRIER_ID ,
1942 SERVICE_LEVEL ,
1943 MODE_OF_TRANSPORT ,
1944 LANE_ID ,
1945 SOURCE ,
1946 ENABLED ,
1947 ESTIMATED_RATE ,
1948 CURRENCY_CODE ,
1949 VEHICLE_ITEM_ID ,
1950 ESTIMATED_TRANSIT_TIME ,
1951 TRANSIT_TIME_UOM ,
1952 VERSION ,
1953 CONSIGNEE_CARRIER_AC_NO ,
1954 FREIGHT_TERMS_CODE ,
1955 INITSMCONFIG ,
1956 ATTRIBUTE_CATEGORY ,
1957 ATTRIBUTE1 ,
1958 ATTRIBUTE2 ,
1959 ATTRIBUTE3 ,
1960 ATTRIBUTE4 ,
1961 ATTRIBUTE5 ,
1962 ATTRIBUTE6 ,
1963 ATTRIBUTE7 ,
1964 ATTRIBUTE8 ,
1965 ATTRIBUTE9 ,
1966 ATTRIBUTE10 ,
1967 ATTRIBUTE11 ,
1968 ATTRIBUTE12 ,
1969 ATTRIBUTE13 ,
1970 ATTRIBUTE14 ,
1971 ATTRIBUTE15 ,
1972 CREATION_DATE ,
1973 CREATED_BY ,
1974 LAST_UPDATE_DATE ,
1975 LAST_UPDATED_BY ,
1976 LAST_UPDATE_LOGIN ,
1977 'N',0,'SORT',NULL,NULL,-99,VEHICLE_ORG_ID,
1978 CALL_RG_FLAG
1979 FROM FTE_CARRIER_RANK_LIST WHERE RANK_ID = p_rank_id;
1980
1981 BEGIN
1982
1983
1984 SAVEPOINT GET_RANK_DETAILS_PUB;
1985 IF l_debug_on THEN
1986 WSH_DEBUG_SV.push(l_module_name);
1987 END IF;
1988
1989
1990 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1991 x_msg_count := 0;
1992 x_msg_data := 0;
1993 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1994 l_number_of_warnings := 0;
1995 l_number_of_errors := 0;
1996
1997
1998 OPEN get_rank_details_c;
1999 FETCH get_rank_details_c INTO x_rankdetails;
2000
2001 IF (get_rank_details_c%NOTFOUND) THEN
2002 FND_MESSAGE.SET_NAME('FTE','FTE_RANK_NOT_FOUND');
2003 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2004 wsh_util_core.add_message(x_return_status);
2005 ELSE
2006 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2007 END IF;
2008
2009 CLOSE get_rank_details_c;
2010
2011
2012 --
2013 --
2014
2015 IF l_debug_on THEN
2016 WSH_DEBUG_SV.pop(l_module_name);
2017 END IF;
2018
2019
2020
2021 EXCEPTION
2022
2023 WHEN FND_API.G_EXC_ERROR THEN
2024 ROLLBACK TO GET_RANK_DETAILS_PUB;
2025 x_return_status := FND_API.G_RET_STS_ERROR ;
2026 FND_MSG_PUB.Count_And_Get
2027 (
2028 p_count => x_msg_count,
2029 p_data => x_msg_data,
2030 p_encoded => FND_API.G_FALSE
2031 );
2032 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2033 ROLLBACK TO GET_RANK_DETAILS_PUB;
2034 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2035 FND_MSG_PUB.Count_And_Get
2036 (
2037 p_count => x_msg_count,
2038 p_data => x_msg_data,
2039 p_encoded => FND_API.G_FALSE
2040 );
2041
2042 WHEN OTHERS THEN
2043 ROLLBACK TO GET_RANK_DETAILS_PUB;
2044 wsh_util_core.default_handler('FTE_CARRIER_RANK_LIST_PVT.GET_RANK_DETAILS');
2045 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2046 FND_MSG_PUB.Count_And_Get
2047 (
2048 p_count => x_msg_count,
2049 p_data => x_msg_data,
2050 p_encoded => FND_API.G_FALSE
2051 );
2052
2053
2054 END GET_RANK_DETAILS;
2055 --
2056
2057 PROCEDURE GET_RANK_LIST(
2058 p_init_msg_list IN VARCHAR2,
2059 x_return_status OUT NOCOPY VARCHAR2,
2060 x_msg_count OUT NOCOPY NUMBER,
2061 x_msg_data OUT NOCOPY VARCHAR2,
2062 x_ranklist OUT NOCOPY carrier_rank_list_tbl_type,
2063 p_trip_id IN NUMBER)
2064 IS
2065
2066
2067 l_api_name CONSTANT VARCHAR2(30) := 'GET_RANK_LIST';
2068 l_api_version CONSTANT NUMBER := 1.0;
2069 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
2070 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || l_api_name;
2071
2072
2073 l_return_status VARCHAR2(32767);
2074 l_msg_count NUMBER;
2075 l_msg_data VARCHAR2(32767);
2076 l_number_of_warnings NUMBER;
2077 l_number_of_errors NUMBER;
2078
2079 l_result_code VARCHAR2(32767);
2080 l_rank_id NUMBER;
2081 l_carrier_rank_list_rec carrier_rank_list_rec;
2082
2083 --}
2084
2085 l_current_version NUMBER;
2086
2087 CURSOR get_rank_list_c IS
2088 SELECT RANK_ID ,
2089 TRIP_ID ,
2090 RANK_SEQUENCE ,
2091 CARRIER_ID ,
2092 SERVICE_LEVEL ,
2093 MODE_OF_TRANSPORT ,
2094 LANE_ID ,
2095 SOURCE ,
2096 ENABLED ,
2097 ESTIMATED_RATE ,
2098 CURRENCY_CODE ,
2099 VEHICLE_ITEM_ID ,
2100 ESTIMATED_TRANSIT_TIME ,
2101 TRANSIT_TIME_UOM ,
2102 VERSION ,
2103 CONSIGNEE_CARRIER_AC_NO ,
2104 FREIGHT_TERMS_CODE ,
2105 INITSMCONFIG ,
2106 ATTRIBUTE_CATEGORY ,
2107 ATTRIBUTE1 ,
2108 ATTRIBUTE2 ,
2109 ATTRIBUTE3 ,
2110 ATTRIBUTE4 ,
2111 ATTRIBUTE5 ,
2112 ATTRIBUTE6 ,
2113 ATTRIBUTE7 ,
2114 ATTRIBUTE8 ,
2115 ATTRIBUTE9 ,
2116 ATTRIBUTE10 ,
2117 ATTRIBUTE11 ,
2118 ATTRIBUTE12 ,
2119 ATTRIBUTE13 ,
2120 ATTRIBUTE14 ,
2121 ATTRIBUTE15 ,
2122 CREATION_DATE ,
2123 CREATED_BY ,
2124 LAST_UPDATE_DATE ,
2125 LAST_UPDATED_BY ,
2126 LAST_UPDATE_LOGIN ,
2127 'N' IS_CURRENT,NULL SINGLE_CURR_RATE,NULL SORT,NULL SCHEDULE_FROM,NULL SCHEDULE_TO,NULL SCHEDULE_ID,
2128 VEHICLE_ORG_ID,
2129 CALL_RG_FLAG
2130 FROM FTE_CARRIER_RANK_LIST WHERE trip_id = p_trip_id
2131 ORDER BY RANK_ID, RANK_SEQUENCE;
2132
2133 idx NUMBER;
2134 BEGIN
2135
2136
2137 SAVEPOINT GET_RANK_LIST_PUB;
2138 IF l_debug_on THEN
2139 WSH_DEBUG_SV.push(l_module_name);
2140 END IF;
2141
2142
2143 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2144 x_msg_count := 0;
2145 x_msg_data := 0;
2146 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2147 l_number_of_warnings := 0;
2148 l_number_of_errors := 0;
2149
2150
2151 IF l_debug_on
2152 THEN
2153 Log(l_module_name,' Getting rank list for trip ' || p_trip_id,
2154 WSH_DEBUG_SV.C_PROC_LEVEL);
2155 END IF;
2156
2157 idx := 1;
2158 FOR get_rank_list_rec IN get_rank_list_c
2159 LOOP
2160 --{
2161 --l_carrier_rank_list_rec := get_rank_list_rec;
2162 l_carrier_rank_list_rec.RANK_ID := get_rank_list_rec.RANK_ID;
2163 l_carrier_rank_list_rec.TRIP_ID := get_rank_list_rec.TRIP_ID;
2164 l_carrier_rank_list_rec.RANK_SEQUENCE := get_rank_list_rec.RANK_SEQUENCE ;
2165 l_carrier_rank_list_rec.CARRIER_ID := get_rank_list_rec.CARRIER_ID ;
2166 l_carrier_rank_list_rec.SERVICE_LEVEL := get_rank_list_rec.SERVICE_LEVEL;
2167 l_carrier_rank_list_rec.MODE_OF_TRANSPORT := get_rank_list_rec.MODE_OF_TRANSPORT;
2168 l_carrier_rank_list_rec.LANE_ID := get_rank_list_rec.LANE_ID ;
2169 l_carrier_rank_list_rec.SOURCE := get_rank_list_rec.SOURCE ;
2170 l_carrier_rank_list_rec.ENABLED := get_rank_list_rec.ENABLED ;
2171 l_carrier_rank_list_rec.ESTIMATED_RATE := get_rank_list_rec.ESTIMATED_RATE ;
2172 l_carrier_rank_list_rec.CURRENCY_CODE := get_rank_list_rec.CURRENCY_CODE ;
2173 l_carrier_rank_list_rec.VEHICLE_ITEM_ID := get_rank_list_rec.VEHICLE_ITEM_ID ;
2174 l_carrier_rank_list_rec.ESTIMATED_TRANSIT_TIME := get_rank_list_rec.ESTIMATED_TRANSIT_TIME ;
2175 l_carrier_rank_list_rec.TRANSIT_TIME_UOM := get_rank_list_rec.TRANSIT_TIME_UOM ;
2176 l_carrier_rank_list_rec.VERSION := get_rank_list_rec.VERSION ;
2177 l_carrier_rank_list_rec.CONSIGNEE_CARRIER_AC_NO := get_rank_list_rec.CONSIGNEE_CARRIER_AC_NO ;
2178 l_carrier_rank_list_rec.FREIGHT_TERMS_CODE := get_rank_list_rec.FREIGHT_TERMS_CODE ;
2179 l_carrier_rank_list_rec.INITSMCONFIG := get_rank_list_rec.INITSMCONFIG ;
2180 l_carrier_rank_list_rec.ATTRIBUTE_CATEGORY := get_rank_list_rec.ATTRIBUTE_CATEGORY ;
2181 l_carrier_rank_list_rec.ATTRIBUTE1 := get_rank_list_rec.ATTRIBUTE1 ;
2182 l_carrier_rank_list_rec.ATTRIBUTE2 := get_rank_list_rec.ATTRIBUTE2 ;
2183 l_carrier_rank_list_rec.ATTRIBUTE3 := get_rank_list_rec.ATTRIBUTE3 ;
2184 l_carrier_rank_list_rec.ATTRIBUTE4 := get_rank_list_rec.ATTRIBUTE4 ;
2185 l_carrier_rank_list_rec.ATTRIBUTE5 := get_rank_list_rec.ATTRIBUTE5 ;
2186 l_carrier_rank_list_rec.ATTRIBUTE6 := get_rank_list_rec.ATTRIBUTE6 ;
2187 l_carrier_rank_list_rec.ATTRIBUTE7 := get_rank_list_rec.ATTRIBUTE7 ;
2188 l_carrier_rank_list_rec.ATTRIBUTE8 := get_rank_list_rec.ATTRIBUTE8 ;
2189 l_carrier_rank_list_rec.ATTRIBUTE9 := get_rank_list_rec.ATTRIBUTE9 ;
2190 l_carrier_rank_list_rec.ATTRIBUTE10 := get_rank_list_rec.ATTRIBUTE10 ;
2191 l_carrier_rank_list_rec.ATTRIBUTE11 := get_rank_list_rec.ATTRIBUTE11 ;
2192 l_carrier_rank_list_rec.ATTRIBUTE12 := get_rank_list_rec.ATTRIBUTE12 ;
2193 l_carrier_rank_list_rec.ATTRIBUTE13 := get_rank_list_rec.ATTRIBUTE13 ;
2194 l_carrier_rank_list_rec.ATTRIBUTE14 := get_rank_list_rec.ATTRIBUTE14 ;
2195 l_carrier_rank_list_rec.ATTRIBUTE15 := get_rank_list_rec.ATTRIBUTE15 ;
2196 l_carrier_rank_list_rec.CREATION_DATE := get_rank_list_rec.CREATION_DATE ;
2197 l_carrier_rank_list_rec.CREATED_BY := get_rank_list_rec.CREATED_BY ;
2198 l_carrier_rank_list_rec.LAST_UPDATE_DATE := get_rank_list_rec.LAST_UPDATE_DATE ;
2199 l_carrier_rank_list_rec.LAST_UPDATED_BY := get_rank_list_rec.LAST_UPDATED_BY ;
2200 l_carrier_rank_list_rec.LAST_UPDATE_LOGIN := get_rank_list_rec.LAST_UPDATE_LOGIN ;
2201 l_carrier_rank_list_rec.IS_CURRENT := get_rank_list_rec.IS_CURRENT ;
2202 l_carrier_rank_list_rec.SINGLE_CURR_RATE := get_rank_list_rec.SINGLE_CURR_RATE ;
2203 l_carrier_rank_list_rec.SORT := get_rank_list_rec.SORT ;
2204 l_carrier_rank_list_rec.SCHEDULE_FROM := get_rank_list_rec.SCHEDULE_FROM ;
2205 l_carrier_rank_list_rec.SCHEDULE_TO := get_rank_list_rec.SCHEDULE_TO ;
2206 l_carrier_rank_list_rec.SCHEDULE_ID := get_rank_list_rec.SCHEDULE_ID ;
2207 l_carrier_rank_list_rec.VEHICLE_ORG_ID := get_rank_list_rec.VEHICLE_ORG_ID ;
2208 l_carrier_rank_list_rec.CALL_RG_FLAG := get_rank_list_rec.CALL_RG_FLAG;
2209
2210 x_ranklist(idx) := l_carrier_rank_list_rec;
2211 idx := idx+1;
2212
2213 --}
2214 END LOOP;
2215
2216 -- END OF
2217 IF get_rank_list_c%ISOPEN THEN
2218 CLOSE get_rank_list_c;
2219 END IF;
2220 --
2221
2222 IF l_debug_on
2223 THEN
2224 Log(l_module_name,' Done getting rank list for trip ' || p_trip_id,
2225 WSH_DEBUG_SV.C_PROC_LEVEL);
2226 END IF;
2227
2228 --
2229
2230 IF l_debug_on THEN
2231 WSH_DEBUG_SV.pop(l_module_name);
2232 END IF;
2233
2234
2235
2236 EXCEPTION
2237
2238 WHEN FND_API.G_EXC_ERROR THEN
2239 ROLLBACK TO GET_RANK_LIST_PUB;
2240 x_return_status := FND_API.G_RET_STS_ERROR ;
2241 FND_MSG_PUB.Count_And_Get
2242 (
2243 p_count => x_msg_count,
2244 p_data => x_msg_data,
2245 p_encoded => FND_API.G_FALSE
2246 );
2247 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2248 ROLLBACK TO GET_RANK_LIST_PUB;
2249 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2250 FND_MSG_PUB.Count_And_Get
2251 (
2252 p_count => x_msg_count,
2253 p_data => x_msg_data,
2254 p_encoded => FND_API.G_FALSE
2255 );
2256
2257 WHEN OTHERS THEN
2258 ROLLBACK TO GET_RANK_LIST_PUB;
2259 wsh_util_core.default_handler('FTE_CARRIER_RANK_LIST_PVT.GET_RANK_LIST');
2260 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2261 FND_MSG_PUB.Count_And_Get
2262 (
2263 p_count => x_msg_count,
2264 p_data => x_msg_data,
2265 p_encoded => FND_API.G_FALSE
2266 );
2267
2268
2269 END GET_RANK_LIST;
2270
2271
2272
2273 PROCEDURE REMOVE_SERVICE_APPLY_NEXT(
2274 p_init_msg_list IN VARCHAR2,
2275 x_return_status OUT NOCOPY VARCHAR2,
2276 x_msg_count OUT NOCOPY NUMBER,
2277 x_msg_data OUT NOCOPY VARCHAR2,
2278 p_trip_id IN NUMBER,
2279 p_price_request_id IN NUMBER)
2280 IS
2281
2282 l_api_name CONSTANT VARCHAR2(30) := 'REMOVE_SERVICE_APPLY_NEXT';
2283 l_api_version CONSTANT NUMBER := 1.0;
2284 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
2285 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || l_api_name;
2286
2287
2288 l_return_status VARCHAR2(32767);
2289 l_msg_count NUMBER;
2290 l_msg_data VARCHAR2(32767);
2291 l_number_of_warnings NUMBER;
2292 l_number_of_errors NUMBER;
2293
2294 l_rank_rec carrier_rank_list_rec;
2295 x_list_tbl FTE_CARRIER_RANK_LIST_PVT.carrier_rank_list_tbl_type;
2296 l_list_tbl FTE_CARRIER_RANK_LIST_PVT.carrier_rank_list_tbl_type;
2297
2298 l_current_rank_sequence NUMBER;
2299
2300 l_list_exhausted VARCHAR2(32767);
2301 l_trip_name WSH_TRIPS.NAME%TYPE;
2302
2303 --{Trip update parameters
2304 p_trip_info_tab WSH_TRIPS_PVT.Trip_Attr_Tbl_Type;
2305 p_trip_info WSH_TRIPS_PVT.Trip_Rec_Type;
2306 p_trip_in_rec WSH_TRIPS_GRP.TripInRecType;
2307 x_out_tab WSH_TRIPS_GRP.trip_Out_tab_type;
2308 --}
2309
2310
2311
2312 CURSOR get_next_rank(p_current_sequence NUMBER) IS
2313 SELECT RANK_ID ,
2314 TRIP_ID ,
2315 RANK_SEQUENCE ,
2316 CARRIER_ID ,
2317 SERVICE_LEVEL ,
2318 MODE_OF_TRANSPORT ,
2319 LANE_ID ,
2320 SOURCE ,
2321 ENABLED ,
2322 ESTIMATED_RATE ,
2323 CURRENCY_CODE ,
2324 VEHICLE_ITEM_ID ,
2325 ESTIMATED_TRANSIT_TIME ,
2326 TRANSIT_TIME_UOM ,
2327 VERSION ,
2328 CONSIGNEE_CARRIER_AC_NO ,
2329 FREIGHT_TERMS_CODE ,
2330 INITSMCONFIG ,
2331 ATTRIBUTE_CATEGORY ,
2332 ATTRIBUTE1 ,
2333 ATTRIBUTE2 ,
2334 ATTRIBUTE3 ,
2335 ATTRIBUTE4 ,
2336 ATTRIBUTE5 ,
2337 ATTRIBUTE6 ,
2338 ATTRIBUTE7 ,
2339 ATTRIBUTE8 ,
2340 ATTRIBUTE9 ,
2341 ATTRIBUTE10 ,
2342 ATTRIBUTE11 ,
2343 ATTRIBUTE12 ,
2344 ATTRIBUTE13 ,
2345 ATTRIBUTE14 ,
2346 ATTRIBUTE15 ,
2347 CREATION_DATE ,
2348 CREATED_BY ,
2349 LAST_UPDATE_DATE ,
2350 LAST_UPDATED_BY ,
2351 LAST_UPDATE_LOGIN ,
2352 'N' IS_CURRENT,NULL SINGLE_CURR_RATE,NULL SORT,NULL SCHEDULE_FROM,NULL SCHEDULE_TO,NULL SCHEDULE_ID,
2353 VEHICLE_ORG_ID,CALL_RG_FLAG
2354 FROM FTE_CARRIER_RANK_LIST rank_list
2355 WHERE rank_list.trip_id = p_trip_id
2356 AND rank_list.RANK_SEQUENCE = (p_current_sequence+1);
2357
2358 BEGIN
2359
2360
2361 SAVEPOINT REMOVE_SERVICE_APPLY_NEXT_PUB;
2362 IF l_debug_on THEN
2363 WSH_DEBUG_SV.push(l_module_name);
2364 END IF;
2365
2366 IF FND_API.to_Boolean( p_init_msg_list )
2367 THEN
2368 FND_MSG_PUB.initialize;
2369 END IF;
2370
2371 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2372 x_msg_count := 0;
2373 x_msg_data := 0;
2374 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2375 l_number_of_warnings := 0;
2376 l_number_of_errors := 0;
2377 l_msg_data := 0;
2378
2379
2380 l_list_exhausted := 'F';
2381 IF l_debug_on
2382 THEN
2383 Log(l_module_name,' Check current rank sequence ',
2384 WSH_DEBUG_SV.C_PROC_LEVEL);
2385 END IF;
2386
2387 BEGIN
2388
2389
2390 SELECT RANK_SEQUENCE INTO l_current_rank_sequence
2391 FROM FTE_CARRIER_RANK_LIST rank_list, WSH_TRIPS trips
2392 WHERE trips.trip_id = p_trip_id
2393 AND trips.RANK_ID = rank_list.RANK_ID;
2394
2395 IF (SQL%NOTFOUND) THEN
2396 RAISE NO_DATA_FOUND;
2397 END IF;
2398
2399 EXCEPTION
2400 WHEN NO_DATA_FOUND THEN
2401 FND_MESSAGE.SET_NAME('FTE','FTE_RANK_NOT_FOUND');
2402 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2403 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
2404 RAISE FND_API.G_EXC_ERROR;
2405 END;
2406
2407 IF l_debug_on
2408 THEN
2409 Log(l_module_name,' Current rank sequence ' || l_current_rank_sequence,
2410 WSH_DEBUG_SV.C_PROC_LEVEL);
2411 END IF;
2412
2413
2414 --Get the next shipmethod in the sequence based on current sequence
2415
2416 OPEN get_next_rank(l_current_rank_sequence);
2417 FETCH get_next_rank INTO l_rank_rec;
2418
2419 IF l_debug_on
2420 THEN
2421 Log(l_module_name,' Executed rank query ',
2422 WSH_DEBUG_SV.C_PROC_LEVEL);
2423 END IF;
2424
2425
2426 IF (get_next_rank%NOTFOUND) THEN
2427 SELECT NAME INTO l_trip_name from WSH_TRIPS
2428 WHERE TRIP_ID = p_trip_id;
2429
2430 FND_MESSAGE.SET_NAME('FTE','FTE_RANK_LIST_EXAUSTED');
2431 FND_MESSAGE.SET_TOKEN('TRIP_NAME',l_trip_name);
2432
2433 l_list_exhausted := 'T';
2434 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2435 wsh_util_core.add_message(x_return_status);
2436 END IF;
2437
2438 CLOSE get_next_rank;
2439
2440
2441 IF (l_list_exhausted = 'T')
2442 THEN
2443 --{
2444
2445 IF l_debug_on
2446 THEN
2447 Log(l_module_name,' Rank list exhausted so nothing to do ' ,
2448 WSH_DEBUG_SV.C_PROC_LEVEL);
2449 END IF;
2450 --}
2451 ELSE
2452 --{
2453
2454 -- First cancel service
2455 IF l_debug_on
2456 THEN
2457 Log(l_module_name,' cancel service on the trip. Also get rid of rates ',
2458 WSH_DEBUG_SV.C_PROC_LEVEL);
2459 END IF;
2460
2461
2462 IF l_debug_on
2463 THEN
2464 Log(l_module_name,' Now update trip with next shipmethod ',
2465 WSH_DEBUG_SV.C_PROC_LEVEL);
2466 Log(l_module_name,' Carrier Id ' || l_rank_rec.CARRIER_ID,
2467 WSH_DEBUG_SV.C_PROC_LEVEL);
2468 Log(l_module_name,' Service Level ' || l_rank_rec.SERVICE_LEVEL,
2469 WSH_DEBUG_SV.C_PROC_LEVEL);
2470 Log(l_module_name,' Mode Of Transport ' || l_rank_rec.MODE_OF_TRANSPORT,
2471 WSH_DEBUG_SV.C_PROC_LEVEL);
2472 Log(l_module_name,' Lane Id ' || l_rank_rec.LANE_ID,
2473 WSH_DEBUG_SV.C_PROC_LEVEL);
2474 Log(l_module_name,' Freight Terms Code ' || l_rank_rec.FREIGHT_TERMS_CODE,
2475 WSH_DEBUG_SV.C_PROC_LEVEL);
2476 Log(l_module_name,' Consignee carrier ac no ' || l_rank_rec.CONSIGNEE_CARRIER_AC_NO,
2477 WSH_DEBUG_SV.C_PROC_LEVEL);
2478 END IF;
2479
2480 -- Call shipping API to update trip information with next shipmethod
2481 /**
2482 UPDATE WSH_TRIPS
2483 SET CARRIER_ID = l_rank_rec.CARRIER_ID,
2484 SERVICE_LEVEL = l_rank_rec.SERVICE_LEVEL,
2485 MODE_OF_TRANSPORT = l_rank_rec.MODE_OF_TRANSPORT,
2486 VEHICLE_ITEM_ID = l_rank_rec.VEHICLE_ITEM_ID,
2487 VEHICLE_ORGANIZATION_ID = l_rank_rec.VEHICLE_ORG_ID,
2488 LANE_ID = l_rank_rec.LANE_ID,
2489 FREIGHT_TERMS_CODE = l_rank_rec.FREIGHT_TERMS_CODE,
2490 CONSIGNEE_CARRIER_AC_NO = l_rank_rec.CONSIGNEE_CARRIER_AC_NO
2491 WHERE TRIP_ID = p_trip_id;
2492 */
2493
2494 IF l_debug_on
2495 THEN
2496 WSH_DEBUG_SV.logmsg(l_module_name,' Calling UPDATE_SERVICE_ON_TRIP ' ||
2497 l_return_status,
2498 WSH_DEBUG_SV.C_PROC_LEVEL);
2499 END IF;
2500
2501
2502 FTE_MLS_WRAPPER.UPDATE_SERVICE_ON_TRIP(
2503 p_API_VERSION_NUMBER => 1.0,
2504 p_INIT_MSG_LIST => FND_API.G_FALSE,
2505 p_COMMIT => FND_API.G_FALSE,
2506 p_CALLER => 'FTE_LOAD_TENDER',
2507 p_SERVICE_ACTION => 'UPDATE',
2508 p_DELIVERY_ID => null,
2509 p_DELIVERY_LEG_ID => null,
2510 p_TRIP_ID => p_trip_id,
2511 p_LANE_ID => l_rank_rec.LANE_ID,
2512 p_SCHEDULE_ID => l_rank_rec.SCHEDULE_ID,
2513 p_CARRIER_ID => l_rank_rec.CARRIER_ID,
2514 p_SERVICE_LEVEL => l_rank_rec.SERVICE_LEVEL,
2515 p_MODE_OF_TRANSPORT => l_rank_rec.MODE_OF_TRANSPORT,
2516 p_VEHICLE_ITEM_ID => l_rank_rec.VEHICLE_ITEM_ID,
2517 p_VEHICLE_ORG_ID => l_rank_rec.VEHICLE_ORG_ID,
2518 p_CONSIGNEE_CARRIER_AC_NO => l_rank_rec.CONSIGNEE_CARRIER_AC_NO,
2519 p_FREIGHT_TERMS_CODE => l_rank_rec.FREIGHT_TERMS_CODE,
2520 x_RETURN_STATUS => l_return_status,
2521 x_MSG_COUNT => l_msg_count,
2522 x_MSG_DATA => l_msg_data);
2523
2524 IF l_debug_on
2525 THEN
2526 WSH_DEBUG_SV.logmsg(l_module_name,' REturn value from UPDATE_SERVICE_ON_TRIP ' ||
2527 l_return_status,
2528 WSH_DEBUG_SV.C_PROC_LEVEL);
2529 END IF;
2530
2531 wsh_util_core.api_post_call(
2532 p_return_status =>l_return_status,
2533 x_num_warnings =>l_number_of_warnings,
2534 x_num_errors =>l_number_of_errors,
2535 p_msg_data =>l_msg_data);
2536
2537 IF l_number_of_errors > 0
2538 THEN
2539 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2540 RAISE FND_API.G_EXC_ERROR;
2541 ELSIF l_number_of_warnings > 0
2542 THEN
2543 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2544 ELSE
2545 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2546 END IF;
2547
2548
2549 IF (p_price_request_id IS NOT NULL)
2550 THEN
2551 IF l_debug_on
2552 THEN
2553 Log(l_module_name,' Price request is not null so call move ',
2554 WSH_DEBUG_SV.C_PROC_LEVEL);
2555 END IF;
2556
2557 FTE_TRIP_RATING_GRP.Move_Records_To_Main (p_trip_id => p_trip_id,
2558 p_lane_id => l_rank_rec.LANE_ID,
2559 p_schedule_id => l_rank_rec.SCHEDULE_ID,
2560 p_service_type_code => l_rank_rec.SERVICE_LEVEL,
2561 p_comparison_request_id => p_price_request_id,
2562 x_return_status => l_return_status);
2563
2564 IF l_debug_on
2565 THEN
2566 Log(l_module_name,' After calling move rates ' || l_return_status,
2567 WSH_DEBUG_SV.C_PROC_LEVEL);
2568 END IF;
2569
2570
2571 wsh_util_core.api_post_call(
2572 p_return_status =>l_return_status,
2573 x_num_warnings =>l_number_of_warnings,
2574 x_num_errors =>l_number_of_errors);
2575
2576 ELSE
2577 IF l_debug_on
2578 THEN
2579 Log(l_module_name,' Price request is null so call re-price ',
2580 WSH_DEBUG_SV.C_PROC_LEVEL);
2581 END IF;
2582
2583 FTE_MLS_WRAPPER.REPRICE_TRIP(p_api_version => 1.0,
2584 p_init_msg_list => FND_API.G_FALSE,
2585 p_trip_id => p_trip_id,
2586 x_return_status => l_return_status,
2587 x_msg_count => l_msg_count,
2588 x_msg_data => l_msg_data);
2589
2590 IF l_debug_on
2591 THEN
2592 Log(l_module_name,' After calling reprice trip ' || l_return_status,
2593 WSH_DEBUG_SV.C_PROC_LEVEL);
2594 END IF;
2595
2596
2597 IF l_debug_on
2598 THEN
2599 Log(l_module_name,' Number of errors ' || l_number_of_errors,
2600 WSH_DEBUG_SV.C_PROC_LEVEL);
2601 Log(l_module_name,' l_msg_data ' || l_msg_data,
2602 WSH_DEBUG_SV.C_PROC_LEVEL);
2603 Log(l_module_name,' Continue even if rating failed ',
2604 WSH_DEBUG_SV.C_PROC_LEVEL);
2605
2606 END IF;
2607
2608 -- Continue even if rating failed
2609 /**
2610 wsh_util_core.api_post_call(
2611 p_return_status =>l_return_status,
2612 x_num_warnings =>l_number_of_warnings,
2613 x_num_errors =>l_number_of_errors,
2614 p_msg_data =>l_msg_data);
2615 **/
2616 END IF;
2617
2618
2619
2620 IF l_number_of_errors > 0
2621 THEN
2622 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2623 RAISE FND_API.G_EXC_ERROR;
2624 ELSIF l_number_of_warnings > 0
2625 THEN
2626 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2627 ELSE
2628 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2629 END IF;
2630
2631 l_list_tbl(1) := l_rank_rec;
2632
2633 IF l_debug_on
2634 THEN
2635 Log(l_module_name,' Before Calling Rank List Action to set current ' ||
2636 l_return_status,
2637 WSH_DEBUG_SV.C_PROC_LEVEL);
2638 END IF;
2639
2640
2641 RANK_LIST_ACTION(
2642 p_api_version_number => 1.0,
2643 p_init_msg_list => FND_API.G_FALSE,
2644 x_return_status => l_return_status,
2645 x_msg_count => l_msg_count,
2646 x_msg_data => l_msg_data,
2647 p_action_code => FTE_CARRIER_RANK_LIST_PVT.S_SET_CURRENT,
2648 p_ranklist => l_list_tbl,
2649 p_trip_id => p_trip_id,
2650 p_rank_id => l_rank_rec.RANK_ID);
2651
2652
2653 IF l_debug_on
2654 THEN
2655 Log(l_module_name,' Result FTE_CARRIER_RANK_LIST_PVT.RANK_LIST_ACTION ' ||
2656 l_return_status,
2657 WSH_DEBUG_SV.C_PROC_LEVEL);
2658 END IF;
2659
2660 wsh_util_core.api_post_call(
2661 p_return_status =>l_return_status,
2662 x_num_warnings =>l_number_of_warnings,
2663 x_num_errors =>l_number_of_errors,
2664 p_msg_data =>l_msg_data);
2665
2666
2667 IF l_number_of_errors > 0
2668 THEN
2669 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2670 RAISE FND_API.G_EXC_ERROR;
2671 ELSIF l_number_of_warnings > 0
2672 THEN
2673 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2674 ELSE
2675 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2676 END IF;
2677 --}
2678 END IF;
2679
2680 -- Standard call to get message count and if count is 1,get message info.
2681 --
2682 FND_MSG_PUB.Count_And_Get
2683 (
2684 p_count => x_msg_count,
2685 p_data => x_msg_data,
2686 p_encoded => FND_API.G_FALSE
2687 );
2688
2689
2690 --
2691 --
2692
2693 IF l_debug_on THEN
2694 WSH_DEBUG_SV.pop(l_module_name);
2695 END IF;
2696
2697 --}
2698 EXCEPTION
2699 --{
2700 WHEN FND_API.G_EXC_ERROR THEN
2701 ROLLBACK TO REMOVE_SERVICE_APPLY_NEXT_PUB;
2702 x_return_status := FND_API.G_RET_STS_ERROR ;
2703 FND_MSG_PUB.Count_And_Get
2704 (
2705 p_count => x_msg_count,
2706 p_data => x_msg_data,
2707 p_encoded => FND_API.G_FALSE
2708 );
2709 IF l_debug_on THEN
2710 WSH_DEBUG_SV.pop(l_module_name);
2711 END IF;
2712
2713 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2714 ROLLBACK TO REMOVE_SERVICE_APPLY_NEXT_PUB;
2715 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2716 FND_MSG_PUB.Count_And_Get
2717 (
2718 p_count => x_msg_count,
2719 p_data => x_msg_data,
2720 p_encoded => FND_API.G_FALSE
2721 );
2722 IF l_debug_on THEN
2723 WSH_DEBUG_SV.pop(l_module_name);
2724 END IF;
2725 WHEN OTHERS THEN
2726 ROLLBACK TO REMOVE_SERVICE_APPLY_NEXT_PUB;
2727 wsh_util_core.default_handler('FTE_SS_INTERFACE.REMOVE_SERVICE_APPLY_NEXT');
2728 x_return_status := FND_API.G_RET_STS_ERROR;
2729 FND_MSG_PUB.Count_And_Get
2730 (
2731 p_count => x_msg_count,
2732 p_data => x_msg_data,
2733 p_encoded => FND_API.G_FALSE
2734 );
2735 IF l_debug_on THEN
2736 WSH_DEBUG_SV.pop(l_module_name);
2737 END IF;
2738
2739 END REMOVE_SERVICE_APPLY_NEXT;
2740
2741
2742 PROCEDURE IS_RANK_LIST_EXHAUSTED(
2743 p_init_msg_list IN VARCHAR2,
2744 x_is_exhausted OUT NOCOPY VARCHAR2,
2745 x_return_status OUT NOCOPY VARCHAR2,
2746 x_msg_count OUT NOCOPY NUMBER,
2747 x_msg_data OUT NOCOPY VARCHAR2,
2748 p_trip_id IN NUMBER)
2749 IS
2750
2751 l_api_name CONSTANT VARCHAR2(30) := 'IS_RANK_LIST_EXHAUSTED';
2752 l_api_version CONSTANT NUMBER := 1.0;
2753 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
2754 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || l_api_name;
2755
2756
2757 l_return_status VARCHAR2(32767);
2758 l_msg_count NUMBER;
2759 l_msg_data VARCHAR2(32767);
2760 l_number_of_warnings NUMBER;
2761 l_number_of_errors NUMBER;
2762
2763 l_count NUMBER;
2764
2765
2766
2767 BEGIN
2768
2769
2770 SAVEPOINT IS_RANK_LIST_EXHAUSTED_PUB;
2771 IF l_debug_on THEN
2772 WSH_DEBUG_SV.push(l_module_name);
2773 END IF;
2774
2775 IF FND_API.to_Boolean( p_init_msg_list )
2776 THEN
2777 FND_MSG_PUB.initialize;
2778 END IF;
2779
2780 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2781 x_msg_count := 0;
2782 x_msg_data := 0;
2783 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2784 l_number_of_warnings := 0;
2785 l_number_of_errors := 0;
2786
2787
2788 x_is_exhausted := 'F';
2789
2790 BEGIN
2791
2792
2793 SELECT COUNT(*) INTO l_count FROM FTE_CARRIER_RANK_LIST
2794 WHERE (VERSION IS NULL OR VERSION = 0)
2795 AND trip_id = p_trip_id;
2796
2797 IF (SQL%NOTFOUND) THEN
2798 RAISE NO_DATA_FOUND;
2799 END IF;
2800
2801 EXCEPTION
2802 WHEN NO_DATA_FOUND THEN
2803 FND_MESSAGE.SET_NAME('FTE','FTE_RANK_NOT_FOUND');
2804 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2805 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
2806 RAISE FND_API.G_EXC_ERROR;
2807
2808 END;
2809
2810 IF l_debug_on
2811 THEN
2812 Log(l_module_name,' Remaining Services in the rank list ' || l_count,
2813 WSH_DEBUG_SV.C_PROC_LEVEL);
2814 END IF;
2815
2816
2817 IF (l_count = 0)
2818 THEN
2819 x_is_exhausted := 'T';
2820 IF l_debug_on
2821 THEN
2822 Log(l_module_name,' Carrier rank list exhausted ',
2823 WSH_DEBUG_SV.C_PROC_LEVEL);
2824 END IF;
2825 ELSE
2826 IF l_debug_on
2827 THEN
2828 Log(l_module_name,' Carrier rank list not exhausted ',
2829 WSH_DEBUG_SV.C_PROC_LEVEL);
2830 END IF;
2831
2832 END IF;
2833
2834 -- Standard call to get message count and if count is 1,get message info.
2835 --
2836 FND_MSG_PUB.Count_And_Get
2837 (
2838 p_count => x_msg_count,
2839 p_data => x_msg_data,
2840 p_encoded => FND_API.G_FALSE
2841 );
2842
2843
2844 --
2845 --
2846
2847 IF l_debug_on THEN
2848 WSH_DEBUG_SV.pop(l_module_name);
2849 END IF;
2850
2851 --}
2852 EXCEPTION
2853 --{
2854 WHEN FND_API.G_EXC_ERROR THEN
2855 ROLLBACK TO IS_RANK_LIST_EXHAUSTED_PUB;
2856 x_return_status := FND_API.G_RET_STS_ERROR ;
2857 FND_MSG_PUB.Count_And_Get
2858 (
2859 p_count => x_msg_count,
2860 p_data => x_msg_data,
2861 p_encoded => FND_API.G_FALSE
2862 );
2863 IF l_debug_on THEN
2864 WSH_DEBUG_SV.pop(l_module_name);
2865 END IF;
2866
2867 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2868 ROLLBACK TO IS_RANK_LIST_EXHAUSTED_PUB;
2869 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2870 FND_MSG_PUB.Count_And_Get
2871 (
2872 p_count => x_msg_count,
2873 p_data => x_msg_data,
2874 p_encoded => FND_API.G_FALSE
2875 );
2876 IF l_debug_on THEN
2877 WSH_DEBUG_SV.pop(l_module_name);
2878 END IF;
2879 WHEN OTHERS THEN
2880 ROLLBACK TO IS_RANK_LIST_EXHAUSTED_PUB;
2881 wsh_util_core.default_handler('FTE_SS_INTERFACE.REMOVE_SERVICE_APPLY_NEXT');
2882 x_return_status := FND_API.G_RET_STS_ERROR;
2883 FND_MSG_PUB.Count_And_Get
2884 (
2885 p_count => x_msg_count,
2886 p_data => x_msg_data,
2887 p_encoded => FND_API.G_FALSE
2888 );
2889 IF l_debug_on THEN
2890 WSH_DEBUG_SV.pop(l_module_name);
2891 END IF;
2892
2893 END IS_RANK_LIST_EXHAUSTED;
2894
2895
2896 PROCEDURE PRINT_RANK_LIST(p_trip_id IN NUMBER)
2897 IS
2898
2899
2900 l_api_name CONSTANT VARCHAR2(30) := 'PRINT_RANK_LIST';
2901 l_api_version CONSTANT NUMBER := 1.0;
2902 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
2903 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || l_api_name;
2904
2905
2906 l_return_status VARCHAR2(32767);
2907 l_msg_count NUMBER;
2908 l_msg_data VARCHAR2(32767);
2909 l_number_of_warnings NUMBER;
2910 l_number_of_errors NUMBER;
2911
2912 l_result_code VARCHAR2(32767);
2913 l_rank_id NUMBER;
2914
2915 --}
2916
2917 l_current_version NUMBER;
2918
2919 CURSOR get_rank_list_c IS
2920 SELECT RANK_ID ,
2921 TRIP_ID ,
2922 RANK_SEQUENCE ,
2923 CARRIER_ID ,
2924 SERVICE_LEVEL ,
2925 MODE_OF_TRANSPORT ,
2926 LANE_ID ,
2927 SOURCE ,
2928 ENABLED ,
2929 ESTIMATED_RATE ,
2930 CURRENCY_CODE ,
2931 VEHICLE_ITEM_ID ,
2932 VEHICLE_ORG_ID ,
2933 ESTIMATED_TRANSIT_TIME ,
2934 TRANSIT_TIME_UOM ,
2935 VERSION ,
2936 CONSIGNEE_CARRIER_AC_NO ,
2937 FREIGHT_TERMS_CODE ,
2938 INITSMCONFIG ,
2939 ATTRIBUTE_CATEGORY ,
2940 ATTRIBUTE1 ,
2941 ATTRIBUTE2 ,
2942 ATTRIBUTE3 ,
2943 ATTRIBUTE4 ,
2944 ATTRIBUTE5 ,
2945 ATTRIBUTE6 ,
2946 ATTRIBUTE7 ,
2947 ATTRIBUTE8 ,
2948 ATTRIBUTE9 ,
2949 ATTRIBUTE10 ,
2950 ATTRIBUTE11 ,
2951 ATTRIBUTE12 ,
2952 ATTRIBUTE13 ,
2953 ATTRIBUTE14 ,
2954 ATTRIBUTE15 ,
2955 CREATION_DATE ,
2956 CREATED_BY ,
2957 LAST_UPDATE_DATE ,
2958 LAST_UPDATED_BY ,
2959 LAST_UPDATE_LOGIN ,
2960 'N'
2961 FROM FTE_CARRIER_RANK_LIST WHERE trip_id = p_trip_id
2962 ORDER BY RANK_ID , RANK_SEQUENCE;
2963
2964 idx NUMBER;
2965
2966 BEGIN
2967
2968
2969 SAVEPOINT PRINT_RANK_LIST_PUB;
2970 IF l_debug_on THEN
2971 WSH_DEBUG_SV.push(l_module_name);
2972 END IF;
2973
2974
2975 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2976 l_number_of_warnings := 0;
2977 l_number_of_errors := 0;
2978
2979
2980 IF l_debug_on
2981 THEN
2982 Log(l_module_name,' Getting rank list for trip ' || p_trip_id,
2983 WSH_DEBUG_SV.C_PROC_LEVEL);
2984 END IF;
2985
2986 idx := 1;
2987 FOR p_rank_info_rec IN get_rank_list_c
2988 LOOP
2989 --{
2990
2991 IF l_debug_on
2992 THEN
2993 Log(l_module_name,' p_rank_info_rec.RANK_ID ' || p_rank_info_rec.RANK_ID,WSH_DEBUG_SV.C_PROC_LEVEL);
2994 Log(l_module_name,' p_rank_info_rec.TRIP_ID ' || p_rank_info_rec.TRIP_ID ,WSH_DEBUG_SV.C_PROC_LEVEL);
2995 Log(l_module_name,' p_rank_info_rec.RANK_SEQUENCE ' || p_rank_info_rec.RANK_SEQUENCE,WSH_DEBUG_SV.C_PROC_LEVEL);
2996 Log(l_module_name,' p_rank_info_rec.CARRIER_ID ' || p_rank_info_rec.CARRIER_ID,WSH_DEBUG_SV.C_PROC_LEVEL);
2997 Log(l_module_name,' p_rank_info_rec.SERVICE_LEVEL ' || p_rank_info_rec.SERVICE_LEVEL,WSH_DEBUG_SV.C_PROC_LEVEL);
2998 Log(l_module_name,' p_rank_info_rec.MODE_OF_TRANSPORT ' || p_rank_info_rec.MODE_OF_TRANSPORT,WSH_DEBUG_SV.C_PROC_LEVEL);
2999 Log(l_module_name,' p_rank_info_rec.LANE_ID ' || p_rank_info_rec.LANE_ID,WSH_DEBUG_SV.C_PROC_LEVEL);
3000 Log(l_module_name,' p_rank_info_rec.SOURCE ' || p_rank_info_rec.SOURCE,WSH_DEBUG_SV.C_PROC_LEVEL);
3001 Log(l_module_name,' p_rank_info_rec.ESTIMATED_RATE ' || p_rank_info_rec.ESTIMATED_RATE,WSH_DEBUG_SV.C_PROC_LEVEL);
3002 Log(l_module_name,' p_rank_info_rec.CURRENCY_CODE ' || p_rank_info_rec.CURRENCY_CODE,WSH_DEBUG_SV.C_PROC_LEVEL);
3003 Log(l_module_name,' p_rank_info_rec.VEHICLE_ITEM_ID ' || p_rank_info_rec.VEHICLE_ITEM_ID,WSH_DEBUG_SV.C_PROC_LEVEL);
3004 Log(l_module_name,' p_rank_info_rec.VEHICLE_ORG_ID ' || p_rank_info_rec.VEHICLE_ORG_ID,WSH_DEBUG_SV.C_PROC_LEVEL);
3005 Log(l_module_name,' p_rank_info_rec.ESTIMATED_TRANSIT_TIME ' || p_rank_info_rec.ESTIMATED_TRANSIT_TIME,WSH_DEBUG_SV.C_PROC_LEVEL);
3006 Log(l_module_name,' p_rank_info_rec.TRANSIT_TIME_UOM ' || p_rank_info_rec.TRANSIT_TIME_UOM,WSH_DEBUG_SV.C_PROC_LEVEL);
3007 Log(l_module_name,' p_rank_info_rec.CONSIGNEE_CARRIER_AC_NO ' || p_rank_info_rec.CONSIGNEE_CARRIER_AC_NO,WSH_DEBUG_SV.C_PROC_LEVEL);
3008 Log(l_module_name,' p_rank_info_rec.FREIGHT_TERMS_CODE ' || p_rank_info_rec.FREIGHT_TERMS_CODE,WSH_DEBUG_SV.C_PROC_LEVEL);
3009 Log(l_module_name,' l_config ' || p_rank_info_rec.INITSMCONFIG ,WSH_DEBUG_SV.C_PROC_LEVEL);
3010 Log(l_module_name,' ********************************************* ' ,WSH_DEBUG_SV.C_PROC_LEVEL);
3011 END IF;
3012 --}
3013 END LOOP;
3014
3015 -- END OF
3016 IF get_rank_list_c%ISOPEN THEN
3017 CLOSE get_rank_list_c;
3018 END IF;
3019 --
3020
3021 IF l_debug_on
3022 THEN
3023 Log(l_module_name,' Done getting rank list for trip ' || p_trip_id,
3024 WSH_DEBUG_SV.C_PROC_LEVEL);
3025 END IF;
3026
3027 --
3028
3029 IF l_debug_on THEN
3030 WSH_DEBUG_SV.pop(l_module_name);
3031 END IF;
3032
3033
3034
3035 EXCEPTION
3036
3037 WHEN FND_API.G_EXC_ERROR THEN
3038 ROLLBACK TO PRINT_RANK_LIST_PUB;
3039 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3040 ROLLBACK TO PRINT_RANK_LIST_PUB;
3041 WHEN OTHERS THEN
3042 ROLLBACK TO PRINT_RANK_LIST_PUB;
3043 wsh_util_core.default_handler('FTE_CARRIER_RANK_LIST_PVT.PRINT_RANK_LIST');
3044 END PRINT_RANK_LIST;
3045
3046
3047 --{
3048
3049 PROCEDURE DELETE_RANK_LIST_UIWRAPPER(
3050 p_api_version_number IN NUMBER,
3051 p_init_msg_list IN VARCHAR2,
3052 x_return_status OUT NOCOPY VARCHAR2,
3053 x_msg_count OUT NOCOPY NUMBER,
3054 x_msg_data OUT NOCOPY VARCHAR2,
3055 p_trip_id IN FTE_ID_TAB_TYPE)
3056 IS
3057
3058 l_warning_tab FTE_ID_TAB_TYPE;
3059 l_error_tab FTE_ID_TAB_TYPE;
3060 l_success_tab FTE_ID_TAB_TYPE;
3061
3062 l_return_status VARCHAR2(1);
3063
3064 l_trip_names VARCHAR2(4000);
3065 l_temp_name VARCHAR2(100);
3066
3067 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_RANK_LIST_UIWRAPPER';
3068 l_api_version CONSTANT NUMBER := 1.0;
3069 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
3070 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || l_api_name;
3071
3072
3073 BEGIN
3074
3075 SAVEPOINT DELETE_RANK_LIST_UIWRAPPER_PUB;
3076
3077 IF l_debug_on THEN
3078 WSH_DEBUG_SV.push(l_module_name);
3079 END IF;
3080
3081
3082 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3083
3084
3085 IF l_debug_on
3086 THEN
3087 Log(l_module_name,' Delete rank list ',
3088 WSH_DEBUG_SV.C_PROC_LEVEL);
3089 END IF;
3090
3091
3092 l_warning_tab := FTE_ID_TAB_TYPE();
3093 l_error_tab := FTE_ID_TAB_TYPE();
3094 l_success_tab := FTE_ID_TAB_TYPE();
3095
3096
3097 IF (p_trip_id.COUNT > 0)
3098 THEN
3099 -- loop through each trip and call delete
3100 FOR i IN p_trip_id.FIRST..p_trip_id.LAST LOOP
3101
3102 IF l_debug_on
3103 THEN
3104 Log(l_module_name,' Calling DELETE_RANK_LIST for trip ' || p_trip_id(i) ,
3105 WSH_DEBUG_SV.C_PROC_LEVEL);
3106 END IF;
3107
3108 DELETE_RANK_LIST(p_trip_id => p_trip_id(i),
3109 x_return_status => l_return_status);
3110
3111
3112 IF l_debug_on
3113 THEN
3114 Log(l_module_name,' Return message after delete rank for trip ' || p_trip_id(i) || ' ' ||
3115 l_return_status,
3116 WSH_DEBUG_SV.C_PROC_LEVEL);
3117 END IF;
3118
3119
3120 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
3121 THEN
3122 l_error_tab.EXTEND;
3123 l_error_tab(l_error_tab.COUNT) := p_trip_id(i);
3124 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3125
3126 -- Get the trip name
3127 SELECT NAME INTO l_temp_name FROM WSH_TRIPS
3128 WHERE TRIP_ID = p_trip_id(i);
3129
3130 l_trip_names := l_trip_names || ', ' || l_temp_name;
3131 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING)
3132 THEN
3133 l_warning_tab.EXTEND;
3134 l_warning_tab(l_warning_tab.COUNT) := p_trip_id(i);
3135 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3136 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS)
3137 THEN
3138 l_success_tab.EXTEND;
3139 l_success_tab(l_success_Tab.COUNT) := p_trip_id(i);
3140 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3141 ELSE
3142 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3143 END IF;
3144
3145
3146 END LOOP;
3147 ELSE
3148
3149 IF l_debug_on
3150 THEN
3151 Log(l_module_name,' Trip id table is empty ',
3152 WSH_DEBUG_SV.C_PROC_LEVEL);
3153 END IF;
3154
3155 END IF;
3156
3157 IF l_debug_on
3158 THEN
3159 Log(l_module_name,' Success count ' || l_success_tab.count,
3160 WSH_DEBUG_SV.C_PROC_LEVEL);
3161 Log(l_module_name,' Error count ' || l_error_tab.count,
3162 WSH_DEBUG_SV.C_PROC_LEVEL);
3163 Log(l_module_name,' Warning count ' || l_warning_tab.count,
3164 WSH_DEBUG_SV.C_PROC_LEVEL);
3165 Log(l_module_name,' Trip count ' || p_trip_id.count,
3166 WSH_DEBUG_SV.C_PROC_LEVEL);
3167 END IF;
3168
3169
3170 IF (l_success_tab.count = p_trip_id.count)
3171 THEN
3172 --FND_MESSAGE.SET_NAME('FTE','FTE_MULTI_RANK_DEL_SUCCESS');
3173 --FND_MSG_PUB.ADD;
3174 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3175 ELSIF (l_error_tab.count = p_trip_id.count)
3176 THEN
3177 FND_MESSAGE.SET_NAME('FTE','FTE_MULTI_RANK_DEL_FAIL');
3178 FND_MESSAGE.SET_TOKEN('TRIP_NAMES', l_trip_names);
3179 FND_MSG_PUB.ADD;
3180 RAISE FND_API.G_EXC_ERROR;
3181 ELSE
3182 FND_MESSAGE.SET_NAME('FTE','FTE_MULTI_RANK_DEL_WARN');
3183 FND_MESSAGE.SET_TOKEN('TRIP_NAMES', l_trip_names);
3184 FND_MSG_PUB.ADD;
3185 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3186 END IF;
3187
3188
3189 FND_MSG_PUB.Count_And_Get
3190 (
3191 p_count => x_msg_count,
3192 p_data => x_msg_data,
3193 p_encoded => FND_API.G_FALSE
3194 );
3195
3196
3197 EXCEPTION
3198
3199 WHEN FND_API.G_EXC_ERROR THEN
3200 ROLLBACK TO DELETE_RANK_LIST_UIWRAPPER_PUB;
3201 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3202 FND_MSG_PUB.Count_And_Get
3203 (
3204 p_count => x_msg_count,
3205 p_data => x_msg_data,
3206 p_encoded => FND_API.G_FALSE
3207 );
3208
3209 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3210 ROLLBACK TO DELETE_RANK_LIST_UIWRAPPER_PUB;
3211 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3212 FND_MSG_PUB.Count_And_Get
3213 (
3214 p_count => x_msg_count,
3215 p_data => x_msg_data,
3216 p_encoded => FND_API.G_FALSE
3217 );
3218 WHEN OTHERS THEN
3219 ROLLBACK TO DELETE_RANK_LIST_UIWRAPPER_PUB;
3220 wsh_util_core.default_handler('FTE_CARRIER_RANK_LIST_PVT.DELETE_RANK_LIST_UIWRAPPER');
3221 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3222 FND_MSG_PUB.Count_And_Get
3223 (
3224 p_count => x_msg_count,
3225 p_data => x_msg_data,
3226 p_encoded => FND_API.G_FALSE
3227 );
3228
3229
3230 END DELETE_RANK_LIST_UIWRAPPER;
3231
3232
3233 --}
3234
3235
3236
3237 --{
3238
3239 PROCEDURE CREATE_RANK_LIST_BULK(
3240 p_api_version_number IN NUMBER,
3241 p_init_msg_list IN VARCHAR2,
3242 x_return_status OUT NOCOPY VARCHAR2,
3243 x_msg_count OUT NOCOPY NUMBER,
3244 x_msg_data OUT NOCOPY VARCHAR2,
3245 p_ranklist IN OUT NOCOPY carrier_rank_list_bulk_rec)
3246 IS
3247
3248 l_return_status VARCHAR2(1);
3249
3250 l_trip_names VARCHAR2(4000);
3251 l_temp_name VARCHAR2(100);
3252 l_trip_id NUMBER;
3253 l_trip_name VARCHAR2(100);
3254
3255 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_RANK_LIST_BULK';
3256 l_api_version CONSTANT NUMBER := 1.0;
3257 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
3258 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || l_api_name;
3259
3260 l_temp_table carrier_rank_list_bulk_rec;
3261
3262 CURSOR get_trip (trip_id NUMBER) IS
3263 SELECT trip_id FROM FTE_CARRIER_RANK_LIST
3264 WHERE TRIP_ID = trip_id
3265 AND ROWNUM = 1;
3266
3267 idx NUMBER;
3268
3269 BEGIN
3270
3271 SAVEPOINT CREATE_RANK_LIST_BULK_PUB;
3272
3273 IF l_debug_on THEN
3274 WSH_DEBUG_SV.push(l_module_name);
3275 END IF;
3276
3277
3278 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3279 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3280 x_msg_count := 0;
3281
3282 IF l_debug_on
3283 THEN
3284 Log(l_module_name,' Loop through all the rank list table and check failure cases ',
3285 WSH_DEBUG_SV.C_PROC_LEVEL);
3286 END IF;
3287
3288 idx := 1;
3289 FOR i IN p_ranklist.TRIP_ID.FIRST..p_ranklist.TRIP_ID.LAST
3290 LOOP
3291
3292 IF l_debug_on
3293 THEN
3294 Log(l_module_name,' Trip Id ' || p_ranklist.TRIP_ID(i) || ' i value ' || i,
3295 WSH_DEBUG_SV.C_PROC_LEVEL);
3296 END IF;
3297
3298
3299 BEGIN
3300
3301 SELECT trip_id INTO l_trip_id
3302 FROM FTE_CARRIER_RANK_LIST
3303 WHERE TRIP_ID = p_ranklist.TRIP_ID(i)
3304 AND ROWNUM = 1;
3305
3306 IF (SQL%NOTFOUND) THEN
3307 RAISE NO_DATA_FOUND;
3308 ELSE
3309
3310 IF l_debug_on
3311 THEN
3312 Log(l_module_name,' Rank list exist for trip ' || p_ranklist.TRIP_ID(i),
3313 WSH_DEBUG_SV.C_PROC_LEVEL);
3314 END IF;
3315
3316 SELECT NAME INTO l_trip_name FROM WSH_TRIPS WHERE TRIP_ID = p_ranklist.TRIP_ID(i);
3317 l_trip_names := l_trip_names || ', ' || l_trip_name;
3318 END IF;
3319
3320 EXCEPTION
3321 WHEN NO_DATA_FOUND THEN
3322
3323 SELECT FTE_CARRIER_RANK_LIST_S.NEXTVAL INTO
3324 p_ranklist.RANK_ID(i) FROM DUAL;
3325
3326 IF l_debug_on
3327 THEN
3328 Log(l_module_name,' Rank Id ' || p_ranklist.RANK_ID(i) || ' i value ' || i,
3329 WSH_DEBUG_SV.C_PROC_LEVEL);
3330 END IF;
3331
3332
3333 l_temp_table.RANK_ID(idx) := p_ranklist.RANK_ID(i);
3334 l_temp_table.TRIP_ID(idx) := p_ranklist.TRIP_ID(i);
3335 l_temp_table.RANK_SEQUENCE(idx) := p_ranklist.RANK_SEQUENCE(i);
3336 l_temp_table.CARRIER_ID(idx) := p_ranklist.CARRIER_ID(i);
3337 l_temp_table.SERVICE_LEVEL(idx) := p_ranklist.SERVICE_LEVEL(i);
3338 l_temp_table.MODE_OF_TRANSPORT(idx) := p_ranklist.MODE_OF_TRANSPORT(i);
3339 l_temp_table.LANE_ID(idx) := p_ranklist.LANE_ID(i);
3340 l_temp_table.SOURCE(idx) := p_ranklist.SOURCE(i);
3341 l_temp_table.ESTIMATED_RATE(idx) := p_ranklist.ESTIMATED_RATE(i);
3342 l_temp_table.CURRENCY_CODE(idx) := p_ranklist.CURRENCY_CODE(i);
3343 l_temp_table.VEHICLE_ITEM_ID(idx) := p_ranklist.VEHICLE_ITEM_ID(i);
3344 l_temp_table.VEHICLE_ORG_ID(idx) := p_ranklist.VEHICLE_ORG_ID(i);
3345 l_temp_table.ESTIMATED_TRANSIT_TIME(idx) := p_ranklist.ESTIMATED_TRANSIT_TIME(i);
3346 l_temp_table.TRANSIT_TIME_UOM(idx) := p_ranklist.TRANSIT_TIME_UOM(i);
3347 l_temp_table.CONSIGNEE_CARRIER_AC_NO(idx) := p_ranklist.CONSIGNEE_CARRIER_AC_NO(i);
3348 l_temp_table.FREIGHT_TERMS_CODE(idx) := p_ranklist.FREIGHT_TERMS_CODE(i);
3349 l_temp_table.ATTRIBUTE_CATEGORY(idx) := p_ranklist.ATTRIBUTE_CATEGORY(i);
3350 l_temp_table.ATTRIBUTE1(idx) := p_ranklist.ATTRIBUTE1(i);
3351 l_temp_table.ATTRIBUTE2(idx) := p_ranklist.ATTRIBUTE2(i);
3352 l_temp_table.ATTRIBUTE3(idx) := p_ranklist.ATTRIBUTE3(i);
3353 l_temp_table.ATTRIBUTE4(idx) := p_ranklist.ATTRIBUTE4(i);
3354 l_temp_table.ATTRIBUTE5(idx) := p_ranklist.ATTRIBUTE5(i);
3355 l_temp_table.ATTRIBUTE6(idx) := p_ranklist.ATTRIBUTE6(i);
3356 l_temp_table.ATTRIBUTE7(idx) := p_ranklist.ATTRIBUTE7(i);
3357 l_temp_table.ATTRIBUTE8(idx) := p_ranklist.ATTRIBUTE8(i);
3358 l_temp_table.ATTRIBUTE9(idx) := p_ranklist.ATTRIBUTE9(i);
3359 l_temp_table.ATTRIBUTE10(idx) := p_ranklist.ATTRIBUTE10(i);
3360 l_temp_table.ATTRIBUTE11(idx) := p_ranklist.ATTRIBUTE11(i);
3361 l_temp_table.ATTRIBUTE12(idx) := p_ranklist.ATTRIBUTE12(i);
3362 l_temp_table.ATTRIBUTE13(idx) := p_ranklist.ATTRIBUTE13(i);
3363 l_temp_table.ATTRIBUTE14(idx) := p_ranklist.ATTRIBUTE14(i);
3364 l_temp_table.ATTRIBUTE15(idx) := p_ranklist.ATTRIBUTE15(i);
3365
3366 idx := idx+1;
3367 END;
3368
3369
3370 END LOOP;
3371
3372 IF l_debug_on
3373 THEN
3374 Log(l_module_name,' After copy ',WSH_DEBUG_SV.C_PROC_LEVEL);
3375 END IF;
3376
3377
3378 IF (l_temp_table.RANK_ID.COUNT > 0)
3379 THEN
3380 FORALL i IN l_temp_table.RANK_ID.FIRST..l_temp_table.RANK_ID.LAST
3381 insert into FTE_CARRIER_RANK_LIST
3382 ( RANK_ID ,
3383 TRIP_ID ,
3384 RANK_SEQUENCE ,
3385 CARRIER_ID ,
3386 SERVICE_LEVEL ,
3387 MODE_OF_TRANSPORT ,
3388 LANE_ID ,
3389 SOURCE ,
3390 ENABLED ,
3391 ESTIMATED_RATE ,
3392 CURRENCY_CODE ,
3393 VEHICLE_ITEM_ID ,
3394 VEHICLE_ORG_ID ,
3395 ESTIMATED_TRANSIT_TIME ,
3396 TRANSIT_TIME_UOM ,
3397 VERSION ,
3398 CONSIGNEE_CARRIER_AC_NO ,
3399 FREIGHT_TERMS_CODE ,
3400 INITSMCONFIG ,
3401 CALL_RG_FLAG ,
3402 ATTRIBUTE_CATEGORY ,
3403 ATTRIBUTE1 ,
3404 ATTRIBUTE2 ,
3405 ATTRIBUTE3 ,
3406 ATTRIBUTE4 ,
3407 ATTRIBUTE5 ,
3408 ATTRIBUTE6 ,
3409 ATTRIBUTE7 ,
3410 ATTRIBUTE8 ,
3411 ATTRIBUTE9 ,
3412 ATTRIBUTE10 ,
3413 ATTRIBUTE11 ,
3414 ATTRIBUTE12 ,
3415 ATTRIBUTE13 ,
3416 ATTRIBUTE14 ,
3417 ATTRIBUTE15 ,
3418 CREATION_DATE ,
3419 CREATED_BY ,
3420 LAST_UPDATE_DATE ,
3421 LAST_UPDATED_BY ,
3422 LAST_UPDATE_LOGIN
3423 )
3424 values
3425 (
3426 l_temp_table.RANK_ID(i),
3427 l_temp_table.TRIP_ID(i),
3428 l_temp_table.RANK_SEQUENCE(i),
3429 decode(l_temp_table.CARRIER_ID(i),FND_API.G_MISS_NUM,NULL,l_temp_table.CARRIER_ID(i)),
3430 decode(l_temp_table.SERVICE_LEVEL(i),FND_API.G_MISS_CHAR,NULL,l_temp_table.SERVICE_LEVEL(i)),
3431 decode(l_temp_table.MODE_OF_TRANSPORT(i),FND_API.G_MISS_CHAR,NULL,l_temp_table.MODE_OF_TRANSPORT(i)),
3432 decode(l_temp_table.LANE_ID(i),FND_API.G_MISS_NUM,NULL,l_temp_table.LANE_ID(i)),
3433 decode(l_temp_table.SOURCE(i),FND_API.G_MISS_CHAR,NULL,l_temp_table.SOURCE(i)),
3434 'Y',
3435 decode(l_temp_table.ESTIMATED_RATE(i),FND_API.G_MISS_NUM,NULL,l_temp_table.ESTIMATED_RATE(i)),
3436 decode(l_temp_table.CURRENCY_CODE(i),FND_API.G_MISS_CHAR,NULL,l_temp_table.CURRENCY_CODE(i)),
3437 decode(l_temp_table.VEHICLE_ITEM_ID(i),FND_API.G_MISS_NUM,NULL,l_temp_table.VEHICLE_ITEM_ID(i)),
3438 decode(l_temp_table.VEHICLE_ORG_ID(i),FND_API.G_MISS_NUM,NULL,l_temp_table.VEHICLE_ORG_ID(i)),
3439 decode(l_temp_table.ESTIMATED_TRANSIT_TIME(i),FND_API.G_MISS_NUM,NULL,l_temp_table.ESTIMATED_TRANSIT_TIME(i)),
3440 decode(l_temp_table.TRANSIT_TIME_UOM(i),FND_API.G_MISS_CHAR,NULL,l_temp_table.TRANSIT_TIME_UOM(i)),
3441 0,
3442 decode(l_temp_table.CONSIGNEE_CARRIER_AC_NO(i),FND_API.G_MISS_CHAR,NULL,l_temp_table.CONSIGNEE_CARRIER_AC_NO(i)),
3443 decode(l_temp_table.FREIGHT_TERMS_CODE(i),FND_API.G_MISS_CHAR,NULL,l_temp_table.FREIGHT_TERMS_CODE(i)),
3444 'CSM',
3445 'Y',
3446 decode(l_temp_table.ATTRIBUTE_CATEGORY(i),FND_API.G_MISS_CHAR,NULL,
3447 l_temp_table.ATTRIBUTE_CATEGORY(i)),
3448 decode(l_temp_table.ATTRIBUTE1(i),FND_API.G_MISS_CHAR,NULL,l_temp_table.ATTRIBUTE1(i)),
3449 decode(l_temp_table.ATTRIBUTE2(i),FND_API.G_MISS_CHAR,NULL,l_temp_table.ATTRIBUTE2(i)),
3450 decode(l_temp_table.ATTRIBUTE3(i),FND_API.G_MISS_CHAR,NULL,l_temp_table.ATTRIBUTE3(i)),
3451 decode(l_temp_table.ATTRIBUTE4(i),FND_API.G_MISS_CHAR,NULL,l_temp_table.ATTRIBUTE4(i)),
3452 decode(l_temp_table.ATTRIBUTE5(i),FND_API.G_MISS_CHAR,NULL,l_temp_table.ATTRIBUTE5(i)),
3453 decode(l_temp_table.ATTRIBUTE6(i),FND_API.G_MISS_CHAR,NULL,l_temp_table.ATTRIBUTE6(i)),
3454 decode(l_temp_table.ATTRIBUTE7(i),FND_API.G_MISS_CHAR,NULL,l_temp_table.ATTRIBUTE7(i)),
3455 decode(l_temp_table.ATTRIBUTE8(i),FND_API.G_MISS_CHAR,NULL,l_temp_table.ATTRIBUTE8(i)),
3456 decode(l_temp_table.ATTRIBUTE9(i),FND_API.G_MISS_CHAR,NULL,l_temp_table.ATTRIBUTE9(i)),
3457 decode(l_temp_table.ATTRIBUTE10(i),FND_API.G_MISS_CHAR,NULL,l_temp_table.ATTRIBUTE10(i)),
3458 decode(l_temp_table.ATTRIBUTE11(i),FND_API.G_MISS_CHAR,NULL,l_temp_table.ATTRIBUTE11(i)),
3459 decode(l_temp_table.ATTRIBUTE12(i),FND_API.G_MISS_CHAR,NULL,l_temp_table.ATTRIBUTE12(i)),
3460 decode(l_temp_table.ATTRIBUTE13(i),FND_API.G_MISS_CHAR,NULL,l_temp_table.ATTRIBUTE13(i)),
3461 decode(l_temp_table.ATTRIBUTE14(i),FND_API.G_MISS_CHAR,NULL,l_temp_table.ATTRIBUTE14(i)),
3462 decode(l_temp_table.ATTRIBUTE15(i),FND_API.G_MISS_CHAR,NULL,l_temp_table.ATTRIBUTE15(i)),
3463 SYSDATE,
3464 FND_GLOBAL.USER_ID,
3465 SYSDATE,
3466 FND_GLOBAL.USER_ID,
3467 FND_GLOBAL.USER_ID
3468 );
3469
3470 IF l_debug_on
3471 THEN
3472 Log(l_module_name,' After Insert ',WSH_DEBUG_SV.C_PROC_LEVEL);
3473 END IF;
3474
3475
3476
3477 FOR i IN l_temp_table.TRIP_ID.FIRST..l_temp_table.TRIP_ID.LAST
3478 LOOP
3479 UPDATE WSH_TRIPS
3480 SET RANK_ID = l_temp_table.RANK_ID(i)
3481 WHERE TRIP_ID = l_temp_table.TRIP_ID(i);
3482
3483 IF l_debug_on
3484 THEN
3485 Log(l_module_name,' Rank Id ' || l_temp_table.RANK_ID(i),WSH_DEBUG_SV.C_PROC_LEVEL);
3486 END IF;
3487 END LOOP;
3488 END IF;
3489
3490 IF (l_trip_names IS NOT NULL)
3491 THEN
3492
3493 FND_MESSAGE.SET_NAME('FTE','FTE_RANK_CRE_FAILED_TRIPS');
3494 FND_MESSAGE.SET_TOKEN('trip_names', l_trip_names);
3495 FND_MSG_PUB.ADD;
3496 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3497 END IF;
3498
3499 EXCEPTION
3500
3501 WHEN FND_API.G_EXC_ERROR THEN
3502 ROLLBACK TO CREATE_RANK_LIST_BULK_PUB;
3503 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3504 FND_MSG_PUB.Count_And_Get
3505 (
3506 p_count => x_msg_count,
3507 p_data => x_msg_data,
3508 p_encoded => FND_API.G_FALSE
3509 );
3510
3511 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3512 ROLLBACK TO CREATE_RANK_LIST_BULK_PUB;
3513 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3514 FND_MSG_PUB.Count_And_Get
3515 (
3516 p_count => x_msg_count,
3517 p_data => x_msg_data,
3518 p_encoded => FND_API.G_FALSE
3519 );
3520 WHEN OTHERS THEN
3521 ROLLBACK TO CREATE_RANK_LIST_BULK_PUB;
3522 wsh_util_core.default_handler('FTE_CARRIER_RANK_LIST_PVT.CREATE_RANK_LIST_BULK');
3523 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3524 FND_MSG_PUB.Count_And_Get
3525 (
3526 p_count => x_msg_count,
3527 p_data => x_msg_data,
3528 p_encoded => FND_API.G_FALSE
3529 );
3530
3531
3532 END CREATE_RANK_LIST_BULK;
3533
3534
3535 --}
3536
3537
3538 --
3539 END FTE_CARRIER_RANK_LIST_PVT;