[Home] [Help]
PACKAGE BODY: APPS.CAC_TASK_PURGE_PVT
Source
1 PACKAGE BODY CAC_TASK_PURGE_PVT AS
2 /* $Header: cactkpvb.pls 120.18 2006/07/13 12:22:58 sbarat noship $ */
3 /*=======================================================================+
4 | Copyright (c) 2002 Oracle Corporation Redwood Shores, California, USA|
5 | All rights reserved. |
6 +=======================================================================+
7 | FILENAME |
8 | cactkpvb.pls |
9 | |
10 | DESCRIPTION |
11 | - This package is implemented for the commonly used procedure or |
12 | function. |
13 | |
14 | NOTES |
15 | |
16 | Date Developer Change |
17 | ------ --------------- ----------------------------------|
18 | 10-Aug-2005 Swapan Barat Created |
19 | 12-Sep-2005 Swapan Barat Added logic to delete attachments |
20 | and calling Note's API to delete |
21 | Notes associated with task |
22 | 19-Jan-2006 Swapan Barat Added INDEX hint for bug# 4888496 |
23 | 02-Feb-2006 Swapan Barat Added FND_LOG |
24 | 21-Feb-2006 Swapan Barat Added call to Field Service, |
25 | UWQ and Interaction History's API |
26 | 02-Mar-2006 Swapan Barat Added Task's Timezone concept |
27 | for bug# 5058905 |
28 | 15-May-2006 Manas Padhiary Added code to delete from table |
29 | JTF_TASK_ALL_ASSIGNMENT and |
30 | Added code to delete record |
31 | record from JTF_TASK_PHONE table |
32 | for Bug # 5216358. |
33 | 30-May-2006 Swapan Barat For bug# 5213367. Using index |
34 | fnd_concurrent_programs_U1,instead|
35 | of fnd_concurrent_programs_U2 |
36 | 13-Jul-2006 Swapan Barat Checking template_flag <> 'Y' |
37 | before removing records from |
38 | JTF_TASK_DEPENDS for bug# 5388975 |
39 +======================================================================*/
40
41 Procedure PURGE_STANDALONE_TASKS (
42 errbuf OUT NOCOPY VARCHAR2,
43 retcode OUT NOCOPY VARCHAR2,
44 p_creation_date_from IN VARCHAR2 ,
45 p_creation_date_to IN VARCHAR2 ,
46 p_last_updation_date_from IN VARCHAR2 ,
47 p_last_updation_date_to IN VARCHAR2 ,
48 p_planned_end_date_from IN VARCHAR2 ,
49 p_planned_end_date_to IN VARCHAR2 ,
50 p_scheduled_end_date_from IN VARCHAR2 ,
51 p_scheduled_end_date_to IN VARCHAR2 ,
52 p_actual_end_date_from IN VARCHAR2 ,
53 p_actual_end_date_to IN VARCHAR2 ,
54 p_task_type_id IN NUMBER DEFAULT NULL ,
55 p_task_status_id IN NUMBER DEFAULT NULL ,
56 p_delete_closed_task_only IN VARCHAR2 DEFAULT fnd_api.g_false ,
57 p_delete_deleted_task_only IN VARCHAR2 DEFAULT fnd_api.g_false,
58 p_no_of_worker IN NUMBER DEFAULT 4 )
59 IS
60 l_api_version CONSTANT NUMBER := 1.0;
61 l_api_name CONSTANT VARCHAR2(30) := 'PURGE_STANDALONE_TASKS';
62
63 l_sql_string VARCHAR2(2000);
64 l_request_id NUMBER;
65 l_request_data VARCHAR2(1);
66 l_no_of_worker NUMBER;
67 l_batch_size NUMBER;
68 l_set_worker NUMBER;
69 l_start NUMBER;
70 l_end NUMBER;
71
72 l_msg_count NUMBER;
73 l_msg_data VARCHAR2(2000);
74
75 l_tz_enabled_prof VARCHAR2(10) := fnd_profile.value('ENABLE_TIMEZONE_CONVERSIONS');
76 l_server_tz_id NUMBER := to_number(fnd_profile.value_specific('SERVER_TIMEZONE_ID'));
77 l_tz_enabled VARCHAR2(10) := 'N';
78
79 l_creation_date_from DATE;
80 l_creation_date_to DATE;
81 l_last_updation_date_from DATE;
82 l_last_updation_date_to DATE;
83 l_planned_end_date_from DATE;
84 l_planned_end_date_to DATE;
85 l_scheduled_end_date_from DATE;
86 l_scheduled_end_date_to DATE;
87 l_actual_end_date_from DATE;
88 l_actual_end_date_to DATE;
89
90 -- Variables holding the status information of each
91 -- worker concurrent request
92
93 l_worker_conc_req_phase VARCHAR2(100);
94 l_worker_conc_req_status VARCHAR2(100);
95 l_worker_conc_req_dev_phase VARCHAR2(100);
96 l_worker_conc_req_dev_status VARCHAR2(100);
97 l_worker_conc_req_message VARCHAR2(512);
98
99 -- Variables holding the status information of
100 -- the parent concurrent request
101
102 l_main_conc_req_phase VARCHAR2(100);
103 l_main_conc_req_status VARCHAR2(100);
104 l_main_conc_req_dev_phase VARCHAR2(100);
105 l_main_conc_req_dev_status VARCHAR2(100);
106 l_main_conc_req_message VARCHAR2(512);
107 l_child_message VARCHAR2(4000);
108
109 Cursor C_Child_Request(p_request_id NUMBER) Is
110 Select request_id From FND_CONCURRENT_REQUESTS
111 Where parent_request_id = p_request_id;
112
113 TYPE C_Cur_Type IS REF CURSOR;
114 C_Cur_Ref C_Cur_Type;
115 TYPE t_tab_num Is Table Of NUMBER;
116 TYPE t_tab_char Is Table Of VARCHAR2(80);
117
118 l_tab_task_id t_tab_num:=t_tab_num();
119 l_tab_task_entity t_tab_char:=t_tab_char();
120 l_worker_conc_req_arr t_tab_num:=t_tab_num();
121
122 -- predefined error codes for concurrent programs
123 l_cp_succ Constant NUMBER := 0;
124 l_cp_warn Constant NUMBER := 1;
125 l_cp_err Constant NUMBER := 2;
126
127 BEGIN
128
129 SAVEPOINT purge_standalone_tasks;
130
131 IF NOT fnd_api.compatible_api_call (
132 l_api_version,
133 l_api_version,
134 l_api_name,
135 g_pkg_name
136 )
137 THEN
138 RAISE fnd_api.g_exc_unexpected_error;
139 END IF;
140
141 fnd_msg_pub.initialize;
142
143 -- To get concurrent request id as well as data
144
145 l_request_id := fnd_global.conc_request_id;
146 l_request_data := fnd_conc_global.request_data;
147
148
149 ----------------------------
150 -- Procedure level Logging
151 ----------------------------
152 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
153 THEN
154 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'CAC_TASK_PURGE_PVT.PURGE_STANDALONE_TASKS', 'request_id = '||l_request_id);
155 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'CAC_TASK_PURGE_PVT.PURGE_STANDALONE_TASKS', 'request_data = '||l_request_data);
156 END IF;
157
158
159 IF l_request_data IS NULL
160 THEN
161
162 ----------------------------
163 -- Procedure level Logging
164 ----------------------------
165 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
166 THEN
167 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'CAC_TASK_PURGE_PVT.PURGE_STANDALONE_TASKS', 'p_creation_date_from = '||p_creation_date_from);
168 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'CAC_TASK_PURGE_PVT.PURGE_STANDALONE_TASKS', 'p_creation_date_to = '||p_creation_date_to);
169 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'CAC_TASK_PURGE_PVT.PURGE_STANDALONE_TASKS', 'p_last_updation_date_from = '||p_last_updation_date_from);
170 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'CAC_TASK_PURGE_PVT.PURGE_STANDALONE_TASKS', 'p_last_updation_date_to = '||p_last_updation_date_to);
171 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'CAC_TASK_PURGE_PVT.PURGE_STANDALONE_TASKS', 'p_planned_end_date_from = '||p_planned_end_date_from);
172 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'CAC_TASK_PURGE_PVT.PURGE_STANDALONE_TASKS', 'p_planned_end_date_to = '||p_planned_end_date_to);
173 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'CAC_TASK_PURGE_PVT.PURGE_STANDALONE_TASKS', 'p_scheduled_end_date_from = '||p_scheduled_end_date_from);
174 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'CAC_TASK_PURGE_PVT.PURGE_STANDALONE_TASKS', 'p_scheduled_end_date_to = '||p_scheduled_end_date_to);
175 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'CAC_TASK_PURGE_PVT.PURGE_STANDALONE_TASKS', 'p_actual_end_date_from = '||p_actual_end_date_from);
176 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'CAC_TASK_PURGE_PVT.PURGE_STANDALONE_TASKS', 'p_actual_end_date_to = '||p_actual_end_date_to);
177 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'CAC_TASK_PURGE_PVT.PURGE_STANDALONE_TASKS', 'p_task_type_id = '||p_task_type_id);
178 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'CAC_TASK_PURGE_PVT.PURGE_STANDALONE_TASKS', 'p_task_status_id = '||p_task_status_id);
179 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'CAC_TASK_PURGE_PVT.PURGE_STANDALONE_TASKS', 'p_delete_closed_task_only = '||p_delete_closed_task_only);
180 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'CAC_TASK_PURGE_PVT.PURGE_STANDALONE_TASKS', 'p_delete_deleted_task_only = '||p_delete_deleted_task_only);
181 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'CAC_TASK_PURGE_PVT.PURGE_STANDALONE_TASKS', 'p_no_of_worker = '||p_no_of_worker);
182 END IF;
183
184
185 -- Converting all dates from VARCHAR2 to DATE datatype
186
187 l_creation_date_from :=TO_DATE(p_creation_date_from, 'YYYY/MM/DD HH24:MI:SS');
188 l_creation_date_to :=TO_DATE(p_creation_date_to, 'YYYY/MM/DD HH24:MI:SS');
189 l_last_updation_date_from :=TO_DATE(p_last_updation_date_from, 'YYYY/MM/DD HH24:MI:SS');
190 l_last_updation_date_to :=TO_DATE(p_last_updation_date_to, 'YYYY/MM/DD HH24:MI:SS');
191 l_planned_end_date_from :=TO_DATE(p_planned_end_date_from, 'YYYY/MM/DD HH24:MI:SS');
192 l_planned_end_date_to :=TO_DATE(p_planned_end_date_to, 'YYYY/MM/DD HH24:MI:SS');
193 l_scheduled_end_date_from :=TO_DATE(p_scheduled_end_date_from , 'YYYY/MM/DD HH24:MI:SS');
194 l_scheduled_end_date_to :=TO_DATE(p_scheduled_end_date_to, 'YYYY/MM/DD HH24:MI:SS');
195 l_actual_end_date_from :=TO_DATE(p_actual_end_date_from, 'YYYY/MM/DD HH24:MI:SS');
196 l_actual_end_date_to :=TO_DATE(p_actual_end_date_to, 'YYYY/MM/DD HH24:MI:SS');
197
198 -- Validation for TO Date.
199
200 IF ((l_creation_date_to is null) And (l_last_updation_date_to is null)
201 And (l_planned_end_date_to is null) And (l_scheduled_end_date_to is null)
202 And (l_actual_end_date_to is null))
203 THEN
204 FND_MESSAGE.Set_Name ('JTF', 'CAC_NO_TO_DATE_PROVIDED');
205 FND_MSG_PUB.Add;
206 --fnd_file.put_line(FND_FILE.LOG, FND_MESSAGE.GET);
207 RAISE fnd_api.g_exc_unexpected_error;
208 End if;
209
210
211 ----------------------------
212 -- Statement level Logging
213 ----------------------------
214 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
215 THEN
216 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.PURGE_STANDALONE_TASKS', 'Deleting data from staging table - JTF_TASK_PURGE');
217 END IF;
218
219
220 -- Cleanup process: Delete all the rows in the staging table corresponding
221 -- to completed concurrent programs that have been left behind by an earlier
222 -- execution of this concurrent program.
223
224 -- Added INDEX hint by SBARAT on 19/01/2006 for bug# 4888496
225 -- Modified by SBARAT on 30/05/2006 for bug# 5213367
226
227 Delete JTF_TASK_PURGE
228 Where concurrent_request_id In
229 (Select /*+ INDEX(p fnd_concurrent_programs_U1) */ r.request_id
230 From fnd_concurrent_requests r ,
231 fnd_concurrent_programs p
232 Where r.phase_code = 'C'
233 And p.concurrent_program_id = r.concurrent_program_id
234 And p.concurrent_program_name = 'CACTKPUR');
235
236 ----------------------------
237 -- Statement level Logging
238 ----------------------------
239 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
240 THEN
241 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.PURGE_STANDALONE_TASKS', 'Checking whether environment is timezone enabled');
242 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.PURGE_STANDALONE_TASKS', 'Profile value of Enable Timezone Conversions = '||l_tz_enabled_prof);
243 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.PURGE_STANDALONE_TASKS', 'Profile value of Server Timezone = '||l_server_tz_id);
244 END IF;
245
246 IF (NVL(l_tz_enabled_prof, 'N') = 'Y' AND l_server_tz_id IS NOT NULL)
247 THEN
248 l_tz_enabled := 'Y';
249 END IF;
250
251 ----------------------------
252 -- Statement level Logging
253 ----------------------------
254 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
255 THEN
256 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.PURGE_STANDALONE_TASKS', 'Overall timezone status = '||l_tz_enabled);
257 END IF;
258
259 ----------------------------
260 -- Statement level Logging
261 ----------------------------
262 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
263 THEN
264 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.PURGE_STANDALONE_TASKS', 'Constructing dynamic select statement');
265 END IF;
266
267
268 -- constructing the query
269
270 l_sql_string := 'Select task_id, entity From JTF_TASKS_B Where source_object_type_code = ''TASK''';
271
272 -- when p_creation_date_to is not null
273 IF l_creation_date_to IS NOT NULL
274 THEN
275 l_sql_string := l_sql_string||' And creation_date <= To_Date('''||
276 To_Char(l_creation_date_to,'DD-MON-YYYY HH24:MI:SS')||''''||','||'''DD-MON-YYYY HH24:MI:SS'')';
277 END IF;
278
279 -- when p_creation_date_from is not null
280 IF l_creation_date_from IS NOT NULL
281 THEN
282 l_sql_string := l_sql_string||' And creation_date >= To_Date('''||
283 To_Char(l_creation_date_from,'DD-MON-YYYY HH24:MI:SS')||''''||','||'''DD-MON-YYYY HH24:MI:SS'')';
284 END IF;
285
286 -- when p_last_updation_date_to is not null
287 IF l_last_updation_date_to IS NOT NULL
288 THEN
289 l_sql_string := l_sql_string||' And last_update_date <= To_Date('''||
290 To_Char(l_last_updation_date_to,'DD-MON-YYYY HH24:MI:SS')||''''||','||'''DD-MON-YYYY HH24:MI:SS'')';
291 END IF;
292
293 -- when p_last_updation_date_from is not null
294 IF l_last_updation_date_from IS NOT NULL
295 THEN
296 l_sql_string := l_sql_string||' And last_update_date >= To_Date('''||
297 To_Char(l_last_updation_date_from,'DD-MON-YYYY HH24:MI:SS')||''''||','||'''DD-MON-YYYY HH24:MI:SS'')';
298 END IF;
299
300 -- when p_planned_end_date_to is not null
301 IF l_planned_end_date_to IS NOT NULL
302 THEN
303 IF (NVL(l_tz_enabled, 'N') = 'Y')
304 THEN
305 l_sql_string := l_sql_string||' And Decode(timezone_id, NULL, planned_end_date, '||
306 'NVL(HZ_TIMEZONE_PUB.CONVERT_DATETIME(timezone_id, '||
307 l_server_tz_id||', '||
311 ') <= To_Date('''||
308 'planned_end_date), '||
309 'planned_end_date'||
310 ')'||
312 To_Char(l_planned_end_date_to,'DD-MON-YYYY HH24:MI:SS')||''''||','||'''DD-MON-YYYY HH24:MI:SS'')';
313 ELSE
314 l_sql_string := l_sql_string||' And planned_end_date <= To_Date('''||
315 To_Char(l_planned_end_date_to,'DD-MON-YYYY HH24:MI:SS')||''''||','||'''DD-MON-YYYY HH24:MI:SS'')';
316 END IF;
317 END IF;
318
319 -- when p_planned_end_date_from is not null
320 IF l_planned_end_date_from IS NOT NULL
321 THEN
322 IF (NVL(l_tz_enabled, 'N') = 'Y')
323 THEN
324 l_sql_string := l_sql_string||' And Decode(timezone_id, NULL, planned_end_date, '||
325 'NVL(HZ_TIMEZONE_PUB.CONVERT_DATETIME(timezone_id, '||
326 l_server_tz_id||', '||
327 'planned_end_date), '||
328 'planned_end_date'||
329 ')'||
330 ') >= To_Date('''||
331 To_Char(l_planned_end_date_from,'DD-MON-YYYY HH24:MI:SS')||''''||','||'''DD-MON-YYYY HH24:MI:SS'')';
332
333 ELSE
334 l_sql_string := l_sql_string||' And planned_end_date >= To_Date('''||
335 To_Char(l_planned_end_date_from,'DD-MON-YYYY HH24:MI:SS')||''''||','||'''DD-MON-YYYY HH24:MI:SS'')';
336 END IF;
337 END IF;
338
339 -- when p_scheduled_end_date_to is not null
340 IF l_scheduled_end_date_to IS NOT NULL
341 THEN
342 IF (NVL(l_tz_enabled, 'N') = 'Y')
343 THEN
344 l_sql_string := l_sql_string||' And Decode(timezone_id, NULL, scheduled_end_date, '||
345 'NVL(HZ_TIMEZONE_PUB.CONVERT_DATETIME(timezone_id, '||
346 l_server_tz_id||', '||
347 'scheduled_end_date), '||
348 'scheduled_end_date'||
349 ')'||
350 ') <= To_Date('''||
351 To_Char(l_scheduled_end_date_to,'DD-MON-YYYY HH24:MI:SS')||''''||','||'''DD-MON-YYYY HH24:MI:SS'')';
352 ELSE
353 l_sql_string := l_sql_string||' And scheduled_end_date <= To_Date('''||
354 To_Char(l_scheduled_end_date_to,'DD-MON-YYYY HH24:MI:SS')||''''||','||'''DD-MON-YYYY HH24:MI:SS'')';
355 END IF;
356 END IF;
357
358 -- when p_scheduled_end_date_from is not null
359 IF l_scheduled_end_date_from IS NOT NULL
360 THEN
361 IF (NVL(l_tz_enabled, 'N') = 'Y')
362 THEN
363 l_sql_string := l_sql_string||' And Decode(timezone_id, NULL, scheduled_end_date, '||
364 'NVL(HZ_TIMEZONE_PUB.CONVERT_DATETIME(timezone_id, '||
365 l_server_tz_id||', '||
366 'scheduled_end_date), '||
367 'scheduled_end_date'||
368 ')'||
369 ') >= To_Date('''||
370 To_Char(l_scheduled_end_date_from,'DD-MON-YYYY HH24:MI:SS')||''''||','||'''DD-MON-YYYY HH24:MI:SS'')';
371 ELSE
372 l_sql_string := l_sql_string||' And scheduled_end_date >= To_Date('''||
373 To_Char(l_scheduled_end_date_from,'DD-MON-YYYY HH24:MI:SS')||''''||','||'''DD-MON-YYYY HH24:MI:SS'')';
374 END IF;
375 END IF;
376
377
378 -- when p_actual_end_date_to is not null
379 IF l_actual_end_date_to IS NOT NULL
380 THEN
381 IF (NVL(l_tz_enabled, 'N') = 'Y')
382 THEN
383 l_sql_string := l_sql_string||' And Decode(timezone_id, NULL, actual_end_date, '||
384 'NVL(HZ_TIMEZONE_PUB.CONVERT_DATETIME(timezone_id, '||
385 l_server_tz_id||', '||
386 'actual_end_date), '||
387 'actual_end_date'||
388 ')'||
389 ') <= To_Date('''||
390 To_Char(l_actual_end_date_to,'DD-MON-YYYY HH24:MI:SS')||''''||','||'''DD-MON-YYYY HH24:MI:SS'')';
391 ELSE
392 l_sql_string := l_sql_string||' And actual_end_date <= To_Date('''||
393 To_Char(l_actual_end_date_to,'DD-MON-YYYY HH24:MI:SS')||''''||','||'''DD-MON-YYYY HH24:MI:SS'')';
394 END IF;
395 END IF;
396
397
398 -- when p_actual_end_date_from is not null
399 IF l_actual_end_date_from IS NOT NULL
400 THEN
401 IF (NVL(l_tz_enabled, 'N') = 'Y')
402 THEN
406 'actual_end_date), '||
403 l_sql_string := l_sql_string||' And Decode(timezone_id, NULL, actual_end_date, '||
404 'NVL(HZ_TIMEZONE_PUB.CONVERT_DATETIME(timezone_id, '||
405 l_server_tz_id||', '||
407 'actual_end_date'||
408 ')'||
409 ') >= To_Date('''||
410 To_Char(l_actual_end_date_from,'DD-MON-YYYY HH24:MI:SS')||''''||','||'''DD-MON-YYYY HH24:MI:SS'')';
411 ELSE
412 l_sql_string := l_sql_string||' And actual_end_date >= To_Date('''||
413 To_Char(l_actual_end_date_from,'DD-MON-YYYY HH24:MI:SS')||''''||','||'''DD-MON-YYYY HH24:MI:SS'')';
414 END IF;
415 END IF;
416
417
418 -- when p_task_type_id if not null
419 IF p_task_type_id IS NOT NULL
420 THEN
421 l_sql_string := l_sql_string||' And task_type_id='||p_task_type_id;
422 END IF;
423
424
425 -- when p_task_status_id if not null
426 IF p_task_status_id IS NOT NULL
427 THEN
428 l_sql_string := l_sql_string||' And task_status_id='||p_task_status_id;
429 END IF;
430
431 -- when p_delete_closed_task_only is not null
432 IF ((p_delete_closed_task_only IS NOT NULL)
433 And (p_delete_closed_task_only = 'Y'))
434 THEN
435 l_sql_string := l_sql_string||' And NVL(open_flag,''Y'') = ''N''';
436 END IF;
437
438
439 -- when p_delete_deleted_task_only is not null
440 IF ((p_delete_deleted_task_only IS NOT NULL)
441 And (p_delete_deleted_task_only = 'Y'))
442 THEN
443 l_sql_string := l_sql_string||' And NVL(deleted_flag,''N'') = ''Y''';
444 END IF;
445
446
447 ----------------------------
448 -- Statement level Logging
449 ----------------------------
450 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
451 THEN
452 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.PURGE_STANDALONE_TASKS', 'Calling dynamic select statement = '||l_sql_string);
453 END IF;
454
455
456 -- Open the cursor and fetch values in variables
457
458 Open C_Cur_Ref For l_sql_string;
459 Fetch C_Cur_Ref Bulk Collect Into l_tab_task_id, l_tab_task_entity;
460 Close C_Cur_Ref;
461
462 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
463 THEN
464 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.PURGE_STANDALONE_TASKS', 'No of fetched records = '||l_tab_task_id.count);
465 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.PURGE_STANDALONE_TASKS', 'Inserting data into staging table - JTF_TASK_PURGE');
466 END IF;
467
468 -- Inserting values into JTF_TASK_PURGE
469
470 FORALL i IN l_tab_task_id.FIRST..l_tab_task_id.LAST
471 Insert Into JTF_TASK_PURGE(object_type,
472 object_id,
473 concurrent_request_id)
474 Values (l_tab_task_entity(i),
475 l_tab_task_id(i),
476 l_request_id);
477
478
479 -- Initializing l_no_of_worker. If p_no_of_worker
480 -- is null, then set l_no_of_worker to 4
481
482 l_no_of_worker:=NVL(p_no_of_worker, 4);
483
484
485 -- Start of main logic for invoking child concurrent-program
486
487 IF (l_tab_task_id.COUNT > 0)
488 THEN
489
490 -- Checking whether l_no_of_worker is less than
491 -- the no of tasks to be purged. If so, setting
492 -- l_no-worker equal to no of tasks to be purged
493
494 IF l_no_of_worker > l_tab_task_id.COUNT
495 THEN
496 l_no_of_worker := l_tab_task_id.COUNT;
497 END IF;
498
499
500 -- setting batch size i.e. avg. no of tasks to be purged
501 -- in a single operation
502
503 l_batch_size:=TRUNC(l_tab_task_id.COUNT/l_no_of_worker);
504
505
506 -- Initializing l_start and l_end
507
508 l_start:=l_tab_task_id.FIRST;
509 l_end:=l_batch_size;
510
511
512 ----------------------------
513 -- Statement level Logging
514 ----------------------------
515 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
516 THEN
517 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.PURGE_STANDALONE_TASKS', 'Final no of workers set = '||l_no_of_worker);
518 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.PURGE_STANDALONE_TASKS', 'Batch size = '||l_batch_size);
519 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.PURGE_STANDALONE_TASKS', 'Updating JTF_TASK_PURGE to set worker id');
520 END IF;
521
522
523 -- updating worker_id in JTF_TASK_PURGE table
524
525 FOR i IN 1..l_no_of_worker LOOP
526 l_set_worker:=i;
527 Forall j In l_start..l_end
528 Update JTF_TASK_PURGE Set worker_id=l_set_worker
529 Where concurrent_request_id=l_request_id
530 And object_id=l_tab_task_id(j);
531
535 THEN
532 l_start:=l_start+l_batch_size;
533
534 IF ((i+1) < l_no_of_worker)
536 l_end:=l_end+l_batch_size;
537 ELSE
538 l_end:=l_tab_task_id.LAST;
539 END IF;
540
541 END LOOP;
542
543
544 ----------------------------
545 -- Statement level Logging
546 ----------------------------
547 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
548 THEN
549 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.PURGE_STANDALONE_TASKS', 'Invoke child concurrent request');
550 END IF;
551
552
553 -- Start child concurrent programs
554
555 FOR j IN 1..l_no_of_worker LOOP
556 l_worker_conc_req_arr.EXTEND;
557 l_worker_conc_req_arr(j) := fnd_request.submit_request(
558 application => 'JTF' ,
559 program => 'CACTKCHPUR' ,
560 description => TO_CHAR(j) , -- Displayed in the Name column of Requests Screen
561 start_time => NULL ,
562 sub_request => TRUE ,
563 argument1 => 1 , -- p_api_version_number
564 argument2 => fnd_api.g_false , -- p_init_msg_list
565 argument3 => fnd_api.g_false , -- p_commit
566 argument4 => j , -- p_worker_id
567 argument5 => l_request_id -- p_concurrent_request_id
568 );
569
570 -- If the worker request was not created successfully
571 -- raise an unexpected exception and terminate the
572 -- process.
573
574 IF l_worker_conc_req_arr(j) = 0
575 THEN
576 FND_MESSAGE.Set_Name('JTF', 'CAC_TASK_PURGE_SUBMIT_REQUEST');
577 FND_MSG_PUB.Add;
578 RAISE fnd_api.g_exc_unexpected_error;
579 END IF;
580 END LOOP;
581
582
583 ----------------------------
584 -- Statement level Logging
585 ----------------------------
586 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
587 THEN
588 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.PURGE_STANDALONE_TASKS', 'Pausing parent concurrent request');
589 END IF;
590
591
592 -- Moving the parent concurrent request to Paused
593 -- status in order to start the child
594
595 fnd_conc_global.set_req_globals (
596 conc_status => 'PAUSED' ,
597 request_data => '1'
598 );
599
600 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
601 THEN
602 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.PURGE_STANDALONE_TASKS', 'Commiting so that child concurrent request runs');
603 END IF;
604
605 -- Committing so that the worker concurrent program that
606 -- was submitted above is started by the concurrent manager.
607
608 COMMIT WORK;
609
610 -- At this point, execution of the parent request, invoked for the
611 -- first time, gets over. Here the parent request is moved to a
612 -- paused status after which the procedure execution ends.
613
614 END IF;
615
616 ELSE -- When l_request_data Is NOT NULL
617
618 -- If the concurrent request is restarted from the PAUSED state,
619 -- this portion of the code is executed. When all the child
620 -- requests have completed their work, (their PHASE_CODE
621 -- is 'COMPLETED') the concurrent manager restarts the parent. This
622 -- time, the request_data returns a Non NULL value and so this
623 -- portion of the code is executed.
624
625 l_main_conc_req_dev_status := 'NORMAL';
626
627
628 ----------------------------
629 -- Statement level Logging
630 ----------------------------
631 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
632 THEN
633 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.PURGE_STANDALONE_TASKS', 'Checking the status of child concurrent requests');
634 END IF;
635
636
637 -- check status of worker concurrent request
638 -- to arrive at the parent request's
639 -- completion status
640
641 FOR r_child_request IN c_child_request(l_request_id) LOOP
642
643 IF fnd_concurrent.get_request_status(
644 request_id => r_child_request.request_id ,
645 phase => l_worker_conc_req_phase ,
646 status => l_worker_conc_req_status ,
647 dev_phase => l_worker_conc_req_dev_phase ,
648 dev_status => l_worker_conc_req_dev_status,
649 message => l_worker_conc_req_message
653 ----------------------------
650 )
651 THEN
652
654 -- Statement level Logging
655 ----------------------------
656 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
657 THEN
658 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.PURGE_STANDALONE_TASKS', 'Status of child concurrent request id = '||r_child_request.request_id);
659 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.PURGE_STANDALONE_TASKS', 'l_worker_conc_req_phase = '||l_worker_conc_req_phase);
660 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.PURGE_STANDALONE_TASKS', 'l_worker_conc_req_status = '||l_worker_conc_req_status);
661 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.PURGE_STANDALONE_TASKS', 'l_worker_conc_req_dev_phase = '||l_worker_conc_req_dev_phase);
662 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.PURGE_STANDALONE_TASKS', 'l_worker_conc_req_dev_status = '||l_worker_conc_req_dev_status);
663 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.PURGE_STANDALONE_TASKS', 'l_worker_conc_req_message = '||l_worker_conc_req_message);
664 END IF;
665
666
667 -- If the current worker has completed its work, based
668 -- on the return status of the worker, mark the completion
669 -- status of the main concurrent request.
670
671 IF l_worker_conc_req_dev_status <> 'NORMAL'
672 THEN
673 IF (l_main_conc_req_dev_status IN ('WARNING', 'NORMAL')
674 AND l_worker_conc_req_dev_status IN ('ERROR', 'DELETED', 'TERMINATED'))
675 THEN
676 l_main_conc_req_dev_status := 'ERROR';
677 l_child_message := l_worker_conc_req_message;
678
679 ELSIF (l_main_conc_req_dev_status = 'NORMAL'
680 AND l_worker_conc_req_dev_status = 'WARNING')
681 THEN
682 l_main_conc_req_dev_status := 'WARNING';
683 l_child_message := l_worker_conc_req_message;
684 END IF;
685
686 ----------------------------
687 -- Statement level Logging
688 ----------------------------
689 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
690 THEN
691 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.PURGE_STANDALONE_TASKS', 'Set l_main_conc_req_dev_status = '||l_main_conc_req_dev_status);
692 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.PURGE_STANDALONE_TASKS', 'l_child_message = '||l_child_message);
693 END IF;
694
695 END IF;
696
697 ELSE
698
699 ----------------------------
700 -- Statement level Logging
701 ----------------------------
702 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
703 THEN
704 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.PURGE_STANDALONE_TASKS', 'Error in checking statuses of child requests');
705 END IF;
706
707
708 -- There was a failure while collecting a child request
709 -- status, raising an unexpected exception
710
711 FND_MESSAGE.Set_Name('JTF', 'CAC_TASK_NO_CP_STATUS');
712 FND_MSG_PUB.Add;
713
714 RAISE fnd_api.g_exc_unexpected_error;
715 END IF;
716 END LOOP;
717
718 -- Set the completion status of the main concurrent request
719 -- by raising corresponding exceptions.
720
721 IF l_main_conc_req_dev_status = 'WARNING'
722 THEN
723 FND_MESSAGE.Set_Name('JTF', 'CAC_TASK_WORKER_RET_STAT_WARN');
724 FND_MSG_PUB.Add;
725 RAISE fnd_api.g_exc_unexpected_error;
726 ELSIF l_main_conc_req_dev_status = 'ERROR'
727 THEN
728 FND_MESSAGE.Set_Name('JTF', 'CAC_TASK_WORKER_RET_STAT_ERR');
729 FND_MSG_PUB.Add;
730 RAISE fnd_api.g_exc_unexpected_error;
731 END IF;
732
733 -- In case of 'NORMAL', setting OUT variable to successful,
734 -- committing the work and also truncating JTF_TASK_PURGE table
735
736 IF l_main_conc_req_dev_status = 'NORMAL'
737 THEN
738 FND_MESSAGE.Set_Name('JTF', 'CAC_TASK_WORKER_RET_STAT_SUCC');
739 FND_MSG_PUB.Add;
740
741 l_msg_data:=FND_MSG_PUB.Get(
742 p_msg_index => FND_MSG_PUB.G_LAST ,
743 p_encoded => 'F'
744 );
745
746 -- Setting the completion status of this concurrent
747 -- request as COMPLETED NORMALLY
748
749 errbuf := l_msg_data;
750 retcode := l_cp_succ;
751
752 -- committing the work
753
754 COMMIT WORK;
755
756 END IF;
757
758 ----------------------------
759 -- Procedure level Logging
760 ----------------------------
761 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
762 THEN
763 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'CAC_TASK_PURGE_PVT.PURGE_STANDALONE_TASKS', 'errbuf = '||errbuf);
764 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'CAC_TASK_PURGE_PVT.PURGE_STANDALONE_TASKS', 'retcode = '||retcode);
765 END IF;
766
767 END IF; -- End of l_request_data Is NULL
771 THEN
768
769 EXCEPTION
770 WHEN fnd_api.g_exc_unexpected_error
772 ROLLBACK TO purge_standalone_tasks;
773 FND_MSG_PUB.Count_And_Get (
774 p_count => l_msg_count,
775 p_data => l_msg_data
776 );
777
778
779 IF (l_main_conc_req_dev_status = 'ERROR')
780 THEN
781 errbuf := SUBSTRB(REPLACE(l_msg_data,CHR(0),' '), 1, 240);
782 retcode := l_cp_err;
783 ELSIF (l_main_conc_req_dev_status = 'WARNING')
784 THEN
785 errbuf := SUBSTRB(REPLACE(l_msg_data,CHR(0),' '), 1, 240);
786 retcode := l_cp_warn;
787 ELSE
788 errbuf := SUBSTRB(REPLACE(l_msg_data,CHR(0),' '), 1, 240);
789 retcode := l_cp_err;
790 END IF;
791
792 ----------------------------
793 -- Exception level Logging
794 ----------------------------
795 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
796 THEN
797 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'CAC_TASK_PURGE_PVT.PURGE_STANDALONE_TASKS', 'fnd_api.g_exc_unexpected_error');
798 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'CAC_TASK_PURGE_PVT.PURGE_STANDALONE_TASKS', 'l_msg_count = '||l_msg_count);
799 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'CAC_TASK_PURGE_PVT.PURGE_STANDALONE_TASKS', 'Error message = '||REPLACE(l_msg_data,CHR(0),' '));
800 END IF;
801
802
803 WHEN OTHERS
804 THEN
805 ROLLBACK TO purge_standalone_tasks;
806 FND_MESSAGE.Set_Name ('JTF', 'CAC_TASK_UNKNOWN_ERROR');
807 FND_MESSAGE.Set_Token ('P_TEXT', G_PKG_NAME||'.'||l_api_name||' : '||SQLCODE||': '|| SQLERRM);
808 FND_MSG_PUB.Add;
809 FND_MSG_PUB.Count_And_Get (
810 p_count => l_msg_count,
811 p_data => l_msg_data
812 );
813
814
815 IF (l_main_conc_req_dev_status = 'ERROR')
816 THEN
817 errbuf := SUBSTRB(REPLACE(l_msg_data,CHR(0),' '), 1, 240);
818 retcode := l_cp_err;
819 ELSIF (l_main_conc_req_dev_status = 'WARNING')
820 THEN
821 errbuf := SUBSTRB(REPLACE(l_msg_data,CHR(0),' '), 1, 240);
822 retcode := l_cp_warn;
823 ELSE
824 errbuf := SUBSTRB(REPLACE(l_msg_data,CHR(0),' '), 1, 240);
825 retcode := l_cp_err;
826 END IF;
827
828 ----------------------------
829 -- Eeception level Logging
830 ----------------------------
831 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
832 THEN
833 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'CAC_TASK_PURGE_PVT.PURGE_STANDALONE_TASKS', 'OTHERS error');
834 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'CAC_TASK_PURGE_PVT.PURGE_STANDALONE_TASKS', 'l_msg_count = '||l_msg_count);
835 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'CAC_TASK_PURGE_PVT.PURGE_STANDALONE_TASKS', 'Error message = '||REPLACE(l_msg_data,CHR(0),' '));
836 END IF;
837
838
839 END PURGE_STANDALONE_TASKS;
840
841 Procedure DELETE_TASK_ATTACHMENTS (
842 p_api_version IN NUMBER ,
843 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false ,
844 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false ,
845 p_processing_set_id IN NUMBER ,
846 x_return_status OUT NOCOPY VARCHAR2 ,
847 x_msg_data OUT NOCOPY VARCHAR2 ,
848 x_msg_count OUT NOCOPY NUMBER )
849 IS
850 l_api_version CONSTANT NUMBER := 1.0;
851 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_TASK_ATTACHMENTS';
852 l_entity_name VARCHAR2(40):='JTF_TASKS_B';
853
854 Cursor C_Task_Id Is
855
856 Select temp.object_id From JTF_OBJECT_PURGE_PARAM_TMP temp, fnd_attached_documents fad
857 Where temp.object_type = 'TASK'
858 And temp.processing_set_id = p_processing_set_id
859 And NVL(temp.purge_status,'Y') <> 'E'
860 and fad.entity_name='JTF_TASKS_B'
861 and fad.pk1_value=to_char(temp.object_id);
862
863 TYPE t_tab_num Is Table Of NUMBER;
864
865 l_tab_task_id t_tab_num:=t_tab_num();
866
867 BEGIN
868
869 SAVEPOINT delete_task_attachments;
870
871 x_return_status := fnd_api.g_ret_sts_success;
872
873 IF NOT fnd_api.compatible_api_call (
874 l_api_version,
875 p_api_version,
876 l_api_name,
877 g_pkg_name
878 )
879 THEN
880 RAISE fnd_api.g_exc_unexpected_error;
881 END IF;
882
883 IF fnd_api.to_boolean (p_init_msg_list)
884 THEN
885 fnd_msg_pub.initialize;
886 END IF;
887
888
889 ----------------------------
890 -- Procedure level Logging
891 ----------------------------
892 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
893 THEN
894 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'CAC_TASK_PURGE_PVT.DELETE_TASK_ATTACHMENTS', 'p_processing_set_id = '||p_processing_set_id);
895 END IF;
896
897 ----------------------------
898 -- Statement level Logging
899 ----------------------------
900 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
901 THEN
902 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.DELETE_TASK_ATTACHMENTS', 'Fetching record from JTF_OBJECT_PURGE_PARAM_TMP');
903 END IF;
907 Fetch C_Task_Id Bulk Collect Into l_tab_task_id;
904
905
906 Open C_Task_Id;
908 Close C_Task_Id;
909
910 IF l_tab_task_id.COUNT > 0
911 THEN
912
913 ----------------------------
914 -- Statement level Logging
915 ----------------------------
916 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
917 THEN
918 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');
919 END IF;
920
921
922 -- Deleting attachment information from Attachment table
923
924 FOR j IN 1..l_tab_task_id.LAST LOOP
925
926 FND_ATTACHED_DOCUMENTS2_PKG.Delete_Attachments (
927 X_entity_name => 'JTF_TASKS_B' ,
928 X_pk1_value => to_char(l_tab_task_id(j)) ,
929 X_pk2_value => NULL ,
930 X_pk3_value => NULL ,
931 X_pk4_value => NULL ,
932 X_pk5_value => NULL ,
933 X_delete_document_flag => 'Y' ,
934 X_automatically_added_flag => NULL
935 ) ;
936
937 END LOOP;
938
939 END IF;
940
941 IF fnd_api.to_boolean(p_commit)
942 THEN
943 COMMIT WORK;
944 END IF;
945
946 EXCEPTION
947 WHEN fnd_api.g_exc_unexpected_error
948 THEN
949 ROLLBACK TO delete_task_attachments;
950 x_return_status := fnd_api.g_ret_sts_unexp_error;
951 FND_MSG_PUB.Count_And_Get (
952 p_count => x_msg_count,
953 p_data => x_msg_data
954 );
955 IF l_tab_task_id.COUNT > 0
956 THEN
957 FORALL j IN l_tab_task_id.FIRST..l_tab_task_id.LAST
958 Update JTF_OBJECT_PURGE_PARAM_TMP Set purge_status='E', purge_error_message=SUBSTRB(x_msg_data,1,4000)
959 Where object_type = 'TASK'
960 And processing_set_id = p_processing_set_id
961 And object_id = l_tab_task_id(j);
962 END IF;
963
964 ----------------------------
965 -- Exception level Logging
966 ----------------------------
967 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
968 THEN
969 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'CAC_TASK_PURGE_PVT.DELETE_TASK_ATTACHMENTS', 'fnd_api.g_exc_unexpected_error');
970 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'CAC_TASK_PURGE_PVT.DELETE_TASK_ATTACHMENTS', 'x_msg_count = '||x_msg_count);
971 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'CAC_TASK_PURGE_PVT.DELETE_TASK_ATTACHMENTS', 'Error message = '||REPLACE(x_msg_data,CHR(0),' '));
972 END IF;
973
974 WHEN OTHERS
975 THEN
976 ROLLBACK TO delete_task_attachments;
977 FND_MESSAGE.Set_Name ('JTF', 'CAC_TASK_UNKNOWN_ERROR');
978 FND_MESSAGE.Set_Token ('P_TEXT', G_PKG_NAME||'.'||l_api_name ||' : '||SQLCODE||' : '|| SQLERRM);
979 FND_MSG_PUB.Add;
980 x_return_status := fnd_api.g_ret_sts_unexp_error;
981 FND_MSG_PUB.Count_And_Get (
982 p_count => x_msg_count,
983 p_data => x_msg_data
984 );
985
986 IF l_tab_task_id.COUNT > 0
987 THEN
988 FORALL j IN l_tab_task_id.FIRST..l_tab_task_id.LAST
989 Update JTF_OBJECT_PURGE_PARAM_TMP Set purge_status='E', purge_error_message=SUBSTRB(x_msg_data,1,4000)
990 Where object_type = 'TASK'
991 And processing_set_id = p_processing_set_id
992 And object_id = l_tab_task_id(j);
993 END IF;
994
995 ----------------------------
996 -- Exception level Logging
997 ----------------------------
998 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
999 THEN
1000 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'CAC_TASK_PURGE_PVT.DELETE_TASK_ATTACHMENTS', 'OTHERS error');
1001 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'CAC_TASK_PURGE_PVT.DELETE_TASK_ATTACHMENTS', 'x_msg_count = '||x_msg_count);
1002 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'CAC_TASK_PURGE_PVT.DELETE_TASK_ATTACHMENTS', 'Error message = '||REPLACE(x_msg_data,CHR(0),' '));
1003 END IF;
1004
1005 END DELETE_TASK_ATTACHMENTS;
1006
1007 Procedure VALIDATE_STANDALONE_TASK(
1008 p_api_version IN NUMBER,
1009 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
1010 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
1011 p_processing_set_id IN NUMBER,
1012 x_return_status OUT NOCOPY VARCHAR2,
1013 x_msg_data OUT NOCOPY VARCHAR2,
1014 x_msg_count OUT NOCOPY NUMBER)
1015 IS
1016 l_api_version CONSTANT NUMBER := 1.0;
1017 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_STANDALONE_TASK';
1018
1019 BEGIN
1020 SAVEPOINT validate_standalone_task;
1021
1022 x_return_status := fnd_api.g_ret_sts_success;
1023
1024 IF NOT fnd_api.compatible_api_call (
1025 l_api_version,
1026 p_api_version,
1027 l_api_name,
1028 g_pkg_name
1029 )
1030 THEN
1031 RAISE fnd_api.g_exc_unexpected_error;
1032 END IF;
1033
1037 END IF;
1034 IF fnd_api.to_boolean (p_init_msg_list)
1035 THEN
1036 fnd_msg_pub.initialize;
1038
1039
1040 ----------------------------
1041 -- Procedure level Logging
1042 ----------------------------
1043 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1044 THEN
1045 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'CAC_TASK_PURGE_PVT.VALIDATE_STANDALONE_TASK', 'Start of VALIDATE_STANDALONE_TASK');
1046 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'CAC_TASK_PURGE_PVT.VALIDATE_STANDALONE_TASK', 'p_processing_set_id = '||p_processing_set_id);
1047 END IF;
1048
1049 ----------------------------
1050 -- Statement level Logging
1051 ----------------------------
1052 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1053 THEN
1054 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.VALIDATE_STANDALONE_TASK', 'Before calling CSM_TASK_PURGE_PKG.VALIDATE_MFS_TASKS');
1055 END IF;
1056
1057 CSM_TASK_PURGE_PKG.VALIDATE_MFS_TASKS(
1058 P_API_VERSION => 1.0,
1059 P_INIT_MSG_LIST => FND_API.G_FALSE ,
1060 P_COMMIT => FND_API.G_FALSE ,
1061 P_PROCESSING_SET_ID => p_processing_set_id ,
1062 P_OBJECT_TYPE => 'TASK' ,
1063 X_RETURN_STATUS => x_return_status ,
1064 X_MSG_COUNT => x_msg_count ,
1065 X_MSG_DATA => x_msg_data);
1066
1067 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
1068 THEN
1069 x_return_status := fnd_api.g_ret_sts_unexp_error;
1070
1071 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1072 THEN
1073 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'VALIDATE_STANDALONE_TASK', 'return status error after calling CSM_TASK_PURGE_PKG.VALIDATE_MFS_TASKS');
1074 END IF;
1075
1076 RAISE fnd_api.g_exc_unexpected_error;
1077
1078 END IF;
1079
1080
1081 IF fnd_api.to_boolean (p_commit)
1082 THEN
1083 COMMIT WORK;
1084 END IF;
1085
1086 ----------------------------
1087 -- Procedure level Logging
1088 ----------------------------
1089 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1090 THEN
1091 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'CAC_TASK_PURGE_PVT.VALIDATE_STANDALONE_TASK', 'End of VALIDATE_STANDALONE_TASK');
1092 END IF;
1093
1094 FND_MSG_PUB.Count_And_Get (
1095 p_count => x_msg_count,
1096 p_data => x_msg_data
1097 );
1098
1099 EXCEPTION
1100 WHEN fnd_api.g_exc_unexpected_error
1101 THEN
1102 ROLLBACK TO validate_standalone_task;
1103 x_return_status := fnd_api.g_ret_sts_unexp_error;
1104 FND_MSG_PUB.Count_And_Get (
1105 p_count => x_msg_count,
1106 p_data => x_msg_data
1107 );
1108
1109 Update JTF_OBJECT_PURGE_PARAM_TMP
1110 Set purge_status='E',
1111 purge_error_message=SUBSTRB(x_msg_data,1,4000)
1112 Where object_type = 'TASK'
1113 And processing_set_id = p_processing_set_id;
1114
1115 ----------------------------
1116 -- Exception level Logging
1117 ----------------------------
1118 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1119 THEN
1120 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'CAC_TASK_PURGE_PVT.VALIDATE_STANDALONE_TASK', 'fnd_api.g_exc_unexpected_error');
1121 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'CAC_TASK_PURGE_PVT.VALIDATE_STANDALONE_TASK', 'x_msg_count = '||x_msg_count);
1122 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'CAC_TASK_PURGE_PVT.VALIDATE_STANDALONE_TASK', 'Error message = '||REPLACE(x_msg_data,CHR(0),' '));
1123 END IF;
1124
1125 WHEN OTHERS
1126 THEN
1127 ROLLBACK TO validate_standalone_task;
1128 FND_MESSAGE.Set_Name ('JTF', 'CAC_TASK_UNKNOWN_ERROR');
1129 FND_MESSAGE.Set_Token ('P_TEXT', G_PKG_NAME||'.'||l_api_name ||' : '||SQLCODE||' : '|| SQLERRM);
1130 FND_MSG_PUB.Add;
1131 x_return_status := fnd_api.g_ret_sts_unexp_error;
1132 FND_MSG_PUB.Count_And_Get (
1133 p_count => x_msg_count,
1134 p_data => x_msg_data
1135 );
1136
1137 Update JTF_OBJECT_PURGE_PARAM_TMP
1138 Set purge_status='E',
1139 purge_error_message=SUBSTRB(x_msg_data,1,4000)
1140 Where object_type = 'TASK'
1141 And processing_set_id = p_processing_set_id;
1142
1143 ----------------------------
1144 -- Exception level Logging
1145 ----------------------------
1146 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1147 THEN
1148 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'CAC_TASK_PURGE_PVT.VALIDATE_STANDALONE_TASK', 'OTHERS error');
1149 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'CAC_TASK_PURGE_PVT.VALIDATE_STANDALONE_TASK', 'x_msg_count = '||x_msg_count);
1150 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'CAC_TASK_PURGE_PVT.VALIDATE_STANDALONE_TASK', 'Error message = '||REPLACE(x_msg_data,CHR(0),' '));
1151 END IF;
1152
1153 END VALIDATE_STANDALONE_TASK;
1154
1155 Procedure POPULATE_PURGE_TMP (
1156 errbuf OUT NOCOPY VARCHAR2,
1157 retcode OUT NOCOPY VARCHAR2,
1158 p_api_version IN NUMBER ,
1159 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false ,
1160 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false ,
1164 l_api_version CONSTANT NUMBER := 1.0;
1161 p_worker_id IN NUMBER ,
1162 p_concurrent_request_id IN NUMBER)
1163 IS
1165 l_api_name CONSTANT VARCHAR2(30) := 'POPULATE_PURGE_TMP';
1166
1167 l_processing_set_id NUMBER;
1168 l_return_status VARCHAR2(10);
1169 l_msg_data VARCHAR2(4000);
1170 l_msg_count NUMBER;
1171
1172 Cursor C_Object_Id Is
1173 Select object_type, object_id
1174 From JTF_TASK_PURGE
1175 Where concurrent_request_id = p_concurrent_request_id
1176 And worker_id = p_worker_id
1177 And NVL(purge_status,'Y') <> 'E';
1178
1179 Cursor C_Error_Tmp Is
1180 Select object_type, object_id, purge_status, purge_error_message
1181 From JTF_OBJECT_PURGE_PARAM_TMP
1182 Where object_type = 'TASK'
1183 And processing_set_id = l_processing_set_id
1184 And purge_status IS NOT NULL;
1185
1186 TYPE t_tab_num Is Table Of NUMBER;
1187 TYPE t_tab_char Is Table Of VARCHAR2(80);
1188 TYPE t_tab_small_char Is Table Of VARCHAR2(1);
1189 TYPE t_tab_long_char Is Table Of VARCHAR2(4000);
1190
1191 l_tab_task_id t_tab_num := t_tab_num();
1192 l_task_source_tab t_tab_char := t_tab_char();
1193 l_tmp_object_type t_tab_char := t_tab_char();
1194 l_tmp_object_id t_tab_num := t_tab_num();
1195 l_tmp_purge_status t_tab_small_char:=t_tab_small_char();
1196 l_tmp_purge_error_msg t_tab_long_char:=t_tab_long_char();
1197
1198 -- predefined error codes for concurrent programs
1199 l_cp_succ Constant NUMBER := 0;
1200 l_cp_warn Constant NUMBER := 1;
1201 l_cp_err Constant NUMBER := 2;
1202
1203 BEGIN
1204
1205 SAVEPOINT populate_purge_tmp;
1206
1207 IF NOT fnd_api.compatible_api_call (
1208 l_api_version,
1209 p_api_version,
1210 l_api_name,
1211 g_pkg_name
1212 )
1213 THEN
1214 RAISE fnd_api.g_exc_unexpected_error;
1215 END IF;
1216
1217 IF fnd_api.to_boolean (p_init_msg_list)
1218 THEN
1219 fnd_msg_pub.initialize;
1220 END IF;
1221
1222
1223 ----------------------------
1224 -- Procedure level Logging
1225 ----------------------------
1226 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1227 THEN
1228 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'CAC_TASK_PURGE_PVT.POPULATE_PURGE_TMP', 'p_worker_id = '||p_worker_id);
1229 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'CAC_TASK_PURGE_PVT.POPULATE_PURGE_TMP', 'p_concurrent_request_id = '||p_concurrent_request_id);
1230 END IF;
1231
1232 ----------------------------
1233 -- Statement level Logging
1234 ----------------------------
1235 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1236 THEN
1237 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.POPULATE_PURGE_TMP', 'Fetching data from JTF_TASK_PURGE');
1238 END IF;
1239
1240
1241 Open C_Object_Id;
1242 Fetch C_Object_Id BULK COLLECT INTO l_task_source_tab, l_tab_task_id;
1243 Close C_Object_Id;
1244
1245
1246 ----------------------------
1247 -- Statement level Logging
1248 ----------------------------
1249 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1250 THEN
1251 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.POPULATE_PURGE_TMP', 'No of records fetched from JTF_TASK_PURGE');
1252 END IF;
1253
1254
1255 IF l_tab_task_id.COUNT > 0
1256 THEN
1257
1258 Select JTF_OBJECT_PURGE_PROC_SET_S.NEXTVAL
1259 Into l_processing_set_id
1260 From DUAL;
1261
1262
1263 ----------------------------
1264 -- Statement level Logging
1265 ----------------------------
1266 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1267 THEN
1268 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.POPULATE_PURGE_TMP', 'Inserting data into temp table - JTF_OBJECT_PURGE_PARAM_TMP');
1269 END IF;
1270
1271
1272 Forall j In l_tab_task_id.FIRST..l_tab_task_id.LAST
1273 Insert Into JTF_OBJECT_PURGE_PARAM_TMP (object_type,
1274 object_id,
1275 processing_set_id,
1276 purge_status,
1277 purge_error_message)
1278 Values (l_task_source_tab(j),
1279 l_tab_task_id(j),
1280 l_processing_set_id,
1281 NULL,
1282 NULL);
1283
1284 ----------------------------
1285 -- Statement level Logging
1286 ----------------------------
1287 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1288 THEN
1289 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.VALIDATE_STANDALONE_TASK', 'Calling wrapper API - VALIDATE_STANDALONE_TASK');
1290 END IF;
1291
1292 -- Calling VALIDATE_STANDALONE_TASK to validate standalone tasks
1293
1294 VALIDATE_STANDALONE_TASK (
1298 p_processing_set_id => l_processing_set_id ,
1295 p_api_version => 1.0 ,
1296 p_init_msg_list => fnd_api.g_false ,
1297 p_commit => fnd_api.g_false ,
1299 x_return_status => l_return_status ,
1300 x_msg_count => l_msg_count ,
1301 x_msg_data => l_msg_data
1302 );
1303
1304 IF NOT (l_return_status = fnd_api.g_ret_sts_success)
1305 THEN
1306 l_return_status := fnd_api.g_ret_sts_unexp_error;
1307 RAISE fnd_api.g_exc_unexpected_error;
1308 END IF;
1309
1310 ----------------------------
1311 -- Statement level Logging
1312 ----------------------------
1313 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1314 THEN
1315 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.POPULATE_PURGE_TMP', 'Calling wrapper API - DELETE_TASK_ATTACHMENTS');
1316 END IF;
1317
1318
1319 -- Calling DELETE_TASK_ATTACHMENTS to delete attachments
1320
1321 DELETE_TASK_ATTACHMENTS (
1322 p_api_version => 1.0 ,
1323 p_init_msg_list => fnd_api.g_false ,
1324 p_commit => fnd_api.g_false ,
1325 p_processing_set_id => l_processing_set_id ,
1326 x_return_status => l_return_status ,
1327 x_msg_data => l_msg_data ,
1328 x_msg_count => l_msg_count
1329 );
1330
1331 IF NOT (l_return_status = fnd_api.g_ret_sts_success)
1332 THEN
1333 l_return_status := fnd_api.g_ret_sts_unexp_error;
1334 RAISE fnd_api.g_exc_unexpected_error;
1335 END IF;
1336
1337
1338 ----------------------------
1339 -- Statement level Logging
1340 ----------------------------
1341 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1342 THEN
1343 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.POPULATE_PURGE_TMP', 'Calling note API - CAC_NOTE_PURGE_PUB.PURGE_NOTES');
1344 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.POPULATE_PURGE_TMP', 'Passing l_processing_set_id = '||l_processing_set_id);
1345 END IF;
1346
1347
1348 -- Calling CAC_NOTE_PURGE_PUB.PURGE_NOTES to delete notes
1349
1350 CAC_NOTE_PURGE_PUB.PURGE_NOTES (
1351 p_api_version => 1.0 ,
1352 p_init_msg_list => fnd_api.g_false,
1353 p_commit => fnd_api.g_false,
1354 x_return_status => l_return_status ,
1355 x_msg_data => l_msg_data ,
1356 x_msg_count => l_msg_count ,
1357 p_processing_set_id => l_processing_set_id ,
1358 p_object_type => 'TASK'
1359 ) ;
1360
1361 IF NOT (l_return_status = fnd_api.g_ret_sts_success)
1362 THEN
1363 l_return_status := fnd_api.g_ret_sts_unexp_error;
1364 RAISE fnd_api.g_exc_unexpected_error;
1365 END IF;
1366
1367
1368 ----------------------------
1369 -- Statement level Logging
1370 ----------------------------
1371 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1372 THEN
1373 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.POPULATE_PURGE_TMP', 'Before calling CSM_TASK_PURGE_PKG.VALIDATE_MFS_TASKS');
1374 END IF;
1375
1376 -- Calling CSM_TASK_PURGE_PKG.DELETE_MFS_TASKS to delete data from Field Service
1377
1378 CSM_TASK_PURGE_PKG.DELETE_MFS_TASKS(
1379 p_api_version => 1.0,
1380 p_init_msg_list => fnd_api.g_false,
1381 p_commit => fnd_api.g_false,
1382 p_processing_set_id => l_processing_set_id ,
1383 p_object_type => 'TASK' ,
1384 x_return_status => l_return_status,
1385 x_msg_count => l_msg_count,
1386 x_msg_data => l_msg_data
1387 );
1388
1389 IF NOT (l_return_status = fnd_api.g_ret_sts_success)
1390 THEN
1391 l_return_status := fnd_api.g_ret_sts_unexp_error;
1392 RAISE fnd_api.g_exc_unexpected_error;
1393 END IF;
1394
1395
1396 ----------------------------
1397 -- Statement level Logging
1398 ----------------------------
1399 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1400 THEN
1401 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.POPULATE_PURGE_TMP', 'Before calling IEU_WR_PUB.Purge_Wr_Item');
1402 END IF;
1403
1404
1405 -- Calling IEU_WR_PUB.Purge_Wr_Item to delete data from UWQ
1406
1407 IEU_WR_PUB.Purge_Wr_Item(
1408 p_api_version_number => 1.0,
1409 p_init_msg_list => fnd_api.g_false,
1413 x_return_status => l_return_status,
1410 p_commit => fnd_api.g_false,
1411 p_processing_set_id => l_processing_set_id ,
1412 p_object_type => 'TASK' ,
1414 x_msg_count => l_msg_count,
1415 x_msg_data => l_msg_data
1416 );
1417
1418 IF NOT (l_return_status = fnd_api.g_ret_sts_success)
1419 THEN
1420 l_return_status := fnd_api.g_ret_sts_unexp_error;
1421 RAISE fnd_api.g_exc_unexpected_error;
1422 END IF;
1423
1424
1425 ----------------------------
1426 -- Statement level Logging
1427 ----------------------------
1428 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1429 THEN
1430 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.POPULATE_PURGE_TMP', 'Before calling JTF_IH_PURGE.P_Delete_Interactions');
1431 END IF;
1432
1433
1434 -- Calling JTF_IH_PURGE.P_Delete_Interactions to delete data from Interaction History
1435
1436 JTF_IH_PURGE.P_Delete_Interactions(
1437 p_api_version => 1.0,
1438 p_init_msg_list => fnd_api.g_false,
1439 p_commit => fnd_api.g_false,
1440 p_processing_set_id => l_processing_set_id ,
1441 p_object_type => 'TASK' ,
1442 x_return_status => l_return_status,
1443 x_msg_count => l_msg_count,
1444 x_msg_data => l_msg_data
1445 );
1446
1447
1448 IF NOT (l_return_status = fnd_api.g_ret_sts_success)
1449 THEN
1450 l_return_status := fnd_api.g_ret_sts_unexp_error;
1451 RAISE fnd_api.g_exc_unexpected_error;
1452 END IF;
1453
1454
1455 ----------------------------
1456 -- Statement level Logging
1457 ----------------------------
1458 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1459 THEN
1460 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.POPULATE_PURGE_TMP', 'Calling CAC_TASK_PURGE_PVT.PURGE_TASK_ENTITIES');
1461 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.POPULATE_PURGE_TMP', 'Passing l_processing_set_id = '||l_processing_set_id);
1462 END IF;
1463
1464
1465 -- Calling CAC_TASK_PURGE_PVT.PURGE_TASK_ENTITIES to delte task entities
1466
1467 CAC_TASK_PURGE_PVT.PURGE_TASK_ENTITIES(
1468 p_api_version => 1.0 ,
1469 p_init_msg_list => fnd_api.g_false ,
1470 p_commit => fnd_api.g_false ,
1471 p_processing_set_id => l_processing_set_id ,
1472 x_return_status => l_return_status ,
1473 x_msg_data => l_msg_data ,
1474 x_msg_count => l_msg_count ,
1475 p_object_type => 'TASK'
1476 );
1477
1478 IF NOT (l_return_status = fnd_api.g_ret_sts_success)
1479 THEN
1480 l_return_status := fnd_api.g_ret_sts_unexp_error;
1481 RAISE fnd_api.g_exc_unexpected_error;
1482 END IF;
1483
1484
1485 ----------------------------
1486 -- Statement level Logging
1487 ----------------------------
1488 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1489 THEN
1490 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.POPULATE_PURGE_TMP', 'Updating process_flag of JTF_TASK_PURGE');
1491 END IF;
1492
1493
1494 Forall j In l_tab_task_id.FIRST..l_tab_task_id.LAST
1495 Update JTF_TASK_PURGE Set process_flag = 'Y'
1496 Where concurrent_request_id = p_concurrent_request_id
1497 And worker_id = p_worker_id
1498 And object_type = l_task_source_tab(j)
1499 And object_id = l_tab_task_id(j);
1500
1501 END IF;
1502
1503 ----------------------------
1504 -- Statement level Logging
1505 ----------------------------
1506 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1507 THEN
1508 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.POPULATE_PURGE_TMP', 'Updating purge_status of JTF_TASK_PURGE');
1509 END IF;
1510
1511
1512 Open C_Error_Tmp;
1513 Fetch C_Error_Tmp BULK COLLECT INTO l_tmp_object_type, l_tmp_object_id, l_tmp_purge_status, l_tmp_purge_error_msg;
1514 Close C_Error_Tmp;
1515
1516 IF l_tmp_object_id.COUNT > 0
1517 THEN
1518 Forall j In l_tmp_object_id.FIRST..l_tmp_object_id.LAST
1519 Update JTF_TASK_PURGE Set purge_status = l_tmp_purge_status(j),
1520 purge_error_message = l_tmp_purge_error_msg(j)
1521 Where object_id = l_tmp_object_id(j)
1522 And object_type = l_tmp_object_type(j)
1523 And concurrent_request_id = p_concurrent_request_id
1527 -- setting the message to success
1524 And worker_id = p_worker_id;
1525 END IF;
1526
1528
1529 FND_MESSAGE.Set_Name('JTF', 'CAC_TASK_WORKER_RET_STAT_SUCC');
1530 FND_MSG_PUB.Add;
1531
1532 l_msg_data:=FND_MSG_PUB.Get(
1533 p_msg_index => FND_MSG_PUB.G_LAST ,
1534 p_encoded => 'F'
1535 );
1536
1537 -- Setting the completion status of this child concurrent
1538 -- request as COMPLETED NORMALLY
1539
1540 errbuf := l_msg_data;
1541 retcode := l_cp_succ;
1542
1543 IF fnd_api.to_boolean(p_commit)
1544 THEN
1545 COMMIT WORK;
1546 END IF;
1547
1548 ----------------------------
1549 -- Procedure level Logging
1550 ----------------------------
1551 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1552 THEN
1553 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'CAC_TASK_PURGE_PVT.POPULATE_PURGE_TMP', 'errbuf = '||errbuf);
1554 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'CAC_TASK_PURGE_PVT.POPULATE_PURGE_TMP', 'retcode = '||retcode);
1555 END IF;
1556
1557 EXCEPTION
1558 WHEN fnd_api.g_exc_unexpected_error
1559 THEN
1560 ROLLBACK TO populate_purge_tmp;
1561 FND_MSG_PUB.Count_And_Get (
1562 p_count => l_msg_count,
1563 p_data => l_msg_data
1564 );
1565
1566 IF l_tab_task_id.COUNT > 0
1567 THEN
1568 Forall j In l_tab_task_id.FIRST..l_tab_task_id.LAST
1569 Update JTF_TASK_PURGE Set process_flag = 'Y' ,
1570 purge_status = 'E' ,
1571 purge_error_message = SUBSTRB(l_msg_data,1,4000)
1572 Where concurrent_request_id = p_concurrent_request_id
1573 And worker_id = p_worker_id
1574 And object_type = l_task_source_tab(j)
1575 And object_id = l_tab_task_id(j);
1576 END IF;
1577
1578 errbuf := SUBSTRB(REPLACE(l_msg_data,CHR(0),' '), 1, 240);
1579 retcode := l_cp_err;
1580
1581 ----------------------------
1582 -- Exception level Logging
1583 ----------------------------
1584 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1585 THEN
1586 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'CAC_TASK_PURGE_PVT.POPULATE_PURGE_TMP', 'fnd_api.g_exc_unexpected_error');
1587 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'CAC_TASK_PURGE_PVT.POPULATE_PURGE_TMP', 'l_msg_count = '||l_msg_count);
1588 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'CAC_TASK_PURGE_PVT.POPULATE_PURGE_TMP', 'Error message = '||REPLACE(l_msg_data,CHR(0),' '));
1589 END IF;
1590
1591 WHEN OTHERS
1592 THEN
1593 ROLLBACK TO populate_purge_tmp;
1594 FND_MESSAGE.Set_Name ('JTF', 'CAC_TASK_UNKNOWN_ERROR');
1595 FND_MESSAGE.Set_Token ('P_TEXT', G_PKG_NAME||'.'||l_api_name ||' : '||SQLCODE||' : '|| SQLERRM);
1596 FND_MSG_PUB.Add;
1597 FND_MSG_PUB.Count_And_Get (
1598 p_count => l_msg_count,
1599 p_data => l_msg_data
1600 );
1601
1602 IF l_tab_task_id.COUNT > 0
1603 THEN
1604 Forall j In l_tab_task_id.FIRST..l_tab_task_id.LAST
1605 Update JTF_TASK_PURGE Set process_flag = 'Y' ,
1606 purge_status = 'E' ,
1607 purge_error_message = SUBSTRB(l_msg_data,1,4000)
1608 Where concurrent_request_id = p_concurrent_request_id
1609 And worker_id = p_worker_id
1610 And object_type = l_task_source_tab(j)
1611 And object_id = l_tab_task_id(j);
1612 END IF;
1613
1614 errbuf := SUBSTRB(REPLACE(l_msg_data,CHR(0),' '), 1, 240);
1615 retcode := l_cp_err;
1616
1617 ----------------------------
1618 -- Exception level Logging
1619 ----------------------------
1620 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1621 THEN
1622 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'CAC_TASK_PURGE_PVT.POPULATE_PURGE_TMP', 'OTHERS error');
1623 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'CAC_TASK_PURGE_PVT.POPULATE_PURGE_TMP', 'l_msg_count = '||l_msg_count);
1624 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'CAC_TASK_PURGE_PVT.POPULATE_PURGE_TMP', 'Error message = '||REPLACE(l_msg_data,CHR(0),' '));
1625 END IF;
1626
1627 END POPULATE_PURGE_TMP;
1628
1629 Procedure PURGE_TASK_ENTITIES (
1630 p_api_version IN NUMBER ,
1631 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false ,
1632 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false ,
1633 p_processing_set_id IN NUMBER ,
1634 x_return_status OUT NOCOPY VARCHAR2 ,
1635 x_msg_data OUT NOCOPY VARCHAR2 ,
1636 x_msg_count OUT NOCOPY NUMBER ,
1637 p_object_type IN VARCHAR2 )
1638 IS
1639 l_api_version CONSTANT NUMBER := 1.0;
1640 l_api_name CONSTANT VARCHAR2(30) := 'PURGE_TASK_ENTITIES';
1641
1642 Cursor C_Task_Id Is
1643 Select object_id From JTF_OBJECT_PURGE_PARAM_TMP
1644 Where object_type = p_object_type
1645 And processing_set_id = p_processing_set_id
1646 And NVL(purge_status,'Y') <> 'E';
1647
1651 l_tab_task_ref_id t_tab_num:=t_tab_num();
1648 TYPE t_tab_num Is Table Of NUMBER;
1649
1650 l_tab_task_id t_tab_num:=t_tab_num();
1652 l_tab_task_audits_id t_tab_num:=t_tab_num();
1653 l_tab_rec_rule_id t_tab_num:=t_tab_num();
1654 --Added by MPADHIAR for Bug # 5216358
1655 l_tab_task_contact_id t_tab_num:=t_tab_num();
1656
1657 BEGIN
1658
1659 SAVEPOINT purge_task_entities;
1660
1661 x_return_status := fnd_api.g_ret_sts_success;
1662
1663 IF NOT fnd_api.compatible_api_call (
1664 l_api_version,
1665 p_api_version,
1666 l_api_name,
1667 g_pkg_name
1668 )
1669 THEN
1670 RAISE fnd_api.g_exc_unexpected_error;
1671 END IF;
1672
1673 IF fnd_api.to_boolean (p_init_msg_list)
1674 THEN
1675 fnd_msg_pub.initialize;
1676 END IF;
1677
1678
1679 ----------------------------
1680 -- Procedure level Logging
1681 ----------------------------
1682 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1683 THEN
1684 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'CAC_TASK_PURGE_PVT.PURGE_TASK_ENTITIES', 'p_processing_set_id = '||p_processing_set_id);
1685 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'CAC_TASK_PURGE_PVT.PURGE_TASK_ENTITIES', 'p_object_type = '||p_object_type);
1686 END IF;
1687
1688 ----------------------------
1689 -- Statement level Logging
1690 ----------------------------
1691 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1692 THEN
1693 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.PURGE_TASK_ENTITIES', 'Fetching record from JTF_OBJECT_PURGE_PARAM_TMP');
1694 END IF;
1695
1696
1697 Open C_Task_Id;
1698 Fetch C_Task_Id Bulk Collect Into l_tab_task_id;
1699 Close C_Task_Id;
1700
1701 IF l_tab_task_id.COUNT > 0
1702 THEN
1703
1704 ----------------------------
1705 -- Statement level Logging
1706 ----------------------------
1707 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1708 THEN
1709 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.PURGE_TASK_ENTITIES', 'Deleting from table - JTF_TASK_DATES');
1710 END IF;
1711
1712
1713 --Delete data from JTF_TASK_DATES table
1714 Forall j In l_tab_task_id.FIRST.. l_tab_task_id.LAST
1715 Delete JTF_TASK_DATES
1716 Where task_id= l_tab_task_id(j);
1717
1718
1719 ----------------------------
1720 -- Statement level Logging
1721 ----------------------------
1722 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1723 THEN
1724 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.PURGE_TASK_ENTITIES', 'Deleting from table - JTF_TASK_DEPENDS');
1725 END IF;
1726
1727
1728 --Delete data from JTF_TASK_DEPENDS table
1729 Forall j In l_tab_task_id.FIRST..l_tab_task_id.LAST
1730 Delete JTF_TASK_DEPENDS
1731 Where NVL(template_flag, 'N') <> 'Y' -- Added for bug# 5388975
1732 AND (task_id = l_tab_task_id(j)
1733 or dependent_on_task_id = l_tab_task_id(j));
1734
1735
1736 ----------------------------
1737 -- Statement level Logging
1738 ----------------------------
1739 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1740 THEN
1741 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.PURGE_TASK_ENTITIES', 'Deleting from table - JTF_TASK_CONTACTS');
1742 END IF;
1743
1744
1745 --Delete data from JTF_TASK_CONTACTS table
1746 Forall j In l_tab_task_id.FIRST..l_tab_task_id.LAST
1747 Delete JTF_TASK_CONTACTS
1748 Where task_id = l_tab_task_id(j)
1749 --Added By MPADHIAR for Bug#5216358
1750 Returning task_contact_id Bulk Collect Into l_tab_task_contact_id;
1751
1752 ----------------------------
1753 -- Statement level Logging
1754 ----------------------------
1755 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1756 THEN
1757 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.PURGE_TASK_ENTITIES', 'Deleting from table - JTF_TASK_PHONES');
1758 END IF;
1759
1760
1761 --Delete data from JTF_TASK_PHONES table
1762 Forall j In l_tab_task_id.FIRST..l_tab_task_id.LAST
1763 Delete JTF_TASK_PHONES
1764 WHERE owner_table_name = 'JTF_TASKS_B'
1765 AND task_contact_id = l_tab_task_id(j);
1766
1767 --Added By MPADHIAR for Bug#5216358
1768 IF l_tab_task_contact_id.COUNT > 0 THEN
1769 --Delete data from JTF_TASK_PHONES table for phone created for Task Contact
1770 Forall j In l_tab_task_contact_id.FIRST..l_tab_task_contact_id.LAST
1771 Delete JTF_TASK_PHONES
1772 WHERE owner_table_name = 'JTF_TASK_CONTACTS'
1773 AND task_contact_id = l_tab_task_contact_id(j);
1774 END IF;
1775 --Added By MPADHIAR for Bug#5216358 Ends here
1776
1777 ----------------------------
1778 -- Statement level Logging
1779 ----------------------------
1780 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1781 THEN
1782 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.PURGE_TASK_ENTITIES', 'Deleting from table - JTF_TASK_RSC_REQS');
1783 END IF;
1784
1785
1786 --Delete data from JTF_TASK_RSC_REQS
1790
1787 Forall j In l_tab_task_id.FIRST..l_tab_task_id.LAST
1788 Delete JTF_TASK_RSC_REQS
1789 where task_id = l_tab_task_id(j);
1791
1792 ----------------------------
1793 -- Statement level Logging
1794 ----------------------------
1795 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1796 THEN
1797 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.PURGE_TASK_ENTITIES', 'Deleting from table - JTF_TASK_REFERENCES_B');
1798 END IF;
1799
1800
1801 --Delete data from JTF_TASK_REFERENCES_B table
1802 Forall j In l_tab_task_id.FIRST..l_tab_task_id.LAST
1803 Delete JTF_TASK_REFERENCES_B
1804 Where task_id = l_tab_task_id(j)
1805 Returning task_reference_id Bulk Collect Into l_tab_task_ref_id;
1806
1807
1808 --Delete data from JTF_TASK_REFERENCES_TL table
1809 IF l_tab_task_ref_id.COUNT > 0
1810 THEN
1811
1812 ----------------------------
1813 -- Statement level Logging
1814 ----------------------------
1815 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1816 THEN
1817 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.PURGE_TASK_ENTITIES', 'Deleting from table - JTF_TASK_REFERENCES_TL');
1818 END IF;
1819
1820
1821 Forall i In l_tab_task_ref_id.FIRST..l_tab_task_ref_id.LAST
1822 Delete JTF_TASK_REFERENCES_TL
1823 Where task_reference_id = l_tab_task_ref_id(i);
1824
1825 END IF;
1826
1827 ----------------------------
1828 -- Statement level Logging
1829 ----------------------------
1830 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1831 THEN
1832 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.PURGE_TASK_ENTITIES', 'Deleting from table - JTF_TASK_AUDITS_B');
1833 END IF;
1834
1835
1836 --Delete data from JTF_TASK_AUDITS_B table
1837 Forall j In l_tab_task_id.FIRST..l_tab_task_id.LAST
1838 Delete JTF_TASK_AUDITS_B
1839 Where task_id = l_tab_task_id(j)
1840 Returning task_audit_id Bulk Collect Into l_tab_task_audits_id;
1841
1842 --Delete data from JTF_TASK_AUDITS_TL table
1843 IF l_tab_task_audits_id.COUNT > 0
1844 THEN
1845
1846 ----------------------------
1847 -- Statement level Logging
1848 ----------------------------
1849 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1850 THEN
1851 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.PURGE_TASK_ENTITIES', 'Deleting from table - JTF_TASK_AUDITS_TL');
1852 END IF;
1853
1854
1855 Forall i In l_tab_task_audits_id.FIRST..l_tab_task_audits_id.LAST
1856 Delete JTF_TASK_AUDITS_TL
1857 Where task_audit_id = l_tab_task_audits_id(i);
1858
1859 END IF;
1860
1861
1862 --Modified By MPADHIAR for Bug#5216358
1863 ----------------------------
1864 -- Statement level Logging
1865 ----------------------------
1866 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1867 THEN
1868 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.PURGE_TASK_ENTITIES', 'Deleting from table - JTF_TASK_ALL_ASSIGNMENTS');
1869 END IF;
1870
1871 --Delete data from JTF_TASK_ALL_ASSIGNMENTS table
1872 Forall j In l_tab_task_id.FIRST..l_tab_task_id.LAST
1873 Delete JTF_TASK_ALL_ASSIGNMENTS
1874 Where task_id = l_tab_task_id(j);
1875
1876 --Modified By MPADHIAR for Bug#5216358 Ends here
1877
1878 ----------------------------
1879 -- Statement level Logging
1880 ----------------------------
1881 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1882 THEN
1883 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.PURGE_TASK_ENTITIES', 'Breaking parent-child relationship for the tasks to be purged in JTF_TASKS_B');
1884 END IF;
1885
1886
1887 --Break parent-child relationship for the tasks to be purged
1888 Forall j In l_tab_task_id.FIRST..l_tab_task_id.LAST
1889 Update JTF_TASKS_B
1890 Set parent_task_id = NULL
1891 Where parent_task_id = l_tab_task_id(j);
1892
1893
1894 ----------------------------
1895 -- Statement level Logging
1896 ----------------------------
1897 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1898 THEN
1899 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.PURGE_TASK_ENTITIES', 'Deleting from table - JTF_TASKS_B');
1900 END IF;
1901
1902
1903 --Delete data from JTF_TASKS_B table
1904 Forall j In l_tab_task_id.FIRST..l_tab_task_id.LAST
1905 Delete JTF_TASKS_B
1906 Where task_id = l_tab_task_id(j)
1907 Returning recurrence_rule_id Bulk Collect Into l_tab_rec_rule_id;
1908
1909
1910 ----------------------------
1911 -- Statement level Logging
1912 ----------------------------
1913 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1914 THEN
1915 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.PURGE_TASK_ENTITIES', 'Deleting from table - JTF_TASKS_TL');
1916 END IF;
1917
1918
1919 --Delete data from JTF_TASKS_TL table
1920 Forall j In l_tab_task_id.FIRST..l_tab_task_id.LAST
1924
1921 Delete JTF_TASKS_TL
1922 Where task_id = l_tab_task_id(j);
1923
1925 --Delete data from JTF_TASK_RECUR_RULES table
1926 IF l_tab_rec_rule_id.COUNT > 0
1927 THEN
1928
1929 ----------------------------
1930 -- Statement level Logging
1931 ----------------------------
1932 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1933 THEN
1934 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'CAC_TASK_PURGE_PVT.PURGE_TASK_ENTITIES', 'Deleting from table - JTF_TASK_RECUR_RULES');
1935 END IF;
1936
1937
1938 Forall j In l_tab_rec_rule_id.FIRST..l_tab_rec_rule_id.LAST
1939 Delete JTF_TASK_RECUR_RULES
1940 Where recurrence_rule_id = l_tab_rec_rule_id(j)
1941 And l_tab_rec_rule_id(j) IS NOT NULL
1942 And NOT EXISTS (Select task_id From JTF_TASKS_B
1943 Where recurrence_rule_id = l_tab_rec_rule_id(j));
1944
1945 END IF;
1946
1947 END IF;
1948
1949 IF fnd_api.to_boolean(p_commit)
1950 THEN
1951 COMMIT WORK;
1952 END IF;
1953
1954 ----------------------------
1955 -- Procedure level Logging
1956 ----------------------------
1957 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1958 THEN
1959 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'CAC_TASK_PURGE_PVT.PURGE_TASK_ENTITIES', 'End of PURGE_TASK_ENTITIES');
1960 END IF;
1961
1962 EXCEPTION
1963 WHEN fnd_api.g_exc_unexpected_error
1964 THEN
1965 ROLLBACK TO purge_task_entities;
1966 x_return_status := fnd_api.g_ret_sts_unexp_error;
1967 FND_MSG_PUB.Count_And_Get (
1968 p_count => x_msg_count,
1969 p_data => x_msg_data
1970 );
1971 IF l_tab_task_id.COUNT > 0
1972 THEN
1973 FORALL j IN l_tab_task_id.FIRST..l_tab_task_id.LAST
1974 Update JTF_OBJECT_PURGE_PARAM_TMP Set purge_status='E', purge_error_message=SUBSTRB(x_msg_data,1,4000)
1975 Where object_type = p_object_type
1976 And processing_set_id = p_processing_set_id
1977 And object_id = l_tab_task_id(j);
1978 END IF;
1979
1980 ----------------------------
1981 -- Exception level Logging
1982 ----------------------------
1983 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1984 THEN
1985 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'CAC_TASK_PURGE_PVT.PURGE_TASK_ENTITIES', 'fnd_api.g_exc_unexpected_error');
1986 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'CAC_TASK_PURGE_PVT.PURGE_TASK_ENTITIES', 'x_msg_count = '||x_msg_count);
1987 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'CAC_TASK_PURGE_PVT.PURGE_TASK_ENTITIES', 'Error message = '||REPLACE(x_msg_data,CHR(0),' '));
1988 END IF;
1989
1990 WHEN OTHERS
1991 THEN
1992 ROLLBACK TO purge_task_entities;
1993 FND_MESSAGE.Set_Name ('JTF', 'CAC_TASK_UNKNOWN_ERROR');
1994 FND_MESSAGE.Set_Token ('P_TEXT', G_PKG_NAME||'.'||l_api_name ||' : '||SQLCODE||' : '|| SQLERRM);
1995 FND_MSG_PUB.Add;
1996 x_return_status := fnd_api.g_ret_sts_unexp_error;
1997 FND_MSG_PUB.Count_And_Get (
1998 p_count => x_msg_count,
1999 p_data => x_msg_data
2000 );
2001
2002 IF l_tab_task_id.COUNT > 0
2003 THEN
2004 FORALL j IN l_tab_task_id.FIRST..l_tab_task_id.LAST
2005 Update JTF_OBJECT_PURGE_PARAM_TMP Set purge_status='E', purge_error_message=SUBSTRB(x_msg_data,1,4000)
2006 Where object_type = p_object_type
2007 And processing_set_id = p_processing_set_id
2008 And object_id = l_tab_task_id(j);
2009 END IF;
2010
2011 ----------------------------
2012 -- Exception level Logging
2013 ----------------------------
2014 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
2015 THEN
2016 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'CAC_TASK_PURGE_PVT.PURGE_TASK_ENTITIES', 'OTHERS error');
2017 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'CAC_TASK_PURGE_PVT.PURGE_TASK_ENTITIES', 'x_msg_count = '||x_msg_count);
2018 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'CAC_TASK_PURGE_PVT.PURGE_TASK_ENTITIES', 'Error message = '||REPLACE(x_msg_data,CHR(0),' '));
2019 END IF;
2020
2021 END PURGE_TASK_ENTITIES;
2022
2023 END CAC_TASK_PURGE_PVT;