1 PACKAGE BODY FTE_ESTIMATE_FREIGHT_RATE AS
2 /* $Header: FTEEFREB.pls 120.2 2005/07/26 14:06:56 schennal noship $ */
3 /*
4 -- Global constants
5 -- +======================================================================+
6 -- Procedure :
7 -- Freight Estimate Rate Search
8 --
9 -- Description:
10 -- Call the Process Lines API to get the service and rates
11 -- for the given inputs
12 -- Apply discount/markup on top of the recieved rates.
13 -- Inputs:
14 -- rate input parameters ( self explained )
15 -- Output:
16 -- Insert the rows into Temp Table to populate the rows in the UI.
17 -- Status, and messages data
18 -- DB:
19 -- +======================================================================+
20 */
21
22 --
23 G_PKG_NAME CONSTANT VARCHAR2(50) := 'FTE_ESTIMATE_FREIGHT_RATE';
24 --
25
26 PROCEDURE Rate_Search(
27 p_init_msg_list IN VARCHAR2 default fnd_api.g_false,
28 p_api_version_number IN NUMBER default 1.0,
29 p_origin IN VARCHAR2,
30 p_destination IN VARCHAR2,
31 p_org_location_id IN NUMBER,
32 p_dest_location_id IN NUMBER,
33 p_org_country IN VARCHAR2,
34 p_dest_country IN VARCHAR2,
35 p_weight IN NUMBER,
36 p_weight_uom IN VARCHAR2,
37 p_volume IN NUMBER,
38 p_volume_uom IN VARCHAR2,
39 p_distance IN NUMBER,
40 p_distance_uom IN VARCHAR2,
41 p_show_ltl_rates_flag IN VARCHAR2,
42 p_show_tl_rates_flag IN VARCHAR2,
43 p_show_parcel_rates_flag IN VARCHAR2,
44 p_ship_date IN VARCHAR2,
45 p_del_date IN VARCHAR2,
46 p_carrier_id IN NUMBER,
47 p_service_level IN VARCHAR2,
48 p_md_type IN VARCHAR2, -- Markup / Discount Type (M/D)
49 p_md_percent IN NUMBER,
50 p_commodity_catg_id IN NUMBER,
51 p_vehicle_type_id IN NUMBER,
52 x_msg_count OUT NOCOPY NUMBER,
53 x_msg_data OUT NOCOPY VARCHAR2,
54 x_return_status OUT NOCOPY VARCHAR2)
55 is
56
57 l_vehicle_type_id number;
58
59 -- Define cursor to get the item name for the given inv.item.id
60 -- Here vehicle type id considered as inv item id
61 -- This is used to get the Vehicle type as INPUT parameter only
62 cursor c_get_vehicle_name(p_vehicle_type_id number) is
63 SELECT m.concatenated_segments
64 FROM mtl_system_items_vl m
65 WHERE m.inventory_item_id = p_vehicle_type_id
66 AND m.organization_id in ( select p.master_organization_id
67 from mtl_parameters p)
68 AND rownum = 1;
69
70 -- Here vehicle type id considered as vehicle type id itself
71 -- This is used to get the Vehicle type as OUTPUT parameter only
72 cursor c_get_vehicle_type_name(l_vehicle_type_id number) is
73 select mtl.concatenated_segments VehicleName
74 from mtl_system_items_kfv mtl, fte_vehicle_types veh
75 where mtl.vehicle_item_flag = 'Y'
76 and mtl.inventory_item_id = veh.inventory_item_id
77 and veh.vehicle_type_id = l_vehicle_type_id
78 and rownum=1;
79
80 -- Define cursor to get the carrier name
81 cursor c_get_carrier_name(p_carrier_id number) is
82 select party_name from hz_parties
83 where party_id = p_carrier_id;
84
85 -- define cursor for global parameter for Volume
86 CURSOR c_get_volume_uom IS
87 SELECT GU_VOLUME_UOM
88 FROM wsh_global_parameters;
89
90 -- Define cursor to get lookup meaning for Mode and Service
91 cursor c_lookup_meaning (p_lookup_type varchar2, p_lookup_code varchar2 ) is
92 select meaning
93 from fnd_lookup_values_vl
94 where lookup_type = p_lookup_type
95 and nvl(start_date_active, sysdate) <= sysdate
96 and nvl(end_date_active, sysdate) >= sysdate
97 and lookup_code = p_lookup_code
98 and enabled_flag='Y';
99
100 l_modes varchar2(3);
101 l_count number;
102 l_line_count number;
103 /* declare the variables to call process_lines */
104
105 l_source_line_tab FTE_PROCESS_REQUESTS.fte_source_line_tab;
106 l_source_header_tab FTE_PROCESS_REQUESTS.fte_source_header_tab;
107 l_source_type VARCHAR2(10) := 'FTE';
108 l_action VARCHAR2(30) := 'GET_ESTIMATE_RATE';
109 l_source_line_rates_tab FTE_PROCESS_REQUESTS.fte_source_line_rates_tab;
110 l_source_header_rates_tab FTE_PROCESS_REQUESTS.fte_source_header_rates_tab;
111 l_return_status VARCHAR2(1);
112 l_msg_count NUMBER;
113 l_msg_data VARCHAR2(2000);
114 l_org_location_id NUMBER := p_org_location_id;
115 l_dest_location_id NUMBER := p_dest_location_id;
116
117 -- Define orign/destination variables to split into city/state/zip code
118 l_origin_state varchar2(500);
119 l_origin_city varchar2(500);
120 l_origin_zip varchar2(500);
121 l_destination_state varchar2(500);
122 l_destination_city varchar2(500);
123 l_destination_zip varchar2(500);
124
125 /* declare local variables to apply the discounts and getting the meanings of codes/ids */
126
127 l_del_date DATE;
128 l_ship_date DATE;
129 l_rate_temp_id number;
130 l_carrier VARCHAR2(60);
131 l_prev_carrier_id NUMBER;
132 l_volume_uom varchar2(3);
133 l_carrier_id NUMBER;
134 l_mode_of_transport VARCHAR2(30);
135 l_prev_mode_of_transport VARCHAR2(30);
136 l_mode_of_transport_parcel VARCHAR2(30);
137 l_mode_of_transport_tl VARCHAR2(30);
138 l_mode_of_transport_ltl VARCHAR2(30);
139 l_mode_of_transport_meaning varchar2(80);
140 l_service_level VARCHAR2(60);
141 l_prev_service_level VARCHAR2(30);
142 l_vehicle_type VARCHAR2(60);
143 l_base_price number ;
144 l_accessorial_charges number ;
145 l_estimated_rate number;
146 l_markup_discount_value number;
147 l_base_price_c varchar2(60);
148 l_estimated_rate_c varchar2(60);
149 l_accessorial_charges_c varchar2(60);
150 l_currency_code VARCHAR2(30);
151 l_markup_discount_value_c varchar2(60);
152 l_est_transit_time NUMBER;
153 l_time_feasible_flag VARCHAR2(1);
154
155 l_org_error_flag varchar2(1);
156
157 l_precision number;
158 l_ext_precision number;
159 l_min_acct_unit number;
160
161 -- Define exception variables
162 e_process_lines_api_failed EXCEPTION;
163 e_no_data_found EXCEPTION;
164 e_invalid_carrier EXCEPTION;
165 e_invalid_vehicle EXCEPTION;
166 e_invalid_service_level EXCEPTION;
167 e_invalid_mode_of_transport EXCEPTION;
168 e_failed_split_csz_api EXCEPTION;
169 e_org_is_too_long EXCEPTION;
170 e_dest_is_too_long EXCEPTION;
171 e_org_dest_is_too_long EXCEPTION;
172 --
173 l_debug_on BOOLEAN;
174 l_debugfile varchar2(2000);
175
176 --
177 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || 'RATE_SEARCH';
178 --
179 BEGIN
180 --
181 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
182 --
183 IF l_debug_on IS NULL
184 THEN
185 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
186 END IF;
187 --
188 --
189 -- Debug Statements
190 --
191 IF l_debug_on THEN
192 fnd_profile.get('WSH_DEBUG_LOG_DIRECTORY',l_debugfile);
193 l_debugfile := l_debugfile||'/'||WSH_DEBUG_SV.g_file;
194
195 WSH_DEBUG_SV.push(l_module_name);
196 WSH_DEBUG_SV.log(l_module_name,'Begin of the process ',l_debugfile);
197
198 END IF;
199 --
200 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
201
202 -- delete to refresh the data from Global Temp Table
203 delete from FTE_FREIGHT_ESTIMATE_TEMP;
204 -- initialize old message from stack
205 FND_MSG_PUB.initialize;
206 -- passing null instead of 0 from UI for those null values
207 if p_volume_uom is null then
208 OPEN c_get_volume_uom;
209 FETCH c_get_volume_uom INTO l_volume_uom;
210 CLOSE c_get_volume_uom;
211 end if;
212
213 if nvl(p_carrier_id,0) = 0 then
214 l_carrier_id := null;
215 else
216 l_carrier_id := p_carrier_id;
217 end if;
218 -- Building the array for multi-modes
219 if p_show_parcel_rates_flag = 'Y' then
220 l_mode_of_transport_parcel := 'PARCEL';
221 l_modes := l_modes||'P';
222 end if;
223 if p_show_tl_rates_flag = 'Y' then
224 l_mode_of_transport_tl := 'TRUCK';
225 l_modes := l_modes||'T';
226 end if;
227 if p_show_ltl_rates_flag = 'Y' then
228 l_mode_of_transport_ltl := 'LTL';
229 l_modes := l_modes||'L';
230 end if;
231
232 l_ship_date := fnd_date.DISPLAYDT_TO_DATE(p_ship_date,'YYYY-MM-DD HH24:MI:SS');
233
234 if p_del_date is null then
235 l_del_date := sysdate;
236 else
237 l_del_date := fnd_date.DISPLAYDT_TO_DATE(p_del_date,'YYYY-MM-DD HH24:MI:SS');
238 --dbms_output.put_line('ship_date-2 :'||l_ship_date);
239 end if;
240 -- Splitting Orgin into City, State and ZIP Code.
241 l_org_error_flag := 'N' ;
242 if nvl(p_org_location_id,0) = 0 then
243 l_org_location_id := null;
244 FTE_ESTIMATE_FREIGHT_RATE.SPLIT_CITY_STATE_ZIP
245 (p_city_state_zip=>p_origin,
246 x_city =>l_origin_city,
247 x_state =>l_origin_state,
248 x_zip =>l_origin_zip,
249 x_return_status=>l_return_status);
250 if l_return_status <> 'S' then
251 --dbms_output.put_line('Error '||sqlerrm );
252 raise e_failed_split_csz_api;
253 end if ;
254 -- Validating that City/State/Zip should not be greater than 30 characters
255 if (length(l_origin_city) > 30 or
256 length(l_origin_state) > 30 or
257 length(l_origin_zip) > 30 ) then
258 --dbms_output.put_line('Error long origin '|| p_dest_location_id);
259
260 l_org_error_flag := 'Y' ;
261
262 if nvl(p_dest_location_id,0) <> 0 then
263 raise e_org_is_too_long;
264 end if;
265 end if;
266 --dbms_output.put_line('Origin : '||p_origin||'-'||l_origin_city||'-'||
267 -- l_origin_state||'-'||l_origin_zip);
268 end if;
269 -- Splitting Destination into City, State and ZIP Code.
270 if nvl(p_dest_location_id,0) = 0 then
271 l_dest_location_id := null;
272 FTE_ESTIMATE_FREIGHT_RATE.SPLIT_CITY_STATE_ZIP
273 (p_city_state_zip=>p_destination,
274 x_city =>l_destination_city,
275 x_state =>l_destination_state,
276 x_zip =>l_destination_zip,
277 x_return_status=>l_return_status);
278 if l_return_status <> 'S' then
279 raise e_failed_split_csz_api;
280 --dbms_output.put_line('Error in .SPLIT_CITY_STATE_ZIP '||sqlerrm );
281 -- dbms_output.put_line('Error '||sqlerrm );
282 end if ;
283 -- Validating that City/State/Zip should not be greater than 30 characters
284 if (length(l_destination_city) > 30 or
285 length(l_destination_state) > 30 or
286 length(l_destination_zip) > 30 ) then
287
288 --dbms_output.put_line('Error long dest ');
289
290 if l_org_error_flag = 'Y' then
291 --dbms_output.put_line('Error both ');
292 raise e_org_dest_is_too_long;
293 else
294
295 --dbms_output.put_line('Error nly dest is long ');
296 raise e_dest_is_too_long;
297 end if;
298 else
299 if l_org_error_flag = 'Y' then
300 raise e_org_is_too_long;
301 end if;
302 end if;
303 --dbms_output.put_line('Dest : '||p_destination||'-'||l_destination_city||'-'|| l_destination_state||'-'||l_destination_zip);
304 end if;
305
306 --dbms_output.put_line('you are here - 1');
307 --for all modes
308 l_count := 1;
309 -- Header Information input
310 --for all modes
311 l_count := 1;
312 -- Header Information input
313 l_source_header_tab(l_count).consolidation_id := l_count;
314 l_source_header_tab(l_count).ship_from_location_id := l_org_location_id;
315 l_source_header_tab(l_count).ship_to_location_id := l_dest_location_id;
316 l_source_header_tab(l_count).ship_date := l_ship_date;
317 l_source_header_tab(l_count).arrival_date := l_del_date;
318 l_source_header_tab(l_count).total_weight := p_weight;
319 l_source_header_tab(l_count).weight_uom_code := p_weight_uom;
320 l_source_header_tab(l_count).total_volume := p_volume;
321 l_source_header_tab(l_count).volume_uom_code := nvl(p_volume_uom,l_volume_uom);
322 l_source_header_tab(l_count).distance := p_distance;
323 l_source_header_tab(l_count).distance_uom := p_distance_uom;
324 l_source_header_tab(l_count).carrier_id := l_carrier_id;
325 l_source_header_tab(l_count).service_level := p_service_level;
326 l_source_header_tab(l_count).origin_country := p_org_country;
327 --dbms_output.put_line('you are here - 1--1');
328 l_source_header_tab(l_count).origin_city := l_origin_city;
329 l_source_header_tab(l_count).origin_state := l_origin_state;
330 l_source_header_tab(l_count).origin_zip := l_origin_zip;
331 --dbms_output.put_line('you are here - 1--2');
332 l_source_header_tab(l_count).destination_country := p_dest_country;
333 l_source_header_tab(l_count).destination_state := l_destination_state;
334 l_source_header_tab(l_count).destination_city := l_destination_city;
335 l_source_header_tab(l_count).destination_zip := l_destination_zip;
336 l_source_header_tab(l_count).commodity_category_id := p_commodity_catg_id;
337 l_source_header_tab(l_count).vehicle_item_id := p_vehicle_type_id;
338 -- Lines Information input
339 l_source_line_tab(l_count).source_type := 'FTE';
340 l_source_line_tab(l_count).source_line_id := l_count;
341 l_source_line_tab(l_count).consolidation_id := l_count;
342 l_source_line_tab(l_count).ship_date := l_ship_date;
343 l_source_line_tab(l_count).arrival_date := l_del_date;
344 l_source_line_tab(l_count).carrier_id := l_carrier_id;
345 l_source_line_tab(l_count).service_level := p_service_level;
346 l_source_line_tab(l_count).weight := p_weight;
347 l_source_line_tab(l_count).weight_uom_code := p_weight_uom;
348 l_source_line_tab(l_count).volume := p_volume;
349 l_source_line_tab(l_count).volume_uom_code := p_volume_uom;
350 l_source_line_tab(l_count).ship_from_location_id := l_org_location_id;
351 l_source_line_tab(l_count).ship_to_location_id := l_dest_location_id;
352 l_source_line_tab(l_count).origin_country := p_org_country;
353 l_source_line_tab(l_count).origin_state := l_origin_state;
354 l_source_line_tab(l_count).origin_city := l_origin_city;
355 l_source_line_tab(l_count).origin_zip := l_origin_zip;
356 l_source_line_tab(l_count).destination_country := p_dest_country;
357 --dbms_output.put_line('you are here - 1-1');
358 l_source_line_tab(l_count).destination_state := l_destination_state;
359 l_source_line_tab(l_count).destination_city := l_destination_city;
360 l_source_line_tab(l_count).destination_zip := l_destination_zip;
361 --dbms_output.put_line('you are here - a1');
362 l_source_line_tab(l_count).distance := p_distance;
363 l_source_line_tab(l_count).distance_uom := p_distance_uom;
364 l_source_line_tab(l_count).vehicle_item_id := p_vehicle_type_id;
365 l_source_line_tab(l_count).commodity_category_id := p_commodity_catg_id;
366 --dbms_output.put_line('you are here - 3');
367
368 /* Get Vehicle Name */
369 --dbms_output.put_line('Vehicle type id :'||p_vehicle_type_id);
370 if p_vehicle_type_id is not null then
371 open c_get_vehicle_name(p_vehicle_type_id);
372 if c_get_vehicle_name%notfound then
373 --dbms_output.put_line('Vehicle type id-2 :'||p_vehicle_type_id);
374 close c_get_vehicle_name;
375 raise e_invalid_vehicle;
376 end if;
377 fetch c_get_vehicle_name into l_vehicle_type;
378 close c_get_vehicle_name;
379 end if;
380 IF l_debug_on THEN
381 WSH_DEBUG_SV.log(l_module_name,'Vehicle Name ',l_vehicle_type);
382 END IF;
383 --dbms_output.put_line('Vehicle name :'||l_vehicle_type);
384 -- Building multiple records in the table based on the no of modes passed.
385 LOOP
386 --dbms_output.put_line('no of modes '||length(l_modes));
387 -- populate the info to all records since the data is same
388 l_source_line_tab(l_count) := l_source_line_tab(1);
389 l_source_header_tab(l_count) := l_source_header_tab(1);
390
391 -- getting the appropriate mode
392 if substr(l_modes,l_count,1) = 'P' then -- PARCEL
393 l_mode_of_transport := l_mode_of_transport_parcel;
394 elsif substr(l_modes,l_count,1) = 'T' then -- TRUCK
395 l_mode_of_transport := l_mode_of_transport_tl;
396 else -- LTL
397 l_mode_of_transport := l_mode_of_transport_ltl;
398 end if;
399 l_source_header_tab(l_count).consolidation_id := l_count;
400 l_source_line_tab(l_count).consolidation_id := l_count;
401 l_source_line_tab(l_count).mode_of_transport := l_mode_of_transport;
402 l_source_header_tab(l_count).mode_of_transport := l_mode_of_transport;
403 -- for next mode
404 l_count :=l_count+1;
405 if length(l_modes)<l_count then
406 exit;
407 end if;
408 END LOOP;
409 --dbms_output.put_line('no of records '||l_source_line_tab.COUNT);
410
411 FTE_PROCESS_REQUESTS.Process_Lines(
412 p_source_line_tab =>l_source_line_tab,
413 p_source_header_tab =>l_source_header_tab,
414 p_source_type =>l_source_type,
415 p_action =>l_action,
416 x_source_line_rates_tab =>l_source_line_rates_tab,
417 x_source_header_rates_tab =>l_source_header_rates_tab,
418 x_return_status =>l_return_status,
419 x_msg_count =>l_msg_count,
420 x_msg_data =>l_msg_data );
421 if l_return_status not in ('S','W') then
422 raise e_process_lines_api_failed;
423 end if;
424 l_count := l_source_header_rates_tab.count;
425 -- No records to process
426 if nvl(l_count,0) = 0 then
427 raise e_no_data_found;
428 end if;
429 l_count := l_source_header_rates_tab.FIRST;
430 IF l_debug_on THEN
431 WSH_DEBUG_SV.log(l_module_name,'No of records header : ',l_source_header_rates_tab.count);
432 WSH_DEBUG_SV.log(l_module_name,'No of records lines : ',l_source_line_rates_tab.count);
433 END IF;
434
435 LOOP
436 --dbms_output.put_line('you are here - carrier id '||l_source_header_rates_tab(l_count).carrier_id);
437 /* Get Carrier Name */
438 if nvl(l_source_header_rates_tab(l_count).carrier_id,-99) <> nvl(l_prev_carrier_id,-99) then
439 open c_get_carrier_name(l_source_header_rates_tab(l_count).carrier_id);
440 if c_get_carrier_name%notfound then
441 close c_get_carrier_name;
442 raise e_invalid_carrier;
443 end if;
444 fetch c_get_carrier_name into l_carrier;
445 close c_get_carrier_name;
446 l_prev_carrier_id := l_source_header_rates_tab(l_count).carrier_id;
447 end if;
448 --dbms_output.put_line('you are here - 5');
449 /* Get Vehicle Name */
450 if ((p_vehicle_type_id is null or p_vehicle_type_id = 0) and l_source_header_rates_tab(l_count).vehicle_type_id is not null) then
451 if nvl(l_vehicle_type_id,-99) <> nvl(l_source_header_rates_tab(l_count).vehicle_type_id,-99) then
452 --{
453 l_vehicle_type := null;
454 l_vehicle_type_id := l_source_header_rates_tab(l_count).vehicle_type_id;
455 --dbms_output.put_line('Vehicle id :'||l_vehicle_type_id||'-'||l_vehicle_type);
456 open c_get_vehicle_type_name(l_vehicle_type_id);
457 if c_get_vehicle_type_name%notfound then
458 close c_get_vehicle_type_name;
459 raise e_invalid_vehicle;
460 end if;
461 fetch c_get_vehicle_type_name into l_vehicle_type;
462 close c_get_vehicle_type_name;
463 end if;
464 end if;
465 --dbms_output.put_line('Vehicle name-2 :'||l_vehicle_type_id||'-'||l_vehicle_type);
466 /*Lookup Types-- 'WSH_SERVICE_LEVELS', 'WSH_MODE_OF_TRANSPORT' */
467 /* Get Service Level*/
468 if nvl(l_prev_service_level,'XX') <> nvl(l_source_header_rates_tab(l_count).service_level,'XX') then
469 open c_lookup_meaning('WSH_SERVICE_LEVELS',
470 l_source_header_rates_tab(l_count).service_level);
471 if c_lookup_meaning%notfound then
472 close c_lookup_meaning;
473 raise e_invalid_service_level;
474 end if;
475 fetch c_lookup_meaning into l_service_level;
476 close c_lookup_meaning;
477 l_prev_service_level := l_source_header_rates_tab(l_count).service_level;
478 end if;
479 --dbms_output.put_line('you are here - 6');
480 /* Get Mode of Transport */
481 if nvl(l_prev_mode_of_transport,'XX') <> nvl(l_source_header_rates_tab(l_count).mode_of_transport,'XX') then
482 open c_lookup_meaning('WSH_MODE_OF_TRANSPORT',
483 l_source_header_rates_tab(l_count).mode_of_transport);
484 if c_lookup_meaning%notfound then
485 close c_lookup_meaning;
486 raise e_invalid_mode_of_transport;
487 end if;
488 fetch c_lookup_meaning into l_mode_of_transport_meaning;
489 close c_lookup_meaning;
490 l_prev_mode_of_transport :=l_source_header_rates_tab(l_count).mode_of_transport;
491 end if;
492 --dbms_output.put_line('you are here - 7');
493 l_est_transit_time := l_source_header_rates_tab(l_count).transit_time;
494 l_time_feasible_flag := 'N';
495
496 if nvl(l_est_transit_time,0) = 0 or p_del_date is null or
497 ((l_del_date - l_ship_date) >= nvl(l_est_transit_time,0)) then
498 l_time_feasible_flag := 'Y';
499 end if;
500
501 --dbms_output.put_line('you are here - 8');
502 l_currency_code :=l_source_header_rates_tab(l_count).currency ;
503 /* get the rounding factor before the calculation due to 0.01 descrepancy */
504 FND_CURRENCY.GET_INFO(
505 CURRENCY_CODE=>l_currency_code,
506 PRECISION =>l_precision,
507 EXT_PRECISION =>l_ext_precision,
508 MIN_ACCT_UNIT=>l_min_acct_unit);
509 -- For each Line ( BASE Price and Acc Charge)
510 l_line_count := l_source_header_rates_tab(l_count).first_line_index;
511 IF l_debug_on THEN
512 WSH_DEBUG_SV.log(l_module_name,'Mode at header level : ',l_mode_of_transport_meaning);
513 WSH_DEBUG_SV.log(l_module_name,'Carrier at header level : ',l_carrier);
514 WSH_DEBUG_SV.log(l_module_name,'Vehicle at header level : ',l_vehicle_type);
515 WSH_DEBUG_SV.log(l_module_name,'Service at header level : ',l_service_level);
516 END IF;
517 -- initialized the variable for each header
518 l_base_price := 0;
519 l_accessorial_charges := 0;
520 l_estimated_rate := 0;
521 l_markup_discount_value := 0;
522 LOOP
523 --dbms_output.put_line('you are here - line type '||l_source_line_rates_tab(l_line_count).line_type_code);
524 --dbms_output.put_line('you are here - line-consolidationid '||l_source_line_rates_tab(l_line_count).consolidation_id);
525 --dbms_output.put_line('you are here - header-consolidationid '||l_source_header_rates_tab(l_count).consolidation_id);
526 --dbms_output.put_line('you are here - header-lane_id '||l_source_header_rates_tab(l_count).lane_id);
527 --dbms_output.put_line('you are here - line-lane_id '||l_source_line_rates_tab(l_line_count).lane_id);
528 --dbms_output.put_line('you are here - header-carrier_id '||l_source_header_rates_tab(l_count).carrier_id);
529 --dbms_output.put_line('you are here - line-carrier_id '||l_source_line_rates_tab(l_line_count).carrier_id);
530 --dbms_output.put_line('you are here - cost type '||l_source_line_rates_tab(l_line_count).cost_type);
531 --dbms_output.put_line('you are here -base price '||l_source_line_rates_tab(l_line_count).adjusted_price);
532 -- If consolidation id does not match between header and line
533 -- no more rate for the given header
534 if ((l_source_header_rates_tab(l_count).consolidation_id <>
535 l_source_line_rates_tab(l_line_count).consolidation_id)
536 or (l_source_header_rates_tab(l_count).lane_id <>
537 l_source_line_rates_tab(l_line_count).lane_id)
538 or (nvl(l_source_header_rates_tab(l_count).vehicle_type_id,-99) <>
539 nvl(l_source_line_rates_tab(l_line_count).vehicle_type_id,-99))
540 or (nvl(l_source_header_rates_tab(l_count).service_level,'XX') <>
541 nvl(l_source_line_rates_tab(l_line_count).service_level,'XX'))) then
542 EXIT;
543 end if;
544 IF l_debug_on THEN
545 WSH_DEBUG_SV.log(l_module_name,'Lane ID at line level : ',l_source_line_rates_tab(l_line_count).lane_id);
546 WSH_DEBUG_SV.log(l_module_name,'Consol ID at line level : ',l_source_line_rates_tab(l_line_count).consolidation_id);
547 WSH_DEBUG_SV.log(l_module_name,'Carrier at line level : ',l_source_line_rates_tab(l_line_count).carrier_id);
548 WSH_DEBUG_SV.log(l_module_name,'Service at line level : ',l_source_line_rates_tab(l_line_count).service_level);
549 WSH_DEBUG_SV.log(l_module_name,'Vehicle at line level : ',l_source_line_rates_tab(l_line_count).vehicle_type_id);
550 WSH_DEBUG_SV.log(l_module_name,'Cost Type at line level : ',l_source_line_rates_tab(l_line_count).cost_type);
551 WSH_DEBUG_SV.log(l_module_name,'Adjusted Price at line level : ',l_source_line_rates_tab(l_line_count).adjusted_price);
552 END IF;
553
554 --dbms_output.put_line('you are here - 10');
555 /* rounding to 4 decimal */
556 if (l_source_line_rates_tab(l_line_count).line_type_code = 'PRICE' and
557 l_source_line_rates_tab(l_line_count).cost_type = 'FTEPRICE') then
558
559 --l_base_price := round(nvl(l_source_line_rates_tab(l_line_count).base_price,0),l_precision);
560 -- replacing base_price with adjusted_price to apply the discounted price
561 l_base_price := round(nvl(l_source_line_rates_tab(l_line_count).adjusted_price,0),l_precision);
562
563 elsif (l_source_line_rates_tab(l_line_count).line_type_code = 'CHARGE' and
564 l_source_line_rates_tab(l_line_count).cost_type = 'FTECHARGE') then
565
566 l_accessorial_charges := round(nvl(l_source_line_rates_tab(l_line_count).adjusted_price,0),l_precision);
567
568 end if;
569 --dbms_output.put_line('you are here - 11');
570 if l_line_count = l_source_line_rates_tab.LAST then
571 exit;
572 else
573 l_line_count := l_source_line_rates_tab.NEXT(l_line_count);
574 end if;
575
576 END LOOP;
577 -- Calculate Discount Amount
578 --dbms_output.put_line('you are here - 12');
579 if p_md_type is not null then
580 l_markup_discount_value :=round((((l_base_price+l_accessorial_charges)*p_md_percent)/100),l_precision);
581 end if;
582 if p_md_type = 'D' then
583 l_estimated_rate := l_base_price+l_accessorial_charges-l_markup_discount_value;
584 elsif p_md_type = 'M' then
585 l_estimated_rate := l_base_price+l_accessorial_charges+l_markup_discount_value;
586 else
587 l_estimated_rate := l_base_price+l_accessorial_charges;
588 end if;
589 l_estimated_rate := round(l_estimated_rate,l_precision);
590
591 IF l_debug_on THEN
592 WSH_DEBUG_SV.log(l_module_name,'Base Price : ',l_base_price);
593 WSH_DEBUG_SV.log(l_module_name,'Acc.Charge : ',l_accessorial_charges);
594 WSH_DEBUG_SV.log(l_module_name,'Markup-Discount : ',l_markup_discount_value);
595 WSH_DEBUG_SV.log(l_module_name,'Estimated Rate : ',l_estimated_rate);
596 WSH_DEBUG_SV.logmsg(l_module_name,'inserting the row into temp table ');
597 END IF;
598 -- Padding currency with amount columns to display in the UI
599 -- There is no currency conversion since there will be same currency for Charges and Base price
600 /* not required since it is taking care of at UI level
601 l_estimated_rate_c := to_char(l_estimated_rate)||' '||l_currency_code;
602
603 l_base_price_c := to_char(l_base_price)||' '||l_currency_code;
604
605 l_accessorial_charges_c := to_char(l_accessorial_charges)||' '||l_currency_code;
606 if p_md_type = 'D' then
607 END IF;
608 -- Padding currency with amount columns to display in the UI
609 -- There is no currency conversion since there will be same currency for Charges and Base price
610 /* not required since it is taking care of at UI level
611 l_estimated_rate_c := to_char(l_estimated_rate)||' '||l_currency_code;
612
613 l_base_price_c := to_char(l_base_price)||' '||l_currency_code;
614
615 l_accessorial_charges_c := to_char(l_accessorial_charges)||' '||l_currency_code;
616 if p_md_type = 'D' then
617 l_markup_discount_value_c := '( '||l_markup_discount_value||' ) '||l_currency_code;
618 else
619 l_markup_discount_value_c := l_markup_discount_value||' '||l_currency_code;
620 end if;
621 */
622 --dbms_output.put_line('you are here - inserting row for '||l_carrier||' '||l_count);
623 INSERT into FTE_FREIGHT_ESTIMATE_TEMP
624 (
625 RATE_TEMP_ID ,
626 CARRIER ,
627 MODE_OF_TRANSPORT ,
628 SERVICE_LEVEL ,
629 VEHICLE_TYPE ,
630 ESTIMATED_RATE ,
631 BASE_PRICE ,
632 ACCESSORIAL_CHARGES ,
633 MARKUP_DISCOUNT_TYPE ,
634 MARKUP_DISCOUNT_VALUE ,
635 CURRENCY_CODE,
636 EST_TRANSIT_TIME ,
637 TIME_FEASIBLE_FLAG ,
638 CREATED_BY ,
639 CREATION_DATE ,
640 LAST_UPDATED_BY ,
641 LAST_UPDATE_DATE ,
642 LAST_UPDATE_LOGIN
643 )
644 values
645 (
646 FTE_FREIGHT_COSTS_TEMP_S.nextval,
647 l_carrier ,
648 l_mode_of_transport_meaning ,
649 l_service_level ,
650 decode(l_source_header_rates_tab(l_count).mode_of_transport,'TRUCK',l_vehicle_type,null) ,
651 l_estimated_rate ,
652 l_base_price ,
653 l_accessorial_charges ,
654 p_md_type ,
655 l_markup_discount_value ,
656 l_currency_code,
657 l_est_transit_time ,
658 l_time_feasible_flag ,
659 fnd_global.user_id,
660 sysdate ,
661 fnd_global.user_id ,
662 sysdate ,
663 fnd_global.login_id
664 );
665 if (SQL%NOTFOUND) then
666 raise e_no_data_found;
667 end if;
668 --dbms_output.put_line('you are here - mode '||l_source_header_rates_tab(l_count).mode_of_transport);
669 --dbms_output.put_line('you are here - after inserting row for '||l_carrier||' '||l_count);
670 if l_count = l_source_header_rates_tab.LAST then
671 exit;
672 else
673 l_count := l_source_header_rates_tab.NEXT(l_count);
674 end if;
675 END LOOP;
676 x_return_status := l_return_status;
677 --commit;
678 EXCEPTION
679 WHEN e_org_is_too_long then
680 FND_MESSAGE.SET_NAME('FTE','FTE_ORG_TOO_LONG');
681 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
682 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
683
684 WHEN e_dest_is_too_long then
685 FND_MESSAGE.SET_NAME('FTE','FTE_DEST_TOO_LONG');
686 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
687 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
688
689 WHEN e_org_dest_is_too_long then
690 FND_MESSAGE.SET_NAME('FTE','FTE_ORG_DEST_TOO_LONG');
691 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
692 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
693
694 WHEN e_process_lines_api_failed then
695 FND_MESSAGE.SET_NAME('FTE','FTE_PROCESS_LINE_API_FAILED');
696 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
697 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
698 --dbms_output.put_line('API FTE_PROCESS_LINE_API_FAILED call failed '||sqlerrm );
699
700 WHEN e_invalid_service_level THEN
701 FND_MESSAGE.SET_NAME('FTE','FTE_INVALID_SERVICE_LEVEL');
702 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
703 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
704
705 WHEN e_invalid_mode_of_transport THEN
706 FND_MESSAGE.SET_NAME('FTE','FTE_INVALID_MODE');
707 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
708 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
709
710 WHEN e_invalid_carrier THEN
711 FND_MESSAGE.SET_NAME('FTE','FTE_INVALID_CARRIER');
712 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
713 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
714
715 WHEN e_invalid_vehicle THEN
716 FND_MESSAGE.SET_NAME('FTE','FTE_INVALID_VEHICLE');
717 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
718 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
719
720 WHEN e_failed_split_csz_api THEN
721 FND_MESSAGE.SET_NAME('FTE','FTE_FAILED_SPLIT_CSZ_API');
722 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
723 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
724
725 WHEN e_no_data_found THEN
726 FND_MESSAGE.SET_NAME('FTE','FTE_NO_RATE_EXISTS');
727 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
728 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
729
730 WHEN others then
731 wsh_util_core.default_handler('FTE_ESTIMATE_FREIGHT_RATE.RATE_SEARCH');
732 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
733 --dbms_output.put_line('Unhandled Exception '||sqlerrm );
734 END;
735
736 /* This API will be used to split city_state_zip into city state zip
737 INPUTS can be maximum 3 values seperated by comma
738 if there are 3, it will be converted city, state, zip
739 if there are 2, it will be converted state, zip
740 if there are 1, it will be converted zip
741 */
742
743 PROCEDURE SPLIT_CITY_STATE_ZIP (
744 p_city_state_zip IN VARCHAR2,
745 x_city OUT NOCOPY VARCHAR2,
746 x_state OUT NOCOPY VARCHAR2,
747 x_zip OUT NOCOPY VARCHAR2,
748 x_return_status OUT NOCOPY VARCHAR2)
749 IS
750 l_csz varchar2(2000) := p_city_state_zip;
751 l_length number;
752 l_1st_position number;
753 l_2nd_position number;
754 i number;
755 l_zip_exists_flag VARCHAR2(1) := 'N';
756 BEGIN
757 l_length := length(trim(l_csz));
758 l_1st_position := instr(l_csz,',');
759 l_2nd_position := instr(substr(l_csz,l_1st_position+1),',');
760 x_city := trim(substr(l_csz,1,l_1st_position-1));
761 x_state := trim(substr(l_csz,l_1st_position+1,l_2nd_position-1));
762 x_zip := trim(substr(l_csz,l_1st_position+l_2nd_position+1));
763 i := 1;
764 while (i <= length(x_zip) )
765 loop
766 if ascii(substr(x_zip,i,1)) >= 48 and ascii(substr(x_zip,i,1)) <= 57 then
767 l_zip_exists_flag := 'Y';
768 exit;
769 end if;
770 i := i + 1;
771 end loop;
772 if l_zip_exists_flag = 'Y'then
773 --dbms_output.put_line('Ascii value '|| ascii(x_zip));
774 if x_state is null then
775 x_state := x_city;
776 x_city := null;
777 end if;
778 else
779 if x_state is null then
780 x_state := x_zip;
781 x_zip := null;
782 end if;
783 end if;
784 x_return_status:= 'S' ;
785 EXCEPTION
786 WHEN OTHERS
787 THEN
788 x_return_status:= 'E';
789 raise;
790 end;
791
792 END FTE_ESTIMATE_FREIGHT_RATE;
[Home] [Help]