[Home] [Help]
PACKAGE BODY: APPS.CSL_JTF_TASK_ASS_ACC_PKG
Source
1 PACKAGE BODY CSL_JTF_TASK_ASS_ACC_PKG AS
2 /* $Header: csltaacb.pls 120.0 2005/05/24 18:02:19 appldev noship $ */
3
4 /*** Globals ***/
5 g_acc_table_name CONSTANT VARCHAR2(30) := 'CSL_JTF_TASK_ASS_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_TASK_ASSIGNMENTS');
8 g_table_name CONSTANT VARCHAR2(30) := 'JTF_TASK_ASSIGNMENTS';
9 g_pk1_name CONSTANT VARCHAR2(30) := 'TASK_ASSIGNMENT_ID';
10 g_old_resource_id NUMBER; -- variable containing old resource_id; populated in Pre_Update hook
11 g_debug_level NUMBER; -- debug level
12
13 /*** Function that checks if assignment record should be replicated. Returns TRUE if it should ***/
14 FUNCTION Replicate_Record
15 ( p_task_assignment_id IN NUMBER
16 , p_flow_type IN NUMBER --DEFAULT CSL_CS_INCIDENTS_ALL_ACC_PKG.G_FLOW_NORMAL
17 )
18 RETURN BOOLEAN
19 IS
20 -- Fix for Bug# 3466610. Added filter to get only those records whose
21 -- Resource Type is Employee Resource.
22 CURSOR c_task_assignment (b_task_assignment_id NUMBER) IS
23 SELECT *
24 FROM JTF_TASK_ASSIGNMENTS -- don't use synonym as that one filters on OWNER records
25 WHERE task_assignment_id = b_task_assignment_id
26 AND resource_type_code = 'RS_EMPLOYEE';
27
28 r_task_assignment c_task_assignment%ROWTYPE;
29
30 CURSOR c_assignment_status (b_assignment_status_id NUMBER) IS
31 SELECT null
32 FROM JTF_TASK_STATUSES_B
33 WHERE TASK_STATUS_ID = b_assignment_status_id
34 AND (
35 NVL(ASSIGNED_FLAG, 'N') = 'Y'
36 OR NVL(CANCELLED_FLAG, 'N') = 'Y'
37 OR NVL(COMPLETED_FLAG, 'N') = 'Y'
38 OR NVL(CLOSED_FLAG, 'N') = 'Y'
39 );
40 r_assignment_status c_assignment_status%ROWTYPE;
41
42 CURSOR c_personal_task( b_task_assignment_id NUMBER ) IS
43 SELECT tk.TASK_ID
44 FROM JTF_TASK_ASSIGNMENTS ta
45 , JTF_TASKS_B tk
46 WHERE tk.TASK_ID = ta.TASK_ID
47 AND tk.SOURCE_OBJECT_TYPE_CODE = 'TASK'
48 AND ta.TASK_ASSIGNMENT_ID = b_task_assignment_id;
49
50 r_personal_task c_personal_task%ROWTYPE;
51
52
53 BEGIN
54 /*** get debug level ***/
55 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
56
57 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
58 jtm_message_log_pkg.Log_Msg
59 ( p_task_assignment_id
60 , g_table_name
61 , 'Entering Replicate_Record'
62 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
63 END IF;
64
65 OPEN c_task_assignment( p_task_assignment_id );
66 FETCH c_task_assignment INTO r_task_assignment;
67 IF c_task_assignment%NOTFOUND THEN
68 /*** could not find assignment record -> exit ***/
69 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
70 jtm_message_log_pkg.Log_Msg
71 ( p_task_assignment_id
72 , g_table_name
73 , 'Replicate_Record error: Could not find task_assignment_id ' || p_task_assignment_id
74 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
75 END IF;
76
77 CLOSE c_task_assignment;
78 RETURN FALSE;
79 END IF;
80 CLOSE c_task_assignment;
81
82 /*** is this an ASSIGNEE task assignment? ***/
83 IF NVL(r_task_assignment.assignee_role,'') <> 'ASSIGNEE' THEN
84 /*** No -> exit ***/
85 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
86 jtm_message_log_pkg.Log_Msg
87 ( p_task_assignment_id
88 , g_table_name
89 , 'Replicate_Record returned FALSE' || fnd_global.local_chr(10) ||
90 'ASSIGNEE_ROLE <> ''ASSIGNEE'''
91 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
92 END IF;
93 RETURN FALSE;
94 END IF;
95
96 IF p_flow_type <> CSL_CS_INCIDENTS_ALL_ACC_PKG.G_FLOW_HISTORY THEN
97 /*** is resource a mobile user? ***/
98 IF NOT JTM_HOOK_UTIL_PKG.isMobileFSresource( r_task_assignment.resource_id ) THEN
99 /*** No -> exit ***/
100 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
101 jtm_message_log_pkg.Log_Msg
102 ( p_task_assignment_id
103 , g_table_name
104 , 'Replicate_Record returned FALSE' || fnd_global.local_chr(10) ||
105 'Resource_id ' || r_task_assignment.resource_id || ' is not a mobile user.'
106 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
107 END IF;
108
109 RETURN FALSE;
110 END IF;
111 END IF;
112
113 OPEN c_personal_task( p_task_assignment_id);
114 FETCH c_personal_task INTO r_personal_task;
115 IF c_personal_task%NOTFOUND THEN
116 /*** not a personal task created by this user so check assignment status ***/
117 OPEN c_assignment_status( r_task_assignment.assignment_status_id );
118 FETCH c_assignment_status INTO r_assignment_status;
119 IF c_assignment_status%NOTFOUND THEN
120 /*** status should not be replicated -> exit ***/
121 CLOSE c_assignment_status;
122 CLOSE c_personal_task;
123 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
124 jtm_message_log_pkg.Log_Msg
125 ( p_task_assignment_id
126 , g_table_name
127 , 'Replicate_Record returned FALSE' || fnd_global.local_chr(10) ||
128 'Assignment status should have either ASSIGNED_FLAG, CANCELLED_FLAG, '||
129 'COMPLETED_FLAG or CLOSED_FLAG set to ''Y''.'
130 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
131 END IF;
132 RETURN FALSE;
133 END IF;--c_assignment_status%NOTFOUND
134 CLOSE c_assignment_status;
135 END IF; --c_personal_task%NOTFOUND
136 CLOSE c_personal_task;
137
138 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
139 jtm_message_log_pkg.Log_Msg
140 ( p_task_assignment_id
141 , g_table_name
142 , 'Replicate_Record returned TRUE'
143 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
144 END IF;
145
146 /** Record matched criteria -> return true ***/
147 RETURN TRUE;
148 END Replicate_Record;
149
150 /*** Private procedure that replicates given assignment related data for resource ***/
151 PROCEDURE Insert_ACC_Record
152 ( p_task_assignment_id IN NUMBER
153 ,p_resource_id IN NUMBER
154 )
155 IS
156 --Bug 3724142
157 CURSOR c_debrief_header ( b_task_assignment_id NUMBER)
158 IS SELECT debrief_header_id FROM csf_debrief_headers
159 WHERE task_assignment_id = b_task_assignment_id;
160
161 l_debrief_header_id NUMBER;
162
163 BEGIN
164 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
165 jtm_message_log_pkg.Log_Msg
166 ( p_task_assignment_id
167 , g_table_name
168 , 'Entering Insert_ACC_Record'
169 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
170 END IF;
171
172 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
173 jtm_message_log_pkg.Log_Msg
174 ( p_task_assignment_id
175 , g_table_name
176 , 'Inserting ACC record for resource_id = ' || p_resource_id
177 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
178 END IF;
179
180 JTM_HOOK_UTIL_PKG.Insert_Acc
181 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
182 ,P_ACC_TABLE_NAME => g_acc_table_name
183 ,P_PK1_NAME => g_pk1_name
184 ,P_PK1_NUM_VALUE => p_task_assignment_id
185 ,P_RESOURCE_ID => p_resource_id
186 );
187
188 /* insert debrief */
189 CSL_CSF_DEBRIEF_LINE_ACC_PKG.Pre_Insert_Children
190 ( p_task_assignment_id
191 ,p_resource_id
192 );
193
194 --Bug 3724142
195 OPEN c_debrief_header (p_task_assignment_id);
196 FETCH c_debrief_header INTO l_debrief_header_id;
197 CLOSE c_debrief_header;
198
199 /* insert attachment - signature record */
200 --Bug 3724142 - changed p_task_assignment_id to l_debrief_header_id
201 CSL_LOBS_ACC_PKG.insert_acc_record
202 ( l_debrief_header_id
203 ,p_resource_id
204 );
205
206
207 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
208 jtm_message_log_pkg.Log_Msg
209 ( p_task_assignment_id
210 , g_table_name
211 , 'Leaving Insert_ACC_Record'
212 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
213 END IF;
214 END Insert_ACC_Record;
215
216 /*** Private procedure that re-sends given assignment to mobile ***/
217 PROCEDURE Update_ACC_Record
218 ( p_task_assignment_id IN NUMBER
219 ,p_resource_id IN NUMBER
220 ,p_acc_id IN NUMBER
221 )
222 IS
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_assignment_id
227 , g_table_name
228 , 'Entering Update_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_assignment_id
235 , g_table_name
236 , 'Updating ACC record for resource_id = ' || p_resource_id || fnd_global.local_chr(10) || 'access_id = ' || p_acc_id
237 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
238 END IF;
239
240 JTM_HOOK_UTIL_PKG.Update_Acc
241 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
242 ,P_ACC_TABLE_NAME => g_acc_table_name
243 ,P_RESOURCE_ID => p_resource_id
244 ,P_ACCESS_ID => p_acc_id
245 );
246
247 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
248 jtm_message_log_pkg.Log_Msg
249 ( p_task_assignment_id
250 , g_table_name
251 , 'Leaving Update_ACC_Record'
252 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
253 END IF;
254 END Update_ACC_Record;
255
256 /*** Private procedure that deletes assignment for resource from acc table ***/
257 PROCEDURE Delete_ACC_Record
258 ( p_task_assignment_id IN NUMBER
259 ,p_resource_id IN NUMBER
260 )
261 IS
262 BEGIN
263 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
264 jtm_message_log_pkg.Log_Msg
265 ( p_task_assignment_id
266 , g_table_name
267 , 'Entering Delete_ACC_Record'
268 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
269 END IF;
270
271 /* Delete Attachments - Signature */
272 CSL_LOBS_ACC_PKG.delete_acc_record
273 ( p_task_assignment_id
274 ,p_resource_id
275 );
276
277
278 /* delete debrief header, lines */
279 CSL_CSF_DEBRIEF_LINE_ACC_PKG.Post_Delete_Children
280 ( p_task_assignment_id,
281 p_resource_id
282 );
283
284 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
285 jtm_message_log_pkg.Log_Msg
286 ( p_task_assignment_id
287 , g_table_name
288 , 'Deleting ACC record for resource_id = ' || p_resource_id
289 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
290 END IF;
291
292 /*** Delete task assignment ACC record ***/
293 JTM_HOOK_UTIL_PKG.Delete_Acc
294 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
295 ,P_ACC_TABLE_NAME => g_acc_table_name
296 ,P_PK1_NAME => g_pk1_name
297 ,P_PK1_NUM_VALUE => p_task_assignment_id
298 ,P_RESOURCE_ID => p_resource_id
299 );
300
301 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
302 jtm_message_log_pkg.Log_Msg
303 ( p_task_assignment_id
304 , g_table_name
305 , 'Leaving Delete_ACC_Record'
306 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
307 END IF;
308 END Delete_ACC_Record;
309
310 /*** function that returns parent TASK_ID for given TASK_ASSIGNMENT_ID ***/
311 FUNCTION GetParentId( p_task_assignment_id NUMBER)
312 RETURN NUMBER
313 IS
314 CURSOR c_task_assignment( b_task_assignment_id NUMBER )
315 IS
316 SELECT task_id
317 FROM jtf_task_assignments -- don't use synonym as that one filters on OWNER records
318 WHERE task_assignment_id = b_task_assignment_id;
319 r_task_assignment c_task_assignment%ROWTYPE;
320 BEGIN
321 OPEN c_task_assignment( p_task_assignment_id );
322 FETCH c_task_assignment INTO r_task_assignment;
323 IF c_task_assignment%NOTFOUND THEN
324 /*** could not find assignment record -> exit ***/
325 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
326 CLOSE c_task_assignment;
327 jtm_message_log_pkg.Log_Msg
328 ( p_task_assignment_id
329 , g_table_name
330 , 'Post_Delete_Child error: Could not find task_assignment_id ' || p_task_assignment_id
331 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
332 -- RAISE_APPLICATION_ERROR(-20000, 'Post_Delete_Child error: Could not find task_assignment_id ' || p_task_assignment_id);
333 RETURN -1;
334 END IF;
335 END IF;
336 CLOSE c_task_assignment;
337 /*** found assignment -> return task_id ***/
338 RETURN r_task_assignment.task_id;
339 END GetParentId;
340
341 /***
342 Public function that gets called when an assignment needs to be inserted into ACC table.
343 Returns TRUE when record already was or has been inserted into ACC table.
344 ***/
345 FUNCTION Pre_Insert_Child
346 ( p_task_assignment_id IN NUMBER
347 ,p_resource_id IN NUMBER
348 ,p_flow_type IN NUMBER --DEFAULT CSL_CS_INCIDENTS_ALL_ACC_PKG.G_FLOW_NORMAL
349 )
350 RETURN BOOLEAN
351 IS
352 l_success BOOLEAN;
353 BEGIN
354 /*** get debug level ***/
355 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
356
357 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
358 jtm_message_log_pkg.Log_Msg
359 ( p_task_assignment_id
360 , g_table_name
361 , 'Entering Pre_Insert_Child procedure'
362 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
363 END IF;
364
365 l_success := FALSE;
366 /*** no -> does record match criteria? ***/
367 IF Replicate_Record( p_task_assignment_id, p_flow_type ) THEN
368
369 IF p_flow_type <> CSL_CS_INCIDENTS_ALL_ACC_PKG.G_FLOW_HISTORY THEN
370 /*** yes -> is insert task acc record successful? ***/
371 IF CSL_JTF_TASKS_ACC_PKG.Pre_Insert_Child( GetParentId( p_task_assignment_id), p_resource_id) THEN
372 /*** yes -> insert assignment acc record ***/
373 Insert_ACC_Record
374 ( p_task_assignment_id
375 ,p_resource_id
376 );
377 l_success := TRUE;
378 END IF;
379 ELSE
380 Insert_ACC_Record
381 ( p_task_assignment_id
382 ,p_resource_id
383 );
384 l_success := TRUE;
385 END IF;
386 END IF;
387
388 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
389 jtm_message_log_pkg.Log_Msg
393 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
390 ( p_task_assignment_id
391 , g_table_name
392 , 'Leaving Pre_Insert_Child procedure'
394 END IF;
395
396 RETURN l_success;
397 END Pre_Insert_Child;
398
399 /***
400 Public procedure that gets called when an assignment needs to be deleted from ACC table.
401 ***/
402 PROCEDURE Post_Delete_Child
403 ( p_task_assignment_id IN NUMBER
404 ,p_resource_id IN NUMBER
405 ,p_flow_type IN NUMBER --DEFAULT CSL_CS_INCIDENTS_ALL_ACC_PKG.G_FLOW_NORMAL
406 )
407 IS
408 l_acc_id NUMBER;
409 BEGIN
410 /*** get debug level ***/
411 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
412
413 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
414 jtm_message_log_pkg.Log_Msg
415 ( p_task_assignment_id
416 , g_table_name
417 , 'Entering Post_Delete_Child'
418 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
419 END IF;
420
421 /*** does record exist in ACC table? ***/
422 l_acc_id := JTM_HOOK_UTIL_PKG.Get_Acc_Id
423 ( P_ACC_TABLE_NAME => g_acc_table_name
424 ,P_PK1_NAME => g_pk1_name
425 ,P_PK1_NUM_VALUE => p_task_assignment_id
426 ,P_RESOURCE_ID => p_resource_id);
427
428 IF l_acc_id > -1 THEN
429 /*** yes -> delete assignment record from ACC ***/
430 Delete_ACC_Record
431 ( p_task_assignment_id
432 ,p_resource_id);
433
434 /*** call delete task ***/
435 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
436 jtm_message_log_pkg.Log_Msg
437 ( p_task_assignment_id
438 , g_table_name
439 , 'Calling CSL_JTF_TASKS_ACC_PKG.Post_Delete_Child'
440 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
441 END IF;
442 /*If history flow is SR -> task -> assignment, so no need to call tasks again*/
443 IF p_flow_type <> CSL_CS_INCIDENTS_ALL_ACC_PKG.G_FLOW_HISTORY THEN
444 CSL_JTF_TASKS_ACC_PKG.Post_Delete_Child( GetParentId( p_task_assignment_id), p_resource_id );
445 END IF;
446 END IF;
447
448 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
449 jtm_message_log_pkg.Log_Msg
450 ( p_task_assignment_id
451 , g_table_name
452 , 'Leaving Post_Delete_Child'
453 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
454 END IF;
455 END Post_Delete_Child;
456
457
458 /* Called during user creation */
459 PROCEDURE INSERT_ALL_ACC_RECORDS( p_resource_id IN NUMBER, x_return_status OUT NOCOPY VARCHAR2 ) IS
460 CURSOR c_task_assignment( b_resource_id NUMBER ) IS
461 SELECT TASK_ASSIGNMENT_ID
462 FROM JTF_TASK_ASSIGNMENTS
463 WHERE ASSIGNEE_ROLE = 'ASSIGNEE'
464 AND RESOURCE_ID = b_resource_id;
465 l_return_status BOOLEAN;
466 BEGIN
467 FOR r_task_assignment IN c_task_assignment( p_resource_id ) LOOP
468 l_return_status := Pre_Insert_Child
469 ( r_task_assignment.task_assignment_id
470 ,p_resource_id);
471 END LOOP;
472 x_return_status := FND_API.G_RET_STS_SUCCESS;
473 EXCEPTION
474 WHEN OTHERS THEN
475 -- x_return_status := FND_API.G_RET_STS_ERROR;
476 x_return_status := FND_API.G_RET_STS_SUCCESS;
477 END INSERT_ALL_ACC_RECORDS;
478
479 /* Called during deletion of a user */
480 PROCEDURE DELETE_ALL_ACC_RECORDS( p_resource_id IN NUMBER, x_return_status OUT NOCOPY VARCHAR2 ) IS
481 CURSOR c_task_assignment( b_resource_id NUMBER ) IS
482 SELECT TASK_ASSIGNMENT_ID
483 FROM JTF_TASK_ASSIGNMENTS
484 WHERE ASSIGNEE_ROLE = 'ASSIGNEE'
485 AND RESOURCE_ID = b_resource_id;
486 BEGIN
487 FOR r_task_assignment IN c_task_assignment( p_resource_id ) LOOP
488 Post_Delete_Child
489 ( r_task_assignment.task_assignment_id
490 ,p_resource_id);
491 END LOOP;
492 x_return_status := FND_API.G_RET_STS_SUCCESS;
493 EXCEPTION
494 WHEN OTHERS THEN
495 -- x_return_status := FND_API.G_RET_STS_ERROR;
496 x_return_status := FND_API.G_RET_STS_SUCCESS;
497 END DELETE_ALL_ACC_RECORDS;
498
499
500 /*** Called before assignment Insert ***/
501 PROCEDURE PRE_INSERT_TASK_ASSIGNMENT
502 ( x_return_status OUT NOCOPY varchar2
503 )
504 IS
505 BEGIN
506 x_return_status := FND_API.G_RET_STS_SUCCESS;
507 END PRE_INSERT_TASK_ASSIGNMENT;
508
509 /*** Called after assignment Insert ***/
510 PROCEDURE POST_INSERT_TASK_ASSIGNMENT
511 ( x_return_status OUT NOCOPY varchar2
512 )
513 IS
514 l_resource_id NUMBER;
515 l_task_assignment_id NUMBER;
516 l_dummy BOOLEAN;
517 l_enabled_flag VARCHAR2(30);
518 BEGIN
519 l_enabled_flag := JTM_PROFILE_UTL_PKG.GET_ENABLE_FLAG_AT_RESP(P_APP_SHORT_NAME => 'CSL' );
520 IF l_enabled_flag <> 'Y' THEN
521 x_return_status := FND_API.G_RET_STS_SUCCESS;
522 RETURN;
523 END IF;
524 /*** get assignment record details from public API ***/
525 l_task_assignment_id := jtf_task_assignments_pub.p_task_assignments_user_hooks.task_assignment_id;
526 l_resource_id := jtf_task_assignments_pub.p_task_assignments_user_hooks.resource_id;
527
528 /*** get debug level ***/
529 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
530
531 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
535 , 'Entering POST_INSERT hook'
532 jtm_message_log_pkg.Log_Msg
533 ( l_task_assignment_id
534 , g_table_name
536 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
537 END IF;
538
539 /*** Insert record if applicable ***/
540 l_dummy := Pre_Insert_Child
541 ( l_task_assignment_id
542 ,l_resource_id
543 );
544
545 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
546 jtm_message_log_pkg.Log_Msg
547 ( l_task_assignment_id
548 , g_table_name
549 , 'Leaving POST_INSERT hook'
550 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
551 END IF;
552
553 x_return_status := FND_API.G_RET_STS_SUCCESS;
554 RETURN;
555
556 EXCEPTION WHEN OTHERS THEN
557 /*** hook failed -> log error ***/
558 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
559 jtm_message_log_pkg.Log_Msg
560 ( l_task_assignment_id
561 , g_table_name
562 , 'Caught exception in POST_INSERT hook:' || fnd_global.local_chr(10) || sqlerrm
563 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
564 END IF;
565 fnd_msg_pub.Add_Exc_Msg('CSL_JTF_TASK_ASS_ACC_PKG','POST_INSERT_TASK_ASSIGNMENT',sqlerrm);
566 -- x_return_status := FND_API.G_RET_STS_ERROR;
567 x_return_status := FND_API.G_RET_STS_SUCCESS;
568 END POST_INSERT_TASK_ASSIGNMENT;
569
570 /* Called before assignment Update */
571 PROCEDURE PRE_UPDATE_TASK_ASSIGNMENT
572 ( x_return_status OUT NOCOPY varchar2
573 )
574 IS
575 CURSOR c_task_assignment( b_task_assignment_id NUMBER ) IS
576 SELECT resource_id
577 FROM jtf_task_assignments -- don't use synonym as that one filters on OWNER records
578 WHERE task_assignment_id = b_task_assignment_id;
579
580 r_task_assignment c_task_assignment%ROWTYPE;
581 l_task_assignment_id NUMBER;
582 l_enabled_flag VARCHAR2(30);
583 BEGIN
584 l_enabled_flag := JTM_PROFILE_UTL_PKG.GET_ENABLE_FLAG_AT_RESP(P_APP_SHORT_NAME => 'CSL' );
585 IF l_enabled_flag <> 'Y' THEN
586 x_return_status := FND_API.G_RET_STS_SUCCESS;
587 RETURN;
588 END IF;
589 /*** get assignment record details from public API ***/
590 l_task_assignment_id := jtf_task_assignments_pub.p_task_assignments_user_hooks.task_assignment_id;
591
592 /*** get debug level ***/
593 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
594
595 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
596 jtm_message_log_pkg.Log_Msg
597 ( l_task_assignment_id
598 , g_table_name
599 , 'Entering PRE_UPDATE hook'
600 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
601 END IF;
602
603 /*** retrieve old resource_id for task assignment ***/
604 OPEN c_task_assignment(l_task_assignment_id);
605 FETCH c_task_assignment INTO r_task_assignment;
606 g_old_resource_id := r_task_assignment.resource_id;
607 CLOSE c_task_assignment;
608
609 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
610 jtm_message_log_pkg.Log_Msg
611 ( l_task_assignment_id
612 , g_table_name
613 , 'Leaving PRE_UPDATE hook'
614 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
615 END IF;
616
617 x_return_status := FND_API.G_RET_STS_SUCCESS;
618
619 EXCEPTION WHEN OTHERS THEN
620 /*** hook failed -> log error ***/
621 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
622 jtm_message_log_pkg.Log_Msg
623 ( l_task_assignment_id
624 , g_table_name
625 , 'Caught exception in PRE_UPDATE hook:' || fnd_global.local_chr(10) || sqlerrm
626 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
627 END IF;
628 fnd_msg_pub.Add_Exc_Msg('CSL_JTF_TASK_ASS_ACC_PKG','PRE_UPDATE_TASK_ASSIGNMENT',sqlerrm);
629 -- x_return_status := FND_API.G_RET_STS_ERROR;
630 x_return_status := FND_API.G_RET_STS_SUCCESS;
631 END PRE_UPDATE_TASK_ASSIGNMENT;
632
633 /* Called after assignment Update */
634 PROCEDURE POST_UPDATE_TASK_ASSIGNMENT
635 ( x_return_status OUT NOCOPY varchar2
636 )
637 IS
638 l_resource_id NUMBER;
639 l_task_assignment_id NUMBER;
640 l_acc_id NUMBER;
641 l_replicate BOOLEAN;
642 l_dummy BOOLEAN;
643 l_enabled_flag VARCHAR2(30);
644 BEGIN
645 l_enabled_flag := JTM_PROFILE_UTL_PKG.GET_ENABLE_FLAG_AT_RESP(P_APP_SHORT_NAME => 'CSL' );
646 IF l_enabled_flag <> 'Y' THEN
647 x_return_status := FND_API.G_RET_STS_SUCCESS;
648 RETURN;
649 END IF;
650 /*** get assignment record details from public API ***/
651 l_task_assignment_id := jtf_task_assignments_pub.p_task_assignments_user_hooks.task_assignment_id;
652 l_resource_id := jtf_task_assignments_pub.p_task_assignments_user_hooks.resource_id;
653
654 /*** get debug level ***/
655 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
656
657 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
658 jtm_message_log_pkg.Log_Msg
659 ( l_task_assignment_id
660 , g_table_name
661 , 'Entering POST_UPDATE hook'
662 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
663 END IF;
664
665 /*** did resource_id get changed? ***/
666 IF (g_old_resource_id <> l_resource_id) THEN
667 /*** yes -> do cascading delete for old resource_id ***/
671 , g_table_name
668 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
669 jtm_message_log_pkg.Log_Msg
670 ( l_task_assignment_id
672 , 'Task assignment resource_id changed from ' || g_old_resource_id || ' to ' || l_resource_id || '.' || fnd_global.local_chr(10) ||
673 'Deleting old assignment ACC record (if exists).'
674 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
675 END IF;
676
677 Post_Delete_Child
678 ( l_task_assignment_id
679 ,g_old_resource_id);
680
681 /*** record doesn't exist for new resource_id yet ***/
682 l_acc_id := -1;
683 ELSE
684 /*** resource_id is same as before the update -> check if it already exists on mobile ***/
685 l_acc_id := JTM_HOOK_UTIL_PKG.Get_Acc_Id
686 ( P_ACC_TABLE_NAME => g_acc_table_name
687 ,P_PK1_NAME => g_pk1_name
688 ,P_PK1_NUM_VALUE => l_task_assignment_id
689 ,P_RESOURCE_ID => l_resource_id);
690 END IF;
691
692 /*** check if updated record needs to be replicated ***/
693 l_replicate := Replicate_Record( l_task_assignment_id );
694 IF l_replicate THEN
695 /*** record should be replicated ***/
696 IF l_acc_id = -1 THEN
697 /*** record doesn't exist on mobile but should be replicated -> Insert ***/
698 l_dummy := Pre_Insert_Child
699 ( l_task_assignment_id
700 ,l_resource_id);
701 ELSE
702 /*** record exists on mobile and should still be replicated -> push changed record to mobile ***/
703 Update_ACC_Record
704 ( l_task_assignment_id
705 ,l_resource_id
706 ,l_acc_id);
707 END IF;
708 ELSE
709 /*** record should not be replicated ***/
710 IF l_acc_id > -1 THEN
711 /*** record exists on mobile but should not be replicated anymore -> delete from mobile ***/
712 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
713 jtm_message_log_pkg.Log_Msg
714 ( l_task_assignment_id
715 , g_table_name
716 , 'Task assignment was replicated before update, but should not be replicated anymore.'
717 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
718 END IF;
719
720 Post_Delete_Child
721 ( l_task_assignment_id
722 ,l_resource_id);
723 END IF;
724 END IF;
725
726 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
727 jtm_message_log_pkg.Log_Msg
728 ( l_task_assignment_id
729 , g_table_name
730 , 'Leaving POST_UPDATE hook'
731 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
732 END IF;
733
734 x_return_status := FND_API.G_RET_STS_SUCCESS;
735 EXCEPTION WHEN OTHERS THEN
736 /*** hook failed -> log error ***/
737 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
738 jtm_message_log_pkg.Log_Msg
739 ( l_task_assignment_id
740 , g_table_name
741 , 'Caught exception in POST_UPDATE hook:' || fnd_global.local_chr(10) || sqlerrm
742 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
743 END IF;
744 fnd_msg_pub.Add_Exc_Msg('CSL_JTF_TASK_ASS_ACC_PKG','POST_UPDATE_TASK_ASSIGNMENT',sqlerrm);
745 -- x_return_status := FND_API.G_RET_STS_ERROR;
746 x_return_status := FND_API.G_RET_STS_SUCCESS;
747 END POST_UPDATE_TASK_ASSIGNMENT;
748
749 /* Called before assignment Delete */
750 PROCEDURE PRE_DELETE_TASK_ASSIGNMENT
751 ( x_return_status OUT NOCOPY varchar2
752 )
753 IS
754 l_resource_id NUMBER;
755 l_task_assignment_id NUMBER;
756 l_enabled_flag VARCHAR2(30);
757 BEGIN
758 l_enabled_flag := JTM_PROFILE_UTL_PKG.GET_ENABLE_FLAG_AT_RESP(P_APP_SHORT_NAME => 'CSL' );
759 IF l_enabled_flag <> 'Y' THEN
760 x_return_status := FND_API.G_RET_STS_SUCCESS;
761 RETURN;
762 END IF;
763 /*** get assignment record details from public API ***/
764 l_task_assignment_id := jtf_task_assignments_pub.p_task_assignments_user_hooks.task_assignment_id;
765 l_resource_id := jtf_task_assignments_pub.p_task_assignments_user_hooks.resource_id;
766
767 /*** get debug level ***/
768 g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;
769
770 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
771 jtm_message_log_pkg.Log_Msg
772 ( l_task_assignment_id
773 , g_table_name
774 , 'Entering PRE_DELETE hook'
775 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
776 END IF;
777
778 /*** yes -> delete assignment related data from the ACC tables ***/
779 Post_Delete_Child
780 ( l_task_assignment_id
781 ,l_resource_id);
782
783 x_return_status := FND_API.G_RET_STS_SUCCESS;
784
785 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
786 jtm_message_log_pkg.Log_Msg
787 ( l_task_assignment_id
788 , g_table_name
789 , 'Leaving PRE_DELETE hook'
790 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
791 END IF;
792
793 EXCEPTION WHEN OTHERS THEN
794 /*** hook failed -> log error ***/
795 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
796 jtm_message_log_pkg.Log_Msg
797 ( l_task_assignment_id
798 , g_table_name
799 , 'Caught exception in PRE_DELETE hook:' || fnd_global.local_chr(10) || sqlerrm
800 , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
802 fnd_msg_pub.Add_Exc_Msg('CSL_JTF_TASK_ASS_ACC_PKG','PRE_DELETE_TASK_ASSIGNMENT',sqlerrm);
803 -- x_return_status := FND_API.G_RET_STS_ERROR;
804 x_return_status := FND_API.G_RET_STS_SUCCESS;
805 END PRE_DELETE_TASK_ASSIGNMENT;
806
807 /* Called after assignment Delete */
808 PROCEDURE POST_DELETE_TASK_ASSIGNMENT
809 ( x_return_status OUT NOCOPY varchar2
810 )
811 IS
812 BEGIN
813 x_return_status := FND_API.G_RET_STS_SUCCESS;
814 END POST_DELETE_TASK_ASSIGNMENT;
815
816
817 /***Purge logic for Expired/Completed Task Assignments***/
818 --Bug 3475657
819 PROCEDURE PURGE_TASK_ASSIGNMENTS(p_status OUT NOCOPY VARCHAR2,
820 p_message OUT NOCOPY VARCHAR2)
821 IS
822 CURSOR lcur_purge_task_assignments is
823 SELECT acc.task_assignment_id, acc.resource_id
824 FROM csl_jtf_task_ass_acc acc,
825 jtf_task_assignments jta,
826 jtf_tasks_b jt,
827 jtf_task_statuses_b jts,
828 jtf_task_statuses_b jta_jts
829 WHERE acc.task_assignment_id = jta.task_assignment_id
830 AND jt.task_id = jta.task_id
831 AND jts.task_status_id = jt.task_status_id
832 AND jta_jts.task_status_id = jta.assignment_status_id
833 AND (NVL(jt.scheduled_start_date, SYSDATE) < (SYSDATE - TO_NUMBER(FND_PROFILE.Value('CSL_APPL_HISTORY_IN_DAYS'))))
834 AND (NVL(jts.cancelled_flag,'N') = 'Y'
835 OR NVL(jts.closed_flag, 'N') = 'Y'
836 OR NVL(jts.completed_flag, 'N') = 'Y'
837 OR NVL(jts.rejected_flag, 'N') = 'Y'
838 OR NVL(jta_jts.cancelled_flag,'N') = 'Y'
839 OR NVL(jta_jts.closed_flag,'N') = 'Y'
840 OR NVL(jta_jts.completed_flag,'N') = 'Y'
841 OR NVL(jta_jts.rejected_flag, 'N') = 'Y')
842 AND NOT EXISTS (SELECT 'x' FROM CSL_SERVICE_HISTORY hist
843 WHERE hist.history_incident_id = jt.source_object_id
844 AND jt.source_object_type_code = 'SR'
845 AND hist.resource_id = acc.resource_id);
846
847 rcur_purge_task_assignments lcur_purge_task_assignments%ROWTYPE;
848
849 BEGIN
850
851
852 --Delete the Task Assignements which have expired or been been closed.
853 FOR rcur_purge_task_assignments in lcur_purge_task_assignments
854 LOOP
855 Post_Delete_Child(rcur_purge_task_assignments.task_assignment_id, rcur_purge_task_assignments.resource_id);
856 END LOOP;
857
858 CSL_JTF_TASKS_ACC_PKG.Purge_Tasks;
859
860 p_status := 'FINE';
861 p_message := 'CSL_JTF_TASK_ASS_ACC_PKG.PURGE_TASK_ASSIGNMENTS Completed successfully';
862
863 EXCEPTION
864
865 WHEN OTHERS THEN
866
867 ROLLBACK;
868 p_status := 'ERROR';
869 p_message := 'Error in CSL_JTF_TASK_ASS_ACC_PKG.PURGE_TASK_ASSIGNMENTS: ' || substr(SQLERRM, 1, 2000);
870
871 jtm_message_log_pkg.Log_Msg
872 (rcur_purge_task_assignments.task_assignment_id,
873 'CSL_JTF_TASK_ASS_ACC',
874 'Exception occured in PURGE_TASK_ASSIGNMENT for id: ' || rcur_purge_task_assignments.task_assignment_id,
875 JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
876
877 END PURGE_TASK_ASSIGNMENTS;
878
879 END CSL_JTF_TASK_ASS_ACC_PKG;
801 END IF;