[Home] [Help]
PACKAGE BODY: APPS.FTE_TRIP_RATING_GRP
Source
1 PACKAGE BODY FTE_TRIP_RATING_GRP AS
2 /* $Header: FTEGTRRB.pls 120.11 2005/08/22 14:47:10 susurend ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'FTE_TRIP_RATING_GRP';
5
6
7 SORT_TYPE_RL CONSTANT NUMBER :=1;
8 SORT_TYPE_UI CONSTANT NUMBER :=2;
9
10 DELIVERY_TYPE_STANDARD CONSTANT NUMBER :=1;
11 DELIVERY_TYPE_CONSOL CONSTANT NUMBER :=2;
12 DELIVERY_TYPE_CHILD CONSTANT NUMBER :=3;
13
14 TYPE trip_info_rec IS RECORD
15 (
16 trip_id NUMBER,
17 name VARCHAR2(30),
18 planned_flag VARCHAR2(1),
19 status_code VARCHAR2(2),
20 carrier_id NUMBER,
21 ship_method_code VARCHAR2(30),
22 service_level VARCHAR2(30),
23 mode_of_transport VARCHAR2(30),
24 lane_id NUMBER,
25 schedule_id NUMBER,
26 load_tender_status wsh_trips.load_tender_status%TYPE,
27 tp_plan_name wsh_trips.tp_plan_name%TYPE,
28 move_id NUMBER
29 );
30
31 TYPE trip_info_tab IS TABLE OF trip_info_rec INDEX BY BINARY_INTEGER;
32
33 CURSOR c_trip_info(c_trip_id NUMBER)
34 IS
35 SELECT wt.trip_id,
36 wt.name,
37 wt.planned_flag,
38 wt.status_code,
39 wt.carrier_id,
40 wt.ship_method_code,
41 wt.service_level,
42 wt.mode_of_transport,
43 wt.lane_id,
44 wt.schedule_id,
45 wt.load_tender_status,
46 wt.tp_plan_name,
47 ftm.move_id
48 FROM wsh_trips wt, fte_trip_moves ftm
49 WHERE wt.trip_id = c_trip_id
50 AND wt.trip_id = ftm.trip_id (+) ;
51
52
53 g_finished_success EXCEPTION;
54
55 PROCEDURE Compare_Sort_Value(
56 p_value1 IN Sort_Value_Rec_Type,
57 p_value2 IN Sort_Value_Rec_Type,
58 x_result OUT NOCOPY VARCHAR2,
59 x_return_status OUT NOCOPY VARCHAR2)
60 IS
61
62 i NUMBER;
63
64 l_return_status VARCHAR2(1);
65
66 l_log_level NUMBER := FTE_FREIGHT_PRICING_UTIL.G_DBG;
67
68 l_warning_count NUMBER:=0;
69
70 BEGIN
71
72 FTE_FREIGHT_PRICING_UTIL.reset_dbg_vars;
73 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,'Compare_Sort_Value','start');
74
75 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
76
77
78
79 x_result:='=';
80 i:=p_value1.value.FIRST;
81
82 -- p_value1, p_value2 should have the same fields existing
83
84 WHILE((i IS NOT NULL) AND (x_result='='))
85 LOOP
86 IF (p_value1.value.EXISTS(i) AND p_value2.value.EXISTS(i))
87 THEN
88 IF((p_value1.value(i) IS NULL ) AND (p_value2.value(i) IS NULL))
89 THEN
90 NULL;
91
92 ELSIF((p_value1.value(i) IS NOT NULL ) AND (p_value2.value(i) IS NULL))
93 THEN
94 --not null < null
95
96 x_result:='<';
97
98 ELSIF((p_value1.value(i) IS NULL ) AND (p_value2.value(i) IS NOT NULL))
99 THEN
100
101 --null > not null
102
103 x_result:='>';
104
105 ELSIF(p_value1.value(i) <p_value2.value(i))
106 THEN
107 x_result:='<';
108
109 ELSIF(p_value1.value(i) > p_value2.value(i))
110 THEN
111 x_result:='>';
112
113 ELSIF(p_value1.value(i) = p_value2.value(i))
114 THEN
115 NULL;
116
117 END IF;
118
119
120 END IF;
121
122 i:=p_value1.value.NEXT(i);
123
124 END LOOP;
125
126
127
128 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Compare_Sort_Value');
129
130 IF (l_warning_count > 0)
131 THEN
132 x_return_status:=WSH_UTIL_CORE.G_RET_STS_WARNING;
133 END IF;
134
135 EXCEPTION
136
137
138
139 WHEN others THEN
140 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
141 FTE_FREIGHT_PRICING_UTIL.set_exception('Compare_Sort_Value',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_others');
142 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
143 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Compare_Sort_Value');
144
145
146
147
148 END Compare_Sort_Value;
149
150
151
152 PROCEDURE Partition(
153 p_values_tab IN Sort_Value_Tab_Type,
154 p_low_index IN NUMBER,
155 p_hi_index IN NUMBER,
156 p_sort_type IN VARCHAR2,--To support variations in the future
157 x_sorted_index IN OUT NOCOPY dbms_utility.number_array,
158 x_partition_index OUT NOCOPY NUMBER,
159 x_return_status OUT NOCOPY VARCHAR2)
160 IS
161
162 i NUMBER;
163 j NUMBER;
164 l_pivot Sort_Value_Rec_Type;
165 l_result VARCHAR2(1);
166 l_temp NUMBER;
167
168 l_return_status VARCHAR2(1);
169
170 l_log_level NUMBER := FTE_FREIGHT_PRICING_UTIL.G_DBG;
171
172 l_warning_count NUMBER:=0;
173
174 BEGIN
175
176 FTE_FREIGHT_PRICING_UTIL.reset_dbg_vars;
177 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,'Partition','start');
178
179 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
180
181 j:=p_hi_index+1;
182 i:=p_low_index-1;
183 x_partition_index:=p_low_index;
184 l_pivot:=p_values_tab(x_sorted_index(x_partition_index));
185 WHILE(i<j)
186 LOOP
187
188 l_result:='>';
189 WHILE((l_result='>') AND (j>=i))
190 LOOP
191 j:=j-1;
192
193 Compare_Sort_Value(
194 p_value1=>p_values_tab(x_sorted_index(j)),
195 p_value2=>l_pivot ,
196 x_result=> l_result,
197 x_return_status=>l_return_status);
198
199 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
200 THEN
201 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
202 THEN
203 raise FTE_FREIGHT_PRICING_UTIL.g_quicksort_compare_fail;
204
205 END IF;
206 END IF;
207
208
209
210
211 END LOOP;
212
213 l_result:='<';
214 WHILE((l_result='<') AND (i<=j))
215 LOOP
216 i:=i+1;
217
218 Compare_Sort_Value(
219 p_value1=>p_values_tab(x_sorted_index(i)),
220 p_value2=>l_pivot ,
221 x_result=> l_result,
222 x_return_status=>l_return_status);
223
224
225 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
226 THEN
227 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
228 THEN
229 raise FTE_FREIGHT_PRICING_UTIL.g_quicksort_compare_fail;
230
231 END IF;
232 END IF;
233
234
235
236
237 END LOOP;
238
239
240 IF (i < j)
241 THEN
242
243 l_temp:=x_sorted_index(j);
244 x_sorted_index(j):=x_sorted_index(i);
245 x_sorted_index(i):=l_temp;
246
247
248 END IF;
249
250
251
252
253 END LOOP;
254
255
256 x_partition_index:=j;
257
258
259
260 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Partition');
261
262 IF (l_warning_count > 0)
263 THEN
264 x_return_status:=WSH_UTIL_CORE.G_RET_STS_WARNING;
265 END IF;
266
267 EXCEPTION
268
269
270 WHEN FTE_FREIGHT_PRICING_UTIL.g_quicksort_compare_fail THEN
271 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
272 FTE_FREIGHT_PRICING_UTIL.set_exception('Partition',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_quicksort_compare_fail');
273 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Partition');
274
275
276
277 WHEN others THEN
278 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
279 FTE_FREIGHT_PRICING_UTIL.set_exception('Partition',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_others');
280 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
281 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Partition');
282
283
284
285
286 END Partition;
287
288
289
290 PROCEDURE Quick_Sort(
291 p_values_tab IN Sort_Value_Tab_Type,
292 p_low_index IN NUMBER,
293 p_hi_index IN NUMBER,
294 p_sort_type IN VARCHAR2,--To support variations in the future
295 x_sorted_index IN OUT NOCOPY dbms_utility.number_array,
296 x_return_status OUT NOCOPY VARCHAR2)
297 IS
298
299 l_partition_index NUMBER;
300
301
302
303 l_return_status VARCHAR2(1);
304
305 l_log_level NUMBER := FTE_FREIGHT_PRICING_UTIL.G_DBG;
306
307 l_warning_count NUMBER:=0;
308
309 BEGIN
310
311 FTE_FREIGHT_PRICING_UTIL.reset_dbg_vars;
312 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,'Quick_Sort','start');
313
314 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
315
316 IF(p_low_index < p_hi_index)
317 THEN
318
319 Partition(
320 p_values_tab=>p_values_tab,
321 p_low_index=>p_low_index,
322 p_hi_index=>p_hi_index,
323 p_sort_type=>p_sort_type,
324 x_sorted_index=>x_sorted_index,
325 x_partition_index=>l_partition_index,
326 x_return_status=>l_return_status);
327
328 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
329 THEN
330 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
331 THEN
332 raise FTE_FREIGHT_PRICING_UTIL.g_quicksort_partition_fail;
333
334 END IF;
335 END IF;
336
337 Quick_Sort(
338 p_values_tab=>p_values_tab,
339 p_low_index=>p_low_index,
340 p_hi_index=>l_partition_index,
341 p_sort_type=>p_sort_type,
342 x_sorted_index=>x_sorted_index,
343 x_return_status=>l_return_status);
344 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
345 THEN
346 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
347 THEN
348 raise FTE_FREIGHT_PRICING_UTIL.g_quicksort_fail;
349
350 END IF;
351 END IF;
352
353
354 Quick_Sort(
355 p_values_tab=>p_values_tab,
356 p_low_index=>l_partition_index+1,
357 p_hi_index=>p_hi_index,
358 p_sort_type=>p_sort_type,
359 x_sorted_index=>x_sorted_index,
360 x_return_status=>l_return_status);
361 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
362 THEN
363 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
364 THEN
365 raise FTE_FREIGHT_PRICING_UTIL.g_quicksort_fail;
366
367 END IF;
368 END IF;
369
370
371
372 END IF;
373
374
375 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Quick_Sort');
376
377 IF (l_warning_count > 0)
378 THEN
379 x_return_status:=WSH_UTIL_CORE.G_RET_STS_WARNING;
380 END IF;
381
382 EXCEPTION
383
384
385 WHEN FTE_FREIGHT_PRICING_UTIL.g_quicksort_fail THEN
389
386 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
387 FTE_FREIGHT_PRICING_UTIL.set_exception('Quick_Sort',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_quicksort_fail');
388 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Quick_Sort');
390
391 WHEN FTE_FREIGHT_PRICING_UTIL.g_quicksort_partition_fail THEN
392 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
393 FTE_FREIGHT_PRICING_UTIL.set_exception('Quick_Sort',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_quicksort_partition_fail');
394 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Quick_Sort');
395
396
397 WHEN others THEN
398 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
399 FTE_FREIGHT_PRICING_UTIL.set_exception('Quick_Sort',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_others');
400 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
401 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Quick_Sort');
402
403
404
405 END Quick_Sort;
406
407
408 PROCEDURE Sort(
409 p_values_tab IN Sort_Value_Tab_Type,
410 p_sort_type IN VARCHAR2,--To support variations in the future
411 x_sorted_index OUT NOCOPY dbms_utility.number_array,
412 x_return_status OUT NOCOPY VARCHAR2)
413
414 IS
415 l_return_status VARCHAR2(1);
416
417 i NUMBER;
418
419 l_log_level NUMBER := FTE_FREIGHT_PRICING_UTIL.G_DBG;
420
421 l_warning_count NUMBER:=0;
422 BEGIN
423
424 FTE_FREIGHT_PRICING_UTIL.reset_dbg_vars;
425 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,'Sort','start');
426
427 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
428
429
430 i:=p_values_tab.FIRST;
431 WHILE(i IS NOT NULL)
432 LOOP
433
434 x_sorted_index(i):=i;
435 i:=p_values_tab.NEXT(i);
436 END LOOP;
437
438
439 IF (p_values_tab.COUNT > 1)
440 THEN
441
442
443 Quick_Sort(
444 p_values_tab=>p_values_tab,
445 p_low_index=>p_values_tab.FIRST,
446 p_hi_index=>p_values_tab.LAST,
447 p_sort_type=>NULL,--To support variations in the future
448 x_sorted_index=>x_sorted_index,
449 x_return_status=>l_return_status);
450
451 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
452 THEN
453 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
454 THEN
455 raise FTE_FREIGHT_PRICING_UTIL.g_quicksort_fail;
456
457 END IF;
458 END IF;
459
460
461
462 END IF;
463
464 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Sort');
465
466 IF (l_warning_count > 0)
467 THEN
468 x_return_status:=WSH_UTIL_CORE.G_RET_STS_WARNING;
469 END IF;
470
471 EXCEPTION
472
473
474 WHEN FTE_FREIGHT_PRICING_UTIL.g_quicksort_fail THEN
475 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
476 FTE_FREIGHT_PRICING_UTIL.set_exception('Sort',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_quicksort_fail');
477 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Sort');
478
479
480 WHEN others THEN
481 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
482 FTE_FREIGHT_PRICING_UTIL.set_exception('Sort',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_others');
483 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
484 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Sort');
485
486
487 END Sort;
488
489
490
491
492 PROCEDURE Compare_Trip_Rates (
493 p_api_version IN NUMBER DEFAULT 1.0,
494 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
495 p_init_prc_log IN VARCHAR2 DEFAULT 'Y',
496 p_trip_id IN NUMBER DEFAULT NULL,
497 p_lane_sched_id_tab IN FTE_ID_TAB_TYPE, -- lane_ids or schedule_ids
498 p_lane_sched_tab IN FTE_CODE_TAB_TYPE, -- 'L' or 'S' (Lane or Schedule)
499 p_mode_tab IN FTE_CODE_TAB_TYPE,
500 p_service_type_tab IN FTE_CODE_TAB_TYPE,
501 p_vehicle_type_tab IN FTE_ID_TAB_TYPE,
502 p_dep_date IN DATE DEFAULT sysdate,
503 p_arr_date IN DATE DEFAULT sysdate,
504 p_event IN VARCHAR2 DEFAULT 'FTE_TRIP_COMP',
505 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
506 x_request_id OUT NOCOPY NUMBER,
507 x_lane_sched_id_tab OUT NOCOPY FTE_ID_TAB_TYPE, -- lane_ids or schedule_ids
508 x_lane_sched_tab OUT NOCOPY FTE_CODE_TAB_TYPE, -- 'L' or 'S' (Lane or Schedule)
509 x_vehicle_type_tab OUT NOCOPY FTE_ID_TAB_TYPE,--Vehicle Type Id
510 x_mode_tab OUT NOCOPY FTE_CODE_TAB_TYPE,
511 x_service_type_tab OUT NOCOPY FTE_CODE_TAB_TYPE,
512 x_sum_rate_tab OUT NOCOPY FTE_ID_TAB_TYPE,
513 x_sum_rate_curr_tab OUT NOCOPY FTE_CODE_TAB_TYPE,
514 x_return_status OUT NOCOPY VARCHAR2,
518 CURSOR c_get_req_id IS
515 x_msg_count OUT NOCOPY NUMBER,
516 x_msg_data OUT NOCOPY VARCHAR2) IS
517
519 SELECT fte_pricing_comp_request_s.nextval
520 FROM sys.dual;
521
522 i NUMBER :=0;
523 L NUMBER :=0;
524 S NUMBER :=0;
525 j NUMBER :=0;
526 l_request_id NUMBER;
527
528 -- variables for shipment_price_compare
529 l_lane_id_tab wsh_util_core.id_tab_type;
530 l_sched_id_tab wsh_util_core.id_tab_type;
531 l_service_lane_tab wsh_util_core.column_tab_type;
532 l_service_sched_tab wsh_util_core.column_tab_type;
533 l_lane_price_tab wsh_util_core.id_tab_type;
534 l_lane_curr_tab wsh_util_core.column_tab_type;
535 l_sched_price_tab wsh_util_core.id_tab_type;
536 l_sched_curr_tab wsh_util_core.column_tab_type;
537 l_lane_xref dbms_utility.number_array;
538 l_sched_xref dbms_utility.number_array;
539
540 -- variables for tl_trip_price_compare
541 l_lane_rows dbms_utility.number_array;
542 l_schedule_rows dbms_utility.number_array;
543 l_vehicle_rows dbms_utility.number_array;
544 l_lane_sched_sum_rows dbms_utility.number_array;
545 l_lane_sched_curr_rows dbms_utility.name_array;
546 l_tl_xref dbms_utility.number_array;
547
548 l_exploded_lane_rows dbms_utility.number_array;
549 l_exploded_schedule_rows dbms_utility.number_array;
550 l_exploded_vehicle_rows dbms_utility.number_array;
551 l_exploded_ref_rows dbms_utility.number_array;
552
553 l_output_count NUMBER;
554 l_ref NUMBER;
555 k NUMBER;
556
557 l_return_status VARCHAR2(1);
558 l_warn_flag VARCHAR2(1) := 'N';
559 l_log_level NUMBER := FTE_FREIGHT_PRICING_UTIL.G_LOG;
560
561 PRAGMA AUTONOMOUS_TRANSACTION;
562
563 BEGIN
564 SAVEPOINT COMPARE_TRIP_RATES;
565 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
566
567 IF FND_API.to_Boolean( p_init_msg_list )
568 THEN
569 FND_MSG_PUB.initialize;
570 END IF;
571
572 IF(p_init_prc_log='Y')
573 THEN
574
575 FTE_FREIGHT_PRICING_UTIL.initialize_logging( p_init_msg_list => p_init_msg_list,
576 x_return_status => l_return_status );
577
578 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
579 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
580 x_return_status := l_return_status;
581 RETURN;
582 END IF;
583 ELSE
584 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Initialize Logging successful ');
585 END IF;
586 END IF;
587
588 FTE_FREIGHT_PRICING_UTIL.reset_dbg_vars;
589 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,'Compare_Trip_Rates','start');
590
591 -- generate comp request id
592 -- loop over the lane/schedules
593 -- collect all LTL/PARCEL lanes together
594 -- collect all TL lanes togehter
595 -- accordingly call TL or LTL/Parcel api in batches
596 -- return rate summary and currency
597 -- return warning if only some lanes fail
598
599
600
601 OPEN c_get_req_id;
602 FETCH c_get_req_id INTO l_request_id;
603 CLOSE c_get_req_id;
604 x_request_id := l_request_id;
605
606 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'>>2 l_request_id='||l_request_id);
607
608 L := 0; S := 0; j :=0;
609 IF (p_lane_sched_id_tab.COUNT > 0) THEN
610 FOR i IN p_lane_sched_id_tab.FIRST .. p_lane_sched_id_tab.LAST
611 LOOP
612 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,' '||i||' '||p_mode_tab(i)||' '||p_lane_sched_tab(i)||' '||p_lane_sched_id_tab(i));
613 IF (p_mode_tab(i) <> 'TRUCK' ) THEN
614 IF (p_lane_sched_tab(i) = 'L') THEN
615 L := L + 1;
616 l_lane_id_tab(L) := p_lane_sched_id_tab(i);
617 l_service_lane_tab(L) := p_service_type_tab(i);
618 l_lane_xref(L) := i; -- xref to input index
619 ELSIF (p_lane_sched_tab(i) = 'S') THEN
620 S := S + 1;
621 l_sched_id_tab(S) := p_lane_sched_id_tab(i);
622 l_service_sched_tab(S) := p_service_type_tab(i);
623 l_sched_xref(S) := i; -- xref to input index
624 END IF;
625
626 ELSIF (p_mode_tab(i) = 'TRUCK' ) THEN
627 j := j + 1;
628 IF (p_lane_sched_tab(i) = 'L') THEN
629 l_lane_rows(j) := p_lane_sched_id_tab(i);
630 l_schedule_rows(j) := NULL;
631 ELSIF (p_lane_sched_tab(i) = 'S') THEN
632 l_lane_rows(j) := NULL;
633 l_schedule_rows(j) := p_lane_sched_id_tab(i);
634 END IF;
635
636
637 l_vehicle_rows(j):=p_vehicle_type_tab(i);
638
639
640 IF (l_vehicle_rows(j) = -1)
641 THEN
642 l_vehicle_rows(j):=NULL;
643
644 END IF;
645
646
650 END LOOP;
647 l_tl_xref(j) := i; -- xref to input index
648 END IF;
649
651 END IF;
652
653
654
655
656 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'>>l_lane_id_tab.COUNT'||l_lane_id_tab.COUNT);
657 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'>>l_sched_id_tab.COUNT'||l_sched_id_tab.COUNT);
658 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'>>l_lane_rows.COUNT'||l_lane_rows.COUNT);
659 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'>>l_schedule_rows.COUNT'||l_schedule_rows.COUNT);
660 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'>>l_tl_xref.COUNT'||l_tl_xref.COUNT);
661 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'>>3');
662
663
664 --FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'x_sum_rate_tab.COUNT='||x_sum_rate_tab.COUNT);
665 --FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'x_sum_rate_curr_tab.COUNT='||x_sum_rate_curr_tab.COUNT);
666 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'>>4');
667 -- Call LTL/PARCEL API
668
669 L :=0; S:=0; j:=0;
670
671 IF (l_lane_id_tab.COUNT >0 OR l_sched_id_tab.COUNT >0) THEN
672
673 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'>>5');
674 FTE_FREIGHT_PRICING.shipment_price_compare_pvt (
675 p_delivery_id => NULL,
676 p_trip_id => p_trip_id,
677 p_lane_id_tab => l_lane_id_tab,
678 p_sched_id_tab => l_sched_id_tab,
679 p_service_lane_tab => l_service_lane_tab,
680 p_service_sched_tab => l_service_sched_tab,
681 p_dep_date => p_dep_date,
682 p_arr_date => p_arr_date,
683 x_sum_lane_price_tab => l_lane_price_tab,
684 x_sum_lane_price_curr_tab => l_lane_curr_tab,
685 x_sum_sched_price_tab => l_sched_price_tab,
686 x_sum_sched_price_curr_tab =>l_sched_curr_tab,
687 x_request_id => l_request_id,
688 x_return_status => l_return_status );
689
690 -- Error checking here
691 -- For now only unexpected errors returned cause this procedure to fail
692 -- However, we can go more granular, and fail even on certain
693 -- errors caused in the child procedures
694
695 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'>>l_return_status='||l_return_status);
696 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
697 THEN
698 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
699 THEN
700 raise FTE_FREIGHT_PRICING_UTIL.g_unexp_err;
701 ELSE
702 l_warn_flag := 'Y';
703 END IF;
704 END IF;
705 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'>>l_lane_price_tab.COUNT='||l_lane_price_tab.COUNT);
706 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'>>l_sched_price_tab.COUNT='||l_sched_price_tab.COUNT);
707
708
709 END IF;
710
711 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'>>>');
712 -- Call TL API
713
714
715
716 IF (l_lane_rows.COUNT > 0) THEN
717
718
719
720 FTE_TL_RATING.Get_Vehicles_For_LaneSchedules(
721 p_trip_id =>p_trip_id,
722 p_lane_rows =>l_lane_rows,
723 p_schedule_rows =>l_schedule_rows,
724 p_vehicle_rows =>l_vehicle_rows,
725 x_vehicle_rows =>l_exploded_vehicle_rows,
726 x_lane_rows =>l_exploded_lane_rows,
727 x_schedule_rows =>l_exploded_schedule_rows,
728 x_ref_rows =>l_exploded_ref_rows,
729 x_return_status =>l_return_status);
730
731 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
732 THEN
733 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
734 THEN
735 raise FTE_FREIGHT_PRICING_UTIL.g_unexp_err;
736 ELSE
737 l_warn_flag := 'Y';
738 END IF;
739 END IF;
740
741
742
743
744
745 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'>>6');
746 FTE_TL_RATING.TL_TRIP_PRICE_COMPARE(
747 p_wsh_trip_id => p_trip_id,
748 p_lane_rows => l_exploded_lane_rows,
749 p_schedule_rows => l_exploded_schedule_rows,
750 p_vehicle_rows => l_exploded_vehicle_rows,
751 x_request_id => l_request_id,
752 x_lane_sched_sum_rows => l_lane_sched_sum_rows,
753 x_lane_sched_curr_rows => l_lane_sched_curr_rows,
754 x_return_status => l_return_status );
755
756 -- Error checking here
757 -- For now only unexpected errors returned cause this procedure to fail
758 -- However, we can go more granular, and fail even on certain
759 -- errors caused in the child procedures
760
761 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'>>l_return_status='||l_return_status);
762 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
763 THEN
767 ELSE
764 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
765 THEN
766 raise FTE_FREIGHT_PRICING_UTIL.g_unexp_err;
768 l_warn_flag := 'Y';
769 END IF;
770 END IF;
771
772 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'>>l_lane_sched_sum_rows.COUNT='||l_lane_sched_sum_rows.COUNT);
773
774 END IF;
775
776 --populate output from both TL and non-TL
777
778 l_output_count:=p_lane_sched_id_tab.COUNT;
779 IF (l_lane_rows.COUNT > 0)
780 THEN
781 l_output_count:=l_output_count+ l_exploded_ref_rows.COUNT-l_lane_rows.COUNT;
782 END IF;
783
784 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Op count:'||l_output_count);
785
786 -- initialize output nested tables
787 IF (p_lane_sched_id_tab.COUNT > 0 )
788 THEN
789
790 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Init op tables');
791
792 x_sum_rate_tab := FTE_ID_TAB_TYPE(0);
793 x_sum_rate_curr_tab := FTE_CODE_TAB_TYPE('NULL');
794 -- init all elements the tables with 0 and 'NULL' resp.
795
796 x_lane_sched_id_tab:=FTE_ID_TAB_TYPE(0);
797 x_lane_sched_tab:=FTE_CODE_TAB_TYPE('NULL');
798 x_vehicle_type_tab:=FTE_ID_TAB_TYPE(0);
799 x_mode_tab:=FTE_CODE_TAB_TYPE('NULL');
800 x_service_type_tab:=FTE_CODE_TAB_TYPE('NULL');
801
802
803 x_sum_rate_tab.EXTEND(l_output_count-1,1);
804 x_sum_rate_curr_tab.EXTEND(l_output_count-1,1);
805
806 x_lane_sched_id_tab.EXTEND(l_output_count-1,1);
807 x_lane_sched_tab.EXTEND(l_output_count-1,1);
808 x_vehicle_type_tab.EXTEND(l_output_count-1,1);
809 x_mode_tab.EXTEND(l_output_count-1,1);
810 x_service_type_tab.EXTEND(l_output_count-1,1);
811
812 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Init op tables End');
813
814 END IF;
815
816 L :=l_lane_price_tab.FIRST;
817 S:=l_sched_price_tab.FIRST;
818 j:=l_lane_rows.FIRST;
819 i:=p_lane_sched_id_tab.FIRST;
820 k:=x_sum_rate_tab.FIRST;
821 l_ref:=l_exploded_ref_rows.FIRST;
822
823 WHILE(k<=l_output_count)
824 LOOP
825 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Op index:'||k);
826
827 IF (p_mode_tab(i) <> 'TRUCK' )
828 THEN
829 IF (p_lane_sched_tab(i) = 'L')
830 THEN
831
832 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Non TL Lane:');
833
834 IF ((l_lane_price_tab.EXISTS(L)) AND (l_lane_curr_tab.EXISTS(L)))
835 THEN
836 x_sum_rate_tab(k) := l_lane_price_tab(L);
837 x_sum_rate_curr_tab(k) := l_lane_curr_tab(L);
838 END IF;
839
840 x_lane_sched_id_tab(k):=p_lane_sched_id_tab(i);
841 x_lane_sched_tab(k):=p_lane_sched_tab(i);
842 x_vehicle_type_tab(k):=p_vehicle_type_tab(i);
843 x_mode_tab(k):=p_mode_tab(i);
844 x_service_type_tab(k):=p_service_type_tab(i);
845
846
847 L := L + 1;
848 k:=k+1;
849
850 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Non TL Lane ENd:');
851
852 ELSIF (p_lane_sched_tab(i) = 'S')
853 THEN
854
855 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Non TL Schedule:');
856
857 IF((l_sched_price_tab.EXISTS(S)) AND (l_sched_curr_tab.EXISTS(S)))
858 THEN
859
860 x_sum_rate_tab(k) := l_sched_price_tab(S);
861 x_sum_rate_curr_tab(k) := l_sched_curr_tab(S);
862 END IF;
863 x_lane_sched_id_tab(k):=p_lane_sched_id_tab(i);
864 x_lane_sched_tab(k):=p_lane_sched_tab(i);
865 x_vehicle_type_tab(k):=p_vehicle_type_tab(i);
866 x_mode_tab(k):=p_mode_tab(i);
867 x_service_type_tab(k):=p_service_type_tab(i);
868
869
870
871 S := S + 1;
872 k:=k+1;
873
874 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Non TL Schedule End:');
875
876 END IF;
877
878 ELSIF (p_mode_tab(i) = 'TRUCK' )
879 THEN
880
881 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'TL :');
882 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'l_ref'||l_ref);
883 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'j'||j);
884 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'l_exploded_ref_rows count'||l_exploded_ref_rows.COUNT);
885
886 WHILE(l_exploded_ref_rows.EXISTS(l_ref) AND l_exploded_ref_rows(l_ref)=j)
887 LOOP
888 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'l_exploded_ref_rows(l_ref)'||l_exploded_ref_rows(l_ref));
889
890 --FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'l_lane_sched_sum_rows(l_ref)'||l_lane_sched_sum_rows(l_ref));
891
892
893 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'TL 1:');
897 x_sum_rate_tab(k) := nvl(l_lane_sched_sum_rows(l_ref),-1);
894 x_sum_rate_tab(k):=-1;
895 IF (l_lane_sched_sum_rows.EXISTS(l_ref))
896 THEN
898 END IF;
899
900 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'TL 2:');
901
902 x_sum_rate_curr_tab(k):='NULL';
903 IF (l_lane_sched_curr_rows.EXISTS(l_ref))
904 THEN
905 x_sum_rate_curr_tab(k) := nvl(l_lane_sched_curr_rows(l_ref),'NULL');
906 END IF;
907
908 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'TL 3:');
909
910 x_lane_sched_id_tab(k):=p_lane_sched_id_tab(i);
911 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'TL 4:');
912 x_lane_sched_tab(k):=p_lane_sched_tab(i);
913 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'TL 5:');
914 IF (l_exploded_vehicle_rows.EXISTS(l_ref))
915 THEN
916 x_vehicle_type_tab(k):=l_exploded_vehicle_rows(l_ref);
917 END IF;
918 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'TL 6:');
919 x_mode_tab(k):=p_mode_tab(i);
920 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'TL 7:');
921 x_service_type_tab(k):=p_service_type_tab(i);
922 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'TL 8:');
923
924 --FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
925 --' '||l_ref||'-'||i||'-'||l_lane_sched_sum_rows(l_ref)||'-'||l_lane_sched_curr_rows(l_ref));
926
927
928 k:=k+1;
929
930 l_ref:=l_ref+1;
931 END LOOP;
932
933 j := j + 1;
934
935 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'TL End:');
936
937 END IF;
938
939
940 i:=i+1;
941 END LOOP;
942
943
944 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'>>7');
945 IF (l_warn_flag = 'Y') THEN
946 FTE_FREIGHT_PRICING_UTIL.set_trip_prc_comp_exit_warn;
947 --Added to ensure return status is warning if l_warn_flag=Y
948 x_return_status :=WSH_UTIL_CORE.G_RET_STS_WARNING;
949
950 END IF;
951 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'>>8 l_warn_flag='||l_warn_flag);
952 x_request_id := l_request_id;
953
954 IF (x_sum_rate_tab.COUNT > 0) THEN
955 FOR i IN x_sum_rate_tab.FIRST .. x_sum_rate_tab.LAST
956 LOOP
957 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
958 ' '||x_lane_sched_id_tab(i)||' '||x_lane_sched_tab(i)||' '||x_mode_tab(i)||' '||x_service_type_tab(i)||' '
959 ||x_vehicle_type_tab(i)||' '||x_sum_rate_tab(i)||' '||x_sum_rate_curr_tab(i) );
960 END LOOP;
961 END IF;
962
963 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'>>9.1');
964 FND_MSG_PUB.Count_And_Get
965 (
966 p_count => x_msg_count,
967 p_data => x_msg_data,
968 p_encoded => FND_API.G_FALSE
969 );
970
971 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'>>9.2');
972 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Compare_Trip_Rates');
973 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'>>9.3');
974
975 IF(p_init_prc_log='Y')
976 THEN
977 FTE_FREIGHT_PRICING_UTIL.close_logs;
978 END IF;
979
980
981
982 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'>>9.4');
983
984 COMMIT; -- Commit Autonomous transaction
985
986 /*
987 IF FND_API.to_Boolean( p_commit )
988 THEN
989 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'>>9.5');
990 COMMIT; -- Commit Autonomous transaction
991 ELSE
992 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'>>9.6');
993 ROLLBACK; --TO COMPARE_TRIP_RATES;
994
995 END IF;
996 */
997
998 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'>>9.7');
999 EXCEPTION
1000
1001 WHEN FTE_FREIGHT_PRICING_UTIL.g_unexp_err THEN
1002 ROLLBACK;-- TO COMPARE_TRIP_RATES;
1003 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1004 FTE_FREIGHT_PRICING_UTIL.set_exit_exception('Compare_Trip_Rates','g_others');
1005 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Compare_Trip_Rates');
1006 IF(p_init_prc_log='Y')
1007 THEN
1008 FTE_FREIGHT_PRICING_UTIL.close_logs;
1009 END IF;
1010
1011
1012 WHEN others THEN
1013 ROLLBACK;-- TO COMPARE_TRIP_RATES;
1014 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1015 FTE_FREIGHT_PRICING_UTIL.set_exit_exception('Compare_Trip_Rates','g_others');
1016 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
1017 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Compare_Trip_Rates');
1018 IF(p_init_prc_log='Y')
1019 THEN
1020 FTE_FREIGHT_PRICING_UTIL.close_logs;
1021 END IF;
1022
1023
1024 END Compare_Trip_Rates;
1025
1026
1027
1028 PROCEDURE Compare_Trip_Rates (
1032 p_lane_sched_id_tab IN FTE_ID_TAB_TYPE,
1029 p_api_version IN NUMBER DEFAULT 1.0,
1030 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1031 p_trip_id IN NUMBER DEFAULT NULL,
1033 p_lane_sched_tab IN FTE_CODE_TAB_TYPE,
1034 p_mode_tab IN FTE_CODE_TAB_TYPE,
1035 p_service_type_tab IN FTE_CODE_TAB_TYPE,
1036 p_vehicle_id_tab IN FTE_ID_TAB_TYPE,
1037 p_dep_date IN DATE DEFAULT sysdate,
1038 p_arr_date IN DATE DEFAULT sysdate,
1039 p_event IN VARCHAR2 DEFAULT 'FTE_TRIP_COMP',
1040 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1041 x_request_id OUT NOCOPY NUMBER,
1042 x_sum_rate_tab OUT NOCOPY FTE_ID_TAB_TYPE,
1043 x_sum_rate_curr_tab OUT NOCOPY FTE_CODE_TAB_TYPE,
1044 x_return_status OUT NOCOPY VARCHAR2,
1045 x_msg_count OUT NOCOPY NUMBER,
1046 x_msg_data OUT NOCOPY VARCHAR2)
1047 IS
1048
1049 BEGIN
1050
1051 NULL;
1052
1053 END Compare_Trip_Rates;
1054
1055 --
1056 -- Procedure : Rate_Trip_Int
1057 -- This is an internal procedure. It calls appropriate apis to rate the
1058 -- trip based upon the parameters p_rate_mode and p_event
1059 -- Assumes that most validations regarding the event have been done
1060 -- by the calling procedure
1061 --
1062
1063 PROCEDURE Rate_Trip_Int (
1064 p_trip_info_rec trip_info_rec,
1065 p_rate_mode IN VARCHAR2 DEFAULT 'REGULAR',
1066 p_event IN VARCHAR2,
1067 x_return_status OUT NOCOPY VARCHAR2,
1068 x_msg_count OUT NOCOPY NUMBER,
1069 x_msg_data OUT NOCOPY VARCHAR2)
1070 IS
1071
1072 l_api_version CONSTANT NUMBER := 1.0;
1073 l_api_name CONSTANT VARCHAR2(30) := 'RATE_TRIP_INT';
1074 l_log_level NUMBER := FTE_FREIGHT_PRICING_UTIL.G_LOG;
1075 l_return_status VARCHAR2(1);
1076 l_return_status_1 VARCHAR2(1);
1077
1078 l_dummy_fc_tab FTE_FREIGHT_PRICING.Freight_Cost_Temp_Tab_Type;
1079
1080 l_msg_count NUMBER;
1081 l_msg_data VARCHAR2(32767);
1082
1083 l_number_of_errors NUMBER;
1084 l_number_of_warnings NUMBER;
1085 l_commit VARCHAR2(100) := FND_API.G_FALSE;
1086 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
1087 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || 'RATE_TRIP_INT';
1088 BEGIN
1089
1090 SAVEPOINT RATE_TRIP_INT;
1091
1092 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1093 --
1094 IF l_debug_on THEN
1095 wsh_debug_sv.push(l_module_name);
1096 END IF;
1097 --
1098 IF l_debug_on THEN
1099 WSH_DEBUG_SV.logmsg(
1100 l_module_name,
1101 'p_trip_id'|| p_trip_info_rec.trip_id,
1102 WSH_DEBUG_SV.C_PROC_LEVEL);
1103 END IF;
1104 IF l_debug_on THEN
1105 WSH_DEBUG_SV.logmsg(
1106 l_module_name,
1107 'p_event'|| p_event,
1108 WSH_DEBUG_SV.C_PROC_LEVEL);
1109 END IF;
1110 IF l_debug_on THEN
1111 WSH_DEBUG_SV.logmsg(
1112 l_module_name,
1113 'p_rate_mode'|| p_rate_mode,
1114 WSH_DEBUG_SV.C_PROC_LEVEL);
1115 END IF;
1116
1117 FTE_FREIGHT_PRICING_UTIL.reset_dbg_vars;
1118 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,l_api_name,'start');
1119 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'trip_id='||p_trip_info_rec.trip_id);
1120 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'p_event='||p_event);
1121 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'p_rate_mode='||p_rate_mode);
1122 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'mode_of_trans='||p_trip_info_rec.mode_of_transport);
1123
1124 IF ( p_rate_mode = 'TP-REL') THEN
1125 -- irrespective of event, the same apis can be called to rate tp released trips
1126 -- when they are rated the first time
1127 -- that is why we did not check for the p_event in this case
1128
1129 -- redirect based upon mode of transport
1130 -- lane_id is assumed to be present
1131
1132 IF (p_trip_info_rec.mode_of_transport='TRUCK') THEN
1133 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'calling tl_rate_trip ');
1134 -- call TL rating
1135
1136 -- TO DO: No reprice flag checking
1137
1138 FTE_TL_RATING.tl_rate_trip (
1139 p_trip_id => p_trip_info_rec.trip_id,
1140 p_output_type => 'M',
1141 x_output_cost_tab => l_dummy_fc_tab,
1142 x_return_status => l_return_status);
1143
1144 -- error checking
1145 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
1146 THEN
1147 RAISE FND_API.G_EXC_ERROR;
1148 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
1149 THEN
1153 x_return_status := l_return_status;
1150 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1151 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING )
1152 THEN
1154 END IF;
1155
1156 ELSE -- non TL
1157 -- call shipment_price_consolidate
1158 -- No reprice flag checking
1159
1160 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'calling shipment_price_consolidate ');
1161 FTE_FREIGHT_PRICING.shipment_price_consolidate (
1162 p_segment_id => p_trip_info_rec.trip_id,
1163 p_check_reprice_flag => 'N',
1164 x_return_status => l_return_status);
1165
1166 -- error checking
1167 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
1168 THEN
1169 RAISE FND_API.G_EXC_ERROR;
1170 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
1171 THEN
1172 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1173 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING )
1174 THEN
1175 x_return_status := l_return_status;
1176 END IF;
1177
1178 END IF;
1179
1180
1181 END IF;
1182
1183
1184 IF (p_event = 'SHIP-CONFIRM' AND
1185 p_rate_mode = 'REGULAR') THEN
1186
1187 -- call Rate_Delivery
1188 -- it does reprice flag checking by default
1189 -- it can handle both TL and non-TL
1190 -- can also do LCSS where eligible
1191
1192 IF (p_trip_info_rec.lane_id IS NOT NULL) THEN
1193
1194 IF (p_trip_info_rec.mode_of_transport='TRUCK') THEN
1195 -- call TL rate trip with reprice checking
1196 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'calling tl_rate_trip ');
1197
1198 FTE_TL_RATING.tl_rate_trip (
1199 p_trip_id => p_trip_info_rec.trip_id,
1200 p_output_type => 'M',
1201 p_check_reprice_flag => 'Y',
1202 x_output_cost_tab => l_dummy_fc_tab,
1203 x_return_status => l_return_status);
1204
1205 -- error checking
1206 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
1207 THEN
1208 RAISE FND_API.G_EXC_ERROR;
1209 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
1210 THEN
1211 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1212 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING )
1213 THEN
1214 x_return_status := l_return_status;
1215 END IF;
1216
1217
1218 ELSE
1219 -- call shipment_price_consolidate with reprice checking
1220
1221 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'calling shipment_price_consolidate ');
1222 FTE_FREIGHT_PRICING.shipment_price_consolidate (
1223 p_segment_id => p_trip_info_rec.trip_id,
1224 p_check_reprice_flag => 'Y',
1225 x_return_status => l_return_status);
1226
1227 -- error checking
1228 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
1229 THEN
1230 RAISE FND_API.G_EXC_ERROR;
1231 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
1232 THEN
1233 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1234 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING )
1235 THEN
1236 x_return_status := l_return_status;
1237 END IF;
1238
1239
1240 END IF;
1241
1242 ELSE
1243 -- lane_id is null
1244 -- route this request through LCSS
1245
1246 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'calling Rate_Delivery ');
1247 FTE_FREIGHT_RATING_DLVY_GRP.Rate_Delivery (
1248 p_api_version => 1,
1249 p_init_msg_list => FND_API.G_FALSE,
1250 p_delivery_id => null,
1251 p_trip_id => p_trip_info_rec.trip_id,
1252 p_action => 'RATE',
1253 p_commit => FND_API.G_FALSE,
1254 p_init_prc_log => 'N',
1255 x_return_status => l_return_status,
1256 x_msg_count => x_msg_count,
1257 x_msg_data => x_msg_data );
1258
1259 -- error checking
1260 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
1261 THEN
1262 RAISE FND_API.G_EXC_ERROR;
1263 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
1264 THEN
1265 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1266 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING )
1267 THEN
1268 x_return_status := l_return_status;
1269 END IF;
1270 END IF;
1271
1272 END IF;
1273
1274 IF (p_event = 'RE-RATING' AND p_rate_mode = 'RE-RATE') THEN
1275
1276 IF (p_trip_info_rec.mode_of_transport='TRUCK') THEN
1280 -- No reprice flag checking
1277 -- call TL rating
1278
1279 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'calling tl_rate_trip ');
1281
1282 FTE_TL_RATING.tl_rate_trip (
1283 p_trip_id => p_trip_info_rec.trip_id,
1284 p_output_type => 'M',
1285 -- p_check_reprice_flag =>'N',
1286 x_output_cost_tab => l_dummy_fc_tab,
1287 x_return_status => l_return_status);
1288
1289 -- error checking
1290 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
1291 THEN
1292 RAISE FND_API.G_EXC_ERROR;
1293 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
1294 THEN
1295 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1296 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING )
1297 THEN
1298 x_return_status := l_return_status;
1299 END IF;
1300
1301 ELSE -- non TL
1302 -- call shipment_price_consolidate
1303 -- No reprice flag checking
1304
1305 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'calling shipment_price_consolidate ');
1306 FTE_FREIGHT_PRICING.shipment_price_consolidate (
1307 p_segment_id => p_trip_info_rec.trip_id,
1308 p_check_reprice_flag => 'N',
1309 x_return_status => l_return_status);
1310
1311 -- error checking
1312 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
1313 THEN
1314 RAISE FND_API.G_EXC_ERROR;
1315 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
1316 THEN
1317 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1318 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING )
1319 THEN
1320 x_return_status := l_return_status;
1321 END IF;
1322
1323 END IF;
1324
1325 END IF;
1326
1327 IF l_debug_on THEN
1328 WSH_DEBUG_SV.pop(l_module_name);
1329 END IF;
1330 --
1331 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_api_name);
1332 --
1333 EXCEPTION
1334 WHEN FND_API.G_EXC_ERROR THEN
1335 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1336 IF l_debug_on THEN
1337 WSH_DEBUG_SV.pop(l_module_name);
1338 END IF;
1339 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_api_name);
1340 --
1341 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1342 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1343 IF l_debug_on THEN
1344 WSH_DEBUG_SV.pop(l_module_name);
1345 END IF;
1346 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_api_name);
1347 --
1348 WHEN OTHERS THEN
1349 WSH_UTIL_CORE.DEFAULT_HANDLER(G_PKG_NAME||'.Rate_Trip_Int');
1350 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1351 IF l_debug_on THEN
1352 WSH_DEBUG_SV.pop(l_module_name);
1353 END IF;
1354 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_api_name);
1355 --
1356 END Rate_Trip_Int;
1357
1358
1359
1360 -- +======================================================================+
1361 -- Procedure :
1362 -- Rate_Trip
1363 --
1364 -- Description:
1365 -- Rate Trip from various event points
1366 -- Inputs:
1367 -- p_action_params => parameters identifying the
1368 -- action to be performed
1369 -- -> caller -> 'FTE','WSH'
1370 -- -> event -> 'TP-RELEASE','SHIP-CONFIRM','RE-RATING'
1371 -- -> action -> 'RATE'
1372 -- -> trip_id_list -> valid list of wsh trip_id
1373 -- p_commit => FND_API.G_FALSE / G_TRUE
1374 -- Output:
1375 -- x_return_status OUT NOCOPY VARCHAR2 => Return status
1376 --
1377 -- Global dependencies:
1378 --
1379 --
1380 -- DB:
1381 --
1382 -- +======================================================================+
1383
1384
1385 PROCEDURE Rate_Trip (
1386 p_api_version IN NUMBER DEFAULT 1.0,
1387 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1388 p_action_params IN FTE_TRIP_RATING_GRP.action_param_rec,
1389 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1390 p_init_prc_log IN VARCHAR2 DEFAULT 'Y',
1391 x_return_status OUT NOCOPY VARCHAR2,
1392 x_msg_count OUT NOCOPY NUMBER,
1393 x_msg_data OUT NOCOPY VARCHAR2)
1394 IS
1395
1396 CURSOR tp_rel_param IS
1397 SELECT auto_rate_tp_rel_trips
1398 FROM wsh_global_parameters;
1399
1400
1401 l_api_version CONSTANT NUMBER := 1.0;
1402 l_api_name CONSTANT VARCHAR2(30) := 'RATE_TRIP';
1403 l_log_level NUMBER := FTE_FREIGHT_PRICING_UTIL.G_LOG;
1404 l_return_status VARCHAR2(1);
1405 l_return_status_1 VARCHAR2(1);
1406
1407 l_trip_info_rec trip_info_rec;
1408 l_tp_rel_rate_event VARCHAR2(30);
1412 j NUMBER;
1409 -- internal flag
1410 l_rate_mode VARCHAR2(30) := null;
1411 i NUMBER;
1413 l_rated_move_ids WSH_UTIL_CORE.id_tab_type;
1414
1415 l_msg_count NUMBER;
1416 l_msg_data VARCHAR2(32767);
1417
1418 l_number_of_errors NUMBER;
1419 l_number_of_warnings NUMBER;
1420 l_commit VARCHAR2(100) := FND_API.G_FALSE;
1421 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
1422 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || 'RATE_TRIP';
1423
1424 l_warn_count NUMBER :=0;
1425 BEGIN
1426
1427 SAVEPOINT RATE_TRIP;
1428 --
1429 -- Standard call to check for call compatibility.
1430 IF NOT FND_API.Compatible_API_Call
1431 (
1432 l_api_version,
1433 p_api_version,
1434 l_api_name,
1435 G_PKG_NAME
1436 )
1437 THEN
1438 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1439 END IF;
1440
1441 -- Initialize message list if p_init_msg_list is set to TRUE.
1442 IF FND_API.to_Boolean( p_init_msg_list )
1443 THEN
1444 FND_MSG_PUB.initialize;
1445 END IF;
1446
1447 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1448 --
1449 IF l_debug_on THEN
1450 wsh_debug_sv.push(l_module_name);
1451 END IF;
1452 --
1453 IF l_debug_on THEN
1454 WSH_DEBUG_SV.logmsg(
1455 l_module_name,
1456 'p_action_params.trip_id_list.COUNT '|| p_action_params.trip_id_list.COUNT,
1457 WSH_DEBUG_SV.C_PROC_LEVEL);
1458 END IF;
1459 IF l_debug_on THEN
1460 WSH_DEBUG_SV.logmsg(
1461 l_module_name,
1462 'p_action_params.caller '|| p_action_params.caller,
1463 WSH_DEBUG_SV.C_PROC_LEVEL);
1464 END IF;
1465 IF l_debug_on THEN
1466 WSH_DEBUG_SV.logmsg(
1467 l_module_name,
1468 'p_action_params.event '|| p_action_params.event,
1469 WSH_DEBUG_SV.C_PROC_LEVEL);
1470 END IF;
1471 IF l_debug_on THEN
1472 WSH_DEBUG_SV.logmsg(
1473 l_module_name,
1474 'p_action_params.action '|| p_action_params.action,
1475 WSH_DEBUG_SV.C_PROC_LEVEL);
1476 END IF;
1477 IF l_debug_on THEN
1478 WSH_DEBUG_SV.logmsg(
1479 l_module_name,
1480 'p_commit '|| p_commit,
1481 WSH_DEBUG_SV.C_PROC_LEVEL);
1482 END IF;
1483
1484 IF p_init_prc_log = 'Y' THEN
1485 FTE_FREIGHT_PRICING_UTIL.initialize_logging( x_return_status => l_return_status );
1486
1487 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) and
1488 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
1489 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1490 FTE_FREIGHT_RATING_DLVY_GRP.api_post_call
1491 (
1492 p_api_name => l_module_name,
1493 p_api_return_status => l_return_status,
1494 p_message_name => 'FTE_INIT_LOG_FAIL',
1495 x_number_of_errors => l_number_of_errors,
1496 x_number_of_warnings => l_number_of_warnings,
1497 x_return_status => l_return_status_1
1498 );
1499 --
1500 IF (l_return_status_1 = WSH_UTIL_CORE.G_RET_STS_ERROR)
1501 THEN
1502 RAISE FND_API.G_EXC_ERROR;
1503 ELSIF (l_return_status_1 = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
1504 THEN
1505 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1506 ELSIF (l_return_status_1 = WSH_UTIL_CORE.G_RET_STS_WARNING )
1507 THEN
1508 x_return_status := l_return_status_1;
1509 END IF;
1510 ELSE
1511 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'Initialize Logging successful ');
1512 END IF;
1513 END IF;
1514
1515 FTE_FREIGHT_PRICING_UTIL.reset_dbg_vars;
1516 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,l_api_name,'start');
1517
1518 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'p_action_params->');
1519 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,' trip_id_list.COUNT='||p_action_params.trip_id_list.COUNT);
1520 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,' event='||p_action_params.event);
1521 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,' caller='||p_action_params.caller);
1522 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,' action='||p_action_params.action);
1523 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'p_commit='||p_commit);
1524
1525 IF (p_action_params.trip_id_list.COUNT = 0
1526 OR p_action_params.event IS NULL
1527 OR p_action_params.caller IS NULL
1528 OR p_action_params.action IS NULL ) THEN
1529
1530 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1531 FTE_FREIGHT_RATING_DLVY_GRP.api_post_call
1532 (
1533 p_api_name => l_module_name,
1534 p_api_return_status => l_return_status,
1535 p_message_name => 'FTE_TRIP_RATING_INV_PARAMS',
1536 x_number_of_errors => l_number_of_errors,
1537 x_number_of_warnings => l_number_of_warnings,
1538 x_return_status => l_return_status_1
1539 );
1540 --
1541
1542 IF (l_return_status_1 = WSH_UTIL_CORE.G_RET_STS_ERROR)
1543 THEN
1547 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1544 RAISE FND_API.G_EXC_ERROR;
1545 ELSIF (l_return_status_1 = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
1546 THEN
1548 ELSIF (l_return_status_1 = WSH_UTIL_CORE.G_RET_STS_WARNING )
1549 THEN
1550 x_return_status := l_return_status_1;
1551 END IF;
1552 END IF;
1553
1554 -- check global parameters to see if rating should be done for tp released
1555 -- trips at this event
1556
1557 OPEN tp_rel_param;
1558 FETCH tp_rel_param INTO l_tp_rel_rate_event;
1559 CLOSE tp_rel_param;
1560
1561 IF (l_tp_rel_rate_event = 'S') THEN
1562 l_tp_rel_rate_event := 'SHIP-CONFIRM';
1563 ELSIF (l_tp_rel_rate_event = 'R') THEN
1564 l_tp_rel_rate_event := 'TP-RELEASE';
1565 ELSE
1566 l_tp_rel_rate_event := 'NONE';
1567 END IF;
1568
1569 IF l_debug_on THEN
1570 WSH_DEBUG_SV.logmsg(
1571 l_module_name, 'l_tp_rel_rate_event = '||l_tp_rel_rate_event,
1572 WSH_DEBUG_SV.C_PROC_LEVEL);
1573 END IF;
1574 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'l_tp_rel_rate_event = '||l_tp_rel_rate_event);
1575
1576 -- if calling event is TP-RELEASE, rating needs to be done only if
1577 -- global parameter is set to TP-RELEASE
1578
1579 IF (p_action_params.event = 'TP-RELEASE'
1580 AND l_tp_rel_rate_event <> 'TP-RELEASE') THEN
1581 -- raise warning : rating is not required at this event
1582 -- exit
1583 l_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1584 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'Warning =FTE_TRP_RATING_RATE_NOT_REQ');
1585 FTE_FREIGHT_RATING_DLVY_GRP.api_post_call
1586 (
1587 p_api_name => l_module_name,
1588 p_api_return_status => l_return_status,
1589 p_message_name => 'FTE_TRP_RATING_RATE_NOT_REQ',
1590 p_trip_id => null,
1591 p_delivery_id => null,
1592 p_delivery_leg_id => null,
1593 x_number_of_errors => l_number_of_errors,
1594 x_number_of_warnings => l_number_of_warnings,
1595 x_return_status => l_return_status_1
1596 );
1597 --
1598 IF (l_return_status_1 = WSH_UTIL_CORE.G_RET_STS_ERROR)
1599 THEN
1600 RAISE FND_API.G_EXC_ERROR;
1601 ELSIF (l_return_status_1 = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
1602 THEN
1603 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1604 ELSIF (l_return_status_1 = WSH_UTIL_CORE.G_RET_STS_WARNING )
1605 THEN
1606 x_return_status := l_return_status_1;
1607 RAISE g_finished_success;
1608 END IF;
1609 END IF;
1610
1611 -- Now loop over all trips in the input
1612
1613 i := p_action_params.trip_id_list.FIRST;
1614 LOOP
1615 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'>>>trip_id='||p_action_params.trip_id_list(i));
1616
1617 -- Get trip information from db
1618 OPEN c_trip_info(p_action_params.trip_id_list(i));
1619 FETCH c_trip_info INTO l_trip_info_rec;
1620 IF (c_trip_info%NOTFOUND) THEN
1621 -- raise exception
1622 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1623 FTE_FREIGHT_RATING_DLVY_GRP.api_post_call
1624 (
1625 p_api_name => l_module_name,
1626 p_api_return_status => l_return_status,
1627 p_message_name => 'FTE_TRIP_RATING_INV_TRIP',
1628 p_trip_id => p_action_params.trip_id_list(i),
1629 p_delivery_id => null,
1630 p_delivery_leg_id => null,
1631 x_number_of_errors => l_number_of_errors,
1632 x_number_of_warnings => l_number_of_warnings,
1633 x_return_status => l_return_status_1
1634 );
1635 --
1636 IF (l_return_status_1 = WSH_UTIL_CORE.G_RET_STS_ERROR)
1637 THEN
1638 RAISE FND_API.G_EXC_ERROR;
1639 ELSIF (l_return_status_1 = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
1640 THEN
1641 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1642 ELSIF (l_return_status_1 = WSH_UTIL_CORE.G_RET_STS_WARNING )
1643 THEN
1644 x_return_status := l_return_status_1;
1645 END IF;
1646
1647 END IF;
1648 CLOSE c_trip_info;
1649
1650 -- Check if this trip is part of a continuous move
1651 -- If it was already rated in an earlier iteration, don't rate
1652
1653 IF (l_trip_info_rec.move_id IS NOT NULL) THEN
1654 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'move_id='||l_trip_info_rec.move_id);
1655 IF l_debug_on THEN
1656 WSH_DEBUG_SV.logmsg( l_module_name, 'move_id = '||l_trip_info_rec.move_id,
1657 WSH_DEBUG_SV.C_PROC_LEVEL);
1658 END IF;
1659 IF l_rated_move_ids.EXISTS(l_trip_info_rec.move_id) THEN
1660 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'move already rated - next pass');
1661 IF l_debug_on THEN
1662 WSH_DEBUG_SV.logmsg( l_module_name, 'move already rated - next pass',
1663 WSH_DEBUG_SV.C_PROC_LEVEL);
1664 END IF;
1665 GOTO nextpass;
1666 END IF;
1670 -- Shipmethod is always required on trip
1667 END IF;
1668
1669
1671
1672 IF ( l_trip_info_rec.carrier_id IS NULL
1673 --OR l_trip_info_rec.ship_method_code IS NULL
1674 OR l_trip_info_rec.mode_of_transport IS NULL
1675 OR l_trip_info_rec.service_level IS NULL ) THEN
1676
1677 -- raise error: carrier info missing
1678 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1679 FTE_FREIGHT_RATING_DLVY_GRP.api_post_call
1680 (
1681 p_api_name => l_module_name,
1682 p_api_return_status => l_return_status,
1683 p_message_name => 'FTE_TRIP_RATING_MISS_CARR',
1684 p_trip_id => l_trip_info_rec.trip_id,
1685 p_delivery_id => null,
1686 p_delivery_leg_id => null,
1687 x_number_of_errors => l_number_of_errors,
1688 x_number_of_warnings => l_number_of_warnings,
1689 x_return_status => l_return_status_1
1690 );
1691 --
1692 IF (l_return_status_1 = WSH_UTIL_CORE.G_RET_STS_ERROR)
1693 THEN
1694 RAISE FND_API.G_EXC_ERROR;
1695 ELSIF (l_return_status_1 = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
1696 THEN
1697 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1698 ELSIF (l_return_status_1 = WSH_UTIL_CORE.G_RET_STS_WARNING )
1699 THEN
1700 x_return_status := l_return_status_1;
1701 END IF;
1702 END IF;
1703
1704 IF (p_action_params.event = 'TP-RELEASE') THEN
1705
1706 IF (l_trip_info_rec.tp_plan_name IS NOT NULL ) THEN
1707 -- OK to rate
1708 -- global parameter has already been validated earlier
1709 l_rate_mode := 'TP-REL';
1710 ELSE
1711 -- for now raise error : trip is not a tp released trip
1712 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1713 FTE_FREIGHT_RATING_DLVY_GRP.api_post_call
1714 (
1715 p_api_name => l_module_name,
1716 p_api_return_status => l_return_status,
1717 p_message_name => 'FTE_TRIP_RATING_NOT_TP_REL',
1718 p_trip_id => l_trip_info_rec.trip_id,
1719 p_delivery_id => null,
1720 p_delivery_leg_id => null,
1721 x_number_of_errors => l_number_of_errors,
1722 x_number_of_warnings => l_number_of_warnings,
1723 x_return_status => l_return_status_1
1724 );
1725 --
1726 IF (l_return_status_1 = WSH_UTIL_CORE.G_RET_STS_ERROR)
1727 THEN
1728 RAISE FND_API.G_EXC_ERROR;
1729 ELSIF (l_return_status_1 = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
1730 THEN
1731 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1732 ELSIF (l_return_status_1 = WSH_UTIL_CORE.G_RET_STS_WARNING )
1733 THEN
1734 x_return_status := l_return_status_1;
1735 END IF;
1736 END IF;
1737
1738 END IF; -- event = TP-RELEASE
1739
1740
1741 IF (p_action_params.event = 'SHIP-CONFIRM') THEN
1742
1743 -- check if this is a tp released trip
1744 -- and needs to be rated
1745
1746 IF ( l_trip_info_rec.tp_plan_name IS NOT NULL ) THEN
1747
1748 IF (l_tp_rel_rate_event = 'SHIP-CONFIRM') THEN
1749 -- OK to rate without reprice flag checking
1750 l_rate_mode := 'TP-REL';
1751 ELSIF (l_tp_rel_rate_event = 'TP-RELEASE') THEN
1752 -- Rate as a standard trip with reprice flag checking
1753 -- We still don't allow LCSS on this trip
1754 -- So lane_id cannot be null
1755 l_rate_mode := 'REGULAR';
1756 ELSE
1757 l_rate_mode := 'NONE';
1758 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level, 'rate_mode='||l_rate_mode);
1759 -- We can't rate this trip anytime
1760 -- raise warning : rating not required for this trip
1761 -- exit
1762 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,
1763 'Warning =FTE_TRP_RATING_RATE_NOT_REQ');
1764 l_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1765 FTE_FREIGHT_RATING_DLVY_GRP.api_post_call
1766 (
1767 p_api_name => l_module_name,
1768 p_api_return_status => l_return_status,
1769 p_message_name => 'FTE_TRP_RATING_RATE_NOT_REQ',
1770 p_trip_id => l_trip_info_rec.trip_id,
1771 p_delivery_id => null,
1772 p_delivery_leg_id => null,
1773 x_number_of_errors => l_number_of_errors,
1774 x_number_of_warnings => l_number_of_warnings,
1775 x_return_status => l_return_status_1
1776 );
1777 --
1778 IF (l_return_status_1 = WSH_UTIL_CORE.G_RET_STS_ERROR)
1779 THEN
1780 RAISE FND_API.G_EXC_ERROR;
1781 ELSIF (l_return_status_1 = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
1782 THEN
1783 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1784 ELSIF (l_return_status_1 = WSH_UTIL_CORE.G_RET_STS_WARNING )
1785 THEN
1786 x_return_status := l_return_status_1;
1787 l_warn_count := l_warn_count + 1;
1788 -- RAISE g_finished_success;
1789 END IF;
1790 END IF;
1791
1792 ELSE -- tp_plan_name IS NULL
1793
1794 -- If this is a standard trip, rate it normally
1795 -- redirect to the existing api which does rating
1796 -- at ship confirm
1797
1798 l_rate_mode := 'REGULAR';
1799
1800 END IF;
1801
1802 END IF; -- event = SHIP-CONFIRM
1803
1804 -- Allow re-rating to be done with caller WSH this is for inbound
1805 IF ((p_action_params.event = 'RE-RATING'
1806 AND p_action_params.caller = 'FTE')
1807 OR
1808 (p_action_params.event = 'RE-RATING'
1809 AND p_action_params.caller = 'WSH'))
1810 THEN
1811 l_rate_mode := 'RE-RATE';
1812 END IF;
1813
1814 -- Note :
1815 -- It is possible for a trip to not have lane information for SHIP-CONFIRM
1816 -- (Trip stop closing). The LCSS functionality can search for services
1817 -- in this case.
1818 -- However, for TP Released trips for any event we should have lane_id.
1819 -- Also, for RE-RATING event, we need lane_id.
1820
1821 IF (l_trip_info_rec.lane_id IS NULL) THEN
1822 IF (p_action_params.event = 'SHIP-CONFIRM'
1823 AND l_rate_mode = 'REGULAR'
1824 AND l_trip_info_rec.tp_plan_name IS NULL)
1825 THEN
1826 -- ok to rate
1827 null;
1828 ELSE
1829 -- lane_id is null
1830 -- raise error : lane_id is missing on tp released trip
1831 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1832 FTE_FREIGHT_RATING_DLVY_GRP.api_post_call
1833 (
1834 p_api_name => l_module_name,
1835 p_api_return_status => l_return_status,
1836 p_message_name => 'FTE_TRIP_RATING_MISS_LANE',
1837 p_trip_id => l_trip_info_rec.trip_id,
1838 p_delivery_id => null,
1839 p_delivery_leg_id => null,
1840 x_number_of_errors => l_number_of_errors,
1841 x_number_of_warnings => l_number_of_warnings,
1842 x_return_status => l_return_status_1
1843 );
1844 --
1845 IF (l_return_status_1 = WSH_UTIL_CORE.G_RET_STS_ERROR)
1846 THEN
1847 RAISE FND_API.G_EXC_ERROR;
1848 ELSIF (l_return_status_1 = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
1849 THEN
1850 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1851 ELSIF (l_return_status_1 = WSH_UTIL_CORE.G_RET_STS_WARNING )
1852 THEN
1853 x_return_status := l_return_status_1;
1854 END IF;
1855
1856 END IF;
1857 END IF;
1858
1859
1860 IF l_debug_on THEN
1861 WSH_DEBUG_SV.logmsg( l_module_name,'tripId:'||l_trip_info_rec.trip_id||' mode: '||l_rate_mode
1865 END IF;
1862 ||' plan:'||l_trip_info_rec.tp_plan_name||' laneId:'||l_trip_info_rec.lane_id
1863 ||' modeTrans:'||l_trip_info_rec.mode_of_transport,
1864 WSH_DEBUG_SV.C_PROC_LEVEL);
1866 -- call rate trip API
1867 IF (l_rate_mode IS NOT NULL AND l_rate_mode <> 'NONE') THEN
1868
1869 -- rate trip accordingly
1870 Rate_Trip_Int (
1871 p_trip_info_rec => l_trip_info_rec,
1872 p_rate_mode => l_rate_mode,
1873 p_event => p_action_params.event,
1874 x_return_status => l_return_status,
1875 x_msg_count => l_msg_count,
1876 x_msg_data => l_msg_data );
1877
1878 -- error checking
1879 -- any trip that reports and error
1880 -- causes the entire action to terminate with errors
1881 -- bug 3278059 : Don't set this message if we get warning
1882 -- e.g. reprice not required is generally a warning
1883 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
1884 l_warn_count := l_warn_count + 1;
1885 ELSE
1886
1887 FTE_FREIGHT_RATING_DLVY_GRP.api_post_call
1888 (
1889 p_api_name => l_module_name,
1890 p_api_return_status => l_return_status,
1891 p_message_name => 'FTE_TRP_RATING_TRP_RATE_FAIL',
1892 p_trip_id => l_trip_info_rec.trip_id,
1893 p_delivery_id => null,
1894 p_delivery_leg_id => null,
1895 x_number_of_errors => l_number_of_errors,
1896 x_number_of_warnings => l_number_of_warnings,
1897 x_return_status => l_return_status_1
1898 );
1899 --
1900 IF (l_return_status_1 = WSH_UTIL_CORE.G_RET_STS_ERROR)
1901 THEN
1902 RAISE FND_API.G_EXC_ERROR;
1903 ELSIF (l_return_status_1 = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
1904 THEN
1905 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1906 ELSIF (l_return_status_1 = WSH_UTIL_CORE.G_RET_STS_WARNING )
1907 THEN
1908 x_return_status := l_return_status_1;
1909 END IF;
1910 END IF;
1911
1912 END IF;
1913
1914
1915
1916 -- If reached this point, then the current trip was rated successfully
1917 -- If the trip is part of a continuous move, add the cm to the list
1918 IF (l_trip_info_rec.move_id IS NOT NULL) THEN
1919 IF NOT l_rated_move_ids.EXISTS(l_trip_info_rec.move_id) THEN
1920 l_rated_move_ids(l_trip_info_rec.move_id) := l_trip_info_rec.move_id;
1921 END IF;
1922 END IF;
1923
1924 <<nextpass>>
1925
1926 EXIT WHEN i = p_action_params.trip_id_list.LAST;
1927 i := p_action_params.trip_id_list.NEXT(i);
1928 END LOOP;
1929
1930 --
1931 IF FND_API.To_Boolean( p_commit ) THEN
1932 COMMIT WORK;
1933 END IF;
1934
1935 FND_MSG_PUB.Count_And_Get
1936 (
1937 p_count => x_msg_count,
1938 p_data => x_msg_data,
1939 p_encoded => FND_API.G_FALSE
1940 );
1941 --
1942 IF l_debug_on THEN
1943 WSH_DEBUG_SV.pop(l_module_name);
1944 END IF;
1945 --
1946 --
1947 IF (l_warn_count > 0)
1948 THEN
1949 x_return_status:=WSH_UTIL_CORE.G_RET_STS_WARNING;
1950 END IF;
1951
1952
1953 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'x_return_status='||x_return_status);
1954
1955 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_api_name);
1956 IF p_init_prc_log = 'Y' THEN
1957 FTE_FREIGHT_PRICING_UTIL.close_logs;
1958 END IF;
1959
1960 EXCEPTION
1961 WHEN g_finished_success THEN
1962
1963 IF FND_API.To_Boolean( p_commit ) THEN
1964 COMMIT WORK;
1965 END IF;
1966
1967 FND_MSG_PUB.Count_And_Get
1968 (
1969 p_count => x_msg_count,
1970 p_data => x_msg_data,
1971 p_encoded => FND_API.G_FALSE
1972 );
1973 --
1974 IF l_debug_on THEN
1975 WSH_DEBUG_SV.pop(l_module_name);
1976 END IF;
1977 --
1978 --
1979 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'x_return_status='||x_return_status);
1980
1981 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_api_name);
1982 IF p_init_prc_log = 'Y' THEN
1983 FTE_FREIGHT_PRICING_UTIL.close_logs;
1984 END IF;
1985 --
1986 WHEN FND_API.G_EXC_ERROR THEN
1987 ROLLBACK TO RATE_TRIP;
1988 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1989 FND_MSG_PUB.Count_And_Get
1990 (
1991 p_count => x_msg_count,
1992 p_data => x_msg_data,
1993 p_encoded => FND_API.G_FALSE
1994 );
1995 IF l_debug_on THEN
1996 WSH_DEBUG_SV.pop(l_module_name);
1997 END IF;
1998 FTE_FREIGHT_PRICING_UTIL.set_exit_exception(l_module_name,'FND_API.G_EXC_ERROR');
1999 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_api_name);
2000 IF p_init_prc_log = 'Y' THEN
2001 FTE_FREIGHT_PRICING_UTIL.close_logs;
2002 END IF;
2003 --
2004 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2005 ROLLBACK TO RATE_TRIP;
2006 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2007 FND_MSG_PUB.Count_And_Get
2008 (
2009 p_count => x_msg_count,
2010 p_data => x_msg_data,
2014 WSH_DEBUG_SV.pop(l_module_name);
2011 p_encoded => FND_API.G_FALSE
2012 );
2013 IF l_debug_on THEN
2015 END IF;
2016 FTE_FREIGHT_PRICING_UTIL.set_exit_exception(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR');
2017 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_api_name);
2018 IF p_init_prc_log = 'Y' THEN
2019 FTE_FREIGHT_PRICING_UTIL.close_logs;
2020 END IF;
2021 --
2022 WHEN OTHERS THEN
2023 ROLLBACK TO RATE_TRIP;
2024 WSH_UTIL_CORE.DEFAULT_HANDLER(G_PKG_NAME||'.Rate_Trip');
2025 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_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 IF l_debug_on THEN
2033 WSH_DEBUG_SV.pop(l_module_name);
2034 END IF;
2035 FTE_FREIGHT_PRICING_UTIL.set_exit_exception(l_module_name,'OTHERS');
2036 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
2037 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_api_name);
2038 IF p_init_prc_log = 'Y' THEN
2039 FTE_FREIGHT_PRICING_UTIL.close_logs;
2040 END IF;
2041
2042 END Rate_Trip;
2043
2044
2045 -- This API is called directly from the trip re-rating concurrent program
2046 -- The input to it should be either wsh trip id or wsh trip name
2047
2048 PROCEDURE Rate_Trip_conc (
2049 errbuf OUT NOCOPY VARCHAR2,
2050 retcode OUT NOCOPY VARCHAR2,
2051 p_trip_id IN NUMBER DEFAULT NULL,
2052 p_trip_name IN VARCHAR2 DEFAULT NULL )
2053 IS
2054
2055 l_return_status VARCHAR2(1);
2056 l_msg_count NUMBER;
2057 l_msg_data VARCHAR2(32767);
2058 l_status VARCHAR2(10);
2059 l_temp BOOLEAN;
2060 l_params action_param_rec;
2061 l_trip_id NUMBER;
2062
2063 l_log_level NUMBER := FTE_FREIGHT_PRICING_UTIL.G_LOG;
2064 l_api_name CONSTANT VARCHAR2(30) := 'Rate_Trip_conc';
2065
2066 CURSOR c_get_trip_id(c_trip_name VARCHAR2) IS
2067 SELECT trip_id
2068 FROM wsh_trips
2069 WHERE name = c_trip_name;
2070
2071 BEGIN
2072 FTE_FREIGHT_PRICING_UTIL.initialize_logging(p_debug_mode => 'CONC',
2073 x_return_status => l_return_status );
2074
2075 FTE_FREIGHT_PRICING_UTIL.reset_dbg_vars;
2076 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,l_api_name,'start');
2077
2078 IF p_trip_id is null AND p_trip_name is null THEN
2079 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'both trip id and trip name are null');
2080 raise FND_API.G_EXC_ERROR;
2081 END IF;
2082
2083 l_trip_id := null;
2084 IF p_trip_id is not null THEN
2085 l_trip_id := p_trip_id;
2086 ELSE
2087 OPEN c_get_trip_id(p_trip_name);
2088 FETCH c_get_trip_id INTO l_trip_id;
2089 CLOSE c_get_trip_id;
2090 IF l_trip_id is null THEN
2091 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'cannot get trip id from trip name');
2092 raise FND_API.G_EXC_ERROR;
2093 END IF;
2094 END IF;
2095
2096 l_params.caller := 'FTE';
2097 l_params.event := 'RE-RATING';
2098 l_params.action := 'RATE';
2099 l_params.trip_id_list(1) := l_trip_id;
2100
2101 Rate_Trip (
2102 p_action_params => l_params,
2103 p_commit => FND_API.G_TRUE,
2104 p_init_prc_log => 'N',
2105 x_msg_count => l_msg_count,
2106 x_msg_data => l_msg_data,
2107 x_return_status => l_return_status );
2108
2109 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2110 l_status := 'NORMAL';
2111 errbuf := 'Trip Rerating is completed successfully';
2112 retcode := '0';
2113 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2114 l_status := 'WARNING';
2115 errbuf := 'Trip Rerating is completed with warning';
2116 retcode := '1';
2117 ELSE
2118 l_status := 'ERROR';
2119 errbuf := 'Trip Rerating is completed with error';
2120 retcode := '2';
2121 END IF;
2122
2123 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_status,'');
2124 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_api_name);
2125 FTE_FREIGHT_PRICING_UTIL.close_logs;
2126 EXCEPTION
2127 WHEN FND_API.G_EXC_ERROR THEN
2128 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','');
2129 errbuf := 'Trip Rerating is completed with error';
2130 retcode := '2';
2131 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_api_name);
2132 FTE_FREIGHT_PRICING_UTIL.close_logs;
2133 WHEN OTHERS THEN
2134 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','');
2135 errbuf := 'Trip Rerating is completed with an Unexpected error';
2136 retcode := '2';
2137 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_api_name);
2138 FTE_FREIGHT_PRICING_UTIL.close_logs;
2139
2140 END Rate_Trip_conc;
2141
2142 -- +======================================================================+
2143 -- Procedure :
2144 -- Move_Records_To_Main
2145 --
2146 -- Description:
2147 -- Move rates from temp table to main table
2148 -- Inputs:
2149 -- p_trip_id => trip_id (required)
2150 -- p_lane_id => lane_id (either lane_id or schedule_id
2151 -- required)
2152 -- p_schedule_id => schedule_id
2153 -- p_service_type_code => service_type_code
2154 -- p_comparison_request_id => comparison_request_id (required)
2155 -- Output:
2159 --
2156 -- x_return_status OUT NOCOPY VARCHAR2 => Return status
2157 --
2158 -- Global dependencies:
2160 --
2161 -- DB:
2162 --
2163 -- +======================================================================+
2164
2165 PROCEDURE Move_Records_To_Main(
2166 p_trip_id IN NUMBER,
2167 p_lane_id IN NUMBER,
2168 p_schedule_id IN NUMBER,
2169 p_service_type_code IN VARCHAR2 DEFAULT NULL,
2170 p_comparison_request_id IN NUMBER,
2171 p_init_prc_log IN VARCHAR2 DEFAULT 'Y',
2172 x_return_status OUT NOCOPY VARCHAR2) IS
2173
2174
2175 CURSOR c_get_lane_info(c_lane_id NUMBER)
2176 IS
2177 SELECT mode_of_transportation_code
2178 FROM fte_lanes
2179 WHERE lane_id = c_lane_id;
2180
2181
2182 CURSOR c_get_sched_info(c_schedule_id NUMBER)
2183 IS
2184 SELECT mode_of_transportation_code
2185 FROM fte_lanes l, fte_schedules s
2186 WHERE l.lane_id = s.lane_id
2187 AND s.schedules_id = c_schedule_id;
2188
2189 CURSOR c_get_dlegs_from_trip(c_trip_id IN NUMBER) IS
2190 SELECT dl.delivery_leg_id
2191 FROM wsh_delivery_legs dl ,
2192 wsh_trip_stops s
2193 WHERE dl.pick_up_stop_id = s.stop_id
2194 and s.trip_id=c_trip_id;
2195
2196 l_mode VARCHAR2(30);
2197 l_return_status VARCHAR2(1);
2198 l_rowid VARCHAR2(30);
2199 l_init_msg_list VARCHAR2(30) :=FND_API.G_FALSE;
2200 l_log_level NUMBER := FTE_FREIGHT_PRICING_UTIL.G_DBG;
2201
2202 l_warning_count NUMBER:=0;
2203 BEGIN
2204
2205
2206 IF(p_init_prc_log='Y')
2207 THEN
2208 FTE_FREIGHT_PRICING_UTIL.initialize_logging( p_init_msg_list => l_init_msg_list,
2209 x_return_status => l_return_status );
2210
2211 END IF;
2212
2213
2214
2215 FTE_FREIGHT_PRICING_UTIL.reset_dbg_vars;
2216 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,'Move_Records_To_Main','start');
2217
2218 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2219
2220 SAVEPOINT Move_Records_To_Main_2;
2221 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
2222 'p_trip_id=>'||p_trip_id||' p_lane_id=>'||p_lane_id
2223 ||' p_schedule_id=>'||p_schedule_id||' p_service_type_code='||p_service_type_code
2224 ||'p_comparison_request_id=>'||p_comparison_request_id);
2225
2226 IF (NOT ((p_lane_id IS NULL AND p_schedule_id IS NOT NULL)
2227 OR (p_lane_id IS NOT NULL AND p_schedule_id IS NULL)))
2228 THEN
2229 raise FTE_FREIGHT_PRICING_UTIL.g_tl_move_rec_lane_sched_null;
2230
2231 END IF;
2232
2233 IF (p_lane_id IS NOT NULL) THEN
2234
2235 OPEN c_get_lane_info(p_lane_id);
2236 FETCH c_get_lane_info INTO l_mode;
2237 CLOSE c_get_lane_info;
2238
2239 ELSIF (p_schedule_id IS NOT NULL) THEN
2240
2241 OPEN c_get_sched_info(p_schedule_id);
2242 FETCH c_get_sched_info INTO l_mode;
2243 CLOSE c_get_sched_info;
2244
2245 END IF;
2246
2247 IF (l_mode = 'TRUCK') THEN
2248
2249 FTE_TL_RATING.Move_Records_To_Main(
2250 p_trip_id => p_trip_id,
2251 p_lane_id => p_lane_id,
2252 p_schedule_id => p_schedule_id,
2253 p_comparison_request_id => p_comparison_request_id,
2254 x_return_status => l_return_status);
2255
2256 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2257 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2258 x_return_status := l_return_status;
2259 raise FTE_FREIGHT_PRICING_UTIL.g_tl_move_rec_to_main_fail;
2260 END IF;
2261 ELSE
2262 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
2263 'Moved TL rates successfully');
2264 END IF;
2265
2266 ELSE
2267
2268 FTE_FREIGHT_PRICING.Move_fc_temp_to_main (
2269 p_init_msg_list => fnd_api.g_false,
2270 p_init_prc_log => 'N',
2271 p_request_id => p_comparison_request_id,
2272 p_trip_id => p_trip_id,
2273 p_lane_id => p_lane_id,
2274 p_schedule_id => p_schedule_id,
2275 p_service_type_code => p_service_type_code,
2276 x_return_status => l_return_status);
2277
2278 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2279 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2280 x_return_status := l_return_status;
2281 raise FTE_FREIGHT_PRICING_UTIL.g_nontl_move_rec_to_main_fail;
2282 END IF;
2283 END IF;
2284
2285
2286
2287 END IF;
2288
2289
2290 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Move_Records_To_Main');
2291
2292 IF (l_warning_count > 0)
2293 THEN
2294 x_return_status:=WSH_UTIL_CORE.G_RET_STS_WARNING;
2295 END IF;
2296
2297 IF(p_init_prc_log='Y')
2298 THEN
2299 FTE_FREIGHT_PRICING_UTIL.close_logs;
2300 END IF;
2301
2302
2303 EXCEPTION
2304
2305 WHEN FTE_FREIGHT_PRICING_UTIL.g_nontl_move_rec_to_main_fail THEN
2306 ROLLBACK TO Move_Records_To_Main_2;
2307 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2308 FTE_FREIGHT_PRICING_UTIL.set_exception('Move_Records_To_Main',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_nontl_move_rec_to_main_fail');
2309 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Move_Records_To_Main');
2313 ROLLBACK TO Move_Records_To_Main_2;
2310 FTE_FREIGHT_PRICING_UTIL.close_logs;
2311
2312 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_move_rec_to_main_fail THEN
2314 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2315 FTE_FREIGHT_PRICING_UTIL.set_exception('Move_Records_To_Main',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_move_rec_to_main_fail');
2316 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Move_Records_To_Main');
2317 IF(p_init_prc_log='Y')
2318 THEN
2319 FTE_FREIGHT_PRICING_UTIL.close_logs;
2320 END IF;
2321
2322
2323 WHEN others THEN
2324 ROLLBACK TO Move_Records_To_Main_2;
2325 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2326 FTE_FREIGHT_PRICING_UTIL.set_exception('Move_Records_To_Main',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_others');
2327 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
2328 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Move_Records_To_Main');
2329 IF(p_init_prc_log='Y')
2330 THEN
2331 FTE_FREIGHT_PRICING_UTIL.close_logs;
2332 END IF;
2333
2334
2335 END Move_Records_To_Main;
2336
2337
2338
2339 PROCEDURE Delete_Main_Records(
2340 p_trip_id IN NUMBER,
2341 p_init_prc_log IN VARCHAR2 DEFAULT 'Y',
2342 x_return_status OUT NOCOPY VARCHAR2) IS
2343
2344 l_return_status VARCHAR2(1);
2345
2346 l_init_msg_list VARCHAR2(30) :=FND_API.G_FALSE;
2347 l_mode VARCHAR2(30);
2348 l_log_level NUMBER := FTE_FREIGHT_PRICING_UTIL.G_DBG;
2349
2350 l_warning_count NUMBER:=0;
2351
2352 CURSOR c_trip_mode(c_trip_id NUMBER)
2353 IS
2354 SELECT t.mode_of_transport
2355 FROM wsh_trips t
2356 WHERE t.trip_id=c_trip_id;
2357
2358
2359 BEGIN
2360
2361 IF (p_init_prc_log='Y')
2362 THEN
2363
2364 FTE_FREIGHT_PRICING_UTIL.initialize_logging( p_init_msg_list => l_init_msg_list,
2365 x_return_status => l_return_status );
2366 END IF;
2367
2368 FTE_FREIGHT_PRICING_UTIL.reset_dbg_vars;
2369 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,'Delete_Main_Records','start');
2370
2371 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2372
2373 l_mode:=NULL;
2374
2375 OPEN c_trip_mode(p_trip_id);
2376 FETCH c_trip_mode INTO l_mode;
2377 CLOSE c_trip_mode;
2378
2379 IF ((l_mode is not NULL) AND (l_mode='TRUCK'))
2380 THEN
2381 FTE_TL_RATING.Delete_Main_Records(
2382 p_trip_id=>p_trip_id,
2383 x_return_status=>l_return_status);
2384
2385 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
2386 THEN
2387 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
2388 THEN
2389 raise FTE_FREIGHT_PRICING_UTIL.g_tl_delete_main_rec_fail;
2390 END IF;
2391 END IF;
2392
2393
2394
2395 ELSE
2396
2397 FTE_FREIGHT_PRICING.delete_invalid_fc_recs (
2398 p_segment_id => p_trip_id,
2399 x_return_status => l_return_status ) ;
2400
2401 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2402 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2403 raise FTE_FREIGHT_PRICING_UTIL.g_delete_invalid_fc_failed;
2404 END IF;
2405 END IF;
2406
2407 END IF;
2408
2409 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Delete_Main_Records');
2410
2411 IF (l_warning_count > 0)
2412 THEN
2413 x_return_status:=WSH_UTIL_CORE.G_RET_STS_WARNING;
2414 END IF;
2415
2416 IF (p_init_prc_log='Y')
2417 THEN
2418
2419 FTE_FREIGHT_PRICING_UTIL.close_logs;
2420 END IF;
2421
2422 EXCEPTION
2423
2424 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_delete_main_rec_fail THEN
2425 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2426 FTE_FREIGHT_PRICING_UTIL.set_exception('Delete_Main_Records',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_delete_main_rec_fail');
2427 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Delete_Main_Records');
2428 IF (p_init_prc_log='Y')
2429 THEN
2430
2431 FTE_FREIGHT_PRICING_UTIL.close_logs;
2432 END IF;
2433
2434 WHEN FTE_FREIGHT_PRICING_UTIL.g_delete_invalid_fc_failed THEN
2435 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2436 FTE_FREIGHT_PRICING_UTIL.set_exception('Delete_Main_Records',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_delete_invalid_fc_failed');
2437 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Delete_Main_Records');
2438 IF (p_init_prc_log='Y')
2439 THEN
2440
2441 FTE_FREIGHT_PRICING_UTIL.close_logs;
2442 END IF;
2443
2444
2445 WHEN others THEN
2446 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2447 FTE_FREIGHT_PRICING_UTIL.set_exception('Delete_Main_Records',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_others');
2448 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
2449 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Delete_Main_Records');
2450 IF (p_init_prc_log='Y')
2451 THEN
2452
2453 FTE_FREIGHT_PRICING_UTIL.close_logs;
2454 END IF;
2455
2456
2457 END Delete_Main_Records;
2458
2459
2460 PROCEDURE Copy_LaneSched_To_Rank(
2461 p_lane_rec IN fte_lane_rec,
2462 p_sched_rec IN fte_schedule_rec,
2463 x_rank_rec IN OUT NOCOPY FTE_CARRIER_RANK_LIST_PVT.carrier_rank_list_rec,
2464 x_return_status OUT NOCOPY VARCHAR2)
2465 IS
2466
2467 l_log_level NUMBER := FTE_FREIGHT_PRICING_UTIL.G_DBG;
2468
2469 BEGIN
2470
2471 FTE_FREIGHT_PRICING_UTIL.reset_dbg_vars;
2472 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,'Copy_LaneSched_To_Rank','start');
2473
2474 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2478 THEN
2475
2476
2477 IF(p_lane_rec IS NOT NULL)
2479
2480
2481 x_rank_rec.lane_id:=p_lane_rec.lane_id;
2482 --x_rank_rec.schedule_id:=NULL;
2483 x_rank_rec.carrier_id:=p_lane_rec.carrier_id;
2484 x_rank_rec.mode_of_transport:=p_lane_rec.mode_of_transport_code;
2485 x_rank_rec.service_level:=p_lane_rec.service_code;
2486 x_rank_rec.estimated_transit_time:=p_lane_rec.transit_time;
2487 x_rank_rec.transit_time_uom:=p_lane_rec.transit_time_uom;
2488 --x_rank_rec.schedule_from:=p_lane_rec.schedule_from;
2489 --x_rank_rec.schedule_to:=p_lane_rec.schedule_to;
2490
2491
2492 ELSE
2493
2494 x_rank_rec.lane_id:=p_sched_rec.lane_id;
2495 x_rank_rec.schedule_id:=NULL;
2496 x_rank_rec.carrier_id:=p_sched_rec.carrier_id;
2497 x_rank_rec.mode_of_transport:=p_sched_rec.mode_of_transport_code;
2498 x_rank_rec.service_level:=p_sched_rec.service_code;
2499 x_rank_rec.estimated_transit_time:=p_sched_rec.transit_time;
2500 x_rank_rec.transit_time_uom:=p_sched_rec.transit_time_uom;
2501 --x_rank_rec.schedule_from:=p_sched_rec.schedule_from;
2502 --x_rank_rec.schedule_to:=p_sched_rec.schedule_to;
2503
2504 END IF;
2505
2506 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Copy_LaneSched_To_Rank');
2507
2508 EXCEPTION
2509 WHEN others THEN
2510 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2511 FTE_FREIGHT_PRICING_UTIL.set_exception('Copy_LaneSched_To_Rank',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_others');
2512 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
2513 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Copy_LaneSched_To_Rank');
2514
2515
2516 END Copy_LaneSched_To_Rank;
2517
2518
2519 PROCEDURE LaneSched_Matches_Rank(
2520 p_lane_rec IN fte_lane_rec,
2521 p_schedule_rec IN fte_schedule_rec,
2522 p_rank_rec IN FTE_CARRIER_RANK_LIST_PVT.carrier_rank_list_rec,
2523 x_match_result OUT NOCOPY VARCHAR2,--'Y' or 'N'
2524 x_return_status OUT NOCOPY VARCHAR2)
2525 IS
2526
2527 l_log_level NUMBER := FTE_FREIGHT_PRICING_UTIL.G_DBG;
2528
2529
2530 BEGIN
2531
2532 FTE_FREIGHT_PRICING_UTIL.reset_dbg_vars;
2533 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,'LaneSched_Matches_Rank','start');
2534
2535 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2536
2537
2538 x_match_result:='Y';
2539
2540 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Rank rec:Lane id:'||p_rank_rec.lane_id||
2541 ' Carrier:'||p_rank_rec.carrier_id||' Service level:'||p_rank_rec.service_level||' Mode:'||p_rank_rec.mode_of_transport);
2542
2543
2544 IF(p_lane_rec IS NOT NULL)
2545 THEN
2546
2547 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Lane rec:Lane id:'||p_lane_rec.lane_id||
2548 ' Carrier:'||p_lane_rec.carrier_id||' Service level:'||p_lane_rec.service_code||' Mode:'||p_lane_rec.mode_of_transport_code);
2549
2550 IF(p_rank_rec.carrier_id IS NOT NULL)
2551 THEN
2552 IF((p_lane_rec.carrier_id IS NOT NULL) AND (p_lane_rec.carrier_id <> p_rank_rec.carrier_id))
2553 THEN
2554 x_match_result:='N';
2555 END IF;
2556
2557 END IF;
2558
2559 IF(p_rank_rec.service_level IS NOT NULL)
2560 THEN
2561 IF((p_lane_rec.service_code IS NOT NULL) AND (p_lane_rec.service_code <> p_rank_rec.service_level))
2562 THEN
2563 x_match_result:='N';
2564 END IF;
2565
2566 END IF;
2567
2568
2569 IF(p_rank_rec.mode_of_transport IS NOT NULL)
2570 THEN
2571 IF((p_lane_rec.mode_of_transport_code IS NOT NULL) AND (p_lane_rec.mode_of_transport_code <> p_rank_rec.mode_of_transport))
2572 THEN
2573 x_match_result:='N';
2574 END IF;
2575
2576 END IF;
2577
2578
2579 ELSE
2580
2581 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Schedule rec:Schedule ID'||p_schedule_rec.schedule_id||' Lane id:'||p_schedule_rec.lane_id||
2582 ' Carrier:'||p_schedule_rec.carrier_id||' Service level:'||p_schedule_rec.service_code||' Mode:'||p_schedule_rec.mode_of_transport_code);
2583
2584
2585 IF(p_rank_rec.carrier_id IS NOT NULL)
2586 THEN
2587 IF((p_schedule_rec.carrier_id IS NOT NULL) AND (p_schedule_rec.carrier_id <> p_rank_rec.carrier_id))
2588 THEN
2589 x_match_result:='N';
2590 END IF;
2591
2592 END IF;
2593
2594 IF(p_rank_rec.service_level IS NOT NULL)
2595 THEN
2596 IF((p_schedule_rec.service_code IS NOT NULL) AND (p_schedule_rec.service_code <> p_rank_rec.service_level))
2597 THEN
2598 x_match_result:='N';
2599 END IF;
2600
2601 END IF;
2602
2603
2604 IF(p_rank_rec.mode_of_transport IS NOT NULL)
2605 THEN
2606 IF((p_schedule_rec.mode_of_transport_code IS NOT NULL) AND (p_schedule_rec.mode_of_transport_code <> p_rank_rec.mode_of_transport))
2607 THEN
2608 x_match_result:='N';
2609 END IF;
2610
2611 END IF;
2612
2613
2614
2615 END IF;
2616
2617
2618
2619
2620
2621
2622 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Match result:'||x_match_result);
2623
2624 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'LaneSched_Matches_Rank');
2625
2626 EXCEPTION
2627
2628 WHEN others THEN
2629 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2630 FTE_FREIGHT_PRICING_UTIL.set_exception('LaneSched_Matches_Rank',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_others');
2631 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
2632 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'LaneSched_Matches_Rank');
2633
2634
2635 END LaneSched_Matches_Rank;
2636
2637 PROCEDURE Search_Multi_ShipMethods(
2641 x_ref OUT NOCOPY dbms_utility.number_array,
2638 p_ss_attr_rec IN FTE_SS_ATTR_REC,
2639 p_ss_rate_sort_tab IN FTE_CARRIER_RANK_LIST_PVT.carrier_rank_list_tbl_type,
2640 x_ss_rate_sort_tab OUT NOCOPY FTE_CARRIER_RANK_LIST_PVT.carrier_rank_list_tbl_type,
2642 x_return_status OUT NOCOPY VARCHAR2)
2643 IS
2644 l_match_result VARCHAR2(1);
2645 i NUMBER;
2646 j NUMBER;
2647 k NUMBER;
2648 l_search_criteria_tab fte_search_criteria_tab;
2649 l_search_criteria_rec fte_search_criteria_rec;
2650 l_lanes_tab fte_lane_tab;
2651 l_schedules_tab fte_schedule_tab;
2652 l_lane_rec fte_lane_rec;
2653 l_msg_data VARCHAR2(32767);
2654 l_warning_count NUMBER:=0;
2655 l_return_status VARCHAR2(1);
2656 l_log_level NUMBER := FTE_FREIGHT_PRICING_UTIL.G_DBG;
2657
2658 BEGIN
2659
2660
2661 FTE_FREIGHT_PRICING_UTIL.reset_dbg_vars;
2662 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,'Search_Multi_ShipMethods','start');
2663
2664 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2665
2666 l_search_criteria_tab:=fte_search_criteria_tab();
2667 j:=1;
2668
2669 i:=p_ss_rate_sort_tab.FIRST;
2670 WHILE ( i IS NOT NULL)
2671 LOOP
2672 IF (p_ss_rate_sort_tab(i).lane_id IS NULL)
2673 THEN
2674
2675 l_search_criteria_rec := fte_search_criteria_rec(
2676 relax_flag => 'Y',
2677 origin_loc_id => p_ss_attr_rec.origin_location_id,
2678 destination_loc_id => p_ss_attr_rec.destination_location_id,
2679 origin_country => null,
2680 origin_state => null,
2681 origin_city => null,
2682 origin_zip => null,
2683 destination_country => null,
2684 destination_state => null,
2685 destination_city => null,
2686 destination_zip => null,
2687 mode_of_transport => p_ss_rate_sort_tab(i).mode_of_transport,
2688 lane_number => null,
2689 carrier_id => p_ss_rate_sort_tab(i).carrier_id,
2690 carrier_name => null,
2691 commodity_catg_id => null,
2692 commodity => null,
2693 service_code => p_ss_rate_sort_tab(i).service_level,
2694 service => null,
2695 --equipment_code => null, -- removed J+
2696 --equipment => null, -- removed J+
2697 schedule_only_flag => null,
2698 dep_date_from => p_ss_attr_rec.dep_date_from,
2699 dep_date_to => p_ss_attr_rec.dep_date_to,
2700 arr_date_from => p_ss_attr_rec.arr_date_from,
2701 arr_date_to => p_ss_attr_rec.arr_date_to,
2702 lane_ids_string => null,
2703 delivery_leg_id => null,
2704 exists_in_database => null,
2705 delivery_id => null,
2706 sequence_number => null,
2707 pick_up_stop_id => p_ss_attr_rec.pick_up_stop_id,
2708 drop_off_stop_id => p_ss_attr_rec.drop_off_stop_id,
2709 pickupstop_location_id => p_ss_attr_rec.pick_up_stop_location_id,
2710 dropoffstop_location_id => p_ss_attr_rec.drop_off_stop_location_id,
2711 ship_to_site_id => null,
2712 vehicle_id => null,
2713 --Changes made to fte_search_criteria_rec 19-FEB-2004
2714 effective_date => p_ss_attr_rec.dep_date_from,
2715 effective_date_type => '=',
2716 tariff_name => null -- Added J+
2717 );
2718
2719 l_search_criteria_tab.EXTEND(1);
2720 l_search_criteria_tab(j):=l_search_criteria_rec;
2721 j:=j+1;
2722
2723
2724 END IF;
2725
2726 i:=p_ss_rate_sort_tab.NEXT(i);
2727 END LOOP;
2728
2729
2730 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'Calling FTE_LANE_SEARCH.Search_Lanes:'||TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));
2731
2732 FTE_LANE_SEARCH.Search_Lanes(
2733 p_search_criteria=>l_search_criteria_tab,
2734 p_num_results=>999,
2735 p_search_type=> 'L',
2736 x_lane_results=>l_lanes_tab,
2737 x_schedule_results=>l_schedules_tab,
2738 x_return_message=>l_msg_data,
2739 x_return_status=>l_return_status);
2740
2741 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'After FTE_LANE_SEARCH.Search_Lanes:'||TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));
2742 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'Status:'||l_return_status||':Message:'||l_msg_data);
2743
2744 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
2745 THEN
2746 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
2747 THEN
2748 raise FTE_FREIGHT_PRICING_UTIL.g_lane_search_failed;
2749 ELSE
2750 l_warning_count:=l_warning_count+1;
2751
2752 END IF;
2753 END IF;
2754
2755 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'DBG-1');
2756 k:=1;
2757 j:=p_ss_rate_sort_tab.FIRST;
2758 i:=l_lanes_tab.FIRST;
2759 WHILE((j IS NOT NULL) OR (i IS NOT NULL))
2760 LOOP
2761 IF ( (i IS NOT NULL) AND l_lanes_tab.EXISTS(i))
2762 THEN
2763 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'Lane id:'||l_lanes_tab(i).lane_id||'transit time:'||l_lanes_tab(i).transit_time||' UOM:'||l_lanes_tab(i).transit_time_uom);
2764 ELSE
2765 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'No lane tab for i:'||i);
2766 END IF;
2767 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'DBG-2 i:'||i||' j:'||j);
2768 IF((j IS NOT NULL) AND (i IS NOT NULL) AND (p_ss_rate_sort_tab.EXISTS(j)) AND (p_ss_rate_sort_tab(j).lane_id IS NULL))
2769 THEN
2770 --This rank was used for lane search
2771 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'DBG-3 i:'||i||' j:'||j);
2772 l_match_result:='N';
2773 WHILE((l_match_result='N') AND (j IS NOT NULL))
2774 LOOP
2775
2776 LaneSched_Matches_Rank(
2777 p_lane_rec =>l_lanes_tab(i),
2778 p_schedule_rec =>NULL,
2779 p_rank_rec =>p_ss_rate_sort_tab(j),
2780 x_match_result =>l_match_result,
2784 THEN
2781 x_return_status =>l_return_status);
2782
2783 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
2785 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
2786 THEN
2787 raise FTE_FREIGHT_PRICING_UTIL.g_lane_matches_rank_fail;
2788 END IF;
2789 END IF;
2790 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'DBG-4 i:'||i||' j:'||j);
2791 IF(l_match_result='N')
2792 THEN
2793 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'DBG-5 i:'||i||' j:'||j);
2794 j:=p_ss_rate_sort_tab.NEXT(j);
2795
2796 END IF;
2797
2798 END LOOP;
2799 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'DBG-6 i:'||i||' j:'||j);
2800 IF (l_match_result='N')
2801 THEN
2802 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'Lane did not match with any rank');
2803
2804 ELSE
2805 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'DBG-7 i:'||i||' j:'||j);
2806 x_ss_rate_sort_tab(k):=p_ss_rate_sort_tab(j);
2807
2808 Copy_LaneSched_To_Rank(
2809 p_lane_rec =>l_lanes_tab(i),
2810 p_sched_rec =>NULL,
2811 x_rank_rec =>x_ss_rate_sort_tab(k),
2812 x_return_status =>l_return_status);
2813
2814 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
2815 THEN
2816 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
2817 THEN
2818 raise FTE_FREIGHT_PRICING_UTIL.g_copy_lane_rank_fail;
2819 END IF;
2820 END IF;
2821 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'DBG-8 i:'||i||' j:'||j);
2822
2823 x_ref(k):=j;
2824 k:=k+1;
2825 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'DBG-9 i:'||i||' j:'||j);
2826
2827 END IF;
2828 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'DBG-10 i:'||i||' j:'||j);
2829 IF (i IS NOT NULL)
2830 THEN
2831 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'DBG-11 i:'||i||' j:'||j);
2832 i:=l_lanes_tab.NEXT(i);
2833 ELSIF(j IS NOT NULL)
2834 THEN
2835 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'DBG-12 i:'||i||' j:'||j);
2836 j:=p_ss_rate_sort_tab.NEXT(j);
2837 END IF;
2838
2839
2840
2841 ELSIF ((j IS NOT NULL) AND (p_ss_rate_sort_tab.EXISTS(j)) AND (p_ss_rate_sort_tab(j).lane_id IS NOT NULL))
2842 THEN
2843 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'DBG-13 i:'||i||' j:'||j);
2844 --This rank was not used for lane search
2845 --Copy to output
2846
2847 x_ss_rate_sort_tab(k):=p_ss_rate_sort_tab(j);
2848 x_ref(k):=j;
2849 k:=k+1;
2850 j:=p_ss_rate_sort_tab.NEXT(j);
2851
2852 ELSIF( i IS NULL AND j IS NOT NULL)
2853 THEN
2854 j:=p_ss_rate_sort_tab.NEXT(j);
2855
2856 END IF;
2857
2858 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'DBG-14 i:'||i||' j:'||j);
2859
2860 END LOOP;
2861
2862
2863
2864 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'DBG-15 i:'||i||' j:'||j);
2865
2866 j:=p_ss_rate_sort_tab.FIRST;
2867 i:=l_schedules_tab.FIRST;
2868 WHILE((j IS NOT NULL) OR (i IS NOT NULL))
2869 LOOP
2870 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'DBG-16 i:'||i||' j:'||j);
2871 IF((j IS NOT NULL) AND (i IS NOT NULL) AND (p_ss_rate_sort_tab.EXISTS(j)) AND (p_ss_rate_sort_tab(j).schedule_id IS NULL))
2872 THEN
2873 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'DBG-17 i:'||i||' j:'||j);
2874 --This rank was used for lane search
2875
2876 l_match_result:='N';
2877 WHILE((l_match_result='N') AND (j IS NOT NULL))
2878 LOOP
2879 LaneSched_Matches_Rank(
2880 p_lane_rec =>NULL,
2881 p_schedule_rec =>l_schedules_tab(i),
2882 p_rank_rec =>p_ss_rate_sort_tab(j),
2883 x_match_result =>l_match_result,
2884 x_return_status =>l_return_status);
2885
2886 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
2887 THEN
2888 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
2889 THEN
2890 raise FTE_FREIGHT_PRICING_UTIL.g_lane_matches_rank_fail;
2891 END IF;
2892 END IF;
2893
2894 IF(l_match_result='N')
2895 THEN
2896 j:=p_ss_rate_sort_tab.NEXT(j);
2897
2898 END IF;
2899
2900 END LOOP;
2901
2902 IF (l_match_result='N')
2903 THEN
2904 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'Lane did not match with any rank');
2905
2906 ELSE
2907 x_ss_rate_sort_tab(k):=p_ss_rate_sort_tab(j);
2908
2909 Copy_LaneSched_To_Rank(
2910 p_lane_rec =>NULL,
2911 p_sched_rec =>l_schedules_tab(i),
2912 x_rank_rec =>x_ss_rate_sort_tab(k),
2913 x_return_status =>l_return_status);
2914
2915 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
2916 THEN
2917 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
2918 THEN
2919 raise FTE_FREIGHT_PRICING_UTIL.g_copy_lane_rank_fail;
2920 END IF;
2921 END IF;
2922
2923
2924 x_ref(k):=j;
2925 k:=k+1;
2926
2927
2928 END IF;
2929
2930 IF (i IS NOT NULL)
2931 THEN
2932 i:=l_lanes_tab.NEXT(i);
2933 ELSIF(j IS NOT NULL)
2934 THEN
2935 j:=p_ss_rate_sort_tab.NEXT(j);
2936 END IF;
2937
2938
2939
2940 ELSIF ((j IS NOT NULL) AND (p_ss_rate_sort_tab.EXISTS(j)) AND (p_ss_rate_sort_tab(j).schedule_id IS NOT NULL))
2941 THEN
2942 --This rank was not used for lane search
2943 --Copy to output
2944
2945 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'DBG-18 i:'||i||' j:'||j);
2946 x_ss_rate_sort_tab(k):=p_ss_rate_sort_tab(j);
2947 x_ref(k):=j;
2948 k:=k+1;
2949 j:=p_ss_rate_sort_tab.NEXT(j);
2950
2951 ELSIF( i IS NULL AND j IS NOT NULL)
2952 THEN
2953 j:=p_ss_rate_sort_tab.NEXT(j);
2954
2958 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'DBG-19 i:'||i||' j:'||j);
2955
2956
2957 END IF;
2959 END LOOP;
2960
2961 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'DBG-20 i:'||i||' j:'||j);
2962
2963 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Search_Multi_ShipMethods');
2964
2965 IF (l_warning_count > 0)
2966 THEN
2967 x_return_status:=WSH_UTIL_CORE.G_RET_STS_WARNING;
2968 END IF;
2969
2970
2971 EXCEPTION
2972
2973 WHEN FTE_FREIGHT_PRICING_UTIL.g_lane_search_failed THEN
2974 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2975 FTE_FREIGHT_PRICING_UTIL.set_exception('Search_Multi_ShipMethods',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_lane_search_failed');
2976 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Search_Multi_ShipMethods');
2977
2978
2979
2980 WHEN FTE_FREIGHT_PRICING_UTIL.g_lane_matches_rank_fail THEN
2981 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2982 FTE_FREIGHT_PRICING_UTIL.set_exception('Search_Multi_ShipMethods',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_lane_matches_rank_fail');
2983 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Search_Multi_ShipMethods');
2984
2985
2986 WHEN FTE_FREIGHT_PRICING_UTIL.g_copy_lane_rank_fail THEN
2987 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2988 FTE_FREIGHT_PRICING_UTIL.set_exception('Search_Multi_ShipMethods',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_copy_lane_rank_fail');
2989 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Search_Multi_ShipMethods');
2990
2991 WHEN others THEN
2992 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2993 FTE_FREIGHT_PRICING_UTIL.set_exception('Search_Multi_ShipMethods',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_others');
2994 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
2995 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Search_Multi_ShipMethods');
2996
2997
2998 END Search_Multi_ShipMethods;
2999
3000
3001 PROCEDURE Display_Attr_Rec(p_attr_rec IN FTE_SS_ATTR_REC)
3002 IS
3003
3004 l_log_level NUMBER := FTE_FREIGHT_PRICING_UTIL.G_DBG;
3005
3006 BEGIN
3007
3008
3009 FTE_FREIGHT_PRICING_UTIL.reset_dbg_vars;
3010 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,'Display_Attr_Rec','start');
3011
3012
3013 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
3014 ' ');
3015 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
3016 'START ATTR REC');
3017 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
3018 ' DELIVERY_ID :'||p_attr_rec.delivery_id);
3019 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
3020 ' DELIVERY_LEG_ID :'||p_attr_rec.delivery_leg_id);
3021 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
3022 ' TRIP_ID :'||p_attr_rec.trip_id);
3023 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
3024 ' ARR_DATE_TO :'||p_attr_rec.arr_date_to);
3025 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
3026 ' ARR_DATE_FROM :'||p_attr_rec.arr_date_from);
3027 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
3028 ' DEP_DATE_TO :'||p_attr_rec.dep_date_to);
3029 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
3030 ' DEP_DATE_FROM :'||p_attr_rec.dep_date_from);
3031 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
3032 ' ORIGIN_LOCATION_ID :'||p_attr_rec.origin_location_id);
3033 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
3034 ' DESTI_LOCATION_ID :'||p_attr_rec.destination_location_id);
3035 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
3036 ' PICK_UP_STOP_ID :'||p_attr_rec.pick_up_stop_id);
3037 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
3038 ' DROP_OFF_STOP_ID :'||p_attr_rec.drop_off_stop_id);
3039 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
3040 ' PICK_UP_STOP_LOC_ID:'||p_attr_rec.pick_up_stop_location_id);
3041 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
3042 ' DROP_OFF_STOP_LC_ID:'||p_attr_rec.drop_off_stop_location_id);
3043 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
3044 ' LANE_ID :'||p_attr_rec.lane_id);
3045 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
3046 ' SCHEDULE_ID :'||p_attr_rec.schedule_id);
3047 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
3048 ' CARRIER_ID :'||p_attr_rec.carrier_id);
3049 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
3050 ' MODE_OF_TRANSPORT :'||p_attr_rec.mode_of_transport);
3051 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
3052 ' SERVICE_LEVEL :'||p_attr_rec.service_level);
3053 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
3054 ' VEHICLE_ITEM_ID :'||p_attr_rec.vehicle_item_id);
3055 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
3056 ' VEHICLE_ORG_ID :'||p_attr_rec.vehicle_org_id);
3057 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
3058 ' RULE_ID :'||p_attr_rec.rule_id);
3059 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
3060 ' RANK_ID :'||p_attr_rec.rank_id);
3061 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
3062 ' APPEND_LIST_FLAG :'||p_attr_rec.append_list_flag);
3063 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
3064 'END ATTR REC');
3065 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
3066 ' ');
3067
3068 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Display_Attr_Rec');
3069
3070 END Display_Attr_Rec;
3071
3072
3073
3074 PROCEDURE Display_Rank_Rec(p_rank_rec IN FTE_CARRIER_RANK_LIST_PVT.carrier_rank_list_rec)
3075 IS
3076 l_log_level NUMBER := FTE_FREIGHT_PRICING_UTIL.G_DBG;
3077
3081 FTE_FREIGHT_PRICING_UTIL.reset_dbg_vars;
3078 BEGIN
3079
3080
3082 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,'Display_Rank_Rec','start');
3083
3084
3085 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
3086 ' ');
3087 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
3088 'START RANK REC');
3089 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
3090 ' RANK_ID :'||p_rank_rec.rank_id);
3091 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
3092 ' TRIP_ID :'||p_rank_rec.trip_id);
3093 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
3094 ' RANK_SEQUENCE :'||p_rank_rec.rank_sequence);
3095 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
3096 ' CARRIER_ID :'||p_rank_rec.carrier_id);
3097 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
3098 ' SERVICE_LEVEL :'||p_rank_rec.service_level);
3099 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
3100 ' MODE_OF_TRANSPORT :'||p_rank_rec.mode_of_transport);
3101 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
3102 ' LANE_ID :'||p_rank_rec.lane_id);
3103 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
3104 ' SOURCE :'||p_rank_rec.source);
3105 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
3106 ' ENABLED :'||p_rank_rec.enabled);
3107 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
3108 ' ESTIMATED_RATE :'||p_rank_rec.estimated_rate);
3109 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
3110 ' CURRENCY_CODE :'||p_rank_rec.currency_code);
3111 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
3112 ' VEHICLE_ITEM_ID :'||p_rank_rec.vehicle_item_id);
3113 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
3114 ' ESTIMATED_TRANSIT_T:'||p_rank_rec.estimated_transit_time);
3115 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
3116 ' TRANSIT_TIME_UOM :'||p_rank_rec.transit_time_uom);
3117 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
3118 ' VERSION :'||p_rank_rec.version);
3119 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
3120 ' CONSIGNEE_CR_AC_NO :'||p_rank_rec.consignee_carrier_ac_no);
3121 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
3122 ' FREIGHT_TERMS_CODE :'||p_rank_rec.freight_terms_code);
3123 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
3124 ' INITSMCONFIG :'||p_rank_rec.initsmconfig);
3125 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
3126 ' CREATION_DATE :'||p_rank_rec.creation_date);
3127 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
3128 ' CREATED_BY :'||p_rank_rec.created_by);
3129 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
3130 ' LAST_UPDATE_DATE :'||p_rank_rec.last_update_date);
3131 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
3132 ' LAST_UPDATE_BY :'||p_rank_rec.last_updated_by);
3133 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
3134 ' LAST_UPDATE_LOGIN :'||p_rank_rec.last_update_login);
3135 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
3136 ' IS_CURRENT :'||p_rank_rec.is_current);
3137 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
3138 ' SINGLE_CURR_RATE :'||p_rank_rec.single_curr_rate);
3139 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
3140 ' SORT :'||p_rank_rec.sort);
3141 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
3142 ' SCHEDULE_FROM :'||p_rank_rec.schedule_from);
3143 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
3144 ' SCHEDULE_TO :'||p_rank_rec.schedule_to);
3145 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
3146 ' SCHEDULE_ID :'||p_rank_rec.schedule_id);
3147 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
3148 ' VEHICLE_ORG_ID :'||p_rank_rec.vehicle_org_id);
3149 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
3150 'END RANK REC');
3151 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
3152 ' ');
3153
3154 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Display_Rank_Rec');
3155
3156 END Display_Rank_Rec;
3157
3158 PROCEDURE Display_Rank_Tab(
3159 p_rank_tab IN FTE_CARRIER_RANK_LIST_PVT.carrier_rank_list_tbl_type,
3160 p_input_ref IN dbms_utility.number_array)
3161 IS
3162 l_log_level NUMBER := FTE_FREIGHT_PRICING_UTIL.G_DBG;
3163 i NUMBER;
3164 BEGIN
3165
3166 FTE_FREIGHT_PRICING_UTIL.reset_dbg_vars;
3167 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,'Display_Rank_Tab','start');
3168
3169 IF (p_rank_tab IS NOT NULL)
3170 THEN
3171 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'Number of Rank recs:'||p_rank_tab.COUNT );
3172
3173 i:=p_rank_tab.FIRST;
3174 WHILE ( i IS NOT NULL)
3175 LOOP
3176
3177 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'Rank rec:'||i );
3178
3179 IF(p_input_ref IS NOT NULL AND p_input_ref.EXISTS(i))
3180 THEN
3181 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'Input ref:'|| p_input_ref(i));
3182 END IF;
3183
3184 Display_Rank_Rec(p_rank_tab(i));
3185 i:=p_rank_tab.NEXT(i);
3186 END LOOP;
3187
3188 END IF;
3189
3190
3191 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Display_Rank_Tab');
3192
3193 END Display_Rank_Tab;
3194
3195 PROCEDURE Get_Vehicle_Item_Org(
3196 p_vehicle_type IN NUMBER,
3197 x_vehicle_item_id OUT NOCOPY NUMBER,
3198 x_vehicle_item_org OUT NOCOPY NUMBER,
3199 x_return_status OUT NOCOPY VARCHAR2)
3200 IS
3201
3202 CURSOR c_get_veh_item_org(c_vehicle_type IN NUMBER)
3203 IS
3207 WHERE v.vehicle_type_id=c_vehicle_type;
3204 SELECT v.inventory_item_id,
3205 v.organization_id
3206 FROM FTE_VEHICLE_TYPES v
3208
3209
3210 l_log_level NUMBER := FTE_FREIGHT_PRICING_UTIL.G_DBG;
3211
3212 BEGIN
3213
3214 FTE_FREIGHT_PRICING_UTIL.reset_dbg_vars;
3215 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,'Get_Vehicle_Item_Org','start');
3216
3217 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3218
3219
3220 x_vehicle_item_id:=NULL;
3221 x_vehicle_item_org:=NULL;
3222
3223 OPEN c_get_veh_item_org(p_vehicle_type);
3224 FETCH c_get_veh_item_org INTO x_vehicle_item_id,x_vehicle_item_org;
3225 CLOSE c_get_veh_item_org;
3226
3227 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Get_Vehicle_Item_Org');
3228
3229 EXCEPTION
3230
3231 WHEN others THEN
3232 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3233 FTE_FREIGHT_PRICING_UTIL.set_exception('Get_Vehicle_Item_Org',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_others');
3234 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
3235 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Get_Vehicle_Item_Org');
3236
3237
3238 END Get_Vehicle_Item_Org;
3239
3240
3241 PROCEDURE Seq_Tender_Sort(
3242 p_ss_rate_sort_tab IN FTE_CARRIER_RANK_LIST_PVT.carrier_rank_list_tbl_type,
3243 p_input_ref IN dbms_utility.number_array ,-- Reference of p_ss_rate_sort_tab to input ranks
3244 x_ss_rate_sort_tab OUT NOCOPY FTE_CARRIER_RANK_LIST_PVT.carrier_rank_list_tbl_type,
3245 x_input_ref OUT NOCOPY dbms_utility.number_array ,-- Reference of p_ss_rate_sort_tab to input ranks
3246 x_return_status OUT NOCOPY VARCHAR2)
3247 IS
3248 i NUMBER;
3249 l_single_currency VARCHAR2(3);
3250 l_single_curr_rate NUMBER;
3251 l_time_uom VARCHAR2(3);
3252 l_transit_time NUMBER;
3253 l_values_tab Sort_Value_Tab_Type;
3254 l_values_rec Sort_Value_Rec_Type;
3255 l_sorted_index dbms_utility.number_array;
3256 l_log_level NUMBER := FTE_FREIGHT_PRICING_UTIL.G_DBG;
3257 l_return_status VARCHAR2(1);
3258 BEGIN
3259
3260
3261 FTE_FREIGHT_PRICING_UTIL.reset_dbg_vars;
3262 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,'Seq_Tender_Sort','start');
3263
3264 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3265
3266
3267 l_single_currency:=NULL;
3268 l_time_uom:=NULL;
3269 i:=p_ss_rate_sort_tab.FIRST;
3270 WHILE( i IS NOT NULL)
3271 LOOP
3272 --Calculate transit time in a single uom
3273 --Used for sorting
3274
3275 l_transit_time:=NULL;
3276 IF((p_ss_rate_sort_tab(i).estimated_transit_time IS NOT NULL ) AND
3277 (p_ss_rate_sort_tab(i).transit_time_uom IS NOT NULL ))
3278 THEN
3279 IF(l_time_uom IS NOT NULL)
3280 THEN
3281 l_time_uom:=p_ss_rate_sort_tab(i).transit_time_uom;
3282 END IF;
3283
3284 l_transit_time:=FTE_FREIGHT_PRICING_UTIL.convert_uom(
3285 p_ss_rate_sort_tab(i).transit_time_uom,
3286 l_time_uom,
3287 p_ss_rate_sort_tab(i).estimated_transit_time,
3288 0);
3289
3290 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'index:'||i||'Transit time:'||l_transit_time);
3291
3292
3293 END IF;
3294
3295
3296 --Populate Sort input structures
3297
3298 l_values_tab(i):=l_values_rec;
3299
3300 --RL < UI
3301 IF (p_ss_rate_sort_tab(i).sort IS NOT NULL)
3302 THEN
3303 IF (p_ss_rate_sort_tab(i).sort='RL')
3304 THEN
3305 l_values_tab(i).value(1):=SORT_TYPE_RL;
3306
3307 ELSIF (p_ss_rate_sort_tab(i).sort='UI')
3308 THEN
3309 l_values_tab(i).value(1):=SORT_TYPE_UI;
3310 END IF;
3311
3312 END IF;
3313
3314 --Input reference
3315 l_values_tab(i).value(2):=p_input_ref(i);
3316
3317 --Rates
3318
3319 l_values_tab(i).value(3):=p_ss_rate_sort_tab(i).single_curr_rate;
3320
3321 --Transit time
3322 l_values_tab(i).value(4):=l_transit_time;
3323
3324 --Lane
3325 l_values_tab(i).value(5):=p_ss_rate_sort_tab(i).lane_id;
3326
3327 --Vehicle
3328 l_values_tab(i).value(6):=p_ss_rate_sort_tab(i).vehicle_item_id;
3329
3330 i:=p_ss_rate_sort_tab.NEXT(i);
3331 END LOOP;
3332
3333
3334
3335 Sort(
3336 p_values_tab=>l_values_tab,
3337 p_sort_type=>'SEQ_TENDER',
3338 x_sorted_index=>l_sorted_index,
3339 x_return_status=>l_return_status);
3340
3341 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
3342 THEN
3343 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
3344 THEN
3345 raise FTE_FREIGHT_PRICING_UTIL.g_sort_fail;
3346 END IF;
3347 END IF;
3348
3349
3350 i:=l_sorted_index.FIRST;
3351 WHILE( i IS NOT NULL)
3352 LOOP
3353 IF(p_ss_rate_sort_tab.EXISTS(l_sorted_index(i)))
3354 THEN
3355 x_ss_rate_sort_tab(i):=p_ss_rate_sort_tab(l_sorted_index(i));
3356 x_input_ref(i):=p_input_ref(l_sorted_index(i));
3357 ELSE
3358
3359 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Sorted value index:'||i||' Does not exist in input at:'||l_sorted_index(i));
3360 END IF;
3361
3362 i:=l_sorted_index.NEXT(i);
3363 END LOOP;
3364
3365 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Seq_Tender_Sort');
3366
3367 EXCEPTION
3368
3369 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_conv_currency_fail THEN
3370 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3371 FTE_FREIGHT_PRICING_UTIL.set_exception('Seq_Tender_Sort',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_conv_currency_fail');
3372 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Seq_Tender_Sort');
3373
3374
3375 WHEN FTE_FREIGHT_PRICING_UTIL.g_sort_fail THEN
3379
3376 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3377 FTE_FREIGHT_PRICING_UTIL.set_exception('Seq_Tender_Sort',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_sort_fail');
3378 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Seq_Tender_Sort');
3380 WHEN others THEN
3381 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3382 FTE_FREIGHT_PRICING_UTIL.set_exception('Seq_Tender_Sort',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_others');
3383 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
3384 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Seq_Tender_Sort');
3385
3386
3387 END Seq_Tender_Sort;
3388
3389 PROCEDURE Remove_Duplicate_Lanes(
3390 p_ss_rate_sort_tab IN FTE_CARRIER_RANK_LIST_PVT.carrier_rank_list_tbl_type,
3391 p_input_ref IN dbms_utility.number_array ,-- Reference of p_ss_rate_sort_tab to input ranks
3392 x_ss_rate_sort_tab OUT NOCOPY FTE_CARRIER_RANK_LIST_PVT.carrier_rank_list_tbl_type,
3393 x_input_ref OUT NOCOPY dbms_utility.number_array ,-- Reference of p_ss_rate_sort_tab to input ranks
3394 x_return_status OUT NOCOPY VARCHAR2)
3395 IS
3396 i NUMBER;
3397 j NUMBER;
3398 l_prev_index NUMBER;
3399 l_values_tab Sort_Value_Tab_Type;
3400 l_values_rec Sort_Value_Rec_Type;
3401 l_sorted_index dbms_utility.number_array;
3402 l_log_level NUMBER := FTE_FREIGHT_PRICING_UTIL.G_DBG;
3403
3404 l_eliminated_index dbms_utility.number_array;
3405
3406 l_index NUMBER;
3407 l_result VARCHAR2(1);
3408 l_return_status VARCHAR2(1);
3409 BEGIN
3410
3411
3412 FTE_FREIGHT_PRICING_UTIL.reset_dbg_vars;
3413 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,'Remove_Duplicate_Lanes','start');
3414
3415 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3416
3417
3418 --Sort based on on schedule,lane,vehicle,rank reference
3419 --This will result in a list where the entries having the same
3420 --schedule/lane/vehicle will be contigous, and in order of rank reference
3421 --For all contigous entries having the same schedule/lane/vehicle,all except the first can be
3422 --eliminated
3423
3424 i:=p_ss_rate_sort_tab.FIRST;
3425 WHILE(i IS NOT NULL)
3426 LOOP
3427
3428 l_values_tab(i):=l_values_rec;
3429
3430 l_values_tab(i).value(1):=p_ss_rate_sort_tab(i).schedule_id;
3431 l_values_tab(i).value(2):=p_ss_rate_sort_tab(i).lane_id;
3432 l_values_tab(i).value(3):=p_ss_rate_sort_tab(i).vehicle_item_id;
3433 l_values_tab(i).value(4):=i;-- To ensure that higher ranks
3434
3435 i:=p_ss_rate_sort_tab.NEXT(i);
3436 END LOOP;
3437
3438 Sort(
3439 p_values_tab=>l_values_tab,
3440 p_sort_type=>'ELIM_DUPLICATES',
3441 x_sorted_index=>l_sorted_index,
3442 x_return_status=>l_return_status);
3443
3444 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
3445 THEN
3446 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
3447 THEN
3448 raise FTE_FREIGHT_PRICING_UTIL.g_sort_fail;
3449 END IF;
3450 END IF;
3451
3452 --Mark duplicates for removal
3453
3454 l_prev_index:=NULL;
3455 i:=l_sorted_index.FIRST;
3456 WHILE( i IS NOT NULL)
3457 LOOP
3458 IF(l_prev_index IS NOT NULL)
3459 THEN
3460
3461
3462
3463 l_index:=l_values_tab(l_sorted_index(i)).value(4);
3464
3465 l_eliminated_index(l_index):=1;
3466
3467 --Ignore rank reference for equality test;
3468 l_values_tab(l_sorted_index(i)).value(4):=NULL;
3469 l_values_tab(l_sorted_index(l_prev_index)).value(4):=NULL;
3470
3471 Compare_Sort_Value(
3472 p_value1=>l_values_tab(l_sorted_index(i)),
3473 p_value2=>l_values_tab(l_sorted_index(l_prev_index)) ,
3474 x_result=> l_result,
3475 x_return_status=>l_return_status);
3476
3477
3478 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
3479 THEN
3480 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
3481 THEN
3482 raise FTE_FREIGHT_PRICING_UTIL.g_quicksort_compare_fail;
3483 END IF;
3484 END IF;
3485
3486
3487 IF(l_result = '=')
3488 THEN
3489 --Equality this rank entry is a duplicate,mark for removal
3490
3491 l_eliminated_index(l_index):=0;
3492
3493 END IF;
3494
3495 ELSE
3496
3497
3498 l_index:=l_values_tab(l_sorted_index(i)).value(4);
3499
3500 l_eliminated_index(l_index):=1;
3501
3502 END IF;
3503
3504 l_prev_index:=i;
3505 i:=l_sorted_index.NEXT(i);
3506 END LOOP;
3507
3508
3509 --Copy over unique lanes to output
3510
3511 j:=1;
3512 i:=l_eliminated_index.FIRST;
3513 WHILE(i IS NOT NULL)
3514 LOOP
3515
3516 IF(l_eliminated_index(i)=1)
3517 THEN
3518
3519 x_ss_rate_sort_tab(j):=p_ss_rate_sort_tab(i);
3520 x_input_ref(j):=p_input_ref(i);
3521 j:=j+1;
3522
3523 END IF;
3524
3525
3526 i:=l_eliminated_index.NEXT(i);
3527 END LOOP;
3528
3529
3530
3531 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Remove_Duplicate_Lanes');
3532
3533 EXCEPTION
3534
3535 WHEN FTE_FREIGHT_PRICING_UTIL.g_quicksort_compare_fail THEN
3536 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3537 FTE_FREIGHT_PRICING_UTIL.set_exception('Remove_Duplicate_Lanes',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_quicksort_compare_fail');
3538 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Remove_Duplicate_Lanes');
3539
3540
3541 WHEN FTE_FREIGHT_PRICING_UTIL.g_sort_fail THEN
3542 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3543 FTE_FREIGHT_PRICING_UTIL.set_exception('Remove_Duplicate_Lanes',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_sort_fail');
3547 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3544 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Remove_Duplicate_Lanes');
3545
3546 WHEN others THEN
3548 FTE_FREIGHT_PRICING_UTIL.set_exception('Seq_Tender_Sort',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_others');
3549 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
3550 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Seq_Tender_Sort');
3551
3552
3553
3554
3555 END Remove_Duplicate_Lanes;
3556
3557
3558
3559
3560 PROCEDURE Get_MDC_Delivery_Type(
3561 p_delivery_id IN NUMBER,
3562 p_trip_id IN NUMBER,
3563 x_delivery_type OUT NOCOPY NUMBER,
3564 x_return_status OUT NOCOPY VARCHAR2) IS
3565
3566
3567 CURSOR c_delivery_type(c_delivery_id IN Number) IS
3568 SELECT delivery_type
3569 FROM wsh_new_deliveries
3570 WHERE delivery_id=c_delivery_id;
3571
3572 CURSOR c_check_parent_exists(c_delivery_id IN Number,c_trip_id IN NUMBER) IS
3573 select dl.parent_delivery_leg_id
3574 FROM wsh_delivery_legs dl,
3575 wsh_trip_stops s
3576 WHERE dl.delivery_id=c_delivery_id
3577 AND dl.parent_delivery_leg_id is not null
3578 AND dl.pick_up_stop_id = s.stop_id
3579 AND s.trip_id=c_trip_id;
3580
3581
3582 l_delivery_type VARCHAR2(30);
3583 l_parent_dleg_id NUMBER;
3584
3585 l_log_level NUMBER := FTE_FREIGHT_PRICING_UTIL.G_DBG;
3586
3587 l_return_status VARCHAR2(1);
3588 BEGIN
3589
3590
3591 FTE_FREIGHT_PRICING_UTIL.reset_dbg_vars;
3592 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,'Get_MDC_Delivery_Type','start');
3593
3594 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3595
3596
3597
3598 x_delivery_type:=DELIVERY_TYPE_STANDARD;
3599
3600 l_delivery_type:=NULL;
3601 OPEN c_delivery_type(p_delivery_id);
3602 FETCH c_delivery_type into l_delivery_type;
3603 CLOSE c_delivery_type;
3604
3605 IF ((l_delivery_type IS NOT NULL) AND (l_delivery_type='CONSOLIDATION'))
3606 THEN
3607
3608 x_delivery_type:=DELIVERY_TYPE_CONSOL;
3609
3610 ELSE
3611
3612 l_parent_dleg_id:=NULL;
3613
3614 OPEN c_check_parent_exists(p_delivery_id,p_trip_id);
3615 FETCH c_check_parent_exists into l_parent_dleg_id;
3616 CLOSE c_check_parent_exists;
3617
3618
3619 IF(l_parent_dleg_id IS NOT NULL)
3620 THEN
3621 x_delivery_type:=DELIVERY_TYPE_CHILD;
3622
3623 ELSE
3624 x_delivery_type:=DELIVERY_TYPE_STANDARD;
3625 END IF;
3626
3627
3628 END IF;
3629
3630 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Get_MDC_Delivery_Type');
3631
3632 EXCEPTION
3633
3634 WHEN others THEN
3635 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3636 FTE_FREIGHT_PRICING_UTIL.set_exception('Get_MDC_Delivery_Type',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_others');
3637 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
3638 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Get_MDC_Delivery_Type');
3639
3640
3641 END Get_MDC_Delivery_Type;
3642
3643
3644 PROCEDURE Search_Rate_Sort(
3645 p_api_version IN NUMBER DEFAULT 1.0,
3646 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
3647 p_event IN VARCHAR2 DEFAULT 'FTE_TRIP_COMP',
3648 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
3649 p_init_prc_log IN VARCHAR2 DEFAULT 'Y',
3650 p_ss_rate_sort_tab IN FTE_CARRIER_RANK_LIST_PVT.carrier_rank_list_tbl_type,
3651 p_ss_rate_sort_atr_rec IN FTE_SS_ATTR_REC,
3652 x_ss_rate_sort_tab OUT NOCOPY FTE_CARRIER_RANK_LIST_PVT.carrier_rank_list_tbl_type,
3653 x_rating_request_id OUT NOCOPY NUMBER,
3654 x_return_status OUT NOCOPY VARCHAR2,
3655 x_msg_count OUT NOCOPY NUMBER,
3656 x_msg_data OUT NOCOPY VARCHAR2)
3657 IS
3658
3659 i NUMBER;
3660 j NUMBER;
3661 k NUMBER;
3662
3663 l_sort_rank_tab FTE_CARRIER_RANK_LIST_PVT.carrier_rank_list_tbl_type;
3664 l_rate_rank_tab FTE_CARRIER_RANK_LIST_PVT.carrier_rank_list_tbl_type;
3665 l_lane_rank_tab FTE_CARRIER_RANK_LIST_PVT.carrier_rank_list_tbl_type;
3666 l_elim_rank_tab FTE_CARRIER_RANK_LIST_PVT.carrier_rank_list_tbl_type;
3667 l_lane_rank_ref DBMS_UTILITY.NUMBER_ARRAY;
3668 l_rate_rank_ref DBMS_UTILITY.NUMBER_ARRAY;
3669 l_sort_rank_ref DBMS_UTILITY.NUMBER_ARRAY;
3670
3671 l_elim_rank_ref DBMS_UTILITY.NUMBER_ARRAY;
3672
3673 l_lane_sched_id_tab FTE_ID_TAB_TYPE; -- lane_ids or schedule_ids
3674 l_lane_sched_tab FTE_CODE_TAB_TYPE; -- 'L' or 'S' (Lane or Schedule)
3675 l_mode_tab FTE_CODE_TAB_TYPE;
3676 l_service_type_tab FTE_CODE_TAB_TYPE;
3677 l_vehicle_type_tab FTE_ID_TAB_TYPE;
3678
3679 l_out_lane_sched_id_tab FTE_ID_TAB_TYPE; -- lane_ids or schedule_ids
3680 l_out_lane_sched_tab FTE_CODE_TAB_TYPE; -- 'L' or 'S' (Lane or Schedule)
3681 l_out_mode_tab FTE_CODE_TAB_TYPE;
3682 l_out_service_type_tab FTE_CODE_TAB_TYPE;
3683 l_out_vehicle_type_tab FTE_ID_TAB_TYPE;
3684
3685 l_sum_rate_tab FTE_ID_TAB_TYPE;
3686 l_sum_rate_curr_tab FTE_CODE_TAB_TYPE;
3687 l_request_id NUMBER;
3688 l_return_status VARCHAR2(1);
3689 l_rating_API_fail VARCHAR2(1);
3690 l_log_level NUMBER := FTE_FREIGHT_PRICING_UTIL.G_DBG;
3691
3692 l_api_version CONSTANT NUMBER := 1.0;
3693 l_api_name CONSTANT VARCHAR2(30) := 'Search_Rate_Sort';
3694 l_msg_count NUMBER;
3695 l_msg_data VARCHAR2(32767);
3696
3697 l_laneSched_count NUMBER;
3698 l_single_currency VARCHAR2(30);
3699 l_single_curr_rate NUMBER;
3700 l_warning_count NUMBER:=0;
3701
3702 l_delivery_type NUMBER;
3703
3704 BEGIN
3705
3709 (
3706
3707 -- Standard call to check for call compatibility.
3708 IF NOT FND_API.Compatible_API_Call
3710 l_api_version,
3711 p_api_version,
3712 l_api_name,
3713 G_PKG_NAME
3714 )
3715 THEN
3716 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3717 END IF;
3718
3719 -- Initialize message list if p_init_msg_list is set to TRUE.
3720 IF FND_API.to_Boolean( p_init_msg_list )
3721 THEN
3722 FND_MSG_PUB.initialize;
3723 END IF;
3724
3725 IF(p_init_prc_log='Y')
3726 THEN
3727
3728 FTE_FREIGHT_PRICING_UTIL.initialize_logging( p_init_msg_list => p_init_msg_list,
3729 x_return_status => l_return_status );
3730
3731 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3732 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
3733 x_return_status := l_return_status;
3734 RETURN;
3735 END IF;
3736 ELSE
3737 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Initialize Logging successful ');
3738 END IF;
3739 END IF;
3740
3741
3742 FTE_FREIGHT_PRICING_UTIL.reset_dbg_vars;
3743 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,'Search_Rate_Sort','start');
3744
3745 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3746
3747
3748 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Input ranks');
3749 Display_Rank_Tab(p_rank_tab=>p_ss_rate_sort_tab,
3750 p_input_ref=>l_lane_rank_ref);
3751
3752 Display_Attr_Rec(p_attr_rec=>p_ss_rate_sort_atr_rec);
3753
3754
3755 --Lane Search
3756
3757 Search_Multi_ShipMethods(
3758 p_ss_attr_rec=>p_ss_rate_sort_atr_rec,
3759 p_ss_rate_sort_tab=>p_ss_rate_sort_tab,
3760 x_ss_rate_sort_tab=>l_lane_rank_tab,
3761 x_ref=>l_lane_rank_ref,
3762 x_return_status=>l_return_status);
3763
3764 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
3765 THEN
3766 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
3767 THEN
3768 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Search_Multi_ShipMethods FAILED');
3769 raise FTE_FREIGHT_PRICING_UTIL.g_unexp_err;
3770 ELSE
3771 l_warning_count:=l_warning_count+1;
3772 END IF;
3773 END IF;
3774
3775
3776 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Ranks after lane search');
3777 Display_Rank_Tab(p_rank_tab=>l_lane_rank_tab,
3778 p_input_ref=>l_lane_rank_ref);
3779
3780
3781
3782
3783
3784 IF (l_lane_rank_tab.COUNT > 0)
3785 THEN
3786
3787
3788 Remove_Duplicate_Lanes(
3789 p_ss_rate_sort_tab=>l_lane_rank_tab,
3790 p_input_ref=>l_lane_rank_ref,
3791 x_ss_rate_sort_tab=>l_elim_rank_tab,
3792 x_input_ref=>l_elim_rank_ref,
3793 x_return_status=>l_return_status);
3794
3795 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
3796 THEN
3797 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
3798 THEN
3799 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'g_elim_dup_rank_fail');
3800 raise FTE_FREIGHT_PRICING_UTIL.g_unexp_err;
3801 END IF;
3802 END IF;
3803
3804
3805 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Ranks after Eliminating duplicates');
3806 Display_Rank_Tab(p_rank_tab=>l_elim_rank_tab,
3807 p_input_ref=>l_elim_rank_ref);
3808
3809
3810 l_lane_rank_tab.DELETE;
3811 l_lane_rank_ref.DELETE;
3812
3813
3814 --Prepare inputs for rating
3815
3816 l_lane_sched_id_tab:=FTE_ID_TAB_TYPE();
3817 l_lane_sched_tab:=FTE_CODE_TAB_TYPE();
3818 l_mode_tab:=FTE_CODE_TAB_TYPE();
3819 l_service_type_tab:=FTE_CODE_TAB_TYPE();
3820 l_vehicle_type_tab:=FTE_ID_TAB_TYPE();
3821
3822 l_laneSched_count:=l_elim_rank_tab.COUNT;
3823
3824 l_lane_sched_id_tab.EXTEND(l_laneSched_count);
3825 l_lane_sched_tab.EXTEND(l_laneSched_count);
3826 l_mode_tab.EXTEND(l_laneSched_count);
3827 l_service_type_tab.EXTEND(l_laneSched_count);
3828 l_vehicle_type_tab.EXTEND(l_laneSched_count);
3829
3830
3831 j:=1;
3832 i:=l_elim_rank_tab.FIRST;
3833 WHILE(i IS NOT NULL)
3834 LOOP
3835 --Always clear old rates in rank rec
3836 l_elim_rank_tab(i).estimated_rate:=NULL;
3837 l_elim_rank_tab(i).currency_code:=NULL;
3838 l_elim_rank_tab(i).single_curr_rate:=NULL;
3839
3840 IF(l_elim_rank_tab(i).schedule_id IS NOT NULL)
3841 THEN
3842 l_lane_sched_id_tab(j):=l_elim_rank_tab(i).schedule_id;
3843 l_lane_sched_tab(j):='S';
3844 ELSE
3845
3846 l_lane_sched_id_tab(j):=l_elim_rank_tab(i).lane_id;
3847 l_lane_sched_tab(j):='L';
3848
3849
3850 END IF;
3851 l_mode_tab(j):=l_elim_rank_tab(i).mode_of_transport;
3852 l_service_type_tab(j):=l_elim_rank_tab(i).service_level;
3853
3854
3855
3856 l_vehicle_type_tab(j):=NULL;
3857
3858 IF (l_elim_rank_tab(i).vehicle_item_id IS NOT NULL)
3859 THEN
3860
3861 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Calling vehicle API inventory item:'||l_elim_rank_tab(i).vehicle_item_id);
3862 l_vehicle_type_tab(j):=FTE_VEHICLE_PKG.GET_VEHICLE_TYPE_ID(
3863 p_inventory_item_id=> l_elim_rank_tab(i).vehicle_item_id);
3864
3865 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Vehicle API returned:'||l_vehicle_type_tab(j));
3866
3867 IF(l_vehicle_type_tab(j)=-1)
3868 THEN
3869
3870 l_vehicle_type_tab(j):=NULL;
3871
3872 END IF;
3873
3874
3875
3876 END IF;
3877
3878 j:=j+1;
3879 i:=l_elim_rank_tab.NEXT(i);
3880 END LOOP;
3881
3882 --Rating
3886 IF ((p_ss_rate_sort_atr_rec.delivery_id IS NOT NULL) AND (p_ss_rate_sort_atr_rec.trip_id IS NOT NULL))
3883
3884 l_delivery_type:= NULL;
3885
3887 THEN
3888
3889 Get_MDC_Delivery_Type(
3890 p_delivery_id=>p_ss_rate_sort_atr_rec.delivery_id,
3891 p_trip_id=>p_ss_rate_sort_atr_rec.trip_id,
3892 x_delivery_type=>l_delivery_type,
3893 x_return_status=>l_return_status);
3894
3895 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
3896 THEN
3897 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
3898 THEN
3899 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Get_MDC_Delivery_Type FAILED');
3900 raise FTE_FREIGHT_PRICING_UTIL.g_unexp_err;
3901 END IF;
3902 END IF;
3903
3904 IF(l_delivery_type=DELIVERY_TYPE_CHILD)
3905 THEN
3906 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Attempt to rate a child delivery..error');
3907 raise FTE_FREIGHT_PRICING_UTIL.g_unexp_err;
3908
3909 END IF;
3910
3911
3912
3913 END IF;
3914
3915 l_rating_API_fail:='N';
3916
3917 IF(p_ss_rate_sort_atr_rec.trip_id IS NOT NULL)
3918 THEN
3919 --Trip Centric
3920
3921 FTE_TRIP_RATING_GRP.Compare_Trip_Rates (
3922 p_api_version =>1.0,
3923 p_init_msg_list =>FND_API.G_FALSE,
3924 p_init_prc_log =>'N',
3925 p_trip_id =>p_ss_rate_sort_atr_rec.trip_id,
3926 p_lane_sched_id_tab =>l_lane_sched_id_tab,
3927 p_lane_sched_tab =>l_lane_sched_tab,
3928 p_mode_tab =>l_mode_tab,
3929 p_service_type_tab =>l_service_type_tab,
3930 p_vehicle_type_tab =>l_vehicle_type_tab,
3931 p_dep_date =>p_ss_rate_sort_atr_rec.dep_date_from,
3932 p_arr_date =>p_ss_rate_sort_atr_rec.arr_date_to,
3933 x_lane_sched_id_tab =>l_out_lane_sched_id_tab,
3934 x_lane_sched_tab =>l_out_lane_sched_tab,
3935 x_mode_tab =>l_out_mode_tab,
3936 x_service_type_tab =>l_out_service_type_tab,
3937 x_vehicle_type_tab =>l_out_vehicle_type_tab,
3938 x_sum_rate_tab =>l_sum_rate_tab,
3939 x_sum_rate_curr_tab =>l_sum_rate_curr_tab,
3940 x_request_id =>x_rating_request_id,
3941 x_msg_count =>l_msg_count,
3942 x_msg_data =>l_msg_data,
3943 x_return_status =>l_return_status) ;
3944
3945 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Compare_Trip_Rates status:'||l_return_status);
3946 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
3947 THEN
3948 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
3949 THEN
3950 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
3951 THEN
3952
3953 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Compare_Trip_Rates FAILED');
3954 --Convert E to W as WB did in 10+
3955 --ServicePriceCompareVOImpl.java 115.29.11510.1
3956 --raise FTE_FREIGHT_PRICING_UTIL.g_unexp_err;
3957 l_warning_count:=l_warning_count+1;
3958 l_rating_API_fail:='Y';
3959 ELSE
3960 --If there is an unexpeted error it is propogated
3961 raise FTE_FREIGHT_PRICING_UTIL.g_unexp_err;
3962
3963 END IF;
3964 ELSE
3965 l_warning_count:=l_warning_count+1;
3966
3967 END IF;
3968 END IF;
3969
3970
3971
3972 ELSIF (((l_delivery_type IS NULL) OR (l_delivery_type <> DELIVERY_TYPE_CONSOL)) AND ((p_ss_rate_sort_atr_rec.delivery_id IS NOT NULL) OR (p_ss_rate_sort_atr_rec.delivery_leg_id IS NOT NULL)))
3973 THEN
3974 --Delivery/Leg Centric
3975
3976
3977 FTE_FREIGHT_PRICING.shipment_price_compare (
3978 p_init_msg_list =>fnd_api.g_false,
3979 p_init_prc_log =>'N',
3980 p_delivery_id =>p_ss_rate_sort_atr_rec.delivery_id,
3981 p_trip_id =>p_ss_rate_sort_atr_rec.trip_id,
3982 p_lane_sched_id_tab =>l_lane_sched_id_tab,
3983 p_lane_sched_tab =>l_lane_sched_tab,
3984 p_mode_tab =>l_mode_tab,
3985 p_service_type_tab =>l_service_type_tab,
3986 p_vehicle_type_tab =>l_vehicle_type_tab,
3987 p_dep_date =>p_ss_rate_sort_atr_rec.dep_date_from,
3988 p_arr_date =>p_ss_rate_sort_atr_rec.arr_date_to,
3989 p_pickup_location_id =>p_ss_rate_sort_atr_rec.origin_location_id,
3990 p_dropoff_location_id =>p_ss_rate_sort_atr_rec.destination_location_id,
3991 x_lane_sched_id_tab =>l_out_lane_sched_id_tab,
3992 x_lane_sched_tab =>l_out_lane_sched_tab,
3993 x_mode_tab =>l_out_mode_tab,
3994 x_service_type_tab =>l_out_service_type_tab,
3995 x_vehicle_type_tab =>l_out_vehicle_type_tab,
3996 x_sum_rate_tab =>l_sum_rate_tab,
3997 x_sum_rate_curr_tab =>l_sum_rate_curr_tab,
3998 x_request_id =>x_rating_request_id,
3999 x_return_status =>l_return_status) ;
4000
4001 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'shipment_price_compare status:'||l_return_status);
4002
4003 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
4004 THEN
4005 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
4006 THEN
4007 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
4008 THEN
4009 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'shipment_price_compare FAILED');
4010 --Convert E to W as WB did in 10+
4011 --ServicePriceCompareVOImpl.java 115.29.11510.1
4012 --raise FTE_FREIGHT_PRICING_UTIL.g_unexp_err;
4013 l_warning_count:=l_warning_count+1;
4014 l_rating_API_fail:='Y';
4015 ELSE
4016 --If there is an unexpeted error it is propogated
4017 raise FTE_FREIGHT_PRICING_UTIL.g_unexp_err;
4018
4019 END IF;
4020
4021 ELSE
4022 l_warning_count:=l_warning_count+1;
4026
4023 END IF;
4024 END IF;
4025
4027 END IF;
4028
4029 IF(l_rating_API_fail='Y')
4030 THEN
4031 l_rate_rank_tab:=l_elim_rank_tab;
4032 l_rate_rank_ref:=l_elim_rank_ref;
4033
4034 ELSE
4035
4036 --Populate rating output into the rank structures
4037
4038 k:=1;
4039 j:=l_elim_rank_tab.FIRST;
4040 i:=l_out_lane_sched_id_tab.FIRST;
4041 l_single_currency:=NULL;
4042 WHILE(i IS NOT NULL)
4043 LOOP
4044
4045 --If the lane id of the rank tab is the same as the output lane id it could be the case that a
4046 --TL lane with multiple vehicles.
4047 --Only increment the lane index if we are through with all the outputs which have the same lane id
4048 IF((j IS NOT NULL) AND (l_elim_rank_tab(j).mode_of_transport='TRUCK') AND (((l_out_lane_sched_tab(i)='L') AND (l_out_lane_sched_id_tab(i)<>l_elim_rank_tab(j).lane_id))
4049 OR((l_out_lane_sched_tab(i)='S') AND (l_out_lane_sched_id_tab(i)<>l_elim_rank_tab(j).schedule_id))))
4050 THEN
4051 j:=l_elim_rank_tab.NEXT(j);
4052
4053 END IF;
4054
4055
4056
4057 IF((j IS NOT NULL ) AND (((l_out_lane_sched_tab(i)='L') AND (l_out_lane_sched_id_tab(i)=l_elim_rank_tab(j).lane_id))
4058 OR((l_out_lane_sched_tab(i)='S') AND (l_out_lane_sched_id_tab(i)=l_elim_rank_tab(j).schedule_id))))
4059 THEN
4060
4061 l_rate_rank_tab(k):=l_elim_rank_tab(j);
4062 IF(l_out_vehicle_type_tab(i) IS NOT NULL)
4063 THEN
4064
4065 Get_Vehicle_Item_Org(
4066 p_vehicle_type=>l_out_vehicle_type_tab(i),
4067 x_vehicle_item_id=>l_rate_rank_tab(k).vehicle_item_id,
4068 x_vehicle_item_org=>l_rate_rank_tab(k).vehicle_org_id,
4069 x_return_status=>l_return_status);
4070
4071
4072 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
4073 THEN
4074 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
4075 THEN
4076 raise FTE_FREIGHT_PRICING_UTIL.g_get_veh_item_org_fail;
4077 END IF;
4078 END IF;
4079
4080 END IF;
4081
4082 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'DBG-1');
4083
4084 IF ((l_sum_rate_tab(i) IS NOT NULL) AND (l_sum_rate_curr_tab(i) IS NOT NULL)
4085 AND (l_sum_rate_tab(i)> 0) AND (l_sum_rate_curr_tab(i)<>'NULL'))
4086 THEN
4087
4088 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'DBG-2');
4089 l_rate_rank_tab(k).estimated_rate:=l_sum_rate_tab(i);
4090 l_rate_rank_tab(k).currency_code:=l_sum_rate_curr_tab(i);
4091
4092
4093 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'DBG-3');
4094 IF (l_single_currency IS NULL)
4095 THEN
4096 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'DBG-4');
4097 l_single_currency:=l_rate_rank_tab(k).currency_code;
4098 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'DBG-5');
4099 END IF;
4100
4101 IF(l_single_currency = l_rate_rank_tab(k).currency_code)
4102 THEN
4103 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'DBG-6');
4104 l_single_curr_rate:=l_rate_rank_tab(k).estimated_rate;
4105
4106 ELSE
4107 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'DBG-7');
4108 l_single_curr_rate:=NULL;
4109 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'DBG-7.1');
4110 l_single_curr_rate:=GL_CURRENCY_API.convert_amount(
4111 l_rate_rank_tab(k).currency_code,
4112 l_single_currency,
4113 SYSDATE,
4114 'Corporate',
4115 l_rate_rank_tab(k).estimated_rate
4116 );
4117 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'DBG-7.2');
4118
4119 IF (l_single_curr_rate IS NULL)
4120 THEN
4121 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'g_tl_conv_currency_fail');
4122 raise FTE_FREIGHT_PRICING_UTIL.g_unexp_err;
4123
4124 END IF;
4125
4126 END IF;
4127 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'DBG-8');
4128 l_rate_rank_tab(k).single_curr_rate:=l_single_curr_rate;
4129 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'DBG-9');
4130
4131 END IF;
4132 --Mantain reference to original rank input
4133 l_rate_rank_ref(k):=l_elim_rank_ref(j);
4134
4135 k:=k+1;
4136
4137
4138 END IF;
4139
4140 --Increment lane_rank_tab index only if mode is not truck
4141 -- If the mode is truck the same lane may have multiple vehicles
4142 --In which case the increment is done only if the lane is different
4143 IF((j IS NOT NULL) AND (l_elim_rank_tab(j).mode_of_transport<>'TRUCK'))
4144 THEN
4145 j:=l_elim_rank_tab.NEXT(j);
4146 END IF;
4147
4148 i:=l_out_lane_sched_id_tab.NEXT(i);
4149 END LOOP;
4150
4151 END IF;
4152
4153 l_elim_rank_tab.DELETE;
4154 l_elim_rank_ref.DELETE;
4155
4156
4157
4158 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Ranks after Rating');
4159 Display_Rank_Tab(p_rank_tab=>l_rate_rank_tab,
4160 p_input_ref=>l_rate_rank_ref);
4161
4162
4163 Seq_Tender_Sort(
4164 p_ss_rate_sort_tab=>l_rate_rank_tab,
4165 p_input_ref=>l_rate_rank_ref,
4166 x_ss_rate_sort_tab=>l_sort_rank_tab,
4167 x_input_ref=>l_sort_rank_ref,
4168 x_return_status=>l_return_status);
4169
4170 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
4171 THEN
4172 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
4173 THEN
4174 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'g_seq_tender_sort_fail');
4175 raise FTE_FREIGHT_PRICING_UTIL.g_unexp_err;
4176 END IF;
4177 END IF;
4178
4179 l_rate_rank_tab.DELETE;
4180 l_rate_rank_ref.DELETE;
4181
4182 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Ranks after Sorting');
4183 Display_Rank_Tab(p_rank_tab=>l_sort_rank_tab,
4184 p_input_ref=>l_sort_rank_ref);
4185
4186
4187 Remove_Duplicate_Lanes(
4188 p_ss_rate_sort_tab=>l_sort_rank_tab,
4189 p_input_ref=>l_sort_rank_ref,
4190 x_ss_rate_sort_tab=>x_ss_rate_sort_tab,
4191 x_input_ref=>l_elim_rank_ref,
4192 x_return_status=>l_return_status);
4193
4194 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
4195 THEN
4196 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
4197 THEN
4198 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'g_elim_dup_rank_fail');
4199 raise FTE_FREIGHT_PRICING_UTIL.g_unexp_err;
4200 END IF;
4201 END IF;
4202
4203
4204 l_sort_rank_tab.DELETE;
4205 l_sort_rank_ref.DELETE;
4206
4207
4208 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Ranks after Eliminating duplicates');
4209 Display_Rank_Tab(p_rank_tab=>x_ss_rate_sort_tab,
4210 p_input_ref=>l_elim_rank_ref);
4211
4212 ELSE
4213 --Lane search returned no results
4214 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'No lanes from lane search');
4215 x_rating_request_id:=NULL;
4216 x_ss_rate_sort_tab:=p_ss_rate_sort_tab;
4217
4218 END IF;
4219
4220 IF (l_warning_count > 0)
4221 THEN
4222 x_return_status:=WSH_UTIL_CORE.G_RET_STS_WARNING;
4223 END IF;
4224
4225 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Search_Rate_Sort return status:'||x_return_status);
4226
4227 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Search_Rate_Sort');
4228
4229 IF(p_init_prc_log='Y')
4230 THEN
4231 FTE_FREIGHT_PRICING_UTIL.close_logs;
4232 END IF;
4233
4234
4235
4236 EXCEPTION
4237
4238 WHEN others THEN
4239 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4240 FTE_FREIGHT_PRICING_UTIL.set_exit_exception('Search_Rate_Sort','g_others');
4241 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
4242 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Search_Rate_Sort');
4243 IF(p_init_prc_log='Y')
4244 THEN
4245 FTE_FREIGHT_PRICING_UTIL.close_logs;
4246 END IF;
4247
4248
4249 END Search_Rate_Sort;
4250
4251
4252
4253 END FTE_TRIP_RATING_GRP;