1 PACKAGE BODY QP_Maintain_Denormalized_Data AS
2 /*$Header: QPXDENOB.pls 120.15 2010/06/15 10:46:54 hmohamme ship $ */
3
4 --added for bug 5237249
5 G_LIST_HEADER_ID NUMBER;
6 G_LIST_HEADER_ID_HIGH NUMBER;
7 G_UPDATE_TYPE VARCHAR2(50);
8
9 /**********************added these debug procedures as a part of bug fix for 2181164**********************/
10
11 ----procedure to write the log messages
12 Procedure put_line(p_mesg_text IN varchar2)
13 IS
14 BEGIN
15 IF nvl(fnd_profile.value('CONC_REQUEST_ID'),0) <> 0 THEN
16 fnd_file.put_line(FND_FILE.LOG,p_mesg_text);
17 END IF;
18 END put_line;
19
20 /**************************end of changes bug fix 2181164****************************************************/
21
22
23 --hvop
24 Procedure Set_HVOP_Pricing (x_return_status OUT NOCOPY VARCHAR2,
25 x_return_status_text OUT NOCOPY VARCHAR2)
26 Is
27
28 Cursor l_basic_modifiers_cur Is
29 SELECT 'N' FROM dual WHERE
30 EXISTS(
31 SELECT 'Y'
32 FROM qp_list_headers_b qh,
33 qp_list_lines ql
34 WHERE qh.list_type_code = 'PRO'
35 and qh.active_flag = 'Y'
36 and ql.list_header_id = qh.list_header_id
37 and ql.list_line_type_code in ('PRG','IUE','TSN','CIE')
38 and rownum = 1
39 );
40
41 Cursor l_limits_cur IS
42 SELECT 'N'
43 FROM qp_list_headers_b qh
44 WHERE qh.active_flag = 'Y'
45 and qh.list_type_code in ('PRO','DLT','SLT','DEL','CHARGES')
46 and exists (select 'Y'
47 from qp_limits qlim
48 where qlim.list_header_id = qh.list_header_id)
49 and rownum = 1;
50 l_HVOP_Possible VARCHAR2(1) := 'Y';
51 dummy BOOLEAN;
52
53 BEGIN
54
55
56 If QP_Code_Control.Get_Code_Release_Level > '110509'
57 Then
58 If QP_Java_Engine_Util_PUB.Java_Engine_Running = 'Y'
59 Then
60 Open l_basic_modifiers_cur;
61 Fetch l_basic_modifiers_cur Into l_HVOP_Possible;
62 Close l_basic_modifiers_cur;
63
64 If l_HVOP_Possible = 'Y' Then
65 Open l_limits_cur;
66 Fetch l_limits_cur Into l_HVOP_Possible;
67 Close l_limits_cur;
68 End If;
69 Else
70 l_HVOP_Possible := 'N';
71 End If;
72
73 dummy := fnd_profile.save ( x_name => 'QP_HVOP_PRICING_SETUP',X_VALUE => l_HVOP_Possible, X_LEVEL_NAME => 'SITE');
74
75 End If; --Code Control
76
77 -- commit; --Commented because of Bug #3548384
78
79 EXCEPTION
80 When OTHERS Then
81 x_return_status := FND_API.G_RET_STS_ERROR;
82 x_return_status_text := 'Exception in Set_HVOP_Pricing: '||SQLERRM;
83
84 END Set_HVOP_Pricing;
85 --hvop
86
87 procedure update_adv_mod_products(x_return_status OUT NOCOPY VARCHAR2,
88 x_return_status_text OUT NOCOPY VARCHAR2) IS
89
90 BEGIN
91 --this procedure is called to populate the product dependencies for
92 --line group based and OID/PRG based discounts to identify which lines
93 --need to be passed to the pricing engine
94
95 --this same operation is done in the delayed request API for delayed requests
96 --done from the forms. In case any bug fixes are done to this, the same
97 --needs to be propagated to QPXUREQB.pls procedures
98 --update_changed_lines_add/del/act/ph
99
100 if G_UPDATE_TYPE <> 'BATCH_ADV_MOD_PRODUCTS' then
101 --added for bug 5237249
102 --delete should not happen for parallel threads from qpxsourc.sql
103 --otherwise it will delete the previous worker's rows
104 --moved the delete to qpxsourc.sql
105 delete from qp_adv_mod_products;
106 end if;
107
108 insert into qp_adv_mod_products
109 (pricing_phase_id, product_attribute, product_attr_value)
110 (select /*+ ORDERED USE_NL(qlh) */ ql.pricing_phase_id, 'PRICING_ATTRIBUTE3', 'ALL_ITEMS'
111 from qp_list_lines ql, qp_list_headers_b qlh
112 where ql.qualification_ind = 0
113 and ql.pricing_phase_id <> 1
114 and ql.modifier_level_code = 'LINEGROUP'
115 --added for bug 5237249
116 and ((G_LIST_HEADER_ID IS NOT null
117 and G_LIST_HEADER_ID_HIGH IS NOT null
118 and qlh.list_header_id between G_LIST_HEADER_ID and G_LIST_HEADER_ID_HIGH)
119 or (G_LIST_HEADER_ID IS NULL)
120 or (G_LIST_HEADER_ID_HIGH IS NULL))
121 and not exists (select 'Y' from qp_pricing_attributes qpa
122 where qpa.list_line_id = ql.list_line_id)
123 and qlh.list_header_id = ql.list_header_id
124 and qlh.active_flag = 'Y'
125 and rownum =1);
126
127 --Removed hints from the sql to make it more cost effective.
128 insert into qp_adv_mod_products
129 (pricing_phase_id, product_attribute, product_attr_value)
130 (select
131 distinct ql.pricing_phase_id, qpa.product_attribute, qpa.product_attr_value
132 from qp_rltd_modifiers rltd, qp_list_lines ql, qp_list_headers_b qlh
133 ,qp_pricing_attributes qpa
134 where rltd.rltd_modifier_grp_type = 'BENEFIT'
135 and ql.list_line_id = rltd.to_rltd_modifier_id
136 --and ql.list_line_type_code = 'DIS'
137 and qlh.list_header_id = ql.list_header_id
138 --added for bug 5237249
139 and ((G_LIST_HEADER_ID IS NOT null
140 and G_LIST_HEADER_ID_HIGH IS NOT null
141 and qlh.list_header_id between G_LIST_HEADER_ID and G_LIST_HEADER_ID_HIGH)
142 or (G_LIST_HEADER_ID IS NULL)
143 or (G_LIST_HEADER_ID_HIGH IS NULL))
144 and qlh.active_flag = 'Y'
145 --and qlh.list_type_code in ( 'DEL', 'PRO')
146 and qpa.list_line_id = ql.list_line_id
147 and not exists (select 'Y' from qp_adv_mod_products item
148 where item.pricing_phase_id = qpa.pricing_phase_id
149 and item.product_attribute = qpa.product_attribute
150 and item.product_attr_value = qpa.product_attr_value)
151 UNION
152 select
153 distinct ql.pricing_phase_id, qpa.product_attribute, qpa.product_attr_value
154 from qp_list_lines ql
155 , qp_list_headers_b qlh
156 , qp_pricing_attributes qpa
157 where ql.pricing_phase_id > 1
158 and ql.qualification_ind > 0
159 and ql.list_line_type_code in ('OID', 'PRG', 'RLTD')
160 and qpa.list_line_id = ql.list_line_id
161 and qlh.list_header_id = ql.list_header_id
162 and qlh.active_flag = 'Y'
163 --added for bug 5237249
164 and ((G_LIST_HEADER_ID IS NOT null
165 and G_LIST_HEADER_ID_HIGH IS NOT null
166 and qlh.list_header_id between G_LIST_HEADER_ID and G_LIST_HEADER_ID_HIGH)
167 or (G_LIST_HEADER_ID IS NULL)
168 or (G_LIST_HEADER_ID_HIGH IS NULL))
169 and qlh.list_type_code in ('DLT', 'SLT', 'DEL', 'PRO', 'CHARGES')
170 and not exists (select 'Y' from qp_adv_mod_products item
171 where item.pricing_phase_id = qpa.pricing_phase_id
172 and item.product_attribute = qpa.product_attribute
173 and item.product_attr_value = qpa.product_attr_value)
174 UNION
175 select
176 distinct ql.pricing_phase_id, qpa.product_attribute, qpa.product_attr_value
177 from qp_list_lines ql
178 , qp_list_headers_b qlh
179 , qp_pricing_attributes qpa
180 where ql.modifier_level_code = 'LINEGROUP'
181 and ql.pricing_phase_id > 1
182 and qpa.list_line_id = ql.list_line_id
183 and qlh.list_header_id = ql.list_header_id
184 and qlh.active_flag = 'Y'
185 --added for bug 5237249
186 and ((G_LIST_HEADER_ID IS NOT null
187 and G_LIST_HEADER_ID_HIGH IS NOT null
188 and qlh.list_header_id between G_LIST_HEADER_ID and G_LIST_HEADER_ID_HIGH)
189 or (G_LIST_HEADER_ID IS NULL)
190 or (G_LIST_HEADER_ID_HIGH IS NULL))
191 and qlh.list_type_code in ('DLT', 'SLT', 'DEL', 'PRO', 'CHARGES')
192 and not exists (select 'Y' from qp_adv_mod_products item
193 where item.pricing_phase_id = qpa.pricing_phase_id
194 and item.product_attribute = qpa.product_attribute
195 and item.product_attr_value = qpa.product_attr_value));
196
197 commit;
198 EXCEPTION
199 When OTHERS Then
200 x_return_status := FND_API.G_RET_STS_ERROR;
201 x_return_status_text := 'update_adv_mod_products exception '||SQLERRM;
202 END update_adv_mod_products;
203
204 procedure update_pricing_phases(p_update_type IN VARCHAR2
205 , p_pricing_phase_id IN NUMBER
206 , p_automatic_flag IN VARCHAR2
207 , p_count NUMBER
208 , p_call_from NUMBER
209 , x_return_status OUT NOCOPY VARCHAR2
210 , x_return_status_text OUT NOCOPY VARCHAR2) IS
211
212 /* Changed the cursor below for the bug#2572053 */
213 CURSOR l_basic_modifiers_cur IS
214 SELECT 'Y' FROM DUAL WHERE
215 EXISTS(
216 SELECT 'Y'
217 FROM qp_list_headers_b qh,
218 qp_list_lines ql
219 WHERE qh.list_type_code = 'PRO'
220 and qh.active_flag = 'Y'
221 and ql.list_header_id = qh.list_header_id
222 and ql.list_line_type_code in ('OID','PRG','IUE','TSN','CIE')
223 and rownum = 1
224 );
225
226 CURSOR l_limits_exist_cur IS
227 SELECT 'Y'
228 FROM qp_list_headers_b qh
229 WHERE qh.active_flag = 'Y'
230 and qh.list_type_code in ('PRO','DLT','SLT','DEL','CHARGES')
231 and exists (select 'Y'
232 from qp_limits qlim
233 where qlim.list_header_id = qh.list_header_id)
234 and rownum = 1;
235
236 -- Essilor Fix bug 2789138
237 -- Commented for bug#2894244
238 CURSOR l_phase_cur(p_phase_id NUMBER) IS
239 select pricing_phase_id
240 from qp_pricing_phases
241 where pricing_phase_id = nvl(p_phase_id, pricing_phase_id);
242
243 -- Added the following variables for bug#2572053
244 --l_rltd_exists VARCHAR2(1) := 'N';
245 --l_oid_exists VARCHAR2(1) := 'N';
246 --l_line_group_exists VARCHAR2(1) := 'N';
247 --l_freight_exists VARCHAR2(1) := 'N';
248
249 -- Essilor Fix bug 2789138
250 -- Commented for bug#2894244
251 l_automatic_exists VARCHAR2(1) := 'N';
252 l_manual_exists VARCHAR2(1) := 'N';
253 l_manual_modifier_flag VARCHAR2(1);
254
255 l_limits_exist VARCHAR2(1) := 'N';
256 l_basic_modifiers_exist VARCHAR2(1) := 'N';
257 l_profile_val varchar2(300);
258 Phase_Exception Exception;
259 d_manual_modifier_flag varchar2(1);
260 begin
261 IF p_update_type in ('PHASE', 'ALL')
262 THEN
263 put_line('Begin Pricing Phase Update');
264 END IF;
265 -- bug 3448292
266 -- Changed the update statement to look for pricing_phase_id other than 1, bug 2981629
267 -- also rearranged the tables and changed the optimizer hint
268 /*update qp_pricing_phases PH
269 --at least 1 PRG modifier exists with rltd line
270 set rltd_exists = (
271 select /*+ ordered use_nl(rlt lh) index(ll QP_LIST_LINES_N5) * / 'Y'
272 from qp_list_lines LL, qp_rltd_modifiers RLT, qp_list_headers_b LH
273 where LH.active_flag = 'Y'
274 and LH.list_type_code = 'PRO'
275 and LL.pricing_phase_id = PH.pricing_phase_id
276 and LL.list_header_id = LH.list_header_id
277 and LL.list_line_id = RLT.from_rltd_modifier_id
278 and RLT.rltd_modifier_grp_type = 'QUALIFIER'
279 and LL.list_line_type_code = 'PRG'
280 and rownum = 1)
281 --atleast 1 modifier of type OID exist
282 , oid_exists = (
283 SELECT /*+ ordered use_nl(lh) index(ll QP_LIST_LINES_N5) * / 'Y'
284 from qp_list_lines LL, qp_list_headers_b LH
285 where LH.list_type_code = 'PRO'
286 and LH.active_flag = 'Y'
287 and LL.pricing_phase_id = PH.pricing_phase_id
288 and LL.list_line_type_code = 'OID'
289 and LL.list_header_id = LH.list_header_id
290 and rownum = 1)
291 --at least 1 modifier of level line_group exist
292 , line_group_exists = (
293 SELECT /*+ ordered use_nl(lh) index(ll QP_LIST_LINES_N4) * / 'Y'
294 from qp_list_lines LL, qp_list_headers_b LH
295 where LH.list_type_code in ('DLT','DEL','SLT','PRO','CHARGES')
296 and LH.active_flag = 'Y'
297 and LL.list_header_id = LH.list_header_id
298 and LL.pricing_phase_id = PH.pricing_phase_id
299 and LL.modifier_level_code = 'LINEGROUP'
300 and rownum = 1)
301 --at least 1 freight charge modifier exist
302 , freight_exists = (
303 SELECT /*+ ordered use_nl(lh) index(ll QP_LIST_LINES_N5) * / 'Y'
304 from qp_list_lines LL, qp_list_headers_b LH
305 where LH.list_type_code = 'CHARGES'
306 and LH.active_flag = 'Y'
307 and LL.list_header_id = LH.list_header_id
308 and LL.pricing_phase_id = PH.pricing_phase_id
309 and LL.list_line_type_code = 'FREIGHT_CHARGE'
310 and rownum = 1)
311 where PH.pricing_phase_id = nvl(p_pricing_phase_id,
312 PH.pricing_phase_id)
313 and ph.pricing_phase_id > 1;
314 */
315 if (nvl(p_pricing_phase_id,2) > 1)
316 and (p_call_from is null or p_call_from = 1)then
317 update qp_pricing_phases PH
318 --at least 1 PRG modifier exists with rltd line
319 --[julin/4698834] removed qp_rltd_modifiers RLT; per bug, needs to be 'Y' if PRG simply exists
320 set rltd_exists = (
321 select 'Y'
322 from qp_list_lines LL, qp_list_headers_b LH
323 where LH.active_flag = 'Y'
324 and LH.list_type_code = 'PRO'
325 and LL.pricing_phase_id = PH.pricing_phase_id
326 and LL.list_header_id = LH.list_header_id
327 --and LL.list_line_id = RLT.from_rltd_modifier_id
328 --and RLT.rltd_modifier_grp_type = 'QUALIFIER'
329 and LL.list_line_type_code = 'PRG'
330 and rownum = 1)
331 where PH.pricing_phase_id = nvl(p_pricing_phase_id,
332 PH.pricing_phase_id)
333 and ph.pricing_phase_id > 1;
334 --atleast 1 modifier of type OID exist
335 update qp_pricing_phases PH
336 set oid_exists = (
337 SELECT 'Y'
338 from qp_list_lines LL, qp_list_headers_b LH
339 where LH.list_type_code = 'PRO'
340 and LH.active_flag = 'Y'
341 and LL.pricing_phase_id = PH.pricing_phase_id
342 and LL.list_line_type_code = 'OID'
343 and LL.list_header_id = LH.list_header_id
344 and rownum = 1)
345 where PH.pricing_phase_id = nvl(p_pricing_phase_id,
346 PH.pricing_phase_id)
347 and ph.pricing_phase_id > 1;
348 --at least 1 modifier of level line_group exist
349 if (nvl(p_pricing_phase_id,3) >2) then
350 update qp_pricing_phases PH
351 set line_group_exists = (
352 SELECT /*+ ordered use_nl(lh) index(ll QP_LIST_LINES_N4) */ 'Y'
353 from qp_list_lines LL, qp_list_headers_b LH
354 where LH.list_type_code in ('DLT','DEL','SLT','PRO','CHARGES')
355 and LH.active_flag = 'Y'
356 and LL.list_header_id = LH.list_header_id
357 and LL.pricing_phase_id = PH.pricing_phase_id
358 and LL.modifier_level_code = 'LINEGROUP'
359 and ph.pricing_phase_id >2
360 and rownum = 1)
361 where PH.pricing_phase_id = nvl(p_pricing_phase_id,
362 PH.pricing_phase_id)
363 and ph.pricing_phase_id > 1;
364 end if;
365 --at least 1 freight charge modifier exist
366 update qp_pricing_phases PH
367 set freight_exists = (
368 SELECT 'Y'
369 from qp_list_lines LL, qp_list_headers_b LH
370 where LH.list_type_code = 'CHARGES'
371 and LH.active_flag = 'Y'
372 and LL.list_header_id = LH.list_header_id
373 and LL.pricing_phase_id = PH.pricing_phase_id
374 and LL.list_line_type_code = 'FREIGHT_CHARGE'
375 and rownum = 1)
376 where PH.pricing_phase_id = nvl(p_pricing_phase_id,
377 PH.pricing_phase_id)
378 and ph.pricing_phase_id > 1;
379 end if;
380 -- Essilor Fix bug 2789138
381 -- Fix for 3456907 - added rownum = 1 to the two select's below
382 --bug 3448292 uncommented the pl-sql part.
383 For I in l_phase_cur(p_pricing_phase_id) LOOP
384
385 if I.pricing_phase_id >1 and
386 (p_call_from is null or p_call_from =2)then -- bug 3509423, look into the cursor value, not parameter
387
388 ----------------------------fix for bug 3756625
389 if p_count is not null then
390 if p_count > 1 then
391 l_automatic_exists :='Y';
392 l_manual_exists :='Y';
393 end if;
394 if p_count = 1 then
395 if p_automatic_flag ='Y' then
396 l_automatic_exists :='Y';
397 else
398 l_manual_exists :='Y';
399 end if;
400 end if;
401 elsif p_automatic_flag is not null then
402 select manual_modifier_flag into d_manual_modifier_flag from qp_pricing_phases
403 where pricing_phase_id = I.pricing_phase_id;
404 if d_manual_modifier_flag= 'A' then
405 l_automatic_exists :='Y';
406 elsif d_manual_modifier_flag='M' then
407 l_manual_exists :='Y';
408 elsif d_manual_modifier_flag='B' then
409 l_automatic_exists :='Y';
410 l_manual_exists :='Y';
411 else null;
412 end if;
413 if p_automatic_flag = 'Y' then
414 l_automatic_exists :='Y';
415 else
416 l_manual_exists :='Y';
417 end if;
418 ----------------------------------fix for bug 3756625
419 else
420
421
422 begin
423 /*
424 select 'Y' into l_automatic_exists
425 from qp_list_lines l, qp_list_headers_b h
426 where l.automatic_flag = 'Y'
427 and l.pricing_phase_id = I.pricing_phase_id
428 and l.list_header_id = h.list_header_id
429 and l.modifier_level_code in ('LINE', 'LINEGROUP', 'ORDER')
430 and h.active_flag = 'Y'
431 and rownum = 1;
432 */
433 --fix for sql repository perf bug 3640054
434 select 'Y' into l_automatic_exists from dual
435 where exists (select 1 from qp_list_lines l
436 where l.automatic_flag = 'Y'
437 and l.pricing_phase_id = I.pricing_phase_id
438 and exists (select 'x' from qp_list_headers_b h
439 where l.list_header_id = h.list_header_id
440 and h.active_flag = 'Y'));
441 exception
442 WHEN no_data_found THEN
443 NULL;
444 end;
445
446 begin
447 /*
448 select 'Y' into l_manual_exists
449 from qp_list_lines l, qp_list_headers_b h
450 where l.automatic_flag = 'N'
451 and l.pricing_phase_id = I.pricing_phase_id
452 and l.list_header_id = h.list_header_id
453 and l.modifier_level_code in ('LINE', 'LINEGROUP', 'ORDER')
454 and h.active_flag = 'Y'
455 and rownum = 1;
456 */
457 --fix for sql repository perf bug 3640054
458 select 'Y' into l_manual_exists from dual
459 where exists (select 1 from qp_list_lines l
460 where l.automatic_flag = 'N'
461 and l.pricing_phase_id = I.pricing_phase_id
462 and exists (select 'x' from qp_list_headers_b h
463 where l.list_header_id = h.list_header_id
464 and h.active_flag = 'Y'));
465 exception
466 WHEN no_data_found THEN
467 NULL;
468 end;
469 end if;
470 IF l_automatic_exists = 'Y' THEN
471 IF l_manual_exists = 'Y' THEN
472 l_manual_modifier_flag := 'B';
473 ELSE
474 l_manual_modifier_flag := 'A';
475 END IF;
476 ELSIF l_manual_exists = 'Y' THEN
477 l_manual_modifier_flag := 'M';
478 END IF;
479
480 update qp_pricing_phases
481 set manual_modifier_flag = l_manual_modifier_flag
482 where pricing_phase_id = I.pricing_phase_id;
483
484 l_automatic_exists := 'N';
485 l_manual_exists := 'N';
486 l_manual_modifier_flag := NULL;
487 end if;
488 END LOOP;
489 -- bug 3448292 had to comment out because of high cost involved in SORT and
490 --CONCATENATE
491 --Added for bug#2894244
492 -- Changed the update statement to look for pricing_phase_id other than 1, bug 2981629
493 -- also rearranged the tables and changed the optimizer hint
494 /* update qp_pricing_phases I
495 set manual_modifier_flag =
496 (select /*+ ordered use_nl(h) index(l QP_LIST_LINES_N5)
497 decode(min(l.automatic_flag)||max(l.automatic_flag),'YY','A','NN','M','B')
498 from qp_list_lines l, qp_list_headers_b h
499 where l.pricing_phase_id = I.pricing_phase_id
500 and l.list_header_id = h.list_header_id
501 and h.active_flag = 'Y')
502 where pricing_phase_id = nvl(p_pricing_phase_id, pricing_phase_id)
503 and pricing_phase_id > 1;
504 */
505 IF p_update_type in ('PHASE', 'ALL')
506 THEN
507 put_line('End Pricing Phase Update');
508 END IF;
509
510 --post QP.I, the OM integration code will go thru the performance code path
511 --in all cases. So profile QP_BASIC_MODIFIERS_SETUP will be set to N always
512 IF QP_CODE_CONTROL.Get_Code_Release_Level < '110509' THEN
513 OPEN l_basic_modifiers_cur;
514 FETCH l_basic_modifiers_cur INTO l_basic_modifiers_exist;
515 CLOSE l_basic_modifiers_cur;
516
517 OPEN l_limits_exist_cur;
518 FETCH l_limits_exist_cur INTO l_limits_exist;
519 CLOSE l_limits_exist_cur;
520
521 IF p_update_type in ('PHASE', 'ALL')
522 THEN
523 put_line('Completed Update of Profile: limits exist: '||l_limits_exist||' basic modifiers exist: '||l_basic_modifiers_exist);
524 END IF;--update_type
525 END IF;--QP_CODE_CONTROL.Get_Code_Release_Level
526
527 --this was added for QP.H to check if there are only basic modifiers in the
528 --setup and to setup a profile accordingly. Based on this profile
529 --OM-QP integration will call new code path if profile is 'Y'
530 --if there are no limits and no advanced modifiers
531 --post QP.I, the OM integration code will go thru the performance code path
532 --in all cases. So profile QP_BASIC_MODIFIERS_SETUP will be set to N always
533
534 IF QP_CODE_CONTROL.Get_Code_Release_Level < '110509' THEN -- Bug 2841107
535 IF ((l_limits_exist = 'N')
536 and (l_basic_modifiers_exist = 'N'))
537 THEN
538 IF p_update_type in ('PHASE', 'ALL')
539 THEN
540 put_line('Completed Update of Profile: limits exist: '||l_limits_exist||' basic modifiers exist: '||l_basic_modifiers_exist);
541 END IF;--update_type
542 IF (FND_PROFILE.SAVE('QP_BASIC_MODIFIERS_SETUP'
543 ,'Y','SITE'))--) = FND_API.G_FALSE)
544 THEN
545 null;
546 ELSE
547 Raise Phase_Exception;
548 END IF;
549 ELSE
550 IF p_update_type in ('PHASE', 'ALL')
551 THEN
552 put_line('Completed Update of Profile: limits exist: '||l_limits_exist||' basic modifiers exist: '||l_basic_modifiers_exist);
553 END IF;--update_type
554
555 IF (FND_PROFILE.SAVE('QP_BASIC_MODIFIERS_SETUP'
556 ,'N','SITE'))--) = FND_API.G_FALSE)
557 THEN
558 null;
559 ELSE
560 Raise Phase_Exception;
561 END IF;
562 END IF;
563
564 -- END IF; --commenting for bug#3798392
565
566 IF p_update_type in ('PHASE', 'ALL', 'BATCH')
567 THEN
568 commit;
569 END IF;
570
571 IF p_update_type in ('PHASE', 'ALL')
572 THEN
573 FND_PROFILE.GET('QP_BASIC_MODIFIERS_SETUP',l_profile_val);
574 put_line('Completed Update of Profile:'||l_profile_val);
575 END IF;--update_type
576 END IF; --for bug#3798392
577
578
579 EXCEPTION
580 When Phase_Exception THEN
581 x_return_status := FND_API.G_RET_STS_ERROR;
582 x_return_status_text := 'QP_DENOB.update_pricing_phases:'||substr(SQLERRM,1,200);
583 IF p_update_type in ('ALL', 'PHASE')
584 THEN
585 put_line('EXCEPTION RAISED IN PHASE UPDATE'||SQLERRM);
586 END IF;
587 When OTHERS THEN
588 x_return_status := FND_API.G_RET_STS_ERROR;
589 x_return_status_text := 'QP_DENOB.update_pricing_phases:'||substr(SQLERRM,1,200);
590 IF p_update_type in ('ALL', 'PHASE')
591 THEN
592 put_line('EXCEPTION RAISED IN PHASE UPDATE'||SQLERRM);
593 END IF;
594 end update_pricing_phases;
595
596 procedure update_row_count(p_List_Header_Id_low NUMBER
597 ,p_List_Header_Id_High NUMBER
598 ,p_update_type VARCHAR
599 ,x_return_status OUT NOCOPY VARCHAR2
600 ,x_return_status_text OUT NOCOPY VARCHAR2)
601 is
602
603 /* Changes for bug 3136350.
604 This is performance bug fix. The update statement for volume data was taking long time.
605 Modified the logic to eliminate the corelated query update logic. Changed the login to do
606 a bulk update.
607 */
608
609 -- bug 3136350 start
610
611 CURSOR upd_distinct_row_count IS
612 SELECT qpq1.qualifier_context,
613 qpq1.qualifier_attribute,
614 qpq1.comparison_operator_code,
615 qpq1.qualifier_attr_value,
616 qpq1.qualifier_attr_value_to,
617 count(*) distinct_rows
618 FROM qp_qualifiers qpq1
619 WHERE qpq1.list_header_id between p_List_Header_Id_low and p_List_Header_Id_High --5860276
620 and qpq1.list_header_id is not null
621 AND qpq1.active_flag = 'Y'
622 --for bug 5121471
623 and qpq1.list_type_code not in ('PRL', 'AGR')
624 GROUP BY qpq1.qualifier_context,
625 qpq1.qualifier_attribute,
626 qpq1.comparison_operator_code,
627 qpq1.qualifier_attr_value,
628 qpq1.qualifier_attr_value_to;
629
630 -- bug 3136350 end
631 begin
632
633 --enclosed all fnd_file within this IF block to prevent writing into log
634 --file as it raises an exception in case of delayed requests bug 1621199
635 --and batch calls for the same reason as above
636 IF P_UPDATE_TYPE IN ('ALL','DENORMALIZED','QUAL_IND') THEN
637 fnd_file.put_line(FND_FILE.LOG,'Begin Row Count Update');
638 END IF;
639
640
641 /* Commented for bug 3136350
642 update qp_qualifiers qpq set DISTINCT_ROW_COUNT=
643 (select count(*) from qp_qualifiers qpq1 where
644 qpq.qualifier_context=qpq1.qualifier_context and
645 qpq.qualifier_attribute=qpq1.qualifier_attribute and
646 qpq.qualifier_attr_value=qpq1.qualifier_attr_value and
647 nvl(qpq.qualifier_attr_value_to,'-x') = nvl(qpq1.qualifier_attr_value_to,'-x') and
648 qpq.comparison_operator_code=qpq1.comparison_operator_code and
649 qpq1.active_flag='Y' and
650 qpq1.list_header_id is not null
651 and qpq1.list_header_id between p_List_Header_Id_low and p_list_header_id_high)
652 where (qpq.list_header_id between p_List_Header_Id_low and p_list_header_id_high);
653 -- p_List_Header_Id is null);
654
655 */
656
657 -- bug 3136350 start
658
659 FOR rec IN upd_distinct_row_count LOOP
660
661 UPDATE qp_qualifiers qpq
662 SET DISTINCT_ROW_COUNT = rec.distinct_rows
663 WHERE qpq.qualifier_context = rec.qualifier_context
664 AND qpq.qualifier_attribute = rec.qualifier_attribute
665 AND qpq.comparison_operator_code = rec.comparison_operator_code
666 AND qpq.qualifier_attr_value = rec.qualifier_attr_value
667 AND nvl(qpq.qualifier_attr_value_to,'-x') = nvl(rec.qualifier_attr_value_to,'-x')
668 --for bug 5121471
669 AND qpq.list_type_code not in ('PRL', 'AGR')
670 AND (qpq.list_header_id between p_List_Header_Id_low and p_list_header_id_high);
671
672 END LOOP;
673
674 -- bug 3136350 end
675
676 IF P_UPDATE_TYPE IN ('BATCH','ALL', 'DENORMALIZED') THEN
677 commit;
678 END IF;
679
680 IF P_UPDATE_TYPE IN ('ALL','DENORMALIZED','QUAL_IND') THEN
681 fnd_file.put_line(FND_FILE.LOG,'Completed Row Count Update');
682 END IF;
683 EXCEPTION
684 WHEN OTHERS THEN
685 x_return_status:= FND_API.G_RET_STS_ERROR;
686 x_return_status_text:='Exception In Update Row '||substr(sqlerrm,1,300);
687 end update_row_count;
688
689 PROCEDURE Update_Qualifiers
690 (err_buff out NOCOPY VARCHAR2,
691 retcode out NOCOPY NUMBER,
692 p_List_Header_Id NUMBER,
693 p_List_Header_Id_high NUMBER,
694 p_update_type VARCHAR2,
695 p_dummy VARCHAR2,
696 p_request_id NUMBER := NULL --bug 8359554
697 ) Is
698
699 l_old_Header_id number := -9999;
700 l_old_Line_id number := -9999;
701 l_old_qualifier_grouping_no number := -9999;
702 l_header_qual_exists Varchar2(1);
703 l_group_cnt number := 0;
704 l_Qual_cnt number := 0;
705 L_Search_Ind Number;
706 l_Grp_Change Boolean := FALSE;
707 l_Grp_Start_Index number := 1;
708 l_null_grp_count number := 0;
709 l_list_header_id NUMBER :=-1; -- 7321919
710 l_list_header_id_low NUMBER :=0;
711 l_list_header_id_high NUMBER :=0;
712
713 TYPE Num_Type IS TABLE OF Number INDEX BY BINARY_INTEGER;
714 TYPE Char_Type IS TABLE OF Varchar2(1) INDEX BY BINARY_INTEGER;
715 TYPE Rowid_Type IS TABLE OF rowid INDEX BY BINARY_INTEGER;
716
717 l_header_qual_exists_Tbl Char_Type;
718 l_group_cnt_Tbl num_type;
719 L_Search_Ind_Tbl num_type;
720 l_rowid_tbl Rowid_Type;
721 l_null_header_id_tbl num_type;
722 l_null_line_id_tbl num_type;
723 l_null_header_Exists_tbl num_type;
724 l_null_line_Exists_tbl num_type;
725 l_others_group_cnt_tbl num_type;
726 l_qual_cnt_tbl num_type;
727
728 TYPE Char30_Type IS TABLE OF Varchar2(30) INDEX BY BINARY_INTEGER;
729
730 l_list_line_id_tbl num_type;
731 l_qualification_ind_tbl num_type;
732 l_list_type_code_tbl char30_type;
733 l_list_header_id_tbl num_type;
734
735 l_return_status varchar2(1);
736 l_count number;
737 l_qcount number := 0; --7321919
738 l_rows NATURAL := 5000;
739 l_total_rows number := 0;
740 l_header_line_change_index number := 1;
741 l_list_type_code varchar2(30); --5922279
742 l_max_qual_no number := 0; --7038849
743
744
745 /*
746 Changed cursor definition for bug 8359554. Added one input parameter l_request_id
747 and a where condition to match the passed request id. If the l_request_id is not
748 null then cursor will return only those list lines which have been updated
749 or inserted for this request.
750 */
751
752 cursor list_lines_cur(a_list_header_id number,b_list_header_id NUMBER, l_request_id NUMBER)
753 is
754 select /*+ index(l qp_list_lines_n15) index(h qp_list_headers_b_n7)*/ --8418006
755 l.list_line_id, l.qualification_ind, h.list_type_code, h.list_header_id
756 from qp_list_lines l, qp_list_headers_b h
757 where l.list_header_id = h.list_header_id
758 and h.active_flag = 'Y'
759 and (h.list_header_id between a_list_header_id and b_list_header_id)
760 and decode (l_request_id,null,1,l.REQUEST_ID) = nvl (l_request_id,1) --bug 8359554
761 order by h.list_header_id; --7321919
762 -- or a_list_header_id is null);
763
764 cursor list_headers_cur(a_list_header_id NUMBER, b_list_header_id NUMBER)
765 is
766 select list_header_id
767 from qp_list_headers_b
768 where list_header_id between a_list_header_id and b_list_header_id
769 and list_type_code not in ('PRL', 'AGR', 'PML');
770
771 Phase_Exception Exception;
772
773 --hw 07/19/02
774 -- added to assign search_ind = 1 to 'NOT =' only if there's no any other operator
775 -- in the group
776 l_search_ind_1_set number := -1;
777
778 --nhase/hw 07/29/02
779 cursor l_qp_list_header_phases_qual is
780 select list_header_id, pricing_phase_id from qp_list_header_phases
781 where list_header_id between
782 --for bug 5121471
783 p_List_Header_Id and p_List_Header_Id_high;
784 cursor l_line_qual_exc (p_pricing_phase_id number,
785 p_list_header_id number) is
786 select list_line_id from qp_list_lines where
787 list_header_id = p_list_header_id
788 and pricing_phase_id = p_pricing_phase_id
789 minus
790 select list_line_id from qp_qualifiers where
791 list_header_id = p_list_header_id
792 and list_line_id <> -1;
793 l_list_line_id number;
794
795 begin
796
797 --*************************************************************************
798 --To update qp_adv_mod_products for changed lines
799 --*************************************************************************
800
801 IF P_UPDATE_TYPE in ('HVOP_PRICING_SETUP','ALL')
802 and QP_CODE_CONTROL.Get_Code_Release_Level > '110509' THEN
803 Set_HVOP_Pricing(l_return_status, err_buff);
804 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
805 IF P_UPDATE_TYPE in ('HVOP_PRICING_SETUP','ALL') THEN
806 put_line('Exception in Update_HVOP Profile: '||l_return_status);
807 END IF;
808 Raise Phase_Exception;
809 END IF;
810 END IF;--P_UPDATE_TYPE = 'HVOP_PRICING_SETUP'
811
812 --*************************************************************************
813 --To update qp_adv_mod_products for changed lines
814 --*************************************************************************
815
816 IF P_UPDATE_TYPE in ('BATCH_ADV_MOD_PRODUCTS', 'ADV_MOD_PRODUCTS','ALL')
817 and QP_CODE_CONTROL.Get_Code_Release_Level > '110508' THEN
818 --for bug 5237249
819 IF p_list_header_id IS NOT NULL THEN
820 G_LIST_HEADER_ID := p_list_header_id;
821 END IF;--5237249
822
823 IF p_list_header_id_high IS NOT NULL THEN
824 G_LIST_HEADER_ID_HIGH := p_list_header_id_high;
825 END IF;--5237249
826
827 G_UPDATE_TYPE := P_UPDATE_TYPE;
828
829 Update_adv_mod_products(l_return_status, err_buff);
830 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
831 IF P_UPDATE_TYPE in ('ADV_MOD_PRODUCTS','ALL') THEN
832 put_line('Exception in Update_adv_mod_products: '||l_return_status);
833 END IF;
834 Raise Phase_Exception;
835 END IF;
836 retcode := 1;
837 END IF;--P_UPDATE_TYPE = 'ADV_MOD_PRODUCTS'
838
839 --*************************************************************************
840
841 --LOOP TO UPDATE PRICING PHASES added by spgopal
842
843 --*************************************************************************
844
845
846 IF P_UPDATE_TYPE in ('PHASE','ALL') THEN
847
848 put_line('Begin Update Pricing Phases - update_type '||p_update_type);
849
850 update_pricing_phases(p_update_type => p_update_type,
851 x_return_status => l_return_status,
852 x_return_status_text => err_buff);
853
854 IF l_return_status = FND_API.G_RET_STS_ERROR
855 THEN
856 Raise Phase_Exception;
857 END IF;
858 commit;
859 put_line('Completed Update of Pricing Phases');
860 END IF;--P_UPDATE_TYPE = 'PHASE'
861
862 --************************************************************************
863
864 /***********************************************************************
865 Begin code to denormalize qp_pricing_attributes and qp_factor_list_attrs
866 for Factor Lists only. -rchellam (08/28/01). POSCO change.
867 ***********************************************************************/
868
869 IF p_update_type IN ('ALL','FACTOR','BATCH')
870 THEN
871
872 IF P_UPDATE_TYPE IN ('ALL','FACTOR') THEN
873 put_line('Begin Factor Attrs Denormalization');
874 END IF;
875
876 BEGIN
877 QP_Denormalized_Pricing_Attrs.Update_Pricing_Attributes(
878 p_list_header_id,
879 p_list_header_id_high,
880 p_update_type);
881 EXCEPTION
882 WHEN OTHERS THEN
883 put_line('Exception occured while excecuting QP_Denormalized_Pricing_Attrs.Update_Pricing_Attributes');
884 END;
885
886 IF P_UPDATE_TYPE IN ('ALL','FACTOR') THEN
887 put_line('End Factor Attrs Denormalization');
888 put_line('Begin Insertion of Factor List Attrs');
889 END IF;
890
891 BEGIN
892 QP_Denormalized_Pricing_Attrs.Populate_Factor_List_Attrs(
893 p_list_header_id,
894 p_list_header_id_high);
895 EXCEPTION
896 WHEN OTHERS THEN
897 put_line('Exception occured while excecuting QP_Denormalized_Pricing_Attrs.Populate_Factor_List_Attrs');
898 END;
899
900 IF P_UPDATE_TYPE IN ('ALL','FACTOR') THEN
901 put_line('End Insertion of Factor List Attrs');
902 END IF;
903
904 END IF; --If p_update_type in ALL, FACTOR, BATCH
905 /***********************************************************************
906 End code to denormalize qp_pricing_attributes and qp_factor_list_attrs
907 for Factor Lists only. -rchellam (08/28/01). POSCO change.
908 ***********************************************************************/
909
910 IF p_list_header_id IS NOT NULL
911 and P_UPDATE_TYPE <> 'BATCH_ADV_MOD_PRODUCTS' THEN
912
913 IF P_UPDATE_TYPE IN ('ALL','DENORMALIZED','QUAL_IND') THEN
914 put_line('Begin Update list_header_id '||p_list_header_id||' '||p_list_header_id_high||' update_type '||p_update_type);
915 END IF;
916
917 if p_list_header_id_high is not null then
918 --ensure that p_list_header_id < p_list_header_id_high
919 l_list_header_id_low := least(p_list_header_id, p_list_header_id_high);
920 l_list_header_id_high := greatest(p_list_header_id, p_list_header_id_high);
921 else
922 l_list_header_id_low := p_list_header_id;
923 l_list_header_id_high := p_list_header_id;
924 end if;
925
926 IF P_UPDATE_TYPE IN ('ALL','DENORMALIZED','QUAL_IND') THEN
927 put_line('list_header_ids '||l_list_header_id_low||'- '||l_list_header_id_high||' update_type '||p_update_type);
928 END IF;
929
930 IF p_update_type IN ('QUAL_IND','ALL') THEN --(Qual Ind)
931
932 -- IF P_UPDATE_TYPE IN ('ALL','QUAL_IND') THEN
933 put_line('Begin Update Qualification_Ind ');
934 -- END IF;
935
936 OPEN list_lines_cur(l_list_header_id_low, l_list_header_id_high, p_request_id); --bug 8359554
937
938 LOOP
939 l_list_line_id_tbl.delete;
940 l_list_header_id_tbl.delete;
941 l_qualification_ind_tbl.delete;
942 l_list_type_code_tbl.delete;
943
944 FETCH list_lines_cur BULK COLLECT INTO l_list_line_id_tbl,
945 l_qualification_ind_tbl, l_list_type_code_tbl,
946 l_list_header_id_tbl LIMIT l_rows;
947
948 EXIT WHEN l_list_line_id_tbl.COUNT = 0;
949
950 BEGIN
951
952 FOR i IN l_list_line_id_tbl.FIRST..l_list_line_id_tbl.LAST
953 LOOP
954 BEGIN
955 --Initialize qualification_ind to 0.
956 l_qualification_ind_tbl(i) := 0;
957
958 --If line has rltd modifiers, then increment qual_ind by 1.
959 BEGIN
960 select 1
961 into l_count
962 from dual where exists
963 (select 'x'
964 from qp_rltd_modifiers
965 where to_rltd_modifier_id = l_list_line_id_tbl(i)
966 and rltd_modifier_grp_type <> 'COUPON');
967 EXCEPTION
968 WHEN NO_DATA_FOUND THEN
969 l_count := 0;
970 END;
971
972 IF l_count > 0 THEN
973 l_qualification_ind_tbl(i) := l_qualification_ind_tbl(i) + 1;
974 END IF;
975
976 --If line belongs to Price List or Agreement and if the PRL or AGR
977 --has header-level qualifier other than Primary PL that are
978 --qualifiers of Secondary PLs, then increment qual_ind by 2.
979 IF l_list_type_code_tbl(i) IN ('AGR', 'PRL') THEN
980 --added for 5922279
981 IF l_list_header_id_low = l_list_header_id_high then
982 l_list_type_code:= l_list_type_code_tbl(l_list_type_code_tbl.FIRST);
983 End if;
984 --Bug#7321919: No need to run the same query if the header id is the same.
985 IF l_list_header_id_tbl(i) <> l_list_header_id THEN
986 BEGIN
987 select 1
988 into l_count
989 from dual where exists
990 (select 'x'
991 from qp_qualifiers
992 where list_header_id = l_list_header_id_tbl(i)
993 and NOT (qualifier_context = 'MODLIST' and
994 qualifier_attribute = 'QUALIFIER_ATTRIBUTE4')
995 );
996 EXCEPTION
997 WHEN NO_DATA_FOUND THEN
998 l_count := 0;
999 END;
1000 l_list_header_id := l_list_header_id_tbl(i);
1001 l_qcount := l_count;
1002 ELSE
1003 l_count := l_qcount;
1004 END IF; --7321919
1005
1006 IF l_count > 0 THEN
1007 l_qualification_ind_tbl(i) := l_qualification_ind_tbl(i) + 2;
1008 END IF;
1009
1010 --For all other list header types
1011 ELSE
1012 --If header-level qualifier exists for the list_header_id then
1013 --increment qual ind by 2
1014 BEGIN
1015 select 1
1016 into l_count
1017 from dual where exists
1018 (select 'x'
1019 from qp_qualifiers
1020 where list_header_id = l_list_header_id_tbl(i)
1021 and nvl(list_line_id,-1) = -1);
1022 EXCEPTION
1023 WHEN NO_DATA_FOUND THEN
1024 l_count := 0;
1025 END;
1026
1027 IF l_count > 0 THEN
1028 l_qualification_ind_tbl(i) := l_qualification_ind_tbl(i) + 2;
1029 END IF;
1030
1031 --If line-level qualifier exists for the list_line_id then
1032 --increment qual ind by 8
1033 BEGIN
1034 select 1
1035 into l_count
1036 from dual where exists
1037 (select 'x'
1038 from qp_qualifiers
1039 where list_header_id = l_list_header_id_tbl(i)
1040 and list_line_id = l_list_line_id_tbl(i));
1041 EXCEPTION
1042 WHEN NO_DATA_FOUND THEN
1043 l_count := 0;
1044 END;
1045
1046 IF l_count > 0 THEN
1047 l_qualification_ind_tbl(i) := l_qualification_ind_tbl(i) + 8;
1048 END IF;
1049
1050 END IF;
1051
1052 --If line has product attributes, then increment qual_ind by 4.
1053 BEGIN
1054 select 1
1055 into l_count
1056 from dual where exists
1057 (select 'x'
1058 from qp_pricing_attributes
1059 where list_line_id = l_list_line_id_tbl(i)
1060 and excluder_flag = 'N');
1061 EXCEPTION
1062 WHEN NO_DATA_FOUND THEN
1063 l_count := 0;
1064 END;
1065
1066 IF l_count > 0 THEN
1067 l_qualification_ind_tbl(i) := l_qualification_ind_tbl(i) + 4;
1068 END IF;
1069
1070 --If line has pricing attributes, then increment qual_ind by 16.
1071 BEGIN
1072 select 1
1073 into l_count
1074 from dual where exists
1075 (select 'x'
1076 from qp_pricing_attributes
1077 where list_line_id = l_list_line_id_tbl(i)
1078 and pricing_attribute_context is not null
1079 and pricing_attribute is not null
1080 -- changes made per rchellam's request--spgopal
1081 and pricing_attr_value_from IS NOT NULL);
1082 EXCEPTION
1083 WHEN NO_DATA_FOUND THEN
1084 l_count := 0;
1085 END;
1086
1087 IF l_count > 0 THEN
1088 l_qualification_ind_tbl(i) := l_qualification_ind_tbl(i) + 16;
1089 END IF;
1090
1091 EXCEPTION
1092 WHEN OTHERS THEN
1093 IF P_UPDATE_TYPE IN ('ALL','QUAL_IND') THEN
1094 put_line( substr(sqlerrm, 1, 240) );
1095 put_line(
1096 'Error in processing list_line_id '||
1097 to_char(l_list_line_id_tbl(l_list_line_id_tbl.FIRST + SQL%ROWCOUNT)));
1098 END IF;
1099
1100 END;
1101 END LOOP; --End of For Loop
1102
1103 FORALL j IN l_list_line_id_tbl.FIRST..l_list_line_id_tbl.LAST
1104 UPDATE qp_list_lines
1105 SET qualification_ind = l_qualification_ind_tbl(j)
1106 WHERE list_line_id = l_list_line_id_tbl(j);
1107
1108 FORALL k IN l_list_line_id_tbl.FIRST..l_list_line_id_tbl.LAST
1109 UPDATE qp_pricing_attributes
1110 SET qualification_ind = l_qualification_ind_tbl(k)
1111 WHERE list_line_id = l_list_line_id_tbl(k);
1112
1113 l_total_rows := l_total_rows + SQL%ROWCOUNT;
1114
1115 EXCEPTION
1116 WHEN OTHERS THEN
1117 IF P_UPDATE_TYPE IN ('ALL','QUAL_IND') THEN
1118 put_line( substr(sqlerrm, 1, 240));
1119 put_line(
1120 'Error in processing list_line_id '||
1121 to_char(l_list_line_id_tbl(l_list_line_id_tbl.FIRST + SQL%ROWCOUNT)));
1122 END IF;
1123
1124 END;
1125
1126 COMMIT; --after every 5000(l_rows) lines are processed
1127
1128 END LOOP; --End of cursor loop
1129
1130 CLOSE list_lines_cur;
1131
1132 IF P_UPDATE_TYPE IN ('ALL','QUAL_IND') THEN
1133 put_line('Qualification_Ind Update Completed');
1134 put_line( to_char(l_total_rows) || ' list lines processed');
1135 END IF;
1136
1137 END IF; --IF update_type IN (ALL, QUAL_IND), (see matching (Qual Ind))
1138
1139 /************************************************************************
1140 Begin code to maintain qp_list_header_phases for modifierlist types only
1141 ************************************************************************/
1142 IF P_UPDATE_TYPE IN ('BATCH','HEADER_PHASE','ALL')
1143 THEN
1144
1145 IF P_UPDATE_TYPE IN ('ALL','HEADER_PHASE') THEN
1146 put_line('Begin Maintain List Header Phases ');
1147 END IF;
1148
1149 --ensure that p_list_header_id < p_list_header_id_high
1150 IF p_list_header_id_high IS NOT NULL THEN
1151 l_list_header_id_low := least(p_list_header_id, p_list_header_id_high);
1152 l_list_header_id_high :=
1153 greatest(p_list_header_id, p_list_header_id_high);
1154 ELSE
1155 l_list_header_id_low := p_list_header_id;
1156 l_list_header_id_high := p_list_header_id;
1157 END IF;
1158
1159 FOR l_rec in list_headers_cur(l_list_header_id_low,l_list_header_id_high)
1160 LOOP
1161 delete from qp_list_header_phases
1162 where list_header_id = l_rec.list_header_id;
1163
1164 /*
1165 Bug - 8224336
1166 Changes for Pattern Engine - added column PRIC_PROD_ATTR_ONLY_FLAG
1167 Column PRIC_PROD_ATTR_ONLY_FLAG in table qp_list_header_phases will be -
1168 'Y' - If all the lines in the header for that phase have only product or pricing or both attributes (but not qualifiers).
1169 'N' - If atleast one line within that header or header itself has qualifiers attached, for that phase
1170 */
1171
1172 insert into qp_list_header_phases (list_header_id,pricing_phase_id,PRIC_PROD_ATTR_ONLY_FLAG) /* Added column names for 2236671 */
1173 (select distinct list_header_id, pricing_phase_id,'N'
1174 from qp_list_lines
1175 where pricing_phase_id > 1
1176 and qualification_ind in (2,6,8,10,12,14,22,28,30)
1177 and list_header_id = l_rec.list_header_id);
1178
1179 END LOOP;
1180
1181 commit;
1182
1183 IF P_UPDATE_TYPE IN ('ALL','HEADER_PHASE') THEN
1184 put_line('Completed Maintain List Header Phases');
1185 END IF;
1186
1187 /************************************************************************
1188 End code to maintain qp_list_header_phases -rchellam. Moved this code
1189 to below the qualification_ind code (05/29/01) -rchellam
1190 ************************************************************************/
1191
1192 ELSE
1193 --We do not want the update of qualification_ind when calling from delayed request package as this would have been done already by the other delayed requests update_list_qual_ind and update_line_qual_ind --spgopal
1194 null;
1195 END IF; --p_list_header_is is not null
1196
1197
1198 IF p_update_type IN ('BATCH', 'ALL', 'DENORMALIZED', 'DELAYED_REQ', 'UPD_QUAL') THEN
1199 --we want to process the request for different updates based on update type
1200
1201 --Added for 5922279. procedure update_row_count does not need to be called for AGR,PRL through UI
1202 IF NOT( l_list_header_id_low = l_list_header_id_high AND l_list_type_code in ('PRL','AGR')) then
1203 Update_row_count(l_list_header_id_low,l_list_header_id_high, p_update_type,l_return_status,err_buff);
1204 IF l_return_status=FND_API.G_RET_STS_ERROR THEN
1205 put_line('Exception While Updating Row Count'||l_return_status);
1206 Raise Phase_Exception;
1207 END IF;
1208 END IF;
1209 IF P_UPDATE_TYPE IN ('ALL','DENORMALIZED','QUAL_IND') THEN
1210 put_line('Begin Update Denormalized columns ');
1211 END IF;
1212
1213 /* Added for bug 7038849 */
1214 BEGIN
1215 SELECT MAX(ABS(qualifier_grouping_no))
1216 INTO l_max_qual_no
1217 FROM qp_qualifiers
1218 WHERE list_header_id between l_list_header_id_low and l_list_header_id_high;
1219 EXCEPTION
1220 WHEN OTHERS THEN
1221 l_max_qual_no:= 0;
1222 END;
1223 /* Added for bug7038849--changed order by clause
1224 logic has been changed to support -ve qualifier grouping no. other than -1.
1225 When max absolute value is added to qualifier grp no, result will be always positive or zero
1226 for non -1 qualifier grp no and hence during order by, -1 qualifier grp no will come at the top
1227 in result due to decode. This way we dont have to change any pl/sql logic
1228 */
1229
1230
1231
1232 --hw
1233 -- this is for modifier only
1234 for c1 in (select rowid,list_header_id,nvl(list_line_id,-1) list_line_id,
1235 nvl(qualifier_grouping_no,-1) qualifier_grouping_no, Distinct_row_count, comparison_operator_code
1236 from qp_qualifiers
1237 where (list_header_id between l_list_header_id_low and l_list_header_id_high)
1238 and list_type_code not in ('PRL','AGR')
1239 --order by list_header_id,nvl(list_line_id,-1),decode(nvl(qualifier_grouping_no,-1), -1, -9999, qualifier_grouping_no), comparison_operator_code, Distinct_row_count) -- 7038849
1240 order by list_header_id,nvl(list_line_id,-1),decode(nvl(qualifier_grouping_no,-1),-1,nvl(qualifier_grouping_no,-1),nvl(qualifier_grouping_no,-1)+l_max_qual_no), comparison_operator_code, Distinct_row_count) -- 7038849
1241 loop
1242
1243 --oe_debug_pub.add(c1.rowid ||', '|| c1.list_header_id ||', '|| c1.list_line_id ||', '|| c1.qualifier_grouping_no ||', '|| l_qual_cnt ||', '|| l_group_cnt ||', '|| l_null_grp_count);
1244
1245 if l_old_header_id <> c1.list_header_id
1246 then
1247 l_grp_Change := TRUE;
1248 l_header_qual_exists := 'N';
1249 end if; -- Grp no has changed.
1250
1251 If c1.list_line_id = -1 then
1252 l_header_qual_exists := 'Y';
1253 End If;
1254
1255 if l_old_line_id <> c1.list_line_id
1256 then
1257 l_grp_Change := TRUE;
1258 end if; -- Grp no has changed.
1259
1260
1261 if l_old_qualifier_grouping_no <> c1.qualifier_grouping_no
1262 then
1263 l_grp_Change := TRUE;
1264 end if; -- Grp no has changed.
1265
1266 If l_grp_Change then
1267 l_grp_Change := FALSE;
1268
1269 --hw
1270 --if 'not =' is the only operator in this group (when l_search_ind_1_set is not set),
1271 --then reset the search_ind to 1
1272 if l_search_ind_1_set = 0 then
1273 l_search_ind_tbl(l_search_ind_tbl.count) := 1;
1274 end if;
1275 l_search_ind_1_set := 0;
1276
1277 /* Update the rows of the group with group count */
1278 If l_group_cnt_tbl.count > 0 then
1279
1280 For k in l_Grp_Start_Index..l_group_cnt_tbl.Last loop
1281 If c1.qualifier_grouping_no <> -1 then
1282 If (c1.list_header_id = l_old_header_id and
1283 c1.list_line_id = l_old_line_id and l_null_grp_count > 0 ) then
1284 --fix for bug 2102211 performance problem
1285 --populating the l_null_header_id_tbl and
1286 --l_null_line_id_tbl only when list_header_id
1287 --or list_line_id changes
1288 If not l_null_header_exists_tbl.exists(l_old_header_id) then
1289 l_null_header_exists_tbl(l_old_header_id) := 1;
1290 l_null_Line_exists_tbl.delete;
1291 end if;
1292 If not l_null_Line_exists_tbl.exists(l_old_Line_id) then
1293 l_null_Line_exists_tbl(l_old_line_id ) := 1;
1294 l_null_header_id_tbl(l_null_header_id_tbl.count+1) := l_old_header_id;
1295 l_null_line_id_tbl(l_null_line_id_tbl.count+1) := l_old_line_id;
1296 end if;
1297 End If;--l_null_grp_cnt > 0
1298 End If;--c1.qualifier_grouping_no <> -1
1299
1300 If l_old_qualifier_grouping_no <> -1 then
1301 l_group_cnt_tbl(k) := l_group_cnt + l_null_grp_count;
1302 else
1303 l_group_cnt_tbl(k) := l_group_cnt;
1304 End If;
1305
1306 End Loop;
1307
1308 --hw
1309 if l_old_header_id <> c1.list_header_id or l_old_line_id <> c1.list_line_id then
1310
1311 --oe_debug_pub.add('l_header_line_change_index, l_group_cnt_tbl.last: ' || l_header_line_change_index ||', '|| l_group_cnt_tbl.last);
1312
1313 for k in l_header_line_change_index..l_group_cnt_tbl.last loop
1314 l_others_group_cnt_tbl(k) := l_qual_cnt - l_null_grp_count;
1315 end loop;
1316
1317 If l_header_qual_exists_tbl.count >= 1000 then
1318 Forall K in l_header_qual_exists_tbl.First..l_header_qual_exists_tbl.Last
1319 update qp_qualifiers
1320 Set SEARCH_IND = L_Search_Ind_tbl(K),
1321 QUALIFIER_GROUP_CNT = l_group_cnt_tbl(K),
1322 -- qualifier_group_cnt = decode(qualifier_grouping_no, -1, 0, l_group_cnt_tbl(k)),
1323 HEADER_QUALS_EXIST_FLAG=l_header_qual_exists_tbl(K),
1324 others_group_cnt = l_others_group_cnt_tbl(k)
1325 Where rowid = l_rowid_tbl(K)
1326 and list_type_code not in ('PRL', 'AGR');
1327
1328 IF p_update_type IN ('BATCH','DENORMALIZED', 'ALL') THEN
1329 Commit;
1330 END IF;
1331
1332 l_rowid_tbl.delete;
1333 L_Search_Ind_tbl.delete;
1334 l_group_cnt_tbl.delete;
1335 l_others_group_cnt_tbl.delete;
1336 l_header_qual_exists_tbl.delete;
1337 End If; -- > 1000
1338 l_qual_cnt := 0;
1339 l_null_grp_count := 0;
1340 l_header_line_change_index := l_search_ind_tbl.count + 1;
1341
1342 end if; -- header or line changed
1343
1344 end If; -- l_group_cnt_tbl.count > 0
1345
1346
1347 l_group_cnt := 0;
1348 l_Grp_Start_Index := L_Search_Ind_tbl.count+1;
1349
1350 end If; -- The Line or header has changed or group_no changed
1351
1352 l_group_cnt := l_group_cnt +1;
1353 l_qual_cnt := l_qual_cnt + 1;
1354
1355 If c1.qualifier_grouping_no = -1 then
1356 l_null_grp_count := l_null_grp_count+1;
1357 end If;
1358
1359 if l_group_cnt = 1 then
1360 if c1.comparison_operator_code <> 'NOT =' then
1361 l_search_ind := 1;
1362 l_search_ind_1_set := 1;
1363 else
1364 l_search_ind := 2;
1365 end if;
1366 else
1367 if l_search_ind_1_set <> 1 and c1.comparison_operator_code <> 'NOT =' then
1368 l_search_ind := 1;
1369 l_search_ind_1_set := 1;
1370 else
1371 l_search_ind := 2;
1372 end if;
1373 end if;
1374
1375 l_rowid_tbl(l_rowid_tbl.count+1) := c1.rowid;
1376 L_Search_Ind_tbl(L_Search_Ind_tbl.count+1) := L_Search_Ind;
1377 l_group_cnt_tbl(l_group_cnt_tbl.count+1) := l_group_cnt;
1378 l_header_qual_exists_tbl(l_header_qual_exists_tbl.count+1) := l_header_qual_exists;
1379
1380 l_old_header_id := c1.list_header_id;
1381 l_old_line_id := c1.list_line_id;
1382 l_old_qualifier_grouping_no := c1.qualifier_grouping_no;
1383
1384 end loop;
1385
1386 --update the remaining groups
1387 if l_search_ind_1_set = 0 then
1388 l_search_ind_tbl(l_search_ind_tbl.count) := 1;
1389 end if;
1390 l_search_ind_1_set := 0;
1391
1392 IF (l_group_cnt_tbl.COUNT > 0 ) THEN
1393 For k in l_Grp_Start_Index..l_group_cnt_tbl.Last loop
1394
1395 If l_old_qualifier_grouping_no <> -1 then
1396 l_group_cnt_tbl(k) := l_group_cnt + l_null_grp_count;
1397 Else
1398 l_group_cnt_tbl(k) := l_group_cnt ;
1399 End If;
1400
1401 End Loop;
1402
1403 --oe_debug_pub.add('l_header_line_change_index, l_group_cnt_tbl.last: ' || l_header_line_change_index ||', '|| l_group_cnt_tbl.last);
1404
1405 for k in l_header_line_change_index..l_group_cnt_tbl.last loop
1406 l_others_group_cnt_tbl(k) := l_qual_cnt - l_null_grp_count;
1407 end loop;
1408
1409 If l_header_qual_exists_tbl.count > 0 then
1410 Forall K in l_header_qual_exists_tbl.First..l_header_qual_exists_tbl.Last
1411 update qp_qualifiers
1412 Set SEARCH_IND = L_Search_Ind_tbl(K),
1413 QUALIFIER_GROUP_CNT = l_group_cnt_tbl(K),
1414 -- qualifier_group_cnt = decode(qualifier_grouping_no, -1, 0, l_group_cnt_tbl(k)),
1415 HEADER_QUALS_EXIST_FLAG=l_header_qual_exists_tbl(K),
1416 others_group_cnt = l_others_group_cnt_tbl(k)
1417 Where rowid = l_rowid_tbl(K)
1418 and list_type_code not in ('PRL', 'AGR');
1419
1420 IF P_UPDATE_TYPE IN ('BATCH','DENORMALIZED', 'ALL', 'UPD_QUAL') THEN
1421 Commit;
1422 END IF;
1423
1424 /*
1425 For K in l_header_qual_exists_tbl.First..l_header_qual_exists_tbl.Last loop
1426 oe_debug_pub.add(' update: ' || l_rowid_tbl(K) ||', '|| l_group_cnt_tbl(K) ||', '|| l_others_group_cnt_tbl(K));
1427 end loop;
1428 */
1429
1430 l_rowid_tbl.delete;
1431 L_Search_Ind_tbl.delete;
1432 l_group_cnt_tbl.delete;
1433 l_others_group_cnt_tbl.delete;
1434 l_header_qual_exists_tbl.delete;
1435 End If;
1436
1437 END IF;
1438
1439 -- Update all the null grouping number rows to search ind 2 , if there is any other grp
1440 If l_null_header_id_tbl.count > 0 then
1441 Forall J in l_null_header_id_tbl.First..l_null_header_id_tbl.Last
1442 update qp_qualifiers
1443 set search_ind = 2
1444 -- qualifier_group_cnt = qualifier_group_cnt + 1 -- Arbitary increasing the ct by 1 for null grp -- dont care
1445 where nvl(qualifier_grouping_no,-1) = -1
1446 and list_header_id = l_null_header_id_tbl(J)
1447 and list_line_id = l_null_line_id_tbl(J);
1448
1449 IF P_UPDATE_TYPE IN ('BATCH' ,'DENORMALIZED', 'ALL')THEN
1450 commit;
1451 END IF;
1452 End If;
1453
1454 END IF;--p_update_type in 'BATCH', 'DENORMALIZED', 'ALL', 'DELAYED_REQ'
1455 -- set return status
1456 err_buff := '';
1457 retcode := 0;
1458
1459 IF P_UPDATE_TYPE IN ('ALL','DENORMALIZED','QUAL_IND') THEN
1460 put_line('Completed Update Denormalized columns ');
1461 END IF;
1462
1463 ELSE
1464 IF P_UPDATE_TYPE IN ('ALL','DENORMALIZED','QUAL_IND','HEADER PHASE') THEN
1465 put_line('Could not perform updates');
1466 put_line('At least one value of modifier list or pricelist must be entered');
1467 err_buff :='Could not perform updates , At least one value of modifier list or pricelist must be entered';
1468 retcode:=2;
1469 END IF;
1470 END IF;
1471
1472 -- This procedure update the qualifier flag to Y if all the lines of the header have line level qualifiers.
1473 -- set the flag to null for all records
1474 IF P_UPDATE_TYPE IN ('ALL','DENORMALIZED','QUAL_IND','HEADER PHASE','UPD_QUAL', 'BATCH') THEN --BATCH added for bug 5121471
1475 update qp_list_header_phases set qualifier_flag = NULL
1476 WHERE qualifier_flag is not null; --9732576
1477 for i in l_qp_list_header_phases_qual
1478 loop
1479 l_list_line_id := Null;
1480 open l_line_qual_exc(i.pricing_phase_id, i.list_header_id);
1481 fetch l_line_qual_exc into l_list_line_id;
1482 close l_line_qual_exc;
1483 if l_list_line_id is null then
1484 update qp_list_header_phases
1485 set qualifier_flag = 'Y'
1486 where list_header_id = i.list_header_id
1487 and pricing_phase_id = i.pricing_phase_id;
1488 end if;
1489 l_list_line_id := null;
1490 end loop;
1491 end if;
1492
1493
1494 EXCEPTION
1495 When Phase_Exception Then
1496 retcode := -1;
1497 WHEN OTHERS THEN
1498 IF P_UPDATE_TYPE IN ('ALL','DENORMALIZED','QUAL_IND','HEADER_PHASE', 'FACTOR') THEN
1499 put_line(substr(sqlerrm,1,300));
1500 END IF;
1501 err_buff := substr(sqlcode,1,50)||' : '||substr(sqlerrm,1,250);
1502 retcode := 2;
1503
1504 END Update_Qualifiers;
1505
1506 End QP_Maintain_Denormalized_Data;
[Home] [Help]