1 PACKAGE BODY CS_SR_WORKITEM_PVT AS
2 /* $Header: csvsrwib.pls 120.6 2006/03/08 17:36:02 spusegao ship $ */
3
4
5 PROCEDURE Create_Workitem(
6 p_api_version IN NUMBER,
7 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
8 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
9 p_incident_id IN NUMBER,
10 p_incident_number IN VARCHAR2,
11 p_sr_rec IN CS_ServiceRequest_PVT.service_request_rec_type,
12 --p_owner_group_id IN NUMBER DEFAULT NULL,
13 --p_owner_group_type IN VARCHAR2 DEFAULT NULL,
14 --p_individual_owner_id IN NUMBER DEFAULT NULL,
15 --p_individual_owner_type IN VARCHAR2 DEFAULT NULL,
16 --p_incident_status_id IN NUMBER,
17 --p_incident_severity_id IN NUMBER,
18 --p_customer_id IN NUMBER,
19 --p_summary IN VARCHAR2,
20 --p_responded_by_date IN DATE DEFAULT NULL,
21 --p_obligation_date IN DATE DEFAULT NULL,
22 --p_expected_resolution_date IN DATE DEFAULT NULL,
23 p_user_id IN NUMBER, -- Required
24 p_resp_appl_id IN NUMBER, -- Required
25 p_login_id IN NUMBER DEFAULT NULL,
26 x_work_item_id OUT NOCOPY NUMBER,
27 x_return_status OUT NOCOPY VARCHAR2,
28 x_msg_count OUT NOCOPY NUMBER,
29 x_msg_data OUT NOCOPY VARCHAR2) IS
30
31 l_priority_code VARCHAR2(30);
32 l_due_date DATE;
33 l_return_status VARCHAR2(1);
34 l_work_item_id NUMBER;
35 l_wi_status VARCHAR2(30);
36 l_close_flag VARCHAR2(1);
37 l_API_ERROR EXCEPTION;
38
39 l_owner_group_id NUMBER;
40 l_owner_group_type cs_incidents_all_b.GROUP_TYPE%TYPE;
41 l_individual_owner_id NUMBER;
42 l_individual_owner_type cs_incidents_all_b.RESOURCE_TYPE%TYPE;
43 l_responded_by_date DATE;
44 l_obligation_date DATE;
45 l_expected_resolution_date DATE;
46 l_login_id NUMBER;
47 l_resp_appl_id NUMBER := p_resp_appl_id;
48 l_user_id NUMBER := p_user_id;
49 l_sr_activation_status VARCHAR2(3) := 'N';
50 l_change_wi_flag VARCHAR2(3);
51
52 CURSOR sel_status_csr IS
53 SELECT decode(on_hold_flag, 'Y', 'SLEEP', 'OPEN') wi_status,
54 nvl(close_flag,'N')
55 FROM cs_incident_statuses_b
56 WHERE incident_status_id = p_sr_rec.status_id;
57
58 cursor sel_sr_wi_csr is
59 select work_item_id
60 from ieu_uwqm_items
61 where WORKITEM_OBJ_CODE = 'SR'
62 and WORKITEM_PK_ID = p_incident_id;
63
64 begin
65
66 --dbms_output.put_line('Start of Create_Workitem() ');
67
68 --dbms_output.put_line('owner_group_id:' || p_sr_rec.owner_group_id );
69 --dbms_output.put_line('owner_group_type:' || p_sr_rec.group_type );
70
71 x_return_status := FND_API.G_RET_STS_SUCCESS;
72
73 -- First thing to do is check if user wants to use UWQ assignment.
74 -- See if service request as a work source is activated.
75
76 IEU_WR_PUB.CHECK_WS_ACTIVATION_STATUS
77 ( p_api_version => 1.0,
78 p_init_msg_list => fnd_api.g_false,
79 p_commit => fnd_api.g_false,
80 p_ws_code => 'SR',
81 x_ws_activation_status => l_sr_activation_status,
82 x_msg_count => x_msg_count,
83 x_msg_data => x_msg_data,
84 x_return_status => l_return_status);
85
86 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
87 l_sr_activation_status := 'N' ;
88 END IF;
89 IF (NVL(l_sr_activation_status,'N') = 'Y') THEN
90
91 -- Obtain 'Close Flag' and Work Item Status
92 OPEN sel_status_csr;
93 FETCH sel_status_csr
94 INTO l_wi_status, l_close_flag;
95 CLOSE sel_status_csr;
96
97 -- See if work item already exists for SR.
98 open sel_sr_wi_csr;
99 fetch sel_sr_wi_csr into l_work_item_id;
100
101 IF (sel_sr_wi_csr%NOTFOUND AND l_close_flag = 'N') THEN
102
103 --dbms_output.put_line('Create_Workitem(), Preparing to call CREATE_WR_ITEM ');
104
105 -- Default all optional fields. UWQ does not have concept of missing
106 -- paramaters , G_MISS_CHAR, etc. So we will have to default to FALSE.
107
108 --dbms_output.put_line('In Workitem API');
109 --dbms_output.put_line(' Owner Id : '||p_sr_rec.owner_id);
110 --dbms_output.put_line('Owner Type :'||p_sr_rec.resource_type);
111 --dbms_output.put_line('Owner Group Type :'||p_sr_rec.group_type);
112 --dbms_output.put_line('Owner Group ID :'||p_sr_rec.owner_group_id);
113
114 IF (p_sr_rec.group_type = FND_API.G_MISS_CHAR) THEN
115 l_owner_group_type := NULL;
116 ELSE
117 l_owner_group_type := p_sr_rec.group_type;
118 END IF;
119
120 IF (p_sr_rec.owner_group_id = FND_API.G_MISS_NUM) THEN
121 l_owner_group_id := NULL;
122 l_owner_group_type := NULL;
123 ELSIF p_sr_rec.owner_group_id IS NULL THEN
124 l_owner_group_id := NULL;
125 l_owner_group_type := NULL;
126 ELSE
127 l_owner_group_id := p_sr_rec.owner_group_id;
128 END IF;
129
130 IF (p_sr_rec.resource_type = FND_API.G_MISS_CHAR) THEN
131 l_individual_owner_type := NULL;
132 ELSE
133 l_individual_owner_type := p_sr_rec.resource_type;
134 END IF;
135
136 IF (p_sr_rec.owner_id = FND_API.G_MISS_NUM) THEN
137 l_individual_owner_id := NULL;
138 l_individual_owner_type := NULL;
139 ELSIF p_sr_rec.owner_id IS NULL THEN
140 l_individual_owner_id := NULL;
141 l_individual_owner_type := NULL;
142 ELSE
143 l_individual_owner_id := p_sr_rec.owner_id;
144 END IF;
145
146 --dbms_output.put_line('After Reassignment');
147 --dbms_output.put_line(' Owner Id : '||l_individual_owner_id);
148 --dbms_output.put_line('Owner Type :'||l_individual_owner_type);
149 --dbms_output.put_line('Owner Group Type :'||l_owner_group_type);
150 --dbms_output.put_line('Owner Group ID :'||l_owner_group_id);
151
152 Apply_Priority_Rule
153 (P_New_Inc_Responded_By_Date => p_sr_rec.inc_responded_by_date,
154 P_New_Obligation_Date => p_sr_rec.obligation_date,
155 P_New_Exp_Resolution_Date => p_sr_rec.exp_resolution_date,
156 P_New_Severity_id => p_sr_rec.severity_id ,
157 P_Old_Inc_Responded_By_Date => NULL,
158 P_Old_Obligation_Date => NULL,
159 P_Old_Exp_Resolution_Date => NULL,
160 P_Old_Severity_id => NULL,
161 P_Operation_mode => 'CREATE',
162 X_Change_WI_Flag => l_change_wi_flag,
163 X_Due_Date => l_due_date,
164 X_Priority_Code => l_priority_code,
165 X_Return_Status => X_Return_Status,
166 X_Msg_Count => X_Msg_Count,
167 X_Msg_Data => X_Msg_Data);
168
169
170 IF (p_login_id = FND_API.G_MISS_NUM) THEN
171 l_login_id := NULL;
172 ELSE
173 l_login_id := p_login_id;
174 END IF;
175
176 --dbms_output.put_line('B4 calling IEU_WR_PUB.CREATE_WR_ITEM');
177 --dbms_output.put_line('l_owner_group_id:'||l_owner_group_id||' , l_owner_group_type:'|| l_owner_group_type);
178
179 -- Default these values since NULL not allowed in UWQ.
180 IF (p_resp_appl_id IS NULL) THEN
181 l_resp_appl_id := FND_GLOBAL.RESP_APPL_ID;
182 END IF;
183 IF (p_user_id IS NULL) THEN
184 l_user_id := FND_GLOBAL.USER_ID;
185 END IF;
186
187 IEU_WR_PUB.CREATE_WR_ITEM(
188 p_api_version => 1.0,
189 p_init_msg_list => p_init_msg_list,
190 p_commit => p_commit,
191 p_workitem_obj_code => 'SR',
192 p_workitem_pk_id => p_incident_id,
193 p_work_item_number => p_incident_number,
194 p_title => p_sr_rec.summary,
195 p_party_id => p_sr_rec.customer_id,
196 p_priority_code => l_priority_code,
197 p_due_date => l_due_date,
198 p_owner_id => l_owner_group_id,
199 p_owner_type => l_owner_group_type,
200 p_assignee_id => l_individual_owner_id,
201 p_assignee_type => l_individual_owner_type,
202 p_source_object_id => null,
203 p_source_object_type_code => null,
204 p_application_id => l_resp_appl_id,
205 p_ieu_enum_type_uuid => 'SR',
206 p_work_item_status => l_wi_status,
207 p_user_id => l_user_id,
208 p_login_id => l_login_id,
209 x_work_item_id => x_work_item_id,
210 x_msg_count => x_msg_count,
211 x_msg_data => x_msg_data,
212 x_return_status => l_return_status);
213
214 --dbms_output.put_line('After calling IEU_WR_PUB.CREATE_WR_ITEM');
215 --dbms_output.put_line('l_return_status:' ||l_return_status );
216
217 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
218 x_return_status := l_return_status;
219 raise l_API_ERROR;
220 END IF;
221
222 END IF; -- sel_sr_wi_csr%NOTFOUND
223
224 CLOSE sel_sr_wi_csr;
225
226 END IF; -- FND_PROFILE.value('CS_SR_ENABLE_UWQ_WORKITEM' = 'Y'
227
228 IF FND_API.To_Boolean(p_commit) THEN
229 COMMIT WORK;
230 END IF;
231
232
233 EXCEPTION
234
235 WHEN l_API_ERROR THEN
236 IF (sel_sr_wi_csr%ISOPEN) THEN
237 CLOSE sel_sr_wi_csr;
238 END IF;
239 FND_MSG_PUB.Count_And_Get
240 ( p_count => x_msg_count,
241 p_data => x_msg_data );
242
243 WHEN FND_API.G_EXC_ERROR THEN
244 IF (sel_sr_wi_csr%ISOPEN) THEN
245 CLOSE sel_sr_wi_csr;
246 END IF;
247 x_return_status := FND_API.G_RET_STS_ERROR;
248 FND_MSG_PUB.Count_And_Get
249 ( p_count => x_msg_count,
250 p_data => x_msg_data );
251
252 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
253 IF (sel_sr_wi_csr%ISOPEN) THEN
254 CLOSE sel_sr_wi_csr;
255 END IF;
256 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
257 FND_MSG_PUB.Count_And_Get
258 ( p_count => x_msg_count,
259 p_data => x_msg_data );
260
261 WHEN OTHERS THEN
262 IF (sel_sr_wi_csr%ISOPEN) THEN
263 CLOSE sel_sr_wi_csr;
264 END IF;
265 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
266 FND_MSG_PUB.Count_And_Get
267 ( p_count => x_msg_count,
268 p_data => x_msg_data );
269
270 END Create_Workitem;
271
272
273 /******************************************************
274 Update_Workitem() -
275 ******************************************************/
276
277 PROCEDURE Update_Workitem(
278 p_api_version IN NUMBER,
279 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
280 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
281 p_incident_id IN NUMBER,
282 p_old_sr_rec IN CS_ServiceRequest_PVT.sr_oldvalues_rec_type,
283 p_new_sr_rec IN CS_ServiceRequest_PVT.service_request_rec_type,
284 p_user_id IN NUMBER, -- Required
285 p_resp_appl_id IN NUMBER, -- Required
286 p_login_id IN NUMBER DEFAULT NULL,
287 x_return_status OUT NOCOPY VARCHAR2,
288 x_msg_count OUT NOCOPY NUMBER,
289 x_msg_data OUT NOCOPY VARCHAR2) IS
290
291 l_return_status VARCHAR2(1);
292 l_change_wi_attr VARCHAR2(1) := 'N';
293 l_change_wi_attr1 VARCHAR2(1) := 'N';
294 l_old_priority cs_incident_severities_b.priority_code%TYPE;
295 l_new_priority cs_incident_severities_b.priority_code%TYPE;
296 l_old_close_flag cs_incident_statuses_b.close_flag%TYPE;
297 l_new_close_flag cs_incident_statuses_b.close_flag%TYPE;
298 l_old_on_hold_flag cs_incident_statuses_b.on_hold_flag%TYPE;
299 l_new_on_hold_flag cs_incident_statuses_b.on_hold_flag%TYPE;
300 l_work_item_status VARCHAR2(25);
301 l_incident_number VARCHAR2(64);
302 l_due_date DATE;
303 l_work_item_id NUMBER;
304
305 l_owner_id NUMBER;
306 l_resource_type cs_incidents_all_b.RESOURCE_TYPE%TYPE;
307 l_owner_group_id NUMBER;
308 l_group_type cs_incidents_all_b.GROUP_TYPE%TYPE;
309 l_summary cs_incidents_all_tl.SUMMARY%TYPE;
310 l_customer_id NUMBER;
311 l_priority cs_incident_severities_b.priority_code%TYPE;
312 l_new_status_id NUMBER;
313 l_new_severity_id NUMBER;
314 l_inc_responded_by_date DATE;
315 l_obligation_date DATE;
316 l_exp_resolution_date DATE;
317 l_API_ERROR EXCEPTION;
318 l_sr_rec CS_ServiceRequest_PVT.service_request_rec_type;
319 l_resp_appl_id NUMBER := p_resp_appl_id;
320 l_user_id NUMBER := p_user_id;
321 l_sr_activation_status VARCHAR2(3) := 'N' ;
322
323 cursor sel_sr_wi_csr is
324 select work_item_id
325 from ieu_uwqm_items
326 where WORKITEM_OBJ_CODE = 'SR'
327 and WORKITEM_PK_ID = p_incident_id;
328
329 cursor sel_status_flags_csr(l_status_id IN NUMBER) IS
330 SELECT nvl(close_flag,'N'),
331 nvl(on_hold_flag,'N')
332 FROM cs_incident_statuses_b
333 WHERE incident_status_id = l_status_id;
334
335 CURSOR sel_incident_number_csr IS
336 SELECT incident_number
337 FROM cs_incidents_all_b
338 WHERE incident_id = p_incident_id;
339
340
341 BEGIN
342
343 x_return_status := FND_API.G_RET_STS_SUCCESS;
344
345 -- First thing to do is check if user wants to use UWQ assignment.
346
347 -- See if service request as a work source is activated.
348
349 IEU_WR_PUB.CHECK_WS_ACTIVATION_STATUS
350 ( p_api_version => 1.0,
351 p_init_msg_list => fnd_api.g_false,
352 p_commit => fnd_api.g_false,
353 p_ws_code => 'SR',
354 x_ws_activation_status => l_sr_activation_status,
355 x_msg_count => x_msg_count,
356 x_msg_data => x_msg_data,
357 x_return_status => l_return_status);
358
359 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
360 l_sr_activation_status := 'N' ;
361 END IF;
362
363 IF (NVL(l_sr_activation_status,'N') = 'Y') THEN
364
365 -- Next we find out if only significant attributes changed that
366 -- will require a change in the work item.
367
368 -- p_new_sr_rec.owner_id could be G_MISS_NUM, so for unchanged field,
369 -- we need to set from the old values.
370 -- Also, CS store G_MISS_NUM, G_MISS_CHAR, G_MISS_DATE in tables
371 -- so we have to default these values to null since UWQ does not
372 -- interpret these values correctly.
373
374 IF (Is_Value_Changed(p_old_sr_rec.incident_owner_id,
375 p_new_sr_rec.owner_id)) THEN
376 l_owner_id := p_new_sr_rec.owner_id;
377 l_change_wi_attr := 'Y';
378 ELSE
379 l_owner_id := p_old_sr_rec.incident_owner_id;
380 END IF;
381
382 IF (Is_Value_Changed(p_old_sr_rec.resource_type,
383 p_new_sr_rec.resource_type)) THEN
384 l_resource_type := p_new_sr_rec.resource_type;
385 l_change_wi_attr := 'Y';
386 ELSE
387 l_resource_type := p_old_sr_rec.resource_type;
388 END IF;
389
390 IF (Is_Value_Changed(p_old_sr_rec.owner_group_id,
391 p_new_sr_rec.owner_group_id)) THEN
392 l_owner_group_id := p_new_sr_rec.owner_group_id;
393 l_change_wi_attr := 'Y';
394 ELSE
395 l_owner_group_id := p_old_sr_rec.owner_group_id;
396 END IF;
397
398 --dbms_output.put_line('l_owner_group_id:'||l_owner_group_id);
399
400 IF (Is_Value_Changed(p_old_sr_rec.group_type,
401 p_new_sr_rec.group_type)) THEN
402 l_group_type := p_new_sr_rec.group_type;
403 l_change_wi_attr := 'Y';
404 ELSE
405 l_group_type := p_old_sr_rec.group_type;
406 END IF;
407
408 --dbms_output.put_line('l_group_type:'||l_group_type);
409
410 IF (Is_Value_Changed(p_old_sr_rec.summary, p_new_sr_rec.summary)) THEN
411 l_summary := p_new_sr_rec.summary;
412 l_change_wi_attr := 'Y';
413 ELSE
414 l_summary := p_old_sr_rec.summary;
415 END IF;
416
417 IF (Is_Value_Changed(p_old_sr_rec.customer_id,
418 p_new_sr_rec.customer_id)) THEN
419 l_customer_id := p_new_sr_rec.customer_id;
420 l_change_wi_attr := 'Y';
421 ELSE
422 l_customer_id := p_old_sr_rec.customer_id;
423 END IF;
424
425 -- SR record may not pass new severity id since it may not have changed.
426 IF (p_new_sr_rec.severity_id = FND_API.G_MISS_NUM) THEN
427 l_new_priority := l_old_priority;
428 l_new_severity_id := p_old_sr_rec.incident_severity_id;
429 ELSE
430 l_new_severity_id := p_new_sr_rec.severity_id;
431 END IF;
432
433 -- Determine if we need to update Work Item Due Date
434 -- Check if inc_responded_by_date changed from NULL to
435 -- NOT NULL and vice versa.
436
437 Apply_Priority_Rule
438 (P_New_Inc_Responded_By_Date => p_new_sr_rec.inc_responded_by_date,
439 P_New_Obligation_Date => p_new_sr_rec.obligation_date,
440 P_New_Exp_Resolution_Date => p_new_sr_rec.exp_resolution_date,
441 P_New_Severity_Id => p_new_sr_rec.severity_id,
442 P_Old_Inc_Responded_By_Date => p_old_sr_rec.inc_responded_by_date,
443 P_Old_Obligation_Date => p_old_sr_rec.obligation_date,
444 P_Old_Exp_Resolution_Date => p_old_sr_rec.expected_resolution_date,
445 P_Old_Severity_Id => p_old_sr_rec.incident_severity_id,
446 P_Operation_mode => 'UPDATE',
447 X_Change_WI_Flag => l_change_wi_attr1,
448 X_Due_Date => l_due_date,
449 X_Priority_Code => l_priority,
450 X_Return_Status => X_Return_Status,
451 X_Msg_Count => X_Msg_Count,
452 X_Msg_Data => X_Msg_Data);
453
454
455 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
456 IF l_change_wi_attr1 = 'Y' THEN
457 l_change_wi_attr := l_change_wi_attr1;
458 END IF;
459 ELSE
460 x_return_status := l_return_status;
461 raise l_API_ERROR;
462 END IF;
463
464 -- Status may not have changed, but the Status Attributes:
465 -- Closed Flag or On Hold Flag could have changed.
466 -- Or Status may have changed but it's attributes did not,
467 -- so no need to call Update work item API.
468
469 OPEN sel_status_flags_csr(p_old_sr_rec.incident_status_id);
470 FETCH sel_status_flags_csr
471 INTO l_old_close_flag, l_old_on_hold_flag;
472 -- The Status might have been deleted in the set-up form. Not sure
473 -- if this is a valid scenario.
474 IF sel_status_flags_csr%NOTFOUND THEN
475 NULL;
476 END IF;
477 CLOSE sel_status_flags_csr;
478
479 -- SR record may not pass new status id since it may not have changed.
480 IF (p_new_sr_rec.status_id = FND_API.G_MISS_NUM) THEN
481 l_new_status_id := p_old_sr_rec.incident_status_id;
482 l_new_close_flag := l_old_close_flag;
483 l_new_on_hold_flag := l_old_on_hold_flag;
484 ELSE
485 l_new_status_id := p_new_sr_rec.status_id;
486 OPEN sel_status_flags_csr(p_new_sr_rec.status_id);
487 FETCH sel_status_flags_csr
488 INTO l_new_close_flag, l_new_on_hold_flag;
489 CLOSE sel_status_flags_csr;
490 END IF;
491
492 -- This check needs to go first . When changing to Status with 'Close'
493 -- and 'On Hold' flag turned on, Work Item Status should be
494 -- 'CLOSE', not 'SLEEP'.
495
496 IF (l_old_close_flag <> l_new_close_flag) THEN
497
498 l_change_wi_attr := 'Y';
499 IF (l_new_close_flag = 'Y') THEN
500 l_work_item_status := 'CLOSE';
501 ELSIF (l_new_on_hold_flag = 'Y') THEN
502 l_work_item_status := 'SLEEP';
503 ELSE
504 l_work_item_status := 'OPEN';
505 END IF;
506
507 ELSE
508
509 IF (l_new_on_hold_flag = 'Y') THEN
510 l_work_item_status := 'SLEEP';
511 ELSE
512 l_work_item_status := 'OPEN';
513 END IF;
514 IF (l_old_on_hold_flag <> l_new_on_hold_flag) THEN
515 l_change_wi_attr := 'Y';
516 END IF;
517
518 END IF;
519
520
521 -- CS stores G_MISS_NUM, G_MISS_CHAR, G_MISS_DATE in tables
522 -- so we have to default these values to null since UWQ does not
523 -- interpret these values correctly.
524
525 IF (l_resource_type = FND_API.G_MISS_CHAR) THEN
526 l_resource_type := NULL;
527 END IF;
528
529 IF (l_owner_id = FND_API.G_MISS_NUM) THEN
530 l_owner_id := NULL;
531 l_resource_type := NULL;
532 ELSIF l_owner_id IS NULL THEN
533 l_resource_type := NULL;
534 END IF;
535
536 IF (l_group_type = FND_API.G_MISS_CHAR) THEN
537 l_group_type := NULL;
538 END IF;
539
540 IF (l_owner_group_id = FND_API.G_MISS_NUM) THEN
541 l_owner_group_id := NULL;
542 l_group_type := NULL;
543 ELSIF l_owner_group_id IS NULL THEN
544 l_group_type := NULL;
545 END IF;
546
547 IF (l_summary = FND_API.G_MISS_CHAR) THEN
548 l_summary := NULL;
549 END IF;
550 IF (l_customer_id = FND_API.G_MISS_NUM) THEN
551 l_customer_id := NULL;
552 END IF;
553
554 IF (l_priority = FND_API.G_MISS_CHAR) THEN
555 l_priority := NULL;
556 END IF;
557 IF (l_due_date = FND_API.G_MISS_DATE) THEN
558 l_due_date := NULL;
559 END IF;
560
561 /************************************************/
562
563 -- See if work item already exists for SR.
564 open sel_sr_wi_csr;
565 fetch sel_sr_wi_csr into l_work_item_id;
566
567 IF (sel_sr_wi_csr%FOUND AND l_work_item_id IS NOT NULL) THEN
568
569 IF (l_change_wi_attr = 'Y') THEN
570
571 -- Default these values since NULL not allowed in UWQ.
572 IF (p_resp_appl_id IS NULL) THEN
573 l_resp_appl_id := FND_GLOBAL.RESP_APPL_ID;
574 END IF;
575 IF (p_user_id IS NULL) THEN
576 l_user_id := FND_GLOBAL.USER_ID;
577 END IF;
578
579 IEU_WR_PUB.UPDATE_WR_ITEM(
580 p_api_version => 1.0,
581 p_init_msg_list => p_init_msg_list,
582 p_commit => p_commit,
583 p_workitem_obj_code => 'SR',
584 p_workitem_pk_id => p_incident_id,
585 p_title => l_summary,
586 p_party_id => l_customer_id,
587 p_priority_code => l_priority,
588 p_due_date => l_due_date,
589 p_owner_id => l_owner_group_id,
590 p_owner_type => l_group_type,
591 p_assignee_id => l_owner_id,
592 p_assignee_type => l_resource_type,
593 p_source_object_id => NULL,
594 p_source_object_type_code => NULL,
595 p_application_id => l_resp_appl_id,
596 p_work_item_status => l_work_item_status,
597 p_user_id => l_user_id,
598 p_login_id => p_login_id,
599 x_msg_count => x_msg_count,
600 x_msg_data => x_msg_data,
601 x_return_status => l_return_status
602 );
603
604 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
605 x_return_status := l_return_status;
606 raise l_API_ERROR;
607 END IF;
608
609
610 END IF; -- l_change_wi_attr = 'Y'
611
612
613 ELSE -- sel_sr_wi_csr%FOUND OR l_work_item_id IS NOT NULL
614
615 CS_ServiceRequest_PVT.initialize_rec(l_sr_rec);
616
617 OPEN sel_incident_number_csr;
618 FETCH sel_incident_number_csr INTO l_incident_number;
619 CLOSE sel_incident_number_csr;
620
621 l_sr_rec.owner_group_id := l_owner_group_id;
622 l_sr_rec.group_type := l_group_type;
623 l_sr_rec.owner_id := l_owner_id;
624 l_sr_rec.resource_type := l_resource_type;
625 l_sr_rec.status_id := l_new_status_id;
626 l_sr_rec.severity_id := l_new_severity_id;
627 l_sr_rec.customer_id := l_customer_id;
628 l_sr_rec.summary := l_summary;
629 l_sr_rec.inc_responded_by_date := l_inc_responded_by_date;
630 l_sr_rec.obligation_date := l_obligation_date;
631 l_sr_rec.exp_resolution_date := l_exp_resolution_date;
632
633 --dbms_output.put_line('Calling IEU_WR_PUB.CREATE_WR_ITEM');
634
635 --dbms_output.put_line('l_owner_group_id:'||l_owner_group_id);
636 --dbms_output.put_line('l_group_type:'||l_group_type);
637
638 CS_SR_WORKITEM_PVT.Create_Workitem(
639 p_api_version => 1.0,
640 p_init_msg_list => p_init_msg_list,
641 p_commit => p_commit,
642 p_incident_id => p_incident_id,
643 p_incident_number => l_incident_number,
644 p_sr_rec => l_sr_rec,
645 p_user_id => p_user_id,
646 p_resp_appl_id => p_resp_appl_id,
647 p_login_id => p_login_id,
648 x_work_item_id => l_work_item_id,
649 x_return_status => l_return_status,
650 x_msg_count => x_msg_count,
651 x_msg_data => x_msg_data);
652
653
654 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
655 x_return_status := l_return_status;
656 raise l_API_ERROR;
657 END IF;
658
659
660 END IF; -- sel_sr_wi_csr%FOUND OR l_work_item_id IS NOT NULL
661
662 close sel_sr_wi_csr;
663
664 END IF; -- FND_PROFILE.value('CS_SR_ENABLE_UWQ_WORKITEM' = 'Y')
665
666 IF FND_API.To_Boolean(p_commit) THEN
667 COMMIT WORK;
668 END IF;
669
670 EXCEPTION
671
672 WHEN l_API_ERROR THEN
673 IF (sel_sr_wi_csr%ISOPEN) THEN
674 CLOSE sel_sr_wi_csr;
675 END IF;
676 FND_MSG_PUB.Count_And_Get
677 ( p_count => x_msg_count,
678 p_data => x_msg_data );
679
680 WHEN FND_API.G_EXC_ERROR THEN
681 IF (sel_sr_wi_csr%ISOPEN) THEN
682 CLOSE sel_sr_wi_csr;
683 END IF;
684 x_return_status := FND_API.G_RET_STS_ERROR;
685 FND_MSG_PUB.Count_And_Get
686 ( p_count => x_msg_count,
687 p_data => x_msg_data );
688
689 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
690 IF (sel_sr_wi_csr%ISOPEN) THEN
691 CLOSE sel_sr_wi_csr;
692 END IF;
693 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
694 FND_MSG_PUB.Count_And_Get
695 ( p_count => x_msg_count,
696 p_data => x_msg_data );
697
698 WHEN OTHERS THEN
699 IF (sel_sr_wi_csr%ISOPEN) THEN
700 CLOSE sel_sr_wi_csr;
701 END IF;
702 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
703 FND_MSG_PUB.Count_And_Get
704 ( p_count => x_msg_count,
705 p_data => x_msg_data );
706
707 END Update_Workitem;
708
709
710 /******************************************************
711 FUNCTION Is_Value_Changed()
712 ******************************************************/
713
714 FUNCTION Is_Value_Changed(
715 p_old_val IN VARCHAR2,
716 p_new_val IN VARCHAR2) RETURN BOOLEAN IS
717
718 BEGIN
719
720 IF (p_new_val = FND_API.G_MISS_CHAR) THEN
721 return false;
722 ELSIF ( (p_old_val IS NOT NULL AND p_new_val IS NULL) OR
723 (p_old_val IS NULL AND p_new_val IS NOT NULL) OR
724 (p_old_val IS NOT NULL AND p_new_val IS NOT NULL AND
725 p_old_val <> p_new_val) ) THEN
726 return true;
727 ELSE
728 return false;
729 END IF;
730
731 END Is_Value_Changed;
732
733 /******************************************************
734 FUNCTION Is_Value_Changed()
735 ******************************************************/
736
737 FUNCTION Is_Value_Changed(
738 p_old_val IN NUMBER,
739 p_new_val IN NUMBER) RETURN BOOLEAN IS
740
741 BEGIN
742
743 IF (p_new_val = FND_API.G_MISS_NUM) THEN
744 return false;
745 ELSIF ( (p_old_val IS NOT NULL AND p_new_val IS NULL) OR
746 (p_old_val IS NULL AND p_new_val IS NOT NULL) OR
747 (p_old_val IS NOT NULL AND p_new_val IS NOT NULL AND
748 p_old_val <> p_new_val) ) THEN
749 return true;
750 ELSE
751 return false;
752 END IF;
753
754 END Is_Value_Changed;
755
756 /******************************************************
757 FUNCTION Is_Value_Changed()
758 ******************************************************/
759
760 FUNCTION Is_Value_Changed(
761 p_old_val IN DATE,
762 p_new_val IN DATE) RETURN BOOLEAN IS
763
764 BEGIN
765
766 IF (p_new_val = FND_API.G_MISS_DATE) THEN
767 return false;
768 ELSIF ( (p_old_val IS NOT NULL AND p_new_val IS NULL) OR
769 (p_old_val IS NULL AND p_new_val IS NOT NULL) OR
770 (p_old_val IS NOT NULL AND p_new_val IS NOT NULL AND
771 p_old_val <> p_new_val) ) THEN
772 return true;
773 ELSE
774 return false;
775 END IF;
776
777 END Is_Value_Changed;
778
779
780
781
782 /********************************************************
783 Procedure Set priority rules
784 Description : This procedure derives the
785
786 *********************************************************/
787
788 PROCEDURE Apply_Priority_Rule
789 (P_New_Inc_Responded_By_Date IN DATE,
790 P_New_Obligation_Date IN DATE,
791 P_New_Exp_Resolution_Date IN DATE,
792 P_New_Severity_Id IN NUMBER,
793 P_Old_Inc_Responded_By_Date IN DATE,
794 P_Old_Obligation_Date IN DATE,
795 P_Old_Exp_Resolution_Date IN DATE,
796 P_Old_Severity_Id IN NUMBER,
797 P_Operation_mode IN VARCHAR2,
798 X_Change_WI_Flag OUT NOCOPY VARCHAR2,
799 X_Due_Date OUT NOCOPY DATE,
800 X_Priority_Code OUT NOCOPY VARCHAR2,
801 X_Return_Status OUT NOCOPY VARCHAR2,
802 X_Msg_Count OUT NOCOPY NUMBER,
803 X_Msg_Data OUT NOCOPY VARCHAR2) IS
804
805
806 l_new_priority_code cs_incident_severities_b.priority_code%TYPE;
807 l_old_priority_code cs_incident_severities_b.priority_code%TYPE;
808 l_inc_responded_by_date DATE;
809 l_obligation_date DATE;
810 l_exp_resolution_date DATE;
811
812 CURSOR c_get_priority_code (l_severity_id IN NUMBER) IS
813 SELECT priority_code
814 FROM cs_incident_severities_b
815 WHERE incident_severity_id = l_severity_id;
816
817 BEGIN
818
819 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
820
821 IF p_operation_mode = 'CREATE' THEN -- Operation Mode
822
823 IF p_new_inc_responded_by_date = FND_API.G_MISS_DATE THEN
824 l_Inc_responded_by_date := NULL;
825 ELSE
826 l_Inc_responded_by_date := p_new_inc_responded_by_date;
827 END IF;
828
829 IF P_New_obligation_date = FND_API.G_MISS_DATE THEN
830 l_obligation_date := NULL;
831 ELSE
832 l_obligation_date := P_New_obligation_date;
833 END IF;
834
835 IF P_New_exp_resolution_date = FND_API.G_MISS_DATE THEN
836 l_exp_resolution_date := NULL;
837 ELSE
838 l_exp_resolution_date := P_New_exp_resolution_date;
839 END IF;
840
841 IF l_Inc_responded_by_date is NULL THEN
842 X_due_date := l_obligation_date;
843 ELSE
844 X_due_date := l_exp_resolution_date;
845 END IF;
846
847 -- Set the Priority Code
848
849 OPEN c_get_priority_code (P_New_Severity_Id) ;
850 FETCH c_get_priority_code INTO l_new_priority_code;
851 CLOSE c_get_priority_code ;
852
853 X_Priority_Code := l_new_priority_code;
854
855 ELSE --p_operation_mode = 'UPDATE' THEN -- Operation Mode
856
857 IF ((p_old_inc_responded_by_date IS NULL AND
858 p_new_inc_responded_by_date IS NOT NULL AND
859 p_new_inc_responded_by_date <> FND_API.G_MISS_DATE) OR
860 (p_old_inc_responded_by_date IS NOT NULL AND
861 p_new_inc_responded_by_date IS NULL)
862 ) THEN
863
864 --Set value for Due Date work item parameter
865 IF (p_new_inc_responded_by_date IS NULL) THEN
866 X_due_date := p_new_obligation_date;
867 ELSE
868 X_due_date := p_new_exp_resolution_date;
869 END IF;
870
871 X_change_wi_flag := 'Y';
872
873 -- 'Respond On' date may not have changed, but either 'Respond By' or
874 -- 'Resolution By' date did.
875 ELSE
876 IF p_old_inc_responded_by_date IS NULL THEN
877 IF (Is_Value_Changed(p_old_obligation_date, p_new_obligation_date)) THEN
878 X_due_date := p_new_obligation_date;
879 X_change_wi_flag := 'Y';
880 ELSE
881 -- We still need to set due_date to current due_date as required
882 -- in UWQ API and passing null updates UWQ schema to NULL.
883 X_due_date := p_old_obligation_date;
884 END IF;
885 ELSE
886 IF (Is_Value_Changed(p_old_exp_resolution_date,
887 p_new_exp_resolution_date)) THEN
888 X_due_date := p_new_exp_resolution_date;
889 X_change_wi_flag := 'Y';
890 ELSE
891 X_due_date := p_old_exp_resolution_date;
892 END IF;
893 END IF;
894 END IF; -- End of: Determine if we need to update Work Item Due Date.
895
896 -- Derive the responded by and resolve on dates.
897
898 IF p_new_inc_responded_by_date = FND_API.G_MISS_DATE THEN
899 l_inc_responded_by_date := p_old_inc_responded_by_date;
900 ELSE
901 l_inc_responded_by_date := p_new_inc_responded_by_date;
902 END IF;
903
904 IF p_new_obligation_date = FND_API.G_MISS_DATE THEN
905 l_obligation_date := p_old_obligation_date;
906 ELSE
907 l_obligation_date := p_new_obligation_date;
908 END IF;
909
910 IF p_new_exp_resolution_date = FND_API.G_MISS_DATE THEN
911 l_exp_resolution_date := p_old_exp_resolution_date;
912 ELSE
913 l_exp_resolution_date := p_new_exp_resolution_date;
914 END IF;
915
916 -- Set the priority cdoe
917
918 -- Get the priority code for the new severity
919
920 OPEN c_get_priority_code (P_Old_Severity_Id) ;
921 FETCH c_get_priority_code INTO l_Old_priority_code;
922
923 IF c_get_priority_code%NOTFOUND THEN
924 NULL;
925 END IF;
926
927 CLOSE c_get_priority_code ;
928
929 -- SR record may not pass new severity id since it may not have changed.
930 IF p_new_Severity_Id = FND_API.G_MISS_NUM THEN
931 l_new_priority_code := l_old_priority_code;
932 ELSE
933 OPEN c_get_priority_code (P_New_Severity_Id) ;
934 FETCH c_get_priority_code INTO l_new_priority_code;
935 CLOSE c_get_priority_code;
936 END IF ;
937
938 IF (Is_Value_Changed(l_Old_priority_code,l_new_priority_code)) THEN
939 X_change_wi_flag := 'Y';
940 X_Priority_Code := l_New_priority_code;
941 ELSE
942 X_Priority_Code := l_Old_priority_code;
943 END IF ;
944
945 END IF ; -- Operation Mode
946
947 EXCEPTION
948 WHEN OTHERS THEN
949 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
950 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
951 p_data => x_msg_data );
952
953 END Apply_Priority_Rule;
954
955 END CS_SR_WORKITEM_PVT;
[Home] [Help]