[Home] [Help]
15: g_kpi_xtd.delete;
16: end if;
17: g_num_kpi_xtd:=0;
18: --Changed by Arun, 10/14/2005 due to perf. concerns raised by Mandar, Bug 4676527
19: p_table_name := 'bsc_rpt_keys';
20:
21: Exception when others then
22: if sqlcode=-00955 then
23: --do nothing
36: p_user_id number,
37: p_error_message out nocopy varchar2
38: )is
39: l_stmt varchar2(1000);
40: 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;
41: l_table varchar2(100);
42: Begin
43: -- Changed by Arun for bug/enh 4676527
44: delete bsc_rpt_keys;
40: 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;
41: l_table varchar2(100);
42: Begin
43: -- Changed by Arun for bug/enh 4676527
44: delete bsc_rpt_keys;
45: --clear cache, added for bug 4652655
46: if g_num_kpi_xtd is not null and g_num_kpi_xtd>0 then
47: g_kpi_xtd.delete;
48: end if;
709: for i in 1..p_num_report_date loop
710: if insert_rpt_cal_keys(
711: p_table_name,
712: p_report_date(i),
713: p_report_date(i),--this is inserted into report_date col in bsc_rpt_keys_table
714: p_xtd_periodicity,
715: p_xtd_period(i),
716: p_xtd_year(i),
717: l_hier,
763: g_kpi_xtd(g_num_kpi_xtd).xtd_periodicity:=p_xtd_periodicity;
764: /*
765: here we are not caching if its xtd or rolling xtd. we dont want to have a situation
766: where there is rolling xtd and xtd for the same as of date
767: pmv is not filtering on the bsc_rpt_keys table with rolling_flag
768: so if pmv wants to go from rolling xtd on a as of date to xtd on the same as of date, they
769: must first clean up the table
770: */
771: end loop;
819: ) is
820: l_stmt varchar2(10000);
821: Begin
822: for i in 1..p_num_report_date loop
823: l_stmt:='insert into bsc_rpt_keys(session_id,report_date,xtd_periodicity,'||
824: 'xtd_period,xtd_year,period,year,period_type_id,periodicity_id,period_flag,day_count,rolling_flag,last_update_date) '||
825: 'select :1,:2,:3,:4,:5,max(period),max(year),1,:6,1,1,''N'',sysdate from bsc_reporting_calendar where '||
826: 'calendar_id=:7 and report_date=:8 and period_type_id=1 and rolling_flag=''N''';
827: if g_file and g_debug then
844:
845: function insert_rpt_cal_keys(
846: p_table_name varchar2,
847: p_report_date date,--used to join with bsc_reporting_calendar
848: p_report_date_insert date,--used to insert into bsc_rpt_keys table
849: p_xtd_periodicity number,
850: p_xtd_period number,
851: p_xtd_year number,
852: p_hier varchar2,
889: end if;
890: if l_status is null then
891: --need to load into the cal keys table
892: if g_file and g_debug then
893: BSC_im_utils.write_to_log_file_n('insert into bsc_rpt_keys(session_id,report_date,'||
894: 'xtd_periodicity,xtd_period,xtd_year,period,year,period_type_id,periodicity_id,day_count,rolling_flag,last_update_date) '||
895: 'select '||g_session_id||',p_report_date_insert,'||p_xtd_periodicity||',p_xtd_period,p_xtd_year,'||
896: 'period,year,period_type_id,periodicity_id,day_count,rolling_flag,sysdate from bsc_reporting_calendar where '||
897: 'calendar_id='||p_calendar_id||' and report_date=p_report_date and hierarchy='||p_hier||
899: BSC_im_utils.write_to_log_file(p_report_date||' '||p_report_date_insert||' '||p_xtd_period||' '||p_xtd_year);
900: end if;
901: --need to make compatible with 8i. cannot have forall stmt. 8i does not allow execute immediate inside forall
902: --forall i in 1..p_num_report_date
903: execute immediate 'insert into bsc_rpt_keys(session_id,report_date,xtd_periodicity,'||
904: 'xtd_period,xtd_year,period,year,period_type_id,periodicity_id,period_flag,day_count,rolling_flag,last_update_date) '||
905: 'select :1,:2,:3,:4,:5,period,year,period_type_id,periodicity_id,0,day_count,rolling_flag,'||
906: 'sysdate from bsc_reporting_calendar where '||
907: 'calendar_id=:6 and report_date=:7 and hierarchy=:8 and rolling_flag=:9 '||
1027: BSC_im_utils.write_to_log_file(l_pid_missing||' '||l_periodicity_missing||' '||l_db_column_missing);
1028: BSC_im_utils.write_to_log_file_n('Present period type id and db column name');
1029: BSC_im_utils.write_to_log_file(l_pid_present||' '||l_periodicity_present||' '||l_db_column_present);
1030: end if;
1031: l_stmt:='insert into bsc_rpt_keys(session_id,report_date,xtd_periodicity,xtd_period,xtd_year,period,year,'||
1032: --'period_type_id,periodicity_id,period_flag,period_day_count,last_update_date) '||
1033: 'period_type_id,periodicity_id,period_flag,day_count,rolling_flag,last_update_date) '||
1034: 'select rpt.session_id,rpt.report_date,rpt.xtd_periodicity,'||
1035: 'rpt.xtd_period,rpt.xtd_year,cal.'||l_db_column_present||',rpt.year,:2,:3,0,cal.day_count,rpt.rolling_flag,:4 from '||
1179: --the periodicities, daily,weekly,montly,quarterly and yearly
1180: if insert_rpt_cal_keys(
1181: p_table_name,
1182: l_roll_date,
1183: p_report_date(i),--this is inserted in report_date col of bsc_rpt_keys table
1184: p_xtd_periodicity,
1185: p_xtd_period(i),
1186: p_xtd_year(i),
1187: p_hier,
1388: loop
1389: if insert_rpt_cal_keys(
1390: p_table_name,
1391: l_Bs_date,
1392: p_xtd_report_date,--this is inserted in report_date col of bsc_rpt_keys table
1393: p_xtd_periodicity,
1394: p_xtd_period,
1395: p_xtd_year,
1396: p_hier,