1 package body BSC_AW_LOAD AS
2 /*$Header: BSCAWLOB.pls 120.7 2006/04/20 11:27 vsurendr noship $*/
3 /*
4 the top package to handle all AW data loads, aggregations and forecasts
5 handles dim loads and kpi loads
6 */
7
8 /*
9 load dim handles aw dim loads. input to this procedure is a list of dim and options
10 */
11 procedure load_dim(
12 p_dim_level_list dbms_sql.varchar2_table,
13 p_options varchar2
14 ) is
15 Begin
16 bsc_aw_utility.g_options.delete;
17 bsc_aw_utility.parse_parameter_values(p_options,',',bsc_aw_utility.g_options);
18 bsc_aw_utility.open_file('TEST');
19 bsc_aw_utility.dmp_g_options(bsc_aw_utility.g_options);
20 init_all;
21 bsc_aw_adapter.upgrade(p_options);/*serial across all processes. we call upgrade(p_options) to invoke workspace attach in that api*/
22 bsc_aw_load_dim.load_dim(p_dim_level_list);
23 bsc_aw_management.detach_workspace;
24 Exception when others then
25 bsc_aw_management.detach_workspace;
26 rollback;
27 log_n('Exception in load_dim '||sqlerrm);
28 raise;
29 End;
30
31 /*
32 the aw dim corresponding to the level being purged is completely cleaned up, not just the level. if city is to
33 be purged, geog dim and all its levels will be purged.
34
35 NOTE!!! when dim is purged, all related kpi are completely purged. if a dim is to be removed from a kpi, the kpi has
36 to be recreated. so if a dim is purged, data in the kpi makes no sense anymore
37 */
38 procedure purge_dim(
39 p_dim_level_list dbms_sql.varchar2_table,
40 p_options varchar2
41 ) is
42 Begin
43 bsc_aw_utility.g_options.delete;
44 bsc_aw_utility.parse_parameter_values(p_options,',',bsc_aw_utility.g_options);
45 bsc_aw_utility.open_file('TEST');
46 bsc_aw_utility.dmp_g_options(bsc_aw_utility.g_options);
47 init_all;
48 bsc_aw_load_dim.purge_dim(p_dim_level_list);
49 bsc_aw_management.detach_workspace;
50 Exception when others then
51 bsc_aw_management.detach_workspace;
52 rollback;
53 log_n('Exception in purge_dim '||sqlerrm);
54 raise;
55 End;
56
57 /*
58 this procedure will dmp the dim level data into table bsc_aw_dim_data
59 used for bis dimensions that are not materialized. bsc loader needs the dim values
60 to know which values have got deleted
61
62 this creates the program on the fly, executes it and drops the program
63 NO COMMIT!!!
64 */
65 procedure dmp_dim_level_into_table(
66 p_dim_level_list dbms_sql.varchar2_table,
67 p_options varchar2
68 ) is
69 Begin
70 bsc_aw_utility.g_options.delete;
71 bsc_aw_utility.parse_parameter_values(p_options,',',bsc_aw_utility.g_options);
72 bsc_aw_utility.open_file('TEST');
73 bsc_aw_utility.dmp_g_options(bsc_aw_utility.g_options);
74 init_all;
75 bsc_aw_management.get_workspace_lock('ro',null);
76 bsc_aw_load_dim.dmp_dim_level_into_table(p_dim_level_list);
77 bsc_aw_management.detach_workspace;
78 Exception when others then
79 bsc_aw_management.detach_workspace;
80 rollback;
81 log_n('Exception in dmp_dim_level_into_table '||sqlerrm);
82 raise;
83 End;
84
85 /*
86 for kpi, there are 2 ways to load
87 1 load a kpi
88 2 load base table and kpi associated with them
89 */
90
91 procedure load_kpi(
92 p_kpi_list dbms_sql.varchar2_table,
93 p_options varchar2
94 ) is
95 Begin
96 bsc_aw_utility.g_options.delete;
97 bsc_aw_utility.parse_parameter_values(p_options,',',bsc_aw_utility.g_options);
98 bsc_aw_utility.open_file('TEST');
99 bsc_aw_utility.dmp_g_options(bsc_aw_utility.g_options);
100 init_all;
101 bsc_aw_adapter.upgrade(p_options);/*serial across all processes*/
102 bsc_aw_load_kpi.load_kpi(p_kpi_list);
103 bsc_aw_management.detach_workspace;
104 Exception when others then
105 bsc_aw_management.detach_workspace;
106 rollback;
107 log_n('Exception in load_kpi '||sqlerrm);
108 raise;
109 End;
110
111 /*
112 p_base_table_list and p_kpi_list are 1 to 1. the entries can look as
113 BSC_B_1 3014
114 BSC_B_1 4000
115 BSC_B_2 3014
116 */
117 procedure load_base_table(
118 p_base_table_list dbms_sql.varchar2_table,
119 p_kpi_list dbms_sql.varchar2_table,
120 p_options varchar2
121 ) is
122 Begin
123 bsc_aw_utility.g_options.delete;
124 bsc_aw_utility.parse_parameter_values(p_options,',',bsc_aw_utility.g_options);
125 bsc_aw_utility.open_file('TEST');
126 bsc_aw_utility.dmp_g_options(bsc_aw_utility.g_options);
127 init_all;
128 bsc_aw_adapter.upgrade(p_options);/*serial across all processes*/
129 bsc_aw_load_kpi.load_base_table(p_base_table_list,p_kpi_list);
130 bsc_aw_management.detach_workspace;
131 Exception when others then
132 bsc_aw_management.detach_workspace;
133 rollback;
134 log_n('Exception in load_base_table '||sqlerrm);
135 raise;
136 End;
137
138 procedure purge_kpi(p_kpi varchar2,p_options varchar2) is
139 Begin
140 bsc_aw_utility.g_options.delete;
141 bsc_aw_utility.parse_parameter_values(p_options,',',bsc_aw_utility.g_options);
142 bsc_aw_utility.open_file('TEST');
143 bsc_aw_utility.dmp_g_options(bsc_aw_utility.g_options);
144 init_all;
145 bsc_aw_load_kpi.purge_kpi(p_kpi);
146 bsc_aw_management.detach_workspace;
147 Exception when others then
148 bsc_aw_management.detach_workspace;
149 rollback;
150 log_n('Exception in purge_kpi '||sqlerrm);
151 raise;
152 End;
153
154 /*
155 pass a kpi. this will loop over all dimset, all dim and levels. it eill create tables
156 as p_table_name||dimset||1,2 etc. then these table names will be returned in p_tables
157 */
158 procedure dmp_kpi_cubes_into_table(
159 p_kpi varchar2,
160 p_table_name varchar2,
161 p_options varchar2,
162 p_tables out nocopy dbms_sql.varchar2_table
163 ) is
164 Begin
165 bsc_aw_utility.g_options.delete;
166 bsc_aw_utility.parse_parameter_values(p_options,',',bsc_aw_utility.g_options);
167 bsc_aw_utility.open_file('TEST');
168 bsc_aw_utility.dmp_g_options(bsc_aw_utility.g_options);
169 init_all;
170 --read lock only
171 bsc_aw_management.get_workspace_lock('ro',null);
172 bsc_aw_load_kpi.dmp_kpi_cubes_into_table(p_kpi,p_table_name,p_tables);
173 bsc_aw_management.detach_workspace;
174 Exception when others then
175 bsc_aw_management.detach_workspace;
176 rollback;
177 log_n('Exception in dmp_kpi_cubes_into_table '||sqlerrm);
178 raise;
179 End;
180
181 procedure dmp_kpi_cubes_into_table(
182 p_kpi varchar2,
183 p_dimset varchar2,
184 p_dim_levels dbms_sql.varchar2_table,
185 p_table_name varchar2,
186 p_options varchar2) is
187 Begin
188 bsc_aw_utility.g_options.delete;
189 bsc_aw_utility.parse_parameter_values(p_options,',',bsc_aw_utility.g_options);
190 bsc_aw_utility.open_file('TEST');
191 bsc_aw_utility.dmp_g_options(bsc_aw_utility.g_options);
192 init_all;
193 --read lock only
194 bsc_aw_management.get_workspace_lock('ro',null);
195 bsc_aw_load_kpi.dmp_kpi_cubes_into_table(p_kpi,p_dimset,p_dim_levels,p_table_name);
196 bsc_aw_management.detach_workspace;
197 Exception when others then
198 bsc_aw_management.detach_workspace;
199 rollback;
200 log_n('Exception in dmp_kpi_cubes_into_table '||sqlerrm);
201 raise;
202 End;
203
204 /*
205 for base table change vector management
206 we will handle inc load using the change vector column in the base table. we will not have the b_aw table anymore
207 we hold meradata on the latest change vector for a base table. to begin, its 0. when loader loads from I table,
208 calculates projections etc, it will load the value it gets from get_bt_next_change_vector into the change vector
209 ...
210 init_bt_change_vector('BSC_B_1');
211 l_cv:=get_bt_next_change_vector('BSC_B_1');
212 ...
213 load from I -> B
214 commit
215 ...
216 projections
217 commit
218 ...
219 update_bt_change_vector('BSC_B_1',l_cv);
220 commit;
221 truncate I table
222
223 init_bt_change_vector will create a metadata entry for B table if it does not exist
224 we cannot have a situation where change_vector column in B table is null
225 if the change vector in the B table > in the metadata, it is due to some loader failure before update_bt_change_vector
226 was called
227 when a B table is dropped by the MO, it needs to call drop_bt_change_vector
228 */
229 procedure init_bt_change_vector(p_base_table varchar2) is
230 Begin
231 bsc_aw_md_api.create_bt_change_vector(upper(p_base_table)); /*creates cv and current period */
232 Exception when others then
233 log_n('Exception in init_bt_change_vector '||sqlerrm);
234 raise;
235 End;
236
237 procedure drop_bt_change_vector(p_base_table varchar2) is
238 Begin
239 bsc_aw_md_api.drop_bt_change_vector(upper(p_base_table)); /*drops cv and cp */
240 Exception when others then
241 log_n('Exception in drop_bt_change_vector '||sqlerrm);
242 raise;
243 End;
244
245 function get_bt_next_change_vector(p_base_table varchar2) return number is
246 l_value number;
247 Begin
248 l_value:=bsc_aw_md_api.get_bt_change_vector(upper(p_base_table))+1;
249 return l_value;
250 Exception when others then
251 log_n('Exception in get_bt_next_change_vector '||sqlerrm);
252 raise;
253 End;
254
255 --for base table change vector management
256 procedure update_bt_change_vector(p_base_table varchar2, p_value number) is
257 Begin
258 bsc_aw_md_api.update_bt_change_vector(upper(p_base_table),p_value);
259 Exception when others then
260 log_n('Exception in update_bt_change_vector '||sqlerrm);
261 raise;
262 End;
263
264 /*to set the current period of the B table , p_value is period.year format at the periodicity of the B table
265 we need this value to set projection and balance aggregations on time to null when the cp moves forward*/
266 procedure update_bt_current_period(p_base_table varchar2,p_period number,p_year number) is
267 Begin
268 bsc_aw_md_api.update_bt_current_period(upper(p_base_table),p_period||'.'||p_year);
269 Exception when others then
270 log_n('Exception in update_bt_current_period '||sqlerrm);
271 raise;
272 End;
273
274 --------------------------------------------------------
275 procedure init_all is
276 Begin
277 if bsc_aw_utility.get_parameter_value(bsc_aw_utility.g_options,'DEBUG LOG')='Y'
278 or bsc_aw_utility.g_log_level>=FND_LOG.G_CURRENT_RUNTIME_LEVEL then
279 g_debug:=true;
280 else
281 g_debug:=false;
282 end if;
283 bsc_aw_utility.init_all(g_debug);
284 bsc_aw_load_dim.init_all;
285 bsc_aw_load_kpi.init_all;
286 bsc_aw_dbms_aw.init_all;
287 bsc_aw_md_api.init_all;
288 bsc_aw_md_wrapper.init_all;
289 bsc_aw_management.init_all;
290 Exception when others then
291 log_n('Exception in init_all '||sqlerrm);
292 raise;
293 End;
294
295 procedure log(p_message varchar2) is
296 Begin
297 bsc_aw_utility.log(p_message);
298 Exception when others then
299 null;
300 End;
301
302 procedure log_n(p_message varchar2) is
303 Begin
304 log(' ');
305 log(p_message);
306 Exception when others then
307 null;
308 End;
309
310 END BSC_AW_LOAD;
[Home] [Help]