[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
sSql := 'SELECT DISTINCT JTFI.INTERACTION_ID FROM JTF_IH_INTERACTIONS JTFI, JTF_IH_ACTIVITIES ACT ';
sSql := 'SELECT INTERACTION_ID FROM JTF_IH_INTERACTIONS JTFI ';
SELECT p_End_Date-p_Start_Date INTO n_Res FROM DUAL;
SELECT TO_NUMBER(REPLACE(REPLACE(p_Party_IDs,',',''),' ','')) INTO n_Res FROM DUAL;
sSql := 'SELECT INTERACTION_ID FROM JTF_IH_INTERACTIONS WHERE ACTIVE = :active ORDER BY INTERACTION_ID ';
FOR curActivity IN (SELECT ACTIVITY_ID FROM JTF_IH_ACTIVITIES
WHERE INTERACTION_ID = nInteraction_Id) LOOP
DELETE FROM JTF_IH_ACTIVITIES
WHERE ACTIVITY_ID = curActivity.ACTIVITY_ID
RETURNING Media_ID INTO n_Media_Id;
SELECT Count(*) INTO nCountMedia
FROM JTF_IH_ACTIVITIES WHERE MEDIA_ID = n_Media_Id;
DELETE FROM JTF_IH_MEDIA_ITEMS WHERE MEDIA_ID = n_Media_Id;
DELETE FROM JTF_IH_MEDIA_ITEM_LC_SEGS WHERE MEDIA_ID = n_Media_Id;
DELETE FROM JTF_IH_INTERACTION_INTERS WHERE
(INTERACT_INTERACTION_IDRELATES = nInteraction_Id)
OR (INTERACT_INTERACTION_ID = nInteraction_Id);
DELETE FROM JTF_IH_INTERACTIONS WHERE INTERACTION_ID = nInteraction_Id;
SELECT nCountActivities + Count(*) INTO nCountActivities
FROM JTF_IH_ACTIVITIES WHERE INTERACTION_ID = nInteraction_Id;
SELECT nCountMediaItems + Count(*) INTO nCountMediaItems
FROM JTF_IH_ACTIVITIES WHERE INTERACTION_ID = nInteraction_Id AND
Media_ID IS NOT NULL;
PROCEDURE P_DELETE_INTERACTIONS (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
p_processing_set_id IN NUMBER,
p_object_type IN VARCHAR2
)IS
l_api_name CONSTANT VARCHAR2(30) := 'P_DELETE_INTERACTIONS';
SELECT distinct object_id, null
FROM JTF_OBJECT_PURGE_PARAM_TMP
WHERE nvl(purge_status, 'S') <> 'E'
AND OBJECT_TYPE = p_object_type
AND PROCESSING_SET_ID = p_processing_set_id;
l_fnd_log_msg := 'P_DELETE_INTERACTIONS In parameters :'||
'p_api_version = '|| p_api_version ||
'p_init_msg_list = '|| p_init_msg_list ||
'p_commit = '|| p_commit||
'p_processing_set_id = '|| p_processing_set_id ||
'p_object_type = '|| p_object_type;
'jtf.plsql.JTF_IH_PURGE.P_DELETE_INTERACTIONS.begin', l_fnd_log_msg);
SAVEPOINT delete_interactions_p;
DELETE
FROM JTF_IH_ACTIVITIES
WHERE doc_id = l_incident_ids(i)
AND doc_ref = p_object_type
AND action_item_id
IN (SELECT meaning
FROM FND_LOOKUP_VALUES
WHERE lookup_type = 'JTF_IH_PURGE_OBJ_AI_MAP'
AND lookup_code like l_obj_type_lookup
AND view_application_id = 0
AND security_group_id = 0
)
RETURNING media_id,interaction_id,activity_id BULK COLLECT
INTO l_media_ids,l_int_ids,l_activity_id;
'jtf.plsql.JTF_IH_PURGE.P_DELETE_INTERACTIONS.delete_activity',l_fnd_log_msg);
SELECT JTF_OBJECT_PURGE_PROC_SET_S.NEXTVAL
INTO l_processing_set_id
FROM dual
WHERE rownum = 1;
INSERT INTO JTF_OBJECT_PURGE_PARAM_TMP
(
object_id,
object_type,
processing_set_id,
purge_status,
purge_error_message
)
values
(
l_activity_id(i),
l_jtf_activity,
l_processing_set_id,
null,
null
);
--Logging after inserting activities
IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
l_fnd_log_msg := 'No. of Activities Inserted into temp table = '||SQL%ROWCOUNT;
'jtf.plsql.JTF_IH_PURGE.P_DELETE_INTERACTIONS.activity_tmp',l_fnd_log_msg);
'jtf.plsql.JTF_IH_PURGE.P_DELETE_INTERACTIONS.begin.purge_notes', 'Calling Notes Purge API for Activities');
'jtf.plsql.JTF_IH_PURGE.P_DELETE_INTERACTIONS.end.purge_notes', 'After call to Notes Purge API for Activities');
UPDATE JTF_IH_ACTIVITIES
SET doc_id = l_dummy_col(i),
doc_ref = l_dummy_col(i)
WHERE doc_id = l_incident_ids(i)
AND doc_ref = p_object_type;
'jtf.plsql.JTF_IH_PURGE.P_DELETE_INTERACTIONS.delink_act',l_fnd_log_msg);
DELETE
FROM JTF_IH_INTERACTIONS
WHERE interaction_id = l_int_ids(j)
AND
NOT EXISTS (SELECT 1
FROM JTF_IH_ACTIVITIES
WHERE interaction_id = l_int_ids(j))
RETURNING interaction_id BULK COLLECT INTO l_inter_int_ids;
'jtf.plsql.JTF_IH_PURGE.P_DELETE_INTERACTIONS.del_inter',l_fnd_log_msg);
DELETE
FROM JTF_IH_INTERACTION_INTERS
WHERE (INTERACT_INTERACTION_IDRELATES = l_inter_int_ids(j))
OR (INTERACT_INTERACTION_ID = l_inter_int_ids(j));
'jtf.plsql.JTF_IH_PURGE.P_DELETE_INTERACTIONS.del_inter_inter',l_fnd_log_msg);
SELECT JTF_OBJECT_PURGE_PROC_SET_S.NEXTVAL
INTO l_processing_set_id
FROM dual
WHERE rownum = 1;
INSERT INTO JTF_OBJECT_PURGE_PARAM_TMP
(
object_id,
object_type,
processing_set_id,
purge_status,
purge_error_message
)
values
(
l_inter_int_ids(i),
l_jtf_interaction,
l_processing_set_id,
null,
null
);
--Logging after inserting interactions into temp table
IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
l_fnd_log_msg := 'No. of Interactions inserted into temp table = '||SQL%ROWCOUNT;
'jtf.plsql.JTF_IH_PURGE.P_DELETE_INTERACTIONS.inter_tmp',l_fnd_log_msg);
'jtf.plsql.JTF_IH_PURGE.P_DELETE_INTERACTIONS.begin.purge_notes', 'Calling Notes Purge API for Interactions');
'jtf.plsql.JTF_IH_PURGE.P_DELETE_INTERACTIONS.end.purge_notes', 'After call to Notes Purge API for Activities');
DELETE
FROM JTF_IH_MEDIA_ITEMS
WHERE media_id IS NOT NULL
AND media_id <> fnd_api.g_miss_num
AND media_id = l_media_ids(j)
AND
NOT EXISTS (SELECT 1
FROM JTF_IH_ACTIVITIES
WHERE MEDIA_ID = l_media_ids(j))
RETURNING media_id BULK COLLECT INTO lc_media_ids;
'jtf.plsql.JTF_IH_PURGE.P_DELETE_INTERACTIONS.delete_MI',l_fnd_log_msg);
DELETE
FROM JTF_IH_MEDIA_ITEM_LC_SEGS
WHERE MEDIA_ID = lc_media_ids(j);
'jtf.plsql.JTF_IH_PURGE.P_DELETE_INTERACTIONS.delete_MLCS',l_fnd_log_msg);
l_fnd_log_msg := 'P_DELETE_INTERACTIONS Out parameters:' ||
'x_return_status = '|| x_return_status ||
'x_msg_data = '||x_msg_data||
'x_msg_count ='||x_msg_count;
'jtf.plsql.JTF_IH_PURGE.P_DELETE_INTERACTIONS.end', l_fnd_log_msg);
ROLLBACK TO delete_interactions_p;
'jtf.plsql.JTF_IH_PURGE.P_DELETE_INTERACTIONS', x_msg_data);
ROLLBACK TO delete_interactions_p;
'jtf.plsql.JTF_IH_PURGE.P_DELETE_INTERACTIONS', x_msg_data);
ROLLBACK TO delete_interactions_p;
'jtf.plsql.JTF_IH_PURGE.P_DELETE_INTERACTIONS', x_msg_data);
ROLLBACK TO delete_interactions_p;
'jtf.plsql.JTF_IH_PURGE.P_DELETE_INTERACTIONS', x_msg_data);
END P_DELETE_INTERACTIONS;