[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
h_sql := 'SELECT d.code'||
' FROM '||x_dimension_table||' d, '||x_temp_table||' t'||
' WHERE d.code = t.code AND ('||h_cond||')';
l_parent_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
l_src_parent_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
BSC_UPDATE_UTIL.Make_Lst_Description(l_parent_columns, l_num_parent_columns, 'VARCHAR2(400)');
SELECT COUNT(1) INTO l_count
FROM all_snapshot_logs
WHERE master = l_table_name AND log_owner = l_table_owner;
SELECT COUNT(1) INTO l_count
FROM all_constraints
WHERE owner = l_table_owner AND constraint_type = 'P' AND table_name = l_table_name;
l_sql := l_sql||','||BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(l_parent_columns, l_num_parent_columns);
h_table_columns BSC_UPDATE_UTIL.t_array_temp_table_cols;
h_table_name := 'BSC_AW_DIM_DELETE';
h_table_columns.delete;
h_table_columns(h_num_columns).column_name := 'DELETE_VALUE';
IF NOT BSC_UPDATE_UTIL.Create_Permanent_Table(h_table_name, h_table_columns, h_num_columns,
h_tablespace, h_idx_tablespace) THEN
RAISE e_unexpected_error;
h_table_columns.delete;
IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
RAISE e_unexpected_error;
h_table_columns.delete;
IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
RAISE e_unexpected_error;
h_table_columns.delete;
IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
RAISE e_unexpected_error;
BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_UNEXPECTED_ERROR'),
x_source => 'BSC_UPDATE_DIM.Create_AW_Dim_Temp_Tables');
x_source => 'BSC_UPDATE_DIM.Create_AW_Dim_Temp_Tables');
h_table_columns BSC_UPDATE_UTIL.t_array_temp_table_cols;
h_table_columns.delete;
IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
RAISE e_unexpected_error;
h_table_columns.delete;
IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
RAISE e_unexpected_error;
IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
RAISE e_unexpected_error;
h_table_columns.delete;
IF NOT BSC_UPDATE_UTIL.Create_Permanent_Table(h_table_name, h_table_columns, h_num_columns,
h_tablespace, h_idx_tablespace) THEN
RAISE e_unexpected_error;
h_table_columns.delete;
IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
RAISE e_unexpected_error;
BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_UNEXPECTED_ERROR'),
x_source => 'BSC_UPDATE_DIM.Create_Dbi_Dim_Temp_Tables');
x_source => 'BSC_UPDATE_DIM.Create_Dbi_Dim_Temp_Tables');
| FUNCTION Delete_Codes_Cascade
+============================================================================*/
FUNCTION Delete_Codes_Cascade(
x_dim_table IN VARCHAR2,
x_deleted_codes IN BSC_UPDATE_UTIL.t_array_of_number,
x_num_deleted_codes IN NUMBER
) RETURN BOOLEAN IS
e_unexpected_error EXCEPTION;
SELECT dim_level_id, level_pk_col
FROM bsc_sys_dim_levels_b
WHERE level_table_name = p_level_table_name;
SELECT r.relation_col, t.level_pk_col
FROM bsc_sys_dim_level_rels r, bsc_sys_dim_levels_b t
WHERE t.dim_level_id = r.parent_dim_level_id AND
r.dim_level_id = p_dim_level_id AND r.relation_type = p_relation_type;
SELECT t.level_table_name
FROM bsc_sys_dim_levels_b t, bsc_sys_dim_level_rels r
WHERE t.dim_level_id = r.dim_level_id AND
r.parent_dim_level_id = p_parent_id AND r.relation_type = p_relation_type;
h_deleted_codes BSC_UPDATE_UTIL.t_array_of_number;
h_deleted_codes1 BSC_UPDATE_UTIL.t_array_of_varchar2;
h_deleted_codes2 BSC_UPDATE_UTIL.t_array_of_varchar2;
h_num_deleted_codes NUMBER;
h_num_deleted_codes := 0;
FOR h_i IN 1..x_num_deleted_codes LOOP
BSC_APPS.Add_Value_Big_In_Cond(1, x_deleted_codes(h_i));
IF NOT Delete_Key_Values_In_Tables(h_level_pk_col, h_condition) THEN
RAISE e_unexpected_error;
FOR h_i IN 1..x_num_deleted_codes LOOP
BSC_APPS.Add_Value_Big_In_Cond(1, x_deleted_codes(h_i));
h_sql := 'SELECT '||h_level_pk_col||', '||h_mn_level_pk_col||
' FROM '||h_mn_dim_table||
' WHERE '||h_condition;
h_num_deleted_codes := 0;
h_num_deleted_codes := h_num_deleted_codes + 1;
h_deleted_codes1(h_num_deleted_codes) := h_code1;
h_deleted_codes2(h_num_deleted_codes) := h_code2;
IF h_num_deleted_codes > 0 THEN
IF NOT Delete_Codes_CascadeMN(h_mn_dim_table,
h_level_pk_col,
h_mn_level_pk_col,
h_deleted_codes1,
h_deleted_codes2,
h_num_deleted_codes) THEN
RAISE e_unexpected_error;
FOR h_i IN 1..x_num_deleted_codes LOOP
BSC_APPS.Add_Value_Big_In_Cond(1, x_deleted_codes(h_i));
h_sql := 'SELECT DISTINCT CODE FROM '||h_child_dim_table||
' WHERE '||h_condition;
h_num_deleted_codes := 0;
h_num_deleted_codes := h_num_deleted_codes + 1;
h_deleted_codes(h_num_deleted_codes) := h_code;
IF h_num_deleted_codes > 0 THEN
IF NOT Delete_Codes_Cascade(h_child_dim_table,
h_deleted_codes,
h_num_deleted_codes) THEN
RAISE e_unexpected_error;
h_dim_level_list.delete;
FOR h_i IN 1..x_num_deleted_codes LOOP
BSC_APPS.Add_Value_Big_In_Cond(1, x_deleted_codes(h_i));
Insert_AW_Delete_Value(x_dim_table, x_deleted_codes(h_i));
h_sql := 'DELETE FROM '||x_dim_table||
' WHERE '||h_condition;
BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_DIMREC_DELETE_FAILED'),
x_source => 'BSC_UPDATE_BASE.Delete_Codes_Cascade');
x_source => 'BSC_UPDATE_DIM.Delete_Codes_Cascade');
END Delete_Codes_Cascade;
| FUNCTION Delete_Codes_CascadeMN
+============================================================================*/
FUNCTION Delete_Codes_CascadeMN(
x_dim_table IN VARCHAR2,
x_key_column1 IN VARCHAR2,
x_key_column2 IN VARCHAR2,
x_deleted_codes1 IN BSC_UPDATE_UTIL.t_array_of_varchar2,
x_deleted_codes2 IN BSC_UPDATE_UTIL.t_array_of_varchar2,
x_num_deleted_codes IN NUMBER
) RETURN BOOLEAN IS
h_condition VARCHAR2(32700);
SELECT DISTINCT bt.table_name
FROM (SELECT DISTINCT table_name
FROM bsc_db_tables_rels
WHERE source_table_name IN (
SELECT table_name
FROM bsc_db_tables
WHERE table_type = p_table_type)
) bt,
bsc_db_tables_cols c
WHERE bt.table_name = c.table_name AND
(c.column_name = p_col_name1 OR
c.column_name = p_col_name2) AND
c.column_type = p_col_type
GROUP BY bt.table_name
HAVING COUNT(*) = p_count;
FOR h_i IN 1..x_num_deleted_codes LOOP
BSC_APPS.Add_Value_Big_In_Cond(1, x_deleted_codes1(h_i)||'-'||x_deleted_codes2(h_i));
h_sql := 'DELETE FROM '||h_system_table||
' WHERE '||h_condition;
BEGIN BSC_UPDATE_UTIL.Execute_Immediate(h_sql); EXCEPTION WHEN OTHERS THEN NULL; END;
h_proj_tbl_name := BSC_UPDATE_BASE_V2.Get_Base_Proj_Tbl_Name(h_system_table);
h_sql := 'DELETE FROM '||h_proj_tbl_name||
' WHERE '||h_condition;
BEGIN BSC_UPDATE_UTIL.Execute_Immediate(h_sql); EXCEPTION WHEN OTHERS THEN NULL; END;
h_sql := 'DELETE FROM '||x_dim_table||
' WHERE '||h_condition;
BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
x_source => 'BSC_UPDATE_DIM.Delete_Codes_CascadeMN');
END Delete_Codes_CascadeMN;
| FUNCTION Delete_Key_Values_In_Tables
+============================================================================*/
FUNCTION Delete_Key_Values_In_Tables(
x_level_pk_col IN VARCHAR2,
x_condition IN VARCHAR2
) RETURN BOOLEAN IS
h_sql VARCHAR2(32700);
SELECT DISTINCT bt.table_name
FROM (SELECT DISTINCT table_name
FROM bsc_db_tables_rels
WHERE source_table_name IN (
SELECT table_name
FROM bsc_db_tables
WHERE table_type = p_table_type)
) bt,
bsc_db_tables_cols c
WHERE bt.table_name = c.table_name AND
c.column_name = p_col_name AND
c.column_type = p_col_type;
h_sql := 'DELETE FROM '||h_table_name||
' WHERE '||x_condition;
BEGIN BSC_UPDATE_UTIL.Execute_Immediate(h_sql); EXCEPTION WHEN OTHERS THEN NULL; END;
h_proj_tbl_name := BSC_UPDATE_BASE_V2.Get_Base_Proj_Tbl_Name(h_table_name);
h_sql := 'DELETE FROM '||h_proj_tbl_name||
' WHERE '||x_condition;
BEGIN BSC_UPDATE_UTIL.Execute_Immediate(h_sql); EXCEPTION WHEN OTHERS THEN NULL; END;
x_source => 'BSC_UPDATE_DIM.Delete_Key_Values_In_Tables');
END Delete_Key_Values_In_Tables;
l_dbi_dim_data BSC_UPDATE_DIM.t_dbi_dim_data;
SELECT level_table_name
INTO l_level_table_name
FROM bsc_sys_dim_levels_b
WHERE short_name = l_dim_short_name;
l_sql := 'UPDATE bsc_object_refresh_log'||
' SET refresh_start_time = SYSDATE, refresh_end_time = NULL'||
' WHERE object_name = :1 AND object_type = :2';
l_sql := 'INSERT INTO bsc_object_refresh_log'||
' (object_name, object_type, refresh_start_time, refresh_end_time)'||
' VALUES (:1, :2, SYSDATE, NULL)';
BSC_UPDATE_UTIL.Execute_Immediate('DELETE BSC_AW_TMP_DENORM');
l_sql := 'INSERT INTO BSC_AW_TMP_DENORM (CHILD_VALUE, PARENT_VALUE)'||
' SELECT '||l_dbi_dim_data.child_col||', '||l_dbi_dim_data.parent_col||
' FROM '||l_dbi_dim_data.denorm_table;
BSC_UPDATE_UTIL.Truncate_Table(l_dbi_dim_data.denorm_table);
l_sql := 'SELECT DISTINCT '||l_dbi_dim_data.parent_col_src||
' FROM '||l_dbi_dim_data.denorm_src_object||
' WHERE top_node_flag = :1';
l_sql := 'SELECT DISTINCT imm_child_id'||
' FROM '||l_dbi_dim_data.denorm_src_object||
' WHERE '||l_dbi_dim_data.parent_col_src||' <> imm_child_id'||
' AND ('||l_where_level||')';
l_sql := 'INSERT INTO '||l_dbi_dim_data.denorm_table||' ('||
l_dbi_dim_data.parent_col||', '||l_dbi_dim_data.child_col||', '||l_dbi_dim_data.parent_level_col||')'||
' SELECT '||l_dbi_dim_data.parent_col_src||', '||l_dbi_dim_data.child_col_src||', :1'||
' FROM '||l_dbi_dim_data.denorm_src_object||
' WHERE '||l_where_level;
l_sql := 'INSERT INTO BSC_AW_REC_DIM_HIER_CHANGE (DIM_LEVEL, CHILD_VALUE, PARENT_VALUE)'||
' SELECT :1, CHILD_VALUE, PARENT_VALUE FROM BSC_AW_TMP_DENORM'||
' MINUS '||
' SELECT :2, '||l_dbi_dim_data.child_col||', '||l_dbi_dim_data.parent_col||
' FROM '||l_dbi_dim_data.denorm_table;
l_sql := 'UPDATE bsc_object_refresh_log'||
' SET refresh_end_time = SYSDATE'||
' WHERE object_name = :1 AND object_type = :2';
x_source => 'BSC_UPDATE_DIM.Denorm_Eni_Item_Vbh_Cat');
l_dbi_dim_data BSC_UPDATE_DIM.t_dbi_dim_data;
l_ids_this_level BSC_UPDATE_UTIL.t_array_of_number;
l_ids BSC_UPDATE_UTIL.t_array_of_number;
SELECT level_table_name
INTO l_level_table_name
FROM bsc_sys_dim_levels_b
WHERE short_name = l_short_name;
l_sql := 'UPDATE bsc_object_refresh_log'||
' SET refresh_start_time = SYSDATE, refresh_end_time = NULL'||
' WHERE object_name = :1 AND object_type = :2';
l_sql := 'INSERT INTO bsc_object_refresh_log'||
' (object_name, object_type, refresh_start_time, refresh_end_time)'||
' VALUES (:1, :2, SYSDATE, NULL)';
BSC_UPDATE_UTIL.Execute_Immediate('DELETE BSC_AW_TMP_DENORM');
l_sql := 'INSERT INTO BSC_AW_TMP_DENORM (CHILD_VALUE, PARENT_VALUE)'||
' SELECT '||l_dbi_dim_data.child_col||', '||l_dbi_dim_data.parent_col||
' FROM '||l_dbi_dim_data.denorm_table;
BSC_UPDATE_UTIL.Truncate_Table(l_dbi_dim_data.denorm_table);
l_ids_this_level.delete;
l_sql := 'SELECT DISTINCT '||l_dbi_dim_data.child_col_src||
' FROM '||l_dbi_dim_data.denorm_src_object||
' WHERE '||l_src_condition||
' AND '||l_dbi_dim_data.parent_col_src||' IS NULL';
l_sql := 'SELECT DISTINCT '||l_dbi_dim_data.child_col_src||
' FROM '||l_dbi_dim_data.denorm_src_object||
' WHERE '||l_src_condition||
' AND '||l_where_level;
l_sql := 'INSERT INTO '||l_dbi_dim_data.denorm_table||' ('||
l_dbi_dim_data.parent_col||', '||l_dbi_dim_data.child_col||', '||l_dbi_dim_data.parent_level_col||
') VALUES (:1, :2, :3)';
IF NOT Insert_Children_Denorm_Table(x_parent_id => l_ids_this_level(l_i),
x_ids => l_ids,
x_num_ids => l_num_ids,
x_level => l_current_level,
x_denorm_table => l_dbi_dim_data.denorm_table,
x_child_col => l_dbi_dim_data.child_col,
x_parent_col => l_dbi_dim_data.parent_col,
x_parent_level_col => l_dbi_dim_data.parent_level_col,
x_denorm_src_object => l_dbi_dim_data.denorm_src_object,
x_child_col_src => l_dbi_dim_data.child_col_src,
x_parent_col_src => l_dbi_dim_data.parent_col_src,
x_src_condition => l_src_condition) THEN
RAISE e_unexpected_error;
l_sql := 'INSERT INTO BSC_AW_REC_DIM_HIER_CHANGE (DIM_LEVEL, CHILD_VALUE, PARENT_VALUE)'||
' SELECT :1, CHILD_VALUE, PARENT_VALUE FROM BSC_AW_TMP_DENORM'||
' MINUS '||
' SELECT :2, '||l_dbi_dim_data.child_col||', '||l_dbi_dim_data.parent_col||
' FROM '||l_dbi_dim_data.denorm_table;
l_sql := 'UPDATE bsc_object_refresh_log'||
' SET refresh_end_time = SYSDATE'||
' WHERE object_name = :1 AND object_type = :2';
BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_UNEXPECTED_ERROR'),
x_source => 'BSC_UPDATE_DIM.Denorm_Eni_Item_Itm_Cat');
x_source => 'BSC_UPDATE_DIM.Denorm_Eni_Item_Itm_Cat');
l_dbi_dim_data BSC_UPDATE_DIM.t_dbi_dim_data;
SELECT level_table_name
INTO l_level_table_name
FROM bsc_sys_dim_levels_b
WHERE short_name = l_dim_short_name;
l_sql := 'UPDATE bsc_object_refresh_log'||
' SET refresh_start_time = SYSDATE, refresh_end_time = NULL'||
' WHERE object_name = :1 AND object_type = :2';
l_sql := 'INSERT INTO bsc_object_refresh_log'||
' (object_name, object_type, refresh_start_time, refresh_end_time)'||
' VALUES (:1, :2, SYSDATE, NULL)';
BSC_UPDATE_UTIL.Execute_Immediate('DELETE BSC_AW_TMP_DENORM');
l_sql := 'INSERT INTO BSC_AW_TMP_DENORM (CHILD_VALUE, PARENT_VALUE)'||
' SELECT '||l_dbi_dim_data.child_col||', '||l_dbi_dim_data.parent_col||
' FROM '||l_dbi_dim_data.denorm_table;
BSC_UPDATE_UTIL.Truncate_Table(l_dbi_dim_data.denorm_table);
l_sql := 'INSERT INTO '||l_dbi_dim_data.denorm_table||' ('||l_lst_cols||')'||
' SELECT '||l_lst_src_cols||
' FROM '||l_dbi_dim_data.denorm_src_object||
' WHERE '||l_dbi_dim_data.parent_level_src_col||' <= :1';
l_sql := 'INSERT INTO BSC_AW_REC_DIM_HIER_CHANGE (DIM_LEVEL, CHILD_VALUE, PARENT_VALUE)'||
' SELECT :1, CHILD_VALUE, PARENT_VALUE FROM BSC_AW_TMP_DENORM'||
' MINUS '||
' SELECT :2, '||l_dbi_dim_data.child_col||', '||l_dbi_dim_data.parent_col||
' FROM '||l_dbi_dim_data.denorm_table;
l_sql := 'UPDATE bsc_object_refresh_log'||
' SET refresh_end_time = SYSDATE'||
' WHERE object_name = :1 AND object_type = :2';
x_source => 'BSC_UPDATE_DIM.Denorm_Hri_Per_Usrdr_H');
l_dbi_dim_data BSC_UPDATE_DIM.t_dbi_dim_data;
l_ids_this_level BSC_UPDATE_UTIL.t_array_of_number;
l_ids BSC_UPDATE_UTIL.t_array_of_number;
SELECT level_table_name
INTO l_level_table_name
FROM bsc_sys_dim_levels_b
WHERE short_name = l_short_name;
l_sql := 'UPDATE bsc_object_refresh_log'||
' SET refresh_start_time = SYSDATE, refresh_end_time = NULL'||
' WHERE object_name = :1 AND object_type = :2';
l_sql := 'INSERT INTO bsc_object_refresh_log'||
' (object_name, object_type, refresh_start_time, refresh_end_time)'||
' VALUES (:1, :2, SYSDATE, NULL)';
BSC_UPDATE_UTIL.Execute_Immediate('DELETE BSC_AW_TMP_DENORM');
l_sql := 'INSERT INTO BSC_AW_TMP_DENORM (CHILD_VALUE, PARENT_VALUE)'||
' SELECT '||l_dbi_dim_data.child_col||', '||l_dbi_dim_data.parent_col||
' FROM '||l_dbi_dim_data.denorm_table;
BSC_UPDATE_UTIL.Truncate_Table(l_dbi_dim_data.denorm_table);
l_ids_this_level.delete;
l_sql := 'SELECT DISTINCT '||l_dbi_dim_data.child_col_src||
' FROM '||l_dbi_dim_data.denorm_src_object||
' WHERE '||l_dbi_dim_data.parent_col_src||' IS NULL';
l_sql := 'SELECT DISTINCT '||l_dbi_dim_data.child_col_src||
' FROM '||l_dbi_dim_data.denorm_src_object||
' WHERE '||l_where_level;
l_sql := 'INSERT INTO '||l_dbi_dim_data.denorm_table||' ('||
l_dbi_dim_data.parent_col||', '||l_dbi_dim_data.child_col||', '||l_dbi_dim_data.parent_level_col||
') VALUES (:1, :2, :3)';
IF NOT Insert_Children_Denorm_Table(x_parent_id => l_ids_this_level(l_i),
x_ids => l_ids,
x_num_ids => l_num_ids,
x_level => l_current_level,
x_denorm_table => l_dbi_dim_data.denorm_table,
x_child_col => l_dbi_dim_data.child_col,
x_parent_col => l_dbi_dim_data.parent_col,
x_parent_level_col => l_dbi_dim_data.parent_level_col,
x_denorm_src_object => l_dbi_dim_data.denorm_src_object,
x_child_col_src => l_dbi_dim_data.child_col_src,
x_parent_col_src => l_dbi_dim_data.parent_col_src,
x_src_condition => NULL) THEN
RAISE e_unexpected_error;
l_sql := 'INSERT INTO BSC_AW_REC_DIM_HIER_CHANGE (DIM_LEVEL, CHILD_VALUE, PARENT_VALUE)'||
' SELECT :1, CHILD_VALUE, PARENT_VALUE FROM BSC_AW_TMP_DENORM'||
' MINUS '||
' SELECT :2, '||l_dbi_dim_data.child_col||', '||l_dbi_dim_data.parent_col||
' FROM '||l_dbi_dim_data.denorm_table;
l_sql := 'UPDATE bsc_object_refresh_log'||
' SET refresh_end_time = SYSDATE'||
' WHERE object_name = :1 AND object_type = :2';
BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_UNEXPECTED_ERROR'),
x_source => 'BSC_UPDATE_DIM.Denorm_Pji_Organizations');
x_source => 'BSC_UPDATE_DIM.Denorm_Pji_Organizations');
select count(level_table_name)
into h_count
from bsc_kpi_dim_levels_b
where indicator in (
select p.indicator
from bsc_kpi_properties p, bsc_kpis_b k
where p.indicator = k.indicator and
p.property_code = h_aw_impl_type_name and
p.property_value = h_aw_impl_type and
k.prototype_flag in (0,6,7)
) and level_table_name = x_dim_table;
select count(level_table_name)
into h_count
from bsc_kpi_dim_levels_b
where indicator in (
select k.indicator
from bsc_kpi_properties p, bsc_kpis_b k
where p.indicator (+) = k.indicator and
p.property_code (+) = h_mv_impl_type_name and
(p.property_value is null or p.property_value = h_mv_impl_type) and
k.prototype_flag in (0,6,7)
) and level_table_name = x_dim_table;
x_dbi_dim_data OUT NOCOPY BSC_UPDATE_DIM.t_array_dbi_dim_data
) IS
l_i NUMBER;
x_aux_fields IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2
) RETURN NUMBER IS
TYPE t_cursor IS REF CURSOR;
c_aux_fields_sql VARCHAR2(2000) := 'SELECT c.column_name'||
' FROM bsc_sys_dim_level_cols c, bsc_sys_dim_levels_b d'||
' WHERE d.dim_level_id = c.dim_level_id AND'||
' d.level_table_name = :1 AND'||
' column_type = :2';
SELECT c.column_name
FROM bsc_sys_dim_level_cols c, bsc_sys_dim_levels_b d
WHERE d.dim_level_id = c.dim_level_id AND
d.level_table_name = p_level_table_name AND
column_type = p_column_type;
x_child_dimensions IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2
) RETURN NUMBER IS
h_num_child_dimensions NUMBER;
c_child_dimensions_sql VARCHAR2(2000) := 'SELECT t.level_table_name'||
' FROM bsc_sys_dim_levels_b t, bsc_sys_dim_level_rels r'||
' WHERE t.dim_level_id = r.dim_level_id AND'||
' r.parent_dim_level_id = :1 AND r.relation_type = :2';
SELECT t.level_table_name
FROM bsc_sys_dim_levels_b t, bsc_sys_dim_level_rels r
WHERE t.dim_level_id = r.dim_level_id AND
r.parent_dim_level_id = p_parent_id AND r.relation_type = p_relation_type;
h_sql := 'SELECT dim_level_id'||
' FROM bsc_sys_dim_levels_b'||
' WHERE level_table_name = :1';
SELECT dim_level_id
INTO h_table_id
FROM bsc_sys_dim_levels_b
WHERE level_table_name = x_dimension_table;
x_parent_columns IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
x_src_parent_columns IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2
) RETURN NUMBER IS
CURSOR c_parent_cols IS
SELECT p.level_pk_col
FROM bsc_sys_dim_levels_b c, bsc_sys_dim_level_rels r, bsc_sys_dim_levels_b p
WHERE c.dim_level_id = r.dim_level_id AND
r.parent_dim_level_id = p.dim_level_id AND
r.relation_type = 1 AND
r.dim_level_id <> r.parent_dim_level_id AND
c.short_name = x_dim_short_name
ORDER BY p.level_pk_col;
l_dbi_dim_data BSC_UPDATE_DIM.t_dbi_dim_data;
SELECT level_view_name
INTO l_view_name
FROM bsc_sys_dim_levels_b
WHERE short_name = x_dim_short_name;
x_dbi_dim_data OUT NOCOPY BSC_UPDATE_DIM.t_dbi_dim_data
) IS
l_i NUMBER;
x_indicators IN BSC_UPDATE_UTIL.t_array_of_number,
x_num_indicators IN NUMBER,
x_dbi_dimensions IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
x_num_dbi_dimensions IN OUT NOCOPY NUMBER
) RETURN BOOLEAN IS
h_where_indics VARCHAR2(32000);
h_dbi_dim_data BSC_UPDATE_DIM.t_dbi_dim_data;
h_sql := 'SELECT DISTINCT level_shortname'||
' FROM bsc_kpi_dim_levels_vl'||
' WHERE ('||h_where_indics||') AND level_source = :1';
x_source => 'BSC_UPDATE_DIM.Get_Dbi_Dims_Kpis');
| FUNCTION Get_Deleted_Records
+============================================================================*/
FUNCTION Get_Deleted_Records(
x_dimension_table IN VARCHAR2,
x_temp_table IN VARCHAR2,
x_deleted_records IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number
) RETURN NUMBER IS
h_num_deleted_records NUMBER;
h_num_deleted_records := 0;
h_sql := 'SELECT T.CODE'||
' FROM '||x_temp_table||' T, '||x_dimension_table||' D'||
' WHERE T.CODE = D.CODE (+)'||
' AND D.CODE IS NULL';
h_num_deleted_records := h_num_deleted_records + 1;
x_deleted_records(h_num_deleted_records) := h_code;
RETURN h_num_deleted_records;
END Get_Deleted_Records;
h_sql := 'SELECT table_name'||
' FROM bsc_db_tables_rels'||
' WHERE source_table_name = :1';
SELECT table_name
INTO h_table_name
FROM bsc_db_tables_rels
WHERE source_table_name = x_input_table;
h_sql := 'SELECT COUNT(*)'||
' FROM bsc_sys_dim_levels_b'||
' WHERE level_table_name = :1';
SELECT COUNT(*)
INTO h_count
FROM bsc_sys_dim_levels_b
WHERE level_table_name = x_dim_table;
h_sql := 'SELECT COUNT(*)'||
' FROM bsc_sys_dim_level_rels'||
' WHERE relation_col = :1';
SELECT COUNT(*)
INTO h_count
FROM bsc_sys_dim_level_rels
WHERE relation_col = x_dim_table;
x_parent_tables IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
x_parent_keys IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2
) RETURN NUMBER IS
TYPE t_cursor IS REF CURSOR;
c_parents_sql VARCHAR2(2000) := 'SELECT dp.level_table_name, dp.level_pk_col'||
' FROM bsc_sys_dim_levels_b d, bsc_sys_dim_levels_b dp,'||
' bsc_sys_dim_level_rels r'||
' WHERE d.dim_level_id = r.dim_level_id AND'||
' r.parent_dim_level_id = dp.dim_level_id AND'||
' DECODE(r.relation_type, 2, r.relation_col,'||
' d.level_table_name) = :1';
SELECT dp.level_table_name, dp.level_pk_col
FROM bsc_sys_dim_levels_b d, bsc_sys_dim_levels_b dp, bsc_sys_dim_level_rels r
WHERE d.dim_level_id = r.dim_level_id AND
r.parent_dim_level_id = dp.dim_level_id AND
DECODE(r.relation_type, 2, r.relation_col, d.level_table_name) = p_dim_table;
h_sql := 'SELECT level_pk_col'||
' FROM bsc_sys_dim_levels_b'||
' WHERE level_table_name = :1';
SELECT level_pk_col
INTO h_level_pk_col
FROM bsc_sys_dim_levels_b
WHERE level_table_name = x_dimension_table;
h_sql := 'SELECT NVL(MAX(CODE) + 1, 1) FROM '||x_dim_table;
x_source => 'BSC_UPDATE_BASE.Get_New_Code');
x_parent_dimensions IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2
) RETURN NUMBER IS
h_num_parent_dimensions NUMBER;
c_parent_dimensions_sql VARCHAR2(2000) := 'SELECT t.level_table_name'||
' FROM bsc_sys_dim_levels_b t, bsc_sys_dim_level_rels r'||
' WHERE t.dim_level_id = r.parent_dim_level_id AND'||
' r.dim_level_id = :1 AND r.relation_type = :2';
SELECT t.level_table_name
FROM bsc_sys_dim_levels_b t, bsc_sys_dim_level_rels r
WHERE t.dim_level_id = r.parent_dim_level_id AND
r.dim_level_id = p_dim_level_id AND r.relation_type = p_relation_type;
h_sql := 'SELECT dim_level_id'||
' FROM bsc_sys_dim_levels_b'||
' WHERE level_table_name = :1';
SELECT dim_level_id
INTO h_table_id
FROM bsc_sys_dim_levels_b
WHERE level_table_name = x_dimension_table;
x_relation_cols IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2
) RETURN NUMBER IS
TYPE t_cursor IS REF CURSOR;
c_relation_cols_sql VARCHAR2(2000) := 'SELECT r.relation_col'||
' FROM bsc_sys_dim_levels_b d, bsc_sys_dim_level_rels r'||
' WHERE d.dim_level_id = r.dim_level_id AND'||
' d.level_table_name = :1 AND r.relation_type = :2';
SELECT r.relation_col
FROM bsc_sys_dim_levels_b d, bsc_sys_dim_level_rels r
WHERE d.dim_level_id = r.dim_level_id AND
d.level_table_name = p_level_table_name AND r.relation_type = p_relation_type;
l_ids BSC_UPDATE_UTIL.t_array_of_number;
l_values BSC_UPDATE_UTIL.t_array_of_varchar2;
select count(view_name) into l_count
from user_views
where view_name = l_dbi_plans_view;
l_sql := 'DELETE FROM bsc_sys_benchmarks_b'||
' WHERE source_type = :1'||
' AND data_type NOT IN ('||
' SELECT id FROM isc_plan_snapshot_v'||
')';
DELETE FROM bsc_sys_benchmarks_tl
WHERE bm_id NOT IN (SELECT bm_id FROM bsc_sys_benchmarks_b);
l_sql := 'SELECT s.id, s.value'||
' FROM isc_plan_snapshot_v s, bsc_sys_benchmarks_b b'||
' WHERE s.id = b.data_type (+) AND b.source_type (+) = :1 AND b.data_type IS NULL AND s.id < 1000';
SELECT NVL(MAX(bm_id)+1,1) INTO l_bm_id
FROM bsc_sys_benchmarks_b;
BSC_SYS_BENCHMARKS_PKG.Insert_Row(x_bm_id => l_bm_id,
x_color => 0,
x_data_type => l_ids(l_i),
x_source_type => 2,
x_periodicity_id => 0,
x_no_display_flag => 0,
x_name => l_values(l_i));
g_dbi_dim_data.delete;
g_dbi_dim_data(l_i).source_object := '(SELECT /*+parallel (drg)*/ to_char(drg.id) USER_CODE,to_char(drg.id) CODE '||
'FROM jtf_rs_dbi_denorm_res_groups drg
UNION ALL SELECT TO_CHAR(-1111) USER_CODE,TO_CHAR(-1111) CODE FROM dual
UNION ALL SELECT /*+parallel (drg)*/ to_char(drg.id_for_grp_mem) USER_CODE,to_char(drg.id_for_grp_mem) CODE FROM '||
'jtf_rs_dbi_denorm_res_groups drg
UNION ALL SELECT /*+parallel (d1)*/ to_char(d1.group_id) USER_CODE,to_char(d1.group_id) CODE '||
'FROM jtf_rs_groups_denorm d1) JTF_ORG_SALES_GROUP';
g_dbi_dim_data(l_i).source_object := '(SELECT /*+ parallel (s) */ TO_CHAR(s.vacancy_id) USER_CODE,'||
' to_char(s.vacancy_id) CODE'||
' FROM per_all_vacancies s'||
' UNION ALL SELECT TO_CHAR(-1) USER_CODE, TO_CHAR(-1) CODE'||
' FROM dual) PER_ALL_VACANCIES_S';
g_dbi_dim_data(l_i).source_object := '(SELECT /*+ parallel (s) */ TO_CHAR(s.grade_id) USER_CODE,'||
' to_char(s.grade_id) CODE'||
' FROM per_grades s'||
' UNION ALL SELECT TO_CHAR(-1) USER_CODE, TO_CHAR(-1) CODE'||
' FROM dual) PER_GRADES_S';
g_dbi_dim_data(l_i).source_object := '(SELECT /*+ parallel (s) */ TO_CHAR(s.job_id) USER_CODE,'||
' to_char(s.job_id) CODE'||
' FROM per_jobs s'||
' UNION ALL SELECT TO_CHAR(-1) USER_CODE, TO_CHAR(-1) CODE'||
' FROM dual) PER_JOBS_S';
g_dbi_dim_data(l_i).source_object := '(SELECT /*+ parallel (s) */ TO_CHAR(s.org_structure_version_id) USER_CODE,'||
' to_char(s.org_structure_version_id) CODE'||
' FROM per_org_structure_versions s) PER_ORG_STRUCTURE_VERSIONS_S';
g_dbi_dim_data(l_i).source_object := '(SELECT /*+ parallel (s) */ TO_CHAR(s.organization_structure_id) USER_CODE,'||
' to_char(s.organization_structure_id) CODE'||
' FROM per_organization_structures s) PER_ORGANIZATION_STRUCTURES_S';
g_dbi_dim_data(l_i).source_object := '(SELECT /*+ parallel (s) parallel (s1) */ TO_CHAR(s.organization_id) USER_CODE,'||
' to_char(s.organization_id) CODE'||
' FROM hr_all_organization_units s, hr_organization_information s1'||
' WHERE s.organization_id = s1.organization_id AND'||
' s1.org_information1 = ''HR_ORG'''||
' UNION ALL SELECT TO_CHAR(-1) USER_CODE, TO_CHAR(-1) CODE'||
' FROM dual) HR_ALL_ORGANIZATION_UNITS_S';
g_dbi_dim_data(l_i).source_object := '(SELECT /*+ parallel (s) parallel (s1) */ TO_CHAR(s.organization_id) USER_CODE,'||
' to_char(s.organization_id) CODE'||
' FROM hr_all_organization_units s, hr_organization_information s1'||
' WHERE s.organization_id = s1.organization_id AND'||
' s1.org_information1 = ''HR_ORG'''||
' UNION ALL SELECT TO_CHAR(-1) USER_CODE, TO_CHAR(-1) CODE'||
' FROM dual) HR_ALL_ORGANIZATION_UNITS_S';
g_dbi_dim_data(l_i).source_object := '(SELECT /*+ parallel (s) parallel (s1) */ DISTINCT'||
' TO_CHAR(s.organization_id) USER_CODE, to_char(s.organization_id) CODE'||
' FROM hr_all_organization_units s, hri_org_hrchy_summary s1'||
' WHERE s.organization_id = s1.organization_id AND s1.sub_org_relative_level = 0'||
') HR_ALL_ORGANIZATION_UNITS_S';
g_dbi_dim_data(l_i).source_object := '(SELECT /*+ parallel (s) */ TO_CHAR(s.person_id) USER_CODE,'||
' to_char(s.person_id) CODE,s.effective_start_date, s.effective_end_date'||
' FROM per_all_people_f s'||
' WHERE current_employee_flag = ''Y'' AND'||
' trunc(sysdate) BETWEEN effective_start_date AND effective_end_date'||
' UNION ALL SELECT TO_CHAR(-1) USER_CODE, TO_CHAR(-1) CODE,'||
' hr_general.start_of_time effective_start_date,'||
' hr_general.end_of_time effective_end_date'||
' FROM dual) PER_ALL_PEOPLE_F_S';
g_dbi_dim_data(l_i).source_object := '(SELECT /*+ parallel (s) */ TO_CHAR(s.person_id) USER_CODE,'||
' to_char(s.person_id) CODE,s.effective_start_date, s.effective_end_date'||
' FROM per_all_people_f s'||
' WHERE trunc(sysdate) BETWEEN effective_start_date AND effective_end_date'||
' ) PER_ALL_PEOPLE_F_S';
g_dbi_dim_data(l_i).source_object := '(SELECT /*+ parallel (s) */ TO_CHAR(s.person_id) USER_CODE,'||
' to_char(s.person_id) CODE,s.effective_start_date, s.effective_end_date'||
' FROM per_all_people_f s'||
' WHERE (current_employee_flag = ''Y'' OR current_npw_flag = ''Y'') AND'||
' trunc(sysdate) BETWEEN effective_start_date AND effective_end_date AND'||
' EXISTS (SELECT -1 FROM per_assignments_f asg, per_people_f peo2'||
' WHERE s.person_id = asg.supervisor_id AND asg.person_id = peo2.person_id AND'||
' trunc(sysdate) BETWEEN peo2.effective_start_date AND peo2.effective_end_date AND'||
' trunc(sysdate) BETWEEN asg.effective_start_date AND asg.effective_end_date AND'||
' (peo2.current_employee_flag = ''Y'' OR peo2.current_npw_flag = ''Y''))'||
' UNION ALL SELECT TO_CHAR(-1) USER_CODE, TO_CHAR(-1) CODE,'||
' hr_general.start_of_time effective_start_date,'||
' hr_general.end_of_time effective_end_date'||
' FROM dual) PER_ALL_PEOPLE_F_S';
g_dbi_dim_data(l_i).source_object := '(SELECT /*+ parallel (s) */ TO_CHAR(s.position_id) USER_CODE,'||
' to_char(s.position_id) CODE,s.date_effective effective_start_date,'||
' NVL(s.date_end, hr_general.end_of_time) effective_end_date'||
' FROM per_positions s'||
' UNION ALL SELECT TO_CHAR(-1) USER_CODE, TO_CHAR(-1) CODE,'||
' hr_general.start_of_time effective_start_date,'||
' hr_general.end_of_time effective_end_date'||
' FROM dual) PER_POSITIONS_S';
g_dbi_dim_data(l_i).source_object := '(SELECT /*+ parallel (s) */ TO_CHAR(s.position_id) USER_CODE,'||
' to_char(s.position_id) CODE,s.date_effective effective_start_date,'||
' NVL(s.date_end, hr_general.end_of_time) effective_end_date'||
' FROM per_positions s'||
' WHERE TRUNC(sysdate) <= NVL(s.date_end, hr_general.end_of_time)'||
' UNION ALL SELECT TO_CHAR(-1) USER_CODE, TO_CHAR(-1) CODE,'||
' hr_general.start_of_time effective_start_date,'||
' hr_general.end_of_time effective_end_date'||
' FROM dual) PER_POSITIONS_S';
g_dbi_dim_data(l_i).source_object := '(SELECT /*+ parallel (o) parallel (o2) parallel (o3) */'||
' TO_CHAR(o.organization_id) USER_CODE, to_char(o.organization_id) CODE'||
' FROM hr_all_organization_units o, hr_organization_information o2,'||
' hr_organization_information o3'||
' WHERE o.organization_id = o2.organization_id AND'||
' o.organization_id = o3.organization_id (+) AND'||
' o2.org_information_context = ''CLASS'' AND'||
' o3.org_information_context (+) = ''Legal Entity Accounting'' AND'||
' o2.org_information1 = ''HR_LEGAL'' AND'||
' o2.org_information2 = ''Y'') HR_ALL_ORGANIZATION_UNITS_O';
g_dbi_dim_data(l_i).source_object := '(SELECT /*+ parallel (s) */ TO_CHAR(s.co_code) USER_CODE, to_char(s.co_code) CODE'||
' FROM sy_orgn_mst s'||
' WHERE s.orgn_code = s.co_code) SY_ORGN_MST_S';
g_dbi_dim_data(l_i).source_object := '(SELECT /*+ parallel (s) */ TO_CHAR(s.orgn_code) USER_CODE, to_char(s.orgn_code) CODE'||
' FROM sy_orgn_mst s) SY_ORGN_MST_S';
g_dbi_dim_data(l_i).source_object := '(SELECT /*+ parallel (s) */ s.whse_code USER_CODE, s.whse_code CODE'||
' FROM ic_whse_mst s) IC_WHSE_MST_S';
g_dbi_dim_data(l_i).source_object := ' (SELECT DISTINCT USER_CODE, CODE FROM '||
' (SELECT /*+ parallel (s) */ TO_CHAR(s.id) USER_CODE, to_char(s.id) CODE'||
' FROM jtf_rs_dbi_denorm_res_groups s WHERE s.id IS NOT NULL'||
' UNION ALL SELECT TO_CHAR(-1111) USER_CODE, TO_CHAR(-1111) CODE FROM dual'||
' UNION ALL SELECT /*+ parallel (s) */ TO_CHAR(s.id_for_grp_mem) USER_CODE,'||
' to_char(s.id_for_grp_mem) CODE'||
' FROM jtf_rs_dbi_denorm_res_groups s WHERE s.id_for_grp_mem IS NOT NULL'||
' UNION ALL SELECT /*+ parallel (s) */ TO_CHAR(s.group_id) USER_CODE,'||
' to_char(s.group_id) CODE FROM jtf_rs_groups_denorm s WHERE s.group_id IS NOT NULL)'||
' ) JTF_ORG_INTERACTION_CENTER_S';
g_dbi_dim_data(l_i).source_object := '(SELECT /*+ parallel (s) */ s.id USER_CODE, s.id CODE'||
' FROM eni_oltp_item_star s'||
' WHERE s.master_id is null) ENI_ITEM_S';
| FUNCTION Insert_Children_Denorm_Table
+============================================================================*/
FUNCTION Insert_Children_Denorm_Table(
x_parent_id IN number,
x_ids IN BSC_UPDATE_UTIL.t_array_of_number,
x_num_ids IN NUMBER,
x_level IN NUMBER,
x_denorm_table IN VARCHAR2,
x_child_col IN VARCHAR2,
x_parent_col IN VARCHAR2,
x_parent_level_col IN VARCHAR2,
x_denorm_src_object IN VARCHAR2,
x_child_col_src IN VARCHAR2,
x_parent_col_src IN VARCHAR2,
x_src_condition IN VARCHAR2
) RETURN BOOLEAN IS
l_sql VARCHAR2(32000);
l_child_ids BSC_UPDATE_UTIL.t_array_of_number;
l_sql := 'SELECT DISTINCT '||x_child_col_src||
' FROM '||x_denorm_src_object||
' WHERE ';
l_sql := 'INSERT INTO '||x_denorm_table||' ('||
x_parent_col||', '||x_child_col||', '||x_parent_level_col||
') VALUES (:1, :2, :3)';
IF NOT Insert_Children_Denorm_Table(x_parent_id => x_parent_id,
x_ids => l_child_ids,
x_num_ids => l_num_child_ids,
x_level => x_level,
x_denorm_table => x_denorm_table,
x_child_col => x_child_col,
x_parent_col => x_parent_col,
x_parent_level_col => x_parent_level_col,
x_denorm_src_object => x_denorm_src_object,
x_child_col_src => x_child_col_src,
x_parent_col_src => x_parent_col_src,
x_src_condition => x_src_condition) THEN
RETURN FALSE;
x_source => 'BSC_UPDATE_DIM.Insert_Children_Denorm_Table');
x_source => 'BSC_UPDATE_DIM.Insert_Children_Denorm_Table');
END Insert_Children_Denorm_Table;
select count(d.dim_level_id)
into h_count
from bsc_sys_dim_levels_b d, bsc_sys_dim_level_rels r
where d.dim_level_id = r.dim_level_id and
d.level_table_name = x_dim_table and
r.parent_dim_level_id = r.dim_level_id;
| FUNCTION Insert_AW_Delete_Value
+============================================================================*/
PROCEDURE Insert_AW_Delete_Value(
x_dim_table IN VARCHAR2,
x_delete_value IN VARCHAR2
) IS
h_sql VARCHAR2(32000);
l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_varchar2;
h_sql := 'INSERT INTO BSC_AW_DIM_DELETE (DIM_LEVEL, DELETE_VALUE)'||
' VALUES (:1,:2)';
l_bind_vars_values.delete;
l_bind_vars_values(2) := x_delete_value;
BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,2);
END Insert_AW_Delete_Value;
h_dim_level_list.delete;
h_deleted_codes BSC_UPDATE_UTIL.t_array_of_number;
h_deleted_codes1 BSC_UPDATE_UTIL.t_array_of_varchar2;
h_deleted_codes2 BSC_UPDATE_UTIL.t_array_of_varchar2;
h_num_deleted_codes NUMBER;
h_parent_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
h_parent_keys BSC_UPDATE_UTIL.t_array_of_varchar2;
h_aux_fields BSC_UPDATE_UTIL.t_array_of_varchar2;
h_installed_languages BSC_UPDATE_UTIL.t_array_of_varchar2;
h_p_insert VARCHAR2(32700);
h_p_select VARCHAR2(32700);
h_aux_insert VARCHAR2(32700);
h_aux_select VARCHAR2(32700);
l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_varchar2;
h_num_deleted_codes := 0;
SELECT generation_type
INTO h_loading_mode
FROM bsc_db_tables
WHERE table_name = x_input_table;
h_sql := 'SELECT DISTINCT code FROM '||x_dim_table||
' WHERE code > :1 AND user_code NOT IN ('||
' SELECT user_code FROM '||x_input_table||')';
h_num_deleted_codes := h_num_deleted_codes + 1;
h_deleted_codes(h_num_deleted_codes) := h_code;
IF h_num_deleted_codes > 0 THEN
IF NOT Delete_Codes_Cascade(x_dim_table, h_deleted_codes, h_num_deleted_codes) THEN
RAISE e_unexpected_error;
h_sql := 'SELECT DISTINCT '||h_parent_keys(1)||', '||h_parent_keys(2)||
' FROM '||x_dim_table||
' WHERE ('||h_parent_keys(1)||', '||h_parent_keys(2)||') NOT IN ('||
' SELECT d1.code, d2.code'||
' FROM '||x_input_table||' i, '||h_parent_tables(1)||' d1, '||h_parent_tables(2)||' d2'||
' WHERE i.'||h_parent_keys(1)||'_usr = d1.user_code'||
' AND i.'||h_parent_keys(2)||'_usr = d2.user_code)';
h_num_deleted_codes := h_num_deleted_codes + 1;
h_deleted_codes1(h_num_deleted_codes) := h_code1;
h_deleted_codes2(h_num_deleted_codes) := h_code2;
IF h_num_deleted_codes > 0 THEN
IF NOT Delete_Codes_CascadeMN(x_dim_table,
h_parent_keys(1),
h_parent_keys(2),
h_deleted_codes1,
h_deleted_codes2,
h_num_deleted_codes) THEN
RAISE e_unexpected_error;
h_sql := 'SELECT DISTINCT code FROM '||x_dim_table||
' WHERE user_code IN ('||
' SELECT user_code FROM '||x_input_table||')';
h_sql := 'UPDATE '||x_dim_table||' d'||
' SET name = ('||
' SELECT name'||
' FROM '||x_input_table||' i'||
' WHERE i.user_code = d.user_code),'||
' source_lang = :1'||
' WHERE user_code IN (SELECT user_code FROM '||x_input_table||')'||
' AND (language = :2 OR source_lang = :3)';
l_bind_vars_values.delete;
BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,3);
h_sql := 'SELECT user_code FROM '||x_input_table||' i'||
' WHERE '||h_parent_keys(h_i)||'_usr <> ('||
' SELECT DISTINCT '||h_parent_keys(h_i)||'_usr'||
' FROM '||x_dim_table||' d'||
' WHERE d.user_code = i.user_code)';
h_sql := 'UPDATE '||x_dim_table||' d'||
' SET ('||h_parent_keys(h_i)||', '||h_parent_keys(h_i)||'_usr) = ('||
' SELECT DISTINCT p.code, i.'||h_parent_keys(h_i)||'_usr'||
' FROM '||x_input_table||' i, '||h_parent_tables(h_i)||' p'||
' WHERE d.user_code = i.user_code'||
' AND i.'||h_parent_keys(h_i)||'_usr = p.user_code)'||
' WHERE d.user_code IN (SELECT user_code FROM '||x_input_table||')';
BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
h_sql := 'UPDATE '||x_dim_table||' d'||
' SET '||h_aux_fields(h_i)||' = ('||
' SELECT i.'||h_aux_fields(h_i)||
' FROM '||x_input_table||' i'||
' WHERE d.user_code = i.user_code)'||
' WHERE d.user_code IN (SELECT user_code FROM '||x_input_table||')';
BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
h_sql := 'DELETE FROM '||x_input_table||
' WHERE user_code IN (SELECT user_code FROM '||x_dim_table||')';
BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
h_num_installed_languages := BSC_UPDATE_UTIL.Get_Installed_Languages(h_installed_languages);
h_p_insert := NULL;
h_p_select := NULL;
h_p_insert := h_p_insert||', '||h_parent_keys(h_i)||', '||h_parent_keys(h_i)||'_USR';
h_p_select := h_p_select||', p'||h_i||'.code, i.'||h_parent_keys(h_i)||'_USR';
h_aux_insert := NULL;
h_aux_select := NULL;
h_aux_insert := h_aux_insert||', '||h_aux_fields(h_i);
h_aux_select := h_aux_select||', i.'||h_aux_fields(h_i);
h_sql := 'SELECT DISTINCT user_code FROM '||x_input_table;
h_sql := 'INSERT INTO '||x_dim_table||' ('||
' code, user_code, name'||h_p_insert||h_aux_insert||', language, source_lang)'||
' SELECT :1, i.user_code, i.name'||h_p_select||h_aux_select||
', :2, :3'||
' FROM '||x_input_table||' i'||h_p_from||
' WHERE i.user_code = :4 '||h_p_where;
h_sql := 'DELETE FROM '||x_input_table||
' WHERE ('||h_parent_keys(1)||'_usr, '||h_parent_keys(2)||'_usr) IN ('||
' SELECT d1.user_code, d2.user_code'||
' FROM '||x_dim_table||' d, '||h_parent_tables(1)||' d1, '||h_parent_tables(2)||' d2'||
' WHERE d.'||h_parent_keys(1)||' = d1.code'||
' AND d.'||h_parent_keys(2)||' = d2.code)';
BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
h_sql := 'INSERT INTO '||x_dim_table||' ('||h_parent_keys(1)||', '||h_parent_keys(2)||')'||
' SELECT p1.code, p2.code'||
' FROM '||x_input_table||' i, '||h_parent_tables(1)||' p1, '||h_parent_tables(2)||' p2'||
' WHERE i.'||h_parent_keys(1)||'_USR = p1.user_code AND p1.language = :1 AND'||
' i.'||h_parent_keys(2)||'_USR = p2.user_code AND p2.language = :2';
l_bind_vars_values.delete;
BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,2);
BSC_UPDATE_UTIL.Truncate_Table(x_input_table);
IF h_dim_table_type = BSC_UPDATE_DIM.DIM_TABLE_TYPE_1N THEN
IF Dimension_Used_In_AW_Kpi(x_dim_table) THEN
h_dim_level_list.delete;
UPDATE BSC_KPIS_B K
SET PROTOTYPE_FLAG = 6,
LAST_UPDATED_BY = BSC_APPS.fnd_global_user_id,
LAST_UPDATE_DATE = SYSDATE
WHERE INDICATOR IN (SELECT D.INDICATOR
FROM BSC_KPI_DIM_LEVELS_B D
WHERE K.INDICATOR = D.INDICATOR AND
(D.LEVEL_TABLE_NAME = x_dim_table OR
D.TABLE_RELATION = x_dim_table)) AND
PROTOTYPE_FLAG in (0, 6, 7);
UPDATE bsc_kpi_analysis_measures_b k
SET prototype_flag = BSC_DESIGNER_PVT.C_COLOR_CHANGE -- 7
WHERE indicator IN (SELECT d.indicator
FROM bsc_kpi_dim_levels_b d
WHERE k.indicator = d.indicator
AND (d.level_table_name = x_dim_table
OR d.table_relation = x_dim_table));
BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_DIMTABLE_UPDATE_FAILED'),
x_source => 'BSC_UPDATE_BASE.Load_Dim_Table');
x_source => 'BSC_UPDATE_BASE.Load_Dim_Table');
h_dim_level_list.delete;
l_objs_to_check BSC_UPDATE_UTIL.t_array_of_varchar2;
l_sql := 'SELECT refresh_end_time'||
' FROM bsc_object_refresh_log'||
' WHERE object_name = :1 AND object_type = :2';
l_num_objs_to_check := BSC_UPDATE_UTIL.Decompose_Varchar2_List(x_source_to_check, l_objs_to_check, ',');
l_sql := 'SELECT MAX(last_update_date)'||
' FROM '||l_objs_to_check(l_i);
l_dbi_dim_data BSC_UPDATE_DIM.t_dbi_dim_data;
l_lst_select VARCHAR2(8000);
l_lst_select_src VARCHAR2(8000);
l_lst_select_tmp VARCHAR2(8000);
l_lst_select_tmp_t VARCHAR2(8000);
l_parent_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
l_src_parent_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
SELECT level_table_name
INTO l_level_table_name
FROM bsc_sys_dim_levels_b
WHERE short_name = x_dim_short_name;
l_sql := 'UPDATE bsc_object_refresh_log'||
' SET refresh_start_time = SYSDATE, refresh_end_time = NULL'||
' WHERE object_name = :1 AND object_type = :2';
l_sql := 'INSERT INTO bsc_object_refresh_log'||
' (object_name, object_type, refresh_start_time, refresh_end_time)'||
' VALUES (:1, :2, SYSDATE, NULL)';
l_lst_select := 'USER_CODE,CODE';
l_lst_select_src := l_user_code||','||l_code;
l_lst_select_src := l_user_code||',decode(to_char('||l_user_code||'),''0'',''-99999999'','||
'to_char('||l_user_code||')) '||l_code;
l_lst_select_tmp := 'USER_CODE,CODE';
l_lst_select_tmp_t := 'T.USER_CODE,T.CODE';
l_lst_select := l_lst_select||', '||l_parent_columns(l_i);
l_lst_select_src := l_lst_select_src||', '||l_src_parent_columns(l_i);
l_lst_select_tmp := l_lst_select_tmp||', PARENT_CODE'||l_i;
l_lst_select_tmp_t := l_lst_select_tmp_t||', T.PARENT_CODE'||l_i;
l_lst_select := l_lst_select||', EFFECTIVE_START_DATE, EFFECTIVE_END_DATE';
l_lst_select_src := l_lst_select_src||', EFFECTIVE_START_DATE, EFFECTIVE_END_DATE';
l_lst_select_tmp := l_lst_select_tmp||', EFFECTIVE_START_DATE, EFFECTIVE_END_DATE';
l_lst_select_tmp_t := l_lst_select_tmp_t||', T.EFFECTIVE_START_DATE, T.EFFECTIVE_END_DATE';
BSC_UPDATE_UTIL.Execute_Immediate('delete BSC_TMP_DBI_DIM');
BSC_UPDATE_UTIL.Execute_Immediate('delete BSC_TMP_DBI_DIM_ADD');
BSC_UPDATE_UTIL.Execute_Immediate('delete BSC_TMP_DBI_DIM_DEL');
l_sql := 'INSERT /*+ parallel(BSC_TMP_DBI_DIM) */'||
' INTO BSC_TMP_DBI_DIM ('||l_lst_select_tmp||')'||
' SELECT /*+ parallel('||l_source_object_alias||') */ DISTINCT '||l_lst_select_src||
' FROM '||l_source_object;
BSC_UPDATE_UTIL.Execute_Immediate(l_sql);
l_sql := 'INSERT /*+ parallel(BSC_TMP_DBI_DIM_ADD) */'||
' INTO BSC_TMP_DBI_DIM_ADD (USER_CODE)'||
' SELECT USER_CODE'||
' FROM BSC_TMP_DBI_DIM MINUS select USER_CODE from '||l_dbi_dim_data.table_name;
BSC_UPDATE_UTIL.Execute_Immediate(l_sql);
l_sql := 'INSERT /*+ parallel(BSC_TMP_DBI_DIM_DEL) */'||
' INTO BSC_TMP_DBI_DIM_DEL (USER_CODE)'||
' SELECT USER_CODE'||
' FROM '||l_dbi_dim_data.table_name||' MINUS select USER_CODE from BSC_TMP_DBI_DIM';
BSC_UPDATE_UTIL.Execute_Immediate(l_sql);
l_sql := 'INSERT /*+ parallel(BSC_AW_DIM_DELETE) */'||
' INTO BSC_AW_DIM_DELETE (DIM_LEVEL, DELETE_VALUE)'||
' SELECT :1, CODE'||
' FROM '||l_dbi_dim_data.table_name||' MINUS SELECT :2, CODE FROM BSC_TMP_DBI_DIM';
l_sql := 'UPDATE '||l_dbi_dim_data.table_name||' B'||
' SET ('||l_lst_set||') = ('||
' SELECT '||l_lst_set_tmp||
' FROM BSC_TMP_DBI_DIM T'||
' WHERE T.USER_CODE = B.USER_CODE'||
' )'||
' WHERE EXISTS ('||
' SELECT T.USER_CODE'||
' FROM BSC_TMP_DBI_DIM T'||
' WHERE T.USER_CODE = B.USER_CODE AND ('||l_cond_parents||')';
BSC_UPDATE_UTIL.Execute_Immediate(l_sql);
l_sql := 'INSERT /*+ parallel('||l_dbi_dim_data.table_name||') */'||
' INTO '||l_dbi_dim_data.table_name||' ('||l_lst_select||')'||
' SELECT '||l_lst_select_tmp_t||
' FROM BSC_TMP_DBI_DIM T, BSC_TMP_DBI_DIM_ADD N'||
' WHERE T.USER_CODE = N.USER_CODE';
BSC_UPDATE_UTIL.Execute_Immediate(l_sql);
l_sql := 'DELETE FROM '||l_dbi_dim_data.table_name||
' WHERE USER_CODE IN (SELECT USER_CODE FROM BSC_TMP_DBI_DIM_DEL)';
BSC_UPDATE_UTIL.Execute_Immediate(l_sql);
l_sql := 'UPDATE bsc_object_refresh_log'||
' SET refresh_end_time = SYSDATE'||
' WHERE object_name = :1 AND object_type = :2';
l_dim_level_list.delete;
l_sql := 'INSERT /*+ parallel(BSC_AW_DIM_DELETE) */'||
' INTO BSC_AW_DIM_DELETE (DIM_LEVEL, DELETE_VALUE)'||
' SELECT DIM_LEVEL, CODE'||
' FROM BSC_AW_DIM_DATA'||
' WHERE DIM_LEVEL = :1'||
' MINUS '||
' SELECT :2, TO_CHAR(CODE) FROM '||l_level_table_name;
l_sql := 'delete from bsc_aw_dim_delete'||
' where dim_level = :1 and delete_value = :2';
l_dim_level_list.delete;
BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_UNEXPECTED_ERROR'),
x_source => 'BSC_UPDATE_DIM.Refresh_Dbi_Dimension_Table');
x_source => 'BSC_UPDATE_DIM.Refresh_Dbi_Dimension_Table');
IF NOT BSC_UPDATE_LOCK.Lock_DBI_Dimension(x_dim_short_name) THEN
RAISE e_could_not_get_lock;
BSC_UPDATE_LOG.Write_Errors_To_Log;
ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UNEXPECTED_ERROR');
BSC_UPDATE_LOG.Write_Errors_To_Log;
x_source => 'BSC_UPDATE_DIM.Refresh_Dbi_Dimension',
x_mode => 'I');
BSC_UPDATE_LOG.Write_Errors_To_Log;
ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UNEXPECTED_ERROR');
select level_view_name, level_pk_col
into l_level_view_name, l_level_pk_col
from bsc_sys_dim_levels_b
where level_table_name = x_level_table_name;
l_sql := 'select distinct code'||
' from '||l_level_view_name||
' where '||l_level_pk_col||' is null';
denorm_child.delete;
denorm_parent.delete;
dc_level.delete;
dp_level.delete;
norm_child.delete;
norm_parent.delete;
l_sql := 'select '||l_level_pk_col||', code, level'||
' from '||l_level_view_name||
' start with '||l_level_pk_col||' = :1'||
' connect by prior code = '||l_level_pk_col;
prev_p.delete;
prev_p.delete(m);
prev_p_level.delete(m);
IF BSC_UPDATE_UTIL.Table_Has_Any_Row(x_denorm_table_name) THEN
-- Incremental load
BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_DNT');
execute immediate 'insert into bsc_tmp_dnt (parent_code, code, child_level, parent_level)'||
' values (:1, :2, :3, :4)'
using denorm_parent(i), denorm_child(i), dc_level(i), dp_level(i);
l_sql := 'delete from '||x_denorm_table_name||
' where (parent_code, code, child_level, parent_level) in ('||
' select parent_code, code, child_level, parent_level'||
' from '||x_denorm_table_name||
' minus'||
' select parent_code, code, child_level, parent_level'||
' from bsc_tmp_dnt'||
' )';
l_sql := 'insert into '||x_denorm_table_name||' (parent_code, code, child_level, parent_level)'||
' select parent_code, code, child_level, parent_level'||
' from bsc_tmp_dnt'||
' minus'||
' select parent_code, code, child_level, parent_level'||
' from '||x_denorm_table_name;
execute immediate 'insert into '||x_denorm_table_name||
' (parent_code, code, child_level, parent_level)'||
' values (:1, :2, :3, :4)'
using denorm_parent(i), denorm_child(i), dc_level(i), dp_level(i);
x_source => 'BSC_UPDATE_DIM.Refresh_Denorm_Table');
x_mod_dimensions IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
x_num_mod_dimensions IN OUT NOCOPY NUMBER,
x_checked_dimensions IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
x_num_checked_dimensions IN OUT NOCOPY NUMBER
) RETURN BOOLEAN IS
e_unexpected_error EXCEPTION;
h_relation_cols BSC_UPDATE_UTIL.t_array_of_varchar2;
h_deleted_records BSC_UPDATE_UTIL.t_array_of_number;
h_num_deleted_records NUMBER;
h_child_dimensions BSC_UPDATE_UTIL.t_array_of_varchar2;
h_parent_dimensions BSC_UPDATE_UTIL.t_array_of_varchar2;
h_num_deleted_records := 0;
IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(x_dimension_table,
x_checked_dimensions,
x_num_checked_dimensions) THEN
BSC_UPDATE_LOG.Write_Line_Log(x_dimension_table, BSC_UPDATE_LOG.OUTPUT);
IF NOT BSC_UPDATE_UTIL.Drop_Table('BSC_TMP_DIMENSION') THEN
RAISE e_unexpected_error;
h_lst_relation_cols := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(h_relation_cols, h_num_relation_cols);
h_lst_relation_cols_desc := BSC_UPDATE_UTIL.Make_Lst_Description(h_relation_cols, h_num_relation_cols, 'NUMBER');
h_sql := 'INSERT INTO BSC_TMP_DIMENSION (CODE'||h_lst_relation_cols||')'||
' SELECT CODE'||h_lst_relation_cols||
' FROM '||x_dimension_table;
BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
IF NOT BSC_UPDATE_UTIL.Create_Unique_Index('BSC_TMP_DIMENSION',
'BSC_TMP_DIMENSION_U1',
'CODE',
BSC_APPS.other_index_tbs_type) THEN
RAISE e_unexpected_error;
h_num_deleted_records := Get_Deleted_Records(x_dimension_table, 'BSC_TMP_DIMENSION', h_deleted_records);
IF h_num_deleted_records > 0 THEN
-- Delete from all system tables rows for deleted values
-- h_condition := BSC_UPDATE_UTIL.Make_Lst_Cond_Number(h_level_pk_col, h_deleted_records, h_num_deleted_records, 'OR');
FOR h_i IN 1..h_num_deleted_records LOOP
BSC_APPS.Add_Value_Big_In_Cond(1, h_deleted_records(h_i));
IF NOT Delete_Key_Values_In_Tables(h_level_pk_col, h_condition) THEN
RAISE e_unexpected_error;
IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(x_dimension_table,
x_mod_dimensions,
x_num_mod_dimensions) THEN
-- Add the dimension to the array x_checked dimensions
x_num_mod_dimensions := x_num_mod_dimensions + 1;
IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(x_dimension_table,
x_mod_dimensions,
x_num_mod_dimensions) THEN
-- Add the dimension to the array x_checked dimensions
x_num_mod_dimensions := x_num_mod_dimensions + 1;
IF NOT BSC_UPDATE_UTIL.Drop_Table('BSC_TMP_DIMENSION') THEN
RAISE e_unexpected_error;
BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_REFRESH_EDW_DIM_FAILED'),
x_source => 'BSC_UPDATE_DIM.Refresh_EDW_Dimension');
x_source => 'BSC_UPDATE_DIM.Refresh_EDW_Dimension');
x_dimension_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
x_num_dimension_tables IN NUMBER
) RETURN BOOLEAN IS
e_unexpected_error EXCEPTION;
h_mod_dimensions BSC_UPDATE_UTIL.t_array_of_varchar2;
h_checked_dimensions BSC_UPDATE_UTIL.t_array_of_varchar2;
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_REFRESHING_EDW_DIM'), BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_RECALC_KPI_DIMTABLES'), BSC_UPDATE_LOG.OUTPUT);
UPDATE BSC_KPIS_B K
SET PROTOTYPE_FLAG = 6,
LAST_UPDATED_BY = BSC_APPS.fnd_global_user_id,
LAST_UPDATE_DATE = SYSDATE
WHERE INDICATOR IN (SELECT D.INDICATOR
FROM BSC_KPI_DIM_LEVELS_B D
WHERE K.INDICATOR = D.INDICATOR AND
D.LEVEL_TABLE_NAME = h_mod_dimensions(h_i)) AND
PROTOTYPE_FLAG in (0, 6, 7);
UPDATE bsc_kpi_analysis_measures_b k
SET prototype_flag = BSC_DESIGNER_PVT.C_COLOR_CHANGE -- 7
WHERE indicator IN (SELECT d.indicator
FROM bsc_kpi_dim_levels_b d
WHERE k.indicator = d.indicator
AND d.level_table_name = h_mod_dimensions(h_i));
BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_REFRESH_EDW_DIM_FAILED'),
x_source => 'BSC_UPDATE_DIM.Refresh_EDW_Dimensions');
x_source => 'BSC_UPDATE_DIM.Refresh_EDW_Dimensions');
l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_varchar2;
SELECT DISTINCT L.DIM_LEVEL_ID, D.LEVEL_TABLE_NAME
FROM BSC_SYS_COM_DIM_LEVELS L, BSC_SYS_DIM_LEVELS_B D
WHERE L.DIM_LEVEL_ID = D.DIM_LEVEL_ID;
h_sql := 'SELECT DISTINCT L.DIM_LEVEL_ID, D.LEVEL_TABLE_NAME'||
' FROM BSC_SYS_COM_DIM_LEVELS L, BSC_SYS_DIM_LEVELS_B D'||
' WHERE L.DIM_LEVEL_ID = D.DIM_LEVEL_ID';
h_sql := 'DELETE FROM BSC_USER_LIST_ACCESS'||
' WHERE (RESPONSIBILITY_ID, TAB_ID) IN ('||
' SELECT LA.RESPONSIBILITY_ID, LA.TAB_ID'||
' FROM BSC_SYS_COM_DIM_LEVELS L, BSC_USER_LIST_ACCESS LA'||
' WHERE L.TAB_ID = LA.TAB_ID'||
' AND L.DIM_LEVEL_INDEX = LA.DIM_LEVEL_INDEX'||
' AND L.DIM_LEVEL_ID = :1'||
' AND LA.DIM_LEVEL_VALUE <> ''0'''||
' AND LA.DIM_LEVEL_VALUE NOT IN ('||
' SELECT CODE FROM '||h_level_table_name||'))';
l_bind_vars_values.delete;
BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,1);
x_source => 'BSC_UPDATE_DIM.Sync_Sec_Assigments');
h_parent_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
h_parent_keys BSC_UPDATE_UTIL.t_array_of_varchar2;
h_aux_fields BSC_UPDATE_UTIL.t_array_of_varchar2;
l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_varchar2;
h_sql := 'DELETE FROM bsc_db_validation'||
' WHERE input_table_name = :1';
DELETE FROM bsc_db_validation
WHERE input_table_name = x_input_table;
h_sql := 'SELECT generation_type'||
' FROM bsc_db_tables'||
' WHERE table_name = :1';
SELECT generation_type
INTO h_loading_mode
FROM bsc_db_tables
WHERE table_name = x_input_table;
h_null := BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'NULL');
h_sql := 'INSERT INTO bsc_db_validation (input_table_name, column_name, invalid_code)
SELECT DISTINCT :1, ''USER_CODE'',
NVL(USER_CODE,:2)
FROM '||x_input_table||'
WHERE NVL(USER_CODE, ''0'') = ''0''';
l_bind_vars_values.delete;
BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,2);
h_sql := 'INSERT INTO bsc_db_validation (input_table_name, column_name, invalid_code)
SELECT DISTINCT :1, ''NAME'', :2
FROM '||x_input_table||'
WHERE NAME IS NULL';
l_bind_vars_values.delete;
BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,2);
h_sql := 'INSERT INTO bsc_db_validation (input_table_name, column_name, invalid_code)
SELECT DISTINCT :1, ''NAME'', name
FROM '||x_input_table||'
WHERE name IS NOT NULL
GROUP BY name
HAVING count(*) > 1';
l_bind_vars_values.delete;
BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,1);
h_sql := 'INSERT INTO bsc_db_validation (input_table_name, column_name, invalid_code)
SELECT DISTINCT :1, :2, name
FROM (SELECT user_code, name
FROM '||x_input_table||'
UNION
SELECT d.user_code, d.name
FROM '||x_dim_table||' d, '||x_input_table||' i
WHERE d.user_code = i.user_code (+) AND i.user_code IS NULL AND
d.language = :3 AND d.source_lang = :4)
WHERE name IS NOT NULL
GROUP BY name
HAVING count(*) > 1';
l_bind_vars_values.delete;
BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,4);
h_sql := 'INSERT INTO bsc_db_validation (input_table_name, column_name, invalid_code)
SELECT DISTINCT :1, :2,
NVL(TO_CHAR('||h_parent_keys(h_i)||'_USR), :3)
FROM '||x_input_table||'
WHERE NVL(TO_CHAR('||h_parent_keys(h_i)||'_USR), ''0'') NOT IN (
SELECT TO_CHAR(USER_CODE) FROM '||h_parent_tables(h_i)||' WHERE TO_CHAR(USER_CODE) <> ''0'')';
l_bind_vars_values.delete;
BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,3);
h_sql := 'INSERT INTO bsc_db_validation (input_table_name, column_name, invalid_code)
SELECT DISTINCT :1, :2, :3
FROM '||x_input_table||'
WHERE '||h_aux_fields(h_i)||' IS NULL';
l_bind_vars_values.delete;
BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,3);
h_sql := 'SELECT COUNT(*) FROM BSC_DB_VALIDATION'||
' WHERE ROWNUM < :1 AND INPUT_TABLE_NAME = :2';
SELECT COUNT(*)
INTO h_num_rows
FROM BSC_DB_VALIDATION
WHERE ROWNUM < 2 AND INPUT_TABLE_NAME = x_input_table;
BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_ITABLE_VALID_FAILED'),
x_source => 'BSC_UPDATE_DIM.Validate_Input_Table');
x_source => 'BSC_UPDATE_DIM.Validate_Input_Table');
c_indicators_sql VARCHAR2(2000) := 'SELECT d.indicator, d.level_table_name, d.default_key_value'||
' FROM bsc_kpi_dim_levels_b d, bsc_kpis_b k'||
' WHERE d.indicator = k.indicator AND'||
' d.default_key_value IS NOT NULL';
SELECT d.indicator, d.level_table_name, d.default_key_value
FROM bsc_kpi_dim_levels_b d, bsc_kpis_b k
WHERE d.indicator = k.indicator AND
d.default_key_value IS NOT NULL;
h_sql := 'SELECT code FROM '||h_level_table_name||
' WHERE code = :1';
BSC_UPDATE_LOG.Write_Line_Log('', BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.Write_Line_Log('+---------------------------------------------------------------------------+',
BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_DFT_DIMVALUE_MISSING'), BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.Write_Line_Log('+---------------------------------------------------------------------------+',
BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.Write_Line_Log(RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_BACKEND', 'KPI_CODE'), C_INDICATOR_W)||
RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'DIMENSION_TABLE_NAME'), C_DIMENSION_TABLE_W)||
RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_KPIDESIGNER', 'DEFAULT_VALUE'), C_DEFAULT_VALUE_W),
BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.Write_Line_Log(RPAD(TO_CHAR(h_indicator), C_INDICATOR_W)||
RPAD(h_level_table_name, C_DIMENSION_TABLE_W)||
RPAD(h_default_key_value, C_DEFAULT_VALUE_W),
BSC_UPDATE_LOG.OUTPUT);
BSC_UPDATE_LOG.Write_Line_Log('', BSC_UPDATE_LOG.OUTPUT);
x_source => 'BSC_UPDATE_DIM.WriteRemovedKeyItems');