[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select b.channel_category_id
from amv_c_categories_b b, amv_c_categories_tl tl
where tl.channel_category_name = p_category_name
and tl.language = userenv('lang')
and tl.channel_category_id = b.channel_category_id
and b.parent_channel_category_id is null;
select b.channel_category_id
from amv_c_categories_b b, amv_c_categories_tl tl
where tl.channel_category_name = p_category_name
and tl.language = userenv('lang')
and tl.channel_category_id = b.channel_category_id
and b.parent_channel_category_id = p_parent_category_id;
select channel_category_name
from amv_c_categories_tl
where channel_category_id = p_category_id
and language = userenv('lang');
select A.channel_category_id, channel_category_name
from amv_c_categories_b A, amv_c_categories_tl B
where parent_channel_category_id = l_cat_id
and A.channel_category_id = B.channel_category_id
and B.language = USERENV('LANG')
order by channel_category_name;
select parent_channel_category_id
from amv_c_categories_b
where channel_category_id = l_cat_id;
SELECT amv_c_categories_b_s.nextval
FROM dual;
SELECT NVL(MAX(channel_category_order) + 1, 1)
FROM amv_c_categories_b
WHERE parent_channel_category_id is null
and application_id = p_application_id;
SELECT NVL(MAX(channel_category_order) + 1, 1)
FROM amv_c_categories_b
WHERE parent_channel_category_id = l_parent_category_id;
AMV_C_CATEGORIES_PKG.INSERT_ROW(
X_ROWID => l_row_id,
X_CHANNEL_CATEGORY_ID => l_category_id ,
X_APPLICATION_ID => p_application_id,
X_OBJECT_VERSION_NUMBER => l_object_version_number,
X_CHANNEL_CATEGORY_ORDER => l_order,
X_PARENT_CHANNEL_CATEGORY_ID => l_parent_category_id,
X_CHANNEL_COUNT => 0,
X_CHANNEL_CATEGORY_NAME => p_category_name,
X_DESCRIPTION => p_description,
X_CREATION_DATE => sysdate,
X_CREATED_BY => l_user_id,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => l_user_id,
X_LAST_UPDATE_LOGIN => l_login_user_id
);
PROCEDURE Delete_Category
( 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_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
p_application_id IN NUMBER := AMV_UTILITY_PVT.G_AMV_APP_ID,
p_category_id IN NUMBER := FND_API.G_MISS_NUM,
p_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_parent_category_id IN NUMBER := FND_API.G_MISS_NUM,
p_parent_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Category';
l_delete_category_flag varchar2(1);
select channel_id
from amv_c_channels_b
where channel_category_id = l_category_id;
SAVEPOINT Delete_Category_PVT;
l_delete_category_flag := FND_API.G_TRUE;
IF (AMV_UTILITY_PVT.Get_DeleteCategoryStatus(
l_category_id,
l_resource_id,
AMV_UTILITY_PVT.G_USER) )
THEN
l_delete_category_flag := FND_API.G_TRUE;
IF l_delete_category_flag = FND_API.G_TRUE THEN
l_category_hr := amv_cat_hierarchy_varray_type();
DELETE FROM amv_u_my_channels
WHERE subscribing_to_id = l_channel_id
AND subscribing_to_type = AMV_UTILITY_PVT.G_CHANNEL;
DELETE FROM amv_u_access
WHERE access_to_table_code = AMV_UTILITY_PVT.G_CHANNEL
AND access_to_table_record_id = l_channel_id;
DELETE FROM amv_c_authors
WHERE channel_id = l_channel_id;
DELETE FROM amv_c_keywords
WHERE channel_id = l_channel_id;
DELETE FROM amv_c_content_types
WHERE channel_id = l_channel_id;
DELETE FROM amv_c_chl_perspectives
WHERE channel_id = l_channel_id;
DELETE FROM amv_c_item_types
WHERE channel_id = l_channel_id;
AMV_C_CHANNELS_PKG.DELETE_ROW( l_channel_id);
DELETE FROM amv_c_chl_item_match
WHERE channel_category_id = l_category_id;
DELETE FROM amv_u_my_channels
--WHERE subscribing_to_id = l_category_id pls refer the bug# 2626331,2720397
WHERE subscribing_to_id = l_category_hr(i).id
AND subscribing_to_type = AMV_UTILITY_PVT.G_CATEGORY;
AMV_C_CATEGORIES_PKG.DELETE_ROW(l_category_hr(i).id);
ROLLBACK TO Delete_Category_PVT;
ROLLBACK TO Delete_Category_PVT;
ROLLBACK TO Delete_Category_PVT;
END Delete_Category;
SELECT b.channel_category_id
FROM amv_c_categories_b b, amv_c_categories_tl tl
WHERE b.parent_channel_category_id is null
and b.application_id = p_application_id
ORDER BY tl.channel_category_name;
SELECT b.channel_category_id
FROM amv_c_categories_b b, amv_c_categories_tl tl
WHERE b.parent_channel_category_id = l_parent_category_id
ORDER BY tl.channel_category_name;
SELECT application_id
, channel_count
FROM amv_c_categories_b
WHERE channel_category_id = l_category_id;
AMV_C_CATEGORIES_PKG.UPDATE_B_ROW(
X_CHANNEL_CATEGORY_ID => l_category_id,
X_APPLICATION_ID => l_application_id,
X_OBJECT_VERSION_NUMBER => l_object_version_number,
X_CHANNEL_CATEGORY_ORDER => l_order,
X_PARENT_CHANNEL_CATEGORY_ID => l_parent_category_id,
X_CHANNEL_COUNT => l_channel_count,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => l_user_id,
X_LAST_UPDATE_LOGIN => l_login_user_id
);
select application_id,
parent_channel_category_id,
channel_count
from amv_c_categories_b
where channel_category_id = l_category_id;
AMV_C_CATEGORIES_PKG.UPDATE_B_ROW(
X_CHANNEL_CATEGORY_ID => l_category_id,
X_APPLICATION_ID => l_application_id,
X_OBJECT_VERSION_NUMBER => l_object_version_number,
X_CHANNEL_CATEGORY_ORDER => p_category_new_order(i),
X_PARENT_CHANNEL_CATEGORY_ID => l_parent_category_id,
X_CHANNEL_COUNT => l_channel_count,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => l_user_id,
X_LAST_UPDATE_LOGIN => l_login_user_id
);
PROCEDURE Update_Category
( 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_check_login_user IN VARCHAR2 := FND_API.G_TRUE,
p_object_version_number IN NUMBER,
p_application_id IN NUMBER := AMV_UTILITY_PVT.G_AMV_APP_ID,
p_category_id IN NUMBER := FND_API.G_MISS_NUM,
p_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_parent_category_id IN NUMBER := FND_API.G_MISS_NUM,
p_parent_category_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_category_order IN NUMBER := FND_API.G_MISS_NUM,
p_category_new_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_description IN VARCHAR2 := FND_API.G_MISS_CHAR
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Category';
l_update_category_flag varchar2(1);
select object_version_number,
application_id,
channel_category_order,
parent_channel_category_id,
channel_count
from amv_c_categories_b
where channel_category_id = l_category_id;
select description
from amv_c_categories_tl
where channel_category_id = l_category_id
and language = userenv('lang');
SAVEPOINT Update_Category_PVT;
l_update_category_flag := FND_API.G_TRUE;
IF (AMV_UTILITY_PVT.Get_UpdateCategoryStatus(
l_category_id,
l_resource_id,
AMV_UTILITY_PVT.G_USER) )
THEN
l_update_category_flag := FND_API.G_TRUE;
IF l_update_category_flag = FND_API.G_TRUE THEN
-- get the category record in database
OPEN Get_CatRec_csr;
AMV_C_CATEGORIES_PKG.UPDATE_ROW(
X_CHANNEL_CATEGORY_ID => l_category_id,
X_APPLICATION_ID => l_application_id,
X_OBJECT_VERSION_NUMBER => p_object_version_number + 1,
X_CHANNEL_CATEGORY_ORDER => l_category_order,
X_PARENT_CHANNEL_CATEGORY_ID => l_parent_category_id,
X_CHANNEL_COUNT => l_channel_count,
X_CHANNEL_CATEGORY_NAME => l_category_current_name,
X_DESCRIPTION => l_description,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => l_user_id,
X_LAST_UPDATE_LOGIN => l_login_user_id
);
ROLLBACK TO Update_Category_PVT;
ROLLBACK TO Update_Category_PVT;
ROLLBACK TO Update_Category_PVT;
END Update_Category;
select channel_category_id,
object_version_number,
parent_channel_category_id,
channel_category_order,
nvl(channel_count,0),
channel_category_name,
description
from amv_c_categories_vl
where channel_category_name like p_category_name
and channel_category_name not in ('AMV_GROUP', 'AMV_PRIVATE')
and application_id = p_application_id
order by channel_category_name;
select channel_category_id,
object_version_number,
parent_channel_category_id,
channel_category_order,
nvl(channel_count,0),
channel_category_name,
description
from amv_c_categories_vl
where channel_category_name like p_category_name
and channel_category_name not in ('AMV_GROUP', 'AMV_PRIVATE')
and application_id = p_application_id
and parent_channel_category_id is null
order by channel_category_order;
select channel_category_id,
object_version_number,
parent_channel_category_id,
channel_category_order,
nvl(channel_count,0),
channel_category_name,
description
from amv_c_categories_vl
where channel_category_name like p_category_name
and channel_category_name not in ('AMV_GROUP', 'AMV_PRIVATE')
and application_id = p_application_id
and parent_channel_category_id = l_parent_category_id
order by channel_category_order;
select count(channel_category_id)
from amv_c_categories_vl
where channel_category_name like p_category_name
and channel_category_name not in ('AMV_GROUP', 'AMV_PRIVATE')
and application_id = p_application_id;
select count(channel_category_id)
from amv_c_categories_vl
where channel_category_name like p_category_name
and channel_category_name not in ('AMV_GROUP', 'AMV_PRIVATE')
and application_id = p_application_id
and parent_channel_category_id is null;
select count(channel_category_id)
from amv_c_categories_vl
where channel_category_name not in ('AMV_GROUP', 'AMV_PRIVATE')
and application_id = p_application_id
and parent_channel_category_id = l_category_id;
select count(channel_id)
from amv_c_channels_b
where channel_category_id = l_category_id;
select b.channel_id
, b.channel_name
from amv_c_channels_vl b
where b.channel_category_id = l_category_id
and b.effective_start_date <= sysdate
and nvl(b.expiration_date, sysdate) >= sysdate
order by b.channel_name;
select ib.item_id
, ib.item_name
from amv_c_chl_item_match cim
, jtf_amv_items_vl ib
where cim.channel_category_id = l_category_id
and cim.channel_id is null
and cim.approval_status_type = AMV_UTILITY_PVT.G_APPROVED
and cim.table_name_code = AMV_UTILITY_PVT.G_TABLE_NAME_CODE
and cim.available_for_channel_date <= sysdate
and cim.item_id = ib.item_id
and nvl(ib.effective_start_date, sysdate) <= sysdate + 1
and nvl(ib.expiration_date, sysdate) >= sysdate
order by ib.effective_start_date;
select ib.item_id
, ib.item_name
from amv_c_chl_item_match cim
, jtf_amv_items_vl ib
where cim.channel_category_id = l_category_id
and cim.channel_id is null
and cim.approval_status_type = AMV_UTILITY_PVT.G_APPROVED
and cim.table_name_code = AMV_UTILITY_PVT.G_TABLE_NAME_CODE
and cim.available_for_channel_date <= sysdate
and cim.item_id = ib.item_id
and nvl(ib.effective_start_date, sysdate) <= sysdate + 1
and nvl(ib.expiration_date, sysdate) >= sysdate
order by l_sort_col ||' '||l_sort_dir;
select count(cim.item_id)
from amv_c_chl_item_match cim
, jtf_amv_items_vl ib
where cim.channel_category_id = l_category_id
and cim.channel_id is null
and cim.approval_status_type = AMV_UTILITY_PVT.G_APPROVED
and cim.table_name_code = AMV_UTILITY_PVT.G_TABLE_NAME_CODE
and cim.available_for_channel_date <= sysdate
and cim.item_id = ib.item_id
and nvl(ib.effective_start_date, sysdate) <= sysdate + 1
and nvl(ib.expiration_date, sysdate) >= sysdate;
select tl.channel_name
, b.channel_category_id
from amv_c_channels_b b
, amv_c_channels_tl tl
where b.channel_id = p_channel_id
and b.channel_id = tl.channel_id
and tl.language = userenv('lang');
l_update_flag varchar2(1) := FND_API.G_FALSE;
select parent_channel_category_id
, channel_category_name
, object_version_number
, application_id
, channel_category_order
, channel_count
from amv_c_categories_vl
where channel_category_id = p_category_id;
select channel_category_name
from amv_c_categories_vl
where parent_channel_category_id = p_parent_category_id;
l_update_flag := FND_API.G_TRUE;
l_update_flag := FND_API.G_TRUE;
l_update_flag := FND_API.G_FALSE;
IF l_update_flag = FND_API.G_TRUE THEN
IF p_object_version_number = l_object_version_number THEN
-- check to see if parent is not its child
l_category_hr := amv_cat_hierarchy_varray_type();
l_update_flag := FND_API.G_FALSE;
IF l_update_flag = FND_API.G_FALSE THEN
IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
FND_MESSAGE.Set_Name('AMV', 'AMV_CAT_PARENT_LOOPING');
l_update_flag := FND_API.G_FALSE;
IF l_update_flag = FND_API.G_FALSE THEN
IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
FND_MESSAGE.Set_Name('AMV', 'AMV_CAT_NAME_EXISTS');
AMV_C_CATEGORIES_PKG.UPDATE_B_ROW(
X_CHANNEL_CATEGORY_ID => p_category_id,
X_APPLICATION_ID => l_application_id,
X_OBJECT_VERSION_NUMBER => p_object_version_number + 1,
X_CHANNEL_CATEGORY_ORDER => l_order,
X_PARENT_CHANNEL_CATEGORY_ID => p_parent_category_id,
X_CHANNEL_COUNT => l_channel_count,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => l_user_id,
X_LAST_UPDATE_LOGIN => l_login_user_id
);
select b.parent_channel_category_id
, b.object_version_number
, b.application_id
, b.channel_category_order
, b.channel_count
from amv_c_categories_b b
where b.channel_category_id = p_category_id;
AMV_C_CATEGORIES_PKG.UPDATE_B_ROW(
X_CHANNEL_CATEGORY_ID => p_category_id,
X_APPLICATION_ID => l_application_id,
X_OBJECT_VERSION_NUMBER => p_object_version_number + 1,
X_CHANNEL_CATEGORY_ORDER => l_order,
X_PARENT_CHANNEL_CATEGORY_ID => l_parent_category_id,
X_CHANNEL_COUNT => l_channel_count,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => l_user_id,
X_LAST_UPDATE_LOGIN => l_login_user_id
);