[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_kpi_xtd.delete;
cursor c1 is SELECT TABLE_NAME FROM ALL_TABLES WHERE TABLE_NAME LIKE 'BSC_RPT_KEYS_%' AND TEMPORARY='Y' AND OWNER = BSC_APPS.get_user_schema;
delete bsc_rpt_keys;
g_kpi_xtd.delete;
procedure delete_rpt_keys(
p_table_name varchar2,
p_session_id number,
p_error_message out nocopy varchar2
)is
Begin
if g_file and g_debug then
BSC_im_utils.write_to_log_file_n('delete '||p_table_name||' where session_id='||p_session_id||get_time);
execute immediate 'delete '||p_table_name||' where session_id=:1' using p_session_id;
BSC_im_utils.write_to_log_file_n('Deleted '||sql%rowcount||' rows '||get_time);
g_kpi_xtd.delete;
select bsc_kpi_periodicities.periodicity_id,calendar_id,period_type_id,db_column_name,num_of_periods
from bsc_kpi_periodicities ,bsc_sys_periodicities
where indicator=p_kpi and bsc_kpi_periodicities.periodicity_id=bsc_sys_periodicities.periodicity_id
order by indicator,display_order desc;
select xtd_pattern from bsc_sys_periodicities where periodicity_id=p_xtd_periodicity;
select decode(edw_calendar_id,1001,decode(edw_calendar_type_id,1,'DBI-ENT',null),null) from bsc_sys_calendars_b
where calendar_id=p_calendar_id;
needs day to date. if daily periodicity, we simply insert one row for this day.
open issue: is this functionality needed for the lowest level periodicity. say a kpi is monthly, quarterly, yearly. user chooses
MTD. do they expect to see non xtd value, ie, just the month aggregation? for now, we do this only for daily periodicity
*/
if is_daily_periodicity(p_xtd_periodicity) then
populate_rpt_keys_daily(p_table_name,p_session_id,l_calendar_id,p_report_date,p_num_report_date,p_xtd_period,p_xtd_year,p_xtd_periodicity,p_xtd_type);
if insert_rpt_cal_keys(
p_table_name,
p_report_date(i),
p_report_date(i),--this is inserted into report_date col in bsc_rpt_keys_table
p_xtd_periodicity,
p_xtd_period(i),
p_xtd_year(i),
l_hier,
l_xtd_pattern_value,
l_calendar_id,
'N',
l_periodicity_missing,
ll_temp_period_periodicity,
ll_temp_period_missing,
ll_num_temp_pattern_period
)=false then
return false;
select period_type_id into l_period_type_id from bsc_sys_periodicities where periodicity_id=p_periodicity;
l_stmt:='insert into bsc_rpt_keys(session_id,report_date,xtd_periodicity,'||
'xtd_period,xtd_year,period,year,period_type_id,periodicity_id,period_flag,day_count,rolling_flag,last_update_date) '||
'select :1,:2,:3,:4,:5,max(period),max(year),1,:6,1,1,''N'',sysdate from bsc_reporting_calendar where '||
'calendar_id=:7 and report_date=:8 and period_type_id=1 and rolling_flag=''N''';
BSC_im_utils.write_to_log_file_n('Inserted(Daily) '||sql%rowcount||' rows '||get_time);
function insert_rpt_cal_keys(
p_table_name varchar2,
p_report_date date,--used to join with bsc_reporting_calendar
p_report_date_insert date,--used to insert into bsc_rpt_keys table
p_xtd_periodicity number,
p_xtd_period number,
p_xtd_year number,
p_hier varchar2,
p_xtd_pattern number,
p_calendar_id number,
p_roll_flag varchar2,
p_periodicity_missing boolean,
p_period_periodicity number_tabletype,
p_period_missing boolean_tabletype,
p_num_pattern_period number
)return boolean is
----------
l_status number;
BSC_im_utils.write_to_log_file_n('In insert_rpt_cal_keys p_xtd_periodicity='||
p_xtd_periodicity||',p_hier='||p_hier||
',p_xtd_pattern='||p_xtd_pattern||',p_calendar_id='||p_calendar_id||',p_roll_flag='||p_roll_flag||get_time);
BSC_im_utils.write_to_log_file(p_report_date||' '||p_report_date_insert||' '||p_xtd_period||' '||p_xtd_year);
BSC_im_utils.write_to_log_file_n('insert into bsc_rpt_keys(session_id,report_date,'||
'xtd_periodicity,xtd_period,xtd_year,period,year,period_type_id,periodicity_id,day_count,rolling_flag,last_update_date) '||
'select '||g_session_id||',p_report_date_insert,'||p_xtd_periodicity||',p_xtd_period,p_xtd_year,'||
'period,year,period_type_id,periodicity_id,day_count,rolling_flag,sysdate from bsc_reporting_calendar where '||
'calendar_id='||p_calendar_id||' and report_date=p_report_date and hierarchy='||p_hier||
'and rolling_flag='||p_roll_flag||' and bitand(record_type_id,'||p_xtd_pattern||')=record_type_id');
BSC_im_utils.write_to_log_file(p_report_date||' '||p_report_date_insert||' '||p_xtd_period||' '||p_xtd_year);
execute immediate 'insert into bsc_rpt_keys(session_id,report_date,xtd_periodicity,'||
'xtd_period,xtd_year,period,year,period_type_id,periodicity_id,period_flag,day_count,rolling_flag,last_update_date) '||
'select :1,:2,:3,:4,:5,period,year,period_type_id,periodicity_id,0,day_count,rolling_flag,'||
'sysdate from bsc_reporting_calendar where '||
'calendar_id=:6 and report_date=:7 and hierarchy=:8 and rolling_flag=:9 '||
'and bitand(record_type_id,:10)=record_type_id'
using g_session_id,p_report_date_insert,p_xtd_periodicity,p_xtd_period,p_xtd_year,p_calendar_id,
p_report_date,p_hier,p_roll_flag,p_xtd_pattern;
BSC_im_utils.write_to_log_file_n('Inserted '||sql%rowcount||' rows '||get_time);
l_stmt:='update '||p_table_name||' set period_flag=1 '||
'where session_id=:1 '||
'and report_date=:2 '||
'and xtd_periodicity=:3 '||
'and period_type_id=1 '||
'and rolling_flag=:4 '||
'and period=(select max(period) '||
'from '||p_table_name||' where session_id=:5 '||
'and report_date=:6 '||
'and xtd_periodicity=:7 and period_type_id=1 and rolling_flag=:8)';
BSC_im_utils.write_to_log_file_n(l_stmt||' using '||g_session_id||',p_report_date_insert,'||p_xtd_periodicity||
p_roll_flag||','||g_session_id||',p_report_date_insert,'||p_xtd_periodicity||','||p_roll_flag);
execute immediate l_stmt using g_session_id,p_report_date_insert,p_xtd_periodicity,p_roll_flag,
g_session_id,p_report_date_insert,p_xtd_periodicity,p_roll_flag;
BSC_im_utils.write_to_log_file_n('Updated '||sql%rowcount||' rows '||get_time);
p_report_date_insert,
p_xtd_periodicity,
p_period_periodicity(i),--missing
p_period_periodicity(j),--present
p_calendar_id,
p_roll_flag)=false then
--'N')=false then --3919980
return false;
select period_type_id,periodicity_id,db_column_name from bsc_sys_periodicities where periodicity_id=p_periodicity_id;
l_stmt:='insert into bsc_rpt_keys(session_id,report_date,xtd_periodicity,xtd_period,xtd_year,period,year,'||
--'period_type_id,periodicity_id,period_flag,period_day_count,last_update_date) '||
'period_type_id,periodicity_id,period_flag,day_count,rolling_flag,last_update_date) '||
'select rpt.session_id,rpt.report_date,rpt.xtd_periodicity,'||
'rpt.xtd_period,rpt.xtd_year,cal.'||l_db_column_present||',rpt.year,:2,:3,0,cal.day_count,rpt.rolling_flag,:4 from '||
'(select '||l_db_column_present||','||l_db_column_missing||',year,count(*) day_count from bsc_db_calendar where '||
'calendar_id=:5 group by '||l_db_column_present||','||l_db_column_missing||',year) cal,'||
p_table_name||' rpt where cal.'||l_db_column_missing||'='||
'rpt.period and cal.year=rpt.year and rpt.period_type_id=:6 and rpt.report_date=:7 and rpt.session_id=:8 and '||
'rpt.xtd_periodicity=:9 and rpt.rolling_flag=:10';
BSC_im_utils.write_to_log_file_n('Inserted '||sql%rowcount||' rows '||get_time);
l_stmt:='delete '||p_table_name||' where session_id=:1 and report_date=:2 and xtd_periodicity=:3 and '||
'period_type_id=:4 and rolling_flag=:5';
BSC_im_utils.write_to_log_file_n('Deleted '||sql%rowcount||' rows '||get_time);
if insert_rpt_cal_keys(
p_table_name,
l_roll_date,
p_report_date(i),--this is inserted in report_date col of bsc_rpt_keys table
p_xtd_periodicity,
p_xtd_period(i),
p_xtd_year(i),
p_hier,
p_xtd_pattern,
p_calendar_id,
'Y',--'Y'
p_periodicity_missing,
p_period_periodicity,
p_period_missing,
p_num_pattern_period
)=false then
return false;
delete_table(p_table_name,p_session_id,p_report_date(i),'N');
BSC_im_utils.write_to_log_file_n('Complex...XTD + Delete OR RTD + Delete');
l_stmt:='delete '||p_table_name||' where period_type_id=32 and rolling_flag=''Y'' and period in '||
'(select max(period) from '||p_table_name||' where period_type_id=32 and rolling_flag=''Y'')';
l_stmt:='delete '||p_table_name||' where period_type_id=32 and rolling_flag=''Y'' and period in '||
'(select max(period) from '||p_table_name||' where period_type_id=32 and rolling_flag=''Y'')';
l_stmt:='delete '||p_table_name||' where period_type_id=32 and rolling_flag=''N'' and period in '||
'(select min(period) from '||p_table_name||' where period_type_id=32 and rolling_flag=''N'')';
/*4968072 : the earlier logic of inserting
for i in 1..(p_reqd_count-p_current_count) loop
execute immediate l_stmt using l_period+i...
is not correct. consider jan 2 2006. we want rolling wtd. week starts on jan 2. jan 2 - 6 gives dec 27. we do wtd as of jan 2 and
then rwtd as of dec 27. we miss out jan 1. earlier logic was assuming that we are considering the case where week in 2005 is ending before
dec 31.
best soln is to have a generic day filler given 2 dates. this can be expensive because we have to go to db calendar
this api is called only in the year boundary for weeks on rolling mtd for mar 1 (missing out feb completely). so the api will be specific
--
after thinking through, this algo will be implented
xtd date =A and Rtd=B
--|-----|-----||----|----|---
B Bs YB As A
or
--|-----|---------|----|---
B Bs As A
or
--|-----|--------|---
B Bs=As A
Bs=start period for B. As=start period for A.
we get daycount for A. subtract to get As. we get daycount for B, add to get Bs
we follow this logic
Bs=Bs+1
if Bs=As
exit
end if
end loop
end if
This makes the api absolutely generic. given 2 dates, it fill any missing values
*/
procedure correct_rolling_data(
p_table_name varchar2,
p_session_id number,
p_xtd_report_date date,
p_rtd_report_date date,
p_xtd_periodicity number,
p_xtd_period number,
p_xtd_year number,
p_hier varchar2,
p_xtd_pattern number,
p_calendar_id number,
p_periodicity_missing boolean,
p_period_periodicity number_tabletype,
p_period_missing boolean_tabletype,
p_num_pattern_period number
) is
--
l_prev_day_count number;
if insert_rpt_cal_keys(
p_table_name,
l_Bs_date,
p_xtd_report_date,--this is inserted in report_date col of bsc_rpt_keys table
p_xtd_periodicity,
p_xtd_period,
p_xtd_year,
p_hier,
p_xtd_pattern,
p_calendar_id,
'Y',
p_periodicity_missing,
p_period_periodicity,
p_period_missing,
p_num_pattern_period
)=false then
raise g_exception;
l_stmt:='select sum(day_count) from '||p_table_name||' where session_id=:1 and report_date=:2 and rolling_flag=:3';
procedure delete_table(
p_table_name varchar2,
p_session_id number,
p_report_date date,
p_roll_flag varchar2) is
--
l_stmt varchar2(5000);
l_stmt:='delete '||p_table_name||' where session_id=:1 and report_date=:2 and rolling_flag=:3';
BSC_im_utils.write_to_log_file_n('Deleted '||sql%rowcount||' rows ');