[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Delete_Item
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
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_item_id IN NUMBER
) AS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Item';
SAVEPOINT Delete_Item_Pub;
amv_perspective_pvt.Delete_ItemPersps
(
p_api_version => p_api_version,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_check_login_user => FND_API.G_FALSE,
p_item_id => p_item_id
);
DELETE FROM amv_c_chl_item_match
WHERE item_id = p_item_id
AND table_name_code = G_USED_BY_ITEM;
DELETE FROM amv_u_access
WHERE access_to_table_record_id = p_item_id
AND access_to_table_code = G_USED_BY_ITEM;
AMV_DistRule_Pvt.Delete_ItemFromDistRules
(
p_api_version => p_api_version,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_item_id => p_item_id
);
JTF_AMV_ITEM_PUB.Delete_Item
(
p_api_version => p_api_version,
p_init_msg_list => FND_API.G_FALSE,
p_commit => p_commit,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_item_id => p_item_id
);
ROLLBACK TO Delete_Item_Pub;
ROLLBACK TO Delete_Item_Pub;
ROLLBACK TO Delete_Item_Pub;
END Delete_Item;
PROCEDURE Update_Item
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
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_channel_id_array IN AMV_NUMBER_VARRAY_TYPE := NULL,
p_item_obj IN AMV_ITEM_OBJ_TYPE,
p_file_array IN AMV_NUMBER_VARRAY_TYPE,
p_persp_array IN AMV_NAMEID_VARRAY_TYPE,
p_author_array IN AMV_CHAR_VARRAY_TYPE,
p_keyword_array IN AMV_CHAR_VARRAY_TYPE
) AS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Item';
SELECT channel_id
FROM amv_c_chl_item_match
WHERE item_id = p_item_obj.item_id
AND available_due_to_type = AMV_UTILITY_PVT.G_PUSH;
SAVEPOINT Update_Item_Pub;
JTF_AMV_ITEM_PUB.Update_Item
(
p_api_version => p_api_version,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_item_rec => l_item_rec
);
amv_perspective_pvt.Update_ItemPersps
(
p_api_version => p_api_version,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_check_login_user => FND_API.G_FALSE,
p_item_id => p_item_obj.item_id,
p_perspective_array => l_persp_id_array
);
-- build channels list to delete
FOR i in 1..l_channel_id_array.count LOOP
IF l_channel_id_array(i).name is null THEN
l_channel_remove_id.extend;
-- delete removed channels
FOR i IN 1..l_channel_remove_id.COUNT LOOP
IF (AMV_UTILITY_PVT.Is_ChannelIdValid(l_channel_remove_id(i))=TRUE)
THEN
-- remove the channel from content item
AMV_MATCH_PVT.Remove_ItemChannelMatch
(
p_api_version => l_api_version,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_check_login_user => FND_API.G_FALSE,
p_channel_id => l_channel_remove_id(i),
p_item_id => p_item_obj.item_id,
p_table_name_code => G_USED_BY_ITEM
);
ROLLBACK TO Update_Item_Pub;
ROLLBACK TO Update_Item_Pub;
ROLLBACK TO Update_Item_Pub;
END Update_Item;
x_item_obj.last_update_date := l_item_rec.LAST_UPDATE_DATE;
x_item_obj.last_updated_by := l_item_rec.LAST_UPDATED_BY;
x_item_obj.last_update_login := l_item_rec.LAST_UPDATE_LOGIN;
l_last_update_date DATE;
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
'Select ' ||
'ITEM_ID, ' ||
'OBJECT_VERSION_NUMBER, ' ||
'CREATION_DATE, ' ||
'CREATED_BY, ' ||
'LAST_UPDATE_DATE, ' ||
'LAST_UPDATED_BY, ' ||
'LAST_UPDATE_LOGIN, ' ||
'APPLICATION_ID, ' ||
'EXTERNAL_ACCESS_FLAG, ' ||
'ITEM_NAME, ' ||
'DESCRIPTION, ' ||
'TEXT_STRING, ' ||
'LANGUAGE_CODE, ' ||
'STATUS_CODE, ' ||
'EFFECTIVE_START_DATE, ' ||
'EXPIRATION_DATE, ' ||
'ITEM_TYPE, ' ||
'URL_STRING, ' ||
'PUBLICATION_DATE, ' ||
'PRIORITY, ' ||
'CONTENT_TYPE_ID, ' ||
'OWNER_ID, ' ||
'DEFAULT_APPROVER_ID, ' ||
'ITEM_DESTINATION_TYPE ' ||
'From JTF_AMV_ITEMS_VL';
'Select count(*) ' ||
'From JTF_AMV_ITEMS_VL';
l_last_update_date,
l_last_updated_by,
l_last_update_login,
l_application_id,
l_external_access,
l_item_name,
l_description,
l_text_string,
l_language_code,
l_status_code,
l_effective_start_date,
l_expiration_date,
l_item_type,
l_url_string,
l_publication_date,
l_priority,
l_content_type_id,
l_owner_id,
l_default_approver_id,
l_item_destination_type;
x_item_obj_array(l_fetch_count).last_update_date := l_LAST_UPDATE_DATE;
x_item_obj_array(l_fetch_count).last_updated_by := l_LAST_UPDATED_BY;
x_item_obj_array(l_fetch_count).last_update_login := l_LAST_UPDATE_LOGIN;
PROCEDURE Delete_ItemKeyword
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
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_item_id IN NUMBER,
p_keyword_varray IN AMV_CHAR_VARRAY_TYPE
) AS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_ItemKeyword';
SAVEPOINT Delete_ItemKeyword_Pub;
JTF_AMV_ITEM_PUB.Delete_ItemKeyword
(
p_api_version => p_api_version,
p_init_msg_list => FND_API.G_FALSE,
p_commit => p_commit,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_item_id => p_item_id,
p_keyword_tab => l_char_tab
);
ROLLBACK TO Delete_ItemKeyword_Pub;
ROLLBACK TO Delete_ItemKeyword_Pub;
ROLLBACK TO Delete_ItemKeyword_Pub;
END Delete_ItemKeyword;
PROCEDURE Delete_ItemKeyword
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
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_item_id IN NUMBER,
p_keyword IN VARCHAR2
) AS
l_char_varray AMV_CHAR_VARRAY_TYPE;
Delete_ItemKeyword
(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_check_login_user => p_check_login_user,
p_item_id => p_item_id,
p_keyword_varray => l_char_varray
);
END Delete_ItemKeyword;
JTF_AMV_ITEM_PUB.Delete_ItemKeyword
(
p_api_version => p_api_version,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_item_id => p_item_id,
p_keyword_tab => NULL
);
PROCEDURE Delete_ItemAuthor
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
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_item_id IN NUMBER,
p_author_varray IN AMV_CHAR_VARRAY_TYPE
) AS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_ItemAuthor';
SAVEPOINT Delete_ItemAuthor_Pub;
JTF_AMV_ITEM_PUB.Delete_ItemAuthor
(
p_api_version => p_api_version,
p_init_msg_list => FND_API.G_FALSE,
p_commit => p_commit,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_item_id => p_item_id,
p_author_tab => l_char_tab
);
ROLLBACK TO Delete_ItemAuthor_Pub;
ROLLBACK TO Delete_ItemAuthor_Pub;
ROLLBACK TO Delete_ItemAuthor_Pub;
END Delete_ItemAuthor;
PROCEDURE Delete_ItemAuthor
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
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_item_id IN NUMBER,
p_author IN VARCHAR2
) AS
l_char_varray AMV_CHAR_VARRAY_TYPE;
Delete_ItemAuthor
(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_check_login_user => p_check_login_user,
p_item_id => p_item_id,
p_author_varray => l_char_varray
);
END Delete_ItemAuthor;
JTF_AMV_ITEM_PUB.Delete_ItemAuthor
(
p_api_version => p_api_version,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_item_id => p_item_id,
p_author_tab => NULL
);
SELECT
NVL(language, USERENV('LANG'))
FROM fnd_lobs
WHERE file_id = p_file_id
--And PROGRAM_NAME = 'MES'
--And PROGRAM_TAG = 'MES'
;
SELECT
file_id
FROM jtf_amv_attachments_v
WHERE file_id = p_file_id
AND attachment_used_by_id = p_item_id
AND attachment_used_by = G_USED_BY_ITEM;
PROCEDURE Delete_ItemFile
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
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_item_id IN NUMBER,
p_file_id_varray IN AMV_NUMBER_VARRAY_TYPE
) AS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_ItemFile';
SELECT
attachment_id, object_version_number
FROM jtf_amv_attachments_v
WHERE file_id = p_file_id
AND attachment_used_by_id = p_item_id
AND attachment_used_by = G_USED_BY_ITEM;
SAVEPOINT Delete_ItemFile_Pub;
jtf_amv_attachment_pub.delete_act_attachment
(
p_api_version => p_api_version,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_act_attachment_id => l_temp_number,
p_object_version => l_object_version_number
);
DELETE FROM jtf_amv_attachments
WHERE attachment_used_by_id = p_item_id
AND attachment_used_by = G_USED_BY_ITEM;
ROLLBACK TO Delete_ItemFile_Pub;
ROLLBACK TO Delete_ItemFile_Pub;
ROLLBACK TO Delete_ItemFile_Pub;
END Delete_ItemFile;
PROCEDURE Delete_ItemFile
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
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_item_id IN NUMBER,
p_file_id IN NUMBER
) AS
l_number_varray AMV_NUMBER_VARRAY_TYPE;
Delete_ItemFile
(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_check_login_user => p_check_login_user,
p_item_id => p_item_id,
p_file_id_varray => l_number_varray
);
END Delete_ItemFile;
Delete_ItemFile
(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_check_login_user => p_check_login_user,
p_item_id => p_item_id,
p_file_id_varray => NULL
);
SELECT
File_id
FROM jtf_amv_attachments_v
WHERE attachment_used_by_id = p_item_id
AND attachment_used_by = G_USED_BY_ITEM;
SELECT
item.item_id,
item.item_name
FROM jtf_amv_items_vl item, amv_c_chl_item_match match,
amv_u_my_channels mych
WHERE item.item_type = 'MESSAGE_ITEM'
AND item.item_id = match.item_id
AND match.table_name_code = 'ITEM'
AND match.approval_status_type = 'APPROVED'
AND match.channel_id = mych.subscribing_to_id
AND mych.subscribing_to_type = 'CHANNEL'
AND mych.subscription_reason_type = 'ENFORCED'
AND mych.user_or_group_type = 'USER'
AND mych.user_or_group_id = p_user_id
UNION
SELECT
item.item_id,
item.item_name
FROM jtf_amv_items_vl item, amv_c_chl_item_match match,
amv_u_my_channels mych, jtf_rs_group_members mem,
jtf_rs_groups_vl g
WHERE item.item_type = 'MESSAGE_ITEM'
AND item.item_id = match.item_id
AND match.table_name_code = 'ITEM'
AND match.approval_status_type = 'APPROVED'
AND match.channel_id = mych.subscribing_to_id
AND mych.user_or_group_type = 'GROUP'
AND mych.subscribing_to_type = 'CHANNEL'
AND mych.subscription_reason_type = 'ENFORCED'
AND mych.user_or_group_id = mem.group_id
AND mem.delete_flag <> 'Y'
AND mem.resource_id = p_user_id
AND mem.group_id = g.group_id
AND g.start_date_active <= SYSDATE
AND NVL(g.end_date_active, SYSDATE+1) > SYSDATE
;
SELECT
item.item_id,
item.object_version_number,
item.creation_date,
item.created_by,
item.last_update_date,
item.last_updated_by,
item.last_update_login,
item.application_id,
item.external_access_flag,
item.item_name,
item.description,
item.text_string,
item.language_code,
item.status_code,
item.effective_start_date,
item.expiration_date,
item.item_type,
item.url_string,
item.publication_date,
item.priority,
item.content_type_id,
item.owner_id,
item.default_approver_id,
item.item_destination_type
FROM jtf_amv_items_vl item, amv_c_chl_item_match match,
amv_u_my_channels mych
WHERE item.item_type = 'MESSAGE_ITEM'
AND item.item_id = match.item_id
AND match.table_name_code = 'ITEM'
AND match.approval_status_type = 'APPROVED'
AND match.channel_id = mych.subscribing_to_id
AND mych.subscribing_to_type = 'CHANNEL'
AND mych.subscription_reason_type = 'ENFORCED'
AND mych.user_or_group_type = 'USER'
AND mych.user_or_group_id = p_user_id
UNION
SELECT
item.item_id,
item.object_version_number,
item.creation_date,
item.created_by,
item.last_update_date,
item.last_updated_by,
item.last_update_login,
item.application_id,
item.external_access_flag,
item.item_name,
item.description,
item.text_string,
item.language_code,
item.status_code,
item.effective_start_date,
item.expiration_date,
item.item_type,
item.url_string,
item.publication_date,
item.priority,
item.content_type_id,
item.owner_id,
item.default_approver_id,
item.item_destination_type
FROM jtf_amv_items_vl item, amv_c_chl_item_match match,
amv_u_my_channels mych, jtf_rs_group_members mem,
jtf_rs_groups_vl g
WHERE item.item_type = 'MESSAGE_ITEM'
AND item.item_id = match.item_id
AND match.table_name_code = 'ITEM'
AND match.approval_status_type = 'APPROVED'
AND match.channel_id = mych.subscribing_to_id
AND mych.user_or_group_type = 'GROUP'
AND mych.subscribing_to_type = 'CHANNEL'
AND mych.subscription_reason_type = 'ENFORCED'
AND mych.user_or_group_id = mem.group_id
AND mem.delete_flag <> 'Y'
AND mem.resource_id = p_user_id
AND mem.group_id = g.group_id
AND g.start_date_active <= SYSDATE
AND NVL(g.end_date_active, SYSDATE+1) > SYSDATE
;
x_item_varray(l_count).last_update_date := cur.LAST_UPDATE_DATE;
x_item_varray(l_count).last_updated_by := cur.LAST_UPDATED_BY;
x_item_varray(l_count).last_update_login := cur.LAST_UPDATE_LOGIN;
select c.channel_id
, c.channel_name
from amv_c_channels_vl c
, amv_c_chl_item_match m
where m.item_id = p_item_id
and m.channel_id = c.channel_id
and c.channel_type = amv_utility_pvt.g_content
and c.access_level_type = amv_utility_pvt.g_public
and m.approval_status_type = AMV_UTILITY_PVT.G_APPROVED
and m.table_name_code = amv_utility_pvt.g_table_name_code
and decode(p_match_type, FND_API.G_MISS_CHAR, p_match_type, m.available_due_to_type) = p_match_type;