[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
h_sql := 'SELECT DISTINCT si.short_name,si.dataset_id'||
' FROM bis_indicators@'||g_db_link||' si'||
', bsc_sys_datasets_b@'||g_db_link||' sd'||
' WHERE sd.dataset_id = si.dataset_id AND'||
' si.created_by NOT IN (:2, :3, :4, :5)';
UPDATE bis_indicators SET dataset_id = h_dataset_id WHERE short_name = h_short_name;
h_sql := 'DELETE FROM bsc_user_tab_access';
h_sql := 'DELETE FROM bsc_user_list_access';
h_sql := 'DELETE FROM bsc_user_kpi_access';
h_sql := 'INSERT INTO bsc_user_tab_access (responsibility_id, tab_id, creation_date,'||
' created_by, last_update_date, last_updated_by, last_update_login, start_date,'||
' end_date)'||
' SELECT DECODE (responsibility_id, '||h_decode_lst||'), tab_id, MAX(creation_date),'||
' MAX(created_by), MAX(last_update_date), MAX(last_updated_by), MAX(last_update_login),'||
' MAX(start_date), MAX(end_date)'||
' FROM bsc_user_tab_access@'||g_db_link||
' WHERE ('||h_src_resps_condition||')';
h_sql := 'INSERT INTO bsc_user_list_access (responsibility_id, tab_id, dim_level_index,'||
' dim_level_value, creation_date, created_by, last_update_date, last_updated_by,'||
' last_update_login)'||
' SELECT DECODE (responsibility_id, '||h_decode_lst||'), tab_id, dim_level_index,'||
' MIN(dim_level_value), MAX(creation_date), MAX(created_by), MAX(last_update_date),'||
' MAX(last_updated_by), MAX(last_update_login)'||
' FROM bsc_user_list_access@'||g_db_link||
' WHERE ('||h_src_resps_condition||')';
h_sql := 'INSERT INTO bsc_user_kpi_access (responsibility_id, indicator, creation_date,'||
' created_by, last_update_date, last_updated_by, last_update_login, start_date,'||
' end_date)'||
' SELECT DECODE (responsibility_id, '||h_decode_lst||'), indicator, MAX(creation_date),'||
' MAX(created_by), MAX(last_update_date), MAX(last_updated_by), MAX(last_update_login),'||
' MAX(start_date), MAX(end_date)'||
' FROM bsc_user_kpi_access@'||g_db_link||
' WHERE ('||h_src_resps_condition||')';
delete from bsc_sys_init
where property_code = h_property_code;
h_lst_insert VARCHAR2(32700);
h_lst_select VARCHAR2(32700);
SELECT column_name
FROM user_tab_columns
WHERE table_name = UPPER(h_table_name);
SELECT column_name
FROM all_tab_columns
WHERE table_name = UPPER(h_table_name) AND
owner = UPPER(BSC_APPS.BSC_APPS_SCHEMA);
h_sql := 'SELECT DISTINCT language_code, installed_flag'||
' FROM fnd_languages@'||g_db_link||
' WHERE installed_flag IN (:1, :2)';
h_sql := 'SELECT DISTINCT language_code'||
' FROM fnd_languages'||
' WHERE installed_flag IN (:1, :2)';
h_lst_insert := NULL;
h_lst_select := NULL;
IF h_lst_insert IS NOT NULL THEN
h_lst_insert := h_lst_insert||', ';
h_lst_select := h_lst_select||', ';
h_lst_insert := h_lst_insert||h_column_name;
h_lst_select := h_lst_select||''''||h_trg_languages(h_j)||'''';
h_lst_select := h_lst_select||h_column_name;
IF h_lst_insert IS NOT NULL THEN
h_lst_insert := h_lst_insert||', ';
h_lst_select := h_lst_select||', ';
h_lst_insert := h_lst_insert||h_column_name;
h_lst_select := h_lst_select||''''||h_trg_languages(h_j)||'''';
h_lst_select := h_lst_select||h_column_name;
h_sql := 'INSERT INTO '||h_table_name||' ('||h_lst_insert||')'||
' SELECT '||h_lst_select||
' FROM '||h_table_name||
' WHERE LANGUAGE = :1';
h_sql := 'DELETE FROM '||h_table_name||
' WHERE LANGUAGE = :1';
h_sql := 'SELECT level_table_name'||
' FROM bsc_sys_dim_levels_b'||
' WHERE nvl(source, ''BSC'') = ''BSC''';
h_lst_insert := NULL;
h_lst_select := NULL;
IF h_lst_insert IS NOT NULL THEN
h_lst_insert := h_lst_insert||', ';
h_lst_select := h_lst_select||', ';
h_lst_insert := h_lst_insert||h_column_name;
h_lst_select := h_lst_select||''''||h_trg_languages(h_j)||'''';
h_lst_select := h_lst_select||h_column_name;
IF h_lst_insert IS NOT NULL THEN
h_lst_insert := h_lst_insert||', ';
h_lst_select := h_lst_select||', ';
h_lst_insert := h_lst_insert||h_column_name;
h_lst_select := h_lst_select||''''||h_trg_languages(h_j)||'''';
h_lst_select := h_lst_select||h_column_name;
h_sql := 'INSERT INTO '||h_table_name||' ('||h_lst_insert||')'||
' SELECT '||h_lst_select||
' FROM '||h_table_name||
' WHERE LANGUAGE = :1';
h_sql := 'DELETE FROM '||h_table_name||
' WHERE LANGUAGE = :1';
h_sql := 'DELETE FROM bsc_sys_dataset_calc'||
' WHERE dataset_id IN ('||
' SELECT dataset_id'||
' FROM bsc_sys_datasets_b'||
' WHERE measure_id1 IN ('||
' SELECT measure_id'||
' FROM bsc_sys_measures'||
' WHERE '||h_condition||
' )'||
' )';
h_sql := 'DELETE FROM bsc_sys_datasets_tl'||
' WHERE dataset_id IN ('||
' SELECT dataset_id'||
' FROM bsc_sys_datasets_b'||
' WHERE measure_id1 IN ('||
' SELECT measure_id'||
' FROM bsc_sys_measures'||
' WHERE '||h_condition||
' )'||
' )';
h_sql := 'DELETE FROM bsc_sys_datasets_b'||
' WHERE measure_id1 IN ('||
' SELECT measure_id'||
' FROM bsc_sys_measures'||
' WHERE '||h_condition||
' )';
h_sql := 'DELETE FROM bsc_db_measure_cols_tl'||
' WHERE measure_col IN ('||
' SELECT measure_col'||
' FROM bsc_sys_measures'||
' WHERE '||h_condition||
' )';
h_sql := 'DELETE FROM bsc_sys_measures'||
' WHERE '||h_condition;
h_sql := 'DELETE FROM bsc_sys_dim_levels_by_group'||
' WHERE dim_group_id IN ('||
' SELECT dim_group_id'||
' FROM bsc_sys_dim_groups_tl'||
' WHERE '||h_condition||
' )';
h_sql := 'DELETE FROM bsc_sys_dim_groups_tl'||
' WHERE '||h_condition;
h_sql := 'DELETE FROM bsc_sys_dim_levels_by_group'||
' WHERE dim_level_id IN ('||
' SELECT dim_level_id'||
' FROM bsc_sys_dim_levels_b'||
' WHERE '||h_condition||
' )';
h_sql := 'DELETE FROM bsc_sys_dim_level_cols'||
' WHERE dim_level_id IN ('||
' SELECT dim_level_id'||
' FROM bsc_sys_dim_levels_b'||
' WHERE '||h_condition||
' )';
h_sql := 'DELETE FROM bsc_sys_dim_level_rels'||
' WHERE dim_level_id IN ('||
' SELECT dim_level_id'||
' FROM bsc_sys_dim_levels_b'||
' WHERE '||h_condition||
' )';
h_sql := 'DELETE FROM bsc_sys_dim_level_rels'||
' WHERE parent_dim_level_id IN ('||
' SELECT dim_level_id'||
' FROM bsc_sys_dim_levels_b'||
' WHERE '||h_condition||
' )';
h_sql := 'DELETE FROM bsc_sys_dim_levels_tl'||
' WHERE dim_level_id IN ('||
' SELECT dim_level_id'||
' FROM bsc_sys_dim_levels_b'||
' WHERE '||h_condition||
' )';
h_sql := 'DELETE FROM bsc_sys_dim_levels_b'||
' WHERE '||h_condition;
h_sql := 'SELECT column_id, column_name, data_type, data_length,'||
' data_precision, data_scale, nullable'||
' FROM user_tab_columns@'||g_db_link||
' WHERE table_name = :1'||
' ORDER BY column_id';
h_sql := 'SELECT column_id, column_name, data_type, data_length,'||
' data_precision, data_scale, nullable'||
' FROM all_tab_columns@'||g_db_link||
' WHERE table_name = :1 AND'||
' owner = :2'||
' ORDER BY column_id';
h_sql := 'SELECT initial_extent, next_extent, max_extents, pct_increase'||
' FROM user_tables@'||g_db_link||
' WHERE table_name = :1';
h_sql := 'select count(*)'||
' from all_tab_partitions@'||g_db_link||
' where table_owner = :1 and table_name = :2';
h_sql := 'select max(initial_extent), max(next_extent), max(max_extent), max(pct_increase)'||
' from all_tab_partitions@'||g_db_link||
' where table_owner = :1 and table_name = :2';
h_sql := 'SELECT initial_extent, next_extent, max_extents, pct_increase'||
' FROM all_tables@'||g_db_link||
' WHERE table_name = :1 AND'||
' owner = :2';
h_sql := 'SELECT initial_extent, next_extent, max_extents, pct_increase'||
' FROM all_tables@'||g_db_link||
' WHERE table_name = :1 AND'||
' owner = :2';
h_sql := 'SELECT column_id, column_name'||
' FROM user_tab_columns@'||g_db_link||
' WHERE table_name = :1'||
' ORDER BY column_id';
h_sql := 'SELECT text'||
' FROM user_views@'||g_db_link||
' WHERE view_name = :1';
h_key_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
h_key_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
h_source_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
h_source_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
h_sql := 'SELECT level_table_name, level_view_name, nvl(source, ''BSC'')'||
' FROM bsc_sys_dim_levels_b';
h_sql := 'SELECT table_name'||
' FROM bsc_db_tables'||
' WHERE table_type = 2';
h_sql := 'SELECT DISTINCT relation_col'||
' FROM bsc_sys_dim_level_rels'||
' WHERE relation_type = 2';
h_sql := 'SELECT level_view_name'||
' FROM bsc_sys_filters_views';
IF NOT BSC_UPDATE_UTIL.Get_Information_Key_Columns(h_table,
h_key_columns,
h_key_dim_tables,
h_source_columns,
h_source_dim_tables,
h_num_key_columns) THEN
RAISE e_unexpected_error;
IF BSC_UPDATE_UTIL.Get_Table_Generation_Type(h_table) <> -1 THEN
IF Is_Input_Table(h_table) THEN
h_tbs_type := BSC_APPS.input_table_tbs_type;
h_sql := 'SELECT DISTINCT projection_data'||
' FROM bsc_kpi_data_tables'||
' WHERE projection_data IS NOT NULL';
h_sql := 'SELECT level_table_name'||
' FROM bsc_sys_dim_levels_b';
h_sql := 'SELECT table_name'||
' FROM bsc_db_tables'||
' WHERE table_type = 2';
h_sql := 'SELECT DISTINCT relation_col'||
' FROM bsc_sys_dim_level_rels'||
' WHERE relation_type = 2';
IF BSC_UPDATE_UTIL.Get_Table_Generation_Type(h_table) <> -1 THEN
IF Is_Input_Table(h_table) THEN
h_tbs_type := BSC_APPS.input_index_tbs_type;
h_sql := 'SELECT DISTINCT projection_data'||
' FROM bsc_kpi_data_tables'||
' WHERE projection_data IS NOT NULL';
h_sql := 'SELECT level_table_name'||
' FROM bsc_sys_dim_levels_b'||
' WHERE NVL(source, ''BSC'') = ''BSC''';
h_sql := 'SELECT index_name, index_type, uniqueness, initial_extent, next_extent, max_extents, pct_increase, partitioned'||
' FROM user_indexes@'||g_db_link||
' WHERE table_name = :1';
h_sql := 'SELECT index_name, index_type, uniqueness, initial_extent, next_extent, max_extents, pct_increase, partitioned'||
' FROM all_indexes@'||g_db_link||
' WHERE table_name = :1 AND'||
' owner = :2';
h_sql := 'SELECT column_position, column_name'||
' FROM user_ind_columns@'||g_db_link||
' WHERE index_name = :1'||
' ORDER BY column_position';
h_sql := 'SELECT column_position, column_name'||
' FROM all_ind_columns@'||g_db_link||
' WHERE index_name = :1 AND'||
' index_owner = :2'||
' ORDER BY column_position';
select count(partition_name)
into h_count
from all_tab_partitions
where table_owner = g_src_bsc_schema and table_name = x_table;
| FUNCTION Delete_Metadata_Tables
+============================================================================*/
FUNCTION Delete_Metadata_Tables RETURN BOOLEAN IS
h_i NUMBER;
BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_MIG_DELETE_METADATA'), BSC_APPS.OUTPUT_FILE);
x_source => 'BSC_MIGRATION.Delete_Metadata_Tables');
END Delete_Metadata_Tables;
SELECT table_name
FROM bsc_db_tables;
SELECT level_table_name, level_view_name, nvl(edw_flag,0)
FROM bsc_sys_dim_levels_b;
SELECT DISTINCT relation_col
FROM bsc_sys_dim_level_rels
WHERE relation_type = 2;
SELECT level_view_name
FROM bsc_sys_filters_views;
select bsc_migration.get_rowid_table_name(table_name)
from bsc_db_tables
where table_type = 0;
select bsc_migration.get_proj_table_name(table_name)
from bsc_db_tables
where table_type = 1;
h_sql := 'SELECT DISTINCT MV_NAME'||
' FROM BSC_KPI_DATA_TABLES'||
' WHERE MV_NAME IS NOT NULL';
h_sql := 'SELECT DISTINCT BSC_BIA_WRAPPER.Get_Sum_Table_MV_Name(SOURCE_TABLE_NAME) MV_NAME'||
' FROM BSC_DB_TABLES_RELS'||
' WHERE TABLE_NAME IN ('||
' SELECT TABLE_NAME'||
' FROM BSC_KPI_DATA_TABLES'||
' WHERE TABLE_NAME IS NOT NULL'||
' ) AND'||
' RELATION_TYPE = 1';
h_sql := 'SELECT DISTINCT PROJECTION_DATA'||
' FROM BSC_KPI_DATA_TABLES'||
' WHERE PROJECTION_DATA IS NOT NULL';
SELECT count(*)
INTO h_count
FROM bsc_message_logs
WHERE type = 0
AND UPPER(source) = 'BSC_MIGRATION.MIGRATE_SYSTEM'
AND last_update_login = USERENV('SESSIONID');
ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
SELECT column_name
FROM user_tab_columns
WHERE table_name = UPPER(x_table);
SELECT column_name
FROM all_tab_columns
WHERE table_name = UPPER(x_table) AND
owner = UPPER(BSC_APPS.BSC_APPS_SCHEMA);
h_sql := 'SELECT input_table_name'||
' FROM bsc_db_loader_control'||
' WHERE process_id = :1'||
' ORDER BY input_table_name';
h_sql := 'SELECT DISTINCT bsc_d.short_name'||
' FROM bsc_sys_dim_levels_b@'||g_db_link||' bsc_do,'||
' bsc_sys_dim_groups_vl@'||g_db_link||' bsc_d,'||
' bsc_sys_dim_levels_by_group@'||g_db_link||' bsc_dlg,'||
' bis_dimensions pmf_d'||
' WHERE bsc_do.dim_level_id = bsc_dlg.dim_level_id AND'||
' bsc_dlg.dim_group_id = bsc_d.dim_group_id AND'||
' bsc_do.source = :1 AND'||
' bsc_d.short_name = pmf_d.short_name(+) AND'||
' pmf_d.short_name IS NULL';
h_sql := 'SELECT DISTINCT k.indicator'||
' FROM bsc_kpi_dim_groups@'||g_db_link||' k,'||
' bsc_sys_dim_levels_b@'||g_db_link||' bsc_do,'||
' bsc_sys_dim_groups_vl@'||g_db_link||' bsc_d,'||
' bsc_sys_dim_levels_by_group@'||g_db_link||' bsc_dlg,'||
' bis_dimensions pmf_d'||
' WHERE k.dim_group_id = bsc_d.dim_group_id AND'||
' bsc_do.dim_level_id = bsc_dlg.dim_level_id AND'||
' bsc_dlg.dim_group_id = bsc_d.dim_group_id AND'||
' bsc_do.source = :1 AND'||
' bsc_d.short_name = pmf_d.short_name(+) AND'||
' pmf_d.short_name IS NULL';
h_sql := 'SELECT DISTINCT bsc_dl.short_name'||
' FROM bsc_sys_dim_levels_b@'||g_db_link||' bsc_dl, bis_levels pmf_dl'||
' WHERE bsc_dl.source = :1 AND'||
' bsc_dl.short_name = pmf_dl.short_name(+) AND'||
' pmf_dl.short_name IS NULL';
h_sql := 'SELECT DISTINCT k.indicator'||
' FROM bsc_kpi_dim_levels_b@'||g_db_link||' k,'||
' bsc_sys_dim_levels_b@'||g_db_link||' bsc_dl,'||
' bis_levels pmf_dl'||
' WHERE k.level_table_name = bsc_dl.level_table_name AND'||
' bsc_dl.source = :1 AND'||
' bsc_dl.short_name = pmf_dl.short_name(+) AND'||
' pmf_dl.short_name IS NULL';
h_sql := 'SELECT DISTINCT bsc_m.short_name'||
' FROM bsc_sys_measures@'||g_db_link||' bsc_m, bis_indicators pmf_m'||
' WHERE bsc_m.source = :1 AND'||
' bsc_m.short_name = pmf_m.short_name(+) AND'||
' pmf_m.short_name IS NULL';
h_sql := 'SELECT DISTINCT k.indicator'||
' FROM bsc_kpi_analysis_measures_b@'||g_db_link||' k,'||
' bsc_sys_datasets_b@'||g_db_link||' d,'||
' bsc_sys_measures@'||g_db_link||' m,'||
' bis_indicators i'||
' WHERE k.dataset_id = d.dataset_id AND'||
' d.measure_id1 = m.measure_id AND'||
' m.source = :1 AND'||
' m.short_name = i.short_name (+) AND'||
' i.short_name IS NULL';
h_sql := 'SELECT DISTINCT d.short_name'||
' FROM bis_indicator_dimensions@'||g_db_link||' id,'||
' bis_indicators@'||g_db_link||' i,'||
' bis_dimensions@'||g_db_link||' d,'||
' bis_dimensions td'||
' WHERE i.indicator_id = id.indicator_id AND'||
' d.dimension_id = id.dimension_id AND'||
' td.short_name = d.short_name(+) AND '||
' td.short_name IS NULL';
h_sql := 'SELECT DISTINCT k.indicator'||
' FROM bsc_kpi_analysis_measures_b@'||g_db_link||' k,'||
' bsc_sys_datasets_b@'||g_db_link||' d,'||
' bsc_sys_measures@'||g_db_link||' m,'||
' bis_indicator_dimensions@'||g_db_link||' id,'||
' bis_indicators@'||g_db_link||' i,'||
' bis_dimensions@'||g_db_link||' sd,'||
' bis_dimensions td'||
' WHERE k.dataset_id = d.dataset_id AND'||
' d.measure_id1 = m.measure_id AND'||
' m.source = :1 AND'||
' m.short_name = i.short_name AND'||
' i.indicator_id = id.indicator_id AND'||
' sd.dimension_id = id.dimension_id AND'||
' td.short_name = sd.short_name(+) AND'||
' td.short_name IS NULL';
' SELECT LEVEL_TABLE_NAME'||
' FROM BSC_SYS_DIM_LEVELS_B@'||g_db_link||')';
' SELECT RELATION_COL'||
' FROM BSC_SYS_DIM_LEVEL_RELS@'||g_db_link||
' WHERE RELATION_TYPE = 2)';
' SELECT CALENDAR_ID'||
' FROM BSC_SYS_CALENDARS_B@'||g_db_link||
' WHERE NVL(EDW_FLAG,0) = 0)';
' SELECT DIM_LEVEL_ID'||
' FROM BSC_SYS_DIM_LEVELS_B@'||g_db_link||
' WHERE NVL(EDW_FLAG,0) = 0)';
' SELECT DATASET_ID'||
' FROM BSC_SYS_DATASETS_B@'||g_db_link||
' WHERE NVL(EDW_FLAG,0) = 0)';
' SELECT DATASET_ID'||
' FROM BSC_SYS_DATASETS_B@'||g_db_link||
' WHERE NVL(EDW_FLAG,0) = 0)';
' SELECT DIM_LEVEL_ID'||
' FROM BSC_SYS_DIM_LEVELS_B@'||g_db_link||
' WHERE NVL(EDW_FLAG,0) = 0)';
' SELECT DIM_LEVEL_ID'||
' FROM BSC_SYS_DIM_LEVELS_B@'||g_db_link||
' WHERE NVL(EDW_FLAG,0) = 0)';
' SELECT DIM_LEVEL_ID'||
' FROM BSC_SYS_DIM_LEVELS_B@'||g_db_link||
' WHERE NVL(EDW_FLAG,0) = 0)';
' SELECT DIM_LEVEL_ID'||
' FROM BSC_SYS_DIM_LEVELS_B@'||g_db_link||
' WHERE NVL(EDW_FLAG,0) = 0)';
' SELECT DIM_LEVEL_ID'||
' FROM BSC_SYS_DIM_LEVELS_B@'||g_db_link||
' WHERE NVL(EDW_FLAG,0) = 0)';
' SELECT DIM_LEVEL_ID'||
' FROM BSC_SYS_DIM_LEVELS_B@'||g_db_link||
' WHERE NVL(EDW_FLAG,0) = 0)';
' SELECT DIM_LEVEL_ID'||
' FROM BSC_SYS_DIM_LEVELS_B@'||g_db_link||
' WHERE NVL(EDW_FLAG,0) = 0)';
' SELECT DIM_LEVEL_ID'||
' FROM BSC_SYS_DIM_LEVELS_B@'||g_db_link||
' WHERE NVL(EDW_FLAG,0) = 0)';
' SELECT PERIODICITY_ID'||
' FROM BSC_SYS_PERIODICITIES@'||g_db_link||
' WHERE NVL(EDW_FLAG, 0) = 0)';
' SELECT COLOR_RANGE_ID ' ||
' FROM BSC_COLOR_TYPE_PROPS) ';
h_sql := 'SELECT rt.tab_id, rk.indicator'||
' FROM bsc_responsibility_vl@'||g_db_link||' r,'||
' bsc_user_tab_access@'||g_db_link||' rt,'||
' bsc_user_kpi_access@'||g_db_link||' rk,'||
' bsc_tab_indicators@'||g_db_link||' tk,'||
' bsc_kpis_b@'||g_db_link||' k'||
' WHERE r.responsibility_id = rt.responsibility_id AND'||
' r.responsibility_id = rk.responsibility_id AND'||
' rt.tab_id = tk.tab_id AND'||
' rk.indicator = tk.indicator AND'||
' tk.indicator = k.indicator AND'||
' NVL(k.edw_flag, 0) = 0 AND '||h_filter_condition;
h_sql := 'SELECT rt.tab_id'||
' FROM bsc_responsibility_vl@'||g_db_link||' r,'||
' bsc_user_tab_access@'||g_db_link||' rt'||
' WHERE r.responsibility_id = rt.responsibility_id AND '||h_filter_condition;
h_sql := 'SELECT DISTINCT table_name'||
' FROM bsc_kpi_data_tables_v@'||g_db_link||
' WHERE ('||h_kpi_cond||') AND'||
' table_name IS NOT NULL';
Insert_Origin_Tables(h_tables, h_num_tables);
SELECT table_type
FROM bsc_db_tables
WHERE table_name = x_table_name;
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;
| PROCEDURE Insert_Origin_Tables
+============================================================================*/
PROCEDURE Insert_Origin_Tables(
x_tables IN t_array_of_varchar2,
x_num_tables IN NUMBER
) IS
h_sql VARCHAR2(32700);
h_sql := 'SELECT DISTINCT source_table_name'||
' FROM bsc_db_tables_rels@'||g_db_link||
' WHERE '||h_table_cond;
Insert_Origin_Tables(h_tables, h_num_tables);
END Insert_Origin_Tables;
h_sql := 'SELECT level_table_name'||
' FROM bsc_sys_dim_levels_b'||
' WHERE NVL(source, ''BSC'') = ''BSC''';
h_sql := 'INSERT /*+ append parallel('||h_table||') */ INTO '||h_table||
' SELECT /*+ parallel('||h_table||') */ * FROM '||h_table||'@'||g_db_link;
h_sql := 'SELECT DISTINCT relation_col'||
' FROM bsc_sys_dim_level_rels'||
' WHERE relation_type = 2';
h_sql := 'INSERT /*+ append parallel('||h_table||') */ INTO '||h_table||
' SELECT /* parallel('||h_table||') */ * FROM '||h_table||'@'||g_db_link;
h_sql := 'INSERT /*+ append parallel('||h_table||') */ INTO '||h_table||
' SELECT /*+ parallel('||h_table||') */ * FROM '||h_table||'@'||g_db_link;
h_sql := 'INSERT /*+ append parallel('||h_table||') */ INTO '||h_table||
' SELECT /*+ parallel('||h_table||') */ * FROM '||h_table||'@'||g_db_link;
IF BSC_UPDATE_UTIL.Get_Table_Generation_Type(h_table) <> -1 THEN
-- ENH_B_TABLES_PERF: for base table we need to check if it has a projection table.
-- in this case the base table is in the new architecture (partitions and projection table)
-- We need to fix the batch column in th esame time we are inserting rows inthe target
-- since the number of partititons may be different from the source.
IF Is_Base_Table(h_table) THEN
h_proj_table_name := Get_Proj_Table_Name(h_table);
h_sql := 'INSERT /*+ append parallel('||h_table||') */ INTO '||h_table||
' SELECT /*+ parallel('||h_table||') */ * FROM '||h_table||'@'||g_db_link;
h_sql := 'INSERT /*+ append parallel('||h_table||') */ INTO '||h_table||
' SELECT /*+ parallel('||h_table||') */ * FROM '||h_table||'@'||g_db_link;
h_sql := 'SELECT DISTINCT projection_data'||
' FROM bsc_kpi_data_tables'||
' WHERE projection_data IS NOT NULL';
h_sql := 'INSERT /*+ append parallel('||h_table||') */ INTO '||h_table||
' SELECT /*+ parallel('||h_table||') */ * FROM '||h_table||'@'||g_db_link;
h_sql := 'INSERT /*+ append parallel('||g_metadata_tables(h_i).table_name||') */'||
' INTO '||g_metadata_tables(h_i).table_name||' ('||h_lst_table_columns||')'||
' SELECT /*+ parallel('||g_metadata_tables(h_i).table_name||') */ '||h_lst_table_columns||
' FROM '||g_metadata_tables(h_i).table_name||'@'||g_db_link;
h_sql := 'DELETE FROM bsc_kpi_cause_effect_rels'||
' WHERE NOT ('||h_cause_cond||') OR'||
' NOT ('||h_effect_cond||')';
h_sql := 'SELECT BSC_SYS_IMAGE_ID_S.NEXTVAL@'||g_db_link||' FROM DUAL';
SELECT BSC_SYS_IMAGE_ID_S.NEXTVAL INTO h_currval FROM DUAL;
SELECT BSC_SYS_IMAGE_ID_S.NEXTVAL INTO h_currval FROM DUAL;
h_sql := 'SELECT BSC_SYS_CALENDAR_ID_S.NEXTVAL@'||g_db_link||' FROM DUAL';
SELECT BSC_SYS_CALENDAR_ID_S.NEXTVAL INTO h_currval FROM DUAL;
SELECT BSC_SYS_CALENDAR_ID_S.NEXTVAL INTO h_currval FROM DUAL;
h_sql := 'SELECT BSC_SYS_PERIODICITY_ID_S.NEXTVAL@'||g_db_link||' FROM DUAL';
SELECT BSC_SYS_PERIODICITY_ID_S.NEXTVAL INTO h_currval FROM DUAL;
SELECT BSC_SYS_PERIODICITY_ID_S.NEXTVAL INTO h_currval FROM DUAL;
h_sql := 'UPDATE bsc_tab_view_labels_b
SET link_id = -1
WHERE label_type = 2';
h_sql := 'UPDATE bsc_kpi_comments c
SET created_by = NVL((SELECT u.user_id
FROM fnd_user u, fnd_user@'||g_db_link||' s
WHERE u.user_name = s.user_name AND
c.created_by = s.user_id), :1),
last_updated_by = NVL((SELECT u.user_id
FROM fnd_user u, fnd_user@'||g_db_link||' s
WHERE u.user_name = s.user_name AND
c.last_updated_by = s.user_id), :2)';
h_sql := 'UPDATE bsc_kpi_comments c
SET created_by = :1,
last_updated_by = :2';
h_sql := 'SELECT BSC_KPI_COMMENTS_ID_S.NEXTVAL@'||g_db_link||' FROM DUAL';
SELECT BSC_KPI_COMMENTS_ID_S.NEXTVAL INTO h_currval FROM DUAL;
SELECT BSC_KPI_COMMENTS_ID_S.NEXTVAL INTO h_currval FROM DUAL;
INSERT INTO BSC_TAB_IND_GROUPS_TL (TAB_ID,CSF_ID,IND_GROUP_ID,LANGUAGE,SOURCE_LANG,NAME,HELP)
SELECT -1,CSF_ID,IND_GROUP_ID,LANGUAGE,MIN(SOURCE_LANG),MIN(NAME),MIN(HELP)
FROM BSC_TAB_IND_GROUPS_TL
GROUP BY -1,CSF_ID,IND_GROUP_ID,LANGUAGE;
INSERT INTO BSC_TAB_IND_GROUPS_B (TAB_ID,CSF_ID,IND_GROUP_ID,GROUP_TYPE,NAME_POSITION,
NAME_JUSTIFICATION,LEFT_POSITION,TOP_POSITION,WIDTH,HEIGHT,SHORT_NAME)
SELECT -1,CSF_ID,IND_GROUP_ID,MIN(GROUP_TYPE),MIN(NAME_POSITION),
MIN(NAME_JUSTIFICATION),MIN(LEFT_POSITION),MIN(TOP_POSITION),MIN(WIDTH),MIN(HEIGHT),SHORT_NAME
FROM BSC_TAB_IND_GROUPS_B
GROUP BY -1,CSF_ID,IND_GROUP_ID,SHORT_NAME;
h_sql := 'UPDATE bsc_tabs_b c
SET owner_id = NVL((SELECT u.user_id
FROM fnd_user u, fnd_user@'||g_db_link||' s
WHERE u.user_name = s.user_name AND
c.owner_id = s.user_id), :1)';
h_sql := 'UPDATE bsc_tabs_b
SET owner_id = :1';
UPDATE bsc_tabs_b
SET parent_tab_id = NULL
WHERE
parent_tab_id NOT IN (
SELECT tab_id
FROM bsc_tabs_b
);
h_sql := 'SELECT BSC_INTERNAL_COLUMN_S.NEXTVAL@'||g_db_link||' FROM DUAL';
SELECT BSC_INTERNAL_COLUMN_S.NEXTVAL INTO h_currval FROM DUAL;
SELECT BSC_INTERNAL_COLUMN_S.NEXTVAL INTO h_currval FROM DUAL;
h_sql := 'SELECT BSC_SYS_DIM_LEVEL_ID_S.NEXTVAL@'||g_db_link||' FROM DUAL';
SELECT BSC_SYS_DIM_LEVEL_ID_S.NEXTVAL INTO h_currval FROM DUAL;
SELECT BSC_SYS_DIM_LEVEL_ID_S.NEXTVAL INTO h_currval FROM DUAL;
h_sql := 'SELECT BSC_SYS_DIM_GROUP_ID_S.NEXTVAL@'||g_db_link||' FROM DUAL';
SELECT BSC_SYS_DIM_GROUP_ID_S.NEXTVAL INTO h_currval FROM DUAL;
SELECT BSC_SYS_DIM_GROUP_ID_S.NEXTVAL INTO h_currval FROM DUAL;
h_sql := 'SELECT BSC_SYS_DATASET_ID_S.NEXTVAL@'||g_db_link||' FROM DUAL';
SELECT BSC_SYS_DATASET_ID_S.NEXTVAL INTO h_currval FROM DUAL;
SELECT BSC_SYS_DATASET_ID_S.NEXTVAL INTO h_currval FROM DUAL;
h_sql := 'SELECT BSC_SYS_MEASURE_ID_S.NEXTVAL@'||g_db_link||' FROM DUAL';
SELECT BSC_SYS_MEASURE_ID_S.NEXTVAL INTO h_currval FROM DUAL;
SELECT BSC_SYS_MEASURE_ID_S.NEXTVAL INTO h_currval FROM DUAL;
h_sql := 'SELECT BSC_DB_MEASURE_GROUPS_S.NEXTVAL@'||g_db_link||' FROM DUAL';
SELECT BSC_DB_MEASURE_GROUPS_S.NEXTVAL INTO h_currval FROM DUAL;
SELECT BSC_DB_MEASURE_GROUPS_S.NEXTVAL INTO h_currval FROM DUAL;
h_sql := 'SELECT BSC_INDICATOR_ID_S.NEXTVAL@'||g_db_link||' FROM DUAL';
SELECT BSC_INDICATOR_ID_S.NEXTVAL INTO h_currval FROM DUAL;
SELECT BSC_INDICATOR_ID_S.NEXTVAL INTO h_currval FROM DUAL;
h_sql := 'SELECT BSC_KPI_MEASURE_S.NEXTVAL@'||g_db_link||' FROM DUAL';
SELECT BSC_KPI_MEASURE_S.NEXTVAL INTO h_currval FROM DUAL;
SELECT BSC_KPI_MEASURE_S.NEXTVAL INTO h_currval FROM DUAL;
h_sql := 'SELECT BSC_COLOR_RANGE_ID_S.NEXTVAL@'||g_db_link||' FROM DUAL';
SELECT BSC_COLOR_RANGE_ID_S.NEXTVAL INTO h_currval FROM DUAL;
SELECT BSC_COLOR_RANGE_ID_S.NEXTVAL INTO h_currval FROM DUAL;
SELECT property_value FROM
bsc_sys_init WHERE property_code = 'TARGET_STATUS';
SELECT COUNT(1) FROM
ALL_TABLES WHERE TABLE_NAME = pTableName
AND OWNER = pSchema;
SELECT
CMT.INDICATOR,
CMT.YEAR,
CMT.PERIODICITY_ID,
CMT.PERIOD_ID,
CMT.TREND_FLAG,
CMT.COMMENT_TEXT,
CMT.COMMENT_TYPE,
CMT.CREATED_BY,
CMT.CREATION_DATE,
CMT.LAST_UPDATED_BY,
CMT.LAST_UPDATE_DATE,
CMT.LAST_UPDATE_LOGIN,
CMT.COMMENT_SUBJECT,
CMT.COMMENT_ID,
''N'' PRESERVE_FLAG,
1000000 CONC_REQUEST_ID,
sysdate MIGRATION_DATE
FROM
BSC_KPI_COMMENTS CMT,
BSC_KPIS_B TGT
WHERE
CMT.INDICATOR = TGT.INDICATOR';
PROCEDURE update_comment_backup_table IS
l_stmt VARCHAR2(4000) ;
l_stmt := 'UPDATE '||c_comments_bak||' set preserve_flag = :1 where indicator in
(SELECT TGT.INDICATOR FROM
BSC_KPIS_B TGT,
BSC_KPIS_B@'||g_db_link||' SRC
WHERE
TGT.INDICATOR = SRC.INDICATOR
AND TGT.CREATION_DATE = SRC.CREATION_DATE)';
l_stmt := 'UPDATE '||c_comments_bak||' set CONC_REQUEST_ID = :1, migration_date = :2';
x_source => 'BSC_MIGRATION.update_comment_backup_table');
update_comment_backup_table ;
l_stmt := 'delete bsc_kpi_comments ';
l_stmt := 'delete bsc_kpi_comments '; -- preserve target or source fully. no mix and match.
l_stmt := 'INSERT INTO BSC_KPI_COMMENTS
(INDICATOR,
YEAR,
PERIODICITY_ID,
PERIOD_ID,
TREND_FLAG,
COMMENT_TEXT,
COMMENT_TYPE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
COMMENT_SUBJECT,
COMMENT_ID)
SELECT
CMT.INDICATOR,
CMT.YEAR,
CMT.PERIODICITY_ID,
CMT.PERIOD_ID,
CMT.TREND_FLAG,
CMT.COMMENT_TEXT,
CMT.COMMENT_TYPE,
CMT.CREATED_BY,
CMT.CREATION_DATE,
CMT.LAST_UPDATED_BY,
CMT.LAST_UPDATE_DATE,
CMT.LAST_UPDATE_LOGIN,
CMT.COMMENT_SUBJECT,
CMT.COMMENT_ID
FROM
BSC_KPI_COMMENTS_BAK CMT,
BSC_KPIS_B KPIS
WHERE PRESERVE_FLAG = :1
AND CMT.indicator = kpis.indicator';
h_base_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
SELECT property_value
FROM bsc_kpi_properties
WHERE indicator = pIndicator
AND property_code = pPropertyCode;
h_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
h_sql := 'SELECT object_name FROM user_objects@'||g_db_link||
' WHERE object_name = :1';
h_sql := 'SELECT property_value'||
' FROM bsc_sys_init@'||g_db_link||
' WHERE property_code = :1';
h_sql := 'SELECT table_owner FROM user_synonyms@'||g_db_link||
' WHERE table_name = :1';
UPDATE bsc_sys_init
SET property_value = 'UNAPPLIED',
last_updated_by = BSC_APPS.fnd_global_user_id,
last_update_date = SYSDATE
WHERE property_code = 'TARGET_STATUS';
IF NOT Delete_Metadata_Tables THEN
RAISE e_unexpected_error;
h_sql := 'SELECT user_id FROM fnd_user WHERE user_name = ''SYSADMIN''';
DELETE BSC_SYS_INIT where PROPERTY_CODE <> 'PATCH_NUMBER';
h_sql := 'INSERT /*+ append parallel(BSC_SYS_INIT)*/'||
' INTO BSC_SYS_INIT ('||h_lst_columns||')'||
' SELECT /*+ parallel(BSC_SYS_INIT)*/ '||h_lst_columns||
' FROM BSC_SYS_INIT'||'@'||g_db_link ||
' WHERE PROPERTY_CODE <> ''PATCH_NUMBER'' AND PROPERTY_CODE <>''EDW_INSTALLED'' ';
UPDATE bsc_sys_init
SET property_value = 'UNAPPLIED',
last_updated_by = BSC_APPS.fnd_global_user_id,
last_update_date = SYSDATE
WHERE property_code = 'TARGET_STATUS';
h_sql := 'SELECT short_name'||
' FROM bsc_kpis_b'||
' WHERE indicator = :1';
IF NOT Update_BSC_Dimensions_In_PMF THEN
RAISE e_unexpected_error;
h_sql := 'SELECT prototype_flag, BSC_DBGEN_UTILS.get_Objective_Type(short_name)'||
' FROM bsc_kpis_b@'||g_db_link||
' WHERE indicator = :1';
IF BSC_UPDATE_UTIL.Get_Kpi_Impl_Type(g_mig_kpis(h_i)) = 2 THEN
--AW implementation
h_message := 'Creating AW cubes: '||g_mig_kpis(h_i);
BSC_APPS.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
h_kpi_list.delete;
BSC_APPS.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
IF NOT BSC_UPDATE_DIM.Create_Dbi_Dim_Temp_Tables THEN
RAISE e_unexpected_error;
IF NOT BSC_UPDATE_DIM.Create_AW_Dim_Temp_Tables THEN
RAISE e_unexpected_error;
IF NOT BSC_UPDATE_DIM.Create_Dbi_Dim_Tables(h_error_msg) THEN
RAISE e_creating_dbi_dim_tables;
h_sql := 'select distinct short_name'||
' from bsc_sys_dim_levels_b'||
' where source = :1';
IF NOT BSC_UPDATE_DIM.Refresh_Dbi_Dimension_Table(h_dbi_short_name) THEN
-- fix bug#4682494: if for any reason the dbi dimension cannot be refreshed
-- then we write this to the log file an continue
g_warnings := TRUE;
BSC_UPDATE_LOG.OUTPUT);
IF BSC_UPDATE_UTIL.Exists_AW_Kpi THEN
h_dim_level_list.delete;
h_sql := 'select distinct level_table_name'||
' from bsc_kpi_dim_levels_vl kd, bsc_kpi_properties kp, bsc_kpis_b k'||
' where kd.indicator = kp.indicator and'||
' kd.indicator = k.indicator and'||
' k.prototype_flag IN (0,6,7) and'||
' kp.property_code = :1 and kp.property_value = :2 and'||
' kd.status = :3 and kd.level_source = :4';
IF NOT BSC_UPDATE.Load_Dims_Into_AW(h_dim_tables, h_num_dim_tables) THEN
RAISE e_unexpected_error;
h_sql := 'select calendar_id'||
' from bsc_sys_calendars_b';
h_sql := 'select distinct calendar_id'||
' from bsc_kpis_b k, bsc_kpi_properties kp'||
' where k.indicator = kp.indicator and'||
' k.prototype_flag IN (0,6,7) and'||
' kp.property_code = :1 and kp.property_value = :2';
h_sql := 'SELECT table_name FROM bsc_db_tables_rels'||
' WHERE source_table_name IN ('||
' SELECT table_name FROM bsc_db_tables'||
' WHERE table_type = :1)';
IF NOT BSC_UPDATE_UTIL.Is_Table_For_AW_Kpi(h_table) THEN
-- The base table is for a MV indicator
h_num_base_tables := h_num_base_tables + 1;
IF NOT BSC_UPDATE.Refresh_System_MVs_Mig(h_base_tables, h_num_base_tables) THEN
RAISE e_unexpected_error;
h_sql := 'select k.indicator'||
' from bsc_kpis_b k, bsc_kpi_properties kp'||
' where k.indicator = kp.indicator and'||
' k.prototype_flag IN (0,6,7) and'||
' kp.property_code = :1 and kp.property_value = :2';
h_kpi_list.delete;
UPDATE bsc_sys_init
SET property_value = 'APPLIED',
last_updated_by = BSC_APPS.fnd_global_user_id,
last_update_date = SYSDATE
WHERE property_code = 'TARGET_STATUS';
BSC_MESSAGE.Add(x_message => 'Error in BSC_UPDATE_DIM.Create_Dbi_Dim_Tables: '||h_error_msg,
x_source => 'BSC_MIGRATION.Migrate_System',
x_mode => 'I');
SELECT DISTINCT spmf_dl.level_id
FROM bsc_sys_dim_levels_b sbsc_dl,
bis_levels spmf_dl
WHERE sbsc_dl.short_name = spmf_dl.short_name AND
spmf_dl.created_by NOT IN (p1, p2,p3,p4);
BIS_DIMENSION_LEVEL_PUB.Delete_Dimension_Level (
p_commit => FND_API.G_FALSE
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,p_Dimension_Level_Rec => h_bis_dim_level_rec
,x_return_status => h_return_status
,x_error_Tbl => h_error_tbl
);
SELECT DISTINCT spmf_d.dimension_id
FROM bsc_sys_dim_groups_vl sbsc_d,
bis_dimensions spmf_d
WHERE sbsc_d.short_name = spmf_d.short_name AND
spmf_d.created_by NOT IN (p1, p2,p3,p4);
BIS_DIMENSION_PUB.Delete_Dimension(
p_commit => FND_API.G_FALSE
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,p_Dimension_Rec => h_bis_dimension_rec
,x_return_status => h_return_status
,x_error_Tbl => h_error_tbl
);
UPDATE bis_dimensions
SET dim_grp_id = NULL;
SELECT DISTINCT si.indicator_id
FROM bis_indicators si,
bsc_sys_datasets_b sd
WHERE sd.dataset_id = si.dataset_id AND
si.created_by NOT IN (p1, p2, p3,p4);
BIS_MEASURE_PUB.Delete_Measure(
p_api_version => 1.0
,p_commit => FND_API.G_FALSE
,p_Measure_Rec => h_measure_rec
,x_return_status => h_return_status
,x_error_Tbl => h_error_tbl);
UPDATE bis_indicators
SET dataset_id = NULL;
| FUNCTION Update_BSC_Dimensions_In_PMF
+============================================================================*/
FUNCTION Update_BSC_Dimensions_In_PMF RETURN BOOLEAN IS
TYPE t_cursor IS REF CURSOR;
h_sql := 'UPDATE bis_dimensions_tl tdtl'||
' SET description = ('||
' SELECT sdtl.description'||
' FROM bis_dimensions@'||g_db_link||' sd,'||
' bis_dimensions_tl@'||g_db_link||' sdtl,'||
' bis_dimensions td'||
' WHERE tdtl.dimension_id = td.dimension_id AND'||
' td.short_name = sd.short_name AND'||
' sd.dimension_id = sdtl.dimension_id AND'||
' tdtl.language = sdtl.language'||
' )'||
' WHERE tdtl.dimension_id IN ('||
' SELECT DISTINCT pmf_d.dimension_id'||
' FROM bis_levels pmf_do, bsc_sys_dim_levels_vl bsc_do,'||
' bis_dimensions pmf_d, bsc_sys_dim_groups_tl bsc_d,'||
' bsc_sys_dim_levels_by_group bsc_dlg'||
' WHERE pmf_do.short_name = bsc_do.short_name AND'||
' bsc_do.source = :1 AND bsc_do.dim_level_id = bsc_dlg.dim_level_id AND'||
' bsc_dlg.dim_group_id = bsc_d.dim_group_id AND'||
' bsc_d.short_name = pmf_d.short_name'||
' )';
SELECT language_code INTO h_base_language
FROM fnd_languages
WHERE installed_flag = 'B';
h_sql := 'SELECT DISTINCT language_code'||
' FROM fnd_languages'||
' WHERE installed_flag IN (:1, :2) AND'||
' language_code NOT IN ('||
' SELECT language_code'||
' FROM fnd_languages@'||g_db_link||
' WHERE installed_flag IN (:3, :4)'||
' )';
h_sql := 'UPDATE bis_dimensions_tl d1'||
' SET description = ('||
' SELECT description'||
' FROM bis_dimensions_tl d2'||
' WHERE d2.dimension_id = d1.dimension_id AND'||
' d2.language = :1'||
' ),'||
' source_lang = :2'||
' WHERE d1.language = :3 AND'||
' d1.dimension_id IN ('||
' SELECT DISTINCT pmf_d.dimension_id'||
' FROM bis_levels pmf_do, bsc_sys_dim_levels_vl bsc_do,'||
' bis_dimensions pmf_d, bsc_sys_dim_groups_tl bsc_d,'||
' bsc_sys_dim_levels_by_group bsc_dlg'||
' WHERE pmf_do.short_name = bsc_do.short_name AND'||
' bsc_do.source = :4 AND bsc_do.dim_level_id = bsc_dlg.dim_level_id AND'||
' bsc_dlg.dim_group_id = bsc_d.dim_group_id AND'||
' bsc_d.short_name = pmf_d.short_name'||
' )';
x_source => 'BSC_MIGRATION.Update_BSC_Dimensions_In_PMF');
END Update_BSC_Dimensions_In_PMF;
h_sql := 'SELECT tab_id'||
' FROM bsc_user_tab_access@'||g_db_link||
' WHERE tab_id = :1 AND ('||h_condition||')';
h_sql := 'SELECT indicator'||
' FROM bsc_user_kpi_access@'||g_db_link||
' WHERE indicator = :1 AND ('||h_condition||')';
h_sql := 'SELECT responsibility_id'||
' FROM bsc_responsibility_vl@'||g_db_link||
' WHERE responsibility_id = :1';
h_sql := 'SELECT responsibility_id'||
' FROM bsc_responsibility_vl'||
' WHERE responsibility_id = :1';
h_sql := 'SELECT property_value'||
' FROM bsc_sys_init'||
' WHERE UPPER(property_code) = :1';
h_sql := 'SELECT property_value'||
' FROM bsc_sys_init@'||g_db_link||
' WHERE UPPER(property_code) = :1';
h_sql := 'SELECT property_value'||
' FROM bsc_sys_init@'||g_db_link||
' WHERE UPPER(property_code) = :1';
h_sql := 'SELECT property_value'||
' FROM bsc_sys_init'||
' WHERE UPPER(property_code) = :1';
h_sql := 'SELECT level_table_name, name'||
' FROM bsc_sys_dim_levels_vl@'||g_db_link||
' WHERE NVL(source, ''BSC'') = ''BSC'' AND '||
' UPPER(level_table_name) NOT IN ('||
' SELECT table_name'||
' FROM all_tables@'||g_db_link||
' WHERE owner = :1)';
h_sql := 'SELECT DISTINCT r.relation_col, c.name, p.name'||
' FROM bsc_sys_dim_level_rels@'||g_db_link||' r,'||
' bsc_sys_dim_levels_vl@'||g_db_link||' c, '||
' bsc_sys_dim_levels_vl@'||g_db_link||' p'||
' WHERE r.relation_type = 2 AND r.dim_level_id = c.dim_level_id (+)'||
' AND r.parent_dim_level_id = p.dim_level_id (+)'||
' AND UPPER(r.relation_col) NOT IN ('||
' SELECT table_name'||
' FROM all_tables@'||g_db_link||
' WHERE owner = :1)';
h_sql := 'select distinct k.indicator, k.name'||
' from bsc_kpi_data_tables@'||g_db_link||' t, bsc_kpis_vl@'||g_db_link||' k'||
' where t.indicator = k.indicator and'||
' (t.table_name = :1 or'||
' t.table_name in ('||
' select distinct table_name'||
' from bsc_db_tables_rels@'||g_db_link||
' start with source_table_name = :2'||
' connect by source_table_name = prior table_name))';
h_sql := 'select count(*)'||
' from all_tables@'||g_db_link||
' where owner = :1 and table_name = :2';
h_sql := 'SELECT generation_type'||
' FROM bsc_db_tables@'||g_db_link||
' WHERE table_name = :1';
BSC_UPDATE_LOG.OUTPUT);
h_sql := 'SELECT indicator, name'||
' FROM bsc_kpis_vl@'||g_db_link||
' WHERE '||h_condition;
BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.OUTPUT);
h_sql := 'SELECT itl.name, i.short_name, f.application_name'||
' FROM bis_indicators@'||g_db_link||' i,'||
' bis_indicators_tl@'||g_db_link||' itl,'||
' bis_application_measures@'||g_db_link||' am,'||
' fnd_application_vl@'||g_db_link||' f'||
' WHERE i.indicator_id = itl.indicator_id AND'||
' itl.language = USERENV(''LANG'') AND'||
' i.indicator_id = am.indicator_id AND'||
' am.application_id = f.application_id(+) AND '||h_condition;
BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.OUTPUT);
h_sql := 'SELECT DISTINCT dtl.name, d.short_name, f.application_name'||
' FROM bis_dimensions@'||g_db_link||' d,'||
' bis_dimensions_tl@'||g_db_link||' dtl,'||
' fnd_application_vl@'||g_db_link||' f'||
' WHERE d.dimension_id = dtl.dimension_id AND'||
' dtl.language = USERENV(''LANG'') AND'||
' d.application_id = f.application_id (+) AND '||h_condition;
BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.OUTPUT);
h_sql := 'SELECT DISTINCT ltl.name, l.short_name, f.application_name'||
' FROM bis_levels@'||g_db_link||' l,'||
' bis_levels_tl@'||g_db_link||' ltl,'||
' fnd_application_vl@'||g_db_link||' f'||
' WHERE l.level_id = ltl.level_id AND'||
' ltl.language = USERENV(''LANG'') AND'||
' l.application_id = f.application_id (+) AND '||h_condition;
BSC_UPDATE_LOG.OUTPUT);
h_sql := 'SELECT DISTINCT sbsc_d.short_name'||
' FROM bsc_sys_dim_groups_vl@'||g_db_link||' sbsc_d,'||
' bis_dimensions@'||g_db_link||' spmf_d'||
' WHERE sbsc_d.short_name = spmf_d.short_name AND'||
' spmf_d.created_by NOT IN (:1, :2, :3, :4)';
select count(short_name)
into h_count
from bis_dimensions
where short_name = h_dimension_short_name;
BIS_DIMENSION_PUB.Update_Dimension(
p_api_version => 1.0
, p_commit => FND_API.G_FALSE
, p_validation_level => FND_API.G_VALID_LEVEL_FULL
, p_Dimension_Rec => h_dimension_rec
, x_return_status => h_return_status
, x_error_Tbl => h_error_tbl
);
h_sql := 'SELECT DISTINCT sbsc_dl.short_name, sbsc_dl.source'||
' FROM bsc_sys_dim_levels_b@'||g_db_link||' sbsc_dl,'||
' bis_levels@'||g_db_link||' spmf_dl'||
' WHERE sbsc_dl.short_name = spmf_dl.short_name AND'||
' spmf_dl.created_by NOT IN (:1, :2, :3, :4)';
h_sql := 'select count(*) from '||h_level_values_view_name||' where rownum < :1';
select count(short_name)
into h_count
from bis_levels
where short_name = h_dimension_level_short_name;
BIS_DIMENSION_LEVEL_PUB.Update_Dimension_Level(
p_api_version => 1.0
, p_commit => FND_API.G_FALSE
, p_validation_level => FND_API.G_VALID_LEVEL_FULL
, p_Dimension_Level_Rec => h_dimension_level_rec
, x_return_status => h_return_status
, x_error_Tbl => h_error_tbl
);
h_sql := 'SELECT DISTINCT si.short_name'||
' FROM bis_indicators@'||g_db_link||' si,'||
' bsc_sys_datasets_b@'||g_db_link||' sd'||
' WHERE sd.dataset_id= si.dataset_id AND'||
' si.created_by NOT IN (:2, :3, :4, :5)';
select count(short_name)
into h_count
from bis_indicators
where short_name = h_measure_short_name;
BIS_MEASURE_PUB.Update_Measure(
p_api_version => 1.0
, p_commit => FND_API.G_FALSE
, p_Measure_Rec => h_measure_rec
, p_owner => BIS_UTILITIES_PUB.G_CUSTOM_OWNER
, x_return_status => h_return_status
, x_error_Tbl => h_error_tbl
);
h_list.delete;
h_list.delete;
h_list.delete;
h_key_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
h_key_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
h_source_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
h_source_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
h_dbi_dim_data BSC_UPDATE_DIM.t_dbi_dim_data;
SELECT column_name
FROM all_tab_columns
WHERE table_name = UPPER(p_table) AND
owner = UPPER(BSC_APPS.BSC_APPS_SCHEMA);
h_lst_select VARCHAR2(32000);
h_lst_insert VARCHAR2(32000);
IF NOT BSC_UPDATE_UTIL.Get_Information_Key_Columns(x_base_table,
h_key_columns,
h_key_dim_tables,
h_source_columns,
h_source_dim_tables,
h_num_key_columns) THEN
RAISE e_unexpected_error;
SELECT source
INTO h_level_source
FROM bsc_sys_dim_levels_b
WHERE level_view_name = h_key_dim_tables(h_i);
h_lst_insert := null;
h_lst_select := null;
IF h_lst_insert IS NOT NULL THEN
h_lst_insert := h_lst_insert||', ';
h_lst_select := h_lst_select||', ';
h_lst_insert := h_lst_insert||h_column;
h_lst_select := h_lst_select||'dbms_utility.get_hash_value(';
h_lst_select := h_lst_select||'||''.''||';
h_lst_select := h_lst_select||'b.'||h_key_columns(h_j);
h_lst_select := h_lst_select||h_key_dim_tables(h_j)||'.USER_CODE';
h_lst_select := h_lst_select||', 0, '||h_max_partitions||')';
h_lst_select := h_lst_select||'0';
h_lst_select := h_lst_select||'b.'||h_column;
h_sql := 'insert /*+ append parallel('||h_table||') */ into '||h_table||
' ('||h_lst_insert||')'||
' select /*+ parallel(b)';
h_sql := h_sql||' */ '||h_lst_select||
' from '||h_table||'@'||g_db_link||' b';
h_sql := 'INSERT /*+ append parallel('||x_base_table||') */ INTO '||x_base_table||
' SELECT /*+ parallel('||x_base_table||') */ * FROM '||x_base_table||'@'||g_db_link;
h_sql := 'INSERT /*+ append parallel('||x_proj_table||') */ INTO '||x_proj_table||
' SELECT /*+ parallel('||x_proj_table||') */ * FROM '||x_proj_table||'@'||g_db_link;
e_delete_ak_region_error EXCEPTION;
h_sql := 'SELECT region_application_id, database_object_name, name,'||
' description, num_rows_display, region_style, region_object_type,'||
' isform_flag, attribute_category, attribute1, attribute2, attribute3,'||
' attribute4, attribute5, attribute6, attribute7, attribute8, attribute9,'||
' attribute10, attribute11, attribute12, attribute13, attribute14,'||
' attribute15, created_by'||
' FROM ak_regions_vl@'||g_db_link||
' WHERE region_code = :1';
select count(region_code) into h_count
from ak_regions
where region_application_id = h_region_application_id and
region_code = p_region_code;
BIS_AK_REGION_PUB.DELETE_REGION_AND_REGION_ITEMS(
p_REGION_CODE => p_region_code
,p_REGION_APPLICATION_ID => h_region_application_id
,x_return_status => h_return_status
,x_msg_count => h_msg_count
,x_msg_data => h_msg_data
);
RAISE e_delete_ak_region_error;
BIS_AK_REGION_PUB.INSERT_REGION_ROW (
X_ROWID => h_rowid,
X_USER_ID => fnd_global.user_id,
X_REGION_APPLICATION_ID => h_region_application_id,
X_REGION_CODE => p_region_code,
X_DATABASE_OBJECT_NAME => h_database_object_name,
X_NAME => h_name,
X_DESCRIPTION => h_description,
X_NUM_ROWS_DISPLAY => h_num_rows_display,
X_REGION_STYLE => h_region_style,
X_REGION_OBJECT_TYPE => h_region_object_type,
X_ISFORM_FLAG => h_isform_flag,
X_ATTRIBUTE_CATEGORY => h_attribute_category,
X_ATTRIBUTE1 => h_attribute1,
X_ATTRIBUTE2 => h_attribute2,
X_ATTRIBUTE3 => h_attribute3,
X_ATTRIBUTE4 => h_attribute4,
X_ATTRIBUTE5 => h_attribute5,
X_ATTRIBUTE6 => h_attribute6,
X_ATTRIBUTE7 => h_attribute7,
X_ATTRIBUTE8 => h_attribute8,
X_ATTRIBUTE9 => h_attribute9,
X_ATTRIBUTE10 => h_attribute10,
X_ATTRIBUTE11 => h_attribute11,
X_ATTRIBUTE12 => h_attribute12,
X_ATTRIBUTE13 => h_attribute13,
X_ATTRIBUTE14 => h_attribute14,
X_ATTRIBUTE15 => h_attribute15
);
h_sql := 'select attribute16, attribute17, attribute18, attribute19, attribute20, attribute21,'||
' attribute22, attribute23, attribute24, attribute25, attribute26, attribute27,'||
' attribute28, attribute29, attribute30, attribute31, attribute32, attribute33,'||
' attribute34, attribute35, attribute36, attribute37, attribute38, attribute39,'||
' attribute40'||
' from bis_ak_region_extension@'||g_db_link||
' where region_code = :1 and region_application_id = :2';
h_sql := 'select attribute_application_id, attribute_code, display_sequence, node_display_flag,'||
' node_query_flag, attribute_label_long, attribute_label_length, display_value_length,'||
' item_style, required_flag, nested_region_code, nested_region_application_id,'||
' attribute_category, attribute1, attribute2, attribute3, attribute4, attribute5,'||
' attribute6, attribute7, attribute8, attribute9, attribute10, attribute11, attribute12,'||
' attribute13, attribute14, attribute15, url, order_sequence, order_direction'||
' from ak_region_items_vl@'||g_db_link||
' where region_code = :1 and region_application_id = :2';
BIS_AK_REGION_PUB.INSERT_REGION_ITEM_ROW (
X_ROWID => h_rowid,
X_USER_ID => fnd_global.user_id,
X_REGION_APPLICATION_ID => h_region_application_id,
X_REGION_CODE => p_region_code,
X_ATTRIBUTE_APPLICATION_ID => h_attribute_application_id,
X_ATTRIBUTE_CODE => h_attribute_code,
X_DISPLAY_SEQUENCE => h_display_sequence,
X_NODE_DISPLAY_FLAG => h_node_display_flag,
X_NODE_QUERY_FLAG => h_node_query_flag,
X_ATTRIBUTE_LABEL_LONG => h_attribute_label_long,
X_ATTRIBUTE_LABEL_LENGTH => h_attribute_label_length,
X_DISPLAY_VALUE_LENGTH => h_display_value_length,
X_ITEM_STYLE => h_item_style,
X_REQUIRED_FLAG => h_required_flag,
X_NESTED_REGION_CODE => h_nested_region_code,
X_NESTED_REGION_APPL_ID => h_nested_region_application_id,
X_ATTRIBUTE_CATEGORY => h_attribute_category,
X_ATTRIBUTE1 => h_attribute1,
X_ATTRIBUTE2 => h_attribute2,
X_ATTRIBUTE3 => h_attribute3,
X_ATTRIBUTE4 => h_attribute4,
X_ATTRIBUTE5 => h_attribute5,
X_ATTRIBUTE6 => h_attribute6,
X_ATTRIBUTE7 => h_attribute7,
X_ATTRIBUTE8 => h_attribute8,
X_ATTRIBUTE9 => h_attribute9,
X_ATTRIBUTE10 => h_attribute10,
X_ATTRIBUTE11 => h_attribute11,
X_ATTRIBUTE12 => h_attribute12,
X_ATTRIBUTE13 => h_attribute13,
X_ATTRIBUTE14 => h_attribute14,
X_ATTRIBUTE15 => h_attribute15,
X_URL => h_url,
X_ORDER_SEQUENCE => h_order_sequence,
X_ORDER_DIRECTION => h_order_direction
);
h_sql := 'SELECT attribute16, attribute17, attribute18, attribute19, attribute20, attribute21,'||
' attribute22, attribute23, attribute24, attribute25, attribute26, attribute27,'||
' attribute28, attribute29, attribute30, attribute31, attribute32, attribute33,'||
' attribute34, attribute35, attribute36, attribute37, attribute38, attribute39,'||
' attribute40'||
' from bis_ak_region_item_extension@'||g_db_link||
' where region_code = :1 and region_application_id = :2 and'||
' attribute_code = :3 and attribute_application_id = :4';
WHEN e_delete_ak_region_error THEN
rollback;
SELECT function_id
FROM fnd_form_functions
WHERE function_name = p_function_name;
h_sql := 'SELECT web_host_name, web_agent_name, web_html_call, web_encrypt_parameters,'||
' web_secured, web_icon, object_id, region_application_id, region_code,'||
' application_id, form_id, parameters, type, user_function_name,'||
' description, maintenance_mode_support, context_dependence, jrad_ref_path,'||
' created_by'||
' FROM fnd_form_functions_vl@'||g_db_link||
' WHERE function_name = :1';
FND_FORM_FUNCTIONS_PKG.DELETE_ROW(X_FUNCTION_ID => h_function_id);
SELECT fnd_form_functions_s.nextval INTO h_function_id FROM DUAL;
FND_FORM_FUNCTIONS_PKG.INSERT_ROW (
X_ROWID => h_rowid,
X_FUNCTION_ID => h_function_id,
X_WEB_HOST_NAME => h_web_host_name,
X_WEB_AGENT_NAME => h_web_agent_name,
X_WEB_HTML_CALL => h_web_html_call,
X_WEB_ENCRYPT_PARAMETERS => h_web_encrypt_parameters,
X_WEB_SECURED => h_web_secured,
X_WEB_ICON => h_web_icon,
X_OBJECT_ID => h_object_id,
X_REGION_APPLICATION_ID => h_region_application_id,
X_REGION_CODE => h_region_code,
X_FUNCTION_NAME => p_function_name,
X_APPLICATION_ID => h_application_id,
X_FORM_ID => h_form_id,
X_PARAMETERS => h_parameters,
X_TYPE => h_type,
X_USER_FUNCTION_NAME => h_user_function_name,
X_DESCRIPTION => h_description,
X_CREATION_DATE => sysdate,
X_CREATED_BY => fnd_global.user_id,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => fnd_global.user_id,
X_LAST_UPDATE_LOGIN => fnd_global.user_id,
X_MAINTENANCE_MODE_SUPPORT => h_maintenance_mode_support,
X_CONTEXT_DEPENDENCE => h_context_dependence,
X_JRAD_REF_PATH => h_jrad_ref_path
);
h_sql := 'select s.user_id'||
' from fnd_user@'||g_db_link||' s, fnd_user t'||
' where t.user_name = s.user_name and t.user_id = :1';
h_sql := 'select user_id'||
' from fnd_user@'||g_db_link||
' where user_name = :1';
PROCEDURE Update_AK_Item_Props (
p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_region_code IN VARCHAR2
,p_region_application_id IN NUMBER
,p_Attribute_Code IN AK_REGION_ITEMS.ATTRIBUTE_CODE%TYPE
,p_Attribute_Application_Id IN AK_REGION_ITEMS.ATTRIBUTE_APPLICATION_ID%TYPE
,p_Attribute2 IN AK_REGION_ITEMS.ATTRIBUTE2%TYPE
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_Ak_Region_Item_Rec BIS_AK_REGION_PUB.Bis_Region_Item_Rec_Type;
BIS_AK_REGION_PUB.Update_Region_Item_Row (
p_region_code => p_region_code
,p_region_application_id => p_region_application_id
,p_Region_Item_Rec => l_Ak_Region_Item_Rec
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
x_msg_data := x_msg_data||' -> BSC_MIGRATION.Update_AK_Item_Props';
x_msg_data := SQLERRM||' at BSC_MIGRATION.Update_AK_Item_Props ';
x_msg_data := x_msg_data||' -> BSC_MIGRATION.Update_AK_Item_Props ';
x_msg_data := SQLERRM||' at BSC_MIGRATION.Update_AK_Item_Props ';
END Update_AK_Item_Props;
SELECT
property_value
FROM
bsc_kpi_properties
WHERE
indicator = p_Trg_indicator AND
property_code = 'S_NODE_ID';
l_sql := 'SELECT ak_item.region_code,ak_item.region_application_id,ak_item.attribute_code,ak_item.attribute_application_id, i.comparison_source,i.enable_link, ';
SELECT
short_name,actual_data_source
INTO
l_New_Attribute2,l_Actual_Data_Source
FROM
bis_indicators
WHERE
dataset_id = p_New_DataSet_Map(i);
UPDATE
bis_indicators
SET
actual_data_source = p_short_name || '.' || l_Item_Att_Code,
function_name = p_short_name,
actual_data_source_type = 'AK',
comparison_source = DECODE(l_Comparison_Source,NULL,NULL,p_short_name || '.' || l_Item_Att_Code || '_B'),
enable_link = l_Enable_Link
WHERE
short_name = l_New_Attribute2;
Update_AK_Item_Props (
p_commit => FND_API.G_FALSE
,p_region_code => p_short_name
,p_region_application_id => l_Region_Att_Id
,p_Attribute_Code => l_Item_Att_Code
,p_Attribute_Application_Id => l_Item_Att_App_Id
,p_Attribute2 => l_New_Attribute2
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
UPDATE
ak_region_items_tl ak_item
SET
attribute_label_long = (SELECT
d.name
FROM
bsc_sys_datasets_tl d
WHERE
d.dataset_id = p_New_DataSet_Map(i) AND
d.language = ak_item.language)
WHERE
ak_item.region_code = p_short_name AND
ak_item.region_application_id = l_Region_Att_Id AND
ak_item.attribute_code = l_Item_Att_Code AND
ak_item.attribute_application_id = l_Item_Att_App_Id;
UPDATE
bsc_kpi_tree_nodes_b
SET
node_id = p_New_DataSet_Map(i)
WHERE
indicator = p_Trg_indicator AND
node_id = p_Old_DataSet_Map(i);
UPDATE
bsc_kpi_tree_nodes_tl tr
SET
node_id = p_New_DataSet_Map(i),
name = (SELECT
d.name
FROM
bsc_sys_datasets_tl d
WHERE
d.dataset_id = p_New_DataSet_Map(i) AND
(d.language = tr.language OR
d.language = tr.language)),
help = (SELECT
d.help
FROM
bsc_sys_datasets_tl d
WHERE
d.dataset_id = p_New_DataSet_Map(i) AND
(d.language = tr.language OR
d.language = tr.language))
WHERE
indicator = p_Trg_indicator AND
node_id = p_Old_DataSet_Map(i);
UPDATE
bsc_tab_view_labels_b
SET
link_id = p_New_DataSet_Map(i)
WHERE
tab_view_id = p_Trg_indicator AND
tab_id = -999 AND
link_id = p_Old_DataSet_Map(i);
UPDATE
bsc_kpi_properties
SET
property_value = p_New_DataSet_Map(i)
WHERE
indicator = p_Trg_indicator AND
property_code = 'S_NODE_ID';
l_sql := 'SELECT ak_item.region_code,ak_item.region_application_id,ak_item.attribute_code,ak_item.attribute_application_id,ak_item.attribute2,';
SELECT
short_name
INTO
l_Calendar_Sht_Name
FROM
bsc_sys_calendars_b
WHERE
calendar_id = p_Target_Calendar;
SELECT
short_name
INTO
l_Periodicity_Sht_Name
FROM
bsc_sys_periodicities
WHERE
periodicity_id = p_New_Periodicities(i);
Update_AK_Item_Props (
p_commit => FND_API.G_FALSE
,p_region_code => p_short_name
,p_region_application_id => l_Region_Att_Id
,p_Attribute_Code => l_Item_Att_Code
,p_Attribute_Application_Id => l_Item_Att_App_Id
,p_Attribute2 => l_New_Attribute2
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
UPDATE
ak_region_items_tl ak_item
SET
attribute_label_long = (SELECT
tl.name
FROM
bsc_sys_periodicities_tl tl,
bsc_sys_periodicities b
WHERE
b.short_name = l_Periodicity_Sht_Name AND
b.periodicity_id = tl.periodicity_id AND
tl.language = ak_item.language)
WHERE
ak_item.region_code = p_short_name AND
ak_item.region_application_id = l_Region_Att_Id AND
ak_item.attribute_code = l_Item_Att_Code AND
ak_item.attribute_application_id = l_Item_Att_App_Id;
l_sql := 'SELECT ak_item.region_code,ak_item.region_application_id,ak_item.attribute_code,ak_item.attribute_application_id,';
l_sql := l_sql || ' SELECT ak_item.region_code,ak_item.region_application_id,ak_item.attribute_code,ak_item.attribute_application_id,';
SELECT
short_name
INTO
l_Dim_Short_Name
FROM
bsc_sys_dim_groups_vl
WHERE
dim_group_id = p_New_Dim_Groups(i);
SELECT
short_name
INTO
l_Dim_Lev_Sht_Name
FROM
bsc_sys_dim_levels_vl
WHERE
dim_level_id = p_New_Dim_Levels(i);
Update_AK_Item_Props (
p_commit => FND_API.G_FALSE
,p_region_code => p_short_name
,p_region_application_id => l_Region_Att_Id
,p_Attribute_Code => l_Item_Att_Code
,p_Attribute_Application_Id => l_Item_Att_App_Id
,p_Attribute2 => l_New_Attribute2
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
UPDATE
ak_region_items_tl ak_item
SET
attribute_label_long = (SELECT
tl.name
FROM
bsc_sys_dim_levels_tl tl,
bsc_sys_dim_levels_b b
WHERE
b.short_name = l_Dim_Lev_Sht_Name AND
b.dim_level_id = tl.dim_level_id AND
tl.language = ak_item.language)
WHERE
ak_item.region_code = p_short_name AND
ak_item.region_application_id = l_Region_Att_Id AND
ak_item.attribute_code = l_Item_Att_Code AND
ak_item.attribute_application_id = l_Item_Att_App_Id;
SELECT
attribute_code,attribute_application_id
BULK COLLECT INTO
l_Attribute_Code_Tbl,l_Attribute_App_Id_Tbl
FROM
ak_region_items
WHERE
region_code = p_short_name AND
attribute_code in (SELECT
attribute_code
FROM
ak_region_items
WHERE
region_code = p_short_name AND
node_query_flag = 'Y' AND
attribute2 like '%+%' AND
attribute2 not like 'TIME_COMPARISON_TYPE+BUDGET'
MINUS
SELECT
column_value attribute_code
FROM
TABLE(CAST(l_Retain_Dim_Att2 AS BIS_STRING_ARRAY)));
SELECT
region_application_id
INTO
l_Region_Att_Id
FROM
ak_regions
WHERE
region_code = p_short_name;
BIS_PMV_REGION_ITEMS_PVT.DELETE_REGION_ITEMS (
p_commit => p_commit
,p_region_code => p_short_name
,p_region_application_id => l_Region_Att_Id
,p_Attribute_Code_Tbl => l_Attribute_Code_Tbl
,p_Attribute_Appl_Id_Tbl => l_Attribute_App_Id_Tbl
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
l_sql := 'SELECT attribute_code,attribute_application_id,item_style,attribute_category ';
SELECT COUNT(1)
INTO l_Count
FROM AK_ATTRIBUTES
WHERE attribute_code = l_Attribute_Code AND
attribute_application_id = l_Attribute_Application_Id;
AK_ATTRIBUTES_PKG.INSERT_ROW (
X_ROWID => l_attribute_rowid,
X_ATTRIBUTE_APPLICATION_ID => l_Attribute_Application_Id,
X_ATTRIBUTE_CODE => l_Attribute_Code,
X_ATTRIBUTE_LABEL_LENGTH => BIS_AK_REGION_PUB.c_ATTR_LABEL_LENGTH,
X_ATTRIBUTE_VALUE_LENGTH => BIS_AK_REGION_PUB.c_ATTR_VALUE_LENGTH,
X_BOLD => BIS_AK_REGION_PUB.c_BOLD ,
X_ITALIC => BIS_AK_REGION_PUB.c_ITALIC,
X_UPPER_CASE_FLAG => BIS_AK_REGION_PUB.c_UPPER_CASE_FLAG,
X_VERTICAL_ALIGNMENT => BIS_AK_REGION_PUB.c_VERTICAL_ALIGNMENT,
X_HORIZONTAL_ALIGNMENT => BIS_AK_REGION_PUB.c_HORIZONTAL_ALIGNMENT,
X_DEFAULT_VALUE_VARCHAR2 => null,
X_DEFAULT_VALUE_NUMBER => null,
X_DEFAULT_VALUE_DATE => null,
X_LOV_REGION_CODE => null,
X_LOV_REGION_APPLICATION_ID => null,
X_DATA_TYPE => BIS_AK_REGION_PUB.c_ATTR_DATATYPE,
X_DISPLAY_HEIGHT => null,
X_ITEM_STYLE => l_Item_Style,
X_CSS_CLASS_NAME => null,
X_CSS_LABEL_CLASS_NAME => null,
X_PRECISION => null,
X_EXPANSION => null,
X_ALS_MAX_LENGTH => null,
X_POPLIST_VIEWOBJECT => null,
X_POPLIST_DISPLAY_ATTRIBUTE => null,
X_POPLIST_VALUE_ATTRIBUTE => null,
X_ATTRIBUTE_CATEGORY => l_Attribute_Category,
X_ATTRIBUTE1 => null,
X_ATTRIBUTE2 => null,
X_ATTRIBUTE3 => null,
X_ATTRIBUTE4 => null,
X_ATTRIBUTE5 => null,
X_ATTRIBUTE6 => null,
X_ATTRIBUTE7 => null,
X_ATTRIBUTE8 => null,
X_ATTRIBUTE9 => null,
X_ATTRIBUTE10 => null,
X_ATTRIBUTE11 => null,
X_ATTRIBUTE12 => null,
X_ATTRIBUTE13 => null,
X_ATTRIBUTE14 => null,
X_ATTRIBUTE15 => null,
X_NAME => l_Attribute_Code,
X_ATTRIBUTE_LABEL_LONG => null,
X_ATTRIBUTE_LABEL_SHORT => null,
X_DESCRIPTION => null,
X_CREATION_DATE => sysdate,
X_CREATED_BY => fnd_global.user_id,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => fnd_global.user_id,
X_LAST_UPDATE_LOGIN => fnd_global.user_id);
l_arr_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
SELECT column_name
FROM all_tab_columns
WHERE table_name = UPPER(l_table_name)
AND OWNER = UPPER(BSC_APPS.BSC_APPS_SCHEMA);
SELECT COUNT(0)
INTO l_count
FROM ak_regions
WHERE region_code = p_Old_Region_Code
AND region_application_id =BSC_MIGRATION.C_BSC_APP_ID;
BSC_KPI_PUB.Delete_Sim_Tree_Data
(
p_commit => FND_API.G_FALSE
, p_Bsc_Kpi_Entity_Rec => l_Bsc_Kpi_Entity_Rec
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
l_sql:= 'INSERT INTO ( SELECT ';
l_sql:= l_sql || ' SELECT ';
SELECT bsc_sys_image_id_s.nextval
INTO l_next_image_id
FROM dual;
h_sql := ' SELECT DISTINCT image_id '||
' FROM BSC_SYS_IMAGES_MAP_TL '||'@'||g_db_link ||
' WHERE SOURCE_TYPE =2 AND SOURCE_CODE = '|| p_Src_indicator;
SELECT distinct image_id
INTO l_image_id
FROM BSC_SYS_IMAGES_MAP_TL
WHERE SOURCE_TYPE =2
AND SOURCE_CODE =p_Trg_indicator;
BSC_UPDATE_UTIL.Execute_Immediate(l_sql);
SELECT
FND_FORM_FUNCTIONS_S.NEXTVAL
INTO
l_New_Function_Id
FROM dual;
UPDATE
bsc_kpis_b
SET
short_name = p_Region_Code
WHERE
indicator = p_Trg_indicator;
UPDATE
bsc_kpi_analysis_options_b
SET
short_name = p_Region_Code
WHERE
indicator = p_Trg_indicator;
UPDATE fnd_form_functions_tl tl
SET user_function_name = (SELECT name FROM bsc_kpis_tl k, bsc_kpis_b b
WHERE b.indicator = k.indicator AND
b.short_name = p_Region_Code AND k.language = tl.language)
WHERE
function_id = l_new_function_id;
UPDATE
ak_regions
SET
attribute12 = p_Region_Code
WHERE
region_code = p_Region_Code;