[Home] [Help]
PACKAGE BODY: APPS.QP_PS_ATTR_GRP_PVT
Source
1 PACKAGE BODY QP_PS_ATTR_GRP_PVT AS
2 /* $Header: QPXPATGB.pls 120.6 2011/07/12 10:11:26 dnema noship $ */
3
4 G_LINES_PER_INSERT CONSTANT NUMBER := 5000;
5
6 g_min_date DATE := TO_DATE ('01-01-1901','mm-dd-yyyy');
7 g_max_date DATE := TO_DATE ('12-31-9999','mm-dd-yyyy');
8 PROCEDURE update_pattern_phases (
9 p_list_header_id NUMBER,
10 p_min_list_line_id NUMBER,
11 p_max_list_line_id NUMBER
12 )
13 IS
14
15 CURSOR pattern_phases_dates_flags_all IS
16 SELECT pricing_phase_id, pattern_id,
17 DECODE(MIN(NVL(sda,g_min_date)),g_min_date,null,MIN(sda)) min_start_date,
18 DECODE(MAX(NVL(eda,g_max_date)),g_max_date,null,MAX(eda)) max_end_date,
19 NVL(MAX(active_flag),'N') active_flag
20 FROM
21 ( -- Modifier Lines
22 SELECT pricing_phase_id, pattern_id, START_DATE_ACTIVE_L sda,
23 END_DATE_ACTIVE_L eda, active_flag
24 FROM qp_attribute_groups
25 WHERE list_line_id <> -1
26 UNION ALL
27 -- Modifier Headers
28 SELECT /*+ ordered USE_NL(qplhp qpag) */
29 qplhp.pricing_phase_id, qpag.pattern_id,
30 qpag.START_DATE_ACTIVE_H sda, qpag.END_DATE_ACTIVE_H eda, qpag.active_flag
31 FROM qp_list_header_phases qplhp, qp_attribute_groups qpag
32 WHERE qpag.list_header_id = qplhp.list_header_id
33 AND qpag.list_line_id = -1
34 UNION ALL
35 -- Price List Lines
36 SELECT /*+ ordered USE_NL(qpl qph) */
37 qpl.pricing_phase_id, qpl.pattern_id, qpl.START_DATE_ACTIVE sda,
38 qpl.END_DATE_ACTIVE eda, qph.active_flag
39 FROM qp_list_lines qpl, qp_list_headers_all_b qph
40 WHERE pricing_phase_id = 1
41 AND qph.list_header_id = qpl.list_header_id
42 UNION ALL
43 -- Price List headers
44 SELECT pricing_phase_id, pattern_id,
45 START_DATE_ACTIVE_H sda, END_DATE_ACTIVE_H eda, active_flag
46 FROM qp_attribute_groups
47 WHERE pricing_phase_id = 1
48 AND list_line_id = -1
49 )b
50 GROUP BY pricing_phase_id, pattern_id;
51
52 CURSOR pat_phases_dates_flags_mod IS
53 SELECT pricing_phase_id, pattern_id,
54 DECODE(MIN(NVL(sda,g_min_date)),g_min_date,null,MIN(sda)) min_start_date,
55 DECODE(MAX(NVL(eda,g_max_date)),g_max_date,null,MAX(eda)) max_end_date,
56 NVL(MAX(active_flag),'N') active_flag
57 FROM
58 ( -- Modifier Lines
59 SELECT pricing_phase_id, pattern_id, START_DATE_ACTIVE_L sda,
60 END_DATE_ACTIVE_L eda, active_flag
61 FROM qp_attribute_groups
62 WHERE list_line_id <> -1
63 AND list_header_id = p_list_header_id
64 UNION ALL
65 -- Modifier Headers
66 SELECT /*+ ordered USE_NL(qplhp qpag) */
67 qplhp.pricing_phase_id, qpag.pattern_id,
68 qpag.START_DATE_ACTIVE_H sda, qpag.END_DATE_ACTIVE_H eda, qpag.active_flag
69 FROM qp_list_header_phases qplhp, qp_attribute_groups qpag
70 WHERE qplhp.list_header_id = p_list_header_id
71 AND qpag.list_header_id = qplhp.list_header_id
72 AND qpag.list_line_id = -1
73 )b
74 GROUP BY pricing_phase_id, pattern_id;
75
76
77 CURSOR pat_phases_dates_flags_prl IS
78 SELECT pricing_phase_id, pattern_id,
79 DECODE(MIN(NVL(sda,g_min_date)),g_min_date,null,MIN(sda)) min_start_date,
80 DECODE(MAX(NVL(eda,g_max_date)),g_max_date,null,MAX(eda)) max_end_date,
81 NVL(MAX(active_flag),'N') active_flag
82 FROM
83 (
84 -- Price List Lines
88 FROM qp_list_lines qpl, qp_list_headers_all_b qph
85 SELECT /*+ ordered USE_NL(qpl qph) index(qpl QP_LIST_LINES_N1) */
86 qpl.pricing_phase_id, qpl.pattern_id, qpl.START_DATE_ACTIVE sda,
87 qpl.END_DATE_ACTIVE eda, qph.active_flag
89 WHERE pricing_phase_id = 1
90 AND qpl.list_header_id = p_list_header_id --NVL(p_list_header_id, qpl.list_header_id)
91 AND qph.list_header_id = qpl.list_header_id
92 UNION ALL
93 -- Price List headers
94 SELECT pricing_phase_id, pattern_id,
95 START_DATE_ACTIVE_H sda, END_DATE_ACTIVE_H eda, active_flag
96 FROM qp_attribute_groups
97 WHERE pricing_phase_id = 1
98 AND list_header_id = p_list_header_id
99 AND list_line_id = -1
100 )b
101 GROUP BY pricing_phase_id, pattern_id;
102
103 CURSOR pat_phases_dates_flags_mod_all IS
104 SELECT pricing_phase_id, pattern_id,
105 DECODE(MIN(NVL(sda,g_min_date)),g_min_date,null,MIN(sda)) min_start_date,
106 DECODE(MAX(NVL(eda,g_max_date)),g_max_date,null,MAX(eda)) max_end_date,
107 NVL(MAX(active_flag),'N') active_flag
108 FROM
109 ( -- Modifier Lines
110 SELECT pricing_phase_id, pattern_id, START_DATE_ACTIVE_L sda,
111 END_DATE_ACTIVE_L eda, active_flag
112 FROM qp_attribute_groups
113 WHERE list_line_id <> -1
114 UNION ALL
115 -- Modifier Headers
116 SELECT /*+ ordered */
117 qplhp.pricing_phase_id, qpag.pattern_id,
118 qpag.START_DATE_ACTIVE_H sda, qpag.END_DATE_ACTIVE_H eda, qpag.active_flag
119 FROM qp_list_header_phases qplhp, qp_attribute_groups qpag
120 WHERE qpag.list_line_id = -1
121 AND qpag.list_header_id = qplhp.list_header_id
122 )b
123 GROUP BY pricing_phase_id, pattern_id;
124
125
126 CURSOR pat_phases_dates_flags_prl_all IS
127 SELECT pricing_phase_id, pattern_id,
128 DECODE(MIN(NVL(sda,g_min_date)),g_min_date,null,MIN(sda)) min_start_date,
129 DECODE(MAX(NVL(eda,g_max_date)),g_max_date,null,MAX(eda)) max_end_date,
130 NVL(MAX(active_flag),'N') active_flag
131 FROM
132 (
133 -- Price List Lines
134 SELECT /*+ ordered */
135 qpl.pricing_phase_id, qpl.pattern_id, qpl.START_DATE_ACTIVE sda,
136 qpl.END_DATE_ACTIVE eda, qph.active_flag
137 FROM qp_list_lines qpl, qp_list_headers_all_b qph
138 WHERE pricing_phase_id = 1
139 AND qph.list_header_id = qpl.list_header_id
140 UNION ALL
141 -- Price List headers
142 SELECT pricing_phase_id, pattern_id,
143 START_DATE_ACTIVE_H sda, END_DATE_ACTIVE_H eda, active_flag
144 FROM qp_attribute_groups
145 WHERE pricing_phase_id = 1
146 AND list_line_id = -1
147 )b
148 GROUP BY pricing_phase_id, pattern_id;
149
150
151 l_phase_ids_tbl number_tbl_type;
152 l_pattern_ids_tbl number_tbl_type;
153 l_start_date_tbl date_tbl_type;
154 l_end_date_tbl date_tbl_type;
155 l_active_flag_tbl varchar1_tbl_type;
156
157 --bug 9594320
158 /*
159 l_phase_id NUMBER := NULL;
160 l_pattern_id NUMBER := NULL;
161 l_start_date DATE := NULL;
162 l_end_date DATE := NULL;
163 l_active_flag VARCHAR2(1) := NULL;
164 */
165
166 l_old_start_date DATE := NULL;
167 l_old_end_date DATE := NULL;
168 l_old_active_flag VARCHAR2(1) := NULL;
169 l_routine VARCHAR2(240):='Routine : QP_PS_ATTR_GRP_PVT.update_pattern_phases';
170
171 BEGIN
172
173 IF p_min_list_line_id IS NOT NULL -- IF 1
174 AND p_min_list_line_id = p_max_list_line_id THEN
175
176 --bug 9594320 start
177 l_phase_ids_tbl.delete;
178 l_pattern_ids_tbl.delete;
179 l_start_date_tbl.delete;
180 l_end_date_tbl.delete;
181 l_active_flag_tbl.delete;
182 --bug 9594320 end
183
184 IF g_qp_pattern_search IN ('P','B') AND g_list_type IN ('PRL','AGR') THEN -- IF 1.1
185
186 SELECT qpl.pricing_phase_id,
187 qpl.pattern_id,
188 qpl.start_date_active,
189 qpl.end_date_active,
190 qph.active_flag
191 --bug 9594320 start
192 BULK COLLECT INTO
193 l_phase_ids_tbl,
194 l_pattern_ids_tbl,
195 l_start_date_tbl,
196 l_end_date_tbl,
197 l_active_flag_tbl
198 --bug 9594320 end
199 FROM qp_list_lines qpl, qp_list_headers_all_b qph
200 WHERE qpl.list_line_id = p_min_list_line_id
201 AND qph.list_header_id = qpl.list_header_id;
202
203 ELSIF g_qp_pattern_search IN ('M','B') AND g_list_type NOT IN ('PRL','AGR','PML') THEN -- IF 1.1
204
205 SELECT pricing_phase_id,
206 pattern_id,
207 start_date_active_l,
208 end_date_active_l,
209 active_flag
210 --bug 9594320 start
211 BULK COLLECT INTO
212 l_phase_ids_tbl,
213 l_pattern_ids_tbl,
214 l_start_date_tbl,
215 l_end_date_tbl,
216 l_active_flag_tbl
217 --bug 9594320 start
218 FROM qp_attribute_groups qpg
219 WHERE qpg.list_line_id = p_min_list_line_id;
220
221 END IF; -- IF 1.1
222
223 --bug 9594320 added loop
224 FOR i IN l_phase_ids_tbl.first..l_phase_ids_tbl.last LOOP
225
226 SELECT start_date_active,
227 end_date_active,
228 active_flag
229 INTO l_old_start_date,
230 l_old_end_date,
231 l_old_active_flag
232 FROM qp_pattern_phases
233 WHERE pricing_phase_id = l_phase_ids_tbl(i)
234 AND pattern_id = l_pattern_ids_tbl(i);
238 l_start_date_tbl(i) > l_old_start_date))
235
236 IF ((l_old_start_date IS NULL) OR
237 ( l_start_date_tbl(i) IS NOT NULL AND
239 THEN
240 l_start_date_tbl(i) := l_old_start_date;
241 END IF;
242
243
244 IF ((l_old_end_date IS NULL) OR
245 ( l_end_date_tbl(i) IS NOT NULL AND
246 l_end_date_tbl(i) < l_old_end_date))
247 THEN
248 l_end_date_tbl(i) := l_old_end_date;
249 END IF;
250
251 IF (l_old_active_flag = 'Y')
252 THEN
253 l_active_flag_tbl(i) := l_old_active_flag;
254 END IF;
255
256
257 UPDATE qp_pattern_phases
258 SET start_date_active = l_start_date_tbl(i),
259 end_date_active = l_end_date_tbl(i),
260 active_flag = l_active_flag_tbl(i)
261 WHERE pricing_phase_id = l_phase_ids_tbl(i)
262 AND pattern_id = l_pattern_ids_tbl(i);
263
264 END LOOP; --Bug 9594320
265
266 ELSE -- IF 1
267
268 IF p_list_header_id IS NULL THEN -- IF 2
269 IF g_qp_pattern_search = 'B' THEN -- IF 3
270
271 OPEN pattern_phases_dates_flags_all;
272
273 FETCH pattern_phases_dates_flags_all BULK COLLECT INTO
274 l_phase_ids_tbl,
275 l_pattern_ids_tbl,
276 l_start_date_tbl,
277 l_end_date_tbl,
278 l_active_flag_tbl;
279
280 CLOSE pattern_phases_dates_flags_all;
281
282 ELSIF g_qp_pattern_search = 'M' THEN -- IF 3
283
284 OPEN pat_phases_dates_flags_mod_all;
285
286 FETCH pat_phases_dates_flags_mod_all BULK COLLECT INTO
287 l_phase_ids_tbl,
288 l_pattern_ids_tbl,
289 l_start_date_tbl,
290 l_end_date_tbl,
291 l_active_flag_tbl;
292
293 CLOSE pat_phases_dates_flags_mod_all;
294
295 ELSIF g_qp_pattern_search = 'P' THEN -- IF 3
296
297 OPEN pat_phases_dates_flags_prl_all;
298
299 FETCH pat_phases_dates_flags_prl_all BULK COLLECT INTO
300 l_phase_ids_tbl,
301 l_pattern_ids_tbl,
302 l_start_date_tbl,
303 l_end_date_tbl,
304 l_active_flag_tbl;
305
306 CLOSE pat_phases_dates_flags_prl_all;
307
308 END IF; -- IF 3
309
310 FORALL i IN 1..l_phase_ids_tbl.COUNT
311 UPDATE qp_pattern_phases
312 SET start_date_active = l_start_date_tbl(i),
313 end_date_active = l_end_date_tbl(i),
314 active_flag = l_active_flag_tbl(i)
315 WHERE pricing_phase_id = l_phase_ids_tbl(i)
316 AND pattern_id = l_pattern_ids_tbl(i);
317
318 ELSE -- IF 2
319
320 IF g_qp_pattern_search IN ('P','B') AND g_list_type IN ('PRL','AGR') THEN -- IF 4
321
322 OPEN pat_phases_dates_flags_prl;
323
324 FETCH pat_phases_dates_flags_prl BULK COLLECT INTO
325 l_phase_ids_tbl,
326 l_pattern_ids_tbl,
327 l_start_date_tbl,
328 l_end_date_tbl,
329 l_active_flag_tbl;
330
331 CLOSE pat_phases_dates_flags_prl;
332
333 ELSIF g_qp_pattern_search IN ('M','B') AND g_list_type NOT IN ('PRL','AGR','PML') THEN -- IF 4
334
335 OPEN pat_phases_dates_flags_mod;
336
337 FETCH pat_phases_dates_flags_mod BULK COLLECT INTO
338 l_phase_ids_tbl,
339 l_pattern_ids_tbl,
340 l_start_date_tbl,
341 l_end_date_tbl,
342 l_active_flag_tbl;
343
344 CLOSE pat_phases_dates_flags_mod;
345
346 END IF; -- IF 4
347
348 FORALL i IN 1..l_phase_ids_tbl.COUNT
349 UPDATE qp_pattern_phases
350 SET start_date_active = l_start_date_tbl(i)
351 WHERE pricing_phase_id = l_phase_ids_tbl(i)
352 AND pattern_id = l_pattern_ids_tbl(i)
353 AND ((l_start_date_tbl(i) IS NULL )
354 OR
355 (start_date_active IS NOT NULL AND
356 start_date_active > l_start_date_tbl(i)
357 ));
358
359 FORALL i IN 1..l_phase_ids_tbl.COUNT
360 UPDATE qp_pattern_phases
361 SET end_date_active = l_end_date_tbl(i)
362 WHERE pricing_phase_id = l_phase_ids_tbl(i)
363 AND pattern_id = l_pattern_ids_tbl(i)
364 AND ((l_end_date_tbl(i) IS NULL )
365 OR
366 (end_date_active IS NOT NULL AND
367 end_date_active < l_end_date_tbl(i)
368 ));
369
370 FORALL i IN 1..l_phase_ids_tbl.COUNT
371 UPDATE qp_pattern_phases
372 SET active_flag = NVL(l_active_flag_tbl(i),'N')
373 WHERE pricing_phase_id = l_phase_ids_tbl(i)
374 AND pattern_id = l_pattern_ids_tbl(i)
375 AND NVL(active_flag,'N') <> 'Y';
376
377
378 END IF; -- IF 2
379
380
381 END if; -- IF 1
382 EXCEPTION
383 WHEN OTHERS THEN
384 IF pattern_phases_dates_flags_all%ISOPEN THEN
385 CLOSE pattern_phases_dates_flags_all;
386 END if;
387 IF pat_phases_dates_flags_mod%ISOPEN THEN
388 CLOSE pat_phases_dates_flags_mod;
389 END if;
390 IF pat_phases_dates_flags_prl%ISOPEN THEN
391 CLOSE pat_phases_dates_flags_prl;
392 END if;
393 IF pat_phases_dates_flags_mod_all%ISOPEN THEN
394 CLOSE pat_phases_dates_flags_mod_all;
395 END if;
396 IF pat_phases_dates_flags_prl_all%ISOPEN THEN
397 CLOSE pat_phases_dates_flags_prl_all;
401 oe_debug_pub.add('QP_PS_ATTR_GRP_PVT.update_pattern_phases ' || SQLERRM);
398 END if;
399
400 if g_call_from_setup = 'Y' THEN
402 else
403 write_log( 'QP_PS_ATTR_GRP_PVT.update_pattern_phases ' || SQLERRM );
404 end if;
405
406 END update_pattern_phases;
407
408 procedure create_pattern_slabs(
409 p_total_lines IN number,
410 p_list_header_id IN number default null,
411 p_no_of_threads IN NUMBER default 1)
412 is
413 cursor list_line is
414 select list_line_id from qp_list_lines where qualification_ind in (8,10,12,14,28,30, 4,6,20,22,2,0)
415 and list_header_id = nvl(p_list_header_id, list_header_id)
416 order by list_line_id;
417
418 l_gap number := 0;
419 l_list_line_id number := 0;
420 l_counter number := 0;
421 l_min_line number := 0;
422 l_max_line number := 0;
423 l_worker_count number := 0;
424 l_start_flag number := 0;
425 l_no_of_threads number := 0;
426
427 begin
428
429 if g_call_from_setup = 'Y' then
430 oe_debug_pub.add('Begin create pattern slabs');
431 else
432 write_log( 'Begin create pattern slabs');
433 write_log( 'Start time :'||to_char(sysdate,'MM/DD/YYYY:HH:MI:SSSS'));
434 end if;
435 l_no_of_threads := p_no_of_threads;
436 if l_no_of_threads > p_total_lines then
437 l_no_of_threads := p_total_lines;
438 end if;
439 l_gap := round(p_total_lines / l_no_of_threads, 0);
440
441 if g_call_from_setup = 'Y' then
442 oe_debug_pub.add('Total lines:'||p_total_lines);
443 oe_debug_pub.add('l_gap:'||l_gap);
444 else
445 write_log( 'Total lines:'||p_total_lines);
446 write_log( 'l_gap:'||l_gap);
447 end if;
448 if p_total_lines > 0 then
449 for line_rec in list_line loop
450
451 l_list_line_id := line_rec.list_line_id;
452 l_counter := l_counter + 1;
453
454 if l_start_flag = 0 then
455 l_start_flag := 1;
456 l_min_line := line_rec.list_line_id;
457 l_max_line := NULL;
458 l_worker_count := l_worker_count + 1;
459 end if;
460
461 if l_counter = l_gap and l_worker_count < l_no_of_threads
462 then
463 l_max_line := line_rec.list_line_id;
464
465 -- add l_worker_count, l_min_line, l_max_line into a pl/sql table
466 g_pattern_upg_slab_table(l_worker_count).worker := l_worker_count;
467 g_pattern_upg_slab_table(l_worker_count).low_list_line_id := l_min_line;
468 g_pattern_upg_slab_table(l_worker_count).high_list_line_id := l_max_line;
469
470 l_counter := 0;
471 l_start_flag := 0;
472
473 end if;
474
475 end loop;
476 l_max_line := l_list_line_id;
477 -- add l_worker_count, l_min_line, l_max_line into a pl/sql table
478 g_pattern_upg_slab_table(l_worker_count).worker := l_worker_count;
479 g_pattern_upg_slab_table(l_worker_count).low_list_line_id := l_min_line;
480 g_pattern_upg_slab_table(l_worker_count).high_list_line_id := l_max_line;
481
482 end if;
483 write_log( 'End create pattern slabs');
484 write_log( 'End time :'||to_char(sysdate,'MM/DD/YYYY:HH:MI:SSSS'));
485
486 end create_pattern_slabs;
487 /********************************************/
488 procedure create_pattern_chunks(
489 p_low_list_line_id IN NUMBER default null,
490 p_high_list_line_id IN NUMBER default null)
491 is
492 cursor list_line is
493 select list_line_id from qp_list_lines where qualification_ind in (8,10,12,14,28,30, 4,6,20,22,2,0)
494 and list_line_id BETWEEN p_low_list_line_id AND p_high_list_line_id
495 order by list_line_id;
496
497 l_gap number := 5000;
498 l_list_line_id number := 0;
499 l_counter number := 0;
500 l_min_line number := 0;
501 l_max_line number := 0;
502 l_worker_count number := 0;
503 l_start_flag number := 0;
504 l_no_of_threads number := 0;
505 begin
506 if g_call_from_setup = 'Y' then
507 oe_debug_pub.add('Begin create pattern chunks');
508 else
509 write_log( 'Begin create pattern chunks');
510 write_log( 'Start time :'||to_char(sysdate,'MM/DD/YYYY:HH:MI:SSSS'));
511 end if;
512 for line_rec in list_line loop
513
514 l_list_line_id := line_rec.list_line_id;
515 l_counter := l_counter + 1;
516
517 if l_start_flag = 0 then
518 l_start_flag := 1;
519 l_min_line := line_rec.list_line_id;
520 l_max_line := NULL;
521 l_worker_count := l_worker_count + 1;
522 end if;
523
524 if l_counter = l_gap
525 then
526 l_max_line := line_rec.list_line_id;
527
528 -- add l_worker_count, l_min_line, l_max_line into a pl/sql table
529 g_pattern_upg_chunk_table(l_worker_count).worker := l_worker_count;
530 g_pattern_upg_chunk_table(l_worker_count).low_list_line_id := l_min_line;
531 g_pattern_upg_chunk_table(l_worker_count).high_list_line_id := l_max_line;
532
533 l_counter := 0;
534 l_start_flag := 0;
535 end if;
536
537 end loop;
538 l_max_line := l_list_line_id;
539 -- add l_worker_count, l_min_line, l_max_line into a pl/sql table
540 g_pattern_upg_chunk_table(l_worker_count).worker := l_worker_count;
541 g_pattern_upg_chunk_table(l_worker_count).low_list_line_id := l_min_line;
542 g_pattern_upg_chunk_table(l_worker_count).high_list_line_id := l_max_line;
543
544 write_log( 'End create pattern chunk');
548 /********************************************/
545 write_log( 'End time :'||to_char(sysdate,'MM/DD/YYYY:HH:MI:SSSS'));
546
547 end create_pattern_chunks;
549 procedure remove_duplicate_patterns
550 is
551
552 l_min_pattern_id number;
553
554 cursor c_dupl_pattern is
555 select distinct b.pattern_type, b.pattern_string, b.pattern_id
556 from qp_patterns a, qp_patterns b
557 where a.pattern_type = b.pattern_type
558 and a.pattern_string = b.pattern_string
559 and a.pattern_id <> b.pattern_id
560 and b.pattern_id >(select min(c.pattern_id)
561 from qp_patterns c
562 where c.pattern_type = a.pattern_type
563 and c.pattern_string = a.pattern_string);
564 begin
565
566 write_log( 'In Remove_Duplicate_Patterns');
567 write_log( 'Start time :'||to_char(sysdate,'MM/DD/YYYY:HH:MI:SSSS'));
568
569 g_pattern_pattern_id_final_tbl.delete;
570 g_pattern_pat_type_final_tbl.delete;
571 g_pattern_pat_string_final_tbl.delete;
572
573 open c_dupl_pattern;
574
575 FETCH c_dupl_pattern BULK COLLECT INTO
576 g_pattern_pat_type_final_tbl,
577 g_pattern_pat_string_final_tbl,
578 g_pattern_pattern_id_final_tbl;
579 CLOSE c_dupl_pattern;
580
581 write_log( 'No of Duplicate_Patterns='||g_pattern_pattern_id_final_tbl.count);
582 if g_pattern_pattern_id_final_tbl.count > 0 then
583 for i in 1..g_pattern_pattern_id_final_tbl.count
584 loop
585 select min(c.pattern_id)
586 into l_min_pattern_id
587 from qp_patterns c
588 where c.pattern_type = g_pattern_pat_type_final_tbl(i)
589 and c.pattern_string = g_pattern_pat_string_final_tbl(i);
590 IF g_qp_pattern_search = 'M' THEN
591 update qp_attribute_groups
592 set pattern_id = l_min_pattern_id
593 where pattern_id = g_pattern_pattern_id_final_tbl(i);
594 ELSIF g_pattern_pat_type_final_tbl(i) = 'PP' then
595 update /*+ index(lines QP_LIST_LINES_N9) */ qp_list_lines lines
596 set pattern_id = l_min_pattern_id
597 where pattern_id = g_pattern_pattern_id_final_tbl(i);
598 else
599 update qp_attribute_groups
600 set pattern_id = l_min_pattern_id
601 where pattern_id = g_pattern_pattern_id_final_tbl(i);
602 end if;
603
604 -- delete the records where pricing_phase_id matches to avoid duplicates
605 delete from qp_pattern_phases a
606 where a.pattern_id = g_pattern_pattern_id_final_tbl(i)
607 and a.pricing_phase_id in (select b.pricing_phase_id
608 from qp_pattern_phases b
609 where b.pattern_id = l_min_pattern_id);
610
611 -- update the records where pricing_phase_id DO NOT matches
612 update qp_pattern_phases
613 set pattern_id = l_min_pattern_id
614 where pattern_id = g_pattern_pattern_id_final_tbl(i);
615 end loop;
616 end if;
617
618 FORALL i in 1 .. G_pattern_pattern_id_final_tbl.count
619 DELETE qp_patterns
620 where pattern_id = g_pattern_pattern_id_final_tbl(i);
621
622 g_pattern_pattern_id_final_tbl.delete;
623 g_pattern_pat_type_final_tbl.delete;
624 g_pattern_pat_string_final_tbl.delete;
625
626 ----Added for PL/SQL Pattern Search
627 update qp_pattern_phases a
628 set (start_date_active, end_date_active, active_flag) =
629 (
630 select decode(min(nvl(sda,g_min_date)),g_min_date,null,min(sda)) min_start_date,
631 decode(max(nvl(eda,g_max_date)),g_max_date,null,max(eda)) max_end_date,
632 nvl(max(active_flag),'N') active_flag
633 from
634 ( -- Modifier Lines
635 select pricing_phase_id, pattern_id, START_DATE_ACTIVE_L sda, END_DATE_ACTIVE_L eda, active_flag
636 from qp_attribute_groups
637 where list_line_id <> -1
638 UNION ALL
639 -- Modifier Headers
640 select /*+ ordered */ qplhp.pricing_phase_id, qpag.pattern_id, qpag.START_DATE_ACTIVE_H sda,
641 qpag.END_DATE_ACTIVE_H eda, qpag.active_flag
642 from qp_list_header_phases qplhp, qp_attribute_groups qpag
643 where qpag.list_header_id = qplhp.list_header_id
644 and qpag.list_line_id = -1
645 union all
646 -- Price List Lines
647 select /*+ ordered */ qpl.pricing_phase_id, qpl.pattern_id, qpl.START_DATE_ACTIVE sda,
648 qpl.END_DATE_ACTIVE eda, qph.active_flag
649 from qp_list_lines qpl, qp_list_headers_all_b qph
650 where pricing_phase_id = 1
651 and qph.list_header_id = qpl.list_header_id
652 union ALL
653 -- Price List headers
654 select pricing_phase_id, pattern_id, START_DATE_ACTIVE_H sda,
655 END_DATE_ACTIVE_H eda, active_flag
656 from qp_attribute_groups
657 where pricing_phase_id = 1
658 and list_line_id = -1
659 ) b
660 where b.pricing_phase_id = a.pricing_phase_id
661 and b.pattern_id = a.pattern_id
662 --group by pricing_phase_id, pattern_id
663 );
664
665 ----Added for PL/SQL Pattern Search
666
667 write_log( 'End time :'||to_char(sysdate,'MM/DD/YYYY:HH:MI:SSSS'));
668 write_log( 'COMMIT in remove_duplicate_patterns');
669 commit;
670
671 EXCEPTION
672 WHEN OTHERS THEN
673 if g_call_from_setup = 'Y' then
674 oe_debug_pub.add('PS_ATTR_GRP_PVT.Remove_Duplicate_Patterns ' || SQLERRM);
675 else
676 write_log( 'PS_ATTR_GRP_PVT.Remove_Duplicate_Patterns ' || SQLERRM );
680
677 end if;
678
679 end remove_duplicate_patterns;
681 PROCEDURE Pattern_Upgrade (
682 err_buff out NOCOPY VARCHAR2,
683 retcode out NOCOPY NUMBER,
684 p_list_header_id IN number default null,
685 p_low_list_line_id IN NUMBER default null,
686 p_high_list_line_id IN NUMBER default null,
687 p_no_of_threads IN NUMBER default 1,
688 p_spawned_request IN VARCHAR2 default 'N',
689 p_debug IN VARCHAR2)
690 is
691 l_slab_count NUMBER;
692 l_chunk_count NUMBER;
693 l_count NUMBER;
694 l_new_request_id NUMBER;
695 l_no_of_threads NUMBER:=p_no_of_threads;
696 l_req_data VARCHAR2(10);
697 l_total_lines number := 0;
698
699 l_start_time number;
700 l_end_time number;
701 v_sid number;
702
703 l_qp_schema VARCHAR2(30);
704 l_stmt VARCHAR2(200);
705 l_status VARCHAR2(30);
706 l_industry VARCHAR2(30);
707
708 BEGIN
709 G_QP_DEBUG := p_debug;
710 -- Check QP_PATTERN_SEARCH Installed profile
711 IF FND_PROFILE.VALUE('QP_PATTERN_SEARCH') IS NULL THEN
712 g_qp_pattern_search := 'B';
713 ELSE
714 g_qp_pattern_search := FND_PROFILE.VALUE('QP_PATTERN_SEARCH');
715 END IF;
716
717 IF p_list_header_id IS NOT NULL THEN
718 SELECT list_type_code INTO g_list_type FROM qp_list_headers WHERE list_header_id = p_list_header_id;
719 ELSIF g_qp_pattern_search = 'P' THEN
720 g_list_type := 'PRL';
721 ELSE
722 g_list_type := 'JP'; --- 'JP' is a dummy value
723 END IF;
724
725 -- IF g_qp_pattern_search = 'Y' THEN
726 IF (g_qp_pattern_search = 'P' AND g_list_type IN ('PRL','AGR')) OR (g_qp_pattern_search = 'M' AND g_list_type NOT IN ('PRL','AGR','PML')) OR g_qp_pattern_search = 'B' then
727 write_log( 'Pattern search initiated');
728 write_log( 'p_list_header_id ' || p_list_header_id);
729 write_log( 'p_low_list_line_id ' || p_low_list_line_id);
730 write_log( 'p_high_list_line_id ' || p_high_list_line_id);
731 write_log( 'p_no_of_threads ' || p_no_of_threads);
732 write_log( 'p_spawned_request ' || p_spawned_request);
733
734 select sid into v_sid from v$session where audsid = userenv('SESSIONID');
735
736 write_log( 'session ID = ' || v_sid);
737 ELSE
738 write_log( 'Patterns are not created because');
739 write_log( 'QP: Pattern Search Profile is set to Pattern Search Off');
740 return;
741 end if;
742
743 --refresh the pattern data
744 if p_spawned_request = 'N' then
745 if p_no_of_threads is NULL or p_no_of_threads = 0 then
746 l_no_of_threads := 1;
747 end if;
748 l_req_data := fnd_conc_global.request_data;
749 write_log( 'l_req_data : ' || l_req_data);
750 if l_req_data is not NULL then
751 remove_duplicate_patterns;
752 --- updation of used in pattern
753 --Added for PL/SQL Pattern Search
754 select hsecs into l_end_time from v$timer;
755 write_log( 'Start time :'||to_char(sysdate,'MM/DD/YYYY:HH:MI:SSSS'));
756 IF p_list_header_id IS NULL THEN
757 update qp_pte_segments
758 set used_in_search ='Y'
759 where NVL(used_in_search,'N') ='N'
760 and segment_id in
761 ( select DISTINCT segment_id
762 from qp_patterns );
763 update qp_pte_segments
764 set used_in_search ='N'
765 where NVL(used_in_search,'Y') ='Y'
766 and segment_id not in
767 ( select DISTINCT segment_id
768 from qp_patterns );
769 UPDATE /*+ index (QP_PTE_SEGMENTS QP_PTE_SEGMENTS_U2) */ qp_pte_segments
770 SET used_in_search = 'Y'
771 WHERE NVL(used_in_search,'N') = 'N'
772 AND segment_id in
773 (select pricing_segment_id
774 from qp_pricing_attributes
775 where /*list_line_id in (
776 select list_line_id from qp_attribute_groups where eq_flag = 'N'
777 UNION
778 select list_line_id from qp_list_lines where eq_flag = 'N')
779 and */
780 --list_header_id = nvl(p_list_header_id, list_header_id)
781 comparison_operator_code <> '='
782 and pricing_segment_id is not NULL
783 UNION all
784 select segment_id
785 from qp_qualifiers
786 where /*list_line_id in (
787 select list_line_id from qp_attribute_groups where eq_flag = 'N'
788 UNION
789 select list_line_id from qp_list_lines where eq_flag = 'N')
790 and */
791 --list_header_id = nvl(p_list_header_id, list_header_id)
792 comparison_operator_code <> '='
793 and segment_id is not null);
794 ELSE
795 UPDATE /*+ index (QP_PTE_SEGMENTS QP_PTE_SEGMENTS_U2) */ qp_pte_segments
796 SET used_in_search = 'Y'
797 WHERE NVL(used_in_search,'N') = 'N'
798 AND segment_id in
799 (select pricing_segment_id
800 from qp_pricing_attributes
801 where /*list_line_id in (
802 select list_line_id from qp_attribute_groups where eq_flag = 'N'
803 UNION
804 select list_line_id from qp_list_lines where eq_flag = 'N')
805 and */
806 list_header_id = p_list_header_id
807 --AND comparison_operator_code <> '='
808 and pricing_segment_id is not NULL
809 UNION all
810 select segment_id
811 from qp_qualifiers
812 where /*list_line_id in (
813 select list_line_id from qp_attribute_groups where eq_flag = 'N'
814 UNION
815 select list_line_id from qp_list_lines where eq_flag = 'N')
816 and */
817 list_header_id = p_list_header_id
818 --AND comparison_operator_code <> '='
819 and segment_id is not null);
820 END IF;
821 update_pattern_phases(p_list_header_id,p_low_list_line_id,p_high_list_line_id);
825 IF p_list_header_id IS NULL THEN
822 update qp_patterns a set segment_count = (select count(segment_id) from qp_patterns b where a.pattern_id = b.pattern_id);
823 /*Populate row in qp_list_heder_phases when no qualifer is
824 present for lines or headers for that phase and will poulate 'Y' for PRIC_PROD_ATTR_ONLY_FLAG*/
826 insert into qp_list_header_phases (list_header_id,pricing_phase_id,PRIC_PROD_ATTR_ONLY_FLAG)
827 (select distinct list_header_id, pricing_phase_id,'Y'
828 from qp_list_lines
829 where pricing_phase_id > 1
830 and qualification_ind in (0,4,20)
831 MINUS
832 SELECT distinct list_header_id, pricing_phase_id,'Y'
833 FROM qp_list_header_phases
834 );
835
836 -- bug 12731268
837 UPDATE qp_list_lines
838 SET pattern_id=NULL
839 WHERE pattern_id IN (-2,-3)
840 AND list_line_type_code IN ('PLL','PBH');
841
842 ELSE
843 insert into qp_list_header_phases (list_header_id,pricing_phase_id,PRIC_PROD_ATTR_ONLY_FLAG)
844 (select distinct list_header_id, pricing_phase_id,'Y'
845 from qp_list_lines
846 where pricing_phase_id > 1
847 and qualification_ind in (0,4,20)
848 and list_header_id = nvl(p_list_header_id, list_header_id)
849 MINUS
850 SELECT distinct list_header_id, pricing_phase_id,'Y'
851 FROM qp_list_header_phases
852 where list_header_id = nvl(p_list_header_id, list_header_id)
853 );
854
855 -- bug 12731268
856 UPDATE qp_list_lines
857 SET pattern_id=NULL
858 WHERE pattern_id IN (-2,-3)
859 AND list_line_type_code IN ('PLL','PBH')
860 AND list_header_id = p_list_header_id;
861
862 END IF;
863 ----Added for PL/SQL Pattern Search
864
865 -- bug 12731268
866 /*DELETE qp_pattern_phases
867 where pattern_id IN (-2,-3)
868 AND pricing_phase_id=1;*/
869
870 select hsecs into l_end_time from v$timer;
871 write_log( 'End time :'||to_char(sysdate,'MM/DD/YYYY:HH:MI:SSSS'));
872 write_log( 'Time taken for the Update process (sec):' ||(l_end_time - l_start_time)/100);
873 return;
874 end if;
875
876 select hsecs into l_start_time from v$timer;
877 write_log( 'Start time :'||to_char(sysdate,'MM/DD/YYYY:HH:MI:SSSS'));
878
879 if p_list_header_id is null then
880 -- like upgrade, refresh everything related to patterns
881 IF (FND_INSTALLATION.GET_APP_INFO('QP', l_status, l_industry, l_qp_schema))
882 THEN
883
884 l_stmt := 'TRUNCATE TABLE ' || l_qp_schema || '.qp_pattern_phases';
885 EXECUTE IMMEDIATE l_stmt;
886 l_stmt := 'TRUNCATE TABLE ' || l_qp_schema || '.qp_attribute_groups';
887 EXECUTE IMMEDIATE l_stmt;
888 l_stmt := 'TRUNCATE TABLE ' || l_qp_schema || '.qp_patterns';
889 EXECUTE IMMEDIATE l_stmt;
890
891 END IF;
892
893 write_log( 'Deleted all records from 3 Pattern Master tables');
894 else
895 -- refresh only for the passed list_header_id
896 delete from qp_attribute_groups
897 where list_header_id = p_list_header_id
898 and list_line_id = -1;
899 write_log( 'Deleted records from qp_attribute_groups for HP for list_header_id:'||p_list_header_id);
900
901 end if;
902 Update_Qual_Segment_id(p_list_header_id, null, -1, -1);
903 generate_hp_atgrps(p_list_header_id, null);
904
905 g_pattern_upg_slab_table.delete;
906 IF p_list_header_id IS NULL THEN
907 select count(*)
908 into l_total_lines
909 from qp_list_lines
910 where qualification_ind in (8,10,12,14,28,30, 4,6,20,22,2,0);
911 ELSE
912 select count(*)
913 into l_total_lines
914 from qp_list_lines
915 where qualification_ind in (8,10,12,14,28,30, 4,6,20,22,2,0)
916 and list_header_id = p_list_header_id;
917 END IF;
918 write_log( 'l_total_lines ' || l_total_lines);
919 if l_total_lines > 0 then
920 create_pattern_slabs(l_total_lines, p_list_header_id, l_no_of_threads);
921 l_slab_count := g_pattern_upg_slab_table.count;
922 write_log( 'l_slab_count ' || l_slab_count);
923 l_count := 1;
924 loop
925 l_new_request_id := fnd_request.submit_request(
926 'QP',
927 'QPXVATG',
928 'Pattern Upgrade '||to_char(l_count),
929 NULL,
930 TRUE,
931 p_list_header_id,
932 g_pattern_upg_slab_table(l_count).low_list_line_id,
933 g_pattern_upg_slab_table(l_count).high_list_line_id,
934 1,
935 'Y',
936 G_QP_DEBUG);
937 if l_new_request_id = 0 then
938 retcode := 2;
939 err_buff := fnd_message.get;
940 write_log( 'err_buff ' || err_buff);
941 return;
942 end if;
943 write_log('Child '||l_count||' request_id: '||l_new_request_id);
944
945 l_count := l_count + 1;
946 exit when l_count > l_slab_count;
947 end loop;
948
949 select hsecs into l_end_time from v$timer;
950 write_log( 'End time :'||to_char(sysdate,'MM/DD/YYYY:HH:MI:SSSS'));
951 write_log( 'Time taken for the header process (sec):' ||(l_end_time - l_start_time)/100);
952
953 fnd_conc_global.set_req_globals(conc_status => 'PAUSED',
954 request_data => to_char(l_count));
955
956 write_log( 'Time after parent request PAUSE over :'||to_char(sysdate,'MM/DD/YYYY:HH:MI:SSSS'));
957
958 else -- if l_total_lines > 0
959 if p_list_header_id IS not null then
960 -- like upgrade, refresh everything related to patterns
961 /* update qp_list_lines
962 set pattern_id = null,
966 cache_key = null;
963 pricing_attribute_count = null,
964 product_uom_code = null,
965 hash_key = null,
967 --where cache_key is not null;
968
969 else
970 -- refresh only for the passed list_header_id */
971
972 delete from qp_attribute_groups
973 where list_header_id = p_list_header_id
974 and list_line_id <> -1;
975
976 /* update qp_list_lines
977 set pattern_id = null,
978 pricing_attribute_count = null,
979 product_uom_code = null,
980 hash_key = null,
981 cache_key = null
982 where --cache_key is not null
983 list_header_id = p_list_header_id;*/
984 end if;
985
986 end if; -- if l_total_lines > 0
987 end if; -- if p_spawned_request = 'N'
988
989 if p_spawned_request = 'Y' then
990 select hsecs into l_start_time from v$timer;
991 write_log( 'Start time :'||to_char(sysdate,'MM/DD/YYYY:HH:MI:SSSS'));
992 if p_list_header_id is NOT null then
993 -- like upgrade, refresh everything related to patterns
994 -- IF g_qp_pattern_search <> 'M' THEN
995 /* update /*+ index_asc(lines QP_LIST_LINES_PK) */ /*qp_list_lines lines
996 set pattern_id = null,
997 pricing_attribute_count = null,
998 product_uom_code = null,
999 hash_key = null,
1000 cache_key = null
1001 where --cache_key is not null
1002 list_line_id between p_low_list_line_id and p_high_list_line_id; */
1003 -- END IF;
1004 -- else
1005 -- refresh only for the passed list_header_id
1006
1007 delete from qp_attribute_groups
1008 where list_header_id = p_list_header_id
1009 and list_line_id between p_low_list_line_id and p_high_list_line_id;
1010 --IF g_qp_pattern_search <> 'M' THEN
1011 /*update /*+ index_asc(lines QP_LIST_LINES_N18) *//* qp_list_lines lines
1012 set pattern_id = null,
1013 pricing_attribute_count = null,
1014 product_uom_code = null,
1015 hash_key = null,
1016 cache_key = null
1017 where --cache_key is not null
1018 list_header_id = p_list_header_id
1019 and list_line_id between p_low_list_line_id and p_high_list_line_id;*/
1020 -- END IF;
1021 end if;
1022 g_pattern_upg_chunk_table.delete;
1023 create_pattern_chunks(p_low_list_line_id,p_high_list_line_id);
1024 l_chunk_count := g_pattern_upg_chunk_table.count;
1025 write_log( 'l_chunk_count ' || l_chunk_count);
1026 l_count := 1;
1027 loop
1028 -- update the segment_id columns for qualifiers
1029 Update_Qual_Segment_id(p_list_header_id, null,
1030 g_pattern_upg_chunk_table(l_count).low_list_line_id,
1031 g_pattern_upg_chunk_table(l_count).high_list_line_id);
1032
1033 -- update the product_segment_id and pricing_segment_id columns in
1034 -- qp_pricing_attributes
1035 Update_Prod_Pric_Segment_id(p_list_header_id,
1036 g_pattern_upg_chunk_table(l_count).low_list_line_id,
1037 g_pattern_upg_chunk_table(l_count).high_list_line_id);
1038
1039 generate_lp_atgrps(p_list_header_id, null,
1040 g_pattern_upg_chunk_table(l_count).low_list_line_id,
1041 g_pattern_upg_chunk_table(l_count).high_list_line_id);
1042 update_pp_lines(p_list_header_id,
1043 g_pattern_upg_chunk_table(l_count).low_list_line_id,
1044 g_pattern_upg_chunk_table(l_count).high_list_line_id);
1045 write_log('Child '||l_count);
1046 l_count := l_count + 1;
1047 exit when l_count > l_chunk_count;
1048 end loop;
1049 select hsecs into l_end_time from v$timer;
1050 write_log( 'End time :'||to_char(sysdate,'MM/DD/YYYY:HH:MI:SSSS'));
1051 write_log( 'Time taken for the line process (sec):' ||(l_end_time - l_start_time)/100);
1052 end if; -- if p_spawned_request = 'Y'
1053
1054 -- commit the changes done so far
1055 write_log( 'COMMIT in Pattern_Upgrade');
1056 select hsecs into l_start_time from v$timer;
1057 write_log( 'Start time :'||to_char(sysdate,'MM/DD/YYYY:HH:MI:SSSS'));
1058 commit;
1059 select hsecs into l_end_time from v$timer;
1060 write_log( 'End time :'||to_char(sysdate,'MM/DD/YYYY:HH:MI:SSSS'));
1061 write_log( 'Time taken for the Commit process (sec):' ||(l_end_time - l_start_time)/100);
1062 err_buff := '';
1063 retcode := 0;
1064 exception
1065 when others then
1066 if g_call_from_setup = 'Y' then
1067 oe_debug_pub.add('PS_ATTR_GRP_PVT.Pattern_Upgrade ' || SQLERRM);
1068 else
1069 write_log( 'PS_ATTR_GRP_PVT.Pattern_Upgrade ' || SQLERRM );
1070 end if;
1071 err_buff := 'Others Error in procedure Pattern_Upgrade';
1072 retcode := 1;
1073
1074 end Pattern_Upgrade;
1075
1076
1077 procedure generate_hp_atgrps(p_list_header_id number
1078 ,p_qualifier_group number)
1079 is
1080 cursor c_attr_grp_hq_csr is
1081 SELECT * FROM (
1082 select qpq.list_header_id,
1083 qpq.list_line_id,
1084 qpq.segment_id,
1085 qph.active_flag, --bug#11927380
1086 qpq.list_type_code,
1087 qpq.start_date_active start_date_active_q,
1088 qpq.end_date_active end_date_active_q,
1089 ---Added for PL/SQL Pattern Search
1090 qpq.header_quals_exist_flag,
1091 qph.orig_org_id,
1092 qph.global_flag,
1093 null product_uom_code,
1094 qph.start_date_active_first,
1095 qph.end_date_active_first,
1096 qph.start_date_active_second,
1097 qph.end_date_active_second,
1098 qph.start_date_active start_date_active_h,
1099 qph.end_date_active end_date_active_h,
1100 qph.active_date_first_type,
1101 qph.active_date_second_type,
1102 qph.currency_header_id,
1103 qpq.qualify_hier_descendents_flag,
1104 NULL,
1105 NULL list_line_type_code,
1106 qph.automatic_flag,
1107 ----Added for PL/SQL Pattern Search
1108 qph.currency_code,
1112 qph.source_system_code,
1109 qph.ask_for_flag,
1110 qph.limit_exists_flag header_limit_exists,
1111 NULL line_limit_exists,
1113 qph.pte_code,
1114 qpq.qualifier_precedence effective_precedence,
1115 qpq.qualifier_grouping_no,
1116 qpq.comparison_operator_code,
1117 ---1 pricing_phase_id,
1118 DECODE(qph.list_type_code,'PRL',1,'AGR',1,'PML',null,-1),
1119 null modifier_level_code,
1120 qpq.qualifier_datatype attribute_datatype,
1121 qpq.qualifier_attr_value attribute_value,
1122 'QUAL' attribute_type
1123 from qp_qualifiers qpq,
1124 qp_list_headers_all_b qph
1125 where qpq.list_line_id = -1
1126 and qph.list_header_id = qpq.list_header_id
1127 and qpq.list_header_id = nvl(p_list_header_id, qpq.list_header_id)
1128 and ((p_qualifier_group is not null and qpq.qualifier_grouping_no in (-1, p_qualifier_group))
1129 OR
1130 (p_qualifier_group is null)
1131 )
1132 and ((qpq.list_type_code = 'PRL' and qpq.qualifier_context <> 'MODLIST'
1133 and qpq.qualifier_attribute <> 'QUALIFIER_ATTRIBUTE4')
1134 OR
1135 (qpq.list_type_code <> 'PRL')
1136 )
1137 AND (( g_qp_pattern_search = 'P' AND qph.list_type_code IN ('PRL','AGR'))
1138 OR (g_qp_pattern_search = 'M' AND qph.list_type_code NOT IN ('PRL','AGR','PML'))
1139 OR g_qp_pattern_search = 'B')
1140 UNION ALL
1141 SELECT qph.list_header_id,
1142 -1 list_line_id,
1143 NULL segment_id,
1144 qph.active_flag,
1145 qph.list_type_code,
1146 NULL start_date_active_q,
1147 NULL end_date_active_q,
1148 ---Added for PL/SQL Pattern Search
1149 'N' header_quals_exist_flag,
1150 qph.orig_org_id,
1151 qph.global_flag,
1152 null product_uom_code,
1153 qph.start_date_active_first,
1154 qph.end_date_active_first,
1155 qph.start_date_active_second,
1156 qph.end_date_active_second,
1157 qph.start_date_active start_date_active_h,
1158 qph.end_date_active end_date_active_h,
1159 qph.active_date_first_type,
1160 qph.active_date_second_type,
1161 qph.currency_header_id,
1162 'N',
1163 NULL,
1164 NULL list_line_type_code,
1165 qph.automatic_flag,
1166 ----Added for PL/SQL Pattern Search
1167 qph.currency_code,
1168 qph.ask_for_flag,
1169 qph.limit_exists_flag header_limit_exists,
1170 NULL line_limit_exists,
1171 qph.source_system_code,
1172 qph.pte_code,
1173 null,--qpq.qualifier_precedence effective_precedence,
1174 -1,--qpq.qualifier_grouping_no,
1175 'BLIND' comparison_operator_code,--qpq.comparison_operator_code,
1176 ---1 pricing_phase_id,
1177 DECODE(qph.list_type_code,'PRL',1,'AGR',1,'PML',null,-1),
1178 null modifier_level_code,
1179 'C' attribute_datatype,--qpq.qualifier_datatype attribute_datatype,
1180 NULL attribute_value, --qpq.qualifier_attr_value attribute_value,
1181 'BLIN' attribute_type
1182 from qp_list_headers_all_b qph
1183 where
1184 qph.list_header_id = nvl(p_list_header_id, qph.list_header_id)
1185 AND NOT EXISTS ( SELECT 1
1186 FROM qp_qualifiers qpq
1187 WHERE qpq.list_header_id = qph.list_header_id
1188 AND qpq.list_line_id = -1
1189 AND (( qph.list_type_code IN ('PRL','AGR') AND qpq.qualifier_context <> 'MODLIST'
1190 and qpq.qualifier_attribute <> 'QUALIFIER_ATTRIBUTE4'
1191 )
1192 OR qph.list_type_code NOT IN ('PRL','AGR'))
1193 )
1194 )attr_view
1195 order by attr_view.list_header_id, attr_view.list_line_id, attr_view.segment_id;
1196 begin
1197 -- delete the data from cursor, temp and final tables to start with
1198 if g_call_from_setup = 'Y' then
1199 oe_debug_pub.add('Begin generate_hp_atgrps');
1200 else
1201 write_log( 'Begin generate_hp_atgrps');
1202 end if;
1203 Reset_c_tables;
1204 Reset_tmp_tables;
1205 Reset_final_tables;
1206
1207 open c_attr_grp_hq_csr;
1208
1209 FETCH c_attr_grp_hq_csr BULK COLLECT INTO
1210 g_list_header_id_c_tbl,
1211 g_list_line_id_c_tbl,
1212 g_segment_id_c_tbl,
1213 g_active_flag_c_tbl,
1214 g_list_type_code_c_tbl,
1215 g_start_date_active_q_c_tbl,
1216 g_end_date_active_q_c_tbl,
1217 -----Added for PL/SQL Pattern Search
1218 g_header_quals_exist_c_tbl,
1219 g_orig_org_id_c_tbl,
1220 g_global_flag_c_tbl,
1221 g_product_uom_code_j_c_tbl,
1222 g_start_date_act_firs_c_tbl,
1223 g_end_date_act_firs_c_tbl,
1224 g_start_date_act_sec_c_tbl,
1225 g_end_date_act_sec_c_tbl,
1226 g_start_date_act_h_c_tbl,
1227 g_end_date_act_h_c_tbl,
1228 g_act_date_firs_type_c_tbl,
1229 g_act_date_sec_type_c_tbl,
1230 g_currency_header_id_c_tbl,
1231 g_desc_quals_exist_c_tbl,
1232 g_prc_brk_typ_code_c_tbl,
1233 g_list_line_typ_code_c_tbl,
1234 g_automatic_flag_c_tbl,
1235 -----Added for PL/SQL Pattern Search
1236 g_currency_code_c_tbl,
1237 g_ask_for_flag_c_tbl,
1238 g_header_limit_exists_c_tbl,
1239 g_line_limit_exists_c_tbl,
1240 g_source_system_code_c_tbl,
1241 g_pte_code_c_tbl,
1242 g_effective_precedence_c_tbl,
1243 g_qual_grouping_no_c_tbl,
1244 g_comparison_opr_code_c_tbl,
1245 g_pricing_phase_id_c_tbl,
1246 g_modifier_level_code_c_tbl,
1247 g_qual_datatype_c_tbl,
1248 g_qual_attr_val_c_tbl,
1249 g_attribute_type_c_tbl;
1250
1251 CLOSE c_attr_grp_hq_csr;
1252
1253 if g_list_header_id_c_tbl.count > 0 then
1254 process_c_tables('HP');
1255 end if;
1256 if g_call_from_setup = 'Y' then
1257 oe_debug_pub.add('End generate_hp_atgrps');
1261
1258 else
1259 write_log( 'End generate_hp_atgrps');
1260 end if;
1262 EXCEPTION
1263 WHEN OTHERS THEN
1264 if g_call_from_setup = 'Y' then
1265 oe_debug_pub.add('PS_ATTR_GRP_PVT.Generate_Hp_Atgrps ' || SQLERRM);
1266 else
1267 write_log( 'PS_ATTR_GRP_PVT.Generate_Hp_Atgrps ' || SQLERRM );
1268 end if;
1269
1270 end generate_hp_atgrps;
1271
1272 procedure generate_lp_atgrps(p_list_header_id number
1273 -- ,p_list_line_id number
1274 ,p_qualifier_group number
1275 ,p_low_list_line_id IN NUMBER
1276 ,p_high_list_line_id IN NUMBER)
1277 is
1278 cursor c_attr_grp_lq_csr is
1279 select * from
1280 (select /*+ ordered use_nl(qpq, qph) index(qpl QP_LIST_LINES_N18) c_attr_grp_lq_csr*/ qpq.list_header_id,
1281 qpq.list_line_id,
1282 qpq.segment_id,
1283 qph.active_flag, --bug#11927380
1284 qpq.list_type_code,
1285 qpq.start_date_active start_date_active_q,
1286 qpq.end_date_active end_date_active_q,
1287 ---Added for PL/SQL Pattern Search
1288 qpq.header_quals_exist_flag,
1289 qph.orig_org_id,
1290 qph.global_flag,
1291 null product_uom_code_j,
1292 qpl.start_date_active start_date_active_l,
1293 qpl.end_date_active end_date_active_l,
1294 qph.start_date_active_first,
1295 qph.end_date_active_first,
1296 qph.start_date_active_second,
1297 qph.end_date_active_second,
1298 qph.start_date_active start_date_active_h,
1299 qph.end_date_active end_date_active_h,
1300 qph.active_date_first_type,
1301 qph.active_date_second_type,
1302 qph.currency_header_id,
1303 qpq.qualify_hier_descendents_flag,
1304 qpl.price_break_type_code,
1305 qpl.list_line_type_code,
1306 qpl.automatic_flag,
1307 ----Added for PL/SQL Pattern Search
1308 qph.currency_code,
1309 qph.ask_for_flag,
1310 qph.limit_exists_flag header_limit_exists,
1311 qpl.limit_exists_flag line_limit_exists,
1312 qph.source_system_code,
1313 qph.pte_code,
1314 qpq.qualifier_precedence effective_precedence,
1315 qpq.qualifier_grouping_no,
1316 qpq.comparison_operator_code,
1317 qpl.pricing_phase_id pricing_phase_id,
1318 qpl.modifier_level_code modifier_level_code,
1319 qpq.qualifier_datatype attribute_datatype,
1320 qpq.qualifier_attr_value attribute_value,
1321 'QUAL' attribute_type
1322 from qp_list_headers_all_b qph, qp_list_lines qpl, qp_qualifiers qpq
1323 where qph.list_header_id = p_list_header_id
1324 AND qpl.list_header_id = qph.list_header_id
1325 and qph.list_header_id = qpq.list_header_id
1326 and qpl.list_line_id = qpq.list_line_id
1327 AND qpq.list_line_id <> -1
1328 and qpl.pricing_phase_id > 1
1329 and qpl.qualification_ind in (8,10,12,14,28,30)
1330 and qpl.list_line_id between p_low_list_line_id and p_high_list_line_id
1331 and ((p_qualifier_group is not null and qpq.qualifier_grouping_no in (-1, p_qualifier_group))
1332 OR
1333 (p_qualifier_group is null)
1334 )
1335 AND (( g_qp_pattern_search = 'P' AND qph.list_type_code IN ('PRL','AGR'))
1336 OR (g_qp_pattern_search = 'M' AND qph.list_type_code NOT IN ('PRL','AGR','PML'))
1337 OR g_qp_pattern_search = 'B' AND qph.list_type_code NOT IN ('PML'))
1338 union
1339 select /*+ ordered use_nl(qpa, qph) index(qpl QP_LIST_LINES_N18) c_attr_grp_lq_csr*/ distinct qpl.list_header_id,
1340 qpl.list_line_id,
1341 qpa.product_segment_id segment_id,
1342 qph.active_flag,
1343 qph.list_type_code,
1344 to_date(null) start_date_active_q,
1345 to_date(null) end_date_active_q,
1346 ---Added for PL/SQL Pattern Search
1347 --'N' header_quals_exist_flag,
1348 decode(bitand(qpl.qualification_ind,2),2,'Y','N') header_quals_exist_flag,
1349 qph.orig_org_id,
1350 qph.global_flag,
1351 qpa.product_uom_code product_uom_code_j,
1352 qpl.start_date_active start_date_active_l,
1353 qpl.end_date_active end_date_active_l,
1354 qph.start_date_active_first,
1355 qph.end_date_active_first,
1356 qph.start_date_active_second,
1357 qph.end_date_active_second,
1358 qph.start_date_active start_date_active_h,
1359 qph.end_date_active end_date_active_h,
1360 qph.active_date_first_type,
1361 qph.active_date_second_type,
1362 qph.currency_header_id,
1363 'N',
1364 qpl.price_break_type_code,
1365 qpl.list_line_type_code,
1366 qpl.automatic_flag,
1367 ----Added for PL/SQL Pattern Search
1368 qph.currency_code,
1369 qph.ask_for_flag,
1370 qph.limit_exists_flag header_limit_exists,
1371 qpl.limit_exists_flag line_limit_exists,
1372 qph.source_system_code,
1373 qph.pte_code,
1374 qpl.product_precedence effective_precedence,
1375 -1 qualifier_grouping_no,
1376 '=' comparison_operator_code,
1377 qpl.pricing_phase_id pricing_phase_id,
1378 qpl.modifier_level_code modifier_level_code,
1379 qpa.product_attribute_datatype attribute_datatype,
1380 qpa.product_attr_value attribute_value,
1381 'PROD' attribute_type
1382 from qp_list_headers_all_b qph, qp_list_lines qpl, qp_pricing_attributes qpa
1383 where qph.list_header_id = p_list_header_id
1384 AND qpl.list_header_id = qph.list_header_id
1385 and qpl.list_line_id = qpa.list_line_id
1386 and qpa.excluder_flag = 'N'
1387 and qpl.pricing_phase_id > 1
1388 and qpl.qualification_ind in (8,10,12,14,28,30)
1389 and qpa.product_attribute_context is not null
1390 and (qpa.pricing_attribute_context = 'VOLUME' or
1391 qpa.pricing_attribute_context is null
1392 )
1393 and qpl.list_line_id between p_low_list_line_id and p_high_list_line_id
1397 union
1394 AND (( g_qp_pattern_search = 'P' AND qph.list_type_code IN ('PRL','AGR'))
1395 OR (g_qp_pattern_search = 'M' AND qph.list_type_code NOT IN ('PRL','AGR','PML'))
1396 OR g_qp_pattern_search = 'B' AND qph.list_type_code NOT IN ('PML'))
1398 select /*+ ordered use_nl(qpa, qph) index(qpl QP_LIST_LINES_N18) c_attr_grp_lq_csr*/ qpl.list_header_id,
1399 qpl.list_line_id,
1400 qpa.pricing_segment_id segment_id,
1401 qph.active_flag,
1402 qph.list_type_code,
1403 to_date(null) start_date_active_q,
1404 to_date(null) end_date_active_q,
1405 ---Added for PL/SQL Pattern Search
1406 --'N' header_quals_exist_flag,
1407 decode(bitand(qpl.qualification_ind,2),2,'Y','N') header_quals_exist_flag,
1408 qph.orig_org_id,
1409 qph.global_flag,
1410 qpa.product_uom_code product_uom_code_j,
1411 qpl.start_date_active start_date_active_l,
1412 qpl.end_date_active end_date_active_l,
1413 qph.start_date_active_first,
1414 qph.end_date_active_first,
1415 qph.start_date_active_second,
1416 qph.end_date_active_second,
1417 qph.start_date_active start_date_active_h,
1418 qph.end_date_active end_date_active_h,
1419 qph.active_date_first_type,
1420 qph.active_date_second_type,
1421 qph.currency_header_id,
1422 'N',
1423 qpl.price_break_type_code,
1424 qpl.list_line_type_code,
1425 qpl.automatic_flag,
1426 ----Added for PL/SQL Pattern Search
1427 qph.currency_code,
1428 qph.ask_for_flag,
1429 qph.limit_exists_flag header_limit_exists,
1430 qpl.limit_exists_flag line_limit_exists,
1431 qph.source_system_code,
1432 qph.pte_code,
1433 qpl.product_precedence effective_precedence,
1434 -1 qualifier_grouping_no,
1435 qpa.comparison_operator_code,
1436 qpl.pricing_phase_id pricing_phase_id,
1437 qpl.modifier_level_code modifier_level_code,
1438 qpa.pricing_attribute_datatype attribute_datatype,
1439 qpa.pricing_attr_value_from attribute_value,
1440 'PRIC' attribute_type
1441 from qp_list_headers_all_b qph, qp_list_lines qpl, qp_pricing_attributes qpa
1442 where qph.list_header_id = p_list_header_id
1443 AND qph.list_header_id = qpl.list_header_id
1444 and qpl.list_line_id = qpa.list_line_id
1445 and qpl.pricing_phase_id > 1
1446 and qpl.qualification_ind in (8,10,12,14,28,30)
1447 and qpa.pricing_attribute_context is not null
1448 and qpl.list_line_id between p_low_list_line_id and p_high_list_line_id
1449 AND (( g_qp_pattern_search = 'P' AND qph.list_type_code IN ('PRL','AGR'))
1450 OR (g_qp_pattern_search = 'M' AND qph.list_type_code NOT IN ('PRL','AGR','PML'))
1451 OR g_qp_pattern_search = 'B' AND qph.list_type_code NOT IN ('PML'))
1452 ) attr_view
1453 order by attr_view.list_header_id, attr_view.list_line_id, attr_view.segment_id;
1454 cursor c_attr_grp_lq_csr_hdr_null is
1455 select * from
1456 (select /*+ ordered use_nl(qpq, qph) index(qpl QP_LIST_LINES_N18) c_attr_grp_lq_csr_hdr_null*/ qpq.list_header_id,
1457 qpq.list_line_id,
1458 qpq.segment_id,
1459 qph.active_flag,
1460 qpq.list_type_code,
1461 qpq.start_date_active start_date_active_q,
1462 qpq.end_date_active end_date_active_q,
1463 ---Added for PL/SQL Pattern Search
1464 qpq.header_quals_exist_flag,
1465 qph.orig_org_id,
1466 qph.global_flag,
1467 null product_uom_code_j,
1468 qpl.start_date_active start_date_active_l,
1469 qpl.end_date_active end_date_active_l,
1470 qph.start_date_active_first,
1471 qph.end_date_active_first,
1472 qph.start_date_active_second,
1473 qph.end_date_active_second,
1474 qph.start_date_active start_date_active_h,
1475 qph.end_date_active end_date_active_h,
1476 qph.active_date_first_type,
1477 qph.active_date_second_type,
1478 qph.currency_header_id,
1479 'N',
1480 qpl.price_break_type_code,
1481 qpl.list_line_type_code,
1482 qpl.automatic_flag,
1483 ----Added for PL/SQL Pattern Search
1484 qph.currency_code,
1485 qph.ask_for_flag,
1486 qph.limit_exists_flag header_limit_exists,
1487 qpl.limit_exists_flag line_limit_exists,
1488 qph.source_system_code,
1489 qph.pte_code,
1490 qpq.qualifier_precedence effective_precedence,
1491 qpq.qualifier_grouping_no,
1492 qpq.comparison_operator_code,
1493 qpl.pricing_phase_id pricing_phase_id,
1494 qpl.modifier_level_code modifier_level_code,
1495 qpq.qualifier_datatype attribute_datatype,
1496 qpq.qualifier_attr_value attribute_value,
1497 'QUAL' attribute_type
1498 from qp_list_headers_all_b qph, qp_list_lines qpl, qp_qualifiers qpq
1499 where qpl.list_header_id = qph.list_header_id
1500 and qph.list_header_id = qpq.list_header_id
1501 and qpl.list_line_id = qpq.list_line_id
1502 AND qpq.list_line_id <> -1
1503 and qpl.pricing_phase_id > 1
1504 and qpl.qualification_ind in (8,10,12,14,28,30)
1505 and qpl.list_line_id between p_low_list_line_id and p_high_list_line_id
1506 and ((p_qualifier_group is not null and qpq.qualifier_grouping_no in (-1, p_qualifier_group))
1507 OR
1508 (p_qualifier_group is null)
1509 )
1510 AND (( g_qp_pattern_search = 'P' AND qph.list_type_code IN ('PRL','AGR'))
1511 OR (g_qp_pattern_search = 'M' AND qph.list_type_code NOT IN ('PRL','AGR','PML'))
1512 OR g_qp_pattern_search = 'B' AND qph.list_type_code NOT IN ('PML'))
1513 union
1514 select /*+ ordered use_nl(qpa, qph) index(qpl QP_LIST_LINES_N18) c_attr_grp_lq_csr_hdr_null*/ distinct qpl.list_header_id,
1515 qpl.list_line_id,
1516 qpa.product_segment_id segment_id,
1517 qph.active_flag,
1518 qph.list_type_code,
1519 to_date(null) start_date_active_q,
1520 to_date(null) end_date_active_q,
1521 ---Added for PL/SQL Pattern Search
1525 qph.global_flag,
1522 --'N' header_quals_exist_flag,
1523 decode(bitand(qpl.qualification_ind,2),2,'Y','N') header_quals_exist_flag,
1524 qph.orig_org_id,
1526 qpa.product_uom_code product_uom_code_j,
1527 qpl.start_date_active start_date_active_l,
1528 qpl.end_date_active end_date_active_l,
1529 qph.start_date_active_first,
1530 qph.end_date_active_first,
1531 qph.start_date_active_second,
1532 qph.end_date_active_second,
1533 qph.start_date_active start_date_active_h,
1534 qph.end_date_active end_date_active_h,
1535 qph.active_date_first_type,
1536 qph.active_date_second_type,
1537 qph.currency_header_id,
1538 'N',
1539 qpl.price_break_type_code,
1540 qpl.list_line_type_code,
1541 qpl.automatic_flag,
1542 ----Added for PL/SQL Pattern Search
1543 qph.currency_code,
1544 qph.ask_for_flag,
1545 qph.limit_exists_flag header_limit_exists,
1546 qpl.limit_exists_flag line_limit_exists,
1547 qph.source_system_code,
1548 qph.pte_code,
1549 qpl.product_precedence effective_precedence,
1550 -1 qualifier_grouping_no,
1551 '=' comparison_operator_code,
1552 qpl.pricing_phase_id pricing_phase_id,
1553 qpl.modifier_level_code modifier_level_code,
1554 qpa.product_attribute_datatype attribute_datatype,
1555 qpa.product_attr_value attribute_value,
1556 'PROD' attribute_type
1557 from qp_list_headers_all_b qph, qp_list_lines qpl, qp_pricing_attributes qpa
1558 where qpl.list_header_id = qph.list_header_id
1559 and qpl.list_line_id = qpa.list_line_id
1560 and qpa.excluder_flag = 'N'
1561 and qpl.pricing_phase_id > 1
1562 and qpl.qualification_ind in (8,10,12,14,28,30)
1563 and qpa.product_attribute_context is not null
1564 and (qpa.pricing_attribute_context = 'VOLUME' or
1565 qpa.pricing_attribute_context is null
1566 )
1567 and qpl.list_line_id between p_low_list_line_id and p_high_list_line_id
1568 AND (( g_qp_pattern_search = 'P' AND qph.list_type_code IN ('PRL','AGR'))
1569 OR (g_qp_pattern_search = 'M' AND qph.list_type_code NOT IN ('PRL','AGR','PML'))
1570 OR g_qp_pattern_search = 'B' AND qph.list_type_code NOT IN ('PML'))
1571 union
1572 select /*+ ordered use_nl(qpa, qph) index(qpl QP_LIST_LINES_N18) c_attr_grp_lq_csr_hdr_null*/ qpl.list_header_id,
1573 qpl.list_line_id,
1574 qpa.pricing_segment_id segment_id,
1575 qph.active_flag,
1576 qph.list_type_code,
1577 to_date(null) start_date_active_q,
1578 to_date(null) end_date_active_q,
1579 ---Added for PL/SQL Pattern Search
1580 --'N' header_quals_exist_flag,
1581 decode(bitand(qpl.qualification_ind,2),2,'Y','N') header_quals_exist_flag,
1582 qph.orig_org_id,
1583 qph.global_flag,
1584 qpa.product_uom_code product_uom_code_j,
1585 qpl.start_date_active start_date_active_l,
1586 qpl.end_date_active end_date_active_l,
1587 qph.start_date_active_first,
1588 qph.end_date_active_first,
1589 qph.start_date_active_second,
1590 qph.end_date_active_second,
1591 qph.start_date_active start_date_active_h,
1592 qph.end_date_active end_date_active_h,
1593 qph.active_date_first_type,
1594 qph.active_date_second_type,
1595 qph.currency_header_id,
1596 'N',
1597 qpl.price_break_type_code,
1598 qpl.list_line_type_code,
1599 qpl.automatic_flag,
1600 ----Added for PL/SQL Pattern Search
1601 qph.currency_code,
1602 qph.ask_for_flag,
1603 qph.limit_exists_flag header_limit_exists,
1604 qpl.limit_exists_flag line_limit_exists,
1605 qph.source_system_code,
1606 qph.pte_code,
1607 qpl.product_precedence effective_precedence,
1608 -1 qualifier_grouping_no,
1609 qpa.comparison_operator_code,
1610 qpl.pricing_phase_id pricing_phase_id,
1611 qpl.modifier_level_code modifier_level_code,
1612 qpa.pricing_attribute_datatype attribute_datatype,
1613 qpa.pricing_attr_value_from attribute_value,
1614 'PRIC' attribute_type
1615 from qp_list_headers_all_b qph, qp_list_lines qpl, qp_pricing_attributes qpa
1616 where qph.list_header_id = qpl.list_header_id
1617 and qpl.list_line_id = qpa.list_line_id
1618 and qpl.pricing_phase_id > 1
1619 and qpl.qualification_ind in (8,10,12,14,28,30)
1620 and qpa.pricing_attribute_context is not null
1621 and qpl.list_line_id between p_low_list_line_id and p_high_list_line_id
1622 AND (( g_qp_pattern_search = 'P' AND qph.list_type_code IN ('PRL','AGR'))
1623 OR (g_qp_pattern_search = 'M' AND qph.list_type_code NOT IN ('PRL','AGR','PML'))
1624 OR g_qp_pattern_search = 'B' AND qph.list_type_code NOT IN ('PML'))
1625 ) attr_view
1626 order by attr_view.list_header_id, attr_view.list_line_id, attr_view.segment_id;
1627 begin
1628 -- delete the data from cursor, temp and final tables to start with
1629 Reset_c_tables;
1630 Reset_tmp_tables;
1631 Reset_final_tables;
1632 IF p_list_header_id IS NULL THEN
1633 open c_attr_grp_lq_csr_hdr_null;
1634
1635 FETCH c_attr_grp_lq_csr_hdr_null BULK COLLECT INTO
1636 g_list_header_id_c_tbl,
1637 g_list_line_id_c_tbl,
1638 g_segment_id_c_tbl,
1639 g_active_flag_c_tbl,
1640 g_list_type_code_c_tbl,
1641 g_start_date_active_q_c_tbl,
1642 g_end_date_active_q_c_tbl,
1643 -----Added for PL/SQL Pattern Search
1644 g_header_quals_exist_c_tbl,
1645 g_orig_org_id_c_tbl,
1646 g_global_flag_c_tbl,
1647 g_product_uom_code_c_tbl,
1648 g_start_date_act_l_c_tbl,
1649 g_end_date_act_l_c_tbl,
1650 g_start_date_act_firs_c_tbl,
1651 g_end_date_act_firs_c_tbl,
1652 g_start_date_act_sec_c_tbl,
1653 g_end_date_act_sec_c_tbl,
1654 g_start_date_act_h_c_tbl,
1658 g_currency_header_id_c_tbl,
1655 g_end_date_act_h_c_tbl,
1656 g_act_date_firs_type_c_tbl,
1657 g_act_date_sec_type_c_tbl,
1659 g_desc_quals_exist_c_tbl,
1660 g_prc_brk_typ_code_c_tbl,
1661 g_list_line_typ_code_c_tbl,
1662 g_automatic_flag_c_tbl,
1663 -----Added for PL/SQL Pattern Search
1664 g_currency_code_c_tbl,
1665 g_ask_for_flag_c_tbl,
1666 g_header_limit_exists_c_tbl,
1667 g_line_limit_exists_c_tbl,
1668 g_source_system_code_c_tbl,
1669 g_pte_code_c_tbl,
1670 g_effective_precedence_c_tbl,
1671 g_qual_grouping_no_c_tbl,
1672 g_comparison_opr_code_c_tbl,
1673 g_pricing_phase_id_c_tbl,
1674 g_modifier_level_code_c_tbl,
1675 g_qual_datatype_c_tbl,
1676 g_qual_attr_val_c_tbl,
1677 g_attribute_type_c_tbl;
1678 CLOSE c_attr_grp_lq_csr_hdr_null;
1679 else
1680 open c_attr_grp_lq_csr;
1681
1682 FETCH c_attr_grp_lq_csr BULK COLLECT INTO
1683 g_list_header_id_c_tbl,
1684 g_list_line_id_c_tbl,
1685 g_segment_id_c_tbl,
1686 g_active_flag_c_tbl,
1687 g_list_type_code_c_tbl,
1688 g_start_date_active_q_c_tbl,
1689 g_end_date_active_q_c_tbl,
1690 -----Added for PL/SQL Pattern Search
1691 g_header_quals_exist_c_tbl,
1692 g_orig_org_id_c_tbl,
1693 g_global_flag_c_tbl,
1694 g_product_uom_code_c_tbl,
1695 g_start_date_act_l_c_tbl,
1696 g_end_date_act_l_c_tbl,
1697 g_start_date_act_firs_c_tbl,
1698 g_end_date_act_firs_c_tbl,
1699 g_start_date_act_sec_c_tbl,
1700 g_end_date_act_sec_c_tbl,
1701 g_start_date_act_h_c_tbl,
1702 g_end_date_act_h_c_tbl,
1703 g_act_date_firs_type_c_tbl,
1704 g_act_date_sec_type_c_tbl,
1705 g_currency_header_id_c_tbl,
1706 g_desc_quals_exist_c_tbl,
1707 g_prc_brk_typ_code_c_tbl,
1708 g_list_line_typ_code_c_tbl,
1709 g_automatic_flag_c_tbl,
1710 -----Added for PL/SQL Pattern Search
1711 g_currency_code_c_tbl,
1712 g_ask_for_flag_c_tbl,
1713 g_header_limit_exists_c_tbl,
1714 g_line_limit_exists_c_tbl,
1715 g_source_system_code_c_tbl,
1716 g_pte_code_c_tbl,
1717 g_effective_precedence_c_tbl,
1718 g_qual_grouping_no_c_tbl,
1719 g_comparison_opr_code_c_tbl,
1720 g_pricing_phase_id_c_tbl,
1721 g_modifier_level_code_c_tbl,
1722 g_qual_datatype_c_tbl,
1723 g_qual_attr_val_c_tbl,
1724 g_attribute_type_c_tbl;
1725
1726 CLOSE c_attr_grp_lq_csr;
1727 END if;
1728 if g_list_header_id_c_tbl.count > 0 then
1729 process_c_tables('LP');
1730 end if;
1731
1732 EXCEPTION
1733 WHEN OTHERS THEN
1734 if g_call_from_setup = 'Y' then
1735 oe_debug_pub.add('PS_ATTR_GRP_PVT.Generate_Lp_Atgrps ' || SQLERRM);
1736 else
1737 write_log( 'PS_ATTR_GRP_PVT.Generate_Lp_Atgrps ' || SQLERRM );
1738 end if;
1739
1740 end generate_lp_atgrps;
1741
1742
1743
1744 procedure update_pp_lines(p_list_header_id number
1745 -- ,p_list_line_id number
1746 ,p_low_list_line_id IN NUMBER
1747 ,p_high_list_line_id IN NUMBER)
1748 IS
1749
1750 cursor c_lines_pp_csr is
1751 select * from
1752 (select /*+ ordered use_nl(qpa) index(qpl QP_LIST_LINES_N18) c_lines_pp_csr*/ distinct qpa.list_header_id,
1753 qpa.list_line_id,
1754 qpa.product_segment_id segment_id,
1755 '=' comparison_operator_code,
1756 qpa.pricing_phase_id,
1757 qpa.product_uom_code,
1758 qpa.product_attribute_datatype attribute_datatype,
1759 qpa.product_attr_value attribute_value,
1760 --- Added for PL/SQL Pattern Search
1761 qpl.created_by,
1762 qpl.creation_date,
1763 qpl.request_id,
1764 qpl.program_update_date,
1765 qpl.program_id,
1766 qpl.program_application_id,
1767 qpl.modifier_level_code,
1768 qph.limit_exists_flag header_limit_exists,
1769 qpl.limit_exists_flag line_limit_exists,
1770 qpl.product_precedence effective_precedence,
1771 qph.active_flag,
1772 qph.list_type_code,
1773 qph.currency_code,
1774 qph.ask_for_flag,
1775 qph.source_system_code,
1776 qph.pte_code,
1777 qph.global_flag,
1778 qph.orig_org_id,
1779 qpl.start_date_active start_date_active_l,
1780 qpl.end_date_active end_date_active_l,
1781 qph.start_date_active_first,
1782 qph.end_date_active_first,
1783 qph.start_date_active_second,
1784 qph.end_date_active_second,
1785 qph.start_date_active start_date_active_h,
1786 qph.end_date_active end_date_active_h,
1787 decode(bitand(qpl.qualification_ind,2),2,'Y','N') header_quals_exist_flag,
1788 qph.active_date_first_type,
1789 qph.active_date_second_type,
1790 qph.currency_header_id,
1791 qpl.price_break_type_code,
1792 qpl.list_line_type_code,
1793 qpl.automatic_flag,
1794
1795 ---- Added for PL/SQL Pattern Search
1796
1797 'PROD' attribute_type
1798 from qp_list_headers_all_b qph,
1799 qp_list_lines qpl,
1800 qp_pricing_attributes qpa
1801 where qph.list_header_id = p_list_header_id
1802 AND qph.list_header_id = qpl.list_header_id
1803 AND qpl.list_line_id = qpa.list_line_id
1804 and qpa.excluder_flag = 'N'
1805 and qpl.qualification_ind in (4,6,20,22)
1806 and qpa.product_attribute_context is not null
1807 and (qpa.pricing_attribute_context = 'VOLUME' or
1808 qpa.pricing_attribute_context is null
1809 )
1810 and qpl.list_line_id between p_low_list_line_id and p_high_list_line_id
1811 AND (( g_qp_pattern_search = 'P' AND qph.list_type_code IN ('PRL','AGR'))
1812 OR (g_qp_pattern_search = 'M' AND qph.list_type_code NOT IN ('PRL','AGR','PML'))
1816 qpa.list_line_id,
1813 OR g_qp_pattern_search = 'B' AND qph.list_type_code NOT IN ('PML'))
1814 union
1815 select /*+ ordered use_nl(qpa) index(qpl QP_LIST_LINES_N18) c_lines_pp_csr*/ qpa.list_header_id,
1817 qpa.pricing_segment_id segment_id,
1818 qpa.comparison_operator_code,
1819 qpa.pricing_phase_id,
1820 qpa.product_uom_code,
1821 qpa.pricing_attribute_datatype attribute_datatype,
1822 qpa.pricing_attr_value_from attribute_value,
1823 --- Added for PL/SQL Pattern Search
1824 qpl.created_by,
1825 qpl.creation_date,
1826 qpl.request_id,
1827 qpl.program_update_date,
1828 qpl.program_id,
1829 qpl.program_application_id,
1830 qpl.modifier_level_code,
1831 qph.limit_exists_flag header_limit_exists,
1832 qpl.limit_exists_flag line_limit_exists,
1833 qpl.product_precedence effective_precedence,
1834 qph.active_flag,
1835 qph.list_type_code,
1836 qph.currency_code,
1837 qph.ask_for_flag,
1838 qph.source_system_code,
1839 qph.pte_code,
1840 qph.global_flag,
1841 qph.orig_org_id,
1842 qpl.start_date_active start_date_active_l,
1843 qpl.end_date_active end_date_active_l,
1844 qph.start_date_active_first,
1845 qph.end_date_active_first,
1846 qph.start_date_active_second,
1847 qph.end_date_active_second,
1848 qph.start_date_active start_date_active_h,
1849 qph.end_date_active end_date_active_h,
1850 decode(bitand(qpl.qualification_ind,2),2,'Y','N') header_quals_exist_flag,
1851 qph.active_date_first_type,
1852 qph.active_date_second_type,
1853 qph.currency_header_id,
1854 qpl.price_break_type_code,
1855 qpl.list_line_type_code,
1856 qpl.automatic_flag,
1857
1858 ---- Added for PL/SQL Pattern Search
1859
1860 'PRIC' attribute_type
1861 from qp_list_headers_all_b qph,
1862 qp_list_lines qpl,
1863 qp_pricing_attributes qpa
1864 where qph.list_header_id = p_list_header_id
1865 AND qpl.list_header_id = qph.list_header_id
1866 AND qpl.list_line_id = qpa.list_line_id
1867 and qpl.qualification_ind in (20,22)
1868 and qpa.pricing_attribute_context is not null
1869 and qpl.list_line_id between p_low_list_line_id and p_high_list_line_id
1870 AND (( g_qp_pattern_search = 'P' AND qph.list_type_code IN ('PRL','AGR'))
1871 OR (g_qp_pattern_search = 'M' AND qph.list_type_code NOT IN ('PRL','AGR','PML'))
1872 OR g_qp_pattern_search = 'B' AND qph.list_type_code NOT IN ('PML'))
1873 union
1874 select /*+ ordered use_nl(qpa) index(qpl QP_LIST_LINES_N18) c_lines_pp_csr*/ distinct qph.list_header_id,
1875 qpl.list_line_id,
1876 NULL segment_id,
1877 'BLIND' comparison_operator_code,
1878 qpl.pricing_phase_id,
1879 NULL product_uom_code,
1880 'C' attribute_datatype,
1881 'NULL' attribute_value,
1882 ---Added for PL/SQL Pattern Search
1883 qpl.created_by,
1884 qpl.creation_date,
1885 qpl.request_id,
1886 qpl.program_update_date,
1887 qpl.program_id,
1888 qpl.program_application_id,
1889 qpl.modifier_level_code,
1890 qph.limit_exists_flag header_limit_exists,
1891 qpl.limit_exists_flag line_limit_exists,
1892 qpl.product_precedence effective_precedence,
1893 qph.active_flag,
1894 qph.list_type_code,
1895 qph.currency_code,
1896 qph.ask_for_flag,
1897 qph.source_system_code,
1898 qph.pte_code,
1899 qph.global_flag,
1900 qph.orig_org_id,
1901 qpl.start_date_active start_date_active_l,
1902 qpl.end_date_active end_date_active_l,
1903 qph.start_date_active_first,
1904 qph.end_date_active_first,
1905 qph.start_date_active_second,
1906 qph.end_date_active_second,
1907 qph.start_date_active start_date_active_h,
1908 qph.end_date_active end_date_active_h,
1909 decode(bitand(qpl.qualification_ind,2),2,'Y','N') header_quals_exist_flag,
1910 qph.active_date_first_type,
1911 qph.active_date_second_type,
1912 qph.currency_header_id,
1913 qpl.price_break_type_code,
1914 qpl.list_line_type_code,
1915 qpl.automatic_flag,
1916
1917
1918 ---- Added for PL/SQL Pattern Search
1919
1920 'BLIN' attribute_type
1921 from qp_list_headers_all_b qph,
1922 qp_list_lines qpl
1923 where qph.list_header_id = p_list_header_id
1924 AND qpl.list_header_id = qph.list_header_id
1925 and qpl.qualification_ind in (0,2)
1926 and qpl.list_line_id between p_low_list_line_id and p_high_list_line_id
1927 AND (( g_qp_pattern_search = 'P' AND qph.list_type_code IN ('PRL','AGR'))
1928 OR (g_qp_pattern_search = 'M' AND qph.list_type_code NOT IN ('PRL','AGR','PML'))
1929 OR g_qp_pattern_search = 'B' AND qph.list_type_code NOT IN ('PML'))
1930 ) attr_view
1931 order by attr_view.list_header_id, attr_view.list_line_id, attr_view.segment_id;
1932 cursor c_lines_pp_csr_hdr_null is
1933 select * from
1934 (select /*+ ordered use_nl(qpa) index(qpl QP_LIST_LINES_N18) c_lines_pp_csr_hdr_null*/ distinct qpa.list_header_id,
1935 qpa.list_line_id,
1936 qpa.product_segment_id segment_id,
1937 '=' comparison_operator_code,
1938 qpa.pricing_phase_id,
1939 qpa.product_uom_code,
1940 qpa.product_attribute_datatype attribute_datatype,
1941 qpa.product_attr_value attribute_value,
1942 --- Added for PL/SQL Pattern Search
1943 qpl.created_by,
1944 qpl.creation_date,
1945 qpl.request_id,
1946 qpl.program_update_date,
1947 qpl.program_id,
1948 qpl.program_application_id,
1949 qpl.modifier_level_code,
1950 qph.limit_exists_flag header_limit_exists,
1951 qpl.limit_exists_flag line_limit_exists,
1952 qpl.product_precedence effective_precedence,
1953 qph.active_flag,
1954 qph.list_type_code,
1955 qph.currency_code,
1956 qph.ask_for_flag,
1960 qph.orig_org_id,
1957 qph.source_system_code,
1958 qph.pte_code,
1959 qph.global_flag,
1961 qpl.start_date_active start_date_active_l,
1962 qpl.end_date_active end_date_active_l,
1963 qph.start_date_active_first,
1964 qph.end_date_active_first,
1965 qph.start_date_active_second,
1966 qph.end_date_active_second,
1967 qph.start_date_active start_date_active_h,
1968 qph.end_date_active end_date_active_h,
1969 decode(bitand(qpl.qualification_ind,2),2,'Y','N') header_quals_exist_flag,
1970 qph.active_date_first_type,
1971 qph.active_date_second_type,
1972 qph.currency_header_id,
1973 qpl.price_break_type_code,
1974 qpl.list_line_type_code,
1975 qpl.automatic_flag,
1976
1977 ---- Added for PL/SQL Pattern Search
1978 'PROD' attribute_type
1979 from qp_list_headers_all_b qph,
1980 qp_list_lines qpl,
1981 qp_pricing_attributes qpa
1982 where qph.list_header_id = qpl.list_header_id
1983 AND qpl.list_line_id = qpa.list_line_id
1984 and qpa.excluder_flag = 'N'
1985 and qpl.qualification_ind in (4,6,20,22)
1986 and qpa.product_attribute_context is not null
1987 and (qpa.pricing_attribute_context = 'VOLUME' or
1988 qpa.pricing_attribute_context is null
1989 )
1990 and qpl.list_line_id between p_low_list_line_id and p_high_list_line_id
1991 AND (( g_qp_pattern_search = 'P' AND qph.list_type_code IN ('PRL','AGR'))
1992 OR (g_qp_pattern_search = 'M' AND qph.list_type_code NOT IN ('PRL','AGR','PML'))
1993 OR g_qp_pattern_search = 'B' AND qph.list_type_code NOT IN ('PML'))
1994 union
1995 select /*+ ordered use_nl(qpa) index(qpl QP_LIST_LINES_N18) c_lines_pp_csr_hdr_null*/ qpa.list_header_id,
1996 qpa.list_line_id,
1997 qpa.pricing_segment_id segment_id,
1998 qpa.comparison_operator_code,
1999 qpa.pricing_phase_id,
2000 qpa.product_uom_code,
2001 qpa.pricing_attribute_datatype attribute_datatype,
2002 qpa.pricing_attr_value_from attribute_value,
2003 --- Added for PL/SQL Pattern Search
2004 qpl.created_by,
2005 qpl.creation_date,
2006 qpl.request_id,
2007 qpl.program_update_date,
2008 qpl.program_id,
2009 qpl.program_application_id,
2010 qpl.modifier_level_code,
2011 qph.limit_exists_flag header_limit_exists,
2012 qpl.limit_exists_flag line_limit_exists,
2013 qpl.product_precedence effective_precedence,
2014 qph.active_flag,
2015 qph.list_type_code,
2016 qph.currency_code,
2017 qph.ask_for_flag,
2018 qph.source_system_code,
2019 qph.pte_code,
2020 qph.global_flag,
2021 qph.orig_org_id,
2022 qpl.start_date_active start_date_active_l,
2023 qpl.end_date_active end_date_active_l,
2024 qph.start_date_active_first,
2025 qph.end_date_active_first,
2026 qph.start_date_active_second,
2027 qph.end_date_active_second,
2028 qph.start_date_active start_date_active_h,
2029 qph.end_date_active end_date_active_h,
2030 decode(bitand(qpl.qualification_ind,2),2,'Y','N') header_quals_exist_flag,
2031 qph.active_date_first_type,
2032 qph.active_date_second_type,
2033 qph.currency_header_id,
2034 qpl.price_break_type_code,
2035 qpl.list_line_type_code,
2036 qpl.automatic_flag,
2037 ---- Added for PL/SQL Pattern Search
2038 'PRIC' attribute_type
2039 from qp_list_headers_all_b qph,
2040 qp_list_lines qpl,
2041 qp_pricing_attributes qpa
2042 where qpl.list_header_id = qph.list_header_id
2043 AND qpl.list_line_id = qpa.list_line_id
2044 and qpl.qualification_ind in (20,22)
2045 and qpa.pricing_attribute_context is not null
2046 and qpl.list_line_id between p_low_list_line_id and p_high_list_line_id
2047 AND (( g_qp_pattern_search = 'P' AND qph.list_type_code IN ('PRL','AGR'))
2048 OR (g_qp_pattern_search = 'M' AND qph.list_type_code NOT IN ('PRL','AGR','PML'))
2049 OR g_qp_pattern_search = 'B' AND qph.list_type_code NOT IN ('PML'))
2050 union
2051 select /*+ ordered use_nl(qpa) index(qpl QP_LIST_LINES_N18) c_lines_pp_csr_hdr_null*/ distinct qph.list_header_id,
2052 qpl.list_line_id,
2053 NULL segment_id,
2054 'BLIND' comparison_operator_code,
2055 qpl.pricing_phase_id,
2056 NULL product_uom_code,
2057 'C' attribute_datatype,
2058 'NULL' attribute_value,
2059 ---Added for PL/SQL Pattern Search
2060 qpl.created_by,
2061 qpl.creation_date,
2062 qpl.request_id,
2063 qpl.program_update_date,
2064 qpl.program_id,
2065 qpl.program_application_id,
2066 qpl.modifier_level_code,
2067 qph.limit_exists_flag header_limit_exists,
2068 qpl.limit_exists_flag line_limit_exists,
2069 qpl.product_precedence effective_precedence,
2070 qph.active_flag,
2071 qph.list_type_code,
2072 qph.currency_code,
2073 qph.ask_for_flag,
2074 qph.source_system_code,
2075 qph.pte_code,
2076 qph.global_flag,
2077 qph.orig_org_id,
2078 qpl.start_date_active start_date_active_l,
2079 qpl.end_date_active end_date_active_l,
2080 qph.start_date_active_first,
2081 qph.end_date_active_first,
2082 qph.start_date_active_second,
2083 qph.end_date_active_second,
2084 qph.start_date_active start_date_active_h,
2085 qph.end_date_active end_date_active_h,
2086 decode(bitand(qpl.qualification_ind,2),2,'Y','N') header_quals_exist_flag,
2087 qph.active_date_first_type,
2088 qph.active_date_second_type,
2089 qph.currency_header_id,
2090 qpl.price_break_type_code,
2091 qpl.list_line_type_code,
2092 qpl.automatic_flag,
2093 ---- Added for PL/SQL Pattern Search
2094 'BLIN' attribute_type
2095 from qp_list_headers_all_b qph,
2096 qp_list_lines qpl
2097 where qpl.list_header_id = qph.list_header_id
2098 and qpl.qualification_ind in (0,2)
2102 OR g_qp_pattern_search = 'B' AND qph.list_type_code NOT IN ('PML'))
2099 and qpl.list_line_id between p_low_list_line_id and p_high_list_line_id
2100 AND (( g_qp_pattern_search = 'P' AND qph.list_type_code IN ('PRL','AGR'))
2101 OR (g_qp_pattern_search = 'M' AND qph.list_type_code NOT IN ('PRL','AGR','PML'))
2103 ) attr_view
2104 order by attr_view.list_header_id, attr_view.list_line_id, attr_view.segment_id;
2105 BEGIN
2106
2107 -- delete the data from cursor, temp and final tables to start with
2108 Reset_c_tables;
2109 Reset_tmp_tables;
2110 Reset_final_tables;
2111 IF p_list_header_id IS NULL THEN
2112 open c_lines_pp_csr_hdr_null;
2113 FETCH c_lines_pp_csr_hdr_null BULK COLLECT INTO
2114 g_list_header_id_c_tbl,
2115 g_list_line_id_c_tbl,
2116 g_segment_id_c_tbl,
2117 g_comparison_opr_code_c_tbl,
2118 g_pricing_phase_id_c_tbl,
2119 g_product_uom_code_c_tbl,
2120 g_qual_datatype_c_tbl,
2121 g_qual_attr_val_c_tbl,
2122 --- Added for PL/SQL Pattern Search
2123 g_created_by_c_tbl,
2124 g_creation_date_c_tbl,
2125 g_request_id_c_tbl,
2126 g_program_update_date_c_tbl,
2127 g_program_id_c_tbl,
2128 g_program_applic_id_c_tbl,
2129 g_modifier_level_code_c_tbl,
2130 g_header_limit_exists_c_tbl,
2131 g_line_limit_exists_c_tbl,
2132 g_effective_precedence_c_tbl,
2133 g_active_flag_c_tbl,
2134 g_list_type_code_c_tbl,
2135 g_currency_code_c_tbl,
2136 g_ask_for_flag_c_tbl,
2137 g_source_system_code_c_tbl,
2138 g_pte_code_c_tbl,
2139 g_global_flag_c_tbl,
2140 g_orig_org_id_c_tbl,
2141 g_start_date_act_l_c_tbl,
2142 g_end_date_act_l_c_tbl,
2143 g_start_date_act_firs_c_tbl,
2144 g_end_date_act_firs_c_tbl,
2145 g_start_date_act_sec_c_tbl,
2146 g_end_date_act_sec_c_tbl,
2147 g_start_date_act_h_c_tbl,
2148 g_end_date_act_h_c_tbl,
2149 g_header_quals_exist_c_tbl,
2150 g_act_date_firs_type_c_tbl,
2151 g_act_date_sec_type_c_tbl,
2152 g_currency_header_id_c_tbl,
2153 g_prc_brk_typ_code_c_tbl,
2154 g_list_line_typ_code_c_tbl,
2155 g_automatic_flag_c_tbl,
2156 ---- Added for PL/SQL Pattern Search
2157 g_attribute_type_c_tbl;
2158 CLOSE c_lines_pp_csr_hdr_null;
2159 ELSE
2160 open c_lines_pp_csr;
2161
2162 FETCH c_lines_pp_csr BULK COLLECT INTO
2163 g_list_header_id_c_tbl,
2164 g_list_line_id_c_tbl,
2165 g_segment_id_c_tbl,
2166 g_comparison_opr_code_c_tbl,
2167 g_pricing_phase_id_c_tbl,
2168 g_product_uom_code_c_tbl,
2169 g_qual_datatype_c_tbl,
2170 g_qual_attr_val_c_tbl,
2171 --- Added for PL/SQL Pattern Search
2172 g_created_by_c_tbl,
2173 g_creation_date_c_tbl,
2174 g_request_id_c_tbl,
2175 g_program_update_date_c_tbl,
2176 g_program_id_c_tbl,
2177 g_program_applic_id_c_tbl,
2178 g_modifier_level_code_c_tbl,
2179 g_header_limit_exists_c_tbl,
2180 g_line_limit_exists_c_tbl,
2181 g_effective_precedence_c_tbl,
2182 g_active_flag_c_tbl,
2183 g_list_type_code_c_tbl,
2184 g_currency_code_c_tbl,
2185 g_ask_for_flag_c_tbl,
2186 g_source_system_code_c_tbl,
2187 g_pte_code_c_tbl,
2188 g_global_flag_c_tbl,
2189 g_orig_org_id_c_tbl,
2190 g_start_date_act_l_c_tbl,
2191 g_end_date_act_l_c_tbl,
2192 g_start_date_act_firs_c_tbl,
2193 g_end_date_act_firs_c_tbl,
2194 g_start_date_act_sec_c_tbl,
2195 g_end_date_act_sec_c_tbl,
2196 g_start_date_act_h_c_tbl,
2197 g_end_date_act_h_c_tbl,
2198 g_header_quals_exist_c_tbl,
2199 g_act_date_firs_type_c_tbl,
2200 g_act_date_sec_type_c_tbl,
2201 g_currency_header_id_c_tbl,
2202 g_prc_brk_typ_code_c_tbl,
2203 g_list_line_typ_code_c_tbl,
2204 g_automatic_flag_c_tbl,
2205 ---- Added for PL/SQL Pattern Search
2206
2207 g_attribute_type_c_tbl;
2208
2209 CLOSE c_lines_pp_csr;
2210 END if;
2211
2212 oe_debug_pub.add('exiting update pp lines' || g_list_header_id_c_tbl.count);
2213 if g_list_header_id_c_tbl.count > 0 then
2214 process_c_tables_pp('PP');
2215 end if;
2216
2217 EXCEPTION
2218 WHEN OTHERS THEN
2219 if g_call_from_setup = 'Y' then
2220 oe_debug_pub.add('PS_ATTR_GRP_PVT.Update_Pp_Lines ' || SQLERRM);
2221 else
2222 write_log( 'PS_ATTR_GRP_PVT.Update_Pp_Lines ' || SQLERRM );
2223 end if;
2224
2225 end update_pp_lines;
2226
2227 procedure process_c_tables(p_pattern_type VARCHAR2)
2228 is
2229 l_old_list_header_id number;
2230 l_old_list_line_id number;
2231 l_current_grp number;
2232 l_pat_tmp_index number;
2233 other_grp_index number;
2234 l_prefix_value_from_null_grp varchar2(1);
2235 l_debug VARCHAR2(3);
2236 l_line_counter number := 0;
2237 l_product_uom_code_j VARCHAR2(3);
2238 begin
2239 l_old_list_header_id := g_init_val;
2240 l_old_list_line_id := g_init_val;
2241 l_product_uom_code_j := NULL;
2242
2243 if g_call_from_setup = 'Y' then
2244 oe_debug_pub.add('Start process_c_tables - Pattern type '||p_pattern_type);
2245 oe_debug_pub.add('cursor tables total = ' || g_list_header_id_c_tbl.count);
2246 else
2247 QP_PREQ_GRP.Set_QP_Debug;
2248 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
2249 IF l_debug = FND_API.G_TRUE THEN
2250 write_log( 'Start process_c_tables - Pattern type '||p_pattern_type);
2251 write_log( 'cursor tables total = ' || g_list_header_id_c_tbl.count);
2252 END IF;
2253 end if;
2254
2255 if g_list_header_id_c_tbl.count > 0 then
2259 oe_debug_pub.add('i = ' || i ||
2256 for i in 1..g_list_header_id_c_tbl.count
2257 LOOP
2258 if g_call_from_setup = 'Y' then
2260 ', list_header_id = ' || g_list_header_id_c_tbl(i) ||
2261 ', list_line_id = ' || g_list_line_id_c_tbl(i) ||
2262 ', group = ' || g_qual_grouping_no_c_tbl(i) ||
2263 ', operator = ' || g_comparison_opr_code_c_tbl(i));
2264 elsif l_debug = FND_API.G_TRUE then
2265 write_log( 'i = ' || i ||
2266 ', list_header_id = ' || g_list_header_id_c_tbl(i) ||
2267 ', list_line_id = ' || g_list_line_id_c_tbl(i) ||
2268 ', group = ' || g_qual_grouping_no_c_tbl(i) ||
2269 ', operator = ' || g_comparison_opr_code_c_tbl(i));
2270 end if;
2271
2272 if (i > 1
2273 and (g_list_header_id_c_tbl(i) <> l_old_list_header_id
2274 or g_list_line_id_c_tbl(i) <> l_old_list_line_id)
2275 )then
2276 Move_data_from_tmp_to_final(p_pattern_type);
2277 Reset_tmp_tables;
2278 l_product_uom_code_j := null;
2279 -- logic to insert into tables every G_LINES_PER_INSERT lines
2280 l_line_counter := l_line_counter + 1;
2281 if l_line_counter >= G_LINES_PER_INSERT then
2282 if g_call_from_setup = 'Y' then
2283 oe_debug_pub.add('inserting data for ' || G_LINES_PER_INSERT || ' lines');
2284 elsif l_debug = FND_API.G_TRUE then
2285 write_log( 'inserting data for ' || G_LINES_PER_INSERT || ' lines');
2286 end if;
2287 populate_atgrps;
2288 IF g_qp_pattern_search <> 'M' THEN
2289 if p_pattern_type = 'LP' then
2290 update_list_lines_cache_key;
2291 end if;
2292 END IF;
2293 reset_final_tables;
2294 if g_call_from_setup <> 'Y' then
2295 if l_debug = FND_API.G_TRUE then
2296 write_log( 'committing data for ' || G_LINES_PER_INSERT || ' lines');
2297 end if;
2298 commit;
2299 end if;
2300 l_line_counter := 0;
2301 end if;
2302 end if;
2303 l_current_grp := g_qual_grouping_no_c_tbl(i);
2304
2305 if g_qual_grouping_no_tmp_tbl.exists(l_current_grp) = TRUE then
2306
2307 /*
2308 if g_call_from_setup = 'Y' then
2309 oe_debug_pub.add('record exists in temp table for current group');
2310 oe_debug_pub.add('current record Operator is ' || g_comparison_opr_code_c_tbl(i));
2311 else
2312 write_log( 'record exists in temp table for current group');
2313 write_log( 'current record Operator is ' || g_comparison_opr_code_c_tbl(i));
2314 end if;
2315 */
2316
2317 -- if grp_no -1 record is getting updated then update the other grps records for pattern string,
2318 -- hash key, effctive dates of qualifiers, effective precedence
2319 if l_current_grp = -1 then
2320 other_grp_index := g_qual_grouping_no_tmp_tbl.first;
2321 while other_grp_index is not null
2322 LOOP
2323 /*
2324 if g_call_from_setup = 'Y' then
2325 oe_debug_pub.add('other_grp_index = ' || other_grp_index);
2326 else
2327 write_log( 'other_grp_index = ' || other_grp_index);
2328 end if;
2329 */
2330 -- do not update the -1 grp no record as -1 grp no record will be updated automatically
2331 -- outside this loop
2332 if other_grp_index = -1 then
2333 null;
2334 else
2335 if (g_start_date_active_q_c_tbl(i) is not null
2336 and g_start_date_active_q_tmp_tbl(other_grp_index) is not null
2337 and g_start_date_active_q_c_tbl(i) > g_start_date_active_q_tmp_tbl(other_grp_index)) then
2338 g_start_date_active_q_tmp_tbl(other_grp_index) := g_start_date_active_q_c_tbl(i);
2339 elsif (g_start_date_active_q_tmp_tbl(other_grp_index) is null
2340 and g_start_date_active_q_c_tbl(i) is not null) then
2341 g_start_date_active_q_tmp_tbl(other_grp_index) := g_start_date_active_q_c_tbl(i);
2342 end if;
2343
2344 if (g_end_date_active_q_c_tbl(i) is not null
2345 and g_end_date_active_q_tmp_tbl(other_grp_index) is not null
2346 and g_end_date_active_q_c_tbl(i) < g_end_date_active_q_tmp_tbl(other_grp_index)) then
2347 g_end_date_active_q_tmp_tbl(other_grp_index) := g_end_date_active_q_c_tbl(i);
2348 elsif (g_end_date_active_q_tmp_tbl(other_grp_index) is null
2349 and g_end_date_active_q_c_tbl(i) is not null) then
2350 g_end_date_active_q_tmp_tbl(other_grp_index) := g_end_date_active_q_c_tbl(i);
2351 end if;
2352
2353 g_header_limit_exists_tmp_tbl(other_grp_index) := nvl(g_header_limit_exists_c_tbl(i), 'N');
2354 g_line_limit_exists_tmp_tbl(other_grp_index) := nvl(g_line_limit_exists_c_tbl(i), 'N');
2355
2356 if g_effective_precedence_c_tbl(i) < g_effective_precedence_tmp_tbl(other_grp_index) then
2357 g_effective_precedence_tmp_tbl(other_grp_index) := g_effective_precedence_c_tbl(i);
2358 end if;
2359
2360 IF NOT g_desc_quals_exist_tmp_tbl.exists(other_grp_index) THEN
2361 g_desc_quals_exist_tmp_tbl(other_grp_index) :='N';
2362 END if;
2363
2364 IF g_desc_quals_exist_c_tbl(i) = 'Y' THEN
2365 g_desc_quals_exist_tmp_tbl(other_grp_index) :='Y';
2366 ELSE
2367 g_desc_quals_exist_tmp_tbl(other_grp_index) :='N';
2371 if g_comparison_opr_code_c_tbl(i) = '=' then
2368 END IF;
2369
2370
2372 if g_pat_string_tmp_tbl(other_grp_index) is not null then
2373 g_pat_string_tmp_tbl(other_grp_index) := g_pat_string_tmp_tbl(other_grp_index) || g_delimiter || g_segment_id_c_tbl(i);
2374 else
2375 g_pat_string_tmp_tbl(other_grp_index) := g_segment_id_c_tbl(i);
2376 end if;
2377
2378 if g_hash_key_tmp_tbl(other_grp_index) is not null then
2379 g_hash_key_tmp_tbl(other_grp_index) := g_hash_key_tmp_tbl(other_grp_index) || g_delimiter || g_qual_attr_val_c_tbl(i);
2380 else
2381 g_hash_key_tmp_tbl(other_grp_index) := g_delimiter || g_qual_attr_val_c_tbl(i);
2382 end if;
2383
2384 if g_attribute_type_c_tbl(i) = 'PROD' then
2385 g_cache_key_tmp_tbl(other_grp_index) := g_list_header_id_c_tbl(i) || g_delimiter || g_segment_id_c_tbl(i) || g_delimiter || g_qual_attr_val_c_tbl(i);
2386 end if;
2387 ----- Added for PL/SQL Pattern Search
2388
2389 IF NOT g_eq_flag_tmp_tbl.exists(other_grp_index) THEN
2390 g_eq_flag_tmp_tbl(other_grp_index) :='Y';
2391 END if;
2392
2393 ------ Added for PL/SQL Pattern Search
2394 ELSE -- g_comparison_opr_code_c_tbl(i) = '='
2395
2396 g_null_other_oprt_cnt_tmp_tbl(other_grp_index) := g_null_other_oprt_cnt_tmp_tbl(other_grp_index)+1;
2397
2398 IF NOT g_eq_flag_tmp_tbl.exists(other_grp_index) THEN
2399 g_eq_flag_tmp_tbl(other_grp_index) :='N';
2400 ELSIF(g_eq_flag_tmp_tbl(other_grp_index)='Y') THEN
2401 g_eq_flag_tmp_tbl(other_grp_index) :='N';
2402 END if;
2403 if (g_comparison_opr_code_c_tbl(i) = 'BLIND') then
2404 g_eq_flag_tmp_tbl(other_grp_index) :='Y';
2405 END IF;
2406
2407 ---- Added for PL/SQL Pattern Search
2408 end if; -- g_comparison_opr_code_c_tbl(i) = '='
2409 end if; -- other_grp_index = -1
2410
2411 --Added for PL/SQL Pattern Search
2412 IF g_attribute_type_c_tbl(i) = 'PROD' THEN
2413 l_product_uom_code_j := g_product_uom_code_c_tbl(i);
2414 END IF;
2415 g_product_uom_code_tmp_tbl(other_grp_index) := l_product_uom_code_j;
2416 ----Added for PL/SQL Pattern Search
2417
2418 other_grp_index := g_qual_grouping_no_tmp_tbl.next(other_grp_index);
2419 END LOOP; -- other_grp_index is not null
2420 IF g_comparison_opr_code_c_tbl(i) <> '=' then
2421 g_null_other_oprt_cnt_tmp_tbl(l_current_grp) := g_null_other_oprt_cnt_tmp_tbl(l_current_grp)+1;
2422 END if;
2423
2424 end if; -- l_current_grp = -1
2425
2426 --Added for PL/SQL Pattern Search
2427 g_product_uom_code_tmp_tbl(l_current_grp) := l_product_uom_code_j;
2428 g_attribute_type_tmp_tbl(l_current_grp) := g_attribute_type_c_tbl(i);
2429
2430 --Added for PL/SQL Pattern Search
2431 if (g_start_date_active_q_c_tbl(i) is not null
2432 and g_start_date_active_q_tmp_tbl(l_current_grp) is not null
2433 and g_start_date_active_q_c_tbl(i) > g_start_date_active_q_tmp_tbl(l_current_grp)) then
2434 g_start_date_active_q_tmp_tbl(l_current_grp) := g_start_date_active_q_c_tbl(i);
2435 elsif (g_start_date_active_q_tmp_tbl(l_current_grp) is null
2436 and g_start_date_active_q_c_tbl(i) is not null) then
2437 g_start_date_active_q_tmp_tbl(l_current_grp) := g_start_date_active_q_c_tbl(i);
2438 end if;
2439
2440 if (g_end_date_active_q_c_tbl(i) is not null
2441 and g_end_date_active_q_tmp_tbl(l_current_grp) is not null
2442 and g_end_date_active_q_c_tbl(i) < g_end_date_active_q_tmp_tbl(l_current_grp)) then
2443 g_end_date_active_q_tmp_tbl(l_current_grp) := g_end_date_active_q_c_tbl(i);
2444 elsif (g_end_date_active_q_tmp_tbl(l_current_grp) is null
2445 and g_end_date_active_q_c_tbl(i) is not null) then
2446 g_end_date_active_q_tmp_tbl(l_current_grp) := g_end_date_active_q_c_tbl(i);
2447 end if;
2448
2449 g_header_limit_exists_tmp_tbl(l_current_grp) := nvl(g_header_limit_exists_c_tbl(i), 'N');
2450 g_line_limit_exists_tmp_tbl(l_current_grp) := nvl(g_line_limit_exists_c_tbl(i), 'N');
2451
2452 if g_effective_precedence_c_tbl(i) < g_effective_precedence_tmp_tbl(l_current_grp) then
2453 g_effective_precedence_tmp_tbl(l_current_grp) := g_effective_precedence_c_tbl(i);
2454 end if;
2455
2456 IF NOT g_desc_quals_exist_tmp_tbl.exists(l_current_grp) THEN
2457 g_desc_quals_exist_tmp_tbl(l_current_grp) :='N';
2458 END if;
2459
2460 IF g_desc_quals_exist_c_tbl(i) = 'Y' THEN
2461 g_desc_quals_exist_tmp_tbl(l_current_grp) :='Y';
2462 ELSE
2463 g_desc_quals_exist_tmp_tbl(l_current_grp) :='N';
2464 END IF;
2465
2466 if g_comparison_opr_code_c_tbl(i) = '=' then
2467 if g_pat_string_tmp_tbl(l_current_grp) is not null then
2468 g_pat_string_tmp_tbl(l_current_grp) := g_pat_string_tmp_tbl(l_current_grp) || g_delimiter || g_segment_id_c_tbl(i);
2469 else
2470 g_pat_string_tmp_tbl(l_current_grp) := g_segment_id_c_tbl(i);
2471 end if;
2472
2473 if g_hash_key_tmp_tbl(l_current_grp) is not null then
2474 g_hash_key_tmp_tbl(l_current_grp) := g_hash_key_tmp_tbl(l_current_grp) || g_delimiter || g_qual_attr_val_c_tbl(i);
2475 else
2476 g_hash_key_tmp_tbl(l_current_grp) := g_delimiter || g_qual_attr_val_c_tbl(i);
2477 end if;
2478
2479 if g_attribute_type_c_tbl(i) = 'PROD' then
2483 -- populate the pattern temp table
2480 g_cache_key_tmp_tbl(l_current_grp) := g_list_header_id_c_tbl(i) || g_delimiter || g_segment_id_c_tbl(i) || g_delimiter || g_qual_attr_val_c_tbl(i);
2481 end if;
2482
2484 l_pat_tmp_index := g_pattern_grouping_no_tmp_tbl.count;
2485
2486 g_pattern_grouping_no_tmp_tbl(l_pat_tmp_index + 1) := g_qual_grouping_no_c_tbl(i);
2487 g_pattern_segment_id_tmp_tbl(l_pat_tmp_index + 1) := g_segment_id_c_tbl(i);
2488 ----- Added for PL/SQL Pattern Search
2489 IF NOT g_eq_flag_tmp_tbl.exists(l_current_grp) THEN
2490 g_eq_flag_tmp_tbl(l_current_grp) :='Y';
2491 END if;
2492 ------ Added for PL/SQL Pattern Search
2493 ELSE -- g_comparison_opr_code_c_tbl(i) = '='
2494
2495 g_other_oprt_count_tmp_tbl(l_current_grp) := g_other_oprt_count_tmp_tbl(l_current_grp)+1;
2496
2497 IF NOT g_eq_flag_tmp_tbl.exists(l_current_grp) THEN
2498 g_eq_flag_tmp_tbl(l_current_grp) :='N';
2499 ELSIF(g_eq_flag_tmp_tbl(l_current_grp)='Y') THEN
2500 g_eq_flag_tmp_tbl(l_current_grp) :='N';
2501 END if;
2502 if (g_comparison_opr_code_c_tbl(i) = 'BLIND') then
2503 g_eq_flag_tmp_tbl(other_grp_index) :='Y';
2504 END IF;
2505
2506 ----Added for PL/SQL Pattern Search
2507 end if; -- g_comparison_opr_code_c_tbl(i) = '='
2508
2509 g_act_date_firs_type_tmp_tbl(l_current_grp) := g_act_date_firs_type_c_tbl(i);
2510 g_act_date_sec_type_tmp_tbl(l_current_grp) := g_act_date_sec_type_c_tbl(i);
2511
2512 else -- record does not exists for the group
2513 /*
2514 if g_call_from_setup = 'Y' then
2515 oe_debug_pub.add('record DOES NOT exists in temp table for current group');
2516 oe_debug_pub.add('current record Operator is ' || g_comparison_opr_code_c_tbl(i));
2517 else
2518 write_log( 'record DOES NOT exists in temp table for current group');
2519 write_log( 'current record Operator is ' || g_comparison_opr_code_c_tbl(i));
2520 end if;
2521 */
2522
2523 l_prefix_value_from_null_grp := 'N';
2524
2525 g_other_oprt_count_tmp_tbl(l_current_grp) := 0;
2526 g_null_other_oprt_cnt_tmp_tbl(l_current_grp) := 0;
2527
2528 -- if grp_no -1 record is getting created then update the other grps records for pattern string,
2529 -- hash key, effctive dates of qualifiers, effective precedence
2530 if l_current_grp = -1 then
2531 other_grp_index := g_qual_grouping_no_tmp_tbl.first;
2532 while other_grp_index is not null
2533 LOOP
2534 /*
2535 if g_call_from_setup = 'Y' then
2536 oe_debug_pub.add('other_grp_index = ' || other_grp_index);
2537 else
2538 write_log( 'other_grp_index = ' || other_grp_index);
2539 end if;
2540 */
2541
2542 --Added for PL/SQL Pattern Search
2543 IF g_attribute_type_c_tbl(i) = 'PROD' THEN
2544 l_product_uom_code_j := g_product_uom_code_c_tbl(i);
2545
2546 END IF;
2547 g_product_uom_code_tmp_tbl(other_grp_index) := l_product_uom_code_j;
2548 ----Added for PL/SQL Pattern Search
2549
2550 if (g_start_date_active_q_c_tbl(i) is not null
2551 and g_start_date_active_q_tmp_tbl(other_grp_index) is not null
2552 and g_start_date_active_q_c_tbl(i) > g_start_date_active_q_tmp_tbl(other_grp_index)) then
2553 g_start_date_active_q_tmp_tbl(other_grp_index) := g_start_date_active_q_c_tbl(i);
2554 elsif (g_start_date_active_q_tmp_tbl(other_grp_index) is null
2555 and g_start_date_active_q_c_tbl(i) is not null) then
2556 g_start_date_active_q_tmp_tbl(other_grp_index) := g_start_date_active_q_c_tbl(i);
2557 end if;
2558
2559 if (g_end_date_active_q_c_tbl(i) is not null
2560 and g_end_date_active_q_tmp_tbl(other_grp_index) is not null
2561 and g_end_date_active_q_c_tbl(i) < g_end_date_active_q_tmp_tbl(other_grp_index)) then
2562 g_end_date_active_q_tmp_tbl(other_grp_index) := g_end_date_active_q_c_tbl(i);
2563 elsif (g_end_date_active_q_tmp_tbl(other_grp_index) is null
2564 and g_end_date_active_q_c_tbl(i) is not null) then
2565 g_end_date_active_q_tmp_tbl(other_grp_index) := g_end_date_active_q_c_tbl(i);
2566 end if;
2567
2568 g_header_limit_exists_tmp_tbl(other_grp_index) := nvl(g_header_limit_exists_c_tbl(i), 'N');
2569 g_line_limit_exists_tmp_tbl(other_grp_index) := nvl(g_line_limit_exists_c_tbl(i), 'N');
2570
2571 if g_effective_precedence_c_tbl(i) < g_effective_precedence_tmp_tbl(other_grp_index) then
2572 g_effective_precedence_tmp_tbl(other_grp_index) := g_effective_precedence_c_tbl(i);
2573 end if;
2574
2575 IF NOT g_desc_quals_exist_tmp_tbl.exists(other_grp_index) THEN
2576 g_desc_quals_exist_tmp_tbl(other_grp_index) :='N';
2577 END if;
2578
2579 IF g_desc_quals_exist_c_tbl(i) = 'Y' THEN
2580 g_desc_quals_exist_tmp_tbl(other_grp_index) :='Y';
2581 ELSE
2582 g_desc_quals_exist_tmp_tbl(other_grp_index) :='N';
2583 END IF;
2584
2585 if g_comparison_opr_code_c_tbl(i) = '=' then
2586 if g_pat_string_tmp_tbl(other_grp_index) is not null then
2587 g_pat_string_tmp_tbl(other_grp_index) := g_pat_string_tmp_tbl(other_grp_index) || g_delimiter || g_segment_id_c_tbl(i);
2588 else
2589 g_pat_string_tmp_tbl(other_grp_index) := g_segment_id_c_tbl(i);
2590 end if;
2591
2592 if g_hash_key_tmp_tbl(other_grp_index) is not null then
2593 g_hash_key_tmp_tbl(other_grp_index) := g_hash_key_tmp_tbl(other_grp_index) || g_delimiter || g_qual_attr_val_c_tbl(i);
2594 else
2598 if g_attribute_type_c_tbl(i) = 'PROD' then
2595 g_hash_key_tmp_tbl(other_grp_index) := g_delimiter || g_qual_attr_val_c_tbl(i);
2596 end if;
2597
2599 g_cache_key_tmp_tbl(other_grp_index) := g_list_header_id_c_tbl(i) || g_delimiter || g_segment_id_c_tbl(i) || g_delimiter || g_qual_attr_val_c_tbl(i);
2600 end if;
2601 ----- Added for PL/SQL Pattern Search
2602
2603 IF NOT g_eq_flag_tmp_tbl.exists(other_grp_index) THEN
2604 g_eq_flag_tmp_tbl(other_grp_index) :='Y';
2605 END if;
2606
2607 ------Added for PL/SQL Pattern Search
2608 ELSE -- g_comparison_opr_code_c_tbl(i) = '='
2609 g_null_other_oprt_cnt_tmp_tbl(other_grp_index) := g_null_other_oprt_cnt_tmp_tbl(other_grp_index)+1;
2610
2611 IF NOT g_eq_flag_tmp_tbl.exists(other_grp_index) THEN
2612 g_eq_flag_tmp_tbl(other_grp_index) :='N';
2613 ELSIF(g_eq_flag_tmp_tbl(other_grp_index)='Y') THEN
2614 g_eq_flag_tmp_tbl(other_grp_index) :='N';
2615 END if;
2616 if (g_comparison_opr_code_c_tbl(i) = 'BLIND') then
2617 g_eq_flag_tmp_tbl(other_grp_index) :='Y';
2618 END IF;
2619 ----Added for PL/SQL Pattern Search
2620 end if; -- g_comparison_opr_code_c_tbl(i) = '='
2621
2622 other_grp_index := g_qual_grouping_no_tmp_tbl.next(other_grp_index);
2623 END LOOP; -- other_grp_index is not null
2624
2625 IF g_comparison_opr_code_c_tbl(i) <> '=' THEN
2626 g_null_other_oprt_cnt_tmp_tbl(l_current_grp) := 1;
2627 END if;
2628
2629 ELSE
2630
2631 -- if l_current_grp is not -1 then look for existence of -1 grp no record, if exists then
2632 -- use it for populating pattern string, hash key, cache key, qualifier dates, precedence etc.
2633 if g_qual_grouping_no_tmp_tbl.exists(-1) = TRUE then
2634 l_prefix_value_from_null_grp := 'Y';
2635 end if;
2636 end if; -- l_current_grp = -1
2637
2638 --Added for PL/SQL Pattern Search
2639 g_product_uom_code_tmp_tbl(l_current_grp) := l_product_uom_code_j;
2640 g_attribute_type_tmp_tbl(l_current_grp) := g_attribute_type_c_tbl(i);
2641
2642 --Added for PL/SQL Pattern Search
2643 g_list_header_id_tmp_tbl(l_current_grp) := g_list_header_id_c_tbl(i);
2644 g_list_line_id_tmp_tbl(l_current_grp) := g_list_line_id_c_tbl(i);
2645 g_active_flag_tmp_tbl(l_current_grp) := g_active_flag_c_tbl(i);
2646 g_list_type_code_tmp_tbl(l_current_grp) := g_list_type_code_c_tbl(i);
2647 if l_prefix_value_from_null_grp = 'Y' then
2648 if (g_start_date_active_q_c_tbl(i) is not null
2649 and g_start_date_active_q_tmp_tbl(-1) is not null) then
2650 if g_start_date_active_q_c_tbl(i) > g_start_date_active_q_tmp_tbl(-1) then
2651 g_start_date_active_q_tmp_tbl(l_current_grp) := g_start_date_active_q_c_tbl(i);
2652 else
2653 g_start_date_active_q_tmp_tbl(l_current_grp) := g_start_date_active_q_tmp_tbl(-1);
2654 end if;
2655 elsif (g_start_date_active_q_tmp_tbl(-1) is null
2656 and g_start_date_active_q_c_tbl(i) is not null) then
2657 g_start_date_active_q_tmp_tbl(l_current_grp) := g_start_date_active_q_c_tbl(i);
2658 elsif (g_start_date_active_q_tmp_tbl(-1) is not null
2659 and g_start_date_active_q_c_tbl(i) is null) then
2660 g_start_date_active_q_tmp_tbl(l_current_grp) := g_start_date_active_q_tmp_tbl(-1);
2661 else
2662 g_start_date_active_q_tmp_tbl(l_current_grp) := null;
2663 end if;
2664
2665 if (g_end_date_active_q_c_tbl(i) is not null
2666 and g_end_date_active_q_tmp_tbl(-1) is not null) then
2667 if g_end_date_active_q_c_tbl(i) < g_end_date_active_q_tmp_tbl(-1) then
2668 g_end_date_active_q_tmp_tbl(l_current_grp) := g_end_date_active_q_c_tbl(i);
2669 else
2670 g_end_date_active_q_tmp_tbl(l_current_grp) := g_end_date_active_q_tmp_tbl(-1);
2671 end if;
2672 elsif (g_end_date_active_q_tmp_tbl(-1) is null
2673 and g_end_date_active_q_c_tbl(i) is not null) then
2674 g_end_date_active_q_tmp_tbl(l_current_grp) := g_end_date_active_q_c_tbl(i);
2675 elsif (g_end_date_active_q_tmp_tbl(-1) is not null
2676 and g_end_date_active_q_c_tbl(i) is null) then
2677 g_end_date_active_q_tmp_tbl(l_current_grp) := g_end_date_active_q_tmp_tbl(-1);
2678 else
2679 g_end_date_active_q_tmp_tbl(l_current_grp) := null;
2680 end if;
2681
2682 if g_effective_precedence_tmp_tbl(-1) < g_effective_precedence_c_tbl(i) then
2683 g_effective_precedence_tmp_tbl(l_current_grp) := g_effective_precedence_tmp_tbl(-1);
2684 else
2685 g_effective_precedence_tmp_tbl(l_current_grp) := g_effective_precedence_c_tbl(i);
2686 end if;
2687 else
2688 g_start_date_active_q_tmp_tbl(l_current_grp) := g_start_date_active_q_c_tbl(i);
2689 g_end_date_active_q_tmp_tbl(l_current_grp) := g_end_date_active_q_c_tbl(i);
2690 g_effective_precedence_tmp_tbl(l_current_grp) := g_effective_precedence_c_tbl(i);
2691 end if; -- l_prefix_value_from_null_grp = 'Y'
2692
2693 g_currency_code_tmp_tbl(l_current_grp) := g_currency_code_c_tbl(i);
2694 g_ask_for_flag_tmp_tbl(l_current_grp) := g_ask_for_flag_c_tbl(i);
2695 g_header_limit_exists_tmp_tbl(l_current_grp) := nvl(g_header_limit_exists_c_tbl(i), 'N');
2696 g_line_limit_exists_tmp_tbl(l_current_grp) := nvl(g_line_limit_exists_c_tbl(i), 'N');
2697 g_source_system_code_tmp_tbl(l_current_grp) := g_source_system_code_c_tbl(i);
2698 g_qual_grouping_no_tmp_tbl(l_current_grp) := g_qual_grouping_no_c_tbl(i);
2699 g_pricing_phase_id_tmp_tbl(l_current_grp) := g_pricing_phase_id_c_tbl(i);
2703 g_start_date_act_firs_tmp_tbl(l_current_grp) := g_start_date_act_firs_c_tbl(i);
2700 g_modifier_level_code_tmp_tbl(l_current_grp) := g_modifier_level_code_c_tbl(i);
2701
2702 g_pricing_attr_count_tmp_tbl(l_current_grp) := null;
2704 g_end_date_act_firs_tmp_tbl(l_current_grp) := g_end_date_act_firs_c_tbl(i);
2705 g_start_date_act_sec_tmp_tbl(l_current_grp) := g_start_date_act_sec_c_tbl(i);
2706 g_end_date_act_sec_tmp_tbl(l_current_grp) := g_end_date_act_sec_c_tbl(i);
2707 g_start_date_act_h_tmp_tbl(l_current_grp) := g_start_date_act_h_c_tbl(i);
2708 g_end_date_act_h_tmp_tbl(l_current_grp) := g_end_date_act_h_c_tbl(i);
2709 g_act_date_firs_type_tmp_tbl(l_current_grp) := g_act_date_firs_type_c_tbl(i);
2710 g_act_date_sec_type_tmp_tbl(l_current_grp) := g_act_date_sec_type_c_tbl(i);
2711 g_prc_brk_typ_code_tmp_tbl(l_current_grp) := g_prc_brk_typ_code_c_tbl(i);
2712 g_list_line_typ_code_tmp_tbl(l_current_grp) := g_list_line_typ_code_c_tbl(i);
2713 g_automatic_flag_tmp_tbl(l_current_grp) := g_automatic_flag_c_tbl(i);
2714 IF p_pattern_type = 'HP' THEN
2715 g_start_date_act_l_tmp_tbl(l_current_grp) := null;
2716 g_end_date_act_l_tmp_tbl(l_current_grp) := null;
2717 ELSE
2718 g_start_date_act_l_tmp_tbl(l_current_grp) := g_start_date_act_l_c_tbl(i);
2719 g_end_date_act_l_tmp_tbl(l_current_grp) := g_end_date_act_l_c_tbl(i);
2720 END IF;
2721
2722 IF NOT g_desc_quals_exist_tmp_tbl.exists(l_current_grp) THEN
2723 g_desc_quals_exist_tmp_tbl(l_current_grp) :='N';
2724 END if;
2725
2726 IF g_desc_quals_exist_c_tbl(i) = 'Y' THEN
2727 g_desc_quals_exist_tmp_tbl(l_current_grp) :='Y';
2728 ELSE
2729 g_desc_quals_exist_tmp_tbl(l_current_grp) :='N';
2730 END IF;
2731
2732
2733
2734 if g_comparison_opr_code_c_tbl(i) = '=' then
2735 if l_prefix_value_from_null_grp = 'Y' then
2736 if g_pat_string_tmp_tbl(-1) is not null then
2737 g_pat_string_tmp_tbl(l_current_grp) := g_pat_string_tmp_tbl(-1) || g_delimiter || g_segment_id_c_tbl(i);
2738 else
2739 g_pat_string_tmp_tbl(l_current_grp) := g_segment_id_c_tbl(i);
2740 end if;
2741
2742 if g_hash_key_tmp_tbl(-1) is not null then
2743 g_hash_key_tmp_tbl(l_current_grp) := g_hash_key_tmp_tbl(-1) || g_delimiter || g_qual_attr_val_c_tbl(i);
2744 else
2745 g_hash_key_tmp_tbl(l_current_grp) := g_delimiter || g_qual_attr_val_c_tbl(i);
2746 end if;
2747
2748 if g_attribute_type_c_tbl(i) = 'PROD' then
2749 g_cache_key_tmp_tbl(l_current_grp) := g_list_header_id_c_tbl(i) || g_delimiter || g_segment_id_c_tbl(i) || g_delimiter || g_qual_attr_val_c_tbl(i);
2750 elsif g_cache_key_tmp_tbl(-1) is not null then
2751 g_cache_key_tmp_tbl(l_current_grp) := g_cache_key_tmp_tbl(-1);
2752 else
2753 g_cache_key_tmp_tbl(l_current_grp) := null;
2754 end if;
2755
2756 g_null_other_oprt_cnt_tmp_tbl(l_current_grp) := g_other_oprt_count_tmp_tbl(-1);
2757
2758 else
2759 g_pat_string_tmp_tbl(l_current_grp) := g_segment_id_c_tbl(i);
2760 g_hash_key_tmp_tbl(l_current_grp) := g_delimiter || g_qual_attr_val_c_tbl(i);
2761
2762 if g_attribute_type_c_tbl(i) = 'PROD' then
2763 g_cache_key_tmp_tbl(l_current_grp) := g_list_header_id_c_tbl(i) || g_delimiter || g_segment_id_c_tbl(i) || g_delimiter || g_qual_attr_val_c_tbl(i);
2764 else
2765 g_cache_key_tmp_tbl(l_current_grp) := null;
2766 end if;
2767 end if; -- l_prefix_value_from_null_grp = 'Y'
2768
2769 -- populate the pattern temp table
2770 l_pat_tmp_index := g_pattern_grouping_no_tmp_tbl.count;
2771
2772 g_pattern_grouping_no_tmp_tbl(l_pat_tmp_index + 1) := g_qual_grouping_no_c_tbl(i);
2773 g_pattern_segment_id_tmp_tbl(l_pat_tmp_index + 1) := g_segment_id_c_tbl(i);
2774 ----- Added for PL/SQL Pattern Search
2775
2776 IF NOT g_eq_flag_tmp_tbl.exists(l_current_grp) THEN
2777 IF l_prefix_value_from_null_grp = 'Y' THEN
2778 g_eq_flag_tmp_tbl(l_current_grp) := g_eq_flag_tmp_tbl(-1);
2779 ELSE
2780 g_eq_flag_tmp_tbl(l_current_grp) :='Y';
2781 END if;
2782 END if;
2783
2784 ----Added for PL/SQL Pattern Search
2785 else -- operator other than =
2786 ------Added for PL/SQL Pattern Search
2787 IF l_prefix_value_from_null_grp = 'Y' THEN
2788 g_null_other_oprt_cnt_tmp_tbl(l_current_grp) := g_other_oprt_count_tmp_tbl(-1);
2789 END if;
2790
2791 g_other_oprt_count_tmp_tbl(l_current_grp) := 1;
2792
2793 IF NOT g_eq_flag_tmp_tbl.exists(l_current_grp) THEN
2794 g_eq_flag_tmp_tbl(l_current_grp) :='N';
2795 ELSIF(g_eq_flag_tmp_tbl(l_current_grp)='Y') THEN
2796 g_eq_flag_tmp_tbl(l_current_grp) :='N';
2797 END if;
2798 if (g_comparison_opr_code_c_tbl(i) = 'BLIND') THEN
2799 g_other_oprt_count_tmp_tbl(l_current_grp) := 0;
2800 g_eq_flag_tmp_tbl(l_current_grp) :='Y';
2801 END IF;
2802 ----Added for PL/SQL Pattern Search
2803 if l_prefix_value_from_null_grp = 'Y' then
2804 if g_pat_string_tmp_tbl(-1) is not null then
2805 g_pat_string_tmp_tbl(l_current_grp) := g_pat_string_tmp_tbl(-1);
2806 else
2807 g_pat_string_tmp_tbl(l_current_grp) := null;
2808 end if;
2809
2810 if g_hash_key_tmp_tbl(-1) is not null then
2811 g_hash_key_tmp_tbl(l_current_grp) := g_hash_key_tmp_tbl(-1);
2812 else
2813 g_hash_key_tmp_tbl(l_current_grp) := null;
2814 end if;
2815
2816 if g_cache_key_tmp_tbl(-1) is not null then
2817 g_cache_key_tmp_tbl(l_current_grp) := g_cache_key_tmp_tbl(-1);
2821 else
2818 else
2819 g_cache_key_tmp_tbl(l_current_grp) := null;
2820 end if;
2822 g_pat_string_tmp_tbl(l_current_grp) := null;
2823 g_hash_key_tmp_tbl(l_current_grp) := null;
2824 g_cache_key_tmp_tbl(l_current_grp) := null;
2825 end if; -- l_prefix_value_from_null_grp = 'Y'
2826 end if; -- g_comparison_opr_code_c_tbl(i) = '='
2827
2828 end if; -- g_qual_grouping_no_tmp_tbl.exists(l_current_grp)
2829 -- store the header and line id
2830
2831 ----------- Added for PL/SQL Pattern Search
2832 g_header_quals_exist_tmp_tbl(l_current_grp) := g_header_quals_exist_c_tbl(i);
2833 g_orig_org_id_tmp_tbl(l_current_grp) := g_orig_org_id_c_tbl(i);
2834 g_global_flag_tmp_tbl(l_current_grp) := g_global_flag_c_tbl(i);
2835 g_currency_header_id_tmp_tbl(l_current_grp) := g_currency_header_id_c_tbl(i);
2836 g_pte_code_tmp_tbl(l_current_grp) := g_pte_code_c_tbl(i);
2837 ---------- Added for PL/SQL Pattern Search
2838
2839 l_old_list_header_id := g_list_header_id_c_tbl(i);
2840 l_old_list_line_id := g_list_line_id_c_tbl(i);
2841 end loop; -- i in 1..g_list_header_id_c_tbl.count
2842
2843 end if; -- g_list_header_id_c_tbl.count > 0
2844
2845 -- move data for last pair of header and line id
2846 Move_data_from_tmp_to_final(p_pattern_type);
2847
2848 -- insert into qp_attribute_groups from final tables
2849 populate_atgrps;
2850
2851 -- for line pattern, qp_list_lines.cache_key need to be populated as well
2852 IF g_qp_pattern_search <> 'M' THEN ----
2853 if p_pattern_type = 'LP' then
2854 update_list_lines_cache_key;
2855 end if;
2856 END IF;
2857
2858 EXCEPTION
2859 WHEN OTHERS THEN
2860 if g_call_from_setup = 'Y' then
2861 oe_debug_pub.add('PS_ATTR_GRP_PVT.Process_C_Tables ' || SQLERRM);
2862 else
2863 write_log( 'PS_ATTR_GRP_PVT.Process_C_Tables ' || SQLERRM );
2864 end if;
2865
2866 end process_c_tables;
2867
2868 procedure process_c_tables_pp(p_pattern_type VARCHAR2)
2869 is
2870 l_old_list_header_id number;
2871 l_old_list_line_id number;
2872 l_pat_tmp_index number;
2873 l_first_pa_rec_for_line varchar2(1);
2874 l_debug VARCHAR2(3);
2875 l_line_counter number := 0;
2876 l_prod_uom_code VARCHAR2(10):=null;
2877 begin
2878 l_old_list_header_id := g_init_val;
2879 l_old_list_line_id := g_init_val;
2880
2881 if g_call_from_setup = 'Y' then
2882 oe_debug_pub.add('PP cursor tables total = ' || g_list_header_id_c_tbl.count);
2883 else
2884 QP_PREQ_GRP.Set_QP_Debug;
2885 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
2886 IF l_debug = FND_API.G_TRUE THEN
2887 write_log( 'PP cursor tables total = ' || g_list_header_id_c_tbl.count);
2888 END IF;
2889 end if;
2890
2891 l_first_pa_rec_for_line := 'Y';
2892
2893 if g_list_header_id_c_tbl.count > 0 then
2894 for i in 1..g_list_header_id_c_tbl.count
2895 LOOP
2896 if g_call_from_setup = 'Y' then
2897 oe_debug_pub.add('PP i = ' || i ||
2898 ', list_header_id = ' || g_list_header_id_c_tbl(i) ||
2899 ', list_line_id = ' || g_list_line_id_c_tbl(i) ||
2900 ', operator = ' || g_comparison_opr_code_c_tbl(i));
2901 elsif l_debug = FND_API.G_TRUE then
2902 write_log( 'PP i = ' || i ||
2903 ', list_header_id = ' || g_list_header_id_c_tbl(i) ||
2904 ', list_line_id = ' || g_list_line_id_c_tbl(i) ||
2905 ', operator = ' || g_comparison_opr_code_c_tbl(i));
2906 end if;
2907
2908 if (i > 1
2909 and (g_list_header_id_c_tbl(i) <> l_old_list_header_id
2910 or g_list_line_id_c_tbl(i) <> l_old_list_line_id)
2911 ) then
2912 Move_data_from_tmp_to_final(p_pattern_type);
2913 Reset_tmp_tables;
2914 l_first_pa_rec_for_line := 'Y';
2915
2916 -- logic to insert into tables every G_LINES_PER_INSERT lines
2917 l_line_counter := l_line_counter + 1;
2918 if l_line_counter >= G_LINES_PER_INSERT then
2919 if g_call_from_setup = 'Y' then
2920 oe_debug_pub.add('inserting data for ' || G_LINES_PER_INSERT || ' lines');
2921 elsif l_debug = FND_API.G_TRUE then
2922 write_log( 'inserting data for ' || G_LINES_PER_INSERT || ' lines');
2923 end if;
2924 IF g_qp_pattern_search <> 'M' THEN
2925 update_list_lines;
2926 END IF;
2927 populate_pp_atgrps;
2928 reset_final_tables;
2929 if g_call_from_setup <> 'Y' then
2930 if l_debug = FND_API.G_TRUE then
2931 write_log( 'committing data for ' || G_LINES_PER_INSERT || ' lines');
2932 end if;
2933 commit;
2934 end if;
2935 l_line_counter := 0;
2936 end if;
2937 end if;
2938
2939 if l_first_pa_rec_for_line = 'Y' then
2940 g_list_header_id_tmp_tbl(-1) := g_list_header_id_c_tbl(i);
2941 g_list_line_id_tmp_tbl(-1) := g_list_line_id_c_tbl(i);
2942 l_prod_uom_code := g_product_uom_code_c_tbl(i);
2943 g_product_uom_code_tmp_tbl(-1) := g_product_uom_code_c_tbl(i);
2944 g_qual_grouping_no_tmp_tbl(-1) := -1;
2945 g_pricing_phase_id_tmp_tbl(-1) := g_pricing_phase_id_c_tbl(i);
2946 g_start_date_active_q_tmp_tbl(-1) := null;
2947 g_end_date_active_q_tmp_tbl(-1) := null;
2948 g_effective_precedence_tmp_tbl(-1) := null;
2949 --------- Added for PL/SQL Pattern Search
2950 g_header_quals_exist_tmp_tbl(-1) := g_header_quals_exist_c_tbl(i);
2951 g_product_uom_code_j_tmp_tbl(-1) := null;
2952 g_created_by_tmp_tbl(-1) := g_created_by_c_tbl(i);
2956 g_program_id_tmp_tbl(-1) := g_program_id_c_tbl(i);
2953 g_creation_date_tmp_tbl(-1) := g_creation_date_c_tbl(i);
2954 g_request_id_tmp_tbl(-1) := g_request_id_c_tbl(i);
2955 g_program_update_date_tmp_tbl(-1) := g_program_update_date_c_tbl(i);
2957 g_program_applic_id_tmp_tbl(-1) := g_program_applic_id_c_tbl(i);
2958 g_modifier_level_code_tmp_tbl(-1) := g_modifier_level_code_c_tbl(i);
2959 g_header_limit_exists_tmp_tbl(-1) := NVL(g_header_limit_exists_c_tbl(i),'N');
2960 g_line_limit_exists_tmp_tbl(-1) := NVL(g_line_limit_exists_c_tbl(i),'N');
2961 if g_effective_precedence_c_tbl(i) < g_effective_precedence_tmp_tbl(-1) then
2962 g_effective_precedence_tmp_tbl(-1) := g_effective_precedence_c_tbl(i);
2963 end if;
2964 g_active_flag_tmp_tbl(-1) := g_active_flag_c_tbl(i);
2965 g_list_type_code_tmp_tbl(-1) := g_list_type_code_c_tbl(i);
2966 g_currency_code_tmp_tbl(-1) := g_currency_code_c_tbl(i);
2967 g_ask_for_flag_tmp_tbl(-1) := g_ask_for_flag_c_tbl(i);
2968 g_source_system_code_tmp_tbl(-1) := g_source_system_code_c_tbl(i);
2969 g_global_flag_tmp_tbl(-1) := g_global_flag_c_tbl(i);
2970 g_orig_org_id_tmp_tbl(-1) := g_orig_org_id_c_tbl(i);
2971 g_start_date_act_firs_tmp_tbl(-1) := g_start_date_act_firs_c_tbl(i);
2972 g_end_date_act_firs_tmp_tbl(-1) := g_end_date_act_firs_c_tbl(i);
2973 g_start_date_act_sec_tmp_tbl(-1) := g_start_date_act_sec_c_tbl(i);
2974 g_end_date_act_sec_tmp_tbl(-1) := g_end_date_act_sec_c_tbl(i);
2975 g_start_date_act_h_tmp_tbl(-1) := g_start_date_act_h_c_tbl(i);
2976 g_end_date_act_h_tmp_tbl(-1) := g_end_date_act_h_c_tbl(i);
2977 g_start_date_act_l_tmp_tbl(-1) := g_start_date_act_l_c_tbl(i);
2978 g_end_date_act_l_tmp_tbl(-1) := g_end_date_act_l_c_tbl(i);
2979 g_attribute_type_tmp_tbl(-1) := g_attribute_type_c_tbl(i);
2980 g_act_date_firs_type_tmp_tbl(-1) := g_act_date_firs_type_c_tbl(i);
2981 g_act_date_sec_type_tmp_tbl(-1) := g_act_date_sec_type_c_tbl(i);
2982 g_currency_header_id_tmp_tbl(-1) := g_currency_header_id_c_tbl(i);
2983 g_pte_code_tmp_tbl(-1) := g_pte_code_c_tbl(i);
2984 g_other_oprt_count_tmp_tbl(-1) := 0;
2985 g_null_other_oprt_cnt_tmp_tbl(-1) := 0;
2986 g_prc_brk_typ_code_tmp_tbl(-1) := g_prc_brk_typ_code_c_tbl(i);
2987 g_desc_quals_exist_tmp_tbl(-1) := 'N';
2988 g_list_line_typ_code_tmp_tbl(-1) := g_list_line_typ_code_c_tbl(i);
2989 g_automatic_flag_tmp_tbl(-1) := g_automatic_flag_c_tbl(i);
2990
2991
2992
2993 --------- Added for PL/SQL Pattern Search
2994
2995 if g_attribute_type_c_tbl(i) = 'PRIC' then
2996 g_pricing_attr_count_tmp_tbl(-1) := 1;
2997 else
2998 g_pricing_attr_count_tmp_tbl(-1) := 0;
2999 end if;
3000
3001 if g_comparison_opr_code_c_tbl(i) = '=' then
3002 g_pat_string_tmp_tbl(-1) := g_segment_id_c_tbl(i);
3003 g_hash_key_tmp_tbl(-1) := g_delimiter || g_qual_attr_val_c_tbl(i);
3004
3005 if g_attribute_type_c_tbl(i) = 'PROD' then
3006 g_cache_key_tmp_tbl(-1) := g_list_header_id_c_tbl(i) || g_delimiter || g_segment_id_c_tbl(i) || g_delimiter || g_qual_attr_val_c_tbl(i);
3007 else
3008 g_cache_key_tmp_tbl(-1) := null;
3009 end if;
3010
3011 -- populate the pattern temp table
3012 l_pat_tmp_index := g_pattern_grouping_no_tmp_tbl.count;
3013
3014 g_pattern_grouping_no_tmp_tbl(l_pat_tmp_index + 1) := -1;
3015 g_pattern_segment_id_tmp_tbl(l_pat_tmp_index + 1) := g_segment_id_c_tbl(i);
3016
3017 -- Added for PL/SQL Pattern Search
3018
3019 IF NOT g_eq_flag_tmp_tbl.exists(-1) THEN
3020 g_eq_flag_tmp_tbl(-1) :='Y';
3021 END if;
3022
3023 ------ Added for PL/SQL Pattern Search
3024
3025 else -- operator other than =
3026 g_pat_string_tmp_tbl(-1) := null;
3027 g_hash_key_tmp_tbl(-1) := null;
3028 g_cache_key_tmp_tbl(-1) := null;
3029 g_other_oprt_count_tmp_tbl(-1) := g_other_oprt_count_tmp_tbl(-1)+1;
3030 g_null_other_oprt_cnt_tmp_tbl(-1) := g_null_other_oprt_cnt_tmp_tbl(-1)+1;
3031
3032 -- Added for PL/SQL Pattern Search
3033
3034 IF NOT g_eq_flag_tmp_tbl.exists(-1) THEN
3035 g_eq_flag_tmp_tbl(-1) :='N';
3036 ELSIF(g_eq_flag_tmp_tbl(-1)='Y') THEN
3037 g_eq_flag_tmp_tbl(-1) :='N';
3038 END if;
3039 if (g_comparison_opr_code_c_tbl(i) = 'BLIND') THEN
3040 g_other_oprt_count_tmp_tbl(-1) := 0;
3041 g_eq_flag_tmp_tbl(-1) :='Y';
3042 END IF;
3043
3044 -- Added for PL/SQL Pattern Search
3045 end if; -- g_comparison_opr_code_c_tbl(i) = '='
3046
3047 else -- not first pa record for a line
3048 g_product_uom_code_tmp_tbl(-1) := l_prod_uom_code;
3049 if g_attribute_type_c_tbl(i) = 'PRIC' then
3050 g_pricing_attr_count_tmp_tbl(-1) := g_pricing_attr_count_tmp_tbl(-1) + 1;
3051 end if;
3052
3053 if g_comparison_opr_code_c_tbl(i) = '=' then
3054 if g_pat_string_tmp_tbl(-1) is not null then
3055 g_pat_string_tmp_tbl(-1) := g_pat_string_tmp_tbl(-1) || g_delimiter || g_segment_id_c_tbl(i);
3056 else
3057 g_pat_string_tmp_tbl(-1) := g_segment_id_c_tbl(i);
3058 end if;
3059
3060 if g_hash_key_tmp_tbl(-1) is not null then
3061 g_hash_key_tmp_tbl(-1) := g_hash_key_tmp_tbl(-1) || g_delimiter || g_qual_attr_val_c_tbl(i);
3062 else
3063 g_hash_key_tmp_tbl(-1) := g_delimiter || g_qual_attr_val_c_tbl(i);
3064 end if;
3065
3066 if g_attribute_type_c_tbl(i) = 'PROD' then
3067 g_cache_key_tmp_tbl(-1) := g_list_header_id_c_tbl(i) || g_delimiter || g_segment_id_c_tbl(i) || g_delimiter || g_qual_attr_val_c_tbl(i);
3068 end if;
3069
3070 -- populate the pattern temp table
3074 g_pattern_segment_id_tmp_tbl(l_pat_tmp_index + 1) := g_segment_id_c_tbl(i);
3071 l_pat_tmp_index := g_pattern_grouping_no_tmp_tbl.count;
3072
3073 g_pattern_grouping_no_tmp_tbl(l_pat_tmp_index + 1) := -1;
3075
3076 ----- Added for PL/SQL Pattern Search
3077
3078 IF NOT g_eq_flag_tmp_tbl.exists(-1) THEN
3079 g_eq_flag_tmp_tbl(-1) :='Y';
3080 END if;
3081 ------ Added for PL/SQL Pattern Search
3082 ELSE -- g_comparison_opr_code_c_tbl(i) = '='
3083
3084 g_other_oprt_count_tmp_tbl(-1) := g_other_oprt_count_tmp_tbl(-1)+1;
3085 g_null_other_oprt_cnt_tmp_tbl(-1) := g_null_other_oprt_cnt_tmp_tbl(-1)+1;
3086
3087 IF NOT g_eq_flag_tmp_tbl.exists(-1) THEN
3088 g_eq_flag_tmp_tbl(-1) :='N';
3089 ELSIF(g_eq_flag_tmp_tbl(-1)='Y') THEN
3090 g_eq_flag_tmp_tbl(-1) :='N';
3091 END if;
3092 if (g_comparison_opr_code_c_tbl(i) = 'BLIND') then
3093 g_eq_flag_tmp_tbl(-1) :='Y';
3094 END IF;
3095 ---- Added for PL/SQL Pattern Search
3096 end if; -- g_comparison_opr_code_c_tbl(i) = '='
3097 end if; -- l_first_pa_rec_for_line = 'Y'
3098
3099
3100 -- set first record indicator to 'N'
3101 l_first_pa_rec_for_line := 'N';
3102
3103 -- store the header and line id
3104 l_old_list_header_id := g_list_header_id_c_tbl(i);
3105 l_old_list_line_id := g_list_line_id_c_tbl(i);
3106 end loop; -- i in 1..g_list_header_id_c_tbl.count
3107
3108 end if; -- g_list_header_id_c_tbl.count > 0
3109
3110 -- move data for last pair of header and line id
3111
3112 oe_debug_pub.add('Count final table before move data - '||g_list_header_id_final_tbl.count);
3113
3114 Move_data_from_tmp_to_final(p_pattern_type);
3115
3116 oe_debug_pub.add('Count final table after move data - '||g_list_header_id_final_tbl.count);
3117
3118 -- update qp_list_lines from final tables
3119 IF g_qp_pattern_search <> 'M' THEN
3120 update_list_lines;
3121 END IF;
3122
3123 -- Added for PL/SQL Pattern Search
3124 --- update populate_pp_atgrps from final tables
3125
3126 oe_debug_pub.add('Count final table before final insertion - '||g_list_line_id_final_tbl.count);
3127
3128 populate_pp_atgrps;
3129 reset_final_tables;
3130 -- Added for PL/SQL Pattern Search
3131 EXCEPTION
3132 WHEN OTHERS THEN
3133 if g_call_from_setup = 'Y' then
3134 oe_debug_pub.add('PS_ATTR_GRP_PVT.Process_C_Tables_Pp ' || SQLERRM);
3135 else
3136 write_log( 'PS_ATTR_GRP_PVT.Process_C_Tables_Pp ' || SQLERRM );
3137 end if;
3138
3139 end process_c_tables_pp;
3140
3141 PROCEDURE Move_data_from_tmp_to_final(p_pattern_type VARCHAR2)
3142 is
3143 l_other_grp_exists varchar2(1);
3144 l_pattern_id number;
3145 l_atgrp_final_index number;
3146 grp_no_index number;
3147 l_product_precedence number;
3148 BEGIN
3149 /*
3150 if g_call_from_setup = 'Y' then
3151 oe_debug_pub.add('Moving data from temp table to final table');
3152 oe_debug_pub.add('temp tables total = ' || g_list_header_id_tmp_tbl.count);
3153 else
3154 write_log( 'Moving data from temp table to final table');
3155 write_log( 'temp tables total = ' || g_list_header_id_tmp_tbl.count);
3156 end if;
3157 */
3158
3159 -- find out whether any qual groups exists other than -1
3160 l_other_grp_exists := 'N';
3161 if g_list_header_id_tmp_tbl.count > 1 and g_list_header_id_tmp_tbl.exists(-1) = TRUE then
3162 l_other_grp_exists := 'Y';
3163 end if; -- g_list_header_id_tmp_tbl.count > 1
3164
3165 /*
3166 if g_call_from_setup = 'Y' then
3167 oe_debug_pub.add('l_other_grp_exists = ' || l_other_grp_exists);
3168 else
3169 write_log( 'l_other_grp_exists = ' || l_other_grp_exists);
3170 end if;
3171 */
3172
3173 -- now loop thru the atgrp temp tables and move to the final atgrp tables
3174 grp_no_index := g_qual_grouping_no_tmp_tbl.first;
3175 while grp_no_index is not null
3176 LOOP
3177
3178 /*
3179 if g_call_from_setup = 'Y' then
3180 oe_debug_pub.add('grouping no = ' || grp_no_index);
3181 else
3182 write_log( 'grouping no = ' || grp_no_index);
3183 end if;
3184 */
3185 -- if other groups exists then skip -1 record i.e. do not move temp table data to final table
3186 write_log( 'Moving data from tmp to final for '||g_list_line_id_tmp_tbl(grp_no_index));
3187 write_log( 'l_other_grp_exists = ' || l_other_grp_exists);
3188 write_log( 'grp_no_index = ' || grp_no_index);
3189 write_log( 'g_pat_string_tmp_tbl(grp_no_index) ' || g_pat_string_tmp_tbl(grp_no_index));
3190 write_log( 'g_attribute_type_tmp_tbl(grp_no_index) '
3191 || g_attribute_type_tmp_tbl(grp_no_index));
3192 write_log( 'p_pattern_type = ' || p_pattern_type);
3193
3194
3195 if l_other_grp_exists = 'Y' and grp_no_index = -1 then
3196 null;
3197 else
3198 if g_pat_string_tmp_tbl(grp_no_index) is not null then
3199 l_pattern_id := get_pattern_id(p_pattern_type, g_pat_string_tmp_tbl(grp_no_index),
3200 grp_no_index);
3201 ELSE
3202 write_log( 'in else');
3203 IF g_attribute_type_tmp_tbl(grp_no_index) = 'BLIN' THEN
3204 IF g_header_quals_exist_tmp_tbl(grp_no_index)= 'N' THEN
3205 IF g_list_line_id_tmp_tbl(grp_no_index) = -1 THEN
3206 l_pattern_id := -3;
3207 else
3208 l_pattern_id := -2;
3209 END IF;
3210 ELSE
3211 l_pattern_id := -3;
3212 END IF;
3216 g_hash_key_tmp_tbl(grp_no_index) := 'NOEQUALS';
3213 g_hash_key_tmp_tbl(grp_no_index) := 'BLIND';
3214 ELSE
3215 l_pattern_id := -1;
3217 END IF;
3218 end if;
3219 /*
3220 if g_call_from_setup = 'Y' then
3221 oe_debug_pub.add('Pattern_id='||l_pattern_id);
3222 else
3223 write_log( 'Pattern_id='||l_pattern_id);
3224 end if;
3225 */
3226
3227 -- maintain data in qp_pattern_phases
3228 if p_pattern_type in ('LP', 'PP') then
3229 Populate_Pattern_Phases(null, g_pricing_phase_id_tmp_tbl(grp_no_index), l_pattern_id);
3230 elsif p_pattern_type = 'HP' THEN
3231 Populate_Pattern_Phases(g_list_header_id_tmp_tbl(grp_no_index), null, l_pattern_id);
3232 end if;
3233 -- move the data from tmp tables to final tables for qp_attribute_groups
3234 /*
3235 if g_call_from_setup = 'Y' then
3236 oe_debug_pub.add('move the data from tmp tables to final tables for qp_attribute_groups');
3237 else
3238 write_log( 'move the data from tmp tables to final tables for qp_attribute_groups');
3239 end if;
3240 */
3241
3242 l_atgrp_final_index := g_list_header_id_final_tbl.count + 1;
3243
3244
3245 write_log( 'l_atgrp_final_index = ' || l_atgrp_final_index);
3246 write_log( 'g_pricing_phase_id_tmp_tbl(grp_no_index) = '
3247 || g_pricing_phase_id_tmp_tbl(grp_no_index));
3248
3249 g_list_header_id_final_tbl(l_atgrp_final_index) := g_list_header_id_tmp_tbl(grp_no_index);
3250 g_list_line_id_final_tbl(l_atgrp_final_index) := g_list_line_id_tmp_tbl(grp_no_index);
3251 g_active_flag_final_tbl(l_atgrp_final_index) := g_active_flag_tmp_tbl(grp_no_index);
3252 g_list_type_code_final_tbl(l_atgrp_final_index) := g_list_type_code_tmp_tbl(grp_no_index);
3253 g_st_date_active_q_final_tbl(l_atgrp_final_index) := g_start_date_active_q_tmp_tbl(grp_no_index);
3254 g_end_date_active_q_final_tbl(l_atgrp_final_index) := g_end_date_active_q_tmp_tbl(grp_no_index);
3255 g_pattern_id_final_tbl(l_atgrp_final_index) := l_pattern_id;
3256 g_currency_code_final_tbl(l_atgrp_final_index) := g_currency_code_tmp_tbl(grp_no_index);
3257 g_ask_for_flag_final_tbl(l_atgrp_final_index) := g_ask_for_flag_tmp_tbl(grp_no_index);
3258 g_header_limit_exists_fnl_tbl(l_atgrp_final_index) := g_header_limit_exists_tmp_tbl(grp_no_index);
3259 g_line_limit_exists_fnl_tbl(l_atgrp_final_index) := g_line_limit_exists_tmp_tbl(grp_no_index);
3260 g_source_system_code_final_tbl(l_atgrp_final_index) := g_source_system_code_tmp_tbl(grp_no_index);
3261 g_effec_precedence_final_tbl(l_atgrp_final_index) := g_effective_precedence_tmp_tbl(grp_no_index);
3262 g_qual_grouping_no_final_tbl(l_atgrp_final_index) := g_qual_grouping_no_tmp_tbl(grp_no_index);
3263 g_pricing_phase_id_final_tbl(l_atgrp_final_index) := g_pricing_phase_id_tmp_tbl(grp_no_index);
3264 g_modifier_lvl_code_final_tbl(l_atgrp_final_index) := g_modifier_level_code_tmp_tbl(grp_no_index);
3265 g_hash_key_final_tbl(l_atgrp_final_index) := g_hash_key_tmp_tbl(grp_no_index);
3266 g_start_date_act_firs_fnl_tbl(l_atgrp_final_index) := g_start_date_act_firs_tmp_tbl(grp_no_index);
3267 g_end_date_act_firs_fnl_tbl(l_atgrp_final_index) := g_end_date_act_firs_tmp_tbl(grp_no_index);
3268 g_start_date_act_sec_fnl_tbl(l_atgrp_final_index) := g_start_date_act_sec_tmp_tbl(grp_no_index);
3269 g_end_date_act_sec_fnl_tbl(l_atgrp_final_index) := g_end_date_act_sec_tmp_tbl(grp_no_index);
3270 g_start_date_act_h_fnl_tbl(l_atgrp_final_index) := g_start_date_act_h_tmp_tbl(grp_no_index);
3271 g_end_date_act_h_fnl_tbl(l_atgrp_final_index) := g_end_date_act_h_tmp_tbl(grp_no_index);
3272 g_start_date_act_l_fnl_tbl(l_atgrp_final_index) := g_start_date_act_l_tmp_tbl(grp_no_index);
3273 g_end_date_act_l_fnl_tbl(l_atgrp_final_index) := g_end_date_act_l_tmp_tbl(grp_no_index);
3274 g_act_date_first_type_fnl_tbl(l_atgrp_final_index) := g_act_date_firs_type_tmp_tbl(grp_no_index);
3275 g_act_date_sec_type_fnl_tbl(l_atgrp_final_index) := g_act_date_sec_type_tmp_tbl(grp_no_index);
3276 g_currency_header_id_fnl_tbl(l_atgrp_final_index) := g_currency_header_id_tmp_tbl(grp_no_index);
3277 g_other_oprt_count_fnl_tbl(l_atgrp_final_index) := g_other_oprt_count_tmp_tbl(grp_no_index);
3278 g_null_other_oprt_cnt_fnl_tbl(l_atgrp_final_index) := g_null_other_oprt_cnt_tmp_tbl(grp_no_index);
3279 g_pte_code_fnl_tbl(l_atgrp_final_index) := g_pte_code_tmp_tbl(grp_no_index);
3280 g_prc_brk_typ_code_fnl_tbl(l_atgrp_final_index) := g_prc_brk_typ_code_tmp_tbl(grp_no_index);
3281 g_list_line_typ_code_fnl_tbl(l_atgrp_final_index) := g_list_line_typ_code_tmp_tbl(grp_no_index);
3282 g_automatic_flag_fnl_tbl(l_atgrp_final_index) := g_automatic_flag_tmp_tbl(grp_no_index);
3283
3284
3285
3286 -- if there is no product attached to a line then cache_key should contain only list_header_id
3287 if g_cache_key_tmp_tbl(grp_no_index) is null and p_pattern_type in ('LP', 'PP') then
3288 g_cache_key_final_tbl(l_atgrp_final_index) := g_list_header_id_tmp_tbl(grp_no_index);
3289
3290 select product_precedence into l_product_precedence
3291 from qp_list_lines where list_line_id = g_list_line_id_final_tbl(l_atgrp_final_index);
3292
3293 if (l_product_precedence is not null) and (g_effec_precedence_final_tbl(l_atgrp_final_index) is not null)
3294 and (l_product_precedence < g_effec_precedence_final_tbl(l_atgrp_final_index)) then
3295 g_effec_precedence_final_tbl(l_atgrp_final_index) := l_product_precedence;
3296 end if;
3297 /*
3298 if g_call_from_setup = 'Y' then
3299 oe_debug_pub.add('cache_key should be just list_header_id ');
3300 else
3301 write_log( 'cache_key should be just list_header_id ');
3302 end if;
3303 */
3304 else
3305 g_cache_key_final_tbl(l_atgrp_final_index) := g_cache_key_tmp_tbl(grp_no_index);
3306 /*
3310 write_log( 'cache_key should be standard OR null');
3307 if g_call_from_setup = 'Y' then
3308 oe_debug_pub.add('cache_key should be standard OR null');
3309 else
3311 end if;
3312 */
3313 end if;
3314 ----------- Added for PL/SQL Pattern Search
3315 g_header_quals_exist_fnl_tbl(l_atgrp_final_index) := g_header_quals_exist_tmp_tbl(grp_no_index);
3316 g_orig_org_id_fnl_tbl(l_atgrp_final_index) := g_orig_org_id_tmp_tbl(grp_no_index);
3317 g_global_flag_fnl_tbl(l_atgrp_final_index) := g_global_flag_tmp_tbl(grp_no_index);
3318 g_eq_flag_fnl_tbl(l_atgrp_final_index) := g_eq_flag_tmp_tbl(grp_no_index);
3319 g_desc_quals_exist_fnl_tbl(l_atgrp_final_index) := g_desc_quals_exist_tmp_tbl(grp_no_index);
3320 ---- Added for PL/SQL Pattern Search
3321
3322 g_product_uom_code_final_tbl(l_atgrp_final_index) := g_product_uom_code_tmp_tbl(grp_no_index);
3323 g_pricing_attr_count_final_tbl(l_atgrp_final_index) := g_pricing_attr_count_tmp_tbl(grp_no_index);
3324
3325 -- populate the standard who columns
3326 ------------- Added for PL/SQL Pattern Search
3327
3328 IF ( p_pattern_type = 'PP') THEN
3329
3330 g_created_by_final_tbl(l_atgrp_final_index) := g_created_by_tmp_tbl(grp_no_index);
3331
3332 g_creation_date_final_tbl(l_atgrp_final_index) := g_creation_date_tmp_tbl(grp_no_index);
3333
3334 g_request_id_fnl_tbl(l_atgrp_final_index) := g_request_id_tmp_tbl(grp_no_index);
3335 g_program_update_date_fnl_tbl(l_atgrp_final_index) := g_program_update_date_tmp_tbl(grp_no_index);
3336 g_program_id_fnl_tbl(l_atgrp_final_index) := g_program_id_tmp_tbl(grp_no_index);
3337 g_program_applic_id_fnl_tbl(l_atgrp_final_index) := g_program_applic_id_tmp_tbl(grp_no_index);
3338 g_effec_precedence_final_tbl(l_atgrp_final_index) := g_effective_precedence_tmp_tbl(grp_no_index);
3339 g_list_type_code_final_tbl(l_atgrp_final_index) := g_list_type_code_tmp_tbl(grp_no_index);
3340 g_currency_code_final_tbl(l_atgrp_final_index) := g_currency_code_tmp_tbl(grp_no_index);
3341 g_ask_for_flag_final_tbl(l_atgrp_final_index) := g_ask_for_flag_tmp_tbl(grp_no_index);
3342 g_source_system_code_final_tbl(l_atgrp_final_index) := g_source_system_code_tmp_tbl(grp_no_index);
3343 g_orig_org_id_fnl_tbl(l_atgrp_final_index) := g_orig_org_id_tmp_tbl(grp_no_index);
3344 g_global_flag_fnl_tbl(l_atgrp_final_index) := g_global_flag_tmp_tbl(grp_no_index);
3345 ELSE
3346 g_creation_date_final_tbl(l_atgrp_final_index) := sysdate;
3347 g_created_by_final_tbl(l_atgrp_final_index) := FND_GLOBAL.USER_ID;
3348
3349 END IF;
3350
3351 ---------------- Added for PL/SQL Pattern Search
3352 g_last_update_date_final_tbl(l_atgrp_final_index) := sysdate;
3353 g_last_updated_by_final_tbl(l_atgrp_final_index) := FND_GLOBAL.USER_ID;
3354 g_last_update_login_final_tbl(l_atgrp_final_index) := FND_GLOBAL.LOGIN_ID;
3355 g_program_appl_id_final_tbl(l_atgrp_final_index) := FND_GLOBAL.PROG_APPL_ID;
3356 g_program_id_final_tbl(l_atgrp_final_index) := FND_GLOBAL.CONC_PROGRAM_ID;
3357 g_program_upd_date_final_tbl(l_atgrp_final_index) := sysdate;
3358 g_request_id_final_tbl(l_atgrp_final_index) := FND_GLOBAL.CONC_REQUEST_ID;
3359 end if; -- l_other_grp_exists = 'Y' and grp_no_index = -1
3360
3361 grp_no_index := g_qual_grouping_no_tmp_tbl.next(grp_no_index);
3362
3363 END LOOP; -- while
3364 /*
3365 if g_call_from_setup = 'Y' then
3366 oe_debug_pub.add('End Moving data from temp table to final table');
3367 else
3368 write_log( 'End Moving data from temp table to final table');
3369 end if;
3370 */
3371
3372 EXCEPTION
3373 WHEN OTHERS THEN
3374 if g_call_from_setup = 'Y' then
3375 oe_debug_pub.add('PS_ATTR_GRP_PVT.Move_Data_From_Tmp_To_Final ' || SQLERRM);
3376 else
3377 write_log( 'PS_ATTR_GRP_PVT.Move_Data_From_Tmp_To_Final ' || SQLERRM );
3378 end if;
3379
3380 END Move_data_from_tmp_to_final;
3381
3382 -- bulk insert patterns into qp_patterns table
3383 PROCEDURE Populate_Patterns
3384 is
3385 BEGIN
3386
3387 /*
3388 if g_call_from_setup = 'Y' then
3389 oe_debug_pub.add('Begin Populate_patterns');
3390 else
3391 write_log( 'Begin Populate_patterns');
3392 end if;
3393 */
3394
3395 FORALL i in 1 .. G_pattern_pattern_id_final_tbl.count
3396 INSERT INTO qp_patterns
3397 (
3398 pattern_id,
3399 segment_id,
3400 segment_index,
3401 pattern_type,
3402 pattern_string,
3403 creation_date,
3404 created_by,
3405 last_update_date,
3406 last_updated_by,
3407 last_update_login,
3408 program_application_id,
3409 program_id,
3410 program_update_date,
3411 request_id
3412 )
3413 VALUES
3414 (
3415 g_pattern_pattern_id_final_tbl(i),
3416 g_pattern_segment_id_final_tbl(i),
3417 g_pattern_segment_ind_fnl_tbl(i),
3418 g_pattern_pat_type_final_tbl(i),
3419 g_pattern_pat_string_final_tbl(i),
3420 g_pattern_cr_dt_final_tbl(i),
3421 g_pattern_cr_by_final_tbl(i),
3422 g_pattern_lst_up_dt_final_tbl(i),
3423 g_pattern_lt_up_by_final_tbl(i),
3424 g_pattern_lt_up_lg_final_tbl(i),
3425 g_pattern_pr_ap_id_final_tbl(i),
3426 g_pattern_pr_id_final_tbl(i),
3427 g_pattern_pr_up_dt_final_tbl(i),
3428 g_pattern_req_id_final_tbl(i)
3429 );
3430
3431 g_pattern_pattern_id_final_tbl.delete;
3432 g_pattern_segment_id_final_tbl.delete;
3433 g_pattern_pat_type_final_tbl.delete;
3434 g_pattern_pat_string_final_tbl.delete;
3435 g_pattern_cr_dt_final_tbl.delete;
3436 g_pattern_cr_by_final_tbl.delete;
3437 g_pattern_lst_up_dt_final_tbl.delete;
3441 g_pattern_pr_id_final_tbl.delete;
3438 g_pattern_lt_up_by_final_tbl.delete;
3439 g_pattern_lt_up_lg_final_tbl.delete;
3440 g_pattern_pr_ap_id_final_tbl.delete;
3442 g_pattern_pr_up_dt_final_tbl.delete;
3443 g_pattern_req_id_final_tbl.delete;
3444 g_pattern_segment_ind_fnl_tbl.delete;
3445
3446 /*
3447 if g_call_from_setup = 'Y' then
3448 oe_debug_pub.add('End Populate_patterns');
3449 else
3450 commit;
3451 write_log( 'End Populate_patterns');
3452 end if;
3453 */
3454 EXCEPTION
3455 WHEN OTHERS THEN
3456 if g_call_from_setup = 'Y' then
3457 oe_debug_pub.add('PS_ATTR_GRP_PVT.Populate_Patterns ' || SQLERRM);
3458 else
3459 write_log( 'PS_ATTR_GRP_PVT.Populate_Patterns ' || SQLERRM );
3460 end if;
3461 raise;
3462 END Populate_Patterns;
3463
3464 -- bulk update qp_list_lines table
3465 PROCEDURE update_list_lines
3466 is
3467 BEGIN
3468
3469 FORALL i in 1 .. g_list_line_id_final_tbl.count
3470 UPDATE /*+ index(lines QP_LIST_LINES_PK) */ qp_list_lines lines
3471 set pattern_id = g_pattern_id_final_tbl(i),
3472 product_uom_code = g_product_uom_code_final_tbl(i),
3473 pricing_attribute_count = g_pricing_attr_count_final_tbl(i),
3474 hash_key = g_hash_key_final_tbl(i),
3475 cache_key = g_cache_key_final_tbl(i),
3476 last_update_date = g_last_update_date_final_tbl(i),
3477 last_updated_by = g_last_updated_by_final_tbl(i),
3478 last_update_login = g_last_update_login_final_tbl(i),
3479 eq_flag = g_eq_flag_fnl_tbl(i),
3480 null_other_oprt_count = g_null_other_oprt_cnt_fnl_tbl(i),
3481 pte_code = g_pte_code_fnl_tbl(i),
3482 source_system_code = g_source_system_code_final_tbl(i)
3483 where list_header_id = g_list_header_id_final_tbl(i)
3484 AND list_line_id = g_list_line_id_final_tbl(i)
3485 --- Added for PL/SQL Pattern Search
3486 AND g_pricing_phase_id_final_tbl(i)= 1; --- for price lists only
3487 ------ Added for PL/SQL Pattern Search
3488 if g_call_from_setup = 'Y' then
3489 /*Populate row in qp_list_heder_phases when no qualifer is
3490 present for lines or headers for that phase and will poulate 'Y' for PRIC_PROD_ATTR_ONLY_FLAG*/
3491
3492 FORALL i in 1 .. g_list_line_id_final_tbl.COUNT
3493 insert into qp_list_header_phases (list_header_id,pricing_phase_id,PRIC_PROD_ATTR_ONLY_FLAG)
3494 (select /*+ index(QP_LIST_LINES QP_LIST_LINES_U1) */ distinct list_header_id, pricing_phase_id,'Y'
3495 from qp_list_lines
3496 where pricing_phase_id > 1
3497 and qualification_ind in (0,4,20)
3498 and list_header_id = g_list_header_id_final_tbl(i)
3499 MINUS
3500 SELECT distinct list_header_id, pricing_phase_id,'Y'
3501 FROM qp_list_header_phases
3502 where list_header_id = g_list_header_id_final_tbl(i)
3503 );
3504 END if;
3505 EXCEPTION
3506 WHEN OTHERS THEN
3507 if g_call_from_setup = 'Y' then
3508 oe_debug_pub.add('PS_ATTR_GRP_PVT.Update_List_Lines ' || SQLERRM);
3509 else
3510 write_log( 'PS_ATTR_GRP_PVT.Update_List_Lines ' || SQLERRM );
3511 end if;
3512
3513 end update_list_lines;
3514
3515 ----------- Added for PL/SQL Pattern Search
3516
3517 PROCEDURE populate_pp_atgrps
3518 is
3519 BEGIN
3520
3521 oe_debug_pub.add('total count - ' || g_list_line_id_final_tbl.COUNT);
3522 FORALL i in 1 .. g_list_line_id_final_tbl.COUNT
3523 INSERT INTO qp_attribute_groups
3524
3525 (list_header_id,
3526 list_line_id,
3527 pattern_id,
3528 product_uom_code,
3529 hash_key,
3530 cache_key,
3531 last_update_date,
3532 last_updated_by,
3533 last_update_login,
3534 created_by,
3535 creation_date,
3536 request_id,
3537 program_update_date,
3538 program_id,
3539 program_application_id,
3540 pricing_phase_id,
3541 modifier_level_code,
3542 header_limit_exists,
3543 line_limit_exists,
3544 effective_precedence,
3545 active_flag,
3546 list_type_code,
3547 currency_code,
3548 ask_for_flag,
3549 source_system_code,
3550 orig_org_id,
3551 global_flag,
3552 eq_flag,
3553 descendents_quals_exist,
3554 grouping_no,
3555 start_date_active_first,
3556 end_date_active_first,
3557 start_date_active_second,
3558 end_date_active_second,
3559 start_date_active_h,
3560 end_date_active_h,
3561 start_date_active_l,
3562 end_date_active_l,
3563 header_quals_exist_flag,
3564 active_date_first_type,
3565 active_date_second_type,
3566 currency_header_id,
3567 other_oprt_count,
3568 null_other_oprt_count,
3569 pte_code,
3570 price_break_type_code,
3571 list_line_type_code,
3572 automatic_flag)
3573 SELECT g_list_header_id_final_tbl(i),
3574 g_list_line_id_final_tbl(i),
3575 g_pattern_id_final_tbl(i),
3576 g_product_uom_code_final_tbl(i),
3577 g_hash_key_final_tbl(i),
3578 g_cache_key_final_tbl(i),
3579 g_last_update_date_final_tbl(i),
3580 g_last_updated_by_final_tbl(i),
3581 g_last_update_login_final_tbl(i),
3582 g_created_by_final_tbl(i),
3583 g_creation_date_final_tbl(i),
3584 g_request_id_fnl_tbl(i),
3585 g_program_update_date_fnl_tbl(i),
3586 g_program_id_fnl_tbl(i),
3587 g_program_applic_id_fnl_tbl(i),
3588 g_pricing_phase_id_final_tbl(i),
3589 g_modifier_lvl_code_final_tbl(i),
3590 g_header_limit_exists_fnl_tbl(i),
3591 g_line_limit_exists_fnl_tbl(i),
3592 g_effec_precedence_final_tbl(i),
3596 g_ask_for_flag_final_tbl(i),
3593 g_active_flag_final_tbl(i),
3594 g_list_type_code_final_tbl(i),
3595 g_currency_code_final_tbl(i),
3597 g_source_system_code_final_tbl(i),
3598 g_orig_org_id_fnl_tbl(i),
3599 g_global_flag_fnl_tbl(i),
3600 g_eq_flag_fnl_tbl(i) ,
3601 g_desc_quals_exist_fnl_tbl(i),
3602 -1,
3603 g_start_date_act_firs_fnl_tbl(i),
3604 g_end_date_act_firs_fnl_tbl(i),
3605 g_start_date_act_sec_fnl_tbl(i),
3606 g_end_date_act_sec_fnl_tbl(i),
3607 g_start_date_act_h_fnl_tbl(i),
3608 g_end_date_act_h_fnl_tbl(i),
3609 g_start_date_act_l_fnl_tbl(i),
3610 g_end_date_act_l_fnl_tbl(i),
3611 g_header_quals_exist_fnl_tbl(i),
3612 g_act_date_first_type_fnl_tbl(i),
3613 g_act_date_sec_type_fnl_tbl(i),
3614 g_currency_header_id_fnl_tbl(i),
3615 g_other_oprt_count_fnl_tbl(i),
3616 g_null_other_oprt_cnt_fnl_tbl(i),
3617 g_pte_code_fnl_tbl(i),
3618 g_prc_brk_typ_code_fnl_tbl(i),
3619 g_list_line_typ_code_fnl_tbl(i),
3620 g_automatic_flag_fnl_tbl(i) FROM dual WHERE g_pricing_phase_id_final_tbl(i) <> 1 ;
3621 EXCEPTION
3622 WHEN OTHERS THEN
3623 if g_call_from_setup = 'Y' then
3624 oe_debug_pub.add('PS_ATTR_GRP_PVT.populate_pp_atgrps ' || SQLERRM);
3625 else
3626 write_log( 'PS_ATTR_GRP_PVT.populate_pp_atgrps ' || SQLERRM );
3627 end if;
3628
3629 end populate_pp_atgrps;
3630
3631 --------Added for PL/SQL Pattern Search
3632 -- bulk update qp_list_lines.cache_key for line patterns
3633 PROCEDURE update_list_lines_cache_key
3634 is
3635 BEGIN
3636
3637 FORALL i in 1 .. g_list_line_id_final_tbl.count
3638 UPDATE qp_list_lines
3639 set cache_key = g_cache_key_final_tbl(i),
3640 last_update_date = g_last_update_date_final_tbl(i),
3641 last_updated_by = g_last_updated_by_final_tbl(i),
3642 last_update_login = g_last_update_login_final_tbl(i)
3643 where list_line_id = g_list_line_id_final_tbl(i)
3644 AND pricing_phase_id = 1;
3645
3646 EXCEPTION
3647 WHEN OTHERS THEN
3648 if g_call_from_setup = 'Y' then
3649 oe_debug_pub.add('PS_ATTR_GRP_PVT.Update_List_Lines_Cache_Key ' || SQLERRM);
3650 else
3651 write_log( 'PS_ATTR_GRP_PVT.Update_List_Lines_Cache_Key ' || SQLERRM );
3652 end if;
3653
3654 end update_list_lines_cache_key;
3655
3656 -- bulk insert patterns into qp_patterns table
3657 PROCEDURE Populate_Atgrps
3658 is
3659 BEGIN
3660
3661 FORALL i in 1 .. g_list_header_id_final_tbl.count
3662 INSERT INTO qp_attribute_groups
3663 (list_header_id,
3664 list_line_id,
3665 active_flag,
3666 list_type_code,
3667 start_date_active_q,
3668 end_date_active_q,
3669 pattern_id,
3670 header_quals_exist_flag,
3671 orig_org_id,
3672 global_flag,
3673 product_uom_code,
3674 currency_code,
3675 ask_for_flag,
3676 header_limit_exists,
3677 line_limit_exists,
3678 source_system_code,
3679 effective_precedence,
3680 grouping_no,
3681 pricing_phase_id,
3682 modifier_level_code,
3683 hash_key,
3684 cache_key,
3685 creation_date,
3686 created_by,
3687 last_update_date,
3688 last_updated_by,
3689 last_update_login,
3690 program_application_id,
3691 program_id,
3692 program_update_date,
3693 request_id,
3694 eq_flag,
3695 descendents_quals_exist,
3696 start_date_active_first,
3697 end_date_active_first,
3698 start_date_active_second,
3699 end_date_active_second,
3700 start_date_active_h,
3701 end_date_active_h,
3702 start_date_active_l,
3703 end_date_active_l,
3704 active_date_first_type,
3705 active_date_second_type,
3706 currency_header_id,
3707 other_oprt_count,
3708 null_other_oprt_count,
3709 pte_code,
3710 price_break_type_code,
3711 list_line_type_code,
3712 automatic_flag
3713 )
3714 VALUES
3715 (g_list_header_id_final_tbl(i),
3716 g_list_line_id_final_tbl(i),
3717 g_active_flag_final_tbl(i),
3718 g_list_type_code_final_tbl(i),
3719 g_st_date_active_q_final_tbl(i),
3720 g_end_date_active_q_final_tbl(i),
3721 g_pattern_id_final_tbl(i),
3722 ----------- Added for PL/SQL Pattern Search
3723 g_header_quals_exist_fnl_tbl(i),
3724 g_orig_org_id_fnl_tbl(i),
3725 g_global_flag_fnl_tbl(i),
3726 g_product_uom_code_final_tbl(i),
3727 ----------- Added for PL/SQL Pattern Search
3728 g_currency_code_final_tbl(i),
3729 g_ask_for_flag_final_tbl(i),
3730 g_header_limit_exists_fnl_tbl(i),
3731 g_line_limit_exists_fnl_tbl(i),
3732 g_source_system_code_final_tbl(i),
3733 g_effec_precedence_final_tbl(i),
3734 g_qual_grouping_no_final_tbl(i),
3735 g_pricing_phase_id_final_tbl(i),
3736 g_modifier_lvl_code_final_tbl(i),
3737 g_hash_key_final_tbl(i),
3738 g_cache_key_final_tbl(i),
3739 g_creation_date_final_tbl(i),
3740 g_created_by_final_tbl(i),
3741 g_last_update_date_final_tbl(i),
3742 g_last_updated_by_final_tbl(i),
3743 g_last_update_login_final_tbl(i),
3744 g_program_appl_id_final_tbl(i),
3745 g_program_id_final_tbl(i),
3746 g_program_upd_date_final_tbl(i),
3747 g_request_id_final_tbl(i),
3748 g_eq_flag_fnl_tbl(i) ,
3749 g_desc_quals_exist_fnl_tbl(i),
3750 g_start_date_act_firs_fnl_tbl(i),
3751 g_end_date_act_firs_fnl_tbl(i),
3752 g_start_date_act_sec_fnl_tbl(i),
3753 g_end_date_act_sec_fnl_tbl(i),
3754 g_start_date_act_h_fnl_tbl(i),
3755 g_end_date_act_h_fnl_tbl(i),
3759 g_act_date_sec_type_fnl_tbl(i),
3756 g_start_date_act_l_fnl_tbl(i),
3757 g_end_date_act_l_fnl_tbl(i),
3758 g_act_date_first_type_fnl_tbl(i),
3760 g_currency_header_id_fnl_tbl(i),
3761 g_other_oprt_count_fnl_tbl(i),
3762 g_null_other_oprt_cnt_fnl_tbl(i),
3763 g_pte_code_fnl_tbl(i),
3764 g_prc_brk_typ_code_fnl_tbl(i),
3765 g_list_line_typ_code_fnl_tbl(i),
3766 g_automatic_flag_fnl_tbl(i)
3767 );
3768
3769 if g_call_from_setup = 'Y' then
3770 /*Populate row in qp_list_heder_phases when no qualifer is
3771 present for lines or headers for that phase and will poulate 'Y' for PRIC_PROD_ATTR_ONLY_FLAG*/
3772
3773 FORALL i in 1 .. g_list_line_id_final_tbl.COUNT
3774 insert into qp_list_header_phases (list_header_id,pricing_phase_id,PRIC_PROD_ATTR_ONLY_FLAG)
3775 (select /*+ index(QP_LIST_LINES QP_LIST_LINES_U1) */ distinct list_header_id, pricing_phase_id,'Y'
3776 from qp_list_lines
3777 where pricing_phase_id > 1
3778 and qualification_ind in (0,4,20)
3779 and list_header_id = g_list_header_id_final_tbl(i)
3780 MINUS
3781 SELECT distinct list_header_id, pricing_phase_id,'Y'
3782 FROM qp_list_header_phases
3783 where list_header_id = g_list_header_id_final_tbl(i)
3784 );
3785 END if;
3786
3787 EXCEPTION
3788 WHEN OTHERS THEN
3789 if g_call_from_setup = 'Y' then
3790 oe_debug_pub.add('PS_ATTR_GRP_PVT.Populate_Atgrps ' || SQLERRM);
3791 else
3792 write_log( 'PS_ATTR_GRP_PVT.Populate_Atgrps ' || SQLERRM );
3793 end if;
3794 raise;
3795 END Populate_Atgrps;
3796
3797 Procedure Reset_tmp_tables
3798 is
3799 begin
3800 /*
3801 if g_call_from_setup = 'Y' then
3802 oe_debug_pub.add('Reset temp tables');
3803 else
3804 write_log( 'Reset temp tables');
3805 end if;
3806 */
3807 g_list_header_id_tmp_tbl.delete;
3808 g_list_line_id_tmp_tbl.delete;
3809 g_active_flag_tmp_tbl.delete;
3810 g_list_type_code_tmp_tbl.delete;
3811 g_start_date_active_q_tmp_tbl.delete;
3812 g_end_date_active_q_tmp_tbl.delete;
3813 ------- Added for PL/SQL Pattern Search
3814 g_header_quals_exist_tmp_tbl.delete;
3815 g_orig_org_id_tmp_tbl.delete;
3816 g_global_flag_tmp_tbl.delete;
3817 g_product_uom_code_j_tmp_tbl.delete;
3818 g_product_uom_code_tmp_tbl.delete;
3819 g_creation_date_tmp_tbl.delete;
3820 g_created_by_tmp_tbl.delete;
3821 g_request_id_tmp_tbl.delete;
3822 g_program_update_date_tmp_tbl.delete;
3823 g_program_id_tmp_tbl.delete;
3824 g_program_applic_id_tmp_tbl.delete;
3825 g_start_date_act_firs_tmp_tbl.delete;
3826 g_end_date_act_firs_tmp_tbl.delete;
3827 g_start_date_act_sec_tmp_tbl.delete;
3828 g_end_date_act_sec_tmp_tbl.delete;
3829 g_start_date_act_h_tmp_tbl.delete;
3830 g_end_date_act_h_tmp_tbl.delete;
3831 g_start_date_act_l_tmp_tbl.delete;
3832 g_end_date_act_l_tmp_tbl.delete;
3833 g_eq_flag_tmp_tbl.delete;
3834 g_act_date_firs_type_tmp_tbl.delete;
3835 g_act_date_sec_type_tmp_tbl.delete;
3836 g_currency_header_id_tmp_tbl.delete;
3837 g_pte_code_tmp_tbl.delete;
3838 g_desc_quals_exist_tmp_tbl.delete;
3839 g_prc_brk_typ_code_tmp_tbl.delete;
3840 g_list_line_typ_code_tmp_tbl.delete;
3841 g_automatic_flag_tmp_tbl.delete;
3842 --------Added for PL/SQL Pattern Search
3843 g_currency_code_tmp_tbl.delete;
3844 g_ask_for_flag_tmp_tbl.delete;
3845 g_header_limit_exists_tmp_tbl.delete;
3846 g_line_limit_exists_tmp_tbl.delete;
3847 g_source_system_code_tmp_tbl.delete;
3848 g_effective_precedence_tmp_tbl.delete;
3849 g_qual_grouping_no_tmp_tbl.delete;
3850 g_pricing_phase_id_tmp_tbl.delete;
3851 g_modifier_level_code_tmp_tbl.delete;
3852 g_hash_key_tmp_tbl.delete;
3853 g_cache_key_tmp_tbl.delete;
3854 g_pat_string_tmp_tbl.delete;
3855
3856 g_pattern_grouping_no_tmp_tbl.delete;
3857 g_pattern_segment_id_tmp_tbl.delete;
3858
3859 g_product_uom_code_tmp_tbl.delete;
3860 g_pricing_attr_count_tmp_tbl.delete;
3861 g_other_oprt_count_tmp_tbl.delete;
3862 g_null_other_oprt_cnt_tmp_tbl.delete;
3863 end Reset_tmp_tables;
3864
3865 function get_pattern_id(p_pattern_type varchar2, p_pat_string varchar2,
3866 p_grp_no number)
3867 return number
3868 is
3869 l_pattern_to_be_created varchar2(1);
3870 l_pattern_id number;
3871 l_pattern_final_index number;
3872 begin
3873 /*
3874 if g_call_from_setup = 'Y' then
3875 oe_debug_pub.add('Begin get_pattern_id');
3876 else
3877 write_log( 'Begin get_pattern_id');
3878 end if;
3879 */
3880 begin
3881 l_pattern_to_be_created := 'N';
3882 select /*+ index(qp_pat QP_PATTERNS_N1) */ pattern_id
3883 into l_pattern_id
3884 from qp_patterns qp_pat
3885 where pattern_string = p_pat_string
3886 -- and pattern_type = p_pattern_type
3887 and rownum = 1;
3888 exception
3889 when no_data_found then
3890 select qp_patterns_s.nextval into l_pattern_id from dual;
3891 l_pattern_to_be_created := 'Y';
3892
3893 when others then
3894 raise;
3895 end;
3896
3897 /*
3898 if g_call_from_setup = 'Y' then
3899 oe_debug_pub.add('l_pattern_to_be_created='||l_pattern_to_be_created);
3900 else
3901 write_log( 'l_pattern_to_be_created='||l_pattern_to_be_created);
3902 end if;
3903 */
3904
3905 -- move the data from temp tables to final tables for qp_patterns, if new pattern to be created
3906 if l_pattern_to_be_created = 'Y' then
3907 for k in 1..g_pattern_grouping_no_tmp_tbl.count
3908 loop
3912 g_pattern_pattern_id_final_tbl(l_pattern_final_index) := l_pattern_id;
3909 if (g_pattern_grouping_no_tmp_tbl(k) = -1 or g_pattern_grouping_no_tmp_tbl(k) = p_grp_no) then
3910 l_pattern_final_index := g_pattern_pattern_id_final_tbl.count + 1;
3911
3913 g_pattern_segment_id_final_tbl(l_pattern_final_index) := g_pattern_segment_id_tmp_tbl(k);
3914 g_pattern_segment_ind_fnl_tbl(l_pattern_final_index) := l_pattern_final_index;
3915 g_pattern_pat_type_final_tbl(l_pattern_final_index) := p_pattern_type;
3916 g_pattern_pat_string_final_tbl(l_pattern_final_index) := p_pat_string;
3917
3918 g_pattern_cr_dt_final_tbl(l_pattern_final_index) := sysdate;
3919 g_pattern_cr_by_final_tbl(l_pattern_final_index) := FND_GLOBAL.USER_ID;
3920 g_pattern_lst_up_dt_final_tbl(l_pattern_final_index) := sysdate;
3921 g_pattern_lt_up_by_final_tbl(l_pattern_final_index) := FND_GLOBAL.USER_ID;
3922 g_pattern_lt_up_lg_final_tbl(l_pattern_final_index) := FND_GLOBAL.LOGIN_ID;
3923 g_pattern_pr_ap_id_final_tbl(l_pattern_final_index) := FND_GLOBAL.PROG_APPL_ID;
3924 g_pattern_pr_id_final_tbl(l_pattern_final_index) := FND_GLOBAL.CONC_PROGRAM_ID;
3925 g_pattern_pr_up_dt_final_tbl(l_pattern_final_index) := sysdate;
3926 g_pattern_req_id_final_tbl(l_pattern_final_index) := FND_GLOBAL.CONC_REQUEST_ID;
3927 end if;
3928 end loop; --k in 1..g_pattern_grouping_no_tmp_tbl.count
3929 populate_patterns;
3930 end if; -- l_pattern_to_be_created = 'Y'
3931 /*
3932 if g_call_from_setup = 'Y' then
3933 oe_debug_pub.add('End get_pattern_id');
3934 else
3935 write_log( 'End get_pattern_id');
3936 end if;
3937 */
3938
3939 return l_pattern_id;
3940
3941 EXCEPTION
3942 WHEN OTHERS THEN
3943 if g_call_from_setup = 'Y' then
3944 oe_debug_pub.add('PS_ATTR_GRP_PVT.Get_Pattern_Id ' || SQLERRM);
3945 else
3946 write_log( 'PS_ATTR_GRP_PVT.Get_Pattern_Id ' || SQLERRM );
3947 end if;
3948
3949 end get_pattern_id;
3950
3951 procedure reset_final_tables
3952 is
3953 begin
3954 g_list_header_id_final_tbl.delete;
3955 g_list_line_id_final_tbl.delete;
3956 g_active_flag_final_tbl.delete;
3957 g_list_type_code_final_tbl.delete;
3958 g_st_date_active_q_final_tbl.delete;
3959 g_end_date_active_q_final_tbl.delete;
3960 g_pattern_id_final_tbl.delete;
3961 g_currency_code_final_tbl.delete;
3962 ------- Added for PL/SQL Pattern Search
3963 g_header_quals_exist_fnl_tbl.delete;
3964 g_orig_org_id_fnl_tbl.delete;
3965 g_global_flag_fnl_tbl.delete;
3966 g_product_uom_code_j_fnl_tbl.delete;
3967 g_request_id_fnl_tbl.delete;
3968 g_program_update_date_fnl_tbl.delete;
3969 g_program_id_fnl_tbl.delete;
3970 g_program_applic_id_fnl_tbl.delete;
3971 g_start_date_act_firs_fnl_tbl.delete;
3972 g_end_date_act_firs_fnl_tbl.delete;
3973 g_start_date_act_sec_fnl_tbl.delete;
3974 g_end_date_act_sec_fnl_tbl.delete;
3975 g_start_date_act_h_fnl_tbl.delete;
3976 g_end_date_act_h_fnl_tbl.delete;
3977 g_start_date_act_l_fnl_tbl.delete;
3978 g_end_date_act_l_fnl_tbl.delete;
3979 g_eq_flag_fnl_tbl.delete;
3980 g_act_date_first_type_fnl_tbl.delete;
3981 g_act_date_sec_type_fnl_tbl.delete;
3982 g_currency_header_id_fnl_tbl.delete;
3983 g_pte_code_fnl_tbl.delete;
3984 g_desc_quals_exist_fnl_tbl.delete;
3985 g_prc_brk_typ_code_fnl_tbl.delete;
3986 g_list_line_typ_code_fnl_tbl.delete;
3987 g_automatic_flag_fnl_tbl.delete;
3988 --------Added for PL/SQL Pattern Search
3989 g_ask_for_flag_final_tbl.delete;
3990 g_header_limit_exists_fnl_tbl.delete;
3991 g_line_limit_exists_fnl_tbl.delete;
3992 g_source_system_code_final_tbl.delete;
3993 g_effec_precedence_final_tbl.delete;
3994 g_qual_grouping_no_final_tbl.delete;
3995 g_pricing_phase_id_final_tbl.delete;
3996 g_modifier_lvl_code_final_tbl.delete;
3997 g_hash_key_final_tbl.delete;
3998 g_cache_key_final_tbl.delete;
3999 g_product_uom_code_final_tbl.delete;
4000 g_pricing_attr_count_final_tbl.delete;
4001 g_creation_date_final_tbl.delete;
4002 g_created_by_final_tbl.delete;
4003 g_last_update_date_final_tbl.delete;
4004 g_last_updated_by_final_tbl.delete;
4005 g_last_update_login_final_tbl.delete;
4006 g_program_appl_id_final_tbl.delete;
4007 g_program_id_final_tbl.delete;
4008 g_program_upd_date_final_tbl.delete;
4009 g_request_id_final_tbl.delete;
4010 g_null_other_oprt_cnt_fnl_tbl.delete;
4011 g_other_oprt_count_fnl_tbl.delete;
4012
4013 EXCEPTION
4014 WHEN OTHERS THEN
4015 if g_call_from_setup = 'Y' then
4016 oe_debug_pub.add('PS_ATTR_GRP_PVT.Reset_Final_Tables ' || SQLERRM);
4017 else
4018 write_log( 'PS_ATTR_GRP_PVT.Reset_Final_Tables ' || SQLERRM );
4019 end if;
4020
4021 end reset_final_tables;
4022
4023 procedure reset_c_tables
4024 is
4025 begin
4026 g_list_header_id_c_tbl.delete;
4027 g_list_line_id_c_tbl.delete;
4028 g_segment_id_c_tbl.delete;
4029 g_active_flag_c_tbl.delete;
4030 g_list_type_code_c_tbl.delete;
4031 g_start_date_active_q_c_tbl.delete;
4032 g_end_date_active_q_c_tbl.delete;
4033 ------- Added for PL/SQL Pattern Search
4034 g_header_quals_exist_c_tbl.delete;
4035 g_orig_org_id_c_tbl.delete;
4036 g_global_flag_c_tbl.delete;
4037 g_product_uom_code_j_c_tbl.delete;
4038 g_creation_date_c_tbl.delete;
4039 g_created_by_c_tbl.delete;
4040 g_request_id_c_tbl.delete;
4041 g_program_update_date_c_tbl.delete;
4042 g_program_id_c_tbl.delete;
4043 g_program_applic_id_c_tbl.delete;
4044 g_start_date_act_firs_c_tbl.delete;
4045 g_end_date_act_firs_c_tbl.delete;
4049 g_end_date_act_h_c_tbl.delete;
4046 g_start_date_act_sec_c_tbl.delete;
4047 g_end_date_act_sec_c_tbl.delete;
4048 g_start_date_act_h_c_tbl.delete;
4050 g_start_date_act_l_c_tbl.delete;
4051 g_end_date_act_l_c_tbl.delete;
4052 g_act_date_firs_type_c_tbl.delete;
4053 g_act_date_sec_type_c_tbl.delete;
4054 g_currency_header_id_c_tbl.delete;
4055 g_pte_code_c_tbl.delete;
4056 g_desc_quals_exist_c_tbl.delete;
4057 g_prc_brk_typ_code_c_tbl.delete;
4058 g_list_line_typ_code_c_tbl.delete;
4059 g_automatic_flag_c_tbl.delete;
4060 --------Added for PL/SQL Pattern Search
4061 g_currency_code_c_tbl.delete;
4062 g_ask_for_flag_c_tbl.delete;
4063 g_header_limit_exists_c_tbl.delete;
4064 g_line_limit_exists_c_tbl.delete;
4065 g_source_system_code_c_tbl.delete;
4066 g_effective_precedence_c_tbl.delete;
4067 g_qual_grouping_no_c_tbl.delete;
4068 g_comparison_opr_code_c_tbl.delete;
4069 g_pricing_phase_id_c_tbl.delete;
4070 g_modifier_level_code_c_tbl.delete;
4071 g_qual_datatype_c_tbl.delete;
4072 g_qual_attr_val_c_tbl.delete;
4073 g_attribute_type_c_tbl.delete;
4074 g_product_uom_code_c_tbl.delete;
4075
4076 EXCEPTION
4077 WHEN OTHERS THEN
4078 if g_call_from_setup = 'Y' then
4079 oe_debug_pub.add('PS_ATTR_GRP_PVT.Reset_C_Tables ' || SQLERRM);
4080 else
4081 write_log( 'PS_ATTR_GRP_PVT.Reset_C_Tables ' || SQLERRM );
4082 end if;
4083 end reset_c_tables;
4084
4085 PROCEDURE Populate_Pattern_Phases (
4086 p_list_header_id IN NUMBER,
4087 p_pricing_phase_id IN NUMBER,
4088 p_pattern_id IN NUMBER) IS
4089
4090 CURSOR l_phase_id_to_insert_csr IS
4091 SELECT distinct pricing_phase_id, list_header_id
4092 FROM qp_list_header_phases
4093 WHERE list_header_id = p_list_header_id;
4094
4095 l_exists varchar2(1);
4096
4097 l_prl_flag varchar2(1) := 'N';
4098 l_list_type varchar2(30);
4099
4100 BEGIN
4101
4102 IF p_list_header_id is not null then
4103 -- HP case
4104 /*
4105 if g_call_from_setup = 'Y' then
4106 oe_debug_pub.add('Begin Populate_Pattern_Phases for HP case');
4107 else
4108 write_log( 'Begin Populate_Pattern_Phases for HP case');
4109 end if;
4110 */
4111 FOR j IN l_phase_id_to_insert_csr LOOP
4112 l_prl_flag := 'Y';
4113 begin
4114 select /*+ index(qp_pp QP_PATTERN_PHASES_N1) */ 'Y'
4115 into l_exists
4116 from qp_pattern_phases qp_pp
4117 where pattern_id = p_pattern_id
4118 and pricing_phase_id = j.pricing_phase_id;
4119 exception
4120 when no_data_found then
4121 INSERT INTO qp_pattern_phases
4122 (pattern_id,
4123 pricing_phase_id,
4124 creation_date,
4125 created_by,
4126 last_update_date,
4127 last_updated_by,
4128 last_update_login,
4129 program_application_id,
4130 program_id,
4131 program_update_date,
4132 request_id
4133 )
4134 VALUES
4135 (p_pattern_id,
4136 j.pricing_phase_id,
4137 sysdate,
4138 FND_GLOBAL.USER_ID,
4139 sysdate,
4140 FND_GLOBAL.USER_ID,
4141 FND_GLOBAL.LOGIN_ID,
4142 FND_GLOBAL.PROG_APPL_ID,
4143 FND_GLOBAL.CONC_PROGRAM_ID,
4144 sysdate,
4145 FND_GLOBAL.CONC_REQUEST_ID
4146 );
4147
4148 if g_call_from_setup <> 'Y' then
4149 commit;
4150 end if;
4151
4152 when others then
4153 raise;
4154 end;
4155 END LOOP; --j IN l_phase_id_to_insert_csr
4156
4157 if l_prl_flag='N' then
4158
4159 select list_type_code into l_list_type
4160 from qp_list_headers_all_b
4161 where list_header_id = p_list_header_id;
4162
4163 if l_list_type = 'PRL' then
4164 begin
4165 select /*+ index(qp_pp QP_PATTERN_PHASES_N1) */ 'Y'
4166 into l_exists
4167 from qp_pattern_phases qp_pp
4168 where pattern_id = p_pattern_id
4169 and pricing_phase_id = 1;
4170 exception
4171 when no_data_found then
4172 INSERT INTO qp_pattern_phases
4173 (pattern_id,
4174 pricing_phase_id,
4175 creation_date,
4176 created_by,
4177 last_update_date,
4178 last_updated_by,
4179 last_update_login,
4180 program_application_id,
4181 program_id,
4182 program_update_date,
4183 request_id
4184 )
4185 VALUES
4186 (p_pattern_id,
4187 1,
4188 sysdate,
4189 FND_GLOBAL.USER_ID,
4190 sysdate,
4191 FND_GLOBAL.USER_ID,
4192 FND_GLOBAL.LOGIN_ID,
4193 FND_GLOBAL.PROG_APPL_ID,
4194 FND_GLOBAL.CONC_PROGRAM_ID,
4195 sysdate,
4196 FND_GLOBAL.CONC_REQUEST_ID
4197 );
4198
4199 if g_call_from_setup <> 'Y' then
4200 commit;
4201 end if;
4202 when others then
4203 raise;
4204 end;
4205 end if;
4206 end if;
4207 else
4208 -- LP, PP case
4209 /*
4210 if g_call_from_setup = 'Y' then
4211 oe_debug_pub.add('Begin Populate_Pattern_Phases for LP, PP case');
4215 */
4212 else
4213 write_log( 'Begin Populate_Pattern_Phases for LP, PP case');
4214 end if;
4216 begin
4217 select /*+ index(qp_pp QP_PATTERN_PHASES_N1) */ 'Y'
4218 into l_exists
4219 from qp_pattern_phases qp_pp
4220 where pattern_id = p_pattern_id
4221 and pricing_phase_id = p_pricing_phase_id
4222 and rownum = 1; -- needed in case same combination is inserted by 2 diff. threads and one has commited before other
4223 exception
4224 when no_data_found then
4225 /*
4226 if g_call_from_setup = 'Y' then
4227 oe_debug_pub.add('No pattern_phases found; go insert');
4228 else
4229 write_log( 'No pattern_phases found; go insert');
4230 end if;
4231 */
4232 INSERT INTO qp_pattern_phases
4233 (pattern_id,
4234 pricing_phase_id,
4235 creation_date,
4236 created_by,
4237 last_update_date,
4238 last_updated_by,
4239 last_update_login,
4240 program_application_id,
4241 program_id,
4242 program_update_date,
4243 request_id
4244 )
4245 VALUES
4246 (p_pattern_id,
4247 p_pricing_phase_id,
4248 sysdate,
4249 FND_GLOBAL.USER_ID,
4250 sysdate,
4251 FND_GLOBAL.USER_ID,
4252 FND_GLOBAL.LOGIN_ID,
4253 FND_GLOBAL.PROG_APPL_ID,
4254 FND_GLOBAL.CONC_PROGRAM_ID,
4255 sysdate,
4256 FND_GLOBAL.CONC_REQUEST_ID
4257 );
4258
4259 if g_call_from_setup <> 'Y' then
4260 commit;
4261 end if;
4262
4263 when others then
4264 if g_call_from_setup = 'Y' then
4265 oe_debug_pub.add('Insert failure:'||sqlerrm);
4266 else
4267 write_log( 'Insert failure:'||sqlerrm);
4268 end if;
4269 raise;
4270 end;
4271
4272 END IF; -- p_list_header_id is not null
4273 /*
4274 if g_call_from_setup = 'Y' then
4275 oe_debug_pub.add('End Populate_Pattern_Phases ');
4276 else
4277 write_log( 'End Populate_Pattern_Phases ');
4278 end if;
4279 */
4280
4281 EXCEPTION
4282 WHEN OTHERS THEN
4283 if g_call_from_setup = 'Y' then
4284 oe_debug_pub.add('PS_ATTR_GRP_PVT.Populate_Pattern_Phases ' || SQLERRM);
4285 else
4286 write_log( 'PS_ATTR_GRP_PVT.Populate_Pattern_Phases ' || SQLERRM );
4287 end if;
4288 raise;
4289 END Populate_Pattern_Phases;
4290
4291 PROCEDURE Header_Pattern_Main(
4292 p_list_header_id IN NUMBER
4293 ,p_qualifier_group IN NUMBER
4294 ,p_setup_action IN VARCHAR2 ) IS
4295
4296 -- p_setup_action I (for insert), U (for update), D (for delete) or
4297 -- UD (for update in denormalized columns from header like active_flag, currency etc.)
4298 l_status_code VARCHAR2(30) := NULL;
4299 l_status_text VARCHAR2(2000) := NULL;
4300 l_pattern_id NUMBER;
4301 l_qual_exists VARCHAR2(1) := 'N';
4302 l_ACTIVE_FLAG qp_list_headers_all_b.active_flag%type;
4303 l_LIST_TYPE_CODE qp_list_headers_all_b.list_type_code%type;
4304 l_CURRENCY_CODE qp_list_headers_all_b.CURRENCY_CODE%type;
4305 l_ASK_FOR_FLAG qp_list_headers_all_b.ASK_FOR_FLAG%type;
4306 l_HEADER_LIMIT_EXISTS qp_list_headers_all_b.LIMIT_EXISTS_FLAG%type;
4307 l_SOURCE_SYSTEM_CODE qp_list_headers_all_b.SOURCE_SYSTEM_CODE%type;
4308 l_END_DATE_ACTIVE qp_list_headers_all_b.END_DATE_ACTIVE%type;
4309 l_START_DATE_ACTIVE qp_list_headers_all_b.START_DATE_ACTIVE%type;
4310 l_START_DATE_ACTIVE_FIRST qp_list_headers_all_b.START_DATE_ACTIVE_FIRST%type;
4311 l_END_DATE_ACTIVE_FIRST qp_list_headers_all_b.END_DATE_ACTIVE_FIRST%type;
4312 l_ACTIVE_DATE_FIRST_TYPE qp_list_headers_all_b.ACTIVE_DATE_FIRST_TYPE%type;
4313 l_START_DATE_ACTIVE_SECOND qp_list_headers_all_b.START_DATE_ACTIVE_SECOND%type;
4314 l_END_DATE_ACTIVE_SECOND qp_list_headers_all_b.END_DATE_ACTIVE_SECOND%type;
4315 l_ACTIVE_DATE_SECOND_TYPE qp_list_headers_all_b.ACTIVE_DATE_SECOND_TYPE%type;
4316 l_GLOBAL_FLAG qp_list_headers_all_b.GLOBAL_FLAG%type;
4317
4318
4319 BEGIN
4320 g_qp_pattern_search := FND_PROFILE.VALUE('QP_PATTERN_SEARCH');
4321 g_call_from_setup := 'Y';
4322 G_QP_DEBUG := 'N';
4323
4324 if g_call_from_setup = 'Y' then
4325 oe_debug_pub.add('Header_Pattern_Main - p_list_header_id = ' ||p_list_header_id);
4326 oe_debug_pub.add('Header_Pattern_Main - p_qualifier_group = ' ||p_qualifier_group);
4327 oe_debug_pub.add('Header_Pattern_Main - p_setup_action = ' ||p_setup_action);
4328 else
4329 write_log( 'Header_Pattern_Main - p_list_header_id = ' ||p_list_header_id);
4330 write_log( 'Header_Pattern_Main - p_qualifier_group = ' ||p_qualifier_group);
4331 write_log( 'Header_Pattern_Main - p_setup_action = ' ||p_setup_action);
4332 end if;
4333
4334 IF p_list_header_id IS NOT NULL THEN
4335 SELECT list_type_code INTO g_list_type FROM qp_list_headers WHERE list_header_id = p_list_header_id;
4336 ELSIF g_qp_pattern_search = 'P' THEN
4337 g_list_type := 'PRL';
4338 ELSE
4339 g_list_type := 'JP';
4340 END IF;
4341
4342 -- when called while set up of modifier/price list
4343 if p_setup_action <> 'UD' then
4344 -- No need to delete pattern in case of update in denormalized columns in header
4345 -- delete from qp_attribute_groups first
4346 if p_qualifier_group is null then
4347 delete from qp_attribute_groups
4348 where list_header_id = p_list_header_id
4349 and list_line_id = -1;
4350 elsif p_qualifier_group is not null then
4351 delete from qp_attribute_groups
4352 where list_header_id = p_list_header_id
4353 and list_line_id = -1
4354 and GROUPING_NO in (-1, p_qualifier_group);
4355 end if;
4356
4357 -- update the segment_id columns for qualifiers
4358 Update_Qual_Segment_id(p_list_header_id, p_qualifier_group, -1, -1);
4359 end if;
4360
4361 -- populate the records in qp_attribute_groups afresh for p_list_header_id, list_line_id -1
4362 -- and p_qualifier_group
4363 if p_setup_action = 'I' then
4364 -- insert case
4365 generate_hp_atgrps(p_list_header_id, p_qualifier_group);
4366 elsif p_setup_action = 'U' or p_setup_action = 'D' then
4367 -- update or delete case
4368 if p_qualifier_group is null then
4369 generate_hp_atgrps(p_list_header_id, p_qualifier_group);
4370 elsif p_qualifier_group is not null then
4371 begin
4372 select 'Y'
4373 into l_qual_exists
4374 from qp_qualifiers
4375 where list_header_id = p_list_header_id
4376 and list_line_id = -1
4377 and ((list_type_code = 'PRL'
4378 AND QUALIFIER_CONTEXT <> 'MODLIST'
4379 AND QUALIFIER_ATTRIBUTE <> 'QUALIFIER_ATTRIBUTE4')
4380 OR
4381 (list_type_code <> 'PRL')
4382 )
4383 and QUALIFIER_GROUPING_NO = p_qualifier_group
4384 and rownum = 1;
4385 exception
4386 when no_data_found then
4387 l_qual_exists := 'N';
4388 end;
4389
4390 if l_qual_exists = 'Y' then
4391 -- means some qualifiers still exist for p_qualifier_group
4392 generate_hp_atgrps(p_list_header_id, p_qualifier_group);
4393 else
4394 begin
4395 select 'Y'
4396 into l_qual_exists
4397 from qp_qualifiers
4398 where list_header_id = p_list_header_id
4399 and list_line_id = -1
4400 and ((list_type_code = 'PRL'
4401 AND QUALIFIER_CONTEXT <> 'MODLIST'
4402 AND QUALIFIER_ATTRIBUTE <> 'QUALIFIER_ATTRIBUTE4')
4403 OR
4404 (list_type_code <> 'PRL')
4405 )
4406 and QUALIFIER_GROUPING_NO <> -1
4407 and rownum = 1;
4408 exception
4409 when no_data_found then
4410 l_qual_exists := 'N';
4411 end;
4412
4413 if l_qual_exists = 'N' then
4414 -- no qualifiers exist other than -1 qualifier_grouping_no
4415 -- this may insert in qp_attribute_groups with HDR_QUAL_GROUPING_NO = -1, if any
4416 -- qualifiers exist with qualifier_grouping_no = -1
4417 generate_hp_atgrps(p_list_header_id, p_qualifier_group);
4418 end if; -- l_qual_exists = 'N'
4419 end if; -- l_qual_exists = 'Y'
4420 end if; --p_qualifier_group is null
4421 elsif p_setup_action = 'UD' THEN
4422
4423 begin
4424 select ACTIVE_FLAG,
4425 LIST_TYPE_CODE,
4426 CURRENCY_CODE,
4427 ASK_FOR_FLAG,
4428 LIMIT_EXISTS_FLAG,
4429 SOURCE_SYSTEM_CODE,
4430 END_DATE_ACTIVE,
4431 START_DATE_ACTIVE,
4432 START_DATE_ACTIVE_FIRST,
4433 END_DATE_ACTIVE_FIRST,
4434 ACTIVE_DATE_FIRST_TYPE,
4435 START_DATE_ACTIVE_SECOND,
4436 END_DATE_ACTIVE_SECOND,
4437 ACTIVE_DATE_SECOND_TYPE,
4438 GLOBAL_FLAG
4439 into l_ACTIVE_FLAG,
4440 l_LIST_TYPE_CODE,
4441 l_CURRENCY_CODE,
4442 l_ASK_FOR_FLAG,
4443 l_HEADER_LIMIT_EXISTS,
4444 l_SOURCE_SYSTEM_CODE,
4445 l_END_DATE_ACTIVE,
4446 l_START_DATE_ACTIVE,
4447 l_START_DATE_ACTIVE_FIRST,
4448 l_END_DATE_ACTIVE_FIRST,
4449 l_ACTIVE_DATE_FIRST_TYPE,
4450 l_START_DATE_ACTIVE_SECOND,
4451 l_END_DATE_ACTIVE_SECOND,
4452 l_ACTIVE_DATE_SECOND_TYPE,
4453 l_GLOBAL_FLAG
4454 from qp_list_headers_all_b
4455 where list_header_id = p_list_header_id;
4456
4457 -- update header pattern records
4458 update qp_attribute_groups
4459 set ACTIVE_FLAG = l_ACTIVE_FLAG,
4460 LIST_TYPE_CODE = l_LIST_TYPE_CODE,
4461 CURRENCY_CODE = l_CURRENCY_CODE,
4462 ASK_FOR_FLAG = l_ASK_FOR_FLAG,
4463 HEADER_LIMIT_EXISTS = l_HEADER_LIMIT_EXISTS,
4464 SOURCE_SYSTEM_CODE = l_SOURCE_SYSTEM_CODE,
4465 END_DATE_ACTIVE_H = l_END_DATE_ACTIVE,
4466 START_DATE_ACTIVE_H = l_START_DATE_ACTIVE,
4467 START_DATE_ACTIVE_FIRST = l_START_DATE_ACTIVE_FIRST,
4468 END_DATE_ACTIVE_FIRST = l_END_DATE_ACTIVE_FIRST,
4469 ACTIVE_DATE_FIRST_TYPE = l_ACTIVE_DATE_FIRST_TYPE,
4470 START_DATE_ACTIVE_SECOND = l_START_DATE_ACTIVE_SECOND,
4471 END_DATE_ACTIVE_SECOND = l_END_DATE_ACTIVE_SECOND,
4472 ACTIVE_DATE_SECOND_TYPE = l_ACTIVE_DATE_SECOND_TYPE,
4473 GLOBAL_FLAG = l_GLOBAL_FLAG
4474
4475 where list_header_id = p_list_header_id;
4476 -- and list_line_id = -1;
4477
4478 -- update line pattern records
4479 /* update qp_attribute_groups
4480 set ACTIVE_FLAG = l_ACTIVE_FLAG,
4481 LIST_TYPE_CODE = l_LIST_TYPE_CODE,
4482 CURRENCY_CODE = l_CURRENCY_CODE,
4483 ASK_FOR_FLAG = l_ASK_FOR_FLAG,
4484 SOURCE_SYSTEM_CODE = l_SOURCE_SYSTEM_CODE
4485 where list_header_id = p_list_header_id
4486 and list_line_id <> -1;*/
4487
4488 exception
4489 when no_data_found then
4490 if g_call_from_setup = 'Y' then
4491 oe_debug_pub.add('Header_Pattern_Main - no_data_found in action UD ' );
4492 end if;
4493 null;
4494
4495 when others then
4496 if g_call_from_setup = 'Y' then
4497 oe_debug_pub.add('Header_Pattern_Main - others exceptions in action UD ' );
4498 end if;
4499 null;
4500 end;
4501 end if; -- p_setup_action = 'I'
4502
4503 begin
4504 select 'Y'
4505 into l_qual_exists
4506 from qp_qualifiers
4507 where list_header_id = p_list_header_id
4508 and list_line_id = -1
4509 and ((list_type_code = 'PRL'
4510 AND QUALIFIER_CONTEXT <> 'MODLIST'
4511 AND QUALIFIER_ATTRIBUTE <> 'QUALIFIER_ATTRIBUTE4')
4512 OR
4513 (list_type_code <> 'PRL')
4514 )
4515 and rownum = 1;
4516 exception
4517 when no_data_found then
4518 l_qual_exists := 'N';
4519 end;
4520
4521 update qp_attribute_groups
4522
4523 set HEADER_QUALS_EXIST_FLAG = l_QUAL_EXISTS
4524 where list_header_id = p_list_header_id;
4525
4526 /* update qp_pte_segments
4527 set used_in_search ='Y'
4528 where NVL(used_in_search,'N') ='N'
4529 and segment_id in
4530 ( select DISTINCT segment_id
4531 from qp_patterns );
4532
4533
4534 update qp_pte_segments
4535 set used_in_search ='N'
4536 where NVL(used_in_search,'Y') ='Y'
4537 and segment_id not in
4538 ( select DISTINCT segment_id
4539 from qp_patterns ); */
4540
4541 UPDATE /*+ index (QP_PTE_SEGMENTS QP_PTE_SEGMENTS_U2) */ qp_pte_segments
4542 SET used_in_search = 'Y'
4543 WHERE NVL(used_in_search,'N') = 'N'
4544 AND segment_id in
4545 (select pricing_segment_id
4546 from qp_pricing_attributes
4547 where /*list_line_id in (
4548 select list_line_id from qp_attribute_groups where eq_flag = 'N'
4549 UNION
4550 select list_line_id from qp_list_lines where eq_flag = 'N')
4551 and */list_header_id = nvl(p_list_header_id, list_header_id)
4552 --AND comparison_operator_code <> '='
4553 and pricing_segment_id is not null
4554 UNION all
4555 select segment_id
4556 from qp_qualifiers
4557 where /*list_line_id in (
4558 select list_line_id from qp_attribute_groups where eq_flag = 'N'
4559 UNION
4560 select list_line_id from qp_list_lines where eq_flag = 'N')
4561 and */list_header_id = nvl(p_list_header_id, list_header_id)
4562 --AND comparison_operator_code <> '='
4563 and segment_id is not null);
4564
4565
4566
4567 update_pattern_phases(p_list_header_id,null,null);
4568 update qp_patterns a set segment_count = (select count(segment_id) from qp_patterns b where a.pattern_id = b.pattern_id);
4569
4570
4571 EXCEPTION
4572 WHEN OTHERS THEN
4573 if g_call_from_setup = 'Y' then
4574 oe_debug_pub.add('PS_ATTR_GRP_PVT.Header_Pattern_Main ' || SQLERRM);
4575 else
4576 write_log( 'PS_ATTR_GRP_PVT.Header_Pattern_Main ' || SQLERRM );
4577 end if;
4578
4579 END Header_Pattern_Main;
4580
4581 PROCEDURE Line_Pattern_Main(
4582 p_list_header_id IN NUMBER
4583 ,p_list_line_id IN NUMBER
4584 ,p_qualifier_group IN NUMBER
4585 ,p_setup_action IN VARCHAR2 ) IS
4586
4590 l_qual_exists varchar2(1) := 'N';
4587 l_status_code VARCHAR2(30) := NULL;
4588 l_status_text VARCHAR2(2000) := NULL;
4589 l_pid NUMBER := NULL;
4591 l_line_LIMIT_EXISTS qp_list_lines.LIMIT_EXISTS_FLAG%type;
4592
4593 BEGIN
4594 g_qp_pattern_search := FND_PROFILE.VALUE('QP_PATTERN_SEARCH');
4595 g_call_from_setup := 'Y';
4596 G_QP_DEBUG := 'N';
4597
4598 if g_call_from_setup = 'Y' then
4599 oe_debug_pub.add('Line_Pattern_Main - p_list_header_id = ' ||p_list_header_id);
4600 oe_debug_pub.add('Line_Pattern_Main - p_list_line_id = ' ||p_list_line_id);
4601 oe_debug_pub.add('Line_Pattern_Main - p_qualifier_group = ' ||p_qualifier_group);
4602 oe_debug_pub.add('Line_Pattern_Main - p_setup_action = ' ||p_setup_action);
4603 else
4604 write_log( 'Line_Pattern_Main - p_list_header_id = ' ||p_list_header_id);
4605 write_log( 'Line_Pattern_Main - p_list_line_id = ' ||p_list_line_id);
4606 write_log( 'Line_Pattern_Main - p_qualifier_group = ' ||p_qualifier_group);
4607 write_log( 'Line_Pattern_Main - p_setup_action = ' ||p_setup_action);
4608 end if;
4609
4610 IF p_list_header_id IS NOT NULL THEN
4611 SELECT list_type_code INTO g_list_type FROM qp_list_headers WHERE list_header_id = p_list_header_id;
4612 ELSIF g_qp_pattern_search = 'P' THEN
4613 g_list_type := 'PRL';
4614 ELSE
4615 g_list_type := 'JP';
4616 END IF;
4617
4618 -- when called while set up of modifier
4619 if p_setup_action <> 'UD' then
4620 -- delete from qp_attribute_groups first
4621 if p_qualifier_group is null then
4622 delete from qp_attribute_groups
4623 where list_header_id = p_list_header_id
4624 and list_line_id = p_list_line_id;
4625 elsif p_qualifier_group is not null then
4626 delete from qp_attribute_groups
4627 where list_header_id = p_list_header_id
4628 and list_line_id = p_list_line_id
4629 and GROUPING_NO in (-1, p_qualifier_group);
4630 end if;
4631
4632 -- update the segment_id columns for qualifiers
4633 Update_Qual_Segment_id(p_list_header_id, p_qualifier_group, p_list_line_id, p_list_line_id);
4634
4635 -- update the product_segment_id and pricing_segment_id columns in qp_pricing_attributes
4636 Update_Prod_Pric_Segment_id(p_list_header_id, p_list_line_id, p_list_line_id );
4637 end if;
4638
4639 -- populate the records in qp_attribute_groups afresh for p_list_header_id, p_list_line_id
4640 -- and p_qualifier_group
4641 if p_setup_action = 'I' then
4642 -- insert case
4643 generate_lp_atgrps(p_list_header_id, p_qualifier_group,
4644 p_list_line_id, p_list_line_id);
4645 elsif p_setup_action = 'U' or p_setup_action = 'D' then
4646 -- update or delete case
4647 if p_qualifier_group is null then
4648 update qp_list_lines
4649 set pattern_id = DECODE(qualification_ind,0,-2,-3),
4650 hash_key = null,
4651 cache_key = null
4652 where list_line_id = p_list_line_id
4653 and qualification_ind in (0, 2);
4654 generate_lp_atgrps(p_list_header_id, p_qualifier_group,
4655 p_list_line_id, p_list_line_id);
4656 elsif p_qualifier_group is not null then
4657 begin
4658 select 'Y'
4659 into l_qual_exists
4660 from qp_qualifiers
4661 where list_header_id = p_list_header_id
4662 and list_line_id = p_list_line_id
4663 and QUALIFIER_GROUPING_NO = p_qualifier_group
4664 and rownum = 1;
4665 exception
4666 when no_data_found then
4667 l_qual_exists := 'N';
4668 end;
4669
4670 if l_qual_exists = 'Y' then
4671 -- means some qualifiers still exist for p_qualifier_group
4672 update qp_list_lines
4673 set pattern_id = DECODE(qualification_ind,0,-2,-3),
4674 hash_key = null,
4675 cache_key = null
4676 where list_line_id = p_list_line_id
4677 and qualification_ind in (0, 2);
4678 generate_lp_atgrps(p_list_header_id, p_qualifier_group,
4679 p_list_line_id, p_list_line_id);
4680 else
4681 begin
4682 select 'Y'
4683 into l_qual_exists
4684 from qp_qualifiers
4685 where list_header_id = p_list_header_id
4686 and list_line_id = p_list_line_id
4687 and QUALIFIER_GROUPING_NO <> -1
4688 and rownum = 1;
4689 exception
4690 when no_data_found then
4691 l_qual_exists := 'N';
4692 end;
4693
4694 if l_qual_exists = 'N' then
4695 -- no qualifiers exist other than -1 qualifier_grouping_no
4696 -- this may insert in qp_attribute_groups with LINE_QUAL_GROUPING_NO = -1, if any
4697 -- qualifiers exist with qualifier_grouping_no = -1
4698 update qp_list_lines
4699 set pattern_id = DECODE(qualification_ind,0,-2,-3),
4700 hash_key = null,
4701 cache_key = null
4702 where list_line_id = p_list_line_id
4703 and qualification_ind in (0, 2);
4704 generate_lp_atgrps(p_list_header_id,p_qualifier_group,
4705 p_list_line_id, p_list_line_id);
4706 end if; -- l_qual_exists = 'N'
4707 end if; -- l_qual_exists = 'Y'
4708 end if; --p_qualifier_group is null
4709 elsif p_setup_action = 'UD' then
4710 begin
4711 select LIMIT_EXISTS_FLAG
4712 into l_line_LIMIT_EXISTS
4713 from qp_list_lines
4714 where list_line_id = p_list_line_id;
4715
4719 where list_header_id = p_list_header_id
4716 -- update line pattern records
4717 update qp_attribute_groups
4718 set LINE_LIMIT_EXISTS = l_line_LIMIT_EXISTS
4720 and list_line_id = p_list_line_id;
4721
4722 exception
4723 when no_data_found then
4724 if g_call_from_setup = 'Y' then
4725 oe_debug_pub.add('Line_Pattern_Main - no_data_found in action UD ' );
4726 end if;
4727 null;
4728
4729 when others then
4730 if g_call_from_setup = 'Y' then
4731 oe_debug_pub.add('Line_Pattern_Main - others exceptions in action UD ' );
4732 end if;
4733 null;
4734 end;
4735 end if; -- p_setup_action = 'I'
4736
4737 -- at last, delete/restore PP depending on whether LP exists or not for passed header_id, line_id
4738 if p_setup_action = 'D' then
4739 -- line qualifier delete case
4740 begin
4741 select 'Y'
4742 into l_qual_exists
4743 from qp_attribute_groups
4744 where list_header_id = p_list_header_id
4745 and list_line_id = p_list_line_id
4746 and rownum = 1;
4747 exception
4748 when no_data_found then
4749 l_qual_exists := 'N';
4750 end;
4751 else
4752 -- line qualifier insert/update case, assume there will be record in qp_attribute_groups
4753 l_qual_exists := 'Y';
4754 end if;
4755
4756 /* if p_setup_action = 'I' then
4757 -- assume LP exists, and so nullify the PP values in qp_list_lines table
4758 --except cache_key
4759 update /*+ index(lines QP_LIST_LINES_PK) */ /*qp_list_lines
4760 set pattern_id = null,
4761 pricing_attribute_count = null,
4762 product_uom_code = null,
4763 hash_key = null
4764 where list_line_id = p_list_line_id
4765 and pattern_id is not null;
4766
4767 end if;*/
4768
4769 if l_qual_exists = 'N' and p_setup_action = 'D' then
4770 -- means restore PP
4771 remove_prod_pattern_for_line(p_list_line_id);
4772 update_pp_lines(p_list_header_id, p_list_line_id, p_list_line_id);
4773 end if;
4774
4775 --------- Added for PL/SQL Pattern Search
4776 /* update qp_pte_segments
4777 set used_in_search ='Y'
4778 where NVL(used_in_search,'N') ='N'
4779 and segment_id in
4780 (select DISTINCT segment_id
4781 from qp_patterns );
4782
4783
4784 update qp_pte_segments
4785 set used_in_search ='N'
4786 where NVL(used_in_search,'Y') ='Y'
4787 and segment_id not in
4788 (select DISTINCT segment_id
4789 from qp_patterns );*/
4790
4791 UPDATE /*+ index (QP_PTE_SEGMENTS QP_PTE_SEGMENTS_U2) */ qp_pte_segments
4792 SET used_in_search = 'Y'
4793 WHERE NVL(used_in_search,'N') = 'N'
4794 AND segment_id in
4795 (select pricing_segment_id
4796 from qp_pricing_attributes
4797 where /*list_line_id in (
4798 select list_line_id from qp_attribute_groups where eq_flag = 'N'
4799 UNION
4800 select list_line_id from qp_list_lines where eq_flag = 'N')
4801 and */list_header_id = nvl(p_list_header_id, list_header_id)
4802 --AND comparison_operator_code <> '='
4803 and pricing_segment_id is not null
4804 UNION all
4805 select segment_id
4806 from qp_qualifiers
4807 where /*list_line_id in (
4808 select list_line_id from qp_attribute_groups where eq_flag = 'N'
4809 UNION
4810 select list_line_id from qp_list_lines where eq_flag = 'N')
4811 and */list_header_id = nvl(p_list_header_id, list_header_id)
4812 --AND comparison_operator_code <> '='
4813 and segment_id is not null);
4814
4815 update_pattern_phases(p_list_header_id,p_list_line_id,p_list_line_id);
4816 update qp_patterns a set segment_count = (select count(segment_id) from qp_patterns b where a.pattern_id = b.pattern_id);
4817
4818
4819 --------- Added for PL/SQL Pattern Search
4820
4821 EXCEPTION
4822 WHEN OTHERS THEN
4823 if g_call_from_setup = 'Y' then
4824 oe_debug_pub.add('PS_ATTR_GRP_PVT.Line_Pattern_Main ' || SQLERRM);
4825 else
4826 write_log( 'PS_ATTR_GRP_PVT.Line_Pattern_Main ' || SQLERRM );
4827 end if;
4828
4829 END Line_Pattern_Main;
4830
4831 PROCEDURE Product_Pattern_Main(
4832 p_list_header_id IN NUMBER ,
4833 p_list_line_id IN NUMBER ,
4834 p_setup_action IN VARCHAR2 ) IS
4835
4836 l_status_code VARCHAR2(30) := NULL;
4837 l_status_text VARCHAR2(2000) := NULL;
4838 l_qual_exists varchar2(1) := 'N';
4839 l_product_uom_code qp_list_lines.product_uom_code%type;
4840 l_qual_ind number;
4841
4842 BEGIN
4843 g_qp_pattern_search := FND_PROFILE.VALUE('QP_PATTERN_SEARCH');
4844 g_call_from_setup := 'Y';
4845 G_QP_DEBUG := 'N';
4846 if g_call_from_setup = 'Y' then
4847 oe_debug_pub.add('Product_Pattern_Main - p_list_header_id = ' ||p_list_header_id);
4848 oe_debug_pub.add('Product_Pattern_Main - p_list_line_id = ' ||p_list_line_id);
4849 oe_debug_pub.add('Product_Pattern_Main - p_setup_action = ' ||p_setup_action);
4850 else
4851 write_log( 'Product_Pattern_Main - p_list_header_id = ' ||p_list_header_id);
4852 write_log( 'Product_Pattern_Main - p_list_line_id = ' ||p_list_line_id);
4853 write_log( 'Product_Pattern_Main - p_setup_action = ' ||p_setup_action);
4854 end if;
4855
4856 select qualification_ind
4857 into l_qual_ind
4858 from qp_list_lines
4859 where list_line_id = p_list_line_id;
4860 IF p_list_header_id IS NOT NULL THEN
4861 SELECT list_type_code INTO g_list_type FROM qp_list_headers WHERE list_header_id = p_list_header_id;
4862 ELSIF g_qp_pattern_search = 'P' THEN
4863 g_list_type := 'PRL';
4864 ELSE
4865 g_list_type := 'JP';
4866 END IF;
4867
4868 oe_debug_pub.add('Product_Pattern_Main - l_qual_ind = ' ||l_qual_ind);
4869
4870 -- do nothing, return back if called for child line, bug 3581058
4871 if l_qual_ind in (4, 6, 8, 10, 12, 14, 20, 22, 28, 30,0,2) then
4872 null;
4873 else
4874 return;
4875 end if;
4876
4877 if p_setup_action = 'UD' then
4878 -- update qp_list_lines.product_uom_code
4879 begin
4880 select product_uom_code
4881 into l_product_uom_code
4882 from qp_pricing_attributes
4883 where list_header_id = p_list_header_id
4884 and list_line_id = p_list_line_id
4885 and product_uom_code is not null
4886 and rownum = 1;
4887 exception
4888 when no_data_found then
4889 l_product_uom_code := null;
4890 end;
4891
4892 update /*+ index(lines QP_LIST_LINES_PK) */ qp_list_lines
4893 set product_uom_code = l_product_uom_code
4894 where list_line_id = p_list_line_id;
4895 else
4896 -- when called while set up of price list/modifier
4897 begin
4898 select 'Y'
4899 into l_qual_exists
4900 from qp_qualifiers
4901 where list_header_id = p_list_header_id
4902 and list_line_id = p_list_line_id
4903 and rownum = 1;
4904 exception
4905 when no_data_found then
4906 l_qual_exists := 'N';
4907 end;
4908
4909 if l_qual_exists = 'Y' then
4910 if g_call_from_setup = 'Y' then
4911 oe_debug_pub.add('going to populate LP');
4912 else
4913 write_log( 'going to populate LP');
4914 end if;
4915 Line_Pattern_Main(p_list_header_id, p_list_line_id, null, 'I');
4916 else
4917 if g_call_from_setup = 'Y' then
4918 oe_debug_pub.add('going to populate PP');
4919 else
4920 write_log( 'going to populate PP');
4921 end if;
4922
4923 remove_prod_pattern_for_line(p_list_line_id);
4924
4925 -- update the product_segment_id and pricing_segment_id columns in qp_pricing_attributes
4926 Update_Prod_Pric_Segment_id(p_list_header_id, p_list_line_id,
4927 p_list_line_id);
4928
4929 update_pp_lines(p_list_header_id, p_list_line_id, p_list_line_id);
4930 -- to generate blind row for the price list which does not have any qualifier
4931 -- IF (g_list_type IN ('PRL','AGR') )THEN
4932 delete from qp_attribute_groups
4933 where list_header_id = p_list_header_id
4934 AND list_line_id = -1;
4935 generate_hp_atgrps(p_list_header_id, null);
4936 -- END IF;
4937 end if;
4938 end if; -- p_setup_action = 'UD'
4939
4940 --------- Added for PL/SQL Pattern Search
4941
4942 /* update qp_pte_segments
4943 set used_in_search ='Y'
4944 where NVL(used_in_search,'N') ='N'
4945 and segment_id in
4946 (select DISTINCT segment_id
4947 from qp_patterns );
4948
4949
4950 update qp_pte_segments
4951 set used_in_search ='N'
4952 where NVL(used_in_search,'Y') ='Y'
4953 and segment_id not in
4954 ( select DISTINCT segment_id
4955 from qp_patterns );*/
4956
4957
4958 UPDATE /*+ index (QP_PTE_SEGMENTS QP_PTE_SEGMENTS_U2) */ qp_pte_segments
4959 SET used_in_search = 'Y'
4960 WHERE NVL(used_in_search,'N') = 'N'
4961 AND segment_id in
4962 (select pricing_segment_id
4963 from qp_pricing_attributes
4964 where /*list_line_id in (
4965 select list_line_id from qp_attribute_groups where eq_flag = 'N'
4966 UNION
4967 select list_line_id from qp_list_lines where eq_flag = 'N')
4968 and */list_header_id = nvl(p_list_header_id, list_header_id)
4969 --AND comparison_operator_code <> '='
4970 and pricing_segment_id is not null
4971 UNION all
4972 select segment_id
4973 from qp_qualifiers
4974 where /*list_line_id in (
4975 select list_line_id from qp_attribute_groups where eq_flag = 'N'
4976 UNION
4977 select list_line_id from qp_list_lines where eq_flag = 'N')
4978 and */list_header_id = nvl(p_list_header_id, list_header_id)
4979 --AND comparison_operator_code <> '='
4980 and segment_id is not null);
4981
4982
4983 update_pattern_phases(p_list_header_id,p_list_line_id,p_list_line_id);
4984 update qp_patterns a set segment_count = (select count(segment_id) from qp_patterns b where a.pattern_id = b.pattern_id);
4985
4986 -------- Added for PL/SQL Pattern Search
4987
4988 EXCEPTION
4989 WHEN OTHERS THEN
4990 if g_call_from_setup = 'Y' then
4991 oe_debug_pub.add('PS_ATTR_GRP_PVT.Product_Pattern_Main ' || SQLERRM);
4992 else
4993 write_log( 'PS_ATTR_GRP_PVT.Product_Pattern_Main ' || SQLERRM );
4994 end if;
4995
4996 END Product_Pattern_Main;
4997
4998 procedure Remove_Prod_Pattern_for_Line(p_list_line_id IN NUMBER)
4999 is
5000 begin
5001 /* update /*+ index(lines QP_LIST_LINES_PK) */ /*qp_list_lines
5002 set pattern_id = null,
5003 pricing_attribute_count = null,
5004 product_uom_code = null,
5005 hash_key = null,
5006 cache_key = null
5007 where list_line_id = p_list_line_id
5008 and pattern_id is not null;*/
5009
5010 ------ Added for PL/SQL Pattern Search
5011
5012 delete from qp_attribute_groups
5013
5014 where list_line_id = p_list_line_id;
5015
5016 if g_call_from_setup = 'Y' then
5017 oe_debug_pub.add('Deleted records from qp_attribute_groups for PP for list_line_id:'||p_list_line_id);
5018 else
5019 write_log( 'Deleted records from qp_attribute_groups for PP for list_line_id:'||p_list_line_id);
5020 end if;
5021
5022
5023 ------ Added for PL/SQL Pattern Search
5024
5025
5026 exception
5027 when no_data_found then
5028 null;
5029
5030 when others then
5031 if g_call_from_setup = 'Y' then
5032 oe_debug_pub.add('PS_ATTR_GRP_PVT.Remove_Prod_Pattern_For_Line ' || SQLERRM);
5033 else
5034 write_log( 'PS_ATTR_GRP_PVT.Remove_Prod_Pattern_For_Line ' || SQLERRM );
5035 end if;
5036 raise;
5037 end remove_prod_pattern_for_line;
5038
5039 procedure Update_Qual_Segment_id(p_list_header_id IN NUMBER
5040 ,p_qualifier_group IN NUMBER
5041 ,p_low_list_line_id IN NUMBER
5042 ,p_high_list_line_id IN NUMBER)
5043 is
5044 cursor c_qual_seg_id is
5045 select distinct QUALIFIER_CONTEXT, QUALIFIER_ATTRIBUTE
5046 from qp_qualifiers
5047 where QUALIFIER_CONTEXT is not null
5048 and QUALIFIER_ATTRIBUTE is not null
5049 and list_header_id = p_list_header_id
5050 and list_line_id between p_low_list_line_id and p_high_list_line_id
5051 and ((p_qualifier_group is not null and qualifier_grouping_no in (-1, p_qualifier_group))
5052 OR
5053 (p_qualifier_group is null)
5054 );
5055
5056 cursor c_qual_seg_id_hdr_null is
5057 select distinct QUALIFIER_CONTEXT, QUALIFIER_ATTRIBUTE
5058 from qp_qualifiers
5059 where QUALIFIER_CONTEXT is not null
5060 and QUALIFIER_ATTRIBUTE is not null
5061 and list_line_id between p_low_list_line_id and p_high_list_line_id
5062 and ((p_qualifier_group is not null and qualifier_grouping_no in (-1, p_qualifier_group))
5063 OR
5064 (p_qualifier_group is null)
5065 );
5066 TYPE segment_id_tab IS TABLE OF qp_qualifiers.segment_id%TYPE INDEX BY BINARY_INTEGER;
5067 TYPE context_tab IS TABLE OF qp_qualifiers.QUALIFIER_CONTEXT%TYPE INDEX BY BINARY_INTEGER;
5068 TYPE attribute_tab IS TABLE OF qp_qualifiers.QUALIFIER_ATTRIBUTE%TYPE INDEX BY BINARY_INTEGER;
5069
5070 segment_id_t segment_id_tab;
5071 context_t context_tab;
5072 attribute_t attribute_tab;
5073
5074 begin
5075 -- update the segment_id columns for qualifiers
5076 if g_call_from_setup = 'Y' then
5077 oe_debug_pub.add('Inside Update_Qual_Segment_id');
5078 oe_debug_pub.add('Start time :'||to_char(sysdate,'MM/DD/YYYY:HH:MI:SSSS'));
5079 else
5080 write_log( 'Inside Update_Qual_Segment_id');
5081 write_log( 'Start time :'||to_char(sysdate,'MM/DD/YYYY:HH:MI:SSSS'));
5082 end if;
5083 segment_id_t.delete;
5084 context_t.delete;
5085 attribute_t.delete;
5086
5087 IF p_list_header_id IS NULL THEN
5088 OPEN c_qual_seg_id_hdr_null;
5089 FETCH c_qual_seg_id_hdr_null BULK COLLECT INTO
5090 context_t,
5091 attribute_t;
5092 CLOSE c_qual_seg_id_hdr_null;
5093 ELSE
5094 OPEN c_qual_seg_id;
5095 FETCH c_qual_seg_id BULK COLLECT INTO
5096 context_t,
5097 attribute_t;
5098 CLOSE c_qual_seg_id;
5099 END IF;
5100
5101 if context_t.count > 0 then
5102 if g_call_from_setup = 'Y' then
5103 oe_debug_pub.add('Context_t.count='||context_t.count);
5104 else
5105 write_log( 'Context_t.count='||context_t.count);
5106 end if;
5107 FOR i in 1..context_t.count
5108 LOOP
5109 select b.segment_id
5110 into segment_id_t(i)
5111 from qp_prc_contexts_b a, qp_segments_b b
5112 where b.prc_context_id = a.prc_context_id
5113 and a.PRC_CONTEXT_CODE = context_t(i)
5114 and b.SEGMENT_MAPPING_COLUMN = attribute_t(i);
5115 END LOOP;
5116
5117 FORALL j in 1..context_t.count
5118 update qp_qualifiers
5119 set segment_id = segment_id_t(j)
5120 where QUALIFIER_CONTEXT = context_t(j)
5121 and QUALIFIER_ATTRIBUTE = attribute_t(j)
5122 and list_header_id = nvl(p_list_header_id, list_header_id)
5123 and list_line_id between p_low_list_line_id and p_high_list_line_id;
5124
5125 if g_call_from_setup = 'Y' then
5126 oe_debug_pub.add('No of qualifiers updated='||SQL%ROWCOUNT);
5127 else
5128 write_log( 'No of qualifiers updated='||SQL%ROWCOUNT);
5129 end if;
5130 end if; -- context_t.count > 0
5131
5132 if g_call_from_setup = 'Y' then
5133 oe_debug_pub.add('End Update_Qual_Segment_id');
5134 oe_debug_pub.add('End time :'||to_char(sysdate,'MM/DD/YYYY:HH:MI:SSSS'));
5135 else
5136 write_log( 'End Update_Qual_Segment_id');
5137 write_log( 'End time :'||to_char(sysdate,'MM/DD/YYYY:HH:MI:SSSS'));
5138 end if;
5139 exception
5140 when no_data_found then
5141 if g_call_from_setup = 'Y' then
5142 oe_debug_pub.add('No data found in c_qual_seg_id');
5143 else
5144 write_log( 'No data found in c_qual_seg_id');
5145 end if;
5146
5147 when others then
5148 if g_call_from_setup = 'Y' then
5149 oe_debug_pub.add('PS_ATTR_GRP_PVT.Update_Qual_Segment_Id ' || SQLERRM);
5150 else
5151 write_log( 'PS_ATTR_GRP_PVT.Update_Qual_Segment_Id ' || SQLERRM );
5152 end if;
5153
5154 raise;
5155
5156 end Update_Qual_Segment_id;
5157
5158 procedure Update_Prod_Pric_Segment_id(p_list_header_id IN NUMBER
5159 -- ,p_list_line_id IN NUMBER
5160 ,p_low_list_line_id IN NUMBER
5161 ,p_high_list_line_id IN NUMBER)
5162 is
5163 cursor c_prod_seg_id is
5164 select distinct PRODUCT_ATTRIBUTE_CONTEXT, PRODUCT_ATTRIBUTE
5165 from qp_pricing_attributes
5166 where PRODUCT_ATTRIBUTE_CONTEXT is not null
5167 and PRODUCT_ATTRIBUTE is not null
5168 and list_header_id = p_list_header_id
5169 and list_line_id between p_low_list_line_id and p_high_list_line_id;
5170
5171 cursor c_prod_seg_id_hdr_null is
5172 select distinct PRODUCT_ATTRIBUTE_CONTEXT, PRODUCT_ATTRIBUTE
5173 from qp_pricing_attributes
5174 where PRODUCT_ATTRIBUTE_CONTEXT is not null
5175 and PRODUCT_ATTRIBUTE is not null
5176 and list_line_id between p_low_list_line_id and p_high_list_line_id;
5177 cursor c_pric_seg_id is
5178 select distinct PRICING_ATTRIBUTE_CONTEXT, PRICING_ATTRIBUTE
5179 from qp_pricing_attributes
5180 where PRICING_ATTRIBUTE_CONTEXT is not null
5181 and PRICING_ATTRIBUTE is not null
5182 and list_header_id = p_list_header_id
5183 and list_line_id between p_low_list_line_id and p_high_list_line_id;
5184 cursor c_pric_seg_id_hdr_null is
5185 select distinct PRICING_ATTRIBUTE_CONTEXT, PRICING_ATTRIBUTE
5186 from qp_pricing_attributes
5187 where PRICING_ATTRIBUTE_CONTEXT is not null
5188 and PRICING_ATTRIBUTE is not null
5189 and list_line_id between p_low_list_line_id and p_high_list_line_id;
5190
5191 TYPE segment_id_tab IS TABLE OF qp_pricing_attributes.product_segment_id%TYPE INDEX BY BINARY_INTEGER;
5192 TYPE context_tab IS TABLE OF qp_pricing_attributes.PRODUCT_ATTRIBUTE_CONTEXT%TYPE INDEX BY BINARY_INTEGER;
5193 TYPE attribute_tab IS TABLE OF qp_pricing_attributes.PRODUCT_ATTRIBUTE%TYPE INDEX BY BINARY_INTEGER;
5194
5195 segment_id_t segment_id_tab;
5196 context_t context_tab;
5197 attribute_t attribute_tab;
5198
5199
5200 begin
5201 if g_call_from_setup = 'Y' then
5202 oe_debug_pub.add('Inside Update_Prod_Pric_Segment_id');
5203 oe_debug_pub.add('Start time :'||to_char(sysdate,'MM/DD/YYYY:HH:MI:SSSS'));
5204 else
5205 write_log( 'Inside Update_Prod_Pric_Segment_id');
5206 write_log( 'Start time :'||to_char(sysdate,'MM/DD/YYYY:HH:MI:SSSS'));
5207 end if;
5208 -- update the product_segment_id column in qp_pricing_attributes
5209 segment_id_t.delete;
5210 context_t.delete;
5211 attribute_t.delete;
5212
5213 if (p_list_header_id IS null) THEN
5214 OPEN c_prod_seg_id_hdr_null;
5215 FETCH c_prod_seg_id_hdr_null BULK COLLECT INTO
5216 context_t,
5217 attribute_t;
5218 CLOSE c_prod_seg_id_hdr_null;
5219 ELSE
5220 OPEN c_prod_seg_id;
5221 FETCH c_prod_seg_id BULK COLLECT INTO
5222 context_t,
5223 attribute_t;
5224 CLOSE c_prod_seg_id;
5225 END if;
5226
5227 if context_t.count > 0 then
5228 if g_call_from_setup = 'Y' then
5229 oe_debug_pub.add('Context_t.count='||context_t.count);
5230 else
5231 write_log( 'Context_t.count='||context_t.count);
5232 end if;
5233 FOR i in 1..context_t.count
5234 LOOP
5235 select b.segment_id
5236 into segment_id_t(i)
5237 from qp_prc_contexts_b a, qp_segments_b b
5238 where b.prc_context_id = a.prc_context_id
5239 and a.PRC_CONTEXT_CODE = context_t(i)
5240 and b.SEGMENT_MAPPING_COLUMN = attribute_t(i);
5241 END LOOP;
5242
5243 FORALL j in 1..context_t.count
5244 update qp_pricing_attributes
5245 set product_segment_id = segment_id_t(j)
5246 where PRODUCT_ATTRIBUTE_CONTEXT = context_t(j)
5247 and PRODUCT_ATTRIBUTE = attribute_t(j)
5248 and list_header_id = nvl(p_list_header_id, list_header_id)
5249 and list_line_id between p_low_list_line_id and p_high_list_line_id;
5250
5251 if g_call_from_setup = 'Y' then
5252 oe_debug_pub.add('No of product segment ids updated='||SQL%ROWCOUNT);
5253 oe_debug_pub.add('End Time product segments :'||to_char(sysdate,'MM/DD/YYYY:HH:MI:SSSS'));
5254 else
5255 write_log( 'No of product segment ids updated='||SQL%ROWCOUNT);
5256 write_log( 'End time product segments :'||to_char(sysdate,'MM/DD/YYYY:HH:MI:SSSS'));
5257 end if;
5258 end if; -- context_t.count > 0
5259
5260 -- update the pricing_segment_id columns in qp_pricing_attributes
5261 segment_id_t.delete;
5262 context_t.delete;
5263 attribute_t.delete;
5264
5265 if (p_list_header_id IS null) THEN
5266 OPEN c_pric_seg_id_hdr_null;
5267 FETCH c_pric_seg_id_hdr_null BULK COLLECT INTO
5268 context_t,
5269 attribute_t;
5270 CLOSE c_pric_seg_id_hdr_null;
5271 else
5272 OPEN c_pric_seg_id;
5273 FETCH c_pric_seg_id BULK COLLECT INTO
5274 context_t,
5275 attribute_t;
5276 CLOSE c_pric_seg_id;
5277 END if;
5278
5279 if context_t.count > 0 then
5280 if g_call_from_setup = 'Y' then
5281 oe_debug_pub.add('Context_t.count='||context_t.count);
5282 else
5283 write_log( 'Context_t.count='||context_t.count);
5284 end if;
5285
5286 FOR i in 1..context_t.count
5287 LOOP
5288 select b.segment_id
5289 into segment_id_t(i)
5290 from qp_prc_contexts_b a, qp_segments_b b
5291 where b.prc_context_id = a.prc_context_id
5292 and a.PRC_CONTEXT_CODE = context_t(i)
5293 and b.SEGMENT_MAPPING_COLUMN = attribute_t(i);
5294 END LOOP;
5295
5296 FORALL j in 1..context_t.count
5297 update qp_pricing_attributes
5298 set pricing_segment_id = segment_id_t(j)
5299 where PRICING_ATTRIBUTE_CONTEXT = context_t(j)
5300 and PRICING_ATTRIBUTE = attribute_t(j)
5301 and list_header_id = nvl(p_list_header_id, list_header_id)
5302 and list_line_id between p_low_list_line_id and p_high_list_line_id;
5303
5304 if g_call_from_setup = 'Y' then
5305 oe_debug_pub.add('No of pricing segment ids updated='||SQL%ROWCOUNT);
5306 oe_debug_pub.add('End Time pricing segments :'||to_char(sysdate,'MM/DD/YYYY:HH:MI:SSSS'));
5307 else
5308 write_log( 'No of pricing segment ids updated='||SQL%ROWCOUNT);
5309 write_log( 'End time pricing segments :'||to_char(sysdate,'MM/DD/YYYY:HH:MI:SSSS'));
5310 end if;
5311 end if; -- context_t.count > 0
5312
5313 if g_call_from_setup = 'Y' then
5314 oe_debug_pub.add('End Update_Prod_Pric_Segment_id');
5315 else
5316 write_log( 'End Update_Prod_Pric_Segment_id');
5317 end if;
5318 exception
5319 when no_data_found then
5320 if g_call_from_setup = 'Y' then
5321 oe_debug_pub.add('No data found in Update_Prod_Pric_Segment_id');
5322 else
5323 write_log( 'No data found in Update_Prod_Pric_Segment_id');
5324 end if;
5325
5326 null;
5327
5328 when others then
5329 if g_call_from_setup = 'Y' then
5330 oe_debug_pub.add('PS_ATTR_GRP_PVT.Update_Prod_Pric_Segment_id ' || SQLERRM);
5331 else
5332 write_log( 'PS_ATTR_GRP_PVT.Update_Prod_Pric_Segment_id ' || SQLERRM );
5333 end if;
5334
5335 raise;
5336
5337 end Update_Prod_Pric_Segment_id;
5338
5339 PROCEDURE write_log(log_text VARCHAR2)
5340 IS
5341 BEGIN
5342 IF G_QP_DEBUG = 'Y' THEN
5343 FND_FILE.PUT_LINE( FND_FILE.LOG, log_text);
5344 END IF;
5345 END;
5346
5347 --Bug 10634202
5348 PROCEDURE Set_Debug(p_debug IN VARCHAR2 DEFAULT 'Y')
5349 IS
5350 BEGIN
5351 IF UPPER(p_debug) NOT IN (G_PAT_DEBUG_ON, G_PAT_DEBUG_OFF)
5352 THEN
5353 FND_FILE.PUT_LINE( FND_FILE.LOG, 'QP_PS_ATTR_GRP_PVT.Set_Debug: Invalid value of p_debug. Value should be either Y or N' );
5354 ELSE
5355 G_QP_DEBUG := UPPER(p_debug);
5356 END IF;
5357 END Set_Debug;
5358
5359 end QP_PS_ATTR_GRP_PVT; -- end package