[Home] [Help]
PACKAGE BODY: APPS.EDW_METADATA_REFRESH
Source
1 package body edw_metadata_refresh as
2 /* $Header: EDWMDRFB.pls 115.17 2004/04/06 16:41:34 vsurendr noship $*/
3
4 -- This procedure refreshes metadata tables from owb repository
5
6 PROCEDURE refresh_metadata_tables(Errbuf out nocopy varchar2, Retcode out nocopy varchar2) IS
7 l_stmt varchar2(20000);
8 l_errbuf varchar2(1000);
9 l_retcode varchar2(200);
10 l_var varchar2(200);
11 l_count number;
12 check_tspace_exist varchar(1);
13 check_ts_mode varchar(1);
14 physical_tspace_name varchar2(100);
15 BEGIN
16
17
18 -- check the repository is OWB9i or OWB211. If it is 211, do nothing.
19 -- if it is 9i, refresh the metadata tables.
20
21 select count(*) into l_count from user_objects
22 where object_name='ALL_IV_DIMENSIONS';
23
24 IF (l_count=0) THEN NULL;
25 ELSE
26 Errbuf := NULL;
27 Retcode := 0;
28 open_log_file;
29 g_owb_schema:=fnd_oracle_schema.getouvalue('OWB');
30 if g_owb_schema is null then
31 g_owb_schema:='EDWREP';
32 end if;
33 log('OWB Schema '||g_owb_schema);
34 log('system time is '|| fnd_date.date_to_displaydt (sysdate) );
35 g_bis_owner:=get_db_user('BIS');
36 log('Bis owner= '||g_bis_owner);
37 if g_bis_owner is null then
38 raise_application_error(-20000,errbuf);
39 return;
40 end if;
41 --log('system time is '|| fnd_date.date_to_displaydt (sysdate) );
42 --l_stmt:='begin OWM_VIEW_UTILITIES.AUTO_SET_PRIMARY_SOURCE(false); end;';
43 --log(l_stmt);
44 --execute immediate l_stmt;
45 --commit;
46 log('system time is '|| fnd_date.date_to_displaydt (sysdate) );
47
48 /*******Bug 3008332******/
49 --Getting the operational tablespace
50 g_op_table_space:=fnd_profile.value('EDW_OP_TABLE_SPACE');
51 if g_op_table_space is null then
52 AD_TSPACE_UTIL.is_new_ts_mode (check_ts_mode);
53 If check_ts_mode ='Y' then
54 AD_TSPACE_UTIL.get_tablespace_name ('BIS', 'INTERFACE','Y',check_tspace_exist, physical_tspace_name);
55 if check_tspace_exist='Y' and physical_tspace_name is not null then
56 g_op_table_space := physical_tspace_name;
57 end if;
58 end if;
59 end if;
60 if g_op_table_space is null then
61 g_op_table_space:=EDW_OWB_COLLECTION_UTIL.get_table_space(g_bis_owner);
62 end if;
63 log('Operational tablespance is : ' || g_op_table_space);
64 /*******/
65
66 log('going to call refresh_owb_mv to refresh materialized views');
67 if refresh_owb_mv=false then
68 raise_application_error(-20000,errbuf);
69 return;
70 end if;
71 log('going to truncate all metadata tables'||get_time);
72 if truncate_all=false then
73 raise_application_error(-20000,errbuf);
74 return;
75 end if;
76 ---------populate atomic tables ---------------------------------------
77 /**For ***Bug 3008332****/
78 l_stmt:= 'ALTER SESSION disable parallel query';
79 log( 'going to execute '||l_stmt);
80 execute immediate l_stmt;
81 log('Session altered to : DISABLED PARALLEL QUERY');
82 /****************************************************/
83
84 log('system time is '|| fnd_date.date_to_displaydt (sysdate) );
85 log('going to execute following sql statement ');
86 l_stmt:='insert into EDW_ALL_COLUMNS_MD (
87 ENTITY_ID,
88 ENTITY_TYPE,
89 ENTITY_NAME,
90 COLUMN_ID,
91 COLUMN_NAME,
92 BUSINESS_NAME,
93 DESCRIPTION,
94 POSITION,
95 DATA_TYPE,
96 LENGTH
97 )
98 select
99 ENTITY_ID,
100 ENTITY_TYPE,
101 ENTITY_NAME,
102 COLUMN_ID,
103 COLUMN_NAME,
104 BUSINESS_NAME,
105 DESCRIPTION,
106 POSITION,
107 DATA_TYPE,
108 LENGTH
109 FROM ALL_IV_COLUMNS
110 ';
111 log(l_stmt);
112 execute immediate l_stmt;
113 log('No of Rows inserted : '||sql%rowcount||' '||get_time);
114 commit;
115
116
117 log('going to execute following sql statement ');
118 l_stmt:='insert into EDW_ATTRIBUTE_SETS_MD
119 (ENTITY_ID,
120 ENTITY_TYPE,
121 ENTITY_NAME,
122 ATTRIBUTE_GROUP_NAME,
123 ATTRIBUTE_GROUP_ID,
124 DESCRIPTION )
125 SELECT
126 DATA_ENTITY_ID,
127 DATA_ENTITY_TYPE,
128 DATA_ENTITY_NAME,
129 ATTRIBUTE_GROUP_NAME,
130 ATTRIBUTE_GROUP_ID,
131 DESCRIPTION
132 FROM ALL_IV_ATTR_GROUPS
133 ';
134 log(l_stmt);
135 execute immediate l_stmt;
136 log('No of Rows inserted : '||sql%rowcount||' '||get_time);
137 commit;
138
139 log('going to execute following sql statement ');
140 l_stmt:='insert into edw_dimensions_md(
141 dim_id,
142 dim_name,
143 dim_prefix,
144 dim_long_name,
145 dim_table_name,
146 dim_description
147 )
148 SELECT
149 DIM.dimension_id,
150 DIM.DIMENSION_NAME,
151 DIM.DIMENSION_PREFIX,
152 DIM.BUSINESS_NAME,
153 DIM.DIMENSION_NAME,
154 DIM.DESCRIPTION
155 FROM all_iv_dimensions DIM'
156 ;
157 log(l_stmt);
158 execute immediate l_stmt;
159 log('No of Rows inserted : '||sql%rowcount||' '||get_time);
160 commit;
161
162
163 log('going to execute following sql statement ');
164
165 l_stmt:='insert into edw_facts_md
166 (
167 fact_id,
168 fact_name,
169 fact_longname ,
170 fact_description)
171 SELECT
172 FACT.CUBE_ID,
173 FACT.CUBE_NAME,
174 FACT.BUSINESS_NAME,
175 FACT.DESCRIPTION
176 FROM
177 ALL_IV_CUBES FACT';
178 log(l_stmt);
179 execute immediate l_stmt;
180 log('No of Rows inserted : '||sql%rowcount||' '||get_time);
181 commit;
182
183
184 log('going to execute following sql statement ');
185 l_stmt:='insert into EDW_FOREIGN_KEYS_MD
186 (
187 entity_id,
188 entity_type,
189 entity_name,
190 foreign_key_name,
191 foreign_key_id,
192 business_name,
193 description,
194 key_id,
195 key_name
196 )
197 select
198 entity_id,
199 entity_type,
200 entity_name,
201 foreign_key_name,
202 foreign_key_id,
203 business_name,
204 description,
205 key_id,
206 key_name
207 from all_iv_foreign_keys'
208 ;
209 log(l_stmt);
210 execute immediate l_stmt;
211 log('No of Rows inserted : '||sql%rowcount||' '||get_time);
212 commit;
213
214
215 log('going to execute following sql statement ');
216 l_stmt:='insert into edw_hierarchies_md (
217 dim_id,
218 dim_name,
219 hier_id,
220 hier_name,
221 hier_prefix,
222 hier_long_name )
223 SELECT
224 dim.dimension_id dim_id,
225 dim.dimension_name dim_name,
226 hier.hierarchy_id hier_id,
227 hier.hierarchy_name hier_name,
228 hier.hierarchy_prefix hier_prefix,
229 hier.business_name hier_long_name
230 FROM
231 ALL_IV_DIMENSIONS dim, ALL_IV_DIM_HIERARCHIES hier
232 WHERE
233 hier.dimension_id = dim.dimension_id';
234 log(l_stmt);
235 execute immediate l_stmt;
236 log('No of Rows inserted : '||sql%rowcount||' '||get_time);
237 commit;
238
239
240 log('going to execute following sql statement ');
241 l_stmt:='insert into edw_relationmapping_md
242 (
243 sourcedataentity,
244 targetdataentity,
245 elementid,
246 parentmodel,
247 name
248 )
249 select
250 src_map.data_entity_id sourcedataentity,
251 tgt_map.data_entity_id targetdataentity,
252 src_map.map_id elementid,
253 src_map.map_id parentmodel,
254 tgt_map.map_name name
255 from
256 ALL_IV_XFORM_MAP_PRIM_SOURCEs src_map,
257 ALL_IV_XFORM_MAP_TARGETS tgt_map
258 where
259 src_map.map_id=tgt_map.map_id'
260 ;
261 log(l_stmt);
262 execute immediate l_stmt;
263 log('No of Rows inserted : '||sql%rowcount||' '||get_time);
264 commit;
265
266
267 log('going to execute following sql statement ');
268 l_stmt:='insert into EDW_RELATIONS_MD
269 (
270 relation_id,
271 relation_name,
272 relation_long_name,
273 description,
274 relation_type
275 )
276 select table_id, table_name, business_name, description, null
277 from all_iv_tables
278 where table_name not in (select dim_name from edw_dimensions_md union
279 select fact_name from edw_facts_md)
280 union all
281 select fact_id, fact_name, fact_longname, fact_description, ''CMPWBCube''
282 from edw_facts_md
283 union all
284 select dim_id, dim_name, dim_long_name, dim_description, ''CMPWBDimension''
285 from edw_dimensions_md
286 union all
287 select sequence_id, sequence_name, business_name, description, ''CMPWBSequence''
288 from all_iv_sequences
289 union all
290 select view_id, view_name, business_name, description, ''CMPWBView''
291 from all_iv_views';
292 log(l_stmt);
293 execute immediate l_stmt;
294 log('No of Rows inserted : '||sql%rowcount||' '||get_time);
295 commit;
296
297
298 log('going to execute following sql statement ');
299 l_stmt:='insert into EDW_UNIQUE_KEYS_MD
300 (entity_id,
301 entity_type,
302 entity_name,
303 Key_id,
304 Key_name,
305 Business_name,
306 Description,
307 primarykey
308 )
309 SELECT
310 entity_id,
311 entity_type,
312 entity_name,
313 Key_id,
314 Key_name,
315 Business_name,
316 Description,
317 decode(is_primary, ''Y'', 1, 0)
318 FROM all_iv_keys';
319 log(l_stmt);
320 execute immediate l_stmt;
321 log('No of Rows inserted : '||sql%rowcount||' '||get_time);
322 commit;
323
324
325 log('going to execute following sql statement ');
326 l_stmt:='insert into EDW_UNIQUE_KEY_COLUMNS_MD
327 (
328 Key_id,
329 Key_name,
330 column_id,
331 column_name
332 )
333 SELECT
334 Key_id,
335 key_name,
336 column_id,
337 column_name
338 FROM all_iv_key_column_uses';
339 log(l_stmt);
340 execute immediate l_stmt;
341 log('No of Rows inserted : '||sql%rowcount||' '||get_time);
342 commit;
343
344 ------------ populate pvt tables ---------------------------
345 if populate_pvt_tables=false then
346 raise_application_error(-20000,errbuf);
347 return;
348 end if;
349
350 ------------ populate secondary tables ---------------------------
351
352 log('system time is '|| fnd_date.date_to_displaydt (sysdate) );
353 log('going to execute following sql statement ');
354 l_stmt:='insert into edw_tables_md(
355 Elementid,
356 Name,
357 long_name)
358 SELECT
359 table_ID Elementid,
360 table_NAME Name,
361 business_name long_name
362 FROM
363 all_iv_tables TBL
364 WHERE
365 not exists( select dim_id from edw_dimensions_md where
366 dim_id = table_id) and
367 not exists(select fact_id from edw_facts_md where fact_id = table_id) '
368 ;
369 log(l_stmt);
370 execute immediate l_stmt;
371 log('No of Rows inserted : '||sql%rowcount||' '||get_time);
372 commit;
373
374 log('going to execute following sql statement ');
375 l_stmt:='insert into edw_levels_md (
376 level_id,
377 level_name,
378 level_prefix,
379 level_long_name,
380 level_table_id,
381 level_table_name,
382 description,
383 dim_id,
384 dim_name)
385 SELECT
386 LVL.LEVEL_ID,
387 LVL.LEVEL_NAME,
388 LVL.LEVEL_PREFIX ,
389 LVL.BUSINESS_NAME level_long_name,
390 tbl.relation_id level_table_id,
391 LVL.LEVEL_NAME||''_LTC'' level_table_name,
392 LVL.DESCRIPTION,
393 LVL.dimension_id dim_id,
394 LVL.dimension_name dim_name
395 FROM
396 all_iv_dim_levels lvl, EDW_RELATIONS_MD tbl
397 where
398 lvl.level_name ||''_LTC'' = tbl.relation_name (+)'
399 ;
400 log(l_stmt);
401 execute immediate l_stmt;
402 log('No of Rows inserted : '||sql%rowcount||' '||get_time);
403 commit;
404
405 log('going to execute following sql statement ');
406 l_stmt:='insert into EDW_ATTRIBUTE_SET_COLUMN_MD
407 (ENTITY_ID,
408 ENTITY_TYPE,
409 ENTITY_NAME,
410 ATTRIBUTE_GROUP_NAME,
411 ATTRIBUTE_GROUP_ID,
412 COLUMN_ID,
413 COLUMN_TYPE,
414 COLUMN_NAME
415 )
416 select
417 GRP.DATA_ENTITY_ID ENTITY_ID,
418 GRP.DATA_ENTITY_TYPE ENTITY_TYPE,
419 GRP.DATA_ENTITY_NAME ENTITY_NAME,
420 GRP.ATTRIBUTE_GROUP_NAME ATTRIBUTE_GROUP_NAME,
421 GRP.ATTRIBUTE_GROUP_ID ATTRIBUTE_GROUP_ID,
422 USES.DATA_ITEM_ID COLUMN_ID,
423 USES.DATA_ITEM_TYPE COLUMN_TYPE,
424 USES.DATA_ITEM_NAME COLUMN_NAME
425 from ALL_IV_ATTR_GROUPS grp,
426 ALL_IV_ATTR_GROUP_ITEM_USES uses
427 where
428 grp.attribute_group_id = uses.attribute_group_id ';
429 log(l_stmt);
430 execute immediate l_stmt;
431 log('No of Rows inserted : '||sql%rowcount||' '||get_time);
432 commit;
433
434
435 log('going to execute following sql statement ');
436 l_stmt:='insert into edw_dim_attributes_md (
437 dim_id,
438 dim_name,
439 attribute_id,
440 attribute_name,
441 attribute_longname,
442 attribute_source_level,
443 attribute_source_level_prefix,
444 uk_id,
445 uk_name )
446 select dim.dim_id dim_id,
447 dim.DIM_NAME dim_name,
448 lvlattr.column_id attribute_id,
449 lvlattr.COLUMN_NAME attribute_name,
450 lvlattr.business_name attribute_longname,
451 lvl.level_name attribute_source_level,
452 lvl.level_prefix attribute_source_level_prefix,
453 to_number(null) uk_id,
454 to_char(null) uk_name
455 from edw_dimensions_md dim, edw_levels_md lvl,
456 EDW_ALL_COLUMNS_MD lvlattr
457 where dim.dim_id = lvl.dim_id
458 and lvl.level_id = lvlattr.ENTITY_ID
459 union
460 select dim.dim_id dim_id,
461 dim.dim_name dim_name,
462 lvlattr.column_id attribute_id,
463 lvlattr.column_name attribute_name,
464 lvlattr.business_name attribute_longname,
465 lvl.level_name attribute_source_level,
466 lvl.level_prefix attribute_source_level_prefix,
467 keys.key_id uk_id,
468 keys.key_name uk_name
469 from edw_dimensions_md dim, edw_levels_md lvl,
470 EDW_ALL_COLUMNS_MD lvlattr,
471 EDW_UNIQUE_KEYS_MD keys,EDW_UNIQUE_KEY_COLUMNS_MD uses
472 where dim.dim_id = lvl.dim_id
473 and lvl.level_id = lvlattr.entity_id
474 and lvl.level_id = keys.entity_id
475 and keys.key_id = uses.key_id
476 and uses.column_id = lvlattr.column_id '
477 ;
478 log(l_stmt);
479 execute immediate l_stmt;
480 log('No of Rows inserted : '||sql%rowcount||' '||get_time);
481 commit;
482
483 log('going to execute following sql statement ');
484 l_stmt:='insert into edw_fact_attributes_md(
485 fact_id,
486 fact_name,
487 attribute_type,
488 attribute_id,
489 attribute_name,
490 attribute_longname,
491 key_type,
492 key_id,
493 key_name )
494 SELECT cube.fact_id,
495 cube.fact_name,
496 null,
497 col.column_id,
498 col.column_name,
499 col.business_name,
500 ''FK'',
501 keys.key_id,
502 keys.key_name
503 FROM edw_all_columns_md col,
504 edw_facts_md cube,
505 edw_foreign_keys_md keys,
506 edw_pvt_key_columns_md uses
507 where col.entity_id = cube.fact_id
508 and cube.fact_id = keys.entity_id
509 and keys.key_id = uses.key_id
510 and uses.column_id = col.column_id
511 UNION all
515 col.measure_id,
512 SELECT cube.fact_id,
513 cube.fact_name,
514 DECODE(allcols.DATA_TYPE, ''NUMBER'' , ''MEASURE'', null),
516 col.measure_name,
517 col.business_name,
518 to_char(null),
519 to_number(null),
520 to_char(null)
521 FROM ALL_IV_CUBE_MEASURES col, edw_facts_md cube,
522 edw_all_columns_md allcols
523 where col.cube_id = cube.fact_id
524 and allcols.entity_id = cube.fact_id
525 and allcols.column_id = col.measure_id'
526 ;
527 log(l_stmt);
528 execute immediate l_stmt;
529 log('No of Rows inserted : '||sql%rowcount||' '||get_time);
530 commit;
531
532 log('going to execute following sql statement ');
533 l_stmt:='insert into edw_fact_dim_relations_md (
534 fact_id,
535 fact_name,
536 fact_fk_id,
537 fact_fk_name,
538 fact_fk_col_id,
539 fact_fk_col_name,
540 dim_uk_col_id,
541 dim_uk_col_name,
542 dim_uk_id,
543 dim_uk_name,
544 dim_uk_long_name,
545 dim_id,
546 dim_name ,
547 fact_long_name)
548 select cube.cube_id,
549 cube.cube_name,
550 fk.foreign_key_id,
551 fk.foreign_key_name,
552 fkuses.column_id,
553 fkuses.column_name,
554 pkuses.column_id,
555 pkuses.column_name,
556 pkuses.key_id,
557 pkuses.key_name,
558 pkkey.business_name,
559 pkkey.entity_id,
560 pkkey.entity_name ,
561 cube.business_name
562 from all_iv_cubes cube,
563 all_iv_foreign_keys fk,
564 all_iv_key_column_uses fkuses,
565 all_iv_key_column_uses pkuses,
566 all_iv_keys pkkey
567 where cube.cube_id = fk.entity_id
568 and fk.foreign_key_id = fkuses.key_id
569 and fk.key_id = pkuses.key_id
570 and pkuses.key_id = pkkey.key_id '
571 ;
572 log(l_stmt);
573 execute immediate l_stmt;
574 log('No of Rows inserted : '||sql%rowcount||' '||get_time);
575 commit;
576
577 log('going to execute following sql statement ');
578 l_stmt:='insert into edw_fact_hier_md(
579 fact_id,
580 fact_name,
581 dim_id,
582 dim_name,
583 hier_id,
584 hier_name )
585 select fact.fact_id,
586 fact.fact_name ,
587 keys.entity_id,
588 keys.entity_name,
589 hier.hier_id,
590 hier.hier_name
591 from
592 edw_facts_md fact,
593 edw_hierarchies_md hier,
594 EDW_FOREIGN_KEYS_MD fk,
595 EDW_UNIQUE_KEYS_MD keys
596 where fact.fact_id = fk.entity_id
597 and fk.key_id = keys.key_id
598 and keys.entity_id = hier.dim_id';
599 log(l_stmt);
600 execute immediate l_stmt;
601 log('No of Rows inserted : '||sql%rowcount||' '||get_time);
602 commit;
603
604 log('going to execute following sql statement ');
605 l_stmt:='insert into EDW_FOREIGN_KEY_COLUMNS_MD
606 (entity_id,
607 entity_type,
608 entity_name,
609 fk_name,
610 fk_id,
611 fk_logical_name,
612 fk_description,
613 pk_id,
614 pk_name,
615 fk_column_id,
616 fk_column_name,
617 fk_position)
618 select fk.entity_id,
619 fk.entity_type,
620 fk.entity_name,
621 fk.foreign_key_name fk_name,
622 fk.foreign_key_id fk_id,
623 fk.business_name fk_logical_name,
624 fk.description fk_description,
625 fk.key_id pk_id,
626 fk.key_name pk_name,
627 fkuse.column_id fk_column_id,
628 fkuse.column_name fk_column_name,
629 fkuse.position fk_position
630 from all_iv_foreign_keys fk, all_iv_key_column_uses fkuse
631 where fk.foreign_key_id = fkuse.key_id ';
632 log(l_stmt);
633 execute immediate l_stmt;
634 log('No of Rows inserted : '||sql%rowcount||' '||get_time);
635 commit;
636
637
638 log('going to execute following sql statement ');
639 l_stmt:='insert into edw_hierarchy_level_md (
640 dim_id,
641 dim_name,
642 hier_id,
643 hier_name,
644 lvl_id,
645 lvl_name,
646 lvl_prefix,
647 parent_lvl_id
648 )
649 select
650 dim.dim_id,
651 dim.dim_name,
652 hier.hier_id,
653 hier.hier_name,
654 lvl.level_id lvl_id,
655 lvl.level_name lvl_name,
656 lvl.level_prefix lvl_prefix,
657 hierlvl.parent_level_id parent_lvl_id
658 from edw_dimensions_md dim,
659 EDW_LEVELS_MD lvl,
660 edw_hierarchies_md hier,
661 all_iv_dim_hierarchy_levels hierlvl
662 where dim.dim_id = lvl.dim_id
663 and dim.dim_id = hier.dim_id
664 and hier.hier_id = hierlvl.hierarchy_id
665 and lvl.level_id = hierlvl.level_id '
666 ;
667 log(l_stmt);
668 execute immediate l_stmt;
669 log('No of Rows inserted : '||sql%rowcount||' '||get_time);
670 commit;
671
672 log('going to execute following sql statement ');
673 l_stmt:='insert into EDW_LEVEL_ATTS_MD
674 ( dim_id,
675 dim_name,
676 lvl_id,
677 lvl_name,
678 lvl_col_id,
679 lvl_col_name,
680 lvl_col_long_name,
681 uk_id,
682 uk_name )
683 select dim.dim_id ,
684 dim.dim_name,
685 lvl.level_id ,
686 lvl.level_name ,
687 lvlattr.column_id ,
688 lvlattr.column_name ,
689 lvlattr.business_name ,
690 to_number(null) ,
691 to_char(null)
692 from
693 edw_dimensions_md dim,
694 edw_levels_md lvl ,
695 edw_all_columns_md lvlattr
696 where
700 select dim.dim_id,
697 dim.dim_id = lvl.dim_id
698 and lvl.level_id = lvlattr.entity_id
699 union
701 dim.dim_name,
702 lvl.level_id ,
703 lvl.level_name ,
704 lvlattr.column_id ,
705 lvlattr.column_name ,
706 lvlattr.business_name ,
707 keys.key_id ,
708 keys.key_name
709 from
710 edw_dimensions_md dim,
711 edw_levels_md lvl ,
712 edw_all_columns_md lvlattr,
713 EDW_UNIQUE_KEYS_MD keys,
714 EDW_UNIQUE_KEY_COLUMNS_MD keycols
715 where
716 dim.dim_id = lvl.dim_id
717 and lvl.level_id = lvlattr.entity_id
718 and lvl.level_id = keys.entity_id (+)
719 and keys.key_id = keycols.key_id (+)
720 and lvlattr.column_id(+) = keycols.column_id'
721 ;
722 log(l_stmt);
723 execute immediate l_stmt;
724 log('No of Rows inserted : '||sql%rowcount||' '||get_time);
725 commit;
726
727 analyze_all;
728
729 log('going to execute following sql statement ');
730 l_stmt:='insert into edw_level_relations_md
731 ( level_relation_id,
732 dim_id,
733 dim_name,
734 hier_id,
735 hier_name,
736 parent_lvl_id,
737 parent_lvl_name,
738 parent_lvl_prefix,
739 parent_lvltbl_id,
740 parent_lvltbl_name,
741 uk_id,
742 uk_name,
743 child_lvl_id,
744 child_lvl_name,
745 child_lvl_prefix,
746 child_lvltbl_id,
747 chil_lvltbl_name,
748 fk_id,
749 fk_name )
750 select
751 lvlrel.level_use_id ,
752 dim.dim_id ,
753 dim.dim_name ,
754 hier.hier_id,
755 hier.hier_name,
756 lvlrel.parent_level_id,
757 plvl.level_name ,
758 plvl.level_prefix ,
759 ptbl.relation_id ,
760 ptbl.relation_name ,
761 uk.key_id ,
762 uk.key_name ,
763 lvlrel.level_id ,
764 clvl.level_name ,
765 clvl.level_prefix ,
766 ctbl.relation_id ,
767 ctbl.relation_name ,
768 fk.foreign_key_id ,
769 fk.foreign_key_name
770 FROM
771 edw_dimensions_md dim,
772 edw_hierarchies_md hier,
773 all_iv_dim_hierarchy_levels lvlrel,
774 edw_levels_md plvl,
775 EDW_RELATIONS_MD ptbl,
776 EDW_UNIQUE_KEYS_MD uk,
777 edw_levels_md clvl,
778 EDW_RELATIONS_MD ctbl,
779 EDW_FOREIGN_KEYS_MD fk
780 where
781 dim.dim_id = hier.dim_id
782 and hier.hier_id = lvlrel.HIERARCHY_id
783 and lvlrel.parent_level_id = plvl.level_id
784 and plvl.level_name || ''_LTC'' = ptbl.relation_name (+)
785 and ptbl.relation_name is not null
786 and ptbl.relation_id = uk.entity_id(+)
787 and lvlrel.level_id = clvl.level_id
788 and clvl.level_name||''_LTC'' = ctbl.relation_name (+)
789 and ctbl.relation_name is not null
790 and ctbl.relation_id = fk.entity_id (+)
791 and fk.key_id = uk.key_id
792 UNION ALL
793 SELECT
794 LVLREL.LEVEL_USE_ID ,
795 DIM.DIM_ID ,
796 DIM.DIM_NAME ,
797 HIER.HIER_ID,
798 HIER.HIER_NAME,
799 LVLREL.PARENT_LEVEL_ID,
800 PLVL.LEVEL_NAME ,
801 PLVL.LEVEL_PREFIX ,
802 TO_NUMBER(NULL) ,
803 NULL ,
804 TO_NUMBER(NULL) ,
805 NULL ,
806 LVLREL.LEVEL_ID ,
807 CLVL.LEVEL_NAME ,
808 CLVL.LEVEL_PREFIX ,
809 TO_NUMBER(NULL) ,
810 NULL ,
811 TO_NUMBER(NULL) ,
812 NULL
813 FROM edw_dimensions_md DIM,
814 edw_hierarchies_md hier,
815 all_iv_dim_hierarchy_levels lvlrel,
816 edw_levels_md plvl ,
817 EDW_RELATIONS_MD ptbl,
818 edw_levels_md Clvl,
819 EDW_RELATIONS_MD CTBL
820 WHERE
821 DIM.DIM_ID = HIER.DIM_ID
822 AND HIER.HIER_ID = LVLREL.HIERARCHY_ID
823 AND LVLREL.PARENT_LEVEL_ID = PLVL.LEVEL_ID
824 AND PLVL.LEVEL_NAME || ''_LTC'' = PTBL.RELATION_NAME (+)
825 AND PTBL.RELATION_NAME IS NULL AND LVLREL.LEVEL_ID = CLVL.LEVEL_ID
826 AND CLVL.LEVEL_NAME || ''_LTC'' = CTBL.RELATION_NAME (+)
827 AND CTBL.RELATION_NAME IS NULL'
828 ;
829 log(l_stmt);
830 execute immediate l_stmt;
831 log('No of Rows inserted : '||sql%rowcount||' '||get_time);
832 commit;
833
834 log('going to execute following sql statement ');
835 l_stmt:='insert into edw_level_table_atts_md (dim_id, dim_name,
836 level_table_id, level_table_name, level_prefix,
837 level_table_col_id, level_table_col_name, level_table_col_long_name,
838 key_type, key_id, key_name, level_id,
839 level_name, level_long_name)
840 SELECT DIM.DIM_ID, DIM.DIM_NAME, LVLTBL.ELEMENTID,
841 LVLTBL.NAME, LVL.LEVEL_PREFIX, UCOL.COLUMN_ID, UCOL.COLUMN_NAME,
842 UCOL.BUSINESS_NAME, DECODE(uk.KEY_ID, NULL, NULL, ''UK''),
843 UK.KEY_ID, UK.KEY_NAME, lvl.LEVEL_id, lvl.LEVEL_name,
844 LVL.LEVEL_LONG_NAME
845 FROM EDW_DIMENSIONS_MD DIM , EDW_LEVELS_MD LVL , EDW_TABLES_MD LVLTBL ,
846 EDW_ALL_COLUMNS_MD UCOL ,
847 EDW_UNIQUE_KEYS_MD UK, EDW_UNIQUE_KEY_COLUMNS_MD ukuse
848 WHERE DIM.DIM_ID = LVL.DIM_ID
849 AND LVL.LEVEL_NAME || ''_LTC'' = LVLTBL.NAME AND LVLTBL.ELEMENTID =
850 UCOL.ENTITY_ID
851 AND LVLTBL.ELEMENTID = UK.ENTITY_ID
852 and uk.key_id = ukuse.key_id
853 and ukuse.column_id = ucol.column_id
854 UNION ALL
855 SELECT DIM.DIM_ID, DIM.DIM_NAME, LVLTBL.ELEMENTID,
859 FROM EDW_DIMENSIONS_MD_V DIM , EDW_LEVELS_MD LVL , EDW_TABLES_MD LVLTBL,
856 LVLTBL.NAME, LVL.LEVEL_PREFIX, UCOL.COLUMN_ID, UCOL.COLUMN_NAME,
857 UCOL.BUSINESS_NAME, null,
858 to_number(null), null, lvl.LEVEL_id, lvl.LEVEL_name, LVL.LEVEL_LONG_NAME
860 EDW_ALL_COLUMNS_MD UCOL
861 WHERE DIM.DIM_ID = LVL.DIM_ID
862 AND LVL.LEVEL_NAME || ''_LTC'' = LVLTBL.NAME AND LVLTBL.ELEMENTID =
863 UCOL.ENTITY_ID
864 and ucol.column_id not in
865 (select keyuse.column_id from EDW_UNIQUE_KEYS_MD keys,
866 EDW_UNIQUE_KEY_COLUMNS_MD keyuse
867 where keys.key_id = keyuse.key_id
868 and keys.entity_id = LVLTBL.ELEMENTID)
869 and ucol.column_id not in (
870 select keyuse.column_id from EDW_FOREIGN_KEYS_MD keys,
871 EDW_UNIQUE_KEY_COLUMNS_MD keyuse
872 where keys.foreign_key_id = keyuse.key_id
873 and keys.entity_id = LVLTBL.ELEMENTID
874 )
875 UNION ALL
876 SELECT
877 DIM.DIM_ID, DIM.DIM_NAME,
878 LVLTBL.ELEMENTID, LVLTBL.NAME, LVL.LEVEL_PREFIX, FCOL.COLUMN_ID,
879 FCOL.COLUMN_NAME,
880 FCOL.BUSINESS_NAME, ''FK'', FK.KEY_ID, FK.KEY_NAME, lvl.level_id,
881 lvl.level_name, LVL.LEVEL_LONG_NAME
882 FROM
883 EDW_DIMENSIONS_MD_V DIM , EDW_LEVELS_MD LVL , EDW_TABLES_MD LVLTBL ,
884 EDW_ALL_COLUMNS_MD FCOL ,
885 EDW_FOREIGN_KEYS_MD FK, EDW_UNIQUE_KEY_COLUMNS_MD fkuse
886 WHERE DIM.DIM_ID = LVL.DIM_ID
887 AND LVL.LEVEL_NAME || ''_LTC'' = LVLTBL.NAME AND LVLTBL.ELEMENTID = FCOL.ENTITY_ID
888 AND LVLTBL.ELEMENTID = FK.ENTITY_ID
889 and fk.foreign_key_id = fkuse.key_id
890 and fkuse.column_id = fcol.column_id';
891 log(l_stmt);
892 execute immediate l_stmt;
893 log('No of Rows inserted : '||sql%rowcount||' '||get_time);
894 commit;
895
896 analyze_all;
897 END IF;
898 log ('Finished procedure refresh_metadata_tables');
899 Exception when others then
900 g_status_message:=sqlerrm;
901 log('Error in populate metadata tables '||sqlerrm);
902 raise_application_error(-20000,errbuf);
903 END refresh_metadata_tables;
904
905
906 function get_db_user(p_product varchar2) return varchar2 is
907 l_dummy1 varchar2(2000);
908 l_dummy2 varchar2(2000);
909 l_schema varchar2(400);
910 Begin
911 if FND_INSTALLATION.GET_APP_INFO(p_product,l_dummy1, l_dummy2,l_schema) = false then
912 log('FND_INSTALLATION.GET_APP_INFO returned with error');
913 return null;
914 end if;
915 return l_schema;
916 Exception when others then
917 g_status_message:=sqlerrm;
918 log('Error in get_db_user '||sqlerrm);
919 return null;
920 End;
921
922 procedure log(p_message varchar2) is
923 Begin
924 edw_owb_collection_util.write_to_log_file_n(p_message);
925 Exception when others then
926 g_status_message:=sqlerrm;
927 null;
928 End;
929
930 function get_time return varchar2 is
931 Begin
932 return ' '||fnd_date.date_to_displaydt (sysdate);
933 Exception when others then
934 null;
935 End;
936
937 function populate_pvt_tables return boolean is
938 l_stmt varchar2(32000);
939 l_table varchar2(300);
940 l_table2 varchar2(300);
941 l_table3 varchar2(300);
942 l_table4 varchar2(300);
943 Begin
944 log('going to execute following sql statement ');
945 l_stmt:='insert into edw_pvt_level_relation_md( '||
946 'hierarchy_id, '||
947 'parent_level_id, '||
948 'child_level_id) '||
949 'select '||
950 'hierarchy_id, '||
951 'parent_level_id, '||
952 'level_id child_level_id '||
953 'from '||
954 'all_iv_dim_hierarchy_levels ';
955 log(l_stmt||get_time);
956 execute immediate l_stmt;
957 log(sql%rowcount||get_time);
958 commit;
959 l_stmt:='insert into edw_pvt_sequences_md( '||
960 ' sequence_id, '||
961 ' sequence_name, '||
962 'logical_name, '||
963 'description) '||
964 'select '||
965 ' sequence_id, '||
966 ' sequence_name, '||
967 ' business_name logical_name, '||
968 ' description description '||
969 'from '||
970 'all_iv_sequences ';
971 log(l_stmt||get_time);
972 execute immediate l_stmt;
973 log(sql%rowcount||get_time);
974 commit;
975 l_stmt:='insert into edw_pvt_views_md( '||
976 ' view_id, '||
977 ' view_name, '||
978 'logical_name, '||
979 'description '||
980 ') '||
981 'select '||
982 ' view_id, '||
983 ' view_name, '||
984 ' business_name logical_name, '||
985 ' description description '||
986 'from '||
987 'all_iv_views ';
988 log(l_stmt||get_time);
989 execute immediate l_stmt;
990 log(sql%rowcount||get_time);
991 commit;
992 l_stmt:='insert into edw_pvt_columns_md( '||
993 ' column_id, '||
994 ' column_name, '||
995 'parent_object_id, '||
996 ' data_type, '||
997 ' length, '||
998 ' logical_name, '||
999 ' description '||
1000 ') '||
1001 ' select '||
1002 ' column_id, '||
1003 ' column_name, '||
1004 ' entity_id parent_object_id, '||
1005 ' data_type, '||
1006 ' length, '||
1007 ' business_name logical_name, '||
1008 ' description description '||
1009 'from '||
1013 log(sql%rowcount||get_time);
1010 'all_iv_columns ';
1011 log(l_stmt||get_time);
1012 execute immediate l_stmt;
1014 commit;
1015 l_stmt:='insert into edw_pvt_key_columns_md( '||
1016 'key_id, '||
1017 'column_id, '||
1018 'KEY_TYPE, '||
1019 'KEY_NAME '||
1020 ') '||
1021 'select '||
1022 'key_id, '||
1023 'column_id, '||
1024 'KEY_TYPE, '||
1025 'KEY_NAME '||
1026 'from '||
1027 'all_iv_key_column_uses ';
1028 log(l_stmt||get_time);
1029 execute immediate l_stmt;
1030 log(sql%rowcount||get_time);
1031 commit;
1032 l_stmt:='insert into edw_pvt_mappings_md( '||
1033 'mapping_id, '||
1034 'mapping_name, '||
1035 'logical_name, '||
1036 'description '||
1037 ') '||
1038 'select '||
1039 'map_id mapping_id, '||
1040 'map_name mapping_name, '||
1041 'business_name logical_name, '||
1042 'description description '||
1043 'from '||
1044 'all_iv_xform_maps ';
1045 log(l_stmt||get_time);
1046 execute immediate l_stmt;
1047 log(sql%rowcount||get_time);
1048 commit;
1049 l_table:=g_bis_owner||'.edw_pvt_map_properties_md1';
1050 drop_table(l_table);
1051
1052 l_stmt:='create table '||l_table||' tablespace '||g_op_table_space||' as '||
1053 'select '||
1054 'comp.map_id mapping_id, '||
1055 'prop.property_value text, '||
1056 '''Filter'' text_type '||
1057 'from '||
1058 'all_iv_xform_map_components comp, '||
1059 'all_iv_xform_map_properties prop '||
1060 'where '||
1061 'prop.map_component_id = comp.map_component_id and '||
1062 'comp.operator_type = ''Filter''';
1063 log(l_stmt||get_time);
1064 execute immediate l_stmt;
1065 log(sql%rowcount||get_time);
1066 commit;
1067
1068 l_stmt:='insert into edw_pvt_map_properties_md( '||
1069 'mapping_id, '||
1070 'Primary_source, '||
1071 'Primary_target, '||
1072 'text, '||
1073 'text_type '||
1074 ') '||
1075 'select '||
1076 'src.map_id mapping_id, '||
1077 'src.DATA_ENTITY_ID Primary_source, '||
1078 'tgt.DATA_ENTITY_ID Primary_target, '||
1079 'line.text text, '||
1080 'line.text_type text_type '||
1081 'from '||
1082 'ALL_IV_XFORM_MAP_PRIM_SOURCES src, '||
1083 'ALL_IV_XFORM_MAP_TARGETS tgt, '||
1084 l_table||' line '||
1085 'where '||
1086 'src.map_id=tgt.map_id '||
1087 'and line.mapping_id(+)=src.map_id ';
1088 log(l_stmt||get_time);
1089 execute immediate l_stmt;
1090 log(sql%rowcount||get_time);
1091 commit;
1092 drop_table(l_table);
1093 l_stmt:='insert into edw_pvt_map_sources_md( '||
1094 'mapping_id, '||
1095 'source_id, '||
1096 'source_usage_id, '||
1097 'source_alias '||
1098 ') '||
1099 'select '||
1100 'src.map_id mapping_id, '||
1101 'src.DATA_ENTITY_ID source_id, '||
1102 'src.map_component_id source_usage_id, '||
1103 'src.map_component_name source_alias '||
1104 'from '||
1105 'ALL_IV_XFORM_MAPS map, '||
1106 'ALL_IV_XFORM_MAP_SOURCES src '||
1107 'where map.map_id=src.map_id ';
1108 log(l_stmt);
1109 execute immediate l_stmt;
1110 log('No of Rows inserted : '||sql%rowcount||' '||get_time);
1111 commit;
1112 ------------------------------------------------------------------------------
1113 --fix for 2739489.
1114 log('going to execute following sql statement ');
1115 l_stmt:='create table '||l_table||' tablespace '||g_op_table_space||' as select edw_pvt_map_sources_md.*, '||
1116 'owm_view_utilities.ISREALSOURCE(source_usage_id) col from edw_pvt_map_sources_md ';
1117 log(l_stmt||get_time);
1118 execute immediate l_stmt;
1119 log(sql%rowcount||get_time);
1120 l_stmt := 'truncate table '||g_bis_owner||'.EDW_PVT_MAP_SOURCES_MD';
1121 log(l_stmt||get_time);
1122 execute immediate l_stmt;
1123 l_stmt:='insert into edw_pvt_map_sources_md(mapping_id, '||
1124 'source_id, '||
1125 'source_usage_id, '||
1126 'source_alias) select mapping_id, '||
1127 'source_id, '||
1128 'source_usage_id, '||
1129 'source_alias from '||l_table||' where col=''Y''';
1130 log(l_stmt||get_time);
1131 execute immediate l_stmt;
1132 log(sql%rowcount||get_time);
1133 commit;
1134 drop_table(l_table);
1135 ------------------------------------------------------------------------------
1136 log('going to execute following sql statement ');
1137 l_stmt:='insert into edw_pvt_map_targets_md( '||
1138 'mapping_id, '||
1139 'target_id, '||
1140 'target_usage_id, '||
1141 'target_alias '||
1142 ') '||
1143 'select '||
1144 'tgt.map_id mapping_id, '||
1145 'tgt.DATA_ENTITY_ID target_id, '||
1146 'tgt.map_component_id target_usage_id, '||
1147 'tgt.map_component_name target_alias '||
1148 'from '||
1149 'ALL_IV_XFORM_MAPS map, '||
1150 'ALL_IV_XFORM_MAP_TARGETS tgt '||
1151 'where map.map_id=tgt.map_id ';
1152 log(l_stmt||get_time);
1153 execute immediate l_stmt;
1154 log(sql%rowcount||get_time);
1155 commit;
1156 l_table:=g_bis_owner||'.edw_pvt_map_columns_md_1';
1157 drop_table(l_table);
1158 l_stmt:='create table '||l_table||' tablespace '||g_op_table_space||' as '||
1159 'SELECT * from ALL_IV_XFORM_MAP_PARAMETERS3';
1163 commit;
1160 log(l_stmt);
1161 execute immediate l_stmt;
1162 log('No of Rows inserted : '||sql%rowcount||' '||get_time);
1164
1165 log('going to execute following sql statement ');
1166 l_stmt:='insert into edw_pvt_map_columns_tgt_md ('||
1167 ' map_id, '||
1168 ' map_component_id, '||
1169 ' source_parameter_id, '||
1170 ' parameter_id, '||
1171 ' data_item_id ) '||
1172 'select '||
1173 ' tgt_ru.map_id, '||
1174 ' tgt_ru.map_component_id, '||
1175 ' tgt_iu.source_parameter_id, '||
1176 ' tgt_iu.parameter_id, '||
1177 ' tgt_iu.data_item_id '||
1178 'from '||
1179 ' all_iv_xform_map_targets tgt_ru, '||
1180 ' '||l_table||' tgt_iu '||
1181 'where '||
1182 ' tgt_ru.map_component_id = tgt_iu.map_component_id ';
1183 log(l_stmt);
1184 execute immediate l_stmt;
1185 log('No of Rows inserted : '||sql%rowcount||' '||get_time);
1186 commit;
1187
1188 drop_table(l_table);
1189
1190 log('going to execute following sql statement ');
1191 l_stmt:='insert into edw_pvt_map_columns_src_md('||
1192 ' map_id, '||
1193 ' map_component_id, '||
1194 ' parameter_id, '||
1195 ' data_item_id) '||
1196 'select '||
1197 'src_ru.map_id, '||
1198 'src_ru.map_component_id, '||
1199 'src_iu.parameter_id, '||
1200 'src_iu.data_item_id '||
1201 'from '||
1202 'all_iv_xform_map_components2 src_ru, '||
1203 'all_iv_xform_map_parameters2 src_iu '||
1204 'where '||
1205 'src_ru.map_component_id = src_iu.map_component_id ';
1206 log(l_stmt||get_time);
1207 execute immediate l_stmt;
1208 log(sql%rowcount||get_time);
1209 commit;
1210 l_table:=g_bis_owner||'.t_xform_map_components';
1211 l_table2:=g_bis_owner||'.t_foreign_keys';
1212 l_table3:=g_bis_owner||'.t_keys';
1213 drop_table(l_table);
1214 drop_table(l_table2);
1215 drop_table(l_table3);
1216 l_stmt:='create table '||l_table||' tablespace '||g_op_table_space||' as select * from all_iv_xform_map_components3';
1217 log(l_stmt);
1218 execute immediate l_stmt;
1219 log('No of Rows inserted : '||sql%rowcount||' '||get_time);
1220 commit;
1221
1222 log('going to execute following sql statement ');
1223 l_stmt:='create table '||l_table2||' tablespace '||g_op_table_space||' as select * from all_iv_foreign_keys';
1224 log(l_stmt);
1225 execute immediate l_stmt;
1226 log('No of Rows inserted : '||sql%rowcount||' '||get_time);
1227 commit;
1228
1229 log('going to execute following sql statement ');
1230 l_stmt:='create table '||l_table3||' tablespace '||g_op_table_space||' as select * from all_iv_keys';
1231 log(l_stmt);
1232 execute immediate l_stmt;
1233 log('No of Rows inserted : '||sql%rowcount||' '||get_time);
1234 commit;
1235
1236 log('going to execute following sql statement ');
1237 l_stmt:='insert into edw_pvt_map_key_usages_md( '||
1238 'Source_usage_id, '||
1239 'Parent_table_usage_id, '||
1240 'foreign_key_usage_id, '||
1241 'foreign_key_id, '||
1242 'Unique_key_id, '||
1243 'mapping_id '||
1244 ') '||
1245 'select /*+use_hash(fstg_usage,fstg_fk,dim_usage,dim_pk)*/ '||
1246 ' fstg_usage.map_component_id source_usage_id, '||
1247 ' dim_usage.map_component_id parent_table_usage_id, '||
1248 ' fstg_fk.foreign_key_id foreign_key_usage_id, '||
1249 ' fstg_fk.foreign_key_id foreign_key_id, '||
1250 ' dim_pk.key_id unique_key_id, '||
1251 ' fstg_usage.map_id map_id '||
1252 'from '||
1253 ' '||l_table||' fstg_usage, '||
1254 ' '||l_table2||' fstg_fk, '||
1255 ' '||l_table||' dim_usage, '||
1256 ' '||l_table3||' dim_pk '||
1257 'where '||
1258 ' fstg_fk.entity_id = fstg_usage.data_entity_id '||
1259 'and fstg_usage.map_id = dim_usage.map_id '||
1260 'and fstg_fk.key_id = dim_pk.key_id '||
1261 'and dim_pk.entity_id = dim_usage.data_entity_id ';
1262 log(l_stmt);
1263 execute immediate l_stmt;
1264 log('No of Rows inserted : '||sql%rowcount||' '||get_time);
1265 commit;
1266
1267 drop_table(l_table);
1268 drop_table(l_table2);
1269 drop_table(l_table3);
1270 ------------------------------------------------------------------------------
1271 --fix for 2739489.
1272 log('going to execute following sql statement ');
1273 l_stmt:='create table '||l_table||' tablespace '||g_op_table_space||' as select edw_pvt_map_key_usages_md.*,'||
1274 'owm_view_utilities.ISREALSOURCE(source_usage_id) col from edw_pvt_map_key_usages_md ';
1275 log(l_stmt||get_time);
1276 execute immediate l_stmt;
1277 log(sql%rowcount||get_time);
1278 l_stmt := 'truncate table '||g_bis_owner||'.edw_pvt_map_key_usages_md';
1279 log(l_stmt||get_time);
1280 execute immediate l_stmt;
1281 l_stmt:='insert into edw_pvt_map_key_usages_md( '||
1282 'Source_usage_id, '||
1283 'Parent_table_usage_id, '||
1284 'foreign_key_usage_id, '||
1285 'foreign_key_id, '||
1286 'Unique_key_id, '||
1287 'mapping_id) select '||
1288 'Source_usage_id, '||
1289 'Parent_table_usage_id, '||
1293 'mapping_id from '||l_table||' where col=''Y''';
1290 'foreign_key_usage_id, '||
1291 'foreign_key_id, '||
1292 'Unique_key_id, '||
1294 log(l_stmt);
1295 execute immediate l_stmt;
1296 log(sql%rowcount||' '||get_time);
1297 commit;
1298 drop_table(l_table);
1299 ------------------------------------------------------------------------------
1300 log('going to execute following sql statement ');
1301 --Bug 3548744
1302 l_stmt:='insert into edw_pvt_map_func_md( '||
1303 'func_name, '||
1304 'category_name, '||
1305 'column_name, '||
1306 'column_id, '||
1307 'column_usage_id , '||
1308 'aggregation, '||
1309 'is_distinct, '||
1310 'relation_id, '||
1311 'relation_name, '||
1312 'relation_usage_id, '||
1313 'relation_type, '||
1314 'func_usage_id, '||
1315 'attribute_position, '||
1316 'func_default_value, '||
1317 'mapping_id '||
1318 ') '||
1319 'select /*+ ordered(v2,rel) no_merge(v2) */ '||
1320 ' v2.function_name, '||
1321 ' v2.function_library_name, '||
1322 ' v2.parameter_name src_parameter_name, '||
1323 ' col.column_id, '||
1324 ' v2.src_parameter_id, '||
1325 ' owm_view_utilities.getaggregationfunction(v2.src_parameter_id), '||
1326 ' null, '||
1327 ' rel.object_id, '||
1328 ' rel.object_name, '||
1329 ' v2.src_component_id as map_component_id, '||
1330 ' rel.object_type, '||
1331 ' v2.operator_id as func_usage_id, '||
1332 ' v2.position, '||
1333 ' v2.default_value as defaultvalue, '||
1334 ' v2.map_id '||
1335 ' from '||
1336 ' (select /*+ ordered(v1,mpv) no_merge(v1) */ '||
1337 ' v1.function_name, '||
1338 ' v1.src_parameter_name, '||
1339 ' v1.src_parameter_id, '||
1340 ' v1.src_component_id, '||
1341 ' v1.siusage, '||
1342 ' v1.operator_id, '||
1343 ' v1.map_id, '||
1344 ' v1.position, '||
1345 ' v1.default_value, '||
1346 ' v1.function_library_name, '||
1347 ' (select parameter_name from all_iv_xform_map_parameters '||
1348 ' where parameter_id = v1.siusage) parameter_name, '||
1349 ' (select sc.data_entity_id '||
1350 ' from all_iv_xform_map_parameters mpv, '||
1351 ' all_iv_xform_map_components sc '||
1352 ' where v1.siusage = mpv.parameter_id '||
1353 ' and mpv.map_component_id = sc.map_component_id) data_entity_id '||
1354 ' from '||
1355 ' (select /*+ ordered(ops,fa,fcat) '||
1356 ' no_merge(fa) no_merge(ops) no_merge(fa)*/ '||
1357 ' ops.function_name, '||
1358 ' ops.src_parameter_name, '||
1359 ' ops.src_parameter_id, '||
1360 ' ops.src_component_id, '||
1361 ' ops.operator_id, '||
1362 ' ops.map_id, '||
1363 ' owm_view_utilities.findsourceitemusage(ops.src_parameter_id) siusage, '||
1364 ' fa.position, '||
1365 ' fa.default_value, '||
1366 ' fcat.function_library_name '||
1367 ' from all_iv_operator_sources2 ops, '||
1368 ' all_iv_function_parameters fa, '||
1369 ' all_iv_function_libraries fcat, '||
1370 ' all_iv_xform_map_parameters maprmv '||
1371 ' where '||
1372 ' ops.function_id = fa.function_id '||
1373 ' and ops.map_id = maprmv.map_id '||
1374 ' and ops.op_param_id = maprmv.parameter_id '||
1375 ' and maprmv.position = fa.position '||
1376 ' and fcat.function_library_id =ops.function_library_id '||
1377 ' ) v1 '||
1378 ') v2, '||
1379 ' all_iv_all_objects rel, '||
1380 ' edw_pvt_columns_md col '||
1381 ' where '||
1382 ' v2.data_entity_id = rel.object_id (+) '||
1383 ' and col.parent_object_id = rel.object_id '||
1384 ' and col.column_name=v2.parameter_name ';
1385 log(l_stmt);
1386 execute immediate l_stmt;
1387 log('No of Rows inserted : '||sql%rowcount||' '||get_time);
1388 commit;
1389 /*
1390 Bug 2638686
1391 The above sql only inserted half the info. it only inserted the src columns to
1392 a transform and missed the tgt columns.the sql below gets the tgt column.
1393 the sql below written by vsurendr and not owb team
1394 */
1395 log('going to execute following sql statement ');
1396 l_stmt:='insert into edw_pvt_map_func_md( '||
1397 'func_name, '||
1398 'category_name, '||
1399 'column_name, '||
1400 'column_id, '||
1401 'column_usage_id , '||
1402 'aggregation, '||
1403 'is_distinct, '||
1404 'relation_id, '||
1405 'relation_name, '||
1406 'relation_usage_id, '||
1407 'relation_type, '||
1408 'func_usage_id, '||
1409 'attribute_position, '||
1410 'func_default_value, '||
1411 'mapping_id '||
1412 ') '||
1413 'select '||
1414 'tgt.function_name, '||
1415 'fcat.function_library_name, '||
1416 'tgt.tgt_parameter_name, '||
1417 'col.column_id, '||
1418 'tgt.tgt_parameter_id, '||
1419 'null, '||
1420 'null, '||
1421 'map_tgt.data_entity_id, '||
1422 'tgt.tgt_component_name, '||
1426 '0, '||
1423 'tgt.tgt_component_id, '||
1424 'null, '||
1425 'tgt.operator_id, '||
1427 'null, '||
1428 'tgt.map_id '||
1429 'from '||
1430 'all_iv_function_libraries fcat, '||
1431 'all_iv_operator_targets tgt, '||
1432 'ALL_IV_XFORM_MAP_TARGETS map_tgt, '||
1433 'edw_pvt_columns_md col '||
1434 'where '||
1435 'fcat.function_library_id=tgt.function_library_id '||
1436 'and map_tgt.map_id=tgt.map_id '||
1437 'and map_tgt.data_entity_name=tgt.tgt_component_name '||
1438 'and col.column_name=tgt.tgt_parameter_name '||
1439 'and col.parent_object_id =map_tgt.data_entity_id ';
1440 log(l_stmt);
1441 execute immediate l_stmt;
1442 log('No of Rows inserted : '||sql%rowcount||' '||get_time);
1443 commit;
1444 /*
1445 bug 3109244
1446 maps were using aggregation operator. we did not support aggregation operator.
1447 in 2.1.1, to get count(...), you would need to define a transform in edw_stand_alone
1448 transform category called count with one parameter as input .
1449 then you would use this transform in the map. (like group_by).
1450 bookings_pk will the input to this transform and the output of the transform
1451 goes to order_count in the fact. This is how you would have does in 2.1.1 and
1452 this is what we support in 9i.
1453 there is a need to start supporting aggregation operators because they will be used in maps
1454 made a change to support aggregation operator. aggregation operator is simulated
1455 as a transform edw_stand_alone.count(...) or edw_stand_alone.max(...) etc
1456 its import to know that we are simulating the aggregation operator as a transform
1457 EDW_STAND_ALONE.count(...)
1458 If we bring into the map a real EDW_STAND_ALONE.count(...), this will still work. that is
1459 because the earlier insert into edw_pvt_map_func_md will handle that
1460 we support ONLY ONE aggregation operator in the map!!!
1461 */
1462 l_table:=g_bis_owner||'.edw_pvt_map_func_md_T1';
1463 l_table2:=g_bis_owner||'.edw_pvt_map_func_md_T2';
1464 drop_table(l_table);
1465 drop_table(l_table2);
1466 l_stmt:='create table '||l_table||' tablespace '||g_op_table_space||' as '||
1467 'select map_id,map_component_id from all_iv_xform_map_components where operator_type=''Aggregation''';
1468 log(l_stmt);
1469 execute immediate l_stmt;
1470 log('Created with '||sql%rowcount||' rows '||get_time);
1471 l_stmt:='create table '||l_table2||' tablespace '||g_op_table_space||' as '||
1472 'select '||
1473 'out_param.map_id, '||
1474 'out_param.parameter_id out_param, '||
1475 'target_param.data_item_id target_column, '||
1476 'target_param.parameter_id target_column_usage, '||
1477 'substr(out_param.transformation_expression,1,instr(out_param.transformation_expression,''('')-1) expression, '||
1478 'in_param.source_parameter_id in_param '||
1479 'from '||
1480 'all_iv_xform_map_parameters out_param, '||
1481 'all_iv_xform_map_parameters in_param, '||
1482 'all_iv_xform_map_parameters target_param, '||
1483 'all_iv_xform_map_targets targets, '||
1484 l_table||' '||
1485 'where '||
1486 l_table||'.map_id=out_param.map_id '||
1487 'and '||l_table||'.map_component_id=out_param.map_component_id '||
1488 'and '||l_table||'.map_id=in_param.map_id '||
1489 'and '||l_table||'.map_component_id=in_param.map_component_id '||
1490 'and instr(out_param.transformation_expression,''('')>0 '||
1491 'and out_param.position=in_param.position '||
1492 'and out_param.parameter_type=''OUT'' '||
1493 'and in_param.parameter_type=''IN'' '||
1494 'and target_param.map_id='||l_table||'.map_id '||
1495 'and target_param.map_component_id=targets.map_component_id '||
1496 'and targets.map_id='||l_table||'.map_id '||
1497 'and target_param.source_parameter_id=out_param.parameter_id ';
1498 log(l_stmt);
1499 execute immediate l_stmt;
1500 log('Created with '||sql%rowcount||' rows '||get_time);
1501 l_stmt:='insert into edw_pvt_map_func_md( '||
1502 'func_name, '||
1503 'category_name, '||
1504 'column_name, '||
1505 'column_id, '||
1506 'column_usage_id , '||
1507 'aggregation, '||
1508 'is_distinct, '||
1509 'relation_id, '||
1510 'relation_name, '||
1511 'relation_usage_id, '||
1512 'relation_type, '||
1513 'func_usage_id, '||
1514 'attribute_position, '||
1515 'func_default_value, '||
1516 'mapping_id '||
1517 ') '||
1518 'select '||
1519 l_table2||'.expression, '||
1520 '''EDW_STAND_ALONE'', '||
1521 'col.column_name, '||
1522 'col.column_id, '||
1523 'param.parameter_id, '||--column usage id
1524 'null, '||--aggregation
1525 'null, '||--is_distinct
1526 'rel.relation_id, '||
1527 'rel.relation_name, '||
1528 'src_usage.source_usage_id, '||
1529 'rel.relation_type, '||
1530 l_table2||'.target_column_usage function_usage_id, '||
1531 'rownum attribute_position, '||
1532 'null, '||--func_default_value
1533 l_table2||'.map_id '||
1534 'from '||
1535 l_table2||', '||
1536 'all_iv_xform_map_parameters param, '||
1537 'edw_pvt_columns_md col, '||
1538 'edw_relations_md rel, '||
1539 'edw_pvt_map_sources_md src_usage '||
1540 'where '||
1541 'param.parameter_id=owm_view_utilities.findsourceitemusage('||l_table2||'.in_param) '||
1545 'and src_usage.mapping_id='||l_table2||'.map_id '||
1542 'and param.map_id='||l_table2||'.map_id '||
1543 'and col.column_id=param.data_item_id '||
1544 'and rel.relation_id=col.parent_object_id '||
1546 'and src_usage.source_id=rel.relation_id '||
1547 'union all '||
1548 'select '||
1549 l_table2||'.expression, '||
1550 '''EDW_STAND_ALONE'', '||
1551 'col.column_name, '||
1552 'col.column_id, '||
1553 l_table2||'.target_column_usage, '||
1554 'null, '||--aggregation
1555 'null, '||--is_distinct
1556 'rel.relation_id, '||
1557 'rel.relation_name, '||
1558 'tgt_usage.target_usage_id, '||
1559 'rel.relation_type, '||
1560 l_table2||'.target_column_usage function_usage_id, '||
1561 '0 attribute_position, '||
1562 'null, '||--default value
1563 l_table2||'.map_id '||
1564 'from '||
1565 l_table2||', '||
1566 'edw_pvt_columns_md col, '||
1567 'edw_relations_md rel, '||
1568 'edw_pvt_map_targets_md tgt_usage '||
1569 'where '||
1570 ' col.column_id='||l_table2||'.target_column '||
1571 'and rel.relation_id=col.parent_object_id '||
1572 'and tgt_usage.mapping_id='||l_table2||'.map_id '||
1573 'and tgt_usage.target_id=rel.relation_id ';
1574 log(l_stmt);
1575 execute immediate l_stmt;
1576 log('Inserted '||sql%rowcount||' rows '||get_time);
1577 commit;
1578 drop_table(l_table);
1579 drop_table(l_table2);
1580 return true;
1581 Exception when others then
1582 g_status_message:=sqlerrm;
1583 log('Error in populate_pvt_tables '||sqlerrm);
1584 return false;
1585 End;
1586
1587 procedure drop_table(p_table varchar2) is
1588 Begin
1589 execute immediate 'drop table '||p_table;
1590 Exception when others then
1591 null;
1592 End;
1593
1594 procedure analyze_all is
1595 l_errbuf varchar2(2000);
1596 l_retcode varchar2(200);
1597 Begin
1598 log('system time is '|| fnd_date.date_to_displaydt (sysdate) );
1599 log('going to analyze metadata tables ');
1600 fnd_stats.gather_table_stats(l_errbuf, l_retcode,g_bis_owner,'EDW_ALL_COLUMNS_MD',10,1);
1601 fnd_stats.gather_table_stats(l_errbuf, l_retcode,g_bis_owner,'EDW_ATTRIBUTE_SETS_MD',10,1);
1602 fnd_stats.gather_table_stats(l_errbuf, l_retcode,g_bis_owner,'EDW_ATTRIBUTE_SET_COLUMN_MD',10,1);
1603 fnd_stats.gather_table_stats(l_errbuf, l_retcode,g_bis_owner,'EDW_DIMENSIONS_MD',10,1);
1604 fnd_stats.gather_table_stats(l_errbuf, l_retcode,g_bis_owner,'EDW_DIM_ATTRIBUTES_MD',10,1);
1605 fnd_stats.gather_table_stats(l_errbuf, l_retcode,g_bis_owner,'EDW_FACTS_MD',10,1);
1606 fnd_stats.gather_table_stats(l_errbuf, l_retcode,g_bis_owner,'EDW_FACT_ATTRIBUTES_MD',10,1);
1607 fnd_stats.gather_table_stats(l_errbuf, l_retcode,g_bis_owner,'EDW_FACT_DIM_RELATIONS_MD',10,1);
1608 fnd_stats.gather_table_stats(l_errbuf, l_retcode,g_bis_owner,'EDW_FACT_HIER_MD',10,1);
1609 fnd_stats.gather_table_stats(l_errbuf, l_retcode,g_bis_owner,'EDW_FOREIGN_KEYS_MD',10,1);
1610 fnd_stats.gather_table_stats(l_errbuf, l_retcode,g_bis_owner,'EDW_FOREIGN_KEY_COLUMNS_MD',10,1);
1611 fnd_stats.gather_table_stats(l_errbuf, l_retcode,g_bis_owner,'EDW_HIERARCHIES_MD',10,1);
1612 fnd_stats.gather_table_stats(l_errbuf, l_retcode,g_bis_owner,'EDW_HIERARCHY_LEVEL_MD',10,1);
1613 fnd_stats.gather_table_stats(l_errbuf, l_retcode,g_bis_owner,'EDW_LEVELS_MD',10,1);
1614 fnd_stats.gather_table_stats(l_errbuf, l_retcode,g_bis_owner,'EDW_LEVEL_ATTS_MD',10,1);
1615 fnd_stats.gather_table_stats(l_errbuf, l_retcode,g_bis_owner,'EDW_LEVEL_RELATIONS_MD',10,1);
1616 fnd_stats.gather_table_stats(l_errbuf, l_retcode,g_bis_owner,'EDW_LEVEL_TABLE_ATTS_MD',10,1);
1617 fnd_stats.gather_table_stats(l_errbuf, l_retcode,g_bis_owner,'EDW_RELATIONMAPPING_MD',10,1);
1618 fnd_stats.gather_table_stats(l_errbuf, l_retcode,g_bis_owner,'EDW_RELATIONS_MD',10,1);
1619 fnd_stats.gather_table_stats(l_errbuf, l_retcode,g_bis_owner,'EDW_TABLES_MD',10,1);
1620 fnd_stats.gather_table_stats(l_errbuf, l_retcode,g_bis_owner,'EDW_UNIQUE_KEYS_MD',10,1);
1621 fnd_stats.gather_table_stats(l_errbuf, l_retcode,g_bis_owner,'EDW_UNIQUE_KEY_COLUMNS_MD',10,1);
1622 ----pvt tables
1623 fnd_stats.gather_table_stats(l_errbuf, l_retcode,g_bis_owner,'EDW_PVT_COLUMNS_MD',10,1);
1624 fnd_stats.gather_table_stats(l_errbuf, l_retcode,g_bis_owner,'EDW_PVT_KEY_COLUMNS_MD',10,1);
1625 fnd_stats.gather_table_stats(l_errbuf, l_retcode,g_bis_owner,'EDW_PVT_LEVEL_RELATION_MD',10,1);
1626 fnd_stats.gather_table_stats(l_errbuf, l_retcode,g_bis_owner,'EDW_PVT_MAPPINGS_MD',10,1);
1627 fnd_stats.gather_table_stats(l_errbuf, l_retcode,g_bis_owner,'EDW_PVT_MAP_COLUMNS_MD',10,1);
1628 fnd_stats.gather_table_stats(l_errbuf, l_retcode,g_bis_owner,'edw_pvt_map_columns_tgt_md',10,1);
1629 fnd_stats.gather_table_stats(l_errbuf, l_retcode,g_bis_owner,'edw_pvt_map_columns_src_md',10,1);
1630 fnd_stats.gather_table_stats(l_errbuf, l_retcode,g_bis_owner,'EDW_PVT_MAP_FUNC_MD',10,1);
1631 fnd_stats.gather_table_stats(l_errbuf, l_retcode,g_bis_owner,'EDW_PVT_MAP_KEY_USAGES_MD',10,1);
1632 fnd_stats.gather_table_stats(l_errbuf, l_retcode,g_bis_owner,'EDW_PVT_MAP_PROPERTIES_MD',10,1);
1633 fnd_stats.gather_table_stats(l_errbuf, l_retcode,g_bis_owner,'EDW_PVT_MAP_SOURCES_MD',10,1);
1634 fnd_stats.gather_table_stats(l_errbuf, l_retcode,g_bis_owner,'EDW_PVT_MAP_TARGETS_MD',10,1);
1635 fnd_stats.gather_table_stats(l_errbuf, l_retcode,g_bis_owner,'EDW_PVT_SEQUENCES_MD',10,1);
1636 fnd_stats.gather_table_stats(l_errbuf, l_retcode,g_bis_owner,'EDW_PVT_VIEWS_MD',10,1);
1640 log('Error in analye_all '||sqlerrm);
1637 log('finish analyzing metadata tables.');
1638 log('system time is '|| fnd_date.date_to_displaydt (sysdate) );
1639 Exception when others then
1641 null;
1642 End;
1643
1644 function truncate_all return boolean is
1645 l_stmt varchar2(4000);
1646 Begin
1647 l_stmt := 'truncate table '||g_bis_owner||'.EDW_ALL_COLUMNS_MD';
1648 log(l_stmt||get_time);
1649 execute immediate l_stmt;
1650 l_stmt := 'truncate table '||g_bis_owner||'.EDW_ATTRIBUTE_SETS_MD';
1651 log(l_stmt||get_time);
1652 execute immediate l_stmt;
1653 l_stmt := 'truncate table '||g_bis_owner||'.EDW_ATTRIBUTE_SET_COLUMN_MD';
1654 log(l_stmt||get_time);
1655 execute immediate l_stmt;
1656 l_stmt := 'truncate table '||g_bis_owner||'.EDW_DIMENSIONS_MD';
1657 log(l_stmt||get_time);
1658 execute immediate l_stmt;
1659 l_stmt := 'truncate table '||g_bis_owner||'.EDW_DIM_ATTRIBUTES_MD';
1660 log(l_stmt||get_time);
1661 execute immediate l_stmt;
1662 l_stmt := 'truncate table '||g_bis_owner||'.EDW_FACTS_MD';
1663 log(l_stmt||get_time);
1664 execute immediate l_stmt;
1665 l_stmt := 'truncate table '||g_bis_owner||'.EDW_FACT_ATTRIBUTES_MD';
1666 log(l_stmt||get_time);
1667 execute immediate l_stmt;
1668 l_stmt := 'truncate table '||g_bis_owner||'.EDW_FACT_DIM_RELATIONS_MD';
1669 log(l_stmt||get_time);
1670 execute immediate l_stmt;
1671 l_stmt := 'truncate table '||g_bis_owner||'.EDW_FACT_HIER_MD';
1672 log(l_stmt||get_time);
1673 execute immediate l_stmt;
1674 l_stmt := 'truncate table '||g_bis_owner||'.EDW_FOREIGN_KEYS_MD';
1675 log(l_stmt||get_time);
1676 execute immediate l_stmt;
1677 l_stmt := 'truncate table '||g_bis_owner||'.EDW_FOREIGN_KEY_COLUMNS_MD';
1678 log(l_stmt||get_time);
1679 execute immediate l_stmt;
1680 l_stmt := 'truncate table '||g_bis_owner||'.EDW_HIERARCHIES_MD';
1681 log(l_stmt||get_time);
1682 execute immediate l_stmt;
1683 l_stmt := 'truncate table '||g_bis_owner||'.EDW_HIERARCHY_LEVEL_MD';
1684 log(l_stmt||get_time);
1685 execute immediate l_stmt;
1686 l_stmt := 'truncate table '||g_bis_owner||'.EDW_LEVELS_MD';
1687 log(l_stmt||get_time);
1688 execute immediate l_stmt;
1689 l_stmt := 'truncate table '||g_bis_owner||'.EDW_LEVEL_ATTS_MD';
1690 log(l_stmt||get_time);
1691 execute immediate l_stmt;
1692 l_stmt := 'truncate table '||g_bis_owner||'.EDW_LEVEL_RELATIONS_MD';
1693 log(l_stmt||get_time);
1694 execute immediate l_stmt;
1695 l_stmt := 'truncate table '||g_bis_owner||'.EDW_LEVEL_TABLE_ATTS_MD';
1696 log(l_stmt||get_time);
1697 execute immediate l_stmt;
1698 l_stmt := 'truncate table '||g_bis_owner||'.EDW_RELATIONMAPPING_MD';
1699 log(l_stmt||get_time);
1700 execute immediate l_stmt;
1701 l_stmt := 'truncate table '||g_bis_owner||'.EDW_RELATIONS_MD';
1702 log(l_stmt||get_time);
1703 execute immediate l_stmt;
1704 l_stmt := 'truncate table '||g_bis_owner||'.EDW_TABLES_MD';
1705 log(l_stmt||get_time);
1706 execute immediate l_stmt;
1707 l_stmt := 'truncate table '||g_bis_owner||'.EDW_UNIQUE_KEYS_MD';
1708 log(l_stmt||get_time);
1709 execute immediate l_stmt;
1710 l_stmt := 'truncate table '||g_bis_owner||'.EDW_UNIQUE_KEY_COLUMNS_MD';
1711 log(l_stmt||get_time);
1712 execute immediate l_stmt;
1713 l_stmt := 'truncate table '||g_bis_owner||'.EDW_PVT_COLUMNS_MD';
1714 log(l_stmt||get_time);
1715 execute immediate l_stmt;
1716 l_stmt := 'truncate table '||g_bis_owner||'.EDW_PVT_KEY_COLUMNS_MD';
1717 log(l_stmt||get_time);
1718 execute immediate l_stmt;
1719 l_stmt := 'truncate table '||g_bis_owner||'.EDW_PVT_LEVEL_RELATION_MD';
1720 log(l_stmt||get_time);
1721 execute immediate l_stmt;
1722 l_stmt := 'truncate table '||g_bis_owner||'.EDW_PVT_MAPPINGS_MD';
1723 log(l_stmt||get_time);
1724 execute immediate l_stmt;
1725 l_stmt := 'truncate table '||g_bis_owner||'.EDW_PVT_MAP_COLUMNS_MD';
1726 log(l_stmt||get_time);
1727 execute immediate l_stmt;
1728 l_stmt := 'truncate table '||g_bis_owner||'.edw_pvt_map_columns_tgt_md';
1729 log(l_stmt||get_time);
1730 execute immediate l_stmt;
1731 l_stmt := 'truncate table '||g_bis_owner||'.edw_pvt_map_columns_src_md';
1732 log(l_stmt||get_time);
1733 execute immediate l_stmt;
1734 l_stmt := 'truncate table '||g_bis_owner||'.EDW_PVT_MAP_FUNC_MD';
1735 log(l_stmt||get_time);
1736 execute immediate l_stmt;
1737 l_stmt := 'truncate table '||g_bis_owner||'.EDW_PVT_MAP_KEY_USAGES_MD';
1738 log(l_stmt||get_time);
1739 execute immediate l_stmt;
1740 l_stmt := 'truncate table '||g_bis_owner||'.EDW_PVT_MAP_PROPERTIES_MD';
1741 log(l_stmt||get_time);
1742 execute immediate l_stmt;
1743 l_stmt := 'truncate table '||g_bis_owner||'.EDW_PVT_MAP_SOURCES_MD';
1744 log(l_stmt||get_time);
1745 execute immediate l_stmt;
1746 l_stmt := 'truncate table '||g_bis_owner||'.EDW_PVT_MAP_TARGETS_MD';
1747 log(l_stmt||get_time);
1748 execute immediate l_stmt;
1749 l_stmt := 'truncate table '||g_bis_owner||'.EDW_PVT_SEQUENCES_MD';
1750 log(l_stmt||get_time);
1751 execute immediate l_stmt;
1752 l_stmt := 'truncate table '||g_bis_owner||'.EDW_PVT_VIEWS_MD';
1753 log(l_stmt||get_time);
1754 execute immediate l_stmt;
1755 log('All tables truncated '||get_time);
1756 return true;
1757 Exception when others then
1758 log('Error in truncate_all '||sqlerrm);
1759 return false;
1760 End;
1761
1762 procedure open_log_file is
1763 l_dir varchar2(2000);
1764 Begin
1765 l_dir:='bis';
1766 l_dir:=l_dir||'.'||'edw';
1767 l_dir:=l_dir||'.'||'metadata_refresh';
1768 EDW_OWB_COLLECTION_UTIL.init_all('MD_LOAD',null,l_dir);
1769 Exception when others then
1770 null;
1771 End;
1772
1773 function refresh_owb_mv return boolean is
1774 Begin
1775 dbms_snapshot.refresh(g_owb_schema||'.ALL_IV_XFORM_MAP_TARGETS');
1776 dbms_snapshot.refresh(g_owb_schema||'.ALL_IV_XFORM_MAP_SOURCES');
1777 return true;
1778 Exception when others then
1779 log('Error in refresh_owb_mv '||sqlerrm);
1780 return false;
1781 End;
1782
1783 END edw_metadata_refresh;