[Home] [Help]
PACKAGE BODY: APPS.QP_ATTR_GRP_PVT
Source
1 package body QP_ATTR_GRP_PVT as
2 /* $Header: QPXVATGB.pls 120.2 2006/03/14 17:15:05 jhkuo noship $ */
3
4 G_LINES_PER_INSERT CONSTANT NUMBER := 5000;
5
6 procedure create_pattern_slabs(
7 p_total_lines IN number,
8 p_list_header_id IN number default null,
9 p_no_of_threads IN NUMBER default 1)
10 is
11 cursor list_line is
12 select list_line_id from qp_list_lines where qualification_ind in (8,10,12,14,28,30, 4,6,20,22)
13 and list_header_id = p_list_header_id
14 order by list_line_id;
15
16 l_gap number := 0;
17 l_list_line_id number := 0;
18 l_counter number := 0;
19 l_min_line number := 0;
20 l_max_line number := 0;
21 l_worker_count number := 0;
22 l_start_flag number := 0;
23 l_no_of_threads number := 0;
24
25 begin
26
27 if g_call_from_setup = 'Y' then
28 oe_debug_pub.add('Begin create pattern slabs');
29 else
30 fnd_file.put_line(FND_FILE.LOG, 'Begin create pattern slabs');
31 fnd_file.put_line(FND_FILE.LOG, 'Start time :'||to_char(sysdate,'MM/DD/YYYY:HH:MI:SSSS'));
32 end if;
33 l_no_of_threads := p_no_of_threads;
34 if l_no_of_threads > p_total_lines then
35 l_no_of_threads := p_total_lines;
36 end if;
37 l_gap := round(p_total_lines / l_no_of_threads, 0);
38
39 if g_call_from_setup = 'Y' then
40 oe_debug_pub.add('Total lines:'||p_total_lines);
41 oe_debug_pub.add('l_gap:'||l_gap);
42 else
43 fnd_file.put_line(FND_FILE.LOG, 'Total lines:'||p_total_lines);
44 fnd_file.put_line(FND_FILE.LOG, 'l_gap:'||l_gap);
45 end if;
46 if p_total_lines > 0 then
47 for line_rec in list_line loop
48
49 l_list_line_id := line_rec.list_line_id;
50 l_counter := l_counter + 1;
51
52 if l_start_flag = 0 then
53 l_start_flag := 1;
54 l_min_line := line_rec.list_line_id;
55 l_max_line := NULL;
56 l_worker_count := l_worker_count + 1;
57 end if;
58
59 if l_counter = l_gap and l_worker_count < l_no_of_threads
60 then
61 l_max_line := line_rec.list_line_id;
62
63 -- add l_worker_count, l_min_line, l_max_line into a pl/sql table
64 g_pattern_upg_slab_table(l_worker_count).worker := l_worker_count;
65 g_pattern_upg_slab_table(l_worker_count).low_list_line_id := l_min_line;
66 g_pattern_upg_slab_table(l_worker_count).high_list_line_id := l_max_line;
67
68 l_counter := 0;
69 l_start_flag := 0;
70
71 end if;
72
73 end loop;
74 l_max_line := l_list_line_id;
75 -- add l_worker_count, l_min_line, l_max_line into a pl/sql table
76 g_pattern_upg_slab_table(l_worker_count).worker := l_worker_count;
77 g_pattern_upg_slab_table(l_worker_count).low_list_line_id := l_min_line;
78 g_pattern_upg_slab_table(l_worker_count).high_list_line_id := l_max_line;
79
80 end if;
81 fnd_file.put_line(FND_FILE.LOG, 'End create pattern slabs');
82 fnd_file.put_line(FND_FILE.LOG, 'End time :'||to_char(sysdate,'MM/DD/YYYY:HH:MI:SSSS'));
83
84 end create_pattern_slabs;
85
86 procedure remove_duplicate_patterns
87 is
88
89 l_min_pattern_id number;
90
91 cursor c_dupl_pattern is
92 select distinct b.pattern_type, b.pattern_string, b.pattern_id
93 from qp_patterns a, qp_patterns b
94 where a.pattern_type = b.pattern_type
95 and a.pattern_string = b.pattern_string
96 and a.pattern_id <> b.pattern_id
97 and b.pattern_id >(select min(c.pattern_id)
98 from qp_patterns c
99 where c.pattern_type = a.pattern_type
100 and c.pattern_string = a.pattern_string);
101 begin
102
103 fnd_file.put_line(FND_FILE.LOG, 'In Remove_Duplicate_Patterns');
104 fnd_file.put_line(FND_FILE.LOG, 'Start time :'||to_char(sysdate,'MM/DD/YYYY:HH:MI:SSSS'));
105
106 g_pattern_pattern_id_final_tbl.delete;
107 g_pattern_pat_type_final_tbl.delete;
108 g_pattern_pat_string_final_tbl.delete;
109
110 open c_dupl_pattern;
111
112 FETCH c_dupl_pattern BULK COLLECT INTO
113 g_pattern_pat_type_final_tbl,
114 g_pattern_pat_string_final_tbl,
115 g_pattern_pattern_id_final_tbl;
116 CLOSE c_dupl_pattern;
117
118 fnd_file.put_line(FND_FILE.LOG, 'No of Duplicate_Patterns='||g_pattern_pattern_id_final_tbl.count);
119 if g_pattern_pattern_id_final_tbl.count > 0 then
120 for i in 1..g_pattern_pattern_id_final_tbl.count
121 loop
122 select min(c.pattern_id)
123 into l_min_pattern_id
124 from qp_patterns c
125 where c.pattern_type = g_pattern_pat_type_final_tbl(i)
126 and c.pattern_string = g_pattern_pat_string_final_tbl(i);
127
128 if g_pattern_pat_type_final_tbl(i) = 'PP' then
129 update /*+ index(lines QP_LIST_LINES_N9) */ qp_list_lines lines
130 set pattern_id = l_min_pattern_id
131 where pattern_id = g_pattern_pattern_id_final_tbl(i);
132 else
133 update qp_attribute_groups
134 set pattern_id = l_min_pattern_id
135 where pattern_id = g_pattern_pattern_id_final_tbl(i);
136 end if;
137
138 -- delete the records where pricing_phase_id matches to avoid duplicates
139 delete from qp_pattern_phases a
140 where a.pattern_id = g_pattern_pattern_id_final_tbl(i)
141 and a.pricing_phase_id in (select b.pricing_phase_id
142 from qp_pattern_phases b
143 where b.pattern_id = l_min_pattern_id);
144
145 -- update the records where pricing_phase_id DO NOT matches
146 update qp_pattern_phases
147 set pattern_id = l_min_pattern_id
148 where pattern_id = g_pattern_pattern_id_final_tbl(i);
149 end loop;
150 end if;
151
152 FORALL i in 1 .. G_pattern_pattern_id_final_tbl.count
153 DELETE qp_patterns
154 where pattern_id = g_pattern_pattern_id_final_tbl(i);
155
156 g_pattern_pattern_id_final_tbl.delete;
157 g_pattern_pat_type_final_tbl.delete;
158 g_pattern_pat_string_final_tbl.delete;
159
160 fnd_file.put_line(FND_FILE.LOG, 'End time :'||to_char(sysdate,'MM/DD/YYYY:HH:MI:SSSS'));
161 fnd_file.put_line(FND_FILE.LOG, 'COMMIT in remove_duplicate_patterns');
162 commit;
163
164 EXCEPTION
165 WHEN OTHERS THEN
166 if g_call_from_setup = 'Y' then
167 oe_debug_pub.add('ATTR_GRP_PVT.Remove_Duplicate_Patterns ' || SQLERRM);
168 else
169 FND_FILE.PUT_LINE( FND_FILE.LOG, 'ATTR_GRP_PVT.Remove_Duplicate_Patterns ' || SQLERRM );
170 end if;
171
172 end remove_duplicate_patterns;
173
174 PROCEDURE Pattern_Upgrade (
175 err_buff out NOCOPY VARCHAR2,
176 retcode out NOCOPY NUMBER,
177 p_list_header_id IN number default null,
178 p_low_list_line_id IN NUMBER default null,
179 p_high_list_line_id IN NUMBER default null,
180 p_no_of_threads IN NUMBER default 1,
181 p_spawned_request IN VARCHAR2 default 'N')
182 is
183 l_slab_count NUMBER;
184 l_count NUMBER;
185 l_new_request_id NUMBER;
186 l_no_of_threads NUMBER:=p_no_of_threads;
187 l_req_data VARCHAR2(10);
188 l_total_lines number := 0;
189
190 l_start_time number;
191 l_end_time number;
192 v_sid number;
193
194 l_qp_schema VARCHAR2(30);
195 l_stmt VARCHAR2(200);
196 l_status VARCHAR2(30);
197 l_industry VARCHAR2(30);
198
199
200 BEGIN
201 -- Check Java Engine Installed profile
202 if QP_JAVA_ENGINE_UTIL_PUB.Java_Engine_Installed = 'Y' then
203 fnd_file.put_line(FND_FILE.LOG, 'Java Engine Installed');
204 fnd_file.put_line(FND_FILE.LOG, 'p_list_header_id ' || p_list_header_id);
205 fnd_file.put_line(FND_FILE.LOG, 'p_low_list_line_id ' || p_low_list_line_id);
206 fnd_file.put_line(FND_FILE.LOG, 'p_high_list_line_id ' || p_high_list_line_id);
207 fnd_file.put_line(FND_FILE.LOG, 'p_no_of_threads ' || p_no_of_threads);
208 fnd_file.put_line(FND_FILE.LOG, 'p_spawned_request ' || p_spawned_request);
209
210 select sid into v_sid from v$session where audsid = userenv('SESSIONID');
211
212 fnd_file.put_line(FND_FILE.LOG, 'session ID = ' || v_sid);
213 else
214 fnd_file.put_line(FND_FILE.LOG, 'Java Engine not Installed');
215 return;
216 end if;
217
218 --refresh the pattern data
219 if p_spawned_request = 'N' then
220 if p_no_of_threads is NULL or p_no_of_threads = 0 then
221 l_no_of_threads := 1;
222 end if;
223 l_req_data := fnd_conc_global.request_data;
224 fnd_file.put_line(FND_FILE.LOG, 'l_req_data : ' || l_req_data);
225 if l_req_data is not NULL then
226 remove_duplicate_patterns;
227 return;
228 end if;
229
230 select hsecs into l_start_time from v$timer;
231 fnd_file.put_line(FND_FILE.LOG, 'Start time :'||to_char(sysdate,'MM/DD/YYYY:HH:MI:SSSS'));
232
233 if p_list_header_id is null then
234 -- like upgrade, refresh everything related to patterns
235 IF (FND_INSTALLATION.GET_APP_INFO('QP', l_status, l_industry, l_qp_schema))
236 THEN
237 l_stmt := 'TRUNCATE TABLE ' || l_qp_schema || '.qp_pattern_phases';
238 EXECUTE IMMEDIATE l_stmt;
239 l_stmt := 'TRUNCATE TABLE ' || l_qp_schema || '.qp_attribute_groups';
240 EXECUTE IMMEDIATE l_stmt;
241 l_stmt := 'TRUNCATE TABLE ' || l_qp_schema || '.qp_patterns';
242 EXECUTE IMMEDIATE l_stmt;
243 END IF;
244
245 fnd_file.put_line(FND_FILE.LOG, 'Deleted all records from 3 Pattern Master tables');
246 else
247 -- refresh only for the passed list_header_id
248 delete from qp_attribute_groups
249 where list_header_id = p_list_header_id
250 and list_line_id = -1;
251 fnd_file.put_line(FND_FILE.LOG, 'Deleted records from qp_attribute_groups for HP for list_header_id:'||p_list_header_id);
252
253 end if;
254 Update_Qual_Segment_id(p_list_header_id, null, -1, -1);
255 generate_hp_atgrps(p_list_header_id, null);
256
257 g_pattern_upg_slab_table.delete;
258 IF p_list_header_id IS NULL THEN
259 select count(*)
260 into l_total_lines
261 from qp_list_lines
262 where qualification_ind in (8,10,12,14,28,30, 4,6,20,22);
263 ELSE
264 select count(*)
265 into l_total_lines
266 from qp_list_lines
267 where qualification_ind in (8,10,12,14,28,30, 4,6,20,22)
268 and list_header_id = p_list_header_id;
269 END IF;
270 fnd_file.put_line(FND_FILE.LOG, 'l_total_lines ' || l_total_lines);
271 if l_total_lines > 0 then
272 create_pattern_slabs(l_total_lines, p_list_header_id, l_no_of_threads);
273 l_slab_count := g_pattern_upg_slab_table.count;
274 fnd_file.put_line(FND_FILE.LOG, 'l_slab_count ' || l_slab_count);
275 l_count := 1;
276 loop
277 l_new_request_id := fnd_request.submit_request(
278 'QP',
279 'QPXVATG',
280 'Pattern Upgrade '||to_char(l_count),
281 NULL,
282 TRUE,
283 p_list_header_id,
284 g_pattern_upg_slab_table(l_count).low_list_line_id,
285 g_pattern_upg_slab_table(l_count).high_list_line_id,
286 1,
287 'Y');
288 if l_new_request_id = 0 then
289 retcode := 2;
290 err_buff := fnd_message.get;
291 fnd_file.put_line(FND_FILE.LOG, 'err_buff ' || err_buff);
292 return;
293 end if;
294 FND_FILE.PUT_LINE(FND_FILE.LOG , 'Child '||l_count||' request_id: '||l_new_request_id);
295
296 l_count := l_count + 1;
297 exit when l_count > l_slab_count;
298 end loop;
299
300 select hsecs into l_end_time from v$timer;
301 fnd_file.put_line(FND_FILE.LOG, 'End time :'||to_char(sysdate,'MM/DD/YYYY:HH:MI:SSSS'));
302 fnd_file.put_line(fnd_file.log, 'Time taken for the header process (sec):' ||(l_end_time - l_start_time)/100);
303
304 fnd_conc_global.set_req_globals(conc_status => 'PAUSED',
305 request_data => to_char(l_count));
306
307 fnd_file.put_line(FND_FILE.LOG, 'Time after parent request PAUSE over :'||to_char(sysdate,'MM/DD/YYYY:HH:MI:SSSS'));
308
309 else -- if l_total_lines > 0
310 if p_list_header_id is null then
311 -- like upgrade, refresh everything related to patterns
312 update qp_list_lines
313 set pattern_id = null,
314 pricing_attribute_count = null,
315 product_uom_code = null,
316 hash_key = null,
317 cache_key = null
318 where cache_key is not null;
319
320 else
321 -- refresh only for the passed list_header_id
322
323 delete from qp_attribute_groups
324 where list_header_id = p_list_header_id
325 and list_line_id <> -1;
326
327 update qp_list_lines
328 set pattern_id = null,
329 pricing_attribute_count = null,
330 product_uom_code = null,
331 hash_key = null,
332 cache_key = null
333 where cache_key is not null
334 and list_header_id = p_list_header_id;
335 end if;
336
337 end if; -- if l_total_lines > 0
338 end if; -- if p_spawned_request = 'N'
339
340 if p_spawned_request = 'Y' then
341 select hsecs into l_start_time from v$timer;
342 fnd_file.put_line(FND_FILE.LOG, 'Start time :'||to_char(sysdate,'MM/DD/YYYY:HH:MI:SSSS'));
343 if p_list_header_id is null then
344 -- like upgrade, refresh everything related to patterns
345 update /*+ index_asc(lines QP_LIST_LINES_PK) */ qp_list_lines lines
346 set pattern_id = null,
347 pricing_attribute_count = null,
348 product_uom_code = null,
349 hash_key = null,
350 cache_key = null
351 where cache_key is not null
352 and list_line_id between p_low_list_line_id and p_high_list_line_id;
353 else
354 -- refresh only for the passed list_header_id
355
356 delete from qp_attribute_groups
357 where list_header_id = p_list_header_id
358 and list_line_id between p_low_list_line_id and p_high_list_line_id;
359
360 update /*+ index_asc(lines QP_LIST_LINES_PK) */ qp_list_lines lines
361 set pattern_id = null,
362 pricing_attribute_count = null,
363 product_uom_code = null,
364 hash_key = null,
365 cache_key = null
366 where cache_key is not null
367 and list_header_id = p_list_header_id
368 and list_line_id between p_low_list_line_id and p_high_list_line_id;
369 end if;
370 -- update the segment_id columns for qualifiers
371 Update_Qual_Segment_id(p_list_header_id, null,
372 p_low_list_line_id,
373 p_high_list_line_id);
374
375 -- update the product_segment_id and pricing_segment_id columns in
376 -- qp_pricing_attributes
377 Update_Prod_Pric_Segment_id(p_list_header_id,
378 p_low_list_line_id,
379 p_high_list_line_id);
380
384 update_pp_lines(p_list_header_id,
381 generate_lp_atgrps(p_list_header_id, null,
382 p_low_list_line_id,
383 p_high_list_line_id);
385 p_low_list_line_id,
386 p_high_list_line_id);
387 select hsecs into l_end_time from v$timer;
388 fnd_file.put_line(FND_FILE.LOG, 'End time :'||to_char(sysdate,'MM/DD/YYYY:HH:MI:SSSS'));
389 fnd_file.put_line(fnd_file.log, 'Time taken for the line process (sec):' ||(l_end_time - l_start_time)/100);
390 end if; -- if p_spawned_request = 'Y'
391 -- commit the changes done so far
392 fnd_file.put_line(FND_FILE.LOG, 'COMMIT in Pattern_Upgrade');
393 commit;
394
395
396 err_buff := '';
397 retcode := 0;
398 exception
399 when others then
400 if g_call_from_setup = 'Y' then
401 oe_debug_pub.add('ATTR_GRP_PVT.Pattern_Upgrade ' || SQLERRM);
402 else
403 FND_FILE.PUT_LINE( FND_FILE.LOG, 'ATTR_GRP_PVT.Pattern_Upgrade ' || SQLERRM );
404 end if;
405 err_buff := 'Others Error in procedure Pattern_Upgrade';
406 retcode := 1;
407
408 end Pattern_Upgrade;
409
410
411 procedure generate_hp_atgrps(p_list_header_id number
412 ,p_qualifier_group number)
413 is
414 cursor c_attr_grp_hq_csr is
415 select qpq.list_header_id,
416 qpq.list_line_id,
417 qpq.segment_id,
418 qpq.active_flag,
419 qpq.list_type_code,
420 qpq.start_date_active start_date_active_q,
421 qpq.end_date_active end_date_active_q,
422 qph.currency_code,
423 qph.ask_for_flag,
424 qph.limit_exists_flag limit_exists,
425 qph.source_system_code,
426 qpq.qualifier_precedence effective_precedence,
427 qpq.qualifier_grouping_no,
428 qpq.comparison_operator_code,
429 -1 pricing_phase_id,
430 null modifier_level_code,
431 qpq.qualifier_datatype attribute_datatype,
432 qpq.qualifier_attr_value attribute_value,
433 'QUAL' attribute_type
434 from qp_qualifiers qpq,
435 qp_list_headers_b qph
436 where qpq.list_line_id = -1
437 and qph.list_header_id = qpq.list_header_id
438 and qpq.list_header_id = nvl(p_list_header_id, qpq.list_header_id)
439 and ((p_qualifier_group is not null and qpq.qualifier_grouping_no in (-1, p_qualifier_group))
440 OR
441 (p_qualifier_group is null)
442 )
443 and ((qpq.list_type_code = 'PRL' and qpq.qualifier_context <> 'MODLIST'
444 and qpq.qualifier_attribute <> 'QUALIFIER_ATTRIBUTE4')
445 OR
446 (qpq.list_type_code <> 'PRL')
447 )
448 order by qpq.list_header_id, qpq.list_line_id, qpq.segment_id;
449 begin
450 -- delete the data from cursor, temp and final tables to start with
451 if g_call_from_setup = 'Y' then
452 oe_debug_pub.add('Begin generate_hp_atgrps');
453 else
454 fnd_file.put_line(FND_FILE.LOG, 'Begin generate_hp_atgrps');
455 end if;
456 Reset_c_tables;
457 Reset_tmp_tables;
458 Reset_final_tables;
459
460 open c_attr_grp_hq_csr;
461
462 FETCH c_attr_grp_hq_csr BULK COLLECT INTO
463 g_list_header_id_c_tbl,
464 g_list_line_id_c_tbl,
465 g_segment_id_c_tbl,
466 g_active_flag_c_tbl,
467 g_list_type_code_c_tbl,
468 g_start_date_active_q_c_tbl,
469 g_end_date_active_q_c_tbl,
470 g_currency_code_c_tbl,
471 g_ask_for_flag_c_tbl,
472 g_limit_exists_c_tbl,
473 g_source_system_code_c_tbl,
474 g_effective_precedence_c_tbl,
475 g_qual_grouping_no_c_tbl,
476 g_comparison_opr_code_c_tbl,
477 g_pricing_phase_id_c_tbl,
478 g_modifier_level_code_c_tbl,
479 g_qual_datatype_c_tbl,
480 g_qual_attr_val_c_tbl,
481 g_attribute_type_c_tbl;
482
483 CLOSE c_attr_grp_hq_csr;
484
485 if g_list_header_id_c_tbl.count > 0 then
486 process_c_tables('HP');
487 end if;
488 if g_call_from_setup = 'Y' then
489 oe_debug_pub.add('End generate_hp_atgrps');
490 else
491 fnd_file.put_line(FND_FILE.LOG, 'End generate_hp_atgrps');
492 end if;
493
494 EXCEPTION
495 WHEN OTHERS THEN
496 if g_call_from_setup = 'Y' then
497 oe_debug_pub.add('ATTR_GRP_PVT.Generate_Hp_Atgrps ' || SQLERRM);
498 else
499 FND_FILE.PUT_LINE( FND_FILE.LOG, 'ATTR_GRP_PVT.Generate_Hp_Atgrps ' || SQLERRM );
500 end if;
501
502 end generate_hp_atgrps;
503
504 procedure generate_lp_atgrps(p_list_header_id number
505 -- ,p_list_line_id number
506 ,p_qualifier_group number
507 ,p_low_list_line_id IN NUMBER
508 ,p_high_list_line_id IN NUMBER)
509 is
510 cursor c_attr_grp_lq_csr is
511 select * from
512 (select /*+ ordered use_nl(qpq, qph) index(qpl qp_list_lines_pk) */ qpq.list_header_id,
513 qpq.list_line_id,
514 qpq.segment_id,
515 qpq.active_flag,
516 qpq.list_type_code,
517 qpq.start_date_active start_date_active_q,
518 qpq.end_date_active end_date_active_q,
519 qph.currency_code,
520 qph.ask_for_flag,
521 qpl.limit_exists_flag limit_exists,
522 qph.source_system_code,
523 qpq.qualifier_precedence effective_precedence,
524 qpq.qualifier_grouping_no,
528 qpq.qualifier_datatype attribute_datatype,
525 qpq.comparison_operator_code,
526 qpl.pricing_phase_id pricing_phase_id,
527 qpl.modifier_level_code modifier_level_code,
529 qpq.qualifier_attr_value attribute_value,
530 'QUAL' attribute_type
531 from qp_list_lines qpl, qp_qualifiers qpq, qp_list_headers_b qph
532 where qpq.list_line_id <> -1
533 and qph.list_header_id = qpq.list_header_id
534 and qpl.list_header_id = qph.list_header_id
535 and qpl.list_line_id = qpq.list_line_id
536 and qpl.pricing_phase_id > 1
537 and qpl.qualification_ind in (8,10,12,14,28,30)
538 and qpl.list_line_id between p_low_list_line_id and p_high_list_line_id
539 and ((p_qualifier_group is not null and qpq.qualifier_grouping_no in (-1, p_qualifier_group))
540 OR
541 (p_qualifier_group is null)
542 )
543 union
544 select /*+ ordered use_nl(qpa, qph) index(qpl qp_list_lines_pk) */ distinct qpl.list_header_id,
545 qpl.list_line_id,
546 qpa.product_segment_id segment_id,
547 qph.active_flag,
548 qph.list_type_code,
549 to_date(null) start_date_active_q,
550 to_date(null) end_date_active_q,
551 qph.currency_code,
552 qph.ask_for_flag,
553 qpl.limit_exists_flag limit_exists,
554 qph.source_system_code,
555 qpl.product_precedence effective_precedence,
556 -1 qualifier_grouping_no,
557 '=' comparison_operator_code,
558 qpl.pricing_phase_id pricing_phase_id,
559 qpl.modifier_level_code modifier_level_code,
560 qpa.product_attribute_datatype attribute_datatype,
561 qpa.product_attr_value attribute_value,
562 'PROD' attribute_type
563 from qp_list_lines qpl, qp_pricing_attributes qpa, qp_list_headers_b qph
564 where qph.list_header_id = qpl.list_header_id
565 and qpl.list_line_id = qpa.list_line_id
566 and qpa.excluder_flag = 'N'
567 and qpl.pricing_phase_id > 1
568 and qpl.qualification_ind in (8,10,12,14,28,30)
569 and qpa.product_attribute_context is not null
570 and (qpa.pricing_attribute_context = 'VOLUME' or
571 qpa.pricing_attribute_context is null
572 )
573 and qpl.list_line_id between p_low_list_line_id and p_high_list_line_id
574 union
575 select /*+ ordered use_nl(qpa, qph) index(qpl qp_list_lines_pk) */ qpl.list_header_id,
576 qpl.list_line_id,
577 qpa.pricing_segment_id segment_id,
578 qph.active_flag,
579 qph.list_type_code,
580 to_date(null) start_date_active_q,
581 to_date(null) end_date_active_q,
582 qph.currency_code,
583 qph.ask_for_flag,
584 qpl.limit_exists_flag limit_exists,
585 qph.source_system_code,
586 qpl.product_precedence effective_precedence,
587 -1 qualifier_grouping_no,
588 qpa.comparison_operator_code,
589 qpl.pricing_phase_id pricing_phase_id,
590 qpl.modifier_level_code modifier_level_code,
591 qpa.pricing_attribute_datatype attribute_datatype,
592 qpa.pricing_attr_value_from attribute_value,
593 'PRIC' attribute_type
594 from qp_list_lines qpl, qp_pricing_attributes qpa, qp_list_headers_b qph
595 where qph.list_header_id = qpl.list_header_id
596 and qpl.list_line_id = qpa.list_line_id
597 and qpl.pricing_phase_id > 1
598 and qpl.qualification_ind in (8,10,12,14,28,30)
599 and qpa.pricing_attribute_context is not null
600 and qpl.list_line_id between p_low_list_line_id and p_high_list_line_id
601 ) attr_view
602 order by attr_view.list_header_id, attr_view.list_line_id, attr_view.segment_id;
603 begin
604 -- delete the data from cursor, temp and final tables to start with
605 Reset_c_tables;
606 Reset_tmp_tables;
607 Reset_final_tables;
608
609 open c_attr_grp_lq_csr;
610
611 FETCH c_attr_grp_lq_csr BULK COLLECT INTO
612 g_list_header_id_c_tbl,
613 g_list_line_id_c_tbl,
614 g_segment_id_c_tbl,
615 g_active_flag_c_tbl,
616 g_list_type_code_c_tbl,
617 g_start_date_active_q_c_tbl,
618 g_end_date_active_q_c_tbl,
619 g_currency_code_c_tbl,
620 g_ask_for_flag_c_tbl,
621 g_limit_exists_c_tbl,
622 g_source_system_code_c_tbl,
623 g_effective_precedence_c_tbl,
624 g_qual_grouping_no_c_tbl,
625 g_comparison_opr_code_c_tbl,
626 g_pricing_phase_id_c_tbl,
627 g_modifier_level_code_c_tbl,
628 g_qual_datatype_c_tbl,
629 g_qual_attr_val_c_tbl,
630 g_attribute_type_c_tbl;
631
632 CLOSE c_attr_grp_lq_csr;
633
634 if g_list_header_id_c_tbl.count > 0 then
635 process_c_tables('LP');
636 end if;
637
638 EXCEPTION
639 WHEN OTHERS THEN
640 if g_call_from_setup = 'Y' then
641 oe_debug_pub.add('ATTR_GRP_PVT.Generate_Lp_Atgrps ' || SQLERRM);
642 else
643 FND_FILE.PUT_LINE( FND_FILE.LOG, 'ATTR_GRP_PVT.Generate_Lp_Atgrps ' || SQLERRM );
644 end if;
645
646 end generate_lp_atgrps;
647
648 procedure update_pp_lines(p_list_header_id number
649 -- ,p_list_line_id number
650 ,p_low_list_line_id IN NUMBER
651 ,p_high_list_line_id IN NUMBER)
652 is
653 cursor c_lines_pp_csr is
654 select * from
655 (select /*+ ordered use_nl(qpa) index(qpl qp_list_lines_pk) */ distinct qpa.list_header_id,
659 qpa.pricing_phase_id,
656 qpa.list_line_id,
657 qpa.product_segment_id segment_id,
658 '=' comparison_operator_code,
660 qpa.product_uom_code,
661 qpa.product_attribute_datatype attribute_datatype,
662 qpa.product_attr_value attribute_value,
663 'PROD' attribute_type
664 from qp_list_lines qpl,
665 qp_pricing_attributes qpa
666 where qpl.list_line_id = qpa.list_line_id
667 and qpa.excluder_flag = 'N'
668 and qpl.qualification_ind in (4,6,20,22)
669 and qpa.product_attribute_context is not null
670 and (qpa.pricing_attribute_context = 'VOLUME' or
671 qpa.pricing_attribute_context is null
672 )
673 and qpl.list_line_id between p_low_list_line_id and p_high_list_line_id
674 union
675 select /*+ ordered use_nl(qpa) index(qpl qp_list_lines_pk) */ qpa.list_header_id,
676 qpa.list_line_id,
677 qpa.pricing_segment_id segment_id,
678 qpa.comparison_operator_code,
679 qpa.pricing_phase_id,
680 qpa.product_uom_code,
681 qpa.pricing_attribute_datatype attribute_datatype,
682 qpa.pricing_attr_value_from attribute_value,
683 'PRIC' attribute_type
684 from qp_list_lines qpl,
685 qp_pricing_attributes qpa
686 where qpl.list_line_id = qpa.list_line_id
687 and qpl.qualification_ind in (20,22)
688 and qpa.pricing_attribute_context is not null
689 and qpl.list_line_id between p_low_list_line_id and p_high_list_line_id
690 ) attr_view
691 order by attr_view.list_header_id, attr_view.list_line_id, attr_view.segment_id;
692 begin
693 -- delete the data from cursor, temp and final tables to start with
694 Reset_c_tables;
695 Reset_tmp_tables;
696 Reset_final_tables;
697
698 open c_lines_pp_csr;
699
700 FETCH c_lines_pp_csr BULK COLLECT INTO
701 g_list_header_id_c_tbl,
702 g_list_line_id_c_tbl,
703 g_segment_id_c_tbl,
704 g_comparison_opr_code_c_tbl,
705 g_pricing_phase_id_c_tbl,
706 g_product_uom_code_c_tbl,
707 g_qual_datatype_c_tbl,
708 g_qual_attr_val_c_tbl,
709 g_attribute_type_c_tbl;
710
711 CLOSE c_lines_pp_csr;
712
713 if g_list_header_id_c_tbl.count > 0 then
714 process_c_tables_pp('PP');
715 end if;
716
717 EXCEPTION
718 WHEN OTHERS THEN
719 if g_call_from_setup = 'Y' then
720 oe_debug_pub.add('ATTR_GRP_PVT.Update_Pp_Lines ' || SQLERRM);
721 else
722 FND_FILE.PUT_LINE( FND_FILE.LOG, 'ATTR_GRP_PVT.Update_Pp_Lines ' || SQLERRM );
723 end if;
724
725 end update_pp_lines;
726
727 procedure process_c_tables(p_pattern_type VARCHAR2)
728 is
729 l_old_list_header_id number;
730 l_old_list_line_id number;
731 l_current_grp number;
732 l_pat_tmp_index number;
733 other_grp_index number;
734 l_prefix_value_from_null_grp varchar2(1);
735 l_debug VARCHAR2(3);
736 l_line_counter number := 0;
737
738 begin
739 l_old_list_header_id := g_init_val;
740 l_old_list_line_id := g_init_val;
741
742 if g_call_from_setup = 'Y' then
743 oe_debug_pub.add('Start process_c_tables - Pattern type '||p_pattern_type);
744 oe_debug_pub.add('cursor tables total = ' || g_list_header_id_c_tbl.count);
745 else
746 QP_PREQ_GRP.Set_QP_Debug;
747 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
748 IF l_debug = FND_API.G_TRUE THEN
749 fnd_file.put_line(FND_FILE.LOG, 'Start process_c_tables - Pattern type '||p_pattern_type);
750 fnd_file.put_line(FND_FILE.LOG, 'cursor tables total = ' || g_list_header_id_c_tbl.count);
751 END IF;
752 end if;
753
754 if g_list_header_id_c_tbl.count > 0 then
755 for i in 1..g_list_header_id_c_tbl.count
756 loop
757 if g_call_from_setup = 'Y' then
758 oe_debug_pub.add('i = ' || i ||
759 ', list_header_id = ' || g_list_header_id_c_tbl(i) ||
760 ', list_line_id = ' || g_list_line_id_c_tbl(i) ||
761 ', group = ' || g_qual_grouping_no_c_tbl(i) ||
762 ', operator = ' || g_comparison_opr_code_c_tbl(i));
763 --oe_debug_pub.add('l_old_list_header_id ' || l_old_list_header_id);
764 --oe_debug_pub.add('l_old_list_line_id ' || l_old_list_line_id);
765 elsif l_debug = FND_API.G_TRUE then
766 fnd_file.put_line(FND_FILE.LOG, 'i = ' || i ||
767 ', list_header_id = ' || g_list_header_id_c_tbl(i) ||
768 ', list_line_id = ' || g_list_line_id_c_tbl(i) ||
769 ', group = ' || g_qual_grouping_no_c_tbl(i) ||
770 ', operator = ' || g_comparison_opr_code_c_tbl(i));
771 --fnd_file.put_line(FND_FILE.LOG, 'l_old_list_header_id ' || l_old_list_header_id);
772 --fnd_file.put_line(FND_FILE.LOG, 'l_old_list_line_id ' || l_old_list_line_id);
773 end if;
774
775 if (i > 1
776 and (g_list_header_id_c_tbl(i) <> l_old_list_header_id
777 or g_list_line_id_c_tbl(i) <> l_old_list_line_id)
778 ) then
779 Move_data_from_tmp_to_final(p_pattern_type);
780 Reset_tmp_tables;
781
785 if g_call_from_setup = 'Y' then
782 -- logic to insert into tables every G_LINES_PER_INSERT lines
783 l_line_counter := l_line_counter + 1;
784 if l_line_counter >= G_LINES_PER_INSERT then
786 oe_debug_pub.add('inserting data for ' || G_LINES_PER_INSERT || ' lines');
787 elsif l_debug = FND_API.G_TRUE then
788 fnd_file.put_line(FND_FILE.LOG, 'inserting data for ' || G_LINES_PER_INSERT || ' lines');
789 end if;
790 populate_atgrps;
791 if p_pattern_type = 'LP' then
792 update_list_lines_cache_key;
793 end if;
794 reset_final_tables;
795 if g_call_from_setup <> 'Y' then
796 if l_debug = FND_API.G_TRUE then
797 fnd_file.put_line(FND_FILE.LOG, 'committing data for ' || G_LINES_PER_INSERT || ' lines');
798 end if;
799 commit;
800 end if;
801 l_line_counter := 0;
802 end if;
803 end if;
804 l_current_grp := g_qual_grouping_no_c_tbl(i);
805
806 if g_qual_grouping_no_tmp_tbl.exists(l_current_grp) = TRUE then
807
808 /*
809 if g_call_from_setup = 'Y' then
810 oe_debug_pub.add('record exists in temp table for current group');
811 oe_debug_pub.add('current record Operator is ' || g_comparison_opr_code_c_tbl(i));
812 else
813 fnd_file.put_line(FND_FILE.LOG, 'record exists in temp table for current group');
814 fnd_file.put_line(FND_FILE.LOG, 'current record Operator is ' || g_comparison_opr_code_c_tbl(i));
815 end if;
816 */
817
818 -- if grp_no -1 record is getting updated then update the other grps records for pattern string,
819 -- hash key, effctive dates of qualifiers, effective precedence
820 if l_current_grp = -1 then
821 other_grp_index := g_qual_grouping_no_tmp_tbl.first;
822 while other_grp_index is not null
823 LOOP
824 /*
825 if g_call_from_setup = 'Y' then
826 oe_debug_pub.add('other_grp_index = ' || other_grp_index);
827 else
828 fnd_file.put_line(FND_FILE.LOG, 'other_grp_index = ' || other_grp_index);
829 end if;
830 */
831 -- do not update the -1 grp no record as -1 grp no record will be updated automatically
832 -- outside this loop
833 if other_grp_index = -1 then
834 null;
835 else
836 if (g_start_date_active_q_c_tbl(i) is not null
837 and g_start_date_active_q_tmp_tbl(other_grp_index) is not null
838 and g_start_date_active_q_c_tbl(i) > g_start_date_active_q_tmp_tbl(other_grp_index)) then
839 g_start_date_active_q_tmp_tbl(other_grp_index) := g_start_date_active_q_c_tbl(i);
840 elsif (g_start_date_active_q_tmp_tbl(other_grp_index) is null
841 and g_start_date_active_q_c_tbl(i) is not null) then
842 g_start_date_active_q_tmp_tbl(other_grp_index) := g_start_date_active_q_c_tbl(i);
843 end if;
844
845 if (g_end_date_active_q_c_tbl(i) is not null
846 and g_end_date_active_q_tmp_tbl(other_grp_index) is not null
847 and g_end_date_active_q_c_tbl(i) < g_end_date_active_q_tmp_tbl(other_grp_index)) then
848 g_end_date_active_q_tmp_tbl(other_grp_index) := g_end_date_active_q_c_tbl(i);
849 elsif (g_end_date_active_q_tmp_tbl(other_grp_index) is null
850 and g_end_date_active_q_c_tbl(i) is not null) then
851 g_end_date_active_q_tmp_tbl(other_grp_index) := g_end_date_active_q_c_tbl(i);
852 end if;
853
854 g_limit_exists_tmp_tbl(other_grp_index) := nvl(g_limit_exists_c_tbl(i), 'N');
855
856 if g_effective_precedence_c_tbl(i) < g_effective_precedence_tmp_tbl(other_grp_index) then
857 g_effective_precedence_tmp_tbl(other_grp_index) := g_effective_precedence_c_tbl(i);
858 end if;
859
860 if g_comparison_opr_code_c_tbl(i) = '=' then
861 if g_pat_string_tmp_tbl(other_grp_index) is not null then
862 g_pat_string_tmp_tbl(other_grp_index) := g_pat_string_tmp_tbl(other_grp_index) || g_delimiter || g_segment_id_c_tbl(i);
863 else
864 g_pat_string_tmp_tbl(other_grp_index) := g_segment_id_c_tbl(i);
865 end if;
866
867 if g_hash_key_tmp_tbl(other_grp_index) is not null then
868 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);
869 else
870 g_hash_key_tmp_tbl(other_grp_index) := g_qual_attr_val_c_tbl(i);
871 end if;
872
873 if g_attribute_type_c_tbl(i) = 'PROD' then
874 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);
875 end if;
876 end if; -- g_comparison_opr_code_c_tbl(i) = '='
877
878 end if; -- other_grp_index = -1
879
880 other_grp_index := g_qual_grouping_no_tmp_tbl.next(other_grp_index);
881 END LOOP; -- other_grp_index is not null
882
886 and g_start_date_active_q_tmp_tbl(l_current_grp) is not null
883 end if; -- l_current_grp = -1
884
885 if (g_start_date_active_q_c_tbl(i) is not null
887 and g_start_date_active_q_c_tbl(i) > g_start_date_active_q_tmp_tbl(l_current_grp)) then
888 g_start_date_active_q_tmp_tbl(l_current_grp) := g_start_date_active_q_c_tbl(i);
889 elsif (g_start_date_active_q_tmp_tbl(l_current_grp) is null
890 and g_start_date_active_q_c_tbl(i) is not null) then
891 g_start_date_active_q_tmp_tbl(l_current_grp) := g_start_date_active_q_c_tbl(i);
892 end if;
893
894 if (g_end_date_active_q_c_tbl(i) is not null
895 and g_end_date_active_q_tmp_tbl(l_current_grp) is not null
896 and g_end_date_active_q_c_tbl(i) < g_end_date_active_q_tmp_tbl(l_current_grp)) then
897 g_end_date_active_q_tmp_tbl(l_current_grp) := g_end_date_active_q_c_tbl(i);
898 elsif (g_end_date_active_q_tmp_tbl(l_current_grp) is null
899 and g_end_date_active_q_c_tbl(i) is not null) then
900 g_end_date_active_q_tmp_tbl(l_current_grp) := g_end_date_active_q_c_tbl(i);
901 end if;
902
903 g_limit_exists_tmp_tbl(l_current_grp) := nvl(g_limit_exists_c_tbl(i), 'N');
904
905 if g_effective_precedence_c_tbl(i) < g_effective_precedence_tmp_tbl(l_current_grp) then
906 g_effective_precedence_tmp_tbl(l_current_grp) := g_effective_precedence_c_tbl(i);
907 end if;
908
909 if g_comparison_opr_code_c_tbl(i) = '=' then
910 if g_pat_string_tmp_tbl(l_current_grp) is not null then
911 g_pat_string_tmp_tbl(l_current_grp) := g_pat_string_tmp_tbl(l_current_grp) || g_delimiter || g_segment_id_c_tbl(i);
912 else
913 g_pat_string_tmp_tbl(l_current_grp) := g_segment_id_c_tbl(i);
914 end if;
915
916 if g_hash_key_tmp_tbl(l_current_grp) is not null then
917 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);
918 else
919 g_hash_key_tmp_tbl(l_current_grp) := g_qual_attr_val_c_tbl(i);
920 end if;
921
922 if g_attribute_type_c_tbl(i) = 'PROD' then
923 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);
924 end if;
925
926 -- populate the pattern temp table
927 l_pat_tmp_index := g_pattern_grouping_no_tmp_tbl.count;
928
929 g_pattern_grouping_no_tmp_tbl(l_pat_tmp_index + 1) := g_qual_grouping_no_c_tbl(i);
930 g_pattern_segment_id_tmp_tbl(l_pat_tmp_index + 1) := g_segment_id_c_tbl(i);
931 end if; -- g_comparison_opr_code_c_tbl(i) = '='
932
933 else -- record does not exists for the group
934 /*
935 if g_call_from_setup = 'Y' then
936 oe_debug_pub.add('record DOES NOT exists in temp table for current group');
937 oe_debug_pub.add('current record Operator is ' || g_comparison_opr_code_c_tbl(i));
938 else
939 fnd_file.put_line(FND_FILE.LOG, 'record DOES NOT exists in temp table for current group');
940 fnd_file.put_line(FND_FILE.LOG, 'current record Operator is ' || g_comparison_opr_code_c_tbl(i));
941 end if;
942 */
943
944 l_prefix_value_from_null_grp := 'N';
945 -- if grp_no -1 record is getting created then update the other grps records for pattern string,
946 -- hash key, effctive dates of qualifiers, effective precedence
947 if l_current_grp = -1 then
948 other_grp_index := g_qual_grouping_no_tmp_tbl.first;
949 while other_grp_index is not null
950 LOOP
951 /*
952 if g_call_from_setup = 'Y' then
953 oe_debug_pub.add('other_grp_index = ' || other_grp_index);
954 else
955 fnd_file.put_line(FND_FILE.LOG, 'other_grp_index = ' || other_grp_index);
956 end if;
957 */
958
959 if (g_start_date_active_q_c_tbl(i) is not null
960 and g_start_date_active_q_tmp_tbl(other_grp_index) is not null
961 and g_start_date_active_q_c_tbl(i) > g_start_date_active_q_tmp_tbl(other_grp_index)) then
962 g_start_date_active_q_tmp_tbl(other_grp_index) := g_start_date_active_q_c_tbl(i);
963 elsif (g_start_date_active_q_tmp_tbl(other_grp_index) is null
964 and g_start_date_active_q_c_tbl(i) is not null) then
965 g_start_date_active_q_tmp_tbl(other_grp_index) := g_start_date_active_q_c_tbl(i);
966 end if;
967
968 if (g_end_date_active_q_c_tbl(i) is not null
969 and g_end_date_active_q_tmp_tbl(other_grp_index) is not null
970 and g_end_date_active_q_c_tbl(i) < g_end_date_active_q_tmp_tbl(other_grp_index)) then
971 g_end_date_active_q_tmp_tbl(other_grp_index) := g_end_date_active_q_c_tbl(i);
972 elsif (g_end_date_active_q_tmp_tbl(other_grp_index) is null
973 and g_end_date_active_q_c_tbl(i) is not null) then
974 g_end_date_active_q_tmp_tbl(other_grp_index) := g_end_date_active_q_c_tbl(i);
975 end if;
976
977 g_limit_exists_tmp_tbl(other_grp_index) := nvl(g_limit_exists_c_tbl(i), 'N');
978
979 if g_effective_precedence_c_tbl(i) < g_effective_precedence_tmp_tbl(other_grp_index) then
983 if g_comparison_opr_code_c_tbl(i) = '=' then
980 g_effective_precedence_tmp_tbl(other_grp_index) := g_effective_precedence_c_tbl(i);
981 end if;
982
984 if g_pat_string_tmp_tbl(other_grp_index) is not null then
985 g_pat_string_tmp_tbl(other_grp_index) := g_pat_string_tmp_tbl(other_grp_index) || g_delimiter || g_segment_id_c_tbl(i);
986 else
987 g_pat_string_tmp_tbl(other_grp_index) := g_segment_id_c_tbl(i);
988 end if;
989
990 if g_hash_key_tmp_tbl(other_grp_index) is not null then
991 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);
992 else
993 g_hash_key_tmp_tbl(other_grp_index) := g_qual_attr_val_c_tbl(i);
994 end if;
995
996 if g_attribute_type_c_tbl(i) = 'PROD' then
997 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);
998 end if;
999 end if; -- g_comparison_opr_code_c_tbl(i) = '='
1000
1001 other_grp_index := g_qual_grouping_no_tmp_tbl.next(other_grp_index);
1002 END LOOP; -- other_grp_index is not null
1003 else
1004 -- if l_current_grp is not -1 then look for existence of -1 grp no record, if exists then
1005 -- use it for populating pattern string, hash key, cache key, qualifier dates, precedence etc.
1006 if g_qual_grouping_no_tmp_tbl.exists(-1) = TRUE then
1007 l_prefix_value_from_null_grp := 'Y';
1008 end if;
1009 end if; -- l_current_grp = -1
1010
1011 g_list_header_id_tmp_tbl(l_current_grp) := g_list_header_id_c_tbl(i);
1012 g_list_line_id_tmp_tbl(l_current_grp) := g_list_line_id_c_tbl(i);
1013 g_active_flag_tmp_tbl(l_current_grp) := g_active_flag_c_tbl(i);
1014 g_list_type_code_tmp_tbl(l_current_grp) := g_list_type_code_c_tbl(i);
1015 if l_prefix_value_from_null_grp = 'Y' then
1016 if (g_start_date_active_q_c_tbl(i) is not null
1017 and g_start_date_active_q_tmp_tbl(-1) is not null) then
1018 if g_start_date_active_q_c_tbl(i) > g_start_date_active_q_tmp_tbl(-1) then
1019 g_start_date_active_q_tmp_tbl(l_current_grp) := g_start_date_active_q_c_tbl(i);
1020 else
1021 g_start_date_active_q_tmp_tbl(l_current_grp) := g_start_date_active_q_tmp_tbl(-1);
1022 end if;
1023 elsif (g_start_date_active_q_tmp_tbl(-1) is null
1024 and g_start_date_active_q_c_tbl(i) is not null) then
1025 g_start_date_active_q_tmp_tbl(l_current_grp) := g_start_date_active_q_c_tbl(i);
1026 elsif (g_start_date_active_q_tmp_tbl(-1) is not null
1027 and g_start_date_active_q_c_tbl(i) is null) then
1028 g_start_date_active_q_tmp_tbl(l_current_grp) := g_start_date_active_q_tmp_tbl(-1);
1029 else
1030 g_start_date_active_q_tmp_tbl(l_current_grp) := null;
1031 end if;
1032
1033 if (g_end_date_active_q_c_tbl(i) is not null
1034 and g_end_date_active_q_tmp_tbl(-1) is not null) then
1035 if g_end_date_active_q_c_tbl(i) < g_end_date_active_q_tmp_tbl(-1) then
1036 g_end_date_active_q_tmp_tbl(l_current_grp) := g_end_date_active_q_c_tbl(i);
1037 else
1038 g_end_date_active_q_tmp_tbl(l_current_grp) := g_end_date_active_q_tmp_tbl(-1);
1039 end if;
1040 elsif (g_end_date_active_q_tmp_tbl(-1) is null
1041 and g_end_date_active_q_c_tbl(i) is not null) then
1042 g_end_date_active_q_tmp_tbl(l_current_grp) := g_end_date_active_q_c_tbl(i);
1043 elsif (g_end_date_active_q_tmp_tbl(-1) is not null
1044 and g_end_date_active_q_c_tbl(i) is null) then
1045 g_end_date_active_q_tmp_tbl(l_current_grp) := g_end_date_active_q_tmp_tbl(-1);
1046 else
1047 g_end_date_active_q_tmp_tbl(l_current_grp) := null;
1048 end if;
1049
1050 if g_effective_precedence_tmp_tbl(-1) < g_effective_precedence_c_tbl(i) then
1051 g_effective_precedence_tmp_tbl(l_current_grp) := g_effective_precedence_tmp_tbl(-1);
1052 else
1053 g_effective_precedence_tmp_tbl(l_current_grp) := g_effective_precedence_c_tbl(i);
1054 end if;
1055 else
1056 g_start_date_active_q_tmp_tbl(l_current_grp) := g_start_date_active_q_c_tbl(i);
1057 g_end_date_active_q_tmp_tbl(l_current_grp) := g_end_date_active_q_c_tbl(i);
1058 g_effective_precedence_tmp_tbl(l_current_grp) := g_effective_precedence_c_tbl(i);
1059 end if; -- l_prefix_value_from_null_grp = 'Y'
1060 g_currency_code_tmp_tbl(l_current_grp) := g_currency_code_c_tbl(i);
1061 g_ask_for_flag_tmp_tbl(l_current_grp) := g_ask_for_flag_c_tbl(i);
1062 g_limit_exists_tmp_tbl(l_current_grp) := nvl(g_limit_exists_c_tbl(i), 'N');
1063 g_source_system_code_tmp_tbl(l_current_grp) := g_source_system_code_c_tbl(i);
1064 g_qual_grouping_no_tmp_tbl(l_current_grp) := g_qual_grouping_no_c_tbl(i);
1065 g_pricing_phase_id_tmp_tbl(l_current_grp) := g_pricing_phase_id_c_tbl(i);
1066 g_modifier_level_code_tmp_tbl(l_current_grp) := g_modifier_level_code_c_tbl(i);
1067 g_product_uom_code_tmp_tbl(l_current_grp) := null;
1068 g_pricing_attr_count_tmp_tbl(l_current_grp) := null;
1072 if g_pat_string_tmp_tbl(-1) is not null then
1069
1070 if g_comparison_opr_code_c_tbl(i) = '=' then
1071 if l_prefix_value_from_null_grp = 'Y' then
1073 g_pat_string_tmp_tbl(l_current_grp) := g_pat_string_tmp_tbl(-1) || g_delimiter || g_segment_id_c_tbl(i);
1074 else
1075 g_pat_string_tmp_tbl(l_current_grp) := g_segment_id_c_tbl(i);
1076 end if;
1077
1078 if g_hash_key_tmp_tbl(-1) is not null then
1079 g_hash_key_tmp_tbl(l_current_grp) := g_hash_key_tmp_tbl(-1) || g_delimiter || g_qual_attr_val_c_tbl(i);
1080 else
1081 g_hash_key_tmp_tbl(l_current_grp) := g_qual_attr_val_c_tbl(i);
1082 end if;
1083
1084 if g_attribute_type_c_tbl(i) = 'PROD' then
1085 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);
1086 elsif g_cache_key_tmp_tbl(-1) is not null then
1087 g_cache_key_tmp_tbl(l_current_grp) := g_cache_key_tmp_tbl(-1);
1088 else
1089 g_cache_key_tmp_tbl(l_current_grp) := null;
1090 end if;
1091 else
1092 g_pat_string_tmp_tbl(l_current_grp) := g_segment_id_c_tbl(i);
1093 g_hash_key_tmp_tbl(l_current_grp) := g_qual_attr_val_c_tbl(i);
1094
1095 if g_attribute_type_c_tbl(i) = 'PROD' then
1096 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);
1097 else
1098 g_cache_key_tmp_tbl(l_current_grp) := null;
1099 end if;
1100 end if; -- l_prefix_value_from_null_grp = 'Y'
1101
1102 -- populate the pattern temp table
1103 l_pat_tmp_index := g_pattern_grouping_no_tmp_tbl.count;
1104
1105 g_pattern_grouping_no_tmp_tbl(l_pat_tmp_index + 1) := g_qual_grouping_no_c_tbl(i);
1106 g_pattern_segment_id_tmp_tbl(l_pat_tmp_index + 1) := g_segment_id_c_tbl(i);
1107 else -- operator other than =
1108 if l_prefix_value_from_null_grp = 'Y' then
1109 if g_pat_string_tmp_tbl(-1) is not null then
1110 g_pat_string_tmp_tbl(l_current_grp) := g_pat_string_tmp_tbl(-1);
1111 else
1112 g_pat_string_tmp_tbl(l_current_grp) := null;
1113 end if;
1114
1115 if g_hash_key_tmp_tbl(-1) is not null then
1116 g_hash_key_tmp_tbl(l_current_grp) := g_hash_key_tmp_tbl(-1);
1117 else
1118 g_hash_key_tmp_tbl(l_current_grp) := null;
1119 end if;
1120
1121 if g_cache_key_tmp_tbl(-1) is not null then
1122 g_cache_key_tmp_tbl(l_current_grp) := g_cache_key_tmp_tbl(-1);
1123 else
1124 g_cache_key_tmp_tbl(l_current_grp) := null;
1125 end if;
1126 else
1127 g_pat_string_tmp_tbl(l_current_grp) := null;
1128 g_hash_key_tmp_tbl(l_current_grp) := null;
1129 g_cache_key_tmp_tbl(l_current_grp) := null;
1130 end if; -- l_prefix_value_from_null_grp = 'Y'
1131 end if; -- g_comparison_opr_code_c_tbl(i) = '='
1132
1133 end if; -- g_qual_grouping_no_tmp_tbl.exists(l_current_grp)
1134 -- store the header and line id
1135 l_old_list_header_id := g_list_header_id_c_tbl(i);
1136 l_old_list_line_id := g_list_line_id_c_tbl(i);
1137 end loop; -- i in 1..g_list_header_id_c_tbl.count
1138
1139 end if; -- g_list_header_id_c_tbl.count > 0
1140
1141 -- move data for last pair of header and line id
1142 Move_data_from_tmp_to_final(p_pattern_type);
1143
1144 -- insert into qp_attribute_groups from final tables
1145 populate_atgrps;
1146
1147 -- for line pattern, qp_list_lines.cache_key need to be populated as well
1148 if p_pattern_type = 'LP' then
1149 update_list_lines_cache_key;
1150 end if;
1151
1152 EXCEPTION
1153 WHEN OTHERS THEN
1154 if g_call_from_setup = 'Y' then
1155 oe_debug_pub.add('ATTR_GRP_PVT.Process_C_Tables ' || SQLERRM);
1156 else
1157 FND_FILE.PUT_LINE( FND_FILE.LOG, 'ATTR_GRP_PVT.Process_C_Tables ' || SQLERRM );
1158 end if;
1159
1160 end process_c_tables;
1161
1162 procedure process_c_tables_pp(p_pattern_type VARCHAR2)
1163 is
1164 l_old_list_header_id number;
1165 l_old_list_line_id number;
1166 l_pat_tmp_index number;
1167 l_first_pa_rec_for_line varchar2(1);
1168 l_debug VARCHAR2(3);
1169 l_line_counter number := 0;
1170
1171 begin
1172 l_old_list_header_id := g_init_val;
1173 l_old_list_line_id := g_init_val;
1174
1175 if g_call_from_setup = 'Y' then
1176 oe_debug_pub.add('PP cursor tables total = ' || g_list_header_id_c_tbl.count);
1177 else
1178 QP_PREQ_GRP.Set_QP_Debug;
1179 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
1180 IF l_debug = FND_API.G_TRUE THEN
1181 fnd_file.put_line(FND_FILE.LOG, 'PP cursor tables total = ' || g_list_header_id_c_tbl.count);
1182 END IF;
1183 end if;
1184
1185 l_first_pa_rec_for_line := 'Y';
1186
1187 if g_list_header_id_c_tbl.count > 0 then
1188 for i in 1..g_list_header_id_c_tbl.count
1189 loop
1190 if g_call_from_setup = 'Y' then
1191 oe_debug_pub.add('PP i = ' || i ||
1192 ', list_header_id = ' || g_list_header_id_c_tbl(i) ||
1196 --oe_debug_pub.add('PP l_old_list_line_id ' || l_old_list_line_id);
1193 ', list_line_id = ' || g_list_line_id_c_tbl(i) ||
1194 ', operator = ' || g_comparison_opr_code_c_tbl(i));
1195 --oe_debug_pub.add('PP l_old_list_header_id ' || l_old_list_header_id);
1197 elsif l_debug = FND_API.G_TRUE then
1198 fnd_file.put_line(FND_FILE.LOG, 'PP i = ' || i ||
1199 ', list_header_id = ' || g_list_header_id_c_tbl(i) ||
1200 ', list_line_id = ' || g_list_line_id_c_tbl(i) ||
1201 ', operator = ' || g_comparison_opr_code_c_tbl(i));
1202 --fnd_file.put_line(FND_FILE.LOG, 'PP l_old_list_header_id ' || l_old_list_header_id);
1203 --fnd_file.put_line(FND_FILE.LOG, 'PP l_old_list_line_id ' || l_old_list_line_id);
1204 end if;
1205
1206 if (i > 1
1207 and (g_list_header_id_c_tbl(i) <> l_old_list_header_id
1208 or g_list_line_id_c_tbl(i) <> l_old_list_line_id)
1209 ) then
1210 Move_data_from_tmp_to_final(p_pattern_type);
1211 Reset_tmp_tables;
1212 l_first_pa_rec_for_line := 'Y';
1213
1214 -- logic to insert into tables every G_LINES_PER_INSERT lines
1215 l_line_counter := l_line_counter + 1;
1216 if l_line_counter >= G_LINES_PER_INSERT then
1217 if g_call_from_setup = 'Y' then
1218 oe_debug_pub.add('inserting data for ' || G_LINES_PER_INSERT || ' lines');
1219 elsif l_debug = FND_API.G_TRUE then
1220 fnd_file.put_line(FND_FILE.LOG, 'inserting data for ' || G_LINES_PER_INSERT || ' lines');
1221 end if;
1222 update_list_lines;
1223 reset_final_tables;
1224 if g_call_from_setup <> 'Y' then
1225 if l_debug = FND_API.G_TRUE then
1226 fnd_file.put_line(FND_FILE.LOG, 'committing data for ' || G_LINES_PER_INSERT || ' lines');
1227 end if;
1228 commit;
1229 end if;
1230 l_line_counter := 0;
1231 end if;
1232 end if;
1233
1234 if l_first_pa_rec_for_line = 'Y' then
1235 g_list_header_id_tmp_tbl(-1) := g_list_header_id_c_tbl(i);
1236 g_list_line_id_tmp_tbl(-1) := g_list_line_id_c_tbl(i);
1237 g_product_uom_code_tmp_tbl(-1) := g_product_uom_code_c_tbl(i);
1238
1239 g_active_flag_tmp_tbl(-1) := null;
1240 g_list_type_code_tmp_tbl(-1) := null;
1241 g_currency_code_tmp_tbl(-1) := null;
1242 g_ask_for_flag_tmp_tbl(-1) := null;
1243 g_limit_exists_tmp_tbl(-1) := null;
1244 g_source_system_code_tmp_tbl(-1) := null;
1245 g_qual_grouping_no_tmp_tbl(-1) := -1;
1246 g_pricing_phase_id_tmp_tbl(-1) := g_pricing_phase_id_c_tbl(i);
1247 g_modifier_level_code_tmp_tbl(-1) := null;
1248 g_start_date_active_q_tmp_tbl(-1) := null;
1249 g_end_date_active_q_tmp_tbl(-1) := null;
1250 g_effective_precedence_tmp_tbl(-1) := null;
1251
1252 if g_attribute_type_c_tbl(i) = 'PRIC' then
1253 g_pricing_attr_count_tmp_tbl(-1) := 1;
1254 else
1255 g_pricing_attr_count_tmp_tbl(-1) := 0;
1256 end if;
1257
1258 if g_comparison_opr_code_c_tbl(i) = '=' then
1259 g_pat_string_tmp_tbl(-1) := g_segment_id_c_tbl(i);
1260 g_hash_key_tmp_tbl(-1) := g_qual_attr_val_c_tbl(i);
1261
1262 if g_attribute_type_c_tbl(i) = 'PROD' then
1263 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);
1264 else
1265 g_cache_key_tmp_tbl(-1) := null;
1266 end if;
1267
1268 -- populate the pattern temp table
1269 l_pat_tmp_index := g_pattern_grouping_no_tmp_tbl.count;
1270
1271 g_pattern_grouping_no_tmp_tbl(l_pat_tmp_index + 1) := -1;
1272 g_pattern_segment_id_tmp_tbl(l_pat_tmp_index + 1) := g_segment_id_c_tbl(i);
1273 else -- operator other than =
1274 g_pat_string_tmp_tbl(-1) := null;
1275 g_hash_key_tmp_tbl(-1) := null;
1276 g_cache_key_tmp_tbl(-1) := null;
1277 end if; -- g_comparison_opr_code_c_tbl(i) = '='
1278
1279 else -- not first pa record for a line
1280 if g_attribute_type_c_tbl(i) = 'PRIC' then
1281 g_pricing_attr_count_tmp_tbl(-1) := g_pricing_attr_count_tmp_tbl(-1) + 1;
1282 end if;
1283
1284 if g_comparison_opr_code_c_tbl(i) = '=' then
1285 if g_pat_string_tmp_tbl(-1) is not null then
1286 g_pat_string_tmp_tbl(-1) := g_pat_string_tmp_tbl(-1) || g_delimiter || g_segment_id_c_tbl(i);
1287 else
1288 g_pat_string_tmp_tbl(-1) := g_segment_id_c_tbl(i);
1289 end if;
1290
1291 if g_hash_key_tmp_tbl(-1) is not null then
1292 g_hash_key_tmp_tbl(-1) := g_hash_key_tmp_tbl(-1) || g_delimiter || g_qual_attr_val_c_tbl(i);
1293 else
1294 g_hash_key_tmp_tbl(-1) := g_qual_attr_val_c_tbl(i);
1295 end if;
1296
1297 if g_attribute_type_c_tbl(i) = 'PROD' then
1298 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);
1299 end if;
1300
1301 -- populate the pattern temp table
1302 l_pat_tmp_index := g_pattern_grouping_no_tmp_tbl.count;
1303
1307 end if; -- g_comparison_opr_code_c_tbl(i) = '='
1304 g_pattern_grouping_no_tmp_tbl(l_pat_tmp_index + 1) := -1;
1305 g_pattern_segment_id_tmp_tbl(l_pat_tmp_index + 1) := g_segment_id_c_tbl(i);
1306
1308 end if; -- l_first_pa_rec_for_line = 'Y'
1309
1310
1311 -- set first record indicator to 'N'
1312 l_first_pa_rec_for_line := 'N';
1313
1314 -- store the header and line id
1315 l_old_list_header_id := g_list_header_id_c_tbl(i);
1316 l_old_list_line_id := g_list_line_id_c_tbl(i);
1317 end loop; -- i in 1..g_list_header_id_c_tbl.count
1318
1319 end if; -- g_list_header_id_c_tbl.count > 0
1320
1321 -- move data for last pair of header and line id
1322 Move_data_from_tmp_to_final(p_pattern_type);
1323
1324 -- update qp_list_lines from final tables
1325 update_list_lines;
1326
1327 EXCEPTION
1328 WHEN OTHERS THEN
1329 if g_call_from_setup = 'Y' then
1330 oe_debug_pub.add('ATTR_GRP_PVT.Process_C_Tables_Pp ' || SQLERRM);
1331 else
1332 FND_FILE.PUT_LINE( FND_FILE.LOG, 'ATTR_GRP_PVT.Process_C_Tables_Pp ' || SQLERRM );
1333 end if;
1334
1335 end process_c_tables_pp;
1336
1337 PROCEDURE Move_data_from_tmp_to_final(p_pattern_type VARCHAR2)
1338 is
1339 l_other_grp_exists varchar2(1);
1340 l_pattern_id number;
1341 l_atgrp_final_index number;
1342 grp_no_index number;
1343 l_product_precedence number;
1344 BEGIN
1345 /*
1346 if g_call_from_setup = 'Y' then
1347 oe_debug_pub.add('Moving data from temp table to final table');
1348 oe_debug_pub.add('temp tables total = ' || g_list_header_id_tmp_tbl.count);
1349 else
1350 fnd_file.put_line(FND_FILE.LOG, 'Moving data from temp table to final table');
1351 fnd_file.put_line(FND_FILE.LOG, 'temp tables total = ' || g_list_header_id_tmp_tbl.count);
1352 end if;
1353 */
1354
1355 -- find out whether any qual groups exists other than -1
1356 l_other_grp_exists := 'N';
1357 if g_list_header_id_tmp_tbl.count > 1 and g_list_header_id_tmp_tbl.exists(-1) = TRUE then
1358 l_other_grp_exists := 'Y';
1359 end if; -- g_list_header_id_tmp_tbl.count > 1
1360
1361 /*
1362 if g_call_from_setup = 'Y' then
1363 oe_debug_pub.add('l_other_grp_exists = ' || l_other_grp_exists);
1364 else
1365 fnd_file.put_line(FND_FILE.LOG, 'l_other_grp_exists = ' || l_other_grp_exists);
1366 end if;
1367 */
1368
1369 -- now loop thru the atgrp temp tables and move to the final atgrp tables
1370 grp_no_index := g_qual_grouping_no_tmp_tbl.first;
1371 while grp_no_index is not null
1372 LOOP
1373 /*
1374 if g_call_from_setup = 'Y' then
1375 oe_debug_pub.add('grouping no = ' || grp_no_index);
1376 else
1377 fnd_file.put_line(FND_FILE.LOG, 'grouping no = ' || grp_no_index);
1378 end if;
1379 */
1380 -- if other groups exists then skip -1 record i.e. do not move temp table data to final table
1381 if l_other_grp_exists = 'Y' and grp_no_index = -1 then
1382 null;
1383 else
1384 if g_pat_string_tmp_tbl(grp_no_index) is not null then
1385 l_pattern_id := get_pattern_id(p_pattern_type, g_pat_string_tmp_tbl(grp_no_index),
1386 grp_no_index);
1387 else
1388 l_pattern_id := -1;
1389 end if;
1390 /*
1391 if g_call_from_setup = 'Y' then
1392 oe_debug_pub.add('Pattern_id='||l_pattern_id);
1393 else
1394 fnd_file.put_line(FND_FILE.LOG, 'Pattern_id='||l_pattern_id);
1395 end if;
1396 */
1397
1398 -- maintain data in qp_pattern_phases
1399 if p_pattern_type in ('LP', 'PP') then
1400 Populate_Pattern_Phases(null, g_pricing_phase_id_tmp_tbl(grp_no_index), l_pattern_id);
1401 elsif p_pattern_type = 'HP' then
1402 Populate_Pattern_Phases(g_list_header_id_tmp_tbl(grp_no_index), null, l_pattern_id);
1403 end if;
1404
1405 -- move the data from tmp tables to final tables for qp_attribute_groups
1406 /*
1407 if g_call_from_setup = 'Y' then
1408 oe_debug_pub.add('move the data from tmp tables to final tables for qp_attribute_groups');
1409 else
1410 fnd_file.put_line(FND_FILE.LOG, 'move the data from tmp tables to final tables for qp_attribute_groups');
1411 end if;
1412 */
1413
1414 l_atgrp_final_index := g_list_header_id_final_tbl.count + 1;
1415
1416 g_list_header_id_final_tbl(l_atgrp_final_index) := g_list_header_id_tmp_tbl(grp_no_index);
1417 g_list_line_id_final_tbl(l_atgrp_final_index) := g_list_line_id_tmp_tbl(grp_no_index);
1418 g_active_flag_final_tbl(l_atgrp_final_index) := g_active_flag_tmp_tbl(grp_no_index);
1419 g_list_type_code_final_tbl(l_atgrp_final_index) := g_list_type_code_tmp_tbl(grp_no_index);
1420 g_st_date_active_q_final_tbl(l_atgrp_final_index) := g_start_date_active_q_tmp_tbl(grp_no_index);
1421 g_end_date_active_q_final_tbl(l_atgrp_final_index) := g_end_date_active_q_tmp_tbl(grp_no_index);
1422 g_pattern_id_final_tbl(l_atgrp_final_index) := l_pattern_id;
1423 g_currency_code_final_tbl(l_atgrp_final_index) := g_currency_code_tmp_tbl(grp_no_index);
1424 g_ask_for_flag_final_tbl(l_atgrp_final_index) := g_ask_for_flag_tmp_tbl(grp_no_index);
1425 g_limit_exists_final_tbl(l_atgrp_final_index) := g_limit_exists_tmp_tbl(grp_no_index);
1429 g_pricing_phase_id_final_tbl(l_atgrp_final_index) := g_pricing_phase_id_tmp_tbl(grp_no_index);
1426 g_source_system_code_final_tbl(l_atgrp_final_index) := g_source_system_code_tmp_tbl(grp_no_index);
1427 g_effec_precedence_final_tbl(l_atgrp_final_index) := g_effective_precedence_tmp_tbl(grp_no_index);
1428 g_qual_grouping_no_final_tbl(l_atgrp_final_index) := g_qual_grouping_no_tmp_tbl(grp_no_index);
1430 g_modifier_lvl_code_final_tbl(l_atgrp_final_index) := g_modifier_level_code_tmp_tbl(grp_no_index);
1431 g_hash_key_final_tbl(l_atgrp_final_index) := g_hash_key_tmp_tbl(grp_no_index);
1432 -- if there is no product attached to a line then cache_key should contain only list_header_id
1433 if g_cache_key_tmp_tbl(grp_no_index) is null and p_pattern_type in ('LP', 'PP') then
1434 g_cache_key_final_tbl(l_atgrp_final_index) := g_list_header_id_tmp_tbl(grp_no_index);
1435
1436 -- bug 3703391 - if product not given, even then consider product precedence to decide effective precedence
1437 select product_precedence into l_product_precedence
1438 from qp_list_lines where list_line_id = g_list_line_id_final_tbl(l_atgrp_final_index);
1439
1440 if (l_product_precedence is not null) and (g_effec_precedence_final_tbl(l_atgrp_final_index) is not null)
1441 and (l_product_precedence < g_effec_precedence_final_tbl(l_atgrp_final_index)) then
1442 g_effec_precedence_final_tbl(l_atgrp_final_index) := l_product_precedence;
1443 end if;
1444 /*
1445 if g_call_from_setup = 'Y' then
1446 oe_debug_pub.add('cache_key should be just list_header_id ');
1447 else
1448 fnd_file.put_line(FND_FILE.LOG, 'cache_key should be just list_header_id ');
1449 end if;
1450 */
1451 else
1452 g_cache_key_final_tbl(l_atgrp_final_index) := g_cache_key_tmp_tbl(grp_no_index);
1453 /*
1454 if g_call_from_setup = 'Y' then
1455 oe_debug_pub.add('cache_key should be standard OR null');
1456 else
1457 fnd_file.put_line(FND_FILE.LOG, 'cache_key should be standard OR null');
1458 end if;
1459 */
1460 end if;
1461
1462 g_product_uom_code_final_tbl(l_atgrp_final_index) := g_product_uom_code_tmp_tbl(grp_no_index);
1463 g_pricing_attr_count_final_tbl(l_atgrp_final_index) := g_pricing_attr_count_tmp_tbl(grp_no_index);
1464
1465 -- populate the standard who columns
1466 g_creation_date_final_tbl(l_atgrp_final_index) := sysdate;
1467 g_created_by_final_tbl(l_atgrp_final_index) := FND_GLOBAL.USER_ID;
1468 g_last_update_date_final_tbl(l_atgrp_final_index) := sysdate;
1469 g_last_updated_by_final_tbl(l_atgrp_final_index) := FND_GLOBAL.USER_ID;
1470 g_last_update_login_final_tbl(l_atgrp_final_index) := FND_GLOBAL.LOGIN_ID;
1471 g_program_appl_id_final_tbl(l_atgrp_final_index) := FND_GLOBAL.PROG_APPL_ID;
1472 g_program_id_final_tbl(l_atgrp_final_index) := FND_GLOBAL.CONC_PROGRAM_ID;
1473 g_program_upd_date_final_tbl(l_atgrp_final_index) := sysdate;
1474 g_request_id_final_tbl(l_atgrp_final_index) := FND_GLOBAL.CONC_REQUEST_ID;
1475
1476 end if; -- l_other_grp_exists = 'Y' and grp_no_index = -1
1477
1478 grp_no_index := g_qual_grouping_no_tmp_tbl.next(grp_no_index);
1479
1480 END LOOP; -- while
1481 /*
1482 if g_call_from_setup = 'Y' then
1483 oe_debug_pub.add('End Moving data from temp table to final table');
1484 else
1485 fnd_file.put_line(FND_FILE.LOG, 'End Moving data from temp table to final table');
1486 end if;
1487 */
1488
1489 EXCEPTION
1490 WHEN OTHERS THEN
1491 if g_call_from_setup = 'Y' then
1492 oe_debug_pub.add('ATTR_GRP_PVT.Move_Data_From_Tmp_To_Final ' || SQLERRM);
1493 else
1494 FND_FILE.PUT_LINE( FND_FILE.LOG, 'ATTR_GRP_PVT.Move_Data_From_Tmp_To_Final ' || SQLERRM );
1495 end if;
1496
1497 END Move_data_from_tmp_to_final;
1498
1499 -- bulk insert patterns into qp_patterns table
1500 PROCEDURE Populate_Patterns
1501 is
1502 BEGIN
1503
1504 /*
1505 if g_call_from_setup = 'Y' then
1506 oe_debug_pub.add('Begin Populate_patterns');
1507 else
1508 fnd_file.put_line(FND_FILE.LOG, 'Begin Populate_patterns');
1509 end if;
1510 */
1511
1512 FORALL i in 1 .. G_pattern_pattern_id_final_tbl.count
1513 INSERT INTO qp_patterns
1514 (
1515 pattern_id,
1516 segment_id,
1517 pattern_type,
1518 pattern_string,
1519 creation_date,
1520 created_by,
1521 last_update_date,
1522 last_updated_by,
1523 last_update_login,
1524 program_application_id,
1525 program_id,
1526 program_update_date,
1527 request_id
1528 )
1529 VALUES
1530 (
1531 g_pattern_pattern_id_final_tbl(i),
1532 g_pattern_segment_id_final_tbl(i),
1533 g_pattern_pat_type_final_tbl(i),
1534 g_pattern_pat_string_final_tbl(i),
1535 g_pattern_cr_dt_final_tbl(i),
1536 g_pattern_cr_by_final_tbl(i),
1537 g_pattern_lst_up_dt_final_tbl(i),
1538 g_pattern_lt_up_by_final_tbl(i),
1539 g_pattern_lt_up_lg_final_tbl(i),
1540 g_pattern_pr_ap_id_final_tbl(i),
1541 g_pattern_pr_id_final_tbl(i),
1542 g_pattern_pr_up_dt_final_tbl(i),
1543 g_pattern_req_id_final_tbl(i)
1544 );
1545
1546 g_pattern_pattern_id_final_tbl.delete;
1547 g_pattern_segment_id_final_tbl.delete;
1548 g_pattern_pat_type_final_tbl.delete;
1549 g_pattern_pat_string_final_tbl.delete;
1550 g_pattern_cr_dt_final_tbl.delete;
1551 g_pattern_cr_by_final_tbl.delete;
1555 g_pattern_pr_ap_id_final_tbl.delete;
1552 g_pattern_lst_up_dt_final_tbl.delete;
1553 g_pattern_lt_up_by_final_tbl.delete;
1554 g_pattern_lt_up_lg_final_tbl.delete;
1556 g_pattern_pr_id_final_tbl.delete;
1557 g_pattern_pr_up_dt_final_tbl.delete;
1558 g_pattern_req_id_final_tbl.delete;
1559
1560 /*
1561 if g_call_from_setup = 'Y' then
1562 oe_debug_pub.add('End Populate_patterns');
1563 else
1564 commit;
1565 fnd_file.put_line(FND_FILE.LOG, 'End Populate_patterns');
1566 end if;
1567 */
1568 EXCEPTION
1569 WHEN OTHERS THEN
1570 if g_call_from_setup = 'Y' then
1571 oe_debug_pub.add('ATTR_GRP_PVT.Populate_Patterns ' || SQLERRM);
1572 else
1573 FND_FILE.PUT_LINE( FND_FILE.LOG, 'ATTR_GRP_PVT.Populate_Patterns ' || SQLERRM );
1574 end if;
1575 raise;
1576 END Populate_Patterns;
1577
1578 -- bulk update qp_list_lines table
1579 PROCEDURE update_list_lines
1580 is
1581 BEGIN
1582
1583 FORALL i in 1 .. g_list_line_id_final_tbl.count
1584 UPDATE /*+ index(lines QP_LIST_LINES_PK) */ qp_list_lines lines
1585 set pattern_id = g_pattern_id_final_tbl(i),
1586 product_uom_code = g_product_uom_code_final_tbl(i),
1587 pricing_attribute_count = g_pricing_attr_count_final_tbl(i),
1588 hash_key = g_hash_key_final_tbl(i),
1589 cache_key = g_cache_key_final_tbl(i),
1590 last_update_date = g_last_update_date_final_tbl(i),
1591 last_updated_by = g_last_updated_by_final_tbl(i),
1592 last_update_login = g_last_update_login_final_tbl(i)
1593 where list_line_id = g_list_line_id_final_tbl(i);
1594
1595 EXCEPTION
1596 WHEN OTHERS THEN
1597 if g_call_from_setup = 'Y' then
1598 oe_debug_pub.add('ATTR_GRP_PVT.Update_List_Lines ' || SQLERRM);
1599 else
1600 FND_FILE.PUT_LINE( FND_FILE.LOG, 'ATTR_GRP_PVT.Update_List_Lines ' || SQLERRM );
1601 end if;
1602
1603 end update_list_lines;
1604
1605 -- bulk update qp_list_lines.cache_key for line patterns
1606 PROCEDURE update_list_lines_cache_key
1607 is
1608 BEGIN
1609
1610 FORALL i in 1 .. g_list_line_id_final_tbl.count
1611 UPDATE qp_list_lines
1612 set cache_key = g_cache_key_final_tbl(i),
1613 last_update_date = g_last_update_date_final_tbl(i),
1614 last_updated_by = g_last_updated_by_final_tbl(i),
1615 last_update_login = g_last_update_login_final_tbl(i)
1616 where list_line_id = g_list_line_id_final_tbl(i);
1617
1618 EXCEPTION
1619 WHEN OTHERS THEN
1620 if g_call_from_setup = 'Y' then
1621 oe_debug_pub.add('ATTR_GRP_PVT.Update_List_Lines_Cache_Key ' || SQLERRM);
1622 else
1623 FND_FILE.PUT_LINE( FND_FILE.LOG, 'ATTR_GRP_PVT.Update_List_Lines_Cache_Key ' || SQLERRM );
1624 end if;
1625
1626 end update_list_lines_cache_key;
1627
1628 -- bulk insert patterns into qp_patterns table
1629 PROCEDURE Populate_Atgrps
1630 is
1631 BEGIN
1632
1633 FORALL i in 1 .. g_list_header_id_final_tbl.count
1634 INSERT INTO qp_attribute_groups
1635 (list_header_id,
1636 list_line_id,
1637 active_flag,
1638 list_type_code,
1639 start_date_active_q,
1640 end_date_active_q,
1641 pattern_id,
1642 currency_code,
1643 ask_for_flag,
1644 limit_exists,
1645 source_system_code,
1646 effective_precedence,
1647 grouping_no,
1648 pricing_phase_id,
1649 modifier_level_code,
1650 hash_key,
1651 cache_key,
1652 creation_date,
1653 created_by,
1654 last_update_date,
1655 last_updated_by,
1656 last_update_login,
1657 program_application_id,
1658 program_id,
1659 program_update_date,
1660 request_id
1661 )
1662 VALUES
1663 (g_list_header_id_final_tbl(i),
1664 g_list_line_id_final_tbl(i),
1665 g_active_flag_final_tbl(i),
1666 g_list_type_code_final_tbl(i),
1667 g_st_date_active_q_final_tbl(i),
1668 g_end_date_active_q_final_tbl(i),
1669 g_pattern_id_final_tbl(i),
1670 g_currency_code_final_tbl(i),
1671 g_ask_for_flag_final_tbl(i),
1672 g_limit_exists_final_tbl(i),
1673 g_source_system_code_final_tbl(i),
1674 g_effec_precedence_final_tbl(i),
1675 g_qual_grouping_no_final_tbl(i),
1676 g_pricing_phase_id_final_tbl(i),
1677 g_modifier_lvl_code_final_tbl(i),
1678 g_hash_key_final_tbl(i),
1679 g_cache_key_final_tbl(i),
1680 g_creation_date_final_tbl(i),
1681 g_created_by_final_tbl(i),
1682 g_last_update_date_final_tbl(i),
1683 g_last_updated_by_final_tbl(i),
1684 g_last_update_login_final_tbl(i),
1685 g_program_appl_id_final_tbl(i),
1686 g_program_id_final_tbl(i),
1687 g_program_upd_date_final_tbl(i),
1688 g_request_id_final_tbl(i)
1689 );
1690 EXCEPTION
1691 WHEN OTHERS THEN
1692 if g_call_from_setup = 'Y' then
1693 oe_debug_pub.add('ATTR_GRP_PVT.Populate_Atgrps ' || SQLERRM);
1694 else
1695 FND_FILE.PUT_LINE( FND_FILE.LOG, 'ATTR_GRP_PVT.Populate_Atgrps ' || SQLERRM );
1696 end if;
1697 raise;
1698 END Populate_Atgrps;
1699
1700 Procedure Reset_tmp_tables
1701 is
1702 begin
1703 /*
1704 if g_call_from_setup = 'Y' then
1705 oe_debug_pub.add('Reset temp tables');
1706 else
1707 fnd_file.put_line(FND_FILE.LOG, 'Reset temp tables');
1708 end if;
1709 */
1713 g_list_type_code_tmp_tbl.delete;
1710 g_list_header_id_tmp_tbl.delete;
1711 g_list_line_id_tmp_tbl.delete;
1712 g_active_flag_tmp_tbl.delete;
1714 g_start_date_active_q_tmp_tbl.delete;
1715 g_end_date_active_q_tmp_tbl.delete;
1716 g_currency_code_tmp_tbl.delete;
1717 g_ask_for_flag_tmp_tbl.delete;
1718 g_limit_exists_tmp_tbl.delete;
1719 g_source_system_code_tmp_tbl.delete;
1720 g_effective_precedence_tmp_tbl.delete;
1721 g_qual_grouping_no_tmp_tbl.delete;
1722 g_pricing_phase_id_tmp_tbl.delete;
1723 g_modifier_level_code_tmp_tbl.delete;
1724 g_hash_key_tmp_tbl.delete;
1725 g_cache_key_tmp_tbl.delete;
1726 g_pat_string_tmp_tbl.delete;
1727
1728 g_pattern_grouping_no_tmp_tbl.delete;
1729 g_pattern_segment_id_tmp_tbl.delete;
1730
1731 g_product_uom_code_tmp_tbl.delete;
1732 g_pricing_attr_count_tmp_tbl.delete;
1733 end Reset_tmp_tables;
1734
1735 function get_pattern_id(p_pattern_type varchar2, p_pat_string varchar2,
1736 p_grp_no number)
1737 return number
1738 is
1739 l_pattern_to_be_created varchar2(1);
1740 l_pattern_id number;
1741 l_pattern_final_index number;
1742 begin
1743 /*
1744 if g_call_from_setup = 'Y' then
1745 oe_debug_pub.add('Begin get_pattern_id');
1746 else
1747 fnd_file.put_line(FND_FILE.LOG, 'Begin get_pattern_id');
1748 end if;
1749 */
1750 begin
1751 l_pattern_to_be_created := 'N';
1752 select /*+ index(qp_pat QP_PATTERNS_N1) */ pattern_id
1753 into l_pattern_id
1754 from qp_patterns qp_pat
1755 where pattern_string = p_pat_string
1756 and pattern_type = p_pattern_type
1757 and rownum = 1;
1758 exception
1759 when no_data_found then
1760 select qp_patterns_s.nextval into l_pattern_id from dual;
1761 l_pattern_to_be_created := 'Y';
1762
1763 when others then
1764 raise;
1765 end;
1766
1767 /*
1768 if g_call_from_setup = 'Y' then
1769 oe_debug_pub.add('l_pattern_to_be_created='||l_pattern_to_be_created);
1770 else
1771 fnd_file.put_line(FND_FILE.LOG, 'l_pattern_to_be_created='||l_pattern_to_be_created);
1772 end if;
1773 */
1774
1775 -- move the data from temp tables to final tables for qp_patterns, if new pattern to be created
1776 if l_pattern_to_be_created = 'Y' then
1777 for k in 1..g_pattern_grouping_no_tmp_tbl.count
1778 loop
1779 if (g_pattern_grouping_no_tmp_tbl(k) = -1 or g_pattern_grouping_no_tmp_tbl(k) = p_grp_no) then
1780 l_pattern_final_index := g_pattern_pattern_id_final_tbl.count + 1;
1781
1782 g_pattern_pattern_id_final_tbl(l_pattern_final_index) := l_pattern_id;
1783 g_pattern_segment_id_final_tbl(l_pattern_final_index) := g_pattern_segment_id_tmp_tbl(k);
1784 g_pattern_pat_type_final_tbl(l_pattern_final_index) := p_pattern_type;
1785 g_pattern_pat_string_final_tbl(l_pattern_final_index) := p_pat_string;
1786
1787 g_pattern_cr_dt_final_tbl(l_pattern_final_index) := sysdate;
1788 g_pattern_cr_by_final_tbl(l_pattern_final_index) := FND_GLOBAL.USER_ID;
1789 g_pattern_lst_up_dt_final_tbl(l_pattern_final_index) := sysdate;
1790 g_pattern_lt_up_by_final_tbl(l_pattern_final_index) := FND_GLOBAL.USER_ID;
1791 g_pattern_lt_up_lg_final_tbl(l_pattern_final_index) := FND_GLOBAL.LOGIN_ID;
1792 g_pattern_pr_ap_id_final_tbl(l_pattern_final_index) := FND_GLOBAL.PROG_APPL_ID;
1793 g_pattern_pr_id_final_tbl(l_pattern_final_index) := FND_GLOBAL.CONC_PROGRAM_ID;
1794 g_pattern_pr_up_dt_final_tbl(l_pattern_final_index) := sysdate;
1795 g_pattern_req_id_final_tbl(l_pattern_final_index) := FND_GLOBAL.CONC_REQUEST_ID;
1796 end if;
1797 end loop; --k in 1..g_pattern_grouping_no_tmp_tbl.count
1798 populate_patterns;
1799 end if; -- l_pattern_to_be_created = 'Y'
1800 /*
1801 if g_call_from_setup = 'Y' then
1802 oe_debug_pub.add('End get_pattern_id');
1803 else
1804 fnd_file.put_line(FND_FILE.LOG, 'End get_pattern_id');
1805 end if;
1806 */
1807
1808 return l_pattern_id;
1809
1810 EXCEPTION
1811 WHEN OTHERS THEN
1812 if g_call_from_setup = 'Y' then
1813 oe_debug_pub.add('ATTR_GRP_PVT.Get_Pattern_Id ' || SQLERRM);
1814 else
1815 FND_FILE.PUT_LINE( FND_FILE.LOG, 'ATTR_GRP_PVT.Get_Pattern_Id ' || SQLERRM );
1816 end if;
1817
1818 end get_pattern_id;
1819
1820 procedure reset_final_tables
1821 is
1822 begin
1823 g_list_header_id_final_tbl.delete;
1824 g_list_line_id_final_tbl.delete;
1825 g_active_flag_final_tbl.delete;
1826 g_list_type_code_final_tbl.delete;
1827 g_st_date_active_q_final_tbl.delete;
1828 g_end_date_active_q_final_tbl.delete;
1829 g_pattern_id_final_tbl.delete;
1830 g_currency_code_final_tbl.delete;
1831 g_ask_for_flag_final_tbl.delete;
1832 g_limit_exists_final_tbl.delete;
1833 g_source_system_code_final_tbl.delete;
1834 g_effec_precedence_final_tbl.delete;
1835 g_qual_grouping_no_final_tbl.delete;
1836 g_pricing_phase_id_final_tbl.delete;
1837 g_modifier_lvl_code_final_tbl.delete;
1838 g_hash_key_final_tbl.delete;
1839 g_cache_key_final_tbl.delete;
1840
1841 g_product_uom_code_final_tbl.delete;
1842 g_pricing_attr_count_final_tbl.delete;
1843
1844 g_creation_date_final_tbl.delete;
1845 g_created_by_final_tbl.delete;
1846 g_last_update_date_final_tbl.delete;
1847 g_last_updated_by_final_tbl.delete;
1851 g_program_upd_date_final_tbl.delete;
1848 g_last_update_login_final_tbl.delete;
1849 g_program_appl_id_final_tbl.delete;
1850 g_program_id_final_tbl.delete;
1852 g_request_id_final_tbl.delete;
1853
1854 EXCEPTION
1855 WHEN OTHERS THEN
1856 if g_call_from_setup = 'Y' then
1857 oe_debug_pub.add('ATTR_GRP_PVT.Reset_Final_Tables ' || SQLERRM);
1858 else
1859 FND_FILE.PUT_LINE( FND_FILE.LOG, 'ATTR_GRP_PVT.Reset_Final_Tables ' || SQLERRM );
1860 end if;
1861
1862 end reset_final_tables;
1863
1864 procedure reset_c_tables
1865 is
1866 begin
1867 g_list_header_id_c_tbl.delete;
1868 g_list_line_id_c_tbl.delete;
1869 g_segment_id_c_tbl.delete;
1870 g_active_flag_c_tbl.delete;
1871 g_list_type_code_c_tbl.delete;
1872 g_start_date_active_q_c_tbl.delete;
1873 g_end_date_active_q_c_tbl.delete;
1874 g_currency_code_c_tbl.delete;
1875 g_ask_for_flag_c_tbl.delete;
1876 g_limit_exists_c_tbl.delete;
1877 g_source_system_code_c_tbl.delete;
1878 g_effective_precedence_c_tbl.delete;
1879 g_qual_grouping_no_c_tbl.delete;
1880 g_comparison_opr_code_c_tbl.delete;
1881 g_pricing_phase_id_c_tbl.delete;
1882 g_modifier_level_code_c_tbl.delete;
1883 g_qual_datatype_c_tbl.delete;
1884 g_qual_attr_val_c_tbl.delete;
1885 g_attribute_type_c_tbl.delete;
1886
1887 g_product_uom_code_c_tbl.delete;
1888
1889 EXCEPTION
1890 WHEN OTHERS THEN
1891 if g_call_from_setup = 'Y' then
1892 oe_debug_pub.add('ATTR_GRP_PVT.Reset_C_Tables ' || SQLERRM);
1893 else
1894 FND_FILE.PUT_LINE( FND_FILE.LOG, 'ATTR_GRP_PVT.Reset_C_Tables ' || SQLERRM );
1895 end if;
1896 end reset_c_tables;
1897
1898 PROCEDURE Populate_Pattern_Phases (
1899 p_list_header_id IN NUMBER,
1900 p_pricing_phase_id IN NUMBER,
1901 p_pattern_id IN NUMBER) IS
1902
1903 CURSOR l_phase_id_to_insert_csr IS
1904 SELECT distinct pricing_phase_id, list_header_id
1905 FROM qp_list_header_phases
1906 WHERE list_header_id = p_list_header_id;
1907
1908 l_exists varchar2(1);
1909
1910 BEGIN
1911
1912 IF p_list_header_id is not null then
1913 -- HP case
1914 /*
1915 if g_call_from_setup = 'Y' then
1916 oe_debug_pub.add('Begin Populate_Pattern_Phases for HP case');
1917 else
1918 fnd_file.put_line(FND_FILE.LOG, 'Begin Populate_Pattern_Phases for HP case');
1919 end if;
1920 */
1921 FOR j IN l_phase_id_to_insert_csr LOOP
1922 begin
1923 select /*+ index(qp_pp QP_PATTERN_PHASES_N1) */ 'Y'
1924 into l_exists
1925 from qp_pattern_phases qp_pp
1926 where pattern_id = p_pattern_id
1927 and pricing_phase_id = j.pricing_phase_id;
1928 exception
1929 when no_data_found then
1930 INSERT INTO qp_pattern_phases
1931 (pattern_id,
1932 pricing_phase_id,
1933 creation_date,
1934 created_by,
1935 last_update_date,
1936 last_updated_by,
1937 last_update_login,
1938 program_application_id,
1939 program_id,
1940 program_update_date,
1941 request_id
1942 )
1943 VALUES
1944 (p_pattern_id,
1945 j.pricing_phase_id,
1946 sysdate,
1947 FND_GLOBAL.USER_ID,
1948 sysdate,
1949 FND_GLOBAL.USER_ID,
1950 FND_GLOBAL.LOGIN_ID,
1951 FND_GLOBAL.PROG_APPL_ID,
1952 FND_GLOBAL.CONC_PROGRAM_ID,
1953 sysdate,
1954 FND_GLOBAL.CONC_REQUEST_ID
1955 );
1956
1957 if g_call_from_setup <> 'Y' then
1958 commit;
1959 end if;
1960
1961 when others then
1962 raise;
1963 end;
1964 END LOOP; --j IN l_phase_id_to_insert_csr
1965 else
1966 -- LP, PP case
1967 /*
1968 if g_call_from_setup = 'Y' then
1969 oe_debug_pub.add('Begin Populate_Pattern_Phases for LP, PP case');
1970 else
1971 fnd_file.put_line(FND_FILE.LOG, 'Begin Populate_Pattern_Phases for LP, PP case');
1972 end if;
1973 */
1974 begin
1975 select /*+ index(qp_pp QP_PATTERN_PHASES_N1) */ 'Y'
1976 into l_exists
1977 from qp_pattern_phases qp_pp
1978 where pattern_id = p_pattern_id
1979 and pricing_phase_id = p_pricing_phase_id
1980 and rownum = 1; -- needed in case same combination is inserted by 2 diff. threads and one has commited before other
1981 exception
1982 when no_data_found then
1983 /*
1984 if g_call_from_setup = 'Y' then
1985 oe_debug_pub.add('No pattern_phases found; go insert');
1986 else
1987 fnd_file.put_line(FND_FILE.LOG, 'No pattern_phases found; go insert');
1988 end if;
1989 */
1990 INSERT INTO qp_pattern_phases
1991 (pattern_id,
1992 pricing_phase_id,
1993 creation_date,
1994 created_by,
1995 last_update_date,
1996 last_updated_by,
1997 last_update_login,
1998 program_application_id,
1999 program_id,
2000 program_update_date,
2001 request_id
2005 p_pricing_phase_id,
2002 )
2003 VALUES
2004 (p_pattern_id,
2006 sysdate,
2007 FND_GLOBAL.USER_ID,
2008 sysdate,
2009 FND_GLOBAL.USER_ID,
2010 FND_GLOBAL.LOGIN_ID,
2011 FND_GLOBAL.PROG_APPL_ID,
2012 FND_GLOBAL.CONC_PROGRAM_ID,
2013 sysdate,
2014 FND_GLOBAL.CONC_REQUEST_ID
2015 );
2016
2017 if g_call_from_setup <> 'Y' then
2018 commit;
2019 end if;
2020
2021 when others then
2022 if g_call_from_setup = 'Y' then
2023 oe_debug_pub.add('Insert failure:'||sqlerrm);
2024 else
2025 fnd_file.put_line(FND_FILE.LOG, 'Insert failure:'||sqlerrm);
2026 end if;
2027 raise;
2028 end;
2029
2030 END IF; -- p_list_header_id is not null
2031 /*
2032 if g_call_from_setup = 'Y' then
2033 oe_debug_pub.add('End Populate_Pattern_Phases ');
2034 else
2035 fnd_file.put_line(FND_FILE.LOG, 'End Populate_Pattern_Phases ');
2036 end if;
2037 */
2038
2039 EXCEPTION
2040 WHEN OTHERS THEN
2041 if g_call_from_setup = 'Y' then
2042 oe_debug_pub.add('ATTR_GRP_PVT.Populate_Pattern_Phases ' || SQLERRM);
2043 else
2044 FND_FILE.PUT_LINE( FND_FILE.LOG, 'ATTR_GRP_PVT.Populate_Pattern_Phases ' || SQLERRM );
2045 end if;
2046 raise;
2047 END Populate_Pattern_Phases;
2048
2049 PROCEDURE Header_Pattern_Main(
2050 p_list_header_id IN NUMBER
2051 ,p_qualifier_group IN NUMBER
2052 ,p_setup_action IN VARCHAR2 ) IS
2053
2054 -- p_setup_action I (for insert), U (for update), D (for delete) or
2055 -- UD (for update in denormalized columns from header like active_flag, currency etc.)
2056 l_status_code VARCHAR2(30) := NULL;
2057 l_status_text VARCHAR2(2000) := NULL;
2058 l_pattern_id NUMBER;
2059 l_qual_exists VARCHAR2(1) := 'N';
2060 l_ACTIVE_FLAG qp_list_headers_b.active_flag%type;
2061 l_LIST_TYPE_CODE qp_list_headers_b.list_type_code%type;
2062 l_CURRENCY_CODE qp_list_headers_b.CURRENCY_CODE%type;
2063 l_ASK_FOR_FLAG qp_list_headers_b.ASK_FOR_FLAG%type;
2064 l_HEADER_LIMIT_EXISTS qp_list_headers_b.LIMIT_EXISTS_FLAG%type;
2065 l_SOURCE_SYSTEM_CODE qp_list_headers_b.SOURCE_SYSTEM_CODE%type;
2066
2067 BEGIN
2068 g_call_from_setup := 'Y';
2069
2070 if g_call_from_setup = 'Y' then
2071 oe_debug_pub.add('Header_Pattern_Main - p_list_header_id = ' ||p_list_header_id);
2072 oe_debug_pub.add('Header_Pattern_Main - p_qualifier_group = ' ||p_qualifier_group);
2073 oe_debug_pub.add('Header_Pattern_Main - p_setup_action = ' ||p_setup_action);
2074 else
2075 fnd_file.put_line(FND_FILE.LOG, 'Header_Pattern_Main - p_list_header_id = ' ||p_list_header_id);
2076 fnd_file.put_line(FND_FILE.LOG, 'Header_Pattern_Main - p_qualifier_group = ' ||p_qualifier_group);
2077 fnd_file.put_line(FND_FILE.LOG, 'Header_Pattern_Main - p_setup_action = ' ||p_setup_action);
2078 end if;
2079
2080 -- when called while set up of modifier/price list
2081 if p_setup_action <> 'UD' then
2082 -- No need to delete pattern in case of update in denormalized columns in header
2083 -- delete from qp_attribute_groups first
2084 if p_qualifier_group is null then
2085 delete from qp_attribute_groups
2086 where list_header_id = p_list_header_id
2087 and list_line_id = -1;
2088 elsif p_qualifier_group is not null then
2089 delete from qp_attribute_groups
2090 where list_header_id = p_list_header_id
2091 and list_line_id = -1
2092 and GROUPING_NO in (-1, p_qualifier_group);
2093 end if;
2094
2095 -- update the segment_id columns for qualifiers
2096 Update_Qual_Segment_id(p_list_header_id, p_qualifier_group, -1, -1);
2097 end if;
2098
2099 -- populate the records in qp_attribute_groups afresh for p_list_header_id, list_line_id -1
2100 -- and p_qualifier_group
2101 if p_setup_action = 'I' then
2102 -- insert case
2103 generate_hp_atgrps(p_list_header_id, p_qualifier_group);
2104 elsif p_setup_action = 'U' or p_setup_action = 'D' then
2105 -- update or delete case
2106 if p_qualifier_group is null then
2107 generate_hp_atgrps(p_list_header_id, p_qualifier_group);
2108 elsif p_qualifier_group is not null then
2109 begin
2110 select 'Y'
2111 into l_qual_exists
2112 from qp_qualifiers
2113 where list_header_id = p_list_header_id
2114 and list_line_id = -1
2115 and ((list_type_code = 'PRL'
2116 AND QUALIFIER_CONTEXT <> 'MODLIST'
2117 AND QUALIFIER_ATTRIBUTE <> 'QUALIFIER_ATTRIBUTE4')
2118 OR
2119 (list_type_code <> 'PRL')
2120 )
2121 and QUALIFIER_GROUPING_NO = p_qualifier_group
2122 and rownum = 1;
2123 exception
2124 when no_data_found then
2125 l_qual_exists := 'N';
2126 end;
2127
2128 if l_qual_exists = 'Y' then
2129 -- means some qualifiers still exist for p_qualifier_group
2133 select 'Y'
2130 generate_hp_atgrps(p_list_header_id, p_qualifier_group);
2131 else
2132 begin
2134 into l_qual_exists
2135 from qp_qualifiers
2136 where list_header_id = p_list_header_id
2137 and list_line_id = -1
2138 and ((list_type_code = 'PRL'
2139 AND QUALIFIER_CONTEXT <> 'MODLIST'
2140 AND QUALIFIER_ATTRIBUTE <> 'QUALIFIER_ATTRIBUTE4')
2141 OR
2142 (list_type_code <> 'PRL')
2143 )
2144 and QUALIFIER_GROUPING_NO <> -1
2145 and rownum = 1;
2146 exception
2147 when no_data_found then
2148 l_qual_exists := 'N';
2149 end;
2150
2151 if l_qual_exists = 'N' then
2152 -- no qualifiers exist other than -1 qualifier_grouping_no
2153 -- this may insert in qp_attribute_groups with HDR_QUAL_GROUPING_NO = -1, if any
2154 -- qualifiers exist with qualifier_grouping_no = -1
2155 generate_hp_atgrps(p_list_header_id, p_qualifier_group);
2156 end if; -- l_qual_exists = 'N'
2157 end if; -- l_qual_exists = 'Y'
2158 end if; --p_qualifier_group is null
2159 elsif p_setup_action = 'UD' then
2160 begin
2161 select ACTIVE_FLAG,
2162 LIST_TYPE_CODE,
2163 CURRENCY_CODE,
2164 ASK_FOR_FLAG,
2165 LIMIT_EXISTS_FLAG,
2166 SOURCE_SYSTEM_CODE
2167 into l_ACTIVE_FLAG,
2168 l_LIST_TYPE_CODE,
2169 l_CURRENCY_CODE,
2170 l_ASK_FOR_FLAG,
2171 l_HEADER_LIMIT_EXISTS,
2172 l_SOURCE_SYSTEM_CODE
2173 from qp_list_headers_b
2174 where list_header_id = p_list_header_id;
2175
2176 -- update header pattern records
2177 update qp_attribute_groups
2178 set ACTIVE_FLAG = l_ACTIVE_FLAG,
2179 LIST_TYPE_CODE = l_LIST_TYPE_CODE,
2180 CURRENCY_CODE = l_CURRENCY_CODE,
2181 ASK_FOR_FLAG = l_ASK_FOR_FLAG,
2182 LIMIT_EXISTS = l_HEADER_LIMIT_EXISTS,
2183 SOURCE_SYSTEM_CODE = l_SOURCE_SYSTEM_CODE
2184 where list_header_id = p_list_header_id
2185 and list_line_id = -1;
2186
2187 -- update line pattern records
2188 update qp_attribute_groups
2189 set ACTIVE_FLAG = l_ACTIVE_FLAG,
2190 LIST_TYPE_CODE = l_LIST_TYPE_CODE,
2191 CURRENCY_CODE = l_CURRENCY_CODE,
2192 ASK_FOR_FLAG = l_ASK_FOR_FLAG,
2193 SOURCE_SYSTEM_CODE = l_SOURCE_SYSTEM_CODE
2194 where list_header_id = p_list_header_id
2195 and list_line_id <> -1;
2196
2197 exception
2198 when no_data_found then
2199 if g_call_from_setup = 'Y' then
2200 oe_debug_pub.add('Header_Pattern_Main - no_data_found in action UD ' );
2201 end if;
2202 null;
2203
2204 when others then
2205 if g_call_from_setup = 'Y' then
2206 oe_debug_pub.add('Header_Pattern_Main - others exceptions in action UD ' );
2207 end if;
2208 null;
2209 end;
2210 end if; -- p_setup_action = 'I'
2211
2212 EXCEPTION
2213 WHEN OTHERS THEN
2214 if g_call_from_setup = 'Y' then
2215 oe_debug_pub.add('ATTR_GRP_PVT.Header_Pattern_Main ' || SQLERRM);
2216 else
2217 FND_FILE.PUT_LINE( FND_FILE.LOG, 'ATTR_GRP_PVT.Header_Pattern_Main ' || SQLERRM );
2218 end if;
2219
2220 END Header_Pattern_Main;
2221
2222 PROCEDURE Line_Pattern_Main(
2223 p_list_header_id IN NUMBER
2224 ,p_list_line_id IN NUMBER
2225 ,p_qualifier_group IN NUMBER
2226 ,p_setup_action IN VARCHAR2 ) IS
2227
2228 l_status_code VARCHAR2(30) := NULL;
2229 l_status_text VARCHAR2(2000) := NULL;
2230 l_pid NUMBER := NULL;
2231 l_qual_exists varchar2(1) := 'N';
2232 l_line_LIMIT_EXISTS qp_list_lines.LIMIT_EXISTS_FLAG%type;
2233
2234 BEGIN
2235 g_call_from_setup := 'Y';
2236
2237 if g_call_from_setup = 'Y' then
2238 oe_debug_pub.add('Line_Pattern_Main - p_list_header_id = ' ||p_list_header_id);
2239 oe_debug_pub.add('Line_Pattern_Main - p_list_line_id = ' ||p_list_line_id);
2240 oe_debug_pub.add('Line_Pattern_Main - p_qualifier_group = ' ||p_qualifier_group);
2241 oe_debug_pub.add('Line_Pattern_Main - p_setup_action = ' ||p_setup_action);
2242 else
2243 fnd_file.put_line(FND_FILE.LOG, 'Line_Pattern_Main - p_list_header_id = ' ||p_list_header_id);
2244 fnd_file.put_line(FND_FILE.LOG, 'Line_Pattern_Main - p_list_line_id = ' ||p_list_line_id);
2245 fnd_file.put_line(FND_FILE.LOG, 'Line_Pattern_Main - p_qualifier_group = ' ||p_qualifier_group);
2246 fnd_file.put_line(FND_FILE.LOG, 'Line_Pattern_Main - p_setup_action = ' ||p_setup_action);
2247 end if;
2248
2249 -- when called while set up of modifier
2250 if p_setup_action <> 'UD' then
2251 -- delete from qp_attribute_groups first
2252 if p_qualifier_group is null then
2253 delete from qp_attribute_groups
2254 where list_header_id = p_list_header_id
2258 where list_header_id = p_list_header_id
2255 and list_line_id = p_list_line_id;
2256 elsif p_qualifier_group is not null then
2257 delete from qp_attribute_groups
2259 and list_line_id = p_list_line_id
2260 and GROUPING_NO in (-1, p_qualifier_group);
2261 end if;
2262
2263 -- update the segment_id columns for qualifiers
2264 Update_Qual_Segment_id(p_list_header_id, p_qualifier_group, p_list_line_id, p_list_line_id);
2265
2266 -- update the product_segment_id and pricing_segment_id columns in qp_pricing_attributes
2267 Update_Prod_Pric_Segment_id(p_list_header_id, p_list_line_id, p_list_line_id );
2268 end if;
2269
2270 -- populate the records in qp_attribute_groups afresh for p_list_header_id, p_list_line_id
2271 -- and p_qualifier_group
2272 if p_setup_action = 'I' then
2273 -- insert case
2274 generate_lp_atgrps(p_list_header_id, p_qualifier_group,
2275 p_list_line_id, p_list_line_id);
2276 elsif p_setup_action = 'U' or p_setup_action = 'D' then
2277 -- update or delete case
2278 if p_qualifier_group is null then
2279 update qp_list_lines
2280 set pattern_id = null,
2281 hash_key = null,
2282 cache_key = null
2283 where list_line_id = p_list_line_id
2284 and qualification_ind in (0, 2);
2285 generate_lp_atgrps(p_list_header_id, p_qualifier_group,
2286 p_list_line_id, p_list_line_id);
2287 elsif p_qualifier_group is not null then
2288 begin
2289 select 'Y'
2290 into l_qual_exists
2291 from qp_qualifiers
2292 where list_header_id = p_list_header_id
2293 and list_line_id = p_list_line_id
2294 and QUALIFIER_GROUPING_NO = p_qualifier_group
2295 and rownum = 1;
2296 exception
2297 when no_data_found then
2298 l_qual_exists := 'N';
2299 end;
2300
2301 if l_qual_exists = 'Y' then
2302 -- means some qualifiers still exist for p_qualifier_group
2303 update qp_list_lines
2304 set pattern_id = null,
2305 hash_key = null,
2306 cache_key = null
2307 where list_line_id = p_list_line_id
2308 and qualification_ind in (0, 2);
2309 generate_lp_atgrps(p_list_header_id, p_qualifier_group,
2310 p_list_line_id, p_list_line_id);
2311 else
2312 begin
2313 select 'Y'
2314 into l_qual_exists
2315 from qp_qualifiers
2316 where list_header_id = p_list_header_id
2317 and list_line_id = p_list_line_id
2318 and QUALIFIER_GROUPING_NO <> -1
2319 and rownum = 1;
2320 exception
2321 when no_data_found then
2322 l_qual_exists := 'N';
2323 end;
2324
2325 if l_qual_exists = 'N' then
2326 -- no qualifiers exist other than -1 qualifier_grouping_no
2327 -- this may insert in qp_attribute_groups with LINE_QUAL_GROUPING_NO = -1, if any
2328 -- qualifiers exist with qualifier_grouping_no = -1
2329 update qp_list_lines
2330 set pattern_id = null,
2331 hash_key = null,
2332 cache_key = null
2333 where list_line_id = p_list_line_id
2334 and qualification_ind in (0, 2);
2335 generate_lp_atgrps(p_list_header_id,p_qualifier_group,
2336 p_list_line_id, p_list_line_id);
2337 end if; -- l_qual_exists = 'N'
2338 end if; -- l_qual_exists = 'Y'
2339 end if; --p_qualifier_group is null
2340 elsif p_setup_action = 'UD' then
2341 begin
2342 select LIMIT_EXISTS_FLAG
2343 into l_line_LIMIT_EXISTS
2344 from qp_list_lines
2345 where list_line_id = p_list_line_id;
2346
2347 -- update line pattern records
2348 update qp_attribute_groups
2349 set LIMIT_EXISTS = l_line_LIMIT_EXISTS
2350 where list_header_id = p_list_header_id
2351 and list_line_id = p_list_line_id;
2352
2353 exception
2354 when no_data_found then
2355 if g_call_from_setup = 'Y' then
2356 oe_debug_pub.add('Line_Pattern_Main - no_data_found in action UD ' );
2357 end if;
2358 null;
2359
2360 when others then
2361 if g_call_from_setup = 'Y' then
2362 oe_debug_pub.add('Line_Pattern_Main - others exceptions in action UD ' );
2363 end if;
2364 null;
2365 end;
2366 end if; -- p_setup_action = 'I'
2367
2368 -- at last, delete/restore PP depending on whether LP exists or not for passed header_id, line_id
2369 if p_setup_action = 'D' then
2370 -- line qualifier delete case
2371 begin
2372 select 'Y'
2373 into l_qual_exists
2374 from qp_attribute_groups
2375 where list_header_id = p_list_header_id
2376 and list_line_id = p_list_line_id
2377 and rownum = 1;
2378 exception
2379 when no_data_found then
2380 l_qual_exists := 'N';
2381 end;
2382 else
2386
2383 -- line qualifier insert/update case, assume there will be record in qp_attribute_groups
2384 l_qual_exists := 'Y';
2385 end if;
2387 if p_setup_action = 'I' then
2388 -- assume LP exists, and so nullify the PP values in qp_list_lines table
2389 --except cache_key
2390 update qp_list_lines
2391 set pattern_id = null,
2392 pricing_attribute_count = null,
2393 product_uom_code = null,
2394 hash_key = null
2395 where list_line_id = p_list_line_id
2396 and pattern_id is not null;
2397
2398 end if;
2399
2400 if l_qual_exists = 'N' and p_setup_action = 'D' then
2401 -- means restore PP
2402 remove_prod_pattern_for_line(p_list_line_id);
2403 update_pp_lines(p_list_header_id, p_list_line_id, p_list_line_id);
2404 end if;
2405
2406 EXCEPTION
2407 WHEN OTHERS THEN
2408 if g_call_from_setup = 'Y' then
2409 oe_debug_pub.add('ATTR_GRP_PVT.Line_Pattern_Main ' || SQLERRM);
2410 else
2411 FND_FILE.PUT_LINE( FND_FILE.LOG, 'ATTR_GRP_PVT.Line_Pattern_Main ' || SQLERRM );
2412 end if;
2413
2414 END Line_Pattern_Main;
2415
2416 PROCEDURE Product_Pattern_Main(
2417 p_list_header_id IN NUMBER ,
2418 p_list_line_id IN NUMBER ,
2419 p_setup_action IN VARCHAR2 ) IS
2420
2421 l_status_code VARCHAR2(30) := NULL;
2422 l_status_text VARCHAR2(2000) := NULL;
2423 l_qual_exists varchar2(1) := 'N';
2424 l_product_uom_code qp_list_lines.product_uom_code%type;
2425 l_qual_ind number;
2426
2427 BEGIN
2428 g_call_from_setup := 'Y';
2429
2430 if g_call_from_setup = 'Y' then
2431 oe_debug_pub.add('Product_Pattern_Main - p_list_header_id = ' ||p_list_header_id);
2432 oe_debug_pub.add('Product_Pattern_Main - p_list_line_id = ' ||p_list_line_id);
2433 oe_debug_pub.add('Product_Pattern_Main - p_setup_action = ' ||p_setup_action);
2434 else
2435 fnd_file.put_line(FND_FILE.LOG, 'Product_Pattern_Main - p_list_header_id = ' ||p_list_header_id);
2436 fnd_file.put_line(FND_FILE.LOG, 'Product_Pattern_Main - p_list_line_id = ' ||p_list_line_id);
2437 fnd_file.put_line(FND_FILE.LOG, 'Product_Pattern_Main - p_setup_action = ' ||p_setup_action);
2438 end if;
2439
2440 select qualification_ind
2441 into l_qual_ind
2442 from qp_list_lines
2443 where list_line_id = p_list_line_id;
2444
2445 oe_debug_pub.add('Product_Pattern_Main - l_qual_ind = ' ||l_qual_ind);
2446
2447 -- do nothing, return back if called for child line, bug 3581058
2448 if l_qual_ind in (4, 6, 8, 10, 12, 14, 20, 22, 28, 30) then
2449 null;
2450 else
2451 return;
2452 end if;
2453
2454 if p_setup_action = 'UD' then
2455 -- update qp_list_lines.product_uom_code
2456 begin
2457 select product_uom_code
2458 into l_product_uom_code
2459 from qp_pricing_attributes
2460 where list_header_id = p_list_header_id
2461 and list_line_id = p_list_line_id
2462 and product_uom_code is not null
2463 and rownum = 1;
2464 exception
2465 when no_data_found then
2466 l_product_uom_code := null;
2467 end;
2468
2469 update qp_list_lines
2470 set product_uom_code = l_product_uom_code
2471 where list_line_id = p_list_line_id;
2472 else
2473 -- when called while set up of price list/modifier
2474 begin
2475 select 'Y'
2476 into l_qual_exists
2477 from qp_qualifiers
2478 where list_header_id = p_list_header_id
2479 and list_line_id = p_list_line_id
2480 and rownum = 1;
2481 exception
2482 when no_data_found then
2483 l_qual_exists := 'N';
2484 end;
2485
2486 if l_qual_exists = 'Y' then
2487 if g_call_from_setup = 'Y' then
2488 oe_debug_pub.add('going to populate LP');
2489 else
2490 fnd_file.put_line(FND_FILE.LOG, 'going to populate LP');
2491 end if;
2492 Line_Pattern_Main(p_list_header_id, p_list_line_id, null, 'I');
2493 else
2494 if g_call_from_setup = 'Y' then
2495 oe_debug_pub.add('going to populate PP');
2496 else
2497 fnd_file.put_line(FND_FILE.LOG, 'going to populate PP');
2498 end if;
2499
2500 remove_prod_pattern_for_line(p_list_line_id);
2501
2502 -- update the product_segment_id and pricing_segment_id columns in qp_pricing_attributes
2503 Update_Prod_Pric_Segment_id(p_list_header_id, p_list_line_id,
2504 p_list_line_id);
2505
2506 update_pp_lines(p_list_header_id, p_list_line_id, p_list_line_id);
2507 end if;
2508 end if; -- p_setup_action = 'UD'
2509
2510 EXCEPTION
2511 WHEN OTHERS THEN
2512 if g_call_from_setup = 'Y' then
2513 oe_debug_pub.add('ATTR_GRP_PVT.Product_Pattern_Main ' || SQLERRM);
2514 else
2515 FND_FILE.PUT_LINE( FND_FILE.LOG, 'ATTR_GRP_PVT.Product_Pattern_Main ' || SQLERRM );
2516 end if;
2517
2518 END Product_Pattern_Main;
2519
2520 procedure Remove_Prod_Pattern_for_Line(p_list_line_id IN NUMBER)
2521 is
2522 begin
2523 update qp_list_lines
2524 set pattern_id = null,
2525 pricing_attribute_count = null,
2526 product_uom_code = null,
2530 and pattern_id is not null;
2527 hash_key = null,
2528 cache_key = null
2529 where list_line_id = p_list_line_id
2531
2532 exception
2533 when no_data_found then
2534 null;
2535
2536 when others then
2537 if g_call_from_setup = 'Y' then
2538 oe_debug_pub.add('ATTR_GRP_PVT.Remove_Prod_Pattern_For_Line ' || SQLERRM);
2539 else
2540 FND_FILE.PUT_LINE( FND_FILE.LOG, 'ATTR_GRP_PVT.Remove_Prod_Pattern_For_Line ' || SQLERRM );
2541 end if;
2542 raise;
2543 end remove_prod_pattern_for_line;
2544
2545 procedure Update_Qual_Segment_id(p_list_header_id IN NUMBER
2546 ,p_qualifier_group IN NUMBER
2547 ,p_low_list_line_id IN NUMBER
2548 ,p_high_list_line_id IN NUMBER)
2549 is
2550 cursor c_qual_seg_id is
2551 select distinct QUALIFIER_CONTEXT, QUALIFIER_ATTRIBUTE
2552 from qp_qualifiers
2553 where QUALIFIER_CONTEXT is not null
2554 and QUALIFIER_ATTRIBUTE is not null
2555 and list_header_id = nvl(p_list_header_id, list_header_id)
2556 and list_line_id between p_low_list_line_id and p_high_list_line_id
2557 and ((p_qualifier_group is not null and qualifier_grouping_no in (-1, p_qualifier_group))
2558 OR
2559 (p_qualifier_group is null)
2560 );
2561
2562 TYPE segment_id_tab IS TABLE OF qp_qualifiers.segment_id%TYPE INDEX BY BINARY_INTEGER;
2563 TYPE context_tab IS TABLE OF qp_qualifiers.QUALIFIER_CONTEXT%TYPE INDEX BY BINARY_INTEGER;
2564 TYPE attribute_tab IS TABLE OF qp_qualifiers.QUALIFIER_ATTRIBUTE%TYPE INDEX BY BINARY_INTEGER;
2565
2566 segment_id_t segment_id_tab;
2567 context_t context_tab;
2568 attribute_t attribute_tab;
2569
2570 begin
2571 -- update the segment_id columns for qualifiers
2572 if g_call_from_setup = 'Y' then
2573 oe_debug_pub.add('Inside Update_Qual_Segment_id');
2574 oe_debug_pub.add('Start time :'||to_char(sysdate,'MM/DD/YYYY:HH:MI:SSSS'));
2575 else
2576 fnd_file.put_line(FND_FILE.LOG, 'Inside Update_Qual_Segment_id');
2577 fnd_file.put_line(FND_FILE.LOG, 'Start time :'||to_char(sysdate,'MM/DD/YYYY:HH:MI:SSSS'));
2578 end if;
2579 segment_id_t.delete;
2580 context_t.delete;
2581 attribute_t.delete;
2582
2583 OPEN c_qual_seg_id;
2584 FETCH c_qual_seg_id BULK COLLECT INTO
2585 context_t,
2586 attribute_t;
2587 CLOSE c_qual_seg_id;
2588
2589 if context_t.count > 0 then
2590 if g_call_from_setup = 'Y' then
2591 oe_debug_pub.add('Context_t.count='||context_t.count);
2592 else
2593 fnd_file.put_line(FND_FILE.LOG, 'Context_t.count='||context_t.count);
2594 end if;
2595 FOR i in 1..context_t.count
2596 LOOP
2597 select b.segment_id
2598 into segment_id_t(i)
2599 from qp_prc_contexts_b a, qp_segments_b b
2600 where b.prc_context_id = a.prc_context_id
2601 and a.PRC_CONTEXT_CODE = context_t(i)
2602 and b.SEGMENT_MAPPING_COLUMN = attribute_t(i);
2603 END LOOP;
2604
2605 FORALL j in 1..context_t.count
2606 update qp_qualifiers
2607 set segment_id = segment_id_t(j)
2608 where QUALIFIER_CONTEXT = context_t(j)
2609 and QUALIFIER_ATTRIBUTE = attribute_t(j)
2610 and list_header_id = nvl(p_list_header_id, list_header_id)
2611 and list_line_id between p_low_list_line_id and p_high_list_line_id;
2612
2613 if g_call_from_setup = 'Y' then
2614 oe_debug_pub.add('No of qualifiers updated='||SQL%ROWCOUNT);
2615 else
2616 fnd_file.put_line(FND_FILE.LOG, 'No of qualifiers updated='||SQL%ROWCOUNT);
2617 end if;
2618 end if; -- context_t.count > 0
2619
2620 if g_call_from_setup = 'Y' then
2621 oe_debug_pub.add('End Update_Qual_Segment_id');
2622 oe_debug_pub.add('End time :'||to_char(sysdate,'MM/DD/YYYY:HH:MI:SSSS'));
2623 else
2624 fnd_file.put_line(FND_FILE.LOG, 'End Update_Qual_Segment_id');
2625 fnd_file.put_line(FND_FILE.LOG, 'End time :'||to_char(sysdate,'MM/DD/YYYY:HH:MI:SSSS'));
2626 end if;
2627 exception
2628 when no_data_found then
2629 if g_call_from_setup = 'Y' then
2630 oe_debug_pub.add('No data found in c_qual_seg_id');
2631 else
2632 fnd_file.put_line(FND_FILE.LOG, 'No data found in c_qual_seg_id');
2633 end if;
2634
2635 when others then
2636 if g_call_from_setup = 'Y' then
2637 oe_debug_pub.add('ATTR_GRP_PVT.Update_Qual_Segment_Id ' || SQLERRM);
2638 else
2639 FND_FILE.PUT_LINE( FND_FILE.LOG, 'ATTR_GRP_PVT.Update_Qual_Segment_Id ' || SQLERRM );
2640 end if;
2641
2642 raise;
2643
2644 end Update_Qual_Segment_id;
2645
2646 procedure Update_Prod_Pric_Segment_id(p_list_header_id IN NUMBER
2647 -- ,p_list_line_id IN NUMBER
2648 ,p_low_list_line_id IN NUMBER
2649 ,p_high_list_line_id IN NUMBER)
2650 is
2651 cursor c_prod_seg_id is
2652 select distinct PRODUCT_ATTRIBUTE_CONTEXT, PRODUCT_ATTRIBUTE
2653 from qp_pricing_attributes
2654 where PRODUCT_ATTRIBUTE_CONTEXT is not null
2655 and PRODUCT_ATTRIBUTE is not null
2656 --and list_header_id = nvl(p_list_header_id, list_header_id)
2657 and list_line_id between p_low_list_line_id and p_high_list_line_id;
2658
2659 cursor c_pric_seg_id is
2660 select distinct PRICING_ATTRIBUTE_CONTEXT, PRICING_ATTRIBUTE
2661 from qp_pricing_attributes
2662 where PRICING_ATTRIBUTE_CONTEXT is not null
2663 and PRICING_ATTRIBUTE is not null
2664 --and list_header_id = nvl(p_list_header_id, list_header_id)
2665 and list_line_id between p_low_list_line_id and p_high_list_line_id;
2666
2667 TYPE segment_id_tab IS TABLE OF qp_pricing_attributes.product_segment_id%TYPE INDEX BY BINARY_INTEGER;
2668 TYPE context_tab IS TABLE OF qp_pricing_attributes.PRODUCT_ATTRIBUTE_CONTEXT%TYPE INDEX BY BINARY_INTEGER;
2669 TYPE attribute_tab IS TABLE OF qp_pricing_attributes.PRODUCT_ATTRIBUTE%TYPE INDEX BY BINARY_INTEGER;
2670
2671 segment_id_t segment_id_tab;
2672 context_t context_tab;
2673 attribute_t attribute_tab;
2674
2675
2676 begin
2677 if g_call_from_setup = 'Y' then
2678 oe_debug_pub.add('Inside Update_Prod_Pric_Segment_id');
2679 oe_debug_pub.add('Start time :'||to_char(sysdate,'MM/DD/YYYY:HH:MI:SSSS'));
2680 else
2681 fnd_file.put_line(FND_FILE.LOG, 'Inside Update_Prod_Pric_Segment_id');
2682 fnd_file.put_line(FND_FILE.LOG, 'Start time :'||to_char(sysdate,'MM/DD/YYYY:HH:MI:SSSS'));
2683 end if;
2684 -- update the product_segment_id column in qp_pricing_attributes
2685 segment_id_t.delete;
2686 context_t.delete;
2687 attribute_t.delete;
2688
2689 OPEN c_prod_seg_id;
2690 FETCH c_prod_seg_id BULK COLLECT INTO
2691 context_t,
2692 attribute_t;
2693 CLOSE c_prod_seg_id;
2694
2695 if context_t.count > 0 then
2699 fnd_file.put_line(FND_FILE.LOG, 'Context_t.count='||context_t.count);
2696 if g_call_from_setup = 'Y' then
2697 oe_debug_pub.add('Context_t.count='||context_t.count);
2698 else
2700 end if;
2701 FOR i in 1..context_t.count
2702 LOOP
2703 select b.segment_id
2704 into segment_id_t(i)
2705 from qp_prc_contexts_b a, qp_segments_b b
2706 where b.prc_context_id = a.prc_context_id
2707 and a.PRC_CONTEXT_CODE = context_t(i)
2708 and b.SEGMENT_MAPPING_COLUMN = attribute_t(i);
2709 END LOOP;
2710
2711 FORALL j in 1..context_t.count
2712 update qp_pricing_attributes
2713 set product_segment_id = segment_id_t(j)
2714 where PRODUCT_ATTRIBUTE_CONTEXT = context_t(j)
2715 and PRODUCT_ATTRIBUTE = attribute_t(j)
2716 --and list_header_id = nvl(p_list_header_id, list_header_id)
2717 and list_line_id between p_low_list_line_id and p_high_list_line_id;
2718
2719 if g_call_from_setup = 'Y' then
2720 oe_debug_pub.add('No of product segment ids updated='||SQL%ROWCOUNT);
2721 oe_debug_pub.add('End Time product segments :'||to_char(sysdate,'MM/DD/YYYY:HH:MI:SSSS'));
2722 else
2723 fnd_file.put_line(FND_FILE.LOG, 'No of product segment ids updated='||SQL%ROWCOUNT);
2724 fnd_file.put_line(FND_FILE.LOG, 'End time product segments :'||to_char(sysdate,'MM/DD/YYYY:HH:MI:SSSS'));
2725 end if;
2726 end if; -- context_t.count > 0
2727
2728 -- update the pricing_segment_id columns in qp_pricing_attributes
2729 segment_id_t.delete;
2730 context_t.delete;
2731 attribute_t.delete;
2732
2733 OPEN c_pric_seg_id;
2734 FETCH c_pric_seg_id BULK COLLECT INTO
2735 context_t,
2736 attribute_t;
2737 CLOSE c_pric_seg_id;
2738
2739 if context_t.count > 0 then
2740 if g_call_from_setup = 'Y' then
2741 oe_debug_pub.add('Context_t.count='||context_t.count);
2742 else
2743 fnd_file.put_line(FND_FILE.LOG, 'Context_t.count='||context_t.count);
2744 end if;
2745
2746 FOR i in 1..context_t.count
2747 LOOP
2748 select b.segment_id
2749 into segment_id_t(i)
2750 from qp_prc_contexts_b a, qp_segments_b b
2751 where b.prc_context_id = a.prc_context_id
2752 and a.PRC_CONTEXT_CODE = context_t(i)
2753 and b.SEGMENT_MAPPING_COLUMN = attribute_t(i);
2754 END LOOP;
2755
2756 FORALL j in 1..context_t.count
2757 update qp_pricing_attributes
2758 set pricing_segment_id = segment_id_t(j)
2759 where PRICING_ATTRIBUTE_CONTEXT = context_t(j)
2760 and PRICING_ATTRIBUTE = attribute_t(j)
2761 --and list_header_id = nvl(p_list_header_id, list_header_id)
2762 and list_line_id between p_low_list_line_id and p_high_list_line_id;
2763
2764 if g_call_from_setup = 'Y' then
2765 oe_debug_pub.add('No of pricing segment ids updated='||SQL%ROWCOUNT);
2766 oe_debug_pub.add('End Time pricing segments :'||to_char(sysdate,'MM/DD/YYYY:HH:MI:SSSS'));
2767 else
2768 fnd_file.put_line(FND_FILE.LOG, 'No of pricing segment ids updated='||SQL%ROWCOUNT);
2769 fnd_file.put_line(FND_FILE.LOG, 'End time pricing segments :'||to_char(sysdate,'MM/DD/YYYY:HH:MI:SSSS'));
2770 end if;
2771 end if; -- context_t.count > 0
2772
2773 if g_call_from_setup = 'Y' then
2774 oe_debug_pub.add('End Update_Prod_Pric_Segment_id');
2775 else
2776 fnd_file.put_line(FND_FILE.LOG, 'End Update_Prod_Pric_Segment_id');
2777 end if;
2778 exception
2779 when no_data_found then
2780 if g_call_from_setup = 'Y' then
2781 oe_debug_pub.add('No data found in Update_Prod_Pric_Segment_id');
2782 else
2783 fnd_file.put_line(FND_FILE.LOG, 'No data found in Update_Prod_Pric_Segment_id');
2784 end if;
2785
2786 null;
2787
2788 when others then
2789 if g_call_from_setup = 'Y' then
2790 oe_debug_pub.add('ATTR_GRP_PVT.Update_Prod_Pric_Segment_id ' || SQLERRM);
2791 else
2792 FND_FILE.PUT_LINE( FND_FILE.LOG, 'ATTR_GRP_PVT.Update_Prod_Pric_Segment_id ' || SQLERRM );
2793 end if;
2794
2795 raise;
2796
2797 end Update_Prod_Pric_Segment_id;
2798
2799 end QP_ATTR_GRP_PVT; -- end package