[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
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);
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;
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.
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
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,
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
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
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;
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;
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
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);
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;
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;
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;
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;
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;
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;
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;
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;*/
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;
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;
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;
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;
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;
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;
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,
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
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;
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)
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,
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
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;
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)
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);
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;
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)
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
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;
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)
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
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;
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);
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,
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
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
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)
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
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;
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)
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
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;
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)
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
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;
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);
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,
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
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;
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)
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
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;
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)
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
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;
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)
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
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;
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);
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;
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;
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;