[Home] [Help]
PACKAGE BODY: APPS.CSM_TASKS_PKG
Source
1 PACKAGE BODY CSM_TASKS_PKG AS
2 /* $Header: csmutskb.pls 120.19.12020000.2 2013/04/09 11:07:02 saradhak ship $ */
3
4 /*
5 * The function to be called by CSM_SERVICEP_WRAPPER_PKG, for upward sync of
6 * publication item CSM_TASKS
7 */
8 -- Purpose: Update Tasks changes on Handheld to Enterprise database
9 --
10 -- MODIFICATION HISTORY
11 -- Person Date Comments
12 -- DBhagat 11th September 2002 Created
13 --
14 -- --------- ------------------- ------------------------------------------
15 -- Enter package declarations as shown below
16
17 /*** Globals ***/
18 g_object_name CONSTANT VARCHAR2(30) := 'CSM_TASKS_PKG'; -- package name
19 G_PUB_NAME CONSTANT VARCHAR2(30) := 'CSM_TASKS'; -- publication item name
20 G_DEBUG_LEVEL NUMBER; -- debug level
21
22 CURSOR c_tasks_inq( b_user_name VARCHAR2, b_tranid NUMBER) IS
23 SELECT *
24 FROM csm_tasks_inq
25 WHERE tranid$$ = b_tranid
26 AND clid$$cs = b_user_name;
27
28
29 /***
30 This procedure is called by APPLY_CRECORD when
31 an inserted record is to be processed.
32 ***/
33 PROCEDURE APPLY_INSERT
34 (
35 p_record IN c_tasks_inq%ROWTYPE,
36 p_error_msg OUT NOCOPY VARCHAR2,
37 x_return_status IN OUT NOCOPY VARCHAR2,
38 x_reject_row OUT NOCOPY BOOLEAN --Bug 5288413
39 ) IS
40 --Bug 5288413
41 CURSOR c_is_private_owner(b_task_type_id NUMBER) IS
42 SELECT decode(NVL(PRIVATE_FLAG,'N'),'Y',1,0)
43 FROM JTF_TASK_TYPES_B
44 WHERE TASK_TYPE_ID = b_task_type_id;
45
46 --Bug 5288413
47 CURSOR c_res_id (b_user_name VARCHAR2) IS
48 SELECT RESOURCE_ID
49 FROM ASG_USER
50 WHERE USER_NAME=b_user_name;
51
52 CURSOR c_csm_appl IS
53 SELECT APPLICATION_ID
54 FROM fnd_application
55 WHERE application_short_name = 'CSM';
56
57 CURSOR c_csm_resp(c_user_id NUMBER)
58 IS
59 SELECT RESPONSIBILITY_ID
60 FROM ASG_USER
61 WHERE USER_ID = c_user_id;
62
63 -- Declare OUT parameters
64 l_msg_count NUMBER;
65 l_msg_data VARCHAR2(240);
66 l_task_id jtf_tasks_b.task_id%TYPE;
67
68 -- Declare default parameters
69 l_task_type jtf_tasks_b.task_type_id%TYPE;
70 l_task_status jtf_tasks_b.task_status_id%TYPE;
71 l_task_priority jtf_tasks_b.task_priority_id%TYPE;
72 l_task_source_object_name jtf_tasks_b.source_object_name%TYPE;
73 l_address_id JTF_TASKS_B.ADDRESS_ID%TYPE := null;
74 l_customer_id JTF_TASKS_B.CUSTOMER_ID%TYPE;
75 l_incident_location_type CS_INCIDENTS_ALL_B.INCIDENT_LOCATION_TYPE%TYPE;
76 l_incident_location_id CS_INCIDENTS_ALL_B.INCIDENT_LOCATION_ID%TYPE;
77 l_location_id JTF_TASKS_B.LOCATION_ID%TYPE := null;
78 l_owner_id JTF_tasks_b.owner_id%type :=null;
79 l_owner_type JTF_tasks_b.owner_type_code%type :=null;
80 l_is_private NUMBER;
81 l_sync_resource_id l_owner_id%TYPE;
82 l_responsibility_id NUMBER;
83 l_csm_appl_id NUMBER;
84 l_territory_assign VARCHAR2(255);
85 l_service_request_rec CS_ServiceRequest_PUB.service_request_rec_type;
86 l_task_attribute_rec CS_SR_TASK_AUTOASSIGN_PKG.SR_Task_rec_type;
87 l_owner_group_id NUMBER;
88 l_group_type VARCHAR(240);
89 l_territory_id NUMBER;
90 l_profile_value VARCHAR2(240);
91
92 BEGIN
93
94 -- Retrieve default value if null according to profiles --
95 IF p_record.task_type_id IS NULL THEN
96 l_task_type := csm_profile_pkg.value_specific('JTF_TASK_DEFAULT_TASK_TYPE', p_record.created_by);
97 ELSE
98 l_task_type := p_record.task_type_id;
99 END IF;
100
101 --Bug 5288413
102 x_reject_row :=TRUE;
103
104 OPEN c_res_id(p_record.clid$$cs);
105 FETCH c_res_id INTO l_sync_resource_id;
106 CLOSE c_res_id;
107
108 OPEN c_is_private_owner(l_task_type);
109 FETCH c_is_private_owner INTO l_is_private;
110 CLOSE c_is_private_owner;
111
112 -- Bug 5336807
113 l_task_priority := p_record.task_priority_id;
114
115 IF p_record.task_status_id IS NULL THEN
116 l_task_status := csm_profile_pkg.value_specific('CSF_DEFAULT_TASK_INPLANNING_STATUS', p_record.created_by);
117 ELSE
118 l_task_status := p_record.task_status_id;
119 END IF;
120
121 --R12Asset
122 IF p_record.source_object_type_code = 'SR' THEN
123 SELECT INCIDENT_NUMBER, INCIDENT_LOCATION_ID,
124 CUSTOMER_ID,INCIDENT_LOCATION_TYPE
125 INTO l_task_source_object_name , l_incident_location_id,
126 l_customer_id,l_incident_location_type
127 FROM CS_INCIDENTS_ALL_B
128 WHERE INCIDENT_ID = p_record.source_object_id;
129
130 -- Note: location_type is HZ_LOCATION in CS_INCIDENTS_ALL_B and
131 -- HZ_LOCATIONS in CSI_ITEM_INSTANCES
132 IF l_incident_location_type = 'HZ_LOCATION' THEN
133 l_location_id := l_incident_location_id;
134 ELSE
135 l_address_id := l_incident_location_id;
136 END IF;
137 ELSE --Personal Task
138 l_location_id := p_record.location_id;
139 END IF;
140
141 --Get Mobile responsibility
142 OPEN c_csm_resp(p_record.created_by);
143 FETCH c_csm_resp INTO l_responsibility_id;
144 CLOSE c_csm_resp;
145 -- get csm application id
146 OPEN c_csm_appl;
147 FETCH c_csm_appl INTO l_csm_appl_id;
148 CLOSE c_csm_appl;
149
150 --Get Task Assignment Manger profile
151 l_territory_assign := fnd_profile.value_specific('CSM_SELECT_TASK_THRU_TERRITORY_ASSIGN'
152 , p_record.created_by
153 , l_responsibility_id
154 , l_csm_appl_id);
155
156 IF l_territory_assign = 'Y' AND p_record.source_object_type_code ='SR' THEN
157
158 l_task_attribute_rec.task_type_id := l_task_type;
159 l_task_attribute_rec.task_status_id := l_task_status;
160 l_task_attribute_rec.task_priority_id := l_task_priority;
161
162 CS_SR_TASK_AUTOASSIGN_PKG.Assign_Task_Resource
163 (p_api_version => 1.0,
164 p_init_msg_list => fnd_api.g_true,
165 p_commit => fnd_api.g_false,
166 p_incident_id => p_record.source_object_id,
167 p_service_request_rec => l_service_request_rec,
168 p_task_attribute_rec => l_task_attribute_rec,
169 x_owner_group_id => l_owner_group_id,
170 x_group_type => l_group_type,
171 x_owner_type => l_owner_type,
172 x_owner_id => l_owner_id,
173 x_territory_id => l_territory_id,
174 x_return_status => x_return_status,
175 x_msg_count => l_msg_count,
176 x_msg_data => l_msg_data
177 );
178
179 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
180 IF l_owner_id IS NULL THEN
181 l_owner_type := l_group_type;
182 l_owner_id := l_owner_group_id;
183 END IF;
184 ELSE
185 x_return_status := FND_API.G_RET_STS_ERROR;
186
187 CSM_UTIL_PKG.log( 'Error in ' || g_object_name || '.APPLY_INSERT:'
188 || ' ROOT ERROR: CS_SR_TASK_AUTOASSIGN_PKG.Assign_Task_Resource'
189 || ' for PK ' || p_record.TASK_ID || l_msg_data,
190 g_object_name || '.APPLY_INSERT',
191 FND_LOG.LEVEL_ERROR );
192 END IF;
193 ELSE
194 l_owner_type := csm_profile_pkg.value_specific('INC_DEFAULT_INCIDENT_TASK_OWNER_TYPE', p_record.created_by);
195 l_owner_id := csm_profile_pkg.value_specific('INC_DEFAULT_INCIDENT_TASK_OWNER', p_record.created_by);
196
197 END IF;
198
199 IF l_is_private=1 OR l_owner_type IS NULL OR l_owner_id IS NULL THEN
200 l_owner_type := 'RS_EMPLOYEE';
201 l_owner_id :=l_sync_resource_id;
202 END IF;
203
204 -- Create new Task
205 jtf_tasks_pub.Create_Task
206 ( p_api_version => 1.0,
207 p_init_msg_list => fnd_api.g_true,
208 p_commit => fnd_api.g_false,
209 p_task_id => p_record.task_id,
210 p_task_name => p_record.task_name,
211 p_task_type_id => l_task_type,
212 p_description => p_record.description,
213 p_task_status_id => l_task_status,
214 p_task_priority_id => l_task_priority,
215 p_owner_type_code => l_owner_type,
216 p_owner_id => l_owner_id,
217 p_owner_territory_id => l_territory_id,
218 p_planned_start_date => p_record.planned_start_date,
219 p_planned_end_date => p_record.planned_end_date,
220 p_scheduled_start_date => p_record.scheduled_start_date,
221 p_scheduled_end_date => p_record.scheduled_end_date,
222 -- bug 4248868
223 -- p_actual_start_date => p_record.actual_start_date,
224 -- p_actual_end_date => p_record.actual_end_date,
225 p_source_object_type_code => p_record.source_object_type_code,
226 p_source_object_id => p_record.source_object_id,
227 p_source_object_name => l_task_source_object_name,
228 p_planned_effort => p_record.planned_effort,
229 p_planned_effort_uom => p_record.planned_effort_uom,
230 p_escalation_level => p_record.escalation_level,
231 p_address_id => l_address_id,
232 x_return_status => x_return_status,
233 x_msg_count => l_msg_count,
234 x_msg_data => l_msg_data,
235 x_task_id => l_task_id,
236 p_attribute1 => p_record.attribute1,
237 p_attribute2 => p_record.attribute2,
238 p_attribute3 => p_record.attribute3,
239 p_attribute4 => p_record.attribute4,
240 p_attribute5 => p_record.attribute5,
241 p_attribute6 => p_record.attribute6,
242 p_attribute7 => p_record.attribute7,
243 p_attribute8 => p_record.attribute8,
244 p_attribute9 => p_record.attribute9,
245 p_attribute10 => p_record.attribute10,
246 p_attribute11 => p_record.attribute11,
247 p_attribute12 => p_record.attribute12,
248 p_attribute13 => p_record.attribute13,
249 p_attribute14 => p_record.attribute14,
250 p_attribute15 => p_record.attribute15,
251 p_attribute_category => p_record.attribute_category,
252 p_customer_id => NVL(p_record.customer_id, l_customer_id),
253 p_location_id => l_location_id,
254 p_cust_account_id => p_record.cust_account_id
255 );
256
257 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
258 THEN
259 x_return_status := FND_API.G_RET_STS_ERROR;
260
261 CSM_UTIL_PKG.log( 'Error in ' || g_object_name || '.APPLY_INSERT:'
262 || ' ROOT ERROR: jtf_tasks_pub.Create_Task'
263 || ' for PK ' || p_record.TASK_ID,
264 g_object_name || '.APPLY_INSERT',
265 FND_LOG.LEVEL_ERROR );
266 RETURN ;
267 END IF;
268
269 -- success
270 x_return_status := FND_API.G_RET_STS_SUCCESS;
271
272 EXCEPTION
273 WHEN others THEN
274 CSM_UTIL_PKG.log( 'Exception in ' || g_object_name || '.APPLY_INSERT:'
275 || ' for PK ' || p_record.task_id,
276 g_object_name || '.APPLY_INSERT',
277 FND_LOG.LEVEL_EXCEPTION );
278
279 x_return_status := FND_API.G_RET_STS_ERROR;
280
281 END APPLY_INSERT;
282
283
284 /***
285 This procedure is called by APPLY_CRECORD when
286 an updated record is to be processed.
287 For CSM 11583, we support updates on the DFF columns
288 ***/
289 PROCEDURE APPLY_UPDATE
290 (
291 p_record IN c_tasks_inq%ROWTYPE,
292 p_error_msg OUT NOCOPY VARCHAR2,
293 x_return_status IN OUT NOCOPY VARCHAR2
294 )
295 IS
296 CURSOR c_task ( b_task_id NUMBER ) IS
297 SELECT object_version_number
298 FROM jtf_tasks_b
299 WHERE task_id = b_task_id;
300
301 CURSOR c_last_update_date ( b_task_id NUMBER)
302 IS
303 SELECT LAST_UPDATE_DATE,
304 last_updated_by
305 FROM jtf_tasks_b
306 WHERE task_id = b_task_id;
307
308 --variable declarations
309 r_task c_task%ROWTYPE;
310 r_last_update_date c_last_update_date%ROWTYPE;
311 l_profile_value VARCHAR2(240);
312 l_msg_count NUMBER;
313 l_msg_data VARCHAR2(240);
314 l_uom_class MTL_UNITS_OF_MEASURE.UOM_CLASS%TYPE;
315 l_plan_eff_uom CSM_UNIT_OF_MEASURE_TL_ACC.UOM_CODE%TYPE;
316 l_min_uom CSM_UNIT_OF_MEASURE_TL_ACC.UOM_CODE%TYPE;
317 l_planned_effort NUMBER;
318 l_user_id NUMBER;
319
320 BEGIN
321 l_profile_value := fnd_profile.value('JTM_APPL_CONFLICT_RULE');
322 l_user_id := asg_base.get_user_id(p_record.clid$$cs);
323
324 IF l_profile_value = 'SERVER_WINS' AND
325 ASG_DEFER.IS_DEFERRED(p_record.clid$$cs, p_record.tranid$$,g_pub_name, p_record.seqno$$) <> FND_API.G_TRUE
326 THEN
327 OPEN c_last_update_date(b_task_id => p_record.task_id);
328 FETCH c_last_update_date INTO r_last_update_date;
329
330 IF c_last_update_date%FOUND THEN
331 IF (r_last_update_date.last_update_date <> p_record.server_last_update_date AND r_last_update_date.last_updated_by <> asg_base.get_user_id(p_record.clid$$cs)) THEN
332 CLOSE c_last_update_date;
333 CSM_UTIL_PKG.log( 'Record has stale data. Leaving ' || g_object_name || '.APPLY_INSERT:'
334 || ' for PK ' || p_record.task_id,
335 g_object_name || '.APPLY_INSERT',
336 FND_LOG.LEVEL_PROCEDURE );
337 fnd_message.set_name
338 ( 'JTM'
339 , 'JTM_STALE_DATA'
340 );
341 fnd_msg_pub.add;
342 x_return_status := FND_API.G_RET_STS_ERROR;
343 RETURN;
344 END IF;
345 ELSE
346 CSM_UTIL_PKG.log( 'No record found in Apps Database in ' || g_object_name || '.APPLY_INSERT:',
347 g_object_name || '.APPLY_INSERT',
348 FND_LOG.LEVEL_PROCEDURE );
349 END IF;
350 CLOSE c_last_update_date;
351 END IF;
352
353 -- get object version from task record so client updates succeed even when record was updated
354 -- on server side (CLIENT_WINS)
355 OPEN c_task( p_record.task_id );
356 FETCH c_task INTO r_task;
357 CLOSE c_task;
358
359 --Get planned effort
360 IF p_record.planned_effort_uom IS NULL THEN
361 l_plan_eff_uom := CSM_PROFILE_PKG.VALUE_SPECIFIC('CSF_DEFAULT_EFFORT_UOM',l_user_id,NULL,NULL);
362 ELSE
363 l_plan_eff_uom := p_record.planned_effort_uom;
364 END IF;
365
366 l_uom_class := CSM_PROFILE_PKG.VALUE_SPECIFIC('JTF_TIME_UOM_CLASS',l_user_id,NULL,NULL);
367 l_min_uom := CSM_PROFILE_PKG.VALUE_SPECIFIC('CSF_UOM_MINUTES',l_user_id,NULL,NULL);
368 --Get planned effort for the required UOM
369 l_planned_effort := csm_util_pkg.Get_Datediff_For_Req_UOM(
370 p_record.scheduled_start_date,
371 p_record.scheduled_end_date,
372 l_uom_class,
373 l_plan_eff_uom,
374 l_min_uom
375 );
376
377 -- Update the task.
378 JTF_TASKS_PUB.Update_Task (
379 p_api_version => 1.0,
380 p_init_msg_list => FND_API.G_TRUE,
381 p_commit => FND_API.G_FALSE,
382 p_task_id => p_record.TASK_ID,
383 p_task_name => p_record.TASK_NAME,
384 p_description => p_record.description,
385 p_object_version_number => r_task.object_version_number,
386 p_planned_start_date => p_record.planned_start_date,
387 p_planned_end_date => p_record.planned_end_date,
388 p_scheduled_start_date => p_record.scheduled_start_date,
389 p_scheduled_end_date => p_record.scheduled_end_date,
390 -- bug 4248868
391 -- p_actual_start_date => p_record.actual_start_date,
392 -- p_actual_end_date => p_record.actual_end_date,
393 p_attribute1 => p_record.attribute1,
394 p_attribute2 => p_record.attribute2,
395 p_attribute3 => p_record.attribute3,
396 p_attribute4 => p_record.attribute4,
397 p_attribute5 => p_record.attribute5,
398 p_attribute6 => p_record.attribute6,
399 p_attribute7 => p_record.attribute7,
400 p_attribute8 => p_record.attribute8,
401 p_attribute9 => p_record.attribute9,
402 p_attribute10 => p_record.attribute10,
403 p_attribute11 => p_record.attribute11,
404 p_attribute12 => p_record.attribute12,
405 p_attribute13 => p_record.attribute13,
406 p_attribute14 => p_record.attribute14,
407 p_attribute15 => p_record.attribute15,
408 p_attribute_category => p_record.attribute_category,
409 x_return_status => x_return_status,
410 x_msg_count => l_msg_count,
411 x_msg_data => l_msg_data,
412 p_planned_effort => l_planned_effort,
413 p_planned_effort_uom => l_plan_eff_uom,
414 p_cust_account_id => p_record.cust_account_id,
415 p_task_priority_id => p_record.task_priority_id
416 );
417
418 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
419 THEN
420 x_return_status := FND_API.G_RET_STS_ERROR;
421
422 CSM_UTIL_PKG.log( 'Error in ' || g_object_name || '.APPLY_UPDATE:'
423 || ' ROOT ERROR: jtf_tasks_pub.UPDATE_TASK'
424 || ' for PK ' || p_record.TASK_ID,
425 g_object_name || '.APPLY_UPDATE',
426 FND_LOG.LEVEL_ERROR );
427 RETURN ;
428 END IF;
429
430 -- success
431 x_return_status := FND_API.G_RET_STS_SUCCESS;
432
433 EXCEPTION
434 WHEN others THEN
435 CSM_UTIL_PKG.log( 'Exception in ' || g_object_name || '.APPLY_UPDATE:'
436 || ' for PK ' || p_record.task_id,
437 g_object_name || '.APPLY_UPDATE',
438 FND_LOG.LEVEL_EXCEPTION );
439
440 x_return_status := FND_API.G_RET_STS_ERROR;
441
442 END APPLY_UPDATE;
443
444
445 /***
446 This procedure is called by APPLY_CLIENT_CHANGES when a record
447 is to be processed.
448 ***/
449 PROCEDURE APPLY_RECORD
450 (
451 p_record IN c_tasks_inq%ROWTYPE,
452 p_error_msg OUT NOCOPY VARCHAR2,
453 x_return_status IN OUT NOCOPY VARCHAR2,
454 x_reject_row OUT NOCOPY BOOLEAN --Bug 5288413
455 ) IS
456 BEGIN
457
458 /*** initialize return status and message list ***/
459 x_return_status := FND_API.G_RET_STS_SUCCESS;
460 FND_MSG_PUB.INITIALIZE;
461
462
463 IF p_record.dmltype$$='I' THEN
464 -- Process insert
465 APPLY_INSERT
466 (
467 p_record,
468 p_error_msg,
469 x_return_status,
470 x_reject_row --Bug 5288413
471 );
472 ELSIF p_record.dmltype$$='U' THEN -- YLIAO: for 11583, we do support UPDATE
473 -- Process update
474 APPLY_UPDATE
475 (
476 p_record,
477 p_error_msg,
478 x_return_status
479 );
480 ELSE
481 -- Process delete and insert;
482 -- Not supported for this entity
483 CSM_UTIL_PKG.LOG
484 ( 'Delete and Update is not supported for this entity'
485 || ' for PK ' || p_record.task_id ,
486 g_object_name || '.APPLY_RECORD',
487 FND_LOG.LEVEL_ERROR);
488
489 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
490 (
491 p_message => 'CSM_DML_OPERATION'
492 , p_token_name1 => 'DML'
493 , p_token_value1 => p_record.dmltype$$
494 );
495
496 x_return_status := FND_API.G_RET_STS_ERROR;
497 END IF;
498
499 EXCEPTION
500 WHEN OTHERS THEN
501 /*** defer record when any process exception occurs ***/
502 CSM_UTIL_PKG.LOG
503 ( 'Exception occurred in ' || g_object_name || '.APPLY_RECORD:' || ' ' || SQLERRM
504 || ' for PK ' || p_record.task_id ,
505 g_object_name || '.APPLY_RECORD',
506 FND_LOG.LEVEL_EXCEPTION);
507 -- temp -- find more detail --remove comment
508 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_RECORD', SQLERRM);
509 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
510 (
511 p_api_error => TRUE
512 );
513
514 x_return_status := FND_API.G_RET_STS_ERROR;
515
516 END APPLY_RECORD;
517
518
519 /***
520 APPLY_CLIENT_CHANGE procedure is called by CSM_SERVICEP_WRAPPER_PKG, for upward sync of
521 publication item CSM_TASKS
522 ***/
523 PROCEDURE APPLY_CLIENT_CHANGES
524 (
525 p_user_name IN VARCHAR2,
526 p_tranid IN NUMBER,
527 p_debug_level IN NUMBER,
528 x_return_status IN OUT NOCOPY VARCHAR2
529 )
530 IS
531 l_process_status VARCHAR2(1);
532 l_return_status VARCHAR2(1);
533 l_error_msg VARCHAR2(4000);
534 l_reject_row boolean := FALSE;
535 BEGIN
536 csm_util_pkg.log
537 ( g_object_name || '.APPLY_CLIENT_CHANGES entered',
538 g_object_name || '.APPLY_CLIENT_CHANGES',
539 FND_LOG.LEVEL_PROCEDURE);
540 g_debug_level := p_debug_level;
541 x_return_status := FND_API.G_RET_STS_SUCCESS;
542
543 /*** loop through tasks records in inqueue ***/
544 FOR r_tasks IN c_tasks_inq( p_user_name, p_tranid) LOOP
545
546 SAVEPOINT save_rec;
547
548 /*** apply record ***/
549 APPLY_RECORD
550 (
551 r_tasks
552 , l_error_msg
553 , l_process_status
554 , l_reject_row --Bug 5288413
555 );
556
557 /*** was record processed successfully? ***/
558 IF l_process_status = FND_API.G_RET_STS_SUCCESS THEN
559 /*** If Yes -> delete record from inqueue ***/
560 --Bug 5288413
561 IF l_reject_row THEN
562 CSM_UTIL_PKG.REJECT_RECORD
563 (
564 p_user_name,
565 p_tranid,
566 r_tasks.seqno$$,
567 r_tasks.task_id,
568 g_object_name,
569 g_pub_name,
570 l_error_msg,
571 l_return_status
572 );
573
574 --Bug 8931803 - Asg REJECT_ROW doesn't call delete_row on reapply as the record was deferred.
575 IF (l_return_status = FND_API.G_RET_STS_SUCCESS AND
576 ASG_DEFER.is_deferred(p_user_name,p_tranid,g_pub_name,r_tasks.seqno$$)=FND_API.G_TRUE) THEN
577 CSM_UTIL_PKG.DELETE_RECORD
578 (
579 p_user_name,
580 p_tranid,
581 r_tasks.seqno$$,
582 r_tasks.task_id,
583 g_object_name,
584 g_pub_name,
585 l_error_msg,
586 l_return_status
587 );
588 END IF;
589 ELSE
590 CSM_UTIL_PKG.DELETE_RECORD
591 (
592 p_user_name,
593 p_tranid,
594 r_tasks.seqno$$,
595 r_tasks.task_id,
596 g_object_name,
597 g_pub_name,
598 l_error_msg,
599 l_return_status --Introduced new variable l_return_status since Defer
600 ); --process doesn't depend on this delete_record API
601 END IF;
602 /*** was delete/reject successful? ***/
603 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
604 /*** If No -> rollback ***/
605 CSM_UTIL_PKG.LOG
606 ( 'Deleting from inqueue failed, rolling back to savepoint'
607 || ' for PK ' || r_tasks.task_id ,
608 g_object_name || '.APPLY_CLIENT_CHANGES',
609 FND_LOG.LEVEL_ERROR); -- put PK column here
610 ROLLBACK TO save_rec;
611 x_return_status := FND_API.G_RET_STS_ERROR;
612 END IF;
613 END IF;
614
615 IF l_process_Status <> FND_API.G_RET_STS_SUCCESS THEN
616 /*** Record was not processed successfully or delete failed
617 -> defer and reject record ***/
618 CSM_UTIL_PKG.LOG
619 ( 'Record not processed successfully, deferring and rejecting record'
620 || ' for PK ' || r_tasks.task_id ,
621 g_object_name || '.APPLY_CLIENT_CHANGES',
622 FND_LOG.LEVEL_ERROR); -- put PK column here
623
624 CSM_UTIL_PKG.DEFER_RECORD
625 ( p_user_name
626 , p_tranid
627 , r_tasks.seqno$$
628 , r_tasks.task_id
629 , g_object_name
630 , g_pub_name
631 , l_error_msg
632 , l_process_status
633 , r_tasks.dmltype$$
634 );
635
636 /*** Was defer successful? ***/
637 IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
638 /*** no -> rollback ***/
639 CSM_UTIL_PKG.LOG
640 ( 'Defer record failed, rolling back to savepoint'
641 || ' for PK ' || r_tasks.task_id ,
642 g_object_name || '.APPLY_CLIENT_CHANGES',
643 FND_LOG.LEVEL_ERROR); -- put PK column here
644 ROLLBACK TO save_rec;
645 x_return_status := FND_API.G_RET_STS_ERROR;
646 END IF;
647 END IF;
648
649 END LOOP;
650
651 EXCEPTION WHEN OTHERS THEN
652 /*** catch and log exceptions ***/
653 CSM_UTIL_PKG.LOG
654 ( 'Exception occurred in ' || g_object_name || '.APPLY_CLIENT_CHANGES:' || ' ' || SQLERRM,
655 g_object_name || '.APPLY_CLIENT_CHANGES',
656 FND_LOG.LEVEL_EXCEPTION);
657 x_return_status := FND_API.G_RET_STS_ERROR;
658
659 END APPLY_CLIENT_CHANGES;
660
661 END CSM_TASKS_PKG; -- Package spec