[Home] [Help]
PACKAGE BODY: APPS.CS_SR_CHILD_AUDIT_PKG
Source
1 Package body CS_SR_CHILD_AUDIT_PKG AS
2 /* $Header: cssraudb.pls 120.5.12010000.3 2008/09/25 17:11:16 bkanimoz ship $*/
3
4
5 /***************
6 Custom Function corrsponds to the business events published by SR child entities.
7 This custom function will be called from the subscriptions of the update/create/delete events for the
8 following SR child entities.
9 1. SR Tasks
10 2. SR Notes
11 3. SR Solution Links.
12 4. SR Task Assignments
13
14 ***************/
15
16 -- Added for status update --anmukher -- 10/16/03
17 FUNCTION GET_STATUS_FLAG( p_incident_status_id IN NUMBER)
18 RETURN VARCHAR2;
19
20 FUNCTION CS_SR_Audit_ChildEntities
21 (P_subscription_guid IN RAW,
22 P_event IN OUT NOCOPY WF_EVENT_T) RETURN VARCHAR2 IS
23
24 l_event_key NUMBER ;
25 l_event_name VARCHAR2(240) := p_event.getEventName();
26 l_updated_entity_code VARCHAR2(40) ;
27 l_updated_entity_id NUMBER;
28 l_entity_update_date DATE ;
29 l_entity_activity_code VARCHAR2(30) ;
30 l_source_object_code VARCHAR2(240);
31 l_source_object_id NUMBER ;
32 l_incident_id NUMBER ;
33 l_user_id NUMBER := null ;
34 l_audit_id NUMBER ;
35 l_return_status VARCHAR2(30);
36 l_msg_count NUMBER ;
37 l_msg_data VARCHAR2(2000) ;
38
39 -- Cursors to get Task Details
40
41 CURSOR get_Task_Dtls (p_task_id IN NUMBER) IS
42 SELECT task_id ,
43 last_update_date,
44 creation_date ,
45 last_updated_by,
46 source_object_type_code ,
47 source_object_id
48 FROM jtf_tasks_vl
49 WHERE task_id = p_task_id ;
50
51 -- Cursor to get Task Assignment Details
52
53 CURSOR get_TaskAssign_dtls (p_task_assignment_id IN NUMBER) IS
54 SELECT task_assignment_id ,
55 t.task_id ,
56 t.source_object_type_code,
57 t.source_object_id,
58 ta.assignee_role,
59 ta.last_update_date,
60 ta.creation_date,
61 ta.last_updated_by
62 FROM jtf_task_all_assignments ta,
63 jtf_tasks_vl t
64 WHERE ta.task_id = t.task_id
65 AND ta.task_assignment_id = p_task_assignment_id ;
66
67 -- Cursor to get Note details
68
69 CURSOR get_Note_Dtls (p_jtf_note_id IN NUMBER) IS
70 SELECT jtf_note_id ,
71 source_object_code source_object_type_code,
72 source_object_id ,
73 creation_date ,
74 last_update_date,
75 last_updated_by
76 FROM jtf_notes_vl
77 WHERE jtf_note_id = p_jtf_note_id ;
78
79 -- Cursor to get ecsalation details
80
81 CURSOR get_esc_details (p_task_id IN NUMBER) IS
82 SELECT t.task_id ,
83 t.source_objecT_type_code task_objecT_type_code,
84 t.source_object_id task_source_object_id ,
85 t.escalation_level,
86 t.creation_date,
87 t.last_update_date,
88 t.last_updated_by,
89 r.reference_code ,
90 r.object_type_code ref_object_type_code,
91 r.object_id ref_object_id ,
92 r.task_reference_id
93 FROM jtf_tasks_vl t,
94 jtf_task_references_vl r
95 WHERE t.task_id = p_task_id
96 AND t.task_id = r.task_id ;
97
98 -- Cursor to get only escalation details on deleting the esc reference
99
100 CURSOR get_del_esc_dtls (p_task_id IN NUMBER) IS
101 SELECT t.task_id ,
102 t.source_objecT_type_code task_objecT_type_code,
103 t.source_object_id task_source_object_id ,
104 t.escalation_level,
105 t.creation_date,
106 t.last_update_date,
107 t.last_updated_by
108 FROM jtf_tasks_vl t
109 WHERE t.task_id = p_task_id ;
110 --Added by bkanimoz
111 CURSOR get_inc_details (p_incident_id IN NUMBER) IS
112 SELECT incident_number ,
113 incident_type_id,
114 incident_status_id,
115 incident_severity_id,
116 incident_urgency_id,
117 incident_owner_id,
118 owner_group_id,
119 customer_id, last_updated_by, summary
120 FROM cs_incidents_all_vl
121 WHERE incident_id =p_incident_id ;
122
123 e_auditing_child_updates EXCEPTION ;
124
125 l_object_version_number cs_incidents_all_b.object_version_number%TYPE;
126 l_notes CS_SERVICEREQUEST_PVT.notes_table;
127 l_contacts CS_SERVICEREQUEST_PVT.contacts_table;
128 l_service_request_rec CS_ServiceRequest_pvt.service_request_rec_type;
129 l_sr_update_out_rec CS_ServiceRequest_pvt.sr_update_out_rec_type;
130 l_incident_number VARCHAR2(64); -- Bug 7040105
131 l_incident_type_id NUMBER;
132 l_incident_status_id NUMBER;
133 l_incident_severity_id NUMBER;
134 l_incident_urgency_id NUMBER;
135 l_incident_owner_id NUMBER;
136 l_owner_group_id NUMBER;
137 l_customer_id NUMBER;
138 l_last_updated_by NUMBER;
139 l_summary VARCHAR2(240) ;
140 l_api_version number;
141 l_workflow_process_id NUMBER;
142
143 BEGIN
144
145 /** Detect the event raised and determine necessary parameters depending on the event **/
146
147 /**** SR Solution Link Events ****/
148
149 -- SR Solution link updated event
150
151 IF l_event_name = 'oracle.apps.cs.knowledge.SolutionLink.Updated' THEN
152 l_source_object_code := p_event.GetValueForParameter('OBJECT_CODE');
153 l_source_object_id := p_event.GetValueForParameter('OBJECT_ID');
154
155 IF ((l_source_object_code = 'SR') AND (l_source_object_id IS NOT NULL ) ) THEN
156 l_event_key := p_event.GetValueForParameter('LINK_ID') ;
157
158 l_updated_entity_code := 'SR_SOLUTION_LINK';
159 l_updated_entity_id := l_event_key ;
160 l_entity_update_date := p_event.GetValueForParameter('EVENT_DATE');
161 l_entity_activity_code := 'U';
162 l_incident_id := l_source_object_id ;
163 l_user_id := p_event.GetValueForParameter('USER_ID');
164 END IF ;
165
166 -- SR Solution link Created event
167
168 ELSIF l_event_name = 'oracle.apps.cs.knowledge.SolutionLinked' THEN
169 l_source_object_code := p_event.GetValueForParameter('OBJECT_CODE');
170 l_source_object_id := p_event.GetValueForParameter('OBJECT_ID');
171
172 IF ((l_source_object_code = 'SR') AND (l_source_object_id IS NOT NULL ) ) THEN
173
174 l_event_key := p_event.GetValueForParameter('LINK_ID') ;
175
176 l_updated_entity_id := l_event_key ;
177 l_updated_entity_code := 'SR_SOLUTION_LINK';
178 l_entity_update_date := p_event.GetValueForParameter('EVENT_DATE');
179 l_entity_activity_code := 'C';
180 l_incident_id := l_source_object_id ;
181 l_user_id := p_event.GetValueForParameter('USER_ID');
182 END IF ;
183
184 /**** SR Task Events ****/
185
186 -- SR Task Created event
187
188 ELSIF l_event_name = 'oracle.apps.jtf.cac.task.createTask' THEN
189
190 -- l_source_object_code := p_event.GetValueForParameter('SOURCE_OBJECT_TYPE_CODE');
191 -- l_source_object_id := p_event.GetValueForParameter('SOURCE_OBJECT_ID');
192
193
194 l_event_key := p_event.GetValueForParameter('TASK_ID');
195
196 FOR get_task_dtls_rec IN get_task_dtls(l_event_key)
197
198 LOOP
199 IF ((get_task_dtls_rec.source_object_type_code = 'SR') AND
200 (get_task_dtls_rec.source_object_id IS NOT NULL ) ) THEN
201
202 l_updated_entity_id := l_event_key ;
203 l_updated_entity_code := 'SR_TASK' ;
204 l_entity_update_date := get_task_dtls_rec.creation_date;
205 l_entity_activity_code := 'C';
206 l_incident_id := get_task_dtls_rec.source_object_id ;
207 l_user_id := get_task_dtls_rec.last_updated_by ;
208 END IF ;
209 END LOOP;
210
211 -- SR Task Updated event
212
213 ELSIF l_event_name = 'oracle.apps.jtf.cac.task.updateTask' THEN
214 -- l_source_object_code := p_event.GetValueForParameter('SOURCE_OBJECT_TYPE_CODE');
215 -- l_source_object_id := p_event.GetValueForParameter('SOURCE_OBJECT_ID');
216
217
218 l_event_key := p_event.GetValueForParameter('TASK_ID');
219
220 FOR get_task_dtls_rec IN get_task_dtls(l_event_key)
221
222 LOOP
223 IF ((get_task_dtls_rec.source_object_type_code = 'SR') AND
224 (get_task_dtls_rec.source_object_id IS NOT NULL ) ) THEN
225
226 l_updated_entity_id := l_event_key ;
227 l_updated_entity_code := 'SR_TASK' ;
228 l_entity_update_date := get_task_dtls_rec.last_update_date ;
229 l_entity_activity_code := 'U' ;
230 l_incident_id := get_task_dtls_rec.source_object_id ;
231 l_user_id := get_task_dtls_rec.last_updated_by ;
232 END IF ;
233 END LOOP ;
234
235 -- SR Task deleted event
236
237 ELSIF l_event_name = 'oracle.apps.jtf.cac.task.deleteTask' THEN
238
239 l_event_key := p_event.GetValueForParameter('TASK_ID');
240
241 FOR get_task_dtls_rec IN get_task_dtls(l_event_key)
242
243 LOOP
244 IF ((get_task_dtls_rec.source_object_type_code = 'SR') AND
245 (get_task_dtls_rec.source_object_id IS NOT NULL )) THEN
246
247 l_updated_entity_id := l_event_key ;
248 l_updated_entity_code := 'SR_TASK' ;
249 l_entity_update_date := get_task_dtls_rec.last_update_date ;
250 l_entity_activity_code := 'D' ;
251 l_incident_id := get_task_dtls_rec.source_object_id ;
252 l_user_id := get_task_dtls_rec.last_updated_by ;
253
254 END IF ;
255 END LOOP ;
256
257 /**** SR Task Assignments Events ****/
258
259 -- SR Task Assignment Created event
260
261 ELSIF l_event_name = 'oracle.apps.jtf.cac.task.createTaskAssignment' THEN
262
263 l_event_key := p_event.GetValueForParameter('TASK_ASSIGNMENT_ID') ;
264
265 FOR get_taskAssign_dtls_rec IN get_TaskAssign_dtls (l_event_key)
266
267 LOOP
268 IF ((get_taskAssign_dtls_rec.source_object_type_code = 'SR') AND
269 (get_taskAssign_dtls_rec.source_object_id IS NOT NULL ) AND
270 (get_taskAssign_dtls_rec.assignee_role = 'ASSIGNEE') ) THEN
271
272 l_updated_entity_id := get_taskAssign_dtls_rec.task_id ;
273 l_updated_entity_code := 'SR_TASK' ;
274 l_entity_update_date := get_taskAssign_dtls_rec.creation_date ;
275 l_entity_activity_code := 'U';
276 l_incident_id := get_taskAssign_dtls_rec.source_object_id ;
277 l_user_id := get_taskAssign_dtls_rec.last_updated_by ;
278 END IF ;
279 END LOOP ;
280
281 -- SR Task Assignment Updated event
282
283 ELSIF l_event_name = 'oracle.apps.jtf.cac.task.updateTaskAssignment' THEN
284
285
286 l_event_key := p_event.GetValueForParameter('TASK_ASSIGNMENT_ID') ;
287
288 FOR get_taskAssign_dtls_rec IN get_TaskAssign_dtls (l_event_key)
289
290 LOOP
291 IF ((get_taskAssign_dtls_rec.source_object_type_code = 'SR') AND
292 (get_taskAssign_dtls_rec.source_object_id IS NOT NULL ) AND
293 (get_taskAssign_dtls_rec.assignee_role = 'ASSIGNEE') ) THEN
294
295 l_updated_entity_id := get_taskAssign_dtls_rec.task_id ;
296 l_updated_entity_code := 'SR_TASK' ;
297 l_entity_update_date := get_taskAssign_dtls_rec.last_update_date ;
298 l_entity_activity_code := 'U' ;
299 l_incident_id := get_taskAssign_dtls_rec.source_object_id ;
300 l_user_id := get_taskAssign_dtls_rec.last_updated_by ;
301 END IF ;
302 END LOOP ;
303
304 -- SR Task Assignment Deleted event
305
306 ELSIF l_event_name = 'oracle.apps.jtf.cac.task.deleteTaskAssignment' THEN
307
308 l_event_key := p_event.GetValueForParameter('TASK_ASSIGNMENT_ID') ;
309
310 FOR get_taskAssign_dtls_rec IN get_TaskAssign_dtls (l_event_key)
311
312 LOOP
313 IF ((get_taskAssign_dtls_rec.source_object_type_code = 'SR') AND
314 (get_taskAssign_dtls_rec.source_object_id IS NOT NULL ) AND
315 (get_taskAssign_dtls_rec.assignee_role = 'ASSIGNEE') ) THEN
316
317 l_updated_entity_id := get_taskAssign_dtls_rec.task_id ;
318 l_updated_entity_code := 'SR_TASK' ;
319 l_entity_update_date := get_taskAssign_dtls_rec.last_update_date ;
320 l_entity_activity_code := 'U' ;
321 l_incident_id := get_taskAssign_dtls_rec.source_object_id ;
322 l_user_id := get_taskAssign_dtls_rec.last_updated_by ;
323 END IF ;
324 END LOOP ;
325
326 /**** SR Notes Events ****/
327
328 -- SR Note Created event
329
330 ELSIF l_event_name = 'oracle.apps.jtf.cac.notes.create' THEN
331
332 l_source_object_code := p_event.GetValueForParameter('SOURCE_OBJECT_CODE');
333 l_source_object_id := p_event.GetValueForParameter('SOURCE_OBJECT_ID');
334
335 IF ((l_source_object_code = 'SR') AND
336 (l_source_object_id IS NOT NULL ) ) THEN
337
338 l_event_key := p_event.GetValueForParameter('NOTE_ID') ;
339
340 FOR get_note_dtls_rec IN get_note_dtls (l_event_key)
341
342 LOOP
343 l_updated_entity_id := l_event_key ;
344 l_updated_entity_code := 'SR_NOTE' ;
345 l_entity_update_date := get_note_dtls_rec.creation_date ;
346 l_entity_activity_code := 'C' ;
347 l_incident_id := l_source_object_id ;
348 l_user_id := get_note_dtls_rec.last_updated_by ;
349 END LOOP ;
350 END IF ;
351
352 -- SR Note Updated event
353
354 ELSIF l_event_name = 'oracle.apps.jtf.cac.notes.update' THEN
355
356 l_source_object_code := p_event.GetValueForParameter('SOURCE_OBJECT_CODE');
357 l_source_object_id := p_event.GetValueForParameter('SOURCE_OBJECT_ID');
358
359 IF ((l_source_object_code = 'SR') AND
360 (l_source_object_id IS NOT NULL ) ) THEN
361
362 l_event_key := p_event.GetValueForParameter('NOTE_ID') ;
363
364 FOR get_note_dtls_rec IN get_note_dtls (l_event_key)
365
366 LOOP
367 l_updated_entity_id := l_event_key ;
368 l_updated_entity_code := 'SR_NOTE' ;
369 l_entity_update_date := get_note_dtls_rec.last_update_date ;
370 l_entity_activity_code := 'U' ;
371 l_incident_id := l_source_object_id ;
372 l_user_id := get_note_dtls_rec.last_updated_by ;
373 END LOOP ;
374 END IF ;
375
376 -- SR Note Deleted event
377
378 ELSIF l_event_name = 'oracle.apps.jtf.cac.notes.delete' THEN
379
380 l_source_object_code := p_event.GetValueForParameter('SOURCE_OBJECT_TYPE_CODE');
381 l_source_object_id := p_event.GetValueForParameter('SOURCE_OBJECT_ID');
382
383 IF ((l_source_object_code = 'SR') AND
384 (l_source_object_id IS NOT NULL ) ) THEN
385
386 l_event_key := p_event.GetValueForParameter('NOTE_ID') ;
387
388 FOR get_note_dtls_rec IN get_note_dtls (l_event_key)
389
390 LOOP
391 l_updated_entity_id := l_event_key ;
392 l_updated_entity_code := 'SR_NOTE' ;
393 l_entity_update_date := get_note_dtls_rec.last_update_date ;
394 l_entity_activity_code := 'D' ;
395 l_incident_id := l_source_object_id ;
396 l_user_id := get_note_dtls_rec.last_updated_by ;
397 END LOOP ;
398 END IF ;
399
400 /**** SR Task Escalation Events ****/
401
402 -- SR task Escalation Created event
403
404 ELSIF l_event_name = 'oracle.apps.jtf.cac.escalation.createEscalation' THEN
405
406 -- l_source_object_code := p_event.GetValueForParameter('SOURCE_OBJECT_TYPE_CODE');
407 -- l_source_object_id := p_event.GetValueForParameter('SOURCE_OBJECT_ID');
408
409 l_event_key := p_event.GetValueForParameter('TASK_ID');
410
411 FOR get_esc_details_rec IN get_esc_details(l_event_key)
412
413 LOOP
414 IF ((get_esc_details_rec.ref_object_type_code = 'SR') AND
415 (get_esc_details_rec.ref_object_id IS NOT NULL ) ) THEN
416
417 l_updated_entity_id := get_esc_details_rec.task_id ;
418 l_updated_entity_code := 'SR_ESCALATION' ;
419 l_entity_update_date := get_esc_details_rec.creation_date;
420 l_entity_activity_code := 'C';
421 l_incident_id := get_esc_details_rec.ref_object_id ;
422 l_user_id := get_esc_details_rec.last_updated_by ;
423 END IF ;
424 END LOOP;
425
426 -- SR task Escalation Updated event
427
428 ELSIF l_event_name = 'oracle.apps.jtf.cac.escalation.updateEscalation' THEN
429 -- l_source_object_code := p_event.GetValueForParameter('SOURCE_OBJECT_TYPE_CODE');
430 -- l_source_object_id := p_event.GetValueForParameter('SOURCE_OBJECT_ID');
431
432 l_event_key := p_event.GetValueForParameter('TASK_ID');
433
434 FOR get_esc_details_rec IN get_esc_details(l_event_key)
435
436 LOOP
437 IF ((get_esc_details_rec.ref_object_type_code = 'SR') AND
438 (get_esc_details_rec.ref_object_id IS NOT NULL ) ) THEN
439
440 l_updated_entity_id := get_esc_details_rec.task_id ;
441 l_updated_entity_code := 'SR_ESCALATION' ;
442 l_entity_update_date := get_esc_details_rec.last_update_date ;
443 l_entity_activity_code := 'U' ;
444 l_incident_id := get_esc_details_rec.ref_object_id ;
445 l_user_id := get_esc_details_rec.last_updated_by ;
446 END IF ;
447 END LOOP ;
448
449 -- SR task Escalation Deleted event
450
451 ELSIF l_event_name = 'oracle.apps.jtf.cac.escalation.deleteEscReference' THEN
452
453 l_event_key := p_event.GetValueForParameter('TASK_ID');
454 l_source_object_code := p_event.GetValueForParameter('OBJECT_TYPE_CODE');
455 l_source_object_id := p_event.GetValueForParameter('OBJECT_ID');
456
457
458 -- IF ((get_esc_details_rec.ref_object_type_code = 'SR') AND
459 -- (get_esc_details_rec.ref_object_id IS NOT NULL )) THEN
460
461 IF ((l_source_object_code = 'SR') AND (l_source_object_id IS NOT NULL) ) THEN
462
463 FOR get_del_esc_dtls_rec IN get_del_esc_dtls(l_event_key)
464 LOOP
465
466 -- l_updated_entity_id := get_del_esc_dtls_rec.task_id ;
467 l_updated_entity_id := l_event_key ;
468 l_updated_entity_code := 'SR_ESCALATION' ;
469 l_entity_update_date := get_del_esc_dtls_rec.last_update_date ;
470 l_entity_activity_code := 'D' ;
471 l_incident_id := l_source_object_id ;
472 -- l_incident_id := get_del_esc_dtls_rec.ref_object_id ;
473 l_user_id := get_del_esc_dtls_rec.last_updated_by ;
474
475 END LOOP ;
476 END IF ;
477
478 END IF ; -- end if detect event
479
480
481 -- Call SR Child Audit API to create entry in SR audit table.
482 IF ((l_updated_entity_code IS NOT NULL) AND (l_incident_id IS NOT NULL) AND
483 (l_updated_entity_id IS NOT NULL) ) THEN
484
485 --Bug fix 6275359.Commented by bkanimoz on 06-Aug-2007
486 --Whenever the Notes are updated call the Service Request update API so that it sends notification and inturn do the auditing
487
488
489 CS_SR_AUDIT_CHILD
490 (P_incident_id => l_incident_id,
491 P_updated_entity_code => l_updated_entity_code,
492 p_updated_entity_id => l_updated_entity_id ,
493 p_entity_update_date => l_entity_update_date,
494 p_entity_activity_code => l_entity_activity_code ,
495 p_update_program_code => 'EVENT_SUBSCRIPTION',
496 p_user_id => l_user_id ,
497 x_audit_id => l_audit_id,
498 x_return_status => l_return_status,
499 x_msg_count => l_msg_count ,
500 x_msg_data => l_msg_data );
501
502 IF l_return_status <> 'S' THEN
503 RAISE e_auditing_child_updates ;
504 END IF;
505
506 --Bug fix 6158138.Added by bkanimoz on 06-Aug-2007
507 --start
508
509 CS_ServiceRequest_pvt.initialize_rec(l_service_request_rec);
510 /*
511 select object_version_number
512 into l_object_version_number
513 from cs_incidents_all_b
514 where incident_id =l_incident_id;
515
516
517 CS_ServiceRequest_pvt.Update_ServiceRequest(
518 p_api_version => 4.0,
519 p_init_msg_list => 'T',
520 p_commit => 'T',
521 p_validation_level => fnd_api.g_valid_level_none,
522 x_return_status => l_return_status,
523 x_msg_count => l_msg_count,
524 x_msg_data => l_msg_data,
525 p_request_id => l_incident_id,
526 p_object_version_number => l_object_version_number,
527 p_resp_appl_id => NULL,
528 p_resp_id => NULL,
529 p_last_updated_by => l_user_id,
530 p_last_update_login => NULL,
531 p_last_update_date => sysdate,
532 p_service_request_rec => l_service_request_rec,
533 p_update_desc_flex => 'F',
534 p_notes => l_notes,
535 p_contacts => l_contacts,
536 p_audit_comments => NULL,
537 p_called_by_workflow => 'F',
538 p_workflow_process_id => NULL,
539 x_sr_update_out_rec => l_sr_update_out_rec
540 );
541 */
542 --Added by bkanimoz
543 --insert into rm_tmp values ('before calling', 'Raise_SR_EVENT' || l_incident_number, rm_tmp_seq.nextval);
544
545 --bug 7389202
546 If l_event_name = 'oracle.apps.jtf.cac.notes.create'
547 or l_event_name = 'oracle.apps.jtf.cac.notes.update'
548 or l_event_name = 'oracle.apps.jtf.cac.notes.delete'
549 THEN
550
551 OPEN get_inc_details(l_incident_id) ;
552 FETCH get_inc_details INTO l_incident_number ,
553 l_incident_type_id ,
554 l_incident_status_id ,
555 l_incident_severity_id ,
556 l_incident_urgency_id ,
557 l_incident_owner_id ,
558 l_owner_group_id ,
559 l_customer_id ,
560 l_last_updated_by ,
561 l_summary ;
562 CLOSE get_inc_details;
563
564
565 l_Service_Request_rec.type_id :=l_incident_type_id ;
566 l_Service_Request_rec.status_id :=l_incident_status_id ;
567 l_Service_Request_rec.severity_id :=l_incident_severity_id ;
568 l_Service_Request_rec.urgency_id :=l_incident_urgency_id ;
569 l_Service_Request_rec.owner_id :=l_incident_owner_id ;
570 l_Service_Request_rec.owner_group_id :=l_owner_group_id ;
571 l_Service_Request_rec.customer_id :=l_customer_id ;
572 l_Service_Request_rec.summary := l_summary ;
573
574
575 CS_WF_EVENT_PKG.RAISE_SERVICEREQUEST_EVENT(
576 p_api_version => l_api_version,
577 p_init_msg_list => 'T',
578 p_commit => 'T',
579 p_validation_level => fnd_api.g_valid_level_none,
580 p_event_code => 'UPDATE_SERVICE_REQUEST',
581 p_incident_number => l_incident_number,
582 p_user_id => l_last_updated_by,
583 p_resp_id => NULL,
584 p_resp_appl_id => NULL,
585 p_old_sr_rec => l_Service_Request_rec,
586 p_new_sr_rec => l_Service_Request_rec,
587 p_contacts_table => l_contacts,
588 p_link_rec => NULL, -- using default value
589 p_wf_process_id => NULL,
590 p_owner_id => NULL, -- using default value
591 p_wf_manual_launch => 'N' , -- using default value
592 x_wf_process_id => l_workflow_process_id,
593 x_return_status => l_return_status,
594 x_msg_count => l_msg_count,
595 x_msg_data => l_msg_data );
596
597 IF l_return_status <> 'S' THEN
598 RAISE e_auditing_child_updates ;
599 END IF;
600 --end
601
602
603 END IF ;
604 END IF;
605
606
607 RETURN 'SUCCESS';
608
609 EXCEPTION
610 WHEN e_auditing_child_updates THEN
611 WF_CORE.CONTEXT('CS_SR_CHILD_AUDIT_PKG', 'CS_SR_Audit_ChildEntities',
612 l_event_name , p_subscription_guid);
613 WF_EVENT.setErrorInfo(p_event, 'WARNING');
614 return 'WARNING';
615 WHEN others THEN
616 WF_CORE.CONTEXT('CS_SR_CHILD_AUDIT_PKG', 'CS_SR_Audit_ChildEntities',
617 l_event_name , p_subscription_guid);
618 WF_EVENT.setErrorInfo(p_event, 'WARNING');
619 return 'WARNING';
620 END CS_SR_Audit_ChildEntities ;
621
622 /*
623 Modification History
624 Date Name Desc
625 -------- --------- ------------------------------------------------------------
626 03/25/05 smisra Bug 4028675
627 Modified this procedure and set old unassigned indicator
628 based on old owner and old group if
629 p_owner_status_update_flag is OWNER otherwise set it's
630 value from service request record.
631 08/15/05 smisra Set contract_number col using contract_number from SR Rec
632 instead of contract_id column.
633 10/14/05 smisra Bug 4674121
634 Added two parameters p_old_inc_responded_by_date and
635 p_old_incident_resolved_date if this procedure is called
636 by update_status procedure then set
637 old_incident_responded_by_date and old_incident_resolved_date
638 of audit record from the parameter values otherwise set these
639 cols from service request record.
640 11/13/07 gasankar Bug 6621820
641 Handled the locking issue when trying to update the
642 CS_INCIDENTS_ALL_B
643 */
644 PROCEDURE CS_SR_AUDIT_CHILD
645 (P_incident_id IN NUMBER,
646 P_updated_entity_code IN VARCHAR2 ,
647 p_updated_entity_id IN NUMBER ,
648 p_entity_update_date IN DATE ,
649 p_entity_activity_code IN VARCHAR2 ,
650 p_status_id IN NUMBER DEFAULT NULL,
651 p_old_status_id IN NUMBER DEFAULT NULL,
652 p_closed_date IN DATE DEFAULT NULL,
653 p_old_closed_date IN DATE DEFAULT NULL,
654 p_owner_id IN NUMBER DEFAULT NULL,
655 p_old_owner_id IN NUMBER DEFAULT NULL,
656 p_owner_group_id IN NUMBER DEFAULT NULL,
657 p_old_owner_group_id IN NUMBER DEFAULT NULL,
658 p_resource_type IN VARCHAR2 DEFAULT NULL,
659 p_old_resource_type IN VARCHAR2 DEFAULT NULL,
660 p_owner_status_upd_flag IN VARCHAR2 DEFAULT 'NONE',
661 p_update_program_code IN VARCHAR2 DEFAULT 'NONE',
662 p_user_id IN NUMBER DEFAULT NULL,
663 p_old_inc_responded_by_date IN DATE DEFAULT NULL,
664 p_old_incident_resolved_date IN DATE DEFAULT NULL,
665 x_audit_id OUT NOCOPY NUMBER,
666 x_return_status OUT NOCOPY VARCHAR2,
667 x_msg_count OUT NOCOPY NUMBER,
668 x_msg_data OUT NOCOPY VARCHAR2
669 ) IS
670
671 l_audit_vals_rec CS_ServiceRequest_PVT.sr_audit_rec_type ;
672 l_incident_last_modified_date DATE ;
673 l_service_request_rec CS_INCIDENTS_ALL_B%ROWTYPE;
674 --l_return_status VARCHAR2(30) ;
675
676 l_status_flag VARCHAR2(1);
677
678 SR_Lock_Row EXCEPTION; --gasankar
679
680 PRAGMA EXCEPTION_INIT( SR_Lock_Row, -54 ); --gasankar
681
682 CURSOR get_sr_date IS
683 SELECT *
684 FROM cs_incidents_all_b
685 WHERE incident_id = p_incident_id
686 FOR UPDATE NOWAIT ;
687
688 e_create_audit_failed EXCEPTION ;
689 l_text varchar2(240);
690 l_num number;
691
692 BEGIN
693
694 --Added initilization of return status to Success --anmukher --09/12/03
695 x_return_status := FND_API.G_RET_STS_SUCCESS;
696
697 -- Compare SR last Modified date with the entity date and update if entity date is later
698
699 OPEN get_sr_date ;
700 FETCH get_sr_date INTO l_service_request_rec ;
701 -- CLOSE get_sr_date;
702
703 IF p_entity_update_date > l_service_request_rec.incident_last_modified_date THEN
704
705 UPDATE cs_incidents_all_b
706 SET incident_last_modified_date = NVL(p_entity_update_date,sysdate) ,
707 last_update_date = sysdate,
708 last_updated_by = NVL(p_user_id , FND_GLOBAL.user_id)
709 WHERE incident_id = p_incident_id ;
710
711
712 -- Added by aneemuch on 11/02/2004,
713 -- Added code to update cs_incidents_all_tl table. This is due to change in the intermedia index structure
714 -- in 11.5.10 where JTF_NOTES will be part of the intermedia index itself. So any change in notes
715 -- will require conc prog to sunc intermedia index to be run.
716
717 IF p_updated_entity_code = 'SR_NOTE' THEN
718 UPDATE cs_incidents_all_tl
719 SET text_index = 'A'
720 WHERE incident_id = p_incident_id;
721 END IF;
722
723 -- End of code changes by aneemuch 11/02/2004
724
725 END IF ;
726
727
728 -- Populate Audit Record structure
729
730 -- Initialize Audit record
731
732 CS_ServiceRequest_PVT.Initialize_audit_rec( p_sr_audit_record => l_audit_vals_rec) ;
733
734
735 -- Added for owner update --anmukher --10/16/03
736 IF p_owner_status_upd_flag = 'OWNER' THEN
737
738 IF ( (p_owner_id IS NOT NULL AND p_old_owner_id IS NULL)
739 OR (p_owner_id IS NULL AND p_old_owner_id IS NOT NULL)
740 OR (p_owner_id IS NOT NULL AND p_old_owner_id IS NOT NULL
741 AND p_owner_id <> p_old_owner_id)
742 ) THEN
743
744 l_audit_vals_rec.CHANGE_INCIDENT_OWNER_FLAG := 'Y';
745 l_audit_vals_rec.INCIDENT_OWNER_ID := p_owner_id;
746 l_audit_vals_rec.OLD_INCIDENT_OWNER_ID := p_old_owner_id;
747 ELSE
748 l_audit_vals_rec.CHANGE_INCIDENT_OWNER_FLAG := 'N';
749 l_audit_vals_rec.OLD_INCIDENT_OWNER_ID := l_service_request_rec.incident_owner_id;
750 l_audit_vals_rec.INCIDENT_OWNER_ID := l_service_request_rec.incident_owner_id;
751 END IF;
752
753 IF ((p_owner_group_id IS NOT NULL AND p_old_owner_group_id IS NULL)
754 OR (p_owner_group_id IS NULL AND p_old_owner_group_id IS NOT NULL)
755 OR (p_owner_group_id IS NOT NULL AND p_old_owner_group_id IS NOT NULL
756 AND p_owner_group_id <> p_old_owner_group_id)
757 ) THEN
758
759 l_audit_vals_rec.change_group_flag := 'Y';
760 l_audit_vals_rec.group_id := p_owner_group_id ;
761 l_audit_vals_rec.old_group_id := p_old_owner_group_id ;
762 ELSE
763 l_audit_vals_rec.change_group_flag := 'N';
764 l_audit_vals_rec.old_group_id := l_service_request_rec.OWNER_GROUP_ID ;
765 l_audit_vals_rec.group_id := l_service_request_rec.OWNER_GROUP_ID ;
766 END IF;
767
768 IF (p_owner_group_id IS NOT NULL AND p_owner_group_id<>FND_API.G_MISS_NUM)
769 THEN
770 l_audit_vals_rec.group_type:='RS_GROUP';
771 ELSE
772 l_audit_vals_rec.group_type:=NULL;
773 END IF;
774
775 IF ((l_audit_vals_rec.group_type IS NOT NULL
776 AND l_audit_vals_rec.old_group_type IS NULL)
777 OR (l_audit_vals_rec.group_type IS NULL
778 AND l_audit_vals_rec.old_group_type IS NOT NULL))
779 THEN
780 l_audit_vals_rec.change_group_type_flag := 'Y';
781 l_audit_vals_rec.old_group_type := l_service_request_rec.group_type ;
782 ELSE
783 l_audit_vals_rec.change_group_type_flag := 'N';
784 l_audit_vals_rec.old_group_type := l_service_request_rec.group_type ;
785 l_audit_vals_rec.group_type := l_service_request_rec.group_type ;
786 END IF;
787
788 IF ((p_resource_type IS NOT NULL AND p_old_resource_type IS NULL)
789 OR (p_resource_type IS NULL AND p_old_resource_type IS NOT NULL)
790 OR (p_resource_type IS NOT NULL AND p_old_resource_type IS NOT NULL
791 AND p_resource_type <> p_old_resource_type)) THEN
792
793 l_audit_vals_rec.change_resource_type_flag := 'Y';
794 l_audit_vals_rec.resource_type := p_resource_type ;
795 l_audit_vals_rec.old_resource_type := p_old_resource_type ;
796 ELSE
797 l_audit_vals_rec.change_resource_type_flag := 'N';
798 l_audit_vals_rec.old_resource_type := l_service_request_rec.RESOURCE_TYPE ;
799 l_audit_vals_rec.resource_type := l_service_request_rec.RESOURCE_TYPE ;
800 END IF;
801
802 l_audit_vals_rec.old_unassigned_indicator := cs_servicerequest_util.get_unassigned_indicator(
803 p_old_owner_id, p_old_owner_group_id);
804
805 ELSE
806
807 l_audit_vals_rec.change_incident_owner_flag := 'N';
808 l_audit_vals_rec.incident_owner_id := l_service_request_rec.incident_owner_id;
809 l_audit_vals_rec.old_incident_owner_id := l_service_request_rec.incident_owner_id;
810
811 l_audit_vals_rec.CHANGE_GROUP_FLAG := 'N';
812 l_audit_vals_rec.GROUP_ID := l_service_request_rec.OWNER_GROUP_ID;
813 l_audit_vals_rec.OLD_GROUP_ID := l_service_request_rec.OWNER_GROUP_ID;
814 l_audit_vals_rec.CHANGE_GROUP_TYPE_FLAG := 'N';
815 l_audit_vals_rec.group_type := l_service_request_rec.group_type;
816 l_audit_vals_rec.old_group_type := l_service_request_rec.group_type;
817
818 l_audit_vals_rec.CHANGE_RESOURCE_TYPE_FLAG := 'N';
819 l_audit_vals_rec.RESOURCE_TYPE := l_service_request_rec.RESOURCE_TYPE;
820 l_audit_vals_rec.OLD_RESOURCE_TYPE := l_service_request_rec.RESOURCE_TYPE;
821
822 l_audit_vals_rec.old_unassigned_indicator := l_service_request_rec.unassigned_indicator;
823
824 END IF; -- IF p_owner_status_upd_flag = 'OWNER'
825
826 l_audit_vals_rec.CHANGE_ASSIGNED_TIME_FLAG := 'N';
827 l_audit_vals_rec.OWNER_ASSIGNED_TIME := l_service_request_rec.OWNER_ASSIGNED_TIME;
828 l_audit_vals_rec.old_OWNER_ASSIGNED_TIME := l_service_request_rec.OWNER_ASSIGNED_TIME;
829 l_audit_vals_rec.change_product_revision_flag := 'N';
830 l_audit_vals_rec.product_revision := l_service_request_rec.product_revision;
831 l_audit_vals_rec.old_product_revision := l_service_request_rec.product_revision;
832 l_audit_vals_rec.CHANGE_COMP_VER_FLAG := 'N';
833 l_audit_vals_rec.component_version := l_service_request_rec.component_version;
834 l_audit_vals_rec.old_component_version := l_service_request_rec.component_version;
835 l_audit_vals_rec.CHANGE_SUBCOMP_VER_FLAG := 'N';
836 l_audit_vals_rec.subcomponent_version := l_service_request_rec.subcomponent_version;
837 l_audit_vals_rec.old_subcomponent_version := l_service_request_rec.subcomponent_version;
838 l_audit_vals_rec.change_platform_id_flag := 'N';
839 l_audit_vals_rec.platform_id := l_service_request_rec.platform_id;
840 l_audit_vals_rec.old_platform_id := l_service_request_rec.platform_id;
841 l_audit_vals_rec.CHANGE_PLAT_VER_ID_FLAG := 'N';
842 l_audit_vals_rec.platform_version_id := l_service_request_rec.platform_version_id;
843 l_audit_vals_rec.old_platform_version_id := l_service_request_rec.platform_version_id;
844 l_audit_vals_rec.CHANGE_CUSTOMER_PRODUCT_FLAG := 'N';
845 l_audit_vals_rec.customer_product_id := l_service_request_rec.customer_product_id;
846 l_audit_vals_rec.old_customer_product_id := l_service_request_rec.customer_product_id;
847 l_audit_vals_rec.CHANGE_CP_COMPONENT_ID_FLAG := 'N';
848 l_audit_vals_rec.cp_component_id := l_service_request_rec.cp_component_id;
849 l_audit_vals_rec.old_cp_component_id := l_service_request_rec.cp_component_id;
850 l_audit_vals_rec.CHANGE_CP_COMP_VER_ID_FLAG := 'N';
851 l_audit_vals_rec.cp_component_version_id := l_service_request_rec.cp_component_version_id;
852 l_audit_vals_rec.old_cp_component_version_id := l_service_request_rec.cp_component_version_id;
853 l_audit_vals_rec.change_cp_subcomponent_id_flag := 'N';
854 l_audit_vals_rec.cp_subcomponent_id := l_service_request_rec.cp_subcomponent_id;
855 l_audit_vals_rec.old_cp_subcomponent_id := l_service_request_rec.cp_subcomponent_id;
856 l_audit_vals_rec.CHANGE_CP_SUBCOMP_VER_ID_FLAG := 'N';
857 l_audit_vals_rec.cp_subcomponent_version_id := l_service_request_rec.cp_subcomponent_version_id;
858 l_audit_vals_rec.old_cp_subcomponent_version_id := l_service_request_rec.cp_subcomponent_version_id;
859 l_audit_vals_rec.change_cp_revision_id_flag := 'N';
860 l_audit_vals_rec.cp_revision_id := l_service_request_rec.cp_revision_id;
861 l_audit_vals_rec.old_cp_revision_id := l_service_request_rec.cp_revision_id;
862 l_audit_vals_rec.change_inv_item_revision := 'N';
863 l_audit_vals_rec.inv_item_revision := l_service_request_rec.inv_item_revision;
864 l_audit_vals_rec.old_inv_item_revision := l_service_request_rec.inv_item_revision;
865 l_audit_vals_rec.change_inv_component_id := 'N';
866 l_audit_vals_rec.inv_component_id := l_service_request_rec.inv_component_id;
867 l_audit_vals_rec.old_inv_component_id := l_service_request_rec.inv_component_id;
868 l_audit_vals_rec.change_inv_component_version := 'N';
869 l_audit_vals_rec.inv_component_version := l_service_request_rec.inv_component_version;
870 l_audit_vals_rec.old_inv_component_version := l_service_request_rec.inv_component_version;
871 l_audit_vals_rec.change_inv_subcomponent_id := 'N';
872 l_audit_vals_rec.inv_subcomponent_id := l_service_request_rec.inv_subcomponent_id;
873 l_audit_vals_rec.old_inv_subcomponent_id := l_service_request_rec.inv_subcomponent_id;
874 l_audit_vals_rec.CHANGE_INV_SUBCOMP_VERSION := 'N';
875 l_audit_vals_rec.inv_subcomponent_version := l_service_request_rec.inv_subcomponent_version;
876 l_audit_vals_rec.old_inv_subcomponent_version := l_service_request_rec.inv_subcomponent_version;
877 l_audit_vals_rec.CHANGE_INVENTORY_ITEM_FLAG := 'N';
878 l_audit_vals_rec.inventory_item_id := l_service_request_rec.inventory_item_id;
879 l_audit_vals_rec.old_inventory_item_id := l_service_request_rec.inventory_item_id;
880 l_audit_vals_rec.CHANGE_PLATFORM_ORG_ID_FLAG := 'N';
881 l_audit_vals_rec.inv_platform_org_id := l_service_request_rec.inv_platform_org_id;
882 l_audit_vals_rec.old_inv_platform_org_id := l_service_request_rec.inv_platform_org_id;
883 l_audit_vals_rec.CHANGE_RESOLUTION_FLAG := 'N';
884 l_audit_vals_rec.EXPECTED_RESOLUTION_DATE := l_service_request_rec.expected_resolution_date;
885 l_audit_vals_rec.OLD_EXPECTED_RESOLUTION_DATE := l_service_request_rec.expected_resolution_date;
886 l_audit_vals_rec.CHANGE_OBLIGATION_FLAG := 'N';
887 l_audit_vals_rec.obligation_date := l_service_request_rec.obligation_date;
888 l_audit_vals_rec.old_obligation_date := l_service_request_rec.obligation_date;
889 l_audit_vals_rec.CHANGE_SITE_FLAG := 'N';
890 l_audit_vals_rec.site_id := l_service_request_rec.site_id;
891 l_audit_vals_rec.old_site_id := l_service_request_rec.site_id;
892 l_audit_vals_rec.CHANGE_TERRITORY_ID_FLAG := 'N';
893 l_audit_vals_rec.territory_id := l_service_request_rec.territory_id;
894 l_audit_vals_rec.old_territory_id := l_service_request_rec.territory_id;
895 l_audit_vals_rec.CHANGE_BILL_TO_FLAG := 'N';
896 l_audit_vals_rec.bill_to_contact_id := l_service_request_rec.bill_to_contact_id;
897 l_audit_vals_rec.old_bill_to_contact_id := l_service_request_rec.bill_to_contact_id;
898 l_audit_vals_rec.CHANGE_SHIP_TO_FLAG := 'N';
899 l_audit_vals_rec.ship_to_contact_id := l_service_request_rec.ship_to_contact_id;
900 l_audit_vals_rec.old_ship_to_contact_id := l_service_request_rec.ship_to_contact_id;
901
902 -- Added for status update --anmukher --10/14/03
903 IF p_owner_status_upd_flag = 'STATUS' THEN
904 l_audit_vals_rec.change_incident_status_flag := 'Y';
905 l_audit_vals_rec.incident_status_id := p_status_id;
906 l_audit_vals_rec.old_incident_status_id := p_old_status_id;
907 ELSE
908 l_audit_vals_rec.change_incident_status_flag := 'N';
909 l_audit_vals_rec.incident_status_id := l_service_request_rec.incident_status_id;
910 l_audit_vals_rec.old_incident_status_id := l_service_request_rec.incident_status_id;
911 END IF;
912
913 -- Added for status update -- anmukher -- 10/16/03
914 IF p_owner_status_upd_flag = 'STATUS' THEN
915 l_audit_vals_rec.OLD_INCIDENT_RESOLVED_DATE := p_old_incident_resolved_date;
916 l_audit_vals_rec.OLD_INC_RESPONDED_BY_DATE := p_old_inc_responded_by_date;
917 IF NVL(p_closed_date, TO_DATE('09/09/0099', 'dd/mm/yyyy')) <>
918 NVL(p_old_closed_date, TO_DATE('09/09/0099', 'dd/mm/yyyy')) THEN
919 l_audit_vals_rec.CHANGE_CLOSE_DATE_FLAG := 'Y';
920 l_audit_vals_rec.CLOSE_DATE := p_closed_date;
921 l_audit_vals_rec.OLD_CLOSE_DATE := p_old_closed_date;
922 ELSE
923 l_audit_vals_rec.CHANGE_CLOSE_DATE_FLAG := 'N';
924 l_audit_vals_rec.CLOSE_DATE := l_service_request_rec.close_date;
925 l_audit_vals_rec.OLD_CLOSE_DATE := l_service_request_rec.close_date;
926 END IF;
927 ELSE
928 l_audit_vals_rec.CHANGE_CLOSE_DATE_FLAG := 'N';
929 l_audit_vals_rec.CLOSE_DATE := l_service_request_rec.close_date;
930 l_audit_vals_rec.OLD_CLOSE_DATE := l_service_request_rec.close_date;
931 l_audit_vals_rec.OLD_INCIDENT_RESOLVED_DATE := l_service_request_rec.INCIDENT_RESOLVED_DATE;
932 l_audit_vals_rec.OLD_INC_RESPONDED_BY_DATE := l_service_request_rec.INC_RESPONDED_BY_DATE;
933 END IF;
934
935 l_audit_vals_rec.CHANGE_INCIDENT_TYPE_FLAG := 'N';
936 l_audit_vals_rec.INCIDENT_TYPE_ID := l_service_request_rec.incident_type_id;
937 l_audit_vals_rec.OLD_INCIDENT_TYPE_ID := l_service_request_rec.incident_type_id;
938 l_audit_vals_rec.CHANGE_INCIDENT_SEVERITY_FLAG := 'N';
939 l_audit_vals_rec.INCIDENT_SEVERITY_ID := l_service_request_rec.incident_severity_id ;
940 l_audit_vals_rec.OLD_INCIDENT_SEVERITY_ID := l_service_request_rec.incident_severity_id ;
941 l_audit_vals_rec.CHANGE_INCIDENT_DATE_FLAG := 'N';
942 l_audit_vals_rec.INCIDENT_DATE := l_service_request_rec.incident_date;
943 l_audit_vals_rec.OLD_INCIDENT_DATE := l_service_request_rec.incident_date;
944 l_audit_vals_rec.CHANGE_PLAT_VER_ID_FLAG := 'N';
945 l_audit_vals_rec.PLATFORM_VERSION_ID := l_service_request_rec.PLATFORM_VERSION_ID;
946 l_audit_vals_rec.OLD_PLATFORM_VERSION_ID := l_service_request_rec.PLATFORM_VERSION_ID;
947 l_audit_vals_rec.CHANGE_LANGUAGE_ID_FLAG := 'N';
948 l_audit_vals_rec.LANGUAGE_ID := l_service_request_rec.LANGUAGE_ID;
949 l_audit_vals_rec.OLD_LANGUAGE_ID := l_service_request_rec.LANGUAGE_ID;
950 l_audit_vals_rec.CHANGE_INV_ORGANIZATION_FLAG := 'N';
951 l_audit_vals_rec.INV_ORGANIZATION_ID := l_service_request_rec.inv_organization_id;
952 l_audit_vals_rec.OLD_INV_ORGANIZATION_ID := l_service_request_rec.inv_organization_id;
953
954 -- Added for auditing of status update -- anmukher -- 10/16/03
955 IF p_owner_status_upd_flag = 'STATUS' THEN
956 l_status_flag := get_status_flag(p_status_id);
957 IF NVL(l_status_flag, '@') <> NVL(l_service_request_rec.STATUS_FLAG, '@') THEN
958 l_audit_vals_rec.CHANGE_STATUS_FLAG := 'Y';
959 l_audit_vals_rec.STATUS_FLAG := l_status_flag;
960 ELSE
961 l_audit_vals_rec.CHANGE_STATUS_FLAG := 'N';
962 l_audit_vals_rec.STATUS_FLAG := l_service_request_rec.STATUS_FLAG;
963 END IF;
964 ELSE
965 l_audit_vals_rec.CHANGE_STATUS_FLAG := 'N';
966 l_audit_vals_rec.STATUS_FLAG := l_service_request_rec.STATUS_FLAG;
967 END IF;
968
969 IF p_update_program_code <> 'NONE' THEN
970 l_audit_vals_rec.LAST_UPDATE_PROGRAM_CODE := p_update_program_code;
971 l_audit_vals_rec.OLD_LAST_UPDATE_PROGRAM_CODE := l_service_request_rec.LAST_UPDATE_PROGRAM_CODE;
972 ELSE
973 l_audit_vals_rec.LAST_UPDATE_PROGRAM_CODE := l_service_request_rec.LAST_UPDATE_PROGRAM_CODE;
974 l_audit_vals_rec.OLD_LAST_UPDATE_PROGRAM_CODE := l_service_request_rec.LAST_UPDATE_PROGRAM_CODE;
975 END IF ;
976
977
978 l_audit_vals_rec.OLD_STATUS_FLAG := l_service_request_rec.STATUS_FLAG;
979 l_audit_vals_rec.CHANGE_PRIMARY_CONTACT_FLAG := 'N';
980 l_audit_vals_rec.PRIMARY_CONTACT_ID := l_service_request_rec.PRIMARY_CONTACT_ID;
981 l_audit_vals_rec.OLD_PRIMARY_CONTACT_ID := l_service_request_rec.PRIMARY_CONTACT_ID;
982 l_audit_vals_rec.CUSTOMER_ID := l_service_request_rec.CUSTOMER_ID;
983 l_audit_vals_rec.OLD_CUSTOMER_ID := l_service_request_rec.CUSTOMER_ID;
984 l_audit_vals_rec.BILL_TO_SITE_USE_ID := l_service_request_rec.BILL_TO_SITE_USE_ID;
985 l_audit_vals_rec.OLD_BILL_TO_SITE_USE_ID := l_service_request_rec.BILL_TO_SITE_USE_ID;
986 l_audit_vals_rec.EMPLOYEE_ID := l_service_request_rec.EMPLOYEE_ID;
987 l_audit_vals_rec.OLD_EMPLOYEE_ID := l_service_request_rec.EMPLOYEE_ID;
988 l_audit_vals_rec.SHIP_TO_SITE_USE_ID := l_service_request_rec.SHIP_TO_SITE_USE_ID;
989 l_audit_vals_rec.OLD_SHIP_TO_SITE_USE_ID := l_service_request_rec.SHIP_TO_SITE_USE_ID;
990 l_audit_vals_rec.PROBLEM_CODE := l_service_request_rec.PROBLEM_CODE;
991 l_audit_vals_rec.OLD_PROBLEM_CODE := l_service_request_rec.PROBLEM_CODE;
992 l_audit_vals_rec.ACTUAL_RESOLUTION_DATE := l_service_request_rec.ACTUAL_RESOLUTION_DATE;
993 l_audit_vals_rec.OLD_ACTUAL_RESOLUTION_DATE := l_service_request_rec.ACTUAL_RESOLUTION_DATE;
994 l_audit_vals_rec.INSTALL_SITE_USE_ID := l_service_request_rec.INSTALL_SITE_USE_ID;
995 l_audit_vals_rec.OLD_INSTALL_SITE_USE_ID := l_service_request_rec.INSTALL_SITE_USE_ID;
996 l_audit_vals_rec.CURRENT_SERIAL_NUMBER := l_service_request_rec.CURRENT_SERIAL_NUMBER;
997 l_audit_vals_rec.OLD_CURRENT_SERIAL_NUMBER := l_service_request_rec.CURRENT_SERIAL_NUMBER;
998 l_audit_vals_rec.SYSTEM_ID := l_service_request_rec.SYSTEM_ID;
999 l_audit_vals_rec.OLD_SYSTEM_ID := l_service_request_rec.SYSTEM_ID;
1000 l_audit_vals_rec.INCIDENT_ATTRIBUTE_1 := l_service_request_rec.INCIDENT_ATTRIBUTE_1;
1001 l_audit_vals_rec.OLD_INCIDENT_ATTRIBUTE_1 := l_service_request_rec.INCIDENT_ATTRIBUTE_1;
1002 l_audit_vals_rec.INCIDENT_ATTRIBUTE_2 := l_service_request_rec.INCIDENT_ATTRIBUTE_2;
1003 l_audit_vals_rec.OLD_INCIDENT_ATTRIBUTE_1 := l_service_request_rec.INCIDENT_ATTRIBUTE_1;
1004 l_audit_vals_rec.INCIDENT_ATTRIBUTE_3 := l_service_request_rec.INCIDENT_ATTRIBUTE_3;
1005 l_audit_vals_rec.OLD_INCIDENT_ATTRIBUTE_1 := l_service_request_rec.INCIDENT_ATTRIBUTE_1;
1006 l_audit_vals_rec.INCIDENT_ATTRIBUTE_4 := l_service_request_rec.INCIDENT_ATTRIBUTE_4;
1007 l_audit_vals_rec.OLD_INCIDENT_ATTRIBUTE_1 := l_service_request_rec.INCIDENT_ATTRIBUTE_1;
1008 l_audit_vals_rec.INCIDENT_ATTRIBUTE_5 := l_service_request_rec.INCIDENT_ATTRIBUTE_5;
1009 l_audit_vals_rec.OLD_INCIDENT_ATTRIBUTE_1 := l_service_request_rec.INCIDENT_ATTRIBUTE_1;
1010 l_audit_vals_rec.INCIDENT_ATTRIBUTE_6 := l_service_request_rec.INCIDENT_ATTRIBUTE_6;
1011 l_audit_vals_rec.OLD_INCIDENT_ATTRIBUTE_1 := l_service_request_rec.INCIDENT_ATTRIBUTE_1;
1012 l_audit_vals_rec.INCIDENT_ATTRIBUTE_7 := l_service_request_rec.INCIDENT_ATTRIBUTE_7;
1013 l_audit_vals_rec.OLD_INCIDENT_ATTRIBUTE_1 := l_service_request_rec.INCIDENT_ATTRIBUTE_1;
1014 l_audit_vals_rec.INCIDENT_ATTRIBUTE_7 := l_service_request_rec.INCIDENT_ATTRIBUTE_7;
1015 l_audit_vals_rec.OLD_INCIDENT_ATTRIBUTE_1 := l_service_request_rec.INCIDENT_ATTRIBUTE_1;
1016 l_audit_vals_rec.INCIDENT_ATTRIBUTE_8 := l_service_request_rec.INCIDENT_ATTRIBUTE_8;
1017 l_audit_vals_rec.OLD_INCIDENT_ATTRIBUTE_1 := l_service_request_rec.INCIDENT_ATTRIBUTE_1;
1018 l_audit_vals_rec.INCIDENT_ATTRIBUTE_9 := l_service_request_rec.INCIDENT_ATTRIBUTE_9;
1019 l_audit_vals_rec.OLD_INCIDENT_ATTRIBUTE_1 := l_service_request_rec.INCIDENT_ATTRIBUTE_1;
1020 l_audit_vals_rec.INCIDENT_ATTRIBUTE_10 := l_service_request_rec.INCIDENT_ATTRIBUTE_10;
1021 l_audit_vals_rec.OLD_INCIDENT_ATTRIBUTE_1 := l_service_request_rec.INCIDENT_ATTRIBUTE_1;
1022 l_audit_vals_rec.INCIDENT_ATTRIBUTE_11 := l_service_request_rec.INCIDENT_ATTRIBUTE_11;
1023 l_audit_vals_rec.OLD_INCIDENT_ATTRIBUTE_1 := l_service_request_rec.INCIDENT_ATTRIBUTE_1;
1024 l_audit_vals_rec.INCIDENT_ATTRIBUTE_12 := l_service_request_rec.INCIDENT_ATTRIBUTE_12;
1025 l_audit_vals_rec.OLD_INCIDENT_ATTRIBUTE_1 := l_service_request_rec.INCIDENT_ATTRIBUTE_1;
1026 l_audit_vals_rec.INCIDENT_ATTRIBUTE_13 := l_service_request_rec.INCIDENT_ATTRIBUTE_13;
1027 l_audit_vals_rec.OLD_INCIDENT_ATTRIBUTE_1 := l_service_request_rec.INCIDENT_ATTRIBUTE_1;
1028 l_audit_vals_rec.INCIDENT_ATTRIBUTE_14 := l_service_request_rec.INCIDENT_ATTRIBUTE_14;
1029 l_audit_vals_rec.OLD_INCIDENT_ATTRIBUTE_1 := l_service_request_rec.INCIDENT_ATTRIBUTE_1;
1030 l_audit_vals_rec.INCIDENT_ATTRIBUTE_15 := l_service_request_rec.INCIDENT_ATTRIBUTE_15;
1031 l_audit_vals_rec.OLD_INCIDENT_ATTRIBUTE_1 := l_service_request_rec.INCIDENT_ATTRIBUTE_1;
1032 l_audit_vals_rec.INCIDENT_CONTEXT := l_service_request_rec.INCIDENT_CONTEXT;
1033 l_audit_vals_rec.OLD_INCIDENT_CONTEXT := l_service_request_rec.INCIDENT_CONTEXT;
1034 l_audit_vals_rec.RESOLUTION_CODE := l_service_request_rec.RESOLUTION_CODE;
1035 l_audit_vals_rec.OLD_RESOLUTION_CODE := l_service_request_rec.RESOLUTION_CODE;
1036 l_audit_vals_rec.ORIGINAL_ORDER_NUMBER := l_service_request_rec.ORIGINAL_ORDER_NUMBER;
1037 l_audit_vals_rec.OLD_ORIGINAL_ORDER_NUMBER := l_service_request_rec.ORIGINAL_ORDER_NUMBER;
1038 l_audit_vals_rec.PURCHASE_ORDER_NUMBER := l_service_request_rec.PURCHASE_ORDER_NUM;
1039 l_audit_vals_rec.OLD_PURCHASE_ORDER_NUMBER := l_service_request_rec.PURCHASE_ORDER_NUM;
1040 l_audit_vals_rec.PUBLISH_FLAG := l_service_request_rec.PUBLISH_FLAG;
1041 l_audit_vals_rec.OLD_PUBLISH_FLAG := l_service_request_rec.PUBLISH_FLAG;
1042 l_audit_vals_rec.QA_COLLECTION_ID := l_service_request_rec.QA_COLLECTION_ID;
1043 l_audit_vals_rec.OLD_QA_COLLECTION_ID := l_service_request_rec.QA_COLLECTION_ID;
1044 l_audit_vals_rec.CONTRACT_ID := l_service_request_rec.CONTRACT_ID;
1045 l_audit_vals_rec.OLD_CONTRACT_ID := l_service_request_rec.CONTRACT_ID;
1046 l_audit_vals_rec.CONTRACT_SERVICE_ID := l_service_request_rec.CONTRACT_SERVICE_ID;
1047 l_audit_vals_rec.OLD_CONTRACT_SERVICE_ID := l_service_request_rec.CONTRACT_SERVICE_ID;
1048 l_audit_vals_rec.TIME_ZONE_ID := l_service_request_rec.TIME_ZONE_ID;
1049 l_audit_vals_rec.OLD_TIME_ZONE_ID := l_service_request_rec.TIME_ZONE_ID;
1050 l_audit_vals_rec.ACCOUNT_ID := l_service_request_rec.ACCOUNT_ID;
1051 l_audit_vals_rec.OLD_ACCOUNT_ID := l_service_request_rec.ACCOUNT_ID;
1052 l_audit_vals_rec.TIME_DIFFERENCE := l_service_request_rec.TIME_DIFFERENCE;
1053 l_audit_vals_rec.OLD_TIME_DIFFERENCE := l_service_request_rec.TIME_DIFFERENCE;
1054 l_audit_vals_rec.CUSTOMER_PO_NUMBER := l_service_request_rec.CUSTOMER_PO_NUMBER;
1055 l_audit_vals_rec.OLD_CUSTOMER_PO_NUMBER := l_service_request_rec.CUSTOMER_PO_NUMBER;
1056 l_audit_vals_rec.CUSTOMER_TICKET_NUMBER := l_service_request_rec.CUSTOMER_TICKET_NUMBER;
1057 l_audit_vals_rec.OLD_CUSTOMER_TICKET_NUMBER := l_service_request_rec.CUSTOMER_TICKET_NUMBER;
1058 l_audit_vals_rec.CUSTOMER_SITE_ID := l_service_request_rec.CUSTOMER_SITE_ID;
1059 l_audit_vals_rec.OLD_CUSTOMER_SITE_ID := l_service_request_rec.CUSTOMER_SITE_ID;
1060 l_audit_vals_rec.CALLER_TYPE := l_service_request_rec.CALLER_TYPE;
1061 l_audit_vals_rec.OLD_CALLER_TYPE := l_service_request_rec.CALLER_TYPE;
1062 l_audit_vals_rec.PROJECT_NUMBER := l_service_request_rec.PROJECT_NUMBER;
1063 l_audit_vals_rec.OLD_PROJECT_NUMBER := l_service_request_rec.PROJECT_NUMBER;
1064 l_audit_vals_rec.PLATFORM_VERSION := l_service_request_rec.PLATFORM_VERSION;
1065 l_audit_vals_rec.OLD_PLATFORM_VERSION := l_service_request_rec.PLATFORM_VERSION;
1066 l_audit_vals_rec.DB_VERSION := l_service_request_rec.DB_VERSION;
1067 l_audit_vals_rec.OLD_DB_VERSION := l_service_request_rec.DB_VERSION;
1068 l_audit_vals_rec.CUST_PREF_LANG_ID := l_service_request_rec.CUST_PREF_LANG_ID;
1069 l_audit_vals_rec.OLD_CUST_PREF_LANG_ID := l_service_request_rec.CUST_PREF_LANG_ID;
1070 l_audit_vals_rec.TIER := l_service_request_rec.TIER;
1071 l_audit_vals_rec.OLD_TIER := l_service_request_rec.TIER;
1072 l_audit_vals_rec.CATEGORY_ID := l_service_request_rec.CATEGORY_ID;
1073 l_audit_vals_rec.OLD_CATEGORY_ID := l_service_request_rec.CATEGORY_ID;
1074 l_audit_vals_rec.OPERATING_SYSTEM := l_service_request_rec.OPERATING_SYSTEM;
1075 l_audit_vals_rec.OLD_OPERATING_SYSTEM := l_service_request_rec.OPERATING_SYSTEM;
1076 l_audit_vals_rec.DATABASE := l_service_request_rec.DATABASE;
1077 l_audit_vals_rec.OLD_DATABASE := l_service_request_rec.DATABASE;
1078 l_audit_vals_rec.GROUP_TERRITORY_ID := l_service_request_rec.GROUP_TERRITORY_ID;
1079 l_audit_vals_rec.OLD_GROUP_TERRITORY_ID := l_service_request_rec.GROUP_TERRITORY_ID;
1080 l_audit_vals_rec.COMM_PREF_CODE := l_service_request_rec.COMM_PREF_CODE;
1081 l_audit_vals_rec.OLD_COMM_PREF_CODE := l_service_request_rec.COMM_PREF_CODE;
1082 l_audit_vals_rec.LAST_UPDATE_CHANNEL := l_service_request_rec.LAST_UPDATE_CHANNEL;
1083 l_audit_vals_rec.OLD_LAST_UPDATE_CHANNEL := l_service_request_rec.LAST_UPDATE_CHANNEL;
1084 l_audit_vals_rec.CUST_PREF_LANG_CODE := l_service_request_rec.CUST_PREF_LANG_CODE;
1085 l_audit_vals_rec.OLD_CUST_PREF_LANG_CODE := l_service_request_rec.CUST_PREF_LANG_CODE;
1086 l_audit_vals_rec.ERROR_CODE := l_service_request_rec.ERROR_CODE;
1087 l_audit_vals_rec.OLD_ERROR_CODE := l_service_request_rec.ERROR_CODE;
1088 l_audit_vals_rec.CATEGORY_SET_ID := l_service_request_rec.CATEGORY_SET_ID;
1089 l_audit_vals_rec.OLD_CATEGORY_SET_ID := l_service_request_rec.CATEGORY_SET_ID;
1090 l_audit_vals_rec.EXTERNAL_REFERENCE := l_service_request_rec.EXTERNAL_REFERENCE;
1091 l_audit_vals_rec.OLD_EXTERNAL_REFERENCE := l_service_request_rec.EXTERNAL_REFERENCE;
1092 l_audit_vals_rec.INCIDENT_OCCURRED_DATE := l_service_request_rec.INCIDENT_OCCURRED_DATE;
1093 l_audit_vals_rec.OLD_INCIDENT_OCCURRED_DATE := l_service_request_rec.INCIDENT_OCCURRED_DATE;
1094 l_audit_vals_rec.INCIDENT_RESOLVED_DATE := l_service_request_rec.INCIDENT_RESOLVED_DATE;
1095 l_audit_vals_rec.INC_RESPONDED_BY_DATE := l_service_request_rec.INC_RESPONDED_BY_DATE;
1096 l_audit_vals_rec.INCIDENT_LOCATION_ID := l_service_request_rec.INCIDENT_LOCATION_ID;
1097 l_audit_vals_rec.OLD_INCIDENT_LOCATION_ID := l_service_request_rec.INCIDENT_LOCATION_ID;
1098 l_audit_vals_rec.INCIDENT_ADDRESS := l_service_request_rec.INCIDENT_ADDRESS;
1099 l_audit_vals_rec.OLD_INCIDENT_ADDRESS := l_service_request_rec.INCIDENT_ADDRESS;
1100 l_audit_vals_rec.INCIDENT_CITY := l_service_request_rec.INCIDENT_CITY;
1101 l_audit_vals_rec.OLD_INCIDENT_CITY := l_service_request_rec.INCIDENT_CITY;
1102 l_audit_vals_rec.INCIDENT_STATE := l_service_request_rec.INCIDENT_STATE;
1103 l_audit_vals_rec.OLD_INCIDENT_STATE := l_service_request_rec.INCIDENT_STATE;
1104 l_audit_vals_rec.INCIDENT_COUNTRY := l_service_request_rec.INCIDENT_COUNTRY;
1105 l_audit_vals_rec.OLD_INCIDENT_COUNTRY := l_service_request_rec.INCIDENT_COUNTRY;
1106 l_audit_vals_rec.INCIDENT_PROVINCE := l_service_request_rec.INCIDENT_PROVINCE;
1107 l_audit_vals_rec.OLD_INCIDENT_PROVINCE := l_service_request_rec.INCIDENT_PROVINCE;
1108 l_audit_vals_rec.INCIDENT_POSTAL_CODE := l_service_request_rec.INCIDENT_POSTAL_CODE;
1109 l_audit_vals_rec.OLD_INCIDENT_POSTAL_CODE := l_service_request_rec.INCIDENT_POSTAL_CODE;
1110 l_audit_vals_rec.INCIDENT_COUNTY := l_service_request_rec.INCIDENT_COUNTY;
1111 l_audit_vals_rec.OLD_INCIDENT_COUNTY := l_service_request_rec.INCIDENT_COUNTY;
1112 l_audit_vals_rec.SR_CREATION_CHANNEL := l_service_request_rec.SR_CREATION_CHANNEL;
1113 l_audit_vals_rec.OLD_SR_CREATION_CHANNEL := l_service_request_rec.SR_CREATION_CHANNEL;
1114 l_audit_vals_rec.EXTERNAL_ATTRIBUTE_1 := l_service_request_rec.EXTERNAL_ATTRIBUTE_1;
1115 l_audit_vals_rec.OLD_EXTERNAL_ATTRIBUTE_1 := l_service_request_rec.EXTERNAL_ATTRIBUTE_1;
1116 l_audit_vals_rec.EXTERNAL_ATTRIBUTE_2 := l_service_request_rec.EXTERNAL_ATTRIBUTE_2;
1117 l_audit_vals_rec.OLD_EXTERNAL_ATTRIBUTE_1 := l_service_request_rec.EXTERNAL_ATTRIBUTE_1;
1118 l_audit_vals_rec.EXTERNAL_ATTRIBUTE_3 := l_service_request_rec.EXTERNAL_ATTRIBUTE_3;
1119 l_audit_vals_rec.OLD_EXTERNAL_ATTRIBUTE_1 := l_service_request_rec.EXTERNAL_ATTRIBUTE_1;
1120 l_audit_vals_rec.EXTERNAL_ATTRIBUTE_4 := l_service_request_rec.EXTERNAL_ATTRIBUTE_4;
1121 l_audit_vals_rec.OLD_EXTERNAL_ATTRIBUTE_1 := l_service_request_rec.EXTERNAL_ATTRIBUTE_1;
1122 l_audit_vals_rec.EXTERNAL_ATTRIBUTE_5 := l_service_request_rec.EXTERNAL_ATTRIBUTE_5;
1123 l_audit_vals_rec.OLD_EXTERNAL_ATTRIBUTE_1 := l_service_request_rec.EXTERNAL_ATTRIBUTE_1;
1124 l_audit_vals_rec.EXTERNAL_ATTRIBUTE_6 := l_service_request_rec.EXTERNAL_ATTRIBUTE_6;
1125 l_audit_vals_rec.OLD_EXTERNAL_ATTRIBUTE_1 := l_service_request_rec.EXTERNAL_ATTRIBUTE_1;
1126 l_audit_vals_rec.EXTERNAL_ATTRIBUTE_7 := l_service_request_rec.EXTERNAL_ATTRIBUTE_7;
1127 l_audit_vals_rec.OLD_EXTERNAL_ATTRIBUTE_1 := l_service_request_rec.EXTERNAL_ATTRIBUTE_1;
1128 l_audit_vals_rec.EXTERNAL_ATTRIBUTE_8 := l_service_request_rec.EXTERNAL_ATTRIBUTE_8;
1129 l_audit_vals_rec.OLD_EXTERNAL_ATTRIBUTE_1 := l_service_request_rec.EXTERNAL_ATTRIBUTE_1;
1130 l_audit_vals_rec.EXTERNAL_ATTRIBUTE_9 := l_service_request_rec.EXTERNAL_ATTRIBUTE_9;
1131 l_audit_vals_rec.OLD_EXTERNAL_ATTRIBUTE_1 := l_service_request_rec.EXTERNAL_ATTRIBUTE_1;
1132 l_audit_vals_rec.EXTERNAL_ATTRIBUTE_10 := l_service_request_rec.EXTERNAL_ATTRIBUTE_10;
1133 l_audit_vals_rec.OLD_EXTERNAL_ATTRIBUTE_1 := l_service_request_rec.EXTERNAL_ATTRIBUTE_1;
1134 l_audit_vals_rec.EXTERNAL_ATTRIBUTE_11 := l_service_request_rec.EXTERNAL_ATTRIBUTE_11;
1135 l_audit_vals_rec.OLD_EXTERNAL_ATTRIBUTE_1 := l_service_request_rec.EXTERNAL_ATTRIBUTE_1;
1136 l_audit_vals_rec.EXTERNAL_ATTRIBUTE_12 := l_service_request_rec.EXTERNAL_ATTRIBUTE_12;
1137 l_audit_vals_rec.OLD_EXTERNAL_ATTRIBUTE_1 := l_service_request_rec.EXTERNAL_ATTRIBUTE_1;
1138 l_audit_vals_rec.EXTERNAL_ATTRIBUTE_13 := l_service_request_rec.EXTERNAL_ATTRIBUTE_13;
1139 l_audit_vals_rec.OLD_EXTERNAL_ATTRIBUTE_1 := l_service_request_rec.EXTERNAL_ATTRIBUTE_1;
1140 l_audit_vals_rec.EXTERNAL_ATTRIBUTE_14 := l_service_request_rec.EXTERNAL_ATTRIBUTE_14;
1141 l_audit_vals_rec.OLD_EXTERNAL_ATTRIBUTE_1 := l_service_request_rec.EXTERNAL_ATTRIBUTE_1;
1142 l_audit_vals_rec.EXTERNAL_ATTRIBUTE_15 := l_service_request_rec.EXTERNAL_ATTRIBUTE_15;
1143 l_audit_vals_rec.OLD_EXTERNAL_ATTRIBUTE_1 := l_service_request_rec.EXTERNAL_ATTRIBUTE_1;
1144 l_audit_vals_rec.EXTERNAL_CONTEXT := l_service_request_rec.EXTERNAL_CONTEXT;
1145 l_audit_vals_rec.OLD_EXTERNAL_CONTEXT := l_service_request_rec.EXTERNAL_CONTEXT;
1146 l_audit_vals_rec.CREATION_PROGRAM_CODE := l_service_request_rec.CREATION_PROGRAM_CODE;
1147 l_audit_vals_rec.OLD_CREATION_PROGRAM_CODE := l_service_request_rec.CREATION_PROGRAM_CODE;
1148 l_audit_vals_rec.COVERAGE_TYPE := l_service_request_rec.COVERAGE_TYPE;
1149 l_audit_vals_rec.OLD_COVERAGE_TYPE := l_service_request_rec.COVERAGE_TYPE;
1150 l_audit_vals_rec.BILL_TO_ACCOUNT_ID := l_service_request_rec.BILL_TO_ACCOUNT_ID;
1151 l_audit_vals_rec.OLD_BILL_TO_ACCOUNT_ID := l_service_request_rec.BILL_TO_ACCOUNT_ID;
1152 l_audit_vals_rec.SHIP_TO_ACCOUNT_ID := l_service_request_rec.SHIP_TO_ACCOUNT_ID;
1153 l_audit_vals_rec.OLD_SHIP_TO_ACCOUNT_ID := l_service_request_rec.SHIP_TO_ACCOUNT_ID;
1154 l_audit_vals_rec.CUSTOMER_EMAIL_ID := l_service_request_rec.CUSTOMER_EMAIL_ID;
1155 l_audit_vals_rec.OLD_CUSTOMER_EMAIL_ID := l_service_request_rec.CUSTOMER_EMAIL_ID;
1156 l_audit_vals_rec.CUSTOMER_PHONE_ID := l_service_request_rec.CUSTOMER_PHONE_ID;
1157 l_audit_vals_rec.OLD_CUSTOMER_PHONE_ID := l_service_request_rec.CUSTOMER_PHONE_ID;
1158 l_audit_vals_rec.BILL_TO_PARTY_ID := l_service_request_rec.BILL_TO_PARTY_ID;
1159 l_audit_vals_rec.OLD_BILL_TO_PARTY_ID := l_service_request_rec.BILL_TO_PARTY_ID;
1160 l_audit_vals_rec.SHIP_TO_PARTY_ID := l_service_request_rec.SHIP_TO_PARTY_ID;
1161 l_audit_vals_rec.OLD_SHIP_TO_PARTY_ID := l_service_request_rec.SHIP_TO_PARTY_ID;
1162 l_audit_vals_rec.BILL_TO_SITE_ID := l_service_request_rec.BILL_TO_SITE_ID;
1163 l_audit_vals_rec.OLD_BILL_TO_SITE_ID := l_service_request_rec.BILL_TO_SITE_ID;
1164 l_audit_vals_rec.SHIP_TO_SITE_ID := l_service_request_rec.SHIP_TO_SITE_ID;
1165 l_audit_vals_rec.OLD_SHIP_TO_SITE_ID := l_service_request_rec.SHIP_TO_SITE_ID;
1166 l_audit_vals_rec.PROGRAM_LOGIN_ID := l_service_request_rec.PROGRAM_LOGIN_ID;
1167 l_audit_vals_rec.OLD_PROGRAM_LOGIN_ID := l_service_request_rec.PROGRAM_LOGIN_ID;
1168 l_audit_vals_rec.INCIDENT_POINT_OF_INTEREST := l_service_request_rec.INCIDENT_POINT_OF_INTEREST;
1169 l_audit_vals_rec.OLD_INCIDENT_POINT_OF_INTEREST := l_service_request_rec.INCIDENT_POINT_OF_INTEREST;
1170 l_audit_vals_rec.INCIDENT_CROSS_STREET := l_service_request_rec.INCIDENT_CROSS_STREET;
1171 l_audit_vals_rec.OLD_INCIDENT_CROSS_STREET := l_service_request_rec.INCIDENT_CROSS_STREET;
1172 l_audit_vals_rec.INCIDENT_DIRECTION_QUALIF := l_service_request_rec.incident_direction_qualifier;
1173 l_audit_vals_rec.OLD_INCIDENT_DIRECTION_QUALIF := l_service_request_rec.incident_direction_qualifier;
1174 l_audit_vals_rec.INCIDENT_DISTANCE_QUALIF := l_service_request_rec.incident_distance_qualifier;
1175 l_audit_vals_rec.OLD_INCIDENT_DISTANCE_QUALIF := l_service_request_rec.incident_distance_qualifier;
1176 l_audit_vals_rec.INCIDENT_DISTANCE_QUAL_UOM := l_service_request_rec.INCIDENT_DISTANCE_QUAL_UOM;
1177 l_audit_vals_rec.OLD_INCIDENT_DISTANCE_QUAL_UOM := l_service_request_rec.INCIDENT_DISTANCE_QUAL_UOM;
1178 l_audit_vals_rec.INCIDENT_ADDRESS2 := l_service_request_rec.INCIDENT_ADDRESS2;
1179 l_audit_vals_rec.OLD_INCIDENT_ADDRESS2 := l_service_request_rec.INCIDENT_ADDRESS2;
1180 l_audit_vals_rec.INCIDENT_ADDRESS3 := l_service_request_rec.INCIDENT_ADDRESS3;
1181 l_audit_vals_rec.OLD_INCIDENT_ADDRESS3 := l_service_request_rec.INCIDENT_ADDRESS3;
1182 l_audit_vals_rec.INCIDENT_ADDRESS4 := l_service_request_rec.INCIDENT_ADDRESS4;
1183 l_audit_vals_rec.OLD_INCIDENT_ADDRESS4 := l_service_request_rec.INCIDENT_ADDRESS4;
1184 l_audit_vals_rec.INCIDENT_ADDRESS_STYLE := l_service_request_rec.INCIDENT_ADDRESS_STYLE;
1185 l_audit_vals_rec.OLD_INCIDENT_ADDRESS_STYLE := l_service_request_rec.INCIDENT_ADDRESS_STYLE;
1186 l_audit_vals_rec.INCIDENT_ADDR_LNS_PHONETIC := l_service_request_rec.incident_addr_lines_phonetic;
1187 l_audit_vals_rec.OLD_INCIDENT_ADDR_LNS_PHONETIC := l_service_request_rec.incident_addr_lines_phonetic;
1188 l_audit_vals_rec.INCIDENT_PO_BOX_NUMBER := l_service_request_rec.INCIDENT_PO_BOX_NUMBER;
1189 l_audit_vals_rec.OLD_INCIDENT_PO_BOX_NUMBER := l_service_request_rec.INCIDENT_PO_BOX_NUMBER;
1190 l_audit_vals_rec.INCIDENT_HOUSE_NUMBER := l_service_request_rec.INCIDENT_HOUSE_NUMBER;
1191 l_audit_vals_rec.OLD_INCIDENT_HOUSE_NUMBER := l_service_request_rec.INCIDENT_HOUSE_NUMBER;
1192 l_audit_vals_rec.INCIDENT_STREET_SUFFIX := l_service_request_rec.INCIDENT_STREET_SUFFIX;
1193 l_audit_vals_rec.OLD_INCIDENT_STREET_SUFFIX := l_service_request_rec.INCIDENT_STREET_SUFFIX;
1194 l_audit_vals_rec.INCIDENT_STREET := l_service_request_rec.INCIDENT_STREET;
1195 l_audit_vals_rec.OLD_INCIDENT_STREET := l_service_request_rec.INCIDENT_STREET;
1196 l_audit_vals_rec.INCIDENT_STREET_NUMBER := l_service_request_rec.INCIDENT_STREET_NUMBER;
1197 l_audit_vals_rec.OLD_INCIDENT_STREET_NUMBER := l_service_request_rec.INCIDENT_STREET_NUMBER;
1198 l_audit_vals_rec.INCIDENT_FLOOR := l_service_request_rec.INCIDENT_FLOOR;
1199 l_audit_vals_rec.OLD_INCIDENT_FLOOR := l_service_request_rec.INCIDENT_FLOOR;
1200 l_audit_vals_rec.INCIDENT_SUITE := l_service_request_rec.INCIDENT_SUITE;
1201 l_audit_vals_rec.OLD_INCIDENT_SUITE := l_service_request_rec.INCIDENT_SUITE;
1202 l_audit_vals_rec.INCIDENT_POSTAL_PLUS4_CODE := l_service_request_rec.INCIDENT_POSTAL_PLUS4_CODE;
1203 l_audit_vals_rec.OLD_INCIDENT_POSTAL_PLUS4_CODE := l_service_request_rec.INCIDENT_POSTAL_PLUS4_CODE;
1204 l_audit_vals_rec.INCIDENT_POSITION := l_service_request_rec.INCIDENT_POSITION;
1205 l_audit_vals_rec.OLD_INCIDENT_POSITION := l_service_request_rec.INCIDENT_POSITION;
1206 l_audit_vals_rec.INCIDENT_LOC_DIRECTIONS := l_service_request_rec.incident_location_directions;
1207 l_audit_vals_rec.OLD_INCIDENT_LOC_DIRECTIONS := l_service_request_rec.incident_location_directions;
1208 l_audit_vals_rec.INCIDENT_LOC_DESCRIPTION := l_service_request_rec.incident_location_description;
1209 l_audit_vals_rec.OLD_INCIDENT_LOC_DESCRIPTION := l_service_request_rec.incident_location_description;
1210 l_audit_vals_rec.INSTALL_SITE_ID := l_service_request_rec.INSTALL_SITE_ID;
1211 l_audit_vals_rec.OLD_INSTALL_SITE_ID := l_service_request_rec.INSTALL_SITE_ID;
1212 l_audit_vals_rec.TIER_VERSION := l_service_request_rec.TIER_VERSION;
1213 l_audit_vals_rec.OLD_TIER_VERSION := l_service_request_rec.TIER_VERSION;
1214 l_audit_vals_rec.ORG_ID := l_service_request_rec.ORIGINAL_ORDER_NUMBER;
1215 l_audit_vals_rec.OLD_ORG_ID := l_service_request_rec.ORIGINAL_ORDER_NUMBER;
1216 l_audit_vals_rec.CONTRACT_NUMBER := l_service_request_rec.CONTRACT_NUMBER;
1217 l_audit_vals_rec.OLD_CONTRACT_NUMBER := l_service_request_rec.CONTRACT_NUMBER;
1218 l_audit_vals_rec.DEF_DEFECT_ID := l_service_request_rec.DEF_DEFECT_ID;
1219 l_audit_vals_rec.OLD_DEF_DEFECT_ID := l_service_request_rec.DEF_DEFECT_ID;
1220 l_audit_vals_rec.DEF_DEFECT_ID2 := l_service_request_rec.DEF_DEFECT_ID2;
1221 l_audit_vals_rec.OLD_DEF_DEFECT_ID2 := l_service_request_rec.DEF_DEFECT_ID2;
1222 l_audit_vals_rec.UPDATED_ENTITY_CODE := P_updated_entity_code ;
1223 l_audit_vals_rec.UPDATED_ENTITY_ID := p_updated_entity_id ;
1224 l_audit_vals_rec.INCIDENT_LAST_MODIFIED_DATE := p_entity_update_date;
1225 l_audit_vals_rec.ENTITY_ACTIVITY_CODE := p_entity_activity_code;
1226 --anmukher --09/12/03
1227 l_audit_vals_rec.OLD_INC_OBJECT_VERSION_NUMBER := l_service_request_rec.OBJECT_VERSION_NUMBER;
1228 --Removed addition of 1 to object version number since SR Header record is not updated
1229 --anmukher -- 10/15/03
1230 l_audit_vals_rec.INC_OBJECT_VERSION_NUMBER := l_service_request_rec.OBJECT_VERSION_NUMBER;
1231 l_audit_vals_rec.OLD_INC_REQUEST_ID := l_service_request_rec.REQUEST_ID;
1232 l_audit_vals_rec.INC_REQUEST_ID := l_service_request_rec.REQUEST_ID;
1233 l_audit_vals_rec.OLD_INC_PROGRAM_APPLICATION_ID := l_service_request_rec.PROGRAM_APPLICATION_ID;
1234 l_audit_vals_rec.INC_PROGRAM_APPLICATION_ID := l_service_request_rec.PROGRAM_APPLICATION_ID;
1235 l_audit_vals_rec.OLD_INC_PROGRAM_ID := l_service_request_rec.PROGRAM_ID;
1236 l_audit_vals_rec.INC_PROGRAM_ID := l_service_request_rec.PROGRAM_ID;
1237 l_audit_vals_rec.OLD_INC_PROGRAM_UPDATE_DATE := l_service_request_rec.PROGRAM_UPDATE_DATE;
1238 l_audit_vals_rec.INC_PROGRAM_UPDATE_DATE := l_service_request_rec.PROGRAM_UPDATE_DATE;
1239 l_audit_vals_rec.OLD_OWNING_DEPARTMENT_ID := l_service_request_rec.OWNING_DEPARTMENT_ID;
1240 l_audit_vals_rec.OWNING_DEPARTMENT_ID := l_service_request_rec.OWNING_DEPARTMENT_ID;
1241 l_audit_vals_rec.OLD_INCIDENT_LOCATION_TYPE := l_service_request_rec.INCIDENT_LOCATION_TYPE;
1242 l_audit_vals_rec.INCIDENT_LOCATION_TYPE := l_service_request_rec.INCIDENT_LOCATION_TYPE;
1243 l_audit_vals_rec.UNASSIGNED_INDICATOR := l_service_request_rec.UNASSIGNED_INDICATOR;
1244 l_audit_vals_rec.OLD_ORG_ID := l_service_request_rec.ORG_ID ;
1245 l_audit_vals_rec.ORG_ID := l_service_request_rec.ORG_ID ;
1246 l_audit_vals_rec.INCIDENT_NUMBER := l_service_request_rec.INCIDENT_NUMBER ;
1247 l_audit_vals_rec.OLD_INCIDENT_NUMBER := l_service_request_rec.INCIDENT_NUMBER ;
1248 -- audit component R12 project
1249 l_audit_vals_rec.OLD_MAINT_ORGANIZATION_ID := l_service_request_rec.MAINT_ORGANIZATION_ID ;
1250 l_audit_vals_rec.MAINT_ORGANIZATION_ID := l_service_request_rec.MAINT_ORGANIZATION_ID ;
1251
1252 -- Call CS_ServiceRequest_PVT.Create_Audit_Record API to create SR Audit record.
1253
1254 CS_ServiceRequest_PVT.Create_Audit_Record (
1255 p_api_version => 2.0,
1256 p_init_msg_list => FND_API.G_FALSE ,
1257 p_commit => FND_API.G_FALSE ,
1258 p_request_id => p_incident_id ,
1259 p_audit_id => null ,
1260 p_audit_vals_rec => l_audit_vals_rec ,
1261 p_user_id => NVL(p_user_id,FND_GLOBAL.USER_ID),
1262 p_last_update_date => sysdate,
1263 p_creation_date => sysdate,
1264 x_return_status => x_return_status ,
1265 x_msg_count => x_msg_count ,
1266 x_msg_data => x_msg_data ,
1267 x_audit_id => x_audit_id );
1268
1269 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1270 RAISE e_create_audit_failed ;
1271 END IF ;
1272
1273 CLOSE get_sr_date;
1274
1275 EXCEPTION
1276 WHEN e_create_audit_failed THEN
1277 CLOSE get_sr_date;
1278 x_return_status := FND_API.G_RET_STS_ERROR;
1279
1280 WHEN SR_Lock_Row THEN
1281 IF (get_sr_date%ISOPEN) THEN
1282 CLOSE get_sr_date;
1283 END IF;
1284 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1285 FND_MESSAGE.SET_NAME('FND','FORM_RECORD_CHANGED');
1286 FND_MSG_PUB.ADD;
1287 FND_MSG_PUB.Count_And_Get
1288 ( p_count => x_msg_count,
1289 p_data => x_msg_data
1290 );
1291
1292 WHEN others THEN
1293 CLOSE get_sr_date;
1294 l_text := sqlerrm ;
1295 l_num := sqlcode ;
1296 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1297
1298 END CS_SR_AUDIT_CHILD;
1299
1300 -- Added for status update --anmukher -- 10/16/03
1301 FUNCTION GET_STATUS_FLAG( p_incident_status_id IN NUMBER)
1302 RETURN VARCHAR2 IS
1303 CURSOR get_close_flag IS
1304 SELECT close_flag
1305 FROM cs_incident_statuses_b
1306 WHERE incident_status_id = p_incident_status_id;
1307
1308 l_closed_flag VARCHAR2(1);
1309 l_status_flag VARCHAR2(1):='O';
1310 BEGIN
1311 OPEN get_close_flag;
1312 FETCH get_close_flag INTO l_closed_flag;
1313 CLOSE get_close_flag;
1314
1315 IF l_closed_flag = 'Y' THEN
1316 l_status_flag:= 'C';
1317 ELSE
1318 l_status_flag:= 'O';
1319 END IF;
1320 RETURN(l_status_flag);
1321
1322 END GET_STATUS_FLAG;
1323
1324 END CS_SR_CHILD_AUDIT_PKG;