[Home] [Help]
PACKAGE BODY: APPS.CUG_GENERIC_WF_PKG
Source
1 Package Body CUG_GENERIC_WF_PKG AS
2 /* $Header: CUGGNWFB.pls 115.29 2003/03/28 19:56:55 rhungund noship $ */
3 --
4 -- To modify this template, edit file PKGBODY.TXT in TEMPLATE
5 -- directory of SQL Navigator
6 --
7 -- Purpose: Briefly explain the functionality of the package body
8 --
9 -- MODIFICATION HISTORY
10 -- Person Date Comments
11 -- --------- ------ ------------------------------------------
12 -- Enter procedure, function bodies as shown below
13
14 PROCEDURE GET_OTHER_SR_ATTRIBUTES(
15 itemtype VARCHAR2,
16 itemkey VARCHAR2,
17 actid NUMBER,
18 funmode VARCHAR2,
19 result OUT NOCOPY VARCHAR2 )
20 IS
21
22 l_install_site_use_id NUMBER;
23 l_incident_location_id NUMBER;
24 l_request_id NUMBER;
25 l_incident_type_id NUMBER;
26 l_tsk_type_id NUMBER;
27
28 l_return_status VARCHAR2(1);
29 l_incident_address VARCHAR2(1000);
30 l_result VARCHAR2(1);
31
32 l_API_ERROR EXCEPTION;
33
34 -- Start of change by ANEEMUCH date 09-May-2002
35 -- Capturing of incident address done in SR Tab
36 /*
37 CURSOR l_ServiceRequest_csr IS
38 SELECT INSTALL_SITE_USE_ID
39 FROM CS_INCIDENTS_ALL_VL
40 WHERE INCIDENT_ID = l_request_id;
41
42 CURSOR l_IncidentAddress_csr IS
43 SELECT ADDRESS1, ADDRESS2, CITY, STATE, COUNTRY, POSTAL_CODE
44 FROM HZ_LOCATIONS WHERE LOCATION_ID = l_install_site_use_id;
45 l_IncidentAddress_rec l_IncidentAddress_csr%ROWTYPE;
46 */
47
48 CURSOR l_ServiceRequest_csr IS
49 SELECT incident_location_id, incident_address, incident_city, incident_state,
50 incident_postal_code, incident_country
51 FROM cs_incidents_all_b
52 WHERE incident_id = l_request_id;
53 l_ServiceRequest_rec l_ServiceRequest_csr%ROWTYPE;
54
55 CURSOR l_IncidentAddress_csr IS
56 SELECT address, city, state, postal_code, country
57 FROM cs_sr_incident_address_v
58 WHERE location_id = l_incident_location_id;
59 l_IncidentAddress_rec l_IncidentAddress_csr%ROWTYPE;
60
61 -- End of changes by ANEEMUCH date 09-May-2002
62
63 CURSOR l_CheckIfSRHdrInfoPresent_csr IS
64 select INCIDENT_TYPE_ID, SR_DUP_CHECK_FLAG from CUG_SR_TYPE_DUP_CHK_INFO
65 WHERE INCIDENT_TYPE_ID = l_incident_type_id;
66 l_CheckIfSRHdrInfoPresent_rec l_CheckIfSRHdrInfoPresent_csr%ROWTYPE;
67
68 CURSOR l_CheckIfSRAttrPresent_csr IS
69 SELECT incidnt_attr_val_id from CUG_INCIDNT_ATTR_VALS_VL
70 WHERE INCIDENT_ID = l_request_id;
71 l_CheckIfSRAttrPresent_rec l_CheckIfSRAttrPresent_csr%ROWTYPE;
72
73 CURSOR l_CheckIfTaskPresent_csr IS
74 SELECT tsk_typ_attr_dep_id FROM CUG_TSK_TYP_ATTR_DEPS_B
75 WHERE incident_type_id = l_incident_type_id;
76
77 BEGIN
78
79 IF (funmode = 'RUN') THEN
80 l_request_id := WF_Engine.GetItemAttrNumber(itemtype => itemtype,
81 itemkey => itemkey,
82 aname => 'REQUEST_ID');
83
84 OPEN l_CheckIfSRAttrPresent_csr;
85 FETCH l_CheckIfSRAttrPresent_csr INTO l_CheckIfSRAttrPresent_rec;
86 IF (l_CheckIfSRAttrPresent_csr%NOTFOUND) THEN
87 SELECT incident_type_id into l_incident_type_id FROM CS_INCIDENTS_ALL_B WHERE
88 incident_id = l_request_id;
89
90 l_result := 'Y';
91 OPEN l_CheckIfSRHdrInfoPresent_csr;
92 FETCH l_CheckIfSRHdrInfoPresent_csr into l_CheckIfSRHdrInfoPresent_rec;
93 IF (l_CheckIfSRHdrInfoPresent_csr%NOTFOUND) THEN
94 l_result := 'N' ;
95 ELSE
96 l_result := 'Y';
97 END IF;
98 CLOSE l_CheckIfSRHdrInfoPresent_csr;
99
100
101 OPEN l_CheckIfTaskPresent_csr;
102 FETCH l_CheckIfTaskPresent_csr into l_tsk_type_id;
103 IF (l_CheckIfTaskPresent_csr%NOTFOUND) THEN
104 IF (l_result = 'N') THEN
105 result := 'N' ;
106 ELSE
107 result := 'Y';
108 END IF;
109 ELSE
110 result := 'Y';
111 END IF;
112 CLOSE l_CheckIfTaskPresent_csr;
113 return;
114 ELSE
115 result := 'Y';
116 END IF;
117
118 -- Start of changes by ANEEMUCH date 09-May-2002
119 /*
120 OPEN l_ServiceRequest_csr;
121 FETCH l_ServiceRequest_csr INTO l_install_site_use_id;
122
123 IF (l_ServiceRequest_csr%NOTFOUND OR
124 l_install_site_use_id is NULL) THEN
125 l_incident_address := ' ';
126 ELSE
127 OPEN l_IncidentAddress_csr;
128 FETCH l_IncidentAddress_csr INTO l_IncidentAddress_rec;
129 IF (l_IncidentAddress_csr%NOTFOUND) THEN
130 RAISE fnd_api.g_exc_unexpected_error;
131 END IF;
132
133 l_incident_address := l_IncidentAddress_rec.address1 || ',' ||
134 l_IncidentAddress_rec.address2 || ',' ||
135 l_IncidentAddress_rec.city || ',' ||
136 l_IncidentAddress_rec.state || ' - '||
137 l_IncidentAddress_rec.postal_code || ',' ||
138 l_IncidentAddress_rec.country;
139 END IF;
140 */
141
142 OPEN l_ServiceRequest_csr;
143 FETCH l_ServiceRequest_csr INTO l_ServiceRequest_rec;
144
145 IF (l_ServiceRequest_rec.incident_location_id IS NULL) THEN
146
147 l_incident_address := l_ServiceRequest_rec.incident_address || ',' ||
148 l_ServiceRequest_rec.incident_city || ',' ||
149 l_ServiceRequest_rec.incident_state || ' - '||
150 l_ServiceRequest_rec.incident_postal_code || ',' ||
151 l_ServiceRequest_rec.incident_country;
152 ELSE
153 /* the following line added to fix bug # 2744139 */
154 l_incident_location_id := l_ServiceRequest_rec.incident_location_id;
155 OPEN l_IncidentAddress_csr;
156 FETCH l_IncidentAddress_csr INTO l_IncidentAddress_rec;
157 IF (l_IncidentAddress_csr%FOUND) THEN
158
159 l_incident_address := l_IncidentAddress_rec.address || ',' ||
160 l_IncidentAddress_rec.city || ',' ||
161 l_IncidentAddress_rec.state || ' - '||
162 l_IncidentAddress_rec.postal_code || ',' ||
163 l_IncidentAddress_rec.country;
164 ELSE
165 l_incident_address := ' ';
166 END IF;
167 CLOSE l_IncidentAddress_csr;
168 END IF;
169 CLOSE l_ServiceRequest_csr;
170
171 -- End of changes by ANEEMUCH date 09-May-2002
172
173 WF_ENGINE.SetItemAttrText(
174 itemtype => 'SERVEREQ',
175 itemkey => itemkey,
176 aname => 'CUG_INCIDENT_ADDRESS',
177 avalue => l_incident_address );
178
179 END IF;
180 result := 'Y';
181
182 EXCEPTION
183 WHEN l_API_ERROR THEN
184 WF_CORE.Context('CUG_GENERIC_WF_PKG', 'GET_OTHER_SR_ATTRIBUTES',
185 itemtype, itemkey, actid, funmode);
186 RAISE;
187 WHEN OTHERS THEN
188 WF_CORE.Context('CUG_GENERIC_WF_PKG', 'GET_OTHER_SR_ATTRIBUTES',
189 itemtype, itemkey, actid, funmode);
190 RAISE;
191
192 END GET_OTHER_SR_ATTRIBUTES;
193
194
195
196
197 PROCEDURE REPLACE_SR_OWNER(
198 itemtype VARCHAR2,
199 itemkey VARCHAR2,
200 actid NUMBER,
201 funmode VARCHAR2,
202 result OUT NOCOPY VARCHAR2 )
203 IS
204
205 l_request_id NUMBER;
206 l_incident_type_id NUMBER;
207 l_default_owner_id NUMBER := 0;
208 l_msg_count NUMBER;
209 l_interaction_id NUMBER;
210 l_object_version_number NUMBER;
211
212
213 l_owner_id NUMBER;
214
215 l_errmsg_name VARCHAR2(30);
216 l_resource_type VARCHAR2(11);
217 l_msg_data VARCHAR2(64);
218 l_return_status VARCHAR2(64);
219
220 CURSOR l_GetResourceID_csr IS
221 select resource_id FROM CUG_SR_TYPE_DUP_CHK_INFO
222 WHERE incident_type_id = l_incident_type_id;
223 l_GetResourceID_rec l_GetResourceID_csr%ROWTYPE;
224
225
226
227 /* 2576759 - BEGIN - Added resource_type check in the where clause */
228 CURSOR l_GetResourceName_csr IS
229 select resource_name, resource_type from cs_sr_owners_v where
230 resource_id = l_default_owner_id and
231 resource_type = 'RS_EMPLOYEE';
232 /* 2576759 - END - Added resource_type check in the where clause */
233 l_GetResourceName_rec l_GetResourceName_csr%ROWTYPE;
234
235
236 /* begin - to fix bug # 2576759 - added the following cursors and variables */
237 CURSOR l_GetResourceInfo_csr IS
238 select incident_owner_id, owner_group_id, org_id from
239 cs_incidents_all_b where
240 incident_id = l_request_id;
241 l_GetResourceInfo_rec l_GetResourceInfo_csr%ROWTYPE;
242
243 CURSOR l_GetSourceId_csr IS
244 select source_id from jtf_rs_resource_extns where
245 resource_id = l_default_owner_id;
246 /* end - to fix bug # 2576759*/
247
248
249
250
251 /* bug fix : 1964270 */
252
253 CURSOR l_GetPerson_Id_csr IS
254 SELECT person_id FROM per_people_x
255 WHERE full_name = l_GetResourceName_rec.resource_name AND employee_number IS NOT NULL;
256
257 l_person_id NUMBER;
258 /* bug fix : 1964270 */
259
260
261 /* Roopa - bug fix 2312069 */
262 l_resource_id NUMBER;
263 l_source_id NUMBER;
264
265 l_owner_role VARCHAR2(100);
266 l_owner_name VARCHAR2(240);
267 l_API_ERROR EXCEPTION;
268
269 CURSOR l_GetGroupResourceId_csr IS
270 select resource_id from jtf_rs_group_members_vl where
271 group_id = l_default_owner_id;
272
273 CURSOR l_GetTeamResourceId_csr IS
274 select team_resource_id from jtf_rs_team_members_vl where
275 team_id = l_default_owner_id;
276 /* Roopa - bug fix 2312069 */
277
278 BEGIN
279 IF (funmode = 'RUN') THEN
280
281 l_request_id := WF_Engine.GetItemAttrNumber(itemtype => itemtype,
282 itemkey => itemkey,
283 aname => 'REQUEST_ID');
284
285 select INCIDENT_TYPE_ID into l_incident_type_id FROM
286 CS_INCIDENTS_ALL_VL WHERE INCIDENT_ID = l_request_id;
287
288 OPEN l_GetResourceID_csr;
289 FETCH l_GetResourceID_csr INTO l_GetResourceID_rec;
290
291 IF (l_GetResourceID_csr%NOTFOUND OR
292 l_GetResourceID_rec.resource_id is null) THEN
293 result := 'N';
294 ELSE
295 l_default_owner_id := l_GetResourceID_rec.resource_id;
296
297
298 OPEN l_GetResourceName_csr;
299 FETCH l_GetResourceName_csr INTO l_GetResourceName_rec;
300
301 IF (l_GetResourceName_csr%NOTFOUND) THEN
302 result := 'N';
303 ELSE
304
305 /* begin - to fix bug # 2576759 -
306 *) if the current sr already has the group_id filled, do NOT update the inci
307 dent_owner id
308 *) if the current sr does not have the gorup is filled , but individual owne
309 r id filled - do not update the incident_owner id
310 *) if the current sr has no group and no current sr - update the incident_ow
311 ner id col with resp party id
312 */
313
314 OPEN l_GetResourceInfo_csr;
315 FETCH l_GetResourceInfo_csr INTO l_GetResourceInfo_rec;
316 IF (l_GetResourceInfo_csr%NOTFOUND) THEN
317 result := 'N';
318 ELSE
319 l_resource_id := l_default_owner_id;
320 OPEN l_GetSourceId_csr;
321 LOOP
322 FETCH l_GetSourceId_csr INTO l_source_id;
323 EXIT ;
324 END LOOP;
325 IF (l_GetSourceId_csr%NOTFOUND) THEN
326 result := 'N';
327 ELSE
328 -- Retrieve the role name for the request owner
329 CS_WORKFLOW_PUB.Get_Employee_Role (
330 p_api_version => 1.0,
331 p_return_status => l_return_status,
332 p_msg_count => l_msg_count,
333 p_msg_data => l_msg_data,
334 p_employee_id => l_source_id,
335 p_role_name => l_owner_role,
336 p_role_display_name => l_owner_name );
337
338 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) OR
339 (l_owner_role is NULL) THEN
340 IF(FND_PROFILE.Value('CUG_DEFAULT_SR_WF_ROLE') is null) THEN
341 wf_core.context( pkg_name => 'CUG_GENERIC_WKFLW_PKG',
342 proc_name => 'Replace_SR_Owner',
343 arg1 => 'p_owner_id=>'||
344 to_char(l_source_id));
345 l_errmsg_name := 'CUG_INCIDENT_OWNER_NOT_VALID';
346 raise l_API_ERROR;
347 ELSE
348 l_owner_role := FND_PROFILE.Value('CUG_DEFAULT_SR_WF_ROLE');
349 END IF; -- End of fnd_profile check
350 END IF; -- End of l_return_status check
351
352 END IF; -- If source id != null
353
354 END IF; -- End of resource_type check
355 /*
356 ELSE
357 l_owner_role := FND_PROFILE.Value('CUG_DEFAULT_SR_WF_ROLE');
358 END IF; --end of the entire if-then-else block
359 */
360
361
362 WF_ENGINE.SetItemAttrText(
363 itemtype => 'SERVEREQ',
364 itemkey => itemkey,
365 aname => 'OWNER_NAME',
366 avalue => l_owner_role );
367
368 /*
369 WF_ENGINE.SetItemAttrText(
370 itemtype => 'SERVEREQ',
371 itemkey => itemkey,
372 aname => 'OWNER_NAME',
373 avalue => l_GetResourceName_rec.resource_name );
374
375
376 WF_ENGINE.SetItemAttrText(
377 itemtype => 'SERVEREQ',
378 itemkey => itemkey,
379 aname => 'OWNER_ROLE',
380 avalue => l_owner_role );
381 */
382
383 result := 'Y';
384
385 END IF;
386 END IF;
387 END IF;
388
389 EXCEPTION
393 WF_CORE.Context('CUG_GENERIC_WF_PKG', 'REPLACE_SR_OWNER',
390 WHEN l_API_ERROR THEN
391 WF_CORE.Raise(l_errmsg_name);
392 WHEN OTHERS THEN
394 itemtype, itemkey, actid, funmode);
395 RAISE;
396 END REPLACE_SR_OWNER;
397
398
399
400 PROCEDURE ALLOW_ADDRESS_OVERWRITE(
401 itemtype VARCHAR2,
402 itemkey VARCHAR2,
403 actid NUMBER,
404 funmode VARCHAR2,
405 result OUT NOCOPY VARCHAR2 )
406 IS
407
408 l_request_id NUMBER;
409 l_incident_type_id NUMBER;
410 l_default_owner_id NUMBER;
411 l_msg_count NUMBER;
412
413 l_address_overwrite_flag VARCHAR2(1) := NULL;
414 l_errmsg_name VARCHAR2(30);
415 l_resource_type VARCHAR2(11);
416 l_msg_data VARCHAR2(64);
417 l_return_status VARCHAR2(64);
418
419 l_API_ERROR EXCEPTION;
420
421 CURSOR c_GetOverrideFlag_csr IS
422 SELECT DISTINCT OVERRIDE_ADDR_VALID_FLAG FROM CUG_INCIDNT_ATTR_VALS_B
423 WHERE INCIDENT_ID = l_request_id;
424
425
426 BEGIN
427
428 IF (funmode = 'RUN') THEN
429
430 l_request_id := WF_Engine.GetItemAttrNumber(itemtype => itemtype,
431 itemkey => itemkey,
432 aname => 'REQUEST_ID');
433
434 OPEN c_GetOverrideFlag_csr;
435 FETCH c_GetOverrideFlag_csr into l_address_overwrite_flag;
436
437 IF(c_GetOverrideFlag_csr%NOTFOUND OR
438 l_address_overwrite_flag = 'N') THEN
439 result := 'N';
440 ELSE
441 result := 'Y';
442 END IF;
443 CLOSE c_GetOverrideFlag_csr;
444 END IF;
445
446 EXCEPTION
447 WHEN l_API_ERROR THEN
448 WF_CORE.Context('CUG_GENERIC_WF_PKG', 'ALLOW_ADDRESS_OVERWRITE',
449 itemtype, itemkey, actid, funmode);
450 RAISE;
451 WHEN OTHERS THEN
452 WF_CORE.Context('CUG_GENERIC_WF_PKG', 'ALLOW_ADDRESS_OVERWRITE',
453 itemtype, itemkey, actid, funmode);
454 RAISE;
455
456 END ALLOW_ADDRESS_OVERWRITE;
457
458
459
460 PROCEDURE DUPLICATE_CHECKING_REQUIRED(
461 itemtype VARCHAR2,
462 itemkey VARCHAR2,
463 actid NUMBER,
464 funmode VARCHAR2,
465 result OUT NOCOPY VARCHAR2 )
466 IS
467 l_request_id NUMBER;
468 l_request_type_id NUMBER;
469 l_msg_count NUMBER;
470 l_address_overwrite_flag NUMBER;
471
472 l_duplicate_check_flag VARCHAR2(1);
473 l_errmsg_name VARCHAR2(30);
474 l_resource_type VARCHAR2(11);
475 l_msg_data VARCHAR2(64);
476 l_return_status VARCHAR2(64);
477
478 l_API_ERROR EXCEPTION;
479
480 CURSOR c_CheckIfDupCheckOn_csr IS
481 select SR_DUP_CHECK_FLAG from CUG_SR_TYPE_DUP_CHK_INFO
482 WHERE INCIDENT_TYPE_ID = l_request_type_id;
483
484
485 BEGIN
486 IF (funmode = 'RUN') THEN
487
488 l_request_id := WF_Engine.GetItemAttrNumber(itemtype => itemtype,
489 itemkey => itemkey,
490 aname => 'REQUEST_ID');
491
492 select INCIDENT_TYPE_ID into l_request_type_id from CS_INCIDENTS_ALL_VL
493 WHERE INCIDENT_ID = l_request_id;
494
495 OPEN c_CheckIfDupCheckOn_csr;
496 FETCH c_CheckIfDupCheckOn_csr into l_duplicate_check_flag;
497 IF (c_CheckIfDupCheckOn_csr%NOTFOUND) THEN
498 l_duplicate_check_flag := 'N';
499 END IF;
500 CLOSE c_CheckIfDupCheckOn_csr;
501
502 IF (l_duplicate_check_flag = 'Y') THEN
503 result := 'Y';
504 ELSE
505 result := 'N';
506 END IF;
507
508 END IF;
509
510 EXCEPTION
511 WHEN l_API_ERROR THEN
512 WF_CORE.Context('CUG_GENERIC_WF_PKG', 'DUPLICATE_CHECKING_REQUIRED',
513 itemtype, itemkey, actid, funmode);
514 RAISE;
515 WHEN OTHERS THEN
516 WF_CORE.Context('CUG_GENERIC_WF_PKG', 'DUPLICATE_CHECKING_REQUIRED',
517 itemtype, itemkey, actid, funmode);
518 RAISE;
519 END DUPLICATE_CHECKING_REQUIRED;
520
521
522
523 PROCEDURE SR_A_DUPLICATE(
524 itemtype VARCHAR2,
525 itemkey VARCHAR2,
526 actid NUMBER,
527 funmode VARCHAR2,
528 result OUT NOCOPY VARCHAR2 )
529 IS
530 l_request_id NUMBER;
531 l_request_type_id NUMBER;
532 l_msg_count NUMBER;
533 l_install_at_site_id NUMBER;
534 l_match_found NUMBER;
535
536 l_incident_location_id NUMBER;
537 l_incident_address VARCHAR2(960);
538 l_incident_city VARCHAR2(60);
539 l_incident_state VARCHAR2(60);
540 l_incident_postal_code VARCHAR2(60);
541 l_incident_country VARCHAR2(60);
542
543 l_counter NUMBER;
544 l_attr_counter NUMBER;
545 l_incident_counter NUMBER;
549 l_msg_data VARCHAR2(64);
546
547 l_errmsg_name VARCHAR2(30);
548 l_resource_type VARCHAR2(11);
550 l_return_status VARCHAR2(64);
551
552 l_SRAttribute_value_new VARCHAR2(1997);
553 l_SRAttribute_value_old VARCHAR2(1997);
554 l_sql_stmt VARCHAR2(2000);
555 l_sql_stmt1 VARCHAR2(2000);
556
557 l_owner_name VARCHAR2(240);
558
559 l_duplicate_date DATE;
560
561 l_API_ERROR EXCEPTION;
562
563 -- Begin of changes by ANEEMUCH date 10-May-2002
564 /*
565 CURSOR l_IncidentAddress_csr IS
566 select ADDRESS1, ADDRESS2, CITY, STATE, POSTAL_CODE, COUNTRY FROM
567 HZ_LOCATIONS where LOCATION_ID = l_install_at_site_id;
568 l_IncidentAddress_rec l_IncidentAddress_csr%ROWTYPE;
569
570 CURSOR l_IncidentId_csr IS
571 SELECT CS_INCIDENTS_ALL_VL.INCIDENT_ID,CS_INCIDENTS_ALL_VL.INCIDENT_NUMBER
572 FROM CS_INCIDENTS_ALL_VL, HZ_LOCATIONS
573 WHERE HZ_LOCATIONS.LOCATION_ID = CS_INCIDENTS_ALL_VL.INSTALL_SITE_USE_ID AND
574 HZ_LOCATIONS.ADDRESS1 = l_IncidentAddress_rec.Address1 AND
575 nvl(HZ_LOCATIONS.ADDRESS2, 'Not Filled') = nvl(l_IncidentAddress_rec.Address2, 'Not Filled') AND
576 nvl(HZ_LOCATIONS.CITY, 'Not Filled') = nvl(l_IncidentAddress_rec.City, 'Not Filled') AND
577 nvl(HZ_LOCATIONS.STATE, 'Not Filled') = nvl(l_IncidentAddress_rec.State, 'Not Filled') AND
578 nvl(HZ_LOCATIONS.POSTAL_CODE, 'Not Filled') = nvl(l_IncidentAddress_rec.Postal_Code, 'Not Filled') AND
579 -- HZ_LOCATIONS.COUNTRY = l_IncidentAddress_rec.Country AND
580 CS_INCIDENTS_ALL_VL.LAST_UPDATE_DATE > l_duplicate_date AND
581 CS_INCIDENTS_ALL_VL.INCIDENT_TYPE_ID = l_request_type_id AND
582 CS_INCIDENTS_ALL_VL.INCIDENT_ID <> l_request_id;
583 l_IncidentId_rec l_IncidentId_csr%ROWTYPE;
584 */
585
586 CURSOR l_ServiceRequest_csr IS
587 SELECT incident_location_id, incident_address, incident_city, incident_state,
588 incident_postal_code, incident_country
589 FROM cs_incidents_all_b
590 WHERE incident_id = l_request_id;
591 l_ServiceRequest_rec l_ServiceRequest_csr%ROWTYPE;
592
593 CURSOR l_IncidentAddress_csr IS
594 SELECT address, city, state, postal_code, country
595 FROM cs_sr_incident_address_v
596 WHERE location_id = l_incident_location_id;
597 l_IncidentAddress_rec l_IncidentAddress_csr%ROWTYPE;
598
599 CURSOR l_IncidentId_csr IS
600 SELECT CS_INCIDENTS_ALL_VL.INCIDENT_ID,CS_INCIDENTS_ALL_VL.INCIDENT_NUMBER
601 FROM CS_INCIDENTS_ALL_VL, HZ_LOCATIONS
602 WHERE HZ_LOCATIONS.LOCATION_ID = CS_INCIDENTS_ALL_VL.INCIDENT_LOCATION_ID AND
603 nvl(HZ_LOCATIONS.ADDRESS1, 'Not Filled') = nvl(l_incident_address, 'Not Filled') AND
604 nvl(HZ_LOCATIONS.CITY, 'Not Filled') = nvl(l_incident_city, 'Not Filled') AND
605 nvl(HZ_LOCATIONS.STATE, 'Not Filled') = nvl(l_incident_state, 'Not Filled') AND
606 nvl(HZ_LOCATIONS.POSTAL_CODE, 'Not Filled') = nvl(l_incident_postal_Code, 'Not Filled') AND
607 nvl(HZ_LOCATIONS.COUNTRY, 'Not Filled') = nvl(l_incident_country, 'Not Filled') AND
608 CS_INCIDENTS_ALL_VL.LAST_UPDATE_DATE > l_duplicate_date AND
609 CS_INCIDENTS_ALL_VL.INCIDENT_TYPE_ID = l_request_type_id AND
613 FROM CS_INCIDENTS_ALL_VL
610 CS_INCIDENTS_ALL_VL.INCIDENT_ID <> l_request_id
611 UNION
612 SELECT CS_INCIDENTS_ALL_VL.INCIDENT_ID,CS_INCIDENTS_ALL_VL.INCIDENT_NUMBER
614 WHERE nvl(incident_ADDRESS, 'Not Filled') = nvl(l_incident_address, 'Not Filled') AND
615 nvl(incident_CITY, 'Not Filled') = nvl(l_incident_city, 'Not Filled') AND
616 nvl(incident_STATE, 'Not Filled') = nvl(l_incident_state, 'Not Filled') AND
617 nvl(incident_POSTAL_CODE, 'Not Filled') = nvl(l_incident_postal_Code, 'Not Filled') AND
618 nvl(incident_COUNTRY, 'Not Filled') = nvl(l_incident_country, 'Not Filled') AND
619 LAST_UPDATE_DATE > l_duplicate_date AND
620 INCIDENT_TYPE_ID = l_request_type_id AND
621 INCIDENT_ID <> l_request_id;
622 l_IncidentId_rec l_IncidentId_csr%ROWTYPE;
623
624 -- End of changes by ANEEMUCH date 10-May-2002
625 --
626
627 -- Changed the where condition to fix bug # 2471602
628 CURSOR l_IncidentId_noLoc_csr IS
629 SELECT CS_INCIDENTS_ALL_VL.INCIDENT_ID,CS_INCIDENTS_ALL_VL.INCIDENT_NUMBER
630 FROM CS_INCIDENTS_ALL_VL
631 WHERE CS_INCIDENTS_ALL_VL.LAST_UPDATE_DATE > l_duplicate_date AND
632 CS_INCIDENTS_ALL_VL.INCIDENT_TYPE_ID = l_request_type_id AND
633 CS_INCIDENTS_ALL_VL.INCIDENT_ID <> l_request_id AND
634 CS_INCIDENTS_ALL_VL.INCIDENT_LOCATION_ID is NULL AND
635 CS_INCIDENTS_ALL_VL.incident_ADDRESS is NULL AND
636 CS_INCIDENTS_ALL_VL.incident_CITY is NULL AND
637 CS_INCIDENTS_ALL_VL.incident_STATE is NULL AND
638 CS_INCIDENTS_ALL_VL.incident_POSTAL_CODE is NULL AND
639 CS_INCIDENTS_ALL_VL.incident_COUNTRY is NULL;
640 l_IncidentId_noLoc_rec l_IncidentId_noLoc_csr%ROWTYPE;
641
642
643 CURSOR l_DuplicateCheckAttrs_csr IS
644 select SR_ATTRIBUTE_CODE from CUG_SR_TYPE_ATTR_MAPS_VL
645 where INCIDENT_TYPE_ID = l_request_type_id AND
646 SR_ATTR_DUP_CHECK_FLAG = 'Y' AND
647 ( END_DATE_ACTIVE IS NULL OR
648 to_number(to_char(END_DATE_ACTIVE, 'YYYYMMDD')) >= to_number(to_char(sysdate, 'YYYYMMDD')) );
649 l_DuplicateCheckAttrs_rec l_DuplicateCheckAttrs_csr%ROWTYPE;
650
651
652 CURSOR l_NewDupAttrValue_csr IS
653 SELECT sr_attribute_value FROM cug_incidnt_attr_vals_vl WHERE
654 sr_attribute_code = l_DuplicateCheckAttrs_rec.sr_attribute_code AND
655 incident_id = l_request_id;
656
657 CURSOR l_OldDupAttrValue_csr IS
658 SELECT sr_attribute_value FROM cug_incidnt_attr_vals_vl WHERE
659 sr_attribute_code = l_DuplicateCheckAttrs_rec.sr_attribute_code AND
660 incident_id = l_IncidentId_rec.Incident_Id;
661
662 CURSOR l_OldDupAttrValue_noLoc_csr IS
663 SELECT sr_attribute_value FROM cug_incidnt_attr_vals_vl WHERE
664 sr_attribute_code = l_DuplicateCheckAttrs_rec.sr_attribute_code AND
665 incident_id = l_IncidentId_noLoc_rec.Incident_Id;
666
667
668 TYPE IncidentList IS VARRAY(400) OF CS_INCIDENTS_ALL_VL.incident_id%TYPE;
669 l_IncidentList IncidentList := IncidentList();
670
671
672 TYPE SRAttrDupCheck IS REF CURSOR;
673 l_SRAttrDupCheck_str SRAttrDupCheck;
674 l_SRAttrDupCheck_rec CS_INCIDENTS_ALL_VL.incident_id%TYPE;
675
676
677 BEGIN
678 IF (funmode = 'RUN') THEN
679
680 l_attr_counter := 0;
681 l_incident_counter := 1;
682
683 l_request_id := WF_Engine.GetItemAttrNumber(itemtype => itemtype,
684 itemkey => itemkey,
685 aname => 'REQUEST_ID');
686
687 l_owner_name := WF_Engine.GetItemAttrText(itemtype => itemtype,
688 itemkey => itemkey,
689 aname => 'OWNER_NAME');
690
691 -- Beging of changes by ANEEMUCH date 10-May-2002
692 /*
693 select INSTALL_SITE_USE_ID into l_install_at_site_id
694 from CS_INCIDENTS_ALL_VL where INCIDENT_ID = l_request_id;
695 */
696
697 OPEN l_ServiceRequest_csr;
698 FETCH l_ServiceRequest_csr into l_ServiceRequest_rec;
699 IF (l_ServiceRequest_rec.incident_location_id IS NOT NULL) THEN
700
701 -- Fix for bug# 2500202. Initialize l_incident_location_id used for
702 -- cursor l_IncidentAddress_csr. rmanabat 08/07/02.
703 l_incident_location_id := l_ServiceRequest_rec.incident_location_id;
704
705 OPEN l_IncidentAddress_csr;
706 FETCH l_IncidentAddress_csr INTO l_IncidentAddress_rec;
707 IF (l_IncidentAddress_csr%FOUND) THEN
708 l_incident_address := l_IncidentAddress_rec.address;
709 l_incident_city := l_IncidentAddress_rec.city;
710 l_incident_state := l_IncidentAddress_rec.state;
711 l_incident_postal_code := l_IncidentAddress_rec.postal_code;
712 l_incident_country := l_IncidentAddress_rec.country;
713 END IF;
714 CLOSE l_IncidentAddress_csr;
715 ELSE
716 l_incident_address := l_ServiceRequest_rec.incident_address;
720 l_incident_country := l_ServiceRequest_rec.incident_country;
717 l_incident_city := l_ServiceRequest_rec.incident_city;
718 l_incident_state := l_ServiceRequest_rec.incident_state;
719 l_incident_postal_code := l_ServiceRequest_rec.incident_postal_code;
721 END IF;
722 CLOSE l_ServiceRequest_csr;
723 -- End of changes by ANEEMUCH date 10-May-2002
724
725 select INCIDENT_TYPE_ID into l_request_type_id
726 from CS_INCIDENTS_ALL_VL where INCIDENT_ID = l_request_id;
727
728 -- Begin changes by ANEEMUCH 10-May-2002
729 -- IF ( l_install_at_site_id IS NOT NULL) THEN
730 IF (l_incident_address IS NOT NULL OR l_incident_city IS NOT NULL OR
731 l_incident_state IS NOT NULL OR l_incident_postal_code IS NOT NULL OR
732 l_incident_country IS NOT NULL) Then
733
734 CALCULATE_DUPLICATE_TIME_FRAME(p_service_request_id => l_request_id,
735 p_request_type_id => l_request_type_id,
736 p_duplicate_time_frame => l_duplicate_date);
737
738
739 IF (l_duplicate_date IS NULL) THEN
740 RAISE fnd_api.g_exc_unexpected_error;
741 END IF;
742
743 -- Begin changes by ANEEMUCH 13-May-2002
744 /*
745 OPEN l_IncidentAddress_csr;
746 FETCH l_IncidentAddress_csr into l_IncidentAddress_rec;
747 IF (l_IncidentAddress_csr%NOTFOUND) THEN
748 RAISE fnd_api.g_exc_unexpected_error;
749 END IF;
750 */
751 -- End of changes by ANEEMUCH 13-May-2002
752
753 l_match_found := 1;
754 OPEN l_IncidentId_csr;
755 LOOP
756
757 FETCH l_IncidentId_csr into l_IncidentId_rec;
758 EXIT WHEN l_IncidentId_csr%NOTFOUND;
759
760
761 OPEN l_DuplicateCheckAttrs_csr;
762 LOOP
763 FETCH l_DuplicateCheckAttrs_csr into l_DuplicateCheckAttrs_rec;
764 EXIT WHEN l_DuplicateCheckAttrs_csr%NOTFOUND;
765
766 l_sql_stmt := 'select sr_attribute_value from cug_incidnt_attr_vals_vl where sr_attribute_code = '
767 || l_DuplicateCheckAttrs_rec.sr_attribute_code || ' and incident_id = :incident_id';
768
769 OPEN l_NewDupAttrValue_csr;
770 FETCH l_NewDupAttrValue_csr into l_SRAttribute_value_new;
771 IF(l_NewDupAttrValue_csr%NOTFOUND) THEN
772 l_SRAttribute_value_new := ' ';
773 END IF;
774 CLOSE l_NewDupAttrValue_csr;
775
776 OPEN l_OldDupAttrValue_csr;
777 FETCH l_OldDupAttrValue_csr into l_SRAttribute_value_old;
778 IF(l_OldDupAttrValue_csr%NOTFOUND) THEN
779 l_SRAttribute_value_old := ' ';
780 END IF;
781 CLOSE l_OldDupAttrValue_csr;
782
783 IF (l_SRAttribute_value_new <> l_SRAttribute_value_old) THEN
784 l_match_found := 0;
785 exit;
786 ELSIF (l_SRAttribute_value_new = l_SRAttribute_value_old) THEN
787 l_match_found := 1;
788 END IF;
789 END LOOP;
790 CLOSE l_DuplicateCheckAttrs_csr;
791
792 IF (l_match_found = 1) THEN
793 result := 'Y';
794 Wf_Engine.SetItemAttrNumber(itemtype => itemtype,
795 itemkey => itemkey,
796 aname => 'CUG_PARENT_REQUEST_ID',
797 avalue => l_IncidentId_rec.Incident_Id);
798 Wf_Engine.SetItemAttrText(itemtype => itemtype,
799 itemkey => itemkey,
800 aname => 'CUG_PARENT_REQUEST_NUMBER',
801 avalue => l_IncidentId_rec.Incident_Number);
802 return;
803 END IF;
804
805 END LOOP;
806 CLOSE l_IncidentId_csr;
807 ELSE
808
809 CALCULATE_DUPLICATE_TIME_FRAME(p_service_request_id => l_request_id,
810 p_request_type_id => l_request_type_id,
811 p_duplicate_time_frame => l_duplicate_date);
812
813
814 IF (l_duplicate_date IS NULL) THEN
815 RAISE fnd_api.g_exc_unexpected_error;
816 END IF;
817
818
819 l_match_found := 1;
820 OPEN l_IncidentId_noLoc_csr;
821 LOOP
822
823 FETCH l_IncidentId_noLoc_csr into l_IncidentId_noLoc_rec;
824 EXIT WHEN l_IncidentId_noLoc_csr%NOTFOUND;
825
826
827 OPEN l_DuplicateCheckAttrs_csr;
828 LOOP
829 FETCH l_DuplicateCheckAttrs_csr into l_DuplicateCheckAttrs_rec;
833 || l_DuplicateCheckAttrs_rec.sr_attribute_code || ' and incident_id = :incident_id';
830 EXIT WHEN l_DuplicateCheckAttrs_csr%NOTFOUND;
831
832 l_sql_stmt := 'select sr_attribute_value from cug_incidnt_attr_vals_vl where sr_attribute_code = '
834
835 OPEN l_NewDupAttrValue_csr;
836 FETCH l_NewDupAttrValue_csr into l_SRAttribute_value_new;
837 IF(l_NewDupAttrValue_csr%NOTFOUND) THEN
838 l_SRAttribute_value_new := ' ';
839 END IF;
840 CLOSE l_NewDupAttrValue_csr;
841
842 OPEN l_OldDupAttrValue_noLoc_csr;
843 FETCH l_OldDupAttrValue_noLoc_csr into l_SRAttribute_value_old;
844 IF(l_OldDupAttrValue_noLoc_csr%NOTFOUND) THEN
845 l_SRAttribute_value_old := ' ';
846 END IF;
847 CLOSE l_OldDupAttrValue_noLoc_csr;
848
849 IF (l_SRAttribute_value_new <> l_SRAttribute_value_old) THEN
850 l_match_found := 0;
851 exit;
852 ELSIF (l_SRAttribute_value_new = l_SRAttribute_value_old) THEN
853 l_match_found := 1;
854 END IF;
855 END LOOP;
856 CLOSE l_DuplicateCheckAttrs_csr;
857
858 IF (l_match_found = 1) THEN
859 result := 'Y';
860 Wf_Engine.SetItemAttrNumber(itemtype => itemtype,
861 itemkey => itemkey,
862 aname => 'CUG_PARENT_REQUEST_ID',
863 avalue => l_IncidentId_noLoc_rec.Incident_Id);
864 Wf_Engine.SetItemAttrText(itemtype => itemtype,
865 itemkey => itemkey,
866 aname => 'CUG_PARENT_REQUEST_NUMBER',
867 avalue => l_IncidentId_noLoc_rec.Incident_Number);
868 return;
869 END IF;
870
871 END LOOP;
872 CLOSE l_IncidentId_noLoc_csr;
873
874 END IF;
875
876 result := 'N';
877 END IF;
878
879 EXCEPTION
880 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
881 WF_CORE.Context('CUG_GENERIC_WF_PKG', 'SR_A_DUPLICATE',
882 itemtype, itemkey, actid, funmode);
883 RAISE;
884 WHEN l_API_ERROR THEN
885 WF_CORE.Context('CUG_GENERIC_WF_PKG', 'SR_A_DUPLICATE',
886 itemtype, itemkey, actid, funmode);
887 RAISE;
888 WHEN OTHERS THEN
889 WF_CORE.Context('CUG_GENERIC_WF_PKG', 'SR_A_DUPLICATE',
890 itemtype, itemkey, actid, funmode);
891 RAISE;
892
893
894 END SR_A_DUPLICATE;
895
896
897
898 PROCEDURE UPDATE_DUPLICATE_INFO(
899 itemtype VARCHAR2,
900 itemkey VARCHAR2,
901 actid NUMBER,
902 funmode VARCHAR2,
903 result OUT NOCOPY VARCHAR2 )
904 IS
905 l_request_id NUMBER;
906 l_request_type_id NUMBER;
907 l_default_owner_id NUMBER;
908 l_note_id NUMBER;
909 l_note_context_id NUMBER;
910 l_note_context_type_id NUMBER;
911 l_last_updated_by NUMBER(15):= FND_GLOBAL.USER_ID;
912 l_created_by NUMBER(15) := FND_GLOBAL.USER_ID;
913 l_last_update_login NUMBER := FND_GLOBAL.LOGIN_ID;
914
915 l_msg_count NUMBER;
916
917 l_return_status VARCHAR2(1);
918 l_msg_data VARCHAR2(2000);
919 l_note_context_type VARCHAR2(240);
920
921 l_API_ERROR EXCEPTION;
922
923 CURSOR c_SR_Attr_Note_csr IS
924 SELECT jtf_note_id from JTF_NOTES_B WHERE
925 note_type = 'CUG_SR_ATTR_DETAILS' AND
926 source_object_code = 'SR' AND
927 source_object_id = l_request_id;
928 l_SR_Attr_Note_rec c_SR_Attr_Note_csr%ROWTYPE;
929
930 BEGIN
931
932 IF (funmode = 'RUN') THEN
933
934 l_request_id := WF_Engine.GetItemAttrNumber(itemtype => itemtype,
935 itemkey => itemkey,
936 aname => 'REQUEST_ID');
937
938 select INCIDENT_TYPE_ID into l_request_type_id FROM
939 CS_INCIDENTS_ALL_VL WHERE INCIDENT_ID = l_request_id;
940
941 select RESOURCE_ID into l_default_owner_id FROM CUG_SR_TYPE_DUP_CHK_INFO
942 WHERE Incident_Type_ID = l_request_type_id;
943
944
945 IF (l_default_owner_id is not null ) THEN
946
947 /*
948 JTF_NOTES_PUB.Create_note
949 ( p_api_version => 1.0,
950 x_return_status => l_return_status,
951 x_msg_count => l_msg_count,
952 x_msg_data => l_msg_data,
953 p_source_object_id => l_request_id,
954 p_source_object_code => 'SR',
955 p_notes => 'This Service Request is a duplicate.',
959 p_last_update_date => sysdate,
956 p_entered_by => l_default_owner_id,
957 p_entered_date => sysdate,
958 x_jtf_note_id => l_note_id,
960 p_last_updated_by => l_default_owner_id,
961 p_creation_date => sysdate,
962 p_note_type => 'Duplicate Service Request'
963 );
964
965 IF NOT (l_return_status = fnd_api.g_ret_sts_success)
966 THEN
967 l_return_status := fnd_api.g_ret_sts_unexp_error;
968 RAISE fnd_api.g_exc_unexpected_error;
969 END IF;
970
971 JTF_NOTES_PUB.Create_note_context (
972 x_return_status => l_return_status,
973 p_jtf_note_id => l_note_id,
974 p_last_update_date => sysdate,
975 p_last_updated_by => l_default_owner_id,
976 p_creation_date => sysdate,
977 p_note_context_type_id => l_note_context_id,
978 p_note_context_type => 'Duplicate',
979 x_note_context_id => l_note_context_id);
980
981
982 IF NOT (l_return_status = fnd_api.g_ret_sts_success)
983 THEN
984 l_return_status := fnd_api.g_ret_sts_unexp_error;
985 RAISE fnd_api.g_exc_unexpected_error;
986 END IF;
987 */
988
989 SELECT jtf_notes_s.NEXTVAL INTO l_note_context_id FROM dual;
990
991 OPEN c_SR_Attr_Note_csr;
992 FETCH c_SR_Attr_Note_csr into l_note_id;
993
994 l_note_context_type_id := WF_Engine.GetItemAttrNumber(itemtype => itemtype,
995 itemkey => itemkey,
996 aname => 'CUG_PARENT_REQUEST_ID');
997
998
999 INSERT INTO JTF_NOTE_CONTEXTS(
1000 NOTE_CONTEXT_ID,
1001 JTF_NOTE_ID,
1002 NOTE_CONTEXT_TYPE_ID,
1003 NOTE_CONTEXT_TYPE,
1004 LAST_UPDATE_DATE,
1005 LAST_UPDATED_BY,
1006 CREATION_DATE,
1007 CREATED_BY,
1008 LAST_UPDATE_LOGIN)
1009 VALUES
1010 (l_note_context_id,
1011 l_note_id,
1012 l_note_context_type_id,
1013 'CUG_DUPLICATE_SR',
1014 sysdate,
1015 l_last_updated_by,
1016 sysdate,
1017 l_created_by,
1018 l_last_update_login);
1019
1020 ELSE
1021 RAISE fnd_api.g_exc_unexpected_error;
1022 END IF;
1023 END IF;
1024
1025 EXCEPTION
1026 WHEN l_API_ERROR THEN
1027 WF_CORE.Context('CUG_GENERIC_WF_PKG', 'UPDATE_DUPLICATE_INFO',
1028 itemtype, itemkey, actid, funmode);
1029 RAISE;
1030 WHEN OTHERS THEN
1031 WF_CORE.Context('CUG_GENERIC_WF_PKG', 'UPDATE_DUPLICATE_INFO',
1032 itemtype, itemkey, actid, funmode);
1033 RAISE;
1034
1035
1036 END UPDATE_DUPLICATE_INFO;
1037
1038
1039
1040 PROCEDURE CREATE_ALL_SR_TASKS(
1041 itemtype VARCHAR2,
1042 itemkey VARCHAR2,
1043 actid NUMBER,
1044 funmode VARCHAR2,
1045 result OUT NOCOPY VARCHAR2 )
1046 IS
1047
1048 l_request_id NUMBER;
1049 l_request_type_id NUMBER;
1050 l_msg_count NUMBER;
1051 l_create_task NUMBER;
1052 l_task_id NUMBER;
1053 l_tsk_typ_attr_dep_id NUMBER;
1054 l_task_type_id NUMBER;
1055 /* To fix bug # - 1966258 - Start */
1056 l_task_assignment_id NUMBER;
1057 l_user_id NUMBER;
1058 /* To fix bug # - 1966258 - Start */
1059
1060 l_return_status VARCHAR2(30);
1061 l_msg_data VARCHAR2(300);
1062 l_sr_attribute_value VARCHAR2(1997);
1063 l_workflow_name VARCHAR2(30);
1064 l_sr_attribute_code VARCHAR2(30);
1065 /* Begin - 09/24/2001 - change made to accomodate Rahul's problem with update_task API */
1066 /* added the following parameter */
1067 l_request_number VARCHAR2(64);
1068 /* End - 09/24/2001 - change made to accomodate Rahul's problem with update_task API */
1069
1070 l_planned_start_date date;
1071 l_planned_end_date date;
1072 l_scheduled_start_date date;
1073 l_scheduled_end_date date;
1074
1075 l_API_ERROR EXCEPTION;
1076
1077 CURSOR c_SRTasks_csr
1078 IS
1079 SELECT * FROM CUG_TSK_TYP_ATTR_DEPS_VL WHERE INCIDENT_TYPE_ID = l_request_type_id AND
1080 (START_DATE_ACTIVE IS NULL OR
1081 to_number(to_char(START_DATE_ACTIVE, 'YYYYMMDD')) <= to_number(to_char(sysdate, 'YYYYMMDD')) ) AND
1082 (END_DATE_ACTIVE is NULL OR
1083 to_number(to_char(END_DATE_ACTIVE, 'YYYYMMDD')) >= to_number(to_char(sysdate, 'YYYYMMDD')) );
1084 l_SRTasks_rec c_SRTasks_csr%ROWTYPE;
1085
1086 CURSOR c_SRTasks_Details_csr
1087 IS
1088 SELECT * FROM CUG_SR_TASK_TYPE_DETS_VL WHERE
1089 TSK_TYP_ATTR_DEP_ID = l_tsk_typ_attr_dep_id;
1090 l_SRTasks_Details_rec c_SRTasks_Details_csr%ROWTYPE;
1091
1092
1093 CURSOR c_SRAttr_Value_csr
1094 IS
1095 SELECT SR_ATTRIBUTE_VALUE FROM CUG_INCIDNT_ATTR_VALS_VL WHERE
1096 INCIDENT_ID = l_request_id AND
1097 SR_ATTRIBUTE_CODE = l_sr_attribute_code;
1098 l_SRAttr_Value_rec c_SRAttr_Value_csr%ROWTYPE;
1099
1100
1101 CURSOR c_Workflow_Check_csr
1102 IS
1103 SELECT workflow FROM JTF_TASK_TYPES_B
1104 WHERE task_type_id = l_task_type_id;
1105 l_Workflow_Check_rec c_Workflow_Check_csr%ROWTYPE;
1106
1107 CURSOR c_LookupCode_Check_csr
1108 IS
1109 SELECT lookup_code from FND_LOOKUP_VALUES where
1110 description = l_sr_attribute_value;
1111 l_LookupCode_Check_rec c_LookupCode_Check_csr%ROWTYPE;
1112
1113
1114 BEGIN
1115
1116
1117 -- Get the list of tasks for the given SR type
1118 -- For each task, see if it is dependent on a SR attribute
1119 -- If yes, get teh value it is dependent on.
1120 -- Also, get the runtime value for the attribute
1121 -- Compare the 2. If they match, then, create the task
1122 -- Else, skip the task creation for that particular task
1123
1124
1125 IF (funmode = 'RUN') THEN
1126
1127 l_request_id := WF_Engine.GetItemAttrNumber(itemtype => itemtype,
1128 itemkey => itemkey,
1129 aname => 'REQUEST_ID');
1130 /* Begin - 09/24/2001 - change made to accomodate Rahul's problem with update_task API */
1131 l_request_number := WF_Engine.GetItemAttrText(itemtype => itemtype,
1132 itemkey => itemkey,
1133 aname => 'REQUEST_NUMBER');
1134 /* End - 09/24/2001 - change made to accomodate Rahul's problem with update_task API */
1135
1136 SELECT INCIDENT_TYPE_ID into l_request_type_id FROM CS_INCIDENTS_ALL_VL WHERE
1137 INCIDENT_ID = l_request_id;
1138
1139 l_create_task := 1;
1140
1141 /* 08/30 -- to begin bug# 1964265 */
1142 result := 'Y';
1143 /* 08/30 -- to end bug# 1964265 */
1144
1145 -- FOR l_SRTasks_rec IN c_SRTasks_csr LOOP
1146
1147 OPEN c_SRTasks_csr;
1148 LOOP
1149
1150 FETCH c_SRTasks_csr into l_SRTasks_rec;
1151 EXIT WHEN c_SRTasks_csr%NOTFOUND;
1152
1153 l_tsk_typ_attr_dep_id := l_SRTasks_rec.tsk_typ_attr_dep_id;
1154 l_sr_attribute_code := l_SRTasks_rec.sr_attribute_code;
1155
1156
1157 IF (l_SRTasks_rec.sr_attribute_code IS NOT NULL) THEN
1158 OPEN c_SRAttr_Value_csr;
1159 FETCH c_SRAttr_Value_csr into l_SRAttr_Value_rec;
1160 IF (c_SRAttr_Value_csr%NOTFOUND) THEN
1161 RAISE fnd_api.g_exc_unexpected_error;
1162 ELSIF (l_SRAttr_Value_rec.sr_attribute_value IS NULL) THEN
1163 null;
1164 ELSE
1165 l_sr_attribute_value := l_SRAttr_Value_rec.sr_attribute_value;
1166 OPEN c_LookupCode_Check_csr;
1167 LOOP
1168 FETCH c_LookupCode_Check_csr into l_LookupCode_Check_rec;
1169 EXIT WHEN c_LookupCode_Check_csr%NOTFOUND;
1170
1171 IF ( l_LookupCode_Check_rec.lookup_code = l_SRTasks_rec.sr_attribute_value) THEN
1172 l_create_task := 1;
1173 exit;
1174 ELSE
1175 l_create_task := 0;
1176 END IF;
1177
1178 END LOOP;
1179 CLOSE c_LookupCode_Check_csr;
1180 /*
1181 IF (l_SRTasks_rec.sr_attribute_value = l_SRAttr_Value_rec.sr_attribute_value) THEN
1182 l_create_task := 1;
1183 ELSE
1184 l_create_task := 0;
1185 END IF;
1186 */
1187 END IF;
1188 CLOSE c_SRAttr_Value_csr;
1189 END IF;
1190
1191 IF (l_SRTasks_rec.sr_attribute_code IS NULL) THEN
1192 l_create_task := 1;
1193 END IF;
1194
1195 IF (l_create_task > 0) THEN
1196 OPEN c_SRTasks_Details_csr;
1197 FETCH c_SRTasks_Details_csr INTO l_SRTasks_Details_rec;
1198
1199 CALCULATE_DATE(p_uom => l_SRTasks_Details_rec.planned_start_uom,
1200 p_offset => l_SRTasks_Details_rec.planned_start_offset,
1201 x_date => l_planned_start_date);
1202
1203 CALCULATE_DATE(p_uom => l_SRTasks_Details_rec.planned_end_uom,
1204 p_offset => l_SRTasks_Details_rec.planned_end_offset,
1205 x_date => l_planned_end_date);
1206
1207 CALCULATE_DATE(p_uom => l_SRTasks_Details_rec.scheduled_start_uom,
1208 p_offset => l_SRTasks_Details_rec.scheduled_start_offset,
1209 x_date => l_scheduled_start_date);
1210
1211 CALCULATE_DATE(p_uom => l_SRTasks_Details_rec.scheduled_end_uom,
1212 p_offset => l_SRTasks_Details_rec.scheduled_end_offset,
1213 x_date => l_scheduled_end_date);
1214
1215 /* 08/30 -- begin - to fix bug# 1964265 */
1216 VALIDATE_TASK_DETAILS(p_task_type_id => l_SRTasks_rec.task_type_id,
1217 p_task_status_id => l_SRTasks_Details_rec.task_status_id,
1218 p_task_priority_id => l_SRTasks_Details_rec.task_priority_id,
1219 p_itemkey => itemkey,
1220 p_return_status => l_return_status);
1221 IF NOT (l_return_status = 'S') THEN
1222 result := 'N';
1223 ELSE
1224 /* 08/30 -- begin - to fix bug# 1964265 */
1225
1226 FND_PROFILE.Get('USER_ID', l_user_id);
1227
1228 JTF_TASKS_PUB.create_task (
1229 p_api_version => 1.0,
1230 p_task_name => l_SRTasks_Details_rec.task_name,
1231 p_task_type_id => l_SRTasks_rec.task_type_id,
1232 /* 08/30 -- begin - to fix bug# 1966258 - made the status to point to the config caprured value*/
1233 p_task_status_id => l_SRTasks_Details_rec.task_status_id,
1234 /* 08/30 -- begin - to fix bug# 1966258 */
1235 p_task_priority_id => l_SRTasks_Details_rec.task_priority_id,
1236 p_owner_type_code => l_SRTasks_Details_rec.owner_type_code,
1237 p_owner_id => l_SRTasks_Details_rec.owner_id,
1238 /* 08/30 -- begin - to fix bug# 1966258 - uncomment the following line*/
1239 p_assigned_by_id => l_user_id,
1240 /* 08/30 -- end - to fix bug# 1966258 */
1241 p_planned_start_date => sysdate,
1242 p_planned_end_date => sysdate,
1243 /* Begin - 09/24/2001 - change made to accomodate Rahul's problem with update_task API */
1244 /* Uncommented the following 3 lines */
1245 p_source_object_type_code => 'SR',
1246 p_source_object_id => l_request_id,
1247 p_source_object_name => l_request_number,
1248 /* End - 09/24/2001 - change made to accomodate Rahul's problem with update_task API */
1249 p_scheduled_start_date => sysdate,
1250 p_scheduled_end_date => sysdate,
1251 p_private_flag => l_SRTasks_Details_rec.private_flag,
1252 p_publish_flag => l_SRTasks_Details_rec.publish_flag,
1253 x_return_status => l_return_status,
1254 x_msg_count => l_msg_count,
1255 x_msg_data => l_msg_data,
1256 x_task_id => l_task_id
1257 );
1258
1259 /* 08/30 -- begin - to fix bug# 1964265 */
1260 IF NOT (l_return_status = fnd_api.g_ret_sts_success) THEN
1261 result := 'N';
1262 ELSE
1263 /* 08/30 -- end - to fix bug# 1964265 */
1264
1265 /* To fix bug # - 1966258 - Start */
1266 IF(l_SRTasks_Details_rec.assignee_type_code is not null AND
1267 l_SRTasks_Details_rec.assigned_by_id is not null) THEN
1268 jtf_task_assignments_pub.create_task_assignment(
1269 p_api_version => 1.0,
1270 p_init_msg_list => cs_core_util.get_g_true,
1271 p_commit => cs_core_util.get_g_true,
1272 p_task_id => l_task_id,
1273 p_resource_type_code => l_SRTasks_Details_rec.assignee_type_code,
1274 p_resource_id => l_SRTasks_Details_rec.assigned_by_id,
1275 p_assignment_status_id => l_SRTasks_Details_rec.task_status_id,
1276 x_return_status => l_return_status,
1277 x_msg_count => l_msg_count,
1278 x_msg_data => l_msg_data,
1279 x_task_assignment_id => l_task_assignment_id);
1280 END IF;
1281 /* To fix bug # - 1966258 - End */
1282
1283 /* Begin - 09/24/2001 - change made to accomodate Rahul's problem with update_task API */
1284 /* commenting out the following section */
1285 /*
1286 update jtf_tasks_b set
1287 source_object_id = l_request_id,
1288 source_object_type_code = 'SR' where
1289 task_id = l_task_id;
1290 */
1291 /* End - 09/24/2001 - change made to accomodate Rahul's problem with update_task API */
1292
1293 l_task_type_id := l_SRTasks_rec.task_type_id;
1294 OPEN c_Workflow_Check_csr;
1295 FETCH c_Workflow_Check_csr into l_workflow_name;
1296
1297 IF (l_workflow_name is not null) THEN
1298 start_task_workflow (
1299 p_api_version => 1.0,
1300 p_commit => 'T',
1301 p_task_id => l_task_id,
1302 p_old_assignee_code => l_SRTasks_Details_rec.assignee_type_code,
1303 p_old_assignee_id => l_SRTasks_Details_rec.assigned_by_id,
1304 p_tsk_typ_attr_dep_id => l_tsk_typ_attr_dep_id,
1305 p_wf_process => l_workflow_name,
1306 p_wf_item_type => 'JTFTASK',
1307 x_return_status => l_return_status,
1308 x_msg_count => l_msg_count,
1309 x_msg_data => l_msg_data
1310 );
1311
1312 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1313 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1314 END IF;
1315
1316 END IF;
1317 CLOSE c_Workflow_Check_csr;
1318 /* 08/30 -- to begin bug# 1964265 */
1319 END IF; -- If l_return_status <> success
1320 END IF; /* If validate_task_details <> 's' */
1321 /* 08/30 -- to end bug# 1964265 */
1322 CLOSE c_SRTasks_Details_csr;
1323 END IF; -- If l_create_task > 0
1324 END LOOP;
1325
1326 END IF; -- If funmode = 'run'
1327
1328 EXCEPTION
1329 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1330 WF_CORE.Context('CUG_GENERIC_WF_PKG', 'CREATE_ALL_SR_TASKS',
1331 itemtype, itemkey, actid, funmode);
1332 RAISE;
1333 WHEN l_API_ERROR THEN
1334 WF_CORE.Context('CUG_GENERIC_WF_PKG', 'CREATE_ALL_SR_TASKS',
1335 itemtype, itemkey, actid, funmode);
1336 RAISE;
1337 WHEN OTHERS THEN
1338 WF_CORE.Context('CUG_GENERIC_WF_PKG', 'CREATE_ALL_SR_TASKS',
1339 itemtype, itemkey, actid, funmode);
1340 RAISE;
1341 END CREATE_ALL_SR_TASKS;
1342
1343
1344
1345 PROCEDURE CHECK_ON_TASK_STATUS(
1346 itemtype VARCHAR2,
1347 itemkey VARCHAR2,
1348 actid NUMBER,
1349 funmode VARCHAR2,
1350 result OUT NOCOPY VARCHAR2 )
1351 IS
1352
1353 l_request_id NUMBER;
1354 l_tasks_pending NUMBER;
1355 l_task_owner_id NUMBER;
1356 l_task_type_id NUMBER;
1357 l_request_type_id NUMBER;
1358 l_loop_counter NUMBER := 0;
1359
1360 l_resource_name VARCHAR2(360);
1361 l_resource_type VARCHAR2(30);
1362 l_resource_id NUMBER;
1363 l_role_name VARCHAR2(60);
1364 l_task_number VARCHAR2(30);
1365
1366 l_API_ERROR EXCEPTION;
1367
1368 CURSOR c_SRTasks_csr IS
1369 SELECT tsk.task_number, tsk.task_id, tsk.task_type_id, sts.name FROM JTF_TASKS_VL tsk, JTF_TASK_STATUSES_VL sts WHERE
1370 tsk.task_status_id = sts.task_status_id and
1371 tsk.SOURCE_OBJECT_ID = l_request_id and
1372 tsk.source_object_type_code = 'SR';
1373 l_SRTasks_rec c_SRTasks_csr%ROWTYPE;
1374
1375 -- Begin of changes by ANEEMUCH date 23-May-2002
1376 -- Bug# 2347600
1377 /*
1378 CURSOR c_GetTaskOwnerId_csr IS
1379 SELECT cst.owner_id from CUG_SR_TASK_TYPE_DETS_B cst, CUG_TSK_TYP_ATTR_DEPS_B cta WHERE
1380 cst.tsk_typ_attr_dep_id = cta.tsk_typ_attr_dep_id AND
1381 cta.task_type_id = l_task_type_id AND
1382 cta.incident_type_id = l_request_type_id;
1383
1384 CURSOR c_GetResourceName_csr is
1385 SELECT source_name from JTF_RS_RESOURCE_EXTNS WHERE
1386 resource_id = l_task_owner_id;
1387 */
1388
1389 CURSOR c_GetTaskOwnerId_csr IS
1390 SELECT cst.owner_id, cst.owner_type_code
1391 from CUG_SR_TASK_TYPE_DETS_B cst, CUG_TSK_TYP_ATTR_DEPS_B cta
1392 WHERE cst.tsk_typ_attr_dep_id = cta.tsk_typ_attr_dep_id AND
1393 cta.task_type_id = l_task_type_id AND
1394 cta.incident_type_id = l_request_type_id;
1395
1396 CURSOR c_GetResourceName_csr is
1397 SELECT resource_id from JTF_RS_RESOURCE_EXTNS WHERE
1398 resource_id = l_task_owner_id;
1399
1400 --
1401 -- End of changes by ANEEMUCH date 23-May-2002
1402
1403 BEGIN
1404
1405 -- For the given incident_id, check on the task status of all it's tasks
1406 -- If the task status has changed to success status continue to check the next task
1407 -- Else, if the task status has changed to failed, set error_type item attribute = 'failed task'
1408 -- set result = 'failed task status' and return
1409
1410 IF (funmode = 'RUN') THEN
1411
1412 l_request_id := WF_Engine.GetItemAttrNumber(itemtype => itemtype,
1413 itemkey => itemkey,
1414 aname => 'REQUEST_ID');
1415
1416 SELECT incident_type_id into l_request_type_id from CS_INCIDENTS_ALL_B where incident_id = l_request_id;
1417
1418 l_tasks_pending := 0;
1419
1420 OPEN c_SRTasks_csr;
1421 LOOP
1422
1423 FETCH c_SRTasks_csr into l_SRTasks_rec;
1424 EXIT WHEN c_SRTasks_csr%NOTFOUND;
1425
1426 -- FOR l_SRTasks_rec IN c_SRTasks_csr LOOP
1427 l_task_type_id := l_SRTasks_rec.task_type_id;
1428
1429 IF (l_SRTasks_rec.name = FND_PROFILE.Value('CUG_TASK_FAILED_STATUS')) THEN
1430 WF_Engine.SetItemAttrText(itemtype => itemtype,
1431 itemkey => itemkey,
1432 aname => 'CUG_ERROR_TYPE',
1433 avalue => 'failed task');
1434
1435 WF_Engine.SetItemAttrText(itemtype => itemtype,
1436 itemkey => itemkey,
1437 aname => 'CUG_TASK_NUMBER',
1438 avalue => l_SRTasks_rec.task_number);
1439
1440 -- Begin of changes by ANEEMUCH date 23-May-2002
1441 -- Bug# 2347600
1442 /*
1443 OPEN c_GetTaskOwnerId_csr;
1444 FETCH c_GetTaskOwnerId_csr into l_task_owner_id;
1445 CLOSE c_GetTaskOwnerId_csr;
1446
1447 OPEN c_GetResourceName_csr;
1448 FETCH c_GetResourceName_csr into l_resource_name;
1449 CLOSE c_GetResourceName_csr;
1450 */
1451
1452 OPEN c_GetTaskOwnerId_csr;
1453 FETCH c_GetTaskOwnerId_csr into l_task_owner_id, l_resource_type;
1454 CLOSE c_GetTaskOwnerId_csr;
1455
1456 OPEN c_GetResourceName_csr;
1457 FETCH c_GetResourceName_csr into l_resource_id;
1458 CLOSE c_GetResourceName_csr;
1459
1460 IF (l_resource_type = 'RS_EMPLOYEE') THEN
1461 l_role_name := JTF_RS_RESOURCE_PUB.get_wf_role(l_resource_id);
1462 IF (l_role_name IS NULL) THEN
1463 l_resource_name := FND_PROFILE.VALUE('CUG_DEFAULT_TASK_WF_ROLE');
1464 ELSE
1465 l_resource_name := l_role_name;
1466 END IF;
1467 ELSE
1468 l_role_name := FND_PROFILE.VALUE('CUG_DEFAULT_TASK_WF_ROLE');
1469 END IF;
1470 --
1471 -- End of changes by ANEEMUCH date 23-May-2002
1472
1473 WF_Engine.SetItemAttrText(itemtype => itemtype,
1474 itemkey => itemkey,
1475 aname => 'CUG_TASK_OWNER_NAME',
1476 avalue => l_resource_name);
1477
1478 WF_Engine.SetItemAttrNumber(itemtype => itemtype,
1479 itemkey => itemkey,
1480 aname => 'CUG_TASK_ID',
1481 avalue => l_SRTasks_rec.task_id);
1482
1483
1484 result := 'CUGCIC_A_TASK_FAILED';
1485 return;
1486 ELSIF (l_SRTasks_rec.name = FND_PROFILE.Value('CUG_TASK_SUCCESS_STATUS')) THEN
1487 l_tasks_pending := 1;
1488 ELSE
1489 l_tasks_pending := 0;
1490 result := 'CUGCIC_WAITING_FOR_COMPLETION';
1491 return;
1492 END IF;
1493 l_loop_counter := l_loop_counter + 1;
1494 END LOOP;
1495
1496 CLOSE c_SRTasks_csr;
1497
1498 IF (l_tasks_pending = 0) THEN
1499 result := 'CUGCIC_WAITING_FOR_COMPLETION';
1500 ELSIF (l_tasks_pending = 1) THEN
1501 result := 'CUGCIC_TASKS_COMPLETED';
1502 END IF;
1503
1504 IF (l_loop_counter = 0) THEN
1505 result := 'CUGCIC_TASKS_COMPLETED';
1506
1507
1508 END IF;
1509
1510 END IF;
1511
1512 EXCEPTION
1513 WHEN l_API_ERROR THEN
1514 WF_CORE.Context('CUG_GENERIC_WF_PKG', 'CHECK_ON_TASK_STATUS',
1515 itemtype, itemkey, actid, funmode);
1516 RAISE;
1517 WHEN OTHERS THEN
1518 WF_CORE.Context('CUG_GENERIC_WF_PKG', 'CHECK_ON_TASK_STATUS',
1519 itemtype, itemkey, actid, funmode);
1520 RAISE;
1521
1522 END CHECK_ON_TASK_STATUS;
1523
1524
1525
1526 PROCEDURE CALCULATE_DATE(p_uom IN VARCHAR2,
1527 p_offset IN NUMBER,
1528 x_date OUT NOCOPY DATE)
1529 IS
1530 l_offset NUMBER;
1531 l_multiple_by NUMBER;
1532 l_uom VARCHAR2(30);
1533
1534 BEGIN
1535 l_offset := p_offset;
1536 l_uom := p_uom;
1537
1538 IF ( l_uom = 'Weeks') THEN
1539 l_multiple_by := 7;
1540 ELSE
1541 l_multiple_by := 1;
1542 END IF;
1543
1544 l_multiple_by := l_multiple_by * l_offset;
1545
1546 x_date := sysdate + l_multiple_by;
1547
1548 END CALCULATE_DATE;
1549
1550
1551
1552 PROCEDURE CALCULATE_DUPLICATE_TIME_FRAME(p_service_request_id NUMBER,
1553 p_request_type_id NUMBER,
1554 p_duplicate_time_frame OUT NOCOPY DATE)
1555 IS
1556 l_request_id NUMBER;
1557 l_request_type_id NUMBER;
1558 l_multiple_by NUMBER;
1559
1560 l_duplicate_uom VARCHAR2(30);
1561
1562 CURSOR c_SRTypeID_csr IS
1563 SELECT incident_type_id FROM CS_INCIDENTS_ALL_VL
1564 WHERE INCIDENT_ID = l_request_id;
1565
1566
1567 CURSOR c_DuplicateTimeInfo_csr IS
1568 SELECT duplicate_offset, duplicate_uom FROM CUG_SR_TYPE_DUP_CHK_INFO
1569 WHERE INCIDENT_TYPE_ID = l_request_type_id;
1570 l_DuplicateTimeInfo_rec c_DuplicateTimeInfo_csr%ROWTYPE;
1571
1572 CURSOR c_UOM_Conversion_Rate_csr IS
1573 SELECT conversion_rate FROM MTL_UOM_CONVERSIONS
1574 WHERE UNIT_OF_MEASURE = l_duplicate_uom;
1575 l_UOM_Conversion_Rate_rec c_UOM_Conversion_Rate_csr%ROWTYPE;
1576
1577 BEGIN
1578
1579
1580
1581 l_request_id := p_service_request_id;
1582
1583 OPEN c_SRTypeID_csr;
1584 FETCH c_SRTypeID_csr INTO l_request_type_id;
1585
1586 OPEN c_DuplicateTimeInfo_csr;
1587 FETCH c_DuplicateTimeInfo_csr INTO l_DuplicateTimeInfo_rec;
1588 IF (c_DuplicateTimeInfo_csr%NOTFOUND) THEN
1589 RAISE fnd_api.g_exc_unexpected_error;
1590 END IF;
1591
1592 l_duplicate_uom := l_DuplicateTimeInfo_rec.duplicate_uom;
1593
1594 OPEN c_UOM_Conversion_Rate_csr;
1595 FETCH c_UOM_Conversion_Rate_csr into l_UOM_Conversion_Rate_rec;
1596 IF (c_UOM_Conversion_Rate_csr%NOTFOUND) THEN
1597 RAISE fnd_api.g_exc_unexpected_error;
1598 END IF;
1599
1600 IF ( l_DuplicateTimeInfo_rec.duplicate_uom = 'Day') THEN
1601 l_multiple_by := 1;
1602 ELSIF (l_DuplicateTimeInfo_rec.duplicate_uom = 'Hour') THEN
1603 l_multiple_by := l_DuplicateTimeInfo_rec.duplicate_offset/24;
1604 ELSIF (l_DuplicateTimeInfo_rec.duplicate_uom = 'Month') THEN
1605 l_multiple_by := (l_DuplicateTimeInfo_rec.duplicate_offset * 720)/24;
1606 ELSIF (l_DuplicateTimeInfo_rec.duplicate_uom = 'Week') THEN
1607 l_multiple_by := (l_DuplicateTimeInfo_rec.duplicate_offset*168)/24;
1608 ELSIF (l_DuplicateTimeInfo_rec.duplicate_uom = 'Year') THEN
1609 l_multiple_by := (l_DuplicateTimeInfo_rec.duplicate_offset*8760)/24;
1610 ELSIF (l_DuplicateTimeInfo_rec.duplicate_uom = 'Minute') THEN
1611 l_multiple_by := (l_DuplicateTimeInfo_rec.duplicate_offset*0.016667)/24;
1612 ELSIF (l_DuplicateTimeInfo_rec.duplicate_uom = 'Jal') THEN
1613 l_multiple_by := (l_DuplicateTimeInfo_rec.duplicate_offset*0.016667)/24;
1614 ELSE
1615 l_multiple_by := (l_DuplicateTimeInfo_rec.duplicate_offset*l_UOM_Conversion_Rate_rec.conversion_rate)/24;
1616 END IF;
1617
1618 -- l_multiple_by := l_multiple_by * l_DuplicateTimeInfo_rec.duplicate_offset;
1619
1620 p_duplicate_time_frame := sysdate - l_multiple_by;
1621
1622 END CALCULATE_DUPLICATE_TIME_FRAME;
1623
1624
1625
1626 PROCEDURE start_task_workflow (
1627 p_api_version IN NUMBER,
1628 p_init_msg_list IN VARCHAR2,
1629 p_commit IN VARCHAR2,
1630 p_task_id IN NUMBER,
1631 p_old_assignee_code IN VARCHAR2,
1632 p_old_assignee_id IN NUMBER,
1633 p_new_assignee_code IN VARCHAR2,
1634 p_new_assignee_id IN NUMBER,
1635 p_old_owner_code IN VARCHAR2,
1636 p_old_owner_id IN NUMBER,
1637 p_new_owner_code IN VARCHAR2,
1638 p_new_owner_id IN NUMBER,
1639 p_wf_display_name IN VARCHAR2,
1640 p_tsk_typ_attr_dep_id IN NUMBER,
1641 p_wf_process IN VARCHAR2,
1642 p_wf_item_type IN VARCHAR2,
1643 x_return_status OUT NOCOPY VARCHAR2,
1644 x_msg_count OUT NOCOPY NUMBER,
1645 x_msg_data OUT NOCOPY VARCHAR2
1646 )
1647 IS
1648 l_api_version CONSTANT NUMBER
1649 := 1.0;
1650 l_api_name CONSTANT VARCHAR2(30)
1651 := 'START_TASK_WORKFLOW';
1652 l_wf_process_id NUMBER;
1653 l_itemkey wf_item_activity_statuses.item_key%TYPE;
1654 l_old_assigned_user_name fnd_user.user_name%TYPE;
1655 l_new_assigned_user_name fnd_user.user_name%TYPE;
1656 l_owner_user_name fnd_user.user_name%TYPE;
1657 l_task_name jtf_tasks_tl.task_name%TYPE;
1658 l_description jtf_tasks_tl.description%TYPE;
1659 l_owner_code jtf_tasks_b.owner_type_code%TYPE;
1660 l_owner_id jtf_tasks_b.owner_id%TYPE;
1661 l_task_number jtf_tasks_b.task_number%TYPE;
1662 l_task_status_name jtf_tasks_v.task_status%type ;
1663 l_task_type_name jtf_tasks_v.task_type%type ;
1664 l_task_priority_name jtf_tasks_v.task_priority%type ;
1665 current_record NUMBER;
1666 source_text VARCHAR2(200);
1667 l_errname varchar2(60);
1668 l_errmsg varchar2(2000);
1669 l_errstack varchar2(4000);
1670 l_task_dep_id NUMBER;
1671
1672 CURSOR c_wf_processs_id
1673 IS
1674 SELECT jtf_task_workflow_process_s.nextval
1675 FROM dual;
1676
1677 CURSOR c_task_details
1678 IS
1679 SELECT task_name, description, owner_type_code owner_code, owner_id, task_number
1680
1681 FROM jtf_tasks_v
1682 WHERE task_id = p_task_id;
1683 BEGIN
1684
1685
1686
1687 SAVEPOINT start_task_workflow;
1688 x_return_status := fnd_api.g_ret_sts_success;
1689 l_task_dep_id := p_tsk_typ_attr_dep_id;
1690
1691 IF fnd_api.to_boolean (p_init_msg_list)
1692 THEN
1693 fnd_msg_pub.initialize;
1694 END IF;
1695
1696
1697 OPEN c_wf_processs_id;
1698 FETCH c_wf_processs_id INTO l_wf_process_id;
1699 CLOSE c_wf_processs_id;
1700 l_itemkey := TO_CHAR (p_task_id) || '-' || TO_CHAR (l_wf_process_id);
1701 OPEN c_task_details;
1702 FETCH c_task_details INTO l_task_name,
1703 l_description,
1704 l_owner_code,
1705 l_owner_id,
1706 l_task_number;
1707
1708 IF c_task_details%NOTFOUND
1709 THEN
1710 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_ID');
1711 fnd_msg_pub.add;
1712 RAISE fnd_api.g_exc_unexpected_error;
1713 END IF;
1714 CLOSE c_task_details;
1715
1716
1717 wf_engine.createprocess (
1718 itemtype => 'JTFTASK',
1719 itemkey => l_itemkey,
1720 process => p_wf_process
1721 );
1722
1723
1724 wf_engine.setitemuserkey (
1725 itemtype => 'JTFTASK',
1726 itemkey => l_itemkey,
1727 userkey => l_task_name
1728 );
1729
1730 wf_engine.setitemattrtext (
1731 itemtype => 'JTFTASK',
1732 itemkey => l_itemkey,
1733 aname => 'TASK_NAME',
1734 avalue => l_task_name
1735 );
1736
1737
1738 wf_engine.setitemattrtext (
1739 itemtype => 'JTFTASK',
1740 itemkey => l_itemkey,
1741 aname => 'TASK_DESC',
1742 avalue => l_description
1743 );
1744
1745 wf_engine.setitemattrtext (
1746 itemtype => 'JTFTASK',
1747 itemkey => l_itemkey,
1748 aname => 'TASK_NUMBER',
1749 avalue => l_task_number
1750 );
1751
1752 select task_status, task_priority , task_type
1753 into l_task_status_name, l_task_priority_name , l_task_type_name
1754 from jtf_tasks_v where task_id = p_task_id ;
1755
1756
1757 wf_engine.setitemattrtext (
1758 itemtype => 'JTFTASK',
1759 itemkey => l_itemkey,
1760 aname => 'TASK_STATUS_NAME',
1761 avalue => l_task_status_name
1762 );
1763
1764 wf_engine.setitemattrtext (
1765 itemtype => 'JTFTASK',
1766 itemkey => l_itemkey,
1767 aname => 'TASK_PRIORITY_NAME',
1768 avalue => l_task_priority_name
1769 );
1770
1771 wf_engine.setitemattrtext (
1772 itemtype => 'JTFTASK',
1773 itemkey => l_itemkey,
1774 aname => 'TASK_TYPE_NAME',
1775 avalue => l_task_type_name
1776 );
1777
1778 ----
1779 ---- Task Owner
1780 ----
1781
1782 /* Roopa
1783 l_owner_user_name := jtf_rs_resource_pub.get_wf_role( l_owner_id );
1784
1785 if l_owner_user_name is null then
1786 raise fnd_api.g_exc_unexpected_error;
1787 end if ;
1788 End Roopa */
1789
1790
1791 wf_engine.setitemattrtext (
1792 itemtype => 'JTFTASK',
1793 itemkey => l_itemkey,
1794 aname => 'OWNER_ID',
1795 avalue => l_owner_user_name
1796 );
1797
1798
1799 /* Roopa
1800 wf_engine.setitemattrtext (
1801 itemtype => 'JTFTASK',
1802 itemkey => l_itemkey,
1803 aname => 'OWNER_NAME',
1804 -- avalue => wf_directory.getroledisplayname (l_owner_user_name)
1805 avalue => jtf_task_utl.get_owner(l_owner_code, l_owner_id)
1806 );
1807 End Roopa */
1808
1809 wf_engine.setitemattrtext (
1810 itemtype => 'JTFTASK',
1811 itemkey => l_itemkey,
1812 aname => 'TASK_DESC',
1813 avalue => l_description
1814 );
1815
1816
1817 wf_engine.setitemattrnumber (
1818 itemtype => 'JTFTASK',
1819 itemkey => l_itemkey,
1820 aname => 'CUG_TASK_DEP_ID',
1821 avalue => l_task_dep_id
1822 );
1823
1824 wf_engine.startprocess (
1825 itemtype => 'JTFTASK',
1826 itemkey => l_itemkey
1827 );
1828
1829 IF fnd_api.to_boolean (p_commit)
1830 THEN
1831 -- COMMIT WORK;
1832 return;
1833 END IF;
1834
1835
1836 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1837
1838 EXCEPTION
1839 WHEN fnd_api.g_exc_unexpected_error
1840 THEN
1841 ROLLBACK TO start_task_workflow;
1842 x_return_status := fnd_api.g_ret_sts_unexp_error;
1843 fnd_msg_pub.count_and_get (
1844 p_count => x_msg_count,
1845 p_data => x_msg_data
1846 );
1847 WHEN OTHERS
1848 THEN
1849
1850 ROLLBACK TO start_task_workflow ;
1851
1852 wf_core.get_error(l_errname, l_errmsg, l_errstack);
1853 if (l_errname is not null) then
1854 fnd_message.set_name('FND', 'WF_ERROR');
1855 fnd_message.set_token('ERROR_MESSAGE', l_errmsg);
1856 fnd_message.set_token('ERROR_STACK', l_errstack);
1857 fnd_msg_pub.add;
1858 end if;
1859
1860 x_return_status := fnd_api.g_ret_sts_unexp_error;
1861 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1862 END;
1863
1864
1865 PROCEDURE VALIDATE_TASK_DETAILS(p_task_type_id NUMBER,
1866 p_task_status_id NUMBER,
1867 p_task_priority_id NUMBER,
1868 p_itemkey VARCHAR2,
1869 p_return_status OUT NOCOPY VARCHAR2)
1870 IS
1871 CURSOR csr_task_type_id
1872 IS
1873 SELECT task_type_id
1874 FROM jtf_task_types_vl
1875 WHERE task_type_id = p_task_type_id
1876 AND (START_DATE_ACTIVE IS NULL OR
1877 to_number(to_char(START_DATE_ACTIVE, 'YYYYMMDD')) <= to_number(to_char(sysdate, 'YYYYMMDD')) ) AND
1878 (END_DATE_ACTIVE is NULL OR
1879 to_number(to_char(END_DATE_ACTIVE, 'YYYYMMDD')) >= to_number(to_char(sysdate, 'YYYYMMDD')) );
1880
1881
1882 CURSOR csr_task_priority_id
1883 IS
1884 SELECT task_priority_id
1885 FROM jtf_task_priorities_b
1886 WHERE task_priority_id = p_task_priority_id
1887 AND (START_DATE_ACTIVE IS NULL OR
1888 to_number(to_char(START_DATE_ACTIVE, 'YYYYMMDD')) <= to_number(to_char(sysdate, 'YYYYMMDD')) ) AND
1889 (END_DATE_ACTIVE is NULL OR
1890 to_number(to_char(END_DATE_ACTIVE, 'YYYYMMDD')) >= to_number(to_char(sysdate, 'YYYYMMDD')) );
1891
1892
1893 CURSOR csr_task_status_id
1894 IS
1895 SELECT task_status_id
1896 FROM jtf_task_statuses_b
1897 WHERE task_status_id = p_task_status_id
1898 AND (START_DATE_ACTIVE IS NULL OR
1899 to_number(to_char(START_DATE_ACTIVE, 'YYYYMMDD')) <= to_number(to_char(sysdate, 'YYYYMMDD')) ) AND
1900 (END_DATE_ACTIVE is NULL OR
1901 to_number(to_char(END_DATE_ACTIVE, 'YYYYMMDD')) >= to_number(to_char(sysdate, 'YYYYMMDD')) );
1902
1903
1904
1905 l_task_type_id NUMBER;
1906 l_task_status_id NUMBER;
1907 l_task_priority_id NUMBER;
1908 l_task_name VARCHAR2(30);
1909
1910 BEGIN
1911
1912 p_return_status := 'S';
1913 SELECT name into l_task_name
1914 FROM jtf_task_types_vl
1915 WHERE task_type_id = p_task_type_id;
1916 WF_ENGINE.SetItemAttrText(
1917 itemtype => 'SERVEREQ',
1918 itemkey => p_itemkey,
1919 aname => 'CUG_TASK_TYPE',
1920 avalue => l_task_name);
1921
1922
1923 OPEN csr_task_type_id;
1924 FETCH csr_task_type_id into l_task_type_id;
1925 IF (csr_task_type_id%NOTFOUND) THEN
1926 p_return_status := 'U';
1927 END IF;
1928 CLOSE csr_task_type_id;
1929
1930 OPEN csr_task_status_id;
1931 FETCH csr_task_status_id into l_task_status_id;
1932 IF (csr_task_status_id%NOTFOUND) THEN
1933 p_return_status := 'U';
1934 END IF;
1935 CLOSE csr_task_status_id;
1936
1937 OPEN csr_task_priority_id;
1938 FETCH csr_task_priority_id into l_task_priority_id;
1939 IF (csr_task_priority_id%NOTFOUND) THEN
1940 p_return_status := 'U';
1941 END IF;
1942 CLOSE csr_task_priority_id;
1943
1944 EXCEPTION
1945 WHEN OTHERS THEN
1946 p_return_status := 'U';
1947
1948 END VALIDATE_TASK_DETAILS;
1949
1950
1951 -- -----------------------------------------------------------------------
1952 -- Update_CIC_Request_Info
1953 -- Refresh the item attributes with the latest values in the database.
1954 -- -----------------------------------------------------------------------
1955
1956 -- Roopa - This procedure is added to fix bug # 2576759
1957 -- This proc is a copy of CS_WF_ACTIVITIES_PKG.UPDATE_REQUEST_INFO procedure
1958 -- The only difference here is that the validation of the incident owner as well as
1959 -- setting the owner attributes is skipped if the incident owner id is null for the current SR
1960
1961 PROCEDURE Update_CIC_Request_Info ( itemtype VARCHAR2,
1962 itemkey VARCHAR2,
1963 actid NUMBER,
1964 funmode VARCHAR2,
1965 result OUT NOCOPY VARCHAR2 ) IS
1966
1967 l_return_status VARCHAR2(1);
1968 l_msg_count NUMBER;
1969 l_msg_data VARCHAR2(2000);
1970 l_request_id NUMBER;
1971 l_owner_role VARCHAR2(100);
1972 l_owner_name VARCHAR2(240);
1973 l_errmsg_name VARCHAR2(30);
1974 l_API_ERROR EXCEPTION;
1975
1976 CURSOR l_ServiceRequest_csr IS
1977 SELECT inc.customer_product_id, inc.expected_resolution_date,inc.inventory_item_id,inc.summary, emp.source_id incident_owner_id
1978 FROM cs_incidents_all_vl inc ,cs_sr_owners_v owner, jtf_rs_resource_extns emp
1979 WHERE inc.INCIDENT_OWNER_ID = owner.resource_id(+) AND
1980 emp.resource_id = owner.resource_id AND
1981 incident_id = l_request_id;
1982
1983 l_ServiceRequest_rec l_ServiceRequest_csr%ROWTYPE;
1984
1985 BEGIN
1986
1987 IF (funmode = 'RUN') THEN
1988
1989 -- Get the service request ID
1990 l_request_id := WF_ENGINE.GetItemAttrNumber(
1991 itemtype => itemtype,
1992 itemkey => itemkey,
1993 aname => 'REQUEST_ID' );
1994
1995 -- Extract the service request record
1996 OPEN l_ServiceRequest_csr;
1997 FETCH l_ServiceRequest_csr INTO l_ServiceRequest_rec;
1998
1999
2000 -- fix for bug 2576759. Added the following if condition - Roopa
2001
2002 IF(l_ServiceRequest_rec.incident_owner_id is not null) THEN
2003 -- Retrieve the role name for the request owner
2004 CS_WORKFLOW_PUB.Get_Employee_Role (
2005 p_api_version => 1.0,
2006 p_return_status => l_return_status,
2007 p_msg_count => l_msg_count,
2008 p_msg_data => l_msg_data,
2009 p_employee_id => l_ServiceRequest_rec.incident_owner_id,
2010 p_role_name => l_owner_role,
2011 p_role_display_name => l_owner_name );
2012
2013 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) OR
2014 (l_owner_role is NULL) THEN
2015 wf_core.context( pkg_name => 'CS_WORKFLOW_PUB',
2016 proc_name => 'Get_Employee_Role',
2017 arg1 => 'p_employee_id=>'||
2018 to_char(l_ServiceRequest_rec.incident_owner_id));
2019 l_errmsg_name := 'CS_WF_SR_CANT_FIND_OWNER';
2020 raise l_API_ERROR;
2021 END IF;
2022
2023 -- Update service request item attributes
2024 WF_ENGINE.SetItemAttrNumber(
2025 itemtype => 'SERVEREQ',
2026 itemkey => itemkey,
2027 aname => 'OWNER_ID',
2028 avalue => l_ServiceRequest_rec.incident_owner_id );
2029
2030 WF_ENGINE.SetItemAttrText(
2031 itemtype => 'SERVEREQ',
2032 itemkey => itemkey,
2033 aname => 'OWNER_ROLE',
2034 avalue => l_owner_role );
2035
2036 WF_ENGINE.SetItemAttrText(
2037 itemtype => 'SERVEREQ',
2038 itemkey => itemkey,
2039 aname => 'OWNER_NAME',
2040 avalue => l_owner_name );
2041 END IF; -- fix for bug 2576759
2042
2043
2044 WF_ENGINE.SetItemAttrNumber(
2045 itemtype => 'SERVEREQ',
2046 itemkey => itemkey,
2047 aname => 'CUSTOMER_PRODUCT_ID',
2048 avalue => l_ServiceRequest_rec.customer_product_id );
2049
2050 WF_ENGINE.SetItemAttrDate(
2051 itemtype => 'SERVEREQ',
2052 itemkey => itemkey,
2053 aname => 'EXPECTED_RESOLUTION_DATE',
2054 avalue => l_ServiceRequest_rec.expected_resolution_date );
2055
2056 WF_ENGINE.SetItemAttrNumber(
2057 itemtype => 'SERVEREQ',
2058 itemkey => itemkey,
2059 aname => 'INVENTORY_ITEM_ID',
2060 avalue => l_ServiceRequest_rec.inventory_item_id );
2061
2062 WF_ENGINE.SetItemAttrText(
2063 itemtype => 'SERVEREQ',
2064 itemkey => itemkey,
2065 aname => 'REQUEST_SUMMARY',
2066 avalue => l_ServiceRequest_rec.summary );
2067
2068 result := 'COMPLETE';
2069
2070 ELSIF (funmode = 'CANCEL') THEN
2071 result := 'COMPLETE';
2072 END IF;
2073
2074 CLOSE l_ServiceRequest_csr;
2075
2076 EXCEPTION
2077 WHEN l_API_ERROR THEN
2078 IF (l_ServiceRequest_csr%ISOPEN) THEN
2079 CLOSE l_ServiceRequest_csr;
2080 END IF;
2081 WF_CORE.Raise(l_errmsg_name);
2082 WHEN OTHERS THEN
2083 WF_CORE.Context('CUG_GENERIC_WF_PKG', 'Update_CIC_Request_Info',
2084 itemtype, itemkey, actid, funmode);
2085 RAISE;
2086
2087 END Update_CIC_Request_Info;
2088
2089 -- ---------------------------------------------------------------------------
2090 -- Initialize_Request
2091 -- This procedure initializes the item attributes that will remain constant
2092 -- over the duration of the Workflow. These attributes include REQUEST_ID,
2093 -- REQUEST_NUMBER, REQUEST_DATE, and REQUEST_TYPE. In addition, the
2094 -- ESCALATION_HISTORY item attribute is initialized with the assignment
2095 -- information of the current owner.
2096 -- ---------------------------------------------------------------------------
2097
2098 PROCEDURE CIC_Initialize_Request( itemtype VARCHAR2,
2099 itemkey VARCHAR2,
2100 actid NUMBER,
2101 funmode VARCHAR2,
2102 result OUT NOCOPY VARCHAR2 ) IS
2103
2104 l_msg_count NUMBER;
2105 l_msg_data VARCHAR2(2000);
2106 l_request_number VARCHAR2(64);
2107 l_dummy NUMBER;
2108 l_return_status VARCHAR2(1);
2109 l_API_ERROR EXCEPTION;
2110
2111 CURSOR l_ServiceRequest_csr IS
2112 SELECT *
2113 FROM CS_INCIDENTS_ALL_VL
2114 WHERE INCIDENT_NUMBER = l_request_number;
2115
2116 l_ServiceRequest_rec l_ServiceRequest_csr%ROWTYPE;
2117 l_errmsg_name VARCHAR2(30);
2118
2119 BEGIN
2120
2121 IF (funmode = 'RUN') THEN
2122
2123 -- Decode the item key to get the service request number
2124 CS_WORKFLOW_PUB.Decode_Servereq_Itemkey(
2125 p_api_version => 1.0,
2126 p_return_status => l_return_status,
2127 p_msg_count => l_msg_count,
2128 p_msg_data => l_msg_data,
2129 p_itemkey => itemkey,
2130 p_request_number => l_request_number,
2131 p_wf_process_id => l_dummy );
2132
2133 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2134 wf_core.context( pkg_name => 'CS_WORKFLOW_PUB',
2135 proc_name => 'Decode_Servereq_Itemkey',
2136 arg1 => 'p_itemkey=>'||itemkey );
2137 l_errmsg_name := 'CS_WF_SR_CANT_DECODE_ITEMKEY';
2138 raise l_API_ERROR;
2139 END IF;
2140
2141 -- Extract the service request record
2142 OPEN l_ServiceRequest_csr;
2143 FETCH l_ServiceRequest_csr INTO l_ServiceRequest_rec;
2144
2145 -- Initialize item attributes that will remain constant
2146 WF_ENGINE.SetItemAttrDate(
2147 itemtype => 'SERVEREQ',
2148 itemkey => itemkey,
2149 aname => 'REQUEST_DATE',
2150 avalue => l_ServiceRequest_rec.incident_date );
2151
2152 WF_ENGINE.SetItemAttrNumber(
2153 itemtype => 'SERVEREQ',
2154 itemkey => itemkey,
2155 aname => 'REQUEST_ID',
2156 avalue => l_ServiceRequest_rec.incident_id );
2157
2158 WF_ENGINE.SetItemAttrText(
2159 itemtype => 'SERVEREQ',
2160 itemkey => itemkey,
2161 aname => 'REQUEST_NUMBER',
2162 avalue => l_ServiceRequest_rec.incident_number );
2163
2164 /*
2165 WF_ENGINE.SetItemAttrText(
2166 itemtype => 'SERVEREQ',
2167 itemkey => itemkey,
2168 aname => 'REQUEST_TYPE',
2169 avalue => l_ServiceRequest_rec.incident_type );
2170 */
2171 CLOSE l_ServiceRequest_csr;
2172
2173 result := 'COMPLETE';
2174
2175 ELSIF (funmode = 'CANCEL') THEN
2176 result := 'COMPLETE';
2177 END IF;
2178
2179 EXCEPTION
2180 WHEN l_API_ERROR THEN
2181 WF_CORE.Raise(l_errmsg_name);
2182 WHEN OTHERS THEN
2183 WF_CORE.Context('CUG_GENERIC_WF_PKG', 'CIC_Initialize_Request',
2184 itemtype, itemkey, actid, funmode);
2185 RAISE;
2186
2187 END CIC_Initialize_Request;
2188
2189 -- Enter further code below as specified in the Package spec.
2190 END;