[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* Insert the category */
cs_kb_soln_categories_pkg.insert_row
(
X_ROWID => l_rowid,
X_CATEGORY_ID => l_category_id,
X_PARENT_CATEGORY_ID => p_parent_category_id,
X_NAME => p_name,
X_DESCRIPTION => p_description,
X_CREATION_DATE => l_current_date,
X_CREATED_BY => l_current_user_id,
X_LAST_UPDATE_DATE => l_current_date,
X_LAST_UPDATED_BY => l_current_user_id,
X_LAST_UPDATE_LOGIN => l_current_login_id,
X_VISIBILITY_ID => p_visibility_id
);
l_delete_status number;
select link_id
from cs_kb_set_links
where object_code = link_obj_code
and other_id = category_id;
select /*+ index(sl) */ count( * ) into n_child_solutions
from cs_kb_set_categories sl, cs_kb_sets_b b
where sl.category_id = p_category_id
and b.set_id = sl.set_id
--and b.status = 'PUB';
select count( * ) into n_subcatgories
from cs_kb_soln_categories_b
where parent_category_id = p_category_id;
FND_MESSAGE.set_name('CS', 'CS_KB_C_CAT_DELETE_FAILED');
delete /*+ index(sl) */ from cs_kb_set_categories sl
where sl.category_id = p_category_id;
l_delete_status :=
cs_kb_set_links_pkg.delete_set_link
(
p_link_id => linkIdRec.link_id
);
cs_kb_soln_categories_pkg.delete_row( p_category_id );
SELECT SolnCategoryEO.CATEGORY_ID
FROM cs_kb_soln_categories_b SolnCategoryEO
start with SolnCategoryEO.CATEGORY_ID = cp_category_id
connect by prior CATEGORY_ID = PARENT_CATEGORY_ID
order by level desc;
SELECT cs_kb_soln_categories_pvt.admin_cat_fullpath_names( cp_category_id, ' > ' )
FROM dual;
FND_MESSAGE.set_name('CS', 'CS_KB_C_CAT_DELETE_CAS_FAILED');
procedure updateCategory
(
p_api_version in number,
p_init_msg_list in varchar2 := FND_API.G_FALSE,
p_commit in varchar2 := FND_API.G_FALSE,
p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
x_return_status OUT NOCOPY varchar2,
x_msg_count OUT NOCOPY number,
x_msg_data OUT NOCOPY varchar2,
p_category_id in number,
p_parent_category_id in number,
p_name in varchar2,
p_description in varchar2
)
is
begin
updateCategory( p_api_version ,
p_init_msg_list ,
p_commit ,
p_validation_level ,
x_return_status ,
x_msg_count ,
x_msg_data ,
p_category_id ,
p_parent_category_id,
p_name ,
p_description ,
3 ); -- default to external visibility, if called from JTT
procedure updateCategory
(
p_api_version in number,
p_init_msg_list in varchar2 := FND_API.G_FALSE,
p_commit in varchar2 := FND_API.G_FALSE,
p_validation_level in number := FND_API.G_VALID_LEVEL_FULL,
x_return_status OUT NOCOPY varchar2,
x_msg_count OUT NOCOPY number,
x_msg_data OUT NOCOPY varchar2,
p_category_id in number,
p_parent_category_id in number,
p_name in varchar2,
p_description in varchar2,
p_visibility_id in number
)
is
l_current_date date;
SELECT Visibility_Id
FROM CS_KB_SOLN_CATEGORIES_B
WHERE Category_Id = p_category_id;
SELECT parent_category_Id
FROM CS_KB_SOLN_CATEGORIES_B
WHERE Category_Id = p_category_id;
SELECT c.category_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;
SELECT Distinct Category_Group_Id
FROM CS_KB_CAT_GROUP_DENORM
WHERE CHILD_CATEGORY_ID = P_PARENT_CATEGORY_ID
intersect
select distinct m.category_group_id
FROM CS_KB_CAT_GROUP_MEMBERS m
WHERE Category_Id = cp_category_id;
savepoint updateCategory_PVT;
/* Update the category */
cs_kb_soln_categories_pkg.update_row
(
X_CATEGORY_ID => p_category_id,
X_PARENT_CATEGORY_ID => p_parent_category_id,
X_NAME => p_name,
X_DESCRIPTION => p_description,
X_LAST_UPDATE_DATE => l_current_date,
X_LAST_UPDATED_BY => l_current_user_id,
X_LAST_UPDATE_LOGIN => l_current_login_id,
X_VISIBILITY_ID => p_visibility_id
);
cs_kb_security_pvt.UPDATE_CATEGORY_TO_DENORM (
P_CATEGORY_ID => p_category_id,
P_VISIBILITY_ID => p_visibility_id,
X_RETURN_STATUS => x_return_status,
X_MSG_DATA => x_msg_data,
X_MSG_COUNT => x_msg_count
);
CS_KB_SECURITY_PVT.delete_category_group_member(
p_category_group_id => y.category_group_id,
p_category_id => x.category_id,
X_RETURN_STATUS => x_return_status,
X_MSG_DATA => x_msg_data,
X_MSG_COUNT => x_msg_count
);
ROLLBACK TO updateCategory_PVT;
FND_MESSAGE.set_name('CS', 'CS_KB_C_DELETE_ERR');
ROLLBACK TO updateCategory_PVT;
end updateCategory;
insert into CS_KB_SET_CATEGORIES (
SET_ID,
CATEGORY_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15
) values (
p_solution_id,
p_category_id,
l_date,
l_user,
l_date,
l_user,
l_login,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null
);
delete from cs_kb_set_categories
where set_id = p_solution_id
and category_id = p_category_id;
select tl.name--, b.category_id, b.lev
from ( SELECT category_id, level lev
FROM cs_kb_soln_categories_b
START WITH category_id = p_cat_id
CONNECT BY prior parent_category_id = category_id
) b, cs_kb_soln_categories_tl tl, cs_kb_cat_group_denorm mv
where
b.category_id = tl.category_id
and tl.language = userenv( 'LANG' )
and tl.category_id = mv.child_category_id
and mv.category_group_id = p_category_group_id -- 2
and mv.visibility_position >= p_soln_visibility_position -- 1000
order by b.lev desc;
select tl.name--, b.category_id, b.lev
from ( SELECT category_id, level lev
FROM cs_kb_soln_categories_b
START WITH category_id = cat_id
CONNECT BY prior parent_category_id = category_id
) b, cs_kb_soln_categories_tl tl
where
b.category_id = tl.category_id
and tl.language = userenv( 'LANG' )
order by b.lev desc;
SELECT category_id
FROM cs_kb_soln_categories_b
START WITH category_id = cat_id
CONNECT BY prior parent_category_id = category_id
order by level desc;
SELECT CATEGORY_ID
FROM cs_kb_soln_categories_b
start with CATEGORY_ID = cp_category_id
connect by prior CATEGORY_ID = PARENT_CATEGORY_ID;
select /*+ index(sl) */ b.set_id
from cs_kb_set_categories sl, cs_kb_sets_b b
where sl.category_id = cp_category_id
and b.set_id = sl.set_id
and (b.status = 'PUB' or (b.status <> 'OBS' and b.latest_version_flag = 'Y'));