[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure delete_atth_to_tasks(
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
p_object_type IN VARCHAR2,
p_processing_set_id IN NUMBER)
is
cursor c_fetch_task_ids is
select b.task_id
from jtf_tasks_b b, fnd_attached_documents fad
where b.source_object_type_code = p_object_type
and b.source_object_id in ( select object_id
from jtf_object_purge_param_tmp
where processing_set_id = p_processing_set_id
and ( purge_status is null or purge_status <> 'E'))
and fad.entity_name='JTF_TASKS_B'
and fad.pk1_value=to_char(b.task_id);
l_api_name CONSTANT VARCHAR2(30) := 'delete_atth_to_tasks';
fnd_attached_documents2_pkg.delete_attachments(X_entity_name=>l_entity_name,
X_pk1_value =>to_char(l_tab_task_ids(j)),
X_pk2_value => NULL,
X_pk3_value => NULL,
X_pk4_value => NULL,
X_pk5_value => NULL,
X_delete_document_flag =>'Y',
X_automatically_added_flag => NULL) ;
FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'cac_task_purge_pub.delete_atth_to_tasks', ' x_return_status= '||x_return_status);
FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'cac_task_purge_pub.delete_atth_to_tasks', ' x_msg_data= '||x_msg_data);
FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'cac_task_purge_pub.delete_atth_to_tasks', ' x_msg_count= '||x_msg_count);
FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'cac_task_purge_pub.delete_atth_to_tasks', ' x_return_status= '||x_return_status);
FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'cac_task_purge_pub.delete_atth_to_tasks', ' x_msg_data= '||x_msg_data);
FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'cac_task_purge_pub.delete_atth_to_tasks', ' x_msg_count= '||x_msg_count);
end delete_atth_to_tasks;
Select task_id
from jtf_tasks_b
Where source_object_type_code=b_object_type
And source_object_id in
(select distinct object_id from jtf_object_purge_param_tmp
where processing_set_id=b_processing_set_id and
purge_status is null and object_type=b_object_type)
and entity='TASK';
Select task_id
from jtf_task_references_b
Where object_id in
(select distinct object_id from jtf_object_purge_param_tmp
where (processing_set_id=b_processing_set_id_sr
and object_type=b_object_type)
or (processing_set_id = b_processing_set_id_task
and object_type='TASK')
and purge_status is null);
select jtf_object_purge_proc_set_s.nextval into proc_seq_num from dual;
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'cac_task_purge_pub.purge_tasks', 'Inserting task data into global tem table');
Insert into jtf_object_purge_param_tmp p
( processing_set_id,object_id,object_type,purge_status,purge_error_message)
values
(proc_seq_num, l_tab_tasks_id(i),'TASK', null,null);
Insert into jtf_object_purge_param_tmp p
( processing_set_id,object_id,object_type,purge_status,purge_error_message)
values
(proc_seq_num, l_tab_esc_tasks_id(i),'TASK', null,null);
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'cac_task_purge_pub.purge_tasks', 'Before calling CSM_TASK_PURGE_PKG.DELETE_MFS_TASKS');
CSM_TASK_PURGE_PKG.DELETE_MFS_TASKS(
P_API_VERSION => 1.0,
P_INIT_MSG_LIST => FND_API.G_FALSE,
P_COMMIT => FND_API.G_FALSE,
P_PROCESSING_SET_ID => proc_seq_num ,
P_OBJECT_TYPE => 'TASK' ,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data);
FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'cac_task_purge_pub.purge_tasks', 'return status error after calling CSM_TASK_PURGE_PKG.DELETE_MFS_TASKS');
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'cac_task_purge_pub.purge_tasks', 'Before calling JTF_IH_PURGE.P_DELETE_INTERACTIONS');
JTF_IH_PURGE.P_Delete_Interactions(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_processing_set_id => proc_seq_num,
p_object_type => 'TASK',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'cac_task_purge_pub.purge_tasks', 'return status error after calling JTF_IH_PURGE.P_DELETE_INTERACTIONS');
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'cac_task_purge_pub.purge_tasks', 'Before calling cac_task_purge_pub.delete_atth_to_tasks');
delete_atth_to_tasks(
p_processing_set_id=>proc_seq_num,
p_object_type =>'TASK',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'cac_task_purge_pub.purge_tasks', 'return status error after calling cac_task_purge_pub.delete_atth_to_tasks');
else --no task data exists for the selected service requests
--else for If ( get_task_processing_set_id.COUNT > 0) then
-- dbms_output.put_line(' point 5');
Select task_id
from jtf_tasks_b
Where source_object_type_code=b_object_type
And source_object_id in
(select distinct object_id from jtf_object_purge_param_tmp
where processing_set_id=b_processing_set_id and
purge_status is null and object_type=b_object_type)
and entity='TASK';
Update jtf_object_purge_param_tmp joppt
Set joppt.PURGE_STATUS='E', joppt.PURGE_ERROR_MESSAGE='JTF:JTF_TASK_PURGE_VALID_FAIL'
where joppt.processing_set_id=p_processing_set_id
and joppt.object_type=p_object_type
and exists ( select 1 from jtf_tasks_b where
source_object_type_code=joppt.object_type
and nvl(open_flag,'Y')='Y'
and source_object_id=joppt.object_id
and entity='TASK');
select jtf_object_purge_proc_set_s.nextval into proc_seq_num from dual;
Insert into jtf_object_purge_param_tmp p
( processing_set_id,object_id,object_type,purge_status,purge_error_message)
values
(proc_seq_num, l_tab_tasks_id(i),'TASK', null,null);
Update jtf_object_purge_param_tmp temp
Set temp.purge_status='E',
temp.purge_error_message=(select purge_error_message from jtf_object_purge_param_tmp
where processing_set_id=proc_seq_num
and object_type='TASK'
and object_id in (select task_id from jtf_tasks_b
where source_object_id=temp.object_id
and source_object_type_code=temp.object_type and
entity='TASK') and
purge_status is not null and rownum =1)
Where temp.processing_set_id=p_processing_set_id
and temp.object_type=p_object_type
and temp.object_id in
( select distinct b.source_object_id from jtf_tasks_b b,
jtf_object_purge_param_tmp temp where temp.object_id=b.task_id and
temp.processing_set_id=proc_seq_num and temp.object_type='TASK' and temp.purge_status is not null
and b.entity='TASK');