1 package body BSC_DBI_CALENDAR AS
2 /*$Header: BSCDBICB.pls 120.16.12000000.3 2007/05/10 05:32:52 amitgupt ship $*/
3
4 function generate_short_name return varchar2 is
5 begin
6 return BSC_PERIODS_UTILITY_PKG.Get_Unique_Short_Name();
7 end;
8
9 function get_calendar_short_name(p_calendar_id number) return varchar2 is
10 begin
11 return BSC_PERIODS_UTILITY_PKG.Get_Calendar_Short_Name(p_calendar_Id);
12 end;
13
14 function get_periodicity_short_name(p_periodicity_id number) return varchar2 is
15 begin
16 return BSC_PERIODS_UTILITY_PKG.Get_Periodicity_Short_Name(p_periodicity_id);
17 end;
18
19
20 function dimension_exists(p_dim in varchar2) return boolean is
21 l_val number;
22 TYPE CurTyp IS REF CURSOR;
23 cv CurTyp;
24 begin
25 --making it dynamic sql to not intorduce any bis dependency
26 open cv for 'select count(1) from bis_dimensions where short_name=:1' using p_dim;
27 fetch cv into l_val;
28 close cv;
29 if (l_val>0) then
30 return true;
31 end if;
32 return false;
33 end;
34
35 function dimension_object_exists(p_dim in varchar2, p_dim_object in varchar2) return boolean is
36 l_val number;
37 TYPE CurTyp IS REF CURSOR;
38 cv CurTyp;
39 l_stmt varchar2(1000);
40 begin
41 --making it dynamic sql to not intorduce any bis dependency
42 l_stmt := 'select 1 from bis_levels lvl, bis_dimensions dim where lvl.dimension_id=dim.dimension_id and dim.short_name =:1 and lvl.short_name=:2';
43 open cv for l_stmt using p_dim, p_dim_object;
44 fetch cv into l_val;
45 close cv;
46 if (l_val=1) then
47 return true;
48 end if;
49 return false;
50 end;
51
52 --7/30/05 for calendar short_name issue
53 function create_dim_obj(p_dim_obj in varchar2, p_dim in varchar2, p_view_name in varchar2, p_error_msg out nocopy varchar2) return boolean is
54 x_return_status varchar2(1000);
55 x_msg_count number;
56 x_msg_data varchar2(1000);
57 begin
58
59 --Create dimension object only if it doesnt already exist
60 if (dimension_object_exists(p_dim, p_dim_obj)) then
61 return true;
62 end if;
63 BSC_BIS_DIM_OBJ_PUB.Create_Dim_Object
64 (
65 p_commit => FND_API.G_FALSE
66 , p_dim_obj_short_name => p_dim_obj
67 , p_display_name => p_dim_obj
68 , p_application_id => BSC_PERIODS_UTILITY_PKG.C_BSC_APPLICATION_ID
69 , p_description => p_dim_obj
70 , p_data_source => BSC_PERIODS_UTILITY_PKG.C_PMF_DO_TYPE
71 , p_source_table => p_view_name
72 , p_where_clause => NULL
73 , p_comparison_label_code => NULL
74 , p_table_column => NULL
75 , p_source_type => BSC_PERIODS_UTILITY_PKG.C_OLTP_DO_TYPE
76 , p_maximum_code_size => NULL
77 , p_maximum_name_size => NULL
78 , p_all_item_text => NULL
79 , p_comparison_item_text => NULL
80 , p_prototype_default_value => NULL
81 , p_dimension_values_order => NULL
82 , p_comparison_order => 1
83 , p_dim_short_names => p_dim
84 , x_return_status => x_return_status
85 , x_msg_count => x_msg_count
86 , x_msg_data => x_msg_data
87 );
88 if (x_return_status= FND_API.G_RET_STS_SUCCESS) then
89 return true;
90 else
91 p_error_msg := x_msg_data;
92 return false;
93 end if;
94 exception when others then
95 p_error_msg := x_msg_data;
96 end;
97
98 function create_dim(p_dim in varchar2, p_error_msg out nocopy varchar2) return boolean is
99 x_return_status varchar2(1000);
100 x_msg_count number;
101 x_msg_data varchar2(1000);
102 begin
103 -- create dimension only if doesnt already exist
104 if (dimension_exists(p_dim)) then
105 return true;
106 end if;
107 BSC_BIS_DIMENSION_PUB.Create_Dimension
108 ( p_commit => FND_API.G_FALSE
109 , p_dim_short_name => p_dim
110 , p_display_name => p_dim
111 , p_description => p_dim
112 , p_dim_obj_short_names => NULL
113 , p_application_id => BSC_PERIODS_UTILITY_PKG.C_BSC_APPLICATION_ID
114 , p_create_view => 0
115 , x_return_status => x_Return_Status
116 , x_msg_count => x_Msg_Count
117 , x_msg_data => x_Msg_Data
118 );
119 if (x_return_status= FND_API.G_RET_STS_SUCCESS) then
120 return true;
121 else
122 p_error_msg:= x_msg_data;
123 return false;
124 end if;
125 end;
126
127 /*
128 Public API
129 For PMD. this will load bsc calendar and periodicity so that end to end kpi can be created
130 */
131 procedure load_dbi_cal_metadata(
132 p_error_message out nocopy varchar2
133 ) is
134 Begin
135 savepoint sp_load_dbi_metadata;
136 if is_dbi_cal_metadata_loaded=false then
137 init_all;
138 if check_for_dbi then
139 init_mem_values('full');
140 delete_dbi_calendar_metadata;
141 load_dbi_ent_cal;
142 load_dbi_445_cal;
143 load_dbi_greg_cal;
144 commit;
145 end if;
146 end if;
147 Exception when others then
148 rollback to sp_load_dbi_metadata;
149 p_error_message:=sqlerrm;
150 raise;
151 End;
152
153 /*
154 Public API. called from RSG
155 */
156 procedure load_dbi_cal_into_bsc(
157 Errbuf out nocopy varchar2,
158 Retcode out nocopy varchar2,
159 p_option_string varchar2
160 ) is
161 --
162 l_error_message varchar2(4000);
163 --
164 Begin
165 --if load_dbi_cal_into_bsc encounters any exception, it will raise
166 load_dbi_cal_into_bsc(p_option_string,l_error_message);
167 Exception when others then
168 rollback;
169 errbuf:=g_status_message;
170 retcode:='2';
171 raise;
172 End;
173
174 /*
175 Public API Called from upgrade
176 */
177 procedure load_dbi_cal_into_bsc(
178 p_option_string varchar2,
179 p_error_message out nocopy varchar2
180 ) is
181 --
182 l_refresh_mode number;
183 --
184 Begin
185 p_error_message:=null;
186 if BSC_IM_UTILS.parse_values(p_option_string,',',g_options,g_number_options)=false then
187 raise g_exception;
188 end if;
189 init_all;
190 if check_for_dbi then
191 ----
192 if BSC_IM_UTILS.get_option_value(g_options,g_number_options,'FULL REFRESH')='Y' then
193 l_refresh_mode:=1;
194 else
195 l_refresh_mode:=check_for_inc_refresh;
196 end if;
197 if l_refresh_mode=0 then
198 return;
199 elsif l_refresh_mode=1 then
200 load_dbi_cal_into_bsc_full;
201 elsif l_refresh_mode=2 then
202 load_dbi_cal_into_bsc_inc;
203 end if;
204 ----
205 if g_db_cal_modified then
206 if g_debug then
207 write_to_log_file_n('bsc_db_calendar modified. Need to analyze and refresh reporting calendar');
208 end if;
209 analyze_tables;
210 refresh_reporting_calendars(p_error_message);
211 --AW_INTEGRATION: Need to load DBI calendars into AW
212 load_dbi_calendars_into_aw;
213 end if;
214 commit;
215 end if;
216 Exception when others then
217 rollback;
218 p_error_message:=g_status_message;
219 write_to_log_file_n('Error in load_dbi_cal_into_bsc '||g_status_message||get_time);
220 raise;
221 End;
222
223 procedure refresh_reporting_calendars(p_error_message out nocopy varchar2) is
224 Begin
225 if g_debug then
226 write_to_log_file_n('Refresh Ent Reporting Calendar '||get_time);
227 end if;
228 if BSC_BSC_ADAPTER.load_reporting_calendar(g_ent_cal_id,g_options,g_number_options)=false then
229 --bug#3973335
230 p_error_message:=BSC_BSC_ADAPTER.g_status_message;
231 raise g_exception;
232 end if;
233 if g_debug then
234 write_to_log_file_n('Done Refresh Ent Calendar '||get_time);
235 end if;
236 --
237 if g_debug then
238 write_to_log_file_n('Refresh 445 Reporting Calendar '||get_time);
239 end if;
240 if BSC_BSC_ADAPTER.load_reporting_calendar(g_445_cal_id,g_options,g_number_options)=false then
241 --bug#3973335
242 p_error_message:=BSC_BSC_ADAPTER.g_status_message;
243 raise g_exception;
244 end if;
245 if g_debug then
246 write_to_log_file_n('Done Refresh 445 Calendar '||get_time);
247 end if;
248 --
249 if g_debug then
250 write_to_log_file_n('Refresh Greg Reporting Calendar '||get_time);
251 end if;
252 if BSC_BSC_ADAPTER.load_reporting_calendar(g_greg_cal_id,g_options,g_number_options)=false then
253 --bug#3973335
254 p_error_message:=BSC_BSC_ADAPTER.g_status_message;
255 raise g_exception;
256 end if;
257 if g_debug then
258 write_to_log_file_n('Done Refresh Greg Calendar '||get_time);
259 end if;
260 Exception when others then
261 g_status_message:=sqlerrm;
262 write_to_log_file_n('Error in refresh_reporting_calendars '||g_status_message||get_time);
263 raise;
264 End;
265
266 procedure init_mem_values(p_mode varchar2) is
267 Begin
268 if g_init_mem is null or g_init_mem=false then
269 get_bsc_greg_fiscal_year;
270 init_cal_per_ids;
271 if p_mode='full' then
272 load_fii_time_day_full;
273 else
274 load_fii_time_day_inc;
275 end if;
276 get_ent_cal_start_date(p_mode);
277 get_445_cal_start_date(p_mode);
278 get_greg_cal_start_date(p_mode);
279 g_init_mem:=true;
280 end if;
281 Exception when others then
282 g_status_message:=sqlerrm;
283 write_to_log_file_n('Error in init_mem_values '||sqlerrm||get_time);
284 raise;
285 End;
286
287 /*
288 3990678
289 when 445 calendar and ent cal starts on diff dates, we had an issue with weeks.
290 when ent cal year starts, week will not reset to 1 . it crosses through the
291 boundary of ent year. the difficulty is that in BSC, we expect all periodicities
292 to reset at year boundary. if we look at bsc_db_calendar, all periodicities have the
293 same year. this means the same period value of week cannot cross across ent year.
294 to solve this issue, we have to create ent week. we are going to start the week from 1
295 at the start of the year. there is no impact because the periods of the week are only
296 internal representation. if we look at bsc_sys_periods_tl, the week display fields will
297 still say 05-APR-1997 etc. in DBI, 05-APR-1997 may be week 14 of year 1997. in BSC, this will
298 be week 1, 1998.
299 */
300 procedure correct_ent_week(p_mode varchar2) is
301 --
302 cursor c1(p_calendar number) is
303 select to_date(calendar_year||'/'||calendar_month||'/'||calendar_day,'YYYY/MM/DD'),week52,year from bsc_db_calendar where calendar_id=p_calendar
304 order by calendar_year desc,calendar_month desc,calendar_day desc;
305 --
306 l_stmt varchar2(20000);
307 TYPE CurTyp IS REF CURSOR;
308 cv CurTyp;
309 --
310 l_index number;
311 l_max_date date;
312 l_prev_week number;--ent week for week
313 l_prev_year number;--ent year for week
314 l_prev_fii_week varchar2(40);
315 l_week_change boolean;
316 l_week_last_yr number;
317 l_ent_start_date date;
318 l_ent_yr_index number;
319 --
320 Begin
321 if g_debug then
322 write_to_log_file_n('In correct_ent_week'||get_time);
323 end if;
324
325 --bug fix 5461356
326 --get first year start date from fii_ent_year
327 l_ent_yr_index :=1;
328 l_ent_start_date:= g_dbi_ent_year(l_ent_yr_index).start_date;
329
330 --if initial, then assume that cal start date is part of g_dbi_cal_record
331 if p_mode='full' then
332 --count the number of weeks in the last year bug 5461356
333 --I am counting this so that while setting the week ids in reverse order,
334 --I can initialize week counter properly, I can not assume how many weeks will be there in
335 -- first incomplete year
336 l_week_last_yr :=1;
337 l_prev_week :=g_dbi_cal_record(1).week_id;
338 for i in 1..g_num_dbi_cal_record loop
339 -- increment week counter when the week changes
340 -- bug 5461356
341 if g_dbi_cal_record(i).week_id<>l_prev_week then
342 l_prev_week:=g_dbi_cal_record(i).week_id;
343 l_week_last_yr := l_week_last_yr+1;
344 end if;
345 -- do not assume that start date is same for every year
346 if to_char(l_ent_start_date,'MM/DD/YYYY')=to_char(g_dbi_cal_record(i).report_date,'MM/DD/YYYY') then
347 l_index:=i;
348 exit;
349 end if;
350 end loop;
351
352 if g_debug then
353 write_to_log_file_n('l_index='||l_index);
354 end if;
355 if l_index is null then
356 write_to_log_file_n('could not locate start month/date');
357 raise g_exception;
358 end if;
359 l_index:=l_index-1; --set to one day prior to start, 31 mar 1997
360 if l_index>0 then
361 -- bug 5461356
362 -- I can not assume number of weeks in the last incomplete year, it could be 54 also
363 -- as DBI year is 365+/- 14
364 if(l_week_last_yr> 53) then
365 l_prev_week := l_week_last_yr;
366 else
367 l_prev_week:=53;
368 end if;
369 l_prev_year:=substr(g_dbi_cal_record(l_index).ent_period_id,1,4);
370 l_prev_fii_week:=g_dbi_cal_record(l_index).week_id;
371 update_dbi_445_ent_week(l_prev_fii_week,l_prev_week,l_prev_year);
372 for i in reverse 1..l_index loop
373 if g_dbi_cal_record(i).week_id<>l_prev_fii_week then
374 l_prev_week:=l_prev_week-1;
375 l_prev_year:=substr(g_dbi_cal_record(i).ent_period_id,1,4);
376 l_prev_fii_week:=g_dbi_cal_record(i).week_id;
377 update_dbi_445_ent_week(l_prev_fii_week,l_prev_week,l_prev_year);
378 end if;
379 g_dbi_cal_record(i).ent_week_id:=l_prev_week;
380 end loop;
381 end if;
382 l_index:=l_index+1;--reset it to the start of the ent year, 01 apr 1997
383 l_prev_week:=53;--prev week
384 if l_index=1 then --only when the start date of the ent year is the first record in g_dbi_cal_record
385 l_prev_fii_week:=g_dbi_cal_record(l_index).week_id;
386 l_prev_year:=substr(g_dbi_cal_record(l_index).ent_period_id,1,4);
387 else
388 l_prev_fii_week:=g_dbi_cal_record(l_index-1).week_id;
389 l_prev_year:=substr(g_dbi_cal_record(l_index-1).ent_period_id,1,4);
390 end if;
391 update_dbi_445_ent_week(l_prev_fii_week,l_prev_week,l_prev_year);
392 else
393 --get the max date from bsc_sys_periods
394 --INCREMENTAL
395 if g_debug then
396 write_to_log_file_n('select to_date(calendar_year,calendar_month,calendar_day,week52,year from bsc_db_calendar where calendar_id=p_calendar
397 order by calendar_year desc,calendar_month desc,calendar_day desc using '||g_ent_cal_id);
398 end if;
399 open c1(g_ent_cal_id);
400 fetch c1 into l_max_date,l_prev_week,l_prev_year;
401 close c1;
402 if g_debug then
403 write_to_log_file_n('l_max_date='||l_max_date||', l_prev_week='||l_prev_week||', l_prev_year='||l_prev_year);
404 end if;
405 l_stmt:='select week_id from fii_time_week where week_id in (select week_id from fii_time_day where report_date=:1)';
406 if g_debug then
407 write_to_log_file_n(l_stmt);
408 end if;
409 open cv for l_stmt using l_max_date;
410 fetch cv into l_prev_fii_week;
411 if g_debug then
412 write_to_log_file_n('l_prev_fii_week='||l_prev_fii_week);
413 end if;
414 l_index:=1;
415 --assume dates are consequetive, this means that the first date in g_dbi_cal_record is l_max_date +1
416 end if;
417 if g_debug then
418 write_to_log_file_n('Going to correct data. ');
419 write_to_log_file('l_prev_week='||l_prev_week);
420 write_to_log_file('l_prev_year='||l_prev_year);
421 write_to_log_file('l_prev_fii_week='||l_prev_fii_week);
422 write_to_log_file('l_index='||l_index);
423 end if;
424 g_num_ent_week:=0;
425 for i in l_index..g_num_dbi_cal_record loop
426 l_week_change:=false;
427 if g_dbi_cal_record(i).week_id<>l_prev_fii_week then
428 l_prev_week:=l_prev_week+1; --this can become 54 if this date is ent year start. will be reset to 1 in the next if...
429 l_prev_year:=substr(g_dbi_cal_record(i).ent_period_id,1,4);
430 l_prev_fii_week:=g_dbi_cal_record(i).week_id;
431 update_dbi_445_ent_week(l_prev_fii_week,l_prev_week,l_prev_year);
432 l_week_change:=true;
433 end if;
434 --don't assume that every year start on same date bug 5461356
435 if to_char(l_ent_start_date,'MM/DD/YYYY')=to_char(g_dbi_cal_record(i).report_date,'MM/DD/YYYY') then
436 -- will not change the following logic for bug 5461356, as this is already agreed upon format
437 --if there is no week change as we cross the ent boundary, have to create a record for this week end in bsc sys periods
438 --g_ent_week holds only the additional weeks at ent year boundary we need to create
439 if l_week_change=false and i>1 then
440 g_num_ent_week:=g_num_ent_week+1;
441 g_ent_week(g_num_ent_week).week_id:=null;
442 g_ent_week(g_num_ent_week).year_id:=get_dbi_445_year(l_prev_fii_week);--not used anywhere
443 g_ent_week(g_num_ent_week).sequence:=l_prev_week;
444 g_ent_week(g_num_ent_week).name:=to_char(g_dbi_cal_record(i-1).report_date,'dd-Mon-rr');--week end date
445 g_ent_week(g_num_ent_week).ent_week_id:=l_prev_week;
446 g_ent_week(g_num_ent_week).ent_year_id:=substr(g_dbi_cal_record(i-1).ent_period_id,1,4);
447 /* 4992925 earlier we had a check on if g_ent_week(g_num_ent_week).year_id is null then
448 since year_id is not used we stop this check*/
449 end if;
450 l_prev_week:=1;
451 l_prev_year:=substr(g_dbi_cal_record(i).ent_period_id,1,4);
452 update_dbi_445_ent_week(l_prev_fii_week,l_prev_week,l_prev_year);
453 /*
454 there is an issue here. (soln is the code above of creating records in g_ent_week)
455 imagine that the 445 week is going across the ent year boundary
456 29-mar 30-mar 31-mar 1-apr 2apr
457 13 14 14 14 14
458 now, the ent week is going to be
459 29-mar 30-mar 31-mar 1-apr 2apr
460 51 52 52 1 1
461 in bsc_sys_periods, there is an entry for the week ending 29 mar. this is week 51
462 for the week ending 05-apr, the entry will say ent week of 1
463 but, this week now only has 5 days, 01-apr to 05-apr. when the user will see weekly
464 aggregation, the data for 30-mar and 31-mar will not be seen. the MV has the agg for
465 these 2 dates. but, there is no entry in bsc_sys_periods for week 52.
466 Solutions:
467 1 do we create a row in bsc sys periods tl for week 52? what if the language is chinese? we cannot assume 01-apr-2003 format
468 2 do we ask fii team to see how they generate the name?
469 checked FII_TIME_C. they have hard coded the format
470 insert into fii_time_week ...
471 name,
472 ...)
473 values
474 (...
475 to_char(l_week_end,'dd-Mon-rr'),
476 );
477 Can we also hard code the format
478
479 Asked patricia is its ok for us to create a row for the week ending on the fiscal year boundary. so in bsc sys periods
480 user sees week ending 29 mar, another week ending 31 mar and then another week ending 05 apr. in DBI, they will
481 not see the week ending 31 mar. patricia said this is ok since even now, users are used to this behavior in bsc
482 regarding the format, asked fii team. it seems the upper management in fii team has approved the format. its not
483 multi lingual
484 */
485 --find next year start date
486 l_ent_yr_index :=l_ent_yr_index+1;
487 if(l_ent_yr_index<=g_num_dbi_ent_year) then
488 l_ent_start_date:= g_dbi_ent_year(l_ent_yr_index).start_date;
489 else
490 l_ent_start_date:= g_ent_start_date;
491 end if;
492 end if;
493 g_dbi_cal_record(i).ent_week_id:=l_prev_week;
494 end loop;
495 if g_debug then
496 write_to_log_file_n('Output from g_dbi_445_week');
497 for i in 1..g_num_dbi_445_week loop
498 write_to_log_file(g_dbi_445_week(i).week_id||' '||g_dbi_445_week(i).sequence||' '||g_dbi_445_week(i).ent_week_id||' '||
499 g_dbi_445_week(i).ent_year_id);
500 end loop;
501 write_to_log_file_n('Output from g_ent_week, the Extra weeks created');
502 for i in 1..g_num_ent_week loop
503 write_to_log_file(g_ent_week(i).ent_year_id||' '||g_ent_week(i).sequence||' '||g_ent_week(i).ent_week_id||' '||g_ent_week(i).name);
504 end loop;
505 end if;
506 Exception when others then
507 g_status_message:=sqlerrm;
508 write_to_log_file_n('Error in correct_ent_week '||sqlerrm||get_time);
509 raise;
510 End;
511
512 procedure update_dbi_445_ent_week(
513 p_prev_fii_week varchar2,
514 p_prev_week number,
515 p_prev_year number) is
516 Begin
517 --if g_debug then
518 --write_to_log_file_n('In update_dbi_445_ent_week '||p_prev_fii_week||' '||p_prev_week);
519 --end if;
520 for i in 1..g_num_dbi_445_week loop
521 if g_dbi_445_week(i).week_id=p_prev_fii_week then
522 g_dbi_445_week(i).ent_week_id:=p_prev_week;
523 g_dbi_445_week(i).ent_year_id:=p_prev_year;
524 exit;
525 end if;
526 end loop;
527 Exception when others then
528 g_status_message:=sqlerrm;
529 write_to_log_file_n('Error in update_dbi_445_ent_week '||sqlerrm||get_time);
530 raise;
531 End;
532
533 function get_dbi_445_year(p_prev_fii_week varchar2) return number is
534 Begin
535 for i in 1..g_num_dbi_445_week loop
536 if g_dbi_445_week(i).week_id=p_prev_fii_week then
537 return g_dbi_445_week(i).year_id;
538 end if;
539 end loop;
540 return null;
541 Exception when others then
542 g_status_message:=sqlerrm;
543 write_to_log_file_n('Error in get_dbi_445_year '||sqlerrm||get_time);
544 raise;
545 End;
546
547 /*
548 return status
549 0 no refresh reqd
550 1 full refresh reqd
551 2 inc refresh
552 */
553 function check_for_inc_refresh return number is
554 --
555 /*cursor c1 is select 1 from bsc_db_calendar,bsc_sys_calendars_b where edw_calendar_type_id=1 and edw_calendar_id =1001
556 and bsc_db_calendar.calendar_id=bsc_sys_calendars_b.calendar_id and rownum=1;
557 cursor c2 is select 1 from mlog$_fii_time_day where rownum=1;
558 cursor c3 is select 1 from mlog$_fii_time_day where dmltype$$ <>'I' and rownum=1;
559 */
560 --
561 l_stmt varchar2(20000);
562 TYPE CurTyp IS REF CURSOR;
563 c1 CurTyp;
564 c2 CurTyp;
565 c3 CurTyp;
566 l_res number;
567 Begin
568 l_res:=null;
569 l_stmt:='select 1 from bsc_db_calendar,bsc_sys_calendars_b where edw_calendar_type_id=1 and edw_calendar_id =1001
570 and bsc_db_calendar.calendar_id=bsc_sys_calendars_b.calendar_id and rownum=1';
571 if g_debug then
572 write_to_log_file_n(l_stmt);
573 end if;
574 open c1 for l_stmt;
575 fetch c1 into l_res;
576 close c1;
577 if l_res is null then
578 if g_debug then
579 write_to_log_file_n('DBI calendar not brought in. Full refresh');
580 end if;
581 return 1;
582 end if;
583 --see if mlog$_fii_time_day has only insert rows
584 /*4769746
585 checking mlog of fii_time_day has the following disadvantage. if a dependent mv never refreshes, this code may always find the same entries
586 in the mv log and do a full refresh (when there are complex changes)
587 a possible approach around this is to have our dummy mv with mv log on top of this. we refresh our mv and look at our mv's snapshot log
588 if fii mvlog has 1000 entries and they do not change from t1 to t2, our mv log will be empty.
589 however, using a mv ontop of fii time day table has the disadv that unless this mv is refreshed, fii time day mv log will keep growing. this can be
590 a potential issue.
591 another approach is to get some max date from mv log and save it somewhere and next time see if there are records beyond this max date. SNAPTIME
592 is always set to jan 4000. so we cannot use this, since t1 and t2 will see jan 4000. using such hidden columns can also be dangerous since
593 server team may modify its behavior
594 the perf gain of full vs inc is small. in prod systems, the chances that mv log for fii time day will have data in it that is not cleaned up
595 is small. even if we keep doing full refresh each time, its fine. note> the max time is in refreshing the reporting calendar. this table has to
596 be refreshed fully in full or inc mode. so no big diff between the two
597 putting the right value for fiscal change is very imp. fiscal change=1 will trigger data cleanup of dependent kpis
598 we have g_ent_fiscal_change, g_445_fiscal_change and g_greg_fiscal_change to handle this now
599 */
600 l_res:=null;
601 l_stmt:='select 1 from mlog$_fii_time_day where rownum=1';
602 if g_debug then
603 write_to_log_file_n(l_stmt);
604 end if;
605 open c2 for l_stmt;
606 fetch c2 into l_res;
607 close c2;
608 if l_res is null then
609 if g_debug then
610 write_to_log_file_n('No inc change in mlog$_fii_time_day');
611 end if;
612 return 0;
613 end if;
614 l_res:=null;
615 l_stmt:='select 1 from mlog$_fii_time_day where dmltype$$ <>''I'' and rownum=1';
616 if g_debug then
617 write_to_log_file_n(l_stmt);
618 end if;
619 open c3 for l_stmt;
620 fetch c3 into l_res;
621 close c3;
622 if l_res is null then
623 if g_debug then
624 write_to_log_file_n('Only insert rows. Inc change');
625 end if;
626 return 2;
627 else
628 if g_debug then
629 write_to_log_file_n('Complex change...full refresh');
630 end if;
631 --g_fiscal_change:=1;
632 return 1;
633 end if;
634 Exception when others then
635 g_status_message:=sqlerrm;
636 write_to_log_file_n('Error in check_for_inc_refresh '||g_status_message||get_time);
637 raise;
638 End;
639
640 /*
641 Private API
642 */
643
644 procedure load_dbi_cal_into_bsc_full is
645 --
646 l_error varchar2(4000);
647 --
648 Begin
649 --bsc_sys_calendars_b,bsc_sys_calendars_tl,bsc_sys_periods,bsc_sys_periods_tl,bsc_sys_periodicities
650 if g_debug then
651 write_to_log_file_n('In load_dbi_cal_into_bsc_full '||get_time);
652 end if;
653 --get the dbi cal info into memory
654 init_mem_values('full');
655 loadmem_ent_full;
656 loadmem_445_full;
657 loadmem_greg_full;
658 -----------
659 --3990678
660 --correct the week info
661 correct_ent_week('full');
662 calculate_day365('full',g_ent_cal_id);
663 calculate_day365('full',g_greg_cal_id);
664 calculate_day365_445('full');
665 if g_debug then
666 dmp_g_dbi_cal_record;
667 end if;
668 -----------
669 /*5461356 . we were deleting the metadata first. this means bsc_sys_periodicities got cleaned. then when we did
670 delete bsc_sys_periods where periodicity_id in bsc_sys_periodicities, it did not clean bsc_sys_periods and so there was unique
671 constraint error. soln-> first delete bsc_sys_periods and db calendar (delete_dbi_calendars), then periodicities and sys calendar */
672 delete_dbi_calendars;
673 delete_dbi_calendar_metadata;
674 load_dbi_ent_cal;
675 load_dbi_445_cal;
676 load_dbi_greg_cal;
677 --
678 --loadmem_ent_full; --old place
679 --loadmem_445_full; --old place
680 --loadmem_greg_full; --old place
681 ----
682 load_dbi_ent_cal_data;
683 --
684 load_dbi_445_cal_data;
685 --
686 load_dbi_greg_cal_data;
687 --
688 if g_debug then
689 write_to_log_file_n('Done load_dbi_cal_into_bsc_full '||get_time);
690 end if;
691 Exception when others then
692 g_status_message:=sqlerrm;
693 write_to_log_file_n('Error in load_dbi_cal_into_bsc_full '||g_status_message||get_time);
694 raise;
695 End;
696
697 procedure load_dbi_cal_into_bsc_inc is
698 --
699 --
700 Begin
701 --bsc_sys_calendars_b,bsc_sys_calendars_tl,bsc_sys_periods,bsc_sys_periods_tl,bsc_sys_periodicities
702 if g_debug then
703 write_to_log_file_n('In load_dbi_cal_into_bsc_inc '||get_time);
704 end if;
705 --get the dbi cal info into memory
706 --looks like mlog$_fii_time_day has no column for greg month_id. this means that we
707 --cannot extend the greg calendar.
708 init_mem_values('inc');
709 --
710 loadmem_ent_inc;
711 loadmem_445_inc;
712 ---bug fix 5461356 if thr are no records don't process anything
713 if g_num_dbi_cal_record=0 then
714 return ;
715 end if;
716 -----------
717 --3990678
718 --correct the week info
719 correct_ent_week('inc');
720 calculate_day365('inc',g_ent_cal_id);
721 calculate_day365('inc',g_greg_cal_id);
722 calculate_day365_445('inc');
723 if g_debug then
724 dmp_g_dbi_cal_record;
725 end if;
726 -----------
727 if g_num_dbi_cal_record>0 then
728 ----
729 load_dbi_ent_cal_data;
730 --
731 load_dbi_445_cal_data;
732 end if;
733 if g_debug then
734 write_to_log_file_n('Done load_dbi_cal_into_bsc_inc '||get_time);
735 end if;
736 Exception when others then
737 g_status_message:=sqlerrm;
738 write_to_log_file_n('Error in load_dbi_cal_into_bsc_inc '||g_status_message||get_time);
739 raise;
740 End;
741
742
743 procedure get_bsc_greg_fiscal_year is
744 --
745 l_stmt varchar2(20000);
746 l_max_year number;
747 l_min_year number;
748 l_current_year number;
749 --
750 TYPE CurTyp IS REF CURSOR;
751 c1 CurTyp;
752 --
753 Begin
754 --fix bug#4607690: Currently we are reading the fiscal year from the bsc gregorian calendar.
755 -- Now we are going to take the year of SYSDATE. If this year is not available in FII_TIME_DAY
756 -- then we will use max(year) from FII_TIME_DAY.
757 l_stmt := 'select max(ent_year_id), min(ent_year_id), to_number(to_char(sysdate,''YYYY'')) from fii_time_day';
758 if g_debug then
759 write_to_log_file_n('cursor c1 is '||l_stmt||';'||get_time);
760 end if;
761 open c1 for l_stmt;
762 fetch c1 into l_max_year, l_min_year, l_current_year;
763 close c1;
764 if g_debug then
765 write_to_log_file_n('l_max_year='||l_max_year||' '||get_time);
766 write_to_log_file_n('l_min_year='||l_min_year||' '||get_time);
767 write_to_log_file_n('l_current_year='||l_current_year||' '||get_time);
768 end if;
769 if (l_current_year >= l_min_year) and (l_current_year <= l_max_year) then
770 g_bsc_greg_fiscal_year:=l_current_year;
771 else
772 g_bsc_greg_fiscal_year:=l_max_year;
773 end if;
774 if g_debug then
775 write_to_log_file_n('Finaly g_bsc_greg_fiscal_year='||g_bsc_greg_fiscal_year);
776 end if;
777 Exception when others then
778 g_status_message:=sqlerrm;
779 write_to_log_file_n('Error in get_bsc_greg_fiscal_year '||g_status_message||get_time);
780 raise;
781 End;
782
783 procedure init_cal_per_ids is
784 --
785 cursor c1(p_cal_id number) is select bsc_sys_periodicities.calendar_id,
786 bsc_sys_periodicities.periodicity_id,bsc_sys_periodicities.period_type_id ,
787 bsc_sys_calendars_b.short_name, bsc_sys_periodicities.short_name,bsc_sys_calendars_b.fiscal_change
788 from bsc_sys_periodicities ,bsc_sys_calendars_b
789 where bsc_sys_periodicities.calendar_id=bsc_sys_calendars_b.calendar_id
790 and bsc_sys_calendars_b.edw_calendar_type_id=1 and bsc_sys_calendars_b.edw_calendar_id=p_cal_id;
791 --
792 l_cal_id number;
793 l_cal_short_name varchar2(100);
794 l_per_id number;
795 l_per_short_name varchar2(100);
796 l_per_type_id number;
797 l_fiscal_change number;
798 --
799 Begin
800 if g_debug then
801 write_to_log_file_n('select bsc_sys_periodicities.periodicity_id,bsc_sys_periodicities.period_type_id '||
802 'from bsc_sys_periodicities ,bsc_sys_calendars_b '||
803 'where bsc_sys_periodicities.calendar_id=bsc_sys_calendars_b.calendar_id '||
804 'and bsc_sys_calendars_b.edw_calendar_type_id=1 and bsc_sys_calendars_b.edw_calendar_id=1001/1002/1003 ');
805 end if;
806 open c1(1001);
807 loop
808 fetch c1 into l_cal_id,l_per_id,l_per_type_id, l_cal_short_name, l_per_short_name,l_fiscal_change;
809 exit when c1%notfound;
810 g_ent_cal_id:=l_cal_id;
811 g_ent_fiscal_change:=l_fiscal_change;
812 if l_per_type_id=1 then
813 g_ent_day_per_id:=l_per_id;
814 elsif l_per_type_id=16 then
815 g_ent_week_per_id:=l_per_id;
816 elsif l_per_type_id=32 then
817 g_ent_period_per_id:=l_per_id;
818 elsif l_per_type_id=64 then
819 g_ent_qtr_per_id:=l_per_id;
820 elsif l_per_type_id=128 then
821 g_ent_year_per_id:=l_per_id;
822 end if;
823 end loop;
824 close c1;
825 if g_ent_cal_id is null then
826 g_ent_cal_id:=get_calendar_nextval;
827 g_ent_fiscal_change:=0;
828 g_ent_day_per_id:=get_periodicity_nextval;
829 g_ent_week_per_id:=get_periodicity_nextval;
830 g_ent_period_per_id:=get_periodicity_nextval;
831 g_ent_qtr_per_id:=get_periodicity_nextval;
832 g_ent_year_per_id:=get_periodicity_nextval;
833 end if;
834 ---
835 open c1(1002);
836 loop
837 fetch c1 into l_cal_id,l_per_id,l_per_type_id, l_cal_short_name, l_per_short_name,l_fiscal_change;
838 exit when c1%notfound;
839 g_445_cal_id:=l_cal_id;
840 g_445_fiscal_change:=l_fiscal_change;
841 g_445_cal_short_name := l_cal_short_name;
842 if l_per_type_id=1 then
843 g_445_day_per_id:=l_per_id;
844 g_445_day_short_name := l_per_short_name;
845 elsif l_per_type_id=16 then
846 g_445_week_per_id:=l_per_id;
847 g_445_week_short_name := l_per_short_name;
848 elsif l_per_type_id=32 then
849 g_445_p445_per_id:=l_per_id;
850 g_445_p445_short_name := l_per_short_name;
851 elsif l_per_type_id=128 then
852 g_445_year_per_id:=l_per_id;
853 g_445_year_short_name := l_per_short_name;
854 end if;
855 end loop;
856 close c1;
857 if g_445_cal_id is null then
858 g_445_cal_id:=get_calendar_nextval;
859 g_445_fiscal_change:=0;
860 g_445_day_per_id:=get_periodicity_nextval;
861 g_445_week_per_id:=get_periodicity_nextval;
862 g_445_p445_per_id:=get_periodicity_nextval;
863 g_445_year_per_id:=get_periodicity_nextval;
864 end if;
865 ---
866 open c1(1003);
867 loop
868 fetch c1 into l_cal_id,l_per_id,l_per_type_id, l_cal_short_name, l_per_short_name,l_fiscal_change;
869 exit when c1%notfound;
870 g_greg_cal_id:=l_cal_id;
871 g_greg_fiscal_change:=l_fiscal_change;
872 g_greg_cal_short_name := l_cal_short_name;
873 if l_per_type_id=1 then
874 g_greg_day_per_id:=l_per_id;
875 g_greg_day_short_name := l_per_short_name;
876 elsif l_per_type_id=32 then
877 g_greg_period_per_id:=l_per_id;
878 g_greg_period_short_name := l_per_short_name;
879 elsif l_per_type_id=64 then
880 g_greg_qtr_per_id:=l_per_id;
881 g_greg_qtr_short_name := l_per_short_name;
882 elsif l_per_type_id=128 then
883 g_greg_year_per_id:=l_per_id;
884 g_greg_year_short_name := l_per_short_name;
885 end if;
886 end loop;
887 close c1;
888 if g_greg_cal_id is null then
889 g_greg_cal_id:=get_calendar_nextval;
890 g_greg_fiscal_change:=0;
891 g_greg_day_per_id:=get_periodicity_nextval;
892 g_greg_period_per_id:=get_periodicity_nextval;
893 g_greg_qtr_per_id:=get_periodicity_nextval;
894 g_greg_year_per_id:=get_periodicity_nextval;
895 end if;
896 if g_debug then
897 write_to_log_file_n('Ent periodicities');
898 write_to_log_file('g_ent_cal_id='||g_ent_cal_id);
899 write_to_log_file('g_ent_fiscal_change='||g_ent_fiscal_change);
900 write_to_log_file('g_ent_day_per_id='||g_ent_day_per_id);
901 write_to_log_file('g_ent_week_per_id='||g_ent_week_per_id);
902 write_to_log_file('g_ent_period_per_id='||g_ent_period_per_id);
903 write_to_log_file('g_ent_qtr_per_id='||g_ent_qtr_per_id);
904 write_to_log_file('g_ent_year_per_id='||g_ent_year_per_id);
905 write_to_log_file_n('445 periodicities');
906 write_to_log_file('g_445_cal_id='||g_445_cal_id);
907 write_to_log_file('g_445_fiscal_change='||g_445_fiscal_change);
908 write_to_log_file('g_445_cal_short_name='||g_445_cal_short_name);
909 write_to_log_file('g_445_day_per_id='||g_445_day_per_id);
910 write_to_log_file('g_445_day__short_name='||g_445_day_short_name);
911 write_to_log_file('g_445_week_per_id='||g_445_week_per_id);
912 write_to_log_file('g_445_week_short_name='||g_445_week_short_name);
913 write_to_log_file('g_445_p445_per_id='||g_445_p445_per_id);
914 write_to_log_file('g_445_p445_short_name='||g_445_p445_short_name);
915 write_to_log_file('g_445_year_per_id='||g_445_year_per_id);
916 write_to_log_file('g_445_year_short_name='||g_445_year_short_name);
917 write_to_log_file_n('Greg periodicities');
918 write_to_log_file('g_greg_cal_id='||g_greg_cal_id);
919 write_to_log_file('g_greg_fiscal_change='||g_greg_fiscal_change);
920 write_to_log_file('g_greg_cal_short_name='||g_greg_cal_short_name);
921 write_to_log_file('g_greg_day_per_id='||g_greg_day_per_id);
922 write_to_log_file('g_greg_day_short_name='||g_greg_day_short_name);
923 write_to_log_file('g_greg_period_per_id='||g_greg_period_per_id);
924 write_to_log_file('g_greg_period_short_name='||g_greg_period_short_name);
925 write_to_log_file('g_greg_qtr_per_id='||g_greg_qtr_per_id);
926 write_to_log_file('g_greg_qtr_short_name='||g_greg_qtr_short_name);
927 write_to_log_file('g_greg_year_per_id='||g_greg_year_per_id);
928 write_to_log_file('g_greg_year_short='||g_greg_year_short_name);
929 end if;
930 Exception when others then
931 g_status_message:=sqlerrm;
932 write_to_log_file_n('Error in init_cal_per_ids '||g_status_message||get_time);
933 raise;
934 End;
935
936 procedure load_fii_time_day_full is
937 l_stmt varchar2(20000);
938 TYPE CurTyp IS REF CURSOR;
939 c_dbi CurTyp;
940 Begin
941 l_stmt:='select report_date,to_char(report_date,''DD''),to_char(report_date,''MM''),to_char(report_date,''YYYY''),
942 month_id,ent_period_id,week_id, start_date, end_date from fii_time_day order by report_date';
943 if g_debug then
944 write_to_log_file_n(l_stmt);
945 end if;
946 g_num_dbi_cal_record:=1;
947 open c_dbi for l_stmt;
948 loop
949 fetch c_dbi into g_dbi_cal_record(g_num_dbi_cal_record).report_date,
950 g_dbi_cal_record(g_num_dbi_cal_record).cal_day,
951 g_dbi_cal_record(g_num_dbi_cal_record).cal_month,
952 g_dbi_cal_record(g_num_dbi_cal_record).cal_year,
953 g_dbi_cal_record(g_num_dbi_cal_record).month_id,
954 g_dbi_cal_record(g_num_dbi_cal_record).ent_period_id,
955 g_dbi_cal_record(g_num_dbi_cal_record).week_id,
956 g_dbi_cal_record(g_num_dbi_cal_record).start_date,
957 g_dbi_cal_record(g_num_dbi_cal_record).end_date;
958 exit when c_dbi%notfound;
959 g_dbi_cal_record(g_num_dbi_cal_record).row_num:=g_num_dbi_cal_record;
960 g_num_dbi_cal_record:=g_num_dbi_cal_record+1;
961 end loop;
962 close c_dbi;
963 g_num_dbi_cal_record:=g_num_dbi_cal_record-1;
964 if g_debug then
965 write_to_log_file_n('g_num_dbi_cal_record='||g_num_dbi_cal_record);
966 end if;
967 /*if g_debug then
968 for i in 1..g_num_dbi_cal_record loop
969 write_to_log_file(g_dbi_cal_record(i).report_date||' '||g_dbi_cal_record(i).cal_day||' '||
970 g_dbi_cal_record(i).cal_month||' '||g_dbi_cal_record(i).cal_year||' '||
971 g_dbi_cal_record(i).month_id||' '||g_dbi_cal_record(i).ent_period_id||' '||
972 g_dbi_cal_record(i).week_id||' '||g_dbi_cal_record(i).row_num);
973 end loop;
974 end if;*/
975 Exception when others then
976 g_status_message:=sqlerrm;
977 write_to_log_file_n('Error in load_fii_time_day_full '||g_status_message||get_time);
978 raise;
979 End;
980
981 procedure load_fii_time_day_inc is
982 l_stmt varchar2(20000);
983 TYPE CurTyp IS REF CURSOR;
984 c_dbi CurTyp;
985 Begin
986 l_stmt:='select report_date,to_char(report_date,''DD''),to_char(report_date,''MM''),to_char(report_date,''YYYY''),
987 ent_period_id,week_id from mlog$_fii_time_day
988 where not exists (select 1 from bsc_sys_periods where
989 periodicity_id=:1 and time_fk=to_char(report_date,''MM/DD/YYYY''))
990 order by report_date';
991 if g_debug then
992 write_to_log_file_n(l_stmt||' '||g_ent_day_per_id);
993 end if;
994 g_num_dbi_cal_record:=1;
995 open c_dbi for l_stmt using g_ent_day_per_id;
996 loop
997 fetch c_dbi into g_dbi_cal_record(g_num_dbi_cal_record).report_date,
998 g_dbi_cal_record(g_num_dbi_cal_record).cal_day,
999 g_dbi_cal_record(g_num_dbi_cal_record).cal_month,
1000 g_dbi_cal_record(g_num_dbi_cal_record).cal_year,
1001 g_dbi_cal_record(g_num_dbi_cal_record).ent_period_id,
1002 g_dbi_cal_record(g_num_dbi_cal_record).week_id;
1003 exit when c_dbi%notfound;
1004 g_dbi_cal_record(g_num_dbi_cal_record).row_num:=g_num_dbi_cal_record;
1005 g_num_dbi_cal_record:=g_num_dbi_cal_record+1;
1006 end loop;
1007 close c_dbi;
1008 g_num_dbi_cal_record:=g_num_dbi_cal_record-1;
1009 if g_debug then
1010 write_to_log_file_n('g_num_dbi_cal_record='||g_num_dbi_cal_record);
1011 end if;
1012 /*if g_debug then
1013 for i in 1..g_num_dbi_cal_record loop
1014 write_to_log_file(g_dbi_cal_record(i).report_date||' '||g_dbi_cal_record(i).cal_day||' '||
1015 g_dbi_cal_record(i).cal_month||' '||g_dbi_cal_record(i).cal_year||' '||
1016 g_dbi_cal_record(i).month_id||' '||g_dbi_cal_record(i).ent_period_id||' '||
1017 g_dbi_cal_record(i).week_id||' '||g_dbi_cal_record(i).row_num);
1018 end loop;
1019 end if;*/
1020 Exception when others then
1021 g_status_message:=sqlerrm;
1022 write_to_log_file_n('Error in load_fii_time_day_inc '||g_status_message||get_time);
1023 raise;
1024 End;
1025
1026 procedure loadmem_ent_full is
1027 l_stmt varchar2(20000);
1028 TYPE CurTyp IS REF CURSOR;
1029 c1 CurTyp;
1030 c2 CurTyp;
1031 c3 CurTyp;
1032 Begin
1033 g_num_dbi_ent_period:=1;
1034 g_num_dbi_ent_qtr:=1;
1035 g_num_dbi_ent_year:=1;
1036 ---period
1037 l_stmt:='select ent_period_id,ent_year_id,sequence,name, start_date, end_date from FII_TIME_ENT_PERIOD order by ent_period_id';
1038 if g_debug then
1039 write_to_log_file_n(l_stmt);
1040 end if;
1041 open c1 for l_stmt;
1042 loop
1043 fetch c1 into
1044 g_dbi_ent_period(g_num_dbi_ent_period).ent_period_id,
1045 g_dbi_ent_period(g_num_dbi_ent_period).ent_year_id,
1046 g_dbi_ent_period(g_num_dbi_ent_period).sequence,
1047 g_dbi_ent_period(g_num_dbi_ent_period).name,
1048 g_dbi_ent_period(g_num_dbi_ent_period).start_date,
1049 g_dbi_ent_period(g_num_dbi_ent_period).end_date;
1050 exit when c1%notfound;
1051 g_num_dbi_ent_period:=g_num_dbi_ent_period+1;
1052 end loop;
1053 close c1;
1054 g_num_dbi_ent_period:=g_num_dbi_ent_period-1;
1055 if g_debug then
1056 write_to_log_file_n('g_num_dbi_ent_period='||g_num_dbi_ent_period);
1057 end if;
1058 ---qtr
1059 l_stmt:='select ent_qtr_id,ent_year_id,sequence,name, start_date, end_date from FII_TIME_ENT_QTR order by ent_qtr_id';
1060 if g_debug then
1061 write_to_log_file_n(l_stmt);
1062 end if;
1063 open c2 for l_stmt;
1064 loop
1065 fetch c2 into
1066 g_dbi_ent_qtr(g_num_dbi_ent_qtr).ent_qtr_id,
1067 g_dbi_ent_qtr(g_num_dbi_ent_qtr).ent_year_id,
1068 g_dbi_ent_qtr(g_num_dbi_ent_qtr).sequence,
1069 g_dbi_ent_qtr(g_num_dbi_ent_qtr).name,
1070 g_dbi_ent_qtr(g_num_dbi_ent_qtr).start_date,
1071 g_dbi_ent_qtr(g_num_dbi_ent_qtr).end_date;
1072 exit when c2%notfound;
1073 g_num_dbi_ent_qtr:=g_num_dbi_ent_qtr+1;
1074 end loop;
1075 close c2;
1076 g_num_dbi_ent_qtr:=g_num_dbi_ent_qtr-1;
1077 if g_debug then
1078 write_to_log_file_n('g_num_dbi_ent_qtr='||g_num_dbi_ent_qtr);
1079 end if;
1080 ---year
1081 l_stmt:='select ent_year_id,sequence,name, start_date, end_date from FII_TIME_ENT_YEAR order by ent_year_id';
1082 if g_debug then
1083 write_to_log_file_n(l_stmt);
1084 end if;
1085 open c3 for l_stmt;
1086 loop
1087 fetch c3 into
1088 g_dbi_ent_year(g_num_dbi_ent_year).ent_year_id,
1089 g_dbi_ent_year(g_num_dbi_ent_year).sequence,
1090 g_dbi_ent_year(g_num_dbi_ent_year).name,
1091 g_dbi_ent_year(g_num_dbi_ent_year).start_date,
1092 g_dbi_ent_year(g_num_dbi_ent_year).end_date;
1093 exit when c3%notfound;
1094 g_num_dbi_ent_year:=g_num_dbi_ent_year+1;
1095 end loop;
1096 close c3;
1097 g_num_dbi_ent_year:=g_num_dbi_ent_year-1;
1098 if g_debug then
1099 write_to_log_file_n('g_num_dbi_ent_year='||g_num_dbi_ent_year);
1100 end if;
1101 ---
1102 Exception when others then
1103 g_status_message:=sqlerrm;
1104 write_to_log_file_n('Error in loadmem_ent_full '||g_status_message||get_time);
1105 raise;
1106 End;
1107
1108 procedure loadmem_ent_inc is
1109 l_stmt varchar2(20000);
1110 TYPE CurTyp IS REF CURSOR;
1111 c1 CurTyp;
1112 c2 CurTyp;
1113 c3 CurTyp;
1114 Begin
1115 g_num_dbi_ent_period:=1;
1116 g_num_dbi_ent_qtr:=1;
1117 g_num_dbi_ent_year:=1;
1118 ---period
1119 l_stmt:='select ent_period_id,ent_year_id,sequence,name,start_date, end_date from FII_TIME_ENT_PERIOD
1120 where ent_period_id not in (select to_number(time_fk) from bsc_sys_periods where periodicity_id=:1)
1121 order by ent_period_id';
1122 if g_debug then
1123 write_to_log_file_n(l_stmt||' '||g_ent_period_per_id);
1124 end if;
1125 open c1 for l_stmt using g_ent_period_per_id;
1126 loop
1127 fetch c1 into
1128 g_dbi_ent_period(g_num_dbi_ent_period).ent_period_id,
1129 g_dbi_ent_period(g_num_dbi_ent_period).ent_year_id,
1130 g_dbi_ent_period(g_num_dbi_ent_period).sequence,
1131 g_dbi_ent_period(g_num_dbi_ent_period).name,
1132 g_dbi_ent_period(g_num_dbi_ent_period).start_date,
1133 g_dbi_ent_period(g_num_dbi_ent_period).end_date;
1134 exit when c1%notfound;
1135 g_num_dbi_ent_period:=g_num_dbi_ent_period+1;
1136 end loop;
1137 close c1;
1138 g_num_dbi_ent_period:=g_num_dbi_ent_period-1;
1139 if g_debug then
1140 write_to_log_file_n('g_num_dbi_ent_period='||g_num_dbi_ent_period);
1141 end if;
1142 ---qtr
1143 l_stmt:='select ent_qtr_id,ent_year_id,sequence,name, start_date, end_date from FII_TIME_ENT_QTR
1144 where ent_qtr_id not in (select to_number(time_fk) from bsc_sys_periods where periodicity_id=:1)
1145 order by ent_qtr_id';
1146 if g_debug then
1147 write_to_log_file_n(l_stmt||' '||g_ent_qtr_per_id);
1148 end if;
1149 open c2 for l_stmt using g_ent_qtr_per_id;
1150 loop
1151 fetch c2 into
1152 g_dbi_ent_qtr(g_num_dbi_ent_qtr).ent_qtr_id,
1153 g_dbi_ent_qtr(g_num_dbi_ent_qtr).ent_year_id,
1154 g_dbi_ent_qtr(g_num_dbi_ent_qtr).sequence,
1155 g_dbi_ent_qtr(g_num_dbi_ent_qtr).name,
1156 g_dbi_ent_qtr(g_num_dbi_ent_qtr).start_date,
1157 g_dbi_ent_qtr(g_num_dbi_ent_qtr).end_date;
1158 exit when c2%notfound;
1159 g_num_dbi_ent_qtr:=g_num_dbi_ent_qtr+1;
1160 end loop;
1161 close c2;
1162 g_num_dbi_ent_qtr:=g_num_dbi_ent_qtr-1;
1163 if g_debug then
1164 write_to_log_file_n('g_num_dbi_ent_qtr='||g_num_dbi_ent_qtr);
1165 end if;
1166 ---year
1167 l_stmt:='select ent_year_id,sequence,name, start_date, end_date from FII_TIME_ENT_YEAR
1168 where ent_year_id not in (select to_number(time_fk) from bsc_sys_periods where periodicity_id=:1)
1169 order by ent_year_id';
1170 if g_debug then
1171 write_to_log_file_n(l_stmt||' '||g_ent_year_per_id);
1172 end if;
1173 open c3 for l_stmt using g_ent_year_per_id;
1174 loop
1175 fetch c3 into
1176 g_dbi_ent_year(g_num_dbi_ent_year).ent_year_id,
1177 g_dbi_ent_year(g_num_dbi_ent_year).sequence,
1178 g_dbi_ent_year(g_num_dbi_ent_year).name,
1179 g_dbi_ent_year(g_num_dbi_ent_year).start_date,
1180 g_dbi_ent_year(g_num_dbi_ent_year).end_date;
1181 exit when c3%notfound;
1182 g_num_dbi_ent_year:=g_num_dbi_ent_year+1;
1183 end loop;
1184 close c3;
1185 g_num_dbi_ent_year:=g_num_dbi_ent_year-1;
1186 if g_debug then
1187 write_to_log_file_n('g_num_dbi_ent_year='||g_num_dbi_ent_year);
1188 end if;
1189 ---
1190 Exception when others then
1191 g_status_message:=sqlerrm;
1192 write_to_log_file_n('Error in loadmem_ent_inc '||g_status_message||get_time);
1193 raise;
1194 End;
1195
1196 procedure loadmem_445_full is
1197 l_stmt varchar2(20000);
1198 TYPE CurTyp IS REF CURSOR;
1199 c1 CurTyp;
1200 c2 CurTyp;
1201 c3 CurTyp;
1202 Begin
1203 ---week
1204 g_num_dbi_445_week:=1;
1205 g_num_dbi_445_p445:=1;
1206 g_num_dbi_445_year:=1;
1207 --Added start_date, end_date for bug 4482933
1208 l_stmt:='select week_id,substr(week_id,1,4),substr(week_id,1,4),sequence,name, start_date, end_date from FII_TIME_WEEK order by week_id';
1209 if g_debug then
1210 write_to_log_file_n(l_stmt);
1211 end if;
1212 open c1 for l_stmt;
1213 loop
1214 fetch c1 into
1215 g_dbi_445_week(g_num_dbi_445_week).week_id,
1216 g_dbi_445_week(g_num_dbi_445_week).year_id,
1217 g_dbi_445_week(g_num_dbi_445_week).ent_year_id,
1218 g_dbi_445_week(g_num_dbi_445_week).sequence,
1219 g_dbi_445_week(g_num_dbi_445_week).name,
1220 g_dbi_445_week(g_num_dbi_445_week).start_date,
1221 g_dbi_445_week(g_num_dbi_445_week).end_date;
1222 exit when c1%notfound;
1223 g_num_dbi_445_week:=g_num_dbi_445_week+1;
1224 end loop;
1225 close c1;
1226 g_num_dbi_445_week:=g_num_dbi_445_week-1;
1227 if g_debug then
1228 write_to_log_file_n('g_num_dbi_445_week='||g_num_dbi_445_week);
1229 end if;
1230 ---p445
1231 l_stmt:='select period445_id,year445_id,sequence,name, start_date, end_date from fii_time_p445 order by period445_id';
1232 if g_debug then
1233 write_to_log_file_n(l_stmt);
1234 end if;
1235 open c2 for l_stmt;
1236 loop
1237 fetch c2 into
1238 g_dbi_445_p445(g_num_dbi_445_p445).period445_id,
1239 g_dbi_445_p445(g_num_dbi_445_p445).year445_id,
1240 g_dbi_445_p445(g_num_dbi_445_p445).sequence,
1241 g_dbi_445_p445(g_num_dbi_445_p445).name,
1242 g_dbi_445_p445(g_num_dbi_445_p445).start_date,
1243 g_dbi_445_p445(g_num_dbi_445_p445).end_date;
1244 exit when c2%notfound;
1245 g_num_dbi_445_p445:=g_num_dbi_445_p445+1;
1246 end loop;
1247 close c2;
1248 g_num_dbi_445_p445:=g_num_dbi_445_p445-1;
1249 if g_debug then
1250 write_to_log_file_n('g_num_dbi_445_p445='||g_num_dbi_445_p445);
1251 end if;
1252 ---year
1253 l_stmt:='select year445_id col1,year445_id col2,name, start_date, end_date from fii_time_year445 order by year445_id';
1254 if g_debug then
1255 write_to_log_file_n(l_stmt);
1256 end if;
1257 open c3 for l_stmt;
1258 loop
1259 fetch c3 into
1260 g_dbi_445_year(g_num_dbi_445_year).year445_id,
1261 g_dbi_445_year(g_num_dbi_445_year).sequence,
1262 g_dbi_445_year(g_num_dbi_445_year).name,
1263 g_dbi_445_year(g_num_dbi_445_year).start_date,
1264 g_dbi_445_year(g_num_dbi_445_year).end_date;
1265 exit when c3%notfound;
1266 g_num_dbi_445_year:=g_num_dbi_445_year+1;
1267 end loop;
1268 close c3;
1269 g_num_dbi_445_year:=g_num_dbi_445_year-1;
1270 if g_debug then
1271 write_to_log_file_n('g_num_dbi_445_year='||g_num_dbi_445_year);
1272 end if;
1273 ---
1274 Exception when others then
1275 g_status_message:=sqlerrm;
1276 write_to_log_file_n('Error in loadmem_445_full '||g_status_message||get_time);
1277 raise;
1278 End;
1279
1280 procedure loadmem_445_inc is
1281 l_stmt varchar2(20000);
1282 TYPE CurTyp IS REF CURSOR;
1283 c1 CurTyp;
1284 c2 CurTyp;
1285 c3 CurTyp;
1286 Begin
1287 ---week
1288 g_num_dbi_445_week:=1;
1289 g_num_dbi_445_p445:=1;
1290 g_num_dbi_445_year:=1;
1291 l_stmt:='select week_id,substr(week_id,1,4),substr(week_id,1,4),sequence,name, start_date, end_date from FII_TIME_WEEK
1292 where week_id not in (select to_number(time_fk) from bsc_sys_periods where periodicity_id=:1)
1293 order by week_id';
1294 if g_debug then
1295 write_to_log_file_n(l_stmt||' '||g_445_week_per_id);
1296 end if;
1297 open c1 for l_stmt using g_445_week_per_id;
1298 loop
1299 fetch c1 into
1300 g_dbi_445_week(g_num_dbi_445_week).week_id,
1301 g_dbi_445_week(g_num_dbi_445_week).year_id,
1302 g_dbi_445_week(g_num_dbi_445_week).ent_year_id,
1303 g_dbi_445_week(g_num_dbi_445_week).sequence,
1304 g_dbi_445_week(g_num_dbi_445_week).name,
1305 g_dbi_445_week(g_num_dbi_445_week).start_date,
1306 g_dbi_445_week(g_num_dbi_445_week).end_date;
1307 exit when c1%notfound;
1308 g_num_dbi_445_week:=g_num_dbi_445_week+1;
1309 end loop;
1310 close c1;
1311 g_num_dbi_445_week:=g_num_dbi_445_week-1;
1312 if g_debug then
1313 write_to_log_file_n('g_num_dbi_445_week='||g_num_dbi_445_week);
1314 end if;
1315 ---p445
1316 l_stmt:='select period445_id,year445_id,sequence,name, start_date, end_date from fii_time_p445
1317 where period445_id not in (select to_number(time_fk) from bsc_sys_periods where periodicity_id=:1)
1318 order by period445_id';
1319 if g_debug then
1320 write_to_log_file_n(l_stmt||' '||g_445_p445_per_id);
1321 end if;
1322 open c2 for l_stmt using g_445_p445_per_id;
1323 loop
1324 fetch c2 into
1325 g_dbi_445_p445(g_num_dbi_445_p445).period445_id,
1326 g_dbi_445_p445(g_num_dbi_445_p445).year445_id,
1327 g_dbi_445_p445(g_num_dbi_445_p445).sequence,
1328 g_dbi_445_p445(g_num_dbi_445_p445).name,
1329 g_dbi_445_p445(g_num_dbi_445_p445).start_date,
1330 g_dbi_445_p445(g_num_dbi_445_p445).end_date;
1331 exit when c2%notfound;
1332 g_num_dbi_445_p445:=g_num_dbi_445_p445+1;
1333 end loop;
1334 close c2;
1335 g_num_dbi_445_p445:=g_num_dbi_445_p445-1;
1336 if g_debug then
1337 write_to_log_file_n('g_num_dbi_445_p445='||g_num_dbi_445_p445);
1338 end if;
1339 ---year
1340 l_stmt:='select year445_id col1,year445_id col2,name, start_date, end_date from fii_time_year445
1341 where year445_id not in (select to_number(time_fk) from bsc_sys_periods where periodicity_id=:1)
1342 order by year445_id';
1343 if g_debug then
1344 write_to_log_file_n(l_stmt||' '||g_445_year_per_id);
1345 end if;
1346 open c3 for l_stmt using g_445_year_per_id;
1347 loop
1348 fetch c3 into
1349 g_dbi_445_year(g_num_dbi_445_year).year445_id,
1350 g_dbi_445_year(g_num_dbi_445_year).sequence,
1351 g_dbi_445_year(g_num_dbi_445_year).name,
1352 g_dbi_445_year(g_num_dbi_445_year).start_date,
1353 g_dbi_445_year(g_num_dbi_445_year).end_date;
1354 exit when c3%notfound;
1355 g_num_dbi_445_year:=g_num_dbi_445_year+1;
1356 end loop;
1357 close c3;
1358 g_num_dbi_445_year:=g_num_dbi_445_year-1;
1359 if g_debug then
1360 write_to_log_file_n('g_num_dbi_445_year='||g_num_dbi_445_year);
1361 end if;
1362 ---
1363 Exception when others then
1364 g_status_message:=sqlerrm;
1365 write_to_log_file_n('Error in loadmem_445_inc '||g_status_message||get_time);
1366 raise;
1367 End;
1368
1369 procedure loadmem_greg_full is
1370 /*cursor c1 is select month_id,substr(month_id,1,4),substr(month_id,6),name from FII_TIME_MONTH order by month_id;
1371 cursor c2 is select quarter_id,year_id,substr(quarter_id,5),name from fii_time_qtr order by quarter_id;
1372 cursor c3 is select year_id col1,year_id col2,name from fii_time_year order by year_id;*/
1373 l_stmt varchar2(20000);
1374 TYPE CurTyp IS REF CURSOR;
1375 c1 CurTyp;
1376 c2 CurTyp;
1377 c3 CurTyp;
1378 Begin
1379 ---month
1380 g_num_dbi_greg_period:=1;
1381 g_num_dbi_greg_qtr:=1;
1382 g_num_dbi_greg_year:=1;
1383 l_stmt:='select month_id,substr(month_id,1,4),substr(month_id,6),name, start_date, end_date from FII_TIME_MONTH order by month_id';
1384 if g_debug then
1385 write_to_log_file_n(l_stmt);
1386 end if;
1387 open c1 for l_stmt;
1388 loop
1389 fetch c1 into
1390 g_dbi_greg_period(g_num_dbi_greg_period).month_id,
1391 g_dbi_greg_period(g_num_dbi_greg_period).year_id,
1392 g_dbi_greg_period(g_num_dbi_greg_period).sequence,
1393 g_dbi_greg_period(g_num_dbi_greg_period).name,
1394 g_dbi_greg_period(g_num_dbi_greg_period).start_date,
1395 g_dbi_greg_period(g_num_dbi_greg_period).end_date;
1396 exit when c1%notfound;
1397 g_num_dbi_greg_period:=g_num_dbi_greg_period+1;
1398 end loop;
1399 close c1;
1400 g_num_dbi_greg_period:=g_num_dbi_greg_period-1;
1401 if g_debug then
1402 write_to_log_file_n('g_num_dbi_greg_period='||g_num_dbi_greg_period);
1403 end if;
1404 ---qtr
1405 l_stmt:='select quarter_id,year_id,substr(quarter_id,5),name, start_date, end_date from fii_time_qtr order by quarter_id';
1406 if g_debug then
1407 write_to_log_file_n(l_stmt);
1408 end if;
1409 open c2 for l_stmt;
1410 loop
1411 fetch c2 into
1412 g_dbi_greg_qtr(g_num_dbi_greg_qtr).quarter_id,
1413 g_dbi_greg_qtr(g_num_dbi_greg_qtr).year_id,
1414 g_dbi_greg_qtr(g_num_dbi_greg_qtr).sequence,
1415 g_dbi_greg_qtr(g_num_dbi_greg_qtr).name,
1416 g_dbi_greg_qtr(g_num_dbi_greg_qtr).start_date,
1417 g_dbi_greg_qtr(g_num_dbi_greg_qtr).end_date;
1418 exit when c2%notfound;
1419 g_num_dbi_greg_qtr:=g_num_dbi_greg_qtr+1;
1420 end loop;
1421 close c2;
1422 g_num_dbi_greg_qtr:=g_num_dbi_greg_qtr-1;
1423 if g_debug then
1424 write_to_log_file_n('g_num_dbi_greg_qtr='||g_num_dbi_greg_qtr);
1425 end if;
1426 ---year
1427 l_stmt:='select year_id col1,year_id col2,name, start_date, end_date from fii_time_year order by year_id';
1428 if g_debug then
1429 write_to_log_file_n(l_stmt);
1430 end if;
1431 open c3 for l_stmt;
1432 loop
1433 fetch c3 into
1434 g_dbi_greg_year(g_num_dbi_greg_year).year_id,
1435 g_dbi_greg_year(g_num_dbi_greg_year).sequence,
1436 g_dbi_greg_year(g_num_dbi_greg_year).name,
1437 g_dbi_greg_year(g_num_dbi_greg_year).start_date,
1438 g_dbi_greg_year(g_num_dbi_greg_year).end_date;
1439 exit when c3%notfound;
1440 g_num_dbi_greg_year:=g_num_dbi_greg_year+1;
1441 end loop;
1442 close c3;
1443 g_num_dbi_greg_year:=g_num_dbi_greg_year-1;
1444 if g_debug then
1445 write_to_log_file_n('g_num_dbi_greg_year='||g_num_dbi_greg_year);
1446 end if;
1447 ---
1448 Exception when others then
1449 g_status_message:=sqlerrm;
1450 write_to_log_file_n('Error in loadmem_greg_full '||g_status_message||get_time);
1451 raise;
1452 End;
1453
1454 procedure loadmem_greg_inc is
1455 /*cursor c1 is select month_id,substr(month_id,1,4),substr(month_id,6),name from FII_TIME_MONTH
1456 where month_id not in (select to_number(time_fk) from bsc_sys_periods where periodicity_id=g_greg_period_per_id)
1457 order by month_id;
1458 cursor c2 is select quarter_id,year_id,substr(quarter_id,5),name from fii_time_qtr
1459 where quarter_id not in (select to_number(time_fk) from bsc_sys_periods where periodicity_id=g_greg_qtr_per_id)
1460 order by quarter_id;
1461 cursor c3 is select year_id col1,year_id col2,name from fii_time_year
1462 where year_id not in (select to_number(time_fk) from bsc_sys_periods where periodicity_id=g_greg_year_per_id)
1463 order by year_id;*/
1464 l_stmt varchar2(20000);
1465 TYPE CurTyp IS REF CURSOR;
1466 c1 CurTyp;
1467 c2 CurTyp;
1468 c3 CurTyp;
1469 Begin
1470 ---month
1471 g_num_dbi_greg_period:=1;
1472 g_num_dbi_greg_qtr:=1;
1473 g_num_dbi_greg_year:=1;
1474 l_stmt:='select month_id,substr(month_id,1,4),substr(month_id,6),name, start_date, end_date from FII_TIME_MONTH
1475 where month_id not in (select to_number(time_fk) from bsc_sys_periods where periodicity_id=:1)
1476 order by month_id';
1477 if g_debug then
1478 write_to_log_file_n(l_stmt||' '||g_greg_period_per_id);
1479 end if;
1480 open c1 for l_stmt using g_greg_period_per_id;
1481 loop
1482 fetch c1 into
1483 g_dbi_greg_period(g_num_dbi_greg_period).month_id,
1484 g_dbi_greg_period(g_num_dbi_greg_period).year_id,
1485 g_dbi_greg_period(g_num_dbi_greg_period).sequence,
1486 g_dbi_greg_period(g_num_dbi_greg_period).name,
1487 g_dbi_greg_period(g_num_dbi_greg_period).start_date,
1488 g_dbi_greg_period(g_num_dbi_greg_period).end_date;
1489 exit when c1%notfound;
1490 g_num_dbi_greg_period:=g_num_dbi_greg_period+1;
1491 end loop;
1492 close c1;
1493 g_num_dbi_greg_period:=g_num_dbi_greg_period-1;
1494 if g_debug then
1495 write_to_log_file_n('g_num_dbi_greg_period='||g_num_dbi_greg_period);
1496 end if;
1497 ---qtr
1498 l_stmt:='select quarter_id,year_id,substr(quarter_id,5),name, start_date, end_date from fii_time_qtr
1499 where quarter_id not in (select to_number(time_fk) from bsc_sys_periods where periodicity_id=:1)
1500 order by quarter_id';
1501 if g_debug then
1502 write_to_log_file_n(l_stmt||' '||g_greg_qtr_per_id);
1503 end if;
1504 open c2 for l_stmt using g_greg_qtr_per_id;
1505 loop
1506 fetch c2 into
1507 g_dbi_greg_qtr(g_num_dbi_greg_qtr).quarter_id,
1508 g_dbi_greg_qtr(g_num_dbi_greg_qtr).year_id,
1509 g_dbi_greg_qtr(g_num_dbi_greg_qtr).sequence,
1510 g_dbi_greg_qtr(g_num_dbi_greg_qtr).name,
1511 g_dbi_greg_qtr(g_num_dbi_greg_qtr).start_date,
1512 g_dbi_greg_qtr(g_num_dbi_greg_qtr).end_date;
1513 exit when c2%notfound;
1514 g_num_dbi_greg_qtr:=g_num_dbi_greg_qtr+1;
1515 end loop;
1516 close c2;
1517 g_num_dbi_greg_qtr:=g_num_dbi_greg_qtr-1;
1518 if g_debug then
1519 write_to_log_file_n('g_num_dbi_greg_qtr='||g_num_dbi_greg_qtr);
1520 end if;
1521 ---year
1522 l_stmt:='select year_id col1,year_id col2,name, start_date, end_date from fii_time_year
1523 where year_id not in (select to_number(time_fk) from bsc_sys_periods where periodicity_id=:1)
1524 order by year_id';
1525 if g_debug then
1526 write_to_log_file_n(l_stmt||' '||g_greg_year_per_id);
1527 end if;
1528 open c3 for l_stmt using g_greg_year_per_id;
1529 loop
1530 fetch c3 into
1531 g_dbi_greg_year(g_num_dbi_greg_year).year_id,
1532 g_dbi_greg_year(g_num_dbi_greg_year).sequence,
1533 g_dbi_greg_year(g_num_dbi_greg_year).name,
1534 g_dbi_greg_year(g_num_dbi_greg_year).start_date,
1535 g_dbi_greg_year(g_num_dbi_greg_year).end_date;
1536 exit when c3%notfound;
1537 g_num_dbi_greg_year:=g_num_dbi_greg_year+1;
1538 end loop;
1539 close c3;
1540 g_num_dbi_greg_year:=g_num_dbi_greg_year-1;
1541 if g_debug then
1542 write_to_log_file_n('g_num_dbi_greg_year='||g_num_dbi_greg_year);
1543 end if;
1544 ---
1545 Exception when others then
1546 g_status_message:=sqlerrm;
1547 write_to_log_file_n('Error in loadmem_greg_inc '||g_status_message||get_time);
1548 raise;
1549 End;
1550
1551 /*
1552 we need to make week also a part of the ent calendar.
1553 Ent calendar is treated unique
1554 DBi hier is built into this calendar
1555 reporting calendar module will treat any hier with
1556 */
1557 procedure load_dbi_ent_cal is
1558 ----
1559 l_ent_current_year number;
1560 l_ent_start_month number;
1561 l_ent_start_day number;
1562 --
1563 l_year number;
1564 l_qtr number;
1565 l_period number;
1566 ----
1567 l_xtd_pattern varchar2(4000);
1568 ----
1569 --all these translated
1570 l_cal_name varchar2(400);
1571 l_name varchar2(400);
1572 l_source_lang varchar2(100);
1573 ---
1574 Begin
1575 if g_debug then
1576 write_to_log_file_n('In load_dbi_ent_cal'||get_time);
1577 end if;
1578 l_ent_current_year:=to_number(to_char(g_ent_start_date,'YYYY'));
1579 l_ent_start_month:=to_number(to_char(g_ent_start_date,'MM'));
1580 l_ent_start_day:=to_number(to_char(g_ent_start_date,'DD'));
1581 if g_debug then
1582 write_to_log_file_n('l_ent_current_year='||l_ent_current_year||',l_ent_start_month='||
1583 l_ent_start_month||',l_ent_start_day='||l_ent_start_day);
1584 end if;
1585 --cal
1586 --for ENT calendar, the EDW_CALENDAR_TYPE_ID is set to 2.
1587 --for period 445 and greg , this flag is set to 1
1588 insert into bsc_sys_calendars_b(CALENDAR_ID,EDW_FLAG,EDW_CALENDAR_ID,EDW_CALENDAR_TYPE_ID,
1589 FISCAL_YEAR,FISCAL_CHANGE,RANGE_YR_MOD,CURRENT_YEAR,START_MONTH,START_DAY,CREATED_BY,CREATION_DATE,
1590 LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN, short_name) values (
1591 g_ent_cal_id,0,1001,1,g_bsc_greg_fiscal_year,g_ent_fiscal_change,0,l_ent_current_year,
1592 l_ent_start_month,l_ent_start_day,g_who,sysdate,g_who,sysdate,g_who, 'TIME');
1593 --cal TL
1594 l_cal_name:=get_lookup_value('BSC_DBI_CAL_NAME','ENT_CALENDAR_NAME',g_src_lang,l_source_lang);
1595 --FND_MESSAGE.SET_NAME('BSC','BSC_DBI_ENT_CALENDAR_NAME');
1596 --l_cal_name:=FND_MESSAGE.GET;
1597 if g_debug then
1598 write_to_log_file_n('cal name='||l_cal_name);
1599 end if;
1600 for i in 1..g_num_lang loop
1601 l_cal_name:=get_lookup_value('BSC_DBI_CAL_NAME','ENT_CALENDAR_NAME',g_lang(i),l_source_lang); --BugFix#4043934
1602 insert into bsc_sys_calendars_tl(CALENDAR_ID,LANGUAGE,SOURCE_LANG,NAME,HELP,
1603 CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
1604 values(g_ent_cal_id,g_lang(i),l_source_lang,l_cal_name,l_cal_name,g_who,sysdate,g_who,sysdate,g_who);
1605 end loop;
1606 /*
1607 need to fill later
1608 */
1609 --bsc_sys_periodicity
1610 l_xtd_pattern:=','||g_ent_year_per_id||','||g_ent_qtr_per_id||','||g_ent_period_per_id||','||
1611 g_ent_day_per_id||',:225;';
1612 l_xtd_pattern:=l_xtd_pattern||','||g_ent_year_per_id||','||g_ent_qtr_per_id||','||g_ent_period_per_id||','||
1613 g_ent_week_per_id||','||g_ent_day_per_id||',:1143;';
1614 insert into bsc_sys_periodicities(PERIODICITY_ID,NUM_OF_PERIODS,SOURCE,NUM_OF_SUBPERIODS,
1615 PERIOD_COL_NAME,SUBPERIOD_COL_NAME,YEARLY_FLAG,EDW_FLAG,CALENDAR_ID,EDW_PERIODICITY_ID,
1616 CUSTOM_CODE,DB_COLUMN_NAME,PERIODICITY_TYPE,PERIOD_TYPE_ID,RECORD_TYPE_ID,XTD_PATTERN, short_name) values (
1617 g_ent_day_per_id,365,null,0,'PERIOD',null,0,0,g_ent_cal_id,0,1,'DAY365',9,1,1,l_xtd_pattern, 'FII_TIME_DAY');
1618 --FND_MESSAGE.SET_NAME('BSC','BSC_DBI_ENT_DAY_NAME');
1619 l_name:=get_bis_dim_long_name('FII_TIME_DAY',g_src_lang,l_source_lang);
1620 if g_debug then
1621 write_to_log_file_n('periodicity name DBI ent day='||l_name);
1622 end if;
1623 for i in 1..g_num_lang loop
1624 l_name:=get_bis_dim_long_name('FII_TIME_DAY',g_lang(i),l_source_lang);
1625 insert into bsc_sys_periodicities_tl(PERIODICITY_ID,LANGUAGE,SOURCE_LANG,NAME,CREATED_BY,
1626 CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
1627 values(g_ent_day_per_id,g_lang(i),l_source_lang,l_name,g_who,sysdate,g_who,sysdate,g_who);
1628 end loop;
1629 --
1630 --week
1631 l_xtd_pattern:=','||g_ent_year_per_id||','||g_ent_qtr_per_id||','||g_ent_period_per_id||','||
1632 g_ent_day_per_id||',:1;';
1633 l_xtd_pattern:=l_xtd_pattern||','||g_ent_year_per_id||','||g_ent_qtr_per_id||','||g_ent_period_per_id||','||
1634 g_ent_week_per_id||','||g_ent_day_per_id||',:11;';
1635 insert into bsc_sys_periodicities(PERIODICITY_ID,NUM_OF_PERIODS,SOURCE,NUM_OF_SUBPERIODS,
1636 PERIOD_COL_NAME,SUBPERIOD_COL_NAME,YEARLY_FLAG,EDW_FLAG,CALENDAR_ID,EDW_PERIODICITY_ID,
1637 CUSTOM_CODE,DB_COLUMN_NAME,PERIODICITY_TYPE,PERIOD_TYPE_ID,RECORD_TYPE_ID,XTD_PATTERN, short_name) values (
1638 g_ent_week_per_id,52,g_ent_day_per_id,0,'PERIOD',null,0,0,g_ent_cal_id,0,2,'WEEK52',7,16,16,l_xtd_pattern, 'FII_TIME_WEEK');
1639 --FND_MESSAGE.SET_NAME('BSC','BSC_DBI_ENT_WEEK_NAME');
1640 l_name:=get_bis_dim_long_name('FII_TIME_WEEK',g_src_lang,l_source_lang);
1641 if g_debug then
1642 write_to_log_file_n('periodicity name DBI ent week='||l_name);
1643 end if;
1644 for i in 1..g_num_lang loop
1645 l_name:=get_bis_dim_long_name('FII_TIME_WEEK',g_lang(i),l_source_lang);
1646 insert into bsc_sys_periodicities_tl(PERIODICITY_ID,LANGUAGE,SOURCE_LANG,NAME,CREATED_BY,
1647 CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
1648 values(g_ent_week_per_id,g_lang(i),l_source_lang,l_name,g_who,sysdate,g_who,sysdate,g_who);
1649 end loop;
1650 --
1651 --period
1652 l_xtd_pattern:=','||g_ent_year_per_id||','||g_ent_qtr_per_id||','||g_ent_period_per_id||','||
1653 g_ent_day_per_id||',:1;';
1654 l_xtd_pattern:=l_xtd_pattern||','||g_ent_year_per_id||','||g_ent_qtr_per_id||','||g_ent_period_per_id||','||
1655 g_ent_week_per_id||','||g_ent_day_per_id||',:23;';
1656 insert into bsc_sys_periodicities(PERIODICITY_ID,NUM_OF_PERIODS,SOURCE,NUM_OF_SUBPERIODS,
1657 PERIOD_COL_NAME,SUBPERIOD_COL_NAME,YEARLY_FLAG,EDW_FLAG,CALENDAR_ID,EDW_PERIODICITY_ID,
1658 CUSTOM_CODE,DB_COLUMN_NAME,PERIODICITY_TYPE,PERIOD_TYPE_ID,RECORD_TYPE_ID,XTD_PATTERN, short_name) values (
1659 g_ent_period_per_id,12,g_ent_day_per_id,0,'PERIOD',null,0,0,g_ent_cal_id,0,2,'MONTH',5,32,32,l_xtd_pattern, 'FII_TIME_ENT_PERIOD');
1660 --FND_MESSAGE.SET_NAME('BSC','BSC_DBI_ENT_PERIOD_NAME');
1661 l_name:=get_bis_dim_long_name('FII_TIME_ENT_PERIOD',g_src_lang,l_source_lang);
1662 if g_debug then
1663 write_to_log_file_n('periodicity name DBI ent period='||l_name);
1664 end if;
1665 for i in 1..g_num_lang loop
1666 l_name:=get_bis_dim_long_name('FII_TIME_ENT_PERIOD',g_lang(i),l_source_lang);
1667 insert into bsc_sys_periodicities_tl(PERIODICITY_ID,LANGUAGE,SOURCE_LANG,NAME,CREATED_BY,
1668 CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
1669 values(g_ent_period_per_id,g_lang(i),l_source_lang,l_name,g_who,sysdate,g_who,sysdate,g_who);
1670 end loop;
1671 --
1672 l_xtd_pattern:=','||g_ent_year_per_id||','||g_ent_qtr_per_id||','||g_ent_period_per_id||','||
1673 g_ent_day_per_id||',:33;';
1674 l_xtd_pattern:=l_xtd_pattern||','||g_ent_year_per_id||','||g_ent_qtr_per_id||','||g_ent_period_per_id||','||
1675 g_ent_week_per_id||','||g_ent_day_per_id||',:55;';
1676 insert into bsc_sys_periodicities(PERIODICITY_ID,NUM_OF_PERIODS,SOURCE,NUM_OF_SUBPERIODS,
1677 PERIOD_COL_NAME,SUBPERIOD_COL_NAME,YEARLY_FLAG,EDW_FLAG,CALENDAR_ID,EDW_PERIODICITY_ID,
1678 CUSTOM_CODE,DB_COLUMN_NAME,PERIODICITY_TYPE,PERIOD_TYPE_ID,RECORD_TYPE_ID,XTD_PATTERN, short_name) values (
1679 g_ent_qtr_per_id,4,g_ent_period_per_id,0,'PERIOD',null,0,0,g_ent_cal_id,0,2,'QUARTER',3,64,64,l_xtd_pattern, 'FII_TIME_ENT_QTR');
1680 --FND_MESSAGE.SET_NAME('BSC','BSC_DBI_ENT_QTR_NAME');
1681 l_name:=get_bis_dim_long_name('FII_TIME_ENT_QTR',g_src_lang,l_source_lang);
1682 if g_debug then
1683 write_to_log_file_n('periodicity name DBI ent qtr='||l_name);
1684 end if;
1685 for i in 1..g_num_lang loop
1686 l_name:=get_bis_dim_long_name('FII_TIME_ENT_QTR',g_lang(i),l_source_lang);
1687 insert into bsc_sys_periodicities_tl(PERIODICITY_ID,LANGUAGE,SOURCE_LANG,NAME,CREATED_BY,
1688 CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
1689 values(g_ent_qtr_per_id,g_lang(i),l_source_lang,l_name,g_who,sysdate,g_who,sysdate,g_who);
1690 end loop;
1691 --
1692 l_xtd_pattern:=','||g_ent_year_per_id||','||g_ent_qtr_per_id||','||g_ent_period_per_id||','||
1693 g_ent_day_per_id||',:97;';
1694 l_xtd_pattern:=l_xtd_pattern||','||g_ent_year_per_id||','||g_ent_qtr_per_id||','||g_ent_period_per_id||','||
1695 g_ent_week_per_id||','||g_ent_day_per_id||',:119;';
1696 insert into bsc_sys_periodicities(PERIODICITY_ID,NUM_OF_PERIODS,SOURCE,NUM_OF_SUBPERIODS,
1697 PERIOD_COL_NAME,SUBPERIOD_COL_NAME,YEARLY_FLAG,EDW_FLAG,CALENDAR_ID,EDW_PERIODICITY_ID,
1698 CUSTOM_CODE,DB_COLUMN_NAME,PERIODICITY_TYPE,PERIOD_TYPE_ID,RECORD_TYPE_ID,XTD_PATTERN, short_name) values (
1699 g_ent_year_per_id,1,g_ent_qtr_per_id,0,'PERIOD',null,1,0,g_ent_cal_id,0,2,'YEAR',1,128,128,l_xtd_pattern, 'FII_TIME_ENT_YEAR');
1700 --FND_MESSAGE.SET_NAME('BSC','BSC_DBI_ENT_YEAR_NAME');
1701 l_name:=get_bis_dim_long_name('FII_TIME_ENT_YEAR',g_src_lang,l_source_lang);
1702 if g_debug then
1703 write_to_log_file_n('periodicity name DBI ent year='||l_name);
1704 end if;
1705 for i in 1..g_num_lang loop
1706 l_name:=get_bis_dim_long_name('FII_TIME_ENT_YEAR',g_lang(i),l_source_lang);
1707 insert into bsc_sys_periodicities_tl(PERIODICITY_ID,LANGUAGE,SOURCE_LANG,NAME,CREATED_BY,
1708 CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
1709 values(g_ent_year_per_id,g_lang(i),l_source_lang,l_name,g_who,sysdate,g_who,sysdate,g_who);
1710 end loop;
1711 --
1712 Exception when others then
1713 g_status_message:=sqlerrm;
1714 write_to_log_file_n('Error in load_dbi_ent_cal '||g_status_message||get_time);
1715 raise;
1716 End;
1717
1718 procedure load_dbi_ent_cal_data is
1719 l_prev_qtr number;
1720 l_prev_period number;
1721 --
1722 l_days_in_year number;
1723 l_year number;
1724 l_qtr number;
1725 l_period number;
1726 l_day number;
1727 l_week number;
1728 --l_week_past_52 boolean;
1729 --l_week_year_change boolean;
1730 --
1731 Begin
1732 --bsc_sys_periods
1733 --ent day info
1734 --l_day:=get_day365(g_ent_start_date,g_dbi_cal_record(1).report_date)-1; ---1 because we inc day before insert
1735 --if l_day is null then
1736 --raise g_exception;
1737 --end if;
1738 --populate bsc_sys_periods and bsc_db_calendar for DAY
1739 l_prev_qtr:=0;
1740 l_prev_period:=0;
1741 --3990678 the fix for this bug has made l_week_past_52 and l_week_year_change obsolete
1742 --l_week_past_52:=false;
1743 --l_week_year_change:=false;
1744 for i in 1..g_num_dbi_cal_record loop
1745 l_year:=substr(g_dbi_cal_record(i).ent_period_id,1,4);
1746 l_qtr:=substr(g_dbi_cal_record(i).ent_period_id,5,1);
1747 l_period:=substr(g_dbi_cal_record(i).ent_period_id,6);
1748 --l_week:=substr(g_dbi_cal_record(i).week_id,7);
1749 --3990678
1750 l_week:=g_dbi_cal_record(i).ent_week_id;
1751 l_day:=g_dbi_cal_record(i).ent_day;
1752 --if l_week=52 then
1753 --l_week_past_52:=true;
1754 --end if;
1755 --if l_prev_period<>0 and l_prev_qtr<>0 and
1756 --l_period=1 and l_period<>l_prev_period and l_qtr=1 and l_qtr<>l_prev_qtr then
1757 --l_day:=1;
1758 --l_week_past_52:=false;
1759 --l_week_year_change:=true;
1760 --else
1761 --l_day:=l_day+1;
1762 --end if;
1763 --correct_ent_week has obsoleted the need for l_week_year_change and l_week_past_52
1764 --if l_week_past_52 and l_week=1 then
1765 --l_week:=53;
1766 --end if;
1767 --if l_week_year_change and l_week<52 then
1768 --l_week_year_change:=false;
1769 --end if;
1770 --if l_week_year_change and l_week>=52 then
1771 --l_week:=1;
1772 --end if;
1773 --insert into bsc_sys_periods
1774 --time_fk is in MM/DD/YYYY format
1775 insert into bsc_sys_periods(PERIODICITY_ID,YEAR,PERIOD_ID,START_DATE,END_DATE,START_PERIOD,
1776 END_PERIOD,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,TIME_FK)
1777 values(g_ent_day_per_id,l_year,l_day, g_dbi_cal_record(i).start_date, g_dbi_cal_record(i).end_date, null,null,g_who,sysdate,g_who,sysdate,g_who,
1778 g_dbi_cal_record(i).cal_month||'/'||g_dbi_cal_record(i).cal_day||'/'||g_dbi_cal_record(i).cal_year);
1779 for j in 1..g_num_lang loop
1780 insert into bsc_sys_periods_tl(YEAR,PERIODICITY_ID,PERIOD_ID,MONTH,LANGUAGE,SOURCE_LANG,NAME,SHORT_NAME)
1781 values(l_year,g_ent_day_per_id,l_day,1,g_lang(j),g_src_lang,g_dbi_cal_record(i).report_date,null);
1782 end loop;
1783 --insert into bsc_db_calendar
1784 g_db_cal_modified:=true;
1785 insert into bsc_db_calendar(CALENDAR_YEAR,CALENDAR_MONTH,CALENDAR_DAY,YEAR,SEMESTER,
1786 QUARTER,BIMESTER,MONTH,WEEK52,WEEK4,DAY365,DAY30,HOLYDAY_FLAG,WORKDAY_FLAG,CALENDAR_ID)
1787 values(g_dbi_cal_record(i).cal_year,g_dbi_cal_record(i).cal_month,g_dbi_cal_record(i).cal_day,
1788 l_year,0,l_qtr,0,l_period,l_week,0,l_day,0,null,null,g_ent_cal_id);
1789 --CUSTOM_1,CUSTOM_2,CUSTOM_3,CUSTOM_4)
1790 --l_day,l_week,l_period,l_qtr);
1791 ----
1792 l_prev_qtr:=l_qtr;
1793 l_prev_period:=l_period;
1794 end loop;
1795 --
1796 --populate bsc_sys_periods for WEEK. for week, we dont populate bsc_db_calendar
1797 for i in 1..g_num_dbi_445_week loop
1798 --3990678
1799 insert into bsc_sys_periods(PERIODICITY_ID,YEAR,PERIOD_ID,START_DATE,END_DATE,START_PERIOD,
1800 END_PERIOD,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,TIME_FK)
1801 values(g_ent_week_per_id,g_dbi_445_week(i).ent_year_id,g_dbi_445_week(i).ent_week_id, g_dbi_445_week(i).start_date, g_dbi_445_week(i).end_date, null,null,
1802 g_who,sysdate,g_who,sysdate,g_who,g_dbi_445_week(i).week_id);
1803 --values(g_ent_week_per_id,g_dbi_445_week(i).year_id,g_dbi_445_week(i).sequence,null,null,null,null,
1804 --g_who,sysdate,g_who,sysdate,g_who,g_dbi_445_week(i).week_id);
1805 for j in 1..g_num_lang loop
1806 insert into bsc_sys_periods_tl(YEAR,PERIODICITY_ID,PERIOD_ID,MONTH,LANGUAGE,SOURCE_LANG,NAME,SHORT_NAME)
1807 values(g_dbi_445_week(i).ent_year_id,g_ent_week_per_id,g_dbi_445_week(i).ent_week_id,1,g_lang(j),
1808 g_src_lang,g_dbi_445_week(i).name,null);
1809 end loop;
1810 end loop;
1811 --3990678
1812 --insert the extra weeks
1813 if g_num_ent_week is not null and g_num_ent_week>0 then
1814 for i in 1..g_num_ent_week loop
1815 insert into bsc_sys_periods(PERIODICITY_ID,YEAR,PERIOD_ID,START_DATE,END_DATE,START_PERIOD,
1816 END_PERIOD,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,TIME_FK)
1817 values(g_ent_week_per_id,g_ent_week(i).ent_year_id,g_ent_week(i).ent_week_id, g_ent_week(i).start_date, g_ent_week(i).end_date, null,null,
1818 g_who,sysdate,g_who,sysdate,g_who,g_ent_week(i).week_id);
1819 for j in 1..g_num_lang loop
1820 insert into bsc_sys_periods_tl(YEAR,PERIODICITY_ID,PERIOD_ID,MONTH,LANGUAGE,SOURCE_LANG,NAME,SHORT_NAME)
1821 values(g_ent_week(i).ent_year_id,g_ent_week_per_id,g_ent_week(i).ent_week_id,1,g_lang(j),
1822 g_src_lang,g_ent_week(i).name,null);
1823 end loop;
1824 end loop;
1825 end if;
1826 --
1827 --populate bsc_sys_periods for PERIOD. for period, we dont populate bsc_db_calendar
1828 --cursor c1 is select ent_period_id,ent_year_id,sequence from FII_TIME_ENT_PERIOD order by ent_period_id;
1829 for i in 1..g_num_dbi_ent_period loop
1830 insert into bsc_sys_periods(PERIODICITY_ID,YEAR,PERIOD_ID,START_DATE,END_DATE,START_PERIOD,
1831 END_PERIOD,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,TIME_FK)
1832 values(g_ent_period_per_id,g_dbi_ent_period(i).ent_year_id,g_dbi_ent_period(i).sequence,
1833 g_dbi_ent_period(i).start_date, g_dbi_ent_period(i).end_date,null,null,
1834 g_who,sysdate,g_who,sysdate,g_who,g_dbi_ent_period(i).ent_period_id);
1835 for j in 1..g_num_lang loop
1836 insert into bsc_sys_periods_tl(YEAR,PERIODICITY_ID,PERIOD_ID,MONTH,LANGUAGE,SOURCE_LANG,NAME,SHORT_NAME)
1837 values(g_dbi_ent_period(i).ent_year_id,g_ent_period_per_id,g_dbi_ent_period(i).sequence,1,
1838 g_lang(j),g_src_lang,g_dbi_ent_period(i).name,null);
1839 end loop;
1840 end loop;
1841 --populate bsc_sys_periods for QTR. for qtr, we dont populate bsc_db_calendar
1842 --cursor c2 is select ent_qtr_id,ent_year_id,sequence from FII_TIME_ENT_QTR order by ent_qtr_id;
1843 for i in 1..g_num_dbi_ent_qtr loop
1844 insert into bsc_sys_periods(PERIODICITY_ID,YEAR,PERIOD_ID,START_DATE,END_DATE,START_PERIOD,
1845 END_PERIOD,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,TIME_FK)
1846 values(g_ent_qtr_per_id,g_dbi_ent_qtr(i).ent_year_id,g_dbi_ent_qtr(i).sequence, g_dbi_ent_qtr(i).start_date, g_dbi_ent_qtr(i).end_date, null,null,
1847 g_who,sysdate,g_who,sysdate,g_who,g_dbi_ent_qtr(i).ent_qtr_id);
1848 for j in 1..g_num_lang loop
1849 insert into bsc_sys_periods_tl(YEAR,PERIODICITY_ID,PERIOD_ID,MONTH,LANGUAGE,SOURCE_LANG,NAME,SHORT_NAME)
1850 values(g_dbi_ent_qtr(i).ent_year_id,g_ent_qtr_per_id,g_dbi_ent_qtr(i).sequence,1,
1851 g_lang(j),g_src_lang,g_dbi_ent_qtr(i).name,null);
1852 end loop;
1853 end loop;
1854 --populate bsc_sys_periods for YEAR. for year, we dont populate bsc_db_calendar
1855 --cursor c3 is select ent_year_id,sequence from FII_TIME_ENT_YEAR order by ent_year_id;
1856 for i in 1..g_num_dbi_ent_year loop
1857 insert into bsc_sys_periods(PERIODICITY_ID,YEAR,PERIOD_ID,START_DATE,END_DATE,START_PERIOD,
1858 END_PERIOD,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,TIME_FK)
1859 values(g_ent_year_per_id,g_dbi_ent_year(i).sequence,g_dbi_ent_year(i).sequence, g_dbi_ent_year(i).start_date, g_dbi_ent_year(i).end_date, null,null,
1860 g_who,sysdate,g_who,sysdate,g_who,g_dbi_ent_year(i).ent_year_id);
1861 for j in 1..g_num_lang loop
1862 insert into bsc_sys_periods_tl(YEAR,PERIODICITY_ID,PERIOD_ID,MONTH,LANGUAGE,SOURCE_LANG,NAME,SHORT_NAME)
1863 values(g_dbi_ent_year(i).sequence,g_ent_year_per_id,g_dbi_ent_year(i).sequence,1,
1864 g_lang(j),g_src_lang,g_dbi_ent_year(i).name,null);
1865 end loop;
1866 end loop;
1867 Exception when others then
1868 g_status_message:=sqlerrm;
1869 write_to_log_file_n('Error in load_dbi_ent_cal_data '||g_status_message||get_time);
1870 raise;
1871 End;
1872
1873 procedure load_dbi_445_cal is
1874 ----
1875 l_445_current_year number;
1876 l_445_start_month number;
1877 l_445_start_day number;
1878 ----
1879 l_year number;
1880 l_p445 number;
1881 l_week number;
1882 --
1883 l_xtd_pattern varchar2(4000);
1884 ----
1885 l_cal_name varchar2(400);
1886 l_name varchar2(400);
1887 l_source_lang varchar2(100);
1888 ---
1889 x_Return_Status varchar2(1000);
1890 x_msg_count number;
1891 x_msg_data varchar2(1000);
1892 Begin
1893 if g_debug then
1894 write_to_log_file_n('In load_dbi_445_cal'||get_time);
1895 end if;
1896 --Enh 4530872
1897 if (g_445_cal_short_name is null) then
1898 g_445_cal_short_name := generate_short_name;
1899 if create_dim(g_445_cal_short_name, x_msg_data) then
1900 null;
1901 else
1902 g_status_message := 'Unable to create TIME_445 dimension:'||g_445_cal_short_name||':'||x_msg_data;
1903 raise g_exception;
1904 end if;
1905 end if;
1906 -- Get short name of 445 day
1907 if (g_445_day_short_name is null) then
1908 g_445_day_short_name := generate_short_name;
1909 if create_dim_obj(g_445_day_short_name, g_445_cal_short_name, 'FII_TIME_DAY_V', x_msg_data) then
1910 null;
1911 else
1912 g_status_message := 'Unable to create TIME_DAY_445 level:'||g_445_day_short_name||':'||x_msg_data;
1913 raise g_exception;
1914 end if;
1915 end if;
1916
1917 if (g_445_week_short_name is null) then
1918 g_445_week_short_name := generate_short_name;
1919 if create_dim_obj(g_445_week_short_name, g_445_cal_short_name, 'FII_TIME_WEEK_V', x_msg_data) then
1920 null;
1921 else
1922 g_status_message := 'Unable to create TIME_WEEK445 level:'||g_445_week_short_name||':'||x_msg_data;
1923 raise g_exception;
1924 end if;
1925 end if;
1926
1927 if (g_445_p445_short_name is null) then
1928 g_445_p445_short_name := generate_short_name;
1929 if create_dim_obj( g_445_p445_short_name , g_445_cal_short_name, 'FII_TIME_P445_V', x_msg_data) then
1930 null;
1931 else
1932 g_status_message := 'Unable to create TIME_P445 level:'||g_445_cal_short_name||':'||x_msg_data;
1933 raise g_exception;
1934 end if;
1935 end if;
1936
1937 if (g_445_year_short_name is null) then
1938 g_445_year_short_name := generate_short_name;
1939 if create_dim_obj(g_445_year_short_name, g_445_cal_short_name, 'FII_TIME_YEAR445_V', x_msg_data) then
1940 null;
1941 else
1942 g_status_message := 'Unable to create TIME_YEAR445 level:'||g_445_year_short_name||':'||x_msg_data;
1943 raise g_exception;
1944 end if;
1945 end if;
1946
1947 l_445_current_year:=to_number(to_char(g_445_start_date,'YYYY'));
1948 l_445_start_month:=to_number(to_char(g_445_start_date,'MM'));
1949 l_445_start_day:=to_number(to_char(g_445_start_date,'DD'));
1950 if g_debug then
1951 write_to_log_file_n('l_445_current_year='||l_445_current_year||',l_445_start_month='||
1952 l_445_start_month||',l_445_start_day='||l_445_start_day);
1953 end if;
1954
1955 /* CREATE 445 CALENDAR */
1956
1957 insert into bsc_sys_calendars_b(CALENDAR_ID,EDW_FLAG,EDW_CALENDAR_ID,EDW_CALENDAR_TYPE_ID,
1958 FISCAL_YEAR,FISCAL_CHANGE,RANGE_YR_MOD,CURRENT_YEAR,START_MONTH,START_DAY,CREATED_BY,CREATION_DATE,
1959 LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN, SHORT_NAME) values (
1960 g_445_cal_id,0,1002,1,g_bsc_greg_fiscal_year,g_445_fiscal_change,0,l_445_current_year,
1961 l_445_start_month,l_445_start_day,g_who,sysdate,g_who,sysdate,g_who, g_445_cal_short_name);
1962 --cal TL
1963 l_cal_name:=get_lookup_value('BSC_DBI_CAL_NAME','P445_CALENDAR_NAME',g_src_lang,l_source_lang);
1964 --FND_MESSAGE.SET_NAME('BSC','BSC_DBI_445_CALENDAR_NAME');
1965 --l_cal_name:=FND_MESSAGE.GET;
1966 if g_debug then
1967 write_to_log_file_n('cal name='||l_cal_name);
1968 end if;
1969 for i in 1..g_num_lang loop
1970 l_cal_name:=get_lookup_value('BSC_DBI_CAL_NAME','P445_CALENDAR_NAME',g_lang(i),l_source_lang);
1971 insert into bsc_sys_calendars_tl(CALENDAR_ID,LANGUAGE,SOURCE_LANG,NAME,HELP,
1972 CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
1973 values(g_445_cal_id,g_lang(i),l_source_lang,l_cal_name,l_cal_name,g_who,sysdate,g_who,sysdate,g_who);
1974 end loop;
1975 /*
1976 need to fill later
1977 */
1978 --bsc_sys_periodicity
1979 l_xtd_pattern:=','||g_445_year_per_id||','||g_445_p445_per_id||','||g_445_week_per_id||','||
1980 g_445_day_per_id||',:177;';
1981
1982 /* INSERT 445 DAY PERIODICITY */
1983
1984 insert into bsc_sys_periodicities(PERIODICITY_ID,NUM_OF_PERIODS,SOURCE,NUM_OF_SUBPERIODS,
1985 PERIOD_COL_NAME,SUBPERIOD_COL_NAME,YEARLY_FLAG,EDW_FLAG,CALENDAR_ID,EDW_PERIODICITY_ID,
1986 CUSTOM_CODE,DB_COLUMN_NAME,PERIODICITY_TYPE,PERIOD_TYPE_ID,RECORD_TYPE_ID,XTD_PATTERN, short_name) values (
1987 g_445_day_per_id,365,null,0,'PERIOD',null,0,0,g_445_cal_id,0,1,'CUSTOM_1',9,1,1,l_xtd_pattern, g_445_day_short_name);
1988 --FND_MESSAGE.SET_NAME('BSC','BSC_DBI_445_DAY_NAME');
1989 l_name:=get_bis_dim_long_name('FII_TIME_DAY',g_src_lang,l_source_lang);
1990 if g_debug then
1991 write_to_log_file_n('periodicity name DBI 445 day='||l_name);
1992 end if;
1993 for i in 1..g_num_lang loop
1994 l_name:=get_bis_dim_long_name('FII_TIME_DAY',g_lang(i),l_source_lang);
1995 insert into bsc_sys_periodicities_tl(PERIODICITY_ID,LANGUAGE,SOURCE_LANG,NAME,CREATED_BY,
1996 CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
1997 values(g_445_day_per_id,g_lang(i),l_source_lang,l_name,g_who,sysdate,g_who,sysdate,g_who);
1998 end loop;
1999 --
2000 l_xtd_pattern:=','||g_445_year_per_id||','||g_445_p445_per_id||','||g_445_week_per_id||','||
2001 g_445_day_per_id||',:1;';
2002
2003 insert into bsc_sys_periodicities(PERIODICITY_ID,NUM_OF_PERIODS,SOURCE,NUM_OF_SUBPERIODS,
2004 PERIOD_COL_NAME,SUBPERIOD_COL_NAME,YEARLY_FLAG,EDW_FLAG,CALENDAR_ID,EDW_PERIODICITY_ID,
2005 CUSTOM_CODE,DB_COLUMN_NAME,PERIODICITY_TYPE,PERIOD_TYPE_ID,RECORD_TYPE_ID,XTD_PATTERN, short_name) values (
2006 g_445_week_per_id,52,g_445_day_per_id,0,'PERIOD',null,0,0,g_445_cal_id,0,2,'CUSTOM_2',0,16,16,l_xtd_pattern, g_445_week_short_name);
2007 --FND_MESSAGE.SET_NAME('BSC','BSC_DBI_445_WEEK_NAME');
2008 l_name:=get_bis_dim_long_name('FII_TIME_WEEK',g_src_lang,l_source_lang);
2009 if g_debug then
2010 write_to_log_file_n('periodicity name DBI 445 week='||l_name);
2011 end if;
2012 for i in 1..g_num_lang loop
2013 l_name:=get_bis_dim_long_name('FII_TIME_WEEK',g_lang(i),l_source_lang);
2014 insert into bsc_sys_periodicities_tl(PERIODICITY_ID,LANGUAGE,SOURCE_LANG,NAME,CREATED_BY,
2015 CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
2016 values(g_445_week_per_id,g_lang(i),l_source_lang,l_name,g_who,sysdate,g_who,sysdate,g_who);
2017 end loop;
2018 --
2019 l_xtd_pattern:=','||g_445_year_per_id||','||g_445_p445_per_id||','||g_445_week_per_id||','||
2020 g_445_day_per_id||',:17;';
2021
2022 insert into bsc_sys_periodicities(PERIODICITY_ID,NUM_OF_PERIODS,SOURCE,NUM_OF_SUBPERIODS,
2023 PERIOD_COL_NAME,SUBPERIOD_COL_NAME,YEARLY_FLAG,EDW_FLAG,CALENDAR_ID,EDW_PERIODICITY_ID,
2024 CUSTOM_CODE,DB_COLUMN_NAME,PERIODICITY_TYPE,PERIOD_TYPE_ID,RECORD_TYPE_ID,XTD_PATTERN, short_name) values (
2025 g_445_p445_per_id,12,g_445_week_per_id,0,'PERIOD',null,0,0,g_445_cal_id,0,2,'CUSTOM_3',0,32,32,l_xtd_pattern, g_445_p445_short_name);
2026
2027 l_name:=get_bis_dim_long_name('FII_TIME_P445',g_src_lang,l_source_lang);
2028 if g_debug then
2029 write_to_log_file_n('periodicity name DBI 445 p445='||l_name);
2030 end if;
2031 for i in 1..g_num_lang loop
2032 l_name:=get_bis_dim_long_name('FII_TIME_P445',g_lang(i),l_source_lang);
2033 insert into bsc_sys_periodicities_tl(PERIODICITY_ID,LANGUAGE,SOURCE_LANG,NAME,CREATED_BY,
2034 CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
2035 values(g_445_p445_per_id,g_lang(i),l_source_lang,l_name,g_who,sysdate,g_who,sysdate,g_who);
2036 end loop;
2037 --
2038 l_xtd_pattern:=','||g_445_year_per_id||','||g_445_p445_per_id||','||g_445_week_per_id||','||
2039 g_445_day_per_id||',:49;';
2040
2041 insert into bsc_sys_periodicities(PERIODICITY_ID,NUM_OF_PERIODS,SOURCE,NUM_OF_SUBPERIODS,
2042 PERIOD_COL_NAME,SUBPERIOD_COL_NAME,YEARLY_FLAG,EDW_FLAG,CALENDAR_ID,EDW_PERIODICITY_ID,
2043 CUSTOM_CODE,DB_COLUMN_NAME,PERIODICITY_TYPE,PERIOD_TYPE_ID,RECORD_TYPE_ID,XTD_PATTERN, short_name) values (
2044 g_445_year_per_id,1,g_445_p445_per_id,0,'PERIOD',null,1,0,g_445_cal_id,0,2,'YEAR',1,128,128,l_xtd_pattern, g_445_year_short_name);
2045 l_name:=get_bis_dim_long_name('FII_TIME_YEAR445',g_src_lang,l_source_lang);
2046 if g_debug then
2047 write_to_log_file_n('periodicity name DBI 445 year='||l_name);
2048 end if;
2049 for i in 1..g_num_lang loop
2050 l_name:=get_bis_dim_long_name('FII_TIME_YEAR445',g_lang(i),l_source_lang);
2051 insert into bsc_sys_periodicities_tl(PERIODICITY_ID,LANGUAGE,SOURCE_LANG,NAME,CREATED_BY,
2052 CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
2053 values(g_445_year_per_id,g_lang(i),l_source_lang,l_name,g_who,sysdate,g_who,sysdate,g_who);
2054 end loop;
2055 -----
2056
2057
2058 Exception when others then
2059 g_status_message:=g_status_message||' '||sqlerrm;
2060 write_to_log_file_n('Error in load_dbi_445_cal '||g_status_message||get_time);
2061 raise;
2062 End;
2063
2064 procedure load_dbi_445_cal_data is
2065 --
2066 l_prev_p445 number;
2067 l_prev_week number;
2068 l_days_in_year number;
2069 l_year number;
2070 l_p445 number;
2071 l_week number;
2072 l_day number;
2073 --
2074 Begin
2075 --l_day:=get_day365(g_445_start_date,g_dbi_cal_record(1).report_date)-1;
2076 --if l_day is null then
2077 --raise g_exception;
2078 --end if;
2079 --populate bsc_sys_periods and bsc_db_calendar for DAY
2080 l_prev_p445:=0;
2081 l_prev_week:=0;
2082 for i in 1..g_num_dbi_cal_record loop
2083 l_year:=substr(g_dbi_cal_record(i).week_id,1,4);
2084 l_p445:=substr(g_dbi_cal_record(i).week_id,5,2);
2085 l_week:=substr(g_dbi_cal_record(i).week_id,7);
2086 l_day:=g_dbi_cal_record(i).p445_day;
2087 --if l_prev_week<>0 and l_prev_p445<>0 and
2088 --l_week=1 and l_week<>l_prev_week and l_p445=1 and l_p445<>l_prev_p445 then
2089 --l_day:=1;
2090 --else
2091 --l_day:=l_day+1;
2092 --end if;
2093 --insert into bsc_sys_periods
2094 --time_fk is in MM/DD/YYYY format
2095 insert into bsc_sys_periods(PERIODICITY_ID,YEAR,PERIOD_ID,START_DATE,END_DATE,START_PERIOD,
2096 END_PERIOD,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,TIME_FK)
2097 values(g_445_day_per_id,l_year,l_day, g_dbi_cal_record(i).start_date, g_dbi_cal_record(i).end_date, null,null,g_who,sysdate,g_who,sysdate,g_who,
2098 g_dbi_cal_record(i).cal_month||'/'||g_dbi_cal_record(i).cal_day||'/'||g_dbi_cal_record(i).cal_year);
2099 for j in 1..g_num_lang loop
2100 insert into bsc_sys_periods_tl(YEAR,PERIODICITY_ID,PERIOD_ID,MONTH,LANGUAGE,SOURCE_LANG,NAME,SHORT_NAME)
2101 values(l_year,g_445_day_per_id,l_day,1,g_lang(j),g_src_lang,g_dbi_cal_record(i).report_date,null);
2102 end loop;
2103 g_db_cal_modified:=true;
2104 --insert into bsc_db_calendar
2105 insert into bsc_db_calendar(CALENDAR_YEAR,CALENDAR_MONTH,CALENDAR_DAY,YEAR,SEMESTER,
2106 QUARTER,BIMESTER,MONTH,WEEK52,WEEK4,DAY365,DAY30,HOLYDAY_FLAG,WORKDAY_FLAG,CALENDAR_ID,
2107 CUSTOM_1,CUSTOM_2,CUSTOM_3)
2108 values(g_dbi_cal_record(i).cal_year,g_dbi_cal_record(i).cal_month,g_dbi_cal_record(i).cal_day,
2109 l_year,0,0,0,0,0,0,0,0,null,null,g_445_cal_id,l_day,l_week,l_p445);
2110 ----
2111 l_prev_p445:=l_p445;
2112 l_prev_week:=l_week;
2113 end loop;
2114 --populate bsc_sys_periods for PERIOD. for period, we dont populate bsc_db_calendar
2115 --cursor c1 is select week_id,substr(week_id,1,4),sequence from FII_TIME_WEEK order by week_id;
2116 for i in 1..g_num_dbi_445_week loop
2117 insert into bsc_sys_periods(PERIODICITY_ID,YEAR,PERIOD_ID,START_DATE,END_DATE,START_PERIOD,
2118 END_PERIOD,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,TIME_FK)
2119 values(g_445_week_per_id,g_dbi_445_week(i).year_id,g_dbi_445_week(i).sequence, g_dbi_445_week(i).start_date, g_dbi_445_week(i).end_date, null,null,
2120 g_who,sysdate,g_who,sysdate,g_who,g_dbi_445_week(i).week_id);
2121 for j in 1..g_num_lang loop
2122 insert into bsc_sys_periods_tl(YEAR,PERIODICITY_ID,PERIOD_ID,MONTH,LANGUAGE,SOURCE_LANG,NAME,SHORT_NAME)
2123 values(g_dbi_445_week(i).year_id,g_445_week_per_id,g_dbi_445_week(i).sequence,1,g_lang(j),g_src_lang,
2124 g_dbi_445_week(i).name,null);
2125 end loop;
2126 end loop;
2127 --populate bsc_sys_periods for QTR. for qtr, we dont populate bsc_db_calendar
2128 --cursor c2 is select period445_id,year445_id,sequence from fii_time_p445 order by period445_id;
2129 for i in 1..g_num_dbi_445_p445 loop
2130 insert into bsc_sys_periods(PERIODICITY_ID,YEAR,PERIOD_ID,START_DATE,END_DATE,START_PERIOD,
2131 END_PERIOD,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,TIME_FK)
2132 values(g_445_p445_per_id,g_dbi_445_p445(i).year445_id,g_dbi_445_p445(i).sequence, g_dbi_445_p445(i).start_date, g_dbi_445_p445(i).end_date, null,null,
2133 g_who,sysdate,g_who,sysdate,g_who,g_dbi_445_p445(i).period445_id);
2134 for j in 1..g_num_lang loop
2135 insert into bsc_sys_periods_tl(YEAR,PERIODICITY_ID,PERIOD_ID,MONTH,LANGUAGE,SOURCE_LANG,NAME,SHORT_NAME)
2136 values(g_dbi_445_p445(i).year445_id,g_445_p445_per_id,g_dbi_445_p445(i).sequence,1,g_lang(j),g_src_lang,
2137 g_dbi_445_p445(i).name,null);
2138 end loop;
2139 end loop;
2140 --populate bsc_sys_periods for YEAR. for year, we dont populate bsc_db_calendar
2141 --cursor c3 is select year445_id,year445_id from fii_time_year445;
2142 for i in 1..g_num_dbi_445_year loop
2143 insert into bsc_sys_periods(PERIODICITY_ID,YEAR,PERIOD_ID,START_DATE,END_DATE,START_PERIOD,
2144 END_PERIOD,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,TIME_FK)
2145 values(g_445_year_per_id,g_dbi_445_year(i).sequence,g_dbi_445_year(i).sequence, g_dbi_445_year(i).start_date, g_dbi_445_year(i).end_date, null,null,
2146 g_who,sysdate,g_who,sysdate,g_who,g_dbi_445_year(i).year445_id);
2147 for j in 1..g_num_lang loop
2148 insert into bsc_sys_periods_tl(YEAR,PERIODICITY_ID,PERIOD_ID,MONTH,LANGUAGE,SOURCE_LANG,NAME,SHORT_NAME)
2149 values(g_dbi_445_year(i).sequence,g_445_year_per_id,g_dbi_445_year(i).sequence,1,g_lang(j),g_src_lang,
2150 g_dbi_445_year(i).name,null);
2151 end loop;
2152 end loop;
2153 Exception when others then
2154 g_status_message:=sqlerrm;
2155 write_to_log_file_n('Error in load_dbi_445_cal_data '||g_status_message||get_time);
2156 raise;
2157 End;
2158
2159 procedure load_dbi_greg_cal is
2160 ----
2161 l_greg_current_year number;
2162 l_greg_start_month number;
2163 l_greg_start_day number;
2164 ----
2165 l_year number;
2166 l_qtr number;
2167 l_period number;
2168 l_xtd_pattern varchar2(4000);
2169 ----
2170 l_cal_name varchar2(400);
2171 l_name varchar2(400);
2172 l_source_lang varchar2(100);
2173 ---
2174 x_msg_data varchar2(1000);
2175
2176 Begin
2177 if g_debug then
2178 write_to_log_file_n('In load_dbi_greg_cal'||get_time);
2179 end if;
2180
2181 --Enh 4530872
2182 /* Get short name of calendar, create dimension corresponding to calendar if it doesnt exist */
2183 if (g_greg_cal_short_name is null) then
2184 g_greg_cal_short_name := generate_short_name;
2185 if create_dim(g_greg_cal_short_name, x_msg_data) then
2186 null;
2187 else
2188 g_status_message := 'Unable to create Gregorian calendar:'||g_greg_cal_short_name||' dimension, '||x_msg_data;
2189 raise g_exception;
2190 end if;
2191 end if;
2192
2193 /* Get short name of Greg day, create level/dim object if it doesnt exist */
2194
2195 if (g_greg_day_short_name is null) then
2196 g_greg_day_short_name := generate_short_name;
2197 if create_dim_obj(g_greg_day_short_name, g_greg_cal_short_name, 'FII_TIME_DAY_V', x_msg_data) then
2198 null;
2199 else
2200 g_status_message := 'Unable to create TIME_DAYGREG level:'||g_greg_day_short_name||':'||x_msg_data;
2201 raise g_exception;
2202 end if;
2203 end if;
2204
2205 -- Get short name of Greg month
2206 if (g_greg_period_short_name is null) then
2207 g_greg_period_short_name := generate_short_name;
2208 if create_dim_obj(g_greg_period_short_name, g_greg_cal_short_name, 'FII_TIME_MONTH_V', x_msg_data) then
2209 null;
2210 else
2211 g_status_message := 'Unable to create TIME_MONTHGREG level:'||g_greg_period_short_name||':'||x_msg_data;
2212 raise g_exception;
2213 end if;
2214 end if;
2215
2216 -- Get short name of Greg quarter
2217 if (g_greg_qtr_short_name is null) then
2218 g_greg_qtr_short_name := generate_short_name;
2219 if create_dim_obj(g_greg_qtr_short_name, g_greg_cal_short_name, 'FII_TIME_QTR_V', x_msg_data) then
2220 null;
2221 else
2222 g_status_message := 'Unable to create TIME_QTRGREG level:'||g_greg_qtr_short_name||':'||x_msg_data;
2223 raise g_exception;
2224 end if;
2225 end if;
2226
2227 -- Get short name of Greg year
2228 if (g_greg_year_short_name is null) then
2229 g_greg_year_short_name := generate_short_name;
2230 if create_dim_obj(g_greg_year_short_name, g_greg_cal_short_name, 'FII_TIME_YEAR_V', x_msg_data) then
2231 null;
2232 else
2233 g_status_message := 'Unable to create TIME_YEARGREG level:'||g_greg_year_short_name||':'||x_msg_data;
2234 raise g_exception;
2235 end if;
2236 end if;
2237
2238 l_greg_current_year:=to_number(to_char(g_greg_start_date,'YYYY'));
2239 l_greg_start_month:=to_number(to_char(g_greg_start_date,'MM'));
2240 l_greg_start_day:=to_number(to_char(g_greg_start_date,'DD'));
2241 if g_debug then
2242 write_to_log_file_n('l_greg_current_year='||l_greg_current_year||',l_greg_start_month='||
2243 l_greg_start_month||',l_greg_start_day='||l_greg_start_day);
2244 end if;
2245 --cal
2246 insert into bsc_sys_calendars_b(CALENDAR_ID,EDW_FLAG,EDW_CALENDAR_ID,EDW_CALENDAR_TYPE_ID,
2247 FISCAL_YEAR,FISCAL_CHANGE,RANGE_YR_MOD,CURRENT_YEAR,START_MONTH,START_DAY,CREATED_BY,CREATION_DATE,
2248 LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN, SHORT_NAME) values (
2249 g_greg_cal_id,0,1003,1,g_bsc_greg_fiscal_year,g_greg_fiscal_change,0,l_greg_current_year,
2250 l_greg_start_month,l_greg_start_day,g_who,sysdate,g_who,sysdate,g_who, g_greg_cal_short_name);
2251 --cal TL
2252 l_cal_name:=get_lookup_value('BSC_DBI_CAL_NAME','GREG_CALENDAR_NAME',g_src_lang,l_source_lang);
2253 --FND_MESSAGE.SET_NAME('BSC','BSC_DBI_GREG_CALENDAR_NAME');
2254 --l_cal_name:=FND_MESSAGE.GET;
2255 if g_debug then
2256 write_to_log_file_n('cal name='||l_cal_name);
2257 end if;
2258 for i in 1..g_num_lang loop
2259 l_cal_name:=get_lookup_value('BSC_DBI_CAL_NAME','GREG_CALENDAR_NAME',g_lang(i),l_source_lang);
2260 insert into bsc_sys_calendars_tl(CALENDAR_ID,LANGUAGE,SOURCE_LANG,NAME,HELP,
2261 CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
2262 values(g_greg_cal_id,g_lang(i),l_source_lang,l_cal_name,l_cal_name,g_who,sysdate,g_who,sysdate,g_who);
2263 end loop;
2264 /*
2265 need to fill later
2266 */
2267 --bsc_sys_periodicity
2268 l_xtd_pattern:=','||g_greg_year_per_id||','||g_greg_qtr_per_id||','||g_greg_period_per_id||','||
2269 g_greg_day_per_id||',:225;';
2270
2271 /* INSERT GREGORIAN DAY PERIODICITY */
2272
2273 insert into bsc_sys_periodicities(PERIODICITY_ID,NUM_OF_PERIODS,SOURCE,NUM_OF_SUBPERIODS,
2274 PERIOD_COL_NAME,SUBPERIOD_COL_NAME,YEARLY_FLAG,EDW_FLAG,CALENDAR_ID,EDW_PERIODICITY_ID,
2275 CUSTOM_CODE,DB_COLUMN_NAME,PERIODICITY_TYPE,PERIOD_TYPE_ID,RECORD_TYPE_ID,XTD_PATTERN, short_name) values (
2276 g_greg_day_per_id,365,null,0,'PERIOD',null,0,0,g_greg_cal_id,0,1,'CUSTOM_1',9,1,1,l_xtd_pattern, g_greg_day_short_name);
2277 --FND_MESSAGE.SET_NAME('BSC','BSC_DBI_GREG_DAY_NAME');
2278 l_name:=get_bis_dim_long_name('FII_TIME_DAY',g_src_lang,l_source_lang);
2279 if g_debug then
2280 write_to_log_file_n('periodicity name DBI greg day='||l_name);
2281 end if;
2282 for i in 1..g_num_lang loop
2283 l_name:=get_bis_dim_long_name('FII_TIME_DAY',g_lang(i),l_source_lang);
2284 insert into bsc_sys_periodicities_tl(PERIODICITY_ID,LANGUAGE,SOURCE_LANG,NAME,CREATED_BY,
2285 CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
2286 values(g_greg_day_per_id,g_lang(i),l_source_lang,l_name,g_who,sysdate,g_who,sysdate,g_who);
2287 end loop;
2288 --
2289 l_xtd_pattern:=','||g_greg_year_per_id||','||g_greg_qtr_per_id||','||g_greg_period_per_id||','||
2290 g_greg_day_per_id||',:1;';
2291
2292 /* INSERT GREGORIAN MONTH PERIODICITY */
2293
2294 insert into bsc_sys_periodicities(PERIODICITY_ID,NUM_OF_PERIODS,SOURCE,NUM_OF_SUBPERIODS,
2295 PERIOD_COL_NAME,SUBPERIOD_COL_NAME,YEARLY_FLAG,EDW_FLAG,CALENDAR_ID,EDW_PERIODICITY_ID,
2296 CUSTOM_CODE,DB_COLUMN_NAME,PERIODICITY_TYPE,PERIOD_TYPE_ID,RECORD_TYPE_ID,XTD_PATTERN, short_name) values (
2297 g_greg_period_per_id,12,g_greg_day_per_id,0,'PERIOD',null,0,0,g_greg_cal_id,0,2,'CUSTOM_2',0,32,32,l_xtd_pattern, g_greg_period_short_name);
2298 --FND_MESSAGE.SET_NAME('BSC','BSC_DBI_GREG_PERIOD_NAME');
2299 l_name:=get_bis_dim_long_name('FII_TIME_MONTH',g_src_lang,l_source_lang);
2300 if g_debug then
2301 write_to_log_file_n('periodicity name DBI greg period='||l_name);
2302 end if;
2303 for i in 1..g_num_lang loop
2304 l_name:=get_bis_dim_long_name('FII_TIME_MONTH',g_lang(i),l_source_lang);
2305 insert into bsc_sys_periodicities_tl(PERIODICITY_ID,LANGUAGE,SOURCE_LANG,NAME,CREATED_BY,
2306 CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
2307 values(g_greg_period_per_id,g_lang(i),l_source_lang,l_name,g_who,sysdate,g_who,sysdate,g_who);
2308 end loop;
2309 --
2310 l_xtd_pattern:=','||g_greg_year_per_id||','||g_greg_qtr_per_id||','||g_greg_period_per_id||','||
2311 g_greg_day_per_id||',:33;';
2312
2313 /* INSERT GREGORIAN QUARTER PERIODICITY */
2314
2315 insert into bsc_sys_periodicities(PERIODICITY_ID,NUM_OF_PERIODS,SOURCE,NUM_OF_SUBPERIODS,
2316 PERIOD_COL_NAME,SUBPERIOD_COL_NAME,YEARLY_FLAG,EDW_FLAG,CALENDAR_ID,EDW_PERIODICITY_ID,
2317 CUSTOM_CODE,DB_COLUMN_NAME,PERIODICITY_TYPE,PERIOD_TYPE_ID,RECORD_TYPE_ID,XTD_PATTERN, short_name) values (
2318 g_greg_qtr_per_id,4,g_greg_period_per_id,0,'PERIOD',null,0,0,g_greg_cal_id,0,2,'CUSTOM_3',0,64,64,l_xtd_pattern, g_greg_qtr_short_name);
2319 --FND_MESSAGE.SET_NAME('BSC','BSC_DBI_GREG_QTR_NAME');
2320 l_name:=get_bis_dim_long_name('FII_TIME_QTR',g_src_lang,l_source_lang);
2321 if g_debug then
2322 write_to_log_file_n('periodicity name DBI greg qtr='||l_name);
2323 end if;
2324 for i in 1..g_num_lang loop
2325 l_name:=get_bis_dim_long_name('FII_TIME_QTR',g_lang(i),l_source_lang);
2326 insert into bsc_sys_periodicities_tl(PERIODICITY_ID,LANGUAGE,SOURCE_LANG,NAME,CREATED_BY,
2327 CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
2328 values(g_greg_qtr_per_id,g_lang(i),l_source_lang,l_name,g_who,sysdate,g_who,sysdate,g_who);
2329 end loop;
2330 --
2331 l_xtd_pattern:=','||g_greg_year_per_id||','||g_greg_qtr_per_id||','||g_greg_period_per_id||','||
2332 g_greg_day_per_id||',:97;';
2333
2334 /* INSERT GREGORIAN YEAR PERIODICITY */
2335
2336 insert into bsc_sys_periodicities(PERIODICITY_ID,NUM_OF_PERIODS,SOURCE,NUM_OF_SUBPERIODS,
2337 PERIOD_COL_NAME,SUBPERIOD_COL_NAME,YEARLY_FLAG,EDW_FLAG,CALENDAR_ID,EDW_PERIODICITY_ID,
2338 CUSTOM_CODE,DB_COLUMN_NAME,PERIODICITY_TYPE,PERIOD_TYPE_ID,RECORD_TYPE_ID,XTD_PATTERN, short_name) values (
2339 g_greg_year_per_id,1,g_greg_qtr_per_id,0,'PERIOD',null,1,0,g_greg_cal_id,0,2,'YEAR',1,128,128,l_xtd_pattern, g_greg_year_short_name);
2340 --FND_MESSAGE.SET_NAME('BSC','BSC_DBI_GREG_YEAR_NAME');
2341 l_name:=get_bis_dim_long_name('FII_TIME_YEAR',g_src_lang,l_source_lang);
2342 if g_debug then
2343 write_to_log_file_n('periodicity name DBI greg year='||l_name);
2344 end if;
2345 for i in 1..g_num_lang loop
2346 l_name:=get_bis_dim_long_name('FII_TIME_YEAR', g_lang(i),l_source_lang);
2347 insert into bsc_sys_periodicities_tl(PERIODICITY_ID,LANGUAGE,SOURCE_LANG,NAME,CREATED_BY,
2348 CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
2349 values(g_greg_year_per_id,g_lang(i),l_source_lang,l_name,g_who,sysdate,g_who,sysdate,g_who);
2350 end loop;
2351
2352 Exception when others then
2353 g_status_message:= g_status_message||' '||sqlerrm;
2354 write_to_log_file_n('Error in load_dbi_greg_cal '||g_status_message||get_time);
2355 raise;
2356 End;
2357
2358 procedure load_dbi_greg_cal_data is
2359 --
2360 l_prev_qtr number;
2361 l_prev_period number;
2362 l_days_in_year number;
2363 l_year number;
2364 l_qtr number;
2365 l_period number;
2366 l_day number;
2367 --
2368 Begin
2369 --l_day:=get_day365(g_greg_start_date,g_dbi_cal_record(1).report_date)-1;
2370 --if l_day is null then
2371 --raise g_exception;
2372 --end if;
2373 --populate bsc_sys_periods and bsc_db_calendar for DAY
2374 l_prev_qtr:=0;
2375 l_prev_period:=0;
2376 for i in 1..g_num_dbi_cal_record loop
2377 l_year:=substr(g_dbi_cal_record(i).month_id,1,4);
2378 l_qtr:=substr(g_dbi_cal_record(i).month_id,5,1);
2379 l_period:=substr(g_dbi_cal_record(i).month_id,6);
2380 l_day:=g_dbi_cal_record(i).greg_day;
2381 --insert into bsc_sys_periods
2382 --time_fk is in MM/DD/YYYY format
2383 insert into bsc_sys_periods(PERIODICITY_ID,YEAR,PERIOD_ID,START_DATE,END_DATE,START_PERIOD,
2384 END_PERIOD,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,TIME_FK)
2385 values(g_greg_day_per_id,l_year,l_day, g_dbi_cal_record(i).start_date, g_dbi_cal_record(i).end_date, null,null,g_who,sysdate,g_who,sysdate,g_who,
2386 g_dbi_cal_record(i).cal_month||'/'||g_dbi_cal_record(i).cal_day||'/'||g_dbi_cal_record(i).cal_year);
2387 for j in 1..g_num_lang loop
2388 insert into bsc_sys_periods_tl(YEAR,PERIODICITY_ID,PERIOD_ID,MONTH,LANGUAGE,SOURCE_LANG,NAME,SHORT_NAME)
2389 values(l_year,g_greg_day_per_id,l_day,1,g_lang(j),g_src_lang,g_dbi_cal_record(i).report_date,null);
2390 end loop;
2391 --insert into bsc_db_calendar
2392 g_db_cal_modified:=true;
2393 insert into bsc_db_calendar(CALENDAR_YEAR,CALENDAR_MONTH,CALENDAR_DAY,YEAR,SEMESTER,
2394 QUARTER,BIMESTER,MONTH,WEEK52,WEEK4,DAY365,DAY30,HOLYDAY_FLAG,WORKDAY_FLAG,CALENDAR_ID,
2395 CUSTOM_1,CUSTOM_2,CUSTOM_3)
2396 values(g_dbi_cal_record(i).cal_year,g_dbi_cal_record(i).cal_month,g_dbi_cal_record(i).cal_day,
2397 l_year,0,0,0,0,0,0,0,0,null,null,g_greg_cal_id,l_day,l_period,l_qtr);
2398 ----
2399 l_prev_qtr:=l_qtr;
2400 l_prev_period:=l_period;
2401 end loop;
2402 --populate bsc_sys_periods for PERIOD. for period, we dont populate bsc_db_calendar
2403 --cursor c1 is select month_id,substr(month_id,1,4),substr(month_id,6) from FII_TIME_MONTH order by month_id;
2404 for i in 1..g_num_dbi_greg_period loop
2405 insert into bsc_sys_periods(PERIODICITY_ID,YEAR,PERIOD_ID,START_DATE,END_DATE,START_PERIOD,
2406 END_PERIOD,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,TIME_FK)
2407 values(g_greg_period_per_id,g_dbi_greg_period(i).year_id,g_dbi_greg_period(i).sequence, g_dbi_greg_period(i).start_date, g_dbi_greg_period(i).end_date, null,null,
2408 g_who,sysdate,g_who,sysdate,g_who,g_dbi_greg_period(i).month_id);
2409 for j in 1..g_num_lang loop
2410 insert into bsc_sys_periods_tl(YEAR,PERIODICITY_ID,PERIOD_ID,MONTH,LANGUAGE,SOURCE_LANG,NAME,SHORT_NAME)
2411 values(g_dbi_greg_period(i).year_id,g_greg_period_per_id,g_dbi_greg_period(i).sequence,1,g_lang(j),
2412 g_src_lang,g_dbi_greg_period(i).name,null);
2413 end loop;
2414 end loop;
2415 --populate bsc_sys_periods for QTR. for qtr, we dont populate bsc_db_calendar
2416 --cursor c2 is select quarter_id,year_id,substr(quarter_id,5) from fii_time_quarter order by quarter_id;
2417 for i in 1..g_num_dbi_greg_qtr loop
2418 insert into bsc_sys_periods(PERIODICITY_ID,YEAR,PERIOD_ID,START_DATE,END_DATE,START_PERIOD,
2419 END_PERIOD,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,TIME_FK)
2420 values(g_greg_qtr_per_id,g_dbi_greg_qtr(i).year_id,g_dbi_greg_qtr(i).sequence, g_dbi_greg_qtr(i).start_date, g_dbi_greg_qtr(i).end_date, null,null,
2421 g_who,sysdate,g_who,sysdate,g_who,g_dbi_greg_qtr(i).quarter_id);
2422 for j in 1..g_num_lang loop
2423 insert into bsc_sys_periods_tl(YEAR,PERIODICITY_ID,PERIOD_ID,MONTH,LANGUAGE,SOURCE_LANG,NAME,SHORT_NAME)
2424 values(g_dbi_greg_qtr(i).year_id,g_greg_qtr_per_id,g_dbi_greg_qtr(i).sequence,1,g_lang(j),g_src_lang,
2425 g_dbi_greg_qtr(i).name,null);
2426 end loop;
2427 end loop;
2428 --populate bsc_sys_periods for YEAR. for year, we dont populate bsc_db_calendar
2429 --cursor c3 is select year_id,year_id from fii_time_year order by year_id;
2430 for i in 1..g_num_dbi_greg_year loop
2431 insert into bsc_sys_periods(PERIODICITY_ID,YEAR,PERIOD_ID,START_DATE,END_DATE,START_PERIOD,
2432 END_PERIOD,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,TIME_FK)
2433 values(g_greg_year_per_id,g_dbi_greg_year(i).sequence,g_dbi_greg_year(i).sequence, g_dbi_greg_year(i).start_date, g_dbi_greg_year(i).end_date, null,null,
2434 g_who,sysdate,g_who,sysdate,g_who,g_dbi_greg_year(i).year_id);
2435 for j in 1..g_num_lang loop
2436 insert into bsc_sys_periods_tl(YEAR,PERIODICITY_ID,PERIOD_ID,MONTH,LANGUAGE,SOURCE_LANG,NAME,SHORT_NAME)
2437 values(g_dbi_greg_year(i).sequence,g_greg_year_per_id,g_dbi_greg_year(i).sequence,1,g_lang(j),g_src_lang,
2438 g_dbi_greg_year(i).name,null);
2439 end loop;
2440 end loop;
2441 Exception when others then
2442 g_status_message:=sqlerrm;
2443 write_to_log_file_n('Error in load_dbi_greg_cal_data '||g_status_message||get_time);
2444 raise;
2445 End;
2446
2447 procedure delete_dbi_calendar_metadata is
2448 ---
2449 cursor c1 is select calendar_id from bsc_sys_calendars_b where edw_calendar_type_id in (1) and
2450 edw_calendar_id in (1001,1002,1003);
2451 ---
2452 l_calendar_id number;
2453 Begin
2454 if g_debug then
2455 write_to_log_file_n('In delete_dbi_calendar_metadata'||get_time);
2456 end if;
2457 if g_debug then
2458 write_to_log_file_n('select calendar_id from bsc_sys_calendars_b where edw_calendar_type_id in (1) and
2459 edw_calendar_id in (1001,1002,1003)');
2460 end if;
2461 open c1;
2462 loop
2463 fetch c1 into l_calendar_id;
2464 exit when c1%notfound;
2465 if g_debug then
2466 write_to_log_file_n('delete bsc_sys_periodicities_tl where periodicity_id in '||
2467 '(select periodicity_id from bsc_sys_periodicities where calendar_id='||l_calendar_id||')'||get_time);
2468 end if;
2469 delete bsc_sys_periodicities_tl where periodicity_id in
2470 (select periodicity_id from bsc_sys_periodicities where calendar_id=l_calendar_id);
2471 if g_debug then
2472 write_to_log_file_n('Deleted '||sql%rowcount||' rows '||get_time);
2473 end if;
2474 if g_debug then
2475 write_to_log_file_n('delete bsc_sys_periodicities where calendar_id='||l_calendar_id||';'||get_time);
2476 end if;
2477 delete bsc_sys_periodicities where calendar_id=l_calendar_id;
2478 if g_debug then
2479 write_to_log_file_n('Deleted '||sql%rowcount||' rows '||get_time);
2480 end if;
2481 if g_debug then
2482 write_to_log_file_n('delete bsc_sys_calendars_tl where calendar_id='||l_calendar_id||');'||get_time);
2483 end if;
2484 delete bsc_sys_calendars_tl where calendar_id=l_calendar_id;
2485 if g_debug then
2486 write_to_log_file_n('Deleted '||sql%rowcount||' rows '||get_time);
2487 end if;
2488 if g_debug then
2489 write_to_log_file_n('delete bsc_sys_calendars_b where calendar_id='||l_calendar_id||');'||get_time);
2490 end if;
2491 delete bsc_sys_calendars_b where calendar_id=l_calendar_id;
2492 if g_debug then
2493 write_to_log_file_n('Deleted '||sql%rowcount||' rows '||get_time);
2494 end if;
2495 end loop;
2496 Exception when others then
2497 g_status_message:=sqlerrm;
2498 write_to_log_file_n('Error in delete_dbi_calendar_metadata '||g_status_message||get_time);
2499 raise;
2500 End;
2501
2502 procedure delete_dbi_calendars
2503 is
2504 ---
2505 cursor c1 is select calendar_id from bsc_sys_calendars_b where edw_calendar_type_id in (1) and
2506 edw_calendar_id in (1001,1002,1003);
2507 ---
2508 l_calendar_id number;
2509 Begin
2510 if g_debug then
2511 write_to_log_file_n('In delete_dbi_calendars'||get_time);
2512 end if;
2513 if g_debug then
2514 write_to_log_file_n('select calendar_id from bsc_sys_calendars_b where edw_calendar_type_id in (1) and
2515 edw_calendar_id in (1001,1002,1003)');
2516 end if;
2517 open c1;
2518 loop
2519 fetch c1 into l_calendar_id;
2520 exit when c1%notfound;
2521 if g_debug then
2522 write_to_log_file_n('delete bsc_sys_periods where periodicity_id in (select periodicity_id '||
2523 'from bsc_sys_periodicities where calendar_id='||l_calendar_id||');'||get_time);
2524 end if;
2525 delete bsc_sys_periods where periodicity_id in (select periodicity_id from bsc_sys_periodicities
2526 where calendar_id=l_calendar_id);
2527 if g_debug then
2528 write_to_log_file_n('Deleted '||sql%rowcount||' rows '||get_time);
2529 end if;
2530 if g_debug then
2531 write_to_log_file_n('delete bsc_sys_periods_tl where periodicity_id in (select periodicity_id '||
2532 'from bsc_sys_periodicities where calendar_id='||l_calendar_id||');'||get_time);
2533 end if;
2534 delete bsc_sys_periods_tl where periodicity_id in (select periodicity_id from bsc_sys_periodicities
2535 where calendar_id=l_calendar_id);
2536 if g_debug then
2537 write_to_log_file_n('Deleted '||sql%rowcount||' rows '||get_time);
2538 end if;
2539 if g_debug then
2540 write_to_log_file_n('delete bsc_db_calendar where calendar_id='||l_calendar_id||');'||get_time);
2541 end if;
2542 delete bsc_db_calendar where calendar_id=l_calendar_id;
2543 if g_debug then
2544 write_to_log_file_n('Deleted '||sql%rowcount||' rows '||get_time);
2545 end if;
2546 /*
2547 reporting calendar is always truncated and full refreshed in
2548 BSC_BSC_ADAPTER.load_reporting_calendar
2549 if g_debug then
2550 write_to_log_file_n('delete bsc_reporting_calendar where calendar_id='||l_calendar_id||');'||get_time);
2551 end if;
2552 delete bsc_reporting_calendar where calendar_id=l_calendar_id;
2553 if g_debug then
2554 write_to_log_file_n('Deleted '||sql%rowcount||' rows '||get_time);
2555 end if;
2556 */
2557 end loop;
2558 Exception when others then
2559 g_status_message:=sqlerrm;
2560 write_to_log_file_n('Error in delete_dbi_calendars '||g_status_message||get_time);
2561 raise;
2562 End;
2563
2564 procedure analyze_tables is
2565 l_owner varchar2(200);
2566 Begin
2567 if g_debug then
2568 write_to_log_file_n('In analyze_tables'||get_time);
2569 end if;
2570 l_owner:=BSC_IM_UTILS.get_bsc_owner;
2571 BSC_IM_UTILS.analyze_object('BSC_SYS_PERIODS',l_owner,null,null,null);
2572 BSC_IM_UTILS.analyze_object('BSC_DB_CALENDAR',l_owner,null,null,null);
2573 if g_debug then
2574 write_to_log_file_n('Done analyze_tables'||get_time);
2575 end if;
2576 Exception when others then
2577 g_status_message:=sqlerrm;
2578 write_to_log_file_n('Error in analyze_tables '||g_status_message||get_time);
2579 raise;
2580 End;
2581
2582 procedure init_all is
2583 cursor c1 is select language_code from FND_LANGUAGES where INSTALLED_FLAG in ('I', 'B');
2584 Begin
2585 g_who:=0;
2586 if BSC_IM_UTILS.get_option_value(g_options,g_number_options,'DEBUG LOG')='Y' then
2587 g_debug:=true;
2588 BSC_IM_UTILS.open_file('TEST');
2589 end if;
2590 g_debug:=true;
2591 g_src_lang:=USERENV('LANG');
2592 g_num_lang:=1;
2593 open c1;
2594 loop
2595 fetch c1 into g_lang(g_num_lang);
2596 exit when c1%notfound;
2597 g_num_lang:=g_num_lang+1;
2598 end loop;
2599 g_num_lang:=g_num_lang-1;
2600 if g_debug then
2601 write_to_log_file_n('source language='||g_src_lang);
2602 write_to_log_file('Installed languages');
2603 for i in 1..g_num_lang loop
2604 write_to_log_file(g_lang(i));
2605 end loop;
2606 end if;
2607 g_db_cal_modified:=false;
2608 g_ent_fiscal_change:=0;
2609 g_445_fiscal_change:=0;
2610 g_greg_fiscal_change:=0;
2611 Exception when others then
2612 g_status_message:=sqlerrm;
2613 raise;
2614 End;
2615
2616 procedure write_to_log_file(p_message varchar2) is
2617 Begin
2618 BSC_IM_UTILS.write_to_log_file(p_message);
2619 Exception when others then
2620 BSC_IM_UTILS.g_status_message:=sqlerrm;
2621 null;
2622 End;
2623
2624 procedure write_to_log_file_n(p_message varchar2) is
2625 begin
2626 write_to_log_file(' ');
2627 write_to_log_file(p_message);
2628 Exception when others then
2629 BSC_IM_UTILS.g_status_message:=sqlerrm;
2630 null;
2631 end;
2632
2633 function get_time return varchar2 is
2634 begin
2635 return BSC_IM_UTILS.get_time;
2636 Exception when others then
2637 BSC_IM_UTILS.g_status_message:=sqlerrm;
2638 null;
2639 End;
2640
2641 function get_periodicity_nextval return number is
2642 l_seqval number;
2643 Begin
2644 select bsc_sys_periodicity_id_s.nextval into l_seqval from dual;
2645 return l_seqval;
2646 Exception when others then
2647 BSC_IM_UTILS.g_status_message:=sqlerrm;
2648 raise;
2649 End;
2650
2651 function get_calendar_nextval return number is
2652 l_seqval number;
2653 Begin
2654 select bsc_sys_calendar_id_s.nextval into l_seqval from dual;
2655 return l_seqval;
2656 Exception when others then
2657 BSC_IM_UTILS.g_status_message:=sqlerrm;
2658 raise;
2659 End;
2660
2661 /*
2662 given a start date of the calendar and a particular date, this api tells you
2663 what is the day365 value of this date
2664 */
2665 --this function is obsolete...superseded by calculate_day365 and calculate_day365_445
2666 function get_day365(
2667 p_cal_start_date date,
2668 p_this_date date
2669 )return number is
2670 --
2671 l_this_year number;
2672 l_start_date date;
2673 l_day365 number;
2674 l_days_in_year number;
2675 --
2676 Begin
2677 l_this_year:=to_number(to_char(p_this_date,'YYYY'));
2678 if mod(l_this_year,4)=0 then --leap year
2679 l_days_in_year:=366;
2680 else
2681 l_days_in_year:=365;
2682 end if;
2683 l_start_date:=to_date(to_char(p_cal_start_date,'MM/DD')||'/'||l_this_year,'MM/DD/YYYY');
2684 if l_start_date>p_this_date then
2685 l_day365:=l_days_in_year-(l_start_date-p_this_date)+1;
2686 else
2687 l_day365:=p_this_date-l_start_date+1;
2688 end if;
2689 return l_day365;
2690 Exception when others then
2691 BSC_IM_UTILS.g_status_message:=sqlerrm;
2692 raise;
2693 End;
2694
2695 procedure calculate_day365(p_mode varchar2,p_cal_id number) is
2696 --
2697 cursor c1(p_cal_id number) is select day365 from bsc_db_calendar where calendar_id=p_cal_id
2698 order by calendar_year desc,calendar_month desc,calendar_day desc;
2699 --
2700 l_index number;
2701 l_prev_day number;
2702 l_cal_start_date date;
2703 l_ent_yr_index number;
2704 l_yr_compare_fmt VARCHAR2(100);
2705 --
2706 Begin
2707 if g_debug then
2708 write_to_log_file_n('In calculate_day365 '||p_mode||', cal='||p_cal_id||get_time);
2709 end if;
2710
2711 -- Fix: in condition to check year change; we should also include YYYY part in date comparison
2712 -- added for bug 6014462, separated the logic for enterprise and gregorian calendar
2713 -- because enterprise calendar can start at different date every year
2714 -- for gregorian the year start date is same for every year so here we do not
2715 -- need dd/mm/yyyy comparison simpel dd/mm comparision will do.
2716 l_yr_compare_fmt := 'MM/DD/YYYY';
2717
2718 if p_cal_id=g_ent_cal_id then
2719 --bug 5461356 don't assume that start date will be same for every year
2720 -- for enterprise and 445 calendar
2721 l_ent_yr_index :=1;
2722 l_cal_start_date:=g_dbi_ent_year(l_ent_yr_index).start_date;
2723 else
2724 l_cal_start_date:=g_greg_start_date;
2725 l_yr_compare_fmt := 'MM/DD';
2726 end if;
2727 if p_mode='full' then
2728 for i in 1..g_num_dbi_cal_record loop
2729 if to_char(l_cal_start_date,l_yr_compare_fmt)=to_char(g_dbi_cal_record(i).report_date,l_yr_compare_fmt) then
2730 l_index:=i;
2731 exit;
2732 end if;
2733 end loop;
2734 if g_debug then
2735 write_to_log_file_n('l_index='||l_index);
2736 end if;
2737 if l_index is null then
2738 write_to_log_file_n('could not locate start month/date');
2739 raise g_exception;
2740 end if;
2741 l_index:=l_index-1;
2742 if l_index>0 then
2743 -- bug5461356
2744 -- we can not assume that an year will have max 365 or 366 days..
2745 -- in dbi it can be 365+/-14
2746 if(l_index>366) then
2747 l_prev_day:=l_index;
2748 else
2749 l_prev_day:=366;
2750 end if;
2751 for i in reverse 1..l_index loop
2752 if p_cal_id=g_ent_cal_id then
2753 g_dbi_cal_record(i).ent_day:=l_prev_day;
2754 else
2755 g_dbi_cal_record(i).greg_day:=l_prev_day;
2756 end if;
2757 l_prev_day:=l_prev_day-1;
2758 end loop;
2759 end if;
2760 l_index:=l_index+1;
2761 l_prev_day:=0;
2762 else
2763 --INCREMENTAL
2764 if g_debug then
2765 write_to_log_file_n('select day365 from bsc_db_calendar where calendar_id=:1
2766 order by calendar_year desc,calendar_month desc,calendar_day desc using '||p_cal_id);
2767 end if;
2768 open c1(p_cal_id);
2769 fetch c1 into l_prev_day;
2770 close c1;
2771 l_index:=1;
2772 --assume dates are consequetive, this means that the first date in g_dbi_cal_record is l_max_date +1
2773 end if;
2774 if g_debug then
2775 write_to_log_file_n('l_prev_day='||l_prev_day||', l_index='||l_index);
2776 end if;
2777 for i in l_index..g_num_dbi_cal_record loop
2778 if to_char(l_cal_start_date,l_yr_compare_fmt)=to_char(g_dbi_cal_record(i).report_date,l_yr_compare_fmt) then
2779 if p_cal_id=g_ent_cal_id then
2780 l_ent_yr_index:=l_ent_yr_index+1;
2781 if(l_ent_yr_index<=g_num_dbi_ent_year) then
2782 l_cal_start_date := g_dbi_ent_year(l_ent_yr_index).start_date;
2783 else
2784 l_cal_start_date := g_ent_start_date;
2785 end if;
2786 end if;
2787 l_prev_day:=1;
2788 else
2789 l_prev_day:=l_prev_day+1;
2790 end if;
2791 if p_cal_id=g_ent_cal_id then
2792 g_dbi_cal_record(i).ent_day:=l_prev_day;
2793 else
2794 g_dbi_cal_record(i).greg_day:=l_prev_day;
2795 end if;
2796 end loop;
2797 Exception when others then
2798 g_status_message:=sqlerrm;
2799 write_to_log_file_n('Error in calculate_day365 '||sqlerrm||get_time);
2800 raise;
2801 End;
2802
2803 procedure calculate_day365_445(p_mode varchar2) is
2804 --
2805 cursor c1(p_cal_id number) is select day365 from bsc_db_calendar where calendar_id=p_cal_id
2806 order by calendar_year desc,calendar_month desc,calendar_day desc;
2807 --
2808 l_index number;
2809 l_prev_day number;
2810 l_cal_start_date date;
2811 l_week number;
2812 l_p445 number;
2813 l_flag boolean;
2814 --
2815 Begin
2816 if g_debug then
2817 write_to_log_file_n('In calculate_day365_445 '||p_mode||get_time);
2818 end if;
2819 l_cal_start_date:=g_445_start_date;
2820 if p_mode='full' then
2821 for i in 1..g_num_dbi_cal_record loop
2822 l_p445:=substr(g_dbi_cal_record(i).week_id,5,2);
2823 l_week:=substr(g_dbi_cal_record(i).week_id,7);
2824 if l_p445=1 and l_week=1 then
2825 l_index:=i;
2826 exit;
2827 end if;
2828 end loop;
2829 if g_debug then
2830 write_to_log_file_n('l_index='||l_index);
2831 end if;
2832 if l_index is null then
2833 write_to_log_file_n('could not locate start month/date');
2834 raise g_exception;
2835 end if;
2836 l_index:=l_index-1;
2837 if l_index>0 then
2838 -- bug5461356
2839 -- we can not assume that an year will have max 365 or 366 days..
2840 -- in dbi it can be 365+/-14
2841 if(l_index>366) then
2842 l_prev_day:=l_index;
2843 else
2844 l_prev_day:=366;
2845 end if;
2846 l_prev_day:=366;
2847 for i in reverse 1..l_index loop
2848 g_dbi_cal_record(i).p445_day:=l_prev_day;
2849 l_prev_day:=l_prev_day-1;
2850 end loop;
2851 end if;
2852 l_index:=l_index+1;
2853 l_prev_day:=0;
2854 else
2855 --INCREMENTAL
2856 if g_debug then
2857 write_to_log_file_n('select day365 from bsc_db_calendar where calendar_id=:1
2858 order by calendar_year desc,calendar_month desc,calendar_day desc using '||g_445_cal_id);
2859 end if;
2860 open c1(g_445_cal_id);
2861 fetch c1 into l_prev_day;
2862 close c1;
2863 l_index:=1;
2864 --assume dates are consequetive, this means that the first date in g_dbi_cal_record is l_max_date +1
2865 end if;
2866 if g_debug then
2867 write_to_log_file_n('l_prev_day='||l_prev_day||', l_index='||l_index);
2868 end if;
2869 l_flag:=true;
2870 for i in l_index..g_num_dbi_cal_record loop
2871 l_p445:=substr(g_dbi_cal_record(i).week_id,5,2);
2872 l_week:=substr(g_dbi_cal_record(i).week_id,7);
2873 if l_week=1 and l_p445=1 then
2874 if l_flag then
2875 l_prev_day:=1;
2876 l_flag:=false;
2877 else
2878 l_prev_day:=l_prev_day+1;
2879 end if;
2880 else
2881 l_prev_day:=l_prev_day+1;
2882 l_flag:=true;
2883 end if;
2884 g_dbi_cal_record(i).p445_day:=l_prev_day;
2885 end loop;
2886 Exception when others then
2887 g_status_message:=sqlerrm;
2888 write_to_log_file_n('Error in calculate_day365_445 '||sqlerrm||get_time);
2889 raise;
2890 End;
2891
2892 procedure dmp_g_dbi_cal_record is
2893 Begin
2894 write_to_log_file_n('Output from g_dbi_cal_record');
2895 for i in 1..g_num_dbi_cal_record loop
2896 write_to_log_file(g_dbi_cal_record(i).report_date||' '||g_dbi_cal_record(i).cal_day||' '||g_dbi_cal_record(i).cal_month||' '||
2897 g_dbi_cal_record(i).cal_year||' '||g_dbi_cal_record(i).month_id||' '||g_dbi_cal_record(i).ent_period_id||' '||
2898 g_dbi_cal_record(i).week_id||' '||g_dbi_cal_record(i).ent_week_id||' '||g_dbi_cal_record(i).row_num||' '||
2899 g_dbi_cal_record(i).ent_day||' '||g_dbi_cal_record(i).p445_day||' '||g_dbi_cal_record(i).greg_day);
2900 end loop;
2901 Exception when others then
2902 write_to_log_file_n('Error in dmp_g_dbi_cal_record '||sqlerrm||get_time);
2903 End;
2904
2905 procedure get_ent_cal_start_date(p_mode varchar2) is
2906 --
2907 cursor c1 is select start_month||'/'||start_day||'/'||current_year from bsc_sys_calendars_b
2908 where edw_calendar_type_id=1 and edw_calendar_id=1001;
2909 l_start_date date;
2910 l_year number;
2911 l_qtr number;
2912 l_period number;
2913 l_cal_start_date varchar2(200);
2914 --
2915 Begin
2916 if g_debug then
2917 write_to_log_file_n('select to_date(start_month||''/''||start_day||''/''||current_year,''MM/DD/YYYY'') '||
2918 'from bsc_sys_calendars_b where edw_calendar_type_id=1 and edw_calendar_id=1001');
2919 end if;
2920 open c1;
2921 fetch c1 into l_cal_start_date;
2922 close c1;
2923 if l_cal_start_date is null or l_cal_start_date='//' then
2924 g_ent_start_date:=null;
2925 else
2926 g_ent_start_date:=to_date(l_cal_start_date,'MM/DD/YYYY');
2927 end if;
2928 if g_ent_start_date is null or p_mode='full' then
2929 if g_debug then
2930 write_to_log_file_n('Check mem data from fii_time_day');
2931 end if;
2932 l_start_date:=null;
2933 for i in 1..g_num_dbi_cal_record loop
2934 l_year:=substr(g_dbi_cal_record(i).ent_period_id,1,4);
2935 l_qtr:=substr(g_dbi_cal_record(i).ent_period_id,5,1);
2936 l_period:=substr(g_dbi_cal_record(i).ent_period_id,6);
2937 if l_year=g_bsc_greg_fiscal_year and l_qtr=1 and l_period=1 then
2938 l_start_date:=to_date(g_dbi_cal_record(i).cal_month||'/'||g_dbi_cal_record(i).cal_day||'/'||
2939 g_dbi_cal_record(i).cal_year,'MM/DD/YYYY');
2940 exit;
2941 end if;
2942 end loop;
2943 if l_start_date is null then
2944 /*issue ling reported. fii_time_day is populated with this filter where adjustment_period_flag='N' . this means that periods need not start
2945 from 1 if period 1 is an adjusting period.*/
2946 for i in 1..g_num_dbi_cal_record loop
2947 l_year:=substr(g_dbi_cal_record(i).ent_period_id,1,4);
2948 if l_year=g_bsc_greg_fiscal_year then /*as soon as we touch the fiscal year, set this as the ent start date */
2949 l_start_date:=to_date(g_dbi_cal_record(i).cal_month||'/'||g_dbi_cal_record(i).cal_day||'/'||
2950 g_dbi_cal_record(i).cal_year,'MM/DD/YYYY');
2951 exit;
2952 end if;
2953 end loop;
2954 end if;
2955 --
2956 if g_debug then
2957 write_to_log_file_n('g_ent_start_date='||g_ent_start_date||', l_start_date='||l_start_date);
2958 end if;
2959 /*4995603 we need to check only the month and day to see if there is a change in start date the year is upto BSC */
2960 if g_ent_start_date is not null and to_char(g_ent_start_date,'MM/DD')<>to_char(l_start_date,'MM/DD') then
2961 g_ent_fiscal_change:=1;
2962 end if;
2963 g_ent_start_date:=l_start_date;
2964 end if;
2965 if g_debug then
2966 write_to_log_file_n('g_ent_start_date='||g_ent_start_date);
2967 end if;
2968 if g_ent_start_date is null then
2969 write_to_log_file_n('g_ent_start_date NULL. fatal...');
2970 raise g_exception;
2971 end if;
2972 Exception when others then
2973 BSC_IM_UTILS.g_status_message:=sqlerrm;
2974 write_to_log_file_n('Error in get_ent_cal_start_date '||g_status_message||get_time);
2975 raise;
2976 End;
2977
2978 procedure get_445_cal_start_date(p_mode varchar2) is
2979 --
2980 cursor c1 is select start_month||'/'||start_day||'/'||current_year from bsc_sys_calendars_b
2981 where edw_calendar_type_id=1 and edw_calendar_id=1002;
2982 --
2983 l_start_date date;
2984 l_year number;
2985 l_p445 number;
2986 l_week number;
2987 l_cal_start_date varchar2(200);
2988 --
2989 Begin
2990 if g_debug then
2991 write_to_log_file_n('select to_date(start_month||''/''||start_day||''/''||current_year,''MM/DD/YYYY'') '||
2992 'from bsc_sys_calendars_b where edw_calendar_type_id=1 and edw_calendar_id=1002');
2993 end if;
2994 open c1;
2995 fetch c1 into l_cal_start_date;
2996 close c1;
2997 if l_cal_start_date is null or l_cal_start_date='//' then
2998 g_445_start_date:=null;
2999 else
3000 g_445_start_date:=to_date(l_cal_start_date,'MM/DD/YYYY');
3001 end if;
3002 if g_445_start_date is null or p_mode='full' then
3003 if g_debug then
3004 write_to_log_file_n('Check mem data from fii_time_day');
3005 end if;
3006 for i in 1..g_num_dbi_cal_record loop
3007 l_year:=substr(g_dbi_cal_record(i).week_id,1,4);
3008 l_p445:=substr(g_dbi_cal_record(i).week_id,5,2);
3009 l_week:=substr(g_dbi_cal_record(i).week_id,7);
3010 if l_year=g_bsc_greg_fiscal_year and l_p445=1 and l_week=1 then
3011 --g_445_start_date:=to_date(g_dbi_cal_record(i).cal_month||'/'||g_dbi_cal_record(i).cal_day||'/'||
3012 --g_dbi_cal_record(i).cal_year,'MM/DD/YYYY');
3013 --3872505
3014 --now, when the fiscal year changes say from 1997 to 1998, all periodicities are reset inclusing week
3015 --in that case, start day must always be 1
3016 l_start_date:=to_date(g_dbi_cal_record(i).cal_month||'/'||g_dbi_cal_record(i).cal_day||'/'||
3017 g_dbi_cal_record(i).cal_year,'MM/DD/YYYY');
3018 if g_debug then
3019 write_to_log_file_n('g_445_start_date='||g_445_start_date||', l_start_date='||l_start_date);
3020 end if;
3021 /*4995603 we need to check only the month and day to see if there is a change in start date the year is upto BSC */
3022 if g_445_start_date is not null and to_char(g_445_start_date,'MM/DD')<>to_char(l_start_date,'MM/DD') then
3023 g_445_fiscal_change:=1;
3024 end if;
3025 g_445_start_date:=l_start_date;
3026 exit;
3027 end if;
3028 end loop;
3029 end if;
3030 if g_debug then
3031 write_to_log_file_n('g_445_start_date='||g_445_start_date);
3032 end if;
3033 if g_445_start_date is null then
3034 write_to_log_file_n('g_445_start_date NULL. fatal...');
3035 raise g_exception;
3036 end if;
3037 Exception when others then
3038 BSC_IM_UTILS.g_status_message:=sqlerrm;
3039 write_to_log_file_n('Error in get_445_cal_start_date '||g_status_message||get_time);
3040 raise;
3041 End;
3042
3043 procedure get_greg_cal_start_date(p_mode varchar2) is
3044 --
3045 cursor c1 is select start_month||'/'||start_day||'/'||current_year from bsc_sys_calendars_b
3046 where edw_calendar_type_id=1 and edw_calendar_id=1003;
3047 --
3048 l_start_date date;
3049 l_year number;
3050 l_qtr number;
3051 l_period number;
3052 l_cal_start_date varchar2(200);
3053 --
3054 Begin
3055 if g_debug then
3056 write_to_log_file_n('select to_date(start_month||''/''||start_day||''/''||current_year,''MM/DD/YYYY'') '||
3057 'from bsc_sys_calendars_b where edw_calendar_type_id=1 and edw_calendar_id=1003');
3058 end if;
3059 open c1;
3060 fetch c1 into l_cal_start_date;
3061 close c1;
3062 if l_cal_start_date is null or l_cal_start_date='//' then
3063 g_greg_start_date:=null;
3064 else
3065 g_greg_start_date:=to_date(l_cal_start_date,'MM/DD/YYYY');
3066 end if;
3067 if g_greg_start_date is null or p_mode='full' then
3068 if g_debug then
3069 write_to_log_file_n('Check mem data from fii_time_day');
3070 end if;
3071 for i in 1..g_num_dbi_cal_record loop
3072 l_year:=substr(g_dbi_cal_record(i).month_id,1,4);
3073 l_qtr:=substr(g_dbi_cal_record(i).month_id,5,1);
3074 l_period:=substr(g_dbi_cal_record(i).month_id,6);
3075 if l_year=g_bsc_greg_fiscal_year and l_qtr=1 and l_period=1 then
3076 l_start_date:=to_date(g_dbi_cal_record(i).cal_month||'/'||g_dbi_cal_record(i).cal_day||'/'||
3077 g_dbi_cal_record(i).cal_year,'MM/DD/YYYY');
3078 if g_debug then
3079 write_to_log_file_n('g_greg_start_date='||g_greg_start_date||', l_start_date='||l_start_date);
3080 end if;
3081 /*4995603 we need to check only the month and day to see if there is a change in start date the year is upto BSC */
3082 if g_greg_start_date is not null and to_char(g_greg_start_date,'MM/DD')<>to_char(l_start_date,'MM/DD') then
3083 g_greg_fiscal_change:=1;
3084 end if;
3085 g_greg_start_date:=l_start_date;
3086 exit;
3087 end if;
3088 end loop;
3089 end if;
3090 if g_debug then
3091 write_to_log_file_n('g_greg_start_date='||g_greg_start_date);
3092 end if;
3093 if g_greg_start_date is null then
3094 write_to_log_file_n('g_greg_start_date NULL. fatal...');
3095 raise g_exception;
3096 end if;
3097 Exception when others then
3098 BSC_IM_UTILS.g_status_message:=sqlerrm;
3099 write_to_log_file_n('Error in get_greg_cal_start_date '||g_status_message||get_time);
3100 raise;
3101 End;
3102
3103 function get_bis_dim_long_name(p_dim varchar2, p_lang varchar2, p_source_lang out nocopy varchar2) return varchar2 is
3104 --
3105 --cursor c1(p_dim varchar2) is select name from bis_levels_vl where short_name=p_dim;
3106 cursor c1 is select name, source_lang from bis_levels_tl tl, bis_levels l
3107 where l.short_name = p_dim and
3108 l.level_id = tl.level_id and
3109 tl.language = p_lang;
3110 --
3111 l_name varchar2(4000);
3112 Begin
3113 if g_debug then
3114 write_to_log_file_n('select name from bis_levels_vl where short_name='||p_dim);
3115 end if;
3116 open c1;
3117 fetch c1 into l_name,p_source_lang;
3118 close c1;
3119 if g_debug then
3120 write_to_log_file('l_name='||l_name);
3121 end if;
3122 return l_name;
3123 Exception when others then
3124 BSC_IM_UTILS.g_status_message:=sqlerrm;
3125 write_to_log_file_n('Error in get_bis_dim_long_name '||g_status_message||get_time);
3126 raise;
3127 End;
3128
3129 function get_lookup_value(p_lookup_type varchar2,p_lookup_code varchar2,p_lang varchar2,p_source_lang out nocopy varchar2)
3130 return varchar2 is
3131 --
3132 cursor c1 is select meaning, source_lang from fnd_lookup_values where lookup_type=p_lookup_type
3133 and lookup_code=p_lookup_code
3134 and language=p_lang;
3135 --
3136 l_var varchar2(4000);
3137 --
3138 Begin
3139 if g_debug then
3140 write_to_log_file_n('get_lookup_value '||p_lookup_type||' '||p_lookup_code);
3141 end if;
3142 open c1;
3143 fetch c1 into l_var, p_source_lang;
3144 close c1;
3145 if l_var is null then
3146 l_var:=p_lookup_code;
3147 end if;
3148 return l_var;
3149 Exception when others then
3150 BSC_IM_UTILS.g_status_message:=sqlerrm;
3151 write_to_log_file_n('Error in get_lookup_value '||g_status_message||get_time);
3152 raise;
3153 End;
3154
3155 /*
3156 This api is needed by pmd. given a time level name, they need to know the
3157 bsc periodicity. time level comes from
3158 SELECT distinct attribute2
3159 from ak_region_items
3160 where attribute1 ='DIMENSION LEVEL'
3161 and attribute2 like 'TIME+%';
3162 */
3163
3164 /* Original API removed by Arun for bug 4482933, this is replaced by the simpler api logic that follows*/
3165
3166 function get_bsc_Periodicity(
3167 p_time_level_name varchar2,
3168 x_periodicity_id out nocopy number,
3169 x_calendar_id out nocopy number,
3170 x_message out nocopy varchar2
3171 )return boolean is
3172 --
3173 cursor c1 is
3174 select periodicity_id, calendar_id from bsc_sys_periodicities where short_name= p_time_level_name;
3175 --
3176 Begin
3177
3178 open c1;
3179 fetch c1 into x_periodicity_id, x_calendar_id;
3180 close c1;
3181 if (x_periodicity_id is not null) then
3182 return true;
3183 else
3184 return false;
3185 end if;
3186 Exception when others then
3187 x_message:=sqlerrm;
3188 return false;
3189 End;
3190
3191
3192 --for PMV
3193 procedure get_bsc_Periodicity_jdbc(
3194 p_time_level_name varchar2,
3195 x_periodicity_id out nocopy number,
3196 x_calendar_id out nocopy number,
3197 x_status out nocopy number,
3198 x_message out nocopy varchar2
3199 ) is
3200 BEGIN
3201 if (get_bsc_Periodicity ( p_time_level_name,
3202 x_periodicity_id ,
3203 x_calendar_id ,
3204 x_message )) then
3205 x_status := 0;
3206 else
3207 x_status := 1;
3208 end if;
3209 Exception when others then
3210 x_message:=sqlerrm;
3211 x_status := 1;
3212 END;
3213
3214 function is_dbi_cal_metadata_loaded return boolean is
3215 cursor c1 is select 1 from bsc_sys_calendars_b where edw_calendar_type_id=1 and edw_calendar_id =1001;
3216 l_res number;
3217 Begin
3218 open c1;
3219 fetch c1 into l_res;
3220 close c1;
3221 if l_res=1 then
3222 return true;
3223 else
3224 return false;
3225 end if;
3226 Exception when others then
3227 raise;
3228 End;
3229
3230 function check_for_dbi return boolean is
3231 cursor c1 is select 1 from user_objects where object_name='FII_TIME_DAY';
3232 l_sql varchar2(2000);
3233 TYPE CurTyp IS REF CURSOR;
3234 cv CurTyp;
3235 l_res number;
3236 Begin
3237 open c1;
3238 fetch c1 into l_res;
3239 close c1;
3240 if l_res=1 then
3241 if g_debug then
3242 write_to_log_file_n('DBI Implemented');
3243 end if;
3244
3245 -- Fix bug#4027766: If fii_time_day is empty we return false. nothing to import
3246 l_sql:='select 1 from fii_time_day where rownum=1';
3247 open cv for l_sql;
3248 fetch cv into l_res;
3249 if cv%notfound then
3250 close cv;
3251 if g_debug then
3252 write_to_log_file_n('fii_time_day is empty. DBI NOT Implemented');
3253 end if;
3254 return false;
3255 else
3256 close cv;
3257 if g_debug then
3258 write_to_log_file_n('fii_time_day has data. DBI Implemented');
3259 end if;
3260 return true;
3261 end if;
3262 else
3263 if g_debug then
3264 write_to_log_file_n('DBI NOT Implemented');
3265 end if;
3266 return false;
3267 end if;
3268 Exception when others then
3269 raise;
3270 End;
3271
3272 --AW_INTEGRATION: New procedure
3273 procedure load_dbi_calendars_into_aw is
3274 --
3275 cursor c1 is select calendar_id from bsc_sys_calendars_b where edw_calendar_type_id = 1;
3276 l_calendar_id number;
3277 --
3278 l_dim varchar2(200);
3279 l_oo bsc_aw_md_wrapper.bsc_olap_object_tb;
3280 Begin
3281 if g_debug then
3282 write_to_log_file_n('In load_dbi_calendars_into_aw '||get_time);
3283 write_to_log_file_n('cursor c1 is select calendar_id from bsc_sys_calendars_b where edw_calendar_type_id = 1;'||get_time);
3284 end if;
3285 open c1;
3286 loop
3287 fetch c1 into l_calendar_id;
3288 exit when c1%notfound;
3289 /*5350867. first, do not create the calendar here. if calendar is already installed, only then do we try to refresh it */
3290 l_dim:=bsc_aw_calendar.get_calendar_name(l_calendar_id);
3291 l_oo.delete;
3292 bsc_aw_md_api.get_bsc_olap_object(l_dim,'dimension',l_dim,'dimension',l_oo);
3293 if l_oo.count>0 then
3294 /*bsc_aw_calendar.create_calendar(p_calendar => l_calendar_id,p_options => 'DEBUG LOG, RECREATE');
3295 do not create calendar here
3296 */
3297 bsc_aw_calendar.load_calendar(p_calendar => l_calendar_id,p_options => 'DEBUG LOG');
3298 end if;
3299 end loop;
3300 close c1;
3301 if g_debug then
3302 write_to_log_file_n('Done load_dbi_calendars_into_aw '||get_time);
3303 end if;
3304 Exception when others then
3305 g_status_message:=sqlerrm;
3306 write_to_log_file_n('Error in load_dbi_calendars_into_aw '||g_status_message||get_time);
3307 raise;
3308 End;
3309
3310 END BSC_DBI_CALENDAR;
[Home] [Help]