1 PACKAGE FTE_TL_RATING AUTHID CURRENT_USER AS
2 /* $Header: FTEVTLRS.pls 120.1 2007/11/30 06:08:02 sankarun ship $ */
3
4
5
6
7 -- Public Procedures --
8
9 -- +======================================================================+
10 -- Procedure :
11 -- tl_rate_trip
12 --
13 -- Description:
14 -- Rates a trip. To be called from main rating apis.
15 -- Inputs:
16 -- p_trip_id IN NUMBER => a valid wsh trip id
17 -- p_output_type IN VARCHAR2 => 'M'- main table
18 -- 'T'- temp table
19 -- 'P'- plsql table
20 -- p_check_reprice_flag IN VARCHAR2 => 'Y'- checks repirce flag on delivery legs
21 -- 'N'- always rates
22 -- Output:
23 -- x_output_cost_tab OUT NOCOPY
24 -- FTE_FREIGHT_PRICING.Freight_Cost_Temp_Tab_Type
25 -- => Table which may hold the rates for the trip
26 -- if p_output_type = 'P'
27 -- x_return_status OUT NOCOPY VARCHAR2 => Return status
28 --
29 -- Global dependencies:
30 -- No direct
31 --
32 -- DB:
33 -- No direct
34 -- +======================================================================+
35
36 PROCEDURE tl_rate_trip (
37 p_trip_id IN NUMBER ,
38 p_output_type IN VARCHAR2,
39 p_check_reprice_flag IN VARCHAR2 DEFAULT 'N',
40 x_output_cost_tab OUT NOCOPY FTE_FREIGHT_PRICING.Freight_Cost_Temp_Tab_Type ,
41 x_return_status OUT NOCOPY VARCHAR2);
42
43
44 -- +======================================================================+
45 -- Procedure :
46 -- tl_rate_move
47 --
48 -- Description:
49 -- Rates a continuous move.
50 -- Inputs:
51 -- p_fte_move_id IN NUMBER => a valid wsh trip id
52 -- p_output_type IN VARCHAR2 => 'M'- main table
53 -- 'T'- temp table
54 -- 'P'- plsql table
55 -- p_build_cache IN VARCHAR2 => 'Y'- rebuild cache
56 -- 'N'- don't rebuild cache
57 -- Output:
58 -- x_output_cost_tab OUT NOCOPY
59 -- FTE_FREIGHT_PRICING.Freight_Cost_Temp_Tab_Type
60 -- => Table which may hold the rates for the trip
61 -- if p_output_type = 'P'
62 -- x_return_status OUT NOCOPY VARCHAR2 => Return status
63 --
64 -- Global dependencies:
65 -- No direct
66 --
67 -- DB:
68 -- No direct
69 -- +======================================================================+
70
71 PROCEDURE tl_rate_move (
72 p_fte_move_id IN NUMBER ,
73 p_output_type IN VARCHAR2,
74 x_output_cost_tab OUT NOCOPY FTE_FREIGHT_PRICING.Freight_Cost_Temp_Tab_Type ,
75 x_return_status OUT NOCOPY VARCHAR2);
76
77 --rates the given TL trip, using lanes/schedules. Used for multileg search services
78
79 PROCEDURE TL_TRIP_PRICE_COMPARE(
80 p_wsh_trip_id IN Number ,
81 p_lane_rows IN dbms_utility.number_array ,
82 p_schedule_rows IN dbms_utility.number_array,
83 p_vehicle_rows IN dbms_utility.number_array,
84 x_request_id IN OUT NOCOPY NUMBER,
85 x_lane_sched_sum_rows OUT NOCOPY dbms_utility.number_array,
86 x_lane_sched_curr_rows OUT NOCOPY dbms_utility.name_array,
87 x_return_status OUT NOCOPY Varchar2);
88
89
90 -- This copies over rated from fte_freight_cost_temp to wsh_freight_costs
91 -- It deletes the rates from fte_freight_cost_temp
92 --Either the lane id or the schedule id have to be specified.The rates of a trip
93 --are identified by a combination of the lane/schedule id and the comparison request id
94
95 PROCEDURE Move_Records_To_Main(
96 p_trip_id IN NUMBER,
97 p_lane_id IN NUMBER,
98 p_schedule_id IN NUMBER,
99 p_comparison_request_id IN NUMBER,
100 x_return_status OUT NOCOPY VARCHAR2);
101
102 --Deletes all the rates for the trip , from WSH_FREIGHT_COSTS
103
104 PROCEDURE Delete_Main_Records(
105 p_trip_id IN NUMBER,
106 x_return_status OUT NOCOPY VARCHAR2);
107
108
109 --Calculates an estimate of the TL price for the given weight/vol using the lanes/schedules.
110 --Used for Freight Estimate. Pickup/dropoff location can be null.
111 --It returns rates as base price, accessory charges
112
113 PROCEDURE TL_FREIGHT_ESTIMATE(
114 p_lane_rows IN dbms_utility.number_array ,
115 p_schedule_rows IN dbms_utility.number_array,
116 p_vehicle_rows IN dbms_utility.number_array,
117 p_pickup_location_id IN NUMBER,
118 p_dropoff_location_id IN NUMBER,
119 p_ship_date IN DATE,
120 p_delivery_date IN DATE,
121 p_weight IN NUMBER,
122 p_weight_uom IN VARCHAR2,
123 p_volume IN NUMBER,
124 p_volume_uom IN VARCHAR2,
125 p_distance IN NUMBER,
126 p_distance_uom in VARCHAR2,
127 x_lane_sched_base_rows OUT NOCOPY dbms_utility.number_array,
128 x_lane_sched_acc_rows OUT NOCOPY dbms_utility.number_array,
129 x_lane_sched_curr_rows OUT NOCOPY dbms_utility.name_array,
130 x_return_status OUT NOCOPY Varchar2,
131 --Bug 6625274
132 p_origin_id IN NUMBER DEFAULT NULL,
133 p_destination_id IN NUMBER DEFAULT NULL);
134
135
136
137 PROCEDURE Get_Vehicles_For_LaneSchedules(
138 p_trip_id IN NUMBER DEFAULT NULL,
139 p_lane_rows IN dbms_utility.number_array,
140 p_schedule_rows IN dbms_utility.number_array,
141 p_vehicle_rows IN dbms_utility.number_array,
142 x_vehicle_rows OUT NOCOPY dbms_utility.number_array,
143 x_lane_rows OUT NOCOPY dbms_utility.number_array,
144 x_schedule_rows OUT NOCOPY dbms_utility.number_array,
145 x_ref_rows OUT NOCOPY dbms_utility.number_array,
146 x_return_status OUT NOCOPY Varchar2);
147
148
149
150 PROCEDURE TL_DELIVERY_PRICE_COMPARE(
151 p_wsh_delivery_id IN Number ,
152 p_lane_rows IN dbms_utility.number_array ,
153 p_schedule_rows IN dbms_utility.number_array,
154 p_vehicle_rows IN dbms_utility.number_array,
155 p_dep_date IN DATE DEFAULT sysdate,
156 p_arr_date IN DATE DEFAULT sysdate,
157 p_pickup_location_id IN NUMBER,
158 p_dropoff_location_id IN NUMBER,
159 x_request_id IN OUT NOCOPY NUMBER,
160 x_lane_sched_sum_rows OUT NOCOPY dbms_utility.number_array,
161 x_lane_sched_curr_rows OUT NOCOPY dbms_utility.name_array,
162 x_return_status OUT NOCOPY Varchar2);
163
164
165
166 PROCEDURE TL_OM_RATING(
167 p_lane_rows IN dbms_utility.number_array ,
168 p_schedule_rows IN dbms_utility.number_array,
169 p_lane_info_tab IN FTE_FREIGHT_RATING_PUB.lane_info_tab_type,
170 p_source_header_rec IN FTE_PROCESS_REQUESTS.fte_source_header_rec,
171 p_source_lines_tab IN FTE_PROCESS_REQUESTS.fte_source_line_tab,
172 p_LCSS_flag IN VARCHAR2,
173 x_source_header_rates_tab IN OUT NOCOPY FTE_PROCESS_REQUESTS.fte_source_header_rates_tab,
174 x_source_line_rates_tab IN OUT NOCOPY FTE_PROCESS_REQUESTS.fte_source_line_rates_tab,
175 x_return_status OUT NOCOPY Varchar2);
176
177
178
179 PROCEDURE BEGIN_LCSS (
180 p_trip_id IN NUMBER,
181 p_lane_rows IN dbms_utility.number_array ,
182 x_trip_index OUT NOCOPY NUMBER,
183 x_trip_charges_rec OUT NOCOPY FTE_TL_CACHE.TL_trip_output_rec_type ,
184 x_stop_charges_tab OUT NOCOPY FTE_TL_CACHE.TL_trip_stop_output_tab_type,
185 x_total_cost OUT NOCOPY NUMBER,
186 x_currency OUT NOCOPY VARCHAR2,
187 x_vehicle_type OUT NOCOPY NUMBER,
188 x_lane_ref OUT NOCOPY NUMBER,
189 x_return_status OUT NOCOPY VARCHAR2);
190
191
192 PROCEDURE END_LCSS (
193 p_trip_index IN NUMBER,
194 p_trip_charges_rec IN FTE_TL_CACHE.TL_trip_output_rec_type ,
195 p_stop_charges_tab IN FTE_TL_CACHE.TL_trip_stop_output_tab_type,
196 x_return_status OUT NOCOPY VARCHAR2);
197
198
199 PROCEDURE ABORT_LCSS (
200 x_return_status OUT NOCOPY VARCHAR2);
201
202
203 PROCEDURE LCSS (
204 p_trip_id IN NUMBER,
205 p_lane_rows IN dbms_utility.number_array ,
206 x_return_status OUT NOCOPY VARCHAR2);
207
208
209 PROCEDURE Move_Dlv_Records_To_Main(
210 p_dleg_id IN NUMBER,
211 p_lane_id IN NUMBER,
212 p_schedule_id IN NUMBER,
213 p_comparison_request_id IN NUMBER,
214 x_return_status OUT NOCOPY VARCHAR2);
215
216
217
218 END FTE_TL_RATING;
219
220
221
222