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