[Home] [Help]
PACKAGE: APPS.FTE_TL_CACHE
Source
1 PACKAGE FTE_TL_CACHE AUTHID CURRENT_USER AS
2 /* $Header: FTEVTLCS.pls 120.1 2007/11/30 05:57:45 sankarun ship $ */
3
4 FAKE_DLEG_ID NUMBER:=-1;
5 FAKE_TRIP_ID NUMBER:= -1;
6 FAKE_STOP_ID_1 NUMBER:=-1;
7 FAKE_STOP_ID_2 NUMBER:=-2;
8 FAKE_LOCATION_ID_1 NUMBER:=-1;
9 FAKE_LOCATION_ID_2 NUMBER:=-2;
10
11
12 TYPE TL_trip_data_input_rec_type IS RECORD (
13
14 trip_id NUMBER,
15 lane_id NUMBER,
16 schedule_id NUMBER,
17 service_type VARCHAR2(30),
18 carrier_id NUMBER,
19 mode_of_transport VARCHAR2(30),
20 vehicle_type NUMBER,
21 price_list_id NUMBER,
22 loaded_distance NUMBER,
23 unloaded_distance NUMBER,
24 number_of_pallets NUMBER,
25 number_of_containers NUMBER,
26 time NUMBER,
27 number_of_stops NUMBER,
28 total_trip_distance NUMBER,
29 total_direct_distance NUMBER,
30 distance_method VARCHAR2(30),
31 total_weight NUMBER,
32 total_volume NUMBER,
33 continuous_move VARCHAR2(1),
34 planned_departure_date DATE,
35 planned_arrival_date DATE,
36 dead_head VARCHAR2(1),
37 stop_reference NUMBER,
38 delivery_leg_reference NUMBER,
39 child_dleg_reference NUMBER);
40
41 TYPE TL_trip_data_input_tab_type IS TABLE OF TL_trip_data_input_rec_type INDEX BY BINARY_INTEGER;
42
43
44 TYPE TL_TRIP_STOP_INPUT_REC_TYPE IS RECORD(
45
46
47 stop_id NUMBER,
48 trip_id NUMBER,
49 location_id NUMBER,
50 weekday_layovers NUMBER,
51 weekend_layovers NUMBER,
52 distance_to_next_stop NUMBER,
53 time_to_next_stop NUMBER,
54 pickup_weight NUMBER,
55 pickup_volume NUMBER,
56 pickup_pallets NUMBER,
57 pickup_containers NUMBER,
58 loading_protocol VARCHAR2(30),
59 dropoff_weight NUMBER,
60 dropoff_volume NUMBER,
61 dropoff_pallets NUMBER,
62 dropoff_containers NUMBER,
63 stop_region NUMBER,
64 stop_zone VARCHAR2(30),
65 planned_arrival_date DATE,
66 planned_departure_date DATE,
67 stop_type VARCHAR2(30),--either PU,DO,PD,NA
68 physical_stop_id NUMBER, --added for dummy stop fix
69 physical_location_id NUMBER, --added for dummy stop fix
70 fac_pickup_weight NUMBER,
71 fac_pickup_volume NUMBER,
72 fac_dropoff_weight NUMBER,
73 fac_dropoff_volume NUMBER,
74 fac_charge_basis VARCHAR2(30),
75 fac_handling_time NUMBER,
76 fac_currency VARCHAR2(30),
77 fac_modifier_id NUMBER,
78 fac_pricelist_id NUMBER,
79 fac_weight_uom_class VARCHAR2(30),
80 fac_weight_uom VARCHAR2(30),
81 fac_volume_uom_class VARCHAR2(30),
82 fac_volume_uom VARCHAR2(30),
83 fac_distance_uom_class VARCHAR2(30),
84 fac_distance_uom VARCHAR2(30),
85 fac_time_uom_class VARCHAR2(30),
86 fac_time_uom VARCHAR2(30));
87
88
89 TYPE TL_TRIP_STOP_INPUT_TAB_TYPE IS TABLE OF TL_TRIP_STOP_INPUT_REC_TYPE INDEX BY BINARY_INTEGER;
90
91 TYPE TL_CARRIER_PREF_REC_TYPE IS RECORD(
92
93 carrier_id NUMBER,
94 max_out_of_route NUMBER,
95 min_cm_distance NUMBER,
96 min_cm_time NUMBER,
97 cm_free_dh_mileage NUMBER,
98 cm_first_load_discount_flag VARCHAR2(1),
99 currency VARCHAR2(30),
100 cm_rate_variant VARCHAR2(30),
101 unit_basis VARCHAR2(30),
102 weight_uom_class VARCHAR2(30),
103 weight_uom VARCHAR2(30),
104 volume_uom_class VARCHAR2(30),
105 volume_uom VARCHAR2(30),
106 distance_uom_class VARCHAR2(30),
107 distance_uom VARCHAR2(30),
108 time_uom_class VARCHAR2(30),
109 time_uom VARCHAR2(30),
110 region_level VARCHAR2(30),
111 distance_calculation_method VARCHAR2(30),
112 dim_factor NUMBER,
113 dim_weight_uom VARCHAR2(30),
114 dim_volume_uom VARCHAR2(30),
115 dim_length_uom VARCHAR2(30),
116 dim_min_volume NUMBER
117 );
118
119 TYPE TL_CARRIER_PREF_TAB_TYPE IS TABLE OF TL_CARRIER_PREF_REC_TYPE INDEX BY BINARY_INTEGER;
120
121
122
123 TYPE TL_delivery_leg_rec_type IS RECORD(
124
125 delivery_leg_id NUMBER,
126 trip_id NUMBER,
127 delivery_id NUMBER,
128 pickup_stop_id NUMBER,
129 pickup_location_id NUMBER,
130 dropoff_stop_id NUMBER,
131 dropoff_location_id NUMBER,
132 weight NUMBER,
133 volume NUMBER,
134 pallets NUMBER,
135 containers NUMBER,
136 distance NUMBER,
137 direct_distance NUMBER,
138 parent_dleg_id NUMBER,--MDC
139 children_weight NUMBER,--MDC
140 children_volume NUMBER,--MDC
141 is_parent_dleg VARCHAR2(1),-- MDC 'Y' or 'N'
142 parent_with_no_consol_lpn VARCHAR2(1) --MDC 'Y' or 'N'
143 );
144
145 TYPE TL_delivery_leg_tab_type IS TABLE OF TL_delivery_leg_rec_type INDEX BY BINARY_INTEGER;
146
147 TYPE TL_DLV_DETAIL_MAP_REC_TYPE IS RECORD(
148 delivery_id NUMBER,
149 delivery_detail_id NUMBER);
150
151 TYPE TL_DLV_DETAIL_MAP_TAB_TYPE IS TABLE OF TL_DLV_DETAIL_MAP_REC_TYPE INDEX BY BINARY_INTEGER;
152
153
154
155
156
157 TYPE TL_TRIP_OUTPUT_REC_TYPE IS RECORD (
158 trip_id NUMBER,
159 base_dist_load_chrg NUMBER,
160 base_dist_load_unit_chrg NUMBER,
161 base_dist_unload_chrg NUMBER,
162 base_dist_unload_unit_chrg NUMBER,
163
164 base_unit_chrg NUMBER,
165 base_unit_unit_chrg NUMBER,
166
167 base_time_chrg NUMBER,
168 base_time_unit_chrg NUMBER,
169
170 base_flat_chrg NUMBER,
171
172 stop_off_chrg NUMBER,
173 out_of_route_chrg NUMBER,
174 document_chrg NUMBER,
175 handling_chrg NUMBER,
176 handling_chrg_basis NUMBER,
177 fuel_chrg NUMBER,
178 cm_discount_percent NUMBER,
179 cm_discount_value NUMBER,
180 currency VARCHAR2(30),
181 total_trip_rate NUMBER,
182 stop_charge_reference NUMBER --pointer to the stop charge to handle multiple
183 );
184
185
186 TYPE TL_TRIP_OUTPUT_TAB_TYPE IS TABLE OF TL_TRIP_OUTPUT_REC_TYPE INDEX BY BINARY_INTEGER;
187
188
189 TYPE TL_TRIP_STOP_OUTPUT_REC_TYPE IS RECORD (
190 stop_id NUMBER,
191 trip_id NUMBER,
192 weekday_layover_chrg NUMBER,
193 weekend_layover_chrg NUMBER,
194 loading_chrg NUMBER,
195 loading_chrg_basis NUMBER,
196 ast_loading_chrg NUMBER,
197 ast_loading_chrg_basis NUMBER,
198 unloading_chrg NUMBER,
199 unloading_chrg_basis NUMBER,
200 ast_unloading_chrg NUMBER,
201 ast_unloading_chrg_basis NUMBER,
202 origin_surchrg NUMBER,
203 destination_surchrg NUMBER,
204 fac_loading_chrg NUMBER,
205 fac_loading_chrg_basis NUMBER,
206 fac_ast_loading_chrg NUMBER,
207 fac_ast_loading_chrg_basis NUMBER,
208 fac_unloading_chrg NUMBER,
209 fac_unloading_chrg_basis NUMBER,
210 fac_ast_unloading_chrg NUMBER,
211 fac_ast_unloading_chrg_basis NUMBER,
212 fac_handling_chrg NUMBER,
213 fac_handling_chrg_basis NUMBER,
214 fac_currency VARCHAR2(30) );
215
216
217 TYPE TL_TRIP_STOP_OUTPUT_TAB_TYPE IS TABLE OF TL_TRIP_STOP_OUTPUT_REC_TYPE INDEX BY BINARY_INTEGER;
218
219
220
221
222 --Delivery Leg Cache
223 g_tl_delivery_leg_rows TL_delivery_leg_tab_type;
224
225 --Trip Cache
226 g_tl_trip_rows TL_trip_data_input_tab_type;
227
228 --Trip Stop Rows
229 g_tl_trip_stop_rows TL_TRIP_STOP_INPUT_TAB_TYPE;
230
231 --Carrier preference cache
232 g_tl_carrier_pref_rows TL_CARRIER_PREF_TAB_TYPE;
233
234
235 g_tl_shipment_line_rows FTE_FREIGHT_PRICING.shipment_line_tab_type;
236
237 --The following hash and map , are the equivalent of wsh_delivery_assignments, they capture which dlv-details are assigned to which dlv
238
239 --Delivery Detail map cache
240 --This contains a list dlv-details. All the dlv-details belonging to a dlv are contiguous.Given an index to the first dlv-dtl of a dlv
241 -- all the dlv-dtls for that dlv can be found by sequentially accessing records until dlv-dtl belonging to another dlv is found.
242 g_tl_delivery_detail_map TL_DLV_DETAIL_MAP_TAB_TYPE;
243
244 --Delivery to delivery detail hash
245
246 -- This is a hash indexed by delivery id, it returns an index into the Delivery detail map cache. It points to the begining of the list of
247 --dlv-details belonging to that dlv
248 g_tl_delivery_detail_hash DBMS_UTILITY.NUMBER_ARRAY;
249
250
251 --For MDC, all containers that are above the top level detail of deliveries
252 g_tl_int_shipment_line_rows FTE_FREIGHT_PRICING.shipment_line_tab_type;
253
254 --This will hold the child delivery legs information.
255 --These delivery legs will not be in g_tl_delivery_leg_rows.
256 --The weight will always be non-dimensional.
257 g_tl_chld_delivery_leg_rows TL_delivery_leg_tab_type;
258
259
260 PROCEDURE TL_Build_Cache_For_Move(
261 p_fte_move_id IN NUMBER,
262 x_return_status OUT NOCOPY VARCHAR2);
263
264 PROCEDURE TL_Build_Cache_For_Trip(
265 p_wsh_trip_id IN NUMBER,
266 x_return_status OUT NOCOPY VARCHAR2);
267
268 PROCEDURE TL_Build_Cache_For_Delivery(
269 p_wsh_new_delivery_id IN NUMBER,
270 p_wsh_delivery_leg_id IN NUMBER ,
271 p_lane_rows IN DBMS_UTILITY.NUMBER_ARRAY,
272 p_schedule_rows IN DBMS_UTILITY.NUMBER_ARRAY,
273 x_return_status OUT NOCOPY VARCHAR2);
274
275 PROCEDURE TL_Build_Cache_For_OM(
276 x_return_status OUT NOCOPY VARCHAR2);
277
278 PROCEDURE TL_BUILD_CACHE_FOR_TRP_COMPARE(
279 p_wsh_trip_id IN Number ,
280 p_lane_rows IN dbms_utility.number_array ,
281 p_schedule_rows IN dbms_utility.number_array,
282 p_vehicle_rows IN dbms_utility.number_array,
283 x_return_status OUT NOCOPY Varchar2);
284
285
286 PROCEDURE TL_BUILD_CACHE_FOR_LCS(
287 p_wsh_trip_id IN Number ,
288 p_lane_rows IN dbms_utility.number_array ,
289 p_schedule_rows IN dbms_utility.number_array ,
290 p_vehicle_rows IN dbms_utility.number_array,
291 x_return_status OUT NOCOPY Varchar2);
292
293
294
295 PROCEDURE Delete_Cache(x_return_status OUT NOCOPY VARCHAR2);
296
297 PROCEDURE Display_Cache;
298
299 PROCEDURE get_approximate_distance_time(
300 p_from_location_id IN NUMBER,
301 p_to_location_id IN NUMBER,
302 x_distance OUT NOCOPY NUMBER,
303 x_distance_uom OUT NOCOPY VARCHAR2,
304 x_transit_time OUT NOCOPY NUMBER,
305 x_transit_time_uom OUT NOCOPY VARCHAR2,
306 x_return_status OUT NOCOPY VARCHAR2);
307
308
309 PROCEDURE FPA_Get_Trip_Info(
310 p_trip_id IN NUMBER,
311 x_distance OUT NOCOPY NUMBER,
312 x_distance_uom OUT NOCOPY VARCHAR2,
313 x_weight OUT NOCOPY VARCHAR2,
314 x_weight_uom OUT NOCOPY VARCHAR2,
315 x_return_status OUT NOCOPY VARCHAR2);
316
317
318 PROCEDURE TL_BUILD_CACHE_FOR_ESTIMATE(
319 p_lane_rows IN dbms_utility.number_array ,
320 p_schedule_rows IN dbms_utility.number_array,
321 p_vehicle_rows IN dbms_utility.number_array,
322 p_pickup_location_id IN NUMBER,
323 p_dropoff_location_id IN NUMBER,
324 p_ship_date IN DATE,
325 p_delivery_date IN DATE,
326 p_weight IN NUMBER,
327 p_weight_uom IN VARCHAR2,
328 p_volume IN NUMBER,
329 p_volume_uom IN VARCHAR2,
330 p_distance IN NUMBER,
331 p_distance_uom in VARCHAR2,
332 x_return_status OUT NOCOPY Varchar2,
333 --Bug 6625274
334 p_origin_id IN NUMBER DEFAULT NULL,
335 p_destination_id IN NUMBER DEFAULT NULL);
336
337
338 PROCEDURE Get_Vehicle_Type(
339 p_trip_id IN NUMBER,
340 p_vehicle_item_id IN NUMBER,
341 x_vehicle_type IN OUT NOCOPY NUMBER,
342 x_return_status OUT NOCOPY VARCHAR2);
343
344
345 PROCEDURE Calculate_Dimensional_Weight(
346 p_carrier_pref_rec IN TL_CARRIER_PREF_REC_TYPE,
347 p_weight IN NUMBER,
348 p_volume IN NUMBER,
349 x_dim_weight IN OUT NOCOPY NUMBER,
350 x_return_status OUT NOCOPY VARCHAR2);
351
352
353
354
355 PROCEDURE TL_BUILD_CACHE_FOR_DLV_COMPARE(
356 p_wsh_delivery_id IN Number ,
357 p_lane_rows IN dbms_utility.number_array ,
358 p_schedule_rows IN dbms_utility.number_array,
359 p_vehicle_rows IN dbms_utility.number_array,
360 p_dep_date IN DATE DEFAULT sysdate,
361 p_arr_date IN DATE DEFAULT sysdate,
362 p_pickup_location_id IN NUMBER,
363 p_dropoff_location_id IN NUMBER,
364 x_return_status OUT NOCOPY Varchar2);
365
366
367 PROCEDURE TL_BUILD_CACHE_FOR_OM(
368 p_source_header_rec IN FTE_PROCESS_REQUESTS.fte_source_header_rec,
369 p_source_lines_tab IN FTE_PROCESS_REQUESTS.fte_source_line_tab,
370 p_lane_rows IN dbms_utility.number_array ,
371 p_schedule_rows IN dbms_utility.number_array,
372 p_vehicle_rows IN dbms_utility.number_array,
373 x_return_status OUT NOCOPY Varchar2);
374
375
376
377 END FTE_TL_CACHE;