[Home] [Help]
PACKAGE BODY: APPS.CSM_TASKS_PKG
Source
1 PACKAGE BODY CSM_TASKS_PKG AS
2 /* $Header: csmutskb.pls 120.12.12010000.2 2008/11/26 13:21:45 trajasek 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 :=FALSE;
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 END IF;
138
139 --Get Mobile responsibility
140 OPEN c_csm_resp(p_record.created_by);
141 FETCH c_csm_resp INTO l_responsibility_id;
142 CLOSE c_csm_resp;
143 -- get csm application id
144 OPEN c_csm_appl;
145 FETCH c_csm_appl INTO l_csm_appl_id;
146 CLOSE c_csm_appl;
147
148 --Get Task Assignment Manger profile
149 l_territory_assign := fnd_profile.value_specific('CSM_SELECT_TASK_THRU_TERRITORY_ASSIGN'
150 , p_record.created_by
151 , l_responsibility_id
152 , l_csm_appl_id);
153
154 IF l_territory_assign = 'Y' AND p_record.source_object_type_code ='SR' THEN
155
156 l_task_attribute_rec.task_type_id := l_task_type;
157 l_task_attribute_rec.task_status_id := l_task_status;
158 l_task_attribute_rec.task_priority_id := l_task_priority;
159
160 CS_SR_TASK_AUTOASSIGN_PKG.Assign_Task_Resource
161 (p_api_version => 1.0,
162 p_init_msg_list => fnd_api.g_true,
163 p_commit => fnd_api.g_false,
164 p_incident_id => p_record.source_object_id,
165 p_service_request_rec => l_service_request_rec,
166 p_task_attribute_rec => l_task_attribute_rec,
167 x_owner_group_id => l_owner_group_id,
168 x_group_type => l_group_type,
169 x_owner_type => l_owner_type,
170 x_owner_id => l_owner_id,
171 x_territory_id => l_territory_id,
172 x_return_status => x_return_status,
173 x_msg_count => l_msg_count,
174 x_msg_data => l_msg_data
175 );
176
177 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
178 THEN
179 x_return_status := FND_API.G_RET_STS_ERROR;
180
181 CSM_UTIL_PKG.log( 'Error in ' || g_object_name || '.APPLY_INSERT:'
182 || ' ROOT ERROR: CS_SR_TASK_AUTOASSIGN_PKG.Assign_Task_Resource'
183 || ' for PK ' || p_record.TASK_ID || l_msg_data,
184 g_object_name || '.APPLY_INSERT',
185 FND_LOG.LEVEL_ERROR );
186 END IF;
187 ELSE
188 l_owner_type := csm_profile_pkg.value_specific('INC_DEFAULT_INCIDENT_TASK_OWNER_TYPE', p_record.created_by);
189 l_owner_id := csm_profile_pkg.value_specific('INC_DEFAULT_INCIDENT_TASK_OWNER', p_record.created_by);
190
191 END IF;
192
193 IF l_is_private=1 OR l_owner_type IS NULL OR l_owner_id IS NULL THEN
194 l_owner_type := 'RS_EMPLOYEE';
195 l_owner_id :=l_sync_resource_id;
196 ELSE
197 IF (l_owner_type <> 'RS_EMPLOYEE' OR l_owner_id <> l_sync_resource_id) THEN
198 x_reject_row:=TRUE;
199 END IF;
200 END IF;
201
202 -- Create new Task
203 jtf_tasks_pub.Create_Task
204 ( p_api_version => 1.0,
205 p_init_msg_list => fnd_api.g_true,
206 p_commit => fnd_api.g_false,
207 p_task_id => p_record.task_id,
208 p_task_name => p_record.task_name,
209 p_task_type_id => l_task_type,
210 p_description => p_record.description,
211 p_task_status_id => l_task_status,
212 p_task_priority_id => l_task_priority,
213 p_owner_type_code => l_owner_type,
214 p_owner_id => l_owner_id,
215 p_owner_territory_id => l_territory_id,
216 p_planned_start_date => p_record.planned_start_date,
217 p_planned_end_date => p_record.planned_end_date,
218 p_scheduled_start_date => p_record.scheduled_start_date,
219 p_scheduled_end_date => p_record.scheduled_end_date,
220 -- bug 4248868
221 -- p_actual_start_date => p_record.actual_start_date,
222 -- p_actual_end_date => p_record.actual_end_date,
223 p_source_object_type_code => p_record.source_object_type_code,
224 p_source_object_id => p_record.source_object_id,
225 p_source_object_name => l_task_source_object_name,
226 p_planned_effort => p_record.planned_effort,
227 p_planned_effort_uom => p_record.planned_effort_uom,
228 p_escalation_level => p_record.escalation_level,
229 p_address_id => l_address_id,
230 x_return_status => x_return_status,
231 x_msg_count => l_msg_count,
232 x_msg_data => l_msg_data,
233 x_task_id => l_task_id,
234 p_attribute1 => p_record.attribute1,
235 p_attribute2 => p_record.attribute2,
236 p_attribute3 => p_record.attribute3,
237 p_attribute4 => p_record.attribute4,
238 p_attribute5 => p_record.attribute5,
239 p_attribute6 => p_record.attribute6,
240 p_attribute7 => p_record.attribute7,
241 p_attribute8 => p_record.attribute8,
242 p_attribute9 => p_record.attribute9,
243 p_attribute10 => p_record.attribute10,
244 p_attribute11 => p_record.attribute11,
245 p_attribute12 => p_record.attribute12,
246 p_attribute13 => p_record.attribute13,
247 p_attribute14 => p_record.attribute14,
248 p_attribute15 => p_record.attribute15,
249 p_attribute_category => p_record.attribute_category,
250 p_customer_id => NVL(p_record.customer_id, l_customer_id),
251 p_location_id => l_location_id,
252 p_cust_account_id => p_record.cust_account_id
253 );
254
255 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
256 THEN
257 x_return_status := FND_API.G_RET_STS_ERROR;
258
259 CSM_UTIL_PKG.log( 'Error in ' || g_object_name || '.APPLY_INSERT:'
260 || ' ROOT ERROR: jtf_tasks_pub.Create_Task'
261 || ' for PK ' || p_record.TASK_ID,
262 g_object_name || '.APPLY_INSERT',
263 FND_LOG.LEVEL_ERROR );
264 RETURN ;
265 END IF;
266
267 -- success
268 x_return_status := FND_API.G_RET_STS_SUCCESS;
269
270 EXCEPTION
271 WHEN others THEN
272 CSM_UTIL_PKG.log( 'Exception in ' || g_object_name || '.APPLY_INSERT:'
273 || ' for PK ' || p_record.task_id,
274 g_object_name || '.APPLY_INSERT',
275 FND_LOG.LEVEL_EXCEPTION );
276
277 x_return_status := FND_API.G_RET_STS_ERROR;
278
279 END APPLY_INSERT;
280
281
282 /***
283 This procedure is called by APPLY_CRECORD when
284 an updated record is to be processed.
285 For CSM 11583, we support updates on the DFF columns
286 ***/
287 PROCEDURE APPLY_UPDATE
288 (
289 p_record IN c_tasks_inq%ROWTYPE,
290 p_error_msg OUT NOCOPY VARCHAR2,
291 x_return_status IN OUT NOCOPY VARCHAR2
292 )
293 IS
294 CURSOR c_task ( b_task_id NUMBER ) IS
295 SELECT object_version_number
296 FROM jtf_tasks_b
297 WHERE task_id = b_task_id;
298
299 CURSOR c_last_update_date ( b_task_id NUMBER)
300 IS
301 SELECT LAST_UPDATE_DATE,
302 last_updated_by
303 FROM jtf_tasks_b
304 WHERE task_id = b_task_id;
305
306 --variable declarations
307 r_task c_task%ROWTYPE;
308 r_last_update_date c_last_update_date%ROWTYPE;
309 l_profile_value VARCHAR2(240);
310 l_msg_count NUMBER;
311 l_msg_data VARCHAR2(240);
312 l_uom_class MTL_UNITS_OF_MEASURE.UOM_CLASS%TYPE;
313 l_plan_eff_uom CSM_UNIT_OF_MEASURE_TL_ACC.UOM_CODE%TYPE;
314 l_min_uom CSM_UNIT_OF_MEASURE_TL_ACC.UOM_CODE%TYPE;
315 l_planned_effort NUMBER;
316 l_user_id NUMBER;
317
318 BEGIN
319 l_profile_value := fnd_profile.value('JTM_APPL_CONFLICT_RULE');
320 l_user_id := asg_base.get_user_id(p_record.clid$$cs);
321
322 IF l_profile_value = 'SERVER_WINS' AND
323 ASG_DEFER.IS_DEFERRED(p_record.clid$$cs, p_record.tranid$$,g_pub_name, p_record.seqno$$) <> FND_API.G_TRUE
324 THEN
325 OPEN c_last_update_date(b_task_id => p_record.task_id);
326 FETCH c_last_update_date INTO r_last_update_date;
327
328 IF c_last_update_date%FOUND THEN
329 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
330 CLOSE c_last_update_date;
331 CSM_UTIL_PKG.log( 'Record has stale data. Leaving ' || g_object_name || '.APPLY_INSERT:'
332 || ' for PK ' || p_record.task_id,
333 g_object_name || '.APPLY_INSERT',
334 FND_LOG.LEVEL_PROCEDURE );
335 fnd_message.set_name
336 ( 'JTM'
337 , 'JTM_STALE_DATA'
338 );
339 fnd_msg_pub.add;
340 x_return_status := FND_API.G_RET_STS_ERROR;
341 RETURN;
342 END IF;
343 ELSE
344 CSM_UTIL_PKG.log( 'No record found in Apps Database in ' || g_object_name || '.APPLY_INSERT:',
345 g_object_name || '.APPLY_INSERT',
346 FND_LOG.LEVEL_PROCEDURE );
347 END IF;
348 CLOSE c_last_update_date;
349 END IF;
350
351 -- get object version from task record so client updates succeed even when record was updated
352 -- on server side (CLIENT_WINS)
353 OPEN c_task( p_record.task_id );
354 FETCH c_task INTO r_task;
355 CLOSE c_task;
356
357 --Get planned effort
358 IF p_record.planned_effort_uom IS NULL THEN
359 l_plan_eff_uom := CSM_PROFILE_PKG.VALUE_SPECIFIC('CSF_DEFAULT_EFFORT_UOM',l_user_id,NULL,NULL);
360 ELSE
361 l_plan_eff_uom := p_record.planned_effort_uom;
362 END IF;
363
364 l_uom_class := CSM_PROFILE_PKG.VALUE_SPECIFIC('JTF_TIME_UOM_CLASS',l_user_id,NULL,NULL);
365 l_min_uom := CSM_PROFILE_PKG.VALUE_SPECIFIC('CSF_UOM_MINUTES',l_user_id,NULL,NULL);
366 --Get planned effort for the required UOM
367 l_planned_effort := csm_util_pkg.Get_Datediff_For_Req_UOM(
368 p_record.scheduled_start_date,
369 p_record.scheduled_end_date,
370 l_uom_class,
371 l_plan_eff_uom,
372 l_min_uom
373 );
374
375 -- Update the task.
376 JTF_TASKS_PUB.Update_Task (
377 p_api_version => 1.0,
378 p_init_msg_list => FND_API.G_TRUE,
379 p_commit => FND_API.G_FALSE,
380 p_task_id => p_record.TASK_ID,
381 p_description => p_record.description,
382 p_object_version_number => r_task.object_version_number,
383 p_planned_start_date => p_record.planned_start_date,
384 p_planned_end_date => p_record.planned_end_date,
385 p_scheduled_start_date => p_record.scheduled_start_date,
386 p_scheduled_end_date => p_record.scheduled_end_date,
387 -- bug 4248868
388 -- p_actual_start_date => p_record.actual_start_date,
389 -- p_actual_end_date => p_record.actual_end_date,
390 p_attribute1 => p_record.attribute1,
391 p_attribute2 => p_record.attribute2,
392 p_attribute3 => p_record.attribute3,
393 p_attribute4 => p_record.attribute4,
394 p_attribute5 => p_record.attribute5,
395 p_attribute6 => p_record.attribute6,
396 p_attribute7 => p_record.attribute7,
397 p_attribute8 => p_record.attribute8,
398 p_attribute9 => p_record.attribute9,
399 p_attribute10 => p_record.attribute10,
400 p_attribute11 => p_record.attribute11,
401 p_attribute12 => p_record.attribute12,
402 p_attribute13 => p_record.attribute13,
403 p_attribute14 => p_record.attribute14,
404 p_attribute15 => p_record.attribute15,
405 p_attribute_category => p_record.attribute_category,
406 x_return_status => x_return_status,
407 x_msg_count => l_msg_count,
408 x_msg_data => l_msg_data,
409 p_planned_effort => l_planned_effort,
410 p_planned_effort_uom => l_plan_eff_uom,
411 p_cust_account_id => p_record.cust_account_id,
412 p_task_priority_id => p_record.task_priority_id
413 );
414
415 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
416 THEN
417 x_return_status := FND_API.G_RET_STS_ERROR;
418
419 CSM_UTIL_PKG.log( 'Error in ' || g_object_name || '.APPLY_UPDATE:'
420 || ' ROOT ERROR: jtf_tasks_pub.UPDATE_TASK'
421 || ' for PK ' || p_record.TASK_ID,
422 g_object_name || '.APPLY_UPDATE',
423 FND_LOG.LEVEL_ERROR );
424 RETURN ;
425 END IF;
426
427 -- success
428 x_return_status := FND_API.G_RET_STS_SUCCESS;
429
430 EXCEPTION
431 WHEN others THEN
432 CSM_UTIL_PKG.log( 'Exception in ' || g_object_name || '.APPLY_UPDATE:'
433 || ' for PK ' || p_record.task_id,
434 g_object_name || '.APPLY_UPDATE',
435 FND_LOG.LEVEL_EXCEPTION );
436
437 x_return_status := FND_API.G_RET_STS_ERROR;
438
439 END APPLY_UPDATE;
440
441
442 /***
443 This procedure is called by APPLY_CLIENT_CHANGES when a record
444 is to be processed.
445 ***/
446 PROCEDURE APPLY_RECORD
447 (
448 p_record IN c_tasks_inq%ROWTYPE,
449 p_error_msg OUT NOCOPY VARCHAR2,
450 x_return_status IN OUT NOCOPY VARCHAR2,
451 x_reject_row OUT NOCOPY BOOLEAN --Bug 5288413
452 ) IS
453 BEGIN
454
455 /*** initialize return status and message list ***/
456 x_return_status := FND_API.G_RET_STS_SUCCESS;
457 FND_MSG_PUB.INITIALIZE;
458
459
460 IF p_record.dmltype$$='I' THEN
461 -- Process insert
462 APPLY_INSERT
463 (
464 p_record,
465 p_error_msg,
466 x_return_status,
467 x_reject_row --Bug 5288413
468 );
469 ELSIF p_record.dmltype$$='U' THEN -- YLIAO: for 11583, we do support UPDATE
470 -- Process update
471 APPLY_UPDATE
472 (
473 p_record,
474 p_error_msg,
475 x_return_status
476 );
477 ELSE
478 -- Process delete and insert;
479 -- Not supported for this entity
480 CSM_UTIL_PKG.LOG
481 ( 'Delete and Update is not supported for this entity'
482 || ' for PK ' || p_record.task_id ,
483 g_object_name || '.APPLY_RECORD',
484 FND_LOG.LEVEL_ERROR);
485
486 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
487 (
488 p_message => 'CSM_DML_OPERATION'
489 , p_token_name1 => 'DML'
490 , p_token_value1 => p_record.dmltype$$
491 );
492
493 x_return_status := FND_API.G_RET_STS_ERROR;
494 END IF;
495
496 EXCEPTION
497 WHEN OTHERS THEN
498 /*** defer record when any process exception occurs ***/
499 CSM_UTIL_PKG.LOG
500 ( 'Exception occurred in ' || g_object_name || '.APPLY_RECORD:' || ' ' || SQLERRM
501 || ' for PK ' || p_record.task_id ,
502 g_object_name || '.APPLY_RECORD',
503 FND_LOG.LEVEL_EXCEPTION);
504 -- temp -- find more detail --remove comment
505 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_RECORD', SQLERRM);
506 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
507 (
508 p_api_error => TRUE
509 );
510
511 x_return_status := FND_API.G_RET_STS_ERROR;
512
513 END APPLY_RECORD;
514
515
516 /***
517 APPLY_CLIENT_CHANGE procedure is called by CSM_SERVICEP_WRAPPER_PKG, for upward sync of
518 publication item CSM_TASKS
519 ***/
520 PROCEDURE APPLY_CLIENT_CHANGES
521 (
522 p_user_name IN VARCHAR2,
523 p_tranid IN NUMBER,
524 p_debug_level IN NUMBER,
525 x_return_status IN OUT NOCOPY VARCHAR2
526 )
527 IS
528 l_process_status VARCHAR2(1);
529 l_return_status VARCHAR2(1);
530 l_error_msg VARCHAR2(4000);
531 l_reject_row boolean;
532 BEGIN
533 csm_util_pkg.log
534 ( g_object_name || '.APPLY_CLIENT_CHANGES entered',
535 g_object_name || '.APPLY_CLIENT_CHANGES',
536 FND_LOG.LEVEL_PROCEDURE);
537 g_debug_level := p_debug_level;
538 x_return_status := FND_API.G_RET_STS_SUCCESS;
539
540 /*** loop through tasks records in inqueue ***/
541 FOR r_tasks IN c_tasks_inq( p_user_name, p_tranid) LOOP
542
543 SAVEPOINT save_rec;
544
545 /*** apply record ***/
546 APPLY_RECORD
547 (
548 r_tasks
549 , l_error_msg
550 , l_process_status
551 , l_reject_row --Bug 5288413
552 );
553
554 /*** was record processed successfully? ***/
555 IF l_process_status = FND_API.G_RET_STS_SUCCESS THEN
556 /*** If Yes -> delete record from inqueue ***/
557 --Bug 5288413
558 IF l_reject_row THEN
559 CSM_UTIL_PKG.REJECT_RECORD
560 (
561 p_user_name,
562 p_tranid,
563 r_tasks.seqno$$,
564 r_tasks.task_id,
565 g_object_name,
566 g_pub_name,
567 l_error_msg,
568 l_return_status
569 );
570 ELSE
571 CSM_UTIL_PKG.DELETE_RECORD
572 (
573 p_user_name,
574 p_tranid,
575 r_tasks.seqno$$,
576 r_tasks.task_id,
577 g_object_name,
578 g_pub_name,
579 l_error_msg,
580 l_return_status --Introduced new variable l_return_status since Defer
581 ); --process doesn't depend on this delete_record API
582 END IF;
583 /*** was delete/reject successful? ***/
584 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
585 /*** If No -> rollback ***/
586 CSM_UTIL_PKG.LOG
587 ( 'Deleting from inqueue failed, rolling back to savepoint'
588 || ' for PK ' || r_tasks.task_id ,
589 g_object_name || '.APPLY_CLIENT_CHANGES',
590 FND_LOG.LEVEL_ERROR); -- put PK column here
591 ROLLBACK TO save_rec;
592 x_return_status := FND_API.G_RET_STS_ERROR;
593 END IF;
594 END IF;
595
596 IF l_process_Status <> FND_API.G_RET_STS_SUCCESS THEN
597 /*** Record was not processed successfully or delete failed
598 -> defer and reject record ***/
599 CSM_UTIL_PKG.LOG
600 ( 'Record not processed successfully, deferring and rejecting record'
601 || ' for PK ' || r_tasks.task_id ,
602 g_object_name || '.APPLY_CLIENT_CHANGES',
603 FND_LOG.LEVEL_ERROR); -- put PK column here
604
605 CSM_UTIL_PKG.DEFER_RECORD
606 ( p_user_name
607 , p_tranid
608 , r_tasks.seqno$$
609 , r_tasks.task_id
610 , g_object_name
611 , g_pub_name
612 , l_error_msg
613 , l_process_status
614 , r_tasks.dmltype$$
615 );
616
617 /*** Was defer successful? ***/
618 IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
619 /*** no -> rollback ***/
620 CSM_UTIL_PKG.LOG
621 ( 'Defer record failed, rolling back to savepoint'
622 || ' for PK ' || r_tasks.task_id ,
623 g_object_name || '.APPLY_CLIENT_CHANGES',
624 FND_LOG.LEVEL_ERROR); -- put PK column here
625 ROLLBACK TO save_rec;
626 x_return_status := FND_API.G_RET_STS_ERROR;
627 END IF;
628 END IF;
629
630 END LOOP;
631
632 EXCEPTION WHEN OTHERS THEN
633 /*** catch and log exceptions ***/
634 CSM_UTIL_PKG.LOG
635 ( 'Exception occurred in ' || g_object_name || '.APPLY_CLIENT_CHANGES:' || ' ' || SQLERRM,
636 g_object_name || '.APPLY_CLIENT_CHANGES',
637 FND_LOG.LEVEL_EXCEPTION);
638 x_return_status := FND_API.G_RET_STS_ERROR;
639
640 END APPLY_CLIENT_CHANGES;
641
642 END CSM_TASKS_PKG; -- Package spec