[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| This package Creates, Retrieve, Update, Delete |
| for BSC KPI information. |
| |
| History: |
| |
| 04-MAR-2003 PAJOHRI MLS Bug #2721899 |
| 1. Modified Update Query for BSC_KPIS_TL, BSC_KPI_DEFAULTS_TL|
| 2. Modified Insert Query for BSC_KPI_DEFAULTS_TL. |
| 3. Changed nvl(.Bsc_Language, userenv('LANG')) |
| to userenv('LANG') |
| 20-MAR-03 PWALI for bug #2843082 |
| 13-MAY-2003 PWALI Bug #2942895, SQL BIND COMPLIANCE |
| 24-JUL-2003 Adeulgao fixed bug#3047536 |
| Granted access of KPIS to BSC_PMD_USER |
| 14-NOV-2003 ADRAO Modified for Bug #3248729, |
| 02-MAR-2004 WLEUNG Modified for Bug #3476004 |
| new procedure Set_Default_Value_By_Option_ID |
| 06-MAR-2004 kyadamak for the bug#3439029 |
| 11-MAR-2004 PAJOHRI Bug #3500012 |
| 30-MAR-2003 PAJOHRI Bug #3539639, modified closing of cursor c_old_option_id |
| 18-MAY-04 adrao Modified PL/SQL records and CRUD to accept SHORT_NAME |
| 18-JUN-04 adrao added BSC_KPI_ANALYSIS_GROUP.SHORT_NAME to PL/SQL APIs |
| Bug#3691035 |
| 21-JUL-04 adrao made short_name nonunique. prototype_flag =2 Bug#3781764 |
| 11-APR-05 adrao fixed API Create_Kpi_Analysis to pass appropriate name |
| for Bug#4294920 |
| 27-APR-05 adrao Fixed Bug#4331964 |
| 21-JUL-2005 ashankar Bug#4314386 |
| 16-NOV-2006 ankgoel Color By KPI enh#5244136 |
| 16-NOV-2006 vtulasi Color By KPI enh#5244136 |
| 09-feb-2007 ashankar Simulation Tree Enhacement 5386112 |
+======================================================================================+
*/
G_PKG_NAME CONSTANT varchar2(30) := 'BSC_KPI_PVT';
SELECT COUNT(1) INTO l_Count
FROM BSC_KPIS_B
WHERE INDICATOR = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
SELECT COUNT(1) INTO l_Count
FROM BSC_KPIS_B
WHERE SHORT_NAME = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Short_Name
AND PROTOTYPE_FLAG <> BSC_KPI_PUB.DELETE_KPI_FLAG;
select count(1) + 1
into l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Display_Order
from BSC_KPIS_B
where ind_group_id = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Group_Id;
if l_Bsc_Kpi_Entity_Rec.Last_Update_Date is null then
l_Bsc_Kpi_Entity_Rec.Last_Update_Date := sysdate;
insert into BSC_KPIS_B( INDICATOR
,CSF_ID
,IND_GROUP_ID
,DISP_ORDER
,PROTOTYPE_FLAG
,INDICATOR_TYPE
,CONFIG_TYPE
,PERIODICITY_ID
,BM_GROUP_ID
,APPLY_COLOR_FLAG
,PROTOTYPE_COLOR
,SHARE_FLAG
,SOURCE_INDICATOR
,PUBLISH_FLAG
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,EDW_FLAG
,CALENDAR_ID
,SHORT_NAME
,COLOR_ROLLUP_TYPE
,PROTOTYPE_COLOR_ID
,PROTOTYPE_TREND_ID
,WEIGHTED_COLOR_METHOD)
values( l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
,l_Bsc_Kpi_Entity_Rec.Bsc_Csf_Id
,l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Group_Id
,l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Display_Order
,l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Prototype_Flag
,l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Indicator_Type
,l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Config_Type
,l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Periodicity_Id
,l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Bm_Group_Id
,l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Color_Flag
,l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Prototype_Color
,l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Share_Flag
,l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Source_Ind
,l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Publish_Flag
,l_Bsc_Kpi_Entity_Rec.Created_By
,sysdate
,l_Bsc_Kpi_Entity_Rec.Last_Updated_By
,sysdate
,l_Bsc_Kpi_Entity_Rec.Last_Update_Login
,l_Bsc_Kpi_Entity_Rec.Bsc_Edw_Flag
,l_Bsc_Kpi_Entity_Rec.Bsc_Calendar_Id
,l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Short_Name
,l_Bsc_Kpi_Entity_Rec.Bsc_Color_Rollup_Type
,l_Bsc_Kpi_Entity_Rec.Bsc_Prototype_Color_Id
,l_Bsc_Kpi_Entity_Rec.Bsc_Prototype_Trend_Id
,l_Bsc_Kpi_Entity_Rec.Bsc_Weighted_Color_Method);
insert into BSC_KPIS_TL (
INDICATOR,
LANGUAGE,
SOURCE_LANG,
NAME,
HELP
) select
l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id,
L.LANGUAGE_CODE,
userenv('LANG'),
l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Name,
l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Help
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from BSC_KPIS_TL T
where T.INDICATOR = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
and T.LANGUAGE = L.LANGUAGE_CODE);
select count(indicator)
into l_count
from bsc_kpis_b
where indicator = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id ;
SELECT DISTINCT A.CSF_ID
,A.IND_GROUP_ID
,A.DISP_ORDER
,A.PROTOTYPE_FLAG
,A.INDICATOR_TYPE
,A.CONFIG_TYPE
,A.PERIODICITY_ID
,A.BM_GROUP_ID
,A.APPLY_COLOR_FLAG
,A.PROTOTYPE_COLOR
,A.SHARE_FLAG
,A.SOURCE_INDICATOR
,A.PUBLISH_FLAG
,A.CREATED_BY
,A.CREATION_DATE
,A.LAST_UPDATED_BY
,A.LAST_UPDATE_DATE
,A.LAST_UPDATE_LOGIN
,A.EDW_FLAG
,A.CALENDAR_ID
,A.SHORT_NAME
,A.COLOR_ROLLUP_TYPE
,A.PROTOTYPE_COLOR_ID
,A.PROTOTYPE_TREND_ID
,A.WEIGHTED_COLOR_METHOD
,B.NAME
,B.HELP
,B.SOURCE_LANG
INTO x_Bsc_Kpi_Entity_Rec.Bsc_Csf_Id
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Group_Id
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Display_Order
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Prototype_Flag
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Indicator_Type
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Config_Type
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Periodicity_Id
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Bm_Group_Id
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Color_Flag
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Prototype_Color
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Share_Flag
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Source_Ind
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Publish_Flag
,x_Bsc_Kpi_Entity_Rec.Created_By
,x_Bsc_Kpi_Entity_Rec.Creation_Date
,x_Bsc_Kpi_Entity_Rec.Last_Updated_By
,x_Bsc_Kpi_Entity_Rec.Last_Update_Date
,x_Bsc_Kpi_Entity_Rec.Last_Update_Login
,x_Bsc_Kpi_Entity_Rec.Bsc_Edw_Flag
,x_Bsc_Kpi_Entity_Rec.Bsc_Calendar_Id
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Short_Name
,x_Bsc_Kpi_Entity_Rec.Bsc_Color_Rollup_Type
,x_Bsc_Kpi_Entity_Rec.Bsc_Prototype_Color_Id
,x_Bsc_Kpi_Entity_Rec.Bsc_Prototype_Trend_Id
,x_Bsc_Kpi_Entity_Rec.Bsc_Weighted_Color_Method
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Name
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Help
,x_Bsc_Kpi_Entity_Rec.Bsc_Source_Language
FROM BSC_KPIS_B A
,BSC_KPIS_TL B
WHERE A.INDICATOR = B.INDICATOR
AND A.INDICATOR = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
AND B.LANGUAGE = USERENV('LANG');
procedure Update_Kpi(
p_commit IN varchar2 := FND_API.G_FALSE
,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
,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;
if p_Bsc_Kpi_Entity_Rec.Last_Updated_By is not null then
l_Bsc_Kpi_Entity_Rec.Last_Updated_By := p_Bsc_Kpi_Entity_Rec.Last_Updated_By;
if p_Bsc_Kpi_Entity_Rec.Last_Update_Date is not null then
l_Bsc_Kpi_Entity_Rec.Last_Update_Date := p_Bsc_Kpi_Entity_Rec.Last_Update_Date;
if p_Bsc_Kpi_Entity_Rec.Last_Update_Login is not null then
l_Bsc_Kpi_Entity_Rec.Last_Update_Login := p_Bsc_Kpi_Entity_Rec.Last_Update_Login;
update BSC_KPIS_B
set csf_id = l_Bsc_Kpi_Entity_Rec.Bsc_Csf_Id
,ind_group_id = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Group_Id
,disp_order = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Display_Order
,prototype_flag = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Prototype_Flag
,indicator_type = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Indicator_Type
,config_type = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Config_Type
,periodicity_id = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Periodicity_Id
,bm_group_id = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Bm_Group_Id
,apply_color_flag = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Color_Flag
,prototype_color = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Prototype_Color
,share_flag = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Share_Flag
,source_indicator = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Source_Ind
,publish_flag = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Publish_Flag
,created_by = l_Bsc_Kpi_Entity_Rec.Created_By
,creation_date = l_Bsc_Kpi_Entity_Rec.Creation_Date
,last_updated_by = l_Bsc_Kpi_Entity_Rec.Last_Updated_By
,last_update_date = l_Bsc_Kpi_Entity_Rec.Last_Update_Date
,last_update_login = l_Bsc_Kpi_Entity_Rec.Last_Update_Login
,edw_flag = l_Bsc_Kpi_Entity_Rec.Bsc_Edw_Flag
,calendar_id = l_Bsc_Kpi_Entity_Rec.Bsc_Calendar_Id
,color_rollup_type = l_Bsc_Kpi_Entity_Rec.Bsc_Color_Rollup_Type
,prototype_color_id = l_Bsc_Kpi_Entity_Rec.Bsc_Prototype_Color_Id
,prototype_trend_id = l_Bsc_Kpi_Entity_Rec.Bsc_Prototype_Trend_Id
,weighted_color_method = l_Bsc_Kpi_Entity_Rec.Bsc_Weighted_Color_Method
where indicator = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
update BSC_KPIS_TL
set name = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Name
,help = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Help
,SOURCE_LANG = userenv('LANG')
where indicator = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
and userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Update_Kpi ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Update_Kpi ';
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Update_Kpi ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Update_Kpi ';
end Update_Kpi;
procedure Delete_Kpi(
p_commit IN varchar2 := FND_API.G_FALSE
,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
) is
l_count number;
l_Delete_Kpi_b BOOLEAN := TRUE;
SELECT COUNT(INDICATOR)
INTO l_count
FROM BSC_KPI_DATA_TABLES
WHERE INDICATOR = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
AND TABLE_NAME IS NOT NULL; -- need to check if actual KPI Tables are there
l_Delete_Kpi_b := FALSE;
IF l_Delete_Kpi_b THEN
DELETE FROM BSC_KPIS_B
WHERE INDICATOR = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
DELETE FROM BSC_KPIS_TL
WHERE INDICATOR = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
UPDATE BSC_KPIS_B
SET PROTOTYPE_FLAG = BSC_KPI_PUB.DELETE_KPI_FLAG
WHERE INDICATOR = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Delete_Kpi ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Delete_Kpi ';
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Delete_Kpi ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Delete_Kpi ';
end Delete_Kpi;
procedure Update_Kpi_Defaults(
p_commit IN varchar2 := FND_API.G_FALSE
,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
) is
begin
FND_MSG_PUB.Initialize;
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Update_Kpi_Defaults ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Update_Kpi_Defaults ';
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Update_Kpi_Defaults ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Update_Kpi_Defaults ';
end Update_Kpi_Defaults;
procedure Delete_Kpi_Defaults(
p_commit IN varchar2 := FND_API.G_FALSE
,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
) is
begin
FND_MSG_PUB.Initialize;
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Delete_Kpi_Defaults ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Delete_Kpi_Defaults ';
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Delete_Kpi_Defaults ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Delete_Kpi_Defaults ';
end Delete_Kpi_Defaults;
insert into BSC_KPI_PROPERTIES( indicator
,property_code
,property_value
,secondary_value)
values( p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
,p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Property_Code
,p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Property_Value
,p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Secondary_Value);
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Delete_Kpi_Defaults ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Delete_Kpi_Defaults ';
select distinct property_value
,secondary_value
into x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Property_Value
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Secondary_Value
from BSC_KPI_PROPERTIES
where indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
and property_code = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Property_Code;
procedure Update_Kpi_Properties(
p_commit IN varchar2 := FND_API.G_FALSE
,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
,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;
update BSC_KPI_PROPERTIES
set property_value = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Property_Value
,secondary_value = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Secondary_Value
where indicator = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
and property_code = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Property_Code;
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Update_Kpi_Properties ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Update_Kpi_Properties ';
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Update_Kpi_Properties ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Update_Kpi_Properties ';
end Update_Kpi_Properties;
procedure Delete_Kpi_Properties(
p_commit IN varchar2 := FND_API.G_FALSE
,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
,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_KPI_PROPERTIES
where indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Delete_Kpi_Properties ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Delete_Kpi_Properties ';
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Delete_Kpi_Properties ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Delete_Kpi_Properties ';
end Delete_Kpi_Properties;
insert into BSC_KPI_ANALYSIS_GROUPS( INDICATOR
,ANALYSIS_GROUP_ID
,NUM_OF_OPTIONS
,DEPENDENCY_FLAG
,PARENT_ANALYSIS_ID
,CHANGE_DIM_SET
,DEFAULT_VALUE
,SHORT_NAME)
values( p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
,p_Bsc_Kpi_Entity_Rec.Bsc_Anal_Group_Id
,p_Bsc_Kpi_Entity_Rec.Bsc_Num_Options
,p_Bsc_Kpi_Entity_Rec.Bsc_Dependency_Flag
,p_Bsc_Kpi_Entity_Rec.Bsc_Parent_Anal_Id
,p_Bsc_Kpi_Entity_Rec.Bsc_Change_Dim_Set
,p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Default_Value
,p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Ana_Group_Short_Name);
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Delete_Kpi_Properties ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Delete_Kpi_Properties ';
select distinct analysis_group_id
,num_of_options
,dependency_flag
,parent_analysis_id
,change_dim_set
,default_value
,short_name
into x_Bsc_Kpi_Entity_Rec.Bsc_Anal_Group_Id
,x_Bsc_Kpi_Entity_Rec.Bsc_Num_Options
,x_Bsc_Kpi_Entity_Rec.Bsc_Dependency_Flag
,x_Bsc_Kpi_Entity_Rec.Bsc_Parent_Anal_Id
,x_Bsc_Kpi_Entity_Rec.Bsc_Change_Dim_Set
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Default_Value
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Ana_Group_Short_Name
from BSC_KPI_ANALYSIS_GROUPS
where indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
and analysis_group_id = p_Bsc_Kpi_Entity_Rec.Bsc_Anal_Group_Id;
select distinct analysis_group_id
,num_of_options
,dependency_flag
,parent_analysis_id
,change_dim_set
,default_value
,short_name
into x_Bsc_Kpi_Entity_Rec.Bsc_Anal_Group_Id
,x_Bsc_Kpi_Entity_Rec.Bsc_Num_Options
,x_Bsc_Kpi_Entity_Rec.Bsc_Dependency_Flag
,x_Bsc_Kpi_Entity_Rec.Bsc_Parent_Anal_Id
,x_Bsc_Kpi_Entity_Rec.Bsc_Change_Dim_Set
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Default_Value
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Ana_Group_Short_Name
from BSC_KPI_ANALYSIS_GROUPS
where indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
procedure Update_Kpi_Analysis(
p_commit IN varchar2 := FND_API.G_FALSE
,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
,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;
update BSC_KPI_ANALYSIS_GROUPS
set analysis_group_id = l_Bsc_Kpi_Entity_Rec.Bsc_Anal_Group_Id
,num_of_options = l_Bsc_Kpi_Entity_Rec.Bsc_Num_Options
,dependency_flag = l_Bsc_Kpi_Entity_Rec.Bsc_Dependency_Flag
,parent_analysis_id = l_Bsc_Kpi_Entity_Rec.Bsc_Parent_Anal_Id
,change_dim_set = l_Bsc_Kpi_Entity_Rec.Bsc_Change_Dim_Set
,default_value = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Default_Value
,short_name = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Ana_Group_Short_Name
where indicator = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
AND analysis_group_id = l_Bsc_Kpi_Entity_Rec.Bsc_Anal_Group_Id;
update BSC_KPI_ANALYSIS_GROUPS
set analysis_group_id = l_Bsc_Kpi_Entity_Rec.Bsc_Anal_Group_Id
,num_of_options = l_Bsc_Kpi_Entity_Rec.Bsc_Num_Options
,dependency_flag = l_Bsc_Kpi_Entity_Rec.Bsc_Dependency_Flag
,parent_analysis_id = l_Bsc_Kpi_Entity_Rec.Bsc_Parent_Anal_Id
,change_dim_set = l_Bsc_Kpi_Entity_Rec.Bsc_Change_Dim_Set
,default_value = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Default_Value
,short_name = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Ana_Group_Short_Name
where indicator = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Update_Kpi_Analysis ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Update_Kpi_Analysis ';
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Update_Kpi_Analysis ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Update_Kpi_Analysis ';
end Update_Kpi_Analysis;
procedure Delete_Kpi_Analysis(
p_commit IN varchar2 := FND_API.G_FALSE
,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
,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_kpi_analysis_groups
WHERE indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
DELETE
FROM bsc_kpi_analysis_options_b
WHERE indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
DELETE
FROM bsc_kpi_analysis_options_tl
WHERE indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
DELETE
FROM bsc_kpi_analysis_measures_b
WHERE indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
DELETE
FROM bsc_kpi_analysis_measures_tl
WHERE indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
DELETE
FROM bsc_kpi_analysis_opt_user
WHERE indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Delete_Kpi_Analysis ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Delete_Kpi_Analysis ';
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Delete_Kpi_Analysis ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Delete_Kpi_Analysis ';
end Delete_Kpi_Analysis;
procedure Delete_Objective_Color_Data(
p_commit IN varchar2 := FND_API.G_FALSE
,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
) is
l_count number;
bsc_kpi_measure_props_pub.Delete_Obj_Kpi_Measure_Props (
p_commit => FND_API.G_FALSE
,p_objective_id => p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
,p_cascade_shared => FALSE
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_Msg_Data
);
bsc_color_ranges_pub.Delete_Color_Prop_Ranges (
p_commit => FND_API.G_FALSE
,p_objective_id => p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
,p_kpi_measure_id => NULL
,p_cascade_shared => FALSE
,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_KPI_PVT.Delete_Objective_Color_Data ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Delete_Objective_Color_Data ';
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Delete_Objective_Color_Data ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Delete_Objective_Color_Data ';
end Delete_Objective_Color_Data;
select count(display_order)
into l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Periodicity_Display
from BSC_KPI_PERIODICITIES
where indicator = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
insert into BSC_KPI_PERIODICITIES( indicator
,periodicity_id
,display_order
,previous_years
,num_of_years
,viewport_flag
,viewport_default_size
,user_level0
,user_level1
,user_level1_default
,user_level2
,user_level2_default
,current_period
,last_update_date)
values( l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
,l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Periodicity_Id
,l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Periodicity_Display
,l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Previous_Years
,l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Num_Years
,l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Viewport_Flag
,l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Viewport_Default_Size
,l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level0
,l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level1
,l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level1_Default
,l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level2
,l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level2_Default
,l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Current_Period
,sysdate);
select distinct display_order
,previous_years
,num_of_years
,viewport_flag
,viewport_default_size
,user_level0
,user_level1
,user_level1_default
,user_level2
,user_level2_default
,current_period
,last_update_date
into x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Periodicity_Display
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Previous_Years
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Num_Years
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Viewport_Flag
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Viewport_Default_Size
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level0
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level1
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level1_Default
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level2
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level2_Default
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Current_Period
,x_Bsc_Kpi_Entity_Rec.Last_Update_Date
from BSC_KPI_PERIODICITIES
where indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
and periodicity_id = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Periodicity_Id;
procedure Update_Kpi_Periodicity(
p_commit IN varchar2 := FND_API.G_FALSE
,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
,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;
if p_Bsc_Kpi_Entity_Rec.Last_Update_Date is not null then
l_Bsc_Kpi_Entity_Rec.Last_Update_Date := p_Bsc_Kpi_Entity_Rec.Last_Update_Date;
update BSC_KPI_PERIODICITIES
set display_order = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Periodicity_Display
,previous_years = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Previous_Years
,num_of_years = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Num_Years
,viewport_flag = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Viewport_Flag
,viewport_default_size = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Viewport_Default_Size
,user_level0 = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level0
,user_level1 = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level1
,user_level1_default = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level1_Default
,user_level2 = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level2
,user_level2_default = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level2_Default
,current_period = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Current_Period
,last_update_date = l_Bsc_Kpi_Entity_Rec.Last_Update_Date
where indicator = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
and periodicity_id = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Periodicity_Id;
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Update_Kpi_Periodicity ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Update_Kpi_Periodicity ';
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Update_Kpi_Periodicity ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Update_Kpi_Periodicity ';
end Update_Kpi_Periodicity;
procedure Delete_Kpi_Periodicity(
p_commit IN varchar2 := FND_API.G_FALSE
,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
,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_KPI_PERIODICITIES
where indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Delete_Kpi_Periodicity ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Delete_Kpi_Periodicity ';
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Delete_Kpi_Periodicity ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Delete_Kpi_Periodicity ';
end Delete_Kpi_Periodicity;
insert into BSC_KPI_DATA_TABLES( indicator
,periodicity_id
,dim_set_id
,level_comb
,table_name
,filter_condition)
values( p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
,p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Periodicity_Id
,p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Set_Id
,p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Level_Comb
,p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Table_Name
,p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Filter_Condition);
select distinct level_comb
,table_name
,filter_condition
into x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Level_Comb
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Table_Name
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Filter_Condition
from BSC_KPI_DATA_TABLES
where indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
and dim_set_id = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Set_Id
and periodicity_id = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Periodicity_Id;
procedure Update_Kpi_Data_Tables(
p_commit IN varchar2 := FND_API.G_FALSE
,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
,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;
update BSC_KPI_DATA_TABLES
set level_comb = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Level_Comb
,table_name = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Table_Name
,filter_condition = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Filter_Condition
where indicator = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
and dim_set_id = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Set_Id
and periodicity_id = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Periodicity_Id;
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Update_Kpi_Data_Tables ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Update_Kpi_Data_Tables ';
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Update_Kpi_Data_Tables ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Update_Kpi_Data_Tables ';
end Update_Kpi_Data_Tables;
procedure Delete_Kpi_Data_Tables(
p_commit IN varchar2 := FND_API.G_FALSE
,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
) is
l_count number;
l_Delete_Kpi_Data BOOLEAN := TRUE;
SELECT COUNT(INDICATOR)
INTO l_count
FROM BSC_KPI_DATA_TABLES
WHERE INDICATOR = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
AND TABLE_NAME IS NOT NULL; -- need to check if actual KPI Tables are there
l_Delete_Kpi_Data := FALSE;
IF (l_Delete_Kpi_Data) THEN
DELETE FROM BSC_KPI_DATA_TABLES
WHERE INDICATOR = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Delete_Kpi_Data_Tables ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Delete_Kpi_Data_Tables ';
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Delete_Kpi_Data_Tables ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Delete_Kpi_Data_Tables ';
end Delete_Kpi_Data_Tables;
insert into BSC_KPI_CALCULATIONS( indicator
,calculation_id
,user_level0
,user_level1
,user_level1_default
,user_level2
,user_level2_default
,default_value)
values( p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
,p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Calculation_Id
,p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level0
,p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level1
,p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level1_Default
,p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level2
,p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level2_Default
,p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Default_Value);
select distinct user_level0
,user_level1
,user_level1_default
,user_level2
,user_level2_default
,default_value
into x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level0
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level1
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level1_Default
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level2
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level2_Default
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Default_Value
from BSC_KPI_CALCULATIONS
where indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
and calculation_id = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Calculation_Id;
procedure Update_Kpi_Calculations(
p_commit IN varchar2 := FND_API.G_FALSE
,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
,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;
update BSC_KPI_CALCULATIONS
set user_level0 = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level0
,user_level1 = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level1
,user_level1_default = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level1_Default
,user_level2 = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level2
,user_level2_default = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level2_Default
,default_value = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Default_Value
where indicator = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
and calculation_id = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Calculation_Id;
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Update_Kpi_Calculations ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Update_Kpi_Calculations ';
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Update_Kpi_Calculations ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Update_Kpi_Calculations ';
end Update_Kpi_Calculations;
procedure Delete_Kpi_Calculations(
p_commit IN varchar2 := FND_API.G_FALSE
,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
,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_kpi_calculations
WHERE indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
DELETE
FROM bsc_kpi_calculations_user
WHERE indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Delete_Kpi_Calculations ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Delete_Kpi_Calculations ';
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Delete_Kpi_Calculations ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Delete_Kpi_Calculations ';
end Delete_Kpi_Calculations;
insert into BSC_USER_KPI_ACCESS( responsibility_id
,indicator
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,start_date
,end_date)
values( p_Bsc_Kpi_Entity_Rec.Bsc_Responsibility_Id
,p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
,sysdate
,p_Bsc_Kpi_Entity_Rec.Created_By
,sysdate
,p_Bsc_Kpi_Entity_Rec.Last_Updated_By
,p_Bsc_Kpi_Entity_Rec.Last_Update_Login
,p_Bsc_Kpi_Entity_Rec.Bsc_Resp_Start_Date
,p_Bsc_Kpi_Entity_Rec.Bsc_Resp_End_Date);
select distinct responsibility_id
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,start_date
,end_date
into x_Bsc_Kpi_Entity_Rec.Bsc_Responsibility_Id
,x_Bsc_Kpi_Entity_Rec.Creation_Date
,x_Bsc_Kpi_Entity_Rec.Created_By
,x_Bsc_Kpi_Entity_Rec.Last_Update_Date
,x_Bsc_Kpi_Entity_Rec.Last_Updated_By
,x_Bsc_Kpi_Entity_Rec.Last_Update_Login
,x_Bsc_Kpi_Entity_Rec.Bsc_Resp_Start_Date
,x_Bsc_Kpi_Entity_Rec.Bsc_Resp_End_Date
from BSC_USER_KPI_ACCESS
where indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
and responsibility_id = p_Bsc_Kpi_Entity_Rec.Bsc_Responsibility_Id;
procedure Update_Kpi_User_Access(
p_commit IN varchar2 := FND_API.G_FALSE
,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
,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;
if p_Bsc_Kpi_Entity_Rec.Last_Update_Date is not null then
l_Bsc_Kpi_Entity_Rec.Last_Update_Date := p_Bsc_Kpi_Entity_Rec.Last_Update_Date;
if p_Bsc_Kpi_Entity_Rec.Last_Updated_By is not null then
l_Bsc_Kpi_Entity_Rec.Last_Updated_By := p_Bsc_Kpi_Entity_Rec.Last_Updated_By;
if p_Bsc_Kpi_Entity_Rec.Last_Update_Login is not null then
l_Bsc_Kpi_Entity_Rec.Last_Update_Login := p_Bsc_Kpi_Entity_Rec.Last_Update_Login;
update BSC_USER_KPI_ACCESS
set responsibility_id = l_Bsc_Kpi_Entity_Rec.Bsc_Responsibility_Id
,creation_date = l_Bsc_Kpi_Entity_Rec.Creation_Date
,created_by = l_Bsc_Kpi_Entity_Rec.Created_By
,last_update_date = l_Bsc_Kpi_Entity_Rec.Last_Update_Date
,last_updated_by = l_Bsc_Kpi_Entity_Rec.Last_Updated_By
,last_update_login = l_Bsc_Kpi_Entity_Rec.Last_Update_Login
,start_date = l_Bsc_Kpi_Entity_Rec.Bsc_Resp_Start_Date
,end_date = l_Bsc_Kpi_Entity_Rec.Bsc_Resp_End_Date
where indicator = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Update_Kpi_User_Access ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Update_Kpi_User_Access ';
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Update_Kpi_User_Access ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Update_Kpi_User_Access ';
end Update_Kpi_User_Access;
procedure Delete_Kpi_User_Access(
p_commit IN varchar2 := FND_API.G_FALSE
,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
,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_USER_KPI_ACCESS
where indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Delete_Kpi_User_Access ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Delete_Kpi_User_Access ';
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Delete_Kpi_User_Access ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Delete_Kpi_User_Access ';
end Delete_Kpi_User_Access;
insert into BSC_KPI_DEFAULTS_B( tab_id
,indicator
,format_mask
,color_method
,dim_set_id
,dim_level1_value
,dim_level2_value
,dim_level3_value
,dim_level4_value
,dim_level5_value
,dim_level6_value
,dim_level7_value
,dim_level8_value
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,measure_source)
values( -1
,l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
,l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Format_Mask
,l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Color_Method
,l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Set_Id
,l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level1_Value
,l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level2_Value
,l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level3_Value
,l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level4_Value
,l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level5_Value
,l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level6_Value
,l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level7_Value
,l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level8_Value
,sysdate
,l_Bsc_Kpi_Entity_Rec.Last_Updated_By
,sysdate
,l_Bsc_Kpi_Entity_Rec.Created_By
,l_Bsc_Kpi_Entity_Rec.Last_Update_Login
,l_Bsc_Kpi_Entity_Rec.Bsc_Measure_Source);
insert into BSC_KPI_DEFAULTS_TL( tab_id
,indicator
,language
,source_lang
,analysis_option0_name
,analysis_option1_name
,analysis_option2_name
,period_name
,series_name
,dim_level1_name
,dim_level2_name
,dim_level3_name
,dim_level4_name
,dim_level5_name
,dim_level6_name
,dim_level7_name
,dim_level8_name
,dim_level1_text
,dim_level2_text
,dim_level3_text
,dim_level4_text
,dim_level5_text
,dim_level6_text
,dim_level7_text
,dim_level8_text)
SELECT -1
, l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
, L.LANGUAGE_CODE
, USERENV('LANG')
, l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Anal_Opt0_Name
, l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Anal_Opt1_Name
, l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Anal_Opt2_Name
, l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Period_Name
, l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Series_Name
, l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level1_Name
, l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level2_Name
, l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level3_Name
, l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level4_Name
, l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level5_Name
, l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level6_Name
, l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level7_Name
, l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level8_Name
, l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level1_Text
, l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level2_Text
, l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level3_Text
, l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level4_Text
, l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level5_Text
, l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level6_Text
, l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level7_Text
, l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level8_Text
FROM FND_LANGUAGES L
WHERE L.INSTALLED_FLAG IN ('I', 'B')
AND NOT EXISTS
( SELECT NULL
FROM BSC_KPI_DEFAULTS_TL T
WHERE T.indicator = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
AND T.LANGUAGE = L.LANGUAGE_CODE);
select distinct a.tab_id
,a.format_mask
,a.color_method
,a.dim_set_id
,a.dim_level1_value
,a.dim_level2_value
,a.dim_level3_value
,a.dim_level4_value
,a.dim_level5_value
,a.dim_level6_value
,a.dim_level7_value
,a.dim_level8_value
,a.last_update_date
,a.last_updated_by
,a.creation_date
,a.created_by
,a.last_update_login
,a.measure_source
,b.analysis_option0_name
,b.analysis_option1_name
,b.analysis_option2_name
,b.period_name
,b.series_name
,b.dim_level1_name
,b.dim_level2_name
,b.dim_level3_name
,b.dim_level4_name
,b.dim_level5_name
,b.dim_level6_name
,b.dim_level7_name
,b.dim_level8_name
,b.dim_level1_text
,b.dim_level2_text
,b.dim_level3_text
,b.dim_level4_text
,b.dim_level5_text
,b.dim_level6_text
,b.dim_level7_text
,b.dim_level8_text
into x_Bsc_Kpi_Entity_Rec.Bsc_Tab_Id
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Format_Mask
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Color_Method
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Set_Id
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level1_Value
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level2_Value
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level3_Value
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level4_Value
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level5_Value
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level6_Value
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level7_Value
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level8_Value
,x_Bsc_Kpi_Entity_Rec.Last_Update_Date
,x_Bsc_Kpi_Entity_Rec.Last_Updated_By
,x_Bsc_Kpi_Entity_Rec.Creation_Date
,x_Bsc_Kpi_Entity_Rec.Created_By
,x_Bsc_Kpi_Entity_Rec.Last_Update_Login
,x_Bsc_Kpi_Entity_Rec.Bsc_Measure_Source
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Anal_Opt0_Name
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Anal_Opt1_Name
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Anal_Opt2_Name
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Period_Name
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Series_Name
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level1_Name
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level2_Name
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level3_Name
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level4_Name
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level5_Name
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level6_Name
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level7_Name
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level8_Name
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level1_Text
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level2_Text
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level3_Text
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level4_Text
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level5_Text
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level6_Text
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level7_Text
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level8_Text
from BSC_KPI_DEFAULTS_B a
,BSC_KPI_DEFAULTS_TL b
where a.indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
and a.indicator = b.indicator
and b.language = USERENV('LANG');
procedure Update_Kpi_Default_Values(
p_commit IN varchar2 := FND_API.G_FALSE
,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
,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;
if p_Bsc_Kpi_Entity_Rec.Last_Update_Date is not null then
l_Bsc_Kpi_Entity_Rec.Last_Update_Date := p_Bsc_Kpi_Entity_Rec.Last_Update_Date;
if p_Bsc_Kpi_Entity_Rec.Last_Updated_By is not null then
l_Bsc_Kpi_Entity_Rec.Last_Updated_By := p_Bsc_Kpi_Entity_Rec.Last_Updated_By;
if p_Bsc_Kpi_Entity_Rec.Last_Update_Login is not null then
l_Bsc_Kpi_Entity_Rec.Last_Update_Login := p_Bsc_Kpi_Entity_Rec.Last_Update_Login;
update BSC_KPI_DEFAULTS_B
set tab_id = l_Bsc_Kpi_Entity_Rec.Bsc_Tab_Id
,format_mask = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Format_Mask
,color_method = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Color_Method
,dim_set_id = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Set_Id
,dim_level1_value = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level1_Value
,dim_level2_value = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level2_Value
,dim_level3_value = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level3_Value
,dim_level4_value = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level4_Value
,dim_level5_value = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level5_Value
,dim_level6_value = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level6_Value
,dim_level7_value = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level7_Value
,dim_level8_value = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level8_Value
,last_update_date = l_Bsc_Kpi_Entity_Rec.Last_Update_Date
,last_updated_by = l_Bsc_Kpi_Entity_Rec.Last_Updated_By
,creation_date = l_Bsc_Kpi_Entity_Rec.Creation_Date
,created_by = l_Bsc_Kpi_Entity_Rec.Created_By
,last_update_login = l_Bsc_Kpi_Entity_Rec.Last_Update_Login
,measure_source = l_Bsc_Kpi_Entity_Rec.Bsc_Measure_Source
where indicator = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
update BSC_KPI_DEFAULTS_TL
set analysis_option0_name = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Anal_Opt0_Name
,analysis_option1_name = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Anal_Opt1_Name
,analysis_option2_name = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Anal_Opt2_Name
,period_name = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Period_Name
,series_name = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Series_Name
,dim_level1_name = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level1_Name
,dim_level2_name = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level2_Name
,dim_level3_name = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level3_Name
,dim_level4_name = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level4_Name
,dim_level5_name = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level5_Name
,dim_level6_name = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level6_Name
,dim_level7_name = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level7_Name
,dim_level8_name = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level8_Name
,dim_level1_text = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level1_Text
,dim_level2_text = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level2_Text
,dim_level3_text = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level3_Text
,dim_level4_text = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level4_Text
,dim_level5_text = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level5_Text
,dim_level6_text = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level6_Text
,dim_level7_text = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level7_Text
,dim_level8_text = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level8_Text
,SOURCE_LANG = userenv('LANG')
where indicator = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
and userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Update_Kpi_Default_Values ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Update_Kpi_Default_Values ';
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Update_Kpi_Default_Values ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Update_Kpi_Default_Values ';
end Update_Kpi_Default_Values;
procedure Delete_Kpi_Default_Values(
p_commit IN varchar2 := FND_API.G_FALSE
,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
,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_KPI_DEFAULTS_B
where indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
delete from BSC_KPI_DEFAULTS_TL
where indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Delete_Kpi_Default_Values ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Delete_Kpi_Default_Values ';
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Delete_Kpi_Default_Values ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Delete_Kpi_Default_Values ';
end Delete_Kpi_Default_Values;
select count(*)
into l_count
from BSC_TAB_IND_GROUPS_B
where tab_id = l_Bsc_Kpi_Entity_Rec.Bsc_Tab_Id
and ind_group_id = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Group_Id;
l_db_object := 'Update_Kpi_Group';
BSC_KPI_GROUP_PUB.Update_Kpi_Group( p_commit
,l_Bsc_Kpi_Group_Rec
,x_return_status
,x_msg_count
,x_msg_data);
insert into BSC_TAB_INDICATORS( tab_id
,indicator
,bsc_model_flag
,left_position
,top_position
,width
,height
,backcolor)
values( l_Bsc_Kpi_Entity_Rec.Bsc_Tab_Id
,l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
,l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Model_Flag
,l_Bsc_Kpi_Entity_Rec.Bsc_Left_Position_In_Tab
,l_Bsc_Kpi_Entity_Rec.Bsc_Top_Position_In_Tab
,l_Bsc_Kpi_Entity_Rec.Bsc_Group_Width
,l_Bsc_Kpi_Entity_Rec.Bsc_Group_Height
,l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Backcolor);
Update_Kpi_In_Tab( p_commit
,l_Bsc_Kpi_Entity_Rec
,x_return_status
,x_msg_count
,x_msg_data);
select distinct tab_id
,bsc_model_flag
,left_position
,top_position
,width
,height
,backcolor
into x_Bsc_Kpi_Entity_Rec.Bsc_Tab_Id
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Model_Flag
-- This is not KPI Group position and size
-- It is KPI position and Size for Stratege Map View
-- Not use yet it I-Builder
,x_Bsc_Kpi_Entity_Rec.Bsc_Left_Position_In_Tab
,x_Bsc_Kpi_Entity_Rec.Bsc_Top_Position_In_Tab
,x_Bsc_Kpi_Entity_Rec.Bsc_Group_Width
,x_Bsc_Kpi_Entity_Rec.Bsc_Group_Height
,x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Backcolor
from BSC_TAB_INDICATORS
where indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
procedure Update_Kpi_In_Tab(
p_commit IN varchar2 := FND_API.G_FALSE
,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
) is
l_count number;
update BSC_KPIS_B
set prototype_flag = nvl(p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Prototype_Flag,7)
where indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
and decode(prototype_flag, 0, 8, prototype_flag) > 7;
update BSC_KPI_DEFAULTS_B
set tab_id = p_Bsc_Kpi_Entity_Rec.Bsc_Tab_Id
where indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
update BSC_KPI_DEFAULTS_TL
set tab_id = p_Bsc_Kpi_Entity_Rec.Bsc_Tab_Id
where indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
update BSC_KPI_DIM_LEVELS_B
set default_value = 'T'
where (default_value like 'D%')
and indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Update_Kpi_In_Tab ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Update_Kpi_In_Tab ';
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Update_Kpi_In_Tab ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Update_Kpi_In_Tab ';
end Update_Kpi_In_Tab;
procedure Delete_Kpi_In_Tab(
p_commit IN varchar2 := FND_API.G_FALSE
,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
,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_TAB_INDICATORS
where indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Delete_Kpi_In_Tab ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Delete_Kpi_In_Tab ';
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Delete_Kpi_In_Tab ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Delete_Kpi_In_Tab ';
end Delete_Kpi_In_Tab;
procedure Update_Kpi_Time_Stamp(
p_commit IN varchar2 := FND_API.G_FALSE
,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
) is
begin
FND_MSG_PUB.Initialize;
update BSC_KPIS_B
set last_update_date = sysdate
where indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Update_Kpi_Time_Stamp ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Update_Kpi_Time_Stamp ';
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Update_Kpi_Time_Stamp ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Update_Kpi_Time_Stamp ';
end Update_Kpi_Time_Stamp;
Update_Kpi( p_commit
,l_Bsc_Kpi_Entity_Rec
,x_return_status
,x_msg_count
,x_msg_data);
update BSC_KPI_ANALYSIS_OPTIONS_B
set USER_LEVEL1 = USER_LEVEL0
where indicator = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
SELECT COUNT(1) INTO l_Count
FROM BSC_TAB_IND_GROUPS_B
WHERE IND_GROUP_ID = l_x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Group_Id;
SELECT config_type,short_name
INTO l_config_type,l_short_name
FROM bsc_kpis_vl
WHERE indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
select decode(property_value,1,1,3)
into l_prototype_flag
from bsc_sys_init
where property_code = 'SYSTEM_STAGE';
update BSC_KPIS_B
set share_flag = 2
,prototype_flag = l_prototype_flag
,source_indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
where indicator = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
update BSC_KPI_ANALYSIS_OPTIONS_B
set USER_LEVEL1 = USER_LEVEL0
where indicator = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
/* Delete Rows in BSC_KPI_DATA_TABLES for the Share KPI
We can not use procedure Delete_Kpi_Data_tables because of the
validation added when the KPI is deleting */
DELETE FROM BSC_KPI_DATA_TABLES
WHERE INDICATOR = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
select responsibility_id
into l_Bsc_Kpi_Entity_Rec.Bsc_Responsibility_Id
from FND_RESPONSIBILITY
where responsibility_key = 'BSC_Manager';
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 responsibility_id
into l_Bsc_Kpi_Entity_Rec.Bsc_Responsibility_Id
from FND_RESPONSIBILITY
where responsibility_key = 'BSC_PMD_USER';
l_sql := 'select distinct tab_id, tab_index ' ||
' from BSC_TABS_B ' ||
' where tab_index >= :1' ||
' and tab_id != :2' ||
' order by tab_index asc';
update BSC_TABS_B
set tab_index = l_Bsc_Kpi_Entity_Tbl(i).Bsc_Tab_Index + 1
where tab_id = l_Bsc_Kpi_Entity_Tbl(i).Bsc_Tab_Id;
select count(*)
into l_count
from BSC_TABS_TL
where upper(name) = upper(p_Tab_Name);
select count(*)
into l_count
from BSC_TAB_IND_GROUPS_TL
where upper(name) = upper(p_Kpi_Group_Name);
select count(*)
into l_count
from BSC_KPIS_TL
where upper(name) = upper(p_Kpi_Name);
CURSOR c_Select_Indicator IS
SELECT INDICATOR
FROM BSC_KPIS_B
WHERE SOURCE_INDICATOR = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_id
AND PROTOTYPE_FLAG <> BSC_KPI_PUB.Delete_Kpi_Flag;
select count(*)
into l_count
from BSC_KPI_ANALYSIS_OPTIONS_B
where indicator = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_id
and analysis_group_id = 0
and user_level1 = 1;
SELECT SHARE_FLAG, PROTOTYPE_FLAG
INTO l_share_flag, l_proto_flag
FROM BSC_KPIS_B
WHERE INDICATOR = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_id;
if ((l_share_flag = 1) and (l_proto_flag <> BSC_KPI_PUB.DELETE_KPI_FLAG)) then
-- Second step is to set the default option (1) by selecting the first displayed
-- option. At this point all displayed options are flagged with 2.
update BSC_KPI_ANALYSIS_OPTIONS_B
set user_level1 = 1
where indicator = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_id
and analysis_group_id = 0
and option_id = (select option_id
from BSC_KPI_ANALYSIS_OPTIONS_B
where indicator = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_id
and user_level1 = 2
and rownum < 2);
select option_id
into l_def_option
from BSC_KPI_ANALYSIS_OPTIONS_B
where indicator = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_id
and analysis_group_id = 0
and user_level1 = 1;
update BSC_KPI_ANALYSIS_GROUPS
set default_value = l_def_option
where indicator = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_id;
FOR SrcInd IN c_Select_Indicator LOOP
l_shared_kpi := SrcInd.INDICATOR;
UPDATE BSC_KPI_ANALYSIS_OPTIONS_B
SET USER_LEVEL1 = 2
WHERE INDICATOR = l_shared_kpi
AND ANALYSIS_GROUP_ID = 0
AND USER_LEVEL1 = 1;
UPDATE BSC_KPI_ANALYSIS_OPTIONS_B
SET USER_LEVEL1 = 1
WHERE INDICATOR = l_shared_kpi
AND ANALYSIS_GROUP_ID = 0
AND OPTION_ID = L_DEF_OPTION;
UPDATE BSC_KPI_ANALYSIS_GROUPS
SET DEFAULT_VALUE = l_def_option
WHERE INDICATOR = l_shared_kpi;
elsif ((l_share_flag = 2) and (l_proto_flag <> BSC_KPI_PUB.DELETE_KPI_FLAG)) then-- this is a shared indicator.
-- Get the source indicator for this shared indicator.
select source_indicator
into l_shared_kpi
from BSC_KPIS_B
where indicator = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_id;
select default_value
into l_def_option
from BSC_KPI_ANALYSIS_GROUPS
where indicator = l_shared_kpi
and analysis_group_id = 0;
update BSC_KPI_ANALYSIS_OPTIONS_B
set user_level1 = 1
where indicator = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_id
and analysis_group_id = 0
and option_id = l_def_option;
SELECT Option_Id, Parent_Option_Id, Grandparent_Option_Id
FROM BSC_KPI_ANALYSIS_OPTIONS_B
WHERE Indicator = p_Kpi_Id
AND Analysis_Group_ID = p_group_Id
AND User_Level0 = 1;
SELECT Dependency_Flag, Default_Value
INTO l_Dependency_Flag, l_Default_Value
FROM BSC_KPI_ANALYSIS_GROUPS
WHERE Indicator = p_Kpi_Id
AND Analysis_Group_Id = p_group_Id;
UPDATE BSC_KPI_ANALYSIS_OPTIONS_B
SET User_Level0 = 1
, User_Level1 = 1
WHERE Indicator = p_Kpi_Id
AND Analysis_Group_Id = p_group_Id
AND Option_Id = p_option_Id;
UPDATE BSC_KPI_ANALYSIS_GROUPS
SET Default_Value = p_option_Id
WHERE Indicator = p_Kpi_Id
AND Analysis_Group_Id = p_group_Id;
UPDATE BSC_KPI_ANALYSIS_OPTIONS_B
SET User_Level0 = 2
, User_Level1 = 2
WHERE Indicator = p_Kpi_Id
AND Analysis_Group_Id = p_group_Id
AND Option_Id = l_old_option_id;
UPDATE BSC_KPI_ANALYSIS_OPTIONS_B
SET User_Level0 = 1
, User_Level1 = 1
WHERE Indicator = p_Kpi_Id
AND Analysis_Group_Id = p_group_Id
AND Option_Id = p_option_Id;
UPDATE BSC_KPI_ANALYSIS_GROUPS
SET Default_Value = p_option_Id
WHERE Indicator = p_Kpi_Id
AND Analysis_Group_Id = p_group_Id;
UPDATE BSC_KPI_ANALYSIS_OPTIONS_B
SET User_Level0 = 2
, User_Level1 = 2
WHERE Indicator = p_Kpi_Id
AND Analysis_Group_Id = p_group_Id
AND Option_Id = l_old_option_id;
UPDATE BSC_KPI_ANALYSIS_OPTIONS_B
SET User_Level0 = 1
, User_Level1 = 1
WHERE Indicator = p_Kpi_Id
AND Analysis_Group_Id = p_group_Id
AND Option_Id = p_option_Id
AND Parent_Option_Id = p_Parent_Option_Id;
UPDATE BSC_KPI_ANALYSIS_GROUPS
SET Default_Value = p_option_Id
WHERE Indicator = p_Kpi_Id
AND Analysis_Group_Id = p_group_Id;
UPDATE BSC_KPI_ANALYSIS_OPTIONS_B
SET User_Level0 = 2
, User_Level1 = 2
WHERE Indicator = p_Kpi_Id
AND Analysis_Group_Id = p_group_Id
AND Option_Id = l_old_option_Id
AND Parent_Option_Id = l_old_parent_option_Id;
UPDATE BSC_KPI_ANALYSIS_OPTIONS_B
SET User_Level0 = 1
, User_Level1 = 1
WHERE Indicator = p_Kpi_Id
AND Analysis_Group_Id = p_group_Id
AND Option_Id = p_option_Id
AND Parent_Option_Id = p_Parent_Option_Id
AND Grandparent_Option_Id = p_Grand_Parent_Option_Id;
UPDATE BSC_KPI_ANALYSIS_GROUPS
SET Default_Value = p_option_Id
WHERE Indicator = p_Kpi_Id
AND Analysis_Group_Id = p_group_Id;
UPDATE BSC_KPI_ANALYSIS_OPTIONS_B
SET User_Level0 = 2
, User_Level1 = 2
WHERE Indicator = p_Kpi_Id
AND Analysis_Group_Id = p_group_Id
AND Option_Id = l_old_option_Id
AND Parent_Option_Id = l_old_parent_option_Id
AND Grandparent_Option_Id = l_old_grandparent_option_Id;
SELECT Source_Indicator, Prototype_Flag
INTO l_source_kpi, l_proto_flag
FROM BSC_KPIS_B
WHERE INDICATOR = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
IF (l_proto_flag <> BSC_KPI_PUB.Delete_Kpi_Flag) THEN
-- Get the number of analysis groups in this or in source kpi.
select max(analysis_group_id)
into l_group_count
from BSC_KPI_ANALYSIS_GROUPS
where indicator = l_source_kpi;
select default_value
into l_default_option
from BSC_KPI_ANALYSIS_GROUPS
where indicator = l_source_kpi
and analysis_group_id = 0;
update BSC_KPI_ANALYSIS_OPTIONS_B
set user_level1 = 1
where indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
and analysis_group_id = 0
and option_id = l_default_option;
select dependency_flag, default_value
into l_dependency1_flag, l_default_option
from BSC_KPI_ANALYSIS_GROUPS
where indicator = l_source_kpi
and analysis_group_id = 1;
update BSC_KPI_ANALYSIS_OPTIONS_B
set user_level1 = 1
where indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
and analysis_group_id = 1
and option_id = l_default_option;
select default_value
into l_default_parent_option
from BSC_KPI_ANALYSIS_GROUPS
where indicator = l_source_kpi
and analysis_group_id = 0;
update BSC_KPI_ANALYSIS_OPTIONS_B
set user_level1 = 1
where indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
and analysis_group_id = 1
and option_id = l_default_option
and parent_option_id = l_default_parent_option;
select dependency_flag, default_value
into l_dependency2_flag, l_default_option
from BSC_KPI_ANALYSIS_GROUPS
where indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
and analysis_group_id = 2;
update BSC_KPI_ANALYSIS_OPTIONS_B
set user_level1 = 1
where indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
and analysis_group_id = 2
and option_id = l_default_option;
select default_value
into l_default_parent_option
from BSC_KPI_ANALYSIS_GROUPS
where indicator = l_source_kpi
and analysis_group_id = 1;
update BSC_KPI_ANALYSIS_OPTIONS_B
set user_level1 = 1
where indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
and analysis_group_id = 2
and option_id = l_default_option
and parent_option_id = l_default_parent_option;
update BSC_KPI_ANALYSIS_OPTIONS_B
set user_level1 = 1
where indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
and analysis_group_id = 2
and option_id = l_default_option;
select default_value
into l_default_parent_option
from BSC_KPI_ANALYSIS_GROUPS
where indicator = l_source_kpi
and analysis_group_id = 1;
select default_value
into l_default_grandparent_option
from BSC_KPI_ANALYSIS_GROUPS
where indicator = l_source_kpi
and analysis_group_id = 0;
update BSC_KPI_ANALYSIS_OPTIONS_B
set user_level1 = 1
where indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
and analysis_group_id = 2
and option_id = l_default_option
and parent_option_id = l_default_parent_option
and grandparent_option_id = l_default_grandparent_option;
update BSC_KPI_ANALYSIS_OPTIONS_B
set user_level1 = user_level0
where indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
and analysis_group_id = p_Bsc_kpi_Entity_Rec.Bsc_kpi_Group_Id
and option_id = l_option_id
and parent_option_id = l_parent_option_id
and grandparent_option_id = l_grandparent_option_id;
update BSC_KPI_ANALYSIS_OPTIONS_B
set user_level1 = 0
where indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
and analysis_group_id = p_Bsc_kpi_Entity_Rec.Bsc_kpi_Group_Id
and option_id = l_option_id
and parent_option_id = l_parent_option_id
and grandparent_option_id = l_grandparent_option_id
and user_level0 > 1;
select count(option_id)
into l_count
from BSC_KPI_ANALYSIS_OPTIONS_B
where indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
and analysis_group_id = p_Bsc_kpi_Entity_Rec.Bsc_kpi_Group_Id
and parent_option_id = l_parent_option_id
and grandparent_option_id = l_grandparent_option_id
and user_level1 > 0;
function Is_Analysis_Option_Selected(
p_Bsc_kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_kpi_Entity_Rec
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
) return varchar2 IS
l_option_id number;
Select count(indicator)
into l_count
from BSC_KPI_ANALYSIS_OPTIONS_B
where indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
and analysis_group_id = p_Bsc_kpi_Entity_Rec.Bsc_kpi_Group_Id
and option_id = l_option_id
and parent_option_id = l_parent_option_id
and grandparent_option_id = l_grandparent_option_id
and user_level1 <> 0;
l_temp := Is_Analysis_Option_Selected(l_Bsc_kpi_Entity_Rec
,x_return_status
,x_msg_count
,x_msg_data);
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Is_Analysis_Option_Selected ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Is_Analysis_Option_Selected ';
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Is_Analysis_Option_Selected ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Is_Analysis_Option_Selected ';
end Is_Analysis_Option_Selected;
select count(ANALYSIS_GROUP_ID)
into l_count
from BSC_KPI_ANALYSIS_GROUPS
where indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
and PARENT_ANALYSIS_ID = p_Bsc_kpi_Entity_Rec.Bsc_kpi_Group_Id;
select count(option_id)
into l_count_child_options
from BSC_KPI_ANALYSIS_OPTIONS_B
where indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
and analysis_group_id = l_child_analysis_Group
and parent_option_id = l_option_id
and grandparent_option_id = l_parent_option_id;
Name : Delete_Ind_Cause_Effect_Rels
Description : This API deletes the cause and effect relationship
of the current indicator.
created by : ashankar 20-JUL-2005
/********************************************************/
PROCEDURE Delete_Ind_Cause_Effect_Rels(
p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.BSC_KPI_ENTITY_REC
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_count NUMBER;
SELECT COUNT(0)
INTO l_count
FROM bsc_kpis_b
WHERE indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
DELETE
FROM bsc_kpi_cause_effect_rels
WHERE cause_indicator =p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
AND NVL(cause_level,BSC_KPI_PUB.c_IND_LEVEL)=BSC_KPI_PUB.c_IND_LEVEL
AND NVL(effect_level,BSC_KPI_PUB.c_IND_LEVEL)=BSC_KPI_PUB.c_IND_LEVEL;
DELETE
FROM bsc_kpi_cause_effect_rels
WHERE effect_indicator =p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
AND NVL(cause_level,BSC_KPI_PUB.c_IND_LEVEL)=BSC_KPI_PUB.c_IND_LEVEL
AND NVL(effect_level,BSC_KPI_PUB.c_IND_LEVEL)=BSC_KPI_PUB.c_IND_LEVEL;
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Delete_Ind_Cause_Effect_Rels ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Delete_Ind_Cause_Effect_Rels ';
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Delete_Ind_Cause_Effect_Rels ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Delete_Ind_Cause_Effect_Rels ';
END Delete_Ind_Cause_Effect_Rels;
Name : Delete_Ind_Shell_Cmds
Description : This API deletes shell cammand entries
of the current indicator.
created by : ashankar 20-JUL-2005
/********************************************************/
PROCEDURE Delete_Ind_Shell_Cmds
(
p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.BSC_KPI_ENTITY_REC
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_count NUMBER;
SAVEPOINT DeleteIndShellCmds;
SELECT COUNT(0)
INTO l_count
FROM bsc_kpis_b
WHERE indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
DELETE
FROM bsc_kpi_shell_cmds_tl
WHERE indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
DELETE
FROM bsc_kpi_shell_cmds_user
WHERE indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
ROLLBACK TO DeleteIndShellCmds;
ROLLBACK TO DeleteIndShellCmds;
ROLLBACK TO DeleteIndShellCmds;
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Delete_Ind_Shell_Cmds ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Delete_Ind_Shell_Cmds ';
ROLLBACK TO DeleteIndShellCmds;
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Delete_Ind_Shell_Cmds ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Delete_Ind_Shell_Cmds ';
END Delete_Ind_Shell_Cmds;
Name : Delete_Ind_MM_Controls
Description : This API deletes multimedia entries
of the current indicator.
created by : ashankar 20-JUL-2005
/********************************************************/
PROCEDURE Delete_Ind_MM_Controls
(
p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.BSC_KPI_ENTITY_REC
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_count NUMBER;
SAVEPOINT DeleteIndMMControls;
SELECT COUNT(0)
INTO l_count
FROM bsc_kpis_b
WHERE indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
DELETE
FROM bsc_kpi_mm_controls
WHERE indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
ROLLBACK TO DeleteIndMMControls;
ROLLBACK TO DeleteIndMMControls;
ROLLBACK TO DeleteIndMMControls;
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Delete_Ind_MM_Controls ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Delete_Ind_MM_Controls ';
ROLLBACK TO DeleteIndMMControls;
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Delete_Ind_MM_Controls ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Delete_Ind_MM_Controls ';
END Delete_Ind_MM_Controls;
Name : Delete_Ind_Subtitles
Description : This API deletes subtitle entries of the current indicator.
created by : ashankar 20-JUL-2005
/********************************************************/
PROCEDURE Delete_Ind_Subtitles
(
p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.BSC_KPI_ENTITY_REC
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_count NUMBER;
SAVEPOINT DeleteIndSubtitles;
SELECT COUNT(0)
INTO l_count
FROM bsc_kpis_b
WHERE indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
DELETE
FROM bsc_kpi_subtitles_tl
WHERE indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
ROLLBACK TO DeleteIndSubtitles;
ROLLBACK TO DeleteIndSubtitles;
ROLLBACK TO DeleteIndSubtitles;
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Delete_Ind_Subtitles ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Delete_Ind_Subtitles ';
ROLLBACK TO DeleteIndSubtitles;
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Delete_Ind_Subtitles ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Delete_Ind_Subtitles ';
END Delete_Ind_Subtitles;
Name : Delete_Ind_SeriesColors
Description : This API deletes series colors of the current indicator.
created by : ashankar 20-JUL-2005
/********************************************************/
PROCEDURE Delete_Ind_SeriesColors
(
p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.BSC_KPI_ENTITY_REC
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_count NUMBER;
SAVEPOINT DeleteIndSeriesColors;
SELECT COUNT(0)
INTO l_count
FROM bsc_kpis_b
WHERE indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
DELETE
FROM bsc_kpi_series_colors
WHERE indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
ROLLBACK TO DeleteIndSeriesColors;
ROLLBACK TO DeleteIndSeriesColors;
ROLLBACK TO DeleteIndSeriesColors;
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Delete_Ind_SeriesColors ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Delete_Ind_SeriesColors ';
ROLLBACK TO DeleteIndSeriesColors;
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Delete_Ind_SeriesColors ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Delete_Ind_SeriesColors ';
END Delete_Ind_SeriesColors;
Name : DeleteIndImages
Description : This API deletes all the images attached with the objective.
created by : ashankar 20-JUL-2005
/********************************************************/
PROCEDURE Delete_Ind_Images
(
p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.BSC_KPI_ENTITY_REC
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_count NUMBER;
SAVEPOINT DeleteIndImages;
SELECT COUNT(0)
INTO l_count
FROM bsc_kpis_b
WHERE indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
DELETE
FROM bsc_sys_images
WHERE image_id IN (
SELECT image_id
FROM bsc_kpi_graphs
WHERE indicator =p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
);
DELETE
FROM bsc_kpi_graphs
WHERE indicator=p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
DELETE
FROM bsc_user_kpigraph_plugs
WHERE indicator=p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
ROLLBACK TO DeleteIndImages;
ROLLBACK TO DeleteIndImages;
ROLLBACK TO DeleteIndImages;
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Delete_Ind_Images ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Delete_Ind_Images ';
ROLLBACK TO DeleteIndImages;
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Delete_Ind_Images ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Delete_Ind_Images ';
END Delete_Ind_Images;
Name : Delete_Ind_Sys_Prop
Description : This API deletes all the system level proeprties attached to the objective
created by : ashankar 20-JUL-2005
/********************************************************/
PROCEDURE Delete_Ind_Sys_Prop
(
p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.BSC_KPI_ENTITY_REC
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_count NUMBER;
SAVEPOINT DeleteIndSysProp;
SELECT COUNT(0)
INTO l_count
FROM bsc_kpis_b
WHERE indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
DELETE
FROM bsc_sys_files
WHERE indicator=p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
DELETE
FROM bsc_sys_kpi_colors
WHERE indicator=p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
DELETE
FROM bsc_sys_objective_colors
WHERE indicator=p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
DELETE
FROM bsc_sys_labels_b
WHERE source_type = BSC_KPI_PUB.c_IND_TYPE
AND source_code = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
DELETE
FROM bsc_sys_labels_tl
WHERE source_type = BSC_KPI_PUB.c_IND_TYPE
AND source_code = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
DELETE
FROM bsc_sys_lines
WHERE source_type = BSC_KPI_PUB.c_IND_TYPE
AND source_code = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
DELETE
FROM bsc_sys_user_options
WHERE source_type = BSC_KPI_PUB.c_IND_TYPE
AND source_code = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
ROLLBACK TO DeleteIndSysProp;
ROLLBACK TO DeleteIndSysProp;
ROLLBACK TO DeleteIndSysProp;
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Delete_Ind_Sys_Prop ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Delete_Ind_Sys_Prop ';
ROLLBACK TO DeleteIndSysProp;
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Delete_Ind_Sys_Prop ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Delete_Ind_Sys_Prop ';
END Delete_Ind_Sys_Prop;
Name : Delete_Ind_Sys_Prop
Description : This API deletes objective comments
created by : ashankar 20-JUL-2005
/********************************************************/
PROCEDURE Delete_Ind_Comments
(
p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.BSC_KPI_ENTITY_REC
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_count NUMBER;
SAVEPOINT DeleteIndComments;
SELECT COUNT(0)
INTO l_count
FROM bsc_kpis_b
WHERE indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
DELETE
FROM bsc_kpi_comments
WHERE indicator=p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
ROLLBACK TO DeleteIndComments;
ROLLBACK TO DeleteIndComments;
ROLLBACK TO DeleteIndComments;
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Delete_Ind_Comments ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Delete_Ind_Comments ';
ROLLBACK TO DeleteIndComments;
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Delete_Ind_Comments ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Delete_Ind_Comments ';
END Delete_Ind_Comments;
Name : Delete_Ind_Tree_Nodes
Description : This API deletes nodes of the objectives
created by : ashankar 20-JUL-2005
/********************************************************/
PROCEDURE Delete_Ind_Tree_Nodes
(
p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.BSC_KPI_ENTITY_REC
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_count NUMBER;
SAVEPOINT DeleteIndTreeNodes;
SELECT COUNT(0)
INTO l_count
FROM bsc_kpis_b
WHERE indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
DELETE
FROM bsc_kpi_tree_nodes_b
WHERE indicator=p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
DELETE
FROM bsc_kpi_tree_nodes_tl
WHERE indicator=p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
ROLLBACK TO DeleteIndTreeNodes;
ROLLBACK TO DeleteIndTreeNodes;
ROLLBACK TO DeleteIndTreeNodes;
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Delete_Ind_Tree_Nodes ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Delete_Ind_Tree_Nodes ';
ROLLBACK TO DeleteIndTreeNodes;
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Delete_Ind_Tree_Nodes ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Delete_Ind_Tree_Nodes ';
END Delete_Ind_Tree_Nodes;
Name : Delete_Ind_User_Access
Description : This API deletes nodes of the objectives
created by : ashankar 20-JUL-2005
/********************************************************/
PROCEDURE Delete_Ind_User_Access
(
p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.BSC_KPI_ENTITY_REC
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_count NUMBER;
SAVEPOINT DeleteIndUserAccess;
SELECT COUNT(0)
INTO l_count
FROM bsc_kpis_b
WHERE indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
DELETE
FROM bsc_user_kpi_access
WHERE indicator=p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
ROLLBACK TO DeleteIndUserAccess;
ROLLBACK TO DeleteIndUserAccess;
ROLLBACK TO DeleteIndUserAccess;
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Delete_Ind_User_Access ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Delete_Ind_User_Access ';
ROLLBACK TO DeleteIndUserAccess;
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Delete_Ind_User_Access ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Delete_Ind_User_Access ';
END Delete_Ind_User_Access;
Name : Delete_Sim_Tree_Data
Description : This API deletes Simulation Tree Data
created by : ashankar 20-JUL-2005
/********************************************************/
PROCEDURE Delete_Sim_Tree_Data
(
p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.BSC_KPI_ENTITY_REC
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_count NUMBER;
SELECT image_id
FROM BSC_SYS_IMAGES
WHERE image_id NOT IN
( SELECT DISTINCT(image_id)
FROM BSC_SYS_IMAGES_MAP_TL
);
SAVEPOINT DeleteSimTreeData;
SELECT COUNT(0)
INTO l_count
FROM bsc_kpis_b
WHERE indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
SELECT short_name
INTO l_short_name
FROM bsc_kpis_vl
WHERE indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
DELETE
FROM bsc_kpi_tree_nodes_b
WHERE indicator=p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
DELETE
FROM bsc_kpi_tree_nodes_tl
WHERE indicator=p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
DELETE
FROM BSC_TAB_VIEW_LABELS_B
WHERE tab_id =BSC_KPI_PVT.c_SIM_TAB_ID
AND tab_view_id = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
DELETE
FROM BSC_TAB_VIEW_LABELS_TL
WHERE tab_id =BSC_KPI_PVT.c_SIM_TAB_ID
AND tab_view_id = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
DELETE
FROM BSC_SYS_IMAGES_MAP_TL
WHERE SOURCE_TYPE = 2
AND SOURCE_CODE = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
AND TYPE = BSC_SIMULATION_VIEW_PUB.c_TYPE;
DELETE
FROM BSC_SYS_IMAGES
WHERE IMAGE_ID = l_CustView_Rec.Bsc_Image_Id;
SELECT COUNT(0)
INTO l_count
FROM ak_regions
WHERE region_code =l_short_name;
BIS_FORM_FUNCTIONS_PUB.DELETE_FUNCTION_AND_MENU_ENT
(
p_function_name => l_short_name
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
BIS_AK_REGION_PUB.DELETE_REGION_AND_REGION_ITEMS
(
p_REGION_CODE => l_short_name
,p_REGION_APPLICATION_ID => 271
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
ROLLBACK TO DeleteSimTreeData;
ROLLBACK TO DeleteSimTreeData;
ROLLBACK TO DeleteSimTreeData;
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Delete_Ind_User_Access ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Delete_Ind_User_Access ';
ROLLBACK TO DeleteSimTreeData;
x_msg_data := x_msg_data||' -> BSC_KPI_PVT.Delete_Ind_User_Access ';
x_msg_data := SQLERRM||' at BSC_KPI_PVT.Delete_Ind_User_Access ';
END Delete_Sim_Tree_Data;