[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| 03-Sep-03 adrao Fixed Bug #3123509 (Update_Dataset) |
| 05-Sep-03 mdamle Fixed Bug #3123558 Added check for duplicate measure_col |
| 07-Sep-03 arhegde bug# 3123901 Propogate error to outer layers. |
| 25-SEP-03 mdamle Bug#3160325 - Sync up measures for all installed |
| languages |
| 29-SEP-03 adrao Bug#3160325 - Sync up measures for all installed |
| source languages |
| 25-SEP-03 mdamle Bug#3170184 - Check for duplicate source column by source |
| type |
| 28-OCT-03 PAJOHRI Bug #3184408, removed TRIM from Create_Measures & |
| Update_Measures API |
| 27-NOV-03 adrao Bug#3238554 - Modifed procedure Update_Measure and added |
| condition to perform incremental changes |
| 02-DEC-03 ashankar Bug#3291278 - Modifed procedure Update_Measure and created |
| cursor to get the value of the Type column from bsc_sys_meas|
| ures for the measure. |
| 11-DEC-03 PAJOHRI Bug #3309050 |
| 06-JAN-04 PAJOHRI Bug #3349897, modified procedure Update_Measures to fix |
| record l_Dataset_Rec.Bsc_Measure_Color_Formula|
| to flag prototype_flag = 4 if value is changed|
| 24-FEB-04 KYADAMAK Bug #3439942 space not allowed for PMF Measures |
| 02-MAR-04 ANKGOEL Bug #3464470 Forward port fix of bug#3450505 |
| 24-MAR-04 ADRAO Bug #3528425 Perform structural change, when Data Group is |
| changed for any measure |
| 24-MAY-04 ADRAO Bug #3628113 Removed Measure Columns based on MEASURE_ID2 |
| in Delete_Measure API |
| 27-JUL-04 sawu Added logic to set WHO columns in create/update api |
| 28-JUL-04 adrao Bug#3781176 Added logic in Delete_Measures(), whenever |
| the same source column is referenced in both |
| BSC_SYS_DATASETS_B.MEASURE_ID1/MEASURE_ID2 |
| 17-AUG-04 visuri Bug#3681116 Added logic in Update_Dataset() API to ensure |
| that numeric format change of any measure also |
| updates the default format of indicators for |
| which that measure is a default measure. |
| 24-AUG-2004 ashankar Bug#3844190 Creating unique measure col across the system. |
| 20-Dec-2004 sawu Bug#4045278: updated update_measure and update_dataset to |
| populate last_update_date from record structure|
| Updated create_measure and create_dataset to |
| populate creation_date and LUD also. |
| 20-Sep-2005 akoduri Bug#4613172: CDS type measures should not get populated into|
| bsc_db_measure_cols_tl |
| 05-JAN-06 ppandey Enh#4860106 Handled structureal and non-structural |
| formula change |
| 13-JAN-06 ppandey Enh#4860106 Reverting due to open Bug #4941403 from backend.|
| 24-JAN-06 ankgoel Bug#4954663 Show Info text for AG to PL/SQL or VB conversion|
| 04-AUG-06 akoduri Enh#5416542 Cause Effect Phase2 |
| 14-Feb-07 rkumar Bug#5877454 Changed l_indicator length to 32000 |
| 24-MAY-07 ppandey Bug#5954147 Changing goal type will reset thresholds, as |
| thresholds are at Kpi level with color enh. |
| 27-JUN-07 ashankar Bug#6134461 Filtered out P&L objectives when GOAL type is changed |
+======================================================================================+
*/
G_PKG_NAME CONSTANT varchar2(30) := 'BSC_DATASETS_PVT';
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;
if l_Dataset_Rec.Bsc_Measure_Last_Update_Date is null then
l_Dataset_Rec.Bsc_Measure_Last_Update_Date := sysdate;
select count(1)
into l_count
from BSC_SYS_MEASURES
where measure_id = l_Dataset_Rec.Bsc_Measure_Id;
select count(1) into l_count_mescol
from BSC_DB_MEASURE_COLS_VL
where upper(measure_col) = upper(l_Dataset_Rec.Bsc_Measure_Col);
insert into BSC_SYS_MEASURES( measure_id
,measure_col
,operation
,type
,min_actual_value
,max_actual_value
,min_budget_value
,max_budget_value
,random_style
,edw_flag
,edw_fact_id
,edw_meas_id
,short_name
,source
,s_color_formula
,created_by -- PMD
,creation_date -- PMD
,last_updated_by -- PMD
,last_update_date -- PMD
,last_update_login) -- PMD
values( l_Dataset_Rec.Bsc_Measure_Id
,l_Dataset_Rec.Bsc_Measure_Col
,l_Dataset_Rec.Bsc_Measure_Operation
,l_Dataset_Rec.Bsc_Meas_Type
,l_Dataset_Rec.Bsc_Measure_Min_Act_Value
,l_Dataset_Rec.Bsc_Measure_Max_Act_Value
,l_Dataset_Rec.Bsc_Measure_Min_Bud_Value
,l_Dataset_Rec.Bsc_Measure_Max_Bud_Value
,l_Dataset_Rec.Bsc_Measure_Random_Style
,0
,null
,null
,l_Dataset_Rec.Bsc_Measure_Short_Name
,l_Dataset_Rec.Bsc_Source
,l_Dataset_Rec.Bsc_Measure_Color_Formula
,l_Dataset_Rec.Bsc_Measure_Created_By -- PMD
,l_Dataset_Rec.Bsc_Measure_Creation_Date -- PMD
,l_Dataset_Rec.Bsc_Measure_Last_Update_By -- PMD
,l_Dataset_Rec.Bsc_Measure_Last_Update_Date -- PMD
,l_Dataset_Rec.Bsc_Measure_Last_Update_Login);-- PMD
insert into BSC_DB_MEASURE_COLS_TL( measure_col
,language
,source_lang
,help
,measure_group_id
,projection_id
,measure_type)
values( p_Dataset_Rec.Bsc_Measure_Col
,p_Dataset_Rec.Bsc_Language
,p_Dataset_Rec.Bsc_Source_Language
,p_Dataset_Rec.Bsc_Measure_Help
,p_Dataset_Rec.Bsc_Measure_Group_Id
,p_Dataset_Rec.Bsc_Measure_Projection_Id
,p_Dataset_Rec.Bsc_Measure_Type);
select distinct a.measure_col
,a.operation
,a.type
,a.min_actual_value
,a.max_actual_value
,a.min_budget_value
,a.max_budget_value
,a.random_style
,a.s_color_formula
,a.source
,a.created_by -- PMD
,a.creation_date -- PMD
,a.last_updated_by -- PMD
,a.last_update_date -- PMD
,a.last_update_login -- PMD
,b.projection_id
,nvl(b.measure_type, 0)
,nvl(b.measure_group_id, -1)
into x_Dataset_Rec.Bsc_Measure_Col
,x_Dataset_Rec.Bsc_Measure_Operation
,x_Dataset_Rec.Bsc_Meas_Type
,x_Dataset_Rec.Bsc_Measure_Min_Act_Value
,x_Dataset_Rec.Bsc_Measure_Max_Act_Value
,x_Dataset_Rec.Bsc_Measure_Min_Bud_Value
,x_Dataset_Rec.Bsc_Measure_Max_Bud_Value
,x_Dataset_Rec.Bsc_Measure_Random_Style
,x_Dataset_Rec.Bsc_measure_color_formula
,x_Dataset_Rec.Bsc_Source
,x_Dataset_Rec.Bsc_Measure_Created_By -- PMD
,x_Dataset_Rec.Bsc_Measure_Creation_Date -- PMD
,x_Dataset_Rec.Bsc_Measure_Last_Update_By -- PMD
,x_Dataset_Rec.Bsc_Measure_Last_Update_Date -- PMD
,x_Dataset_Rec.Bsc_Measure_Last_Update_Login -- PMD
,x_Dataset_Rec.Bsc_Measure_Projection_Id
,x_Dataset_Rec.Bsc_Measure_Type
,x_Dataset_Rec.Bsc_Measure_Group_Id
from BSC_SYS_MEASURES a
,bsc_db_measure_cols_vl b
where a.measure_id = p_Dataset_Rec.Bsc_Measure_Id
and a.measure_col = b.Measure_Col(+);
procedure Update_Measures(
p_commit IN varchar2 := FND_API.G_FALSE
,p_Dataset_Rec 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_Dataset_Rec BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type;
SELECT am.indicator, kpi.prototype_flag
FROM bsc_kpi_analysis_measures_b am,
bsc_kpis_b kpi
WHERE kpi.indicator = am.indicator
AND dataset_id = p_Dataset_Rec.Bsc_Dataset_Id;
SELECT Type
FROM BSC_SYS_MEASURES
WHERE MEASURE_ID = p_Dataset_Rec.Bsc_Measure_Id;
SELECT MEASURE_COL
INTO l_current_formula
FROM BSC_SYS_MEASURES
WHERE MEASURE_ID = p_Dataset_Rec.Bsc_Measure_Id;
select count(1) into l_count_mescol
from BSC_DB_MEASURE_COLS_VL
where upper(measure_col) = upper(p_Dataset_Rec.Bsc_Measure_Col);
l_Dataset_Rec.Bsc_Measure_Last_Update_By := p_Dataset_Rec.Bsc_Measure_Last_Update_By;
l_Dataset_Rec.Bsc_Measure_Last_Update_Date := p_Dataset_Rec.Bsc_Measure_Last_Update_Date;
l_Dataset_Rec.Bsc_Measure_Last_Update_Login := p_Dataset_Rec.Bsc_Measure_Last_Update_Login;
l_kpi_flag := BSC_DESIGNER_PVT.G_ActionFlag.GAA_Update;
l_kpi_flag := BSC_DESIGNER_PVT.G_ActionFlag.GAA_Update;
if p_Dataset_Rec.Bsc_Measure_Last_Update_By is null then
l_Dataset_Rec.Bsc_Measure_Last_Update_By := fnd_global.USER_ID;
if p_Dataset_Rec.Bsc_Measure_Last_Update_Date is null then
l_Dataset_Rec.Bsc_Measure_Last_Update_Date := SYSDATE;
if p_Dataset_Rec.Bsc_Measure_Last_Update_Login is null then
l_Dataset_Rec.Bsc_Measure_Last_Update_Login := fnd_global.LOGIN_ID;
UPDATE BSC_SYS_MEASURES
-- mdamle 03/12/2003 - PMD - Measure Definer
-- Changed set measure_col = l_Dataset_Rec.Bsc_Measure_Short_Name
SET measure_col = l_Dataset_Rec.Bsc_Measure_Col
,operation = l_Dataset_Rec.Bsc_Measure_Operation
,type = l_Dataset_Rec.Bsc_Meas_Type
,min_actual_value = l_Dataset_Rec.Bsc_Measure_Min_Act_Value
,max_actual_value = l_Dataset_Rec.Bsc_Measure_Max_Act_Value
,min_budget_value = l_Dataset_Rec.Bsc_Measure_Min_Bud_Value
,max_budget_value = l_Dataset_Rec.Bsc_Measure_Max_Bud_Value
,random_style = l_Dataset_Rec.Bsc_Measure_Random_Style
,s_color_formula = l_Dataset_Rec.Bsc_Measure_color_formula
,source = l_Dataset_Rec.Bsc_Source
,last_updated_by = l_Dataset_Rec.Bsc_Measure_Last_Update_By -- PMD
,last_update_date = l_Dataset_Rec.Bsc_Measure_Last_Update_Date -- PMD
,last_update_login = l_Dataset_Rec.Bsc_Measure_Last_Update_Login -- PMD
WHERE measure_id = l_Dataset_Rec.Bsc_Measure_Id;
end Update_Measures;
PROCEDURE Delete_Measures(
p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_Dataset_Rec 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_Count NUMBER;
SELECT COUNT(1) INTO l_Count
FROM BSC_SYS_MEASURES
WHERE MEASURE_ID = p_Dataset_Rec.Bsc_Measure_Id;
SELECT COUNT(1) INTO l_Count
FROM BSC_SYS_MEASURES
WHERE MEASURE_ID = p_Dataset_Rec.Bsc_Measure_Id2;
SELECT COUNT(DATASET_ID)
INTO l_Count
FROM BSC_SYS_DATASETS_B
WHERE MEASURE_ID1 = p_Dataset_Rec.Bsc_Measure_Id
OR MEASURE_ID2 = p_Dataset_Rec.Bsc_Measure_Id;
SELECT MEASURE_COL INTO l_Measure_Col
FROM BSC_SYS_MEASURES
WHERE MEASURE_ID = p_Dataset_Rec.Bsc_Measure_Id;
DELETE FROM BSC_SYS_MEASURES
WHERE MEASURE_ID = p_Dataset_Rec.Bsc_Measure_Id;
SELECT COUNT(1) INTO l_count
FROM BSC_SYS_MEASURES
WHERE SOURCE = BSC_BIS_MEASURE_PUB.c_BSC
AND MEASURE_COL LIKE '%' || l_Measure_Col || '%';
SELECT COUNT(1) INTO l_count
FROM BSC_DB_MEASURE_COLS_VL
WHERE MEASURE_COL = l_Measure_Col;
BSC_DB_MEASURE_COLS_PKG.delete_row(l_Measure_Col);
SELECT COUNT(DATASET_ID)
INTO l_Count
FROM BSC_SYS_DATASETS_B
WHERE MEASURE_ID1 = p_Dataset_Rec.Bsc_Measure_Id2
OR MEASURE_ID2 = p_Dataset_Rec.Bsc_Measure_Id2;
SELECT MEASURE_COL INTO l_Measure_Col
FROM BSC_SYS_MEASURES
WHERE MEASURE_ID = p_Dataset_Rec.Bsc_Measure_Id2;
DELETE FROM BSC_SYS_MEASURES
WHERE MEASURE_ID = p_Dataset_Rec.Bsc_Measure_Id2;
SELECT COUNT(1) INTO l_Count
FROM BSC_SYS_MEASURES
WHERE SOURCE = BSC_BIS_MEASURE_PUB.c_BSC
AND MEASURE_COL LIKE '%' || l_Measure_Col || '%';
SELECT COUNT(1) INTO l_count
FROM BSC_DB_MEASURE_COLS_VL
WHERE MEASURE_COL = l_Measure_Col;
BSC_DB_MEASURE_COLS_PKG.delete_row(l_Measure_Col);
END Delete_Measures;
procedure Delete_Formats(
p_commit IN varchar2 := FND_API.G_FALSE
,p_Dataset_Rec 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
begin
if (p_commit = FND_API.G_TRUE) then
commit;
end Delete_Formats;
if l_Dataset_Rec.Bsc_Dataset_Last_Update_By is null then
l_Dataset_Rec.Bsc_Dataset_Last_Update_By := fnd_global.USER_ID;
if l_Dataset_Rec.Bsc_Dataset_Last_Update_Login is null then
l_Dataset_Rec.Bsc_Dataset_Last_Update_Login := fnd_global.LOGIN_ID;
if l_Dataset_Rec.Bsc_Dataset_Last_Update_Date is null then
l_Dataset_Rec.Bsc_Dataset_Last_Update_Date := sysdate;
select count(1)
into l_count
from BSC_SYS_DATASETS_B
where dataset_id = l_Dataset_Rec.Bsc_Dataset_Id;
insert into BSC_SYS_DATASETS_B( dataset_id
,measure_id1
,operation
,measure_id2
,format_id
,color_method
,projection_flag
,edw_flag
,autoscale_flag
,source
,created_by -- PMD
,creation_date -- PMD
,last_updated_by -- PMD
,last_update_date -- PMD
,last_update_login) -- PMD
values( l_Dataset_Rec.Bsc_Dataset_Id
,l_Dataset_Rec.Bsc_Measure_Id
-- mdamle 03/12/2003 - PMD - Measure Definer
-- Changed from Measure_operation to Dataset_Operation
,l_Dataset_Rec.Bsc_Dataset_operation
,l_Dataset_Rec.Bsc_Measure_Id2
,l_Dataset_Rec.Bsc_Dataset_Format_Id
,l_Dataset_Rec.Bsc_Dataset_Color_Method
,l_Dataset_Rec.Bsc_Dataset_Projection_Flag
,0
,l_Dataset_Rec.Bsc_Dataset_Autoscale_Flag
,l_Dataset_Rec.Bsc_Source
,l_Dataset_Rec.Bsc_Dataset_Created_By -- PMD
,l_Dataset_Rec.Bsc_Dataset_Creation_Date -- PMD
,l_Dataset_Rec.Bsc_Dataset_Last_Update_By -- PMD
,l_Dataset_Rec.Bsc_Dataset_Last_Update_Date -- PMD
,l_Dataset_Rec.Bsc_Dataset_Last_Update_Login);-- PMD
insert into BSC_SYS_DATASETS_TL( dataset_id
,language
,source_lang
,name
,help
,y_axis_title)
select l_Dataset_Rec.Bsc_Dataset_Id
,L.LANGUAGE_CODE
,userenv('LANG')
,l_Dataset_Rec.Bsc_Dataset_Name
,l_Dataset_Rec.Bsc_Dataset_Help
,l_Dataset_Rec.Bsc_y_axis_title
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from BSC_SYS_DATASETS_TL T
where T.dataset_id = l_Dataset_Rec.Bsc_Dataset_Id
and T.LANGUAGE = L.LANGUAGE_CODE);
select distinct measure_id1
,operation
,measure_id2
,format_id
,color_method
,projection_flag
,autoscale_flag
,name
,help
,y_axis_title
,source
,created_by -- PMD
,creation_date -- PMD
,last_updated_by -- PMD
,last_update_date -- PMD
,last_update_login -- PMD
into x_Dataset_Rec.Bsc_Measure_Id
-- mdamle 03/12/2003 - PMD - Measure Definer
-- Changed from Measure_operation to Dataset_Operation
,x_Dataset_Rec.Bsc_Dataset_Operation
,x_Dataset_Rec.Bsc_Measure_Id2
,x_Dataset_Rec.Bsc_Dataset_Format_Id
,x_Dataset_Rec.Bsc_Dataset_Color_Method
,x_Dataset_Rec.Bsc_Dataset_Projection_Flag
,x_Dataset_Rec.Bsc_Dataset_Autoscale_Flag
,x_Dataset_Rec.Bsc_Dataset_Name
,x_Dataset_Rec.Bsc_Dataset_Help
,x_Dataset_Rec.Bsc_y_axis_title
,x_Dataset_Rec.Bsc_Source
,x_Dataset_Rec.Bsc_Dataset_Created_By -- PMD
,x_Dataset_Rec.Bsc_Dataset_Creation_Date -- PMD
,x_Dataset_Rec.Bsc_Dataset_Last_Update_By -- PMD
,x_Dataset_Rec.Bsc_Dataset_Last_Update_Date -- PMD
,x_Dataset_Rec.Bsc_Dataset_Last_Update_Login -- PMD
from BSC_SYS_DATASETS_VL
where dataset_id = p_Dataset_Rec.Bsc_Dataset_Id;
procedure Update_Dataset(
p_commit IN varchar2 := FND_API.G_FALSE
, p_Dataset_Rec 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_Dataset_Rec BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type;
SELECT DISTINCT INDICATOR
FROM BSC_KPI_ANALYSIS_MEASURES_b
WHERE DATASET_ID =p_Dataset_Rec.Bsc_Dataset_Id;
SELECT b.indicator
FROM bsc_kpi_analysis_measures_b b,
bsc_kpis_b a
WHERE a.indicator =b.indicator
AND a.config_type <>3
AND b.dataset_id = p_Dataset_Rec.Bsc_Dataset_Id;
l_Dataset_Rec.Bsc_Dataset_Last_Update_By := p_Dataset_Rec.Bsc_Dataset_Last_Update_By;
l_Dataset_Rec.Bsc_Dataset_Last_Update_Date := p_Dataset_Rec.Bsc_Dataset_Last_Update_Date;
l_Dataset_Rec.Bsc_Dataset_Last_Update_Login := p_Dataset_Rec.Bsc_Dataset_Last_Update_Login;
if p_Dataset_Rec.Bsc_Dataset_Last_Update_By is null then
l_Dataset_Rec.Bsc_Dataset_Last_Update_By := fnd_global.USER_ID;
if p_Dataset_Rec.Bsc_Dataset_Last_Update_Date is null then
l_Dataset_Rec.Bsc_Dataset_Last_Update_Date := SYSDATE;
if p_Dataset_Rec.Bsc_Dataset_Last_Update_Login is null then
l_Dataset_Rec.Bsc_Dataset_Last_Update_Login := fnd_global.LOGIN_ID;
update BSC_SYS_DATASETS_B
set operation = l_Dataset_Rec.Bsc_Dataset_Operation
,format_id = l_Dataset_Rec.Bsc_Dataset_Format_Id
,color_method = l_Dataset_Rec.Bsc_Dataset_Color_Method
,projection_flag = l_Dataset_Rec.Bsc_Dataset_Projection_Flag
,autoscale_flag = l_Dataset_Rec.Bsc_Dataset_Autoscale_Flag
,measure_id1 = l_Dataset_Rec.Bsc_Measure_Id
,measure_id2 = l_Dataset_Rec.Bsc_Measure_Id2
,source = l_Dataset_Rec.Bsc_Source
,last_updated_by = l_Dataset_Rec.Bsc_Dataset_Last_Update_By -- PMD
,last_update_date = l_Dataset_Rec.Bsc_Dataset_Last_Update_Date -- PMD
,last_update_login = l_Dataset_Rec.Bsc_Dataset_Last_Update_Login -- PMD
where dataset_id = l_Dataset_Rec.Bsc_Dataset_Id;
update BSC_SYS_DATASETS_TL
set name = l_Dataset_Rec.Bsc_Dataset_Name
,help = l_Dataset_Rec.Bsc_Dataset_Help
,y_axis_title = l_Dataset_Rec.Bsc_y_axis_title
,source_lang = l_Dataset_Rec.Bsc_Source_Language
where dataset_id = l_Dataset_Rec.Bsc_Dataset_Id
and l_Dataset_Rec.Bsc_Source_Language in (LANGUAGE, SOURCE_LANG);
SELECT kpi_measure_id
INTO l_kpi_measure_id
FROM bsc_kpi_analysis_measures_b
WHERE indicator = l_indicator_table(i)
AND dataset_id = p_Dataset_Rec.Bsc_Dataset_Id;
BSC_COLOR_RANGES_PUB.Delete_Color_Prop_Ranges (p_objective_id => l_indicator_table(i)
,p_kpi_measure_id => l_kpi_measure_id
,p_cascade_shared => TRUE
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
end Update_Dataset;
procedure Delete_Dataset(
p_commit IN varchar2 := FND_API.G_FALSE
,p_Dataset_Rec 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
cursor indicators_cursor IS
select distinct k.name
from bsc_kpi_analysis_measures_vl am, bsc_kpis_vl k
where am.indicator = k.indicator
and dataset_id = p_Dataset_Rec.Bsc_Dataset_Id;
SELECT
short_name
FROM
bis_indicators
WHERE dataset_id = l_dataset_id;
FND_MESSAGE.SET_NAME('BSC','BSC_DELETE_MEASURE_IND_ERR_TXT');
DELETE FROM bis_custom_cause_effect_rels
WHERE cause_short_name = l_short_name OR effect_short_name = l_short_name;
BSC_CAUSE_EFFECT_REL_PUB.Delete_All_Cause_Effect_Rels(
p_commit => p_commit
,p_indicator => p_Dataset_Rec.Bsc_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);
delete from BSC_SYS_DATASETS_B
where dataset_id = p_Dataset_Rec.Bsc_Dataset_Id;
delete from BSC_SYS_DATASETS_TL
where dataset_id = p_Dataset_Rec.Bsc_Dataset_Id;
end Delete_Dataset;
select count(1)
into l_count
from BSC_SYS_DATASET_CALC
where dataset_id = p_Dataset_Rec.Bsc_Dataset_Id
and disabled_calc_id = p_Dataset_Rec.Bsc_Disabled_Calc_Id;
insert into BSC_SYS_DATASET_CALC( dataset_id
,disabled_calc_id)
values( p_Dataset_Rec.Bsc_Dataset_Id
,p_Dataset_Rec.Bsc_Disabled_Calc_Id);
select distinct disabled_calc_id
into x_Dataset_Rec.Bsc_Disabled_Calc_Id
from BSC_SYS_DATASET_CALC
where dataset_id = x_Dataset_Rec.Bsc_Dataset_Id;
procedure Update_Dataset_Calc(
p_commit IN varchar2 := FND_API.G_FALSE
,p_Dataset_Rec 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_count number;
update BSC_SYS_DATASET_CALC
set disabled_calc_id = p_Dataset_Rec.Bsc_Disabled_Calc_Id
where dataset_id = p_Dataset_Rec.Bsc_Dataset_Id;
end Update_Dataset_Calc;
procedure Delete_Dataset_Calc(
p_commit IN varchar2 := FND_API.G_FALSE
,p_Dataset_Rec 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_count number;
delete from BSC_SYS_DATASET_CALC
where dataset_id = p_Dataset_Rec.Bsc_Dataset_Id;
end Delete_Dataset_Calc;
select count(1)
into l_count
-- from BSC_SYS_MEASURES
from bis_indicators i, bsc_sys_datasets_vl d
where short_name = p_Measure_Name
and i.dataset_id = d.dataset_id;
UPDATE bsc_sys_datasets_tl
SET name = p_Dataset_Rec.Bsc_Dataset_Name
,help = p_Dataset_Rec.Bsc_Dataset_Help
,y_axis_title = p_Dataset_Rec.Bsc_y_axis_title
,source_lang = userenv('LANG')
WHERE dataset_id = p_Dataset_Rec.Bsc_Dataset_Id
AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
UPDATE BSC_SYS_DATASETS_TL
SET name = p_Dataset_Rec.Bsc_Dataset_Name
,help = p_Dataset_Rec.Bsc_Dataset_Help
,y_axis_title = p_Dataset_Rec.Bsc_y_axis_title
,source_lang = p_source_lang
WHERE dataset_id = p_Dataset_Rec.Bsc_Dataset_Id
and LANGUAGE = p_lang;