1 package body BSC_SYNC_MVLOGS AS
2 /*$Header: BSCMVLGB.pls 120.3 2006/02/09 14:11 arsantha noship $*/
3
4 FUNCTION get_apps_schema RETURN VARCHAR2 IS
5 l_apps_schema varchar2(30);
6 CURSOR c_get_apps_schema is
7 SELECT oracle_username
8 FROM fnd_oracle_userid
9 WHERE oracle_id between 900 and 999;
10 /* In 11i on any env this query will always return
11 one row */
12 BEGIN
13 IF ( c_get_apps_schema%ISOPEN ) THEN
14 CLOSE c_get_apps_schema;
15 END IF;
16 OPEN c_get_apps_schema;
17 FETCH c_get_apps_schema into l_apps_schema;
18 CLOSE c_get_apps_schema;
19 return l_apps_schema;
20 END;
21
22 function get_table_owner(p_table varchar2) return varchar2 is
23 l_owner varchar2(400);
24 l_stmt varchar2(4000);
25 cursor c1(p_table varchar2) is select table_owner from user_synonyms where synonym_name=p_table;
26 -----------------------------------
27 Begin
28 if instr(p_table,'.')<>0 then
29 l_owner:=substr(p_table,1,instr(p_table,'.')-1);
30 return l_owner;
31 end if;
32 open c1(p_table);
33 fetch c1 into l_owner;
34 close c1;
35 if l_owner is null then
36 -- owner is apps return apps schema name
37 l_owner := get_apps_schema;
38 end if;
39 return l_owner;
40 Exception when others then
41 return null;
42 End;
43
44 --given a level table with the user_code column size change
45 --we have to modify all the I tables used by objectives
46 procedure alter_objective_input_tables(p_level in varchar2, p_owner in varchar2) is
47 cursor cInputTables(p_level_col varchar2, p_datatype varchar2, p_data_length varchar2 ) is
48 select cols.table_name
49 from all_tab_columns cols
50 , bsc_db_tables tbls
51 where cols.owner=p_owner
52 and cols.table_name=tbls.table_name
53 and tbls.table_type=0
54 and cols.column_name =p_level_col
55 and (cols.data_type<>p_datatype or cols.data_length <>p_data_length)
56 ;
57
58 l_level_col varchar2(100);
59 l_datatype varchar2(100);
60 l_length number;
61
62 cursor cCol is
63 select level_pk_col, cols.data_type, cols.data_length
64 from bsc_sys_dim_levels_b lvl
65 , all_tab_columns cols
66 where lvl.level_table_name=p_level
67 and lvl.level_table_name = cols.table_name
68 and lvl.level_pk_col = cols.column_name
69 and cols.owner = p_owner;
70
71 l_datatype_with_length varchar2(100);
72
73 begin
74
75 open cCol;
76 fetch cCol into l_level_col, l_datatype, l_length;
77 close cCol;
78
79 if l_length is not null then
80 l_datatype_with_length := l_datatype||'('||l_length||')';
81 end if;
82 for i in cInputTables(l_level_col, l_datatype, l_length) loop
83 execute immediate 'alter table '||p_owner||'.'||i.table_name||' modify '||l_level_col||' '||l_datatype_with_length;
84 end loop;
85 end;
86
87
88 procedure alter_mv_logs(p_level in varchar2, p_owner in varchar2) is
89
90 cursor c_mv_log_name is
91 select distinct log_table
92 from all_snapshot_logs
93 where master = p_level and log_owner = p_owner;
94
95 cursor cCols(p_mv_log varchar2) is
96 select cols.column_name, cols.data_type, cols.data_length
97 from bsc_sys_dim_levels_b lvl
98 , all_tab_columns cols
99 where lvl.level_table_name = p_level
100 and lvl.level_table_name = cols.table_name
101 and cols.owner = p_owner
102 and cols.data_type = 'VARCHAR2'
103 and (cols.column_name = 'LANGUAGE' or cols.column_name = 'NAME'
104 OR cols.column_name in -- code
105 (select column_name
106 from bsc_sys_dim_level_cols lvlcols
107 where lvlcols.dim_level_id=lvl.dim_level_id
108 and column_type='P')
109 OR cols.column_name in -- fk code
110 (select relation_col
111 from bsc_Sys_dim_level_rels rels
112 where rels.dim_level_id=lvl.dim_level_id
113 and rels.relation_type=1)
114 )
115 minus
116 select column_name, data_type, data_length
117 from all_tab_columns
118 where table_name =(select distinct log_table from all_snapshot_logs where log_owner=p_owner and log_table=p_mv_log )
119 and owner=p_owner
120 and data_type='VARCHAR2';
121 l_datatype_with_length varchar2(100);
122
123 begin
124
125 for i in c_mv_log_name loop
126 for j in cCols(i.log_table) loop
127 if j.data_length is not null then
128 l_datatype_with_length := j.data_type||'('||j.data_length||')';
129 else
130 l_datatype_with_length := j.data_type;
131 end if;
132 begin
133 execute immediate 'alter materialized view log on '||p_owner||'.'||p_level||' modify '||j.column_name||' '||l_datatype_with_length;
134 exception when others then
135 if sqlcode = -904 then -- column doesnt exist, so add it
136 -- dont add NAME
137 if j.column_name<>'NAME' then
138 execute immediate 'alter materialized view log on '||p_owner||'.'||p_level||' add '||j.column_name||' '||l_datatype_with_length;
139 end if;
140 end if;
141 end;
142 end loop;
143 end loop;
144
145 end;
146
147 --Fix bug#4180632: new function to syncronize the mv log structure with the
148 -- structure of the dimension table
149 -- It was decided on April 05, 2005 after a conference call between
150 -- Ling, Arun, Vladimir and Venu that we should not drop the columns of
151 -- the MV log as it is not supported.
152 -- This sync api is called only for structural changes and all affected KPIs
153 -- are marked as 3 by PMD. so it safe to drop the MV log at this point
154 -- The GDB process will recreate the MV log only on the REQUIRED columns
155 -- for that KPI. If the MV log exists and the reqd column is not there
156 -- we can add the column to the MV log.
157
158 -- Nov 14, 2005, changing MV log behavior after discussion of bug 4630892 between
159 -- Patricia, Venu, Ling, and Arun.
160
161 function sync_dim_table_mv_log(
162 p_dim_table_name in varchar2,
163 p_error_message out nocopy varchar2
164 ) return boolean is
165 l_table_owner varchar2(80);
166 l_sql VARCHAR2(1000);
167 l_mv_log_name VARCHAR2(1000);
168 Begin
169 l_table_owner := null;
170 -- get dimension table mv log name
171 l_table_owner := get_table_owner(p_dim_table_name);
172
173 -- if the user_code column has changed in length, these must be
174 -- propagated to the Input tables also (BSC_I tables)
175
176 alter_objective_input_tables(p_dim_table_name, l_table_owner);
177
178 -- alter the mv logs to increase column size or include new columns if required
179 --not required for now as name is removed in the upgrade script
180 --and mv logs on dims have only code and fk_code both of which are numbers
181 alter_mv_logs(p_dim_table_name, l_table_owner);
182
183
184 return true;
185 Exception
186 when others then
187 p_error_message := sqlerrm;
188 return false;
189 End;
190
191
192 -- drop mv log, only called from upgrade
193
194 function drop_dim_table_mv_log(
195 p_dim_table_name in varchar2,
196 p_error_message out nocopy varchar2
197 ) return boolean is
198 ------------------------------------------
199 cursor c_mv_log_name (p_table_name varchar2, p_table_owner varchar2) is
200 select log_table
201 from all_snapshot_logs
202 where master = p_table_name and log_owner = p_table_owner;
203 ------------------------------------------
204 l_table_owner varchar2(80);
205 l_sql VARCHAR2(1000);
206 l_mv_log_name VARCHAR2(1000);
207 Begin
208 l_table_owner := null;
209 -- get dimension table mv log name
210 l_table_owner := get_table_owner(p_dim_table_name);
211
212 ------------------------------------------
213 open c_mv_log_name(p_dim_table_name, l_table_owner);
214 fetch c_mv_log_name into l_mv_log_name;
215 if c_mv_log_name%notfound then
216 -- no mv log created for this dimension
217 close c_mv_log_name;
218 return true;
219 end if;
220 ------------------------------------------
221
222 l_sql := 'drop materialized view log on ';
223 if (l_table_owner is not null) then
224 l_sql := l_sql||l_table_owner||'.';
225 end if;
226 l_sql := l_sql||p_dim_table_name;
227 execute immediate l_sql;
228 return true;
229 Exception
230 when others then
231 p_error_message := sqlerrm;
232 return false;
233 End;
234
235
236 END BSC_SYNC_MVLOGS;
[Home] [Help]