[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
( p_dataset_id IN NUMBER -- if NULL it means Create otherwise update
, p_ui_flag IN VARCHAR2
, p_dataset_source IN VARCHAR2 -- BSC or PMF
, p_dataset_name IN VARCHAR2 -- passed measure name
, x_measure_name OUT NOCOPY VARCHAR2 -- trimmed output measure name
) IS
l_Flag BOOLEAN;
SELECT DISTINCT BSC_MEAS.Source Bsc_Source
, BSC_DSET.Name Bsc_Name
, BIS_TAR.Source Bis_Source
, BIS_IND.Indicator_Id Bis_Ind_Id
, BIS_IND.Actual_Data_Source Bis_Act_Source
FROM BIS_INDICATORS BIS_IND
, BSC_SYS_MEASURES BSC_MEAS
, BSC_SYS_DATASETS_VL BSC_DSET
, BIS_TARGET_LEVELS BIS_TAR
WHERE UPPER(TRIM(BSC_DSET.Name)) = UPPER(x_measure_name)
AND BIS_IND.Indicator_Id = BIS_TAR.Indicator_Id(+)
AND BIS_IND.Short_Name = BSC_MEAS.Short_Name
AND BSC_MEAS.Measure_Id = BSC_DSET.Measure_Id1;
CURSOR c_Update_Measure IS
SELECT DISTINCT BSC_MEAS.Source Bsc_Source
, BSC_DSET.Name Bsc_Name
, BIS_TAR.Source Bis_Source
, BIS_IND.Indicator_Id Bis_Ind_Id
, BIS_IND.Actual_Data_Source Bis_Act_Source
FROM BIS_INDICATORS BIS_IND
, BSC_SYS_MEASURES BSC_MEAS
, BSC_SYS_DATASETS_VL BSC_DSET
, BIS_TARGET_LEVELS BIS_TAR
WHERE UPPER(TRIM(BSC_DSET.Name)) = UPPER(x_measure_name)
AND BIS_IND.Indicator_Id = BIS_TAR.Indicator_Id(+)
AND BIS_IND.Short_Name = BSC_MEAS.Short_Name
AND BSC_MEAS.Measure_Id = BSC_DSET.Measure_Id1
AND BSC_DSET.Dataset_Id <> p_dataset_id;
IF (p_dataset_id IS NULL) THEN -- called from update API
IF(p_ui_flag = 'Y') THEN
x_measure_name := TRIM(p_dataset_name);
SELECT COUNT(0)
INTO l_Count
FROM BSC_SYS_DATASETS_VL
WHERE UPPER(TRIM(Name)) =UPPER(x_measure_name)
AND Source = c_BSC;
SELECT COUNT(0)
INTO l_Count
FROM BSC_SYS_DATASETS_VL
WHERE UPPER(TRIM(Name)) =UPPER(x_measure_name)
AND Source = c_BSC
AND Dataset_id <> p_dataset_id;
FOR cd IN c_Update_Measure LOOP
l_Flag := BSC_BIS_MEASURE_PUB.Validate_Conditions
( p_Bsc_source => p_dataset_source
, p_Pmf_Old_source => cd.Bis_Source
, p_Bsc_Old_Source => cd.Bsc_Source
);
SELECT count(1) INTO l_count
FROM BIS_INDICATORS
WHERE UPPER(TRIM(Short_Name)) = UPPER(TRIM(l_temp_var));
,p_last_update_date => sysdate
,p_func_area_short_name => p_func_area_short_name
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
,p_last_update_date => sysdate
,p_func_area_short_name => p_func_area_short_name
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
,p_last_update_date IN BIS_INDICATORS.LAST_UPDATE_DATE%TYPE
,p_func_area_short_name IN VARCHAR2 := NULL
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) is
l_Dataset_Rec BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type;
l_last_update_date BIS_INDICATORS.LAST_UPDATE_DATE%TYPE;
l_last_update_date := nvl(p_last_update_date, sysdate);
l_Dataset_Rec.Bsc_Dataset_Last_Update_By := BIS_UTILITIES_PUB.Get_Owner_Id(p_owner);
l_Dataset_Rec.Bsc_Dataset_Last_Update_Login := fnd_global.LOGIN_ID;
l_Dataset_Rec.Bsc_Dataset_Creation_Date := l_last_update_date;
l_Dataset_Rec.Bsc_Dataset_Last_Update_Date := l_last_update_date;
l_Dataset_Rec.Bsc_Measure_Last_Update_By := BIS_UTILITIES_PUB.Get_Owner_Id(p_owner);
l_Dataset_Rec.Bsc_Measure_Last_Update_Login := fnd_global.LOGIN_ID;
l_Dataset_Rec.Bsc_Measure_Creation_Date := l_last_update_date;
l_Dataset_Rec.Bsc_Measure_Last_Update_Date := l_last_update_date;
SELECT COUNT(Short_Name) INTO l_count
FROM BIS_INDICATORS
WHERE UPPER(TRIM(Short_Name)) = l_Dataset_Rec.Bsc_Measure_Short_Name;
SELECT COUNT(Measure_Id)
INTO l_count
FROM BSC_SYS_MEASURES
WHERE Measure_Id = -1
AND Measure_Col = l_Dataset_Rec.Bsc_Measure_Col;
BSC_DB_MEASURE_COLS_PKG.Update_Measure_Column_Help (
p_Measure_Col => l_Dataset_Rec.Bsc_Measure_Col
, p_Help => l_Dataset_Rec.Bsc_Measure_Col_Help
, x_Return_Status => x_return_status
, x_Msg_Count => x_msg_count
, x_Msg_Data => x_msg_data
);
,p_lud => l_Dataset_Rec.Bsc_Dataset_Last_Update_Date
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
) ;
,p_lud => l_Dataset_Rec.Bsc_Dataset_Last_Update_Date
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
) ;
l_Measure_rec.Creation_Date := l_last_update_date;
l_Measure_rec.Last_Updated_By := BIS_UTILITIES_PUB.Get_Owner_Id(p_owner);
l_Measure_rec.Last_Update_Login := fnd_global.LOGIN_ID;
l_Measure_rec.Last_Update_Date := l_last_update_date;
bsc_db_measure_cols_pkg.insert_row(
l_Dataset_Rec.Bsc_Measure_Col
,p_measure_group_id
,l_Dataset_Rec.Bsc_Measure_Projection_Id
,p_Measure_Type
,l_Dataset_Rec.Bsc_Measure_Col_Help);
procedure Update_Measure(
p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_dataset_id IN NUMBER
,p_dataset_source IN VARCHAR2
,p_dataset_name IN VARCHAR2
,p_dataset_help IN VARCHAR2 := NULL
,p_dataset_measure_id1 IN NUMBER := NULL
,p_dataset_operation IN VARCHAR2 := NULL
,p_dataset_measure_id2 IN NUMBER := NULL
,p_dataset_format_id IN NUMBER := NULL
,p_dataset_color_method IN NUMBER := NULL
,p_dataset_autoscale_flag IN NUMBER := NULL
,p_dataset_projection_flag IN NUMBER := NULL
,p_measure_short_name IN VARCHAR2
,p_region_app_id IN Ak_Region_Items.REGION_APPLICATION_ID%Type := -1
,p_source_column_app_id IN Ak_Region_Items.ATTRIBUTE_APPLICATION_ID%Type := -1
,p_compare_column_app_id IN Ak_Region_Items.ATTRIBUTE_APPLICATION_ID%Type := -1
,p_measure_act_data_src_type IN VARCHAR2 := NULL
,p_measure_act_data_src IN VARCHAR2 := NULL
,p_measure_comparison_source IN VARCHAR2 := NULL
,p_measure_operation IN VARCHAR2 := c_SUM
,p_measure_uom_class IN VARCHAR2 := NULL
,p_measure_increase_in_measure IN VARCHAR2 := NULL
,p_measure_random_style IN NUMBER := NULL
,p_measure_min_act_value IN NUMBER := NULL
,p_measure_max_act_value IN NUMBER := NULL
,p_measure_min_bud_value IN NUMBER := NULL
,p_measure_max_bud_value IN NUMBER := NULL
,p_measure_app_id IN NUMBER := NULL
,p_measure_col IN VARCHAR2 := NULL
,p_measure_col_help IN VARCHAR2 := NULL
,p_measure_group_id IN NUMBER := NULL
,p_measure_projection_id IN NUMBER := NULL
,p_measure_type IN NUMBER := NULL
,p_measure_apply_rollup IN VARCHAR2 := NULL
,p_measure_function_name IN VARCHAR2 := NULL
,p_measure_enable_link IN VARCHAR2 := NULL
,p_measure_obsolete IN VARCHAR2 := FND_API.G_FALSE
,p_type IN VARCHAR2 := NULL -- This is used for weighted kpis,This can take values CDS_SCORE,CDS_PERF or Null
,p_measure_is_validate IN VARCHAR2 := FND_API.G_TRUE -- ankgoel: bug#3557236
,p_time_stamp IN VARCHAR2 := NULL -- Added for Granular Locking
,p_dimension1_id IN NUMBER
,p_dimension2_id IN NUMBER
,p_dimension3_id IN NUMBER
,p_dimension4_id IN NUMBER
,p_dimension5_id IN NUMBER
,p_dimension6_id IN NUMBER
,p_dimension7_id IN NUMBER
,p_y_axis_title IN VARCHAR2 := NULL
,p_owner IN VARCHAR2 := BIS_UTILITIES_PUB.G_CUSTOM_OWNER
,p_ui_flag IN VARCHAR2
,p_func_area_short_name IN VARCHAR2 := NULL
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) is
begin
SAVEPOINT SP_UPDATE_MEASURE;
Update_Measure(
p_commit => p_commit
,p_dataset_id => p_dataset_id
,p_dataset_source => p_dataset_source
,p_dataset_name => p_dataset_name
,p_dataset_help => p_dataset_help
,p_dataset_measure_id1 => p_dataset_measure_id1
,p_dataset_operation => p_dataset_operation
,p_dataset_measure_id2 => p_dataset_measure_id2
,p_dataset_format_id => p_dataset_format_id
,p_dataset_color_method => p_dataset_color_method
,p_dataset_autoscale_flag => p_dataset_autoscale_flag
,p_dataset_projection_flag => p_dataset_projection_flag
,p_measure_short_name => p_measure_short_name
,p_region_app_id => p_region_app_id
,p_source_column_app_id => p_source_column_app_id
,p_compare_column_app_id => p_compare_column_app_id
,p_measure_act_data_src_type => p_measure_act_data_src_type
,p_measure_act_data_src => p_measure_act_data_src
,p_measure_comparison_source => p_measure_comparison_source
,p_measure_operation => p_measure_operation
,p_measure_uom_class => p_measure_uom_class
,p_measure_increase_in_measure => p_measure_increase_in_measure
,p_measure_random_style => p_measure_random_style
,p_measure_min_act_value => p_measure_min_act_value
,p_measure_max_act_value => p_measure_max_act_value
,p_measure_min_bud_value => p_measure_min_bud_value
,p_measure_max_bud_value => p_measure_max_bud_value
,p_measure_app_id => p_measure_app_id
,p_measure_col => p_measure_col
,p_measure_col_help => p_measure_col_help
,p_measure_group_id => p_measure_group_id
,p_measure_projection_id => p_measure_projection_id
,p_measure_type => p_measure_type
,p_measure_apply_rollup => p_measure_apply_rollup
,p_measure_function_name => p_measure_function_name
,p_measure_enable_link => p_measure_enable_link
,p_measure_obsolete => p_measure_obsolete
,p_type => p_type
,p_measure_is_validate => p_measure_is_validate
,p_time_stamp => p_time_stamp
,p_dimension1_id => p_dimension1_id
,p_dimension2_id => p_dimension2_id
,p_dimension3_id => p_dimension3_id
,p_dimension4_id => p_dimension4_id
,p_dimension5_id => p_dimension5_id
,p_dimension6_id => p_dimension6_id
,p_dimension7_id => p_dimension7_id
,p_y_axis_title => p_y_axis_title
,p_owner => p_owner
,p_ui_flag => p_ui_flag
,p_last_update_date => sysdate
,p_func_area_short_name => p_func_area_short_name
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
ROLLBACK TO SP_UPDATE_MEASURE;
ROLLBACK TO SP_UPDATE_MEASURE;
x_msg_data := x_msg_data||' -> BSC_BIS_MEASURE_PUB.Update_measure ';
x_msg_data := SQLERRM||' at BSC_BIS_MEASURE_PUB.Update_measure ';
ROLLBACK TO SP_UPDATE_MEASURE;
x_msg_data := x_msg_data||' -> BSC_BIS_MEASURE_PUB.Update_measure ';
x_msg_data := SQLERRM||' at BSC_BIS_MEASURE_PUB.Update_measure ';
ROLLBACK TO SP_UPDATE_MEASURE;
end Update_measure;
SELECT MEASURE_COL
FROM BSC_SYS_MEASURES;
PROCEDURE Update_Single_To_Formula(
p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_Dataset_Rec IN BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type
,p_Dataset_Rec_db IN BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_kpi_flag number := -1;
SELECT distinct indicator
FROM bsc_kpi_analysis_measures_b
WHERE dataset_id = l_Dataset_Rec.Bsc_Dataset_Id;
if l_Dataset_Rec.Bsc_Measure_Last_Update_By is null then
l_Dataset_Rec.Bsc_Measure_Last_Update_By := fnd_global.USER_ID;
if l_Dataset_Rec.Bsc_Measure_Last_Update_Login is null then
l_Dataset_Rec.Bsc_Measure_Last_Update_Login := fnd_global.LOGIN_ID;
BSC_DATASETS_PUB.Update_Dataset(
p_commit => p_commit
,p_Dataset_Rec => l_Dataset_Rec
,p_update_dset_calc => false
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
END Update_Single_To_Formula;
procedure Update_Measure(
p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_dataset_id IN NUMBER
,p_dataset_source IN VARCHAR2
,p_dataset_name IN VARCHAR2
,p_dataset_help IN VARCHAR2 := NULL
,p_dataset_measure_id1 IN NUMBER := NULL
,p_dataset_operation IN VARCHAR2 := NULL
,p_dataset_measure_id2 IN NUMBER := NULL
,p_dataset_format_id IN NUMBER := NULL
,p_dataset_color_method IN NUMBER := NULL
,p_dataset_autoscale_flag IN NUMBER := NULL
,p_dataset_projection_flag IN NUMBER := NULL
,p_measure_short_name IN VARCHAR2
,p_region_app_id IN Ak_Region_Items.REGION_APPLICATION_ID%Type := -1
,p_source_column_app_id IN Ak_Region_Items.ATTRIBUTE_APPLICATION_ID%Type := -1
,p_compare_column_app_id IN Ak_Region_Items.ATTRIBUTE_APPLICATION_ID%Type := -1
,p_measure_act_data_src_type IN VARCHAR2 := NULL
,p_measure_act_data_src IN VARCHAR2 := NULL
,p_measure_comparison_source IN VARCHAR2 := NULL
,p_measure_operation IN VARCHAR2 := c_SUM
,p_measure_uom_class IN VARCHAR2 := NULL
,p_measure_increase_in_measure IN VARCHAR2 := NULL
,p_measure_random_style IN NUMBER := NULL
,p_measure_min_act_value IN NUMBER := NULL
,p_measure_max_act_value IN NUMBER := NULL
,p_measure_min_bud_value IN NUMBER := NULL
,p_measure_max_bud_value IN NUMBER := NULL
,p_measure_app_id IN NUMBER := NULL
,p_measure_col IN VARCHAR2 := NULL
,p_measure_col_help IN VARCHAR2 := NULL
,p_measure_group_id IN NUMBER := NULL
,p_measure_projection_id IN NUMBER := NULL
,p_measure_type IN NUMBER := NULL
,p_measure_apply_rollup IN VARCHAR2 := NULL
,p_measure_function_name IN VARCHAR2 := NULL
,p_measure_enable_link IN VARCHAR2 := NULL
,p_measure_obsolete IN VARCHAR2 := FND_API.G_FALSE
,p_type IN VARCHAR2 := NULL -- This is used for weighted kpis,This can take values CDS_SCORE,CDS_PERF or Null
,p_measure_is_validate IN VARCHAR2 := FND_API.G_TRUE -- ankgoel: bug#3557236
,p_time_stamp IN VARCHAR2 := NULL -- Added for Granular Locking
,p_dimension1_id IN NUMBER
,p_dimension2_id IN NUMBER
,p_dimension3_id IN NUMBER
,p_dimension4_id IN NUMBER
,p_dimension5_id IN NUMBER
,p_dimension6_id IN NUMBER
,p_dimension7_id IN NUMBER
,p_y_axis_title IN VARCHAR2 := NULL
,p_owner IN VARCHAR2 := BIS_UTILITIES_PUB.G_CUSTOM_OWNER
,p_ui_flag IN VARCHAR2
,p_last_update_date IN BIS_INDICATORS.LAST_UPDATE_DATE%TYPE
,p_func_area_short_name IN VARCHAR2 := NULL
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) is
l_Dataset_Rec BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type;
l_Delete_Source BOOLEAN;
l_last_update_date BIS_INDICATORS.LAST_UPDATE_DATE%TYPE;
SELECT measure_group_id
, projection_id
, measure_type
, help
FROM bsc_db_measure_cols_vl
WHERE measure_col = c_measure_col_name;
SELECT s_Color_Formula
, Measure_Col
FROM BSC_SYS_MEASURES
WHERE Measure_Id = l_Dataset_Rec.Bsc_Measure_Id;
SELECT MEASURE_ID1
, MEASURE_ID2
FROM BSC_SYS_DATASETS_B
WHERE DATASET_ID = p_dataset_id;
SELECT NAME
FROM BSC_SYS_DATASETS_VL
WHERE DATASET_ID = p_dataset_id;
SELECT NAME
FROM BIS_INDICATORS_VL
WHERE DATASET_ID = p_dataset_id;
SELECT D.MEASURE_ID1, D.MEASURE_ID2, M.MEASURE_COL
FROM BSC_SYS_DATASETS_B D, BSC_SYS_MEASURES M
WHERE D.DATASET_ID = p_Dataset_ID
AND M.MEASURE_ID = D.MEASURE_ID1;
SAVEPOINT SP_UPDATE_MEASURE;
l_last_update_date := nvl(p_last_update_date, sysdate);
l_Dataset_Rec.Bsc_Dataset_Last_Update_By := BIS_UTILITIES_PUB.Get_Owner_Id(p_owner);
l_Dataset_Rec.Bsc_Dataset_Last_Update_Login := fnd_global.LOGIN_ID;
l_Dataset_Rec.Bsc_Dataset_Last_Update_Date := l_last_update_date;
l_Dataset_Rec.Bsc_Measure_Last_Update_By := BIS_UTILITIES_PUB.Get_Owner_Id(p_owner);
l_Dataset_Rec.Bsc_Measure_Last_Update_Login := fnd_global.LOGIN_ID;
l_Dataset_Rec.Bsc_Measure_Last_Update_Date := l_last_update_date;
BSC_BIS_LOCKS_PUB.LOCK_UPDATE_MEASURE(
p_dataset_id => l_Dataset_Rec.Bsc_dataset_id
,p_time_stamp => p_time_stamp
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
) ;
changed, then we need to delete it during update and ensure that it is not orphened
i.e its achieved using the Delete_Measures from the Private datasets package (BSC_DATASETS_PVT)
*/
l_Delete_Source := FALSE;
l_Delete_Source := TRUE;
l_Delete_Source := TRUE;
a new Measure Record should be inserted in bsc_sys_measures
*/
BSC_DATASETS_PUB.Retrieve_Measures(
p_commit => p_commit
,p_Dataset_Rec => l_Dataset_Rec
,x_Dataset_Rec => l_Dataset_Rec_db
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
BSC_BIS_MEASURE_PUB.Update_Single_To_Formula(
p_commit => p_commit
,p_Dataset_Rec => l_Dataset_Rec
,p_Dataset_Rec_db => l_Dataset_Rec_db
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
BSC_DATASETS_PUB.Update_Measures(
p_commit => p_commit
,p_Dataset_Rec => l_Dataset_Rec
,p_update_dset_calc => false
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
IF (l_Delete_Source = TRUE) THEN
-- this API deletes only the source columns (bsc_sys_measures) and not the actual measure itself.
BSC_DATASETS_PVT.Delete_Measures(
p_commit => p_commit
,p_Dataset_Rec => l_Del_Dataset_Rec
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
BSC_DB_MEASURE_COLS_PKG.Update_Row
( x_Measure_Col => l_Dataset_Rec.Bsc_Measure_Col
, x_Measure_Group_Id => NVL(p_measure_group_id, l_measure_group_id)
, x_Projection_Id => NVL(l_Dataset_Rec.Bsc_Measure_Projection_Id,l_projection_id)
, x_Measure_Type => NVL(p_Measure_Type, l_measure_type)
, x_Help => NVL(l_measure_col_help, l_Dataset_Rec.Bsc_Measure_Col)
);
bsc_db_measure_cols_pkg.insert_row(
l_Dataset_Rec.Bsc_Measure_Col
,p_measure_group_id
,l_Dataset_Rec.Bsc_Measure_Projection_Id
,p_Measure_Type
,l_measure_col_help);
select pm.measure_id
into l_Measure_Rec.Measure_Id
from bisbv_performance_measures pm
where dataset_id = p_dataset_id;
l_measure_rec.Last_Updated_By := BIS_UTILITIES_PUB.Get_Owner_Id(p_owner);
l_measure_rec.Last_Update_Login := fnd_global.LOGIN_ID;
l_measure_rec.Last_Update_Date := l_last_update_date;
BIS_MEASURE_PUB.Update_Measure(
p_api_version => 1.0
,p_commit => p_commit
,p_Measure_Rec => l_measure_rec
,p_owner => p_owner
,x_return_status => x_return_status
,x_error_tbl => l_error_tbl);
,p_lud => l_Dataset_Rec.Bsc_Dataset_Last_Update_Date
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
) ;
,p_lud => l_Dataset_Rec.Bsc_Dataset_Last_Update_Date
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
) ;
,p_lud => l_Dataset_Rec.Bsc_Dataset_Last_Update_Date
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
) ;
ROLLBACK TO SP_UPDATE_MEASURE;
ROLLBACK TO SP_UPDATE_MEASURE;
x_msg_data := x_msg_data||' -> BSC_BIS_MEASURE_PUB.Update_measure ';
x_msg_data := SQLERRM||' at BSC_BIS_MEASURE_PUB.Update_measure ';
ROLLBACK TO SP_UPDATE_MEASURE;
x_msg_data := x_msg_data||' -> BSC_BIS_MEASURE_PUB.Update_measure ';
x_msg_data := SQLERRM||' at BSC_BIS_MEASURE_PUB.Update_measure ';
ROLLBACK TO SP_UPDATE_MEASURE;
end Update_measure;
procedure Delete_measure(
p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_dataset_id IN NUMBER
,p_time_stamp IN VARCHAR2 := NULL -- Added for Granular Locking
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) is
l_Dataset_Rec BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type;
BSC_BIS_LOCKS_PUB.LOCK_DELETE_MEASURE(
p_dataset_id => l_Dataset_Rec.Bsc_dataset_id
,p_time_stamp => p_time_stamp
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
) ;
SELECT short_name INTO l_Meas_Extn_Rec.Measure_Short_Name FROM bis_indicators WHERE dataset_id = p_dataset_id;
select measure_id1, measure_id2, pm.measure_id
into l_Dataset_Rec.Bsc_Measure_Id, l_Dataset_Rec.Bsc_Measure_Id2, l_Measure_Rec.Measure_Id
from bsc_sys_datasets_B d, bisbv_performance_measures pm
where d.dataset_id = p_dataset_id
and d.dataset_id = pm.dataset_id (+);
BSC_DATASETS_PUB.Delete_Measures(
p_commit => p_commit
,p_Dataset_Rec => l_Dataset_Rec
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
BIS_MEASURE_PUB.Delete_Measure(
p_api_version => 1.0
,p_commit => p_commit
,p_Measure_Rec => l_Measure_Rec
,x_return_status => x_return_status
,x_error_Tbl => l_error_tbl);
SELECT
COUNT(1) INTO l_count
FROM
BIS_MEASURES_EXTENSION_VL
WHERE
MEASURE_SHORT_NAME = l_Meas_Extn_Rec.Measure_Short_Name;
BIS_OBJECT_EXTENSIONS_PUB.Delete_Measure_Extension(
p_Api_Version =>1.0
,p_Commit => p_commit
,p_Meas_Extn_Rec => l_Meas_Extn_Rec
,x_Return_Status => x_Return_Status
,x_Msg_Count => x_Msg_Count
,x_Msg_Data => x_Msg_Data
);
end delete_measure;
SELECT
km.indicator,
km.analysis_option0,
km.analysis_option1,
km.analysis_option2,
km.series_id
FROM
bsc_kpi_analysis_measures_b km,
bsc_kpi_measure_props kp
WHERE
kp.indicator = km.indicator AND
kp.kpi_measure_id = km.kpi_measure_id AND
km.dataset_id = p_dataset_id AND
kp.default_calculation IS NOT NULL AND
kp.default_calculation = p_disabled_calculation;
BSC_DATASETS_PUB.Delete_Dataset_Calc(
p_commit => p_commit
,p_Dataset_Rec => l_Dataset_Rec
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
SELECT
cause_indicator
FROM
bsc_kpi_cause_effect_rels
WHERE
effect_level = 'DATASET'
AND effect_indicator = p_dataset_id;
SELECT
effect_indicator
FROM
bsc_kpi_cause_effect_rels
WHERE
cause_level = 'DATASET'
AND cause_indicator = p_dataset_id;
select name into l_name
from bsc_sys_datasets_vl
where dataset_id = p_causes_table(i);
BIS_CUSTOM_CAUSE_EFFECT_PVT.Delete_Custom_Cause_Effect_Rel(
p_commit => p_commit
, p_Cause_DataSetId => l_temp_dataset_id
, p_Effect_DataSetId => p_dataset_id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
BIS_CUSTOM_CAUSE_EFFECT_PVT.Delete_Custom_Cause_Effect_Rel(
p_commit => p_commit
, p_Cause_DataSetId => p_dataset_id
, p_Effect_DataSetId => l_temp_dataset_id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
BSC_CAUSE_EFFECT_REL_PUB.Delete_All_Cause_Effect_Rels(
p_commit => p_commit
,p_indicator => p_dataset_id
,p_level => BSC_BIS_MEASURE_PUB.c_LEVEL
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
select BSC_INTERNAL_COLUMN_S.nextval into l_column_name from dual;
SELECT MAX( ANALYSIS_GROUP_ID)
INTO h_ag_count
FROM BSC_KPI_ANALYSIS_GROUPS
WHERE INDICATOR = p_indicator;
SELECT NAME INTO l_anal_name
FROM BSC_KPI_ANALYSIS_OPTIONS_VL
WHERE ANALYSIS_GROUP_ID =0
AND OPTION_ID = p_a0
AND INDICATOR = p_indicator;
SELECT DEPENDENCY_FLAG INTO h_ag_depend
FROM BSC_KPI_ANALYSIS_GROUPS
WHERE ANALYSIS_GROUP_ID =1
AND INDICATOR = p_indicator;
SELECT NAME INTO l_anal_name
FROM BSC_KPI_ANALYSIS_OPTIONS_VL
WHERE ANALYSIS_GROUP_ID = 1
AND OPTION_ID = p_a1
AND INDICATOR = p_indicator;
SELECT NAME INTO l_anal_name
FROM BSC_KPI_ANALYSIS_OPTIONS_VL
WHERE ANALYSIS_GROUP_ID =1
AND OPTION_ID = p_a1
AND PARENT_OPTION_ID = p_a0
AND INDICATOR = p_indicator;
SELECT DEPENDENCY_FLAG
INTO h_ag1_depend
FROM BSC_KPI_ANALYSIS_GROUPS
WHERE ANALYSIS_GROUP_ID =1
AND INDICATOR = p_indicator;
SELECT DEPENDENCY_FLAG
INTO h_ag2_depend
FROM BSC_KPI_ANALYSIS_GROUPS
WHERE ANALYSIS_GROUP_ID = 2
AND INDICATOR = p_indicator;
SELECT NAME
INTO l_anal_name
FROM BSC_KPI_ANALYSIS_OPTIONS_VL
WHERE ANALYSIS_GROUP_ID =2
AND OPTION_ID=p_a2
AND INDICATOR=p_indicator;
SELECT NAME
INTO l_anal_name
FROM BSC_KPI_ANALYSIS_OPTIONS_VL
WHERE ANALYSIS_GROUP_ID = 2
AND OPTION_ID = p_a2
AND PARENT_OPTION_ID = p_a1
AND INDICATOR = p_indicator;
SELECT NAME
INTO l_anal_name
FROM BSC_KPI_ANALYSIS_OPTIONS_VL
WHERE ANALYSIS_GROUP_ID = 2
AND OPTION_ID = p_a2
AND PARENT_OPTION_ID = p_a1
AND GRANDPARENT_OPTION_ID = p_a0
AND INDICATOR = p_indicator;
SELECT COUNT(SERIES_ID)
FROM BSC_KPI_ANALYSIS_MEASURES_VL
WHERE INDICATOR = p_indicator
AND ANALYSIS_OPTION0 = p_a0
AND ANALYSIS_OPTION1 = p_a1
AND ANALYSIS_OPTION2 = p_a2;
l_msg := l_msg || ' could not be created/updated.';
,p_file_lud => p_Measure_Rec.Last_Update_Date
,p_custom_mode => p_custom_mode
);
SELECT min(measure_group_id) into l_measure_group_id
FROM bsc_db_measure_groups_vl
WHERE help = p_measure_group_name;
bsc_db_measure_groups_pkg.insert_row(
x_measure_group_id => l_measure_group_id
,x_help => p_measure_group_name);
,p_last_update_date => p_measure_rec.Last_Update_Date
,p_func_area_short_name => l_measure_rec.Func_Area_Short_Name
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
BSC_BIS_MEASURE_PUB.Update_Measure(
p_commit => FND_API.G_FALSE
,p_dataset_id => l_dataset_id
,p_dataset_source => NVL(l_dataset_rec.Bsc_Source, 'PMF') -- :SOURCE
,p_dataset_name => NVL(l_dataset_rec.Bsc_Dataset_Name, l_dataset_rec_db.Bsc_Dataset_Name)
,p_dataset_help => NVL(l_dataset_rec.Bsc_Dataset_Help, l_dataset_rec_db.Bsc_Dataset_Help)
,p_dataset_measure_id1 => NVL(l_measure_id1, l_dataset_rec_db.Bsc_Measure_Id)
,p_dataset_operation => NVL(l_dataset_rec.Bsc_Dataset_Operation, l_dataset_rec_db.Bsc_Dataset_Operation)
,p_dataset_measure_id2 => NVL(l_measure_id2, l_dataset_rec_db.Bsc_Measure_Id2)
,p_dataset_format_id => NVL(l_dataset_rec.Bsc_Dataset_Format_Id, l_dataset_rec_db.Bsc_Dataset_Format_Id)
,p_dataset_color_method => NVL(l_dataset_rec.Bsc_Dataset_Color_Method, l_dataset_rec_db.Bsc_Dataset_Color_Method)
,p_dataset_autoscale_flag => NVL(l_dataset_rec.Bsc_Dataset_Autoscale_Flag, l_dataset_rec_db.Bsc_Dataset_Autoscale_Flag)
,p_dataset_projection_flag => NVL(l_dataset_rec.Bsc_Dataset_Projection_Flag, l_dataset_rec_db.Bsc_Dataset_Projection_Flag)
,p_measure_short_name => l_measure_rec.Measure_Short_Name
,p_measure_act_data_src_type => l_measure_rec.Actual_Data_Source_Type
,p_measure_act_data_src => l_measure_rec.Actual_Data_Source
,p_measure_comparison_source => l_measure_rec.Comparison_Source
,p_measure_operation => NVL(l_dataset_rec.Bsc_Measure_Operation, l_dataset_rec_db1.Bsc_Measure_Operation)
,p_measure_uom_class => l_measure_rec.Unit_Of_Measure_Class
,p_measure_increase_in_measure => l_measure_rec.Increase_In_Measure
,p_measure_random_style => NVL(l_dataset_rec.Bsc_Measure_Random_Style, l_dataset_rec_db1.Bsc_Measure_Random_Style)
,p_measure_min_act_value => NVL(l_dataset_rec.Bsc_Measure_Min_Act_Value, l_dataset_rec_db1.Bsc_Measure_Min_Act_Value)
,p_measure_max_act_value => NVL(l_dataset_rec.Bsc_Measure_Max_Act_Value, l_dataset_rec_db1.Bsc_Measure_Max_Act_Value)
,p_measure_min_bud_value => NVL(l_dataset_rec.Bsc_Measure_Min_Bud_Value, l_dataset_rec_db1.Bsc_Measure_Min_Bud_Value)
,p_measure_max_bud_value => NVL(l_dataset_rec.Bsc_Measure_Max_Bud_Value, l_dataset_rec_db1.Bsc_Measure_Max_Bud_Value)
,p_measure_app_id => l_Application_rec.Application_id
,p_measure_col => NVL(l_dataset_rec.Bsc_Measure_Col, l_dataset_rec_db1.Bsc_Measure_Col)
,p_measure_group_id => NVL(l_dataset_rec.Bsc_Measure_Group_Id, -1)
,p_measure_projection_id => NVL(l_dataset_rec.Bsc_Measure_Projection_Id, 3)
,p_measure_type => NVL(l_dataset_rec.Bsc_Measure_Type, l_dataset_rec_db1.Bsc_Measure_Type)
,p_measure_apply_rollup => p_measure_apply_rollup
,p_measure_function_name => l_measure_rec.Function_Name
,p_measure_enable_link => l_measure_rec.Enable_Link
,p_measure_obsolete => l_measure_rec.Obsolete
,p_type => l_measure_rec.Measure_Type
,p_measure_is_validate => l_measure_rec.is_validate -- ankgoel: bug#3557236
,p_time_stamp => l_time_stamp
,p_dimension1_id => l_measure_rec.Dimension1_id
,p_dimension2_id => l_measure_rec.Dimension2_id
,p_dimension3_id => l_measure_rec.Dimension3_id
,p_dimension4_id => l_measure_rec.Dimension4_id
,p_dimension5_id => l_measure_rec.Dimension5_id
,p_dimension6_id => l_measure_rec.Dimension6_id
,p_dimension7_id => l_measure_rec.Dimension7_id
,p_y_axis_title => NVL(l_dataset_rec.Bsc_Y_Axis_Title, l_dataset_rec_db.Bsc_Y_Axis_Title)
,p_owner => p_owner
,p_ui_flag => c_UI_FLAG
,p_last_update_date => p_measure_rec.Last_Update_Date
,p_func_area_short_name => l_measure_rec.Func_Area_Short_Name
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
,p_file_lud => p_Measure_Rec.Last_Update_Date
,p_custom_mode => p_custom_mode
);
l_dataset_rec.Bsc_Dataset_Last_Update_By := BIS_UTILITIES_PUB.Get_Owner_Id(p_owner);
l_dataset_rec.Bsc_Dataset_Last_Update_Login := fnd_global.LOGIN_ID;
l_dataset_rec.Bsc_Measure_Last_Update_By := BIS_UTILITIES_PUB.Get_Owner_Id(p_owner);
l_dataset_rec.Bsc_Measure_Last_Update_Login := fnd_global.LOGIN_ID;
l_measure_rec.Last_Updated_By := BIS_UTILITIES_PUB.Get_Owner_Id(p_owner);
l_measure_rec.Last_Update_Login := fnd_global.LOGIN_ID;
SELECT dataset_id
FROM bis_indicators
WHERE short_name = cp_measure_short_name;
SELECT COUNT(1) INTO l_count
FROM BSC_SYS_MEASURES
WHERE UPPER(measure_col) = UPPER(l_temp_var);
SELECT COUNT(1) INTO l_count_col
FROM BSC_DB_MEASURE_COLS_TL
WHERE UPPER(measure_col) = UPPER(l_temp_var);
SELECT COUNT(1) INTO l_count
FROM bsc_sys_measures
WHERE UPPER(measure_col) = UPPER(l_temp_var)
AND measure_id <> p_measure_id;
SELECT COUNT(1) INTO l_count_col
FROM BSC_DB_MEASURE_COLS_TL
WHERE UPPER(measure_col) = UPPER(l_temp_var);
SELECT measure_group_id
FROM bsc_sys_datasets_vl d
, bsc_sys_measures m
, bsc_db_measure_cols_vl db
WHERE d.dataset_id = p_dataset_id
AND m.measure_id = d.measure_id1
AND m.measure_col =db.measure_col;
select count(c.projection_id)
into l_count
from bsc_sys_datasets_vl d, bsc_sys_measures m, bsc_db_measure_cols_vl c
where m.measure_id = d.measure_id1
and c.measure_col = m.measure_col
and d.dataset_id = p_dataset_id;
select c.projection_id, c.measure_type
into l_proj_id, l_measure_type
from bsc_sys_datasets_vl d, bsc_sys_measures m, bsc_db_measure_cols_vl c
where m.measure_id = d.measure_id1
and c.measure_col = m.measure_col
and d.dataset_id = p_dataset_id;
select decode(count(disabled_calc_id), 1, 'N', 0, 'Y', 'N') isDisabled
into l_is_ytd_enabled
from bsc_sys_dataset_calc
where dataset_id = p_dataset_id
and disabled_calc_id = c_YTD_CODE;
select decode(count(disabled_calc_id), 1, 'N', 0, 'Y', 'N') isDisabled
into l_is_qtd_enabled
from bsc_sys_dataset_calc
where dataset_id = p_dataset_id
and disabled_calc_id = c_QTD_CODE;
select decode(count(disabled_calc_id), 1, 'N', 0, 'Y', 'N') isDisabled
into l_is_xtd_enabled
from bsc_sys_dataset_calc
where dataset_id = p_dataset_id
and disabled_calc_id = c_XTD_CODE;
select decode(nvl(BSC_APPS.Get_Property_Value(m.S_COLOR_FORMULA, 'pAvgL'), 'N'), 'Y', 'AVL', m.operation) rollup
into l_rollup_calc
from bsc_sys_datasets_vl d, bsc_sys_measures m
where m.measure_id = d.measure_id1
and d.dataset_id = p_dataset_id ;
select m.measure_col formula
into l_formula
from bsc_sys_datasets_vl d, bsc_sys_measures m
where m.measure_id = d.measure_id1
and d.dataset_id = p_dataset_id ;
select k.name || ' [' || k.indicator || '] ' name
from bsc_kpis_vl k
where indicator in
(
select distinct indicator
from bsc_kpi_analysis_measures_b d
where dataset_id = p_dataset_id
)
and k.share_flag <> 2;
SELECT k.name || ' [' || k.indicator || '] ' name
FROM bsc_kpis_vl k
WHERE indicator IN
(
SELECT DISTINCT d.indicator
FROM bsc_kpi_analysis_measures_b d,
bsc_kpis_b kpi
WHERE d.dataset_id = p_dataset_id
AND d.indicator = kpi.indicator
AND kpi.short_name IS NOT NULL
)
AND k.share_flag <> 2;
SELECT DISTINCT B.NAME||'['||B.INDICATOR||']' NAME
FROM BSC_KPI_ANALYSIS_MEASURES_B A,
BSC_KPIS_VL B,
BSC_DB_COLOR_KPI_DEFAULTS_V D
WHERE INSTR(L_DATASET_LIST, ','||A.DATASET_ID||',') > 0
AND D.DATASET_ID = A.DATASET_ID
AND A.INDICATOR =B.INDICATOR
AND B.INDICATOR =D.INDICATOR
AND B.PROTOTYPE_FLAG <> 2
AND B.SHARE_FLAG <> 2;
select B.DATASET_ID DATASET_ID, C.COLOR_METHOD COLOR_METHOD
from BSC_OAF_ANALYSYS_OPT_COMB_V A,
BSC_KPI_ANALYSIS_MEASURES_VL B,
BSC_SYS_DATASETS_VL C
where A.INDICATOR = B.INDICATOR
and A.SERIES_ID = B.SERIES_ID
and A.ANALYSIS_OPTION0 = B.ANALYSIS_OPTION0
and A.ANALYSIS_OPTION1 = B.ANALYSIS_OPTION1
and A.ANALYSIS_OPTION2 = B.ANALYSIS_OPTION2
and B.DATASET_ID = C.DATASET_ID
and A.INDICATOR = p_kpi_id
order by B.DATASET_ID;
select a.property_value, b.property_Value, c.property_value,
d.property_value, e.property_Value, f.property_value,
g.property_value, h.property_Value
into l2_m1_accept, l2_m1_marg, l2_m2_accept,
l2_m2_marg, l2_m3_upr_accept, l2_m3_upr_marg,
l2_m3_lwr_accept, l2_m3_lwr_marg
from bsc_kpi_properties a, bsc_kpi_properties b, bsc_kpi_properties c,
bsc_kpi_properties d, bsc_kpi_properties e, bsc_kpi_properties f,
bsc_kpi_properties g, bsc_kpi_properties h
where a.property_code like 'COL_M1_LEVEL1'
and b.property_code like 'COL_M1_LEVEL2'
and c.property_code like 'COL_M2_LEVEL1'
and d.property_code like 'COL_M2_LEVEL2'
and e.property_code like 'COL_M3_LEVEL1'
and f.property_code like 'COL_M3_LEVEL2'
and g.property_code like 'COL_M3_LEVEL3'
and h.property_code like 'COL_M3_LEVEL4'
and a.indicator = p_kpi_id
and b.indicator = p_kpi_id
and c.indicator = p_kpi_id
and d.indicator = p_kpi_id
and e.indicator = p_kpi_id
and f.indicator = p_kpi_id
and g.indicator = p_kpi_id
and h.indicator = p_kpi_id;
select name || '[' || indicator || ']'
into l_kpi_name
from bsc_kpis_vl
where indicator = p_kpi_id;
SELECT data_type INTO l_data_type
FROM user_tab_columns
WHERE table_name = p_table_name
AND column_name = p_column_name;
SELECT last_updated_by, last_update_date
FROM bis_indicators_vl
WHERE short_name = cp_measure_short_name;
SELECT tl.last_updated_by, tl.last_update_date
FROM bis_indicators b, bis_indicators_tl tl
WHERE b.INDICATOR_ID = tl.indicator_id
AND b.short_name = cp_measure_short_name
AND tl.LANGUAGE = userenv('LANG');
l_db_lub BIS_INDICATORS.LAST_UPDATED_BY%TYPE;
l_db_lud BIS_INDICATORS.LAST_UPDATE_DATE%TYPE;
SELECT measure_id,measure_col
FROM bsc_sys_measures
WHERE measure_col like '%'||p_measure_col||'%';
SELECT name
FROM bsc_sys_datasets_vl
WHERE measure_id1 = p_measure_id
OR measure_id2 = p_measure_id;
SELECT COUNT(1) into l_count
FROM BSC_SYS_DATASETS_B
WHERE MEASURE_ID1 =TO_NUMBER(p_measure_id)
AND MEASURE_ID2 IS NULL;
SELECT DATASET_ID INTO l_dataset_id
FROM BSC_SYS_DATASETS_B
WHERE MEASURE_ID1 = TO_NUMBER(p_measure_id)
AND MEASURE_ID2 IS NULL;
SELECT substr(actual_data_source, 1, instr(actual_data_source, '.') -1) INTO l_retval
FROM bis_indicators
WHERE indicator_id = p_indicator_id;
SELECT
OB.NAME
FROM
BSC_KPIS_VL OB,
BSC_KPI_ANALYSIS_MEASURES_B AM,
BSC_SYS_DATASETS_B ME
WHERE
ME.DATASET_ID = p_Dataset_Id AND
AM.DATASET_ID = ME.DATASET_ID AND
OB.INDICATOR = AM.INDICATOR AND
OB.SHORT_NAME IS NOT NULL;