[Home] [Help]
PACKAGE BODY: APPS.FTE_TL_CORE
Source
1 PACKAGE BODY FTE_TL_CORE AS
2 /* $Header: FTEVTLOB.pls 120.2 2005/07/25 13:26:29 susurend noship $ */
3
4
5 -- package global declarations
6 G_PKG_NAME VARCHAR2(100) := 'FTE_TL_CORE';
7
8 -- following constants used to identify purpose of request lines
9
10 G_LOADED_DIST_BASE_LINE NUMBER := 1;
11 G_UNLOADED_DIST_BASE_LINE NUMBER := 2;
12 G_CONT_DIST_BASE_LINE NUMBER := 3;
13 G_UNITS_BASE_LINE NUMBER := 4;
14 G_TIME_BASE_LINE NUMBER := 5;
15 G_FLAT_BASE_LINE NUMBER := 6;
16 G_LOAD_CHARGE_LINE NUMBER := 7;
17 G_STOP_CHARGE_LINE NUMBER := 8;
18 G_FACILITY_CHARGE_LINE NUMBER := 9;
19 G_CONT_DH_BASE_LINE NUMBER := 10;
20
21 TYPE req_line_info_rec_type IS RECORD (
22 line_index NUMBER, -- index of qp request line
23 line_type NUMBER, -- identifies purpose of request line
24 trip_index NUMBER, -- index into the trip cache (for multiple)
25 stop_index NUMBER, -- index into stop cache
26 line_qty NUMBER,
27 line_uom VARCHAR2(30),
28 currency VARCHAR2(30), -- lines can have diff. currency
29 lane_id NUMBER,
30 pricelist_id NUMBER,
31 carrier_id NUMBER
32 );
33
34 TYPE req_line_info_tab_type IS TABLE OF req_line_info_rec_type INDEX BY BINARY_INTEGER;
35
36 g_req_line_info_tab req_line_info_tab_type;
37
38 CURSOR get_uom_for_each
39 IS
40 SELECT uom_for_num_of_units
41 FROM wsh_global_parameters;
42
43 -- Procedure declarations
44
45 PROCEDURE create_input_line (
46 p_req_line_rec IN req_line_info_rec_type,
47 p_trip_rec IN FTE_TL_CACHE.TL_trip_data_input_rec_type,
48 p_stop_tab IN FTE_TL_CACHE.TL_trip_stop_input_tab_type,
49 p_carrier_pref IN FTE_TL_CACHE.TL_carrier_pref_rec_type,
50 x_return_status OUT NOCOPY VARCHAR2);
51
52 PROCEDURE create_control_rec ( x_return_status OUT NOCOPY VARCHAR2);
53
54
55 PROCEDURE create_engine_inputs (
56 p_trip_rec IN FTE_TL_CACHE.TL_trip_data_input_rec_type,
57 p_stop_tab IN FTE_TL_CACHE.TL_trip_stop_input_tab_type,
58 p_carrier_pref IN FTE_TL_CACHE.TL_carrier_pref_rec_type,
59 p_trip_index IN NUMBER DEFAULT NULL,
60 x_implicit_non_dummy_cnt OUT NOCOPY NUMBER,
61 x_return_status OUT NOCOPY VARCHAR2);
62
63 PROCEDURE create_line_attributes (
64 p_req_line_rec IN req_line_info_rec_type,
65 p_trip_rec IN FTE_TL_CACHE.TL_trip_data_input_rec_type,
66 p_stop_tab IN FTE_TL_CACHE.TL_trip_stop_input_tab_type,
67 p_carrier_pref IN FTE_TL_CACHE.TL_carrier_pref_rec_type,
68 x_return_status OUT NOCOPY VARCHAR2);
69
70
71 PROCEDURE create_charge_line_attributes (
72 p_req_line_rec IN req_line_info_rec_type,
73 p_trip_rec IN FTE_TL_CACHE.TL_trip_data_input_rec_type,
74 p_stop_tab IN FTE_TL_CACHE.TL_trip_stop_input_tab_type,
75 p_carrier_pref IN FTE_TL_CACHE.TL_carrier_pref_rec_type,
76 x_pricing_attr_tab IN OUT NOCOPY fte_freight_pricing.pricing_attribute_tab_type,
77 x_return_status OUT NOCOPY VARCHAR2);
78
79
80 PROCEDURE create_line_qualifiers (
81 p_req_line_rec IN req_line_info_rec_type,
82 p_trip_rec IN FTE_TL_CACHE.TL_trip_data_input_rec_type,
83 p_stop_tab IN FTE_TL_CACHE.TL_trip_stop_input_tab_type,
84 p_carrier_pref IN FTE_TL_CACHE.TL_carrier_pref_rec_type,
85 x_return_status OUT NOCOPY VARCHAR2);
86
87 PROCEDURE retrieve_qp_output (
88 p_trip_rec IN FTE_TL_CACHE.TL_trip_data_input_rec_type,
89 p_stop_tab IN FTE_TL_CACHE.TL_trip_stop_input_tab_type,
90 p_carrier_pref IN FTE_TL_CACHE.TL_carrier_pref_rec_type,
91 p_qp_output_line_rows IN QP_PREQ_GRP.LINE_TBL_TYPE,
92 p_qp_output_detail_rows IN QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE,
93 x_trip_charges_rec OUT NOCOPY FTE_TL_CACHE.TL_trip_output_rec_type,
94 x_stop_charges_tab OUT NOCOPY FTE_TL_CACHE.TL_trip_stop_output_tab_type,
95 x_return_status OUT NOCOPY VARCHAR2);
96
97 PROCEDURE print_output(
98 p_trip_charges_rec IN FTE_TL_CACHE.TL_trip_output_rec_type,
99 p_stop_charges_tab IN FTE_TL_CACHE.TL_trip_stop_output_tab_type
100 );
101
102 -- This procedure throws a not on pricelist excpetion if
103 -- all the non-dummy(not dummy stop,trip charge lines) qp output lines have an IPL status
104 --
105
106 PROCEDURE check_qp_ipl(p_qp_output_line_rows IN QP_PREQ_GRP.LINE_TBL_TYPE,
107 p_implicit_non_dummy_cnt IN NUMBER,
108 x_return_status OUT NOCOPY VARCHAR2)
109 IS
110 i NUMBER;
111 l_non_dummy_row_count NUMBER;
112 l_dummy_row_count NUMBER;
113 l_dummy_ipl_count NUMBER;
114 l_non_dummy_ipl_count NUMBER;
115 l_line_type NUMBER;
116 l_line_index NUMBER;
117 l_ipl_flag VARCHAR2(1);
118
119 l_log_level NUMBER := fte_freight_pricing_util.G_DBG;
120 l_method_name VARCHAR2(50) := 'check_qp_ipl';
121
122 BEGIN
123 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
124 fte_freight_pricing_util.reset_dbg_vars;
125 fte_freight_pricing_util.set_method(l_log_level,l_method_name);
126
127 -- l_non_dummy_row_count:=0;
128 -- bug 3610889
129 l_non_dummy_row_count:= p_implicit_non_dummy_cnt;
130 l_dummy_row_count:=0;
131 l_dummy_ipl_count:=0;
132 l_non_dummy_ipl_count:=0;
133 i:=p_qp_output_line_rows.FIRST;
134 WHILE (i IS NOT NULL)
135 LOOP
136 IF ((p_qp_output_line_rows(i).status_code IS NOT NULL) AND (p_qp_output_line_rows(i).status_code='IPL'))
137 THEN
138 l_ipl_flag:='Y';
139 ELSE
140 l_ipl_flag:='N';
141 END IF;
142 l_line_index:=p_qp_output_line_rows(i).line_index;
143 IF ((l_line_index IS NOT NULL) AND (g_req_line_info_tab.EXISTS(l_line_index)))
144 THEN
145 l_line_type:=g_req_line_info_tab(l_line_index).line_type;
146 --SUSUREND :classiffy a line as dummy if it is a trip level charge line,stop level charge line
147 --or facility line
148 IF ((l_line_type IS NOT NULL) AND (l_line_type <> G_LOAD_CHARGE_LINE)
149 AND (l_line_type <>G_STOP_CHARGE_LINE ) AND (l_line_type <>G_FACILITY_CHARGE_LINE))
150 THEN
151 l_non_dummy_row_count:=l_non_dummy_row_count+1;
152 IF (l_ipl_flag='Y')
153 THEN
154 l_non_dummy_ipl_count:=l_non_dummy_ipl_count+1;
155 END IF;
156 ELSE
157 l_dummy_row_count:=l_dummy_row_count+1;
158 IF (l_ipl_flag='Y')
159 THEN
160 l_dummy_ipl_count:=l_dummy_ipl_count+1;
161 END IF;
162
163
164 END IF;
165
166 ELSE
167 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Index not found:'||i);
168
169 END IF;
170
171
172 i:=p_qp_output_line_rows.NEXT(i);
173 END LOOP;
174
175 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,' Number of dummy lines:'||l_dummy_row_count
176 ||' Number of non dummy lines:'||l_non_dummy_row_count||' Dummy IPLs:'||l_dummy_ipl_count||
177 ' Non Dummy IPLs :'||l_non_dummy_ipl_count);
178 IF (l_non_dummy_ipl_count >= l_non_dummy_row_count)
179 THEN
180 raise fte_freight_pricing_util.g_not_on_pricelist;
181 END IF;
182
183 fte_freight_pricing_util.unset_method(l_log_level,l_method_name);
184
185 EXCEPTION
186 WHEN fte_freight_pricing_util.g_not_on_pricelist THEN
187 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
188 -- can use tokens here
189 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_not_on_pricelist');
190 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_ERR,'Item quantity not found on pricelist ');
191 fte_freight_pricing_util.unset_method(l_log_level,l_method_name);
192
193 WHEN OTHERS THEN
194 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
195 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_others');
196 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
197 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_method_name);
198
199 END check_qp_ipl;
200
201
202
203 PROCEDURE print_req_line_tab;
204
205 -- +======================================================================+
206 -- Procedure :
207 -- tl_core
208 --
209 -- Description:
210 -- Build the call structure for the pricing engine, invoke the
211 -- rating engine, analyze results, return results.
212 -- Inputs:
213 -- p_trip_rec IN TL_trip_data_input_rec_type
214 -- p_stop_tab IN TL_trip_stop_input_tab_type,
215 -- p_carrier_pref IN TL_carrier_pref_rec_type,
216 -- Output:
217 -- x_trip_charges_rec OUT NOCOPY TL_trip_output_rec_type,
218 -- x_stop_charges_tab OUT NOCOPY TL_trip_stop_output_tab_type,
219 -- x_return_status OUT NOCOPY VARCHAR2
220 --
221 -- Global dependencies:
222 -- No direct
223 --
224 -- DB:
225 -- No direct
226 -- +======================================================================+
227
228 PROCEDURE tl_core (
229 p_trip_rec IN FTE_TL_CACHE.TL_trip_data_input_rec_type,
230 p_stop_tab IN FTE_TL_CACHE.TL_trip_stop_input_tab_type,
231 p_carrier_pref IN FTE_TL_CACHE.TL_carrier_pref_rec_type,
232 x_trip_charges_rec OUT NOCOPY FTE_TL_CACHE.TL_trip_output_rec_type,
233 x_stop_charges_tab OUT NOCOPY FTE_TL_CACHE.TL_trip_stop_output_tab_type,
234 x_return_status OUT NOCOPY VARCHAR2)
235 IS
236
237 l_pricing_control_rec fte_freight_pricing.pricing_control_input_rec_type;
238 l_pricing_engine_input_rec fte_freight_pricing.pricing_engine_input_rec_type;
239 l_curr_line_idx NUMBER := 0;
240 l_req_line_info_rec req_line_info_rec_type;
241 l_qp_output_line_rows QP_PREQ_GRP.LINE_TBL_TYPE;
242 l_qp_output_detail_rows QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE;
243 l_implicit_non_dummy_cnt NUMBER := 0;
244
245
246 l_return_status VARCHAR2(1);
247
248 l_log_level NUMBER := fte_freight_pricing_util.G_DBG;
249 l_method_name VARCHAR2(50) := 'tl_core';
250 BEGIN
251
252 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
253 fte_freight_pricing_util.reset_dbg_vars;
254 fte_freight_pricing_util.set_method(l_log_level,l_method_name);
255
256 -- create request lines
257 -- distance, time, ..
258 -- for each request line - build qualifiers
259 -- for each base price request line - build attributes
260 -- for each charge request line - build attributes
261 -- call qp engine
262 -- check qp errors
263 -- analyze o/p
264 -- analyze and extract charges
265
266 -- First clear all global tables ***
267 g_req_line_info_tab.DELETE;
268 FTE_QP_ENGINE.clear_globals(x_return_status => l_return_status);
269
270 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
271 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
272 FTE_FREIGHT_PRICING_UTIL.set_location(p_loc => 'After clear_globals ');
273 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_tl_clear_globals_fl');
274 raise FND_API.G_EXC_ERROR;
275 END IF;
276 END IF;
277
278 -- g_effectivity dates is the global variable which stores the dates passed to QP
279 -- these dates are set to the trip departure dates. For price list selection, only the
280 -- trip departure date is used (not arrival).
281 --SUSUREND 11-Nov-2003
282
283 fte_freight_pricing.g_effectivity_dates.date_from:=p_trip_rec.planned_departure_date;
284 fte_freight_pricing.g_effectivity_dates.date_to:=p_trip_rec.planned_departure_date;
285
286 -- bug 3610889 : added new parameter p_implicit_non_dummy_cnt
287 create_engine_inputs (
288 p_trip_rec => p_trip_rec,
289 p_stop_tab => p_stop_tab,
290 p_carrier_pref => p_carrier_pref,
291 x_implicit_non_dummy_cnt => l_implicit_non_dummy_cnt,
292 x_return_status => l_return_status );
293
294 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
295 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
296 FTE_FREIGHT_PRICING_UTIL.set_location(p_loc => 'After create_engine_inputs ');
297 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_tl_cr_eng_inp_failed');
298 raise FND_API.G_EXC_ERROR;
299 END IF;
300 END IF;
301
302 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_implicit_non_dummy_cnt = '||l_implicit_non_dummy_cnt);
303 -- fte_qp_engine.print_qp_input;
304
305 -- call qp engine
306
307 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'g_req_line_info_tab.COUNT = '||g_req_line_info_tab.COUNT);
308
309 print_req_line_tab;
310
311 fte_qp_engine.call_qp_api (
312 x_qp_output_line_rows => l_qp_output_line_rows,
313 x_qp_output_detail_rows => l_qp_output_detail_rows,
314 x_return_status => l_return_status);
315
316 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
317 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
318 FTE_FREIGHT_PRICING_UTIL.set_location(p_loc => 'After call_qp_api ');
319 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_tl_call_qp_api_failed');
320 raise FND_API.G_EXC_ERROR;
321 END IF;
322 END IF;
323
324 -- fte_qp_engine.check_qp_output_errors (x_return_status => l_return_status);
325 fte_qp_engine.check_tl_qp_output_errors (x_return_status => l_return_status);
326
327 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
328 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
329 FTE_FREIGHT_PRICING_UTIL.set_location(p_loc => 'After check_qp_output_errors ');
330 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_tl_chk_qp_output_failed');
331 raise FND_API.G_EXC_ERROR;
332 END IF;
333 END IF;
334
335
336 check_qp_ipl(p_qp_output_line_rows=>l_qp_output_line_rows,
337 p_implicit_non_dummy_cnt => l_implicit_non_dummy_cnt,
338 x_return_status => l_return_status );
339
340 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
341 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
342 FTE_FREIGHT_PRICING_UTIL.set_location(p_loc => 'After check_qp_ipl ');
343 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_tl_check_qp_ipl_failed');
344 raise FND_API.G_EXC_ERROR;
345 END IF;
346 END IF;
347
348
349
350 -- check qp engine output for errors
351 -- handle errors if any
352
353 -- process qp output
354 retrieve_qp_output (
355 p_trip_rec => p_trip_rec,
356 p_stop_tab => p_stop_tab,
357 p_carrier_pref => p_carrier_pref,
358 p_qp_output_line_rows => l_qp_output_line_rows,
359 p_qp_output_detail_rows => l_qp_output_detail_rows,
360 x_trip_charges_rec => x_trip_charges_rec,
361 x_stop_charges_tab => x_stop_charges_tab,
362 x_return_status => l_return_status);
363
364 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
365 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
366 FTE_FREIGHT_PRICING_UTIL.set_location(p_loc => 'After retrieve_qp_output ');
367 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_tl_ret_qp_out_failed');
368 raise FND_API.G_EXC_ERROR;
369 END IF;
370 END IF;
371
372 print_output( p_trip_charges_rec => x_trip_charges_rec,
373 p_stop_charges_tab => x_stop_charges_tab);
374
375
376
377 fte_freight_pricing_util.unset_method(l_log_level,l_method_name);
378
379 EXCEPTION
380 WHEN FND_API.G_EXC_ERROR THEN
381 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
382 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_method_name);
383
384 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
385 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
386 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_method_name);
387
388 WHEN OTHERS THEN
389 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
390 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_others');
391 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
392 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_method_name);
393 END tl_core;
394
395 PROCEDURE create_engine_inputs (
396 p_trip_rec IN FTE_TL_CACHE.TL_trip_data_input_rec_type,
397 p_stop_tab IN FTE_TL_CACHE.TL_trip_stop_input_tab_type,
398 p_carrier_pref IN FTE_TL_CACHE.TL_carrier_pref_rec_type,
399 p_trip_index IN NUMBER DEFAULT NULL,
400 x_implicit_non_dummy_cnt OUT NOCOPY NUMBER,
401 x_return_status OUT NOCOPY VARCHAR2)
402 IS
403
404 l_uom_ea VARCHAR2(30);
405 l_pricing_control_rec fte_freight_pricing.pricing_control_input_rec_type;
406 l_pricing_engine_input_rec fte_freight_pricing.pricing_engine_input_rec_type;
407 l_curr_line_idx NUMBER := 0;
408 l_req_line_info_rec req_line_info_rec_type;
409 i NUMBER := 0;
410 l_return_status VARCHAR2(1);
411
412 l_tmp_dist NUMBER;
413
414
415 l_log_level NUMBER := fte_freight_pricing_util.G_DBG;
416 l_method_name VARCHAR2(50) := 'create_engine_inputs';
417
418 BEGIN
419
420 -- do we need to store which line index corresponds to which type of line?
421 -- create distance lines
422 -- create time line
423 -- create unit line
424 -- create flat line
425 -- create load line
426 -- create stop lines
427 -- create control rec
428
429 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
430 fte_freight_pricing_util.reset_dbg_vars;
431 fte_freight_pricing_util.set_method(l_log_level,l_method_name);
432
433 x_implicit_non_dummy_cnt := 0;
434
435 IF (p_trip_index IS NOT NULL)
436 THEN
437 l_req_line_info_rec.trip_index:=p_trip_index;
438 l_curr_line_idx:=g_req_line_info_tab.COUNT;
439
440 END IF;
441
442
443 OPEN get_uom_for_each;
444 FETCH get_uom_for_each INTO l_uom_ea;
445 CLOSE get_uom_for_each;
446
447 IF l_uom_ea is null THEN
448 FTE_FREIGHT_PRICING_UTIL.set_location(p_loc => 'After get_uom_for_each ');
449 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_get_uom_for_each_failed');
450 raise FND_API.G_EXC_ERROR;
451 END IF;
452
453 -- VVP: 09/18/03
454 -- Made a few changes to account for 'DIRECT_ROUTE' vs 'FULL_ROUTE'
455 -- Basically, if distance method is direct route, we should be using direct route
456 -- distance for base prices. This means we cannot account for loaded
457 -- and unloaded distances. We still use loaded and unloaded distance fields
458 -- to decide if the trip is non-empty or not.
459
460 -- LineIndex=1 : Loaded (Generic) Distance Line
461 IF ( p_trip_rec.loaded_distance > 0
462 AND ( p_trip_rec.continuous_move = 'N'
463 OR (p_trip_rec.continuous_move = 'Y'
464 AND p_carrier_pref.cm_rate_variant = 'DISCOUNT'))
465 AND (
466 (p_trip_rec.distance_method <> 'DIRECT_ROUTE')
467 OR
468 (p_trip_rec.distance_method = 'DIRECT_ROUTE'
469 AND p_trip_rec.total_direct_distance >0 )
470 )
471 ) THEN
472
473 l_curr_line_idx := l_curr_line_idx + 1;
474
475
476 l_req_line_info_rec.line_index := l_curr_line_idx;
477 l_req_line_info_rec.line_type := G_LOADED_DIST_BASE_LINE;
478 l_req_line_info_rec.stop_index := null;
479 IF (p_trip_rec.distance_method = 'DIRECT_ROUTE') THEN
480 l_req_line_info_rec.line_qty := p_trip_rec.total_direct_distance;
481 ELSE
482 l_req_line_info_rec.line_qty := p_trip_rec.loaded_distance;
483 END IF;
484 l_req_line_info_rec.line_uom := p_carrier_pref.distance_uom;
485 l_req_line_info_rec.currency := p_carrier_pref.currency;
486 l_req_line_info_rec.lane_id := p_trip_rec.lane_id;
487 l_req_line_info_rec.pricelist_id := p_trip_rec.price_list_id;
488 l_req_line_info_rec.carrier_id := p_trip_rec.carrier_id;
489
490 g_req_line_info_tab(l_curr_line_idx) := l_req_line_info_rec;
491 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
492 'l_curr_line_idx = '||l_curr_line_idx);
493
494 create_input_line (
495 p_req_line_rec => l_req_line_info_rec,
496 p_trip_rec => p_trip_rec,
497 p_stop_tab => p_stop_tab,
498 p_carrier_pref => p_carrier_pref,
499 x_return_status => l_return_status);
500
501 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
502 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
503 FTE_FREIGHT_PRICING_UTIL.set_location(p_loc => 'After create_engine_inputs ');
504 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_tl_cr_inp_line_failed');
505 raise FND_API.G_EXC_ERROR;
506 END IF;
507 END IF;
508
509 END IF;
510
511
512 -- Note For Unloaded line and DIRECT_ROUTE:
513 -- If distance_method is DIRECT_ROUTE then normally we don't need to go here.
514 -- This is because :
515 -- a) non-continuous move - distance rate taken care of by generic (loaded) dist line
516 -- [ there is no such thing as unloaded direct distance rate ]
517 -- b) continuous move - and rate_var=DISCOUNT - same as above
518 -- c) continuous move - and rate_var=RATE and not dead_head - taken care of by cont.
519 -- distance line
520 -- d) continuous move - and rate_var=RATE and is dead_head - taken care of by cont.
521 -- distance deadhead line
522 -- Exception :
523 -- Empty trips: If non-continuous and continuous move with trip fully empty
524 -- (empty trip or deadhead) then no point applying loaded distance rate
525 -- even with direct_route.
526
527 -- LineIndex=2 : Unloaded Distance Line
528 -- Also for deadhead with discount variant
529 IF (p_trip_rec.unloaded_distance > 0
530 AND ( p_trip_rec.continuous_move = 'N'
531 OR (p_trip_rec.continuous_move = 'Y'
532 AND p_carrier_pref.cm_rate_variant = 'DISCOUNT'))
533 AND (
534 (p_trip_rec.distance_method <> 'DIRECT_ROUTE')
535 OR
536 (p_trip_rec.distance_method = 'DIRECT_ROUTE'
537 AND p_trip_rec.total_direct_distance >0
538 AND p_trip_rec.loaded_distance = 0 ) -- see note above (empty trip)
539 )
540 )
541 THEN
542
543 l_curr_line_idx := l_curr_line_idx + 1;
544
545 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
546 'l_curr_line_idx = '||l_curr_line_idx);
547 fte_freight_pricing_util.print_msg(l_log_level,
548 'p_trip_rec.unloaded_distance = '||p_trip_rec.unloaded_distance);
549 fte_freight_pricing_util.print_msg(l_log_level,
550 'p_trip_rec.continuous_move = '||p_trip_rec.continuous_move);
551 fte_freight_pricing_util.print_msg(l_log_level,
552 'p_carrier_pref.cm_rate_variant = '||p_carrier_pref.cm_rate_variant);
553 fte_freight_pricing_util.print_msg(l_log_level,
554 'p_trip_rec.dead_head = '||p_trip_rec.dead_head);
555 fte_freight_pricing_util.print_msg(l_log_level,
556 'p_carrier_pref.cm_free_dh_mileage = '||p_carrier_pref.cm_free_dh_mileage);
557
558 IF (p_trip_rec.distance_method = 'DIRECT_ROUTE') THEN
559 l_tmp_dist := p_trip_rec.total_direct_distance;
560 ELSE
561 l_tmp_dist := p_trip_rec.unloaded_distance;
562 END IF;
563
564 -- subtract the free deadhead mileage from the deadhead distance
565 -- if this is a deadhead
566 IF (p_trip_rec.dead_head='Y' AND p_trip_rec.continuous_move='Y'
567 AND p_carrier_pref.cm_free_dh_mileage >0) THEN
568 IF l_tmp_dist > p_carrier_pref.cm_free_dh_mileage THEN
569 l_req_line_info_rec.line_qty := l_tmp_dist
570 - p_carrier_pref.cm_free_dh_mileage;
571 ELSE
572 l_req_line_info_rec.line_qty := 0;
573 END IF;
574 ELSE
575 l_req_line_info_rec.line_qty := l_tmp_dist;
576 END IF;
577
578 IF (l_req_line_info_rec.line_qty > 0) THEN
579
580 l_req_line_info_rec.line_index := l_curr_line_idx;
581 l_req_line_info_rec.line_type := G_UNLOADED_DIST_BASE_LINE;
582 l_req_line_info_rec.stop_index := null;
583 --l_req_line_info_rec.line_qty := p_trip_rec.unloaded_distance;
584 l_req_line_info_rec.line_uom := p_carrier_pref.distance_uom;
585 l_req_line_info_rec.currency := p_carrier_pref.currency;
586 l_req_line_info_rec.lane_id := p_trip_rec.lane_id;
587 l_req_line_info_rec.pricelist_id := p_trip_rec.price_list_id;
588 l_req_line_info_rec.carrier_id := p_trip_rec.carrier_id;
589
590 g_req_line_info_tab(l_curr_line_idx) := l_req_line_info_rec;
591
592 create_input_line (
593 p_req_line_rec => l_req_line_info_rec,
594 p_trip_rec => p_trip_rec,
595 p_stop_tab => p_stop_tab,
596 p_carrier_pref => p_carrier_pref,
597 x_return_status => l_return_status);
598
599 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
600 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
601 FTE_FREIGHT_PRICING_UTIL.set_location(p_loc => 'After create_engine_inputs ');
602 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_tl_cr_inp_line_failed');
603 raise FND_API.G_EXC_ERROR;
604 END IF;
605 END IF;
606
607 END IF;
608
609 END IF;
610
611 -- Continuous Move Distance Line
612 -- Is this required? What is the condition?
613 IF ( p_trip_rec.loaded_distance > 0
614 AND p_trip_rec.continuous_move = 'Y'
615 AND p_carrier_pref.cm_rate_variant = 'RATE'
616 AND (
617 (p_trip_rec.distance_method <> 'DIRECT_ROUTE')
618 OR
619 (p_trip_rec.distance_method = 'DIRECT_ROUTE'
620 AND p_trip_rec.total_direct_distance > 0)
621 )
622 ) THEN
623
624 l_curr_line_idx := l_curr_line_idx + 1;
625
626 l_req_line_info_rec.line_index := l_curr_line_idx;
627 l_req_line_info_rec.line_type := G_CONT_DIST_BASE_LINE;
628 l_req_line_info_rec.stop_index := null;
629 IF ( p_trip_rec.distance_method = 'DIRECT_ROUTE') THEN
630 l_req_line_info_rec.line_qty := p_trip_rec.total_direct_distance;
631 ELSE
632 l_req_line_info_rec.line_qty := p_trip_rec.loaded_distance;
633 END IF;
634 l_req_line_info_rec.line_uom := p_carrier_pref.distance_uom;
635 l_req_line_info_rec.currency := p_carrier_pref.currency;
636 l_req_line_info_rec.lane_id := p_trip_rec.lane_id;
637 l_req_line_info_rec.pricelist_id := p_trip_rec.price_list_id;
638 l_req_line_info_rec.carrier_id := p_trip_rec.carrier_id;
639
640 g_req_line_info_tab(l_curr_line_idx) := l_req_line_info_rec;
641 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
642 'l_curr_line_idx = '||l_curr_line_idx);
643
644 create_input_line (
645 p_req_line_rec => l_req_line_info_rec,
646 p_trip_rec => p_trip_rec,
647 p_stop_tab => p_stop_tab,
648 p_carrier_pref => p_carrier_pref,
649 x_return_status => l_return_status);
650
651 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
652 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
653 FTE_FREIGHT_PRICING_UTIL.set_location(p_loc => 'After create_engine_inputs ');
654 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_tl_cr_inp_line_failed');
655 raise FND_API.G_EXC_ERROR;
656 END IF;
657 END IF;
658
659 END IF;
660
661 -- Assumption here is that if a trip is in a continuous move
662 -- then it will have any unloaded distance only if it is a dead head
663 -- Need to verify this.
664
665 -- Continuous Move Deadhead Line
666 IF ( p_trip_rec.unloaded_distance > 0
667 AND ( (p_trip_rec.unloaded_distance > p_carrier_pref.cm_free_dh_mileage
668 AND p_trip_rec.distance_method <> 'DIRECT_ROUTE'
669 )
670 OR
671 (p_trip_rec.distance_method='DIRECT_ROUTE'
672 AND p_trip_rec.total_direct_distance > 0
673 AND p_trip_rec.total_direct_distance > p_carrier_pref.cm_free_dh_mileage
674 )
675 )
676 AND p_trip_rec.continuous_move = 'Y'
677 AND p_carrier_pref.cm_rate_variant = 'RATE') THEN
678
679 l_curr_line_idx := l_curr_line_idx + 1;
680
681 l_req_line_info_rec.line_index := l_curr_line_idx;
682 l_req_line_info_rec.line_type := G_CONT_DH_BASE_LINE;
683 l_req_line_info_rec.stop_index := null;
684 IF (p_trip_rec.distance_method = 'DIRECT_ROUTE') THEN
685 l_req_line_info_rec.line_qty :=
686 p_trip_rec.total_direct_distance - p_carrier_pref.cm_free_dh_mileage;
687 ELSE
688 l_req_line_info_rec.line_qty :=
689 p_trip_rec.unloaded_distance - p_carrier_pref.cm_free_dh_mileage;
690 END IF;
691
692 l_req_line_info_rec.line_uom := p_carrier_pref.distance_uom;
693 l_req_line_info_rec.currency := p_carrier_pref.currency;
694 l_req_line_info_rec.lane_id := p_trip_rec.lane_id;
695 l_req_line_info_rec.pricelist_id := p_trip_rec.price_list_id;
696 l_req_line_info_rec.carrier_id := p_trip_rec.carrier_id;
697
698 g_req_line_info_tab(l_curr_line_idx) := l_req_line_info_rec;
699 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
700 'l_curr_line_idx = '||l_curr_line_idx);
701
702 create_input_line (
703 p_req_line_rec => l_req_line_info_rec,
704 p_trip_rec => p_trip_rec,
705 p_stop_tab => p_stop_tab,
706 p_carrier_pref => p_carrier_pref,
707 x_return_status => l_return_status);
708
709 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
710 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
711 FTE_FREIGHT_PRICING_UTIL.set_location(p_loc => 'After create_engine_inputs ');
712 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_tl_cr_inp_line_failed');
713 raise FND_API.G_EXC_ERROR;
714 END IF;
715 END IF;
716 ELSE
717 -- bug 3610889
718 -- Under following condn we assume success w/o creating engine line
719 IF ( p_trip_rec.unloaded_distance > 0
720 AND ( (p_trip_rec.unloaded_distance <= p_carrier_pref.cm_free_dh_mileage
721 AND p_trip_rec.distance_method <> 'DIRECT_ROUTE'
722 )
723 OR
724 (p_trip_rec.distance_method='DIRECT_ROUTE'
725 AND p_trip_rec.total_direct_distance > 0
726 AND p_trip_rec.total_direct_distance <= p_carrier_pref.cm_free_dh_mileage
727 )
728 )
729 AND p_trip_rec.continuous_move = 'Y'
730 AND p_carrier_pref.cm_rate_variant = 'RATE')
731 THEN
732 x_implicit_non_dummy_cnt := x_implicit_non_dummy_cnt + 1;
733 END IF;
734 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
735 'x_implicit_non_dummy_cnt = '||x_implicit_non_dummy_cnt);
736
737 END IF;
738
739 -- if trip is loaded. We can also check for weight,volume,piece,pallet.
740 IF (p_trip_rec.loaded_distance > 0) THEN
741
742 IF (p_carrier_pref.unit_basis = 'WEIGHT') THEN
743 l_req_line_info_rec.line_qty := p_trip_rec.total_weight;
744 l_req_line_info_rec.line_uom := p_carrier_pref.weight_uom;
745 ELSIF (p_carrier_pref.unit_basis = 'VOLUME') THEN
746 l_req_line_info_rec.line_qty := p_trip_rec.total_volume;
747 l_req_line_info_rec.line_uom := p_carrier_pref.weight_uom;
748 ELSIF (p_carrier_pref.unit_basis = 'CONTAINER') THEN
749 l_req_line_info_rec.line_qty := p_trip_rec.number_of_containers;
750 l_req_line_info_rec.line_uom := l_uom_ea;
751 ELSIF (p_carrier_pref.unit_basis = 'PALLET') THEN
752 l_req_line_info_rec.line_qty := p_trip_rec.number_of_pallets;
753 l_req_line_info_rec.line_uom := l_uom_ea;
754 END IF;
755
756 IF l_req_line_info_rec.line_qty > 0 THEN
757
758 l_curr_line_idx := l_curr_line_idx + 1;
759
760 l_req_line_info_rec.line_index := l_curr_line_idx;
761 l_req_line_info_rec.line_type := G_UNITS_BASE_LINE;
762 l_req_line_info_rec.stop_index := null;
763
764 l_req_line_info_rec.currency := p_carrier_pref.currency;
765 l_req_line_info_rec.lane_id := p_trip_rec.lane_id;
766 l_req_line_info_rec.pricelist_id := p_trip_rec.price_list_id;
767 l_req_line_info_rec.carrier_id := p_trip_rec.carrier_id;
768
769 g_req_line_info_tab(l_curr_line_idx) := l_req_line_info_rec;
770 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
771 'l_curr_line_idx = '||l_curr_line_idx);
772
773 create_input_line (
774 p_req_line_rec => l_req_line_info_rec,
775 p_trip_rec => p_trip_rec,
776 p_stop_tab => p_stop_tab,
777 p_carrier_pref => p_carrier_pref,
778 x_return_status => l_return_status);
779
780 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
781 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
782 FTE_FREIGHT_PRICING_UTIL.set_location(p_loc => 'After create_engine_inputs ');
783 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_tl_cr_inp_line_failed');
784 raise FND_API.G_EXC_ERROR;
785 END IF;
786 END IF;
787
788 END IF;
789 END IF;
790
791 -- Time base rate line
792
793 IF (p_trip_rec.time > 0) THEN
794 l_curr_line_idx := l_curr_line_idx + 1;
795
796 l_req_line_info_rec.line_index := l_curr_line_idx;
797 l_req_line_info_rec.line_type := G_TIME_BASE_LINE;
798 l_req_line_info_rec.stop_index := null;
799
800 l_req_line_info_rec.line_qty := p_trip_rec.time;
801 l_req_line_info_rec.line_uom := p_carrier_pref.time_uom;
802
803 l_req_line_info_rec.currency := p_carrier_pref.currency;
804 l_req_line_info_rec.lane_id := p_trip_rec.lane_id;
805 l_req_line_info_rec.pricelist_id := p_trip_rec.price_list_id;
806 l_req_line_info_rec.carrier_id := p_trip_rec.carrier_id;
807
808 g_req_line_info_tab(l_curr_line_idx) := l_req_line_info_rec;
809 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
810 'l_curr_line_idx = '||l_curr_line_idx);
811
812 create_input_line (
813 p_req_line_rec => l_req_line_info_rec,
814 p_trip_rec => p_trip_rec,
815 p_stop_tab => p_stop_tab,
816 p_carrier_pref => p_carrier_pref,
817 x_return_status => l_return_status);
818
819 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
820 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
821 FTE_FREIGHT_PRICING_UTIL.set_location(p_loc => 'After create_engine_inputs ');
822 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_tl_cr_inp_line_failed');
823 raise FND_API.G_EXC_ERROR;
824 END IF;
825 END IF;
826 END IF;
827
828 -- Flat base rate line
829
830 l_curr_line_idx := l_curr_line_idx + 1;
831
832 l_req_line_info_rec.line_index := l_curr_line_idx;
833 l_req_line_info_rec.line_type := G_FLAT_BASE_LINE;
834 l_req_line_info_rec.stop_index := null;
835
836 l_req_line_info_rec.line_qty := 1;
837 l_req_line_info_rec.line_uom := l_uom_ea;
838
839 l_req_line_info_rec.currency := p_carrier_pref.currency;
840 l_req_line_info_rec.lane_id := p_trip_rec.lane_id;
841 l_req_line_info_rec.pricelist_id := p_trip_rec.price_list_id;
842 l_req_line_info_rec.carrier_id := p_trip_rec.carrier_id;
843
844 g_req_line_info_tab(l_curr_line_idx) := l_req_line_info_rec;
845 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
846 'l_curr_line_idx = '||l_curr_line_idx);
847
848 create_input_line (
849 p_req_line_rec => l_req_line_info_rec,
850 p_trip_rec => p_trip_rec,
851 p_stop_tab => p_stop_tab,
852 p_carrier_pref => p_carrier_pref,
853 x_return_status => l_return_status);
854
855 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
856 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
857 FTE_FREIGHT_PRICING_UTIL.set_location(p_loc => 'After create_engine_inputs ');
858 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_tl_cr_inp_line_failed');
859 raise FND_API.G_EXC_ERROR;
860 END IF;
861 END IF;
862
863 -- LOAD CHARGE LINE
864
865 l_curr_line_idx := l_curr_line_idx + 1;
866
867 l_req_line_info_rec.line_index := l_curr_line_idx;
868 l_req_line_info_rec.line_type := G_LOAD_CHARGE_LINE;
869 l_req_line_info_rec.stop_index := null;
870
871 l_req_line_info_rec.line_qty := 1;
872 l_req_line_info_rec.line_uom := l_uom_ea;
873
874 l_req_line_info_rec.currency := p_carrier_pref.currency;
875 l_req_line_info_rec.lane_id := p_trip_rec.lane_id;
876 l_req_line_info_rec.pricelist_id := p_trip_rec.price_list_id;
877 l_req_line_info_rec.carrier_id := p_trip_rec.carrier_id;
878
879 g_req_line_info_tab(l_curr_line_idx) := l_req_line_info_rec;
880 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
881 'l_curr_line_idx = '||l_curr_line_idx);
882
883 create_input_line (
884 p_req_line_rec => l_req_line_info_rec,
885 p_trip_rec => p_trip_rec,
886 p_stop_tab => p_stop_tab,
887 p_carrier_pref => p_carrier_pref,
888 x_return_status => l_return_status);
889
890 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
891 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
892 FTE_FREIGHT_PRICING_UTIL.set_location(p_loc => 'After create_engine_inputs ');
893 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_tl_cr_inp_line_failed');
894 raise FND_API.G_EXC_ERROR;
895 END IF;
896 END IF;
897
898 -- create qp control record
899 create_control_rec ( x_return_status => l_return_status);
900
901 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
902 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
903 FTE_FREIGHT_PRICING_UTIL.set_location(p_loc => 'After create_engine_inputs ');
904 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_tl_cr_ctrl_rec_failed');
905 raise FND_API.G_EXC_ERROR;
906 END IF;
907 END IF;
908
909 -- STOP CHARGE LINES
910 -- 1 line per stop
911
912 IF (p_stop_tab.COUNT >0) THEN
913 -- i := p_stop_tab.FIRST;
914 i := p_trip_rec.stop_reference;
915 LOOP
916
917 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
918 'stop_ref='||p_trip_rec.stop_reference);
919 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
920 'i='||i);
921 --IF ( (p_stop_tab(i).trip_id <> p_trip_rec.trip_id)
922 IF (i >= (p_trip_rec.stop_reference + p_trip_rec.number_of_stops ) )
923 THEN
924 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Exit stop loop');
925 EXIT;
926 END IF;
927
928 l_curr_line_idx := l_curr_line_idx + 1;
929
930 l_req_line_info_rec.line_index := l_curr_line_idx;
931 l_req_line_info_rec.line_type := G_STOP_CHARGE_LINE;
932 l_req_line_info_rec.stop_index := i; -- index into stop table
933
934 l_req_line_info_rec.line_qty := 1;
935 l_req_line_info_rec.line_uom := l_uom_ea;
936
937 l_req_line_info_rec.currency := p_carrier_pref.currency;
938 l_req_line_info_rec.lane_id := p_trip_rec.lane_id;
939 l_req_line_info_rec.pricelist_id := p_trip_rec.price_list_id;
940 l_req_line_info_rec.carrier_id := p_trip_rec.carrier_id;
941
942 g_req_line_info_tab(l_curr_line_idx) := l_req_line_info_rec;
943
944 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
945 'l_curr_line_idx = '||l_curr_line_idx);
946 create_input_line (
947 p_req_line_rec => l_req_line_info_rec,
948 p_trip_rec => p_trip_rec,
949 p_stop_tab => p_stop_tab,
950 p_carrier_pref => p_carrier_pref,
951 x_return_status => l_return_status);
952
953 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
954 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
955 FTE_FREIGHT_PRICING_UTIL.set_location(p_loc => 'After create_engine_inputs ');
956 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_tl_cr_inp_line_failed');
957 raise FND_API.G_EXC_ERROR;
958 END IF;
959 END IF;
960
961 EXIT WHEN i = p_stop_tab.LAST ;
962 i := p_stop_tab.NEXT(i);
963
964 END LOOP;
965 END IF; --stop_tab.count
966
967 -- FACILITY CHARGE LINES
968
969 IF (p_stop_tab.COUNT >0) THEN
970 i := p_trip_rec.stop_reference;
971 LOOP
972
973 IF (i >= (p_trip_rec.stop_reference + p_trip_rec.number_of_stops ) )
974 THEN
975 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Exit stop loop');
976 EXIT;
977 END IF;
978
979 IF (p_stop_tab(i).fac_pricelist_id IS NOT NULL
980 AND p_stop_tab(i).fac_pricelist_id > 0
981 AND p_stop_tab(i).stop_type <> 'NA'
982 --AND p_stop_tab(i).loading_protocol <> 'CARRIER'
983 AND p_stop_tab(i).fac_currency IS NOT NULL ) THEN
984
985 l_curr_line_idx := l_curr_line_idx + 1;
986
987 l_req_line_info_rec.line_index := l_curr_line_idx;
988 l_req_line_info_rec.line_type := G_FACILITY_CHARGE_LINE;
989 l_req_line_info_rec.stop_index := i;
990
991 l_req_line_info_rec.line_qty := 1;
992 l_req_line_info_rec.line_uom := l_uom_ea;
993
994 l_req_line_info_rec.currency := p_stop_tab(i).fac_currency;
995 l_req_line_info_rec.lane_id := p_trip_rec.lane_id;
996 l_req_line_info_rec.pricelist_id := p_stop_tab(i).fac_pricelist_id;
997 l_req_line_info_rec.carrier_id := NULL;
998
999 g_req_line_info_tab(l_curr_line_idx) := l_req_line_info_rec;
1000 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
1001 'l_curr_line_idx = '||l_curr_line_idx);
1002
1003 create_input_line (
1004 p_req_line_rec => l_req_line_info_rec,
1005 p_trip_rec => p_trip_rec,
1006 p_stop_tab => p_stop_tab,
1007 p_carrier_pref => p_carrier_pref,
1008 x_return_status => l_return_status);
1009
1010 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1011 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1012 FTE_FREIGHT_PRICING_UTIL.set_location(p_loc => 'After create_engine_inputs ');
1013 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_tl_cr_inp_line_failed');
1014 raise FND_API.G_EXC_ERROR;
1015 END IF;
1016 END IF;
1017
1018 END IF;
1019
1020 EXIT WHEN i = p_stop_tab.LAST;
1021 i := p_stop_tab.NEXT(i);
1022
1023 END LOOP;
1024 END IF; --stop_tab.count
1025
1026 -- create qp control record
1027 -- create_control_rec ( x_return_status => l_return_status);
1028
1029
1030 fte_freight_pricing_util.unset_method(l_log_level,l_method_name);
1031
1032 EXCEPTION
1033 WHEN FND_API.G_EXC_ERROR THEN
1034 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1035 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_method_name);
1036
1037 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1038 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1039 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_method_name);
1040
1041 WHEN OTHERS THEN
1042 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1043 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_others');
1044 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
1045 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_method_name);
1046
1047 END create_engine_inputs;
1048
1049
1050 --
1051 -- procedure : create_input_line
1052 -- Hides details of using fte_qp_engine.create_line_record
1053 --
1054
1055 PROCEDURE create_input_line (
1056 p_req_line_rec IN req_line_info_rec_type,
1057 p_trip_rec IN FTE_TL_CACHE.TL_trip_data_input_rec_type,
1058 p_stop_tab IN FTE_TL_CACHE.TL_trip_stop_input_tab_type,
1059 p_carrier_pref IN FTE_TL_CACHE.TL_carrier_pref_rec_type,
1060 x_return_status OUT NOCOPY VARCHAR2)
1061
1062 IS
1063
1064 l_pricing_control_rec fte_freight_pricing.pricing_control_input_rec_type;
1065 l_pricing_engine_input_rec fte_freight_pricing.pricing_engine_input_rec_type;
1066 l_return_status VARCHAR2(1);
1067 l_log_level NUMBER := fte_freight_pricing_util.G_DBG;
1068 l_method_name VARCHAR2(50) := 'create_input_line';
1069
1070 BEGIN
1071
1072 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1073 fte_freight_pricing_util.reset_dbg_vars;
1074 fte_freight_pricing_util.set_method(l_log_level,l_method_name);
1075
1076 l_pricing_control_rec.pricing_event_num := fte_freight_pricing.G_LINE_EVENT_NUM;
1077 l_pricing_control_rec.currency_code := p_req_line_rec.currency;
1078 l_pricing_control_rec.lane_id := p_req_line_rec.lane_id;
1079 l_pricing_control_rec.price_list_id := p_req_line_rec.pricelist_id;
1080 l_pricing_control_rec.party_id := p_req_line_rec.carrier_id;
1081
1082 l_pricing_engine_input_rec.input_index := p_req_line_rec.line_index;
1083 l_pricing_engine_input_rec.line_quantity := p_req_line_rec.line_qty;
1084 l_pricing_engine_input_rec.line_uom := p_req_line_rec.line_uom;
1085
1086 fte_qp_engine.create_line_record (
1087 p_pricing_control_rec => l_pricing_control_rec,
1088 p_pricing_engine_input_rec => l_pricing_engine_input_rec,
1089 x_return_status => l_return_status );
1090
1091 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1092 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1093 FTE_FREIGHT_PRICING_UTIL.set_location(p_loc => 'After create_engine_inputs ');
1094 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_tl_cr_line_rec_failed');
1095 raise FND_API.G_EXC_ERROR;
1096 END IF;
1097 END IF;
1098
1099 create_line_attributes (
1100 p_req_line_rec => p_req_line_rec,
1101 p_trip_rec => p_trip_rec,
1102 p_stop_tab => p_stop_tab,
1103 p_carrier_pref => p_carrier_pref,
1104 x_return_status => l_return_status);
1105
1106 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1107 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1108 FTE_FREIGHT_PRICING_UTIL.set_location(p_loc => 'After create_engine_inputs ');
1109 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_tl_cr_line_attr_failed');
1110 raise FND_API.G_EXC_ERROR;
1111 END IF;
1112 END IF;
1113
1114 create_line_qualifiers (
1115 p_req_line_rec => p_req_line_rec,
1116 p_trip_rec => p_trip_rec,
1117 p_stop_tab => p_stop_tab,
1118 p_carrier_pref => p_carrier_pref,
1119 x_return_status => l_return_status);
1120
1121 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1122 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1123 FTE_FREIGHT_PRICING_UTIL.set_location(p_loc => 'After create_engine_inputs ');
1124 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_tl_cr_line_qual_failed');
1125 raise FND_API.G_EXC_ERROR;
1126 END IF;
1127 END IF;
1128
1129 fte_freight_pricing_util.unset_method(l_log_level,l_method_name);
1130
1131 EXCEPTION
1132 WHEN FND_API.G_EXC_ERROR THEN
1133 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1134 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_method_name);
1135
1136 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1137 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1138 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_method_name);
1139
1140 WHEN OTHERS THEN
1141 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1142 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_others');
1143 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
1144 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_method_name);
1145
1146 END create_input_line;
1147
1148 --
1149 -- procedure : create_line_attributes
1150 -- Hides details of creating attributes for a given input line
1151 --
1152
1153 PROCEDURE create_line_attributes (
1154 p_req_line_rec IN req_line_info_rec_type,
1155 p_trip_rec IN FTE_TL_CACHE.TL_trip_data_input_rec_type,
1156 p_stop_tab IN FTE_TL_CACHE.TL_trip_stop_input_tab_type,
1157 p_carrier_pref IN FTE_TL_CACHE.TL_carrier_pref_rec_type,
1158 x_return_status OUT NOCOPY VARCHAR2)
1159 IS
1160 l_pricing_attr_rec fte_freight_pricing.pricing_attribute_rec_type;
1161 l_pricing_attr_tab fte_freight_pricing.pricing_attribute_tab_type;
1162 l_return_status VARCHAR2(1);
1163
1164 l_attr_idx NUMBER :=0;
1165 l_log_level NUMBER := fte_freight_pricing_util.G_DBG;
1166 l_method_name VARCHAR2(50) := 'create_line_attributes';
1167 BEGIN
1168 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1169 fte_freight_pricing_util.reset_dbg_vars;
1170 fte_freight_pricing_util.set_method(l_log_level,l_method_name);
1171
1172
1173 -- TODO : check if source value is not null / valid before creating the attribute
1174
1175 IF (p_req_line_rec.line_type = G_LOADED_DIST_BASE_LINE) THEN
1176
1177 l_attr_idx := l_attr_idx + 1;
1178 l_pricing_attr_rec.attribute_index := l_attr_idx;
1179 l_pricing_attr_rec.input_index := p_req_line_rec.line_index;
1180 l_pricing_attr_rec.attribute_name := 'TL_RATE_BASIS';
1181 l_pricing_attr_rec.attribute_value := 'DISTANCE';
1182
1183 l_pricing_attr_tab(l_attr_idx) := l_pricing_attr_rec;
1184
1185 l_attr_idx := l_attr_idx + 1;
1186 l_pricing_attr_rec.attribute_index := l_attr_idx;
1187 l_pricing_attr_rec.input_index := p_req_line_rec.line_index;
1188 l_pricing_attr_rec.attribute_name := 'TL_RATE_TYPE';
1189 l_pricing_attr_rec.attribute_value := 'BASE_RATE';
1190
1191 l_pricing_attr_tab(l_attr_idx) := l_pricing_attr_rec;
1192
1193 ELSIF (p_req_line_rec.line_type = G_UNLOADED_DIST_BASE_LINE) THEN
1194
1195 l_attr_idx := l_attr_idx + 1;
1196 l_pricing_attr_rec.attribute_index := l_attr_idx;
1197 l_pricing_attr_rec.input_index := p_req_line_rec.line_index;
1198 l_pricing_attr_rec.attribute_name := 'TL_RATE_BASIS';
1199 l_pricing_attr_rec.attribute_value := 'DISTANCE';
1200
1201 l_pricing_attr_tab(l_attr_idx) := l_pricing_attr_rec;
1202
1203 l_attr_idx := l_attr_idx + 1;
1204 l_pricing_attr_rec.attribute_index := l_attr_idx;
1205 l_pricing_attr_rec.input_index := p_req_line_rec.line_index;
1206 l_pricing_attr_rec.attribute_name := 'TL_RATE_TYPE';
1207 l_pricing_attr_rec.attribute_value := 'BASE_RATE';
1208
1209 l_pricing_attr_tab(l_attr_idx) := l_pricing_attr_rec;
1210
1211 l_attr_idx := l_attr_idx + 1;
1212 l_pricing_attr_rec.attribute_index := l_attr_idx;
1213 l_pricing_attr_rec.input_index := p_req_line_rec.line_index;
1214 l_pricing_attr_rec.attribute_name := 'TL_DISTANCE_TYPE';
1215 l_pricing_attr_rec.attribute_value := 'UNLOADED';
1216
1217 l_pricing_attr_tab(l_attr_idx) := l_pricing_attr_rec;
1218
1219 ELSIF (p_req_line_rec.line_type = G_CONT_DIST_BASE_LINE) THEN
1220
1221 l_attr_idx := l_attr_idx + 1;
1222 l_pricing_attr_rec.attribute_index := l_attr_idx;
1223 l_pricing_attr_rec.input_index := p_req_line_rec.line_index;
1224 l_pricing_attr_rec.attribute_name := 'TL_RATE_BASIS';
1225 l_pricing_attr_rec.attribute_value := 'DISTANCE';
1226
1227 l_pricing_attr_tab(l_attr_idx) := l_pricing_attr_rec;
1228
1229 l_attr_idx := l_attr_idx + 1;
1230 l_pricing_attr_rec.attribute_index := l_attr_idx;
1231 l_pricing_attr_rec.input_index := p_req_line_rec.line_index;
1232 l_pricing_attr_rec.attribute_name := 'TL_RATE_TYPE';
1233 l_pricing_attr_rec.attribute_value := 'BASE_RATE';
1234
1235 l_pricing_attr_tab(l_attr_idx) := l_pricing_attr_rec;
1236
1237 l_attr_idx := l_attr_idx + 1;
1238 l_pricing_attr_rec.attribute_index := l_attr_idx;
1239 l_pricing_attr_rec.input_index := p_req_line_rec.line_index;
1240 l_pricing_attr_rec.attribute_name := 'TL_DISTANCE_TYPE';
1241 l_pricing_attr_rec.attribute_value := 'CONTINUOUS_MOVE';
1242
1243 l_pricing_attr_tab(l_attr_idx) := l_pricing_attr_rec;
1244
1245 ELSIF (p_req_line_rec.line_type = G_CONT_DH_BASE_LINE) THEN
1246
1247 l_attr_idx := l_attr_idx + 1;
1248 l_pricing_attr_rec.attribute_index := l_attr_idx;
1249 l_pricing_attr_rec.input_index := p_req_line_rec.line_index;
1250 l_pricing_attr_rec.attribute_name := 'TL_RATE_BASIS';
1251 l_pricing_attr_rec.attribute_value := 'DISTANCE';
1252
1253 l_pricing_attr_tab(l_attr_idx) := l_pricing_attr_rec;
1254
1255 l_attr_idx := l_attr_idx + 1;
1256 l_pricing_attr_rec.attribute_index := l_attr_idx;
1257 l_pricing_attr_rec.input_index := p_req_line_rec.line_index;
1258 l_pricing_attr_rec.attribute_name := 'TL_RATE_TYPE';
1259 l_pricing_attr_rec.attribute_value := 'BASE_RATE';
1260
1261 l_pricing_attr_tab(l_attr_idx) := l_pricing_attr_rec;
1262
1263 l_attr_idx := l_attr_idx + 1;
1264 l_pricing_attr_rec.attribute_index := l_attr_idx;
1265 l_pricing_attr_rec.input_index := p_req_line_rec.line_index;
1266 l_pricing_attr_rec.attribute_name := 'TL_DISTANCE_TYPE';
1267 l_pricing_attr_rec.attribute_value := 'CONTINUOUS_MOVE';
1268
1269 l_pricing_attr_tab(l_attr_idx) := l_pricing_attr_rec;
1270
1271 IF (p_trip_rec.dead_head='Y') THEN
1272
1273 l_attr_idx := l_attr_idx + 1;
1274 l_pricing_attr_rec.attribute_index := l_attr_idx;
1275 l_pricing_attr_rec.input_index := p_req_line_rec.line_index;
1276 l_pricing_attr_rec.attribute_name := 'TL_DEADHEAD_RT_VAR';
1277 l_pricing_attr_rec.attribute_value := 'Y';
1278
1279 l_pricing_attr_tab(l_attr_idx) := l_pricing_attr_rec;
1280
1281 END IF;
1282
1283 ELSIF (p_req_line_rec.line_type = G_UNITS_BASE_LINE ) THEN
1284
1285 l_attr_idx := l_attr_idx + 1;
1286 l_pricing_attr_rec.attribute_index := l_attr_idx;
1287 l_pricing_attr_rec.input_index := p_req_line_rec.line_index;
1288 l_pricing_attr_rec.attribute_name := 'TL_RATE_BASIS';
1289
1290 IF (p_carrier_pref.unit_basis = 'WEIGHT') THEN
1291 l_pricing_attr_rec.attribute_value := 'WEIGHT';
1292 ELSIF (p_carrier_pref.unit_basis = 'VOLUME') THEN
1293 l_pricing_attr_rec.attribute_value := 'VOLUME';
1294 ELSIF (p_carrier_pref.unit_basis = 'CONTAINER') THEN
1295 l_pricing_attr_rec.attribute_value := 'CONTAINER';
1296 ELSIF (p_carrier_pref.unit_basis = 'PALLET') THEN
1297 l_pricing_attr_rec.attribute_value := 'PALLET';
1298 END IF;
1299
1300 l_pricing_attr_tab(l_attr_idx) := l_pricing_attr_rec;
1301
1302 l_attr_idx := l_attr_idx + 1;
1303 l_pricing_attr_rec.attribute_index := l_attr_idx;
1304 l_pricing_attr_rec.input_index := p_req_line_rec.line_index;
1305 l_pricing_attr_rec.attribute_name := 'TL_RATE_TYPE';
1306 l_pricing_attr_rec.attribute_value := 'BASE_RATE';
1307
1308 l_pricing_attr_tab(l_attr_idx) := l_pricing_attr_rec;
1309
1310 ELSIF (p_req_line_rec.line_type = G_TIME_BASE_LINE ) THEN
1311
1312 l_attr_idx := l_attr_idx + 1;
1313 l_pricing_attr_rec.attribute_index := l_attr_idx;
1314 l_pricing_attr_rec.input_index := p_req_line_rec.line_index;
1315 l_pricing_attr_rec.attribute_name := 'TL_RATE_BASIS';
1316
1317 l_pricing_attr_rec.attribute_value := 'TIME';
1318
1319 l_pricing_attr_tab(l_attr_idx) := l_pricing_attr_rec;
1320
1321 l_attr_idx := l_attr_idx + 1;
1322 l_pricing_attr_rec.attribute_index := l_attr_idx;
1323 l_pricing_attr_rec.input_index := p_req_line_rec.line_index;
1324 l_pricing_attr_rec.attribute_name := 'TL_RATE_TYPE';
1325 l_pricing_attr_rec.attribute_value := 'BASE_RATE';
1326
1327 l_pricing_attr_tab(l_attr_idx) := l_pricing_attr_rec;
1328
1329 ELSIF (p_req_line_rec.line_type = G_FLAT_BASE_LINE ) THEN
1330
1331 l_attr_idx := l_attr_idx + 1;
1332 l_pricing_attr_rec.attribute_index := l_attr_idx;
1333 l_pricing_attr_rec.input_index := p_req_line_rec.line_index;
1334 l_pricing_attr_rec.attribute_name := 'TL_RATE_BASIS';
1335
1336 l_pricing_attr_rec.attribute_value := 'FLAT';
1337
1338 l_pricing_attr_tab(l_attr_idx) := l_pricing_attr_rec;
1339
1340 l_attr_idx := l_attr_idx + 1;
1341 l_pricing_attr_rec.attribute_index := l_attr_idx;
1342 l_pricing_attr_rec.input_index := p_req_line_rec.line_index;
1343 l_pricing_attr_rec.attribute_name := 'TL_RATE_TYPE';
1344 l_pricing_attr_rec.attribute_value := 'BASE_RATE';
1345
1346 l_pricing_attr_tab(l_attr_idx) := l_pricing_attr_rec;
1347
1348 ELSE
1349
1350 -- create_charge_line_attributes (IN OUT l_pricing_attr_tab)
1351 create_charge_line_attributes (
1352 p_req_line_rec => p_req_line_rec,
1353 p_trip_rec => p_trip_rec,
1354 p_stop_tab => p_stop_tab,
1355 p_carrier_pref => p_carrier_pref,
1356 x_pricing_attr_tab => l_pricing_attr_tab,
1357 x_return_status => l_return_status );
1358
1359 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1360 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1361 FTE_FREIGHT_PRICING_UTIL.set_location(p_loc => 'After create_engine_inputs ');
1362 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_tl_cr_chrg_line_attr_failed');
1363 raise FND_API.G_EXC_ERROR;
1364 END IF;
1365 END IF;
1366
1367 l_attr_idx := l_pricing_attr_tab.COUNT;
1368
1369 END IF;
1370
1371 l_attr_idx := l_attr_idx + 1;
1372 l_pricing_attr_rec.attribute_index := l_attr_idx;
1373 l_pricing_attr_rec.input_index := p_req_line_rec.line_index;
1374 l_pricing_attr_rec.attribute_name := 'SERVICE_TYPE';
1375 l_pricing_attr_rec.attribute_value := p_trip_rec.service_type;
1376
1377 l_pricing_attr_tab(l_attr_idx) := l_pricing_attr_rec;
1378
1379 l_attr_idx := l_attr_idx + 1;
1380 l_pricing_attr_rec.attribute_index := l_attr_idx;
1381 l_pricing_attr_rec.input_index := p_req_line_rec.line_index;
1382 l_pricing_attr_rec.attribute_name := 'VEHICLE';
1383 l_pricing_attr_rec.attribute_value := p_trip_rec.vehicle_type;
1384
1385 l_pricing_attr_tab(l_attr_idx) := l_pricing_attr_rec;
1386
1387 IF ( p_trip_rec.continuous_move = 'Y'
1388 AND p_carrier_pref.cm_rate_variant = 'DISCOUNT') THEN
1389
1390 l_attr_idx := l_attr_idx + 1;
1391 l_pricing_attr_rec.attribute_index := l_attr_idx;
1392 l_pricing_attr_rec.input_index := p_req_line_rec.line_index;
1393 l_pricing_attr_rec.attribute_name := 'TL_CM_DISCOUNT_FLG';
1394 l_pricing_attr_rec.attribute_value := 'Y';
1395
1396 l_pricing_attr_tab(l_attr_idx) := l_pricing_attr_rec;
1397 END IF;
1398
1399 fte_qp_engine.prepare_qp_line_attributes (
1400 p_event_num => fte_qp_engine.G_LINE_EVENT_NUM,
1401 p_input_index => p_req_line_rec.line_index,
1402 p_attr_rows => l_pricing_attr_tab,
1403 x_return_status => l_return_status );
1404
1405 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1406 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1407 FTE_FREIGHT_PRICING_UTIL.set_location(p_loc => 'After create_engine_inputs ');
1408 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_tl_prep_qp_line_attr_failed');
1409 raise FND_API.G_EXC_ERROR;
1410 END IF;
1411 END IF;
1412
1413 fte_freight_pricing_util.unset_method(l_log_level,l_method_name);
1414
1415 EXCEPTION
1416 WHEN FND_API.G_EXC_ERROR THEN
1417 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1418 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_method_name);
1419
1420 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1421 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1422 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_method_name);
1423
1424 WHEN OTHERS THEN
1425 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1426 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_others');
1427 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
1428 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_method_name);
1429
1430 END create_line_attributes;
1431
1432 --
1433 -- procedure : create_line_attributes
1434 -- Contains details of creating attributes for a given charge input line
1435 -- Returns table of attributes. Does not actually call fte_qp_engine.
1436 --
1437
1438 PROCEDURE create_charge_line_attributes (
1439 p_req_line_rec IN req_line_info_rec_type,
1440 p_trip_rec IN FTE_TL_CACHE.TL_trip_data_input_rec_type,
1441 p_stop_tab IN FTE_TL_CACHE.TL_trip_stop_input_tab_type,
1442 p_carrier_pref IN FTE_TL_CACHE.TL_carrier_pref_rec_type,
1443 x_pricing_attr_tab IN OUT NOCOPY fte_freight_pricing.pricing_attribute_tab_type,
1444 x_return_status OUT NOCOPY VARCHAR2)
1445 IS
1446 l_pricing_attr_rec fte_freight_pricing.pricing_attribute_rec_type;
1447 l_return_status VARCHAR2(1);
1448 l_attr_idx NUMBER :=0;
1449 l_out_of_rt_dist NUMBER :=0;
1450 l_fac_handling_wt NUMBER :=0;
1451 l_fac_handling_vol NUMBER :=0;
1452 l_stop_rec FTE_TL_CACHE.tl_trip_stop_input_rec_type;
1453 l_log_level NUMBER := fte_freight_pricing_util.G_DBG;
1454 l_method_name VARCHAR2(50) := 'create_charge_line_attributes';
1455
1456 BEGIN
1457 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1458 fte_freight_pricing_util.reset_dbg_vars;
1459 fte_freight_pricing_util.set_method(l_log_level,l_method_name);
1460
1461 -- note : validate attribute source values before using them
1462
1463 l_attr_idx := x_pricing_attr_tab.COUNT;
1464
1465 -- TODO : check if source value is not null / valid before creating the attribute
1466
1467 IF (p_req_line_rec.line_type = G_LOAD_CHARGE_LINE) THEN
1468
1469 l_attr_idx := l_attr_idx + 1;
1470 l_pricing_attr_rec.attribute_index := l_attr_idx;
1471 l_pricing_attr_rec.input_index := p_req_line_rec.line_index;
1472 l_pricing_attr_rec.attribute_name := 'TL_RATE_TYPE';
1473 l_pricing_attr_rec.attribute_value := 'LOAD_CHARGE';
1474
1475 x_pricing_attr_tab(l_attr_idx) := l_pricing_attr_rec;
1476
1477 -- For Stop Off Charges
1478 IF (p_trip_rec.number_of_stops IS NOT NULL
1479 AND p_trip_rec.number_of_stops >= 2 ) THEN
1480
1481 l_attr_idx := l_attr_idx + 1;
1482 l_pricing_attr_rec.attribute_index := l_attr_idx;
1483 l_pricing_attr_rec.input_index := p_req_line_rec.line_index;
1484 l_pricing_attr_rec.attribute_name := 'TL_NUM_STOPS';
1485 -- bug 3394807 : TP expects that the first and last stops of the trip not be
1486 -- counted towards stop off charge. Free stops and other breaks apply only
1487 -- to intermediate stops
1488 -- l_pricing_attr_rec.attribute_value := to_char(p_trip_rec.number_of_stops);
1489 l_pricing_attr_rec.attribute_value := to_char(p_trip_rec.number_of_stops-2);
1490 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'num stops for stop off charge='||to_char(p_trip_rec.number_of_stops-2));
1491
1492 x_pricing_attr_tab(l_attr_idx) := l_pricing_attr_rec;
1493
1494 END IF;
1495
1496
1497 -- VVP:09/18/03
1498 -- Per Hema, out of route charges need to be calculated
1499 -- regardless of distance calc method, eventhough typicaly
1500 -- this charge is not needed for Full Route method
1501 -- Also, if max_out_of_route is NULL, this means that no charge to be applied
1502 -- - this is not the same as max_out_of_route=0
1503
1504 -- For Out of Route Charges
1505 -- IF ( p_trip_rec.distance_method = 'DIRECT_ROUTE'
1506 -- AND p_trip_rec.total_direct_distance IS NOT NULL
1507 -- AND p_trip_rec.total_direct_distance > 0 ) THEN
1508
1509 IF ( p_trip_rec.total_direct_distance IS NOT NULL
1510 AND p_trip_rec.total_direct_distance > 0
1511 AND p_carrier_pref.max_out_of_route IS NOT NULL) THEN
1512
1513 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'total_direct_distance='||p_trip_rec.total_direct_distance);
1514 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'total_trip_distance='||p_trip_rec.total_trip_distance);
1515 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'max_out_of_route='||p_carrier_pref.max_out_of_route);
1516
1517 l_out_of_rt_dist := p_trip_rec.total_trip_distance
1518 - ( p_trip_rec.total_direct_distance
1519 * (1 + p_carrier_pref.max_out_of_route/100) );
1520 IF (l_out_of_rt_dist > 0) THEN
1521
1522 l_attr_idx := l_attr_idx + 1;
1523 l_pricing_attr_rec.attribute_index := l_attr_idx;
1524 l_pricing_attr_rec.input_index := p_req_line_rec.line_index;
1525 l_pricing_attr_rec.attribute_name := 'TL_CHARGED_OUT_RT_DISTANCE';
1526 l_pricing_attr_rec.attribute_value := to_char(l_out_of_rt_dist);
1527
1528 x_pricing_attr_tab(l_attr_idx) := l_pricing_attr_rec;
1529
1530 END IF;
1531
1532 END IF;
1533
1534 -- For Document Charges
1535 -- no special attributes needed
1536 -- For Handling Charges
1537 -- send both wt and volume
1538
1539 --Apply handling charge only if the trip is NOT a CM dead head
1540 --bug 3635944
1541 IF (NOT (p_trip_rec.dead_head='Y' AND p_trip_rec.continuous_move='Y'))
1542 THEN
1543
1544 IF (p_carrier_pref.unit_basis = 'WEIGHT') THEN
1545
1546 l_attr_idx := l_attr_idx + 1;
1547 l_pricing_attr_rec.attribute_index := l_attr_idx;
1548 l_pricing_attr_rec.input_index := p_req_line_rec.line_index;
1549 l_pricing_attr_rec.attribute_name := 'TL_HANDLING_WT';
1550 l_pricing_attr_rec.attribute_value := to_char(nvl(p_trip_rec.total_weight,0));
1551
1552 x_pricing_attr_tab(l_attr_idx) := l_pricing_attr_rec;
1553
1554 ELSIF (p_carrier_pref.unit_basis = 'VOLUME') THEN
1555
1556 l_attr_idx := l_attr_idx + 1;
1557 l_pricing_attr_rec.attribute_index := l_attr_idx;
1558 l_pricing_attr_rec.input_index := p_req_line_rec.line_index;
1559 l_pricing_attr_rec.attribute_name := 'TL_HANDLING_VOL';
1560 l_pricing_attr_rec.attribute_value := to_char(nvl(p_trip_rec.total_volume,0));
1561
1562 x_pricing_attr_tab(l_attr_idx) := l_pricing_attr_rec;
1563
1564 END IF;
1565
1566 l_attr_idx := l_attr_idx + 1;
1567 l_pricing_attr_rec.attribute_index := l_attr_idx;
1568 l_pricing_attr_rec.input_index := p_req_line_rec.line_index;
1569 l_pricing_attr_rec.attribute_name := 'TL_HANDLING_ACT';
1570 l_pricing_attr_rec.attribute_value := 'Y';
1571
1572
1573
1574 x_pricing_attr_tab(l_attr_idx) := l_pricing_attr_rec;
1575 END IF;
1576
1577 ELSIF (p_req_line_rec.line_type = G_STOP_CHARGE_LINE) THEN
1578
1579 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
1580 'line_index = '||p_req_line_rec.line_index);
1581 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
1582 'stop_index = '||p_req_line_rec.stop_index);
1583 IF ( p_stop_tab.COUNT > 0 AND p_stop_tab.EXISTS(p_req_line_rec.stop_index) ) THEN
1584 l_stop_rec := p_stop_tab(p_req_line_rec.stop_index);
1585
1586 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
1587 'l_stop_rec.stop_id = '||l_stop_rec.stop_id);
1588
1589 l_attr_idx := l_attr_idx + 1;
1590 l_pricing_attr_rec.attribute_index := l_attr_idx;
1591 l_pricing_attr_rec.input_index := p_req_line_rec.line_index;
1592 l_pricing_attr_rec.attribute_name := 'TL_RATE_TYPE';
1593 l_pricing_attr_rec.attribute_value := 'STOP_CHARGE';
1594
1595 x_pricing_attr_tab(l_attr_idx) := l_pricing_attr_rec;
1596
1597 IF (l_stop_rec.loading_protocol = 'CARRIER'
1598 OR l_stop_rec.loading_protocol = 'JOINT')
1599 AND (l_stop_rec.stop_type = 'PU'
1600 OR l_stop_rec.stop_type = 'DO'
1601 OR l_stop_rec.stop_type = 'PD') THEN
1602
1603 l_attr_idx := l_attr_idx + 1;
1604 l_pricing_attr_rec.attribute_index := l_attr_idx;
1605 l_pricing_attr_rec.input_index := p_req_line_rec.line_index;
1606 l_pricing_attr_rec.attribute_name := 'LOADING_PROTOCOL';
1607 l_pricing_attr_rec.attribute_value := l_stop_rec.loading_protocol;
1608
1609 x_pricing_attr_tab(l_attr_idx) := l_pricing_attr_rec;
1610
1611 IF (l_stop_rec.stop_type = 'PU' or l_stop_rec.stop_type = 'PD') THEN
1612
1613 -- For loading and assisted loading charges
1614
1615 l_attr_idx := l_attr_idx + 1;
1616 l_pricing_attr_rec.attribute_index := l_attr_idx;
1617 l_pricing_attr_rec.input_index := p_req_line_rec.line_index;
1618 IF (p_carrier_pref.unit_basis = 'WEIGHT') THEN
1619 l_pricing_attr_rec.attribute_name := 'TL_PICKUP_WT';
1620 l_pricing_attr_rec.attribute_value := l_stop_rec.pickup_weight;
1621 ELSIF (p_carrier_pref.unit_basis = 'VOLUME') THEN
1622 l_pricing_attr_rec.attribute_name := 'TL_PICKUP_VOL';
1623 l_pricing_attr_rec.attribute_value := l_stop_rec.pickup_volume;
1624 ELSIF (p_carrier_pref.unit_basis = 'CONTAINER') THEN
1625 l_pricing_attr_rec.attribute_name := 'TL_PICKUP_CONTAINER';
1626 l_pricing_attr_rec.attribute_value := l_stop_rec.pickup_containers;
1627 ELSIF (p_carrier_pref.unit_basis = 'PALLET') THEN
1628 l_pricing_attr_rec.attribute_name := 'TL_PICKUP_PALLET';
1629 l_pricing_attr_rec.attribute_value := l_stop_rec.pickup_pallets;
1630 END IF;
1631
1632 x_pricing_attr_tab(l_attr_idx) := l_pricing_attr_rec;
1633
1634 l_attr_idx := l_attr_idx + 1;
1635 l_pricing_attr_rec.attribute_index := l_attr_idx;
1636 l_pricing_attr_rec.input_index := p_req_line_rec.line_index;
1637 l_pricing_attr_rec.attribute_name := 'TL_STOP_LOADING_ACT';
1638 l_pricing_attr_rec.attribute_value := 'Y';
1639
1640 x_pricing_attr_tab(l_attr_idx) := l_pricing_attr_rec;
1641
1642 ELSE
1643
1644 l_attr_idx := l_attr_idx + 1;
1645 l_pricing_attr_rec.attribute_index := l_attr_idx;
1646 l_pricing_attr_rec.input_index := p_req_line_rec.line_index;
1647 l_pricing_attr_rec.attribute_name := 'TL_STOP_LOADING_ACT';
1648 l_pricing_attr_rec.attribute_value := 'N';
1649
1650 x_pricing_attr_tab(l_attr_idx) := l_pricing_attr_rec;
1651
1652 END IF;
1653
1654 IF (l_stop_rec.stop_type = 'DO' or l_stop_rec.stop_type = 'PD') THEN
1655
1656 -- For unloading and assisted unloading charges
1657
1658 l_attr_idx := l_attr_idx + 1;
1659 l_pricing_attr_rec.attribute_index := l_attr_idx;
1660 l_pricing_attr_rec.input_index := p_req_line_rec.line_index;
1661 IF (p_carrier_pref.unit_basis = 'WEIGHT') THEN
1662 l_pricing_attr_rec.attribute_name := 'TL_DROPOFF_WT';
1663 l_pricing_attr_rec.attribute_value := l_stop_rec.dropoff_weight;
1664 ELSIF (p_carrier_pref.unit_basis = 'VOLUME') THEN
1665 l_pricing_attr_rec.attribute_name := 'TL_DROPOFF_VOL';
1666 l_pricing_attr_rec.attribute_value := l_stop_rec.dropoff_volume;
1667 ELSIF (p_carrier_pref.unit_basis = 'CONTAINER') THEN
1668 l_pricing_attr_rec.attribute_name := 'TL_DROPOFF_CONTAINER';
1669 l_pricing_attr_rec.attribute_value := l_stop_rec.dropoff_containers;
1670 ELSIF (p_carrier_pref.unit_basis = 'PALLET') THEN
1671 l_pricing_attr_rec.attribute_name := 'TL_DROPOFF_PALLET';
1672 l_pricing_attr_rec.attribute_value := l_stop_rec.dropoff_pallets;
1673 END IF;
1674
1675 x_pricing_attr_tab(l_attr_idx) := l_pricing_attr_rec;
1676
1677 l_attr_idx := l_attr_idx + 1;
1678 l_pricing_attr_rec.attribute_index := l_attr_idx;
1679 l_pricing_attr_rec.input_index := p_req_line_rec.line_index;
1680 l_pricing_attr_rec.attribute_name := 'TL_STOP_UNLOADING_ACT';
1681 l_pricing_attr_rec.attribute_value := 'Y';
1682
1683 x_pricing_attr_tab(l_attr_idx) := l_pricing_attr_rec;
1684
1685 ELSE
1686
1687 l_attr_idx := l_attr_idx + 1;
1688 l_pricing_attr_rec.attribute_index := l_attr_idx;
1689 l_pricing_attr_rec.input_index := p_req_line_rec.line_index;
1690 l_pricing_attr_rec.attribute_name := 'TL_STOP_UNLOADING_ACT';
1691 l_pricing_attr_rec.attribute_value := 'N';
1692
1693 x_pricing_attr_tab(l_attr_idx) := l_pricing_attr_rec;
1694
1695 END IF;
1696
1697 END IF; -- loading protocol
1698
1699 -- For weekday layover charges
1700 IF (l_stop_rec.weekday_layovers > 0) THEN
1701
1702 l_attr_idx := l_attr_idx + 1;
1703 l_pricing_attr_rec.attribute_index := l_attr_idx;
1704 l_pricing_attr_rec.input_index := p_req_line_rec.line_index;
1705 l_pricing_attr_rec.attribute_name := 'TL_NUM_WEEKDAY_LAYOVERS';
1706 IF (l_stop_rec.weekday_layovers > 0) THEN
1707 l_pricing_attr_rec.attribute_value := l_stop_rec.weekday_layovers;
1708 ELSE
1709 l_pricing_attr_rec.attribute_value := '0';
1710 END IF;
1711 x_pricing_attr_tab(l_attr_idx) := l_pricing_attr_rec;
1712
1713 END IF;
1714
1715 -- For weekend layover charges
1716 IF (l_stop_rec.weekend_layovers > 0) THEN
1717
1718 l_attr_idx := l_attr_idx + 1;
1719 l_pricing_attr_rec.attribute_index := l_attr_idx;
1720 l_pricing_attr_rec.input_index := p_req_line_rec.line_index;
1721 l_pricing_attr_rec.attribute_name := 'TL_WEEKEND_LAYOVER_MILEAGE';
1722 l_pricing_attr_rec.attribute_value := l_stop_rec.distance_to_next_stop;
1723
1724 x_pricing_attr_tab(l_attr_idx) := l_pricing_attr_rec;
1725
1726 --l_attr_idx := l_attr_idx + 1;
1727 --l_pricing_attr_rec.attribute_index := l_attr_idx;
1728 --l_pricing_attr_rec.input_index := p_req_line_rec.line_index;
1729 --l_pricing_attr_rec.attribute_name := 'TL_NUM_WEEKEND_LAYOVERS';
1730 --l_pricing_attr_rec.attribute_value := l_stop_rec.weekend_layovers;
1731
1732 --x_pricing_attr_tab(l_attr_idx) := l_pricing_attr_rec;
1733 END IF;
1734
1735 IF (l_stop_rec.stop_region IS NOT NULL) THEN
1736
1737 IF l_stop_rec.stop_type = 'PU' or l_stop_rec.stop_type = 'PD' THEN
1738 -- For origin charges
1739 l_attr_idx := l_attr_idx + 1;
1740 l_pricing_attr_rec.attribute_index := l_attr_idx;
1741 l_pricing_attr_rec.input_index := p_req_line_rec.line_index;
1742 l_pricing_attr_rec.attribute_name := 'TL_ORIGIN_ZONE';
1743 l_pricing_attr_rec.attribute_value := l_stop_rec.stop_region;
1744
1745 x_pricing_attr_tab(l_attr_idx) := l_pricing_attr_rec;
1746 END IF;
1747
1748 IF l_stop_rec.stop_type = 'DO' or l_stop_rec.stop_type = 'PD' THEN
1749 -- For destination charges
1750 l_attr_idx := l_attr_idx + 1;
1751 l_pricing_attr_rec.attribute_index := l_attr_idx;
1752 l_pricing_attr_rec.input_index := p_req_line_rec.line_index;
1753 l_pricing_attr_rec.attribute_name := 'TL_DESTINATION_ZONE';
1754 l_pricing_attr_rec.attribute_value := l_stop_rec.stop_region;
1755
1756 x_pricing_attr_tab(l_attr_idx) := l_pricing_attr_rec;
1757 END IF;
1758
1759 END IF;
1760
1761 END IF; -- p_stop_tab.count
1762
1763 ELSIF (p_req_line_rec.line_type = G_FACILITY_CHARGE_LINE) THEN
1764
1765 IF (p_stop_tab.COUNT > 0 AND p_stop_tab.EXISTS(p_req_line_rec.stop_index) ) THEN
1766 l_stop_rec := p_stop_tab(p_req_line_rec.stop_index);
1767
1768 l_attr_idx := l_attr_idx + 1;
1769 l_pricing_attr_rec.attribute_index := l_attr_idx;
1770 l_pricing_attr_rec.input_index := p_req_line_rec.line_index;
1771 l_pricing_attr_rec.attribute_name := 'TL_RATE_TYPE';
1772 l_pricing_attr_rec.attribute_value := 'FACILITY_CHARGE';
1773
1774 x_pricing_attr_tab(l_attr_idx) := l_pricing_attr_rec;
1775
1776 --IF (l_stop_rec.loading_protocol = 'FACILITY'
1777 -- OR l_stop_rec.loading_protocol = 'JOINT')
1778 -- AND (l_stop_rec.stop_type = 'PU'
1779 -- OR l_stop_rec.stop_type = 'DO'
1780 -- OR l_stop_rec.stop_type = 'PD') THEN
1781
1782 l_attr_idx := l_attr_idx + 1;
1783 l_pricing_attr_rec.attribute_index := l_attr_idx;
1784 l_pricing_attr_rec.input_index := p_req_line_rec.line_index;
1785 l_pricing_attr_rec.attribute_name := 'LOADING_PROTOCOL';
1786 l_pricing_attr_rec.attribute_value := l_stop_rec.loading_protocol;
1787
1788 x_pricing_attr_tab(l_attr_idx) := l_pricing_attr_rec;
1789
1790 IF (l_stop_rec.stop_type = 'PU' or l_stop_rec.stop_type = 'PD') THEN
1791
1792 -- For loading and assisted loading charges
1793
1794 IF (l_stop_rec.fac_charge_basis = 'WEIGHT') THEN
1795 l_pricing_attr_rec.attribute_name := 'FAC_PICKUP_WT';
1796 l_pricing_attr_rec.attribute_value := l_stop_rec.fac_pickup_weight;
1797 l_fac_handling_wt := l_stop_rec.fac_pickup_weight;
1798 l_attr_idx := l_attr_idx + 1;
1799 l_pricing_attr_rec.attribute_index := l_attr_idx;
1800 l_pricing_attr_rec.input_index := p_req_line_rec.line_index;
1801 x_pricing_attr_tab(l_attr_idx) := l_pricing_attr_rec;
1802 ELSIF (l_stop_rec.fac_charge_basis = 'VOLUME') THEN
1803 l_pricing_attr_rec.attribute_name := 'FAC_PICKUP_VOL';
1804 l_pricing_attr_rec.attribute_value := l_stop_rec.fac_pickup_volume;
1805 l_fac_handling_vol := l_stop_rec.fac_pickup_volume;
1806 l_attr_idx := l_attr_idx + 1;
1807 l_pricing_attr_rec.attribute_index := l_attr_idx;
1808 l_pricing_attr_rec.input_index := p_req_line_rec.line_index;
1809 x_pricing_attr_tab(l_attr_idx) := l_pricing_attr_rec;
1810 ELSIF (l_stop_rec.fac_charge_basis = 'CONTAINER') THEN
1811 l_pricing_attr_rec.attribute_name := 'FAC_PICKUP_CONTAINER';
1812 l_pricing_attr_rec.attribute_value := l_stop_rec.pickup_containers;
1813 l_attr_idx := l_attr_idx + 1;
1814 l_pricing_attr_rec.attribute_index := l_attr_idx;
1815 l_pricing_attr_rec.input_index := p_req_line_rec.line_index;
1816 x_pricing_attr_tab(l_attr_idx) := l_pricing_attr_rec;
1817 ELSIF (l_stop_rec.fac_charge_basis = 'PALLET') THEN
1818 l_pricing_attr_rec.attribute_name := 'FAC_PICKUP_PALLET';
1819 l_pricing_attr_rec.attribute_value := l_stop_rec.pickup_pallets;
1820 l_attr_idx := l_attr_idx + 1;
1821 l_pricing_attr_rec.attribute_index := l_attr_idx;
1822 l_pricing_attr_rec.input_index := p_req_line_rec.line_index;
1823 x_pricing_attr_tab(l_attr_idx) := l_pricing_attr_rec;
1824 END IF;
1825
1826 l_attr_idx := l_attr_idx + 1;
1827 l_pricing_attr_rec.attribute_index := l_attr_idx;
1828 l_pricing_attr_rec.input_index := p_req_line_rec.line_index;
1829 l_pricing_attr_rec.attribute_name := 'TL_STOP_LOADING_ACT';
1830 l_pricing_attr_rec.attribute_value := 'Y';
1831
1832 x_pricing_attr_tab(l_attr_idx) := l_pricing_attr_rec;
1833
1834 ELSE
1835
1836 l_attr_idx := l_attr_idx + 1;
1837 l_pricing_attr_rec.attribute_index := l_attr_idx;
1838 l_pricing_attr_rec.input_index := p_req_line_rec.line_index;
1839 l_pricing_attr_rec.attribute_name := 'TL_STOP_LOADING_ACT';
1840 l_pricing_attr_rec.attribute_value := 'N';
1841
1842 x_pricing_attr_tab(l_attr_idx) := l_pricing_attr_rec;
1843
1844 END IF;
1845
1846 IF (l_stop_rec.stop_type = 'DO' or l_stop_rec.stop_type = 'PD') THEN
1847 -- For unloading and assisted unloading charges
1848
1849 IF (l_stop_rec.fac_charge_basis = 'WEIGHT') THEN
1850 l_pricing_attr_rec.attribute_name := 'FAC_DROPOFF_WT';
1851 l_pricing_attr_rec.attribute_value := l_stop_rec.fac_dropoff_weight;
1852 --IF l_fac_handling_wt <= 0 THEN
1853 -- l_fac_handling_wt := l_stop_rec.fac_dropoff_weight;
1854 --Facility handling is based on sum of pickup+dropoff
1855 --END IF;
1856 --4045314
1857 l_fac_handling_wt := l_fac_handling_wt+l_stop_rec.fac_dropoff_weight;
1858
1859 l_attr_idx := l_attr_idx + 1;
1860 l_pricing_attr_rec.attribute_index := l_attr_idx;
1861 l_pricing_attr_rec.input_index := p_req_line_rec.line_index;
1862 x_pricing_attr_tab(l_attr_idx) := l_pricing_attr_rec;
1863 ELSIF (l_stop_rec.fac_charge_basis = 'VOLUME') THEN
1864 l_pricing_attr_rec.attribute_name := 'FAC_DROPOFF_VOL';
1865 l_pricing_attr_rec.attribute_value := l_stop_rec.fac_dropoff_volume;
1866 --IF l_fac_handling_vol <= 0 THEN
1867 -- l_fac_handling_vol := l_stop_rec.fac_dropoff_volume;
1868 --END IF;
1869 --4045314
1870 l_fac_handling_vol := l_fac_handling_vol+l_stop_rec.fac_dropoff_volume;
1871
1872 l_attr_idx := l_attr_idx + 1;
1873 l_pricing_attr_rec.attribute_index := l_attr_idx;
1874 l_pricing_attr_rec.input_index := p_req_line_rec.line_index;
1875 x_pricing_attr_tab(l_attr_idx) := l_pricing_attr_rec;
1876 ELSIF (l_stop_rec.fac_charge_basis = 'CONTAINER') THEN
1877 l_pricing_attr_rec.attribute_name := 'FAC_DROPOFF_CONTAINER';
1878 l_pricing_attr_rec.attribute_value := l_stop_rec.dropoff_containers;
1879 l_attr_idx := l_attr_idx + 1;
1880 l_pricing_attr_rec.attribute_index := l_attr_idx;
1881 l_pricing_attr_rec.input_index := p_req_line_rec.line_index;
1882 x_pricing_attr_tab(l_attr_idx) := l_pricing_attr_rec;
1883 ELSIF (l_stop_rec.fac_charge_basis = 'PALLET') THEN
1884 l_pricing_attr_rec.attribute_name := 'FAC_DROPOFF_PALLET';
1885 l_pricing_attr_rec.attribute_value := l_stop_rec.dropoff_pallets;
1886 l_attr_idx := l_attr_idx + 1;
1887 l_pricing_attr_rec.attribute_index := l_attr_idx;
1888 l_pricing_attr_rec.input_index := p_req_line_rec.line_index;
1889 x_pricing_attr_tab(l_attr_idx) := l_pricing_attr_rec;
1890 END IF;
1891
1892 l_attr_idx := l_attr_idx + 1;
1893 l_pricing_attr_rec.attribute_index := l_attr_idx;
1894 l_pricing_attr_rec.input_index := p_req_line_rec.line_index;
1895 l_pricing_attr_rec.attribute_name := 'TL_STOP_UNLOADING_ACT';
1896 l_pricing_attr_rec.attribute_value := 'Y';
1897
1898 x_pricing_attr_tab(l_attr_idx) := l_pricing_attr_rec;
1899
1900 ELSE
1901
1902 l_attr_idx := l_attr_idx + 1;
1903 l_pricing_attr_rec.attribute_index := l_attr_idx;
1904 l_pricing_attr_rec.input_index := p_req_line_rec.line_index;
1905 l_pricing_attr_rec.attribute_name := 'TL_STOP_UNLOADING_ACT';
1906 l_pricing_attr_rec.attribute_value := 'N';
1907
1908 x_pricing_attr_tab(l_attr_idx) := l_pricing_attr_rec;
1909
1910 END IF;
1911
1912 -- For handling charges
1913 --TODO : which weights (pickup/dropoff) are used for facility handling charges?
1914
1915 --This has now been clarified we take the sum of pickup + dropoff 4045314
1916
1917 IF (l_stop_rec.fac_charge_basis = 'WEIGHT') THEN
1918 l_pricing_attr_rec.attribute_name := 'FAC_HANDLING_WT';
1919 l_pricing_attr_rec.attribute_value := l_fac_handling_wt;
1920 l_attr_idx := l_attr_idx + 1;
1921 l_pricing_attr_rec.attribute_index := l_attr_idx;
1922 l_pricing_attr_rec.input_index := p_req_line_rec.line_index;
1923 x_pricing_attr_tab(l_attr_idx) := l_pricing_attr_rec;
1924 ELSIF (l_stop_rec.fac_charge_basis = 'VOLUME') THEN
1925 l_pricing_attr_rec.attribute_name := 'FAC_HANDLING_VOL';
1926 l_pricing_attr_rec.attribute_value := l_fac_handling_vol;
1927 l_attr_idx := l_attr_idx + 1;
1928 l_pricing_attr_rec.attribute_index := l_attr_idx;
1929 l_pricing_attr_rec.input_index := p_req_line_rec.line_index;
1930 x_pricing_attr_tab(l_attr_idx) := l_pricing_attr_rec;
1931 ELSIF (l_stop_rec.fac_charge_basis = 'CONTAINER') THEN
1932 l_pricing_attr_rec.attribute_name := 'FAC_HANDLING_CONTAINER';
1933 l_pricing_attr_rec.attribute_value := l_stop_rec.dropoff_containers;
1934 l_attr_idx := l_attr_idx + 1;
1935 l_pricing_attr_rec.attribute_index := l_attr_idx;
1936 l_pricing_attr_rec.input_index := p_req_line_rec.line_index;
1937 x_pricing_attr_tab(l_attr_idx) := l_pricing_attr_rec;
1938 ELSIF (l_stop_rec.fac_charge_basis = 'PALLET') THEN
1939 l_pricing_attr_rec.attribute_name := 'FAC_HANDLING_PALLET';
1940 l_pricing_attr_rec.attribute_value := l_stop_rec.dropoff_pallets;
1941 l_attr_idx := l_attr_idx + 1;
1942 l_pricing_attr_rec.attribute_index := l_attr_idx;
1943 l_pricing_attr_rec.input_index := p_req_line_rec.line_index;
1944 x_pricing_attr_tab(l_attr_idx) := l_pricing_attr_rec;
1945 END IF;
1946
1947 l_attr_idx := l_attr_idx + 1;
1948 l_pricing_attr_rec.attribute_index := l_attr_idx;
1949 l_pricing_attr_rec.input_index := p_req_line_rec.line_index;
1950 l_pricing_attr_rec.attribute_name := 'TL_HANDLING_ACT';
1951 l_pricing_attr_rec.attribute_value := 'Y';
1952
1953 x_pricing_attr_tab(l_attr_idx) := l_pricing_attr_rec;
1954
1955 --END IF; -- loading protocol
1956
1957 END IF; -- p_stop_tab.count
1958
1959 END IF;
1960
1961 fte_freight_pricing_util.unset_method(l_log_level,l_method_name);
1962
1963 EXCEPTION
1964 WHEN FND_API.G_EXC_ERROR THEN
1965 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1966 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_method_name);
1967
1968 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1969 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1970 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_method_name);
1971
1972 WHEN OTHERS THEN
1973 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1974 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_others');
1975 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
1976 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_method_name);
1977
1978 END create_charge_line_attributes;
1979
1980 --
1981 -- procedure : create_line_qualifiers
1982 -- Hides details of creating qualifiers for a given input line
1983 --
1984
1985 PROCEDURE create_line_qualifiers (
1986 p_req_line_rec IN req_line_info_rec_type,
1987 p_trip_rec IN FTE_TL_CACHE.TL_trip_data_input_rec_type,
1988 p_stop_tab IN FTE_TL_CACHE.TL_trip_stop_input_tab_type,
1989 p_carrier_pref IN FTE_TL_CACHE.TL_carrier_pref_rec_type,
1990 x_return_status OUT NOCOPY VARCHAR2)
1991 IS
1992 l_qual_rec fte_qp_engine.qualifier_rec_type;
1993 l_return_status VARCHAR2(1);
1994 l_qual_idx NUMBER := 0;
1995 l_log_level NUMBER := fte_freight_pricing_util.G_DBG;
1996 l_method_name VARCHAR2(50) := 'create_line_qualifiers';
1997 BEGIN
1998
1999 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2000 fte_freight_pricing_util.reset_dbg_vars;
2001 fte_freight_pricing_util.set_method(l_log_level,l_method_name);
2002
2003
2004 IF (p_req_line_rec.line_type <> G_FACILITY_CHARGE_LINE ) THEN
2005
2006 -- carrier related qualifiers
2007
2008 l_qual_idx := l_qual_idx + 1;
2009
2010 l_qual_rec.qualifier_index := l_qual_idx;
2011 l_qual_rec.input_index := p_req_line_rec.line_index;
2012 l_qual_rec.qualifier_name :='PRICELIST';
2013 l_qual_rec.qualifier_value := to_char(p_req_line_rec.pricelist_id);
2014
2015 fte_qp_engine.create_qual_record (p_event_num => fte_qp_engine.G_LINE_EVENT_NUM,
2016 p_qual_rec => l_qual_rec,
2017 x_return_status => l_return_status);
2018
2019 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2020 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2021 FTE_FREIGHT_PRICING_UTIL.set_location(p_loc => 'After create_qual_record ');
2022 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_tl_cr_qual_rec_failed');
2023 raise FND_API.G_EXC_ERROR;
2024 END IF;
2025 END IF;
2026
2027 l_qual_idx := l_qual_idx + 1;
2028
2029 l_qual_rec.qualifier_index := l_qual_idx;
2030 l_qual_rec.input_index := p_req_line_rec.line_index;
2031 l_qual_rec.qualifier_name :='SUPPLIER';
2032 l_qual_rec.qualifier_value := to_char(p_req_line_rec.carrier_id);
2033
2034 fte_qp_engine.create_qual_record (p_event_num => fte_qp_engine.G_LINE_EVENT_NUM,
2035 p_qual_rec => l_qual_rec,
2036 x_return_status => l_return_status);
2037
2038 l_qual_idx := l_qual_idx + 1;
2039
2040 l_qual_rec.qualifier_index := l_qual_idx;
2041 l_qual_rec.input_index := p_req_line_rec.line_index;
2042 l_qual_rec.qualifier_name :='MODE_OF_TRANSPORT';
2043 l_qual_rec.qualifier_value := p_trip_rec.mode_of_transport;
2044
2045 fte_qp_engine.create_qual_record (p_event_num => fte_qp_engine.G_LINE_EVENT_NUM,
2046 p_qual_rec => l_qual_rec,
2047 x_return_status => l_return_status);
2048
2049 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2050 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2051 FTE_FREIGHT_PRICING_UTIL.set_location(p_loc => 'After create_qual_record ');
2052 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_tl_cr_qual_rec_failed');
2053 raise FND_API.G_EXC_ERROR;
2054 END IF;
2055 END IF;
2056
2057 l_qual_idx := l_qual_idx + 1;
2058
2059 l_qual_rec.qualifier_index := l_qual_idx;
2060 l_qual_rec.input_index := p_req_line_rec.line_index;
2061 l_qual_rec.qualifier_name :='SERVICE_TYPE';
2062 l_qual_rec.qualifier_value := p_trip_rec.service_type;
2063
2064 fte_qp_engine.create_qual_record (p_event_num => fte_qp_engine.G_LINE_EVENT_NUM,
2065 p_qual_rec => l_qual_rec,
2066 x_return_status => l_return_status);
2067
2068 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2069 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2070 FTE_FREIGHT_PRICING_UTIL.set_location(p_loc => 'After create_qual_record ');
2071 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_tl_cr_qual_rec_failed');
2072 raise FND_API.G_EXC_ERROR;
2073 END IF;
2074 END IF;
2075
2076 ELSE
2077
2078 -- Facility price list
2079
2080 l_qual_idx := l_qual_idx + 1;
2081
2082 l_qual_rec.qualifier_index := l_qual_idx;
2083 l_qual_rec.input_index := p_req_line_rec.line_index;
2084 l_qual_rec.qualifier_name :='PRICELIST';
2085 l_qual_rec.qualifier_value := to_char(p_req_line_rec.pricelist_id);
2086
2087 fte_qp_engine.create_qual_record (p_event_num => fte_qp_engine.G_LINE_EVENT_NUM,
2088 p_qual_rec => l_qual_rec,
2089 x_return_status => l_return_status);
2090
2091 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2092 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2093 FTE_FREIGHT_PRICING_UTIL.set_location(p_loc => 'After create_qual_record ');
2094 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_tl_cr_qual_rec_failed');
2095 raise FND_API.G_EXC_ERROR;
2096 END IF;
2097 END IF;
2098
2099 END IF;
2100
2101 fte_freight_pricing_util.unset_method(l_log_level,l_method_name);
2102
2103 EXCEPTION
2104 WHEN FND_API.G_EXC_ERROR THEN
2105 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2106 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_method_name);
2107
2108 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2109 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2110 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_method_name);
2111
2112 WHEN OTHERS THEN
2113 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2114 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_others');
2115 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
2116 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_method_name);
2117
2118
2119 END create_line_qualifiers;
2120
2121 PROCEDURE create_control_rec ( x_return_status OUT NOCOPY VARCHAR2)
2122 IS
2123 l_return_status VARCHAR2(1);
2124 l_log_level NUMBER := fte_freight_pricing_util.G_DBG;
2125 l_method_name VARCHAR2(50) := 'create_control_rec';
2126 BEGIN
2127 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2128 fte_freight_pricing_util.reset_dbg_vars;
2129 fte_freight_pricing_util.set_method(l_log_level,l_method_name);
2130
2131 fte_qp_engine.create_control_record (p_event_num => fte_qp_engine.G_LINE_EVENT_NUM,
2132 x_return_status => l_return_status);
2133
2134 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2135 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2136 FTE_FREIGHT_PRICING_UTIL.set_location(p_loc => 'After create_qual_record ');
2137 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_tl_cr_control_rec_failed');
2138 raise FND_API.G_EXC_ERROR;
2139 END IF;
2140 END IF;
2141
2142 x_return_status := l_return_status;
2143
2144 fte_freight_pricing_util.unset_method(l_log_level,l_method_name);
2145
2146 EXCEPTION
2147 WHEN FND_API.G_EXC_ERROR THEN
2148 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2149 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_method_name);
2150
2151 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2152 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2153 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_method_name);
2154
2155 WHEN OTHERS THEN
2156 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2157 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_others');
2158 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
2159 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_method_name);
2160
2161 END create_control_rec;
2162
2163 PROCEDURE init_stop_rec (x_stop_rec IN OUT NOCOPY FTE_TL_CACHE.TL_trip_stop_output_rec_type )
2164 IS
2165 l_log_level NUMBER := fte_freight_pricing_util.G_DBG;
2166 l_method_name VARCHAR2(50) := 'init_stop_rec';
2167 BEGIN
2168 fte_freight_pricing_util.reset_dbg_vars;
2169 fte_freight_pricing_util.set_method(l_log_level,l_method_name);
2170 x_stop_rec.stop_id := 0;
2171 x_stop_rec.trip_id := 0;
2172 x_stop_rec.weekday_layover_chrg := 0;
2173 x_stop_rec.weekend_layover_chrg := 0;
2174 x_stop_rec.loading_chrg := 0;
2175 x_stop_rec.loading_chrg_basis := null;
2176 x_stop_rec.ast_loading_chrg := 0;
2177 x_stop_rec.ast_loading_chrg_basis := null;
2178 x_stop_rec.unloading_chrg := 0;
2179 x_stop_rec.unloading_chrg_basis := 0;
2180 x_stop_rec.ast_unloading_chrg := 0;
2181 x_stop_rec.ast_unloading_chrg_basis := null;
2182 x_stop_rec.origin_surchrg := 0;
2183 x_stop_rec.destination_surchrg := 0;
2184 x_stop_rec.fac_loading_chrg := 0;
2185 x_stop_rec.fac_loading_chrg_basis := null;
2186 x_stop_rec.fac_ast_loading_chrg := 0;
2187 x_stop_rec.fac_ast_loading_chrg_basis := null;
2188 x_stop_rec.fac_unloading_chrg := 0;
2189 x_stop_rec.fac_unloading_chrg_basis := null;
2190 x_stop_rec.fac_ast_unloading_chrg := 0;
2191 x_stop_rec.fac_ast_unloading_chrg_basis := null;
2192 x_stop_rec.fac_handling_chrg := 0;
2193 x_stop_rec.fac_handling_chrg_basis := null;
2194 x_stop_rec.fac_currency := null;
2195 fte_freight_pricing_util.unset_method(l_log_level,l_method_name);
2196
2197 EXCEPTION
2198 WHEN OTHERS THEN
2199 --x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2200 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_others');
2201 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
2202 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_method_name);
2203
2204 END init_stop_rec;
2205
2206 --
2207 -- Procedure : retrieve_qp_output
2208 -- Reads the qp output lines and details, extracts various base rates and charges
2209 -- and plugs in the values into the output data structures.These data structures
2210 -- can be utilized by cost allocation.
2211 --
2212
2213 PROCEDURE retrieve_qp_output (
2214 p_trip_rec IN FTE_TL_CACHE.TL_trip_data_input_rec_type,
2215 p_stop_tab IN FTE_TL_CACHE.TL_trip_stop_input_tab_type,
2216 p_carrier_pref IN FTE_TL_CACHE.TL_carrier_pref_rec_type,
2217 p_qp_output_line_rows IN QP_PREQ_GRP.LINE_TBL_TYPE,
2218 p_qp_output_detail_rows IN QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE,
2219 x_trip_charges_rec OUT NOCOPY FTE_TL_CACHE.TL_trip_output_rec_type,
2220 x_stop_charges_tab OUT NOCOPY FTE_TL_CACHE.TL_trip_stop_output_tab_type,
2221 x_return_status OUT NOCOPY VARCHAR2)
2222 IS
2223 l_return_status VARCHAR2(1);
2224 i NUMBER := 0;
2225 line_idx NUMBER := 0;
2226
2227 l_loaded_dist_price NUMBER := 0;
2228 l_unit_loaded_dist_price NUMBER := 0;
2229 l_unloaded_dist_price NUMBER := 0;
2230 l_unit_unloaded_dist_price NUMBER := 0;
2231 l_cm_dist_price NUMBER := 0;
2232 l_unit_cm_dist_price NUMBER := 0;
2233 l_unit_base_price NUMBER := 0;
2234 l_unit_unit_base_price NUMBER := 0;
2235 l_time_price NUMBER := 0;
2236 l_unit_time_price NUMBER := 0;
2237 l_flat_price NUMBER := 0;
2238 l_num_of_weekend_layover NUMBER;
2239 l_stop_index NUMBER := 0;
2240
2241 l_qp_out_det_rec QP_PREQ_GRP.LINE_DETAIL_REC_TYPE;
2242 l_stop_rec FTE_TL_CACHE.TL_trip_stop_output_rec_type;
2243 l_stop_id NUMBER;
2244 l_req_line_info_rec req_line_info_rec_type;
2245 l_log_level NUMBER := fte_freight_pricing_util.G_DBG;
2246 l_method_name VARCHAR2(50) := 'retrieve_qp_output';
2247
2248 BEGIN
2249 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2250 fte_freight_pricing_util.reset_dbg_vars;
2251 fte_freight_pricing_util.set_method(l_log_level,l_method_name);
2252
2253 -- Loop through the line table
2254 -- look for base rates returned (incl. continuous move rates if applicable)
2255 -- Loop through the detail table
2256 -- look for minimum charges on base prices (how will we process these?)
2257 -- look for accessorial charges
2258 -- look for continuous move discount **
2259 -- Apply minimum charges to base prices ?
2260 -- Apply continuous move discount if applicable (or will cost allocation deal with it?)
2261 -- uom conversion?
2262
2263 ------------- BASE PRICES -----------------------------------
2264
2265 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
2266 'g_req_line_info_tab.COUNT = '||g_req_line_info_tab.COUNT);
2267 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
2268 'p_qp_output_line_rows.COUNT = '||p_qp_output_line_rows.COUNT);
2269
2270 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
2271 'Now looping on p_qp_output_line_rows ');
2272
2273 i := p_qp_output_line_rows.FIRST;
2274 IF (i IS NOT NULL) THEN
2275 LOOP
2276 -- get line index
2277 -- get req info for line index
2278 -- get base rate (if applicable)
2279 -- loop thru line details
2280 -- depending upon the type (purpose) of the line, filter out unwanted details
2281 -- analyze the charge sub type code, and extract the charge.
2282
2283 line_idx := p_qp_output_line_rows(i).line_index;
2284 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
2285 'line_idx = '||line_idx);
2286
2287 --
2288 -- NOTE : We use adjusted_unit_price instead on unit_price on most base rates
2289 -- to account for minimum charges that get applied. If there are no
2290 -- minimum charges, then unit_price will be equal to adjusted_unit_price.
2291 -- This is because currently we don't have any other modifiers that
2292 -- apply on these lines.
2293
2294 IF ( g_req_line_info_tab(line_idx).line_type = G_LOADED_DIST_BASE_LINE ) THEN
2295 -- get loaded distance base rate
2296 IF (p_qp_output_line_rows(i).adjusted_unit_price IS NOT NULL) THEN
2297 l_loaded_dist_price := p_qp_output_line_rows(i).line_quantity
2298 * p_qp_output_line_rows(i).adjusted_unit_price;
2299 l_unit_loaded_dist_price := p_qp_output_line_rows(i).adjusted_unit_price;
2300 END IF;
2301 END IF;
2302
2303 IF ( g_req_line_info_tab(line_idx).line_type = G_UNLOADED_DIST_BASE_LINE ) THEN
2304 -- get unloaded distance base rate
2305 IF (p_qp_output_line_rows(i).adjusted_unit_price IS NOT NULL) THEN
2306 l_unloaded_dist_price := p_qp_output_line_rows(i).line_quantity
2307 * p_qp_output_line_rows(i).adjusted_unit_price;
2308 l_unit_unloaded_dist_price := p_qp_output_line_rows(i).adjusted_unit_price;
2309 END IF;
2310 END IF;
2311
2312
2313 IF ( g_req_line_info_tab(line_idx).line_type = G_CONT_DIST_BASE_LINE ) THEN
2314 -- get continuous distance base rate (or cm deadhead rate)
2315 IF (p_qp_output_line_rows(i).adjusted_unit_price IS NOT NULL) THEN
2316 l_cm_dist_price := p_qp_output_line_rows(i).line_quantity
2317 * p_qp_output_line_rows(i).adjusted_unit_price;
2318 l_unit_cm_dist_price := p_qp_output_line_rows(i).adjusted_unit_price;
2319 END IF;
2320 END IF;
2321
2322 IF ( g_req_line_info_tab(line_idx).line_type = G_CONT_DH_BASE_LINE ) THEN
2323 -- get continuous distance base rate (or cm deadhead rate)
2324 IF (p_qp_output_line_rows(i).adjusted_unit_price IS NOT NULL) THEN
2325 l_unloaded_dist_price := p_qp_output_line_rows(i).line_quantity
2326 * p_qp_output_line_rows(i).adjusted_unit_price;
2327 l_unit_unloaded_dist_price := p_qp_output_line_rows(i).adjusted_unit_price;
2328 END IF;
2329 END IF;
2330
2331 IF ( g_req_line_info_tab(line_idx).line_type = G_UNITS_BASE_LINE ) THEN
2332 -- get unit base rate
2333 IF (p_qp_output_line_rows(i).adjusted_unit_price IS NOT NULL) THEN
2334 l_unit_base_price := p_qp_output_line_rows(i).line_quantity
2335 * p_qp_output_line_rows(i).adjusted_unit_price;
2336 l_unit_unit_base_price := p_qp_output_line_rows(i).adjusted_unit_price;
2337 END IF;
2338 END IF;
2339
2340 IF ( g_req_line_info_tab(line_idx).line_type = G_TIME_BASE_LINE ) THEN
2341 -- get time base rate
2342 IF (p_qp_output_line_rows(i).adjusted_unit_price IS NOT NULL) THEN
2343 l_time_price := p_qp_output_line_rows(i).line_quantity
2344 * p_qp_output_line_rows(i).adjusted_unit_price;
2345 l_unit_time_price := p_qp_output_line_rows(i).adjusted_unit_price;
2346 END IF;
2347 END IF;
2348
2349 IF ( g_req_line_info_tab(line_idx).line_type = G_FLAT_BASE_LINE ) THEN
2350 -- get loaded distance base rate
2351 IF (p_qp_output_line_rows(i).unit_price IS NOT NULL) THEN
2352 l_flat_price := p_qp_output_line_rows(i).line_quantity
2353 * p_qp_output_line_rows(i).unit_price;
2354 END IF;
2355 END IF;
2356
2357 EXIT WHEN i = p_qp_output_line_rows.LAST;
2358 i := p_qp_output_line_rows.NEXT(i);
2359 END LOOP;
2360 END IF;
2361
2362 -- Generate base price output --
2363 -- Assumption : both loaded/unloaded and cm dist price cannot be non-zero at the same time.
2364 -- Does not include continuous move discount
2365 x_trip_charges_rec.trip_id := p_trip_rec.trip_id;
2366 x_trip_charges_rec.currency := p_carrier_pref.currency;
2367 -- x_trip_charges_rec.base_distance_chrg := l_loaded_dist_price + l_unloaded_dist_price + l_cm_dist_price ;
2368 IF (l_cm_dist_price <> 0) THEN
2369 x_trip_charges_rec.base_dist_load_chrg := l_cm_dist_price;
2370 x_trip_charges_rec.base_dist_load_unit_chrg := l_unit_cm_dist_price;
2371 ELSE
2372 x_trip_charges_rec.base_dist_load_chrg := l_loaded_dist_price;
2373 x_trip_charges_rec.base_dist_load_unit_chrg := l_unit_loaded_dist_price;
2374 END IF;
2375 x_trip_charges_rec.base_dist_unload_chrg := l_unloaded_dist_price;
2376 x_trip_charges_rec.base_dist_unload_unit_chrg := l_unit_unloaded_dist_price;
2377
2378 x_trip_charges_rec.base_unit_chrg := l_unit_base_price;
2379 x_trip_charges_rec.base_unit_unit_chrg := l_unit_unit_base_price;
2380 x_trip_charges_rec.base_time_chrg := l_time_price;
2381 x_trip_charges_rec.base_time_unit_chrg := l_unit_time_price;
2382 x_trip_charges_rec.base_flat_chrg := l_flat_price;
2383
2384 -- init the load charge
2385 x_trip_charges_rec.out_of_route_chrg := 0;
2386 x_trip_charges_rec.stop_off_chrg := 0;
2387 x_trip_charges_rec.document_chrg := 0;
2388 x_trip_charges_rec.handling_chrg := 0;
2389 x_trip_charges_rec.cm_discount_percent := 0;
2390 x_trip_charges_rec.fuel_chrg := 0;
2391
2392 -- Generate dummy entries into x_stop_charges_tab --
2393 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
2394 'Added dummy records to x_stop_charges_rec');
2395 IF (p_stop_tab.COUNT >0) THEN
2396 l_stop_index := p_trip_rec.stop_reference;
2397 LOOP
2398 init_stop_rec (x_stop_rec => l_stop_rec);
2399 IF (l_stop_index >= (p_trip_rec.stop_reference + p_trip_rec.number_of_stops ) )
2400 THEN
2401 EXIT;
2402 END IF;
2403 l_stop_rec.stop_id := p_stop_tab(l_stop_index).stop_id;
2404 l_stop_rec.trip_id := p_trip_rec.trip_id;
2405 l_stop_rec.fac_currency := p_stop_tab(l_stop_index).fac_currency;
2406
2407 x_stop_charges_tab(l_stop_index) := l_stop_rec;
2408
2409 EXIT WHEN l_stop_index >= p_stop_tab.LAST;
2410 l_stop_index := p_stop_tab.NEXT(l_stop_index);
2411 END LOOP;
2412 END IF;
2413
2414 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
2415 'x_stop_charges_tab.COUNT='||x_stop_charges_tab.COUNT);
2416
2417
2418 ----------- ACCESSORIAL CHARGES,DISCOUNTS AND BASE MIN CHARGES -----------------------
2419
2420 -- Query line details --
2421
2422 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
2423 'Now looping on p_qp_output_detail_rows ');
2424
2425 i := p_qp_output_detail_rows.FIRST;
2426 IF (i IS NOT NULL) THEN
2427 LOOP
2428
2429 -- get load level charges
2430 -- get continuous move discount (if applicable)
2431 -- get stop level charges (for each stop)
2432 -- get facility charges (for each stop)
2433
2434 l_qp_out_det_rec := p_qp_output_detail_rows(i);
2435 l_req_line_info_rec := g_req_line_info_tab(l_qp_out_det_rec.line_index);
2436
2437 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
2438 'i = '||i);
2439 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
2440 'l_qp_out_det_rec.line_index='||l_qp_out_det_rec.line_index);
2441
2442 -------------------- MIN BASE CHARGES -------------------
2443
2444
2445 IF (l_req_line_info_rec.line_type = G_LOADED_DIST_BASE_LINE ) THEN
2446
2447 -- Min charges for distance charges is across all dist types
2448 -- It is assumed that both continuous move line and loaded dist line
2449 -- do not exist in the same call
2450
2451 IF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_MIN_DISTANCE_CH
2452 AND nvl(l_qp_out_det_rec.adjustment_amount,0) >0 ) THEN
2453 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
2454 'MIN_CHARGE: Minimum charge applied to distance base rates');
2455 END IF;
2456
2457 END IF;
2458
2459 IF (l_req_line_info_rec.line_type = G_CONT_DIST_BASE_LINE
2460 AND l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_MIN_DISTANCE_CH
2461 AND nvl(l_qp_out_det_rec.adjustment_amount,0) >0 ) THEN
2462
2463 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
2464 'MIN_CHARGE: Minimum charge applied to distance base rates (continuous move)');
2465
2466 END IF;
2467
2468 IF (l_req_line_info_rec.line_type = G_UNITS_BASE_LINE
2469 AND l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_MIN_UNIT_CH
2470 AND nvl(l_qp_out_det_rec.adjustment_amount,0) > 0) THEN
2471
2472 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
2473 'MIN_CHARGE: Minimum charge applied to units base rates ');
2474
2475 END IF;
2476
2477 IF (l_req_line_info_rec.line_type = G_TIME_BASE_LINE
2478 AND l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_MIN_TIME_CH
2479 AND nvl(l_qp_out_det_rec.adjustment_amount,0) > 0) THEN
2480
2481 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
2482 'MIN_CHARGE: Minimum charge applied to time base rates ');
2483
2484 END IF;
2485
2486 -------------------- LOAD (TRIP) CHARGES -------------------
2487
2488 IF (l_req_line_info_rec.line_type = G_LOAD_CHARGE_LINE ) THEN
2489
2490 -- dig up load (trip) level charges
2491 IF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_STOP_OFF_CH) THEN
2492 x_trip_charges_rec.stop_off_chrg := l_qp_out_det_rec.adjustment_amount;
2493 END IF;
2494
2495 IF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_OUT_OF_ROUTE_CH) THEN
2496 IF (l_qp_out_det_rec.adjustment_amount is not null
2497 AND l_qp_out_det_rec.line_quantity is not null) THEN
2498 -- x_trip_charges_rec.out_of_route_chrg :=
2499 -- l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
2500 -- bug 3474455
2501 x_trip_charges_rec.out_of_route_chrg := l_qp_out_det_rec.adjustment_amount;
2502 END IF;
2503 END IF;
2504
2505 IF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_DOCUMENT_CH) THEN
2506 x_trip_charges_rec.document_chrg := l_qp_out_det_rec.adjustment_amount;
2507 END IF;
2508
2509 IF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_HANDLING_WEIGHT_CH) THEN
2510 IF (l_qp_out_det_rec.adjustment_amount is not null
2511 AND l_qp_out_det_rec.line_quantity is not null) THEN
2512 -- x_trip_charges_rec.handling_chrg :=
2513 -- l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
2514 -- bug 3474455
2515 x_trip_charges_rec.handling_chrg := l_qp_out_det_rec.adjustment_amount;
2516 x_trip_charges_rec.handling_chrg_basis := fte_rtg_globals.G_WEIGHT_BASIS;
2517 END IF;
2518 END IF;
2519
2520 IF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_HANDLING_VOLUME_CH) THEN
2521 IF (l_qp_out_det_rec.adjustment_amount is not null
2522 AND l_qp_out_det_rec.line_quantity is not null) THEN
2523 -- x_trip_charges_rec.handling_chrg :=
2524 -- l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
2525 -- bug 3474455
2526 x_trip_charges_rec.handling_chrg := l_qp_out_det_rec.adjustment_amount;
2527 x_trip_charges_rec.handling_chrg_basis := fte_rtg_globals.G_VOLUME_BASIS;
2528 END IF;
2529 END IF;
2530
2531 IF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_HANDLING_FLAT_CH) THEN
2532 x_trip_charges_rec.handling_chrg := l_qp_out_det_rec.adjustment_amount;
2533 x_trip_charges_rec.handling_chrg_basis := fte_rtg_globals.G_FLAT_BASIS;
2534 END IF;
2535
2536 -- TODO : Add other handling basis if we support them
2537
2538 IF (l_qp_out_det_rec.charge_subtype_code
2539 = fte_rtg_globals.G_C_CONTINUOUS_MOVE_DISCOUNT) THEN
2540 x_trip_charges_rec.cm_discount_percent := l_qp_out_det_rec.operand_value;
2541 END IF;
2542
2543 -- Fuel Surcharge : bug: 3353264 (enhancement)
2544 IF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_FUEL_CH) THEN
2545 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
2546 'G_C_FUEL_CH : operand_value='||l_qp_out_det_rec.operand_value);
2547 IF (l_qp_out_det_rec.operand_value >0) THEN
2548 x_trip_charges_rec.fuel_chrg :=
2549 ( (x_trip_charges_rec.base_dist_load_chrg
2550 + x_trip_charges_rec.base_dist_unload_chrg
2551 + x_trip_charges_rec.base_unit_chrg
2552 + x_trip_charges_rec.base_time_chrg
2553 + x_trip_charges_rec.base_flat_chrg) * l_qp_out_det_rec.operand_value )/100;
2554 ELSE
2555 x_trip_charges_rec.fuel_chrg := 0;
2556 END IF;
2557 END IF;
2558
2559 END IF; -- load charges
2560
2561 -------------------- STOP CHARGES --------------------------
2562 -- Initialize l_stop_rec before each iteration. Otherwise nasty spillover effect
2563 l_stop_rec.stop_id := 0;
2564 l_stop_rec.trip_id := 0;
2565 l_stop_rec.weekday_layover_chrg := 0;
2566 l_stop_rec.weekend_layover_chrg := 0;
2567 l_stop_rec.loading_chrg := 0;
2568 l_stop_rec.loading_chrg_basis := null;
2569 l_stop_rec.ast_loading_chrg := 0;
2570 l_stop_rec.ast_loading_chrg_basis := null;
2571 l_stop_rec.unloading_chrg := 0;
2572 l_stop_rec.unloading_chrg_basis := 0;
2573 l_stop_rec.ast_unloading_chrg := 0;
2574 l_stop_rec.ast_unloading_chrg_basis := null;
2575 l_stop_rec.origin_surchrg := 0;
2576 l_stop_rec.destination_surchrg := 0;
2577 l_stop_rec.fac_loading_chrg := 0;
2578 l_stop_rec.fac_loading_chrg_basis := null;
2579 l_stop_rec.fac_ast_loading_chrg := 0;
2580 l_stop_rec.fac_ast_loading_chrg_basis := null;
2581 l_stop_rec.fac_unloading_chrg := 0;
2582 l_stop_rec.fac_unloading_chrg_basis := null;
2583 l_stop_rec.fac_ast_unloading_chrg := 0;
2584 l_stop_rec.fac_ast_unloading_chrg_basis := null;
2585 l_stop_rec.fac_handling_chrg := 0;
2586 l_stop_rec.fac_handling_chrg_basis := null;
2587 l_stop_rec.fac_currency := null;
2588
2589 IF (l_req_line_info_rec.line_type = G_STOP_CHARGE_LINE
2590 OR l_req_line_info_rec.line_type = G_FACILITY_CHARGE_LINE ) THEN
2591
2592 l_stop_index := g_req_line_info_tab(p_qp_output_detail_rows(i).line_index).stop_index;
2593 l_stop_id := p_stop_tab(l_stop_index).stop_id;
2594
2595 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
2596 'l_stop_index='||l_stop_index||' l_stop_id='||l_stop_id);
2597
2598 /*
2599 -- check if this stop_id is already in the stop output table
2600 -- *** Assumes that x_stop_charges_tab is indexed by stop_id
2601 IF ( x_stop_charges_tab.EXISTS(l_stop_id) ) THEN
2602 l_stop_rec := x_stop_charges_tab(l_stop_id);
2603 ELSE
2604 l_stop_rec.stop_id := l_stop_id;
2605 l_stop_rec.trip_id := p_trip_rec.trip_id;
2606 l_stop_rec.fac_currency := p_stop_tab(l_stop_index).fac_currency;
2607 END IF;
2608 */
2609 IF ( x_stop_charges_tab.EXISTS(l_stop_index) ) THEN
2610 l_stop_rec := x_stop_charges_tab(l_stop_index);
2611 ELSE
2612 l_stop_rec.stop_id := l_stop_id;
2613 l_stop_rec.trip_id := p_stop_tab(l_stop_index).trip_id;
2614 l_stop_rec.fac_currency := p_stop_tab(l_stop_index).fac_currency;
2615 END IF;
2616
2617
2618 END IF;
2619
2620
2621 -------------------- CARRIER STOP CHARGES -------------------
2622
2623 IF (l_req_line_info_rec.line_type = G_STOP_CHARGE_LINE ) THEN
2624 -- dig up stop level charges
2625
2626 -- Look for Loading and Assisted Loading Charges --
2627
2628 IF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_LOADING_WEIGHT_CH ) THEN
2629
2630 IF (l_qp_out_det_rec.adjustment_amount is not null
2631 AND l_qp_out_det_rec.line_quantity is not null) THEN
2632 -- l_stop_rec.loading_chrg :=
2633 -- l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
2634 -- bug 3474455
2635 l_stop_rec.loading_chrg := l_qp_out_det_rec.adjustment_amount;
2636 l_stop_rec.loading_chrg_basis := fte_rtg_globals.G_WEIGHT_BASIS;
2637 END IF;
2638
2639 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_LOADING_VOLUME_CH ) THEN
2640
2641 IF (l_qp_out_det_rec.adjustment_amount is not null
2642 AND l_qp_out_det_rec.line_quantity is not null) THEN
2643 --l_stop_rec.loading_chrg :=
2644 -- l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
2645 -- bug 3474455
2646 l_stop_rec.loading_chrg := l_qp_out_det_rec.adjustment_amount;
2647 l_stop_rec.loading_chrg_basis := fte_rtg_globals.G_VOLUME_BASIS;
2648 END IF;
2649
2650 ELSIF (l_qp_out_det_rec.charge_subtype_code
2651 = fte_rtg_globals.G_C_LOADING_CONTAINER_CH ) THEN
2652
2653 IF (l_qp_out_det_rec.adjustment_amount is not null
2654 AND l_qp_out_det_rec.line_quantity is not null) THEN
2655 -- l_stop_rec.loading_chrg :=
2656 -- l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
2657 -- bug 3474455
2658 l_stop_rec.loading_chrg := l_qp_out_det_rec.adjustment_amount;
2659 l_stop_rec.loading_chrg_basis := fte_rtg_globals.G_CONTAINER_BASIS;
2660 END IF;
2661
2662 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_LOADING_PALLET_CH ) THEN
2663
2664 IF (l_qp_out_det_rec.adjustment_amount is not null
2665 AND l_qp_out_det_rec.line_quantity is not null) THEN
2666 -- l_stop_rec.loading_chrg :=
2667 -- l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
2668 -- bug 3474455
2669 l_stop_rec.loading_chrg := l_qp_out_det_rec.adjustment_amount;
2670 l_stop_rec.loading_chrg_basis := fte_rtg_globals.G_PALLET_BASIS;
2671 END IF;
2672
2673 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_LOADING_FLAT_CH ) THEN
2674
2675 l_stop_rec.loading_chrg := l_qp_out_det_rec.adjustment_amount;
2676 l_stop_rec.loading_chrg_basis := fte_rtg_globals.G_FLAT_BASIS;
2677
2678 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_AST_LOADING_WEIGHT_CH ) THEN
2679
2680 IF (l_qp_out_det_rec.adjustment_amount is not null
2681 AND l_qp_out_det_rec.line_quantity is not null) THEN
2682 -- l_stop_rec.ast_loading_chrg :=
2683 -- l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
2684 -- bug 3474455
2685 l_stop_rec.ast_loading_chrg := l_qp_out_det_rec.adjustment_amount;
2686 l_stop_rec.ast_loading_chrg_basis := fte_rtg_globals.G_WEIGHT_BASIS;
2687 END IF;
2688
2689 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_AST_LOADING_VOLUME_CH ) THEN
2690
2691 IF (l_qp_out_det_rec.adjustment_amount is not null
2692 AND l_qp_out_det_rec.line_quantity is not null) THEN
2693 -- l_stop_rec.ast_loading_chrg :=
2694 -- l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
2695 -- bug 3474455
2696 l_stop_rec.ast_loading_chrg := l_qp_out_det_rec.adjustment_amount;
2697 l_stop_rec.ast_loading_chrg_basis := fte_rtg_globals.G_VOLUME_BASIS;
2698 END IF;
2699
2700 ELSIF (l_qp_out_det_rec.charge_subtype_code
2701 = fte_rtg_globals.G_C_AST_LOADING_CONTAINER_CH ) THEN
2702
2703 IF (l_qp_out_det_rec.adjustment_amount is not null
2704 AND l_qp_out_det_rec.line_quantity is not null) THEN
2705 -- l_stop_rec.ast_loading_chrg :=
2706 -- l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
2707 -- bug 3474455
2708 l_stop_rec.ast_loading_chrg_basis := fte_rtg_globals.G_CONTAINER_BASIS;
2709 l_stop_rec.ast_loading_chrg := l_qp_out_det_rec.adjustment_amount;
2710 END IF;
2711
2712 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_AST_LOADING_PALLET_CH ) THEN
2713
2714 IF (l_qp_out_det_rec.adjustment_amount is not null
2715 AND l_qp_out_det_rec.line_quantity is not null) THEN
2716 -- l_stop_rec.ast_loading_chrg :=
2717 -- l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
2718 -- bug 3474455
2719 l_stop_rec.ast_loading_chrg := l_qp_out_det_rec.adjustment_amount;
2720 l_stop_rec.ast_loading_chrg_basis := fte_rtg_globals.G_PALLET_BASIS;
2721 END IF;
2722
2723 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_AST_LOADING_FLAT_CH ) THEN
2724
2725 l_stop_rec.ast_loading_chrg := l_qp_out_det_rec.adjustment_amount;
2726 l_stop_rec.ast_loading_chrg_basis := fte_rtg_globals.G_FLAT_BASIS;
2727
2728 END IF; -- Loading and Assisted Loading
2729
2730 -- Look for Unloading and Assisted Unloading Charges
2731 IF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_UNLOADING_WEIGHT_CH ) THEN
2732
2733 IF (l_qp_out_det_rec.adjustment_amount is not null
2734 AND l_qp_out_det_rec.line_quantity is not null) THEN
2735 -- l_stop_rec.unloading_chrg :=
2736 -- l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
2737 -- bug 3474455
2738 l_stop_rec.unloading_chrg := l_qp_out_det_rec.adjustment_amount;
2739 l_stop_rec.unloading_chrg_basis := fte_rtg_globals.G_WEIGHT_BASIS;
2740 END IF;
2741
2742 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_UNLOADING_VOLUME_CH ) THEN
2743
2744 IF (l_qp_out_det_rec.adjustment_amount is not null
2745 AND l_qp_out_det_rec.line_quantity is not null) THEN
2746 -- l_stop_rec.unloading_chrg :=
2747 -- l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
2748 -- bug 3474455
2749 l_stop_rec.unloading_chrg := l_qp_out_det_rec.adjustment_amount;
2750 l_stop_rec.unloading_chrg_basis := fte_rtg_globals.G_VOLUME_BASIS;
2751 END IF;
2752
2753 ELSIF (l_qp_out_det_rec.charge_subtype_code
2754 = fte_rtg_globals.G_C_UNLOADING_CONTAINER_CH ) THEN
2755
2756 IF (l_qp_out_det_rec.adjustment_amount is not null
2757 AND l_qp_out_det_rec.line_quantity is not null) THEN
2758 -- l_stop_rec.unloading_chrg :=
2759 -- l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
2760 -- bug 3474455
2761 l_stop_rec.unloading_chrg := l_qp_out_det_rec.adjustment_amount;
2762 l_stop_rec.unloading_chrg_basis := fte_rtg_globals.G_CONTAINER_BASIS;
2763 END IF;
2764
2765 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_UNLOADING_PALLET_CH ) THEN
2766
2767 IF (l_qp_out_det_rec.adjustment_amount is not null
2768 AND l_qp_out_det_rec.line_quantity is not null) THEN
2769 -- l_stop_rec.unloading_chrg :=
2770 -- l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
2771 -- bug 3474455
2772 l_stop_rec.unloading_chrg := l_qp_out_det_rec.adjustment_amount;
2773 l_stop_rec.unloading_chrg_basis := fte_rtg_globals.G_PALLET_BASIS;
2774 END IF;
2775
2776 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_UNLOADING_FLAT_CH ) THEN
2777
2778 l_stop_rec.unloading_chrg := l_qp_out_det_rec.adjustment_amount;
2779 l_stop_rec.unloading_chrg_basis := fte_rtg_globals.G_FLAT_BASIS;
2780
2781 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_AST_UNLOADING_WEIGHT_CH ) THEN
2782
2783 IF (l_qp_out_det_rec.adjustment_amount is not null
2784 AND l_qp_out_det_rec.line_quantity is not null) THEN
2785 -- l_stop_rec.ast_unloading_chrg :=
2786 -- l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
2787 -- bug 3474455
2788 l_stop_rec.ast_unloading_chrg := l_qp_out_det_rec.adjustment_amount;
2789 l_stop_rec.ast_unloading_chrg_basis := fte_rtg_globals.G_WEIGHT_BASIS;
2790 END IF;
2791
2792 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_AST_UNLOADING_VOLUME_CH ) THEN
2793
2794 IF (l_qp_out_det_rec.adjustment_amount is not null
2795 AND l_qp_out_det_rec.line_quantity is not null) THEN
2796 -- l_stop_rec.ast_unloading_chrg :=
2797 -- l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
2798 -- bug 3474455
2799 l_stop_rec.ast_unloading_chrg := l_qp_out_det_rec.adjustment_amount;
2800 l_stop_rec.ast_unloading_chrg_basis := fte_rtg_globals.G_VOLUME_BASIS;
2801 END IF;
2802
2803 ELSIF (l_qp_out_det_rec.charge_subtype_code
2804 = fte_rtg_globals.G_C_AST_UNLOADING_CONTAINER_CH ) THEN
2805
2806 IF (l_qp_out_det_rec.adjustment_amount is not null
2807 AND l_qp_out_det_rec.line_quantity is not null) THEN
2808 -- l_stop_rec.ast_unloading_chrg :=
2809 -- l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
2810 -- bug 3474455
2811 l_stop_rec.ast_unloading_chrg := l_qp_out_det_rec.adjustment_amount;
2812 l_stop_rec.ast_unloading_chrg_basis := fte_rtg_globals.G_CONTAINER_BASIS;
2813 END IF;
2814
2815 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_AST_UNLOADING_PALLET_CH ) THEN
2816
2817 IF (l_qp_out_det_rec.adjustment_amount is not null
2818 AND l_qp_out_det_rec.line_quantity is not null) THEN
2819 -- l_stop_rec.ast_unloading_chrg :=
2820 -- l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
2821 -- bug 3474455
2822 l_stop_rec.ast_unloading_chrg := l_qp_out_det_rec.adjustment_amount;
2823 l_stop_rec.ast_unloading_chrg_basis := fte_rtg_globals.G_PALLET_BASIS;
2824 END IF;
2825
2826 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_AST_UNLOADING_FLAT_CH ) THEN
2827
2828 l_stop_rec.ast_unloading_chrg := l_qp_out_det_rec.adjustment_amount;
2829 l_stop_rec.ast_unloading_chrg_basis := fte_rtg_globals.G_FLAT_BASIS;
2830
2831 END IF; -- Unloading and Assisted Unloading
2832
2833
2834 -- Look for Origin and Destination Surcharges --
2835
2836 IF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_ORIGIN_SURCHRG ) THEN
2837
2838 l_stop_rec.origin_surchrg := l_qp_out_det_rec.adjustment_amount;
2839
2840 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_DESTINATION_SURCHRG ) THEN
2841
2842 l_stop_rec.destination_surchrg := l_qp_out_det_rec.adjustment_amount;
2843
2844 END IF;
2845
2846 -- Look for Weekday and Weekend Layover Charges
2847
2848 IF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_WEEKDAY_LAYOVER_CH ) THEN
2849
2850 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
2851 'G_C_WEEKDAY_LAYOVER_CH : adjustment_amount='||l_qp_out_det_rec.adjustment_amount);
2852 IF (l_qp_out_det_rec.adjustment_amount is not null
2853 AND l_qp_out_det_rec.line_quantity is not null) THEN
2854 -- l_stop_rec.weekday_layover_chrg :=
2855 -- l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
2856 -- bug 3474455
2857 l_stop_rec.weekday_layover_chrg := l_qp_out_det_rec.adjustment_amount;
2858 END IF;
2859
2860 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_WEEKEND_LAYOVER_CH ) THEN
2861
2862 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
2863 'G_C_WEEKEND_LAYOVER_CH : adjustment_amount='||l_qp_out_det_rec.adjustment_amount);
2864
2865 l_num_of_weekend_layover := p_stop_tab(l_stop_index).weekend_layovers;
2866 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
2867 'l_num_of_weekend_layover='||l_num_of_weekend_layover);
2868
2869 l_stop_rec.weekend_layover_chrg :=
2870 l_qp_out_det_rec.adjustment_amount * l_num_of_weekend_layover;
2871
2872 END IF;
2873
2874 -- Assign the l_stop_rec back to the stop output table
2875 --x_stop_charges_tab(l_stop_id) := l_stop_rec ;
2876 x_stop_charges_tab(l_stop_index) := l_stop_rec ;
2877
2878
2879 END IF; -- stop charge line
2880
2881 -------------------- FACILITY STOP CHARGES -------------------
2882
2883 IF (l_req_line_info_rec.line_type = G_FACILITY_CHARGE_LINE ) THEN
2884 -- dig up facility level charges
2885
2886 -- Look for Facility Loading and Assisted Loading Charges --
2887
2888 IF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_F_LOADING_WEIGHT_CH ) THEN
2889
2890 -- l_stop_rec.fac_loading_chrg
2891 -- := l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
2892 -- bug 3474455
2893 l_stop_rec.fac_loading_chrg := l_qp_out_det_rec.adjustment_amount;
2894 l_stop_rec.fac_loading_chrg_basis := fte_rtg_globals.G_WEIGHT_BASIS;
2895
2896 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_F_LOADING_VOLUME_CH ) THEN
2897
2898 -- l_stop_rec.fac_loading_chrg
2899 -- := l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
2900 -- bug 3474455
2901 l_stop_rec.fac_loading_chrg := l_qp_out_det_rec.adjustment_amount;
2902 l_stop_rec.fac_loading_chrg_basis := fte_rtg_globals.G_VOLUME_BASIS;
2903
2904 ELSIF (l_qp_out_det_rec.charge_subtype_code
2905 = fte_rtg_globals.G_F_LOADING_CONTAINER_CH ) THEN
2906
2907 -- l_stop_rec.fac_loading_chrg
2908 -- := l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
2909 -- bug 3474455
2910 l_stop_rec.fac_loading_chrg := l_qp_out_det_rec.adjustment_amount;
2911 l_stop_rec.fac_loading_chrg_basis := fte_rtg_globals.G_CONTAINER_BASIS;
2912
2913 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_F_LOADING_PALLET_CH ) THEN
2914
2915 -- l_stop_rec.fac_loading_chrg
2916 -- := l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
2917 -- bug 3474455
2918 l_stop_rec.fac_loading_chrg := l_qp_out_det_rec.adjustment_amount;
2919 l_stop_rec.fac_loading_chrg_basis := fte_rtg_globals.G_PALLET_BASIS;
2920
2921 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_F_LOADING_FLAT_CH ) THEN
2922
2923 l_stop_rec.fac_loading_chrg := l_qp_out_det_rec.adjustment_amount;
2924 l_stop_rec.fac_loading_chrg_basis := fte_rtg_globals.G_FLAT_BASIS;
2925
2926 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_F_AST_LOADING_WEIGHT_CH ) THEN
2927
2928 -- l_stop_rec.fac_ast_loading_chrg
2929 -- := l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
2930 -- bug 3474455
2931 l_stop_rec.fac_ast_loading_chrg := l_qp_out_det_rec.adjustment_amount;
2932 l_stop_rec.fac_ast_loading_chrg_basis := fte_rtg_globals.G_WEIGHT_BASIS;
2933
2934 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_F_AST_LOADING_VOLUME_CH ) THEN
2935
2936 -- l_stop_rec.fac_ast_loading_chrg
2937 -- := l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
2938 -- bug 3474455
2939 l_stop_rec.fac_ast_loading_chrg := l_qp_out_det_rec.adjustment_amount;
2940 l_stop_rec.fac_ast_loading_chrg_basis := fte_rtg_globals.G_VOLUME_BASIS;
2941
2942 ELSIF (l_qp_out_det_rec.charge_subtype_code
2943 = fte_rtg_globals.G_F_AST_LOADING_CONTAINER_CH ) THEN
2944
2945 -- l_stop_rec.fac_ast_loading_chrg
2946 -- := l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
2947 -- bug 3474455
2948 l_stop_rec.fac_ast_loading_chrg := l_qp_out_det_rec.adjustment_amount;
2949 l_stop_rec.fac_ast_loading_chrg_basis := fte_rtg_globals.G_CONTAINER_BASIS;
2950
2951 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_F_AST_LOADING_PALLET_CH ) THEN
2952
2953 l_stop_rec.fac_ast_loading_chrg
2954 := l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
2955 -- bug 3474455
2956 l_stop_rec.fac_ast_loading_chrg := l_qp_out_det_rec.adjustment_amount;
2957 l_stop_rec.fac_ast_loading_chrg_basis := fte_rtg_globals.G_PALLET_BASIS;
2958
2959 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_F_AST_LOADING_FLAT_CH ) THEN
2960
2961 l_stop_rec.fac_ast_loading_chrg := l_qp_out_det_rec.adjustment_amount;
2962 l_stop_rec.fac_ast_loading_chrg_basis := fte_rtg_globals.G_FLAT_BASIS;
2963
2964 END IF; -- Facility Loading and Assisted Loading
2965
2966 -- Look for Facility Unloading and Assisted Unloading Charges
2967 IF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_F_UNLOADING_WEIGHT_CH ) THEN
2968
2969 -- l_stop_rec.fac_unloading_chrg
2970 -- := l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
2971 -- bug 3474455
2972 l_stop_rec.fac_unloading_chrg := l_qp_out_det_rec.adjustment_amount;
2973 l_stop_rec.fac_unloading_chrg_basis := fte_rtg_globals.G_WEIGHT_BASIS;
2974
2975 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_F_UNLOADING_VOLUME_CH ) THEN
2976
2977 -- l_stop_rec.fac_unloading_chrg
2978 -- := l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
2979 -- bug 3474455
2980 l_stop_rec.fac_unloading_chrg := l_qp_out_det_rec.adjustment_amount;
2981 l_stop_rec.fac_unloading_chrg_basis := fte_rtg_globals.G_VOLUME_BASIS;
2982
2983 ELSIF (l_qp_out_det_rec.charge_subtype_code
2984 = fte_rtg_globals.G_F_UNLOADING_CONTAINER_CH ) THEN
2985
2986 -- l_stop_rec.fac_unloading_chrg
2987 -- := l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
2988 -- bug 3474455
2989 l_stop_rec.fac_unloading_chrg := l_qp_out_det_rec.adjustment_amount;
2990 l_stop_rec.fac_unloading_chrg_basis := fte_rtg_globals.G_CONTAINER_BASIS;
2991
2992 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_F_UNLOADING_PALLET_CH ) THEN
2993
2994 -- l_stop_rec.fac_unloading_chrg
2995 -- := l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
2996 -- bug 3474455
2997 l_stop_rec.fac_unloading_chrg := l_qp_out_det_rec.adjustment_amount;
2998 l_stop_rec.fac_unloading_chrg_basis := fte_rtg_globals.G_PALLET_BASIS;
2999
3000 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_F_UNLOADING_FLAT_CH ) THEN
3001
3002 l_stop_rec.fac_unloading_chrg := l_qp_out_det_rec.adjustment_amount;
3003 l_stop_rec.fac_unloading_chrg_basis := fte_rtg_globals.G_FLAT_BASIS;
3004
3005 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_F_AST_UNLOADING_WEIGHT_CH ) THEN
3006
3007 -- l_stop_rec.fac_ast_unloading_chrg
3008 -- := l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
3009 -- bug 3474455
3010 l_stop_rec.fac_ast_unloading_chrg := l_qp_out_det_rec.adjustment_amount;
3011 l_stop_rec.fac_ast_unloading_chrg_basis := fte_rtg_globals.G_WEIGHT_BASIS;
3012
3013 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_F_AST_UNLOADING_VOLUME_CH ) THEN
3014
3015 -- l_stop_rec.fac_ast_unloading_chrg
3016 -- := l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
3017 -- bug 3474455
3018 l_stop_rec.fac_ast_unloading_chrg := l_qp_out_det_rec.adjustment_amount;
3019 l_stop_rec.fac_ast_unloading_chrg_basis := fte_rtg_globals.G_VOLUME_BASIS;
3020
3021 ELSIF (l_qp_out_det_rec.charge_subtype_code
3022 = fte_rtg_globals.G_F_AST_UNLOADING_CONTAINER_CH ) THEN
3023
3024 -- l_stop_rec.fac_ast_unloading_chrg
3025 -- := l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
3026 -- bug 3474455
3027 l_stop_rec.fac_ast_unloading_chrg := l_qp_out_det_rec.adjustment_amount;
3028 l_stop_rec.fac_ast_unloading_chrg_basis := fte_rtg_globals.G_CONTAINER_BASIS;
3029
3030 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_F_AST_UNLOADING_PALLET_CH ) THEN
3031
3032 -- l_stop_rec.fac_ast_unloading_chrg
3033 -- := l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
3034 -- bug 3474455
3035 l_stop_rec.fac_ast_unloading_chrg := l_qp_out_det_rec.adjustment_amount;
3036 l_stop_rec.fac_ast_unloading_chrg_basis := fte_rtg_globals.G_PALLET_BASIS;
3037
3038 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_F_AST_UNLOADING_FLAT_CH ) THEN
3039
3040 l_stop_rec.fac_ast_unloading_chrg := l_qp_out_det_rec.adjustment_amount;
3041 l_stop_rec.fac_ast_unloading_chrg_basis := fte_rtg_globals.G_FLAT_BASIS;
3042
3043 END IF; -- Facility Unloading and Assisted Unloading
3044
3045 -- Look for Facility Handling Charges --
3046
3047 IF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_F_HANDLING_WEIGHT_CH ) THEN
3048
3049 -- l_stop_rec.fac_handling_chrg
3050 -- := l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
3051 -- bug 3474455
3052 l_stop_rec.fac_handling_chrg := l_qp_out_det_rec.adjustment_amount;
3053 l_stop_rec.fac_handling_chrg_basis := fte_rtg_globals.G_WEIGHT_BASIS;
3054
3055 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_F_HANDLING_VOLUME_CH ) THEN
3056
3057 -- l_stop_rec.fac_handling_chrg
3058 -- := l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
3059 -- bug 3474455
3060 l_stop_rec.fac_handling_chrg := l_qp_out_det_rec.adjustment_amount;
3061 l_stop_rec.fac_handling_chrg_basis := fte_rtg_globals.G_VOLUME_BASIS;
3062
3063 ELSIF (l_qp_out_det_rec.charge_subtype_code
3064 = fte_rtg_globals.G_F_HANDLING_CONTAINER_CH ) THEN
3065
3066 -- l_stop_rec.fac_handling_chrg
3067 -- := l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
3068 -- bug 3474455
3069 l_stop_rec.fac_handling_chrg := l_qp_out_det_rec.adjustment_amount;
3070 l_stop_rec.fac_handling_chrg_basis := fte_rtg_globals.G_CONTAINER_BASIS;
3071
3072 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_F_HANDLING_PALLET_CH ) THEN
3073
3074 -- l_stop_rec.fac_handling_chrg
3075 -- := l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
3076 -- bug 3474455
3077 l_stop_rec.fac_handling_chrg := l_qp_out_det_rec.adjustment_amount;
3078 l_stop_rec.fac_handling_chrg_basis := fte_rtg_globals.G_PALLET_BASIS;
3079
3080 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_F_HANDLING_FLAT_CH ) THEN
3081
3082 l_stop_rec.fac_handling_chrg := l_qp_out_det_rec.adjustment_amount;
3083 l_stop_rec.fac_handling_chrg_basis := fte_rtg_globals.G_FLAT_BASIS;
3084
3085 END IF; -- facility handling charges
3086
3087 -----------------------------------------------------------------
3088
3089 -- Assign the l_stop_rec back to the stop output table
3090 --x_stop_charges_tab(l_stop_id) := l_stop_rec ;
3091 x_stop_charges_tab(l_stop_index) := l_stop_rec ;
3092
3093 END IF; -- facility charge line
3094
3095 EXIT WHEN i = p_qp_output_detail_rows.LAST;
3096 i := p_qp_output_detail_rows.NEXT(i);
3097 END LOOP;
3098 END IF;
3099
3100 fte_freight_pricing_util.unset_method(l_log_level,l_method_name);
3101
3102 EXCEPTION
3103 WHEN FND_API.G_EXC_ERROR THEN
3104 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3105 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_method_name);
3106
3107 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3108 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3109 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_method_name);
3110
3111 WHEN OTHERS THEN
3112 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3113 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_others');
3114 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
3115 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_method_name);
3116
3117 END retrieve_qp_output;
3118
3119 PROCEDURE print_req_line_tab
3120 IS
3121 i NUMBER;
3122 l_log_level NUMBER := fte_freight_pricing_util.G_DBG;
3123 l_method_name VARCHAR2(50) := 'print_req_line_tab';
3124 BEGIN
3125 fte_freight_pricing_util.reset_dbg_vars;
3126 fte_freight_pricing_util.set_method(l_log_level,l_method_name);
3127
3128 i := g_req_line_info_tab.FIRST;
3129 IF ( g_req_line_info_tab.COUNT >0) THEN
3130 LOOP
3131 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3132 '------------------- g_req_line_info_tab-----------------------');
3133 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3134 'line_index :'||g_req_line_info_tab(i).line_index);
3135 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3136 'line_type :'||g_req_line_info_tab(i).line_type);
3137 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3138 'trip_index :'||g_req_line_info_tab(i).trip_index);
3139 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3140 'stop_index :'||g_req_line_info_tab(i).stop_index);
3141 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3142 'line_qty :'||g_req_line_info_tab(i).line_qty);
3143 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3144 'line_uom :'||g_req_line_info_tab(i).line_uom);
3145 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3146 'currency :'||g_req_line_info_tab(i).currency);
3147 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3148 'lane_id :'||g_req_line_info_tab(i).lane_id);
3149 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3150 'pricelist_id :'||g_req_line_info_tab(i).pricelist_id);
3151 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3152 'carrier_id :'||g_req_line_info_tab(i).carrier_id);
3153 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3154 '----------------------------------------------------------------');
3155 EXIT WHEN (i >= g_req_line_info_tab.LAST );
3156 i := g_req_line_info_tab.NEXT(i);
3157 END LOOP;
3158 END IF;
3159
3160 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_method_name);
3161 EXCEPTION
3162 WHEN OTHERS THEN
3163 null;
3164 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_method_name);
3165 END print_req_line_tab;
3166
3167 PROCEDURE print_output(
3168 p_trip_charges_rec IN FTE_TL_CACHE.TL_trip_output_rec_type,
3169 p_stop_charges_tab IN FTE_TL_CACHE.TL_trip_stop_output_tab_type
3170 ) IS
3171 l_log_level NUMBER := fte_freight_pricing_util.G_DBG;
3172 l_method_name VARCHAR2(50) := 'print_output';
3173 i NUMBER;
3174 BEGIN
3175 fte_freight_pricing_util.reset_dbg_vars;
3176 fte_freight_pricing_util.set_method(l_log_level,l_method_name);
3177
3178 -- print trip rec
3179 -- print stop tab
3180 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3181 '----------------------- Trip Output----------------------------');
3182 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3183 'trip_id :'||p_trip_charges_rec.trip_id );
3184 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3185 'base_dist_load_chrg :'||p_trip_charges_rec.base_dist_load_chrg );
3186 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3187 'base_dist_load_unit_chrg :'||p_trip_charges_rec.base_dist_load_unit_chrg );
3188 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3189 'base_dist_unload_chrg :'||p_trip_charges_rec.base_dist_unload_chrg );
3190 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3191 'base_dist_unload_unit_chrg :'||p_trip_charges_rec.base_dist_unload_unit_chrg );
3192 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3193 'base_unit_chrg :'||p_trip_charges_rec.base_unit_chrg );
3194 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3195 'base_unit_unit_chrg :'||p_trip_charges_rec.base_unit_unit_chrg );
3196 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3197 'base_time_chrg :'||p_trip_charges_rec.base_time_chrg );
3198 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3199 'base_time_unit_chrg :'||p_trip_charges_rec.base_time_unit_chrg );
3200 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3201 'base_flat_chrg :'||p_trip_charges_rec.base_flat_chrg );
3202 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3203 'stop_off_chrg :'||p_trip_charges_rec.stop_off_chrg );
3204 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3205 'out_of_route_chrg :'||p_trip_charges_rec.out_of_route_chrg );
3206 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3207 'document_chrg :'||p_trip_charges_rec.document_chrg );
3208 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3209 'handling_chrg :'||p_trip_charges_rec.handling_chrg );
3210 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3211 'handling_chrg_basis :'||p_trip_charges_rec.handling_chrg_basis );
3212 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3213 'cm_discount_percent :'||p_trip_charges_rec.cm_discount_percent );
3214 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3215 'cm_discount_value :'||p_trip_charges_rec.cm_discount_value );
3216 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3217 'currency :'||p_trip_charges_rec.currency );
3218 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3219 'fuel_chrg :'||p_trip_charges_rec.fuel_chrg );
3220 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3221 '----------------------------------------------------------------');
3222
3223
3224 i := p_stop_charges_tab.FIRST;
3225 IF ( p_stop_charges_tab.COUNT >0) THEN
3226 LOOP
3227
3228 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3229 '----------------------- Stop Output----------------------------');
3230 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3231 'stop_id :'||p_stop_charges_tab(i).stop_id);
3232 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3233 'trip_id :'||p_stop_charges_tab(i).trip_id);
3234 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3235 'weekday_layover_chrg :'||p_stop_charges_tab(i).weekday_layover_chrg);
3236 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3237 'weekend_layover_chrg :'||p_stop_charges_tab(i).weekend_layover_chrg);
3238 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3239 'loading_chrg :'||p_stop_charges_tab(i).loading_chrg);
3240 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3241 'loading_chrg_basis :'||p_stop_charges_tab(i).loading_chrg_basis);
3242 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3243 'ast_loading_chrg :'||p_stop_charges_tab(i).ast_loading_chrg);
3244 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3245 'ast_loading_chrg_basis :'||p_stop_charges_tab(i).ast_loading_chrg_basis);
3246 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3247 'unloading_chrg :'||p_stop_charges_tab(i).unloading_chrg);
3248 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3249 'unloading_chrg_basis :'||p_stop_charges_tab(i).unloading_chrg_basis);
3250 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3251 'ast_unloading_chrg :'||p_stop_charges_tab(i).ast_unloading_chrg);
3252 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3253 'ast_unloading_chrg_basis :'||p_stop_charges_tab(i).ast_unloading_chrg_basis);
3254 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3255 'origin_surchrg :'||p_stop_charges_tab(i).origin_surchrg);
3256 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3257 'destination_surchrg :'||p_stop_charges_tab(i).destination_surchrg);
3258 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3259 'fac_loading_chrg :'||p_stop_charges_tab(i).fac_loading_chrg);
3260 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3261 'fac_loading_chrg_basis :'||p_stop_charges_tab(i).fac_loading_chrg_basis);
3262 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3263 'fac_ast_loading_chrg :'||p_stop_charges_tab(i).fac_ast_loading_chrg);
3264 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3265 'fac_ast_loading_chrg_basis :'||p_stop_charges_tab(i).fac_ast_loading_chrg_basis);
3266 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3267 'fac_unloading_chrg :'||p_stop_charges_tab(i).fac_unloading_chrg);
3268 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3269 'fac_unloading_chrg_basis :'||p_stop_charges_tab(i).fac_unloading_chrg_basis);
3270 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3271 'fac_ast_unloading_chrg :'||p_stop_charges_tab(i).fac_ast_unloading_chrg);
3272 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3273 'fac_ast_unloading_chrg_basis :'||p_stop_charges_tab(i).fac_ast_unloading_chrg_basis);
3274 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3275 'fac_handling_chrg :'||p_stop_charges_tab(i).fac_handling_chrg);
3276 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3277 'fac_handling_chrg_basis :'||p_stop_charges_tab(i).fac_handling_chrg_basis);
3278 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3279 'fac_currency :'||p_stop_charges_tab(i).fac_currency);
3280 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3281 '----------------------------------------------------------------');
3282
3283 EXIT WHEN (i >= p_stop_charges_tab.LAST );
3284 i := p_stop_charges_tab.NEXT(i);
3285 END LOOP;
3286 END IF;
3287
3288 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_method_name);
3289 END print_output;
3290
3291
3292
3293 PROCEDURE print_output_multiple(
3294 p_start_trip_index IN NUMBER,
3295 p_end_trip_index IN NUMBER,
3296 p_trip_charges_tab IN FTE_TL_CACHE.TL_TRIP_OUTPUT_TAB_TYPE,
3297 p_stop_charges_tab IN FTE_TL_CACHE.TL_trip_stop_output_tab_type
3298 ) IS
3299 l_log_level NUMBER := fte_freight_pricing_util.G_DBG;
3300 l_method_name VARCHAR2(50) := 'print_output_multiple';
3301 i NUMBER;
3302 j NUMBER;
3303 BEGIN
3304 fte_freight_pricing_util.reset_dbg_vars;
3305 fte_freight_pricing_util.set_method(l_log_level,l_method_name);
3306
3307
3308
3309 i := p_start_trip_index;
3310 WHILE(i <= p_end_trip_index)
3311 LOOP
3312
3313 -- print_output(p_trip_charges_rec=>p_trip_charges_tab(i),
3314 -- p_stop_charges_tab=>p_stop_charges_tab);
3315
3316
3317 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3318 '----------------------- Trip Output----------------------------');
3319 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3320 'trip_id :'||p_trip_charges_tab(i).trip_id );
3321 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3322 'base_dist_load_chrg :'||p_trip_charges_tab(i).base_dist_load_chrg );
3323 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3324 'base_dist_load_unit_chrg :'||p_trip_charges_tab(i).base_dist_load_unit_chrg );
3325 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3326 'base_dist_unload_chrg :'||p_trip_charges_tab(i).base_dist_unload_chrg );
3327 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3328 'base_dist_unload_unit_chrg :'||p_trip_charges_tab(i).base_dist_unload_unit_chrg );
3329 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3330 'base_unit_chrg :'||p_trip_charges_tab(i).base_unit_chrg );
3331 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3332 'base_unit_unit_chrg :'||p_trip_charges_tab(i).base_unit_unit_chrg );
3333 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3334 'base_time_chrg :'||p_trip_charges_tab(i).base_time_chrg );
3335 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3336 'base_time_unit_chrg :'||p_trip_charges_tab(i).base_time_unit_chrg );
3337 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3338 'base_flat_chrg :'||p_trip_charges_tab(i).base_flat_chrg );
3339 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3340 'stop_off_chrg :'||p_trip_charges_tab(i).stop_off_chrg );
3341 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3342 'out_of_route_chrg :'||p_trip_charges_tab(i).out_of_route_chrg );
3343 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3344 'document_chrg :'||p_trip_charges_tab(i).document_chrg );
3345 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3346 'handling_chrg :'||p_trip_charges_tab(i).handling_chrg );
3347 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3348 'handling_chrg_basis :'||p_trip_charges_tab(i).handling_chrg_basis );
3349 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3350 'cm_discount_percent :'||p_trip_charges_tab(i).cm_discount_percent );
3351 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3352 'cm_discount_value :'||p_trip_charges_tab(i).cm_discount_value );
3353 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3354 'currency :'||p_trip_charges_tab(i).currency );
3355 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3356 'fuel_chrg :'||p_trip_charges_tab(i).fuel_chrg );
3357 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3358 '----------------------------------------------------------------');
3359
3360
3361 j := p_trip_charges_tab(i).stop_charge_reference;
3362 WHILE((FTE_TL_CACHE.g_tl_trip_rows(i).number_of_stops > 0) AND
3363 (j<(FTE_TL_CACHE.g_tl_trip_rows(i).number_of_stops+p_trip_charges_tab(i).stop_charge_reference)))
3364 LOOP
3365
3366 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3367 '----------------------- Stop Output----------------------------');
3368 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3369 'stop_id :'||p_stop_charges_tab(j).stop_id);
3370 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3371 'trip_id :'||p_stop_charges_tab(j).trip_id);
3372 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3373 'weekday_layover_chrg :'||p_stop_charges_tab(j).weekday_layover_chrg);
3374 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3375 'weekend_layover_chrg :'||p_stop_charges_tab(j).weekend_layover_chrg);
3376 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3377 'loading_chrg :'||p_stop_charges_tab(j).loading_chrg);
3378 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3379 'loading_chrg_basis :'||p_stop_charges_tab(j).loading_chrg_basis);
3380 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3381 'ast_loading_chrg :'||p_stop_charges_tab(j).ast_loading_chrg);
3382 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3383 'ast_loading_chrg_basis :'||p_stop_charges_tab(j).ast_loading_chrg_basis);
3384 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3385 'unloading_chrg :'||p_stop_charges_tab(j).unloading_chrg);
3386 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3387 'unloading_chrg_basis :'||p_stop_charges_tab(j).unloading_chrg_basis);
3388 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3389 'ast_unloading_chrg :'||p_stop_charges_tab(j).ast_unloading_chrg);
3390 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3391 'ast_unloading_chrg_basis :'||p_stop_charges_tab(j).ast_unloading_chrg_basis);
3392 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3393 'origin_surchrg :'||p_stop_charges_tab(j).origin_surchrg);
3394 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3395 'destination_surchrg :'||p_stop_charges_tab(j).destination_surchrg);
3396 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3397 'fac_loading_chrg :'||p_stop_charges_tab(j).fac_loading_chrg);
3398 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3399 'fac_loading_chrg_basis :'||p_stop_charges_tab(j).fac_loading_chrg_basis);
3400 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3401 'fac_ast_loading_chrg :'||p_stop_charges_tab(j).fac_ast_loading_chrg);
3402 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3403 'fac_ast_loading_chrg_basis :'||p_stop_charges_tab(j).fac_ast_loading_chrg_basis);
3404 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3405 'fac_unloading_chrg :'||p_stop_charges_tab(j).fac_unloading_chrg);
3406 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3407 'fac_unloading_chrg_basis :'||p_stop_charges_tab(j).fac_unloading_chrg_basis);
3408 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3409 'fac_ast_unloading_chrg :'||p_stop_charges_tab(j).fac_ast_unloading_chrg);
3410 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3411 'fac_ast_unloading_chrg_basis :'||p_stop_charges_tab(j).fac_ast_unloading_chrg_basis);
3412 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3413 'fac_handling_chrg :'||p_stop_charges_tab(j).fac_handling_chrg);
3414 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3415 'fac_handling_chrg_basis :'||p_stop_charges_tab(j).fac_handling_chrg_basis);
3416 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3417 'fac_currency :'||p_stop_charges_tab(j).fac_currency);
3418 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3419 '----------------------------------------------------------------');
3420
3421
3422 j := p_stop_charges_tab.NEXT(j);
3423 END LOOP;
3424
3425
3426
3427
3428
3429 i:=i+1;
3430 END LOOP;
3431 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_method_name);
3432 END print_output_multiple;
3433
3434
3435
3436
3437 PROCEDURE api_post_call
3438 (
3439 p_api_name IN VARCHAR2,
3440 p_api_return_status IN VARCHAR2,
3441 p_message_name IN VARCHAR2,
3442 p_trip_id IN VARCHAR2 DEFAULT NULL,
3443 p_delivery_id IN VARCHAR2 DEFAULT NULL,
3444 p_delivery_leg_id IN VARCHAR2 DEFAULT NULL,
3445 x_number_of_errors IN OUT NOCOPY NUMBER,
3446 x_number_of_warnings IN OUT NOCOPY NUMBER,
3447 x_return_status OUT NOCOPY VARCHAR2
3448 )
3449 IS
3450 BEGIN
3451 x_return_status := p_api_return_status; -- default
3452 IF p_api_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
3453 THEN
3454 IF p_api_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING
3455 THEN
3456 x_number_of_warnings := x_number_of_warnings + 1;
3457 ELSE
3458 IF p_api_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
3459 OR p_api_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR
3460 THEN
3461 FND_MESSAGE.SET_NAME('FTE', p_message_name );
3462 FND_MESSAGE.SET_TOKEN('PROGRAM_UNIT_NAME', p_api_name);
3463 IF p_trip_id IS NOT NULL
3464 THEN
3465 FND_MESSAGE.SET_TOKEN('TRIP_ID', p_trip_id);
3466 END IF;
3467 IF p_delivery_id IS NOT NULL
3468 THEN
3469 FND_MESSAGE.SET_TOKEN('DELIVERY_ID', p_delivery_id);
3470 END IF;
3471 IF p_delivery_leg_id IS NOT NULL
3472 THEN
3473 FND_MESSAGE.SET_TOKEN('DELIVERY_LEG_ID', p_delivery_leg_id);
3474 END IF;
3475 IF (p_api_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
3476 THEN
3477 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
3478 x_return_status := p_api_return_status;
3479 ELSE
3480 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR);
3481 x_return_status := p_api_return_status;
3482 END IF;
3483 RETURN;
3484 ELSE
3485 x_number_of_errors := x_number_of_errors + 1;
3486 END IF;
3487
3488 END IF;
3489 END IF;
3490
3491 EXCEPTION
3492 WHEN OTHERS THEN
3493 wsh_util_core.default_handler(G_PKG_NAME||'.API_POST_CALL');
3494 WSH_UTIL_CORE.ADD_MESSAGE(WSH_UTIL_CORE.G_RET_STS_ERROR);
3495 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3496 END api_post_call;
3497
3498
3499
3500 PROCEDURE retrieve_qp_output_multiple (
3501 p_start_trip_index IN NUMBER,
3502 p_end_trip_index IN NUMBER,
3503 p_trip_tab IN FTE_TL_CACHE.TL_trip_data_input_tab_type,
3504 p_stop_tab IN FTE_TL_CACHE.TL_trip_stop_input_tab_type,
3505 p_carrier_pref_tab IN FTE_TL_CACHE.TL_CARRIER_PREF_TAB_TYPE,
3506 p_qp_output_line_rows IN QP_PREQ_GRP.LINE_TBL_TYPE,
3507 p_qp_output_detail_rows IN QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE,
3508 x_trip_charges_tab OUT NOCOPY FTE_TL_CACHE.TL_TRIP_OUTPUT_TAB_TYPE,
3509 x_stop_charges_tab OUT NOCOPY FTE_TL_CACHE.TL_trip_stop_output_tab_type,
3510 x_return_status OUT NOCOPY VARCHAR2)
3511
3512 IS
3513 l_return_status VARCHAR2(1);
3514 i NUMBER := 0;
3515 line_idx NUMBER := 0;
3516
3517 l_num_of_weekend_layover NUMBER;
3518 l_stop_index NUMBER := 0;
3519
3520
3521
3522 l_loaded_dist_price_tab DBMS_UTILITY.NUMBER_ARRAY;
3523 l_unit_loaded_dist_price_tab DBMS_UTILITY.NUMBER_ARRAY;
3524 l_unloaded_dist_price_tab DBMS_UTILITY.NUMBER_ARRAY;
3525 l_unit_unloaded_dist_price_tab DBMS_UTILITY.NUMBER_ARRAY;
3526 l_cm_dist_price_tab DBMS_UTILITY.NUMBER_ARRAY;
3527 l_unit_cm_dist_price_tab DBMS_UTILITY.NUMBER_ARRAY;
3528 l_unit_base_price_tab DBMS_UTILITY.NUMBER_ARRAY;
3529 l_unit_unit_base_price_tab DBMS_UTILITY.NUMBER_ARRAY;
3530 l_time_price_tab DBMS_UTILITY.NUMBER_ARRAY;
3531 l_unit_time_price_tab DBMS_UTILITY.NUMBER_ARRAY;
3532 l_flat_price_tab DBMS_UTILITY.NUMBER_ARRAY;
3533
3534
3535 j NUMBER;
3536 l_trip_index NUMBER;
3537 l_qp_out_det_rec QP_PREQ_GRP.LINE_DETAIL_REC_TYPE;
3538 l_trip_rec FTE_TL_CACHE.TL_trip_output_rec_type;
3539 l_stop_rec FTE_TL_CACHE.TL_trip_stop_output_rec_type;
3540 l_stop_id NUMBER;
3541 l_req_line_info_rec req_line_info_rec_type;
3542 l_log_level NUMBER := fte_freight_pricing_util.G_DBG;
3543 l_method_name VARCHAR2(50) := 'retrieve_qp_output_multiple';
3544
3545 BEGIN
3546 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3547 fte_freight_pricing_util.reset_dbg_vars;
3548 fte_freight_pricing_util.set_method(l_log_level,l_method_name);
3549
3550
3551 --Initialize to 0
3552 i:=p_start_trip_index;
3553 WHILE (i<=p_end_trip_index)
3554 LOOP
3555 l_loaded_dist_price_tab(i):=0;
3556 l_unit_loaded_dist_price_tab(i):=0;
3557 l_unloaded_dist_price_tab(i):=0;
3558 l_unit_unloaded_dist_price_tab(i):=0;
3559 l_cm_dist_price_tab(i):=0;
3560 l_unit_cm_dist_price_tab(i):=0;
3561 l_unit_base_price_tab(i):=0;
3562 l_unit_unit_base_price_tab(i):=0;
3563 l_time_price_tab(i):=0;
3564 l_unit_time_price_tab(i):=0;
3565 l_flat_price_tab(i):=0;
3566
3567
3568 x_trip_charges_tab(i):=l_trip_rec;
3569 x_trip_charges_tab(i).trip_id:=p_trip_tab(i).trip_id;
3570 x_trip_charges_tab(i).currency:=p_carrier_pref_tab(i).currency;
3571 x_trip_charges_tab(i).stop_charge_reference:=p_trip_tab(i).stop_reference;
3572
3573 i:=i+1;
3574 END LOOP;
3575
3576
3577 -- Loop through the line table
3578 -- look for base rates returned (incl. continuous move rates if applicable)
3579 -- Loop through the detail table
3580 -- look for minimum charges on base prices (how will we process these?)
3581 -- look for accessorial charges
3582 -- look for continuous move discount **
3583 -- Apply minimum charges to base prices ?
3584 -- Apply continuous move discount if applicable (or will cost allocation deal with it?)
3585 -- uom conversion?
3586
3587 ------------- BASE PRICES -----------------------------------
3588
3589 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3590 'g_req_line_info_tab.COUNT = '||g_req_line_info_tab.COUNT);
3591 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3592 'p_qp_output_line_rows.COUNT = '||p_qp_output_line_rows.COUNT);
3593
3594 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3595 'Now looping on p_qp_output_line_rows ');
3596
3597 i := p_qp_output_line_rows.FIRST;
3598 IF (i IS NOT NULL) THEN
3599 LOOP
3600 -- get line index
3601 -- get req info for line index
3602 -- get base rate (if applicable)
3603 -- loop thru line details
3604 -- depending upon the type (purpose) of the line, filter out unwanted details
3605 -- analyze the charge sub type code, and extract the charge.
3606
3607 line_idx := p_qp_output_line_rows(i).line_index;
3608 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3609 'line_idx = '||line_idx);
3610
3611 l_trip_index:=g_req_line_info_tab(line_idx).trip_index;
3612 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3613 'trip_index = '||l_trip_index);
3614
3615
3616
3617 --
3618 -- NOTE : We use adjusted_unit_price instead on unit_price on most base rates
3619 -- to account for minimum charges that get applied. If there are no
3620 -- minimum charges, then unit_price will be equal to adjusted_unit_price.
3621 -- This is because currently we don't have any other modifiers that
3622 -- apply on these lines.
3623
3624 IF ( g_req_line_info_tab(line_idx).line_type = G_LOADED_DIST_BASE_LINE ) THEN
3625 -- get loaded distance base rate
3626 IF (p_qp_output_line_rows(i).adjusted_unit_price IS NOT NULL) THEN
3627 l_loaded_dist_price_tab(l_trip_index) := p_qp_output_line_rows(i).line_quantity
3628 * p_qp_output_line_rows(i).adjusted_unit_price;
3629 l_unit_loaded_dist_price_tab(l_trip_index) := p_qp_output_line_rows(i).adjusted_unit_price;
3630 END IF;
3631 END IF;
3632
3633 IF ( g_req_line_info_tab(line_idx).line_type = G_UNLOADED_DIST_BASE_LINE ) THEN
3634 -- get unloaded distance base rate
3635 IF (p_qp_output_line_rows(i).adjusted_unit_price IS NOT NULL) THEN
3636 l_unloaded_dist_price_tab(l_trip_index) := p_qp_output_line_rows(i).line_quantity
3637 * p_qp_output_line_rows(i).adjusted_unit_price;
3638 l_unit_unloaded_dist_price_tab(l_trip_index) := p_qp_output_line_rows(i).adjusted_unit_price;
3639 END IF;
3640 END IF;
3641
3642
3643 IF ( g_req_line_info_tab(line_idx).line_type = G_CONT_DIST_BASE_LINE ) THEN
3644 -- get continuous distance base rate (or cm deadhead rate)
3645 IF (p_qp_output_line_rows(i).adjusted_unit_price IS NOT NULL) THEN
3646 l_cm_dist_price_tab(l_trip_index) := p_qp_output_line_rows(i).line_quantity
3647 * p_qp_output_line_rows(i).adjusted_unit_price;
3648 l_unit_cm_dist_price_tab(l_trip_index) := p_qp_output_line_rows(i).adjusted_unit_price;
3649 END IF;
3650 END IF;
3651
3652 IF ( g_req_line_info_tab(line_idx).line_type = G_CONT_DH_BASE_LINE ) THEN
3653 -- get continuous distance base rate (or cm deadhead rate)
3654 IF (p_qp_output_line_rows(i).adjusted_unit_price IS NOT NULL) THEN
3655 l_unloaded_dist_price_tab(l_trip_index) := p_qp_output_line_rows(i).line_quantity
3656 * p_qp_output_line_rows(i).adjusted_unit_price;
3657 l_unit_unloaded_dist_price_tab(l_trip_index) := p_qp_output_line_rows(i).adjusted_unit_price;
3658 END IF;
3659 END IF;
3660
3661 IF ( g_req_line_info_tab(line_idx).line_type = G_UNITS_BASE_LINE ) THEN
3662 -- get unit base rate
3663 IF (p_qp_output_line_rows(i).adjusted_unit_price IS NOT NULL) THEN
3664 l_unit_base_price_tab(l_trip_index) := p_qp_output_line_rows(i).line_quantity
3665 * p_qp_output_line_rows(i).adjusted_unit_price;
3666 l_unit_unit_base_price_tab(l_trip_index) := p_qp_output_line_rows(i).adjusted_unit_price;
3667
3668
3669 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3670 'trip_index = '||l_trip_index||'base price '||l_unit_base_price_tab(l_trip_index)||
3671 ' lin_idx:'||line_idx||' output line idx:'||i||' Quantity:'||p_qp_output_line_rows(i).line_quantity||' Adj unit price'||p_qp_output_line_rows(i).adjusted_unit_price );
3672
3673 END IF;
3674 END IF;
3675
3676 IF ( g_req_line_info_tab(line_idx).line_type = G_TIME_BASE_LINE ) THEN
3677 -- get time base rate
3678 IF (p_qp_output_line_rows(i).adjusted_unit_price IS NOT NULL) THEN
3679 l_time_price_tab(l_trip_index) := p_qp_output_line_rows(i).line_quantity
3680 * p_qp_output_line_rows(i).adjusted_unit_price;
3681 l_unit_time_price_tab(l_trip_index) := p_qp_output_line_rows(i).adjusted_unit_price;
3682 END IF;
3683 END IF;
3684
3685 IF ( g_req_line_info_tab(line_idx).line_type = G_FLAT_BASE_LINE ) THEN
3686 -- get loaded distance base rate
3687 IF (p_qp_output_line_rows(i).unit_price IS NOT NULL) THEN
3688 l_flat_price_tab(l_trip_index) := p_qp_output_line_rows(i).line_quantity
3689 * p_qp_output_line_rows(i).unit_price;
3690 END IF;
3691 END IF;
3692
3693 EXIT WHEN i = p_qp_output_line_rows.LAST;
3694 i := p_qp_output_line_rows.NEXT(i);
3695 END LOOP;
3696 END IF;
3697
3698 -- Generate base price output --
3699
3700
3701 i:=p_start_trip_index;
3702 WHILE (i<=p_end_trip_index)
3703 LOOP
3704
3705 -- Assumption : both loaded/unloaded and cm dist price cannot be non-zero at the same time.
3706 -- Does not include continuous move discount
3707 -- x_trip_charges_rec.base_distance_chrg := l_loaded_dist_price_tab(i) + l_unloaded_dist_price_tab(i) + l_cm_dist_price_tab(i) ;
3708 IF (l_cm_dist_price_tab(i) <> 0) THEN
3709 x_trip_charges_tab(i).base_dist_load_chrg := l_cm_dist_price_tab(i);
3710 x_trip_charges_tab(i).base_dist_load_unit_chrg := l_unit_cm_dist_price_tab(i);
3711 ELSE
3712 x_trip_charges_tab(i).base_dist_load_chrg := l_loaded_dist_price_tab(i);
3713 x_trip_charges_tab(i).base_dist_load_unit_chrg := l_unit_loaded_dist_price_tab(i);
3714 END IF;
3715 x_trip_charges_tab(i).base_dist_unload_chrg := l_unloaded_dist_price_tab(i);
3716 x_trip_charges_tab(i).base_dist_unload_unit_chrg := l_unit_unloaded_dist_price_tab(i);
3717
3718 x_trip_charges_tab(i).base_unit_chrg := l_unit_base_price_tab(i);
3719 x_trip_charges_tab(i).base_unit_unit_chrg := l_unit_unit_base_price_tab(i);
3720
3721 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3722 'trip unit charge trip_index = '||i||' charge:'||x_trip_charges_tab(i).base_unit_chrg );
3723
3724
3725 x_trip_charges_tab(i).base_time_chrg := l_time_price_tab(i);
3726 x_trip_charges_tab(i).base_time_unit_chrg := l_unit_time_price_tab(i);
3727 x_trip_charges_tab(i).base_flat_chrg := l_flat_price_tab(i);
3728
3729 -- init the load charge
3730 x_trip_charges_tab(i).out_of_route_chrg := 0;
3731 x_trip_charges_tab(i).stop_off_chrg := 0;
3732 x_trip_charges_tab(i).document_chrg := 0;
3733 x_trip_charges_tab(i).handling_chrg := 0;
3734 x_trip_charges_tab(i).cm_discount_percent := 0;
3735 x_trip_charges_tab(i).fuel_chrg := 0;
3736
3737
3738 i:=i+1;
3739 END LOOP;
3740
3741 -- Generate dummy entries into x_stop_charges_tab --
3742 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3743 'Added dummy records to x_stop_charges_rec');
3744
3745 j:=p_start_trip_index;
3746 WHILE (j<=p_end_trip_index)
3747 LOOP
3748
3749
3750 IF (p_trip_tab(j).number_of_stops > 0)
3751 THEN
3752 l_stop_index := p_trip_tab(j).stop_reference;
3753 WHILE(l_stop_index <= p_trip_tab(j).number_of_stops+p_trip_tab(j).stop_reference)
3754 LOOP
3755 init_stop_rec (x_stop_rec => l_stop_rec);
3756
3757 l_stop_rec.stop_id := p_stop_tab(l_stop_index).stop_id;
3758 l_stop_rec.trip_id := p_stop_tab(l_stop_index).trip_id;
3759 l_stop_rec.fac_currency := p_stop_tab(l_stop_index).fac_currency;
3760 x_stop_charges_tab(l_stop_index) := l_stop_rec;
3761 l_stop_index := p_stop_tab.NEXT(l_stop_index);
3762 END LOOP;
3763 END IF;
3764
3765
3766 j:=j+1;
3767 END LOOP;
3768
3769
3770 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3771 'x_stop_charges_tab.COUNT='||x_stop_charges_tab.COUNT);
3772
3773
3774 ----------- ACCESSORIAL CHARGES,DISCOUNTS AND BASE MIN CHARGES -----------------------
3775
3776 -- Query line details --
3777
3778 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3779 'Now looping on p_qp_output_detail_rows ');
3780
3781 i := p_qp_output_detail_rows.FIRST;
3782 IF (i IS NOT NULL) THEN
3783 LOOP
3784
3785 -- get load level charges
3786 -- get continuous move discount (if applicable)
3787 -- get stop level charges (for each stop)
3788 -- get facility charges (for each stop)
3789
3790 l_qp_out_det_rec := p_qp_output_detail_rows(i);
3791 l_req_line_info_rec := g_req_line_info_tab(l_qp_out_det_rec.line_index);
3792
3793 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3794 'i = '||i);
3795 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3796 'l_qp_out_det_rec.line_index='||l_qp_out_det_rec.line_index);
3797
3798 l_trip_index:=l_req_line_info_rec.trip_index;
3799
3800 -------------------- MIN BASE CHARGES -------------------
3801
3802
3803
3804
3805 IF (l_req_line_info_rec.line_type = G_LOADED_DIST_BASE_LINE ) THEN
3806
3807 -- Min charges for distance charges is across all dist types
3808 -- It is assumed that both continuous move line and loaded dist line
3809 -- do not exist in the same call
3810
3811 IF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_MIN_DISTANCE_CH
3812 AND nvl(l_qp_out_det_rec.adjustment_amount,0) >0 ) THEN
3813 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3814 'MIN_CHARGE: Minimum charge applied to distance base rates');
3815 END IF;
3816
3817 END IF;
3818
3819 IF (l_req_line_info_rec.line_type = G_CONT_DIST_BASE_LINE
3820 AND l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_MIN_DISTANCE_CH
3821 AND nvl(l_qp_out_det_rec.adjustment_amount,0) >0 ) THEN
3822
3823 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3824 'MIN_CHARGE: Minimum charge applied to distance base rates (continuous move)');
3825
3826 END IF;
3827
3828 IF (l_req_line_info_rec.line_type = G_UNITS_BASE_LINE
3829 AND l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_MIN_UNIT_CH
3830 AND nvl(l_qp_out_det_rec.adjustment_amount,0) > 0) THEN
3831
3832 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3833 'MIN_CHARGE: Minimum charge applied to units base rates ');
3834
3835 END IF;
3836
3837 IF (l_req_line_info_rec.line_type = G_TIME_BASE_LINE
3838 AND l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_MIN_TIME_CH
3839 AND nvl(l_qp_out_det_rec.adjustment_amount,0) > 0) THEN
3840
3841 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3842 'MIN_CHARGE: Minimum charge applied to time base rates ');
3843
3844 END IF;
3845
3846 -------------------- LOAD (TRIP) CHARGES -------------------
3847
3848 IF (l_req_line_info_rec.line_type = G_LOAD_CHARGE_LINE ) THEN
3849
3850 -- dig up load (trip) level charges
3851 IF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_STOP_OFF_CH) THEN
3852 x_trip_charges_tab(l_trip_index).stop_off_chrg := l_qp_out_det_rec.adjustment_amount;
3853 END IF;
3854
3855 IF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_OUT_OF_ROUTE_CH) THEN
3856 IF (l_qp_out_det_rec.adjustment_amount is not null
3857 AND l_qp_out_det_rec.line_quantity is not null) THEN
3858 -- x_trip_charges_rec.out_of_route_chrg :=
3859 -- l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
3860 -- bug 3474455
3861 x_trip_charges_tab(l_trip_index).out_of_route_chrg := l_qp_out_det_rec.adjustment_amount;
3862 END IF;
3863 END IF;
3864
3865 IF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_DOCUMENT_CH) THEN
3866 x_trip_charges_tab(l_trip_index).document_chrg := l_qp_out_det_rec.adjustment_amount;
3867 END IF;
3868
3869 IF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_HANDLING_WEIGHT_CH) THEN
3870 IF (l_qp_out_det_rec.adjustment_amount is not null
3871 AND l_qp_out_det_rec.line_quantity is not null) THEN
3872 -- x_trip_charges_rec.handling_chrg :=
3873 -- l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
3874 -- bug 3474455
3875 x_trip_charges_tab(l_trip_index).handling_chrg := l_qp_out_det_rec.adjustment_amount;
3876 x_trip_charges_tab(l_trip_index).handling_chrg_basis := fte_rtg_globals.G_WEIGHT_BASIS;
3877 END IF;
3878 END IF;
3879
3880 IF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_HANDLING_VOLUME_CH) THEN
3881 IF (l_qp_out_det_rec.adjustment_amount is not null
3882 AND l_qp_out_det_rec.line_quantity is not null) THEN
3883 -- x_trip_charges_rec.handling_chrg :=
3884 -- l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
3885 -- bug 3474455
3886 x_trip_charges_tab(l_trip_index).handling_chrg := l_qp_out_det_rec.adjustment_amount;
3887 x_trip_charges_tab(l_trip_index).handling_chrg_basis := fte_rtg_globals.G_VOLUME_BASIS;
3888 END IF;
3889 END IF;
3890
3891 IF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_HANDLING_FLAT_CH) THEN
3892 x_trip_charges_tab(l_trip_index).handling_chrg := l_qp_out_det_rec.adjustment_amount;
3893 x_trip_charges_tab(l_trip_index).handling_chrg_basis := fte_rtg_globals.G_FLAT_BASIS;
3894 END IF;
3895
3896 -- TODO : Add other handling basis if we support them
3897
3898 IF (l_qp_out_det_rec.charge_subtype_code
3899 = fte_rtg_globals.G_C_CONTINUOUS_MOVE_DISCOUNT) THEN
3900 x_trip_charges_tab(l_trip_index).cm_discount_percent := l_qp_out_det_rec.operand_value;
3901 END IF;
3902
3903 -- Fuel Surcharge : bug: 3353264 (enhancement)
3904 IF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_FUEL_CH) THEN
3905 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3906 'G_C_FUEL_CH : operand_value='||l_qp_out_det_rec.operand_value);
3907 IF (l_qp_out_det_rec.operand_value >0) THEN
3908 x_trip_charges_tab(l_trip_index).fuel_chrg :=
3909 ( (x_trip_charges_tab(l_trip_index).base_dist_load_chrg
3910 + x_trip_charges_tab(l_trip_index).base_dist_unload_chrg
3911 + x_trip_charges_tab(l_trip_index).base_unit_chrg
3912 + x_trip_charges_tab(l_trip_index).base_time_chrg
3913 + x_trip_charges_tab(l_trip_index).base_flat_chrg) * l_qp_out_det_rec.operand_value )/100;
3914 ELSE
3915 x_trip_charges_tab(l_trip_index).fuel_chrg := 0;
3916 END IF;
3917 END IF;
3918
3919 END IF; -- load charges
3920
3921 -------------------- STOP CHARGES --------------------------
3922 -- Initialize l_stop_rec before each iteration. Otherwise nasty spillover effect
3923 l_stop_rec.stop_id := 0;
3924 l_stop_rec.trip_id := 0;
3925 l_stop_rec.weekday_layover_chrg := 0;
3926 l_stop_rec.weekend_layover_chrg := 0;
3927 l_stop_rec.loading_chrg := 0;
3928 l_stop_rec.loading_chrg_basis := null;
3929 l_stop_rec.ast_loading_chrg := 0;
3930 l_stop_rec.ast_loading_chrg_basis := null;
3931 l_stop_rec.unloading_chrg := 0;
3932 l_stop_rec.unloading_chrg_basis := 0;
3933 l_stop_rec.ast_unloading_chrg := 0;
3934 l_stop_rec.ast_unloading_chrg_basis := null;
3935 l_stop_rec.origin_surchrg := 0;
3936 l_stop_rec.destination_surchrg := 0;
3937 l_stop_rec.fac_loading_chrg := 0;
3938 l_stop_rec.fac_loading_chrg_basis := null;
3939 l_stop_rec.fac_ast_loading_chrg := 0;
3940 l_stop_rec.fac_ast_loading_chrg_basis := null;
3941 l_stop_rec.fac_unloading_chrg := 0;
3942 l_stop_rec.fac_unloading_chrg_basis := null;
3943 l_stop_rec.fac_ast_unloading_chrg := 0;
3944 l_stop_rec.fac_ast_unloading_chrg_basis := null;
3945 l_stop_rec.fac_handling_chrg := 0;
3946 l_stop_rec.fac_handling_chrg_basis := null;
3947 l_stop_rec.fac_currency := null;
3948
3949 IF (l_req_line_info_rec.line_type = G_STOP_CHARGE_LINE
3950 OR l_req_line_info_rec.line_type = G_FACILITY_CHARGE_LINE ) THEN
3951
3952 l_stop_index := g_req_line_info_tab(p_qp_output_detail_rows(i).line_index).stop_index;
3953 l_stop_id := p_stop_tab(l_stop_index).stop_id;
3954
3955 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
3956 'l_stop_index='||l_stop_index||' l_stop_id='||l_stop_id);
3957 /*
3958 -- check if this stop_id is already in the stop output table
3959 -- *** Assumes that x_stop_charges_tab is indexed by stop_id
3960 IF ( x_stop_charges_tab.EXISTS(l_stop_id) ) THEN
3961 l_stop_rec := x_stop_charges_tab(l_stop_id);
3962 ELSE
3963 l_stop_rec.stop_id := l_stop_id;
3964 l_stop_rec.trip_id := p_stop_tab(l_stop_index).trip_id;
3965 l_stop_rec.fac_currency := p_stop_tab(l_stop_index).fac_currency;
3966 END IF;
3967 */
3968 IF ( x_stop_charges_tab.EXISTS(l_stop_index) ) THEN
3969 l_stop_rec := x_stop_charges_tab(l_stop_index);
3970 ELSE
3971 l_stop_rec.stop_id := l_stop_id;
3972 l_stop_rec.trip_id := p_stop_tab(l_stop_index).trip_id;
3973 l_stop_rec.fac_currency := p_stop_tab(l_stop_index).fac_currency;
3974 END IF;
3975
3976
3977 END IF;
3978
3979
3980 -------------------- CARRIER STOP CHARGES -------------------
3981
3982 IF (l_req_line_info_rec.line_type = G_STOP_CHARGE_LINE ) THEN
3983 -- dig up stop level charges
3984
3985 -- Look for Loading and Assisted Loading Charges --
3986
3987 IF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_LOADING_WEIGHT_CH ) THEN
3988
3989 IF (l_qp_out_det_rec.adjustment_amount is not null
3990 AND l_qp_out_det_rec.line_quantity is not null) THEN
3991 -- l_stop_rec.loading_chrg :=
3992 -- l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
3993 -- bug 3474455
3994 l_stop_rec.loading_chrg := l_qp_out_det_rec.adjustment_amount;
3995 l_stop_rec.loading_chrg_basis := fte_rtg_globals.G_WEIGHT_BASIS;
3996 END IF;
3997
3998 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_LOADING_VOLUME_CH ) THEN
3999
4000 IF (l_qp_out_det_rec.adjustment_amount is not null
4001 AND l_qp_out_det_rec.line_quantity is not null) THEN
4002 --l_stop_rec.loading_chrg :=
4003 -- l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
4004 -- bug 3474455
4005 l_stop_rec.loading_chrg := l_qp_out_det_rec.adjustment_amount;
4006 l_stop_rec.loading_chrg_basis := fte_rtg_globals.G_VOLUME_BASIS;
4007 END IF;
4008
4009 ELSIF (l_qp_out_det_rec.charge_subtype_code
4010 = fte_rtg_globals.G_C_LOADING_CONTAINER_CH ) THEN
4011
4012 IF (l_qp_out_det_rec.adjustment_amount is not null
4013 AND l_qp_out_det_rec.line_quantity is not null) THEN
4014 -- l_stop_rec.loading_chrg :=
4015 -- l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
4016 -- bug 3474455
4017 l_stop_rec.loading_chrg := l_qp_out_det_rec.adjustment_amount;
4018 l_stop_rec.loading_chrg_basis := fte_rtg_globals.G_CONTAINER_BASIS;
4019 END IF;
4020
4021 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_LOADING_PALLET_CH ) THEN
4022
4023 IF (l_qp_out_det_rec.adjustment_amount is not null
4024 AND l_qp_out_det_rec.line_quantity is not null) THEN
4025 -- l_stop_rec.loading_chrg :=
4026 -- l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
4027 -- bug 3474455
4028 l_stop_rec.loading_chrg := l_qp_out_det_rec.adjustment_amount;
4029 l_stop_rec.loading_chrg_basis := fte_rtg_globals.G_PALLET_BASIS;
4030 END IF;
4031
4032 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_LOADING_FLAT_CH ) THEN
4033
4034 l_stop_rec.loading_chrg := l_qp_out_det_rec.adjustment_amount;
4035 l_stop_rec.loading_chrg_basis := fte_rtg_globals.G_FLAT_BASIS;
4036
4037 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_AST_LOADING_WEIGHT_CH ) THEN
4038
4039 IF (l_qp_out_det_rec.adjustment_amount is not null
4040 AND l_qp_out_det_rec.line_quantity is not null) THEN
4041 -- l_stop_rec.ast_loading_chrg :=
4042 -- l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
4043 -- bug 3474455
4044 l_stop_rec.ast_loading_chrg := l_qp_out_det_rec.adjustment_amount;
4045 l_stop_rec.ast_loading_chrg_basis := fte_rtg_globals.G_WEIGHT_BASIS;
4046 END IF;
4047
4048 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_AST_LOADING_VOLUME_CH ) THEN
4049
4050 IF (l_qp_out_det_rec.adjustment_amount is not null
4051 AND l_qp_out_det_rec.line_quantity is not null) THEN
4052 -- l_stop_rec.ast_loading_chrg :=
4053 -- l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
4054 -- bug 3474455
4055 l_stop_rec.ast_loading_chrg := l_qp_out_det_rec.adjustment_amount;
4056 l_stop_rec.ast_loading_chrg_basis := fte_rtg_globals.G_VOLUME_BASIS;
4057 END IF;
4058
4059 ELSIF (l_qp_out_det_rec.charge_subtype_code
4060 = fte_rtg_globals.G_C_AST_LOADING_CONTAINER_CH ) THEN
4061
4062 IF (l_qp_out_det_rec.adjustment_amount is not null
4063 AND l_qp_out_det_rec.line_quantity is not null) THEN
4064 -- l_stop_rec.ast_loading_chrg :=
4065 -- l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
4066 -- bug 3474455
4067 l_stop_rec.ast_loading_chrg_basis := fte_rtg_globals.G_CONTAINER_BASIS;
4068 l_stop_rec.ast_loading_chrg := l_qp_out_det_rec.adjustment_amount;
4069 END IF;
4070
4071 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_AST_LOADING_PALLET_CH ) THEN
4072
4073 IF (l_qp_out_det_rec.adjustment_amount is not null
4074 AND l_qp_out_det_rec.line_quantity is not null) THEN
4075 -- l_stop_rec.ast_loading_chrg :=
4076 -- l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
4077 -- bug 3474455
4078 l_stop_rec.ast_loading_chrg := l_qp_out_det_rec.adjustment_amount;
4079 l_stop_rec.ast_loading_chrg_basis := fte_rtg_globals.G_PALLET_BASIS;
4080 END IF;
4081
4082 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_AST_LOADING_FLAT_CH ) THEN
4083
4084 l_stop_rec.ast_loading_chrg := l_qp_out_det_rec.adjustment_amount;
4085 l_stop_rec.ast_loading_chrg_basis := fte_rtg_globals.G_FLAT_BASIS;
4086
4087 END IF; -- Loading and Assisted Loading
4088
4089 -- Look for Unloading and Assisted Unloading Charges
4090 IF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_UNLOADING_WEIGHT_CH ) THEN
4091
4092 IF (l_qp_out_det_rec.adjustment_amount is not null
4093 AND l_qp_out_det_rec.line_quantity is not null) THEN
4094 -- l_stop_rec.unloading_chrg :=
4095 -- l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
4096 -- bug 3474455
4097 l_stop_rec.unloading_chrg := l_qp_out_det_rec.adjustment_amount;
4098 l_stop_rec.unloading_chrg_basis := fte_rtg_globals.G_WEIGHT_BASIS;
4099 END IF;
4100
4101 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_UNLOADING_VOLUME_CH ) THEN
4102
4103 IF (l_qp_out_det_rec.adjustment_amount is not null
4104 AND l_qp_out_det_rec.line_quantity is not null) THEN
4105 -- l_stop_rec.unloading_chrg :=
4106 -- l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
4107 -- bug 3474455
4108 l_stop_rec.unloading_chrg := l_qp_out_det_rec.adjustment_amount;
4109 l_stop_rec.unloading_chrg_basis := fte_rtg_globals.G_VOLUME_BASIS;
4110 END IF;
4111
4112 ELSIF (l_qp_out_det_rec.charge_subtype_code
4113 = fte_rtg_globals.G_C_UNLOADING_CONTAINER_CH ) THEN
4114
4115 IF (l_qp_out_det_rec.adjustment_amount is not null
4116 AND l_qp_out_det_rec.line_quantity is not null) THEN
4117 -- l_stop_rec.unloading_chrg :=
4118 -- l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
4119 -- bug 3474455
4120 l_stop_rec.unloading_chrg := l_qp_out_det_rec.adjustment_amount;
4121 l_stop_rec.unloading_chrg_basis := fte_rtg_globals.G_CONTAINER_BASIS;
4122 END IF;
4123
4124 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_UNLOADING_PALLET_CH ) THEN
4125
4126 IF (l_qp_out_det_rec.adjustment_amount is not null
4127 AND l_qp_out_det_rec.line_quantity is not null) THEN
4128 -- l_stop_rec.unloading_chrg :=
4129 -- l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
4130 -- bug 3474455
4131 l_stop_rec.unloading_chrg := l_qp_out_det_rec.adjustment_amount;
4132 l_stop_rec.unloading_chrg_basis := fte_rtg_globals.G_PALLET_BASIS;
4133 END IF;
4134
4135 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_UNLOADING_FLAT_CH ) THEN
4136
4137 l_stop_rec.unloading_chrg := l_qp_out_det_rec.adjustment_amount;
4138 l_stop_rec.unloading_chrg_basis := fte_rtg_globals.G_FLAT_BASIS;
4139
4140 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_AST_UNLOADING_WEIGHT_CH ) THEN
4141
4142 IF (l_qp_out_det_rec.adjustment_amount is not null
4143 AND l_qp_out_det_rec.line_quantity is not null) THEN
4144 -- l_stop_rec.ast_unloading_chrg :=
4145 -- l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
4146 -- bug 3474455
4147 l_stop_rec.ast_unloading_chrg := l_qp_out_det_rec.adjustment_amount;
4148 l_stop_rec.ast_unloading_chrg_basis := fte_rtg_globals.G_WEIGHT_BASIS;
4149 END IF;
4150
4151 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_AST_UNLOADING_VOLUME_CH ) THEN
4152
4153 IF (l_qp_out_det_rec.adjustment_amount is not null
4154 AND l_qp_out_det_rec.line_quantity is not null) THEN
4155 -- l_stop_rec.ast_unloading_chrg :=
4156 -- l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
4157 -- bug 3474455
4158 l_stop_rec.ast_unloading_chrg := l_qp_out_det_rec.adjustment_amount;
4159 l_stop_rec.ast_unloading_chrg_basis := fte_rtg_globals.G_VOLUME_BASIS;
4160 END IF;
4161
4162 ELSIF (l_qp_out_det_rec.charge_subtype_code
4163 = fte_rtg_globals.G_C_AST_UNLOADING_CONTAINER_CH ) THEN
4164
4165 IF (l_qp_out_det_rec.adjustment_amount is not null
4166 AND l_qp_out_det_rec.line_quantity is not null) THEN
4167 -- l_stop_rec.ast_unloading_chrg :=
4168 -- l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
4169 -- bug 3474455
4170 l_stop_rec.ast_unloading_chrg := l_qp_out_det_rec.adjustment_amount;
4171 l_stop_rec.ast_unloading_chrg_basis := fte_rtg_globals.G_CONTAINER_BASIS;
4172 END IF;
4173
4174 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_AST_UNLOADING_PALLET_CH ) THEN
4175
4176 IF (l_qp_out_det_rec.adjustment_amount is not null
4177 AND l_qp_out_det_rec.line_quantity is not null) THEN
4178 -- l_stop_rec.ast_unloading_chrg :=
4179 -- l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
4180 -- bug 3474455
4181 l_stop_rec.ast_unloading_chrg := l_qp_out_det_rec.adjustment_amount;
4182 l_stop_rec.ast_unloading_chrg_basis := fte_rtg_globals.G_PALLET_BASIS;
4183 END IF;
4184
4185 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_AST_UNLOADING_FLAT_CH ) THEN
4186
4187 l_stop_rec.ast_unloading_chrg := l_qp_out_det_rec.adjustment_amount;
4188 l_stop_rec.ast_unloading_chrg_basis := fte_rtg_globals.G_FLAT_BASIS;
4189
4190 END IF; -- Unloading and Assisted Unloading
4191
4192
4193 -- Look for Origin and Destination Surcharges --
4194
4195 IF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_ORIGIN_SURCHRG ) THEN
4196
4197 l_stop_rec.origin_surchrg := l_qp_out_det_rec.adjustment_amount;
4198
4199 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_DESTINATION_SURCHRG ) THEN
4200
4201 l_stop_rec.destination_surchrg := l_qp_out_det_rec.adjustment_amount;
4202
4203 END IF;
4204
4205 -- Look for Weekday and Weekend Layover Charges
4206
4207 IF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_WEEKDAY_LAYOVER_CH ) THEN
4208
4209 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
4210 'G_C_WEEKDAY_LAYOVER_CH : adjustment_amount='||l_qp_out_det_rec.adjustment_amount);
4211 IF (l_qp_out_det_rec.adjustment_amount is not null
4212 AND l_qp_out_det_rec.line_quantity is not null) THEN
4213 -- l_stop_rec.weekday_layover_chrg :=
4214 -- l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
4215 -- bug 3474455
4216 l_stop_rec.weekday_layover_chrg := l_qp_out_det_rec.adjustment_amount;
4217 END IF;
4218
4219 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_C_WEEKEND_LAYOVER_CH ) THEN
4220
4221 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
4222 'G_C_WEEKEND_LAYOVER_CH : adjustment_amount='||l_qp_out_det_rec.adjustment_amount);
4223
4224 l_num_of_weekend_layover := p_stop_tab(l_stop_index).weekend_layovers;
4225 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
4226 'l_num_of_weekend_layover='||l_num_of_weekend_layover);
4227
4228 l_stop_rec.weekend_layover_chrg :=
4229 l_qp_out_det_rec.adjustment_amount * l_num_of_weekend_layover;
4230
4231 END IF;
4232
4233 -- Assign the l_stop_rec back to the stop output table
4234 -- For multiple the stop_charges_tab is indexed by the stop_index and
4235 -- not stop id
4236 x_stop_charges_tab(l_stop_index) := l_stop_rec ;
4237
4238 END IF; -- stop charge line
4239
4240 -------------------- FACILITY STOP CHARGES -------------------
4241
4242 IF (l_req_line_info_rec.line_type = G_FACILITY_CHARGE_LINE ) THEN
4243 -- dig up facility level charges
4244
4245 -- Look for Facility Loading and Assisted Loading Charges --
4246
4247 IF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_F_LOADING_WEIGHT_CH ) THEN
4248
4249 -- l_stop_rec.fac_loading_chrg
4250 -- := l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
4251 -- bug 3474455
4252 l_stop_rec.fac_loading_chrg := l_qp_out_det_rec.adjustment_amount;
4253 l_stop_rec.fac_loading_chrg_basis := fte_rtg_globals.G_WEIGHT_BASIS;
4254
4255 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_F_LOADING_VOLUME_CH ) THEN
4256
4257 -- l_stop_rec.fac_loading_chrg
4258 -- := l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
4259 -- bug 3474455
4260 l_stop_rec.fac_loading_chrg := l_qp_out_det_rec.adjustment_amount;
4261 l_stop_rec.fac_loading_chrg_basis := fte_rtg_globals.G_VOLUME_BASIS;
4262
4263 ELSIF (l_qp_out_det_rec.charge_subtype_code
4264 = fte_rtg_globals.G_F_LOADING_CONTAINER_CH ) THEN
4265
4266 -- l_stop_rec.fac_loading_chrg
4267 -- := l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
4268 -- bug 3474455
4269 l_stop_rec.fac_loading_chrg := l_qp_out_det_rec.adjustment_amount;
4270 l_stop_rec.fac_loading_chrg_basis := fte_rtg_globals.G_CONTAINER_BASIS;
4271
4272 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_F_LOADING_PALLET_CH ) THEN
4273
4274 -- l_stop_rec.fac_loading_chrg
4275 -- := l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
4276 -- bug 3474455
4277 l_stop_rec.fac_loading_chrg := l_qp_out_det_rec.adjustment_amount;
4278 l_stop_rec.fac_loading_chrg_basis := fte_rtg_globals.G_PALLET_BASIS;
4279
4280 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_F_LOADING_FLAT_CH ) THEN
4281
4282 l_stop_rec.fac_loading_chrg := l_qp_out_det_rec.adjustment_amount;
4283 l_stop_rec.fac_loading_chrg_basis := fte_rtg_globals.G_FLAT_BASIS;
4284
4285 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_F_AST_LOADING_WEIGHT_CH ) THEN
4286
4287 -- l_stop_rec.fac_ast_loading_chrg
4288 -- := l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
4289 -- bug 3474455
4290 l_stop_rec.fac_ast_loading_chrg := l_qp_out_det_rec.adjustment_amount;
4291 l_stop_rec.fac_ast_loading_chrg_basis := fte_rtg_globals.G_WEIGHT_BASIS;
4292
4293 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_F_AST_LOADING_VOLUME_CH ) THEN
4294
4295 -- l_stop_rec.fac_ast_loading_chrg
4296 -- := l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
4297 -- bug 3474455
4298 l_stop_rec.fac_ast_loading_chrg := l_qp_out_det_rec.adjustment_amount;
4299 l_stop_rec.fac_ast_loading_chrg_basis := fte_rtg_globals.G_VOLUME_BASIS;
4300
4301 ELSIF (l_qp_out_det_rec.charge_subtype_code
4302 = fte_rtg_globals.G_F_AST_LOADING_CONTAINER_CH ) THEN
4303
4304 -- l_stop_rec.fac_ast_loading_chrg
4305 -- := l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
4306 -- bug 3474455
4307 l_stop_rec.fac_ast_loading_chrg := l_qp_out_det_rec.adjustment_amount;
4308 l_stop_rec.fac_ast_loading_chrg_basis := fte_rtg_globals.G_CONTAINER_BASIS;
4309
4310 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_F_AST_LOADING_PALLET_CH ) THEN
4311
4312 l_stop_rec.fac_ast_loading_chrg
4313 := l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
4314 -- bug 3474455
4315 l_stop_rec.fac_ast_loading_chrg := l_qp_out_det_rec.adjustment_amount;
4316 l_stop_rec.fac_ast_loading_chrg_basis := fte_rtg_globals.G_PALLET_BASIS;
4317
4318 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_F_AST_LOADING_FLAT_CH ) THEN
4319
4320 l_stop_rec.fac_ast_loading_chrg := l_qp_out_det_rec.adjustment_amount;
4321 l_stop_rec.fac_ast_loading_chrg_basis := fte_rtg_globals.G_FLAT_BASIS;
4322
4323 END IF; -- Facility Loading and Assisted Loading
4324
4325 -- Look for Facility Unloading and Assisted Unloading Charges
4326 IF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_F_UNLOADING_WEIGHT_CH ) THEN
4327
4328 -- l_stop_rec.fac_unloading_chrg
4329 -- := l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
4330 -- bug 3474455
4331 l_stop_rec.fac_unloading_chrg := l_qp_out_det_rec.adjustment_amount;
4332 l_stop_rec.fac_unloading_chrg_basis := fte_rtg_globals.G_WEIGHT_BASIS;
4333
4334 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_F_UNLOADING_VOLUME_CH ) THEN
4335
4336 -- l_stop_rec.fac_unloading_chrg
4337 -- := l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
4338 -- bug 3474455
4339 l_stop_rec.fac_unloading_chrg := l_qp_out_det_rec.adjustment_amount;
4340 l_stop_rec.fac_unloading_chrg_basis := fte_rtg_globals.G_VOLUME_BASIS;
4341
4342 ELSIF (l_qp_out_det_rec.charge_subtype_code
4343 = fte_rtg_globals.G_F_UNLOADING_CONTAINER_CH ) THEN
4344
4345 -- l_stop_rec.fac_unloading_chrg
4346 -- := l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
4347 -- bug 3474455
4348 l_stop_rec.fac_unloading_chrg := l_qp_out_det_rec.adjustment_amount;
4349 l_stop_rec.fac_unloading_chrg_basis := fte_rtg_globals.G_CONTAINER_BASIS;
4350
4351 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_F_UNLOADING_PALLET_CH ) THEN
4352
4353 -- l_stop_rec.fac_unloading_chrg
4354 -- := l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
4355 -- bug 3474455
4356 l_stop_rec.fac_unloading_chrg := l_qp_out_det_rec.adjustment_amount;
4357 l_stop_rec.fac_unloading_chrg_basis := fte_rtg_globals.G_PALLET_BASIS;
4358
4359 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_F_UNLOADING_FLAT_CH ) THEN
4360
4361 l_stop_rec.fac_unloading_chrg := l_qp_out_det_rec.adjustment_amount;
4362 l_stop_rec.fac_unloading_chrg_basis := fte_rtg_globals.G_FLAT_BASIS;
4363
4364 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_F_AST_UNLOADING_WEIGHT_CH ) THEN
4365
4366 -- l_stop_rec.fac_ast_unloading_chrg
4367 -- := l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
4368 -- bug 3474455
4369 l_stop_rec.fac_ast_unloading_chrg := l_qp_out_det_rec.adjustment_amount;
4370 l_stop_rec.fac_ast_unloading_chrg_basis := fte_rtg_globals.G_WEIGHT_BASIS;
4371
4372 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_F_AST_UNLOADING_VOLUME_CH ) THEN
4373
4374 -- l_stop_rec.fac_ast_unloading_chrg
4375 -- := l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
4376 -- bug 3474455
4377 l_stop_rec.fac_ast_unloading_chrg := l_qp_out_det_rec.adjustment_amount;
4378 l_stop_rec.fac_ast_unloading_chrg_basis := fte_rtg_globals.G_VOLUME_BASIS;
4379
4380 ELSIF (l_qp_out_det_rec.charge_subtype_code
4381 = fte_rtg_globals.G_F_AST_UNLOADING_CONTAINER_CH ) THEN
4382
4383 -- l_stop_rec.fac_ast_unloading_chrg
4384 -- := l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
4385 -- bug 3474455
4386 l_stop_rec.fac_ast_unloading_chrg := l_qp_out_det_rec.adjustment_amount;
4387 l_stop_rec.fac_ast_unloading_chrg_basis := fte_rtg_globals.G_CONTAINER_BASIS;
4388
4389 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_F_AST_UNLOADING_PALLET_CH ) THEN
4390
4391 -- l_stop_rec.fac_ast_unloading_chrg
4392 -- := l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
4393 -- bug 3474455
4394 l_stop_rec.fac_ast_unloading_chrg := l_qp_out_det_rec.adjustment_amount;
4395 l_stop_rec.fac_ast_unloading_chrg_basis := fte_rtg_globals.G_PALLET_BASIS;
4396
4397 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_F_AST_UNLOADING_FLAT_CH ) THEN
4398
4399 l_stop_rec.fac_ast_unloading_chrg := l_qp_out_det_rec.adjustment_amount;
4400 l_stop_rec.fac_ast_unloading_chrg_basis := fte_rtg_globals.G_FLAT_BASIS;
4401
4402 END IF; -- Facility Unloading and Assisted Unloading
4403
4404 -- Look for Facility Handling Charges --
4405
4406 IF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_F_HANDLING_WEIGHT_CH ) THEN
4407
4408 -- l_stop_rec.fac_handling_chrg
4409 -- := l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
4410 -- bug 3474455
4411 l_stop_rec.fac_handling_chrg := l_qp_out_det_rec.adjustment_amount;
4412 l_stop_rec.fac_handling_chrg_basis := fte_rtg_globals.G_WEIGHT_BASIS;
4413
4414 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_F_HANDLING_VOLUME_CH ) THEN
4415
4416 -- l_stop_rec.fac_handling_chrg
4417 -- := l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
4418 -- bug 3474455
4419 l_stop_rec.fac_handling_chrg := l_qp_out_det_rec.adjustment_amount;
4420 l_stop_rec.fac_handling_chrg_basis := fte_rtg_globals.G_VOLUME_BASIS;
4421
4422 ELSIF (l_qp_out_det_rec.charge_subtype_code
4423 = fte_rtg_globals.G_F_HANDLING_CONTAINER_CH ) THEN
4424
4425 -- l_stop_rec.fac_handling_chrg
4426 -- := l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
4427 -- bug 3474455
4428 l_stop_rec.fac_handling_chrg := l_qp_out_det_rec.adjustment_amount;
4429 l_stop_rec.fac_handling_chrg_basis := fte_rtg_globals.G_CONTAINER_BASIS;
4430
4431 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_F_HANDLING_PALLET_CH ) THEN
4432
4433 -- l_stop_rec.fac_handling_chrg
4434 -- := l_qp_out_det_rec.adjustment_amount * l_qp_out_det_rec.line_quantity;
4435 -- bug 3474455
4436 l_stop_rec.fac_handling_chrg := l_qp_out_det_rec.adjustment_amount;
4437 l_stop_rec.fac_handling_chrg_basis := fte_rtg_globals.G_PALLET_BASIS;
4438
4439 ELSIF (l_qp_out_det_rec.charge_subtype_code = fte_rtg_globals.G_F_HANDLING_FLAT_CH ) THEN
4440
4441 l_stop_rec.fac_handling_chrg := l_qp_out_det_rec.adjustment_amount;
4442 l_stop_rec.fac_handling_chrg_basis := fte_rtg_globals.G_FLAT_BASIS;
4443
4444 END IF; -- facility handling charges
4445
4446 -----------------------------------------------------------------
4447
4448 -- Assign the l_stop_rec back to the stop output table
4449 -- For multiple the stop_charges_tab is indexed by the stop_index and
4450 -- not stop id
4451
4452 x_stop_charges_tab(l_stop_index) := l_stop_rec ;
4453
4454 END IF; -- facility charge line
4455
4456 EXIT WHEN i = p_qp_output_detail_rows.LAST;
4457 i := p_qp_output_detail_rows.NEXT(i);
4458 END LOOP;
4459 END IF;
4460
4461 fte_freight_pricing_util.unset_method(l_log_level,l_method_name);
4462
4463 EXCEPTION
4464 WHEN FND_API.G_EXC_ERROR THEN
4465 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4466 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_method_name);
4467
4468 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4469 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4470 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_method_name);
4471
4472 WHEN OTHERS THEN
4473 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4474 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_others');
4475 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
4476 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_method_name);
4477
4478
4479 END retrieve_qp_output_multiple;
4480
4481
4482
4483
4484 PROCEDURE check_qp_ipl_multiple(
4485 p_start_index IN NUMBER,
4486 p_end_index IN NUMBER,
4487 p_qp_output_line_rows IN QP_PREQ_GRP.LINE_TBL_TYPE,
4488 x_exceptions IN OUT NOCOPY FTE_TL_CORE.tl_exceptions_tab_type,
4489 x_return_status OUT NOCOPY VARCHAR2)
4490
4491 IS
4492 i NUMBER;
4493 l_non_dummy_row_count NUMBER;
4494 l_dummy_row_count NUMBER;
4495 l_dummy_ipl_count NUMBER;
4496 l_non_dummy_ipl_count NUMBER;
4497 l_line_type NUMBER;
4498 l_line_index NUMBER;
4499 l_ipl_flag VARCHAR2(1);
4500
4501
4502 l_trip_index NUMBER;
4503 l_non_dummy_row_count_tab DBMS_UTILITY.NUMBER_ARRAY;
4504 l_dummy_row_count_tab DBMS_UTILITY.NUMBER_ARRAY;
4505 l_dummy_ipl_count_tab DBMS_UTILITY.NUMBER_ARRAY;
4506 l_non_dummy_ipl_count_tab DBMS_UTILITY.NUMBER_ARRAY;
4507
4508
4509 l_log_level NUMBER := fte_freight_pricing_util.G_DBG;
4510 l_method_name VARCHAR2(50) := 'check_qp_ipl';
4511
4512 BEGIN
4513 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4514 fte_freight_pricing_util.reset_dbg_vars;
4515 fte_freight_pricing_util.set_method(l_log_level,l_method_name);
4516
4517 -- l_non_dummy_row_count:=0;
4518 -- bug 3610889
4519 i:=p_start_index;
4520 WHILE (i <= p_end_index)
4521 LOOP
4522 l_non_dummy_row_count_tab(i):=x_exceptions(i).implicit_non_dummy_cnt;
4523 l_dummy_row_count_tab(i):=0;
4524 l_dummy_ipl_count_tab(i):=0;
4525 l_non_dummy_ipl_count_tab(i):=0;
4526
4527 i:=i+1;
4528 END LOOP;
4529
4530 i:=p_qp_output_line_rows.FIRST;
4531 WHILE (i IS NOT NULL)
4532 LOOP
4533 IF ((p_qp_output_line_rows(i).status_code IS NOT NULL) AND (p_qp_output_line_rows(i).status_code='IPL'))
4534 THEN
4535 l_ipl_flag:='Y';
4536 ELSE
4537 l_ipl_flag:='N';
4538 END IF;
4539 l_line_index:=p_qp_output_line_rows(i).line_index;
4540 IF ((l_line_index IS NOT NULL) AND (g_req_line_info_tab.EXISTS(l_line_index)))
4541 THEN
4542 l_trip_index:=g_req_line_info_tab(l_line_index).trip_index;
4543 l_line_type:=g_req_line_info_tab(l_line_index).line_type;
4544 --SUSUREND :classiffy a line as dummy if it is a trip level charge line,stop level charge line
4545 --or facility line
4546 IF ((l_line_type IS NOT NULL) AND (l_line_type <> G_LOAD_CHARGE_LINE)
4547 AND (l_line_type <>G_STOP_CHARGE_LINE ) AND (l_line_type <>G_FACILITY_CHARGE_LINE))
4548 THEN
4549 l_non_dummy_row_count_tab(l_trip_index):=l_non_dummy_row_count_tab(l_trip_index)+1;
4550 IF (l_ipl_flag='Y')
4551 THEN
4552 l_non_dummy_ipl_count_tab(l_trip_index):=l_non_dummy_ipl_count_tab(l_trip_index)+1;
4553 END IF;
4554 ELSE
4555 l_dummy_row_count_tab(l_trip_index):=l_dummy_row_count_tab(l_trip_index)+1;
4556 IF (l_ipl_flag='Y')
4557 THEN
4558 l_dummy_ipl_count_tab(l_trip_index):=l_dummy_ipl_count_tab(l_trip_index)+1;
4559 END IF;
4560
4561
4562 END IF;
4563
4564 ELSE
4565 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,'Index not found:'||i);
4566
4567 END IF;
4568
4569
4570 i:=p_qp_output_line_rows.NEXT(i);
4571 END LOOP;
4572
4573 i:=p_start_index;
4574 WHILE (i <= p_end_index)
4575 LOOP
4576
4577 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_DBG,' Number of dummy lines:'||l_dummy_row_count_tab(i)
4578 ||' Number of non dummy lines:'||l_non_dummy_row_count_tab(i)||' Dummy IPLs:'||l_dummy_ipl_count_tab(i)||
4579 ' Non Dummy IPLs :'||l_non_dummy_ipl_count_tab(i));
4580 IF (l_non_dummy_ipl_count_tab(i) >= l_non_dummy_row_count_tab(i))
4581 THEN
4582 x_exceptions(i).check_qp_ipl_fail:='Y';
4583 x_exceptions(i).not_on_pl_flag:='Y';
4584 --raise fte_freight_pricing_util.g_not_on_pricelist;
4585 END IF;
4586
4587 i:=i+1;
4588
4589 END LOOP;
4590
4591
4592
4593 fte_freight_pricing_util.unset_method(l_log_level,l_method_name);
4594
4595 EXCEPTION
4596 WHEN fte_freight_pricing_util.g_not_on_pricelist THEN
4597 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4598 -- can use tokens here
4599 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_not_on_pricelist');
4600 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_ERR,'Item quantity not found on pricelist ');
4601 fte_freight_pricing_util.unset_method(l_log_level,l_method_name);
4602
4603 WHEN OTHERS THEN
4604 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4605 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_others');
4606 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
4607 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_method_name);
4608
4609
4610 END check_qp_ipl_multiple;
4611
4612
4613
4614 PROCEDURE check_tl_qp_op_err_multiple (
4615 p_start_index IN NUMBER,
4616 p_end_index IN NUMBER,
4617 p_req_line_info_tab IN req_line_info_tab_type,
4618 x_exceptions IN OUT NOCOPY FTE_TL_CORE.tl_exceptions_tab_type,
4619 x_return_status OUT NOCOPY VARCHAR2)
4620 IS
4621
4622 i NUMBER :=0;
4623
4624 l_category VARCHAR2(30);
4625
4626 l_trip_index NUMBER;
4627 l_ipl_cnt_tab DBMS_UTILITY.NUMBER_ARRAY;
4628 l_error_flag_tab DBMS_UTILITY.NUMBER_ARRAY;
4629 l_line_cnt_tab DBMS_UTILITY.NUMBER_ARRAY;
4630
4631
4632 l_log_level NUMBER := fte_freight_pricing_util.G_LOG;
4633 l_method_name VARCHAR2(50) := 'check_tl_qp_op_err_multiple';
4634 BEGIN
4635 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4636 fte_freight_pricing_util.reset_dbg_vars;
4637 fte_freight_pricing_util.set_method(l_log_level,l_method_name);
4638
4639 --init tab variables
4640 i:=p_start_index;
4641 WHILE(i <=p_end_index)
4642 LOOP
4643
4644 l_ipl_cnt_tab(i):=0;
4645 l_error_flag_tab(i):=0;
4646 l_line_cnt_tab(i):=0;
4647
4648 i:=i+1;
4649 END LOOP;
4650
4651 i := FTE_QP_ENGINE.g_O_line_tbl.FIRST;
4652 IF (i IS NOT NULL) THEN
4653 LOOP
4654
4655 --get the trip index of this output line
4656 l_trip_index:=p_req_line_info_tab(FTE_QP_ENGINE.g_O_line_tbl(i).line_index).trip_index;
4657
4658 l_line_cnt_tab(l_trip_index):=l_line_cnt_tab(l_trip_index)+1;
4659
4660
4661 IF (FTE_QP_ENGINE.g_O_line_tbl(i).status_code IN (
4662 QP_PREQ_GRP.G_STATUS_INVALID_PRICE_LIST ,
4663 QP_PREQ_GRP.G_STATUS_GSA_VIOLATION ,
4664 QP_PREQ_GRP.G_STS_LHS_NOT_FOUND ,
4665 QP_PREQ_GRP.G_STATUS_FORMULA_ERROR ,
4666 QP_PREQ_GRP.G_STATUS_OTHER_ERRORS ,
4667 QP_PREQ_GRP.G_STATUS_INCOMP_LOGIC ,
4668 QP_PREQ_GRP.G_STATUS_CALC_ERROR ,
4669 QP_PREQ_GRP.G_STATUS_UOM_FAILURE ,
4670 QP_PREQ_GRP.G_STATUS_INVALID_UOM ,
4671 QP_PREQ_GRP.G_STATUS_DUP_PRICE_LIST ,
4672 QP_PREQ_GRP.G_STATUS_INVALID_UOM_CONV ,
4673 QP_PREQ_GRP.G_STATUS_INVALID_INCOMP ,
4674 QP_PREQ_GRP.G_STATUS_BEST_PRICE_EVAL_ERROR )) THEN
4675 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_LOG,' LineIndex = '||i||' Status Code = '||FTE_QP_ENGINE.g_O_line_tbl(i).status_code||' Text = '||FTE_QP_ENGINE.g_O_line_tbl(i).status_text);
4676 IF (FTE_QP_ENGINE.g_O_line_tbl(i).status_code = 'IPL') THEN
4677
4678
4679 l_ipl_cnt_tab(l_trip_index):=l_ipl_cnt_tab(l_trip_index)+1;
4680
4681
4682 FTE_QP_ENGINE.g_O_line_tbl(i).unit_price := 0;
4683 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_LOG,
4684 'Following item quantity not found on pricelist :');
4685 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_LOG,
4686 ' Quantity = '||FTE_QP_ENGINE.g_I_line_quantity(i)||' '||FTE_QP_ENGINE.g_I_line_uom_code(i));
4687 ELSE
4688 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_LOG,'Settng error flag Case 1 LineIndex = '||i||' trip index:'||l_trip_index);
4689 l_error_flag_tab(l_trip_index):=1;
4690
4691 END IF;
4692 END IF;
4693 IF (FTE_QP_ENGINE.g_O_line_tbl(i).unit_price IS NULL) THEN
4694
4695 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_LOG,'Settng error flag Case 2 LineIndex = '||i||' trip index:'||l_trip_index);
4696
4697 l_error_flag_tab(l_trip_index):=1;
4698
4699 fte_freight_pricing_util.print_msg(l_log_level,'Unit price is null');
4700 -- ELSIF (FTE_QP_ENGINE.g_O_line_tbl(i).unit_price <= 0) THEN
4701 ELSIF (FTE_QP_ENGINE.g_O_line_tbl(i).unit_price < 0) THEN -- TL
4702 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_LOG,'Settng error flag Case 3 LineIndex = '||i||' trip index:'||l_trip_index);
4703 l_error_flag_tab(l_trip_index):=1;
4704 -- fte_freight_pricing_util.print_msg(l_log_level,'Unit price non-positive');
4705 fte_freight_pricing_util.print_msg(l_log_level,'Unit price negative');
4706 END IF;
4707 EXIT WHEN i >= FTE_QP_ENGINE.g_O_line_tbl.LAST;
4708 i := FTE_QP_ENGINE.g_O_line_tbl.NEXT(i);
4709 END LOOP;
4710 END IF;
4711
4712
4713 i := FTE_QP_ENGINE.g_O_line_detail_tbl.FIRST;
4714 IF (i IS NOT NULL) THEN
4715 LOOP
4716 IF (FTE_QP_ENGINE.g_O_line_detail_tbl(i).adjustment_amount IS NULL)
4717 THEN
4718
4719 l_trip_index:=p_req_line_info_tab(FTE_QP_ENGINE.g_O_line_detail_tbl(i).line_index).trip_index;
4720 l_error_flag_tab(l_trip_index) := 1;
4721 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_LOG,'Settng error flag Case 4 LineIndex = '||i||' trip index:'||l_trip_index);
4722 fte_freight_pricing_util.print_msg(l_log_level,'Adjustment amount is null');
4723 END IF;
4724 EXIT WHEN i >= FTE_QP_ENGINE.g_O_line_detail_tbl.LAST;
4725 i := FTE_QP_ENGINE.g_O_line_detail_tbl.NEXT(i);
4726 END LOOP;
4727 END IF;
4728
4729
4730
4731 i:=p_start_index;
4732 WHILE ( i <= p_end_index)
4733 LOOP
4734
4735 IF (l_ipl_cnt_tab(i) >= l_line_cnt_tab(i)) THEN
4736 -- probably big failure - not good
4737 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_LOG,'Settng error flag Case 5 trip Index = '||i);
4738 fte_freight_pricing_util.print_msg(l_log_level,'l_ipl_cnt >= l_line_cnt for trip_index:'||i);
4739 x_exceptions(i).check_tlqp_ouputfail:='Y';
4740 x_exceptions(i).not_on_pl_flag:='Y';
4741
4742 --raise fte_freight_pricing_util.g_not_on_pricelist;
4743 ELSIF (l_ipl_cnt_tab(i) > 0) THEN
4744 -- probably ok
4745 fte_freight_pricing_util.print_msg(l_log_level,'WARNING: SOME LINES HAD IPL !!! for trip_index:'||i);
4746 END IF;
4747
4748 IF (l_error_flag_tab(i)=1)
4749 THEN
4750 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_LOG,'Settng error flag Case 6 trip Index = '||i);
4751 x_exceptions(i).check_tlqp_ouputfail:='Y';
4752 x_exceptions(i).price_req_failed:='Y';
4753 --x_exceptions(i).exception_name:=fte_freight_pricing_util.g_qp_price_request_failed;
4754 END IF;
4755
4756 i:=i+1;
4757 END LOOP;
4758
4759
4760
4761
4762
4763 fte_freight_pricing_util.unset_method(l_log_level,l_method_name);
4764 EXCEPTION
4765 WHEN fte_freight_pricing_util.g_not_on_pricelist THEN
4766 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4767 -- can use tokens here
4768 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_not_on_pricelist');
4769 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_ERR,'Following item quantity not found on pricelist :');
4770 l_category := FTE_QP_ENGINE.g_I_line_extras_tbl(i).category_id;
4771 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_ERR,' Quantity = '||FTE_QP_ENGINE.g_I_line_quantity(i)||' '||FTE_QP_ENGINE.g_I_line_uom_code(i));
4772 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_ERR,' CategoryId = '||nvl(l_category,'Consolidated'));
4773 fte_freight_pricing_util.unset_method(l_log_level,l_method_name);
4774 WHEN fte_freight_pricing_util.g_qp_price_request_failed THEN
4775 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4776 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_qp_price_request_failed');
4777 fte_freight_pricing_util.unset_method(l_log_level,l_method_name);
4778 WHEN others THEN
4779 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4780 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_others');
4781 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
4782 fte_freight_pricing_util.unset_method(l_log_level,l_method_name);
4783
4784 END check_tl_qp_op_err_multiple;
4785
4786
4787 --Identifies cache entries which have the same SM/Vehicle/Trip
4788 --Avoid sending these in one call to QP as minimum charges wont apply correctly
4789 --same SM/Vehicle/Trip results in same rate for TL
4790
4791 PROCEDURE Identify_Same_Rate_Inputs(
4792 p_start_trip_index IN NUMBER,
4793 p_end_trip_index IN NUMBER,
4794 p_trip_tab IN FTE_TL_CACHE.TL_trip_data_input_tab_type,
4795 p_stop_tab IN FTE_TL_CACHE.TL_trip_stop_input_tab_type,
4796 x_same_rate_map OUT NOCOPY DBMS_UTILITY.NUMBER_ARRAY,
4797 x_return_status OUT NOCOPY VARCHAR2) IS
4798
4799 i NUMBER;
4800 l_original_trip_index NUMBER;
4801 l_carrier_hash DBMS_UTILITY.NUMBER_ARRAY;
4802
4803 l_log_level NUMBER := fte_freight_pricing_util.G_DBG;
4804 l_method_name VARCHAR2(50) := 'Identify_Same_Rate_Inputs';
4805
4806 BEGIN
4807 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4808 fte_freight_pricing_util.reset_dbg_vars;
4809 fte_freight_pricing_util.set_method(l_log_level,l_method_name);
4810
4811 x_same_rate_map.delete;
4812 l_carrier_hash.delete;
4813 i:=p_start_trip_index;
4814 WHILE(i<=p_end_trip_index)
4815 LOOP
4816 IF(NOT l_carrier_hash.EXISTS(p_trip_tab(i).carrier_id))
4817 THEN
4818 l_carrier_hash(p_trip_tab(i).carrier_id):=i;
4819
4820 ELSE
4821 l_original_trip_index:=l_carrier_hash(p_trip_tab(i).carrier_id);
4822 IF((p_trip_tab(l_original_trip_index).carrier_id = p_trip_tab(i).carrier_id)
4823 AND (p_trip_tab(l_original_trip_index).service_type = p_trip_tab(i).service_type)
4824 AND (p_trip_tab(l_original_trip_index).mode_of_transport = p_trip_tab(i).mode_of_transport)
4825 AND (p_trip_tab(l_original_trip_index).vehicle_type = p_trip_tab(i).vehicle_type)
4826 AND (p_trip_tab(l_original_trip_index).price_list_id = p_trip_tab(i).price_list_id)
4827 AND (p_trip_tab(l_original_trip_index).trip_id = p_trip_tab(i).trip_id))
4828 THEN
4829 x_same_rate_map(i):=l_original_trip_index;
4830 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'trip Index :'||i||' same as trip Index:'||l_original_trip_index);
4831
4832 END IF;
4833
4834 END IF;
4835
4836 i:=i+1;
4837 END LOOP;
4838
4839
4840
4841 fte_freight_pricing_util.unset_method(l_log_level,l_method_name);
4842
4843 EXCEPTION
4844
4845 WHEN OTHERS THEN
4846 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4847 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_others');
4848 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
4849 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_method_name);
4850 END Identify_Same_Rate_Inputs;
4851
4852
4853 PROCEDURE Copy_Same_Rates(
4854 p_start_trip_index IN NUMBER,
4855 p_end_trip_index IN NUMBER,
4856 p_trip_tab IN FTE_TL_CACHE.TL_trip_data_input_tab_type,
4857 p_stop_tab IN FTE_TL_CACHE.TL_trip_stop_input_tab_type,
4858 p_same_rate_map IN DBMS_UTILITY.NUMBER_ARRAY,
4859 x_trip_charges_tab IN OUT NOCOPY FTE_TL_CACHE.TL_TRIP_OUTPUT_TAB_TYPE,
4860 x_stop_charges_tab IN OUT NOCOPY FTE_TL_CACHE.TL_trip_stop_output_tab_type,
4861 x_exceptions_tab IN OUT NOCOPY FTE_TL_CORE.tl_exceptions_tab_type,
4862 x_return_status OUT NOCOPY VARCHAR2) IS
4863
4864 i NUMBER;
4865 j NUMBER;
4866 k NUMBER;
4867 l_original_index NUMBER;
4868 l_stop_charge_reference NUMBER;
4869 l_log_level NUMBER := fte_freight_pricing_util.G_DBG;
4870 l_method_name VARCHAR2(50) := 'Copy_Same_Rates';
4871
4872 BEGIN
4873 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4874 fte_freight_pricing_util.reset_dbg_vars;
4875 fte_freight_pricing_util.set_method(l_log_level,l_method_name);
4876
4877 i:=p_same_rate_map.FIRST;
4878 WHILE(i IS NOT NULL)
4879 LOOP
4880 l_original_index:=p_same_rate_map(i);
4881 l_stop_charge_reference:=x_trip_charges_tab(i).stop_charge_reference;
4882
4883 x_trip_charges_tab(i):=x_trip_charges_tab(l_original_index);
4884 x_trip_charges_tab(i).stop_charge_reference:=l_stop_charge_reference;
4885 x_exceptions_tab(i):=x_exceptions_tab(l_original_index);
4886
4887 j:=x_trip_charges_tab(i).stop_charge_reference;
4888 k:=x_trip_charges_tab(l_original_index).stop_charge_reference;
4889 WHILE((p_trip_tab(i).number_of_stops > 0) AND
4890 (j<(p_trip_tab(i).number_of_stops+x_trip_charges_tab(i).stop_charge_reference)))
4891 LOOP
4892
4893 x_stop_charges_tab(j):=x_stop_charges_tab(k);
4894 k:=k+1;
4895 j:=j+1;
4896 END LOOP;
4897
4898
4899 i:=p_same_rate_map.NEXT(i);
4900
4901 END LOOP;
4902
4903
4904
4905
4906 fte_freight_pricing_util.unset_method(l_log_level,l_method_name);
4907
4908 EXCEPTION
4909
4910 WHEN OTHERS THEN
4911 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4912 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_others');
4913 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
4914 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_method_name);
4915
4916 END Copy_Same_Rates;
4917
4918
4919
4920 PROCEDURE TL_Core_Multiple (
4921 p_start_trip_index IN NUMBER,
4922 p_end_trip_index IN NUMBER,
4923 p_trip_tab IN FTE_TL_CACHE.TL_trip_data_input_tab_type,
4924 p_stop_tab IN FTE_TL_CACHE.TL_trip_stop_input_tab_type,
4925 p_carrier_pref_tab IN FTE_TL_CACHE.TL_CARRIER_PREF_TAB_TYPE,
4926 x_trip_charges_tab OUT NOCOPY FTE_TL_CACHE.TL_TRIP_OUTPUT_TAB_TYPE,
4927 x_stop_charges_tab OUT NOCOPY FTE_TL_CACHE.TL_trip_stop_output_tab_type,
4928 x_exceptions_tab OUT NOCOPY FTE_TL_CORE.tl_exceptions_tab_type,
4929 x_return_status OUT NOCOPY VARCHAR2) IS
4930
4931
4932 l_pricing_control_rec fte_freight_pricing.pricing_control_input_rec_type;
4933 l_pricing_engine_input_rec fte_freight_pricing.pricing_engine_input_rec_type;
4934 l_curr_line_idx NUMBER := 0;
4935 l_req_line_info_rec req_line_info_rec_type;
4936 l_qp_output_line_rows QP_PREQ_GRP.LINE_TBL_TYPE;
4937 l_qp_output_detail_rows QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE;
4938 l_implicit_non_dummy_cnt NUMBER := 0;
4939
4940 l_same_rate_map DBMS_UTILITY.NUMBER_ARRAY;
4941 l_exception_rec tl_exceptions_type;
4942 l_trip_rec FTE_TL_CACHE.TL_trip_data_input_rec_type;
4943 i NUMBER;
4944 l_return_status VARCHAR2(1);
4945
4946 l_log_level NUMBER := fte_freight_pricing_util.G_DBG;
4947 l_method_name VARCHAR2(50) := 'TL_Core_Multiple';
4948
4949 BEGIN
4950
4951 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4952 fte_freight_pricing_util.reset_dbg_vars;
4953 fte_freight_pricing_util.set_method(l_log_level,l_method_name);
4954
4955 -- create request lines
4956 -- distance, time, ..
4957 -- for each request line - build qualifiers
4958 -- for each base price request line - build attributes
4959 -- for each charge request line - build attributes
4960 -- call qp engine
4961 -- check qp errors
4962 -- analyze o/p
4963 -- analyze and extract charges
4964
4965 -- First clear all global tables ***
4966 g_req_line_info_tab.DELETE;
4967 FTE_QP_ENGINE.clear_globals(x_return_status => l_return_status);
4968
4969 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
4970 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
4971 FTE_FREIGHT_PRICING_UTIL.set_location(p_loc => 'After clear_globals ');
4972 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_tl_clear_globals_fl');
4973 raise FND_API.G_EXC_ERROR;
4974 END IF;
4975 END IF;
4976
4977 --initialize exception counts
4978
4979 l_exception_rec.implicit_non_dummy_cnt:=0;
4980 l_exception_rec.check_tlqp_ouputfail:='N';
4981 l_exception_rec.check_qp_ipl_fail:='N';
4982 l_exception_rec.not_on_pl_flag:='N';
4983 l_exception_rec.price_req_failed:='N';
4984 l_exception_rec.allocation_failed:='N';
4985
4986
4987 -- Identify cache entries which will result in same rates
4988
4989 Identify_Same_Rate_Inputs(
4990 p_start_trip_index =>p_start_trip_index,
4991 p_end_trip_index => p_end_trip_index,
4992 p_trip_tab =>p_trip_tab,
4993 p_stop_tab =>p_stop_tab,
4994 x_same_rate_map=> l_same_rate_map,
4995 x_return_status=>l_return_status);
4996
4997 -- Add in exception handling for identify same rate inputs
4998
4999 i:=p_start_trip_index;
5000 WHILE(i <= p_end_trip_index)
5001 LOOP
5002
5003 x_exceptions_tab(i):=l_exception_rec;
5004 x_exceptions_tab(i).trip_index:=i;
5005
5006 IF (NOT l_same_rate_map.EXISTS(i))
5007 THEN
5008
5009 -- g_effectivity dates is the global variable which stores the dates passed to QP
5010 -- these dates are set to the trip departure dates. For price list selection, only the
5011 -- trip departure date is used (not arrival).
5012
5013
5014 fte_freight_pricing.g_effectivity_dates.date_from:=p_trip_tab(i).planned_departure_date;
5015 fte_freight_pricing.g_effectivity_dates.date_to:=p_trip_tab(i).planned_departure_date;
5016
5017
5018 -- bug 3610889 : added new parameter p_implicit_non_dummy_cnt
5019 --pass in the trip_index so that a reference to it is stored
5020 --in g_req_line_info_tab
5021 create_engine_inputs(
5022 p_trip_rec => p_trip_tab(i),
5023 p_stop_tab => p_stop_tab,
5024 p_carrier_pref => p_carrier_pref_tab(i),
5025 p_trip_index =>i,
5026 x_implicit_non_dummy_cnt => x_exceptions_tab(i).implicit_non_dummy_cnt,
5027 x_return_status => l_return_status );
5028
5029 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
5030 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
5031 FTE_FREIGHT_PRICING_UTIL.set_location(p_loc => 'After create_engine_inputs ');
5032 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_tl_cr_eng_inp_failed');
5033 raise FND_API.G_EXC_ERROR;
5034 END IF;
5035 END IF;
5036
5037 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'l_implicit_non_dummy_cnt = '||x_exceptions_tab(i).implicit_non_dummy_cnt);
5038
5039
5040 ELSE
5041 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Not creating QP inputs for trip index i:'||i||' same rate as index'||l_same_rate_map(i));
5042 END IF;
5043 i:=i+1;
5044 END LOOP;
5045
5046
5047
5048 -- fte_qp_engine.print_qp_input;
5049
5050 -- call qp engine
5051
5052 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'g_req_line_info_tab.COUNT = '||g_req_line_info_tab.COUNT);
5053
5054 print_req_line_tab;
5055
5056 fte_qp_engine.call_qp_api (
5057 x_qp_output_line_rows => l_qp_output_line_rows,
5058 x_qp_output_detail_rows => l_qp_output_detail_rows,
5059 x_return_status => l_return_status);
5060
5061 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
5062 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
5063 FTE_FREIGHT_PRICING_UTIL.set_location(p_loc => 'After call_qp_api ');
5064 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_tl_call_qp_api_failed');
5065 raise FND_API.G_EXC_ERROR;
5066 END IF;
5067 END IF;
5068
5069
5070
5071 -- This will not usually fail, sets flag on x_exceptions if it fails for a particular
5072 --trip index
5073
5074 check_tl_qp_op_err_multiple (
5075 p_start_index=>p_start_trip_index,
5076 p_end_index=>p_end_trip_index,
5077 p_req_line_info_tab =>g_req_line_info_tab,
5078 x_exceptions=>x_exceptions_tab,
5079 x_return_status=>l_return_status);
5080
5081 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
5082 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
5083 FTE_FREIGHT_PRICING_UTIL.set_location(p_loc => 'After check_tl_qp_output_errors ');
5084 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_tl_chk_qp_output_failed');
5085 raise FND_API.G_EXC_ERROR;
5086 END IF;
5087 END IF;
5088
5089
5090 -- This will not usually fail, sets flag on x_exceptions if it fails for a particular
5091 --trip index
5092
5093 check_qp_ipl_multiple(
5094 p_start_index=>p_start_trip_index,
5095 p_end_index=>p_end_trip_index ,
5096 p_qp_output_line_rows=>l_qp_output_line_rows,
5097 x_exceptions=>x_exceptions_tab,
5098 x_return_status=> l_return_status );
5099
5100 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
5101 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
5102 FTE_FREIGHT_PRICING_UTIL.set_location(p_loc => 'After check_qp_ipl_mulitple ');
5103 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_tl_check_qp_ipl_failed');
5104 raise FND_API.G_EXC_ERROR;
5105 END IF;
5106 END IF;
5107
5108
5109
5110
5111 -- process qp output
5112
5113
5114
5115 retrieve_qp_output_multiple (
5116 p_start_trip_index =>p_start_trip_index,
5117 p_end_trip_index =>p_end_trip_index,
5118 p_trip_tab =>p_trip_tab,
5119 p_stop_tab => p_stop_tab,
5120 p_carrier_pref_tab =>p_carrier_pref_tab,
5121 p_qp_output_line_rows => l_qp_output_line_rows,
5122 p_qp_output_detail_rows => l_qp_output_detail_rows,
5123 x_trip_charges_tab => x_trip_charges_tab,
5124 x_stop_charges_tab => x_stop_charges_tab,
5125 x_return_status => l_return_status);
5126
5127 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
5128 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
5129 FTE_FREIGHT_PRICING_UTIL.set_location(p_loc => 'After retrieve_qp_output_multiple ');
5130 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_tl_ret_qp_out_failed');
5131 raise FND_API.G_EXC_ERROR;
5132 END IF;
5133 END IF;
5134
5135 Copy_Same_Rates(
5136 p_start_trip_index =>p_start_trip_index,
5137 p_end_trip_index =>p_end_trip_index,
5138 p_trip_tab =>p_trip_tab,
5139 p_stop_tab => p_stop_tab,
5140 p_same_rate_map =>l_same_rate_map,
5141 x_trip_charges_tab => x_trip_charges_tab,
5142 x_stop_charges_tab => x_stop_charges_tab,
5143 x_exceptions_tab => x_exceptions_tab,
5144 x_return_status => l_return_status);
5145
5146 print_output_multiple(
5147 p_start_trip_index => p_start_trip_index,
5148 p_end_trip_index => p_end_trip_index,
5149 p_trip_charges_tab => x_trip_charges_tab,
5150 p_stop_charges_tab => x_stop_charges_tab);
5151
5152 fte_freight_pricing_util.unset_method(l_log_level,l_method_name);
5153
5154 EXCEPTION
5155 WHEN FND_API.G_EXC_ERROR THEN
5156 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5157 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_method_name);
5158
5159 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5160 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
5161 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_method_name);
5162
5163 WHEN OTHERS THEN
5164 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
5165 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_others');
5166 FTE_FREIGHT_PRICING_UTIL.print_msg(FTE_FREIGHT_PRICING_UTIL.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
5167 FTE_FREIGHT_PRICING_UTIL.unset_method(l_log_level,l_method_name);
5168 END TL_Core_Multiple;
5169
5170
5171
5172 END FTE_TL_CORE;