The following lines contain the word 'select', 'insert', 'update' or 'delete':
Procedure Create_User_Ind_Selection(
p_api_version IN NUMBER,
p_Indicator_Region_Rec
IN BIS_INDICATOR_REGION_PUB.Indicator_Region_Rec_Type,
x_return_status OUT NOCOPY VARCHAR2,
x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
)
IS
l_user_ind_id NUMBER;
select rowid from BIS_USER_IND_SELECTIONS
where IND_SELECTION_ID = l_user_ind_id;
select BIS_USER_IND_SELECTIONS_S.NEXTVAL into l_user_ind_id from dual;
insert into BIS_USER_IND_SELECTIONS (
IND_SELECTION_ID
,USER_ID
,TARGET_LEVEL_ID
-- ,ORGANIZATION_ID
-- ,ORG_LEVEL_VALUE
,LABEL
,PLUG_ID
,RESPONSIBILITY_ID
,DIMENSION1_LEVEL_VALUE
,DIMENSION2_LEVEL_VALUE
,DIMENSION3_LEVEL_VALUE
,DIMENSION4_LEVEL_VALUE
,DIMENSION5_LEVEL_VALUE
,DIMENSION6_LEVEL_VALUE
,DIMENSION7_LEVEL_VALUE
,PLAN_ID
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN)
values (
l_user_ind_id
,p_Indicator_Region_Rec.USER_ID
,p_Indicator_Region_Rec.Target_Level_ID
-- ,NULL
-- ,p_Indicator_Region_Rec.Org_Level_Value_Id
,p_Indicator_Region_Rec.LABEL
,p_Indicator_Region_Rec.PLUG_ID
,DECODE(p_Indicator_Region_Rec.RESPONSIBILITY_ID,FND_API.G_MISS_NUM,NULL,
p_Indicator_Region_Rec.RESPONSIBILITY_ID)
,DECODE(p_Indicator_Region_Rec.DIM1_LEVEL_VALUE_ID,'+',NULL,'-',NULL,
p_Indicator_Region_Rec.DIM1_LEVEL_VALUE_ID)
,DECODE(p_Indicator_Region_Rec.DIM2_LEVEL_VALUE_ID,'+',NULL,'-',NULL,
p_Indicator_Region_Rec.DIM2_LEVEL_VALUE_ID)
,DECODE(p_Indicator_Region_Rec.DIM3_LEVEL_VALUE_ID,'+',NULL,'-',NULL,
p_Indicator_Region_Rec.DIM3_LEVEL_VALUE_ID)
,DECODE(p_Indicator_Region_Rec.DIM4_LEVEL_VALUE_ID,'+',NULL,'-',NULL,
p_Indicator_Region_Rec.DIM4_LEVEL_VALUE_ID)
,DECODE(p_Indicator_Region_Rec.DIM5_LEVEL_VALUE_ID,'+',NULL,'-',NULL,
p_Indicator_Region_Rec.DIM5_LEVEL_VALUE_ID)
,DECODE(p_Indicator_Region_Rec.DIM6_LEVEL_VALUE_ID,'+',NULL,'-',NULL,
p_Indicator_Region_Rec.DIM6_LEVEL_VALUE_ID)
,DECODE(p_Indicator_Region_Rec.DIM7_LEVEL_VALUE_ID,'+',NULL,'-',NULL,
p_Indicator_Region_Rec.DIM7_LEVEL_VALUE_ID)
,p_Indicator_Region_Rec.PLAN_ID
,sysdate
,FND_GLOBAL.USER_ID
,sysdate
,FND_GLOBAL.USER_ID
,FND_GLOBAL.LOGIN_ID);
END Create_User_Ind_Selection;
Procedure Retrieve_User_Ind_Selections(
p_api_version IN NUMBER,
p_user_id IN NUMBER ,
p_user_name IN VARCHAR2 ,
p_plug_id IN NUMBER ,
p_all_info IN VARCHAR2 Default FND_API.G_TRUE,
x_Indicator_Region_Tbl
OUT NOCOPY BIS_INDICATOR_REGION_PUB.Indicator_Region_Tbl_Type,
x_return_status OUT NOCOPY VARCHAR2,
x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
)
IS
cursor c_user_ind_selection is
SELECT distinct a.ind_selection_id
,a.user_id
,a.target_level_id
,a.label
,a.plug_id
,a.dimension1_level_value
,a.dimension2_level_value
,a.dimension3_level_value
,a.dimension4_level_value
,a.dimension5_level_value
,a.dimension6_level_value
,a.dimension7_level_value
,a.responsibility_id
,a.plan_id
FROM bis_user_ind_selections a
,fnd_user_resp_groups b
,bis_indicators c
,bis_target_levels d
WHERE a.user_id = p_user_id
AND a.plug_id = p_plug_id
AND a.user_id = b.user_id
AND b.start_date <= sysdate
AND NVL(b.end_date, sysdate) >= sysdate
AND d.target_level_id = a.target_level_id
AND d.indicator_id = c.indicator_id
ORDER BY a.ind_selection_id;
FOR c_userIndSelection in c_user_ind_selection LOOP
l_Indicator_Region_Rec.ind_selection_id
:= c_userIndSelection.ind_selection_id;
:= c_userIndSelection.target_level_id;
l_Indicator_Region_Rec.Label := c_userIndSelection.label;
:= c_userIndSelection.dimension1_level_value;
:= c_userIndSelection.dimension2_level_value;
:= c_userIndSelection.dimension3_level_value;
:= c_userIndSelection.dimension4_level_value;
:= c_userIndSelection.dimension5_level_value;
:= c_userIndSelection.dimension6_level_value;
:= c_userIndSelection.dimension7_level_value;
:= c_userIndSelection.Plan_ID;
SELECT short_name, name
INTO x_Indicator_Region_Tbl(i).Plan_Short_Name
, x_Indicator_Region_Tbl(i).Plan_Name
FROM BISBV_BUSINESS_PLANS
WHERE plan_id = x_Indicator_Region_Tbl(i).Plan_ID;
htp.p('BIS_INDICATOR_REGION_PVT.Retrieve_User_Ind_Selections:'); htp.para;
htp.p('BIS_INDICATOR_REGION_PVT.Retrieve_User_Ind_Selections:G_EXC_UNEXPECTED_ERROR'); htp.para;
htp.p('BIS_INDICATOR_REGION_PVT.Retrieve_User_Ind_Selections:OTHERS'); htp.para;
END Retrieve_User_Ind_Selections;
Procedure Retrieve_User_Ind_Selections(
p_api_version IN NUMBER
, p_all_info IN VARCHAR2 Default FND_API.G_TRUE
, p_Target_level_id IN NUMBER
, x_Indicator_Region_Tbl
OUT NOCOPY BIS_INDICATOR_REGION_PUB.Indicator_Region_Tbl_Type
, x_return_status OUT NOCOPY VARCHAR2
, x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
)
IS
l_Indicator_Region_rec BIS_INDICATOR_REGION_PUB.Indicator_Region_Rec_Type;
CURSOR c_user_ind_selection IS
select * from bis_user_ind_selections
where Target_Level_ID = p_target_level_id;
FOR c_userIndSelection in c_user_ind_selection LOOP
l_Indicator_Region_Rec.ind_selection_id
:= c_userIndSelection.ind_selection_id;
:= c_userIndSelection.target_level_id;
l_Indicator_Region_Rec.Label := c_userIndSelection.label;
:= c_userIndSelection.dimension1_level_value;
:= c_userIndSelection.dimension2_level_value;
:= c_userIndSelection.dimension3_level_value;
:= c_userIndSelection.dimension4_level_value;
:= c_userIndSelection.dimension5_level_value;
:= c_userIndSelection.dimension6_level_value;
:= c_userIndSelection.dimension7_level_value;
:= c_userIndSelection.Plan_ID;
SELECT name
INTO x_Indicator_Region_Tbl(i).Plan_Name
FROM BISBV_BUSINESS_PLANS
WHERE plan_id = x_Indicator_Region_Tbl(i).Plan_ID;
END Retrieve_User_Ind_Selections;
Procedure Update_User_Ind_Selection(
p_api_version IN NUMBER,
p_user_id IN NUMBER Default BIS_UTILITIES_PUB.G_NULL_NUM,
p_user_name IN VARCHAR2 Default BIS_UTILITIES_PUB.G_NULL_CHAR,
p_plug_id IN NUMBER ,
p_Indicator_Region_Rec
IN BIS_INDICATOR_REGION_PUB.Indicator_Region_Rec_Type,
x_return_status OUT NOCOPY VARCHAR2,
x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
)
IS
BEGIN
x_return_status := FND_API.G_RET_STS_SUCCESS;
END Update_User_Ind_Selection;
Procedure Delete_User_Ind_Selections(
p_api_version IN NUMBER,
p_user_id IN NUMBER Default BIS_UTILITIES_PUB.G_NULL_NUM,
p_user_name IN VARCHAR2 Default BIS_UTILITIES_PUB.G_NULL_CHAR,
p_plug_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
)
IS
l_error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
DELETE FROM BIS_USER_IND_SELECTIONS
WHERE user_id = p_user_id
AND plug_id = p_plug_id;
htp.p('BIS_INDICATOR_REGION_PVT.Delete_User_Ind_Selections:G_EXC_ERROR'); htp.para;
htp.p('BIS_INDICATOR_REGION_PVT.Delete_User_Ind_Selections:G_EXC_UNEXPECTED_ERROR'); htp.para;
htp.p('BIS_INDICATOR_REGION_PVT.Delete_User_Ind_Selections:OTHERS'); htp.para;
END Delete_User_Ind_Selections;
SELECT DISTINCT uis.ind_selection_id
, uis.label
FROM bis_user_ind_selections uis
WHERE uis.user_id = l_user_ID
AND uis.plug_id = p_Plug_ID;
SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = p_user_name;
x_label_tbl(i).Ind_Selection_ID := crlabel.Ind_Selection_ID;
Procedure Validate_User_Ind_Selection(
p_api_version IN NUMBER,
p_event IN VARCHAR2,
p_user_id IN NUMBER,
p_Indicator_Region_Rec IN BIS_INDICATOR_REGION_PUB.Indicator_Region_Rec_Type,
x_return_status OUT NOCOPY VARCHAR2
)
IS
e_InvalidRecordException EXCEPTION;
ELSIF p_event = 'UPDATE' THEN
null;
ELSIF p_event = 'DELETE' THEN
null;
END Validate_User_Ind_Selection;
'UPDATE',
'RETRIEVE',
'DELETE')
THEN
RAISE e_InvalidEventException;
IF p_event IN ('CREATE','UPDATE') THEN
/*
IF (NOT BIS_UTILITIES_PUB.Value_Not_Missing(
p_Indicator_Region_Rec.Target_Level_ID)) OR
(NOT BIS_UTILITIES_PUB.Value_Not_Missing(
p_Indicator_Region_Rec.org_level_value_id)) THEN
*/
IF BIS_UTILITIES_PUB.Value_Not_Missing(
p_Indicator_Region_Rec.Target_Level_ID) = FND_API.G_FALSE
-- mdamle 01/15/2001 - Use Dim6 and Dim7
-- OR BIS_UTILITIES_PUB.Value_Not_Missing(
-- p_Indicator_Region_Rec.Org_Level_Value_ID) = FND_API.G_FALSE
THEN
RAISE e_MissingValuesException;