[Home] [Help]
4254: p_calendar:=p_calendar||l_cal_column(i)||',';
4255: end if;
4256: end loop;
4257: p_calendar:=substr(p_calendar,1,length(p_calendar)-1);
4258: p_calendar:=p_calendar||' from BSC_DB_CALENDAR ';
4259: if l_calendar_id is not null then
4260: p_calendar:=p_calendar||'where calendar_id='||l_calendar_id;
4261: end if;
4262: p_calendar:=p_calendar||')';
4259: if l_calendar_id is not null then
4260: p_calendar:=p_calendar||'where calendar_id='||l_calendar_id;
4261: end if;
4262: p_calendar:=p_calendar||')';
4263: p_calendar_tables:='BSC_DB_CALENDAR';
4264: p_calendar_alias:='BSC_DB_CALENDAR';
4265: p_calendar_join_1:=l_cal_column(1);
4266: p_calendar_join_2:='YEAR';
4267: return true;
4260: p_calendar:=p_calendar||'where calendar_id='||l_calendar_id;
4261: end if;
4262: p_calendar:=p_calendar||')';
4263: p_calendar_tables:='BSC_DB_CALENDAR';
4264: p_calendar_alias:='BSC_DB_CALENDAR';
4265: p_calendar_join_1:=l_cal_column(1);
4266: p_calendar_join_2:='YEAR';
4267: return true;
4268: Exception when others then
4438: 'period_type_id number,period number,year number) on commit preserve rows';
4439: BSC_APPS.Do_DDL(l_stmt, AD_DDL.CREATE_TABLE, l_temp_table);*/
4440: ----------------------------------------
4441: ------------------------
4442: /*for each calendar, get the periodicities to fetch from bsc_db_calendar
4443: find out the relations between the periodicities, see if they are the std
4444: periodicities. if yes, assign the period_type_id etc. if custom, generate
4445: the period_type_id etc. then come up with the hierarchies to load into the
4446: reporting calendar.
4577: 'period_type_id number,period number,year number) on commit preserve rows';
4578: BSC_APPS.Do_DDL(l_stmt, AD_DDL.CREATE_TABLE, l_temp_table);*/
4579: ----------------------------------------
4580: ------------------------
4581: /*for this calendar, get the periodicities to fetch from bsc_db_calendar
4582: find out the relations between the periodicities, see if they are the std
4583: periodicities. if yes, assign the period_type_id etc. if custom, generate
4584: the period_type_id etc. then come up with the hierarchies to load into the
4585: reporting calendar.
4666: cursor c1 (p_calendar_id number)
4667: is select calendar_year,calendar_month,calendar_day,year,semester,bimester,quarter,month,
4668: week52,day365,custom_1,custom_2,custom_3,custom_4,custom_5,custom_6,custom_7,
4669: custom_8,custom_9,custom_10,custom_11,custom_12,custom_13,custom_14,custom_15,
4670: custom_16,custom_17,custom_18,custom_19,custom_20 from bsc_db_calendar where calendar_id=p_calendar_id
4671: order by calendar_year,calendar_month,calendar_day;
4672: Begin
4673: if g_debug then
4674: write_to_log_file_n('select calendar_year,calendar_month,calendar_day,year,semester,bimester,quarter,month, '||
4673: if g_debug then
4674: write_to_log_file_n('select calendar_year,calendar_month,calendar_day,year,semester,bimester,quarter,month, '||
4675: 'week52,day365,custom_1,custom_2,custom_3,custom_4,custom_5,custom_6,custom_7, '||
4676: 'custom_8,custom_9,custom_10,custom_11,custom_12,custom_13,custom_14,custom_15, '||
4677: 'custom_16,custom_17,custom_18,custom_19,custom_20 from bsc_db_calendar where calendar_id='||p_calendar_id||
4678: 'order by calendar_year,calendar_month,calendar_day');
4679: end if;
4680: p_number_calendar_data:=1;
4681: p_calendar_data:=cal_record_table();
5327: for i in 1..l_number_periods loop
5328: l_stmt:=l_stmt||','||l_period_column(i);
5329: end loop;
5330: l_stmt:=l_stmt||'
5331: from bsc_db_calendar where calendar_id='||p_calendar_id||' order by calendar_year,calendar_month,calendar_day;'||
5332: newline;
5333: --build the variables that will be something like quarter for YTD, month for YTD and day for YTD etc
5334: for i in 1..l_number_periods loop
5335: l_stmt:=l_stmt||'l_'||l_period_column(1)||'_'||l_period_column(i)||' BSC_IM_UTILS.number_tabletype;'||newline;