[Home] [Help]
PACKAGE BODY: APPS.QP_UPDATE_FORMULAPRICE_PVT
Source
1 PACKAGE BODY Qp_Update_Formulaprice_Pvt AS
2 /* $Header: QPXVUFPB.pls 120.8.12010000.4 2009/04/21 07:13:01 smbalara ship $ */
3
4
5 PROCEDURE Update_Formula_Price
6 (
7 errbuf OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
8 retcode OUT NOCOPY /* file.sql.39 change */ NUMBER,
9 p_update_flagged_items IN VARCHAR2,
10 p_retrieve_all_flag IN VARCHAR2,
11 p_price_formula_id IN NUMBER
12 )
13 IS
14 l_conc_request_id NUMBER := -1;
15 l_conc_program_application_id NUMBER := -1;
16 l_conc_program_id NUMBER := -1;
17 l_conc_login_id NUMBER := -1;
18 l_user_id NUMBER := -1;
19 l_price_formula_id NUMBER;
20 l_name VARCHAR2(1000);
21 l_formula VARCHAR2(1000);
22 l_sysdate DATE;
23 l_step_count NUMBER := 0;
24 l_req_line_attrs_tbl Qp_Formula_Price_Calc_Pvt.req_line_attrs_tbl;
25
26 x_return_status VARCHAR2(30) := '';
27 l_error_message VARCHAR2(240) := '';
28 l_list_price NUMBER := 0;
29 l_rounding_factor NUMBER := -2;
30 l_price_rounding VARCHAR2(50):='';
31 NEGATIVE_VALUE EXCEPTION;
32
33 CURSOR qp_price_formulas_cur(a_price_formula_id NUMBER,
34 a_retrieve_all_flag VARCHAR2)
35 IS
36 SELECT *
37 FROM qp_price_formulas_vl
38 WHERE price_formula_id = DECODE (a_retrieve_all_flag,
39 'Y', price_formula_id, a_price_formula_id)
40 AND (start_date_active IS NULL OR start_date_active <= SYSDATE)
41 AND (end_date_active IS NULL OR end_date_active >= SYSDATE);
42
43 /*
44 CURSOR qp_list_lines_cur(a_price_formula_id NUMBER,
45 a_update_flagged_items VARCHAR2)
46 IS
47 SELECT *
48 FROM qp_list_lines
49 WHERE generate_using_formula_id = a_price_formula_id
50 AND NVL(reprice_flag, 'N') = DECODE (a_update_flagged_items,
51 'Y', 'Y', NVL(reprice_flag, 'N'))
52 FOR UPDATE;
53 */
54
55 CURSOR qp_pricing_attributes_cur(a_list_line_id NUMBER)
56 IS
57 SELECT *
58 FROM qp_pricing_attributes
59 WHERE list_line_id = a_list_line_id;
60
61 TYPE QpListLinesCurTyp IS REF CURSOR;
62 qp_list_lines_cursor QpListLinesCurTyp;
63
64 l_lines_rec QP_LIST_LINES%ROWTYPE;
65
66 BEGIN
67
68 -- Bug#4968517 - Turn Debug ON.
69 Qp_Preq_Grp.Set_QP_Debug;
70
71 l_conc_request_id := Fnd_Global.CONC_REQUEST_ID;
72 l_conc_program_id := Fnd_Global.CONC_PROGRAM_ID;
73 l_user_id := Fnd_Global.USER_ID;
74 l_conc_login_id := Fnd_Global.CONC_LOGIN_ID;
75 l_conc_program_application_id := Fnd_Global.PROG_APPL_ID;
76
77 l_sysdate := SYSDATE;
78
79 --Change flexible mask to mask below for formula pattern use.
80 Qp_Number.canonical_mask :=
81 '00999999999999999999999.99999999999999999999999999999999999999';
82
83 /* Select the Price Formulas which are to be used to update list prices. */
84 /* Select all or a specific formula depending on the option selected. */
85
86 --dbms_output.put_line('looping through the qp_price_formulas_cur');
87
88 FOR l_formulas_rec IN qp_price_formulas_cur(p_price_formula_id,
89 p_retrieve_all_flag)
90 LOOP
91 --dbms_output.put_line('inside qp_price_formulas_cur formula_id: '||l_formulas_rec.price_formula_id);
92
93 Qp_Formula_Price_Calc_Pvt.Parse_Formula(l_formulas_rec.formula,
94 x_return_status);
95
96 IF x_return_status <> Fnd_Api.G_RET_STS_SUCCESS THEN
97 l_error_message := Fnd_Message.GET;
98 Fnd_File.put_line(Fnd_File.LOG,l_error_message);
99 --dbms_output.put_line('error in formula parsing ' || l_error_message);
100 END IF;
101 --modified where condition for cursor below for fix 8429665
102 IF p_update_flagged_items = 'Y' THEN
103 OPEN qp_list_lines_cursor FOR
104 SELECT *
105 FROM qp_list_lines
106 WHERE generate_using_formula_id = l_formulas_rec.price_formula_id
107 AND reprice_flag = 'Y'
108 FOR UPDATE;
109 ELSE
110 OPEN qp_list_lines_cursor FOR
111 SELECT *
112 FROM qp_list_lines
113 WHERE generate_using_formula_id = l_formulas_rec.price_formula_id
114 FOR UPDATE;
115 END IF;
116
117 FETCH qp_list_lines_cursor INTO l_lines_rec;
118 WHILE qp_list_lines_cursor%FOUND LOOP
119
120 --DBMS_OUTPUT.PUT_LINE ('>>>>>>>>>>>> inside qp_list_lines_cursor loop list_line_id: '||l_lines_rec.list_line_id);
121
122
123 Qp_Number.canonical_mask :=
124 '00999999999999999999999.99999999999999999999999999999999999999'; --Added for 2884567
125 l_req_line_attrs_tbl.DELETE; /* Empty the plsql table for each list line */
126
127 --Delete already existing rows from formula tmp table
128 --DELETE FROM qp_preq_line_attrs_formula_tmp;
129 --dbms_output.put_line('deleted rows from qp_preq_line_attrs_formula_tmp');
130
131 FOR l_attributes_rec IN qp_pricing_attributes_cur(l_lines_rec.list_line_id)
132 LOOP
133
134 /* Get the Product Info from any one pricing attribute of a list line*/
135 IF qp_pricing_attributes_cur%ROWCOUNT = 1 THEN
136
137 --Insert the product information record into the temp table since
138 --the formula processing code has been changed(bug 1806928) to look
139 --into temp tables for factor processing due to performance reasons.
140
141 -- IF l_attributes_rec.pricing_attribute_datatype = 'N' THEN
142 -- bug2425851
143
144 IF l_attributes_rec.product_attribute_datatype = 'N' THEN
145 --Insert request line attrs with datatype = 'N'
146 INSERT INTO qp_preq_line_attrs_formula_tmp
147 (
148 line_index,
149 attribute_type,
150 context,
151 attribute,
152 value_from,
153 pricing_status_code
154 )
155 VALUES
156 (
157 0,
158 'PRODUCT',
159 l_attributes_rec.product_attribute_context,
160 l_attributes_rec.product_attribute,
161 Qp_Number.number_to_canonical(TO_NUMBER(l_attributes_rec.product_attr_value)),
162 Qp_Preq_Grp.G_STATUS_UNCHANGED
163 );
164
165
166 --ELSIF l_attributes_rec.pricing_attribute_datatype IN ('X','Y','C') THEN
167 -- bug 2425851
168
169 ELSIF l_attributes_rec.product_attribute_datatype IN ('X','Y','C') THEN
170 --Insert request line attrs with datatype 'X', 'Y', 'C'
171 INSERT INTO qp_preq_line_attrs_formula_tmp
172 (
173 line_index,
174 attribute_type,
175 context,
176 attribute,
177 value_from,
178 pricing_status_code
179 )
180 VALUES
181 (
182 0,
183 'PRODUCT',
184 l_attributes_rec.product_attribute_context,
185 l_attributes_rec.product_attribute,
186 l_attributes_rec.product_attr_value,
187 Qp_Preq_Grp.G_STATUS_UNCHANGED
188 );
189
190 END IF; --If datatype is 'N'
191
192 END IF; --If cur%rowcount = 1
193
194 --If pricing_attribute_context, pricing_attribute and
195 --pricing_attr_value_from are not null, only then insert into plsql
196 --table and temp table.
197 IF l_attributes_rec.pricing_attribute_context IS NOT NULL AND
198 l_attributes_rec.pricing_attribute IS NOT NULL AND
199 l_attributes_rec.pricing_attr_value_from IS NOT NULL
200 THEN
201
202 --Insert the pricing attr info into the temp table since the formula
203 --processing code has been changed(bug 1806928) to look into temp
204 --tables for factor processing due to performance reasons.
205
206 IF l_attributes_rec.pricing_attribute_datatype = 'N' THEN
207
208 --Insert request line attrs with datatype = 'N'
209 INSERT INTO qp_preq_line_attrs_formula_tmp
210 (
211 line_index,
212 attribute_type,
213 context,
214 attribute,
215 value_from,
216 pricing_status_code
217 )
218 VALUES
219 (
220 0,
221 'PRICING',
222 l_attributes_rec.pricing_attribute_context,
223 l_attributes_rec.pricing_attribute,
224 Qp_Number.number_to_canonical(TO_NUMBER(l_attributes_rec.pricing_attr_value_from)),
225 Qp_Preq_Grp.G_STATUS_UNCHANGED
226 );
227
228
229 ELSIF l_attributes_rec.pricing_attribute_datatype IN ('X','Y','C') THEN
230
231 --Insert request line attrs with datatype 'X', 'Y', 'C'
232 INSERT INTO qp_preq_line_attrs_formula_tmp
233 (
234 line_index,
235 attribute_type,
236 context,
237 attribute,
238 value_from,
239 pricing_status_code
240 )
241 VALUES
242 (
243 0,
244 'PRICING',
245 l_attributes_rec.pricing_attribute_context,
246 l_attributes_rec.pricing_attribute,
247 l_attributes_rec.pricing_attr_value_from,
248 Qp_Preq_Grp.G_STATUS_UNCHANGED
249 );
250
251 END IF; --If datatype is 'N'
252
253 END IF; -- If pricing context, attribute and value_from are not null
254
255 END LOOP; /* loop through l_attributes_rec */
256
257
258 --Added 2 parameters p_line_index and p_list_line_type_code and removed
259 --parameter p_req_line_attrs_tmp for Calculate function (POSCO Changes).
260 --Added paramter p_modifier_value (mkarya bug 1906545 for Tropicana).
261
262 l_list_price := Qp_Formula_Price_Calc_Pvt.Calculate(
263 p_price_formula_id => l_formulas_rec.price_formula_id,
264 p_list_price => l_lines_rec.operand,
265 p_price_effective_date => l_sysdate,
266 --p_req_line_attrs_tmp => l_req_line_attrs_tbl,
267 p_line_index => 0,
268 p_list_line_type_code => 'PLL',
269 x_return_status => x_return_status,
270 p_modifier_value => NULL);
271 --dbms_output.put_line('value returned by the formula calculation engine ' || l_list_price);
272
273 --Delete the temp table records inserted above
274 DELETE FROM qp_preq_line_attrs_formula_tmp; -- no need since it is done at the beginning of processing each line
275 --dbms_output.put_line('deleted rows from qp_preq_line_attrs_formula_tmp');
276
277 IF x_return_status <> Fnd_Api.G_RET_STS_SUCCESS THEN
278 l_error_message := Fnd_Message.GET;
279 Fnd_File.put_line(Fnd_File.LOG,l_error_message);
280 END IF;
281 -- Added negative price validation for 2483391
282
283 IF Fnd_Profile.VALUE('QP_NEGATIVE_PRICING') = 'N' AND l_list_price < 0 THEN
284 errbuf := Fnd_Message.GET_STRING('QP','SO_PR_NEGATIVE_LIST_PRICE');
285 RAISE NEGATIVE_VALUE;
286 ELSE
287
288 l_price_rounding := Fnd_Profile.value('QP_PRICE_ROUNDING'); --Added for Enhancement 1732601
289 IF l_price_rounding IS NOT NULL THEN
290
291 BEGIN
292 SELECT rounding_factor
293 INTO l_rounding_factor
294 FROM qp_list_headers_b
295 WHERE list_header_id = l_lines_rec.list_header_id;
296 EXCEPTION
297 WHEN OTHERS THEN
298 l_rounding_factor := -2;
299 END;
300
301 l_list_price := ROUND(l_list_price, -1 * l_rounding_factor);
302
303
304 END IF;
305
306 UPDATE qp_list_lines
307 SET reprice_flag = NULL,
308 request_id = l_conc_request_id,
309 program_application_id = l_conc_program_application_id,
310 program_id = l_conc_program_id,
311 last_update_date = l_sysdate,
312 last_update_login = l_conc_login_id,
313 operand = l_list_price
314 WHERE list_line_id = l_lines_rec.list_line_id;
315 --dbms_output.put_line('updated qp_list_lines ');
316 END IF;
317 -- further fix 4090315 retrieve the next row,
318 -- the %found condition will be checked bfe the loop continues again
319 FETCH qp_list_lines_cursor INTO l_lines_rec;
320 END LOOP; /* loop through lines cur */
321 CLOSE qp_list_lines_cursor; -- further fix 4090315
322
323 END LOOP; /* loop through formulas cur */
324
325 --Change mask back to flexible mask.
326 Qp_Number.canonical_mask :=
327 'FM999999999999999999999.9999999999999999999999999999999999999999';
328
329 COMMIT;
330
331 errbuf := '';
332 retcode := 0;
333
334 EXCEPTION
335 WHEN NEGATIVE_VALUE THEN
336 Fnd_File.put_line(Fnd_File.LOG,errbuf);
337 retcode := 2;
338
339
340 WHEN OTHERS THEN
341 Fnd_File.put_line(Fnd_File.LOG, SUBSTR(SQLERRM, 1, 300));
342 retcode := 2;
343
344 END Update_Formula_Price;
345
346 END Qp_Update_Formulaprice_Pvt;