The following lines contain the word 'select', 'insert', 'update' or 'delete':
REM | Create_KPI_Analysis_Options & Update_KPI |
REM | 28-Aug-2003 Adeulgao fixed bug#3108877 |
REM | 02-Sep-2003 ADRAO fixed bug#3123858 |
REM | 10-Sep-2003 ADEULGAO fixed bug#3126401 |
REM | 11-Sep-2003 ADEULGAO fixed bug#3136397 |
REM | 11-Sep-2003 ADEULGAO fixed bug#3139925 |
REM | 13-Sep-2003 mahrao fixed bug#3099977, used p_create_view flag in impo.|
REM | 20-OCT-2003 PAJOHRI Bug#3180374, and modularization of the code |
REM | 20-OCT-2003 PAJOHRI Bug#3179995, added two new procedures |
REM | Delete_Dim_Objs_In_DSet, Create_Dim_Objs_In_DSet |
REM | 20-OCT-2003 PAJOHRI Bug #3179995 |
REM | 04-NOV-2003 PAJOHRI Bug #3152258 |
REM | 14-NOV-2003 PAJOHRI Bug #3248729 |
REM | 08-DEC-2003 KYADAMAK Bug #3225685 |
REM | 07-JAN-2004 PAJOHRI Bug #3343860, created two new procudures |
REM | Create_Default_Kpi_Dim_Object & Delete_Default_Kpi_Dim_Object|
REM | to create and delete default row in BSC_KPI_DIM_LEVELS_VL |
REM | 16-JAN-2004 PAJOHRI Bug #3372305 |
REM | 29-JAN-2004 PAJOHRI Bug #3404081 |
REM | 15-MAR-2004 PAJOHRI Bug #3504996, Assign_DSet_Analysis_Options |
REM | procedure is modified, to flag stuct. changes. |
REM | 30-Mar-2004 ADEULGAO fixed DBI Report Issues |
REM | added apis 1. Is_Pmv_Viewby_Report() |
REM | 2. is_Abstract_Pmv_Dimension() |
REM | removed condition IF (l_view_by = 'Y') |
REM | in Get_Default_Viewby_For_Measure() |
REM | 12-APR-2004 PAJOHRI Bug #3426566, added conditions to filter those |
REM | Dimension whose Short_Name = 'UNASSIGNED' |
REM | 13-APR-2004 ASHANKAR BUG#3565772 Modified the fucntions |
REM | Is_Time_In_Dim_Object,Is_Time_With_Measure |
REM | 16-APR-2004 ASHANKAR BUG#3550054 added the validate_list_button |
REM | function |
REM | 23-APR-2004 ASHANKAR Bug #3518610,Added the fucntion Validate |
REM | listbutton |
REM | 01-JUN-2004 ADRAO Fixed for Bug#3663301, initiated an Action Flag |
REM | Change when Default Measure is changed for KPI |
REM | Changed Update_Kpi API |
REM | 14-JUN-2004 ADRAO added Short_Name to Analysis Option for |
REM | Enh#3540302 (ADMINISTRATOR TO ADD KPI TO KPI REGION)|
REM | 17-AUG-2004 WLEUNG added function Remove_Empty_Dims_For_DimSet |
REM | Bug #3784852 |
REM | 29-SEP-2004 ashankar added modules is_Period_Circular, |
REM | Parse_Base_Periods and Find_Period_CircularRef |
REM | for bug#3908204 |
REM | 10-OCT-2004 ashankar Moved Parse_Base_Periods to BSC_UTILITY package |
REM | and renamed it to Parse_String to make it Generic|
REM | enough.This was done as per the review comment |
REM | 18-JAN-2005 WLEUNG bug 4036171 fix Get_Default_Viewby_For_Measure |
REM | 21-FEB-2005 ankagarw enh# 3862703 |
REM | 11-APR-2005 kyadamak bug#4290070 Not validation views for rolling dims|
REM | 18-Jul-2005 ppandey Enh #4417483, Restrict Internal/Calendar Dims |
REM | 22-AUG-2005 ashankar Bug#4220400 Modifed the UPDATE_KPI API |
REM | 19-SEP-2005 adrao fixed Bug#4615361 modified API Update_Dim_Set |
REM | 06-Jan-2006 akoduri Enh#4739401 - Hide Dimensions/Dim Objects |
REM | 24-Jan-2006 akoduri Bug#4958055 Dgrp dimension not getting deleted |
REM | while disassociating from objective |
REM | 15-FEB-2006 akoduri Bug#4305536 Support new attribute type in |
REM | Objective designer |
REM | 11-APR-2006 visuri Bug#5151997 Report not going in Prototype after |
REM | adding PMF Dim Obj |
REM | 19-APR-2006 visuri Bug#5080308 Commented view by validation |
REM | 31-Jan-2007 akoduri Enh #5679096 Migration of multibar functionality |
REM | from VB to Html |
REM | 13-APR-2007 ankgoel Bug#5943068 Impact on common dimension by dim |
REM | reorder in a dim set |
REM | 09-Mar-2007 akoduri Bug#5925299 Key Items are not retained in update |
REM | and in reordering of dim objects in dimension set|
REM | 20-APR-2007 vtulasi Warning and caching issue |
REM | 07-JUN-2007 vtulasi Prototype Flag issue |
REM | 06-JUN-2007 akoduri Bug 5958688 Enable YTD as default at KPI |
REM +=======================================================================+
*/
--PMV abastract dimension type
TIME_COMP_TYPE CONSTANT VARCHAR2(100) := 'TIME_COMPARISON_TYPE';
SELECT
d.dim_level_id
INTO
l_dim_level_id
FROM
bsc_kpi_dim_levels_b kd,
bsc_sys_dim_levels_b d
WHERE
kd.indicator = p_indicator
AND kd.level_table_name = d.level_table_name
AND kd.dim_set_id = p_dim_set_id
AND kd.level_table_name = p_level_table_name;
BSC_DIMENSION_SETS_PUB.Update_Dim_Levels (
p_commit => FND_API.G_FALSE
,p_Dim_Set_Rec => l_Dim_Set_Rec
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
BSC_DIMENSION_SETS_PUB.Update_Dim_Level_Properties (
p_commit => FND_API.G_FALSE
,p_Dim_Set_Rec => l_Dim_Set_Rec
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
SELECT Indicator_Type
INTO l_Indic_Type
FROM BSC_KPIS_B
WHERE Indicator = p_Kpi_ID;
SELECT COUNT(*) INTO l_Count
FROM BSC_KPI_ANALYSIS_OPTIONS_B
WHERE Indicator = p_Kpi_Id
AND Analysis_Group_Id = p_Analysis_Group_ID
AND Option_Id = p_Option_ID
AND Parent_Option_Id = x_Parent_Option_ID;
SELECT COUNT(*) INTO l_Count
FROM BSC_KPI_ANALYSIS_OPTIONS_B
WHERE Indicator = p_Kpi_Id
AND Analysis_Group_Id = p_Analysis_Group_ID
AND Option_Id = p_Option_ID
AND Parent_Option_Id = x_Parent_Option_ID
AND Grandparent_Option_Id = x_GrandParent_Option_ID;
SELECT Analysis_Group_Id
, Num_Of_Options
, Dependency_Flag
FROM BSC_KPI_ANALYSIS_GROUPS
WHERE Indicator = p_kpi_id
ORDER BY Analysis_Group_Id;
SELECT indicator
FROM BSC_KPIS_B
WHERE Source_Indicator = p_kpi_id
AND Prototype_Flag <> 2;
SELECT COUNT(*) INTO l_Count
FROM BSC_KPI_DIM_LEVELS_B
WHERE Indicator = l_bsc_dimset_rec.Bsc_Kpi_Id
AND Dim_Set_Id = l_bsc_dimset_rec.Bsc_Dim_Set_Id;
PROCEDURE Delete_Default_Kpi_Dim_Object
( p_commit IN VARCHAR2 := FND_API.G_TRUE
, p_kpi_id IN NUMBER
, p_dim_set_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
) IS
l_Count NUMBER;
SELECT indicator
FROM BSC_KPIS_B
WHERE Source_Indicator = p_kpi_id
AND Prototype_Flag <> 2;
BSC_DIMENSION_SETS_PUB.Delete_Dim_Levels
( p_commit => FND_API.G_FALSE
, p_Dim_Set_Rec => l_bsc_dimset_rec
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
BSC_DIMENSION_SETS_PUB.Delete_Dim_Levels
( p_commit => FND_API.G_FALSE
, p_Dim_Set_Rec => l_bsc_dimset_rec
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
x_msg_data := x_msg_data||' -> BSC_BIS_KPI_MEAS_PUB.Delete_Default_Kpi_Dim_Object ';
x_msg_data := SQLERRM||' at BSC_BIS_KPI_MEAS_PUB.Delete_Default_Kpi_Dim_Object ';
x_msg_data := x_msg_data||' -> BSC_BIS_KPI_MEAS_PUB.Delete_Default_Kpi_Dim_Object ';
x_msg_data := SQLERRM||' at BSC_BIS_KPI_MEAS_PUB.Delete_Default_Kpi_Dim_Object ';
END Delete_Default_Kpi_Dim_Object;
SELECT A.Dim_Group_Id
, A.Dim_Group_Index
, B.Short_Name
FROM BSC_KPI_DIM_GROUPS A
, BSC_SYS_DIM_GROUPS_VL B
WHERE A.Indicator = p_kpi_id
AND A.Dim_Set_Id = p_dim_set_id
AND A.Dim_Group_ID = B.Dim_Group_ID
ORDER BY A.Dim_Group_Index;
SELECT indicator
FROM BSC_KPIS_B
WHERE Source_Indicator = p_kpi_id
AND Prototype_Flag <> 2;
BSC_DIMENSION_SETS_PUB.Update_Dim_Group_In_Dset
( p_commit => FND_API.G_FALSE
, p_Dim_Set_Rec => l_bsc_dimset_rec
, p_create_Dim_Lev_Grp => FALSE
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
BSC_DIMENSION_SETS_PUB.Update_Dim_Group_In_Dset
( p_commit => FND_API.G_FALSE
, p_Dim_Set_Rec => l_bsc_dimset_rec
, p_create_Dim_Lev_Grp => FALSE
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
BSC_DIMENSION_SETS_PUB.Update_Dim_Group_In_Dset
( p_commit => FND_API.G_FALSE
, p_Dim_Set_Rec => l_bsc_dimset_rec
, p_create_Dim_Lev_Grp => FALSE
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
BSC_DIMENSION_SETS_PUB.Update_Dim_Group_In_Dset
( p_commit => FND_API.G_FALSE
, p_Dim_Set_Rec => l_bsc_dimset_rec
, p_create_Dim_Lev_Grp => FALSE
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
, p_delete => TRUE
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
, p_delete => TRUE
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
SELECT Attribute1
FROM AK_REGIONS
WHERE Region_Code = c_region_code;
this is called from both create and update KPI Analysis options
*******************************************************************/
PROCEDURE Get_Default_Viewby_For_Measure
( p_DimObj_ViewBy_Tbl IN BSC_BIS_KPI_MEAS_PUB.DimObj_Viewby_Tbl_Type
, x_dim_objects OUT NOCOPY VARCHAR
, x_defaults OUT NOCOPY VARCHAR
, x_view_bys OUT NOCOPY VARCHAR
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
)IS
l_default_level_set BOOLEAN := FALSE;
SELECT DISTINCT dim_group_id
FROM BSC_KPI_DIM_GROUPS
WHERE Dim_Set_Id = p_dim_set_id
AND Indicator = p_Kpi_Id;
SELECT C.Source Source
FROM BSC_SYS_DIM_GROUPS_VL A
, BSC_SYS_DIM_LEVELS_BY_GROUP B
, BSC_SYS_DIM_LEVELS_B C
WHERE A.Dim_Group_ID = B.Dim_Group_ID
AND C.Dim_Level_ID = B.Dim_Level_ID
AND A.Short_Name <> BSC_BIS_DIMENSION_PUB.Unassigned_Dim
AND A.Dim_Group_ID = l_dimension_id;
SELECT short_name INTO l_temp_var
FROM BSC_SYS_DIM_GROUPS_VL
WHERE dim_group_id = l_dimension_id;
BSC_BIS_DIMENSION_PUB.Delete_Dimension
( p_commit => FND_API.G_FALSE
, p_dim_short_name => l_temp_var
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
SELECT Short_Name
INTO x_measure_short_name
FROM BSC_SYS_MEASURES
, BSC_SYS_DATASETS_VL
WHERE MEASURE_ID = MEASURE_ID1
AND DATASET_ID = p_data_set_id;
SELECT Short_Name
INTO x_measure_short_name
FROM BSC_SYS_MEASURES
, BSC_SYS_DATASETS_VL
WHERE MEASURE_ID = MEASURE_ID1
AND DATASET_ID = p_data_set_id;
SELECT indicator
FROM BSC_KPIS_B
WHERE Source_Indicator = p_kpi_id
AND Prototype_Flag <> 2;
SELECT COUNT(*) INTO l_count
FROM BSC_KPIS_B
WHERE indicator = p_kpi_id;
FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_DELETE_MESSAGE');
SELECT share_flag
INTO l_count
FROM BSC_KPIS_B
WHERE indicator = l_bsc_dimset_rec.Bsc_Kpi_Id ;
, p_Entity_Action_Type => BSC_UTILITY.c_UPDATE
, x_Return_Status => x_return_status
, x_Msg_Count => x_msg_count
, x_Msg_Data => x_msg_data
);
SELECT NVL(MAX(dim_set_id) + 1, 0)
INTO l_bsc_dimset_rec.bsc_dim_set_id
FROM BSC_KPI_DIM_SETS_VL
WHERE indicator = l_bsc_dimset_rec.bsc_kpi_id;
BSC_DIMENSION_SETS_PUB.Create_Bsc_Kpi_Dim_Sets_Tl--INSERT INTO BSC_KPI_DIM_SETS_TL
( p_commit => FND_API.G_FALSE
, p_Dim_Set_Rec => l_bsc_dimset_rec
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
SELECT NVL(MAX(dim_set_id) + 1, 0)
INTO l_bsc_dimset_rec.bsc_dim_set_id
FROM BSC_KPI_DIM_SETS_VL
WHERE indicator = l_bsc_dimset_rec.bsc_kpi_id;
BSC_DIMENSION_SETS_PUB.Create_Bsc_Kpi_Dim_Sets_Tl--INSERT INTO BSC_KPI_DIM_SETS_TL
( p_commit => FND_API.G_FALSE
, p_Dim_Set_Rec => l_bsc_dimset_rec
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
SELECT COUNT(0) INTO l_count_independent_dim_obj
FROM BSC_KPI_DIM_LEVELS_B
WHERE INDICATOR = p_kpi_id
AND DIM_SET_ID = l_bsc_dimset_rec.bsc_dim_set_id;
SELECT NAME INTO l_kpi_name
FROM BSC_KPIS_VL
WHERE INDICATOR = p_Kpi_Id;
UPDATE DIMENSION-SETS
*********************************************************************************/
PROCEDURE Update_Dim_Set
( p_commit IN VARCHAR2 := FND_API.G_TRUE
, p_kpi_id IN NUMBER
, p_dim_set_id IN NUMBER
, p_display_name IN VARCHAR2
, p_assign_dim_names IN VARCHAR2
, p_unassign_dim_names IN VARCHAR2
, p_time_stamp IN VARCHAR2 := NULL -- Granular Locking
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
)IS
l_bsc_dimset_rec BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type;
, p_Entity_Action_Type => BSC_UTILITY.c_UPDATE
, x_Return_Status => x_return_status
, x_Msg_Count => x_msg_count
, x_Msg_Data => x_msg_data
);
BSC_BIS_KPI_MEAS_PUB.Update_Dim_Set
( p_commit => FND_API.G_FALSE
, p_kpi_id => p_kpi_id
, p_dim_set_id => p_dim_set_id
, p_display_name => p_display_name
, 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 COUNT(0) INTO l_count_independent_dim_obj
FROM BSC_KPI_DIM_LEVELS_B
WHERE INDICATOR = p_kpi_id
AND DIM_SET_ID = p_dim_set_id;
SELECT NAME INTO l_kpi_name
FROM BSC_KPIS_VL
WHERE INDICATOR = p_Kpi_Id;
x_msg_data := x_msg_data||' -> BSC_BIS_KPI_MEAS_PUB.Update_Dim_Set ';
x_msg_data := SQLERRM||' at BSC_BIS_KPI_MEAS_PUB.Update_Dim_Set ';
x_msg_data := x_msg_data||' -> BSC_BIS_KPI_MEAS_PUB.Update_Dim_Set ';
x_msg_data := SQLERRM||' at BSC_BIS_KPI_MEAS_PUB.Update_Dim_Set ';
END Update_Dim_Set;
UPDATE DIMENSION-SETS
*********************************************************************************/
PROCEDURE Update_Dim_Set
( p_commit IN VARCHAR2 := FND_API.G_TRUE
, p_kpi_id IN NUMBER
, p_dim_set_id IN NUMBER
, p_display_name IN VARCHAR2
, p_dim_short_names IN VARCHAR2
, p_time_stamp IN VARCHAR2 := NULL -- Granular Locking
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
)IS
l_unassign_dim_names VARCHAR2(32000);
SELECT short_name FROM BSC_SYS_DIM_GROUPS_VL
WHERE dim_group_id IN (SELECT dim_group_id
FROM BSC_KPI_DIM_GROUPS
WHERE dim_set_id = p_dim_set_id
AND indicator = p_kpi_id);
SELECT COUNT(DIM_SET_ID)
INTO l_count
FROM BSC_KPI_DIM_SETS_VL
WHERE DIM_SET_ID = p_dim_set_id AND INDICATOR = p_kpi_id;
FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_DELETE_MESSAGE');
SELECT MEANING
INTO l_meaning
FROM BSC_LOOKUPS
WHERE LOOKUP_TYPE = 'BSC_UI_COMMON' AND LOOKUP_CODE = 'DIM_SET' ;
, p_Entity_Action_Type => BSC_UTILITY.c_UPDATE
, x_Return_Status => x_return_status
, x_Msg_Count => x_msg_count
, x_Msg_Data => x_msg_data
);
BSC_BIS_KPI_MEAS_PUB.Update_Dim_Set
( p_commit => FND_API.G_FALSE
, p_kpi_id => p_kpi_id
, p_dim_set_id => p_dim_set_id
, p_display_name => p_display_name
, p_assign_dim_names => p_dim_short_names
, p_unassign_dim_names => l_unassign_dim_names
, p_time_stamp => p_time_stamp
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
x_msg_data := x_msg_data||' -> BSC_BIS_KPI_MEAS_PUB.Update_Dim_Set ';
x_msg_data := SQLERRM||' at BSC_BIS_KPI_MEAS_PUB.Update_Dim_Set ';
x_msg_data := x_msg_data||' -> BSC_BIS_KPI_MEAS_PUB.Update_Dim_Set ';
x_msg_data := SQLERRM||' at BSC_BIS_KPI_MEAS_PUB.Update_Dim_Set ';
END Update_Dim_Set;
UPDATE DIMENSION-SET
*********************************************************************************/
PROCEDURE Update_Dim_Set
( p_commit IN VARCHAR2 := FND_API.G_TRUE
, p_kpi_id IN NUMBER
, p_dim_set_id IN NUMBER
, p_display_name IN VARCHAR2
, p_time_stamp IN VARCHAR2 := NULL -- Granular Locking
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
)IS
l_bsc_dimset_rec BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type;
SELECT indicator
FROM BSC_KPIS_B
WHERE Source_Indicator = p_kpi_id
AND Prototype_Flag <> 2;
SAVEPOINT UpdateBSCDimSetPMD;
SELECT share_flag
INTO l_count
FROM BSC_KPIS_B
WHERE indicator = l_bsc_dimset_rec.Bsc_Kpi_Id ;
SELECT COUNT(*) INTO l_count
FROM BSC_KPI_DIM_SETS_VL
WHERE indicator = l_bsc_dimset_rec.Bsc_Kpi_Id
AND dim_set_id = l_bsc_dimset_rec.Bsc_Dim_Set_Id;
BSC_DIMENSION_SETS_PUB.Update_Bsc_Kpi_Dim_Sets_Tl
( p_commit => FND_API.G_FALSE
, p_Dim_Set_Rec => l_bsc_dimset_rec
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
SELECT COUNT(*) INTO l_count FROM BSC_KPI_DIM_SETS_VL
WHERE indicator = l_bsc_dimset_rec.Bsc_Kpi_Id
AND dim_set_id = l_bsc_dimset_rec.Bsc_Dim_Set_Id;
BSC_DIMENSION_SETS_PUB.Update_Bsc_Kpi_Dim_Sets_Tl
( p_commit => FND_API.G_FALSE
, p_Dim_Set_Rec => l_bsc_dimset_rec
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
ROLLBACK TO UpdateBSCDimSetPMD;
ROLLBACK TO UpdateBSCDimSetPMD;
ROLLBACK TO UpdateBSCDimSetPMD;
x_msg_data := x_msg_data||' -> BSC_BIS_KPI_MEAS_PUB.Update_Dim_Set ';
x_msg_data := SQLERRM||' at BSC_BIS_KPI_MEAS_PUB.Update_Dim_Set ';
ROLLBACK TO UpdateBSCDimSetPMD;
x_msg_data := x_msg_data||' -> BSC_BIS_KPI_MEAS_PUB.Update_Dim_Set ';
x_msg_data := SQLERRM||' at BSC_BIS_KPI_MEAS_PUB.Update_Dim_Set ';
END Update_Dim_Set;
SELECT short_name
FROM bsc_kpis_b
WHERE indicator = p_Indicator;
SELECT count(1)
INTO l_count
FROM BSC_KPI_DIM_LEVELS_VL
WHERE INDICATOR = p_Indicator
AND DIM_SET_ID = p_DimSetId
AND LEVEL_SOURCE = BSC_BIS_MEASURE_PUB.c_BSC;
DELETE DIMENSION-SETS
*********************************************************************************/
PROCEDURE Delete_Dim_Set
( p_commit IN VARCHAR2 := FND_API.G_TRUE
, p_kpi_id IN NUMBER
, p_dim_set_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
)IS
l_bsc_dimset_rec BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type;
SELECT indicator
FROM BSC_KPIS_B
WHERE Source_Indicator = p_kpi_id
AND Prototype_Flag <> 2;
SAVEPOINT DeleteBSCDimSetPMD;
FND_MESSAGE.SET_NAME('BSC', 'BSC_DIM_SET_0_NO_DELETE');
SELECT share_flag
INTO l_count
FROM BSC_KPIS_B
WHERE indicator = l_bsc_dimset_rec.Bsc_Kpi_Id ;
SELECT COUNT(*) INTO l_count
FROM BSC_KPI_DIM_SETS_VL
WHERE indicator = l_bsc_dimset_rec.Bsc_Kpi_Id;
BSC_DIMENSION_SETS_PUB.Delete_Dim_Group_In_Dset
( p_commit => FND_API.G_FALSE
, p_Dim_Set_Rec => l_bsc_dimset_rec
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
SELECT COUNT(*) INTO l_count
FROM BSC_KPI_DIM_GROUPS
WHERE indicator = l_bsc_dimset_rec.Bsc_Kpi_Id
AND dim_set_id = p_dim_set_id;
BSC_DIMENSION_SETS_PUB.Delete_Dim_Group_In_Dset
( p_commit => FND_API.G_FALSE
, p_Dim_Set_Rec => l_bsc_dimset_rec
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
ROLLBACK TO DeleteBSCDimSetPMD;
ROLLBACK TO DeleteBSCDimSetPMD;
ROLLBACK TO DeleteBSCDimSetPMD;
x_msg_data := x_msg_data||' -> BSC_BIS_KPI_MEAS_PUB.Delete_Dim_Set ';
x_msg_data := SQLERRM||' at BSC_BIS_KPI_MEAS_PUB.Delete_Dim_Set ';
ROLLBACK TO DeleteBSCDimSetPMD;
x_msg_data := x_msg_data||' -> BSC_BIS_KPI_MEAS_PUB.Delete_Dim_Set ';
x_msg_data := SQLERRM||' at BSC_BIS_KPI_MEAS_PUB.Delete_Dim_Set ';
END Delete_Dim_Set;
SELECT indicator
FROM BSC_KPIS_B
WHERE Source_Indicator = p_kpi_id
AND Prototype_Flag <> 2;
SELECT
level_table_name
FROM
bsc_kpi_dim_levels_b
WHERE
indicator = p_kpi_id
AND dim_set_id = p_dim_set_id
INTERSECT
SELECT
column_value level_table_name
FROM
TABLE(CAST(l_level_table_names AS BSC_EDIT_VLIST));
SELECT
level_table_name
,default_key_value
BULK COLLECT INTO
l_level_table_names,
l_key_item_values
FROM
bsc_kpi_dim_levels_b
WHERE
indicator = p_kpi_id
AND dim_set_id = p_dim_set_id
AND default_key_value IS NOT NULL;
SELECT share_flag
INTO l_count
FROM BSC_KPIS_B
WHERE indicator = l_bsc_dimset_rec.Bsc_Kpi_Id ;
SELECT COUNT(*) INTO l_count FROM BSC_KPI_DIM_SETS_VL
WHERE indicator = l_bsc_dimset_rec.Bsc_Kpi_Id
AND dim_set_id = l_bsc_dimset_rec.Bsc_Dim_Set_Id;
BSC_BIS_KPI_MEAS_PUB.Delete_Default_Kpi_Dim_Object
( p_commit => FND_API.G_FALSE
, p_kpi_id => p_kpi_id
, p_dim_set_id => p_dim_set_id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
SELECT COUNT(*) INTO l_count
FROM BSC_SYS_DIM_GROUPS_VL
WHERE short_name = l_dim_short_name;
SELECT COUNT(*) INTO l_count
FROM BSC_KPI_DIM_GROUPS
WHERE dim_group_id = (SELECT dim_group_id FROM BSC_SYS_DIM_GROUPS_VL WHERE Short_Name = l_dim_short_name)
AND dim_set_id = l_bsc_dimset_rec.Bsc_Dim_Set_Id
AND indicator = l_bsc_dimset_rec.Bsc_Kpi_Id;
SELECT COUNT(*) INTO l_count FROM BSC_KPI_DIM_SETS_VL
WHERE indicator = l_bsc_dimset_rec.Bsc_Kpi_Id
AND dim_set_id = l_bsc_dimset_rec.Bsc_Dim_Set_Id;
SELECT
level_table_name
FROM
bsc_kpi_dim_levels_b
WHERE
indicator = p_kpi_id
AND dim_set_id = p_dim_set_id
INTERSECT
SELECT
column_value level_table_name
FROM
TABLE(CAST(l_level_table_names AS BSC_EDIT_VLIST));
SELECT
level_table_name
,default_key_value
BULK COLLECT INTO
l_level_table_names,
l_key_item_values
FROM
bsc_kpi_dim_levels_b
WHERE
indicator = p_kpi_id
AND dim_set_id = p_dim_set_id
AND default_key_value IS NOT NULL;
So when a dimension set is being updated it has to be validate that
it contains all the common dimension objects.
The validation is done for shared kpis also internally.
/************************************************************/
BSC_COMMON_DIM_LEVELS_PUB.Validate_List_Button
(
p_Kpi_Id => p_kpi_id
, p_Dim_Level_Id => NULL
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
SELECT indicator
FROM BSC_KPIS_B
WHERE Source_Indicator = p_kpi_id
AND Prototype_Flag <> 2;
SELECT B.Short_Name
FROM BSC_KPI_DIM_GROUPS A
, BSC_SYS_DIM_GROUPS_VL B
WHERE A.Indicator = p_kpi_id
AND A.Dim_Set_Id = p_dim_set_id
AND A.Dim_Group_ID = B.Dim_Group_ID
ORDER BY A.Dim_Group_Index;
SELECT share_flag
INTO l_count
FROM BSC_KPIS_B
WHERE indicator = l_bsc_dimset_rec.Bsc_Kpi_Id;
SELECT COUNT(*) INTO l_count FROM BSC_KPI_DIM_SETS_VL
WHERE indicator = l_bsc_dimset_rec.Bsc_Kpi_Id
AND dim_set_id = l_bsc_dimset_rec.Bsc_Dim_Set_Id;
SELECT COUNT(*) INTO l_count
FROM BSC_SYS_DIM_GROUPS_VL A
, BSC_KPI_DIM_GROUPS B
WHERE A.short_name = l_dim_short_name
AND A.Dim_Group_Id = B.Dim_Group_Id
AND B.Indicator = l_bsc_dimset_rec.Bsc_Kpi_Id
AND B.Dim_Set_Id = p_dim_set_id;
SELECT dim_group_id
INTO l_bsc_dimset_rec.Bsc_Dim_Level_Group_Id
FROM BSC_SYS_DIM_GROUPS_VL
WHERE short_name = l_dim_short_name;
BSC_DIMENSION_SETS_PUB.Delete_Dim_Group_In_Dset
( p_commit => FND_API.G_FALSE
, p_Dim_Set_Rec => l_bsc_dimset_rec
, p_create_Dim_Lev_Grp => FALSE
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
SELECT COUNT(*) INTO l_count
FROM BSC_SYS_DIM_GROUPS_VL A
, BSC_KPI_DIM_GROUPS B
WHERE A.short_name = l_dim_short_name
AND A.Dim_Group_Id = B.Dim_Group_Id
AND B.Indicator = l_bsc_dimset_rec.Bsc_Kpi_Id
AND B.Dim_Set_Id = p_dim_set_id;
SELECT dim_group_id
INTO l_bsc_dimset_rec.Bsc_Dim_Level_Group_Id
FROM BSC_SYS_DIM_GROUPS_VL
WHERE short_name = l_dim_short_name;
BSC_DIMENSION_SETS_PUB.Delete_Dim_Group_In_Dset
( p_commit => FND_API.G_FALSE
, p_Dim_Set_Rec => l_bsc_dimset_rec
, p_create_Dim_Lev_Grp => FALSE
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
UPDATE DIMENSION LEVEL SELECTIONS
*********************************************************************************/
PROCEDURE Create_Dim_Grp_Lev_In_Dset
( p_commit IN VARCHAR2 := FND_API.G_TRUE
, p_kpi_id IN NUMBER
, p_dim_set_id IN NUMBER
, p_dim_short_names IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
)IS
l_bsc_dimset_rec BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type;
SELECT COUNT(*) INTO l_count
FROM BSC_KPI_DIM_GROUPS A
, BSC_SYS_DIM_GROUPS_VL B
WHERE A.Dim_Group_Id = B.Dim_Group_Id
AND A.Indicator = l_bsc_dimset_rec.bsc_kpi_id
AND A.Dim_Set_Id = l_bsc_dimset_rec.Bsc_Dim_Set_Id
AND B.Short_Name = l_dim_short_name;
SELECT NVL(MAX(Dim_Group_Index) + 1, 0)
INTO l_bsc_dimset_rec.Bsc_Dim_Level_Group_Index
FROM BSC_KPI_DIM_GROUPS
WHERE indicator = l_bsc_dimset_rec.bsc_kpi_id
AND dim_set_id = l_bsc_dimset_rec.bsc_dim_set_id;
SELECT dim_group_id
INTO l_bsc_dimset_rec.Bsc_Dim_Level_Group_Id
FROM BSC_SYS_DIM_GROUPS_VL
WHERE short_name = l_dim_short_name;
BSC_DIMENSION_SETS_PUB.Create_Dim_Group_In_Dset--insert into BSC_KPI_DIM_GROUPS
( p_commit => FND_API.G_FALSE
, p_Dim_Set_Rec => l_bsc_dimset_rec
, p_create_Dim_Lev_Grp => FALSE
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
BSC_DIMENSION_SETS_PUB.Create_Dim_Level_Properties--insert into BSC_KPI_DIM_LEVEL_PROPERTIES
( p_commit => FND_API.G_FALSE
, p_Dim_Set_Rec => l_bsc_dimset_rec
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
SELECT COUNT(*)
INTO l_count
FROM BSC_KPI_DIM_LEVELS_B
WHERE INDICATOR = l_bsc_dimset_rec.Bsc_Kpi_Id
AND DIM_SET_ID = l_bsc_dimset_rec.Bsc_Dim_Set_Id;
SELECT indicator
FROM BSC_KPIS_B
WHERE Source_Indicator = p_kpi_id
AND Prototype_Flag <> 2;
SELECT COUNT(Indicator) INTO l_Count
FROM BSC_KPI_ANALYSIS_OPTIONS_B
WHERE Dim_Set_Id = l_bsc_dimset_rec.Bsc_Dim_Set_Id
AND Indicator = l_bsc_dimset_rec.Bsc_Kpi_Id
AND Analysis_Group_Id = l_bsc_dimset_rec.Bsc_Analysis_Id
AND Option_Id = l_bsc_dimset_rec.Bsc_Option_Id;
SELECT COUNT(*) INTO l_count FROM BSC_KPI_DIM_SETS_VL
WHERE indicator = l_bsc_dimset_rec.Bsc_Kpi_Id
AND dim_set_id = l_bsc_dimset_rec.Bsc_Dim_Set_Id;
BSC_DIMENSION_SETS_PUB.Update_Kpi_Analysis_Options_B
( p_commit => FND_API.G_FALSE
, p_Dim_Set_Rec => l_bsc_dimset_rec
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
BSC_DESIGNER_PVT.Deflt_Update_AOPTS(l_bsc_dimset_rec.Bsc_Kpi_Id);
SELECT COUNT(*) INTO l_count FROM BSC_KPI_DIM_SETS_VL
WHERE indicator = l_bsc_dimset_rec.Bsc_Kpi_Id
AND dim_set_id = l_bsc_dimset_rec.Bsc_Dim_Set_Id;
BSC_DIMENSION_SETS_PUB.Update_Kpi_Analysis_Options_B
( p_commit => FND_API.G_FALSE
, p_Dim_Set_Rec => l_bsc_dimset_rec
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
SELECT LEVEL_SOURCE
FROM BSC_KPI_DIM_LEVELS_VL
WHERE INDICATOR = p_Indicator
AND DIM_SET_ID = p_DimSetId
AND LEVEL_SOURCE IS NOT NULL;
SELECT D.SOURCE
, D.DataSet_ID
FROM BSC_SYS_DATASETS_VL D
, BSC_DB_DATASET_DIM_SETS_V B
WHERE D.DATASET_ID = B.DATASET_ID
AND B.INDICATOR = p_Indicator
AND B.DIM_SET_ID = p_DimSetId;
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;
API TO UPDATE PMF/BSC MEASURES/ANALYSIS OPTIONS WITHIN AN INDICATOR
*********************************************************************************/
PROCEDURE Update_KPI_Analysis_Options
( p_commit IN VARCHAR2 := FND_API.G_TRUE
, p_kpi_id IN NUMBER
, p_data_source IN VARCHAR2 --this parameter is not needed and can be removed from the API
, p_analysis_group_id IN NUMBER
, p_analysis_option_id0 IN NUMBER
, p_analysis_option_id1 IN NUMBER
, p_analysis_option_id2 IN NUMBER
, p_series_id IN NUMBER
, p_data_set_id IN NUMBER
, p_dim_set_id IN NUMBER
, p_option0_Name IN VARCHAR2
, p_option1_Name IN VARCHAR2
, p_option2_Name IN VARCHAR2
, p_measure_short_name IN VARCHAR2
, p_dim_obj_short_names IN VARCHAR2 --comma seperated dimension objects needed for PMF Measures
, p_default_short_names IN VARCHAR2 := NULL
, p_view_by_name IN VARCHAR2 := NULL
, p_measure_name IN VARCHAR2 --BSC_KPI_ANALYSIS_MEASURES_VL.name
, p_measure_help IN VARCHAR2 --BSC_KPI_ANALYSIS_MEASURES_VL.help
, p_default_value IN NUMBER
, p_time_stamp IN VARCHAR2 := NULL
, p_update_ana_opt IN BOOLEAN := FALSE
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
) IS
l_Bsc_Anal_Opt_Rec BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type;
SELECT dataset_id
FROM BSC_KPI_ANALYSIS_MEASURES_B
WHERE indicator = p_kpi_id
AND analysis_option0 = NVL(p_analysis_option_id0, 0)
AND analysis_option1 = NVL(p_analysis_option_id1, 0)
AND analysis_option2 = NVL(p_analysis_option_id2, 0)
AND series_id = NVL(p_series_id, 0);
SELECT DISTINCT a.dataset_id DataSet_Id
FROM BSC_SYS_DATASETS_B a
, BSC_SYS_MEASURES b
WHERE UPPER(b.short_name) = UPPER(p_measure_short_name)
AND a.measure_id1 = b.measure_id
AND ROWNUM < 2;
SELECT indicator
FROM BSC_KPIS_B
WHERE Source_Indicator = p_kpi_id
AND Prototype_Flag <> 2;
SELECT D.SOURCE,D.DATASET_ID
FROM BSC_SYS_DATASETS_B D,
BSC_KPI_ANALYSIS_MEASURES_B K
WHERE D.DATASET_ID = K.DATASET_ID
AND k.indicator = l_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id
AND analysis_option0 = 0
AND analysis_option1 = l_Bsc_Anal_Opt_Rec.Bsc_Option_Group1
AND analysis_option2 = l_Bsc_Anal_Opt_Rec.Bsc_Option_Group2
AND series_id = 0;
SELECT
stack_series_id
FROM
bsc_kpi_analysis_measures_b
WHERE
indicator = p_kpi_id AND
analysis_option0 = p_analysis_option_id0 AND
analysis_option1 = p_analysis_option_id1 AND
analysis_option2 = p_analysis_option_id2 AND
series_id = p_series_id;
SELECT
default_calculation
FROM
bsc_kpi_measure_props kp,
bsc_kpi_analysis_measures_b km
WHERE
km.indicator = p_kpi_id AND
km.indicator = kp.indicator AND
km.kpi_measure_id = kp.kpi_measure_id AND
km.analysis_option0 = p_analysis_option_id0 AND
km.analysis_option1 = p_analysis_option_id1 AND
km.analysis_option2 = p_analysis_option_id2 AND
km.series_id = p_series_id;
SAVEPOINT UpdateBSCKPIAnaOpts;
SELECT COUNT(*) INTO l_count
FROM BSC_KPIS_B
WHERE indicator = l_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id;
FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_DELETE_MESSAGE');
SELECT share_flag INTO l_count
FROM BSC_KPIS_B
WHERE indicator = l_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id ;
SELECT Source INTO l_data_source
FROM BSC_SYS_DATASETS_B
WHERE Dataset_Id = l_Bsc_Anal_Opt_Rec.Bsc_Dataset_Id;
IF (NOT p_update_ana_opt AND l_Bsc_Anal_Opt_Rec.Bsc_Dim_Set_Id IS NOT NULL) THEN
BSC_BIS_KPI_MEAS_PUB.get_Dim_Set_Source_Info
( p_Indicator => l_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id
, p_DimSetId => l_Bsc_Anal_Opt_Rec.Bsc_Dim_Set_Id
, x_Source => l_temp_data_source
, x_Data_Set_Id => l_data_set_id
);
FND_MESSAGE.SET_NAME('BSC','BSC_NO_UPDATE_MEASURE_SOURCE');
SELECT COUNT(1) INTO l_Count
FROM BSC_SYS_DIM_LEVELS_B
WHERE SHORT_NAME = l_dim_obj_name;
SELECT NVL(MAX(dim_group_id) + 1, 0) INTO l_count
FROM BSC_SYS_DIM_GROUPS_VL;
SELECT COUNT(*) INTO l_count
FROM BSC_SYS_DIM_GROUPS_VL
WHERE UPPER(short_name) = l_temp_var
OR UPPER(name) = l_temp_var;
SELECT NVL(MAX(dim_set_id) + 1, 0)
INTO l_Bsc_Anal_Opt_Rec.Bsc_Dim_Set_Id
FROM BSC_KPI_DIM_SETS_VL
WHERE indicator = l_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id;
SELECT COUNT(*) INTO l_count
FROM BSC_KPI_DIM_SETS_VL
WHERE indicator = l_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id
AND dim_set_id = l_Bsc_Anal_Opt_Rec.Bsc_Dim_Set_Id;
SELECT level_table_name
INTO l_bsc_dimset_rec.Bsc_Level_Name
FROM BSC_SYS_DIM_LEVELS_B
WHERE Short_Name = l_dim_obj_name;
BSC_DIMENSION_SETS_PUB.Update_Dim_Levels
( p_commit => FND_API.G_FALSE
, p_Dim_Set_Rec => l_bsc_dimset_rec
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
BSC_DIMENSION_SETS_PUB.Update_Dim_Levels
( p_commit => FND_API.G_FALSE
, p_Dim_Set_Rec => l_bsc_dimset_rec
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
SELECT level_table_name
INTO l_bsc_dimset_rec.Bsc_Level_Name
FROM BSC_SYS_DIM_LEVELS_B
WHERE Short_Name = l_view_by_name;
BSC_DIMENSION_SETS_PUB.Update_Dim_Levels
( p_commit => FND_API.G_FALSE
, p_Dim_Set_Rec => l_bsc_dimset_rec
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
BSC_DIMENSION_SETS_PUB.Update_Dim_Levels
( p_commit => FND_API.G_FALSE
, p_Dim_Set_Rec => l_bsc_dimset_rec
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
SELECT indicator_type,config_type
INTO l_indic_type, l_config_type
FROM bsc_kpis_b
WHERE indicator = l_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id;
BSC_ANALYSIS_OPTION_PUB.delete_extra_series(
p_Bsc_Anal_Opt_Rec => l_Bsc_Anal_Opt_Rec
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
SELECT COUNT(1) INTO l_namecount
FROM BSC_KPI_ANALYSIS_OPTIONS_VL
WHERE indicator = l_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id;
SELECT COUNT(1) INTO l_index
FROM BSC_KPI_ANALYSIS_MEASURES_B
WHERE indicator = l_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id
AND analysis_option0 = 0
AND analysis_option1 = l_Bsc_Anal_Opt_Rec.Bsc_Option_Group1
AND analysis_option2 = l_Bsc_Anal_Opt_Rec.Bsc_Option_Group2
AND series_id = 0
AND dataset_id = -1;
IF (p_update_ana_opt OR ((l_dim_set_id IS NOT NULL) OR
((l_dim_set_id IS NULL) AND
((l_index = 1) AND (p_analysis_option_id0 = 0 OR l_namecount = 1))))) THEN
--dbms_output.PUT_LINE('IN IF');
BSC_ANALYSIS_OPTION_PUB.Update_Analysis_Options
( p_commit => FND_API.G_FALSE
, p_Anal_Opt_Rec => l_Bsc_Anal_Opt_Rec
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
BSC_ANALYSIS_OPTION_PUB.Update_Analysis_Options
( p_commit => FND_API.G_FALSE
, p_Anal_Opt_Rec => l_Bsc_Anal_Opt_Rec
, p_data_Source => l_data_source
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
BSC_ANALYSIS_OPTION_PUB.Update_Analysis_Options
( p_commit => FND_API.G_FALSE
, p_Anal_Opt_Rec => l_Bsc_Anal_Opt_Rec
, p_data_Source => l_data_source
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
BSC_ANALYSIS_OPTION_PUB.Update_Analysis_Options
( p_commit => FND_API.G_FALSE
, p_Anal_Opt_Rec => l_Bsc_Anal_Opt_Rec
, p_data_Source => l_data_source
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
SELECT COUNT(*) INTO l_count FROM BSC_KPI_ANALYSIS_MEASURES_B
WHERE indicator = l_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id
AND analysis_option0 = l_Bsc_Anal_Opt_Rec.Bsc_Option_Group0
AND analysis_option1 = l_Bsc_Anal_Opt_Rec.Bsc_Option_Group1
AND analysis_option2 = l_Bsc_Anal_Opt_Rec.Bsc_Option_Group2;
UPDATE BSC_KPI_ANALYSIS_MEASURES_B
SET default_value = 1
WHERE indicator = l_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id
AND analysis_option0 = l_Bsc_Anal_Opt_Rec.Bsc_Option_Group0
AND analysis_option1 = l_Bsc_Anal_Opt_Rec.Bsc_Option_Group1
AND analysis_option2 = l_Bsc_Anal_Opt_Rec.Bsc_Option_Group2
AND series_id = p_default_value;
UPDATE BSC_KPI_ANALYSIS_MEASURES_B
SET default_value = 0
WHERE indicator = l_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id
AND analysis_option0 = l_Bsc_Anal_Opt_Rec.Bsc_Option_Group0
AND analysis_option1 = l_Bsc_Anal_Opt_Rec.Bsc_Option_Group1
AND analysis_option2 = l_Bsc_Anal_Opt_Rec.Bsc_Option_Group2
AND series_id <> p_default_value;
ROLLBACK TO UpdateBSCKPIAnaOpts;
ROLLBACK TO UpdateBSCKPIAnaOpts;
ROLLBACK TO UpdateBSCKPIAnaOpts;
x_msg_data := x_msg_data||' -> BSC_BIS_KPI_MEAS_PUB.Update_KPI_Analysis_Options ';
x_msg_data := SQLERRM||' at BSC_BIS_KPI_MEAS_PUB.Update_KPI_Analysis_Options ';
ROLLBACK TO UpdateBSCKPIAnaOpts;
x_msg_data := x_msg_data||' -> BSC_BIS_KPI_MEAS_PUB.Update_KPI_Analysis_Options ';
x_msg_data := SQLERRM||' at BSC_BIS_KPI_MEAS_PUB.Update_KPI_Analysis_Options ';
END Update_KPI_Analysis_Options;
SELECT Source
FROM BSC_SYS_DATASETS_B
WHERE Dataset_Id = p_data_set_id;
SELECT indicator
FROM BSC_KPIS_B
WHERE Source_Indicator = p_kpi_id
AND Prototype_Flag <> 2;
FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_DELETE_MESSAGE');
SELECT MEANING INTO l_meaning
FROM BSC_LOOKUPS
WHERE LOOKUP_TYPE = 'BSC_UI_COMMON' AND LOOKUP_CODE = 'EDW_MEASURE' ;
SELECT COUNT(*) INTO l_count
FROM BSC_KPIS_B
WHERE indicator = l_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id;
FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_DELETE_MESSAGE');
SELECT share_flag INTO l_count
FROM BSC_KPIS_B
WHERE indicator = l_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id ;
BSC_BIS_KPI_MEAS_PUB.Update_KPI_Analysis_Options
( p_commit => FND_API.G_FALSE
, p_kpi_id => l_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id
, p_data_source => l_datasource
, p_analysis_group_id => l_Bsc_Anal_Opt_Rec.Bsc_Analysis_Group_Id
, p_analysis_option_id0 => NULL
, p_analysis_option_id1 => NULL
, p_analysis_option_id2 => NULL
, p_series_id => NULL
, p_data_set_id => l_Bsc_Anal_Opt_Rec.Bsc_Dataset_Id
, p_dim_set_id => NULL
, p_option0_Name => l_Bsc_Anal_Opt_Rec.Bsc_Option_Name
, p_option1_Name => NULL
, p_option2_Name => NULL
, p_measure_short_name => NULL
, p_dim_obj_short_names => NULL
, p_default_short_names => NULL
, p_view_by_name => NULL
, p_measure_name => l_Bsc_Anal_Opt_Rec.Bsc_Measure_Long_Name
, p_measure_help => l_Bsc_Anal_Opt_Rec.Bsc_Measure_Help
, p_default_value => NULL
, p_time_stamp => NULL
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
If all the above conditons are met then only update the option 0.
other wise create a new analysis option.
*********************************************************************************/
SELECT COUNT(0)
INTO l_namecount
FROM BSC_KPI_ANALYSIS_OPTIONS_VL
WHERE indicator = l_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id;
SELECT COUNT(*)
INTO l_index
FROM BSC_KPI_ANALYSIS_MEASURES_B
WHERE indicator = l_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id
AND analysis_option0 = 0
AND analysis_option1 = l_Bsc_Anal_Opt_Rec.Bsc_Option_Group1
AND analysis_option2 = l_Bsc_Anal_Opt_Rec.Bsc_Option_Group2
AND series_id = 0
AND dataset_id = -1;
SELECT Option_Id
INTO l_Bsc_Anal_Opt_Rec.Bsc_Option_Group0
FROM BSC_KPI_ANALYSIS_OPTIONS_B
WHERE Indicator = l_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id
AND Analysis_Group_Id = l_Bsc_Anal_Opt_Rec.Bsc_Analysis_Group_Id;
BSC_ANALYSIS_OPTION_PUB.Update_Analysis_Options
( p_commit => FND_API.G_FALSE
, p_Anal_Opt_Rec => l_Bsc_Anal_Opt_Rec
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
SELECT COUNT(Option_Id) + 1
INTO l_Bsc_Anal_Opt_Rec.Bsc_Option_Group0
FROM BSC_KPI_ANALYSIS_OPTIONS_B
WHERE Indicator = l_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id
AND Analysis_Group_Id = l_Bsc_Anal_Opt_Rec.Bsc_Analysis_Group_Id;
SELECT short_name
INTO l_sname
FROM BSC_KPIS_B
WHERE INDICATOR = p_kpi_id ;
API TO DELETE PMF/BSC MEASURES/ANALYSIS OPTIONS WITHIN AN INDICATOR
*********************************************************************************/
/*PROCEDURE Delete_KPI_Analysis_Options
( p_commit IN VARCHAR2 := FND_API.G_TRUE
, p_kpi_id IN NUMBER
, p_data_source IN VARCHAR2
, p_option_id IN NUMBER
, p_time_stamp IN VARCHAR2 := NULL
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
) IS
l_count NUMBER;
SAVEPOINT DeleteBSCKPIAnaOpts;
SELECT COUNT(*) INTO l_count
FROM BSC_KPIS_B
WHERE indicator = l_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id;
FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_DELETE_MESSAGE');
SELECT share_flag INTO l_count
FROM BSC_KPIS_B
WHERE indicator = l_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id ;
SELECT COUNT(indicator) INTO l_count
FROM BSC_TAB_INDICATORS
WHERE indicator = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
SELECT tab_id INTO l_tab_id
FROM BSC_TAB_INDICATORS
WHERE indicator = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
SELECT dim_set_id INTO l_dim_set_id
FROM BSC_KPI_ANALYSIS_OPTIONS_B
WHERE indicator = p_kpi_id
AND analysis_group_id = 0
AND option_id = p_option_id;
SELECT COUNT (DISTINCT dataset_id) INTO l_count
FROM BSC_KPI_ANALYSIS_MEASURES_B
WHERE INDICATOR = p_kpi_id;
BSC_ANALYSIS_OPTION_PUB.Delete_Analysis_Options
( p_commit => FND_API.G_FALSE
, p_Anal_Opt_Rec => l_Bsc_Anal_Opt_Rec
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
SELECT COUNT (DISTINCT dataset_id) INTO l_new_count
FROM BSC_KPI_ANALYSIS_MEASURES_B
WHERE INDICATOR=p_kpi_id;
BSC_KPI_PUB.Update_Kpi_Time_Stamp
( p_commit => FND_API.G_FALSE
, p_Bsc_Kpi_Entity_Rec => l_Bsc_Kpi_Entity_Rec
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
SELECT COUNT(option_id) INTO l_count
FROM BSC_KPI_ANALYSIS_OPTIONS_B
WHERE indicator = p_kpi_id
AND dim_set_id = l_dim_set_id;
BSC_BIS_KPI_MEAS_PUB.Delete_Dim_Set
( p_commit => FND_API.G_FALSE
, p_kpi_id => p_kpi_id
, p_dim_set_id => l_dim_set_id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
ROLLBACK TO DeleteBSCKPIAnaOpts;
ROLLBACK TO DeleteBSCKPIAnaOpts;
ROLLBACK TO DeleteBSCKPIAnaOpts;
x_msg_data := x_msg_data||' -> BSC_BIS_KPI_MEAS_PUB.Delete_KPI_Analysis_Options ';
x_msg_data := SQLERRM||' at BSC_BIS_KPI_MEAS_PUB.Delete_KPI_Analysis_Options ';
ROLLBACK TO DeleteBSCKPIAnaOpts;
x_msg_data := x_msg_data||' -> BSC_BIS_KPI_MEAS_PUB.Delete_KPI_Analysis_Options ';
x_msg_data := SQLERRM||' at BSC_BIS_KPI_MEAS_PUB.Delete_KPI_Analysis_Options ';
END Delete_KPI_Analysis_Options; */
DELETE_KPI_ANALYSIS_OPTIONS
************************************************************************************/
PROCEDURE Delete_KPI_Analysis_Options
( p_Bsc_Anal_Opt_Rec IN BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type
, p_data_source IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
) IS
l_count NUMBER;
SELECT
dim_group_id
FROM
BSC_KPI_DIM_GROUPS
WHERE
Dim_Set_Id = p_Bsc_Anal_Opt_Rec.Bsc_Dim_Set_Id AND
Indicator = p_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id;
SELECT
short_name
FROM
BSC_SYS_DIM_GROUPS_VL
WHERE
dim_group_id = p_dim_group_id;
SAVEPOINT DeleteBSCKPIAnaOpts;
BSC_ANALYSIS_OPTION_PUB.Delete_Analysis_Options
( p_commit => FND_API.G_FALSE
, p_Anal_Opt_Rec => p_Bsc_Anal_Opt_Rec
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
SELECT COUNT(option_id) INTO l_count
FROM BSC_KPI_ANALYSIS_OPTIONS_B
WHERE indicator = p_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id
AND dim_set_id = p_Bsc_Anal_Opt_Rec.Bsc_Dim_Set_Id;
BSC_BIS_KPI_MEAS_PUB.Delete_Dim_Set
( p_commit => FND_API.G_FALSE
, p_kpi_id => p_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id
, p_dim_set_id => p_Bsc_Anal_Opt_Rec.Bsc_Dim_Set_Id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
SELECT COUNT(indicator) INTO l_count
FROM BSC_TAB_INDICATORS
WHERE indicator = p_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id;
SELECT tab_id INTO l_tab_id
FROM BSC_TAB_INDICATORS
WHERE indicator = p_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id;
BSC_BIS_DIMENSION_PUB.Delete_Dimension
( p_commit => FND_API.G_FALSE
, p_dim_short_name => l_dim_shortName
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
ROLLBACK TO DeleteBSCKPIAnaOpts;
ROLLBACK TO DeleteBSCKPIAnaOpts;
ROLLBACK TO DeleteBSCKPIAnaOpts;
x_msg_data := x_msg_data||' -> BSC_BIS_KPI_MEAS_PUB.Delete_KPI_Analysis_Options ';
x_msg_data := SQLERRM||' at BSC_BIS_KPI_MEAS_PUB.Delete_KPI_Analysis_Options ';
ROLLBACK TO DeleteBSCKPIAnaOpts;
x_msg_data := x_msg_data||' -> BSC_BIS_KPI_MEAS_PUB.Delete_KPI_Analysis_Options ';
x_msg_data := SQLERRM||' at BSC_BIS_KPI_MEAS_PUB.Delete_KPI_Analysis_Options ';
END Delete_KPI_Analysis_Options;
UPDATE KPIS
************************************************************************************/
PROCEDURE Update_Kpi
( p_commit IN VARCHAR2 := FND_API.G_TRUE
, p_kpi_id IN NUMBER
, p_kpi_name IN VARCHAR2
, p_kpi_help IN VARCHAR2 := NULL
, p_responsibility_id IN NUMBER := NULL
, p_default_value IN NUMBER
, p_BM_Property_Value IN NUMBER := BSC_KPI_PUB.Benchmark_Kpi_Line_Graph -- 0 For Lines and 1 for Bars
, p_time_stamp IN VARCHAR2 := NULL
, p_Anal_opt0 IN BSC_KPI_ANALYSIS_MEASURES_B.analysis_option0%TYPE
, p_Anal_opt1 IN BSC_KPI_ANALYSIS_MEASURES_B.analysis_option1%TYPE
, p_Anal_opt2 IN BSC_KPI_ANALYSIS_MEASURES_B.analysis_option2%TYPE
, p_Anal_Series IN BSC_KPI_ANALYSIS_MEASURES_B.series_id%TYPE
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
) IS
l_Bsc_Kpi_Entity_Rec BSC_KPI_PUB.Bsc_Kpi_Entity_Rec;
SELECT indicator
FROM BSC_KPIS_B
WHERE Source_Indicator = p_kpi_id
AND Prototype_Flag <> 2;
SELECT analysis_option0
, analysis_option1
, analysis_option2
, series_id
FROM bsc_oaf_analysys_opt_comb_v
WHERE indicator = p_kpi_id
AND default_flag = 1;
SAVEPOINT UpdatePMDBSCKPIs;
SELECT COUNT(*) INTO l_count
FROM BSC_KPIS_B
WHERE indicator = p_kpi_id;
FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_DELETE_MESSAGE');
SELECT share_flag
INTO l_count
FROM bsc_kpis_b
WHERE indicator = p_kpi_id ;
SELECT COUNT(indicator) INTO l_count
FROM BSC_TAB_INDICATORS A
WHERE A.Indicator <> p_kpi_id
AND A.Tab_Id = (SELECT Tab_Id FROM BSC_TAB_INDICATORS WHERE Indicator = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id)
AND A.Indicator IN (SELECT Indicator FROM BSC_KPIS_TL WHERE Name = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Name);
SELECT v.name
INTO l_tab_name
FROM bsc_tabs_vl v
,bsc_tab_indicators w
WHERE v.tab_id =w.tab_id
AND w.indicator = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
BSC_KPI_PUB.Update_Kpi
( p_commit => FND_API.G_FALSE
, p_Bsc_Kpi_Entity_Rec => l_Bsc_Kpi_Entity_Rec
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
SELECT kpi_measure_id
INTO l_kpi_measure_id
FROM bsc_kpi_analysis_measures_vl
WHERE analysis_option0 = p_Anal_opt0
AND analysis_option1 = p_Anal_opt1
AND analysis_option2 = p_Anal_opt2
AND series_id = p_Anal_Series
AND indicator = p_kpi_id;
SELECT bk.color_rollup_type, km.kpi_measure_id, bk.prototype_flag
INTO l_color_rollup_type, l_def_kpi_measure_id, l_obj_prototype_flag
FROM bsc_db_color_ao_defaults_v dd, bsc_kpi_analysis_measures_vl km, bsc_kpis_b bk
WHERE km.indicator = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id AND
bk.indicator = km.indicator AND
km.indicator = dd.indicator AND
dd.a0_default = km.analysis_option0 AND
dd.a1_default = km.analysis_option1 AND
dd.a2_default = km.analysis_option2 AND
km.default_value = 1;
ROLLBACK TO UpdatePMDBSCKPIs;
ROLLBACK TO UpdatePMDBSCKPIs;
ROLLBACK TO UpdatePMDBSCKPIs;
x_msg_data := x_msg_data||' -> BSC_BIS_KPI_MEAS_PUB.Update_Kpi ';
x_msg_data := SQLERRM||' at BSC_BIS_KPI_MEAS_PUB.Update_Kpi ';
ROLLBACK TO UpdatePMDBSCKPIs;
x_msg_data := x_msg_data||' -> BSC_BIS_KPI_MEAS_PUB.Update_Kpi ';
x_msg_data := SQLERRM||' at BSC_BIS_KPI_MEAS_PUB.Update_Kpi ';
END Update_Kpi;
l_Bsc_Kpi_Entity_Rec.Last_Updated_By := 0;
l_Bsc_Kpi_Entity_Rec.Last_Update_Login := 0;
l_Bsc_Kpi_Entity_Rec.Last_Update_Login := 0;
SELECT Full_Name
INTO l_tokens
FROM bsc_oaf_analysys_opt_comb_v
WHERE Indicator = p_Kpi_Id
AND Analysis_Option0 = p_Option_0
AND Analysis_Option1 = p_Option_1
AND Analysis_Option2 = p_Option_2
and Series_Id = p_Sid;
FND_MESSAGE.SET_NAME('BSC','BSC_MEAS_DELETE_DEPEND');
, p_delete IN BOOLEAN := FALSE
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
)IS
l_bsc_dimset_rec BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type;
SELECT indicator
FROM BSC_KPIS_B
WHERE Source_Indicator = p_kpi_id
AND Prototype_Flag <> 2;
SELECT Dim_Group_ID
FROM BSC_KPI_DIM_GROUPS
WHERE Indicator = l_bsc_dimset_rec.Bsc_Kpi_Id
AND Dim_Set_Id = l_bsc_dimset_rec.Bsc_Dim_Set_Id
ORDER BY Dim_Group_Index;
SELECT A.Dim_Level_Id Dim_Level_Id
FROM BSC_SYS_DIM_LEVELS_BY_GROUP A
, BSC_SYS_DIM_GROUPS_VL B
WHERE A.Dim_Group_Id = l_bsc_dimset_rec.Bsc_Dim_Level_Group_Id
AND A.Dim_Group_ID = B.Dim_Group_ID
AND B.Short_Name <> BSC_BIS_DIMENSION_PUB.Unassigned_Dim;
SELECT MAX(NUM) INTO l_count
FROM (SELECT COUNT(SYS_DIM_LEL.Dim_Group_Id) NUM
, SYS_DIM_LEL.Dim_Level_Id
FROM BSC_KPI_DIM_GROUPS KPI_GROUP
, BSC_SYS_DIM_LEVELS_BY_GROUP SYS_DIM_LEL
WHERE KPI_GROUP.Dim_Group_Id = SYS_DIM_LEL.Dim_Group_Id
AND KPI_GROUP.Indicator = l_bsc_dimset_rec.Bsc_Kpi_Id
AND KPI_GROUP.Dim_Set_Id = l_bsc_dimset_rec.Bsc_Dim_Set_Id
GROUP BY SYS_DIM_LEL.Dim_Level_Id);
IF (p_delete) THEN
--delete all the dimension objects before creating.
--dbms_output.PUT_LINE('DELETE ENABLED');
BSC_BIS_KPI_MEAS_PUB.Delete_Dim_Objs_In_DSet
( p_commit => FND_API.G_FALSE
, p_kpi_id => p_kpi_id
, p_dim_set_id => p_dim_set_id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
BSC_BIS_KPI_MEAS_PUB.Delete_Default_Kpi_Dim_Object
( p_commit => FND_API.G_FALSE
, p_kpi_id => p_kpi_id
, p_dim_set_id => p_dim_set_id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
BSC_DIMENSION_SETS_PUB.Create_Dim_Level_Properties--insert into BSC_KPI_DIM_LEVEL_PROPERTIES
( p_commit => FND_API.G_FALSE
, p_Dim_Set_Rec => l_bsc_dimset_rec
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
SELECT MAX(NUM) INTO l_count
FROM (SELECT COUNT(SYS_DIM_LEL.Dim_Group_Id) NUM
, SYS_DIM_LEL.Dim_Level_Id
FROM BSC_KPI_DIM_GROUPS KPI_GROUP
, BSC_SYS_DIM_LEVELS_BY_GROUP SYS_DIM_LEL
WHERE KPI_GROUP.Dim_Group_Id = SYS_DIM_LEL.Dim_Group_Id
AND KPI_GROUP.Indicator = l_bsc_dimset_rec.Bsc_Kpi_Id
AND KPI_GROUP.Dim_Set_Id = l_bsc_dimset_rec.Bsc_Dim_Set_Id
GROUP BY SYS_DIM_LEL.Dim_Level_Id);
BSC_DIMENSION_SETS_PUB.Create_Dim_Level_Properties--insert into BSC_KPI_DIM_LEVEL_PROPERTIES
( p_commit => FND_API.G_FALSE
, p_Dim_Set_Rec => l_bsc_dimset_rec
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
API to DELETE DIMENSION-OBJECTS IN DIMENSION SETS USED IN CASCADING
It should only be called for BSC type of Dimension Sets not for PMF type
*********************************************************************************/
PROCEDURE Delete_Dim_Objs_In_DSet
( p_commit IN VARCHAR2 := FND_API.G_TRUE
, p_kpi_id IN NUMBER
, p_dim_set_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
)IS
l_bsc_dimset_rec BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type;
SELECT indicator
FROM BSC_KPIS_B
WHERE Source_Indicator = p_kpi_id
AND Prototype_Flag <> 2;
SELECT Dim_Group_ID
FROM BSC_KPI_DIM_GROUPS
WHERE Indicator = l_bsc_dimset_rec.Bsc_Kpi_Id
AND Dim_Set_Id = l_bsc_dimset_rec.Bsc_Dim_Set_Id
ORDER BY Dim_Group_Index;
SELECT dim_level_id
FROM BSC_SYS_DIM_LEVELS_BY_GROUP
WHERE dim_group_id = l_bsc_dimset_rec.Bsc_Dim_Level_Group_Id;
BSC_DIMENSION_SETS_PUB.Delete_Dim_Level_Properties
( p_commit => FND_API.G_FALSE
, p_Dim_Set_Rec => l_bsc_dimset_rec
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
BSC_DIMENSION_SETS_PUB.Delete_Dim_Levels
( p_commit => FND_API.G_FALSE
, p_Dim_Set_Rec => l_bsc_dimset_rec
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
BSC_DIMENSION_SETS_PUB.Delete_Dim_Level_Properties
( p_commit => FND_API.G_FALSE
, p_Dim_Set_Rec => l_bsc_dimset_rec
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
BSC_DIMENSION_SETS_PUB.Delete_Dim_Levels
( p_commit => FND_API.G_FALSE
, p_Dim_Set_Rec => l_bsc_dimset_rec
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
x_msg_data := x_msg_data||' -> BSC_BIS_KPI_MEAS_PUB.Delete_Dim_Objs_In_DSet ';
x_msg_data := SQLERRM||' at BSC_BIS_KPI_MEAS_PUB.Delete_Dim_Objs_In_DSet ';
x_msg_data := x_msg_data||' -> BSC_BIS_KPI_MEAS_PUB.Delete_Dim_Objs_In_DSet ';
x_msg_data := SQLERRM||' at BSC_BIS_KPI_MEAS_PUB.Delete_Dim_Objs_In_DSet ';
END Delete_Dim_Objs_In_DSet;
SELECT Name||'['||Indicator||']' Indicator
FROM BSC_KPIS_VL
WHERE (Source_Indicator = p_kpi_id
OR Indicator = p_kpi_id)
AND share_flag <> 2;
SELECT COUNT(*) INTO l_Count
FROM BSC_KPI_DIM_GROUPS
WHERE Indicator = p_Kpi_ID
AND Dim_Set_Id = p_Dim_Set_ID;
SELECT A.Short_Name
, B.Dim_Group_Index
FROM BSC_SYS_DIM_GROUPS_VL A
, BSC_KPI_DIM_GROUPS B
WHERE A.Dim_Group_Id = B.Dim_Group_Id
AND B.Indicator = p_Kpi_ID
AND B.Dim_Set_ID = p_Dim_Set_ID
ORDER BY B.Dim_Group_Index;
SELECT A.Short_Name
FROM BSC_SYS_DIM_GROUPS_VL A
, BSC_KPI_DIM_GROUPS B
WHERE A.Dim_Group_Id = B.Dim_Group_Id
AND B.Indicator = p_Kpi_ID
AND B.Dim_Set_ID = p_Dim_Set_ID
ORDER BY B.Dim_Group_Index;
SELECT VL.SHORT_NAME
FROM BSC_SYS_DIM_LEVELS_B VL ,
BSC_SYS_DIM_LEVELS_BY_GROUP DG ,
BSC_SYS_DIM_GROUPS_VL TL
WHERE VL.DIM_LEVEL_ID = DG.DIM_LEVEL_ID
AND DG.dim_group_id = TL.DIM_GROUP_ID
AND INSTR(l_final_dim_names,','||TL.SHORT_NAME||',') > 0;
l_old_dim_array.delete;
SELECT COUNT(b.dim_level_id) INTO l_count
FROM BSC_SYS_DIM_LEVELS_BY_GROUP b,
BSC_SYS_DIM_GROUPS_VL vl
WHERE b.dim_group_id = vl.dim_group_id
AND INSTR(l_final_dim_names,','||vl.short_name ||',')>0 ;
SELECT COUNT(DIM_SET_ID) INTO l_dim_set_count
FROM BSC_KPI_DIM_SETS_VL
WHERE INDICATOR = p_Kpi_ID;
l_dim_objs_array.delete;
SELECT PARENT_SHORT_NAME
FROM BSC_SYS_DIM_LEVEL_RELS_V
WHERE SHORT_NAME = l_dim_short_name_temp;
dim_objs_array_temp.delete;
SELECT short_name
INTO l_sname
FROM BSC_KPIS_B
WHERE INDICATOR = p_kpi_id;
SELECT COUNT (DISTINCT dataset_id) INTO l_count
FROM BSC_KPI_ANALYSIS_MEASURES_B
WHERE INDICATOR = p_kpi_id;
SELECT COUNT (DISTINCT A.dataset_id) INTO l_count
FROM BSC_KPI_ANALYSIS_MEASURES_B A, BSC_SYS_DATASETS_B B
WHERE INDICATOR = p_kpi_id
AND A.DATASET_ID = B.DATASET_ID
AND B.SOURCE = 'BSC';
API TO DELETE PMF/BSC MEASURES/ANALYSIS OPTIONS WITHIN AN INDICATOR
*********************************************************************************/
PROCEDURE Delete_KPI_Multi_Groups_Opts
( p_commit IN VARCHAR2 := FND_API.G_TRUE
, p_kpi_id IN NUMBER
, p_data_source IN VARCHAR2
, p_Option_0 IN NUMBER
, p_Option_1 IN NUMBER
, p_Option_2 IN NUMBER
, p_Sid IN NUMBER
, p_time_stamp IN VARCHAR2 := NULL
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
) IS
l_Anal_Num_Tbl BSC_ANALYSIS_OPTION_PUB.Anal_Opt_Comb_Num_Tbl_Type;
l_Default_Delete BOOLEAN := FALSE;
l_series_delete BOOLEAN;
SELECT Analysis_Group_Id
, Num_Of_Options
, Dependency_Flag
FROM BSC_KPI_ANALYSIS_GROUPS
WHERE Indicator = p_kpi_id
ORDER BY Analysis_Group_Id;
SELECT indicator
FROM BSC_KPIS_B
WHERE Source_Indicator = p_kpi_id
AND Prototype_Flag <> 2;
SAVEPOINT DeleteBSCKPIMulAnaOpts;
l_series_delete := FALSE;
SELECT COUNT(*) INTO l_count
FROM BSC_KPIS_B
WHERE indicator = l_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id;
FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_DELETE_MESSAGE');
SELECT share_flag INTO l_count
FROM BSC_KPIS_B
WHERE indicator = l_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id;
SELECT dim_set_id INTO l_Bsc_Anal_Opt_Rec.Bsc_Dim_Set_Id
FROM BSC_KPI_ANALYSIS_OPTIONS_B
WHERE indicator = p_kpi_id
AND analysis_group_id = 0
AND option_id = p_Option_0;
/*SELECT COUNT (DISTINCT dataset_id) INTO l_count
FROM BSC_KPI_ANALYSIS_MEASURES_B
WHERE INDICATOR = p_kpi_id;*/
SELECT COUNT(0)
INTO l_max_group_count
FROM BSC_KPI_ANALYSIS_GROUPS
WHERE INDICATOR = p_kpi_id;
BSC_ANALYSIS_OPTION_PUB.Delete_Ana_Opt_Mult_Groups
( p_commit => FND_API.G_FALSE
, p_Kpi_id => p_kpi_id
, p_Anal_Opt_Tbl => l_Anal_Opt_Tbl
, p_max_group_count => l_max_group_count
, p_Anal_Opt_Comb_Tbl => l_Anal_Num_Tbl
, p_Anal_Opt_Rec => l_Anal_Opt_Rec
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
l_Default_Delete := TRUE;
IF ((p_data_source = 'PMF') OR (l_Default_Delete)) THEN
BSC_BIS_KPI_MEAS_PUB.Delete_KPI_Analysis_Options
( p_Bsc_Anal_Opt_Rec => l_Bsc_Anal_Opt_Rec
, p_data_source => p_data_source
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
BSC_KPI_PUB.Update_Kpi_Time_Stamp
( p_commit => FND_API.G_FALSE
, p_Bsc_Kpi_Entity_Rec => l_Bsc_Kpi_Entity_Rec
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
/* SELECT COUNT (DISTINCT dataset_id) INTO l_new_count
FROM BSC_KPI_ANALYSIS_MEASURES_B
WHERE INDICATOR = p_kpi_id;*/
SELECT COUNT (DISTINCT dataset_id) INTO l_new_count
FROM BSC_KPI_ANALYSIS_MEASURES_B
WHERE INDICATOR = l_kpi_id;
ROLLBACK TO DeleteBSCKPIMulAnaOpts;
ROLLBACK TO DeleteBSCKPIMulAnaOpts;
ROLLBACK TO DeleteBSCKPIMulAnaOpts;
x_msg_data := x_msg_data||' -> BSC_BIS_KPI_MEAS_PUB.Delete_KPI_Multi_Groups_Opts ';
x_msg_data := SQLERRM||' at BSC_BIS_KPI_MEAS_PUB.Delete_KPI_Multi_Groups_Opts ';
ROLLBACK TO DeleteBSCKPIMulAnaOpts;
x_msg_data := x_msg_data||' -> BSC_BIS_KPI_MEAS_PUB.Delete_KPI_Multi_Groups_Opts ';
x_msg_data := SQLERRM||' at BSC_BIS_KPI_MEAS_PUB.Delete_KPI_Multi_Groups_Opts ';
END Delete_KPI_Multi_Groups_Opts;
SELECT SOURCE
INTO l_Source
FROM BSC_SYS_PERIODICITIES
WHERE PERIODICITY_ID =l_baseperiod;
SELECT MES.SHORT_NAME
INTO l_measure_short_name
FROM BSC_SYS_MEASURES MES,
BSC_SYS_DATASETS_B SYS
WHERE MES.MEASURE_ID = SYS.MEASURE_ID1
AND SYS.DATASET_ID = p_dataset_id;
SELECT NAME
INTO x_dimobj_name
FROM BIS_LEVELS_VL
WHERE SHORT_NAME = l_dim_sht_name;
SELECT NAME
INTO x_dimobj_name
FROM BIS_LEVELS_VL
WHERE SHORT_NAME = l_comma_shtnames;
SELECT actual_data_source
INTO l_actual_data_source
FROM BIS_INDICATORS
WHERE SHORT_NAME = p_Measure_Short_Name;
SELECT DISTINCT tab_id
FROM bsc_tab_indicators
WHERE indicator = p_indicator;
SELECT 1
FROM bsc_sys_com_dim_levels
WHERE tab_id = p_tab_id
AND dim_level_id IN ( SELECT dim_level_id
FROM bsc_sys_dim_levels_by_group dim_lvl, bsc_sys_dim_groups_vl dim
WHERE dim.short_name = p_dim
AND dim.dim_group_id = dim_lvl.dim_group_id
);
SELECT indicator
FROM bsc_kpis_b
WHERE source_indicator = p_indicator
AND share_flag = 2
AND prototype_flag <> 2;