[Home] [Help]
PACKAGE BODY: APPS.CSL_TASK_ASSIGNMENTS_PKG
Source
1 PACKAGE BODY CSL_TASK_ASSIGNMENTS_PKG AS
2 /* $Header: cslvtasb.pls 120.1 2005/10/12 00:21:29 hhaugeru noship $ */
3
4 error EXCEPTION;
5
6 /*** Globals ***/
7 g_object_name CONSTANT VARCHAR2(30) := 'CSL_TASK_ASSIGNMENTS_PKG';
8 g_pub_name CONSTANT VARCHAR2(30) := 'CSL_JTF_TASK_ASSIGNMENTS';
9 g_debug_level NUMBER; -- debug level
10
11 CURSOR c_task_ass( b_user_name VARCHAR2, b_tranid NUMBER) is
12 SELECT *
13 FROM CSL_JTF_TASK_ASSIGNMENTS_inq
14 WHERE tranid$$ = b_tranid
15 AND clid$$cs = b_user_name;
16
17 /***
18 This procedure is called by APPLY_CLIENT_CHANGES when an inserted record is to be processed.
19 ***/
20 PROCEDURE APPLY_INSERT
21 (
22 p_record IN c_task_ass%ROWTYPE,
23 p_error_msg OUT NOCOPY VARCHAR2,
24 x_return_status IN OUT NOCOPY VARCHAR2
25 ) IS
26
27 CURSOR c_task_assignment
28 ( b_task_assignment_id number
29 )
30 IS
31 SELECT jta.assignment_status_id
32 , jta.object_version_number
33 FROM jtf_task_assignments jta
34 WHERE jta.task_assignment_id = b_task_assignment_id;
35
36 r_task_assignment c_task_assignment%rowtype;
37
38 l_task_assignment_id NUMBER;
39
40 l_msg_count NUMBER;
41 l_msg_data VARCHAR2(240);
42
43 l_assignment_obj_version_nr NUMBER;
44 l_assignment_status_id NUMBER;
45 l_task_obj_version_nr NUMBER;
46 l_task_status_id NUMBER;
47 l_task_status_name VARCHAR2(30);
48
49 BEGIN
50 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
51 jtm_message_log_pkg.Log_Msg
52 ( v_object_id => p_record.task_assignment_id
53 , v_object_name => g_object_name
54 , v_message => 'Entering ' || g_object_name || '.APPLY_INSERT'
55 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
56 END IF;
57
58 -- Create a Task Assignment
59 JTF_TASK_ASSIGNMENTS_PUB.create_task_assignment (
60 p_api_version => 1.0,
61 p_init_msg_list => FND_API.G_TRUE,
62 p_commit => FND_API.G_FALSE,
63 p_task_assignment_id => p_record.TASK_ASSIGNMENT_ID,
64 p_task_id => p_record.TASK_ID,
65 p_resource_type_code => 'RS_EMPLOYEE',
66 p_resource_id => JTM_HOOK_UTIL_PKG.get_resource_id(p_record.clid$$cs),
67 p_assignment_status_id => p_record.ASSIGNMENT_STATUS_ID,
68 x_return_status => x_return_status,
69 x_msg_count => l_msg_count,
70 x_msg_data => l_msg_data,
71 x_task_assignment_id => l_task_assignment_id
72 );
73
74 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
75 /*** exception occurred in API -> return errmsg ***/
76 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
77 (
78 p_api_error => TRUE
79 );
80 END IF;
81
82
83 -- Retrieve the task_id and the current object version number
84 -- from the known task_assignment_id.
85 OPEN c_task_assignment
86 ( b_task_assignment_id => p_record.task_assignment_id
87 );
88 FETCH c_task_assignment
89 INTO r_task_assignment;
90 IF c_task_assignment%NOTFOUND THEN
91 CLOSE c_task_assignment;
92 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
93 jtm_message_log_pkg.Log_Msg
94 ( v_object_id => p_record.task_assignment_id
95 , v_object_name => g_object_name
96 , v_message => 'Leaving ' || g_object_name || '.APPLY_UPDATE'
97 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
98 END IF;
99 -- Bail out.
100 x_return_status := FND_API.G_RET_STS_ERROR;
101 RETURN; --TODO ADD error msg.
102 END IF;
103
104 l_assignment_obj_version_nr := r_task_assignment.object_version_number;
105 l_assignment_status_id := r_task_assignment.assignment_status_id;
106
107 CLOSE c_task_assignment;
108
109
110 csf_task_assignments_pub.update_assignment_status
111 ( p_api_version => 1.0
112 , p_init_msg_list => FND_API.G_TRUE
113 , p_commit => FND_API.G_FALSE
114 , x_return_status => x_return_status
115 , x_msg_count => l_msg_count
116 , x_msg_data => l_msg_data
117 , p_task_assignment_id => p_record.task_assignment_id
118 , p_assignment_status_id => l_assignment_status_id
119 , p_object_version_number => l_assignment_obj_version_nr
120 , p_update_task => 'T'
121 , x_task_object_version_number => l_task_obj_version_nr
122 , x_task_status_id => l_task_status_id
123 );
124
125 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
126 /*** exception occurred in API -> return errmsg ***/
127 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
128 (
129 p_api_error => TRUE
130 );
131 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
132 jtm_message_log_pkg.Log_Msg
133 ( v_object_id => p_record.task_assignment_id
134 , v_object_name => g_object_name
135 , v_message => 'Leaving ' || g_object_name || '.APPLY_UPDATE'
136 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
137 END IF;
138 RETURN;
139 END IF;
140
141 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
142 jtm_message_log_pkg.Log_Msg
143 ( v_object_id => p_record.task_assignment_id
144 , v_object_name => g_object_name
145 , v_message => 'Leaving ' || g_object_name || '.APPLY_INSERT'
146 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
147 END IF;
148
149 EXCEPTION WHEN OTHERS THEN
150 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
151 jtm_message_log_pkg.Log_Msg
152 ( v_object_id => p_record.task_assignment_id
153 , v_object_name => g_object_name
154 , v_message => 'Exception occurred in APPLY_INSERT:' || fnd_global.local_chr(10) || sqlerrm
155 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
156 END IF;
157
158 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_INSERT', sqlerrm);
159 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
160 (
161 p_api_error => TRUE
162 );
163
164 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
165 jtm_message_log_pkg.Log_Msg
166 ( v_object_id => p_record.task_assignment_id
167 , v_object_name => g_object_name
168 , v_message => 'Leaving ' || g_object_name || '.APPLY_INSERT'
169 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
170 END IF;
171
172 x_return_status := FND_API.G_RET_STS_ERROR;
173 END APPLY_INSERT;
174
175 /***
176 This procedure is called by APPLY_CLIENT_CHANGES when an updated record is to be processed.
177 ***/
178 PROCEDURE APPLY_UPDATE
179 (
180 p_record IN c_task_ass%ROWTYPE,
181 p_error_msg OUT NOCOPY VARCHAR2,
182 x_return_status IN OUT NOCOPY VARCHAR2
183 ) IS
184 CURSOR c_task_assignment
185 ( b_task_assignment_id number
186 )
187 IS
188 SELECT jta.assignment_status_id
189 , jta.object_version_number
190 FROM jtf_task_assignments jta
191 WHERE jta.task_assignment_id = b_task_assignment_id;
192
193 r_task_assignment c_task_assignment%rowtype;
194
195 cursor c_last_update_date
196 ( b_task_assignment_id NUMBER
197 )
198 is
199 SELECT LAST_UPDATE_DATE, LAST_UPDATED_BY
200 from JTF_TASK_ASSIGNMENTS
201 where task_assignment_id = b_task_assignment_id;
202
203 r_last_update_date c_last_update_date%ROWTYPE;
204
205 l_msg_count NUMBER;
206 l_msg_data VARCHAR2(240);
207
208 l_assignment_obj_version_nr NUMBER;
209 l_assignment_status_id NUMBER;
210 l_task_obj_version_nr NUMBER;
211 l_task_status_id NUMBER;
212 l_task_status_name VARCHAR2(30);
213 l_profile_value VARCHAR2(240);
214 l_task_type_id NUMBER;
215
216 BEGIN
217 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
218 jtm_message_log_pkg.Log_Msg
219 ( v_object_id => p_record.task_assignment_id
220 , v_object_name => g_object_name
221 , v_message => 'Entering ' || g_object_name || '.APPLY_UPDATE'
222 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
223 END IF;
224
225 -- Initialize API return status to success
226 x_return_status := FND_API.G_RET_STS_SUCCESS;
227
228 -- Check for Stale data
229 l_profile_value := fnd_profile.value('JTM_APPL_CONFLICT_RULE');
230 if l_profile_value = 'SERVER_WINS' AND
231 ASG_DEFER.IS_DEFERRED(p_record.clid$$cs, p_record.tranid$$,g_object_name, p_record.seqno$$) <> FND_API.G_TRUE
232 then
233 open c_last_update_date(b_task_assignment_id => p_record.task_assignment_id);
234 fetch c_last_update_date into r_last_update_date;
235 if c_last_update_date%found then
236 if r_last_update_date.last_updated_by <> asg_base.get_user_id( p_record.clid$$cs ) AND r_last_update_date.last_update_date <> p_record.last_update_date then
237 close c_last_update_date;
238 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
239 jtm_message_log_pkg.Log_Msg
240 ( v_object_id => p_record.task_assignment_id
241 , v_object_name => g_object_name
242 , v_message => 'Record is stale data'
243 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
244 END IF;
245 fnd_message.set_name
246 ( 'JTM'
247 , 'JTM_STALE_DATA'
248 );
249 fnd_msg_pub.add;
250
251 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
252 jtm_message_log_pkg.Log_Msg
253 ( v_object_id => p_record.task_assignment_id
254 , v_object_name => g_object_name
255 , v_message => 'Leaving ' || g_object_name || '.APPLY_UPDATE'
256 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
257 END IF;
258
259 x_return_status := FND_API.G_RET_STS_SUCCESS;
260 return;
261 end if;
262 else
263 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
264 jtm_message_log_pkg.Log_Msg
265 ( v_object_id => p_record.task_assignment_id
266 , v_object_name => g_object_name
267 , v_message => 'No record found in Apps Database.'
268 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
269 END IF;
270 end if;
271 close c_last_update_date;
272 end if;
273
274 -- Retrieve the task_id and the current object version number
275 -- from the known task_assignment_id.
276 OPEN c_task_assignment
277 ( b_task_assignment_id => p_record.task_assignment_id
278 );
279 FETCH c_task_assignment
280 INTO r_task_assignment;
281 IF c_task_assignment%NOTFOUND THEN
282 CLOSE c_task_assignment;
283 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
284 jtm_message_log_pkg.Log_Msg
285 ( v_object_id => p_record.task_assignment_id
286 , v_object_name => g_object_name
287 , v_message => 'Leaving ' || g_object_name || '.APPLY_UPDATE'
288 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
289 END IF;
290 -- Bail out.
291 x_return_status := FND_API.G_RET_STS_ERROR;
292 RETURN; --TODO ADD error msg.
293 END IF;
294
295 l_assignment_obj_version_nr := r_task_assignment.object_version_number;
296 l_assignment_status_id := r_task_assignment.assignment_status_id;
297
298 CLOSE c_task_assignment;
299
300 -- See "status update"-doc at the beginning of the procedure!
301 -- We do not update the status of the task assignment here.
302 -- A special API for that exists.
303
304 -- Call task assignments public api
305 JTF_TASK_ASSIGNMENTS_PUB.Update_Task_Assignment
306 ( p_api_version => 1.0
307 , p_task_assignment_id => p_record.task_assignment_id
308 , p_object_version_number => l_assignment_obj_version_nr
309 , p_task_id => p_record.task_id
310 , p_resource_type_code => p_record.resource_type_code
311 , p_resource_id => p_record.resource_id
312 , p_resource_territory_id => FND_API.G_MISS_NUM
313 , p_actual_start_date => p_record.actual_start_date
314 , p_actual_end_date => p_record.actual_end_date
315 , p_sched_travel_distance => p_record.sched_travel_distance
316 , p_sched_travel_duration => p_record.sched_travel_duration
317 , p_sched_travel_duration_uom => p_record.sched_travel_duration_uom
318 , p_shift_construct_id => FND_API.G_MISS_NUM
319 , p_attribute1 => p_record.attribute1
320 , p_attribute2 => p_record.attribute2
321 , p_attribute3 => p_record.attribute3
322 , p_attribute4 => p_record.attribute4
323 , p_attribute5 => p_record.attribute5
324 , p_attribute6 => p_record.attribute6
325 , p_attribute7 => p_record.attribute7
326 , p_attribute8 => p_record.attribute8
327 , p_attribute9 => p_record.attribute9
328 , p_attribute10 => p_record.attribute10
329 , p_attribute11 => p_record.attribute11
330 , p_attribute12 => p_record.attribute12
331 , p_attribute13 => p_record.attribute13
332 , p_attribute14 => p_record.attribute14
333 , p_attribute15 => p_record.attribute15
334 , p_attribute_category => p_record.attribute_category
335 , x_return_status => x_return_status
336 , x_msg_count => l_msg_count
337 , x_msg_data => l_msg_data
338 );
339
340 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
341 /*** exception occurred in API -> return errmsg ***/
342 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
343 (
344 p_api_error => TRUE
345 );
346 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
347 jtm_message_log_pkg.Log_Msg
348 ( v_object_id => p_record.task_assignment_id
349 , v_object_name => g_object_name
350 , v_message => 'Leaving ' || g_object_name || '.APPLY_UPDATE'
351 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
352 END IF;
353 RETURN;
354 END IF;
355
356 -- Do a check before calling the API that the status has changed.
357 -- If it hasn't then do nothing.
358 IF l_assignment_status_id <> p_record.assignment_status_id
359 THEN
360 -- Validation is not a good thing for this particular API-call: as
361 -- the laptop application does the check for state changes, it is not
362 -- necessary to redo them here. Even worse, a state change in two steps
363 -- A -> B and B -> C may be OK for laptop application, but if the
364 -- intermediate step is not sent to CRM, the API will see A -> C and
365 -- refuse it.
366 -- To allow for A -> C no validation is done here. This is not a problem
367 -- as the laptop application does the check if going from a -> C in 2
368 -- steps is valid.
369
370 csf_task_assignments_pub.update_assignment_status
371 ( p_api_version => 1.0
372 , p_init_msg_list => FND_API.G_TRUE
373 , p_commit => FND_API.G_FALSE
374 , x_return_status => x_return_status
375 , x_msg_count => l_msg_count
376 , x_msg_data => l_msg_data
377 , p_task_assignment_id => p_record.task_assignment_id
378 , p_assignment_status_id => p_record.assignment_status_id
379 , p_object_version_number => l_assignment_obj_version_nr
380 , p_update_task => 'T'
381 , x_task_object_version_number => l_task_obj_version_nr
382 , x_task_status_id => l_task_status_id
383 );
384 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
385 /*** exception occurred in API -> return errmsg ***/
386 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
387 (
388 p_api_error => TRUE
389 );
390 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
391 jtm_message_log_pkg.Log_Msg
392 ( v_object_id => p_record.task_assignment_id
393 , v_object_name => g_object_name
394 , v_message => 'Leaving ' || g_object_name || '.APPLY_UPDATE'
395 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
396 END IF;
397 RETURN;
398 END IF;
399 END IF;
400
401 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
402 jtm_message_log_pkg.Log_Msg
403 ( v_object_id => p_record.task_assignment_id
404 , v_object_name => g_object_name
405 , v_message => 'Leaving ' || g_object_name || '.APPLY_UPDATE'
406 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
407 END IF;
408
409 EXCEPTION WHEN OTHERS THEN
410 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
411 jtm_message_log_pkg.Log_Msg
412 ( v_object_id => p_record.task_assignment_id
413 , v_object_name => g_object_name
414 , v_message => 'Exception occurred in APPLY_UPDATE:' || fnd_global.local_chr(10) || sqlerrm
415 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
416 END IF;
417
418 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_UPDATE', sqlerrm);
419 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
420 (
421 p_api_error => TRUE
422 );
423
424 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
425 jtm_message_log_pkg.Log_Msg
426 ( v_object_id => p_record.task_assignment_id
427 , v_object_name => g_object_name
428 , v_message => 'Leaving ' || g_object_name || '.APPLY_UPDATE'
429 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
430 END IF;
431
432 x_return_status := FND_API.G_RET_STS_ERROR;
433 END APPLY_UPDATE;
434
435 /***
436 This procedure is called by APPLY_CLIENT_CHANGES for every record in in-queue that needs to be processed.
437 ***/
438 PROCEDURE APPLY_RECORD
439 (
440 p_record IN c_task_ass%ROWTYPE,
441 p_error_msg OUT NOCOPY VARCHAR2,
442 x_return_status IN OUT NOCOPY VARCHAR2
443 ) IS
444 l_rc BOOLEAN;
445 l_access_id NUMBER;
446 BEGIN
447 /*** initialize return status and message list ***/
448 x_return_status := FND_API.G_RET_STS_SUCCESS;
449 FND_MSG_PUB.INITIALIZE;
450
451 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
452 jtm_message_log_pkg.Log_Msg
453 ( v_object_id => p_record.task_assignment_id
454 , v_object_name => g_object_name
455 , v_message => 'Entering ' || g_object_name || '.APPLY_RECORD'
456 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
457 END IF;
458
459 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
460 jtm_message_log_pkg.Log_Msg
461 ( v_object_id => p_record.task_assignment_id
462 , v_object_name => g_object_name
463 , v_message => 'Processing TASK_ASSIGNMENT_ID = ' || p_record.task_assignment_id || fnd_global.local_chr(10) ||
464 'DMLTYPE = ' || p_record.dmltype$$
465 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
466 END IF;
467
468 IF p_record.dmltype$$='I' THEN
469 -- Process insert
470 APPLY_INSERT
471 (
472 p_record,
473 p_error_msg,
474 x_return_status
475 );
476 ELSIF p_record.dmltype$$='U' THEN
477 -- Process update
478 APPLY_UPDATE
479 (
480 p_record,
481 p_error_msg,
482 x_return_status
483 );
484 ELSIF p_record.dmltype$$='D' THEN
485 -- Process delete; not supported for this entity
486 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
487 jtm_message_log_pkg.Log_Msg
488 ( v_object_id => p_record.task_assignment_id
489 , v_object_name => g_object_name
490 , v_message => 'Delete is not supported for this entity'
491 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
492 END IF;
493
494 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
495 (
496 p_message => 'CSL_DML_OPERATION'
497 , p_token_name1 => 'DML'
498 , p_token_value1 => p_record.dmltype$$
499 );
500
501 x_return_status := FND_API.G_RET_STS_ERROR;
502 ELSE
503 -- invalid dml type
504 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
505 jtm_message_log_pkg.Log_Msg
506 ( v_object_id => p_record.task_assignment_id
507 , v_object_name => g_object_name
508 , v_message => 'Invalid DML type: ' || p_record.dmltype$$
509 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
510 END IF;
511
512 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
513 (
514 p_message => 'CSL_DML_OPERATION'
515 , p_token_name1 => 'DML'
516 , p_token_value1 => p_record.dmltype$$
517 );
518
519 x_return_status := FND_API.G_RET_STS_ERROR;
520 END IF;
521
522
523 IF p_record.dmltype$$ = 'U' AND x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
524 l_access_id := jtm_hook_util_pkg.get_acc_id(
525 p_acc_table_name => 'CSL_JTF_TASK_ASS_ACC',
526 p_resource_id => JTM_HOOK_UTIL_PKG.get_resource_id( p_record.clid$$cs ),
527 p_pk1_name => 'TASK_ASSIGNMENT_ID',
528 p_pk1_num_value => p_record.TASK_ASSIGNMENT_ID
529 );
530 l_rc := CSL_SERVICEL_WRAPPER_PKG.AUTONOMOUS_MARK_DIRTY(
531 P_PUB_ITEM => g_pub_name,
532 P_ACCESSID => l_access_id,
533 P_RESOURCEID => JTM_HOOK_UTIL_PKG.get_resource_id( p_record.clid$$cs ),
534 P_DML => 'U',
535 P_TIMESTAMP => sysdate
536 );
537 END IF;
538
539 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
540 jtm_message_log_pkg.Log_Msg
541 ( v_object_id => p_record.task_assignment_id
542 , v_object_name => g_object_name
543 , v_message => 'Leaving ' || g_object_name || '.APPLY_RECORD'
544 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
545 END IF;
546 EXCEPTION WHEN OTHERS THEN
547 /*** defer record when any process exception occurs ***/
548 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
549 jtm_message_log_pkg.Log_Msg
550 ( v_object_id => p_record.task_assignment_id
551 , v_object_name => g_object_name
552 , v_message => 'Exception occurred in APPLY_RECORD:' || fnd_global.local_chr(10) || sqlerrm
553 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
554 END IF;
555
556 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_RECORD', sqlerrm);
557 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
558 (
559 p_api_error => TRUE
560 );
561
562 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
563 jtm_message_log_pkg.Log_Msg
564 ( v_object_id => p_record.task_assignment_id
565 , v_object_name => g_object_name
566 , v_message => 'Leaving ' || g_object_name || '.APPLY_RECORD'
567 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
568 END IF;
569
570 x_return_status := FND_API.G_RET_STS_ERROR;
571 END APPLY_RECORD;
572
573 /***
574 This procedure is called by CSL_SERVICEL_WRAPPER_PKG when publication item CSL_JTF_TASK_ASSIGNMENTS
575 is dirty. This happens when a mobile field service device executed DML on an updatable table and did
576 a fast sync. This procedure will insert the data that came from mobile into the backend tables using
577 public APIs.
578 ***/
579 PROCEDURE APPLY_CLIENT_CHANGES
580 (
581 p_user_name IN VARCHAR2,
582 p_tranid IN NUMBER,
583 p_debug_level IN NUMBER,
584 x_return_status IN OUT NOCOPY VARCHAR2
585 ) IS
586
587 -- ER 3218717
588 -- Check if there are any debrief line records in INQUEUE
589
590 CURSOR c_chk_debrief_lines ( b_task_assignment_id NUMBER) IS
591 SELECT COUNT(*) FROM CSL_CSF_DEBRIEF_LINES_INQ
592 WHERE DEBRIEF_HEADER_ID IN (
593 SELECT DEBRIEF_HEADER_ID
594 FROM CSL_CSF_DEBRIEF_HEADERS_INQ inq
595 WHERE inq.TASK_ASSIGNMENT_ID = b_task_assignment_id
596 UNION
597 SELECT DEBRIEF_HEADER_ID
598 FROM CSF_DEBRIEF_HEADERS header
599 WHERE header.TASK_ASSIGNMENT_ID = b_task_assignment_id
600 ) ;
601
602 -- ER 3218717
603 CURSOR c_chk_task_status
604 ( b_task_assignment_id NUMBER
605 ) IS
606 SELECT dh.debrief_header_id, tst.rejected_flag, tst.on_hold_flag,
607 tst.cancelled_flag, tst.closed_flag, tst.completed_flag
608 FROM csf_debrief_headers dh, jtf_task_assignments tas,
609 jtf_task_statuses_b tst
610 WHERE dh.task_assignment_id = tas.task_assignment_id
611 AND tas.assignment_status_id = tst.task_status_id
612 AND tas.task_assignment_id = b_task_assignment_id;
613
614 l_process_status VARCHAR2(1);
615 l_error_msg VARCHAR2(4000);
616
617 -- ER 3218717
618 l_rejected_flag VARCHAR2(1);
619 l_on_hold_flag VARCHAR2(1);
620 l_cancelled_flag VARCHAR2(1);
621 l_closed_flag VARCHAR2(1);
622 l_completed_flag VARCHAR2(1);
623 l_dbl_count NUMBER := NULL;
624 l_header_id NUMBER := NULL;
625
626 BEGIN
627 g_debug_level := p_debug_level;
628 x_return_status := FND_API.G_RET_STS_SUCCESS;
629
630 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
631 jtm_message_log_pkg.Log_Msg
632 ( v_object_id => null
633 , v_object_name => g_object_name
634 , v_message => 'Entering ' || g_object_name || '.Apply_Client_Changes'
635 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
636 END IF;
637
638 /*** loop through CSL_JTF_TASK_ASSIGNMENTS records in inqueue ***/
639 FOR r_task_ass IN c_task_ass( p_user_name, p_tranid) LOOP
640
641 SAVEPOINT save_rec;
642
643 /*** apply record ***/
644 APPLY_RECORD
645 (
646 r_task_ass
647 , l_error_msg
648 , l_process_status
649 );
650
651 jtm_message_log_pkg.Log_Msg
652 ( v_object_id => r_task_ass.task_assignment_id
653 , v_object_name => g_object_name
654 , v_message => 'l_error_msg = ' || l_error_msg || fnd_global.local_chr(10) ||
655 'l_process_status = ' || l_process_status
656 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
657
658 /*** was record processed successfully? ***/
659 IF l_process_status = FND_API.G_RET_STS_SUCCESS THEN
660
661 -- ER 3218717 Check if there are any Debrief Line records
662 OPEN c_chk_debrief_lines (r_task_ass.task_assignment_id);
663 FETCH c_chk_debrief_lines INTO l_dbl_count;
664 CLOSE c_chk_debrief_lines;
665
666 IF ( l_dbl_count = 0 ) THEN
667
668 OPEN c_chk_task_status (r_task_ass.task_assignment_id);
669 FETCH c_chk_task_status INTO l_header_id, l_rejected_flag,
670 l_on_hold_flag, l_cancelled_flag, l_closed_flag, l_completed_flag;
671 CLOSE c_chk_task_status;
672
673 IF ( (l_rejected_flag='Y') OR (l_on_hold_flag='Y')
674 OR (l_cancelled_flag='Y') OR (l_closed_flag='Y')
675 OR (l_completed_flag='Y') ) THEN
676
677 csf_debrief_update_pkg.form_Call (1.0, l_header_id);
678
679 END IF;
680
681 END IF;
682
683 /*** Yes -> delete record from inqueue ***/
684 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
685 jtm_message_log_pkg.Log_Msg
686 ( v_object_id => r_task_ass.task_assignment_id
687 , v_object_name => g_object_name
688 , v_message => 'Record successfully processed, deleting from inqueue'
689 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
690 END IF;
691
692 CSL_SERVICEL_WRAPPER_PKG.DELETE_RECORD
693 (
694 p_user_name,
695 p_tranid,
696 r_task_ass.seqno$$,
697 r_task_ass.task_assignment_id,
698 g_object_name,
699 g_pub_name,
700 l_error_msg,
701 l_process_status
702 );
703
704 /*** was delete successful? ***/
705 IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
706 /*** no -> rollback ***/
707 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
708 jtm_message_log_pkg.Log_Msg
709 ( v_object_id => r_task_ass.task_assignment_id
710 , v_object_name => g_object_name
711 , v_message => 'Deleting from inqueue failed, rolling back to savepoint'
712 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
713 END IF;
714 ROLLBACK TO save_rec;
715 END IF;
716 END IF;
717
718 IF l_process_Status <> FND_API.G_RET_STS_SUCCESS THEN
719 /*** Record was not processed successfully or delete failed -> defer and reject record ***/
720 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
721 jtm_message_log_pkg.Log_Msg
722 ( v_object_id => r_task_ass.task_assignment_id
723 , v_object_name => g_object_name
724 , v_message => 'Record not processed successfully, deferring and rejecting record'
725 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
726 END IF;
727
728 CSL_SERVICEL_WRAPPER_PKG.DEFER_RECORD
729 (
730 p_user_name
731 , p_tranid
732 , r_task_ass.seqno$$
733 , r_task_ass.task_assignment_id
734 , g_object_name
735 , g_pub_name
736 , l_error_msg
737 , l_process_status
738 , r_task_ass.dmltype$$
739 );
740
741 /*** Was defer successful? ***/
742 IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
743 /*** no -> rollback ***/
744 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
745 jtm_message_log_pkg.Log_Msg
746 ( v_object_id => r_task_ass.task_assignment_id
747 , v_object_name => g_object_name
748 , v_message => 'Defer record failed, rolling back to savepoint'
749 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
750 END IF;
751 ROLLBACK TO save_rec;
752 END IF;
753 END IF;
754
755 END LOOP;
756
757 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
758 jtm_message_log_pkg.Log_Msg
759 ( v_object_id => null
760 , v_object_name => g_object_name
761 , v_message => 'Leaving ' || g_object_name || '.Apply_Client_Changes'
762 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
763 END IF;
764
765 EXCEPTION WHEN OTHERS THEN
766 /*** catch and log exceptions ***/
767 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
768 jtm_message_log_pkg.Log_Msg
769 ( v_object_id => null
770 , v_object_name => g_object_name
771 , v_message => 'Exception occurred in APPLY_CLIENT_CHANGES:' || fnd_global.local_chr(10) || sqlerrm
772 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
773 END IF;
774
775 -- ER 3218717
776 IF c_chk_debrief_lines%ISOPEN THEN
777 CLOSE c_chk_debrief_lines;
778 END IF;
779
780 IF c_chk_task_status%ISOPEN THEN
781 CLOSE c_chk_task_status;
782 END IF;
783
784 x_return_status := FND_API.G_RET_STS_ERROR;
785 END APPLY_CLIENT_CHANGES;
786
787
788 /* New for Scottish Water Bug */
789 FUNCTION CONFLICT_RESOLUTION_METHOD (p_user_name IN VARCHAR2,
790 p_tran_id IN NUMBER,
791 p_sequence IN NUMBER)
792 RETURN VARCHAR2 IS
793 l_profile_value VARCHAR2(30) ;
794 l_user_id NUMBER ;
795 cursor get_user_id(l_tran_id in number,
796 l_user_name in varchar2)
797 IS
798 SELECT b.last_updated_by
799 FROM JTF_TASK_ASSIGNMENTS b, CSL_JTF_TASK_ASSIGNMENTS_INQ a
800 WHERE a.clid$$cs = l_user_name
801 AND tranid$$ = l_tran_id AND a.task_assignment_id = b.task_assignment_id
802 AND a.SEQNO$$ = p_sequence;
803
804 BEGIN
805
806 jtm_message_log_pkg.Log_Msg
807 ( v_object_id => null
808 , v_object_name => g_object_name
809 , v_message => 'Entering Task Assignments CONFLICT_RESOLUTION_HANDLER : User '
810 ||p_user_name
811 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
812
813 l_profile_value := fnd_profile.value('JTM_APPL_CONFLICT_RULE');
814
815 OPEN get_user_id(p_tran_id,p_user_name) ;
816 FETCH get_user_id INTO l_user_id ;
817 CLOSE get_user_id ;
818
819 if l_profile_value = 'SERVER_WINS'
820 AND l_user_id <> asg_base.get_user_id( p_user_name ) then
821 RETURN 'S' ;
822 else
823 RETURN 'C' ;
824 END IF ;
825
826 EXCEPTION
827 WHEN OTHERS THEN
828 RETURN 'C';
829 END CONFLICT_RESOLUTION_METHOD;
830
831 END CSL_TASK_ASSIGNMENTS_PKG;