[Home] [Help]
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
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
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;
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;
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: */
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: --
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;
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;
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;
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,
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)
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)
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)
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)
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)
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);
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,
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)
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)
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)
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);
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)
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)
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)
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)
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);
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: /*
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
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;
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;
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;
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;