[Home] [Help]
PACKAGE BODY: APPS.CAC_TASK_PURGE_PUB
Source
1 PACKAGE BODY cac_task_purge_pub AS
2 /* $Header: cactkprb.pls 120.20 2012/01/11 06:55:09 anangupt ship $ */
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 Cursor get_escalation_tasks_ids(b_processing_set_id_sr NUMBER,b_processing_set_id_task NUMBER,b_object_type VARCHAR2)
146 is
147 Select task_id
148 from jtf_task_references_b
149 Where object_id in
150 (select distinct object_id from jtf_object_purge_param_tmp
151 where (processing_set_id=b_processing_set_id_sr
152 and object_type=b_object_type)
153 or (processing_set_id = b_processing_set_id_task
154 and object_type='TASK')
155 and purge_status is null);
156
157
158 TYPE tab_task_ids is table of NUMBER;
159 l_tab_tasks_id tab_task_ids:=tab_task_ids();
160 l_tab_esc_tasks_id tab_task_ids:=tab_task_ids();
161 proc_seq_num NUMBER;
162 l_msg_index_out VARCHAR2(100);
163 BEGIN
164
165 SAVEPOINT purge_tasks;
166 x_return_status := fnd_api.g_ret_sts_success;
167
168 IF NOT fnd_api.compatible_api_call (
169 l_api_version,
170 p_api_version,
171 l_api_name,
172 g_pkg_name
173 )
174 THEN
175 RAISE fnd_api.g_exc_unexpected_error;
176 END IF;
177
178 IF fnd_api.to_boolean (p_init_msg_list)
179 THEN
180 fnd_msg_pub.initialize;
181 END IF;
182 --Logging input parameters
183
184 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
185 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'cac_task_purge_pub.purge_tasks', ' p_object_type= '||p_object_type);
186 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'cac_task_purge_pub.purge_tasks', ' p_processing_set_id= '||p_processing_set_id);
187 end if;
188
189
190 Open get_tasks_ids(p_processing_set_id ,p_object_type );
191 Fetch get_tasks_ids Bulk Collect Into l_tab_tasks_id;
192
193 if (get_tasks_ids%ISOPEN) then
194 Close get_tasks_ids;
195 END IF;
196
197 If ( l_tab_tasks_id.COUNT > 0) then
198
199 --inserting data into the global temp table
200 select jtf_object_purge_proc_set_s.nextval into proc_seq_num from dual;
201
202
203
204 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
205 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'cac_task_purge_pub.purge_tasks', 'Inserting task data into global tem table');
206 end if;
207
208 FORALL i in l_tab_tasks_id.first..l_tab_tasks_id.last
209
210 Insert into jtf_object_purge_param_tmp p
211 ( processing_set_id,object_id,object_type,purge_status,purge_error_message)
212 values
213 (proc_seq_num, l_tab_tasks_id(i),'TASK', null,null);
214
215 Open get_escalation_tasks_ids(p_processing_set_id ,proc_seq_num, p_object_type );
216 Fetch get_escalation_tasks_ids Bulk Collect Into l_tab_esc_tasks_id;
217
218 IF (get_escalation_tasks_ids%ISOPEN) then
219 Close get_escalation_tasks_ids;
220 END IF;
221
222 FORALL i in l_tab_esc_tasks_id.first..l_tab_esc_tasks_id.last
223 Insert into jtf_object_purge_param_tmp p
224 ( processing_set_id,object_id,object_type,purge_status,purge_error_message)
225 values
226 (proc_seq_num, l_tab_esc_tasks_id(i),'TASK', null,null);
227
228 --Calling the purge APIs for notes , UWQ, Mobile Field Service, Attachments
229 -- and interaction center to purge any references
230
231 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
232 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'cac_task_purge_pub.purge_tasks', 'Before calling CSM_TASK_PURGE_PKG.DELETE_MFS_TASKS');
233 end if;
234
235 CSM_TASK_PURGE_PKG.DELETE_MFS_TASKS(
236 P_API_VERSION => 1.0,
237 P_INIT_MSG_LIST => FND_API.G_FALSE,
238 P_COMMIT => FND_API.G_FALSE,
239 P_PROCESSING_SET_ID => proc_seq_num ,
240 P_OBJECT_TYPE => 'TASK' ,
241 X_RETURN_STATUS => x_return_status,
242 X_MSG_COUNT => x_msg_count,
243 X_MSG_DATA => x_msg_data);
244
245 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
246 THEN
247 x_return_status := fnd_api.g_ret_sts_unexp_error;
248
249 if( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
250 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');
251 end if;
252 RAISE fnd_api.g_exc_unexpected_error;
253 end if;
254
255 /************* Start of addition by SBARAT on 03/02/2006 for bug# 4997851 *************/
256
257 -- Calling UWQ's purge API
258
259 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
260 THEN
261 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'cac_task_purge_pub.purge_tasks', 'Before calling IEU_WR_PUB.PURGE_WR_ITEM');
262 END IF;
263
264 IEU_WR_PUB.Purge_Wr_Item(
265 p_api_version_number => 1.0,
266 p_init_msg_list => FND_API.G_FALSE,
267 p_commit => FND_API.G_FALSE,
268 p_processing_set_id => proc_seq_num,
269 p_object_type => 'TASK',
270 x_return_status => x_return_status,
271 x_msg_count => x_msg_count,
272 x_msg_data => x_msg_data);
273
274 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
275 THEN
276 x_return_status := fnd_api.g_ret_sts_unexp_error;
277
278 IF( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
279 THEN
280 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'cac_task_purge_pub.purge_tasks', 'return status error after calling IEU_WR_PUB.PURGE_WR_ITEM');
281 END IF;
282
283 RAISE fnd_api.g_exc_unexpected_error;
284
285 END IF;
286
287
288 -- Calling Interaction History's purge API
289
290 IF( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
291 THEN
292 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'cac_task_purge_pub.purge_tasks', 'Before calling JTF_IH_PURGE.P_DELETE_INTERACTIONS');
293 END IF;
294
295 JTF_IH_PURGE.P_Delete_Interactions(
296 p_api_version => 1.0,
297 p_init_msg_list => FND_API.G_FALSE,
298 p_commit => FND_API.G_FALSE,
299 p_processing_set_id => proc_seq_num,
300 p_object_type => 'TASK',
301 x_return_status => x_return_status,
302 x_msg_count => x_msg_count,
303 x_msg_data => x_msg_data);
304
305 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
306 THEN
307 x_return_status := fnd_api.g_ret_sts_unexp_error;
308
309 IF( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
310 THEN
311 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'cac_task_purge_pub.purge_tasks', 'return status error after calling JTF_IH_PURGE.P_DELETE_INTERACTIONS');
312 END IF;
313
314 RAISE fnd_api.g_exc_unexpected_error;
315
316 END IF;
317
318 /************* End of addition by SBARAT on 03/02/2006 for bug# 4997851 *************/
319
320 --calling attachment deletion wrapper api.
321 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
322 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'cac_task_purge_pub.purge_tasks', 'Before calling cac_task_purge_pub.delete_atth_to_tasks');
323 end if;
324
325
326 delete_atth_to_tasks(
327 p_processing_set_id=>proc_seq_num,
328 p_object_type =>'TASK',
329 x_return_status => x_return_status,
330 x_msg_count => x_msg_count,
331 x_msg_data => x_msg_data);
332
333 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
334
335 THEN
336
337 x_return_status := fnd_api.g_ret_sts_unexp_error;
338
339
340 if( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
341
342 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');
343 end if;
344 RAISE fnd_api.g_exc_unexpected_error;
345 end if;
346
347
348 --Calling notes api
349 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
350 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'cac_task_purge_pub.purge_tasks', 'Before calling cac_note_purge_pub.purge_notes');
351 end if;
352
353
354
355 cac_note_purge_pub.purge_notes(
356 p_api_version => 1.0,
357 x_return_status => x_return_status,
358 x_msg_count => x_msg_count,
359 x_msg_data => x_msg_data,
360 p_processing_set_id => proc_seq_num,
361 p_object_type => 'TASK' );
362
363 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
364 THEN
365 x_return_status := fnd_api.g_ret_sts_unexp_error;
366
367 if( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
368 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'cac_task_purge_pub.purge_tasks', 'return status error after calling cac_note_purge_pub.purge_notes');
369 end if;
370 RAISE fnd_api.g_exc_unexpected_error;
371 end if;
372
373
374 --calling the cac_task_purge_pvt.purge_task_entities API
375
376 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
377 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'cac_task_purge_pub.purge_tasks', 'Before calling cac_task_purge_pvt.purge_task_entities');
378 end if;
379
380 cac_task_purge_pvt.purge_task_entities(
381 p_api_version => 1.0,
382 p_init_msg_list => fnd_api.g_false,
383 p_commit => fnd_api.g_false,
384 x_return_status => x_return_status,
385 x_msg_data => x_msg_data,
386 x_msg_count => x_msg_count,
387 p_processing_set_id => proc_seq_num,
388 p_object_type => 'TASK'
389 );
390
391
392
393 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
394 THEN
395 x_return_status := fnd_api.g_ret_sts_unexp_error;
396
397 if( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
398 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');
399 end if;
400
401 RAISE fnd_api.g_exc_unexpected_error;
402 END IF;
403
404
405 else --no task data exists for the selected service requests
406 --else for If ( get_task_processing_set_id.COUNT > 0) then
407
408 -- dbms_output.put_line(' point 5');
409
410 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
411 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'cac_task_purge_pub.purge_tasks', 'no task data exists for the given processing set id ');
412 end if;
413
414 end if;-- for If ( l_tab_tasks_id.COUNT > 0)
415
416
417 IF fnd_api.to_boolean (p_commit)
418 THEN
419 COMMIT WORK;
420 END IF;
421
422 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
423
424
425 EXCEPTION
426 WHEN fnd_api.g_exc_unexpected_error
427 THEN
428 ROLLBACK TO purge_tasks;
429 x_return_status := fnd_api.g_ret_sts_unexp_error;
430 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
431 if (get_tasks_ids%ISOPEN) then
432 Close get_tasks_ids;
433 END IF;
434 if( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
435 IF
436 FND_MSG_PUB.Count_Msg > 0
437 THEN
438 FOR
439 i IN 1..FND_MSG_PUB.Count_Msg
440 LOOP
441 FND_MSG_PUB.Get
442 (
443 p_msg_index => i
444 , p_encoded => 'F'
445 , p_data => x_msg_data
446 , p_msg_index_out => l_msg_index_out
447 );
448 fnd_log.string
449 (
450 fnd_log.level_exception
451 , 'Purge Test'
452 , 'Error is ' || x_msg_data || ' [Index:' || l_msg_index_out || ']'
453 );
454 END LOOP;
455 END IF ;
456 END IF;
457
458 WHEN OTHERS
459 THEN
460 ROLLBACK TO purge_tasks;
461 fnd_message.set_name ('JTF', 'CAC_TASK_UNKNOWN_ERROR');
462 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
463 fnd_msg_pub.add;
464 x_return_status := fnd_api.g_ret_sts_unexp_error;
465 if (get_tasks_ids%ISOPEN) then
466 Close get_tasks_ids;
467 END IF;
468 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
469 if( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
470 IF
471 FND_MSG_PUB.Count_Msg > 0
472 THEN
473 FOR
474 i IN 1..FND_MSG_PUB.Count_Msg
475 LOOP
476 FND_MSG_PUB.Get
477 (
478 p_msg_index => i
479 , p_encoded => 'F'
480 , p_data => x_msg_data
481 , p_msg_index_out => l_msg_index_out
482 );
483 fnd_log.string
484 (
485 fnd_log.level_exception
486 , 'Purge Test'
487 , 'Error is ' || x_msg_data || ' [Index:' || l_msg_index_out || ']'
488 );
489 END LOOP;
490 END IF ;
491 END IF;
492
493 END purge_tasks;
494
495
496
497 Procedure validate_tasks(
498 p_api_version IN NUMBER,
499 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
500 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
501 x_return_status OUT NOCOPY VARCHAR2,
502 x_msg_data OUT NOCOPY VARCHAR2,
503 x_msg_count OUT NOCOPY NUMBER,
504 p_object_type IN VARCHAR2,
505 p_processing_set_id IN NUMBER,
506 p_purge_source_with_open_task IN VARCHAR2 DEFAULT 'N')
507 IS
508 l_api_version CONSTANT NUMBER := 1.0;
509 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_TASKS';
510
511 Cursor tasks_ids(b_processing_set_id NUMBER,b_object_type VARCHAR2)
512 is
513
514 Select task_id
515 from jtf_tasks_b
516 Where source_object_type_code=b_object_type
517 And source_object_id in
518 (select distinct object_id from jtf_object_purge_param_tmp
519 where processing_set_id=b_processing_set_id and
520 purge_status is null and object_type=b_object_type)
521 and entity='TASK';
522
523
524 TYPE tab_task_ids is table of NUMBER;
525 l_tab_tasks_id tab_task_ids:=tab_task_ids();
526 proc_seq_num NUMBER;
527 l_msg_index_out VARCHAR2(100);
528 BEGIN
529 SAVEPOINT validate_tasks;
530 x_return_status := fnd_api.g_ret_sts_success;
531
532 IF NOT fnd_api.compatible_api_call (
533 l_api_version,
534 p_api_version,
535 l_api_name,
536 g_pkg_name
537 )
538 THEN
539 RAISE fnd_api.g_exc_unexpected_error;
540 END IF;
541
542 IF fnd_api.to_boolean (p_init_msg_list)
543 THEN
544 fnd_msg_pub.initialize;
545 END IF;
546 --Logging input parameters
547
548 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
549 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'cac_task_purge_pub.validate_tasks', ' p_object_type= '||p_object_type);
550 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'cac_task_purge_pub.validate_tasks', ' p_processing_set_id= '||p_processing_set_id);
551 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);
552 end if;
553
554
555 --calling validations for tasks
556
557 if (p_purge_source_with_open_task='N') then
558
559 Update jtf_object_purge_param_tmp joppt
560 Set joppt.PURGE_STATUS='E', joppt.PURGE_ERROR_MESSAGE='JTF:JTF_TASK_PURGE_VALID_FAIL'
561 where joppt.processing_set_id=p_processing_set_id
562 and joppt.object_type=p_object_type
563 and exists ( select 1 from jtf_tasks_b where
564 source_object_type_code=joppt.object_type
565 and nvl(open_flag,'Y')='Y'
566 and source_object_id=joppt.object_id
567 and entity='TASK');
568
569
570 end if;
571
572
573 Open tasks_ids(p_processing_set_id ,p_object_type );
574 Fetch tasks_ids Bulk Collect Into l_tab_tasks_id;
575
576 if (tasks_ids%ISOPEN) then
577 Close tasks_ids;
578 END IF;
579
580 If ( l_tab_tasks_id.COUNT > 0) then
581
582 select jtf_object_purge_proc_set_s.nextval into proc_seq_num from dual;
583
584 FORALL i in l_tab_tasks_id.first..l_tab_tasks_id.last
585
586 Insert into jtf_object_purge_param_tmp p
587 ( processing_set_id,object_id,object_type,purge_status,purge_error_message)
588 values
589 (proc_seq_num, l_tab_tasks_id(i),'TASK', null,null);
590
591 --call MFS validation API
592
593 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
594 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'cac_task_purge_pub.validate_tasks', 'Before calling CSM_TASK_PURGE_PKG.VALIDATE_MFS_TASKS');
595 end if;
596
597 CSM_TASK_PURGE_PKG.VALIDATE_MFS_TASKS(
598 P_API_VERSION => 1.0,
599 P_INIT_MSG_LIST => FND_API.G_FALSE,
600 P_COMMIT => FND_API.G_FALSE,
601 P_PROCESSING_SET_ID => proc_seq_num ,
602 P_OBJECT_TYPE => 'TASK' ,
603 X_RETURN_STATUS => x_return_status,
604 X_MSG_COUNT => x_msg_count,
605 X_MSG_DATA => x_msg_data);
606
607 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
608 THEN
609 x_return_status := fnd_api.g_ret_sts_unexp_error;
610
611 if( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
612 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');
613 end if;
614 RAISE fnd_api.g_exc_unexpected_error;
615 end if;
616
617
618 --bulk update the rows with error status E for which tasks validations have failed.
619
620 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
621 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'cac_task_purge_pub.validate_tasks', ' before updating jtf_object_purge_param_tmp');
622 end if;
623
624 Update jtf_object_purge_param_tmp temp
625 Set temp.purge_status='E',
626 temp.purge_error_message=(select purge_error_message from jtf_object_purge_param_tmp
627 where processing_set_id=proc_seq_num
628 and object_type='TASK'
629 and object_id in (select task_id from jtf_tasks_b
630 where source_object_id=temp.object_id
631 and source_object_type_code=temp.object_type and
632 entity='TASK') and
633 purge_status is not null and rownum =1)
634 Where temp.processing_set_id=p_processing_set_id
635 and temp.object_type=p_object_type
636 and temp.object_id in
637
638 ( select distinct b.source_object_id from jtf_tasks_b b,
639 jtf_object_purge_param_tmp temp where temp.object_id=b.task_id and
640 temp.processing_set_id=proc_seq_num and temp.object_type='TASK' and temp.purge_status is not null
641 and b.entity='TASK');
642
643
644 END IF;-- for If ( l_tab_tasks_id.COUNT > 0) then
645
646 IF NOT (x_return_status = fnd_api.g_ret_sts_success)
647 THEN
648 x_return_status := fnd_api.g_ret_sts_unexp_error;
649 RAISE fnd_api.g_exc_unexpected_error;
650 END IF;
651
652 IF fnd_api.to_boolean (p_commit)
653 THEN
654 COMMIT WORK;
655 END IF;
656
657 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
658
659 EXCEPTION
660 WHEN fnd_api.g_exc_unexpected_error
661 THEN
662 ROLLBACK TO validate_tasks;
663 if (tasks_ids%ISOPEN) then
664 CLOSE tasks_ids;
665 end if;
666 x_return_status := fnd_api.g_ret_sts_unexp_error;
667 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
668
669
670 if( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
671 IF
672 FND_MSG_PUB.Count_Msg > 0
673 THEN
674 FOR
675 i IN 1..FND_MSG_PUB.Count_Msg
676 LOOP
677 FND_MSG_PUB.Get
678 (
679 p_msg_index => i
680 , p_encoded => 'F'
681 , p_data => x_msg_data
682 , p_msg_index_out => l_msg_index_out
683 );
684 fnd_log.string
685 (
686 fnd_log.level_exception
687 , 'Purge Test'
688 , 'Error is ' || x_msg_data || ' [Index:' || l_msg_index_out || ']'
689 );
690 END LOOP;
691 END IF ;
692 END IF;
693
694 WHEN OTHERS
695 THEN
696 ROLLBACK TO validate_tasks;
697 if (tasks_ids%ISOPEN) then
698 CLOSE tasks_ids;
699 end if;
700 fnd_message.set_name ('JTF', 'JTF_TASK_VALID_UNKNOWN_ERR');
701 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
702 fnd_msg_pub.add;
703 x_return_status := fnd_api.g_ret_sts_unexp_error;
704
705 if( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
706 IF FND_MSG_PUB.Count_Msg > 0
707 THEN
708 FOR
709 i IN 1..FND_MSG_PUB.Count_Msg
710 LOOP
711 FND_MSG_PUB.Get
712 (
713 p_msg_index => i
714 , p_encoded => 'F'
715 , p_data => x_msg_data
716 , p_msg_index_out => l_msg_index_out
717 );
718
719
720 fnd_log.string
721 (
722 fnd_log.level_exception
723 , 'Purge Test'
724 , 'Error is ' || x_msg_data || ' [Index:' || l_msg_index_out || ']'
725 );
726 END LOOP;
727 END IF ;
728
729 END IF;
730
731 END validate_tasks;
732
733 END CAC_TASK_PURGE_PUB;