[Home] [Help]
PACKAGE BODY: APPS.CUG_WF_EMAIL_UTIL
Source
1 Package Body CUG_WF_EMAIL_UTIL AS
2 /* $Header: CUGWFEUB.pls 120.0 2005/07/20 12:13:45 appldev 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
8 --
9 -- MODIFICATION HISTORY
10 -- Person Date Comments
11 -- --------- ------ ------------------------------------------
12 -- Enter package declarations as shown below
13 PROCEDURE Get_SR_Details
14 ( ITEMTYPE IN VARCHAR2 -- Workflow Item Type := 'JTFTASKS'
18 ,RESULTOUT OUT NOCOPY VARCHAR2
15 ,ITEMKEY IN VARCHAR2 -- to_char(p_task_id)||'-'||to_char(l_wf_process_id)
16 ,ACTID IN NUMBER
17 ,FUNCMODE IN VARCHAR2
19 )IS
20 -- Enter the procedure variables here. As shown below
21 --variable_name datatype NOT NULL DEFAULT default_value;
22 lv_itemtype VARCHAR2(8);
23 lv_itemkey VARCHAR2(240);
24 lv_task_number VARCHAR2(30);
25 lv_incident_number VARCHAR2(64);
26 lv_release_resource_type VARCHAR2(80);
27 X_RETURN_STATUS VARCHAR2(80):= FND_API.G_RET_STS_SUCCESS;
28
29 lv_task_id NUMBER := 0;
30 lv_task_type_id NUMBER := 0;
31 lv_incident_id NUMBER := 0;
32 lv_incident_type_id NUMBER := 0;
33
34 lv_incident_date DATE;
35
36
37 BEGIN
38 IF ( funcmode = 'RUN' ) THEN
39
40 lv_itemtype := itemtype;
41 lv_itemkey := itemkey;
42
43 -- Get the Service Request Id by using the GetItemAttr function for 'TASK_ID' and then
44 -- execute a SQL query to find out the source_object_id from JTF_TASKS_VL view for the
45 lv_task_number := wf_engine.GetItemAttrText(
46 ITEMTYPE => lv_itemtype,
47 ITEMKEY => lv_itemkey,
48 ANAME => 'TASK_NUMBER' );
49
50 -- Make sure by putting specific check that it will execute only for EMAIL NOTIFICATION TASK TYPE
51 -- for a given Service Request
52 select ciav.incident_id, ciav.incident_type_id , ciav.incident_number,
53 ciav.incident_date, jtv.task_id, jtv.task_type_id
54 into lv_incident_id, lv_incident_type_id, lv_incident_number,
55 lv_incident_date, lv_task_id, lv_task_type_id
56 from cs_incidents_all_b ciav,
57 jtf_tasks_b jtv
58 where ciav.incident_id = jtv.source_object_id
59 and jtv.task_number = lv_task_number
60 and jtv.source_object_type_code ='SR';
61
62
63
64
65 Get_Task_Attrs_Details (
66 ITEMTYPE
67 , ITEMKEY
68 , lv_incident_type_id
69 , lv_task_id
70 , lv_task_type_id
71 , X_RETURN_STATUS );
72
73
74
75
76 IF (X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS ) THEN
77 RESULTOUT := 'COMPLETE:';
78 return;
79 END IF;
80
81 -- Call the Get_Incident_Addr_Details procedure. This call, after getting the
82 -- address details from HZ_LOCATIONS table for the given Service Request, set
83 -- the item attribute 'CUG_INC_ADDR' in the workflow.
84
85
86
87
88 Get_Incident_Addr_Details (
89 ITEMTYPE
90 ,ITEMKEY
91 ,lv_incident_number
92 ,X_RETURN_STATUS );
93
94
95 IF (X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS ) THEN
96 RESULTOUT := 'COMPLETE:';
97 return;
98 END IF;
99
100 -- Get the Service Request Attributes details and store it in the
101 -- Workflow Item Attributes declared from CUG_SR_ATTRS_DET1 to CUG_SR_ATTRS_DET5
102
103
104
105 Get_SR_Attrs_Details (
106 ITEMTYPE
107 , ITEMKEY
108 , lv_incident_id
109 , lv_incident_type_id
110 , lv_incident_number
111 , lv_task_number
112 , X_RETURN_STATUS );
113
114
115
116 IF (X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS ) THEN
117 RESULTOUT := 'COMPLETE:';
118 return;
119 END IF;
120 RESULTOUT := 'COMPLETE:';
121 return;
122 END IF;
123 EXCEPTION
124 WHEN NO_DATA_FOUND THEN
125 RESULTOUT := 'COMPLETE:';
126 return;
127 When Others then
128 WF_CORE.context('CUG_WF_EMAIL_UTIL', 'GET_SR_DETAILS', itemtype, itemkey, actid, funcmode );
129 RAISE;
130 END Get_SR_Details;
131
132 PROCEDURE Get_Task_Attrs_Details
133 (
134 ITEMTYPE IN VARCHAR2
135 , ITEMKEY IN VARCHAR2
136 , INCIDENT_TYPE_ID IN NUMBER
137 , TASK_ID IN NUMBER
138 , TASK_TYPE_ID IN NUMBER
139 , X_RETURN_STATUS OUT NOCOPY VARCHAR2
140 ) IS
141 --Declaration of all the local variables
142 lv_itemtype VARCHAR2(8);
143 lv_itemkey VARCHAR2(240);
144 lv_incident_type_id NUMBER;
145 lv_task_id NUMBER;
146 lv_task_type_id NUMBER;
147 lv_attr_name VARCHAR2(30);
148 lv_task_type_attr_value VARCHAR2(1997);
149 lv_task_type_attr_name VARCHAR2(30);
150 lv_task_type_attr_type VARCHAR2(8);
151 lv_task_type_attr_dep_id NUMBER;
152 lv_incident_date DATE;
153
154 -- Cursor declaration for all the Task attributes from CUG_TSK_TYP_ATTR_MAPS_VL table
155 -- for a given TASK_TYPE_ID, and TASK_ID as input parameter, and ITEMTYPE is the Workflow
156 -- internal Name for Task related workflow.
157
158 CURSOR c_all_task_attrs IS
159 SELECT task_type_attribute_code
160 FROM cug_tsk_typ_attr_maps_vl
161 WHERE TASK_TYPE_ID = lv_task_type_id;
162
163 BEGIN
164 lv_itemtype := ITEMTYPE;
165 lv_itemkey := ITEMKEY;
166 lv_task_id := TASK_ID;
170 lv_task_type_attr_dep_id := wf_engine.GetItemAttrText(
167 lv_task_type_id:= TASK_TYPE_ID;
168 lv_incident_type_id := INCIDENT_TYPE_ID;
169
171 ITEMTYPE => lv_itemtype,
172 ITEMKEY => lv_itemkey,
173 ANAME => 'CUG_TASK_DEP_ID' );
174
175 OPEN c_all_task_attrs;
176 LOOP
177 lv_attr_name := null;
178 -- Fetch all the attributes Name from the cursor.
179 FETCH c_all_task_attrs INTO lv_attr_name;
180 EXIT WHEN c_all_task_attrs%NOTFOUND;
181
182 -- Process for Each Attribute one by one in the loop by getting the attribute name and value.
183 SELECT cvals.task_type_attr_value, wiav.name, wiav.type
184 INTO lv_task_type_attr_value, lv_task_type_attr_name, lv_task_type_attr_type
185 FROM CUG_TSK_TYP_ATTR_MAPS_VL cttamv,
186 cug_sr_task_attr_vals_vl cvals,
187 WF_ITEM_ATTRIBUTES_VL wiav,
188 CUG_TSK_TYP_ATTR_DEPS_VL cttadv
189 WHERE cttamv.tsk_typ_attr_map_id = cvals.task_type_attr_map_id
190 AND cttamv.task_type_attribute_code = wiav.name
191 AND wiav.name = lv_attr_name
192 AND cvals.tsk_typ_attr_depend_id = cttadv.tsk_typ_attr_dep_id
193 AND cttadv.tsk_typ_attr_dep_id = lv_task_type_attr_dep_id
194 AND cttadv.task_type_id = cttamv.task_type_id
195 AND cttadv.task_type_id = lv_task_type_id
196 AND cttadv.incident_type_id = lv_incident_type_id
197 AND wiav.item_type = 'CUGTASKS';
198
199
200 IF (lv_task_type_attr_type = 'ROLE' ) THEN
201 wf_engine.SetItemAttrText(
202 ITEMTYPE => lv_itemtype ,
203 ITEMKEY => lv_itemkey,
204 ANAME =>lv_task_type_attr_name,
205 AVALUE => upper(lv_task_type_attr_value) );
206 ELSIF (lv_task_type_attr_type = 'NUMBER' ) THEN
207 wf_engine.SetItemAttrNumber(
208 ITEMTYPE => lv_itemtype ,
209 ITEMKEY => lv_itemkey,
210 ANAME =>lv_task_type_attr_name,
211 AVALUE => to_number(lv_task_type_attr_value ) );
212
213 ELSIF (lv_task_type_attr_type = 'DATE' ) THEN
214 wf_engine.SetItemAttrDate(
215 ITEMTYPE => lv_itemtype ,
216 ITEMKEY => lv_itemkey,
217 ANAME =>lv_task_type_attr_name,
218 AVALUE =>to_date(lv_task_type_attr_value,'DD-MM-YYYY') );
219
220 ELSE
221 wf_engine.SetItemAttrText(
222 ITEMTYPE => lv_itemtype ,
223 ITEMKEY => lv_itemkey,
224 ANAME =>lv_task_type_attr_name,
225 AVALUE => lv_task_type_attr_value );
226 END IF;
227
228 END LOOP;
229
230 CLOSE c_all_task_attrs;
231
232 EXCEPTION
233 WHEN NO_DATA_FOUND THEN
234 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR ;
235 END Get_Task_Attrs_Details;
236
237
238
239
240 -----------------------------------------------------------------------
241 -- Get_Incident_Addr_Details
242 --
243 -- Modification History:
244 --
245 -- Date Name Desc
246 -- ---------- --------- -------------------------------------------
247 -- 05/18/04 RMANABAT Fix for bug 3630834. Modified cursor
248 -- l_Incident_Addr_csr. Added cursor l_hz_party_site_csr.
249 -- Address is from hz_locations is location type is
250 -- HZ_LOCATION, if HZ_PARTY_SITE then location_id is
251 -- derived from hz_party_sites.
252 ----------------------------------------------------------------------
253
254 PROCEDURE Get_Incident_Addr_Details
255 (
256 ITEMTYPE IN VARCHAR2
257 , ITEMKEY IN VARCHAR2
258 , INCIDENT_NUMBER IN VARCHAR2
259 ,X_RETURN_STATUS OUT NOCOPY VARCHAR2
260 )
261 IS
262 -- Declaration of all the local variables
263
264 lv_itemtype VARCHAR2(8);
265 lv_itemkey VARCHAR2(240);
266 lv_incident_number VARCHAR2(64);
267 lv_incident_address VARCHAR2(2000);
268
269 -- Begin of changes by ANEEMUCH 13-May-2002
270 -- Changed because of GSCC error, to remove CHR(10) and replace with CHR(0)
271
272 /***
273 cursor l_Incident_Addr_csr is
274 SELECT hl.address , hl.city, hl.state, hl.province, hl.postal_code, hl.country
275 FROM cs_sr_incident_address_v hl,
276 CS_INCIDENTS_ALL_b cia
277 WHERE hl.location_id = cia.incident_location_id
278 AND cia.incident_number = lv_incident_number;
279 ***/
280 cursor l_Incident_Addr_csr(l_location_id IN NUMBER) is
281 SELECT l.address1 ||
282 DECODE(l.address2,NULL,NULL,'; '|| l.address2) ||
283 DECODE(l.address3,NULL,NULL,'; '|| l.address3) ||
284 DECODE(l.address4,NULL,NULL,'; '|| l.address4) address,
285 l.city,
286 l.state,
287 l.province,
288 l.postal_code,
289 l.country
290 FROM hz_locations l
291 WHERE l.location_id = l_location_id;
292 l_Incident_Addr_rec l_Incident_Addr_csr%rowtype;
293
294 cursor l_SR_Addr_csr is
295 SELECT incident_location_id, incident_address, incident_city, incident_state,
296 incident_province, incident_postal_code, incident_country,
297 incident_location_type
298 FROM CS_INCIDENTS_ALL_b
299 WHERE incident_number = lv_incident_number;
300 l_SR_Addr_rec l_SR_Addr_csr%rowtype;
301
302 CURSOR l_hz_party_site_csr(l_party_site_id IN NUMBER) IS
303 SELECT ps.location_id
304 FROM hz_party_sites ps
305 WHERE ps.party_site_id = l_party_site_id;
306 lv_location_id NUMBER;
307
308
309 --
310 -- End of changes added by ANEEMUCH 13-May-2002
314 l_city VARCHAR2(60);
311
312 l_address1 VARCHAR2(100);
313 l_address2 VARCHAR2(100);
315 l_state VARCHAR2(60);
316 l_province VARCHAR2(60);
317 l_postal_code VARCHAR2(30);
318 l_country VARCHAR2(60);
319 BEGIN
320
321 lv_itemtype := ITEMTYPE;
322 lv_itemkey := ITEMKEY;
323 lv_incident_number := INCIDENT_NUMBER;
324
325 -- Process to get the Incident Address information from HZ_LOCATIONS table for the given
326 -- Service Request Id. and store it in some local variable v_incident_address
327
328 -- Begin of changes by ANEEMUCH 12-May-2002
329 -- Changed because of GSCC error, to remove CHR(10) and replace with CHR(0)
330 /*
331 SELECT hl.address1 || CHR(10) || hl.address2 || CHR(10) || hl.city || CHR(10) ||
332 hl.state || CHR(10) || hl.province || CHR(10) || hl.postal_code || CHR(10) ||
333 hl.country
334 INTO lv_incident_address
335 FROM HZ_LOCATIONS hl,
336 CS_INCIDENTS_ALL_b cia
337 WHERE hl.location_id = cia.install_site_use_id
338 AND cia.incident_number = lv_incident_number;
339 */
340
341 -- Changes added by ANEEMUCH dated 13-May-2002
342 -- Incident Address capture done in SR Tab
343 /*
344 SELECT hl.address1, hl.address2, hl.city, hl.state, hl.province, hl.postal_code, hl.country
345 INTO l_address1, l_address2, l_city, l_state, l_province, l_postal_code, l_country
346 FROM HZ_LOCATIONS hl,
347 CS_INCIDENTS_ALL_b cia
348 WHERE hl.location_id = cia.install_site_use_id
349 AND cia.incident_number = lv_incident_number;
350
351 lv_incident_address := l_address1 || '
352 '|| l_address2 || '
353 '|| l_city || '
354 '|| l_state || '
355 '|| l_province || '
356 '|| l_postal_code || '
357 '|| l_country;
358 */
359
360 OPEN l_SR_Addr_csr;
361 FETCH l_SR_Addr_csr INTO l_SR_Addr_rec;
362 IF l_SR_Addr_rec.incident_location_id IS NOT NULL THEN
363
364 IF (l_SR_Addr_rec.incident_location_type = 'HZ_LOCATION') THEN
365
366 OPEN l_Incident_Addr_csr(l_SR_Addr_rec.incident_location_id);
367 FETCH l_Incident_Addr_csr INTO l_Incident_Addr_rec;
368 IF (l_Incident_Addr_csr%FOUND) THEN
369 lv_incident_address := l_Incident_Addr_rec.address || '
370 '|| l_Incident_Addr_rec.city || '
371 '|| l_Incident_Addr_rec.state || '
372 '|| l_Incident_Addr_rec.province || '
373 '|| l_Incident_Addr_rec.postal_code || '
374 '|| l_Incident_Addr_rec.country;
375 ELSE
376 lv_incident_address := '';
377 END IF;
378
379 IF l_Incident_Addr_csr%ISOPEN THEN
380 CLOSE l_Incident_Addr_csr;
381 END IF;
382
383 ELSIF (l_SR_Addr_rec.incident_location_type = 'HZ_PARTY_SITE') THEN
384
385 OPEN l_hz_party_site_csr(l_SR_Addr_rec.incident_location_id);
386 FETCH l_hz_party_site_csr INTO lv_location_id;
387
388 IF (l_hz_party_site_csr%FOUND AND lv_location_id IS NOT NULL) THEN
389 OPEN l_Incident_Addr_csr(lv_location_id);
390 FETCH l_Incident_Addr_csr INTO l_Incident_Addr_rec;
391 IF (l_Incident_Addr_csr%FOUND) THEN
392 lv_incident_address := l_Incident_Addr_rec.address || '
393 '|| l_Incident_Addr_rec.city || '
394 '|| l_Incident_Addr_rec.state || '
395 '|| l_Incident_Addr_rec.province || '
396 '|| l_Incident_Addr_rec.postal_code || '
397 '|| l_Incident_Addr_rec.country;
398 ELSE
399 lv_incident_address := '';
400 END IF;
401 IF l_Incident_Addr_csr%ISOPEN THEN
402 CLOSE l_Incident_Addr_csr;
403 END IF;
404
405 ELSE
406 lv_incident_address := '';
407 END IF;
408
409 IF l_hz_party_site_csr%ISOPEN THEN
410 CLOSE l_hz_party_site_csr;
411 END IF;
412
413 END IF; -- IF (l_SR_Addr_rec.incident_location_type = 'HZ_LOCATION')
414
415
416 ELSE
417 lv_incident_address := l_SR_Addr_rec.incident_address || '
418 '|| l_SR_Addr_rec.incident_city || '
419 '|| l_SR_Addr_rec.incident_state || '
420 '|| l_SR_Addr_rec.incident_province || '
421 '|| l_SR_Addr_rec.incident_postal_code || '
422 '|| l_SR_Addr_rec.incident_country;
423 END IF;
424
425 IF l_SR_Addr_csr%ISOPEN THEN
426 CLOSE l_SR_Addr_csr;
427 END IF;
428
429 -- End of changes by ANEEMUCH 12-May-2002
430
431 -- Set the value of the incident address in the Workflow Item attribute Incident Address
432 wf_engine.SetItemAttrText(
433 ITEMTYPE => lv_itemtype ,
434 ITEMKEY => lv_itemkey,
435 ANAME =>'CUG_INC_ADDR',
436 AVALUE => lv_incident_address );
437
438 EXCEPTION
439 WHEN NO_DATA_FOUND THEN
440 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR ;
441 End Get_Incident_Addr_Details;
442
443 PROCEDURE Get_SR_Attrs_Details (
444 ITEMTYPE IN VARCHAR2
445 , ITEMKEY IN VARCHAR2
446 , INCIDENT_ID IN NUMBER
447 , INCIDENT_TYPE_ID IN NUMBER
448 , INCIDENT_NUMBER IN VARCHAR2
449 , TASK_NUMBER IN VARCHAR2
450 , X_RETURN_STATUS OUT NOCOPY VARCHAR2
451 )
452 IS
453
454 -- Declaration of all the local variables
455 lv_itemtype VARCHAR2(8);
456 lv_itemkey VARCHAR2(240);
457 lv_attr_code VARCHAR2(30);
458 lv_attr_name VARCHAR2(240);
459 lv_attr_value VARCHAR2(1997):= NULL;
460 str_attr_det VARCHAR2(4000);
464 lv_incident_id NUMBER;
461 lv_sr_attrs_det VARCHAR2(4000);
462 lv_incident_number VARCHAR2(64);
463 lv_task_number VARCHAR2(30) ;
465 lv_incident_type_id NUMBER;
466 lv_wf_item_attr_nm VARCHAR2(30) := 'CUG_SR_ATTR_DETAILS1';
467 lv_item_attr_cnt NUMBER := 1;
468 lv_item_attr_max_cnt NUMBER := 5;
469 i NUMBER := 0;
470 set_Flag VARCHAR2(1):= 'N';
471 lv_incident_name VARCHAR2(30);
472
473 -- DECLARE a CURSOR to select all the attributes related to that Service Request
474 /* Roopa begin - to fix bug 2347352
475 This cursor has been changed to also get sr attributes that were
476 end-dated AFTER the SR was submitted */
477 l_incident_date DATE;
478
479 CURSOR c_all_sr_attrs_details IS
480 SELECT flv.lookup_code, flv.description
481 FROM FND_LOOKUPS flv,
482 CUG_SR_TYPE_ATTR_MAPS_VL cstamv
483 WHERE flv.lookup_code = cstamv.sr_attribute_code
484 AND cstamv.incident_type_id = lv_incident_type_id
485 AND lookup_type = 'CUG_SR_TYPE_ATTRIBUTES';
486 -- AND (cstamv.end_date_active is null or
487 -- cstamv.end_date_active >= l_incident_date) ;
488 /* Roopa end - to fix bug 2347352 */
489
490
491 /* Roopa begin - to fix bug 2347352 */
492
493
494 CURSOR c_get_incident_date IS
495 SELECT incident_date
496 FROM CS_INCIDENTS_ALL_B
497 WHERE INCIDENT_ID = lv_incident_id;
498
499 CURSOR c_get_sr_attr_value IS
500 SELECT sr_attribute_value
501 FROM CUG_INCIDNT_ATTR_VALS_VL
502 WHERE sr_attribute_code = lv_attr_code
503 AND incident_id = lv_incident_id ;
504
505
506 /* Roopa end - to fix bug 2347352 */
507
508 BEGIN
509
510 lv_itemtype := ITEMTYPE;
511 lv_itemkey := ITEMKEY;
512 lv_incident_id := INCIDENT_ID;
513 lv_incident_type_id := INCIDENT_TYPE_ID;
514 lv_incident_number := INCIDENT_NUMBER;
515 lv_task_number := TASK_NUMBER;
516
517 SELECT name
518 INTO lv_incident_name
519 FROM cs_incident_types
520 WHERE INCIDENT_TYPE_ID = lv_incident_type_id;
521
522
523
524 OPEN c_get_incident_date;
525 FETCH c_get_incident_date into l_incident_date;
526 CLOSE c_get_incident_date;
527
528
529 OPEN c_all_sr_attrs_details;
530 lv_sr_attrs_det := NULL;
531 lv_sr_attrs_det := 'Service Request #' ||' : ' || lv_incident_number || '
532 ';
533 lv_sr_attrs_det := lv_sr_attrs_det || 'Service Request Type'||' : ' ||lv_incident_name || '
534 ';
535 lv_sr_attrs_det := lv_sr_attrs_det || 'Task #' ||' : ' || lv_task_number || '
536 ';
537 LOOP
538 i := i + 1 ;
539 -- Fetch all the attributes code and name from the cursor.
540 FETCH c_all_sr_attrs_details INTO lv_attr_code, lv_attr_name;
541 EXIT WHEN c_all_sr_attrs_details%NOTFOUND;
542 lv_attr_value := NULL;
543
544 -- CHANGES REQUIRED IN THIS QUERY
545
546 /* Roopa begin - to fix bug 2347352 */
547 -- removed the following explicit select and using a cursor instead to fetch records
548 -- SELECT sr_attribute_value
549 -- INTO lv_attr_value
550 -- FROM CUG_INCIDNT_ATTR_VALS_VL
551 -- WHERE sr_attribute_code = lv_attr_code
552 -- AND incident_id = lv_incident_id ;
553
554 OPEN c_get_sr_attr_value;
555 FETCH c_get_sr_attr_value INTO lv_attr_value;
556 IF(c_get_sr_attr_value%NOTFOUND) THEN
557 null;
558 ELSE
559 /* Roopa end - to fix bug 2347352 */
560 str_attr_det := lv_attr_name ||' : ' || lv_attr_value || '
561 ';
562 IF ( length(str_attr_det) <= (4000-length(lv_sr_attrs_det))) THEN
563 lv_sr_attrs_det := lv_sr_attrs_det || str_attr_det;
564 set_Flag := 'N';
565 ELSE
566 wf_engine.SetItemAttrText (
567 ITEMTYPE => lv_itemtype ,
568 ITEMKEY => lv_itemkey,
569 ANAME => lv_wf_item_attr_nm,
570 AVALUE => lv_sr_attrs_det);
571
572 lv_item_attr_cnt := lv_item_attr_cnt + 1;
573 lv_wf_item_attr_nm := 'CUG_SR_ATTR_DETAILS'|| to_char(lv_item_attr_cnt);
574 IF ( lv_item_attr_cnt > lv_item_attr_max_cnt ) THEN
575 EXIT;
576 END IF;
577 lv_sr_attrs_det := str_attr_det ;
578 set_Flag := 'Y';
579 END IF;
580 /* Roopa begin - to fix bug 2347352 */
581 END IF;
582 CLOSE c_get_sr_attr_value;
583 /* Roopa end - to fix bug 2347352 */
584 END LOOP;
585 if (set_Flag = 'N' ) then
586 wf_engine.SetItemAttrText (
587 ITEMTYPE => lv_itemtype ,
588 ITEMKEY => lv_itemkey,
589 ANAME => lv_wf_item_attr_nm,
590 AVALUE => lv_sr_attrs_det);
591 end if;
592 CLOSE c_all_sr_attrs_details;
593 return;
594 EXCEPTION
595 WHEN NO_DATA_FOUND THEN
596 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR ;
597 return;
598 End Get_SR_Attrs_Details;
599
600 PROCEDURE Set_Reminder_Interval
601 ( ITEMTYPE IN VARCHAR2
602 ,ITEMKEY IN VARCHAR2
603 ,ACTID IN NUMBER
604 ,FUNCMODE IN VARCHAR2
605 ,RESULTOUT OUT NOCOPY VARCHAR2
606 )
607 IS
608 lv_itemtype VARCHAR2(8);
609 lv_itemkey VARCHAR2(240);
610 lv_task_number NUMBER;
611 lv_reminder_interval NUMBER;
612 lv_uom_type VARCHAR2(30);
613 lv_offset_value NUMBER;
614 lv_conversion_rate NUMBER;
615
616 begin
617
618 lv_itemtype := ITEMTYPE ;
619 lv_itemkey := ITEMKEY;
620
624 ITEMKEY => lv_itemkey,
621 IF ( funcmode = 'RUN' ) THEN
622 lv_reminder_interval := wf_engine.getItemAttrNumber (
623 ITEMTYPE => lv_itemtype,
625 ANAME => 'CUG_REMINDER_INTERVAL');
626
627 if ( lv_reminder_interval is NULL ) then
628
629 -- Get the UOM Type from the 'Email Notification' workflow
630 lv_uom_type := wf_engine.getItemAttrText (
631 ITEMTYPE => lv_itemtype ,
632 ITEMKEY => lv_itemkey,
633 ANAME => 'CUG_UOM_DUE_DATE');
634
635 -- Get the Offset Value for the calculation of the due Date from
636 -- 'Email Notification' workflow
637 lv_offset_value := wf_engine.getItemAttrText (
638 ITEMTYPE => lv_itemtype ,
639 ITEMKEY => lv_itemkey,
640 ANAME => 'CUG_OFFSET_DUE_DATE');
641
642 -- get the Conversion rate for the given UOM Type
643 select conversion_rate
644 into lv_conversion_rate
645 from mtl_uom_conversions
646 where uom_class='Time'
647 and inventory_item_id=0
648 and upper(unit_of_measure)=upper(lv_uom_type);
649
650 -- Calculate the Reminder Interval and set that Item Attribute
651 -- 'CUG_REMINDER_INTERVAL' in the Workflow.
652
653 lv_reminder_interval := (lv_conversion_rate * lv_offset_value * 60);
654 wf_engine.SetItemAttrNumber(
655 ITEMTYPE => lv_itemtype ,
656 ITEMKEY => lv_itemkey,
657 ANAME => 'CUG_REMINDER_INTERVAL',
658 AVALUE => lv_reminder_intervaL );
659
660 RESULTOUT := 'COMPLETE:';
661 return;
662 end if;
663 End if;
664 EXCEPTION
665 When NO_DATA_FOUND Then
666 wf_engine.SetItemAttrNumber(
667 ITEMTYPE => lv_itemtype ,
668 ITEMKEY => lv_itemkey,
669 ANAME => 'CUG_REMINDER_INTERVAL',
670 AVALUE => 0 );
671 RESULTOUT := 'COMPLETE:';
672 return;
673 When Others then
674 WF_CORE.context('CUG_WF_EMAIL_UTIL', 'SET_REMINDER_INTERVAL', itemtype, itemkey, actid, funcmode );
675 RAISE;
676
677 End Set_Reminder_Interval;
678
679 PROCEDURE Check_For_CIC_SR
680 ( ITEMTYPE IN VARCHAR2 -- Workflow Item Type := 'JTFTASKS'
681 ,ITEMKEY IN VARCHAR2 -- to_char(p_task_id)||'-'||to_char(l_wf_process_id)
682 ,ACTID IN NUMBER
683 ,FUNCMODE IN VARCHAR2
684 ,RESULTOUT OUT NOCOPY VARCHAR2
685 )IS
686 lv_itemtype VARCHAR2(8);
687 lv_itemkey VARCHAR2(240);
688 lv_task_number VARCHAR2(30);
689 lv_task_id NUMBER;
690 lv_task_type_id NUMBER;
691 lv_source_object_type_code VARCHAR2(30);
692 lv_task_attr_cnt NUMBER := 0;
693
694 Begin
695 lv_itemtype := itemtype;
696 lv_itemkey := itemkey;
697
698
699 IF ( funcmode = 'RUN' ) THEN
700 -- Get the Service Request Id by using the GetItemAttr function for 'TASK_ID' and then
701 -- execute a SQL query to find out the source_object_id from JTF_TASKS_VL view for the
702
703 lv_task_number := wf_engine.GetItemAttrText(
704 ITEMTYPE => lv_itemtype,
705 ITEMKEY => lv_itemkey,
706 ANAME => 'TASK_NUMBER' );
707
708 -- Make sure by putting specific check that it will execute only for EMAIL NOTIFICATION TASK TYPE
709 -- for a given Service Request
710 select jtv.task_id, jtv.task_type_id, jtv.source_object_type_code
711 into lv_task_id, lv_task_type_id, lv_source_object_type_code
712 from cs_incidents_all_b ciav,
713 jtf_tasks_b jtv
714 where ciav.incident_id = jtv.source_object_id
715 and jtv.task_number = lv_task_number;
716
717 if (lv_source_object_type_code = 'SR' ) then
718 select count(*)
719 into lv_task_attr_cnt
720 from cug_tsk_typ_attr_maps_b
721 where task_type_id=lv_task_type_id;
722
723 if (lv_task_attr_cnt > 0) then
724 RESULTOUT := 'COMPLETE:Y' ;
725 return ;
726 end if;
727 end if;
728 RESULTOUT := 'COMPLETE:N';
729 RETURN;
730 end if;
731
732 EXCEPTION
733 When NO_DATA_FOUND then
734 RESULTOUT := 'COMPLETE:N';
735 RETURN;
736 When OTHERS then
737 WF_CORE.context('CUG_WF_EMAIL_UTIL', 'Check_For_CIC_SR', itemtype, itemkey, actid, funcmode );
738 RAISE;
739 End Check_For_CIC_SR;
740
741
742
743 ------------------------------------------------------------
744 -- Procedure name : Set_Email_status
745 --
746 -- Parameters
747 -- IN
748 -- p_source_type: Source Type
749 -- ITEMTYPE : Workflow Item Type
750 -- ITEMKEY : Workflow Item Key
751 -- ACTID : Action ID
752 -- FUNCMODE : Function Mode
753 --
754 -- OUT
755 -- RESULTOUT : Return result
756 --
757 --
758 -- Description : This procedure checks the return action for the notification set
759 -- and updates the task with either success or failure.
760 --
761 -- Modification History :
762 -- Date Name Desc
763 -- ---------- --------- ----------------------------------
764 -- 04/13/2004 PSKANNAN Created.
765 -- 05/16/2005 ANEEMUCH Fixed bug 4323360. CUG Task Success and Failure profile stored
766 -- translatable values instead of ID's. Change in profile, to store
767 -- id, resulted change in this procedure.
768 -- ------------------------------------------------------------
769
770
771 PROCEDURE Set_Email_status
772 ( ITEMTYPE IN VARCHAR2 -- Workflow Item Type := 'JTFTASKS'
773 ,ITEMKEY IN VARCHAR2 -- to_char(p_task_id)||'-'||to_char(l_wf_process_id)
774 ,ACTID IN NUMBER
775 ,FUNCMODE IN VARCHAR2
776 ,RESULTOUT OUT NOCOPY VARCHAR2
777 )IS
778
779 lv_itemtype VARCHAR2(8);
780 lv_itemkey VARCHAR2(240);
781 lv_task_number VARCHAR2(30);
782 lv_activity_result_code VARCHAR2(30);
783 lv_result_code VARCHAR2(30);
784 lv_source_object_id NUMBER;
785 lv_task_id NUMBER;
786 lv_source_object_type_code VARCHAR2(60);
787 lv_task_status_id NUMBER;
788 lv_object_version_number NUMBER;
789 x_return_status VARCHAR2(240);
790 x_msg_count NUMBER;
791 x_msg_data VARCHAR2(240);
792 begin
793
794 lv_itemtype := ITEMTYPE;
795 lv_itemkey := ITEMKEY;
796
797 IF ( funcmode = 'RUN' ) THEN
798
799 -- Get the Service Request Id by using the GetItemAttr function for 'TASK_ID' and then
800 -- execute a SQL query to find out the source_object_id from JTF_TASKS_VL view for the
801 lv_task_number := wf_engine.GetItemAttrText(
802 ITEMTYPE => lv_itemtype,
803 ITEMKEY => lv_itemkey,
804 ANAME => 'TASK_NUMBER' );
805
806 -- Find out the SOURCE_OBJECT_ID, TASK_ID, SOURCE_OBJECT_TYPE_CODE
807 -- and OBJECT_VERSION_NUMBER for a given TASK_NUMBER
808 select jtb.source_object_id , jtb.task_id,
809 jtb.source_object_type_code, jtb.object_version_number
810 into lv_source_object_id , lv_task_id ,
811 lv_source_object_type_code, lv_object_version_number
812 from JTF_TASKS_B jtb
813 where jtb.TASK_NUMBER= lv_task_number;
814
815 begin
816 select ACTIVITY_RESULT_CODE
817 into lv_activity_result_code
818 from wf_item_activity_statuses
819 where item_type = lv_itemtype
820 and item_key = lv_itemkey
821 and (ACTIVITY_RESULT_CODE = 'CUG_ACKNOWLEDGED' or
822 ACTIVITY_RESULT_CODE = 'N' );
823
824 lv_result_code := 'CUG_COMPLETED';
825 exception
826 When NO_DATA_FOUND then
827 lv_result_code := 'CUG_CANCELLED';
828 end;
829
830 begin
831 if (lv_result_code = 'CUG_COMPLETED') then
832 -- set value for Successful completition.
833 lv_task_status_id := fnd_profile.value('CUG_TASK_SUCCESS_STATUS');
834
835 else
836 -- Set value for Failure Competition.
837 lv_task_status_id := fnd_profile.value('CUG_TASK_FAILED_STATUS');
838
839 end if;
840
841 if (lv_task_status_id is null ) then
842 WF_CORE.context('CUG_WF_EMAIL_UTIL', 'Set_Email_Status:task_status_id', itemtype, itemkey, actid, funcmode );
843 end if;
844
845 exception
846 When NO_DATA_FOUND then
847 WF_CORE.context('CUG_WF_EMAIL_UTIL', 'Set_Email_Status:profile_not_set', itemtype, itemkey, actid, funcmode );
848 RAISE;
849 end;
850
851 x_return_status := fnd_api.g_ret_sts_success ;
852
853 -- Update the status in the
854
855 jtf_tasks_pub.update_task (
856 p_api_version => 1.0,
857 p_init_msg_list => fnd_api.g_false ,
858 p_commit => fnd_api.g_true,
859 p_object_version_number => lv_object_version_number ,
860 p_task_id => lv_task_id ,
861 p_task_status_id => lv_task_status_id ,
862 x_return_status => x_return_status ,
863 x_msg_count => x_msg_count ,
864 x_msg_data => x_msg_data );
865
866 IF NOT (x_return_status <> fnd_api.g_ret_sts_success ) THEN
867 WF_CORE.context('CUG_WF_EMAIL_UTIL', 'Set_Email_Status:update_task', itemtype, itemkey, actid, funcmode );
868 END IF;
869
870 RESULTOUT := 'COMPLETE:';
871 return;
872
873 end if;
874 exception
875 When NO_DATA_FOUND then
876 RESULTOUT := 'COMPLETE:';
877 RETURN;
878 When OTHERS then
879 WF_CORE.context('CUG_WF_EMAIL_UTIL', 'Set_Email_Status', itemtype, itemkey, actid, funcmode );
880 RAISE;
881 end Set_Email_Status;
882
883 END;