[Home] [Help]
PACKAGE BODY: APPS.CSL_JTF_TASKS_ACC_PKG
Source
1 PACKAGE BODY CSL_JTF_TASKS_ACC_PKG AS
2 /* $Header: csltkacb.pls 120.0 2005/05/24 17:32:14 appldev noship $ */
3
4 /*** Globals ***/
5 g_acc_table_name CONSTANT VARCHAR2(30) := 'CSL_JTF_TASKS_ACC';
6 g_publication_item_name CONSTANT JTM_HOOK_UTIL_PKG.t_publication_item_list :=
7 JTM_HOOK_UTIL_PKG.t_publication_item_list('CSL_JTF_TASKS_VL');
8 g_table_name CONSTANT VARCHAR2(30) := 'JTF_TASKS';
9 g_pk1_name CONSTANT VARCHAR2(30) := 'TASK_ID';
10
11 g_debug_level NUMBER; -- debug level
12 g_replicate_pre_update BOOLEAN; -- true when task was replicated before the update
13
14 g_cached_task_address_id NUMBER;
15
16 /*** Function that checks if task record should be replicated. Returns TRUE if it should ***/
17 FUNCTION Replicate_Record
18 ( p_task_id NUMBER
19 )
20 RETURN BOOLEAN
21 IS
22 CURSOR c_task (b_task_id NUMBER) IS
23 SELECT *
24 FROM JTF_TASKS_B
25 WHERE task_id = b_task_id;
26 r_task c_task%ROWTYPE;
27
28 CURSOR c_task_status (b_task_status_id NUMBER) IS
29 SELECT null
30 FROM JTF_TASK_STATUSES_B
31 WHERE TASK_STATUS_ID = b_task_status_id
32 AND (
33 NVL(ASSIGNED_FLAG, 'N') = 'Y'
34 OR NVL(CANCELLED_FLAG, 'N') = 'Y'
35 OR NVL(COMPLETED_FLAG, 'N') = 'Y'
36 OR NVL(CLOSED_FLAG, 'N') = 'Y'
37 );
38 r_task_status c_task_status%ROWTYPE;
39
40 CURSOR c_task_type ( b_task_type_id NUMBER ) IS
41 SELECT null
42 FROM jtf_task_types_b
43 WHERE task_type_id = b_task_type_id
44 AND rule = 'DISPATCH';
45 r_task_type c_task_type%ROWTYPE;
46
47 CURSOR c_private_task_type( b_task_type_id NUMBER ) IS
48 SELECT null
49 FROM jtf_task_types_b
50 WHERE task_type_id = b_task_type_id
51 AND private_flag = 'Y';
52 r_private_task_type c_private_task_type%ROWTYPE;
53
54 BEGIN
55 /*** get debug level ***/
56 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
57
58 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
59 jtm_message_log_pkg.Log_Msg
60 ( p_task_id
61 , g_table_name
62 , 'Entering Replicate_Record'
63 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
64 END IF;
65
66 OPEN c_task( p_task_id );
67 FETCH c_task INTO r_task;
68 IF c_task%NOTFOUND THEN
69 /*** could not find task record -> exit ***/
70 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
71 jtm_message_log_pkg.Log_Msg
72 ( p_task_id
73 , g_table_name
74 , 'Replicate_Record error: Could not find task_id ' || p_task_id
75 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
76 END IF;
77
78 CLOSE c_task;
79 RETURN FALSE;
80 END IF;
81 CLOSE c_task;
82
83 /*** is this a SR or personal (trip) task? ***/
84 IF r_task.source_object_type_code NOT IN ('TASK', 'SR') THEN
85 /*** no -> don't replicate ***/
86 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
87 jtm_message_log_pkg.Log_Msg
88 ( p_task_id
89 , g_table_name
90 , 'Replicate_Record returned FALSE' || fnd_global.local_chr(10) ||
91 'SOURCE_OBJECT_TYPE CODE NOT IN (''SR'',''TASK'')'
92 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
93 END IF;
94 RETURN FALSE;
95 END IF;
96
97 /*** check if scheduled start and end dates are not null ***/
98 IF r_task.scheduled_start_date IS NULL OR
99 r_task.scheduled_end_date IS NULL THEN
100 /*** no -> don't replicate ***/
101 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
102 jtm_message_log_pkg.Log_Msg
103 ( p_task_id
104 , g_table_name
105 , 'Replicate_Record returned FALSE ' || fnd_global.local_chr(10) ||
106 'SCHEDULED_START_DATE and SCHEDULED_END_DATE should both be NOT NULL.'
107 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
108 END IF;
109 RETURN FALSE;
110 END IF;
111
112
113 /*** is this a SR task? ***/
114 IF r_task.source_object_type_code = 'SR' THEN
115 /*** yes -> check if task type rule = DISPATCH ***/
116 OPEN c_task_type ( r_task.task_type_id );
117 FETCH c_task_type INTO r_task_type;
118 IF c_task_type%NOTFOUND THEN
119 /*** no -> don't replicate ***/
120 CLOSE c_task_type;
121 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
122 jtm_message_log_pkg.Log_Msg
123 ( p_task_id
124 , g_table_name
125 , 'Replicate_Record returned FALSE ' || fnd_global.local_chr(10) ||
126 'Task''s task type RULE <> ''DISPATCH''.'
127 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
128 END IF;
129 RETURN FALSE;
130 END IF;
131 CLOSE c_task_type;
132
133 /*** check task status ***/
134 OPEN c_task_status ( r_task.task_status_id );
135 FETCH c_task_status INTO r_task_status;
136 IF c_task_status%NOTFOUND THEN
137 /*** no -> don't replicate ***/
138 CLOSE c_task_status;
139 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
140 jtm_message_log_pkg.Log_Msg
141 ( p_task_id
142 , g_table_name
143 , 'Replicate_Record returned FALSE ' || fnd_global.local_chr(10) ||
144 'Task status for SR tasks should have either ASSIGNED_FLAG, CANCELLED_FLAG, '||
145 'COMPLETED_FLAG or CLOSED_FLAG set to ''Y''.'
146 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
147 END IF;
148 RETURN FALSE;
149 END IF;
150 CLOSE c_task_status;
151 END IF;
152
153 /*Is task personal task ?*/
154 IF r_task.source_object_type_code = 'TASK' THEN
155 /*** yes -> check if task type rule = DISPATCH ***/
156 OPEN c_private_task_type ( r_task.task_type_id );
157 FETCH c_private_task_type INTO r_private_task_type;
158 IF c_private_task_type%NOTFOUND THEN
159 /*** no -> don't replicate ***/
160 CLOSE c_private_task_type;
161 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
162 jtm_message_log_pkg.Log_Msg
163 ( p_task_id
164 , g_table_name
165 , 'Replicate_Record returned FALSE ' || fnd_global.local_chr(10) ||
166 'Task''s task type private flag <> ''Y''.'
167 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
168 END IF;
169 RETURN FALSE;
170 END IF;
171 CLOSE c_private_task_type;
172 END IF;
173
174 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
175 jtm_message_log_pkg.Log_Msg
176 ( p_task_id
177 , g_table_name
178 , 'Replicate_Record returned TRUE'
179 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
180 END IF;
181
182 /** Record matched criteria -> return true ***/
183 RETURN TRUE;
184 END Replicate_Record;
185
186
187 /*Function to get the parent*/
188 FUNCTION GetParentId( p_task_id IN Number )
189 RETURN NUMBER
190 IS
191 CURSOR c_parent( b_task_id NUMBER ) IS
192 SELECT source_object_id
193 FROM jtf_tasks_b
194 WHERE source_object_type_code = 'SR'
195 AND task_id = b_task_id;
196 r_parent c_parent%ROWTYPE;
197 BEGIN
198 OPEN c_parent( p_task_id );
199 FETCH c_parent INTO r_parent;
200 IF c_parent%NOTFOUND THEN
201 CLOSE c_parent;
202 RETURN -1;
203 END IF;
204 CLOSE c_parent;
205 RETURN r_parent.source_object_id;
206 END GetParentId;
207
208 /*** Private procedure that replicates given task related data for resource ***/
209 PROCEDURE Insert_ACC_Record
210 ( p_task_id IN NUMBER
211 ,p_resource_id IN NUMBER
212 ,p_flow_type IN NUMBER ) --DEFAULT CSL_CS_INCIDENTS_ALL_ACC_PKG.G_FLOW_NORMAL
213 IS
214 l_return BOOLEAN;
215
216 CURSOR c_task( b_task_id NUMBER ) IS
217 SELECT *
218 FROM JTF_TASKS_B
219 WHERE TASK_ID = b_task_id;
220
221 r_task c_task%ROWTYPE;
222
223 BEGIN
224 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
225 jtm_message_log_pkg.Log_Msg
226 ( p_task_id
227 , g_table_name
228 , 'Entering Insert_ACC_Record'
229 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
230 END IF;
231
232 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
233 jtm_message_log_pkg.Log_Msg
234 ( p_task_id
235 , g_table_name
236 , 'Inserting ACC record for resource_id = ' || p_resource_id
237 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
238 END IF;
239
240 /*** Insert task ACC record ***/
241 JTM_HOOK_UTIL_PKG.Insert_Acc
242 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
243 ,P_ACC_TABLE_NAME => g_acc_table_name
244 ,P_PK1_NAME => g_pk1_name
245 ,P_PK1_NUM_VALUE => p_task_id
246 ,P_RESOURCE_ID => p_resource_id
247 );
248
249 /*Insert the non critical dependant record*/
250 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
251 jtm_message_log_pkg.Log_Msg
252 ( p_task_id
253 , g_table_name
254 , 'Inserting non-critical dependant records'
255 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
256 END IF;
257
258 /*Do not replicate notes for history tasks*/
259 IF p_flow_type <> CSL_CS_INCIDENTS_ALL_ACC_PKG.G_FLOW_HISTORY THEN
260 --NOTES
261 l_return := CSL_JTF_NOTES_ACC_PKG.PRE_INSERT_CHILDREN
262 ( P_SOURCE_OBJ_ID => p_task_id
263 , P_SOURCE_OBJ_CODE => 'TASK'
264 , P_RESOURCE_ID => p_resource_id );
265 END IF;
266
267 OPEN c_task( b_task_id => p_task_id );
268 FETCH c_task INTO r_task;
269 IF c_task%FOUND THEN
270 --PARTY_SITE
271 --fix for bug 2472668: check if address_id is null
272 IF r_task.address_id IS NOT NULL THEN
273 CSL_HZ_PARTY_SITES_ACC_PKG.INSERT_PARTY_SITE( p_party_site_id => r_task.address_id
274 , p_resource_id => p_resource_id );
275 END IF;
276
277
278 --Bug 3724142
279 --ATTACHMENTS
280 CSL_LOBS_ACC_PKG.DOWNLOAD_TASK_ATTACHMENTS(p_task_id);
281
282 END IF;
283 CLOSE c_task;
284
285
286 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
287 jtm_message_log_pkg.Log_Msg
288 ( p_task_id
289 , g_table_name
290 , 'Leaving Insert_ACC_Record'
291 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
292 END IF;
293 END Insert_ACC_Record;
294
295 /*** Private procedure that re-sends given task to mobile ***/
296 PROCEDURE Update_ACC_Record
297 ( p_task_id IN NUMBER
298 ,p_resource_id IN NUMBER
299 ,p_acc_id IN NUMBER
300 )
301 IS
302 BEGIN
303 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
304 jtm_message_log_pkg.Log_Msg
305 ( p_task_id
306 , g_table_name
307 , 'Entering Update_ACC_Record'
308 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
309 END IF;
310
311 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
312 jtm_message_log_pkg.Log_Msg
313 ( p_task_id
314 , g_table_name
315 , 'Updating ACC record for resource_id = ' || p_resource_id || fnd_global.local_chr(10) ||
316 'access_id = ' || p_acc_id
317 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
318 END IF;
319
320 JTM_HOOK_UTIL_PKG.Update_Acc
321 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
322 ,P_ACC_TABLE_NAME => g_acc_table_name
323 ,P_RESOURCE_ID => p_resource_id
324 ,P_ACCESS_ID => p_acc_id
325 );
326
327 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
328 jtm_message_log_pkg.Log_Msg
329 ( p_task_id
330 , g_table_name
331 , 'Leaving Update_ACC_Record'
332 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
333 END IF;
334 END Update_ACC_Record;
335
336 /*** Private procedure that deletes task for resource from acc table ***/
337 PROCEDURE Delete_ACC_Record
338 ( p_task_id IN NUMBER
339 ,p_resource_id IN NUMBER
340 ,p_flow_type IN NUMBER
341 )
342 IS
343 CURSOR c_task( b_task_id NUMBER ) IS
344 SELECT *
345 FROM JTF_TASKS_B
346 WHERE TASK_ID = b_task_id;
347
348 r_task c_task%ROWTYPE;
349
350 BEGIN
351 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
352 jtm_message_log_pkg.Log_Msg
353 ( p_task_id
354 , g_table_name
355 , 'Entering Delete_ACC_Record'
356 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
357 END IF;
358
359 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
360 jtm_message_log_pkg.Log_Msg
361 ( p_task_id
362 , g_table_name
363 , 'Deleting ACC record for resource_id = ' || p_resource_id
364 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
365 END IF;
366
367 /*** Delete task ACC record ***/
368 JTM_HOOK_UTIL_PKG.Delete_Acc
369 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
370 ,P_ACC_TABLE_NAME => g_acc_table_name
371 ,P_PK1_NAME => g_pk1_name
372 ,P_PK1_NUM_VALUE => p_task_id
373 ,P_RESOURCE_ID => p_resource_id
374 );
375
376 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
377 jtm_message_log_pkg.Log_Msg
378 ( p_task_id
379 , g_table_name
380 , 'Deleting Non-critical dependant records'
381 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
382 END IF;
383
384 /*Notes are not replicated for history so we do not need to delete them*/
385 IF p_flow_type <> CSL_CS_INCIDENTS_ALL_ACC_PKG.G_FLOW_HISTORY THEN
386 --NOTES
387 CSL_JTF_NOTES_ACC_PKG.POST_DELETE_CHILDREN( P_SOURCE_OBJ_ID => p_task_id
388 , P_SOURCE_OBJ_CODE => 'TASK'
389 , P_RESOURCE_ID => p_resource_id );
390 END IF;
391 --PARTY_SITE
392 OPEN c_task( b_task_id => p_task_id );
393 FETCH c_task INTO r_task;
394 IF c_task%FOUND THEN
395 --fix for bug 2472668: check if address_id is null
396 IF r_task.address_id IS NOT NULL THEN
397 CSL_HZ_PARTY_SITES_ACC_PKG.DELETE_PARTY_SITE( p_party_site_id => r_task.ADDRESS_ID
398 , p_resource_id => p_resource_id );
399 END IF;
400
401 --Bug 3724142
402 --ATTACHMENTS
403 /*CSL_LOBS_ACC_PKG.DELETE_ATTACHMENTS ( p_entity_name => 'JTF_TASKS_B',
404 p_primary_key => p_task_id,
405 p_resource_id => p_resource_id);*/
406
407 END IF;
408 CLOSE c_task;
409
410 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
411 jtm_message_log_pkg.Log_Msg
412 ( p_task_id
413 , g_table_name
414 , 'Leaving Delete_ACC_Record'
415 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
416 END IF;
417 END Delete_ACC_Record;
418
419 /***
420 Public function that gets called when a task needs to be inserted into ACC table.
421 Returns TRUE when record already was or has been inserted into ACC table.
422 ***/
423 FUNCTION Pre_Insert_Child
424 ( p_task_id IN NUMBER
425 ,p_resource_id IN NUMBER
426 ,p_flow_type IN NUMBER ) --DEFAULT CSL_CS_INCIDENTS_ALL_ACC_PKG.G_FLOW_NORMAL
427 RETURN BOOLEAN
428 IS
429 l_acc_id NUMBER;
430 l_success BOOLEAN;
431 l_incident_id NUMBER;
432 BEGIN
433 /*** get debug level ***/
434 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
435
436 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
437 jtm_message_log_pkg.Log_Msg
438 ( p_task_id
439 , g_table_name
440 , 'Entering Pre_Insert_Child procedure'
441 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
442 END IF;
443
444 l_success := FALSE;
445
446 /*** is this a history record? ***/
447 IF p_flow_type = CSL_CS_INCIDENTS_ALL_ACC_PKG.G_FLOW_HISTORY THEN
448 /*** yes -> replicate without further checking ***/
449 l_success := TRUE;
450 ELSE
451 /*** SR task? ***/
452 IF Replicate_Record( p_task_id ) THEN
453 /*** yes -> is this an SR task? ***/
454 l_incident_id := GetParentId( p_task_id);
455 IF l_incident_id = -1 THEN
456 /*** no -> replicate personal task ***/
457 l_success := TRUE;
458 ELSE
459 /*** yes -> insert parent SR ***/
460 IF CSL_CS_INCIDENTS_ALL_ACC_PKG.Pre_Insert_Child( l_incident_id, p_resource_id, p_flow_type) THEN
461 /*** yes -> replicate task ***/
462 l_success := TRUE;
463 END IF;
464 END IF;
465 END IF;
466 END IF;
467
468 IF l_success THEN
469 /*** successful -> insert task acc record ***/
470 Insert_ACC_Record
471 ( p_task_id
472 , p_resource_id
473 , p_flow_type
474 );
475 END IF;
476
477 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
478 jtm_message_log_pkg.Log_Msg
479 ( p_task_id
480 , g_table_name
481 , 'Leaving Pre_Insert_Child procedure'
482 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
483 END IF;
484
485 RETURN l_success;
486 END Pre_Insert_Child;
487
488 /***
489 Public procedure that gets called when a task needs to be deleted from ACC table.
490 ***/
491 PROCEDURE Post_Delete_Child
492 ( p_task_id IN NUMBER
493 ,p_resource_id IN NUMBER
494 ,p_flow_type IN NUMBER )--DEFAULT CSL_CS_INCIDENTS_ALL_ACC_PKG.G_FLOW_NORMAL
495 IS
496 l_incident_id NUMBER;
497 BEGIN
498 /*** get debug level ***/
499 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
500
501 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
502 jtm_message_log_pkg.Log_Msg
503 ( p_task_id
504 , g_table_name
505 , 'Entering Post_Delete_Child'
506 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
507 END IF;
508
509 /*** delete task record from ACC ***/
510 Delete_ACC_Record
511 ( p_task_id
512 , p_resource_id
513 , p_flow_type);
514
515 /*** call delete service request ***/
516 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
517 jtm_message_log_pkg.Log_Msg
518 ( p_task_id
519 , g_table_name
520 , 'Calling CSL_CS_INCIDENTS_ALL_ACC_PKG.Post_Delete_Child'
521 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
522 END IF;
523
524 /*Do not delete sr when history, flow is sr -> task -> assignment */
525 IF p_flow_type <> CSL_CS_INCIDENTS_ALL_ACC_PKG.G_FLOW_HISTORY THEN
526 /* delete the dependant SR */
527 l_incident_id := GetParentId( p_task_id);
528 IF l_incident_id <> -1 THEN
529 CSL_CS_INCIDENTS_ALL_ACC_PKG.Post_Delete_Child( l_incident_id, p_resource_id );
530 END IF;
531 END IF;
532
533 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
534 jtm_message_log_pkg.Log_Msg
535 ( p_task_id
536 , g_table_name
537 , 'Leaving Post_Delete_Child'
538 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
539 END IF;
540 END Post_Delete_Child;
541
542
543 /* Called before task Insert */
544 PROCEDURE PRE_INSERT_TASK
545 ( x_return_status OUT NOCOPY varchar2
546 )
547 IS
548 BEGIN
549 x_return_status := FND_API.G_RET_STS_SUCCESS;
550 END PRE_INSERT_TASK;
551
552 /* Called after task Insert */
553 PROCEDURE POST_INSERT_TASK
554 ( x_return_status OUT NOCOPY varchar2
555 )
556 IS
557 l_task_id NUMBER;
558 CURSOR c_task( b_task_id NUMBER ) IS
559 SELECT tk.SOURCE_OBJECT_TYPE_CODE
560 , au.RESOURCE_ID
561 FROM JTF_TASKS_B tk
562 , ASG_USER au
563 WHERE tk.TASK_ID = b_task_id
564 AND tk.CREATED_BY = au.USER_ID;
565 r_task c_task%ROWTYPE;
566 l_enabled_flag VARCHAR2(30);
567 BEGIN
568 l_enabled_flag := JTM_PROFILE_UTL_PKG.GET_ENABLE_FLAG_AT_RESP( P_APP_SHORT_NAME => 'CSL' );
569 IF l_enabled_flag <> 'Y' THEN
570 x_return_status := FND_API.G_RET_STS_SUCCESS;
571 RETURN;
572 END IF;
573 /*** get debug level ***/
574 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
575
576 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
577 jtm_message_log_pkg.Log_Msg
578 ( l_task_id
579 , g_table_name
580 , 'Entering POST_INSERT_TASK hook'
581 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
582 END IF;
583
584 l_task_id := jtf_tasks_pub.p_task_user_hooks.task_id;
585 OPEN c_task( l_task_id );
586 FETCH c_task INTO r_task;
587 IF c_task%FOUND THEN
588 IF r_task.SOURCE_OBJECT_TYPE_CODE = 'SR' AND
589 JTM_HOOK_UTIL_PKG.isMobileFSresource(r_task.RESOURCE_ID) = TRUE THEN
590 Insert_ACC_Record
591 ( l_task_id
592 , r_task.RESOURCE_ID
593 , CSL_CS_INCIDENTS_ALL_ACC_PKG.G_FLOW_NORMAL
594 );
595 END IF;--Type = SR
596 END IF;--c_task%FOUND
597 CLOSE c_task;
598
599 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
600 jtm_message_log_pkg.Log_Msg
601 ( l_task_id
602 , g_table_name
603 , 'Leaving POST_INSERT_TASK hook'
604 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
605 END IF;
606
607 x_return_status := FND_API.G_RET_STS_SUCCESS;
608 EXCEPTION WHEN OTHERS THEN
609 /*** hook failed -> log error ***/
610 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
611 jtm_message_log_pkg.Log_Msg
612 ( l_task_id
613 , g_table_name
614 , 'Caught exception in POST_INSERT_TASK hook:' || fnd_global.local_chr(10) || sqlerrm
615 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
616 END IF;
617 fnd_msg_pub.Add_Exc_Msg('CSL_JTF_TASKS_ACC_PKG','POST_INSERT_TASK',sqlerrm);
618 -- x_return_status := FND_API.G_RET_STS_ERROR;
619 x_return_status := FND_API.G_RET_STS_SUCCESS;
620 END POST_INSERT_TASK;
621
622 /* Called before task Update */
623 PROCEDURE PRE_UPDATE_TASK
624 ( x_return_status OUT NOCOPY varchar2
625 )
626 IS
627 l_task_id NUMBER;
628 l_enabled_flag VARCHAR2(30);
629 CURSOR c_task_address( b_task_id NUMBER ) IS
630 SELECT ADDRESS_ID
631 FROM JTF_TASKS_B
632 WHERE TASK_ID = b_task_id;
633 BEGIN
634 l_enabled_flag := JTM_PROFILE_UTL_PKG.GET_ENABLE_FLAG_AT_RESP( P_APP_SHORT_NAME => 'CSL');
635 IF l_enabled_flag <> 'Y' THEN
636 x_return_status := FND_API.G_RET_STS_SUCCESS;
637 RETURN;
638 END IF;
639 /*** get task record details from public API ***/
640 l_task_id := jtf_tasks_pub.p_task_user_hooks.task_id;
641
642 /*** get debug level ***/
643 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
644
645 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
646 jtm_message_log_pkg.Log_Msg
647 ( l_task_id
648 , g_table_name
649 , 'Entering PRE_UPDATE hook'
650 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
651 END IF;
652
653 /*** Check if task before update matched criteria ***/
654 g_replicate_pre_update := Replicate_Record( l_task_id );
655 /* Cache the address id ( might change ) */
656 OPEN c_task_address( l_task_id );
657 FETCH c_task_address INTO g_cached_task_address_id;
658 CLOSE c_task_address;
659
660 x_return_status := FND_API.G_RET_STS_SUCCESS;
661
662 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
663 jtm_message_log_pkg.Log_Msg
664 ( l_task_id
665 , g_table_name
666 , 'Leaving PRE_UPDATE hook'
667 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
668 END IF;
669
670 EXCEPTION WHEN OTHERS THEN
671 /*** hook failed -> log error ***/
672 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
673 jtm_message_log_pkg.Log_Msg
674 ( l_task_id
675 , g_table_name
676 , 'Caught exception in PRE_UPDATE hook:' || fnd_global.local_chr(10) || sqlerrm
677 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
678 END IF;
679 fnd_msg_pub.Add_Exc_Msg('CSL_JTF_TASKS_ACC_PKG','POST_UPDATE_TASK',sqlerrm);
680 -- x_return_status := FND_API.G_RET_STS_ERROR;
681 x_return_status := FND_API.G_RET_STS_SUCCESS;
682 END PRE_UPDATE_TASK;
683
684 /* Called after task Update */
685 PROCEDURE POST_UPDATE_TASK
686 ( x_return_status OUT NOCOPY varchar2
687 )
688 IS
689 CURSOR c_task_assignment( b_task_id NUMBER )
690 IS
691 SELECT task_assignment_id, resource_id
692 FROM jtf_task_assignments
693 WHERE task_id = b_task_id;
694 r_task_assignment c_task_assignment%ROWTYPE;
695
696 CURSOR c_task_address( b_task_id NUMBER ) IS
697 SELECT ADDRESS_ID
698 FROM JTF_TASKS_B
699 WHERE TASK_ID = b_task_id;
700
701 l_address_id NUMBER;
702 l_address_changed BOOLEAN := FALSE;
703 l_task_id NUMBER;
704 l_replicate BOOLEAN;
705 l_dummy BOOLEAN;
706
707 l_tab_resource_id dbms_sql.Number_Table;
708 l_tab_access_id dbms_sql.Number_Table;
709 l_enabled_flag VARCHAR2(30);
710 BEGIN
711 l_enabled_flag := JTM_PROFILE_UTL_PKG.GET_ENABLE_FLAG_AT_RESP( P_APP_SHORT_NAME => 'CSL');
712 IF l_enabled_flag <> 'Y' THEN
713 x_return_status := FND_API.G_RET_STS_SUCCESS;
714 RETURN;
715 END IF;
716 /*** get task record details from public API ***/
717 l_task_id := jtf_tasks_pub.p_task_user_hooks.task_id;
718
719 /*** get debug level ***/
720 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
721
722 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
723 jtm_message_log_pkg.Log_Msg
724 ( l_task_id
725 , g_table_name
726 , 'Entering POST_UPDATE hook'
727 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
728 END IF;
729
730 /*** Check if task after update matches criteria ***/
731 l_replicate := Replicate_Record( l_task_id );
732
733 /*** replicate record after update? ***/
734 IF l_replicate THEN
735 /*** yes -> was record already replicated? ***/
736 IF g_replicate_pre_update THEN
737 /*** yes -> re-send updated task record to all resources ***/
738 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
739 jtm_message_log_pkg.Log_Msg
740 ( l_task_id
741 , g_table_name
742 , 'Task was replicateable before and after update. Re-sending task record to mobile users.'
743 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
744 END IF;
745 /*** get list of resources to whom the record was replicated ***/
746 JTM_HOOK_UTIL_PKG.Get_Resource_Acc_List
747 ( P_ACC_TABLE_NAME => g_acc_table_name
748 ,P_PK1_NAME => g_pk1_name
749 ,P_PK1_NUM_VALUE => l_task_id
750 ,L_TAB_RESOURCE_ID => l_tab_resource_id
751 ,L_TAB_ACCESS_ID => l_tab_access_id
752 );
753
754 /*Check if address is changed if so also chage address in acc table*/
755 OPEN c_task_address( l_task_id );
756 FETCH c_task_address INTO l_address_id;
757 IF c_task_address%FOUND THEN
758 IF l_address_id <> g_cached_task_address_id AND l_address_id IS NOT NULL THEN
759 l_address_changed := TRUE;
760 END IF;
761 END IF;
762 CLOSE c_task_address;
763 /*** re-send rec to all resources ***/
764 IF l_tab_resource_id.COUNT > 0 THEN
765 FOR i IN l_tab_resource_id.FIRST .. l_tab_resource_id.LAST LOOP
766 Update_ACC_Record
767 ( l_task_id
768 ,l_tab_resource_id(i)
769 ,l_tab_access_id(i)
770 );
771 IF l_address_changed = TRUE THEN
772 /*Address changed check if there was an address*/
773 IF g_cached_task_address_id IS NOT NULL THEN
774 CSL_HZ_PARTY_SITES_ACC_PKG.CHANGE_PARTY_SITE( g_cached_task_address_id
775 , l_address_id
776 , l_tab_resource_id(i));
777 ELSE
778 CSL_HZ_PARTY_SITES_ACC_PKG.INSERT_PARTY_SITE( l_address_id
779 , l_tab_resource_id(i));
780 END IF;--g_cached not null
781 END IF;--addess changed
782 END LOOP;
783 END IF;
784 ELSE
785 /***
786 record was not replicated before update but should be replicated now ->
787 send record related data to all resources
788 ***/
789 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
790 jtm_message_log_pkg.Log_Msg
791 ( l_task_id
792 , g_table_name
793 , 'Task was not replicated before update, but should be replicated now.'
794 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
795 END IF;
796
797 FOR r_task_assignment IN c_task_assignment( l_task_id ) LOOP
798
799 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
800 jtm_message_log_pkg.Log_Msg
801 ( l_task_id
802 , g_table_name
803 , 'Evaluating task_assignment_id ' ||
804 r_task_assignment.task_assignment_id || fnd_global.local_chr(10) ||
805 'for resource_id ' || r_task_assignment.resource_id
806 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
807 END IF;
808
809 /*** insert task assignment data ***/
810 l_dummy := CSL_JTF_TASK_ASS_ACC_PKG.Pre_Insert_Child
811 ( r_task_assignment.task_assignment_id
812 ,r_task_assignment.resource_id
813 );
814 END LOOP;
815 END IF;
816 ELSE
817 /*** record should not be replicated anymore -> was it replicated before? ***/
818 IF g_replicate_pre_update THEN
819 /*** yes -> delete record related data for all resources ***/
820 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
821 jtm_message_log_pkg.Log_Msg
822 ( l_task_id
823 , g_table_name
824 , 'Task was replicated before update, but should no longer be replicated.'
825 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
826 END IF;
827
828 FOR r_task_assignment IN c_task_assignment( l_task_id ) LOOP
829
830 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
831 jtm_message_log_pkg.Log_Msg
832 ( l_task_id
833 , g_table_name
834 , 'Evaluating task_assignment_id ' || r_task_assignment.task_assignment_id ||
835 fnd_global.local_chr(10) ||'for resource_id ' || r_task_assignment.resource_id
836 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
837 END IF;
838
839 /*** delete task assignment data ***/
840 CSL_JTF_TASK_ASS_ACC_PKG.Post_Delete_Child
841 ( r_task_assignment.task_assignment_id
842 ,r_task_assignment.resource_id
843 );
844 END LOOP;
845 END IF;
846 END IF;
847
848 x_return_status := FND_API.G_RET_STS_SUCCESS;
849
850 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
851 jtm_message_log_pkg.Log_Msg
852 ( l_task_id
853 , g_table_name
854 , 'Leaving POST_UPDATE hook'
855 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
856 END IF;
857
858 EXCEPTION WHEN OTHERS THEN
859 /*** hook failed -> log error ***/
860 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
861 jtm_message_log_pkg.Log_Msg
862 ( l_task_id
863 , g_table_name
864 , 'Caught exception in POST_UPDATE hook:' || fnd_global.local_chr(10) || sqlerrm
865 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
866 END IF;
867 fnd_msg_pub.Add_Exc_Msg('CSL_JTF_TASKS_ACC_PKG','POST_UPDATE_TASK',sqlerrm);
868 -- x_return_status := FND_API.G_RET_STS_ERROR;
869 x_return_status := FND_API.G_RET_STS_SUCCESS;
870 END POST_UPDATE_TASK;
871
872 /* Called before task Delete */
873 PROCEDURE PRE_DELETE_TASK
874 ( x_return_status OUT NOCOPY varchar2
875 )
876 IS
877 BEGIN
878 x_return_status := FND_API.G_RET_STS_SUCCESS;
879 END PRE_DELETE_TASK;
880
881 /* Called after task Delete */
882 PROCEDURE POST_DELETE_TASK
883 ( x_return_status OUT NOCOPY varchar2
884 )
885 IS
886 BEGIN
887 x_return_status := FND_API.G_RET_STS_SUCCESS;
888 END POST_DELETE_TASK;
889
890 PROCEDURE INSERT_ALL_ACC_RECORDS
891 ( p_resource_id IN NUMBER
892 , x_return_status OUT NOCOPY VARCHAR2 ) IS
893
894 CURSOR c_task( b_resource_id NUMBER ) IS
895 SELECT tk.task_id
896 FROM JTF_TASKS_B tk
897 , ASG_USER au
898 WHERE tk.SOURCE_OBJECT_TYPE_CODE = 'SR'
899 AND tk.CREATED_BY = au.USER_ID
900 AND au.RESOURCE_ID = b_resource_id;
901 r_task c_task%ROWTYPE;
902 BEGIN
903 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
904 jtm_message_log_pkg.Log_Msg
905 ( p_resource_id
906 , g_table_name
907 , 'Entering POST_INSERT hook'
908 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
909 END IF;
910
911 /*** insert all tasks created by resource ***/
912 FOR r_task IN c_task( p_resource_id ) LOOP
913 Insert_ACC_Record
914 ( r_task.task_id
915 , p_resource_id
916 , CSL_CS_INCIDENTS_ALL_ACC_PKG.G_FLOW_NORMAL
917 );
918 END LOOP;
919
920 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
921 jtm_message_log_pkg.Log_Msg
922 ( p_resource_id
923 , g_table_name
924 , 'Leaving POST_INSERT hook'
925 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
926 END IF;
927
928 x_return_status := FND_API.G_RET_STS_SUCCESS;
929
930 EXCEPTION
931 WHEN OTHERS THEN
932 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
933 jtm_message_log_pkg.Log_Msg
934 ( p_resource_id
935 , g_table_name
936 , 'Caught exception in INSERT_ALL_ACC_RECORDS:' || fnd_global.local_chr(10) || sqlerrm
937 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
938 END IF;
939 x_return_status := FND_API.G_RET_STS_ERROR;
940 END INSERT_ALL_ACC_RECORDS;
941
942 /***Purge logic for Expired/Closed Personal Task***/
943 --Bug 3475657
944 PROCEDURE PURGE_TASKS
945 IS
946
947 CURSOR lcur_purge_tasks IS
948 SELECT acc.task_id, acc.resource_id
949 FROM csl_jtf_tasks_acc acc,
950 jtf_tasks_b jt,
951 jtf_task_statuses_b jts
952 WHERE acc.task_id = jt.task_id
953 AND jts.task_status_id = jt.task_status_id
954 AND (NVL(jt.scheduled_start_date, SYSDATE) < (SYSDATE - TO_NUMBER(FND_PROFILE.Value('CSL_APPL_HISTORY_IN_DAYS'))))
955 AND (NVL(jts.cancelled_flag,'N') = 'Y'
956 OR NVL(jts.closed_flag, 'N') = 'Y'
957 OR NVL(jts.completed_flag, 'N') = 'Y'
958 OR NVL(jts.rejected_flag, 'N') = 'Y')
959 AND source_object_type_code = 'TASK';
960
961 rcur_purge_tasks lcur_purge_tasks%ROWTYPE;
962 BEGIN
963
964 --Delete the Tasks which haven been created on the client and have either expired or been closed/completed.
965 --FOR rcur_purge_tasks in lcur_purge_tasks(p_resource_id)
966 FOR rcur_purge_tasks in lcur_purge_tasks
967 LOOP
968 Post_Delete_Child(rcur_purge_tasks.task_id, rcur_purge_tasks.resource_id);
969 END LOOP;
970
971 EXCEPTION
972
973 WHEN OTHERS THEN
974 jtm_message_log_pkg.Log_Msg
975 (rcur_purge_tasks.task_id,
976 'CSL_JTF_TASK_ASS_ACC',
977 'Exception occured in PURGE_TASKS for id: ' || rcur_purge_tasks.task_id,
978 JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
979
980 END;
981
982 END CSL_JTF_TASKS_ACC_PKG;