[Home] [Help]
PACKAGE BODY: APPS.CS_INCIDENT_ACTIONS_PKG
Source
4 /* ******* LOCAL PROCEDURES ******* */
1 PACKAGE BODY CS_INCIDENT_ACTIONS_PKG as
2 /* $Header: csincab.pls 115.1 99/07/16 08:57:52 porting ship $ */
3
5
6 PROCEDURE Register_New_Action_Inc_Audit( x_action_id NUMBER,
7 x_incident_id NUMBER,
8 x_change_description VARCHAR2,
9 x_action_created_by NUMBER,
10 x_last_Update_login NUMBER );
11 PROCEDURE Process_Action_Audit
12 ( X_Last_Update_Date DATE,
13 X_Last_Updated_By NUMBER,
14 X_Last_Update_Login NUMBER,
15 X_Incident_Action_Id NUMBER,
16 X_Incident_Id NUMBER,
17 X_Action_Owner_Id NUMBER DEFAULT NULL,
18 X_Action_Status_Id NUMBER DEFAULT NULL,
19 X_Action_Severity_Id NUMBER DEFAULT NULL,
20 X_Expected_Resolution_Date DATE DEFAULT NULL,
21 X_Orig_Action_Severity_Id NUMBER DEFAULT NULL,
22 X_Orig_Action_Type_Id NUMBER DEFAULT NULL,
23 X_Orig_Action_Status_Id NUMBER DEFAULT NULL,
24 X_Orig_Action_Owner_Id NUMBER DEFAULT NULL,
25 X_Action_Type_Id NUMBER DEFAULT NULL,
26 X_Orig_Expected_Date DATE DEFAULT NULL );
27
28
29 /* ********* END LOCAL PROCEDURES ********** */
30
31
32 PROCEDURE Insert_Row(X_Rowid IN OUT VARCHAR2,
36 X_Creation_Date DATE,
33 X_Incident_Action_Id IN OUT NUMBER,
34 X_Last_Update_Date DATE,
35 X_Last_Updated_By NUMBER,
37 X_Created_By NUMBER,
38 X_Last_Update_Login NUMBER DEFAULT NULL,
39 X_Incident_Id NUMBER,
40 X_Action_Code VARCHAR2,
41 X_Action_Num NUMBER,
42 X_Action_Type_Id NUMBER DEFAULT NULL,
43 X_Action_Status_Id NUMBER DEFAULT NULL,
44 X_Responsible_Person_Id NUMBER DEFAULT NULL,
45 X_Text VARCHAR2 DEFAULT NULL,
46 X_Completion_Date DATE DEFAULT NULL,
47 X_actual_time number DEFAULT NULL,
48 X_Action_Severity_Id NUMBER DEFAULT NULL,
49 X_Text_Description VARCHAR2 DEFAULT NULL,
50 X_Text_Resolution VARCHAR2 DEFAULT NULL,
51 X_Action_Effective_Date DATE DEFAULT NULL,
52 X_Expected_Resolution_Date DATE DEFAULT NULL,
53 X_Attribute1 VARCHAR2 DEFAULT NULL,
54 X_Attribute2 VARCHAR2 DEFAULT NULL,
55 X_Attribute3 VARCHAR2 DEFAULT NULL,
56 X_Attribute4 VARCHAR2 DEFAULT NULL,
57 X_Attribute5 VARCHAR2 DEFAULT NULL,
58 X_Attribute6 VARCHAR2 DEFAULT NULL,
59 X_Attribute7 VARCHAR2 DEFAULT NULL,
60 X_Attribute8 VARCHAR2 DEFAULT NULL,
61 X_Attribute9 VARCHAR2 DEFAULT NULL,
62 X_Attribute10 VARCHAR2 DEFAULT NULL,
63 X_Attribute11 VARCHAR2 DEFAULT NULL,
64 X_Attribute12 VARCHAR2 DEFAULT NULL,
65 X_Attribute13 VARCHAR2 DEFAULT NULL,
66 X_Attribute14 VARCHAR2 DEFAULT NULL,
67 X_Attribute15 VARCHAR2 DEFAULT NULL,
68 X_Context VARCHAR2 DEFAULT NULL,
69 X_OPEN_FLAG VARCHAR2 DEFAULT NULL ) IS
70
71 CURSOR C IS SELECT rowid FROM cs_incident_actions
72 WHERE incident_action_id = X_Incident_Action_Id;
73 CURSOR C2 IS SELECT cs_incident_actions_s.nextval FROM sys.dual;
74
75 BEGIN
76 IF (X_Incident_Action_Id IS NULL) THEN
77 OPEN C2;
78 FETCH C2 INTO X_Incident_Action_Id;
79 CLOSE C2;
80 END IF;
81
82 INSERT INTO cs_incident_actions(
83 incident_action_id,
84 last_update_date,
85 last_updated_by,
86 creation_date,
87 created_by,
88 last_update_login,
89 incident_id,
90 action_code,
91 action_num,
92 action_type_id,
93 action_status_id,
94 responsible_person_id,
95 text,
96 completion_date,
97 actual_time ,
98 action_severity_id,
99 text_description,
100 text_resolution,
101 action_effective_date,
102 expected_resolution_date,
103 attribute1,
104 attribute2,
105 attribute3,
106 attribute4,
107 attribute5,
108 attribute6,
109 attribute7,
110 attribute8,
111 attribute9,
112 attribute10,
113 attribute11,
114 attribute12,
115 attribute13,
116 attribute14,
117 attribute15,
118 context,
119 open_flag
120 ) VALUES (
121 X_Incident_Action_Id,
122 trunc(sysdate),
123 X_Last_Updated_By,
124 trunc(sysdate),
125 X_Created_By,
126 X_Last_Update_Login,
127 X_Incident_Id,
128 X_Action_Code,
129 X_Action_Num,
130 X_Action_Type_Id,
131 X_Action_Status_Id,
132 X_Responsible_Person_Id,
133 X_Text,
134 X_Completion_Date,
135 X_actual_time ,
136 X_Action_Severity_Id,
137 X_Text_Description,
138 X_Text_Resolution,
139 X_Action_Effective_Date,
140 X_Expected_Resolution_Date,
141 X_Attribute1,
142 X_Attribute2,
143 X_Attribute3,
144 X_Attribute4,
145 X_Attribute5,
146 X_Attribute6,
147 X_Attribute7,
148 X_Attribute8,
149 X_Attribute9,
150 X_Attribute10,
151 X_Attribute11,
152 X_Attribute12,
153 X_Attribute13,
154 X_Attribute14,
158 );
155 X_Attribute15,
156 X_Context,
157 X_Open_Flag
159
160 OPEN C;
161 FETCH C INTO X_Rowid;
162 if (C%NOTFOUND) then
163 CLOSE C;
164 Raise NO_DATA_FOUND;
165 end if;
166 CLOSE C;
167
168 /* Insert into cs_incidents_audit table that an action has been created */
169 Register_New_Action_Inc_Audit (x_incident_action_id,
170 x_incident_id,
171 x_text,
172 x_created_by,
173 x_last_Update_login);
174
175 END Insert_Row;
176
177 PROCEDURE Register_New_Action_Inc_Audit( x_action_id NUMBER,
178 x_incident_id NUMBER,
179 x_change_description VARCHAR2,
180 x_action_created_by NUMBER,
181 x_last_Update_login NUMBER ) IS
182
183 X_INCIDENT_AUDIT_ID NUMBER ;
184 X_CHG_INCIDENT_STATUS_FLAG CHAR(1) := 'N';
185 X_CHG_INCIDENT_TYPE_FLAG CHAR(1) := 'N';
186 X_CHG_INCIDENT_URGENCY_FLAG CHAR(1) := 'N';
187 X_CHG_INCIDENT_SEVERITY_FLAG CHAR(1) := 'N';
188 X_CHG_INCIDENT_OWNER_FLAG CHAR(1) := 'N';
189 X_CHG_RESOLUTION_FLAG CHAR(1) := 'N';
190 X_CREATE_MANUAL_ACTION CHAR(1) := 'N';
191
192 X_INCIDENT_OWNER_ID NUMBER := NULL;
193 X1_INCIDENT_STATUS_ID NUMBER := NULL;
194 X1_INCIDENT_TYPE_ID NUMBER := NULL;
195 X1_INCIDENT_URGENCY_ID NUMBER := NULL;
196 X1_INCIDENT_SEVERITY_ID NUMBER := NULL;
197 X1_EXPECTED_RESOLUTION_DATE DATE := NULL;
198 X1_INCIDENT_OWNER_ID NUMBER := NULL;
199 X1_Orig_status_id NUMBER := NULL;
200 X1_Orig_Incident_Type_Id NUMBER := NULL;
201 X1_Orig_urgency_id NUMBER := NULL;
202 X1_Orig_Severity_id NUMBER := NULL;
203 X1_Orig_Owner_Id NUMBER := NULL;
204 X1_Orig_resolution_date DATE := NULL;
205
206
207 CURSOR C2 IS SELECT cs_incidents_audit_s1.nextval FROM sys.dual;
208
209 BEGIN
210 OPEN C2;
211 FETCH C2 INTO X_Incident_Audit_Id;
212 CLOSE C2;
213
214 X_create_manual_Action := 'Y';
215
216 INSERT INTO CS_Incidents_Audit(
217 incident_audit_id ,
218 incident_id ,
219 last_update_date ,
220 last_updated_by ,
221 creation_date ,
222 created_by ,
223 last_update_login ,
224 creation_time ,
225 incident_status_id ,
226 old_incident_status_id ,
227 change_incident_status_flag ,
228 incident_type_id ,
229 old_incident_type_id ,
230 change_incident_type_flag ,
231 incident_urgency_id ,
232 old_incident_urgency_id ,
233 change_incident_urgency_flag ,
234 incident_severity_id ,
235 old_incident_severity_id ,
236 change_incident_severity_flag,
237 incident_owner_id ,
238 old_incident_owner_id ,
239 change_incident_owner_flag ,
240 create_manual_action ,
241 action_id ,
242 expected_resolution_date ,
243 old_expected_resolution_date ,
244 change_resolution_flag ,
245 change_description )
246 VALUES(
247 X_Incident_Audit_Id,
248 X_Incident_id,
249 trunc(sysdate),
250 x_action_created_by,
251 trunc(sysdate),
252 x_action_created_by,
253 X_last_update_login,
254 to_char(sysdate,'HH:MI PM'),
255 X1_Incident_Status_Id,
256 X1_Orig_status_id,
257 X_Chg_incident_status_flag,
258 X1_Incident_Type_Id,
259 X1_Orig_Incident_Type_Id,
260 X_Chg_INCIDENT_TYPE_FLAG,
261 X1_Incident_Urgency_Id,
262 X1_Orig_urgency_id,
263 X_Chg_incident_urgency_flag,
264 X1_Incident_Severity_Id,
265 X1_Orig_Severity_id,
266 X_Chg_incident_Severity_flag,
267 X1_Incident_Owner_Id,
268 X1_Orig_Owner_Id,
269 X_Chg_incident_owner_flag,
270 X_Create_Manual_Action,
271 X_Action_Id,
272 X1_Expected_Resolution_Date,
273 X1_Orig_resolution_date,
274 X_Chg_resolution_flag,
275 X_Change_Description );
276
277 END Register_New_Action_Inc_Audit;
278
279 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
280 X_Incident_Action_Id NUMBER,
281 X_Incident_Id NUMBER,
282 X_Action_Code VARCHAR2,
283 X_Action_Num NUMBER DEFAULT NULL,
284 X_Action_Type_Id NUMBER DEFAULT NULL,
285 X_Action_Status_Id NUMBER DEFAULT NULL,
286 X_Responsible_Person_Id NUMBER DEFAULT NULL,
287 X_Text VARCHAR2 DEFAULT NULL,
288 X_Completion_Date DATE DEFAULT NULL,
292 X_Text_Resolution VARCHAR2 DEFAULT NULL,
289 X_actual_time number DEFAULT NULL,
290 X_Action_Severity_Id NUMBER DEFAULT NULL,
291 X_Text_Description VARCHAR2 DEFAULT NULL,
293 X_Action_Effective_Date DATE DEFAULT NULL,
294 X_Expected_Resolution_Date DATE DEFAULT NULL,
295 X_Attribute1 VARCHAR2 DEFAULT NULL,
296 X_Attribute2 VARCHAR2 DEFAULT NULL,
297 X_Attribute3 VARCHAR2 DEFAULT NULL,
298 X_Attribute4 VARCHAR2 DEFAULT NULL,
299 X_Attribute5 VARCHAR2 DEFAULT NULL,
300 X_Attribute6 VARCHAR2 DEFAULT NULL,
301 X_Attribute7 VARCHAR2 DEFAULT NULL,
302 X_Attribute8 VARCHAR2 DEFAULT NULL,
303 X_Attribute9 VARCHAR2 DEFAULT NULL,
304 X_Attribute10 VARCHAR2 DEFAULT NULL,
305 X_Attribute11 VARCHAR2 DEFAULT NULL,
306 X_Attribute12 VARCHAR2 DEFAULT NULL,
307 X_Attribute13 VARCHAR2 DEFAULT NULL,
308 X_Attribute14 VARCHAR2 DEFAULT NULL,
309 X_Attribute15 VARCHAR2 DEFAULT NULL,
310 X_Context VARCHAR2 DEFAULT NULL,
311 X_dispatcher_orig_syst VARCHAR2 DEFAULT NULL,
312 X_dispatcher_orig_syst_id NUMBER DEFAULT NULL,
313 X_dispatch_role_name VARCHAR2 DEFAULT NULL ) IS
314 CURSOR C IS
315 SELECT *
316 FROM cs_incident_actions
317 WHERE rowid = X_Rowid
318 FOR UPDATE of Incident_Action_Id NOWAIT;
319 Recinfo C%ROWTYPE;
320
321
322 BEGIN
323 OPEN C;
324 FETCH C INTO Recinfo;
325 if (C%NOTFOUND) then
326 CLOSE C;
327 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
328 APP_EXCEPTION.Raise_Exception;
329 end if;
330 CLOSE C;
331
332 IF (
333 (Recinfo.incident_action_id = X_Incident_Action_Id)
334 AND (Recinfo.incident_id = X_Incident_Id)
335 AND ( (Recinfo.action_num = X_Action_Num)
336 OR ( (Recinfo.action_num IS NULL)
337 AND (X_Action_Num IS NULL)))
338 AND ( (Recinfo.action_type_id = X_Action_Type_Id)
339 OR ( (Recinfo.action_type_id IS NULL)
340 AND (X_Action_Type_Id IS NULL)))
341 AND ( (Recinfo.action_status_id = X_Action_Status_Id)
342 OR ( (Recinfo.action_status_id IS NULL)
343 AND (X_Action_Status_Id IS NULL)))
344 AND ( (Recinfo.responsible_person_id = X_Responsible_Person_Id)
345 OR ( (Recinfo.responsible_person_id IS NULL)
346 AND (X_Responsible_Person_Id IS NULL)))
347 AND ( (Recinfo.text = X_Text)
348 OR ( (Recinfo.text IS NULL)
349 AND (X_Text IS NULL)))
350 AND ( (Recinfo.completion_date = X_Completion_Date)
351 OR ( (Recinfo.completion_date IS NULL)
352 AND (X_Completion_Date IS NULL)))
353 AND ( (Recinfo.actual_time = X_actual_time)
354 OR ( (Recinfo.actual_time IS NULL)
355 AND (X_actual_time IS NULL)))
356 AND ( (Recinfo.action_severity_id = X_Action_Severity_Id)
357 OR ( (Recinfo.action_severity_id IS NULL)
358 AND (X_Action_Severity_Id IS NULL)))
359 AND ( (Recinfo.text_description = X_Text_Description)
360 OR ( (Recinfo.text_description IS NULL)
361 AND (X_Text_Description IS NULL)))
362 AND ( (Recinfo.text_resolution = X_Text_Resolution)
363 OR ( (Recinfo.text_resolution IS NULL)
364 AND (X_Text_Resolution IS NULL)))
365 AND ( (Recinfo.action_effective_date = X_Action_Effective_Date)
366 OR ( (Recinfo.action_effective_date IS NULL)
367 AND (X_Action_Effective_Date IS NULL)))
368 AND ( (Recinfo.expected_resolution_date = X_Expected_Resolution_Date)
369 OR ( (Recinfo.expected_resolution_date IS NULL)
370 AND (X_Expected_Resolution_Date IS NULL)))
371 AND ( (Recinfo.attribute1 = X_Attribute1)
372 OR ( (Recinfo.attribute1 IS NULL)
373 AND (X_Attribute1 IS NULL)))
374 AND ( (Recinfo.attribute2 = X_Attribute2)
375 OR ( (Recinfo.attribute2 IS NULL)
376 AND (X_Attribute2 IS NULL)))
377 AND ( (Recinfo.attribute3 = X_Attribute3)
378 OR ( (Recinfo.attribute3 IS NULL)
379 AND (X_Attribute3 IS NULL)))
380 AND ( (Recinfo.attribute4 = X_Attribute4)
381 OR ( (Recinfo.attribute4 IS NULL)
382 AND (X_Attribute4 IS NULL)))
383 AND ( (Recinfo.attribute5 = X_Attribute5)
384 OR ( (Recinfo.attribute5 IS NULL)
385 AND (X_Attribute5 IS NULL)))
386 AND ( (Recinfo.attribute6 = X_Attribute6)
387 OR ( (Recinfo.attribute6 IS NULL)
388 AND (X_Attribute6 IS NULL)))
389 AND ( (Recinfo.attribute7 = X_Attribute7)
390 OR ( (Recinfo.attribute7 IS NULL)
391 AND (X_Attribute7 IS NULL)))
392 AND ( (Recinfo.attribute8 = X_Attribute8)
396 OR ( (Recinfo.attribute9 IS NULL)
393 OR ( (Recinfo.attribute8 IS NULL)
394 AND (X_Attribute8 IS NULL)))
395 AND ( (Recinfo.attribute9 = X_Attribute9)
397 AND (X_Attribute9 IS NULL)))
398 AND ( (Recinfo.attribute10 = X_Attribute10)
399 OR ( (Recinfo.attribute10 IS NULL)
400 AND (X_Attribute10 IS NULL)))
401 AND ( (Recinfo.attribute11 = X_Attribute11)
402 OR ( (Recinfo.attribute11 IS NULL)
403 AND (X_Attribute11 IS NULL)))
404 AND ( (Recinfo.attribute12 = X_Attribute12)
405 OR ( (Recinfo.attribute12 IS NULL)
406 AND (X_Attribute12 IS NULL)))
407 AND ( (Recinfo.attribute13 = X_Attribute13)
408 OR ( (Recinfo.attribute13 IS NULL)
409 AND (X_Attribute13 IS NULL)))
410 AND ( (Recinfo.attribute14 = X_Attribute14)
411 OR ( (Recinfo.attribute14 IS NULL)
412 AND (X_Attribute14 IS NULL)))
413 AND ( (Recinfo.attribute15 = X_Attribute15)
414 OR ( (Recinfo.attribute15 IS NULL)
415 AND (X_Attribute15 IS NULL)))
416 AND ( (Recinfo.context = X_Context)
417 OR ( (Recinfo.context IS NULL)
418 AND (X_Context IS NULL)))
419 AND ( (Recinfo.dispatcher_orig_syst = X_dispatcher_orig_syst)
420 OR ( (Recinfo.dispatcher_orig_syst IS NULL)
421 AND (X_dispatcher_orig_syst IS NULL)))
422 AND ( (Recinfo.dispatcher_orig_syst_id = X_dispatcher_orig_syst_id)
423 OR ( (Recinfo.dispatcher_orig_syst_id IS NULL)
424 AND (X_dispatcher_orig_syst_id IS NULL)))
425 AND ( (Recinfo.dispatch_role_name = X_dispatch_role_name)
426 OR ( (Recinfo.dispatch_role_name IS NULL)
427 AND (X_dispatch_role_name IS NULL)))
428
429
430 ) THEN
431 RETURN;
432 ELSE
433 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
434 APP_EXCEPTION.Raise_Exception;
435 END IF;
436 END Lock_Row;
437
438
439 PROCEDURE Update_Row(X_Rowid VARCHAR2,
440 X_Incident_Action_Id NUMBER,
441 X_Last_Update_Date DATE,
442 X_Last_Updated_By NUMBER,
443 X_Last_Update_Login NUMBER DEFAULT NULL,
444 X_Incident_Id NUMBER,
445 X_Action_Code VARCHAR2,
446 X_Action_Num NUMBER DEFAULT NULL,
447 X_Action_Type_Id NUMBER DEFAULT NULL,
448 X_Action_Status_Id NUMBER DEFAULT NULL,
449 X_Responsible_Person_Id NUMBER DEFAULT NULL,
450 X_Text VARCHAR2 DEFAULT NULL,
451 X_Completion_Date DATE DEFAULT NULL,
452 X_actual_time NUMBER DEFAULT NULL,
453 X_Action_Severity_Id NUMBER DEFAULT NULL,
454 X_Text_Description VARCHAR2 DEFAULT NULL,
455 X_Text_Resolution VARCHAR2 DEFAULT NULL,
456 X_Action_Effective_Date DATE DEFAULT NULL,
457 X_Expected_Resolution_Date DATE DEFAULT NULL,
458 X_Attribute1 VARCHAR2 DEFAULT NULL,
459 X_Attribute2 VARCHAR2 DEFAULT NULL,
460 X_Attribute3 VARCHAR2 DEFAULT NULL,
461 X_Attribute4 VARCHAR2 DEFAULT NULL,
462 X_Attribute5 VARCHAR2 DEFAULT NULL,
463 X_Attribute6 VARCHAR2 DEFAULT NULL,
464 X_Attribute7 VARCHAR2 DEFAULT NULL,
465 X_Attribute8 VARCHAR2 DEFAULT NULL,
466 X_Attribute9 VARCHAR2 DEFAULT NULL,
467 X_Attribute10 VARCHAR2 DEFAULT NULL,
468 X_Attribute11 VARCHAR2 DEFAULT NULL,
469 X_Attribute12 VARCHAR2 DEFAULT NULL,
470 X_Attribute13 VARCHAR2 DEFAULT NULL,
471 X_Attribute14 VARCHAR2 DEFAULT NULL,
472 X_Attribute15 VARCHAR2 DEFAULT NULL,
473 X_Context VARCHAR2 DEFAULT NULL,
474 X_Orig_Action_Severity_Id NUMBER DEFAULT NULL,
475 X_Orig_Action_Type_Id NUMBER DEFAULT NULL,
476 X_Orig_Action_Status_id NUMBER DEFAULT NULL,
477 X_Orig_Action_Owner_id NUMBER DEFAULT NULL,
478 X_Orig_Expected_Date DATE DEFAULT NULL,
479 X_Open_Flag VARCHAR2 DEFAULT NULL,
480 X_dispatcher_orig_syst VARCHAR2 DEFAULT NULL,
481 X_dispatcher_orig_syst_id NUMBER DEFAULT NULL,
482 X_dispatch_role_name VARCHAR2 DEFAULT NULL
483 ) IS
484
485 BEGIN
486
487 UPDATE cs_incident_actions
488 SET
489 incident_action_id = X_Incident_Action_Id,
490 last_update_date = trunc(sysdate),
491 last_updated_by = X_Last_Updated_By,
492 last_update_login = X_Last_Update_Login,
496 action_type_id = X_Action_Type_Id,
493 incident_id = X_Incident_Id,
494 action_code = X_Action_Code,
495 action_num = X_Action_Num,
497 action_status_id = X_Action_Status_Id,
498 responsible_person_id = X_Responsible_Person_Id,
499 text = X_Text,
500 completion_date = X_Completion_Date,
501 actual_time = X_actual_time,
502 action_severity_id = X_Action_Severity_Id,
503 text_description = X_Text_Description,
504 text_resolution = X_Text_Resolution,
505 action_effective_date = X_Action_Effective_Date,
506 expected_resolution_date = X_Expected_Resolution_Date,
507 attribute1 = X_Attribute1,
508 attribute2 = X_Attribute2,
509 attribute3 = X_Attribute3,
510 attribute4 = X_Attribute4,
511 attribute5 = X_Attribute5,
512 attribute6 = X_Attribute6,
513 attribute7 = X_Attribute7,
514 attribute8 = X_Attribute8,
515 attribute9 = X_Attribute9,
516 attribute10 = X_Attribute10,
517 attribute11 = X_Attribute11,
518 attribute12 = X_Attribute12,
519 attribute13 = X_Attribute13,
520 attribute14 = X_Attribute14,
521 attribute15 = X_Attribute15,
522 context = X_Context,
523 open_flag = X_Open_Flag ,
524 dispatcher_orig_syst = X_Dispatcher_orig_syst,
525 dispatcher_orig_syst_id = X_Dispatcher_orig_syst_id,
526 dispatch_role_name = X_Dispatch_role_name
527 WHERE rowid = X_Rowid;
528
529 if (SQL%NOTFOUND) then
530 Raise NO_DATA_FOUND;
531 end if;
532
533 /* insert a record into the incident action audit table */
534
535 Process_Action_Audit( X_Last_Update_Date ,
536 X_Last_Updated_By ,
537 X_Last_Update_Login ,
538 X_Incident_Action_Id ,
539 X_Incident_Id ,
540 X_Responsible_Person_Id ,
541 X_Action_Status_Id ,
542 X_Action_Severity_Id ,
543 X_Expected_Resolution_Date ,
544 X_Orig_Action_Severity_Id ,
545 X_Orig_Action_Type_Id ,
546 X_Orig_Action_Status_Id ,
547 X_Orig_Action_Owner_Id ,
548 X_Action_Type_Id ,
549 X_Orig_Expected_Date );
550
551 END Update_Row;
552
553
554 PROCEDURE Process_Action_Audit
555 ( X_Last_Update_Date DATE,
556 X_Last_Updated_By NUMBER,
557 X_Last_Update_Login NUMBER,
558 X_Incident_Action_Id NUMBER,
559 X_Incident_Id NUMBER,
560 X_Action_Owner_Id NUMBER DEFAULT NULL,
561 X_Action_Status_Id NUMBER DEFAULT NULL,
562 X_Action_Severity_Id NUMBER DEFAULT NULL,
563 X_Expected_Resolution_Date DATE DEFAULT NULL,
564 X_Orig_Action_Severity_Id NUMBER DEFAULT NULL,
565 X_Orig_Action_Type_Id NUMBER DEFAULT NULL,
566 X_Orig_Action_Status_Id NUMBER DEFAULT NULL,
567 X_Orig_Action_Owner_Id NUMBER DEFAULT NULL,
568 X_Action_Type_Id NUMBER DEFAULT NULL,
569 X_Orig_Expected_Date DATE DEFAULT NULL ) IS
570
571 X_Action_Response_Flag CHAR := 'N';
572 X_Change_Action_Type_Flag CHAR := 'N';
573 X_Change_Action_Status_Flag CHAR := 'N';
574 X_Change_Action_Owner_Flag CHAR := 'N';
575 X_Change_Action_Severity_Flag CHAR := 'N';
576 X_Change_Expected_Date_Flag CHAR := 'N';
577 X_Action_Response CHAR := NULL;
578 X_Incident_Action_Audit_Id NUMBER := NULL;
579
580 l_Action_Owner_ID NUMBER := NULL;
581 l_Action_Severity_ID NUMBER := NULL;
582 l_Action_Status_ID NUMBER := NULL;
583 l_Action_Type_ID NUMBER := NULL;
584 l_Expected_Resolution_Date Date := NULL;
585 l_Orig_Action_Owner_ID NUMBER := NULL;
586 l_Orig_Action_Severity_ID NUMBER := NULL;
587 l_Orig_Action_Status_ID NUMBER := NULL;
588 l_Orig_Action_Type_ID NUMBER := NULL;
589 l_Orig_Expected_Date Date := NULL;
590
591 CURSOR C2 IS SELECT cs_incident_action_audit_s.nextval FROM sys.dual;
592
593 BEGIN
594
595 OPEN C2;
596 FETCH C2 INTO X_Incident_Action_Audit_Id;
597 CLOSE C2;
598
599 IF (NOT ( X_Action_Type_ID IS NULL
600 AND X_Orig_Action_Type_ID IS NULL)) AND
601 (X_Action_Type_ID IS NULL OR
602 X_Orig_Action_Type_ID IS NULL OR
603 X_Action_Type_Id <> X_Orig_Action_Type_Id) THEN
604 X_Change_Action_Type_Flag := 'Y';
608
605 l_Action_Type_ID := X_Action_Type_ID;
606 l_Orig_Action_Type_ID := X_Orig_Action_Type_ID;
607 END IF;
609 IF (NOT ( X_Action_Status_ID IS NULL
610 AND X_Orig_Action_Status_ID IS NULL)) AND
611 (X_Action_Status_ID IS NULL OR
612 X_Orig_Action_Status_ID IS NULL OR
613 X_Action_Status_Id <> X_Orig_Action_Status_Id) THEN
614 X_Change_Action_Status_Flag := 'Y';
615 l_Action_Status_ID := X_Action_Status_ID;
616 l_Orig_Action_Status_ID := X_Orig_Action_Status_ID;
617 END IF;
618
619 IF (NOT ( X_Action_Owner_ID IS NULL
620 AND X_Orig_Action_Owner_ID IS NULL)) AND
621 (X_Action_Owner_ID IS NULL OR
622 X_Orig_Action_Owner_ID IS NULL OR
623 X_Action_Owner_Id <> X_Orig_Action_Owner_Id) THEN
624 X_Change_Action_Owner_Flag := 'Y';
625 l_Action_Owner_ID := X_Action_Owner_ID;
626 l_Orig_Action_Owner_ID := X_Orig_Action_Owner_ID;
627 END IF;
628
629 IF (NOT ( X_Action_Severity_ID IS NULL
630 AND X_Orig_Action_Severity_ID IS NULL)) AND
631 (X_Action_Severity_ID IS NULL OR
632 X_Orig_Action_Severity_ID IS NULL OR
633 X_Action_Severity_Id <> X_Orig_Action_Severity_Id) THEN
634 X_Change_Action_Severity_Flag := 'Y';
635 l_Action_Severity_ID := X_Action_Severity_ID;
636 l_Orig_Action_Severity_ID := X_Orig_Action_Severity_ID;
637 END IF;
638
639 IF (NOT ( X_Expected_Resolution_Date IS NULL
640 AND X_Orig_Expected_Date IS NULL)) AND
641 (X_Expected_Resolution_Date IS NULL OR
642 X_Orig_Expected_Date IS NULL OR
643 X_Expected_Resolution_Date <> X_Orig_Expected_Date) THEN
644 X_Change_Action_Severity_Flag := 'Y';
645 l_Expected_Resolution_Date := X_Expected_Resolution_Date;
646 l_Orig_Expected_Date := X_Orig_Expected_Date;
647 END IF;
648
649 IF X_Change_Action_Type_Flag = 'Y' OR
650 X_Change_Action_Status_Flag = 'Y' OR
651 X_Change_Action_Owner_Flag = 'Y' OR
652 X_Change_Action_Severity_Flag = 'Y' OR
653 X_Change_Expected_Date_Flag = 'Y' THEN
654
655 INSERT INTO cs_incident_action_audit(
656 incident_action_audit_id,
657 last_update_date,
658 last_updated_by,
659 creation_date,
660 created_by,
661 last_update_login,
662 incident_action_id,
663 incident_id,
664 action_owner_id,
665 action_response_flag,
666 action_status_id,
667 action_severity_id,
668 change_action_type_flag,
669 old_action_severity_id,
670 old_action_type_id,
671 old_action_status_id,
672 old_action_owner_id,
673 change_action_status_flag,
674 change_action_owner_flag,
675 action_type_id,
676 action_response,
677 change_expected_date_flag,
678 expected_resolution_date,
679 old_expected_resolution_date
680 ) VALUES (
681 X_Incident_Action_Audit_Id,
682 X_last_update_date,
683 X_last_updated_by,
684 X_last_update_date,
685 X_last_updated_by,
686 X_last_update_login,
687 X_Incident_Action_Id,
688 X_Incident_Id,
689 l_Action_Owner_Id,
690 X_Action_Response_Flag,
691 l_Action_Status_Id,
692 l_Action_Severity_Id,
693 X_Change_Action_Type_Flag,
694 l_Orig_Action_Severity_Id,
695 l_Orig_Action_Type_Id,
696 l_Orig_Action_Status_Id,
697 l_Orig_Action_Owner_Id,
698 X_Change_Action_Status_Flag,
699 X_Change_Action_Owner_Flag,
700 l_Action_Type_Id,
701 X_Action_Response,
702 X_Change_Expected_Date_Flag,
703 l_Expected_Resolution_Date,
704 l_Orig_Expected_Date );
705 END IF;
706
707 END Process_Action_Audit;
708
709
710 PROCEDURE Select_Summary(X_INCIDENT_ID NUMBER,
711 X_TOTAL IN OUT NUMBER,
712 X_TOTAL_RTOT_DB IN OUT NUMBER) IS
713 BEGIN
714
715 SELECT NVL(SUM(ACTUAL_TIME), 0)
716 INTO X_TOTAL
717 FROM CS_INCIDENT_ACTIONS
718 WHERE INCIDENT_ID = X_INCIDENT_ID;
719
720 X_TOTAL_RTOT_DB := X_TOTAL;
721
722 END Select_Summary;
723
724 END CS_INCIDENT_ACTIONS_PKG ;