1 PACKAGE BODY QP_Denormalized_Pricing_Attrs AS
2 /* $Header: QPXDNPAB.pls 120.2.12020000.2 2012/12/13 06:51:39 kdurgasi ship $ */
3
4 --Procedure to update the distinct row-count of pricing attributes per
5 --list_header_id
6
7 PROCEDURE Update_Row_Count(p_list_header_id_low IN NUMBER,
8 p_list_header_id_high IN NUMBER);
9 /* Added for bug 7309559 */
10 PROCEDURE PREPARE_INPUT_DATA(
11 err_buff out nocopy varchar2,
12 retcode out nocopy number,
13 p_list_header_id_low in out nocopy number,
14 p_list_header_id_high in out nocopy number,
15 p_list_header_id_tbl in out nocopy num_type,
16 p_update_type in varchar2
17 ) is
18 l_list_header_id_tbl num_type;
19 l_list_header_id_low NUMBER;
20 l_list_header_id_high NUMBER;
21 BEGIN
22 OE_DEBUG_PUB.ADD('Inside QP_Denormalized_Pricing_Attrs :PREPARE_INPUT_DATA');
23 --Order the parameters and get correct values if null
24 IF p_list_header_id_low IS NULL AND p_list_header_id_high IS NULL THEN
25 BEGIN
26 SELECT min(list_header_id), max(list_header_id)
27 INTO l_list_header_id_low, l_list_header_id_high
28 FROM qp_list_headers_b
29 WHERE list_type_code = 'PML';
30
31 EXCEPTION
32 WHEN OTHERS THEN
33 l_list_header_id_low := 0;
34 l_list_header_id_high := 0;
35 END;
36
37 ELSIF p_list_header_id_low IS NOT NULL AND p_list_header_id_high IS NULL THEN
38 l_list_header_id_low := p_list_header_id_low;
39 l_list_header_id_high := p_list_header_id_low;
40
41 ELSIF p_list_header_id_low IS NULL AND p_list_header_id_high IS NOT NULL THEN
42 l_list_header_id_low := p_list_header_id_high;
43 l_list_header_id_high := p_list_header_id_high;
44
45 ELSE
46 l_list_header_id_low := least(p_list_header_id_low,p_list_header_id_high);
47 l_list_header_id_high := greatest(p_list_header_id_low,p_list_header_id_high);
48 END IF; --If stmt to check values of parameters p_list_header_id_low and high
49
50 --Bulk Collect the Factor List Header Ids into l_list_header_id_tbl
51 SELECT list_header_id
52 BULK COLLECT INTO l_list_header_id_tbl
53 FROM qp_list_headers_b
54 WHERE list_type_code = 'PML'
55 AND list_header_id BETWEEN l_list_header_id_low AND l_list_header_id_high;
56
57 p_list_header_id_tbl := l_list_header_id_tbl;
58 p_list_header_id_low := l_list_header_id_low;
59 p_list_header_id_high := l_list_header_id_high;
60
61 EXCEPTION
62 WHEN OTHERS THEN
63 err_buff := sqlerrm;
64 retcode := 2;
65 END PREPARE_INPUT_DATA;
66
67 PROCEDURE UPDATE_SEARCH_IND(
68 err_buff out nocopy varchar2,
69 retcode out nocopy number,
70 p_list_header_id_low in number,
71 p_list_header_id_high in number,
72 p_list_header_id_tbl in num_type,
73 p_update_type in varchar2 default 'BATCH'
74 ) IS
75 l_pricing_attr_id_tbl num_type;
76 l_list_line_id_tbl num_type;
77 l_list_header_id_tbl num_type;
78 l_list_header_id_low NUMBER;
79 l_list_header_id_high NUMBER;
80 BEGIN
81
82 l_list_header_id_tbl := p_list_header_id_tbl;
83 l_list_header_id_low := p_list_header_id_low;
84 l_list_header_id_high := p_list_header_id_high;
85
86 OE_DEBUG_PUB.ADD('Inside QP_Denormalized_Pricing_Attrs :UPDATE_SEARCH_IND');
87 --Update Distinct_Row_Count for factor pricing attributes
88 QP_Denormalized_Pricing_Attrs.Update_Row_Count(l_list_header_id_low, l_list_header_id_high);
89
90 --Set the format mask for the canonical form of numbers
91 --fnd_number.canonical_mask := '00999999999999999999999.99999999999999999999999999999999999999';
92
93 IF l_list_header_id_tbl.COUNT > 0 THEN
94 --Reset the search_ind value for the factor qp_pricing_attributes initially.
95 FORALL k IN l_list_header_id_tbl.FIRST..l_list_header_id_tbl.LAST
96 UPDATE qp_pricing_attributes
97 SET search_ind = null
98 WHERE list_header_id = l_list_header_id_tbl(k);
99 END IF; --If l_list_header_id_tbl.COUNT > 0
100
101 --Select those rows from qp_pricing_attributes where the distinct_row_count
102 --is the lowest value among the pricing attributes for a given list_line_id.
103 --If multiple such pricing_attributes exist, pick any one.
104 SELECT min(a.pricing_attribute_id), a.list_line_id
105 BULK COLLECT INTO l_pricing_attr_id_tbl, l_list_line_id_tbl
106 FROM qp_pricing_attributes a
107 WHERE a.distinct_row_count = (SELECT min(b.distinct_row_count)
108 FROM qp_pricing_attributes b
109 WHERE b.list_line_id = a.list_line_id)
110 AND a.list_header_id IN (SELECT list_header_id
111 FROM qp_list_headers_b
112 WHERE list_type_code = 'PML'
113 AND list_header_id BETWEEN l_list_header_id_low
114 AND l_list_header_id_high)
115 GROUP BY a.list_line_id;
116
117 IF l_pricing_attr_id_tbl.COUNT > 0 THEN
118 --For rows selected above update the search_ind to 1.
119 FORALL i IN l_pricing_attr_id_tbl.FIRST..l_pricing_attr_id_tbl.LAST
120 UPDATE qp_pricing_attributes
121 SET search_ind = 1
122 WHERE pricing_attribute_id = l_pricing_attr_id_tbl(i);
123 END IF; --If l_pricing_attr_id_tbl.COUNT > 0
124
125 l_pricing_attr_id_tbl.DELETE; --Clear the plsql table.
126
127
128 IF l_list_line_id_tbl.COUNT > 0 THEN
129 --Update the search_ind to 2 for the remaining rows with 'BETWEEN' operator
130 FORALL j IN l_list_line_id_tbl.FIRST..l_list_line_id_tbl.LAST
131 UPDATE qp_pricing_attributes
132 SET search_ind = 2
133 WHERE (search_ind <> 1 or search_ind IS NULL)
134 AND list_line_id = l_list_line_id_tbl(j);
135 END IF; --If l_list_line_id_tbl.COUNT > 0
136
137 l_list_line_id_tbl.DELETE; --Clear the plsql table.
138
139
140 IF l_list_header_id_tbl.COUNT > 0 THEN
141 --Update the group_count column of qp_list_lines with the count of
142 --pricing attributes which have search_ind = 2 for each list_line_id
143 FORALL k IN l_list_header_id_tbl.FIRST..l_list_header_id_tbl.LAST
144 UPDATE qp_list_lines l
145 SET l.group_count = (select count(*)
146 from qp_pricing_attributes a
147 where a.list_line_id = l.list_line_id
148 and a.search_ind = 2)
149 WHERE l.list_header_id = l_list_header_id_tbl(k);
150 END IF; --If l_list_header_id_tbl.COUNT > 0
151
152 IF p_update_type IN ('ALL','FACTOR','BATCH') THEN
153 commit;
154 END IF;
155
156 EXCEPTION
157 WHEN OTHERS THEN
158 err_buff := sqlerrm;
159 retcode := 2;
160
161 END UPDATE_SEARCH_IND;
162
163 PROCEDURE UPDATE_SEARCH_IND(
164 err_buff out nocopy varchar2,
165 retcode out nocopy number,
166 p_list_header_id_low in number,
167 p_list_header_id_high in number,
168 p_update_type in varchar2
169 ) IS
170 l_list_header_id_tbl num_type;
171 l_list_header_id_low NUMBER;
172 l_list_header_id_high NUMBER;
173 BEGIN
174 l_list_header_id_low := p_list_header_id_low;
175 l_list_header_id_high := p_list_header_id_high;
176 OE_DEBUG_PUB.ADD('Inside QP_Denormalized_Pricing_Attrs :UPDATE_SEARCH_IND -called from Concurrent Program');
177 QP_Denormalized_Pricing_Attrs.PREPARE_INPUT_DATA(err_buff,retcode,l_list_header_id_low,l_list_header_id_high,l_list_header_id_tbl,p_update_type);
178
179 QP_Denormalized_Pricing_Attrs.UPDATE_SEARCH_IND(err_buff,retcode,l_list_header_id_low,l_list_header_id_high,l_list_header_id_tbl,p_update_type);
180
181 END UPDATE_SEARCH_IND;
182 /* End Bug -7309559 */
183
184 PROCEDURE Update_Row_Count(p_list_header_id_low IN NUMBER,
185 p_list_header_id_high IN NUMBER)
186 IS
187 BEGIN
188 OE_DEBUG_PUB.ADD('Inside QP_Denormalized_Pricing_Attrs :Update_Row_Count');
189 --Do this only for factor list attributes
190 -- Added the hint inside the subquery for bug#3993301
191 UPDATE qp_pricing_attributes a
192 SET a.distinct_row_count =
193 (SELECT /*+ INDEX(aa QP_PRICING_ATTRIBUTES_N6) */ count(*)
194 FROM qp_pricing_attributes aa
195 WHERE aa.pricing_attribute_context = a.pricing_attribute_context
196 AND aa.pricing_attribute = a.pricing_attribute
197 AND aa.pricing_attr_value_from = a.pricing_attr_value_from
198 AND nvl(aa.pricing_attr_value_to,'-x') =
199 nvl(a.pricing_attr_value_to,'-x')
200 AND aa.comparison_operator_code = a.comparison_operator_code
201 AND aa.list_header_id = a.list_header_id
202 AND aa.pricing_attribute_context IS NOT NULL)
203 WHERE a.list_header_id IN (SELECT list_header_id
204 FROM qp_list_headers_b
205 WHERE list_type_code = 'PML'
206 AND list_header_id BETWEEN p_list_header_id_low
207 AND p_list_header_id_high);
208
209 END Update_Row_Count;
210
211
212 PROCEDURE Populate_Factor_List_Attrs(
213 p_list_header_id_low IN NUMBER default null,
214 p_list_header_id_high IN NUMBER default null)
215 IS
216 l_list_header_id_low NUMBER;
217 l_list_header_id_high NUMBER;
218 l_perf varchar2(30);
219 BEGIN
220 l_perf := nvl(FND_PROFILE.VALUE(g_perf), g_off);
221 if (l_perf = g_off) then
222 OE_DEBUG_PUB.ADD('Inside QP_Denormalized_Pricing_Attrs :Populate_Factor_List_Attrs');
223 --Order the parameters and get correct values if null
224 IF p_list_header_id_low IS NULL AND p_list_header_id_high IS NULL THEN
225 BEGIN
226 SELECT min(list_header_id), max(list_header_id)
227 INTO l_list_header_id_low, l_list_header_id_high
228 FROM qp_list_headers_b
229 WHERE list_type_code = 'PML';
230 EXCEPTION
231 WHEN OTHERS THEN
232 l_list_header_id_low := 0;
233 l_list_header_id_high := 0;
234 END;
235
236 ELSIF p_list_header_id_low IS NOT NULL AND p_list_header_id_high IS NULL THEN
237 l_list_header_id_low := p_list_header_id_low;
238 l_list_header_id_high := p_list_header_id_low;
239
240 ELSIF p_list_header_id_low IS NULL AND p_list_header_id_high IS NOT NULL THEN
241 l_list_header_id_low := p_list_header_id_high;
242 l_list_header_id_high := p_list_header_id_high;
243
244 ELSE
245 l_list_header_id_low := least(p_list_header_id_low,p_list_header_id_high);
246 l_list_header_id_high := greatest(p_list_header_id_low,p_list_header_id_high
247 );
248 END IF; --If stmt to check values of parameters p_list_header_id_low and high
249
250
251 DELETE FROM qp_factor_list_attrs
252 WHERE list_header_id BETWEEN l_list_header_id_low AND l_list_header_id_high;
253
254
255 INSERT INTO qp_factor_list_attrs
256 (SELECT DISTINCT a.list_header_id,
257 a.pricing_attribute_context, a.pricing_attribute
258 FROM qp_pricing_attributes a, qp_list_headers_b b
259 WHERE a.list_header_id = b.list_header_id
260 AND b.list_type_code = 'PML'
261 AND b.list_header_id BETWEEN
262 l_list_header_id_low AND l_list_header_id_high);
263 end if;
264 EXCEPTION
265 WHEN OTHERS THEN
266 RAISE;
267 END Populate_Factor_List_Attrs;
268
269
270 PROCEDURE Update_Pricing_Attributes(
271 p_list_header_id_low IN NUMBER default null,
272 p_list_header_id_high IN NUMBER default null,
273 p_update_type IN VARCHAR2 default 'BATCH')
274 IS
275
276 l_pricing_attr_id_tbl num_type;
277 l_list_line_id_tbl num_type;
278 l_list_header_id_tbl num_type;
279
280 l_list_header_id_low NUMBER:=0;
281 l_list_header_id_high NUMBER:=0;
282 err_buff varchar2(2000):=' ';
283 retcode number:=0;
284 l_perf varchar2(30);
285 BEGIN
286
287 l_list_header_id_low := p_list_header_id_low;
288 l_list_header_id_high := p_list_header_id_high;
289
290 OE_DEBUG_PUB.ADD('Inside QP_Denormalized_Pricing_Attrs :Update_Pricing_Attributes');
291
292 /*Added for bug# 7143714 for performance contrrol*/
293 QP_Denormalized_Pricing_Attrs.PREPARE_INPUT_DATA(err_buff,retcode,l_list_header_id_low,l_list_header_id_high,l_list_header_id_tbl,p_update_type);
294
295 l_perf := nvl(FND_PROFILE.VALUE(g_perf), g_off);
296
297 OE_DEBUG_PUB.ADD('PErformance Control Profile is '||l_perf);
298
299 if (l_perf = g_off) then
300 QP_Denormalized_Pricing_Attrs.UPDATE_SEARCH_IND(err_buff,retcode,l_list_header_id_low,l_list_header_id_high,l_list_header_id_tbl,p_update_type);
301 --end if;
302 /*End Bug 7309559*/
303
304 --Update the pattern_value_from and pattern_value_to columns with canonical
305 --form of the pricing_attr_value_from_number and to_number columns if
306 --datatype = 'N'and for other datatypes, populate the pricing_attr_value_from
307 --and to in the pattern_value_from and pattern_value_to columns
308
309 --Set the format mask for the canonical form of numbers --bug 7696883
310 qp_number.canonical_mask := '00999999999999999999999.99999999999999999999999999999999999999';
311
312 IF l_list_header_id_tbl.COUNT > 0 THEN
313 --When pricing attribute datatype is 'N', operator is between and both
314 --pricing_attr_value_from and pricing_attr_value_to are negative.
315 FORALL k IN l_list_header_id_tbl.FIRST..l_list_header_id_tbl.LAST
316 UPDATE qp_pricing_attributes
317 SET pattern_value_from_negative =
318 LEAST(qp_number.number_to_canonical(pricing_attr_value_from_number),
319 qp_number.number_to_canonical(pricing_attr_value_to_number)),
320 pattern_value_to_negative =
321 GREATEST(
322 qp_number.number_to_canonical(pricing_attr_value_from_number),
323 qp_number.number_to_canonical(pricing_attr_value_to_number)),
324 pattern_value_from_positive = null,
325 pattern_value_to_positive = null
326 WHERE comparison_operator_code = 'BETWEEN'
327 AND pricing_attribute_datatype = 'N'
328 AND list_header_id = l_list_header_id_tbl(k)
329 AND pricing_attr_value_from_number < 0
330 AND pricing_attr_value_to_number < 0;
331
332 --When pricing attribute datatype is 'N', operator is between and both
333 --pricing_attr_value_from and pricing_attr_value_to are positive.
334 FORALL k IN l_list_header_id_tbl.FIRST..l_list_header_id_tbl.LAST
335 UPDATE qp_pricing_attributes
336 SET pattern_value_from_positive =
337 LEAST(qp_number.number_to_canonical(pricing_attr_value_from_number),
338 qp_number.number_to_canonical(pricing_attr_value_to_number)),
339 pattern_value_to_positive =
340 GREATEST(
341 qp_number.number_to_canonical(pricing_attr_value_from_number),
342 qp_number.number_to_canonical(pricing_attr_value_to_number)),
343 pattern_value_from_negative = null,
344 pattern_value_to_negative = null
345 WHERE comparison_operator_code = 'BETWEEN'
346 AND pricing_attribute_datatype = 'N'
347 AND list_header_id = l_list_header_id_tbl(k)
348 AND pricing_attr_value_from_number >= 0
349 AND pricing_attr_value_to_number >= 0;
350
351 --When pricing_attr_value_from is negative,pricing_attr_value_to is 0,
352 --operator is between and pricing attribute datatype is 'N'.
353 FORALL k IN l_list_header_id_tbl.FIRST..l_list_header_id_tbl.LAST
354 UPDATE qp_pricing_attributes
355 SET pattern_value_from_negative =
356 '-' || LTRIM(qp_number.number_to_canonical(0)),
357 pattern_value_to_negative =
358 qp_number.number_to_canonical(pricing_attr_value_from_number),
359 pattern_value_from_positive = null,
360 pattern_value_to_positive = null
361 WHERE comparison_operator_code = 'BETWEEN'
365 AND pricing_attr_value_to_number = 0;
362 AND pricing_attribute_datatype = 'N'
363 AND list_header_id = l_list_header_id_tbl(k)
364 AND pricing_attr_value_from_number < 0
366
367 --When pricing_attr_value_from is negative,pricing_attr_value_to is
368 --positive, operator is between and pricing attribute datatype is 'N'.
369 FORALL k IN l_list_header_id_tbl.FIRST..l_list_header_id_tbl.LAST
370 UPDATE qp_pricing_attributes
371 SET pattern_value_from_negative =
372 '-' || LTRIM(qp_number.number_to_canonical(0)),
373 pattern_value_to_negative =
374 qp_number.number_to_canonical(pricing_attr_value_from_number),
375 pattern_value_from_positive = qp_number.number_to_canonical(0),
376 pattern_value_to_positive =
377 qp_number.number_to_canonical(pricing_attr_value_to_number)
378 WHERE comparison_operator_code = 'BETWEEN'
379 AND pricing_attribute_datatype = 'N'
380 AND list_header_id = l_list_header_id_tbl(k)
381 AND pricing_attr_value_from_number < 0
382 AND pricing_attr_value_to_number > 0;
383
384 --When operator is '=' and pricing attribute datatype is 'N' and
385 --pricing_attr_value_from is positive.
386 FORALL k IN l_list_header_id_tbl.FIRST..l_list_header_id_tbl.LAST
387 UPDATE qp_pricing_attributes
388 SET pattern_value_from_positive =
389 qp_number.number_to_canonical(pricing_attr_value_from_number),
390 pattern_value_to_positive =
391 qp_number.number_to_canonical(pricing_attr_value_from_number),
392 pattern_value_from_negative = null,
393 pattern_value_to_negative = null
394 WHERE comparison_operator_code = '='
395 AND pricing_attribute_datatype = 'N'
396 AND list_header_id = l_list_header_id_tbl(k)
397 AND pricing_attr_value_from_number >= 0;
398
399 --When operator is '=' and pricing attribute datatype is 'N' and
400 --pricing_attr_value_from is negative.
401 FORALL k IN l_list_header_id_tbl.FIRST..l_list_header_id_tbl.LAST
402 UPDATE qp_pricing_attributes
403 SET pattern_value_from_negative =
404 qp_number.number_to_canonical(pricing_attr_value_from_number),
405 pattern_value_to_negative =
406 qp_number.number_to_canonical(pricing_attr_value_from_number),
407 pattern_value_from_positive = null,
408 pattern_value_to_positive = null
409 WHERE comparison_operator_code = '='
410 AND pricing_attribute_datatype = 'N'
411 AND list_header_id = l_list_header_id_tbl(k)
412 AND pricing_attr_value_from_number < 0;
413
414 --When pricing attribute datatype is 'X', 'Y' or 'C' and operator is between
415 FORALL k IN l_list_header_id_tbl.FIRST..l_list_header_id_tbl.LAST
416 UPDATE qp_pricing_attributes
417 SET pattern_value_from_positive =
418 LEAST(pricing_attr_value_from, pricing_attr_value_to),
419 pattern_value_to_positive =
420 GREATEST(pricing_attr_value_from, pricing_attr_value_to),
421 pattern_value_from_negative = null,
422 pattern_value_to_negative = null
423 WHERE comparison_operator_code = 'BETWEEN'
424 AND pricing_attribute_datatype IN ('X','Y','C')
425 AND list_header_id = l_list_header_id_tbl(k);
426
427 --When pricing attribute datatype is 'X', 'Y' or 'C' and operator is '='.
428 FORALL k IN l_list_header_id_tbl.FIRST..l_list_header_id_tbl.LAST
429 UPDATE qp_pricing_attributes
430 SET pattern_value_from_positive = pricing_attr_value_from,
431 pattern_value_to_positive = pricing_attr_value_from,
432 pattern_value_from_negative = null,
433 pattern_value_to_negative = null
434 WHERE comparison_operator_code = '='
435 AND pricing_attribute_datatype IN ('X','Y','C')
436 AND list_header_id = l_list_header_id_tbl(k);
437
438 END IF; --If l_list_header_id_tbl.COUNT > 0
439 end if;
440 IF p_update_type IN ('ALL','FACTOR','BATCH') THEN
441 commit;
442 END IF;
443
444 l_list_header_id_tbl.DELETE; --Clear the plsql table.
445
446 --Set the format mask for the canonical form of numbers
447 qp_number.canonical_mask := 'FM999999999999999999999.9999999999999999999999999999999999999999';
448
449 --dbms_output.put_line('Updated Search_Ind columns');
450 --dbms_output.put_line('Updated Pattern_value_from/Pattern_value_to columns');
451
452 EXCEPTION
453 WHEN OTHERS THEN
454 --dbms_output.put_line('ERR:'||substr(sqlerrm, 1, 240));
455 RAISE;
456
457 END Update_Pricing_Attributes;
458
459 END QP_Denormalized_Pricing_Attrs;
[Home] [Help]