[Home] [Help]
PACKAGE BODY: APPS.CSM_TASK_EVENT_PKG
Source
1 PACKAGE BODY CSM_TASK_EVENT_PKG AS
2 /* $Header: csmetskb.pls 120.3.12020000.3 2013/04/09 11:00:32 saradhak ship $ */
3
4 l_markdirty_failed EXCEPTION;
5
6 /*** Globals ***/
7 g_tasks_acc_table_name CONSTANT VARCHAR2(30) := 'CSM_TASKS_ACC';
8 g_tasks_table_name CONSTANT VARCHAR2(30) := 'JTF_TASKS_B';
9 g_tasks_seq_name CONSTANT VARCHAR2(30) := 'CSM_TASKS_ACC_S';
10 g_tasks_pk1_name CONSTANT VARCHAR2(30) := 'TASK_ID';
11 g_tasks_pubi_name CONSTANT CSM_ACC_PKG.t_publication_item_list :=
12 CSM_ACC_PKG.t_publication_item_list('CSM_TASKS');
13
14 /** to be called from csm_task_assignment_event_pkg.task_assignments_acc_processor **/
15 PROCEDURE ACC_INSERT (p_user_id in fnd_user.user_id%TYPE,
16 p_task_id jtf_tasks_b.task_id%TYPE)
17 IS
18 l_sqlerrno VARCHAR2(20);
19 l_sqlerrmsg VARCHAR2(4000);
20 l_error_msg VARCHAR2(4000);
21 l_return_status VARCHAR2(2000);
22
23 BEGIN
24 CSM_UTIL_PKG.LOG('Entering ACC_INSERT for task_id: ' || p_task_id,
25 'CSM_TASK_EVENT_PKG.ACC_INSERT',FND_LOG.LEVEL_PROCEDURE);
26
27 CSM_ACC_PKG.Insert_Acc
28 ( P_PUBLICATION_ITEM_NAMES => g_tasks_pubi_name
29 ,P_ACC_TABLE_NAME => g_tasks_acc_table_name
30 ,P_SEQ_NAME => g_tasks_seq_name
31 ,P_PK1_NAME => g_tasks_pk1_name
32 ,P_PK1_NUM_VALUE => p_task_id
33 ,P_USER_ID => p_user_id
34 );
35
36 CSM_UTIL_PKG.LOG('Leaving ACC_INSERT for task_id: ' || p_task_id,
37 'CSM_TASK_EVENT_PKG.ACC_INSERT',FND_LOG.LEVEL_PROCEDURE);
38
39 EXCEPTION
40 WHEN OTHERS THEN
41 l_sqlerrno := to_char(SQLCODE);
42 l_sqlerrmsg := substr(SQLERRM, 1,2000);
43 l_error_msg := ' Exception in ACC_INSERT for task_id:'
44 || to_char(p_task_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
45 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_TASK_EVENT_PKG.ACC_INSERT',FND_LOG.LEVEL_EXCEPTION);
46 RAISE;
47 END ACC_INSERT;
48
49
50 /** to be called from csm_task_assignment_event_pkg.task_assignments_acc_d **/
51 PROCEDURE ACC_DELETE (p_user_id in fnd_user.user_id%TYPE,
52 p_task_id jtf_tasks_b.task_id%TYPE)
53 IS
54 l_sqlerrno VARCHAR2(20);
55 l_sqlerrmsg VARCHAR2(4000);
56 l_error_msg VARCHAR2(4000);
57 l_return_status VARCHAR2(2000);
58
59 BEGIN
60 CSM_UTIL_PKG.LOG('Entering ACC_DELETE for task_id: ' || p_task_id,
61 'CSM_TASK_EVENT_PKG.ACC_DELETE',FND_LOG.LEVEL_PROCEDURE);
62
63 CSM_ACC_PKG.Delete_Acc
64 ( P_PUBLICATION_ITEM_NAMES => g_tasks_pubi_name
65 ,P_ACC_TABLE_NAME => g_tasks_acc_table_name
66 ,P_PK1_NAME => g_tasks_pk1_name
67 ,P_PK1_NUM_VALUE => p_task_id
68 ,P_USER_ID => p_user_id
69 );
70
71 CSM_UTIL_PKG.LOG('Leaving ACC_DELETE for task_id: ' || p_task_id,
72 'CSM_TASK_EVENT_PKG.ACC_DELETE',FND_LOG.LEVEL_PROCEDURE);
73
74 EXCEPTION
75 WHEN OTHERS THEN
76 l_sqlerrno := to_char(SQLCODE);
77 l_sqlerrmsg := substr(SQLERRM, 1,2000);
78 l_error_msg := ' Exception in ACC_DELETE for task_id:'
79 || to_char(p_task_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
80 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_TASK_EVENT_PKG.ACC_DELETE',FND_LOG.LEVEL_EXCEPTION);
81 RAISE;
82 END ACC_DELETE;
83
84 /**
85 ** Conc program called every midnight to purge tasks created_by mobile user, depending on the
86 ** history profile of the user
87 **/
88 PROCEDURE PURGE_TASKS_CONC(p_status OUT NOCOPY VARCHAR2, p_message OUT NOCOPY VARCHAR2)
89 IS
90 PRAGMA AUTONOMOUS_TRANSACTION;
91 l_task_id jtf_tasks_b.task_id%TYPE;
92 l_user_id csm_tasks_acc.USER_ID%TYPE;
93 l_last_run_date date;
94 l_dummy NUMBER;
95
96 CURSOR l_purge_tasks_csr
97 IS
98 SELECT acc.task_id,
99 acc.USER_ID,
100 acc.COUNTER,
101 jt.source_object_type_code
102 FROM csm_tasks_acc acc,
103 jtf_tasks_b jt
104 WHERE jt.task_id = acc.task_id
105 AND jt.created_by = acc.user_id -- task is created by the user
106 AND jt.creation_date
107 < (SYSDATE - csm_profile_pkg.get_task_history_days(acc.user_id))
108 ;
109 -- check if the task and user pair exist in csm_task_assignments_acc
110 CURSOR l_task_assignment_csr (b_task_id NUMBER, b_user_id NUMBER)
111 IS
112 SELECT acc.counter
113 FROM CSM_TASK_ASSIGNMENTS_ACC acc,
114 JTF_TASK_ASSIGNMENTS jta
115 WHERE acc.TASK_ASSIGNMENT_ID = jta.TASK_ASSIGNMENT_ID
116 AND jta.TASK_ID = b_task_id
117 AND acc.USER_ID = b_user_id;
118
119 CURSOR l_upd_last_run_date_csr
120 IS
121 SELECT 1
122 FROM jtm_con_request_data
123 WHERE product_code = 'CSM'
124 AND package_name = 'CSM_TASK_EVENT_PKG'
125 AND procedure_name = 'PURGE_TASKS_CONC'
126 FOR UPDATE OF last_run_date NOWAIT
127 ;
128
129 TYPE l_task_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
130 l_task_tbl l_task_tbl_type;
131 TYPE l_userid_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
132 l_userid_tbl l_userid_tbl_type;
133 TYPE l_counter_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
134 l_counter_tbl l_counter_tbl_type;
135 TYPE l_task_src_type_tbl_type IS TABLE OF jtf_tasks_b.source_object_type_code%TYPE INDEX BY BINARY_INTEGER;
136 l_task_src_type_tbl l_task_src_type_tbl_type;
137
138 l_tsk_counter NUMBER;
139 l_tas_counter NUMBER;
140 l_sqlerrno VARCHAR2(20);
141 l_sqlerrmsg VARCHAR2(4000);
142 l_error_msg VARCHAR2(4000);
143 l_return_status VARCHAR2(2000);
144
145 BEGIN
146 l_last_run_date := SYSDATE;
147 l_tsk_counter := 0;
148 l_tas_counter := 0;
149
150 OPEN l_purge_tasks_csr;
151 LOOP
152 IF l_task_tbl.COUNT > 0 THEN
153 l_task_tbl.DELETE;
154 END IF;
155 IF l_userid_tbl.COUNT > 0 THEN
156 l_userid_tbl.DELETE;
157 END IF;
158 IF l_counter_tbl.COUNT > 0 THEN
159 l_counter_tbl.DELETE;
160 END IF;
161
162 FETCH l_purge_tasks_csr BULK COLLECT INTO l_task_tbl, l_userid_tbl, l_counter_tbl,l_task_src_type_tbl LIMIT 50;
163 EXIT WHEN l_task_tbl.COUNT = 0;
164
165 IF l_task_tbl.COUNT > 0 THEN
166 CSM_UTIL_PKG.LOG(TO_CHAR(l_task_tbl.COUNT) || ' task records sent for purge', 'CSM_SR_EVENT_PKG.PURGE_INCIDENTS_CONC',FND_LOG.LEVEL_EVENT);
167 FOR i IN l_task_tbl.FIRST..l_task_tbl.LAST LOOP
168 l_task_id := l_task_tbl(i);
169 l_user_id := l_userid_tbl(i);
170 l_tsk_counter := l_counter_tbl(i);
171
172
173 OPEN l_task_assignment_csr(l_task_id, l_user_id);
174 FETCH l_task_assignment_csr INTO l_tas_counter;
175 IF l_task_assignment_csr%NOTFOUND THEN
176 l_tas_counter := 0;
177 END IF;
178 CLOSE l_task_assignment_csr;
179
180 -- do task delete only for those not to be handled by task_assignment_purge
181 IF l_tsk_counter > l_tas_counter THEN
182
183 IF l_task_src_type_tbl(i) ='SR' AND CSM_SR_EVENT_PKG.IS_SR_OPEN(l_task_id) = FALSE THEN
184 --Delete the taskonly if the SR is closed
185 csm_task_event_pkg.task_del_init(p_task_id=>l_task_id);
186
187 ELSIF l_task_src_type_tbl(i) ='TASK' THEN
188 --Delete simply,as there are no SR attached for a personal task
189 csm_task_event_pkg.task_del_init(p_task_id=>l_task_id);
190
191 END IF;
192
193 END IF; -- if counter > 1
194
195 END LOOP;
196 END IF;
197
198 -- commit after every 50 records
199 COMMIT;
200
201 END LOOP;
202 CLOSE l_purge_tasks_csr;
203
204 -- update last_run_date
205 UPDATE jtm_con_request_data
206 SET last_run_date = l_last_run_date
207 WHERE product_code = 'CSM'
208 AND package_name = 'CSM_TASK_EVENT_PKG'
209 AND procedure_name = 'PURGE_TASKS_CONC';
210
211
212 COMMIT;
213
214 p_status := 'SUCCESS';
215 p_message := 'CSM_TASK_EVENT_PKG.PURGE_TASKS_CONC Executed successfully';
216
217 EXCEPTION
218 WHEN OTHERS THEN
219 l_sqlerrno := to_char(SQLCODE);
220 l_sqlerrmsg := substr(SQLERRM, 1,2000);
221 ROLLBACK;
222 l_error_msg := ' Exception in PURGE_TASKS_CONC for task_id:' || to_char(l_task_id)
223 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
224 p_status := 'ERROR';
225 p_message := 'Error in CSM_TASK_EVENT_PKG.PURGE_TASKS_CONC: ' || l_error_msg;
226 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_TASK_EVENT_PKG.PURGE_TASKS_CONC',FND_LOG.LEVEL_EVENT);
227 END PURGE_TASKS_CONC;
228
229 /**
230 ** Conc program called every midnight to purge tasks created_by mobile user, depending on the
231 ** history profile of the user
232 **/
233 PROCEDURE CHECK_ESCALATION_TASKS_CONC(p_status OUT NOCOPY VARCHAR2, p_message OUT NOCOPY VARCHAR2)
234 IS
235 PRAGMA AUTONOMOUS_TRANSACTION;
236
237 CURSOR c_check_esc_tasks (b_last_run_date DATE)
238 IS
239 SELECT acc.TASK_ID
240 FROM jtf_task_references_b ref, CSM_TASKS_ACC acc, JTF_TASKS_B esc
241 WHERE ref.OBJECT_ID = acc.TASK_ID
242 AND ref.object_type_code = 'TASK'
243 AND ref.reference_code = 'ESC'
244 AND ref.task_id = esc.task_id
245 AND esc.source_object_type_code = 'ESC'
246 AND ref.LAST_UPDATE_DATE >= b_last_run_date;
247
248 CURSOR l_upd_last_run_date_csr
249 IS
250 SELECT last_run_date
251 FROM jtm_con_request_data
252 WHERE product_code = 'CSM'
253 AND package_name = 'CSM_TASK_EVENT_PKG'
254 AND procedure_name = 'CHECK_ESCALATION_TASKS_CONC'
255 FOR UPDATE OF last_run_date NOWAIT;
256
257 l_upd_last_run_date DATE;
258 l_last_run_date DATE;
259 l_task_id JTF_TASKS_B.TASK_ID%TYPE;
260 l_sqlerrno VARCHAR2(20);
261 l_sqlerrmsg VARCHAR2(4000);
262 l_error_msg VARCHAR2(4000);
263 l_return_status VARCHAR2(2000);
264
265 BEGIN
266 l_last_run_date := SYSDATE;
267
268 OPEN l_upd_last_run_date_csr;
269 FETCH l_upd_last_run_date_csr INTO l_upd_last_run_date;
270 IF l_upd_last_run_date_csr%FOUND THEN
271 -- for all the users check for cancelled tasks and history days
272 FOR l_check_esc_task_rec IN c_check_esc_tasks(l_upd_last_run_date) LOOP
273
274 l_task_id := l_check_esc_task_rec.task_id;
275 -- CSM_UTIL_PKG.pvt_log('Task ' || l_task_id || ' is escalated' );
276 CSM_UTIL_PKG.log('Task ID ' || l_task_id || ' is escalated ' ,
277 'CSM_TASK_EVENT_PKG.CHECK_ESCALATION_TASKS_CONC',
278 FND_LOG.LEVEL_STATEMENT);
279
280 csm_task_event_pkg.TASK_MAKE_DIRTY_U_FOREACHUSER(p_task_id=>l_task_id,
281 p_error_msg=>l_error_msg,
282 x_return_status=>l_return_status);
283 END LOOP;
284
285 -- update last_run_date
286 UPDATE jtm_con_request_data
287 SET last_run_date = l_last_run_date
288 WHERE CURRENT OF l_upd_last_run_date_csr;
289 END IF;
290 CLOSE l_upd_last_run_date_csr;
291
292 COMMIT;
293
294 p_status := 'SUCCESS';
295 p_message := 'CSM_TASK_EVENT_PKG.CHECK_ESCALATION_TASKS_CONC Executed successfully';
296
297 EXCEPTION
298 WHEN OTHERS THEN
299 l_sqlerrno := to_char(SQLCODE);
300 l_sqlerrmsg := substr(SQLERRM, 1,2000);
301 l_error_msg := ' Exception in CHECK_ESCALATION_TASKS_CONC for task_id:' || to_char(l_task_id)
302 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
303 p_status := 'ERROR';
304 p_message := 'Error in CSM_TASK_EVENT_PKG.CHECK_ESCALATION_TASKS_CONC: ' || l_error_msg;
305 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_TASK_EVENT_PKG.CHECK_ESCALATION_TASKS_CONC',FND_LOG.LEVEL_EXCEPTION);
306 ROLLBACK;
307 END CHECK_ESCALATION_TASKS_CONC;
308
309 PROCEDURE TASK_MAKE_DIRTY_U_FOREACHUSER(p_task_id IN NUMBER,
310 p_error_msg OUT NOCOPY VARCHAR2,
311 x_return_status IN OUT NOCOPY VARCHAR2)
312 IS
313 l_err_msg VARCHAR2(4000);
314 l_user_id NUMBER;
315 l_markdirty BOOLEAN;
316 l_dmllist asg_download.dml_list;
317 l_dml varchar2(1);
318 l_timestamp DATE;
319 l_accesslist asg_download.access_list;
320 l_resourcelist asg_download.user_list;
321 l_publicationitemname VARCHAR2(50);
322 l_access_count NUMBER;
323 l_task_id jtf_tasks_b.task_id%TYPE;
324 l_sqlerrmsg VARCHAR2(4000);
325 l_error_msg VARCHAR2(4000);
326 l_return_status VARCHAR2(2000);
327
328 CURSOR l_upd_task_foreachuser_csr (p_task_id jtf_tasks_b.task_id%TYPE)
329 IS
330 SELECT acc.user_id, acc.access_id
331 FROM csm_tasks_acc acc
332 WHERE acc.task_id = p_task_id;
333
334 CURSOR l_del_task_foreachuser_csr(p_task_id jtf_tasks_b.task_id%TYPE)
335 IS
336 SELECT acc.user_id, jta.task_assignment_id
337 FROM jtf_tasks_b jt,
338 csm_tasks_acc acc,
339 jtf_task_statuses_b jts,
340 jtf_task_assignments jta
341 WHERE acc.task_id = p_task_id
342 AND jt.task_id = acc.task_id
343 AND jta.task_id = acc.task_id
344 AND jts.task_status_id = jt.task_status_id
345 AND ((CSM_UTIL_PKG.GetLocalTime(jt.scheduled_start_date, acc.user_id) <
346 (SYSDATE - NVL(csm_profile_pkg.get_task_history_days(asg_base.get_user_id),100))
347 )
348 AND
349 (jts.cancelled_flag = 'Y' OR jts.closed_flag = 'Y' OR jts.completed_flag = 'Y' OR jts.rejected_flag = 'Y')
350 );
351
352 CURSOR l_next_seq_value
353 IS
354 SELECT CSM_ACTIVITY_SEQ.nextval FROM dual;
355
356 BEGIN
357 x_return_status := FND_API.G_RET_STS_SUCCESS;
358 l_err_msg := 'Entering CSM_TASK_EVENT_PKG.TASK_MAKE_DIRTY_U_FOREACHUSER' || ' for task_id ' || to_char(p_task_id);
359 CSM_UTIL_PKG.LOG( l_err_msg, 'CSM_TASK_EVENT_PKG.TASK_MAKE_DIRTY_U_FOREACHUSER', FND_LOG.LEVEL_PROCEDURE);
360
361 --get the activity attributes
362 l_task_id := p_task_id;
363
364 --get dml
365 l_dml := 'U';
366
367 --change the DML value to one of the ASG constants for compatability
368 --purposes (in case ASG internally changes these values in future)
369 l_dml := CSM_UTIL_PKG.GetAsgDmlConstant(l_dml);
370
371 l_publicationitemname := 'CSM_TASKS';
372
373 --get the current date to be passed to make dirty api
374 l_timestamp := SYSDATE;
375
376 l_access_count := 0;
377 IF l_accesslist.COUNT > 0 THEN
378 l_accesslist.DELETE;
379 l_resourcelist.DELETE;
380 l_dmllist.DELETE;
381 END IF;
382
383 for l_upd_task_foreachuser_rec in l_upd_task_foreachuser_csr(l_task_id) loop
384 l_access_count := l_access_count + 1;
385 l_accesslist(l_access_count) := l_upd_task_foreachuser_rec.access_id;
386 l_resourcelist(l_access_count) := l_upd_task_foreachuser_rec.user_id;
387 l_dmllist(l_access_count) := l_dml;
388 end loop;
389
390 if l_accesslist.count > 0 then
391 l_markdirty := csm_util_pkg.MakeDirtyForUser (l_publicationitemname,
392 l_accesslist,
393 l_resourcelist,
394 l_dmllist,
395 l_timestamp);
396
397 if not l_markdirty then
398 RAISE l_markdirty_failed;
399 end if;
400 END IF;
401
402 -- get all tasks to be deleted for the user if the scheduled_start_dates have been updated
403 FOR l_del_task_foreachuser_rec IN l_del_task_foreachuser_csr(l_task_id) LOOP
404
405 csm_task_assignment_event_pkg.TASK_ASSIGNMENT_PURGE_INIT
406 (p_task_assignment_id=>l_del_task_foreachuser_rec.task_assignment_id,
407 p_error_msg=>l_error_msg,
408 x_return_status=>l_return_status);
409 END LOOP;
410
411 l_err_msg := 'Leaving CSM_TASK_EVENT_PKG.TASK_MAKE_DIRTY_U_FOREACHUSER' || ' for task_id ' || to_char(p_task_id);
412 CSM_UTIL_PKG.LOG( l_err_msg, 'CSM_TASK_EVENT_PKG.TASK_MAKE_DIRTY_U_FOREACHUSER', FND_LOG.LEVEL_PROCEDURE);
413
414 EXCEPTION
415 WHEN l_markdirty_failed THEN
416 x_return_status := FND_API.G_RET_STS_ERROR;
417 p_error_msg := ' FAILED TASK_MAKE_DIRTY_U_FOREACHUSER:' || to_char(l_task_id);
418 CSM_UTIL_PKG.LOG( p_error_msg, 'CSM_TASK_EVENT_PKG.TASK_MAKE_DIRTY_U_FOREACHUSER', FND_LOG.LEVEL_ERROR);
419 RAISE;
420
421 WHEN others THEN
422 x_return_status := FND_API.G_RET_STS_ERROR;
423 p_error_msg := ' FAILED TASK_MAKE_DIRTY_U_FOREACHUSER:' || to_char(l_task_id);
424 CSM_UTIL_PKG.LOG( p_error_msg, 'CSM_TASK_EVENT_PKG.TASK_MAKE_DIRTY_U_FOREACHUSER', FND_LOG.LEVEL_ERROR);
425 RAISE;
426 END TASK_MAKE_DIRTY_U_FOREACHUSER;
427
428 PROCEDURE TASK_INS_INIT(p_task_id IN NUMBER)
429 IS
430 l_sqlerrno VARCHAR2(20);
431 l_sqlerrmsg VARCHAR2(4000);
432 l_error_msg VARCHAR2(4000);
433 l_return_status VARCHAR2(2000);
434
435 CURSOR l_task_csr (b_task_id NUMBER) IS
436 SELECT jt.CREATED_BY, jtt.private_flag, jt.source_object_type_code, jt.ADDRESS_ID -- 22 means Escalation task
437 FROM JTF_TASKS_B jt,
438 jtf_task_types_b jtt
439 WHERE jt.TASK_ID = b_task_id
440 AND jtt.task_type_id = jt.task_type_id;
441
442 l_task_rec l_task_csr%ROWTYPE;
443
444 BEGIN
445 CSM_UTIL_PKG.LOG('Entering TASK_INS_INIT for task_id: ' || p_task_id,
446 'CSM_TASK_EVENT_PKG.TASK_INS_INIT',FND_LOG.LEVEL_PROCEDURE);
447
448 OPEN l_task_csr(p_task_id);
449 FETCH l_task_csr INTO l_task_rec;
450 IF l_task_csr%NOTFOUND THEN
451 CLOSE l_task_csr;
452 RETURN;
453 END IF;
454 CLOSE l_task_csr;
455
456 IF CSM_UTIL_PKG.IS_HTML5_USER(l_task_rec.CREATED_BY) THEN
457 CSM_UTIL_PKG.LOG('Leaving TASK_INS_INIT as this task is created by HTML5 user','CSM_TASK_EVENT_PKG.TASK_INS_INIT',FND_LOG.LEVEL_PROCEDURE);
458 RETURN;
459 END IF;
460
461 -- check if task is created by a mobile user
462 IF CSM_UTIL_PKG.IS_PALM_USER(l_task_rec.CREATED_BY)
463 AND ( l_task_rec.SOURCE_OBJECT_TYPE_CODE IN ('SR', 'TASK') OR l_task_rec.private_flag = 'Y') THEN
464
465 -- get task notes
466 csm_notes_event_pkg.notes_make_dirty_i_grp(p_sourceobjectcode=>'TASK',
467 p_sourceobjectid=>p_task_id,
468 p_userid=>l_task_rec.CREATED_BY,
469 p_error_msg=>l_error_msg,
470 x_return_status=>l_return_status);
471
472 -- insert into csm_tasks_acc
473 csm_task_event_pkg.acc_insert(p_task_id=>p_task_id,
474 p_user_id=>l_task_rec.CREATED_BY);
475 IF l_task_rec.SOURCE_OBJECT_TYPE_CODE ='TASK' AND l_task_rec.ADDRESS_ID IS NOT NULL THEN
476 --insert party sites
477 csm_party_site_event_pkg.party_sites_acc_i(p_party_site_id => l_task_rec.ADDRESS_ID,
478 p_user_id => l_task_rec.CREATED_BY,
479 p_flowtype => NULL,
480 p_error_msg => l_error_msg,
481 x_return_status => l_return_status);
482 END IF;
483
484 END IF;
485
486 CSM_UTIL_PKG.LOG('Leaving TASK_INS_INIT for task_id: ' || p_task_id,
487 'CSM_TASK_EVENT_PKG.TASK_INS_INIT',FND_LOG.LEVEL_PROCEDURE);
488 EXCEPTION
489 WHEN OTHERS THEN
490 l_sqlerrno := to_char(SQLCODE);
491 l_sqlerrmsg := substr(SQLERRM, 1,2000);
492 l_error_msg := ' Exception in TASK_INS_INIT for task_id:'
493 || to_char(p_task_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
494 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_TASK_EVENT_PKG.TASK_INS_INIT',FND_LOG.LEVEL_EXCEPTION);
495 RAISE;
496 END TASK_INS_INIT;
497
498 PROCEDURE TASK_DEL_INIT(p_task_id IN NUMBER)
499 IS
500 l_sqlerrno VARCHAR2(20);
501 l_sqlerrmsg VARCHAR2(4000);
502 l_error_msg VARCHAR2(4000);
503 l_return_status VARCHAR2(2000);
504
505 CURSOR l_task_csr (b_task_id NUMBER) IS
506 SELECT jt.CREATED_BY, jt.ADDRESS_ID, jt.SOURCE_OBJECT_TYPE_CODE
507 FROM JTF_TASKS_B jt
508 WHERE jt.TASK_ID = b_task_id
509 AND EXISTS
510 (SELECT 1
511 FROM csm_tasks_acc acc
512 WHERE acc.user_id = jt.created_by
513 AND acc.task_id = jt.task_id);
514
515 l_task_rec l_task_csr%ROWTYPE;
516
517 BEGIN
518 CSM_UTIL_PKG.LOG('Entering TASK_DEL_INIT for task_id: ' || p_task_id,
519 'CSM_TASK_EVENT_PKG.TASK_DEL_INIT',FND_LOG.LEVEL_PROCEDURE);
520
521 OPEN l_task_csr(p_task_id);
522 FETCH l_task_csr INTO l_task_rec;
523 IF l_task_csr%NOTFOUND THEN
524 CLOSE l_task_csr;
525 RETURN;
526 END IF;
527 CLOSE l_task_csr;
528
529 -- delete task notes
530 csm_notes_event_pkg.notes_make_dirty_d_grp(p_sourceobjectcode=>'TASK',
531 p_sourceobjectid=>p_task_id,
532 p_userid=>l_task_rec.CREATED_BY,
533 p_error_msg=>l_error_msg,
534 x_return_status=>l_return_status);
535
536 -- delete from csm_tasks_acc
537 csm_task_event_pkg.acc_delete(p_task_id=>p_task_id,
538 p_user_id=>l_task_rec.CREATED_BY);
539
540 IF l_task_rec.SOURCE_OBJECT_TYPE_CODE ='TASK' AND l_task_rec.ADDRESS_ID IS NOT NULL THEN
541
542 --Delete Party Sites
543 csm_party_site_event_pkg.party_sites_acc_d(p_party_site_id => l_task_rec.ADDRESS_ID,
544 p_user_id => l_task_rec.CREATED_BY,
545 p_flowtype => NULL,
546 p_error_msg => l_error_msg,
547 x_return_status => l_return_status);
548 END IF;
549
550 CSM_UTIL_PKG.LOG('Leaving TASK_DEL_INIT for task_id: ' || p_task_id,
551 'CSM_TASK_EVENT_PKG.TASK_DEL_INIT',FND_LOG.LEVEL_PROCEDURE);
552 EXCEPTION
553 WHEN OTHERS THEN
554 l_sqlerrno := to_char(SQLCODE);
555 l_sqlerrmsg := substr(SQLERRM, 1,2000);
556 l_error_msg := ' Exception in TASK_DEL_INIT for task_id:'
557 || to_char(p_task_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
558 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_TASK_EVENT_PKG.TASK_DEL_INIT',FND_LOG.LEVEL_EXCEPTION);
559 RAISE;
560 END TASK_DEL_INIT;
561
562 END CSM_TASK_EVENT_PKG;