[Home] [Help]
PACKAGE BODY: APPS.CSM_TASK_ASSIGNMENTS_PKG
Source
1 PACKAGE BODY CSM_TASK_ASSIGNMENTS_PKG AS
2 /* $Header: csmutab.pls 120.11.12020000.5 2013/04/09 11:06:54 saradhak ship $ */
3
4 /*
5 * The function to be called by CSM_SERVICEP_WRAPPER_PKG, for upward sync of
6 * publication item CSM_TASK_ASSIGNMENTS
7 */
8
9 -- Purpose: Update Task Assignments changes on Handheld to Enterprise database
10 --
11 -- MODIFICATION HISTORY
12 -- Person Date Comments
13 -- DBhagat 11th September 2002 Created
14 --
15 -- --------- ------------------- ------------------------------------------
16 -- Enter package declarations as shown below
17
18 /*** Globals ***/
19 g_object_name CONSTANT VARCHAR2(30) := 'CSM_TASK_ASSIGNMENTS_PKG'; -- package name
20 g_pub_name CONSTANT VARCHAR2(30) := 'CSM_TASK_ASSIGNMENTS'; -- publication item name
21 g_debug_level NUMBER; -- debug level
22
23 CURSOR c_task_assignments_inq( b_user_name VARCHAR2, b_tranid NUMBER) IS
24 SELECT *
25 FROM csm_task_assignments_inq
26 WHERE tranid$$ = b_tranid
27 AND CLID$$CS = B_USER_NAME;
28
29 /***
30 This procedure is called by APPLY_CLIENT_CHANGES when an inserted record is to be processed.
31 ***/
32 PROCEDURE APPLY_INSERT
33 (
34 p_record IN c_task_assignments_inq%ROWTYPE,
35 p_error_msg OUT NOCOPY VARCHAR2,
36 x_return_status IN OUT NOCOPY VARCHAR2
37 ) IS
38
39 l_task_assignment_id NUMBER;
40 l_msg_count NUMBER;
41 l_msg_data VARCHAR2(4000);
42 l_ovn NUMBER;
43 l_ta_object_version_number number;
44 l_task_object_version_number number;
45 l_task_status_id number;
46 l_task_status_name varchar2(240);
47 l_task_type_id number;
48 l_audit_curr_prf varchar2(100);
49
50 CURSOR c_task_assignment
51 ( b_task_assignment_id number
52 )
53 IS
54 SELECT jta.object_version_number
55 FROM jtf_task_assignments jta
56 WHERE jta.task_assignment_id = b_task_assignment_id;
57
58
59 BEGIN
60 CSM_UTIL_PKG.LOG('Entering CSM_TASK_ASSIGNMENTS_PKG.APPLY_INSERT for task_assignment_id ' || p_record.task_assignment_id ,
61 'CSM_TASK_ASSIGNMENTS_PKG.APPLY_INSERT',FND_LOG.LEVEL_PROCEDURE);
62
63 -- Create a Task Assignment
64 csf_task_assignments_pub.create_task_assignment (
65 p_api_version => 1.0,
66 p_init_msg_list => fnd_api.g_true,
67 p_commit => fnd_api.g_false,
68 p_task_assignment_id => p_record.TASK_ASSIGNMENT_ID,
69 p_validation_level => NULL,
70 p_task_id => p_record.TASK_ID,
71 p_task_name => NULL,
72 p_task_number => NULL,
73 p_resource_id => p_record.RESOURCE_ID,
74 p_resource_type_code => 'RS_EMPLOYEE',
75 p_resource_name => NULL,
76 p_actual_effort => NULL,
77 p_actual_effort_uom => NULL,
78 p_schedule_flag => NULL,
79 p_alarm_type_code => NULL,
80 p_alarm_contact => NULL,
81 p_sched_travel_distance=> p_record.SCHED_TRAVEL_DISTANCE,
82 p_sched_travel_duration=> p_record.SCHED_TRAVEL_DURATION,
83 p_sched_travel_duration_uom => p_record.SCHED_TRAVEL_DURATION_UOM,
84 p_actual_travel_distance =>p_record.ACTUAL_TRAVEL_DISTANCE,
85 p_actual_travel_duration => p_record.ACTUAL_TRAVEL_DURATION,
86 p_actual_travel_duration_uom => p_record.ACTUAL_TRAVEL_DURATION_UOM,
87 p_actual_start_date => p_record.ACTUAL_START_DATE,
88 p_actual_end_date => p_record.ACTUAL_END_DATE,
89 p_palm_flag => NULL,
90 p_wince_flag => NULL,
91 p_laptop_flag => NULL,
92 p_device1_flag => NULL,
93 p_device2_flag => NULL,
94 p_device3_flag => NULL,
95 p_resource_territory_id=> NULL,
96 p_assignment_status_id => p_record.ASSIGNMENT_STATUS_ID,
97 p_shift_construct_id => NULL,
98 p_object_capacity_id => NULL,
99 p_update_task => NULL,
100 x_return_status => x_return_status,
101 x_msg_count => l_msg_count,
102 x_msg_data => l_msg_data,
103 x_task_assignment_id => l_task_assignment_id,
104 x_ta_object_version_number => l_ta_object_version_number,
105 x_task_object_version_number => l_task_object_version_number,
106 x_task_status_id => l_task_status_id
107 );
108
109 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
110 THEN
111
112 x_return_status := FND_API.G_RET_STS_ERROR;
113 CSM_UTIL_PKG.log( 'Error in ' || g_object_name || '.APPLY_INSERT:'
114 || ' ROOT ERROR: JTF_TASK_ASSIGNMENTS_PUB.create_task_assignment'
115 || ' for PK ' || p_record.TASK_ASSIGNMENT_ID,
116 g_object_name || '.APPLY_INSERT',FND_LOG.LEVEL_ERROR );
117 RETURN ;
118 END IF;
119
120 -- Ask for the task. It must exist. Exceptions will abort.
121 OPEN c_task_assignment
122 ( b_task_assignment_id => p_record.task_assignment_id
123 );
124 FETCH c_task_assignment INTO l_ovn;
125 CLOSE c_task_assignment;
126
127 -- Synchronize Task Assignment and Task statuses
128 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
129
130 /* Bug 16013131 */
131 BEGIN
132 EXECUTE IMMEDIATE 'BEGIN CSF_DEBRIEF_PUB.SET_AUTO_DEBRIEF(FND_API.TO_BOOLEAN(:1)); END;'
133 USING FND_API.G_FALSE;
134 EXCEPTION
135 WHEN OTHERS THEN
136 CSM_UTIL_PKG.LOG('Exception raised when calling csf_debrief_pub.set_auto_debrief(FALSE): ' || SQLERRM,
137 'CSM_TASK_ASSIGNMENT_PKG.apply_insert', FND_LOG.LEVEL_PROCEDURE);
138 END;
139
140 l_audit_curr_prf:=CSM_UTIL_PKG.set_task_audit(p_new_val=>'N' ,p_old_val=> NULL);
141
142 csf_task_assignments_pub.update_assignment_status
143 ( p_api_version => 1.0
144 , p_init_msg_list => FND_API.G_TRUE
145 , p_commit => FND_API.G_FALSE
146 , p_validation_level => FND_API.G_VALID_LEVEL_NONE
147 , x_return_status => x_return_status
148 , x_msg_count => l_msg_count
149 , x_msg_data => l_msg_data
150 , p_task_assignment_id => p_record.task_assignment_id
151 , p_assignment_status_id => p_record.assignment_status_id
152 , p_object_version_number => l_ovn
153 , p_update_task => 'T'
154 , x_task_object_version_number => l_task_object_version_number
155 , x_task_status_id => l_task_status_id
156 );
157
158 l_audit_curr_prf:=CSM_UTIL_PKG.set_task_audit(p_new_val=>'Y' ,p_old_val=> l_audit_curr_prf);
159
160 END IF;
161
162 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
163 THEN
164
165 x_return_status := FND_API.G_RET_STS_ERROR;
166 CSM_UTIL_PKG.log( 'Error in ' || g_object_name || '.APPLY_INSERT:'
167 || ' ROOT ERROR: csf_tasks_pub.update_assignment_status'
168 || ' for PK ' || p_record.TASK_ASSIGNMENT_ID,
169 g_object_name || '.APPLY_UPDATE',FND_LOG.LEVEL_ERROR );
170 RETURN ;
171 END IF;
172
173
174 -- success
175 x_return_status := FND_API.G_RET_STS_SUCCESS;
176
177 EXCEPTION WHEN OTHERS THEN
178 CSM_UTIL_PKG.log( 'Exception in ' || g_object_name || '.APPLY_INSERT:' ||g_object_name || '.APPLY_INSERT',
179 FND_LOG.LEVEL_EXCEPTION );
180
181 x_return_status := FND_API.G_RET_STS_ERROR;
182 END APPLY_INSERT;
183 /***
184 This procedure is called by APPLY_INSERT when
185 an update is to be reverted.
186 ***/
187
188 PROCEDURE APPLY_UNDO
189 (
190 p_record IN c_task_assignments_inq%ROWTYPE,
191 p_error_msg OUT NOCOPY VARCHAR2,
192 x_return_status IN OUT NOCOPY VARCHAR2
193 )
194 IS
195
196 CURSOR c_get_undo_inq ( c_user_name VARCHAR2, c_tranid NUMBER,c_pk1_value NUMBER)
197 IS
198 SELECT SEQNO$$
199 FROM CSM_CLIENT_UNDO_REQUEST_INQ
200 WHERE tranid$$ = c_tranid
201 AND clid$$cs = c_user_name
202 AND PK1_VALUE = c_pk1_value;
203
204 CURSOR c_get_task_assignment(c_task_assignment_id NUMBER,c_user_id NUMBER)
205 IS
206 SELECT ACCESS_ID
207 FROM CSM_TASK_ASSIGNMENTS_ACC
208 WHERE TASK_ASSIGNMENT_ID = c_task_assignment_id
209 AND USER_ID = c_user_id;
210
211 CURSOR c_get_task(c_task_id NUMBER,c_user_id NUMBER,c_user_name VARCHAR2)
212 IS
213 SELECT ACCESS_ID,INQ.TRANID$$,INQ.SEQNO$$
214 FROM CSM_TASKS_ACC acc,
215 CSM_TASKS_INQ inq
216 WHERE acc.TASK_ID = c_task_id
217 AND acc.USER_ID = c_user_id
218 AND inq.TASK_ID = acc.TASK_ID
219 AND inq.CLID$$CS = c_user_name;
220
221 CURSOR c_get_incident(c_task_id NUMBER,c_user_id NUMBER,c_user_name VARCHAR2)
222 IS
223 SELECT ACCESS_ID,INQ.INCIDENT_ID,INQ.TRANID$$,INQ.SEQNO$$
224 FROM CSM_INCIDENTS_ALL_ACC acc,
225 JTF_TASKS_B b,
226 CSM_INCIDENTS_ALL_INQ inq
227 WHERE acc.USER_ID = c_user_id
228 AND b.TASK_ID = c_task_id
229 AND b.SOURCE_OBJECT_ID = acc.INCIDENT_ID
230 AND b.SOURCE_OBJECT_TYPE_CODE = 'SR'
231 AND acc.INCIDENT_ID = inq.INCIDENT_ID
232 AND inq.CLID$$CS = c_user_name;
233
234
235 CURSOR c_get_debrief_header(c_task_assg_id NUMBER,c_user_id NUMBER,c_user_name VARCHAR2)
236 IS
237 SELECT ACCESS_ID,INQ.DEBRIEF_HEADER_ID,INQ.TRANID$$,INQ.SEQNO$$
238 FROM CSM_DEBRIEF_HEADERS_ACC acc,
239 CSM_DEBRIEF_HEADERS_INQ inq
240 WHERE acc.USER_ID(+) = c_user_id
241 AND inq.TASK_ASSIGNMENT_ID = c_task_assg_id
242 AND inq.DEBRIEF_HEADER_ID = acc.DEBRIEF_HEADER_ID(+)
243 AND inq.CLID$$CS = c_user_name;
244
245
246 CURSOR c_get_debrief_expenses(c_task_assg_id NUMBER,c_user_id NUMBER,c_user_name VARCHAR2)
247 IS
248 SELECT ACCESS_ID,INQ.DEBRIEF_LINE_ID,INQ.TRANID$$,INQ.SEQNO$$
249 FROM CSM_DEBRIEF_LINES_ACC acc,
250 CSF_M_DEBRIEF_EXPENSES_INQ inq
251 WHERE acc.USER_ID(+) = c_user_id
252 AND inq.TASK_ASSIGNMENT_ID = c_task_assg_id
253 AND inq.DEBRIEF_LINE_ID = acc.DEBRIEF_LINE_ID(+)
254 AND inq.CLID$$CS = c_user_name;
255
256 CURSOR c_get_debrief_labor(c_task_assg_id NUMBER,c_user_id NUMBER,c_user_name VARCHAR2)
257 IS
258 SELECT ACCESS_ID,INQ.DEBRIEF_LINE_ID,INQ.TRANID$$,INQ.SEQNO$$
259 FROM CSM_DEBRIEF_LINES_ACC acc,
260 CSF_M_DEBRIEF_LABOR_INQ inq
261 WHERE acc.USER_ID(+) = c_user_id
262 AND inq.TASK_ASSIGNMENT_ID = c_task_assg_id
263 AND inq.DEBRIEF_LINE_ID = acc.DEBRIEF_LINE_ID(+)
264 AND inq.CLID$$CS = c_user_name;
265
266 CURSOR c_get_debrief_parts(c_task_assg_id NUMBER,c_user_id NUMBER,c_user_name VARCHAR2)
267 IS
268 SELECT ACCESS_ID,INQ.DEBRIEF_LINE_ID,INQ.TRANID$$,INQ.SEQNO$$
269 FROM CSM_DEBRIEF_LINES_ACC acc,
270 CSF_M_DEBRIEF_PARTS_INQ inq
271 WHERE acc.USER_ID(+) = c_user_id
272 AND inq.TASK_ASSIGNMENT_ID = c_task_assg_id
273 AND inq.DEBRIEF_LINE_ID = acc.DEBRIEF_LINE_ID(+)
274 AND inq.CLID$$CS = c_user_name;
275
276 CURSOR c_get_req_header(c_task_assg_id NUMBER,c_user_id NUMBER,c_user_name VARCHAR2)
277 IS
278 SELECT ACCESS_ID,INQ.REQUIREMENT_HEADER_ID,INQ.TRANID$$,INQ.SEQNO$$
279 FROM CSM_REQ_HEADERS_ACC acc,
280 CSM_REQ_HEADERS_INQ inq
281 WHERE acc.USER_ID(+) = c_user_id
282 AND inq.TASK_ASSIGNMENT_ID = c_task_assg_id
283 AND inq.REQUIREMENT_HEADER_ID = acc.REQUIREMENT_HEADER_ID(+)
284 AND inq.CLID$$CS = c_user_name;
285
286 CURSOR c_get_req_line(c_req_header_id NUMBER,c_user_id NUMBER,c_user_name VARCHAR2)
287 IS
288 SELECT ACCESS_ID,INQ.REQUIREMENT_LINE_ID,INQ.TRANID$$,INQ.SEQNO$$
289 FROM CSM_REQ_LINES_ACC acc,
290 CSM_REQ_LINES_INQ inq
291 WHERE acc.USER_ID (+) = c_user_id
292 AND inq.REQUIREMENT_HEADER_ID = c_req_header_id
293 AND inq.REQUIREMENT_LINE_ID = acc.REQUIREMENT_LINE_ID (+)
294 AND inq.CLID$$CS = c_user_name;
295
296 CURSOR c_get_notes(c_task_id NUMBER,c_incident_id NUMBER,c_debrief_header_id NUMBER,c_user_id NUMBER,c_user_name VARCHAR2)
297 IS
298 SELECT ACCESS_ID,INQ.JTF_NOTE_ID,INQ.TRANID$$,INQ.SEQNO$$, INQ.DMLTYPE$$
299 FROM CSM_NOTES_ACC acc,
300 CSF_M_NOTES_INQ inq
301 WHERE acc.USER_ID (+) = c_user_id
302 AND inq.JTF_NOTE_ID = acc.JTF_NOTE_ID (+)
303 AND inq.CLID$$CS = c_user_name
304 AND (
305 ( inq.SOURCE_OBJECT_CODE = 'TASK' AND inq.SOURCE_OBJECT_ID = c_task_id )
306 OR ( inq.SOURCE_OBJECT_CODE = 'SR' AND inq.SOURCE_OBJECT_ID = c_incident_id)
307 OR ( inq.SOURCE_OBJECT_CODE = 'SD' AND inq.SOURCE_OBJECT_ID = c_debrief_header_id)
308 );
309
310 CURSOR c_get_user_id (c_user_name VARCHAR2)
311 IS
312 SELECT USER_ID
313 FROM ASG_USER
314 WHERE USER_NAME = c_user_name;
315
316 --Pub items and object declarations
317
318
319 l_task_obj_name VARCHAR2(30) := 'CSM_TASKS_PKG'; -- package name
320 l_task_pub_name VARCHAR2(30) := 'CSM_TASKS';
321
322 l_sr_obj_name VARCHAR2(30) := 'CSM_SERVICE_REQUEST_PKG';
323 l_sr_pub_name VARCHAR2(30) := 'CSM_INCIDENTS_ALL';
324
325 l_dbh_obj_name VARCHAR2(30) := 'CSM_DEBRIEF_HEADERS_PKG';
326 l_dbh_pub_name VARCHAR2(30) := 'CSM_DEBRIEF_HEADERS';
327
328 l_dble_obj_name VARCHAR2(30) := 'CSM_DEBRIEF_EXPENSES_PKG';
329 l_dble_pub_name VARCHAR2(30) := 'CSF_M_DEBRIEF_EXPENSES';
330
331 l_dbll_obj_name VARCHAR2(30) := 'CSM_DEBRIEF_LABOR_PKG';
332 l_dbll_pub_name VARCHAR2(30) := 'CSF_M_DEBRIEF_LABOR';
333
334 l_dblp_obj_name VARCHAR2(30) := 'CSM_DEBRIEF_PARTS_PKG';
335 l_dblp_pub_name VARCHAR2(30) := 'CSF_M_DEBRIEF_PARTS';
336
337 l_notes_obj_name VARCHAR2(30) := 'CSM_NOTES_PKG';
338 l_notes_pub_name VARCHAR2(30) := 'CSF_M_NOTES';
339
340 l_reqh_obj_name VARCHAR2(30) := 'CSM_REQUIREMENTS_PKG';
341 l_reqh_pub_name VARCHAR2(30) := 'CSM_REQ_HEADERS';
342
343 l_reql_obj_name VARCHAR2(30) := 'CSM_REQUIREMENTS_PKG';
344 l_reql_pub_name VARCHAR2(30) := 'CSM_REQ_LINES';
345
346 l_undo_pub_name VARCHAR2(30) := 'CSM_CLIENT_UNDO_REQUEST';
347
348 l_access_id NUMBER;
349 l_mark_dirty BOOLEAN;
350 l_incident_id NUMBER;
351 l_debrief_header_id NUMBER;
352 l_tran_id NUMBER;
353 l_user_name VARCHAR2(100);
354 l_user_id NUMBER;
355 l_task_assignment_id NUMBER;
356 l_task_id NUMBER;
357 l_debrief_line_id NUMBER;
358 l_req_header_id NUMBER;
359 l_req_line_id NUMBER;
360 l_note_id NUMBER;
361 l_process_status VARCHAR2(1);
362 l_error_msg VARCHAR2(4000);
363 l_markdirty_all VARCHAR2(1):= 'Y';
364 l_sequence NUMBER;
365 l_dml_type VARCHAR2(1) := '';
366
367 BEGIN
368
369 x_return_status := FND_API.G_RET_STS_SUCCESS;
370
371 EXCEPTION WHEN OTHERS THEN
372 CSM_UTIL_PKG.log( 'Exception in ' || g_object_name || '.APPLY_UNDO:' ||g_object_name || '.APPLY_UNDO',
373 FND_LOG.LEVEL_EXCEPTION );
374 x_return_status := FND_API.G_RET_STS_ERROR;
375 END APPLY_UNDO;
376
377 /***
378 This procedure is called by APPLY_CRECORD when
379 an updated record is to be processed.
380 ***/
381 PROCEDURE APPLY_UPDATE
382 (
383 p_record IN c_task_assignments_inq%ROWTYPE,
384 p_error_msg OUT NOCOPY VARCHAR2,
385 x_return_status IN OUT NOCOPY VARCHAR2
386 ) IS
387
388 CURSOR c_task_assignment( b_task_assignment_id NUMBER)
389 IS
390 SELECT jta.task_id
391 , jta.object_version_number
392 , jta.last_update_date
393 , jta.last_updated_by
394 FROM jtf_task_assignments jta
395 WHERE jta.task_assignment_id = b_task_assignment_id;
396
397 r_task_assignment c_task_assignment%ROWTYPE;
398 l_ovn NUMBER;
399 l_profile_value VARCHAR2(240);
400
401 -- Declare OUT parameters
402 l_task_object_version_number NUMBER;
403 l_msg_count NUMBER;
404 l_msg_data VARCHAR2(4000);
405 l_task_status_id NUMBER;
406 l_task_status_name VARCHAR2(240);
407 l_task_type_id NUMBER;
408 l_audit_curr_prf varchar2(100);
409
410 BEGIN
411
412 -- Ask for the task. It must exist. Exceptions will abort.
413 OPEN c_task_assignment( b_task_assignment_id => p_record.task_assignment_id);
414 FETCH c_task_assignment INTO r_task_assignment;
415 l_ovn := r_task_assignment.object_version_number;
416 CLOSE c_task_assignment;
417
418 --check for the stale data
419 l_profile_value := fnd_profile.value(csm_profile_pkg.g_JTM_APPL_CONFLICT_RULE);
420 -- SERVER_WINS profile value
421 IF(l_profile_value = csm_profile_pkg.g_SERVER_WINS AND
422 ASG_DEFER.IS_DEFERRED(p_record.clid$$cs, p_record.tranid$$,g_pub_name, p_record.seqno$$) <> FND_API.G_TRUE)
423 THEN
424 IF(r_task_assignment.last_update_date <> p_record.server_last_update_date AND r_task_assignment.last_updated_by <> asg_base.get_user_id(p_record.clid$$cs)) THEN
425 p_error_msg := 'UPWARD SYNC CONFLICT: CLIENT LOST For JTF_TASK_ASSIGNMENTS: CSM_TASK_ASSIGNMENTS_PKG.APPLY_UPDATE: P_KEY = '
426 || p_record.task_assignment_id;
427 x_return_status := FND_API.G_RET_STS_ERROR;
428 csm_util_pkg.log(p_error_msg,
429 g_object_name || '.APPLY_UPDATE',
430 FND_LOG.LEVEL_ERROR);
431 RETURN;
432 END IF;
433 END IF;
434
435
436 -- The column assignment_status_id is the status of the task_assignment_id.
437 -- The column task_assignment_id is task_assignment_id.
438 -- Validation is not a good thing for this particular API-call: as
439 -- the palm application does the check for state changes, it is not
440 -- necessary to redo them here. Even worse, a state change in two steps
441 -- A -> B and B -> C may be OK for palm application, but if the intermediate
442 -- step is not sent to CRM, the API will see A -> C and refuse it.
443 -- To allow for A -> C no validation is done.
444 /* Bug 16013131 */
445 BEGIN
446 EXECUTE IMMEDIATE 'BEGIN CSF_DEBRIEF_PUB.SET_AUTO_DEBRIEF(FND_API.TO_BOOLEAN(:1)); END;'
447 USING FND_API.G_FALSE;
448 EXCEPTION
449 WHEN OTHERS THEN
450 CSM_UTIL_PKG.LOG('Exception raised when calling csf_debrief_pub.set_auto_debrief(FALSE): ' || SQLERRM,
451 'CSM_TASK_ASSIGNMENT_PKG.apply_update', FND_LOG.LEVEL_PROCEDURE);
452 END;
453
454 l_audit_curr_prf:=CSM_UTIL_PKG.set_task_audit(p_new_val=>'N' ,p_old_val=> NULL);
455
456 csf_task_assignments_pub.update_assignment_status
457 ( p_api_version => 1.0
458 , p_init_msg_list => FND_API.G_TRUE
459 , p_commit => FND_API.G_FALSE
460 , p_validation_level => FND_API.G_VALID_LEVEL_NONE
461 , x_return_status => x_return_status
462 , x_msg_count => l_msg_count
463 , x_msg_data => l_msg_data
464 , p_task_assignment_id => p_record.task_assignment_id
465 , p_assignment_status_id => p_record.assignment_status_id
466 , p_object_version_number => l_ovn
467 , p_update_task => 'T'
468 , x_task_object_version_number => l_task_object_version_number
469 , x_task_status_id => l_task_status_id
470 );
471
472 l_audit_curr_prf:=CSM_UTIL_PKG.set_task_audit(p_new_val=>'Y' ,p_old_val=> l_audit_curr_prf);
473
474 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
475 THEN
476
477 x_return_status := FND_API.G_RET_STS_ERROR;
478 CSM_UTIL_PKG.log( 'Error in ' || g_object_name || '.APPLY_UPDATE:'
479 || ' ROOT ERROR: csf_tasks_pub.update_assignment_status'
480 || ' for PK ' || p_record.TASK_ASSIGNMENT_ID,
481 g_object_name || '.APPLY_UPDATE',FND_LOG.LEVEL_ERROR );
482 RETURN ;
483 END IF;
484
485 -- Also need to update the actual times of the task assignment update
486 -- use a different procedure for this because the csf_tasks_pub.update_assignment_status
487 -- doesn't support the actual times and the csf_tasks_pub.Update_Task_Assignment doen't
488 -- support the validation level set to none.
489 csf_task_assignments_pub.Update_Task_Assignment
490 ( p_api_version => 1.0
491 , p_init_msg_list => FND_API.G_TRUE
492 , p_commit => FND_API.G_FALSE
493 , p_task_assignment_id => p_record.task_assignment_id
494 , p_object_version_number => l_ovn
495 , p_task_id => p_record.task_id
496 , p_resource_type_code => FND_API.G_MISS_CHAR
497 , p_resource_id => FND_API.G_MISS_NUM --p_record.resource_id
498 , p_resource_territory_id => FND_API.G_MISS_NUM
499 , p_assignment_status_id => FND_API.G_MISS_NUM
500 , p_actual_start_date => p_record.actual_start_date
501 , p_actual_end_date => p_record.actual_end_date
502 , p_sched_travel_distance => FND_API.G_MISS_NUM
503 , p_sched_travel_duration => FND_API.G_MISS_NUM
504 , p_sched_travel_duration_uom => FND_API.G_MISS_CHAR
505 , p_shift_construct_id => FND_API.G_MISS_NUM
506 , p_object_capacity_id => FND_API.G_MISS_NUM
507 , p_attribute1 => p_record.attribute1
508 , p_attribute2 => p_record.attribute2
509 , p_attribute3 => p_record.attribute3
510 , p_attribute4 => p_record.attribute4
511 , p_attribute5 => p_record.attribute5
512 , p_attribute6 => p_record.attribute6
513 , p_attribute7 => p_record.attribute7
514 , p_attribute8 => p_record.attribute8
515 , p_attribute9 => p_record.attribute9
516 , p_attribute10 => p_record.attribute10
517 , p_attribute11 => p_record.attribute11
518 , p_attribute12 => p_record.attribute12
519 , p_attribute13 => p_record.attribute13
520 , p_attribute14 => p_record.attribute14
521 , p_attribute15 => p_record.attribute15
522 --Bug 5182470
523 , p_attribute_category => p_record.attribute_category
524 , x_return_status => x_return_status
525 , x_msg_count => l_msg_count
526 , x_msg_data => l_msg_data
527 , x_task_object_version_number => l_task_object_version_number
528 , x_task_status_id => l_task_status_id
529 );
530
531 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
532 THEN
533 x_return_status := FND_API.G_RET_STS_ERROR;
534
535 CSM_UTIL_PKG.log( 'Error in ' || g_object_name || '.APPLY_UPDATE:'
536 || ' ROOT ERROR: csf_tasks_pub.Update_Task_Assignment'
537 || ' for PK ' || p_record.TASK_ASSIGNMENT_ID,
538 g_object_name || '.APPLY_UPDATE',
539 FND_LOG.LEVEL_ERROR );
540 RETURN ;
541 END IF;
542
543 -- success
544 x_return_status := FND_API.G_RET_STS_SUCCESS;
545
546 EXCEPTION
547 WHEN others THEN
548 CSM_UTIL_PKG.log( 'Exception in ' || g_object_name || '.APPLY_UPDATE:'
549 || ' for PK ' || p_record.task_assignment_id,
550 g_object_name || '.APPLY_UPDATE',
551 FND_LOG.LEVEL_EXCEPTION );
552
553 x_return_status := FND_API.G_RET_STS_ERROR;
554
555 END APPLY_UPDATE;
556
557
558 /***
559 This procedure is called by APPLY_CLIENT_CHANGES when a record
560 is to be processed.
561 ***/
562 PROCEDURE APPLY_RECORD
563 (
564 p_record IN c_task_assignments_inq%ROWTYPE,
565 p_error_msg OUT NOCOPY VARCHAR2,
566 x_return_status IN OUT NOCOPY VARCHAR2
567 )
568 IS
569 CURSOR c_new_status_closed(b_task_status_id NUMBER)
570 IS
571 SELECT closed_flag
572 FROM jtf_task_statuses_b
573 WHERE task_status_id=b_task_status_id;
574 l_is_closed VARCHAR2(100):='N';
575 BEGIN
576
577 /*** initialize return status and message list ***/
578 x_return_status := FND_API.G_RET_STS_SUCCESS;
579 FND_MSG_PUB.INITIALIZE;
580
581
582 IF p_record.dmltype$$='I' THEN
583 -- Process insert
584 APPLY_INSERT
585 (
586 p_record,
587 p_error_msg,
588 x_return_status
589 );
590 ELSIF p_record.dmltype$$='U' THEN
591
592 --Bug 16339730 --if task is closed in same txn, process debrief-s first
593 OPEN c_new_status_closed(p_record.assignment_status_id);
594 FETCH c_new_status_closed INTO l_is_closed;
595 CLOSE c_new_status_closed;
596
597 IF l_is_closed='Y' THEN
598 CSM_UTIL_PKG.LOG('Task is about to be closed. So Processing debriefs first ' ,'CSM_TASK_ASSIGNMENT_PKG.apply_record', FND_LOG.LEVEL_PROCEDURE);
599 CSM_DEBRIEF_HEADERS_PKG.APPLY_CLIENT_CHANGES(p_record.clid$$cs, p_record.tranid$$, g_debug_level, x_return_status,p_record.task_assignment_id);
600 CSM_DEBRIEF_EXPENSES_PKG.APPLY_CLIENT_CHANGES(p_record.clid$$cs, p_record.tranid$$, g_debug_level, x_return_status,p_record.task_assignment_id);
601 CSM_DEBRIEF_LABOR_PKG.APPLY_CLIENT_CHANGES(p_record.clid$$cs, p_record.tranid$$, g_debug_level, x_return_status,p_record.task_assignment_id);
602 CSM_DEBRIEF_PARTS_PKG.APPLY_CLIENT_CHANGES(p_record.clid$$cs, p_record.tranid$$, g_debug_level, x_return_status,p_record.task_assignment_id);
603 END IF;
604
605 -- Process update
606 APPLY_UPDATE
607 (
608 p_record,
609 p_error_msg,
610 x_return_status
611 );
612 ELSE
613 -- Process delete and insert;
614 -- Not supported for this entity
615 CSM_UTIL_PKG.LOG
616 ( 'Delete and Insert is not supported for this entity'
617 || ' for PK ' || p_record.task_assignment_id ,
618 g_object_name || '.APPLY_RECORD',
619 FND_LOG.LEVEL_ERROR);
620
621 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
622 (
623 p_message => 'CSM_DML_OPERATION'
624 , p_token_name1 => 'DML'
625 , p_token_value1 => p_record.dmltype$$
626 );
627
628 x_return_status := FND_API.G_RET_STS_ERROR;
629 END IF;
630
631 EXCEPTION WHEN OTHERS THEN
632 /*** defer record when any process exception occurs ***/
633 CSM_UTIL_PKG.LOG
634 ( 'Exception occurred in ' || g_object_name || '.APPLY_RECORD:' || ' ' || SQLERRM
635 || ' for PK ' || p_record.task_assignment_id ,
636 g_object_name || '.APPLY_RECORD',
637 FND_LOG.LEVEL_EXCEPTION);
638 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_RECORD', SQLERRM);
639 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
640 (
641 p_api_error => TRUE
642 );
643
644 x_return_status := FND_API.G_RET_STS_ERROR;
645
646 END APPLY_RECORD;
647
648
649 /***
650 APPLY_CLIENT_CHANGE procedure is called by SM_SERVICEP_WRAPPER_PKG, for upward sync of
651 publication item CSM_TASK_ASSIGNMENTS
652 ***/
653 PROCEDURE APPLY_CLIENT_CHANGES
654 (
655 p_user_name IN VARCHAR2,
656 p_tranid IN NUMBER,
657 p_debug_level IN NUMBER,
658 x_return_status IN OUT NOCOPY VARCHAR2
659 )
660 IS
661 l_process_status VARCHAR2(1);
662 l_error_msg VARCHAR2(4000);
663
664 -- ER 3079918
665 CURSOR c_chk_task_status( p_task_assignment_id NUMBER)
666 IS
667 SELECT dh.debrief_header_id, tst.rejected_flag, tst.on_hold_flag,
668 tst.cancelled_flag, tst.closed_flag, tst.completed_flag
669 FROM csf_debrief_headers dh, jtf_task_assignments tas,
670 jtf_task_statuses_b tst
671 WHERE dh.task_assignment_id = tas.task_assignment_id
672 AND tas.assignment_status_id = tst.task_status_id
673 AND tas.task_assignment_id = p_task_assignment_id;
674
675 CURSOR c_check_undo_request(c_task_assignment_id NUMBER, c_tranid NUMBER)
676 IS
677 SELECT PK1_VALUE
678 FROM CSM_CLIENT_UNDO_REQUEST_INQ
679 WHERE PUB_ITEM = 'CSM_TASK_ASSIGNMENTS'
680 AND TRANID$$ = c_tranid
681 AND PK1_VALUE = c_task_assignment_id;
682
683 l_rejected_flag VARCHAR2(1);
684 l_on_hold_flag VARCHAR2(1);
685 l_cancelled_flag VARCHAR2(1);
686 l_closed_flag VARCHAR2(1);
687 l_completed_flag VARCHAR2(1);
688 l_dbl_count NUMBER := NULL;
689 l_header_id NUMBER := NULL;
690 l_task_assignment_id NUMBER := NULL;
691 l_err_message VARCHAR2(4000);
692 l_return_status VARCHAR2(100);
693 l_user_id NUMBER; l_resp_id NUMBER;l_app_id NUMBER;
694 BEGIN
695 csm_util_pkg.log
696 ( g_object_name || '.APPLY_CLIENT_CHANGES entered',
697 g_object_name || '.APPLY_CLIENT_CHANGES',
698 FND_LOG.LEVEL_PROCEDURE);
699 g_debug_level := p_debug_level;
700 x_return_status := FND_API.G_RET_STS_SUCCESS;
701
702 /*** loop through task assignments records in inqueue ***/
703 FOR r_task_assignments IN c_task_assignments_inq( p_user_name, p_tranid) LOOP
704
705 SAVEPOINT save_rec;
706
707 /*** apply record ***/
708 APPLY_RECORD
709 (
710 r_task_assignments
711 , l_error_msg
712 , l_process_status
713 );
714
715 /*** was record processed successfully? ***/
716 IF l_process_status = FND_API.G_RET_STS_SUCCESS THEN
717 -- if update to charges fail, then do not defer the record
718 BEGIN
719 OPEN c_chk_task_status (r_task_assignments.task_assignment_id);
720 FETCH c_chk_task_status INTO l_header_id, l_rejected_flag,
721 l_on_hold_flag, l_cancelled_flag, l_closed_flag, l_completed_flag;
722
723 IF c_chk_task_status%FOUND THEN
724 IF ( (l_rejected_flag='Y') OR (l_on_hold_flag='Y')
725 OR (l_cancelled_flag='Y') OR (l_closed_flag='Y')
726 OR (l_completed_flag='Y') ) THEN
727
728 select user_id,responsibility_id,app_id
729 into l_user_id ,l_resp_id ,l_app_id
730 from asg_user where user_name=p_user_name;
731
732 fnd_global.apps_initialize(l_user_id, l_resp_id, l_app_id); --Bug 16192129
733
734 csf_debrief_update_pkg.form_Call (1.0, l_header_id);
735 END IF;
736 END IF;
737
738 CLOSE c_chk_task_status;
739 EXCEPTION
740 WHEN others THEN
741 NULL;
742 END;
743
744 /*** If Yes -> delete record from inqueue ***/
745 CSM_UTIL_PKG.DELETE_RECORD
746 (
747 p_user_name,
748 p_tranid,
749 r_task_assignments.seqno$$,
750 r_task_assignments.task_assignment_id,
751 g_object_name,
752 g_pub_name,
753 l_error_msg,
754 l_process_status
755 );
756
757 /*** was delete successful? ***/
758 IF l_process_status = FND_API.G_RET_STS_SUCCESS THEN
759 --Task Assignment Audit information is not important and hence
760 -- Task Assignment Audit information failure should not be
761 -- considered as failure and stops TA Upload
762 --Call Task Assignment Audit Upload
763 CSM_TA_AUDIT_PKG.APPLY_CLIENT_CHANGES
764 (
765 p_user_name =>p_user_name,
766 p_tranid =>p_tranid,
767 p_assignment_id =>r_task_assignments.task_assignment_id,
768 p_debug_level =>g_debug_level,
769 x_return_status =>l_process_status
770 );
771
772 /*** was TA AUDIT UPLOAD successful? ***/
773 IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
774 /*** If No -> rollback ***/
775 CSM_UTIL_PKG.LOG
776 ( 'Inserting Task Assignment Audit Failed, Task Assignment Audit Failed for '
777 || ' for PK ' || r_task_assignments.task_assignment_id ,
778 g_object_name || '.APPLY_CLIENT_CHANGES',
779 FND_LOG.LEVEL_ERROR); -- put PK column here
780 END IF;
781
782 ELSE
783 /*** If No -> rollback ***/
784 CSM_UTIL_PKG.LOG
785 ( 'Deleting from inqueue failed, rolling back to savepoint'
786 || ' for PK ' || r_task_assignments.task_assignment_id ,
787 g_object_name || '.APPLY_CLIENT_CHANGES',
788 FND_LOG.LEVEL_ERROR); -- put PK column here
789 ROLLBACK TO save_rec;
790 x_return_status := FND_API.G_RET_STS_ERROR;
791 END IF;
792 ELSIF l_process_Status <> FND_API.G_RET_STS_SUCCESS THEN
793 /*** Record was not processed successfully or delete failed
794 -> defer and reject record ***/
795 CSM_UTIL_PKG.LOG
796 ( 'Record not processed successfully, deferring and rejecting record'
797 || ' for PK ' || r_task_assignments.task_assignment_id ,
798 g_object_name || '.APPLY_CLIENT_CHANGES',
799 FND_LOG.LEVEL_ERROR); -- put PK column here
800
801 CSM_UTIL_PKG.DEFER_RECORD
802 ( p_user_name
803 , p_tranid
804 , r_task_assignments.seqno$$
805 , r_task_assignments.task_assignment_id
806 , g_object_name
807 , g_pub_name
808 , l_error_msg
809 , l_process_status
810 , r_task_assignments.dmltype$$
811 );
812
813 /*** Was defer successful? ***/
814 IF l_process_status = FND_API.G_RET_STS_SUCCESS THEN
815 CSM_UTIL_PKG.LOG
816 ( 'Inserting Task Assignment Audit , Task Assignment Audit for '
817 || ' for PK ' || r_task_assignments.task_assignment_id ,
818 g_object_name || '.APPLY_CLIENT_CHANGES',
819 FND_LOG.LEVEL_ERROR); -- put PK column here
820
821 CSM_TA_AUDIT_PKG.DEFER_CLIENT_CHANGES
822 (
823 p_user_name =>p_user_name,
824 p_tranid =>p_tranid,
825 p_assignment_id =>r_task_assignments.task_assignment_id,
826 p_debug_level =>g_debug_level,
827 x_return_status =>l_process_status
828 );
829
830 END IF;
831
832 IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
833 /*** no -> rollback ***/
834 CSM_UTIL_PKG.LOG
835 ( 'Defer record failed, rolling back to savepoint'
836 || ' for PK ' || r_task_assignments.task_assignment_id ,
837 g_object_name || '.APPLY_CLIENT_CHANGES',
838 FND_LOG.LEVEL_ERROR); -- put PK column here
839 ROLLBACK TO save_rec;
840 x_return_status := FND_API.G_RET_STS_ERROR;
841 END IF;
842
843 END IF;
844 END LOOP;
845
846 EXCEPTION WHEN OTHERS THEN
847 IF c_chk_task_status%ISOPEN THEN
848 CLOSE c_chk_task_status;
849 END IF;
850
851 /*** catch and log exceptions ***/
852 CSM_UTIL_PKG.LOG
853 ( 'Exception occurred in ' || g_object_name || '.APPLY_CLIENT_CHANGES:' || ' ' || SQLERRM,
854 g_object_name || '.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_EXCEPTION);
855 x_return_status := FND_API.G_RET_STS_ERROR;
856
857 END APPLY_CLIENT_CHANGES;
858
859 FUNCTION CONFLICT_RESOLUTION_METHOD (p_user_name IN VARCHAR2,
860 p_tran_id IN NUMBER,
861 p_sequence IN NUMBER)
862 RETURN VARCHAR2 IS
863 l_profile_value VARCHAR2(30) ;
864 l_user_id NUMBER ;
865 cursor get_user_id(l_tran_id in number,
866 l_user_name in varchar2,
867 l_sequence in number)
868 IS
869 SELECT b.last_updated_by
870 FROM JTF_TASK_ASSIGNMENTS b,
871 CSM_TASK_ASSIGNMENTS_INQ a
872 WHERE a.clid$$cs = l_user_name
873 AND tranid$$ = l_tran_id
874 AND seqno$$ = l_sequence
875 AND a.task_assignment_id = b.task_assignment_id ;
876
877 BEGIN
878 CSM_UTIL_PKG.LOG('Entering CSM_TASK_ASSIGNMENTS_PKG.CONFLICT_RESOLUTION_METHOD for user ' || p_user_name ,'CSM_TASK_ASSIGNMENTS_PKG.CONFLICT_RESOLUTION_METHOD',FND_LOG.LEVEL_PROCEDURE);
879 l_profile_value := fnd_profile.value(csm_profile_pkg.g_JTM_APPL_CONFLICT_RULE);
880 OPEN get_user_id(p_tran_id, p_user_name, p_sequence) ;
881 FETCH get_user_id
882 INTO l_user_id ;
883 CLOSE get_user_id ;
884
885 if l_profile_value = 'SERVER_WINS' AND l_user_id <> asg_base.get_user_id(p_user_name) then
886 RETURN 'S' ;
887 else
888 RETURN 'C' ;
889 END IF ;
890
891 EXCEPTION
892 WHEN OTHERS THEN
893 RETURN 'C';
894 END CONFLICT_RESOLUTION_METHOD;
895
896 END CSM_TASK_ASSIGNMENTS_PKG; -- Package spec