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