[Home] [Help]
PACKAGE BODY: APPS.QPR_LOAD_DIM_DATA
Source
1 PACKAGE BODY QPR_LOAD_DIM_DATA AS
2 /* $Header: QPRUDLDB.pls 120.2 2008/06/06 15:03:27 bhuchand ship $ */
3 /* Private Procedures */
4
5 procedure get_data_from_cursor(
6 l_dim_src_instance_id in number,
7 p_dim_code in varchar2,
8 p_hier_code in varchar2,
9 p_instance_id in number,
10 date_from in date,
11 date_to in date,
12 l_inst_type in varchar2);
13
14 procedure insert_dimension_data(
15 p_instance_id in number,
16 p_dim_code in varchar2,
17 p_hier_code in varchar2,
18 c_dim_data_rec in out nocopy QPR_LOAD_DIM_DATA.DIM_DATA_REC_TYPE);
19
20 /*procedure delete_duplicate_data(
21 p_instance_id in number);
22 */
23 procedure load_dim_data(
24 errbuf OUT NOCOPY VARCHAR2,
25 retcode OUT NOCOPY VARCHAR2,
26 p_dim_code in varchar2,
27 p_hier_code in varchar2,
28 p_instance_id in number,
29 p_start_date in varchar2,
30 p_end_date in varchar2)
31 is
32
33 cursor get_all_dim_hier (l_dim_src_instance_id number, l_inst_type varchar2)
34 is
35 select distinct dim_code dim, hier_code hier
36 from qpr_dim_sources
37 where instance_id = l_dim_src_instance_id
38 and instance_type = l_inst_type;
39
40 cursor get_all_hier (p_dim_code varchar2, l_dim_src_instance_id number, l_inst_type varchar2)
41 is
42 select distinct hier_code hier
43 from qpr_dim_sources
44 where instance_id = l_dim_src_instance_id
45 and dim_code = p_dim_code
46 and instance_type = l_inst_type;
47
48 l_start_time number;
49 l_end_time number;
50 l_check_instance number;
51 l_dim_src_instance_id number;
52 l_param_check number;
53 date_from date;
54 date_to date;
55 l_inst_type varchar2(30);
56
57 Begin
58
59 date_from := fnd_date.canonical_to_date(p_start_date);
60 date_to := fnd_date.canonical_to_date(p_end_date);
61
62 fnd_file.put_line(fnd_file.log, 'Starting...');
63 select hsecs into l_start_time from v$timer;
64 fnd_file.put_line(fnd_file.log, 'Start time :'||to_char(sysdate,'MM/DD/YYYY:HH:MM:SS'));
65
66 fnd_file.put_line(fnd_file.log, 'Dimension: '||p_dim_code);
67 fnd_file.put_line(fnd_file.log, 'Hierarchy: '||p_hier_code);
68
69 l_dim_src_instance_id := p_instance_id;
70
71 select instance_type into l_inst_type
72 from qpr_instances
73 where instance_id = p_instance_id;
74
75 select count(*) into l_check_instance
76 from qpr_dim_sources
77 where instance_id = p_instance_id;
78
79 if (l_check_instance = 0)
80 then
81 l_dim_src_instance_id := 1;
82 end if;
83
84 if not qpr_sr_util.dm_parameters_ok then
85 retcode:= 2;
86 fnd_file.put_line(fnd_file.log, 'One or more mandatory parameters are NULL');
87 return;
88 end if;
89
90 -- Calling procedure with proper parameters based on the Dimension/Hierarchy --
91 if (p_dim_code = 'ALL')
92 then
93 for l_dim_hier_rec in get_all_dim_hier (l_dim_src_instance_id, l_inst_type)
94 loop
95 get_data_from_cursor(l_dim_src_instance_id, l_dim_hier_rec.dim, l_dim_hier_rec.hier, p_instance_id, date_from, date_to, l_inst_type);
96 end loop;
97
98 elsif (p_hier_code = 'ALL')
99 then
100 for l_hier_rec in get_all_hier (p_dim_code, l_dim_src_instance_id, l_inst_type)
101 loop
102 get_data_from_cursor(l_dim_src_instance_id, p_dim_code, l_hier_rec.hier, p_instance_id, date_from, date_to, l_inst_type);
103 end loop;
104
105 else
106 get_data_from_cursor(l_dim_src_instance_id, p_dim_code, p_hier_code, p_instance_id, date_from, date_to, l_inst_type);
107
108 end if;
109
110 -- delete_duplicate_data (p_instance_id);
111 -- commit;
112
113 select hsecs into l_end_time from v$timer;
114 fnd_file.put_line(fnd_file.log, 'End time :'||to_char(sysdate,'MM/DD/YYYY:HH:MM:SS'));
115 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Time taken for loading dimension data (sec):' ||(l_end_time - l_start_time)/100);
116
117 exception
118 WHEN NO_DATA_FOUND THEN
119 retcode := 2;
120 errbuf := FND_MESSAGE.GET;
121 fnd_file.put_line( fnd_file.log, 'Unexpected error '||substr(sqlerrm,1200));
122 End;
123
124 /*
125 procedure delete_duplicate_data (p_instance_id in number)
126 is
127 Begin
128 delete from qpr_dimension_values a
129 where a.instance_id = p_instance_id
130 and a.rowid >
131 ANY
132 (select b.rowid
133 from qpr_dimension_values b
134 where a.dim_code = b.dim_code
135 and a.hierarchy_code = b.hierarchy_code
136 and a.instance_id = b.instance_id
137 and a.level1_value = b.level1_value);
138 End;
139 */
140
141
142 procedure get_data_from_cursor(
143 l_dim_src_instance_id in number,
144 p_dim_code in varchar2,
145 p_hier_code in varchar2,
146 p_instance_id in number,
147 date_from in date,
148 date_to in date,
149 l_inst_type in varchar2)
150 is
151
152 c_dim_data QPRDIMDATA;
153 c_dim_data_rec DIM_DATA_REC_TYPE;
154
155 l_rows natural := 1000;
156 l_sql varchar2(4000);
157
158 l_count number;
159 l_attr_num number;
160 l_level_defined number;
161 l_attr_defined number;
162 l_source_view_name varchar2(30);
163 l_desc_column varchar2(30);
164 l_value_column varchar2(30);
165 l_attr_column varchar2(30);
166 l_level_number number;
167 l_hierarchy_level_id number;
168 l_date_column varchar2(30);
169 l_temp_value varchar2(240);
170 procedure delete_arrays(l_ctr number) is
171 begin
172 if l_ctr is null then
173 c_dim_data_rec.level1_value.delete;
174 c_dim_data_rec.level1_desc.delete;
175 c_dim_data_rec.level1_attribute1.delete;
176 c_dim_data_rec.level1_attribute2.delete;
177 c_dim_data_rec.level1_attribute3.delete;
178 c_dim_data_rec.level1_attribute4.delete;
179 c_dim_data_rec.level1_attribute5.delete;
180 c_dim_data_rec.level2_value.delete;
181 c_dim_data_rec.level2_desc.delete;
182 c_dim_data_rec.level2_attribute1.delete;
183 c_dim_data_rec.level2_attribute2.delete;
184 c_dim_data_rec.level2_attribute3.delete;
185 c_dim_data_rec.level2_attribute4.delete;
186 c_dim_data_rec.level2_attribute5.delete;
187 c_dim_data_rec.level3_value.delete;
188 c_dim_data_rec.level3_desc.delete;
189 c_dim_data_rec.level3_attribute1.delete;
190 c_dim_data_rec.level3_attribute2.delete;
191 c_dim_data_rec.level3_attribute3.delete;
192 c_dim_data_rec.level3_attribute4.delete;
193 c_dim_data_rec.level3_attribute5.delete;
194 c_dim_data_rec.level4_value.delete;
195 c_dim_data_rec.level4_desc.delete;
196 c_dim_data_rec.level4_attribute1.delete;
197 c_dim_data_rec.level4_attribute2.delete;
198 c_dim_data_rec.level4_attribute3.delete;
199 c_dim_data_rec.level4_attribute4.delete;
200 c_dim_data_rec.level4_attribute5.delete;
201 c_dim_data_rec.level5_value.delete;
202 c_dim_data_rec.level5_desc.delete;
203 c_dim_data_rec.level5_attribute1.delete;
204 c_dim_data_rec.level5_attribute2.delete;
205 c_dim_data_rec.level5_attribute3.delete;
206 c_dim_data_rec.level5_attribute4.delete;
207 c_dim_data_rec.level5_attribute5.delete;
208 c_dim_data_rec.level6_value.delete;
209 c_dim_data_rec.level6_desc.delete;
210 c_dim_data_rec.level6_attribute1.delete;
211 c_dim_data_rec.level6_attribute2.delete;
212 c_dim_data_rec.level6_attribute3.delete;
213 c_dim_data_rec.level6_attribute4.delete;
214 c_dim_data_rec.level6_attribute5.delete;
215 c_dim_data_rec.level7_value.delete;
216 c_dim_data_rec.level7_desc.delete;
217 c_dim_data_rec.level7_attribute1.delete;
218 c_dim_data_rec.level7_attribute2.delete;
219 c_dim_data_rec.level7_attribute3.delete;
220 c_dim_data_rec.level7_attribute4.delete;
221 c_dim_data_rec.level7_attribute5.delete;
222 c_dim_data_rec.level8_value.delete;
223 c_dim_data_rec.level8_desc.delete;
224 c_dim_data_rec.level8_attribute1.delete;
225 c_dim_data_rec.level8_attribute2.delete;
226 c_dim_data_rec.level8_attribute3.delete;
227 c_dim_data_rec.level8_attribute4.delete;
228 c_dim_data_rec.level8_attribute5.delete;
229 c_dim_data_rec.check_date.delete;
230 else
231 c_dim_data_rec.level1_value.delete(l_ctr);
232 c_dim_data_rec.level1_desc.delete(l_ctr);
233 c_dim_data_rec.level1_attribute1.delete(l_ctr);
234 c_dim_data_rec.level1_attribute2.delete(l_ctr);
235 c_dim_data_rec.level1_attribute3.delete(l_ctr);
236 c_dim_data_rec.level1_attribute4.delete(l_ctr);
237 c_dim_data_rec.level1_attribute5.delete(l_ctr);
238 c_dim_data_rec.level2_value.delete(l_ctr);
239 c_dim_data_rec.level2_desc.delete(l_ctr);
240 c_dim_data_rec.level2_attribute1.delete(l_ctr);
241 c_dim_data_rec.level2_attribute2.delete(l_ctr);
242 c_dim_data_rec.level2_attribute3.delete(l_ctr);
243 c_dim_data_rec.level2_attribute4.delete(l_ctr);
244 c_dim_data_rec.level2_attribute5.delete(l_ctr);
245 c_dim_data_rec.level3_value.delete(l_ctr);
246 c_dim_data_rec.level3_desc.delete(l_ctr);
247 c_dim_data_rec.level3_attribute1.delete(l_ctr);
248 c_dim_data_rec.level3_attribute2.delete(l_ctr);
249 c_dim_data_rec.level3_attribute3.delete(l_ctr);
250 c_dim_data_rec.level3_attribute4.delete(l_ctr);
251 c_dim_data_rec.level3_attribute5.delete(l_ctr);
252 c_dim_data_rec.level4_value.delete(l_ctr);
253 c_dim_data_rec.level4_desc.delete(l_ctr);
254 c_dim_data_rec.level4_attribute1.delete(l_ctr);
255 c_dim_data_rec.level4_attribute2.delete(l_ctr);
256 c_dim_data_rec.level4_attribute3.delete(l_ctr);
257 c_dim_data_rec.level4_attribute4.delete(l_ctr);
258 c_dim_data_rec.level4_attribute5.delete(l_ctr);
259 c_dim_data_rec.level5_value.delete(l_ctr);
260 c_dim_data_rec.level5_desc.delete(l_ctr);
261 c_dim_data_rec.level5_attribute1.delete(l_ctr);
262 c_dim_data_rec.level5_attribute2.delete(l_ctr);
263 c_dim_data_rec.level5_attribute3.delete(l_ctr);
264 c_dim_data_rec.level5_attribute4.delete(l_ctr);
265 c_dim_data_rec.level5_attribute5.delete(l_ctr);
266 c_dim_data_rec.level6_value.delete(l_ctr);
267 c_dim_data_rec.level6_desc.delete(l_ctr);
268 c_dim_data_rec.level6_attribute1.delete(l_ctr);
269 c_dim_data_rec.level6_attribute2.delete(l_ctr);
270 c_dim_data_rec.level6_attribute3.delete(l_ctr);
271 c_dim_data_rec.level6_attribute4.delete(l_ctr);
272 c_dim_data_rec.level6_attribute5.delete(l_ctr);
273 c_dim_data_rec.level7_value.delete(l_ctr);
274 c_dim_data_rec.level7_desc.delete(l_ctr);
275 c_dim_data_rec.level7_attribute1.delete(l_ctr);
276 c_dim_data_rec.level7_attribute2.delete(l_ctr);
277 c_dim_data_rec.level7_attribute3.delete(l_ctr);
278 c_dim_data_rec.level7_attribute4.delete(l_ctr);
279 c_dim_data_rec.level7_attribute5.delete(l_ctr);
280 c_dim_data_rec.level8_value.delete(l_ctr);
281 c_dim_data_rec.level8_desc.delete(l_ctr);
282 c_dim_data_rec.level8_attribute1.delete(l_ctr);
283 c_dim_data_rec.level8_attribute2.delete(l_ctr);
284 c_dim_data_rec.level8_attribute3.delete(l_ctr);
285 c_dim_data_rec.level8_attribute4.delete(l_ctr);
286 c_dim_data_rec.level8_attribute5.delete(l_ctr);
287 c_dim_data_rec.check_date.delete(l_ctr);
288 end if;
289 end delete_arrays;
290
291
292 Begin
293 fnd_file.put_line(fnd_file.log, 'Dimension: '||p_dim_code);
294 fnd_file.put_line(fnd_file.log, 'Hierarchy: '||p_hier_code);
295
296 l_source_view_name := null;
297 l_sql := 'select ';
298 l_count := 1;
299
300 while l_count <= 8
301 loop
302
303 select count(nvl(user_value_column, value_column)) into l_level_defined
304 from qpr_dim_sources
305 where instance_id = l_dim_src_instance_id
306 and instance_type = l_inst_type
307 and dim_code = p_dim_code
308 and hier_code = p_hier_code
309 and nvl(user_level_number, level_number) = l_count;
310
311 if (l_level_defined <> 0)
312 then
313
314 select count(nvl(user_attr_column, attr_column)) into l_attr_defined
315 from qpr_dim_sources
316 where instance_id = l_dim_src_instance_id
317 and instance_type = l_inst_type
318 and dim_code = p_dim_code
319 and hier_code = p_hier_code
320 and nvl(user_level_number, level_number) = l_count;
321
322 if (l_attr_defined <> 0)
323 then
324 l_attr_num := 1;
325 while (l_attr_num <= l_attr_defined)
326 loop
327 select
328 nvl(user_value_column, value_column),
329 nvl(user_desc_column, desc_column),
330 nvl(user_attr_column, attr_column)
331 into
332 l_value_column,
333 l_desc_column,
334 l_attr_column
335 from qpr_dim_sources
336 where instance_id = l_dim_src_instance_id
337 and instance_type = l_inst_type
338 and dim_code = p_dim_code
339 and hier_code = p_hier_code
340 and nvl(user_level_number, level_number) = l_count
341 and nvl(user_attr_number, attr_number) = l_attr_num;
342
343 if (l_attr_num = 1)
344 then
345 l_sql := l_sql || l_value_column || ' level';
346 l_sql := l_sql || l_count || '_value, ';
347
348 l_sql := l_sql || l_desc_column || ' level';
349 l_sql := l_sql || l_count || '_desc, ';
350 end if;
351
352 l_sql := l_sql || l_attr_column || ' level';
353 l_sql := l_sql || l_count || '_attribute';
354 l_sql := l_sql || l_attr_num;
355 if (l_count <> 8 or l_attr_num <> 5)
356 then
357 l_sql := l_sql || ', ';
358 end if;
359
360 l_attr_num := l_attr_num + 1;
361
362 end loop;
363
364 while l_attr_num <= 5
365 loop
366 l_sql := l_sql || 'null level';
367 l_sql := l_sql || l_count || '_attribute';
368 l_sql := l_sql || l_attr_num;
369 if (l_count <> 8 or l_attr_num <> 5)
370 then
371 l_sql := l_sql || ', ';
372 end if;
373 l_attr_num := l_attr_num + 1;
374 end loop;
375
376 else
377 select
378 nvl(user_value_column, value_column),
379 nvl(user_desc_column, desc_column)
380 into
381 l_value_column,
382 l_desc_column
383 from qpr_dim_sources
384 where instance_id = l_dim_src_instance_id
385 and instance_type = l_inst_type
386 and dim_code = p_dim_code
387 and hier_code = p_hier_code
388 and nvl(user_level_number, level_number) = l_count;
389
390 l_sql := l_sql || l_value_column || ' level';
391 l_sql := l_sql || l_count || '_value, ';
392
393 l_sql := l_sql || l_desc_column || ' level';
394 l_sql := l_sql || l_count || '_desc, ';
395
396 l_attr_num := 1;
397 while l_attr_num <= 5
398 loop
399 l_sql := l_sql || 'null level';
400 l_sql := l_sql || l_count || '_attribute';
401 l_sql := l_sql || l_attr_num;
402 if (l_count <> 8 or l_attr_num <> 5)
403 then
404 l_sql := l_sql || ', ';
405 end if;
406 l_attr_num := l_attr_num + 1;
407 end loop;
408 end if;
409
410 else
411 l_sql := l_sql || 'null level';
412 l_sql := l_sql || l_count || '_value, ';
413 l_sql := l_sql || 'null level';
414 l_sql := l_sql || l_count || '_desc, ';
415 l_sql := l_sql || 'null level';
416 l_sql := l_sql || l_count || '_attribute1, ';
417 l_sql := l_sql || 'null level';
418 l_sql := l_sql || l_count || '_attribute2, ';
419 l_sql := l_sql || 'null level';
420 l_sql := l_sql || l_count || '_attribute3, ';
421 l_sql := l_sql || 'null level';
422 l_sql := l_sql || l_count || '_attribute4, ';
423 l_sql := l_sql || 'null level';
424 l_sql := l_sql || l_count || '_attribute5 ';
425
426 if (l_count <> 8)
427 then
428 l_sql := l_sql || ', ';
429 end if;
430
431
432 end if;
433
434 l_count := l_count + 1;
435
436 end loop;
437 begin
438 l_value_column:=null;
439 select
440 nvl(user_value_column, value_column)
441 into
442 l_value_column
443 from qpr_dim_sources
444 where instance_id = l_dim_src_instance_id
445 and instance_type = l_inst_type
446 and dim_code = p_dim_code
447 and hier_code = p_hier_code
448 and nvl(user_level_number, level_number) = 999;
449 exception
450 when others then null;
451 end;
452 if l_value_column is not null then
453 l_sql := l_sql||', '||l_value_column||' ';
454 else
455 l_sql := l_sql||', null ';
456 end if;
457
458 select distinct nvl(user_view_name, view_name)
459 into l_source_view_name
460 from qpr_dim_sources
461 where instance_id = l_dim_src_instance_id
462 and instance_type = l_inst_type
463 and dim_code = p_dim_code
464 and hier_code = p_hier_code;
465
466 if (l_source_view_name is not null)
467 then
468 l_sql := l_sql || ' from ' || l_source_view_name || qpr_sr_util.get_dblink(p_instance_id);
469 else
470 fnd_file.put_line(fnd_file.log, 'Source view name not specified for Dimension: ' || p_dim_code || ' and Hierrarchy: ' || p_hier_code);
471 return;
472 end if;
473
474 if (date_from is not null) or (date_to is not null) then
475 l_date_column:=null;
476 select nvl(user_value_column, value_column)
477 into l_date_column
478 from qpr_dim_sources
479 where instance_id = l_dim_src_instance_id
480 and instance_type = l_inst_type
481 and dim_code = p_dim_code
482 and hier_code = p_hier_code
483 and nvl(user_level_number, level_number) = 998
484 and rownum < 2;
485
486 l_sql := l_sql || ' where (' || l_date_column || ' is null) or (' ;
487 l_sql := l_sql || l_date_column || ' between nvl('''||date_from;
488 l_sql := l_sql || ''', ' || l_date_column || ') and nvl('''|| date_to;
489 l_sql := l_sql || ''', ' || l_date_column || ')) ';
490 -- level1_value is the alias name for leaf_level given during query formation
491 l_sql := l_sql || ' order by level1_value ';
492 else
493 l_sql := l_sql || ' order by level1_value ';
494 end if;
495
496 fnd_file.put_line(fnd_file.log, 'SQL: '||l_sql);
497
498 open c_dim_data for l_sql;
499 loop
500 fnd_file.put_line( fnd_file.log, 'Delete arrays ');
501 delete_arrays(null);
502 l_temp_value := '';
503 fetch c_dim_data bulk collect
504 into c_dim_data_rec.level1_value,
505 c_dim_data_rec.level1_desc,
506 c_dim_data_rec.level1_attribute1,
507 c_dim_data_rec.level1_attribute2,
508 c_dim_data_rec.level1_attribute3,
509 c_dim_data_rec.level1_attribute4,
510 c_dim_data_rec.level1_attribute5,
511 c_dim_data_rec.level2_value,
512 c_dim_data_rec.level2_desc,
513 c_dim_data_rec.level2_attribute1,
514 c_dim_data_rec.level2_attribute2,
515 c_dim_data_rec.level2_attribute3,
516 c_dim_data_rec.level2_attribute4,
517 c_dim_data_rec.level2_attribute5,
518 c_dim_data_rec.level3_value,
519 c_dim_data_rec.level3_desc,
520 c_dim_data_rec.level3_attribute1,
521 c_dim_data_rec.level3_attribute2,
522 c_dim_data_rec.level3_attribute3,
523 c_dim_data_rec.level3_attribute4,
524 c_dim_data_rec.level3_attribute5,
525 c_dim_data_rec.level4_value,
526 c_dim_data_rec.level4_desc,
527 c_dim_data_rec.level4_attribute1,
528 c_dim_data_rec.level4_attribute2,
529 c_dim_data_rec.level4_attribute3,
530 c_dim_data_rec.level4_attribute4,
531 c_dim_data_rec.level4_attribute5,
532 c_dim_data_rec.level5_value,
533 c_dim_data_rec.level5_desc,
534 c_dim_data_rec.level5_attribute1,
535 c_dim_data_rec.level5_attribute2,
536 c_dim_data_rec.level5_attribute3,
537 c_dim_data_rec.level5_attribute4,
538 c_dim_data_rec.level5_attribute5,
539 c_dim_data_rec.level6_value,
540 c_dim_data_rec.level6_desc,
541 c_dim_data_rec.level6_attribute1,
542 c_dim_data_rec.level6_attribute2,
543 c_dim_data_rec.level6_attribute3,
544 c_dim_data_rec.level6_attribute4,
545 c_dim_data_rec.level6_attribute5,
546 c_dim_data_rec.level7_value,
547 c_dim_data_rec.level7_desc,
548 c_dim_data_rec.level7_attribute1,
549 c_dim_data_rec.level7_attribute2,
550 c_dim_data_rec.level7_attribute3,
551 c_dim_data_rec.level7_attribute4,
552 c_dim_data_rec.level7_attribute5,
553 c_dim_data_rec.level8_value,
554 c_dim_data_rec.level8_desc,
555 c_dim_data_rec.level8_attribute1,
556 c_dim_data_rec.level8_attribute2,
557 c_dim_data_rec.level8_attribute3,
558 c_dim_data_rec.level8_attribute4,
559 c_dim_data_rec.level8_attribute5,
560 c_dim_data_rec.check_date
561 limit l_rows;
562
563 exit when c_dim_data_rec.level1_value.count=0;
564
565 fnd_file.put_line( fnd_file.log, 'Populated arrays for dim: '||p_dim_code||' and hier: '||p_hier_code);
566 fnd_file.put_line(fnd_file.log, 'Record read count:' || c_dim_data_rec.level1_value.count);
567 -- looping thro to eliminate duplicate level1_values
568 for i in 1..c_dim_data_rec.level1_value.count loop
569 if nvl(l_temp_value,'*') <> c_dim_data_rec.level1_value(i) then
570 l_temp_value := c_dim_data_rec.level1_value(i);
571 else
572 --delete that index
573 delete_arrays(i);
574 end if;
575 end loop;
576 fnd_file.put_line(fnd_file.log,
577 'Record count after removing duplicate values in read set:' || c_dim_data_rec.level1_value.count);
578
579 insert_dimension_data(p_instance_id, p_dim_code, p_hier_code, c_dim_data_rec);
580
581 end loop;
582
583 close c_dim_data;
584
585 End; -- procedure get_data_from_cursor
586
587 procedure insert_dimension_data(
588 p_instance_id in number,
589 p_dim_code in varchar2,
590 p_hier_code in varchar2,
591 c_dim_data_rec in out nocopy QPR_LOAD_DIM_DATA.DIM_DATA_REC_TYPE)
592 is
593
594 l_request_id number;
595
596 Begin
597 fnd_file.put_line(fnd_file.log,
598 'Deleting duplicate values in table data ');
599
600 fnd_profile.get('CONC_REQUEST_ID', l_request_id);
601
602 -- when looping thro use 'indices of' since this can be a sparse collection
603 -- after deleting duplicate values
604 forall I in indices of c_dim_data_rec.level1_value
605 delete from QPR_DIMENSION_VALUES
606 where dim_code = p_dim_code
607 and hierarchy_code = p_hier_code
608 and instance_id = p_instance_id
609 and level1_value = c_dim_data_rec.level1_value(I);
610
611 fnd_file.put_line(fnd_file.log,
612 'Deleted record count:' || sql%rowcount);
613
614 fnd_file.put_line(fnd_file.log,'Inserting dimension data for: '||p_dim_code||'_'||p_hier_code);
615
616 forall i in indices of c_dim_data_rec.level1_value
617 INSERT INTO QPR_DIMENSION_VALUES
618 (dim_value_id,
619 instance_id,
620 dim_code,
621 hierarchy_code,
622 level1_value,
623 level1_desc,
624 level1_attribute1,
625 level1_attribute2,
626 level1_attribute3,
627 level1_attribute4,
628 level1_attribute5,
629 level2_value,
630 level2_desc,
631 level2_attribute1,
632 level2_attribute2,
633 level2_attribute3,
634 level2_attribute4,
635 level2_attribute5,
636 level3_value,
637 level3_desc,
638 level3_attribute1,
639 level3_attribute2,
640 level3_attribute3,
641 level3_attribute4,
642 level3_attribute5,
643 level4_value,
644 level4_desc,
645 level4_attribute1,
646 level4_attribute2,
647 level4_attribute3,
648 level4_attribute4,
649 level4_attribute5,
650 level5_value,
651 level5_desc,
652 level5_attribute1,
653 level5_attribute2,
654 level5_attribute3,
655 level5_attribute4,
656 level5_attribute5,
657 level6_value,
658 level6_desc,
659 level6_attribute1,
660 level6_attribute2,
661 level6_attribute3,
662 level6_attribute4,
663 level6_attribute5,
664 level7_value,
665 level7_desc,
666 level7_attribute1,
667 level7_attribute2,
668 level7_attribute3,
669 level7_attribute4,
670 level7_attribute5,
671 level8_value,
672 level8_desc,
673 level8_attribute1,
674 level8_attribute2,
675 level8_attribute3,
676 level8_attribute4,
677 level8_attribute5,
678 check_date,
679 creation_date,
680 created_by,
681 last_update_date,
682 last_updated_by,
683 last_update_login,
684 program_application_id,
685 program_id,
686 program_login_id,
687 request_id)
688 values
689 (QPR_DIMENSION_VALUES_S.nextval,
690 p_instance_id,
691 p_dim_code,
692 p_hier_code,
693 c_dim_data_rec.level1_value(I),
694 c_dim_data_rec.level1_desc(I),
695 c_dim_data_rec.level1_attribute1(I),
696 c_dim_data_rec.level1_attribute2(I),
697 c_dim_data_rec.level1_attribute3(I),
698 c_dim_data_rec.level1_attribute4(I),
699 c_dim_data_rec.level1_attribute5(I),
700 c_dim_data_rec.level2_value(I),
701 c_dim_data_rec.level2_desc(I),
702 c_dim_data_rec.level2_attribute1(I),
703 c_dim_data_rec.level2_attribute2(I),
704 c_dim_data_rec.level2_attribute3(I),
705 c_dim_data_rec.level2_attribute4(I),
706 c_dim_data_rec.level2_attribute5(I),
707 c_dim_data_rec.level3_value(I),
708 c_dim_data_rec.level3_desc(I),
709 c_dim_data_rec.level3_attribute1(I),
710 c_dim_data_rec.level3_attribute2(I),
711 c_dim_data_rec.level3_attribute3(I),
712 c_dim_data_rec.level3_attribute4(I),
713 c_dim_data_rec.level3_attribute5(I),
714 c_dim_data_rec.level4_value(I),
715 c_dim_data_rec.level4_desc(I),
716 c_dim_data_rec.level4_attribute1(I),
717 c_dim_data_rec.level4_attribute2(I),
718 c_dim_data_rec.level4_attribute3(I),
719 c_dim_data_rec.level4_attribute4(I),
720 c_dim_data_rec.level4_attribute5(I),
721 c_dim_data_rec.level5_value(I),
722 c_dim_data_rec.level5_desc(I),
723 c_dim_data_rec.level5_attribute1(I),
724 c_dim_data_rec.level5_attribute2(I),
725 c_dim_data_rec.level5_attribute3(I),
726 c_dim_data_rec.level5_attribute4(I),
727 c_dim_data_rec.level5_attribute5(I),
728 c_dim_data_rec.level6_value(I),
729 c_dim_data_rec.level6_desc(I),
730 c_dim_data_rec.level6_attribute1(I),
731 c_dim_data_rec.level6_attribute2(I),
732 c_dim_data_rec.level6_attribute3(I),
733 c_dim_data_rec.level6_attribute4(I),
734 c_dim_data_rec.level6_attribute5(I),
735 c_dim_data_rec.level7_value(I),
736 c_dim_data_rec.level7_desc(I),
737 c_dim_data_rec.level7_attribute1(I),
738 c_dim_data_rec.level7_attribute2(I),
739 c_dim_data_rec.level7_attribute3(I),
740 c_dim_data_rec.level7_attribute4(I),
741 c_dim_data_rec.level7_attribute5(I),
742 c_dim_data_rec.level8_value(I),
743 c_dim_data_rec.level8_desc(I),
744 c_dim_data_rec.level8_attribute1(I),
745 c_dim_data_rec.level8_attribute2(I),
746 c_dim_data_rec.level8_attribute3(I),
747 c_dim_data_rec.level8_attribute4(I),
748 c_dim_data_rec.level8_attribute5(I),
749 c_dim_data_rec.check_date(I),
750 sysdate,
751 fnd_global.user_id,
752 sysdate,
753 fnd_global.user_id,
754 fnd_global.conc_login_id,
755 fnd_global.prog_appl_id,
756 fnd_global.conc_program_id,
757 null,
758 l_request_id);
759 fnd_file.put_line(fnd_file.log, 'No of rows processed: '||sql%rowcount);
760
761 commit;
762
763 EXCEPTION
764 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
765 fnd_file.put_line(fnd_file.log, 'UNEXCPECTED ERROR IN INSERT_DIMENSION_DATA:'||sqlerrm);
766 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
767 WHEN OTHERS THEN
768 fnd_file.put_line(fnd_file.log, 'UNEXCPECTED ERROR IN INSERT_DIMENSION_DATA:'||sqlerrm);
769 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
770 End;
771
772 End;
773