1 PACKAGE BODY QP_Denormalized_Pricing_Attrs AS
2 /* $Header: QPXDNPAB.pls 120.0.12010000.2 2008/08/19 11:04:55 smbalara 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
219 BEGIN
220 OE_DEBUG_PUB.ADD('Inside QP_Denormalized_Pricing_Attrs :Populate_Factor_List_Attrs');
221 --Order the parameters and get correct values if null
222 IF p_list_header_id_low IS NULL AND p_list_header_id_high IS NULL THEN
223 BEGIN
224 SELECT min(list_header_id), max(list_header_id)
225 INTO l_list_header_id_low, l_list_header_id_high
226 FROM qp_list_headers_b
227 WHERE list_type_code = 'PML';
228 EXCEPTION
229 WHEN OTHERS THEN
230 l_list_header_id_low := 0;
231 l_list_header_id_high := 0;
232 END;
233
234 ELSIF p_list_header_id_low IS NOT NULL AND p_list_header_id_high IS NULL THEN
235 l_list_header_id_low := p_list_header_id_low;
236 l_list_header_id_high := p_list_header_id_low;
237
238 ELSIF p_list_header_id_low IS NULL AND p_list_header_id_high IS NOT NULL THEN
239 l_list_header_id_low := p_list_header_id_high;
240 l_list_header_id_high := p_list_header_id_high;
241
242 ELSE
243 l_list_header_id_low := least(p_list_header_id_low,p_list_header_id_high);
244 l_list_header_id_high := greatest(p_list_header_id_low,p_list_header_id_high
245 );
246 END IF; --If stmt to check values of parameters p_list_header_id_low and high
247
248
249 DELETE FROM qp_factor_list_attrs
250 WHERE list_header_id BETWEEN l_list_header_id_low AND l_list_header_id_high;
251
252
253 INSERT INTO qp_factor_list_attrs
254 (SELECT DISTINCT a.list_header_id,
255 a.pricing_attribute_context, a.pricing_attribute
256 FROM qp_pricing_attributes a, qp_list_headers_b b
257 WHERE a.list_header_id = b.list_header_id
258 AND b.list_type_code = 'PML'
259 AND b.list_header_id BETWEEN
260 l_list_header_id_low AND l_list_header_id_high);
261 EXCEPTION
262 WHEN OTHERS THEN
263 RAISE;
264 END Populate_Factor_List_Attrs;
265
266
267 PROCEDURE Update_Pricing_Attributes(
268 p_list_header_id_low IN NUMBER default null,
269 p_list_header_id_high IN NUMBER default null,
270 p_update_type IN VARCHAR2 default 'BATCH')
271 IS
272
273 l_pricing_attr_id_tbl num_type;
274 l_list_line_id_tbl num_type;
275 l_list_header_id_tbl num_type;
276
277 l_list_header_id_low NUMBER:=0;
278 l_list_header_id_high NUMBER:=0;
279 err_buff varchar2(2000):=' ';
280 retcode number:=0;
281 l_perf varchar2(30);
282 BEGIN
283
284 l_list_header_id_low := p_list_header_id_low;
285 l_list_header_id_high := p_list_header_id_high;
286
287 OE_DEBUG_PUB.ADD('Inside QP_Denormalized_Pricing_Attrs :Update_Pricing_Attributes');
288
289 /*Added for bug# 7143714 for performance contrrol*/
290 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);
291
292 l_perf := nvl(FND_PROFILE.VALUE(g_perf), g_off);
293
294 OE_DEBUG_PUB.ADD('PErformance Control Profile is '||l_perf);
295
296 if (l_perf = g_off) then
297 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);
298 end if;
299 /*End Bug 7309559*/
300
301 --Update the pattern_value_from and pattern_value_to columns with canonical
302 --form of the pricing_attr_value_from_number and to_number columns if
303 --datatype = 'N'and for other datatypes, populate the pricing_attr_value_from
304 --and to in the pattern_value_from and pattern_value_to columns
305
306 IF l_list_header_id_tbl.COUNT > 0 THEN
307 --When pricing attribute datatype is 'N', operator is between and both
308 --pricing_attr_value_from and pricing_attr_value_to are negative.
309 FORALL k IN l_list_header_id_tbl.FIRST..l_list_header_id_tbl.LAST
310 UPDATE qp_pricing_attributes
311 SET pattern_value_from_negative =
312 LEAST(qp_number.number_to_canonical(pricing_attr_value_from_number),
313 qp_number.number_to_canonical(pricing_attr_value_to_number)),
314 pattern_value_to_negative =
315 GREATEST(
316 qp_number.number_to_canonical(pricing_attr_value_from_number),
317 qp_number.number_to_canonical(pricing_attr_value_to_number)),
318 pattern_value_from_positive = null,
319 pattern_value_to_positive = null
320 WHERE comparison_operator_code = 'BETWEEN'
321 AND pricing_attribute_datatype = 'N'
322 AND list_header_id = l_list_header_id_tbl(k)
323 AND pricing_attr_value_from_number < 0
324 AND pricing_attr_value_to_number < 0;
325
326 --When pricing attribute datatype is 'N', operator is between and both
327 --pricing_attr_value_from and pricing_attr_value_to are positive.
328 FORALL k IN l_list_header_id_tbl.FIRST..l_list_header_id_tbl.LAST
329 UPDATE qp_pricing_attributes
330 SET pattern_value_from_positive =
331 LEAST(qp_number.number_to_canonical(pricing_attr_value_from_number),
332 qp_number.number_to_canonical(pricing_attr_value_to_number)),
333 pattern_value_to_positive =
334 GREATEST(
335 qp_number.number_to_canonical(pricing_attr_value_from_number),
336 qp_number.number_to_canonical(pricing_attr_value_to_number)),
337 pattern_value_from_negative = null,
338 pattern_value_to_negative = null
339 WHERE comparison_operator_code = 'BETWEEN'
340 AND pricing_attribute_datatype = 'N'
341 AND list_header_id = l_list_header_id_tbl(k)
342 AND pricing_attr_value_from_number >= 0
343 AND pricing_attr_value_to_number >= 0;
344
345 --When pricing_attr_value_from is negative,pricing_attr_value_to is 0,
346 --operator is between and pricing attribute datatype is 'N'.
347 FORALL k IN l_list_header_id_tbl.FIRST..l_list_header_id_tbl.LAST
348 UPDATE qp_pricing_attributes
349 SET pattern_value_from_negative =
350 '-' || LTRIM(qp_number.number_to_canonical(0)),
351 pattern_value_to_negative =
352 qp_number.number_to_canonical(pricing_attr_value_from_number),
353 pattern_value_from_positive = null,
354 pattern_value_to_positive = null
355 WHERE comparison_operator_code = 'BETWEEN'
356 AND pricing_attribute_datatype = 'N'
357 AND list_header_id = l_list_header_id_tbl(k)
358 AND pricing_attr_value_from_number < 0
359 AND pricing_attr_value_to_number = 0;
360
361 --When pricing_attr_value_from is negative,pricing_attr_value_to is
362 --positive, operator is between and pricing attribute datatype is 'N'.
363 FORALL k IN l_list_header_id_tbl.FIRST..l_list_header_id_tbl.LAST
364 UPDATE qp_pricing_attributes
365 SET pattern_value_from_negative =
366 '-' || LTRIM(qp_number.number_to_canonical(0)),
367 pattern_value_to_negative =
368 qp_number.number_to_canonical(pricing_attr_value_from_number),
369 pattern_value_from_positive = qp_number.number_to_canonical(0),
370 pattern_value_to_positive =
371 qp_number.number_to_canonical(pricing_attr_value_to_number)
372 WHERE comparison_operator_code = 'BETWEEN'
373 AND pricing_attribute_datatype = 'N'
374 AND list_header_id = l_list_header_id_tbl(k)
375 AND pricing_attr_value_from_number < 0
376 AND pricing_attr_value_to_number > 0;
377
378 --When operator is '=' and pricing attribute datatype is 'N' and
379 --pricing_attr_value_from is positive.
380 FORALL k IN l_list_header_id_tbl.FIRST..l_list_header_id_tbl.LAST
381 UPDATE qp_pricing_attributes
382 SET pattern_value_from_positive =
383 qp_number.number_to_canonical(pricing_attr_value_from_number),
384 pattern_value_to_positive =
385 qp_number.number_to_canonical(pricing_attr_value_from_number),
386 pattern_value_from_negative = null,
387 pattern_value_to_negative = null
388 WHERE comparison_operator_code = '='
389 AND pricing_attribute_datatype = 'N'
390 AND list_header_id = l_list_header_id_tbl(k)
391 AND pricing_attr_value_from_number >= 0;
392
393 --When operator is '=' and pricing attribute datatype is 'N' and
394 --pricing_attr_value_from is negative.
395 FORALL k IN l_list_header_id_tbl.FIRST..l_list_header_id_tbl.LAST
396 UPDATE qp_pricing_attributes
397 SET pattern_value_from_negative =
398 qp_number.number_to_canonical(pricing_attr_value_from_number),
399 pattern_value_to_negative =
400 qp_number.number_to_canonical(pricing_attr_value_from_number),
401 pattern_value_from_positive = null,
402 pattern_value_to_positive = null
403 WHERE comparison_operator_code = '='
404 AND pricing_attribute_datatype = 'N'
405 AND list_header_id = l_list_header_id_tbl(k)
406 AND pricing_attr_value_from_number < 0;
407
408 --When pricing attribute datatype is 'X', 'Y' or 'C' and operator is between
409 FORALL k IN l_list_header_id_tbl.FIRST..l_list_header_id_tbl.LAST
410 UPDATE qp_pricing_attributes
411 SET pattern_value_from_positive =
412 LEAST(pricing_attr_value_from, pricing_attr_value_to),
413 pattern_value_to_positive =
414 GREATEST(pricing_attr_value_from, pricing_attr_value_to),
415 pattern_value_from_negative = null,
416 pattern_value_to_negative = null
417 WHERE comparison_operator_code = 'BETWEEN'
418 AND pricing_attribute_datatype IN ('X','Y','C')
419 AND list_header_id = l_list_header_id_tbl(k);
420
421 --When pricing attribute datatype is 'X', 'Y' or 'C' and operator is '='.
422 FORALL k IN l_list_header_id_tbl.FIRST..l_list_header_id_tbl.LAST
423 UPDATE qp_pricing_attributes
424 SET pattern_value_from_positive = pricing_attr_value_from,
425 pattern_value_to_positive = pricing_attr_value_from,
426 pattern_value_from_negative = null,
427 pattern_value_to_negative = null
428 WHERE comparison_operator_code = '='
429 AND pricing_attribute_datatype IN ('X','Y','C')
430 AND list_header_id = l_list_header_id_tbl(k);
431
432 END IF; --If l_list_header_id_tbl.COUNT > 0
433
434 IF p_update_type IN ('ALL','FACTOR','BATCH') THEN
435 commit;
436 END IF;
437
438 l_list_header_id_tbl.DELETE; --Clear the plsql table.
439
440 --Set the format mask for the canonical form of numbers
441 qp_number.canonical_mask := 'FM999999999999999999999.9999999999999999999999999999999999999999';
442
443 --dbms_output.put_line('Updated Search_Ind columns');
444 --dbms_output.put_line('Updated Pattern_value_from/Pattern_value_to columns');
445
446 EXCEPTION
447 WHEN OTHERS THEN
448 --dbms_output.put_line('ERR:'||substr(sqlerrm, 1, 240));
449 RAISE;
450
451 END Update_Pricing_Attributes;
452
453 END QP_Denormalized_Pricing_Attrs;
[Home] [Help]