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