1 PACKAGE BODY QP_FORMULA_PRICE_CALC_PVT AS
2 /* $Header: QPXVCALB.pls 120.2.12010000.2 2008/10/16 12:27:52 smbalara ship $ */
3
4 l_debug VARCHAR2(3);
5 -----------------------------------------------------
6 /* The following is a customizable Public Function */
7 -----------------------------------------------------
8 FUNCTION Get_Custom_Price(p_price_formula_id IN NUMBER,
9 p_list_price IN NUMBER,
10 p_price_effective_date IN DATE,
11 p_req_line_attrs_tbl IN QP_FORMULA_PRICE_CALC_PVT.REQ_LINE_ATTRS_TBL)
12 RETURN NUMBER
13 IS
14 BEGIN
15 RETURN NULL;
16 END;
17
18
19 -----------------------------------------------------------------
20 -- Wrapper for Get_Custom_Price called by Java Formula Engine
21 -- Since Java cannot pass in a table of records, use this wrapper
22 -- to construct table of records from the new INT tables, then
23 -- call Get_Custom_Price API
24 -----------------------------------------------------------------
25 FUNCTION Java_Custom_Price(p_price_formula_id IN NUMBER,
26 p_list_price IN NUMBER,
27 p_price_effective_date IN DATE,
28 p_line_index IN NUMBER,
29 p_request_id IN NUMBER)
30 RETURN NUMBER
31 IS
32 CURSOR req_line_attrs_cur(a_line_index NUMBER)
33 IS
34 SELECT line_index, attribute_type, context, attribute, value_from value
35 FROM qp_int_line_attrs_t lattr
36 WHERE request_id = p_request_id
37 AND line_index = p_line_index
38 AND attribute_type IN ('PRICING','PRODUCT')
39 AND pricing_status_code = QP_PREQ_GRP.G_STATUS_UNCHANGED;
40
41 -- qual cursor
42 CURSOR req_line_attrs_qual_cur(a_line_index NUMBER)
43 IS
44 SELECT line_index, attribute_type, context, attribute, value_from value
45 FROM qp_int_line_attrs_t lattr
46 WHERE request_id = p_request_id
47 AND line_index = p_line_index
48 AND attribute_type IN ('PRICING','PRODUCT','QUALIFIER')
49 AND pricing_status_code = QP_PREQ_GRP.G_STATUS_UNCHANGED;
50
51 l_pass_qualifiers VARCHAR2(10) := FND_PROFILE.VALUE('QP_PASS_QUALIFIERS_TO_GET_CUSTOM');
52 l_req_line_attrs_tbl QP_FORMULA_PRICE_CALC_PVT.REQ_LINE_ATTRS_TBL;
53 i NUMBER := 1;
54 BEGIN
55 IF nvl(l_pass_qualifiers, 'N') = 'N' THEN
56 FOR l_line_attrs_rec IN req_line_attrs_cur(p_line_index) LOOP
57 l_req_line_attrs_tbl(i) := l_line_attrs_rec;
58 i := i+1;
59 END LOOP;
60 ELSE
61 FOR l_line_attrs_rec IN req_line_attrs_qual_cur(p_line_index) LOOP
62 l_req_line_attrs_tbl(i) := l_line_attrs_rec;
63 i := i+1;
64 END LOOP;
65 END IF;
66
67 RETURN QP_CUSTOM.Get_Custom_Price(p_price_formula_id, p_list_price,
68 p_price_effective_date, l_req_line_attrs_tbl);
69 END Java_Custom_Price;
70
71
72 -------------------------------------------------------------------
73 /* The following is a public procedure to parse a formula for
74 arithmetic correctness even before substituting the step numbers
75 in it with the corresponding values */
76 -------------------------------------------------------------------
77 PROCEDURE Parse_Formula(p_formula IN VARCHAR2,
78 x_return_status OUT NOCOPY VARCHAR2)
79 IS
80 l_cursor INTEGER;
81
82 BEGIN
83
84 x_return_status := FND_API.G_RET_STS_SUCCESS;
85
86 EXECUTE IMMEDIATE 'SELECT ' || p_formula || ' FROM DUAL';
87
88 EXCEPTION
89 WHEN FND_API.G_EXC_ERROR THEN
90
91 x_return_status := FND_API.G_RET_STS_ERROR;
92
93 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
94 THEN
95 fnd_message.set_name('QP','QP_FORMULA_NOT_FOUND');
96 -- FND_MSG_PUB.Add;
97 END IF;
98
99 -- RAISE;
100
101 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
102
103 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
104
105 -- RAISE;
106
107 WHEN OTHERS THEN
108
109 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
110
111 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
112 THEN
113 FND_MSG_PUB.Add_Exc_Msg
114 (G_PKG_NAME
115 , 'Parse Formula'
116 );
117 END IF;
118
119 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
120
121 END Parse_Formula;
122
123
124 -----------------------------------------------------------------------------
125 -- This function takes a formula string and substitutes the step numbers with
126 -- corresponding operand values, then evaluates the resulting expression in
127 -- the SQL engine to produce a result
128 -----------------------------------------------------------------------------
129 FUNCTION Select_From_Dual(p_formula IN VARCHAR2,
130 p_operand_tbl IN QP_FORMULA_RULES_PVT.T_OPERAND_TBL_TYPE)
131 RETURN NUMBER
132 IS
133 l_char VARCHAR2(1) := '';
134 l_number VARCHAR2(2000) := '';
135 l_new_formula VARCHAR2(20000) := '';
136 l_select_stmt VARCHAR2(20000) := '';
137 l_component_value NUMBER := NULL;
138 i NUMBER;
139 l_formula_value NUMBER;
140 BEGIN
141 FOR i IN 1..LENGTH(p_formula) LOOP
142 l_char := SUBSTR(p_formula, i, 1);
143 IF (l_char = '0') OR (l_char = '1') OR (l_char = '2') OR (l_char = '3') OR
144 (l_char = '4') OR (l_char = '5') OR (l_char = '6') OR (l_char = '7') OR
145 (l_char = '8') OR (l_char = '9')
146 THEN
147 --If retrieved character is a digit
148 l_number := l_number || l_char;
149 IF i = LENGTH(p_formula) THEN
150 BEGIN
151 l_component_value := p_operand_tbl(l_number);
152 EXCEPTION
153 WHEN OTHERS THEN
154 l_component_value := null;
155 END;
156 l_new_formula := l_new_formula||'TO_NUMBER('''||TO_CHAR(l_component_value)||''')';
157 l_number := '';
158 END IF;
159 ELSE -- If character is not a number
160 IF l_number IS NOT NULL THEN
161 -- Convert number to step_number and append the component value of
162 -- that step_number to new_formula
163 BEGIN
164 l_component_value := p_operand_tbl(l_number);
165 EXCEPTION
166 WHEN OTHERS THEN
167 l_component_value := null;
168 END;
169 l_new_formula := l_new_formula||'TO_NUMBER('''||TO_CHAR(l_component_value)||''')';
170 l_number := '';
171 END IF;
172 l_new_formula := l_new_formula || l_char;
173 END IF; -- If the character is a number or not
174 END LOOP; -- Loop through every character in the Formula String
175
176 IF l_debug = FND_API.G_TRUE THEN
177 QP_PREQ_GRP.engine_debug('Formula is ' || l_new_formula);
178 END IF;
179
180 l_select_stmt := 'SELECT '|| l_new_formula || ' FROM DUAL';
181 EXECUTE IMMEDIATE l_select_stmt INTO l_formula_value;
182 RETURN l_formula_value;
183 END Select_From_Dual;
184
185
186 -------------------------------------------------------------------
187 /* The following is a function to calculate the value of a
188 formula expression. This function combines the former 2 functions -
189 Calculate and Component_Value into one function Calculate.
190 This has been introduced as part of Performance Tuning for
191 POSCO, post-1806928 (07/23/2001 and after) */
192 -------------------------------------------------------------------
193 FUNCTION Calculate (p_price_formula_id IN NUMBER,
194 p_list_price IN NUMBER,
195 p_price_effective_date IN DATE,
196 --p_req_line_attrs_tbl IN REQ_LINE_ATTRS_TBL,
197 p_line_index IN NUMBER,
198 p_list_line_type_code IN VARCHAR2,
199 --Added parameters p_line_index and p_list_line_type_code
200 --and commented out parameter p_req_line_attrs_tbl.
201 --POSCO performance related.
202 x_return_status OUT NOCOPY VARCHAR2,
203 p_modifier_value IN NUMBER default NULL) --mkarya for bug 1906545
204 RETURN NUMBER
205 IS
206
207 /*
208 INDX,QP_FORMULA_PRICE_CALC_PVT.calculate.formula_lines_cur,QP_PRICE_FORMULA_LINES_U1,PRICE_FORMULA_ID,1
209 */
210 CURSOR formula_lines_cur (a_price_formula_id NUMBER)
211 IS
212 SELECT step_number, price_formula_line_type_code, numeric_constant,
213 pricing_attribute, pricing_attribute_context,
214 price_formula_id, price_list_line_id
215 FROM qp_price_formula_lines
216 WHERE price_formula_id = a_price_formula_id;
217
218 --Modified factors_cur to incorporate search_ind for pricing_attributes.
219 --POSCO performance related.
220 /*
221 INDX,QP_FORMULA_PRICE_CALC_PVT.calculate.factors_cur,QP_PRICE_FORMULA_LINES_N1,PRICE_FORMULA_LINE_TYPE_CODE,1
222 INDX,QP_FORMULA_PRICE_CALC_PVT.calculate.factors_cur,QP_PRICE_FORMULA_LINES_N1,PRICE_FORMULA_ID,2
223 INDX,QP_FORMULA_PRICE_CALC_PVT.calculate.factors_cur,QP_FACTOR_LIST_ATTRS_N1,LIST_HEADER_ID,1
224 INDX,QP_FORMULA_PRICE_CALC_PVT.calculate.factors_cur,QP_PREQ_LINE_ATTRS_FRML_TMP_N1,PRICING_STATUS_CODE,1
225 INDX,QP_FORMULA_PRICE_CALC_PVT.calculate.factors_cur,QP_PREQ_LINE_ATTRS_FRML_TMP_N1,ATTRIBUTE_TYPE,2
226 INDX,QP_FORMULA_PRICE_CALC_PVT.calculate.factors_cur,QP_PREQ_LINE_ATTRS_FRML_TMP_N1,CONTEXT,3
227 INDX,QP_FORMULA_PRICE_CALC_PVT.calculate.factors_cur,QP_PREQ_LINE_ATTRS_FRML_TMP_N1,ATTRIBUTE,4
228 INDX,QP_FORMULA_PRICE_CALC_PVT.calculate.factors_cur,QP_PREQ_LINE_ATTRS_FRML_TMP_N1,LINE_INDEX,5
229 INDX,QP_FORMULA_PRICE_CALC_PVT.calculate.factors_cur,QP_PRICING_ATTRIBUTES_N8,LIST_HEADER_ID,1
230 INDX,QP_FORMULA_PRICE_CALC_PVT.calculate.factors_cur,QP_PRICING_ATTRIBUTES_N8,PRICING_ATTRIBUTE_CONTEXT,2
231 INDX,QP_FORMULA_PRICE_CALC_PVT.calculate.factors_cur,QP_PRICING_ATTRIBUTES_N8,PRICING_ATTRIBUTE,3
232 INDX,QP_FORMULA_PRICE_CALC_PVT.calculate.factors_cur,QP_PRICING_ATTRIBUTES_N8,SEARCH_IND,4
233 INDX,QP_FORMULA_PRICE_CALC_PVT.calculate.factors_cur,QP_LIST_LINES_PK,LIST_LINE_ID,5
234 INDX,QP_FORMULA_PRICE_CALC_PVT.calculate.factors_cur,QP_PRICING_ATTRIBUTES_N8,PATTERN_VALUE_FROM_POSITIVE,6
235 INDX,QP_FORMULA_PRICE_CALC_PVT.calculate.factors_cur,QP_PRICING_ATTRIBUTES_N8,PATTERN_VALUE_TO_POSITIVE,7
236 INDX,QP_FORMULA_PRICE_CALC_PVT.calculate.factors_cur,QP_PRICING_ATTRIBUTES_N8,PATTERN_VALUE_FROM_NEGATIVE,8
237 INDX,QP_FORMULA_PRICE_CALC_PVT.calculate.factors_cur,QP_PRICING_ATTRIBUTES_N8,PATTERN_VALUE_TO_NEGATIVE,9
238 */
239 CURSOR factors_cur (a_price_formula_id NUMBER,
240 a_line_index NUMBER,
241 a_price_effective_date DATE)
242 IS
243 SELECT /*+ ORDERED index(a QP_PRICING_ATTRIBUTES_N8) */
244 a.list_header_id, l.list_line_id, l.operand,
245 l.start_date_active, l.end_date_active, l.group_count,
246 fl.price_formula_id, fl.step_number
247 FROM qp_price_formula_lines fl, qp_factor_list_attrs fla,
248 qp_preq_line_attrs_formula_tmp t, qp_pricing_attributes a,
249 qp_list_lines l
250 WHERE t.context = a.pricing_attribute_context
251 AND t.attribute = a.pricing_attribute
252 AND fl.price_formula_line_type_code = 'ML'
253 AND t.line_index = a_line_index
254 AND t.attribute_type in ('PRICING','PRODUCT')
255 AND fl.price_formula_id = a_price_formula_id
256 AND t.pricing_status_code = QP_PREQ_GRP.G_STATUS_UNCHANGED
257 AND fla.list_header_id = fl.price_modifier_list_id
258 AND fla.pricing_attribute_context = t.context
259 AND fla.pricing_attribute = t.attribute
260 AND a.list_header_id = fla.list_header_id
261 AND a.list_line_id = l.list_line_id
262 AND a.search_ind = 1
263 AND t.value_from between
264 a.pattern_value_from_positive and a.pattern_value_to_positive
265 AND a_price_effective_date between --3520634 start
266 nvl(l.start_date_active,a_price_effective_date) and
267 nvl(l.end_date_active,a_price_effective_date)
268 UNION --separate sqls for positive and negative pattern_values for 3520634
269 SELECT /*+ ORDERED index(a QP_PRICING_ATTRIBUTES_N10) */
270 a.list_header_id, l.list_line_id, l.operand,
271 l.start_date_active, l.end_date_active, l.group_count,
272 fl.price_formula_id, fl.step_number
273 FROM qp_price_formula_lines fl, qp_factor_list_attrs fla,
274 qp_preq_line_attrs_formula_tmp t, qp_pricing_attributes a,
275 qp_list_lines l
276 WHERE t.context = a.pricing_attribute_context
277 AND t.attribute = a.pricing_attribute
278 AND fl.price_formula_line_type_code = 'ML'
279 AND t.line_index = a_line_index
280 AND t.attribute_type in ('PRICING','PRODUCT')
281 AND fl.price_formula_id = a_price_formula_id
282 AND t.pricing_status_code = QP_PREQ_GRP.G_STATUS_UNCHANGED
283 AND fla.list_header_id = fl.price_modifier_list_id
284 AND fla.pricing_attribute_context = t.context
285 AND fla.pricing_attribute = t.attribute
286 AND a.list_header_id = fla.list_header_id
287 AND a.list_line_id = l.list_line_id
288 AND a.search_ind = 1
289 AND t.value_from between
290 a.pattern_value_from_negative and a.pattern_value_to_negative
291 AND a_price_effective_date between
292 nvl(l.start_date_active,a_price_effective_date) and
293 nvl(l.end_date_active,a_price_effective_date)
294 ORDER BY 8; --3520634 end
295
296 --Introduced sub_factors_cur to incorporate search_ind for pricing_attributes.
297 --POSCO performance related.
298 /*
299 INDX,QP_FORMULA_PRICE_CALC_PVT.calculate.sub_factors_cur,QP_FACTOR_LIST_ATTRS_N1,LIST_HEADER_ID,1
300 INDX,QP_FORMULA_PRICE_CALC_PVT.calculate.sub_factors_cur,QP_PREQ_LINE_ATTRS_FRML_TMP_N1,PRICING_STATUS_CODE,1
301 INDX,QP_FORMULA_PRICE_CALC_PVT.calculate.sub_factors_cur,QP_PREQ_LINE_ATTRS_FRML_TMP_N1,ATTRIBUTE_TYPE,2
302 INDX,QP_FORMULA_PRICE_CALC_PVT.calculate.sub_factors_cur,QP_PREQ_LINE_ATTRS_FRML_TMP_N1,CONTEXT,3
303 INDX,QP_FORMULA_PRICE_CALC_PVT.calculate.sub_factors_cur,QP_PREQ_LINE_ATTRS_FRML_TMP_N1,ATTRIBUTE,4
304 INDX,QP_FORMULA_PRICE_CALC_PVT.calculate.sub_factors_cur,QP_PREQ_LINE_ATTRS_FRML_TMP_N1,LINE_INDEX,5
305 INDX,QP_FORMULA_PRICE_CALC_PVT.calculate.sub_factors_cur,QP_PRICING_ATTRIBUTES_N8,LIST_HEADER_ID,1
306 INDX,QP_FORMULA_PRICE_CALC_PVT.calculate.sub_factors_cur,QP_PRICING_ATTRIBUTES_N8,PRICING_ATTRIBUTE_CONTEXT,2
307 INDX,QP_FORMULA_PRICE_CALC_PVT.calculate.sub_factors_cur,QP_PRICING_ATTRIBUTES_N8,PRICING_ATTRIBUTE,3
308 INDX,QP_FORMULA_PRICE_CALC_PVT.calculate.sub_factors_cur,QP_PRICING_ATTRIBUTES_N8,SEARCH_IND,4
309 INDX,QP_FORMULA_PRICE_CALC_PVT.calculate.sub_factors_cur,QP_PRICING_ATTRIBUTES_N8,LIST_LINE_ID,5
310 INDX,QP_FORMULA_PRICE_CALC_PVT.calculate.sub_factors_cur,QP_PRICING_ATTRIBUTES_N8,PATTERN_VALUE_FROM_POSITIVE,6
311 INDX,QP_FORMULA_PRICE_CALC_PVT.calculate.sub_factors_cur,QP_PRICING_ATTRIBUTES_N8,PATTERN_VALUE_TO_POSITIVE,7
312 INDX,QP_FORMULA_PRICE_CALC_PVT.calculate.sub_factors_cur,QP_PRICING_ATTRIBUTES_N8,PATTERN_VALUE_FROM_NEGATIVE,8
313 INDX,QP_FORMULA_PRICE_CALC_PVT.calculate.sub_factors_cur,QP_PRICING_ATTRIBUTES_N8,PATTERN_VALUE_TO_NEGATIVE,9
314 */
315 CURSOR sub_factors_cur (a_price_formula_id NUMBER,
316 a_line_index NUMBER,
317 a_price_effective_date DATE,
318 a_list_header_id NUMBER,
319 a_list_line_id NUMBER,
320 a_group_count NUMBER)
321 IS
322 SELECT /*+ ordered */ a.list_line_id --5900728
323 FROM qp_factor_list_attrs fla,
324 qp_preq_line_attrs_formula_tmp t, qp_pricing_attributes a
325 WHERE fla.list_header_id = a_list_header_id
326 AND fla.pricing_attribute_context = t.context
327 AND fla.pricing_attribute = t.attribute
328 AND t.context = a.pricing_attribute_context
329 AND t.attribute = a.pricing_attribute
330 AND t.line_index = a_line_index
331 AND t.attribute_type in ('PRICING','PRODUCT')
332 AND t.pricing_status_code = QP_PREQ_GRP.G_STATUS_UNCHANGED
333 AND a.list_header_id = fla.list_header_id
334 AND a.list_line_id = a_list_line_id
335 AND a.search_ind = 2
336 AND(t.value_from BETWEEN a.pattern_value_from_positive
337 AND a.pattern_value_to_positive OR t.value_from BETWEEN a.pattern_value_from_negative
338 AND a.pattern_value_to_negative)
339 GROUP BY a.list_line_id
340 HAVING count(*) = a_group_count;
341
342 -- 5900728
343 -- SELECT /*+ ORDERED index(a QP_PRICING_ATTRIBUTES_N8) */
344 -- a.list_line_id
345 -- FROM qp_factor_list_attrs fla,
346 -- qp_preq_line_attrs_formula_tmp t, qp_pricing_attributes a
347 -- WHERE fla.list_header_id = a_list_header_id
348 -- AND fla.pricing_attribute_context = t.context
349 -- AND fla.pricing_attribute = t.attribute
350 -- AND t.context = a.pricing_attribute_context
351 -- AND t.attribute = a.pricing_attribute
352 -- AND t.line_index = a_line_index
353 -- AND t.attribute_type in ('PRICING','PRODUCT')
354 -- AND t.pricing_status_code = QP_PREQ_GRP.G_STATUS_UNCHANGED
355 -- AND a.list_header_id = fla.list_header_id
356 -- AND a.list_line_id = a_list_line_id
357 -- AND a.search_ind = 2
358 -- AND t.value_from between --3520634 start
359 -- a.pattern_value_from_positive and a.pattern_value_to_positive
360 -- GROUP BY a.list_line_id
361 -- HAVING count(*) = a_group_count
362 --UNION ----separate sqls for positive and negative pattern_values for 3520634
363 -- SELECT /*+ ORDERED index(a QP_PRICING_ATTRIBUTES_N10) */
364 -- a.list_line_id
365 -- FROM qp_factor_list_attrs fla,
366 -- qp_preq_line_attrs_formula_tmp t, qp_pricing_attributes a
367 -- WHERE fla.list_header_id = a_list_header_id
368 -- AND fla.pricing_attribute_context = t.context
369 -- AND fla.pricing_attribute = t.attribute
370 -- AND t.context = a.pricing_attribute_context
371 -- AND t.attribute = a.pricing_attribute
372 -- AND t.line_index = a_line_index
373 -- AND t.attribute_type in ('PRICING','PRODUCT')
374 -- AND t.pricing_status_code = QP_PREQ_GRP.G_STATUS_UNCHANGED
375 -- AND a.list_header_id = fla.list_header_id
376 -- AND a.list_line_id = a_list_line_id
377 -- AND a.search_ind = 2
378 -- AND t.value_from between
379 -- a.pattern_value_from_negative and a.pattern_value_to_negative
380 -- GROUP BY a.list_line_id
381 -- HAVING count(*) = a_group_count; --3520634 end
382 /*
383 INDX,QP_FORMULA_PRICE_CALC_PVT.calculate.price_formulas_cur,QP_PRICE_FORMULAS_B_PK,PRICE_FORMULA_ID,1
384 */
385 CURSOR price_formulas_cur (a_price_formula_id NUMBER,
386 a_price_effective_date DATE)
387 IS
388 SELECT formula
389 FROM qp_price_formulas_b
390 WHERE price_formula_id = a_price_formula_id
391 AND (start_date_active IS NULL OR
392 start_date_active <= a_price_effective_date)
393 AND (end_date_active IS NULL OR
394 end_date_active >= a_price_effective_date);
395
396 --Introduced pra_cur to process formula_line_type of PRA using temp tables
397 --instead of earlier plsql tables. POSCO performance related.
398 /*
399 INDX,QP_FORMULA_PRICE_CALC_PVT.calculate.pra_cur,QP_PREQ_LINE_ATTRS_FRML_TMP_N1,PRICING_STATUS_CODE,1
400 INDX,QP_FORMULA_PRICE_CALC_PVT.calculate.pra_cur,QP_PREQ_LINE_ATTRS_FRML_TMP_N1,ATTRIBUTE_TYPE,2
401 INDX,QP_FORMULA_PRICE_CALC_PVT.calculate.pra_cur,QP_PREQ_LINE_ATTRS_FRML_TMP_N1,CONTEXT,3
402 INDX,QP_FORMULA_PRICE_CALC_PVT.calculate.pra_cur,QP_PREQ_LINE_ATTRS_FRML_TMP_N1,ATTRIBUTE,4
403 INDX,QP_FORMULA_PRICE_CALC_PVT.calculate.pra_cur,QP_PREQ_LINE_ATTRS_FRML_TMP_N1,LINE_INDEX,5
404 */
405 CURSOR pra_cur(a_pricing_attribute_context VARCHAR2,
406 a_pricing_attribute VARCHAR2,
407 a_line_index NUMBER)
408 IS
409 SELECT value_from
410 FROM qp_preq_line_attrs_formula_tmp
411 WHERE context = a_pricing_attribute_context
412 AND attribute = a_pricing_attribute
413 AND line_index = a_line_index
414 AND attribute_type in ('PRICING','PRODUCT')
415 AND pricing_status_code = QP_PREQ_GRP.G_STATUS_UNCHANGED;
416
417 l_pra_rec pra_cur%ROWTYPE;
418 l_attr_count NUMBER := 0;
419 l_attr_flag BOOLEAN := FALSE;
420 l_count NUMBER := 0;
421 l_customized VARCHAR2(1);
422
423 l_attribute_id NUMBER := NULL;
424 l_start_date_active date := NULL;
425 l_end_date_active date := NULL;
426
427 l_formula VARCHAR2(2000) := '';
428 /* increased the length of l_formula,l_number and l_new_formula to 2000
429 to fix the bug 1539041 */
430 l_formula_value NUMBER;
431 l_no_of_comps NUMBER := 0;
432 i NUMBER;
433 j NUMBER := 1;
434
435 --Added as part of POSCO changes
436 TYPE Formula_Line_Rec IS RECORD
437 (step_number NUMBER,
438 price_formula_line_type_code VARCHAR2(10),
439 component_value NUMBER,
440 price_formula_id NUMBER,
441 line_index NUMBER,
442 list_line_type_code VARCHAR2(30),--of the parent line
443 list_header_id NUMBER, --populated for factor list steps
444 list_line_id NUMBER --populated with factor line id
445 );
446
447 --Added as part of POSCO changes
448 TYPE Formula_Line_Tbl_Type IS TABLE OF Formula_Line_Rec INDEX BY BINARY_INTEGER;
449
450 --Added as part of POSCO changes
451 l_formula_line_tbl Formula_Line_Tbl_Type;
452
453 l_req_line_attrs_tbl QP_FORMULA_PRICE_CALC_PVT.REQ_LINE_ATTRS_TBL;
454
455 l_sub_factors_rec sub_factors_cur%ROWTYPE;
456 l_old_step_number NUMBER := -99999999999999;
457 l_skip_factor BOOLEAN := FALSE;
458 l_return_status VARCHAR2(1);
459
460 --Bug 2772214
461 l_pass_qualifiers varchar2(10) := FND_PROFILE.VALUE('QP_PASS_QUALIFIERS_TO_GET_CUSTOM');
462
463 /*
464 INDX,QP_FORMULA_PRICE_CALC_PVT.calculate.req_line_attrs_cur,qp_npreq_line_attrs_tmp_N7,LINE_INDEX,1
465 INDX,QP_FORMULA_PRICE_CALC_PVT.calculate.req_line_attrs_cur,qp_npreq_line_attrs_tmp_N7,ATTRIBUTE_TYPE,2
466 */
467 CURSOR req_line_attrs_cur(a_line_index NUMBER)
468 IS
469 SELECT line_index, attribute_type, context, attribute, value_from value
470 --FROM qp_npreq_line_attrs_tmp
471 -- bug2425851
472 FROM qp_preq_line_attrs_formula_tmp
473 WHERE line_index = a_line_index
474 AND attribute_type IN ('PRICING','PRODUCT');
475
476 -- Bug 2772214, Added qual cursor
477 CURSOR req_line_attrs_qual_cur(a_line_index NUMBER)
478 IS
479 SELECT line_index, attribute_type, context, attribute, value_from value
480 FROM qp_preq_line_attrs_formula_tmp
481 WHERE line_index = a_line_index
482 AND attribute_type IN ('PRICING','PRODUCT','QUALIFIER');
483
484
485 l_null_step_number_tbl Step_Number_Tbl_Type;
486
487 E_FORMULA_NOT_FOUND EXCEPTION;
488 E_INVALID_FORMULA EXCEPTION;
489 E_FORMULA_COMPONENTS_REQ EXCEPTION;
490 E_CUSTOMIZE_GET_CUSTOM_PRICE EXCEPTION;
491 E_INVALID_NUMBER EXCEPTION;
492
493 l_Operand_Tbl QP_FORMULA_RULES_PVT.t_Operand_Tbl_Type;
494
495 l_formula_start_time NUMBER;
496 l_formula_end_time NUMBER;
497 l_time_difference NUMBER;
498 l_formula_name qp_price_formulas_tl.name%TYPE;
499
500 BEGIN
501
502 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
503 l_formula_start_time := dbms_utility.get_time;
504
505 x_return_status := FND_API.G_RET_STS_SUCCESS;
506
507 IF l_debug = FND_API.G_TRUE THEN
508 QP_PREQ_GRP.engine_debug('Start Formula...');
509 END IF;
510
511 --added for formula messages
512 SELECT name
513 INTO l_formula_name
514 FROM qp_price_formulas_tl
515 WHERE price_formula_id = p_price_formula_id
516 AND rownum<2;
517
518 OPEN price_formulas_cur (p_price_formula_id, p_price_effective_date);
519
520 FETCH price_formulas_cur INTO l_formula;
521
522 IF price_formulas_cur%NOTFOUND THEN
523
524 x_return_status := FND_API.G_RET_STS_ERROR;
525
526 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
527 THEN
528 RAISE E_FORMULA_NOT_FOUND;
529 -- FND_MSG_PUB.Add;
530 END IF;
531
532 END IF;
533
534 CLOSE price_formulas_cur;
535
536 Parse_Formula (l_formula, l_return_status);
537
538 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
539 x_return_status := l_return_status;
540 RAISE E_INVALID_FORMULA;
541 END IF;
542
543 --Get the no_of_components in the formula
544 /*
545 INDX,QP_FORMULA_PRICE_CALC_PVT.calculate.sel1,QP_PRICE_FORMULA_LINES_U1,PRICE_FORMULA_ID,1
546 */
547 SELECT count(*)
548 INTO l_no_of_comps
549 FROM qp_price_formula_lines
550 WHERE price_formula_id = p_price_formula_id;
551
552 IF l_no_of_comps = 0 THEN
553
554 x_return_status := FND_API.G_RET_STS_ERROR;
555
556 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
557 THEN
558 RAISE E_FORMULA_COMPONENTS_REQ;
559 -- FND_MSG_PUB.Add;
560 END IF;
561
562 END IF;
563 --Change flexible mask to mask below for formula pattern use (Bug2195879)
564 qp_number.canonical_mask :=
565 '00999999999999999999999.99999999999999999999999999999999999999';
566 --Begin more POSCO changes.
567 FOR l_rec IN formula_lines_cur(p_price_formula_id)
568 LOOP
569
570 IF l_rec.price_formula_line_type_code = 'NUM' THEN
571
572 IF l_debug = FND_API.G_TRUE THEN
573 QP_PREQ_GRP.engine_debug('Formula Line Type = NUM, Step = '|| l_rec.step_number
574 || ', Value = ' || l_rec.numeric_constant);
575 END IF;
576 l_formula_line_tbl(l_rec.step_number).component_value :=
577 l_rec.numeric_constant;
578
579 l_Operand_Tbl(l_rec.step_number) := l_formula_line_tbl(l_rec.step_number).component_value;
580
581 l_formula_line_tbl(l_rec.step_number).step_number := l_rec.step_number;
582 l_formula_line_tbl(l_rec.step_number).price_formula_line_type_code :=
583 l_rec.price_formula_line_type_code;
584 l_formula_line_tbl(l_rec.step_number).price_formula_id :=
585 l_rec.price_formula_id;
586 l_formula_line_tbl(l_rec.step_number).line_index := p_line_index;
587 l_formula_line_tbl(l_rec.step_number).list_header_id := null;
588 l_formula_line_tbl(l_rec.step_number).list_line_id := null;
589 l_formula_line_tbl(l_rec.step_number).list_line_type_code :=
590 p_list_line_type_code;
591
592 ELSIF l_rec.price_formula_line_type_code = 'LP' THEN
593
594 IF l_debug = FND_API.G_TRUE THEN
595 QP_PREQ_GRP.engine_debug('Formula Line Type = LP, Step = ' || l_rec.step_number
596 || ', Value = '|| p_list_price);
597 END IF;
598 l_formula_line_tbl(l_rec.step_number).component_value := p_list_price;
599
600 l_Operand_Tbl(l_rec.step_number) := l_formula_line_tbl(l_rec.step_number).component_value;
601
602 l_formula_line_tbl(l_rec.step_number).step_number := l_rec.step_number;
603 l_formula_line_tbl(l_rec.step_number).price_formula_line_type_code :=
604 l_rec.price_formula_line_type_code;
605 l_formula_line_tbl(l_rec.step_number).price_formula_id :=
606 l_rec.price_formula_id;
607 l_formula_line_tbl(l_rec.step_number).line_index := p_line_index;
608 l_formula_line_tbl(l_rec.step_number).list_header_id := null;
609 l_formula_line_tbl(l_rec.step_number).list_line_id := null;
610 l_formula_line_tbl(l_rec.step_number).list_line_type_code :=
611 p_list_line_type_code;
612 --added for formula messages
613 IF p_list_price IS NULL THEN
614 l_null_step_number_tbl(l_rec.step_number):= l_rec.step_number;
615 END IF;
616
617 ELSIF l_rec.price_formula_line_type_code = 'MV' THEN
618
619 IF l_debug = FND_API.G_TRUE THEN
620 QP_PREQ_GRP.engine_debug('Formula Line Type = MV, Step = ' || l_rec.step_number || ', Value = '|| p_modifier_value);
621
622 END IF;
623 l_formula_line_tbl(l_rec.step_number).component_value := p_modifier_value;
624
625 l_Operand_Tbl(l_rec.step_number) := l_formula_line_tbl(l_rec.step_number).component_value;
626
627 l_formula_line_tbl(l_rec.step_number).step_number := l_rec.step_number;
628 l_formula_line_tbl(l_rec.step_number).price_formula_line_type_code :=
629 l_rec.price_formula_line_type_code;
630 l_formula_line_tbl(l_rec.step_number).price_formula_id :=
631 l_rec.price_formula_id;
632 l_formula_line_tbl(l_rec.step_number).line_index := p_line_index;
633 l_formula_line_tbl(l_rec.step_number).list_header_id := null;
634 l_formula_line_tbl(l_rec.step_number).list_line_id := null;
635 l_formula_line_tbl(l_rec.step_number).list_line_type_code :=
636 p_list_line_type_code;
637 --added for formula messages
638 IF p_modifier_value IS NULL
639 THEN
640 l_null_step_number_tbl(l_rec.step_number) := l_rec.step_number;
641 END IF;
642
643 ELSIF l_rec.price_formula_line_type_code = 'FUNC' THEN
644
645 IF l_debug = FND_API.G_TRUE THEN
646 QP_PREQ_GRP.engine_debug('Formula Line Type = FUNC, Step = ' ||l_rec.step_number);
647 END IF;
648 l_customized := FND_PROFILE.VALUE('QP_GET_CUSTOM_PRICE_CUSTOMIZED');
649 IF l_customized = 'Y' THEN
650 IF l_debug = FND_API.G_TRUE THEN
651 QP_PREQ_GRP.engine_debug('l_customized is Y');
652
653 END IF;
654 --Populate l_req_line_attrs_tbl
655 -- Bug 2772214, Added If condition
656 If nvl(l_pass_qualifiers, 'N') = 'N' Then
657 FOR l_line_attrs_rec IN req_line_attrs_cur(p_line_index)
658 LOOP
659 l_req_line_attrs_tbl(j) := l_line_attrs_rec;
660 j := j + 1;
661 END LOOP;
662 Else
663 FOR l_line_attrs_rec IN req_line_attrs_qual_cur(p_line_index)
664 LOOP
665 l_req_line_attrs_tbl(j) := l_line_attrs_rec;
666 j := j + 1;
667 END LOOP;
668 End If;
669
670 --added for formula enhancement by dhgupta 3531890
671 l_req_line_attrs_tbl(j).line_index:=p_line_index;
672 l_req_line_attrs_tbl(j).attribute_type:=QP_GLOBALS.G_SPECIAL_ATTRIBUTE_TYPE;
673 l_req_line_attrs_tbl(j).context:=QP_GLOBALS.G_SPECIAL_CONTEXT;
674 l_req_line_attrs_tbl(j).attribute:=QP_GLOBALS.G_SPECIAL_ATTRIBUTE1;
675 l_req_line_attrs_tbl(j).value:=l_rec.step_number;
676 -- end 3531890
677
678 BEGIN
679 qp_debug_util.tstart('GET_CUSTOM_PRICE','Calculating the custom price in Formulas');
680 l_formula_line_tbl(l_rec.step_number).component_value :=
681 QP_Custom.Get_Custom_Price(p_price_formula_id,
682 p_list_price,
683 p_price_effective_date,
684 l_req_line_attrs_tbl);
685 qp_debug_util.tstop('GET_CUSTOM_PRICE');
686 l_Operand_Tbl(l_rec.step_number) := l_formula_line_tbl(l_rec.step_number).component_value;
687
688 l_formula_line_tbl(l_rec.step_number).step_number := l_rec.step_number;
689 l_formula_line_tbl(l_rec.step_number).price_formula_line_type_code :=
690 l_rec.price_formula_line_type_code;
691 l_formula_line_tbl(l_rec.step_number).price_formula_id :=
692 l_rec.price_formula_id;
693 l_formula_line_tbl(l_rec.step_number).line_index := p_line_index;
694 l_formula_line_tbl(l_rec.step_number).list_header_id := null;
695 l_formula_line_tbl(l_rec.step_number).list_line_id := null;
696 l_formula_line_tbl(l_rec.step_number).list_line_type_code :=
697 p_list_line_type_code;
698
699 IF l_formula_line_tbl(l_rec.step_number).component_value IS NULL
700 THEN
701 l_null_step_number_tbl(l_rec.step_number) := l_rec.step_number;
702 END IF;
703 EXCEPTION
704 WHEN OTHERS THEN
705 l_null_step_number_tbl(l_rec.step_number) := l_rec.step_number;
706 END;
707
708 ELSE --If customized = 'N'
709 x_return_status := FND_API.G_RET_STS_ERROR;
710 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
711 THEN
712 RAISE E_CUSTOMIZE_GET_CUSTOM_PRICE;
713 END IF;
714 END IF;
715
716 ELSIF l_rec.price_formula_line_type_code = 'PRA' THEN
717
718 IF l_debug = FND_API.G_TRUE THEN
719 QP_PREQ_GRP.engine_debug('Formula Line Type = PRA, Step = ' ||l_rec.step_number);
720
721 END IF;
722 OPEN pra_cur(l_rec.pricing_attribute_context, l_rec.pricing_attribute,
723 p_line_index);
724 FETCH pra_cur INTO l_pra_rec;
725
726 IF pra_cur%FOUND THEN
727 --Return the matching pricing attribute value that is found.
728 BEGIN
729 -- bug 2195879
730 IF l_debug = FND_API.G_TRUE THEN
731 QP_PREQ_GRP.engine_debug('l_pra_rec.value_from = '||l_pra_rec.value_from);
732 END IF;
733 l_formula_line_tbl(l_rec.step_number).component_value :=
734 qp_number.canonical_to_number(l_pra_rec.value_from);
735
736 l_Operand_Tbl(l_rec.step_number) := l_formula_line_tbl(l_rec.step_number).component_value;
737
738 IF l_debug = FND_API.G_TRUE THEN
739 QP_PREQ_GRP.engine_debug('Just after pra cur value from to number conversion');
740 END IF;
741 EXCEPTION
742 WHEN OTHERS THEN
743 x_return_status := FND_API.G_RET_STS_ERROR;
744 IF l_debug = FND_API.G_TRUE THEN
745 QP_PREQ_GRP.engine_debug('Error converting PRA value to number');
746 END IF;
747 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
748 THEN
749 RAISE E_INVALID_NUMBER;
750 END IF;
751 END; -- for Begin block
752
753 ELSE --If pra_cur%NOTFOUND
754 l_formula_line_tbl(l_rec.step_number).component_value := NULL;
755
756 l_Operand_Tbl(l_rec.step_number) := l_formula_line_tbl(l_rec.step_number).component_value;
757 l_null_step_number_tbl(l_rec.step_number) := l_rec.step_number;
758
759 END IF; --If pra_cur%FOUND
760
761 CLOSE pra_cur;
762
763 l_formula_line_tbl(l_rec.step_number).step_number := l_rec.step_number;
764 l_formula_line_tbl(l_rec.step_number).price_formula_line_type_code :=
765 l_rec.price_formula_line_type_code;
766 l_formula_line_tbl(l_rec.step_number).price_formula_id :=
767 l_rec.price_formula_id;
768 l_formula_line_tbl(l_rec.step_number).line_index := p_line_index;
769 l_formula_line_tbl(l_rec.step_number).list_header_id := null;
770 l_formula_line_tbl(l_rec.step_number).list_line_id := null;
771 l_formula_line_tbl(l_rec.step_number).list_line_type_code :=
772 p_list_line_type_code;
773
774 ELSIF l_rec.price_formula_line_type_code = 'PLL' THEN
775
776 IF l_debug = FND_API.G_TRUE THEN
777 QP_PREQ_GRP.engine_debug('Formula Line Type = PLL, Step = ' ||l_rec.step_number);
778 END IF;
779 /*
780 INDX,QP_FORMULA_PRICE_CALC_PVT.calculate.sel2,QP_LIST_LINES_PK,LIST_LINE_ID,1
781 */
782 BEGIN
783 SELECT operand
784 INTO l_formula_line_tbl(l_rec.step_number).component_value
785 FROM qp_list_lines
786 WHERE list_line_id = l_rec.price_list_line_id;
787
788 l_Operand_Tbl(l_rec.step_number) := l_formula_line_tbl(l_rec.step_number).component_value;
789
790 EXCEPTION
791 WHEN OTHERS THEN
792 l_formula_line_tbl(l_rec.step_number).component_value := NULL;
793
794 l_Operand_Tbl(l_rec.step_number) := l_formula_line_tbl(l_rec.step_number).component_value;
795 l_null_step_number_tbl(l_rec.step_number) := l_rec.step_number;
796 END;
797
798 l_formula_line_tbl(l_rec.step_number).step_number := l_rec.step_number;
799 l_formula_line_tbl(l_rec.step_number).price_formula_line_type_code :=
800 l_rec.price_formula_line_type_code;
801 l_formula_line_tbl(l_rec.step_number).price_formula_id :=
802 l_rec.price_formula_id;
803 l_formula_line_tbl(l_rec.step_number).line_index := p_line_index;
804 l_formula_line_tbl(l_rec.step_number).list_header_id := null;
805 l_formula_line_tbl(l_rec.step_number).list_line_id := null;
806 l_formula_line_tbl(l_rec.step_number).list_line_type_code :=
807 p_list_line_type_code;
808
809 ELSIF l_rec.price_formula_line_type_code = 'ML' THEN
810
811 IF l_debug = FND_API.G_TRUE THEN
812 QP_PREQ_GRP.engine_debug('Formula Line Type = ML, Step = ' ||l_rec.step_number);
813 END IF;
814 --null; --Do nothing here. All factor lists will be processed together later
815 l_Operand_Tbl(l_rec.step_number) :=null;----6726052,7249280 smbalara
816 --added for formula messages
817 l_null_step_number_tbl(l_rec.step_number) := l_rec.step_number;
818 ELSE --if price_formula_line_type_code is not one of the expected values.
819
820 x_return_status := FND_API.G_RET_STS_ERROR;
821 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
822 THEN
823 FND_MESSAGE.SET_NAME('QP','QP_INVALID_FORMULA_LINE_TYPE');
824 END IF;
825
826 END IF; -- IF stmt comparing price_formula_line_type_code to various values.
827 l_req_line_attrs_tbl.delete; --3531890 attribute were getting accumulated for every step
828 -- smbalara bug 7188211
829 --Based on a profile option,formula step values will be inserted into qp_nformula_step_values_tmp
830 IF QP_PREQ_GRP.G_INSERT_FORMULA_STEP_VALUES = 'Y' THEN
831 IF l_debug = FND_API.G_TRUE THEN
832 QP_PREQ_GRP.engine_debug('Before populating formula stepvalues temp table');
833 QP_PREQ_GRP.engine_debug('Value 1='||l_formula_line_tbl(l_rec.step_number).price_formula_id );
834 QP_PREQ_GRP.engine_debug('Value 2='||l_formula_line_tbl(l_rec.step_number).step_number );
835 QP_PREQ_GRP.engine_debug('Value 3='||l_formula_line_tbl(l_rec.step_number).component_value );
836 QP_PREQ_GRP.engine_debug('Value 4='||l_formula_line_tbl(l_rec.step_number).price_formula_line_type_code );
837 QP_PREQ_GRP.engine_debug('Value 5='||l_formula_line_tbl(l_rec.step_number).line_index );
838 QP_PREQ_GRP.engine_debug('Value 6='||l_formula_line_tbl(l_rec.step_number).list_line_type_code );
839 QP_PREQ_GRP.engine_debug('Value 7='||l_formula_line_tbl(l_rec.step_number).list_header_id );
840 QP_PREQ_GRP.engine_debug('Value 8='||l_formula_line_tbl(l_rec.step_number).list_line_id );
841 END IF;
842
843 INSERT INTO qp_nformula_step_values_tmp
844 (price_formula_id,
845 step_number,
846 component_value,
847 price_formula_line_type_code,
848 line_index,
849 list_line_type_code,
850 list_header_id,
851 list_line_id
852 )
853 VALUES
854 (l_formula_line_tbl(l_rec.step_number).price_formula_id,
855 l_formula_line_tbl(l_rec.step_number).step_number,
856 l_formula_line_tbl(l_rec.step_number).component_value,
857 l_formula_line_tbl(l_rec.step_number).price_formula_line_type_code,
858 l_formula_line_tbl(l_rec.step_number).line_index,
859 l_formula_line_tbl(l_rec.step_number).list_line_type_code,
860 l_formula_line_tbl(l_rec.step_number).list_header_id,
861 l_formula_line_tbl(l_rec.step_number).list_line_id
862 );
863 IF l_debug = FND_API.G_TRUE THEN
864 QP_PREQ_GRP.engine_debug('After populating formula step values temp table');
865 END IF;
866 END IF;
867 -- smbalara bug 7188211
868 END LOOP; --Loop over formula_lines_cur
869 -- Change mask back to flexible mask
870 qp_number.canonical_mask :=
871 'FM999999999999999999999.9999999999999999999999999999999999999999';
872
873 -- Now Process all Factor Lists in the formula
874
875 IF l_debug = FND_API.G_TRUE THEN
876 QP_PREQ_GRP.engine_debug('Before populating plsql table of formula lines');
877
878 END IF;
879 --Populate l_formula_line_tbl for all factor lists in the formula
880 FOR l_factors_rec IN factors_cur (p_price_formula_id, p_line_index,
881 p_price_effective_date)
882 LOOP
883
884 IF l_debug = FND_API.G_TRUE THEN
885 QP_PREQ_GRP.engine_debug('enter factors_cur loop');
886
887 END IF;
888 IF l_skip_factor AND
889 l_factors_rec.step_number = l_old_step_number
890 THEN
891 l_old_step_number := l_factors_rec.step_number;
892 GOTO factors_loop;
893 END IF;
894
895 IF l_debug = FND_API.G_TRUE THEN
896 QP_PREQ_GRP.engine_debug('after skip check ');
897
898 END IF;
899 l_skip_factor := FALSE;
900
901 --If no attributes with search_ind = 2 then no need to open sub_factors_cur
902 IF l_factors_rec.group_count = 0 THEN
903
904 l_formula_line_tbl(l_factors_rec.step_number).component_value :=
905 l_factors_rec.operand;
906
907 l_Operand_Tbl(l_factors_rec.step_number) := l_formula_line_tbl(l_factors_rec.step_number).component_value;
908
909 l_formula_line_tbl(l_factors_rec.step_number).step_number :=
910 l_factors_rec.step_number;
911 l_formula_line_tbl(l_factors_rec.step_number).price_formula_line_type_code
912 := 'ML';
913 l_formula_line_tbl(l_factors_rec.step_number).price_formula_id :=
914 l_factors_rec.price_formula_id;
915 l_formula_line_tbl(l_factors_rec.step_number).line_index :=
916 p_line_index;
917 l_formula_line_tbl(l_factors_rec.step_number).list_header_id :=
918 l_factors_rec.list_header_id;
919 l_formula_line_tbl(l_factors_rec.step_number).list_line_id :=
920 l_factors_rec.list_line_id;
921 l_formula_line_tbl(l_factors_rec.step_number).list_line_type_code :=
922 p_list_line_type_code;
923
924 IF l_debug = FND_API.G_TRUE THEN
925 QP_PREQ_GRP.engine_debug('In factors_cur, step = ' ||l_factors_rec.step_number);
926
927 END IF;
928 l_skip_factor := TRUE;
929
930 --added for formula messages
931 l_null_step_number_tbl.DELETE(l_factors_rec.step_number);
932
933 ELSE --If l_factors_rec.group_count > 0
934 --sub_factors_cur has to be fetched to determine if all attributes match
935 OPEN sub_factors_cur(p_price_formula_id,
936 p_line_index,
937 p_price_effective_date,
938 l_factors_rec.list_header_id,
939 l_factors_rec.list_line_id,
940 l_factors_rec.group_count);
941 FETCH sub_factors_cur
942 INTO l_sub_factors_rec;
943
944 IF sub_factors_cur%FOUND THEN
945
946 l_formula_line_tbl(l_factors_rec.step_number).component_value :=
947 l_factors_rec.operand;
948
949 l_Operand_Tbl(l_factors_rec.step_number) := l_formula_line_tbl(l_factors_rec.step_number).component_value;
950
951 l_formula_line_tbl(l_factors_rec.step_number).step_number :=
952 l_factors_rec.step_number;
953 l_formula_line_tbl(l_factors_rec.step_number).price_formula_line_type_code
954 := 'ML';
955 l_formula_line_tbl(l_factors_rec.step_number).price_formula_id :=
956 l_factors_rec.price_formula_id;
957 l_formula_line_tbl(l_factors_rec.step_number).line_index :=
958 p_line_index;
959 l_formula_line_tbl(l_factors_rec.step_number).list_header_id :=
960 l_factors_rec.list_header_id;
961 l_formula_line_tbl(l_factors_rec.step_number).list_line_id :=
962 l_factors_rec.list_line_id;
963 l_formula_line_tbl(l_factors_rec.step_number).list_line_type_code :=
964 p_list_line_type_code;
965
966 IF l_debug = FND_API.G_TRUE THEN
967 QP_PREQ_GRP.engine_debug('In sub_factors_cur, step = ' ||l_factors_rec.step_number);
968
969 END IF;
970 l_skip_factor := TRUE;
971
972 l_null_step_number_tbl.DELETE(l_factors_rec.step_number);
973 END IF;--sub_factors_cur%FOUND
974
975 CLOSE sub_factors_cur;
976
977 END IF; --If l_factors_rec.group_count = 0
978
979 l_old_step_number := l_factors_rec.step_number;
980
981 <<factors_loop>>
982 null;
983
984 IF l_debug = FND_API.G_TRUE THEN
985 QP_PREQ_GRP.engine_debug('exit factors_cur loop');
986
987 END IF;
988 END LOOP; --Loop over factors_cur
989
990 IF l_debug = FND_API.G_TRUE THEN
991 QP_PREQ_GRP.engine_debug('After populating plsql table of formula lines');
992
993 END IF;
994 --Based on a profile option, loop over plsql table of formula lines to
995 --populate qp_nformula_step_values_tmp table
996 /* commented for bug 7188211 - temp table insert moved above
997 IF QP_PREQ_GRP.G_INSERT_FORMULA_STEP_VALUES = 'Y' THEN
998
999 IF l_debug = FND_API.G_TRUE THEN
1000 QP_PREQ_GRP.engine_debug('Before populating formula stepvalues temp table');
1001
1002 END IF;
1003 i:= l_formula_line_tbl.FIRST; --set loop index to first element in plsql tbl
1004
1005 WHILE i IS NOT NULL
1006 LOOP
1007 BEGIN
1008
1009 --Insert into temp table only for formulas attached to Price List Lines.
1010 IF l_formula_line_tbl(i).list_line_type_code = 'PLL'
1011 THEN
1012
1013 INSERT INTO qp_nformula_step_values_tmp
1014 (price_formula_id,
1015 step_number,
1016 component_value,
1017 price_formula_line_type_code,
1018 line_index,
1019 list_line_type_code,
1020 list_header_id,
1021 list_line_id
1022 )
1023 VALUES
1024 (l_formula_line_tbl(i).price_formula_id,
1025 l_formula_line_tbl(i).step_number,
1026 l_formula_line_tbl(i).component_value,
1027 l_formula_line_tbl(i).price_formula_line_type_code,
1028 l_formula_line_tbl(i).line_index,
1029 l_formula_line_tbl(i).list_line_type_code,
1030 l_formula_line_tbl(i).list_header_id,
1031 l_formula_line_tbl(i).list_line_id
1032 );
1033
1034 END IF; --If list_line_type_code = 'PLL'
1035
1036 EXCEPTION
1037 WHEN OTHERS THEN
1038 IF l_debug = FND_API.G_TRUE THEN
1039 QP_PREQ_GRP.engine_debug(substr(sqlerrm, 1, 240));
1040 END IF;
1041 END;
1042
1043 i := l_formula_line_tbl.NEXT(i);--set i to next notnull position in plsql
1044
1045 END LOOP; --loop over l_formula_line_tbl
1046
1047 IF l_debug = FND_API.G_TRUE THEN
1048 QP_PREQ_GRP.engine_debug('After populating formula step values temp table');
1049
1050 END IF;
1051 END IF; --If profile option is set
1052 commented for bug 7188211*/
1053 IF l_debug = FND_API.G_TRUE THEN
1054 QP_PREQ_GRP.engine_debug('Before Calling - QP_BUILD_FORMULA_RULES.Get_Formula_Values');
1055 QP_PREQ_GRP.engine_debug('For Formula : '||l_formula);----6726052,7249280 smbalara
1056 END IF;
1057 QP_BUILD_FORMULA_RULES.Get_Formula_Values(l_formula,
1058 l_Operand_Tbl,
1059 'G', --sfiresto
1060 l_formula_value,
1061 l_return_status);
1062
1063 IF l_debug = FND_API.G_TRUE THEN
1064 QP_PREQ_GRP.engine_debug('After Calling - QP_BUILD_FORMULA_RULES.Get_Formula_Values');
1065
1066 QP_PREQ_GRP.engine_debug('Return Status from Get_Formula_Values ' || l_return_status);
1067
1068 END IF;
1069 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) OR (l_formula_value IS NULL) THEN
1070 l_formula_value := Select_From_Dual(l_formula, l_operand_tbl);
1071
1072 IF l_formula_value IS NULL THEN
1073 x_return_status := FND_API.G_RET_STS_ERROR;
1074 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1075 THEN
1076 Set_Message( p_price_formula_id => p_price_formula_id,
1077 p_formula_name => l_formula_name,
1078 p_null_step_number_tbl => l_null_step_number_tbl);
1079 l_null_step_number_tbl.DELETE;
1080 END IF;
1081 END IF;
1082
1083 END IF;
1084
1085 IF l_debug = FND_API.G_TRUE THEN
1086 QP_PREQ_GRP.engine_debug('Formula evaluated to ' || l_formula_value);
1087
1088 QP_PREQ_GRP.engine_debug('Formula Return Status ' || l_return_status);
1089
1090 END IF;
1091 l_formula_line_tbl.DELETE; --Clear the temp table table
1092
1093 l_formula_end_time := dbms_utility.get_time;
1094 l_time_difference := (l_formula_end_time - l_formula_start_time)/100 ;
1095
1096 IF l_debug = FND_API.G_TRUE THEN
1097 QP_PREQ_GRP.engine_debug('##### Total Time in QP_FORMULA_PRICE_CALC_PVT(in sec) : ' || l_time_difference || ' #####');
1098
1099 END IF;
1100 RETURN l_formula_value;
1101 --End more POSCO changes.
1102
1103 EXCEPTION
1104
1105 WHEN E_FORMULA_NOT_FOUND THEN
1106 x_return_status := FND_API.G_RET_STS_ERROR;
1107 fnd_message.set_name('QP','QP_FORMULA_NOT_FOUND');
1108 fnd_message.set_token('FORMULA_NAME',l_formula_name);
1109 -- Change mask back to flexible mask
1110 qp_number.canonical_mask := 'FM999999999999999999999.9999999999999999999999999999999999999999';
1111 RETURN l_formula_value;
1112
1113 WHEN E_INVALID_FORMULA THEN
1114 x_return_status := FND_API.G_RET_STS_ERROR;
1115 fnd_message.set_name('QP','QP_INVALID_FORMULA');
1116 fnd_message.set_token('FORMULA_NAME',l_formula_name);
1117 -- Change mask back to flexible mask
1118 qp_number.canonical_mask := 'FM999999999999999999999.9999999999999999999999999999999999999999';
1119 RETURN l_formula_value;
1120
1121 WHEN E_FORMULA_COMPONENTS_REQ THEN
1122 x_return_status := FND_API.G_RET_STS_ERROR;
1123 fnd_message.set_name('QP','QP_FORMULA_COMPONENTS_REQ');
1124 fnd_message.set_token('FORMULA_NAME',l_formula_name);
1125 -- Change mask back to flexible mask
1126 qp_number.canonical_mask := 'FM999999999999999999999.9999999999999999999999999999999999999999';
1127 RETURN l_formula_value;
1128
1129 WHEN E_CUSTOMIZE_GET_CUSTOM_PRICE THEN
1130 x_return_status := FND_API.G_RET_STS_ERROR;
1131 fnd_message.set_name('QP','QP_CUSTOMIZE_GET_CUSTOM_PRICE');
1132 fnd_message.set_token('FORMULA_NAME',l_formula_name);
1133 -- Change mask back to flexible mask
1134 qp_number.canonical_mask := 'FM999999999999999999999.9999999999999999999999999999999999999999';
1135 RETURN l_formula_value;
1136
1137 WHEN E_INVALID_NUMBER THEN
1138 x_return_status := FND_API.G_RET_STS_ERROR;
1139 fnd_message.set_name('QP','QP_INVALID_NUMBER');
1140 fnd_message.set_token('FORMULA_NAME',l_formula_name);
1141 -- Change mask back to flexible mask
1142 qp_number.canonical_mask := 'FM999999999999999999999.9999999999999999999999999999999999999999';
1143 RETURN l_formula_value;
1144
1145 WHEN FND_API.G_EXC_ERROR THEN
1146
1147 x_return_status := FND_API.G_RET_STS_ERROR;
1148
1149 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1150 THEN
1151 FND_MESSAGE.SET_NAME('QP','QP_FORMULA_FAILED');
1152 END IF;
1153 IF l_debug = FND_API.G_TRUE THEN
1154 QP_PREQ_GRP.engine_debug('Exception '||substr(sqlerrm, 1, 240));
1155 QP_PREQ_GRP.engine_debug('Exception occurred. Formula value returned is ' ||
1156 l_formula_value);
1157 END IF;
1158
1159 l_formula_line_tbl.DELETE; --Clear the temp table table
1160
1161 -- Change mask back to flexible mask
1162 qp_number.canonical_mask := 'FM999999999999999999999.9999999999999999999999999999999999999999';
1163
1164 RETURN l_formula_value;
1165
1166 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1167
1168 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1169 IF l_debug = FND_API.G_TRUE THEN
1170 QP_PREQ_GRP.engine_debug('Unexpected Exception '||substr(sqlerrm, 1, 240));
1171 QP_PREQ_GRP.engine_debug('Exception occurred. Formula value returned is ' ||
1172 l_formula_value);
1173
1174 END IF;
1175 l_formula_line_tbl.DELETE; --Clear the temp table table
1176
1177 -- Change mask back to flexible mask
1178 qp_number.canonical_mask := 'FM999999999999999999999.9999999999999999999999999999999999999999';
1179
1180 RETURN l_formula_value;
1181
1182 WHEN OTHERS THEN
1183
1184 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1185
1186 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1187 THEN
1188 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,
1189 'Calculate:'||sqlerrm);
1190 END IF;
1191
1192 IF l_debug = FND_API.G_TRUE THEN
1193 QP_PREQ_GRP.engine_debug('Other Exception '||substr(sqlerrm, 1, 240));
1194 QP_PREQ_GRP.engine_debug('Exception occurred. Formula value returned is ' ||
1195 l_formula_value);
1196
1197 END IF;
1198 l_formula_line_tbl.DELETE; --Clear the temp table table
1199
1200 -- Change mask back to flexible mask
1201 qp_number.canonical_mask := 'FM999999999999999999999.9999999999999999999999999999999999999999';
1202
1203 RETURN l_formula_value;
1204
1205 END Calculate;
1206
1207 PROCEDURE Set_Message(p_price_formula_id IN NUMBER,
1208 p_formula_name IN VARCHAR2,
1209 p_null_step_number_tbl IN STEP_NUMBER_TBL_TYPE)
1210
1211 IS
1212
1213 l_price_formula_line_type_code qp_price_formula_lines.price_formula_line_type_code%TYPE;
1214 l_pricing_attribute_context qp_price_formula_lines.pricing_attribute_context%TYPE;
1215 l_pricing_attribute qp_price_formula_lines.pricing_attribute%TYPE;
1216 l_all_steps VARCHAR2(100);
1217 l_formula_name qp_price_formulas_tl.name%TYPE;
1218 l_index NUMBER;
1219 l_attribute VARCHAR2(80);
1220 --added for display of right message in case of undefined step.
1221 --can be removed once validation is done in formula form.
1222 l_no_of_comps NUMBER;
1223 l_formula VARCHAR2(2000);
1224 l_count NUMBER :=0;
1225 l_char VARCHAR2(1) :='';
1226 l_number VARCHAR2(2000) :='';
1227
1228 BEGIN
1229
1230 l_formula_name := p_formula_name;
1231 --added for display of right message in case of undefined step
1232 -- query on formula ID instead of name, SQL repos
1233 SELECT formula into l_formula from qp_price_formulas_b
1234 WHERE price_formula_id = p_price_formula_id;
1235
1236 SELECT count(*) into l_no_of_comps from qp_price_formula_lines
1237 WHERE price_formula_id = p_price_formula_id;
1238
1239 FOR i IN 1..LENGTH(l_formula) LOOP
1240 l_char :=SUBSTR(l_formula,i,1);
1241 IF (l_char = '0') OR (l_char = '1') OR (l_char = '2') OR (l_char = '3')
1242 OR (l_char = '4') OR (l_char = '5') OR (l_char ='6')
1243 OR (l_char = '7') OR (l_char = '8') OR (l_char = '9')
1244 THEN
1245 l_number :=l_number ||l_char;
1246 IF (i = LENGTH(l_formula))
1247 THEN
1248 l_count :=l_count+1;
1249 l_number :='';
1250 END IF;
1251 ELSE
1252 IF l_number is NOT NULL
1253 THEN
1254 l_count :=l_count+1;
1255 l_number :='';
1256 END IF;
1257 END IF;
1258 END LOOP;
1259 --added for display of right message in case of undefined step
1260 IF p_null_step_number_tbl.COUNT = 1 AND l_count = l_no_of_comps
1261 THEN
1262 l_index := p_null_step_number_tbl.FIRST;
1263 SELECT price_formula_line_type_code,
1264 pricing_attribute_context,
1265 pricing_attribute
1266 INTO l_price_formula_line_type_code,
1267 l_pricing_attribute_context,
1268 l_pricing_attribute
1269 FROM qp_price_formula_lines
1270 WHERE price_formula_id = p_price_formula_id
1271 AND step_number = l_index;
1272
1273 IF l_price_formula_line_type_code = 'LP'
1274 THEN
1275 fnd_message.set_name('QP','QP_FORMULA_LIST_PRICE_NULL');
1276
1277 ELSIF l_price_formula_line_type_code = 'MV'
1278 THEN
1279 fnd_message.set_name('QP','QP_FORMULA_MODIFIER_VALUE_NULL');
1280
1281 ELSIF l_price_formula_line_type_code = 'FUNC'
1282 THEN
1283 fnd_message.set_name('QP','QP_FORMULA_GET_CUSTOM_PRICE');
1284
1285 ELSIF l_price_formula_line_type_code = 'PRA'
1286 THEN
1287 SELECT nvl(SEGMENTS_TL.SEEDED_SEGMENT_NAME,SEGMENTS_TL.USER_SEGMENT_NAME)
1288 INTO l_attribute
1289 FROM qp_segments_b SEGMENTS, qp_prc_contexts_b PCONTEXTS , qp_segments_tl SEGMENTS_TL
1290 WHERE pcontexts.prc_context_code = l_pricing_attribute_context
1291 AND segments.segment_mapping_column = l_pricing_attribute
1292 AND segments.prc_context_id = pcontexts.prc_context_id
1293 AND segments.segment_id = segments_tl.segment_id
1294 AND rownum<2;
1295
1296 fnd_message.set_name('QP','QP_PRICING_ATTRIBUTE_NULL');
1297 fnd_message.set_token('CONTEXT',l_pricing_attribute_context);
1298 fnd_message.set_token('ATTRIBUTE',l_attribute);
1299
1300 ELSIF l_price_formula_line_type_code = 'PLL'
1301 THEN
1302 fnd_message.set_name('QP','QP_PRICE_LIST_LINE_NOT_EXISTS');
1303
1304 ELSIF l_price_formula_line_type_code = 'ML'
1305 THEN
1306 fnd_message.set_name('QP','QP_FACTOR_LIST_NULL');
1307 END IF;
1308 fnd_message.set_token('STEP_NUMBER',l_index);
1309
1310 ELSIF p_null_step_number_tbl.COUNT>1 AND l_count = l_no_of_comps
1311 THEN -- more than one step nulling out
1312 l_index := p_null_step_number_tbl.FIRST;
1313 WHILE l_index IS NOT NULL
1314 LOOP
1315 IF l_index = p_null_step_number_tbl.LAST
1316 THEN
1317 l_all_steps := l_all_steps || p_null_step_number_tbl(l_index);
1318 ELSE
1319 l_all_steps := l_all_steps || p_null_step_number_tbl(l_index) || ', ';
1320 END IF;
1321 l_index := p_null_step_number_tbl.NEXT(l_index);
1322 END LOOP;
1323 fnd_message.set_name('QP','QP_NULL_STEP_NUMBER');
1324 fnd_message.set_token('STEP_NUMBERS',l_all_steps);
1325 ELSE
1326 fnd_message.set_name('QP','QP_STEP_NO_UNDEFINED');
1327 END IF;
1328 fnd_message.set_token('FORMULA_NAME',l_formula_name);
1329 END Set_Message;
1330
1331
1332 -----------------------------------------------------------------------
1333 -- Wrapper for QP_Build_Formula_Rules.Get_Formula_Values called by Java
1334 -- Formula Engine. The JDBC call will pass in a serialized string of
1335 -- operands, which this procedure deserializes into a PL/SQL table to
1336 -- pass to Get_Formula_Values
1337 -----------------------------------------------------------------------
1338 PROCEDURE Java_Get_Formula_Values(p_formula IN VARCHAR2,
1339 p_operands_str IN VARCHAR2,
1340 p_procedure_type IN VARCHAR2,
1341 x_formula_value OUT NOCOPY NUMBER,
1342 x_return_status OUT NOCOPY VARCHAR2)
1343 IS
1344 head NUMBER;
1345 tail NUMBER;
1346 l_step_num NUMBER;
1347 l_operand_tbl QP_FORMULA_RULES_PVT.t_Operand_Tbl_Type;
1348 BEGIN
1349 -- parse the operands string into a table
1350 -- first read in step number, followed by step values
1351 -- head and tail indicate start and end positions of search in the string
1352 head := 0;
1353 tail := instr(p_operands_str, '|', head+1);
1354 WHILE tail <> 0
1355 LOOP
1356 l_step_num := substr(p_operands_str, head+1, tail-head-1); -- step number
1357 head := tail;
1358 tail := instr(p_operands_str, '|', head+1);
1359 l_operand_tbl(l_step_num) := substr(p_operands_str, head+1, tail-head-1);
1360 head := tail;
1361 tail := instr(p_operands_str, '|', head+1);
1362 END LOOP;
1363
1364 QP_Build_Formula_Rules.Get_Formula_Values(p_formula, l_operand_tbl, p_procedure_type,
1365 x_formula_value, x_return_status);
1366
1367 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) OR (x_formula_value IS NULL) THEN
1368 x_formula_value := Select_From_Dual(p_formula, l_operand_tbl);
1369
1370 IF x_formula_value IS NULL THEN
1371 x_return_status := FND_API.G_RET_STS_ERROR;
1372 ELSE
1373 x_return_status := FND_API.G_RET_STS_SUCCESS;
1374 END IF;
1375 END IF;
1376 END Java_Get_Formula_Values;
1377
1378 END QP_FORMULA_PRICE_CALC_PVT;
[Home] [Help]