[Home] [Help]
PACKAGE BODY: APPS.FTE_QP_ENGINE
Source
1 PACKAGE BODY FTE_QP_ENGINE AS
2 /* $Header: FTEFRQPB.pls 115.21 2003/12/18 21:26:48 susurend ship $ */
3
4 -- This package encapsulates all qp engine related methods and data structures.
5 -- It provides several utility packages to create engine input records.
6 -- It will also hold engine i/p and o/p in global tables per event.
7
8 -- stores defaults for each pricing event
9 g_engine_defaults_tab pricing_engine_def_tab_type;
10
11 -- utility methods
12
13 -- this procedure calculates the total base price of a shipment, for a given set.
14 -- multiplies unit price by line quantity
15 -- price is in the priced currency
16
17 -- can we really have set number here? set num is not stored in any of the actual qp lines
18 PROCEDURE get_total_base_price (p_set_num IN NUMBER DEFAULT 1,
19 x_price OUT NOCOPY NUMBER,
20 x_return_status OUT NOCOPY VARCHAR2)
21 IS
22 i NUMBER;
23 l_currency VARCHAR2(30);
24 l_price NUMBER;
25 l_cum_price NUMBER := 0;
26 l_return_status VARCHAR2(1);
27 l_log_level NUMBER := fte_freight_pricing_util.G_DBG;
28 l_method_name VARCHAR2(50) := 'get_total_base_price';
29 BEGIN
30 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
31 fte_freight_pricing_util.reset_dbg_vars;
32 fte_freight_pricing_util.set_method(l_log_level,'get_total_base_price');
33
34 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'p_set_num = '||p_set_num);
35 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'g_O_line_tbl.COUNT = '||g_O_line_tbl.COUNT);
36 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'g_I_line_extras_tbl.FIRST = '||g_I_line_extras_tbl.FIRST);
37
38 i := g_O_line_tbl.FIRST;
39 IF (i IS NOT NULL) THEN
40 LOOP
41 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Outside If input_set_number = '||g_I_line_extras_tbl(i).input_set_number);
42 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'unit_price = '||g_O_line_tbl(i).unit_price);
43 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'priced_quantity = '||g_O_line_tbl(i).priced_quantity);
44 IF ( g_I_line_extras_tbl(i).input_set_number = p_set_num ) THEN
45 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'input_set_number = '||g_I_line_extras_tbl(i).input_set_number);
46 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'unit_price = '||g_O_line_tbl(i).unit_price);
47 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'priced_quantity = '||g_O_line_tbl(i).priced_quantity);
48 l_cum_price := l_cum_price + (g_O_line_tbl(i).unit_price * g_O_line_tbl(i).priced_quantity);
49 END IF;
50 EXIT WHEN i >= g_O_line_tbl.LAST;
51 i := g_O_line_tbl.NEXT(i);
52 END LOOP;
53 END IF;
54
55 FTE_FREIGHT_PRICING_UTIL.print_msg(fte_freight_pricing_util.G_DBG,'l_cum_price = '||l_cum_price);
56
57 x_price := l_cum_price;
58
59 fte_freight_pricing_util.unset_method(l_log_level,'get_total_base_price');
60 EXCEPTION
61 WHEN others THEN
62 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
63 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_others');
64 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
65 fte_freight_pricing_util.unset_method(l_log_level,'get_total_base_price');
66
67 END get_total_base_price;
68
69 -- prorate (apply) new charge across engine output lines by ratio of current unit_price to current total unit price?
70 -- ( it could also be by ratio of current line amount to current total base price)
71 -- assumes that the new price is in the priced currency.
72 PROCEDURE apply_new_base_price ( p_set_num IN NUMBER DEFAULT 1,
73 p_new_total_price IN NUMBER,
74 x_return_status OUT NOCOPY VARCHAR2)
75 IS
76 i NUMBER;
77 l_cum_price NUMBER := 0;
78 l_cum_unit_price NUMBER := 0;
79 l_total_base_price NUMBER := 0;
80
81 l_return_status VARCHAR2(1);
82 l_log_level NUMBER := fte_freight_pricing_util.G_DBG;
83 l_method_name VARCHAR2(50) := 'apply_new_base_price';
84 BEGIN
85 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
86 fte_freight_pricing_util.reset_dbg_vars;
87 fte_freight_pricing_util.set_method(l_log_level,'apply_new_base_price');
88
89 -- first get total base price for the ratio
90 get_total_base_price(
91 p_set_num => p_set_num,
92 x_price => l_total_base_price,
93 x_return_status => l_return_status);
94
95 FTE_FREIGHT_PRICING_UTIL.set_location(p_loc=>'after get_total_base_price ');
96 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
97 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
98 raise FTE_FREIGHT_PRICING_UTIL.g_total_base_price_failed;
99 END IF;
100 ELSE
101 FTE_FREIGHT_PRICING_UTIL.print_msg(fte_freight_pricing_util.G_DBG,'l_total_base_price = '||l_total_base_price);
102 END IF;
103
104 i := g_O_line_tbl.FIRST;
105 IF (i is NOT NULL) THEN
106 LOOP
107 IF ( g_I_line_extras_tbl(g_O_line_tbl(i).line_index).input_set_number = p_set_num ) THEN
108 g_O_line_tbl(i).unit_price
109 := (g_O_line_tbl(i).unit_price * p_new_total_price)/l_total_base_price;
110 -- also change adjusted unit price just in case.
111 g_O_line_tbl(i).adjusted_unit_price
112 := (g_O_line_tbl(i).adjusted_unit_price * p_new_total_price)/l_total_base_price;
113 END IF; -- set num
114
115 EXIT WHEN i >= g_O_line_tbl.LAST;
116 i := g_O_line_tbl.NEXT(i);
117 END LOOP;
118 END IF;
119
120 fte_freight_pricing_util.unset_method(l_log_level,'apply_new_base_price');
121 EXCEPTION
122 WHEN FTE_FREIGHT_PRICING_UTIL.g_total_base_price_failed THEN
123 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
124 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'total_base_price_failed');
125 fte_freight_pricing_util.unset_method(l_log_level,'apply_new_base_price');
126 WHEN others THEN
127 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
128 FTE_FREIGHT_PRICING_UTIL.set_exception(l_method_name,l_log_level,'g_others');
129 FTE_FREIGHT_PRICING_UTIL.print_msg(fte_freight_pricing_util.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
130 fte_freight_pricing_util.unset_method(l_log_level,'apply_new_base_price');
131
132 END apply_new_base_price;
133
134 PROCEDURE print_commodity_price_rows (p_comm_price_rows IN commodity_price_tbl_type)
135 IS
136 i NUMBER :=0;
137 l_log_level NUMBER := fte_freight_pricing_util.G_DBG;
138 l_method_name VARCHAR2(50) := 'print_commodity_price_rows';
139 BEGIN
140
141 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_INF,'----Commodity Price Rows ----');
142 i := p_comm_price_rows.FIRST;
143 IF ( i IS NOT NULL) THEN
144 LOOP
145 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_INF,'category_id = '||p_comm_price_rows(i).category_id);
146 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_INF,'unit_price = '||p_comm_price_rows(i).unit_price);
147 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_INF,'total_wt = '||p_comm_price_rows(i).total_wt);
148 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_INF,'wt_uom = '||p_comm_price_rows(i).wt_uom);
149 EXIT WHEN i >= p_comm_price_rows.LAST;
150 i := p_comm_price_rows.NEXT(i);
151 END LOOP;
152 END IF;
153
154 END print_commodity_price_rows;
155
156 -- get me unit price for each individual commodity for each set
157 -- get me total wt. for each individual commodity
158 -- give me all weights in the deficit wt uom
159 -- currently we have implementation only for event num =1
160 PROCEDURE analyse_output_for_deficit_wt (p_set_num IN NUMBER,
161 p_wt_uom IN VARCHAR2,
162 x_commodity_price_rows OUT NOCOPY commodity_price_tbl_type,
163 x_return_status OUT NOCOPY VARCHAR2)
164 IS
165 l_return_status VARCHAR2(1);
166 i NUMBER;
167 l_category_id NUMBER;
168 l_comm_tbl commodity_price_tbl_type;
169 l_comm_row commodity_price_rec_type;
170 l_curr_wt NUMBER;
171 l_curr_wt_uom VARCHAR2(30);
172 l_curr_uom_unit_price NUMBER:=0;
173 l_log_level NUMBER := fte_freight_pricing_util.G_DBG;
174 l_method_name VARCHAR2(50) := 'analyse_output_for_deficit_wt';
175 BEGIN
176 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
177 fte_freight_pricing_util.reset_dbg_vars;
178 fte_freight_pricing_util.set_method(l_log_level,'analyse_output_for_deficit_wt');
179
180 -- scan for commodities
181 i := g_O_line_tbl.FIRST;
182 IF (i IS NOT NULL) THEN
183 LOOP
184 IF (g_I_line_extras_tbl(g_O_line_tbl(i).line_index).input_set_number = p_set_num) THEN
185 l_category_id := g_I_line_extras_tbl(g_O_line_tbl(i).line_index).category_id;
186 -- convert current line quantity to p_wt_uom
187 IF (g_O_line_tbl(i).priced_uom_code <> p_wt_uom ) THEN
188 l_curr_wt := WSH_WV_UTILS.convert_uom(g_O_line_tbl(i).priced_uom_code,
189 p_wt_uom,
190 g_O_line_tbl(i).priced_quantity,
191 0); -- Within same UOM class
192 l_curr_uom_unit_price := g_O_line_tbl(i).unit_price*(g_O_line_tbl(i).priced_quantity/l_curr_wt);
193
194 ELSE
195 l_curr_wt := g_O_line_tbl(i).priced_quantity;
196 l_curr_uom_unit_price := g_O_line_tbl(i).unit_price;
197 END IF;
198
199 -- now add the category to the commodity table
200 IF (l_comm_tbl.EXISTS(l_category_id)) THEN
201 -- add to existing category row
202 -- Theoretically this should never happen in LTL
203 -- ie. there should not be more than one output line per commodity
204
205 -- The above assumption is not true, because there are loose items --xizhang
206
207 l_comm_tbl(l_category_id).total_wt := l_comm_tbl(l_category_id).total_wt + l_curr_wt;
208
209 -- the next if statement is not really required, because unit price for a commodity will be
210 -- the same within a shipment, most of the time
211 -- this statement makes sure that, if this is not the case, the lowest unit price is captured
212 -- Not required AG 5/13
213 -- As even if this happens in a rate chart
214 -- it would create a wrong picture of deficit wt calculation
215 -- For now, we will attach deficit wt charge to the
216 -- qp output line that had the lowest unit price for selected commodity
217 -- As according to this design even more than one output line per category
218 -- is a problem
219
220 IF (l_curr_uom_unit_price < l_comm_tbl(l_category_id).unit_price ) THEN
221
222 l_comm_tbl(l_category_id).unit_price := l_curr_uom_unit_price;
223 l_comm_tbl(l_category_id).priced_uom := g_O_line_tbl(i).priced_uom_code;
224 l_comm_tbl(l_category_id).output_line_index := g_O_line_tbl(i).line_index;
225 l_comm_tbl(l_category_id).output_line_priced_quantity := l_curr_wt;
226 END IF;
227
228 ELSE
229 -- create new row
230 l_comm_row.category_id := l_category_id;
231
232 -- The unit price returned here does not take into account the uom conversion
233 -- QP returns unit price always in priced_uom_code
234 -- while p_wt_uom can be different from priced_uom_code AG 5/12
235
236 l_comm_row.unit_price := l_curr_uom_unit_price;
237 FTE_FREIGHT_PRICING_UTIL.print_msg(fte_freight_pricing_util.G_DBG,'l_comm_row.unit_price = '||l_comm_row.unit_price);
238 l_comm_row.total_wt := l_curr_wt; -- it is assumed that only wt basis is present
239 l_comm_row.wt_uom := p_wt_uom;
240 l_comm_row.priced_uom := g_O_line_tbl(i).priced_uom_code;
241 l_comm_row.output_line_index := g_O_line_tbl(i).line_index;
242 l_comm_row.output_line_priced_quantity := l_curr_wt;
243 l_comm_tbl(l_category_id) := l_comm_row;
244 END IF;
245
246 END IF; --set num
247
248 EXIT WHEN i >= g_O_line_tbl.LAST;
249 i := g_O_line_tbl.NEXT(i);
250 END LOOP;
251 END IF;
252
253 print_commodity_price_rows(l_comm_tbl);
254
255 x_commodity_price_rows := l_comm_tbl;
256
257 fte_freight_pricing_util.unset_method(l_log_level,'analyse_output_for_deficit_wt');
258 EXCEPTION
259 WHEN others THEN
260 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
261 FTE_FREIGHT_PRICING_UTIL.set_exception(l_method_name,l_log_level,'g_others');
262 FTE_FREIGHT_PRICING_UTIL.print_msg(fte_freight_pricing_util.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
263 fte_freight_pricing_util.unset_method(l_log_level,'analyse_output_for_deficit_wt');
264
265 END analyse_output_for_deficit_wt;
266
267 -- clear qp input line table, line extra table, attribute/qualifier tabl
268 PROCEDURE clear_qp_input(x_return_status OUT NOCOPY VARCHAR2)
269 IS
270 l_log_level NUMBER := fte_freight_pricing_util.G_DBG;
271 l_method_name VARCHAR2(50) := 'clear_qp_input';
272 BEGIN
273 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
274 fte_freight_pricing_util.reset_dbg_vars;
275 fte_freight_pricing_util.set_method(l_log_level,l_method_name);
276
277 g_I_control_rec := null;
278
279 g_I_request_type_code.delete;
280 g_I_line_id.delete;
281 g_I_line_index.delete;
282 g_I_line_type_code.delete;
283 g_I_pricing_effective_date.delete;
284 g_I_active_date_first.delete;
285 g_I_active_date_second.delete;
286 g_I_line_quantity.delete;
287 g_I_line_uom_code.delete;
288 g_I_currency_code.delete;
289 g_I_price_flag.delete;
290 G_I_ACTIVE_DATE_FIRST_TYPE.delete;
291 G_I_ACTIVE_DATE_SECOND_TYPE.delete;
292 G_I_PRICED_QUANTITY.delete;
293 G_I_PRICED_UOM_CODE.delete;
294 G_I_UNIT_PRICE.delete;
295 G_I_PERCENT_PRICE.delete;
296 G_I_UOM_QUANTITY.delete;
297 G_I_ADJUSTED_UNIT_PRICE.delete;
298 G_I_UPD_ADJUSTED_UNIT_PRICE.delete;
299 G_I_PROCESSED_FLAG.delete;
300 G_I_PROCESSING_ORDER.delete;
301 G_I_PRICING_STATUS_CODE.delete;
302 G_I_PRICING_STATUS_TEXT.delete;
303 G_I_ROUNDING_FLAG.delete;
304 G_I_ROUNDING_FACTOR.delete;
305 G_I_QUALIFIERS_EXIST_FLAG.delete;
306 G_I_PRICING_ATTRS_EXIST_FLAG.delete;
307 G_I_PRICE_LIST_ID.delete;
308 G_I_VALIDATED_FLAG.delete;
309 G_I_PRICE_REQUEST_CODE.delete;
310 G_I_USAGE_PRICING_TYPE.delete;
311 G_I_LINE_CATEGORY.delete;
312
313 g_I_line_extras_tbl.delete;
314
315 g_I_A_LINE_INDEX.delete;
316 g_I_A_CONTEXT.delete;
317 g_I_A_ATTRIBUTE_TYPE.delete;
318 g_I_A_ATTRIBUTE.delete;
319 g_I_A_VALUE_FROM.delete;
320 G_I_A_VALIDATED_FLAG.delete;
321 G_I_A_LINE_DETAIL_INDEX.delete;
322 G_I_A_ATTRIBUTE_LEVEL.delete;
323 G_I_A_LIST_HEADER_ID.delete;
324 G_I_A_LIST_LINE_ID.delete;
325 G_I_A_SETUP_VALUE_FROM.delete;
326 G_I_A_VALUE_TO.delete;
327 G_I_A_SETUP_VALUE_TO.delete;
328 G_I_A_GROUPING_NUMBER.delete;
329 G_I_A_NO_QUALIFIERS_IN_GRP.delete;
330 G_I_A_APPLIED_FLAG.delete;
331 G_I_A_PRICING_STATUS_CODE.delete;
332 G_I_A_PRICING_STATUS_TEXT.delete;
333 G_I_A_QUALIFIER_PRECEDENCE.delete;
334 G_I_A_DATATYPE.delete;
335 G_I_A_PRICING_ATTR_FLAG.delete;
336 G_I_A_QUALIFIER_TYPE.delete;
337 G_I_A_PRODUCT_UOM_CODE.delete;
338 G_I_A_EXCLUDER_FLAG.delete;
339 G_I_A_PRICING_PHASE_ID.delete;
340 G_I_A_INCOMPATABILITY_GRP_CODE.delete;
341 G_I_A_LINE_DETAIL_TYPE_CODE.delete;
342 G_I_A_MODIFIER_LEVEL_CODE.delete;
343 G_I_A_PRIMARY_UOM_FLAG.delete;
344
345 fte_freight_pricing_util.unset_method(l_log_level,l_method_name);
346 EXCEPTION
347 WHEN others THEN
348 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
349 FTE_FREIGHT_PRICING_UTIL.set_exception(l_method_name,l_log_level,'g_others');
350 FTE_FREIGHT_PRICING_UTIL.print_msg(fte_freight_pricing_util.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
351 fte_freight_pricing_util.unset_method(l_log_level,l_method_name);
352 END clear_qp_input;
353
354 -- delete from qp input line table, line extra table, attribute/qualifier table
355 PROCEDURE delete_line_from_qp_input(
356 p_line_index IN NUMBER,
357 x_return_status OUT NOCOPY VARCHAR2)
358 IS
359 i NUMBER;
360 j NUMBER;
361 l_return_status VARCHAR2(1);
362 l_log_level NUMBER := fte_freight_pricing_util.G_DBG;
363 l_method_name VARCHAR2(50) := 'delete_line_from_qp_input';
364 BEGIN
365 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
366 fte_freight_pricing_util.reset_dbg_vars;
367 fte_freight_pricing_util.set_method(l_log_level,l_method_name);
368
369 fte_freight_pricing_util.print_msg(l_log_level,'p_line_index = '||p_line_index);
370
371 i := p_line_index;
372 fte_freight_pricing_util.print_msg(l_log_level,'delete line where i = '||i);
373
374 g_I_request_type_code.delete(i);
375 g_I_line_id.delete(i);
376 g_I_line_index.delete(i);
377 g_I_line_type_code.delete(i);
378 g_I_pricing_effective_date.delete(i);
379 g_I_active_date_first.delete(i);
380 g_I_active_date_second.delete(i);
381 g_I_line_quantity.delete(i);
382 g_I_line_uom_code.delete(i);
383 g_I_currency_code.delete(i);
384 g_I_price_flag.delete(i);
385 G_I_ACTIVE_DATE_FIRST_TYPE.delete(i);
386 G_I_ACTIVE_DATE_SECOND_TYPE.delete(i);
387 G_I_PRICED_QUANTITY.delete(i);
388 G_I_PRICED_UOM_CODE.delete(i);
389 G_I_UNIT_PRICE.delete(i);
390 G_I_PERCENT_PRICE.delete(i);
391 G_I_UOM_QUANTITY.delete(i);
392 G_I_ADJUSTED_UNIT_PRICE.delete(i);
393 G_I_UPD_ADJUSTED_UNIT_PRICE.delete(i);
394 G_I_PROCESSED_FLAG.delete(i);
395 G_I_PROCESSING_ORDER.delete(i);
396 G_I_PRICING_STATUS_CODE.delete(i);
397 G_I_PRICING_STATUS_TEXT.delete(i);
398 G_I_ROUNDING_FLAG.delete(i);
399 G_I_ROUNDING_FACTOR.delete(i);
400 G_I_QUALIFIERS_EXIST_FLAG.delete(i);
401 G_I_PRICING_ATTRS_EXIST_FLAG.delete(i);
402 G_I_PRICE_LIST_ID.delete(i);
403 G_I_VALIDATED_FLAG.delete(i);
404 G_I_PRICE_REQUEST_CODE.delete(i);
405 G_I_USAGE_PRICING_TYPE.delete(i);
406 G_I_LINE_CATEGORY.delete(i);
407
408 g_I_line_extras_tbl.delete(i);
409
410 j := g_I_A_LINE_INDEX.FIRST;
411 if (j is not null) then
412 loop
413 if (g_I_A_LINE_INDEX(j) = i) then
414
415 fte_freight_pricing_util.print_msg(l_log_level,'delete line attr where j = '||j);
416 g_I_A_LINE_INDEX.delete(j);
417 g_I_A_CONTEXT.delete(j);
418 g_I_A_ATTRIBUTE_TYPE.delete(j);
419 g_I_A_ATTRIBUTE.delete(j);
420 g_I_A_VALUE_FROM.delete(j);
421 G_I_A_VALIDATED_FLAG.delete(j);
422 G_I_A_LINE_DETAIL_INDEX.delete(j);
423 G_I_A_ATTRIBUTE_LEVEL.delete(j);
424 G_I_A_LIST_HEADER_ID.delete(j);
425 G_I_A_LIST_LINE_ID.delete(j);
426 G_I_A_SETUP_VALUE_FROM.delete(j);
427 G_I_A_VALUE_TO.delete(j);
428 G_I_A_SETUP_VALUE_TO.delete(j);
429 G_I_A_GROUPING_NUMBER.delete(j);
430 G_I_A_NO_QUALIFIERS_IN_GRP.delete(j);
431 G_I_A_APPLIED_FLAG.delete(j);
432 G_I_A_PRICING_STATUS_CODE.delete(j);
433 G_I_A_PRICING_STATUS_TEXT.delete(j);
434 G_I_A_QUALIFIER_PRECEDENCE.delete(j);
435 G_I_A_DATATYPE.delete(j);
436 G_I_A_PRICING_ATTR_FLAG.delete(j);
437 G_I_A_QUALIFIER_TYPE.delete(j);
438 G_I_A_PRODUCT_UOM_CODE.delete(j);
439 G_I_A_EXCLUDER_FLAG.delete(j);
440 G_I_A_PRICING_PHASE_ID.delete(j);
441 G_I_A_INCOMPATABILITY_GRP_CODE.delete(j);
442 G_I_A_LINE_DETAIL_TYPE_CODE.delete(j);
443 G_I_A_MODIFIER_LEVEL_CODE.delete(j);
444 G_I_A_PRIMARY_UOM_FLAG.delete(j);
445
446 end if;
447 exit when j >= g_I_A_LINE_INDEX.LAST;
448 j := g_I_A_LINE_INDEX.NEXT(j);
449 end loop;
450 end if;
451
452 fte_freight_pricing_util.unset_method(l_log_level,l_method_name);
453 EXCEPTION
454 WHEN others THEN
455 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
456 FTE_FREIGHT_PRICING_UTIL.set_exception(l_method_name,l_log_level,'g_others');
457 FTE_FREIGHT_PRICING_UTIL.print_msg(fte_freight_pricing_util.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
458 fte_freight_pricing_util.unset_method(l_log_level,l_method_name);
459
460 END delete_line_from_qp_input;
461
462 -- delete from qp output line table, line detail table
463 PROCEDURE delete_line_from_qp_output(
464 p_line_index IN NUMBER,
465 x_return_status OUT NOCOPY VARCHAR2)
466 IS
467 j NUMBER;
468 l_return_status VARCHAR2(1);
469 l_log_level NUMBER := fte_freight_pricing_util.G_DBG;
470 l_method_name VARCHAR2(50) := 'delete_line_from_qp_output';
471 BEGIN
472 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
473 fte_freight_pricing_util.reset_dbg_vars;
474 fte_freight_pricing_util.set_method(l_log_level,l_method_name);
475
476 fte_freight_pricing_util.print_msg(l_log_level,'p_line_index = '||p_line_index);
477
478 g_O_line_tbl.delete(p_line_index);
479 fte_freight_pricing_util.print_msg(l_log_level,'delete line where i = '||p_line_index);
480
481 j := g_O_line_detail_tbl.FIRST;
482 if (j is not null) then
483 loop
484 if (g_O_line_detail_tbl(j).line_index = p_line_index) then
485
486 fte_freight_pricing_util.print_msg(l_log_level,'delete line attr where j = '||j);
487
488 g_O_line_detail_tbl.delete(j);
489 end if;
490 exit when j >= g_O_line_detail_tbl.LAST;
491 j := g_O_line_detail_tbl.NEXT(j);
492 end loop;
493 end if;
494
495 fte_freight_pricing_util.unset_method(l_log_level,l_method_name);
496 EXCEPTION
497 WHEN others THEN
498 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
499 FTE_FREIGHT_PRICING_UTIL.set_exception(l_method_name,l_log_level,'g_others');
500 FTE_FREIGHT_PRICING_UTIL.print_msg(fte_freight_pricing_util.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
501 fte_freight_pricing_util.unset_method(l_log_level,l_method_name);
502 END delete_line_from_qp_output;
503
504 -- delete a set from the input and output lines for event 1
505 PROCEDURE delete_set_from_line_event(p_set_num IN NUMBER,
506 x_return_status OUT NOCOPY VARCHAR2)
507 IS
508 i NUMBER :=0;
509 l_return_status VARCHAR2(1);
510 l_log_level NUMBER := fte_freight_pricing_util.G_DBG;
511 l_method_name VARCHAR2(50) := 'delete_set_from_line_event';
512 BEGIN
513 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
514 fte_freight_pricing_util.reset_dbg_vars;
515 fte_freight_pricing_util.set_method(l_log_level,'delete_set_from_line_event');
516
517 FTE_FREIGHT_PRICING_UTIL.print_msg(fte_freight_pricing_util.G_DBG,'p_set_num ='||p_set_num);
518
519 --delete input lines, output lines, attributes and qualifiers
520 i := g_I_LINE_INDEX.FIRST;
521 if (i is not null) then
522 loop
523 FTE_FREIGHT_PRICING_UTIL.print_msg(fte_freight_pricing_util.G_DBG,'i = '||i);
524 FTE_FREIGHT_PRICING_UTIL.print_msg(fte_freight_pricing_util.G_DBG,'line_index = '||g_I_line_index(i));
525 FTE_FREIGHT_PRICING_UTIL.print_msg(fte_freight_pricing_util.G_DBG,'input_set_num ='||g_I_line_extras_tbl(g_I_line_index(i)).input_set_number);
526 IF (g_I_line_extras_tbl(g_I_line_index(i)).input_set_number = p_set_num) THEN
527 delete_line_from_qp_input(
528 p_line_index => i,
529 x_return_status => l_return_status);
530
531 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
532 and l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
533 raise FTE_FREIGHT_PRICING_UTIL.g_delete_set_failed;
534 END IF;
535
536 delete_line_from_qp_output(
537 p_line_index => i,
538 x_return_status => l_return_status);
539
540 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
541 and l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
542 raise FTE_FREIGHT_PRICING_UTIL.g_delete_set_failed;
543 END IF;
544
545 END IF;
546 exit when i>= g_I_LINE_INDEX.LAST;
547 i := g_I_LINE_INDEX.NEXT(i);
548 end loop;
549 end if;
550
551 fte_freight_pricing_util.unset_method(l_log_level,'delete_set_from_line_event');
552 EXCEPTION
553 WHEN FTE_FREIGHT_PRICING_UTIL.g_delete_set_failed THEN
554 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
555 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'delete_set_failed');
556 fte_freight_pricing_util.unset_method(l_log_level,'delete_set_from_line_event');
557 WHEN others THEN
558 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
559 FTE_FREIGHT_PRICING_UTIL.set_exception(l_method_name,l_log_level,'g_others');
560 FTE_FREIGHT_PRICING_UTIL.print_msg(fte_freight_pricing_util.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
561 fte_freight_pricing_util.unset_method(l_log_level,'delete_set_from_line_event');
562 END delete_set_from_line_event;
563
564
565 -- delete from event tables for the specified line_index
566 PROCEDURE delete_lines(p_line_index IN NUMBER,
567 x_qp_output_line_rows IN OUT NOCOPY QP_PREQ_GRP.LINE_TBL_TYPE,
568 x_qp_output_detail_rows IN OUT NOCOPY QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE,
569 x_return_status OUT NOCOPY VARCHAR2)
570 IS
571 i NUMBER;
572 l_return_status VARCHAR2(1);
573 l_log_level NUMBER := fte_freight_pricing_util.G_DBG;
574 l_method_name VARCHAR2(50) := 'delete_lines';
575 BEGIN
576
577 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
578 fte_freight_pricing_util.reset_dbg_vars;
579 fte_freight_pricing_util.set_method(l_log_level,'delete_lines');
580
581 delete_line_from_qp_input(
582 p_line_index => p_line_index,
583 x_return_status => l_return_status);
584
585 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
586 and l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
587 raise FTE_FREIGHT_PRICING_UTIL.g_delete_qpline_failed;
588 END IF;
589
590 delete_line_from_qp_output(
591 p_line_index => p_line_index,
592 x_return_status => l_return_status);
593
594 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
595 and l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
596 raise FTE_FREIGHT_PRICING_UTIL.g_delete_qpline_failed;
597 END IF;
598
599 x_qp_output_line_rows := g_O_line_tbl;
600 x_qp_output_detail_rows := g_O_line_detail_tbl;
601
602 fte_freight_pricing_util.unset_method(l_log_level,'delete_lines');
603 EXCEPTION
604 WHEN FTE_FREIGHT_PRICING_UTIL.g_delete_qpline_failed THEN
605 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
606 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'delete_qpline_failed');
607 fte_freight_pricing_util.unset_method(l_log_level,'delete_lines');
608 WHEN others THEN
609 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
610 FTE_FREIGHT_PRICING_UTIL.set_exception(l_method_name,l_log_level,'g_others');
611 FTE_FREIGHT_PRICING_UTIL.print_msg(fte_freight_pricing_util.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
612 fte_freight_pricing_util.unset_method(l_log_level,'delete_lines');
613
614 END delete_lines;
615
616 PROCEDURE move_line_row(
617 p_from_index IN NUMBER,
618 p_to_index NUMBER)
619 IS
620 l_log_level NUMBER := fte_freight_pricing_util.G_DBG;
621 BEGIN
622 fte_freight_pricing_util.print_msg(l_log_level,'move qp input line from '||p_from_index ||' to '||p_to_index);
623
624 G_I_LINE_INDEX(p_to_index) := G_I_LINE_INDEX(p_from_index);
625 G_I_LINE_TYPE_CODE(p_to_index) := G_I_LINE_TYPE_CODE(p_from_index);
626 G_I_PRICING_EFFECTIVE_DATE(p_to_index) := G_I_PRICING_EFFECTIVE_DATE(p_from_index);
627 G_I_ACTIVE_DATE_FIRST(p_to_index) := G_I_ACTIVE_DATE_FIRST(p_from_index);
628 G_I_ACTIVE_DATE_FIRST_TYPE(p_to_index) := G_I_ACTIVE_DATE_FIRST_TYPE(p_from_index);
629 G_I_ACTIVE_DATE_SECOND(p_to_index) := G_I_ACTIVE_DATE_SECOND(p_from_index);
630 G_I_ACTIVE_DATE_SECOND_TYPE(p_to_index) := G_I_ACTIVE_DATE_SECOND_TYPE(p_from_index);
631 G_I_LINE_QUANTITY(p_to_index) := G_I_LINE_QUANTITY(p_from_index);
632 G_I_LINE_UOM_CODE(p_to_index) := G_I_LINE_UOM_CODE(p_from_index);
633 G_I_REQUEST_TYPE_CODE(p_to_index) := G_I_REQUEST_TYPE_CODE(p_from_index);
634 G_I_PRICED_QUANTITY(p_to_index) := G_I_PRICED_QUANTITY(p_from_index);
635 G_I_PRICED_UOM_CODE(p_to_index) := G_I_PRICED_UOM_CODE(p_from_index);
636 G_I_CURRENCY_CODE(p_to_index) := G_I_CURRENCY_CODE(p_from_index);
637 G_I_UNIT_PRICE(p_to_index) := G_I_UNIT_PRICE(p_from_index);
638 G_I_PERCENT_PRICE(p_to_index) := G_I_PERCENT_PRICE(p_from_index);
639 G_I_UOM_QUANTITY(p_to_index) := G_I_UOM_QUANTITY(p_from_index);
640 G_I_ADJUSTED_UNIT_PRICE(p_to_index) := G_I_ADJUSTED_UNIT_PRICE(p_from_index);
641 G_I_UPD_ADJUSTED_UNIT_PRICE(p_to_index) := G_I_UPD_ADJUSTED_UNIT_PRICE(p_from_index);
642 G_I_PROCESSED_FLAG(p_to_index) := G_I_PROCESSED_FLAG(p_from_index);
643 G_I_PRICE_FLAG(p_to_index) := G_I_PRICE_FLAG(p_from_index);
644 G_I_LINE_ID(p_to_index) := G_I_LINE_ID(p_from_index);
645 G_I_PROCESSING_ORDER(p_to_index) := G_I_PROCESSING_ORDER(p_from_index);
646 G_I_PRICING_STATUS_CODE(p_to_index) := G_I_PRICING_STATUS_CODE(p_from_index);
647 G_I_PRICING_STATUS_TEXT(p_to_index) := G_I_PRICING_STATUS_TEXT(p_from_index);
648 G_I_ROUNDING_FLAG(p_to_index) := G_I_ROUNDING_FLAG(p_from_index);
649 G_I_ROUNDING_FACTOR(p_to_index) := G_I_ROUNDING_FACTOR(p_from_index);
650 G_I_QUALIFIERS_EXIST_FLAG(p_to_index) := G_I_QUALIFIERS_EXIST_FLAG(p_from_index);
651 G_I_PRICING_ATTRS_EXIST_FLAG(p_to_index) := G_I_PRICING_ATTRS_EXIST_FLAG(p_from_index);
652 G_I_PRICE_LIST_ID(p_to_index) := G_I_PRICE_LIST_ID(p_from_index);
653 G_I_VALIDATED_FLAG(p_to_index) := G_I_VALIDATED_FLAG(p_from_index);
654 G_I_PRICE_REQUEST_CODE(p_to_index) := G_I_PRICE_REQUEST_CODE(p_from_index);
655 G_I_USAGE_PRICING_TYPE(p_to_index) := G_I_USAGE_PRICING_TYPE(p_from_index);
656 G_I_LINE_CATEGORY(p_to_index) := G_I_LINE_CATEGORY(p_from_index);
657
658 g_I_line_extras_tbl(p_to_index) := g_I_line_extras_tbl(p_from_index);
659
660 END move_line_row;
661
662 PROCEDURE move_attribute_row(
663 p_from_index IN NUMBER,
664 p_to_index NUMBER)
665 IS
666 l_log_level NUMBER := fte_freight_pricing_util.G_DBG;
667 BEGIN
668 fte_freight_pricing_util.print_msg(l_log_level,'move qp input attribute from '||p_from_index ||' to '||p_to_index);
669 G_I_A_LINE_INDEX(p_to_index) := G_I_A_LINE_INDEX(p_from_index);
670 G_I_A_LINE_DETAIL_INDEX(p_to_index) := G_I_A_LINE_DETAIL_INDEX(p_from_index);
671 G_I_A_ATTRIBUTE_LEVEL(p_to_index) := G_I_A_ATTRIBUTE_LEVEL(p_from_index);
672 G_I_A_ATTRIBUTE_TYPE(p_to_index) := G_I_A_ATTRIBUTE_TYPE(p_from_index);
673 G_I_A_LIST_HEADER_ID(p_to_index) := G_I_A_LIST_HEADER_ID(p_from_index);
674 G_I_A_LIST_LINE_ID(p_to_index) := G_I_A_LIST_LINE_ID(p_from_index);
675 G_I_A_CONTEXT(p_to_index) := G_I_A_CONTEXT(p_from_index);
676 G_I_A_ATTRIBUTE(p_to_index) := G_I_A_ATTRIBUTE(p_from_index);
677 G_I_A_VALUE_FROM(p_to_index) := G_I_A_VALUE_FROM(p_from_index);
678 G_I_A_SETUP_VALUE_FROM(p_to_index) := G_I_A_SETUP_VALUE_FROM(p_from_index);
679 G_I_A_VALUE_TO(p_to_index) := G_I_A_VALUE_TO(p_from_index);
680 G_I_A_SETUP_VALUE_TO(p_to_index) := G_I_A_SETUP_VALUE_TO(p_from_index);
681 G_I_A_GROUPING_NUMBER(p_to_index) := G_I_A_GROUPING_NUMBER(p_from_index);
682 G_I_A_NO_QUALIFIERS_IN_GRP(p_to_index) := G_I_A_NO_QUALIFIERS_IN_GRP(p_from_index);
683 G_I_A_COMPARISON_OPERATOR_TYPE(p_to_index) := G_I_A_COMPARISON_OPERATOR_TYPE(p_from_index);
684 G_I_A_VALIDATED_FLAG(p_to_index) := G_I_A_VALIDATED_FLAG(p_from_index);
685 G_I_A_APPLIED_FLAG(p_to_index) := G_I_A_APPLIED_FLAG(p_from_index);
686 G_I_A_PRICING_STATUS_CODE(p_to_index) := G_I_A_PRICING_STATUS_CODE(p_from_index);
687 G_I_A_PRICING_STATUS_TEXT(p_to_index) := G_I_A_PRICING_STATUS_TEXT(p_from_index);
688 G_I_A_QUALIFIER_PRECEDENCE(p_to_index) := G_I_A_QUALIFIER_PRECEDENCE(p_from_index);
689 G_I_A_DATATYPE(p_to_index) := G_I_A_DATATYPE(p_from_index);
690 G_I_A_PRICING_ATTR_FLAG(p_to_index) := G_I_A_PRICING_ATTR_FLAG(p_from_index);
691 G_I_A_QUALIFIER_TYPE(p_to_index) := G_I_A_QUALIFIER_TYPE(p_from_index);
692 G_I_A_PRODUCT_UOM_CODE(p_to_index) := G_I_A_PRODUCT_UOM_CODE(p_from_index);
693 G_I_A_EXCLUDER_FLAG(p_to_index) := G_I_A_EXCLUDER_FLAG(p_from_index);
694 G_I_A_PRICING_PHASE_ID(p_to_index) := G_I_A_PRICING_PHASE_ID(p_from_index);
695 G_I_A_INCOMPATABILITY_GRP_CODE(p_to_index) := G_I_A_INCOMPATABILITY_GRP_CODE(p_from_index);
696 G_I_A_LINE_DETAIL_TYPE_CODE(p_to_index) := G_I_A_LINE_DETAIL_TYPE_CODE(p_from_index);
697 G_I_A_MODIFIER_LEVEL_CODE(p_to_index) := G_I_A_MODIFIER_LEVEL_CODE(p_from_index);
698 G_I_A_PRIMARY_UOM_FLAG(p_to_index) := G_I_A_PRIMARY_UOM_FLAG(p_from_index);
699
700 END move_attribute_row;
701
702 -- removes gaps in qp input lines and attribtues
703 -- because qp bulk inserts requrie that elements in the input tables has to be consecutive
704 PROCEDURE remove_gaps(
705 x_return_status OUT NOCOPY VARCHAR2)
706 IS
707 l_return_status VARCHAR2(1);
708 l_log_level NUMBER := fte_freight_pricing_util.G_DBG;
709 l_method_name VARCHAR2(50) := 'remove_gaps';
710
711 l_count NUMBER;
712 l_first_index NUMBER;
713 l_old_last_index NUMBER;
714 i NUMBER;
715 l_current_index NUMBER;
716 BEGIN
717 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
718 fte_freight_pricing_util.reset_dbg_vars;
719 fte_freight_pricing_util.set_method(l_log_level,l_method_name);
720
721 -- work on the lines first
722 -- then work on the line attribtues
723
724 l_first_index := G_I_LINE_INDEX.first;
725 l_old_last_index := G_I_LINE_INDEX.last;
726 l_count := G_I_LINE_INDEX.count;
727
728 if (l_count = 0) then
729 fte_freight_pricing_util.print_msg(l_log_level,'qp input line table is empty, do nothing');
730 else
731 if (l_count = (l_old_last_index - l_first_index + 1)) then
732 fte_freight_pricing_util.print_msg(l_log_level,'qp input line table no gaps, do nothing');
733 else
734
735 l_current_index := l_first_index;
736 for i in l_first_index..l_old_last_index loop
737 if (G_I_LINE_INDEX.exists(i)) then
738 if (i > l_current_index) then
739 move_line_row(p_from_index =>i, p_to_index =>l_current_index);
740 end if;
741 l_current_index := l_current_index + 1;
742 end if;
743 end loop;
744
745 for i in l_current_index..l_old_last_index loop
746 if (G_I_LINE_INDEX.exists(i)) then
747 G_I_LINE_INDEX.delete(i);
748 g_I_line_extras_tbl.delete(i);
749 end if;
750 end loop;
751
752 end if;
753 end if;
754
755 l_first_index := G_I_A_LINE_INDEX.first;
756 l_old_last_index := G_I_A_LINE_INDEX.last;
757 l_count := G_I_A_LINE_INDEX.count;
758
759 if (l_count = 0) then
760 fte_freight_pricing_util.print_msg(l_log_level,'qp input attribute table is empty, do nothing');
761 else
762 if (l_count = (l_old_last_index - l_first_index + 1)) then
763 fte_freight_pricing_util.print_msg(l_log_level,'qp input attribute table no gaps, do nothing');
764 else
765
766 l_current_index := l_first_index;
767 for i in l_first_index..l_old_last_index loop
768 if (G_I_A_LINE_INDEX.exists(i)) then
769 if (i > l_current_index) then
770 move_attribute_row(p_from_index =>i, p_to_index =>l_current_index);
771 end if;
772 l_current_index := l_current_index + 1;
773 end if;
774 end loop;
775
776 for i in l_current_index..l_old_last_index loop
777 if (G_I_A_LINE_INDEX.exists(i)) then
778 G_I_A_LINE_INDEX.delete(i);
779 end if;
780 end loop;
781
782 end if;
783 end if;
784
785 fte_freight_pricing_util.unset_method(l_log_level,l_method_name);
786 EXCEPTION
787 WHEN others THEN
788 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
789 FTE_FREIGHT_PRICING_UTIL.set_exception(l_method_name,l_log_level,'g_others');
790 FTE_FREIGHT_PRICING_UTIL.print_msg(fte_freight_pricing_util.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
791 fte_freight_pricing_util.unset_method(l_log_level,l_method_name);
792 END remove_gaps;
793
794 -- copies input lines of one event to the input of another event
795 -- the base prices from the source event are carried over to the input of the target event
796 -- currently it will copy only from event 1 to event 2
797 PROCEDURE prepare_next_event_request ( x_return_status OUT NOCOPY VARCHAR2)
798 IS
799 i NUMBER;
800 j NUMBER;
801 l_return_status VARCHAR2(1);
802 l_log_level NUMBER := fte_freight_pricing_util.G_DBG;
803 l_method_name VARCHAR2(50) := 'prepare_next_event_request';
804 BEGIN
805 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
806 fte_freight_pricing_util.reset_dbg_vars;
807 fte_freight_pricing_util.set_method(l_log_level,'prepare_next_event_request');
808
809 --modify event 1 input based on event 1 output to make it event 2 input
810
811 g_I_control_rec.pricing_event := g_engine_defaults_tab(G_CHARGE_EVENT_NUM).pricing_event_code;
812
813 i := g_I_line_index.FIRST;
814 IF (i IS NOT NULL) THEN
815 LOOP
816 fte_freight_pricing_util.print_msg(l_log_level,'copy line from O to I where i = '||i);
817
818 --copy over unit price and other fields from output
819 g_I_request_type_code(i) := g_engine_defaults_tab(G_CHARGE_EVENT_NUM).request_type_code ;
820 g_I_line_type_code(i) := g_engine_defaults_tab(G_CHARGE_EVENT_NUM).line_type_code;
821 g_I_price_flag(i) := g_engine_defaults_tab(G_CHARGE_EVENT_NUM).price_flag;
822 g_I_unit_price(i) := g_O_line_tbl(i).unit_price;
823 g_I_priced_quantity(i) := g_O_line_tbl(i).priced_quantity;
824 g_I_priced_uom_code(i) := g_O_line_tbl(i).priced_uom_code;
825
826 EXIT WHEN i >= g_I_line_index.LAST;
827 i := g_I_line_index.NEXT(i);
828 END LOOP;
829 END IF;
830
831 remove_gaps(x_return_status => l_return_status);
832 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
833 and l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
834 raise FTE_FREIGHT_PRICING_UTIL.g_remove_gaps_failed;
835 END IF;
836
837 fte_freight_pricing_util.unset_method(l_log_level,'prepare_next_event_request');
838 EXCEPTION
839 WHEN FTE_FREIGHT_PRICING_UTIL.g_remove_gaps_failed THEN
840 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
841 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'remove_gaps_failed');
842 fte_freight_pricing_util.unset_method(l_log_level,l_method_name);
843 WHEN others THEN
844 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
845 FTE_FREIGHT_PRICING_UTIL.set_exception(l_method_name,l_log_level,'g_others');
846 FTE_FREIGHT_PRICING_UTIL.print_msg(fte_freight_pricing_util.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
847 fte_freight_pricing_util.unset_method(l_log_level,'prepare_next_event_request');
848
849 END prepare_next_event_request;
850
851
852 -- we need procedures to :
853 -- create the control record
854 -- create line record
855 -- create attribute record
856 -- create qualifier record
857
858 -- creates a control record based on defaults and adds it to the input table for the event
859 PROCEDURE create_control_record (p_event_num IN NUMBER,
860 x_return_status OUT NOCOPY VARCHAR2) IS
861 l_control_rec QP_PREQ_GRP.CONTROL_RECORD_TYPE;
862 l_log_level NUMBER := fte_freight_pricing_util.G_DBG;
863 l_method_name VARCHAR2(50) := 'create_control_record';
864 BEGIN
865
866 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
867 fte_freight_pricing_util.reset_dbg_vars;
868 fte_freight_pricing_util.set_method(l_log_level,'create_control_record');
869
870 l_control_rec.pricing_event := g_engine_defaults_tab(p_event_num).pricing_event_code;
871 l_control_rec.calculate_flag := 'Y';
872 l_control_rec.simulation_flag := 'N';
873 l_control_rec.rounding_flag := 'N';
874 l_control_rec.temp_table_insert_flag := 'N';
875 -- l_control_rec.request_type_code := 'FTE';
876 l_control_rec.request_type_code := g_engine_defaults_tab(p_event_num).request_type_code;
877
878 g_I_control_rec := l_control_rec;
879
880 fte_freight_pricing_util.unset_method(l_log_level,'create_control_record');
881 EXCEPTION
882 WHEN others THEN
883 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
884 FTE_FREIGHT_PRICING_UTIL.set_exception(l_method_name,l_log_level,'g_others');
885 FTE_FREIGHT_PRICING_UTIL.print_msg(fte_freight_pricing_util.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
886 fte_freight_pricing_util.unset_method(l_log_level,'create_control_record');
887 END create_control_record;
888
889 -- creates a single line record and adds it to the event input table
890 PROCEDURE create_line_record (p_pricing_control_rec IN fte_freight_pricing.pricing_control_input_rec_type,
891 p_pricing_engine_input_rec IN fte_freight_pricing.pricing_engine_input_rec_type,
892 x_return_status OUT NOCOPY VARCHAR2) IS
893
894 line_extras line_extras_rec;
895 l_event_num NUMBER;
896 i NUMBER;
897 l_log_level NUMBER := fte_freight_pricing_util.G_DBG;
898 l_method_name VARCHAR2(50) := 'create_line_record';
899 BEGIN
900
901 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
902 fte_freight_pricing_util.reset_dbg_vars;
903 fte_freight_pricing_util.set_method(l_log_level,'create_line_record');
904
905 l_event_num := p_pricing_control_rec.pricing_event_num;
906
907 i := p_pricing_engine_input_rec.input_index;
908
909 g_I_request_type_code(i) := g_engine_defaults_tab(l_event_num).request_type_code;
910 g_I_line_id(i) := p_pricing_control_rec.lane_id;
911 g_I_line_index(i) := to_char(p_pricing_engine_input_rec.input_index);
912 g_I_line_type_code(i) := g_engine_defaults_tab(l_event_num).line_type_code;
913 g_I_pricing_effective_date(i) := nvl(fte_freight_pricing.g_effectivity_dates.date_from,sysdate);
914 g_I_active_date_first(i) := nvl(fte_freight_pricing.g_effectivity_dates.date_from,sysdate);
915 g_I_active_date_second(i) := nvl(fte_freight_pricing.g_effectivity_dates.date_to,sysdate);
916 g_I_line_quantity(i) := to_char(p_pricing_engine_input_rec.line_quantity);
917 g_I_line_uom_code(i) := p_pricing_engine_input_rec.line_uom;
918 g_I_currency_code(i) := NVL(p_pricing_control_rec.currency_code,'USD'); --nvl for DEBUG ONLY
919 g_I_price_flag(i) := g_engine_defaults_tab(l_event_num).price_flag;
920
921 --following are default values for price request line
922 G_I_ACTIVE_DATE_FIRST_TYPE(i) := 'NO TYPE';
923 G_I_ACTIVE_DATE_SECOND_TYPE(i) := 'NO TYPE';
924 G_I_PRICED_QUANTITY(i) := null;
925 G_I_PRICED_UOM_CODE(i) := null;
926 G_I_UNIT_PRICE(i) := null;
927 G_I_PERCENT_PRICE(i) := null;
928 G_I_UOM_QUANTITY(i) := null;
929 G_I_ADJUSTED_UNIT_PRICE(i) := null;
930 G_I_UPD_ADJUSTED_UNIT_PRICE(i) := null;
931 G_I_PROCESSED_FLAG(i) := null;
932 G_I_PROCESSING_ORDER(i) := null;
933 G_I_PRICING_STATUS_CODE(i) := QP_PREQ_GRP.G_STATUS_UNCHANGED;
934 G_I_PRICING_STATUS_TEXT(i) := null;
935 G_I_ROUNDING_FLAG(i) := null;
936 G_I_ROUNDING_FACTOR(i) := null;
937 G_I_QUALIFIERS_EXIST_FLAG(i) := 'N';
938 G_I_PRICING_ATTRS_EXIST_FLAG(i) := 'N';
939 G_I_PRICE_LIST_ID(i) := -9999;
940 G_I_VALIDATED_FLAG(i) := 'N';
941 G_I_PRICE_REQUEST_CODE(i) := null;
942 G_I_USAGE_PRICING_TYPE(i) := QP_PREQ_GRP.G_REGULAR_USAGE_TYPE;
943 G_I_LINE_CATEGORY(i) := null;
944
945 line_extras.line_index := p_pricing_engine_input_rec.input_index ;
946 line_extras.input_set_number := p_pricing_engine_input_rec.input_set_number;
947 line_extras.category_id := p_pricing_engine_input_rec.category_id; -- note this will have value only for WITHIN
948
949 g_I_line_extras_tbl(i) := line_extras;
950
951 fte_freight_pricing_util.unset_method(l_log_level,'create_line_record');
952 EXCEPTION
953 WHEN others THEN
954 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
955 FTE_FREIGHT_PRICING_UTIL.set_exception(l_method_name,l_log_level,'g_others');
956 FTE_FREIGHT_PRICING_UTIL.print_msg(fte_freight_pricing_util.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
957 fte_freight_pricing_util.unset_method(l_log_level,'create_line_record');
958 END create_line_record;
959
960 -- creates a single qualifier record and adds it to the appropriate i/p table
961 PROCEDURE create_qual_record (p_event_num IN NUMBER,
962 p_qual_rec IN qualifier_rec_type,
963 x_return_status OUT NOCOPY VARCHAR2) IS
964
965 j NUMBER;
966
967 l_log_level NUMBER := fte_freight_pricing_util.G_DBG;
968 l_method_name VARCHAR2(50) := 'create_qual_record';
969 BEGIN
970
971 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
972 fte_freight_pricing_util.reset_dbg_vars;
973 -- fte_freight_pricing_util.set_method(l_log_level,'create_qual_record');
974 -- fte_freight_pricing_util.print_msg(l_log_level,'p_qual_rec.input_index='||p_qual_rec.input_index);
975 -- fte_freight_pricing_util.print_msg(l_log_level,'p_qual_rec.qualifier_name='||p_qual_rec.qualifier_name);
976
977 j := g_I_A_LINE_INDEX.COUNT + 1;
978 -- fte_freight_pricing_util.print_msg(l_log_level,'p_qual_rec.j='||j);
979
980 g_I_A_LINE_INDEX(j) := p_qual_rec.input_index;
981 IF (p_qual_rec.qualifier_name = 'PRICELIST') THEN
982 g_I_A_CONTEXT(j) :='MODLIST';
983 g_I_A_ATTRIBUTE(j) :='QUALIFIER_ATTRIBUTE4';
984 ELSIF (p_qual_rec.qualifier_name = 'SUPPLIER') THEN
985 g_I_A_CONTEXT(j) :='PARTY';
986 g_I_A_ATTRIBUTE(j) :='QUALIFIER_ATTRIBUTE1';
987 ELSIF (p_qual_rec.qualifier_name = 'MODE_OF_TRANSPORT') THEN
988 g_I_A_CONTEXT(j) :=fte_rtg_globals.G_QX_MODE_OF_TRANSPORT;
989 g_I_A_ATTRIBUTE(j) :=fte_rtg_globals.G_Q_MODE_OF_TRANSPORT;
990 ELSIF (p_qual_rec.qualifier_name = 'SERVICE_TYPE') THEN
991 g_I_A_CONTEXT(j) :=fte_rtg_globals.G_QX_SERVICE_TYPE;
992 g_I_A_ATTRIBUTE(j) :=fte_rtg_globals.G_Q_SERVICE_TYPE;
993 END IF;
994 g_I_A_VALUE_FROM(j) := p_qual_rec.qualifier_value;
995 g_I_A_COMPARISON_OPERATOR_TYPE(j) := p_qual_rec.operator;
996
997 fte_freight_pricing_util.print_msg(l_log_level,
998 'inpidx='||p_qual_rec.input_index||' j='||j||'name='||p_qual_rec.qualifier_name||' qualval='||p_qual_rec.qualifier_value);
999
1000 -- default values in attributes
1001 G_I_A_COMPARISON_OPERATOR_TYPE(j) := '=';
1002 G_I_A_VALIDATED_FLAG(j) :='N';
1003 G_I_A_LINE_DETAIL_INDEX(j) := null;
1004 G_I_A_ATTRIBUTE_LEVEL(j) := QP_PREQ_GRP.G_LINE_LEVEL;
1005 G_I_A_LIST_HEADER_ID(j) := null;
1006 G_I_A_LIST_LINE_ID(j) := null;
1007 G_I_A_SETUP_VALUE_FROM(j) := null;
1008 G_I_A_VALUE_TO(j) := null;
1009 G_I_A_SETUP_VALUE_TO(j) := null;
1010 G_I_A_GROUPING_NUMBER(j) := null;
1011 G_I_A_NO_QUALIFIERS_IN_GRP(j) := null;
1012 G_I_A_APPLIED_FLAG(j) := null;
1013 G_I_A_PRICING_STATUS_CODE(j) := QP_PREQ_GRP.G_STATUS_UNCHANGED;
1014 G_I_A_PRICING_STATUS_TEXT(j) := null;
1015 G_I_A_QUALIFIER_PRECEDENCE(j) := null;
1016 G_I_A_DATATYPE(j) := null;
1017 G_I_A_PRICING_ATTR_FLAG(j) := null;
1018 -- default values in qualifiers
1019 G_I_A_VALIDATED_FLAG(j) :='Y';
1020 G_I_A_LINE_DETAIL_INDEX(j) := null;
1021 G_I_A_ATTRIBUTE_LEVEL(j) := QP_PREQ_GRP.G_LINE_LEVEL;
1022 G_I_A_ATTRIBUTE_TYPE(j) := QP_PREQ_GRP.G_QUALIFIER_TYPE;
1023 G_I_A_LIST_HEADER_ID(j) := null;
1024 G_I_A_LIST_LINE_ID(j) := null;
1025 G_I_A_SETUP_VALUE_FROM(j) := null;
1026 G_I_A_VALUE_TO(j) := null;
1027 G_I_A_SETUP_VALUE_TO(j) := null;
1028 G_I_A_GROUPING_NUMBER(j) := null;
1029 G_I_A_NO_QUALIFIERS_IN_GRP(j) := null;
1030 G_I_A_APPLIED_FLAG(j) := null;
1031 G_I_A_PRICING_STATUS_CODE(j) := QP_PREQ_GRP.G_STATUS_UNCHANGED;
1032 G_I_A_PRICING_STATUS_TEXT(j) := null;
1033 G_I_A_QUALIFIER_PRECEDENCE(j) := null;
1034 G_I_A_DATATYPE(j) := null;
1035 G_I_A_PRICING_ATTR_FLAG(j) := null;
1036 G_I_A_QUALIFIER_TYPE(j) := null;
1037 G_I_A_PRODUCT_UOM_CODE(j) := null;
1038 G_I_A_EXCLUDER_FLAG(j) := null;
1039 G_I_A_PRICING_PHASE_ID(j) := null;
1040 G_I_A_INCOMPATABILITY_GRP_CODE(j) := null;
1041 G_I_A_LINE_DETAIL_TYPE_CODE(j) := null;
1042 G_I_A_MODIFIER_LEVEL_CODE(j) := null;
1043 G_I_A_PRIMARY_UOM_FLAG(j) := null;
1044
1045 -- fte_freight_pricing_util.unset_method(l_log_level,'create_qual_record');
1046 EXCEPTION
1047 WHEN others THEN
1048 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1049 FTE_FREIGHT_PRICING_UTIL.set_exception(l_method_name,l_log_level,'g_others');
1050 FTE_FREIGHT_PRICING_UTIL.print_msg(fte_freight_pricing_util.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
1051 fte_freight_pricing_util.unset_method(l_log_level,'create_qual_record');
1052 END create_qual_record;
1053
1054 -- Volume type of qp pricing attribute is passed to QP in attribute_value_from
1055 -- column which is varchar2, to avoid converting errors in QP, trunc it to
1056 -- 20 positions after the decimal point
1057
1058 FUNCTION get_qp_volume_string(
1059 p_string1 IN VARCHAR2) RETURN VARCHAR2
1060 IS
1061 k NUMBER;
1062 l_limit CONSTANT NUMBER := 20;
1063 BEGIN
1064
1065 IF p_string1 is null THEN
1066 RETURN '';
1067 END IF;
1068
1069 k := instr(p_string1, '.', 1,1);
1070 IF k > 0 THEN
1071 RETURN substr(p_string1, 1, k + l_limit);
1072 ELSE
1073 RETURN p_string1;
1074 END IF;
1075
1076 END get_qp_volume_string;
1077
1078 -- creates a single attribute record and adds it to the appropriate i/p table
1079 PROCEDURE create_attr_record (p_event_num IN NUMBER,
1080 p_attr_rec IN fte_freight_pricing.pricing_attribute_rec_type,
1081 x_return_status OUT NOCOPY VARCHAR2) IS
1082
1083 j NUMBER;
1084 k NUMBER;
1085
1086 l_log_level NUMBER := fte_freight_pricing_util.G_DBG;
1087 l_method_name VARCHAR2(50) := 'create_attr_record';
1088 BEGIN
1089
1090 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1091 fte_freight_pricing_util.reset_dbg_vars;
1092 -- fte_freight_pricing_util.set_method(l_log_level,'create_attr_record');
1093
1094 j := g_I_A_LINE_INDEX.COUNT + 1;
1095
1096 g_I_A_LINE_INDEX(j) := p_attr_rec.input_index;
1097
1098 IF (p_attr_rec.attribute_name = 'CONTAINER_TYPE') THEN
1099 g_I_A_CONTEXT(j) :='LOGISTICS'; --
1100 g_I_A_ATTRIBUTE_TYPE(j) := QP_PREQ_GRP.G_PRICING_TYPE;
1101 g_I_A_ATTRIBUTE(j) :='PRICING_ATTRIBUTE2';
1102 g_I_A_VALUE_FROM(j) := p_attr_rec.attribute_value;
1103 ELSIF (p_attr_rec.attribute_name = 'CATEGORY_ID') THEN
1104 g_I_A_CONTEXT(j) :='LOGISTICS'; --
1105 g_I_A_ATTRIBUTE_TYPE(j) := QP_PREQ_GRP.G_PRICING_TYPE;
1106 g_I_A_ATTRIBUTE(j) :='PRICING_ATTRIBUTE1';
1107 g_I_A_VALUE_FROM(j) := p_attr_rec.attribute_value;
1108 ELSIF (p_attr_rec.attribute_name = 'SERVICE_TYPE') THEN
1109 g_I_A_CONTEXT(j) :='LOGISTICS'; --
1110 g_I_A_ATTRIBUTE_TYPE(j) := QP_PREQ_GRP.G_PRICING_TYPE;
1111 g_I_A_ATTRIBUTE(j) :='PRICING_ATTRIBUTE3';
1112 g_I_A_VALUE_FROM(j) := p_attr_rec.attribute_value;
1113 ELSIF (p_attr_rec.attribute_name = 'ADDITIONAL_CHARGE') THEN
1114 g_I_A_CONTEXT(j) :='LOGISTICS'; --
1115 g_I_A_ATTRIBUTE_TYPE(j) := QP_PREQ_GRP.G_PRICING_TYPE;
1116 g_I_A_ATTRIBUTE(j) :='PRICING_ATTRIBUTE4';
1117 g_I_A_VALUE_FROM(j) := p_attr_rec.attribute_value;
1118 ELSIF (p_attr_rec.attribute_name = 'ORIGIN_ZONE') THEN
1119 g_I_A_CONTEXT(j) :='LOGISTICS'; --
1120 g_I_A_ATTRIBUTE_TYPE(j) := QP_PREQ_GRP.G_PRICING_TYPE;
1121 g_I_A_ATTRIBUTE(j) :='PRICING_ATTRIBUTE7';
1122 g_I_A_VALUE_FROM(j) := p_attr_rec.attribute_value;
1123 ELSIF (p_attr_rec.attribute_name = 'DESTINATION_ZONE') THEN
1124 g_I_A_CONTEXT(j) :='LOGISTICS'; --
1125 g_I_A_ATTRIBUTE_TYPE(j) := QP_PREQ_GRP.G_PRICING_TYPE;
1126 g_I_A_ATTRIBUTE(j) :='PRICING_ATTRIBUTE8';
1127 g_I_A_VALUE_FROM(j) := p_attr_rec.attribute_value;
1128 ELSIF (p_attr_rec.attribute_name = 'TOTAL_SHIPMENT_QUANTITY') THEN
1129 g_I_A_CONTEXT(j) :='LOGISTICS'; --
1130 g_I_A_ATTRIBUTE_TYPE(j) := QP_PREQ_GRP.G_PRICING_TYPE;
1131 g_I_A_ATTRIBUTE(j) :='PRICING_ATTRIBUTE9';
1132 g_I_A_VALUE_FROM(j) := p_attr_rec.attribute_value;
1133 ELSIF (p_attr_rec.attribute_name = 'TOTAL_ITEM_QUANTITY') THEN
1134 g_I_A_CONTEXT(j) :='VOLUME'; --
1135 g_I_A_ATTRIBUTE_TYPE(j) := QP_PREQ_GRP.G_PRICING_TYPE;
1136 g_I_A_ATTRIBUTE(j) :='PRICING_ATTRIBUTE20';
1137 -- trunc it to 10 positions after the decimal point
1138 -- in order to avoid converting error in QP
1139 --k := instr(p_attr_rec.attribute_value, '.', 1, 1);
1140 --if (k > 0) then
1141 --g_I_A_VALUE_FROM(j) := substr(p_attr_rec.attribute_value,1,k+10);
1142 --else
1143 --g_I_A_VALUE_FROM(j) := p_attr_rec.attribute_value;
1144 --end if;
1145 g_I_A_VALUE_FROM(j) := get_qp_volume_string(p_attr_rec.attribute_value);
1146 ELSIF (p_attr_rec.attribute_name = 'ITEM_ALL') THEN
1147 g_I_A_CONTEXT(j) :='ITEM'; --
1148 g_I_A_ATTRIBUTE_TYPE(j) := QP_PREQ_GRP.G_PRODUCT_TYPE;
1149 g_I_A_ATTRIBUTE(j) :='PRICING_ATTRIBUTE3';
1150 g_I_A_VALUE_FROM(j) := 'ALL';
1151 ELSIF (p_attr_rec.attribute_name = 'MULTIPIECE_FLAG') THEN
1152 g_I_A_CONTEXT(j) :='LOGISTICS'; --
1153 g_I_A_ATTRIBUTE_TYPE(j) := QP_PREQ_GRP.G_PRICING_TYPE;
1154 g_I_A_ATTRIBUTE(j) :='PRICING_ATTRIBUTE10';
1155 g_I_A_VALUE_FROM(j) := p_attr_rec.attribute_value;
1156 ELSIF (p_attr_rec.attribute_name = 'TL_RATE_BASIS') THEN
1157 g_I_A_CONTEXT(j) := fte_rtg_globals.G_AX_TL_RATE_BASIS;
1158 g_I_A_ATTRIBUTE_TYPE(j) := QP_PREQ_GRP.G_PRICING_TYPE;
1159 g_I_A_ATTRIBUTE(j) := fte_rtg_globals.G_A_TL_RATE_BASIS;
1160 g_I_A_VALUE_FROM(j) := p_attr_rec.attribute_value;
1161 ELSIF (p_attr_rec.attribute_name = 'TL_RATE_TYPE') THEN
1162 g_I_A_CONTEXT(j) := fte_rtg_globals.G_AX_TL_RATE_TYPE;
1163 g_I_A_ATTRIBUTE_TYPE(j) := QP_PREQ_GRP.G_PRICING_TYPE;
1164 g_I_A_ATTRIBUTE(j) := fte_rtg_globals.G_A_TL_RATE_TYPE;
1165 g_I_A_VALUE_FROM(j) := p_attr_rec.attribute_value;
1166 ELSIF (p_attr_rec.attribute_name = 'TL_DISTANCE_TYPE') THEN
1167 g_I_A_CONTEXT(j) := fte_rtg_globals.G_AX_TL_DISTANCE_TYPE;
1168 g_I_A_ATTRIBUTE_TYPE(j) := QP_PREQ_GRP.G_PRICING_TYPE;
1169 g_I_A_ATTRIBUTE(j) := fte_rtg_globals.G_A_TL_DISTANCE_TYPE;
1170 g_I_A_VALUE_FROM(j) := p_attr_rec.attribute_value;
1171 -- ELSIF (p_attr_rec.attribute_name = 'SERVICE_TYPE') THEN
1172 -- g_I_A_CONTEXT(j) := fte_rtg_globals.G_AX_SERVICE_TYPE;
1173 -- g_I_A_ATTRIBUTE_TYPE(j) := QP_PREQ_GRP.G_PRICING_TYPE;
1174 -- g_I_A_ATTRIBUTE(j) := fte_rtg_globals.G_A_SERVICE_TYPE;
1175 -- g_I_A_VALUE_FROM(j) := p_attr_rec.attribute_value;
1176 ELSIF (p_attr_rec.attribute_name = 'VEHICLE') THEN
1177 g_I_A_CONTEXT(j) := fte_rtg_globals.G_AX_VEHICLE;
1178 g_I_A_ATTRIBUTE_TYPE(j) := QP_PREQ_GRP.G_PRICING_TYPE;
1179 g_I_A_ATTRIBUTE(j) := fte_rtg_globals.G_A_VEHICLE;
1180 g_I_A_VALUE_FROM(j) := p_attr_rec.attribute_value;
1181 ELSIF (p_attr_rec.attribute_name = 'TL_ORIGIN_ZONE') THEN
1182 g_I_A_CONTEXT(j) := fte_rtg_globals.G_AX_ORIGIN_ZONE;
1183 g_I_A_ATTRIBUTE_TYPE(j) := QP_PREQ_GRP.G_PRICING_TYPE;
1184 g_I_A_ATTRIBUTE(j) := fte_rtg_globals.G_A_ORIGIN_ZONE;
1185 g_I_A_VALUE_FROM(j) := p_attr_rec.attribute_value;
1186 ELSIF (p_attr_rec.attribute_name = 'TL_DESTINATION_ZONE') THEN
1187 g_I_A_CONTEXT(j) := fte_rtg_globals.G_AX_DESTINATION_ZONE;
1188 g_I_A_ATTRIBUTE_TYPE(j) := QP_PREQ_GRP.G_PRICING_TYPE;
1189 g_I_A_ATTRIBUTE(j) := fte_rtg_globals.G_A_DESTINATION_ZONE;
1190 g_I_A_VALUE_FROM(j) := p_attr_rec.attribute_value;
1191 ELSIF (p_attr_rec.attribute_name = 'TL_NUM_WEEKEND_LAYOVERS') THEN
1192 g_I_A_CONTEXT(j) := fte_rtg_globals.G_AX_TL_NUM_WEEKEND_LAYOVERS;
1193 g_I_A_ATTRIBUTE_TYPE(j) := QP_PREQ_GRP.G_PRICING_TYPE;
1194 g_I_A_ATTRIBUTE(j) := fte_rtg_globals.G_A_TL_NUM_WEEKEND_LAYOVERS;
1195 g_I_A_VALUE_FROM(j) := p_attr_rec.attribute_value;
1196 ELSIF (p_attr_rec.attribute_name = 'LOADING_PROTOCOL') THEN
1197 g_I_A_CONTEXT(j) := fte_rtg_globals.G_AX_LOADING_PROTOCOL;
1198 g_I_A_ATTRIBUTE_TYPE(j) := QP_PREQ_GRP.G_PRICING_TYPE;
1199 g_I_A_ATTRIBUTE(j) := fte_rtg_globals.G_A_LOADING_PROTOCOL;
1200 g_I_A_VALUE_FROM(j) := p_attr_rec.attribute_value;
1201 ELSIF (p_attr_rec.attribute_name = 'TL_CM_DISCOUNT_FLG') THEN
1202 g_I_A_CONTEXT(j) := fte_rtg_globals.G_AX_TL_CM_DISCOUNT_FLG;
1203 g_I_A_ATTRIBUTE_TYPE(j) := QP_PREQ_GRP.G_PRICING_TYPE;
1204 g_I_A_ATTRIBUTE(j) := fte_rtg_globals.G_A_TL_CM_DISCOUNT_FLG;
1205 g_I_A_VALUE_FROM(j) := p_attr_rec.attribute_value;
1206 ELSIF (p_attr_rec.attribute_name = 'TL_DEADHEAD_RT_VAR') THEN
1207 g_I_A_CONTEXT(j) := fte_rtg_globals.G_AX_TL_DEADHEAD_RT_VAR;
1208 g_I_A_ATTRIBUTE_TYPE(j) := QP_PREQ_GRP.G_PRICING_TYPE;
1209 g_I_A_ATTRIBUTE(j) := fte_rtg_globals.G_A_TL_DEADHEAD_RT_VAR;
1210 g_I_A_VALUE_FROM(j) := p_attr_rec.attribute_value;
1211 ELSIF (p_attr_rec.attribute_name = 'TL_NUM_STOPS') THEN
1212 g_I_A_CONTEXT(j) := fte_rtg_globals.G_AX_TL_NUM_STOPS;
1213 g_I_A_ATTRIBUTE_TYPE(j) := QP_PREQ_GRP.G_PRICING_TYPE;
1214 g_I_A_ATTRIBUTE(j) := fte_rtg_globals.G_A_TL_NUM_STOPS;
1215 g_I_A_VALUE_FROM(j) := p_attr_rec.attribute_value;
1216 ELSIF (p_attr_rec.attribute_name = 'TL_CHARGED_OUT_RT_DISTANCE') THEN
1217 g_I_A_CONTEXT(j) := fte_rtg_globals.G_AX_TL_CHARGED_OUT_RT_DIST;
1218 g_I_A_ATTRIBUTE_TYPE(j) := QP_PREQ_GRP.G_PRICING_TYPE;
1219 g_I_A_ATTRIBUTE(j) := fte_rtg_globals.G_A_TL_CHARGED_OUT_RT_DIST;
1220 --g_I_A_VALUE_FROM(j) := p_attr_rec.attribute_value;
1221 g_I_A_VALUE_FROM(j) := get_qp_volume_string(p_attr_rec.attribute_value);
1222 ELSIF (p_attr_rec.attribute_name = 'TL_HANDLING_WT') THEN
1223 g_I_A_CONTEXT(j) := fte_rtg_globals.G_AX_TL_HANDLING_WT;
1224 g_I_A_ATTRIBUTE_TYPE(j) := QP_PREQ_GRP.G_PRICING_TYPE;
1225 g_I_A_ATTRIBUTE(j) := fte_rtg_globals.G_A_TL_HANDLING_WT;
1226 g_I_A_VALUE_FROM(j) := p_attr_rec.attribute_value;
1227 ELSIF (p_attr_rec.attribute_name = 'TL_HANDLING_VOL') THEN
1228 g_I_A_CONTEXT(j) := fte_rtg_globals.G_AX_TL_HANDLING_VOL;
1229 g_I_A_ATTRIBUTE_TYPE(j) := QP_PREQ_GRP.G_PRICING_TYPE;
1230 g_I_A_ATTRIBUTE(j) := fte_rtg_globals.G_A_TL_HANDLING_VOL;
1231 g_I_A_VALUE_FROM(j) := p_attr_rec.attribute_value;
1232 ELSIF (p_attr_rec.attribute_name = 'TL_PICKUP_WT') THEN
1233 g_I_A_CONTEXT(j) := fte_rtg_globals.G_AX_TL_PICKUP_WT;
1234 g_I_A_ATTRIBUTE_TYPE(j) := QP_PREQ_GRP.G_PRICING_TYPE;
1235 g_I_A_ATTRIBUTE(j) := fte_rtg_globals.G_A_TL_PICKUP_WT;
1236 g_I_A_VALUE_FROM(j) := p_attr_rec.attribute_value;
1237 ELSIF (p_attr_rec.attribute_name = 'TL_PICKUP_VOL') THEN
1238 g_I_A_CONTEXT(j) := fte_rtg_globals.G_AX_TL_PICKUP_VOL;
1239 g_I_A_ATTRIBUTE_TYPE(j) := QP_PREQ_GRP.G_PRICING_TYPE;
1240 g_I_A_ATTRIBUTE(j) := fte_rtg_globals.G_A_TL_PICKUP_VOL;
1241 g_I_A_VALUE_FROM(j) := p_attr_rec.attribute_value;
1242 ELSIF (p_attr_rec.attribute_name = 'TL_PICKUP_CONTAINER') THEN
1243 g_I_A_CONTEXT(j) := fte_rtg_globals.G_AX_TL_PICKUP_CONTAINER;
1244 g_I_A_ATTRIBUTE_TYPE(j) := QP_PREQ_GRP.G_PRICING_TYPE;
1245 g_I_A_ATTRIBUTE(j) := fte_rtg_globals.G_A_TL_PICKUP_CONTAINER;
1246 g_I_A_VALUE_FROM(j) := p_attr_rec.attribute_value;
1247 ELSIF (p_attr_rec.attribute_name = 'TL_PICKUP_PALLET') THEN
1248 g_I_A_CONTEXT(j) := fte_rtg_globals.G_AX_TL_PICKUP_PALLET;
1249 g_I_A_ATTRIBUTE_TYPE(j) := QP_PREQ_GRP.G_PRICING_TYPE;
1250 g_I_A_ATTRIBUTE(j) := fte_rtg_globals.G_A_TL_PICKUP_PALLET;
1251 g_I_A_VALUE_FROM(j) := p_attr_rec.attribute_value;
1252 ELSIF (p_attr_rec.attribute_name = 'TL_DROPOFF_WT') THEN
1253 g_I_A_CONTEXT(j) := fte_rtg_globals.G_AX_TL_DROPOFF_WT;
1254 g_I_A_ATTRIBUTE_TYPE(j) := QP_PREQ_GRP.G_PRICING_TYPE;
1255 g_I_A_ATTRIBUTE(j) := fte_rtg_globals.G_A_TL_DROPOFF_WT;
1256 g_I_A_VALUE_FROM(j) := p_attr_rec.attribute_value;
1257 ELSIF (p_attr_rec.attribute_name = 'TL_DROPOFF_VOL') THEN
1258 g_I_A_CONTEXT(j) := fte_rtg_globals.G_AX_TL_DROPOFF_VOL;
1259 g_I_A_ATTRIBUTE_TYPE(j) := QP_PREQ_GRP.G_PRICING_TYPE;
1260 g_I_A_ATTRIBUTE(j) := fte_rtg_globals.G_A_TL_DROPOFF_VOL;
1261 g_I_A_VALUE_FROM(j) := p_attr_rec.attribute_value;
1262 ELSIF (p_attr_rec.attribute_name = 'TL_DROPOFF_CONTAINER') THEN
1263 g_I_A_CONTEXT(j) := fte_rtg_globals.G_AX_TL_DROPOFF_CONTAINER;
1264 g_I_A_ATTRIBUTE_TYPE(j) := QP_PREQ_GRP.G_PRICING_TYPE;
1265 g_I_A_ATTRIBUTE(j) := fte_rtg_globals.G_A_TL_DROPOFF_CONTAINER;
1266 g_I_A_VALUE_FROM(j) := p_attr_rec.attribute_value;
1267 ELSIF (p_attr_rec.attribute_name = 'TL_DROPOFF_PALLET') THEN
1268 g_I_A_CONTEXT(j) := fte_rtg_globals.G_AX_TL_DROPOFF_PALLET;
1269 g_I_A_ATTRIBUTE_TYPE(j) := QP_PREQ_GRP.G_PRICING_TYPE;
1270 g_I_A_ATTRIBUTE(j) := fte_rtg_globals.G_A_TL_DROPOFF_PALLET;
1271 g_I_A_VALUE_FROM(j) := p_attr_rec.attribute_value;
1272 ELSIF (p_attr_rec.attribute_name = 'TL_NUM_WEEKDAY_LAYOVERS') THEN
1273 g_I_A_CONTEXT(j) := fte_rtg_globals.G_AX_TL_NUM_WEEKDAY_LAYOVERS;
1274 g_I_A_ATTRIBUTE_TYPE(j) := QP_PREQ_GRP.G_PRICING_TYPE;
1275 g_I_A_ATTRIBUTE(j) := fte_rtg_globals.G_A_TL_NUM_WEEKDAY_LAYOVERS;
1276 g_I_A_VALUE_FROM(j) := p_attr_rec.attribute_value;
1277 ELSIF (p_attr_rec.attribute_name = 'TL_WEEKEND_LAYOVER_MILEAGE') THEN
1278 g_I_A_CONTEXT(j) := fte_rtg_globals.G_AX_TL_WEEKEND_LAYOVER_MIL;
1279 g_I_A_ATTRIBUTE_TYPE(j) := QP_PREQ_GRP.G_PRICING_TYPE;
1280 g_I_A_ATTRIBUTE(j) := fte_rtg_globals.G_A_TL_WEEKEND_LAYOVER_MIL;
1281 --g_I_A_VALUE_FROM(j) := p_attr_rec.attribute_value;
1282 g_I_A_VALUE_FROM(j) := get_qp_volume_string(p_attr_rec.attribute_value);
1283 ELSIF (p_attr_rec.attribute_name = 'FAC_PICKUP_WT') THEN
1284 g_I_A_CONTEXT(j) := fte_rtg_globals.G_AX_FAC_PICKUP_WT;
1285 g_I_A_ATTRIBUTE_TYPE(j) := QP_PREQ_GRP.G_PRICING_TYPE;
1286 g_I_A_ATTRIBUTE(j) := fte_rtg_globals.G_A_FAC_PICKUP_WT;
1287 g_I_A_VALUE_FROM(j) := p_attr_rec.attribute_value;
1288 ELSIF (p_attr_rec.attribute_name = 'FAC_PICKUP_VOL') THEN
1289 g_I_A_CONTEXT(j) := fte_rtg_globals.G_AX_FAC_PICKUP_VOL;
1290 g_I_A_ATTRIBUTE_TYPE(j) := QP_PREQ_GRP.G_PRICING_TYPE;
1291 g_I_A_ATTRIBUTE(j) := fte_rtg_globals.G_A_FAC_PICKUP_VOL;
1292 g_I_A_VALUE_FROM(j) := p_attr_rec.attribute_value;
1293 ELSIF (p_attr_rec.attribute_name = 'FAC_PICKUP_CONTAINER') THEN
1294 g_I_A_CONTEXT(j) := fte_rtg_globals.G_AX_FAC_PICKUP_CONTAINER;
1295 g_I_A_ATTRIBUTE_TYPE(j) := QP_PREQ_GRP.G_PRICING_TYPE;
1296 g_I_A_ATTRIBUTE(j) := fte_rtg_globals.G_A_FAC_PICKUP_CONTAINER;
1297 g_I_A_VALUE_FROM(j) := p_attr_rec.attribute_value;
1298 ELSIF (p_attr_rec.attribute_name = 'FAC_PICKUP_PALLET') THEN
1299 g_I_A_CONTEXT(j) := fte_rtg_globals.G_AX_FAC_PICKUP_PALLET;
1300 g_I_A_ATTRIBUTE_TYPE(j) := QP_PREQ_GRP.G_PRICING_TYPE;
1301 g_I_A_ATTRIBUTE(j) := fte_rtg_globals.G_A_FAC_PICKUP_PALLET;
1302 g_I_A_VALUE_FROM(j) := p_attr_rec.attribute_value;
1303 ELSIF (p_attr_rec.attribute_name = 'FAC_DROPOFF_WT') THEN
1304 g_I_A_CONTEXT(j) := fte_rtg_globals.G_AX_FAC_DROPOFF_WT;
1305 g_I_A_ATTRIBUTE_TYPE(j) := QP_PREQ_GRP.G_PRICING_TYPE;
1306 g_I_A_ATTRIBUTE(j) := fte_rtg_globals.G_A_FAC_DROPOFF_WT;
1307 g_I_A_VALUE_FROM(j) := p_attr_rec.attribute_value;
1308 ELSIF (p_attr_rec.attribute_name = 'FAC_DROPOFF_VOL') THEN
1309 g_I_A_CONTEXT(j) := fte_rtg_globals.G_AX_FAC_DROPOFF_VOL;
1310 g_I_A_ATTRIBUTE_TYPE(j) := QP_PREQ_GRP.G_PRICING_TYPE;
1311 g_I_A_ATTRIBUTE(j) := fte_rtg_globals.G_A_FAC_DROPOFF_VOL;
1312 g_I_A_VALUE_FROM(j) := p_attr_rec.attribute_value;
1313 ELSIF (p_attr_rec.attribute_name = 'FAC_DROPOFF_CONTAINER') THEN
1314 g_I_A_CONTEXT(j) := fte_rtg_globals.G_AX_FAC_DROPOFF_CONTAINER;
1315 g_I_A_ATTRIBUTE_TYPE(j) := QP_PREQ_GRP.G_PRICING_TYPE;
1316 g_I_A_ATTRIBUTE(j) := fte_rtg_globals.G_A_FAC_DROPOFF_CONTAINER;
1317 g_I_A_VALUE_FROM(j) := p_attr_rec.attribute_value;
1318 ELSIF (p_attr_rec.attribute_name = 'FAC_DROPOFF_PALLET') THEN
1319 g_I_A_CONTEXT(j) := fte_rtg_globals.G_AX_FAC_DROPOFF_PALLET;
1320 g_I_A_ATTRIBUTE_TYPE(j) := QP_PREQ_GRP.G_PRICING_TYPE;
1321 g_I_A_ATTRIBUTE(j) := fte_rtg_globals.G_A_FAC_DROPOFF_PALLET;
1322 g_I_A_VALUE_FROM(j) := p_attr_rec.attribute_value;
1323 ELSIF (p_attr_rec.attribute_name = 'FAC_HANDLING_WT') THEN
1324 g_I_A_CONTEXT(j) := fte_rtg_globals.G_AX_FAC_HANDLING_WT;
1325 g_I_A_ATTRIBUTE_TYPE(j) := QP_PREQ_GRP.G_PRICING_TYPE;
1326 g_I_A_ATTRIBUTE(j) := fte_rtg_globals.G_A_FAC_HANDLING_WT;
1327 g_I_A_VALUE_FROM(j) := p_attr_rec.attribute_value;
1328 ELSIF (p_attr_rec.attribute_name = 'FAC_HANDLING_VOL') THEN
1329 g_I_A_CONTEXT(j) := fte_rtg_globals.G_AX_FAC_HANDLING_VOL;
1330 g_I_A_ATTRIBUTE_TYPE(j) := QP_PREQ_GRP.G_PRICING_TYPE;
1331 g_I_A_ATTRIBUTE(j) := fte_rtg_globals.G_A_FAC_HANDLING_VOL;
1332 g_I_A_VALUE_FROM(j) := p_attr_rec.attribute_value;
1333 ELSIF (p_attr_rec.attribute_name = 'FAC_HANDLING_CONTAINER') THEN
1334 g_I_A_CONTEXT(j) := fte_rtg_globals.G_AX_FAC_HANDLING_CONTAINER;
1335 g_I_A_ATTRIBUTE_TYPE(j) := QP_PREQ_GRP.G_PRICING_TYPE;
1336 g_I_A_ATTRIBUTE(j) := fte_rtg_globals.G_A_FAC_HANDLING_CONTAINER;
1337 g_I_A_VALUE_FROM(j) := p_attr_rec.attribute_value;
1338 ELSIF (p_attr_rec.attribute_name = 'FAC_HANDLING_PALLET') THEN
1339 g_I_A_CONTEXT(j) := fte_rtg_globals.G_AX_FAC_HANDLING_PALLET;
1340 g_I_A_ATTRIBUTE_TYPE(j) := QP_PREQ_GRP.G_PRICING_TYPE;
1341 g_I_A_ATTRIBUTE(j) := fte_rtg_globals.G_A_FAC_HANDLING_PALLET;
1342 g_I_A_VALUE_FROM(j) := p_attr_rec.attribute_value;
1343 ELSIF (p_attr_rec.attribute_name = 'TL_STOP_LOADING_ACT') THEN
1344 g_I_A_CONTEXT(j) := fte_rtg_globals.G_AX_TL_STOP_LOADING_ACT;
1345 g_I_A_ATTRIBUTE_TYPE(j) := QP_PREQ_GRP.G_PRICING_TYPE;
1346 g_I_A_ATTRIBUTE(j) := fte_rtg_globals.G_A_TL_STOP_LOADING_ACT;
1347 g_I_A_VALUE_FROM(j) := p_attr_rec.attribute_value;
1348 ELSIF (p_attr_rec.attribute_name = 'TL_STOP_UNLOADING_ACT') THEN
1349 g_I_A_CONTEXT(j) := fte_rtg_globals.G_AX_TL_STOP_UNLOADING_ACT;
1350 g_I_A_ATTRIBUTE_TYPE(j) := QP_PREQ_GRP.G_PRICING_TYPE;
1351 g_I_A_ATTRIBUTE(j) := fte_rtg_globals.G_A_TL_STOP_UNLOADING_ACT;
1352 g_I_A_VALUE_FROM(j) := p_attr_rec.attribute_value;
1353 ELSIF (p_attr_rec.attribute_name = 'TL_HANDLING_ACT') THEN
1354 g_I_A_CONTEXT(j) := fte_rtg_globals.G_AX_TL_HANDLING_ACT;
1355 g_I_A_ATTRIBUTE_TYPE(j) := QP_PREQ_GRP.G_PRICING_TYPE;
1356 g_I_A_ATTRIBUTE(j) := fte_rtg_globals.G_A_TL_HANDLING_ACT;
1357 g_I_A_VALUE_FROM(j) := p_attr_rec.attribute_value;
1358 ELSE
1359 null;
1360 fte_freight_pricing_util.print_msg(l_log_level,'Big problemo!');
1361 END IF;
1362
1363 fte_freight_pricing_util.print_msg(l_log_level,
1364 'inpidx='||p_attr_rec.input_index||' j='||j||' name='||p_attr_rec.attribute_name||' attrval='||p_attr_rec.attribute_value);
1365
1366 -- default values in attributes
1367 G_I_A_COMPARISON_OPERATOR_TYPE(j) := '=';
1368 G_I_A_VALIDATED_FLAG(j) :='N';
1369 G_I_A_LINE_DETAIL_INDEX(j) := null;
1370 G_I_A_ATTRIBUTE_LEVEL(j) := QP_PREQ_GRP.G_LINE_LEVEL;
1371 G_I_A_LIST_HEADER_ID(j) := null;
1372 G_I_A_LIST_LINE_ID(j) := null;
1373 G_I_A_SETUP_VALUE_FROM(j) := null;
1374 G_I_A_VALUE_TO(j) := null;
1375 G_I_A_SETUP_VALUE_TO(j) := null;
1376 G_I_A_GROUPING_NUMBER(j) := null;
1377 G_I_A_NO_QUALIFIERS_IN_GRP(j) := null;
1378 G_I_A_APPLIED_FLAG(j) := null;
1379 G_I_A_PRICING_STATUS_CODE(j) := QP_PREQ_GRP.G_STATUS_UNCHANGED;
1380 G_I_A_PRICING_STATUS_TEXT(j) := null;
1381 G_I_A_QUALIFIER_PRECEDENCE(j) := null;
1382 G_I_A_DATATYPE(j) := null;
1383 G_I_A_PRICING_ATTR_FLAG(j) := null;
1384 G_I_A_QUALIFIER_TYPE(j) := null;
1385 G_I_A_PRODUCT_UOM_CODE(j) := null;
1386 G_I_A_EXCLUDER_FLAG(j) := null;
1387 G_I_A_PRICING_PHASE_ID(j) := null;
1388 G_I_A_INCOMPATABILITY_GRP_CODE(j) := null;
1389 G_I_A_LINE_DETAIL_TYPE_CODE(j) := null;
1390 G_I_A_MODIFIER_LEVEL_CODE(j) := null;
1391 G_I_A_PRIMARY_UOM_FLAG(j) := null;
1392
1393 -- fte_freight_pricing_util.unset_method(l_log_level,'create_attr_record');
1394 EXCEPTION
1395 WHEN others THEN
1396 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1397 FTE_FREIGHT_PRICING_UTIL.set_exception(l_method_name,l_log_level,'g_others');
1398 FTE_FREIGHT_PRICING_UTIL.print_msg(fte_freight_pricing_util.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
1399 fte_freight_pricing_util.unset_method(l_log_level,'create_attr_record');
1400 END create_attr_record;
1401
1402
1403 -- This procedure is called to create pricing attributes per line rec from the input attr rows
1404 PROCEDURE prepare_qp_line_attributes (
1405 p_event_num IN NUMBER,
1406 p_input_index IN NUMBER,
1407 p_attr_rows IN fte_freight_pricing.pricing_attribute_tab_type,
1408 x_return_status OUT NOCOPY VARCHAR2 )
1409 IS
1410 i NUMBER;
1411 l_return_status VARCHAR2(30);
1412 l_pricing_attr_rec fte_freight_pricing.pricing_attribute_rec_type;
1413 j NUMBER;
1414 l_log_level NUMBER := fte_freight_pricing_util.G_DBG;
1415 l_method_name VARCHAR2(50) := 'prepare_qp_line_attributes';
1416 BEGIN
1417 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1418 fte_freight_pricing_util.reset_dbg_vars;
1419 fte_freight_pricing_util.set_method(l_log_level,'prepare_qp_line_attributes');
1420
1421 i := p_attr_rows.FIRST;
1422 IF (i IS NOT NULL) THEN
1423 LOOP
1424 IF (p_attr_rows(i).input_index = p_input_index ) THEN
1425 create_attr_record ( p_event_num => p_event_num,
1426 p_attr_rec => p_attr_rows(i),
1427 x_return_status => l_return_status);
1428
1429 FTE_FREIGHT_PRICING_UTIL.set_location(p_loc=>'after create_attr_record -1 ');
1430 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1431 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1432 raise FTE_FREIGHT_PRICING_UTIL.g_create_attribute_failed;
1433 END IF;
1434 END IF;
1435
1436 END IF;
1437 EXIT WHEN i = p_attr_rows.LAST;
1438 i := p_attr_rows.NEXT(i);
1439 END LOOP;
1440 END IF;
1441
1442 -- add other default attributes
1443 l_pricing_attr_rec.attribute_index := p_attr_rows(p_attr_rows.LAST).attribute_index + 1;
1444 l_pricing_attr_rec.input_index := p_input_index;
1445 l_pricing_attr_rec.attribute_name := 'ITEM_ALL';
1446 l_pricing_attr_rec.attribute_value := 'ALL';
1447 create_attr_record ( p_event_num => p_event_num,
1448 p_attr_rec => l_pricing_attr_rec,
1449 x_return_status => l_return_status);
1450
1451 FTE_FREIGHT_PRICING_UTIL.set_location(p_loc=>'after create_attr_record -2 ');
1452 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1453 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1454 raise FTE_FREIGHT_PRICING_UTIL.g_create_attribute_failed;
1455 END IF;
1456 END IF;
1457
1458 fte_freight_pricing_util.unset_method(l_log_level,'prepare_qp_line_attributes');
1459 EXCEPTION
1460 WHEN FTE_FREIGHT_PRICING_UTIL.g_create_attribute_failed THEN
1461 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1462 FTE_FREIGHT_PRICING_UTIL.set_exception(l_method_name,l_log_level,'create_attribute_failed');
1463 fte_freight_pricing_util.unset_method(l_log_level,'prepare_qp_line_attributes');
1464 WHEN others THEN
1465 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1466 FTE_FREIGHT_PRICING_UTIL.set_exception(l_method_name,l_log_level,'g_others');
1467 FTE_FREIGHT_PRICING_UTIL.print_msg(fte_freight_pricing_util.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
1468 fte_freight_pricing_util.unset_method(l_log_level,'prepare_qp_line_attributes');
1469
1470 END prepare_qp_line_attributes;
1471
1472 PROCEDURE prepare_qp_line_qualifiers (p_event_num IN NUMBER,
1473 p_pricing_control_rec IN fte_freight_pricing.pricing_control_input_rec_type,
1474 p_input_index IN NUMBER,
1475 x_return_status OUT NOCOPY VARCHAR2 )
1476 IS
1477 l_qual_rec qualifier_rec_type;
1478 l_return_status VARCHAR2(1);
1479 l_log_level NUMBER := fte_freight_pricing_util.G_DBG;
1480 l_method_name VARCHAR2(50) := 'prepare_qp_line_qualifiers';
1481 BEGIN
1482 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1483 fte_freight_pricing_util.reset_dbg_vars;
1484 fte_freight_pricing_util.set_method(l_log_level,'prepare_qp_line_qualifiers');
1485
1486 l_qual_rec.qualifier_index := 1;
1487 l_qual_rec.input_index := p_input_index;
1488 l_qual_rec.qualifier_name :='PRICELIST';
1489 l_qual_rec.qualifier_value := to_char(p_pricing_control_rec.price_list_id);
1490 create_qual_record (p_event_num => p_event_num,
1491 p_qual_rec => l_qual_rec,
1492 x_return_status => l_return_status);
1493
1494 FTE_FREIGHT_PRICING_UTIL.set_location(p_loc=>'after create_qual_record -1 ');
1495 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1496 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1497 raise FTE_FREIGHT_PRICING_UTIL.g_create_qual_record_failed;
1498 END IF;
1499 END IF;
1500
1501 l_qual_rec.qualifier_index := 2;
1502 l_qual_rec.qualifier_name :='SUPPLIER';
1503 l_qual_rec.qualifier_value := to_char(p_pricing_control_rec.party_id);
1504
1505 FTE_FREIGHT_PRICING_UTIL.print_msg(fte_freight_pricing_util.G_DBG,'===>p_pricing_control_rec.party_id = '||p_pricing_control_rec.party_id);
1506 create_qual_record (p_event_num => p_event_num,
1507 p_qual_rec => l_qual_rec,
1508 x_return_status => l_return_status);
1509
1510 FTE_FREIGHT_PRICING_UTIL.set_location(p_loc=>'after create_qual_record -2 ');
1511 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1512 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1513 raise FTE_FREIGHT_PRICING_UTIL.g_create_qual_record_failed;
1514 END IF;
1515 END IF;
1516
1517 fte_freight_pricing_util.unset_method(l_log_level,'prepare_qp_line_qualifiers');
1518 EXCEPTION
1519 WHEN FTE_FREIGHT_PRICING_UTIL.g_create_qual_record_failed THEN
1520 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1521 FTE_FREIGHT_PRICING_UTIL.set_exception(l_method_name,l_log_level,'create_qual_record_failed');
1522 fte_freight_pricing_util.unset_method(l_log_level,'prepare_qp_line_qualifiers');
1523 WHEN others THEN
1524 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1525 FTE_FREIGHT_PRICING_UTIL.set_exception(l_method_name,l_log_level,'g_others');
1526 FTE_FREIGHT_PRICING_UTIL.print_msg(fte_freight_pricing_util.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
1527 fte_freight_pricing_util.unset_method(l_log_level,'prepare_qp_line_qualifiers');
1528
1529 END prepare_qp_line_qualifiers;
1530
1531 -- add one qp output line detail record into qp output line detail table
1532 -- most of the qp output should come directly from qp
1533 -- since qp cannot handle all of FTE pricing reqirement (e.g. deficit weight for LTL)
1534 -- In some cases, FTE pricing engine needs to add some more records into
1535 -- qp output tables
1536 PROCEDURE add_qp_output_detail(
1537 p_line_index IN NUMBER,
1538 p_list_line_type_code IN VARCHAR2,
1539 p_charge_subtype_code IN VARCHAR2,
1540 p_adjustment_amount IN NUMBER,
1541 x_return_status OUT NOCOPY VARCHAR2)
1542 IS
1543 l_qp_output_detail_row QP_PREQ_GRP.LINE_DETAIL_REC_TYPE;
1544 l_line_detail_index NUMBER;
1545 l_return_status VARCHAR2(1);
1546 l_log_level NUMBER := fte_freight_pricing_util.G_DBG;
1547 l_method_name VARCHAR2(50) := 'add_qp_output_detail';
1548 BEGIN
1549 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1550 fte_freight_pricing_util.reset_dbg_vars;
1551 fte_freight_pricing_util.set_method(l_log_level,l_method_name);
1552
1553 fte_freight_pricing_util.print_msg(l_log_level,'p_line_index:'||p_line_index);
1554 fte_freight_pricing_util.print_msg(l_log_level,'p_list_line_type_code:'||p_list_line_type_code);
1555 fte_freight_pricing_util.print_msg(l_log_level,'p_charge_subtype_code:'||p_charge_subtype_code);
1556 fte_freight_pricing_util.print_msg(l_log_level,'p_adjustment_amount:'||p_adjustment_amount);
1557
1558 IF (g_O_line_detail_tbl.COUNT <= 0) THEN
1559 l_line_detail_index := 1;
1560 ELSE
1561 l_line_detail_index := g_O_line_detail_tbl.LAST + 1;
1562 END IF;
1563
1564 l_qp_output_detail_row.line_detail_index := l_line_detail_index;
1565 l_qp_output_detail_row.line_index := p_line_index;
1566 l_qp_output_detail_row.list_line_type_code := p_list_line_type_code;
1567 l_qp_output_detail_row.charge_subtype_code := p_charge_subtype_code;
1568 l_qp_output_detail_row.adjustment_amount := p_adjustment_amount;
1569
1570 g_O_line_detail_tbl(l_line_detail_index) := l_qp_output_detail_row;
1571
1572 fte_freight_pricing_util.unset_method(l_log_level,l_method_name);
1573 EXCEPTION
1574 WHEN others THEN
1575 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1576 FTE_FREIGHT_PRICING_UTIL.set_exception(l_method_name,l_log_level,'g_others');
1577 FTE_FREIGHT_PRICING_UTIL.print_msg(fte_freight_pricing_util.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
1578 fte_freight_pricing_util.unset_method(l_log_level,l_method_name);
1579 END add_qp_output_detail;
1580
1581 PROCEDURE check_qp_output_errors (x_return_status OUT NOCOPY VARCHAR2)
1582 IS
1583 i NUMBER :=0;
1584 l_error_flag BOOLEAN := false;
1585 l_category VARCHAR2(30);
1586 l_log_level NUMBER := fte_freight_pricing_util.G_LOG;
1587 l_method_name VARCHAR2(50) := 'check_qp_output_errors';
1588 BEGIN
1589 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1590 fte_freight_pricing_util.reset_dbg_vars;
1591 fte_freight_pricing_util.set_method(l_log_level,'check_output_errors');
1592
1593 i := g_O_line_tbl.FIRST;
1594 IF (i IS NOT NULL) THEN
1595 LOOP
1596 IF (g_O_line_tbl(i).status_code IN (
1597 QP_PREQ_GRP.G_STATUS_INVALID_PRICE_LIST ,
1598 QP_PREQ_GRP.G_STATUS_GSA_VIOLATION ,
1599 QP_PREQ_GRP.G_STS_LHS_NOT_FOUND ,
1600 QP_PREQ_GRP.G_STATUS_FORMULA_ERROR ,
1601 QP_PREQ_GRP.G_STATUS_OTHER_ERRORS ,
1602 QP_PREQ_GRP.G_STATUS_INCOMP_LOGIC ,
1603 QP_PREQ_GRP.G_STATUS_CALC_ERROR ,
1604 QP_PREQ_GRP.G_STATUS_UOM_FAILURE ,
1605 QP_PREQ_GRP.G_STATUS_INVALID_UOM ,
1606 QP_PREQ_GRP.G_STATUS_DUP_PRICE_LIST ,
1607 QP_PREQ_GRP.G_STATUS_INVALID_UOM_CONV ,
1608 QP_PREQ_GRP.G_STATUS_INVALID_INCOMP ,
1609 QP_PREQ_GRP.G_STATUS_BEST_PRICE_EVAL_ERROR )) THEN
1610 l_error_flag := true;
1611 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_LOG,' LineIndex = '||i||' Status Code = '||g_O_line_tbl(i).status_code||' Text = '||g_O_line_tbl(i).status_text);
1612 IF (g_O_line_tbl(i).status_code = 'IPL') THEN
1613 raise fte_freight_pricing_util.g_not_on_pricelist;
1614 END IF;
1615 END IF;
1616 IF (g_O_line_tbl(i).unit_price IS NULL) THEN
1617 l_error_flag := true;
1618 fte_freight_pricing_util.print_msg(l_log_level,'Unit price is null');
1619 -- ELSIF (g_O_line_tbl(i).unit_price <= 0) THEN
1620 ELSIF (g_O_line_tbl(i).unit_price < 0) THEN -- TL
1621 l_error_flag := true;
1622 -- fte_freight_pricing_util.print_msg(l_log_level,'Unit price non-positive');
1623 fte_freight_pricing_util.print_msg(l_log_level,'Unit price negative');
1624 END IF;
1625 EXIT WHEN i >= g_O_line_tbl.LAST;
1626 i := g_O_line_tbl.NEXT(i);
1627 END LOOP;
1628 END IF;
1629
1630 i := g_O_line_detail_tbl.FIRST;
1631 IF (i IS NOT NULL) THEN
1632 LOOP
1633 IF (g_O_line_detail_tbl(i).adjustment_amount IS NULL) THEN
1634 l_error_flag := true;
1635 fte_freight_pricing_util.print_msg(l_log_level,'Adjustment amount is null');
1636 END IF;
1637 EXIT WHEN i >= g_O_line_detail_tbl.LAST;
1638 i := g_O_line_detail_tbl.NEXT(i);
1639 END LOOP;
1640 END IF;
1641
1642 IF (l_error_flag) THEN
1643 raise FTE_FREIGHT_PRICING_UTIL.g_qp_price_request_failed;
1644 END IF;
1645
1646 fte_freight_pricing_util.unset_method(l_log_level,'check_output_errors');
1647 EXCEPTION
1648 WHEN fte_freight_pricing_util.g_not_on_pricelist THEN
1649 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1650 -- can use tokens here
1651 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_not_on_pricelist');
1652 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_ERR,'Following item quantity not found on pricelist :');
1653 l_category := g_I_line_extras_tbl(i).category_id;
1654 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_ERR,' Quantity = '||g_I_line_quantity(i)||' '||g_I_line_uom_code(i));
1655 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_ERR,' CategoryId = '||nvl(l_category,'Consolidated'));
1656 fte_freight_pricing_util.unset_method(l_log_level,'check_output_errors');
1657 WHEN fte_freight_pricing_util.g_qp_price_request_failed THEN
1658 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1659 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_qp_price_request_failed');
1660 fte_freight_pricing_util.unset_method(l_log_level,'check_output_errors');
1661 WHEN others THEN
1662 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1663 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_others');
1664 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
1665 fte_freight_pricing_util.unset_method(l_log_level,'check_output_errors');
1666
1667 END check_qp_output_errors;
1668
1669 PROCEDURE check_tl_qp_output_errors (x_return_status OUT NOCOPY VARCHAR2)
1670 IS
1671 i NUMBER :=0;
1672 l_error_flag BOOLEAN := false;
1673 l_category VARCHAR2(30);
1674 l_ipl_cnt NUMBER := 0;
1675 l_line_cnt NUMBER := 0;
1676 l_log_level NUMBER := fte_freight_pricing_util.G_LOG;
1677 l_method_name VARCHAR2(50) := 'check_tl_qp_output_errors';
1678 BEGIN
1679 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1680 fte_freight_pricing_util.reset_dbg_vars;
1681 fte_freight_pricing_util.set_method(l_log_level,'check_tl_qp_output_errors');
1682
1683 i := g_O_line_tbl.FIRST;
1684 IF (i IS NOT NULL) THEN
1685 LOOP
1686 l_line_cnt := l_line_cnt + 1;
1687 IF (g_O_line_tbl(i).status_code IN (
1688 QP_PREQ_GRP.G_STATUS_INVALID_PRICE_LIST ,
1689 QP_PREQ_GRP.G_STATUS_GSA_VIOLATION ,
1690 QP_PREQ_GRP.G_STS_LHS_NOT_FOUND ,
1691 QP_PREQ_GRP.G_STATUS_FORMULA_ERROR ,
1692 QP_PREQ_GRP.G_STATUS_OTHER_ERRORS ,
1693 QP_PREQ_GRP.G_STATUS_INCOMP_LOGIC ,
1694 QP_PREQ_GRP.G_STATUS_CALC_ERROR ,
1695 QP_PREQ_GRP.G_STATUS_UOM_FAILURE ,
1696 QP_PREQ_GRP.G_STATUS_INVALID_UOM ,
1697 QP_PREQ_GRP.G_STATUS_DUP_PRICE_LIST ,
1698 QP_PREQ_GRP.G_STATUS_INVALID_UOM_CONV ,
1699 QP_PREQ_GRP.G_STATUS_INVALID_INCOMP ,
1700 QP_PREQ_GRP.G_STATUS_BEST_PRICE_EVAL_ERROR )) THEN
1701 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_LOG,' LineIndex = '||i||' Status Code = '||g_O_line_tbl(i).status_code||' Text = '||g_O_line_tbl(i).status_text);
1702 IF (g_O_line_tbl(i).status_code = 'IPL') THEN
1703 l_ipl_cnt := l_ipl_cnt + 1;
1704 g_O_line_tbl(i).unit_price := 0;
1705 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_LOG,
1706 'Following item quantity not found on pricelist :');
1707 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_LOG,
1708 ' Quantity = '||g_I_line_quantity(i)||' '||g_I_line_uom_code(i));
1709 ELSE
1710 l_error_flag := true;
1711 END IF;
1712 END IF;
1713 IF (g_O_line_tbl(i).unit_price IS NULL) THEN
1714 l_error_flag := true;
1715 fte_freight_pricing_util.print_msg(l_log_level,'Unit price is null');
1716 -- ELSIF (g_O_line_tbl(i).unit_price <= 0) THEN
1717 ELSIF (g_O_line_tbl(i).unit_price < 0) THEN -- TL
1718 l_error_flag := true;
1719 -- fte_freight_pricing_util.print_msg(l_log_level,'Unit price non-positive');
1720 fte_freight_pricing_util.print_msg(l_log_level,'Unit price negative');
1721 END IF;
1722 EXIT WHEN i >= g_O_line_tbl.LAST;
1723 i := g_O_line_tbl.NEXT(i);
1724 END LOOP;
1725 END IF;
1726
1727 IF (l_ipl_cnt >= l_line_cnt) THEN
1728 -- probably big failure - not good
1729 fte_freight_pricing_util.print_msg(l_log_level,'l_ipl_cnt >= l_line_cnt');
1730 raise fte_freight_pricing_util.g_not_on_pricelist;
1731 ELSIF (l_ipl_cnt > 0) THEN
1732 -- probably ok
1733 fte_freight_pricing_util.print_msg(l_log_level,'WARNING: SOME LINES HAD IPL !!!');
1734 END IF;
1735
1736 i := g_O_line_detail_tbl.FIRST;
1737 IF (i IS NOT NULL) THEN
1738 LOOP
1739 IF (g_O_line_detail_tbl(i).adjustment_amount IS NULL) THEN
1740 l_error_flag := true;
1741 fte_freight_pricing_util.print_msg(l_log_level,'Adjustment amount is null');
1742 END IF;
1743 EXIT WHEN i >= g_O_line_detail_tbl.LAST;
1744 i := g_O_line_detail_tbl.NEXT(i);
1745 END LOOP;
1746 END IF;
1747
1748 IF (l_error_flag) THEN
1749 raise FTE_FREIGHT_PRICING_UTIL.g_qp_price_request_failed;
1750 END IF;
1751
1752 fte_freight_pricing_util.unset_method(l_log_level,'check_tl_qp_output_errors');
1753 EXCEPTION
1754 WHEN fte_freight_pricing_util.g_not_on_pricelist THEN
1755 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1756 -- can use tokens here
1757 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_not_on_pricelist');
1758 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_ERR,'Following item quantity not found on pricelist :');
1759 l_category := g_I_line_extras_tbl(i).category_id;
1760 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_ERR,' Quantity = '||g_I_line_quantity(i)||' '||g_I_line_uom_code(i));
1761 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_ERR,' CategoryId = '||nvl(l_category,'Consolidated'));
1762 fte_freight_pricing_util.unset_method(l_log_level,'check_tl_qp_output_errors');
1763 WHEN fte_freight_pricing_util.g_qp_price_request_failed THEN
1764 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1765 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_qp_price_request_failed');
1766 fte_freight_pricing_util.unset_method(l_log_level,'check_tl_qp_output_errors');
1767 WHEN others THEN
1768 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1769 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_others');
1770 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
1771 fte_freight_pricing_util.unset_method(l_log_level,'check_tl_qp_output_errors');
1772
1773 END check_tl_qp_output_errors;
1774
1775 PROCEDURE check_parcel_output_errors (p_event_num IN NUMBER,
1776 x_return_code OUT NOCOPY NUMBER,
1777 x_return_status OUT NOCOPY VARCHAR2)
1778 IS
1779 i NUMBER :=0;
1780 l_error_flag BOOLEAN := false;
1781 l_category VARCHAR2(30);
1782 l_log_level NUMBER := fte_freight_pricing_util.G_LOG;
1783 l_method_name VARCHAR2(50) := 'check_parcel_output_errors';
1784 l_mp_line_cnt NUMBER := 0;
1785 l_mp_ipl_cnt NUMBER := 0;
1786 l_sp_ipl_cnt NUMBER := 0;
1787 BEGIN
1788 -- loop through output
1789 -- If error (any error type) is from set 1
1790 -- if error = ipl, sp_ipl_cnt ++;
1791 -- if error any other than ipl, then throw an error.
1792 -- If error is from set 2 :
1793 -- if error = IPL, iplCount++
1794 -- if error any other than IPL, then throw error
1795 -- If (iplCount from set 2) = (count of lines in set 2), then this is NOT an error. This condition
1796 -- probably means that the total quantity does not fall in any of the defined breaks.
1797 -- Else throw an error
1798 -- x_return_code = G_PAR_NO_MP_PRICE means multi-piece (hundredwt) prices did not exist
1799
1800 -- set 1 is single piece lines
1801 -- set 2 is multi piece lines
1802
1803 -- if (set 2 result all successful)
1804 -- set 2 result valid;
1805 -- else if (set 2 result all ipl)
1806 -- set 2 result invalid; -- but not exception;
1807 -- else -- set 2 parcial ipl
1808 -- exception;
1809 -- if (set 1 result all successfull)
1810 -- set 1 result valid;
1811 -- else -- set 1 all or parcial ipl
1812 -- set 1 result invalid; -- but not exception;
1813
1814 -- if (set 1 result valid) and (set 2 result valid)
1815 -- x_return_status := SUCCESS;
1816 -- x_return_code := 0;
1817 -- else if (set 1 result valid) and (set 2 result invalid)
1818 -- x_return_status := SUCCESS;
1819 -- x_return_code := G_PAR_NO_MP_PRICE;
1820 -- else if (set 1 result invalid) and (set 2 result valid)
1821 -- x_return_status := SUCCESS;
1822 -- x_return_code := G_PAR_NO_SP_PRICE;
1823 -- else --(set 1 result invalid) and (set 2 result invalid)
1824 -- x_return_status := ERROR;
1825 -- x_return_code := 0;
1826
1827
1828 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1829 x_return_code := 0;
1830 fte_freight_pricing_util.reset_dbg_vars;
1831 fte_freight_pricing_util.set_method(l_log_level,l_method_name);
1832
1833 IF (p_event_num = G_LINE_EVENT_NUM ) THEN
1834 i := g_O_line_tbl.FIRST;
1835 IF (i IS NOT NULL) THEN
1836 LOOP
1837 IF (g_I_line_extras_tbl(i).input_set_number = 1) THEN
1838 IF (g_O_line_tbl(i).status_code IN (
1839 --QP_PREQ_GRP.G_STATUS_INVALID_PRICE_LIST ,
1840 QP_PREQ_GRP.G_STATUS_GSA_VIOLATION ,
1841 QP_PREQ_GRP.G_STS_LHS_NOT_FOUND ,
1842 QP_PREQ_GRP.G_STATUS_FORMULA_ERROR ,
1843 QP_PREQ_GRP.G_STATUS_OTHER_ERRORS ,
1844 QP_PREQ_GRP.G_STATUS_INCOMP_LOGIC ,
1845 QP_PREQ_GRP.G_STATUS_CALC_ERROR ,
1846 QP_PREQ_GRP.G_STATUS_UOM_FAILURE ,
1847 QP_PREQ_GRP.G_STATUS_INVALID_UOM ,
1848 QP_PREQ_GRP.G_STATUS_DUP_PRICE_LIST ,
1849 QP_PREQ_GRP.G_STATUS_INVALID_UOM_CONV ,
1850 QP_PREQ_GRP.G_STATUS_INVALID_INCOMP ,
1851 QP_PREQ_GRP.G_STATUS_BEST_PRICE_EVAL_ERROR )) THEN
1852 l_error_flag := true;
1853 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_LOG,'Event = '||p_event_num||' LineIndex = '||i||' Status Code = '||g_O_line_tbl(i).status_code||' Text = '||g_O_line_tbl(i).status_text);
1854 END IF;
1855 IF (g_O_line_tbl(i).status_code = QP_PREQ_GRP.G_STATUS_INVALID_PRICE_LIST) THEN
1856 --IF (g_O_line_tbl(i).status_code = 'IPL') THEN
1857 l_sp_ipl_cnt := l_sp_ipl_cnt + 1;
1858 --raise fte_freight_pricing_util.g_not_on_pricelist;
1859 ELSIF (g_O_line_tbl(i).unit_price IS NULL) THEN
1860 l_error_flag := true;
1861 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_LOG,'Unit price is null');
1862 ELSIF (g_O_line_tbl(i).unit_price <= 0) THEN
1863 l_error_flag := true;
1864 fte_freight_pricing_util.print_msg(l_log_level,'Unit price non-positive');
1865 END IF;
1866 ELSIF (g_I_line_extras_tbl(i).input_set_number = 2) THEN
1867 l_mp_line_cnt := l_mp_line_cnt + 1;
1868 IF (g_O_line_tbl(i).status_code IN (
1869 --QP_PREQ_GRP.G_STATUS_INVALID_PRICE_LIST ,
1870 QP_PREQ_GRP.G_STATUS_GSA_VIOLATION ,
1871 QP_PREQ_GRP.G_STS_LHS_NOT_FOUND ,
1872 QP_PREQ_GRP.G_STATUS_FORMULA_ERROR ,
1873 QP_PREQ_GRP.G_STATUS_OTHER_ERRORS ,
1874 QP_PREQ_GRP.G_STATUS_INCOMP_LOGIC ,
1875 QP_PREQ_GRP.G_STATUS_CALC_ERROR ,
1876 QP_PREQ_GRP.G_STATUS_UOM_FAILURE ,
1877 QP_PREQ_GRP.G_STATUS_INVALID_UOM ,
1878 QP_PREQ_GRP.G_STATUS_DUP_PRICE_LIST ,
1879 QP_PREQ_GRP.G_STATUS_INVALID_UOM_CONV ,
1880 QP_PREQ_GRP.G_STATUS_INVALID_INCOMP ,
1881 QP_PREQ_GRP.G_STATUS_BEST_PRICE_EVAL_ERROR )) THEN
1882 l_error_flag := true;
1883 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_LOG,'Event = '||p_event_num||' LineIndex = '||i||' Status Code = '||g_O_line_tbl(i).status_code||' Text = '||g_O_line_tbl(i).status_text);
1884 END IF;
1885 -- check for IPL
1886 IF (g_O_line_tbl(i).status_code = QP_PREQ_GRP.G_STATUS_INVALID_PRICE_LIST) THEN
1887 l_mp_ipl_cnt := l_mp_ipl_cnt +1;
1888 ELSIF (g_O_line_tbl(i).unit_price IS NULL) THEN
1889 l_error_flag := true;
1890 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_LOG,'Unit price is
1891 null for reasons other than IPL');
1892 ELSIF (g_O_line_tbl(i).unit_price <= 0) THEN
1893 l_error_flag := true;
1894 fte_freight_pricing_util.print_msg(l_log_level,'Unit price non-positive');
1895 END IF;
1896 ELSE
1897 null;
1898 END IF; -- input_set_number
1899
1900 EXIT WHEN i >= g_O_line_tbl.LAST;
1901 i := g_O_line_tbl.NEXT(i);
1902 END LOOP;
1903
1904 IF (l_mp_line_cnt > 0) THEN
1905 IF (l_mp_ipl_cnt > 0) THEN
1906 IF (l_mp_line_cnt = l_mp_ipl_cnt) THEN
1907 IF (l_sp_ipl_cnt > 0) THEN
1908 l_error_flag := true;
1909 ELSE
1910 x_return_code := G_PAR_NO_MP_PRICE;
1911 END IF;
1912 ELSE
1913 raise fte_freight_pricing_util.g_not_on_pricelist;
1914 END IF;
1915 ELSE
1916 IF (l_sp_ipl_cnt > 0) THEN
1917 x_return_code := G_PAR_NO_SP_PRICE;
1918 ELSE
1919 x_return_code := 0;
1920 END IF;
1921 END IF;
1922 ELSE -- no mp lines
1923 IF (l_sp_ipl_cnt >0) THEN
1924 l_error_flag := true;
1925 ELSE
1926 x_return_code := G_PAR_NO_MP_PRICE;
1927 END IF;
1928 END IF;
1929 /*
1930 IF ((l_mp_ipl_cnt < l_mp_line_cnt) AND (l_mp_ipl_cnt <> 0) ) THEN
1931 -- there were IPLs and they are probably not something we can ignore
1932 l_error_flag := true;
1933 raise fte_freight_pricing_util.g_not_on_pricelist;
1934 ELSE
1935 IF (l_mp_ipl_cnt >0 ) THEN
1936 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_LOG,'IPLs were found but num of ipls = num of set2 lines');
1937 x_return_code := G_PAR_NO_MP_PRICE; -- multipiece price not found
1938 END IF;
1939 END IF;
1940 */
1941 END IF; -- i is not null
1942 ELSIF (p_event_num = G_CHARGE_EVENT_NUM) THEN
1943 i := g_O_line_tbl.FIRST ;
1944 IF (i IS NOT NULL) THEN
1945 LOOP
1946 IF (g_O_line_tbl(i).status_code IN (
1947 QP_PREQ_GRP.G_STATUS_INVALID_PRICE_LIST ,
1948 QP_PREQ_GRP.G_STATUS_GSA_VIOLATION ,
1949 QP_PREQ_GRP.G_STS_LHS_NOT_FOUND ,
1950 QP_PREQ_GRP.G_STATUS_FORMULA_ERROR ,
1951 QP_PREQ_GRP.G_STATUS_OTHER_ERRORS ,
1952 QP_PREQ_GRP.G_STATUS_INCOMP_LOGIC ,
1953 QP_PREQ_GRP.G_STATUS_CALC_ERROR ,
1954 QP_PREQ_GRP.G_STATUS_UOM_FAILURE ,
1955 QP_PREQ_GRP.G_STATUS_INVALID_UOM ,
1956 QP_PREQ_GRP.G_STATUS_DUP_PRICE_LIST ,
1957 QP_PREQ_GRP.G_STATUS_INVALID_UOM_CONV ,
1958 QP_PREQ_GRP.G_STATUS_INVALID_INCOMP ,
1959 QP_PREQ_GRP.G_STATUS_BEST_PRICE_EVAL_ERROR )) THEN
1960 l_error_flag := true;
1961 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_LOG,'Event = '||p_event_num||' LineIndex = '||i||' Status Code = '||g_O_line_tbl(i).status_code||' Text = '||g_O_line_tbl(i).status_text);
1962 IF (g_O_line_tbl(i).status_code = 'IPL') THEN
1963 raise fte_freight_pricing_util.g_not_on_pricelist;
1964 END IF;
1965 END IF;
1966 IF (g_O_line_tbl(i).unit_price IS NULL) THEN
1967 l_error_flag := true;
1968 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_LOG,'Unit price is null');
1969 END IF;
1970 EXIT WHEN i >= g_O_line_tbl.LAST;
1971 i := g_O_line_tbl.NEXT(i);
1972 END LOOP;
1973 END IF;
1974 ELSE
1975 null;
1976 END IF;
1977
1978 i := g_O_line_detail_tbl.FIRST;
1979 IF (i IS NOT NULL) THEN
1980 LOOP
1981 IF (g_O_line_detail_tbl(i).adjustment_amount IS NULL) THEN
1982 l_error_flag := true;
1983 fte_freight_pricing_util.print_msg(l_log_level,'Adjustment amount is null');
1984 END IF;
1985 EXIT WHEN i >= g_O_line_detail_tbl.LAST;
1986 i := g_O_line_detail_tbl.NEXT(i);
1987 END LOOP;
1988 END IF;
1989
1990 IF (l_error_flag) THEN
1991 raise FTE_FREIGHT_PRICING_UTIL.g_qp_price_request_failed;
1992 END IF;
1993
1994 fte_freight_pricing_util.unset_method(l_log_level,l_method_name);
1995 EXCEPTION
1996 WHEN fte_freight_pricing_util.g_not_on_pricelist THEN
1997 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1998 -- can use tokens here
1999 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_not_on_pricelist');
2000 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_ERR,'Following item quantity not found on pricelist :');
2001 l_category := g_I_line_extras_tbl(i).category_id;
2002 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_ERR,' Quantity = '||g_I_line_quantity(i)||' '||g_I_line_uom_code(i));
2003 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_ERR,' CategoryId = '||nvl(l_category,'Consolidated'));
2004 fte_freight_pricing_util.unset_method(l_log_level,l_method_name);
2005 WHEN fte_freight_pricing_util.g_qp_price_request_failed THEN
2006 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2007 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_qp_price_request_failed');
2008 fte_freight_pricing_util.unset_method(l_log_level,l_method_name);
2009 WHEN others THEN
2010 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2011 fte_freight_pricing_util.set_exception(l_method_name,l_log_level,'g_others');
2012 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
2013 fte_freight_pricing_util.unset_method(l_log_level,l_method_name);
2014
2015 END check_parcel_output_errors;
2016
2017 -- return the pointer to the qp outputs
2018 PROCEDURE get_qp_output(
2019 x_qp_output_line_rows OUT NOCOPY QP_PREQ_GRP.LINE_TBL_TYPE,
2020 x_qp_output_detail_rows OUT NOCOPY QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE,
2021 x_return_status OUT NOCOPY VARCHAR2)
2022 IS
2023 l_return_status VARCHAR2(1);
2024 l_log_level NUMBER := fte_freight_pricing_util.G_DBG;
2025 l_method_name VARCHAR2(50) := 'get_qp_output';
2026 BEGIN
2027 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2028 fte_freight_pricing_util.reset_dbg_vars;
2029 fte_freight_pricing_util.set_method(l_log_level,l_method_name);
2030
2031 x_qp_output_line_rows := g_O_line_tbl;
2032 x_qp_output_detail_rows := g_O_line_detail_tbl;
2033
2034 fte_freight_pricing_util.unset_method(l_log_level,l_method_name);
2035 EXCEPTION
2036 WHEN others THEN
2037 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2038 FTE_FREIGHT_PRICING_UTIL.set_exception(l_method_name,l_log_level,'g_others');
2039 FTE_FREIGHT_PRICING_UTIL.print_msg(fte_freight_pricing_util.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
2040 fte_freight_pricing_util.unset_method(l_log_level,l_method_name);
2041 END get_qp_output;
2042
2043 -- populate qp output from the temp table
2044 PROCEDURE populate_qp_output (
2045 x_qp_output_line_rows OUT NOCOPY QP_PREQ_GRP.LINE_TBL_TYPE,
2046 x_qp_output_detail_rows OUT NOCOPY QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE,
2047 x_return_status OUT NOCOPY VARCHAR2)
2048 IS
2049 l_return_status_text VARCHAR2(240);
2050 l_return_status VARCHAR2(1);
2051
2052 l_log_level NUMBER := fte_freight_pricing_util.G_LOG;
2053 l_method_name VARCHAR2(50) := 'populate_qp_output';
2054
2055 cursor c_qp_lines_tmp is
2056 select
2057 request_type_code,
2058 line_id,
2059 line_index,
2060 line_type_code,
2061 pricing_effective_date,
2062 line_quantity,
2063 line_uom_code,
2064 priced_quantity,
2065 priced_uom_code,
2066 currency_code,
2067 unit_price,
2068 adjusted_unit_price,
2069 price_flag,
2070 extended_price,
2071 start_date_active_first,
2072 start_date_active_second,
2073 pricing_status_code,
2074 pricing_status_text
2075 from qp_preq_lines_tmp;
2076
2077 cursor c_qp_line_details_tmp is
2078 select
2079 line_detail_index,
2080 line_detail_type_code,
2081 line_index,
2082 list_header_id,
2083 list_line_id,
2084 list_line_type_code,
2085 adjustment_amount,
2086 charge_type_code,
2087 charge_subtype_code,
2088 line_quantity,
2089 operand_calculation_code,
2090 operand_value,
2091 automatic_flag,
2092 override_flag,
2093 pricing_status_code,
2094 pricing_status_text
2095 from qp_ldets_v
2096 where pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW
2097 and ( list_line_type_code = QP_PREQ_GRP.G_DISCOUNT
2098 or ((list_line_type_code = QP_PREQ_GRP.G_SURCHARGE
2099 or list_line_type_code = QP_PREQ_GRP.G_PRICE_BREAK_TYPE ) -- TL
2100 and charge_subtype_code IS NOT NULL) -- TL
2101 );
2102
2103 -- Note : discounts not guaranteed to have a subtype code in LTL / Parcel
2104
2105
2106 CURSOR c_qp_child_details (c_parent_line_index NUMBER,
2107 c_parent_line_detail_index NUMBER)
2108 IS
2109 SELECT
2110 ldets.line_detail_index,
2111 ldets.line_detail_type_code,
2112 ldets.line_index,
2113 ldets.list_header_id,
2114 ldets.list_line_id,
2115 ldets.list_line_type_code,
2116 ldets.adjustment_amount,
2117 ldets.charge_type_code,
2118 ldets.charge_subtype_code,
2119 ldets.line_quantity,
2120 ldets.operand_calculation_code,
2121 ldets.operand_value,
2122 ldets.automatic_flag,
2123 ldets.override_flag,
2124 ldets.pricing_status_code,
2125 ldets.pricing_status_text
2126 FROM qp_ldets_v ldets,
2127 qp_preq_rltd_lines_tmp rltd
2128 WHERE
2129 rltd.related_line_index = ldets.line_index
2130 AND rltd.related_line_detail_index = ldets.line_detail_index
2131 AND rltd.line_index = c_parent_line_index
2132 AND rltd.line_detail_index = c_parent_line_detail_index
2133 AND rltd.relationship_type_code = QP_PREQ_GRP.G_PBH_LINE
2134 AND rltd.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW
2135 AND ldets.pricing_status_code = QP_PREQ_GRP.G_STATUS_NEW
2136 AND ldets.list_line_type_code = QP_PREQ_GRP.G_SURCHARGE
2137 AND ldets.operand_calculation_code = 'LUMPSUM'
2138 AND ldets.line_quantity >0;
2139
2140
2141 i NUMBER;
2142
2143 BEGIN
2144 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2145 fte_freight_pricing_util.reset_dbg_vars;
2146 fte_freight_pricing_util.set_method(l_log_level,l_method_name);
2147
2148 g_O_line_tbl.DELETE;
2149 g_O_line_detail_tbl.DELETE;
2150
2151 i := 0;
2152 for l in c_qp_lines_tmp loop
2153 i := i+1;
2154 g_O_line_tbl(i).request_type_code := l.request_type_code;
2155 g_O_line_tbl(i).line_index := l.line_index;
2156 g_O_line_tbl(i).line_id := l.line_id;
2157 g_O_line_tbl(i).line_type_code := l.line_type_code;
2158 g_O_line_tbl(i).pricing_effective_date := l.pricing_effective_date;
2159 g_O_line_tbl(i).active_date_first := l.start_date_active_first;
2160 g_O_line_tbl(i).active_date_second := l.start_date_active_second;
2161 g_O_line_tbl(i).line_quantity := l.line_quantity;
2162 g_O_line_tbl(i).line_uom_code := l.line_uom_code;
2163 g_O_line_tbl(i).priced_quantity := l.priced_quantity;
2164 g_O_line_tbl(i).priced_uom_code := l.priced_uom_code;
2165 g_O_line_tbl(i).currency_code := l.currency_code;
2166 g_O_line_tbl(i).unit_price := l.unit_price;
2167 g_O_line_tbl(i).adjusted_unit_price := l.adjusted_unit_price;
2168 g_O_line_tbl(i).price_flag := l.price_flag;
2169 g_O_line_tbl(i).extended_price := l.extended_price;
2170 g_O_line_tbl(i).status_code := l.pricing_status_code;
2171 g_O_line_tbl(i).status_text := l.pricing_status_text;
2172 end loop;
2173
2174 i := 0;
2175 for l in c_qp_line_details_tmp loop
2176 i := i+1;
2177 g_O_line_detail_tbl(i).line_detail_index := l.line_detail_index;
2178 g_O_line_detail_tbl(i).line_detail_type_code := l.line_detail_type_code;
2179 g_O_line_detail_tbl(i).line_index := l.line_index;
2180 g_O_line_detail_tbl(i).list_header_id := l.list_header_id;
2181 g_O_line_detail_tbl(i).list_line_id := l.list_line_id;
2182 g_O_line_detail_tbl(i).list_line_type_code := l.list_line_type_code;
2183 g_O_line_detail_tbl(i).adjustment_amount := l.adjustment_amount;
2184 g_O_line_detail_tbl(i).charge_type_code := l.charge_type_code;
2185 g_O_line_detail_tbl(i).charge_subtype_code := l.charge_subtype_code;
2186 g_O_line_detail_tbl(i).line_quantity := l.line_quantity;
2187 g_O_line_detail_tbl(i).operand_calculation_code := l.operand_calculation_code;
2188 g_O_line_detail_tbl(i).operand_value := l.operand_value;
2189 g_O_line_detail_tbl(i).automatic_flag := l.automatic_flag;
2190 g_O_line_detail_tbl(i).override_flag := l.override_flag;
2191 g_O_line_detail_tbl(i).status_code := l.pricing_status_code;
2192 g_O_line_detail_tbl(i).status_text := l.pricing_status_text;
2193
2194
2195 IF l.line_quantity > 0
2196 AND l.charge_subtype_code IN (
2197 fte_rtg_globals.G_C_HANDLING_WEIGHT_CH,
2198 fte_rtg_globals.G_C_HANDLING_VOLUME_CH,
2199 fte_rtg_globals.G_C_LOADING_WEIGHT_CH,
2200 fte_rtg_globals.G_C_LOADING_VOLUME_CH,
2201 fte_rtg_globals.G_C_LOADING_PALLET_CH,
2202 fte_rtg_globals.G_C_LOADING_CONTAINER_CH,
2203 fte_rtg_globals.G_C_AST_LOADING_WEIGHT_CH,
2204 fte_rtg_globals.G_C_AST_LOADING_VOLUME_CH,
2205 fte_rtg_globals.G_C_AST_LOADING_PALLET_CH,
2206 fte_rtg_globals.G_C_AST_LOADING_CONTAINER_CH,
2207 fte_rtg_globals.G_C_UNLOADING_WEIGHT_CH,
2208 fte_rtg_globals.G_C_UNLOADING_VOLUME_CH,
2209 fte_rtg_globals.G_C_UNLOADING_PALLET_CH,
2210 fte_rtg_globals.G_C_UNLOADING_CONTAINER_CH,
2211 fte_rtg_globals.G_C_AST_UNLOADING_WEIGHT_CH,
2212 fte_rtg_globals.G_C_AST_UNLOADING_VOLUME_CH,
2213 fte_rtg_globals.G_C_AST_UNLOADING_PALLET_CH,
2214 fte_rtg_globals.G_C_AST_UNLOADING_CONTAINER_CH,
2215 fte_rtg_globals.G_F_LOADING_WEIGHT_CH,
2216 fte_rtg_globals.G_F_LOADING_VOLUME_CH,
2217 fte_rtg_globals.G_F_LOADING_PALLET_CH,
2218 fte_rtg_globals.G_F_LOADING_CONTAINER_CH,
2219 fte_rtg_globals.G_F_AST_LOADING_WEIGHT_CH,
2220 fte_rtg_globals.G_F_AST_LOADING_VOLUME_CH,
2221 fte_rtg_globals.G_F_AST_LOADING_PALLET_CH,
2222 fte_rtg_globals.G_F_AST_LOADING_CONTAINER_CH,
2223 fte_rtg_globals.G_F_UNLOADING_WEIGHT_CH,
2224 fte_rtg_globals.G_F_UNLOADING_VOLUME_CH,
2225 fte_rtg_globals.G_F_UNLOADING_PALLET_CH,
2226 fte_rtg_globals.G_F_UNLOADING_CONTAINER_CH,
2227 fte_rtg_globals.G_F_AST_UNLOADING_WEIGHT_CH,
2228 fte_rtg_globals.G_F_AST_UNLOADING_VOLUME_CH,
2229 fte_rtg_globals.G_F_AST_UNLOADING_PALLET_CH,
2230 fte_rtg_globals.G_F_AST_UNLOADING_CONTAINER_CH,
2231 fte_rtg_globals.G_F_HANDLING_WEIGHT_CH,
2232 fte_rtg_globals.G_F_HANDLING_VOLUME_CH,
2233 fte_rtg_globals.G_F_HANDLING_PALLET_CH,
2234 fte_rtg_globals.G_F_HANDLING_CONTAINER_CH )
2235 THEN
2236
2237 FTE_FREIGHT_PRICING_UTIL.print_msg(fte_freight_pricing_util.G_DBG,
2238 'Found line_index:'||l.line_index||'det_idx '||l.line_detail_index||
2239 ' charge subtype '||l.charge_subtype_code||' line type :'||l.list_line_type_code
2240 ||' OrigLineQty: '||l.line_quantity );
2241
2242 FOR c_child_rec IN c_qp_child_details(l.line_index, l.line_detail_index) LOOP
2243 FTE_FREIGHT_PRICING_UTIL.print_msg(fte_freight_pricing_util.G_DBG,
2244 'Child detail : LnDetIdx :'||c_child_rec.line_detail_index
2245 ||' LnQty:'||c_child_rec.line_quantity||' AdjAmt:'
2246 ||c_child_rec.adjustment_amount||' OperCalc: '||c_child_rec.operand_calculation_code);
2247
2248 --IF (c_child_rec.operand_calculation_code = 'LUMPSUM') THEN
2249 -- update parent line_quantity
2250 g_O_line_detail_tbl(i).line_quantity := 1;
2251 --END IF;
2252
2253 END LOOP;
2254
2255 END IF;
2256
2257 end loop;
2258
2259
2260 print_qp_output();
2261
2262 x_qp_output_line_rows := g_O_line_tbl;
2263 x_qp_output_detail_rows := g_O_line_detail_tbl;
2264
2265 fte_freight_pricing_util.unset_method(l_log_level,l_method_name);
2266 EXCEPTION
2267 WHEN others THEN
2268 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2269 FTE_FREIGHT_PRICING_UTIL.set_exception(l_method_name,l_log_level,'g_others');
2270 FTE_FREIGHT_PRICING_UTIL.print_msg(fte_freight_pricing_util.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
2271 fte_freight_pricing_util.unset_method(l_log_level,l_method_name);
2272
2273 END populate_qp_output;
2274
2275 PROCEDURE peek_qp_input_line IS
2276 l_log_level NUMBER := fte_freight_pricing_util.G_DBG;
2277 l_method_name VARCHAR2(50) := 'peek_qp_input_line';
2278 i1 NUMBER;
2279 i2 NUMBER;
2280 i3 NUMBER;
2281 i4 NUMBER;
2282 i5 NUMBER;
2283 i6 NUMBER;
2284 i7 NUMBER;
2285 i8 NUMBER;
2286 i9 NUMBER;
2287 i11 NUMBER;
2288 i12 NUMBER;
2289 i13 NUMBER;
2290 i14 NUMBER;
2291 i15 NUMBER;
2292 i16 NUMBER;
2293 i17 NUMBER;
2294 i18 NUMBER;
2295 i19 NUMBER;
2296 i21 NUMBER;
2297 i22 NUMBER;
2298 i23 NUMBER;
2299 i24 NUMBER;
2300 i25 NUMBER;
2301 i26 NUMBER;
2302 i27 NUMBER;
2303 i28 NUMBER;
2304 i29 NUMBER;
2305 i10 NUMBER;
2306 i20 NUMBER;
2307 i30 NUMBER;
2308 i31 NUMBER;
2309 i32 NUMBER;
2310 i33 NUMBER;
2311 BEGIN
2312 fte_freight_pricing_util.set_method(l_log_level,l_method_name);
2313
2314 i1 := G_I_LINE_INDEX.FIRST;
2315 i2 := G_I_LINE_TYPE_CODE.FIRST;
2316 i3 := G_I_PRICING_EFFECTIVE_DATE.FIRST;
2317 i4 := G_I_ACTIVE_DATE_FIRST.FIRST;
2318 i5 := G_I_ACTIVE_DATE_FIRST_TYPE.FIRST;
2319 i6 := G_I_ACTIVE_DATE_SECOND.FIRST;
2320 i7 := G_I_ACTIVE_DATE_SECOND_TYPE.FIRST;
2321 i8 := G_I_LINE_QUANTITY.FIRST;
2322 i9 := G_I_LINE_UOM_CODE.FIRST;
2323 i10 := G_I_REQUEST_TYPE_CODE.FIRST;
2324 i11 := G_I_PRICED_QUANTITY.FIRST;
2325 i12 := G_I_PRICED_UOM_CODE.FIRST;
2326 i13 := G_I_CURRENCY_CODE.FIRST;
2327 i14 := G_I_UNIT_PRICE.FIRST;
2328 i15 := G_I_PERCENT_PRICE.FIRST;
2329 i16 := G_I_UOM_QUANTITY.FIRST;
2330 i17 := G_I_ADJUSTED_UNIT_PRICE.FIRST;
2331 i18 := G_I_UPD_ADJUSTED_UNIT_PRICE.FIRST;
2332 i19 := G_I_PROCESSED_FLAG.FIRST;
2333 i20 := G_I_PRICE_FLAG.FIRST;
2334 i21 := G_I_LINE_ID.FIRST;
2335 i22 := G_I_PROCESSING_ORDER.FIRST;
2336 i23 := G_I_PRICING_STATUS_CODE.FIRST;
2337 i24 := G_I_PRICING_STATUS_TEXT.FIRST;
2338 i25 := G_I_ROUNDING_FLAG.FIRST;
2339 i26 := G_I_ROUNDING_FACTOR.FIRST;
2340 i27 := G_I_QUALIFIERS_EXIST_FLAG.FIRST;
2341 i28 := G_I_PRICING_ATTRS_EXIST_FLAG.FIRST;
2342 i29 := G_I_PRICE_LIST_ID.FIRST;
2343 i30 := G_I_VALIDATED_FLAG.FIRST;
2344 i31 := G_I_PRICE_REQUEST_CODE.FIRST;
2345 i32 := G_I_USAGE_PRICING_TYPE.FIRST;
2346 i33 := G_I_LINE_CATEGORY.FIRST;
2347
2348 if i1 is not null then
2349 loop
2350
2351 fte_freight_pricing_util.print_msg(l_log_level,'i1 = '||i1);
2352 if (i2 <> i1) then
2353 fte_freight_pricing_util.print_msg(l_log_level,'i2 = '||i2);
2354 end if;
2355 if (i3 <> i1) then
2356 fte_freight_pricing_util.print_msg(l_log_level,'i3 = '||i3);
2357 end if;
2358 if (i4 <> i1) then
2359 fte_freight_pricing_util.print_msg(l_log_level,'i4 = '||i4);
2360 end if;
2361 if (i5 <> i1) then
2362 fte_freight_pricing_util.print_msg(l_log_level,'i5 = '||i5);
2363 end if;
2364 if (i6 <> i1) then
2365 fte_freight_pricing_util.print_msg(l_log_level,'i6 = '||i6);
2366 end if;
2367 if (i7 <> i1) then
2368 fte_freight_pricing_util.print_msg(l_log_level,'i7 = '||i7);
2369 end if;
2370 if (i8 <> i1) then
2371 fte_freight_pricing_util.print_msg(l_log_level,'i8 = '||i8);
2372 end if;
2373 if (i9 <> i1) then
2374 fte_freight_pricing_util.print_msg(l_log_level,'i9 = '||i9);
2375 end if;
2376 if (i10 <> i1) then
2377 fte_freight_pricing_util.print_msg(l_log_level,'i10 = '||i10);
2378 end if;
2379 if (i11 <> i1) then
2380 fte_freight_pricing_util.print_msg(l_log_level,'i11 = '||i11);
2381 end if;
2382 if (i12 <> i1) then
2383 fte_freight_pricing_util.print_msg(l_log_level,'i12 = '||i12);
2384 end if;
2385 if (i13 <> i1) then
2386 fte_freight_pricing_util.print_msg(l_log_level,'i13 = '||i13);
2387 end if;
2388 if (i14 <> i1) then
2389 fte_freight_pricing_util.print_msg(l_log_level,'i14 = '||i14);
2390 end if;
2391 if (i15 <> i1) then
2392 fte_freight_pricing_util.print_msg(l_log_level,'i15 = '||i15);
2393 end if;
2394 if (i16 <> i1) then
2395 fte_freight_pricing_util.print_msg(l_log_level,'i16 = '||i16);
2396 end if;
2397 if (i17 <> i1) then
2398 fte_freight_pricing_util.print_msg(l_log_level,'i17 = '||i17);
2399 end if;
2400 if (i18 <> i1) then
2401 fte_freight_pricing_util.print_msg(l_log_level,'i18 = '||i18);
2402 end if;
2403 if (i19 <> i1) then
2404 fte_freight_pricing_util.print_msg(l_log_level,'i19 = '||i19);
2405 end if;
2406 if (i20 <> i1) then
2407 fte_freight_pricing_util.print_msg(l_log_level,'i20 = '||i20);
2408 end if;
2409 if (i21 <> i1) then
2410 fte_freight_pricing_util.print_msg(l_log_level,'i21 = '||i21);
2411 end if;
2412 if (i22 <> i1) then
2413 fte_freight_pricing_util.print_msg(l_log_level,'i22 = '||i22);
2414 end if;
2415 if (i23 <> i1) then
2416 fte_freight_pricing_util.print_msg(l_log_level,'i23 = '||i23);
2417 end if;
2418 if (i24 <> i1) then
2419 fte_freight_pricing_util.print_msg(l_log_level,'i24 = '||i24);
2420 end if;
2421 if (i25 <> i1) then
2422 fte_freight_pricing_util.print_msg(l_log_level,'i25 = '||i25);
2423 end if;
2424 if (i26 <> i1) then
2425 fte_freight_pricing_util.print_msg(l_log_level,'i26 = '||i26);
2426 end if;
2427 if (i27 <> i1) then
2428 fte_freight_pricing_util.print_msg(l_log_level,'i27 = '||i27);
2429 end if;
2430 if (i28 <> i1) then
2431 fte_freight_pricing_util.print_msg(l_log_level,'i28 = '||i28);
2432 end if;
2433 if (i29 <> i1) then
2434 fte_freight_pricing_util.print_msg(l_log_level,'i29 = '||i29);
2435 end if;
2436 if (i30 <> i1) then
2437 fte_freight_pricing_util.print_msg(l_log_level,'i30 = '||i30);
2438 end if;
2439 if (i31 <> i1) then
2440 fte_freight_pricing_util.print_msg(l_log_level,'i31 = '||i31);
2441 end if;
2442 if (i32 <> i1) then
2443 fte_freight_pricing_util.print_msg(l_log_level,'i32 = '||i32);
2444 end if;
2445 if (i33 <> i1) then
2446 fte_freight_pricing_util.print_msg(l_log_level,'i33 = '||i33);
2447 end if;
2448
2449 fte_freight_pricing_util.print_msg(l_log_level,'-----------------------------------------------');
2450
2451 exit when i1 = G_I_LINE_INDEX.LAST;
2452
2453 i1 := G_I_LINE_INDEX.NEXT(i1);
2454 i2 := G_I_LINE_TYPE_CODE.NEXT(i2);
2455 i3 := G_I_PRICING_EFFECTIVE_DATE.NEXT(i3);
2456 i4 := G_I_ACTIVE_DATE_FIRST.NEXT(i4);
2457 i5 := G_I_ACTIVE_DATE_FIRST_TYPE.NEXT(i5);
2458 i6 := G_I_ACTIVE_DATE_SECOND.NEXT(i6);
2459 i7 := G_I_ACTIVE_DATE_SECOND_TYPE.NEXT(i7);
2460 i8 := G_I_LINE_QUANTITY.NEXT(i8);
2461 i9 := G_I_LINE_UOM_CODE.NEXT(i9);
2462 i10 := G_I_REQUEST_TYPE_CODE.NEXT(i10);
2463 i11 := G_I_PRICED_QUANTITY.NEXT(i11);
2464 i12 := G_I_PRICED_UOM_CODE.NEXT(i12);
2465 i13 := G_I_CURRENCY_CODE.NEXT(i13);
2466 i14 := G_I_UNIT_PRICE.NEXT(i14);
2467 i15 := G_I_PERCENT_PRICE.NEXT(i15);
2468 i16 := G_I_UOM_QUANTITY.NEXT(i16);
2469 i17 := G_I_ADJUSTED_UNIT_PRICE.NEXT(i17);
2470 i18 := G_I_UPD_ADJUSTED_UNIT_PRICE.NEXT(i18);
2471 i19 := G_I_PROCESSED_FLAG.NEXT(i19);
2472 i20 := G_I_PRICE_FLAG.NEXT(i20);
2473 i21 := G_I_LINE_ID.NEXT(i21);
2474 i22 := G_I_PROCESSING_ORDER.NEXT(i22);
2475 i23 := G_I_PRICING_STATUS_CODE.NEXT(i23);
2476 i24 := G_I_PRICING_STATUS_TEXT.NEXT(i24);
2477 i25 := G_I_ROUNDING_FLAG.NEXT(i25);
2478 i26 := G_I_ROUNDING_FACTOR.NEXT(i26);
2479 i27 := G_I_QUALIFIERS_EXIST_FLAG.NEXT(i27);
2480 i28 := G_I_PRICING_ATTRS_EXIST_FLAG.NEXT(i28);
2481 i29 := G_I_PRICE_LIST_ID.NEXT(i29);
2482 i30 := G_I_VALIDATED_FLAG.NEXT(i30);
2483 i31 := G_I_PRICE_REQUEST_CODE.NEXT(i31);
2484 i32 := G_I_USAGE_PRICING_TYPE.NEXT(i32);
2485 i33 := G_I_LINE_CATEGORY.NEXT(i33);
2486
2487 end loop;
2488 end if;
2489
2490 i1 := G_I_LINE_INDEX.LAST;
2491 i2 := G_I_LINE_TYPE_CODE.LAST;
2492 i3 := G_I_PRICING_EFFECTIVE_DATE.LAST;
2493 i4 := G_I_ACTIVE_DATE_FIRST.LAST;
2494 i5 := G_I_ACTIVE_DATE_FIRST_TYPE.LAST;
2495 i6 := G_I_ACTIVE_DATE_SECOND.LAST;
2496 i7 := G_I_ACTIVE_DATE_SECOND_TYPE.LAST;
2497 i8 := G_I_LINE_QUANTITY.LAST;
2498 i9 := G_I_LINE_UOM_CODE.LAST;
2499 i10 := G_I_REQUEST_TYPE_CODE.LAST;
2500 i11 := G_I_PRICED_QUANTITY.LAST;
2501 i12 := G_I_PRICED_UOM_CODE.LAST;
2502 i13 := G_I_CURRENCY_CODE.LAST;
2503 i14 := G_I_UNIT_PRICE.LAST;
2504 i15 := G_I_PERCENT_PRICE.LAST;
2505 i16 := G_I_UOM_QUANTITY.LAST;
2506 i17 := G_I_ADJUSTED_UNIT_PRICE.LAST;
2507 i18 := G_I_UPD_ADJUSTED_UNIT_PRICE.LAST;
2508 i19 := G_I_PROCESSED_FLAG.LAST;
2509 i20 := G_I_PRICE_FLAG.LAST;
2510 i21 := G_I_LINE_ID.LAST;
2511 i22 := G_I_PROCESSING_ORDER.LAST;
2512 i23 := G_I_PRICING_STATUS_CODE.LAST;
2513 i24 := G_I_PRICING_STATUS_TEXT.LAST;
2514 i25 := G_I_ROUNDING_FLAG.LAST;
2515 i26 := G_I_ROUNDING_FACTOR.LAST;
2516 i27 := G_I_QUALIFIERS_EXIST_FLAG.LAST;
2517 i28 := G_I_PRICING_ATTRS_EXIST_FLAG.LAST;
2518 i29 := G_I_PRICE_LIST_ID.LAST;
2519 i30 := G_I_VALIDATED_FLAG.LAST;
2520 i31 := G_I_PRICE_REQUEST_CODE.LAST;
2521 i32 := G_I_USAGE_PRICING_TYPE.LAST;
2522 i33 := G_I_LINE_CATEGORY.LAST;
2523
2524 fte_freight_pricing_util.print_msg(l_log_level,'---last index---');
2525 fte_freight_pricing_util.print_msg(l_log_level,'i1 = '||i1);
2526 if (i2 <> i1) then
2527 fte_freight_pricing_util.print_msg(l_log_level,'i2 = '||i2);
2528 end if;
2529 if (i3 <> i1) then
2530 fte_freight_pricing_util.print_msg(l_log_level,'i3 = '||i3);
2531 end if;
2532 if (i4 <> i1) then
2533 fte_freight_pricing_util.print_msg(l_log_level,'i4 = '||i4);
2534 end if;
2535 if (i5 <> i1) then
2536 fte_freight_pricing_util.print_msg(l_log_level,'i5 = '||i5);
2537 end if;
2538 if (i6 <> i1) then
2539 fte_freight_pricing_util.print_msg(l_log_level,'i6 = '||i6);
2540 end if;
2541 if (i7 <> i1) then
2542 fte_freight_pricing_util.print_msg(l_log_level,'i7 = '||i7);
2543 end if;
2544 if (i8 <> i1) then
2545 fte_freight_pricing_util.print_msg(l_log_level,'i8 = '||i8);
2546 end if;
2547 if (i9 <> i1) then
2548 fte_freight_pricing_util.print_msg(l_log_level,'i9 = '||i9);
2549 end if;
2550 if (i10 <> i1) then
2551 fte_freight_pricing_util.print_msg(l_log_level,'i10 = '||i10);
2552 end if;
2553 if (i11 <> i1) then
2554 fte_freight_pricing_util.print_msg(l_log_level,'i11 = '||i11);
2555 end if;
2556 if (i12 <> i1) then
2557 fte_freight_pricing_util.print_msg(l_log_level,'i12 = '||i12);
2558 end if;
2559 if (i13 <> i1) then
2560 fte_freight_pricing_util.print_msg(l_log_level,'i13 = '||i13);
2561 end if;
2562 if (i14 <> i1) then
2563 fte_freight_pricing_util.print_msg(l_log_level,'i14 = '||i14);
2564 end if;
2565 if (i15 <> i1) then
2566 fte_freight_pricing_util.print_msg(l_log_level,'i15 = '||i15);
2567 end if;
2568 if (i16 <> i1) then
2569 fte_freight_pricing_util.print_msg(l_log_level,'i16 = '||i16);
2570 end if;
2571 if (i17 <> i1) then
2572 fte_freight_pricing_util.print_msg(l_log_level,'i17 = '||i17);
2573 end if;
2574 if (i18 <> i1) then
2575 fte_freight_pricing_util.print_msg(l_log_level,'i18 = '||i18);
2576 end if;
2577 if (i19 <> i1) then
2578 fte_freight_pricing_util.print_msg(l_log_level,'i19 = '||i19);
2579 end if;
2580 if (i20 <> i1) then
2581 fte_freight_pricing_util.print_msg(l_log_level,'i20 = '||i20);
2582 end if;
2583 if (i21 <> i1) then
2584 fte_freight_pricing_util.print_msg(l_log_level,'i21 = '||i21);
2585 end if;
2586 if (i22 <> i1) then
2587 fte_freight_pricing_util.print_msg(l_log_level,'i22 = '||i22);
2588 end if;
2589 if (i23 <> i1) then
2590 fte_freight_pricing_util.print_msg(l_log_level,'i23 = '||i23);
2591 end if;
2592 if (i24 <> i1) then
2593 fte_freight_pricing_util.print_msg(l_log_level,'i24 = '||i24);
2594 end if;
2595 if (i25 <> i1) then
2596 fte_freight_pricing_util.print_msg(l_log_level,'i25 = '||i25);
2597 end if;
2598 if (i26 <> i1) then
2599 fte_freight_pricing_util.print_msg(l_log_level,'i26 = '||i26);
2600 end if;
2601 if (i27 <> i1) then
2602 fte_freight_pricing_util.print_msg(l_log_level,'i27 = '||i27);
2603 end if;
2604 if (i28 <> i1) then
2605 fte_freight_pricing_util.print_msg(l_log_level,'i28 = '||i28);
2606 end if;
2607 if (i29 <> i1) then
2608 fte_freight_pricing_util.print_msg(l_log_level,'i29 = '||i29);
2609 end if;
2610 if (i30 <> i1) then
2611 fte_freight_pricing_util.print_msg(l_log_level,'i30 = '||i30);
2612 end if;
2613 if (i31 <> i1) then
2614 fte_freight_pricing_util.print_msg(l_log_level,'i31 = '||i31);
2615 end if;
2616 if (i32 <> i1) then
2617 fte_freight_pricing_util.print_msg(l_log_level,'i32 = '||i32);
2618 end if;
2619 if (i33 <> i1) then
2620 fte_freight_pricing_util.print_msg(l_log_level,'i33 = '||i33);
2621 end if;
2622
2623 fte_freight_pricing_util.unset_method(l_log_level,l_method_name);
2624 END peek_qp_input_line;
2625
2626 PROCEDURE call_qp_api (
2627 x_qp_output_line_rows OUT NOCOPY QP_PREQ_GRP.LINE_TBL_TYPE,
2628 x_qp_output_detail_rows OUT NOCOPY QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE,
2629 x_return_status OUT NOCOPY VARCHAR2)
2630 IS
2631 l_return_status_text VARCHAR2(240);
2632 l_return_status VARCHAR2(1);
2633
2634 start_time DATE;
2635 end_time DATE;
2636
2637 l_log_level NUMBER := fte_freight_pricing_util.G_LOG;
2638 l_method_name VARCHAR2(50) := 'call_qp_api';
2639 BEGIN
2640 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2641 fte_freight_pricing_util.reset_dbg_vars;
2642 fte_freight_pricing_util.set_method(l_log_level,'call_qp_api');
2643
2644 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,
2645 'G_I_LINE_INDEX.count = '||G_I_LINE_INDEX.count);
2646 print_qp_input();
2647
2648 -- peek_qp_input_line();
2649
2650 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_LOG,'CALLING QP ENGINE '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
2651 start_time := sysdate;
2652
2653 --set request_id
2654 QP_PRICE_REQUEST_CONTEXT.set_request_id();
2655
2656 IF G_I_LINE_INDEX.COUNT > 0 THEN
2657 QP_PREQ_GRP.INSERT_LINES2(
2658 p_LINE_INDEX => G_I_LINE_INDEX,
2659 p_LINE_TYPE_CODE => G_I_LINE_TYPE_CODE,
2660 p_PRICING_EFFECTIVE_DATE => G_I_PRICING_EFFECTIVE_DATE,
2661 p_ACTIVE_DATE_FIRST => G_I_ACTIVE_DATE_FIRST,
2662 p_ACTIVE_DATE_FIRST_TYPE => G_I_ACTIVE_DATE_FIRST_TYPE,
2663 p_ACTIVE_DATE_SECOND => G_I_ACTIVE_DATE_SECOND,
2664 p_ACTIVE_DATE_SECOND_TYPE => G_I_ACTIVE_DATE_SECOND_TYPE,
2665 p_LINE_QUANTITY => G_I_LINE_QUANTITY,
2666 p_LINE_UOM_CODE => G_I_LINE_UOM_CODE,
2667 p_REQUEST_TYPE_CODE => G_I_REQUEST_TYPE_CODE,
2668 p_PRICED_QUANTITY => G_I_PRICED_QUANTITY,
2669 p_PRICED_UOM_CODE => G_I_PRICED_UOM_CODE,
2670 p_CURRENCY_CODE => G_I_CURRENCY_CODE,
2671 p_UNIT_PRICE => G_I_UNIT_PRICE,
2672 p_PERCENT_PRICE => G_I_PERCENT_PRICE,
2673 p_UOM_QUANTITY => G_I_UOM_QUANTITY,
2674 p_ADJUSTED_UNIT_PRICE => G_I_ADJUSTED_UNIT_PRICE,
2675 p_UPD_ADJUSTED_UNIT_PRICE => G_I_UPD_ADJUSTED_UNIT_PRICE,
2676 p_PROCESSED_FLAG => G_I_PROCESSED_FLAG,
2677 p_PRICE_FLAG => G_I_PRICE_FLAG,
2678 p_LINE_ID => G_I_LINE_ID,
2679 p_PROCESSING_ORDER => G_I_PROCESSING_ORDER,
2680 p_PRICING_STATUS_CODE => G_I_PRICING_STATUS_CODE,
2681 p_PRICING_STATUS_TEXT => G_I_PRICING_STATUS_TEXT,
2682 p_ROUNDING_FLAG => G_I_ROUNDING_FLAG,
2683 p_ROUNDING_FACTOR => G_I_ROUNDING_FACTOR,
2684 p_QUALIFIERS_EXIST_FLAG => G_I_QUALIFIERS_EXIST_FLAG,
2685 p_PRICING_ATTRS_EXIST_FLAG => G_I_PRICING_ATTRS_EXIST_FLAG,
2686 p_PRICE_LIST_ID => G_I_PRICE_LIST_ID,
2687 p_VALIDATED_FLAG => G_I_VALIDATED_FLAG,
2688 p_PRICE_REQUEST_CODE => G_I_PRICE_REQUEST_CODE,
2689 p_USAGE_PRICING_TYPE => G_I_USAGE_PRICING_TYPE,
2690 p_line_category => G_I_LINE_CATEGORY,
2691 x_status_code => l_return_status,
2692 x_status_text => l_return_status_text);
2693
2694 END IF;
2695
2696 FTE_FREIGHT_PRICING_UTIL.set_location(p_loc=>'after QP_PREQ_GRP_INSERT_LINES2');
2697 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2698 raise FTE_FREIGHT_PRICING_UTIL.g_qp_insert_lines2_failed;
2699 END IF;
2700
2701 IF G_I_A_LINE_INDEX.count > 0 THEN
2702 QP_PREQ_GRP.INSERT_LINE_ATTRS2(
2703 p_LINE_INDEX_tbl => G_I_A_LINE_INDEX,
2704 p_LINE_DETAIL_INDEX_tbl => G_I_A_LINE_DETAIL_INDEX,
2705 p_ATTRIBUTE_LEVEL_tbl => G_I_A_ATTRIBUTE_LEVEL,
2706 p_ATTRIBUTE_TYPE_tbl => G_I_A_ATTRIBUTE_TYPE,
2707 p_LIST_HEADER_ID_tbl => G_I_A_LIST_HEADER_ID,
2708 p_LIST_LINE_ID_tbl => G_I_A_LIST_LINE_ID,
2709 p_CONTEXT_tbl => G_I_A_CONTEXT,
2710 p_ATTRIBUTE_tbl => G_I_A_ATTRIBUTE,
2711 p_VALUE_FROM_tbl => G_I_A_VALUE_FROM,
2712 p_SETUP_VALUE_FROM_tbl => G_I_A_SETUP_VALUE_FROM,
2713 p_VALUE_TO_tbl => G_I_A_VALUE_TO,
2714 p_SETUP_VALUE_TO_tbl => G_I_A_SETUP_VALUE_TO,
2715 p_GROUPING_NUMBER_tbl => G_I_A_GROUPING_NUMBER,
2716 p_NO_QUALIFIERS_IN_GRP_tbl => G_I_A_NO_QUALIFIERS_IN_GRP,
2717 p_COMPARISON_OPERATOR_TYPE_tbl => G_I_A_COMPARISON_OPERATOR_TYPE,
2718 p_VALIDATED_FLAG_tbl => G_I_A_VALIDATED_FLAG,
2719 p_APPLIED_FLAG_tbl => G_I_A_APPLIED_FLAG,
2720 p_PRICING_STATUS_CODE_tbl => G_I_A_PRICING_STATUS_CODE,
2721 p_PRICING_STATUS_TEXT_tbl => G_I_A_PRICING_STATUS_TEXT,
2722 p_QUALIFIER_PRECEDENCE_tbl => G_I_A_QUALIFIER_PRECEDENCE,
2723 p_DATATYPE_tbl => G_I_A_DATATYPE,
2724 p_PRICING_ATTR_FLAG_tbl => G_I_A_PRICING_ATTR_FLAG,
2725 p_QUALIFIER_TYPE_tbl => G_I_A_QUALIFIER_TYPE,
2726 p_PRODUCT_UOM_CODE_TBL => G_I_A_PRODUCT_UOM_CODE,
2727 p_EXCLUDER_FLAG_TBL => G_I_A_EXCLUDER_FLAG,
2728 p_PRICING_PHASE_ID_TBL => G_I_A_PRICING_PHASE_ID,
2729 p_INCOMPATABILITY_GRP_CODE_TBL => G_I_A_INCOMPATABILITY_GRP_CODE,
2730 p_LINE_DETAIL_TYPE_CODE_TBL => G_I_A_LINE_DETAIL_TYPE_CODE,
2731 p_MODIFIER_LEVEL_CODE_TBL => G_I_A_MODIFIER_LEVEL_CODE,
2732 p_PRIMARY_UOM_FLAG_TBL => G_I_A_PRIMARY_UOM_FLAG,
2733 x_status_code => l_return_status,
2734 x_status_text => l_return_status_text);
2735
2736 end if;
2737
2738 FTE_FREIGHT_PRICING_UTIL.set_location(p_loc=>'after QP_PREQ_GRP_INSERT_LINE_ATTRS2');
2739 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2740 raise FTE_FREIGHT_PRICING_UTIL.g_qp_insert_line_attrs2_failed;
2741 END IF;
2742
2743 --The pricing engine output is in the temporary table after this call
2744 QP_PREQ_PUB.PRICE_REQUEST (
2745 g_I_control_rec,
2746 l_return_status,
2747 l_return_status_text);
2748
2749 FTE_FREIGHT_PRICING_UTIL.set_location(p_loc=>'after QP_PREQ_PUB.PRICE_REQUEST');
2750 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2751 raise FTE_FREIGHT_PRICING_UTIL.g_qp_price_request_failed;
2752 ELSE
2753 FTE_FREIGHT_PRICING_UTIL.print_msg(fte_freight_pricing_util.G_DBG,'qp_price_request_finished. Return status = '||l_return_status);
2754 FTE_FREIGHT_PRICING_UTIL.print_msg(fte_freight_pricing_util.G_DBG,'Return status text = '||l_return_status_text);
2755 END IF;
2756
2757 populate_qp_output(
2758 x_qp_output_line_rows => x_qp_output_line_rows,
2759 x_qp_output_detail_rows => x_qp_output_detail_rows,
2760 x_return_status => l_return_status);
2761
2762 FTE_FREIGHT_PRICING_UTIL.set_location(p_loc=>'after populate qp output');
2763 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2764 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2765 raise FTE_FREIGHT_PRICING_UTIL.g_qp_price_request_failed;
2766 END IF;
2767 END IF;
2768
2769 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_LOG,'AFTER CALL TO QP ENGINE '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
2770 end_time := sysdate;
2771 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_LOG,'time difference '||(end_time-start_time)*24*3600 || ' seconds');
2772 --fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_LOG,'time difference '||to_char(to_date('00:00:00','HH24:MI:SS') + (end_time-start_time), 'HH24:MI:SS'));
2773
2774 fte_freight_pricing_util.unset_method(l_log_level,'call_qp_api');
2775 EXCEPTION
2776 WHEN FTE_FREIGHT_PRICING_UTIL.g_qp_price_request_failed THEN
2777 --x_return_status := l_return_status;
2778 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2779 FTE_FREIGHT_PRICING_UTIL.set_exception(l_method_name,l_log_level,'qp_price_request_failed');
2780 FTE_FREIGHT_PRICING_UTIL.print_msg(fte_freight_pricing_util.G_ERR,'Return status = '||l_return_status);
2781 FTE_FREIGHT_PRICING_UTIL.print_msg(fte_freight_pricing_util.G_ERR,'Return status text = '||l_return_status_text);
2782 fte_freight_pricing_util.unset_method(l_log_level,'call_qp_api');
2783 WHEN FTE_FREIGHT_PRICING_UTIL.g_qp_insert_lines2_failed THEN
2784 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2785 FTE_FREIGHT_PRICING_UTIL.set_exception(l_method_name,l_log_level,'qp_insert_lines2_failed');
2786 FTE_FREIGHT_PRICING_UTIL.print_msg(fte_freight_pricing_util.G_ERR,'Return status = '||l_return_status);
2787 FTE_FREIGHT_PRICING_UTIL.print_msg(fte_freight_pricing_util.G_ERR,'Return status text = '||l_return_status_text);
2788 fte_freight_pricing_util.unset_method(l_log_level,'call_qp_api');
2789 WHEN FTE_FREIGHT_PRICING_UTIL.g_qp_insert_line_attrs2_failed THEN
2790 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2791 FTE_FREIGHT_PRICING_UTIL.set_exception(l_method_name,l_log_level,'qp_insert_line_attrs2_failed');
2792 FTE_FREIGHT_PRICING_UTIL.print_msg(fte_freight_pricing_util.G_ERR,'Return status = '||l_return_status);
2793 FTE_FREIGHT_PRICING_UTIL.print_msg(fte_freight_pricing_util.G_ERR,'Return status text = '||l_return_status_text);
2794 fte_freight_pricing_util.unset_method(l_log_level,'call_qp_api');
2795 WHEN others THEN
2796 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2797 print_qp_output();
2798 FTE_FREIGHT_PRICING_UTIL.set_exception(l_method_name,l_log_level,'g_others');
2799 FTE_FREIGHT_PRICING_UTIL.print_msg(fte_freight_pricing_util.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
2800 fte_freight_pricing_util.unset_method(l_log_level,'call_qp_api');
2801
2802 END call_qp_api;
2803
2804 -- Debug methods --
2805
2806 PROCEDURE print_qp_input IS
2807 I NUMBER;
2808 l_log_level NUMBER := fte_freight_pricing_util.G_DBG;
2809 l_method_name VARCHAR2(50) := 'print_qp_input';
2810 BEGIN
2811
2812 fte_freight_pricing_util.set_method(l_log_level,l_method_name);
2813
2814 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'-----------QP Inputs-------------');
2815 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'-----------Control Record Information-------------');
2816
2817 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Pricing Event '||g_I_control_rec.pricing_event);
2818 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Calculate Flag '||g_I_control_rec.calculate_flag);
2819 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Simulation Flag '||g_I_control_rec.simulation_flag);
2820 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Rounding Flag '||g_I_control_rec.rounding_flag);
2821 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Temp Table Insert Flag '||g_I_control_rec.temp_table_insert_flag);
2822 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Request Type Code '||g_I_control_rec.request_type_code);
2823 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'---------------------------------------------------');
2824
2825 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'-------------Input Request Line Information-------------------');
2826
2827 I := g_I_line_index.FIRST;
2828 IF I IS NOT NULL THEN
2829 LOOP
2830 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'I: '||I);
2831 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Line Index: '||g_I_line_index(i));
2832 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Request Type Code : '||g_I_request_type_code(i));
2833 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Line Type Code : '||g_I_line_type_code(i));
2834 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Line Quantity : '||g_I_line_quantity(i));
2835 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Line Uom Code : '||g_I_line_uom_code(i));
2836 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Currency Code : '||g_I_currency_code(i));
2837 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Price Flag : '||g_I_price_flag(i));
2838 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Unit_price: '||g_I_unit_price(i));
2839 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Priced Quantity: '||g_I_priced_quantity(i));
2840 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Priced UOM Code: '||g_I_priced_uom_code(i));
2841 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Percent price: '||g_I_percent_price(i));
2842 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Adjusted Unit Price: '||g_I_adjusted_unit_price(i));
2843 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Pricing Effective Date : '||g_I_pricing_effective_date(i));
2844 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Active Date First: '||g_I_active_date_first(i));
2845 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Active Date Second: '||g_I_active_date_second(i));
2846 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Pricing status code: '||g_I_pricing_status_code(i));
2847 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Pricing status text: '||g_I_pricing_status_text(i));
2848 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'---------------------------------------------------');
2849 EXIT WHEN I = g_I_line_index.LAST;
2850 I := g_I_line_index.NEXT(I);
2851 END LOOP;
2852 END IF;
2853
2854 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'----------- Input Line Extras Table ---------------------');
2855 I := g_I_line_extras_tbl.FIRST;
2856 IF I IS NOT NULL THEN
2857 LOOP
2858 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'I: '||I);
2859 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Line Index: '||g_I_line_extras_tbl(I).line_index);
2860 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'input_set_number : '||g_I_line_extras_tbl(I).input_set_number);
2861 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'category_id : '||g_I_line_extras_tbl(I).category_id);
2862 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'---------------------------------------------------');
2863
2864 EXIT WHEN I = g_I_line_extras_tbl.LAST;
2865 I := g_I_line_extras_tbl.NEXT(I);
2866 END LOOP;
2867 END IF;
2868 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'----------- Input Pricing Attributes and qualifiers Information-------------');
2869
2870 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'g_I_A_line_index.COUNT:'||g_I_A_line_index.COUNT);
2871
2872 I := g_I_A_line_index.FIRST;
2873 IF I IS NOT NULL THEN
2874 LOOP
2875
2876 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'I: '||I);
2877 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Line Index '||g_I_A_line_index(i));
2878 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Context '||g_I_A_context(i));
2879 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Attribute Type '||g_I_A_attribute_type(i));
2880 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Attribute '||g_I_A_attribute(i));
2881 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Value From '||g_I_A_value_from(i));
2882 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Validated Flag '||g_I_A_VALIDATED_FLAG(i));
2883 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'---------------------------------------------------');
2884
2885 EXIT WHEN I = g_I_A_line_index.last;
2886 I:=g_I_A_line_index.NEXT(I);
2887
2888 END LOOP;
2889 END IF;
2890
2891 fte_freight_pricing_util.unset_method(l_log_level,l_method_name);
2892 END print_qp_input;
2893
2894 PROCEDURE print_qp_output IS
2895 I NUMBER;
2896 l_log_level NUMBER := fte_freight_pricing_util.G_DBG;
2897 l_method_name VARCHAR2(50) := 'print_qp_output';
2898 BEGIN
2899 fte_freight_pricing_util.set_method(l_log_level,l_method_name);
2900
2901 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'-------------Output Request Line Information-------------------');
2902
2903 I := g_O_line_tbl.FIRST;
2904 IF I IS NOT NULL THEN
2905 LOOP
2906 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'I: '||I);
2907 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Line Index: '||g_O_line_tbl(I).line_index);
2908 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Request Type Code : '||g_O_line_tbl(I).request_type_code);
2909 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Line Type Code : '||g_O_line_tbl(I).line_type_code);
2910 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Line Quantity : '||g_O_line_tbl(I).line_quantity);
2911 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Line Uom Code : '||g_O_line_tbl(I).line_uom_code);
2912 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Currency Code : '||g_O_line_tbl(I).currency_code);
2913 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Priced Quantity : '||g_O_line_tbl(I).priced_quantity);
2914 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Priced Uom Code : '||g_O_line_tbl(I).priced_uom_code);
2915 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Price Flag : '||g_O_line_tbl(I).price_flag);
2916 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Unit_price: '||g_O_line_tbl(I).unit_price);
2917 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Percent price: '||g_O_line_tbl(I).percent_price);
2918 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Adjusted Unit Price: '||g_O_line_tbl(I).adjusted_unit_price);
2919 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Extended Price: '||g_O_line_tbl(I).extended_price);
2920 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Pricing status code: '||g_O_line_tbl(I).status_code);
2921 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Pricing status text: '||g_O_line_tbl(I).status_text);
2922 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'---------------------------------------------------');
2923 EXIT WHEN I = g_O_line_tbl.LAST;
2924 I := g_O_line_tbl.NEXT(I);
2925 END LOOP;
2926 END IF;
2927
2928 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'------------Price List/Discount Information------------');
2929
2930 I := g_O_line_detail_tbl.FIRST;
2931 IF I IS NOT NULL THEN
2932 LOOP
2933 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'I: '||I);
2934 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Line Index: '||g_O_line_detail_tbl(I).line_index);
2935 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Line Detail Index: '||g_O_line_detail_tbl(I).line_detail_index);
2936 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Line Detail Type:'||g_O_line_detail_tbl(I).line_detail_type_code);
2937 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'List Header Id: '||g_O_line_detail_tbl(I).list_header_id);
2938 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'List Line Id: '||g_O_line_detail_tbl(I).list_line_id);
2939 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'List Line Type Code: '||g_O_line_detail_tbl(I).list_line_type_code);
2940 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Adjustment Amount : '||g_O_line_detail_tbl(I).adjustment_amount);
2941 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Line Quantity : '||g_O_line_detail_tbl(I).line_quantity);
2942 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Operand Calculation Code: '||g_O_line_detail_tbl(I).Operand_calculation_code);
2943 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Operand value: '||g_O_line_detail_tbl(I).operand_value);
2944 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Automatic Flag: '||g_O_line_detail_tbl(I).automatic_flag);
2945 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Override Flag: '||g_O_line_detail_tbl(I).override_flag);
2946 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'ChargeTypeCode : '||g_O_line_detail_tbl(I).charge_type_code);
2947 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'ChargeSubTypeCode : '||g_O_line_detail_tbl(I).charge_subtype_code);
2948 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'status_code: '||g_O_line_detail_tbl(I).status_code);
2949 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'status text: '||g_O_line_detail_tbl(I).status_text);
2950 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'-------------------------------------------');
2951 EXIT WHEN I = g_O_line_detail_tbl.LAST;
2952 I := g_O_line_detail_tbl.NEXT(I);
2953 END LOOP;
2954 END IF;
2955 fte_freight_pricing_util.unset_method(l_log_level,l_method_name);
2956 END print_qp_output;
2957
2958 --
2959 -- Added for TL rating
2960 -- Clears variables used for input to and output from qp
2961 -- Should to be executed before creating inputs for a new call
2962 --
2963
2964 PROCEDURE clear_globals (
2965 x_return_status OUT NOCOPY VARCHAR2)
2966 IS
2967 l_return_status VARCHAR2(1);
2968 l_log_level NUMBER := fte_freight_pricing_util.G_DBG;
2969 l_method_name VARCHAR2(50) := 'CLEAR_GLOBALS';
2970 BEGIN
2971 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2972 fte_freight_pricing_util.reset_dbg_vars;
2973 fte_freight_pricing_util.set_method(l_log_level,l_method_name);
2974
2975 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Before ->G_I_LINE_INDEX.COUNT = '||G_I_LINE_INDEX.COUNT);
2976 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Before ->g_O_line_tbl.COUNT = '||g_O_line_tbl.COUNT);
2977 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'Before ->g_O_line_detail_tbl.COUNT = '||g_O_line_detail_tbl.COUNT);
2978
2979 G_I_LINE_INDEX.DELETE;
2980 G_I_LINE_TYPE_CODE.DELETE;
2981 G_I_PRICING_EFFECTIVE_DATE.DELETE;
2982 G_I_ACTIVE_DATE_FIRST.DELETE;
2983 G_I_ACTIVE_DATE_FIRST_TYPE.DELETE;
2984 G_I_ACTIVE_DATE_SECOND.DELETE;
2985 G_I_ACTIVE_DATE_SECOND_TYPE.DELETE;
2986 G_I_LINE_QUANTITY.DELETE;
2987 G_I_LINE_UOM_CODE.DELETE;
2988 G_I_REQUEST_TYPE_CODE.DELETE;
2989 G_I_PRICED_QUANTITY.DELETE;
2990 G_I_PRICED_UOM_CODE.DELETE;
2991 G_I_CURRENCY_CODE.DELETE;
2992 G_I_UNIT_PRICE.DELETE;
2993 G_I_PERCENT_PRICE.DELETE;
2994 G_I_UOM_QUANTITY.DELETE;
2995 G_I_ADJUSTED_UNIT_PRICE.DELETE;
2996 G_I_UPD_ADJUSTED_UNIT_PRICE.DELETE;
2997 G_I_PROCESSED_FLAG.DELETE;
2998 G_I_PRICE_FLAG.DELETE;
2999 G_I_LINE_ID.DELETE;
3000 G_I_PROCESSING_ORDER.DELETE;
3001 G_I_PRICING_STATUS_CODE.DELETE;
3002 G_I_PRICING_STATUS_TEXT.DELETE;
3003 G_I_ROUNDING_FLAG.DELETE;
3004 G_I_ROUNDING_FACTOR.DELETE;
3005 G_I_QUALIFIERS_EXIST_FLAG.DELETE;
3006 G_I_PRICING_ATTRS_EXIST_FLAG.DELETE;
3007 G_I_PRICE_LIST_ID.DELETE;
3008 G_I_VALIDATED_FLAG.DELETE;
3009 G_I_PRICE_REQUEST_CODE.DELETE;
3010 G_I_USAGE_PRICING_TYPE.DELETE;
3011 G_I_LINE_CATEGORY.DELETE;
3012
3013 G_I_A_LINE_INDEX.DELETE;
3014 G_I_A_LINE_DETAIL_INDEX.DELETE;
3015 G_I_A_ATTRIBUTE_LEVEL.DELETE;
3016 G_I_A_ATTRIBUTE_TYPE.DELETE;
3017 G_I_A_LIST_HEADER_ID.DELETE;
3018 G_I_A_LIST_LINE_ID.DELETE;
3019 G_I_A_CONTEXT.DELETE;
3020 G_I_A_ATTRIBUTE.DELETE;
3021 G_I_A_VALUE_FROM.DELETE;
3022 G_I_A_SETUP_VALUE_FROM.DELETE;
3023 G_I_A_VALUE_TO.DELETE;
3024 G_I_A_SETUP_VALUE_TO.DELETE;
3025 G_I_A_GROUPING_NUMBER.DELETE;
3026 G_I_A_NO_QUALIFIERS_IN_GRP.DELETE;
3027 G_I_A_COMPARISON_OPERATOR_TYPE.DELETE;
3028 G_I_A_VALIDATED_FLAG.DELETE;
3029 G_I_A_APPLIED_FLAG.DELETE;
3030 G_I_A_PRICING_STATUS_CODE.DELETE;
3031 G_I_A_PRICING_STATUS_TEXT.DELETE;
3032 G_I_A_QUALIFIER_PRECEDENCE.DELETE;
3033 G_I_A_DATATYPE.DELETE;
3034 G_I_A_PRICING_ATTR_FLAG.DELETE;
3035 G_I_A_QUALIFIER_TYPE.DELETE;
3036 G_I_A_PRODUCT_UOM_CODE.DELETE;
3037 G_I_A_EXCLUDER_FLAG.DELETE;
3038 G_I_A_PRICING_PHASE_ID.DELETE;
3039 G_I_A_INCOMPATABILITY_GRP_CODE.DELETE;
3040 G_I_A_LINE_DETAIL_TYPE_CODE.DELETE;
3041 G_I_A_MODIFIER_LEVEL_CODE.DELETE;
3042 G_I_A_PRIMARY_UOM_FLAG.DELETE;
3043
3044 --g_I_control_rec := null;
3045 g_I_line_extras_tbl.DELETE;
3046
3047 -- output from QP
3048 g_O_line_tbl.DELETE;
3049 g_O_line_detail_tbl.DELETE;
3050
3051 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'After ->G_I_LINE_INDEX.COUNT = '||G_I_LINE_INDEX.COUNT);
3052 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'After ->g_O_line_tbl.COUNT = '||g_O_line_tbl.COUNT);
3053 fte_freight_pricing_util.print_msg(fte_freight_pricing_util.G_DBG,'After ->g_O_line_detail_tbl.COUNT = '||g_O_line_detail_tbl.COUNT);
3054 fte_freight_pricing_util.unset_method(l_log_level,l_method_name);
3055 EXCEPTION
3056 WHEN others THEN
3057 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3058 FTE_FREIGHT_PRICING_UTIL.set_exception(l_method_name,l_log_level,'g_others');
3059 FTE_FREIGHT_PRICING_UTIL.print_msg(fte_freight_pricing_util.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
3060 fte_freight_pricing_util.unset_method(l_log_level,l_method_name);
3061 END clear_globals;
3062
3063 /* template for a procedure
3064 --
3065 PROCEDURE (
3066 x_return_status OUT NOCOPY VARCHAR2)
3067 IS
3068 l_return_status VARCHAR2(1);
3069 l_log_level NUMBER := fte_freight_pricing_util.G_DBG;
3070 l_method_name VARCHAR2(50) := '';
3071 BEGIN
3072 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3073 fte_freight_pricing_util.reset_dbg_vars;
3074 fte_freight_pricing_util.set_method(l_log_level,l_method_name);
3075
3076
3077 fte_freight_pricing_util.unset_method(l_log_level,l_method_name);
3078 EXCEPTION
3079 WHEN others THEN
3080 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3081 FTE_FREIGHT_PRICING_UTIL.set_exception(l_method_name,l_log_level,'g_others');
3082 FTE_FREIGHT_PRICING_UTIL.print_msg(fte_freight_pricing_util.G_ERR,'Unexpected Error : '||SQLCODE||' : '||SQLERRM);
3083 fte_freight_pricing_util.unset_method(l_log_level,l_method_name);
3084 END ;
3085 */
3086
3087 -- package initialization
3088 -- should be executed only once when the package is loaded into memory
3089 BEGIN
3090 g_engine_defaults_tab.DELETE;
3091
3092 -- Load the defaults table
3093 /* -- Uncomment for TL testing and comment out original block
3094 g_engine_defaults_tab(G_LINE_EVENT_NUM).pricing_event_num := G_LINE_EVENT_NUM;
3095 g_engine_defaults_tab(G_LINE_EVENT_NUM).pricing_event_code := 'BATCH';
3096 g_engine_defaults_tab(G_LINE_EVENT_NUM).request_type_code := 'ONT';
3097 g_engine_defaults_tab(G_LINE_EVENT_NUM).line_type_code := 'LINE';
3098 g_engine_defaults_tab(G_LINE_EVENT_NUM).price_flag := 'Y';
3099
3100 g_engine_defaults_tab(G_CHARGE_EVENT_NUM).pricing_event_num := G_CHARGE_EVENT_NUM;
3101 g_engine_defaults_tab(G_CHARGE_EVENT_NUM).pricing_event_code := G_CHARGE_EVENT_CODE;
3102 g_engine_defaults_tab(G_CHARGE_EVENT_NUM).request_type_code := 'ONT';
3103 g_engine_defaults_tab(G_CHARGE_EVENT_NUM).line_type_code := 'LINE';
3104 g_engine_defaults_tab(G_CHARGE_EVENT_NUM).price_flag := 'Y';
3105 */
3106
3107 -- Load the defaults table
3108 g_engine_defaults_tab(G_LINE_EVENT_NUM).pricing_event_num := G_LINE_EVENT_NUM;
3109 g_engine_defaults_tab(G_LINE_EVENT_NUM).pricing_event_code := G_LINE_EVENT_CODE;
3110 g_engine_defaults_tab(G_LINE_EVENT_NUM).request_type_code := 'FTE';
3111 g_engine_defaults_tab(G_LINE_EVENT_NUM).line_type_code := 'LINE';
3112 g_engine_defaults_tab(G_LINE_EVENT_NUM).price_flag := 'Y';
3113
3114 g_engine_defaults_tab(G_CHARGE_EVENT_NUM).pricing_event_num := G_CHARGE_EVENT_NUM;
3115 g_engine_defaults_tab(G_CHARGE_EVENT_NUM).pricing_event_code := G_CHARGE_EVENT_CODE;
3116 g_engine_defaults_tab(G_CHARGE_EVENT_NUM).request_type_code := 'FTE';
3117 g_engine_defaults_tab(G_CHARGE_EVENT_NUM).line_type_code := 'LINE';
3118 g_engine_defaults_tab(G_CHARGE_EVENT_NUM).price_flag := 'Y';
3119
3120 END FTE_QP_ENGINE;