eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.QP_FORMULA_PRICE_CALC_PVT

Source


1 PACKAGE BODY QP_FORMULA_PRICE_CALC_PVT AS
2 /* $Header: QPXVCALB.pls 120.10.12020000.2 2012/07/04 12:14:24 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) index(t qp_preq_line_attrs_frml_tmp_n1) */ -- 9362867
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) index(t qp_preq_line_attrs_frml_tmp_n1) */ -- 9362867
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 /*+  index(t QP_PREQ_LINE_ATTRS_FRML_TMP_N1) index(A QP_PRICING_ATTRIBUTES_N2) */ --Bug 7452538 Added index hints
323      a.list_line_id     --Bug 8359591 Removing ordered hint
324    -- /*+ ordered */ a.list_line_id     --5900728
325   FROM   qp_factor_list_attrs fla,
326          qp_preq_line_attrs_formula_tmp t, qp_pricing_attributes a
327   WHERE  fla.list_header_id = a_list_header_id
328   AND    fla.pricing_attribute_context = t.context
329   AND    fla.pricing_attribute = t.attribute
330   AND    t.context = a.pricing_attribute_context
331   AND    t.attribute = a.pricing_attribute
332   AND    t.line_index = a_line_index
333   AND    t.attribute_type in ('PRICING','PRODUCT')
334   AND    t.pricing_status_code = QP_PREQ_GRP.G_STATUS_UNCHANGED
335   AND    a.list_header_id = fla.list_header_id
336   AND    a.list_line_id = a_list_line_id
337   AND    a.search_ind = 2
338    AND(t.value_from BETWEEN a.pattern_value_from_positive
339    AND a.pattern_value_to_positive OR t.value_from BETWEEN a.pattern_value_from_negative
340    AND a.pattern_value_to_negative)
341   GROUP BY a.list_line_id
342   HAVING   count(*) = a_group_count;
343 
344 -- 5900728
345 --  SELECT /*+ ORDERED index(a QP_PRICING_ATTRIBUTES_N8) */
346 --         a.list_line_id
347 --  FROM   qp_factor_list_attrs fla,
348 --         qp_preq_line_attrs_formula_tmp t, qp_pricing_attributes a
349 --  WHERE  fla.list_header_id = a_list_header_id
350 --  AND    fla.pricing_attribute_context = t.context
351 --  AND    fla.pricing_attribute = t.attribute
352 --  AND    t.context = a.pricing_attribute_context
353 --  AND    t.attribute = a.pricing_attribute
354 --  AND    t.line_index = a_line_index
355 --  AND    t.attribute_type in ('PRICING','PRODUCT')
356 --  AND    t.pricing_status_code = QP_PREQ_GRP.G_STATUS_UNCHANGED
357 --  AND    a.list_header_id = fla.list_header_id
358 --  AND    a.list_line_id = a_list_line_id
359 --  AND    a.search_ind = 2
360 --  AND    t.value_from between               --3520634 start
361 --          a.pattern_value_from_positive and a.pattern_value_to_positive
362 --  GROUP BY a.list_line_id
363 --  HAVING   count(*) = a_group_count
364 --UNION   ----separate sqls for positive and negative pattern_values for 3520634
365 --  SELECT /*+ ORDERED index(a QP_PRICING_ATTRIBUTES_N10) */
366 --         a.list_line_id
367 --  FROM   qp_factor_list_attrs fla,
368 --         qp_preq_line_attrs_formula_tmp t, qp_pricing_attributes a
369 --  WHERE  fla.list_header_id = a_list_header_id
370 --  AND    fla.pricing_attribute_context = t.context
371 --  AND    fla.pricing_attribute = t.attribute
372 --  AND    t.context = a.pricing_attribute_context
373 --  AND    t.attribute = a.pricing_attribute
374 --  AND    t.line_index = a_line_index
375 --  AND    t.attribute_type in ('PRICING','PRODUCT')
376 --  AND    t.pricing_status_code = QP_PREQ_GRP.G_STATUS_UNCHANGED
377 --  AND    a.list_header_id = fla.list_header_id
378 --  AND    a.list_line_id = a_list_line_id
379 --  AND    a.search_ind = 2
380 --  AND    t.value_from between
381 --          a.pattern_value_from_negative and a.pattern_value_to_negative
382 --  GROUP BY a.list_line_id
383 --  HAVING   count(*) = a_group_count;  --3520634 end
384 /*
385 INDX,QP_FORMULA_PRICE_CALC_PVT.calculate.price_formulas_cur,QP_PRICE_FORMULAS_B_PK,PRICE_FORMULA_ID,1
386 */
387 CURSOR price_formulas_cur (a_price_formula_id     NUMBER,
388 			   a_price_effective_date DATE)
389 IS
390   SELECT formula
391   FROM   qp_price_formulas_b
392   WHERE  price_formula_id = a_price_formula_id
393   AND    (start_date_active IS NULL OR
394 		start_date_active <= a_price_effective_date)
395   AND    (end_date_active IS NULL   OR
396 		end_date_active >= a_price_effective_date);
397 
398 --Introduced pra_cur to process formula_line_type of PRA using temp tables
399 --instead of earlier plsql tables. POSCO performance related.
400 /*
401 INDX,QP_FORMULA_PRICE_CALC_PVT.calculate.pra_cur,QP_PREQ_LINE_ATTRS_FRML_TMP_N1,PRICING_STATUS_CODE,1
402 INDX,QP_FORMULA_PRICE_CALC_PVT.calculate.pra_cur,QP_PREQ_LINE_ATTRS_FRML_TMP_N1,ATTRIBUTE_TYPE,2
403 INDX,QP_FORMULA_PRICE_CALC_PVT.calculate.pra_cur,QP_PREQ_LINE_ATTRS_FRML_TMP_N1,CONTEXT,3
404 INDX,QP_FORMULA_PRICE_CALC_PVT.calculate.pra_cur,QP_PREQ_LINE_ATTRS_FRML_TMP_N1,ATTRIBUTE,4
405 INDX,QP_FORMULA_PRICE_CALC_PVT.calculate.pra_cur,QP_PREQ_LINE_ATTRS_FRML_TMP_N1,LINE_INDEX,5
406 */
407 CURSOR pra_cur(a_pricing_attribute_context VARCHAR2,
408                a_pricing_attribute         VARCHAR2,
409                a_line_index                NUMBER)
410 IS
411   SELECT value_from
412   FROM   qp_preq_line_attrs_formula_tmp
413   WHERE  context = a_pricing_attribute_context
414   AND    attribute = a_pricing_attribute
415   AND    line_index = a_line_index
416   AND    attribute_type in ('PRICING','PRODUCT')
417   AND    pricing_status_code = QP_PREQ_GRP.G_STATUS_UNCHANGED;
418 
419 l_pra_rec            pra_cur%ROWTYPE;
420 l_attr_count         NUMBER := 0;
421 l_attr_flag          BOOLEAN := FALSE;
422 l_count              NUMBER := 0;
423 l_customized         VARCHAR2(1);
424 
425 l_attribute_id       NUMBER := NULL;
426 l_start_date_active  date   := NULL;
427 l_end_date_active    date   := NULL;
428 
429 l_formula       	VARCHAR2(2000) := '';
430 /* increased the length of l_formula,l_number and l_new_formula to 2000
431    to fix the bug 1539041 */
432 l_formula_value     NUMBER;
433 l_no_of_comps       NUMBER := 0;
434 i                   NUMBER;
435 j                   NUMBER := 1;
436 
437 --Added as part of POSCO changes
438 TYPE Formula_Line_Rec IS RECORD
439      (step_number                    NUMBER,
440       price_formula_line_type_code   VARCHAR2(10),
441       component_value                NUMBER,
442       price_formula_id               NUMBER,
443       line_index                     NUMBER,
444       list_line_type_code            VARCHAR2(30),--of the parent line
445       list_header_id                 NUMBER, --populated for factor list steps
446       list_line_id                   NUMBER  --populated with factor line id
447       );
448 
449 --Added as part of POSCO changes
450 TYPE Formula_Line_Tbl_Type IS TABLE OF Formula_Line_Rec INDEX BY BINARY_INTEGER;
451 
452 --Added as part of POSCO changes
453 l_formula_line_tbl  Formula_Line_Tbl_Type;
454 
455 l_req_line_attrs_tbl   QP_FORMULA_PRICE_CALC_PVT.REQ_LINE_ATTRS_TBL;
456 
457 l_sub_factors_rec      sub_factors_cur%ROWTYPE;
458 l_old_step_number      NUMBER := -99999999999999;
459 l_skip_factor          BOOLEAN := FALSE;
460 l_return_status        VARCHAR2(1);
461 
462 --Bug 2772214
463 l_pass_qualifiers varchar2(10) := FND_PROFILE.VALUE('QP_PASS_QUALIFIERS_TO_GET_CUSTOM');
464 
465 /*
466 INDX,QP_FORMULA_PRICE_CALC_PVT.calculate.req_line_attrs_cur,qp_npreq_line_attrs_tmp_N7,LINE_INDEX,1
467 INDX,QP_FORMULA_PRICE_CALC_PVT.calculate.req_line_attrs_cur,qp_npreq_line_attrs_tmp_N7,ATTRIBUTE_TYPE,2
468 */
469 CURSOR req_line_attrs_cur(a_line_index NUMBER)
470 IS
471   SELECT line_index, attribute_type, context, attribute, value_from value
472   --FROM   qp_npreq_line_attrs_tmp
473   -- bug2425851
474   FROM   qp_preq_line_attrs_formula_tmp
475   WHERE  line_index = a_line_index
476   AND    attribute_type IN ('PRICING','PRODUCT');
477 
478 -- Bug 2772214, Added qual cursor
479 CURSOR req_line_attrs_qual_cur(a_line_index NUMBER)
480 IS
481   SELECT line_index, attribute_type, context, attribute, value_from value
482  FROM   qp_preq_line_attrs_formula_tmp
483   WHERE  line_index = a_line_index
484   AND    attribute_type IN ('PRICING','PRODUCT','QUALIFIER');
485 
486 
487 l_null_step_number_tbl  Step_Number_Tbl_Type;
488 
489  E_FORMULA_NOT_FOUND EXCEPTION;
490  E_INVALID_FORMULA EXCEPTION;
491  E_FORMULA_COMPONENTS_REQ EXCEPTION;
492  E_CUSTOMIZE_GET_CUSTOM_PRICE EXCEPTION;
493  E_INVALID_NUMBER EXCEPTION;
494 
495 l_Operand_Tbl          QP_FORMULA_RULES_PVT.t_Operand_Tbl_Type;
496 
497 l_formula_start_time NUMBER;
498 l_formula_end_time   NUMBER;
499 l_time_difference    NUMBER;
500 l_formula_name       qp_price_formulas_tl.name%TYPE;
501 L_MAINT_DYN_SRC_VER VARCHAR2(1) := NVL(Fnd_Profile.value('QP_MAINTAIN_DYNAMIC_SOURCE_VERSIONS'),'N'); -- 13638721
502 l_pkg_ver VARCHAR2(10);
503 BEGIN
504 
505   l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
506   l_formula_start_time := dbms_utility.get_time;
507 
508   x_return_status := FND_API.G_RET_STS_SUCCESS;
509 
510   IF l_debug = FND_API.G_TRUE THEN
511   QP_PREQ_GRP.engine_debug('Start Formula...');
512   END IF;
513 
514   --added for formula messages
515    SELECT name
516      INTO l_formula_name
517      FROM qp_price_formulas_tl
518     WHERE price_formula_id = p_price_formula_id
519     AND   language = userenv('LANG')	   --bug#13250347
520      AND rownum<2;
521 
522   OPEN price_formulas_cur (p_price_formula_id, p_price_effective_date);
523 
524   FETCH price_formulas_cur INTO l_formula;
525 
526   IF price_formulas_cur%NOTFOUND THEN
527 
528            x_return_status := FND_API.G_RET_STS_ERROR;
529 
530            IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
531            THEN
532                RAISE E_FORMULA_NOT_FOUND;
533 --             FND_MSG_PUB.Add;
534            END IF;
535 
536   END IF;
537 
538   CLOSE price_formulas_cur;
539 
540   Parse_Formula (l_formula, l_return_status);
541 
542   IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
543     x_return_status := l_return_status;
544     RAISE E_INVALID_FORMULA;
545   END IF;
546 
547   --Get the no_of_components in the formula
548 /*
549 INDX,QP_FORMULA_PRICE_CALC_PVT.calculate.sel1,QP_PRICE_FORMULA_LINES_U1,PRICE_FORMULA_ID,1
550 */
551   SELECT count(*)
552   INTO   l_no_of_comps
553   FROM   qp_price_formula_lines
554   WHERE  price_formula_id = p_price_formula_id;
555 
556   IF l_no_of_comps = 0 THEN
557 
558          x_return_status := FND_API.G_RET_STS_ERROR;
559 
560          IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
561          THEN
562              RAISE E_FORMULA_COMPONENTS_REQ;
563 --           FND_MSG_PUB.Add;
564          END IF;
565 
566   END IF;
567   --smbalara 11898355 initializing all step values to NULL
568   FOR l_rec IN formula_lines_cur(p_price_formula_id)
569   LOOP
570 	l_Operand_Tbl(l_rec.step_number) :=null;
571   END loop;
572 
573 --process all factors before for bug 10273166 smbalara START
574     -- Now Process all Factor Lists in the formula
575 
576   IF l_debug = FND_API.G_TRUE THEN
577   QP_PREQ_GRP.engine_debug('Before populating plsql table of formula lines');
578 
579   END IF;
580   --Populate l_formula_line_tbl for all factor lists in the formula
581   FOR l_factors_rec IN factors_cur (p_price_formula_id, p_line_index,
582                                     p_price_effective_date)
583   LOOP
584     --l_Operand_Tbl(l_factors_rec.step_number):= null;--10273166,commented for 11898355
585 
586     IF l_debug = FND_API.G_TRUE THEN
587     QP_PREQ_GRP.engine_debug('enter factors_cur loop -step number -'||l_factors_rec.step_number);
588 
589     END IF;
590     IF l_skip_factor AND
591        l_factors_rec.step_number = l_old_step_number
592     THEN
593       l_old_step_number := l_factors_rec.step_number;
594       GOTO factors_loop;
595     END IF;
596 
597     IF l_debug = FND_API.G_TRUE THEN
598 	QP_PREQ_GRP.engine_debug('after skip check ');
599 	QP_PREQ_GRP.engine_debug('l_factors_rec.step_number:'||l_factors_rec.step_number);--11898355
600 	QP_PREQ_GRP.engine_debug('l_old_step_number:'|| l_old_step_number);--11898355
601 	QP_PREQ_GRP.engine_debug('l_factors_rec.group_count :'|| l_factors_rec.group_count );--11898355
602     END IF;
603     l_skip_factor := FALSE;
604 
605     --If no attributes with search_ind = 2 then no need to open sub_factors_cur
606     IF l_factors_rec.group_count = 0 THEN
607 
608       l_formula_line_tbl(l_factors_rec.step_number).component_value :=
609                                      l_factors_rec.operand;
610 
611       l_Operand_Tbl(l_factors_rec.step_number) := l_formula_line_tbl(l_factors_rec.step_number).component_value;
612 
613       l_formula_line_tbl(l_factors_rec.step_number).step_number :=
614                                      l_factors_rec.step_number;
615       l_formula_line_tbl(l_factors_rec.step_number).price_formula_line_type_code
616                                   := 'ML';
617       l_formula_line_tbl(l_factors_rec.step_number).price_formula_id :=
618                                      l_factors_rec.price_formula_id;
619       l_formula_line_tbl(l_factors_rec.step_number).line_index :=
620                                      p_line_index;
621       l_formula_line_tbl(l_factors_rec.step_number).list_header_id :=
622                                      l_factors_rec.list_header_id;
623       l_formula_line_tbl(l_factors_rec.step_number).list_line_id :=
624                                      l_factors_rec.list_line_id;
625       l_formula_line_tbl(l_factors_rec.step_number).list_line_type_code :=
626                                      p_list_line_type_code;
627 
628       IF l_debug = FND_API.G_TRUE THEN
629       QP_PREQ_GRP.engine_debug('In factors_cur, step = ' ||l_factors_rec.step_number);
630       QP_PREQ_GRP.engine_debug('Factor evaluated to = '||l_formula_line_tbl(l_factors_rec.step_number).component_value); --10273166
631       END IF;
632       l_skip_factor := TRUE;
633 
634       --added for formula messages
635       l_null_step_number_tbl.DELETE(l_factors_rec.step_number);
636 
637     ELSE --If l_factors_rec.group_count > 0
638      IF l_debug = FND_API.G_TRUE THEN
639 	QP_PREQ_GRP.engine_debug('sub factors check1 ');--11898355
640      END IF;
641       --sub_factors_cur has to be fetched to determine if all attributes match
642       OPEN sub_factors_cur(p_price_formula_id,
643                            p_line_index,
644                            p_price_effective_date,
645                            l_factors_rec.list_header_id,
646                            l_factors_rec.list_line_id,
647                            l_factors_rec.group_count);
648       FETCH sub_factors_cur
649       INTO  l_sub_factors_rec;
650 
651       IF sub_factors_cur%FOUND THEN
652 	IF l_debug = FND_API.G_TRUE THEN
653 		QP_PREQ_GRP.engine_debug('sub factors check2 ');--11898355
654 	END IF;
655         l_formula_line_tbl(l_factors_rec.step_number).component_value :=
656                                      l_factors_rec.operand;
657 
658         l_Operand_Tbl(l_factors_rec.step_number) := l_formula_line_tbl(l_factors_rec.step_number).component_value;
659 
660         l_formula_line_tbl(l_factors_rec.step_number).step_number :=
661                                      l_factors_rec.step_number;
662         l_formula_line_tbl(l_factors_rec.step_number).price_formula_line_type_code
663                                   := 'ML';
664         l_formula_line_tbl(l_factors_rec.step_number).price_formula_id :=
665                                      l_factors_rec.price_formula_id;
666         l_formula_line_tbl(l_factors_rec.step_number).line_index :=
667                                      p_line_index;
668         l_formula_line_tbl(l_factors_rec.step_number).list_header_id :=
669                                      l_factors_rec.list_header_id;
670         l_formula_line_tbl(l_factors_rec.step_number).list_line_id :=
671                                      l_factors_rec.list_line_id;
672         l_formula_line_tbl(l_factors_rec.step_number).list_line_type_code :=
673                                      p_list_line_type_code;
674 
675         IF l_debug = FND_API.G_TRUE THEN
676         QP_PREQ_GRP.engine_debug('In sub_factors_cur, step = ' ||l_factors_rec.step_number);
677 	 QP_PREQ_GRP.engine_debug('Sub Factor evaluated to = '||l_formula_line_tbl(l_factors_rec.step_number).component_value); --10273166
678         END IF;
679         l_skip_factor := TRUE;
680 
681         l_null_step_number_tbl.DELETE(l_factors_rec.step_number);
682       END IF;--sub_factors_cur%FOUND
683 
684       CLOSE sub_factors_cur;
685       IF l_debug = FND_API.G_TRUE THEN
686 	QP_PREQ_GRP.engine_debug('sub factors check3 ');--11898355
687       END IF;
688     END IF; --If l_factors_rec.group_count = 0
689 
690     l_old_step_number := l_factors_rec.step_number;
691     IF l_debug = FND_API.G_TRUE THEN
692 	QP_PREQ_GRP.engine_debug('factors check end');--11898355
693     END IF;
694 
695     <<factors_loop>>
696     null;
697 
698     IF l_debug = FND_API.G_TRUE THEN
699     QP_PREQ_GRP.engine_debug('exit factors_cur loop');
700 
701     END IF;
702   END LOOP; --Loop over factors_cur
703 
704   IF l_debug = FND_API.G_TRUE THEN
705   QP_PREQ_GRP.engine_debug('After populating plsql table of formula lines');
706 
707   END IF;
708 --process all factors before for bug 10273166 smbalara END
709 
710 --Change flexible mask to mask below for formula pattern use (Bug2195879)
711 qp_number.canonical_mask :=
712               '00999999999999999999999.99999999999999999999999999999999999999';
713   --Begin more POSCO changes.
714   FOR l_rec IN formula_lines_cur(p_price_formula_id)
715   LOOP
716 
717     IF l_rec.price_formula_line_type_code = 'NUM' THEN
718 
719       IF l_debug = FND_API.G_TRUE THEN
720       QP_PREQ_GRP.engine_debug('Formula Line Type = NUM, Step = '|| l_rec.step_number
721                         || ', Value = ' || l_rec.numeric_constant);
722       END IF;
723       l_formula_line_tbl(l_rec.step_number).component_value :=
724                                    l_rec.numeric_constant;
725 
726       l_Operand_Tbl(l_rec.step_number) := l_formula_line_tbl(l_rec.step_number).component_value;
727 
728       l_formula_line_tbl(l_rec.step_number).step_number := l_rec.step_number;
729       l_formula_line_tbl(l_rec.step_number).price_formula_line_type_code :=
730                                    l_rec.price_formula_line_type_code;
731       l_formula_line_tbl(l_rec.step_number).price_formula_id :=
732                                    l_rec.price_formula_id;
733       l_formula_line_tbl(l_rec.step_number).line_index := p_line_index;
734       l_formula_line_tbl(l_rec.step_number).list_header_id := null;
735       l_formula_line_tbl(l_rec.step_number).list_line_id := null;
736       l_formula_line_tbl(l_rec.step_number).list_line_type_code :=
737                                    p_list_line_type_code;
738 
739     ELSIF l_rec.price_formula_line_type_code = 'LP' THEN
740 
741       IF l_debug = FND_API.G_TRUE THEN
742       QP_PREQ_GRP.engine_debug('Formula Line Type = LP, Step = ' || l_rec.step_number
743                         || ', Value = '|| p_list_price);
744       END IF;
745       l_formula_line_tbl(l_rec.step_number).component_value := p_list_price;
746 
747       l_Operand_Tbl(l_rec.step_number) := l_formula_line_tbl(l_rec.step_number).component_value;
748 
749       l_formula_line_tbl(l_rec.step_number).step_number := l_rec.step_number;
750       l_formula_line_tbl(l_rec.step_number).price_formula_line_type_code :=
751                                    l_rec.price_formula_line_type_code;
752       l_formula_line_tbl(l_rec.step_number).price_formula_id :=
753                                    l_rec.price_formula_id;
754       l_formula_line_tbl(l_rec.step_number).line_index := p_line_index;
755       l_formula_line_tbl(l_rec.step_number).list_header_id := null;
756       l_formula_line_tbl(l_rec.step_number).list_line_id := null;
757       l_formula_line_tbl(l_rec.step_number).list_line_type_code :=
758                                    p_list_line_type_code;
759       --added for formula messages
760       IF p_list_price IS NULL THEN
761          l_null_step_number_tbl(l_rec.step_number):= l_rec.step_number;
762       END IF;
763 
764     ELSIF l_rec.price_formula_line_type_code = 'MV' THEN
765 
766       IF l_debug = FND_API.G_TRUE THEN
767       QP_PREQ_GRP.engine_debug('Formula Line Type = MV, Step = ' || l_rec.step_number || ', Value = '|| p_modifier_value);
768 
769       END IF;
770       l_formula_line_tbl(l_rec.step_number).component_value := p_modifier_value;
771 
772       l_Operand_Tbl(l_rec.step_number) := l_formula_line_tbl(l_rec.step_number).component_value;
773 
774       l_formula_line_tbl(l_rec.step_number).step_number := l_rec.step_number;
775       l_formula_line_tbl(l_rec.step_number).price_formula_line_type_code :=
776                                    l_rec.price_formula_line_type_code;
777       l_formula_line_tbl(l_rec.step_number).price_formula_id :=
778                                    l_rec.price_formula_id;
779       l_formula_line_tbl(l_rec.step_number).line_index := p_line_index;
780       l_formula_line_tbl(l_rec.step_number).list_header_id := null;
781       l_formula_line_tbl(l_rec.step_number).list_line_id := null;
782       l_formula_line_tbl(l_rec.step_number).list_line_type_code :=
783                                    p_list_line_type_code;
784       --added for formula messages
785       IF p_modifier_value IS NULL
786       THEN
787           l_null_step_number_tbl(l_rec.step_number) := l_rec.step_number;
788       END IF;
789 
790     ELSIF l_rec.price_formula_line_type_code = 'FUNC' THEN
791 
792       IF l_debug = FND_API.G_TRUE THEN
793       QP_PREQ_GRP.engine_debug('Formula Line Type = FUNC, Step = ' ||l_rec.step_number);
794       END IF;
795       l_customized := FND_PROFILE.VALUE('QP_GET_CUSTOM_PRICE_CUSTOMIZED');
796       IF l_customized = 'Y' THEN
797         IF l_debug = FND_API.G_TRUE THEN
798         QP_PREQ_GRP.engine_debug('l_customized is Y');
799 
800         END IF;
801         --Populate l_req_line_attrs_tbl
802         -- Bug 2772214, Added If condition
803        If nvl(l_pass_qualifiers, 'N') = 'N' Then
804         FOR l_line_attrs_rec IN req_line_attrs_cur(p_line_index)
805         LOOP
806           l_req_line_attrs_tbl(j) := l_line_attrs_rec;
807           j := j + 1;
808         END LOOP;
809        Else
810         FOR l_line_attrs_rec IN req_line_attrs_qual_cur(p_line_index)
811          LOOP
812           l_req_line_attrs_tbl(j) := l_line_attrs_rec;
813           j := j + 1;
814         END LOOP;
815        End If;
816 
817         --added for formula enhancement by dhgupta 3531890
818         l_req_line_attrs_tbl(j).line_index:=p_line_index;
819         l_req_line_attrs_tbl(j).attribute_type:=QP_GLOBALS.G_SPECIAL_ATTRIBUTE_TYPE;
820         l_req_line_attrs_tbl(j).context:=QP_GLOBALS.G_SPECIAL_CONTEXT;
821         l_req_line_attrs_tbl(j).attribute:=QP_GLOBALS.G_SPECIAL_ATTRIBUTE1;
822         l_req_line_attrs_tbl(j).value:=l_rec.step_number;
823         -- end 3531890
824 
825         BEGIN
826 	qp_debug_util.tstart('GET_CUSTOM_PRICE','Calculating the custom price in Formulas');
827         l_formula_line_tbl(l_rec.step_number).component_value :=
828                              QP_Custom.Get_Custom_Price(p_price_formula_id,
829                                                         p_list_price,
830                                                         p_price_effective_date,
831                                                         l_req_line_attrs_tbl);
832 	qp_debug_util.tstop('GET_CUSTOM_PRICE');
833         l_Operand_Tbl(l_rec.step_number) := l_formula_line_tbl(l_rec.step_number).component_value;
834 
835         l_formula_line_tbl(l_rec.step_number).step_number := l_rec.step_number;
836         l_formula_line_tbl(l_rec.step_number).price_formula_line_type_code :=
837                                        l_rec.price_formula_line_type_code;
838         l_formula_line_tbl(l_rec.step_number).price_formula_id :=
839                                        l_rec.price_formula_id;
840         l_formula_line_tbl(l_rec.step_number).line_index := p_line_index;
841         l_formula_line_tbl(l_rec.step_number).list_header_id := null;
842         l_formula_line_tbl(l_rec.step_number).list_line_id := null;
843         l_formula_line_tbl(l_rec.step_number).list_line_type_code :=
844                                        p_list_line_type_code;
845 
846         IF l_formula_line_tbl(l_rec.step_number).component_value  IS NULL
847         THEN
848             l_null_step_number_tbl(l_rec.step_number) := l_rec.step_number;
849         END IF;
850         EXCEPTION
851          WHEN OTHERS THEN
852             l_null_step_number_tbl(l_rec.step_number) := l_rec.step_number;
853         END;
854 
855       ELSE --If customized = 'N'
856         x_return_status := FND_API.G_RET_STS_ERROR;
857         IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
858         THEN
859           RAISE E_CUSTOMIZE_GET_CUSTOM_PRICE;
860         END IF;
861       END IF;
862 
863     ELSIF l_rec.price_formula_line_type_code = 'PRA' THEN
864 
865       IF l_debug = FND_API.G_TRUE THEN
866       QP_PREQ_GRP.engine_debug('Formula Line Type = PRA, Step = ' ||l_rec.step_number);
867 
868       END IF;
869       OPEN  pra_cur(l_rec.pricing_attribute_context, l_rec.pricing_attribute,
870                     p_line_index);
871       FETCH pra_cur INTO l_pra_rec;
872 
873       IF pra_cur%FOUND THEN
874         --Return the matching pricing attribute value that is found.
875         BEGIN
876 -- bug 2195879
877 IF l_debug = FND_API.G_TRUE THEN
878 QP_PREQ_GRP.engine_debug('l_pra_rec.value_from = '||l_pra_rec.value_from);
879 END IF;
880           l_formula_line_tbl(l_rec.step_number).component_value :=
881                 qp_number.canonical_to_number(l_pra_rec.value_from);
882 
883           l_Operand_Tbl(l_rec.step_number) := l_formula_line_tbl(l_rec.step_number).component_value;
884 
885 IF l_debug = FND_API.G_TRUE THEN
886 QP_PREQ_GRP.engine_debug('Just after pra cur value from to number conversion');
887 END IF;
888         EXCEPTION
889           WHEN OTHERS THEN
890             x_return_status := FND_API.G_RET_STS_ERROR;
891             IF l_debug = FND_API.G_TRUE THEN
892             QP_PREQ_GRP.engine_debug('Error converting PRA value to number');
893             END IF;
894             IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
895             THEN
896               RAISE E_INVALID_NUMBER;
897             END IF;
898         END; -- for Begin block
899 
900       ELSE  --If pra_cur%NOTFOUND
901         l_formula_line_tbl(l_rec.step_number).component_value := NULL;
902 
903         l_Operand_Tbl(l_rec.step_number) := l_formula_line_tbl(l_rec.step_number).component_value;
904         l_null_step_number_tbl(l_rec.step_number) := l_rec.step_number;
905 
906       END IF; --If pra_cur%FOUND
907 
908       CLOSE pra_cur;
909 
910       l_formula_line_tbl(l_rec.step_number).step_number := l_rec.step_number;
911       l_formula_line_tbl(l_rec.step_number).price_formula_line_type_code :=
912                                        l_rec.price_formula_line_type_code;
913       l_formula_line_tbl(l_rec.step_number).price_formula_id :=
914                                        l_rec.price_formula_id;
915       l_formula_line_tbl(l_rec.step_number).line_index := p_line_index;
916       l_formula_line_tbl(l_rec.step_number).list_header_id := null;
917       l_formula_line_tbl(l_rec.step_number).list_line_id := null;
918       l_formula_line_tbl(l_rec.step_number).list_line_type_code :=
919                                        p_list_line_type_code;
920 
921     ELSIF l_rec.price_formula_line_type_code = 'PLL' THEN
922 
923       IF l_debug = FND_API.G_TRUE THEN
924       QP_PREQ_GRP.engine_debug('Formula Line Type = PLL, Step = ' ||l_rec.step_number);
925       END IF;
926 /*
927 INDX,QP_FORMULA_PRICE_CALC_PVT.calculate.sel2,QP_LIST_LINES_PK,LIST_LINE_ID,1
928 */
929       BEGIN
930         SELECT operand
931         INTO   l_formula_line_tbl(l_rec.step_number).component_value
932         FROM   qp_list_lines
933         WHERE  list_line_id = l_rec.price_list_line_id;
934 
935         l_Operand_Tbl(l_rec.step_number) := l_formula_line_tbl(l_rec.step_number).component_value;
936 
937       EXCEPTION
938         WHEN OTHERS THEN
939           l_formula_line_tbl(l_rec.step_number).component_value :=  NULL;
940 
941           l_Operand_Tbl(l_rec.step_number) := l_formula_line_tbl(l_rec.step_number).component_value;
942           l_null_step_number_tbl(l_rec.step_number) := l_rec.step_number;
943       END;
944 
945       l_formula_line_tbl(l_rec.step_number).step_number := l_rec.step_number;
946       l_formula_line_tbl(l_rec.step_number).price_formula_line_type_code :=
947                                        l_rec.price_formula_line_type_code;
948       l_formula_line_tbl(l_rec.step_number).price_formula_id :=
949                                        l_rec.price_formula_id;
950       l_formula_line_tbl(l_rec.step_number).line_index := p_line_index;
951       l_formula_line_tbl(l_rec.step_number).list_header_id := null;
952       l_formula_line_tbl(l_rec.step_number).list_line_id := null;
953       l_formula_line_tbl(l_rec.step_number).list_line_type_code :=
954                                        p_list_line_type_code;
955 
956     ELSIF l_rec.price_formula_line_type_code = 'ML' THEN
957 
958       IF l_debug = FND_API.G_TRUE THEN
959       QP_PREQ_GRP.engine_debug('Formula Line Type = ML, Step = ' ||l_rec.step_number);
960       END IF;
961       --null; --Do nothing here. All factor lists will be processed together later
962       --l_Operand_Tbl(l_rec.step_number) :=null;----6726052,7249280 smbalara 10273166-commenting as factor list is processed above
963       --added for formula messages
964       l_null_step_number_tbl(l_rec.step_number) := l_rec.step_number;
965       --smbalara 11898355 start
966       l_formula_line_tbl(l_rec.step_number).step_number := l_rec.step_number;
967       l_formula_line_tbl(l_rec.step_number).price_formula_line_type_code :=
968                                        l_rec.price_formula_line_type_code;
969       l_formula_line_tbl(l_rec.step_number).price_formula_id :=
970                                        l_rec.price_formula_id;
971       l_formula_line_tbl(l_rec.step_number).line_index := p_line_index;
972       l_formula_line_tbl(l_rec.step_number).list_header_id := null;
973       l_formula_line_tbl(l_rec.step_number).list_line_id := null;
974       l_formula_line_tbl(l_rec.step_number).list_line_type_code := p_list_line_type_code;
975       --smbalara 11898355 end
976     ELSE --if price_formula_line_type_code is not one of the expected values.
977 
978       x_return_status := FND_API.G_RET_STS_ERROR;
979       IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
980       THEN
981         FND_MESSAGE.SET_NAME('QP','QP_INVALID_FORMULA_LINE_TYPE');
982       END IF;
983 
984     END IF; -- IF stmt comparing price_formula_line_type_code to various values.
985         l_req_line_attrs_tbl.delete; --3531890 attribute were getting accumulated for every step
986 -- smbalara bug 7188211
987 --Based on a profile option,formula step values will be inserted into qp_nformula_step_values_tmp
988 IF QP_PREQ_GRP.G_INSERT_FORMULA_STEP_VALUES = 'Y' THEN
989 	IF l_debug = FND_API.G_TRUE THEN
990 	QP_PREQ_GRP.engine_debug('Before populating formula stepvalues temp table');
991 	QP_PREQ_GRP.engine_debug('Value price_formula_id	='||l_formula_line_tbl(l_rec.step_number).price_formula_id );
992 	QP_PREQ_GRP.engine_debug('Value step_number		='||l_formula_line_tbl(l_rec.step_number).step_number );
993 	QP_PREQ_GRP.engine_debug('Value component_value	='||l_formula_line_tbl(l_rec.step_number).component_value );
994 	QP_PREQ_GRP.engine_debug('Value price_formula_line_type_code='||l_formula_line_tbl(l_rec.step_number).price_formula_line_type_code );
995 	QP_PREQ_GRP.engine_debug('Value line_index		='||l_formula_line_tbl(l_rec.step_number).line_index );
996 	QP_PREQ_GRP.engine_debug('Value list_line_type_code	='||l_formula_line_tbl(l_rec.step_number).list_line_type_code );
997 	QP_PREQ_GRP.engine_debug('Value list_header_id	='||l_formula_line_tbl(l_rec.step_number).list_header_id );
998 	QP_PREQ_GRP.engine_debug('Value list_line_id	='||l_formula_line_tbl(l_rec.step_number).list_line_id );
999 	END IF;
1000 
1001           INSERT INTO qp_nformula_step_values_tmp
1002           (price_formula_id,
1003            step_number,
1004            component_value,
1005            price_formula_line_type_code,
1006            line_index,
1007            list_line_type_code,
1008            list_header_id,
1009            list_line_id
1010           )
1011           VALUES
1012           (l_formula_line_tbl(l_rec.step_number).price_formula_id,
1013            l_formula_line_tbl(l_rec.step_number).step_number,
1014            l_formula_line_tbl(l_rec.step_number).component_value,
1015            l_formula_line_tbl(l_rec.step_number).price_formula_line_type_code,
1016            l_formula_line_tbl(l_rec.step_number).line_index,
1017            l_formula_line_tbl(l_rec.step_number).list_line_type_code,
1018            l_formula_line_tbl(l_rec.step_number).list_header_id,
1019            l_formula_line_tbl(l_rec.step_number).list_line_id
1020           );
1021 	IF l_debug = FND_API.G_TRUE THEN
1022 		QP_PREQ_GRP.engine_debug('After populating formula step values temp table');
1023 	END IF;
1024 END IF;
1025 -- smbalara bug 7188211
1026 END LOOP; --Loop over formula_lines_cur
1027 -- Change mask back to flexible mask
1028 qp_number.canonical_mask :=
1029               'FM999999999999999999999.9999999999999999999999999999999999999999';
1030 --commenting below for bug 10273166 smbalara START
1031   -- Now Process all Factor Lists in the formula
1032 
1033 /*  IF l_debug = FND_API.G_TRUE THEN
1034   QP_PREQ_GRP.engine_debug('Before populating plsql table of formula lines');
1035 
1036   END IF;
1037   --Populate l_formula_line_tbl for all factor lists in the formula
1038   FOR l_factors_rec IN factors_cur (p_price_formula_id, p_line_index,
1039                                     p_price_effective_date)
1040   LOOP
1041 
1042     IF l_debug = FND_API.G_TRUE THEN
1043     QP_PREQ_GRP.engine_debug('enter factors_cur loop');
1044 
1045     END IF;
1046     IF l_skip_factor AND
1047        l_factors_rec.step_number = l_old_step_number
1048     THEN
1049       l_old_step_number := l_factors_rec.step_number;
1050       GOTO factors_loop;
1051     END IF;
1052 
1053     IF l_debug = FND_API.G_TRUE THEN
1054     QP_PREQ_GRP.engine_debug('after skip check ');
1055 
1056     END IF;
1057     l_skip_factor := FALSE;
1058 
1059     --If no attributes with search_ind = 2 then no need to open sub_factors_cur
1060     IF l_factors_rec.group_count = 0 THEN
1061 
1062       l_formula_line_tbl(l_factors_rec.step_number).component_value :=
1063                                      l_factors_rec.operand;
1064 
1065       l_Operand_Tbl(l_factors_rec.step_number) := l_formula_line_tbl(l_factors_rec.step_number).component_value;
1066 
1067       l_formula_line_tbl(l_factors_rec.step_number).step_number :=
1068                                      l_factors_rec.step_number;
1069       l_formula_line_tbl(l_factors_rec.step_number).price_formula_line_type_code
1070                                   := 'ML';
1071       l_formula_line_tbl(l_factors_rec.step_number).price_formula_id :=
1072                                      l_factors_rec.price_formula_id;
1073       l_formula_line_tbl(l_factors_rec.step_number).line_index :=
1074                                      p_line_index;
1075       l_formula_line_tbl(l_factors_rec.step_number).list_header_id :=
1076                                      l_factors_rec.list_header_id;
1077       l_formula_line_tbl(l_factors_rec.step_number).list_line_id :=
1078                                      l_factors_rec.list_line_id;
1079       l_formula_line_tbl(l_factors_rec.step_number).list_line_type_code :=
1080                                      p_list_line_type_code;
1081 
1082       IF l_debug = FND_API.G_TRUE THEN
1083       QP_PREQ_GRP.engine_debug('In factors_cur, step = ' ||l_factors_rec.step_number);
1084 
1085       END IF;
1086       l_skip_factor := TRUE;
1087 
1088       --added for formula messages
1089       l_null_step_number_tbl.DELETE(l_factors_rec.step_number);
1090 
1091     ELSE --If l_factors_rec.group_count > 0
1092       --sub_factors_cur has to be fetched to determine if all attributes match
1093       OPEN sub_factors_cur(p_price_formula_id,
1094                            p_line_index,
1095                            p_price_effective_date,
1096                            l_factors_rec.list_header_id,
1097                            l_factors_rec.list_line_id,
1098                            l_factors_rec.group_count);
1099       FETCH sub_factors_cur
1100       INTO  l_sub_factors_rec;
1101 
1102       IF sub_factors_cur%FOUND THEN
1103 
1104         l_formula_line_tbl(l_factors_rec.step_number).component_value :=
1105                                      l_factors_rec.operand;
1106 
1107         l_Operand_Tbl(l_factors_rec.step_number) := l_formula_line_tbl(l_factors_rec.step_number).component_value;
1108 
1109         l_formula_line_tbl(l_factors_rec.step_number).step_number :=
1110                                      l_factors_rec.step_number;
1111         l_formula_line_tbl(l_factors_rec.step_number).price_formula_line_type_code
1112                                   := 'ML';
1113         l_formula_line_tbl(l_factors_rec.step_number).price_formula_id :=
1114                                      l_factors_rec.price_formula_id;
1115         l_formula_line_tbl(l_factors_rec.step_number).line_index :=
1116                                      p_line_index;
1117         l_formula_line_tbl(l_factors_rec.step_number).list_header_id :=
1118                                      l_factors_rec.list_header_id;
1119         l_formula_line_tbl(l_factors_rec.step_number).list_line_id :=
1120                                      l_factors_rec.list_line_id;
1121         l_formula_line_tbl(l_factors_rec.step_number).list_line_type_code :=
1122                                      p_list_line_type_code;
1123 
1124         IF l_debug = FND_API.G_TRUE THEN
1125         QP_PREQ_GRP.engine_debug('In sub_factors_cur, step = ' ||l_factors_rec.step_number);
1126 
1127         END IF;
1128         l_skip_factor := TRUE;
1129 
1130         l_null_step_number_tbl.DELETE(l_factors_rec.step_number);
1131       END IF;--sub_factors_cur%FOUND
1132 
1133       CLOSE sub_factors_cur;
1134 
1135     END IF; --If l_factors_rec.group_count = 0
1136 
1137     l_old_step_number := l_factors_rec.step_number;
1138 
1139     <<factors_loop>>
1140     null;
1141 
1142     IF l_debug = FND_API.G_TRUE THEN
1143     QP_PREQ_GRP.engine_debug('exit factors_cur loop');
1144 
1145     END IF;
1146   END LOOP; --Loop over factors_cur
1147 
1148   IF l_debug = FND_API.G_TRUE THEN
1149   QP_PREQ_GRP.engine_debug('After populating plsql table of formula lines');
1150 
1151   END IF;*/
1152    --commenting above for bug 10273166 smbalara END
1153   --Based on a profile option, loop over plsql table of formula lines to
1154   --populate qp_nformula_step_values_tmp table
1155 /* commented for bug 7188211 - temp table insert moved above
1156   IF QP_PREQ_GRP.G_INSERT_FORMULA_STEP_VALUES = 'Y' THEN
1157 
1158     IF l_debug = FND_API.G_TRUE THEN
1159     QP_PREQ_GRP.engine_debug('Before populating formula stepvalues temp table');
1160 
1161     END IF;
1162     i:= l_formula_line_tbl.FIRST; --set loop index to first element in plsql tbl
1163 
1164     WHILE i IS NOT NULL
1165     LOOP
1166       BEGIN
1167 
1168         --Insert into temp table only for formulas attached to Price List Lines.
1169         IF l_formula_line_tbl(i).list_line_type_code = 'PLL'
1170         THEN
1171 
1172           INSERT INTO qp_nformula_step_values_tmp
1173           (price_formula_id,
1174            step_number,
1175            component_value,
1176            price_formula_line_type_code,
1177            line_index,
1178            list_line_type_code,
1179            list_header_id,
1180            list_line_id
1181           )
1182           VALUES
1183           (l_formula_line_tbl(i).price_formula_id,
1184            l_formula_line_tbl(i).step_number,
1185            l_formula_line_tbl(i).component_value,
1186            l_formula_line_tbl(i).price_formula_line_type_code,
1187            l_formula_line_tbl(i).line_index,
1188            l_formula_line_tbl(i).list_line_type_code,
1189            l_formula_line_tbl(i).list_header_id,
1190            l_formula_line_tbl(i).list_line_id
1191           );
1192 
1193         END IF; --If list_line_type_code = 'PLL'
1194 
1195       EXCEPTION
1196         WHEN OTHERS THEN
1197          IF l_debug = FND_API.G_TRUE THEN
1198          QP_PREQ_GRP.engine_debug(substr(sqlerrm, 1, 240));
1199          END IF;
1200       END;
1201 
1202       i :=  l_formula_line_tbl.NEXT(i);--set i to next notnull position in plsql
1203 
1204     END LOOP; --loop over l_formula_line_tbl
1205 
1206     IF l_debug = FND_API.G_TRUE THEN
1207     QP_PREQ_GRP.engine_debug('After populating formula step values temp table');
1208 
1209     END IF;
1210   END IF; --If profile option is set
1211 commented for bug 7188211*/
1212   IF l_debug = FND_API.G_TRUE THEN
1213   QP_PREQ_GRP.engine_debug('Before Calling - QP_BUILD_FORMULA_RULES.Get_Formula_Values');
1214   QP_PREQ_GRP.engine_debug('For Formula : '||l_formula);----6726052,7249280 smbalara
1215   QP_PREQ_GRP.engine_debug('Maintain Dynamic pkgs profile is :'||L_MAINT_DYN_SRC_VER);
1216   END IF;
1217   -- 13638721
1218   IF (L_MAINT_DYN_SRC_VER='N') THEN
1219   QP_BUILD_FORMULA_RULES.Get_Formula_Values(l_formula,
1220                                             l_Operand_Tbl,
1221                                             'G',               --sfiresto
1222                                             l_formula_value,
1223                                             l_return_status);
1224   ELSE
1225       BEGIN
1226       SELECT SEEDED_VALUE INTO l_pkg_ver FROM qp_parameters_b WHERE PARAMETER_CODE='BLD_FORMULA_ACTV_VER' AND ROWNUM=1;
1227 	  IF l_debug = FND_API.G_TRUE THEN
1228 	  QP_PREQ_GRP.engine_debug('1. found row in qp_params for code BLD_FORMULA_ACTV_VER pkg ver : ' || l_pkg_ver);
1229 	  END IF;
1230       EXCEPTION
1231       WHEN OTHERS THEN
1232         l_pkg_ver:='0';
1233 	  IF l_debug = FND_API.G_TRUE THEN
1234 	  QP_PREQ_GRP.engine_debug('1. when others row in qp_params for code BLD_FORMULA_ACTV_VER pkg ver: ' || l_pkg_ver);
1235 	  END IF;
1236       END;
1237         IF (l_pkg_ver='5') THEN
1238 		  QP_BUILD_FORMULA_RULES5.Get_Formula_Values(l_formula,
1239 							    l_Operand_Tbl,
1240 							    'G',
1241 							    l_formula_value,
1242 							    l_return_status);
1243         ELSIF (l_pkg_ver='4') THEN
1244 		  QP_BUILD_FORMULA_RULES4.Get_Formula_Values(l_formula,
1245 							    l_Operand_Tbl,
1246 							    'G',
1247 							    l_formula_value,
1248 							    l_return_status);
1249         ELSIF (l_pkg_ver='3') THEN
1250 		  QP_BUILD_FORMULA_RULES3.Get_Formula_Values(l_formula,
1251 							    l_Operand_Tbl,
1252 							    'G',
1253 							    l_formula_value,
1254 							    l_return_status);
1255         ELSIF (l_pkg_ver='2') THEN
1256 		  QP_BUILD_FORMULA_RULES2.Get_Formula_Values(l_formula,
1257 							    l_Operand_Tbl,
1258 							    'G',
1259 							    l_formula_value,
1260 							    l_return_status);
1261         ELSIF (l_pkg_ver='1') THEN
1262 		  QP_BUILD_FORMULA_RULES1.Get_Formula_Values(l_formula,
1263 							    l_Operand_Tbl,
1264 							    'G',
1265 							    l_formula_value,
1266 							    l_return_status);
1267         ELSE
1268 		  QP_BUILD_FORMULA_RULES.Get_Formula_Values(l_formula,
1269 							    l_Operand_Tbl,
1270 							    'G',
1271 							    l_formula_value,
1272 							    l_return_status);
1273         END IF;
1274   END IF;
1275   -- 13638721
1276 
1277   IF l_debug = FND_API.G_TRUE THEN
1278   QP_PREQ_GRP.engine_debug('After Calling - QP_BUILD_FORMULA_RULES.Get_Formula_Values');
1279 
1280   QP_PREQ_GRP.engine_debug('Return Status from Get_Formula_Values ' || l_return_status);
1281 
1282   END IF;
1283   IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) OR (l_formula_value IS NULL) THEN
1284     l_formula_value := Select_From_Dual(l_formula, l_operand_tbl);
1285 
1286     IF l_formula_value IS NULL THEN
1287       x_return_status := FND_API.G_RET_STS_ERROR;
1288       IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1289       THEN
1290         Set_Message( p_price_formula_id     => p_price_formula_id,
1291                    p_formula_name         => l_formula_name,
1292                    p_null_step_number_tbl => l_null_step_number_tbl);
1293         l_null_step_number_tbl.DELETE;
1294       END IF;
1295     END IF;
1296 
1297   END IF;
1298 
1299   IF l_debug = FND_API.G_TRUE THEN
1300   QP_PREQ_GRP.engine_debug('Formula evaluated to ' || l_formula_value);
1301 
1302   QP_PREQ_GRP.engine_debug('Formula Return Status ' || l_return_status);
1303 
1304   END IF;
1305   l_formula_line_tbl.DELETE; --Clear the temp table table
1306 
1307   l_formula_end_time := dbms_utility.get_time;
1308   l_time_difference := (l_formula_end_time - l_formula_start_time)/100 ;
1309 
1310   IF l_debug = FND_API.G_TRUE THEN
1311   QP_PREQ_GRP.engine_debug('##### Total Time in QP_FORMULA_PRICE_CALC_PVT(in sec) : ' || l_time_difference || ' #####');
1312 
1313   END IF;
1314   RETURN l_formula_value;
1315   --End more POSCO changes.
1316 
1317 EXCEPTION
1318 
1319 WHEN E_FORMULA_NOT_FOUND THEN
1320 x_return_status := FND_API.G_RET_STS_ERROR;
1321 fnd_message.set_name('QP','QP_FORMULA_NOT_FOUND');
1322 fnd_message.set_token('FORMULA_NAME',l_formula_name);
1323 -- Change mask back to flexible mask
1324 qp_number.canonical_mask := 'FM999999999999999999999.9999999999999999999999999999999999999999';
1325 RETURN l_formula_value;
1326 
1327 WHEN E_INVALID_FORMULA THEN
1328 x_return_status := FND_API.G_RET_STS_ERROR;
1329 fnd_message.set_name('QP','QP_INVALID_FORMULA');
1330 fnd_message.set_token('FORMULA_NAME',l_formula_name);
1331 -- Change mask back to flexible mask
1332 qp_number.canonical_mask := 'FM999999999999999999999.9999999999999999999999999999999999999999';
1333 RETURN l_formula_value;
1334 
1335 WHEN  E_FORMULA_COMPONENTS_REQ THEN
1336 x_return_status := FND_API.G_RET_STS_ERROR;
1337 fnd_message.set_name('QP','QP_FORMULA_COMPONENTS_REQ');
1338 fnd_message.set_token('FORMULA_NAME',l_formula_name);
1339 -- Change mask back to flexible mask
1340 qp_number.canonical_mask := 'FM999999999999999999999.9999999999999999999999999999999999999999';
1341 RETURN l_formula_value;
1342 
1343 WHEN  E_CUSTOMIZE_GET_CUSTOM_PRICE THEN
1344 x_return_status := FND_API.G_RET_STS_ERROR;
1345 fnd_message.set_name('QP','QP_CUSTOMIZE_GET_CUSTOM_PRICE');
1346 fnd_message.set_token('FORMULA_NAME',l_formula_name);
1347 -- Change mask back to flexible mask
1348 qp_number.canonical_mask := 'FM999999999999999999999.9999999999999999999999999999999999999999';
1349 RETURN l_formula_value;
1350 
1351 WHEN  E_INVALID_NUMBER THEN
1352 x_return_status := FND_API.G_RET_STS_ERROR;
1353 fnd_message.set_name('QP','QP_INVALID_NUMBER');
1354 fnd_message.set_token('FORMULA_NAME',l_formula_name);
1355 -- Change mask back to flexible mask
1356 qp_number.canonical_mask := 'FM999999999999999999999.9999999999999999999999999999999999999999';
1357 RETURN l_formula_value;
1358 
1359     WHEN FND_API.G_EXC_ERROR THEN
1360 
1361       x_return_status := FND_API.G_RET_STS_ERROR;
1362 
1363       IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1364       THEN
1365         FND_MESSAGE.SET_NAME('QP','QP_FORMULA_FAILED');
1366       END IF;
1367       IF l_debug = FND_API.G_TRUE THEN
1368       QP_PREQ_GRP.engine_debug('Exception '||substr(sqlerrm, 1, 240));
1369       QP_PREQ_GRP.engine_debug('Exception occurred. Formula value returned is ' ||
1370                        l_formula_value);
1371       END IF;
1372 
1373       l_formula_line_tbl.DELETE; --Clear the temp table table
1374 
1375       -- Change mask back to flexible mask
1376       qp_number.canonical_mask := 'FM999999999999999999999.9999999999999999999999999999999999999999';
1377 
1378       RETURN l_formula_value;
1379 
1380     WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1381 
1382       x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1383       IF l_debug = FND_API.G_TRUE THEN
1384       QP_PREQ_GRP.engine_debug('Unexpected Exception '||substr(sqlerrm, 1, 240));
1385       QP_PREQ_GRP.engine_debug('Exception occurred. Formula value returned is ' ||
1386                        l_formula_value);
1387 
1388       END IF;
1389       l_formula_line_tbl.DELETE; --Clear the temp table table
1390 
1391       -- Change mask back to flexible mask
1392       qp_number.canonical_mask := 'FM999999999999999999999.9999999999999999999999999999999999999999';
1393 
1394       RETURN l_formula_value;
1395 
1396     WHEN OTHERS THEN
1397 
1398       x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1399 
1400       IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1401       THEN
1402         FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,
1403                                 'Calculate:'||sqlerrm);
1404       END IF;
1405 
1406       IF l_debug = FND_API.G_TRUE THEN
1407       QP_PREQ_GRP.engine_debug('Other Exception '||substr(sqlerrm, 1, 240));
1408       QP_PREQ_GRP.engine_debug('Exception occurred. Formula value returned is ' ||
1409                          l_formula_value);
1410 
1411       END IF;
1412       l_formula_line_tbl.DELETE; --Clear the temp table table
1413 
1414       -- Change mask back to flexible mask
1415       qp_number.canonical_mask := 'FM999999999999999999999.9999999999999999999999999999999999999999';
1416 
1417       RETURN l_formula_value;
1418 
1419 END Calculate;
1420 
1421 PROCEDURE Set_Message(p_price_formula_id     IN NUMBER,
1422                       p_formula_name         IN VARCHAR2,
1423                       p_null_step_number_tbl IN STEP_NUMBER_TBL_TYPE)
1424 
1425 IS
1426 
1427 l_price_formula_line_type_code qp_price_formula_lines.price_formula_line_type_code%TYPE;
1428 l_pricing_attribute_context    qp_price_formula_lines.pricing_attribute_context%TYPE;
1429 l_pricing_attribute            qp_price_formula_lines.pricing_attribute%TYPE;
1430 l_all_steps                    VARCHAR2(100);
1431 l_formula_name                 qp_price_formulas_tl.name%TYPE;
1432 l_index                        NUMBER;
1433 l_attribute                    VARCHAR2(80);
1434 --added for display of right message in case of undefined step.
1435 --can be removed once validation is done in formula form.
1436 l_no_of_comps                  NUMBER;
1437 l_formula                      VARCHAR2(2000);
1438 l_count                        NUMBER :=0;
1439 l_char                         VARCHAR2(1) :='';
1440 l_number                       VARCHAR2(2000) :='';
1441 
1442 BEGIN
1443 
1444    l_formula_name := p_formula_name;
1445    --added for display of right message in case of undefined step
1446    -- query on formula ID instead of name, SQL repos
1447    SELECT formula into l_formula from qp_price_formulas_b
1448    WHERE price_formula_id = p_price_formula_id;
1449 
1450   SELECT count(*) into l_no_of_comps from qp_price_formula_lines
1451    WHERE price_formula_id = p_price_formula_id;
1452 
1453   FOR i IN 1..LENGTH(l_formula) LOOP
1454     l_char :=SUBSTR(l_formula,i,1);
1455    IF (l_char = '0') OR (l_char = '1') OR (l_char = '2') OR (l_char = '3')
1456        OR (l_char = '4') OR (l_char = '5') OR (l_char ='6')
1457        OR (l_char = '7') OR (l_char = '8') OR (l_char = '9')
1458    THEN
1459       l_number :=l_number ||l_char;
1460        IF (i = LENGTH(l_formula))
1461         THEN
1462           l_count :=l_count+1;
1463           l_number :='';
1464        END IF;
1465    ELSE
1466      IF l_number is NOT NULL
1467        THEN
1468          l_count :=l_count+1;
1469          l_number :='';
1470      END IF;
1471    END IF;
1472  END LOOP;
1473 --added for display of right message in case of undefined step
1474    IF p_null_step_number_tbl.COUNT = 1 AND l_count = l_no_of_comps
1475    THEN
1476      l_index := p_null_step_number_tbl.FIRST;
1477      SELECT price_formula_line_type_code,
1478             pricing_attribute_context,
1479             pricing_attribute
1480        INTO l_price_formula_line_type_code,
1481             l_pricing_attribute_context,
1482             l_pricing_attribute
1483        FROM qp_price_formula_lines
1484       WHERE price_formula_id = p_price_formula_id
1485         AND step_number = l_index;
1486 
1487       IF     l_price_formula_line_type_code = 'LP'
1488       THEN
1489          fnd_message.set_name('QP','QP_FORMULA_LIST_PRICE_NULL');
1490 
1491       ELSIF  l_price_formula_line_type_code = 'MV'
1492       THEN
1493          fnd_message.set_name('QP','QP_FORMULA_MODIFIER_VALUE_NULL');
1494 
1495       ELSIF  l_price_formula_line_type_code = 'FUNC'
1496       THEN
1497          fnd_message.set_name('QP','QP_FORMULA_GET_CUSTOM_PRICE');
1498 
1499       ELSIF  l_price_formula_line_type_code = 'PRA'
1500       THEN
1501          SELECT     nvl(SEGMENTS_TL.SEEDED_SEGMENT_NAME,SEGMENTS_TL.USER_SEGMENT_NAME)
1502          INTO  l_attribute
1503  	 FROM  qp_segments_b SEGMENTS, qp_prc_contexts_b PCONTEXTS , qp_segments_tl SEGMENTS_TL
1504 		WHERE pcontexts.prc_context_code       =  l_pricing_attribute_context
1505 		AND   segments.segment_mapping_column  =  l_pricing_attribute
1506 		AND   segments.prc_context_id          =  pcontexts.prc_context_id
1507 		AND   segments.segment_id              =  segments_tl.segment_id
1508 		AND   segments_tl.language	       = userenv('LANG') --bug#13250347
1509                 AND   rownum<2;
1510 
1511          fnd_message.set_name('QP','QP_PRICING_ATTRIBUTE_NULL');
1512          fnd_message.set_token('CONTEXT',l_pricing_attribute_context);
1513          fnd_message.set_token('ATTRIBUTE',l_attribute);
1514 
1515       ELSIF  l_price_formula_line_type_code = 'PLL'
1516       THEN
1517          fnd_message.set_name('QP','QP_PRICE_LIST_LINE_NOT_EXISTS');
1518 
1519       ELSIF  l_price_formula_line_type_code = 'ML'
1520       THEN
1521          fnd_message.set_name('QP','QP_FACTOR_LIST_NULL');
1522       END IF;
1523       fnd_message.set_token('STEP_NUMBER',l_index);
1524 
1525    ELSIF p_null_step_number_tbl.COUNT>1 AND l_count = l_no_of_comps
1526    THEN                                        -- more than one step nulling out
1527       l_index := p_null_step_number_tbl.FIRST;
1528       WHILE l_index IS NOT NULL
1529       LOOP
1530          IF l_index = p_null_step_number_tbl.LAST
1531          THEN
1532             l_all_steps := l_all_steps || p_null_step_number_tbl(l_index);
1533          ELSE
1534             l_all_steps := l_all_steps || p_null_step_number_tbl(l_index) || ', ';
1535          END IF;
1536          l_index := p_null_step_number_tbl.NEXT(l_index);
1537       END LOOP;
1538          fnd_message.set_name('QP','QP_NULL_STEP_NUMBER');
1539          fnd_message.set_token('STEP_NUMBERS',l_all_steps);
1540    ELSE
1541          fnd_message.set_name('QP','QP_STEP_NO_UNDEFINED');
1542    END IF;
1543    fnd_message.set_token('FORMULA_NAME',l_formula_name);
1544 END Set_Message;
1545 
1546 
1547 -----------------------------------------------------------------------
1548 -- Wrapper for QP_Build_Formula_Rules.Get_Formula_Values called by Java
1549 -- Formula Engine.  The JDBC call will pass in a serialized string of
1550 -- operands, which this procedure deserializes into a PL/SQL table to
1551 -- pass to Get_Formula_Values
1552 -----------------------------------------------------------------------
1553 PROCEDURE Java_Get_Formula_Values(p_formula          IN VARCHAR2,
1554                                   p_operands_str     IN VARCHAR2,
1555                                   p_procedure_type   IN VARCHAR2,
1556                                   x_formula_value    OUT NOCOPY NUMBER,
1557                                   x_return_status    OUT NOCOPY VARCHAR2)
1558 IS
1559   head NUMBER;
1560   tail NUMBER;
1561   l_step_num NUMBER;
1562   l_operand_tbl QP_FORMULA_RULES_PVT.t_Operand_Tbl_Type;
1563 BEGIN
1564   -- parse the operands string into a table
1565   -- first read in step number, followed by step values
1566   -- head and tail indicate start and end positions of search in the string
1567   head := 0;
1568   tail := instr(p_operands_str, '|', head+1);
1569   WHILE tail <> 0
1570   LOOP
1571     l_step_num := substr(p_operands_str, head+1, tail-head-1); -- step number
1572     head := tail;
1573     tail := instr(p_operands_str, '|', head+1);
1574     l_operand_tbl(l_step_num) := substr(p_operands_str, head+1, tail-head-1);
1575     head := tail;
1576     tail := instr(p_operands_str, '|', head+1);
1577   END LOOP;
1578 
1579   QP_Build_Formula_Rules.Get_Formula_Values(p_formula, l_operand_tbl, p_procedure_type,
1580                                             x_formula_value, x_return_status);
1581 
1582   IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) OR (x_formula_value IS NULL) THEN
1583     x_formula_value := Select_From_Dual(p_formula, l_operand_tbl);
1584 
1585     IF x_formula_value IS NULL THEN
1586       x_return_status := FND_API.G_RET_STS_ERROR;
1587     ELSE
1588       x_return_status := FND_API.G_RET_STS_SUCCESS;
1589     END IF;
1590   END IF;
1591 END Java_Get_Formula_Values;
1592 
1593 END QP_FORMULA_PRICE_CALC_PVT;