[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| update_element_CLOB to validate statement_type.
| 24-JAN-2003 MKETTLE Added Update_Statement
| 29-JUL-2003 MKETTLE Added Update_Statement_Admin to be used by the
| Global Statement Update in OA
| 24-SEP-2003 MKETTLE Added ASAP indexing to Update_Statement_Admin
| 06-OCT-2003 MKETTLE Changed Update_Statement_Admin to cater for a
| duplicate found within the same solution
| 31-OCT-2003 MKETTLE In GSU before creating a new Statement check
| if the Statement has changed first
| 18-Nov-2003 MKETTLE Cleanup for 11.5.10
| - Obsolete unused apis
| - Moved table Handlers to ELEMENTS_PKG
| 25-Nov-2003 MKETTLE Added Obsolete_Unused_Statements
| 21-Apr-2004 MKETTLE Fix for Bug 3576066
| 22-Apr-2004 MKETTLE Added rollback/savepoint: Update_Statement_Admin
| 21-Apr-2005 MKETTLE Commented Cursor Check_Statement_Cat_Grp_Usage in
| Update_Statement_Admin, since not used (Perf Rep)
| 17-May-2005 MKETTLE Cleanup - Removed unused apis + cursors
| apis removed in 115.52:
| Get_Previous_Version_id
| Get_Lock_Info
| Locked_By
| Incr_Element_Element
| 09-Aug-2005 MKETTLE Resized Elements_Tl.Name variable to 2000 in
| api Is_Element_Created_Dup
+======================================================================*/
-- return other_element_id if duplicate can be reused
-- return 0 if no duplicate found
FUNCTION Is_Element_Created_Dup(
P_ELEMENT_ID IN NUMBER)
RETURN NUMBER
IS
l_element_number VARCHAR(30);
SELECT b.element_number, b.element_type_id, b.access_level, tl.name, tl.description
INTO l_element_number, l_element_type_id, l_access_level, l_name, l_desc
FROM CS_KB_ELEMENTS_B b,
CS_KB_ELEMENTS_TL tl
WHERE b.element_id = tl.element_id
AND tl.language = USERENV('LANG')
AND b.element_id = p_element_id;
SELECT tl.element_id,
tl.description,
b.status
FROM CS_KB_ELEMENTS_TL tl,
CS_KB_ELEMENTS_B b
WHERE tl.name = c_name
AND tl.language = USERENV('LANG')
AND tl.element_id = b.element_id
AND b.element_number <> c_element_number
AND b.status = 'PUBLISHED'
AND b.element_type_id = c_element_type_id
AND b.access_level = c_access_level;
FUNCTION Is_Element_Updated_Dup(
P_ELEMENT_ID IN NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN Is_Element_Created_Dup(p_element_id);
END Is_Element_Updated_Dup;
SELECT element_number
INTO l_element_number
FROM CS_KB_ELEMENTS_B
WHERE element_id = p_element_id;
SELECT MAX(element_id)
INTO l_latest_version_id
FROM CS_KB_ELEMENTS_B
WHERE element_number = p_element_number;
FUNCTION Update_Element(
P_ELEMENT_ID IN NUMBER,
P_ELEMENT_NUMBER IN VARCHAR2,
P_ELEMENT_TYPE_ID IN NUMBER,
P_DESC IN VARCHAR2,
P_NAME IN VARCHAR2,
P_STATUS IN VARCHAR2,
P_ACCESS_LEVEL IN NUMBER,
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,
P_START_ACTIVE_DATE IN DATE,
P_END_ACTIVE_DATE IN DATE,
P_CONTENT_TYPE IN VARCHAR2 )
RETURN NUMBER
IS
l_offset NUMBER;
l_updated_by NUMBER;
l_ret := Update_Element_CLOB(
P_ELEMENT_ID => p_element_id,
P_ELEMENT_NUMBER => p_element_number,
P_ELEMENT_TYPE_ID => p_element_type_id,
P_DESC => l_clob,
P_NAME => p_name,
P_STATUS => p_status,
P_ACCESS_LEVEL => p_access_level,
P_ATTRIBUTE_CATEGORY => p_attribute_category,
P_ATTRIBUTE1 => p_attribute1,
P_ATTRIBUTE2 => p_attribute2,
P_ATTRIBUTE3 => p_attribute3,
P_ATTRIBUTE4 => p_attribute4,
P_ATTRIBUTE5 => p_attribute5,
P_ATTRIBUTE6 => p_attribute6,
P_ATTRIBUTE7 => p_attribute7,
P_ATTRIBUTE8 => p_attribute8,
P_ATTRIBUTE9 => p_attribute9,
P_ATTRIBUTE10 => p_attribute10,
P_ATTRIBUTE11 => p_attribute11,
P_ATTRIBUTE12 => p_attribute12,
P_ATTRIBUTE13 => p_attribute13,
P_ATTRIBUTE14 => p_attribute14,
P_ATTRIBUTE15 => p_attribute15,
P_START_ACTIVE_DATE => p_start_active_date,
P_END_ACTIVE_DATE => p_end_active_date,
P_CONTENT_TYPE => p_content_type );
END Update_Element;
FUNCTION Update_Element_CLOB(
p_element_id IN NUMBER,
p_element_number IN VARCHAR2,
p_element_type_id IN NUMBER,
p_desc IN CLOB,
p_name IN VARCHAR2,
p_status IN VARCHAR2,
p_access_level IN NUMBER,
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,
p_start_active_date IN DATE,
p_end_active_date IN DATE,
p_content_type IN VARCHAR2 )
RETURN NUMBER
IS
l_return NUMBER;
Update_Statement(
p_element_id => p_element_id,
p_element_number => p_element_number,
p_element_type_id => p_element_type_id,
p_desc => p_desc,
p_name => p_name,
p_status => p_status,
p_access_level => p_access_level,
p_attribute_category => p_attribute_category,
p_attribute1 => p_attribute1,
p_attribute2 => p_attribute2,
p_attribute3 => p_attribute3,
p_attribute4 => p_attribute4,
p_attribute5 => p_attribute5,
p_attribute6 => p_attribute6,
p_attribute7 => p_attribute7,
p_attribute8 => p_attribute8,
p_attribute9 => p_attribute9,
p_attribute10 => p_attribute10,
p_attribute11 => p_attribute11,
p_attribute12 => p_attribute12,
p_attribute13 => p_attribute13,
p_attribute14 => p_attribute14,
p_attribute15 => p_attribute15,
p_start_active_date => p_start_active_date,
p_end_active_date => p_end_active_date,
p_content_type => p_content_type,
x_return => l_return,
x_return_status => l_return_status,
x_msg_data => l_msg_data,
x_msg_count => l_msg_count);
END Update_Element_CLOB;
FUNCTION Delete_Element(
P_ELEMENT_NUMBER IN VARCHAR2)
RETURN NUMBER
IS
l_ret NUMBER;
SELECT COUNT(*) INTO l_count
FROM CS_KB_SET_ELES
WHERE element_id = Get_Latest_Version_Id(p_element_number);
SELECT COUNT(*) INTO l_count
FROM CS_KB_ELEMENT_LINKS
WHERE element_id = Get_Latest_Version_Id(p_element_number);
CS_KB_ELEMENTS_PKG.Delete_Row(x_element_number => p_element_number);
END Delete_Element;
SELECT 'X'
FROM cs_kb_element_types_b
WHERE element_type_id = p_element_type_id
AND trunc(sysdate) BETWEEN trunc(nvl(start_date_active, sysdate))
AND trunc(nvl(end_date_active, sysdate));
SELECT COUNT(*) INTO l_count
FROM CS_KB_ELEMENT_TYPES_B
WHERE element_type_id = p_element_type_id;
SELECT CS_KB_ELEMENTS_S.NEXTVAL INTO l_element_id FROM DUAL;
SELECT TO_CHAR(CS_KB_ELEMENT_NUMBER_S.NEXTVAL) INTO l_element_number FROM DUAL;
SELECT COUNT(element_number) INTO l_count
FROM CS_KB_ELEMENTS_B
WHERE element_number = l_element_number;
SELECT TO_CHAR(CS_KB_ELEMENT_NUMBER_S.NEXTVAL) INTO l_element_number FROM DUAL;
CS_KB_ELEMENTS_PKG.Insert_Row(
x_rowid => l_rowid,
x_element_id => x_element_id,
x_element_number => l_element_number,
x_element_type_id => p_element_type_id,
x_element_name => NULL,
x_group_flag => NULL,
x_status => l_status,
x_access_level => p_access_level,
x_name => p_name,
x_description => p_desc,
x_creation_date => l_date,
x_created_by => l_created_by,
x_last_update_date => l_date,
x_last_updated_by => l_created_by,
x_last_update_login => l_login,
x_locked_by => NULL,
x_lock_date => NULL,
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,
x_start_active_date => p_start_active_date,
x_end_active_date => p_end_active_date,
x_content_type => p_content_type );
PROCEDURE Update_Statement(
P_ELEMENT_ID IN NUMBER,
P_ELEMENT_NUMBER IN VARCHAR2,
P_ELEMENT_TYPE_ID IN NUMBER,
P_DESC IN CLOB,
P_NAME IN VARCHAR2,
P_STATUS IN VARCHAR2,
P_ACCESS_LEVEL IN NUMBER,
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,
P_START_ACTIVE_DATE IN DATE,
P_END_ACTIVE_DATE IN DATE,
P_CONTENT_TYPE IN VARCHAR2,
X_RETURN OUT NOCOPY NUMBER,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_DATA OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER )
IS
l_date DATE;
l_updated_by NUMBER;
SELECT 'X'
FROM cs_kb_element_types_b
WHERE element_type_id = p_element_type_id
AND trunc(sysdate) BETWEEN trunc(nvl(start_date_active, sysdate))
AND trunc(nvl(end_date_active, sysdate));
SELECT 'x'
FROM CS_KB_ELEMENTS_B
WHERE element_id = p_element_id
AND element_type_id = p_element_type_id;
SELECT COUNT(*) INTO l_count
FROM CS_KB_ELEMENT_TYPES_B
WHERE element_type_id = p_element_type_id;
get_who(l_date, l_updated_by, l_login);
CS_KB_ELEMENTS_PKG.Update_Row(
x_element_id => p_element_id,
x_element_number => p_element_number,
x_element_type_id => p_element_type_id,
x_element_name => NULL,
x_group_flag => NULL,
x_status => p_status,
x_access_level => p_access_level,
x_name => p_name,
x_description => p_desc,
x_last_update_date => l_date,
x_last_updated_by => l_updated_by,
x_last_update_login => l_login,
x_locked_by => null,
x_lock_date => null,
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,
x_start_active_date => p_start_active_date,
x_end_active_date => p_end_active_date,
x_content_type => p_content_type );
UPDATE cs_kb_sets_b
SET last_update_date = l_date,
last_updated_by = l_updated_by,
last_update_login = l_login
WHERE set_id IN (SELECT set_id
FROM CS_KB_SET_ELES
WHERE element_id = p_element_id);
FND_MESSAGE.set_name('CS', 'CS_KB_C_UPDATE_ERR');
END Update_Statement;
PROCEDURE Update_Statement_Admin (
P_ELEMENT_ID IN NUMBER,
P_ELEMENT_NUMBER IN VARCHAR2,
P_ACCESS_LEVEL IN NUMBER,
P_ELEMENT_TYPE_ID IN NUMBER,
P_ELEMENT_NAME IN VARCHAR2,
P_ELEMENT_DESC IN CLOB,
P_CONTENT_TYPE IN VARCHAR2,
X_RETURN_ELEMENT OUT NOCOPY NUMBER,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_DATA OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER ) IS
CURSOR Get_Other_Stmt_Attributes IS
SELECT status,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
FROM CS_KB_ELEMENTS_B
WHERE Element_id = P_ELEMENT_ID;
SELECT distinct s.Set_id, s.Set_Number
FROM CS_KB_SETS_B s,
CS_KB_SET_ELES e,
CS_KB_SET_CATEGORIES c,
CS_KB_CAT_GROUP_DENORM d
WHERE s.Set_id = e.Set_Id
AND e.Element_id = P_ELEMENT_ID
AND s.Set_id = c.Set_id
AND c.Category_id = d.Child_Category_id
AND d.Category_Group_Id = CS_KB_SECURITY_PVT.Get_Category_Group_Id
AND (s.Latest_Version_Flag = 'Y' OR s.Viewable_Version_Flag = 'Y');
SELECT distinct s.Set_id, s.Set_Number
FROM CS_KB_SETS_B s,
CS_KB_SET_ELES e,
CS_KB_SET_CATEGORIES c,
CS_KB_CAT_GROUP_DENORM d
WHERE s.Set_id = e.Set_Id
AND e.Element_id = P_ELEMENT_ID
AND s.Set_id = c.Set_id
AND c.Category_id = d.Child_Category_id
AND d.Category_Group_Id = CS_KB_SECURITY_PVT.Get_Category_Group_Id
AND (s.Latest_Version_Flag = 'Y' OR s.Viewable_Version_Flag = 'Y')
AND NOT EXISTS (SELECT 'x'
FROM CS_KB_SET_ELES se
WHERE se.set_id = e.set_id
AND se.element_id = v_dup_id);
SELECT distinct s.Set_id, s.Set_Number
FROM CS_KB_SETS_B s,
CS_KB_SET_ELES e,
CS_KB_SET_CATEGORIES c,
CS_KB_CAT_GROUP_DENORM d
WHERE s.Set_id = e.Set_Id
AND e.Element_id = P_ELEMENT_ID
AND s.Set_id = c.Set_id
AND c.Category_id = d.Child_Category_id
AND d.Category_Group_Id = CS_KB_SECURITY_PVT.Get_Category_Group_Id
AND (s.Latest_Version_Flag = 'Y' OR s.Viewable_Version_Flag = 'Y')
AND EXISTS (SELECT 'x'
FROM CS_KB_SET_ELES se
WHERE se.set_id = e.set_id
AND se.element_id = v_dup_id);
SELECT Element_Number
FROM CS_KB_ELEMENTS_B
WHERE Element_id = P_ELEMENT_ID;
SELECT count(distinct s.Set_Number)
FROM CS_KB_SETS_VL s,
CS_KB_SET_ELES e
WHERE s.Set_id = e.Set_Id
AND e.Element_id = P_ELEMENT_ID
AND s.Latest_Version_Flag = 'Y'
AND s.Status <> 'OBS'
AND s.Set_Id IN (SELECT setb.Set_Id
FROM CS_KB_SETS_B setb,
CS_KB_SET_ELES setele,
CS_KB_SET_CATEGORIES setcat,
CS_KB_CAT_GROUP_DENORM denorm
WHERE setb.set_id = setele.Set_Id
AND setele.Element_id = e.Element_id
AND setb.Latest_Version_Flag = 'Y'
AND setb.Status <> 'OBS'
AND setb.Set_id = setcat.Set_id
AND setcat.Category_id = denorm.Child_Category_id
AND denorm.Category_Group_Id <> CS_KB_SECURITY_PVT.Get_Category_Group_Id
AND NOT EXISTS (SELECT 'x'
FROM CS_KB_SETS_B setb2,
CS_KB_SET_CATEGORIES setcat2,
CS_KB_CAT_GROUP_DENORM denorm2
WHERE setb2.set_id = setb.Set_Id
AND setb2.Latest_Version_Flag = 'Y'
AND setb2.Status <> 'OBS'
AND setb2.Set_id = setcat2.Set_id
AND setcat2.Category_id = denorm2.Child_Category_id
AND denorm2.Category_Group_Id =
CS_KB_SECURITY_PVT.Get_Category_Group_Id )
);
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'csk.plsql.CS_KB_ELEMENTS_AUDIT_PVT.Update_Statement_Admin.Start',
'Started Global Statement Update');
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'csk.plsql.CS_KB_ELEMENTS_AUDIT_PVT.Update_Statement_Admin',
'A Duplicate Statement Exists for this update - UPD with Current CG');
UPDATE CS_KB_SET_ELES se
SET se.ELEMENT_ID = l_dup_id,
se.LAST_UPDATE_DATE = sysdate,
se.LAST_UPDATED_BY = FND_GLOBAL.user_id,
se.LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
WHERE se.Element_Id = P_ELEMENT_ID
AND se.Set_Id = solns.Set_Id;
DELETE FROM CS_KB_SET_ELES se
WHERE se.Element_Id = P_ELEMENT_ID
AND se.Set_Id = remstmts.Set_Id;
ELSE -- The Updated Statement is not a Duplicate
/* -- Need to check Soln Usage !!!
The following Api returns a count of the Number of Solutions that use
the statement, where that Solution only exists in Category Groups
outside of the current user Category Group
-- If the Statement only resides on Solutions within the current CG then
update the statement directly.
-- If the Statement is used on Solutions outside of the current CG only, then these
solutions should remain unchanged
i.e. within the current CG a new Statement will be created and assoicated to the
current CG solutions.
Solutions outside of the current CG will continue to point to the original statement.
-- If a Solution resides in multiple CG's then the statement will be updated directly
and therefore the changed statement will be seen across CG's
*/
OPEN Stmt_In_Soln_Outside_Cur_CG;
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'csk.plsql.CS_KB_ELEMENTS_AUDIT_PVT.Update_Statement_Admin',
'Statement only used in current Category Group - Update Statement');
Update_Statement( p_element_id => P_ELEMENT_ID,
p_element_number => P_ELEMENT_NUMBER,
p_element_type_id => P_ELEMENT_TYPE_ID,
p_desc => P_ELEMENT_DESC,
p_name => P_ELEMENT_NAME,
p_status => l_status,
p_access_level => P_ACCESS_LEVEL,
p_attribute_category => l_attribute_category,
p_attribute1 => l_attribute1,
p_attribute2 => l_attribute2,
p_attribute3 => l_attribute3,
p_attribute4 => l_attribute4,
p_attribute5 => l_attribute5,
p_attribute6 => l_attribute6,
p_attribute7 => l_attribute7,
p_attribute8 => l_attribute8,
p_attribute9 => l_attribute9,
p_attribute10 => l_attribute10,
p_attribute11 => l_attribute11,
p_attribute12 => l_attribute12,
p_attribute13 => l_attribute13,
p_attribute14 => l_attribute14,
p_attribute15 => l_attribute15,
p_start_active_date => null,
p_end_active_date => null,
p_content_type => P_CONTENT_TYPE, --l_content_type,
x_return => l_return,
x_return_status => l_return_status,
x_msg_data => l_msg_data,
x_msg_count => l_msg_count);
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'csk.plsql.CS_KB_ELEMENTS_AUDIT_PVT.Update_Statement_Admin',
'Statement is shared across multiple Category Groups - Create New Stmt');
UPDATE CS_KB_SET_ELES se
SET se.ELEMENT_ID = l_new_element_id,
se.LAST_UPDATE_DATE = sysdate,
se.LAST_UPDATED_BY = FND_GLOBAL.user_id,
se.LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
WHERE se.Element_Id = P_ELEMENT_ID
AND se.Set_Id = solns.Set_Id;
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'csk.plsql.CS_KB_ELEMENTS_AUDIT_PVT.Update_Statement_Admin',
'Stmt is dup of the original - NO new Stmt created.');
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'csk.plsql.CS_KB_ELEMENTS_AUDIT_PVT.Update_Statement_Admin.End',
'Finished Global Statement Update - '||X_RETURN_STATUS||'-'||X_RETURN_ELEMENT );
FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, 'csk.plsql.CS_KB_ELEMENTS_AUDIT_PVT.Update_Statement_Admin.UNEX',
'Unexpected Exception-'||substrb(sqlerrm,1,200) );
FND_MESSAGE.set_name('CS', 'CS_KB_C_UPDATE_ERR');
END Update_Statement_Admin;
SELECT E.element_id
FROM CS_KB_ELEMENTS_B E
WHERE E.status <> 'OBS'
AND NOT EXISTS (SELECT 'x'
FROM CS_KB_SETS_B S,
CS_KB_SET_ELES SE
WHERE SE.Set_Id = S.Set_Id
AND SE.Element_Id = E.Element_Id
AND S.Status <> 'OBS'
AND (S.Latest_Version_Flag = 'Y' OR S.Viewable_Version_Flag = 'Y')
);
UPDATE CS_KB_ELEMENTS_B
SET Status = 'OBS',
Last_Update_Date = sysdate,
Last_Updated_By = l_user,
Last_Update_Login = l_login
WHERE Element_Id = Statements.Element_id;
UPDATE CS_KB_ELEMENTS_TL
SET Composite_Text_Index = 'x',
Last_Update_Date = sysdate,
Last_Updated_By = l_user,
Last_Update_Login = l_login
WHERE Element_Id = Statements.Element_id;