[Home] [Help]
PACKAGE BODY: APPS.FTE_TL_RATING
Source
1 PACKAGE BODY FTE_TL_RATING AS
2 /* $Header: FTEVTLRB.pls 120.14 2007/11/30 06:03:34 sankarun ship $ */
3
4
5
6 --Works only for a 2-stop trip
7
8 PROCEDURE Update_Distance_To_Next_Stop(
9 p_trip_id IN NUMBER,
10 x_return_status OUT NOCOPY VARCHAR2)
11 IS
12 CURSOR get_trip_stops(c_trip_id IN NUMBER) IS
13 SELECT s.stop_id
14 FROM WSH_TRIP_STOPS s
15 WHERE s.trip_id=c_trip_id
16 ORDER BY s.stop_sequence_number;
17
18
19 l_distances dbms_utility.number_array;
20 l_stop_ids dbms_utility.number_array;
21 l_distance_uom VARCHAR2(30);
22 l_weight NUMBER;
23 l_weight_uom VARCHAR2(30);
24
25 i NUMBER;
26 l_return_status VARCHAR2(1);
27
28 l_log_level NUMBER := FTE_FREIGHT_PRICING_UTIL.G_DBG;
29
30 l_warning_count NUMBER:=0;
31 BEGIN
32
33
34 FTE_FREIGHT_PRICING_UTIL.reset_dbg_vars;
35 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,'Update_Distance_To_Next_Stop','start');
36
37 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
38
39 l_distances(1):=NULL;
40 l_distance_uom:=NULL;
41
42 FTE_TL_CACHE.FPA_Get_Trip_Info(
43 p_trip_id=>p_trip_id,
44 x_distance=>l_distances(1),
45 x_distance_uom=>l_distance_uom,
46 x_weight=>l_weight,
47 x_weight_uom=>l_weight_uom,
48 x_return_status=>l_return_status);
49
50 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
51 THEN
52 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
53 THEN
54
55 raise FTE_FREIGHT_PRICING_UTIL.g_tl_fpa_get_trip_inf_fail;
56 END IF;
57 END IF;
58
59 i:=1;
60 OPEN get_trip_stops(p_trip_id);
61 FETCH get_trip_stops INTO l_stop_ids(i);
62 WHILE (get_trip_stops%FOUND)
63 LOOP
64
65 i:=i+1;
66 FETCH get_trip_stops INTO l_stop_ids(i);
67 END LOOP;
68 CLOSE get_trip_stops;
69
70 IF (i <=3)
71 THEN
72
73 IF( (l_distances(1) IS NOT NULL ) AND (l_distance_uom IS NOT NULL))
74 THEN
75 l_distances(2):=0;
76
77 FORALL i IN l_distances.FIRST..l_distances.LAST
78 UPDATE wsh_trip_stops
79 SET distance_to_next_stop = l_distances(i),
80 distance_uom = l_distance_uom
81 WHERE stop_id = l_stop_ids(i);
82
83 END IF;
84 ELSE
85
86 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Update_Distance_To_Next_Stop:'||p_trip_id||' has more than 2 stops');
87
88 END IF;
89
90
91
92 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Update_Distance_To_Next_Stop');
93
94 IF (l_warning_count > 0)
95 THEN
96 x_return_status:=WSH_UTIL_CORE.G_RET_STS_WARNING;
97 END IF;
98
99
100 EXCEPTION
101
102 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_fpa_get_trip_inf_fail THEN
103 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
104 FTE_FREIGHT_PRICING_UTIL.set_exception('Update_Distance_To_Next_Stop',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_fpa_get_trip_inf_fail');
105 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Update_Distance_To_Next_Stop');
106
107
108 WHEN others THEN
109 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
110 FTE_FREIGHT_PRICING_UTIL.set_exception('Update_Distance_To_Next_Stop',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_others');
111 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
112 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Update_Distance_To_Next_Stop');
113
114
115 END Update_Distance_To_Next_Stop;
116
117
118
119 PROCEDURE Get_Vehicles_For_LaneSchedules(
120 p_trip_id IN NUMBER DEFAULT NULL,
121 p_lane_rows IN dbms_utility.number_array,
122 p_schedule_rows IN dbms_utility.number_array,
123 p_vehicle_rows IN dbms_utility.number_array,
124 x_vehicle_rows OUT NOCOPY dbms_utility.number_array,
125 x_lane_rows OUT NOCOPY dbms_utility.number_array,
126 x_schedule_rows OUT NOCOPY dbms_utility.number_array,
127 x_ref_rows OUT NOCOPY dbms_utility.number_array,
128 x_return_status OUT NOCOPY Varchar2) IS
129
130
131
132
133 --Gets the default vehicle for the lane through the carrier service level.
134 CURSOR get_lane_def_vehicle (c_lane_id IN NUMBER) IS
135 SELECT c.default_vehicle_type_id
136 FROM WSH_CARRIER_SERVICES c ,
137 FTE_LANES l
138 WHERE c.carrier_id=l.carrier_id and
139 c.service_level=l.service_type_code and
140 l.lane_id=c_lane_id;
141
142 --Gets the default vehicle for the schedule through the carrier service level.
143 CURSOR get_schedule_def_vehicle(c_schedule_id IN NUMBER) IS
144 SELECT c.default_vehicle_type_id
145 FROM WSH_CARRIER_SERVICES c ,
146 FTE_LANES l,
147 FTE_SCHEDULES s
148 WHERE c.carrier_id=l.carrier_id and
149 c.service_level=l.service_type_code and
150 s.schedules_id=c_schedule_id and
151 s.lane_id=l.lane_id;
152
153 --Gets all the vehicles for the carrier of the lane.
154
155 CURSOR get_lane_carrier_vehicles(c_lane_id IN NUMBER) IS
156 SELECT wcvt.vehicle_type_id,
157 l.carrier_id
158 FROM wsh_carrier_vehicle_types wcvt,
159 fte_lanes l
160 WHERE l.lane_id=c_lane_id
161 and l.carrier_id=wcvt.carrier_id
162 and wcvt.assigned_flag='Y';
163
164
165 --Gets all the vehicles for the carrier of the schedule.
166 CURSOR get_schedule_carrier_vehicles(c_schedule_id IN NUMBER) IS
167 SELECT wcvt.vehicle_type_id,
168 l.carrier_id
169 FROM wsh_carrier_vehicle_types wcvt,
170 fte_lanes l ,
171 fte_schedules s
172 WHERE s.schedules_id=c_schedule_id
173 and l.lane_id=s.lane_id
174 and l.carrier_id=wcvt.carrier_id
175 and wcvt.assigned_flag='Y';
176
177 l_trip_vehicle_type NUMBER;
178 l_carrier_id NUMBER;
179 l_vehicle_type NUMBER;
180 l_vehicle_index NUMBER;
181 l_default_vehicle NUMBER;
182 l_index NUMBER;
183 l_carrier_hash dbms_utility.number_array;--indexed by carrier id has a pointer to l_vehicle_list
184 l_vehicle_list dbms_utility.number_array;
185
186 l_invalid_trip_vehicle dbms_utility.number_array;
187
188 i NUMBER;
189 j NUMBER;
190 l_return_status VARCHAR2(1);
191
192 l_log_level NUMBER := FTE_FREIGHT_PRICING_UTIL.G_DBG;
193
194 l_warning_count NUMBER:=0;
195 BEGIN
196
197
198 FTE_FREIGHT_PRICING_UTIL.reset_dbg_vars;
199 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,'Get_Vehicles_For_LaneSchedules','start');
200
201 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
202
203
204 l_trip_vehicle_type:=NULL;
205
206 IF (p_trip_id IS NOT NULL)
207 THEN
208
209 FTE_TL_CACHE.Get_Vehicle_Type(p_trip_id => p_trip_id,
210 p_vehicle_item_id =>NULL,
211 x_vehicle_type => l_trip_vehicle_type,
212 x_return_status => l_return_status);
213
214 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
215 THEN
216 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
217 THEN
218
219 raise FTE_FREIGHT_PRICING_UTIL.g_tl_get_vehicle_type_fail;
220 END IF;
221 END IF;
222 END IF;
223
224 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Trip:'||p_trip_id||' Trip Vehicle:'||l_trip_vehicle_type);
225
226 i:=p_lane_rows.FIRST;
227 j:=i;
228 WHILE ((i IS NOT NULL) AND((p_lane_rows(i) IS NOT NULL) OR (p_schedule_rows(i) IS NOT NULL)) )
229 LOOP
230
231 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Inputs Lane:'||p_lane_rows(i)||' Schedule:'||p_schedule_rows(i)||' Vehicle:'||p_vehicle_rows(i)||' Index:'||i);
232 l_carrier_id:=NULL;
233 IF (l_trip_vehicle_type IS NOT NULL)
234 THEN
235 --Need to check if trip vehicle is valid for the lane,assume it is not at start
236 l_invalid_trip_vehicle(i):=1;
237 ELSE
238 --No trip vehicle no need to check
239
240 l_invalid_trip_vehicle(i):=0;
241 END IF;
242
243 IF (p_vehicle_rows.EXISTS(i) AND p_vehicle_rows(i) IS NOT NULL)
244 THEN
245 x_lane_rows(j):=p_lane_rows(i);
246 x_schedule_rows(j):=p_schedule_rows(i);
247 x_vehicle_rows(j):=p_vehicle_rows(i);
248 x_ref_rows(j):=i;
249 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Ouput with passed in vehicle Lane:'||x_lane_rows(j)||' Schedule:'||x_schedule_rows(j)||' Vehicle:'||x_vehicle_rows(j)||' Index:'||j);
250 j:=j+1;
251 ELSE
252 --Fetch all vehicle for the lane
253 l_vehicle_type:=NULL;
254 l_carrier_id:=NULL;
255 IF(p_lane_rows(i) IS NOT NULL)
256 THEN
257 OPEN get_lane_carrier_vehicles(p_lane_rows(i));
258 FETCH get_lane_carrier_vehicles INTO l_vehicle_type,l_carrier_id;
259 IF((l_vehicle_type IS NOT NULL) AND (l_carrier_id IS NOT NULL) AND(NOT l_carrier_hash.EXISTS(l_carrier_id)))
260 THEN
261 l_vehicle_index:=l_vehicle_list.LAST;
262 IF (l_vehicle_index IS NULL)
263 THEN
264 l_vehicle_index:=0;
265
266 END IF;
267 l_vehicle_index:=l_vehicle_index+1;
268
269 l_carrier_hash(l_carrier_id):=l_vehicle_index;
270 WHILE(get_lane_carrier_vehicles%FOUND)
271 LOOP
272 l_vehicle_list(l_vehicle_index):=l_vehicle_type;
273 l_vehicle_index:=l_vehicle_index+1;
274
275 --Check if the trip vehicle is valid
276 IF ((l_invalid_trip_vehicle(i)=1) AND (l_trip_vehicle_type IS NOT NULL) AND (l_trip_vehicle_type=l_vehicle_type))
277 THEN
278 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Trip vehicle:'||l_trip_vehicle_type||' Valid for lane:'||p_lane_rows(i));
279 l_invalid_trip_vehicle(i):=0;
280 END IF;
281
282 FETCH get_lane_carrier_vehicles INTO l_vehicle_type,l_carrier_id;
283 END LOOP;
284 --End of vehicle list for a carrier
285 l_vehicle_list(l_vehicle_index):=NULL;
286
287
288
289
290 END IF;
291 CLOSE get_lane_carrier_vehicles;
292
293 l_default_vehicle:=NULL;
294 OPEN get_lane_def_vehicle(p_lane_rows(i));
295 FETCH get_lane_def_vehicle INTO l_default_vehicle;
296 CLOSE get_lane_def_vehicle;
297
298 ELSE
299 OPEN get_schedule_carrier_vehicles(p_lane_rows(i));
300 FETCH get_schedule_carrier_vehicles INTO l_vehicle_type,l_carrier_id;
301 IF((l_vehicle_type IS NOT NULL) AND (l_carrier_id IS NOT NULL) AND(NOT l_carrier_hash.EXISTS(l_carrier_id)))
302 THEN
303 l_vehicle_index:=l_vehicle_list.LAST;
304 IF (l_vehicle_index IS NULL)
305 THEN
306 l_vehicle_index:=0;
307
308 END IF;
309 l_vehicle_index:=l_vehicle_index+1;
310
311 l_carrier_hash(l_carrier_id):=l_vehicle_index;
312 WHILE(get_schedule_carrier_vehicles%FOUND)
313 LOOP
314 l_vehicle_list(l_vehicle_index):=l_vehicle_type;
315 l_vehicle_index:=l_vehicle_index+1;
316
317 --Check if the trip vehicle is valid
318 IF ((l_invalid_trip_vehicle(i)=1) AND (l_trip_vehicle_type IS NOT NULL) AND (l_trip_vehicle_type=l_vehicle_type))
319 THEN
320 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Trip vehicle:'||l_trip_vehicle_type||' Valid for Schedule:'||p_schedule_rows(i));
321 l_invalid_trip_vehicle(i):=0;
322 END IF;
323
324
325 FETCH get_schedule_carrier_vehicles INTO l_vehicle_type,l_carrier_id;
326 END LOOP;
327 --End of vehicle list for a carrier
328 l_vehicle_list(l_vehicle_index):=NULL;
329
330 END IF;
331 CLOSE get_schedule_carrier_vehicles;
332
333 l_default_vehicle:=NULL;
334 OPEN get_schedule_def_vehicle(p_schedule_rows(i));
335 FETCH get_schedule_def_vehicle INTO l_default_vehicle;
336 CLOSE get_schedule_def_vehicle;
337
338 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Default vehicle:'||l_default_vehicle||' for schedule:'||p_schedule_rows(i));
339
340 END IF;
341
342 --Available vehicles for the lane/schedule as well as default is fetched at this point
343
344 IF (l_trip_vehicle_type IS NOT NULL)
345 THEN
346
347
348 IF((l_carrier_id IS NOT NULL) AND (l_carrier_hash.EXISTS(l_carrier_id)) AND (l_invalid_trip_vehicle(i)=1))
349 THEN
350
351 l_index:=l_carrier_hash(l_carrier_id);
352 IF(l_index IS NOT NULL)
353 THEN
354 WHILE((l_vehicle_list.EXISTS(l_index))
355 AND(l_vehicle_list(l_index) IS NOT NULL)
356 AND (l_invalid_trip_vehicle(i)=1))
357 LOOP
358 IF (l_vehicle_list(l_index)=l_trip_vehicle_type)
359 THEN
360 l_invalid_trip_vehicle(i):=0;
361
362 END IF;
363
364 l_index:=l_index+1;
365 END LOOP;
366 END IF;
367
368
369 END IF;
370
371 IF(l_invalid_trip_vehicle(i)=1)
372 THEN
373 x_vehicle_rows(j):=NULL;
374 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Invalid Vehicle');
375
376 ELSE
377 x_vehicle_rows(j):=l_trip_vehicle_type;
378
379 END IF;
380
381 x_lane_rows(j):=p_lane_rows(i);
382 x_schedule_rows(j):=p_schedule_rows(i);
383 x_ref_rows(j):=i;
384 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Ouput with trip vehicle Lane:'||x_lane_rows(j)||' Schedule:'||x_schedule_rows(j)||' Vehicle:'||x_vehicle_rows(j)||' Index:'||j);
385 j:=j+1;
386
387
388 ELSIF(l_default_vehicle IS NOT NULL)
389 THEN
390 x_lane_rows(j):=p_lane_rows(i);
391 x_schedule_rows(j):=p_schedule_rows(i);
392 x_vehicle_rows(j):=l_default_vehicle;
393 x_ref_rows(j):=i;
394 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Ouput with default vehicle Lane:'||x_lane_rows(j)||' Schedule:'||x_schedule_rows(j)||' Vehicle:'||x_vehicle_rows(j)||' Index:'||j);
395 j:=j+1;
396
397 ELSIF(l_carrier_id IS NOT NULL)
398 THEN
399 IF (l_carrier_hash.EXISTS(l_carrier_id))
400 THEN
401 l_index:=l_carrier_hash(l_carrier_id);
402 IF(l_index IS NOT NULL)
403 THEN
404 WHILE((l_vehicle_list.EXISTS(l_index))
405 AND(l_vehicle_list(l_index) IS NOT NULL))
406 LOOP
407 x_lane_rows(j):=p_lane_rows(i);
408 x_schedule_rows(j):=p_schedule_rows(i);
409 x_vehicle_rows(j):=l_vehicle_list(l_index);
410 x_ref_rows(j):=i;
411 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Ouput with carrier vehicle Lane:'||x_lane_rows(j)||' Schedule:'||x_schedule_rows(j)||' Vehicle:'||x_vehicle_rows(j)||' Index:'||j);
412 j:=j+1;
413
414
415 l_index:=l_index+1;
416 END LOOP;
417
418 END IF;
419
420 END IF;
421
422
423 ELSE
424 --No vehicles are present copy over input lanes to outputs
425
426 x_lane_rows(j):=p_lane_rows(i);
427 x_schedule_rows(j):=p_schedule_rows(i);
428 x_vehicle_rows(j):=NULL;
429 x_ref_rows(j):=i;
430 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Ouput no vehicle:'||x_lane_rows(j)||' Schedule:'||x_schedule_rows(j)||' Vehicle:'||x_vehicle_rows(j)||' Index:'||j);
431 j:=j+1;
432
433
434 END IF;
435
436
437
438
439 END IF;
440
441
442
443
444 i:=p_lane_rows.NEXT(i);
445 END LOOP;
446
447
448
449
450 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Get_Vehicles_For_LaneSchedules');
451
452 IF (l_warning_count > 0)
453 THEN
454 x_return_status:=WSH_UTIL_CORE.G_RET_STS_WARNING;
455 END IF;
456
457
458 EXCEPTION
459
460 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_get_vehicle_type_fail THEN
461 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
462 FTE_FREIGHT_PRICING_UTIL.set_exception('Get_Vehicles_For_LaneSchedules',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_get_vehicle_type_fail');
463 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Get_Vehicles_For_LaneSchedules');
464
465
466 WHEN others THEN
467 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
468 FTE_FREIGHT_PRICING_UTIL.set_exception('Get_Vehicles_For_LaneSchedules',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_others');
469 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
470 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Get_Vehicles_For_LaneSchedules');
471
472
473 END Get_Vehicles_For_LaneSchedules;
474
475
476 PROCEDURE Update_Pricing_Required_Flag(
477 p_trip_id IN NUMBER,
478 x_return_status OUT NOCOPY VARCHAR2) IS
479
480 CURSOR lock_dlegs( c_trip_id IN NUMBER ) IS
481 SELECT dl.reprice_required
482 FROM wsh_delivery_legs dl ,
483 wsh_trip_stops s
484 WHERE dl.pick_up_stop_id = s.stop_id
485 and s.trip_id=c_trip_id
486 FOR UPDATE NOWAIT;
487
488
489 l_temp_rec VARCHAR2(1);
490 l_return_status VARCHAR2(1);
491
492 l_log_level NUMBER := FTE_FREIGHT_PRICING_UTIL.G_DBG;
493
494 l_warning_count NUMBER:=0;
495 BEGIN
496
497
498 FTE_FREIGHT_PRICING_UTIL.reset_dbg_vars;
499 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,'Update_Pricing_Required_Flag','start');
500
501 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
502
503 SAVEPOINT Update_Pricing_Required_Flag;
504
505 OPEN lock_dlegs(p_trip_id);
506 FETCH lock_dlegs INTO l_temp_rec;
507 CLOSE lock_dlegs;
508
509 UPDATE wsh_delivery_legs dl
510 SET dl.reprice_required='N'
511 WHERE dl.pick_up_stop_id IN
512 (select s.stop_id FROM wsh_trip_stops s where
513 s.trip_id=p_trip_id );
514
515 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Update_Pricing_Required_Flag');
516
517 IF (l_warning_count > 0)
518 THEN
519 x_return_status:=WSH_UTIL_CORE.G_RET_STS_WARNING;
520 END IF;
521 EXCEPTION
522 WHEN others THEN
523 ROLLBACK TO Update_Pricing_Required_Flag;
524 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
525 FTE_FREIGHT_PRICING_UTIL.set_exception('Update_Pricing_Required_Flag',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_others');
526 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
527 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Update_Pricing_Required_Flag');
528
529
530 END Update_Pricing_Required_Flag;
531
532
533
534 PROCEDURE Check_Freight_Terms_Manifest(
535 p_trip_id IN NUMBER,
536 p_move_id IN NUMBER,
537 x_term_manifest_flag OUT NOCOPY VARCHAR2,
538 x_return_status OUT NOCOPY VARCHAR2) IS
539
540 -- count the number of deliveries which should not be rated due to freight term
541 CURSOR c_check_trip_freight_term(c_trip_id IN NUMBER)
542 IS
543 SELECT count(wd.delivery_id)
544 FROM wsh_new_deliveries wd, wsh_global_parameters wgp
545 WHERE (
546 ((wd.shipment_direction in ('I'))
547 and (wgp.rate_ib_dels_fgt_term is not null)
548 and (wd.freight_terms_code is not null)
549 and (wgp.rate_ib_dels_fgt_term <> wd.freight_terms_code)
550 )
551 OR
552 ((wd.shipment_direction in ('D'))
553 and (wgp.rate_ds_dels_fgt_term_id is not null)
554 and (wd.freight_terms_code is not null)
555 and (wgp.rate_ds_dels_fgt_term_id <> wd.freight_terms_code)
556 )
557 OR
558 ((nvl(wd.shipment_direction,'O') in ('O','IO'))
559 and (wgp.skip_rate_ob_dels_fgt_term is not null)
560 and (wd.freight_terms_code is not null)
561 and (wgp.skip_rate_ob_dels_fgt_term = wd.freight_terms_code)
562 )
563 )
564 AND wd.delivery_id in
565 (SELECT wdl.delivery_id
566 FROM wsh_delivery_legs wdl,
567 wsh_trip_stops wts1,
568 wsh_trip_stops wts2
569 WHERE wts1.trip_id = c_trip_id
570 AND wts2.trip_id = c_trip_id
571 AND wts1.stop_id = wdl.pick_up_stop_id
572 AND wts2.stop_id = wdl.drop_off_stop_id
573 );
574
575
576 -- count the number of deliveries which should not be rated due to manifesting
577 CURSOR c_check_trip_manifesting(c_trip_id IN NUMBER)
578 IS
579 SELECT count(a.delivery_id)
580 FROM wsh_new_deliveries a,
581 mtl_parameters b,
582 wsh_carriers c
583 WHERE a.organization_id = b.organization_id
584 AND a.carrier_id = c.carrier_id
585 AND c.manifesting_enabled_flag = 'Y'
586 AND b.carrier_manifesting_flag = 'Y'
587 AND a.delivery_id in
588 (SELECT wdl.delivery_id
589 FROM wsh_delivery_legs wdl,
590 wsh_trip_stops wts1,
591 wsh_trip_stops wts2
592 WHERE wts1.trip_id = c_trip_id
593 AND wts2.trip_id = c_trip_id
594 AND wts1.stop_id = wdl.pick_up_stop_id
595 AND wts2.stop_id = wdl.drop_off_stop_id
596 );
597
598 -- count the number of deliveries which should not be rated due to freight term
599 CURSOR c_check_move_freight_term(c_move_id IN NUMBER)
600 IS
601 SELECT count(wd.delivery_id)
602 FROM wsh_new_deliveries wd, wsh_global_parameters wgp
603 WHERE (
604 ((wd.shipment_direction in ('I'))
605 and (wgp.rate_ib_dels_fgt_term is not null)
606 and (wd.freight_terms_code is not null)
607 and (wgp.rate_ib_dels_fgt_term <> wd.freight_terms_code)
608 )
609 OR
610 ((wd.shipment_direction in ('D'))
611 and (wgp.rate_ds_dels_fgt_term_id is not null)
612 and (wd.freight_terms_code is not null)
613 and (wgp.rate_ds_dels_fgt_term_id <> wd.freight_terms_code)
614 )
615 OR
616 ((nvl(wd.shipment_direction,'O') in ('O','IO'))
617 and (wgp.skip_rate_ob_dels_fgt_term is not null)
618 and (wd.freight_terms_code is not null)
619 and (wgp.skip_rate_ob_dels_fgt_term = wd.freight_terms_code)
620 )
621 )
622 AND wd.delivery_id in
623 (SELECT wdl.delivery_id
624 FROM wsh_delivery_legs wdl,
625 wsh_trip_stops wts1,
626 wsh_trip_stops wts2
627 WHERE wts1.trip_id IN (SELECT m.trip_id from fte_trip_moves m where m.move_id= c_move_id)
628 AND wts2.trip_id = wts1.trip_id
629 AND wts1.stop_id = wdl.pick_up_stop_id
630 AND wts2.stop_id = wdl.drop_off_stop_id
631 );
632
633
634 -- count the number of deliveries which should not be rated due to manifesting
635 CURSOR c_check_move_manifesting(c_move_id IN NUMBER)
636 IS
637 SELECT count(a.delivery_id)
638 FROM wsh_new_deliveries a,
639 mtl_parameters b,
640 wsh_carriers c
641 WHERE a.organization_id = b.organization_id
642 AND a.carrier_id = c.carrier_id
643 AND c.manifesting_enabled_flag = 'Y'
644 AND b.carrier_manifesting_flag = 'Y'
645 AND a.delivery_id in
646 (SELECT wdl.delivery_id
647 FROM wsh_delivery_legs wdl,
648 wsh_trip_stops wts1,
649 wsh_trip_stops wts2
650 WHERE wts1.trip_id IN (SELECT m.trip_id from fte_trip_moves m where m.move_id=c_move_id)
651 AND wts2.trip_id = wts1.trip_id
652 AND wts1.stop_id = wdl.pick_up_stop_id
653 AND wts2.stop_id = wdl.drop_off_stop_id
654 );
655
656 l_count NUMBER;
657 l_log_level NUMBER;
658
659 BEGIN
660 l_log_level:=FTE_FREIGHT_PRICING_UTIL.G_DBG;
661
662 FTE_FREIGHT_PRICING_UTIL.reset_dbg_vars;
663 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,'Check_Freight_Terms_Manifest','start');
664
665 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
666
667 x_term_manifest_flag:='N';
668 IF (p_move_id IS NULL)
669 THEN
670 -- validate freight term
671 l_count := 0;
672 OPEN c_check_trip_freight_term(p_trip_id);
673 FETCH c_check_trip_freight_term INTO l_count;
674 CLOSE c_check_trip_freight_term;
675 IF ( l_count > 0 ) THEN
676
677 x_term_manifest_flag:='Y';
678 FTE_FREIGHT_PRICING_UTIL.setmsg(
679 p_api => 'Check_Freight_Terms_Manifest',
680 p_exc => ' ',
681 p_msg_name => 'FTE_PRC_NOTRATE_TRP_FGT_TERM',
682 p_msg_type => 'W',
683 p_trip_id => p_trip_id);
684
685
686 END IF;
687
688 -- Manifesting validation
689 l_count := 0;
690 OPEN c_check_trip_manifesting(p_trip_id);
691 FETCH c_check_trip_manifesting INTO l_count;
692 CLOSE c_check_trip_manifesting;
693 IF ( l_count > 0 ) THEN
694 x_term_manifest_flag:='Y';
695 FTE_FREIGHT_PRICING_UTIL.setmsg(
696 p_api => 'Check_Freight_Terms_Manifest',
697 p_exc => ' ',
698 p_msg_name => 'FTE_PRC_NOTRATE_TRP_MAN',
699 p_msg_type => 'W',
700 p_trip_id => p_trip_id);
701
702
703
704 END IF;
705
706
707 ELSE
708
709 -- validate freight term
710 l_count := 0;
711 OPEN c_check_move_freight_term(p_move_id);
712 FETCH c_check_move_freight_term INTO l_count;
713 CLOSE c_check_move_freight_term;
714 IF ( l_count > 0 ) THEN
715
716 x_term_manifest_flag:='Y';
717 FTE_FREIGHT_PRICING_UTIL.setmsg(
718 p_api => 'Check_Freight_Terms_Manifest',
719 p_exc => ' ',
720 p_msg_name => 'FTE_PRC_NOTRATE_TRP_FGT_TERM',
721 p_msg_type => 'W',
722 p_move_id => p_move_id);
723
724
725 END IF;
726
727 -- Manifesting validation
728 l_count := 0;
729 OPEN c_check_move_manifesting(p_move_id);
730 FETCH c_check_move_manifesting INTO l_count;
731 CLOSE c_check_move_manifesting;
732 IF ( l_count > 0 ) THEN
733 x_term_manifest_flag:='Y';
734 FTE_FREIGHT_PRICING_UTIL.setmsg(
735 p_api => 'Check_Freight_Terms_Manifest',
736 p_exc => ' ',
737 p_msg_name => 'FTE_PRC_NOTRATE_TRP_MAN',
738 p_msg_type => 'W',
739 p_move_id => p_move_id);
740
741
742
743 END IF;
744
745
746
747 END IF;
748 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Check_Freight_Terms_Manifest');
749
750 EXCEPTION
751 WHEN others THEN
752
753 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
754 FTE_FREIGHT_PRICING_UTIL.set_exception('Check_Freight_Terms_Manifest',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_others');
755 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
756 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Check_Freight_Terms_Manifest');
757
758
759 END Check_Freight_Terms_Manifest;
760
761 PROCEDURE Move_Records_To_Main(
762 p_trip_id IN NUMBER,
763 p_lane_id IN NUMBER,
764 p_schedule_id IN NUMBER,
765 p_comparison_request_id IN NUMBER,
766 x_return_status OUT NOCOPY VARCHAR2) IS
767
768 CURSOR lock_temp_rec(c_lane_id IN NUMBER,
769 c_schedule_id IN NUMBER,
770 c_vehicle_type_id IN NUMBER,
771 c_request_id IN NUMBER) IS
772 SELECT *
773 FROM FTE_FREIGHT_COSTS_TEMP fct
774 WHERE ( fct.comparison_request_id = c_request_id)
775 AND ((fct.lane_id = c_lane_id) OR (fct.schedule_id= c_schedule_id))
776 AND (fct.vehicle_type_id = c_vehicle_type_id)
777 AND(fct.CHARGE_SOURCE_CODE='PRICING_ENGINE')
778 FOR UPDATE NOWAIT;
779
780
781 l_temp_rec FTE_FREIGHT_COSTS_TEMP%ROWTYPE;
782 l_empty_freight_rec WSH_FREIGHT_COSTS_PVT.Freight_Cost_Rec_Type;
783 l_freight_rec WSH_FREIGHT_COSTS_PVT.Freight_Cost_Rec_Type;
784 l_freight_cost_id NUMBER;
785 l_return_status VARCHAR2(1);
786 l_rowid VARCHAR2(30);
787 l_init_msg_list VARCHAR2(30) :=FND_API.G_FALSE;
788 l_trip_vehicle_type NUMBER;
789
790 l_log_level NUMBER := FTE_FREIGHT_PRICING_UTIL.G_DBG;
791
792 l_warning_count NUMBER:=0;
793 BEGIN
794
795
796 FTE_FREIGHT_PRICING_UTIL.reset_dbg_vars;
797 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,'Move_Records_To_Main','start');
798
799 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
800
801 SAVEPOINT Move_Records_To_Main;
802 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
803 'p_trip_id = '||p_trip_id);
804 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
805 'p_lane_id = '||p_lane_id);
806 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
807 'p_schedule_id = '||p_schedule_id);
808 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
809 'p_comparison_request_id = '||p_comparison_request_id);
810
811 l_trip_vehicle_type:=NULL;
812 FTE_TL_CACHE.Get_Vehicle_Type(p_trip_id => p_trip_id,
813 p_vehicle_item_id =>NULL,
814 x_vehicle_type => l_trip_vehicle_type,
815 x_return_status => l_return_status);
816 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
817 THEN
818 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
819 THEN
820
821 raise FTE_FREIGHT_PRICING_UTIL.g_tl_get_vehicle_type_fail;
822 END IF;
823 END IF;
824
825 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Vehicle:'||l_trip_vehicle_type);
826
827 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'>>1');
828
829 IF (p_lane_id IS NULL AND p_schedule_id IS NULL)
830 THEN
831
832 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Lane:'||p_lane_id||'Schedule:'||p_schedule_id||'Vehicle:'||l_trip_vehicle_type);
833 raise FTE_FREIGHT_PRICING_UTIL.g_tl_move_rec_lane_sched_null;
834
835 END IF;
836
837 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'>>2');
838 FOR l_temp_rec IN lock_temp_rec(p_lane_id,p_schedule_id,l_trip_vehicle_type,p_comparison_request_id)
839 LOOP
840
841 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'>>3');
842 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
843 'l_temp_rec.freight_cost_id='||l_temp_rec.freight_cost_id);
844
845 -- l_freight_rec.freight_cost_id:=l_temp_rec.freight_cost_id;
846 --Clear all values
847 l_freight_rec:=l_empty_freight_rec;
848
849 l_freight_rec.freight_cost_id:= NULL;
850 l_freight_rec.freight_cost_type_id:=l_temp_rec.freight_cost_type_id;
851 l_freight_rec.unit_amount:=l_temp_rec.unit_amount;
852 l_freight_rec.calculation_method:=l_temp_rec.calculation_method;
853 l_freight_rec.uom:=l_temp_rec.uom;
854 l_freight_rec.quantity:=l_temp_rec.quantity;
855 l_freight_rec.total_amount:=l_temp_rec.total_amount;
856 l_freight_rec.currency_code:=l_temp_rec.currency_code;
857 l_freight_rec.conversion_date:=l_temp_rec.conversion_date;
858 l_freight_rec.conversion_rate:=l_temp_rec.conversion_rate;
859 l_freight_rec.conversion_type_code:=l_temp_rec.conversion_type_code;
860 l_freight_rec.trip_id:=l_temp_rec.trip_id;
861
862 l_freight_rec.stop_id:=l_temp_rec.stop_id;
863 l_freight_rec.delivery_id:=l_temp_rec.delivery_id;
864 l_freight_rec.delivery_leg_id:=l_temp_rec.delivery_leg_id;
865 l_freight_rec.delivery_detail_id:=l_temp_rec.delivery_detail_id;
866 l_freight_rec.request_id:=l_temp_rec.request_id;
867 l_freight_rec.line_type_code:=l_temp_rec.line_type_code;
868 l_freight_rec.pricing_list_header_id:=
869 l_temp_rec.pricing_list_header_id;
870 l_freight_rec.pricing_list_line_id:=l_temp_rec.pricing_list_line_id;
871 l_freight_rec.applied_to_charge_id:=l_temp_rec.applied_to_charge_id;
872 l_freight_rec.charge_unit_value:=l_temp_rec.charge_unit_value;
873 l_freight_rec.charge_source_code:=l_temp_rec.charge_source_code;
874 l_freight_rec.estimated_flag:=l_temp_rec.estimated_flag;
875
876
877 --billable columns
878 l_freight_rec.BILLABLE_UOM:= l_temp_rec.BILLABLE_UOM;
879 l_freight_rec.BILLABLE_BASIS:= l_temp_rec.BILLABLE_BASIS;
880 l_freight_rec.BILLABLE_QUANTITY:=l_temp_rec.BILLABLE_QUANTITY;
881
882
883 l_freight_rec.creation_date:=l_temp_rec.creation_date;
884 l_freight_rec.created_by:= l_temp_rec.created_by;
885 l_freight_rec.last_update_date:= l_temp_rec.last_update_date;
886 l_freight_rec.last_updated_by:=l_temp_rec.last_updated_by;
887 l_freight_rec.last_update_login:= l_temp_rec.last_update_login;
888 l_freight_rec.program_application_id:=l_temp_rec.program_application_id;
889 l_freight_rec.program_id:= l_temp_rec.program_id;
890 l_freight_rec.program_update_date:= l_temp_rec.program_update_date;
891
892
893 IF ((l_freight_rec.delivery_leg_id IS NOT NULL)
894 AND(l_freight_rec.delivery_detail_id IS NULL)
895 AND (l_freight_rec.line_type_code='SUMMARY'))
896 THEN
897 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'>>4');
898 l_freight_rec.freight_cost_id:=NULL;
899 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Dleg ID:'||l_freight_rec.delivery_leg_id);
900 l_freight_rec.freight_cost_id:=FTE_FREIGHT_PRICING.get_fc_id_from_dleg(l_freight_rec.delivery_leg_id);
901
902 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'FC ID:'||l_freight_rec.freight_cost_id);
903
904
905 IF (l_freight_rec.freight_cost_id IS NULL )
906 THEN
907 raise FTE_FREIGHT_PRICING_UTIL.g_tl_get_fc_id_fail;
908
909 END IF;
910
911 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'>>5');
912
913 WSH_FREIGHT_COSTS_PVT.Update_Freight_Cost(
914 p_rowid => l_rowid,
915 p_freight_cost_info => l_freight_rec,
916 x_return_status => l_return_status);
917
918 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
919 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
920 FTE_FREIGHT_PRICING_UTIL.set_location(p_loc => 'Update_Freight_Cost');
921 raise FTE_FREIGHT_PRICING_UTIL.g_update_freight_cost_failed;
922 ELSE
923 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_WRN,'Update_Freight_Cost returned warning ');
924 END IF;
925 END IF;
926
927 ELSE
928 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'>>6');
929
930 WSH_FREIGHT_COSTS_PVT.Create_Freight_Cost(
931 p_freight_cost_info => l_freight_rec,
932 x_rowid => l_rowid,
933 x_freight_cost_id => l_freight_cost_id,
934 x_return_status => l_return_status);
935
936 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
937 THEN
938 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
939 THEN
940 raise FTE_FREIGHT_PRICING_UTIL.g_tl_cr_fr_cost_fail;
941 END IF;
942 END IF;
943
944
945 END IF;
946
947 END LOOP;
948
949 ----Reset pricing required flags
950
951 Update_Pricing_Required_Flag(
952 p_trip_id=>p_trip_id,
953 x_return_status =>l_return_status);
954
955 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
956 THEN
957 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
958 THEN
959 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_WRN,
960 'Failied to set reprice required flag for TRIP ID:'||p_trip_id);
961 END IF;
962 END IF;
963
964 ---Delete fc temp record
965 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'>>7');
966
967 DELETE
968 FROM FTE_FREIGHT_COSTS_TEMP fct
969 WHERE ( fct.comparison_request_id = p_comparison_request_id)
970 AND ((fct.lane_id = p_lane_id) OR (fct.schedule_id= p_schedule_id))
971 AND(fct.CHARGE_SOURCE_CODE='PRICING_ENGINE');
972
973 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'>>8');
974
975 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Move_Records_To_Main');
976
977 IF (l_warning_count > 0)
978 THEN
979 x_return_status:=WSH_UTIL_CORE.G_RET_STS_WARNING;
980 END IF;
981
982 EXCEPTION
983
984
985 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_get_vehicle_type_fail THEN
986 ROLLBACK TO Move_Records_To_Main;
987 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
988 FTE_FREIGHT_PRICING_UTIL.set_exception('Move_Records_To_Main',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_get_vehicle_type_fail');
989 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Move_Records_To_Main');
990
991
992 WHEN FTE_FREIGHT_PRICING_UTIL.g_update_freight_cost_failed THEN
993 ROLLBACK TO Move_Records_To_Main;
994 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
995 FTE_FREIGHT_PRICING_UTIL.set_exception('Move_Records_To_Main',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_update_freight_cost_failed');
996 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Move_Records_To_Main');
997
998
999
1000 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_get_fc_id_fail THEN
1001 ROLLBACK TO Move_Records_To_Main;
1002 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1003 FTE_FREIGHT_PRICING_UTIL.set_exception('Move_Records_To_Main',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_get_fc_id_fail');
1004 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Move_Records_To_Main');
1005
1006
1007
1008 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_cr_fr_cost_fail THEN
1009 ROLLBACK TO Move_Records_To_Main;
1010 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1011 FTE_FREIGHT_PRICING_UTIL.set_exception('Move_Records_To_Main',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_cr_fr_cost_fail');
1012 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Move_Records_To_Main');
1013
1014
1015 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_move_rec_lane_sched_null THEN
1016 ROLLBACK TO Move_Records_To_Main;
1017 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1018 FTE_FREIGHT_PRICING_UTIL.set_exception('Move_Records_To_Main',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_move_rec_lane_sched_null');
1019 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Move_Records_To_Main');
1020
1021
1022
1023 WHEN others THEN
1024 ROLLBACK TO Move_Records_To_Main;
1025 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1026 FTE_FREIGHT_PRICING_UTIL.set_exception('Move_Records_To_Main',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_others');
1027 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
1028 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Move_Records_To_Main');
1029
1030
1031 END Move_Records_To_Main;
1032
1033
1034
1035 PROCEDURE Move_Dlv_Records_To_Main(
1036 p_dleg_id IN NUMBER,
1037 p_lane_id IN NUMBER,
1038 p_schedule_id IN NUMBER,
1039 p_comparison_request_id IN NUMBER,
1040 x_return_status OUT NOCOPY VARCHAR2) IS
1041
1042 CURSOR c_check_fake(c_lane_id IN NUMBER,
1043 c_schedule_id IN NUMBER,
1044 c_vehicle_type_id IN NUMBER,
1045 c_request_id IN NUMBER,
1046 c_fake_trip_id IN NUMBER) IS
1047 SELECT fct.trip_id
1048 FROM FTE_FREIGHT_COSTS_TEMP fct
1049 WHERE ( fct.comparison_request_id = c_request_id)
1050 AND ((fct.lane_id = c_lane_id) OR (fct.schedule_id= c_schedule_id))
1051 AND (fct.vehicle_type_id = c_vehicle_type_id)
1052 AND(fct.CHARGE_SOURCE_CODE='PRICING_ENGINE')
1053 AND (fct.trip_id IS NOT NULL)
1054 AND ( fct.trip_id = c_fake_trip_id)
1055 AND ROWNUM=1;
1056
1057
1058 CURSOR c_get_dleg_info_from_dleg(c_dleg_id IN NUMBER)
1059 IS
1060 SELECT dl.delivery_id,
1061 s.trip_id,
1062 dl.pick_up_stop_id,
1063 dl.drop_off_stop_id
1064 FROM wsh_delivery_legs dl,
1065 wsh_trip_stops s
1066 WHERE dl.drop_off_stop_id = s.stop_id and
1067 dl.delivery_leg_id=c_dleg_id;
1068
1069
1070 CURSOR lock_temp_rec(c_lane_id IN NUMBER,
1071 c_schedule_id IN NUMBER,
1072 c_vehicle_type_id IN NUMBER,
1073 c_request_id IN NUMBER) IS
1074 SELECT *
1075 FROM FTE_FREIGHT_COSTS_TEMP fct
1076 WHERE ( fct.comparison_request_id = c_request_id)
1077 AND ((fct.lane_id = c_lane_id) OR (fct.schedule_id= c_schedule_id))
1078 AND (fct.vehicle_type_id = c_vehicle_type_id)
1079 AND(fct.CHARGE_SOURCE_CODE='PRICING_ENGINE')
1080 FOR UPDATE NOWAIT;
1081
1082
1083 l_temp_rec FTE_FREIGHT_COSTS_TEMP%ROWTYPE;
1084 l_empty_freight_rec WSH_FREIGHT_COSTS_PVT.Freight_Cost_Rec_Type;
1085 l_freight_rec WSH_FREIGHT_COSTS_PVT.Freight_Cost_Rec_Type;
1086 l_freight_cost_id NUMBER;
1087 l_return_status VARCHAR2(1);
1088 l_rowid VARCHAR2(30);
1089 l_init_msg_list VARCHAR2(30) :=FND_API.G_FALSE;
1090 l_trip_vehicle_type NUMBER;
1091 l_delivery_id NUMBER;
1092 l_trip_id NUMBER;
1093 l_pickup_stop_id NUMBER;
1094 l_dropoff_stop_id NUMBER;
1095 l_fake_trip NUMBER;
1096
1097 l_log_level NUMBER := FTE_FREIGHT_PRICING_UTIL.G_DBG;
1098
1099 l_warning_count NUMBER:=0;
1100 BEGIN
1101
1102
1103 FTE_FREIGHT_PRICING_UTIL.reset_dbg_vars;
1104 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,'Move_Dlv_Records_To_Main','start');
1105
1106 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1107
1108 SAVEPOINT Move_Dlv_Records_To_Main;
1109 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
1110 'p_dleg_id = '||p_dleg_id);
1111 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
1112 'p_lane_id = '||p_lane_id);
1113 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
1114 'p_schedule_id = '||p_schedule_id);
1115 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
1116 'p_comparison_request_id = '||p_comparison_request_id);
1117
1118 --Get trip/stop/delivery info from the dleg
1119
1120 OPEN c_get_dleg_info_from_dleg(p_dleg_id);
1121 FETCH c_get_dleg_info_from_dleg INTO l_delivery_id,l_trip_id,l_pickup_stop_id,l_dropoff_stop_id;
1122 CLOSE c_get_dleg_info_from_dleg;
1123
1124
1125 l_trip_vehicle_type:=NULL;
1126 FTE_TL_CACHE.Get_Vehicle_Type(p_trip_id => l_trip_id,
1127 p_vehicle_item_id =>NULL,
1128 x_vehicle_type => l_trip_vehicle_type,
1129 x_return_status => l_return_status);
1130 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
1131 THEN
1132 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
1133 THEN
1134
1135 raise FTE_FREIGHT_PRICING_UTIL.g_tl_get_vehicle_type_fail;
1136 END IF;
1137 END IF;
1138
1139 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Vehicle:'||l_trip_vehicle_type);
1140
1141 IF (p_lane_id IS NULL AND p_schedule_id IS NULL)
1142 THEN
1143
1144 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Lane:'||p_lane_id||'Schedule:'||p_schedule_id||'Vehicle:'||l_trip_vehicle_type);
1145 raise FTE_FREIGHT_PRICING_UTIL.g_tl_move_rec_lane_sched_null;
1146
1147 END IF;
1148
1149 --Check for fake trip
1150
1151 OPEN c_check_fake(p_lane_id,p_schedule_id,l_trip_vehicle_type,p_comparison_request_id,FTE_TL_CACHE.FAKE_TRIP_ID);
1152 FETCH c_check_fake INTO l_fake_trip;
1153 IF(c_check_fake%FOUND)
1154 THEN
1155 l_fake_trip:=FTE_TL_CACHE.FAKE_TRIP_ID;
1156
1157 ELSE
1158 l_fake_trip:=NULL;
1159
1160 END IF;
1161 CLOSE c_check_fake;
1162
1163 IF (l_fake_trip IS NOT NULL)
1164 THEN
1165
1166
1167
1168 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'>>1');
1169
1170 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'>>2');
1171 FOR l_temp_rec IN lock_temp_rec(p_lane_id,p_schedule_id,l_trip_vehicle_type,p_comparison_request_id)
1172 LOOP
1173
1174 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'>>3');
1175 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,
1176 'l_temp_rec.freight_cost_id='||l_temp_rec.freight_cost_id);
1177
1178 -- l_freight_rec.freight_cost_id:=l_temp_rec.freight_cost_id;
1179 --Clear all values
1180 l_freight_rec:=l_empty_freight_rec;
1181
1182
1183 l_freight_rec.freight_cost_id:= NULL;
1184 l_freight_rec.freight_cost_type_id:=l_temp_rec.freight_cost_type_id;
1185 l_freight_rec.unit_amount:=l_temp_rec.unit_amount;
1186 l_freight_rec.calculation_method:=l_temp_rec.calculation_method;
1187 l_freight_rec.uom:=l_temp_rec.uom;
1188 l_freight_rec.quantity:=l_temp_rec.quantity;
1189 l_freight_rec.total_amount:=l_temp_rec.total_amount;
1190 l_freight_rec.currency_code:=l_temp_rec.currency_code;
1191 l_freight_rec.conversion_date:=l_temp_rec.conversion_date;
1192 l_freight_rec.conversion_rate:=l_temp_rec.conversion_rate;
1193 l_freight_rec.conversion_type_code:=l_temp_rec.conversion_type_code;
1194
1195 --Plug in real trip id
1196 IF (l_temp_rec.trip_id IS NOT NULL)
1197 THEN
1198 l_freight_rec.trip_id:=l_trip_id;
1199 END IF;
1200
1201
1202 --Plug in real stop id
1203 IF ((l_temp_rec.stop_id IS NOT NULL) AND (l_temp_rec.stop_id = FTE_TL_CACHE.FAKE_STOP_ID_1))
1204 THEN
1205
1206 l_freight_rec.stop_id:=l_pickup_stop_id;
1207
1208 ELSIF((l_temp_rec.stop_id IS NOT NULL) AND (l_temp_rec.stop_id = FTE_TL_CACHE.FAKE_STOP_ID_2))
1209 THEN
1210 l_freight_rec.stop_id:=l_dropoff_stop_id;
1211
1212 ELSE
1213 l_freight_rec.stop_id:=l_temp_rec.stop_id;
1214
1215 END IF;
1216
1217
1218
1219
1220 l_freight_rec.delivery_id:=l_temp_rec.delivery_id;
1221
1222
1223 --Plug in real dleg id
1224 IF((l_temp_rec.delivery_leg_id IS NOT NULL) AND (l_temp_rec.delivery_leg_id = FTE_TL_CACHE.FAKE_DLEG_ID))
1225 THEN
1226 l_freight_rec.delivery_leg_id:=p_dleg_id;
1227 ELSE
1228 l_freight_rec.delivery_leg_id:=l_temp_rec.delivery_leg_id;
1229 END IF;
1230
1231
1232 l_freight_rec.delivery_detail_id:=l_temp_rec.delivery_detail_id;
1233 l_freight_rec.request_id:=l_temp_rec.request_id;
1234 l_freight_rec.line_type_code:=l_temp_rec.line_type_code;
1235 l_freight_rec.pricing_list_header_id:=
1236 l_temp_rec.pricing_list_header_id;
1237 l_freight_rec.pricing_list_line_id:=l_temp_rec.pricing_list_line_id;
1238 l_freight_rec.applied_to_charge_id:=l_temp_rec.applied_to_charge_id;
1239 l_freight_rec.charge_unit_value:=l_temp_rec.charge_unit_value;
1240 l_freight_rec.charge_source_code:=l_temp_rec.charge_source_code;
1241 l_freight_rec.estimated_flag:=l_temp_rec.estimated_flag;
1242
1243
1244 --billable columns
1245 l_freight_rec.BILLABLE_UOM:= l_temp_rec.BILLABLE_UOM;
1246 l_freight_rec.BILLABLE_BASIS:= l_temp_rec.BILLABLE_BASIS;
1247 l_freight_rec.BILLABLE_QUANTITY:=l_temp_rec.BILLABLE_QUANTITY;
1248
1249
1250 l_freight_rec.creation_date:=l_temp_rec.creation_date;
1251 l_freight_rec.created_by:= l_temp_rec.created_by;
1252 l_freight_rec.last_update_date:= l_temp_rec.last_update_date;
1253 l_freight_rec.last_updated_by:=l_temp_rec.last_updated_by;
1254 l_freight_rec.last_update_login:= l_temp_rec.last_update_login;
1255 l_freight_rec.program_application_id:=
1256 l_temp_rec.program_application_id;
1257 l_freight_rec.program_id:= l_temp_rec.program_id;
1258 l_freight_rec.program_update_date:= l_temp_rec.program_update_date;
1259
1260
1261 IF ((l_freight_rec.delivery_leg_id IS NOT NULL)
1262 AND(l_freight_rec.delivery_detail_id IS NULL)
1263 AND (l_freight_rec.line_type_code='SUMMARY'))
1264 THEN
1265 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'>>4');
1266 l_freight_rec.freight_cost_id:=NULL;
1267 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Dleg ID:'||l_freight_rec.delivery_leg_id);
1268 l_freight_rec.freight_cost_id:=FTE_FREIGHT_PRICING.get_fc_id_from_dleg(l_freight_rec.delivery_leg_id);
1269
1270 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'FC ID:'||l_freight_rec.freight_cost_id);
1271
1272
1273 IF (l_freight_rec.freight_cost_id IS NULL )
1274 THEN
1275 raise FTE_FREIGHT_PRICING_UTIL.g_tl_get_fc_id_fail;
1276
1277 END IF;
1278
1279 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'>>5');
1280
1281 WSH_FREIGHT_COSTS_PVT.Update_Freight_Cost(
1282 p_rowid => l_rowid,
1283 p_freight_cost_info => l_freight_rec,
1284 x_return_status => l_return_status);
1285
1286 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1287 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1288 FTE_FREIGHT_PRICING_UTIL.set_location(p_loc => 'Update_Freight_Cost');
1289 raise FTE_FREIGHT_PRICING_UTIL.g_update_freight_cost_failed;
1290 ELSE
1291 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_WRN,'Update_Freight_Cost returned warning ');
1292 END IF;
1293 END IF;
1294
1295 ELSE
1296 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'>>6');
1297
1298 WSH_FREIGHT_COSTS_PVT.Create_Freight_Cost(
1299 p_freight_cost_info => l_freight_rec,
1300 x_rowid => l_rowid,
1301 x_freight_cost_id => l_freight_cost_id,
1302 x_return_status => l_return_status);
1303
1304 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
1305 THEN
1306 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
1307 THEN
1308 raise FTE_FREIGHT_PRICING_UTIL.g_tl_cr_fr_cost_fail;
1309 END IF;
1310 END IF;
1311
1312
1313 END IF;
1314
1315 END LOOP;
1316
1317 ----Reset pricing required flags
1318
1319 Update_Pricing_Required_Flag(
1320 p_trip_id=>l_trip_id,
1321 x_return_status =>l_return_status);
1322
1323 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
1324 THEN
1325 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
1326 THEN
1327 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_WRN,
1328 'Failied to set reprice required flag for TRIP ID:'||l_trip_id);
1329 END IF;
1330 END IF;
1331
1332 ---Delete fc temp record
1333 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'>>7');
1334
1335 DELETE
1336 FROM FTE_FREIGHT_COSTS_TEMP fct
1337 WHERE ( fct.comparison_request_id = p_comparison_request_id)
1338 AND ((fct.lane_id = p_lane_id) OR (fct.schedule_id= p_schedule_id))
1339 AND(fct.CHARGE_SOURCE_CODE='PRICING_ENGINE');
1340
1341 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'>>8');
1342
1343
1344 Update_Distance_To_Next_Stop(
1345 p_trip_id =>l_trip_id,
1346 x_return_status=>l_return_status);
1347
1348 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
1349 THEN
1350 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
1351 THEN
1352 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_WRN,
1353 'Failied to update stop distances for :'||l_trip_id);
1354 raise FTE_FREIGHT_PRICING_UTIL.g_tl_update_dist_stop_fail;
1355 END IF;
1356 END IF;
1357
1358
1359
1360
1361 ELSE
1362
1363
1364 Move_Records_To_Main(
1365 p_trip_id =>l_trip_id,
1366 p_lane_id=>p_lane_id,
1367 p_schedule_id =>p_schedule_id,
1368 p_comparison_request_id=>p_comparison_request_id,
1369 x_return_status=>l_return_status);
1370
1371 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
1372 THEN
1373 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
1374 THEN
1375
1376 raise FTE_FREIGHT_PRICING_UTIL.g_tl_move_rec_to_main_fail;
1377 END IF;
1378 END IF;
1379
1380 END IF;
1381
1382
1383
1384
1385
1386
1387 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Move_Dlv_Records_To_Main');
1388
1389 IF (l_warning_count > 0)
1390 THEN
1391 x_return_status:=WSH_UTIL_CORE.G_RET_STS_WARNING;
1392 END IF;
1393
1394 EXCEPTION
1395
1396
1397 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_update_dist_stop_fail THEN
1398 ROLLBACK TO Move_Dlv_Records_To_Main;
1399 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1400 FTE_FREIGHT_PRICING_UTIL.set_exception('Move_Dlv_Records_To_Main',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_update_dist_stop_fail');
1401 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Move_Dlv_Records_To_Main');
1402
1403
1404
1405 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_move_rec_to_main_fail THEN
1406 ROLLBACK TO Move_Dlv_Records_To_Main;
1407 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1408 FTE_FREIGHT_PRICING_UTIL.set_exception('Move_Dlv_Records_To_Main',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_move_rec_to_main_fail');
1409 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Move_Dlv_Records_To_Main');
1410
1411
1412 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_get_vehicle_type_fail THEN
1413 ROLLBACK TO Move_Dlv_Records_To_Main;
1414 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1415 FTE_FREIGHT_PRICING_UTIL.set_exception('Move_Dlv_Records_To_Main',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_get_vehicle_type_fail');
1416 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Move_Dlv_Records_To_Main');
1417
1418
1419 WHEN FTE_FREIGHT_PRICING_UTIL.g_update_freight_cost_failed THEN
1420 ROLLBACK TO Move_Dlv_Records_To_Main;
1421 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1422 FTE_FREIGHT_PRICING_UTIL.set_exception('Move_Dlv_Records_To_Main',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_update_freight_cost_failed');
1423 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Move_Dlv_Records_To_Main');
1424
1425
1426
1427 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_get_fc_id_fail THEN
1428 ROLLBACK TO Move_Dlv_Records_To_Main;
1429 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1430 FTE_FREIGHT_PRICING_UTIL.set_exception('Move_Dlv_Records_To_Main',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_get_fc_id_fail');
1431 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Move_Dlv_Records_To_Main');
1432
1433
1434
1435 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_cr_fr_cost_fail THEN
1436 ROLLBACK TO Move_Dlv_Records_To_Main;
1437 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1438 FTE_FREIGHT_PRICING_UTIL.set_exception('Move_Dlv_Records_To_Main',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_cr_fr_cost_fail');
1439 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Move_Dlv_Records_To_Main');
1440
1441
1442 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_move_rec_lane_sched_null THEN
1443 ROLLBACK TO Move_Dlv_Records_To_Main;
1444 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1445 FTE_FREIGHT_PRICING_UTIL.set_exception('Move_Dlv_Records_To_Main',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_move_rec_lane_sched_null');
1446 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Move_Dlv_Records_To_Main');
1447
1448
1449
1450 WHEN others THEN
1451 ROLLBACK TO Move_Dlv_Records_To_Main;
1452 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1453 FTE_FREIGHT_PRICING_UTIL.set_exception('Move_Dlv_Records_To_Main',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_others');
1454 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
1455 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Move_Dlv_Records_To_Main');
1456
1457
1458 END Move_Dlv_Records_To_Main;
1459
1460
1461
1462 PROCEDURE Delete_Main_Records(
1463 p_trip_id IN NUMBER,
1464 x_return_status OUT NOCOPY VARCHAR2) IS
1465
1466 CURSOR lock_trip_recs(c_trip_id IN NUMBER ) IS
1467
1468 SELECT wfc.freight_cost_id
1469 FROM wsh_freight_costs wfc
1470 WHERE wfc.trip_id = c_trip_id
1471 and wfc.charge_source_code='PRICING_ENGINE'
1472 FOR UPDATE NOWAIT;
1473
1474 CURSOR get_stops(c_trip_id IN NUMBER ) IS
1475 SELECT s.stop_id
1476 FROM wsh_trip_stops s
1477 WHERE s.trip_id= c_trip_id;
1478
1479
1480 CURSOR lock_stop_rec(c_stop_id IN NUMBER ) IS
1481
1482 SELECT wfc.freight_cost_id
1483 FROM wsh_freight_costs wfc
1484 WHERE wfc.stop_id =c_stop_id
1485 and wfc.charge_source_code='PRICING_ENGINE'
1486 FOR UPDATE OF wfc.freight_cost_id NOWAIT;
1487
1488
1489 CURSOR get_dlegs(c_trip_id IN NUMBER) IS
1490
1491 SELECT dl.delivery_leg_id FROM wsh_delivery_legs dl , wsh_trip_stops s
1492 WHERE dl.pick_up_stop_id=s.stop_id and s.trip_id=p_trip_id;
1493
1494 CURSOR lock_detail_recs(c_trip_id IN NUMBER) IS
1495 SELECT wfc.freight_cost_id
1496 FROM wsh_freight_costs wfc,
1497 wsh_delivery_legs dl ,
1498 wsh_trip_stops s
1499 WHERE wfc.delivery_leg_id = dl.delivery_leg_id
1500 and dl.pick_up_stop_id=s.stop_id
1501 and s.trip_id=c_trip_id
1502 and wfc.charge_source_code='PRICING_ENGINE'
1503 -- and wfc.delivery_detail_id is not null
1504 FOR UPDATE OF wfc.freight_cost_id NOWAIT;
1505
1506
1507 l_lock_ids DBMS_UTILITY.NUMBER_ARRAY;
1508 l_stop_ids DBMS_UTILITY.NUMBER_ARRAY;
1509 l_dleg_ids DBMS_UTILITY.NUMBER_ARRAY;
1510
1511 l_wfc_detail_ids DBMS_UTILITY.NUMBER_ARRAY;
1512 l_wfc_stop_ids DBMS_UTILITY.NUMBER_ARRAY;
1513 l_wfc_trip_ids DBMS_UTILITY.NUMBER_ARRAY;
1514 l_wfc_one_stop_ids DBMS_UTILITY.NUMBER_ARRAY;
1515 l_wfc_temp_stop_ids DBMS_UTILITY.NUMBER_ARRAY;
1516
1517 l_lock_id NUMBER;
1518 j NUMBER;
1519 i NUMBER;
1520
1521 l_return_status VARCHAR2(1);
1522
1523 l_log_level NUMBER := FTE_FREIGHT_PRICING_UTIL.G_DBG;
1524
1525 l_warning_count NUMBER:=0;
1526 BEGIN
1527
1528 FTE_FREIGHT_PRICING_UTIL.reset_dbg_vars;
1529 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,'Delete_Main_Records','start');
1530
1531 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1532
1533 SAVEPOINT Delete_Main_Records;
1534
1535
1536
1537 --lock trip level recs
1538
1539 OPEN lock_trip_recs(p_trip_id);
1540 FETCH lock_trip_recs BULK COLLECT INTO l_wfc_trip_ids;
1541 CLOSE lock_trip_recs;
1542
1543 --Delete trip level records
1544
1545 IF (l_wfc_trip_ids.FIRST IS NOT NULL)
1546 THEN
1547
1548 FORALL i IN l_wfc_trip_ids.FIRST..l_wfc_trip_ids.LAST
1549 DELETE
1550 FROM wsh_freight_costs wfc
1551 WHERE wfc.freight_cost_id = l_wfc_trip_ids(i);
1552 END IF;
1553
1554
1555
1556
1557 --select all the stops for the trip
1558
1559 OPEN get_stops(p_trip_id);
1560 FETCH get_stops BULK COLLECT INTO l_stop_ids;
1561 CLOSE get_stops;
1562
1563 IF (l_stop_ids.FIRST IS NOT NULL)
1564 THEN
1565
1566
1567 --lock stop recs one stop at a time(done for performance)
1568
1569
1570 l_wfc_stop_ids.delete;
1571 l_wfc_temp_stop_ids.delete;
1572 l_lock_id:=NULL;
1573 FOR i IN l_stop_ids.FIRST..l_stop_ids.LAST
1574 LOOP
1575 OPEN lock_stop_rec(l_stop_ids(i));
1576 FETCH lock_stop_rec BULK COLLECT INTO l_wfc_one_stop_ids;
1577 IF (l_wfc_one_stop_ids.FIRST IS NOT NULL)
1578 THEN
1579
1580 FOR j IN l_wfc_one_stop_ids.FIRST .. l_wfc_one_stop_ids.LAST
1581 LOOP
1582 IF (l_wfc_one_stop_ids(j) IS NOT NULL)
1583 THEN
1584 l_wfc_temp_stop_ids(l_wfc_one_stop_ids(j)):=l_wfc_one_stop_ids(j);
1585
1586 END IF;
1587
1588 END LOOP;
1589
1590 END IF;
1591 CLOSE lock_stop_rec;
1592
1593 END LOOP;
1594
1595
1596 --delete stop recs
1597
1598 IF (l_wfc_temp_stop_ids.FIRST IS NOT NULL)
1599 THEN
1600
1601 --consolidate array so that it is contiguos
1602
1603 i:=1;
1604 j:=l_wfc_temp_stop_ids.FIRST;
1605 WHILE(j IS NOT NULL)
1606 LOOP
1607 l_wfc_stop_ids(i):=l_wfc_temp_stop_ids(j);
1608 i:=i+1;
1609 j:=l_wfc_temp_stop_ids.NEXT(j);
1610
1611 END LOOP;
1612
1613 FORALL i IN l_wfc_stop_ids.FIRST..l_wfc_stop_ids.LAST
1614 DELETE
1615 FROM wsh_freight_costs wfc
1616 WHERE wfc.freight_cost_id = l_wfc_stop_ids(i) ;
1617 END IF;
1618
1619
1620 --Do not delete delivery_leg level summaries
1621
1622 --get dlegs
1623
1624 OPEN get_dlegs(p_trip_id);
1625 FETCH get_dlegs BULK COLLECT INTO l_dleg_ids;
1626 CLOSE get_dlegs;
1627
1628
1629 IF(l_dleg_ids.FIRST IS NOT NULL)
1630 THEN
1631
1632 --locks both dleg and detail recs
1633
1634 OPEN lock_detail_recs(p_trip_id);
1635 FETCH lock_detail_recs BULK COLLECT INTO l_wfc_detail_ids;
1636 CLOSE lock_detail_recs;
1637
1638
1639
1640 IF (l_wfc_detail_ids.FIRST IS NOT NULL)
1641 THEN
1642 --Delete detail level records
1643
1644
1645 FORALL i IN l_wfc_detail_ids.FIRST..l_wfc_detail_ids.LAST
1646 DELETE
1647 FROM wsh_freight_costs wfc
1648 WHERE wfc.freight_cost_id = l_wfc_detail_ids(i)
1649 and wfc.delivery_detail_id is not null;
1650
1651
1652 --Clear rates for dleg recs
1653
1654 FORALL i IN l_wfc_detail_ids.FIRST..l_wfc_detail_ids.LAST
1655 UPDATE wsh_freight_costs wfc
1656 set wfc.unit_amount=null,
1657 wfc.total_amount=null,
1658 wfc.currency_code=null
1659 WHERE wfc.freight_cost_id = l_wfc_detail_ids(i)
1660 and wfc.line_type_code='SUMMARY'
1661 and wfc.delivery_detail_id is null;
1662 END IF;
1663
1664
1665
1666 END IF;
1667
1668 END IF;
1669
1670
1671 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Delete_Main_Records');
1672 IF (l_warning_count > 0)
1673 THEN
1674 x_return_status:=WSH_UTIL_CORE.G_RET_STS_WARNING;
1675 END IF;
1676 EXCEPTION
1677 WHEN others THEN
1678 ROLLBACK TO Delete_Main_Records;
1679 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1680 FTE_FREIGHT_PRICING_UTIL.set_exception('Delete_Main_Records',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_others');
1681 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
1682 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Delete_Main_Records');
1683
1684
1685 END Delete_Main_Records;
1686
1687
1688
1689 PROCEDURE Is_Pricing_Required(
1690 p_trip_id IN NUMBER,
1691 p_move_id IN NUMBER,
1692 x_reprice_flag OUT NOCOPY VARCHAR2,
1693 x_return_status OUT NOCOPY VARCHAR2) IS
1694
1695 CURSOR get_dlegs_to_be_priced(c_trip_id IN NUMBER) IS
1696 SELECT dl.delivery_leg_id
1697 FROM wsh_delivery_legs dl ,
1698 wsh_trip_stops s
1699 WHERE dl.pick_up_stop_id = s.stop_id
1700 and s.trip_id=c_trip_id
1701 and ( NVL(dl.reprice_required,'Y') = 'Y');
1702
1703
1704 CURSOR get_move_dlegs_to_be_priced(c_move_id IN NUMBER) IS
1705 SELECT dl.delivery_leg_id
1706 FROM wsh_delivery_legs dl ,
1707 wsh_trip_stops s
1708 WHERE
1709 s.trip_id IN (
1710 SELECT m.trip_id from fte_trip_moves m where m.move_id=c_move_id
1711 )
1712 and dl.pick_up_stop_id = s.stop_id
1713 and ( NVL(dl.reprice_required,'Y') = 'Y');
1714
1715
1716 l_temp NUMBER;
1717
1718 l_return_status VARCHAR2(1);
1719
1720 l_log_level NUMBER := FTE_FREIGHT_PRICING_UTIL.G_DBG;
1721
1722 l_warning_count NUMBER:=0;
1723 BEGIN
1724
1725 FTE_FREIGHT_PRICING_UTIL.reset_dbg_vars;
1726 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,'Is_Pricing_Required','start');
1727
1728 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1729
1730
1731 IF (p_move_id IS NOT NULL)
1732 THEN
1733 OPEN get_move_dlegs_to_be_priced(p_move_id);
1734 FETCH get_move_dlegs_to_be_priced INTO l_temp;
1735 IF (get_move_dlegs_to_be_priced%FOUND)
1736 THEN
1737 x_reprice_flag:='Y';
1738 ELSE
1739 x_reprice_flag:='N';
1740 END IF;
1741 CLOSE get_move_dlegs_to_be_priced;
1742
1743
1744 ELSE
1745
1746 OPEN get_dlegs_to_be_priced(p_trip_id);
1747 FETCH get_dlegs_to_be_priced INTO l_temp;
1748 IF (get_dlegs_to_be_priced%FOUND)
1749 THEN
1750 x_reprice_flag:='Y';
1751 ELSE
1752 x_reprice_flag:='N';
1753 END IF;
1754 CLOSE get_dlegs_to_be_priced;
1755
1756 END IF;
1757
1758 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Is_Pricing_Required');
1759
1760 IF (l_warning_count > 0)
1761 THEN
1762 x_return_status:=WSH_UTIL_CORE.G_RET_STS_WARNING;
1763 END IF;
1764 EXCEPTION
1765 WHEN others THEN
1766 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1767 FTE_FREIGHT_PRICING_UTIL.set_exception('Is_Pricing_Required',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_others');
1768 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
1769 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Is_Pricing_Required');
1770
1771
1772 END Is_Pricing_Required;
1773
1774
1775 PROCEDURE Handle_CM_Discount_Variant(
1776 p_fte_move_id IN NUMBER,
1777 p_trip_index_start IN NUMBER,
1778 p_trip_index_end IN NUMBER,
1779 p_output_type IN VARCHAR2,
1780 x_output_cost_tab OUT NOCOPY FTE_FREIGHT_PRICING.Freight_Cost_Temp_Tab_Type ,
1781 x_return_status OUT NOCOPY VARCHAR2) IS
1782
1783 --All the trip charges in the move will be stored in this table
1784 l_store_trip_rows FTE_TL_CACHE.TL_trip_output_tab_type;
1785
1786 --All the stops of the diff trips in the move will be stored here
1787 l_store_trip_stop_rows FTE_TL_CACHE.TL_trip_stop_output_tab_type;
1788
1789 --All the total charges for the trips are stored in this table, indexed by trip id
1790 l_trip_charges_tab dbms_utility.number_array;
1791
1792 --This table indexed by the trip_id has a reference to the stops for that trip in l_store_trip_stop_rows
1793 l_stop_references dbms_utility.number_array;
1794 l_total_trip_charge NUMBER;
1795 l_non_cm_charge NUMBER;
1796 l_cm_charge NUMBER;
1797 i NUMBER;
1798 j NUMBER;
1799 k NUMBER;
1800 l_cm_discount NUMBER;
1801 l_effective_discount NUMBER;
1802 l_cost_allocation_parameters FTE_TL_COST_ALLOCATION.TL_allocation_params_rec_type;
1803 l_output_cost_tab FTE_FREIGHT_PRICING.Freight_Cost_Temp_Tab_Type;
1804
1805 l_trip_charges_rec FTE_TL_CACHE.TL_trip_output_rec_type;
1806 l_empty_trip_charges_rec FTE_TL_CACHE.TL_trip_output_rec_type;
1807 l_stop_charges_tab FTE_TL_CACHE.TL_trip_stop_output_tab_type;
1808
1809
1810 l_return_status VARCHAR2(1);
1811
1812 l_log_level NUMBER := FTE_FREIGHT_PRICING_UTIL.G_DBG;
1813
1814 l_warning_count NUMBER:=0;
1815 BEGIN
1816
1817 FTE_FREIGHT_PRICING_UTIL.reset_dbg_vars;
1818 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,'Handle_CM_Discount_Variant','start');
1819
1820 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1821
1822
1823 FTE_TL_COST_ALLOCATION.Get_Cost_Allocation_Parameters(
1824 x_cost_allocation_parameters=> l_cost_allocation_parameters,
1825 x_return_status=> l_return_status);
1826
1827 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
1828 THEN
1829 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
1830 THEN
1831 raise FTE_FREIGHT_PRICING_UTIL.g_tl_get_cost_alloc_param_fail;
1832 END IF;
1833 END IF;
1834
1835 l_cost_allocation_parameters.output_type:=p_output_type;
1836
1837 l_cm_charge:=0;
1838 l_non_cm_charge:=0;
1839
1840
1841 j:=1;
1842 i:=p_trip_index_start;
1843 WHILE(i <= p_trip_index_end)
1844 LOOP
1845
1846 FTE_TL_CORE.tl_core (
1847 p_trip_rec => FTE_TL_CACHE.g_tl_trip_rows(i),
1848 p_stop_tab => FTE_TL_CACHE.g_tl_trip_stop_rows,
1849 p_carrier_pref => FTE_TL_CACHE.g_tl_carrier_pref_rows(i),
1850 x_trip_charges_rec => l_trip_charges_rec,
1851 x_stop_charges_tab => l_stop_charges_tab,
1852 x_return_status => l_return_status );
1853
1854 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
1855 THEN
1856 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
1857 THEN
1858 FTE_FREIGHT_PRICING_UTIL.setmsg (
1859 p_api=>'Handle_CM_Discount_Variant',
1860 p_exc=>'g_tl_core_fail',
1861 p_trip_id=>FTE_TL_CACHE.g_tl_trip_rows(i).trip_id);
1862
1863 raise FTE_FREIGHT_PRICING_UTIL.g_tl_core_fail;
1864 END IF;
1865 END IF;
1866
1867
1868
1869 IF(l_trip_charges_rec.cm_discount_percent IS NOT NULL)
1870 THEN
1871 IF (l_trip_charges_rec.cm_discount_percent <> 0)
1872 THEN
1873 l_cm_discount:=l_trip_charges_rec.cm_discount_percent;
1874 END IF;
1875 END IF;
1876
1877 --Get total cost of the trip(without cm disc)
1878
1879 FTE_TL_COST_ALLOCATION.Get_Total_Trip_Cost(
1880 p_trip_index=> i,
1881 p_trip_charges_rec=> l_trip_charges_rec,
1882 p_stop_charges_tab=> l_stop_charges_tab,
1883 x_charge=>l_total_trip_charge,
1884 x_return_status=> l_return_status);
1885
1886 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
1887 THEN
1888 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
1889 THEN
1890 raise FTE_FREIGHT_PRICING_UTIL.g_tl_get_tot_trp_cost_fail;
1891 END IF;
1892 END IF;
1893
1894
1895
1896 --Store total cost of trip
1897 l_trip_charges_tab(l_trip_charges_rec.trip_id):=l_total_trip_charge;
1898
1899 --Store trip level charges
1900 l_store_trip_rows(i):=l_trip_charges_rec;
1901
1902 --Store refernce to stop level charges
1903 l_stop_references(l_trip_charges_rec.trip_id):=j;
1904
1905 --Store stop level charges
1906
1907 k:=l_stop_charges_tab.FIRST;
1908 WHILE(k IS NOT NULL)
1909 LOOP
1910 l_store_trip_stop_rows(j):=l_stop_charges_tab(k);
1911 j:=j+1;
1912
1913 k:=l_stop_charges_tab.NEXT(k);
1914 END LOOP;
1915
1916 IF(FTE_TL_CACHE.g_tl_trip_rows(i).dead_head='N')
1917 THEN
1918 l_non_cm_charge:=l_non_cm_charge+
1919 l_trip_charges_tab(FTE_TL_CACHE.g_tl_trip_rows(i).trip_id);
1920 END IF;
1921 l_cm_charge:=l_cm_charge
1922 +l_trip_charges_tab(FTE_TL_CACHE.g_tl_trip_rows(i).trip_id);
1923
1924 i:=i+1;
1925 END LOOP;
1926
1927 --Calculate effective discount
1928
1929 --l_cm_discount 0..100
1930 --l_effective_discount 0..1 . 0% effective discount => l_effective_discount=1
1931 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'cm charge:'||l_cm_charge||' non cm charge '||l_non_cm_charge);
1932 IF(l_non_cm_charge <> 0)
1933 THEN
1934 l_effective_discount:=((100-l_cm_discount)*l_cm_charge)/(l_non_cm_charge*100);
1935 ELSE
1936 l_effective_discount:=1-0;
1937
1938 END IF;
1939
1940 --If the cm discount is not beneficial (makes it more expensive) then dont apply it
1941
1942 --IF ((l_effective_discount > 1) OR (l_effective_discount < 0))
1943 --THEN
1944 -- l_effective_discount:=1;
1945 --END IF;
1946
1947 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Effective discount'||l_effective_discount);
1948
1949 --Allocate discount to non-dead head trips
1950
1951 i:=l_store_trip_rows.FIRST;
1952 WHILE(i IS NOT NULL)
1953 LOOP
1954 IF((FTE_TL_CACHE.g_tl_trip_rows(i).dead_head='N') AND
1955 NOT((FTE_TL_CACHE.g_tl_carrier_pref_rows(i).cm_first_load_discount_flag='N')AND (i= l_store_trip_rows.FIRST)))
1956 THEN
1957 FTE_TL_COST_ALLOCATION.Scale_Trip_Charges(
1958 p_discount=> l_effective_discount,
1959 x_trip_charges_rec=> l_store_trip_rows(i),
1960 x_return_status=> l_return_status);
1961 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
1962 THEN
1963 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
1964 THEN
1965 raise FTE_FREIGHT_PRICING_UTIL.g_tl_scale_trip_charges_fail;
1966 END IF;
1967 END IF;
1968
1969
1970
1971 l_trip_charges_rec:=l_store_trip_rows(i);
1972 l_trip_charges_rec.cm_discount_value:=
1973 l_trip_charges_tab(l_trip_charges_rec.trip_id)*(1-l_effective_discount);
1974 l_stop_charges_tab.DELETE;
1975 j:=l_stop_references(l_trip_charges_rec.trip_id);
1976 WHILE( j IS NOT NULL )
1977 LOOP
1978 IF (l_store_trip_stop_rows(j).trip_id <> l_trip_charges_rec.trip_id)
1979 THEN
1980 EXIT;
1981 END IF;
1982
1983 FTE_TL_COST_ALLOCATION.Scale_Stop_Charges(
1984 p_discount=> l_effective_discount,
1985 x_stop_charges_rec=> l_store_trip_stop_rows(j),
1986 x_return_status=> l_return_status);
1987
1988 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
1989 THEN
1990 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
1991 THEN
1992 raise FTE_FREIGHT_PRICING_UTIL.g_tl_scale_stop_charges_fail;
1993 END IF;
1994 END IF;
1995
1996 l_stop_charges_tab(l_store_trip_stop_rows(j).stop_id):=l_store_trip_stop_rows(j);
1997 j:=l_store_trip_stop_rows.NEXT(j);
1998
1999 END LOOP;
2000
2001 IF (p_output_type='M')
2002 THEN
2003 Delete_Main_Records(
2004 p_trip_id=>l_trip_charges_rec.trip_id,
2005 x_return_status=>l_return_status);
2006 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
2007 THEN
2008 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
2009 THEN
2010 raise FTE_FREIGHT_PRICING_UTIL.g_tl_delete_main_rec_fail;
2011 END IF;
2012 END IF;
2013
2014
2015 END IF;
2016
2017 FTE_TL_COST_ALLOCATION.TL_COST_ALLOCATION(
2018 p_trip_index=> i,
2019 p_trip_charges_rec=> l_trip_charges_rec,
2020 p_stop_charges_tab=> l_stop_charges_tab,
2021 p_cost_allocation_parameters=> l_cost_allocation_parameters,
2022 x_output_cost_tab=> l_output_cost_tab,
2023 x_return_status=> l_return_status);
2024
2025 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
2026 THEN
2027 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
2028 THEN
2029
2030 FTE_FREIGHT_PRICING_UTIL.setmsg (
2031 p_api=>'Handle_CM_Discount_Variant',
2032 p_exc=>'g_tl_cost_allocation_fail',
2033 p_trip_id=>l_trip_charges_rec.trip_id);
2034
2035 raise FTE_FREIGHT_PRICING_UTIL.g_tl_cost_allocation_fail;
2036 END IF;
2037 END IF;
2038
2039 IF (p_output_type='M')
2040 THEN
2041
2042 Update_Pricing_Required_Flag(
2043 p_trip_id=>l_trip_charges_rec.trip_id,
2044 x_return_status =>l_return_status);
2045
2046 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
2047 THEN
2048 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
2049 THEN
2050 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_WRN,
2051 'Failied to set reprice required flag for TRIP ID:'||l_trip_charges_rec.trip_id);
2052 END IF;
2053 END IF;
2054 END IF;
2055
2056
2057 l_stop_charges_tab.DELETE;
2058 l_trip_charges_rec:=l_empty_trip_charges_rec;
2059 END IF;
2060
2061
2062 i:=l_store_trip_rows.NEXT(i);
2063 END LOOP;
2064
2065 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Handle_CM_Discount_Variant');
2066
2067
2068 IF (l_warning_count > 0)
2069 THEN
2070 x_return_status:=WSH_UTIL_CORE.G_RET_STS_WARNING;
2071 END IF;
2072 EXCEPTION
2073
2074
2075 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_delete_main_rec_fail THEN
2076 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2077 FTE_FREIGHT_PRICING_UTIL.set_exception('Handle_CM_Discount_Variant',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_delete_main_rec_fail');
2078 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Handle_CM_Discount_Variant');
2079
2080
2081 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_get_cost_alloc_param_fail THEN
2082 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2083 FTE_FREIGHT_PRICING_UTIL.set_exception('Handle_CM_Discount_Variant',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_get_cost_alloc_param_fail');
2084 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Handle_CM_Discount_Variant');
2085
2086
2087 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_cost_allocation_fail THEN
2088 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2089 FTE_FREIGHT_PRICING_UTIL.set_exception('Handle_CM_Discount_Variant',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_cost_allocation_fail');
2090 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Handle_CM_Discount_Variant');
2091
2092
2093 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_scale_trip_charges_fail THEN
2094 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2095 FTE_FREIGHT_PRICING_UTIL.set_exception('Handle_CM_Discount_Variant',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_scale_trip_charges_fail');
2096 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Handle_CM_Discount_Variant');
2097
2098
2099 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_scale_stop_charges_fail THEN
2100 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2101 FTE_FREIGHT_PRICING_UTIL.set_exception('Handle_CM_Discount_Variant',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_scale_stop_charges_fail');
2102 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Handle_CM_Discount_Variant');
2103
2104
2105 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_get_tot_trp_cost_fail THEN
2106 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2107 FTE_FREIGHT_PRICING_UTIL.set_exception('Handle_CM_Discount_Variant',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_get_tot_trp_cost_fail');
2108 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Handle_CM_Discount_Variant');
2109
2110
2111 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_core_fail THEN
2112 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2113 FTE_FREIGHT_PRICING_UTIL.set_exception('Handle_CM_Discount_Variant',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_core_fail');
2114 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Handle_CM_Discount_Variant');
2115
2116 WHEN others THEN
2117 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2118 FTE_FREIGHT_PRICING_UTIL.set_exception('Handle_CM_Discount_Variant',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_others');
2119 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
2120 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Handle_CM_Discount_Variant');
2121
2122
2123 END Handle_CM_Discount_Variant;
2124
2125
2126 PROCEDURE Handle_CM_Rate_Variant(
2127 p_fte_move_id IN NUMBER,
2128 p_trip_index_start IN NUMBER,
2129 p_trip_index_end IN NUMBER,
2130 p_output_type IN VARCHAR2,
2131 x_output_cost_tab OUT NOCOPY FTE_FREIGHT_PRICING.Freight_Cost_Temp_Tab_Type ,
2132 x_return_status OUT NOCOPY VARCHAR2) IS
2133
2134 i NUMBER;
2135 j NUMBER;
2136 k NUMBER;
2137 l_total_trip_charge NUMBER;
2138 l_factor NUMBER;
2139
2140 l_cost_allocation_parameters FTE_TL_COST_ALLOCATION.TL_allocation_params_rec_type;
2141 l_trip_charges_rec FTE_TL_CACHE.TL_trip_output_rec_type;
2142 l_empty_trip_charges_rec FTE_TL_CACHE.TL_trip_output_rec_type;
2143 l_stop_charges_tab FTE_TL_CACHE.TL_trip_stop_output_tab_type;
2144
2145 --All the trip charges in the move will be stored in this table
2146 l_store_trip_rows FTE_TL_CACHE.TL_trip_output_tab_type;
2147
2148 --All the stops of the diff trips in the move will be stored here
2149 l_store_trip_stop_rows FTE_TL_CACHE.TL_trip_stop_output_tab_type;
2150
2151 --All the total charges for the trips are stored in this table, indexed by trip id
2152 l_trip_charges_tab dbms_utility.number_array;
2153
2154 --This table indexed by the trip_id has a reference to the stops for that trip in l_store_trip_stop_rows
2155 l_stop_references dbms_utility.number_array;
2156
2157 l_dead_head_charge NUMBER;
2158 l_load_charge NUMBER;
2159
2160 l_return_status VARCHAR2(1);
2161
2162 l_log_level NUMBER := FTE_FREIGHT_PRICING_UTIL.G_DBG;
2163
2164 l_warning_count NUMBER:=0;
2165 BEGIN
2166
2167 FTE_FREIGHT_PRICING_UTIL.reset_dbg_vars;
2168 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,'Handle_CM_Rate_Variant','start');
2169
2170 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2171
2172
2173 FTE_TL_COST_ALLOCATION.Get_Cost_Allocation_Parameters(
2174 x_cost_allocation_parameters=> l_cost_allocation_parameters,
2175 x_return_status=> l_return_status);
2176
2177 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
2178 THEN
2179 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
2180 THEN
2181 raise FTE_FREIGHT_PRICING_UTIL.g_tl_get_cost_alloc_param_fail;
2182 END IF;
2183 END IF;
2184
2185 l_cost_allocation_parameters.output_type:=p_output_type;
2186
2187 l_dead_head_charge:=0;
2188 l_load_charge:=0;
2189
2190 j:=1;
2191 i:=p_trip_index_start;
2192 WHILE(i <= p_trip_index_end)
2193 LOOP
2194
2195 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'trip index:'||i||'stop ref'||FTE_TL_CACHE.g_tl_trip_rows(i).stop_reference);
2196
2197 FTE_TL_CORE.tl_core (
2198 p_trip_rec => FTE_TL_CACHE.g_tl_trip_rows(i),
2199 p_stop_tab => FTE_TL_CACHE.g_tl_trip_stop_rows,
2200 p_carrier_pref => FTE_TL_CACHE.g_tl_carrier_pref_rows(i),
2201 x_trip_charges_rec => l_trip_charges_rec,
2202 x_stop_charges_tab => l_stop_charges_tab,
2203 x_return_status => l_return_status );
2204
2205 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
2206 THEN
2207 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
2208 THEN
2209
2210 FTE_FREIGHT_PRICING_UTIL.setmsg (
2211 p_api=>'Handle_CM_Rate_Variant',
2212 p_exc=>'g_tl_core_fail',
2213 p_trip_id=>FTE_TL_CACHE.g_tl_trip_rows(i).trip_id);
2214
2215 raise FTE_FREIGHT_PRICING_UTIL.g_tl_core_fail;
2216 END IF;
2217 END IF;
2218
2219 --Get total cost of the trip(without cm disc)
2220
2221 FTE_TL_COST_ALLOCATION.Get_Total_Trip_Cost(
2222 p_trip_index=> i,
2223 p_trip_charges_rec=> l_trip_charges_rec,
2224 p_stop_charges_tab=> l_stop_charges_tab,
2225 x_charge=>l_total_trip_charge,
2226 x_return_status=> l_return_status);
2227
2228 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
2229 THEN
2230 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
2231 THEN
2232 raise FTE_FREIGHT_PRICING_UTIL.g_tl_get_tot_trp_cost_fail;
2233 END IF;
2234 END IF;
2235
2236 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Total cost for trip '||
2237 l_trip_charges_rec.trip_id||' : '||l_total_trip_charge);
2238
2239 --Store total cost of trip
2240 l_trip_charges_tab(l_trip_charges_rec.trip_id):=l_total_trip_charge;
2241
2242 --Store trip level charges
2243 l_store_trip_rows(i):=l_trip_charges_rec;
2244
2245 --Store refernce to stop level charges
2246 l_stop_references(l_trip_charges_rec.trip_id):=j;
2247
2248 --Store stop level charges
2249
2250 k:=l_stop_charges_tab.FIRST;
2251 WHILE(k IS NOT NULL)
2252 LOOP
2253 l_store_trip_stop_rows(j):=l_stop_charges_tab(k);
2254 j:=j+1;
2255
2256 k:=l_stop_charges_tab.NEXT(k);
2257 END LOOP;
2258
2259 IF(FTE_TL_CACHE.g_tl_trip_rows(i).dead_head='Y')
2260 THEN
2261 l_dead_head_charge:=l_dead_head_charge+
2262 l_trip_charges_tab(FTE_TL_CACHE.g_tl_trip_rows(i).trip_id);
2263 ELSE
2264 l_load_charge:=l_load_charge
2265 +l_trip_charges_tab(FTE_TL_CACHE.g_tl_trip_rows(i).trip_id);
2266
2267 END IF;
2268
2269
2270
2271 i:=i+1;
2272 END LOOP;
2273
2274
2275
2276 -- Allocate costs to non dead heads
2277
2278 i:=l_store_trip_rows.FIRST;
2279 WHILE(i IS NOT NULL)
2280 LOOP
2281 IF(FTE_TL_CACHE.g_tl_trip_rows(i).dead_head='N')
2282 THEN
2283 IF (l_load_charge <> 0)
2284 THEN
2285 l_factor:=1+l_dead_head_charge/l_load_charge;
2286 ELSE
2287 l_factor:=1;
2288 END IF;
2289
2290
2291 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Effective discount (Rate)trip: '||
2292 FTE_TL_CACHE.g_tl_trip_rows(i).trip_id||' : '||l_factor);
2293
2294 FTE_TL_COST_ALLOCATION.Scale_Trip_Charges(
2295 p_discount=> l_factor,
2296 x_trip_charges_rec=> l_store_trip_rows(i),
2297 x_return_status=> l_return_status);
2298
2299 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
2300 THEN
2301 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
2302 THEN
2303 raise FTE_FREIGHT_PRICING_UTIL.g_tl_scale_trip_charges_fail;
2304 END IF;
2305 END IF;
2306
2307
2308 l_trip_charges_rec:=l_store_trip_rows(i);
2309
2310 l_stop_charges_tab.DELETE;
2311 j:=l_stop_references(l_trip_charges_rec.trip_id);
2312 WHILE( j IS NOT NULL )
2313 LOOP
2314 IF (l_store_trip_stop_rows(j).trip_id <> l_trip_charges_rec.trip_id)
2315 THEN
2316 EXIT;
2317 END IF;
2318
2319 FTE_TL_COST_ALLOCATION.Scale_Stop_Charges(
2320 p_discount=> l_factor,
2321 x_stop_charges_rec=> l_store_trip_stop_rows(j),
2322 x_return_status=> l_return_status);
2323
2324 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
2325 THEN
2326 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
2327 THEN
2328 raise FTE_FREIGHT_PRICING_UTIL.g_tl_scale_stop_charges_fail;
2329 END IF;
2330 END IF;
2331
2332 l_stop_charges_tab(l_store_trip_stop_rows(j).stop_id):=l_store_trip_stop_rows(j);
2333 j:=l_store_trip_stop_rows.NEXT(j);
2334
2335 END LOOP;
2336
2337 IF (p_output_type='M')
2338 THEN
2339 Delete_Main_Records(
2340 p_trip_id=>l_trip_charges_rec.trip_id,
2341 x_return_status=>l_return_status);
2342 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
2343 THEN
2344 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
2345 THEN
2346 raise FTE_FREIGHT_PRICING_UTIL.g_tl_delete_main_rec_fail;
2347 END IF;
2348 END IF;
2349
2350
2351 END IF;
2352
2353
2354 FTE_TL_COST_ALLOCATION.TL_COST_ALLOCATION(
2355 p_trip_index=> i,
2356 p_trip_charges_rec=> l_trip_charges_rec,
2357 p_stop_charges_tab=> l_stop_charges_tab,
2358 p_cost_allocation_parameters=> l_cost_allocation_parameters,
2359 x_output_cost_tab=> x_output_cost_tab,
2360 x_return_status=> l_return_status);
2361
2362 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
2363 THEN
2364 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
2365 THEN
2366
2367 FTE_FREIGHT_PRICING_UTIL.setmsg (
2368 p_api=>'Handle_CM_Rate_Variant',
2369 p_exc=>'g_tl_cost_allocation_fail',
2370 p_trip_id=>l_trip_charges_rec.trip_id);
2371
2372 raise FTE_FREIGHT_PRICING_UTIL.g_tl_cost_allocation_fail;
2373 END IF;
2374 END IF;
2375
2376 IF (p_output_type='M')
2377 THEN
2378
2379 Update_Pricing_Required_Flag(
2380 p_trip_id=>l_trip_charges_rec.trip_id,
2381 x_return_status =>l_return_status);
2382
2383 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
2384 THEN
2385 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
2386 THEN
2387 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_WRN,
2388 'Failied to set reprice required flag for TRIP ID:'||l_trip_charges_rec.trip_id);
2389 END IF;
2390 END IF;
2391 END IF;
2392
2393
2394
2395 l_stop_charges_tab.DELETE;
2396 l_trip_charges_rec:=l_empty_trip_charges_rec;
2397 END IF;
2398 i:=l_store_trip_rows.NEXT(i);
2399
2400
2401 END LOOP;
2402
2403 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Handle_CM_Rate_Variant');
2404
2405 IF (l_warning_count > 0)
2406 THEN
2407 x_return_status:=WSH_UTIL_CORE.G_RET_STS_WARNING;
2408 END IF;
2409 EXCEPTION
2410
2411
2412 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_get_tot_trp_cost_fail THEN
2413 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2414 FTE_FREIGHT_PRICING_UTIL.set_exception('Handle_CM_Rate_Variant',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_get_tot_trp_cost_fail');
2415 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Handle_CM_Rate_Variant');
2416
2417 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_scale_trip_charges_fail THEN
2418 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2419 FTE_FREIGHT_PRICING_UTIL.set_exception('Handle_CM_Rate_Variant',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_scale_trip_charges_fail');
2420 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Handle_CM_Rate_Variant');
2421
2422 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_scale_stop_charges_fail THEN
2423 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2424 FTE_FREIGHT_PRICING_UTIL.set_exception('Handle_CM_Rate_Variant',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_scale_stop_charges_fail');
2425 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Handle_CM_Rate_Variant');
2426
2427
2428 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_delete_main_rec_fail THEN
2429 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2430 FTE_FREIGHT_PRICING_UTIL.set_exception('Handle_CM_Rate_Variant',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_delete_main_rec_fail');
2431 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Handle_CM_Rate_Variant');
2432
2433
2434 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_get_cost_alloc_param_fail THEN
2435 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2436 FTE_FREIGHT_PRICING_UTIL.set_exception('Handle_CM_Rate_Variant',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_get_cost_alloc_param_fail');
2437 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Handle_CM_Rate_Variant');
2438
2439
2440 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_cost_allocation_fail THEN
2441 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2442 FTE_FREIGHT_PRICING_UTIL.set_exception('Handle_CM_Rate_Variant',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_cost_allocation_fail');
2443 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Handle_CM_Rate_Variant');
2444
2445
2446 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_core_fail THEN
2447 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2448 FTE_FREIGHT_PRICING_UTIL.set_exception('Handle_CM_Rate_Variant',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_core_fail');
2449 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Handle_CM_Rate_Variant');
2450
2451 WHEN others THEN
2452 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2453 FTE_FREIGHT_PRICING_UTIL.set_exception('Handle_CM_Rate_Variant',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_others');
2454 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
2455 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Handle_CM_Rate_Variant');
2456
2457
2458 END Handle_CM_Rate_Variant;
2459
2460
2461 PROCEDURE TL_Rate_Move (
2462 p_fte_move_id IN NUMBER ,
2463 p_output_type IN VARCHAR2,
2464 x_output_cost_tab OUT NOCOPY FTE_FREIGHT_PRICING.Freight_Cost_Temp_Tab_Type ,
2465 x_return_status OUT NOCOPY VARCHAR2) IS
2466
2467
2468 l_return_status VARCHAR2(1);
2469
2470 l_log_level NUMBER := FTE_FREIGHT_PRICING_UTIL.G_DBG;
2471 l_trip_index_start NUMBER;
2472 l_trip_index_end NUMBER;
2473
2474
2475 l_warning_count NUMBER:=0;
2476 BEGIN
2477
2478 FTE_FREIGHT_PRICING_UTIL.reset_dbg_vars;
2479 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,'TL_Rate_Move','start');
2480
2481 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2482
2483
2484 IF (FTE_TL_CACHE.g_tl_trip_rows.LAST IS NULL)
2485 THEN
2486 l_trip_index_start:=1;
2487 ELSE
2488 l_trip_index_start:=FTE_TL_CACHE.g_tl_trip_rows.LAST+1;
2489 END IF;
2490
2491 FTE_TL_CACHE.TL_Build_Cache_For_Move(
2492 p_fte_move_id=> p_fte_move_id,
2493 x_return_status=> l_return_status);
2494
2495
2496
2497 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
2498 THEN
2499 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
2500 THEN
2501
2502
2503 FTE_FREIGHT_PRICING_UTIL.setmsg (
2504 p_api=>'TL_Rate_Move',
2505 p_exc=>'g_tl_build_cache_move_fail',
2506 p_move_id=>p_fte_move_id);
2507
2508 raise FTE_FREIGHT_PRICING_UTIL.g_tl_build_cache_move_fail;
2509 END IF;
2510 END IF;
2511
2512 l_trip_index_end:=FTE_TL_CACHE.g_tl_trip_rows.LAST;
2513 FTE_TL_CACHE.Display_Cache;
2514
2515 IF (FTE_TL_CACHE.g_tl_carrier_pref_rows(l_trip_index_start).cm_rate_variant='RATE')
2516 THEN
2517 Handle_CM_Rate_Variant(
2518 p_fte_move_id=> p_fte_move_id,
2519 p_trip_index_start=> l_trip_index_start,
2520 p_trip_index_end=> l_trip_index_end,
2521 p_output_type=> p_output_type,
2522 x_output_cost_tab=> x_output_cost_tab,
2523 x_return_status=> l_return_status);
2524
2525 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
2526 THEN
2527 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
2528 THEN
2529 raise FTE_FREIGHT_PRICING_UTIL.g_tl_handle_cm_rate_var_fail;
2530 END IF;
2531 END IF;
2532
2533
2534 ELSIF (FTE_TL_CACHE.g_tl_carrier_pref_rows(l_trip_index_start).cm_rate_variant='DISCOUNT')
2535 THEN
2536 Handle_CM_Discount_Variant(
2537 p_fte_move_id=> p_fte_move_id,
2538 p_trip_index_start=> l_trip_index_start,
2539 p_trip_index_end=> l_trip_index_end,
2540 p_output_type=> p_output_type,
2541 x_output_cost_tab=> x_output_cost_tab,
2542 x_return_status=> l_return_status);
2543
2544 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
2545 THEN
2546 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
2547 THEN
2548
2549 raise FTE_FREIGHT_PRICING_UTIL.g_tl_handle_cm_disc_var_fail;
2550 END IF;
2551 END IF;
2552
2553
2554 ELSE
2555 FTE_FREIGHT_PRICING_UTIL.setmsg (
2556 p_api=>'TL_Rate_Move',
2557 p_exc=>'g_tl_car_no_cm_rate_variant',
2558 p_carrier_id=>FTE_TL_CACHE.g_tl_carrier_pref_rows(l_trip_index_start).carrier_id);
2559
2560
2561 raise FTE_FREIGHT_PRICING_UTIL.g_tl_car_no_cm_rate_variant;
2562
2563 END IF;
2564
2565 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'TL_Rate_Move');
2566
2567 IF (l_warning_count > 0)
2568 THEN
2569 x_return_status:=WSH_UTIL_CORE.G_RET_STS_WARNING;
2570 END IF;
2571 EXCEPTION
2572
2573
2574
2575 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_car_no_cm_rate_variant THEN
2576 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2577 FTE_FREIGHT_PRICING_UTIL.set_exception('TL_Rate_Move',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_car_no_cm_rate_variant');
2578 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'TL_Rate_Move');
2579
2580
2581 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_handle_cm_disc_var_fail THEN
2582 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2583 FTE_FREIGHT_PRICING_UTIL.set_exception('TL_Rate_Move',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_handle_cm_disc_var_fail');
2584 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'TL_Rate_Move');
2585
2586 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_handle_cm_rate_var_fail THEN
2587 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2588 FTE_FREIGHT_PRICING_UTIL.set_exception('TL_Rate_Move',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_handle_cm_rate_var_fail');
2589 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'TL_Rate_Move');
2590
2591 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_build_cache_move_fail THEN
2592 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2593 FTE_FREIGHT_PRICING_UTIL.set_exception('TL_Rate_Move',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_build_cache_move_fail');
2594 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'TL_Rate_Move');
2595
2596 WHEN others THEN
2597 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2598 FTE_FREIGHT_PRICING_UTIL.set_exception('TL_Rate_Move',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_others');
2599 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
2600 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'TL_Rate_Move');
2601
2602
2603 END TL_Rate_Move;
2604
2605
2606
2607 PROCEDURE TL_Rate_Cached_Trip (
2608 p_trip_index IN NUMBER ,
2609 p_output_type IN VARCHAR2,
2610 p_request_id IN NUMBER DEFAULT NULL,
2611 x_output_cost_tab OUT NOCOPY FTE_FREIGHT_PRICING.Freight_Cost_Temp_Tab_Type ,
2612 x_return_status OUT NOCOPY VARCHAR2) IS
2613
2614
2615 l_trip_charges_rec FTE_TL_CACHE.TL_trip_output_rec_type;
2616 l_stop_charges_tab FTE_TL_CACHE.TL_trip_stop_output_tab_type;
2617 l_cost_allocation_parameters FTE_TL_COST_ALLOCATION.TL_allocation_params_rec_type;
2618 l_output_cost_tab FTE_FREIGHT_PRICING.Freight_Cost_Temp_Tab_Type;
2619
2620 l_return_status VARCHAR2(1);
2621
2622 l_log_level NUMBER := FTE_FREIGHT_PRICING_UTIL.G_DBG;
2623
2624 l_warning_count NUMBER:=0;
2625 BEGIN
2626
2627 FTE_FREIGHT_PRICING_UTIL.reset_dbg_vars;
2628 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,'TL_Rate_Cached_Trip','start');
2629
2630 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2631
2632
2633 FTE_TL_CORE.tl_core (
2634 p_trip_rec => FTE_TL_CACHE.g_tl_trip_rows(p_trip_index),
2635 p_stop_tab => FTE_TL_CACHE.g_tl_trip_stop_rows,
2636 p_carrier_pref => FTE_TL_CACHE.g_tl_carrier_pref_rows(p_trip_index),
2637 x_trip_charges_rec => l_trip_charges_rec,
2638 x_stop_charges_tab => l_stop_charges_tab,
2639 x_return_status => l_return_status );
2640
2641 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
2642 THEN
2643 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
2644 THEN
2645
2646 --FTE_FREIGHT_PRICING_UTIL.setmsg (
2647 -- p_api=>'TL_Rate_Cached_Trip',
2648 -- p_exc=>'g_tl_core_fail',
2649 -- p_trip_id=>FTE_TL_CACHE.g_tl_trip_rows(p_trip_index).trip_id);
2650
2651 raise FTE_FREIGHT_PRICING_UTIL.g_tl_core_fail;
2652 END IF;
2653 END IF;
2654
2655
2656 FTE_TL_COST_ALLOCATION.Get_Cost_Allocation_Parameters(
2657 x_cost_allocation_parameters=> l_cost_allocation_parameters,
2658 x_return_status=> l_return_status);
2659
2660 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
2661 THEN
2662 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
2663 THEN
2664 raise FTE_FREIGHT_PRICING_UTIL.g_tl_get_cost_alloc_param_fail;
2665 END IF;
2666 END IF;
2667
2668 l_cost_allocation_parameters.comparison_request_id := p_request_id;
2669 l_cost_allocation_parameters.output_type := p_output_type;
2670
2671 FTE_TL_COST_ALLOCATION.TL_COST_ALLOCATION(
2672 p_trip_index=> p_trip_index,
2673 p_trip_charges_rec=> l_trip_charges_rec,
2674 p_stop_charges_tab=> l_stop_charges_tab,
2675 p_cost_allocation_parameters=> l_cost_allocation_parameters,
2676 x_output_cost_tab=> l_output_cost_tab,
2677 x_return_status=> l_return_status);
2678
2679 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
2680 THEN
2681 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
2682 THEN
2683
2684 FTE_FREIGHT_PRICING_UTIL.setmsg (
2685 p_api=>'TL_Rate_Cached_Trip',
2686 p_exc=>'g_tl_cost_allocation_fail',
2687 p_trip_id=>l_trip_charges_rec.trip_id);
2688
2689 raise FTE_FREIGHT_PRICING_UTIL.g_tl_cost_allocation_fail;
2690 END IF;
2691 END IF;
2692
2693 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'TL_Rate_Cached_Trip');
2694
2695 IF (l_warning_count > 0)
2696 THEN
2697 x_return_status:=WSH_UTIL_CORE.G_RET_STS_WARNING;
2698 END IF;
2699 EXCEPTION
2700
2701 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_get_cost_alloc_param_fail THEN
2702 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2703 FTE_FREIGHT_PRICING_UTIL.set_exception('TL_Rate_Cached_Trip',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_get_cost_alloc_param_fail');
2704 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'TL_Rate_Cached_Trip');
2705
2706
2707 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_cost_allocation_fail THEN
2708 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2709 FTE_FREIGHT_PRICING_UTIL.set_exception('TL_Rate_Cached_Trip',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_cost_allocation_fail');
2710 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'TL_Rate_Cached_Trip');
2711
2712 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_core_fail THEN
2713 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2714 FTE_FREIGHT_PRICING_UTIL.set_exception('TL_Rate_Cached_Trip',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_core_fail');
2715 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'TL_Rate_Cached_Trip');
2716 WHEN others THEN
2717 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2718 FTE_FREIGHT_PRICING_UTIL.set_exception('TL_Rate_Cached_Trip',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_others');
2719 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
2720 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'TL_Rate_Cached_Trip');
2721
2722
2723 END TL_Rate_Cached_Trip;
2724
2725
2726
2727
2728
2729
2730 PROCEDURE TL_Rate_Cached_Trip_Multiple (
2731 p_start_trip_index IN NUMBER ,
2732 p_end_trip_index IN NUMBER ,
2733 p_output_type IN VARCHAR2,
2734 p_request_id IN NUMBER DEFAULT NULL,
2735 p_allocate_flag IN VARCHAR2,
2736 x_lane_sched_sum_rows OUT NOCOPY dbms_utility.number_array,
2737 x_lane_sched_curr_rows OUT NOCOPY dbms_utility.name_array,
2738 x_output_cost_tab OUT NOCOPY FTE_FREIGHT_PRICING.Freight_Cost_Temp_Tab_Type ,
2739 x_exceptions_tab OUT NOCOPY FTE_TL_CORE.tl_exceptions_tab_type,
2740 x_trip_charges_tab OUT NOCOPY FTE_TL_CACHE.TL_trip_output_tab_type ,
2741 x_stop_charges_tab OUT NOCOPY FTE_TL_CACHE.TL_trip_stop_output_tab_type,
2742 x_return_status OUT NOCOPY VARCHAR2) IS
2743
2744
2745 i NUMBER;
2746 j NUMBER;
2747 k NUMBER;
2748 l_exceptions_tab FTE_TL_CORE.tl_exceptions_tab_type;
2749 l_trip_charges_tab FTE_TL_CACHE.TL_TRIP_OUTPUT_TAB_TYPE;
2750 l_trip_charges_rec FTE_TL_CACHE.TL_trip_output_rec_type;
2751 l_stop_charges_tab FTE_TL_CACHE.TL_trip_stop_output_tab_type;
2752 l_stop_charges_one_trip_tab FTE_TL_CACHE.TL_trip_stop_output_tab_type;
2753 l_cost_allocation_parameters FTE_TL_COST_ALLOCATION.TL_allocation_params_rec_type;
2754
2755
2756 l_return_status VARCHAR2(1);
2757
2758 l_log_level NUMBER := FTE_FREIGHT_PRICING_UTIL.G_DBG;
2759
2760 l_warning_count NUMBER:=0;
2761 BEGIN
2762
2763 FTE_FREIGHT_PRICING_UTIL.reset_dbg_vars;
2764 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,'TL_Rate_Cached_Trip_Multiple','start');
2765
2766 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2767
2768
2769 l_exceptions_tab.delete;
2770
2771 FTE_TL_CORE.TL_Core_Multiple (
2772 p_start_trip_index=>p_start_trip_index,
2773 p_end_trip_index=>p_end_trip_index,
2774 p_trip_tab=>FTE_TL_CACHE.g_tl_trip_rows,
2775 p_stop_tab=>FTE_TL_CACHE.g_tl_trip_stop_rows,
2776 p_carrier_pref_tab=>FTE_TL_CACHE.g_tl_carrier_pref_rows,
2777 x_trip_charges_tab=>l_trip_charges_tab,
2778 x_stop_charges_tab=>l_stop_charges_tab,
2779 x_exceptions_tab=>l_exceptions_tab,
2780 x_return_status => l_return_status );
2781
2782
2783 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
2784 THEN
2785 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
2786 THEN
2787
2788 --FTE_FREIGHT_PRICING_UTIL.setmsg (
2789 -- p_api=>'TL_Rate_Cached_Trip',
2790 -- p_exc=>'g_tl_core_fail',
2791 -- p_trip_id=>FTE_TL_CACHE.g_tl_trip_rows(p_trip_index).trip_id);
2792
2793 raise FTE_FREIGHT_PRICING_UTIL.g_tl_core_fail;
2794 END IF;
2795 END IF;
2796
2797
2798
2799
2800
2801
2802
2803 FTE_TL_COST_ALLOCATION.Get_Cost_Allocation_Parameters(
2804 x_cost_allocation_parameters=> l_cost_allocation_parameters,
2805 x_return_status=> l_return_status);
2806
2807 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
2808 THEN
2809 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
2810 THEN
2811 raise FTE_FREIGHT_PRICING_UTIL.g_tl_get_cost_alloc_param_fail;
2812 END IF;
2813 END IF;
2814
2815 l_cost_allocation_parameters.comparison_request_id := p_request_id;
2816 l_cost_allocation_parameters.output_type := p_output_type;
2817
2818
2819 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG, 'Begining cost allocation index:'||p_start_trip_index||' to:'||p_end_trip_index);
2820
2821 k:=p_start_trip_index;
2822 i:=p_start_trip_index;
2823 WHILE(i<=p_end_trip_index)
2824 LOOP
2825
2826
2827 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG, 'Allocating index:'||i);
2828 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG, 'Allocating index:'||i||'check_tlqp_ouputfail:'||l_exceptions_tab(i).check_tlqp_ouputfail||'check_qp_ipl_fail:'||l_exceptions_tab(i).check_qp_ipl_fail );
2829 IF ((l_exceptions_tab(i).check_tlqp_ouputfail='N') AND (l_exceptions_tab(i).check_qp_ipl_fail='N'))
2830 THEN
2831
2832 l_stop_charges_one_trip_tab.delete;
2833 j:=l_trip_charges_tab(i).stop_charge_reference;
2834 WHILE((FTE_TL_CACHE.g_tl_trip_rows(i).number_of_stops > 0) AND
2835 (j<(FTE_TL_CACHE.g_tl_trip_rows(i).number_of_stops+l_trip_charges_tab(i).stop_charge_reference)))
2836 LOOP
2837
2838 l_stop_charges_one_trip_tab(j):=l_stop_charges_tab(j);
2839 j:=j+1;
2840 END LOOP;
2841
2842
2843 x_lane_sched_sum_rows(k):=NULL;
2844
2845 --Populate summary rates
2846
2847 FTE_TL_COST_ALLOCATION.Get_Total_Trip_Cost(
2848 p_trip_index=> i,
2849 p_trip_charges_rec=> l_trip_charges_tab(i),
2850 p_stop_charges_tab=> l_stop_charges_one_trip_tab,
2851 x_charge=>x_lane_sched_sum_rows(k),
2852 x_return_status=> l_return_status);
2853
2854 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
2855 THEN
2856 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
2857 THEN
2858 raise FTE_FREIGHT_PRICING_UTIL.g_tl_get_tot_trp_cost_fail;
2859 END IF;
2860 END IF;
2861
2862 l_trip_charges_tab(i).total_trip_rate:=x_lane_sched_sum_rows(k);
2863 x_lane_sched_curr_rows(k):=FTE_TL_CACHE.g_tl_carrier_pref_rows(i).currency;
2864
2865
2866 IF (p_allocate_flag = 'Y')
2867 THEN
2868
2869
2870 FTE_TL_COST_ALLOCATION.TL_COST_ALLOCATION(
2871 p_trip_index=> i,
2872 p_trip_charges_rec=> l_trip_charges_tab(i),
2873 p_stop_charges_tab=> l_stop_charges_one_trip_tab,
2874 p_cost_allocation_parameters=> l_cost_allocation_parameters,
2875 x_output_cost_tab=> x_output_cost_tab,
2876 x_return_status=> l_return_status);
2877
2878 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
2879 THEN
2880 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
2881 THEN
2882
2883 --FTE_FREIGHT_PRICING_UTIL.setmsg (
2884 -- p_api=>'TL_Rate_Cached_Trip',
2885 -- p_exc=>'g_tl_cost_allocation_fail',
2886 -- p_trip_id=>l_trip_charges_rec.trip_id);
2887
2888 l_exceptions_tab(i).allocation_failed:='Y';
2889
2890 --raise FTE_FREIGHT_PRICING_UTIL.g_tl_cost_allocation_fail;
2891 END IF;
2892 END IF;
2893
2894 IF (x_output_cost_tab.FIRST IS NOT NULL)
2895 THEN
2896
2897 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'src line rates DBG-1:'||
2898 x_output_cost_tab(x_output_cost_tab.FIRST).lane_id||':'||
2899 x_output_cost_tab(x_output_cost_tab.FIRST).vehicle_type_id||':'||
2900 x_output_cost_tab(x_output_cost_tab.FIRST).delivery_leg_id||':'||
2901 x_output_cost_tab(x_output_cost_tab.FIRST).delivery_detail_id||':'||
2902 x_output_cost_tab(x_output_cost_tab.FIRST).freight_cost_id);
2903
2904 END IF;
2905 ELSE
2906
2907 x_trip_charges_tab:=l_trip_charges_tab;
2908 x_stop_charges_tab:= l_stop_charges_tab;
2909
2910
2911 END IF;
2912 ELSE
2913
2914 x_lane_sched_sum_rows(k):=NULL;
2915 x_lane_sched_curr_rows(k):=NULL;
2916
2917 END IF;
2918
2919 k:=k+1;
2920 i:=i+1;
2921 END LOOP;
2922
2923
2924 x_exceptions_tab:=l_exceptions_tab;
2925 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'TL_Rate_Cached_Trip_Multiple');
2926
2927 IF (l_warning_count > 0)
2928 THEN
2929 x_return_status:=WSH_UTIL_CORE.G_RET_STS_WARNING;
2930 END IF;
2931 EXCEPTION
2932
2933 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_get_cost_alloc_param_fail THEN
2934 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2935 FTE_FREIGHT_PRICING_UTIL.set_exception('TL_Rate_Cached_Trip_Multiple',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_get_cost_alloc_param_fail');
2936 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'TL_Rate_Cached_Trip_Multiple');
2937
2938
2939 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_cost_allocation_fail THEN
2940 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2941 FTE_FREIGHT_PRICING_UTIL.set_exception('TL_Rate_Cached_Trip_Multiple',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_cost_allocation_fail');
2942 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'TL_Rate_Cached_Trip_Multiple');
2943
2944 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_core_fail THEN
2945 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2946 FTE_FREIGHT_PRICING_UTIL.set_exception('TL_Rate_Cached_Trip_Multiple',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_core_fail');
2947 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'TL_Rate_Cached_Trip_Multiple');
2948 WHEN others THEN
2949 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2950 FTE_FREIGHT_PRICING_UTIL.set_exception('TL_Rate_Cached_Trip_Multiple',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_others');
2951 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
2952 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'TL_Rate_Cached_Trip_Multiple');
2953
2954
2955 END TL_Rate_Cached_Trip_Multiple;
2956
2957
2958
2959
2960
2961
2962
2963
2964
2965
2966
2967
2968 PROCEDURE TL_Rate_Trip (
2969 p_trip_id IN NUMBER ,
2970 p_output_type IN VARCHAR2,
2971 p_check_reprice_flag IN VARCHAR2 DEFAULT 'N',
2972 x_output_cost_tab OUT NOCOPY FTE_FREIGHT_PRICING.Freight_Cost_Temp_Tab_Type ,
2973 x_return_status OUT NOCOPY VARCHAR2) IS
2974
2975 CURSOR get_move_id(c_trip_id IN NUMBER) IS
2976 SELECT tm.move_id
2977 FROM FTE_TRIP_MOVES tm
2978 WHERE tm.trip_id=c_trip_id;
2979
2980 l_move_id NUMBER;
2981 l_output_cost_tab FTE_FREIGHT_PRICING.Freight_Cost_Temp_Tab_Type;
2982 l_reprice_flag VARCHAR2(1);
2983 l_term_manifest_flag VARCHAR2(1);
2984
2985 l_return_status VARCHAR2(1);
2986
2987 l_log_level NUMBER := FTE_FREIGHT_PRICING_UTIL.G_DBG;
2988
2989 l_warning_count NUMBER:=0;
2990 BEGIN
2991
2992 SAVEPOINT TL_Rate_Trip;
2993
2994 FTE_FREIGHT_PRICING_UTIL.reset_dbg_vars;
2995 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,'TL_Rate_Trip','start');
2996
2997 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2998
2999 FTE_TL_CACHE.Delete_Cache(x_return_status=>l_return_status);
3000 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
3001 THEN
3002 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
3003 THEN
3004 raise FTE_FREIGHT_PRICING_UTIL.g_tl_delete_cache_fail;
3005 END IF;
3006 END IF;
3007
3008
3009 OPEN get_move_id(p_trip_id);
3010 FETCH get_move_id INTO l_move_id;
3011 IF(get_move_id%FOUND)
3012 THEN
3013 CLOSE get_move_id;
3014
3015 Check_Freight_Terms_Manifest(
3016 p_trip_id=> NULL,
3017 p_move_id=> l_move_id,
3018 x_term_manifest_flag=> l_term_manifest_flag,
3019 x_return_status => l_return_status);
3020
3021 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
3022 THEN
3023 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
3024 THEN
3025 raise FTE_FREIGHT_PRICING_UTIL.g_tl_check_freight_term_fail;
3026 END IF;
3027 END IF;
3028
3029
3030 IF (l_term_manifest_flag='Y')
3031 THEN
3032 --move should not be rated because of freight terms,manifesting
3033 --messages have been set already
3034
3035 l_warning_count:=l_warning_count+1;
3036
3037 ELSE
3038 --No freight term/manifesting issues
3039
3040 IF (p_check_reprice_flag = 'Y')
3041 THEN
3042 Is_Pricing_Required(
3043 p_trip_id => NULL,
3044 p_move_id => l_move_id,
3045 x_reprice_flag => l_reprice_flag,
3046 x_return_status => l_return_status);
3047
3048 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
3049 THEN
3050 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
3051 THEN
3052 raise FTE_FREIGHT_PRICING_UTIL.g_tl_is_pricing_required_fail;
3053 END IF;
3054 END IF;
3055 ELSE
3056 l_reprice_flag:='Y';
3057 END IF;
3058
3059 IF (l_reprice_flag = 'Y')
3060 THEN
3061
3062 TL_Rate_Move(
3063 p_fte_move_id=> l_move_id,
3064 p_output_type=> p_output_type,
3065 x_output_cost_tab=> l_output_cost_tab,
3066 x_return_status => l_return_status);
3067
3068
3069 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
3070 THEN
3071 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
3072 THEN
3073 raise FTE_FREIGHT_PRICING_UTIL.g_tl_rate_move_fail;
3074 END IF;
3075 END IF;
3076 ELSE
3077 l_warning_count:=l_warning_count+1;
3078 FTE_FREIGHT_PRICING_UTIL.setmsg(
3079 p_api => 'TL_Rate_Trip',
3080 p_exc => ' ',
3081 --p_msg_name => 'FTE_PRICING_NOT_REQUIRED',
3082 p_msg_type => 'W');
3083
3084 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_WRN,
3085 ' Not rating move:'||l_move_id||' because of reprice flag ');
3086 END IF;
3087 END IF;
3088
3089 ELSE
3090 CLOSE get_move_id;
3091
3092 Check_Freight_Terms_Manifest(
3093 p_trip_id=> p_trip_id,
3094 p_move_id=> NULL,
3095 x_term_manifest_flag=> l_term_manifest_flag,
3096 x_return_status => l_return_status);
3097
3098 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
3099 THEN
3100 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
3101 THEN
3102 raise FTE_FREIGHT_PRICING_UTIL.g_tl_check_freight_term_fail;
3103 END IF;
3104 END IF;
3105
3106
3107 IF (l_term_manifest_flag='Y')
3108 THEN
3109 --trip should not be rated because of freight terms,manifesting
3110 --messages have been set already
3111
3112 l_warning_count:=l_warning_count+1;
3113
3114 ELSE
3115 --No freight term/manifesting issues
3116
3117 IF (p_check_reprice_flag = 'Y')
3118 THEN
3119 Is_Pricing_Required(
3120 p_trip_id => p_trip_id,
3121 p_move_id => NULL,
3122 x_reprice_flag => l_reprice_flag,
3123 x_return_status => l_return_status);
3124
3125 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
3126 THEN
3127 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
3128 THEN
3129 raise FTE_FREIGHT_PRICING_UTIL.g_tl_is_pricing_required_fail;
3130 END IF;
3131 END IF;
3132 ELSE
3133 l_reprice_flag:='Y';
3134 END IF;
3135
3136 IF (l_reprice_flag = 'Y')
3137 THEN
3138 IF (p_output_type='M')
3139 THEN
3140
3141 Delete_Main_Records(
3142 p_trip_id => p_trip_id,
3143 x_return_status =>l_return_status);
3144
3145 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
3146 THEN
3147 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
3148 THEN
3149 raise FTE_FREIGHT_PRICING_UTIL.g_tl_delete_main_rec_fail;
3150 END IF;
3151 END IF;
3152 END IF;
3153
3154
3155 FTE_TL_CACHE.TL_Build_Cache_For_Trip(
3156 p_wsh_trip_id => p_trip_id,
3157 x_return_status => l_return_status);
3158
3159 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
3160 THEN
3161 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
3162 THEN
3163 FTE_FREIGHT_PRICING_UTIL.setmsg (
3164 p_api=>'TL_Rate_Trip',
3165 p_exc=>'g_tl_build_cache_trp_fail',
3166 p_trip_id=>p_trip_id);
3167
3168 raise FTE_FREIGHT_PRICING_UTIL.g_tl_build_cache_trp_fail;
3169 END IF;
3170 END IF;
3171
3172 FTE_TL_CACHE.Display_Cache;
3173
3174 TL_Rate_Cached_Trip (
3175 p_trip_index=> FTE_TL_CACHE.g_tl_trip_rows.LAST,
3176 p_output_type=> p_output_type,
3177 x_output_cost_tab=> x_output_cost_tab,
3178 x_return_status=> l_return_status
3179 );
3180
3181 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
3182 THEN
3183 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
3184 THEN
3185 raise FTE_FREIGHT_PRICING_UTIL.g_tl_rate_cached_trip_fail;
3186 END IF;
3187 END IF;
3188
3189 IF (p_output_type='M')
3190 THEN
3191
3192 Update_Pricing_Required_Flag(
3193 p_trip_id=>p_trip_id,
3194 x_return_status =>l_return_status);
3195
3196 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
3197 THEN
3198 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
3199 THEN
3200 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_WRN,
3201 'Failied to set reprice required flag for TRIP ID:'||p_trip_id);
3202 END IF;
3203 END IF;
3204 END IF;
3205
3206 ELSE
3207 -- Pricing not required
3208 l_warning_count:=l_warning_count+1;
3209 FTE_FREIGHT_PRICING_UTIL.setmsg(
3210 p_api => 'TL_Rate_Trip',
3211 p_exc => ' ',
3212 --p_msg_name => 'FTE_PRICING_NOT_REQUIRED',
3213 p_msg_type => 'W');
3214
3215 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_WRN,'Pricing was not required for TRIP ID:'||p_trip_id);
3216
3217 END IF;
3218 END IF;
3219
3220 END IF;
3221
3222 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'TL_Rate_Trip');
3223
3224 IF (l_warning_count > 0)
3225 THEN
3226 x_return_status:=WSH_UTIL_CORE.G_RET_STS_WARNING;
3227 END IF;
3228 EXCEPTION
3229
3230
3231
3232 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_check_freight_term_fail THEN
3233 ROLLBACK TO TL_Rate_Trip;
3234 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3235 FTE_FREIGHT_PRICING_UTIL.set_exception('TL_Rate_Trip',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_check_freight_term_fail');
3236 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'TL_Rate_Trip');
3237
3238
3239 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_delete_cache_fail THEN
3240 ROLLBACK TO TL_Rate_Trip;
3241 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3242 FTE_FREIGHT_PRICING_UTIL.set_exception('TL_Rate_Trip',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_delete_cache_fail');
3243 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'TL_Rate_Trip');
3244
3245
3246 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_delete_main_rec_fail THEN
3247 ROLLBACK TO TL_Rate_Trip;
3248 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3249 FTE_FREIGHT_PRICING_UTIL.set_exception('TL_Rate_Trip',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_delete_main_rec_fail');
3250 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'TL_Rate_Trip');
3251
3252 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_is_pricing_required_fail THEN
3253 ROLLBACK TO TL_Rate_Trip;
3254 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3255 FTE_FREIGHT_PRICING_UTIL.set_exception('TL_Rate_Trip',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_is_pricing_required_fail');
3256 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'TL_Rate_Trip');
3257
3258 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_rate_move_fail THEN
3259 ROLLBACK TO TL_Rate_Trip;
3260 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3261 FTE_FREIGHT_PRICING_UTIL.set_exception('TL_Rate_Trip',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_rate_move_fail');
3262 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'TL_Rate_Trip');
3263
3264 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_build_cache_trp_fail THEN
3265 ROLLBACK TO TL_Rate_Trip;
3266 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3267 FTE_FREIGHT_PRICING_UTIL.set_exception('TL_Rate_Trip',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_build_cache_trp_fail');
3268 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'TL_Rate_Trip');
3269
3270 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_rate_cached_trip_fail THEN
3271 ROLLBACK TO TL_Rate_Trip;
3272 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3273 FTE_FREIGHT_PRICING_UTIL.set_exception('TL_Rate_Trip',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_rate_cached_trip_fail');
3274 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'TL_Rate_Trip');
3275
3276 WHEN others THEN
3277 ROLLBACK TO TL_Rate_Trip;
3278 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3279 FTE_FREIGHT_PRICING_UTIL.set_exception('TL_Rate_Trip',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_others');
3280 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
3281 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'TL_Rate_Trip');
3282
3283 END TL_Rate_Trip;
3284
3285
3286 PROCEDURE Populate_Summary_Rates(
3287 p_lane_rows IN dbms_utility.number_array ,
3288 p_schedule_rows IN dbms_utility.number_array,
3289 p_vehicle_rows IN dbms_utility.number_array,
3290 p_start_trip_index IN NUMBER,
3291 p_end_trip_index IN NUMBER,
3292 p_exceptions_tab IN FTE_TL_CORE.tl_exceptions_tab_type,
3293 x_lane_sched_sum_rows IN OUT NOCOPY dbms_utility.number_array,
3294 x_lane_sched_curr_rows IN OUT NOCOPY dbms_utility.name_array,
3295 x_summary_cache_ref OUT NOCOPY dbms_utility.number_array,
3296 x_return_status OUT NOCOPY Varchar2) IS
3297
3298 l_lane_sched_sum_rows dbms_utility.number_array;
3299 l_lane_sched_curr_rows dbms_utility.name_array;
3300 i NUMBER;
3301 j NUMBER;
3302 l_lane_cached VARCHAR2(1);
3303
3304
3305
3306 l_return_status VARCHAR2(1);
3307
3308 l_log_level NUMBER := FTE_FREIGHT_PRICING_UTIL.G_DBG;
3309
3310 l_warning_count NUMBER:=0;
3311 BEGIN
3312
3313
3314 FTE_FREIGHT_PRICING_UTIL.reset_dbg_vars;
3315 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,'Populate_Summary_Rates','start');
3316
3317 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3318
3319
3320
3321
3322 i:=p_lane_rows.FIRST;
3323 j:=p_start_trip_index;
3324 WHILE(i IS NOT NULL)
3325 LOOP
3326 l_lane_sched_sum_rows(i):=NULL;
3327 l_lane_sched_curr_rows(i):=NULL;
3328 x_summary_cache_ref(i):=NULL;
3329
3330 IF( (j IS NOT NULL) AND (p_end_trip_index IS NOT NULL) AND (j<= p_end_trip_index))
3331 THEN
3332
3333 l_lane_cached:='N';
3334
3335 --Get rates only if that lane/schedule was cached
3336
3337 IF( (FTE_TL_CACHE.g_tl_trip_rows(j).schedule_id IS NOT NULL)
3338 AND (FTE_TL_CACHE.g_tl_trip_rows(j).schedule_id= p_schedule_rows(i)) AND (p_vehicle_rows(i)=FTE_TL_CACHE.g_tl_trip_rows(j).vehicle_type ))
3339 THEN
3340 l_lane_cached:='Y';
3341
3342 ELSIF ((FTE_TL_CACHE.g_tl_trip_rows(j).lane_id IS NOT NULL)
3343 AND (FTE_TL_CACHE.g_tl_trip_rows(j).lane_id= p_lane_rows(i)) AND (p_vehicle_rows(i)=FTE_TL_CACHE.g_tl_trip_rows(j).vehicle_type ))
3344 THEN
3345 l_lane_cached:='Y';
3346
3347 END IF;
3348
3349
3350 IF (l_lane_cached= 'Y')
3351 THEN
3352
3353 IF (
3354 p_exceptions_tab(j).check_tlqp_ouputfail='N' AND
3355 p_exceptions_tab(j).check_qp_ipl_fail='N' AND
3356 p_exceptions_tab(j).not_on_pl_flag='N' AND
3357 p_exceptions_tab(j).price_req_failed='N' AND
3358 p_exceptions_tab(j).allocation_failed='N'
3359 )
3360 THEN
3361
3362 l_lane_sched_sum_rows(i):=x_lane_sched_sum_rows(j);
3363 l_lane_sched_curr_rows(i):=x_lane_sched_curr_rows(j);
3364 x_summary_cache_ref(i):=j;
3365
3366 END IF;
3367
3368
3369
3370 j:=j+1;
3371 END IF;
3372
3373 END IF;
3374
3375 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'TL Lane:'||p_lane_rows(i)||'TL Schedule:'||p_schedule_rows(i)||'TL Vehicle:'||p_vehicle_rows(i)||' Rate:'||l_lane_sched_sum_rows(i)||l_lane_sched_curr_rows(i)|| ' Index:'||i);
3376
3377 i:=p_lane_rows.NEXT(i);
3378 END LOOP;
3379
3380
3381 x_lane_sched_sum_rows.DELETE;
3382 x_lane_sched_curr_rows.DELETE;
3383 x_lane_sched_sum_rows:=l_lane_sched_sum_rows;
3384 x_lane_sched_curr_rows:=l_lane_sched_curr_rows;
3385
3386 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Populate_Summary_Rates');
3387
3388 IF (l_warning_count > 0)
3389 THEN
3390 x_return_status:=WSH_UTIL_CORE.G_RET_STS_WARNING;
3391 END IF;
3392 EXCEPTION
3393
3394
3395 WHEN others THEN
3396 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3397 FTE_FREIGHT_PRICING_UTIL.set_exception('Populate_Summary_Rates',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_others');
3398 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
3399 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Populate_Summary_Rates');
3400
3401
3402 END Populate_Summary_Rates;
3403
3404
3405
3406
3407 PROCEDURE TL_TRIP_PRICE_COMPARE(
3408 p_wsh_trip_id IN Number ,
3409 p_lane_rows IN dbms_utility.number_array ,
3410 p_schedule_rows IN dbms_utility.number_array,
3411 p_vehicle_rows IN dbms_utility.number_array,
3412 x_request_id IN OUT NOCOPY NUMBER,
3413 x_lane_sched_sum_rows OUT NOCOPY dbms_utility.number_array,
3414 x_lane_sched_curr_rows OUT NOCOPY dbms_utility.name_array,
3415 x_return_status OUT NOCOPY Varchar2) IS
3416
3417 l_output_tab FTE_FREIGHT_PRICING.Freight_Cost_Temp_Tab_Type;
3418 i NUMBER;
3419 l_request_id NUMBER;
3420 l_exceptions_tab FTE_TL_CORE.tl_exceptions_tab_type;
3421 l_trip_charges_tab FTE_TL_CACHE.TL_trip_output_tab_type;
3422 l_stop_charges_tab FTE_TL_CACHE.TL_trip_stop_output_tab_type;
3423 l_summary_cache_ref dbms_utility.number_array;
3424
3425 CURSOR c_get_req_id IS
3426 SELECT fte_pricing_comp_request_s.nextval
3427 FROM sys.dual;
3428
3429 CURSOR c_trip_sum(c_trip_id NUMBER, c_request_id NUMBER,
3430 c_lane_id NUMBER, c_schedule_id NUMBER)
3431 IS
3432 SELECT ffct.total_amount, ffct.currency_code
3433 FROM fte_freight_costs_temp ffct, wsh_freight_cost_types wfct
3434 WHERE ffct.freight_cost_type_id = wfct.freight_cost_type_id
3435 AND wfct.name='SUMMARY'
3436 AND ffct.trip_id = c_trip_id
3437 AND ffct.comparison_request_id = c_request_id
3438 AND nvl(lane_id,-1) = nvl(c_lane_id,-1)
3439 AND nvl(schedule_id, -1) = nvl(c_schedule_id,-1);
3440
3441
3442 l_term_manifest_flag VARCHAR2(1);
3443 l_return_status VARCHAR2(1);
3444
3445 l_log_level NUMBER := FTE_FREIGHT_PRICING_UTIL.G_DBG;
3446
3447 l_warning_count NUMBER:=0;
3448 BEGIN
3449
3450 FTE_FREIGHT_PRICING_UTIL.reset_dbg_vars;
3451 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,'TL_Trip_Price_Compare','start');
3452
3453 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3454
3455
3456 Check_Freight_Terms_Manifest(
3457 p_trip_id=> p_wsh_trip_id,
3458 p_move_id=> NULL,
3459 x_term_manifest_flag=> l_term_manifest_flag,
3460 x_return_status => l_return_status);
3461
3462 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
3463 THEN
3464 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
3465 THEN
3466 raise FTE_FREIGHT_PRICING_UTIL.g_tl_check_freight_term_fail;
3467 END IF;
3468 END IF;
3469
3470
3471 IF (l_term_manifest_flag='Y')
3472 THEN
3473 --trip should not be rated because of freight terms,manifesting
3474 --messages have been set already
3475
3476 IF (x_request_id IS NULL OR x_request_id = 0) THEN
3477
3478 OPEN c_get_req_id;
3479 FETCH c_get_req_id INTO l_request_id;
3480 CLOSE c_get_req_id;
3481
3482 x_request_id := l_request_id;
3483
3484 ELSE
3485 l_request_id := x_request_id;
3486 END IF;
3487
3488 l_warning_count:=l_warning_count+1;
3489
3490 ELSE
3491 --No freight term/manifesting issues
3492
3493
3494 FTE_TL_CACHE.Delete_Cache(x_return_status=>l_return_status);
3495
3496 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
3497 THEN
3498 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
3499 THEN
3500 raise FTE_FREIGHT_PRICING_UTIL.g_tl_delete_cache_fail;
3501 END IF;
3502 END IF;
3503
3504 FTE_TL_CACHE.TL_BUILD_CACHE_FOR_TRP_COMPARE(
3505 p_wsh_trip_id => p_wsh_trip_id,
3506 p_lane_rows=> p_lane_rows,
3507 p_schedule_rows=> p_schedule_rows,
3508 p_vehicle_rows =>p_vehicle_rows,
3509 x_return_status=> l_return_status);
3510
3511 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
3512 THEN
3513 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
3514 THEN
3515 FTE_FREIGHT_PRICING_UTIL.setmsg (
3516 p_api=>'TL_Trip_Price_Compare',
3517 p_msg_type=>'W',
3518 p_exc=>'g_tl_bld_cache_trp_cmp_fail',
3519 p_trip_id=>p_wsh_trip_id);
3520
3521 --raise FTE_FREIGHT_PRICING_UTIL.g_tl_bld_cache_trp_cmp_fail;
3522 END IF;
3523 END IF;
3524
3525
3526 FTE_TL_CACHE.Display_Cache;
3527
3528 IF (x_request_id IS NULL OR x_request_id = 0) THEN
3529
3530 OPEN c_get_req_id;
3531 FETCH c_get_req_id INTO l_request_id;
3532 CLOSE c_get_req_id;
3533
3534 x_request_id := l_request_id;
3535
3536 ELSE
3537 l_request_id := x_request_id;
3538 END IF;
3539
3540
3541 fte_freight_pricing_util.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'comp request_id='||l_request_id);
3542
3543
3544 IF(FTE_TL_CACHE.g_tl_trip_rows.FIRST IS NOT NULL)
3545 THEN
3546
3547 TL_Rate_Cached_Trip_Multiple (
3548 p_start_trip_index =>FTE_TL_CACHE.g_tl_trip_rows.FIRST,
3549 p_end_trip_index =>FTE_TL_CACHE.g_tl_trip_rows.LAST,
3550 p_output_type =>'T',
3551 p_request_id =>l_request_id,
3552 p_allocate_flag=>'Y',
3553 x_lane_sched_sum_rows=>x_lane_sched_sum_rows,
3554 x_lane_sched_curr_rows=>x_lane_sched_curr_rows,
3555 x_output_cost_tab =>l_output_tab,
3556 x_exceptions_tab =>l_exceptions_tab,
3557 x_trip_charges_tab =>l_trip_charges_tab,
3558 x_stop_charges_tab =>l_stop_charges_tab,
3559 x_return_status =>l_return_status);
3560
3561 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
3562 THEN
3563 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
3564 THEN
3565 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'TL_Rate_Cached_Trip_Multiple has failed');
3566 raise FTE_FREIGHT_PRICING_UTIL.g_tl_rate_cached_trip_fail;
3567 END IF;
3568 END IF;
3569
3570
3571
3572 Populate_Summary_Rates(
3573 p_lane_rows=>p_lane_rows,
3574 p_schedule_rows=>p_schedule_rows,
3575 p_vehicle_rows=>p_vehicle_rows,
3576 p_start_trip_index=>FTE_TL_CACHE.g_tl_trip_rows.FIRST,
3577 p_end_trip_index=>FTE_TL_CACHE.g_tl_trip_rows.LAST,
3578 p_exceptions_tab=>l_exceptions_tab,
3579 x_lane_sched_sum_rows=>x_lane_sched_sum_rows,
3580 x_lane_sched_curr_rows=>x_lane_sched_curr_rows,
3581 x_summary_cache_ref=>l_summary_cache_ref,
3582 x_return_status=>l_return_status);
3583
3584 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
3585 THEN
3586 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
3587 THEN
3588 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Populate_Summary_Rates has failed');
3589 raise FTE_FREIGHT_PRICING_UTIL.g_tl_populate_summary_fail;
3590 END IF;
3591 END IF;
3592
3593
3594 END IF;
3595
3596 fte_freight_pricing_util.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Finished multiple rating and allocation..now checkign errors' );
3597
3598
3599 i:=l_exceptions_tab.FIRST;
3600 WHILE ( i IS NOT NULL)
3601 LOOP
3602
3603 fte_freight_pricing_util.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Error index :'||i );
3604
3605 IF (
3606 l_exceptions_tab(i).check_tlqp_ouputfail='Y' OR
3607 l_exceptions_tab(i).check_qp_ipl_fail='Y' OR
3608 l_exceptions_tab(i).not_on_pl_flag='Y' OR
3609 l_exceptions_tab(i).price_req_failed='Y' OR
3610 l_exceptions_tab(i).allocation_failed='Y'
3611 )
3612 THEN
3613
3614 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Rate cached trip failed for trip index:'||i);
3615 --Add warning message
3616 l_warning_count:=l_warning_count+1;
3617
3618 IF (FTE_TL_CACHE.g_tl_trip_rows(i).schedule_id IS NOT NULL)
3619 THEN
3620 --Show only generic message
3621 --FTE_FREIGHT_PRICING_UTIL.setmsg (
3622 --p_api=>'TL_Trip_Price_Compare',
3623 --p_exc=>'g_tl_trip_cmp_rate_schd_fail',
3624 --p_msg_type=>'W',
3625 --p_trip_id=> p_wsh_trip_id,
3626 --p_schedule_id=>FTE_TL_CACHE.g_tl_trip_rows(i).schedule_id);
3627 NULL;
3628
3629 ELSE
3630 --Show only generic message
3631 -- FTE_FREIGHT_PRICING_UTIL.setmsg (
3632 -- p_api=>'TL_Trip_Price_Compare',
3633 -- p_exc=>'g_tl_trip_cmp_rate_lane_fail',
3634 -- p_msg_type=>'W',
3635 -- p_trip_id=> p_wsh_trip_id,
3636 -- p_lane_id=>FTE_TL_CACHE.g_tl_trip_rows(i).lane_id);
3637
3638 NULL;
3639
3640 END IF;
3641
3642
3643
3644 END IF;
3645
3646
3647 i:=l_exceptions_tab.NEXT(i);
3648 END LOOP;
3649
3650
3651 END IF;
3652
3653
3654 --In case there was a failure populate rates/currencies with NULL
3655
3656 i := p_lane_rows.FIRST;
3657 WHILE (i IS NOT NULL)
3658 LOOP
3659 -- Note lane_id and schedule_id cannot be NOT NULL for the same index
3660 -- However, both tables must contain the same indices
3661 IF (NOT(x_lane_sched_sum_rows.EXISTS(i)))
3662 THEN
3663 x_lane_sched_sum_rows(i):=NULL;
3664 x_lane_sched_curr_rows(i):=NULL;
3665
3666 END IF;
3667
3668 fte_freight_pricing_util.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Index:'||i||' Rate:'||x_lane_sched_sum_rows(i)||' Curr:'||x_lane_sched_curr_rows(i));
3669
3670
3671 i := p_lane_rows.NEXT(i);
3672 END LOOP;
3673
3674
3675 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'TL_Trip_Price_Compare');
3676
3677 IF (l_warning_count > 0)
3678 THEN
3679 x_return_status:=WSH_UTIL_CORE.G_RET_STS_WARNING;
3680 END IF;
3681 EXCEPTION
3682
3683
3684
3685 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_populate_summary_fail THEN
3686 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3687 FTE_FREIGHT_PRICING_UTIL.set_exception('TL_Trip_Price_Compare',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_populate_summary_fail');
3688 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'TL_Trip_Price_Compare');
3689
3690
3691 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_check_freight_term_fail THEN
3692 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3693 FTE_FREIGHT_PRICING_UTIL.set_exception('TL_Trip_Price_Compare',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_check_freight_term_fail');
3694 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'TL_Trip_Price_Compare');
3695
3696
3697 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_delete_cache_fail THEN
3698 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3699 FTE_FREIGHT_PRICING_UTIL.set_exception('TL_Trip_Price_Compare',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_delete_cache_fail');
3700 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'TL_Trip_Price_Compare');
3701
3702 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_bld_cache_trp_cmp_fail THEN
3703 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3704 FTE_FREIGHT_PRICING_UTIL.set_exception('TL_Trip_Price_Compare',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_bld_cache_trp_cmp_fail');
3705 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'TL_Trip_Price_Compare');
3706
3707 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_rate_cached_trip_fail THEN
3708 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3709 FTE_FREIGHT_PRICING_UTIL.set_exception('TL_Trip_Price_Compare',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_rate_cached_trip_fail');
3710 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'TL_Trip_Price_Compare');
3711
3712
3713 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_rate_move_fail THEN
3714 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3715 FTE_FREIGHT_PRICING_UTIL.set_exception('TL_Trip_Price_Compare',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_rate_move_fail');
3716 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'TL_Trip_Price_Compare');
3717
3718
3719 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_build_cache_trp_fail THEN
3720 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3721 FTE_FREIGHT_PRICING_UTIL.set_exception('TL_Trip_Price_Compare',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_build_cache_trp_fail');
3722 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'TL_Trip_Price_Compare');
3723
3724
3725 WHEN others THEN
3726 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3727 FTE_FREIGHT_PRICING_UTIL.set_exception('TL_Trip_Price_Compare',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_others');
3728 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
3729 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'TL_Trip_Price_Compare');
3730
3731
3732
3733 END TL_TRIP_PRICE_COMPARE;
3734
3735
3736
3737 PROCEDURE TL_DELIVERY_PRICE_COMPARE(
3738 p_wsh_delivery_id IN Number ,
3739 p_lane_rows IN dbms_utility.number_array ,
3740 p_schedule_rows IN dbms_utility.number_array,
3741 p_vehicle_rows IN dbms_utility.number_array,
3742 p_dep_date IN DATE DEFAULT sysdate,
3743 p_arr_date IN DATE DEFAULT sysdate,
3744 p_pickup_location_id IN NUMBER,
3745 p_dropoff_location_id IN NUMBER,
3746 x_request_id IN OUT NOCOPY NUMBER,
3747 x_lane_sched_sum_rows OUT NOCOPY dbms_utility.number_array,
3748 x_lane_sched_curr_rows OUT NOCOPY dbms_utility.name_array,
3749 x_return_status OUT NOCOPY Varchar2)
3750 IS
3751
3752
3753 CURSOR c_get_req_id IS
3754 SELECT fte_pricing_comp_request_s.nextval
3755 FROM sys.dual;
3756
3757 CURSOR c_trip_sum(c_trip_id NUMBER, c_request_id NUMBER,
3758 c_lane_id NUMBER, c_schedule_id NUMBER)
3759 IS
3760 SELECT ffct.total_amount, ffct.currency_code
3761 FROM fte_freight_costs_temp ffct, wsh_freight_cost_types wfct
3762 WHERE ffct.freight_cost_type_id = wfct.freight_cost_type_id
3763 AND wfct.name='SUMMARY'
3764 AND ffct.trip_id = c_trip_id
3765 AND ffct.comparison_request_id = c_request_id
3766 AND nvl(lane_id,-1) = nvl(c_lane_id,-1)
3767 AND nvl(schedule_id, -1) = nvl(c_schedule_id,-1);
3768
3769
3770
3771 l_output_tab FTE_FREIGHT_PRICING.Freight_Cost_Temp_Tab_Type;
3772 i NUMBER;
3773 l_request_id NUMBER;
3774 l_exceptions_tab FTE_TL_CORE.tl_exceptions_tab_type;
3775 l_trip_charges_tab FTE_TL_CACHE.TL_trip_output_tab_type;
3776 l_stop_charges_tab FTE_TL_CACHE.TL_trip_stop_output_tab_type;
3777 l_summary_cache_ref dbms_utility.number_array;
3778
3779
3780 l_term_manifest_flag VARCHAR2(1);
3781 l_return_status VARCHAR2(1);
3782
3783 l_log_level NUMBER := FTE_FREIGHT_PRICING_UTIL.G_DBG;
3784
3785 l_warning_count NUMBER:=0;
3786 BEGIN
3787
3788 FTE_FREIGHT_PRICING_UTIL.reset_dbg_vars;
3789 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,'TL_Delivery_Price_Compare','start');
3790
3791 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3792
3793
3794 --No freight term/manifesting issues
3795
3796
3797 FTE_TL_CACHE.Delete_Cache(x_return_status=>l_return_status);
3798
3799 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
3800 THEN
3801 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
3802 THEN
3803 raise FTE_FREIGHT_PRICING_UTIL.g_tl_delete_cache_fail;
3804 END IF;
3805 END IF;
3806
3807 FTE_TL_CACHE.TL_BUILD_CACHE_FOR_DLV_COMPARE(
3808 p_wsh_delivery_id => p_wsh_delivery_id,
3809 p_lane_rows=> p_lane_rows,
3810 p_schedule_rows=> p_schedule_rows,
3811 p_vehicle_rows =>p_vehicle_rows,
3812 p_dep_date=>p_dep_date,
3813 p_arr_date=>p_arr_date,
3814 p_pickup_location_id=>p_pickup_location_id,
3815 p_dropoff_location_id=>p_dropoff_location_id,
3816 x_return_status=> l_return_status);
3817
3818 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
3819 THEN
3820 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
3821 THEN
3822
3823 --l_warning_count:=l_warning_count+1;
3824
3825 --FTE_FREIGHT_PRICING_UTIL.setmsg (
3826 -- p_api=>'TL_Delivery_Price_Compare',
3827 -- p_msg_type=>'W',
3828 -- p_exc=>'g_tl_bld_cache_trp_cmp_fail',
3829 -- p_trip_id=>p_wsh_trip_id);
3830 NULL;
3831 --raise FTE_FREIGHT_PRICING_UTIL.g_tl_bld_cache_trp_cmp_fail;
3832 END IF;
3833 END IF;
3834
3835
3836 FTE_TL_CACHE.Display_Cache;
3837
3838 IF (x_request_id IS NULL OR x_request_id = 0) THEN
3839
3840 OPEN c_get_req_id;
3841 FETCH c_get_req_id INTO l_request_id;
3842 CLOSE c_get_req_id;
3843
3844 x_request_id := l_request_id;
3845
3846 ELSE
3847 l_request_id := x_request_id;
3848 END IF;
3849
3850
3851 fte_freight_pricing_util.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'comp request_id='||l_request_id);
3852
3853
3854 IF(FTE_TL_CACHE.g_tl_trip_rows.FIRST IS NOT NULL)
3855 THEN
3856
3857 TL_Rate_Cached_Trip_Multiple (
3858 p_start_trip_index =>FTE_TL_CACHE.g_tl_trip_rows.FIRST,
3859 p_end_trip_index =>FTE_TL_CACHE.g_tl_trip_rows.LAST,
3860 p_output_type =>'T',
3861 p_request_id =>l_request_id,
3862 p_allocate_flag=>'Y',
3863 x_lane_sched_sum_rows=>x_lane_sched_sum_rows,
3864 x_lane_sched_curr_rows=>x_lane_sched_curr_rows,
3865 x_output_cost_tab =>l_output_tab,
3866 x_exceptions_tab =>l_exceptions_tab,
3867 x_trip_charges_tab =>l_trip_charges_tab,
3868 x_stop_charges_tab =>l_stop_charges_tab,
3869 x_return_status =>l_return_status);
3870
3871 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
3872 THEN
3873 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
3874 THEN
3875 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'TL_Rate_Cached_Trip_Multiple has failed');
3876 raise FTE_FREIGHT_PRICING_UTIL.g_tl_rate_cached_trip_fail;
3877 END IF;
3878 END IF;
3879
3880 Populate_Summary_Rates(
3881 p_lane_rows=>p_lane_rows,
3882 p_schedule_rows=>p_schedule_rows,
3883 p_vehicle_rows=>p_vehicle_rows,
3884 p_start_trip_index=>FTE_TL_CACHE.g_tl_trip_rows.FIRST,
3885 p_end_trip_index=>FTE_TL_CACHE.g_tl_trip_rows.LAST,
3886 p_exceptions_tab=>l_exceptions_tab,
3887 x_lane_sched_sum_rows=>x_lane_sched_sum_rows,
3888 x_lane_sched_curr_rows=>x_lane_sched_curr_rows,
3889 x_summary_cache_ref=>l_summary_cache_ref,
3890 x_return_status=>l_return_status);
3891
3892 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
3893 THEN
3894 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
3895 THEN
3896 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Populate_Summary_Rates has failed');
3897 raise FTE_FREIGHT_PRICING_UTIL.g_tl_populate_summary_fail;
3898 END IF;
3899 END IF;
3900
3901 END IF;
3902
3903 fte_freight_pricing_util.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Finished multiple rating and allocation..now checkign errors.' );
3904
3905 i:=l_exceptions_tab.FIRST;
3906
3907 WHILE ( i IS NOT NULL)
3908 LOOP
3909
3910 fte_freight_pricing_util.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Error index :'||i );
3911
3912 IF (
3913 l_exceptions_tab(i).check_tlqp_ouputfail='Y' OR
3914 l_exceptions_tab(i).check_qp_ipl_fail='Y' OR
3915 l_exceptions_tab(i).not_on_pl_flag='Y' OR
3916 l_exceptions_tab(i).price_req_failed='Y' OR
3917 l_exceptions_tab(i).allocation_failed='Y'
3918 )
3919 THEN
3920
3921 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Rate cached trip failed for trip index:'||i);
3922 --Add warning message
3923 l_warning_count:=l_warning_count+1;
3924
3925 --IF (FTE_TL_CACHE.g_tl_trip_rows(i).schedule_id IS NOT NULL)
3926 --THEN
3927
3928 -- FTE_FREIGHT_PRICING_UTIL.setmsg (
3929 -- p_api=>'TL_Delivery_Price_Compare',
3930 -- p_exc=>'g_tl_trip_cmp_rate_schd_fail',
3931 -- p_msg_type=>'W',
3932 -- p_trip_id=> p_wsh_trip_id,
3933 -- p_schedule_id=>FTE_TL_CACHE.g_tl_trip_rows(i).schedule_id);
3934
3935
3936 --ELSE
3937
3938 -- FTE_FREIGHT_PRICING_UTIL.setmsg (
3939 -- p_api=>'TL_Delivery_Price_Compare',
3940 -- p_exc=>'g_tl_trip_cmp_rate_lane_fail',
3941 -- p_msg_type=>'W',
3942 -- p_trip_id=> p_wsh_trip_id,
3943 -- p_lane_id=>FTE_TL_CACHE.g_tl_trip_rows(i).lane_id);
3944
3945 --END IF;
3946
3947
3948
3949 END IF;
3950
3951
3952 i:=l_exceptions_tab.NEXT(i);
3953 END LOOP;
3954
3955
3956
3957 --Populate null into rates/currencies in case there was a failure
3958
3959 i := p_lane_rows.FIRST;
3960 WHILE (i IS NOT NULL)
3961 LOOP
3962 -- Note lane_id and schedule_id cannot be NOT NULL for the same index
3963 -- However, both tables must contain the same indices
3964 IF (NOT(x_lane_sched_sum_rows.EXISTS(i)))
3965 THEN
3966 x_lane_sched_sum_rows(i):=NULL;
3967 x_lane_sched_curr_rows(i):=NULL;
3968
3969 END IF;
3970
3971 fte_freight_pricing_util.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Index:'||i||' Rate:'||x_lane_sched_sum_rows(i)||' Curr:'||x_lane_sched_curr_rows(i));
3972
3973
3974 i := p_lane_rows.NEXT(i);
3975 END LOOP;
3976
3977
3978 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'TL_Delivery_Price_Compare');
3979
3980 IF (l_warning_count > 0)
3981 THEN
3982 x_return_status:=WSH_UTIL_CORE.G_RET_STS_WARNING;
3983 END IF;
3984 EXCEPTION
3985
3986
3987
3988
3989 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_populate_summary_fail THEN
3990 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3991 FTE_FREIGHT_PRICING_UTIL.set_exception('TL_Delivery_Price_Compare',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_populate_summary_fail');
3992 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'TL_Delivery_Price_Compare');
3993
3994
3995 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_check_freight_term_fail THEN
3996 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3997 FTE_FREIGHT_PRICING_UTIL.set_exception('TL_Delivery_Price_Compare',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_check_freight_term_fail');
3998 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'TL_Delivery_Price_Compare');
3999
4000
4001 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_delete_cache_fail THEN
4002 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4003 FTE_FREIGHT_PRICING_UTIL.set_exception('TL_Delivery_Price_Compare',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_delete_cache_fail');
4004 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'TL_Delivery_Price_Compare');
4005
4006 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_bld_cache_trp_cmp_fail THEN
4007 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4008 FTE_FREIGHT_PRICING_UTIL.set_exception('TL_Delivery_Price_Compare',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_bld_cache_trp_cmp_fail');
4009 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'TL_Delivery_Price_Compare');
4010
4011 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_rate_cached_trip_fail THEN
4012 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4013 FTE_FREIGHT_PRICING_UTIL.set_exception('TL_Delivery_Price_Compare',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_rate_cached_trip_fail');
4014 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'TL_Delivery_Price_Compare');
4015
4016
4017 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_rate_move_fail THEN
4018 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4019 FTE_FREIGHT_PRICING_UTIL.set_exception('TL_Delivery_Price_Compare',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_rate_move_fail');
4020 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'TL_Delivery_Price_Compare');
4021
4022
4023 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_build_cache_trp_fail THEN
4024 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4025 FTE_FREIGHT_PRICING_UTIL.set_exception('TL_Delivery_Price_Compare',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_build_cache_trp_fail');
4026 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'TL_Delivery_Price_Compare');
4027
4028
4029 WHEN others THEN
4030 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4031 FTE_FREIGHT_PRICING_UTIL.set_exception('TL_Delivery_Price_Compare',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_others');
4032 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
4033 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'TL_Delivery_Price_Compare');
4034
4035
4036
4037
4038
4039 END TL_DELIVERY_PRICE_COMPARE;
4040
4041
4042
4043 PROCEDURE Get_Base_Accessory_Charges(
4044 p_trip_index IN NUMBER,
4045 p_trip_charges_rec IN FTE_TL_CACHE.TL_trip_output_rec_type ,
4046 p_stop_charges_tab IN FTE_TL_CACHE.TL_trip_stop_output_tab_type,
4047 x_base_price IN OUT NOCOPY NUMBER,
4048 x_acc_charge IN OUT NOCOPY NUMBER,
4049 x_currency IN OUT NOCOPY VARCHAR2,
4050 x_return_status OUT NOCOPY Varchar2) IS
4051
4052
4053 l_charge NUMBER;
4054 l_return_status VARCHAR2(1);
4055 l_log_level NUMBER := FTE_FREIGHT_PRICING_UTIL.G_DBG;
4056 l_warning_count NUMBER:=0;
4057
4058 BEGIN
4059
4060 FTE_FREIGHT_PRICING_UTIL.reset_dbg_vars;
4061 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,'Get_Base_Accessory_Charges','start');
4062
4063 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4064
4065 FTE_TL_COST_ALLOCATION.Get_Total_Trip_Cost(
4066 p_trip_index=>p_trip_index,
4067 p_trip_charges_rec=>p_trip_charges_rec,
4068 p_stop_charges_tab=>p_stop_charges_tab,
4069 x_charge=> l_charge,
4070 x_return_status=>l_return_status);
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_tl_get_tot_trp_cost_fail;
4077 END IF;
4078 END IF;
4079
4080 --Calculate base price
4081 x_base_price:=p_trip_charges_rec.base_dist_load_chrg+p_trip_charges_rec.base_dist_unload_chrg+
4082 p_trip_charges_rec.base_unit_chrg+p_trip_charges_rec.base_time_chrg+
4083 p_trip_charges_rec.base_flat_chrg;
4084
4085
4086 --Calculate accessory charges
4087 x_acc_charge:=l_charge - x_base_price;
4088
4089 --
4090 x_currency:=p_trip_charges_rec.currency;
4091
4092 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Get_Base_Accessory_Charges');
4093
4094 IF (l_warning_count > 0)
4095 THEN
4096 x_return_status:=WSH_UTIL_CORE.G_RET_STS_WARNING;
4097 END IF;
4098
4099 EXCEPTION
4100
4101 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_get_tot_trp_cost_fail THEN
4102 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4103 FTE_FREIGHT_PRICING_UTIL.set_exception('Get_Base_Accessory_Charges',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_get_tot_trp_cost_fail');
4104 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Get_Base_Accessory_Charges');
4105
4106 WHEN others THEN
4107 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4108 FTE_FREIGHT_PRICING_UTIL.set_exception('Get_Base_Accessory_Charges',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_others');
4109 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
4110 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Get_Base_Accessory_Charges');
4111
4112
4113 END Get_Base_Accessory_Charges;
4114
4115
4116 PROCEDURE TL_FREIGHT_ESTIMATE(
4117 p_lane_rows IN dbms_utility.number_array ,
4118 p_schedule_rows IN dbms_utility.number_array,
4119 p_vehicle_rows IN dbms_utility.number_array,
4120 p_pickup_location_id IN NUMBER,
4121 p_dropoff_location_id IN NUMBER,
4122 p_ship_date IN DATE,
4123 p_delivery_date IN DATE,
4124 p_weight IN NUMBER,
4125 p_weight_uom IN VARCHAR2,
4126 p_volume IN NUMBER,
4127 p_volume_uom IN VARCHAR2,
4128 p_distance IN NUMBER,
4129 p_distance_uom in VARCHAR2,
4130 x_lane_sched_base_rows OUT NOCOPY dbms_utility.number_array,
4131 x_lane_sched_acc_rows OUT NOCOPY dbms_utility.number_array,
4132 x_lane_sched_curr_rows OUT NOCOPY dbms_utility.name_array,
4133 x_return_status OUT NOCOPY Varchar2,
4134 --Bug 6625274
4135 p_origin_id IN NUMBER DEFAULT NULL,
4136 p_destination_id IN NUMBER DEFAULT NULL) IS
4137
4138
4139 l_trip_charges_rec FTE_TL_CACHE.TL_TRIP_OUTPUT_REC_TYPE;
4140 l_stop_charges_tab FTE_TL_CACHE.TL_TRIP_STOP_OUTPUT_TAB_TYPE;
4141 i NUMBER;
4142 j NUMBER;
4143 l_return_status VARCHAR2(1);
4144 l_rating_fail VARCHAR2(1);
4145 l_cache_fail VARCHAR2(1);
4146 l_lane_fail VARCHAR2(1);
4147 l_log_level NUMBER := FTE_FREIGHT_PRICING_UTIL.G_DBG;
4148 l_warning_count NUMBER:=0;
4149
4150 BEGIN
4151
4152 FTE_FREIGHT_PRICING_UTIL.reset_dbg_vars;
4153 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,'TL_FREIGHT_ESTIMATE','start');
4154
4155 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4156
4157 l_cache_fail:='N';
4158
4159 FTE_TL_CACHE.Delete_Cache(x_return_status=>l_return_status);
4160 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
4161 THEN
4162 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
4163 THEN
4164 raise FTE_FREIGHT_PRICING_UTIL.g_tl_delete_cache_fail;
4165 END IF;
4166 END IF;
4167
4168
4169 FTE_TL_CACHE.TL_BUILD_CACHE_FOR_ESTIMATE(
4170 p_lane_rows=>p_lane_rows,
4171 p_schedule_rows=> p_schedule_rows,
4172 p_vehicle_rows=>p_vehicle_rows,
4173 p_pickup_location_id=>p_pickup_location_id,
4174 p_dropoff_location_id=>p_dropoff_location_id,
4175 p_ship_date=>p_ship_date,
4176 p_delivery_date=>p_delivery_date,
4177 p_weight=>p_weight,
4178 p_weight_uom=>p_weight_uom,
4179 p_volume=>p_volume,
4180 p_volume_uom=>p_volume_uom ,
4181 p_distance=>p_distance,
4182 p_distance_uom=>p_distance_uom,
4183 x_return_status=>l_return_status,
4184 --Bug 6625274
4185 p_origin_id => p_origin_id,
4186 p_destination_id => p_destination_id);
4187
4188 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
4189 THEN
4190 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
4191 THEN
4192 l_cache_fail:='Y';
4193 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Failed to build cache for estimate');
4194
4195 --raise FTE_FREIGHT_PRICING_UTIL.g_tl_cache_estimate_fail;
4196 END IF;
4197 END IF;
4198
4199 FTE_TL_CACHE.Display_Cache;
4200
4201 i:=FTE_TL_CACHE.g_tl_trip_rows.FIRST;
4202 j:=p_lane_rows.FIRST;
4203 WHILE( j IS NOT NULL)
4204 LOOP
4205
4206 --Have entries in output for every lane/schedule passed in
4207
4208 x_lane_sched_base_rows(j):=NULL;
4209 x_lane_sched_acc_rows(j):=NULL;
4210 x_lane_sched_curr_rows(j):=NULL;
4211
4212 l_lane_fail:='Y';
4213
4214 --Get rates only if that lane/schedule was cached
4215
4216 IF( (i IS NOT NULL) AND (FTE_TL_CACHE.g_tl_trip_rows(i).schedule_id IS NOT NULL)
4217 AND (FTE_TL_CACHE.g_tl_trip_rows(i).schedule_id= p_schedule_rows(j)) AND (p_vehicle_rows(j)=FTE_TL_CACHE.g_tl_trip_rows(i).vehicle_type ))
4218 THEN
4219 l_lane_fail:='N';
4220
4221 ELSIF ((i IS NOT NULL) AND (FTE_TL_CACHE.g_tl_trip_rows(i).lane_id IS NOT NULL)
4222 AND (FTE_TL_CACHE.g_tl_trip_rows(i).lane_id= p_lane_rows(j)) AND (p_vehicle_rows(j)=FTE_TL_CACHE.g_tl_trip_rows(i).vehicle_type ))
4223 THEN
4224 l_lane_fail:='N';
4225
4226 END IF;
4227
4228
4229 IF ((l_cache_fail='N') AND (l_lane_fail='N'))
4230 THEN
4231
4232 l_rating_fail:='N';
4233
4234 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Now Rating Lane'|| FTE_TL_CACHE.g_tl_trip_rows(i).lane_id||
4235 ' Schedule :'||FTE_TL_CACHE.g_tl_trip_rows(i).schedule_id);
4236
4237 FTE_TL_CORE.tl_core (
4238 p_trip_rec => FTE_TL_CACHE.g_tl_trip_rows(i),
4239 p_stop_tab => FTE_TL_CACHE.g_tl_trip_stop_rows,
4240 p_carrier_pref => FTE_TL_CACHE.g_tl_carrier_pref_rows(i),
4241 x_trip_charges_rec => l_trip_charges_rec,
4242 x_stop_charges_tab => l_stop_charges_tab,
4243 x_return_status => l_return_status );
4244
4245 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
4246 THEN
4247 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
4248 THEN
4249 l_rating_fail:='Y';
4250 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'TL Core failed for trip index :'||i);
4251 --raise FTE_FREIGHT_PRICING_UTIL.g_tl_core_fail;
4252 END IF;
4253 END IF;
4254
4255
4256 IF (l_rating_fail='N')
4257 THEN
4258
4259 Get_Base_Accessory_Charges(
4260 p_trip_index=>i,
4261 p_trip_charges_rec=>l_trip_charges_rec,
4262 p_stop_charges_tab=>l_stop_charges_tab,
4263 x_base_price=>x_lane_sched_base_rows(j),
4264 x_acc_charge=>x_lane_sched_acc_rows(j),
4265 x_currency=>x_lane_sched_curr_rows(j),
4266 x_return_status=>l_return_status);
4267
4268 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
4269 THEN
4270 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
4271 THEN
4272
4273 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'TL base,acc charges failed for trip index :'||i);
4274 --raise FTE_FREIGHT_PRICING_UTIL.g_tl_get_base_acc_chrg_fail;
4275 END IF;
4276 END IF;
4277 END IF;
4278
4279 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'For Lane'|| FTE_TL_CACHE.g_tl_trip_rows(i).lane_id||
4280 ' Schedule :'||FTE_TL_CACHE.g_tl_trip_rows(i).schedule_id);
4281
4282 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Estimate:'||x_lane_sched_base_rows(j)||
4283 ':'||x_lane_sched_acc_rows(j)||':'||x_lane_sched_curr_rows(j));
4284
4285 i:=FTE_TL_CACHE.g_tl_trip_rows.NEXT(i);
4286 END IF;
4287 j:=p_lane_rows.NEXT(j);
4288 END LOOP;
4289
4290 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'TL_FREIGHT_ESTIMATE');
4291
4292 IF (l_warning_count > 0)
4293 THEN
4294 x_return_status:=WSH_UTIL_CORE.G_RET_STS_WARNING;
4295 END IF;
4296
4297 EXCEPTION
4298
4299 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_delete_cache_fail THEN
4300 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4301 FTE_FREIGHT_PRICING_UTIL.set_exception('TL_FREIGHT_ESTIMATE',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_delete_cache_fail');
4302 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'TL_FREIGHT_ESTIMATE');
4303
4304 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_cache_estimate_fail THEN
4305 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4306 FTE_FREIGHT_PRICING_UTIL.set_exception('TL_FREIGHT_ESTIMATE',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_cache_estimate_fail');
4307 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'TL_FREIGHT_ESTIMATE');
4308
4309 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_core_fail THEN
4310 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4311 FTE_FREIGHT_PRICING_UTIL.set_exception('TL_FREIGHT_ESTIMATE',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_core_fail');
4312 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'TL_FREIGHT_ESTIMATE');
4313
4314
4315 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_get_base_acc_chrg_fail THEN
4316 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4317 FTE_FREIGHT_PRICING_UTIL.set_exception('TL_FREIGHT_ESTIMATE',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_get_base_acc_chrg_fail');
4318 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'TL_FREIGHT_ESTIMATE');
4319
4320
4321 WHEN others THEN
4322 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4323 FTE_FREIGHT_PRICING_UTIL.set_exception('TL_FREIGHT_ESTIMATE',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_others');
4324 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
4325 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'TL_FREIGHT_ESTIMATE');
4326
4327
4328 END TL_FREIGHT_ESTIMATE;
4329
4330
4331
4332 PROCEDURE Populate_OM_Rates(
4333 p_start_trip_index IN NUMBER,
4334 p_end_trip_index IN NUMBER,
4335 p_lane_info_tab IN FTE_FREIGHT_RATING_PUB.lane_info_tab_type,
4336 p_output_cost_tab IN FTE_FREIGHT_PRICING.Freight_Cost_Temp_Tab_Type,
4337 p_source_header_rec IN FTE_PROCESS_REQUESTS.fte_source_header_rec,
4338 p_source_lines_tab IN FTE_PROCESS_REQUESTS.fte_source_line_tab,
4339 p_LCSS_flag IN VARCHAR2,
4340 p_lane_sched_sum_rows IN dbms_utility.number_array,
4341 p_lane_sched_curr_rows IN dbms_utility.name_array,
4342 p_filtered_rows IN dbms_utility.number_array,
4343 p_ref_rows IN dbms_utility.number_array,
4344 p_summary_cache_ref IN dbms_utility.number_array,
4345 x_source_header_rates_tab IN OUT NOCOPY FTE_PROCESS_REQUESTS.fte_source_header_rates_tab,
4346 x_source_line_rates_tab IN OUT NOCOPY FTE_PROCESS_REQUESTS.fte_source_line_rates_tab,
4347 x_return_status OUT NOCOPY VARCHAR2)
4348 IS
4349
4350 l_source_header_rate_rec FTE_PROCESS_REQUESTS.fte_source_header_rates_rec;
4351 l_source_line_rate_rec FTE_PROCESS_REQUESTS.fte_source_line_rates_rec;
4352 l_detail_records FTE_PROCESS_REQUESTS.fte_source_line_rates_tab;
4353 l_detail_price dbms_utility.number_array;
4354 l_detail_charge dbms_utility.number_array;
4355 l_price NUMBER;
4356 l_charge NUMBER;
4357
4358
4359 i NUMBER;
4360 j NUMBER;
4361 k NUMBER;
4362 l NUMBER;
4363 m NUMBER;
4364 l_target_currency VARCHAR2(30);
4365 l_converted_amount NUMBER;
4366
4367 l_return_status VARCHAR2(1);
4368 l_log_level NUMBER := FTE_FREIGHT_PRICING_UTIL.G_DBG;
4369 l_warning_count NUMBER:=0;
4370
4371 BEGIN
4372
4373 FTE_FREIGHT_PRICING_UTIL.reset_dbg_vars;
4374 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,'Populate_OM_Rates','start');
4375
4376 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4377
4378
4379 IF (p_source_header_rec.currency is NULL)
4380 THEN
4381 l_target_currency := 'USD';
4382 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'source header currency is null, use USD');
4383 ELSE
4384 l_target_currency := p_source_header_rec.currency;
4385 END IF;
4386
4387 k:=p_output_cost_tab.FIRST;
4388
4389
4390 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'src line rates count:'||p_output_cost_tab.COUNT);
4391
4392 i:=p_lane_sched_sum_rows.FIRST;
4393
4394
4395
4396 m:=x_source_header_rates_tab.LAST;
4397 IF(m IS NULL)
4398 THEN
4399 m:=1;
4400 ELSE
4401 m:=m+1;
4402 END IF;
4403
4404
4405 j:=x_source_line_rates_tab.LAST;
4406 IF(j IS NULL)
4407 THEN
4408 j:=1;
4409 ELSE
4410 j:=j+1;
4411 END IF;
4412
4413
4414
4415 WHILE (i IS NOT NULL)
4416 LOOP
4417
4418
4419 IF (p_filtered_rows(i) = 1)
4420 THEN
4421
4422
4423
4424
4425 IF((p_lane_sched_sum_rows(i) IS NOT NULL))
4426 THEN
4427
4428 x_source_header_rates_tab(m):=l_source_header_rate_rec;
4429 IF (l_target_currency <> p_lane_sched_curr_rows(i))
4430 THEN
4431
4432
4433 l_converted_amount:=GL_CURRENCY_API.convert_amount(
4434 p_lane_sched_curr_rows(i),
4435 l_target_currency,
4436 SYSDATE,
4437 'Corporate',
4438 p_lane_sched_sum_rows(i)
4439 );
4440
4441 IF (l_converted_amount IS NULL)
4442 THEN
4443 raise FTE_FREIGHT_PRICING_UTIL.g_tl_conv_currency_fail;
4444 END IF;
4445 ELSE
4446
4447 l_converted_amount:=p_lane_sched_sum_rows(i);
4448 END IF;
4449
4450 x_source_header_rates_tab(m).consolidation_id := FTE_TL_CACHE.g_tl_trip_rows(p_summary_cache_ref(i)).trip_id;
4451
4452 x_source_header_rates_tab(m).lane_id := FTE_TL_CACHE.g_tl_trip_rows(p_summary_cache_ref(i)).lane_id;
4453 x_source_header_rates_tab(m).carrier_id := FTE_TL_CACHE.g_tl_trip_rows(p_summary_cache_ref(i)).carrier_id;
4454 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'DBG-12');
4455 x_source_header_rates_tab(m).carrier_freight_code := p_lane_info_tab(p_ref_rows(i)).carrier_freight_code;
4456 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'DBG-13');
4457 x_source_header_rates_tab(m).service_level := FTE_TL_CACHE.g_tl_trip_rows(p_summary_cache_ref(i)).service_type;
4458 x_source_header_rates_tab(m).mode_of_transport := FTE_TL_CACHE.g_tl_trip_rows(p_summary_cache_ref(i)).mode_of_transport;
4459 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'DBG-14');
4460 x_source_header_rates_tab(m).ship_method_code := p_lane_info_tab(p_ref_rows(i)).ship_method_code;
4461 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'DBG-15');
4462 x_source_header_rates_tab(m).cost_type_id := NULL;
4463 x_source_header_rates_tab(m).cost_type := 'SUMMARY';
4464 x_source_header_rates_tab(m).price := l_converted_amount;
4465 x_source_header_rates_tab(m).currency := l_target_currency;
4466 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'DBG-16');
4467 x_source_header_rates_tab(m).transit_time := p_lane_info_tab(p_ref_rows(i)).transit_time;
4468 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'DBG-17');
4469 x_source_header_rates_tab(m).transit_time_uom := p_lane_info_tab(p_ref_rows(i)).transit_time_uom;
4470 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'DBG-18');
4471 x_source_header_rates_tab(m).vehicle_type_id:=FTE_TL_CACHE.g_tl_trip_rows(p_summary_cache_ref(i)).vehicle_type;
4472 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'DBG-19');
4473 x_source_header_rates_tab(m).first_line_index := j;
4474
4475
4476 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'DBG-20');
4477
4478 l_detail_records.DELETE;
4479 l_detail_price.DELETE;
4480
4481 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'src line rates DBG0:'||x_source_header_rates_tab(m).lane_id||':'||x_source_header_rates_tab(m).vehicle_type_id||':'||x_source_header_rates_tab(m).consolidation_id);
4482
4483 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'src line rates DBG00:'||p_output_cost_tab(k).lane_id||':'||p_output_cost_tab(k).vehicle_type_id||':'||p_output_cost_tab(k).delivery_leg_id||':'||p_output_cost_tab(k).delivery_detail_id);
4484 --base charges:- base unloaded, base loaded, base unit, base time, base flat(PRICE/FTEPRICE)
4485 --All the rest are surcharges, subract base from summary to get (CHARGE/FTECHARGE)
4486 --Copy main fields from the summary record
4487
4488 -- Position at first detail cost type of this consolidation/lane/vehicle
4489
4490 WHILE((p_output_cost_tab.EXISTS(k)) AND((p_output_cost_tab(k).lane_id <> x_source_header_rates_tab(m).lane_id)
4491 OR (p_output_cost_tab(k).vehicle_type_id <> x_source_header_rates_tab(m).vehicle_type_id)))
4492 LOOP
4493
4494 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'src line rates DBG.1:'||
4495 p_output_cost_tab(k).lane_id||':'||p_output_cost_tab(k).vehicle_type_id||
4496 ':'||p_output_cost_tab(k).delivery_leg_id||':'||p_output_cost_tab(k).delivery_detail_id||
4497 ':'||p_output_cost_tab(k).freight_cost_id);
4498 k:=p_output_cost_tab.NEXT(k);
4499 END LOOP;
4500
4501 WHILE( (p_output_cost_tab.EXISTS(k)) AND(p_output_cost_tab(k).lane_id = x_source_header_rates_tab(m).lane_id)
4502 AND (p_output_cost_tab(k).vehicle_type_id = x_source_header_rates_tab(m).vehicle_type_id) AND ( (p_output_cost_tab(k).delivery_leg_id IS NULL) OR (p_output_cost_tab(k).delivery_leg_id <> x_source_header_rates_tab(m).consolidation_id)))
4503 LOOP
4504 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'src line rates DBG.2');
4505 k:=p_output_cost_tab.NEXT(k);
4506 END LOOP;
4507
4508 WHILE(p_output_cost_tab.EXISTS(k)) AND (p_output_cost_tab(k).delivery_leg_id IS NOT NULL) AND (p_output_cost_tab(k).delivery_leg_id = x_source_header_rates_tab(m).consolidation_id)
4509 AND (p_output_cost_tab(k).lane_id = x_source_header_rates_tab(m).lane_id) AND ((p_output_cost_tab(k).vehicle_type_id = x_source_header_rates_tab(m).vehicle_type_id)
4510 AND (p_output_cost_tab(k).delivery_detail_id IS NULL))
4511 LOOP
4512
4513 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'src line rates DBG.3');
4514 k:=p_output_cost_tab.NEXT(k);
4515 END LOOP;
4516
4517
4518 WHILE((p_output_cost_tab.EXISTS(k)) AND (p_output_cost_tab(k).delivery_leg_id IS NOT NULL) AND (p_output_cost_tab(k).delivery_leg_id = x_source_header_rates_tab(m).consolidation_id)
4519 AND (p_output_cost_tab(k).lane_id = x_source_header_rates_tab(m).lane_id) AND ((p_output_cost_tab(k).vehicle_type_id = x_source_header_rates_tab(m).vehicle_type_id))
4520 AND (p_output_cost_tab(k).delivery_detail_id IS NOT NULL))
4521 LOOP
4522
4523 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'src line rates DBG1');
4524 --If we come across a summary detail entry store it
4525
4526 IF ((p_output_cost_tab(k).line_type_code IS NOT NULL)
4527 AND(p_output_cost_tab(k).line_type_code='SUMMARY'))
4528 THEN
4529
4530 IF (l_detail_records.EXISTS(p_output_cost_tab(k).delivery_detail_id))
4531 THEN
4532 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'Detail Summary already exists: ERROR');
4533
4534 ELSE
4535 l_detail_records(p_output_cost_tab(k).delivery_detail_id):=l_source_line_rate_rec;
4536 l_detail_records(p_output_cost_tab(k).delivery_detail_id).source_line_id:=p_output_cost_tab(k).delivery_detail_id;
4537 l_detail_records(p_output_cost_tab(k).delivery_detail_id).priced_quantity:=p_output_cost_tab(k).billable_quantity;
4538 l_detail_records(p_output_cost_tab(k).delivery_detail_id).priced_uom:=p_output_cost_tab(k).billable_uom;
4539 l_detail_records(p_output_cost_tab(k).delivery_detail_id).currency:=l_target_currency;
4540 l_detail_records(p_output_cost_tab(k).delivery_detail_id).adjusted_price:=p_output_cost_tab(k).total_amount;
4541
4542 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'src line rates DBG2');
4543 END IF;
4544
4545 --If we come across a price record total it up
4546
4547 ELSIF ((p_output_cost_tab(k).line_type_code IS NOT NULL)
4548 AND(p_output_cost_tab(k).line_type_code='TLPRICE'))
4549 THEN
4550
4551 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'src line rates DBG3');
4552 --Do currency conversion later as all the detail rates are in carrier currency
4553
4554 IF (l_detail_price.EXISTS(p_output_cost_tab(k).delivery_detail_id))
4555 THEN
4556 l_detail_price(p_output_cost_tab(k).delivery_detail_id):=l_detail_price(p_output_cost_tab(k).delivery_detail_id)
4557 +p_output_cost_tab(k).total_amount;
4558
4559 ELSE
4560 l_detail_price(p_output_cost_tab(k).delivery_detail_id):=p_output_cost_tab(k).total_amount;
4561
4562 END IF;
4563
4564
4565
4566 ELSE
4567 --Its a charge record
4568 null;
4569
4570 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'src line rates DBG4');
4571 END IF;
4572
4573
4574
4575
4576
4577 k:=p_output_cost_tab.NEXT(k);
4578 END LOOP;
4579
4580
4581
4582 l:=l_detail_price.FIRST;
4583 WHILE(l IS NOT NULL)
4584 LOOP
4585
4586
4587 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'src line rates DBG5');
4588 x_source_line_rates_tab(j):=l_detail_records(l);
4589
4590 --PRICE record
4591
4592
4593 l_price:=l_detail_price(l);
4594 l_charge:=x_source_line_rates_tab(j).adjusted_price - l_price;
4595
4596 x_source_line_rates_tab(j).cost_type_id:=NULL;
4597 x_source_line_rates_tab(j).line_type_code := 'PRICE';
4598 x_source_line_rates_tab(j).cost_type := 'FTEPRICE';
4599 x_source_line_rates_tab(j).cost_sub_type := 'PRICE';
4600
4601 IF (l_target_currency <> p_lane_sched_curr_rows(i))
4602 THEN
4603
4604
4605 l_converted_amount:=GL_CURRENCY_API.convert_amount(
4606 p_lane_sched_curr_rows(i),
4607 l_target_currency,
4608 SYSDATE,
4609 'Corporate',
4610 l_price
4611 );
4612
4613 IF (l_converted_amount IS NULL)
4614 THEN
4615 raise FTE_FREIGHT_PRICING_UTIL.g_tl_conv_currency_fail;
4616 END IF;
4617
4618 ELSE
4619 l_converted_amount:=l_price;
4620
4621 END IF;
4622
4623
4624 x_source_line_rates_tab(j).currency:=l_target_currency;
4625 x_source_line_rates_tab(j).adjusted_price:=l_converted_amount;
4626
4627
4628 IF((x_source_line_rates_tab(j).priced_quantity IS NOT NULL) AND (x_source_line_rates_tab(j).priced_quantity <> 0))
4629 THEN
4630
4631 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'src line rates DBG6');
4632 x_source_line_rates_tab(j).adjusted_unit_price:=l_converted_amount/x_source_line_rates_tab(j).priced_quantity;
4633 x_source_line_rates_tab(j).unit_price := l_converted_amount/x_source_line_rates_tab(j).priced_quantity;
4634
4635 ELSE
4636 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'src line rates DBG7');
4637 x_source_line_rates_tab(j).adjusted_unit_price:=l_converted_amount;
4638 x_source_line_rates_tab(j).unit_price := l_converted_amount;
4639
4640
4641 END IF;
4642 x_source_line_rates_tab(j).base_price := l_converted_amount;
4643
4644 x_source_line_rates_tab(j).consolidation_id := x_source_header_rates_tab(m).consolidation_id;
4645 x_source_line_rates_tab(j).lane_id := x_source_header_rates_tab(m).lane_id ;
4646 x_source_line_rates_tab(j).carrier_id := x_source_header_rates_tab(m).carrier_id;
4647 x_source_line_rates_tab(j).carrier_freight_code := x_source_header_rates_tab(m).carrier_freight_code;
4648 x_source_line_rates_tab(j).service_level := x_source_header_rates_tab(m).service_level;
4649 x_source_line_rates_tab(j).mode_of_transport := x_source_header_rates_tab(m).mode_of_transport;
4650 x_source_line_rates_tab(j).ship_method_code := x_source_header_rates_tab(m).ship_method_code;
4651
4652
4653
4654
4655 --Create another record for charges
4656
4657 j:=j+1;
4658
4659 --CHARGE record
4660
4661 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'src line rates DBG8');
4662 x_source_line_rates_tab(j):=l_detail_records(l);
4663
4664 x_source_line_rates_tab(j).cost_type_id:=NULL;
4665 x_source_line_rates_tab(j).line_type_code := 'CHARGE';
4666 x_source_line_rates_tab(j).cost_type := 'FTECHARGE';
4667 x_source_line_rates_tab(j).cost_sub_type := 'CHARGE';
4668
4669 IF (l_target_currency <> p_lane_sched_curr_rows(i))
4670 THEN
4671 l_converted_amount:=GL_CURRENCY_API.convert_amount(
4672 p_lane_sched_curr_rows(i),
4673 l_target_currency,
4674 SYSDATE,
4675 'Corporate',
4676 l_charge
4677 );
4678
4679 IF (l_converted_amount IS NULL)
4680 THEN
4681 raise FTE_FREIGHT_PRICING_UTIL.g_tl_conv_currency_fail;
4682 END IF;
4683 ELSE
4684 l_converted_amount:=l_charge;
4685
4686 END IF;
4687
4688
4689 x_source_line_rates_tab(j).currency:=l_target_currency;
4690 x_source_line_rates_tab(j).adjusted_price:=l_converted_amount;
4691
4692
4693 IF((x_source_line_rates_tab(j).priced_quantity IS NOT NULL) AND (x_source_line_rates_tab(j).priced_quantity <> 0))
4694 THEN
4695 x_source_line_rates_tab(j).adjusted_unit_price:=l_converted_amount/x_source_line_rates_tab(j).priced_quantity;
4696 x_source_line_rates_tab(j).unit_price := l_converted_amount/x_source_line_rates_tab(j).priced_quantity;
4697
4698 ELSE
4699 x_source_line_rates_tab(j).adjusted_unit_price:=l_converted_amount;
4700 x_source_line_rates_tab(j).unit_price := l_converted_amount;
4701
4702
4703 END IF;
4704 x_source_line_rates_tab(j).base_price := l_converted_amount;
4705
4706 x_source_line_rates_tab(j).consolidation_id := x_source_header_rates_tab(m).consolidation_id;
4707 x_source_line_rates_tab(j).lane_id := x_source_header_rates_tab(m).lane_id ;
4708 x_source_line_rates_tab(j).carrier_id := x_source_header_rates_tab(m).carrier_id;
4709 x_source_line_rates_tab(j).carrier_freight_code := x_source_header_rates_tab(m).carrier_freight_code;
4710 x_source_line_rates_tab(j).service_level := x_source_header_rates_tab(m).service_level;
4711 x_source_line_rates_tab(j).mode_of_transport := x_source_header_rates_tab(m).mode_of_transport;
4712 x_source_line_rates_tab(j).ship_method_code := x_source_header_rates_tab(m).ship_method_code;
4713
4714
4715
4716 j:=j+1;
4717
4718
4719 l:=l_detail_price.NEXT(l);
4720
4721 END LOOP;
4722
4723
4724
4725
4726 --increment to next delivery detail rate for next iter of the loop
4727
4728 WHILE((k IS NOT NULL) AND (p_output_cost_tab(k).delivery_detail_id IS NULL))
4729 LOOP
4730
4731 FTE_FREIGHT_PRICING_UTIL.print_msg(l_log_level,'src line rates DBG9');
4732 k:=p_output_cost_tab.NEXT(k);
4733 END LOOP;
4734
4735 m:=m+1;
4736
4737 END IF;--p_lane_sched_sum_rows(i) IS NOT NULL
4738
4739
4740 END IF;--filtered rows
4741
4742
4743 i:=p_lane_sched_sum_rows.NEXT(i);
4744
4745 END LOOP;
4746
4747
4748
4749
4750 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Populate_OM_Rates');
4751 IF (l_warning_count > 0)
4752 THEN
4753 x_return_status:=WSH_UTIL_CORE.G_RET_STS_WARNING;
4754 END IF;
4755
4756 EXCEPTION
4757
4758 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_conv_currency_fail THEN
4759 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4760 FTE_FREIGHT_PRICING_UTIL.set_exception('Populate_OM_Rates',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_conv_currency_fail');
4761 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Populate_OM_Rates');
4762
4763 WHEN others THEN
4764 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4765 FTE_FREIGHT_PRICING_UTIL.set_exception('Populate_OM_Rates',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_others');
4766 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
4767 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'Populate_OM_Rates');
4768
4769
4770
4771
4772 END Populate_OM_Rates;
4773
4774
4775
4776 -- Only the lanes for which the corresponding x_filtered_list_flag(i)=1 need to be considered
4777 -- For the lanes which were expanded due to vehicles only one of them will have x_filtered_list_flag(i)=1
4778 -- Some lanes need not have a rate
4779 -- If p_LCSS_flag ='Y' the least cost is identified
4780
4781
4782 PROCEDURE TL_Filter_For_Vehicle_Cost(
4783 p_lane_sched_sum_rows IN dbms_utility.number_array,
4784 p_lane_sched_curr_rows IN dbms_utility.name_array,
4785 p_ref_rows IN dbms_utility.number_array,
4786 p_LCSS_flag IN VARCHAR2,
4787 x_filtered_list_flag OUT NOCOPY dbms_utility.number_array,
4788 x_least_index OUT NOCOPY NUMBER,
4789 x_return_status OUT NOCOPY Varchar2)
4790 IS
4791
4792 l_lane_min_ref NUMBER;
4793 l_lane_min_rate NUMBER;
4794 l_lane_min_currency VARCHAR2(30);
4795 l_lane_min_index NUMBER;
4796
4797
4798 l_LCSS_ref NUMBER;
4799 l_LCSS_rate NUMBER;
4800 l_LCSS_currency VARCHAR2(30);
4801 l_LCSS_index NUMBER;
4802
4803
4804 l_converted_amount NUMBER;
4805 i NUMBER;
4806 l_return_status VARCHAR2(1);
4807 l_log_level NUMBER := FTE_FREIGHT_PRICING_UTIL.G_DBG;
4808 l_warning_count NUMBER:=0;
4809
4810 BEGIN
4811
4812 FTE_FREIGHT_PRICING_UTIL.reset_dbg_vars;
4813 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,'TL_Filter_For_Vehicle_Cost','start');
4814
4815 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4816
4817
4818
4819
4820 IF (p_LCSS_flag='Y')
4821 THEN
4822
4823 l_LCSS_ref:=NULL;
4824 l_LCSS_rate:=NULL;
4825 l_LCSS_currency:=NULL;
4826 l_LCSS_index:=NULL;
4827 x_least_index:=NULL;
4828
4829 i:=p_lane_sched_sum_rows.FIRST;
4830
4831 WHILE(i IS NOT NULL)
4832 LOOP
4833 x_filtered_list_flag(i):=0;
4834 IF (p_lane_sched_sum_rows(i) IS NOT NULL)
4835 THEN
4836
4837 IF (l_LCSS_currency IS NULL)
4838 THEN
4839
4840
4841 l_LCSS_rate:=p_lane_sched_sum_rows(i);
4842 l_LCSS_currency:=p_lane_sched_curr_rows(i);
4843 l_LCSS_index:=i;
4844 x_filtered_list_flag(i):=1;
4845 x_least_index:=i;
4846
4847 ELSE
4848
4849 IF(l_LCSS_currency <> p_lane_sched_curr_rows(i))
4850 THEN
4851 l_converted_amount:=GL_CURRENCY_API.convert_amount(
4852 p_lane_sched_curr_rows(i),
4853 l_LCSS_currency,
4854 SYSDATE,
4855 'Corporate',
4856 p_lane_sched_sum_rows(i)
4857 );
4858
4859 IF (l_converted_amount IS NULL)
4860 THEN
4861 raise FTE_FREIGHT_PRICING_UTIL.g_tl_conv_currency_fail;
4862 END IF;
4863
4864
4865 ELSE
4866
4867 l_converted_amount:=p_lane_sched_sum_rows(i);
4868
4869 END IF;
4870
4871 IF (l_converted_amount < l_LCSS_rate)
4872 THEN
4873
4874 x_filtered_list_flag(l_LCSS_index):=0;
4875
4876 l_LCSS_rate:=p_lane_sched_sum_rows(i);
4877 l_LCSS_currency:=p_lane_sched_curr_rows(i);
4878 l_LCSS_index:=i;
4879 x_filtered_list_flag(i):=1;
4880 x_least_index:=i;
4881
4882 END IF;
4883
4884
4885 END IF;
4886
4887 END IF;
4888
4889
4890 i:=p_lane_sched_sum_rows.NEXT(i);
4891 END LOOP;
4892
4893
4894 ELSE
4895 --LCSS_flag='N'
4896
4897
4898
4899 l_lane_min_ref:=NULL;
4900 l_lane_min_rate:=NULL;
4901 l_lane_min_currency:=NULL;
4902 l_lane_min_index:=NULL;
4903
4904
4905
4906
4907 i:=p_lane_sched_sum_rows.FIRST;
4908
4909 WHILE(i IS NOT NULL)
4910 LOOP
4911 IF((l_lane_min_ref IS NOT NULL) AND (p_ref_rows(i) = l_lane_min_ref))
4912 THEN
4913 -- A vehicle with some rate is preferred to a vehicle with no rate
4914
4915 IF((l_lane_min_rate IS NOT NULL) AND (p_lane_sched_curr_rows(i) IS NOT NULL) AND (l_lane_min_currency <> p_lane_sched_curr_rows(i)))
4916 THEN
4917
4918 l_converted_amount:=GL_CURRENCY_API.convert_amount(
4919 p_lane_sched_curr_rows(i),
4920 l_lane_min_currency,
4921 SYSDATE,
4922 'Corporate',
4923 p_lane_sched_sum_rows(i)
4924 );
4925
4926 IF (l_converted_amount IS NULL)
4927 THEN
4928 raise FTE_FREIGHT_PRICING_UTIL.g_tl_conv_currency_fail;
4929 END IF;
4930
4931
4932 ELSE
4933
4934 l_converted_amount:=p_lane_sched_sum_rows(i);
4935
4936 END IF;
4937
4938 IF ((l_converted_amount IS NOT NULL) AND ((l_lane_min_rate IS NULL) OR (l_converted_amount < l_lane_min_rate)) )
4939 THEN
4940 x_filtered_list_flag(l_lane_min_index):=0;
4941 x_filtered_list_flag(i):=1;
4942
4943 l_lane_min_ref:=p_ref_rows(i);
4944 l_lane_min_rate:=p_lane_sched_sum_rows(i);
4945 l_lane_min_currency:=p_lane_sched_curr_rows(i);
4946 l_lane_min_index:=i;
4947
4948
4949 ELSE
4950 x_filtered_list_flag(i):=0;
4951
4952
4953 END IF;
4954
4955
4956 ELSE
4957
4958 x_filtered_list_flag(i):=1;
4959 l_lane_min_ref:=p_ref_rows(i);
4960 l_lane_min_rate:=p_lane_sched_sum_rows(i);
4961 l_lane_min_currency:=p_lane_sched_curr_rows(i);
4962 l_lane_min_index:=i;
4963
4964 END IF;
4965
4966
4967 i:=p_lane_sched_sum_rows.NEXT(i);
4968 END LOOP;
4969
4970
4971 END IF;
4972
4973
4974
4975 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'TL_Filter_For_Vehicle_Cost');
4976 IF (l_warning_count > 0)
4977 THEN
4978 x_return_status:=WSH_UTIL_CORE.G_RET_STS_WARNING;
4979 END IF;
4980
4981
4982
4983 EXCEPTION
4984
4985 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_conv_currency_fail THEN
4986 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4987 FTE_FREIGHT_PRICING_UTIL.set_exception('TL_Filter_For_Vehicle_Cost',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_conv_currency_fail');
4988 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'TL_Filter_For_Vehicle_Cost');
4989
4990 WHEN others THEN
4991 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4992 FTE_FREIGHT_PRICING_UTIL.set_exception('TL_Filter_For_Vehicle_Cost',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_others');
4993 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
4994 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'TL_Filter_For_Vehicle_Cost');
4995
4996
4997 END TL_Filter_For_Vehicle_Cost;
4998
4999
5000 PROCEDURE TL_OM_RATING(
5001 p_lane_rows IN dbms_utility.number_array ,
5002 p_schedule_rows IN dbms_utility.number_array,
5003 p_lane_info_tab IN FTE_FREIGHT_RATING_PUB.lane_info_tab_type,
5004 p_source_header_rec IN FTE_PROCESS_REQUESTS.fte_source_header_rec,
5005 p_source_lines_tab IN FTE_PROCESS_REQUESTS.fte_source_line_tab,
5006 p_LCSS_flag IN VARCHAR2,
5007 x_source_header_rates_tab IN OUT NOCOPY FTE_PROCESS_REQUESTS.fte_source_header_rates_tab,
5008 x_source_line_rates_tab IN OUT NOCOPY FTE_PROCESS_REQUESTS.fte_source_line_rates_tab,
5009 x_return_status OUT NOCOPY Varchar2)
5010 IS
5011
5012 l_exploded_lane_rows dbms_utility.number_array;
5013 l_exploded_schedule_rows dbms_utility.number_array;
5014 l_exploded_vehicle_rows dbms_utility.number_array;
5015 l_exploded_ref_rows dbms_utility.number_array;
5016 l_vehicle_rows dbms_utility.number_array;
5017
5018 l_output_tab FTE_FREIGHT_PRICING.Freight_Cost_Temp_Tab_Type;
5019 i NUMBER;
5020 l_request_id NUMBER;
5021 l_exceptions_tab FTE_TL_CORE.tl_exceptions_tab_type;
5022 l_trip_charges_tab FTE_TL_CACHE.TL_trip_output_tab_type;
5023 l_stop_charges_tab FTE_TL_CACHE.TL_trip_stop_output_tab_type;
5024
5025 l_lane_sched_sum_rows dbms_utility.number_array;
5026 l_lane_sched_curr_rows dbms_utility.name_array;
5027 l_filtered_rows dbms_utility.number_array;
5028 l_summary_cache_ref dbms_utility.number_array;
5029
5030 l_least_index NUMBER;
5031 l_return_status VARCHAR2(1);
5032 l_log_level NUMBER := FTE_FREIGHT_PRICING_UTIL.G_DBG;
5033 l_warning_count NUMBER:=0;
5034
5035 BEGIN
5036
5037
5038 FTE_FREIGHT_PRICING_UTIL.reset_dbg_vars;
5039 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,'TL_OM_Rating','start');
5040
5041 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
5042
5043
5044 --No freight term/manifesting issues
5045
5046
5047 FTE_TL_CACHE.Delete_Cache(x_return_status=>l_return_status);
5048
5049 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
5050 THEN
5051 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
5052 THEN
5053 raise FTE_FREIGHT_PRICING_UTIL.g_tl_delete_cache_fail;
5054 END IF;
5055 END IF;
5056
5057 l_vehicle_rows.delete;
5058
5059 i:=p_lane_rows.FIRST;
5060 WHILE(i IS NOT NULL)
5061 LOOP
5062 l_vehicle_rows(i):=NULL;
5063 i:=p_lane_rows.NEXT(i);
5064 END LOOP;
5065
5066
5067 FTE_TL_RATING.Get_Vehicles_For_LaneSchedules(
5068 p_trip_id =>NULL,
5069 p_lane_rows =>p_lane_rows,
5070 p_schedule_rows =>p_schedule_rows,
5071 p_vehicle_rows =>l_vehicle_rows,
5072 x_vehicle_rows =>l_exploded_vehicle_rows,
5073 x_lane_rows =>l_exploded_lane_rows,
5074 x_schedule_rows =>l_exploded_schedule_rows,
5075 x_ref_rows =>l_exploded_ref_rows,
5076 x_return_status =>l_return_status);
5077 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
5078 THEN
5079 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
5080 THEN
5081 raise FTE_FREIGHT_PRICING_UTIL.g_tl_veh_for_lane_sched_fail;
5082 END IF;
5083 END IF;
5084
5085
5086
5087 FTE_TL_CACHE.TL_BUILD_CACHE_FOR_OM(
5088 p_source_header_rec=>p_source_header_rec,
5089 p_source_lines_tab=>p_source_lines_tab,
5090 p_lane_rows=>l_exploded_lane_rows,
5091 p_schedule_rows=>l_exploded_schedule_rows,
5092 p_vehicle_rows=>l_exploded_vehicle_rows,
5093 x_return_status=>l_return_status);
5094
5095
5096
5097 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
5098 THEN
5099 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
5100 THEN
5101 raise FTE_FREIGHT_PRICING_UTIL.g_tl_bld_cache_om_fail;
5102 END IF;
5103 END IF;
5104
5105 FTE_TL_CACHE.Display_Cache;
5106
5107
5108 IF(FTE_TL_CACHE.g_tl_trip_rows.FIRST IS NOT NULL)
5109 THEN
5110
5111 TL_Rate_Cached_Trip_Multiple (
5112 p_start_trip_index =>FTE_TL_CACHE.g_tl_trip_rows.FIRST,
5113 p_end_trip_index =>FTE_TL_CACHE.g_tl_trip_rows.LAST,
5114 p_output_type =>'P',
5115 p_request_id =>1,
5116 p_allocate_flag=>'Y',
5117 x_lane_sched_sum_rows=>l_lane_sched_sum_rows,
5118 x_lane_sched_curr_rows=>l_lane_sched_curr_rows,
5119 x_output_cost_tab =>l_output_tab,
5120 x_exceptions_tab =>l_exceptions_tab,
5121 x_trip_charges_tab =>l_trip_charges_tab,
5122 x_stop_charges_tab =>l_stop_charges_tab,
5123 x_return_status =>l_return_status);
5124
5125 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
5126 THEN
5127 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
5128 THEN
5129 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'TL_Rate_Cached_Trip_Multiple has failed');
5130 raise FTE_FREIGHT_PRICING_UTIL.g_tl_rate_cached_trip_fail;
5131 END IF;
5132 END IF;
5133
5134
5135 Populate_Summary_Rates(
5136 p_lane_rows=>l_exploded_lane_rows,
5137 p_schedule_rows=>l_exploded_schedule_rows,
5138 p_vehicle_rows=>l_exploded_vehicle_rows,
5139 p_start_trip_index=>FTE_TL_CACHE.g_tl_trip_rows.FIRST,
5140 p_end_trip_index=>FTE_TL_CACHE.g_tl_trip_rows.LAST,
5141 p_exceptions_tab=>l_exceptions_tab,
5142 x_lane_sched_sum_rows=>l_lane_sched_sum_rows,
5143 x_lane_sched_curr_rows=>l_lane_sched_curr_rows,
5144 x_summary_cache_ref=>l_summary_cache_ref,
5145 x_return_status=>l_return_status);
5146
5147 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
5148 THEN
5149 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
5150 THEN
5151 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Populate_Summary_Rates has failed');
5152 raise FTE_FREIGHT_PRICING_UTIL.g_tl_populate_summary_fail;
5153 END IF;
5154 END IF;
5155
5156
5157 END IF;
5158
5159 fte_freight_pricing_util.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Finished multiple rating and allocation..now checkign errors' );
5160
5161 i:=l_exceptions_tab.FIRST;
5162 WHILE ( i IS NOT NULL)
5163 LOOP
5164
5165 fte_freight_pricing_util.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Error index :'||i );
5166
5167 IF (
5168 l_exceptions_tab(i).check_tlqp_ouputfail='Y' OR
5169 l_exceptions_tab(i).check_qp_ipl_fail='Y' OR
5170 l_exceptions_tab(i).not_on_pl_flag='Y' OR
5171 l_exceptions_tab(i).price_req_failed='Y' OR
5172 l_exceptions_tab(i).allocation_failed='Y'
5173 )
5174 THEN
5175
5176 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Rate cached trip failed for trip index:'||i);
5177 --Add warning message
5178 l_warning_count:=l_warning_count+1;
5179
5180 --IF (FTE_TL_CACHE.g_tl_trip_rows(i).schedule_id IS NOT NULL)
5181 --THEN
5182
5183 -- FTE_FREIGHT_PRICING_UTIL.setmsg (
5184 -- p_api=>'TL_OM_Rating',
5185 -- p_exc=>'g_tl_trip_cmp_rate_schd_fail',
5186 -- p_msg_type=>'W',
5187 -- p_trip_id=> p_wsh_trip_id,
5188 -- p_schedule_id=>FTE_TL_CACHE.g_tl_trip_rows(i).schedule_id);
5189
5190
5191 --ELSE
5192
5193 -- FTE_FREIGHT_PRICING_UTIL.setmsg (
5194 -- p_api=>'TL_OM_Rating',
5195 -- p_exc=>'g_tl_trip_cmp_rate_lane_fail',
5196 -- p_msg_type=>'W',
5197 -- p_trip_id=> p_wsh_trip_id,
5198 -- p_lane_id=>FTE_TL_CACHE.g_tl_trip_rows(i).lane_id);
5199
5200 --END IF;
5201
5202
5203
5204 END IF;
5205
5206
5207 i:=l_exceptions_tab.NEXT(i);
5208 END LOOP;
5209
5210
5211 TL_Filter_For_Vehicle_Cost(
5212 p_lane_sched_sum_rows=>l_lane_sched_sum_rows,
5213 p_lane_sched_curr_rows=>l_lane_sched_curr_rows,
5214 p_ref_rows=>l_exploded_ref_rows,
5215 p_LCSS_flag=>p_LCSS_flag,
5216 x_filtered_list_flag=>l_filtered_rows,
5217 x_least_index=>l_least_index,
5218 x_return_status=>l_return_status);
5219
5220 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
5221 THEN
5222 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
5223 THEN
5224
5225 raise FTE_FREIGHT_PRICING_UTIL.g_tl_om_filt_least_veh_fail;
5226 END IF;
5227 END IF;
5228
5229
5230
5231 FTE_FREIGHT_PRICING.print_fc_temp_rows(
5232 p_fc_temp_rows => l_output_tab,
5233 x_return_status => l_return_status);
5234
5235
5236 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Lane Shed Sum Count:'||l_lane_sched_sum_rows.COUNT);
5237 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Filter Count:'||l_filtered_rows.COUNT);
5238 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Cache Ref:'||l_summary_cache_ref.COUNT);
5239 i:=l_lane_sched_sum_rows.FIRST;
5240 WHILE(i IS NOT NULL)
5241 LOOP
5242
5243 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'index:'||i||' Price:'||l_lane_sched_sum_rows(i)||l_lane_sched_curr_rows(i)||' Filter:'||l_filtered_rows(i)||' Cache Ref:'||l_summary_cache_ref(i));
5244 i:=l_lane_sched_sum_rows.NEXT(i);
5245
5246 END LOOP;
5247
5248 Populate_OM_Rates(
5249 p_start_trip_index=>FTE_TL_CACHE.g_tl_trip_rows.FIRST,
5250 p_end_trip_index=>FTE_TL_CACHE.g_tl_trip_rows.LAST,
5251 p_lane_info_tab=>p_lane_info_tab,
5252 p_output_cost_tab=>l_output_tab,
5253 p_source_header_rec=>p_source_header_rec,
5254 p_source_lines_tab=>p_source_lines_tab,
5255 p_LCSS_flag=>p_LCSS_flag,
5256 p_lane_sched_sum_rows=>l_lane_sched_sum_rows,
5257 p_lane_sched_curr_rows=>l_lane_sched_curr_rows,
5258 p_filtered_rows=>l_filtered_rows,
5259 p_ref_rows=>l_exploded_ref_rows,
5260 p_summary_cache_ref=>l_summary_cache_ref,
5261 x_source_header_rates_tab=>x_source_header_rates_tab,
5262 x_source_line_rates_tab=>x_source_line_rates_tab,
5263 x_return_status=>l_return_status);
5264
5265 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
5266 THEN
5267 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
5268 THEN
5269
5270 raise FTE_FREIGHT_PRICING_UTIL.g_tl_om_populate_rate_fail;
5271 END IF;
5272 END IF;
5273
5274
5275 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'TL_OM_Rating');
5276
5277 IF (l_warning_count > 0)
5278 THEN
5279 x_return_status:=WSH_UTIL_CORE.G_RET_STS_WARNING;
5280 END IF;
5281
5282 EXCEPTION
5283
5284
5285 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_populate_summary_fail THEN
5286 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5287 FTE_FREIGHT_PRICING_UTIL.set_exception('TL_OM_Rating',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_populate_summary_fail');
5288 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'TL_OM_Rating');
5289
5290
5291 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_om_filt_least_veh_fail THEN
5292 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5293 FTE_FREIGHT_PRICING_UTIL.set_exception('TL_OM_Rating',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_om_filt_least_veh_fail');
5294 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'TL_OM_Rating');
5295
5296 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_om_populate_rate_fail THEN
5297 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5298 FTE_FREIGHT_PRICING_UTIL.set_exception('TL_OM_Rating',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_om_populate_rate_fail');
5299 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'TL_OM_Rating');
5300
5301
5302
5303 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_delete_cache_fail THEN
5304 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5305 FTE_FREIGHT_PRICING_UTIL.set_exception('TL_OM_Rating',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_delete_cache_fail');
5306 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'TL_OM_Rating');
5307
5308 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_veh_for_lane_sched_fail THEN
5309 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5310 FTE_FREIGHT_PRICING_UTIL.set_exception('TL_OM_Rating',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_veh_for_lane_sched_fail');
5311 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'TL_OM_Rating');
5312
5313 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_rate_cached_trip_fail THEN
5314 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5315 FTE_FREIGHT_PRICING_UTIL.set_exception('TL_OM_Rating',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_rate_cached_trip_fail');
5316 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'TL_OM_Rating');
5317
5318
5319
5320 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_bld_cache_om_fail THEN
5321 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5322 FTE_FREIGHT_PRICING_UTIL.set_exception('TL_OM_Rating',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_bld_cache_om_fail');
5323 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'TL_OM_Rating');
5324
5325
5326 WHEN others THEN
5327 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
5328 FTE_FREIGHT_PRICING_UTIL.set_exception('TL_OM_Rating',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_others');
5329 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
5330 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'TL_OM_Rating');
5331
5332 END TL_OM_RATING;
5333
5334
5335
5336 PROCEDURE BEGIN_LCSS (
5337 p_trip_id IN NUMBER,
5338 p_lane_rows IN dbms_utility.number_array ,
5339 x_trip_index OUT NOCOPY NUMBER,
5340 x_trip_charges_rec OUT NOCOPY FTE_TL_CACHE.TL_trip_output_rec_type ,
5341 x_stop_charges_tab OUT NOCOPY FTE_TL_CACHE.TL_trip_stop_output_tab_type,
5342 x_total_cost OUT NOCOPY NUMBER,
5343 x_currency OUT NOCOPY VARCHAR2,
5344 x_vehicle_type OUT NOCOPY NUMBER,
5345 x_lane_ref OUT NOCOPY NUMBER,
5346 x_return_status OUT NOCOPY VARCHAR2)
5347 IS
5348
5349 i NUMBER;
5350 l_schedule_rows dbms_utility.number_array;
5351 l_exploded_lane_rows dbms_utility.number_array;
5352 l_exploded_schedule_rows dbms_utility.number_array;
5353 l_exploded_vehicle_rows dbms_utility.number_array;
5354 l_exploded_ref_rows dbms_utility.number_array;
5355 l_vehicle_rows dbms_utility.number_array;
5356
5357 l_output_tab FTE_FREIGHT_PRICING.Freight_Cost_Temp_Tab_Type;
5358 l_request_id NUMBER;
5359 l_exceptions_tab FTE_TL_CORE.tl_exceptions_tab_type;
5360 l_lane_sched_sum_rows dbms_utility.number_array;
5361 l_lane_sched_curr_rows dbms_utility.name_array;
5362 l_filtered_rows dbms_utility.number_array;
5363 l_trip_charges_tab FTE_TL_CACHE.TL_trip_output_tab_type;
5364 l_stop_charges_tab FTE_TL_CACHE.TL_trip_stop_output_tab_type;
5365 l_least_index NUMBER;
5366 l_summary_cache_ref dbms_utility.number_array;
5367 l_least_cache_index NUMBER;
5368
5369 l_return_status VARCHAR2(1);
5370 l_log_level NUMBER := FTE_FREIGHT_PRICING_UTIL.G_DBG;
5371 l_warning_count NUMBER:=0;
5372
5373 BEGIN
5374
5375
5376 FTE_FREIGHT_PRICING_UTIL.reset_dbg_vars;
5377 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,'BEGIN_LCSS','start');
5378
5379 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
5380
5381
5382 i:=p_lane_rows.FIRST;
5383 WHILE ( i IS NOT NULL)
5384 LOOP
5385 l_vehicle_rows(i):=NULL;
5386 l_schedule_rows(i):=NULL;
5387 i:=p_lane_rows.NEXT(i);
5388 END LOOP;
5389
5390
5391 FTE_TL_RATING.Get_Vehicles_For_LaneSchedules(
5392 p_trip_id =>NULL,
5393 p_lane_rows =>p_lane_rows,
5394 p_schedule_rows =>l_schedule_rows,
5395 p_vehicle_rows =>l_vehicle_rows,
5396 x_vehicle_rows =>l_exploded_vehicle_rows,
5397 x_lane_rows =>l_exploded_lane_rows,
5398 x_schedule_rows =>l_exploded_schedule_rows,
5399 x_ref_rows =>l_exploded_ref_rows,
5400 x_return_status =>l_return_status);
5401
5402 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
5403 THEN
5404 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
5405 THEN
5406 raise FTE_FREIGHT_PRICING_UTIL.g_tl_veh_for_lane_sched_fail;
5407 END IF;
5408 END IF;
5409
5410
5411
5412 FTE_TL_CACHE.TL_BUILD_CACHE_FOR_LCS(
5413 p_wsh_trip_id => p_trip_id,
5414 p_lane_rows => l_exploded_lane_rows,
5415 p_schedule_rows =>l_exploded_schedule_rows,
5416 p_vehicle_rows=>l_exploded_vehicle_rows,
5417 x_return_status => l_return_status);
5418
5419
5420 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
5421 THEN
5422 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
5423 THEN
5424 raise FTE_FREIGHT_PRICING_UTIL.g_tl_build_cache_lcss_fail;
5425 END IF;
5426 END IF;
5427
5428
5429
5430
5431 FTE_TL_CACHE.DISPLAY_CACHE();
5432
5433 IF (FTE_TL_CACHE.g_tl_trip_rows.FIRST IS NOT NULL)
5434 THEN
5435
5436 TL_Rate_Cached_Trip_Multiple (
5437 p_start_trip_index =>FTE_TL_CACHE.g_tl_trip_rows.FIRST,
5438 p_end_trip_index =>FTE_TL_CACHE.g_tl_trip_rows.LAST,
5439 p_output_type =>'P',
5440 p_request_id =>NULL,
5441 p_allocate_flag=>'N',
5442 x_lane_sched_sum_rows=>l_lane_sched_sum_rows,
5443 x_lane_sched_curr_rows=>l_lane_sched_curr_rows,
5444 x_output_cost_tab =>l_output_tab,
5445 x_exceptions_tab =>l_exceptions_tab,
5446 x_trip_charges_tab =>l_trip_charges_tab,
5447 x_stop_charges_tab =>l_stop_charges_tab,
5448 x_return_status =>l_return_status);
5449
5450 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
5451 THEN
5452 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
5453 THEN
5454 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'TL_Rate_Cached_Trip_Multiple has failed');
5455 raise FTE_FREIGHT_PRICING_UTIL.g_tl_rate_cached_trip_fail;
5456 END IF;
5457 END IF;
5458
5459 Populate_Summary_Rates(
5460 p_lane_rows=>l_exploded_lane_rows,
5461 p_schedule_rows=>l_exploded_schedule_rows,
5462 p_vehicle_rows=>l_exploded_vehicle_rows,
5463 p_start_trip_index=>FTE_TL_CACHE.g_tl_trip_rows.FIRST,
5464 p_end_trip_index=>FTE_TL_CACHE.g_tl_trip_rows.LAST,
5465 p_exceptions_tab=>l_exceptions_tab,
5466 x_lane_sched_sum_rows=>l_lane_sched_sum_rows,
5467 x_lane_sched_curr_rows=>l_lane_sched_curr_rows,
5468 x_summary_cache_ref=>l_summary_cache_ref,
5469 x_return_status=>l_return_status);
5470
5471 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
5472 THEN
5473 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
5474 THEN
5475 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Populate_Summary_Rates has failed');
5476 raise FTE_FREIGHT_PRICING_UTIL.g_tl_populate_summary_fail;
5477 END IF;
5478 END IF;
5479
5480
5481
5482
5483 TL_Filter_For_Vehicle_Cost(
5484 p_lane_sched_sum_rows=>l_lane_sched_sum_rows,
5485 p_lane_sched_curr_rows=>l_lane_sched_curr_rows,
5486 p_ref_rows=>l_exploded_ref_rows,
5487 p_LCSS_flag=>'Y',
5488 x_filtered_list_flag=>l_filtered_rows,
5489 x_least_index=>l_least_index,
5490 x_return_status=>l_return_status);
5491
5492 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
5493 THEN
5494 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
5495 THEN
5496
5497 raise FTE_FREIGHT_PRICING_UTIL.g_tl_om_filt_least_veh_fail;
5498 END IF;
5499 END IF;
5500
5501
5502 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'l_least_index:'||l_least_index);
5503
5504 --l_least_index is the least index in the summary rates, this may not correspond to the cache index
5505 --Some lanes for which rates were null may not have been cached. This could result in the l_least_index
5506 -- not being a valid reference into the cache.
5507
5508 IF (l_least_index IS NOT NULL)
5509 THEN
5510
5511 x_trip_index:=l_summary_cache_ref(l_least_index);
5512 END IF;
5513
5514 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'l_least_cache index:'||x_trip_index);
5515
5516 IF(x_trip_index IS NOT NULL)
5517 THEN
5518
5519 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'DBG-1');
5520 x_lane_ref:=l_exploded_ref_rows(l_least_index);
5521 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'DBG-2');
5522 x_vehicle_type:=l_exploded_vehicle_rows(l_least_index);
5523 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'DBG-3');
5524 x_trip_charges_rec:=l_trip_charges_tab(x_trip_index);
5525 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'DBG-4');
5526
5527 x_total_cost:=x_trip_charges_rec.total_trip_rate;
5528
5529 x_currency:=x_trip_charges_rec.currency;
5530
5531 x_stop_charges_tab.delete;
5532
5533 i:=x_trip_charges_rec.stop_charge_reference;
5534 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'DBG-5');
5535 WHILE((FTE_TL_CACHE.g_tl_trip_rows(x_trip_index).number_of_stops > 0) AND
5536 (i<(FTE_TL_CACHE.g_tl_trip_rows(x_trip_index).number_of_stops+x_trip_charges_rec.stop_charge_reference)))
5537 LOOP
5538 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'DBG-6');
5539 x_stop_charges_tab(i):=l_stop_charges_tab(i);
5540 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'DBG-7');
5541 i:=i+1;
5542 END LOOP;
5543
5544
5545 END IF;
5546 END IF;
5547
5548 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'BEGIN_LCSS');
5549
5550 IF (l_warning_count > 0)
5551 THEN
5552 x_return_status:=WSH_UTIL_CORE.G_RET_STS_WARNING;
5553 END IF;
5554
5555
5556 EXCEPTION
5557
5558
5559 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_populate_summary_fail THEN
5560 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5561 FTE_FREIGHT_PRICING_UTIL.set_exception('BEGIN_LCSS',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_populate_summary_fail');
5562 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'BEGIN_LCSS');
5563
5564
5565 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_veh_for_lane_sched_fail THEN
5566 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5567 FTE_FREIGHT_PRICING_UTIL.set_exception('BEGIN_LCSS',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_veh_for_lane_sched_fail');
5568 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'BEGIN_LCSS');
5569
5570 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_build_cache_lcss_fail THEN
5571 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5572 FTE_FREIGHT_PRICING_UTIL.set_exception('BEGIN_LCSS',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_build_cache_lcss_fail');
5573 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'BEGIN_LCSS');
5574
5575
5576 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_rate_cached_trip_fail THEN
5577 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5578 FTE_FREIGHT_PRICING_UTIL.set_exception('BEGIN_LCSS',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_rate_cached_trip_fail');
5579 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'BEGIN_LCSS');
5580
5581 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_om_filt_least_veh_fail THEN
5582 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5583 FTE_FREIGHT_PRICING_UTIL.set_exception('BEGIN_LCSS',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_om_filt_least_veh_fail');
5584 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'BEGIN_LCSS');
5585
5586 WHEN others THEN
5587 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
5588 FTE_FREIGHT_PRICING_UTIL.set_exception('BEGIN_LCSS',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_others');
5589 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
5590 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'BEGIN_LCSS');
5591
5592
5593
5594 END BEGIN_LCSS;
5595
5596
5597 PROCEDURE END_LCSS (
5598 p_trip_index IN NUMBER,
5599 p_trip_charges_rec IN FTE_TL_CACHE.TL_trip_output_rec_type ,
5600 p_stop_charges_tab IN FTE_TL_CACHE.TL_trip_stop_output_tab_type,
5601 x_return_status OUT NOCOPY VARCHAR2)
5602 IS
5603
5604
5605 l_output_cost_tab FTE_FREIGHT_PRICING.Freight_Cost_Temp_Tab_Type;
5606 l_cost_allocation_parameters FTE_TL_COST_ALLOCATION.TL_allocation_params_rec_type;
5607
5608
5609 l_return_status VARCHAR2(1);
5610 l_log_level NUMBER := FTE_FREIGHT_PRICING_UTIL.G_DBG;
5611 l_warning_count NUMBER:=0;
5612
5613 BEGIN
5614
5615
5616 FTE_FREIGHT_PRICING_UTIL.reset_dbg_vars;
5617 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,'END_LCSS','start');
5618
5619 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
5620
5621
5622
5623 FTE_TL_COST_ALLOCATION.Get_Cost_Allocation_Parameters(
5624 x_cost_allocation_parameters => l_cost_allocation_parameters,
5625 x_return_status => l_return_status);
5626
5627 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
5628 THEN
5629 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
5630 THEN
5631 raise FTE_FREIGHT_PRICING_UTIL.g_tl_get_cost_alloc_param_fail;
5632 END IF;
5633 END IF;
5634
5635
5636 l_cost_allocation_parameters.output_type := 'M';
5637
5638 FTE_TL_COST_ALLOCATION.TL_COST_ALLOCATION(
5639 p_trip_index => p_trip_index,
5640 p_trip_charges_rec => p_trip_charges_rec,
5641 p_stop_charges_tab => p_stop_charges_tab,
5642 p_cost_allocation_parameters=> l_cost_allocation_parameters,
5643 x_output_cost_tab => l_output_cost_tab,
5644 x_return_status => l_return_status);
5645
5646
5647 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
5648 THEN
5649 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
5650 THEN
5651
5652 raise FTE_FREIGHT_PRICING_UTIL.g_tl_cost_allocation_fail;
5653 END IF;
5654 END IF;
5655
5656
5657 Update_Pricing_Required_Flag(
5658 p_trip_id=>FTE_TL_CACHE.g_tl_trip_rows(p_trip_index).trip_id,
5659 x_return_status =>l_return_status);
5660
5661 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
5662 THEN
5663 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
5664 THEN
5665 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_WRN,
5666 'Failied to set reprice required flag for TRIP ID:'||FTE_TL_CACHE.g_tl_trip_rows(p_trip_index).trip_id);
5667 END IF;
5668 END IF;
5669
5670
5671 FTE_TL_CACHE.Delete_Cache(x_return_status=>l_return_status);
5672 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
5673 THEN
5674 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
5675 THEN
5676 raise FTE_FREIGHT_PRICING_UTIL.g_tl_delete_cache_fail;
5677 END IF;
5678 END IF;
5679
5680 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'END_LCSS');
5681
5682 IF (l_warning_count > 0)
5683 THEN
5684 x_return_status:=WSH_UTIL_CORE.G_RET_STS_WARNING;
5685 END IF;
5686
5687
5688 EXCEPTION
5689
5690 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_delete_cache_fail THEN
5691 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5692 FTE_FREIGHT_PRICING_UTIL.set_exception('END_LCSS',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_delete_cache_fail');
5693 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'END_LCSS');
5694
5695 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_get_cost_alloc_param_fail THEN
5696 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5697 FTE_FREIGHT_PRICING_UTIL.set_exception('END_LCSS',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_get_cost_alloc_param_fail');
5698 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'END_LCSS');
5699
5700 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_cost_allocation_fail THEN
5701 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5702 FTE_FREIGHT_PRICING_UTIL.set_exception('END_LCSS',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_cost_allocation_fail');
5703 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'END_LCSS');
5704
5705
5706 WHEN others THEN
5707 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
5708 FTE_FREIGHT_PRICING_UTIL.set_exception('END_LCSS',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_others');
5709 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
5710 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'END_LCSS');
5711
5712
5713 END END_LCSS;
5714
5715
5716
5717 PROCEDURE ABORT_LCSS (
5718 x_return_status OUT NOCOPY VARCHAR2)
5719 IS
5720 l_return_status VARCHAR2(1);
5721
5722 l_log_level NUMBER := FTE_FREIGHT_PRICING_UTIL.G_DBG;
5723
5724 l_warning_count NUMBER:=0;
5725 BEGIN
5726
5727 FTE_FREIGHT_PRICING_UTIL.reset_dbg_vars;
5728 FTE_FREIGHT_PRICING_UTIL.set_method(l_log_level,'ABORT_LCSS','start');
5729
5730 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
5731
5732 FTE_TL_CACHE.Delete_Cache(x_return_status=>l_return_status);
5733
5734 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
5735 THEN
5736 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING
5737 THEN
5738 raise FTE_FREIGHT_PRICING_UTIL.g_tl_delete_cache_fail;
5739 END IF;
5740 END IF;
5741
5742 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'ABORT_LCSS');
5743
5744
5745 IF (l_warning_count > 0)
5746 THEN
5747 x_return_status:=WSH_UTIL_CORE.G_RET_STS_WARNING;
5748 END IF;
5749
5750
5751 EXCEPTION
5752
5753 WHEN FTE_FREIGHT_PRICING_UTIL.g_tl_delete_cache_fail THEN
5754 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5755 FTE_FREIGHT_PRICING_UTIL.set_exception('ABORT_LCSS',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_tl_delete_cache_fail');
5756 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'ABORT_LCSS');
5757
5758 WHEN others THEN
5759 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
5760 FTE_FREIGHT_PRICING_UTIL.set_exception('ABORT_LCSS',FTE_FREIGHT_PRICING_UTIL.G_ERR,'g_others');
5761 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
5762 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,'ABORT_LCSS');
5763
5764
5765 END ABORT_LCSS;
5766
5767 PROCEDURE LCSS (
5768 p_trip_id IN NUMBER,
5769 p_lane_rows IN dbms_utility.number_array ,
5770 x_return_status OUT NOCOPY VARCHAR2)
5771 IS
5772 BEGIN
5773
5774 NULL;
5775
5776 END LCSS;
5777
5778
5779
5780 END FTE_TL_RATING;