[Home] [Help]
PACKAGE BODY: APPS.CSL_SERVICE_REQUESTS_PKG
Source
1 PACKAGE BODY CSL_SERVICE_REQUESTS_PKG AS
2 /* $Header: cslvincb.pls 120.1 2005/08/30 21:26:24 utekumal noship $ */
3
4 error EXCEPTION;
5
6 /*** Globals ***/
7 g_object_name CONSTANT VARCHAR2(30) := 'CSL_SERVICE_REQUESTS_PKG';
8 g_pub_name CONSTANT VARCHAR2(30) := 'CSL_CS_INCIDENTS_ALL_VL';
9 g_debug_level NUMBER; -- debug level
10
11 CURSOR c_incident( b_user_name VARCHAR2, b_tranid NUMBER) is
12 SELECT *
13 FROM CSL_CS_INCIDENTS_ALL_VL_inq
14 WHERE tranid$$ = b_tranid
15 AND clid$$cs = b_user_name;
16
17 CURSOR c_contact( b_incident_id NUMBER, b_tranid NUMBER, b_user_name VARCHAR2 ) IS
18 SELECT *
19 FROM CSL_CS_HZ_SR_CONTACT_PTS_INQ
20 WHERE INCIDENT_ID = b_incident_id
21 AND TRANID$$ = b_tranid
22 AND clid$$cs = b_user_name;
23
24 /***
25 This procedure is called by APPLY_CLIENT_CHANGES when an inserted record is to be processed.
26 ***/
27 PROCEDURE APPLY_INSERT
28 (
29 p_record IN c_incident%ROWTYPE,
30 p_error_msg OUT NOCOPY VARCHAR2,
31 x_return_status IN OUT NOCOPY VARCHAR2
32 ) IS
33
34 -- Commented Out for Sql Repository Performance Fix
35
36 /* CURSOR c_org_rel_contacts
37 ( b_customer_id CS_INCIDENTS_ALL_B.CUSTOMER_ID%TYPE
38 ) IS
39 SELECT b.party_id
40 FROM CSC_HZ_PARTIES_V b
41 WHERE b.object_id = b_customer_id
42 -- AND b.party_id = p_customer_contact_id
43 AND b.sub_status = 'A'
44 AND b.obj_status = 'A'
45 AND b.relation in ('CONTACT_OF','EMPLOYEE_OF')
46 AND b.party_type = 'PARTY_RELATIONSHIP'
47 -- AND b.obj_party_type = 'ORGANIZATION' -- can be both org and person caller type
48 AND rownum <= 1;
49
50 CURSOR c_person_contacts
51 ( b_customer_id CS_INCIDENTS_ALL_B.CUSTOMER_ID%TYPE
52 ) IS
53 SELECT b.subject_id
54 FROM CSC_HZ_PARTIES_V b
55 WHERE b.object_id = b_customer_id
56 -- AND b.subject_id = p_customer_contact_id
57 AND b.sub_status = 'A'
58 AND b.obj_status = 'A'
59 AND rownum <= 1
60 UNION
61 SELECT PARTY_ID
62 FROM HZ_PARTIES
63 WHERE party_id = b_customer_id
64 AND status ='A'; */
65
66
67 CURSOR c_org_rel_contacts
68 ( b_customer_id CS_INCIDENTS_ALL_B.CUSTOMER_ID%TYPE
69 ) IS
70 SELECT b.party_id FROM HZ_RELATIONSHIPS b
71 WHERE b.object_id = b_customer_id
72 AND object_table_name = 'HZ_PARTIES'
73 AND status = 'A'
74 AND b.relationship_code in ('CONTACT_OF','EMPLOYEE_OF')
75 AND rownum <= 1;
76
77
78 CURSOR c_person_contacts
79 ( b_customer_id CS_INCIDENTS_ALL_B.CUSTOMER_ID%TYPE
80 ) IS
81 SELECT b.subject_id FROM HZ_RELATIONSHIPS b
82 WHERE b.object_id = b_customer_id
83 AND object_table_name = 'HZ_PARTIES'
84 AND status = 'A'
85 AND rownum <= 1;
86
87
88 CURSOR c_party_contact
89 ( b_customer_id CS_INCIDENTS_ALL_B.CUSTOMER_ID%TYPE
90 ) IS
91 SELECT PARTY_ID FROM HZ_PARTIES
92 WHERE party_id = b_customer_id AND status = 'A';
93
94
95 /* ER 3746707
96 Cursor to get the Bill to Address */
97 CURSOR c_bill_to_site_id
98 ( b_customer_id CS_INCIDENTS_ALL_B.CUSTOMER_ID%TYPE
99 ) IS
100 SELECT use.party_site_use_id FROM
101 hz_party_sites site, hz_party_site_uses use
102 WHERE site.party_site_id = use.party_site_id
103 AND site.status= 'A'
104 AND use.site_use_type= 'BILL_TO'
105 AND use.primary_per_type = 'Y'
106 AND use.status = 'A'
107 AND site.party_id = b_customer_id
108 AND trunc(SYSDATE) BETWEEN TRUNC (NVL(use.begin_date, SYSDATE))
109 AND (NVL(use.end_date, SYSDATE));
110
111 /* ER 3746707
112 Cursor to get the Ship to Address */
113 CURSOR c_ship_to_site_id
114 ( b_customer_id CS_INCIDENTS_ALL_B.CUSTOMER_ID%TYPE
115 ) IS
116 SELECT use.party_site_use_id FROM
117 hz_party_sites site, hz_party_site_uses use
118 WHERE site.party_site_id = use.party_site_id
119 AND site.status= 'A'
120 AND use.site_use_type= 'SHIP_TO'
121 AND use.primary_per_type = 'Y'
122 AND use.status = 'A'
123 AND site.party_id = b_customer_id
124 AND trunc(SYSDATE) BETWEEN TRUNC (NVL(use.begin_date, SYSDATE))
125 AND (NVL(use.end_date, SYSDATE));
126
127 /* ER 3746707
128 Cursor to get the customer account id */
129 CURSOR c_customer_account
130 ( b_customer_id CS_INCIDENTS_ALL_B.CUSTOMER_ID%TYPE
131 ) IS
132 SELECT custa.cust_account_id FROM hz_cust_accounts custa
133 WHERE custa.status = 'A'
134 AND custa.party_id = b_customer_id;
135
136 l_sr_rec CS_ServiceRequest_PUB.service_request_rec_type;
137 l_user_id NUMBER;
138 l_msg_count NUMBER;
139 l_msg_data VARCHAR2(240);
140 l_notes_tab CS_ServiceRequest_PUB.notes_table;
141 l_contacts_tab CS_ServiceRequest_PUB.contacts_table;
142 l_request_id NUMBER;
143 l_request_number VARCHAR2(64);
144 l_interaction_id NUMBER;
145 l_workflow_process_id NUMBER;
146 l_contact_rec CS_ServiceRequest_PUB.contacts_rec;
147 l_contact_index BINARY_INTEGER;
148 l_contact_id CS_HZ_SR_CONTACT_POINTS.PARTY_ID%TYPE;
149 l_profile_value VARCHAR2(240);
150 l_decript_value VARCHAR2(250);
151
152 -- CS 11.5.9 Uptake
153 x_individual_owner NUMBER;
154 x_individual_type VARCHAR2(240);
155 x_group_owner NUMBER;
156
157 --ER 3746707
158 l_bill_to_site_use_id NUMBER;
159 l_ship_to_site_use_id NUMBER;
160 l_customer_account_id NUMBER;
161
162 BEGIN
163 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
164 jtm_message_log_pkg.Log_Msg
165 ( v_object_id => p_record.incident_id
166 , v_object_name => g_object_name
167 , v_message => 'Entering ' || g_object_name || '.APPLY_INSERT'
168 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
169 END IF;
170
171 -- Lookup the user_id
172 l_user_id := JTM_HOOK_UTIL_PKG.Get_User_Id( p_record.CLID$$CS );
173
174 -- Initialization
175 CS_ServiceRequest_PUB.INITIALIZE_REC
176 ( p_sr_record => l_sr_rec
177 );
178
179 l_sr_rec.CALLER_TYPE := p_record.CALLER_TYPE;
180 l_sr_rec.SUMMARY := p_record.SUMMARY;
181 l_sr_rec.CUSTOMER_ID := p_record.CUSTOMER_ID;
182 l_sr_rec.INSTALL_SITE_ID := NVL(p_record.INSTALL_SITE_ID, p_record.INSTALL_SITE_USE_ID);
183 l_sr_rec.INSTALL_SITE_USE_ID := l_sr_rec.INSTALL_SITE_ID;
184 l_sr_rec.CUSTOMER_PRODUCT_ID := p_record.CUSTOMER_PRODUCT_ID;
185 l_sr_rec.INVENTORY_ITEM_ID := p_record.INVENTORY_ITEM_ID;
186 l_sr_rec.inventory_org_id := TO_NUMBER(fnd_profile.value(
187 'CS_INV_VALIDATION_ORG') ) ;
188 l_sr_rec.PROBLEM_CODE := p_record.PROBLEM_CODE;
189 l_sr_rec.RESOLUTION_CODE := p_record.RESOLUTION_CODE;
190 l_sr_rec.CONTRACT_SERVICE_ID := p_record.CONTRACT_SERVICE_ID;
191 l_sr_rec.INV_ITEM_REVISION := p_record.INV_ITEM_REVISION;
192 l_sr_rec.CUST_PO_NUMBER := p_record.CUSTOMER_PO_NUMBER;
193 l_sr_rec.SEVERITY_ID := p_record.INCIDENT_SEVERITY_ID;
194 l_sr_rec.STATUS_ID := p_record.INCIDENT_STATUS_ID;
195 l_sr_rec.TYPE_ID := p_record.INCIDENT_TYPE_ID;
196 l_sr_rec.URGENCY_ID := p_record.INCIDENT_URGENCY_ID;
197 l_sr_rec.request_attribute_1 := p_record.INCIDENT_ATTRIBUTE_1;
198 l_sr_rec.request_attribute_2 := p_record.INCIDENT_ATTRIBUTE_2;
199 l_sr_rec.request_attribute_3 := p_record.INCIDENT_ATTRIBUTE_3;
200 l_sr_rec.request_attribute_4 := p_record.INCIDENT_ATTRIBUTE_4;
201 l_sr_rec.request_attribute_5 := p_record.INCIDENT_ATTRIBUTE_5;
202 l_sr_rec.request_attribute_6 := p_record.INCIDENT_ATTRIBUTE_6;
203 l_sr_rec.request_attribute_7 := p_record.INCIDENT_ATTRIBUTE_7;
204 l_sr_rec.request_attribute_8 := p_record.INCIDENT_ATTRIBUTE_8;
205 l_sr_rec.request_attribute_9 := p_record.INCIDENT_ATTRIBUTE_9;
206 l_sr_rec.request_attribute_10 := p_record.INCIDENT_ATTRIBUTE_10;
207 l_sr_rec.request_attribute_11 := p_record.INCIDENT_ATTRIBUTE_11;
208 l_sr_rec.request_attribute_12 := p_record.INCIDENT_ATTRIBUTE_12;
209 l_sr_rec.request_attribute_13 := p_record.INCIDENT_ATTRIBUTE_13;
210 l_sr_rec.request_attribute_14 := p_record.INCIDENT_ATTRIBUTE_14;
211 l_sr_rec.request_attribute_15 := p_record.INCIDENT_ATTRIBUTE_15;
212 l_sr_rec.request_context := p_record.INCIDENT_CONTEXT;
213
214 --ER 3949138
215 l_sr_rec.external_attribute_1 := p_record.EXTERNAL_ATTRIBUTE_1;
216 l_sr_rec.external_attribute_2 := p_record.EXTERNAL_ATTRIBUTE_2;
217 l_sr_rec.external_attribute_3 := p_record.EXTERNAL_ATTRIBUTE_3;
218 l_sr_rec.external_attribute_4 := p_record.EXTERNAL_ATTRIBUTE_4;
219 l_sr_rec.external_attribute_5 := p_record.EXTERNAL_ATTRIBUTE_5;
220 l_sr_rec.external_attribute_6 := p_record.EXTERNAL_ATTRIBUTE_6;
221 l_sr_rec.external_attribute_7 := p_record.EXTERNAL_ATTRIBUTE_7;
222 l_sr_rec.external_attribute_8 := p_record.EXTERNAL_ATTRIBUTE_8;
223 l_sr_rec.external_attribute_9 := p_record.EXTERNAL_ATTRIBUTE_9;
224 l_sr_rec.external_attribute_10 := p_record.EXTERNAL_ATTRIBUTE_10;
225 l_sr_rec.external_attribute_11 := p_record.EXTERNAL_ATTRIBUTE_11;
226 l_sr_rec.external_attribute_12 := p_record.EXTERNAL_ATTRIBUTE_12;
227 l_sr_rec.external_attribute_13 := p_record.EXTERNAL_ATTRIBUTE_13;
228 l_sr_rec.external_attribute_14 := p_record.EXTERNAL_ATTRIBUTE_14;
229 l_sr_rec.external_attribute_15 := p_record.EXTERNAL_ATTRIBUTE_15;
230 l_sr_rec.external_context := p_record.EXTERNAL_CONTEXT;
231
232 /* 11.5.10 incident address changes - 3430663 */
233 l_sr_rec.incident_location_id := p_record.incident_location_id;
234 l_sr_rec.incident_address := p_record.incident_address;
235 l_sr_rec.incident_city := p_record.incident_city;
236 l_sr_rec.incident_state := p_record.incident_state;
237 l_sr_rec.incident_country := p_record.incident_country;
238 l_sr_rec.incident_province := p_record.incident_province;
239 l_sr_rec.incident_postal_code := p_record.incident_postal_code;
240 l_sr_rec.incident_county := p_record.incident_county;
241 l_sr_rec.incident_location_type := p_record.incident_location_type;
242
243 /*Get all contacts*/
244 l_contact_index := 0;
245 FOR r_contact IN c_contact ( p_record.incident_id,
246 p_record.tranid$$, p_record.clid$$cs )
247 LOOP
248 /*Contact is passed from mobile*/
249 l_contact_index := l_contact_index + 1;
250 l_contact_rec.SR_CONTACT_POINT_ID := r_contact.SR_CONTACT_POINT_ID;
251 l_contact_rec.PARTY_ID := r_contact.PARTY_ID;
252 l_contact_rec.CONTACT_POINT_ID := r_contact.CONTACT_POINT_ID;
253 l_contact_rec.PRIMARY_FLAG := r_contact.PRIMARY_FLAG;
254 l_contact_rec.CONTACT_TYPE := r_contact.CONTACT_TYPE;
255 l_contacts_tab( l_contact_index ) := l_contact_rec;
256 END LOOP;
257
258 /*
259 -- Comment out the following section, as contact is not mandatory for
260 -- CS APIs anymore, and we don't want to default wrong contact for the SR
261
262 IF l_contact_index = 0 THEN
263
264 -- Contact was not passed from mobile, so get the primary contact from
265 -- the backend contact type is from profile CS_SR_DEFAULT_CONTACT_TYPE
266 -- if person
267
268 OPEN c_org_rel_contacts( p_record.customer_id );
269 FETCH c_org_rel_contacts INTO l_contact_id;
270
271 IF c_org_rel_contacts%FOUND THEN
272 l_contact_rec.PARTY_ID := l_contact_id;
273 l_contact_rec.PRIMARY_FLAG := 'Y';
274 l_contact_rec.CONTACT_TYPE := 'PARTY_RELATIONSHIP';
275
276 ELSIF p_record.CALLER_TYPE = 'PERSON' THEN
277 -- Get the person contact
278 OPEN c_person_contacts( p_record.customer_id );
279 FETCH c_person_contacts INTO l_contact_id;
280
281 IF c_person_contacts%NOTFOUND THEN
282 OPEN c_party_contact (p_record.customer_id);
283 FETCH c_party_contact INTO l_contact_id;
284 IF c_party_contact%NOTFOUND THEN
285 l_contact_id := p_record.customer_id;
286 END IF;
287 END IF;
288
289 CLOSE c_person_contacts;
290 l_contact_rec.PARTY_ID := l_contact_id;
291 l_contact_rec.PRIMARY_FLAG := 'Y';
292 l_contact_rec.CONTACT_TYPE := 'PERSON';
293 END IF; -- c_org_rel_contacts%FOUND THEN
294
295 CLOSE c_org_rel_contacts;
296 -- Set the contact tab to the found contact
297 l_contacts_tab( 1 ) := l_contact_rec;
298 END IF; -- l_contact_index = 0
299
300 */
301
302 -- See if Credit Card profile is set?
303 --Bug 4496299
304 /*
305 l_profile_value := fnd_profile.value('JTM_CREDIT_CARD_ENABLED');
306 IF l_profile_value = 'Y' THEN
307 IF p_record.CREDIT_CARD_NUMBER IS NOT NULL THEN
308 l_decript_value := CS_SERVICEREQUEST_PUB.CC_DECODE(p_record.CREDIT_CARD_NUMBER);
309 l_sr_rec.cc_number := l_decript_value;
310 l_sr_rec.cc_type_code := p_record.CREDIT_CARD_TYPE_CODE;
311 l_sr_rec.cc_expiration_date := p_record.CREDIT_CARD_EXPIRATION_DATE;
312 l_sr_rec.cc_first_name := p_record.CREDIT_CARD_HOLDER_FNAME;
313 l_sr_rec.cc_middle_name := p_record.CREDIT_CARD_HOLDER_MNAME;
314 l_sr_rec.cc_last_name := p_record.CREDIT_CARD_HOLDER_LNAME;
315 l_sr_rec.cc_id := p_record.CREDIT_CARD_ID;
316 END IF;
317 END IF;
318 */
319
320 /* ER 3746707
321 Get Bill to Site id */
322 OPEN c_bill_to_site_id ( p_record.CUSTOMER_ID );
323 FETCH c_bill_to_site_id INTO l_bill_to_site_use_id;
324 IF c_bill_to_site_id%NOTFOUND THEN
325 l_bill_to_site_use_id := NULL;
326 END IF;
327 CLOSE c_bill_to_site_id ;
328
329 l_sr_rec.bill_to_site_use_id := l_bill_to_site_use_id;
330 l_sr_rec.bill_to_party_id := p_record.CUSTOMER_ID;
331
332 /* ER 3746707
333 Get Ship to Site id */
334 OPEN c_ship_to_site_id ( p_record.CUSTOMER_ID );
335 FETCH c_ship_to_site_id INTO l_ship_to_site_use_id;
336 IF c_ship_to_site_id%NOTFOUND THEN
337 l_ship_to_site_use_id := NULL;
338 END IF;
339 CLOSE c_ship_to_site_id ;
340
341 l_sr_rec.ship_to_site_use_id := l_ship_to_site_use_id;
342 l_sr_rec.ship_to_party_id := p_record.CUSTOMER_ID;
343
344 /* ER 3746707
345 Get customer Account id - Just pick the first record */
346 OPEN c_customer_account ( p_record.CUSTOMER_ID );
347 FETCH c_customer_account INTO l_customer_account_id;
348 IF c_customer_account%NOTFOUND THEN
349 l_customer_account_id := NULL;
350 END IF;
351 CLOSE c_customer_account;
352
353 l_sr_rec.account_id := l_customer_account_id;
354
355
356 -- CS 11.5.9 Uptake
357 l_sr_rec.creation_program_code := 'CSL_LAPTOP';
358
359 -- Api Modified, version 3.0, new parameters for Assignment Mgr
360 CS_ServiceRequest_PUB.Create_ServiceRequest
361 ( p_api_version => 3.0
362 , p_init_msg_list => FND_API.G_TRUE
363 , p_commit => FND_API.G_TRUE
364 , x_return_status => x_return_status
365 , x_msg_count => l_msg_count
366 , x_msg_data => l_msg_data
367 , p_user_id => l_user_id
368 , p_org_id => p_record.org_id
369 , p_request_id => p_record.incident_id
370 , p_request_number => p_record.incident_number
371 , p_service_request_rec => l_sr_rec
372 , p_notes => l_notes_tab
373 , p_contacts => l_contacts_tab
374 , p_resp_id => to_number(fnd_profile.value('CSL_SR_CREATE_RESP'))
375 , x_request_id => l_request_id
376 , x_request_number => l_request_number
377 , x_interaction_id => l_interaction_id
378 , x_workflow_process_id => l_workflow_process_id
379 -- CS Uptake 11.5.9 - Assignment Manager
380 , x_individual_owner => x_individual_owner
381 , x_individual_type => x_individual_type
382 , x_group_owner => x_group_owner
383 );
384
385 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
386 /*** exception occurred in API -> return errmsg ***/
387 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
388 (
389 p_api_error => TRUE
390 );
391 END IF;
392
393 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
394 jtm_message_log_pkg.Log_Msg
395 ( v_object_id => p_record.incident_id
396 , v_object_name => g_object_name
397 , v_message => 'Leaving ' || g_object_name || '.APPLY_INSERT'
398 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
399 END IF;
400
401 EXCEPTION WHEN OTHERS THEN
402 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
403 jtm_message_log_pkg.Log_Msg
404 ( v_object_id => p_record.incident_id
405 , v_object_name => g_object_name
406 , v_message => 'Exception occurred in APPLY_INSERT:' || fnd_global.local_chr(10) || sqlerrm
407 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
408 END IF;
409
410 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_INSERT', sqlerrm);
411 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
412 (
413 p_api_error => TRUE
414 );
415
416 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
417 jtm_message_log_pkg.Log_Msg
418 ( v_object_id => p_record.incident_id
419 , v_object_name => g_object_name
420 , v_message => 'Leaving ' || g_object_name || '.APPLY_INSERT'
421 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
422 END IF;
423
424 x_return_status := FND_API.G_RET_STS_ERROR;
425 END APPLY_INSERT;
426
427 /***
428 This procedure is called by APPLY_CLIENT_CHANGES when an updated record is to be processed.
429 ***/
430 PROCEDURE APPLY_UPDATE
431 (
432 p_record IN c_incident%ROWTYPE,
433 p_error_msg OUT NOCOPY VARCHAR2,
434 x_return_status IN OUT NOCOPY VARCHAR2
435 ) IS
436 cursor c_ovn
437 ( b_incident_id number
438 )
439 is
440 select incident_id
441 , object_version_number
442 from cs_incidents
443 where incident_id = b_incident_id;
444
445 r_ovn c_ovn%rowtype;
446
447 cursor c_last_update_date
448 ( b_incident_id NUMBER
449 )
450 is
451 SELECT LAST_UPDATE_DATE
452 from CS_INCIDENTS_ALL_B
453 where incident_id = b_incident_id;
454
455 r_last_update_date c_last_update_date%ROWTYPE;
456
457 cursor c_credit_card
458 ( b_incident_id NUMBER
459 )
460 is
461 SELECT CREDIT_CARD_NUMBER, CREDIT_CARD_TYPE_CODE,
462 CREDIT_CARD_EXPIRATION_DATE, CREDIT_CARD_HOLDER_FNAME,
463 CREDIT_CARD_HOLDER_MNAME, CREDIT_CARD_HOLDER_LNAME, CREDIT_CARD_ID
464 FROM CS_INCIDENTS_ALL_B
465 WHERE incident_id = b_incident_id;
466
467 r_credit_card c_credit_card%ROWTYPE;
468
469
470 l_sr_rec CS_ServiceRequest_PUB.service_request_rec_type;
471 l_msg_count NUMBER;
472 l_msg_data VARCHAR2(240);
473 l_notes_tab CS_ServiceRequest_PUB.notes_table;
474 l_contacts_tab CS_ServiceRequest_PUB.contacts_table;
475 l_interaction_id NUMBER;
476 l_ovn NUMBER;
477 l_user_id NUMBER;
478 l_workflow_id NUMBER;
479 l_resource_id NUMBER;
480 l_profile_value VARCHAR2(240);
481 l_decript_value VARCHAR2(250);
482
483 BEGIN
484 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
485 jtm_message_log_pkg.Log_Msg
486 ( v_object_id => p_record.incident_id
487 , v_object_name => g_object_name
488 , v_message => 'Entering ' || g_object_name || '.APPLY_UPDATE'
489 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
490 END IF;
491
492 -- Check for Stale data
493 l_profile_value := fnd_profile.value('JTM_APPL_CONFLICT_RULE');
494 if l_profile_value = 'SERVER_WINS' AND
495 ASG_DEFER.IS_DEFERRED(p_record.clid$$cs, p_record.tranid$$,g_object_name, p_record.seqno$$) <> FND_API.G_TRUE then
496 open c_last_update_date(b_incident_id => p_record.incident_id);
497 fetch c_last_update_date into r_last_update_date;
498 if c_last_update_date%found then
499 if r_last_update_date.last_update_date <> p_record.last_update_date then
500 close c_last_update_date;
501 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
502 jtm_message_log_pkg.Log_Msg
503 ( v_object_id => p_record.incident_id
504 , v_object_name => g_object_name
505 , v_message => 'Record is stale data'
506 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
507 END IF;
508 fnd_message.set_name
509 ( 'JTM'
510 , 'JTM_STALE_DATA'
511 );
512 fnd_msg_pub.add;
513
514 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
515 jtm_message_log_pkg.Log_Msg
516 ( v_object_id => p_record.incident_id
517 , v_object_name => g_object_name
518 , v_message => 'Leaving ' || g_object_name || '.APPLY_UPDATE'
519 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
520 END IF;
521
522 x_return_status := FND_API.G_RET_STS_SUCCESS;
523 return;
524 end if;
525 else
526 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
527 jtm_message_log_pkg.Log_Msg
528 ( v_object_id => p_record.incident_id
529 , v_object_name => g_object_name
530 , v_message => 'No record found in Apps Database.'
531 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
532 END IF;
533 end if;
534 close c_last_update_date;
535 end if;
536
537 -- Initialization
538 CS_ServiceRequest_PUB.INITIALIZE_REC
539 ( p_sr_record => l_sr_rec
540 );
541
542 -- Lookup the user_id
543 l_user_id := JTM_HOOK_UTIL_PKG.Get_User_Id( p_record.CLID$$CS );
544
545 -- Retrieve the required object_version_number.
546 open c_ovn
547 ( b_incident_id => p_record.incident_id
548 );
549 fetch c_ovn
550 into r_ovn;
551 if c_ovn%found
552 then
553 l_ovn := r_ovn.object_version_number;
554 else
555 -- Let the API complain.
556 --l_ovn := FND_API.G_MISS_NUM;
557 -- FND_API.G_MISS_NUM is obsoleted. against GSCC standard
558 l_ovn := -1;
559 end if;
560 close c_ovn;
561
562 -- instantiate the sr record
563 l_sr_rec.CALLER_TYPE := p_record.CALLER_TYPE;
564 l_sr_rec.SUMMARY := p_record.SUMMARY;
565 l_sr_rec.CUSTOMER_ID := p_record.CUSTOMER_ID;
566 l_sr_rec.INSTALL_SITE_ID := NVL(p_record.INSTALL_SITE_ID, p_record.INSTALL_SITE_USE_ID);
567 l_sr_rec.INSTALL_SITE_USE_ID := l_sr_rec.INSTALL_SITE_ID;
568 l_sr_rec.CUSTOMER_PRODUCT_ID := p_record.CUSTOMER_PRODUCT_ID;
569 l_sr_rec.INVENTORY_ITEM_ID := p_record.INVENTORY_ITEM_ID;
570 l_sr_rec.inventory_org_id := p_record.inv_organization_id ;
571 l_sr_rec.PROBLEM_CODE := p_record.PROBLEM_CODE;
572 l_sr_rec.RESOLUTION_CODE := p_record.RESOLUTION_CODE;
573 /* Update of contract line must be removed see bug 2610677
574 l_sr_rec.CONTRACT_SERVICE_ID := p_record.CONTRACT_SERVICE_ID; */
575 l_sr_rec.INV_ITEM_REVISION := p_record.INV_ITEM_REVISION;
576 l_sr_rec.CUST_PO_NUMBER := p_record.CUSTOMER_PO_NUMBER;
577 l_sr_rec.request_attribute_1 := p_record.INCIDENT_ATTRIBUTE_1;
578 l_sr_rec.request_attribute_2 := p_record.INCIDENT_ATTRIBUTE_2;
579 l_sr_rec.request_attribute_3 := p_record.INCIDENT_ATTRIBUTE_3;
580 l_sr_rec.request_attribute_4 := p_record.INCIDENT_ATTRIBUTE_4;
581 l_sr_rec.request_attribute_5 := p_record.INCIDENT_ATTRIBUTE_5;
582 l_sr_rec.request_attribute_6 := p_record.INCIDENT_ATTRIBUTE_6;
583 l_sr_rec.request_attribute_7 := p_record.INCIDENT_ATTRIBUTE_7;
584 l_sr_rec.request_attribute_8 := p_record.INCIDENT_ATTRIBUTE_8;
585 l_sr_rec.request_attribute_9 := p_record.INCIDENT_ATTRIBUTE_9;
586 l_sr_rec.request_attribute_10 := p_record.INCIDENT_ATTRIBUTE_10;
587 l_sr_rec.request_attribute_11 := p_record.INCIDENT_ATTRIBUTE_11;
588 l_sr_rec.request_attribute_12 := p_record.INCIDENT_ATTRIBUTE_12;
589 l_sr_rec.request_attribute_13 := p_record.INCIDENT_ATTRIBUTE_13;
590 l_sr_rec.request_attribute_14 := p_record.INCIDENT_ATTRIBUTE_14;
591 l_sr_rec.request_attribute_15 := p_record.INCIDENT_ATTRIBUTE_15;
592 l_sr_rec.request_context := p_record.INCIDENT_CONTEXT;
593
594 --ER 3949138
595 l_sr_rec.external_attribute_1 := p_record.EXTERNAL_ATTRIBUTE_1;
596 l_sr_rec.external_attribute_2 := p_record.EXTERNAL_ATTRIBUTE_2;
597 l_sr_rec.external_attribute_3 := p_record.EXTERNAL_ATTRIBUTE_3;
598 l_sr_rec.external_attribute_4 := p_record.EXTERNAL_ATTRIBUTE_4;
599 l_sr_rec.external_attribute_5 := p_record.EXTERNAL_ATTRIBUTE_5;
600 l_sr_rec.external_attribute_6 := p_record.EXTERNAL_ATTRIBUTE_6;
601 l_sr_rec.external_attribute_7 := p_record.EXTERNAL_ATTRIBUTE_7;
602 l_sr_rec.external_attribute_8 := p_record.EXTERNAL_ATTRIBUTE_8;
603 l_sr_rec.external_attribute_9 := p_record.EXTERNAL_ATTRIBUTE_9;
604 l_sr_rec.external_attribute_10 := p_record.EXTERNAL_ATTRIBUTE_10;
605 l_sr_rec.external_attribute_11 := p_record.EXTERNAL_ATTRIBUTE_11;
606 l_sr_rec.external_attribute_12 := p_record.EXTERNAL_ATTRIBUTE_12;
607 l_sr_rec.external_attribute_13 := p_record.EXTERNAL_ATTRIBUTE_13;
608 l_sr_rec.external_attribute_14 := p_record.EXTERNAL_ATTRIBUTE_14;
609 l_sr_rec.external_attribute_15 := p_record.EXTERNAL_ATTRIBUTE_15;
610 l_sr_rec.external_context := p_record.EXTERNAL_CONTEXT;
611
612 /* 11.5.10 incident address changes - 3430663 */
613 l_sr_rec.incident_location_id := p_record.incident_location_id;
614 l_sr_rec.incident_address := p_record.incident_address;
615 l_sr_rec.incident_city := p_record.incident_city;
616 l_sr_rec.incident_state := p_record.incident_state;
617 l_sr_rec.incident_country := p_record.incident_country;
618 l_sr_rec.incident_province := p_record.incident_province;
619 l_sr_rec.incident_postal_code := p_record.incident_postal_code;
620 l_sr_rec.incident_county := p_record.incident_county;
621 l_sr_rec.incident_location_type := p_record.incident_location_type;
622
623 -- See if Credit Card profile is set?
624 --Bug 4496299
625 /*
626 l_profile_value := fnd_profile.value('JTM_CREDIT_CARD_ENABLED');
627 IF l_profile_value = 'Y' THEN
628 IF p_record.CREDIT_CARD_NUMBER IS NOT NULL THEN
629 open c_credit_card(b_incident_id => p_record.incident_id);
630 fetch c_credit_card into r_credit_card;
631 IF c_credit_card%found AND (
632 NVL(r_credit_card.CREDIT_CARD_NUMBER,FND_API.G_MISS_CHAR) <> NVL(p_record.CREDIT_CARD_NUMBER,FND_API.G_MISS_CHAR) OR
633 NVL(r_credit_card.CREDIT_CARD_TYPE_CODE,FND_API.G_MISS_CHAR) <> NVL(p_record.CREDIT_CARD_TYPE_CODE,FND_API.G_MISS_CHAR) OR
634 NVL(r_credit_card.CREDIT_CARD_EXPIRATION_DATE,FND_API.G_MISS_DATE) <> NVL(p_record.CREDIT_CARD_EXPIRATION_DATE,FND_API.G_MISS_DATE) OR
635 NVL(r_credit_card.CREDIT_CARD_HOLDER_FNAME,FND_API.G_MISS_CHAR) <> NVL(p_record.CREDIT_CARD_HOLDER_FNAME,FND_API.G_MISS_CHAR) OR
636 NVL(r_credit_card.CREDIT_CARD_HOLDER_MNAME,FND_API.G_MISS_CHAR) <> NVL(p_record.CREDIT_CARD_HOLDER_MNAME,FND_API.G_MISS_CHAR) OR
637 NVL(r_credit_card.CREDIT_CARD_HOLDER_LNAME,FND_API.G_MISS_CHAR) <> NVL(p_record.CREDIT_CARD_HOLDER_LNAME,FND_API.G_MISS_CHAR) OR
638 NVL(r_credit_card.CREDIT_CARD_ID,FND_API.G_MISS_NUM) <> NVL(p_record.CREDIT_CARD_ID,FND_API.G_MISS_NUM))
639 THEN
640 l_decript_value := CS_SERVICEREQUEST_PUB.CC_DECODE(p_record.CREDIT_CARD_NUMBER);
641 l_sr_rec.cc_number := l_decript_value;
642 l_sr_rec.cc_type_code := p_record.CREDIT_CARD_TYPE_CODE;
643 l_sr_rec.cc_expiration_date := p_record.CREDIT_CARD_EXPIRATION_DATE;
644 l_sr_rec.cc_first_name := p_record.CREDIT_CARD_HOLDER_FNAME;
645 l_sr_rec.cc_middle_name := p_record.CREDIT_CARD_HOLDER_MNAME;
646 l_sr_rec.cc_last_name := p_record.CREDIT_CARD_HOLDER_LNAME;
647 l_sr_rec.cc_id := p_record.CREDIT_CARD_ID;
648 END IF;
649 END IF;
650 END IF;
651 */
652
653 -- CS 11.5.9 Change New column
654 l_sr_rec.last_update_program_code := 'CSL_LAPTOP';
655
656
657 -- Finally the update itself.
658 -- Api Modified for CS 11.5.9 Uptake Version changed to 3.0
659 CS_ServiceRequest_PUB.Update_ServiceRequest
660 ( p_api_version => 3.0
661 , p_init_msg_list => FND_API.G_TRUE
662 , p_commit => FND_API.G_TRUE
663 , x_return_status => x_return_status
664 , x_msg_count => l_msg_count
665 , x_msg_data => l_msg_data
666 , p_request_id => p_record.incident_id
667 , p_object_version_number => l_ovn
668 , p_last_updated_by => l_user_id
669 , p_last_update_date => sysdate
670 , p_service_request_rec => l_sr_rec
671 , p_notes => l_notes_tab
672 , p_contacts => l_contacts_tab
673 , p_resp_id => to_number(fnd_profile.value('CSL_SR_CREATE_RESP'))
674 , x_workflow_process_id => l_workflow_id
675 , x_interaction_id => l_interaction_id
676 );
677
678 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
679 /*** exception occurred in API -> return errmsg ***/
680 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
681 (
682 p_api_error => TRUE
683 );
684 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
685 jtm_message_log_pkg.Log_Msg
686 ( v_object_id => p_record.incident_id
687 , v_object_name => g_object_name
688 , v_message => 'Leaving ' || g_object_name || '.APPLY_UPDATE'
689 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
690 END IF;
691 RETURN;
692 END IF;
693
694 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
695 jtm_message_log_pkg.Log_Msg
696 ( v_object_id => p_record.incident_id
697 , v_object_name => g_object_name
698 , v_message => 'Leaving ' || g_object_name || '.APPLY_UPDATE'
699 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
700 END IF;
701
702 EXCEPTION WHEN OTHERS THEN
703 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
704 jtm_message_log_pkg.Log_Msg
705 ( v_object_id => p_record.incident_id
706 , v_object_name => g_object_name
707 , v_message => 'Exception occurred in APPLY_UPDATE:' || fnd_global.local_chr(10) || sqlerrm
708 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
709 END IF;
710
711 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_UPDATE', sqlerrm);
712 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
713 (
714 p_api_error => TRUE
715 );
716
717 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
718 jtm_message_log_pkg.Log_Msg
719 ( v_object_id => p_record.incident_id
720 , v_object_name => g_object_name
721 , v_message => 'Leaving ' || g_object_name || '.APPLY_UPDATE'
722 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
723 END IF;
724
725 x_return_status := FND_API.G_RET_STS_ERROR;
726 END APPLY_UPDATE;
727
728 /***
729 This procedure is called by APPLY_CLIENT_CHANGES for every record in in-queue that needs to be processed.
730 ***/
731 PROCEDURE APPLY_RECORD
732 (
733 p_record IN c_incident%ROWTYPE,
734 p_error_msg OUT NOCOPY VARCHAR2,
735 x_return_status IN OUT NOCOPY VARCHAR2
736 ) IS
737 l_rc BOOLEAN;
738 l_access_id NUMBER;
739 BEGIN
740 /*** initialize return status and message list ***/
741 x_return_status := FND_API.G_RET_STS_SUCCESS;
742 FND_MSG_PUB.INITIALIZE;
743
744 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
745 jtm_message_log_pkg.Log_Msg
746 ( v_object_id => p_record.incident_id
747 , v_object_name => g_object_name
748 , v_message => 'Entering ' || g_object_name || '.APPLY_RECORD'
749 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
750 END IF;
751
752 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
753 jtm_message_log_pkg.Log_Msg
754 ( v_object_id => p_record.incident_id
755 , v_object_name => g_object_name
756 , v_message => 'Processing INCIDENT_ID = ' || p_record.incident_id || fnd_global.local_chr(10) ||
757 'DMLTYPE = ' || p_record.dmltype$$
758 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
759 END IF;
760
761 IF p_record.dmltype$$='I' THEN
762 -- Process insert
763 APPLY_INSERT
764 (
765 p_record,
766 p_error_msg,
767 x_return_status
768 );
769 ELSIF p_record.dmltype$$='U' THEN
770 -- Process update
771 APPLY_UPDATE
772 (
773 p_record,
774 p_error_msg,
775 x_return_status
776 );
777 ELSIF p_record.dmltype$$='D' THEN
778 -- Process delete; not supported for this entity
779 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
780 jtm_message_log_pkg.Log_Msg
781 ( v_object_id => p_record.incident_id
782 , v_object_name => g_object_name
783 , v_message => 'Delete is not supported for this entity'
784 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
785 END IF;
786
787 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
788 (
789 p_message => 'CSL_DML_OPERATION'
790 , p_token_name1 => 'DML'
791 , p_token_value1 => p_record.dmltype$$
792 );
793
794 x_return_status := FND_API.G_RET_STS_ERROR;
795 ELSE
796 -- invalid dml type
797 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
798 jtm_message_log_pkg.Log_Msg
799 ( v_object_id => p_record.incident_id
800 , v_object_name => g_object_name
801 , v_message => 'Invalid DML type: ' || p_record.dmltype$$
802 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
803 END IF;
804
805 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
806 (
807 p_message => 'CSL_DML_OPERATION'
808 , p_token_name1 => 'DML'
809 , p_token_value1 => p_record.dmltype$$
810 );
811
812 x_return_status := FND_API.G_RET_STS_ERROR;
813 END IF;
814
815 IF p_record.dmltype$$ = 'U' AND x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
816 l_access_id := jtm_hook_util_pkg.get_acc_id(
817 p_acc_table_name => 'CSL_CS_INCIDENTS_ALL_ACC',
818 p_resource_id => JTM_HOOK_UTIL_PKG.get_resource_id( p_record.clid$$cs ),
819 p_pk1_name => 'INCIDENT_ID',
820 p_pk1_num_value => p_record.INCIDENT_ID
821 );
822 l_rc := CSL_SERVICEL_WRAPPER_PKG.AUTONOMOUS_MARK_DIRTY(
823 P_PUB_ITEM => g_pub_name,
824 P_ACCESSID => l_access_id,
825 P_RESOURCEID => JTM_HOOK_UTIL_PKG.get_resource_id( p_record.clid$$cs ),
826 P_DML => 'U',
827 P_TIMESTAMP => sysdate
828 );
829 END IF;
830
831 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
832 jtm_message_log_pkg.Log_Msg
833 ( v_object_id => p_record.incident_id
834 , v_object_name => g_object_name
835 , v_message => 'Leaving ' || g_object_name || '.APPLY_RECORD'
836 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
837 END IF;
838 EXCEPTION WHEN OTHERS THEN
839 /*** defer record when any process exception occurs ***/
840 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
841 jtm_message_log_pkg.Log_Msg
842 ( v_object_id => p_record.incident_id
843 , v_object_name => g_object_name
844 , v_message => 'Exception occurred in APPLY_RECORD:' || fnd_global.local_chr(10) || sqlerrm
845 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
846 END IF;
847
848 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_RECORD', sqlerrm);
849 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
850 (
851 p_api_error => TRUE
852 );
853
854 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
855 jtm_message_log_pkg.Log_Msg
856 ( v_object_id => p_record.incident_id
857 , v_object_name => g_object_name
858 , v_message => 'Leaving ' || g_object_name || '.APPLY_RECORD'
859 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
860 END IF;
861
862 x_return_status := FND_API.G_RET_STS_ERROR;
863 END APPLY_RECORD;
864
865 /***
866 This procedure is called by CSL_SERVICEL_WRAPPER_PKG when publication item CSL_CS_INCIDENTS_ALL_VL
867 is dirty. This happens when a mobile field service device executed DML on an updatable table and did
868 a fast sync. This procedure will insert the data that came from mobile into the backend tables using
869 public APIs.
870 ***/
871 PROCEDURE APPLY_CLIENT_CHANGES
872 (
873 p_user_name IN VARCHAR2,
874 p_tranid IN NUMBER,
875 p_debug_level IN NUMBER,
876 x_return_status IN OUT NOCOPY VARCHAR2
877 ) IS
878
879 l_process_status VARCHAR2(1);
880 l_error_msg VARCHAR2(4000);
881 BEGIN
882 g_debug_level := p_debug_level;
883 x_return_status := FND_API.G_RET_STS_SUCCESS;
884
885 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
886 jtm_message_log_pkg.Log_Msg
887 ( v_object_id => null
888 , v_object_name => g_object_name
889 , v_message => 'Entering ' || g_object_name || '.Apply_Client_Changes'
890 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
891 END IF;
892
893 /*** loop through CSL_CS_INCIDENTS_ALL_VL records in inqueue ***/
894 FOR r_incident IN c_incident( p_user_name, p_tranid) LOOP
895
896 /*** apply record ***/
897 APPLY_RECORD
898 (
899 r_incident
900 , l_error_msg
901 , l_process_status
902 );
903
904 /*** was record processed successfully? ***/
905 IF l_process_status = FND_API.G_RET_STS_SUCCESS THEN
906 /*** Yes -> delete record from inqueue ***/
907 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
908 jtm_message_log_pkg.Log_Msg
909 ( v_object_id => r_incident.incident_id
910 , v_object_name => g_object_name
911 , v_message => 'Record successfully processed, deleting from inqueue'
912 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
913 END IF;
914
915 CSL_SERVICEL_WRAPPER_PKG.DELETE_RECORD
916 (
917 p_user_name,
918 p_tranid,
919 r_incident.seqno$$,
920 r_incident.incident_id,
921 g_object_name,
922 g_pub_name,
923 l_error_msg,
924 l_process_status
925 );
926 /*** was delete successful? ***/
927 IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
928 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
929 jtm_message_log_pkg.Log_Msg
930 ( v_object_id => r_incident.incident_id
931 , v_object_name => g_object_name
932 , v_message => 'Deleting from inqueue failed'
933 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
934 END IF;
935 END IF;
936
937
938 FOR r_contacts IN c_contact( r_incident.incident_id, p_tranid, p_user_name ) LOOP
939 /* Delete matching contact record(s) */
940 CSL_SERVICEL_WRAPPER_PKG.DELETE_RECORD
941 (
942 p_user_name,
943 p_tranid,
944 r_contacts.seqno$$,
945 r_contacts.SR_CONTACT_POINT_ID,
946 g_object_name,
947 'CSL_CS_HZ_SR_CONTACT_PTS',
948 l_error_msg,
949 l_process_status
950 );
951 /*** was delete successful? ***/
952 IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
953 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
954 jtm_message_log_pkg.Log_Msg
955 ( v_object_id => r_contacts.SR_CONTACT_POINT_ID
956 , v_object_name => g_object_name
957 , v_message => 'Deleting from inqueue failed'
958 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
959 END IF;
960 END IF;
961 END LOOP;
962 ELSIF l_process_Status <> FND_API.G_RET_STS_SUCCESS THEN
963 /*** Record was not applied successfully -> defer and reject records ***/
964 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
965 jtm_message_log_pkg.Log_Msg
966 ( v_object_id => r_incident.incident_id
967 , v_object_name => g_object_name
968 , v_message => 'Record not processed successfully, deferring and rejecting records'
969 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
970 END IF;
971
972 CSL_SERVICEL_WRAPPER_PKG.DEFER_RECORD
973 (
974 p_user_name
975 , p_tranid
976 , r_incident.seqno$$
977 , r_incident.incident_id
978 , g_object_name
979 , g_pub_name
980 , l_error_msg
981 , l_process_status
982 , r_incident.dmltype$$
983 );
984
985 FOR r_contacts IN c_contact( r_incident.incident_id, p_tranid, p_user_name ) LOOP
986 /* Defer matching contact record(s) */
987 CSL_SERVICEL_WRAPPER_PKG.DEFER_RECORD
988 (
989 p_user_name,
990 p_tranid,
991 r_contacts.seqno$$,
992 r_contacts.SR_CONTACT_POINT_ID,
993 g_object_name,
994 'CSL_CS_HZ_SR_CONTACT_PTS',
995 l_error_msg,
996 l_process_status,
997 'I'
998 );
999 END LOOP;
1000 END IF;
1001
1002 END LOOP;
1003
1004 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
1005 jtm_message_log_pkg.Log_Msg
1006 ( v_object_id => null
1007 , v_object_name => g_object_name
1008 , v_message => 'Leaving ' || g_object_name || '.Apply_Client_Changes'
1009 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
1010 END IF;
1011
1012 EXCEPTION WHEN OTHERS THEN
1013 /*** catch and log exceptions ***/
1014 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
1015 jtm_message_log_pkg.Log_Msg
1016 ( v_object_id => null
1017 , v_object_name => g_object_name
1018 , v_message => 'Exception occurred in APPLY_CLIENT_CHANGES:' || fnd_global.local_chr(10) || sqlerrm
1019 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
1020 END IF;
1021 x_return_status := FND_API.G_RET_STS_ERROR;
1022 END APPLY_CLIENT_CHANGES;
1023
1024 END CSL_SERVICE_REQUESTS_PKG;