[Home] [Help]
PACKAGE BODY: APPS.CAC_TASK_PURGE_PUB
Source
1 PACKAGE BODY cac_task_purge_pub AS
2 /* $Header: cactkprb.pls 120.17 2006/02/03 03:47:40 sbarat noship $ */
3 /*=======================================================================+
4 | Copyright (c) 2002 Oracle Corporation Redwood Shores, California, USA|
5 | All rights reserved. |
6 +=======================================================================+
7 | FILENAME |
8 | cactkprb.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 | 07--2005 Rahul Shrivastava Created |
19 | 03-Feb-2006 Swapan Barat Added calls to UWQ and IH's purge |
20 | APIs for bug# 4997851 |
21 +======================================================================*/
22
23
24 procedure delete_atth_to_tasks(
25 x_return_status OUT NOCOPY VARCHAR2,
26 x_msg_data OUT NOCOPY VARCHAR2,
27 x_msg_count OUT NOCOPY NUMBER,
28 p_object_type IN VARCHAR2,
29 p_processing_set_id IN NUMBER)
30
31 is
32
33
34
35 cursor c_fetch_task_ids is
36 select b.task_id
37 from jtf_tasks_b b, fnd_attached_documents fad
38 where b.source_object_type_code = p_object_type
39 and b.source_object_id in ( select object_id
40 from jtf_object_purge_param_tmp
41 where processing_set_id = p_processing_set_id
42 and ( purge_status is null or purge_status <> 'E'))
43 and fad.entity_name='JTF_TASKS_B'
44 and fad.pk1_value=to_char(b.task_id);
45
46
47 TYPE t_tab_num Is Table Of NUMBER;
48 l_tab_task_ids t_tab_num:=t_tab_num();
49 l_entity_name VARCHAR2(30) := 'JTF_TASKS_B';
50 l_api_version CONSTANT NUMBER := 1.0;
51 l_api_name CONSTANT VARCHAR2(30) := 'delete_atth_to_tasks';
52 Begin
53
54
55 SAVEPOINT purge_task_attach;
56 x_return_status := fnd_api.g_ret_sts_success;
57
58
59
60 Open c_fetch_task_ids;
61 Fetch c_fetch_task_ids Bulk Collect Into l_tab_task_ids;
62 Close c_fetch_task_ids;
63
64
65 IF l_tab_task_ids.COUNT > 0
66 THEN
67
68 -- Calling delete attachment API
69 For j In 1.. l_tab_task_ids.LAST loop
70
71 fnd_attached_documents2_pkg.delete_attachments(X_entity_name=>l_entity_name,
72 X_pk1_value =>to_char(l_tab_task_ids(j)),
73 X_pk2_value => NULL,
74 X_pk3_value => NULL,
75 X_pk4_value => NULL,
76 X_pk5_value => NULL,
77 X_delete_document_flag =>'Y',
78 X_automatically_added_flag => NULL) ;
79
80 end loop;
81
82
83 END IF;--for IF l_tab_task_ids.COUNT > 0
84
85 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
86
87 EXCEPTION
88 WHEN fnd_api.g_exc_unexpected_error
89 THEN
90 ROLLBACK TO purge_task_attach;
91 x_return_status := fnd_api.g_ret_sts_unexp_error;
92 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
93 if( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
94 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'cac_task_purge_pub.delete_atth_to_tasks', ' x_return_status= '||x_return_status);
95 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'cac_task_purge_pub.delete_atth_to_tasks', ' x_msg_data= '||x_msg_data);
96 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'cac_task_purge_pub.delete_atth_to_tasks', ' x_msg_count= '||x_msg_count);
97 end if;
98
99 WHEN OTHERS
100 THEN
101 ROLLBACK TO purge_task_attach;
102 fnd_message.set_name ('JTF', 'JTF_ATTACHMENT_PURGE_EXCEP');
103 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
104 fnd_msg_pub.add;
105 if( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
106 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'cac_task_purge_pub.delete_atth_to_tasks', ' x_return_status= '||x_return_status);
107 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'cac_task_purge_pub.delete_atth_to_tasks', ' x_msg_data= '||x_msg_data);
108 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'cac_task_purge_pub.delete_atth_to_tasks', ' x_msg_count= '||x_msg_count);
109 end if;
110
111 x_return_status := fnd_api.g_ret_sts_unexp_error;
112 fnd_msg_pub.count_and_get (
113 p_count => x_msg_count,
114 p_data => x_msg_data
115 );
116
117 end delete_atth_to_tasks;
118
119 Procedure purge_tasks(
120 p_api_version IN NUMBER,
121 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
122 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
123 x_return_status OUT NOCOPY VARCHAR2,
124 x_msg_data OUT NOCOPY VARCHAR2,
125 x_msg_count OUT NOCOPY NUMBER,
126 p_object_type IN VARCHAR2,
127 p_processing_set_id IN NUMBER)
128
129 IS
130 l_api_version CONSTANT NUMBER := 1.0;
131 l_api_name CONSTANT VARCHAR2(30) := 'PURGE_TASKS';
132
133 Cursor get_tasks_ids(b_processing_set_id NUMBER,b_object_type VARCHAR2)
134 is
135
136 Select task_id
137 from jtf_tasks_b
138 Where source_object_type_code=b_object_type
139 And source_object_id in
140 (select distinct object_id from jtf_object_purge_param_tmp
141 where processing_set_id=b_processing_set_id and
142 purge_status is null and object_type=b_object_type)
143 and entity='TASK';
144
145 TYPE tab_task_ids is table of NUMBER;
146 l_tab_tasks_id tab_task_ids:=tab_task_ids();
147 proc_seq_num NUMBER;
148 l_msg_index_out VARCHAR2(100);
149 BEGIN
150
151 SAVEPOINT purge_tasks;
152 x_return_status := fnd_api.g_ret_sts_success;
153
154 IF NOT fnd_api.compatible_api_call (
155 l_api_version,
156 p_api_version,
157 l_api_name,
158 g_pkg_name
159 )
160 THEN
161 RAISE fnd_api.g_exc_unexpected_error;
162 END IF;
163
164 IF fnd_api.to_boolean (p_init_msg_list)
165 THEN
166 fnd_msg_pub.initialize;
167 END IF;
168 --Logging input parameters
169
170 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
171 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'cac_task_purge_pub.purge_tasks', ' p_object_type= '||p_object_type);
172 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'cac_task_purge_pub.purge_tasks', ' p_processing_set_id= '||p_processing_set_id);
173 end if;
174
175
176 Open get_tasks_ids(p_processing_set_id ,p_object_type );
177 Fetch get_tasks_ids Bulk Collect Into l_tab_tasks_id;
178
179 if (get_tasks_ids%ISOPEN) then
180 Close get_tasks_ids;
181 END IF;
182
183 If ( l_tab_tasks_id.COUNT > 0) then
184
185 --inserting data into the global temp table
186 select jtf_object_purge_proc_set_s.nextval into proc_seq_num from dual;
187
188
189
190 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
191 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'cac_task_purge_pub.purge_tasks', 'Inserting task data into global tem table');
192 end if;
193
194 FORALL i in l_tab_tasks_id.first..l_tab_tasks_id.last
195
196 Insert into jtf_object_purge_param_tmp p
197 ( processing_set_id,object_id,object_type,purge_status,purge_error_message)
198 values
199 (proc_seq_num, l_tab_tasks_id(i),'TASK', null,null);
200
201
202
203 --Calling the purge APIs for notes , UWQ, Mobile Field Service, Attachments
204 -- and interaction center to purge any references
205
206 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
207 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'cac_task_purge_pub.purge_tasks', 'Before calling CSM_TASK_PURGE_PKG.DELETE_MFS_TASKS');
208 end if;
209
210 CSM_TASK_PURGE_PKG.DELETE_MFS_TASKS(
211 P_API_VERSION => 1.0,
212 P_INIT_MSG_LIST => FND_API.G_FALSE,
213 P_COMMIT => FND_API.G_FALSE,
214 P_PROCESSING_SET_ID => proc_seq_num ,
215 P_OBJECT_TYPE => 'TASK' ,
216 X_RETURN_STATUS => x_return_status,
217 X_MSG_COUNT => x_msg_count,
218 X_MSG_DATA => x_msg_data);
219
220 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
221 THEN
222 x_return_status := fnd_api.g_ret_sts_unexp_error;
223
224 if( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
225 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');
226 end if;
227 RAISE fnd_api.g_exc_unexpected_error;
228 end if;
229
230 /************* Start of addition by SBARAT on 03/02/2006 for bug# 4997851 *************/
231
232 -- Calling UWQ's purge API
233
234 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
235 THEN
236 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'cac_task_purge_pub.purge_tasks', 'Before calling IEU_WR_PUB.PURGE_WR_ITEM');
237 END IF;
238
239 IEU_WR_PUB.Purge_Wr_Item(
240 p_api_version_number => 1.0,
241 p_init_msg_list => FND_API.G_FALSE,
242 p_commit => FND_API.G_FALSE,
243 p_processing_set_id => proc_seq_num,
244 p_object_type => 'TASK',
245 x_return_status => x_return_status,
246 x_msg_count => x_msg_count,
247 x_msg_data => x_msg_data);
248
249 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
250 THEN
251 x_return_status := fnd_api.g_ret_sts_unexp_error;
252
253 IF( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
254 THEN
255 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'cac_task_purge_pub.purge_tasks', 'return status error after calling IEU_WR_PUB.PURGE_WR_ITEM');
256 END IF;
257
258 RAISE fnd_api.g_exc_unexpected_error;
259
260 END IF;
261
262
263 -- Calling Interaction History's purge API
264
265 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
266 THEN
267 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'cac_task_purge_pub.purge_tasks', 'Before calling JTF_IH_PURGE.P_DELETE_INTERACTIONS');
268 END IF;
269
270 JTF_IH_PURGE.P_Delete_Interactions(
271 p_api_version => 1.0,
272 p_init_msg_list => FND_API.G_FALSE,
273 p_commit => FND_API.G_FALSE,
274 p_processing_set_id => proc_seq_num,
275 p_object_type => 'TASK',
276 x_return_status => x_return_status,
277 x_msg_count => x_msg_count,
278 x_msg_data => x_msg_data);
279
280 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
281 THEN
282 x_return_status := fnd_api.g_ret_sts_unexp_error;
283
284 IF( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
285 THEN
286 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'cac_task_purge_pub.purge_tasks', 'return status error after calling JTF_IH_PURGE.P_DELETE_INTERACTIONS');
287 END IF;
288
289 RAISE fnd_api.g_exc_unexpected_error;
290
291 END IF;
292
293 /************* End of addition by SBARAT on 03/02/2006 for bug# 4997851 *************/
294
295 --calling attachment deletion wrapper api.
296 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
297 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'cac_task_purge_pub.purge_tasks', 'Before calling cac_task_purge_pub.delete_atth_to_tasks');
298 end if;
299
300
301 delete_atth_to_tasks(
302 p_processing_set_id=>proc_seq_num,
303 p_object_type =>'TASK',
304 x_return_status => x_return_status,
305 x_msg_count => x_msg_count,
306 x_msg_data => x_msg_data);
307
308 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
309
310 THEN
311
312 x_return_status := fnd_api.g_ret_sts_unexp_error;
313
314
315 if( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
316
317 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');
318 end if;
319 RAISE fnd_api.g_exc_unexpected_error;
320 end if;
321
322
323 --Calling notes api
324 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
325 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'cac_task_purge_pub.purge_tasks', 'Before calling cac_note_purge_pub.purge_notes');
326 end if;
327
328
329
330 cac_note_purge_pub.purge_notes(
331 p_api_version => 1.0,
332 x_return_status => x_return_status,
333 x_msg_count => x_msg_count,
334 x_msg_data => x_msg_data,
335 p_processing_set_id => proc_seq_num,
336 p_object_type => 'TASK' );
337
338 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
339 THEN
340 x_return_status := fnd_api.g_ret_sts_unexp_error;
341
342 if( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
343 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'cac_task_purge_pub.purge_tasks', 'return status error after calling cac_note_purge_pub.purge_notes');
344 end if;
345 RAISE fnd_api.g_exc_unexpected_error;
346 end if;
347
348
349 --calling the cac_task_purge_pvt.purge_task_entities API
350
351 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
352 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'cac_task_purge_pub.purge_tasks', 'Before calling cac_task_purge_pvt.purge_task_entities');
353 end if;
354
355 cac_task_purge_pvt.purge_task_entities(
356 p_api_version => 1.0,
357 p_init_msg_list => fnd_api.g_false,
358 p_commit => fnd_api.g_false,
359 x_return_status => x_return_status,
360 x_msg_data => x_msg_data,
361 x_msg_count => x_msg_count,
362 p_processing_set_id => proc_seq_num,
363 p_object_type => 'TASK'
364 );
365
366
367
368 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
369 THEN
370 x_return_status := fnd_api.g_ret_sts_unexp_error;
371
372 if( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
373 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'cac_task_purge_pub.purge_tasks', ' return status error after calling cac_task_purge_pvt.purge_task_entities');
374 end if;
375
376 RAISE fnd_api.g_exc_unexpected_error;
377 END IF;
378
379
380 else --no task data exists for the selected service requests
381 --else for If ( get_task_processing_set_id.COUNT > 0) then
382
383 -- dbms_output.put_line(' point 5');
384
385 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
386 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'cac_task_purge_pub.purge_tasks', 'no task data exists for the given processing set id ');
387 end if;
388
389 end if;-- for If ( l_tab_tasks_id.COUNT > 0)
390
391
392 IF fnd_api.to_boolean (p_commit)
393 THEN
394 COMMIT WORK;
395 END IF;
396
397 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
398
399
400 EXCEPTION
401 WHEN fnd_api.g_exc_unexpected_error
402 THEN
403 ROLLBACK TO purge_tasks;
404 x_return_status := fnd_api.g_ret_sts_unexp_error;
405 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
406 if (get_tasks_ids%ISOPEN) then
407 Close get_tasks_ids;
408 END IF;
409 if( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
410 IF
411 FND_MSG_PUB.Count_Msg > 0
412 THEN
413 FOR
414 i IN 1..FND_MSG_PUB.Count_Msg
415 LOOP
416 FND_MSG_PUB.Get
417 (
418 p_msg_index => i
419 , p_encoded => 'F'
420 , p_data => x_msg_data
421 , p_msg_index_out => l_msg_index_out
422 );
423 fnd_log.string
424 (
425 fnd_log.level_exception
426 , 'Purge Test'
427 , 'Error is ' || x_msg_data || ' [Index:' || l_msg_index_out || ']'
428 );
429 END LOOP;
430 END IF ;
431 END IF;
432
433 WHEN OTHERS
434 THEN
435 ROLLBACK TO purge_tasks;
436 fnd_message.set_name ('JTF', 'CAC_TASK_UNKNOWN_ERROR');
437 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
438 fnd_msg_pub.add;
439 x_return_status := fnd_api.g_ret_sts_unexp_error;
440 if (get_tasks_ids%ISOPEN) then
441 Close get_tasks_ids;
442 END IF;
443 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
444 if( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
445 IF
446 FND_MSG_PUB.Count_Msg > 0
447 THEN
448 FOR
449 i IN 1..FND_MSG_PUB.Count_Msg
450 LOOP
451 FND_MSG_PUB.Get
452 (
453 p_msg_index => i
454 , p_encoded => 'F'
455 , p_data => x_msg_data
456 , p_msg_index_out => l_msg_index_out
457 );
458 fnd_log.string
459 (
460 fnd_log.level_exception
461 , 'Purge Test'
462 , 'Error is ' || x_msg_data || ' [Index:' || l_msg_index_out || ']'
463 );
464 END LOOP;
465 END IF ;
466 END IF;
467
468 END purge_tasks;
469
470
471
472 Procedure validate_tasks(
473 p_api_version IN NUMBER,
474 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
475 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
476 x_return_status OUT NOCOPY VARCHAR2,
477 x_msg_data OUT NOCOPY VARCHAR2,
478 x_msg_count OUT NOCOPY NUMBER,
479 p_object_type IN VARCHAR2,
480 p_processing_set_id IN NUMBER,
481 p_purge_source_with_open_task IN VARCHAR2 DEFAULT 'N')
482 IS
483 l_api_version CONSTANT NUMBER := 1.0;
484 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_TASKS';
485
486 Cursor tasks_ids(b_processing_set_id NUMBER,b_object_type VARCHAR2)
487 is
488
489 Select task_id
490 from jtf_tasks_b
491 Where source_object_type_code=b_object_type
492 And source_object_id in
493 (select distinct object_id from jtf_object_purge_param_tmp
494 where processing_set_id=b_processing_set_id and
495 purge_status is null and object_type=b_object_type)
496 and entity='TASK';
497
498
499 TYPE tab_task_ids is table of NUMBER;
500 l_tab_tasks_id tab_task_ids:=tab_task_ids();
501 proc_seq_num NUMBER;
502 l_msg_index_out VARCHAR2(100);
503 BEGIN
504 SAVEPOINT validate_tasks;
505 x_return_status := fnd_api.g_ret_sts_success;
506
507 IF NOT fnd_api.compatible_api_call (
508 l_api_version,
509 p_api_version,
510 l_api_name,
511 g_pkg_name
512 )
513 THEN
514 RAISE fnd_api.g_exc_unexpected_error;
515 END IF;
516
517 IF fnd_api.to_boolean (p_init_msg_list)
518 THEN
519 fnd_msg_pub.initialize;
520 END IF;
521 --Logging input parameters
522
523 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
524 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'cac_task_purge_pub.validate_tasks', ' p_object_type= '||p_object_type);
525 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'cac_task_purge_pub.validate_tasks', ' p_processing_set_id= '||p_processing_set_id);
526 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'cac_task_purge_pub.validate_tasks', ' p_purge_source_with_open_task= '||p_purge_source_with_open_task);
527 end if;
528
529
530 --calling validations for tasks
531
532 if (p_purge_source_with_open_task='N') then
533
534 Update jtf_object_purge_param_tmp joppt
535 Set joppt.PURGE_STATUS='E', joppt.PURGE_ERROR_MESSAGE='JTF:JTF_TASK_PURGE_VALID_FAIL'
536 where joppt.processing_set_id=p_processing_set_id
537 and joppt.object_type=p_object_type
538 and exists ( select 1 from jtf_tasks_b where
539 source_object_type_code=joppt.object_type
540 and nvl(open_flag,'Y')='Y'
541 and source_object_id=joppt.object_id
542 and entity='TASK');
543
544
545 end if;
546
547
548 Open tasks_ids(p_processing_set_id ,p_object_type );
549 Fetch tasks_ids Bulk Collect Into l_tab_tasks_id;
550
551 if (tasks_ids%ISOPEN) then
552 Close tasks_ids;
553 END IF;
554
555 If ( l_tab_tasks_id.COUNT > 0) then
556
557 select jtf_object_purge_proc_set_s.nextval into proc_seq_num from dual;
558
559 FORALL i in l_tab_tasks_id.first..l_tab_tasks_id.last
560
561 Insert into jtf_object_purge_param_tmp p
562 ( processing_set_id,object_id,object_type,purge_status,purge_error_message)
563 values
564 (proc_seq_num, l_tab_tasks_id(i),'TASK', null,null);
565
566 --call MFS validation API
567
568 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
569 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'cac_task_purge_pub.validate_tasks', 'Before calling CSM_TASK_PURGE_PKG.VALIDATE_MFS_TASKS');
570 end if;
571
572 CSM_TASK_PURGE_PKG.VALIDATE_MFS_TASKS(
573 P_API_VERSION => 1.0,
574 P_INIT_MSG_LIST => FND_API.G_FALSE,
575 P_COMMIT => FND_API.G_FALSE,
576 P_PROCESSING_SET_ID => proc_seq_num ,
577 P_OBJECT_TYPE => 'TASK' ,
578 X_RETURN_STATUS => x_return_status,
579 X_MSG_COUNT => x_msg_count,
580 X_MSG_DATA => x_msg_data);
581
582 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
583 THEN
584 x_return_status := fnd_api.g_ret_sts_unexp_error;
585
586 if( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
587 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'cac_task_purge_pub.purge_tasks', 'return status error after calling CSM_TASK_PURGE_PKG.VALIDATE_MFS_TASKS');
588 end if;
589 RAISE fnd_api.g_exc_unexpected_error;
590 end if;
591
592
593 --bulk update the rows with error status E for which tasks validations have failed.
594
595 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
596 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'cac_task_purge_pub.validate_tasks', ' before updating jtf_object_purge_param_tmp');
597 end if;
598
599 Update jtf_object_purge_param_tmp temp
600 Set temp.purge_status='E',
601 temp.purge_error_message=(select purge_error_message from jtf_object_purge_param_tmp
602 where processing_set_id=proc_seq_num
603 and object_type='TASK'
604 and object_id in (select task_id from jtf_tasks_b
605 where source_object_id=temp.object_id
606 and source_object_type_code=temp.object_type and
607 entity='TASK') and
608 purge_status is not null and rownum =1)
609 Where temp.processing_set_id=p_processing_set_id
610 and temp.object_type=p_object_type
611 and temp.object_id in
612
613 ( select distinct b.source_object_id from jtf_tasks_b b,
614 jtf_object_purge_param_tmp temp where temp.object_id=b.task_id and
615 temp.processing_set_id=proc_seq_num and temp.object_type='TASK' and temp.purge_status is not null
616 and b.entity='TASK');
617
618
619 END IF;-- for If ( l_tab_tasks_id.COUNT > 0) then
620
621 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
622 THEN
623 x_return_status := fnd_api.g_ret_sts_unexp_error;
624 RAISE fnd_api.g_exc_unexpected_error;
625 END IF;
626
627 IF fnd_api.to_boolean (p_commit)
628 THEN
629 COMMIT WORK;
630 END IF;
631
632 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
633
634 EXCEPTION
635 WHEN fnd_api.g_exc_unexpected_error
636 THEN
637 ROLLBACK TO validate_tasks;
638 if (tasks_ids%ISOPEN) then
639 CLOSE tasks_ids;
640 end if;
641 x_return_status := fnd_api.g_ret_sts_unexp_error;
642 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
643
644
645 if( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
646 IF
647 FND_MSG_PUB.Count_Msg > 0
648 THEN
649 FOR
650 i IN 1..FND_MSG_PUB.Count_Msg
651 LOOP
652 FND_MSG_PUB.Get
653 (
654 p_msg_index => i
655 , p_encoded => 'F'
656 , p_data => x_msg_data
657 , p_msg_index_out => l_msg_index_out
658 );
659 fnd_log.string
660 (
661 fnd_log.level_exception
662 , 'Purge Test'
663 , 'Error is ' || x_msg_data || ' [Index:' || l_msg_index_out || ']'
664 );
665 END LOOP;
666 END IF ;
667 END IF;
668
669 WHEN OTHERS
670 THEN
671 ROLLBACK TO validate_tasks;
672 if (tasks_ids%ISOPEN) then
673 CLOSE tasks_ids;
674 end if;
675 fnd_message.set_name ('JTF', 'JTF_TASK_VALID_UNKNOWN_ERR');
676 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
677 fnd_msg_pub.add;
678 x_return_status := fnd_api.g_ret_sts_unexp_error;
679
680 if( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
681 IF FND_MSG_PUB.Count_Msg > 0
682 THEN
683 FOR
684 i IN 1..FND_MSG_PUB.Count_Msg
685 LOOP
686 FND_MSG_PUB.Get
687 (
688 p_msg_index => i
689 , p_encoded => 'F'
690 , p_data => x_msg_data
691 , p_msg_index_out => l_msg_index_out
692 );
693
694
695 fnd_log.string
696 (
697 fnd_log.level_exception
698 , 'Purge Test'
699 , 'Error is ' || x_msg_data || ' [Index:' || l_msg_index_out || ']'
700 );
701 END LOOP;
702 END IF ;
703
704 END IF;
705
706 END validate_tasks;
707
708 END CAC_TASK_PURGE_PUB;