[Home] [Help]
PACKAGE BODY: APPS.QPR_LOAD_DIM_DATA
Source
1 PACKAGE BODY QPR_LOAD_DIM_DATA AS
2 /* $Header: QPRUDLDB.pls 120.1 2007/12/06 11:28:21 kdhabali noship $ */
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 procedure get_data_from_cursor(
142 l_dim_src_instance_id in number,
143 p_dim_code in varchar2,
144 p_hier_code in varchar2,
145 p_instance_id in number,
146 date_from in date,
147 date_to in date,
148 l_inst_type in varchar2)
149 is
150
151 c_dim_data QPRDIMDATA;
152 c_dim_data_rec DIM_DATA_REC_TYPE;
153
154 l_rows natural := 1000;
155 l_sql varchar2(4000);
156
157 l_count number;
158 l_attr_num number;
159 l_level_defined number;
160 l_attr_defined number;
161 l_source_view_name varchar2(30);
162 l_desc_column varchar2(30);
163 l_value_column varchar2(30);
164 l_attr_column varchar2(30);
165 l_level_number number;
166 l_hierarchy_level_id number;
167 l_date_column varchar2(30);
168
169 Begin
170 fnd_file.put_line(fnd_file.log, 'Dimension: '||p_dim_code);
171 fnd_file.put_line(fnd_file.log, 'Hierarchy: '||p_hier_code);
172
173 l_source_view_name := null;
174 l_sql := 'select ';
175 l_count := 1;
176
177 while l_count <= 8
178 loop
179
180 select count(nvl(user_value_column, value_column)) into l_level_defined
181 from qpr_dim_sources
182 where instance_id = l_dim_src_instance_id
183 and instance_type = l_inst_type
184 and dim_code = p_dim_code
185 and hier_code = p_hier_code
186 and nvl(user_level_number, level_number) = l_count;
187
188 if (l_level_defined <> 0)
189 then
190
191 select count(nvl(user_attr_column, attr_column)) into l_attr_defined
192 from qpr_dim_sources
193 where instance_id = l_dim_src_instance_id
194 and instance_type = l_inst_type
195 and dim_code = p_dim_code
196 and hier_code = p_hier_code
197 and nvl(user_level_number, level_number) = l_count;
198
199 if (l_attr_defined <> 0)
200 then
201 l_attr_num := 1;
202 while (l_attr_num <= l_attr_defined)
203 loop
204 select
205 nvl(user_value_column, value_column),
206 nvl(user_desc_column, desc_column),
207 nvl(user_attr_column, attr_column)
208 into
209 l_value_column,
210 l_desc_column,
211 l_attr_column
212 from qpr_dim_sources
213 where instance_id = l_dim_src_instance_id
214 and instance_type = l_inst_type
215 and dim_code = p_dim_code
216 and hier_code = p_hier_code
217 and nvl(user_level_number, level_number) = l_count
218 and nvl(user_attr_number, attr_number) = l_attr_num;
219
220 if (l_attr_num = 1)
221 then
222 l_sql := l_sql || l_value_column || ' level';
223 l_sql := l_sql || l_count || '_value, ';
224
225 l_sql := l_sql || l_desc_column || ' level';
226 l_sql := l_sql || l_count || '_desc, ';
227 end if;
228
229 l_sql := l_sql || l_attr_column || ' level';
230 l_sql := l_sql || l_count || '_attribute';
231 l_sql := l_sql || l_attr_num;
232 if (l_count <> 8 or l_attr_num <> 5)
233 then
234 l_sql := l_sql || ', ';
235 end if;
236
237 l_attr_num := l_attr_num + 1;
238
239 end loop;
240
241 while l_attr_num <= 5
242 loop
243 l_sql := l_sql || 'null level';
244 l_sql := l_sql || l_count || '_attribute';
245 l_sql := l_sql || l_attr_num;
246 if (l_count <> 8 or l_attr_num <> 5)
247 then
248 l_sql := l_sql || ', ';
249 end if;
250 l_attr_num := l_attr_num + 1;
251 end loop;
252
253 else
254 select
255 nvl(user_value_column, value_column),
256 nvl(user_desc_column, desc_column)
257 into
258 l_value_column,
259 l_desc_column
260 from qpr_dim_sources
261 where instance_id = l_dim_src_instance_id
262 and instance_type = l_inst_type
263 and dim_code = p_dim_code
264 and hier_code = p_hier_code
265 and nvl(user_level_number, level_number) = l_count;
266
267 l_sql := l_sql || l_value_column || ' level';
268 l_sql := l_sql || l_count || '_value, ';
269
270 l_sql := l_sql || l_desc_column || ' level';
271 l_sql := l_sql || l_count || '_desc, ';
272
273 l_attr_num := 1;
274 while l_attr_num <= 5
275 loop
276 l_sql := l_sql || 'null level';
277 l_sql := l_sql || l_count || '_attribute';
278 l_sql := l_sql || l_attr_num;
279 if (l_count <> 8 or l_attr_num <> 5)
280 then
281 l_sql := l_sql || ', ';
282 end if;
283 l_attr_num := l_attr_num + 1;
284 end loop;
285 end if;
286
287 else
288 l_sql := l_sql || 'null level';
289 l_sql := l_sql || l_count || '_value, ';
290 l_sql := l_sql || 'null level';
291 l_sql := l_sql || l_count || '_desc, ';
292 l_sql := l_sql || 'null level';
293 l_sql := l_sql || l_count || '_attribute1, ';
294 l_sql := l_sql || 'null level';
295 l_sql := l_sql || l_count || '_attribute2, ';
296 l_sql := l_sql || 'null level';
297 l_sql := l_sql || l_count || '_attribute3, ';
298 l_sql := l_sql || 'null level';
299 l_sql := l_sql || l_count || '_attribute4, ';
300 l_sql := l_sql || 'null level';
301 l_sql := l_sql || l_count || '_attribute5 ';
302
303 if (l_count <> 8)
304 then
305 l_sql := l_sql || ', ';
306 end if;
307
308
309 end if;
310
311 l_count := l_count + 1;
312
313 end loop;
314 begin
315 l_value_column:=null;
316 select
317 nvl(user_value_column, value_column)
318 into
319 l_value_column
320 from qpr_dim_sources
321 where instance_id = l_dim_src_instance_id
322 and instance_type = l_inst_type
323 and dim_code = p_dim_code
324 and hier_code = p_hier_code
325 and nvl(user_level_number, level_number) = 999;
326 exception
327 when others then null;
328 end;
329 if l_value_column is not null then
330 l_sql := l_sql||', '||l_value_column||' ';
331 else
332 l_sql := l_sql||', null ';
333 end if;
334
335 select distinct nvl(user_view_name, view_name)
336 into l_source_view_name
337 from qpr_dim_sources
338 where instance_id = l_dim_src_instance_id
339 and instance_type = l_inst_type
340 and dim_code = p_dim_code
341 and hier_code = p_hier_code;
342
343 if (l_source_view_name is not null)
344 then
345 l_sql := l_sql || ' from ' || l_source_view_name || qpr_sr_util.get_dblink(p_instance_id);
346 else
347 fnd_file.put_line(fnd_file.log, 'Source view name not specified for Dimension: ' || p_dim_code || ' and Hierrarchy: ' || p_hier_code);
348 return;
349 end if;
350
351 if (date_from is not null) or (date_to is not null) then
352 l_date_column:=null;
353 select nvl(user_value_column, value_column)
354 into l_date_column
355 from qpr_dim_sources
356 where instance_id = l_dim_src_instance_id
357 and instance_type = l_inst_type
358 and dim_code = p_dim_code
359 and hier_code = p_hier_code
360 and nvl(user_level_number, level_number) = 998
361 and rownum < 2;
362
363 l_sql := l_sql || ' where (' || l_date_column || ' is null) or (' ;
364 l_sql := l_sql || l_date_column || ' between nvl('''||date_from;
365 l_sql := l_sql || ''', ' || l_date_column || ') and nvl('''|| date_to;
366 l_sql := l_sql || ''', ' || l_date_column || ')) ';
367 end if;
368
369 fnd_file.put_line(fnd_file.log, 'SQL: '||l_sql);
370
371 open c_dim_data for l_sql;
372 loop
373 fnd_file.put_line( fnd_file.log, 'Delete arrays ');
374 c_dim_data_rec.level1_value.delete;
375 c_dim_data_rec.level1_desc.delete;
376 c_dim_data_rec.level1_attribute1.delete;
377 c_dim_data_rec.level1_attribute2.delete;
378 c_dim_data_rec.level1_attribute3.delete;
379 c_dim_data_rec.level1_attribute4.delete;
380 c_dim_data_rec.level1_attribute5.delete;
381 c_dim_data_rec.level2_value.delete;
382 c_dim_data_rec.level2_desc.delete;
383 c_dim_data_rec.level2_attribute1.delete;
384 c_dim_data_rec.level2_attribute2.delete;
385 c_dim_data_rec.level2_attribute3.delete;
386 c_dim_data_rec.level2_attribute4.delete;
387 c_dim_data_rec.level2_attribute5.delete;
388 c_dim_data_rec.level3_value.delete;
389 c_dim_data_rec.level3_desc.delete;
390 c_dim_data_rec.level3_attribute1.delete;
391 c_dim_data_rec.level3_attribute2.delete;
392 c_dim_data_rec.level3_attribute3.delete;
393 c_dim_data_rec.level3_attribute4.delete;
394 c_dim_data_rec.level3_attribute5.delete;
395 c_dim_data_rec.level4_value.delete;
396 c_dim_data_rec.level4_desc.delete;
397 c_dim_data_rec.level4_attribute1.delete;
398 c_dim_data_rec.level4_attribute2.delete;
399 c_dim_data_rec.level4_attribute3.delete;
400 c_dim_data_rec.level4_attribute4.delete;
401 c_dim_data_rec.level4_attribute5.delete;
402 c_dim_data_rec.level5_value.delete;
403 c_dim_data_rec.level5_desc.delete;
404 c_dim_data_rec.level5_attribute1.delete;
405 c_dim_data_rec.level5_attribute2.delete;
406 c_dim_data_rec.level5_attribute3.delete;
407 c_dim_data_rec.level5_attribute4.delete;
408 c_dim_data_rec.level5_attribute5.delete;
409 c_dim_data_rec.level6_value.delete;
410 c_dim_data_rec.level6_desc.delete;
411 c_dim_data_rec.level6_attribute1.delete;
412 c_dim_data_rec.level6_attribute2.delete;
413 c_dim_data_rec.level6_attribute3.delete;
414 c_dim_data_rec.level6_attribute4.delete;
415 c_dim_data_rec.level6_attribute5.delete;
416 c_dim_data_rec.level7_value.delete;
417 c_dim_data_rec.level7_desc.delete;
418 c_dim_data_rec.level7_attribute1.delete;
419 c_dim_data_rec.level7_attribute2.delete;
420 c_dim_data_rec.level7_attribute3.delete;
421 c_dim_data_rec.level7_attribute4.delete;
422 c_dim_data_rec.level7_attribute5.delete;
423 c_dim_data_rec.level8_value.delete;
424 c_dim_data_rec.level8_desc.delete;
425 c_dim_data_rec.level8_attribute1.delete;
426 c_dim_data_rec.level8_attribute2.delete;
427 c_dim_data_rec.level8_attribute3.delete;
428 c_dim_data_rec.level8_attribute4.delete;
429 c_dim_data_rec.level8_attribute5.delete;
430 c_dim_data_rec.check_date.delete;
431
432 fetch c_dim_data bulk collect
433 into c_dim_data_rec.level1_value,
434 c_dim_data_rec.level1_desc,
435 c_dim_data_rec.level1_attribute1,
436 c_dim_data_rec.level1_attribute2,
437 c_dim_data_rec.level1_attribute3,
438 c_dim_data_rec.level1_attribute4,
439 c_dim_data_rec.level1_attribute5,
440 c_dim_data_rec.level2_value,
441 c_dim_data_rec.level2_desc,
442 c_dim_data_rec.level2_attribute1,
443 c_dim_data_rec.level2_attribute2,
444 c_dim_data_rec.level2_attribute3,
445 c_dim_data_rec.level2_attribute4,
446 c_dim_data_rec.level2_attribute5,
447 c_dim_data_rec.level3_value,
448 c_dim_data_rec.level3_desc,
449 c_dim_data_rec.level3_attribute1,
450 c_dim_data_rec.level3_attribute2,
451 c_dim_data_rec.level3_attribute3,
452 c_dim_data_rec.level3_attribute4,
453 c_dim_data_rec.level3_attribute5,
454 c_dim_data_rec.level4_value,
455 c_dim_data_rec.level4_desc,
456 c_dim_data_rec.level4_attribute1,
457 c_dim_data_rec.level4_attribute2,
458 c_dim_data_rec.level4_attribute3,
459 c_dim_data_rec.level4_attribute4,
460 c_dim_data_rec.level4_attribute5,
461 c_dim_data_rec.level5_value,
462 c_dim_data_rec.level5_desc,
463 c_dim_data_rec.level5_attribute1,
464 c_dim_data_rec.level5_attribute2,
468 c_dim_data_rec.level6_value,
465 c_dim_data_rec.level5_attribute3,
466 c_dim_data_rec.level5_attribute4,
467 c_dim_data_rec.level5_attribute5,
469 c_dim_data_rec.level6_desc,
470 c_dim_data_rec.level6_attribute1,
471 c_dim_data_rec.level6_attribute2,
472 c_dim_data_rec.level6_attribute3,
473 c_dim_data_rec.level6_attribute4,
474 c_dim_data_rec.level6_attribute5,
475 c_dim_data_rec.level7_value,
476 c_dim_data_rec.level7_desc,
477 c_dim_data_rec.level7_attribute1,
478 c_dim_data_rec.level7_attribute2,
479 c_dim_data_rec.level7_attribute3,
480 c_dim_data_rec.level7_attribute4,
481 c_dim_data_rec.level7_attribute5,
482 c_dim_data_rec.level8_value,
483 c_dim_data_rec.level8_desc,
484 c_dim_data_rec.level8_attribute1,
485 c_dim_data_rec.level8_attribute2,
486 c_dim_data_rec.level8_attribute3,
487 c_dim_data_rec.level8_attribute4,
488 c_dim_data_rec.level8_attribute5,
489 c_dim_data_rec.check_date
490 limit l_rows;
491
492 fnd_file.put_line( fnd_file.log, 'Populated arrays for dim: '||p_dim_code||' and hier: '||p_hier_code);
493 insert_dimension_data(p_instance_id, p_dim_code, p_hier_code, c_dim_data_rec);
494
495 exit when c_dim_data%NOTFOUND;
496
497 end loop;
498
499 close c_dim_data;
500
501 End; -- procedure get_data_from_cursor
502
503 procedure insert_dimension_data(
504 p_instance_id in number,
505 p_dim_code in varchar2,
506 p_hier_code in varchar2,
507 c_dim_data_rec in out nocopy QPR_LOAD_DIM_DATA.DIM_DATA_REC_TYPE)
508 is
509
510 l_request_id number;
511
512 Begin
513 fnd_file.put_line(fnd_file.log,'Entering insert dimension data ');
514 fnd_file.put_line(fnd_file.log,'Inserting dimension data for: '||p_dim_code||'_'||p_hier_code);
515
516 -- fnd_file.put_line(fnd_file.log,'Count '||c_dim_data_rec.day_sr_level_value_pk.count);
517 fnd_profile.get('CONC_REQUEST_ID', l_request_id);
518
519 forall I in
520 1..c_dim_data_rec.level1_value.count
521 delete from QPR_DIMENSION_VALUES
522 where dim_code = p_dim_code
523 and hierarchy_code = p_hier_code
524 and instance_id = p_instance_id
525 and level1_value = c_dim_data_rec.level1_value(I);
526
527 FORALL I IN
528 1..c_dim_data_rec.level1_value.count
529 INSERT INTO QPR_DIMENSION_VALUES
530 (dim_value_id,
531 instance_id,
532 dim_code,
533 hierarchy_code,
534 level1_value,
535 level1_desc,
536 level1_attribute1,
537 level1_attribute2,
538 level1_attribute3,
539 level1_attribute4,
540 level1_attribute5,
541 level2_value,
542 level2_desc,
543 level2_attribute1,
544 level2_attribute2,
545 level2_attribute3,
546 level2_attribute4,
547 level2_attribute5,
548 level3_value,
549 level3_desc,
550 level3_attribute1,
551 level3_attribute2,
552 level3_attribute3,
553 level3_attribute4,
554 level3_attribute5,
555 level4_value,
556 level4_desc,
557 level4_attribute1,
558 level4_attribute2,
559 level4_attribute3,
560 level4_attribute4,
561 level4_attribute5,
562 level5_value,
563 level5_desc,
564 level5_attribute1,
565 level5_attribute2,
566 level5_attribute3,
567 level5_attribute4,
568 level5_attribute5,
569 level6_value,
570 level6_desc,
571 level6_attribute1,
572 level6_attribute2,
573 level6_attribute3,
574 level6_attribute4,
575 level6_attribute5,
576 level7_value,
577 level7_desc,
578 level7_attribute1,
579 level7_attribute2,
580 level7_attribute3,
581 level7_attribute4,
582 level7_attribute5,
583 level8_value,
584 level8_desc,
585 level8_attribute1,
586 level8_attribute2,
587 level8_attribute3,
588 level8_attribute4,
589 level8_attribute5,
590 check_date,
591 creation_date,
592 created_by,
593 last_update_date,
594 last_updated_by,
595 last_update_login,
596 program_application_id,
597 program_id,
598 program_login_id,
599 request_id)
600 values
601 (QPR_DIMENSION_VALUES_S.nextval,
602 p_instance_id,
603 p_dim_code,
604 p_hier_code,
605 c_dim_data_rec.level1_value(I),
606 c_dim_data_rec.level1_desc(I),
607 c_dim_data_rec.level1_attribute1(I),
608 c_dim_data_rec.level1_attribute2(I),
609 c_dim_data_rec.level1_attribute3(I),
610 c_dim_data_rec.level1_attribute4(I),
611 c_dim_data_rec.level1_attribute5(I),
612 c_dim_data_rec.level2_value(I),
613 c_dim_data_rec.level2_desc(I),
614 c_dim_data_rec.level2_attribute1(I),
615 c_dim_data_rec.level2_attribute2(I),
616 c_dim_data_rec.level2_attribute3(I),
617 c_dim_data_rec.level2_attribute4(I),
618 c_dim_data_rec.level2_attribute5(I),
619 c_dim_data_rec.level3_value(I),
620 c_dim_data_rec.level3_desc(I),
621 c_dim_data_rec.level3_attribute1(I),
622 c_dim_data_rec.level3_attribute2(I),
623 c_dim_data_rec.level3_attribute3(I),
624 c_dim_data_rec.level3_attribute4(I),
625 c_dim_data_rec.level3_attribute5(I),
626 c_dim_data_rec.level4_value(I),
627 c_dim_data_rec.level4_desc(I),
628 c_dim_data_rec.level4_attribute1(I),
629 c_dim_data_rec.level4_attribute2(I),
630 c_dim_data_rec.level4_attribute3(I),
631 c_dim_data_rec.level4_attribute4(I),
632 c_dim_data_rec.level4_attribute5(I),
633 c_dim_data_rec.level5_value(I),
634 c_dim_data_rec.level5_desc(I),
635 c_dim_data_rec.level5_attribute1(I),
636 c_dim_data_rec.level5_attribute2(I),
637 c_dim_data_rec.level5_attribute3(I),
638 c_dim_data_rec.level5_attribute4(I),
639 c_dim_data_rec.level5_attribute5(I),
640 c_dim_data_rec.level6_value(I),
641 c_dim_data_rec.level6_desc(I),
642 c_dim_data_rec.level6_attribute1(I),
643 c_dim_data_rec.level6_attribute2(I),
644 c_dim_data_rec.level6_attribute3(I),
645 c_dim_data_rec.level6_attribute4(I),
646 c_dim_data_rec.level6_attribute5(I),
647 c_dim_data_rec.level7_value(I),
648 c_dim_data_rec.level7_desc(I),
649 c_dim_data_rec.level7_attribute1(I),
650 c_dim_data_rec.level7_attribute2(I),
651 c_dim_data_rec.level7_attribute3(I),
652 c_dim_data_rec.level7_attribute4(I),
653 c_dim_data_rec.level7_attribute5(I),
654 c_dim_data_rec.level8_value(I),
655 c_dim_data_rec.level8_desc(I),
656 c_dim_data_rec.level8_attribute1(I),
657 c_dim_data_rec.level8_attribute2(I),
658 c_dim_data_rec.level8_attribute3(I),
659 c_dim_data_rec.level8_attribute4(I),
660 c_dim_data_rec.level8_attribute5(I),
661 c_dim_data_rec.check_date(I),
662 sysdate,
663 fnd_global.user_id,
664 sysdate,
665 fnd_global.user_id,
666 fnd_global.conc_login_id,
667 fnd_global.prog_appl_id,
668 fnd_global.conc_program_id,
669 null,
670 l_request_id);
671 fnd_file.put_line(fnd_file.log, 'No of rows processed: '||sql%rowcount);
672
673 commit;
674
675 EXCEPTION
676 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
677 fnd_file.put_line(fnd_file.log, 'UNEXCPECTED ERROR IN INSERT_DIMENSION_DATA:'||sqlerrm);
678 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
679 WHEN OTHERS THEN
680 fnd_file.put_line(fnd_file.log, 'UNEXCPECTED ERROR IN INSERT_DIMENSION_DATA:'||sqlerrm);
681 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
682 End;
683
684 End;
685