The following lines contain the word 'select', 'insert', 'update' or 'delete':
IF(l_Func_Area_Rec.Last_Updated_By IS NULL) THEN
l_Func_Area_Rec.Last_Updated_By := l_Func_Area_Rec.Created_By;
IF(l_Func_Area_Rec.Last_Update_Login IS NULL) THEN
l_Func_Area_Rec.Last_Update_Login := FND_GLOBAL.LOGIN_ID;
IF(l_Func_Area_Rec.Last_Update_Date IS NULL) THEN
l_Func_Area_Rec.Creation_Date := SYSDATE;
l_Func_Area_Rec.Last_Update_Date := SYSDATE;
l_Func_Area_Rec.Creation_Date := l_Func_Area_Rec.Last_Update_Date;
INSERT INTO BIS_FUNCTIONAL_AREAS
(
FUNCTIONAL_AREA_ID
, SHORT_NAME
, CREATED_BY
, CREATION_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
)
VALUES
(
l_Func_Area_Rec.Functional_Area_Id
, l_Func_Area_Rec.Short_Name
, l_Func_Area_Rec.Created_By
, l_Func_Area_Rec.Creation_Date
, l_Func_Area_Rec.Last_Updated_By
, l_Func_Area_Rec.Last_Update_Date
, l_Func_Area_Rec.Last_Update_Login
);
INSERT INTO BIS_FUNCTIONAL_AREAS_TL
(
FUNCTIONAL_AREA_ID
, NAME
, DESCRIPTION
, LANGUAGE
, SOURCE_LANG
, CREATED_BY
, CREATION_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
)
SELECT
l_Func_Area_Rec.Functional_Area_Id
, l_Func_Area_Rec.Name
, l_Func_Area_Rec.Description
, L.LANGUAGE_CODE
, USERENV('LANG')
, l_Func_Area_Rec.Created_By
, l_Func_Area_Rec.Creation_Date
, l_Func_Area_Rec.Last_Updated_By
, l_Func_Area_Rec.Last_Update_Date
, l_Func_Area_Rec.Last_Update_Login
FROM FND_LANGUAGES L
WHERE L.INSTALLED_FLAG IN ('I', 'B')
AND NOT EXISTS
(
SELECT NULL
FROM BIS_FUNCTIONAL_AREAS_TL T
WHERE T.FUNCTIONAL_AREA_ID = l_Func_Area_Rec.Functional_Area_Id
AND T.LANGUAGE = L.LANGUAGE_CODE
);
PROCEDURE Update_Functional_Area(
p_Api_Version IN NUMBER
,p_Commit IN VARCHAR2 := FND_API.G_FALSE
,p_Func_Area_Rec IN BIS_FUNCTIONAL_AREA_PUB.Functional_Area_Rec_Type
,x_Return_Status OUT NOCOPY VARCHAR2
,x_Msg_Count OUT NOCOPY NUMBER
,x_Msg_Data OUT NOCOPY VARCHAR2
) IS
l_Count NUMBER;
SAVEPOINT UpdateFuncAreaSP_Pvt;
IF(p_Func_Area_Rec.Last_Update_Date IS NULL) THEN
l_Func_Area_Rec.Last_Update_Date := SYSDATE;
l_Func_Area_Rec.Last_Update_Date := p_Func_Area_Rec.Last_Update_Date ;
IF (p_Func_Area_Rec.Last_Updated_By IS NULL) THEN
l_Func_Area_Rec.Last_Updated_By := FND_GLOBAL.USER_ID;
l_Func_Area_Rec.Last_Updated_By := p_Func_Area_Rec.Last_Updated_By;
IF (p_Func_Area_Rec.Last_Update_Login IS NULL) THEN
l_Func_Area_Rec.Last_Update_Login := FND_GLOBAL.LOGIN_ID;
l_Func_Area_Rec.Last_Update_Login := p_Func_Area_Rec.Last_Update_Login;
UPDATE BIS_FUNCTIONAL_AREAS
SET
LAST_UPDATED_BY = l_Func_Area_Rec.Last_Updated_By
, LAST_UPDATE_DATE = l_Func_Area_Rec.Last_Update_Date
, LAST_UPDATE_LOGIN = l_Func_Area_Rec.Last_Update_Login
WHERE
SHORT_NAME = l_Func_Area_Rec.Short_Name;
ROLLBACK TO UpdateFuncAreaSP_Pvt;
ROLLBACK TO UpdateFuncAreaSP_Pvt;
ROLLBACK TO UpdateFuncAreaSP_Pvt;
x_msg_data := x_msg_data||' -> BIS_FUNCTIONAL_AREA_PVT.Update_Functional_Area ';
x_msg_data := SQLERRM||' at BIS_FUNCTIONAL_AREA_PVT.Update_Functional_Area ';
ROLLBACK TO UpdateFuncAreaSP_Pvt;
x_msg_data := x_msg_data||' -> BIS_FUNCTIONAL_AREA_PVT.Update_Functional_Area ';
x_msg_data := SQLERRM||' at BIS_FUNCTIONAL_AREA_PVT.Update_Functional_Area ';
END Update_Functional_Area;
IF (p_Func_Area_Rec.Last_Update_Date IS NULL) THEN
l_Func_Area_Rec.Last_Update_Date := SYSDATE;
l_Func_Area_Rec.Last_Update_Date := p_Func_Area_Rec.Last_Update_Date;
IF (p_Func_Area_Rec.Last_Updated_By IS NULL) THEN
l_Func_Area_Rec.Last_Updated_By := FND_GLOBAL.USER_ID;
l_Func_Area_Rec.Last_Updated_By := p_Func_Area_Rec.Last_Updated_By;
IF (p_Func_Area_Rec.Last_Update_Login IS NULL) THEN
l_Func_Area_Rec.Last_Update_Login := FND_GLOBAL.LOGIN_ID;
l_Func_Area_Rec.Last_Update_Login := p_Func_Area_Rec.Last_Update_Login;
UPDATE BIS_FUNCTIONAL_AREAS_TL
SET
NAME = l_Func_Area_Rec.Name
, DESCRIPTION = l_Func_Area_Rec.Description
, LAST_UPDATED_BY = l_Func_Area_Rec.Last_Updated_By
, LAST_UPDATE_DATE = l_Func_Area_Rec.Last_Update_Date
, LAST_UPDATE_LOGIN = l_Func_Area_Rec.Last_Update_Login
, SOURCE_LANG = USERENV('LANG')
WHERE
FUNCTIONAL_AREA_ID = l_Func_Area_Rec.Functional_Area_Id
AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG);
SELECT F.FUNCTIONAL_AREA_ID
, F.SHORT_NAME
, F.NAME
, F.DESCRIPTION
, F.CREATED_BY
, F.CREATION_DATE
, F.LAST_UPDATED_BY
, F.LAST_UPDATE_DATE
, F.LAST_UPDATE_LOGIN
FROM BIS_FUNCTIONAL_AREAS_VL F
WHERE F.SHORT_NAME = p_Func_Area_Rec.Short_Name;
x_Func_Area_Rec.Last_Updated_By := cRFA.LAST_UPDATED_BY;
x_Func_Area_Rec.Last_Update_Date := cRFA.LAST_UPDATE_DATE;
x_Func_Area_Rec.Last_Update_Login := cRFA.LAST_UPDATE_LOGIN;
INSERT INTO BIS_FUNC_AREA_APP_DEPENDENCY
(
FUNCTIONAL_AREA_ID
,APPLICATION_ID
)
VALUES
(
p_Functional_Area_Id
,p_Application_Id
);
PROCEDURE Update_Func_Area_Apps_Dep (
p_Api_Version IN NUMBER
,p_Commit IN VARCHAR2 := FND_API.G_FALSE
,p_Functional_Area_Id IN NUMBER
,p_Application_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;
SAVEPOINT UpdateFuncAreaAppDepSP_Pvt;
ROLLBACK TO UpdateFuncAreaAppDepSP_Pvt;
ROLLBACK TO UpdateFuncAreaAppDepSP_Pvt;
ROLLBACK TO UpdateFuncAreaAppDepSP_Pvt;
x_msg_data := x_msg_data||' -> BIS_FUNCTIONAL_AREA_PVT.Update_Func_Area_Apps_Dep ';
x_msg_data := SQLERRM||' at BIS_FUNCTIONAL_AREA_PVT.Update_Func_Area_Apps_Dep ';
ROLLBACK TO UpdateFuncAreaAppDepSP_Pvt;
x_msg_data := x_msg_data||' -> BIS_FUNCTIONAL_AREA_PVT.Update_Func_Area_Apps_Dep ';
x_msg_data := SQLERRM||' at BIS_FUNCTIONAL_AREA_PVT.Update_Func_Area_Apps_Dep ';
END Update_Func_Area_Apps_Dep;
PROCEDURE Delete_Functional_Area(
p_Api_Version IN NUMBER
,p_Commit IN VARCHAR2 := FND_API.G_FALSE
,p_Func_Area_Rec IN BIS_FUNCTIONAL_AREA_PUB.Functional_Area_Rec_Type
,x_Return_Status OUT NOCOPY VARCHAR2
,x_Msg_Count OUT NOCOPY NUMBER
,x_Msg_Data OUT NOCOPY VARCHAR2
) IS
l_Func_Area_Rec BIS_FUNCTIONAL_AREA_PUB.Functional_Area_Rec_Type;
SELECT B.FUNCTIONAL_AREA_ID,
B.APPLICATION_ID
FROM BIS_FUNC_AREA_APP_DEPENDENCY B
WHERE B.FUNCTIONAL_AREA_ID = l_Func_Area_Rec.Functional_Area_Id;
SAVEPOINT DeleteFuncAreaSP_Pvt;
DELETE BIS_FUNCTIONAL_AREAS
WHERE FUNCTIONAL_AREA_ID = l_Func_Area_Rec.Functional_Area_Id
AND SHORT_NAME = l_Func_Area_Rec.Short_Name;
DELETE BIS_FUNCTIONAL_AREAS_TL
WHERE FUNCTIONAL_AREA_ID = l_Func_Area_Rec.Functional_Area_Id;
ROLLBACK TO DeleteFuncAreaSP_Pvt;
ROLLBACK TO DeleteFuncAreaSP_Pvt;
ROLLBACK TO DeleteFuncAreaSP_Pvt;
x_msg_data := x_msg_data||' -> BIS_FUNCTIONAL_AREA_PVT.Delete_Functional_Area ';
x_msg_data := SQLERRM||' at BIS_FUNCTIONAL_AREA_PVT.Delete_Functional_Area ';
ROLLBACK TO DeleteFuncAreaSP_Pvt;
x_msg_data := x_msg_data||' -> BIS_FUNCTIONAL_AREA_PVT.Delete_Functional_Area ';
x_msg_data := SQLERRM||' at BIS_FUNCTIONAL_AREA_PVT.Delete_Functional_Area ';
END Delete_Functional_Area;
DELETE BIS_FUNC_AREA_APP_DEPENDENCY B
WHERE B.FUNCTIONAL_AREA_ID = p_Functional_Area_Id
AND B.APPLICATION_ID = p_Application_Id;
DELETE FROM BIS_FUNCTIONAL_AREAS_TL T
WHERE NOT EXISTS
(
SELECT NULL
FROM BIS_FUNCTIONAL_AREAS B
WHERE B.FUNCTIONAL_AREA_ID = T.FUNCTIONAL_AREA_ID
);
UPDATE BIS_FUNCTIONAL_AREAS_TL T SET (
NAME,
DESCRIPTION
) = (SELECT
B.NAME,
B.DESCRIPTION
FROM BIS_FUNCTIONAL_AREAS_TL B
WHERE B.FUNCTIONAL_AREA_ID = T.FUNCTIONAL_AREA_ID
AND B.LANGUAGE = T.SOURCE_LANG)
WHERE (
T.FUNCTIONAL_AREA_ID,
T.LANGUAGE
) IN (SELECT
SUBT.FUNCTIONAL_AREA_ID,
SUBT.LANGUAGE
FROM BIS_FUNCTIONAL_AREAS_TL SUBB, BIS_FUNCTIONAL_AREAS_TL SUBT
WHERE SUBB.FUNCTIONAL_AREA_ID = SUBT.FUNCTIONAL_AREA_ID
AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
AND (
SUBB.NAME <> SUBT.NAME
OR SUBB.DESCRIPTION <> SUBT.DESCRIPTION
)
);
INSERT INTO BIS_FUNCTIONAL_AREAS_TL
(
FUNCTIONAL_AREA_ID
, NAME
, DESCRIPTION
, LANGUAGE
, SOURCE_LANG
, CREATED_BY
, CREATION_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
)
SELECT
B.FUNCTIONAL_AREA_ID
, B.NAME
, B.DESCRIPTION
, L.LANGUAGE_CODE
, B.SOURCE_LANG
, B.CREATED_BY
, B.CREATION_DATE
, B.LAST_UPDATED_BY
, B.LAST_UPDATE_DATE
, B.LAST_UPDATE_LOGIN
FROM BIS_FUNCTIONAL_AREAS_TL B, FND_LANGUAGES L
WHERE L.INSTALLED_FLAG IN ('I', 'B')
AND B.LANGUAGE = USERENV('LANG')
AND NOT EXISTS
(
SELECT NULL
FROM BIS_FUNCTIONAL_AREAS_TL T
WHERE T.FUNCTIONAL_AREA_ID = B.FUNCTIONAL_AREA_ID
AND T.LANGUAGE = L.LANGUAGE_CODE
);