[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE delete_bsc_measures_from_pmf
IS
-- part of bug#3436393: the previous query was not getting all
-- the bsc datasets from pmf repository. It was joining with bsc_sys_measures
-- but in case of datasets that are formulas between measures
-- they were not fetched. We need to join is with bsc_sys_datasets_b
CURSOR c_bsc_measures_in_pmf IS
SELECT indicator_id, measure_id1, i.short_name
FROM bis_indicators i, bsc_sys_datasets_b d
WHERE d.dataset_id = i.dataset_id
AND d.source = 'BSC';
SELECT count(1)
FROM bis_indicators BIS_IND
,bsc_sys_datasets_b BSC_DTS
,bsc_sys_measures BSC_MEAS
WHERE BIS_IND.dataset_id = BSC_DTS.dataset_id
AND BSC_DTS.measure_id1 = BSC_MEAS.measure_id
AND BIS_IND.indicator_id = p_indicator_id;
SELECT nvl(max(dataset_id),0) INTO l_max_ds_id_bis
FROM bis_indicators;
SELECT nvl(max(dataset_id),0) INTO l_max_ds_id_bsc
FROM bsc_sys_datasets_b;
IF (l_count = 0) THEN -- custom KPI is corrupt, delete it from PMF side (should not happen ideally)
l_num_ids := l_num_ids + 1;
BIS_MEASURE_PUB.Delete_Measure(
p_api_version => 1.0
,p_commit => FND_API.G_FALSE
,p_Measure_Rec => l_bis_measure_rec
,x_return_status => l_return_status
,x_error_Tbl => l_error_tbl);
BSC_MESSAGE.Add(x_message => 'Deleted measure: ' || l_arr_short_name(i) || ' : Successfully',
x_source => 'BSCUPGRB.delete_bsc_measures_from_pmf',
x_mode => 'I');
BSC_MESSAGE.Add(x_message => 'Could not delete measure: ' || l_err,
x_source => 'BSCUPGRB.delete_bsc_measures_from_pmf',
x_mode => 'I');
x_source => 'BSCUPGRB.delete_bsc_measures_from_pmf',
x_mode => 'I');
END delete_bsc_measures_from_pmf;
SELECT d.dataset_id, i.name as measure_name, i.description, i.short_name as measure_short_name, i.indicator_id measure_id, i.created_by created_by, i.last_updated_by last_updated_by, i.last_update_login last_update_login
FROM bsc_sys_datasets_vl d, bis_indicators_vl i, bsc_sys_measures m
WHERE i.short_name = m.short_name (+)
and (d.source is null or d.source = 'PMF')
and m.measure_id = d.measure_id1 (+)
and (i.dataset_id is null or d.dataset_id <> i.dataset_id);
l_Dataset_Rec.Bsc_Measure_Last_Update_By := icr.last_updated_by;
l_Dataset_Rec.Bsc_Measure_Last_Update_Login := icr.last_update_login;
l_Dataset_Rec.Bsc_Dataset_Last_Update_By := icr.last_updated_by;
l_Dataset_Rec.Bsc_Dataset_Last_Update_Login := icr.last_update_login;
BSC_DB_MEASURE_COLS_PKG.INSERT_ROW (
x_Measure_Col => l_Measure_Col
, x_Measure_Group_Id => l_Measure_Group_Id
, x_Projection_Id => l_Projection_Id
, x_Measure_Type => l_Measure_Type
, x_Help => l_Measure_Col
);
, x_source => 'BSC_DB_MEASURE_COLS_PKG.INSERT_ROW'
, x_mode => 'I'
);
UPDATE bis_indicators
SET dataset_id = l_Dataset_id
WHERE indicator_id = icr.measure_id;
UPDATE bis_indicators
SET dataset_id = icr.dataset_id
WHERE indicator_id = icr.measure_id;
SELECT d.dataset_id, name, m.measure_id, m.created_by created_by, m.last_updated_by last_updated_by, m.last_update_login last_update_login
FROM bsc_sys_datasets_vl d, bsc_sys_measures m, bisbv_performance_measures pm
WHERE d.measure_id1 = m.measure_id
AND m.short_name is null
AND d.source = 'BSC'
AND d.dataset_id = pm.dataset_id (+)
AND pm.dataset_id is null;
l_measure_rec.Last_Updated_By := cr.last_updated_by;
l_measure_rec.Last_Update_Login := cr.last_update_login;
UPDATE bsc_sys_measures
SET short_name = l_measure_rec.Measure_Short_Name
WHERE measure_id = cr.measure_id;
SELECT d.dataset_id, m.short_name
FROM bsc_sys_datasets_vl d, bsc_sys_measures m, bisbv_performance_measures pm
WHERE d.measure_id1 = m.measure_id
AND m.short_name = pm.measure_short_name (+)
AND m.short_name IS NOT NULL
AND d.source = 'CDS'
AND (pm.dataset_id IS NULL);
UPDATE bis_indicators
SET dataset_id =cd.dataset_id
WHERE short_name =cd.short_name;
SELECT d.dataset_id, name, m.short_name, m.measure_id, pm.measure_short_name, m.created_by created_by, m.last_updated_by last_updated_by, m.last_update_login last_update_login
FROM bsc_sys_datasets_vl d, bsc_sys_measures m, bisbv_performance_measures pm
WHERE d.measure_id1 = m.measure_id
AND m.short_name = pm.measure_short_name (+)
AND m.short_name IS NOT NULL
AND d.source = 'BSC'
AND (pm.dataset_id IS NULL OR pm.dataset_id <> d.dataset_id);
SELECT count(1) INTO l_count
FROM BISBV_PERFORMANCE_MEASURES
WHERE dataset_id = cr.dataset_id;
UPDATE BIS_INDICATORS
SET dataset_id = cr.dataset_id
WHERE short_name = cr.measure_short_name;
l_measure_rec.Last_Updated_By := cr.last_updated_by;
l_measure_rec.Last_Update_Login := cr.last_update_login;
UPDATE BSC_SYS_MEASURES
SET short_name = l_measure_rec.Measure_Short_Name
WHERE measure_id = cr.measure_id;
SELECT COUNT(1)
INTO l_count
FROM BSC_SYS_MEASURES
WHERE SHORT_NAME = p_Short_Name;
PROCEDURE update_short_name_bsc_sys_mes
IS
CURSOR cBscSysMeasures IS
SELECT M.short_name bscShortName
,B.short_name bisShortName
,B.dataset_id
,M.measure_id
FROM bsc_sys_measures M
,bsc_sys_datasets_vl V
,bis_indicators_Vl B
WHERE M.measure_id = V.measure_id1
AND B.dataset_id = V.dataset_id
AND V.SOURCE = 'BSC'
AND M.short_name IS NULL;
SAVEPOINT SP_UPDATE_SHORT_NAME;
UPDATE bsc_sys_measures
SET short_name = l_Bsc_Measure_Short_Name
WHERE measure_id = cBSCM.measure_id;
ROLLBACK TO SP_UPDATE_SHORT_NAME;
, x_source => 'Update short names in bsc_sys_measures'
, x_mode => 'I'
);
END update_short_name_bsc_sys_mes;
delete_bsc_measures_from_pmf;
update_short_name_bsc_sys_mes;
select L.language_code
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and language_code <> userenv('LANG');
select name, help, source_lang into l_name, l_help, l_source_lang
from bsc_sys_datasets_tl
where dataset_id = p_dataset_id
and language = cr.language_code;
select L.language_code
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and language_code <> userenv('LANG');
SELECT name INTO l_measure_name
FROM bis_indicators_tl
WHERE indicator_id = p_indicator_id
AND language = userenv('LANG');
select name, description, source_lang
into l_name, l_description, l_source_lang
from bis_indicators_tl
where indicator_id = p_indicator_id
and language = cr.language_code;
select i.indicator_id, itl.language, itl.source_lang, i.short_name, d.source, d.dataset_id,
itl.name indicator_name, dtl.name dataset_name,
itl.description, dtl.help
from bis_indicators i, bis_indicators_tl itl, bsc_sys_datasets_b d, bsc_sys_datasets_tl dtl
where i.indicator_id = itl.indicator_id
and i.dataset_id = dtl.dataset_id
and d.dataset_id = dtl.dataset_id
and itl.language = dtl.language
and itl.name <> dtl.name;
SELECT name INTO l_measure_name
FROM bis_indicators_tl
WHERE indicator_id = scr.indicator_id
AND language = userenv('LANG');
select count(indicator_id) into l_count
from bis_indicators_tl
where upper(name) = upper(l_pmf_disp_name)
and language = p_language;
select count(indicator_id) into l_count
from bis_indicators_tl
where upper(name) = upper(l_pmf_disp_name)
and language = p_language;
SELECT Short_Name
, Dimension_Id
, Level_Values_View_Name
, Where_Clause
, Created_By
, Last_Updated_By
, Last_Update_Date
, Last_Update_Login
, Name
, Description
, Source
, Comparison_Label_Code
, Attribute_Code
, Application_Id
FROM BIS_LEVELS_VL;
SELECT Dim_Level_Id
, Name
, Help
, Total_Disp_Name
, Comp_Disp_Name
, Level_Table_Name
, Table_Type
, Level_Pk_Col
, Abbreviation
, Value_Order_By
, Comp_Order_By
, Custom_Group
, User_Key_Size
, Disp_Key_Size
, Edw_Flag
, Edw_Dim_Id
, Edw_Dim_Level_Id
, Level_View_Name
, Short_Name
, Source
, Created_By
, Last_Updated_By
, Last_Update_Date
, Last_Update_Login
FROM BSC_SYS_DIM_LEVELS_VL;
SELECT level_values_view_name
FROM BIS_LEVELS
WHERE SOURCE = 'OLTP'
AND level_values_view_name NOT LIKE 'BSC_D_%'
AND level_values_view_name IS NOT NULL;
UPDATE BSC_SYS_DIM_LEVELS_B
SET Level_Table_Name = UPPER(Level_Table_Name)
WHERE LEVEL_TABLE_NAME <> UPPER(LEVEL_TABLE_NAME);
UPDATE BSC_KPI_DIM_LEVELS_B
SET Level_Table_Name = UPPER(Level_Table_Name)
WHERE LEVEL_TABLE_NAME <> UPPER(LEVEL_TABLE_NAME);
UPDATE BSC_DB_TABLES_RELS
SET Table_Name = UPPER(Table_Name)
WHERE Table_Name <> UPPER(Table_Name);
UPDATE BSC_SYS_DIM_LEVELS_B
SET SHORT_NAME = 'BSC_DIM_OBJ_'||Dim_Level_Id||'_'||ROWNUM
WHERE short_name IS NULL;
SELECT COUNT(1) INTO l_count
FROM BIS_LEVELS
WHERE short_name = cd.Short_Name;
SELECT COUNT(1) INTO l_count
FROM BIS_LEVELS_TL
WHERE Name = cd.Name
AND language = userenv('LANG');
l_bis_dim_level_rec.Last_Updated_By := cd.Last_Updated_By;
l_bis_dim_level_rec.Last_Update_Date := cd.Last_Update_Date;
l_bis_dim_level_rec.Last_Update_Login := cd.Last_Update_Login;
SELECT COUNT(1) INTO l_count
FROM BSC_SYS_DIM_LEVELS_B
WHERE short_name = cd.Short_Name;
l_bsc_dim_obj_rec.Bsc_Last_Updated_By := cd.Last_Updated_By;
l_bsc_dim_obj_rec.Bsc_Last_Update_Date := cd.Last_Update_Date;
l_bsc_dim_obj_rec.Bsc_Last_Update_Login := cd.Last_Update_Login;
SELECT Dimension_Id
, Short_Name
, Created_By
, Last_Updated_By
, Last_Update_Date
, Last_Update_Login
, Name
, Description
, Application_Id
FROM BIS_DIMENSIONS_VL
WHERE DIM_GRP_ID IS NULL;
SELECT Dim_Group_Id
, Name
, Short_Name
, Created_By
, Last_Updated_By
, Last_Update_Date
, Last_Update_Login
FROM BSC_SYS_DIM_GROUPS_VL;
SELECT A.short_name short_name
FROM BIS_LEVELS A
WHERE A.dimension_id = l_dimension_id;
UPDATE BSC_SYS_DIM_GROUPS_TL
SET SHORT_NAME = 'BSC_DIM_'||Dim_Group_Id
WHERE short_name IS NULL;
SELECT COUNT(1) INTO l_count
FROM BSC_SYS_DIM_GROUPS_VL
WHERE short_name = cd.Short_Name;
SELECT COUNT(1) INTO l_count
FROM BSC_SYS_DIM_GROUPS_TL
WHERE name = cd.Name
AND language = userenv('LANG');
l_bsc_dimension_rec.Bsc_Last_Updated_By := cd.Last_Updated_By;
l_bsc_dimension_rec.Bsc_Last_Update_Date := cd.Last_Update_Date;
l_bsc_dimension_rec.Bsc_Last_Update_Login := cd.Last_Update_Login;
SELECT dim_level_id
INTO l_cn_dim_level_id
FROM BSC_SYS_DIM_LEVELS_B
WHERE short_name = dim_cn.short_name;
SELECT COUNT(1) INTO l_count
FROM BSC_SYS_DIM_LEVELS_BY_GROUP
WHERE dim_level_id = l_cn_dim_level_id
AND dim_group_id = (SELECT Dim_Group_Id
FROM BSC_SYS_DIM_GROUPS_VL WHERE SHORT_NAME = cd.Short_Name);
SELECT COUNT(1) INTO l_count
FROM BIS_DIMENSIONS
WHERE short_name = cd.Short_Name;
SELECT COUNT(1) INTO l_count
FROM BIS_DIMENSIONS_TL
WHERE Name = cd.Name
AND language = userenv('LANG');
l_bis_dimension_rec.Last_Updated_By := cd.Last_Updated_By;
l_bis_dimension_rec.Last_Update_Date := cd.Last_Update_Date;
l_bis_dimension_rec.Last_Update_Login := cd.Last_Update_Login;
UPDATE BIS_DIMENSIONS
SET Dim_Grp_ID = cd.Dim_Group_Id
WHERE Short_Name = cd.short_name;
select L.language_code
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and language_code <> userenv('LANG');
SELECT DIM_LEVEL_ID
INTO l_level_id
FROM BSC_SYS_DIM_LEVELS_B
WHERE SHORT_NAME = p_level_short_name;
SELECT NAME, HELP, LANGUAGE, SOURCE_LANG
INTO l_Dim_Level_Rec.Dimension_Name,
l_Dim_Level_Rec.Description,
l_Dim_Level_Rec.Language,
l_Dim_Level_Rec.Source_Lang
FROM BSC_SYS_DIM_LEVELS_TL
WHERE DIM_LEVEL_ID = l_level_id
AND LANGUAGE = cd.language_code;
select L.language_code
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and language_code <> userenv('LANG');
SELECT LEVEL_ID
INTO l_level_id
FROM BIS_LEVELS
WHERE SHORT_NAME = p_level_short_name;
SELECT NAME, DESCRIPTION, LANGUAGE, LAST_UPDATE_DATE, SOURCE_LANG
INTO l_Dim_Level_Rec.Bsc_Dim_Level_Long_Name,
l_Dim_Level_Rec.Bsc_Dim_Level_Help,
l_Dim_Level_Rec.Bsc_Language,
l_Dim_Level_Rec.Bsc_Last_Update_Date,
l_Dim_Level_Rec.Bsc_Source_Language
FROM BIS_LEVELS_TL
WHERE LEVEL_ID = l_level_id
AND LANGUAGE = cd.language_code;
select L.language_code
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and language_code <> userenv('LANG');
SELECT SHORT_NAME, NAME, LANGUAGE, LAST_UPDATE_DATE, SOURCE_LANG
INTO l_Dim_Grp_Rec.Dimension_Short_Name,
l_Dim_Grp_Rec.Dimension_Name,
l_Dim_Grp_Rec.Language,
l_Dim_Grp_Rec.Last_Update_Date,
l_Dim_Grp_Rec.Source_Lang
FROM BSC_SYS_DIM_GROUPS_TL
WHERE SHORT_NAME = p_dim_short_name
AND LANGUAGE = cd.language_code;
select L.language_code
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and language_code <> userenv('LANG');
SELECT DIMENSION_ID
INTO l_dim_id
FROM BIS_DIMENSIONS
WHERE SHORT_NAME = p_dim_short_name;
SELECT NAME, LANGUAGE, LAST_UPDATE_DATE, SOURCE_LANG
INTO l_Dim_Grp_Rec.Bsc_Dim_Level_Group_Name,
l_Dim_Grp_Rec.Bsc_Language,
l_Dim_Grp_Rec.Bsc_Last_Update_Date,
l_Dim_Grp_Rec.Bsc_Source_Language
FROM BIS_DIMENSIONS_TL
WHERE DIMENSION_ID = l_dim_id
AND LANGUAGE = cd.language_code;
SELECT * FROM BSC_USER_TAB_ACCESS
WHERE RESPONSIBILITY_ID = c_mgr_resp;
SELECT * FROM BSC_USER_KPI_ACCESS
WHERE RESPONSIBILITY_ID = c_mgr_resp;
SELECT responsibility_id
INTO l_pmd_resp
FROM FND_RESPONSIBILITY
WHERE responsibility_key = 'BSC_PMD_USER';
SELECT responsibility_id
INTO l_mgr_resp
FROM FND_RESPONSIBILITY
WHERE responsibility_key = 'BSC_Manager';
SELECT COUNT(1)
INTO l_count
FROM BSC_USER_TAB_ACCESS
WHERE TAB_ID = c_tab_recs.TAB_ID
AND RESPONSIBILITY_ID = l_pmd_resp;
SELECT COUNT(1)
INTO l_valid
FROM BSC_TABS_B
WHERE TAB_ID = c_tab_recs.TAB_ID;
l_Bsc_Tab_Entity_Rec.Bsc_Last_Updated_By := c_tab_recs.CREATED_BY;
l_Bsc_Tab_Entity_Rec.Bsc_Last_Update_Login := c_tab_recs.LAST_UPDATE_LOGIN;
SELECT COUNT(1)
INTO l_count
FROM BSC_USER_KPI_ACCESS
WHERE INDICATOR = c_kpi_recs.INDICATOR
AND RESPONSIBILITY_ID = l_pmd_resp;
SELECT COUNT(1)
INTO l_valid
FROM BSC_KPIS_B
WHERE INDICATOR = c_kpi_recs.INDICATOR;
l_Bsc_Kpi_Entity_Rec.Last_Updated_By := c_kpi_recs.CREATED_BY;
l_Bsc_Kpi_Entity_Rec.Last_Update_Login := c_kpi_recs.LAST_UPDATE_LOGIN;
SELECT NVL(MAX(Entry_Sequence),0)Entry_Sequence
INTO l_count
FROM FND_MENU_ENTRIES
WHERE Menu_Id =p_Menu_Id;
SELECT A.MENU_ID
, A.DESCRIPTION
, A.CREATED_BY
FROM FND_MENUS_VL A
, FND_MENU_ENTRIES B
WHERE A.MENU_NAME LIKE 'BSC_LAUNCHPAD_%'
AND A.MENU_ID = B.SUB_MENU_ID
AND B.MENU_ID = l_mgr_menu;
SELECT MENU_ID
INTO l_mgr_menu
FROM FND_RESPONSIBILITY_VL
WHERE RESPONSIBILITY_ID = p_mgr_resp;
SELECT MENU_ID
INTO l_pmd_menu
FROM FND_RESPONSIBILITY_VL
WHERE RESPONSIBILITY_ID = p_pmd_resp;
SELECT COUNT(0)
INTO l_count
FROM FND_MENU_ENTRIES
WHERE MENU_ID = l_pmd_menu
AND SUB_MENU_ID = cd.menu_id;
BSC_LAUNCH_PAD_PVT.INSERT_APP_MENU_ENTRIES_VB
( X_Menu_Id => l_pmd_menu
, X_Entry_Sequence => get_next_entry_sequence(l_pmd_menu)
, X_Sub_Menu_Id => cd.menu_id
, X_Function_Id => NULL
, X_Grant_Flag =>'Y'
, X_Prompt => NULL
, X_Description => cd.description
, X_User_Id => cd.created_by
);
This procedure identifies the BSC Measures with application_ids -1 and updates them
to 271.
********************************************************************************************/
FUNCTION update_Bsc_Application_Ids
(
x_error_msg OUT NOCOPY VARCHAR2
)RETURN BOOLEAN IS
l_return_Status BOOLEAN;
CURSOR c_update_appid IS
SELECT bisapp.indicator_id
FROM bis_application_measures bisapp,
bis_indicators bisindic,
bsc_sys_datasets_vl bsc
WHERE bisapp.indicator_id = bisindic.indicator_id
AND bisindic.dataset_id = bsc.dataset_id
AND bsc.source= 'BSC'
AND (bisapp.application_id = -1 OR bisapp.application_id IS NULL);
FOR cd IN c_update_appid LOOP
UPDATE bis_application_measures
SET application_id = 271
where indicator_id = cd.indicator_id;
IF(c_update_appid%ISOPEN) THEN
CLOSE c_update_appid;
END update_Bsc_Application_Ids;
SELECT count(1)
INTO l_count
FROM BIS_INDICATORS
WHERE UPPER(TRIM(Short_Name)) = UPPER(TRIM(l_temp_var));
SELECT
M.MEASURE_ID,
M.MEASURE_COL,
M.SOURCE,
I.SHORT_NAME,
I.DATASET_ID,
I.NAME
FROM
BSC_SYS_MEASURES M,
BSC_SYS_DATASETS_VL D,
BIS_INDICATORS_VL I
WHERE
M.MEASURE_ID = D.MEASURE_ID1 AND
D.DATASET_ID=I.DATASET_ID AND
M.SOURCE = BSC_BIS_MEASURE_PUB.c_PMF AND
M.MEASURE_COL = I.SHORT_NAME ORDER BY I.DATASET_ID;
/*SELECT
M.MEASURE_ID,
M.MEASURE_COL,
M.SOURCE,
I.SHORT_NAME,
I.DATASET_ID,
I.NAME
FROM
BSC_SYS_MEASURES M,
BIS_INDICATORS_VL I
WHERE
M.SHORT_NAME = I.SHORT_NAME AND
M.SOURCE = BSC_BIS_MEASURE_PUB.c_PMF AND
M.MEASURE_COL = I.SHORT_NAME ORDER BY I.DATASET_ID;*/
BSC_DATASETS_PVT.Update_Measures(
p_commit => FND_API.G_FALSE
,p_Dataset_Rec => l_Dataset_Rec
,x_return_status => l_Return_Status
,x_msg_count => l_Msg_Count
,x_msg_data => l_Msg_Data
);
SELECT
D.MEASURE_ID1,
M.MEASURE_COL,
D.SOURCE
FROM
BSC_SYS_DATASETS_B D,
BSC_SYS_MEASURES M
WHERE
D.SOURCE = BSC_BIS_MEASURE_PUB.c_PMF AND
M.MEASURE_ID = D.MEASURE_ID1;
SELECT COUNT(1) INTO l_Count
FROM BSC_DB_MEASURE_COLS_VL B
WHERE UPPER(B.MEASURE_COL) = UPPER(cPMFM.MEASURE_COL);
BSC_DB_MEASURE_COLS_PKG.INSERT_ROW (
x_Measure_Col => cPMFM.MEASURE_COL
, x_Measure_Group_Id => l_Measure_Group_Id
, x_Projection_Id => l_Projection_Id
, x_Measure_Type => l_Measure_Type
, x_Help => cPMFM.MEASURE_COL
);
, x_source => 'BSC_DB_MEASURE_COLS_PKG.INSERT_ROW'
, x_mode => 'I'
);
SELECT bsc_lvl.short_name, bis_dim.dimension_id
FROM bsc_sys_dim_groups_vl bsc_dim,
bsc_sys_dim_levels_b bsc_lvl,
bsc_sys_dim_levels_by_group lvl_by_grp,
bis_dimensions bis_dim
WHERE bsc_dim.dim_group_id = lvl_by_grp.dim_group_id
AND bsc_lvl.dim_level_id = lvl_by_grp.dim_level_id
/* AND bsc_dim.dim_group_id = bis_dim.dim_grp_id cannot use since dim_group_id is not updated on BIS side yet */
AND bsc_dim.short_name = bis_dim.short_name /* can assume here since short name are same on both side */
AND bsc_dim.short_name = p_dim_short_name ;
UPDATE bis_levels
SET dimension_id = l_bsc_rel_rec.dimension_id
WHERE short_name = l_bsc_rel_rec.short_name
AND dimension_id = -1 ;
FUNCTION Update_Dim_Hide_Properties (
x_error_msg OUT NOCOPY VARCHAR2
) RETURN BOOLEAN
IS
TYPE t_short_names IS TABLE OF BIS_DIMENSIONS.SHORT_NAME%TYPE INDEX BY BINARY_INTEGER;
SELECT
short_name
FROM
bis_dimensions
WHERE
(bsc_utility.Is_Internal_AG_Dim(short_name) IS NOT NULL OR
bsc_utility.Is_Internal_BIS_Import_Dim(short_name) IS NOT NULL OR
bsc_utility.Is_Internal_WKPI_Dim(short_name) IS NOT NULL)
AND bis_util.is_Seeded(created_by,'T','F') = 'F'
AND NVL(hide_in_design,'F') = 'F';
SAVEPOINT BisHideInDesignUpdate;
UPDATE BIS_DIMENSIONS
SET HIDE_IN_DESIGN = FND_API.G_TRUE
WHERE SHORT_NAME = l_dim_short_names(i);
x_error_msg := 'BSC_UPGRADES.Update_Dim_Hide_Properties Successfully Completed';
ROLLBACK TO BisHideInDesignUpdate;
x_error_msg := x_error_msg||' -> BSC_UPGRADES.Update_Dim_Hide_Properties ';
x_error_msg := SQLERRM||' at BSC_UPGRADES.Update_Dim_Hide_Properties ';
END Update_Dim_Hide_Properties;
SELECT
short_name
FROM
bsc_sys_dim_groups_vl grp
WHERE
bsc_bis_dimension_pub.get_dimension_source(short_name)='PMF' AND
short_name LIKE 'DGRP_%' AND
name = short_name AND
bis_util.is_seeded(created_by,'T','F') = 'F' AND
(SELECT COUNT(1) FROM bsc_kpi_dim_groups WHERE dim_group_id = grp.dim_group_id) = 0;
SAVEPOINT BisUpdateImportDim;
UPDATE BIS_DIMENSIONS
SET HIDE_IN_DESIGN = FND_API.G_TRUE
WHERE SHORT_NAME = l_dim_short_names(i);
ROLLBACK TO BisUpdateImportDim;
PROCEDURE Drop_Update_Dim_Obj_Views(
p_Dim_Obj_Sht_Name IN OUT NOCOPY FND_TABLE_OF_VARCHAR2_30
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
) IS
l_sql VARCHAR2(2000);
SELECT level_view_name,table_type
FROM bsc_sys_dim_levels_b
WHERE short_name = p_sht_name;
UPDATE bsc_sys_dim_levels_b
SET table_type = -1
WHERE short_name = p_Dim_Obj_Sht_Name(i);
x_msg_data := x_msg_data||' -> BSC_BIS_DIM_OBJ_PUB.Drop_Update_Dim_Obj_Views ';
x_msg_data := SQLERRM||' at BSC_BIS_DIM_OBJ_PUB.Drop_Update_Dim_Obj_Views ';
END Drop_Update_Dim_Obj_Views;