1 package body BSC_AW_CALENDAR AS
2 /*$Header: BSCAWCAB.pls 120.18 2006/05/31 20:52:09 vsurendr ship $*/
3
4 /*
5 given a calendar id, create the aw objects for the calendar.
6 This module will directly read the BSC calendars. will not go through bsc_metadata package
7 This procedure is called when the calendar has to be created...this is first time
8 or when there is a change to the calendar structure itself.
9 if the calendar is extended or the start date changes, this procedure is not called. we simply refresh the
10 calendar load program
11 */
12 procedure create_calendar(p_calendar number,p_options varchar2) is
13 l_affected_kpi dbms_sql.varchar2_table;
14 Begin
15 create_calendar(p_calendar,p_options,l_affected_kpi);
16 Exception when others then
17 log_n('Exception in create_calendar '||sqlerrm);
18 raise;
19 End;
20
21 procedure create_calendar(
22 p_calendar number,
23 p_options varchar2,
24 p_affected_kpi out nocopy dbms_sql.varchar2_table
25 ) is
26 Begin
27 if p_options is not null then
28 bsc_aw_utility.parse_parameter_values(p_options,',',bsc_aw_utility.g_options);
29 bsc_aw_utility.open_file('TEST');
30 bsc_aw_utility.dmp_g_options(bsc_aw_utility.g_options);
31 end if;
32 init_all;
33 attach_workspace(p_options);
34 create_calendar(p_calendar,p_affected_kpi);
35 --
36 bsc_aw_management.commit_aw;
37 commit;
38 if nvl(bsc_aw_utility.get_parameter_value('NO DETACH WORKSPACE'),'N')='N' then
39 bsc_aw_management.detach_workspace;
40 end if;
41 Exception when others then
42 bsc_aw_management.detach_workspace;
43 log_n('Exception in create_calendar '||sqlerrm);
44 raise;
45 End;
46
47 procedure create_calendar(p_calendar number,p_affected_kpi out nocopy dbms_sql.varchar2_table) is
48 l_calendar calendar_r;
49 l_create_objects boolean;
50 l_create_program boolean;
51 l_recreate varchar2(40);
52 Begin
53 l_create_objects:=false;
54 l_create_program:=false;
55 l_calendar.calendar_id:=p_calendar;
56 get_bsc_calendar_data(l_calendar);
57 set_aw_object_names(l_calendar);
58 normalize_per_relation(l_calendar);
59 get_kpi_for_calendar(l_calendar);
60 set_calendar_properties(l_calendar);
61 if g_debug then
62 dmp_calendar(l_calendar);
63 end if;
64 check_calendar_create(l_calendar,l_recreate,p_affected_kpi);
65 --if there is no change, then l_recreate is null
66 if l_recreate='create all' then
67 l_create_objects:=true;
68 l_create_program:=true;
69 elsif l_recreate='create program' then
70 l_create_program:=true;
71 end if;
72 bsc_aw_utility.add_sqlerror(-34340,'ignore',null);
73 bsc_aw_utility.add_sqlerror(-36656,'ignore',null);
74 if l_create_objects then
75 create_calendar_objects(l_calendar);
76 end if;
77 if l_create_objects or l_create_program then
78 create_calendar_program(l_calendar);
79 end if;
80 if l_create_objects then
81 create_calendar_metadata(l_calendar);--create in bsc_olap metadata
82 end if;
83 bsc_aw_utility.remove_sqlerror(-34340,'ignore');
84 bsc_aw_utility.remove_sqlerror(-36656,'ignore');
85 Exception when others then
86 log_n('Exception in create_calendar '||sqlerrm);
87 raise;
88 End;
89
90 /*
91 this sets the properties like dim type=time, multi level etc
92 */
93 procedure set_calendar_properties(p_calendar in out nocopy calendar_r) is
94 Begin
95 p_calendar.property:='calendar='||p_calendar.calendar_id||',dimension type=time,multi level,relation name='||
96 p_calendar.relation_name||',denorm relation name='||p_calendar.denorm_relation_name||
97 ',end period relation name='||p_calendar.end_period_relation_name||',level name dim='||p_calendar.levels_name;
98 Exception when others then
99 log_n('Exception in set_calendar_properties '||sqlerrm);
100 raise;
101 End;
102
103 procedure get_bsc_calendar_data(
104 p_calendar in out nocopy calendar_r) is
105 --
106 cursor c1(p_calendar number) is
107 select db_column_name,periodicity_id,periodicity_type,source from bsc_sys_periodicities where calendar_id=p_calendar
108 and periodicity_type not in (11,12);
109 --
110 l_count number;
111 Begin
112 l_count:=1;
113 open c1(p_calendar.calendar_id);
114 loop
115 fetch c1 into p_calendar.periodicity(l_count).db_column_name,
116 p_calendar.periodicity(l_count).periodicity_id,p_calendar.periodicity(l_count).periodicity_type,
117 p_calendar.periodicity(l_count).source;
118 exit when c1%notfound;
119 l_count:=l_count+1;
120 end loop;
121 Exception when others then
122 log_n('Exception in get_bsc_calendar_data '||sqlerrm);
123 raise;
124 End;
125
126 procedure set_aw_object_names(p_calendar in out nocopy calendar_r) is
127 Begin
128 for i in 1..p_calendar.periodicity.count loop
129 p_calendar.periodicity(i).dim_name:=nvl(lower(p_calendar.periodicity(i).db_column_name),'per')||'_'||
130 p_calendar.periodicity(i).periodicity_id||'_cal_'||p_calendar.calendar_id;
131 end loop;
132 --concat dim
133 p_calendar.dim_name:=get_calendar_name(p_calendar.calendar_id);
134 p_calendar.relation_name:=get_calendar_name(p_calendar.calendar_id)||'.rel';
135 p_calendar.denorm_relation_name:=p_calendar.relation_name||'.denorm';
136 p_calendar.end_period_relation_name:=get_calendar_name(p_calendar.calendar_id)||'.rel.end_period';
137 p_calendar.levels_name:=get_calendar_name(p_calendar.calendar_id)||'.levels';
138 p_calendar.end_period_levels_name:=get_calendar_name(p_calendar.calendar_id)||'.end_period_levels';
139 --we create the relations between the bsc periodicities and std periodicities
140 for i in 1..p_calendar.periodicity.count loop
141 if p_calendar.periodicity(i).periodicity_type=9 then
142 p_calendar.periodicity(i).aw_time_dim_name:='aw_day_cal_'||p_calendar.calendar_id;
143 p_calendar.periodicity(i).aw_bsc_aw_rel_name:=p_calendar.periodicity(i).dim_name||'.aw_day.rel'; --given bsc, what is aw?
144 p_calendar.periodicity(i).aw_aw_bsc_rel_name:='aw_day.bsc_calendar_'||p_calendar.calendar_id||'.rel';--given aw, what is bsc time?
145 elsif p_calendar.periodicity(i).periodicity_type=7 then
146 p_calendar.periodicity(i).aw_time_dim_name:='aw_week_cal_'||p_calendar.calendar_id;
147 p_calendar.periodicity(i).aw_bsc_aw_rel_name:=p_calendar.periodicity(i).dim_name||'.aw_week.rel'; --given bsc, what is aw?
148 p_calendar.periodicity(i).aw_aw_bsc_rel_name:='aw_week.bsc_calendar_'||p_calendar.calendar_id||'.rel';--given aw, what is bsc time?
149 elsif p_calendar.periodicity(i).periodicity_type=5 then
150 p_calendar.periodicity(i).aw_time_dim_name:='aw_month_cal_'||p_calendar.calendar_id;
151 p_calendar.periodicity(i).aw_bsc_aw_rel_name:=p_calendar.periodicity(i).dim_name||'.aw_month.rel'; --given bsc, what is aw?
152 p_calendar.periodicity(i).aw_aw_bsc_rel_name:='aw_month.bsc_calendar_'||p_calendar.calendar_id||'.rel';--given aw, what is bsc time?
153 elsif p_calendar.periodicity(i).periodicity_type=3 then
154 p_calendar.periodicity(i).aw_time_dim_name:='aw_quarter_cal_'||p_calendar.calendar_id;
155 p_calendar.periodicity(i).aw_bsc_aw_rel_name:=p_calendar.periodicity(i).dim_name||'.aw_quarter.rel'; --given bsc, what is aw?
156 p_calendar.periodicity(i).aw_aw_bsc_rel_name:='aw_quarter.bsc_calendar_'||p_calendar.calendar_id||'.rel';--given aw, what is bsc time?
157 elsif p_calendar.periodicity(i).periodicity_type=1 then
158 p_calendar.periodicity(i).aw_time_dim_name:='aw_year_cal_'||p_calendar.calendar_id;
159 p_calendar.periodicity(i).aw_bsc_aw_rel_name:=p_calendar.periodicity(i).dim_name||'.aw_year.rel'; --given bsc, what is aw?
160 p_calendar.periodicity(i).aw_aw_bsc_rel_name:='aw_year.bsc_calendar_'||p_calendar.calendar_id||'.rel';--given aw, what is bsc time?
161 end if;
162 end loop;
163 --define cal.period and cal.year. this will be used in the olap table function views
164 p_calendar.misc_object(p_calendar.misc_object.count+1).object_name:='period_cal_'||p_calendar.calendar_id;
165 p_calendar.misc_object(p_calendar.misc_object.count).object_type:='variable';
166 p_calendar.misc_object(p_calendar.misc_object.count).datatype:='number';
167 --
168 p_calendar.misc_object(p_calendar.misc_object.count+1).object_name:='year_cal_'||p_calendar.calendar_id;
169 p_calendar.misc_object(p_calendar.misc_object.count).object_type:='variable';
170 p_calendar.misc_object(p_calendar.misc_object.count).datatype:='number';
171 --
172 p_calendar.misc_object(p_calendar.misc_object.count+1).object_name:='periodicity_cal_'||p_calendar.calendar_id;
173 p_calendar.misc_object(p_calendar.misc_object.count).object_type:='variable';
174 p_calendar.misc_object(p_calendar.misc_object.count).datatype:='number';
175 --
176 Exception when others then
177 log_n('Exception in set_aw_object_names '||sqlerrm);
178 raise;
179 End;
180
181 procedure create_calendar_objects(
182 p_calendar in out nocopy calendar_r) is
183 --
184 Begin
185 --create the objects
186 --create the aw std periodicities
187 g_stmt:='dfn aw_day_cal_'||p_calendar.calendar_id||' dimension day';
188 bsc_aw_dbms_aw.execute(g_stmt);
189 g_stmt:='dfn aw_week_cal_'||p_calendar.calendar_id||' dimension week ending sunday';
190 bsc_aw_dbms_aw.execute(g_stmt);
191 g_stmt:='dfn aw_month_cal_'||p_calendar.calendar_id||' dimension month';
192 bsc_aw_dbms_aw.execute(g_stmt);
193 g_stmt:='dfn aw_quarter_cal_'||p_calendar.calendar_id||' dimension quarter';
194 bsc_aw_dbms_aw.execute(g_stmt);
195 g_stmt:='dfn aw_year_cal_'||p_calendar.calendar_id||' dimension year';
196 bsc_aw_dbms_aw.execute(g_stmt);
197 --
198 for i in 1..p_calendar.periodicity.count loop
199 g_stmt:='dfn '||p_calendar.periodicity(i).dim_name||' dimension text';
200 bsc_aw_dbms_aw.execute(g_stmt);
201 end loop;
202 --
203 g_stmt:='dfn '||p_calendar.dim_name||' dimension concat(';
204 for i in 1..p_calendar.periodicity.count loop
205 g_stmt:=g_stmt||p_calendar.periodicity(i).dim_name||',';
206 end loop;
207 g_stmt:=substr(g_stmt,1,length(g_stmt)-1)||')';
208 bsc_aw_dbms_aw.execute(g_stmt);
209 --to merge new periodicities
210 for i in 1..p_calendar.periodicity.count loop
211 g_stmt:='CHGDFN '||p_calendar.dim_name||' base add '||p_calendar.periodicity(i).dim_name;
212 bsc_aw_dbms_aw.execute(g_stmt); --we have added ORA-36656 to ignore list
213 end loop;
214 g_stmt:='dfn '||p_calendar.levels_name||' dimension text';
215 bsc_aw_dbms_aw.execute(g_stmt);
216 --we need to add levels names as parent.child
217 for i in 1..p_calendar.parent_child.count loop
218 if p_calendar.parent_child(i).parent is not null and p_calendar.parent_child(i).child is not null then
219 g_stmt:='maintain '||p_calendar.levels_name||' merge '''||p_calendar.parent_child(i).parent_dim_name||'.'||
220 p_calendar.parent_child(i).child_dim_name||'''';
221 bsc_aw_dbms_aw.execute(g_stmt);
222 end if;
223 end loop;
224 --
225 g_stmt:='dfn '||p_calendar.end_period_levels_name||' dimension text';
226 bsc_aw_dbms_aw.execute(g_stmt);
227 for i in 1..p_calendar.periodicity.count loop
228 g_stmt:='maintain '||p_calendar.end_period_levels_name||' merge '''||
229 p_calendar.periodicity(i).dim_name||'''';
230 bsc_aw_dbms_aw.execute(g_stmt);
231 end loop;
232 --
233 g_stmt:='dfn '||p_calendar.relation_name||' relation '||p_calendar.dim_name||' <'||p_calendar.dim_name||' '||p_calendar.levels_name||'>';
234 bsc_aw_dbms_aw.execute(g_stmt);
235 g_stmt:='dfn '||p_calendar.denorm_relation_name||' relation '||p_calendar.dim_name||' <'||p_calendar.dim_name||' '||
236 p_calendar.end_period_levels_name||'>'; --end_period_levels_name is simply the level dim names
237 bsc_aw_dbms_aw.execute(g_stmt);
238 g_stmt:='dfn '||p_calendar.end_period_relation_name||' relation '||p_calendar.dim_name||' <'||p_calendar.dim_name||' '||
239 p_calendar.end_period_levels_name||'>';
240 bsc_aw_dbms_aw.execute(g_stmt);
241 --also define a temp variable that all aggregations with balance type measures will use
242 g_stmt:='dfn '||p_calendar.end_period_relation_name||'.temp TEXT <'||p_calendar.dim_name||' '||p_calendar.end_period_levels_name||'>';
243 bsc_aw_dbms_aw.execute(g_stmt);
244 --
245 for i in 1..p_calendar.periodicity.count loop
246 if p_calendar.periodicity(i).aw_time_dim_name is not null then
247 g_stmt:='dfn '||p_calendar.periodicity(i).aw_bsc_aw_rel_name||' relation '||p_calendar.periodicity(i).aw_time_dim_name||
248 '<'||p_calendar.periodicity(i).dim_name||'>';
249 bsc_aw_dbms_aw.execute(g_stmt);
250 g_stmt:='dfn '||p_calendar.periodicity(i).aw_aw_bsc_rel_name||' relation bsc_calendar_'||p_calendar.calendar_id||
251 '<'||p_calendar.periodicity(i).aw_time_dim_name||'>';
252 bsc_aw_dbms_aw.execute(g_stmt);
253 end if;
254 end loop;
255 --
256 for i in 1..p_calendar.misc_object.count loop
257 g_stmt:='dfn '||p_calendar.misc_object(i).object_name||' '||p_calendar.misc_object(i).datatype||' <'||p_calendar.dim_name||'>';
258 bsc_aw_dbms_aw.execute(g_stmt);
259 end loop;
260 Exception when others then
261 log_n('Exception in create_calendar_objects '||sqlerrm);
262 raise;
263 End;
264
265 procedure create_calendar_program(
266 p_calendar in out nocopy calendar_r
267 ) is
268 --
269 l_name varchar2(300);
270 l_lower_periodicities periodicity_tb;
271 l_upper_periodicities periodicity_tb;
272 --
273 Begin
274 --
275 p_calendar.load_program:='load_cal_'||p_calendar.calendar_id;
276 g_commands.delete;
277 bsc_aw_utility.add_g_commands(g_commands,'dfn '||p_calendar.load_program||' program');
278 bsc_aw_utility.add_g_commands(g_commands,'allstat');
279 for i in 1..p_calendar.periodicity.count loop
280 --to populate end_period_relation_name
281 l_name:='prev_'||p_calendar.periodicity(i).db_column_name;
282 bsc_aw_utility.add_g_commands(g_commands,'if exists(\'''||l_name||'\'') eq false');
283 bsc_aw_utility.add_g_commands(g_commands,'then do');
284 bsc_aw_utility.add_g_commands(g_commands,'dfn prev_'||p_calendar.periodicity(i).db_column_name||' TEXT session');
285 bsc_aw_utility.add_g_commands(g_commands,'doend');
286 end loop;
287 --4566074 : these variables must be initialized since sessions are used, else old values get pulled in
288 for i in 1..p_calendar.periodicity.count loop
289 bsc_aw_utility.add_g_commands(g_commands,'prev_'||p_calendar.periodicity(i).db_column_name||'=NA');
290 end loop;
291 --
292 for i in 1..p_calendar.periodicity.count loop
293 if p_calendar.periodicity(i).aw_time_dim_name is not null then
294 bsc_aw_utility.add_g_commands(g_commands,'maintain '||p_calendar.periodicity(i).aw_time_dim_name||' merge arg(1)');
295 bsc_aw_utility.add_g_commands(g_commands,'maintain '||p_calendar.periodicity(i).aw_time_dim_name||' merge arg(2)');
296 end if;
297 end loop;
298 --
299 bsc_aw_utility.add_g_commands(g_commands,'sql declare c1 cursor for select --');
300 for i in 1..p_calendar.periodicity.count loop
301 bsc_aw_utility.add_g_commands(g_commands,p_calendar.periodicity(i).db_column_name||'||\''.\''||year, --');
302 end loop;
303 for i in 1..p_calendar.periodicity.count loop
304 if p_calendar.periodicity(i).periodicity_type=1 then --this is yearly. then we hardcode 0 this is what iviewer wants to see
305 bsc_aw_utility.add_g_commands(g_commands,'0 '||p_calendar.periodicity(i).db_column_name||', --');
306 else
307 bsc_aw_utility.add_g_commands(g_commands,p_calendar.periodicity(i).db_column_name||', --');
308 end if;
309 end loop;
310 for i in 1..p_calendar.periodicity.count loop
311 bsc_aw_utility.add_g_commands(g_commands,'year year'||i||', --');
312 end loop;
313 for i in 1..p_calendar.periodicity.count loop
314 bsc_aw_utility.add_g_commands(g_commands,p_calendar.periodicity(i).periodicity_id||' per_'||
315 p_calendar.periodicity(i).periodicity_id||', --');
316 end loop;
317 /*
318 earlier we were using month||\'' \''||day30||\'' \''||year. got error
319 Exception in load_calendar ORA-35758: (VCTODT03) '2 29 1997' is not a valid date because 29 is out of range for a day of the month.
320 this is because month is fiscal month, not calendar month. so corrected to calendar month, day and year
321 */
322 for i in 1..p_calendar.periodicity.count loop
323 if p_calendar.periodicity(i).aw_time_dim_name is not null then
324 if bsc_aw_utility.get_db_version>=10 then
325 bsc_aw_utility.add_g_commands(g_commands,'to_date(calendar_month||\'' \''||calendar_day||\'' \''||calendar_year,\''MM DD YYYY\'') aw_day_'||p_calendar.periodicity(i).db_column_name||', --');
326 else
327 bsc_aw_utility.add_g_commands(g_commands,'calendar_month||\'' \''||calendar_day||\'' \''||calendar_year aw_day_'||p_calendar.periodicity(i).db_column_name||', --');
328 end if;
329 end if;
330 end loop;
331 bsc_aw_utility.trim_g_commands(g_commands,4,' --');
332 bsc_aw_utility.add_g_commands(g_commands,'from bsc_db_calendar where calendar_id='||p_calendar.calendar_id||' order by calendar_year,calendar_month,calendar_day');
333 bsc_aw_utility.add_g_commands(g_commands,'sql open c1');
334 bsc_aw_utility.add_g_commands(g_commands,'sql fetch c1 loop into --');
335 for i in 1..p_calendar.periodicity.count loop
336 bsc_aw_utility.add_g_commands(g_commands,':append '||p_calendar.periodicity(i).dim_name||' --');
337 end loop;
338 for i in 1..p_calendar.periodicity.count loop
339 bsc_aw_utility.add_g_commands(g_commands,':period_cal_'||p_calendar.calendar_id||'('||p_calendar.dim_name||' '||
340 p_calendar.periodicity(i).dim_name||') --');
341 end loop;
342 for i in 1..p_calendar.periodicity.count loop
343 bsc_aw_utility.add_g_commands(g_commands,':year_cal_'||p_calendar.calendar_id||'('||p_calendar.dim_name||' '||
344 p_calendar.periodicity(i).dim_name||') --');
345 end loop;
346 for i in 1..p_calendar.periodicity.count loop
347 bsc_aw_utility.add_g_commands(g_commands,':periodicity_cal_'||p_calendar.calendar_id||'('||p_calendar.dim_name||' '||
348 p_calendar.periodicity(i).dim_name||') --');
349 end loop;
350 for i in 1..p_calendar.periodicity.count loop
351 if p_calendar.periodicity(i).aw_time_dim_name is not null then
352 bsc_aw_utility.add_g_commands(g_commands,':'||p_calendar.periodicity(i).aw_bsc_aw_rel_name||' --');
353 end if;
354 end loop;
355 bsc_aw_utility.add_g_commands(g_commands,'then --');
356 --
357 --add the code for populating end_period_relation_name
358 for i in 1..p_calendar.periodicity.count loop
359 bsc_aw_utility.add_g_commands(g_commands,'if prev_'||p_calendar.periodicity(i).db_column_name||' EQ NA --');
360 bsc_aw_utility.add_g_commands(g_commands,'then do --');
361 bsc_aw_utility.add_g_commands(g_commands,'prev_'||p_calendar.periodicity(i).db_column_name||' = '||p_calendar.periodicity(i).dim_name||' --');
362 bsc_aw_utility.add_g_commands(g_commands,'doend --');
363 --if this is not the lowest level , ie. day level then...
364 --for day, source is null
365 if p_calendar.periodicity(i).source is not null then
366 --get all the lower periodicities
367 l_lower_periodicities.delete;
368 get_all_lower_periodicities(p_calendar.periodicity(i),p_calendar,l_lower_periodicities);
369 if l_lower_periodicities.count>0 then
370 bsc_aw_utility.add_g_commands(g_commands,'else do --');
371 bsc_aw_utility.add_g_commands(g_commands,'if prev_'||p_calendar.periodicity(i).db_column_name||' NE '||p_calendar.periodicity(i).dim_name||' --');
372 bsc_aw_utility.add_g_commands(g_commands,'then do --');
373 for j in 1..l_lower_periodicities.count loop
374 bsc_aw_utility.add_g_commands(g_commands,p_calendar.end_period_relation_name||'('||p_calendar.periodicity(i).dim_name||' '||
375 'prev_'||p_calendar.periodicity(i).db_column_name||' '||p_calendar.end_period_levels_name||' \'''||l_lower_periodicities(j).dim_name||'\'')='||
376 p_calendar.dim_name||'('||l_lower_periodicities(j).dim_name||' prev_'||l_lower_periodicities(j).db_column_name||') --');
377 end loop;
378 bsc_aw_utility.add_g_commands(g_commands,'doend --');
379 bsc_aw_utility.add_g_commands(g_commands,'doend --');
380 end if;
381 end if;
382 end loop;
383 for i in 1..p_calendar.periodicity.count loop
384 bsc_aw_utility.add_g_commands(g_commands,'if prev_'||p_calendar.periodicity(i).db_column_name||' NE '||p_calendar.periodicity(i).dim_name||' --');
385 bsc_aw_utility.add_g_commands(g_commands,'then do --');
386 bsc_aw_utility.add_g_commands(g_commands,'prev_'||p_calendar.periodicity(i).db_column_name||' = '||p_calendar.periodicity(i).dim_name||' --');
387 bsc_aw_utility.add_g_commands(g_commands,'doend --');
388 end loop;
389 ----------
390 --code to populate denorm relation
391 for i in 1..p_calendar.periodicity.count loop
392 l_upper_periodicities.delete;
393 get_all_upper_periodicities(p_calendar.periodicity(i),p_calendar,l_upper_periodicities);
394 if l_upper_periodicities.count>0 then
395 for j in 1..l_upper_periodicities.count loop
396 bsc_aw_utility.add_g_commands(g_commands,p_calendar.denorm_relation_name||'('||p_calendar.dim_name||' '||p_calendar.periodicity(i).dim_name||
397 ' '||p_calendar.end_period_levels_name||' \'''||l_upper_periodicities(j).dim_name||'\'')='||p_calendar.dim_name||'('||
398 p_calendar.dim_name||' '||l_upper_periodicities(j).dim_name||') --');
399 end loop;
400 end if;
401 end loop;
402 ----------
403 --code to populate the other relations
404 for i in 1..p_calendar.parent_child.count loop
405 if p_calendar.parent_child(i).parent is not null and p_calendar.parent_child(i).child is not null then
406 l_name:=p_calendar.parent_child(i).child_dim_name;
407 bsc_aw_utility.add_g_commands(g_commands,p_calendar.relation_name||'('||p_calendar.dim_name||' '||l_name||' '||
408 p_calendar.levels_name||' \'''||p_calendar.parent_child(i).parent_dim_name||'.'||p_calendar.parent_child(i).child_dim_name
409 ||'\'')='||
410 p_calendar.dim_name||'('||p_calendar.dim_name||' '||p_calendar.parent_child(i).parent_dim_name||') --');
411 end if;
412 end loop;
413 for i in 1..p_calendar.periodicity.count loop
414 if p_calendar.periodicity(i).aw_time_dim_name is not null then
415 bsc_aw_utility.add_g_commands(g_commands,p_calendar.periodicity(i).aw_aw_bsc_rel_name||'('||p_calendar.periodicity(i).aw_time_dim_name||' '||
416 p_calendar.periodicity(i).aw_bsc_aw_rel_name||')='||p_calendar.dim_name||'('||p_calendar.periodicity(i).dim_name||' '||
417 p_calendar.periodicity(i).dim_name||') --');
418 end if;
419 end loop;
420 bsc_aw_utility.trim_g_commands(g_commands,3,null);
421 bsc_aw_utility.add_g_commands(g_commands,'sql close c1');
422 bsc_aw_utility.add_g_commands(g_commands,'sql cleanup');
423 --part II----------------------
424 /*
425 we have an issue where if 2004 is the last year, then data in calendar will not go to 2005. this means for the last periods in 2004,
426 like year 2004, qtr 4 2004, semester 2 2004, we will not have if prev_YEAR NE year_1_cal_1 - so we are not populating end period rel
427 for relation. we have to add this last data to the relation. pick the last row in the calendar, then assign it as the end period rel
428 for year 2004, day 366.2004 is the end period rel etc
429 */
430 bsc_aw_utility.add_g_commands(g_commands,'sql declare c1 cursor for select --');
431 for i in 1..p_calendar.periodicity.count loop
432 bsc_aw_utility.add_g_commands(g_commands,p_calendar.periodicity(i).db_column_name||'||\''.\''||year, --');
433 end loop;
434 bsc_aw_utility.trim_g_commands(g_commands,4,' --');
435 bsc_aw_utility.add_g_commands(g_commands,'from (select * from bsc_db_calendar where calendar_id='||p_calendar.calendar_id||
436 ' order by calendar_year desc,calendar_month desc,calendar_day desc) where rownum=1');
437 bsc_aw_utility.add_g_commands(g_commands,'sql open c1');
438 bsc_aw_utility.add_g_commands(g_commands,'sql fetch c1 loop into --');
439 for i in 1..p_calendar.periodicity.count loop
440 bsc_aw_utility.add_g_commands(g_commands,':match '||p_calendar.periodicity(i).dim_name||' --');
441 end loop;
442 --add the code for populating end_period_relation_name
443 for i in 1..p_calendar.periodicity.count loop
444 if p_calendar.periodicity(i).source is not null then
445 --get all the lower periodicities
446 l_lower_periodicities.delete;
447 get_all_lower_periodicities(p_calendar.periodicity(i),p_calendar,l_lower_periodicities);
448 if l_lower_periodicities.count>0 then
449 for j in 1..l_lower_periodicities.count loop
450 bsc_aw_utility.add_g_commands(g_commands,p_calendar.end_period_relation_name||'('||p_calendar.periodicity(i).dim_name||' '||
451 p_calendar.periodicity(i).dim_name||' '||p_calendar.end_period_levels_name||' \'''||l_lower_periodicities(j).dim_name||'\'')='||
452 p_calendar.dim_name||'('||l_lower_periodicities(j).dim_name||' '||l_lower_periodicities(j).dim_name||') --');
453 end loop;
454 end if;
455 end if;
456 end loop;
457 bsc_aw_utility.trim_g_commands(g_commands,3,null);
458 --
459 bsc_aw_utility.exec_program_commands(p_calendar.load_program,g_commands);
460 Exception when others then
461 log_n('Exception in create_calendar_program '||sqlerrm);
462 raise;
463 End;
464
465 /*
466 given a periodicity, gets all the lower periodicities, ie denormalizes
467 used to fill in end_period_relation_name in create_calendar_program
468 called recursively
469 */
470 procedure get_all_lower_periodicities(
471 p_periodicity periodicity_r,
472 p_calendar calendar_r,
473 p_lower_periodicities in out nocopy periodicity_tb
474 ) is
475 --
476 l_lower_periodicities periodicity_tb;
477 l_flag boolean;
478 Begin
479 get_child_periodicities(p_periodicity,p_calendar,l_lower_periodicities);
480 for i in 1..l_lower_periodicities.count loop
481 get_all_lower_periodicities(l_lower_periodicities(i),p_calendar,p_lower_periodicities);
482 end loop;
483 for i in 1..l_lower_periodicities.count loop
484 --must add only distinct list
485 l_flag:=false;
486 for j in 1..p_lower_periodicities.count loop
487 if p_lower_periodicities(j).periodicity_id=l_lower_periodicities(i).periodicity_id then
488 l_flag:=true;
489 exit;
490 end if;
491 end loop;
492 if l_flag=false then
493 p_lower_periodicities(p_lower_periodicities.count+1):=l_lower_periodicities(i);
494 end if;
495 end loop;
496 Exception when others then
497 log_n('Exception in get_all_lower_periodicities '||sqlerrm);
498 raise;
499 End;
500
501 /*
502 given a periodicity, gets all the upper periodicities, ie denormalizes
503 used to fill in denorm_relation in create_calendar_program called recursively
504 */
505 procedure get_all_upper_periodicities(
506 p_periodicity periodicity_r,
507 p_calendar calendar_r,
508 p_upper_periodicities in out nocopy periodicity_tb
509 ) is
510 --
511 l_upper_periodicities periodicity_tb;
512 l_flag boolean;
513 Begin
514 get_parent_periodicities(p_periodicity,p_calendar,l_upper_periodicities);
515 for i in 1..l_upper_periodicities.count loop
516 get_all_upper_periodicities(l_upper_periodicities(i),p_calendar,p_upper_periodicities);
517 end loop;
518 for i in 1..l_upper_periodicities.count loop
519 --must add only distinct list
520 l_flag:=false;
521 for j in 1..p_upper_periodicities.count loop
522 if p_upper_periodicities(j).periodicity_id=l_upper_periodicities(i).periodicity_id then
523 l_flag:=true;
524 exit;
525 end if;
526 end loop;
527 if l_flag=false then
528 p_upper_periodicities(p_upper_periodicities.count+1):=l_upper_periodicities(i);
529 end if;
530 end loop;
531 Exception when others then
532 log_n('Exception in get_all_upper_periodicities '||sqlerrm);
533 raise;
534 End;
535
536 /*given a calendar name, it reads cal info from olap metadata. NOTE...not all info is read
537 */
538 procedure get_calendar(p_calendar_name varchar2,p_calendar out nocopy calendar_r) is
539 l_oo bsc_aw_md_wrapper.bsc_olap_object_tb;
540 l_oor bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
541 Begin
542 bsc_aw_md_api.get_bsc_olap_object(null,null,p_calendar_name,'dimension',l_oo);
543 bsc_aw_md_api.get_bsc_olap_object_relation(null,null,null,p_calendar_name,'dimension',l_oor);
544 --
545 p_calendar.dim_name:=p_calendar_name;
546 for i in 1..l_oo.count loop
547 if l_oo(i).object=p_calendar_name and l_oo(i).object_type='dimension' then
548 p_calendar.calendar_id:=bsc_aw_utility.get_parameter_value(l_oo(i).property1,'calendar',',');
549 p_calendar.property:=l_oo(i).property1;
550 elsif l_oo(i).object_type='relation' then
551 p_calendar.relation_name:=l_oo(i).object;
552 elsif l_oo(i).object_type='denorm relation' then
553 p_calendar.denorm_relation_name:=l_oo(i).object;
554 elsif l_oo(i).object_type='end period relation' then
555 p_calendar.end_period_relation_name:=l_oo(i).object;
556 elsif l_oo(i).object_type='level name dim' then
557 p_calendar.levels_name:=l_oo(i).object;
558 elsif l_oo(i).object_type='end period level name dim' then
559 p_calendar.end_period_levels_name:=l_oo(i).object;
560 --
561 elsif l_oo(i).object_type='dimension level' then
562 p_calendar.periodicity(p_calendar.periodicity.count+1).dim_name:=l_oo(i).object;
563 p_calendar.periodicity(p_calendar.periodicity.count).periodicity_id:=bsc_aw_utility.get_parameter_value(l_oo(i).property1,'periodicity',',');
564 p_calendar.periodicity(p_calendar.periodicity.count).db_column_name:=bsc_aw_utility.get_parameter_value(l_oo(i).property1,'db_column_name',',');
565 end if;
566 end loop;
567 --parent child relation
568 for i in 1..l_oor.count loop
569 if l_oor(i).relation_type='parent level' then
570 p_calendar.parent_child(p_calendar.parent_child.count+1).parent_dim_name:=l_oor(i).relation_object;
571 p_calendar.parent_child(p_calendar.parent_child.count).parent:=to_number(bsc_aw_utility.get_parameter_value(l_oor(i).property1,'parent periodicity',','));
572 p_calendar.parent_child(p_calendar.parent_child.count).child_dim_name:=l_oor(i).object;
573 p_calendar.parent_child(p_calendar.parent_child.count).child:=to_number(bsc_aw_utility.get_parameter_value(l_oor(i).property1,'child periodicity',','));
574 end if;
575 end loop;
576 Exception when others then
577 log_n('Exception in get_calendar '||sqlerrm);
578 raise;
579 End;
580
581 /*
582 given a periodicity, gets all the child periodicities
583 main procedure get_all_lower_periodicities
584 */
585 procedure get_child_periodicities(
586 p_periodicity periodicity_r,
587 p_calendar calendar_r,
588 p_lower_periodicities out nocopy periodicity_tb
589 ) is
590 Begin
591 for i in 1..p_calendar.parent_child.count loop
592 if p_calendar.parent_child(i).parent=p_periodicity.periodicity_id and p_calendar.parent_child(i).child is not null then
593 p_lower_periodicities(p_lower_periodicities.count+1):=get_periodicity_r(p_calendar.parent_child(i).child,p_calendar.periodicity);
594 end if;
595 end loop;
596 Exception when others then
597 log_n('Exception in get_child_periodicities '||sqlerrm);
598 raise;
599 End;
600
601 procedure get_parent_periodicities(
602 p_periodicity periodicity_r,
603 p_calendar calendar_r,
604 p_upper_periodicities out nocopy periodicity_tb
605 ) is
606 Begin
607 for i in 1..p_calendar.parent_child.count loop
608 if p_calendar.parent_child(i).child=p_periodicity.periodicity_id and p_calendar.parent_child(i).parent is not null then
609 p_upper_periodicities(p_upper_periodicities.count+1):=get_periodicity_r(p_calendar.parent_child(i).parent,p_calendar.periodicity);
610 end if;
611 end loop;
612 Exception when others then
613 log_n('Exception in get_parent_periodicities '||sqlerrm);
614 raise;
615 End;
616
617 /*
618 given a periodicity_id, get the periodicity_r object
619 */
620 function get_periodicity_r(
621 p_periodicity_id number,
622 p_periodicities periodicity_tb
623 ) return periodicity_r is
624 Begin
625 for i in 1..p_periodicities.count loop
626 if p_periodicities(i).periodicity_id=p_periodicity_id then
627 return p_periodicities(i);
628 end if;
629 end loop;
630 return null;
631 Exception when others then
632 log_n('Exception in get_periodicity_r '||sqlerrm);
633 raise;
634 End;
635
636
637 --given a periodicity_id, what is the name of the corresponding aw dim?
638 function get_periodicity_dim_name(p_periodicity periodicity_tb, p_periodicity_id number) return varchar2 is
639 Begin
640 for i in 1..p_periodicity.count loop
641 if p_periodicity(i).periodicity_id=p_periodicity_id then
642 return p_periodicity(i).dim_name;
643 end if;
644 end loop;
645 return null;
646 Exception when others then
647 log_n('Exception in get_periodicity_dim_name '||sqlerrm);
648 raise;
649 End;
650
651
652 /*
653 this api will normalize a denorm relation of the periodicities
654 first parse the source periodicities out.
655 then normalize them
656 */
657 procedure normalize_per_relation(p_calendar in out nocopy calendar_r) is
658 l_relation bsc_aw_utility.parent_child_tb;
659 l_source bsc_aw_utility.value_tb;
660 l_count number;
661 Begin
662 l_count:=0;
663 for i in 1..p_calendar.periodicity.count loop
664 if p_calendar.periodicity(i).source is not null then
665 --we are just reusing bsc_aw_utility.parse_parameter_values
666 l_source.delete;
667 bsc_aw_utility.parse_parameter_values(p_calendar.periodicity(i).source,',',l_source);
668 for j in 1..l_source.count loop
669 l_count:=l_count+1;
670 l_relation(l_count).parent:=p_calendar.periodicity(i).periodicity_id;
671 l_relation(l_count).child:=l_source(j).parameter;
672 end loop;
673 else
674 l_count:=l_count+1;
675 l_relation(l_count).parent:=p_calendar.periodicity(i).periodicity_id;
676 end if;
677 end loop;
678 bsc_aw_utility.normalize_denorm_relation(l_relation);
679 --l_relation is now normalized
680 for i in 1..l_relation.count loop
681 p_calendar.parent_child(i).parent:=to_number(l_relation(i).parent);
682 p_calendar.parent_child(i).parent_dim_name:=get_periodicity_dim_name(p_calendar.periodicity,
683 p_calendar.parent_child(i).parent);
684 p_calendar.parent_child(i).child:=to_number(l_relation(i).child);
685 p_calendar.parent_child(i).child_dim_name:=get_periodicity_dim_name(p_calendar.periodicity,
686 p_calendar.parent_child(i).child);
687 end loop;
688 Exception when others then
689 log_n('Exception in normalize_per_relation '||sqlerrm);
690 raise;
691 End;
692
693 procedure dmp_calendar(p_calendar calendar_r) is
694 Begin
695 log_n('Calendar Dmp :-');
696 log(p_calendar.calendar_id);
697 log('Periodicity:-');
698 for i in 1..p_calendar.periodicity.count loop
699 log(p_calendar.periodicity(i).db_column_name||' perid='||p_calendar.periodicity(i).periodicity_id||' pertype='||
700 p_calendar.periodicity(i).periodicity_type||' source='||p_calendar.periodicity(i).source||' '||
701 'property='||p_calendar.periodicity(i).property);
702 end loop;
703 log('Periodicity Relations:-');
704 for i in 1..p_calendar.parent_child.count loop
705 log(p_calendar.parent_child(i).parent||' '||p_calendar.parent_child(i).child);
706 end loop;
707 Exception when others then
708 log_n('Exception in dmp_calendar '||sqlerrm);
709 raise;
710 End;
711
712 procedure create_calendar_metadata(p_calendar calendar_r) is
713 Begin
714 bsc_aw_md_api.delete_calendar(p_calendar);
715 bsc_aw_md_api.create_calendar(p_calendar);
716 Exception when others then
717 log_n('Exception in create_calendar_metadata '||sqlerrm);
718 raise;
719 End;
720
721 procedure get_kpi_for_calendar(p_calendar in out nocopy calendar_r) is
722 Begin
723 bsc_aw_bsc_metadata.get_kpi_for_calendar(p_calendar);
724 Exception when others then
725 log_n('Exception in get_kpi_for_calendar '||sqlerrm);
726 raise;
727 End;
728
729 procedure attach_workspace(p_options varchar2) is
730 l_options varchar2(8000);
731 Begin
732 l_options:='create workspace,'||p_options;
733 bsc_aw_management.get_workspace_lock('rw',l_options);
734 Exception when others then
735 log_n('Exception in attach_workspace '||sqlerrm);
736 raise;
737 End;
738
739 /*
740 returns the current year of this calendar
741 */
742 procedure get_calendar_current_year(p_calendar number,p_year out nocopy number) is
743 --
744 cursor c1 is select current_year from bsc_sys_calendars_b where calendar_id=p_calendar;
745 Begin
746 open c1;
747 fetch c1 into p_year;
748 close c1;
749 Exception when others then
750 log_n('Exception in get_calendar_current_year '||sqlerrm);
751 raise;
752 End;
753
754 /*
755 see if the calendar is already loaded in AW
756 */
757 function check_calendar_loaded(p_calendar number) return varchar2 is
758 --
759 l_dim varchar2(300);
760 l_bsc_olap_object bsc_aw_md_wrapper.bsc_olap_object_tb;
761 Begin
762 l_dim:=get_calendar_name(p_calendar);
763 bsc_aw_md_api.get_bsc_olap_object(l_dim,'dimension',l_dim,'dimension',l_bsc_olap_object);
764 if l_bsc_olap_object(1).operation_flag is not null and l_bsc_olap_object(1).operation_flag='loaded' then
765 return 'Y';
766 else
767 return 'N';
768 end if;
769 Exception when others then
770 log_n('Exception in check_calendar_loaded '||sqlerrm);
771 raise;
772 End;
773
774 procedure load_calendar(p_calendar number,p_options varchar2) is
775 Begin
776 bsc_aw_utility.g_options.delete;
777 bsc_aw_utility.parse_parameter_values(p_options,',',bsc_aw_utility.g_options);
778 bsc_aw_utility.open_file('TEST');
779 bsc_aw_utility.dmp_g_options(bsc_aw_utility.g_options);
780 init_all;
781 lock_calendar_objects(p_calendar);
782 load_calendar(p_calendar);
783 --
784 bsc_aw_management.commit_aw;
785 commit;
786 bsc_aw_management.detach_workspace;
787 Exception when others then
788 bsc_aw_management.detach_workspace;
789 log_n('Exception in load_calendar '||sqlerrm);
790 raise;
791 End;
792
793 --in bsc_aw_load_kpi, we have a call to this procedure from load_calendar_if_needed
794 procedure load_calendar(p_calendar number) is
795 --
796 --4604538 we select min and max of calendar year
797 cursor c1 is select min(calendar_year)-1,max(calendar_year)+1 from bsc_db_calendar where calendar_id=p_calendar;
798 l_min number;
799 l_max number;
800 --
801 l_pgm varchar2(300);
802 l_dim varchar2(300);
803 l_bsc_olap_object bsc_aw_md_wrapper.bsc_olap_object_tb;
804 l_stmt varchar2(3000);
805 Begin
806 --
807 open c1;
808 fetch c1 into l_min,l_max;
809 close c1;
810 --
811 l_dim:=get_calendar_name(p_calendar);
812 --purge_calendar(p_calendar); there is no need to purge the calendar when loading it
813 l_bsc_olap_object.delete;
814 bsc_aw_md_api.get_bsc_olap_object(null,'dml program',l_dim,'dimension',l_bsc_olap_object);
815 for i in 1..l_bsc_olap_object.count loop
816 if l_bsc_olap_object(i).olap_object_type='dml program initial load' then
817 l_pgm:=l_bsc_olap_object(i).object;
818 exit;
819 end if;
820 end loop;
821 l_stmt:='call '||l_pgm||'(''01 01 '||l_min||''',''01 01 '||l_max||''')';
822 if g_debug then
823 log_n(l_stmt||bsc_aw_utility.get_time);
824 end if;
825 bsc_aw_dbms_aw.execute(l_stmt);
826 if g_debug then
827 log('Finished '||bsc_aw_utility.get_time);
828 end if;
829 --update bsc_olap_object saying operation_flag='loaded'
830 bsc_aw_md_api.update_olap_object(l_dim,'dimension',l_dim,'dimension',null,null,'operation_flag','loaded');
831 Exception when others then
832 log_n('Exception in load_calendar '||sqlerrm);
833 raise;
834 End;
835
836 /*
837 clean up
838 aw time dim level
839 dim level
840 dimension
841 */
842 procedure purge_calendar(p_calendar number,p_options varchar2) is
843 Begin
844 purge_calendar(p_calendar);
845 bsc_aw_management.commit_aw;
846 commit;
847 Exception when others then
848 log_n('Exception in purge_calendar '||sqlerrm);
849 raise;
850 End;
851
852 procedure purge_calendar(p_calendar number) is
853 --
854 l_dim varchar2(300);
855 l_olap_object bsc_aw_md_wrapper.bsc_olap_object_tb;
856 Begin
857 l_dim:=get_calendar_name(p_calendar);
858 bsc_aw_md_api.get_bsc_olap_object(null,null,l_dim,'dimension',l_olap_object);
859 --
860 for i in 1..l_olap_object.count loop
861 if l_olap_object(i).object_type='aw time dim level' then
862 bsc_aw_dbms_aw.execute('maintain '||l_olap_object(i).object||' delete all');
863 end if;
864 end loop;
865 --
866 for i in 1..l_olap_object.count loop
867 if l_olap_object(i).object_type='dimension level' then
868 bsc_aw_dbms_aw.execute('maintain '||l_olap_object(i).object||' delete all');
869 end if;
870 end loop;
871 --update bsc_olap_object saying operation_flag='purged'
872 bsc_aw_md_api.update_olap_object(l_dim,'dimension',l_dim,'dimension',null,null,'operation_flag','purged');
873 Exception when others then
874 log_n('Exception in purge_calendar '||sqlerrm);
875 raise;
876 End;
877
878 procedure check_calendar_create(
879 p_calendar calendar_r,
880 p_recreate out nocopy varchar2,
881 p_affected_kpi out nocopy dbms_sql.varchar2_table) is
882 --
883 Begin
884 if bsc_aw_md_api.is_dim_present(p_calendar.dim_name) then
885 --check to see if the calendar matches the calendar in the olap metadata
886 if bsc_aw_utility.get_parameter_value('RECREATE CALENDAR')='Y' then
887 if g_debug then
888 log('RECREATE CALENDAR specified. Drop and recreate');
889 end if;
890 p_recreate:='create all';
891 drop_calendar_objects(p_calendar.dim_name,null,p_affected_kpi);
892 elsif bsc_aw_utility.get_parameter_value('REIMPLEMENT CALENDAR')='Y' then
893 if g_debug then
894 log('REIMPLEMENT CALENDAR specified. Merge create all');
895 end if;
896 p_recreate:='create all';
897 elsif bsc_aw_utility.get_parameter_value('RECREATE PROGRAM')='Y' then
898 if g_debug then
899 log('RECREATE PROGRAM specified');
900 end if;
901 p_recreate:='create program';
902 else
903 correct_calendar(p_calendar,p_recreate);
904 end if;
905 else --cal not present
906 if g_debug then
907 log('New Calendar '||p_calendar.dim_name);
908 end if;
909 p_recreate:='create all';
910 end if;
911 if g_debug then
912 log('For calendar '||p_calendar.dim_name||', recreate option='||p_recreate);
913 end if;
914 Exception when others then
915 log_n('Exception in check_calendar_create '||sqlerrm);
916 raise;
917 End;
918
919 /*
920 check to see if the olap metadata and the current calendar metadata are in sync
921 right now, see if there are new periodicities or dropped periodicities
922 */
923 procedure correct_calendar(p_calendar calendar_r,p_recreate out nocopy varchar2) is
924 l_oo bsc_aw_md_wrapper.bsc_olap_object_tb;
925 l_oor bsc_aw_md_wrapper.bsc_olap_object_relation_tb;
926 l_periodicities dbms_sql.varchar2_table;
927 l_olap_periodicities dbms_sql.varchar2_table;
928 l_level_name_dim varchar(200);
929 l_olap_periodicity_dim dbms_sql.varchar2_table;
930 l_pc parent_child_tb;
931 --
932 Begin
933 bsc_aw_md_api.get_bsc_olap_object(null,null,p_calendar.dim_name,'dimension',l_oo);
934 bsc_aw_md_api.get_bsc_olap_object_relation(null,null,null,p_calendar.dim_name,'dimension',l_oor);
935 if l_oo.count=0 then
936 return;
937 end if;
938 for i in 1..p_calendar.periodicity.count loop
939 l_periodicities(l_periodicities.count+1):=p_calendar.periodicity(i).periodicity_id;
940 end loop;
941 for i in 1..l_oo.count loop
942 if l_oo(i).object_type='dimension level' then
943 l_olap_periodicities(l_olap_periodicities.count+1):=bsc_aw_utility.get_parameter_value(l_oo(i).property1,'periodicity',',');
944 l_olap_periodicity_dim(l_olap_periodicity_dim.count+1):=l_oo(i).object;
945 elsif l_oo(i).object_type='level name dim' then
946 l_level_name_dim:=l_oo(i).object;
947 end if;
948 end loop;
949 --level name dim
950 for i in 1..l_olap_periodicities.count loop
951 if bsc_aw_utility.in_array(l_periodicities,l_olap_periodicities(i))=false then
952 p_recreate:='create all';
953 for j in 1..l_oor.count loop
954 if l_oor(j).relation_type='parent level' then
955 if l_oor(j).object=l_olap_periodicity_dim(i) or l_oor(j).relation_object=l_olap_periodicity_dim(i) then
956 bsc_aw_dbms_aw.execute('maintain '||l_level_name_dim||' delete '''||l_oor(j).relation_object||'.'||l_oor(j).object||'''');
957 end if;
958 end if;
959 end loop;
960 end if;
961 end loop;
962 --see if there are new periodicities we need to add
963 if p_recreate is null then
964 for i in 1..l_periodicities.count loop
965 if bsc_aw_utility.in_array(l_olap_periodicities,l_periodicities(i))=false then
966 p_recreate:='create all';
967 exit;
968 end if;
969 end loop;
970 end if;
971 --see if any existing relation changed. 4602290
972 if p_recreate is null then
973 l_pc.delete;
974 for i in 1..l_oor.count loop
975 if l_oor(i).relation_type='parent level' then
976 l_pc(l_pc.count+1).parent_dim_name:=l_oor(i).relation_object;
977 l_pc(l_pc.count).child_dim_name:=l_oor(i).object;
978 end if;
979 end loop;
980 if compare_pc_relations(p_calendar.parent_child,l_pc)<>0 then
981 if g_debug then
982 log('Parent child relation diff between old and new');
983 end if;
984 p_recreate:='create all';
985 end if;
986 end if;
987 --recreate the program
988 if p_recreate is null then
989 p_recreate:='create program';
990 end if;
991 Exception when others then
992 log_n('Exception in correct_calendar '||sqlerrm);
993 raise;
994 End;
995
996 function compare_pc_relations(p_pc_1 parent_child_tb,p_pc_2 parent_child_tb) return number is
997 --
998 l_pc_1 bsc_aw_utility.parent_child_tb;
999 l_pc_2 bsc_aw_utility.parent_child_tb;
1000 Begin
1001 for i in 1..p_pc_1.count loop
1002 l_pc_1(i).parent:=p_pc_1(i).parent_dim_name;
1003 l_pc_1(i).child:=p_pc_1(i).child_dim_name;
1004 end loop;
1005 for i in 1..p_pc_2.count loop
1006 l_pc_2(i).parent:=p_pc_2(i).parent_dim_name;
1007 l_pc_2(i).child:=p_pc_2(i).child_dim_name;
1008 end loop;
1009 return bsc_aw_utility.compare_pc_relations(l_pc_1,l_pc_2);
1010 Exception when others then
1011 log_n('Exception in compare_pc_relations '||sqlerrm);
1012 raise;
1013 End;
1014
1015 --p_object_type is null or all or dml program
1016 procedure drop_calendar_objects(p_calendar_name varchar2,p_object_type varchar2,p_affected_kpi out nocopy dbms_sql.varchar2_table) is
1017 --
1018 l_bsc_olap_object bsc_aw_md_wrapper.bsc_olap_object_tb;
1019 l_flag dbms_sql.varchar2_table;
1020 Begin
1021 --first drop all dependent kpi ... if needed
1022 if p_object_type is null or p_object_type='all' then
1023 bsc_aw_md_api.get_kpi_for_dim(p_calendar_name,p_affected_kpi);
1024 for i in 1..p_affected_kpi.count loop
1025 bsc_aw_adapter_kpi.drop_kpi_objects(p_affected_kpi(i));
1026 end loop;
1027 end if;
1028 bsc_aw_md_api.get_bsc_olap_object(null,p_object_type,p_calendar_name,'dimension',l_bsc_olap_object);
1029 --order : drop all relations, variables etc, drop concat dim, then all others
1030 for i in 1..l_bsc_olap_object.count loop
1031 l_flag(i):='N';
1032 end loop;
1033 if l_bsc_olap_object.count>0 then
1034 for i in 1..l_bsc_olap_object.count loop
1035 if l_flag(i)='N' and (l_bsc_olap_object(i).olap_object_type ='relation' or l_bsc_olap_object(i).olap_object_type ='variable') then
1036 bsc_aw_utility.delete_aw_object(l_bsc_olap_object(i).olap_object);
1037 l_flag(i):='Y';
1038 end if;
1039 end loop;
1040 for i in 1..l_bsc_olap_object.count loop
1041 if l_flag(i)='N' and l_bsc_olap_object(i).olap_object_type='concat dimension' then
1042 bsc_aw_utility.delete_aw_object(l_bsc_olap_object(i).olap_object);
1043 l_flag(i):='Y';
1044 end if;
1045 end loop;
1046 for i in 1..l_bsc_olap_object.count loop
1047 if l_flag(i)='N' and l_bsc_olap_object(i).olap_object_type='dimension' then
1048 bsc_aw_utility.delete_aw_object(l_bsc_olap_object(i).olap_object);
1049 l_flag(i):='Y';
1050 end if;
1051 end loop;
1052 --all other objects
1053 for i in 1..l_bsc_olap_object.count loop
1054 if l_flag(i)='N' then
1055 bsc_aw_utility.delete_aw_object(l_bsc_olap_object(i).olap_object);
1056 l_flag(i):='Y';
1057 end if;
1058 end loop;
1059 end if;
1060 Exception when others then
1061 log_n('Exception in drop_calendar_objects '||sqlerrm);
1062 raise;
1063 End;
1064
1065 function get_calendar_name(p_calendar number) return varchar2 is
1066 Begin
1067 return 'bsc_calendar_'||p_calendar;
1068 Exception when others then
1069 log_n('Exception in get_calendar_name '||sqlerrm);
1070 raise;
1071 End;
1072
1073 /*
1074 this procedure finds out the missing levels. it also marks lowest level.
1075 input : periodicity dims
1076 output : missing periodicity dim added.
1077 also : lowest periodicity marked
1078
1079 logic:
1080 for each level, see if a child level is present. if yes, this is not a hanging level
1081 if no child, 2 possibilities
1082 1. lowest level
1083 2. hanging level : must find missing level
1084 find out like this: drill down and see if at any time, a periodicity of the kpi is showing up. if yes, stop.
1085 if no, lowest level
1086 II
1087 then do the same for parent levels
1088 for each child, see if any immediate parent is present. if no, either top level or missing levels
1089 */
1090 procedure get_missing_periodicity(
1091 p_calendar_dim varchar2,
1092 p_periodicity_dim in out nocopy dbms_sql.varchar2_table,
1093 p_lowest_level out nocopy dbms_sql.varchar2_table
1094 ) is
1095 --
1096 l_missing_levels dbms_sql.varchar2_table;
1097 l_found boolean;
1098 l_parent_child_calendar parent_child_tb;
1099 Begin
1100 --get calendar properties
1101 get_calendar_parent_child(p_calendar_dim,l_parent_child_calendar);
1102 --see if a child level is present for the kpi periodicities
1103 for i in 1..p_periodicity_dim.count loop
1104 p_lowest_level(i):='N';
1105 if is_child_present(p_periodicity_dim(i),p_periodicity_dim,l_parent_child_calendar)=false then
1106 --lowest level or hanging level
1107 --drill down hier. if any of the children is present in p_periodicity_dim, this is hanging level
1108 l_missing_levels.delete;
1109 l_found:=false;
1110 get_missing_level_down(p_periodicity_dim(i),p_periodicity_dim,l_parent_child_calendar,l_missing_levels,l_missing_levels,l_found); --false is starting seed value
1111 if l_found and l_missing_levels.count>0 then
1112 for j in 1..l_missing_levels.count loop
1113 if bsc_aw_utility.in_array(p_periodicity_dim,l_missing_levels(j))=false then
1114 p_periodicity_dim(p_periodicity_dim.count+1):=l_missing_levels(j);
1115 p_lowest_level(p_periodicity_dim.count):='N';
1116 end if;
1117 end loop;
1118 else
1119 p_lowest_level(i):='Y';
1120 end if;
1121 end if;
1122 end loop;
1123 --
1124 for i in 1..p_periodicity_dim.count loop
1125 if is_parent_present(p_periodicity_dim(i),p_periodicity_dim,l_parent_child_calendar)=false then
1126 --lowest level or hanging level
1127 --drill down hier. if any of the children is present in p_periodicity_dim, this is hanging level
1128 l_missing_levels.delete;
1129 l_found:=false;
1130 get_missing_level_up(p_periodicity_dim(i),p_periodicity_dim,l_parent_child_calendar,l_missing_levels,l_missing_levels,l_found); --false is starting seed value
1131 if l_found and l_missing_levels.count>0 then
1132 for j in 1..l_missing_levels.count loop
1133 if bsc_aw_utility.in_array(p_periodicity_dim,l_missing_levels(j))=false then
1134 p_periodicity_dim(p_periodicity_dim.count+1):=l_missing_levels(j);
1135 p_lowest_level(p_periodicity_dim.count):='N';
1136 end if;
1137 end loop;
1138 end if;
1139 end if;
1140 end loop;
1141 Exception when others then
1142 log_n('Exception in get_missing_periodicity '||sqlerrm);
1143 raise;
1144 End;
1145
1146 function is_child_present(
1147 p_parent varchar2,
1148 p_periodicity_dim dbms_sql.varchar2_table,
1149 p_parent_child parent_child_tb) return boolean is
1150 Begin
1151 for i in 1..p_parent_child.count loop
1152 if p_parent_child(i).parent_dim_name=p_parent and bsc_aw_utility.in_array(p_periodicity_dim,p_parent_child(i).child_dim_name) then
1153 return true;
1154 end if;
1155 end loop;
1156 return false;
1157 Exception when others then
1158 log_n('Exception in is_child_present '||sqlerrm);
1159 raise;
1160 End;
1161
1162 function is_parent_present(
1163 p_child varchar2,
1164 p_periodicity_dim dbms_sql.varchar2_table,
1165 p_parent_child parent_child_tb) return boolean is
1166 Begin
1167 for i in 1..p_parent_child.count loop
1168 if p_parent_child(i).child_dim_name=p_child and bsc_aw_utility.in_array(p_periodicity_dim,p_parent_child(i).parent_dim_name) then
1169 return true;
1170 end if;
1171 end loop;
1172 return false;
1173 Exception when others then
1174 log_n('Exception in is_parent_present '||sqlerrm);
1175 raise;
1176 End;
1177
1178 --recursively called
1179 procedure get_missing_level_down(
1180 p_parent varchar2,
1181 p_periodicity_dim dbms_sql.varchar2_table,
1182 p_parent_child parent_child_tb,
1183 p_missing_levels_in dbms_sql.varchar2_table,
1184 p_missing_levels_out out nocopy dbms_sql.varchar2_table,
1185 p_found in out nocopy boolean --indicates if child is found and its time to stop
1186 ) is
1187 --
1188 l_missing_levels dbms_sql.varchar2_table;
1189 Begin
1190 if p_found=false then
1191 if is_child_present(p_parent,p_periodicity_dim,p_parent_child) then
1192 p_missing_levels_out:=p_missing_levels_in;
1193 p_found:=true;
1194 else
1195 for i in 1..p_parent_child.count loop
1196 if p_parent_child(i).parent_dim_name=p_parent then
1197 l_missing_levels.delete;
1198 l_missing_levels:=p_missing_levels_in;
1199 l_missing_levels(l_missing_levels.count+1):=p_parent_child(i).child_dim_name;
1200 get_missing_level_down(p_parent_child(i).child_dim_name,p_periodicity_dim,p_parent_child,l_missing_levels,p_missing_levels_out,p_found);
1201 if p_found then --stop
1202 exit;
1203 end if;
1204 end if;
1205 end loop;
1206 end if;
1207 end if;
1208 Exception when others then
1209 log_n('Exception in get_missing_level_down '||sqlerrm);
1210 raise;
1211 End;
1212
1213 --recursively called
1214 procedure get_missing_level_up(
1215 p_child varchar2,
1216 p_periodicity_dim dbms_sql.varchar2_table,
1217 p_parent_child parent_child_tb,
1218 p_missing_levels_in dbms_sql.varchar2_table,
1219 p_missing_levels_out out nocopy dbms_sql.varchar2_table,
1220 p_found in out nocopy boolean --indicates if child is found and its time to stop
1221 ) is
1222 --
1223 l_missing_levels dbms_sql.varchar2_table;
1224 Begin
1225 if p_found=false then
1226 if is_parent_present(p_child,p_periodicity_dim,p_parent_child) then
1227 p_missing_levels_out:=p_missing_levels_in;
1228 p_found:=true;
1229 else
1230 for i in 1..p_parent_child.count loop
1231 if p_parent_child(i).child_dim_name=p_child then
1232 l_missing_levels.delete;
1233 l_missing_levels:=p_missing_levels_in;
1234 l_missing_levels(l_missing_levels.count+1):=p_parent_child(i).parent_dim_name;
1235 get_missing_level_up(p_parent_child(i).parent_dim_name,p_periodicity_dim,p_parent_child,l_missing_levels,p_missing_levels_out,p_found);
1236 if p_found then --stop
1237 exit;
1238 end if;
1239 end if;
1240 end loop;
1241 end if;
1242 end if;
1243 Exception when others then
1244 log_n('Exception in get_missing_level_up '||sqlerrm);
1245 raise;
1246 End;
1247
1248 procedure get_calendar_parent_child(p_calendar_dim varchar2,p_parent_child out nocopy parent_child_tb) is
1249 l_calendar calendar_r;
1250 Begin
1251 get_calendar(p_calendar_dim,l_calendar);
1252 for i in 1..l_calendar.parent_child.count loop
1253 p_parent_child(p_parent_child.count+1):=l_calendar.parent_child(i);
1254 end loop;
1255 Exception when others then
1256 log_n('Exception in get_calendar_parent_child '||sqlerrm);
1257 raise;
1258 End;
1259
1260 procedure get_calendar_periodicities(p_calendar_dim varchar2,p_periodicity out nocopy periodicity_tb) is
1261 l_calendar calendar_r;
1262 Begin
1263 get_calendar(p_calendar_dim,l_calendar);
1264 for i in 1..l_calendar.periodicity.count loop
1265 p_periodicity(p_periodicity.count+1):=l_calendar.periodicity(i);
1266 end loop;
1267 Exception when others then
1268 log_n('Exception in get_calendar_periodicities '||sqlerrm);
1269 raise;
1270 End;
1271
1272 procedure lock_calendar_objects(p_calendar number) is
1273 --
1274 l_lock_objects dbms_sql.varchar2_table;
1275 Begin
1276 get_calendar_objects_to_lock(p_calendar,l_lock_objects);
1277 bsc_aw_management.get_workspace_lock(l_lock_objects,null);
1278 Exception when others then
1279 log_n('Exception in lock_calendar_objects '||sqlerrm);
1280 raise;
1281 End;
1282
1283 /*
1284 in 10g, when calendar is loaded, we want to lock only the calendar objects, not get an exclusive lock on the system
1285 we cannot lock concat dim. got error
1286 acquire BSC_CCDIM_100_101_102_103 (S: 04/13/2005 17:34:10
1287 Exception in execute acquire BSC_CCDIM_100_101_102_103 ORA-37018:Multiwriter operations are not supported for object BSC_AW!BSC_CCDIM_100_101_102_103.
1288 --
1289 we have to acquire locks and update in a certain order . else we get
1290 ORA-37023: (XSMLTUPD01) Object workspace object cannot be updated without dimension workspace object.
1291 we cannot update a relation before a dim. so when we get locks, we first get dim, then relations, then variables
1292 */
1293 procedure get_calendar_objects_to_lock(p_calendar number,p_lock_objects out nocopy dbms_sql.varchar2_table) is
1294 l_dim varchar2(300);
1295 l_bsc_olap_object bsc_aw_md_wrapper.bsc_olap_object_tb;
1296 l_objects dbms_sql.varchar2_table;
1297 Begin
1298 l_dim:=get_calendar_name(p_calendar);
1299 bsc_aw_md_api.get_bsc_olap_object(null,null,l_dim,'dimension',l_bsc_olap_object);
1300 for i in 1..l_bsc_olap_object.count loop
1301 if l_bsc_olap_object(i).olap_object_type is not null and l_bsc_olap_object(i).olap_object_type='dimension' then
1302 l_objects(l_objects.count+1):=l_bsc_olap_object(i).olap_object;
1303 end if;
1304 end loop;
1305 for i in 1..l_bsc_olap_object.count loop
1306 if l_bsc_olap_object(i).olap_object_type is not null and l_bsc_olap_object(i).olap_object_type='relation' then
1307 l_objects(l_objects.count+1):=l_bsc_olap_object(i).olap_object;
1308 end if;
1309 end loop;
1310 for i in 1..l_bsc_olap_object.count loop
1311 if l_bsc_olap_object(i).olap_object_type is not null and l_bsc_olap_object(i).olap_object_type='variable' then
1312 l_objects(l_objects.count+1):=l_bsc_olap_object(i).olap_object;
1313 end if;
1314 end loop;
1315 --there are no limit cubes here like dim.?
1316 --
1317 for i in 1..l_objects.count loop
1318 if bsc_aw_utility.in_array(p_lock_objects,l_objects(i))=false then
1319 p_lock_objects(p_lock_objects.count+1):=l_objects(i);
1320 end if;
1321 end loop;
1322 Exception when others then
1323 log_n('Exception in get_calendar_objects_to_lock '||sqlerrm);
1324 raise;
1325 End;
1326
1327 /*upgrade
1328 we start from latest version and move downwards. actions already done are marked off as false
1329 if p_new_version>=2 then
1330 if action('1') then
1331 ...
1332 action('1')=false
1333 end if;
1334 if action('2') then
1335 ...
1336 ...
1337 end if;
1338 if p_new_version>=1 then
1339 if action('2') then
1340 ...
1341 action('2')=false
1342 end if;
1343 if action('3') then
1344 ...
1345 end if;*/
1346 procedure upgrade(p_new_version number,p_old_version number) is
1347 l_action bsc_aw_utility.boolean_table;
1348 Begin
1349 if g_debug then
1350 log('Calendar upgrade New='||p_new_version||', Old='||p_old_version||bsc_aw_utility.get_time);
1351 end if;
1352 init_all;
1353 if p_new_version>p_old_version then
1354 /*init all actions */
1355 l_action('reimplement calendar'):=true;
1356 /*version by version inc upgrade */
1357 if p_old_version<3 then
1358 if l_action('reimplement calendar') then
1359 reimplement_all_calendars;
1360 l_action('reimplement calendar'):=false;
1361 end if;
1362 end if;
1363 end if;
1364 if g_debug then
1365 log('End upgrade calendar '||bsc_aw_utility.get_time);
1366 end if;
1367 Exception when others then
1368 log_n('Exception in upgrade '||sqlerrm);
1369 raise;
1370 End;
1371
1372 procedure reimplement_all_calendars is
1373 l_oo bsc_aw_md_wrapper.bsc_olap_object_tb;
1374 l_calendar dbms_sql.number_table;
1375 l_year number;
1376 l_cal_id number;
1377 Begin
1378 bsc_aw_md_api.get_bsc_olap_object(null,'dimension',null,'dimension',l_oo);
1379 for i in 1..l_oo.count loop
1380 if instr(l_oo(i).property1,'dimension type=time')>0 then
1381 /*5017796. see if this calendar is still a valid implemented calendar in BSC */
1382 l_cal_id:=null;
1383 l_year:=null;
1384 l_cal_id:=to_number(bsc_aw_utility.get_parameter_value(l_oo(i).property1,'calendar',','));
1385 get_calendar_current_year(l_cal_id,l_year);
1386 if l_year is not null then
1387 bsc_aw_utility.merge_value(l_calendar,l_cal_id);
1388 end if;
1389 end if;
1390 end loop;
1391 bsc_aw_utility.add_option('REIMPLEMENT CALENDAR',null,',');
1392 for i in 1..l_calendar.count loop
1393 reimplement_calendar(l_calendar(i));
1394 end loop;
1395 Exception when others then
1396 log_n('Exception in reimplement_all_calendars '||sqlerrm);
1397 raise;
1398 End;
1399
1400 procedure reimplement_calendar(p_calendar_id number) is
1401 l_affected_kpi dbms_sql.varchar2_table;
1402 l_dim varchar2(100);
1403 Begin
1404 if g_debug then
1405 log('Reimplement calendar '||p_calendar_id||bsc_aw_utility.get_time);
1406 end if;
1407 create_calendar(p_calendar_id,l_affected_kpi);
1408 l_dim:=get_calendar_name(p_calendar_id);
1409 bsc_aw_md_api.update_olap_object(l_dim,'dimension',l_dim,'dimension',null,null,'operation_flag','empty');
1410 Exception when others then
1411 log_n('Exception in reimplement_all_calendars '||sqlerrm);
1412 raise;
1413 End;
1414
1415 ------------------------------
1416 procedure init_all is
1417 Begin
1418 if g_init is null or g_init=false then
1419 g_init:=true;
1420 /*5258418 we need to see if the temp tables and perm tables are created. serialize entry here */
1421 bsc_aw_utility.get_db_lock('bsc_aw_table_create_lock');
1422 bsc_aw_utility.create_temp_tables;
1423 bsc_aw_utility.create_perm_tables;
1424 bsc_aw_utility.release_db_lock('bsc_aw_table_create_lock');
1425 /* */
1426 if bsc_aw_utility.get_parameter_value(bsc_aw_utility.g_options,'DEBUG LOG')='Y'
1427 or bsc_aw_utility.g_log_level>=FND_LOG.G_CURRENT_RUNTIME_LEVEL then
1428 g_debug:=true;
1429 else
1430 g_debug:=false;
1431 end if;
1432 bsc_aw_utility.init_all(g_debug);
1433 bsc_aw_dbms_aw.init_all;
1434 bsc_aw_md_api.init_all;
1435 bsc_aw_md_wrapper.init_all;
1436 bsc_aw_bsc_metadata.init_all;
1437 bsc_metadata.init_all;
1438 bsc_aw_management.init_all;
1439 end if;
1440 Exception when others then
1441 log_n('Exception in init_all '||sqlerrm);
1442 raise;
1443 End;
1444
1445 procedure log(p_message varchar2) is
1446 Begin
1447 bsc_aw_utility.log(p_message);
1448 Exception when others then
1449 null;
1450 End;
1451
1452 procedure log_n(p_message varchar2) is
1453 Begin
1454 log(' ');
1455 log(p_message);
1456 Exception when others then
1457 null;
1458 End;
1459
1460 END BSC_AW_CALENDAR;
[Home] [Help]