[Home] [Help]
1 package body BSC_AW_ADAPTER AS
2 /*$Header: BSCAWAPB.pls 120.14 2006/04/20 11:49 vsurendr noship $*/
3
4 /*
5 implement_kpi_aw is the top procedure. optimizer calls this api, passes a list of kpi to be implemented
6 in aw. also is passed options like debug log or any other parameter
7
8 p_options:
9 RECREATE DIM : will recreate dim except std dim
10 RECREATE STD DIM : will recreate std dim
11 RECREATE KPI : will recreate kpi
12 DEBUG LOG : turns on logging
13 TABLESPACE : which tablespace to create aw workspace
14 SEGMENTSIZE : segment size to use
15 */
16 procedure implement_kpi_aw(
17 p_kpi_list dbms_sql.varchar2_table,
18 p_options varchar2
19 ) is
20 --
21 l_affected_kpi dbms_sql.varchar2_table;
22 Begin
23 implement_kpi_aw(p_kpi_list,p_options,l_affected_kpi);
24 Exception when others then
25 log_n('Exception in implement_kpi_aw '||sqlerrm);
26 raise;
27 End;
28
29 procedure implement_kpi_aw(
30 p_kpi_list dbms_sql.varchar2_table,
31 p_options varchar2,
32 p_affected_kpi out nocopy dbms_sql.varchar2_table
33 ) is
34 l_options varchar2(8000);
35 Begin
36 l_options:='create workspace,'||p_options;
37 upgrade(l_options);
38 set_up(l_options);
39 bsc_aw_management.get_workspace_lock('rw',l_options);
40 implement_kpi_aw(p_kpi_list,p_affected_kpi);
41 bsc_aw_management.commit_aw;
42 bsc_aw_md_api.analyze_md_tables;
43 commit;
44 bsc_aw_management.detach_workspace;
45 Exception when others then
46 bsc_aw_management.detach_workspace;
47 rollback;
48 log_n('Exception in implement_kpi_aw '||sqlerrm);
49 raise;
50 End;
51
52 procedure implement_kpi_aw(
53 p_kpi_list dbms_sql.varchar2_table,
54 p_affected_kpi out nocopy dbms_sql.varchar2_table
55 ) is
56 --
57 l_dim_list dbms_sql.varchar2_table;
58 l_calendar number;
59 l_affected_kpi dbms_sql.varchar2_table;
60 l_calendar_processed dbms_sql.number_table;
61 Begin
62 --drop the kpi that are going to be processed
63 drop_kpi(p_kpi_list);
64 --for the list of kpi, get the dim list...then call the dim adapter
65 --l_dim_list will be the BSC dim levels
66 bsc_aw_bsc_metadata.get_dims_for_kpis(p_kpi_list,l_dim_list);
67 bsc_aw_adapter_dim.create_dim(l_dim_list,p_affected_kpi);
68 --check to see if we need to create calendar
69 --create calendar will only create if needed
70 if bsc_aw_utility.g_debug then
71 bsc_aw_calendar.g_debug:=true;
72 end if;
73 for i in 1..p_kpi_list.count loop
74 bsc_metadata.get_kpi_calendar(p_kpi_list(i),l_calendar);
75 l_affected_kpi.delete;
76 if bsc_aw_utility.in_array(l_calendar_processed,l_calendar)=false then
77 bsc_aw_calendar.create_calendar(l_calendar,l_affected_kpi);
78 bsc_aw_utility.merge_array(p_affected_kpi,l_affected_kpi);
79 l_calendar_processed(l_calendar_processed.count+1):=l_calendar;
80 end if;
81 end loop;
82 --
83 bsc_aw_adapter_kpi.create_kpi(p_kpi_list);
84 bsc_aw_utility.subtract_array(p_affected_kpi,p_kpi_list);
85 bsc_aw_utility.dmp_values(p_affected_kpi,'Affected KPIs');
86 Exception when others then
87 rollback;
88 log_n('Exception in implement_kpi_aw '||sqlerrm);
89 raise;
90 End;
91
92 /*
93 this procedure drops the kpi objects from aw, relational and also cleans up the olap metadata
94 should not error if the kpi does not exist
95 when we drop kpi, we must also process the dim. we can have a case like
96 city,state, country. one kpi on all three. this kpi is dropped. we have to correct the existing dim to city
97 state. if the kpi is dropped and we do not correct the dim, we can have issues later when country is deleted.
98 */
99 procedure drop_kpi(p_kpi_list dbms_sql.varchar2_table,p_options varchar2) is
100 --
101 l_dim_list dbms_sql.varchar2_table;
102 Begin
103 set_up(p_options);
104 bsc_aw_management.get_workspace_lock('rw',p_options);
105 bsc_aw_bsc_metadata.get_dims_for_kpis(p_kpi_list,l_dim_list);
106 bsc_aw_adapter_dim.create_dim(l_dim_list);
107 drop_kpi(p_kpi_list);
108 bsc_aw_management.commit_aw;
109 bsc_aw_management.detach_workspace;
110 commit;
111 Exception when others then
112 bsc_aw_management.detach_workspace;
113 rollback;
114 log_n('Exception in drop_kpi '||sqlerrm);
115 raise;
116 End;
117
118 procedure drop_kpi(p_kpi_list dbms_sql.varchar2_table) is
119 Begin
120 for i in 1..p_kpi_list.count loop
121 bsc_aw_adapter_kpi.drop_kpi_objects(p_kpi_list(i));
122 end loop;
123 Exception when others then
124 log_n('Exception in drop_kpi '||sqlerrm);
125 raise;
126 End;
127
128 /*
129 this procedure lets anyone create a dim or recreate a dim
130 */
131 procedure create_dim(
132 p_dim_level_list dbms_sql.varchar2_table,
133 p_options varchar2
134 ) is
135 l_options varchar2(8000);
136 Begin
137 l_options:='create workspace,'||p_options;
138 upgrade(l_options);
139 set_up(l_options);
140 bsc_aw_management.get_workspace_lock('rw',l_options);
141 bsc_aw_adapter_dim.create_dim(p_dim_level_list);
142 bsc_aw_management.commit_aw;
143 commit;
144 bsc_aw_management.detach_workspace;
145 Exception when others then
146 bsc_aw_management.detach_workspace;
147 rollback;
148 log_n('Exception in create_dim '||sqlerrm);
149 raise;
150 End;
151
152 /*handles cal, dim and kpi upgrade
153 process is serial
154 must be careful with cache. once the upgrade version is updated back in the system, then only must other process read
155 olap metadata*/
156 procedure upgrade(p_options varchar2) is /*call from upgrade script etc */
157 l_old_upgrade_version number;
158 Begin
159 bsc_aw_utility.get_db_lock('bsc_aw_system_upgrade');
160 set_up(p_options);
161 bsc_aw_md_api.clear_all_cache;
162 l_old_upgrade_version:=bsc_aw_md_api.get_upgrade_version;
163 log('System Upgrade, New version='||bsc_aw_utility.g_upgrade_version||', Old version='||l_old_upgrade_version);
164 if bsc_aw_utility.g_upgrade_version>l_old_upgrade_version then
165 bsc_aw_management.get_workspace_lock('rw',p_options);
166 upgrade(bsc_aw_utility.g_upgrade_version,l_old_upgrade_version);
167 bsc_aw_management.commit_aw;
168 commit;
169 bsc_aw_management.detach_workspace;
170 end if;
171 bsc_aw_utility.release_db_lock('bsc_aw_system_upgrade');
172 Exception when others then
173 rollback;
174 bsc_aw_utility.release_db_lock('bsc_aw_system_upgrade');
175 bsc_aw_management.detach_workspace;
176 log_n('Exception in upgrade '||sqlerrm);
177 raise;
178 End;
179
180 procedure upgrade(p_new_version number,p_old_version number) is
181 Begin
182 /*cal upgrade*/
183 bsc_aw_calendar.upgrade(p_new_version,p_old_version);
184 /*dim upgrade*/
188 /*update the latest upgrade version */
185 bsc_aw_adapter_dim.upgrade(p_new_version,p_old_version);
186 /*kpi upgrade*/
187 bsc_aw_adapter_kpi.upgrade(p_new_version,p_old_version);
189 bsc_aw_md_api.set_upgrade_version(p_new_version);
190 Exception when others then
191 log_n('Exception in upgrade '||sqlerrm);
192 raise;
193 End;
194
195 --------------------------------------------
196
197 procedure set_up(p_options varchar2) is
198 Begin
199 bsc_aw_utility.open_file('TEST');
200 bsc_aw_utility.g_options.delete;
201 bsc_aw_utility.parse_parameter_values(p_options,',',bsc_aw_utility.g_options);
202 bsc_aw_utility.dmp_g_options(bsc_aw_utility.g_options);
203 init_all;
204 Exception when others then
205 rollback;
206 log_n('Exception in set_up '||sqlerrm);
207 raise;
208 End;
209
210 procedure init_all is
211 Begin
212 --set g_adv_sum_profile
213 g_adv_sum_profile:=0;
214 /*serialize entry here */
215 bsc_aw_utility.get_db_lock('bsc_aw_table_create_lock');
216 bsc_aw_utility.create_temp_tables;
217 bsc_aw_utility.create_perm_tables;
218 bsc_aw_utility.release_db_lock('bsc_aw_table_create_lock');
219 /* */
220 if bsc_aw_utility.get_parameter_value(bsc_aw_utility.g_options,'DEBUG LOG')='Y'
221 or bsc_aw_utility.g_log_level>=FND_LOG.G_CURRENT_RUNTIME_LEVEL then
222 g_debug:=true;
223 else
224 g_debug:=false;
225 end if;
226 /* */
227 bsc_aw_utility.init_all(g_debug);
228 bsc_aw_adapter_dim.init_all;
229 bsc_aw_adapter_kpi.init_all;
230 bsc_aw_load_dim.init_all;
231 bsc_aw_load_kpi.init_all;
232 bsc_aw_dbms_aw.init_all;
233 bsc_aw_md_api.init_all;
234 bsc_aw_md_wrapper.init_all;
235 bsc_aw_bsc_metadata.init_all;
236 bsc_metadata.init_all;
237 bsc_aw_management.init_all;
238 g_init:=true;
239 Exception when others then
240 rollback;
241 log_n('Exception in init_all '||sqlerrm);
242 raise;
243 End;
244
245 procedure log(p_message varchar2) is
246 Begin
247 bsc_aw_utility.log(p_message);
248 Exception when others then
249 null;
250 End;
251
252 procedure log_n(p_message varchar2) is
253 Begin
254 log(' ');
255 log(p_message);
256 Exception when others then
257 null;
258 End;
259
260 END BSC_AW_ADAPTER;
|
|
|
|