[Home] [Help]
PACKAGE BODY: APPS.CSM_SERVICE_REQUESTS_PKG
Source
1 PACKAGE BODY CSM_SERVICE_REQUESTS_PKG AS
2 /* $Header: csmusrb.pls 120.16.12010000.3 2008/11/26 13:20:58 trajasek ship $ */
3
4 error EXCEPTION;
5
6
7 /*** Globals ***/
8 g_object_name CONSTANT VARCHAR2(30) := 'CSM_SERVICE_REQUESTS_PKG';
9 g_pub_name CONSTANT VARCHAR2(30) := 'CSM_INCIDENTS_ALL';
10 g_debug_level NUMBER; -- debug level
11
12 /* Select all inq records */
13 CURSOR c_incident( b_user_name VARCHAR2, b_tranid NUMBER) is
14 SELECT *
15 FROM CSM_INCIDENTS_ALL_INQ
16 WHERE tranid$$ = b_tranid
17 AND clid$$cs = b_user_name;
18
19 /* Select all contact records for incident from inq table */
20 CURSOR c_contact( b_incident_id NUMBER, b_tranid NUMBER, b_user_name VARCHAR2 ) IS
21 SELECT *
22 FROM CSF_M_SR_CONTACTS_INQ
23 WHERE INCIDENT_ID = b_incident_id
24 AND TRANID$$ = b_tranid
25 AND clid$$cs = b_user_name;
26 --Since from r12 the app_id and responsiblity id is available in asg_user table the cursor is mordied to take the values
27 --from asg_user table
28 CURSOR c_csm_appl(l_userid NUMBER)
29 IS
30 SELECT APP_ID
31 FROM asg_user
32 WHERE user_id = l_userid;
33
34 CURSOR c_csm_resp(l_userid NUMBER)
35 is
36 SELECT RESPONSIBILITY_ID
37 FROM asg_user
38 WHERE user_id = l_userid;
39
40
41 CURSOR c_validate_item_org(p_inventory_item_id IN NUMBER, p_organization_id IN NUMBER)
42 IS
43 SELECT 1
44 FROM mtl_system_items_b
45 WHERE inventory_item_id = p_inventory_item_id
46 AND organization_id = p_organization_id;
47
48 /* Cursor to select party Type */
49 CURSOR c_party ( b_customer_id CS_INCIDENTS_ALL_B.CUSTOMER_ID%TYPE )
50 IS
51 SELECT PARTY_TYPE
52 FROM HZ_PARTIES
53 WHERE party_id = b_customer_id;
54
55 /***
56 This procedure is called by APPLY_CLIENT_CHANGES when an inserted record is to be processed.
57 ***/
58 PROCEDURE APPLY_INSERT
59 (
60 p_record IN c_incident%ROWTYPE,
61 p_error_msg out nocopy VARCHAR2,
62 x_return_status IN out nocopy VARCHAR2
63 ) IS
64
65 /* Bug 3917132
66 Cursor to get the Bill to Address */
67 CURSOR c_bill_to_site_id
68 ( b_customer_id CS_INCIDENTS_ALL_B.CUSTOMER_ID%TYPE
69 ) IS
70 SELECT use.party_site_use_id FROM
71 hz_party_sites site, hz_party_site_uses use
72 WHERE site.party_site_id = use.party_site_id
73 AND site.status= 'A'
74 AND use.site_use_type= 'BILL_TO'
75 AND use.primary_per_type = 'Y'
76 AND use.status = 'A'
77 AND site.party_id = b_customer_id
78 AND trunc(SYSDATE) BETWEEN TRUNC (NVL(use.begin_date, SYSDATE))
79 AND (NVL(use.end_date, SYSDATE));
80
81 /* Bug 3917132
82 Cursor to get the Ship to Address */
83 CURSOR c_ship_to_site_id
84 ( b_customer_id CS_INCIDENTS_ALL_B.CUSTOMER_ID%TYPE
85 ) IS
86 SELECT use.party_site_use_id FROM
87 hz_party_sites site, hz_party_site_uses use
88 WHERE site.party_site_id = use.party_site_id
89 AND site.status= 'A'
90 AND use.site_use_type= 'SHIP_TO'
91 AND use.primary_per_type = 'Y'
92 AND use.status = 'A'
93 AND site.party_id = b_customer_id
94 AND trunc(SYSDATE) BETWEEN TRUNC (NVL(use.begin_date, SYSDATE))
95 AND (NVL(use.end_date, SYSDATE));
96
97 /* Cursor to get the customer account id */
98 CURSOR c_customer_account( b_customer_id CS_INCIDENTS_ALL_B.CUSTOMER_ID%TYPE)
99 IS
100 SELECT custa.cust_account_id
101 FROM hz_cust_accounts custa
102 WHERE custa.status = 'A'
103 AND custa.party_id = b_customer_id;
104
105 --115.10
106 CURSOR l_install_site_csr (p_customer_product_id IN number)
107 IS
108 SELECT install_location_id
109 FROM csi_item_instances
110 WHERE instance_id = p_customer_product_id
111 AND install_location_type_code IN ('HZ_PARTY_SITES','HZ_LOCATIONS');
112
113
114 --Variable Declarations
115 l_install_location_id csi_item_instances.install_location_id%TYPE;
116 l_sr_rec CS_ServiceRequest_PUB.service_request_rec_type;
117 l_user_id NUMBER;
118 l_msg_count NUMBER;
119 l_msg_data VARCHAR2(240);
120 l_notes_tab CS_ServiceRequest_PUB.notes_table;
121 l_contacts_tab CS_ServiceRequest_PUB.contacts_table;
122 l_request_id NUMBER;
123 l_request_number VARCHAR2(64);
124 l_interaction_id NUMBER;
125 l_workflow_process_id NUMBER;
126 l_contact_rec CS_ServiceRequest_PUB.contacts_rec;
127 l_contact_index BINARY_INTEGER;
128 l_contact_id CS_HZ_SR_CONTACT_POINTS.PARTY_ID%TYPE;
129 x_individual_owner NUMBER;
130 x_individual_type VARCHAR2(240);
131 x_group_owner NUMBER;
132 l_profile_value VARCHAR2(240);
133 l_resp_id NUMBER;
134 l_csm_appl_id fnd_application.application_id%TYPE;
135 l_customer_account_id NUMBER;
136 l_bill_to_site_use_id NUMBER;
137 l_ship_to_site_use_id NUMBER;
138 l_dummy NUMBER;
139 l_org_id NUMBER;
140 l_created_by NUMBER;
141 l_party_type VARCHAR2(30);
142 l_responsibility_id NUMBER;
143 l_sr_out_rec CS_ServiceRequest_PUB.sr_create_out_rec_type;
144 l_auto_generate_task VARCHAR2(255);
145
146 BEGIN
147
148 CSM_UTIL_PKG.LOG('Entering CSM_SERVICE_REQUESTS_PKG.APPLY_INSERT for incident_id ' || p_record.incident_id ,
149 'CSM_SERVICE_REQUESTS_PKG.APPLY_INSERT',FND_LOG.LEVEL_PROCEDURE);
150
151 l_user_id := JTM_HOOK_UTIL_PKG.Get_User_Id( p_record.CLID$$CS );
152 l_created_by := p_record.CREATED_BY;
153 l_resp_id := TO_NUMBER(fnd_profile.value('CSM_SR_CREATE_RESP'));
154
155 --get responsiblity from asg_user
156 OPEN c_csm_resp(l_user_id);
157 FETCH c_csm_resp INTO l_responsibility_id;
158 CLOSE c_csm_resp;
159
160 -- get csm application id
161 OPEN c_csm_appl(l_user_id);
162 FETCH c_csm_appl INTO l_csm_appl_id;
163 CLOSE c_csm_appl;
164
165 IF l_resp_id IS NULL THEN
166 l_resp_id := l_responsibility_id;
167 END IF;
168
169 --Get the value for Auto Create Task
170 l_auto_generate_task := fnd_profile.value_specific('CS_SR_AUTO_TASK_CREATE'
171 , p_record.created_by
172 , l_responsibility_id
173 , l_csm_appl_id);
174
175 IF l_auto_generate_task IS NULL OR l_auto_generate_task ='NONE' THEN
176 l_auto_generate_task := 'N';
177 ELSE
178 l_auto_generate_task := 'Y';
179 END IF;
180
181 -- Initialization
182 CS_ServiceRequest_PUB.INITIALIZE_REC( p_sr_record => l_sr_rec );
183
184 --get caller type if not send by client
185 IF p_record.CALLER_TYPE IS NULL THEN
186 --get party type
187 OPEN c_party (p_record.CUSTOMER_ID);
188 FETCH c_party INTO l_party_type;
189 CLOSE c_party;
190 l_sr_rec.CALLER_TYPE := l_party_type;
191 ELSE
192 l_sr_rec.CALLER_TYPE := p_record.CALLER_TYPE;
193 END IF;
194
195 l_sr_rec.SUMMARY := p_record.SUMMARY;
196 l_sr_rec.request_date := p_record.incident_date ;
197 l_sr_rec.severity_id := p_record.incident_severity_id ;
198 l_sr_rec.status_id := p_record.incident_status_id ;
199 l_sr_rec.type_id := p_record.incident_type_id ;
200 l_sr_rec.urgency_id := p_record.incident_urgency_id ;
201 l_sr_rec.CUSTOMER_ID := p_record.CUSTOMER_ID;
202 l_sr_rec.sr_creation_channel := 'MOBILE';
203 l_sr_rec.owner_id := NULL;
204 l_sr_rec.owner_group_id := NULL;
205 l_sr_rec.CUSTOMER_PRODUCT_ID := p_record.CUSTOMER_PRODUCT_ID;
206 l_sr_rec.INVENTORY_ITEM_ID := p_record.INVENTORY_ITEM_ID;
207 l_sr_rec.current_serial_number := p_record.current_serial_number ;
208 l_sr_rec.inventory_org_id := NVL(p_record.inv_organization_id, TO_NUMBER(fnd_profile.value('CS_INV_VALIDATION_ORG'))) ;
209 l_sr_rec.PROBLEM_CODE := p_record.PROBLEM_CODE;
210 l_sr_rec.RESOLUTION_CODE := p_record.RESOLUTION_CODE;
211 l_sr_rec.INV_ITEM_REVISION := p_record.INV_ITEM_REVISION;
212 l_sr_rec.CUST_PO_NUMBER := p_record.CUSTOMER_PO_NUMBER;
213 l_sr_rec.RESOLUTION_SUMMARY := p_record.RESOLUTION_SUMMARY;
214 l_sr_rec.ACCOUNT_ID := p_record.ACCOUNT_ID;
215 l_sr_rec.OWNER_GROUP_ID := p_record.OWNER_GROUP_ID;
216 l_sr_rec.OWNER_ID := p_record.INCIDENT_OWNER_ID;
217 l_sr_rec.request_attribute_1 := p_record.INCIDENT_ATTRIBUTE_1;
218 l_sr_rec.request_attribute_2 := p_record.INCIDENT_ATTRIBUTE_2;
219 l_sr_rec.request_attribute_3 := p_record.INCIDENT_ATTRIBUTE_3;
220 l_sr_rec.request_attribute_4 := p_record.INCIDENT_ATTRIBUTE_4;
221 l_sr_rec.request_attribute_5 := p_record.INCIDENT_ATTRIBUTE_5;
222 l_sr_rec.request_attribute_6 := p_record.INCIDENT_ATTRIBUTE_6;
223 l_sr_rec.request_attribute_7 := p_record.INCIDENT_ATTRIBUTE_7;
224 l_sr_rec.request_attribute_8 := p_record.INCIDENT_ATTRIBUTE_8;
225 l_sr_rec.request_attribute_9 := p_record.INCIDENT_ATTRIBUTE_9;
226 l_sr_rec.request_attribute_10 := p_record.INCIDENT_ATTRIBUTE_10;
227 l_sr_rec.request_attribute_11 := p_record.INCIDENT_ATTRIBUTE_11;
228 l_sr_rec.request_attribute_12 := p_record.INCIDENT_ATTRIBUTE_12;
229 l_sr_rec.request_attribute_13 := p_record.INCIDENT_ATTRIBUTE_13;
230 l_sr_rec.request_attribute_14 := p_record.INCIDENT_ATTRIBUTE_14;
231 l_sr_rec.request_attribute_15 := p_record.INCIDENT_ATTRIBUTE_15;
232 l_sr_rec.request_context := p_record.INCIDENT_CONTEXT;
233 l_sr_rec.external_attribute_1 := p_record.EXTERNAL_ATTRIBUTE_1;
234 l_sr_rec.external_attribute_2 := p_record.EXTERNAL_ATTRIBUTE_2;
235 l_sr_rec.external_attribute_3 := p_record.EXTERNAL_ATTRIBUTE_3;
236 l_sr_rec.external_attribute_4 := p_record.EXTERNAL_ATTRIBUTE_4;
237 l_sr_rec.external_attribute_5 := p_record.EXTERNAL_ATTRIBUTE_5;
238 l_sr_rec.external_attribute_6 := p_record.EXTERNAL_ATTRIBUTE_6;
239 l_sr_rec.external_attribute_7 := p_record.EXTERNAL_ATTRIBUTE_7;
240 l_sr_rec.external_attribute_8 := p_record.EXTERNAL_ATTRIBUTE_8;
241 l_sr_rec.external_attribute_9 := p_record.EXTERNAL_ATTRIBUTE_9;
242 l_sr_rec.external_attribute_10 := p_record.EXTERNAL_ATTRIBUTE_10;
243 l_sr_rec.external_attribute_11 := p_record.EXTERNAL_ATTRIBUTE_11;
244 l_sr_rec.external_attribute_12 := p_record.EXTERNAL_ATTRIBUTE_12;
245 l_sr_rec.external_attribute_13 := p_record.EXTERNAL_ATTRIBUTE_13;
246 l_sr_rec.external_attribute_14 := p_record.EXTERNAL_ATTRIBUTE_14;
247 l_sr_rec.external_attribute_15 := p_record.EXTERNAL_ATTRIBUTE_15;
248 l_sr_rec.external_context := p_record.EXTERNAL_CONTEXT;
249 l_sr_rec.creation_program_code := 'CSM_UPSYNC_WRAPPER';
250 l_sr_rec.incident_location_id := p_record.incident_location_id;
251 l_sr_rec.incident_location_type := nvl(p_record.incident_location_type, 'HZ_PARTY_SITE');
252 l_sr_rec.time_zone_id := TO_NUMBER(FND_PROFILE.VALUE_SPECIFIC(NAME => 'CLIENT_TIMEZONE_ID',
253 USER_ID => l_user_id ,
254 RESPONSIBILITY_ID => l_resp_id ,
255 APPLICATION_ID => l_csm_appl_id ));
256
257 --get location if missing
258 IF p_record.CUSTOMER_PRODUCT_ID IS NOT NULL THEN
259 OPEN l_install_site_csr(p_record.customer_product_id);
260 FETCH l_install_site_csr INTO l_install_location_id;
261 CLOSE l_install_site_csr;
262
263 IF p_record.incident_location_type = 'HZ_PARTY_SITE' THEN
264 l_sr_rec.INSTALL_SITE_ID := l_install_location_id;
265 ELSE
266 l_sr_rec.INSTALL_SITE_ID := NULL;
267 END IF;
268 ELSE
269 l_sr_rec.INSTALL_SITE_ID := NULL;
270 l_sr_rec.INSTALL_SITE_USE_ID := NULL;
271 END IF;
272
273 --Check if the item sent by client is valid
274 OPEN c_validate_item_org(p_inventory_item_id => l_sr_rec.INVENTORY_ITEM_ID,
275 p_organization_id =>l_sr_rec.inventory_org_id);
276 FETCH c_validate_item_org INTO l_dummy;
277 IF c_validate_item_org%NOTFOUND THEN
278 SELECT master_organization_id
279 INTO l_org_id
280 FROM mtl_parameters
281 WHERE organization_id = l_sr_rec.inventory_org_id;
282 l_sr_rec.inventory_org_id := l_org_id;
283 END IF;
284 CLOSE c_validate_item_org;
285
286 /* Get Bill to Site id */
287 OPEN c_bill_to_site_id ( p_record.CUSTOMER_ID );
288 FETCH c_bill_to_site_id INTO l_bill_to_site_use_id;
289 IF c_bill_to_site_id%NOTFOUND THEN
290 l_bill_to_site_use_id := NULL;
291 END IF;
292 CLOSE c_bill_to_site_id ;
293
294 l_sr_rec.bill_to_site_use_id := l_bill_to_site_use_id;
295 l_sr_rec.bill_to_party_id := p_record.CUSTOMER_ID;
296
297 /* Get Ship to Site id */
298 OPEN c_ship_to_site_id ( p_record.CUSTOMER_ID );
299 FETCH c_ship_to_site_id INTO l_ship_to_site_use_id;
300 IF c_ship_to_site_id%NOTFOUND THEN
301 l_ship_to_site_use_id := NULL;
302 END IF;
303 CLOSE c_ship_to_site_id ;
304
305 l_sr_rec.ship_to_site_use_id := l_ship_to_site_use_id;
306 l_sr_rec.ship_to_party_id := p_record.CUSTOMER_ID;
307
308
309 --/* Get customer Account id - Just pick the 1st record */
310 IF l_sr_rec.account_id IS NULL THEN
311 OPEN c_customer_account ( p_record.CUSTOMER_ID );
312 FETCH c_customer_account INTO l_customer_account_id;
313 IF c_customer_account%NOTFOUND THEN
314 l_customer_account_id := NULL;
315 END IF;
316 CLOSE c_customer_account;
317 l_sr_rec.account_id := l_customer_account_id;
318 END IF;
319
320 /*Get all contacts */
321 l_contact_index := 0;
322 FOR r_contact IN c_contact( p_record.incident_id, p_record.tranid$$, p_record.clid$$cs ) LOOP
323 /*Contact is passed from mobile*/
324 l_contact_index := l_contact_index + 1;
325 l_contact_rec.SR_CONTACT_POINT_ID := r_contact.SR_CONTACT_POINT_ID;
326 l_contact_rec.PARTY_ID := r_contact.PARTY_ID;
327 l_contact_rec.CONTACT_POINT_ID := r_contact.CONTACT_POINT_ID;
328 l_contact_rec.CONTACT_POINT_TYPE := r_contact.CONTACT_POINT_TYPE;
329 l_contact_rec.PRIMARY_FLAG := r_contact.PRIMARY_FLAG;
330 l_contact_rec.CONTACT_TYPE := r_contact.CONTACT_TYPE;
331 l_contacts_tab( l_contact_index ) := l_contact_rec;
332 END LOOP;
333
334
335 CSM_UTIL_PKG.LOG('Before calling CS_ServiceRequest_PUB.Create_ServiceRequest for ' || p_record.incident_id ,
336 'CSM_SERVICE_REQUESTS_PKG.APPLY_INSERT',FND_LOG.LEVEL_EVENT);
337
338 /* Calling CS API for actual insert */
339 CS_ServiceRequest_PUB.Create_ServiceRequest
340 ( p_api_version => 4.0
341 , p_init_msg_list => FND_API.G_TRUE
342 , p_commit => FND_API.G_TRUE
343 , x_return_status => x_return_status
344 , x_msg_count => l_msg_count
345 , x_msg_data => l_msg_data
346 , p_user_id => l_created_by
347 , p_org_id => p_record.org_id
348 , p_request_id => p_record.incident_id
349 , p_request_number => p_record.incident_number
350 , p_service_request_rec => l_sr_rec
351 , p_notes => l_notes_tab
352 , p_contacts => l_contacts_tab
353 , p_resp_id => l_resp_id
354 , p_default_contract_sla_ind => 'Y'
355 , p_auto_generate_tasks => l_auto_generate_task
356 , p_auto_assign => 'Y'
357 , x_sr_create_out_rec => l_sr_out_rec
358 );
359
360 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
361 /*** exception occurred in API -> return errmsg ***/
362 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
363 (
364 p_api_error => TRUE
365 );
366 CSM_UTIL_PKG.log( 'Error in ' || g_object_name || '.APPLY_INSERT:'
367 || ' ROOT ERROR: CS_ServiceRequest_PUB.Create_ServiceRequest ' || sqlerrm
368 || ' for incident_id ' || p_record.incident_id,'CSM_SERVICE_REQUESTS_PKG.APPLY_INSERT',FND_LOG.LEVEL_ERROR);
369 x_return_status := FND_API.G_RET_STS_ERROR;
370 return;
371 END IF;
372 CSM_UTIL_PKG.LOG('Leaving CSM_SERVICE_REQUESTS_PKG.APPLY_INSERT for incident_id ' || p_record.incident_id ,
373 'CSM_SERVICE_REQUESTS_PKG.APPLY_INSERT',FND_LOG.LEVEL_PROCEDURE);
374
375 EXCEPTION WHEN OTHERS THEN
376 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_INSERT', sqlerrm);
377 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
378 (
379 p_api_error => TRUE
380 );
381 CSM_UTIL_PKG.log( 'Exception in ' || g_object_name || '.APPLY_INSERT: ' || sqlerrm
382 || ' for incident_id ' || p_record.incident_id ,'CSM_SERVICE_REQUESTS_PKG.APPLY_INSERT',FND_LOG.LEVEL_EXCEPTION);
383
384 IF c_customer_account%ISOPEN THEN
385 CLOSE c_customer_account;
386 END IF;
387
388 x_return_status := FND_API.G_RET_STS_ERROR;
389 END APPLY_INSERT;
390
391 /***
392 This procedure is called by APPLY_CLIENT_CHANGES when an updated record is to be processed.
393 ***/
394 PROCEDURE APPLY_UPDATE
395 (
396 p_record IN c_incident%ROWTYPE,
397 p_error_msg out nocopy VARCHAR2,
398 x_return_status IN out nocopy VARCHAR2
399 ) IS
400 /* Cursor to select object_version_number */
401 cursor c_ovn
402 ( b_incident_id number
403 )
404 is
405 select incident_id
406 , object_version_number
407 from cs_incidents
408 where incident_id = b_incident_id;
409
410 r_ovn c_ovn%rowtype;
411
412 /* Cursor to select last_update_date */
413 cursor c_last_update_date
414 ( b_incident_id NUMBER
415 )
416 is
417 SELECT LAST_UPDATE_DATE,
418 LAST_UPDATED_BY
419 from CS_INCIDENTS_ALL_B
420 where incident_id = b_incident_id;
421
422 --115.10
423 CURSOR l_install_site_csr (p_customer_product_id IN number)
424 IS
425 SELECT install_location_id
426 FROM csi_item_instances
427 WHERE instance_id = p_customer_product_id
428 AND install_location_type_code = 'HZ_PARTY_SITES';
429
430 l_install_location_id csi_item_instances.install_location_id%TYPE;
431 --
432
433 r_last_update_date c_last_update_date%ROWTYPE;
434
435 l_sr_rec CS_ServiceRequest_PUB.service_request_rec_type;
436 l_msg_count NUMBER;
437 l_msg_data VARCHAR2(240);
438 l_notes_tab CS_ServiceRequest_PUB.notes_table;
439 l_contacts_tab CS_ServiceRequest_PUB.contacts_table;
440 l_interaction_id NUMBER;
441 l_ovn NUMBER;
442 l_user_id NUMBER;
443 l_workflow_id NUMBER;
444 l_resource_id NUMBER;
445 l_profile_value VARCHAR2(240);
446 l_resp_id NUMBER;
447 l_csm_appl_id fnd_application.application_id%TYPE;
448 l_dummy NUMBER;
449 l_org_id NUMBER;
450 l_last_updated_by NUMBER;
451 l_party_type VARCHAR2(30);
452
453 BEGIN
454
455 CSM_UTIL_PKG.LOG('Entering CSM_SERVICE_REQUESTS_PKG.APPLY_UPDATE for incident_id ' || p_record.incident_id ,
456 'CSM_SERVICE_REQUESTS_PKG.APPLY_UPDATE',FND_LOG.LEVEL_PROCEDURE);
457 -- Check for Stale data
458 l_profile_value := fnd_profile.value(csm_profile_pkg.g_JTM_APPL_CONFLICT_RULE);
459 if l_profile_value = 'SERVER_WINS' AND
460 ASG_DEFER.IS_DEFERRED(p_record.clid$$cs, p_record.tranid$$,g_pub_name, p_record.seqno$$) <> FND_API.G_TRUE then
461 open c_last_update_date(b_incident_id => p_record.incident_id);
462 fetch c_last_update_date into r_last_update_date;
463 if c_last_update_date%found then
464 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
465 close c_last_update_date;
466 x_return_status := FND_API.G_RET_STS_ERROR;
467 p_error_msg := 'UPWARD SYNC CONFLICT: CLIENT LOST: CSM_SERVICE_REQUESTS_PKG.APPLY_UPDATE: Incident_id = '
468 || p_record.incident_id;
469 csm_util_pkg.log('UPWARD SYNC CONFLICT: CLIENT LOST: CSM_SERVICE_REQUESTS_PKG.APPLY_UPDATE: Incident_id = '
470 || p_record.incident_id,'CSM_SERVICE_REQUESTS_PKG.APPLY_UPDATE',FND_LOG.LEVEL_ERROR);
471 return;
472 end if;
473 else
474 CSM_UTIL_PKG.LOG('No record found in Apps Database for incident_id ' || p_record.incident_id ,
475 'CSM_SERVICE_REQUESTS_PKG.APPLY_UPDATE',FND_LOG.LEVEL_PROCEDURE);
476 end if;
477 close c_last_update_date;
478 end if;
479
480 -- Initialization
481 CS_ServiceRequest_PUB.INITIALIZE_REC
482 ( p_sr_record => l_sr_rec
483 );
484
485 -- Lookup the user_id
486 l_user_id := JTM_HOOK_UTIL_PKG.Get_User_Id( p_record.CLID$$CS );
487 l_last_updated_by := p_record.LAST_UPDATED_BY;
488 l_resp_id := TO_NUMBER(fnd_profile.value('CSM_SR_CREATE_RESP'));
489
490 IF l_resp_id IS NULL THEN
491 OPEN c_csm_resp(l_user_id);
492 FETCH c_csm_resp INTO l_resp_id;
493 CLOSE c_csm_resp;
494 END IF;
495
496 -- get csm application id
497 OPEN c_csm_appl(l_user_id);
498 FETCH c_csm_appl INTO l_csm_appl_id;
499 CLOSE c_csm_appl;
500
501 -- Retrieve the required object_version_number.
502 open c_ovn
503 ( b_incident_id => p_record.incident_id
504 );
505 fetch c_ovn
506 into r_ovn;
507 if c_ovn%found
508 then
509 l_ovn := r_ovn.object_version_number;
510 else
511 -- Let the API complain.
512 l_ovn := FND_API.G_MISS_NUM;
513 end if;
514 close c_ovn;
515
516 --ANURAG
517 --credit card number received from client side is in base 64 encoding.
518 --We need to decode this, before passing to SR API
519 --l_sr_rec.cc_number := CS_SERVICEREQUEST_PUB.CC_DECODE(p_record.credit_card_number);
520
521 -- instantiate the sr record
522 --12.1
523 IF p_record.CALLER_TYPE IS NULL THEN
524 --get party type
525 OPEN c_party (p_record.CUSTOMER_ID);
526 FETCH c_party INTO l_party_type;
527 CLOSE c_party;
528 l_sr_rec.CALLER_TYPE := l_party_type;
529 ELSE
530 l_sr_rec.CALLER_TYPE := p_record.CALLER_TYPE;
531 END IF;
532
533 l_sr_rec.SUMMARY := p_record.SUMMARY;
534 l_sr_rec.severity_id := p_record.incident_severity_id ;
535 l_sr_rec.status_id := p_record.incident_status_id ;
536 l_sr_rec.type_id := p_record.incident_type_id ;
537 l_sr_rec.urgency_id := p_record.incident_urgency_id ;
538 l_sr_rec.CUSTOMER_ID := p_record.CUSTOMER_ID;
539
540 --115.10
541 IF p_record.CUSTOMER_PRODUCT_ID IS NOT NULL THEN
542 OPEN l_install_site_csr(p_record.customer_product_id);
543 FETCH l_install_site_csr INTO l_install_location_id;
544 CLOSE l_install_site_csr;
545
546 IF p_record.incident_location_type = 'HZ_PARTY_SITE' THEN
547 l_sr_rec.INSTALL_SITE_ID := l_install_location_id;
548 ELSE
549 --location does not require install site id.And
550 --thedata which is uploaded is causing the record to deger so
551 l_sr_rec.INSTALL_SITE_ID := NULL;
552 END IF;
553
554 ELSE
555 l_sr_rec.INSTALL_SITE_ID := NULL;
556 l_sr_rec.INSTALL_SITE_USE_ID := NULL;
557 END IF;
558 --
559
560 l_sr_rec.CUSTOMER_PRODUCT_ID := p_record.CUSTOMER_PRODUCT_ID;
561 l_sr_rec.INVENTORY_ITEM_ID := p_record.INVENTORY_ITEM_ID;
562 l_sr_rec.current_serial_number := p_record.current_serial_number ;
563 -- l_sr_rec.inventory_org_id := p_record.inv_organization_id ;
564 l_sr_rec.inventory_org_id := NVL(p_record.inv_organization_id, TO_NUMBER(fnd_profile.value('CS_INV_VALIDATION_ORG'))) ;
565
566 -- Bug 4024541 deferred transactions due to invalid inventory_item_id
567 -- validate item/org; if invalid get org from master organization
568 OPEN c_validate_item_org(p_inventory_item_id => l_sr_rec.INVENTORY_ITEM_ID,
569 p_organization_id =>l_sr_rec.inventory_org_id);
570 FETCH c_validate_item_org INTO l_dummy;
571 IF c_validate_item_org%NOTFOUND THEN
572 SELECT master_organization_id
573 INTO l_org_id
574 FROM mtl_parameters
575 WHERE organization_id = l_sr_rec.inventory_org_id;
576
577 l_sr_rec.inventory_org_id := l_org_id;
578 END IF;
579 CLOSE c_validate_item_org;
580
581 l_sr_rec.PROBLEM_CODE := p_record.PROBLEM_CODE;
582 l_sr_rec.RESOLUTION_CODE := p_record.RESOLUTION_CODE;
583 --l_sr_rec.CONTRACT_SERVICE_ID := p_record.CONTRACT_SERVICE_ID;
584 l_sr_rec.INV_ITEM_REVISION := p_record.INV_ITEM_REVISION;
585 l_sr_rec.CUST_PO_NUMBER := p_record.CUSTOMER_PO_NUMBER;
586 --R12
587 l_sr_rec.RESOLUTION_SUMMARY := p_record.RESOLUTION_SUMMARY;
588 l_sr_rec.ACCOUNT_ID := p_record.ACCOUNT_ID;
589 --12.1
590 l_sr_rec.OWNER_GROUP_ID := p_record.OWNER_GROUP_ID;
591 l_sr_rec.OWNER_ID := p_record.INCIDENT_OWNER_ID;
592
593 IF p_record.OWNER_GROUP_ID IS NOT NULL THEN
594 l_sr_rec.group_type := nvl( FND_PROFILE.value('CS_SR_DEFAULT_GROUP_TYPE'), 'RS_GROUP');
595 END IF;
596
597 --ANURAG
598 --credit card number received from client side is in base 64 encoding.
599 --We need to decode this, before passing to SR API
600 --CREDIT CARD FUNCTIONALITY DISABLED IN R12
601 /*
602 l_profile_value := fnd_profile.value('JTM_CREDIT_CARD_ENABLED');
603 IF l_profile_value = 'Y' THEN
604 IF p_record.CREDIT_CARD_NUMBER IS NOT NULL THEN
605 l_sr_rec.cc_number := p_record.credit_card_number;
606 l_sr_rec.cc_type_code := p_record.CREDIT_CARD_TYPE_CODE ;
607 l_sr_rec.cc_expiration_date := p_record.CREDIT_CARD_EXPIRATION_DATE ;
608 l_sr_rec.cc_first_name := p_record.CREDIT_CARD_HOLDER_FNAME ;
609 l_sr_rec.cc_last_name := p_record.CREDIT_CARD_HOLDER_LNAME ;
610 l_sr_rec.cc_middle_name := p_record.CREDIT_CARD_HOLDER_MNAME ;
611 END IF;
612 END IF;
613 */
614 l_sr_rec.request_attribute_1 := p_record.INCIDENT_ATTRIBUTE_1;
615 l_sr_rec.request_attribute_2 := p_record.INCIDENT_ATTRIBUTE_2;
616 l_sr_rec.request_attribute_3 := p_record.INCIDENT_ATTRIBUTE_3;
617 l_sr_rec.request_attribute_4 := p_record.INCIDENT_ATTRIBUTE_4;
618 l_sr_rec.request_attribute_5 := p_record.INCIDENT_ATTRIBUTE_5;
619 l_sr_rec.request_attribute_6 := p_record.INCIDENT_ATTRIBUTE_6;
620 l_sr_rec.request_attribute_7 := p_record.INCIDENT_ATTRIBUTE_7;
621 l_sr_rec.request_attribute_8 := p_record.INCIDENT_ATTRIBUTE_8;
622 l_sr_rec.request_attribute_9 := p_record.INCIDENT_ATTRIBUTE_9;
623 l_sr_rec.request_attribute_10 := p_record.INCIDENT_ATTRIBUTE_10;
624 l_sr_rec.request_attribute_11 := p_record.INCIDENT_ATTRIBUTE_11;
625 l_sr_rec.request_attribute_12 := p_record.INCIDENT_ATTRIBUTE_12;
626 l_sr_rec.request_attribute_13 := p_record.INCIDENT_ATTRIBUTE_13;
627 l_sr_rec.request_attribute_14 := p_record.INCIDENT_ATTRIBUTE_14;
628 l_sr_rec.request_attribute_15 := p_record.INCIDENT_ATTRIBUTE_15;
629 l_sr_rec.request_context := p_record.INCIDENT_CONTEXT;
630
631 -- 11.5.10.7 for telefonica
632 l_sr_rec.external_attribute_1 := p_record.EXTERNAL_ATTRIBUTE_1;
633 l_sr_rec.external_attribute_2 := p_record.EXTERNAL_ATTRIBUTE_2;
634 l_sr_rec.external_attribute_3 := p_record.EXTERNAL_ATTRIBUTE_3;
635 l_sr_rec.external_attribute_4 := p_record.EXTERNAL_ATTRIBUTE_4;
636 l_sr_rec.external_attribute_5 := p_record.EXTERNAL_ATTRIBUTE_5;
637 l_sr_rec.external_attribute_6 := p_record.EXTERNAL_ATTRIBUTE_6;
638 l_sr_rec.external_attribute_7 := p_record.EXTERNAL_ATTRIBUTE_7;
639 l_sr_rec.external_attribute_8 := p_record.EXTERNAL_ATTRIBUTE_8;
640 l_sr_rec.external_attribute_9 := p_record.EXTERNAL_ATTRIBUTE_9;
641 l_sr_rec.external_attribute_10 := p_record.EXTERNAL_ATTRIBUTE_10;
642 l_sr_rec.external_attribute_11 := p_record.EXTERNAL_ATTRIBUTE_11;
643 l_sr_rec.external_attribute_12 := p_record.EXTERNAL_ATTRIBUTE_12;
644 l_sr_rec.external_attribute_13 := p_record.EXTERNAL_ATTRIBUTE_13;
645 l_sr_rec.external_attribute_14 := p_record.EXTERNAL_ATTRIBUTE_14;
646 l_sr_rec.external_attribute_15 := p_record.EXTERNAL_ATTRIBUTE_15;
647 l_sr_rec.external_context := p_record.EXTERNAL_CONTEXT;
648
649 --ER 4105956
650 l_sr_rec.time_zone_id := TO_NUMBER(FND_PROFILE.VALUE_SPECIFIC(NAME => 'CLIENT_TIMEZONE_ID',
651 USER_ID => l_user_id ,
652 RESPONSIBILITY_ID => l_resp_id ,
653 APPLICATION_ID => l_csm_appl_id ));
654
655 --ANURAG
656 --added for CS 11.5.9 uptake
657 l_sr_rec.last_update_program_code := 'CSM_UPSYNC_WRAPPER';
658
659 --added for CS 11.5.10
660 l_sr_rec.incident_location_id := p_record.incident_location_id;
661 l_sr_rec.incident_location_type := nvl(p_record.incident_location_type, 'HZ_PARTY_SITE');
662
663 CSM_UTIL_PKG.LOG('Before calling CS_ServiceRequest_PUB.Update_ServiceRequest for ' || p_record.incident_id ,
664 'CSM_SERVICE_REQUESTS_PKG.APPLY_UPDATE',FND_LOG.LEVEL_EVENT);
665
666 -- Finally the update itself.
667 CS_ServiceRequest_PUB.Update_ServiceRequest
668 ( p_api_version => 3.0
669 , p_init_msg_list => FND_API.G_TRUE
670 , p_commit => FND_API.G_TRUE
671 , x_return_status => x_return_status
672 , x_msg_count => l_msg_count
673 , x_msg_data => l_msg_data
674 , p_request_id => p_record.incident_id
675 , p_object_version_number => l_ovn
676 , p_last_updated_by => l_last_updated_by
677 , p_last_update_date => sysdate
678 , p_service_request_rec => l_sr_rec
679 , p_notes => l_notes_tab
680 , p_contacts => l_contacts_tab
681 , p_resp_id => l_resp_id
682 , p_default_contract_sla_ind => 'Y'
683 , x_workflow_process_id => l_workflow_id
684 , x_interaction_id => l_interaction_id
685 );
686
687 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
688 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
689 (
690 p_api_error => TRUE
691 );
692 CSM_UTIL_PKG.log( 'Error in ' || g_object_name || '.APPLY_UPDATE:'
693 || ' ROOT ERROR: CS_ServiceRequest_PUB.Update_ServiceRequest ' || sqlerrm
694 || ' for incident_id ' || p_record.incident_id ,'CSM_SERVICE_REQUESTS_PKG.APPLY_UPDATE',FND_LOG.LEVEL_ERROR);
695 x_return_status := FND_API.G_RET_STS_ERROR;
696 RETURN;
697 END IF;
698
699 CSM_UTIL_PKG.LOG('Leaving CSM_SERVICE_REQUESTS_PKG.APPLY_UPDATE for incident_id ' || p_record.incident_id ,
700 'CSM_SERVICE_REQUESTS_PKG.APPLY_UPDATE',FND_LOG.LEVEL_PROCEDURE);
701
702 EXCEPTION WHEN OTHERS THEN
703 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_UPDATE', sqlerrm);
704 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
705 (
706 p_api_error => TRUE
707 );
708 CSM_UTIL_PKG.log( 'Exception in ' || g_object_name || '.APPLY_UPDATE: ' || sqlerrm
709 || ' for incident_id ' || p_record.incident_id ,'CSM_SERVICE_REQUESTS_PKG.APPLY_UPDATE',FND_LOG.LEVEL_EXCEPTION);
710
711 x_return_status := FND_API.G_RET_STS_ERROR;
712 END APPLY_UPDATE;
713
714 /***
715 This procedure is called by APPLY_CLIENT_CHANGES for every record in in-queue that needs to be processed.
716 ***/
717 PROCEDURE APPLY_RECORD
718 (
719 p_record IN c_incident%ROWTYPE,
720 p_error_msg out nocopy VARCHAR2,
721 x_return_status IN out nocopy VARCHAR2
722 ) IS
723 l_rc BOOLEAN;
724 l_access_id NUMBER;
725 BEGIN
726 /*** initialize return status and message list ***/
727 x_return_status := FND_API.G_RET_STS_SUCCESS;
728 FND_MSG_PUB.INITIALIZE;
729
730 CSM_UTIL_PKG.LOG('Entering CSM_SERVICE_REQUESTS_PKG.APPLY_RECORD for incident_id ' || p_record.incident_id ,
731 'CSM_SERVICE_REQUESTS_PKG.APPLY_RECORD',FND_LOG.LEVEL_PROCEDURE);
732
733 IF p_record.dmltype$$='I' THEN
734 -- Process insert
735 APPLY_INSERT
736 (
737 p_record,
738 p_error_msg,
739 x_return_status
740 );
741 ELSIF p_record.dmltype$$='U' THEN
742 -- Process update
743 APPLY_UPDATE
744 (
745 p_record,
746 p_error_msg,
747 x_return_status
748 );
749 ELSIF p_record.dmltype$$='D' THEN
750 CSM_UTIL_PKG.LOG
751 ( 'Delete is not supported for this entity'
752 || ' for Incident_id ' || p_record.incident_id ,'CSM_SERVICE_REQUESTS_PKG.APPLY_RECORD',FND_LOG.LEVEL_ERROR);
753
754 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
755 (
756 p_message => 'CSM_DML_OPERATION'
757 , p_token_name1 => 'DML'
758 , p_token_value1 => p_record.dmltype$$
759 );
760
761 x_return_status := FND_API.G_RET_STS_ERROR;
762 ELSE
763 -- invalid dml type
764 CSM_UTIL_PKG.LOG
765 ( 'Invalid DML type: ' || p_record.dmltype$$ || ' is not supported for this entity'
766 || ' for Incident_id ' || p_record.incident_id ,'CSM_SERVICE_REQUESTS_PKG.APPLY_RECORD',FND_LOG.LEVEL_ERROR);
767
768 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
769 (
770 p_message => 'CSM_DML_OPERATION'
771 , p_token_name1 => 'DML'
772 , p_token_value1 => p_record.dmltype$$
773 );
774
775 x_return_status := FND_API.G_RET_STS_ERROR;
776 END IF;
777
778 CSM_UTIL_PKG.LOG('Leaving CSM_SERVICE_REQUESTS_PKG.APPLY_RECORD for incident_id ' || p_record.incident_id ,
779 'CSM_SERVICE_REQUESTS_PKG.APPLY_RECORD',FND_LOG.LEVEL_PROCEDURE);
780 EXCEPTION WHEN OTHERS THEN
781 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_RECORD', sqlerrm);
782 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
783 (
784 p_api_error => TRUE
785 );
786 CSM_UTIL_PKG.log( 'Exception in ' || g_object_name || '.APPLY_RECORD: ' || sqlerrm
787 || ' for incident_id ' || p_record.incident_id ,'CSM_SERVICE_REQUESTS_PKG.APPLY_RECORD',FND_LOG.LEVEL_EXCEPTION);
788
789 x_return_status := FND_API.G_RET_STS_ERROR;
790
791 END APPLY_RECORD;
792
793 /***
794 This procedure is called by CSM_SERVICEP_WRAPPER_PKG when publication item CSM_INCIDENTS_ALL
795 is dirty. This happens when a mobile field service device executed DML on an updatable table and did
796 a fast sync. This procedure will insert the data that came from mobile into the backend tables using
797 public APIs.
798 ***/
799 PROCEDURE APPLY_CLIENT_CHANGES
800 (
801 p_user_name IN VARCHAR2,
802 p_tranid IN NUMBER,
803 p_debug_level IN NUMBER,
804 x_return_status IN out nocopy VARCHAR2
805 ) IS
806
807 l_process_status VARCHAR2(1);
808 l_error_msg VARCHAR2(4000);
809
810 BEGIN
811 CSM_UTIL_PKG.LOG('Entering CSM_SERVICE_REQUESTS_PKG.APPLY_CLIENT_CHANGES ',
812 'CSM_SERVICE_REQUESTS_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_PROCEDURE);
813 g_debug_level := p_debug_level;
814 x_return_status := FND_API.G_RET_STS_SUCCESS;
815
816 /*** loop through CSM_INCIDENTS_ALL_INQ records in inqueue ***/
817 FOR r_incident IN c_incident( p_user_name, p_tranid) LOOP
818 SAVEPOINT save_rec ;
819 /*** apply record ***/
820 APPLY_RECORD
821 (
822 r_incident
823 , l_error_msg
824 , l_process_status
825 );
826
827 /*** was record processed successfully? ***/
828 IF l_process_status = FND_API.G_RET_STS_SUCCESS THEN
829 /*** Yes -> delete record from inqueue ***/
830 CSM_UTIL_PKG.DELETE_RECORD
831 (
832 p_user_name,
833 p_tranid,
834 r_incident.seqno$$,
835 r_incident.incident_id,
836 g_object_name,
837 g_pub_name,
838 l_error_msg,
839 l_process_status
840 );
841 /*** was delete successful? ***/
842 IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
843 /*** no -> rollback ***/
844 CSM_UTIL_PKG.LOG
845 ( 'Deleting from inqueue failed, rolling back to savepoint'
846 || ' for incident_id ' || r_incident.incident_id ,'CSM_SERVICE_REQUESTS_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_ERROR); -- put PK column here
847 ROLLBACK TO save_rec;
848 x_return_status := FND_API.G_RET_STS_ERROR;
849 ELSE
850 /*** Yes -> Delete contact recs */
851 FOR r_contacts IN c_contact( r_incident.incident_id, p_tranid, p_user_name ) LOOP
852 /* Delete matching contact record(s) */
853 CSM_UTIL_PKG.DELETE_RECORD
854 (
855 p_user_name,
856 p_tranid,
857 r_contacts.seqno$$,
858 r_contacts.SR_CONTACT_POINT_ID,
859 g_object_name,
860 'CSF_M_SR_CONTACTS',
861 l_error_msg,
862 l_process_status
863 );
864 /*** was delete successful? ***/
865 IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
866 /*** no -> rollback ***/
867 CSM_UTIL_PKG.LOG
868 ( 'Deleting from inqueue failed, rolling back to savepoint'
869 || ' for incident_id ' || r_incident.incident_id ,'CSM_SERVICE_REQUESTS_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_ERROR); -- put PK column here
870 ROLLBACK TO save_rec;
871 x_return_status := FND_API.G_RET_STS_ERROR;
872 END IF;
873 END LOOP;
874 END IF;
875 ELSIF l_process_Status <> FND_API.G_RET_STS_SUCCESS THEN
876 /*** Record was not applied successfully -> defer and reject records ***/
877 csm_util_pkg.log( 'Record not processed successfully, deferring and rejecting record'
878 || ' for PK ' || r_incident.incident_id ,'CSM_SERVICE_REQUESTS_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_ERROR); -- put PK column here
879
880 CSM_UTIL_PKG.DEFER_RECORD
881 (
882 p_user_name
883 , p_tranid
884 , r_incident.seqno$$
885 , r_incident.incident_id
886 , g_object_name
887 , g_pub_name
888 , l_error_msg
889 , l_process_status
890 , r_incident.dmltype$$
891 );
892
893 /*** Was defer successful? ***/
894 IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
895 /*** no -> rollback ***/
896 CSM_UTIL_PKG.LOG
897 ( 'Defer record failed, rolling back to savepoint'
898 || ' for PK ' || r_incident.incident_id ,'CSM_SERVICE_REQUESTS_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_ERROR); -- put PK column here
899 ROLLBACK TO save_rec;
900 x_return_status := FND_API.G_RET_STS_ERROR;
901 ELSE
902 /** Yes **/
903 FOR r_contacts IN c_contact( r_incident.incident_id, p_tranid, p_user_name ) LOOP
904 /* Defer matching contact record(s) */
905 CSM_UTIL_PKG.DEFER_RECORD
906 (
907 p_user_name,
908 p_tranid,
909 r_contacts.seqno$$,
910 r_contacts.SR_CONTACT_POINT_ID,
911 g_object_name,
912 'CSF_M_SR_CONTACTS',
913 l_error_msg,
914 l_process_status
915 , r_contacts.dmltype$$
916 );
917 END LOOP;
918 END IF ;
919
920 END IF;
921
922 END LOOP;
923
924 CSM_UTIL_PKG.LOG('Leaving CSM_SERVICE_REQUESTS_PKG.APPLY_CLIENT_CHANGES',
925 'CSM_SERVICE_REQUESTS_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_PROCEDURE);
926
927 EXCEPTION WHEN OTHERS THEN
928 /*** catch and log exceptions ***/
929 CSM_UTIL_PKG.log( 'Exception in ' || g_object_name || '.APPLY_CLIENT_CHANGES: ' || sqlerrm
930 ,'CSM_SERVICE_REQUESTS_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_EXCEPTION);
931
932 x_return_status := FND_API.G_RET_STS_ERROR;
933
934 END APPLY_CLIENT_CHANGES;
935
936 FUNCTION CONFLICT_RESOLUTION_METHOD (p_user_name IN VARCHAR2,
937 p_tran_id IN NUMBER,
938 p_sequence IN NUMBER)
939 RETURN VARCHAR2 IS
940 l_profile_value VARCHAR2(30) ;
941 l_user_id NUMBER ;
942 cursor get_user_id(l_tran_id in number,
943 l_user_name in varchar2,
944 l_sequence in number)
945 IS
946 SELECT b.last_updated_by
947 FROM CS_INCIDENTS_ALL_B b,
948 CSM_INCIDENTS_ALL_INQ a
949 WHERE a.clid$$cs = l_user_name
950 AND tranid$$ = l_tran_id
951 AND seqno$$ = l_sequence
952 AND a.incident_id = b.incident_id ;
953
954 BEGIN
955 CSM_UTIL_PKG.LOG('Entering CSM_SERVICE_REQUESTS_PKG.CONFLICT_RESOLUTION_METHOD for user ' || p_user_name ,'CSM_SERVICE_REQUESTS_PKG.CONFLICT_RESOLUTION_METHOD',FND_LOG.LEVEL_PROCEDURE);
956 l_profile_value := fnd_profile.value(csm_profile_pkg.g_JTM_APPL_CONFLICT_RULE);
957 OPEN get_user_id(p_tran_id, p_user_name, p_sequence) ;
958 FETCH get_user_id
959 INTO l_user_id ;
960 CLOSE get_user_id ;
961
962 if l_profile_value = 'SERVER_WINS' AND l_user_id <> asg_base.get_user_id(p_user_name) then
963 RETURN 'S' ;
964 else
965 RETURN 'C' ;
966 END IF ;
967
968 EXCEPTION
969 WHEN OTHERS THEN
970 RETURN 'C';
971 END CONFLICT_RESOLUTION_METHOD;
972
973 END CSM_SERVICE_REQUESTS_PKG;