[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| 12-Sep-2005 Swapan Barat Added logic to delete attachments |
| and calling Note's API to delete |
| Notes associated with task |
| 19-Jan-2006 Swapan Barat Added INDEX hint for bug# 4888496 |
| 02-Feb-2006 Swapan Barat Added FND_LOG |
| 21-Feb-2006 Swapan Barat Added call to Field Service, |
| UWQ and Interaction History's API |
| 02-Mar-2006 Swapan Barat Added Task's Timezone concept |
| for bug# 5058905 |
| 15-May-2006 Manas Padhiary Added code to delete from table |
| JTF_TASK_ALL_ASSIGNMENT and |
| Added code to delete record |
| record from JTF_TASK_PHONE table |
| for Bug # 5216358. |
| 30-May-2006 Swapan Barat For bug# 5213367. Using index |
| fnd_concurrent_programs_U1,instead|
| of fnd_concurrent_programs_U2 |
| 13-Jul-2006 Swapan Barat Checking template_flag <> 'Y' |
| before removing records from |
| JTF_TASK_DEPENDS for bug# 5388975 |
+======================================================================*/
Procedure PURGE_STANDALONE_TASKS (
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_creation_date_from IN VARCHAR2 ,
p_creation_date_to IN VARCHAR2 ,
p_last_updation_date_from IN VARCHAR2 ,
p_last_updation_date_to IN VARCHAR2 ,
p_planned_end_date_from IN VARCHAR2 ,
p_planned_end_date_to IN VARCHAR2 ,
p_scheduled_end_date_from IN VARCHAR2 ,
p_scheduled_end_date_to IN VARCHAR2 ,
p_actual_end_date_from IN VARCHAR2 ,
p_actual_end_date_to IN VARCHAR2 ,
p_task_type_id IN NUMBER DEFAULT NULL ,
p_task_status_id IN NUMBER DEFAULT NULL ,
p_delete_closed_task_only IN VARCHAR2 DEFAULT fnd_api.g_false ,
p_delete_deleted_task_only IN VARCHAR2 DEFAULT fnd_api.g_false,
p_no_of_worker IN NUMBER DEFAULT 4 )
IS
l_api_version CONSTANT NUMBER := 1.0;
Select request_id From FND_CONCURRENT_REQUESTS
Where parent_request_id = p_request_id;
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'CAC_TASK_PURGE_PVT.PURGE_STANDALONE_TASKS', 'p_delete_closed_task_only = '||p_delete_closed_task_only);
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'CAC_TASK_PURGE_PVT.PURGE_STANDALONE_TASKS', 'p_delete_deleted_task_only = '||p_delete_deleted_task_only);
Delete JTF_TASK_PURGE
Where concurrent_request_id In
(Select /*+ INDEX(p fnd_concurrent_programs_U1) */ r.request_id
From fnd_concurrent_requests r ,
fnd_concurrent_programs p
Where r.phase_code = 'C'
And p.concurrent_program_id = r.concurrent_program_id
And p.concurrent_program_name = 'CACTKPUR');
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.PURGE_STANDALONE_TASKS', 'Constructing dynamic select statement');
l_sql_string := 'Select task_id, entity From JTF_TASKS_B Where source_object_type_code = ''TASK''';
l_sql_string := l_sql_string||' And last_update_date <= To_Date('''||
To_Char(l_last_updation_date_to,'DD-MON-YYYY HH24:MI:SS')||''''||','||'''DD-MON-YYYY HH24:MI:SS'')';
l_sql_string := l_sql_string||' And last_update_date >= To_Date('''||
To_Char(l_last_updation_date_from,'DD-MON-YYYY HH24:MI:SS')||''''||','||'''DD-MON-YYYY HH24:MI:SS'')';
-- when p_delete_closed_task_only is not null
IF ((p_delete_closed_task_only IS NOT NULL)
And (p_delete_closed_task_only = 'Y'))
THEN
l_sql_string := l_sql_string||' And NVL(open_flag,''Y'') = ''N''';
-- when p_delete_deleted_task_only is not null
IF ((p_delete_deleted_task_only IS NOT NULL)
And (p_delete_deleted_task_only = 'Y'))
THEN
l_sql_string := l_sql_string||' And NVL(deleted_flag,''N'') = ''Y''';
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.PURGE_STANDALONE_TASKS', 'Calling dynamic select statement = '||l_sql_string);
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.PURGE_STANDALONE_TASKS', 'Inserting data into staging table - JTF_TASK_PURGE');
Insert Into JTF_TASK_PURGE(object_type,
object_id,
concurrent_request_id)
Values (l_tab_task_entity(i),
l_tab_task_id(i),
l_request_id);
Update JTF_TASK_PURGE Set worker_id=l_set_worker
Where concurrent_request_id=l_request_id
And object_id=l_tab_task_id(j);
AND l_worker_conc_req_dev_status IN ('ERROR', 'DELETED', 'TERMINATED'))
THEN
l_main_conc_req_dev_status := 'ERROR';
Procedure DELETE_TASK_ATTACHMENTS (
p_api_version IN NUMBER ,
p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false ,
p_commit IN VARCHAR2 DEFAULT fnd_api.g_false ,
p_processing_set_id IN NUMBER ,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_data OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER )
IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_TASK_ATTACHMENTS';
Select temp.object_id From JTF_OBJECT_PURGE_PARAM_TMP temp, fnd_attached_documents fad
Where temp.object_type = 'TASK'
And temp.processing_set_id = p_processing_set_id
And NVL(temp.purge_status,'Y') <> 'E'
and fad.entity_name='JTF_TASKS_B'
and fad.pk1_value=to_char(temp.object_id);
SAVEPOINT delete_task_attachments;
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'CAC_TASK_PURGE_PVT.DELETE_TASK_ATTACHMENTS', 'p_processing_set_id = '||p_processing_set_id);
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.DELETE_TASK_ATTACHMENTS', 'Fetching record from JTF_OBJECT_PURGE_PARAM_TMP');
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.DELETE_TASK_ATTACHMENTS', 'Calling FND_ATTACHED_DOCUMENTS2_DKG.Delete_Attachments API to delete attachment');
FND_ATTACHED_DOCUMENTS2_PKG.Delete_Attachments (
X_entity_name => 'JTF_TASKS_B' ,
X_pk1_value => to_char(l_tab_task_id(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
) ;
ROLLBACK TO delete_task_attachments;
Update JTF_OBJECT_PURGE_PARAM_TMP Set purge_status='E', purge_error_message=SUBSTRB(x_msg_data,1,4000)
Where object_type = 'TASK'
And processing_set_id = p_processing_set_id
And object_id = l_tab_task_id(j);
FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'CAC_TASK_PURGE_PVT.DELETE_TASK_ATTACHMENTS', 'fnd_api.g_exc_unexpected_error');
FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'CAC_TASK_PURGE_PVT.DELETE_TASK_ATTACHMENTS', 'x_msg_count = '||x_msg_count);
FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'CAC_TASK_PURGE_PVT.DELETE_TASK_ATTACHMENTS', 'Error message = '||REPLACE(x_msg_data,CHR(0),' '));
ROLLBACK TO delete_task_attachments;
Update JTF_OBJECT_PURGE_PARAM_TMP Set purge_status='E', purge_error_message=SUBSTRB(x_msg_data,1,4000)
Where object_type = 'TASK'
And processing_set_id = p_processing_set_id
And object_id = l_tab_task_id(j);
FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'CAC_TASK_PURGE_PVT.DELETE_TASK_ATTACHMENTS', 'OTHERS error');
FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'CAC_TASK_PURGE_PVT.DELETE_TASK_ATTACHMENTS', 'x_msg_count = '||x_msg_count);
FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'CAC_TASK_PURGE_PVT.DELETE_TASK_ATTACHMENTS', 'Error message = '||REPLACE(x_msg_data,CHR(0),' '));
END DELETE_TASK_ATTACHMENTS;
Update JTF_OBJECT_PURGE_PARAM_TMP
Set purge_status='E',
purge_error_message=SUBSTRB(x_msg_data,1,4000)
Where object_type = 'TASK'
And processing_set_id = p_processing_set_id;
Update JTF_OBJECT_PURGE_PARAM_TMP
Set purge_status='E',
purge_error_message=SUBSTRB(x_msg_data,1,4000)
Where object_type = 'TASK'
And processing_set_id = p_processing_set_id;
Select object_type, object_id
From JTF_TASK_PURGE
Where concurrent_request_id = p_concurrent_request_id
And worker_id = p_worker_id
And NVL(purge_status,'Y') <> 'E';
Select object_type, object_id, purge_status, purge_error_message
From JTF_OBJECT_PURGE_PARAM_TMP
Where object_type = 'TASK'
And processing_set_id = l_processing_set_id
And purge_status IS NOT NULL;
Select JTF_OBJECT_PURGE_PROC_SET_S.NEXTVAL
Into l_processing_set_id
From DUAL;
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.POPULATE_PURGE_TMP', 'Inserting data into temp table - JTF_OBJECT_PURGE_PARAM_TMP');
Insert Into JTF_OBJECT_PURGE_PARAM_TMP (object_type,
object_id,
processing_set_id,
purge_status,
purge_error_message)
Values (l_task_source_tab(j),
l_tab_task_id(j),
l_processing_set_id,
NULL,
NULL);
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.POPULATE_PURGE_TMP', 'Calling wrapper API - DELETE_TASK_ATTACHMENTS');
DELETE_TASK_ATTACHMENTS (
p_api_version => 1.0 ,
p_init_msg_list => fnd_api.g_false ,
p_commit => fnd_api.g_false ,
p_processing_set_id => l_processing_set_id ,
x_return_status => l_return_status ,
x_msg_data => l_msg_data ,
x_msg_count => l_msg_count
);
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 => l_processing_set_id ,
p_object_type => 'TASK' ,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.POPULATE_PURGE_TMP', '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 => l_processing_set_id ,
p_object_type => 'TASK' ,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
Update JTF_TASK_PURGE Set process_flag = 'Y'
Where concurrent_request_id = p_concurrent_request_id
And worker_id = p_worker_id
And object_type = l_task_source_tab(j)
And object_id = l_tab_task_id(j);
Update JTF_TASK_PURGE Set purge_status = l_tmp_purge_status(j),
purge_error_message = l_tmp_purge_error_msg(j)
Where object_id = l_tmp_object_id(j)
And object_type = l_tmp_object_type(j)
And concurrent_request_id = p_concurrent_request_id
And worker_id = p_worker_id;
Update JTF_TASK_PURGE Set process_flag = 'Y' ,
purge_status = 'E' ,
purge_error_message = SUBSTRB(l_msg_data,1,4000)
Where concurrent_request_id = p_concurrent_request_id
And worker_id = p_worker_id
And object_type = l_task_source_tab(j)
And object_id = l_tab_task_id(j);
Update JTF_TASK_PURGE Set process_flag = 'Y' ,
purge_status = 'E' ,
purge_error_message = SUBSTRB(l_msg_data,1,4000)
Where concurrent_request_id = p_concurrent_request_id
And worker_id = p_worker_id
And object_type = l_task_source_tab(j)
And object_id = l_tab_task_id(j);
Select object_id From JTF_OBJECT_PURGE_PARAM_TMP
Where object_type = p_object_type
And processing_set_id = p_processing_set_id
And NVL(purge_status,'Y') <> 'E';
Delete JTF_TASK_DATES
Where task_id= l_tab_task_id(j);
Delete JTF_TASK_DEPENDS
Where NVL(template_flag, 'N') <> 'Y' -- Added for bug# 5388975
AND (task_id = l_tab_task_id(j)
or dependent_on_task_id = l_tab_task_id(j));
Delete JTF_TASK_CONTACTS
Where task_id = l_tab_task_id(j)
--Added By MPADHIAR for Bug#5216358
Returning task_contact_id Bulk Collect Into l_tab_task_contact_id;
Delete JTF_TASK_PHONES
WHERE owner_table_name = 'JTF_TASKS_B'
AND task_contact_id = l_tab_task_id(j);
--Delete data from JTF_TASK_PHONES table for phone created for Task Contact
Forall j In l_tab_task_contact_id.FIRST..l_tab_task_contact_id.LAST
Delete JTF_TASK_PHONES
WHERE owner_table_name = 'JTF_TASK_CONTACTS'
AND task_contact_id = l_tab_task_contact_id(j);
Delete JTF_TASK_RSC_REQS
where task_id = l_tab_task_id(j);
Delete JTF_TASK_REFERENCES_B
Where task_id = l_tab_task_id(j)
Returning task_reference_id Bulk Collect Into l_tab_task_ref_id;
--Delete data from JTF_TASK_REFERENCES_TL table
IF l_tab_task_ref_id.COUNT > 0
THEN
----------------------------
-- Statement level Logging
----------------------------
IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
THEN
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.PURGE_TASK_ENTITIES', 'Deleting from table - JTF_TASK_REFERENCES_TL');
Delete JTF_TASK_REFERENCES_TL
Where task_reference_id = l_tab_task_ref_id(i);
Delete JTF_TASK_AUDITS_B
Where task_id = l_tab_task_id(j)
Returning task_audit_id Bulk Collect Into l_tab_task_audits_id;
--Delete data from JTF_TASK_AUDITS_TL table
IF l_tab_task_audits_id.COUNT > 0
THEN
----------------------------
-- Statement level Logging
----------------------------
IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
THEN
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.PURGE_TASK_ENTITIES', 'Deleting from table - JTF_TASK_AUDITS_TL');
Delete JTF_TASK_AUDITS_TL
Where task_audit_id = l_tab_task_audits_id(i);
Delete JTF_TASK_ALL_ASSIGNMENTS
Where task_id = l_tab_task_id(j);
Update JTF_TASKS_B
Set parent_task_id = NULL
Where parent_task_id = l_tab_task_id(j);
Delete JTF_TASKS_B
Where task_id = l_tab_task_id(j)
Returning recurrence_rule_id Bulk Collect Into l_tab_rec_rule_id;
Delete JTF_TASKS_TL
Where task_id = l_tab_task_id(j);
Delete JTF_TASK_RECUR_RULES
Where recurrence_rule_id = l_tab_rec_rule_id(j)
And l_tab_rec_rule_id(j) IS NOT NULL
And NOT EXISTS (Select task_id From JTF_TASKS_B
Where recurrence_rule_id = l_tab_rec_rule_id(j));
Update JTF_OBJECT_PURGE_PARAM_TMP Set purge_status='E', purge_error_message=SUBSTRB(x_msg_data,1,4000)
Where object_type = p_object_type
And processing_set_id = p_processing_set_id
And object_id = l_tab_task_id(j);
Update JTF_OBJECT_PURGE_PARAM_TMP Set purge_status='E', purge_error_message=SUBSTRB(x_msg_data,1,4000)
Where object_type = p_object_type
And processing_set_id = p_processing_set_id
And object_id = l_tab_task_id(j);