The following lines contain the word 'select', 'insert', 'update' or 'delete':
|| 'AND item_id NOT IN (SELECT item_id FROM jtf_dsp_tpl_ctg '
|| 'WHERE category_id = :ctg_id) ';
sql_stmt := 'SELECT COUNT(*) ' || sql_cond;
|| 'SELECT item_id, access_name, item_name, deliverable_type_code, '
--modified by G. Zhang 05/17/01 5:42PM
|| 'applicable_to_code, description, keyword, file_name, file_id, '
|| 'object_version_number '
|| 'BULK COLLECT INTO :id_tbl, :acc_tbl, :dsp_tbl, :type_tbl, '
--modified by G. Zhang 05/17/01 5:42PM
|| ':appl_tbl, :desc_tbl, :key_tbl, :file_tbl, :file_id_tbl, :version_tbl '
|| 'FROM (SELECT * '
|| sql_cond
|| 'ORDER BY item_name ) '
|| 'WHERE ROWNUM <= :row_num '
|| '; END;';
l_operation_type VARCHAR2(10) := 'INSERT';
l_item_rec.last_update_date := NULL;
l_item_rec.last_updated_by := NULL;
l_item_rec.last_update_login := NULL;
-- Update an existing deliverable
l_operation_type := 'UPDATE';
IF (l_operation_type = 'INSERT') THEN
l_item_rec.deliverable_type_code
:= TRIM(p_deliverable_rec.item_type);
JTF_AMV_ITEM_PUB.update_item(
p_api_version => g_amv_api_version,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_item_rec => l_item_rec
);
-- Delete existing keywords
JTF_AMV_ITEM_PUB.delete_itemkeyword(
p_api_version => g_amv_api_version,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_item_id => p_deliverable_rec.deliverable_id,
p_keyword_tab => NULL
);
-- update the object_version_number
p_deliverable_rec.object_version_number :=
p_deliverable_rec.object_version_number + 1;
l_operation_type VARCHAR2(10) := 'INSERT';
l_delete_mapping VARCHAR2(1) := FND_API.g_false;
-- Update an existing deliverable
l_operation_type := 'UPDATE';
-- Update/Create default attachment for all-site and all-language
BEGIN
SAVEPOINT save_one_attachment_grp;
IF l_operation_type = 'INSERT' THEN
-- Create an attachment for all-site and all-language if any
IF TRIM(p_dlv_ath_rec.ath_file_name) IS NOT NULL THEN
l_create_attachment := FND_API.g_true;
-- Update the default attachment for the existing deliverable
IF TRIM(p_dlv_ath_rec.ath_file_name) IS NULL THEN
l_delete_mapping := FND_API.g_true;
-- If so, validate it! Update all-site and all-lang mappings
-- if it's the new default
-- If not, delete all-site and all-lang mappings if any
-- and create the new attachment with all-site and all-lang
-- mappings
--modified by G. Zhang 05/23/01 10:57AM
l_attachment_id := JTF_DSPMGRVALIDATION_GRP.check_attachment_exists(
p_dlv_ath_rec.deliverable_id,p_dlv_ath_rec.ath_file_id,p_dlv_ath_rec.ath_file_name);
l_delete_mapping := FND_API.g_true;
l_delete_mapping := FND_API.g_true;
IF l_delete_mapping = FND_API.g_true THEN
-- Delete all-site and all-lang mapping for this deliverable
JTF_PhysicalMap_GRP.delete_dlv_all_all(
p_deliverable_id => p_dlv_ath_rec.deliverable_id
);
PROCEDURE delete_deliverable (
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 VARCHAR2,
x_msg_count OUT NUMBER,
x_msg_data OUT VARCHAR2,
p_dlv_id_ver_tbl IN OUT DLV_ID_VER_TBL_TYPE ) IS
l_api_name CONSTANT VARCHAR2(30) := 'delete_deliverable';
SAVEPOINT delete_deliverable_grp;
SAVEPOINT delete_one_deliverable_grp;
-- Delete from the section tables
JTF_DSP_SECTION_GRP.update_deliverable_to_null(
p_api_version => p_api_version,
p_deliverable_id => p_dlv_id_ver_tbl(l_index).deliverable_id,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
-- Delete all the associated display contexts
JTF_DisplayContext_GRP.delete_deliverable(
p_dlv_id_ver_tbl(l_index).deliverable_id
);
-- Delete all the associated template categories
JTF_TplCategory_GRP.delete_deliverable(
p_dlv_id_ver_tbl(l_index).deliverable_id
);
-- Delete all the associated logical contents
JTF_LogicalContent_GRP.delete_deliverable(
p_dlv_id_ver_tbl(l_index).deliverable_id
);
-- Delete all the associated physical_site_language mappings
JTF_PhysicalMap_GRP.delete_deliverable(
p_dlv_id_ver_tbl(l_index).deliverable_id
);
-- Delete the item
JTF_AMV_ITEM_PUB.delete_item(
p_api_version => g_amv_api_version,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_item_id => p_dlv_id_ver_tbl(l_index).deliverable_id
);
ROLLBACK TO delete_one_deliverable_grp;
ROLLBACK TO delete_one_deliverable_grp;
ROLLBACK TO delete_one_deliverable_grp;
ROLLBACK TO delete_deliverable_grp;
ROLLBACK TO delete_deliverable_grp;
ROLLBACK TO delete_deliverable_grp;
END delete_deliverable;