[Home] [Help]
88: end;
89:
90: function get_measures_by_table(
91: p_table_name varchar2,
92: p_measures BSC_IM_UTILS.varchar_tabletype,
93: p_number_measures number
94: ) return varchar2 is
95: cursor c1 is select column_name from bsc_db_tables_cols where table_name = p_table_name;
96: l_column_name VARCHAR2(100);
150:
151:
152: function load_metadata_for_indicators(
153: p_indicator varchar2,
154: p_options BSC_IM_UTILS.varchar_tabletype,
155: p_number_options number
156: )return boolean is
157: l_indicators BSC_IM_UTILS.number_tabletype;
158: l_number_indicators number;
153: p_indicator varchar2,
154: p_options BSC_IM_UTILS.varchar_tabletype,
155: p_number_options number
156: )return boolean is
157: l_indicators BSC_IM_UTILS.number_tabletype;
158: l_number_indicators number;
159: l_final_dimensions BSC_IM_UTILS.varchar_tabletype;
160: l_number_final_dimensions number;
161: Begin
155: p_number_options number
156: )return boolean is
157: l_indicators BSC_IM_UTILS.number_tabletype;
158: l_number_indicators number;
159: l_final_dimensions BSC_IM_UTILS.varchar_tabletype;
160: l_number_final_dimensions number;
161: Begin
162: g_options:=p_options;
163: g_number_options:=p_number_options;
171: return false;
172: end if;
173: return true;
174: Exception when others then
175: BSC_IM_UTILS.g_status_message:=sqlerrm;
176: g_status_message:=sqlerrm;
177: write_to_log_file_n('Exception in load_metadata_for_indicators '||sqlerrm);
178: return false;
179: End;
178: return false;
179: End;
180:
181: function read_metadata(
182: p_indicators BSC_IM_UTILS.number_tabletype,
183: p_number_indicators number,
184: p_final_dimensions out nocopy BSC_IM_UTILS.varchar_tabletype,
185: p_number_final_dimensions out nocopy number
186: ) return boolean is
180:
181: function read_metadata(
182: p_indicators BSC_IM_UTILS.number_tabletype,
183: p_number_indicators number,
184: p_final_dimensions out nocopy BSC_IM_UTILS.varchar_tabletype,
185: p_number_final_dimensions out nocopy number
186: ) return boolean is
187: l_indicator_ids BSC_IM_UTILS.number_tabletype;
188: l_indicators BSC_IM_UTILS.varchar_tabletype;
183: p_number_indicators number,
184: p_final_dimensions out nocopy BSC_IM_UTILS.varchar_tabletype,
185: p_number_final_dimensions out nocopy number
186: ) return boolean is
187: l_indicator_ids BSC_IM_UTILS.number_tabletype;
188: l_indicators BSC_IM_UTILS.varchar_tabletype;
189: Begin
190: for i in 1..p_number_indicators loop
191: l_indicator_ids(i):=p_indicators(i);
184: p_final_dimensions out nocopy BSC_IM_UTILS.varchar_tabletype,
185: p_number_final_dimensions out nocopy number
186: ) return boolean is
187: l_indicator_ids BSC_IM_UTILS.number_tabletype;
188: l_indicators BSC_IM_UTILS.varchar_tabletype;
189: Begin
190: for i in 1..p_number_indicators loop
191: l_indicator_ids(i):=p_indicators(i);
192: l_indicators(i):=p_indicators(i);
207: return false;
208: end if;
209: return true;
210: Exception when others then
211: BSC_IM_UTILS.g_status_message:=sqlerrm;
212: g_status_message:=sqlerrm;
213: write_to_log_file_n('Exception in read_metadata '||sqlerrm);
214: return false;
215: End;
214: return false;
215: End;
216:
217: function read_kpi_required(
218: p_indicators BSC_IM_UTILS.number_tabletype,
219: p_indicator_names BSC_IM_UTILS.varchar_tabletype,
220: p_number_indicators number
221: ) return boolean is
222: -----------------------------------------------------------------
215: End;
216:
217: function read_kpi_required(
218: p_indicators BSC_IM_UTILS.number_tabletype,
219: p_indicator_names BSC_IM_UTILS.varchar_tabletype,
220: p_number_indicators number
221: ) return boolean is
222: -----------------------------------------------------------------
223: l_stmt varchar2(5000);
220: p_number_indicators number
221: ) return boolean is
222: -----------------------------------------------------------------
223: l_stmt varchar2(5000);
224: l_periodicity BSC_IM_UTILS.number_tabletype;
225: l_number_periodicity number;
226: -----------------------------------------------------------------
227: Begin
228: write_to_debug_n('In read_kpi_required '||get_time);
240: end if;
241: end loop;
242: return true;
243: Exception when others then
244: BSC_IM_UTILS.g_status_message:=sqlerrm;
245: g_status_message:=sqlerrm;
246: write_to_log_file_n('Exception in read_kpi_required '||sqlerrm);
247: return false;
248: End;
251: this api for XTD supported MV
252: */
253: function read_kpi_map_info(
254: p_indicator number,
255: p_periodicity BSC_IM_UTILS.number_tabletype,
256: p_number_periodicity number
257: )return boolean is
258: -----------------------------------------------------------------
259: l_map_name varchar2(200);
262: -----------------------------------------------------------------
263: l_mv_name varchar2(200);
264: l_zero_code_mv_name varchar2(200);
265: -----------------------------------------------------------------
266: l_s_tables BSC_IM_UTILS.varchar_tabletype;
267: l_periodicity BSC_IM_UTILS.number_tabletype;
268: l_number_s_tables number;
269: -----------------------------------------------------------------
270: l_distinct_list BSC_IM_UTILS.varchar_tabletype;
263: l_mv_name varchar2(200);
264: l_zero_code_mv_name varchar2(200);
265: -----------------------------------------------------------------
266: l_s_tables BSC_IM_UTILS.varchar_tabletype;
267: l_periodicity BSC_IM_UTILS.number_tabletype;
268: l_number_s_tables number;
269: -----------------------------------------------------------------
270: l_distinct_list BSC_IM_UTILS.varchar_tabletype;
271: l_number_distinct_list number;
266: l_s_tables BSC_IM_UTILS.varchar_tabletype;
267: l_periodicity BSC_IM_UTILS.number_tabletype;
268: l_number_s_tables number;
269: -----------------------------------------------------------------
270: l_distinct_list BSC_IM_UTILS.varchar_tabletype;
271: l_number_distinct_list number;
272: -----------------------------------------------------------------
273: l_temp_var BSC_IM_UTILS.varchar_tabletype;
274: l_number_temp_var number;
269: -----------------------------------------------------------------
270: l_distinct_list BSC_IM_UTILS.varchar_tabletype;
271: l_number_distinct_list number;
272: -----------------------------------------------------------------
273: l_temp_var BSC_IM_UTILS.varchar_tabletype;
274: l_number_temp_var number;
275: -----------------------------------------------------------------
276: ll_s_tables BSC_IM_UTILS.varchar_tabletype;
277: ll_number_s_tables number;
272: -----------------------------------------------------------------
273: l_temp_var BSC_IM_UTILS.varchar_tabletype;
274: l_number_temp_var number;
275: -----------------------------------------------------------------
276: ll_s_tables BSC_IM_UTILS.varchar_tabletype;
277: ll_number_s_tables number;
278: -----------------------------------------------------------------
279:
280: -- added 02/13/2006 by Arun
287: --ignoring xtd pattern due to PMD bug 4503527 not having an upgrade script yet
288: --or xtd_pattern is null )
289: and periodicity_type not in (11,12);
290: l_dummy number;
291: p_options BSC_IM_UTILS.varchar_tabletype;
292: l_calendar_id number;
293:
294: Begin
295: if g_debug then
333: --l_base_tables are the B tables
334: --for each S table, find the set (all periodicities)
335: --SB tables are considered in the same league as the S tables
336: --MV are created for the SB tables.
337: l_lang:=BSC_IM_UTILS.get_lang;
338: ----------------------
339: --create the metadata for KPI
340: if BSC_IM_INT_MD.create_cube(p_indicator,p_indicator,0,'BSC',p_indicator,null)=false then
341: return false;
345: return false;
346: end if;
347: l_number_distinct_list:=0;
348: for i in 1..l_number_s_tables loop
349: if BSC_IM_UTILS.in_array(l_distinct_list,l_number_distinct_list,substr(l_s_tables(i),1,
350: instr(l_s_tables(i),'_',-1)-1))=false then
351: l_number_distinct_list:=l_number_distinct_list+1;
352: l_distinct_list(l_number_distinct_list):=substr(l_s_tables(i),1,instr(l_s_tables(i),'_',-1)-1);
353: end if;
419: write_to_log_file_n('Returning from read_kpi_map_info '||get_time);
420: end if;
421: return true;
422: Exception when others then
423: BSC_IM_UTILS.g_status_message:=sqlerrm;
424: g_status_message:=sqlerrm;
425: write_to_log_file_n('Exception in read_kpi_map_info '||sqlerrm);
426: return false;
427: End;
426: return false;
427: End;
428:
429: --bug 3867313
430: function get_distinct_list(p_parameter IN BSC_IM_UTILS.varchar_tabletype, l_number_parameters IN NUMBER) return number is
431: l_final_list BSC_IM_UTILS.varchar_tabletype;
432: l_final_counter number := 0;
433: bFound boolean := false;
434: begin
427: End;
428:
429: --bug 3867313
430: function get_distinct_list(p_parameter IN BSC_IM_UTILS.varchar_tabletype, l_number_parameters IN NUMBER) return number is
431: l_final_list BSC_IM_UTILS.varchar_tabletype;
432: l_final_counter number := 0;
433: bFound boolean := false;
434: begin
435: for i in 1..l_number_parameters loop
453: p_map_name varchar2,
454: p_mv_name varchar2,
455: p_zero_code_mv_name varchar2,
456: p_zero_code_map_name varchar2,
457: p_s_tables BSC_IM_UTILS.varchar_tabletype,
458: p_number_s_tables number
459: )return boolean is
460: ---------------s table info-------------------------------------
461: l_fk BSC_IM_UTILS.varchar_tabletype;
457: p_s_tables BSC_IM_UTILS.varchar_tabletype,
458: p_number_s_tables number
459: )return boolean is
460: ---------------s table info-------------------------------------
461: l_fk BSC_IM_UTILS.varchar_tabletype;
462: l_number_fk number;
463: l_measures BSC_IM_UTILS.varchar_tabletype;
464: l_number_measures number;
465: ---------------src table info-------------------------------------
459: )return boolean is
460: ---------------s table info-------------------------------------
461: l_fk BSC_IM_UTILS.varchar_tabletype;
462: l_number_fk number;
463: l_measures BSC_IM_UTILS.varchar_tabletype;
464: l_number_measures number;
465: ---------------src table info-------------------------------------
466: l_tables BSC_IM_UTILS.varchar_tabletype;
467: l_source_tables BSC_IM_UTILS.varchar_tabletype;
462: l_number_fk number;
463: l_measures BSC_IM_UTILS.varchar_tabletype;
464: l_number_measures number;
465: ---------------src table info-------------------------------------
466: l_tables BSC_IM_UTILS.varchar_tabletype;
467: l_source_tables BSC_IM_UTILS.varchar_tabletype;
468: l_relation_type BSC_IM_UTILS.varchar_tabletype;
469: l_source_sql BSC_IM_UTILS.varchar_tabletype;
470: l_table_periodicity BSC_IM_UTILS.number_tabletype;
463: l_measures BSC_IM_UTILS.varchar_tabletype;
464: l_number_measures number;
465: ---------------src table info-------------------------------------
466: l_tables BSC_IM_UTILS.varchar_tabletype;
467: l_source_tables BSC_IM_UTILS.varchar_tabletype;
468: l_relation_type BSC_IM_UTILS.varchar_tabletype;
469: l_source_sql BSC_IM_UTILS.varchar_tabletype;
470: l_table_periodicity BSC_IM_UTILS.number_tabletype;
471: l_table_period_type_id BSC_IM_UTILS.number_tabletype;
464: l_number_measures number;
465: ---------------src table info-------------------------------------
466: l_tables BSC_IM_UTILS.varchar_tabletype;
467: l_source_tables BSC_IM_UTILS.varchar_tabletype;
468: l_relation_type BSC_IM_UTILS.varchar_tabletype;
469: l_source_sql BSC_IM_UTILS.varchar_tabletype;
470: l_table_periodicity BSC_IM_UTILS.number_tabletype;
471: l_table_period_type_id BSC_IM_UTILS.number_tabletype;
472: l_periodicity_id_stmt varchar2(20000);
465: ---------------src table info-------------------------------------
466: l_tables BSC_IM_UTILS.varchar_tabletype;
467: l_source_tables BSC_IM_UTILS.varchar_tabletype;
468: l_relation_type BSC_IM_UTILS.varchar_tabletype;
469: l_source_sql BSC_IM_UTILS.varchar_tabletype;
470: l_table_periodicity BSC_IM_UTILS.number_tabletype;
471: l_table_period_type_id BSC_IM_UTILS.number_tabletype;
472: l_periodicity_id_stmt varchar2(20000);
473: l_eliminate BSC_IM_UTILS.varchar_tabletype;--these are for S tables with SB as src, to have not exists(..)
466: l_tables BSC_IM_UTILS.varchar_tabletype;
467: l_source_tables BSC_IM_UTILS.varchar_tabletype;
468: l_relation_type BSC_IM_UTILS.varchar_tabletype;
469: l_source_sql BSC_IM_UTILS.varchar_tabletype;
470: l_table_periodicity BSC_IM_UTILS.number_tabletype;
471: l_table_period_type_id BSC_IM_UTILS.number_tabletype;
472: l_periodicity_id_stmt varchar2(20000);
473: l_eliminate BSC_IM_UTILS.varchar_tabletype;--these are for S tables with SB as src, to have not exists(..)
474: l_group BSC_IM_UTILS.number_tabletype;
467: l_source_tables BSC_IM_UTILS.varchar_tabletype;
468: l_relation_type BSC_IM_UTILS.varchar_tabletype;
469: l_source_sql BSC_IM_UTILS.varchar_tabletype;
470: l_table_periodicity BSC_IM_UTILS.number_tabletype;
471: l_table_period_type_id BSC_IM_UTILS.number_tabletype;
472: l_periodicity_id_stmt varchar2(20000);
473: l_eliminate BSC_IM_UTILS.varchar_tabletype;--these are for S tables with SB as src, to have not exists(..)
474: l_group BSC_IM_UTILS.number_tabletype;
475: l_number_source_tables number;
469: l_source_sql BSC_IM_UTILS.varchar_tabletype;
470: l_table_periodicity BSC_IM_UTILS.number_tabletype;
471: l_table_period_type_id BSC_IM_UTILS.number_tabletype;
472: l_periodicity_id_stmt varchar2(20000);
473: l_eliminate BSC_IM_UTILS.varchar_tabletype;--these are for S tables with SB as src, to have not exists(..)
474: l_group BSC_IM_UTILS.number_tabletype;
475: l_number_source_tables number;
476: ---------------group column info---------------------------------
477: --used in the algo to come up with the min number of union all statements
470: l_table_periodicity BSC_IM_UTILS.number_tabletype;
471: l_table_period_type_id BSC_IM_UTILS.number_tabletype;
472: l_periodicity_id_stmt varchar2(20000);
473: l_eliminate BSC_IM_UTILS.varchar_tabletype;--these are for S tables with SB as src, to have not exists(..)
474: l_group BSC_IM_UTILS.number_tabletype;
475: l_number_source_tables number;
476: ---------------group column info---------------------------------
477: --used in the algo to come up with the min number of union all statements
478: l_group_number BSC_IM_UTILS.number_tabletype;--grp number
474: l_group BSC_IM_UTILS.number_tabletype;
475: l_number_source_tables number;
476: ---------------group column info---------------------------------
477: --used in the algo to come up with the min number of union all statements
478: l_group_number BSC_IM_UTILS.number_tabletype;--grp number
479: l_group_column_formula BSC_IM_UTILS.varchar_tabletype;
480: l_group_column BSC_IM_UTILS.varchar_tabletype;
481: l_number_group_column number;
482: -----------S table columns--------------------------------------
475: l_number_source_tables number;
476: ---------------group column info---------------------------------
477: --used in the algo to come up with the min number of union all statements
478: l_group_number BSC_IM_UTILS.number_tabletype;--grp number
479: l_group_column_formula BSC_IM_UTILS.varchar_tabletype;
480: l_group_column BSC_IM_UTILS.varchar_tabletype;
481: l_number_group_column number;
482: -----------S table columns--------------------------------------
483: l_col_table BSC_IM_UTILS.varchar_tabletype;
476: ---------------group column info---------------------------------
477: --used in the algo to come up with the min number of union all statements
478: l_group_number BSC_IM_UTILS.number_tabletype;--grp number
479: l_group_column_formula BSC_IM_UTILS.varchar_tabletype;
480: l_group_column BSC_IM_UTILS.varchar_tabletype;
481: l_number_group_column number;
482: -----------S table columns--------------------------------------
483: l_col_table BSC_IM_UTILS.varchar_tabletype;
484: l_cols BSC_IM_UTILS.varchar_tabletype;
479: l_group_column_formula BSC_IM_UTILS.varchar_tabletype;
480: l_group_column BSC_IM_UTILS.varchar_tabletype;
481: l_number_group_column number;
482: -----------S table columns--------------------------------------
483: l_col_table BSC_IM_UTILS.varchar_tabletype;
484: l_cols BSC_IM_UTILS.varchar_tabletype;
485: l_col_type BSC_IM_UTILS.varchar_tabletype;
486: l_col_formula BSC_IM_UTILS.varchar_tabletype;
487: l_col_source BSC_IM_UTILS.varchar_tabletype;
480: l_group_column BSC_IM_UTILS.varchar_tabletype;
481: l_number_group_column number;
482: -----------S table columns--------------------------------------
483: l_col_table BSC_IM_UTILS.varchar_tabletype;
484: l_cols BSC_IM_UTILS.varchar_tabletype;
485: l_col_type BSC_IM_UTILS.varchar_tabletype;
486: l_col_formula BSC_IM_UTILS.varchar_tabletype;
487: l_col_source BSC_IM_UTILS.varchar_tabletype;
488: l_number_cols number;
481: l_number_group_column number;
482: -----------S table columns--------------------------------------
483: l_col_table BSC_IM_UTILS.varchar_tabletype;
484: l_cols BSC_IM_UTILS.varchar_tabletype;
485: l_col_type BSC_IM_UTILS.varchar_tabletype;
486: l_col_formula BSC_IM_UTILS.varchar_tabletype;
487: l_col_source BSC_IM_UTILS.varchar_tabletype;
488: l_number_cols number;
489: ------------for 0 code ------------------------------------------
482: -----------S table columns--------------------------------------
483: l_col_table BSC_IM_UTILS.varchar_tabletype;
484: l_cols BSC_IM_UTILS.varchar_tabletype;
485: l_col_type BSC_IM_UTILS.varchar_tabletype;
486: l_col_formula BSC_IM_UTILS.varchar_tabletype;
487: l_col_source BSC_IM_UTILS.varchar_tabletype;
488: l_number_cols number;
489: ------------for 0 code ------------------------------------------
490: l_calculation_table BSC_IM_UTILS.varchar_tabletype;
483: l_col_table BSC_IM_UTILS.varchar_tabletype;
484: l_cols BSC_IM_UTILS.varchar_tabletype;
485: l_col_type BSC_IM_UTILS.varchar_tabletype;
486: l_col_formula BSC_IM_UTILS.varchar_tabletype;
487: l_col_source BSC_IM_UTILS.varchar_tabletype;
488: l_number_cols number;
489: ------------for 0 code ------------------------------------------
490: l_calculation_table BSC_IM_UTILS.varchar_tabletype;
491: l_calculation_type BSC_IM_UTILS.varchar_tabletype;
486: l_col_formula BSC_IM_UTILS.varchar_tabletype;
487: l_col_source BSC_IM_UTILS.varchar_tabletype;
488: l_number_cols number;
489: ------------for 0 code ------------------------------------------
490: l_calculation_table BSC_IM_UTILS.varchar_tabletype;
491: l_calculation_type BSC_IM_UTILS.varchar_tabletype;
492: l_parameter1 BSC_IM_UTILS.varchar_tabletype;
493: l_parameter2 BSC_IM_UTILS.varchar_tabletype;
494: l_parameter3 BSC_IM_UTILS.varchar_tabletype;
487: l_col_source BSC_IM_UTILS.varchar_tabletype;
488: l_number_cols number;
489: ------------for 0 code ------------------------------------------
490: l_calculation_table BSC_IM_UTILS.varchar_tabletype;
491: l_calculation_type BSC_IM_UTILS.varchar_tabletype;
492: l_parameter1 BSC_IM_UTILS.varchar_tabletype;
493: l_parameter2 BSC_IM_UTILS.varchar_tabletype;
494: l_parameter3 BSC_IM_UTILS.varchar_tabletype;
495: l_parameter4 BSC_IM_UTILS.varchar_tabletype;
488: l_number_cols number;
489: ------------for 0 code ------------------------------------------
490: l_calculation_table BSC_IM_UTILS.varchar_tabletype;
491: l_calculation_type BSC_IM_UTILS.varchar_tabletype;
492: l_parameter1 BSC_IM_UTILS.varchar_tabletype;
493: l_parameter2 BSC_IM_UTILS.varchar_tabletype;
494: l_parameter3 BSC_IM_UTILS.varchar_tabletype;
495: l_parameter4 BSC_IM_UTILS.varchar_tabletype;
496: l_parameter5 BSC_IM_UTILS.varchar_tabletype;
489: ------------for 0 code ------------------------------------------
490: l_calculation_table BSC_IM_UTILS.varchar_tabletype;
491: l_calculation_type BSC_IM_UTILS.varchar_tabletype;
492: l_parameter1 BSC_IM_UTILS.varchar_tabletype;
493: l_parameter2 BSC_IM_UTILS.varchar_tabletype;
494: l_parameter3 BSC_IM_UTILS.varchar_tabletype;
495: l_parameter4 BSC_IM_UTILS.varchar_tabletype;
496: l_parameter5 BSC_IM_UTILS.varchar_tabletype;
497: l_number_parameters number;
490: l_calculation_table BSC_IM_UTILS.varchar_tabletype;
491: l_calculation_type BSC_IM_UTILS.varchar_tabletype;
492: l_parameter1 BSC_IM_UTILS.varchar_tabletype;
493: l_parameter2 BSC_IM_UTILS.varchar_tabletype;
494: l_parameter3 BSC_IM_UTILS.varchar_tabletype;
495: l_parameter4 BSC_IM_UTILS.varchar_tabletype;
496: l_parameter5 BSC_IM_UTILS.varchar_tabletype;
497: l_number_parameters number;
498: -----------------------------------------------------------------
491: l_calculation_type BSC_IM_UTILS.varchar_tabletype;
492: l_parameter1 BSC_IM_UTILS.varchar_tabletype;
493: l_parameter2 BSC_IM_UTILS.varchar_tabletype;
494: l_parameter3 BSC_IM_UTILS.varchar_tabletype;
495: l_parameter4 BSC_IM_UTILS.varchar_tabletype;
496: l_parameter5 BSC_IM_UTILS.varchar_tabletype;
497: l_number_parameters number;
498: -----------------------------------------------------------------
499: l_select_sql varchar2(32000);
492: l_parameter1 BSC_IM_UTILS.varchar_tabletype;
493: l_parameter2 BSC_IM_UTILS.varchar_tabletype;
494: l_parameter3 BSC_IM_UTILS.varchar_tabletype;
495: l_parameter4 BSC_IM_UTILS.varchar_tabletype;
496: l_parameter5 BSC_IM_UTILS.varchar_tabletype;
497: l_number_parameters number;
498: -----------------------------------------------------------------
499: l_select_sql varchar2(32000);
500: l_select_sql_inc varchar2(32000);--for incremental mv sql
506: l_select_basic varchar2(32000);
507: l_select_no_aggregation varchar2(32000);
508: b_no_agg boolean;
509: -----------------------------------------------------------------
510: l_filter_from BSC_IM_UTILS.varchar_tabletype;
511: l_filter_where BSC_IM_UTILS.varchar_tabletype;
512: l_number_filter number;
513: l_filter_first_level BSC_IM_UTILS.varchar_tabletype;
514: l_filter_first_level_alias BSC_IM_UTILS.varchar_tabletype;
507: l_select_no_aggregation varchar2(32000);
508: b_no_agg boolean;
509: -----------------------------------------------------------------
510: l_filter_from BSC_IM_UTILS.varchar_tabletype;
511: l_filter_where BSC_IM_UTILS.varchar_tabletype;
512: l_number_filter number;
513: l_filter_first_level BSC_IM_UTILS.varchar_tabletype;
514: l_filter_first_level_alias BSC_IM_UTILS.varchar_tabletype;
515: l_filter_first_level_fk BSC_IM_UTILS.varchar_tabletype;
509: -----------------------------------------------------------------
510: l_filter_from BSC_IM_UTILS.varchar_tabletype;
511: l_filter_where BSC_IM_UTILS.varchar_tabletype;
512: l_number_filter number;
513: l_filter_first_level BSC_IM_UTILS.varchar_tabletype;
514: l_filter_first_level_alias BSC_IM_UTILS.varchar_tabletype;
515: l_filter_first_level_fk BSC_IM_UTILS.varchar_tabletype;
516: l_num_filter_first_level number;
517: -----------------------------------------------------------------
510: l_filter_from BSC_IM_UTILS.varchar_tabletype;
511: l_filter_where BSC_IM_UTILS.varchar_tabletype;
512: l_number_filter number;
513: l_filter_first_level BSC_IM_UTILS.varchar_tabletype;
514: l_filter_first_level_alias BSC_IM_UTILS.varchar_tabletype;
515: l_filter_first_level_fk BSC_IM_UTILS.varchar_tabletype;
516: l_num_filter_first_level number;
517: -----------------------------------------------------------------
518: --for zero code
511: l_filter_where BSC_IM_UTILS.varchar_tabletype;
512: l_number_filter number;
513: l_filter_first_level BSC_IM_UTILS.varchar_tabletype;
514: l_filter_first_level_alias BSC_IM_UTILS.varchar_tabletype;
515: l_filter_first_level_fk BSC_IM_UTILS.varchar_tabletype;
516: l_num_filter_first_level number;
517: -----------------------------------------------------------------
518: --for zero code
519: --there may be more than 1 fk for rollup. we need to have a union for each
518: --for zero code
519: --there may be more than 1 fk for rollup. we need to have a union for each
520: --of the fks with rollup and then one more union with zero code for all the keys
521: --combined
522: l_rollup_select_sql BSC_IM_UTILS.varchar_tabletype;
523: l_rollup_from_sql BSC_IM_UTILS.varchar_tabletype;
524: l_rollup_where_sql BSC_IM_UTILS.varchar_tabletype;
525: l_rollup_group_by_sql BSC_IM_UTILS.varchar_tabletype;
526: l_number_rollup_sql number;
519: --there may be more than 1 fk for rollup. we need to have a union for each
520: --of the fks with rollup and then one more union with zero code for all the keys
521: --combined
522: l_rollup_select_sql BSC_IM_UTILS.varchar_tabletype;
523: l_rollup_from_sql BSC_IM_UTILS.varchar_tabletype;
524: l_rollup_where_sql BSC_IM_UTILS.varchar_tabletype;
525: l_rollup_group_by_sql BSC_IM_UTILS.varchar_tabletype;
526: l_number_rollup_sql number;
527: ----------
520: --of the fks with rollup and then one more union with zero code for all the keys
521: --combined
522: l_rollup_select_sql BSC_IM_UTILS.varchar_tabletype;
523: l_rollup_from_sql BSC_IM_UTILS.varchar_tabletype;
524: l_rollup_where_sql BSC_IM_UTILS.varchar_tabletype;
525: l_rollup_group_by_sql BSC_IM_UTILS.varchar_tabletype;
526: l_number_rollup_sql number;
527: ----------
528: --l_rollup... will contain stmts with union all. these are used to create fast refresh mv
521: --combined
522: l_rollup_select_sql BSC_IM_UTILS.varchar_tabletype;
523: l_rollup_from_sql BSC_IM_UTILS.varchar_tabletype;
524: l_rollup_where_sql BSC_IM_UTILS.varchar_tabletype;
525: l_rollup_group_by_sql BSC_IM_UTILS.varchar_tabletype;
526: l_number_rollup_sql number;
527: ----------
528: --l_rollup... will contain stmts with union all. these are used to create fast refresh mv
529: -- but in the case where we cannot have fast refresh mv, we need to go for full refresh mv.
537: l_db_version varchar2(200);
538: -----------------------------------------------------------------
539: --used for dummy MV creation.
540: --dummy MV are created for snapshot log maintenance
541: l_b_tables BSC_IM_UTILS.varchar_tabletype;
542: l_number_b_tables number;
543: l_dim_level_tables BSC_IM_UTILS.varchar_tabletype;
544: l_number_dim_level_tables number;
545: -----------------------------------------------------------------
539: --used for dummy MV creation.
540: --dummy MV are created for snapshot log maintenance
541: l_b_tables BSC_IM_UTILS.varchar_tabletype;
542: l_number_b_tables number;
543: l_dim_level_tables BSC_IM_UTILS.varchar_tabletype;
544: l_number_dim_level_tables number;
545: -----------------------------------------------------------------
546: l_base_table_stmt varchar2(20000);--this is the stmt that will be used for snapshot log creation
547: l_dim_level_stmt varchar2(20000);--this will be used to create snapshot log on the dim level
548: -----------------------------------------------------------------
549: l_full_zero_code_map_name varchar2(200);
550: -----------------------------------------------------------------
551: l_lowest_s_table varchar2(200);
552: l_bt_tables BSC_IM_UTILS.varchar_tabletype;
553: l_number_bt_tables number;
554: -----------------------------------------------------------------
555: l_return_var varchar2(10);
556: l_number_keys number :=0;
563: write_to_log_file_n('In create_kpi_map_info '||p_map_name||' '||p_mv_name);
564: write_to_log_file_n('In Create_kpi_map_info'||' '||get_time);
565: end if;
566: l_number_source_tables:=0;
567: l_db_version:=BSC_IM_UTILS.get_db_version;
568: l_number_parameters:=0;
569: l_full_zero_code_map_name:=p_zero_code_map_name||'_FULL';
570: --get the fk of the summary tables
571: --if get_table_fks(p_s_tables,p_number_s_tables,l_fk,l_number_fk)=false then
635: l_number_cols)=false then
636: return false;
637: end if;
638: --get 0 code calculations
639: l_return_var := BSC_IM_UTILS.get_option_value(g_options,g_number_options,'NO ROLLUP');
640:
641: if l_return_var='Y' then
642: if g_debug then
643: write_to_log_file_n('No rollup specified');
674: --------------------------------------------------------------------------
675: l_number_b_tables:=0;
676: l_number_dim_level_tables:=0;
677: for i in 1..l_number_source_tables loop
678: if BSC_IM_UTILS.is_like(l_source_tables(i),'BSC_B_') then
679: if BSC_IM_UTILS.in_array(l_b_tables,l_number_b_tables,l_source_tables(i))=false then
680: l_number_b_tables:=l_number_b_tables+1;
681: l_b_tables(l_number_b_tables):=l_source_tables(i);
682: end if;
675: l_number_b_tables:=0;
676: l_number_dim_level_tables:=0;
677: for i in 1..l_number_source_tables loop
678: if BSC_IM_UTILS.is_like(l_source_tables(i),'BSC_B_') then
679: if BSC_IM_UTILS.in_array(l_b_tables,l_number_b_tables,l_source_tables(i))=false then
680: l_number_b_tables:=l_number_b_tables+1;
681: l_b_tables(l_number_b_tables):=l_source_tables(i);
682: end if;
683: end if;
686: write_to_log_file_n('Process the T tables');
687: end if;
688: l_periodicity_id_stmt:=' periodicity_id in (';
689: for i in 1..l_number_source_tables loop
690: if BSC_IM_UTILS.is_like(l_source_tables(i),'BSC_T_') then --this is a T table
691: --construct the sql
692: --we need to get the level tables also here because snp logs need to be created on level tables too
693: --do we need to get the filter to the base tables for T?
694: if get_table_sql(l_source_tables(i),l_source_sql(i),l_b_tables,l_number_b_tables,
721: end if;
722: -----------------------------------
723: --load the base table column info also into int metadata for snapshot log
724: declare
725: ll_fk BSC_IM_UTILS.varchar_tabletype;
726: ll_number_fk number;
727: ll_measures BSC_IM_UTILS.varchar_tabletype;
728: ll_number_measures number;
729: begin
723: --load the base table column info also into int metadata for snapshot log
724: declare
725: ll_fk BSC_IM_UTILS.varchar_tabletype;
726: ll_number_fk number;
727: ll_measures BSC_IM_UTILS.varchar_tabletype;
728: ll_number_measures number;
729: begin
730: for i in 1..l_number_b_tables loop
731: if get_table_fks(l_b_tables(i),ll_fk,ll_number_fk)=false then
787: we use the same formula as the lowest S table.
788: */
789: declare
790: -----------------------------------
791: ll_col BSC_IM_UTILS.varchar_tabletype;
792: ll_col_formula BSC_IM_UTILS.varchar_tabletype;
793: ll_number_col number;
794: ll_index number;
795: -----------------------------------
788: */
789: declare
790: -----------------------------------
791: ll_col BSC_IM_UTILS.varchar_tabletype;
792: ll_col_formula BSC_IM_UTILS.varchar_tabletype;
793: ll_number_col number;
794: ll_index number;
795: -----------------------------------
796: ll_rollup_found boolean;
793: ll_number_col number;
794: ll_index number;
795: -----------------------------------
796: ll_rollup_found boolean;
797: ll_table BSC_IM_UTILS.varchar_tabletype;
798: ll_number_table number;
799: ll_calculation_type BSC_IM_UTILS.varchar_tabletype;
800: ll_parameter1 BSC_IM_UTILS.varchar_tabletype;
801: ll_parameter2 BSC_IM_UTILS.varchar_tabletype;
795: -----------------------------------
796: ll_rollup_found boolean;
797: ll_table BSC_IM_UTILS.varchar_tabletype;
798: ll_number_table number;
799: ll_calculation_type BSC_IM_UTILS.varchar_tabletype;
800: ll_parameter1 BSC_IM_UTILS.varchar_tabletype;
801: ll_parameter2 BSC_IM_UTILS.varchar_tabletype;
802: ll_parameter3 BSC_IM_UTILS.varchar_tabletype;
803: ll_parameter4 BSC_IM_UTILS.varchar_tabletype;
796: ll_rollup_found boolean;
797: ll_table BSC_IM_UTILS.varchar_tabletype;
798: ll_number_table number;
799: ll_calculation_type BSC_IM_UTILS.varchar_tabletype;
800: ll_parameter1 BSC_IM_UTILS.varchar_tabletype;
801: ll_parameter2 BSC_IM_UTILS.varchar_tabletype;
802: ll_parameter3 BSC_IM_UTILS.varchar_tabletype;
803: ll_parameter4 BSC_IM_UTILS.varchar_tabletype;
804: ll_parameter5 BSC_IM_UTILS.varchar_tabletype;
797: ll_table BSC_IM_UTILS.varchar_tabletype;
798: ll_number_table number;
799: ll_calculation_type BSC_IM_UTILS.varchar_tabletype;
800: ll_parameter1 BSC_IM_UTILS.varchar_tabletype;
801: ll_parameter2 BSC_IM_UTILS.varchar_tabletype;
802: ll_parameter3 BSC_IM_UTILS.varchar_tabletype;
803: ll_parameter4 BSC_IM_UTILS.varchar_tabletype;
804: ll_parameter5 BSC_IM_UTILS.varchar_tabletype;
805: ll_number_parameters number;
798: ll_number_table number;
799: ll_calculation_type BSC_IM_UTILS.varchar_tabletype;
800: ll_parameter1 BSC_IM_UTILS.varchar_tabletype;
801: ll_parameter2 BSC_IM_UTILS.varchar_tabletype;
802: ll_parameter3 BSC_IM_UTILS.varchar_tabletype;
803: ll_parameter4 BSC_IM_UTILS.varchar_tabletype;
804: ll_parameter5 BSC_IM_UTILS.varchar_tabletype;
805: ll_number_parameters number;
806: -----------------------------------
799: ll_calculation_type BSC_IM_UTILS.varchar_tabletype;
800: ll_parameter1 BSC_IM_UTILS.varchar_tabletype;
801: ll_parameter2 BSC_IM_UTILS.varchar_tabletype;
802: ll_parameter3 BSC_IM_UTILS.varchar_tabletype;
803: ll_parameter4 BSC_IM_UTILS.varchar_tabletype;
804: ll_parameter5 BSC_IM_UTILS.varchar_tabletype;
805: ll_number_parameters number;
806: -----------------------------------
807: begin
800: ll_parameter1 BSC_IM_UTILS.varchar_tabletype;
801: ll_parameter2 BSC_IM_UTILS.varchar_tabletype;
802: ll_parameter3 BSC_IM_UTILS.varchar_tabletype;
803: ll_parameter4 BSC_IM_UTILS.varchar_tabletype;
804: ll_parameter5 BSC_IM_UTILS.varchar_tabletype;
805: ll_number_parameters number;
806: -----------------------------------
807: begin
808: for i in 1..l_number_source_tables loop
805: ll_number_parameters number;
806: -----------------------------------
807: begin
808: for i in 1..l_number_source_tables loop
809: if BSC_IM_UTILS.is_like(l_source_tables(i),'BSC_T_') or
810: BSC_IM_UTILS.is_like(l_source_tables(i),'BSC_B_') then --this is a T or B table
811: l_lowest_s_table:=l_tables(i);
812: exit;
813: end if;
806: -----------------------------------
807: begin
808: for i in 1..l_number_source_tables loop
809: if BSC_IM_UTILS.is_like(l_source_tables(i),'BSC_T_') or
810: BSC_IM_UTILS.is_like(l_source_tables(i),'BSC_B_') then --this is a T or B table
811: l_lowest_s_table:=l_tables(i);
812: exit;
813: end if;
814: end loop;
831: --set the higher levels
832: for i in 1..l_number_cols loop
833: if l_col_table(i) <> l_lowest_s_table then
834: ll_index:=0;
835: ll_index:=BSC_IM_UTILS.get_index(ll_col,ll_number_col,l_cols(i));
836: if ll_index>0 then
837: if l_col_formula(i) <> ll_col_formula(ll_index) then
838: if g_debug then
839: write_to_log_file('For '||l_col_table(i)||', changing '||l_col_formula(i)||' to '||
864: end if;
865: for i in 1..l_number_parameters loop
866: if l_calculation_table(i)=l_lowest_s_table then
867: ll_index:=0;
868: ll_index:=BSC_IM_UTILS.get_index(l_cols,l_number_cols,l_parameter3(i));
869: if ll_index>0 then
870: if g_debug then
871: write_to_log_file('Changing '||l_parameter5(i)||' to '||l_col_formula(ll_index));
872: end if;
901: for j in 1..l_number_parameters loop
902: if l_calculation_table(j)=p_s_tables(i) then
903: ll_rollup_found:=true;
904: ll_index:=0;
905: ll_index:=BSC_IM_UTILS.get_index(ll_parameter3,ll_number_parameters,l_parameter3(j));
906: if ll_index>0 then
907: if l_parameter5(j)<>ll_parameter5(ll_index) then
908: if g_debug then
909: write_to_log_file('For '||l_calculation_table(j)||', changing '||l_parameter5(j)||
960: --make one source.
961: --see if we need to resolve the source. if the relation is B1,B2-> 24_5 -> 24_3 -> 24_1
962: --then we need to make B1,B2-> 24_5 B1,B2-> 24_3 B1,B2-> 24_1
963: declare
964: ll_tables BSC_IM_UTILS.varchar_tabletype;
965: ll_source_tables BSC_IM_UTILS.varchar_tabletype;
966: ll_relation_type BSC_IM_UTILS.varchar_tabletype;
967: ll_source_sql BSC_IM_UTILS.varchar_tabletype;
968: ll_table_periodicity BSC_IM_UTILS.number_tabletype;
961: --see if we need to resolve the source. if the relation is B1,B2-> 24_5 -> 24_3 -> 24_1
962: --then we need to make B1,B2-> 24_5 B1,B2-> 24_3 B1,B2-> 24_1
963: declare
964: ll_tables BSC_IM_UTILS.varchar_tabletype;
965: ll_source_tables BSC_IM_UTILS.varchar_tabletype;
966: ll_relation_type BSC_IM_UTILS.varchar_tabletype;
967: ll_source_sql BSC_IM_UTILS.varchar_tabletype;
968: ll_table_periodicity BSC_IM_UTILS.number_tabletype;
969: ll_number_source_tables number;
962: --then we need to make B1,B2-> 24_5 B1,B2-> 24_3 B1,B2-> 24_1
963: declare
964: ll_tables BSC_IM_UTILS.varchar_tabletype;
965: ll_source_tables BSC_IM_UTILS.varchar_tabletype;
966: ll_relation_type BSC_IM_UTILS.varchar_tabletype;
967: ll_source_sql BSC_IM_UTILS.varchar_tabletype;
968: ll_table_periodicity BSC_IM_UTILS.number_tabletype;
969: ll_number_source_tables number;
970: ------------------------------------------
963: declare
964: ll_tables BSC_IM_UTILS.varchar_tabletype;
965: ll_source_tables BSC_IM_UTILS.varchar_tabletype;
966: ll_relation_type BSC_IM_UTILS.varchar_tabletype;
967: ll_source_sql BSC_IM_UTILS.varchar_tabletype;
968: ll_table_periodicity BSC_IM_UTILS.number_tabletype;
969: ll_number_source_tables number;
970: ------------------------------------------
971: ll_list BSC_IM_UTILS.varchar_tabletype;
964: ll_tables BSC_IM_UTILS.varchar_tabletype;
965: ll_source_tables BSC_IM_UTILS.varchar_tabletype;
966: ll_relation_type BSC_IM_UTILS.varchar_tabletype;
967: ll_source_sql BSC_IM_UTILS.varchar_tabletype;
968: ll_table_periodicity BSC_IM_UTILS.number_tabletype;
969: ll_number_source_tables number;
970: ------------------------------------------
971: ll_list BSC_IM_UTILS.varchar_tabletype;
972: ll_list_2 BSC_IM_UTILS.varchar_tabletype;
967: ll_source_sql BSC_IM_UTILS.varchar_tabletype;
968: ll_table_periodicity BSC_IM_UTILS.number_tabletype;
969: ll_number_source_tables number;
970: ------------------------------------------
971: ll_list BSC_IM_UTILS.varchar_tabletype;
972: ll_list_2 BSC_IM_UTILS.varchar_tabletype;
973: ll_number_list number;
974: ll_found boolean;
975: ll_count integer;
968: ll_table_periodicity BSC_IM_UTILS.number_tabletype;
969: ll_number_source_tables number;
970: ------------------------------------------
971: ll_list BSC_IM_UTILS.varchar_tabletype;
972: ll_list_2 BSC_IM_UTILS.varchar_tabletype;
973: ll_number_list number;
974: ll_found boolean;
975: ll_count integer;
976: -----------------------------------------
973: ll_number_list number;
974: ll_found boolean;
975: ll_count integer;
976: -----------------------------------------
977: ll_bt_measures BSC_IM_UTILS.varchar_tabletype;
978: ll_number_bt_measures number;
979: ll_column_merge_group BSC_IM_UTILS.varchar_tabletype;
980: ll_column_merge_group_sql BSC_IM_UTILS.varchar_tabletype;
981: ll_number_column_merge_group number;
975: ll_count integer;
976: -----------------------------------------
977: ll_bt_measures BSC_IM_UTILS.varchar_tabletype;
978: ll_number_bt_measures number;
979: ll_column_merge_group BSC_IM_UTILS.varchar_tabletype;
980: ll_column_merge_group_sql BSC_IM_UTILS.varchar_tabletype;
981: ll_number_column_merge_group number;
982: ll_column_merge_sql BSC_IM_UTILS.varchar_tabletype; --uses p_number_s_tables
983: ll_merge_sql varchar2(32000);
976: -----------------------------------------
977: ll_bt_measures BSC_IM_UTILS.varchar_tabletype;
978: ll_number_bt_measures number;
979: ll_column_merge_group BSC_IM_UTILS.varchar_tabletype;
980: ll_column_merge_group_sql BSC_IM_UTILS.varchar_tabletype;
981: ll_number_column_merge_group number;
982: ll_column_merge_sql BSC_IM_UTILS.varchar_tabletype; --uses p_number_s_tables
983: ll_merge_sql varchar2(32000);
984:
978: ll_number_bt_measures number;
979: ll_column_merge_group BSC_IM_UTILS.varchar_tabletype;
980: ll_column_merge_group_sql BSC_IM_UTILS.varchar_tabletype;
981: ll_number_column_merge_group number;
982: ll_column_merge_sql BSC_IM_UTILS.varchar_tabletype; --uses p_number_s_tables
983: ll_merge_sql varchar2(32000);
984:
985: ll_bt_measures_by_table VARCHAR2(32000);
986: -----------------------------------------
994: --there is a difference between l_number_bt_tables and l_number_b_tables
995: --l_number_bt_tables sees how many B and T tables feed this base the first level summary
996: --l_number_b_tables goes recursively down and sees all the B tables involved.
997: for i in 1..l_number_source_tables loop
998: if BSC_IM_UTILS.is_like(l_source_tables(i),'BSC_B_')
999: or BSC_IM_UTILS.is_like(l_source_tables(i),'BSC_T_') then
1000: l_number_bt_tables:=l_number_bt_tables+1;
1001: l_bt_tables(l_number_bt_tables):=l_source_tables(i);
1002: end if;
995: --l_number_bt_tables sees how many B and T tables feed this base the first level summary
996: --l_number_b_tables goes recursively down and sees all the B tables involved.
997: for i in 1..l_number_source_tables loop
998: if BSC_IM_UTILS.is_like(l_source_tables(i),'BSC_B_')
999: or BSC_IM_UTILS.is_like(l_source_tables(i),'BSC_T_') then
1000: l_number_bt_tables:=l_number_bt_tables+1;
1001: l_bt_tables(l_number_bt_tables):=l_source_tables(i);
1002: end if;
1003: end loop;
1047: ll_column_merge_sql(i):=null;
1048: ll_number_column_merge_group:=0;
1049: for j in 1..l_number_source_tables loop
1050: if l_tables(j)=p_s_tables(i) and (
1051: BSC_IM_UTILS.is_like(l_source_tables(j),'BSC_B_')
1052: or BSC_IM_UTILS.is_like(l_source_tables(j),'BSC_T_')) then
1053: ll_number_list:=ll_number_list+1;
1054: ll_list(ll_number_list):=l_source_tables(j);
1055: ll_list_2(ll_number_list):=l_source_sql(j);
1048: ll_number_column_merge_group:=0;
1049: for j in 1..l_number_source_tables loop
1050: if l_tables(j)=p_s_tables(i) and (
1051: BSC_IM_UTILS.is_like(l_source_tables(j),'BSC_B_')
1052: or BSC_IM_UTILS.is_like(l_source_tables(j),'BSC_T_')) then
1053: ll_number_list:=ll_number_list+1;
1054: ll_list(ll_number_list):=l_source_tables(j);
1055: ll_list_2(ll_number_list):=l_source_sql(j);
1056: end if;
1085: ll_number_list)=false then
1086: return false;
1087: end if;
1088: for j in 1..ll_number_list loop
1089: if BSC_IM_UTILS.in_array(l_measures,l_number_measures,ll_list(j)) then
1090: ll_number_bt_measures:=ll_number_bt_measures+1;
1091: ll_bt_measures(ll_number_bt_measures):=ll_list(j);
1092: end if;
1093: end loop;
1115: for j in 1..l_num_filter_first_level loop
1116: ll_column_merge_sql(i):=ll_column_merge_sql(i)||' and '||ll_column_merge_group(1)||'.'||
1117: l_filter_first_level_fk(j)||'='||l_filter_first_level_alias(j)||'.code ';
1118: --3613094
1119: if bsc_im_utils.is_column_in_object(l_filter_first_level(j),'LANGUAGE') then
1120: ll_column_merge_sql(i):=ll_column_merge_sql(i)||' and '||
1121: l_filter_first_level_alias(j)||'.language='''||BSC_IM_UTILS.get_lang||'''';
1122: end if;
1123: end loop;
1117: l_filter_first_level_fk(j)||'='||l_filter_first_level_alias(j)||'.code ';
1118: --3613094
1119: if bsc_im_utils.is_column_in_object(l_filter_first_level(j),'LANGUAGE') then
1120: ll_column_merge_sql(i):=ll_column_merge_sql(i)||' and '||
1121: l_filter_first_level_alias(j)||'.language='''||BSC_IM_UTILS.get_lang||'''';
1122: end if;
1123: end loop;
1124: for j in 1..l_number_filter loop
1125: ll_column_merge_sql(i):=ll_column_merge_sql(i)||' '||l_filter_where(j);
1165: for k in 1..l_num_filter_first_level loop
1166: ll_column_merge_sql(i):=ll_column_merge_sql(i)||' and '||ll_column_merge_group(j)||'.'||
1167: l_filter_first_level_fk(k)||'='||l_filter_first_level_alias(k)||'.code ';
1168: --3613094
1169: if bsc_im_utils.is_column_in_object(l_filter_first_level(k),'LANGUAGE') then
1170: ll_column_merge_sql(i):=ll_column_merge_sql(i)||' and '||
1171: l_filter_first_level_alias(k)||'.language='''||BSC_IM_UTILS.get_lang||''' ';
1172: end if;
1173: end loop;
1167: l_filter_first_level_fk(k)||'='||l_filter_first_level_alias(k)||'.code ';
1168: --3613094
1169: if bsc_im_utils.is_column_in_object(l_filter_first_level(k),'LANGUAGE') then
1170: ll_column_merge_sql(i):=ll_column_merge_sql(i)||' and '||
1171: l_filter_first_level_alias(k)||'.language='''||BSC_IM_UTILS.get_lang||''' ';
1172: end if;
1173: end loop;
1174: for k in 1..l_number_filter loop
1175: ll_column_merge_sql(i):=ll_column_merge_sql(i)||' '||l_filter_where(k);
1205: ll_merge_sql := ' (
1206: b_no_agg := true;
1207: --now correct the pl/sql records for src info
1208: for i in 1..l_number_source_tables loop
1209: if BSC_IM_UTILS.is_like(l_source_tables(i),'BSC_B_')
1210: or BSC_IM_UTILS.is_like(l_source_tables(i),'BSC_T_') then
1211: l_source_tables(i):='BSC_B';
1212: l_source_sql(i):=ll_merge_sql;
1213: end if;
1214: end loop;
1216: --we need this because there could have been multiple B and T tables. they all become the
1217: --consolidated src, BSC_B.
1218: ll_number_source_tables:=0;
1219: for i in 1..l_number_source_tables loop
1220: if BSC_IM_UTILS.in_array(ll_tables,ll_source_tables,ll_number_source_tables,
1221: l_tables(i),l_source_tables(i))=false then
1222: ll_number_source_tables:=ll_number_source_tables+1;
1223: ll_tables(ll_number_source_tables):=l_tables(i);
1224: ll_source_tables(ll_number_source_tables):=l_source_tables(i);
1309: end;
1310: --check to see if we can make a single sql or we need a union all
1311: --update the src table to MV
1312: for i in 1..l_number_source_tables loop
1313: if BSC_IM_UTILS.is_like(l_source_tables(i),'BSC_S_')
1314: or BSC_IM_UTILS.is_like(l_source_tables(i),'BSC_SB_') then
1315: l_source_tables(i):=substr(l_source_tables(i),1,instr(l_source_tables(i),'_',-1)-1)||'_MV';
1316: end if;
1317: end loop;
1310: --check to see if we can make a single sql or we need a union all
1311: --update the src table to MV
1312: for i in 1..l_number_source_tables loop
1313: if BSC_IM_UTILS.is_like(l_source_tables(i),'BSC_S_')
1314: or BSC_IM_UTILS.is_like(l_source_tables(i),'BSC_SB_') then
1315: l_source_tables(i):=substr(l_source_tables(i),1,instr(l_source_tables(i),'_',-1)-1)||'_MV';
1316: end if;
1317: end loop;
1318: if g_debug then
1327: end loop;
1328: --if S tables have SB as source, we need to have not exists clause to remove the target info from
1329: --non SB source
1330: for i in 1..l_number_source_tables loop
1331: if BSC_IM_UTILS.is_like(l_source_tables(i),'BSC_SB_') then
1332: for j in 1..l_number_source_tables loop
1333: if l_tables(j)=l_tables(i) and not(BSC_IM_UTILS.is_like(l_source_tables(j),'BSC_SB_')) then
1334: l_eliminate(j):=l_source_tables(i);
1335: end if;
1329: --non SB source
1330: for i in 1..l_number_source_tables loop
1331: if BSC_IM_UTILS.is_like(l_source_tables(i),'BSC_SB_') then
1332: for j in 1..l_number_source_tables loop
1333: if l_tables(j)=l_tables(i) and not(BSC_IM_UTILS.is_like(l_source_tables(j),'BSC_SB_')) then
1334: l_eliminate(j):=l_source_tables(i);
1335: end if;
1336: end loop;
1337: end if;
1349: declare
1350: ll_eliminate varchar2(200);
1351: begin
1352: for i in 1..l_number_source_tables loop
1353: if not(BSC_IM_UTILS.is_like(l_source_tables(i),'BSC_SB_')) and l_eliminate(i) is not null then
1354: ll_eliminate:=l_eliminate(i);
1355: for j in 1..l_number_source_tables loop
1356: if i<>j then
1357: if l_source_tables(j)=l_source_tables(i) and l_eliminate(j) is null then
1371: end if;
1372: -------------------------------------------------------
1373: --add int metadata for MV relations, useful for summary mv reresh to decide the order
1374: for i in 1..l_number_source_tables loop
1375: if BSC_IM_UTILS.is_like(l_source_tables(i),'BSC_S_')
1376: or BSC_IM_UTILS.is_like(l_source_tables(i),'BSC_SB_') then
1377: if g_debug then
1378: write_to_log_file_n('In Create_kpi_map_info--Before BSC_IM_INT_MD.create_object'||' '||get_time);
1379: end if;
1372: -------------------------------------------------------
1373: --add int metadata for MV relations, useful for summary mv reresh to decide the order
1374: for i in 1..l_number_source_tables loop
1375: if BSC_IM_UTILS.is_like(l_source_tables(i),'BSC_S_')
1376: or BSC_IM_UTILS.is_like(l_source_tables(i),'BSC_SB_') then
1377: if g_debug then
1378: write_to_log_file_n('In Create_kpi_map_info--Before BSC_IM_INT_MD.create_object'||' '||get_time);
1379: end if;
1380: if BSC_IM_INT_MD.create_object(
1403: 4. is the src table different?
1404: */
1405: declare
1406: -------------------------------------
1407: ll_groups BSC_IM_UTILS.varchar_tabletype;
1408: ll_number_groups number;
1409: -------------------------------------
1410: ll_table_grp BSC_IM_UTILS.varchar_tabletype;
1411: ll_table BSC_IM_UTILS.varchar_tabletype;
1406: -------------------------------------
1407: ll_groups BSC_IM_UTILS.varchar_tabletype;
1408: ll_number_groups number;
1409: -------------------------------------
1410: ll_table_grp BSC_IM_UTILS.varchar_tabletype;
1411: ll_table BSC_IM_UTILS.varchar_tabletype;
1412: ll_source BSC_IM_UTILS.varchar_tabletype;
1413: ll_number_table_grp number;
1414: -------------------------------------
1407: ll_groups BSC_IM_UTILS.varchar_tabletype;
1408: ll_number_groups number;
1409: -------------------------------------
1410: ll_table_grp BSC_IM_UTILS.varchar_tabletype;
1411: ll_table BSC_IM_UTILS.varchar_tabletype;
1412: ll_source BSC_IM_UTILS.varchar_tabletype;
1413: ll_number_table_grp number;
1414: -------------------------------------
1415: ll_column_grp BSC_IM_UTILS.varchar_tabletype;
1408: ll_number_groups number;
1409: -------------------------------------
1410: ll_table_grp BSC_IM_UTILS.varchar_tabletype;
1411: ll_table BSC_IM_UTILS.varchar_tabletype;
1412: ll_source BSC_IM_UTILS.varchar_tabletype;
1413: ll_number_table_grp number;
1414: -------------------------------------
1415: ll_column_grp BSC_IM_UTILS.varchar_tabletype;
1416: ll_column_formula BSC_IM_UTILS.varchar_tabletype;
1411: ll_table BSC_IM_UTILS.varchar_tabletype;
1412: ll_source BSC_IM_UTILS.varchar_tabletype;
1413: ll_number_table_grp number;
1414: -------------------------------------
1415: ll_column_grp BSC_IM_UTILS.varchar_tabletype;
1416: ll_column_formula BSC_IM_UTILS.varchar_tabletype;
1417: ll_column BSC_IM_UTILS.varchar_tabletype;
1418: ll_number_column_grp number;
1419: -------------------------------------
1412: ll_source BSC_IM_UTILS.varchar_tabletype;
1413: ll_number_table_grp number;
1414: -------------------------------------
1415: ll_column_grp BSC_IM_UTILS.varchar_tabletype;
1416: ll_column_formula BSC_IM_UTILS.varchar_tabletype;
1417: ll_column BSC_IM_UTILS.varchar_tabletype;
1418: ll_number_column_grp number;
1419: -------------------------------------
1420: ll_rollup_grp BSC_IM_UTILS.varchar_tabletype;
1413: ll_number_table_grp number;
1414: -------------------------------------
1415: ll_column_grp BSC_IM_UTILS.varchar_tabletype;
1416: ll_column_formula BSC_IM_UTILS.varchar_tabletype;
1417: ll_column BSC_IM_UTILS.varchar_tabletype;
1418: ll_number_column_grp number;
1419: -------------------------------------
1420: ll_rollup_grp BSC_IM_UTILS.varchar_tabletype;
1421: ll_rollup_formula BSC_IM_UTILS.varchar_tabletype;
1416: ll_column_formula BSC_IM_UTILS.varchar_tabletype;
1417: ll_column BSC_IM_UTILS.varchar_tabletype;
1418: ll_number_column_grp number;
1419: -------------------------------------
1420: ll_rollup_grp BSC_IM_UTILS.varchar_tabletype;
1421: ll_rollup_formula BSC_IM_UTILS.varchar_tabletype;
1422: ll_rollup_column BSC_IM_UTILS.varchar_tabletype;
1423: ll_number_rollup_grp number;
1424: -------------------------------------
1417: ll_column BSC_IM_UTILS.varchar_tabletype;
1418: ll_number_column_grp number;
1419: -------------------------------------
1420: ll_rollup_grp BSC_IM_UTILS.varchar_tabletype;
1421: ll_rollup_formula BSC_IM_UTILS.varchar_tabletype;
1422: ll_rollup_column BSC_IM_UTILS.varchar_tabletype;
1423: ll_number_rollup_grp number;
1424: -------------------------------------
1425: ll_eliminate_grp BSC_IM_UTILS.varchar_tabletype;
1418: ll_number_column_grp number;
1419: -------------------------------------
1420: ll_rollup_grp BSC_IM_UTILS.varchar_tabletype;
1421: ll_rollup_formula BSC_IM_UTILS.varchar_tabletype;
1422: ll_rollup_column BSC_IM_UTILS.varchar_tabletype;
1423: ll_number_rollup_grp number;
1424: -------------------------------------
1425: ll_eliminate_grp BSC_IM_UTILS.varchar_tabletype;
1426: ll_eliminate BSC_IM_UTILS.varchar_tabletype;
1421: ll_rollup_formula BSC_IM_UTILS.varchar_tabletype;
1422: ll_rollup_column BSC_IM_UTILS.varchar_tabletype;
1423: ll_number_rollup_grp number;
1424: -------------------------------------
1425: ll_eliminate_grp BSC_IM_UTILS.varchar_tabletype;
1426: ll_eliminate BSC_IM_UTILS.varchar_tabletype;
1427: ll_number_eliminate_grp number;
1428: -------------------------------------
1429: ll_new_reqd boolean;
1422: ll_rollup_column BSC_IM_UTILS.varchar_tabletype;
1423: ll_number_rollup_grp number;
1424: -------------------------------------
1425: ll_eliminate_grp BSC_IM_UTILS.varchar_tabletype;
1426: ll_eliminate BSC_IM_UTILS.varchar_tabletype;
1427: ll_number_eliminate_grp number;
1428: -------------------------------------
1429: ll_new_reqd boolean;
1430: -------------------------------------
1427: ll_number_eliminate_grp number;
1428: -------------------------------------
1429: ll_new_reqd boolean;
1430: -------------------------------------
1431: ll_measure BSC_IM_UTILS.varchar_tabletype;
1432: ll_measure_formula BSC_IM_UTILS.varchar_tabletype;
1433: ll_number_measure number;
1434: -------------------------------------
1435: begin
1428: -------------------------------------
1429: ll_new_reqd boolean;
1430: -------------------------------------
1431: ll_measure BSC_IM_UTILS.varchar_tabletype;
1432: ll_measure_formula BSC_IM_UTILS.varchar_tabletype;
1433: ll_number_measure number;
1434: -------------------------------------
1435: begin
1436: ll_number_groups:=0;
1581: l_group_by_sql:=null;
1582: l_hint_sql := null;
1583: declare
1584: ------------------------------------------------------
1585: ll_distinct_groups BSC_IM_UTILS.varchar_tabletype;
1586: ll_number_distinct_groups number;
1587: ------------------------------------------------------
1588: ll_index number;
1589: ll_fk_index number;
1596: ll_rec_dim_key varchar2(100);
1597: ------------------------------------------------------
1598: ll_zero_separate boolean;
1599: ------------------------------------------------------
1600: ll_rollup_fk BSC_IM_UTILS.varchar_tabletype;
1601: ll_rollup_fk_value BSC_IM_UTILS.varchar_tabletype;
1602: ll_number_rollup_fk number;
1603: ------------------------------------------------------
1604: ll_periodicity BSC_IM_UTILS.number_tabletype;
1597: ------------------------------------------------------
1598: ll_zero_separate boolean;
1599: ------------------------------------------------------
1600: ll_rollup_fk BSC_IM_UTILS.varchar_tabletype;
1601: ll_rollup_fk_value BSC_IM_UTILS.varchar_tabletype;
1602: ll_number_rollup_fk number;
1603: ------------------------------------------------------
1604: ll_periodicity BSC_IM_UTILS.number_tabletype;
1605: ll_number_periodicity number;
1600: ll_rollup_fk BSC_IM_UTILS.varchar_tabletype;
1601: ll_rollup_fk_value BSC_IM_UTILS.varchar_tabletype;
1602: ll_number_rollup_fk number;
1603: ------------------------------------------------------
1604: ll_periodicity BSC_IM_UTILS.number_tabletype;
1605: ll_number_periodicity number;
1606: ------------------------------------------------------
1607: ll_keys_stmt varchar2(10000);
1608: ll_prj_table_name varchar2(100);
1609: begin
1610: --get the distinct groups
1611: ll_number_distinct_groups:=0;
1612: for i in 1..l_number_source_tables loop
1613: if BSC_IM_UTILS.in_array(ll_distinct_groups,ll_number_distinct_groups,l_group(i))=false then
1614: ll_number_distinct_groups:=ll_number_distinct_groups+1;
1615: ll_distinct_groups(ll_number_distinct_groups):=l_group(i);
1616: end if;
1617: end loop;
1662: l_hint_sql := l_hint_sql||l_source_tables(ll_index)||' ';
1663: else
1664: if l_source_tables(ll_index)<>'BSC_B' then
1665: -- handle b_prj, P1 5214589
1666: if BSC_IM_UTILS.is_like(l_source_tables(ll_index),'BSC_B_') then
1667: ll_prj_table_name := get_b_prj_table_name(l_source_tables(ll_index));
1668: if (ll_prj_table_name is not null) then
1669: l_from_sql:=l_from_sql||get_prj_union_clause(l_source_tables(ll_index));
1670: else
1667: ll_prj_table_name := get_b_prj_table_name(l_source_tables(ll_index));
1668: if (ll_prj_table_name is not null) then
1669: l_from_sql:=l_from_sql||get_prj_union_clause(l_source_tables(ll_index));
1670: else
1671: l_from_sql:=l_from_sql||bsc_im_utils.get_table_owner(l_source_tables(ll_index))
1672: ||'.'||l_source_tables(ll_index)||
1673: ' '||l_source_tables(ll_index);
1674: end if;
1675: else
1672: ||'.'||l_source_tables(ll_index)||
1673: ' '||l_source_tables(ll_index);
1674: end if;
1675: else
1676: l_from_sql:=l_from_sql||bsc_im_utils.get_table_owner(l_source_tables(ll_index))
1677: ||'.'||l_source_tables(ll_index)||
1678: ' '||l_source_tables(ll_index);
1679: end if;
1680: else
1697: --so ll_zero_separate is true even for 9i
1698: --in 9i, if there is partial rollup, we dont get fast refresh
1699: ll_zero_separate:=true;
1700: -------------------------------
1701: if not(BSC_IM_UTILS.is_like(l_source_tables(ll_index),'BSC_SB_')) then
1702: if ll_zero_separate=false then --this is 9i
1703: --see if the 0 code aggregations are different from the normal aggregations
1704: --if the target is S and src table is SB, no zero code
1705: if BSC_IM_UTILS.is_like(l_tables(ll_index),'BSC_S_')
1701: if not(BSC_IM_UTILS.is_like(l_source_tables(ll_index),'BSC_SB_')) then
1702: if ll_zero_separate=false then --this is 9i
1703: --see if the 0 code aggregations are different from the normal aggregations
1704: --if the target is S and src table is SB, no zero code
1705: if BSC_IM_UTILS.is_like(l_tables(ll_index),'BSC_S_')
1706: and BSC_IM_UTILS.is_like(l_source_tables(ll_index),'BSC_SB_') then
1707: if g_debug then
1708: write_to_log_file_n('No zero code when target is S and src is SB');
1709: end if;
1702: if ll_zero_separate=false then --this is 9i
1703: --see if the 0 code aggregations are different from the normal aggregations
1704: --if the target is S and src table is SB, no zero code
1705: if BSC_IM_UTILS.is_like(l_tables(ll_index),'BSC_S_')
1706: and BSC_IM_UTILS.is_like(l_source_tables(ll_index),'BSC_SB_') then
1707: if g_debug then
1708: write_to_log_file_n('No zero code when target is S and src is SB');
1709: end if;
1710: else
1710: else
1711: for j in 1..l_number_parameters loop
1712: if l_calculation_table(j)=l_tables(ll_index) then
1713: ll_col_index:=0;
1714: ll_col_index:=BSC_IM_UTILS.get_index(l_col_table,l_cols,l_number_cols,l_tables(ll_index),
1715: l_parameter3(j));
1716: if ll_col_index>0 then
1717: if l_parameter5(j)<>l_col_formula(ll_col_index) then
1718: ll_zero_separate:=true;
1731: ll_keys_stmt := null;
1732: write_to_log_file('Now fks');
1733: --then the fks
1734: for j in 1..l_number_fk loop
1735: ll_fk_index:=BSC_IM_UTILS.get_index(l_col_table,l_cols,l_number_cols,l_tables(ll_index),
1736: l_fk(j));
1737: if ll_fk_index>0 then
1738: --S B ok --rollup to higher level in the dimension
1739: --S S ok --rollup to higher level in the dimension
1739: --S S ok --rollup to higher level in the dimension
1740: --SB SB ok --rollup to higher level in the dimension
1741: --S SB not ok
1742: ll_keys_stmt := ll_keys_stmt||l_cols(ll_fk_index)||',';
1743: if BSC_IM_UTILS.is_like(l_tables(ll_index),'BSC_S_')
1744: and BSC_IM_UTILS.is_like(l_source_tables(ll_index),'BSC_SB_') then
1745: l_select_sql:=l_select_sql||l_source_tables(ll_index)||'.'||l_cols(ll_fk_index)||' '||
1746: l_cols(ll_fk_index)||',';
1747: l_group_by_sql:=l_group_by_sql||l_source_tables(ll_index)||'.'||l_cols(ll_fk_index)||',';
1740: --SB SB ok --rollup to higher level in the dimension
1741: --S SB not ok
1742: ll_keys_stmt := ll_keys_stmt||l_cols(ll_fk_index)||',';
1743: if BSC_IM_UTILS.is_like(l_tables(ll_index),'BSC_S_')
1744: and BSC_IM_UTILS.is_like(l_source_tables(ll_index),'BSC_SB_') then
1745: l_select_sql:=l_select_sql||l_source_tables(ll_index)||'.'||l_cols(ll_fk_index)||' '||
1746: l_cols(ll_fk_index)||',';
1747: l_group_by_sql:=l_group_by_sql||l_source_tables(ll_index)||'.'||l_cols(ll_fk_index)||',';
1748: if l_eliminate(ll_index) is not null then
1777: ----------
1778: --l_dim_level_stmt:=l_dim_level_stmt||ll_temp||'+';
1779: ll_temp_alias:=substr(ll_temp,1,30-length(j))||j;
1780: if ll_dim_src_object_type='none' then
1781: if BSC_IM_UTILS.in_array(l_dim_level_tables,l_number_dim_level_tables,ll_dim_src_object)=false then
1782: l_number_dim_level_tables:=l_number_dim_level_tables+1;
1783: l_dim_level_tables(l_number_dim_level_tables):=ll_dim_src_object;
1784: end if;
1785: end if;
1785: end if;
1786: ----------
1787: if ll_rec_dim then
1788: -- ONLY FIRST LEVEL OF RECURSIVE DIMENSION SHD BE DIFFERENT
1789: if BSC_IM_UTILS.is_like(l_source_tables(ll_index),'BSC_S_') then -- higher level rec dim
1790: l_select_sql:=l_select_sql||l_source_tables(ll_index)||'.'||l_cols(ll_fk_index)||' '||l_cols(ll_fk_index)||',';
1791: l_group_by_sql:=l_group_by_sql||l_source_tables(ll_index)||'.'||l_cols(ll_fk_index)||',';
1792: else
1793: l_select_sql:=l_select_sql||'nvl('||ll_temp_alias||'.'||ll_rec_dim_key||','||
1801: end if;
1802: if ll_dim_src_object_type='inline' then
1803: l_from_sql:=l_from_sql||','||ll_dim_src_object||' '||ll_temp_alias;
1804: else
1805: if ll_dim_src_object_type ='recursive' and BSC_IM_UTILS.is_like(l_source_tables(ll_index),'BSC_S_') then
1806: null;
1807: else
1808: l_from_sql:=l_from_sql||','||bsc_im_utils.get_table_owner(ll_dim_src_object)||'.'||ll_dim_src_object||' '||ll_temp_alias;
1809: end if;
1804: else
1805: if ll_dim_src_object_type ='recursive' and BSC_IM_UTILS.is_like(l_source_tables(ll_index),'BSC_S_') then
1806: null;
1807: else
1808: l_from_sql:=l_from_sql||','||bsc_im_utils.get_table_owner(ll_dim_src_object)||'.'||ll_dim_src_object||' '||ll_temp_alias;
1809: end if;
1810: end if;
1811: l_hint_sql := l_hint_sql||ll_temp_alias||' ';
1812: if ll_rec_dim and ll_dim_src_object_type<>'recursive' then
1816: l_where_sql:=l_where_sql||' and '||ll_temp_alias||'.CODE='||l_source_tables(ll_index)||'.'||
1817: l_col_source(ll_fk_index)||' ';
1818: end if;
1819: --3613094
1820: if bsc_im_utils.is_column_in_object(ll_dim_src_object,'LANGUAGE') then
1821: l_where_sql:=l_where_sql||' and '||ll_temp_alias||'.LANGUAGE='''||BSC_IM_UTILS.get_lang||''' ';
1822: end if;
1823: if l_eliminate(ll_index) is not null then
1824: l_eliminate_sql:=l_eliminate_sql||l_eliminate(ll_index)||'.'||l_fk(j)||'='||
1817: l_col_source(ll_fk_index)||' ';
1818: end if;
1819: --3613094
1820: if bsc_im_utils.is_column_in_object(ll_dim_src_object,'LANGUAGE') then
1821: l_where_sql:=l_where_sql||' and '||ll_temp_alias||'.LANGUAGE='''||BSC_IM_UTILS.get_lang||''' ';
1822: end if;
1823: if l_eliminate(ll_index) is not null then
1824: l_eliminate_sql:=l_eliminate_sql||l_eliminate(ll_index)||'.'||l_fk(j)||'='||
1825: ll_temp_alias||'.'||l_cols(ll_fk_index)||' and ';
1845: --l_dim_level_stmt:=l_dim_level_stmt||ll_temp||'+';
1846: ----------
1847: ll_temp_alias:=substr(ll_temp,1,30-length(j))||j;
1848: if ll_dim_src_object_type='none' then
1849: if BSC_IM_UTILS.in_array(l_dim_level_tables,l_number_dim_level_tables,ll_dim_src_object)=false then
1850: l_number_dim_level_tables:=l_number_dim_level_tables+1;
1851: l_dim_level_tables(l_number_dim_level_tables):=ll_dim_src_object;
1852: end if;
1853: end if;
1850: l_number_dim_level_tables:=l_number_dim_level_tables+1;
1851: l_dim_level_tables(l_number_dim_level_tables):=ll_dim_src_object;
1852: end if;
1853: end if;
1854: if ll_rec_dim and NOT BSC_IM_UTILS.is_like(l_source_tables(ll_index),'BSC_S_') then
1855: if ll_dim_src_object_type<>'recursive' then -- DBI recursive
1856: l_select_sql:=l_select_sql||'nvl('||ll_temp_alias||'.'||ll_rec_dim_key||','||
1857: l_source_tables(ll_index)||'.'||l_cols(ll_fk_index)||') '||l_cols(ll_fk_index)||',';
1858: l_group_by_sql:=l_group_by_sql||'nvl('||ll_temp_alias||'.'||ll_rec_dim_key||','||
1868: end if;
1869: if ll_dim_src_object_type='inline' then
1870: l_from_sql:=l_from_sql||','||ll_dim_src_object||' '||ll_temp_alias;
1871: else
1872: if ll_dim_src_object_type='recursive' and BSC_IM_UTILS.is_like(l_source_tables(ll_index),'BSC_S_') then
1873: null;
1874: else
1875: l_from_sql:=l_from_sql||','||bsc_im_utils.get_table_owner(ll_dim_src_object)||'.'||ll_dim_src_object||' '||ll_temp_alias;
1876: end if;
1871: else
1872: if ll_dim_src_object_type='recursive' and BSC_IM_UTILS.is_like(l_source_tables(ll_index),'BSC_S_') then
1873: null;
1874: else
1875: l_from_sql:=l_from_sql||','||bsc_im_utils.get_table_owner(ll_dim_src_object)||'.'||ll_dim_src_object||' '||ll_temp_alias;
1876: end if;
1877: end if;
1878: l_hint_sql := l_hint_sql||ll_temp_alias||' ';
1879: if ll_rec_dim and ll_dim_src_object_type<>'recursive' then
1880: l_where_sql:=l_where_sql||' and '||ll_temp_alias||'.CODE(+)='||l_source_tables(ll_index)||'.'||
1881: l_cols(ll_fk_index)||' ';
1882: else
1883: -- for recursive dim. higher levels, we dont include the dimension
1884: if ll_rec_dim and BSC_IM_UTILS.is_like(l_source_tables(ll_index),'BSC_S_') then --
1885: null;
1886: else
1887: l_where_sql:=l_where_sql||' and '||ll_temp_alias||'.CODE='||l_source_tables(ll_index)||'.'||
1888: l_cols(ll_fk_index)||' ';
1888: l_cols(ll_fk_index)||' ';
1889: end if;
1890: end if;
1891: --3613094
1892: if bsc_im_utils.is_column_in_object(ll_dim_src_object,'LANGUAGE') then
1893: l_where_sql:=l_where_sql||' and '||ll_temp_alias||'.LANGUAGE='''||BSC_IM_UTILS.get_lang||''' ';
1894: end if;
1895: if l_eliminate(ll_index) is not null then
1896: l_eliminate_sql:=l_eliminate_sql||l_eliminate(ll_index)||'.'||l_fk(j)||'='||
1889: end if;
1890: end if;
1891: --3613094
1892: if bsc_im_utils.is_column_in_object(ll_dim_src_object,'LANGUAGE') then
1893: l_where_sql:=l_where_sql||' and '||ll_temp_alias||'.LANGUAGE='''||BSC_IM_UTILS.get_lang||''' ';
1894: end if;
1895: if l_eliminate(ll_index) is not null then
1896: l_eliminate_sql:=l_eliminate_sql||l_eliminate(ll_index)||'.'||l_fk(j)||'='||
1897: l_source_tables(ll_index)||'.'||l_cols(ll_fk_index)||' and ';
1911: ----------------------------------------------------------
1912: write_to_log_file('now measures');
1913: --second the measures
1914: declare
1915: lll_agg_columns BSC_IM_UTILS.varchar_tabletype;
1916: lll_number_agg_columns number;
1917: lll_list BSC_IM_UTILS.varchar_tabletype;
1918: lll_number_list number;
1919: lll_fk_index number;
1913: --second the measures
1914: declare
1915: lll_agg_columns BSC_IM_UTILS.varchar_tabletype;
1916: lll_number_agg_columns number;
1917: lll_list BSC_IM_UTILS.varchar_tabletype;
1918: lll_number_list number;
1919: lll_fk_index number;
1920: begin
1921: lll_number_agg_columns:=0;
1928: end if;
1929: l_select_sql:=l_select_sql||l_col_formula(j)||' '||l_cols(j)||',';
1930: --find_aggregation_columns, needed for inv refresh MV
1931: lll_number_list:=0;
1932: if BSC_IM_UTILS.find_aggregation_columns(l_col_formula(j),lll_list,
1933: lll_number_list)=false then
1934: lll_number_list:=0;
1935: end if;
1936: for k in 1..lll_number_list loop
1933: lll_number_list)=false then
1934: lll_number_list:=0;
1935: end if;
1936: for k in 1..lll_number_list loop
1937: if BSC_IM_UTILS.in_array(lll_agg_columns,lll_number_agg_columns,lll_list(k))=false then
1938: lll_number_agg_columns:=lll_number_agg_columns+1;
1939: lll_agg_columns(lll_number_agg_columns):=lll_list(k);
1940: end if;
1941: end loop;
1980: for j in 1..l_num_filter_first_level loop
1981: l_where_sql:=l_where_sql||' and '||l_source_tables(ll_index)||'.'||
1982: l_filter_first_level_fk(j)||'='||l_filter_first_level_alias(j)||'.code ';
1983: --3613094
1984: if bsc_im_utils.is_column_in_object(l_filter_first_level(j),'LANGUAGE') then
1985: l_where_sql:=l_where_sql||' and '||l_filter_first_level_alias(j)||'.language='''||BSC_IM_UTILS.get_lang||''' ';
1986: end if;
1987: end loop;
1988: for j in 1..l_number_filter loop
1981: l_where_sql:=l_where_sql||' and '||l_source_tables(ll_index)||'.'||
1982: l_filter_first_level_fk(j)||'='||l_filter_first_level_alias(j)||'.code ';
1983: --3613094
1984: if bsc_im_utils.is_column_in_object(l_filter_first_level(j),'LANGUAGE') then
1985: l_where_sql:=l_where_sql||' and '||l_filter_first_level_alias(j)||'.language='''||BSC_IM_UTILS.get_lang||''' ';
1986: end if;
1987: end loop;
1988: for j in 1..l_number_filter loop
1989: l_where_sql:=l_where_sql||' '||l_filter_where(j);
2068: ------------- ZERO CODE CALCULATIONS -------------------------
2069: ---------------------------------------------------------------
2070: declare
2071: ----------------------
2072: ll_union_creator BSC_IM_UTILS.number_tabletype;--does 3C1+3C2+3C3 etc
2073: ll_start number;
2074: ll_run number;
2075: ll_pointer number;
2076: ----------------------
2074: ll_run number;
2075: ll_pointer number;
2076: ----------------------
2077: --what union has what keys
2078: ll_union_table BSC_IM_UTILS.number_tabletype;
2079: ll_union_keys BSC_IM_UTILS.varchar_tabletype;
2080: ll_union_key_values BSC_IM_UTILS.varchar_tabletype;
2081: ll_number_union_table number;
2082: ll_union_number number;
2075: ll_pointer number;
2076: ----------------------
2077: --what union has what keys
2078: ll_union_table BSC_IM_UTILS.number_tabletype;
2079: ll_union_keys BSC_IM_UTILS.varchar_tabletype;
2080: ll_union_key_values BSC_IM_UTILS.varchar_tabletype;
2081: ll_number_union_table number;
2082: ll_union_number number;
2083: ----------------------
2076: ----------------------
2077: --what union has what keys
2078: ll_union_table BSC_IM_UTILS.number_tabletype;
2079: ll_union_keys BSC_IM_UTILS.varchar_tabletype;
2080: ll_union_key_values BSC_IM_UTILS.varchar_tabletype;
2081: ll_number_union_table number;
2082: ll_union_number number;
2083: ----------------------
2084: ll_count number;
2098: write_to_log_file('no. of parameters='||l_number_parameters);
2099: end if;
2100: for j in 1..l_number_parameters loop
2101: if l_calculation_table(j)=l_tables(ll_index) then
2102: if BSC_IM_UTILS.in_array(ll_rollup_fk,ll_number_rollup_fk,l_parameter1(j))=false then
2103: ll_number_rollup_fk:=ll_number_rollup_fk+1;
2104: ll_rollup_fk(ll_number_rollup_fk):=l_parameter1(j);
2105: ll_rollup_fk_value(ll_number_rollup_fk):=l_parameter2(j);
2106: end if;
2154: end loop;
2155: ll_pointer:=j;--the last element
2156: -----------
2157: --add the keys into the union pl/sql table
2158: --ll_union_number BSC_IM_UTILS.number_tabletype;
2159: --ll_union_keys BSC_IM_UTILS.varchar_tabletype;
2160: --ll_number_union_table number;
2161: loop
2162: ll_union_number:=ll_union_number+1;
2155: ll_pointer:=j;--the last element
2156: -----------
2157: --add the keys into the union pl/sql table
2158: --ll_union_number BSC_IM_UTILS.number_tabletype;
2159: --ll_union_keys BSC_IM_UTILS.varchar_tabletype;
2160: --ll_number_union_table number;
2161: loop
2162: ll_union_number:=ll_union_number+1;
2163: for k in 1..j loop
2201: end if;
2202: -------------------------------------------
2203: --at this point, we have the info on union for 1 at a time, 2 at a time etc
2204: l_rollup_full_select_sql:=' select ';
2205: l_rollup_full_from_sql:=' from '||bsc_im_utils.get_table_owner(p_mv_name)||'.'||p_mv_name||' '||p_mv_name;
2206: l_rollup_full_where_sql:=null;
2207: l_rollup_full_group_by_sql:=' group by ';
2208: --loop for each union
2209: for j in 1..ll_union_number loop
2217: write_to_log_file_n('going to process the keys');
2218: end if;
2219: declare
2220: ll_index number;
2221: ll_fk_cube BSC_IM_UTILS.varchar_tabletype;
2222: ll_number_fk_cube number;
2223: begin
2224: if ll_use_union_for_rollup then
2225: for j in 1..ll_union_number loop
2224: if ll_use_union_for_rollup then
2225: for j in 1..ll_union_number loop
2226: for k in 1..l_number_fk loop
2227: ll_index:=0;
2228: ll_index:=BSC_IM_UTILS.get_index(ll_union_keys,ll_union_table,ll_number_union_table,l_fk(k),j);
2229: if ll_index>0 then --this key is in the rollup
2230: l_rollup_select_sql(j):=l_rollup_select_sql(j)||ll_union_key_values(ll_index)||' '||
2231: ll_union_keys(ll_index)||',';
2232: else
2243: end if;
2244: ll_number_fk_cube:=0;
2245: for k in 1..l_number_fk loop
2246: ll_index:=0;
2247: ll_index:=BSC_IM_UTILS.get_index(ll_rollup_fk,ll_number_rollup_fk,l_fk(k));
2248: if ll_index>0 then --this key is in the rollup
2249: l_rollup_full_select_sql:=l_rollup_full_select_sql||'decode(grouping('||ll_rollup_fk(ll_index)||'),'||
2250: '1,'||ll_rollup_fk_value(ll_index)||','||ll_rollup_fk(ll_index)||') '||ll_rollup_fk(ll_index)||',';
2251: ll_number_fk_cube:=ll_number_fk_cube+1;
2274: if g_debug then
2275: write_to_log_file_n('going to process the measures');
2276: end if;
2277: declare
2278: ll_looked_at BSC_IM_UTILS.varchar_tabletype;
2279: ll_number_looked_at number;
2280: begin
2281: if ll_use_union_for_rollup then
2282: for j in 1..ll_union_number loop
2284: for k in 1..l_number_cols loop
2285: if l_col_type(k)='A' then
2286: for m in 1..l_number_parameters loop
2287: if lower(l_parameter3(m))=lower(l_cols(k)) then
2288: if BSC_IM_UTILS.in_array(ll_looked_at,ll_number_looked_at,l_parameter3(m))=false then
2289: l_rollup_select_sql(j):=l_rollup_select_sql(j)||
2290: l_parameter5(m)||' '||l_parameter3(m)||',';
2291: l_rollup_select_sql(j):=l_rollup_select_sql(j)||'count('||l_parameter3(m)||') '||
2292: substr('cnt_'||l_parameter3(m),1,30)||',';
2309: for k in 1..l_number_cols loop
2310: if l_col_type(k)='A' then
2311: for m in 1..l_number_parameters loop
2312: if lower(l_parameter3(m))=lower(l_cols(k)) then
2313: if BSC_IM_UTILS.in_array(ll_looked_at,ll_number_looked_at,l_parameter3(m))=false then
2314: l_rollup_full_select_sql:=l_rollup_full_select_sql||
2315: l_parameter5(m)||' '||l_parameter3(m)||',';
2316: ll_number_looked_at:=ll_number_looked_at+1;
2317: ll_looked_at(ll_number_looked_at):=l_parameter3(m);
2491: --create the fk and columns for dim levels. this info will be used for snapshot log creation.
2492: --we need snapshot logs also on the dim level tables for inc refresh
2493: --l_number_dim_level_tables
2494: declare
2495: ll_level_columns BSC_IM_UTILS.varchar_tabletype;
2496: ll_level_column_type BSC_IM_UTILS.varchar_tabletype;
2497: ll_number_level_columns number;
2498: begin
2499: for i in 1..l_number_dim_level_tables loop
2492: --we need snapshot logs also on the dim level tables for inc refresh
2493: --l_number_dim_level_tables
2494: declare
2495: ll_level_columns BSC_IM_UTILS.varchar_tabletype;
2496: ll_level_column_type BSC_IM_UTILS.varchar_tabletype;
2497: ll_number_level_columns number;
2498: begin
2499: for i in 1..l_number_dim_level_tables loop
2500: if get_dim_level_cols(
2509: 'LEVEL TABLE')=false then
2510: return false;
2511: end if;
2512: --3613094
2513: if bsc_im_utils.is_column_in_object(l_dim_level_tables(i),'LANGUAGE') then
2514: if BSC_IM_INT_MD.create_fk('LANGUAGE','LEVEL TABLE',l_dim_level_tables(i),null,null,null,'BSC',
2515: 'LEVEL TABLE')=false then
2516: return false;
2517: end if;
2543: if BSC_IM_INT_MD.create_fk('DIM_LEVEL_VALUE','LEVEL TABLE','BSC_SYS_FILTERS',null,null,null,'BSC',
2544: 'LEVEL TABLE')=false then
2545: return false;
2546: end if;
2547: if BSC_IM_UTILS.in_array(l_dim_level_tables,l_number_dim_level_tables,'BSC_SYS_FILTERS')=false then
2548: l_number_dim_level_tables:=l_number_dim_level_tables+1;
2549: l_dim_level_tables(l_number_dim_level_tables):='BSC_SYS_FILTERS';
2550: end if;
2551: end if;
2581: write_to_log_file_n('Returning from Create_kpi_map_info'||' '||get_time);
2582: end if;
2583: return true;
2584: Exception when others then
2585: BSC_IM_UTILS.g_status_message:=sqlerrm;
2586: g_status_message:=sqlerrm;
2587: write_to_log_file_n('Exception in create_kpi_map_info '||sqlerrm);
2588: return false;
2589: End;
2591: --used for T table. will go recursively all the way back to the B table and generate the sql.
2592: function get_table_sql(
2593: p_table varchar2,
2594: p_table_sql out nocopy varchar2,
2595: p_b_tables in out nocopy BSC_IM_UTILS.varchar_tabletype,
2596: p_number_b_tables in out nocopy number,
2597: p_dim_level_tables in out nocopy BSC_IM_UTILS.varchar_tabletype,
2598: p_number_dim_level_tables in out nocopy number
2599: )return boolean is
2593: p_table varchar2,
2594: p_table_sql out nocopy varchar2,
2595: p_b_tables in out nocopy BSC_IM_UTILS.varchar_tabletype,
2596: p_number_b_tables in out nocopy number,
2597: p_dim_level_tables in out nocopy BSC_IM_UTILS.varchar_tabletype,
2598: p_number_dim_level_tables in out nocopy number
2599: )return boolean is
2600: -----------------------------------------------------------------
2601: l_tables BSC_IM_UTILS.varchar_tabletype;
2597: p_dim_level_tables in out nocopy BSC_IM_UTILS.varchar_tabletype,
2598: p_number_dim_level_tables in out nocopy number
2599: )return boolean is
2600: -----------------------------------------------------------------
2601: l_tables BSC_IM_UTILS.varchar_tabletype;
2602: l_source_tables BSC_IM_UTILS.varchar_tabletype;
2603: l_relation_type BSC_IM_UTILS.varchar_tabletype;
2604: l_source_sql BSC_IM_UTILS.varchar_tabletype;
2605: l_number_source_tables number;
2598: p_number_dim_level_tables in out nocopy number
2599: )return boolean is
2600: -----------------------------------------------------------------
2601: l_tables BSC_IM_UTILS.varchar_tabletype;
2602: l_source_tables BSC_IM_UTILS.varchar_tabletype;
2603: l_relation_type BSC_IM_UTILS.varchar_tabletype;
2604: l_source_sql BSC_IM_UTILS.varchar_tabletype;
2605: l_number_source_tables number;
2606: -----------T table columns--------------------------------------
2599: )return boolean is
2600: -----------------------------------------------------------------
2601: l_tables BSC_IM_UTILS.varchar_tabletype;
2602: l_source_tables BSC_IM_UTILS.varchar_tabletype;
2603: l_relation_type BSC_IM_UTILS.varchar_tabletype;
2604: l_source_sql BSC_IM_UTILS.varchar_tabletype;
2605: l_number_source_tables number;
2606: -----------T table columns--------------------------------------
2607: l_col_table BSC_IM_UTILS.varchar_tabletype;
2600: -----------------------------------------------------------------
2601: l_tables BSC_IM_UTILS.varchar_tabletype;
2602: l_source_tables BSC_IM_UTILS.varchar_tabletype;
2603: l_relation_type BSC_IM_UTILS.varchar_tabletype;
2604: l_source_sql BSC_IM_UTILS.varchar_tabletype;
2605: l_number_source_tables number;
2606: -----------T table columns--------------------------------------
2607: l_col_table BSC_IM_UTILS.varchar_tabletype;
2608: l_cols BSC_IM_UTILS.varchar_tabletype;
2603: l_relation_type BSC_IM_UTILS.varchar_tabletype;
2604: l_source_sql BSC_IM_UTILS.varchar_tabletype;
2605: l_number_source_tables number;
2606: -----------T table columns--------------------------------------
2607: l_col_table BSC_IM_UTILS.varchar_tabletype;
2608: l_cols BSC_IM_UTILS.varchar_tabletype;
2609: l_col_type BSC_IM_UTILS.varchar_tabletype;
2610: l_col_formula BSC_IM_UTILS.varchar_tabletype;
2611: l_col_source BSC_IM_UTILS.varchar_tabletype;
2604: l_source_sql BSC_IM_UTILS.varchar_tabletype;
2605: l_number_source_tables number;
2606: -----------T table columns--------------------------------------
2607: l_col_table BSC_IM_UTILS.varchar_tabletype;
2608: l_cols BSC_IM_UTILS.varchar_tabletype;
2609: l_col_type BSC_IM_UTILS.varchar_tabletype;
2610: l_col_formula BSC_IM_UTILS.varchar_tabletype;
2611: l_col_source BSC_IM_UTILS.varchar_tabletype;
2612: l_number_cols number;
2605: l_number_source_tables number;
2606: -----------T table columns--------------------------------------
2607: l_col_table BSC_IM_UTILS.varchar_tabletype;
2608: l_cols BSC_IM_UTILS.varchar_tabletype;
2609: l_col_type BSC_IM_UTILS.varchar_tabletype;
2610: l_col_formula BSC_IM_UTILS.varchar_tabletype;
2611: l_col_source BSC_IM_UTILS.varchar_tabletype;
2612: l_number_cols number;
2613: -----------------------------------------------------------------
2606: -----------T table columns--------------------------------------
2607: l_col_table BSC_IM_UTILS.varchar_tabletype;
2608: l_cols BSC_IM_UTILS.varchar_tabletype;
2609: l_col_type BSC_IM_UTILS.varchar_tabletype;
2610: l_col_formula BSC_IM_UTILS.varchar_tabletype;
2611: l_col_source BSC_IM_UTILS.varchar_tabletype;
2612: l_number_cols number;
2613: -----------------------------------------------------------------
2614: l_fk BSC_IM_UTILS.varchar_tabletype;
2607: l_col_table BSC_IM_UTILS.varchar_tabletype;
2608: l_cols BSC_IM_UTILS.varchar_tabletype;
2609: l_col_type BSC_IM_UTILS.varchar_tabletype;
2610: l_col_formula BSC_IM_UTILS.varchar_tabletype;
2611: l_col_source BSC_IM_UTILS.varchar_tabletype;
2612: l_number_cols number;
2613: -----------------------------------------------------------------
2614: l_fk BSC_IM_UTILS.varchar_tabletype;
2615: l_fk_source BSC_IM_UTILS.varchar_tabletype;
2610: l_col_formula BSC_IM_UTILS.varchar_tabletype;
2611: l_col_source BSC_IM_UTILS.varchar_tabletype;
2612: l_number_cols number;
2613: -----------------------------------------------------------------
2614: l_fk BSC_IM_UTILS.varchar_tabletype;
2615: l_fk_source BSC_IM_UTILS.varchar_tabletype;
2616: l_number_fk number;
2617: l_measures BSC_IM_UTILS.varchar_tabletype;
2618: l_number_measures number;
2611: l_col_source BSC_IM_UTILS.varchar_tabletype;
2612: l_number_cols number;
2613: -----------------------------------------------------------------
2614: l_fk BSC_IM_UTILS.varchar_tabletype;
2615: l_fk_source BSC_IM_UTILS.varchar_tabletype;
2616: l_number_fk number;
2617: l_measures BSC_IM_UTILS.varchar_tabletype;
2618: l_number_measures number;
2619: -----------------------------------------------------------------
2613: -----------------------------------------------------------------
2614: l_fk BSC_IM_UTILS.varchar_tabletype;
2615: l_fk_source BSC_IM_UTILS.varchar_tabletype;
2616: l_number_fk number;
2617: l_measures BSC_IM_UTILS.varchar_tabletype;
2618: l_number_measures number;
2619: -----------------------------------------------------------------
2620: l_column_merge_sql varchar2(32000);
2621: l_column_merge_group BSC_IM_UTILS.varchar_tabletype;
2617: l_measures BSC_IM_UTILS.varchar_tabletype;
2618: l_number_measures number;
2619: -----------------------------------------------------------------
2620: l_column_merge_sql varchar2(32000);
2621: l_column_merge_group BSC_IM_UTILS.varchar_tabletype;
2622: l_column_merge_group_sql BSC_IM_UTILS.varchar_tabletype;
2623: l_number_column_merge_group number;
2624: l_merge_sql varchar2(32000);
2625: -----------------------------------------------------------------
2618: l_number_measures number;
2619: -----------------------------------------------------------------
2620: l_column_merge_sql varchar2(32000);
2621: l_column_merge_group BSC_IM_UTILS.varchar_tabletype;
2622: l_column_merge_group_sql BSC_IM_UTILS.varchar_tabletype;
2623: l_number_column_merge_group number;
2624: l_merge_sql varchar2(32000);
2625: -----------------------------------------------------------------
2626: l_table_measures BSC_IM_UTILS.varchar_tabletype;
2622: l_column_merge_group_sql BSC_IM_UTILS.varchar_tabletype;
2623: l_number_column_merge_group number;
2624: l_merge_sql varchar2(32000);
2625: -----------------------------------------------------------------
2626: l_table_measures BSC_IM_UTILS.varchar_tabletype;
2627: l_number_table_measures number;
2628: l_index number;
2629: -----------------------------------------------------------------
2630: l_from_sql varchar2(32000);
2657: l_number_source_tables)=false then
2658: return false;
2659: end if;
2660: for i in 1..l_number_source_tables loop
2661: if BSC_IM_UTILS.is_like(l_source_tables(i),'BSC_T_') then --this is T table
2662: --construct the sql. recursive call
2663: if get_table_sql(l_source_tables(i),l_source_sql(i),p_b_tables,p_number_b_tables,
2664: p_dim_level_tables,p_number_dim_level_tables)=false then
2665: return false;
2668: l_source_sql(i):=null;
2669: end if;
2670: end loop;
2671: for i in 1..l_number_source_tables loop
2672: if BSC_IM_UTILS.is_like(l_source_tables(i),'BSC_B_') and BSC_IM_UTILS.in_array(p_b_tables,p_number_b_tables,
2673: l_source_tables(i))=false then
2674: p_number_b_tables:=p_number_b_tables+1;
2675: p_b_tables(p_number_b_tables):=l_source_tables(i);
2676: end if;
2691: when T table rolls up, we cannot use l_fk() in l_column_merge_sql. l_fk is the fk
2692: of the higher level. we need to get the source of l_fk and use it
2693: */
2694: for i in 1..l_number_fk loop
2695: l_index:=BSC_IM_UTILS.get_index(l_cols,l_number_cols,l_fk(i));
2696: if l_index>0 then
2697: l_fk_source(i):=l_col_source(l_index);
2698: else
2699: l_fk_source(i):=l_fk(i);
2786: -- get prj union clause will return (B union B_PRJ) aliased as B
2787: -- for T tables, we should remove this alias as we're going to use the prim_table alias
2788: l_from_sql:= l_from_sql||get_prj_union_clause(l_source_tables(l_number_source_tables), false);
2789: else
2790: l_from_sql := l_from_sql||bsc_im_utils.get_table_owner(l_source_tables(l_number_source_tables))
2791: ||'.'||l_source_tables(l_number_source_tables);
2792: end if;
2793: l_from_sql := l_from_sql||' prim_table';
2794: else
2809: p_table_sql:=p_table_sql||l_temp_alias||'.'||l_fk(i)||',';
2810: if l_dim_src_object_type='inline' then
2811: l_from_sql:=l_from_sql||','||l_dim_src_object||' '||l_temp_alias;
2812: else
2813: l_from_sql:=l_from_sql||','||bsc_im_utils.get_table_owner(l_dim_src_object)||'.'||l_dim_src_object||' '||l_temp_alias;
2814: end if;
2815: if l_number_source_tables=1 then
2816: l_where_sql:=l_where_sql||' and '||l_temp_alias||'.CODE=prim_table.'||l_fk_source(i);
2817: else
2819: end if;
2820: --bug 3344807
2821: --when the dimension has multiple languages, we must filter by language
2822: --3613094
2823: if bsc_im_utils.is_column_in_object(l_dim_src_object,'LANGUAGE') then
2824: l_where_sql:=l_where_sql||' and '||l_temp_alias||'.LANGUAGE='''||BSC_IM_UTILS.get_lang||''' ';
2825: end if;
2826: l_group_by_sql:=l_group_by_sql||l_temp_alias||'.'||l_fk(i)||',';--l_fk(i)=l_cols(l_index)
2827: if l_dim_src_object_type='none' then
2820: --bug 3344807
2821: --when the dimension has multiple languages, we must filter by language
2822: --3613094
2823: if bsc_im_utils.is_column_in_object(l_dim_src_object,'LANGUAGE') then
2824: l_where_sql:=l_where_sql||' and '||l_temp_alias||'.LANGUAGE='''||BSC_IM_UTILS.get_lang||''' ';
2825: end if;
2826: l_group_by_sql:=l_group_by_sql||l_temp_alias||'.'||l_fk(i)||',';--l_fk(i)=l_cols(l_index)
2827: if l_dim_src_object_type='none' then
2828: if BSC_IM_UTILS.in_array(p_dim_level_tables,p_number_dim_level_tables,l_dim_src_object)=false then
2824: l_where_sql:=l_where_sql||' and '||l_temp_alias||'.LANGUAGE='''||BSC_IM_UTILS.get_lang||''' ';
2825: end if;
2826: l_group_by_sql:=l_group_by_sql||l_temp_alias||'.'||l_fk(i)||',';--l_fk(i)=l_cols(l_index)
2827: if l_dim_src_object_type='none' then
2828: if BSC_IM_UTILS.in_array(p_dim_level_tables,p_number_dim_level_tables,l_dim_src_object)=false then
2829: p_number_dim_level_tables:=p_number_dim_level_tables+1;
2830: p_dim_level_tables(p_number_dim_level_tables):=l_dim_src_object;
2831: end if;
2832: end if;
2837: end loop;--for i in 1..l_number_fk loop
2838: l_group_by_sql:=substr(l_group_by_sql,1,length(l_group_by_sql)-1);
2839: for i in 1..l_number_measures loop
2840: l_index:=0;
2841: l_index:=BSC_IM_UTILS.get_index(l_cols,l_number_cols,l_measures(i));
2842: if l_index>0 then
2843: p_table_sql:=p_table_sql||l_col_formula(l_index)||' '||l_measures(i)||',';
2844: end if;
2845: end loop;
2858: end if;
2859: p_table_sql:=p_table_sql||l_from_sql||l_where_sql||' group by '||l_group_by_sql;
2860: return true;
2861: Exception when others then
2862: BSC_IM_UTILS.g_status_message:=sqlerrm;
2863: g_status_message:=sqlerrm;
2864: write_to_log_file_n('Exception in get_table_sql '||sqlerrm);
2865: return false;
2866: End;
2867:
2868: function get_filter_stmt(
2869: p_indicator number,
2870: p_table varchar2,
2871: p_filter_from out nocopy BSC_IM_UTILS.varchar_tabletype,
2872: p_filter_where out nocopy BSC_IM_UTILS.varchar_tabletype,
2873: p_number_filter out nocopy number,
2874: p_dim_level_tables in out nocopy BSC_IM_UTILS.varchar_tabletype,
2875: p_number_dim_level_tables in out nocopy number,
2868: function get_filter_stmt(
2869: p_indicator number,
2870: p_table varchar2,
2871: p_filter_from out nocopy BSC_IM_UTILS.varchar_tabletype,
2872: p_filter_where out nocopy BSC_IM_UTILS.varchar_tabletype,
2873: p_number_filter out nocopy number,
2874: p_dim_level_tables in out nocopy BSC_IM_UTILS.varchar_tabletype,
2875: p_number_dim_level_tables in out nocopy number,
2876: p_filter_first_level out nocopy BSC_IM_UTILS.varchar_tabletype,
2870: p_table varchar2,
2871: p_filter_from out nocopy BSC_IM_UTILS.varchar_tabletype,
2872: p_filter_where out nocopy BSC_IM_UTILS.varchar_tabletype,
2873: p_number_filter out nocopy number,
2874: p_dim_level_tables in out nocopy BSC_IM_UTILS.varchar_tabletype,
2875: p_number_dim_level_tables in out nocopy number,
2876: p_filter_first_level out nocopy BSC_IM_UTILS.varchar_tabletype,
2877: p_filter_first_level_alias out nocopy BSC_IM_UTILS.varchar_tabletype, --this will be the alias L1
2878: p_filter_first_level_fk out nocopy BSC_IM_UTILS.varchar_tabletype,
2872: p_filter_where out nocopy BSC_IM_UTILS.varchar_tabletype,
2873: p_number_filter out nocopy number,
2874: p_dim_level_tables in out nocopy BSC_IM_UTILS.varchar_tabletype,
2875: p_number_dim_level_tables in out nocopy number,
2876: p_filter_first_level out nocopy BSC_IM_UTILS.varchar_tabletype,
2877: p_filter_first_level_alias out nocopy BSC_IM_UTILS.varchar_tabletype, --this will be the alias L1
2878: p_filter_first_level_fk out nocopy BSC_IM_UTILS.varchar_tabletype,
2879: p_num_filter_first_level out nocopy number
2880: ) return boolean is
2873: p_number_filter out nocopy number,
2874: p_dim_level_tables in out nocopy BSC_IM_UTILS.varchar_tabletype,
2875: p_number_dim_level_tables in out nocopy number,
2876: p_filter_first_level out nocopy BSC_IM_UTILS.varchar_tabletype,
2877: p_filter_first_level_alias out nocopy BSC_IM_UTILS.varchar_tabletype, --this will be the alias L1
2878: p_filter_first_level_fk out nocopy BSC_IM_UTILS.varchar_tabletype,
2879: p_num_filter_first_level out nocopy number
2880: ) return boolean is
2881: --------------------------
2874: p_dim_level_tables in out nocopy BSC_IM_UTILS.varchar_tabletype,
2875: p_number_dim_level_tables in out nocopy number,
2876: p_filter_first_level out nocopy BSC_IM_UTILS.varchar_tabletype,
2877: p_filter_first_level_alias out nocopy BSC_IM_UTILS.varchar_tabletype, --this will be the alias L1
2878: p_filter_first_level_fk out nocopy BSC_IM_UTILS.varchar_tabletype,
2879: p_num_filter_first_level out nocopy number
2880: ) return boolean is
2881: --------------------------
2882: l_stmt varchar2(5000);
2882: l_stmt varchar2(5000);
2883: --------------------------
2884: l_dim_set_id number;
2885: l_indicator_id number;
2886: l_pk_col BSC_IM_UTILS.varchar_tabletype;
2887: l_level_view BSC_IM_UTILS.varchar_tabletype;
2888: l_number_pk_col number;
2889: l_view_owner varchar2(200);
2890: --------------------------
2883: --------------------------
2884: l_dim_set_id number;
2885: l_indicator_id number;
2886: l_pk_col BSC_IM_UTILS.varchar_tabletype;
2887: l_level_view BSC_IM_UTILS.varchar_tabletype;
2888: l_number_pk_col number;
2889: l_view_owner varchar2(200);
2890: --------------------------
2891: cursor c1(p_indicator number,p_dim_set_id number,p_status number,p_table_name varchar2,p_column_type varchar2) is
2943: end loop;
2944: end if;
2945: if l_number_pk_col>0 then
2946: declare
2947: ll_level_table_name BSC_IM_UTILS.varchar_tabletype;
2948: ll_level_pk_col BSC_IM_UTILS.varchar_tabletype;
2949: ll_parent_level_pk_col BSC_IM_UTILS.varchar_tabletype;
2950: ll_number_level number;
2951: ---------------------
2944: end if;
2945: if l_number_pk_col>0 then
2946: declare
2947: ll_level_table_name BSC_IM_UTILS.varchar_tabletype;
2948: ll_level_pk_col BSC_IM_UTILS.varchar_tabletype;
2949: ll_parent_level_pk_col BSC_IM_UTILS.varchar_tabletype;
2950: ll_number_level number;
2951: ---------------------
2952: ll_index number;
2945: if l_number_pk_col>0 then
2946: declare
2947: ll_level_table_name BSC_IM_UTILS.varchar_tabletype;
2948: ll_level_pk_col BSC_IM_UTILS.varchar_tabletype;
2949: ll_parent_level_pk_col BSC_IM_UTILS.varchar_tabletype;
2950: ll_number_level number;
2951: ---------------------
2952: ll_index number;
2953: ---------------------
2988: for i in 1..l_number_pk_col loop
2989: ll_pk_col:=l_pk_col(i);
2990: if ll_pk_col is not null then
2991: ll_index:=0;
2992: ll_index:=BSC_IM_UTILS.get_index(ll_level_pk_col,ll_number_level,ll_pk_col);
2993: if ll_index>0 then
2994: p_num_filter_first_level:=p_num_filter_first_level+1;
2995: ll_level_count:=ll_level_count+1;
2996: p_filter_first_level(p_num_filter_first_level):=ll_level_table_name(ll_index);
3015: end if;--if l_number_pk_col>0 then
3016: end if;--if l_indicator_id is not null and l_dim_set_id is not null then
3017: return true;
3018: Exception when others then
3019: BSC_IM_UTILS.g_status_message:=sqlerrm;
3020: g_status_message:=sqlerrm;
3021: write_to_log_file_n('Exception in get_filter_stmt '||sqlerrm);
3022: return false;
3023: End;
3031: p_level_count in out nocopy number,
3032: p_from_stmt in out nocopy varchar2,
3033: p_where_stmt in out nocopy varchar2,
3034: --added by arun
3035: p_pk_cols BSC_IM_UTILS.varchar_tabletype
3036: ) return boolean is
3037: --------------------------------
3038: l_level_fk BSC_IM_UTILS.varchar_tabletype;
3039: l_parent_level BSC_IM_UTILS.varchar_tabletype;
3034: --added by arun
3035: p_pk_cols BSC_IM_UTILS.varchar_tabletype
3036: ) return boolean is
3037: --------------------------------
3038: l_level_fk BSC_IM_UTILS.varchar_tabletype;
3039: l_parent_level BSC_IM_UTILS.varchar_tabletype;
3040: l_num_parent_levels number;
3041: --------------------------------
3042: l_source_type number;
3035: p_pk_cols BSC_IM_UTILS.varchar_tabletype
3036: ) return boolean is
3037: --------------------------------
3038: l_level_fk BSC_IM_UTILS.varchar_tabletype;
3039: l_parent_level BSC_IM_UTILS.varchar_tabletype;
3040: l_num_parent_levels number;
3041: --------------------------------
3042: l_source_type number;
3043: l_source_code number;
3057: l_res:=get_filter_view_params(p_indicator, p_child_level,l_source_type,l_source_code,l_dim_level_id);
3058: if l_res=1 then
3059: --there is a filter at this level. use it
3060: p_count:=p_count+1;
3061: p_from_stmt:=p_from_stmt||bsc_im_utils.get_table_owner(p_child_level)||'.'||p_child_level||' L'||p_level_count||
3062: ','||bsc_im_utils.get_table_owner('BSC_SYS_FILTERS')||'.bsc_sys_filters f'||p_count||',';
3063: p_where_stmt:=p_where_stmt||' and f'||p_count||'.source_code='||l_source_code||
3064: ' and f'||p_count||'.source_type='||l_source_type||
3065: ' and f'||p_count||'.dim_level_id='||l_dim_level_id||
3058: if l_res=1 then
3059: --there is a filter at this level. use it
3060: p_count:=p_count+1;
3061: p_from_stmt:=p_from_stmt||bsc_im_utils.get_table_owner(p_child_level)||'.'||p_child_level||' L'||p_level_count||
3062: ','||bsc_im_utils.get_table_owner('BSC_SYS_FILTERS')||'.bsc_sys_filters f'||p_count||',';
3063: p_where_stmt:=p_where_stmt||' and f'||p_count||'.source_code='||l_source_code||
3064: ' and f'||p_count||'.source_type='||l_source_type||
3065: ' and f'||p_count||'.dim_level_id='||l_dim_level_id||
3066: ' and f'||p_count||'.dim_level_value=L'||p_level_count||'.code ';
3064: ' and f'||p_count||'.source_type='||l_source_type||
3065: ' and f'||p_count||'.dim_level_id='||l_dim_level_id||
3066: ' and f'||p_count||'.dim_level_value=L'||p_level_count||'.code ';
3067: --bug 3404374
3068: --' and '||p_child_level||'.language='''||BSC_IM_UTILS.get_lang||'''';
3069: else
3070: if get_parent_level(p_child_level,l_level_fk,l_parent_level,l_num_parent_levels)=false then
3071: return false;
3072: end if;
3073: if l_num_parent_levels>0 then
3074: for i in 1..l_num_parent_levels loop
3075: l_res:=get_filter_view_params(p_indicator, l_parent_level(i),l_source_type,l_source_code,l_dim_level_id);
3076: if l_source_type is not null and
3077: bsc_im_utils.get_index(p_pk_cols, p_pk_cols.count, l_parent_level(i)) <1 then
3078: --there is a filter view on this level
3079: l_parent_level_count:=p_level_count+1;
3080: p_from_stmt:=p_from_stmt||bsc_im_utils.get_table_owner(p_child_level)||'.'||p_child_level||
3081: ' L'||p_level_count||',';
3076: if l_source_type is not null and
3077: bsc_im_utils.get_index(p_pk_cols, p_pk_cols.count, l_parent_level(i)) <1 then
3078: --there is a filter view on this level
3079: l_parent_level_count:=p_level_count+1;
3080: p_from_stmt:=p_from_stmt||bsc_im_utils.get_table_owner(p_child_level)||'.'||p_child_level||
3081: ' L'||p_level_count||',';
3082: p_where_stmt:=p_where_stmt||' and L'||p_level_count||'.'||l_level_fk(i)||'=L'||l_parent_level_count||
3083: '.code';
3084: --3613094
3081: ' L'||p_level_count||',';
3082: p_where_stmt:=p_where_stmt||' and L'||p_level_count||'.'||l_level_fk(i)||'=L'||l_parent_level_count||
3083: '.code';
3084: --3613094
3085: if bsc_im_utils.is_column_in_object(l_parent_level(i),'LANGUAGE') then
3086: p_where_stmt:=p_where_stmt||' and L'||l_parent_level_count||'.language='''||BSC_IM_UTILS.get_lang||'''';
3087: end if;
3088: p_level_count:=p_level_count+1;
3089: if get_filter_stmt_rec(p_indicator, l_parent_level(i),l_level_fk(i),p_count,p_level_count,
3082: p_where_stmt:=p_where_stmt||' and L'||p_level_count||'.'||l_level_fk(i)||'=L'||l_parent_level_count||
3083: '.code';
3084: --3613094
3085: if bsc_im_utils.is_column_in_object(l_parent_level(i),'LANGUAGE') then
3086: p_where_stmt:=p_where_stmt||' and L'||l_parent_level_count||'.language='''||BSC_IM_UTILS.get_lang||'''';
3087: end if;
3088: p_level_count:=p_level_count+1;
3089: if get_filter_stmt_rec(p_indicator, l_parent_level(i),l_level_fk(i),p_count,p_level_count,
3090: p_from_stmt,p_where_stmt, p_pk_cols)=false then
3095: end if;
3096: end if;
3097: return true;
3098: Exception when others then
3099: BSC_IM_UTILS.g_status_message:=sqlerrm;
3100: g_status_message:=sqlerrm;
3101: write_to_log_file_n('Exception in get_filter_stmt_rec '||sqlerrm);
3102: return false;
3103: End;
3147: write_to_log_file('l_res='||l_res);
3148: end if;
3149: return l_res;
3150: Exception when others then
3151: BSC_IM_UTILS.g_status_message:=sqlerrm;
3152: g_status_message:=sqlerrm;
3153: write_to_log_file_n('Exception in get_filter_view_params '||sqlerrm);
3154: return -1;
3155: End;
3155: End;
3156:
3157: function get_parent_level(
3158: p_child_level varchar2,
3159: p_level_fk out nocopy BSC_IM_UTILS.varchar_tabletype,
3160: p_parent_level out nocopy BSC_IM_UTILS.varchar_tabletype,
3161: p_num_parent_levels out nocopy number
3162: ) return boolean is
3163: -------------
3156:
3157: function get_parent_level(
3158: p_child_level varchar2,
3159: p_level_fk out nocopy BSC_IM_UTILS.varchar_tabletype,
3160: p_parent_level out nocopy BSC_IM_UTILS.varchar_tabletype,
3161: p_num_parent_levels out nocopy number
3162: ) return boolean is
3163: -------------
3164: cursor c1(p_level varchar2) is
3196: end loop;
3197: end if;
3198: return true;
3199: Exception when others then
3200: BSC_IM_UTILS.g_status_message:=sqlerrm;
3201: g_status_message:=sqlerrm;
3202: write_to_log_file_n('Exception in get_parent_level '||sqlerrm);
3203: return false;
3204: End;
3203: return false;
3204: End;
3205:
3206: function is_recursive_dim(p_child_level in varchar2, p_result OUT NOCOPY boolean) return boolean is
3207: l_level_fk BSC_IM_UTILS.varchar_tabletype;
3208: l_parent_level BSC_IM_UTILS.varchar_tabletype;
3209: l_num_parent_levels number;
3210:
3211: begin
3204: End;
3205:
3206: function is_recursive_dim(p_child_level in varchar2, p_result OUT NOCOPY boolean) return boolean is
3207: l_level_fk BSC_IM_UTILS.varchar_tabletype;
3208: l_parent_level BSC_IM_UTILS.varchar_tabletype;
3209: l_num_parent_levels number;
3210:
3211: begin
3212: p_result := false;
3244: open c_short_name;
3245: fetch c_short_name into l_short_name;
3246: close c_short_name;
3247: l_denorm_table := BSC_DBGEN_METADATA_READER.get_denorm_dimension_table(l_short_name);
3248: l_owner := bsc_im_utils.get_table_owner(p_dim_level);
3249:
3250: open cDataType(l_owner);
3251: fetch cDataType into l_data_type;
3252: close cDataType;
3278: end;
3279: return l_denorm_table;
3280:
3281: Exception when others then
3282: BSC_IM_UTILS.g_status_message:=sqlerrm;
3283: g_status_message:=sqlerrm;
3284: write_to_log_file_n('Exception in create_denorm_table: p_dim_level='||p_dim_level||', pk='||p_level_pk||', stmt='||l_stmt||', error='||sqlerrm);
3285: if (l_data_type is null) then
3286: write_to_log_file('PK Column '||p_level_pk||' specified in bsc_sys_dim_levels_b does not exist in '||p_dim_level);
3394: end if;
3395: ---
3396: return true;
3397: Exception when others then
3398: BSC_IM_UTILS.g_status_message:=sqlerrm;
3399: g_status_message:=sqlerrm;
3400: write_to_log_file_n('Exception in get_level_for_pk '||sqlerrm);
3401: return false;
3402: End;
3402: End;
3403:
3404: function get_table_cols(
3405: p_table_name varchar2,
3406: p_col_table in out nocopy BSC_IM_UTILS.varchar_tabletype,
3407: p_cols in out nocopy BSC_IM_UTILS.varchar_tabletype,
3408: p_col_type in out nocopy BSC_IM_UTILS.varchar_tabletype,
3409: p_source_column in out nocopy BSC_IM_UTILS.varchar_tabletype,
3410: p_source_formula in out nocopy BSC_IM_UTILS.varchar_tabletype,
3403:
3404: function get_table_cols(
3405: p_table_name varchar2,
3406: p_col_table in out nocopy BSC_IM_UTILS.varchar_tabletype,
3407: p_cols in out nocopy BSC_IM_UTILS.varchar_tabletype,
3408: p_col_type in out nocopy BSC_IM_UTILS.varchar_tabletype,
3409: p_source_column in out nocopy BSC_IM_UTILS.varchar_tabletype,
3410: p_source_formula in out nocopy BSC_IM_UTILS.varchar_tabletype,
3411: p_number_cols in out nocopy number
3404: function get_table_cols(
3405: p_table_name varchar2,
3406: p_col_table in out nocopy BSC_IM_UTILS.varchar_tabletype,
3407: p_cols in out nocopy BSC_IM_UTILS.varchar_tabletype,
3408: p_col_type in out nocopy BSC_IM_UTILS.varchar_tabletype,
3409: p_source_column in out nocopy BSC_IM_UTILS.varchar_tabletype,
3410: p_source_formula in out nocopy BSC_IM_UTILS.varchar_tabletype,
3411: p_number_cols in out nocopy number
3412: ) return boolean is
3405: p_table_name varchar2,
3406: p_col_table in out nocopy BSC_IM_UTILS.varchar_tabletype,
3407: p_cols in out nocopy BSC_IM_UTILS.varchar_tabletype,
3408: p_col_type in out nocopy BSC_IM_UTILS.varchar_tabletype,
3409: p_source_column in out nocopy BSC_IM_UTILS.varchar_tabletype,
3410: p_source_formula in out nocopy BSC_IM_UTILS.varchar_tabletype,
3411: p_number_cols in out nocopy number
3412: ) return boolean is
3413: l_stmt varchar2(5000);
3406: p_col_table in out nocopy BSC_IM_UTILS.varchar_tabletype,
3407: p_cols in out nocopy BSC_IM_UTILS.varchar_tabletype,
3408: p_col_type in out nocopy BSC_IM_UTILS.varchar_tabletype,
3409: p_source_column in out nocopy BSC_IM_UTILS.varchar_tabletype,
3410: p_source_formula in out nocopy BSC_IM_UTILS.varchar_tabletype,
3411: p_number_cols in out nocopy number
3412: ) return boolean is
3413: l_stmt varchar2(5000);
3414: ---------------------------------------------
3451: end loop;
3452: end if;
3453: return true;
3454: Exception when others then
3455: BSC_IM_UTILS.g_status_message:=sqlerrm;
3456: g_status_message:=sqlerrm;
3457: write_to_log_file_n('Exception in get_table_cols '||sqlerrm);
3458: return false;
3459: End;
3483: write_to_log_file('Result: '||l_id);
3484: end if;
3485: return l_id;
3486: Exception when others then
3487: BSC_IM_UTILS.g_status_message:=sqlerrm;
3488: g_status_message:=sqlerrm;
3489: write_to_log_file_n('Exception in get_table_periodicity '||sqlerrm);
3490: return -1;
3491: End;
3492:
3493: function init_all return boolean is
3494: Begin
3495: g_status:=true;
3496: if BSC_IM_UTILS.get_db_user('APPS',g_apps_owner)=false then
3497: return null;
3498: end if;
3499: if g_apps_owner is null then
3500: return null;
3499: if g_apps_owner is null then
3500: return null;
3501: end if;
3502: g_prod_owner:='BSC';
3503: if BSC_IM_UTILS.get_db_user(g_prod_owner,g_bsc_owner)=false then
3504: return null;
3505: end if;
3506: if g_debug then
3507: write_to_log_file_n('Apps DB User is '||g_apps_owner||', '||g_prod_owner||' user is '||g_bsc_owner||' '||get_time);
3507: write_to_log_file_n('Apps DB User is '||g_apps_owner||', '||g_prod_owner||' user is '||g_bsc_owner||' '||get_time);
3508: end if;
3509: return true;
3510: Exception when others then
3511: BSC_IM_UTILS.g_status_message:=sqlerrm;
3512: g_status_message:=sqlerrm;
3513: write_to_log_file_n('Exception in init_all '||sqlerrm);
3514: return false;
3515: End;
3515: End;
3516:
3517: procedure write_to_log_file(p_message varchar2) is
3518: Begin
3519: BSC_IM_UTILS.write_to_log_file(p_message);
3520: Exception when others then
3521: BSC_IM_UTILS.g_status_message:=sqlerrm;
3522: null;
3523: End;
3517: procedure write_to_log_file(p_message varchar2) is
3518: Begin
3519: BSC_IM_UTILS.write_to_log_file(p_message);
3520: Exception when others then
3521: BSC_IM_UTILS.g_status_message:=sqlerrm;
3522: null;
3523: End;
3524:
3525: procedure write_to_log_file_n(p_message varchar2) is
3526: begin
3527: write_to_log_file(' ');
3528: write_to_log_file(p_message);
3529: Exception when others then
3530: BSC_IM_UTILS.g_status_message:=sqlerrm;
3531: null;
3532: end;
3533:
3534: procedure write_to_debug_n(p_message varchar2) is
3536: if g_debug then
3537: write_to_log_file_n(p_message);
3538: end if;
3539: Exception when others then
3540: BSC_IM_UTILS.g_status_message:=sqlerrm;
3541: null;
3542: end;
3543:
3544: procedure write_to_debug(p_message varchar2) is
3546: if g_debug then
3547: write_to_log_file(p_message);
3548: end if;
3549: Exception when others then
3550: BSC_IM_UTILS.g_status_message:=sqlerrm;
3551: null;
3552: end;
3553:
3554: function get_time return varchar2 is
3552: end;
3553:
3554: function get_time return varchar2 is
3555: begin
3556: return BSC_IM_UTILS.get_time;
3557: Exception when others then
3558: BSC_IM_UTILS.g_status_message:=sqlerrm;
3559: null;
3560: End;
3554: function get_time return varchar2 is
3555: begin
3556: return BSC_IM_UTILS.get_time;
3557: Exception when others then
3558: BSC_IM_UTILS.g_status_message:=sqlerrm;
3559: null;
3560: End;
3561:
3562: procedure set_globals(p_debug boolean) is
3561:
3562: procedure set_globals(p_debug boolean) is
3563: Begin
3564: g_debug:=p_debug;
3565: BSC_IM_UTILS.set_globals(g_debug);
3566: BSC_IM_INT_MD.set_globals(g_debug);
3567: Exception when others then
3568: BSC_IM_UTILS.g_status_message:=sqlerrm;
3569: null;
3564: g_debug:=p_debug;
3565: BSC_IM_UTILS.set_globals(g_debug);
3566: BSC_IM_INT_MD.set_globals(g_debug);
3567: Exception when others then
3568: BSC_IM_UTILS.g_status_message:=sqlerrm;
3569: null;
3570: End;
3571:
3572: function get_table_fks(
3569: null;
3570: End;
3571:
3572: function get_table_fks(
3573: p_s_tables BSC_IM_UTILS.varchar_tabletype,
3574: p_number_s_tables number,
3575: p_fk out nocopy BSC_IM_UTILS.varchar_tabletype,
3576: p_number_fk out nocopy number
3577: ) return boolean is
3571:
3572: function get_table_fks(
3573: p_s_tables BSC_IM_UTILS.varchar_tabletype,
3574: p_number_s_tables number,
3575: p_fk out nocopy BSC_IM_UTILS.varchar_tabletype,
3576: p_number_fk out nocopy number
3577: ) return boolean is
3578: l_fk BSC_IM_UTILS.varchar_tabletype;
3579: l_number_fk number;
3574: p_number_s_tables number,
3575: p_fk out nocopy BSC_IM_UTILS.varchar_tabletype,
3576: p_number_fk out nocopy number
3577: ) return boolean is
3578: l_fk BSC_IM_UTILS.varchar_tabletype;
3579: l_number_fk number;
3580: Begin
3581: p_number_fk:=0;
3582: for i in 1..p_number_s_tables loop
3583: if get_table_fks(p_s_tables(i),l_fk,l_number_fk)=false then
3584: return false;
3585: end if;
3586: for j in 1..l_number_fk loop
3587: if BSC_IM_UTILS.in_array(p_fk,p_number_fk,l_fk(j))=false then
3588: p_number_fk:=p_number_fk+1;
3589: p_fk(p_number_fk):=l_fk(j);
3590: end if;
3591: end loop;
3597: end loop;
3598: end if;
3599: return true;
3600: Exception when others then
3601: BSC_IM_UTILS.g_status_message:=sqlerrm;
3602: g_status_message:=sqlerrm;
3603: write_to_log_file_n('Exception in get_table_fks '||sqlerrm);
3604: return false;
3605: End;
3605: End;
3606:
3607: function get_table_fks(
3608: p_table varchar2,
3609: p_fk out nocopy BSC_IM_UTILS.varchar_tabletype,
3610: p_number_fk out nocopy number
3611: ) return boolean is
3612: l_stmt varchar2(5000);
3613: --------------------------------------------------
3639: and all_tab_columns.owner(+)=p_owner
3640: order by all_tab_columns.column_id;
3641: ----
3642: l_owner varchar2(200);
3643: l_order BSC_IM_UTILS.number_tabletype;
3644: --------------------------------------------------
3645: l_table_name varchar2(100);
3646: Begin
3647: p_number_fk:=1;
3667: --either there are no fk or this could be a table without entries in bsc_kpi_data_tables...like B tables
3668: if g_debug then
3669: write_to_log_file_n('is select distinct bsc_db_tables_cols.column_name,all_tab_columns.column_id...');
3670: end if;
3671: l_owner:=bsc_im_utils.get_table_owner(l_table_name);
3672: p_number_fk:=p_number_fk+1;
3673: open c1(l_table_name,l_owner);
3674: loop
3675: fetch c1 into p_fk(p_number_fk),l_order(p_number_fk);
3678: end loop;
3679: close c1;
3680: p_number_fk:=p_number_fk-1;
3681: end if;
3682: for i in 1..BSC_IM_UTILS.g_number_global_dimension loop
3683: if BSC_IM_UTILS.g_global_dimension(i)<>'PERIOD' and BSC_IM_UTILS.g_global_dimension(i)<>'TYPE' then
3684: p_number_fk:=p_number_fk+1;
3685: p_fk(p_number_fk):=BSC_IM_UTILS.g_global_dimension(i);
3686: end if;
3679: close c1;
3680: p_number_fk:=p_number_fk-1;
3681: end if;
3682: for i in 1..BSC_IM_UTILS.g_number_global_dimension loop
3683: if BSC_IM_UTILS.g_global_dimension(i)<>'PERIOD' and BSC_IM_UTILS.g_global_dimension(i)<>'TYPE' then
3684: p_number_fk:=p_number_fk+1;
3685: p_fk(p_number_fk):=BSC_IM_UTILS.g_global_dimension(i);
3686: end if;
3687: end loop;
3681: end if;
3682: for i in 1..BSC_IM_UTILS.g_number_global_dimension loop
3683: if BSC_IM_UTILS.g_global_dimension(i)<>'PERIOD' and BSC_IM_UTILS.g_global_dimension(i)<>'TYPE' then
3684: p_number_fk:=p_number_fk+1;
3685: p_fk(p_number_fk):=BSC_IM_UTILS.g_global_dimension(i);
3686: end if;
3687: end loop;
3688: for i in 1..BSC_IM_UTILS.g_number_global_dimension loop
3689: if BSC_IM_UTILS.g_global_dimension(i)='PERIOD' or BSC_IM_UTILS.g_global_dimension(i)='TYPE' then
3684: p_number_fk:=p_number_fk+1;
3685: p_fk(p_number_fk):=BSC_IM_UTILS.g_global_dimension(i);
3686: end if;
3687: end loop;
3688: for i in 1..BSC_IM_UTILS.g_number_global_dimension loop
3689: if BSC_IM_UTILS.g_global_dimension(i)='PERIOD' or BSC_IM_UTILS.g_global_dimension(i)='TYPE' then
3690: p_number_fk:=p_number_fk+1;
3691: p_fk(p_number_fk):=BSC_IM_UTILS.g_global_dimension(i);
3692: end if;
3685: p_fk(p_number_fk):=BSC_IM_UTILS.g_global_dimension(i);
3686: end if;
3687: end loop;
3688: for i in 1..BSC_IM_UTILS.g_number_global_dimension loop
3689: if BSC_IM_UTILS.g_global_dimension(i)='PERIOD' or BSC_IM_UTILS.g_global_dimension(i)='TYPE' then
3690: p_number_fk:=p_number_fk+1;
3691: p_fk(p_number_fk):=BSC_IM_UTILS.g_global_dimension(i);
3692: end if;
3693: end loop;
3687: end loop;
3688: for i in 1..BSC_IM_UTILS.g_number_global_dimension loop
3689: if BSC_IM_UTILS.g_global_dimension(i)='PERIOD' or BSC_IM_UTILS.g_global_dimension(i)='TYPE' then
3690: p_number_fk:=p_number_fk+1;
3691: p_fk(p_number_fk):=BSC_IM_UTILS.g_global_dimension(i);
3692: end if;
3693: end loop;
3694: if g_debug then
3695: write_to_log_file_n('Results');
3698: end loop;
3699: end if;
3700: return true;
3701: Exception when others then
3702: BSC_IM_UTILS.g_status_message:=sqlerrm;
3703: g_status_message:=sqlerrm;
3704: write_to_log_file_n('Exception in get_table_fks '||sqlerrm);
3705: return false;
3706: End;
3705: return false;
3706: End;
3707:
3708: function get_table_measures(
3709: p_s_tables BSC_IM_UTILS.varchar_tabletype,
3710: p_number_s_tables number,
3711: p_measures out nocopy BSC_IM_UTILS.varchar_tabletype,
3712: p_number_measures out nocopy number
3713: ) return boolean is
3707:
3708: function get_table_measures(
3709: p_s_tables BSC_IM_UTILS.varchar_tabletype,
3710: p_number_s_tables number,
3711: p_measures out nocopy BSC_IM_UTILS.varchar_tabletype,
3712: p_number_measures out nocopy number
3713: ) return boolean is
3714: l_measures BSC_IM_UTILS.varchar_tabletype;
3715: l_number_measures number;
3710: p_number_s_tables number,
3711: p_measures out nocopy BSC_IM_UTILS.varchar_tabletype,
3712: p_number_measures out nocopy number
3713: ) return boolean is
3714: l_measures BSC_IM_UTILS.varchar_tabletype;
3715: l_number_measures number;
3716: Begin
3717: p_number_measures:=0;
3718: for i in 1..p_number_s_tables loop
3720: if get_table_measures(p_s_tables(i),l_measures,l_number_measures)=false then
3721: return false;
3722: end if;
3723: for j in 1..l_number_measures loop
3724: if BSC_IM_UTILS.in_array(p_measures,p_number_measures,l_measures(j))=false then
3725: p_number_measures:=p_number_measures+1;
3726: p_measures(p_number_measures):=l_measures(j);
3727: end if;
3728: end loop;
3734: end loop;
3735: end if;
3736: return true;
3737: Exception when others then
3738: BSC_IM_UTILS.g_status_message:=sqlerrm;
3739: g_status_message:=sqlerrm;
3740: write_to_log_file_n('Exception in get_s_table_measures '||sqlerrm);
3741: return false;
3742: End;
3742: End;
3743:
3744: function get_table_measures(
3745: p_table varchar2,
3746: p_measures out nocopy BSC_IM_UTILS.varchar_tabletype,
3747: p_number_measures out nocopy number
3748: ) return boolean is
3749: l_stmt varchar2(5000);
3750: -----------------------------------------
3780: end loop;
3781: end if;
3782: return true;
3783: Exception when others then
3784: BSC_IM_UTILS.g_status_message:=sqlerrm;
3785: g_status_message:=sqlerrm;
3786: write_to_log_file_n('Exception in get_s_table_measures '||sqlerrm);
3787: return false;
3788: End;
3788: End;
3789:
3790: function get_kpi_periodicity(
3791: p_indicator_id number,
3792: p_periodicity out nocopy BSC_IM_UTILS.number_tabletype,
3793: p_number_periodicity out nocopy number
3794: )return boolean is
3795: l_stmt varchar2(5000);
3796: -------------------------------------------
3818: end loop;
3819: end if;
3820: return true;
3821: Exception when others then
3822: BSC_IM_UTILS.g_status_message:=sqlerrm;
3823: g_status_message:=sqlerrm;
3824: write_to_log_file_n('Exception in get_kpi_periodicity '||sqlerrm);
3825: return false;
3826: End;
3826: End;
3827:
3828: function get_s_sb_tables(
3829: p_indicator_id number,
3830: p_s_tables out nocopy BSC_IM_UTILS.varchar_tabletype,
3831: p_s_periodicity out nocopy BSC_IM_UTILS.number_tabletype,
3832: p_number_s_tables out nocopy number
3833: )return boolean is
3834: l_stmt varchar2(5000);
3827:
3828: function get_s_sb_tables(
3829: p_indicator_id number,
3830: p_s_tables out nocopy BSC_IM_UTILS.varchar_tabletype,
3831: p_s_periodicity out nocopy BSC_IM_UTILS.number_tabletype,
3832: p_number_s_tables out nocopy number
3833: )return boolean is
3834: l_stmt varchar2(5000);
3835: l_mv_object_string varchar2(5000);
3832: p_number_s_tables out nocopy number
3833: )return boolean is
3834: l_stmt varchar2(5000);
3835: l_mv_object_string varchar2(5000);
3836: l_mv_object BSC_IM_UTILS.varchar_tabletype;
3837: l_number_mv_object number;
3838: ---------------------------------------------------
3839:
3840: -- overcome issue with large data in bsc_kpi_data_tables, use db_tables_rels instead
3900: end loop;
3901: end if;
3902: return true;
3903: Exception when others then
3904: BSC_IM_UTILS.g_status_message:=sqlerrm;
3905: g_status_message:=sqlerrm;
3906: write_to_log_file_n('Exception in get_s_sb_tables '||sqlerrm);
3907: return false;
3908: End;
3910: function get_db_calculation(
3911: p_indicator number,
3912: p_s_table varchar2,
3913: p_type number,
3914: p_calculation_table in out nocopy BSC_IM_UTILS.varchar_tabletype,
3915: p_calculation_type in out nocopy BSC_IM_UTILS.varchar_tabletype,
3916: p_parameter1 in out nocopy BSC_IM_UTILS.varchar_tabletype,
3917: p_parameter2 in out nocopy BSC_IM_UTILS.varchar_tabletype,
3918: p_parameter3 in out nocopy BSC_IM_UTILS.varchar_tabletype,
3911: p_indicator number,
3912: p_s_table varchar2,
3913: p_type number,
3914: p_calculation_table in out nocopy BSC_IM_UTILS.varchar_tabletype,
3915: p_calculation_type in out nocopy BSC_IM_UTILS.varchar_tabletype,
3916: p_parameter1 in out nocopy BSC_IM_UTILS.varchar_tabletype,
3917: p_parameter2 in out nocopy BSC_IM_UTILS.varchar_tabletype,
3918: p_parameter3 in out nocopy BSC_IM_UTILS.varchar_tabletype,
3919: p_parameter4 in out nocopy BSC_IM_UTILS.varchar_tabletype,
3912: p_s_table varchar2,
3913: p_type number,
3914: p_calculation_table in out nocopy BSC_IM_UTILS.varchar_tabletype,
3915: p_calculation_type in out nocopy BSC_IM_UTILS.varchar_tabletype,
3916: p_parameter1 in out nocopy BSC_IM_UTILS.varchar_tabletype,
3917: p_parameter2 in out nocopy BSC_IM_UTILS.varchar_tabletype,
3918: p_parameter3 in out nocopy BSC_IM_UTILS.varchar_tabletype,
3919: p_parameter4 in out nocopy BSC_IM_UTILS.varchar_tabletype,
3920: p_parameter5 in out nocopy BSC_IM_UTILS.varchar_tabletype,
3913: p_type number,
3914: p_calculation_table in out nocopy BSC_IM_UTILS.varchar_tabletype,
3915: p_calculation_type in out nocopy BSC_IM_UTILS.varchar_tabletype,
3916: p_parameter1 in out nocopy BSC_IM_UTILS.varchar_tabletype,
3917: p_parameter2 in out nocopy BSC_IM_UTILS.varchar_tabletype,
3918: p_parameter3 in out nocopy BSC_IM_UTILS.varchar_tabletype,
3919: p_parameter4 in out nocopy BSC_IM_UTILS.varchar_tabletype,
3920: p_parameter5 in out nocopy BSC_IM_UTILS.varchar_tabletype,
3921: p_number_parameters in out nocopy number
3914: p_calculation_table in out nocopy BSC_IM_UTILS.varchar_tabletype,
3915: p_calculation_type in out nocopy BSC_IM_UTILS.varchar_tabletype,
3916: p_parameter1 in out nocopy BSC_IM_UTILS.varchar_tabletype,
3917: p_parameter2 in out nocopy BSC_IM_UTILS.varchar_tabletype,
3918: p_parameter3 in out nocopy BSC_IM_UTILS.varchar_tabletype,
3919: p_parameter4 in out nocopy BSC_IM_UTILS.varchar_tabletype,
3920: p_parameter5 in out nocopy BSC_IM_UTILS.varchar_tabletype,
3921: p_number_parameters in out nocopy number
3922: ) return boolean is
3915: p_calculation_type in out nocopy BSC_IM_UTILS.varchar_tabletype,
3916: p_parameter1 in out nocopy BSC_IM_UTILS.varchar_tabletype,
3917: p_parameter2 in out nocopy BSC_IM_UTILS.varchar_tabletype,
3918: p_parameter3 in out nocopy BSC_IM_UTILS.varchar_tabletype,
3919: p_parameter4 in out nocopy BSC_IM_UTILS.varchar_tabletype,
3920: p_parameter5 in out nocopy BSC_IM_UTILS.varchar_tabletype,
3921: p_number_parameters in out nocopy number
3922: ) return boolean is
3923: l_stmt varchar2(4000);
3916: p_parameter1 in out nocopy BSC_IM_UTILS.varchar_tabletype,
3917: p_parameter2 in out nocopy BSC_IM_UTILS.varchar_tabletype,
3918: p_parameter3 in out nocopy BSC_IM_UTILS.varchar_tabletype,
3919: p_parameter4 in out nocopy BSC_IM_UTILS.varchar_tabletype,
3920: p_parameter5 in out nocopy BSC_IM_UTILS.varchar_tabletype,
3921: p_number_parameters in out nocopy number
3922: ) return boolean is
3923: l_stmt varchar2(4000);
3924: l_agg_func BSC_IM_UTILS.varchar_tabletype;
3920: p_parameter5 in out nocopy BSC_IM_UTILS.varchar_tabletype,
3921: p_number_parameters in out nocopy number
3922: ) return boolean is
3923: l_stmt varchar2(4000);
3924: l_agg_func BSC_IM_UTILS.varchar_tabletype;
3925: ----------------------------------------------------------
3926: cursor c1 (p_table varchar2)
3927: is select table_name,calculation_type,parameter1,'''0''',upper(parameter3),parameter4,parameter5
3928: from bsc_db_calculations where table_name=p_table
3942: where cols.table_name = dim.level_table_name
3943: and cols.column_name = 'CODE'
3944: and dim.indicator = p_indicator
3945: and dim.level_pk_col = p_column
3946: and cols.owner = bsc_im_utils.get_table_owner(dim.level_table_name) ;
3947:
3948: ----------------------------------------------------------
3949: l_calculation_table BSC_IM_UTILS.varchar_tabletype;
3950: l_calculation_type BSC_IM_UTILS.varchar_tabletype;
3945: and dim.level_pk_col = p_column
3946: and cols.owner = bsc_im_utils.get_table_owner(dim.level_table_name) ;
3947:
3948: ----------------------------------------------------------
3949: l_calculation_table BSC_IM_UTILS.varchar_tabletype;
3950: l_calculation_type BSC_IM_UTILS.varchar_tabletype;
3951: l_parameter1 BSC_IM_UTILS.varchar_tabletype;
3952: l_parameter2 BSC_IM_UTILS.varchar_tabletype;
3953: l_parameter3 BSC_IM_UTILS.varchar_tabletype;
3946: and cols.owner = bsc_im_utils.get_table_owner(dim.level_table_name) ;
3947:
3948: ----------------------------------------------------------
3949: l_calculation_table BSC_IM_UTILS.varchar_tabletype;
3950: l_calculation_type BSC_IM_UTILS.varchar_tabletype;
3951: l_parameter1 BSC_IM_UTILS.varchar_tabletype;
3952: l_parameter2 BSC_IM_UTILS.varchar_tabletype;
3953: l_parameter3 BSC_IM_UTILS.varchar_tabletype;
3954: l_parameter4 BSC_IM_UTILS.varchar_tabletype;
3947:
3948: ----------------------------------------------------------
3949: l_calculation_table BSC_IM_UTILS.varchar_tabletype;
3950: l_calculation_type BSC_IM_UTILS.varchar_tabletype;
3951: l_parameter1 BSC_IM_UTILS.varchar_tabletype;
3952: l_parameter2 BSC_IM_UTILS.varchar_tabletype;
3953: l_parameter3 BSC_IM_UTILS.varchar_tabletype;
3954: l_parameter4 BSC_IM_UTILS.varchar_tabletype;
3955: l_parameter5 BSC_IM_UTILS.varchar_tabletype;
3948: ----------------------------------------------------------
3949: l_calculation_table BSC_IM_UTILS.varchar_tabletype;
3950: l_calculation_type BSC_IM_UTILS.varchar_tabletype;
3951: l_parameter1 BSC_IM_UTILS.varchar_tabletype;
3952: l_parameter2 BSC_IM_UTILS.varchar_tabletype;
3953: l_parameter3 BSC_IM_UTILS.varchar_tabletype;
3954: l_parameter4 BSC_IM_UTILS.varchar_tabletype;
3955: l_parameter5 BSC_IM_UTILS.varchar_tabletype;
3956: l_number_parameters number;
3949: l_calculation_table BSC_IM_UTILS.varchar_tabletype;
3950: l_calculation_type BSC_IM_UTILS.varchar_tabletype;
3951: l_parameter1 BSC_IM_UTILS.varchar_tabletype;
3952: l_parameter2 BSC_IM_UTILS.varchar_tabletype;
3953: l_parameter3 BSC_IM_UTILS.varchar_tabletype;
3954: l_parameter4 BSC_IM_UTILS.varchar_tabletype;
3955: l_parameter5 BSC_IM_UTILS.varchar_tabletype;
3956: l_number_parameters number;
3957: l_fk_datatype varchar2(100);
3950: l_calculation_type BSC_IM_UTILS.varchar_tabletype;
3951: l_parameter1 BSC_IM_UTILS.varchar_tabletype;
3952: l_parameter2 BSC_IM_UTILS.varchar_tabletype;
3953: l_parameter3 BSC_IM_UTILS.varchar_tabletype;
3954: l_parameter4 BSC_IM_UTILS.varchar_tabletype;
3955: l_parameter5 BSC_IM_UTILS.varchar_tabletype;
3956: l_number_parameters number;
3957: l_fk_datatype varchar2(100);
3958: TYPE CurTyp IS REF CURSOR;
3951: l_parameter1 BSC_IM_UTILS.varchar_tabletype;
3952: l_parameter2 BSC_IM_UTILS.varchar_tabletype;
3953: l_parameter3 BSC_IM_UTILS.varchar_tabletype;
3954: l_parameter4 BSC_IM_UTILS.varchar_tabletype;
3955: l_parameter5 BSC_IM_UTILS.varchar_tabletype;
3956: l_number_parameters number;
3957: l_fk_datatype varchar2(100);
3958: TYPE CurTyp IS REF CURSOR;
3959: cv CurTyp;
4081: end if;
4082: -----------------------
4083: return true;
4084: Exception when others then
4085: BSC_IM_UTILS.g_status_message:=sqlerrm;
4086: g_status_message:=sqlerrm;
4087: write_to_log_file_n('Exception in get_db_calculation '||sqlerrm);
4088: return false;
4089: End;
4089: End;
4090:
4091: function get_table_relations(
4092: p_table varchar2,
4093: p_tables in out nocopy BSC_IM_UTILS.varchar_tabletype,
4094: p_source_tables in out nocopy BSC_IM_UTILS.varchar_tabletype,
4095: p_relation_type in out nocopy BSC_IM_UTILS.varchar_tabletype,
4096: p_number_tables in out nocopy number
4097: ) return boolean is
4090:
4091: function get_table_relations(
4092: p_table varchar2,
4093: p_tables in out nocopy BSC_IM_UTILS.varchar_tabletype,
4094: p_source_tables in out nocopy BSC_IM_UTILS.varchar_tabletype,
4095: p_relation_type in out nocopy BSC_IM_UTILS.varchar_tabletype,
4096: p_number_tables in out nocopy number
4097: ) return boolean is
4098: l_stmt varchar2(32000);
4091: function get_table_relations(
4092: p_table varchar2,
4093: p_tables in out nocopy BSC_IM_UTILS.varchar_tabletype,
4094: p_source_tables in out nocopy BSC_IM_UTILS.varchar_tabletype,
4095: p_relation_type in out nocopy BSC_IM_UTILS.varchar_tabletype,
4096: p_number_tables in out nocopy number
4097: ) return boolean is
4098: l_stmt varchar2(32000);
4099: ------------------------------------------------
4137: end loop;
4138: end if;
4139: return true;
4140: Exception when others then
4141: BSC_IM_UTILS.g_status_message:=sqlerrm;
4142: g_status_message:=sqlerrm;
4143: write_to_log_file_n('Exception in get_table_relations '||sqlerrm);
4144: return false;
4145: End;
4160: fetch c1 into l_cal;
4161: close c1;
4162: return l_cal;
4163: Exception when others then
4164: BSC_IM_UTILS.g_status_message:=sqlerrm;
4165: g_status_message:=sqlerrm;
4166: write_to_log_file_n('Exception in get_calendar_for_periodicity '||sqlerrm);
4167: return null;
4168: End;
4175: p_calendar_join_1 out nocopy varchar2,
4176: p_calendar_join_2 out nocopy varchar2
4177: )return boolean is
4178: -------------------------------------------------------
4179: l_periodicity_id BSC_IM_UTILS.number_tabletype;
4180: l_source BSC_IM_UTILS.varchar_tabletype;
4181: l_db_column_name BSC_IM_UTILS.varchar_tabletype;
4182: l_number_periodicity number;
4183: -------------------------------------------------------
4176: p_calendar_join_2 out nocopy varchar2
4177: )return boolean is
4178: -------------------------------------------------------
4179: l_periodicity_id BSC_IM_UTILS.number_tabletype;
4180: l_source BSC_IM_UTILS.varchar_tabletype;
4181: l_db_column_name BSC_IM_UTILS.varchar_tabletype;
4182: l_number_periodicity number;
4183: -------------------------------------------------------
4184: l_values BSC_IM_UTILS.varchar_tabletype;
4177: )return boolean is
4178: -------------------------------------------------------
4179: l_periodicity_id BSC_IM_UTILS.number_tabletype;
4180: l_source BSC_IM_UTILS.varchar_tabletype;
4181: l_db_column_name BSC_IM_UTILS.varchar_tabletype;
4182: l_number_periodicity number;
4183: -------------------------------------------------------
4184: l_values BSC_IM_UTILS.varchar_tabletype;
4185: l_number_values number;
4180: l_source BSC_IM_UTILS.varchar_tabletype;
4181: l_db_column_name BSC_IM_UTILS.varchar_tabletype;
4182: l_number_periodicity number;
4183: -------------------------------------------------------
4184: l_values BSC_IM_UTILS.varchar_tabletype;
4185: l_number_values number;
4186: l_index number;
4187: -------------------------------------------------------
4188: l_cal_column BSC_IM_UTILS.varchar_tabletype;
4184: l_values BSC_IM_UTILS.varchar_tabletype;
4185: l_number_values number;
4186: l_index number;
4187: -------------------------------------------------------
4188: l_cal_column BSC_IM_UTILS.varchar_tabletype;
4189: l_cal_periodicity BSC_IM_UTILS.number_tabletype;
4190: l_number_cal_column number;
4191: l_calendar_id number;
4192: -------------------------------------------------------
4185: l_number_values number;
4186: l_index number;
4187: -------------------------------------------------------
4188: l_cal_column BSC_IM_UTILS.varchar_tabletype;
4189: l_cal_periodicity BSC_IM_UTILS.number_tabletype;
4190: l_number_cal_column number;
4191: l_calendar_id number;
4192: -------------------------------------------------------
4193: cursor c1 is select periodicity_id,source,db_column_name from bsc_sys_periodicities;
4228: end if;
4229: end loop;
4230: for i in 1..l_number_periodicity loop
4231: l_number_values:=0;
4232: if BSC_IM_UTILS.parse_values(l_source(i),',',l_values,l_number_values)=false then
4233: return false;
4234: end if;
4235: for j in 1..l_number_values loop
4236: if l_values(j)=p_periodicity then
4233: return false;
4234: end if;
4235: for j in 1..l_number_values loop
4236: if l_values(j)=p_periodicity then
4237: if BSC_IM_UTILS.in_array(l_cal_column,l_number_cal_column,l_db_column_name(i))=false then
4238: l_number_cal_column:=l_number_cal_column+1;
4239: l_cal_periodicity(l_number_cal_column):=l_periodicity_id(i);
4240: l_cal_column(l_number_cal_column):=l_db_column_name(i);
4241: end if;
4265: p_calendar_join_1:=l_cal_column(1);
4266: p_calendar_join_2:='YEAR';
4267: return true;
4268: Exception when others then
4269: BSC_IM_UTILS.g_status_message:=sqlerrm;
4270: g_status_message:=sqlerrm;
4271: write_to_log_file_n('Exception in get_summarize_calendar '||sqlerrm);
4272: return false;
4273: End;
4273: End;
4274:
4275: function get_columns_in_formula(
4276: p_expression varchar2,
4277: p_measure BSC_IM_UTILS.varchar_tabletype,
4278: p_number_measure number,
4279: p_table out nocopy BSC_IM_UTILS.varchar_tabletype,
4280: p_number_table out nocopy number
4281: )return boolean is
4275: function get_columns_in_formula(
4276: p_expression varchar2,
4277: p_measure BSC_IM_UTILS.varchar_tabletype,
4278: p_number_measure number,
4279: p_table out nocopy BSC_IM_UTILS.varchar_tabletype,
4280: p_number_table out nocopy number
4281: )return boolean is
4282: Begin
4283: p_number_table:=0;
4282: Begin
4283: p_number_table:=0;
4284: for i in 1..p_number_measure loop
4285: if instr(upper(p_expression),upper(p_measure(i)))>0 then
4286: if BSC_IM_UTILS.in_array(p_table,p_number_table,p_measure(i))=false then
4287: p_number_table:=p_number_table+1;
4288: p_table(p_number_table):=p_measure(i);
4289: end if;
4290: end if;
4296: end loop;
4297: end if;
4298: return true;
4299: Exception when others then
4300: BSC_IM_UTILS.g_status_message:=sqlerrm;
4301: g_status_message:=sqlerrm;
4302: write_to_log_file_n('Exception in get_columns_in_formula '||sqlerrm);
4303: return false;
4304: End;
4318: l_period_type_id:=128;
4319: end if;
4320: return l_period_type_id;
4321: Exception when others then
4322: BSC_IM_UTILS.g_status_message:=sqlerrm;
4323: g_status_message:=sqlerrm;
4324: write_to_log_file_n('Exception in get_period_type_id '||sqlerrm);
4325: return null;
4326: End;
4326: End;
4327:
4328: function find_xtd_levels(
4329: p_periodicity number,
4330: p_xtd_levels out nocopy BSC_IM_UTILS.varchar_tabletype,
4331: p_number_xtd_levels out nocopy number
4332: )return boolean is
4333: l_stmt varchar2(2000);
4334: l_period_col_name varchar2(200);
4369: null;
4370: end if;
4371: return true;
4372: Exception when others then
4373: BSC_IM_UTILS.g_status_message:=sqlerrm;
4374: g_status_message:=sqlerrm;
4375: write_to_log_file_n('Exception in find_xtd_levels '||sqlerrm);
4376: return false;
4377: End;
4376: return false;
4377: End;
4378:
4379: function load_reporting_calendar(
4380: p_options BSC_IM_UTILS.varchar_tabletype,
4381: p_number_options number
4382: )return boolean is
4383: ----------------
4384: --1001 is DBI ent calendar
4384: --1001 is DBI ent calendar
4385: cursor c1 is select calendar_id,decode(edw_calendar_type_id,null,0,1,decode(edw_calendar_id,1001,2,1))
4386: from bsc_sys_calendars_b;
4387: ---------------
4388: l_calendar_id BSC_IM_UTILS.number_tabletype;
4389: l_calendar_type BSC_IM_UTILS.number_tabletype;
4390: l_number_calendar_id number;
4391: ---------------
4392: l_calendar_data cal_record_table;
4385: cursor c1 is select calendar_id,decode(edw_calendar_type_id,null,0,1,decode(edw_calendar_id,1001,2,1))
4386: from bsc_sys_calendars_b;
4387: ---------------
4388: l_calendar_id BSC_IM_UTILS.number_tabletype;
4389: l_calendar_type BSC_IM_UTILS.number_tabletype;
4390: l_number_calendar_id number;
4391: ---------------
4392: l_calendar_data cal_record_table;
4393: l_number_calendar_data number;
4392: l_calendar_data cal_record_table;
4393: l_number_calendar_data number;
4394: l_periodicity_data cal_periodicity_table;
4395: l_number_periodicity_data number;
4396: l_hier BSC_IM_UTILS.varchar_tabletype;
4397: l_hier_type BSC_IM_UTILS.varchar_tabletype;
4398: l_number_hier number;
4399: ---------------
4400: l_rpt_calendar varchar2(200);
4393: l_number_calendar_data number;
4394: l_periodicity_data cal_periodicity_table;
4395: l_number_periodicity_data number;
4396: l_hier BSC_IM_UTILS.varchar_tabletype;
4397: l_hier_type BSC_IM_UTILS.varchar_tabletype;
4398: l_number_hier number;
4399: ---------------
4400: l_rpt_calendar varchar2(200);
4401: l_rpt_calendar_owner varchar2(200);
4404: if g_debug then
4405: write_to_log_file_n('In load_reporting_calendar '||get_time);
4406: end if;
4407: --Bug#3973335
4408: if BSC_IM_UTILS.get_option_value(g_options,g_number_options,'DEBUG LOG')='Y' then
4409: g_debug:=true;
4410: end if;
4411: l_rpt_calendar:='BSC_REPORTING_CALENDAR';
4412: l_rpt_calendar_owner:=BSC_IM_UTILS.get_table_owner(l_rpt_calendar);
4408: if BSC_IM_UTILS.get_option_value(g_options,g_number_options,'DEBUG LOG')='Y' then
4409: g_debug:=true;
4410: end if;
4411: l_rpt_calendar:='BSC_REPORTING_CALENDAR';
4412: l_rpt_calendar_owner:=BSC_IM_UTILS.get_table_owner(l_rpt_calendar);
4413: /*if BSC_IM_UTILS.truncate_table(l_rpt_calendar,l_rpt_calendar_owner)=false then
4414: null;
4415: end if;*/
4416: --get the list of calendars
4409: g_debug:=true;
4410: end if;
4411: l_rpt_calendar:='BSC_REPORTING_CALENDAR';
4412: l_rpt_calendar_owner:=BSC_IM_UTILS.get_table_owner(l_rpt_calendar);
4413: /*if BSC_IM_UTILS.truncate_table(l_rpt_calendar,l_rpt_calendar_owner)=false then
4414: null;
4415: end if;*/
4416: --get the list of calendars
4417: if g_debug then
4504: end if;
4505: end loop;
4506: -------------------------
4507: end loop;
4508: if BSC_IM_UTILS.get_option_value(p_options,p_number_options,'ANALYZE')='Y' then
4509: BSC_IM_UTILS.analyze_object(l_rpt_calendar,l_rpt_calendar_owner,null,null,null);
4510: end if;
4511:
4512: return true;
4505: end loop;
4506: -------------------------
4507: end loop;
4508: if BSC_IM_UTILS.get_option_value(p_options,p_number_options,'ANALYZE')='Y' then
4509: BSC_IM_UTILS.analyze_object(l_rpt_calendar,l_rpt_calendar_owner,null,null,null);
4510: end if;
4511:
4512: return true;
4513: Exception when others then
4510: end if;
4511:
4512: return true;
4513: Exception when others then
4514: BSC_IM_UTILS.g_status_message:=sqlerrm;
4515: g_status_message:=sqlerrm;
4516: write_to_log_file_n('Exception in load_reporting_calendar '||sqlerrm);
4517: return false;
4518: End;
4520: --Fix bug#4027813 This function created to load reporting calendar only for the specified
4521: --calendar id
4522: function load_reporting_calendar(
4523: p_calendar_id number,
4524: p_options BSC_IM_UTILS.varchar_tabletype,
4525: p_number_options number
4526: )return boolean is
4527: ----------------
4528: --1001 is DBI ent calendar
4535: l_calendar_data cal_record_table;
4536: l_number_calendar_data number;
4537: l_periodicity_data cal_periodicity_table;
4538: l_number_periodicity_data number;
4539: l_hier BSC_IM_UTILS.varchar_tabletype;
4540: l_hier_type BSC_IM_UTILS.varchar_tabletype;
4541: l_number_hier number;
4542: ---------------
4543: l_rpt_calendar varchar2(200);
4536: l_number_calendar_data number;
4537: l_periodicity_data cal_periodicity_table;
4538: l_number_periodicity_data number;
4539: l_hier BSC_IM_UTILS.varchar_tabletype;
4540: l_hier_type BSC_IM_UTILS.varchar_tabletype;
4541: l_number_hier number;
4542: ---------------
4543: l_rpt_calendar varchar2(200);
4544: l_rpt_calendar_owner varchar2(200);
4551: if calendar_already_refreshed(p_calendar_id) then
4552: return true;
4553: end if;
4554: --Bug#3973335
4555: if BSC_IM_UTILS.get_option_value(g_options,g_number_options,'DEBUG LOG')='Y' then
4556: g_debug:=true;
4557: end if;
4558: l_rpt_calendar:='BSC_REPORTING_CALENDAR';
4559: l_rpt_calendar_owner:=BSC_IM_UTILS.get_table_owner(l_rpt_calendar);
4555: if BSC_IM_UTILS.get_option_value(g_options,g_number_options,'DEBUG LOG')='Y' then
4556: g_debug:=true;
4557: end if;
4558: l_rpt_calendar:='BSC_REPORTING_CALENDAR';
4559: l_rpt_calendar_owner:=BSC_IM_UTILS.get_table_owner(l_rpt_calendar);
4560: drop_and_add_partition(p_calendar_id, l_rpt_calendar_owner);
4561: --bug 4636259, performance issue, so partitioning;
4562:
4563: if g_debug then
4641: end if;
4642: end if;
4643: end loop;
4644: -------------------------
4645: if BSC_IM_UTILS.get_option_value(p_options,p_number_options,'ANALYZE')='Y' then
4646: BSC_IM_UTILS.analyze_object(l_rpt_calendar,l_rpt_calendar_owner,null,null,'p_'||p_calendar_id);
4647: end if;
4648: cache_calendar_as_loaded(p_calendar_id);
4649: if g_debug then
4642: end if;
4643: end loop;
4644: -------------------------
4645: if BSC_IM_UTILS.get_option_value(p_options,p_number_options,'ANALYZE')='Y' then
4646: BSC_IM_UTILS.analyze_object(l_rpt_calendar,l_rpt_calendar_owner,null,null,'p_'||p_calendar_id);
4647: end if;
4648: cache_calendar_as_loaded(p_calendar_id);
4649: if g_debug then
4650: write_to_log_file_n('Completed load_reporting_calendar '||get_time);
4651: write_to_log_file_n('--------------------------------------------');
4652: end if;
4653: return true;
4654: Exception when others then
4655: BSC_IM_UTILS.g_status_message:=sqlerrm;
4656: g_status_message:=sqlerrm;
4657: write_to_log_file_n('Exception in load_reporting_calendar '||sqlerrm);
4658: return false;
4659: End;
4720: p_number_calendar_data:=p_number_calendar_data-1;
4721: close c1;
4722: return true;
4723: Exception when others then
4724: BSC_IM_UTILS.g_status_message:=sqlerrm;
4725: g_status_message:=sqlerrm;
4726: write_to_log_file_n('Exception in get_calendar_data '||sqlerrm);
4727: return false;
4728: End;
4825: end loop;
4826: end if;
4827: return true;
4828: Exception when others then
4829: BSC_IM_UTILS.g_status_message:=sqlerrm;
4830: g_status_message:=sqlerrm;
4831: write_to_log_file_n('Exception in get_periodicity_data '||sqlerrm);
4832: return false;
4833: End;
4835: function set_xtd_pattern(
4836: p_calendar_id number,
4837: p_periodicity_data in out nocopy cal_periodicity_table,
4838: p_number_periodicity_data number,
4839: p_hier BSC_IM_UTILS.varchar_tabletype,
4840: p_hier_type BSC_IM_UTILS.varchar_tabletype,
4841: p_number_hier number
4842: )return boolean is
4843: l_pattern BSC_IM_UTILS.varchar_tabletype;
4836: p_calendar_id number,
4837: p_periodicity_data in out nocopy cal_periodicity_table,
4838: p_number_periodicity_data number,
4839: p_hier BSC_IM_UTILS.varchar_tabletype,
4840: p_hier_type BSC_IM_UTILS.varchar_tabletype,
4841: p_number_hier number
4842: )return boolean is
4843: l_pattern BSC_IM_UTILS.varchar_tabletype;
4844: l_dbi_hier varchar2(400);
4839: p_hier BSC_IM_UTILS.varchar_tabletype,
4840: p_hier_type BSC_IM_UTILS.varchar_tabletype,
4841: p_number_hier number
4842: )return boolean is
4843: l_pattern BSC_IM_UTILS.varchar_tabletype;
4844: l_dbi_hier varchar2(400);
4845: l_pattern_sum number;
4846: Begin
4847: if g_debug then
4894: end loop;
4895: commit;
4896: return true;
4897: Exception when others then
4898: BSC_IM_UTILS.g_status_message:=sqlerrm;
4899: g_status_message:=sqlerrm;
4900: write_to_log_file_n('Exception in set_xtd_pattern '||sqlerrm);
4901: return false;
4902: End;
4907: p_periodicity_data cal_periodicity_table,
4908: p_number_periodicity_data number
4909: ) return number is
4910: l_pattern_sum number;
4911: l_periodicity BSC_IM_UTILS.number_tabletype;
4912: l_number_periodicity number;
4913: --
4914: l_start number;
4915: Begin
4915: Begin
4916:
4917: --see if the periodicity is in the hier, then look at the periodicities below and calculate the pattern
4918: l_pattern_sum:=0;
4919: if BSC_IM_UTILS.parse_values(p_hier,',',l_periodicity,l_number_periodicity)=false then
4920: return null;
4921: end if;
4922: for i in 1..l_number_periodicity loop
4923: if l_periodicity(i)=p_periodicity_id then
4938: end if;
4939: end loop;
4940: return l_pattern_sum;
4941: Exception when others then
4942: BSC_IM_UTILS.g_status_message:=sqlerrm;
4943: g_status_message:=sqlerrm;
4944: write_to_log_file_n('In get_xtd_pattern, p_periodicity_id='||p_periodicity_id||',p_hier='||p_hier);
4945: write_to_log_file_n('Exception in get_xtd_pattern '||sqlerrm);
4946: return null;
4950: p_calendar_id number,
4951: p_calendar_type number,
4952: p_periodicity_data cal_periodicity_table,
4953: p_number_periodicity_data number,
4954: p_hier out nocopy BSC_IM_UTILS.varchar_tabletype,
4955: p_hier_type out nocopy BSC_IM_UTILS.varchar_tabletype,
4956: p_number_hier out nocopy number
4957: )return boolean is
4958: --------------------------
4951: p_calendar_type number,
4952: p_periodicity_data cal_periodicity_table,
4953: p_number_periodicity_data number,
4954: p_hier out nocopy BSC_IM_UTILS.varchar_tabletype,
4955: p_hier_type out nocopy BSC_IM_UTILS.varchar_tabletype,
4956: p_number_hier out nocopy number
4957: )return boolean is
4958: --------------------------
4959: l_parent_hier BSC_IM_UTILS.number_tabletype;
4955: p_hier_type out nocopy BSC_IM_UTILS.varchar_tabletype,
4956: p_number_hier out nocopy number
4957: )return boolean is
4958: --------------------------
4959: l_parent_hier BSC_IM_UTILS.number_tabletype;
4960: l_child_hier BSC_IM_UTILS.number_tabletype;
4961: l_parent_type BSC_IM_UTILS.varchar_tabletype;
4962: l_looked_at BSC_IM_UTILS.boolean_tabletype;
4963: l_number_hier number;
4956: p_number_hier out nocopy number
4957: )return boolean is
4958: --------------------------
4959: l_parent_hier BSC_IM_UTILS.number_tabletype;
4960: l_child_hier BSC_IM_UTILS.number_tabletype;
4961: l_parent_type BSC_IM_UTILS.varchar_tabletype;
4962: l_looked_at BSC_IM_UTILS.boolean_tabletype;
4963: l_number_hier number;
4964: --------------------------
4957: )return boolean is
4958: --------------------------
4959: l_parent_hier BSC_IM_UTILS.number_tabletype;
4960: l_child_hier BSC_IM_UTILS.number_tabletype;
4961: l_parent_type BSC_IM_UTILS.varchar_tabletype;
4962: l_looked_at BSC_IM_UTILS.boolean_tabletype;
4963: l_number_hier number;
4964: --------------------------
4965: l_num_table BSC_IM_UTILS.number_tabletype;
4958: --------------------------
4959: l_parent_hier BSC_IM_UTILS.number_tabletype;
4960: l_child_hier BSC_IM_UTILS.number_tabletype;
4961: l_parent_type BSC_IM_UTILS.varchar_tabletype;
4962: l_looked_at BSC_IM_UTILS.boolean_tabletype;
4963: l_number_hier number;
4964: --------------------------
4965: l_num_table BSC_IM_UTILS.number_tabletype;
4966: l_number_num_table number;
4961: l_parent_type BSC_IM_UTILS.varchar_tabletype;
4962: l_looked_at BSC_IM_UTILS.boolean_tabletype;
4963: l_number_hier number;
4964: --------------------------
4965: l_num_table BSC_IM_UTILS.number_tabletype;
4966: l_number_num_table number;
4967: --------------------------
4968: l_custom_periodicity BSC_IM_UTILS.number_tabletype;
4969: l_number_custom_periodicity number;
4964: --------------------------
4965: l_num_table BSC_IM_UTILS.number_tabletype;
4966: l_number_num_table number;
4967: --------------------------
4968: l_custom_periodicity BSC_IM_UTILS.number_tabletype;
4969: l_number_custom_periodicity number;
4970: l_custom_parent BSC_IM_UTILS.number_tabletype;
4971: l_custom_child BSC_IM_UTILS.number_tabletype;
4972: l_custom_flag BSC_IM_UTILS.boolean_tabletype;
4966: l_number_num_table number;
4967: --------------------------
4968: l_custom_periodicity BSC_IM_UTILS.number_tabletype;
4969: l_number_custom_periodicity number;
4970: l_custom_parent BSC_IM_UTILS.number_tabletype;
4971: l_custom_child BSC_IM_UTILS.number_tabletype;
4972: l_custom_flag BSC_IM_UTILS.boolean_tabletype;
4973: l_number_custom_hier number;
4974: --------------------------
4967: --------------------------
4968: l_custom_periodicity BSC_IM_UTILS.number_tabletype;
4969: l_number_custom_periodicity number;
4970: l_custom_parent BSC_IM_UTILS.number_tabletype;
4971: l_custom_child BSC_IM_UTILS.number_tabletype;
4972: l_custom_flag BSC_IM_UTILS.boolean_tabletype;
4973: l_number_custom_hier number;
4974: --------------------------
4975: l_seed number;
4968: l_custom_periodicity BSC_IM_UTILS.number_tabletype;
4969: l_number_custom_periodicity number;
4970: l_custom_parent BSC_IM_UTILS.number_tabletype;
4971: l_custom_child BSC_IM_UTILS.number_tabletype;
4972: l_custom_flag BSC_IM_UTILS.boolean_tabletype;
4973: l_number_custom_hier number;
4974: --------------------------
4975: l_seed number;
4976: --------------------------
5033: l_number_custom_hier:=0;
5034: for i in 1..p_number_periodicity_data loop
5035: if p_periodicity_data(i).periodicity_type<>0 then
5036: for j in 1..l_number_custom_periodicity loop
5037: if BSC_IM_UTILS.parse_and_find(p_periodicity_data(i).source,',',l_custom_periodicity(j)) then
5038: l_number_custom_hier:=l_number_custom_hier+1;
5039: l_custom_parent(l_number_custom_hier):=p_periodicity_data(i).periodicity_id;
5040: l_custom_child(l_number_custom_hier):=l_custom_periodicity(j);
5041: l_custom_flag(l_number_custom_hier):=true;
5050: end loop;
5051: end if;
5052: --now see if we can eliminate some
5053: for i in 1..l_number_custom_hier loop
5054: if BSC_IM_UTILS.in_array(l_child_hier,l_number_hier,l_custom_child(i)) then
5055: l_custom_flag(i):=false;
5056: else
5057: for j in 1..l_number_custom_hier loop
5058: if i<>j and l_custom_child(i)=l_custom_child(j) and l_custom_parent(i)
5131: end loop;
5132: end if;
5133: return true;
5134: Exception when others then
5135: BSC_IM_UTILS.g_status_message:=sqlerrm;
5136: g_status_message:=sqlerrm;
5137: write_to_log_file_n('Exception in built_hier '||sqlerrm);
5138: return false;
5139: End;
5139: End;
5140:
5141: function built_hier_rec(
5142: p_parent number,
5143: p_parent_hier BSC_IM_UTILS.number_tabletype,
5144: p_child_hier BSC_IM_UTILS.number_tabletype,
5145: p_parent_type BSC_IM_UTILS.varchar_tabletype,
5146: p_number_rel number,
5147: p_hier out nocopy BSC_IM_UTILS.varchar_tabletype,
5140:
5141: function built_hier_rec(
5142: p_parent number,
5143: p_parent_hier BSC_IM_UTILS.number_tabletype,
5144: p_child_hier BSC_IM_UTILS.number_tabletype,
5145: p_parent_type BSC_IM_UTILS.varchar_tabletype,
5146: p_number_rel number,
5147: p_hier out nocopy BSC_IM_UTILS.varchar_tabletype,
5148: p_hier_type out nocopy BSC_IM_UTILS.varchar_tabletype,
5141: function built_hier_rec(
5142: p_parent number,
5143: p_parent_hier BSC_IM_UTILS.number_tabletype,
5144: p_child_hier BSC_IM_UTILS.number_tabletype,
5145: p_parent_type BSC_IM_UTILS.varchar_tabletype,
5146: p_number_rel number,
5147: p_hier out nocopy BSC_IM_UTILS.varchar_tabletype,
5148: p_hier_type out nocopy BSC_IM_UTILS.varchar_tabletype,
5149: p_number_hier out nocopy number
5143: p_parent_hier BSC_IM_UTILS.number_tabletype,
5144: p_child_hier BSC_IM_UTILS.number_tabletype,
5145: p_parent_type BSC_IM_UTILS.varchar_tabletype,
5146: p_number_rel number,
5147: p_hier out nocopy BSC_IM_UTILS.varchar_tabletype,
5148: p_hier_type out nocopy BSC_IM_UTILS.varchar_tabletype,
5149: p_number_hier out nocopy number
5150: )return boolean is
5151: -------
5144: p_child_hier BSC_IM_UTILS.number_tabletype,
5145: p_parent_type BSC_IM_UTILS.varchar_tabletype,
5146: p_number_rel number,
5147: p_hier out nocopy BSC_IM_UTILS.varchar_tabletype,
5148: p_hier_type out nocopy BSC_IM_UTILS.varchar_tabletype,
5149: p_number_hier out nocopy number
5150: )return boolean is
5151: -------
5152: l_hier BSC_IM_UTILS.varchar_tabletype;
5148: p_hier_type out nocopy BSC_IM_UTILS.varchar_tabletype,
5149: p_number_hier out nocopy number
5150: )return boolean is
5151: -------
5152: l_hier BSC_IM_UTILS.varchar_tabletype;
5153: l_hier_type BSC_IM_UTILS.varchar_tabletype;
5154: l_number_hier number;
5155: ----
5156: l_found boolean;
5149: p_number_hier out nocopy number
5150: )return boolean is
5151: -------
5152: l_hier BSC_IM_UTILS.varchar_tabletype;
5153: l_hier_type BSC_IM_UTILS.varchar_tabletype;
5154: l_number_hier number;
5155: ----
5156: l_found boolean;
5157: ----
5186: p_hier_type(p_number_hier):='Std';
5187: end if;
5188: return true;
5189: Exception when others then
5190: BSC_IM_UTILS.g_status_message:=sqlerrm;
5191: g_status_message:=sqlerrm;
5192: write_to_log_file_n('Exception in built_hier_rec '||sqlerrm);
5193: return false;
5194: End;
5206: end if;
5207: end loop;
5208: return null;
5209: Exception when others then
5210: BSC_IM_UTILS.g_status_message:=sqlerrm;
5211: g_status_message:=sqlerrm;
5212: write_to_log_file_n('Exception in get_periodicity_for_type '||sqlerrm);
5213: return null;
5214: End;
5225: end if;
5226: end loop;
5227: return null;
5228: Exception when others then
5229: BSC_IM_UTILS.g_status_message:=sqlerrm;
5230: g_status_message:=sqlerrm;
5231: write_to_log_file_n('Exception in get_period_type_id_for_period '||sqlerrm);
5232: return null;
5233: End;
5247: fetch c1 into l_period_type_id;
5248: close c1;
5249: return l_period_type_id;
5250: Exception when others then
5251: BSC_IM_UTILS.g_status_message:=sqlerrm;
5252: g_status_message:=sqlerrm;
5253: write_to_log_file_n('Exception in get_period_type_id_for_period '||sqlerrm);
5254: return null;
5255: End;
5268: p_periodicity_data cal_periodicity_table,
5269: p_number_periodicity_data number
5270: )return boolean is
5271: -----------------------------
5272: l_periods BSC_IM_UTILS.number_tabletype;
5273: l_period_record_type BSC_IM_UTILS.number_tabletype;
5274: l_period_period_type BSC_IM_UTILS.number_tabletype;
5275: l_period_periodicity_id BSC_IM_UTILS.number_tabletype;
5276: l_period_column BSC_IM_UTILS.varchar_tabletype;
5269: p_number_periodicity_data number
5270: )return boolean is
5271: -----------------------------
5272: l_periods BSC_IM_UTILS.number_tabletype;
5273: l_period_record_type BSC_IM_UTILS.number_tabletype;
5274: l_period_period_type BSC_IM_UTILS.number_tabletype;
5275: l_period_periodicity_id BSC_IM_UTILS.number_tabletype;
5276: l_period_column BSC_IM_UTILS.varchar_tabletype;
5277: l_number_periods number;
5270: )return boolean is
5271: -----------------------------
5272: l_periods BSC_IM_UTILS.number_tabletype;
5273: l_period_record_type BSC_IM_UTILS.number_tabletype;
5274: l_period_period_type BSC_IM_UTILS.number_tabletype;
5275: l_period_periodicity_id BSC_IM_UTILS.number_tabletype;
5276: l_period_column BSC_IM_UTILS.varchar_tabletype;
5277: l_number_periods number;
5278: -----------------------------
5271: -----------------------------
5272: l_periods BSC_IM_UTILS.number_tabletype;
5273: l_period_record_type BSC_IM_UTILS.number_tabletype;
5274: l_period_period_type BSC_IM_UTILS.number_tabletype;
5275: l_period_periodicity_id BSC_IM_UTILS.number_tabletype;
5276: l_period_column BSC_IM_UTILS.varchar_tabletype;
5277: l_number_periods number;
5278: -----------------------------
5279: l_stmt varchar2(32750);
5272: l_periods BSC_IM_UTILS.number_tabletype;
5273: l_period_record_type BSC_IM_UTILS.number_tabletype;
5274: l_period_period_type BSC_IM_UTILS.number_tabletype;
5275: l_period_periodicity_id BSC_IM_UTILS.number_tabletype;
5276: l_period_column BSC_IM_UTILS.varchar_tabletype;
5277: l_number_periods number;
5278: -----------------------------
5279: l_stmt varchar2(32750);
5280: randomString varchar2(20);
5288: write_to_log_file_n('In load_reporting_calendar, p_calendar_id='||p_calendar_id||','||
5289: 'p_calendar_type='||p_calendar_type||',p_hierarchy='||p_hierarchy||',p_hierarchy_type='||p_hierarchy_type||
5290: get_time);
5291: end if;
5292: if BSC_im_utils.parse_values(p_hierarchy,',',l_periods,l_number_periods)=false then
5293: return false;
5294: end if;
5295: --for these periods get the record_type_id
5296: --for each periodicity, there is one record type id which is the same as
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;
5336: l_stmt:=l_stmt||'l_num_'||l_period_column(1)||'_'||l_period_column(i)||' number:=0;'||newline;
5337: l_stmt:=l_stmt||'l_prev_'||l_period_column(1)||'_'||l_period_column(i)||' number:=null;'||newline;
5338: l_stmt:=l_stmt||'l_'||l_period_column(i)||'_period_count BSC_IM_UTILS.number_tabletype;'||newline;
5339: end loop;
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;
5336: l_stmt:=l_stmt||'l_num_'||l_period_column(1)||'_'||l_period_column(i)||' number:=0;'||newline;
5337: l_stmt:=l_stmt||'l_prev_'||l_period_column(1)||'_'||l_period_column(i)||' number:=null;'||newline;
5338: l_stmt:=l_stmt||'l_'||l_period_column(i)||'_period_count BSC_IM_UTILS.number_tabletype;'||newline;
5339: end loop;
5340: l_stmt:=l_stmt||'l_final_report_date BSC_IM_UTILS.date_tabletype;
5341: l_final_period BSC_IM_UTILS.number_tabletype;
5342: l_final_year BSC_IM_UTILS.number_tabletype;
5336: l_stmt:=l_stmt||'l_num_'||l_period_column(1)||'_'||l_period_column(i)||' number:=0;'||newline;
5337: l_stmt:=l_stmt||'l_prev_'||l_period_column(1)||'_'||l_period_column(i)||' number:=null;'||newline;
5338: l_stmt:=l_stmt||'l_'||l_period_column(i)||'_period_count BSC_IM_UTILS.number_tabletype;'||newline;
5339: end loop;
5340: l_stmt:=l_stmt||'l_final_report_date BSC_IM_UTILS.date_tabletype;
5341: l_final_period BSC_IM_UTILS.number_tabletype;
5342: l_final_year BSC_IM_UTILS.number_tabletype;
5343: l_final_period_type_id BSC_IM_UTILS.number_tabletype;
5344: l_final_periodicity_id BSC_IM_UTILS.number_tabletype;
5337: l_stmt:=l_stmt||'l_prev_'||l_period_column(1)||'_'||l_period_column(i)||' number:=null;'||newline;
5338: l_stmt:=l_stmt||'l_'||l_period_column(i)||'_period_count BSC_IM_UTILS.number_tabletype;'||newline;
5339: end loop;
5340: l_stmt:=l_stmt||'l_final_report_date BSC_IM_UTILS.date_tabletype;
5341: l_final_period BSC_IM_UTILS.number_tabletype;
5342: l_final_year BSC_IM_UTILS.number_tabletype;
5343: l_final_period_type_id BSC_IM_UTILS.number_tabletype;
5344: l_final_periodicity_id BSC_IM_UTILS.number_tabletype;
5345: l_final_period_flag BSC_IM_UTILS.number_tabletype;
5338: l_stmt:=l_stmt||'l_'||l_period_column(i)||'_period_count BSC_IM_UTILS.number_tabletype;'||newline;
5339: end loop;
5340: l_stmt:=l_stmt||'l_final_report_date BSC_IM_UTILS.date_tabletype;
5341: l_final_period BSC_IM_UTILS.number_tabletype;
5342: l_final_year BSC_IM_UTILS.number_tabletype;
5343: l_final_period_type_id BSC_IM_UTILS.number_tabletype;
5344: l_final_periodicity_id BSC_IM_UTILS.number_tabletype;
5345: l_final_period_flag BSC_IM_UTILS.number_tabletype;
5346: l_final_period_count BSC_IM_UTILS.number_tabletype;
5339: end loop;
5340: l_stmt:=l_stmt||'l_final_report_date BSC_IM_UTILS.date_tabletype;
5341: l_final_period BSC_IM_UTILS.number_tabletype;
5342: l_final_year BSC_IM_UTILS.number_tabletype;
5343: l_final_period_type_id BSC_IM_UTILS.number_tabletype;
5344: l_final_periodicity_id BSC_IM_UTILS.number_tabletype;
5345: l_final_period_flag BSC_IM_UTILS.number_tabletype;
5346: l_final_period_count BSC_IM_UTILS.number_tabletype;
5347: l_num_final number:=0;
5340: l_stmt:=l_stmt||'l_final_report_date BSC_IM_UTILS.date_tabletype;
5341: l_final_period BSC_IM_UTILS.number_tabletype;
5342: l_final_year BSC_IM_UTILS.number_tabletype;
5343: l_final_period_type_id BSC_IM_UTILS.number_tabletype;
5344: l_final_periodicity_id BSC_IM_UTILS.number_tabletype;
5345: l_final_period_flag BSC_IM_UTILS.number_tabletype;
5346: l_final_period_count BSC_IM_UTILS.number_tabletype;
5347: l_num_final number:=0;
5348: -------------------
5341: l_final_period BSC_IM_UTILS.number_tabletype;
5342: l_final_year BSC_IM_UTILS.number_tabletype;
5343: l_final_period_type_id BSC_IM_UTILS.number_tabletype;
5344: l_final_periodicity_id BSC_IM_UTILS.number_tabletype;
5345: l_final_period_flag BSC_IM_UTILS.number_tabletype;
5346: l_final_period_count BSC_IM_UTILS.number_tabletype;
5347: l_num_final number:=0;
5348: -------------------
5349: begin
5342: l_final_year BSC_IM_UTILS.number_tabletype;
5343: l_final_period_type_id BSC_IM_UTILS.number_tabletype;
5344: l_final_periodicity_id BSC_IM_UTILS.number_tabletype;
5345: l_final_period_flag BSC_IM_UTILS.number_tabletype;
5346: l_final_period_count BSC_IM_UTILS.number_tabletype;
5347: l_num_final number:=0;
5348: -------------------
5349: begin
5350: l_number_cal_records:=1;
5487: end if;
5488: commit;
5489:
5490: exception when others then
5491: BSC_IM_UTILS.g_status_message:=sqlerrm;
5492: g_status_message:=sqlerrm;
5493: write_to_log_file_n('Exception in load_reporting_calendar '||sqlerrm);
5494: return false;
5495: end;
5500: end if;
5501: execute immediate l_stmt;
5502: return true;
5503: Exception when others then
5504: BSC_IM_UTILS.g_status_message:=sqlerrm;
5505: g_status_message:=sqlerrm;
5506: write_to_log_file_n('Exception in load_reporting_calendar '||sqlerrm);
5507: return false;
5508: End;
5520: p_periodicity_data cal_periodicity_table,
5521: p_number_periodicity_data number
5522: )return boolean is
5523: --------------------
5524: l_year_year BSC_IM_UTILS.number_tabletype;
5525: l_year_day_count BSC_IM_UTILS.number_tabletype;--number of days in year
5526: l_prev_year_year number;
5527: l_num_year_year number:=0;
5528: l_year_year_PTD number;
5521: p_number_periodicity_data number
5522: )return boolean is
5523: --------------------
5524: l_year_year BSC_IM_UTILS.number_tabletype;
5525: l_year_day_count BSC_IM_UTILS.number_tabletype;--number of days in year
5526: l_prev_year_year number;
5527: l_num_year_year number:=0;
5528: l_year_year_PTD number;
5529: l_year_year_PID number;
5528: l_year_year_PTD number;
5529: l_year_year_PID number;
5530: l_year_year_RTD number;
5531: --
5532: l_year_quarter BSC_IM_UTILS.number_tabletype;
5533: l_quarter_day_count BSC_IM_UTILS.number_tabletype;
5534: l_prev_year_quarter number;
5535: l_num_year_quarter number:=0;
5536: l_year_quarter_PTD number;
5529: l_year_year_PID number;
5530: l_year_year_RTD number;
5531: --
5532: l_year_quarter BSC_IM_UTILS.number_tabletype;
5533: l_quarter_day_count BSC_IM_UTILS.number_tabletype;
5534: l_prev_year_quarter number;
5535: l_num_year_quarter number:=0;
5536: l_year_quarter_PTD number;
5537: l_year_quarter_PID number;
5536: l_year_quarter_PTD number;
5537: l_year_quarter_PID number;
5538: l_year_quarter_RTD number;
5539: --
5540: l_year_month BSC_IM_UTILS.number_tabletype;
5541: l_month_day_count BSC_IM_UTILS.number_tabletype;
5542: l_prev_year_month number;
5543: l_num_year_month number:=0;
5544: l_year_month_PTD number;
5537: l_year_quarter_PID number;
5538: l_year_quarter_RTD number;
5539: --
5540: l_year_month BSC_IM_UTILS.number_tabletype;
5541: l_month_day_count BSC_IM_UTILS.number_tabletype;
5542: l_prev_year_month number;
5543: l_num_year_month number:=0;
5544: l_year_month_PTD number;
5545: l_year_month_PID number;
5544: l_year_month_PTD number;
5545: l_year_month_PID number;
5546: l_year_month_RTD number;
5547: --
5548: l_year_week BSC_IM_UTILS.number_tabletype;
5549: l_week_day_count BSC_IM_UTILS.number_tabletype;
5550: l_prev_year_week number;
5551: l_num_year_week number:=0;
5552: l_year_week_PTD number;
5545: l_year_month_PID number;
5546: l_year_month_RTD number;
5547: --
5548: l_year_week BSC_IM_UTILS.number_tabletype;
5549: l_week_day_count BSC_IM_UTILS.number_tabletype;
5550: l_prev_year_week number;
5551: l_num_year_week number:=0;
5552: l_year_week_PTD number;
5553: l_year_week_PID number;
5552: l_year_week_PTD number;
5553: l_year_week_PID number;
5554: l_year_week_RTD number;
5555: --
5556: l_year_day BSC_IM_UTILS.number_tabletype;
5557: l_prev_year_day number;
5558: l_num_year_day number:=0;
5559: l_year_day_PTD number;
5560: l_year_day_PID number;
5560: l_year_day_PID number;
5561: l_year_day_RTD number;
5562: --
5563: --for week to date
5564: l_week_day BSC_IM_UTILS.number_tabletype;
5565: l_prev_week_day number;
5566: l_num_week_day number:=0;
5567: l_week_day_PTD number;
5568: l_week_day_PID number;
5567: l_week_day_PTD number;
5568: l_week_day_PID number;
5569: l_week_day_RTD number;
5570: -----
5571: l_month_day BSC_IM_UTILS.number_tabletype; --carries record type id of 4 i.e mtd only
5572: l_num_month_day number:=0;
5573: -----
5574: l_final_report_date BSC_IM_UTILS.date_tabletype;
5575: l_final_period BSC_IM_UTILS.number_tabletype;
5570: -----
5571: l_month_day BSC_IM_UTILS.number_tabletype; --carries record type id of 4 i.e mtd only
5572: l_num_month_day number:=0;
5573: -----
5574: l_final_report_date BSC_IM_UTILS.date_tabletype;
5575: l_final_period BSC_IM_UTILS.number_tabletype;
5576: l_final_year BSC_IM_UTILS.number_tabletype;
5577: l_final_period_type_id BSC_IM_UTILS.number_tabletype;
5578: l_final_periodicity_id BSC_IM_UTILS.number_tabletype;
5571: l_month_day BSC_IM_UTILS.number_tabletype; --carries record type id of 4 i.e mtd only
5572: l_num_month_day number:=0;
5573: -----
5574: l_final_report_date BSC_IM_UTILS.date_tabletype;
5575: l_final_period BSC_IM_UTILS.number_tabletype;
5576: l_final_year BSC_IM_UTILS.number_tabletype;
5577: l_final_period_type_id BSC_IM_UTILS.number_tabletype;
5578: l_final_periodicity_id BSC_IM_UTILS.number_tabletype;
5579: l_final_record_type_id BSC_IM_UTILS.number_tabletype;
5572: l_num_month_day number:=0;
5573: -----
5574: l_final_report_date BSC_IM_UTILS.date_tabletype;
5575: l_final_period BSC_IM_UTILS.number_tabletype;
5576: l_final_year BSC_IM_UTILS.number_tabletype;
5577: l_final_period_type_id BSC_IM_UTILS.number_tabletype;
5578: l_final_periodicity_id BSC_IM_UTILS.number_tabletype;
5579: l_final_record_type_id BSC_IM_UTILS.number_tabletype;
5580: l_final_day_count BSC_IM_UTILS.number_tabletype;
5573: -----
5574: l_final_report_date BSC_IM_UTILS.date_tabletype;
5575: l_final_period BSC_IM_UTILS.number_tabletype;
5576: l_final_year BSC_IM_UTILS.number_tabletype;
5577: l_final_period_type_id BSC_IM_UTILS.number_tabletype;
5578: l_final_periodicity_id BSC_IM_UTILS.number_tabletype;
5579: l_final_record_type_id BSC_IM_UTILS.number_tabletype;
5580: l_final_day_count BSC_IM_UTILS.number_tabletype;
5581: l_final_period_flag BSC_IM_UTILS.number_tabletype;
5574: l_final_report_date BSC_IM_UTILS.date_tabletype;
5575: l_final_period BSC_IM_UTILS.number_tabletype;
5576: l_final_year BSC_IM_UTILS.number_tabletype;
5577: l_final_period_type_id BSC_IM_UTILS.number_tabletype;
5578: l_final_periodicity_id BSC_IM_UTILS.number_tabletype;
5579: l_final_record_type_id BSC_IM_UTILS.number_tabletype;
5580: l_final_day_count BSC_IM_UTILS.number_tabletype;
5581: l_final_period_flag BSC_IM_UTILS.number_tabletype;
5582: l_num_final number:=0;
5575: l_final_period BSC_IM_UTILS.number_tabletype;
5576: l_final_year BSC_IM_UTILS.number_tabletype;
5577: l_final_period_type_id BSC_IM_UTILS.number_tabletype;
5578: l_final_periodicity_id BSC_IM_UTILS.number_tabletype;
5579: l_final_record_type_id BSC_IM_UTILS.number_tabletype;
5580: l_final_day_count BSC_IM_UTILS.number_tabletype;
5581: l_final_period_flag BSC_IM_UTILS.number_tabletype;
5582: l_num_final number:=0;
5583: --
5576: l_final_year BSC_IM_UTILS.number_tabletype;
5577: l_final_period_type_id BSC_IM_UTILS.number_tabletype;
5578: l_final_periodicity_id BSC_IM_UTILS.number_tabletype;
5579: l_final_record_type_id BSC_IM_UTILS.number_tabletype;
5580: l_final_day_count BSC_IM_UTILS.number_tabletype;
5581: l_final_period_flag BSC_IM_UTILS.number_tabletype;
5582: l_num_final number:=0;
5583: --
5584: Begin
5577: l_final_period_type_id BSC_IM_UTILS.number_tabletype;
5578: l_final_periodicity_id BSC_IM_UTILS.number_tabletype;
5579: l_final_record_type_id BSC_IM_UTILS.number_tabletype;
5580: l_final_day_count BSC_IM_UTILS.number_tabletype;
5581: l_final_period_flag BSC_IM_UTILS.number_tabletype;
5582: l_num_final number:=0;
5583: --
5584: Begin
5585: if g_debug then
5760: l_final_day_count(l_num_final):=1;
5761: end loop;
5762: --now the week and month contributions
5763: for j in 1..l_num_week_day loop
5764: if BSC_IM_UTILS.in_array(l_year_day,l_num_year_day,l_week_day(j))=false then
5765: l_num_final:=l_num_final+1;
5766: l_final_report_date(l_num_final):=to_date(p_calendar_data(i).calendar_month||'/'||
5767: p_calendar_data(i).calendar_day||'/'||p_calendar_data(i).calendar_year,'MM/DD/YYYY');
5768: l_final_period(l_num_final):=l_week_day(j);
5833: commit;
5834:
5835: return true;
5836: Exception when others then
5837: BSC_IM_UTILS.g_status_message:=sqlerrm;
5838: g_status_message:=sqlerrm;
5839: write_to_log_file_n('Exception in load_reporting_calendar_DBI '||sqlerrm);
5840: return false;
5841: End;
5856: )return boolean is
5857: --------------------
5858: l_rolling_flag varchar2(40);
5859: --------------------
5860: l_year_year BSC_IM_UTILS.number_tabletype;
5861: l_year_day_count BSC_IM_UTILS.number_tabletype;--number of days in year
5862: l_prev_year_year number;
5863: l_num_year_year number:=0;
5864: l_year_year_PTD number;
5857: --------------------
5858: l_rolling_flag varchar2(40);
5859: --------------------
5860: l_year_year BSC_IM_UTILS.number_tabletype;
5861: l_year_day_count BSC_IM_UTILS.number_tabletype;--number of days in year
5862: l_prev_year_year number;
5863: l_num_year_year number:=0;
5864: l_year_year_PTD number;
5865: l_year_year_PID number;
5864: l_year_year_PTD number;
5865: l_year_year_PID number;
5866: l_year_year_RTD number;
5867: --
5868: l_year_quarter BSC_IM_UTILS.number_tabletype;
5869: l_quarter_day_count BSC_IM_UTILS.number_tabletype;
5870: l_prev_year_quarter number;
5871: l_num_year_quarter number:=0;
5872: l_year_quarter_PTD number;
5865: l_year_year_PID number;
5866: l_year_year_RTD number;
5867: --
5868: l_year_quarter BSC_IM_UTILS.number_tabletype;
5869: l_quarter_day_count BSC_IM_UTILS.number_tabletype;
5870: l_prev_year_quarter number;
5871: l_num_year_quarter number:=0;
5872: l_year_quarter_PTD number;
5873: l_year_quarter_PID number;
5872: l_year_quarter_PTD number;
5873: l_year_quarter_PID number;
5874: l_year_quarter_RTD number;
5875: --
5876: l_year_month BSC_IM_UTILS.number_tabletype;
5877: l_month_day_count BSC_IM_UTILS.number_tabletype;
5878: l_prev_year_month number;
5879: l_num_year_month number:=0;
5880: l_year_month_PTD number;
5873: l_year_quarter_PID number;
5874: l_year_quarter_RTD number;
5875: --
5876: l_year_month BSC_IM_UTILS.number_tabletype;
5877: l_month_day_count BSC_IM_UTILS.number_tabletype;
5878: l_prev_year_month number;
5879: l_num_year_month number:=0;
5880: l_year_month_PTD number;
5881: l_year_month_PID number;
5880: l_year_month_PTD number;
5881: l_year_month_PID number;
5882: l_year_month_RTD number;
5883: --
5884: l_year_week BSC_IM_UTILS.number_tabletype;
5885: l_week_day_count BSC_IM_UTILS.number_tabletype;
5886: l_prev_year_week number;
5887: l_num_year_week number:=0;
5888: l_year_week_PTD number;
5881: l_year_month_PID number;
5882: l_year_month_RTD number;
5883: --
5884: l_year_week BSC_IM_UTILS.number_tabletype;
5885: l_week_day_count BSC_IM_UTILS.number_tabletype;
5886: l_prev_year_week number;
5887: l_num_year_week number:=0;
5888: l_year_week_PTD number;
5889: l_year_week_PID number;
5888: l_year_week_PTD number;
5889: l_year_week_PID number;
5890: l_year_week_RTD number;
5891: --
5892: l_year_day BSC_IM_UTILS.number_tabletype;
5893: l_prev_year_day number;
5894: l_num_year_day number:=0;
5895: l_year_day_PTD number;
5896: l_year_day_PID number;
5896: l_year_day_PID number;
5897: l_year_day_RTD number;
5898: --
5899: --for week to date
5900: l_week_day BSC_IM_UTILS.number_tabletype;
5901: l_prev_week_day number;
5902: l_num_week_day number:=0;
5903: l_week_day_PTD number;
5904: l_week_day_PID number;
5903: l_week_day_PTD number;
5904: l_week_day_PID number;
5905: l_week_day_RTD number;
5906: -----
5907: l_month_day BSC_IM_UTILS.number_tabletype; --carries record type id of 4 i.e mtd only
5908: l_num_month_day number:=0;
5909: -----
5910: l_final_report_date BSC_IM_UTILS.date_tabletype;
5911: l_final_period BSC_IM_UTILS.number_tabletype;
5906: -----
5907: l_month_day BSC_IM_UTILS.number_tabletype; --carries record type id of 4 i.e mtd only
5908: l_num_month_day number:=0;
5909: -----
5910: l_final_report_date BSC_IM_UTILS.date_tabletype;
5911: l_final_period BSC_IM_UTILS.number_tabletype;
5912: l_final_year BSC_IM_UTILS.number_tabletype;
5913: l_final_period_type_id BSC_IM_UTILS.number_tabletype;
5914: l_final_periodicity_id BSC_IM_UTILS.number_tabletype;
5907: l_month_day BSC_IM_UTILS.number_tabletype; --carries record type id of 4 i.e mtd only
5908: l_num_month_day number:=0;
5909: -----
5910: l_final_report_date BSC_IM_UTILS.date_tabletype;
5911: l_final_period BSC_IM_UTILS.number_tabletype;
5912: l_final_year BSC_IM_UTILS.number_tabletype;
5913: l_final_period_type_id BSC_IM_UTILS.number_tabletype;
5914: l_final_periodicity_id BSC_IM_UTILS.number_tabletype;
5915: l_final_record_type_id BSC_IM_UTILS.number_tabletype;
5908: l_num_month_day number:=0;
5909: -----
5910: l_final_report_date BSC_IM_UTILS.date_tabletype;
5911: l_final_period BSC_IM_UTILS.number_tabletype;
5912: l_final_year BSC_IM_UTILS.number_tabletype;
5913: l_final_period_type_id BSC_IM_UTILS.number_tabletype;
5914: l_final_periodicity_id BSC_IM_UTILS.number_tabletype;
5915: l_final_record_type_id BSC_IM_UTILS.number_tabletype;
5916: l_final_day_count BSC_IM_UTILS.number_tabletype;
5909: -----
5910: l_final_report_date BSC_IM_UTILS.date_tabletype;
5911: l_final_period BSC_IM_UTILS.number_tabletype;
5912: l_final_year BSC_IM_UTILS.number_tabletype;
5913: l_final_period_type_id BSC_IM_UTILS.number_tabletype;
5914: l_final_periodicity_id BSC_IM_UTILS.number_tabletype;
5915: l_final_record_type_id BSC_IM_UTILS.number_tabletype;
5916: l_final_day_count BSC_IM_UTILS.number_tabletype;
5917: l_final_period_flag BSC_IM_UTILS.number_tabletype;
5910: l_final_report_date BSC_IM_UTILS.date_tabletype;
5911: l_final_period BSC_IM_UTILS.number_tabletype;
5912: l_final_year BSC_IM_UTILS.number_tabletype;
5913: l_final_period_type_id BSC_IM_UTILS.number_tabletype;
5914: l_final_periodicity_id BSC_IM_UTILS.number_tabletype;
5915: l_final_record_type_id BSC_IM_UTILS.number_tabletype;
5916: l_final_day_count BSC_IM_UTILS.number_tabletype;
5917: l_final_period_flag BSC_IM_UTILS.number_tabletype;
5918: l_num_final number:=0;
5911: l_final_period BSC_IM_UTILS.number_tabletype;
5912: l_final_year BSC_IM_UTILS.number_tabletype;
5913: l_final_period_type_id BSC_IM_UTILS.number_tabletype;
5914: l_final_periodicity_id BSC_IM_UTILS.number_tabletype;
5915: l_final_record_type_id BSC_IM_UTILS.number_tabletype;
5916: l_final_day_count BSC_IM_UTILS.number_tabletype;
5917: l_final_period_flag BSC_IM_UTILS.number_tabletype;
5918: l_num_final number:=0;
5919: --
5912: l_final_year BSC_IM_UTILS.number_tabletype;
5913: l_final_period_type_id BSC_IM_UTILS.number_tabletype;
5914: l_final_periodicity_id BSC_IM_UTILS.number_tabletype;
5915: l_final_record_type_id BSC_IM_UTILS.number_tabletype;
5916: l_final_day_count BSC_IM_UTILS.number_tabletype;
5917: l_final_period_flag BSC_IM_UTILS.number_tabletype;
5918: l_num_final number:=0;
5919: --
5920: Begin
5913: l_final_period_type_id BSC_IM_UTILS.number_tabletype;
5914: l_final_periodicity_id BSC_IM_UTILS.number_tabletype;
5915: l_final_record_type_id BSC_IM_UTILS.number_tabletype;
5916: l_final_day_count BSC_IM_UTILS.number_tabletype;
5917: l_final_period_flag BSC_IM_UTILS.number_tabletype;
5918: l_num_final number:=0;
5919: --
5920: Begin
5921: if g_debug then
6085: l_final_day_count(l_num_final):=1;
6086: end loop;
6087: --now the week and month contributions
6088: for j in 1..l_num_week_day loop
6089: if BSC_IM_UTILS.in_array(l_year_day,l_num_year_day,l_week_day(j))=false then
6090: l_num_final:=l_num_final+1;
6091: l_final_report_date(l_num_final):=to_date(p_calendar_data(i).calendar_month||'/'||
6092: p_calendar_data(i).calendar_day||'/'||p_calendar_data(i).calendar_year,'MM/DD/YYYY');
6093: l_final_period(l_num_final):=l_week_day(j);
6159: commit;
6160:
6161: return true;
6162: Exception when others then
6163: BSC_IM_UTILS.g_status_message:=sqlerrm;
6164: g_status_message:=sqlerrm;
6165: write_to_log_file_n('Exception in load_rpt_cal_DBI_rolling '||sqlerrm);
6166: return false;
6167: End;
6169: function get_reporting_calendar_name return varchar2 is
6170: Begin
6171: return upper('bsc_db_report_struct');
6172: Exception when others then
6173: BSC_IM_UTILS.g_status_message:=sqlerrm;
6174: g_status_message:=sqlerrm;
6175: write_to_log_file_n('Exception in get_reporting_calendar_name '||sqlerrm);
6176: return null;
6177: End;
6177: End;
6178:
6179: function get_dim_level_cols(
6180: p_level varchar2,
6181: p_columns out nocopy BSC_IM_UTILS.varchar_tabletype,
6182: p_column_type out nocopy BSC_IM_UTILS.varchar_tabletype,
6183: p_number_columns out nocopy number
6184: )return boolean is
6185: l_stmt varchar2(5000);
6178:
6179: function get_dim_level_cols(
6180: p_level varchar2,
6181: p_columns out nocopy BSC_IM_UTILS.varchar_tabletype,
6182: p_column_type out nocopy BSC_IM_UTILS.varchar_tabletype,
6183: p_number_columns out nocopy number
6184: )return boolean is
6185: l_stmt varchar2(5000);
6186: ---------------------------------------------
6214: end loop;
6215: end if;
6216: return true;
6217: Exception when others then
6218: BSC_IM_UTILS.g_status_message:=sqlerrm;
6219: g_status_message:=sqlerrm;
6220: write_to_log_file_n('Exception in get_dim_level_cols '||sqlerrm);
6221: return false;
6222: End;
6222: End;
6223:
6224: function get_s_tables_for_mv(
6225: p_mv varchar2,
6226: p_s_tables out nocopy BSC_IM_UTILS.varchar_tabletype,
6227: p_number_s_tables out nocopy number
6228: )return boolean is
6229: l_stmt varchar2(5000);
6230: TYPE CurTyp IS REF CURSOR;
6283: end if;
6284: end if;
6285: return true;
6286: Exception when others then
6287: BSC_IM_UTILS.g_status_message:=sqlerrm;
6288: g_status_message:=sqlerrm;
6289: write_to_log_file_n('Exception in get_s_tables_for_mv '||sqlerrm);
6290: return false;
6291: End;
6305: fetch c1 into l_name;
6306: close c1;
6307: return l_name;
6308: Exception when others then
6309: BSC_IM_UTILS.g_status_message:=sqlerrm;
6310: g_status_message:=sqlerrm;
6311: write_to_log_file_n('Exception in get_level_short_name '||sqlerrm);
6312: return null;
6313: End;
6322: if g_debug then
6323: write_to_log_file_n('Going to call bsc_update_dim.Create_Dbi_Dim_Tables');
6324: end if;
6325: if bsc_update_dim.Create_Dbi_Dim_Tables(l_error_message)=false then
6326: BSC_IM_UTILS.g_status_message:=l_error_message;
6327: g_status_message:=l_error_message;
6328: write_to_log_file_n('Error bsc_update_dim.Create_Dbi_Dim_Tables '||l_error_message);
6329: return false;
6330: end if;
6331: g_create_dbi_dim_tables:=true; --create these tables only once
6332: end if;
6333: return true;
6334: Exception when others then
6335: BSC_IM_UTILS.g_status_message:=sqlerrm;
6336: g_status_message:=sqlerrm;
6337: write_to_log_file_n('Exception in create_dbi_dim_tables '||sqlerrm);
6338: return false;
6339: End;
6458: procedure create_int_md_fk(
6459: p_mv_name varchar2
6460: ) is
6461: --
6462: l_s_tables BSC_IM_UTILS.varchar_tabletype;
6463: l_number_s_tables number;
6464: l_fk BSC_IM_UTILS.varchar_tabletype;
6465: l_number_fk number;
6466: l_exception exception;
6460: ) is
6461: --
6462: l_s_tables BSC_IM_UTILS.varchar_tabletype;
6463: l_number_s_tables number;
6464: l_fk BSC_IM_UTILS.varchar_tabletype;
6465: l_number_fk number;
6466: l_exception exception;
6467: --
6468: Begin