[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT PERIODICITY_ID
FROM BSC_SYS_PERIODICITIES
WHERE CALENDAR_ID = p_calendar_id AND CUSTOM_CODE < p_custom_code
ORDER BY PERIODICITY_ID;
SELECT PERIODICITY_ID,SOURCE
FROM BSC_SYS_PERIODICITIES
WHERE CALENDAR_ID = p_calendar_id
ORDER BY PERIODICITY_ID;
SELECT PERIODICITY_ID
FROM BSC_SYS_PERIODICITIES
WHERE CALENDAR_ID = p_calendar_id AND PERIODICITY_TYPE = p_periodicity_type;
h_tmp_array BSC_UPDATE_UTIL.t_array_of_number;
h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
' INSERT INTO BSC_SYS_PERIODICITIES ' ||
' (PERIODICITY_ID,NUM_OF_PERIODS,SOURCE,NUM_OF_SUBPERIODS,PERIOD_COL_NAME, ' ||
' SUBPERIOD_COL_NAME,YEARLY_FLAG,EDW_FLAG,CALENDAR_ID,EDW_PERIODICITY_ID, ' ||
' CUSTOM_CODE,DB_COLUMN_NAME,PERIODICITY_TYPE) ' ||
' SELECT BSC_SYS_PERIODICITY_ID_S.NEXTVAL PERIODICITY_ID,NUM_OF_PERIODS,SOURCE,NUM_OF_SUBPERIODS, '||
' PERIOD_COL_NAME,SUBPERIOD_COL_NAME,YEARLY_FLAG,EDW_FLAG, '||
' :1 CALENDAR_ID,EDW_PERIODICITY_ID,CUSTOM_CODE,DB_COLUMN_NAME,PERIODICITY_TYPE '||
' FROM BSC_SYS_PERIODICITIES ' ||
' WHERE PERIODICITY_ID = :2';
h_bind_vars_values.delete;
BSC_UPDATE_UTIL.Execute_Immediate(sql_stmt, h_bind_vars_values, h_num_bind_vars);
INSERT INTO BSC_SYS_PERIODICITIES (
PERIODICITY_ID,NUM_OF_PERIODS,SOURCE,NUM_OF_SUBPERIODS,PERIOD_COL_NAME,
SUBPERIOD_COL_NAME,YEARLY_FLAG,EDW_FLAG,CALENDAR_ID,EDW_PERIODICITY_ID,
CUSTOM_CODE,DB_COLUMN_NAME,PERIODICITY_TYPE)
SELECT BSC_SYS_PERIODICITY_ID_S.NEXTVAL PERIODICITY_ID,NUM_OF_PERIODS,SOURCE,NUM_OF_SUBPERIODS,
PERIOD_COL_NAME,SUBPERIOD_COL_NAME,YEARLY_FLAG,EDW_FLAG,
x_calendar_id CALENDAR_ID,EDW_PERIODICITY_ID,CUSTOM_CODE,DB_COLUMN_NAME,PERIODICITY_TYPE
FROM BSC_SYS_PERIODICITIES
WHERE PERIODICITY_ID = h_periodicity_id;
--- Update the SOURCE columns
--OPEN c_new_per FOR c_new_per_sql USING x_calendar_id;
h_count := BSC_UPDATE_UTIL.Decompose_Numeric_List(h_source,h_tmp_array,',');
-- Update the source
/*
sql_stmt := 'UPDATE BSC_SYS_PERIODICITIES SET SOURCE = :1 WHERE PERIODICITY_ID = :2';
UPDATE BSC_SYS_PERIODICITIES
SET SOURCE = h_new_source
WHERE PERIODICITY_ID = h_periodicity_id;
x_source => 'BSC_UPDATE_UTIL.CloneBSCPeriodicitybyCalendar',
x_mode => 'I');
SELECT index_name
FROM user_indexes
WHERE index_name = p_index_name;
SELECT index_name
FROM all_indexes
WHERE index_name = p_index_name AND owner = p_owner;
x_source => 'BSC_UPDATE_UTIL.Create_Unique_Index');
x_table_columns IN BSC_UPDATE_UTIL.t_array_temp_table_cols,
x_num_columns IN NUMBER
) RETURN BOOLEAN IS
h_sql VARCHAR2(32700);
SELECT column_name, data_type, data_length, data_precision
FROM all_tab_columns
WHERE table_name = p_table_name AND owner = p_owner
ORDER BY column_id;
SELECT COUNT(*)
INTO h_count
FROM all_tab_columns
WHERE table_name = x_table_name AND owner = BSC_APPS.BSC_APPS_SCHEMA;
x_source => 'BSC_UPDATE_UTIL.Create_Global_Temp_Table');
x_source => 'BSC_UPDATE_UTIL.Create_Global_Temp_Table');
SELECT COUNT(*)
INTO h_count
FROM all_indexes
WHERE index_name = h_index_name AND owner = BSC_APPS.BSC_APPS_SCHEMA;
x_source => 'BSC_UPDATE_UTIL.Create_Global_Temp_Table');
x_source => 'BSC_UPDATE_UTIL.Create_Global_Temp_Table');
x_table_columns IN BSC_UPDATE_UTIL.t_array_temp_table_cols,
x_num_columns IN NUMBER,
x_tablespace IN VARCHAR2,
x_idx_tablespace IN VARCHAR2
) RETURN BOOLEAN IS
h_sql VARCHAR2(32700);
SELECT column_name, data_type, data_length, data_precision
FROM all_tab_columns
WHERE table_name = p_table_name AND owner = p_owner
ORDER BY column_id;
SELECT COUNT(*)
INTO h_count
FROM all_tab_columns
WHERE table_name = x_table_name AND owner = BSC_APPS.BSC_APPS_SCHEMA;
x_source => 'BSC_UPDATE_UTIL.Create_Permanent_Table');
x_source => 'BSC_UPDATE_UTIL.Create_Permanent_Table');
c_index_sql VARCHAR2(2000) := 'SELECT index_name'||
' FROM user_indexes'||
' WHERE index_name = :1';
SELECT index_name
FROM user_indexes
WHERE index_name = p_index_name;
c_index_apps_sql VARCHAR2(2000) := 'SELECT index_name'||
' FROM all_indexes'||
' WHERE index_name = :1 AND owner = :2';
SELECT index_name
FROM all_indexes
WHERE index_name = p_index_name AND owner = p_owner;
x_source => 'BSC_UPDATE_UTIL.Drop_Index');
x_source => 'BSC_UPDATE_UTIL.Drop_Table');
h_sql_tbl.delete;
x_bind_vars_values IN BSC_UPDATE_UTIL.t_array_of_varchar2,
x_num_bind_vars IN NUMBER
) IS
l_sql VARCHAR2(32700);
x_bind_vars_values IN BSC_UPDATE_UTIL.t_array_of_varchar2,
x_num_bind_vars IN NUMBER
) IS
l_sql VARCHAR2(32700);
h_sql_tbl.delete;
x_bind_vars_values IN BSC_UPDATE_UTIL.t_array_of_varchar2,
x_num_bind_vars IN NUMBER
) RETURN NUMBER IS
l_sql VARCHAR2(32700);
x_bind_vars_values IN BSC_UPDATE_UTIL.t_array_of_number,
x_num_bind_vars IN NUMBER
) IS
l_sql VARCHAR2(32700);
x_bind_vars_values IN BSC_UPDATE_UTIL.t_array_of_number,
x_num_bind_vars IN NUMBER
) IS
l_sql VARCHAR2(32700);
h_sql_tbl.delete;
x_bind_vars_values IN BSC_UPDATE_UTIL.t_array_of_number,
x_num_bind_vars IN NUMBER
) RETURN NUMBER IS
l_sql VARCHAR2(32700);
/* h_sql := 'SELECT edw_flag'||
' FROM bsc_sys_calendars_b'||
' WHERE calendar_id = :1';
SELECT edw_flag
into h_edw_flag
FROM bsc_sys_calendars_b
where calendar_id = x_calendar_id;
SELECT decode(nvl(edw_calendar_type_id, 0), 1, 'PMF', 'BSC')
INTO h_calendar_source
FROM bsc_sys_calendars_b
where calendar_id = x_calendar_id;
h_sql := 'SELECT fiscal_year'||
' FROM bsc_sys_calendars_b'||
' WHERE calendar_id = :1';
SELECT fiscal_year
INTO h_fiscal_year
FROM bsc_sys_calendars_b
WHERE calendar_id = x_calendar_id;
h_sql := 'SELECT calendar_id'||
' FROM bsc_sys_periodicities'||
' WHERE periodicity_id = :1';
SELECT calendar_id
INTO h_calendar_id
FROM bsc_sys_periodicities
WHERE periodicity_id = x_periodicity_id;
h_sql := 'SELECT DECODE(C.EDW_FLAG, 0, C.NAME, C.NAME||'' (''||T.NAME||'')'')'||
' FROM BSC_SYS_CALENDARS_VL C, BSC_EDW_CALENDAR_TYPE_VL T'||
' WHERE C.CALENDAR_ID = :1 AND C.EDW_CALENDAR_TYPE_ID = T.EDW_CALENDAR_TYPE_ID (+)';
SELECT DECODE(C.EDW_FLAG, 0, C.NAME, C.NAME||' ('||T.NAME||')')
INTO h_calendar_name
FROM BSC_SYS_CALENDARS_VL C, BSC_EDW_CALENDAR_TYPE_VL T
WHERE C.CALENDAR_ID = x_calendar_id AND C.EDW_CALENDAR_TYPE_ID = T.EDW_CALENDAR_TYPE_ID (+);
h_sql := 'SELECT current_year, start_month, start_day'||
' FROM bsc_sys_calendars_b'||
' WHERE calendar_id = :1';
SELECT current_year, start_month, start_day
INTO x_start_year, x_start_month, x_start_day
FROM bsc_sys_calendars_b
WHERE calendar_id = x_calendar_id;
UPDATE bsc_sys_calendars_b
SET current_year = x_start_year
WHERE calendar_id = x_calendar_id;
x_source => 'BSC_UPDATE_UTIL.Get_Calendar_Start_Date');
h_sql := 'SELECT db_column_name'||
' FROM bsc_sys_periodicities'||
' WHERE periodicity_id = :1';
SELECT db_column_name
INTO h_db_column_name
FROM bsc_sys_periodicities
WHERE periodicity_id = x_periodicity_id;
SELECT level_table_name
INTO h_level_table_name
FROM bsc_sys_dim_levels_b
WHERE level_pk_col = x_level_pk_col;
SELECT level_view_name
INTO h_level_view_name
FROM bsc_sys_dim_levels_b
WHERE level_pk_col = x_level_pk_col;
c_indic_sql VARCHAR2(2000) := 'SELECT num_of_years, previous_years'||
' FROM bsc_kpi_periodicities'||
' WHERE indicator = :1 AND periodicity_id = :2';
SELECT num_of_years, previous_years
INTO x_num_of_years, x_previous_years
FROM bsc_kpi_periodicities
WHERE indicator = x_indicator AND periodicity_id = x_periodicity;
x_source => 'BSC_UPDATE_UTIL.Get_Indic_Range_Of_Years');
x_data_columns IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
x_data_formulas IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
x_data_proj_methods IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number,
x_data_measure_types IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number,
x_num_data_columns IN OUT NOCOPY NUMBER) RETURN BOOLEAN IS
-- BSC-BIS-DIMENSIONS Note: From thsi implemetation we can run loader when
-- there are indicators in prototype. This open a case where a measure is
-- configured in BSC_DB_TABLES_COLS but it does not exists in BSC_DB_MEASURES_COLS_VL
-- because the user could delete the measure. I am changing the query to return
-- default values in case the measure does not exists in BSC_DB_MEASURES_COLS_VL
-- SUPPORT_BSC_BIS_MEASURES: Only BSC measures exists in bsc_db_measure_cols_vl.
-- For BIS measures by design we assumed that projection method is 0 (no projection)
-- and measure type is 1 (Total)
CURSOR c_data_columns (p_table_name VARCHAR2, p_column_type VARCHAR2) IS
SELECT c.column_name, c.source_formula,
DECODE(NVL(c.source,'BSC'),'BSC',NVL(m.projection_id, 0),0),
DECODE(NVL(c.source,'BSC'),'BSC',NVL(m.measure_type, 1),1)
FROM bsc_db_tables_cols c, bsc_db_measure_cols_vl m
WHERE c.column_name = m.measure_col (+) AND
c.table_name = p_table_name AND c.column_type = p_column_type;
x_source => 'BSC_UPDATE_UTIL.Get_Information_Data_Columns');
x_key_columns IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
x_key_dim_tables IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
x_source_columns IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
x_source_dim_tables IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
x_num_key_columns IN OUT NOCOPY NUMBER) RETURN BOOLEAN IS
TYPE t_cursor IS REF CURSOR;
c_key_columns_sql VARCHAR2(2000) := 'SELECT t.column_name, d.level_view_name, t.source_column, d1.level_view_name'||
' FROM bsc_db_tables_cols t, bsc_sys_dim_levels_b d, bsc_sys_dim_levels_b d1'||
' WHERE t.table_name = :1 AND t.column_type = :2 AND'||
' t.column_name = d.level_pk_col AND'||
' t.source_column = d1.level_pk_col';
SELECT t.column_name, d.level_view_name, t.source_column, d1.level_view_name
FROM bsc_db_tables_cols t, bsc_sys_dim_levels_b d, bsc_sys_dim_levels_b d1
WHERE t.table_name = p_table_name AND t.column_type = p_column_type AND
t.column_name = d.level_pk_col AND
t.source_column = d1.level_pk_col
ORDER BY d.dim_level_id;
x_source => 'BSC_UPDATE_UTIL.Get_Information_Key_Columns');
c_init_sql VARCHAR2(2000) := 'SELECT property_value'||
' FROM bsc_sys_init'||
' WHERE property_code = :1';
h_message := BSC_UPDATE_UTIL.Get_Message('BSC_VAR_NOT_FOUND');
h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'VARIABLE', x_variable_name);
BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_WARNING')||' '||h_message,
x_source => 'BSC_UPDATE_UTIL.Get_Init_Variable_Value');
SELECT property_value
INTO x_variable_value
FROM bsc_sys_init
WHERE property_code = x_variable_name;
h_message := BSC_UPDATE_UTIL.Get_Message('BSC_VAR_NOT_FOUND');
h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'VARIABLE', x_variable_name);
BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_WARNING')||' '||h_message,
x_source => 'BSC_UPDATE_UTIL.Get_Init_Variable_Value');
x_source => 'BSC_UPDATE_UTIL.Get_Init_Variable_Value');
/* h_sql := 'SELECT source_data_type, source_file_name'||
' FROM bsc_db_tables'||
' WHERE table_name = :1';
SELECT source_data_type, TRIM(source_file_name)
INTO x_source_type, x_source_name
FROM bsc_db_tables
WHERE table_name = x_input_table ;
x_source => 'BSC_UPDATE_UTIL.Get_Input_Table_Source');
x_languages IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2
) RETURN NUMBER IS
TYPE t_cursor IS REF CURSOR;
c_languages_sql VARCHAR2(2000) := 'SELECT DISTINCT LANGUAGE_CODE'||
' FROM FND_LANGUAGES'||
' WHERE INSTALLED_FLAG IN (:1, :2)';
SELECT DISTINCT LANGUAGE_CODE
FROM FND_LANGUAGES
WHERE INSTALLED_FLAG IN (p_param1, p_param2);
x_source => 'BSC_UPDATE_UTIL.Get_Installed_Languages');
c_lookup_value_sql VARCHAR2(2000) := 'SELECT meaning'||
' FROM bsc_lookups'||
' WHERE lookup_type = :1 AND lookup_code = :2';
h_message := BSC_UPDATE_UTIL.Get_Message('BSC_MISSING_LOOKUP_VALUES');
h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'LOOKUP_TYPE', x_lookup_type);
h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'LOOKUP_CODE', x_lookup_code);
x_source => 'BSC_UPDATE_UTIL.Get_Lookup_Value');
SELECT meaning
INTO h_lookup_value
FROM bsc_lookups
WHERE lookup_type = x_lookup_type AND lookup_code = x_lookup_code;
h_message := BSC_UPDATE_UTIL.Get_Message('BSC_MISSING_LOOKUP_VALUES');
h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'LOOKUP_TYPE', x_lookup_type);
h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'LOOKUP_CODE', x_lookup_code);
x_source => 'BSC_UPDATE_UTIL.Get_Lookup_Value');
c_message_sql VARCHAR2(2000) := 'SELECT message_text'||
' FROM bsc_messages'||
' WHERE message_name = :1';
SELECT message_text
INTO h_message
FROM bsc_messages
WHERE message_name = x_message_name;
h_sql := 'SELECT MAX('||h_calendar_col_name||')'||
' FROM bsc_db_calendar'||
' WHERE year = :1 AND calendar_id = :2';
x_source => 'BSC_UPDATE_UTIL.Get_Num_Periods_Periodicity');
c_period_names_sql VARCHAR2(2000) := 'SELECT NVL(period_col_name, ''PERIOD''), subperiod_col_name'||
' FROM bsc_sys_periodicities'||
' WHERE periodicity_id = :1';
SELECT NVL(period_col_name, 'PERIOD'), subperiod_col_name
INTO x_period_col_name, x_subperiod_col_name
FROM bsc_sys_periodicities
WHERE periodicity_id = x_periodicity_cod;
x_source => 'BSC_UPDATE_UTIL.Get_Period_Cols_Names');
h_source_col_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(p_source_periodicity_id);
h_col_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(p_periodicity_id);
h_sql := 'SELECT DISTINCT '||h_col_name||
' FROM bsc_db_calendar'||
' WHERE calendar_id = :1 AND year = :2'||
' AND '||h_source_col_name||' = :3';
h_sql := 'SELECT edw_flag'||
' FROM bsc_sys_periodicities'||
' WHERE periodicity_id = :1';
SELECT edw_flag
INTO h_edw_flag
FROM bsc_sys_periodicities
WHERE periodicity_id = x_periodicity_id;
h_sql := 'SELECT periodicity_type'||
' FROM bsc_sys_periodicities'||
' WHERE periodicity_id = :1';
SELECT periodicity_type
INTO h_periodicity_type
FROM bsc_sys_periodicities
WHERE periodicity_id = x_periodicity_id;
h_sql := 'SELECT yearly_flag'||
' FROM bsc_sys_periodicities'||
' WHERE periodicity_id = :1';
SELECT yearly_flag
INTO h_yearly_flag
FROM bsc_sys_periodicities
WHERE periodicity_id = x_periodicity_id;
x_source_periodicities IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number
) RETURN NUMBER IS
h_num_source_periodicities NUMBER;
/* h_sql := 'SELECT NVL(edw_flag, 0)'||
' FROM bsc_db_tables'||
' WHERE table_name = :1';
SELECT NVL(edw_flag, 0)
INTO h_edw_flag
FROM bsc_db_tables
WHERE table_name = x_table_name;
SELECT generation_type
INTO h_table_generation_type
FROM bsc_db_tables
WHERE table_name = x_table_name;
x_source => 'BSC_UPDATE_UTIL.Get_Table_Generation_Type');
SELECT table_type
INTO h_table_type
FROM bsc_db_tables
WHERE table_name = x_table_name;
x_source => 'BSC_UPDATE_UTIL.Get_Table_Type');
c_table_periodicity_sql VARCHAR2(2000) := 'SELECT periodicity_id'||
' FROM bsc_db_tables'||
' WHERE table_name = :1';
SELECT periodicity_id
INTO h_table_periodicity
FROM bsc_db_tables
WHERE table_name = x_table_name;
x_source => 'BSC_UPDATE_UTIL.Get_Table_Periodicity');
SELECT num_of_years, previous_years
INTO x_num_of_years, x_previous_years
FROM bsc_db_tables
WHERE table_name = x_table_name;
x_source => 'BSC_UPDATE_UTIL.Get_Table_Range_Of_Years');
SELECT DISTINCT k.indicator, k.prototype_flag
FROM bsc_kpi_data_tables t, bsc_db_tables_rels r, bsc_kpis_b k
WHERE t.table_name = r.table_name AND
t.indicator = k.indicator AND
(t.table_name = x_table_name OR
(r.source_table_name = x_table_name AND r.relation_type = 1));
SELECT COUNT(indicator)
INTO h_num_rows
FROM bsc_kpis_b
WHERE indicator = x_kpi
AND prototype_flag IN (0,6,7);
SELECT COUNT(indicator)
INTO h_num_rows
FROM bsc_kpi_analysis_measures_b
WHERE kpi_measure_id = p_kpi_measure_id
AND prototype_flag = 7;
h_sql := 'SELECT NVL(target_flag, 0)'||
' FROM bsc_db_tables'||
' WHERE table_name = :1';
SELECT NVL(target_flag, 0)
INTO h_target_flag
FROM bsc_db_tables
WHERE table_name = x_table_name;
SELECT periodicity_id, custom_code, DECODE(INSTR(source, ','), 0, source, SUBSTR(source, 1, INSTR(source, ',') - 1))
FROM bsc_sys_periodicities
WHERE calendar_id = p_calendar_id;
h_calculated_pers BSC_UPDATE_UTIL.t_array_of_number;
select count(periodicity_id)
into h_count
from bsc_sys_periodicities
where calendar_id = x_calendar_id and nvl(custom_code, -1) <> 0 and
(source is null or db_column_name is null);
BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_CALTABLES_INIT_FAILED'),
x_source => 'BSC_UPDATE_UTIL.Init_Calendar_Tables');
x_source => 'BSC_UPDATE_UTIL.Init_Calendar_Tables');
BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_CALTABLES_INIT_FAILED'),
x_source => 'BSC_UPDATE_UTIL.Init_Calendar_Tables');
x_source => 'BSC_UPDATE_UTIL.Init_Calendar_Tables');
SELECT t.table_type
FROM bsc_db_tables t, bsc_db_tables_rels r
WHERE t.table_name = r.source_table_name and
r.table_name = x_table_name;
c_indicator_sql VARCHAR2(2000) := 'SELECT t.indicator'||
' FROM bsc_kpi_data_tables t, bsc_kpis_b k'||
' WHERE t.indicator = k.indicator AND'||
' table_name = :1 AND NVL(k.edw_flag, 0) = :2';
SELECT t.indicator
FROM bsc_kpi_data_tables t, bsc_kpis_b k
WHERE t.indicator = k.indicator AND
table_name = pTableName AND NVL(k.edw_flag, 0) = pEDWFlag;
SELECT PERIODICITY_ID, SOURCE
FROM BSC_SYS_PERIODICITIES
ORDER BY PERIODICITY_ID;
h_sources BSC_UPDATE_UTIL.t_array_of_number;
h_arr_new_periodicity_rels BSC_UPDATE_UTIL.t_array_periodicity_rels;
h_arr_new_periodicity_rels.delete;
x_source => 'BSC_UPDATE_UTIL.Load_Periodicity_Rels');
get_range_yr_sql VARCHAR2(2000) := 'SELECT nvl(max(num_of_years - previous_years), 1),'||
' nvl(max(previous_years), 1)'||
' FROM bsc_db_tables'||
' WHERE table_type <> :1 AND nvl(num_of_years, 0) > :2 AND'||
' periodicity_id IN (SELECT periodicity_id'||
' FROM bsc_sys_periodicities'||
' WHERE calendar_id = :3)';
SELECT nvl(max(num_of_years - previous_years), 1), nvl(max(previous_years), 1)
FROM bsc_db_tables
WHERE table_type <> p_table_type AND nvl(num_of_years, 0) > p_num_of_years AND
periodicity_id IN (
SELECT periodicity_id
FROM bsc_sys_periodicities
WHERE calendar_id = p_calendar_id);
h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
sql_stmt := 'DELETE FROM bsc_db_calendar WHERE calendar_id = :1';
DELETE FROM bsc_db_calendar WHERE calendar_id = x_calendar_id;
'INSERT INTO bsc_db_calendar (calendar_id, calendar_year, calendar_month,'||
' calendar_day, ' || h_pername_list ||
') VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13)';
h_bind_vars_values.delete;
BSC_UPDATE_UTIL.Execute_Immediate(sql_stmt, h_bind_vars_values, h_num_bind_vars);
INSERT INTO bsc_db_calendar (calendar_id, calendar_year, calendar_month, calendar_day,
year, semester, quarter, bimester, month, week52, week4, day365, day30)
VALUES (x_calendar_id, h_year, h_month, h_day, h_year_save, h_semester, h_quarterly,
h_bimonthly, h_monthly, h_weekly52, h_weekly4, h_daily365, h_daily30);
X_Source => 'BSC_UPDATE_UTIL.Populate_Bsc_Db_Calendar');
/* sql_stmt := 'DELETE FROM BSC_DB_WEEK_MAPS'||
' WHERE CALENDAR_ID = :1';
DELETE FROM BSC_DB_WEEK_MAPS
WHERE CALENDAR_ID = x_calendar_id;
/* sql_stmt := 'INSERT INTO bsc_db_week_maps (year, month, week, week52, calendar_id)'||
' SELECT year, min(month), week4, week52, calendar_id'||
' FROM bsc_db_calendar'||
' WHERE calendar_id = :1'||
' GROUP BY year, week4, week52, calendar_id';
INSERT INTO bsc_db_week_maps (year, month, week, week52, calendar_id)
SELECT year, min(month), week4, week52, calendar_id
FROM bsc_db_calendar
WHERE calendar_id = x_calendar_id
GROUP BY year, week4, week52, calendar_id;
x_source => 'BSC_UPDATE_UTIL.Populate_Bsc_Db_Week_Maps');
SELECT periodicity_id, db_column_name
FROM bsc_sys_periodicities
WHERE calendar_id = p_calendar_id AND custom_code <> p_custom_code;
h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
DELETE FROM BSC_SYS_PERIODS_TL
WHERE PERIODICITY_ID IN (
SELECT PERIODICITY_ID
FROM BSC_SYS_PERIODICITIES
WHERE CALENDAR_ID = x_calendar_id);
INSERT INTO BSC_SYS_PERIODS_TL (YEAR, PERIODICITY_ID, PERIOD_ID, MONTH,
LANGUAGE, SOURCE_LANG, NAME, SHORT_NAME)
SELECT
CA.YEAR,
CA.PERIODICITY_ID,
CA.PERIOD_ID,
1 AS MONTH,
L.LANGUAGE_CODE AS LANGUAGE,
L.LANGUAGE_CODE AS SOURCE_LANG,
CA.NAME,
NULL AS SHORT_NAME
FROM
( SELECT
C.YEAR AS YEAR,
P.PERIODICITY_ID AS PERIODICITY_ID,
C.SEMESTER AS PERIOD_ID,
C.CALENDAR_MONTH||';'||C2.CALENDAR_MONTH AS NAME
(SELECT MIN(TO_DATE(C1.CALENDAR_YEAR||'-'||C1.CALENDAR_MONTH||'-'||C1.CALENDAR_DAY,'YYYY-MM-DD'))
FROM BSC_DB_CALENDAR C1
WHERE C1.YEAR = C.YEAR AND C1.SEMESTER = C.SEMESTER AND C1.CALENDAR_ID = C.CALENDAR_ID
) AND
TO_DATE(C2.CALENDAR_YEAR||'-'||C2.CALENDAR_MONTH||'-'||C2.CALENDAR_DAY,'YYYY-MM-DD') =
(SELECT MAX(TO_DATE(C1.CALENDAR_YEAR||'-'||C1.CALENDAR_MONTH||'-'||C1.CALENDAR_DAY,'YYYY-MM-DD'))
FROM BSC_DB_CALENDAR C1
WHERE C1.YEAR = C2.YEAR AND C1.SEMESTER = C2.SEMESTER AND C1.CALENDAR_ID = C2.CALENDAR_ID
)
UNION
SELECT
C.YEAR AS YEAR,
P.PERIODICITY_ID AS PERIODICITY_ID,
C.QUARTER AS PERIOD_ID,
C.CALENDAR_MONTH||';'||C2.CALENDAR_MONTH AS NAME
(SELECT MIN(TO_DATE(C1.CALENDAR_YEAR||'-'||C1.CALENDAR_MONTH||'-'||C1.CALENDAR_DAY,'YYYY-MM-DD'))
FROM BSC_DB_CALENDAR C1
WHERE C1.YEAR = C.YEAR AND C1.QUARTER = C.QUARTER AND C1.CALENDAR_ID = C.CALENDAR_ID
) AND
TO_DATE(C2.CALENDAR_YEAR||'-'||C2.CALENDAR_MONTH||'-'||C2.CALENDAR_DAY,'YYYY-MM-DD') =
(SELECT MAX(TO_DATE(C1.CALENDAR_YEAR||'-'||C1.CALENDAR_MONTH||'-'||C1.CALENDAR_DAY,'YYYY-MM-DD'))
FROM BSC_DB_CALENDAR C1
WHERE C1.YEAR = C2.YEAR AND C1.QUARTER = C2.QUARTER AND C1.CALENDAR_ID = C2.CALENDAR_ID
)
UNION
SELECT
C.YEAR AS YEAR,
P.PERIODICITY_ID AS PERIODICITY_ID,
C.BIMESTER AS PERIOD_ID,
C.CALENDAR_MONTH||';'||C2.CALENDAR_MONTH AS NAME
(SELECT MIN(TO_DATE(C1.CALENDAR_YEAR||'-'||C1.CALENDAR_MONTH||'-'||C1.CALENDAR_DAY,'YYYY-MM-DD'))
FROM BSC_DB_CALENDAR C1
WHERE C1.YEAR = C.YEAR AND C1.BIMESTER = C.BIMESTER AND C1.CALENDAR_ID = C.CALENDAR_ID
) AND
TO_DATE(C2.CALENDAR_YEAR||'-'||C2.CALENDAR_MONTH||'-'||C2.CALENDAR_DAY,'YYYY-MM-DD') =
(SELECT MAX(TO_DATE(C1.CALENDAR_YEAR||'-'||C1.CALENDAR_MONTH||'-'||C1.CALENDAR_DAY,'YYYY-MM-DD'))
FROM BSC_DB_CALENDAR C1
WHERE C1.YEAR = C2.YEAR AND C1.BIMESTER = C2.BIMESTER AND C1.CALENDAR_ID = C2.CALENDAR_ID
)
UNION
SELECT
C.YEAR AS YEAR,
P.PERIODICITY_ID AS PERIODICITY_ID,
C.MONTH AS PERIOD_ID,
TO_CHAR(C.CALENDAR_MONTH) AS NAME
FROM
BSC_DB_CALENDAR C,
BSC_SYS_PERIODICITIES P
WHERE
P.CALENDAR_ID = x_calendar_id AND
P.PERIODICITY_TYPE = 5 AND
C.CALENDAR_ID = P.CALENDAR_ID
GROUP BY C.YEAR, C.MONTH, C.CALENDAR_MONTH, P.PERIODICITY_ID
UNION
SELECT
C.YEAR AS YEAR,
P.PERIODICITY_ID AS PERIODICITY_ID,
C.WEEK52 AS PERIOD_ID,
C.CALENDAR_MONTH||';'||C.CALENDAR_DAY AS NAME
(SELECT MAX(TO_DATE(C1.CALENDAR_YEAR||'-'||C1.CALENDAR_MONTH||'-'||C1.CALENDAR_DAY,'YYYY-MM-DD'))
FROM BSC_DB_CALENDAR C1
WHERE C1.YEAR = C.YEAR AND C1.WEEK52 = C.WEEK52 AND C1.CALENDAR_ID = C.CALENDAR_ID
)
UNION
SELECT
C.YEAR AS YEAR,
P.PERIODICITY_ID AS PERIODICITY_ID,
C.DAY365 AS PERIOD_ID,
C.CALENDAR_MONTH||';'||C.CALENDAR_DAY AS NAME
h_sql := 'INSERT INTO BSC_SYS_PERIODS_TL (
YEAR,
PERIODICITY_ID,
PERIOD_ID,
MONTH,
LANGUAGE,
SOURCE_LANG,
NAME,
SHORT_NAME)
SELECT
C.YEAR AS YEAR,
:1 AS PERIODICITY_ID,
C.'||h_db_column_name||' AS PERIOD_ID,
1 AS MONTH,
L.LANGUAGE_CODE AS LANGUAGE,
L.LANGUAGE_CODE AS SOURCE_LANG,
C.CALENDAR_MONTH||'';''||C.CALENDAR_DAY AS NAME,
(SELECT
MAX(TO_DATE(C1.CALENDAR_YEAR||''-''||C1.CALENDAR_MONTH||''-''||C1.CALENDAR_DAY,''YYYY-MM-DD''))
FROM
BSC_DB_CALENDAR C1
WHERE
C1.YEAR = C.YEAR AND
C1.'||h_db_column_name||' = C.'||h_db_column_name||' AND
C1.CALENDAR_ID = C.CALENDAR_ID
)';
h_bind_vars_values.delete;
BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
x_source => 'BSC_UPDATE_UTIL.Populate_Bsc_Sys_Periods_Tl');
IF NOT BSC_UPDATE_UTIL.Init_Calendar_Tables(x_calendar_id, x_action) THEN
RAISE e_error;
BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_CALTABLES_POP_FAILED'),
x_source => 'BSC_UPDATE_UTIL.Populate_Calendar_Tables',
x_mode => 'I');
x_source => 'BSC_UPDATE_UTIL.Populate_Calendar_Tables',
x_mode => 'I');
x_source => 'BSC_UPDATE_UTIL.Populate_Calendar_Tables',
x_mode => 'I');
x_source => 'BSC_UPDATE_UTIL.Populate_Calendar_Tables',
x_mode => 'I');
SAVEPOINT BscUpdateUtilPopCalTables;
IF NOT BSC_UPDATE_UTIL.Init_Calendar_Tables(p_calendar_id) THEN
RAISE e_error;
ROLLBACK TO BscUpdateUtilPopCalTables;
BSC_UPDATE_UTIL.Get_Message('BSC_CALTABLES_POP_FAILED')
);
ROLLBACK TO BscUpdateUtilPopCalTables;
ROLLBACK TO BscUpdateUtilPopCalTables;
ROLLBACK TO BscUpdateUtilPopCalTables;
h_sql := 'UPDATE bsc_sys_calendars_b'||
' SET fiscal_year = :1, last_updated_by = :2, last_update_date = SYSDATE'||
' WHERE calendar_id = :3';
UPDATE bsc_sys_calendars_b
SET fiscal_year = x_fiscal_year, last_updated_by = h_user_id, last_update_date = SYSDATE
WHERE calendar_id = x_calendar_id;
X_Source => 'BSC_UPDATE_UTIL.Set_Calendar_Fiscal_Year');
get_table_sql VARCHAR2(2000) := 'SELECT table_name'||
' FROM USER_TABLES'||
' WHERE table_name = :1';
SELECT table_name
FROM USER_TABLES
WHERE table_name = p_table_name;
get_table_apps_sql VARCHAR2(2000) := 'SELECT table_name'||
' FROM ALL_TABLES'||
' WHERE table_name = :1'||
' AND owner = :2';
SELECT table_name
FROM ALL_TABLES
WHERE table_name = p_table_name AND owner = p_owner;
h_sql := 'SELECT COUNT(*) FROM '||x_table_name||' WHERE ROWNUM < :1';
X_Source => 'BSC_UPDATE_UTIL.Table_Has_Any_Row');
X_Source => 'BSC_UPDATE_UTIL.Table_Has_Any_Row');
| PROCEDURE Update_AnualPeriodicity_Src
+============================================================================*/
PROCEDURE Update_AnualPeriodicity_Src (
x_calendar_id IN NUMBER,
x_periodicity_id IN NUMBER,
x_action IN NUMBER
) IS
e_error EXCEPTION;
c_new_per_sql VARCHAR2(2000) := 'SELECT SOURCE'||
' FROM BSC_SYS_PERIODICITIES'||
' WHERE CALENDAR_ID = :1 AND PERIODICITY_TYPE = :2'||
' ORDER BY PERIODICITY_ID';
SELECT SOURCE
FROM BSC_SYS_PERIODICITIES
WHERE CALENDAR_ID = p_calendar_id AND PERIODICITY_TYPE = p_periodicity_type
ORDER BY PERIODICITY_ID;
h_tmp_array BSC_UPDATE_UTIL.t_array_of_number;
h_count := BSC_UPDATE_UTIL.Decompose_Numeric_List(h_source,h_tmp_array,',');
--Delete
IF x_action = 2 THEN
IF h_new_per_id <> x_periodicity_id THEN
IF h_new_source IS NOT NULL THEN
h_new_source := h_new_source || ',' || h_new_per_id;
--Update/add
IF h_new_per_id = x_periodicity_id THEN
x_exist := TRUE;
-- Update the source
/*
h_sql := 'UPDATE BSC_SYS_PERIODICITIES SET SOURCE = :1'||
' WHERE CALENDAR_ID = :2 AND PERIODICITY_TYPE = :3';
UPDATE BSC_SYS_PERIODICITIES SET SOURCE = h_new_source
WHERE CALENDAR_ID = x_calendar_id AND PERIODICITY_TYPE = 1;
-- Update the source
/*
h_sql := 'UPDATE BSC_SYS_PERIODICITIES SET SOURCE = :1'||
' WHERE CALENDAR_ID = :2 AND PERIODICITY_TYPE = :3';
UPDATE BSC_SYS_PERIODICITIES SET SOURCE = h_new_source
WHERE CALENDAR_ID = x_calendar_id AND PERIODICITY_TYPE = 1;
x_source => 'BSC_UPDATE_UTIL.UpdAnualPeriodicitySrc',
x_mode => 'I');
END Update_AnualPeriodicity_Src;
| PROCEDURE Update_Kpi_Period_Name
+============================================================================*/
FUNCTION Update_Kpi_Period_Name(
x_indicator IN NUMBER
) RETURN BOOLEAN IS
h_edw_flag NUMBER;
h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
/* h_sql := 'SELECT NVL(edw_flag, 0)'||
' FROM bsc_kpis_b'||
' WHERE indicator = :1';
SELECT NVL(edw_flag, 0)
INTO h_edw_flag
FROM bsc_kpis_b
WHERE indicator = x_indicator;
h_sql := ' UPDATE
BSC_KPI_DEFAULTS_TL D
SET
PERIOD_NAME = (
SELECT
DECODE(P.YEARLY_FLAG,
1, K.PERIODICITY_ID||''-''||C.FISCAL_YEAR,
(SELECT
K.PERIODICITY_ID||''-''||L.NAME
FROM
BSC_KPI_PERIODICITIES KP,
BSC_SYS_PERIODS_TL L
WHERE
K.INDICATOR = KP.INDICATOR AND
K.PERIODICITY_ID = KP.PERIODICITY_ID AND
C.FISCAL_YEAR = L.YEAR AND
KP.PERIODICITY_ID = L.PERIODICITY_ID AND
KP.CURRENT_PERIOD = L.PERIOD_ID AND
D.LANGUAGE = L.LANGUAGE
))
FROM
BSC_DB_COLOR_KPI_V K,
BSC_SYS_PERIODICITIES P,
BSC_SYS_CALENDARS_B C
WHERE
K.TAB_ID = D.TAB_ID AND
K.INDICATOR = D.INDICATOR AND
K.PERIODICITY_ID = P.PERIODICITY_ID AND
P.CALENDAR_ID = C.CALENDAR_ID
)
WHERE
INDICATOR = :1';
h_bind_vars_values.delete;
h_sql := 'UPDATE
BSC_KPI_DEFAULTS_TL D
SET
PERIOD_NAME = (
SELECT
DECODE(P.YEARLY_FLAG,
1, ''EDW-''||C.FISCAL_YEAR,
(SELECT
''EDW-''||L.NAME
FROM
BSC_KPI_PERIODICITIES KP,
BSC_EDW_PERIODS_TL L
WHERE
K.INDICATOR = KP.INDICATOR AND
K.PERIODICITY_ID = KP.PERIODICITY_ID AND
C.FISCAL_YEAR = L.YEAR AND
KP.PERIODICITY_ID = L.PERIODICITY_ID AND
KP.CURRENT_PERIOD = L.PERIOD_ID AND
D.LANGUAGE = L.LANGUAGE
))
FROM
BSC_DB_COLOR_KPI_V K,
BSC_SYS_PERIODICITIES P,
BSC_SYS_CALENDARS_B C
WHERE
K.TAB_ID = D.TAB_ID AND
K.INDICATOR = D.INDICATOR AND
K.PERIODICITY_ID = P.PERIODICITY_ID AND
P.CALENDAR_ID = C.CALENDAR_ID
)
WHERE
INDICATOR = x_indicator' ;
h_bind_vars_values.delete;
X_Source => 'BSC_UPDATE_UTIL.Update_Kpi_Period_Name');
END Update_Kpi_Period_Name;
| PROCEDURE Update_Kpi_Time_Stamp
+============================================================================*/
PROCEDURE Update_Kpi_Time_Stamp(
x_indicator IN NUMBER
) IS
h_user_id NUMBER;
h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
h_sql := 'UPDATE bsc_kpis_b'||
' SET last_updated_by = :1,'||
' last_update_date = SYSDATE'||
' WHERE indicator = :2';
h_bind_vars_values.delete;
UPDATE bsc_kpis_b
SET last_updated_by = h_user_id,
last_update_date = SYSDATE
WHERE indicator = x_indicator;
END Update_Kpi_Time_Stamp;
| PROCEDURE Update_Kpi_Time_Stamp
+============================================================================*/
PROCEDURE Update_Kpi_Time_Stamp(
x_condition IN VARCHAR2
) IS
h_user_id NUMBER;
h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
h_sql := 'UPDATE bsc_kpis_b'||
' SET last_updated_by = :1, last_update_date = SYSDATE'||
' WHERE '||x_condition;
h_bind_vars_values.delete;
END Update_Kpi_Time_Stamp;
| PROCEDURE Update_Kpi_Tab_Time_Stamp
+============================================================================*/
PROCEDURE Update_Kpi_Tab_Time_Stamp(
x_indicator IN NUMBER
) IS
h_user_id NUMBER;
h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
h_sql := 'UPDATE bsc_tabs_b'||
' SET last_updated_by = :1,'||
' last_update_date = SYSDATE'||
' WHERE tab_id IN ('||
' SELECT tab_id'||
' FROM bsc_tab_indicators'||
' WHERE indicator = :2)';
h_bind_vars_values.delete;
UPDATE bsc_tabs_b
SET last_updated_by = h_user_id,
last_update_date = SYSDATE
WHERE tab_id IN (
SELECT tab_id
FROM bsc_tab_indicators
WHERE indicator = x_indicator);
END Update_Kpi_Tab_Time_Stamp;
| PROCEDURE Update_Kpi_Tab_Time_Stamp
+============================================================================*/
PROCEDURE Update_Kpi_Tab_Time_Stamp(
x_condition IN VARCHAR2
) IS
h_user_id NUMBER;
h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
h_sql := 'UPDATE bsc_tabs_b'||
' SET last_updated_by = :1, last_update_date = SYSDATE'||
' WHERE tab_id IN ('||
' SELECT tab_id'||
' FROM bsc_tab_indicators'||
' WHERE '||x_condition||')';
h_bind_vars_values.delete;
END Update_Kpi_Tab_Time_Stamp;
| PROCEDURE Update_Kpi_Table_Time_Stamp
+============================================================================*/
PROCEDURE Update_Kpi_Table_Time_Stamp(
x_table_name IN VARCHAR2
) IS
h_user_id NUMBER;
h_sql := 'UPDATE bsc_kpis_b'||
' SET last_updated_by = :1,'||
' last_update_date = SYSDATE'||
' WHERE indicator IN ('||
' SELECT indicator'||
' FROM bsc_kpi_data_tables'||
' WHERE table_name = :2)';
UPDATE bsc_kpis_b
SET last_updated_by = h_user_id,
last_update_date = SYSDATE
WHERE indicator IN (
SELECT indicator
FROM bsc_kpi_data_tables
WHERE table_name = x_table_name);
END Update_Kpi_Table_Time_Stamp;
| PROCEDURE Update_System_Time_Stamp
+============================================================================*/
PROCEDURE Update_System_Time_Stamp IS
h_user_id NUMBER;
h_sql := 'UPDATE bsc_sys_init'||
' SET last_updated_by = :1,'||
' last_update_date = SYSDATE'||
' WHERE property_code = :2';
UPDATE bsc_sys_init
SET last_updated_by = h_user_id,
last_update_date = SYSDATE
WHERE property_code = h_lock_property_code;
END Update_System_Time_Stamp;
SELECT DISTINCT c.year
FROM bsc_db_calendar c, bsc_sys_periods p
WHERE c.calendar_id = p_calendar_id AND p_periodicity_id = p.periodicity_id (+) AND
c.year = p.year (+) AND p.year IS NULL;
SELECT DISTINCT year
FROM bsc_sys_periods
WHERE periodicity_id = p_periodicity_id AND
year = (SELECT fiscal_year
FROM bsc_sys_calendars_b
WHERE calendar_id = p_calendar_id);
SELECT DECODE(INSTR(source, ','), 0, source, SUBSTR(source, 1, INSTR(source, ',') - 1))
FROM bsc_sys_periodicities
WHERE periodicity_id = p_periodicity_id;
select p2.period_id, p2.start_date - p1.end_date as issue_type
from bsc_sys_periods p1, bsc_sys_periods p2
where p1.periodicity_id = p2.periodicity_id and
p1.year = p2.year and p1.period_id = p2.period_id - 1 and
p1.periodicity_id = p_periodicity_id and p1.year = p_year and
p2.start_date - p1.end_date IN (p_in1, p_in2);
select period_id, abs(p_num1 - period_id) as distance
from bsc_sys_periods
where periodicity_id = p_periodicity_id and year = p_year and
end_date - start_date > p_num2
order by abs(p_num3 - period_id), period_id;
h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
h_sql := 'SELECT MAX(year)'||
' FROM bsc_sys_periods'||
' WHERE periodicity_id = :1';
SELECT MAX(year)
INTO h_model_year
FROM bsc_sys_periods
WHERE periodicity_id = x_periodicity_id;
h_sql := 'INSERT INTO bsc_sys_periods (
periodicity_id,
year,
period_id,
start_date,
end_date,
start_period,
end_period,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
SELECT
p.periodicity_id,
:1,
p.period_id,
add_months(p.start_date, 12*(:2 - :3)),
add_months(p.end_date, 12*(:4 - :5)),
p.start_period,
p.end_period,
p.created_by,
sysdate,
p.last_updated_by,
sysdate,
p.last_update_login
FROM
bsc_sys_periods p
WHERE
periodicity_id = :6 AND
year = :7';
h_bind_vars_values.delete;
BSC_UPDATE_UTIL.Execute_Immediate(h_sql, h_bind_vars_values, h_num_bind_vars);
INSERT INTO bsc_sys_periods (
periodicity_id,
year,
period_id,
start_date,
end_date,
start_period,
end_period,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
SELECT
p.periodicity_id,
h_year,
p.period_id,
add_months(p.start_date, 12*(h_year - h_model_year)),
add_months(p.end_date, 12*(h_year - h_model_year)),
p.start_period,
p.end_period,
p.created_by,
sysdate,
p.last_updated_by,
sysdate,
p.last_update_login
FROM
bsc_sys_periods p
WHERE
periodicity_id = x_periodicity_id AND
year = h_model_year;
h_sql := 'update bsc_sys_periods'||
' set'||
' start_date = DECODE(period_id, :1, start_date, start_date-1),'||
' end_date = end_date-1'||
' where'||
' periodicity_id = :2 and'||
' year = :3 and'||
' period_id < :4 and'||
' period_id >= :5';
h_bind_vars_values.delete;
update bsc_sys_periods
set start_date = DECODE(period_id, h_fix_overlap_period, start_date, start_date-1),
end_date = end_date-1
where periodicity_id = x_periodicity_id and
year = h_year and
period_id < h_bad_period and
period_id >= h_fix_overlap_period;
h_sql := 'update bsc_sys_periods'||
' set'||
' start_date = start_date+1,'||
' end_date = DECODE(period_id, :1, end_date, end_date+1)'||
' where'||
' periodicity_id = :2 and'||
' year = :3 and'||
' period_id <= :4 and'||
' period_id >= :5';
h_bind_vars_values.delete;
update bsc_sys_periods
set start_date = start_date+1,
end_date = DECODE(period_id, h_fix_overlap_period, end_date, end_date+1)
where periodicity_id = x_periodicity_id and
year = h_year and
period_id <= h_fix_overlap_period and
period_id >= h_bad_period;
h_sql := 'update bsc_sys_periods'||
' set start_date = start_date - 1'||
' where periodicity_id = :1 and'||
' year = :2 and period_id = :3';
h_bind_vars_values.delete;
update bsc_sys_periods
set start_date = start_date - 1
where periodicity_id = x_periodicity_id and
year = h_year and
period_id = h_bad_period;
h_sql := 'INSERT INTO bsc_sys_periods (
periodicity_id,
year,
period_id,
start_date,
end_date,
start_period,
end_period,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
SELECT
p.periodicity_id,
:1,
p.period_id,
p.start_date,
p.end_date,
p.start_period,
p.end_period,
p.created_by,
sysdate,
p.last_updated_by,
sysdate,
p.last_update_login
FROM
bsc_sys_periods p
WHERE
periodicity_id = :2 AND
year = :3';
h_bind_vars_values.delete;
INSERT INTO bsc_sys_periods (
periodicity_id,
year,
period_id,
start_date,
end_date,
start_period,
end_period,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
SELECT
p.periodicity_id,
h_year,
p.period_id,
p.start_date,
p.end_date,
p.start_period,
p.end_period,
p.created_by,
sysdate,
p.last_updated_by,
sysdate,
p.last_update_login
FROM
bsc_sys_periods p
WHERE
periodicity_id = x_periodicity_id AND
year = h_model_year;
h_sql := 'UPDATE
bsc_db_calendar d
SET '||h_db_column_name||' = (
SELECT
p.period_id
FROM
bsc_sys_periods p
WHERE
p.periodicity_id = :1 AND
p.year = d.year AND
TO_DATE(d.calendar_year||''-''||d.calendar_month||''-''||d.calendar_day, ''YYYY-MM-DD'')
BETWEEN p.start_date AND p.end_date
)
WHERE
d.calendar_id = :2';
h_bind_vars_values.delete;
h_sql := 'UPDATE
bsc_db_calendar d
SET '||h_db_column_name||' = (
SELECT
p.period_id
FROM
bsc_sys_periods p
WHERE
p.periodicity_id = :1 AND
p.year = d.year AND
d.'||h_db_source_column_name||' BETWEEN p.start_period AND p.end_period
)
WHERE
d.calendar_id = :2';
h_bind_vars_values.delete;
x_source => 'BSC_UPDATE_UTIL.Verify_Custom_Periodicity');
h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
h_sql := 'UPDATE bsc_sys_init'||
' SET property_value = :1,'||
' last_updated_by = :2,'||
' last_update_date = SYSDATE'||
' WHERE property_code = :3';
UPDATE bsc_sys_init
SET property_value = x_variable_value,
last_updated_by = h_user_id,
last_update_date = SYSDATE
WHERE property_code = x_variable_name;
h_sql := 'INSERT INTO bsc_sys_init (property_code, property_value,'||
' created_by, creation_date, last_updated_by, last_update_date)'||
' VALUES (:1, :2, :3, SYSDATE, :4 , SYSDATE)';
INSERT INTO bsc_sys_init (property_code, property_value,
created_by, creation_date, last_updated_by, last_update_date)
VALUES (x_variable_name, x_variable_value, h_user_id, SYSDATE, h_user_id , SYSDATE);
x_source => 'BSC_UPDATE_UTIL.Write_Init_Variable_Value');
x_source => 'BSC_UPDATE_UTIL.is_parallel');
IF (p_Product = BSC_UPDATE_UTIL.G_BIA) THEN
l_variable := BSC_UPDATE_UTIL.G_BIA_PATCH;
ELSIF (p_Product = BSC_UPDATE_UTIL.G_PMF) THEN
l_variable := BSC_UPDATE_UTIL.G_PMF_PATCH;
ELSIF (p_Product = BSC_UPDATE_UTIL.G_PMV) THEN
l_variable := BSC_UPDATE_UTIL.G_PMV_PATCH;
ELSIF (p_Product = BSC_UPDATE_UTIL.G_BSC) THEN
l_variable := BSC_UPDATE_UTIL.G_BSC_PATCH;
IF (BSC_UPDATE_UTIL.Get_Init_Variable_Value(
x_variable_name => l_variable
, x_variable_value => l_version)) THEN
RETURN l_version;
, x_source => 'BSC_UPDATE_UTIL.get_Product_Version'
);
IF (p_Product = BSC_UPDATE_UTIL.G_BIA) THEN
l_variable := BSC_UPDATE_UTIL.G_BIA_PATCH;
ELSIF (p_Product = BSC_UPDATE_UTIL.G_PMF) THEN
l_variable := BSC_UPDATE_UTIL.G_PMF_PATCH;
ELSIF (p_Product = BSC_UPDATE_UTIL.G_PMV) THEN
l_variable := BSC_UPDATE_UTIL.G_PMV_PATCH;
ELSIF (p_Product = BSC_UPDATE_UTIL.G_BSC) THEN
l_variable := BSC_UPDATE_UTIL.G_BSC_PATCH;
RETURN BSC_UPDATE_UTIL.Write_Init_Variable_Value
( x_variable_name => l_variable
, x_variable_value => p_Version
);
, x_source => 'BSC_UPDATE_UTIL.set_Product_Version'
);
select count(table_name)
into h_count
from (
select distinct table_name
from bsc_db_tables_rels
start with table_name in (
select distinct kd.table_name
from bsc_kpi_data_tables kd, bsc_kpi_properties k
where k.indicator = kd.indicator and
k.property_code = h_aw_impl_type_name and
k.property_value = h_aw_kpi_type and
kd.table_name is not null
)
connect by table_name = prior source_table_name
)
where table_name = x_table_name;
select property_value
from bsc_kpi_properties
where indicator = p_kpi and property_code = p_prop_code;
select count(k.indicator)
into h_count
from bsc_kpis_b k, bsc_kpi_properties p
where k.indicator = p.indicator and
k.prototype_flag in (0,6,7) and
p.property_code = h_aw_impl_type_name and
p.property_value = h_aw_kpi_type;
select count(k.calendar_id)
into h_count
from bsc_kpis_vl k, bsc_kpi_properties p
where k.indicator = p.indicator and
k.calendar_id = x_calendar_id and
k.prototype_flag in (0,6,7) and
p.property_code = h_aw_impl_type_name and
p.property_value = h_aw_impl_type;
, x_dim_props_rec OUT NOCOPY BSC_UPDATE_UTIL.t_kpi_dim_props_rec
)
IS
CURSOR c_dim_props(p_indicator NUMBER, p_kpi_measure_id NUMBER) IS
SELECT ds.dim_set_id dim_set_id,
kpi_dim.level_pk_col comp_level_pk_col
FROM bsc_db_dataset_dim_sets_v ds,
bsc_kpi_dim_levels_vl kpi_dim
WHERE ds.indicator = kpi_dim.indicator(+)
AND ds.dim_set_id = kpi_dim.dim_set_id(+)
AND kpi_dim.default_value(+) = 'C'
AND ds.kpi_measure_id = p_kpi_measure_id
AND ds.indicator = p_indicator;
x_source => 'BSC_UPDATE_UTIL.Get_Kpi_Dim_Props');
SELECT m1.operation || '(' ||
NVL(BSC_APPS.Get_Property_Value(m1.s_color_formula, 'pFormulaSource'), m1.measure_col) || ')' ||
ds.operation ||
DECODE(ds.measure_id2,
NULL, NULL,
m2.operation || '(' || NVL(BSC_APPS.Get_Property_Value(m2.s_color_formula, 'pFormulaSource'), m2.measure_col) || ')' ) measure_formula
FROM bsc_tab_indicators tab_ind,
bsc_kpi_analysis_measures_b anal_meas,
bsc_sys_datasets_b ds,
bsc_sys_measures m1,
bsc_sys_measures m2,
bsc_kpis_b obj
WHERE anal_meas.dataset_id = ds.dataset_id
AND ds.measure_id1 = m1.measure_id
AND NVL(ds.measure_id2, ds.measure_id1) = m2.measure_id
AND obj.indicator = anal_meas.indicator
AND tab_ind.indicator = obj.indicator
AND anal_meas.kpi_measure_id = p_kpi_measure_id
AND obj.indicator = p_indicator;
x_source => 'BSC_UPDATE_UTIL.get_kpi_measure_formula');
SELECT ind.measure_type source,
dts.dataset_id
FROM bsc_sys_datasets_b dts,
bis_indicators ind,
bsc_kpi_analysis_measures_b am
WHERE dts.dataset_id = am.dataset_id
AND dts.dataset_id = ind.dataset_id
AND am.indicator = p_indicator
AND am.kpi_measure_id = p_kpi_measure;
x_source => 'BSC_UPDATE_UTIL.Get_SimObj_Color_Formula');
x_source => 'BSC_UPDATE_UTIL.Get_Measure_Formula');
SELECT color_by_total
FROM bsc_kpi_measure_props
WHERE indicator = p_indicator
AND kpi_measure_id = p_kpi_measure_id;
x_source => 'BSC_UPDATE_UTIL.Get_Color_By_Total');
SELECT COUNT(default_calculation) ytd_flag
FROM bsc_kpi_measure_props
WHERE default_calculation = 2
AND kpi_measure_id = p_kpi_measure
AND indicator = p_indicator;
x_source => 'BSC_UPDATE_UTIL.get_ytd_flag');
SELECT apply_color_flag
FROM bsc_kpi_measure_props
WHERE indicator = p_indicator
AND kpi_measure_id = p_kpi_measure_id;
x_source => 'BSC_UPDATE_UTIL.Get_Apply_Color_Flag');