[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| - Update_Visibility |
| - Delete_Visibility |
+=======================================================================*/
-- Start of comments
-- API name : DOES_VISIBILITY_NAME_EXIST
-- Type : Private Function
-- Function : Validates if the Visibility Name is duplicate
-- Pre-reqs : None.
-- Parameters :
-- IN : P_VISIBILITY_ID NUMBER Required
-- P_NAME VARCHAR Required
-- OUT :
-- RETURN : VARCHAR2 -> either 'TRUE' OR 'FALSE'
--
-- History:
-- 07-Jul-03 Matt Kettle Created
-- 24-Sep-03 Matt Kettle Changed Check_Name_Exists to check dates
--
--
--
-- Notes :
-- 1) We only validate duplicate Name NOT description as well
--
-- End of comments
FUNCTION DOES_VISIBILITY_NAME_EXIST (
P_VISIBILITY_ID NUMBER,
P_NAME VARCHAR2
) RETURN VARCHAR2 IS
CURSOR Check_Name_Exists IS
SELECT count(*)
FROM CS_KB_VISIBILITIES_VL
WHERE name = P_NAME
AND sysdate BETWEEN nvl(Start_Date_Active, SYSDATE-1)
AND nvl(End_Date_Active, SYSDATE+1)
AND visibility_id <> P_VISIBILITY_ID;
CURSOR Check_Insert (v_vis_id NUMBER) IS
SELECT COUNT(*)
FROM CS_KB_VISIBILITIES_B
WHERE VISIBILITY_ID = v_vis_id;
SELECT nvl(max(position),0)+1
FROM CS_KB_VISIBILITIES_B;
SELECT COUNT(*)
FROM CS_KB_VISIBILITIES_B
WHERE position = v_position;
SELECT position
FROM CS_KB_VISIBILITIES_B
WHERE visibility_id = v_vis;
SELECT CS_KB_VISIBILITIES_B_S.nextval INTO l_seq from dual;
UPDATE CS_KB_VISIBILITIES_B
SET Position = Position + 1
WHERE Position >= l_new_position;
UPDATE CS_KB_CAT_GROUP_DENORM
SET Visibility_Position = Visibility_Position + 1
WHERE Visibility_Position >= l_new_position;
CS_KB_VISIBILITIES_PKG.INSERT_ROW(
X_ROWID => l_rowid,
X_VISIBILITY_ID => l_seq ,
X_POSITION => l_position,
X_START_DATE_ACTIVE => p_start_date_active,
X_END_DATE_ACTIVE => p_end_date_active,
X_NAME => p_name,
X_DESCRIPTION => p_description,
X_CREATION_DATE => l_date,
X_CREATED_BY => l_current_user,
X_LAST_UPDATE_DATE => l_date,
X_LAST_UPDATED_BY => l_current_user,
X_LAST_UPDATE_LOGIN => l_login,
X_ATTRIBUTE_CATEGORY => p_attribute_category,
X_ATTRIBUTE1 => p_attribute1,
X_ATTRIBUTE2 => p_attribute2,
X_ATTRIBUTE3 => p_attribute3,
X_ATTRIBUTE4 => p_attribute4,
X_ATTRIBUTE5 => p_attribute5,
X_ATTRIBUTE6 => p_attribute6,
X_ATTRIBUTE7 => p_attribute7,
X_ATTRIBUTE8 => p_attribute8,
X_ATTRIBUTE9 => p_attribute9,
X_ATTRIBUTE10 => p_attribute10,
X_ATTRIBUTE11 => p_attribute11,
X_ATTRIBUTE12 => p_attribute12,
X_ATTRIBUTE13 => p_attribute13,
X_ATTRIBUTE14 => p_attribute14,
X_ATTRIBUTE15 => p_attribute15 );
OPEN Check_Insert (l_seq);
FETCH Check_Insert INTO l_check;
CLOSE Check_Insert;
RAISE INSERT_FAILED;
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'cs.plsql.CS_KB_SECURITY_PVT.CREATE_VISIBILITY.insert',
'Visibility Insert Successfull='||l_seq);
END IF; -- Insert Successful Check
WHEN INSERT_FAILED THEN
ROLLBACK TO Create_Visibility_PVT;
FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'cs.plsql.CS_KB_SECURITY_PVT.CREATE_VISIBILITY.insertcheck',
'Insert Row has failed='||l_check);
PROCEDURE UPDATE_VISIBILITY (
P_VISIBILITY_ID IN NUMBER,
P_POSITION IN NUMBER,
P_START_DATE_ACTIVE IN DATE,
P_END_DATE_ACTIVE IN DATE,
P_NAME IN VARCHAR2,
P_DESCRIPTION IN VARCHAR2,
P_ATTRIBUTE_CATEGORY IN VARCHAR2,
P_ATTRIBUTE1 IN VARCHAR2,
P_ATTRIBUTE2 IN VARCHAR2,
P_ATTRIBUTE3 IN VARCHAR2,
P_ATTRIBUTE4 IN VARCHAR2,
P_ATTRIBUTE5 IN VARCHAR2,
P_ATTRIBUTE6 IN VARCHAR2,
P_ATTRIBUTE7 IN VARCHAR2,
P_ATTRIBUTE8 IN VARCHAR2,
P_ATTRIBUTE9 IN VARCHAR2,
P_ATTRIBUTE10 IN VARCHAR2,
P_ATTRIBUTE11 IN VARCHAR2,
P_ATTRIBUTE12 IN VARCHAR2,
P_ATTRIBUTE13 IN VARCHAR2,
P_ATTRIBUTE14 IN VARCHAR2,
P_ATTRIBUTE15 IN VARCHAR2,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_DATA OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER
) IS
l_check NUMBER := 0;
CURSOR Check_Update (v_vis_id NUMBER,
v_name VARCHAR2,
v_desc VARCHAR2 ) IS
SELECT COUNT(*)
FROM CS_KB_VISIBILITIES_VL
WHERE visibility_id = v_vis_id
AND name = v_name;
SAVEPOINT Update_Visibility_PVT;
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_KB_SECURITY_PVT.UPDATE_VISIBILITY.begin',
'User='||l_current_user);
CS_KB_VISIBILITIES_PKG.UPDATE_ROW(
X_VISIBILITY_ID => P_VISIBILITY_ID ,
X_POSITION => P_POSITION,
X_START_DATE_ACTIVE => P_START_DATE_ACTIVE,
X_END_DATE_ACTIVE => P_END_DATE_ACTIVE,
X_NAME => P_NAME,
X_DESCRIPTION => P_DESCRIPTION,
X_LAST_UPDATE_DATE => l_date,
X_LAST_UPDATED_BY => l_current_user,
X_LAST_UPDATE_LOGIN => l_login,
X_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => P_ATTRIBUTE1,
X_ATTRIBUTE2 => P_ATTRIBUTE2,
X_ATTRIBUTE3 => P_ATTRIBUTE3,
X_ATTRIBUTE4 => P_ATTRIBUTE4,
X_ATTRIBUTE5 => P_ATTRIBUTE5,
X_ATTRIBUTE6 => P_ATTRIBUTE6,
X_ATTRIBUTE7 => P_ATTRIBUTE7,
X_ATTRIBUTE8 => P_ATTRIBUTE8,
X_ATTRIBUTE9 => P_ATTRIBUTE9,
X_ATTRIBUTE10 => P_ATTRIBUTE10,
X_ATTRIBUTE11 => P_ATTRIBUTE11,
X_ATTRIBUTE12 => P_ATTRIBUTE12,
X_ATTRIBUTE13 => P_ATTRIBUTE13,
X_ATTRIBUTE14 => P_ATTRIBUTE14,
X_ATTRIBUTE15 => P_ATTRIBUTE15
);
OPEN Check_Update (P_VISIBILITY_ID, P_NAME, P_DESCRIPTION);
FETCH Check_Update INTO l_check;
CLOSE Check_Update;
RAISE UPDATE_FAILED;
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'cs.plsql.CS_KB_SECURITY_PVT.UPDATE_VISIBILITY.update',
'Visibility Update Successfull='||P_VISIBILITY_ID);
END IF; -- Update Successful Check
FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'cs.plsql.CS_KB_SECURITY_PVT.UPDATE_VISIBILITY.invparam',
'P_NAME='||P_NAME||
'P_VISIBILITY='||P_VISIBILITY_ID);
WHEN UPDATE_FAILED THEN
IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'cs.plsql.CS_KB_SECURITY_PVT.UPDATE_VISIBILITY.updcheck',
'Update Row has failed='||l_check);
FND_MESSAGE.set_name('CS', 'CS_KB_C_UPDATE_ERR');
FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'cs.plsql.CS_KB_SECURITY_PVT.UPDATE_VISIBILITY.dupcheck',
'Visibility Name is a Duplicate='||P_NAME);
ROLLBACK TO Update_Visibility_PVT;
FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, 'cs.plsql.CS_KB_SECURITY_PVT.UPDATE_VISIBILITY.UNEXPECTED',
' Error= '||sqlerrm);
END UPDATE_VISIBILITY;
PROCEDURE DELETE_VISIBILITY (
P_VISIBILITY_ID IN NUMBER,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_DATA OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER
) IS
CURSOR USED_IN_SOLUTION IS
SELECT count(*)
FROM CS_KB_SETS_B
WHERE visibility_id = P_VISIBILITY_ID
AND STATUS <> 'OBS'
AND LATEST_VERSION_FLAG = 'Y';
SELECT count(*)
FROM CS_KB_SOLN_CATEGORIES_B
WHERE visibility_id = P_VISIBILITY_ID;
SELECT count(*)
FROM CS_KB_VISIBILITIES_B
WHERE visibility_id = P_VISIBILITY_ID
AND sysdate between nvl(Start_Date_Active, sysdate -1)
and nvl(End_Date_Active, sysdate +1);
SELECT Position
FROM CS_KB_VISIBILITIES_B
WHERE visibility_id = P_VISIBILITY_ID;
SAVEPOINT Delete_Visibility_PVT;
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_KB_SECURITY_PVT.DELETE_VISIBILITY.begin',
'User='||l_current_user);
RAISE UNABLE_TO_DELETE_VIS;
UPDATE CS_KB_VISIBILITIES_B
SET End_Date_Active = sysdate - 0.001,
Last_Update_date = sysdate,
Last_updated_By = FND_GLOBAL.User_id
WHERE Visibility_Id = P_VISIBILITY_ID;
RAISE DELETE_FAILED;
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_KB_SECURITY_PVT.DELETE_VISIBILITY.end',
'Status='||X_RETURN_STATUS);
FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'cs.plsql.CS_KB_SECURITY_PVT.DELETE_VISIBILITY.invparam',
'P_VISIBILITY_ID='||P_VISIBILITY_ID);
WHEN UNABLE_TO_DELETE_VIS THEN
IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'cs.plsql.CS_KB_SECURITY_PVT.DELETE_VISIBILITY.invdel',
'P_VISIBILITY_ID='||P_VISIBILITY_ID);
WHEN DELETE_FAILED THEN
ROLLBACK TO Delete_Visibility_PVT;
FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'cs.plsql.CS_KB_SECURITY_PVT.DELETE_VISIBILITY.deletefail',
'Delete Row has failed='||l_vis_count);
FND_MESSAGE.set_name('CS', 'CS_KB_C_DELETE_ERR');
ROLLBACK TO Delete_Visibility_PVT;
FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'cs.plsql.CS_KB_SECURITY_PVT.DELETE_VISIBILITY.indexsync',
'Index Sync failed='||l_request_id);
ROLLBACK TO Delete_Visibility_PVT;
FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, 'cs.plsql.CS_KB_SECURITY_PVT.DELETE_VISIBILITY.UNEXPECTED',
' Error= '||sqlerrm);
END DELETE_VISIBILITY;
| - Update_Category_Group |
| - Delete_Category_Group |
+=======================================================================*/
-- Start of comments
-- API name : DOES_CATEGORY_GROUP_NAME_EXIST
-- Type : Private Function
-- Function : Validates if the Category Group Name is duplicate
-- Pre-reqs : None.
-- Parameters :
-- IN : P_CATEGORY_GROUP_ID NUMBER Required
-- P_NAME VARCHAR Required
-- OUT :
-- RETURN : VARCHAR2 -> either 'TRUE' OR 'FALSE'
--
-- History:
-- 07-Jul-03 Matt Kettle Created
--
--
--
-- Notes :
-- 1) We only validate duplicate Name NOT description as well
--
-- End of comments
FUNCTION DOES_CATEGORY_GROUP_NAME_EXIST (
P_CATEGORY_GROUP_ID NUMBER,
P_NAME VARCHAR2
) RETURN VARCHAR2 IS
CURSOR Check_Name_Exists IS
SELECT count(*)
FROM CS_KB_CATEGORY_GROUPS_VL
WHERE name = P_NAME
AND category_group_id <> P_CATEGORY_GROUP_ID;
CURSOR Check_Insert (v_cg_id NUMBER) IS
SELECT COUNT(*)
FROM CS_KB_CATEGORY_GROUPS_B
WHERE CATEGORY_GROUP_ID = v_cg_id;
SELECT CS_KB_CATEGORY_GROUPS_B_S.nextval INTO l_seq from dual;
CS_KB_CATEGORY_GROUPS_PKG.INSERT_ROW (
X_ROWID => l_rowid,
X_CATEGORY_GROUP_ID => l_seq ,
X_START_DATE_ACTIVE => p_start_date_active,
X_END_DATE_ACTIVE => p_end_date_active,
X_NAME => p_name,
X_DESCRIPTION => p_description,
X_CREATION_DATE => l_date,
X_CREATED_BY => l_current_user,
X_LAST_UPDATE_DATE => l_date,
X_LAST_UPDATED_BY => l_current_user,
X_LAST_UPDATE_LOGIN => l_login,
X_ATTRIBUTE_CATEGORY => p_attribute_category,
X_ATTRIBUTE1 => p_attribute1,
X_ATTRIBUTE2 => p_attribute2,
X_ATTRIBUTE3 => p_attribute3,
X_ATTRIBUTE4 => p_attribute4,
X_ATTRIBUTE5 => p_attribute5,
X_ATTRIBUTE6 => p_attribute6,
X_ATTRIBUTE7 => p_attribute7,
X_ATTRIBUTE8 => p_attribute8,
X_ATTRIBUTE9 => p_attribute9,
X_ATTRIBUTE10 => p_attribute10,
X_ATTRIBUTE11 => p_attribute11,
X_ATTRIBUTE12 => p_attribute12,
X_ATTRIBUTE13 => p_attribute13,
X_ATTRIBUTE14 => p_attribute14,
X_ATTRIBUTE15 => p_attribute15);
OPEN Check_Insert (l_seq);
FETCH Check_Insert INTO l_check;
CLOSE Check_Insert;
RAISE INSERT_FAILED;
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'cs.plsql.CS_KB_SECURITY_PVT.CREATE_CATEGORY_GROUP.insert',
'Visibility Insert Successfull='||l_seq);
END IF; -- Insert Successful Check
WHEN INSERT_FAILED THEN
IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'cs.plsql.CS_KB_SECURITY_PVT.CREATE_CATEGORY_GROUP.insertcheck',
'Insert Row has failed='||l_check);
PROCEDURE UPDATE_CATEGORY_GROUP (
P_CATEGORY_GROUP_ID IN NUMBER,
P_START_DATE_ACTIVE IN DATE,
P_END_DATE_ACTIVE IN DATE,
P_NAME IN VARCHAR,
P_DESCRIPTION IN VARCHAR,
P_ATTRIBUTE_CATEGORY IN VARCHAR2,
P_ATTRIBUTE1 IN VARCHAR2,
P_ATTRIBUTE2 IN VARCHAR2,
P_ATTRIBUTE3 IN VARCHAR2,
P_ATTRIBUTE4 IN VARCHAR2,
P_ATTRIBUTE5 IN VARCHAR2,
P_ATTRIBUTE6 IN VARCHAR2,
P_ATTRIBUTE7 IN VARCHAR2,
P_ATTRIBUTE8 IN VARCHAR2,
P_ATTRIBUTE9 IN VARCHAR2,
P_ATTRIBUTE10 IN VARCHAR2,
P_ATTRIBUTE11 IN VARCHAR2,
P_ATTRIBUTE12 IN VARCHAR2,
P_ATTRIBUTE13 IN VARCHAR2,
P_ATTRIBUTE14 IN VARCHAR2,
P_ATTRIBUTE15 IN VARCHAR2,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_DATA OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER
) IS
l_check NUMBER;
CURSOR Check_Update IS
SELECT COUNT(*)
FROM CS_KB_CATEGORY_GROUPS_VL
WHERE CATEGORY_GROUP_ID = P_CATEGORY_GROUP_ID
AND NAME = P_NAME;
SAVEPOINT Update_Category_Group_PVT;
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_KB_SECURITY_PVT.UPDATE_CATEGORY_GROUP.begin',
'User='||l_current_user);
CS_KB_CATEGORY_GROUPS_PKG.UPDATE_ROW (
X_CATEGORY_GROUP_ID => P_CATEGORY_GROUP_ID ,
X_START_DATE_ACTIVE => p_start_date_active,
X_END_DATE_ACTIVE => p_end_date_active,
X_NAME => p_name,
X_DESCRIPTION => p_description,
X_LAST_UPDATE_DATE => l_date,
X_LAST_UPDATED_BY => l_current_user,
X_LAST_UPDATE_LOGIN => l_login,
X_ATTRIBUTE_CATEGORY => p_attribute_category,
X_ATTRIBUTE1 => p_attribute1,
X_ATTRIBUTE2 => p_attribute2,
X_ATTRIBUTE3 => p_attribute3,
X_ATTRIBUTE4 => p_attribute4,
X_ATTRIBUTE5 => p_attribute5,
X_ATTRIBUTE6 => p_attribute6,
X_ATTRIBUTE7 => p_attribute7,
X_ATTRIBUTE8 => p_attribute8,
X_ATTRIBUTE9 => p_attribute9,
X_ATTRIBUTE10 => p_attribute10,
X_ATTRIBUTE11 => p_attribute11,
X_ATTRIBUTE12 => p_attribute12,
X_ATTRIBUTE13 => p_attribute13,
X_ATTRIBUTE14 => p_attribute14,
X_ATTRIBUTE15 => p_attribute15);
OPEN Check_Update;
FETCH Check_Update INTO l_check;
CLOSE Check_Update;
RAISE UPDATE_FAILED;
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'cs.plsql.CS_KB_SECURITY_PVT.UPDATE_CATEGORY_GROUP.update',
'Cat Grp Update Successfull='||P_CATEGORY_GROUP_ID);
END IF; -- Update Successful Check
FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'cs.plsql.CS_KB_SECURITY_PVT.UPDATE_CATEGORY_GROUP.invparam',
'P_NAME='||P_NAME||
'P_CATEGORY_GROUP_ID='||P_CATEGORY_GROUP_ID);
FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'cs.plsql.CS_KB_SECURITY_PVT.UPDATE_CATEGORY_GROUP.dupcheck',
'Update is Duplicate='||p_name);
WHEN UPDATE_FAILED THEN
IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'cs.plsql.CS_KB_SECURITY_PVT.UPDATE_CATEGORY_GROUP.updatecheck',
'Update Row has failed='||l_check);
FND_MESSAGE.set_name('CS', 'CS_KB_C_UPDATE_ERR');
ROLLBACK TO Update_Category_Group_PVT;
FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, 'cs.plsql.CS_KB_SECURITY_PVT.UPDATE_CATEGORY_GROUP.UNEXPECTED',
' Error= '||sqlerrm);
END UPDATE_CATEGORY_GROUP;
PROCEDURE DELETE_CATEGORY_GROUP (
P_CATEGORY_GROUP_ID IN NUMBER,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_DATA OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER
) IS
CURSOR CHECK_DELETE IS
SELECT count(*)
FROM CS_KB_CATEGORY_GROUPS_VL
WHERE category_group_id = P_CATEGORY_GROUP_ID;
SELECT count(*)
FROM CS_KB_CAT_GROUP_MEMBERS
WHERE category_group_id = P_CATEGORY_GROUP_ID;
SELECT count(*)
FROM CS_KB_CAT_GROUP_FLOWS
WHERE category_group_id = P_CATEGORY_GROUP_ID;
SAVEPOINT Delete_Category_Group_PVT;
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_KB_SECURITY_PVT.DELETE_CATEGORY_GROUP.begin',
'User='||l_current_user);
OPEN CHECK_DELETE;
FETCH CHECK_DELETE INTO l_valid;
CLOSE CHECK_DELETE;
CS_KB_CATEGORY_GROUPS_PKG.DELETE_ROW (X_CATEGORY_GROUP_ID => P_CATEGORY_GROUP_ID);
OPEN CHECK_DELETE;
FETCH CHECK_DELETE INTO l_valid;
CLOSE CHECK_DELETE;
RAISE DELETE_FAILED;
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_KB_SECURITY_PVT.DELETE_CATEGORY_GROUP.end',
'Status='||X_RETURN_STATUS);
FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'cs.plsql.CS_KB_SECURITY_PVT.DELETE_CATEGORY_GROUP.invparam',
'P_CATEGORY_GROUP_ID='||P_CATEGORY_GROUP_ID);
FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'cs.plsql.CS_KB_SECURITY_PVT.DELETE_CATEGORY_GROUP.invdel',
'P_CATEGORY_GROUP_ID='||P_CATEGORY_GROUP_ID);
WHEN DELETE_FAILED THEN
IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'cs.plsql.CS_KB_SECURITY_PVT.DELETE_CATEGORY_GROUP.deletefail',
'Delete Row has failed='||l_valid);
FND_MESSAGE.set_name('CS', 'CS_KB_C_DELETE_ERR');
ROLLBACK TO Delete_Category_Group_PVT;
FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'cs.plsql.CS_KB_SECURITY_PVT.DELETE_CATEGORY_GROUP.memexist',
'Members Exist for Cat Grp='||l_member_count);
ROLLBACK TO Delete_Category_Group_PVT;
FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'cs.plsql.CS_KB_SECURITY_PVT.DELETE_CATEGORY_GROUP.flowexist',
'Flows Exist for Cat Grp='||l_flow_count);
ROLLBACK TO Delete_Category_Group_PVT;
FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, 'cs.plsql.CS_KB_SECURITY_PVT.DELETE_CATEGORY_GROUP.UNEXPECTED',
' Error= '||sqlerrm);
END DELETE_CATEGORY_GROUP;
| - Delete_Category_Group_Member |
+=======================================================================*/
-- Start of comments
-- API name : DOES_CAT_GROUP_MEMBER_EXIST
-- Type : Private Function
-- Function : Validates if a CG Member exists already
-- Pre-reqs : None.
-- Parameters :
-- IN : P_CATEGORY_GROUP_ID NUMBER Required
-- P_CATEGORY_ID NUMBER Required
--
-- History:
-- 07-Jul-03 Matt Kettle Created
--
--
--
-- Notes :
-- 1) Validates if a CG Member exists already - ie if the
-- Category Exists or if the Category is included already
-- by a Parent
-- Validations:
--
-- End of comments
FUNCTION DOES_CAT_GROUP_MEMBER_EXIST (
P_CATEGORY_GROUP_ID NUMBER,
P_CATEGORY_ID NUMBER
) RETURN VARCHAR2 IS
CURSOR CHECK_PARENT IS
SELECT count(m.category_id)
FROM CS_KB_CAT_GROUP_MEMBERS m
WHERE m.category_group_id = P_CATEGORY_GROUP_ID
AND category_id in (
SELECT b.category_id
FROM cs_kb_soln_categories_b b
START WITH b.category_id = P_CATEGORY_ID
CONNECT BY PRIOR b.parent_category_id = b.category_id);
CURSOR Check_Insert (v_cg_id NUMBER, v_cat_id NUMBER) IS
SELECT COUNT(*)
FROM CS_KB_CAT_GROUP_MEMBERS
WHERE CATEGORY_GROUP_ID = v_cg_id
AND CATEGORY_ID = v_cat_id;
SELECT count(*)
FROM CS_KB_CATEGORY_GROUPS_B
WHERE category_group_id = P_CATEGORY_GROUP_ID;
SELECT count(*)
FROM CS_KB_SOLN_CATEGORIES_B
WHERE category_id = P_CATEGORY_ID;
CS_KB_CAT_GROUP_MEMBERS_PKG.INSERT_ROW (
X_ROWID => l_rowid,
X_CATEGORY_GROUP_ID => P_CATEGORY_GROUP_ID ,
X_CATEGORY_ID => P_CATEGORY_ID ,
X_CREATION_DATE => l_date,
X_CREATED_BY => l_current_user,
X_LAST_UPDATE_DATE => l_date,
X_LAST_UPDATED_BY => l_current_user,
X_LAST_UPDATE_LOGIN => l_login,
X_ATTRIBUTE_CATEGORY => p_attribute_category,
X_ATTRIBUTE1 => p_attribute1,
X_ATTRIBUTE2 => p_attribute2,
X_ATTRIBUTE3 => p_attribute3,
X_ATTRIBUTE4 => p_attribute4,
X_ATTRIBUTE5 => p_attribute5,
X_ATTRIBUTE6 => p_attribute6,
X_ATTRIBUTE7 => p_attribute7,
X_ATTRIBUTE8 => p_attribute8,
X_ATTRIBUTE9 => p_attribute9,
X_ATTRIBUTE10 => p_attribute10,
X_ATTRIBUTE11 => p_attribute11,
X_ATTRIBUTE12 => p_attribute12,
X_ATTRIBUTE13 => p_attribute13,
X_ATTRIBUTE14 => p_attribute14,
X_ATTRIBUTE15 => p_attribute15);
OPEN Check_Insert (P_CATEGORY_GROUP_ID, P_CATEGORY_ID);
FETCH Check_Insert INTO l_check;
CLOSE Check_Insert;
RAISE INSERT_FAILED;
DELETE FROM CS_KB_CAT_GROUP_MEMBERS
WHERE CATEGORY_GROUP_ID = P_CATEGORY_GROUP_ID
AND CATEGORY_ID IN (SELECT b.category_id
FROM cs_kb_soln_categories_b b
START WITH b.parent_category_id = P_CATEGORY_ID
CONNECT BY PRIOR b.category_id = b.parent_category_id);
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'cs.plsql.CS_KB_SECURITY_PVT.CREATE_CATEGORY_GROUP_MEMBER.insert',
'CG Member Insert Successfull='||P_CATEGORY_GROUP_ID||'-'||P_CATEGORY_ID);
END IF; -- Insert Successful Check
WHEN INSERT_FAILED THEN
IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'cs.plsql.CS_KB_SECURITY_PVT.CREATE_CATEGORY_GROUP.insertcheck',
'Insert Row has failed='||l_check);
PROCEDURE DELETE_CATEGORY_GROUP_MEMBER (
P_CATEGORY_GROUP_ID IN NUMBER,
P_CATEGORY_ID IN NUMBER,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_DATA OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER
) IS
CURSOR CHECK_DELETE IS
SELECT count(*)
FROM CS_KB_CAT_GROUP_MEMBERS
WHERE category_group_id = P_CATEGORY_GROUP_ID
AND category_id = P_CATEGORY_ID;
SAVEPOINT Delete_Cat_Group_Member_PVT;
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_KB_SECURITY_PVT.DELETE_CAT_GROUP_MEMBER.begin',
'User='||l_current_user);
OPEN CHECK_DELETE;
FETCH CHECK_DELETE INTO l_valid;
CLOSE CHECK_DELETE;
CS_KB_CAT_GROUP_MEMBERS_PKG.DELETE_ROW (X_CATEGORY_GROUP_ID => P_CATEGORY_GROUP_ID,
X_CATEGORY_ID => P_CATEGORY_ID);
OPEN CHECK_DELETE;
FETCH CHECK_DELETE INTO l_valid;
CLOSE CHECK_DELETE;
RAISE DELETE_FAILED;
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'cs.plsql.CS_KB_SECURITY_PVT.DELETE_CAT_GROUP_MEMBER.end',
'Status='||X_RETURN_STATUS);
FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'cs.plsql.CS_KB_SECURITY_PVT.DELETE_CAT_GROUP_MEMBER.invparam',
'P_CATEGORY_GROUP_ID='||P_CATEGORY_GROUP_ID||
' P_CATEGORY_ID='||P_CATEGORY_ID);
WHEN DELETE_FAILED THEN
IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'cs.plsql.CS_KB_SECURITY_PVT.DELETE_CAT_GROUP_MEMBER.deletefail',
'Delete Row has failed='||l_valid);
FND_MESSAGE.set_name('CS', 'CS_KB_C_DELETE_ERR');
ROLLBACK TO Delete_Cat_Group_Member_PVT;
FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'cs.plsql.CS_KB_SECURITY_PVT.DELETE_CAT_GROUP_MEMBER.indexsync',
'Index Sync failed='||l_request_id);
ROLLBACK TO Delete_Cat_Group_Member_PVT;
FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, 'cs.plsql.CS_KB_SECURITY_PVT.DELETE_CATEGORY_GROUP_MEMBER.UNEXPECTED',
' Error= '||sqlerrm);
END DELETE_CATEGORY_GROUP_MEMBER;
| - Update_Denorm_Vis_Position |
| - Add_Category_To_Denorm |
| - Update_Category_To_Denorm |
| - Remove_Category_From_Cat_Group |
| - Populate_Cat_Grp_Denorm |
+=======================================================================*/
-- Start of comments
-- API name : ADD_CAT_GRP_MEMBER_TO_DENORM
-- Type : Private
-- Function : Populates Denorm Table with new Member + Children
-- Pre-reqs : None.
-- Parameters :
-- IN : P_CATEGORY_GROUP_ID NUMBER Required
-- P_CATEGORY_ID NUMBER Required
-- OUT :
--
-- History:
-- 08-Jul-03 Matt Kettle Created
--
--
--
-- Notes :
-- 1) For every Category_Id added to the CS_KB_CAT_GROUP_MEMBERS table
-- this api populates the Denorm table with the Member Category_id
-- and all of its Children
--
-- Used By:
-- 1) CREATE_CATEGORY_GROUP_MEMBER
-- End of comments
PROCEDURE ADD_CAT_GRP_MEMBER_TO_DENORM ( P_CATEGORY_GROUP_ID NUMBER,
P_CATEGORY_ID NUMBER
) IS
CURSOR GET_CHILD_CATEGORIES IS
SELECT New_Members.category_id, New_Members.visibility_id, v.position
FROM (
SELECT b.category_id, b.visibility_id
FROM cs_kb_soln_categories_b b
START WITH b.parent_category_id = P_CATEGORY_ID
CONNECT BY PRIOR b.category_id = b.parent_category_id
UNION
SELECT b.category_id, b.visibility_id
FROM cs_kb_soln_categories_b b
WHERE b.category_id = P_CATEGORY_ID
) New_Members ,
CS_KB_VISIBILITIES_b v
WHERE New_Members.visibility_id = v.visibility_id
AND NOT EXISTS (SELECT 'x'
FROM CS_KB_CAT_GROUP_DENORM Denorm
WHERE Denorm.Category_Group_id = P_CATEGORY_GROUP_ID
AND Denorm.Child_Category_Id = New_Members.Category_Id);
INSERT INTO CS_KB_CAT_GROUP_DENORM (
CATEGORY_GROUP_ID,
CHILD_CATEGORY_ID,
VISIBILITY_ID,
VISIBILITY_POSITION,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
Values (
P_CATEGORY_GROUP_ID,
child_cat_id_list(i),
vis_id_list(i),
position_list(i),
l_date,
l_current_user,
l_date,
l_current_user,
l_login
);
SELECT b.category_id
FROM cs_kb_soln_categories_b b
START WITH b.parent_category_id = P_CATEGORY_ID
CONNECT BY PRIOR b.category_id = b.parent_category_id
UNION
SELECT b.category_id
FROM cs_kb_soln_categories_b b
WHERE b.category_id = P_CATEGORY_ID;
DELETE FROM CS_KB_CAT_GROUP_DENORM
WHERE Category_Group_id = P_CATEGORY_GROUP_ID
AND Child_Category_Id = child_cat_id_list(i);
SELECT Category_id
FROM CS_KB_SOLN_CATEGORIES_B
WHERE Category_id = P_CATEGORY_ID
AND Parent_Category_Id = P_PARENT_CATEGORY_ID;
SELECT Position
FROM CS_KB_VISIBILITIES_B
WHERE Visibility_Id = P_VISIBILITY_ID
AND sysdate BETWEEN nvl(Start_Date_Active, sysdate-1)
AND nvl(End_Date_Active, sysdate+1);
SELECT Distinct Category_Group_Id
FROM CS_KB_CAT_GROUP_DENORM
WHERE CHILD_CATEGORY_ID = P_PARENT_CATEGORY_ID;
INSERT INTO CS_KB_CAT_GROUP_DENORM
( CATEGORY_GROUP_ID,
CHILD_CATEGORY_ID,
VISIBILITY_ID,
VISIBILITY_POSITION,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
Values ( cat_grp_id_list(i), --x.Category_Group_id,
P_CATEGORY_ID,
P_VISIBILITY_ID,
l_position,
l_date,
l_current_user,
l_date,
l_current_user,
l_login
);
PROCEDURE UPDATE_CATEGORY_TO_DENORM (
P_CATEGORY_ID IN NUMBER,
P_VISIBILITY_ID IN NUMBER,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_DATA OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER
) IS
CURSOR Validate_Category IS
SELECT Category_id, Visibility_id
FROM CS_KB_SOLN_CATEGORIES_B
WHERE Category_id = P_CATEGORY_ID;
SELECT Position
FROM CS_KB_VISIBILITIES_B
WHERE Visibility_id = P_VISIBILITY_ID;
UPDATE CS_KB_CAT_GROUP_DENORM
SET Visibility_id = P_VISIBILITY_ID,
Visibility_Position = l_position,
Last_Update_Date = l_date,
Last_Updated_By = l_current_user,
Last_Update_Login = l_login
WHERE Child_Category_Id = P_CATEGORY_ID
AND Visibility_id <> P_VISIBILITY_ID;
FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'cs.plsql.CS_KB_SECURITY_PVT.UPDATE_CATEGORY_TO_DENORM.invparam',
'P_CATEGORY_ID='||P_CATEGORY_ID||
'P_VISIBILITY_ID='||P_VISIBILITY_ID);
FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, 'cs.plsql.CS_KB_SECURITY_PVT.UPDATE_CATEGORY_TO_DENORM.UNEXPECTED',
' Error= '||sqlerrm);
END UPDATE_CATEGORY_TO_DENORM;
CURSOR Check_Delete_Member IS
SELECT count(*)
FROM CS_KB_CAT_GROUP_MEMBERS
WHERE Category_id = P_CATEGORY_ID;
CURSOR Check_Delete_Denorm IS
SELECT count(*)
FROM CS_KB_CAT_GROUP_DENORM
WHERE Child_Category_id = P_CATEGORY_ID;
DELETE FROM CS_KB_CAT_GROUP_MEMBERS
WHERE Category_Id = P_CATEGORY_ID;
DELETE FROM CS_KB_CAT_GROUP_DENORM
WHERE Child_Category_Id = P_CATEGORY_ID;
OPEN Check_Delete_Member;
FETCH Check_Delete_Member INTO l_member_count;
CLOSE Check_Delete_Member;
OPEN Check_Delete_Denorm;
FETCH Check_Delete_Denorm INTO l_denorm_count;
CLOSE Check_Delete_Denorm;
RAISE DELETE_FAILED;
WHEN DELETE_FAILED THEN
IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'cs.plsql.CS_KB_SECURITY_PVT.REMOVE_CATEGORY_FROM_CAT_GROUP.delfail',
'Member Count='||l_member_count||
'Denorm Count='||l_denorm_count);
FND_MESSAGE.set_name('CS', 'CS_KB_C_DELETE_ERR');
SELECT b.category_id, b.visibility_id , v.position
FROM cs_kb_soln_categories_b b , CS_KB_VISIBILITIES_b v
WHERE b.visibility_id = v.visibility_id
START WITH b.parent_category_id in (select m.category_id
FROM CS_KB_CAT_GROUP_MEMBERS m
where m.category_group_id = v_cg)
CONNECT BY PRIOR b.category_id = b.parent_category_id
UNION
SELECT b.category_id, b.visibility_id, v.position
FROM cs_kb_soln_categories_b b, CS_KB_VISIBILITIES_b v
WHERE b.category_id in (select m.category_id
FROM CS_KB_CAT_GROUP_MEMBERS m
where m.category_group_id = v_cg)
AND b.visibility_id = v.visibility_id;
DELETE FROM CS_KB_CAT_GROUP_DENORM
WHERE category_group_id = P_CATEGORY_GROUP_ID;
INSERT INTO CS_KB_CAT_GROUP_DENORM (
CATEGORY_GROUP_ID,
CHILD_CATEGORY_ID,
VISIBILITY_ID,
VISIBILITY_POSITION,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
Values (
P_CATEGORY_GROUP_ID, --x.category_group_id,
child_cat_id_list(i),
vis_id_list(i),
position_list(i), --x.position --position_list(i) --,
l_date,
l_current_user,
l_date,
l_current_user,
l_login
);
SELECT b.category_id, b.visibility_id , v.position
FROM cs_kb_soln_categories_b b , CS_KB_VISIBILITIES_b v
WHERE b.visibility_id = v.visibility_id
START WITH b.parent_category_id in (select m.category_id
FROM CS_KB_CAT_GROUP_MEMBERS m
where m.category_group_id = v_cg)
CONNECT BY PRIOR b.category_id = b.parent_category_id
UNION
SELECT b.category_id, b.visibility_id, v.position
FROM cs_kb_soln_categories_b b, CS_KB_VISIBILITIES_b v
WHERE b.category_id in (select m.category_id
FROM CS_KB_CAT_GROUP_MEMBERS m
where m.category_group_id = v_cg)
AND b.visibility_id = v.visibility_id;
SELECT Category_Group_Id
FROM CS_KB_CATEGORY_GROUPS_B
WHERE sysdate BETWEEN nvl(start_date_active, sysdate-1)
AND nvl(end_date_active, sysdate+1);
DELETE FROM CS_KB_CAT_GROUP_DENORM;
INSERT INTO CS_KB_CAT_GROUP_DENORM (
CATEGORY_GROUP_ID,
CHILD_CATEGORY_ID,
VISIBILITY_ID,
VISIBILITY_POSITION,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
Values (
x.category_group_id,
child_cat_id_list(i),
vis_id_list(i),
position_list(i), --x.position --position_list(i) --,
l_date,
l_current_user,
l_date,
l_current_user,
l_login
);
SELECT Category_Group_Id
FROM CS_KB_CATEGORY_GROUPS_B
WHERE Category_Group_id = fnd_profile.value('CS_KB_ASSIGNED_CATEGORY_GROUP')
AND sysdate BETWEEN nvl(Start_Date_Active, sysdate-1)
AND nvl(End_Date_Active, sysdate+1);
SELECT Position
FROM CS_KB_VISIBILITIES_B
WHERE VISIBILITY_ID = fnd_profile.value('CS_KB_ASSIGNED_SOLUTION_VISIBILITY_LEVEL')
AND sysdate BETWEEN nvl(Start_Date_Active, sysdate-1)
AND nvl(End_Date_Active, sysdate+1);
SELECT lookup_code
FROM cs_lookups
WHERE lookup_type = 'CS_KB_ACCESS_LEVEL'
AND lookup_code = fnd_profile.value('CS_KB_ASSIGNED_STATEMENT_VISIBILITY_LEVEL')
AND sysdate BETWEEN nvl(Start_Date_Active, sysdate-1)
AND nvl(End_Date_Active, sysdate+1);
SELECT u.User_id, rg.responsibility_id, rg.responsibility_application_id
FROM fnd_user_resp_groups rg,
fnd_user u
WHERE u.user_id = decode(P_USER_ID, -1, FND_GLOBAL.USER_ID, P_USER_ID)
AND u.user_id = rg.user_id
AND rg.responsibility_id = decode(P_USER_ID, -1, FND_GLOBAL.RESP_ID, rg.responsibility_id)
AND sysdate between nvl(rg.start_date, sysdate) and nvl(rg.end_date, sysdate);
SELECT TO_NUMBER(FND_PROFILE.VALUE_SPECIFIC('CS_KB_ASSIGNED_CATEGORY_GROUP',
v_user,
v_resp,
v_app ) ),
TO_NUMBER(FND_PROFILE.VALUE_SPECIFIC('CS_KB_ASSIGNED_SOLUTION_VISIBILITY_LEVEL',
v_user,
v_resp,
v_app ) ),
TO_NUMBER(FND_PROFILE.VALUE_SPECIFIC('CS_KB_ASSIGNED_STATEMENT_VISIBILITY_LEVEL',
v_user,
v_resp,
v_app ))
FROM dual;
SELECT count(*)
FROM CS_KB_SETS_B S,
CS_KB_VISIBILITIES_B V
WHERE S.Set_Id = P_SET_ID
AND S.Visibility_id = V.Visibility_id
AND V.Position >= (SELECT Vis.Position
FROM CS_KB_VISIBILITIES_B Vis
WHere Vis.Visibility_id = v_soln_vis
AND sysdate BETWEEN nvl(Vis.Start_Date_Active, sysdate-1)
AND nvl(Vis.End_Date_Active , sysdate+1)
)
AND EXISTS (SELECT 'x'
FROM CS_KB_SET_CATEGORIES SC,
CS_KB_CAT_GROUP_DENORM D
WHERE SC.Set_id = S.Set_id
AND D.Category_Group_Id = v_cat_grp
AND SC.Category_id = D.Child_Category_id
AND D.Visibility_Position >= (SELECT Vis.Position
FROM CS_KB_VISIBILITIES_B Vis
WHERE Vis.Visibility_id = v_soln_vis
AND sysdate BETWEEN nvl(Vis.Start_Date_Active, sysdate-1)
AND nvl(Vis.End_Date_Active , sysdate+1)
)
);
SELECT count(*)
FROM CS_KB_SET_ELES SE
WHERE SE.Set_id = P_SET_ID;
SELECT count(*)
FROM CS_KB_SET_ELES SE,
CS_KB_ELEMENTS_B E
WHERE SE.Set_id = P_SET_ID --S.Set_Id
AND SE.Element_id = E.Element_Id
AND E.Access_Level >= (SELECT lookup_code
FROM cs_lookups
WHERE lookup_type = 'CS_KB_ACCESS_LEVEL'
AND lookup_code = v_stmt_vis
AND sysdate BETWEEN nvl(Start_Date_Active, sysdate-1)
AND nvl(End_Date_Active, sysdate+1)
);
SELECT c.category_id, c.parent_category_id, c.visibility_id
FROM CS_KB_SOLN_CATEGORIES_B c
START WITH c.category_id = P_CATEGORY_ID
CONNECT BY PRIOR c.category_id = c.parent_category_id
ORDER BY level asc;
DELETE FROM cs_kb_cat_group_denorm d
WHERE d.child_category_id IN (SELECT b.category_id
FROM cs_kb_soln_categories_b b
START WITH b.category_id = P_CATEGORY_ID
CONNECT BY PRIOR b.category_id =
b.parent_category_id)
AND NOT EXISTS (SELECT 'x'
FROM cs_kb_cat_group_members m
WHERE m.category_id IN ( SELECT b.category_id
FROM cs_kb_soln_categories_b b
START WITH b.category_id =
P_CATEGORY_ID
CONNECT BY PRIOR
b.category_id = b.parent_category_id)
AND m.category_id = d.child_category_id
AND m.category_group_id = d.category_group_id );
RAISE UPDATE_FAILED;
WHEN UPDATE_FAILED THEN
IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,
'cs.plsql.CS_KB_SECURITY_PVT.MOVE_CAT_IN_DENORM.update',
'P_CATEGORY_ID='||P_CATEGORY_ID --||
--'P_PARENT_CATEGORY_ID='||P_PARENT_CATEGORY_ID||
--'P_VISIBILITY_ID='||P_VISIBILITY_ID
);