[Home] [Help]
PACKAGE BODY: APPS.CAC_VIEW_WF_PVT
Source
1 PACKAGE BODY CAC_VIEW_WF_PVT AS
2 /* $Header: cacvcwsb.pls 120.6 2012/01/11 06:51:15 anangupt ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'CAC_VIEW_WF_PVT';
5
6 PROCEDURE wf_role
7 ( p_resource_id IN NUMBER
8 , p_resource_type IN VARCHAR2
9 , x_role OUT NOCOPY VARCHAR2
10 , x_name OUT NOCOPY VARCHAR2
11 , x_emp_number OUT NOCOPY NUMBER
12 )
13 IS
14
15 l_employee_number NUMBER;
16 l_name varchar2(320);
17 l_email_address hz_contact_points.EMAIL_ADDRESS%type;
18
19 CURSOR c_resource
20 ( b_resourceID NUMBER
21 )IS SELECT source_id EmployeeNumber
22 FROM JTF_RS_RESOURCE_EXTNS
23 WHERE resource_id = b_ResourceID
24 ;
25
26 CURSOR c_contact_detail (b_resource_id NUMBER ) IS
27 select pers.PARTY_name , hc.email_ADDRESS
28 from hz_parties pers,
29 hz_relationships hr,
30 hz_contact_points hc
31 where hc.owner_table_id = b_resource_id
32 and hr.party_id = hc.owner_table_id
33 and hr.subject_id = pers.party_id
34 and hr.subject_type = 'PERSON'
35 and hc.primary_flag = 'Y'
36 and hc.contact_point_type = 'EMAIL'
37 AND hc.status ='A'
38 and hc.owner_table_name = 'HZ_PARTIES';
39
40 BEGIN
41 ---------------------------------------------------------------------
42 -- Get the employee number
43 ---------------------------------------------------------------------
44 IF (c_Resource%ISOPEN)
45 THEN
46 CLOSE c_Resource;
47 END IF;
48
49 OPEN c_Resource( p_resource_id
50 );
51 FETCH c_Resource INTO l_employee_number;
52 IF (c_Resource%NOTFOUND)
53 THEN
54 /* x_role := NULL;
55 x_name := NULL;*/
56 --get employee number so adhoc user can be associated with the resource
57 x_emp_number := NULL;
58 BEGIN
59 --HZ_PARTIES should be populated in the workflow directory
60 WF_DIRECTORY.GetRoleName( p_orig_system => 'HZ_PARTY'
61 , p_orig_system_id => p_resource_id
62 , p_name => x_role
63 , p_display_name => x_name
64 );
65 EXCEPTION
66 WHEN OTHERS THEN
67 -- if we know name and email_address only we can create an adhoc user
68 IF (c_contact_detail%ISOPEN)
69 THEN
70 CLOSE c_contact_detail;
71 END IF;
72
73 OPEN c_contact_detail ( p_resource_id ) ;
74 FETCH c_contact_detail INTO l_name , l_email_address ;
75 CLOSE c_contact_detail ;
76
77 WF_DIRECTORY.CreateAdHocRole (role_name => l_name
78 ,role_display_name => l_email_address
79 , email_address => l_email_address
80 );
81 END;
82 ELSE
83 -------------------------------------------------------------------------
84 -- Call Workflow API to get the role
85 -- If there is more than one role for this employee, the API will
86 -- return the first one fetched. If no Workflow role exists for
87 -- the employee, out variable will be NULL
88 -------------------------------------------------------------------------
89 WF_DIRECTORY.GetRoleName( p_orig_system => 'PER'
90 , p_orig_system_id => l_employee_number
91 , p_name => x_role
92 , p_display_name => x_name
93 );
94 x_emp_number := l_employee_number;
95 END IF;
96
97 IF (c_Resource%ISOPEN)
98 THEN
99 CLOSE c_Resource;
100 END IF;
101 END wf_role;
102
103 FUNCTION get_type_name (p_task_type_id IN NUMBER)
104 RETURN VARCHAR2
105 AS
106 l_type_name VARCHAR2(30);
107 BEGIN
108 IF p_task_type_id IS NULL
109 THEN
110 RETURN NULL;
111 ELSE
112 SELECT name
113 INTO l_type_name
114 FROM jtf_task_types_tl
115 WHERE task_type_id = p_task_type_id
116 AND language = USERENV('LANG');
117 END IF;
118
119 RETURN l_type_name;
120 EXCEPTION
121 WHEN NO_DATA_FOUND
122 THEN
123 RETURN NULL;
124 WHEN OTHERS
125 THEN
126 RETURN NULL;
127 END;
128
129 FUNCTION get_priority_name (p_task_priority_id IN NUMBER)
130 RETURN VARCHAR2
131 AS
132 l_priority_name VARCHAR2(30);
133 BEGIN
134 IF p_task_priority_id IS NULL
135 THEN
136 RETURN NULL;
137 ELSE
138 SELECT name
139 INTO l_priority_name
140 FROM jtf_task_priorities_tl
141 WHERE task_priority_id = p_task_priority_id
142 AND language = USERENV('LANG');
143 END IF;
144
145 RETURN l_priority_name;
146 EXCEPTION
147 WHEN NO_DATA_FOUND
148 THEN
149 RETURN NULL;
150 WHEN OTHERS
151 THEN
152 RETURN NULL;
153 END;
154 FUNCTION get_duration (p_minutes IN NUMBER)
155 RETURN VARCHAR2
156 AS
157 l_duration VARCHAR2(30);
158 BEGIN
159 IF p_minutes IS NULL
160 THEN
161 RETURN NULL;
162 ELSE
163 SELECT lk.meaning
164 INTO l_duration
165 FROM fnd_lookups lk
166 WHERE lk.lookup_type = 'JTF_CALND_DURATION'
167 AND lk.lookup_code = p_minutes;
168 END IF;
169
170 RETURN l_duration;
171 EXCEPTION
172 WHEN NO_DATA_FOUND
173 THEN
174 RETURN NULL;
175 WHEN OTHERS
176 THEN
177 RETURN NULL;
178 END;
179
180 FUNCTION GetTimezone
181 /*******************************************************************************
182 ** Start of comments
183 ** FUNCTION : GetTimezon
184 ** Description : This function will return the name of the timezone when given an ID
185 ** Parameters :
186 ** name direction type required?
187 ** ---- --------- ---- ---------
188 ** p_DayCode IN VARCHAR2
189 **
190 ** End of comments
191 ******************************************************************************/
192 (p_timezone_id IN NUMBER
193 )RETURN VARCHAR2
194 IS
195 CURSOR c_timezone
196 (b_timezone_id IN NUMBER
197 )IS SELECT htv.name
198 FROM hz_timezones_vl htv
199 WHERE htv.timezone_id = b_timezone_id;
200
201 l_name VARCHAR2(80):= 'Unknown timezone_id';
202
203 BEGIN
204 IF (c_timezone%ISOPEN)
205 THEN
206 CLOSE c_timezone;
207 END IF;
208
209 OPEN c_timezone(p_timezone_id);
210
211 FETCH c_timezone INTO l_name;
212
213 CLOSE c_timezone;
214
215 RETURN l_name;
216
217 END GetTimezone;
218
219
220 PROCEDURE StartSubscription
221 /*******************************************************************************
222 ** Start of comments
223 ** Procedure : StartSubscription
224 ** Description : Given the
225 ** Parameters :
226 ** name direction type required?
227 ** ---- --------- ---- ---------
228 ** p_api_version IN NUMBER required
229 ** p_init_msg_list IN VARCHAR2 optional
230 ** p_commit IN VARCHAR2 optional
231 ** x_return_status OUT VARCHAR2 optional
232 ** x_msg_count OUT NUMBER required
233 ** x_msg_data OUT VARCHAR2 required
234 ** p_REQUESTOR IN NUMBER required
235 ** p_GROUP_ID IN NUMBER optional
236 ** Notes :
237 ** 1)
238 **
239 ** End of comments
240 *******************************************************************************/
241 ( p_api_version IN NUMBER
242 , p_init_msg_list IN VARCHAR2
243 , p_commit IN VARCHAR2
244 , x_return_status OUT NOCOPY VARCHAR2
245 , x_msg_count OUT NOCOPY NUMBER
246 , x_msg_data OUT NOCOPY VARCHAR2
247 , p_CALENDAR_REQUESTOR IN NUMBER -- Resource ID of the Subscriber
248 , p_GROUP_ID IN NUMBER -- Resource ID of Group Calendar
249 , p_GROUP_NAME IN VARCHAR2 -- Name of the Group Calendar
250 , p_GROUP_DESCRIPTION IN VARCHAR2 -- Description of the Group Calendar
251 )
252 IS
253 CURSOR c_Admins
254 /****************************************************************************
255 ** Pick up all the Admins for the given group ID
256 ****************************************************************************/
257 (b_group_id NUMBER)
258 IS SELECT DISTINCT to_number(fgs.grantee_key) ResourceID
259 FROM fnd_grants fgs
260 , fnd_menus fmu
261 , fnd_objects fos
262 WHERE fgs.object_id = fos.object_id -- grants joint to object
263 AND fgs.menu_id = fmu.menu_id -- grants joint to menus
264 AND fos.obj_name = 'JTF_TASK_RESOURCE'
265 AND fmu.menu_name = 'JTF_CAL_ADMIN_ACCESS'
266 AND fgs.instance_pk1_value = to_char(b_group_id)
267 AND fgs.instance_pk2_value = ('RS_GROUP')
268 AND fgs.start_date < SYSDATE
269 AND ( fgs.end_date >= SYSDATE
270 OR fgs.end_date IS NULL
271 )
272 ;
273
274
275 l_api_name CONSTANT VARCHAR2(30) := 'StartSubscription';
276 l_api_version CONSTANT NUMBER := 1.0;
277 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
278
279 l_item_type VARCHAR2(8) := 'CACVWSWF';
280 l_item_key VARCHAR2(30);
281
282 l_requestor_role VARCHAR2(30);
283 l_requestor_name VARCHAR2(80);
284 l_requestor_emp_number NUMBER;
285
286 l_admin_role VARCHAR2(30);
287 l_cal_admin_role VARCHAR2(30);
288 l_admin_name VARCHAR2(80);
289 l_adhoc_role VARCHAR2(2000);
290 l_admin_emp_number NUMBER;
291 no_user EXCEPTION;
292 PRAGMA EXCEPTION_INIT (no_user, -20002);
293
294
295 BEGIN
296 /*****************************************************************************
297 ** Standard call to check for call compatibility
298 *****************************************************************************/
299 IF NOT FND_API.Compatible_API_Call( l_api_version
300 , p_api_version
301 , l_api_name
302 , G_PKG_NAME
303 )
304 THEN
305 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
306 END IF;
307
308 /*****************************************************************************
309 ** Initialize message list if p_init_msg_list is set to TRUE
310 *****************************************************************************/
311 IF FND_API.To_Boolean(p_init_msg_list)
312 THEN
313 FND_MSG_PUB.Initialize;
314 END IF;
315
316 /*****************************************************************************
317 ** Initialize API return status to success
318 *****************************************************************************/
319 x_return_status := FND_API.G_RET_STS_SUCCESS;
320
321 /***************************************************************************
322 ** we need an itemkey
323 ***************************************************************************/
324
325 SELECT to_char(cac_vws_itemkey_s.NEXTVAL) INTO l_item_key
326 FROM DUAL;
327
328 /*****************************************************************************
329 ** Look up the Calendar Administrator from the Profile (this should be an
330 ** existing WF_ROLE)
331 *****************************************************************************/
332 l_cal_admin_role := FND_PROFILE.Value(name => 'JTF_CALENDAR_ADMINISTRATOR');
333
334 l_adhoc_role := fnd_message.get_string('JTF', 'JTF_CAL_WF_GROUP_ADMIN')
335 || ' ' || l_item_key;
336
337 /***************************************************************************
338 ** Initialize the workflow
339 ***************************************************************************/
340 wf_engine.CreateProcess( itemtype => l_item_type
341 , itemkey => l_item_key
342 , process => 'REQUEST_SUBSCRIBTION'
343 );
344
345 /***************************************************************************
346 ** Init Group info
347 ***************************************************************************/
348 wf_engine.SetItemAttrNumber( itemtype => l_item_type
349 , itemkey => l_item_key
350 , aname => 'GROUP_ID'
351 , avalue => p_GROUP_ID
352 );
353
354 wf_engine.SetItemAttrText( itemtype => l_item_type
355 , itemkey => l_item_key
356 , aname => 'CALENDAR_ADMIN'
357 , avalue => l_cal_admin_role
358 );
359
360 wf_engine.SetItemAttrText( itemtype => l_item_type
361 , itemkey => l_item_key
362 , aname => 'GROUP_NAME'
363 , avalue => p_GROUP_NAME
364 );
365
366
367 wf_engine.SetItemAttrText( itemtype => l_item_type
368 , itemkey => l_item_key
369 , aname => 'GROUP_DESCRIPTION'
370 , avalue => p_GROUP_DESCRIPTION
371 );
372
373 /***************************************************************************
374 ** Init requestor info
375 ***************************************************************************/
376 WF_Role( p_CALENDAR_REQUESTOR
377 , 'RS_EMPLOYEE' -- Not used, for future enhancements
378 , l_requestor_role
379 , l_requestor_name
380 , l_requestor_emp_number
381 );
382
383 wf_engine.SetItemAttrNumber( itemtype => l_item_type
384 , itemkey => l_item_key
385 , aname => 'CALENDAR_REQUESTOR'
386 , avalue => p_CALENDAR_REQUESTOR
387 );
388
389 wf_engine.SetItemAttrText( itemtype => l_item_type
390 , itemkey => l_item_key
391 , aname => 'CALENDAR_REQUESTOR_NAME'
392 , avalue => l_requestor_name
393 );
394
395 wf_engine.SetItemAttrText( itemtype => l_item_type
396 , itemkey => l_item_key
397 , aname => 'CALENDAR_REQUESTOR_ROLE'
398 , avalue => l_requestor_role
399 );
400 /*****************************************************************************
401 ** Create adHoc role (ER 2198911)
402 *****************************************************************************/
403 wf_directory.CreateAdHocRole
404 ( role_name => l_adhoc_role,
405 role_display_name => l_adhoc_role
406 );
407 /*****************************************************************************
408 ** Find all the admins and start a notification WF for all of them.
409 *****************************************************************************/
410 FOR r_Admin IN c_Admins(p_Group_ID)
411 LOOP <<ADMINS>>
412
413
414
415 /***************************************************************************
416 ** Init admin info
417 ***************************************************************************/
418 wf_Role( r_Admin.ResourceID
419 , 'RS_EMPLOYEE'
420 , l_admin_role
421 , l_admin_name
422 , l_admin_emp_number
423 );
424
425 wf_engine.SetItemAttrNumber( itemtype => l_item_type
426 , itemkey => l_item_key
427 , aname => 'GROUP_CALENDAR_ADMIN'
428 , avalue => r_Admin.ResourceID
429 );
430 --comment CreateUserRole because it's not available in all versions of workflow
431 --BEGIN
432 wf_directory.addUsersToAdHocRole (role_name => l_adhoc_role,
433 role_users => l_admin_role);
434 /*EXCEPTION
435 --in older versions of WF, user has to be adHoc user (in wf_local_users)
436 --to be part of adHoc role; to skip the validation, call
437 --CreateUserRole with validateUserRole = FALSE
438 WHEN no_user THEN
439 wf_directory.CreateUserRole( user_name => l_admin_Role,
440 role_name => l_adhoc_role,
441 user_orig_system => 'PER',
442 user_orig_system_id => l_admin_emp_number,
443 role_orig_system => 'WF_LOCAL_ROLES',
444 role_orig_system_id => 0,
445 validateUserRole => FALSE);
446 END;*/
447 END LOOP ADMINS;
448 wf_engine.SetItemAttrText( itemtype => l_item_type
449 , itemkey => l_item_key
450 , aname => 'GROUP_CALENDAR_ADMIN_ROLE'
451 , avalue => l_adhoc_role
452 );
453
454 wf_engine.SetItemAttrText( itemtype => l_item_type
455 , itemkey => l_item_key
456 , aname => 'GROUP_CALENDAR_ADMIN_NAME'
457 , avalue => l_adhoc_role
458 );
459
460 /***************************************************************************
461 ** Start the workflow
462 ***************************************************************************/
463 wf_engine.StartProcess( itemtype => l_item_type
464 , itemkey => l_item_key
465 );
466
467
468 /***************************************************************************
469 ** Standard check of p_commit (WF won't start until commited)
470 ***************************************************************************/
471 IF FND_API.To_Boolean(p_commit)
472 THEN
473 COMMIT WORK;
474 END IF;
475
476
477 /*****************************************************************************
478 ** Standard call to get message count and if count is > 1, get message info
479 *****************************************************************************/
480 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
481 , p_data => x_msg_data
482 );
483 EXCEPTION
484 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
485 THEN
486 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
487 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
488 , p_data => x_msg_data);
489 WHEN OTHERS
490 THEN
491 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
492 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
493 THEN
494 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
495 , l_api_name
496 );
497 END IF;
498 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
499 , p_data => x_msg_data
500 );
501 END StartSubscription;
502
503 PROCEDURE ProcessInvitation
504 /*******************************************************************************
505 ** Start of comments
506 ** Procedure : ProcessInvitation
507 ** Description : Given the
508 ** Parameters :
509 ** name direction type required?
510 ** ---- --------- ---- ---------
511 ** p_api_version IN NUMBER required
512 ** p_init_msg_list IN VARCHAR2 optional
513 ** p_commit IN VARCHAR2 optional
514 ** x_return_status OUT VARCHAR2 optional
515 ** x_msg_count OUT NUMBER required
516 ** x_msg_data OUT VARCHAR2 required
517 ** p_task_assignment_id IN NUMBER required
518 ** p_resource_type IN VARCHAR2 required
519 ** p_resource_id IN NUMBER required
520 ** p_assignment_status_id IN NUMBER required
521 ** Notes :
522 ** 1) Created for ER 2219647
523 **
524 ** End of comments
525 *******************************************************************************/
526 ( p_api_version IN NUMBER
527 , p_init_msg_list IN VARCHAR2
528 , p_commit IN VARCHAR2
529 , x_return_status OUT NOCOPY VARCHAR2
530 , x_msg_count OUT NOCOPY NUMBER
531 , x_msg_data OUT NOCOPY VARCHAR2
532 , p_task_assignment_id IN NUMBER
533 , p_resource_type IN VARCHAR2
534 , p_resource_id IN NUMBER
535 , p_assignment_status_id IN NUMBER
536 )
537 IS
538 CURSOR c_invitor
539 /****************************************************************************
540 ** Pick up appointment owner
541 ****************************************************************************/
542 (p_task_assignment_id NUMBER)
543 IS
544 SELECT task_id, owner_id, task_name, description, timezone_id, task_type_id,
545 task_priority_id, calendar_start_date startDate,
546 (calendar_end_date - calendar_start_date)*24*60 duration
547 FROM jtf_tasks_vl
548 WHERE task_id IN
549 (SELECT task_id
550 FROM jtf_task_all_assignments
551 WHERE task_id IN (SELECT task_id
552 FROM jtf_task_all_assignments
553 WHERE task_assignment_id = p_task_assignment_id)
554 AND assignee_role = 'OWNER')
555 ;
556
557
558 l_api_name CONSTANT VARCHAR2(30) := 'ProcessInvitation';
559 l_api_version CONSTANT NUMBER := 1.0;
560 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
561
562 l_item_type VARCHAR2(8) := 'CACVWSWF';
563 l_item_key VARCHAR2(30);
564 l_cal_admin_role VARCHAR2(30);
565 l_invitor c_invitor%ROWTYPE;
566 l_task_type_name VARCHAR2(30);
567 l_task_priority_name VARCHAR2(30);
568 l_timezone VARCHAR2(80);
569
570
571 BEGIN
572 /*****************************************************************************
573 ** Standard call to check for call compatibility
574 *****************************************************************************/
575 IF NOT FND_API.Compatible_API_Call( l_api_version
576 , p_api_version
577 , l_api_name
578 , G_PKG_NAME
579 )
580 THEN
581 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
582 END IF;
583 /*****************************************************************************
584 ** Initialize message list if p_init_msg_list is set to TRUE
585 *****************************************************************************/
586 IF FND_API.To_Boolean(p_init_msg_list)
587 THEN
588 FND_MSG_PUB.Initialize;
589 END IF;
590
591 /*****************************************************************************
592 ** Initialize API return status to success
593 *****************************************************************************/
594 x_return_status := FND_API.G_RET_STS_SUCCESS;
595
596 /***************************************************************************
597 ** we need an itemkey
598 ***************************************************************************/
599
600 SELECT to_char(cac_vws_itemkey_s.NEXTVAL) INTO l_item_key
601 FROM DUAL;
602
603 IF (p_assignment_status_id <> 3 AND p_assignment_status_id <>4) THEN
604 fnd_message.set_name ('JTF', 'JTF_CAL_INVALID_ASSIGNMENT');
605 fnd_message.set_token ('ASSIGNMENT_STATUS_ID', p_assignment_status_id);
606 fnd_msg_pub.add;
607 x_return_status := fnd_api.g_ret_sts_unexp_error;
608 RAISE fnd_api.g_exc_unexpected_error;
609 END IF;
610
611 OPEN c_invitor (p_task_assignment_id);
612 FETCH c_invitor INTO l_invitor;
613 IF c_invitor%NOTFOUND THEN
614 CLOSE c_invitor;
615 RAISE fnd_api.g_exc_unexpected_error;
616 END IF;
617 CLOSE c_invitor;
618
619
620 /*****************************************************************************
621 ** Look up the Calendar Administrator from the Profile (this should be an
622 ** existing WF_ROLE)
623 *****************************************************************************/
624 l_cal_admin_role := FND_PROFILE.Value(name => 'JTF_CALENDAR_ADMINISTRATOR');
625 l_task_type_name := get_type_name(l_invitor.task_type_id);
626 l_task_priority_name := get_priority_name(l_invitor.task_priority_id);
627 l_timezone := GetTimezone(p_timezone_id => l_invitor.timezone_id);
628
629 /***************************************************************************
630 ** Initialize the workflow
631 ***************************************************************************/
632 wf_engine.CreateProcess( itemtype => l_item_type
633 , itemkey => l_item_key
634 , process => 'PROCESS_INVITATION'
635 );
636 wf_engine.SetItemAttrNumber
637 ( itemtype => l_item_type
638 , itemkey => l_item_key
639 , aname => 'TASK_ID'
640 , avalue => l_invitor.task_id
641 );
642
643 wf_engine.SetItemAttrNumber
644 ( itemtype => l_item_type
645 , itemkey => l_item_key
646 , aname => 'ASSIGNMENT_STATUS_ID'
647 , avalue => p_assignment_status_id
648 );
649 wf_engine.SetItemAttrText
650 ( itemtype => l_item_type
651 , itemkey => l_item_key
652 , aname => 'TASK_DESCRIPTION'
653 , avalue => l_invitor.description
654 );
655 wf_engine.SetItemAttrText
656 ( itemtype => l_item_type
657 , itemkey => l_item_key
658 , aname => 'TASK_NAME'
659 , avalue => l_invitor.task_name
660 );
661
662 wf_engine.SetItemAttrDate
663 ( itemtype => l_item_type
664 , itemkey => l_item_key
665 , aname => 'START_DATE'
666 , avalue => l_invitor.startDate
667 );
668 wf_engine.SetItemAttrText
669 ( itemtype => l_item_type
670 , itemkey => l_item_key
671 , aname => 'DURATION'
672 , avalue => get_duration(l_invitor.duration)
673 );
674 wf_engine.SetItemAttrNumber( itemtype => l_item_type
675 , itemkey => l_item_key
676 , aname => 'INVITEE'
677 , avalue => p_resource_id
678 );
679
680 wf_engine.SetItemAttrText( itemtype => l_item_type
681 , itemkey => l_item_key
682 , aname => 'CALENDAR_ADMIN'
683 , avalue => l_cal_admin_role
684 );
685
686 wf_engine.SetItemAttrText( itemtype => l_item_type
687 , itemkey => l_item_key
688 , aname => 'INVITOR'
689 , avalue => l_invitor.owner_id
690 );
691 wf_engine.SetItemAttrText( itemtype => l_item_type
692 , itemkey => l_item_key
693 , aname => 'TIMEZONE'
694 , avalue => l_timezone
695 );
696 wf_engine.SetItemAttrText( itemtype => l_item_type
697 , itemkey => l_item_key
698 , aname => 'TYPE'
699 , avalue => l_task_type_name
700 );
701 wf_engine.SetItemAttrText( itemtype => l_item_type
702 , itemkey => l_item_key
703 , aname => 'PRIORITY'
704 , avalue => l_task_priority_name
705 );
706 /***************************************************************************
707 ** Start the workflow
708 ***************************************************************************/
709 wf_engine.StartProcess( itemtype => l_item_type
710 , itemkey => l_item_key
711 );
712 /***************************************************************************
713 ** Standard check of p_commit (WF won't start until commited)
714 ***************************************************************************/
715 IF FND_API.To_Boolean(p_commit)
716 THEN
717 COMMIT WORK;
718 END IF;
719
720
721 /*****************************************************************************
722 ** Standard call to get message count and if count is > 1, get message info
723 *****************************************************************************/
724 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
725 , p_data => x_msg_data
726 );
727 EXCEPTION
728 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
729 THEN
730 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
731 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
732 , p_data => x_msg_data);
733 WHEN OTHERS
734 THEN
735 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
736 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
737 THEN
738 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
739 , l_api_name
740 );
741 END IF;
742 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
743 , p_data => x_msg_data
744 );
745 END ProcessInvitation;
746
747
748 PROCEDURE ProcessSubscription
749 /*******************************************************************************
750 ** Start of comments
751 ** Procedure : ProcessSubscription
752 ** Description :
753 ** Parameters :
754 ** name direction type required?
755 ** ---- --------- ---- ---------
756 ** itemtype IN VARCHAR2 required
757 ** itemkey IN VARCHAR2 required
758 ** actid IN NUMBER required
759 ** funcmode IN VARCHAR2 required
760 ** resultout OUT VARCHAR2 required
761 **
762 ** Notes :
763 **
764 ** End of comments
765 ******************************************************************************/
766 ( itemtype IN VARCHAR2
767 , itemkey IN VARCHAR2
768 , actid IN NUMBER
769 , funcmode IN VARCHAR2
770 , resultout OUT NOCOPY VARCHAR2
771 )
772 IS
773 l_RequestorID NUMBER;
774 l_RequestorWFRole VARCHAR2(30);
775 l_RequestorWFName VARCHAR2(80);
776
777 l_GroupID NUMBER;
778 l_GroupName VARCHAR2(60);
779 l_GroupNumber VARCHAR2(30);
780 l_GroupDescription VARCHAR2(240);
781 l_Response VARCHAR2(80);
782
783 l_return_status VARCHAR2(1);
784 l_msg_count NUMBER;
785 l_msg_data VARCHAR2(2000);
786 l_grant_guid RAW(16);
787 l_return BOOLEAN;
788
789 BEGIN
790 /*****************************************************************************
791 ** Initialize API return status to success
792 *****************************************************************************/
793 l_return_status := FND_API.G_RET_STS_SUCCESS;
794
795 /*****************************************************************************
796 ** Get the WF attribute values
797 *****************************************************************************/
798 l_RequestorID := wf_engine.GetItemAttrNumber
799 ( itemtype => itemtype
800 , itemkey => itemkey
801 , aname => 'CALENDAR_REQUESTOR'
802 );
803
804 l_GroupID := wf_engine.GetItemAttrNumber
805 ( itemtype => itemtype
806 , itemkey => itemkey
807 , aname => 'GROUP_ID'
808 );
809
810 l_GroupName := wf_engine.GetItemAttrText
811 ( itemtype => itemtype
812 , itemkey => itemkey
813 , aname => 'GROUP_NAME'
814 );
815
816 l_GroupDescription := wf_engine.GetItemAttrText
817 ( itemtype => itemtype
818 , itemkey => itemkey
819 , aname => 'GROUP_DESCRIPTION'
820 );
821
822 l_Response := wf_engine.GetItemAttrText
823 ( itemtype => itemtype
824 , itemkey => itemkey
825 , aname => 'ACCESS_LEVEL');
826
827 /** Check whether the requestor already has an access level to the group
828 ** Added by Jane **/
829
830 l_return := JTF_CAL_GRANTS_PVT.has_access_level(p_resourceid => to_char(l_RequestorID)
831 ,p_groupid => to_char(l_GroupID));
832
833 IF (l_return = true) THEN resultout := 'COMPLETE:NO_ERROR';
834 ELSE
835 BEGIN
836
837 /*****************************************************************************
838 ** Grant privs to the requestor
839 *****************************************************************************/
840 fnd_grants_pkg.grant_function( p_api_version => 1.0
841 , p_menu_name => l_Response
842 , p_instance_type => 'INSTANCE'
843 , p_object_name => 'JTF_TASK_RESOURCE'
844 , p_instance_pk1_value => to_char(l_GroupID)
845 , p_instance_pk2_value => 'RS_GROUP'
846 , p_grantee_type => 'USER'
847 , p_grantee_key => to_char(l_RequestorID)
848 , p_start_date => SYSDATE
849 , p_end_date => NULL
850 , p_program_name => 'CALENDAR'
851 , p_program_tag => 'ACCESS LEVEL'
852 , x_grant_guid => l_grant_guid
853 , x_success => l_return_status
854 , x_errorcode => l_msg_data
855 );
856
857 /*****************************************************************************
858 ** If the Access Level is ADMIN, grant the requstor READ Access as well
859 *****************************************************************************/
860 IF (l_Response = 'CAC_VWS_ADMIN_ACCESS')
861 THEN
862 fnd_grants_pkg.grant_function( p_api_version => 1.0
863 , p_menu_name => 'CAC_VWS_READ_ACCESS'
864 , p_instance_type => 'INSTANCE'
865 , p_object_name => 'JTF_TASK_RESOURCE'
866 , p_instance_pk1_value => to_char(l_GroupID)
867 , p_instance_pk2_value => 'RS_GROUP'
868 , p_grantee_type => 'USER'
869 , p_grantee_key => to_char(l_RequestorID)
870 , p_start_date => SYSDATE
871 , p_end_date => NULL
872 , p_program_name => 'CALENDAR'
873 , p_program_tag => 'ACCESS LEVEL'
874 , x_grant_guid => l_grant_guid
875 , x_success => l_return_status
876 , x_errorcode => l_msg_data
877 );
878
879 END IF;
880
881 IF (l_return_status <> FND_API.G_TRUE)
882 THEN
883 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
884 END IF;
885
886 resultout := 'COMPLETE:NO_ERROR';
887
888 EXCEPTION
889 WHEN OTHERS
890 THEN
891 /*****************************************************************************
892 ** Something went wrong return 'ERROR' and set the ERROR_MESSAGE
893 *****************************************************************************/
894 wf_engine.SetItemAttrText( itemtype => itemtype
895 , itemkey => itemkey
896 , aname => 'ERROR_MESSAGE'
897 , avalue => 'CAC_VIEW_WF_PVT.ProcessSubscription(): ' || to_char(SQLCODE)||':'||SQLERRM
898 );
899
900 resultout := 'COMPLETE:ERROR';
901 END; -- Added by Jane on 04/30/02
902 END IF;
903
904 END ProcessSubscription;
905
906
907 PROCEDURE StartRequest
908 /*******************************************************************************
909 ** Start of comments
910 ** Procedure : StartRequest
911 ** Description : Given the
912 ** Parameters :
913 ** name direction type required?
914 ** ---- --------- ---- ---------
915 ** p_api_version IN NUMBER required
916 ** p_init_msg_list IN VARCHAR2 optional
917 ** p_commit IN VARCHAR2 optional
918 ** x_return_status OUT VARCHAR2 optional
919 ** x_msg_count OUT NUMBER required
920 ** x_msg_data OUT VARCHAR2 required
921 ** p_REQUESTOR IN NUMBER required
922 ** p_GROUP_ID IN NUMBER optional
923 ** p_GROUP_NAME IN VARCHAR2 required
924 ** p_GROUP_DESCRIPTION IN VARCHAR2 required
925 ** p_PUBLIC_FLAG IN VARCHAR2 required
926 ** Notes :
927 ** 1)
928 **
929 ** End of comments
930 *******************************************************************************/
931 ( p_api_version IN NUMBER
932 , p_init_msg_list IN VARCHAR2
933 , p_commit IN VARCHAR2
934 , x_return_status OUT NOCOPY VARCHAR2
935 , x_msg_count OUT NOCOPY NUMBER
936 , x_msg_data OUT NOCOPY VARCHAR2
937 , p_CALENDAR_REQUESTOR IN NUMBER -- Resource ID of the Requestor
938 , p_GROUP_ID IN NUMBER -- Resource ID of Group if known
939 , p_GROUP_NAME IN VARCHAR2 -- (Suggested) Name of the Group Calendar
940 , p_GROUP_DESCRIPTION IN VARCHAR2 -- (Suggested) Description of the Group Calendar
941 , p_PUBLIC IN VARCHAR2 -- Public Calendar flag
942 )
943 IS
944 l_api_name CONSTANT VARCHAR2(30) := 'StartRequestCalendarWF';
945 l_api_version CONSTANT NUMBER := 1.0;
946 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
947
948 l_ItemType VARCHAR2(8) := 'CACVWSWF';
949
950 l_AdminWFRole VARCHAR2(30);
951
952 l_RequestorWFRole VARCHAR2(30);
953 l_RequestorWFName VARCHAR2(80);
954 l_RequestorEmpNumber NUMBER;
955
956 l_ItemKey VARCHAR2(100);
957
958 BEGIN
959 /*****************************************************************************
960 ** Standard call to check for call compatibility
961 *****************************************************************************/
962 IF NOT FND_API.Compatible_API_Call( l_api_version
963 , p_api_version
964 , l_api_name
965 , G_PKG_NAME
966 )
967 THEN
968 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
969 END IF;
970
971 /*****************************************************************************
972 ** Initialize message list if p_init_msg_list is set to TRUE
973 *****************************************************************************/
974 IF FND_API.To_Boolean(p_init_msg_list)
975 THEN
976 FND_MSG_PUB.Initialize;
977 END IF;
978
979 /*****************************************************************************
980 ** Initialize API return status to success
981 *****************************************************************************/
982 x_return_status := FND_API.G_RET_STS_SUCCESS;
983
984 /*****************************************************************************
985 ** Look up the Calendar Administrator from the Profile (this should be an
986 ** existing WF_ROLE)
987 *****************************************************************************/
988 l_AdminWFRole := FND_PROFILE.Value(name => 'JTF_CALENDAR_ADMINISTRATOR');
989
990 /*****************************************************************************
991 ** Look up the WF Role and Name of the requestor
992 *****************************************************************************/
993 WF_Role( p_CALENDAR_REQUESTOR
994 , 'RS_EMPLOYEE' -- Not used, for future enhancements
995 , l_RequestorWFRole
996 , l_RequestorWFName
997 , l_RequestorEmpNumber
998 );
999
1000 /*****************************************************************************
1001 ** Get a WF itemkey
1002 *****************************************************************************/
1003 SELECT to_char(cac_vws_itemkey_s.NEXTVAL) INTO l_ItemKey
1004 FROM DUAL;
1005
1006 /***************************************************************************
1007 ** Initialize the workflow
1008 ***************************************************************************/
1009 wf_engine.CreateProcess( itemtype => l_ItemType
1010 , itemkey => l_ItemKey
1011 , process => 'REQUEST_CALENDAR'
1012 );
1013
1014 wf_engine.SetItemAttrText( itemtype => l_itemtype
1015 , itemkey => l_ItemKey
1016 , aname => 'CALENDAR_ADMIN'
1017 , avalue => l_AdminWFRole
1018 );
1019
1020 wf_engine.SetItemAttrNumber( itemtype => l_itemtype
1021 , itemkey => l_ItemKey
1022 , aname => 'CALENDAR_REQUESTOR'
1023 , avalue => p_CALENDAR_REQUESTOR
1024 );
1025
1026 wf_engine.SetItemAttrText( itemtype => l_itemtype
1027 , itemkey => l_ItemKey
1028 , aname => 'CALENDAR_REQUESTOR_ROLE'
1029 , avalue => l_RequestorWFRole
1030 );
1031
1032 wf_engine.SetItemAttrText( itemtype => l_itemtype
1033 , itemkey => l_ItemKey
1034 , aname => 'CALENDAR_REQUESTOR_NAME'
1035 , avalue => l_RequestorWFName
1036 );
1037
1038 wf_engine.SetItemAttrNumber( itemtype => l_itemtype
1039 , itemkey => l_ItemKey
1040 , aname => 'GROUP_ID'
1041 , avalue => p_GROUP_ID
1042 );
1043
1044 wf_engine.SetItemAttrText( itemtype => l_itemtype
1045 , itemkey => l_ItemKey
1046 , aname => 'GROUP_NAME'
1047 , avalue => p_GROUP_NAME
1048 );
1049
1050 wf_engine.SetItemAttrText( itemtype => l_itemtype
1051 , itemkey => l_ItemKey
1052 , aname => 'GROUP_DESCRIPTION'
1053 , avalue => p_GROUP_DESCRIPTION
1054 );
1055
1056 wf_engine.SetItemAttrText( itemtype => l_itemtype
1057 , itemkey => l_ItemKey
1058 , aname => 'PUBLIC'
1059 , avalue => p_PUBLIC
1060 );
1061
1062 /***************************************************************************
1063 ** Start the workflow
1064 ***************************************************************************/
1065 wf_engine.StartProcess( itemtype => l_itemtype
1066 , itemkey => l_ItemKey
1067 );
1068
1069 /***************************************************************************
1070 ** Standard check of p_commit (WF won't start until commited..)
1071 ***************************************************************************/
1072 IF FND_API.To_Boolean(p_commit)
1073 THEN
1074 COMMIT WORK;
1075 END IF;
1076
1077 /*****************************************************************************
1078 ** Standard call to get message count and if count is > 1, get message info
1079 *****************************************************************************/
1080 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1081 , p_data => x_msg_data
1082 );
1083
1084 EXCEPTION
1085 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
1086 THEN
1087 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1088 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1089 , p_data => x_msg_data
1090 );
1091 WHEN OTHERS
1092 THEN
1093 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1094 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1095 THEN
1096 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
1097 , l_api_name
1098 );
1099 END IF;
1100 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1101 , p_data => x_msg_data
1102 );
1103
1104 END StartRequest;
1105
1106 PROCEDURE ProcessRequest
1107 /*******************************************************************************
1108 ** Start of comments
1109 ** Procedure : ProcessRequest
1110 ** Description :
1111 ** Parameters :
1112 ** name direction type required?
1113 ** ---- --------- ---- ---------
1114 ** itemtype IN VARCHAR2 required
1115 ** itemkey IN VARCHAR2 required
1116 ** actid IN NUMBER required
1117 ** funcmode IN VARCHAR2 required
1118 ** resultout OUT VARCHAR2 required
1119 **
1120 ** Notes :
1121 **
1122 ** End of comments
1123 ******************************************************************************/
1124 ( itemtype IN VARCHAR2
1125 , itemkey IN VARCHAR2
1126 , actid IN NUMBER
1127 , funcmode IN VARCHAR2
1128 , resultout OUT NOCOPY VARCHAR2
1129 )
1130 IS
1131 CURSOR c_GroupExists
1132 /*****************************************************************************
1133 ** If a group already exists with this Name and Description then we just need
1134 ** to create a 'GROUP/PUBLIC CALENDAR' usage for it
1135 *****************************************************************************/
1136 ( b_GroupName VARCHAR2
1137 , b_GroupDescription VARCHAR2
1138 )IS SELECT jrb.group_id
1139 FROM jtf_rs_groups_b jrb
1140 , jtf_rs_groups_tl jrt
1141 WHERE jrb.group_id = jrt.group_id
1142 AND ( (jrb.end_date_active > SYSDATE)
1143 OR (jrb.end_date_active IS NULL)
1144 )
1145 AND jrt.group_name = b_GroupName
1146 -- AND jrt.group_desc = b_GroupDescription
1147 AND jrt.language = userenv('LANG');
1148
1149 CURSOR c_GroupUsageExists
1150 /*****************************************************************************
1151 ** If the group usage already exists we just create the grant
1152 *****************************************************************************/
1153 ( b_GroupID NUMBER
1154 , b_Usage VARCHAR2
1155 )IS SELECT group_usage_id
1156 FROM jtf_rs_group_usages jru
1157 WHERE jru.group_id = b_GroupID
1158 AND jru.usage = b_Usage;
1159
1160 l_AdminWFRole VARCHAR2(30);
1161
1162 l_RequestorID NUMBER;
1163 l_RequestorWFRole VARCHAR2(30);
1164 l_RequestorWFName VARCHAR2(80);
1165
1166 l_GroupID NUMBER;
1167 l_GroupName VARCHAR2(60);
1168 l_GroupNumber VARCHAR2(30);
1169 l_GroupDescription VARCHAR2(240);
1170 l_PublicFlag VARCHAR2(1);
1171 l_GroupUsageID NUMBER;
1172 l_Usage VARCHAR2(240);
1173
1174 l_return_status VARCHAR2(1);
1175 l_msg_count NUMBER;
1176 l_msg_data VARCHAR2(2000);
1177 l_msg_index_out NUMBER;
1178 l_grant_guid RAW(16);
1179
1180 l_debug varchar2(4000);
1181
1182
1183 BEGIN
1184 /*****************************************************************************
1185 ** Initialize message list
1186 *****************************************************************************/
1187 FND_MSG_PUB.Initialize;
1188
1189 /*****************************************************************************
1190 ** Initialize API return status to success
1191 *****************************************************************************/
1192 l_return_status := FND_API.G_RET_STS_SUCCESS;
1193
1194 /*****************************************************************************
1195 ** Get the WF attribute values
1196 *****************************************************************************/
1197 l_RequestorID := wf_engine.GetItemAttrNumber
1198 ( itemtype => itemtype
1199 , itemkey => itemkey
1200 , aname => 'CALENDAR_REQUESTOR'
1201 );
1202
1203 l_GroupID := wf_engine.GetItemAttrNumber
1204 ( itemtype => itemtype
1205 , itemkey => itemkey
1206 , aname => 'GROUP_ID'
1207 );
1208
1209 l_GroupName := wf_engine.GetItemAttrText
1210 ( itemtype => itemtype
1211 , itemkey => itemkey
1212 , aname => 'GROUP_NAME'
1213 );
1214
1215 l_GroupDescription := wf_engine.GetItemAttrText
1216 ( itemtype => itemtype
1217 , itemkey => itemkey
1218 , aname => 'GROUP_DESCRIPTION'
1219 );
1220
1221 l_PublicFlag := wf_engine.GetItemAttrText
1222 ( itemtype => itemtype
1223 , itemkey => itemkey
1224 , aname => 'PUBLIC'
1225 );
1226
1227 IF (l_GroupID IS NULL)
1228 THEN
1229 /***************************************************************************
1230 ** No Group ID is set, let's see if the name/description already exists
1231 ***************************************************************************/
1232 IF (c_GroupExists%ISOPEN)
1233 THEN
1234 CLOSE c_GroupExists;
1235 END IF;
1236
1237 OPEN c_GroupExists( l_GroupName
1238 , l_GroupDescription
1239 );
1240
1241 FETCH c_GroupExists INTO l_GroupID;
1242 IF (c_GroupExists%NOTFOUND)
1243 THEN
1244 /*************************************************************************
1245 ** No Group exists with this name/description, assume it's a new one.
1246 *************************************************************************/
1247 JTF_RS_GROUPS_PUB.Create_Resource_Group
1248 ( P_API_VERSION => 1.0
1249 , P_INIT_MSG_LIST => FND_API.G_TRUE
1250 , P_COMMIT => FND_API.G_FALSE -- Can't commit in WF!
1251 , P_GROUP_NAME => l_GroupName
1252 , P_GROUP_DESC => l_GroupDescription
1253 , P_START_DATE_ACTIVE => SYSDATE
1254 , X_RETURN_STATUS => l_return_status
1255 , X_MSG_COUNT => l_msg_count
1256 , X_MSG_DATA => l_msg_data
1257 , X_GROUP_ID => l_GroupID
1258 --, X_GROUP_NUMBER => l_GroupName
1259 -- Modified by jawang on 12/31/2002 to fix the NOCOPY issue
1260 , X_GROUP_NUMBER => l_GroupNumber
1261 );
1262
1263 /*************************************************************************
1264 ** Standard call to get message count and if count is 1, get message info
1265 *************************************************************************/
1266 FND_MSG_PUB.Count_And_Get( p_count => l_msg_count
1267 , p_data => l_msg_data
1268 );
1269 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1270 THEN
1271 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1272 END IF;
1273 END IF;
1274
1275 IF (c_GroupExists%ISOPEN)
1276 THEN
1277 CLOSE c_GroupExists;
1278 END IF;
1279 END IF;
1280
1281
1282 /*****************************************************************************
1283 ** Now that we made sure the group exists, we need to create the proper usage
1284 *****************************************************************************/
1285 IF (l_PublicFlag = 'Y')
1286 THEN
1287 l_Usage := 'PUBLIC_CALENDAR';
1288 ELSE
1289 l_Usage := 'GROUP_CALENDAR';
1290 END IF;
1291
1292 IF (c_GroupUsageExists%ISOPEN)
1293 THEN
1294 CLOSE c_GroupUsageExists;
1295 END IF;
1296
1297 OPEN c_GroupUsageExists( l_GroupID
1298 , l_Usage
1299 );
1300
1301 FETCH c_GroupUsageExists INTO l_GroupUsageID;
1302 IF (c_GroupUsageExists%NOTFOUND)
1303 THEN
1304 /***************************************************************************
1305 ** Create the usage
1306 ***************************************************************************/
1307 JTF_RS_GROUP_USAGES_PUB.Create_Group_Usage
1308 ( P_API_VERSION => 1.0
1309 , P_INIT_MSG_LIST => FND_API.G_FALSE
1310 , P_COMMIT => FND_API.G_FALSE
1311 , P_GROUP_ID => l_GroupID
1312 , P_GROUP_NUMBER => NULL
1313 , P_USAGE => l_usage
1314 , X_RETURN_STATUS => l_return_status
1315 , X_MSG_COUNT => l_msg_count
1316 , X_MSG_DATA => l_msg_data
1317 , X_GROUP_USAGE_ID => l_GroupUsageID
1318 );
1319
1320 /***************************************************************************
1321 ** Standard call to get message count and if count is 1, get message info
1322 ***************************************************************************/
1323 FND_MSG_PUB.Count_And_Get( p_count => l_msg_count
1324 , p_data => l_msg_data
1325 );
1326 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1327 THEN
1328 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1329 END IF;
1330 --END IF;
1331
1332 /*IF (c_GroupUsageExists%ISOPEN)
1333 THEN
1334 CLOSE c_GroupUsageExists;
1335 END IF;
1336 */
1337
1338 l_return_status := FND_API.G_TRUE;
1339
1340 /*****************************************************************************
1341 ** Grant Administrator privs to the requestor
1342 *****************************************************************************/
1343 fnd_grants_pkg.grant_function( p_api_version => 1.0
1344 , p_menu_name => 'CAC_VWS_ADMIN_ACCESS'
1345 , p_instance_type => 'INSTANCE'
1346 , p_object_name => 'JTF_TASK_RESOURCE'
1347 , p_instance_pk1_value => to_char(nvl(l_GroupID,1))
1348 , p_instance_pk2_value => 'RS_GROUP'
1349 , p_grantee_type => 'USER'
1350 , p_grantee_key => to_char(nvl(l_RequestorID,1))
1351 , p_start_date => SYSDATE
1352 , p_end_date => NULL
1353 , p_program_name => 'CALENDAR'
1354 , p_program_tag => 'ACCESS LEVEL'
1355 , x_grant_guid => l_grant_guid
1356 , x_success => l_return_status
1357 , x_errorcode => l_msg_data
1358 );
1359
1360 /*****************************************************************************
1361 ** Grant Readonly privs to the requestor as well
1362 ** Added by Jane on 04/30/02
1363 *****************************************************************************/
1364 fnd_grants_pkg.grant_function( p_api_version => 1.0
1365 , p_menu_name => 'CAC_VWS_READ_ACCESS'
1366 , p_instance_type => 'INSTANCE'
1367 , p_object_name => 'JTF_TASK_RESOURCE'
1368 , p_instance_pk1_value => to_char(nvl(l_GroupID,1))
1369 , p_instance_pk2_value => 'RS_GROUP'
1370 , p_grantee_type => 'USER'
1371 , p_grantee_key => to_char(nvl(l_RequestorID,1))
1372 , p_start_date => SYSDATE
1373 , p_end_date => NULL
1374 , p_program_name => 'CALENDAR'
1375 , p_program_tag => 'ACCESS LEVEL'
1376 , x_grant_guid => l_grant_guid
1377 , x_success => l_return_status
1378 , x_errorcode => l_msg_data
1379 );
1380
1381 IF (l_return_status <> FND_API.G_TRUE)
1382 THEN
1383 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1384 END IF;
1385
1386 END IF; -- End of c_GroupUsageExists%NOTFOUND
1387
1388 IF (c_GroupUsageExists%ISOPEN)
1389 THEN
1390 CLOSE c_GroupUsageExists;
1391 END IF;
1392
1393 /*****************************************************************************
1394 ** All went well return 'NO_ERROR'
1395 *****************************************************************************/
1396 resultout := 'COMPLETE:NO_ERROR';
1397
1398 EXCEPTION
1399 WHEN OTHERS
1400 THEN
1401 IF (c_GroupExists%ISOPEN)
1402 THEN
1403 CLOSE c_GroupExists;
1404 END IF;
1405 IF (c_GroupUsageExists%ISOPEN)
1406 THEN
1407 CLOSE c_GroupUsageExists;
1408 END IF;
1409
1410 /*****************************************************************************
1411 ** Something went wrong return 'ERROR' and set the ERROR_MESSAGE
1412 *****************************************************************************/
1413 wf_engine.SetItemAttrText( itemtype => itemtype
1414 , itemkey => itemkey
1415 , aname => 'ERROR_MESSAGE'
1416 , avalue => 'CAC_VIEW_WF_PVT.ProcessRequest(): ' || to_char(SQLCODE)||':'||SQLERRM
1417 );
1418
1419 resultout := 'COMPLETE:ERROR';
1420
1421 END ProcessRequest;
1422
1423
1424 PROCEDURE StartInvite
1425 /*******************************************************************************
1426 ** Start of comments
1427 ** Procedure : StartInvite
1428 ** Description : Given the task ID of the appointment (p_TaskID) and the
1429 ** Resource ID of the invitor (p_INVITOR) this procedure will
1430 ** send notifications to all the attendees of the appointment.
1431 ** Parameters :
1432 ** name direction type required?
1433 ** ---- --------- ---- ---------
1434 ** p_api_version IN NUMBER required
1435 ** p_init_msg_list IN VARCHAR2 optional
1436 ** p_commit IN VARCHAR2 optional
1437 ** x_return_status OUT VARCHAR2 optional
1438 ** x_msg_count OUT NUMBER required
1439 ** x_msg_data OUT VARCHAR2 required
1440 ** p_INVITOR IN NUMBER required
1441 ** p_TaskID IN NUMBER required
1442 ** Notes :
1443 ** 1) If an invitee does not exist in the WF directory a notification will
1444 ** be send to the invitor saying that the invitation was not send.
1445 ** 2) Currently invitations are only send to employees
1446 ** 3) The WFs won't be started until a commmit is done.
1447 **
1448 ** End of comments
1449 *******************************************************************************/
1450 ( p_api_version IN NUMBER
1451 , p_init_msg_list IN VARCHAR2
1452 , p_commit IN VARCHAR2
1453 , x_return_status OUT NOCOPY VARCHAR2
1454 , x_msg_count OUT NOCOPY NUMBER
1455 , x_msg_data OUT NOCOPY VARCHAR2
1456 , p_INVITOR IN NUMBER -- Resource ID of Invitor
1457 , p_TaskID IN NUMBER -- Task ID of the appointment
1458 )
1459 IS
1460 l_api_name CONSTANT VARCHAR2(30) := 'StartInviteWF';
1461 l_api_version CONSTANT NUMBER := 1.0;
1462 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
1463 l_ItemType VARCHAR2(8) := 'CACVWSWF';
1464 l_AdminWFRole VARCHAR2(30);
1465 l_task_type_name VARCHAR2(30);
1466 l_task_priority_name VARCHAR2(30);
1467 l_timezone VARCHAR2(80);
1468
1469 CURSOR c_Task
1470 /*****************************************************************************
1471 ** Cursor to pick up all the invitees for the appointment. Also picks up
1472 ** a itemkey from the sequence, this is needed to start the workflow
1473 *****************************************************************************/
1474 (b_TaskID NUMBER
1475 )IS SELECT to_char(cac_vws_itemkey_s.NEXTVAL) ItemKey
1476 , jta.task_assignment_id TASK_ASSIGNMENT_ID
1477 , jta.resource_id INVITEE
1478 , jta.resource_type_code INVITEE_CODE
1479 , jtl.task_name TASK_NAME
1480 , jtl.description TASK_DESCRIPTION
1481 , jtb.calendar_start_date START_DATE
1482 , jtb.task_type_id TYPE_ID
1483 , jtb.task_priority_id PRIORITY_ID
1484 , (jtb.calendar_end_date - jtb.calendar_start_date)*24*60 DURATION
1485 , jtb.timezone_id TIMEZONE_ID
1486 FROM jtf_tasks_b jtb
1487 , jtf_tasks_tl jtl
1488 , jtf_task_all_assignments jta
1489 WHERE jtb.task_id = jtl.task_id
1490 AND jtl.language = userenv('LANG')
1491 AND jta.task_id = jtb.task_id
1492 AND jta.assignee_role = 'ASSIGNEE' -- don't sent one to the owner??
1493 AND jta.show_on_calendar = 'Y' -- so Vanessa doesn't get it..
1494 AND jtb.task_id = b_TaskID;
1495
1496 BEGIN
1497 /*****************************************************************************
1498 ** Standard call to check for call compatibility
1499 *****************************************************************************/
1500 IF NOT FND_API.Compatible_API_Call( l_api_version
1501 , p_api_version
1502 , l_api_name
1503 , G_PKG_NAME
1504 )
1505 THEN
1506 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1507 END IF;
1508
1509 /*****************************************************************************
1510 ** Initialize message list if p_init_msg_list is set to TRUE
1511 *****************************************************************************/
1512 IF FND_API.To_Boolean(p_init_msg_list)
1513 THEN
1514 FND_MSG_PUB.Initialize;
1515 END IF;
1516
1517 /*****************************************************************************
1518 ** Initialize API return status to success
1519 *****************************************************************************/
1520 x_return_status := FND_API.G_RET_STS_SUCCESS;
1521
1522 /*****************************************************************************
1523 ** Look up the Calendar Administrator from the Profile (this should be an
1524 ** existing WF_ROLE)
1525 *****************************************************************************/
1526 l_AdminWFRole := FND_PROFILE.Value(name => 'JTF_CALENDAR_ADMINISTRATOR');
1527
1528 /*****************************************************************************
1529 ** Get Appointment details for every invitee and start a notification WF for
1530 ** all of them.
1531 *****************************************************************************/
1532 FOR r_Task IN c_Task(p_TaskID)
1533 LOOP <<ASSIGNEES>>
1534 /***************************************************************************
1535 ** Initialize the workflow
1536 ***************************************************************************/
1537 l_task_type_name := get_type_name(r_Task.TYPE_ID);
1538 l_task_priority_name := get_priority_name(r_Task.PRIORITY_ID);
1539 l_timezone := GetTimezone(p_timezone_id => r_Task.TIMEZONE_ID);
1540 wf_engine.CreateProcess( itemtype => l_ItemType
1541 , itemkey => r_Task.ItemKey
1542 , process => 'SEND_INVITATION'
1543 );
1544
1545 wf_engine.SetItemAttrText( itemtype => l_itemtype
1546 , itemkey => r_Task.ItemKey
1547 , aname => 'CALENDAR_ADMIN'
1548 , avalue => l_AdminWFRole
1549 );
1550
1551 wf_engine.SetItemAttrNumber( itemtype => l_itemtype
1552 , itemkey => r_Task.ItemKey
1553 , aname => 'INVITEE'
1554 , avalue => r_Task.INVITEE
1555 );
1556 ---Enh # 3443999, amigupta, Setting new attribute value INVITEE_CODE for handling Resource_type_Code also
1557
1558 wf_engine.SetItemAttrText( itemtype => l_itemtype
1559 , itemkey => r_Task.ItemKey
1560 , aname => 'INVITEE_CODE'
1561 , avalue => r_Task.INVITEE_CODE
1562 );
1563
1564 wf_engine.SetItemAttrNumber( itemtype => l_itemtype
1565 , itemkey => r_Task.ItemKey
1566 , aname => 'TASK_ASSIGNMENT_ID'
1567 , avalue => r_Task.TASK_ASSIGNMENT_ID
1568 );
1569
1570 wf_engine.SetItemAttrNumber( itemtype => l_itemtype
1571 , itemkey => r_Task.ItemKey
1572 , aname => 'INVITOR'
1573 , avalue => p_INVITOR
1574 );
1575
1576 wf_engine.SetItemAttrNumber( itemtype => l_itemtype
1577 , itemkey => r_Task.ItemKey
1578 , aname => 'TASK_ID'
1579 , avalue => p_TaskID
1580 );
1581
1582 wf_engine.SetItemAttrText( itemtype => l_itemtype
1583 , itemkey => r_Task.ItemKey
1584 , aname => 'TASK_NAME'
1585 , avalue => r_Task.TASK_NAME
1586 );
1587
1588 wf_engine.SetItemAttrText( itemtype => l_itemtype
1589 , itemkey => r_Task.ItemKey
1590 , aname => 'TASK_DESCRIPTION'
1591 , avalue => r_Task.TASK_DESCRIPTION
1592 );
1593
1594 wf_engine.SetItemAttrDate( itemtype => l_itemtype
1595 , itemkey => r_Task.ItemKey
1596 , aname => 'START_DATE'
1597 , avalue => r_Task.START_DATE
1598 );
1599
1600 wf_engine.SetItemAttrText( itemtype => l_itemtype
1601 , itemkey => r_Task.ItemKey
1602 , aname => 'TYPE'
1603 , avalue => l_task_type_name
1604 );
1605 wf_engine.SetItemAttrText( itemtype => l_itemtype
1606 , itemkey => r_Task.ItemKey
1607 , aname => 'PRIORITY'
1608 , avalue => l_task_priority_name
1609 );
1610 wf_engine.SetItemAttrText( itemtype => l_itemtype
1611 , itemkey => r_Task.ItemKey
1612 , aname => 'DURATION'
1613 , avalue => get_duration(r_Task.DURATION)
1614 );
1615 wf_engine.SetItemAttrText( itemtype => l_itemtype
1616 , itemkey => r_Task.ItemKey
1617 , aname => 'TIMEZONE'
1618 , avalue => l_timezone
1619 );
1620
1621 /***************************************************************************
1622 ** Start the workflow
1623 ***************************************************************************/
1624 wf_engine.StartProcess( itemtype => l_itemtype
1625 , itemkey => r_Task.ItemKey
1626 );
1627
1628
1629 /***************************************************************************
1630 ** Standard check of p_commit (WF won't start until commited)
1631 ***************************************************************************/
1632 IF FND_API.To_Boolean(p_commit)
1633 THEN
1634 COMMIT WORK;
1635 END IF;
1636
1637
1638 END LOOP ASSIGNEES;
1639
1640 /*****************************************************************************
1641 ** Standard call to get message count and if count is > 1, get message info
1642 *****************************************************************************/
1643 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1644 , p_data => x_msg_data
1645 );
1646 EXCEPTION
1647 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
1648 THEN
1649 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1650 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1651 , p_data => x_msg_data
1652 );
1653 WHEN OTHERS
1654 THEN
1655 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1656 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1657 THEN
1658 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
1659 , l_api_name
1660 );
1661 END IF;
1662 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1663 , p_data => x_msg_data
1664 );
1665
1666 END StartInvite;
1667
1668 PROCEDURE StartInviteResource
1669 /*******************************************************************************
1670 ** Start of comments
1671 ** Procedure : StartInviteResource
1672 ** Description : Given the task ID of the appointment (p_TaskID) and the
1673 ** Resource ID of the invitee (p_INVITEE) this procedure will
1674 ** send notification to the paticular attendee of the appointment.
1675 ** Parameters :
1676 ** name direction type required?
1677 ** ---- --------- ---- ---------
1678 ** p_api_version IN NUMBER required
1679 ** p_init_msg_list IN VARCHAR2 optional
1680 ** p_commit IN VARCHAR2 optional
1681 ** x_return_status OUT VARCHAR2 optional
1682 ** x_msg_count OUT NUMBER required
1683 ** x_msg_data OUT VARCHAR2 required
1684 ** p_INVITEE IN NUMBER required
1685 ** p_INVITOR IN NUMBER required
1686 ** p_TaskID IN NUMBER required
1687 ** Notes :
1688 ** 1) If an invitee does not exist in the WF directory a notification will
1689 ** be send to the invitor saying that the invitation was not send.
1690 ** 2) Currently invitations are only send to employees
1691 ** 3) The WFs won't be started until a commmit is done.
1692 **
1693 ** End of comments
1694 *******************************************************************************/
1695 ( p_api_version IN NUMBER
1696 , p_init_msg_list IN VARCHAR2
1697 , p_commit IN VARCHAR2
1698 , x_return_status OUT NOCOPY VARCHAR2
1699 , x_msg_count OUT NOCOPY NUMBER
1700 , x_msg_data OUT NOCOPY VARCHAR2
1701 , p_INVITEE IN NUMBER -- Resource ID of Invitee
1702 , p_INVITEE_TYPE IN VARCHAR2 --Resource Type of the INVITEE
1703 , p_INVITOR IN NUMBER -- Resource ID of Invitor
1704 , p_TaskID IN NUMBER -- Task ID of the appointment
1705 )
1706 IS
1707 l_api_name CONSTANT VARCHAR2(30) := 'StartInviteWF';
1708 l_api_version CONSTANT NUMBER := 1.0;
1709 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
1710 l_ItemType VARCHAR2(8) := 'CACVWSWF';
1711 l_AdminWFRole VARCHAR2(30);
1712 l_task_type_name VARCHAR2(30);
1713 l_task_priority_name VARCHAR2(30);
1714 l_timezone VARCHAR2(80);
1715
1716 CURSOR c_Task
1717 /*****************************************************************************
1718 ** Cursor to pick up all the invitees for the appointment. Also picks up
1719 ** a itemkey from the sequence, this is needed to start the workflow
1720 *****************************************************************************/
1721 (b_TaskID NUMBER, b_INVITEE NUMBER, b_INVITEE_TYPE VARCHAR2
1722 )IS SELECT to_char(cac_vws_itemkey_s.NEXTVAL) ItemKey
1723 , jta.task_assignment_id TASK_ASSIGNMENT_ID
1724 , jta.resource_id INVITEE
1725 , jta.resource_type_code INVITEE_CODE
1726 , jtl.task_name TASK_NAME
1727 , jtl.description TASK_DESCRIPTION
1728 , jtb.calendar_start_date START_DATE
1729 , jtb.task_type_id TYPE_ID
1730 , jtb.task_priority_id PRIORITY_ID
1731 , (jtb.calendar_end_date - jtb.calendar_start_date)*24*60 DURATION
1732 , jtb.timezone_id TIMEZONE_ID
1733 FROM jtf_tasks_b jtb
1734 , jtf_tasks_tl jtl
1735 , jtf_task_all_assignments jta
1736 WHERE jtb.task_id = jtl.task_id
1737 AND jtl.language = userenv('LANG')
1738 AND jta.task_id = jtb.task_id
1739 AND jta.assignee_role = 'ASSIGNEE' -- don't sent one to the owner??
1740 AND jta.show_on_calendar = 'Y' -- so Vanessa doesn't get it..
1741 AND jtb.task_id = b_TaskID
1742 AND jta.resource_id = b_INVITEE
1743 AND jta.resource_type_code = b_INVITEE_TYPE ;
1744
1745 BEGIN
1746 /*****************************************************************************
1747 ** Standard call to check for call compatibility
1748 *****************************************************************************/
1749 IF NOT FND_API.Compatible_API_Call( l_api_version
1750 , p_api_version
1751 , l_api_name
1752 , G_PKG_NAME
1753 )
1754 THEN
1755 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1756 END IF;
1757
1758 /*****************************************************************************
1759 ** Initialize message list if p_init_msg_list is set to TRUE
1760 *****************************************************************************/
1761 IF FND_API.To_Boolean(p_init_msg_list)
1762 THEN
1763 FND_MSG_PUB.Initialize;
1764 END IF;
1765
1766 /*****************************************************************************
1767 ** Initialize API return status to success
1768 *****************************************************************************/
1769 x_return_status := FND_API.G_RET_STS_SUCCESS;
1770
1771 /*****************************************************************************
1772 ** Look up the Calendar Administrator from the Profile (this should be an
1773 ** existing WF_ROLE)
1774 *****************************************************************************/
1775 l_AdminWFRole := FND_PROFILE.Value(name => 'JTF_CALENDAR_ADMINISTRATOR');
1776
1777 /*****************************************************************************
1778 ** Get Appointment details for every invitee and start a notification WF for
1779 ** all of them.
1780 *****************************************************************************/
1781 FOR r_Task IN c_Task(p_TaskID, p_INVITEE, p_INVITEE_TYPE)
1782 LOOP <<ASSIGNEES>>
1783 /***************************************************************************
1784 ** Initialize the workflow
1785 ***************************************************************************/
1786 l_task_type_name := get_type_name(r_Task.TYPE_ID);
1787 l_task_priority_name := get_priority_name(r_Task.PRIORITY_ID);
1788 l_timezone := GetTimezone(p_timezone_id => r_Task.TIMEZONE_ID);
1789 wf_engine.CreateProcess( itemtype => l_ItemType
1790 , itemkey => r_Task.ItemKey
1791 , process => 'SEND_INVITATION'
1792 );
1793
1794 wf_engine.SetItemAttrText( itemtype => l_itemtype
1795 , itemkey => r_Task.ItemKey
1796 , aname => 'CALENDAR_ADMIN'
1797 , avalue => l_AdminWFRole
1798 );
1799
1800 wf_engine.SetItemAttrNumber( itemtype => l_itemtype
1801 , itemkey => r_Task.ItemKey
1802 , aname => 'INVITEE'
1803 , avalue => r_Task.INVITEE
1804 );
1805 ---Enh # 3443999, amigupta, Setting new attribute value INVITEE_CODE for handling Resource_type_Code also
1806
1807 wf_engine.SetItemAttrText( itemtype => l_itemtype
1808 , itemkey => r_Task.ItemKey
1809 , aname => 'INVITEE_CODE'
1810 , avalue => r_Task.INVITEE_CODE
1811 );
1812
1813 wf_engine.SetItemAttrNumber( itemtype => l_itemtype
1814 , itemkey => r_Task.ItemKey
1815 , aname => 'TASK_ASSIGNMENT_ID'
1816 , avalue => r_Task.TASK_ASSIGNMENT_ID
1817 );
1818
1819 wf_engine.SetItemAttrNumber( itemtype => l_itemtype
1820 , itemkey => r_Task.ItemKey
1821 , aname => 'INVITOR'
1822 , avalue => p_INVITOR
1823 );
1824
1825 wf_engine.SetItemAttrNumber( itemtype => l_itemtype
1826 , itemkey => r_Task.ItemKey
1827 , aname => 'TASK_ID'
1828 , avalue => p_TaskID
1829 );
1830
1831 wf_engine.SetItemAttrText( itemtype => l_itemtype
1832 , itemkey => r_Task.ItemKey
1833 , aname => 'TASK_NAME'
1834 , avalue => r_Task.TASK_NAME
1835 );
1836
1837 wf_engine.SetItemAttrText( itemtype => l_itemtype
1838 , itemkey => r_Task.ItemKey
1839 , aname => 'TASK_DESCRIPTION'
1840 , avalue => r_Task.TASK_DESCRIPTION
1841 );
1842
1843 wf_engine.SetItemAttrDate( itemtype => l_itemtype
1844 , itemkey => r_Task.ItemKey
1845 , aname => 'START_DATE'
1846 , avalue => r_Task.START_DATE
1847 );
1848
1849 wf_engine.SetItemAttrText( itemtype => l_itemtype
1850 , itemkey => r_Task.ItemKey
1851 , aname => 'TYPE'
1852 , avalue => l_task_type_name
1853 );
1854 wf_engine.SetItemAttrText( itemtype => l_itemtype
1855 , itemkey => r_Task.ItemKey
1856 , aname => 'PRIORITY'
1857 , avalue => l_task_priority_name
1858 );
1859 wf_engine.SetItemAttrText( itemtype => l_itemtype
1860 , itemkey => r_Task.ItemKey
1861 , aname => 'DURATION'
1862 , avalue => get_duration(r_Task.DURATION)
1863 );
1864 wf_engine.SetItemAttrText( itemtype => l_itemtype
1865 , itemkey => r_Task.ItemKey
1866 , aname => 'TIMEZONE'
1867 , avalue => l_timezone
1868 );
1869
1870 /***************************************************************************
1871 ** Start the workflow
1872 ***************************************************************************/
1873 wf_engine.StartProcess( itemtype => l_itemtype
1874 , itemkey => r_Task.ItemKey
1875 );
1876
1877
1878 /***************************************************************************
1879 ** Standard check of p_commit (WF won't start until commited)
1880 ***************************************************************************/
1881 IF FND_API.To_Boolean(p_commit)
1882 THEN
1883 COMMIT WORK;
1884 END IF;
1885
1886
1887 END LOOP ASSIGNEES;
1888
1889 /*****************************************************************************
1890 ** Standard call to get message count and if count is > 1, get message info
1891 *****************************************************************************/
1892 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1893 , p_data => x_msg_data
1894 );
1895 EXCEPTION
1896 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
1897 THEN
1898 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1899 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1900 , p_data => x_msg_data
1901 );
1902 WHEN OTHERS
1903 THEN
1904 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1905 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1906 THEN
1907 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
1908 , l_api_name
1909 );
1910 END IF;
1911 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1912 , p_data => x_msg_data
1913 );
1914
1915 END StartInviteResource;
1916
1917 PROCEDURE UpdateInvitation
1918 /*******************************************************************************
1919 ** Start of comments
1920 ** Procedure : UpdateInvitation
1921 ** Description : Given the task ID of the appointment (p_TaskID) and the
1922 ** Resource ID of the invitee (p_INVITEE) this procedure will
1923 ** respond to the notifications from the attendees of the appointment.
1924 ** Parameters :
1925 ** name direction type required?
1926 ** ---- --------- ---- ---------
1927 ** itemtype IN VARCHAR2 required
1928 ** itemkey IN VARCHAR2 required
1929 ** actid IN NUMBER required
1930 ** funcmode IN VARCHAR2 required
1931 ** resultout OUT VARCHAR2 required
1932 ** End of comments
1933 *******************************************************************************/
1934 ( itemtype IN VARCHAR2
1935 , itemkey IN VARCHAR2
1936 , actid IN NUMBER
1937 , funcmode IN VARCHAR2
1938 , resultout OUT NOCOPY VARCHAR2
1939 )
1940 IS
1941
1942 l_object_version_number NUMBER :=1 ;
1943 l_InviteeResourceID NUMBER;
1944 l_TaskID NUMBER;
1945 l_task_assignment_id NUMBER;
1946 l_assignment_status_id NUMBER;
1947 l_resource_type varchar2(30);
1948 l_return_status VARCHAR2(1);
1949 l_result varchar2(30);
1950 l_msg_count NUMBER;
1951 l_msg_data VARCHAR2(1000);
1952
1953 BEGIN
1954
1955 IF (funcmode = 'RUN')
1956 THEN
1957 /***************************************************************************
1958 ** 'RUN' function from WF
1959 ***************************************************************************/
1960
1961 /***************************************************************************
1962 ** Pick up the resource ID of the INVITEE
1963 ***************************************************************************/
1964 l_result := wf_engine.getItemAttrText
1965 ( itemtype => itemtype
1966 , itemkey => itemkey
1967 , aname => 'RESULT'
1968 );
1969
1970 l_InviteeResourceID := wf_engine.GetItemAttrNumber
1971 ( itemtype => itemtype
1972 , itemkey => itemkey
1973 , aname => 'INVITEE'
1974 );
1975
1976 l_TaskID := wf_engine.GetItemAttrNumber
1977 ( itemtype => itemtype
1978 , itemkey => itemkey
1979 , aname => 'TASK_ID'
1980 );
1981
1982 l_resource_type := wf_engine.getItemAttrText( itemtype => itemtype
1983 , itemkey => itemkey
1984 , aname => 'INVITEE_CODE'
1985 );
1986
1987 l_task_assignment_id := wf_engine.GetItemAttrNumber
1988 ( itemtype => itemtype
1989 , itemkey => itemkey
1990 , aname => 'TASK_ASSIGNMENT_ID'
1991 );
1992
1993
1994 IF (l_result = 'ACCEPT' OR l_result = 'ACCEPT_ALL')
1995 THEN
1996 l_assignment_status_id :=3;
1997 ELSIF (l_result = 'DECLINE' OR l_result = 'DECLINE_ALL')
1998 THEN
1999 l_assignment_status_id := 4;
2000 END IF;
2001
2002 fnd_msg_pub.initialize;
2003
2004 JTF_TASK_ASSIGNMENTS_PVT.update_task_assignment
2005 (
2006 p_api_version => 1.0,
2007 p_object_version_number => l_object_version_number,
2008 p_init_msg_list => 'T', --?
2009 p_task_assignment_id => l_task_assignment_id,
2010 p_resource_type_code => l_resource_type,
2011 p_resource_id => l_InviteeResourceID,
2012 p_schedule_flag => fnd_api.g_miss_char, --Y Or N??
2013 p_actual_start_date => null, --?
2014 p_actual_end_date => null, --?
2015 p_assignment_status_id => l_assignment_status_id,
2016 p_show_on_calendar => 'Y',
2017 p_enable_workflow => 'N',
2018 p_abort_workflow => 'N',
2019 x_return_status => l_return_status,
2020 x_msg_count => l_msg_count,
2021 x_msg_data => l_msg_data
2022 ) ;
2023
2024
2025 IF l_return_status = fnd_api.g_ret_sts_success
2026 THEN
2027
2028 cac_view_wf_pvt.processinvitation(p_api_version => 1.0
2029 ,p_init_msg_list => 'T'
2030 ,p_commit => 'F'
2031 ,x_return_status => l_return_status
2032 ,x_msg_count => l_msg_count
2033 ,x_msg_data => l_msg_data
2034 ,p_task_assignment_id => l_task_assignment_id
2035 ,p_resource_type => l_resource_type
2036 ,p_resource_id => l_InviteeResourceID
2037 ,p_assignment_status_id => l_assignment_status_id);
2038
2039 END IF;
2040
2041 END IF;
2042
2043 END UpdateInvitation;
2044
2045 PROCEDURE DetermineWFRole
2046 /*******************************************************************************
2047 ** Start of comments
2048 ** Procedure : DetermineWFRole
2049 ** Description : Work out the WF role for the given resource.
2050 ** Used to implement the 'Determine WF Role' function in the
2051 ** 'CACVWSWF.Send Invitation' workflow.
2052 ** Parameters :
2053 ** name direction type required?
2054 ** ---- --------- ---- ---------
2055 ** itemtype IN VARCHAR2 required
2056 ** itemkey IN VARCHAR2 required
2057 ** actid IN NUMBER required
2058 ** funcmode IN VARCHAR2 required
2059 ** resultout OUT VARCHAR2 required
2060 **
2061 ** Notes :
2062 ** 1) Expects WF item attributes 'RESOURCE_ID' and 'RESOURCE_TYPE' to be
2063 ** available to this procedure.
2064 ** 2) This procedure should only be used within Workflow
2065 **
2066 ** End of comments
2067 ******************************************************************************/
2068 ( itemtype IN VARCHAR2
2069 , itemkey IN VARCHAR2
2070 , actid IN NUMBER
2071 , funcmode IN VARCHAR2
2072 , resultout OUT NOCOPY VARCHAR2
2073 )
2074 IS
2075 l_InvitorResourceID NUMBER;
2076 l_InvitorWFRole VARCHAR2(30);
2077 l_InvitorWFName VARCHAR2(80);
2078 l_InvitorEmpNumber NUMBER;
2079
2080 l_InviteeResourceID NUMBER;
2081 l_InviteeWFRole VARCHAR2(30);
2082 l_InviteeWFName VARCHAR2(80);
2083
2084 l_InviteeCode VARCHAR2(30);
2085
2086 CURSOR c_EmployeeInfo
2087 /*****************************************************************************
2088 ** Get the employee number of the Resource
2089 *****************************************************************************/
2090 ( b_ResourceID NUMBER
2091 )IS SELECT source_id EmployeeNumber
2092 FROM JTF_RS_RESOURCE_EXTNS
2093 WHERE resource_id = b_ResourceID;
2094
2095 BEGIN
2096
2097 IF (funcmode = 'RUN')
2098 THEN
2099 /***************************************************************************
2100 ** 'RUN' function from WF
2101 ***************************************************************************/
2102
2103 /***************************************************************************
2104 ** Pick up the resource ID of the INVITOR
2105 ***************************************************************************/
2106 l_InvitorResourceID := wf_engine.GetItemAttrNumber
2107 ( itemtype => itemtype
2108 , itemkey => itemkey
2109 , aname => 'INVITOR'
2110 );
2111
2112 /***************************************************************************
2113 ** Pick up the resource ID of the INVITEE
2114 ***************************************************************************/
2115 l_InviteeResourceID := wf_engine.GetItemAttrNumber
2116 ( itemtype => itemtype
2117 , itemkey => itemkey
2118 , aname => 'INVITEE'
2119 );
2120
2121 ---Enh # 3443999,amigupta Getting attribute value for INVITEE_CODE and passing to WF_ROLE
2122
2123 /***************************************************************************
2124 ** Pick up the resource Type Code of the INVITEE
2125 ***************************************************************************/
2126 l_InviteeCode := wf_engine.GetItemAttrText
2127 ( itemtype => itemtype
2128 , itemkey => itemkey
2129 , aname => 'INVITEE_CODE'
2130 );
2131
2132 /***************************************************************************
2133 ** Determine the WF Role and WF Name for the Invitor
2134 ***************************************************************************/
2135
2136 WF_Role( l_InvitorResourceID
2137 , l_InviteeCode -- Not used, for future enhancements
2138 , l_InvitorWFRole
2139 , l_InvitorWFName
2140 , l_InvitorEmpNumber
2141 );
2142
2143 /***************************************************************************
2144 ** If the invitor doesn't exist in the WF directory we can't send a
2145 ** notification to invitor
2146 ***************************************************************************/
2147 IF ((l_InvitorWFRole IS NULL) OR
2148 (l_InvitorWFName IS NULL)
2149 )
2150 THEN
2151 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2152 ELSE
2153 /*************************************************************************
2154 ** Found INVITOR, set WF Attributes
2155 *************************************************************************/
2156 WF_ENGINE.SetItemAttrText( itemtype => itemtype
2157 , itemkey => itemkey
2158 , aname => 'WF_INVITOR_ROLE'
2159 , avalue => l_InvitorWFRole
2160 );
2161
2162 WF_ENGINE.SetItemAttrText( itemtype => itemtype
2163 , itemkey => itemkey
2164 , aname => 'WF_INVITOR_NAME'
2165 , avalue => l_InvitorWFName
2166 );
2167
2168 /*************************************************************************
2169 ** Determine the WF Role and WF Name for the Invitee
2170 *************************************************************************/
2171 WF_Role( l_InviteeResourceID
2172 , l_InviteeCode -- Not used, for future enhancements
2173 , l_InviteeWFRole
2174 , l_InviteeWFName
2175 , l_InvitorEmpNumber
2176 );
2177
2178 IF ((l_InviteeWFRole IS NULL) OR
2179 (l_InviteeWFName IS NULL)
2180 )
2181 THEN
2182 /***********************************************************************
2183 ** If the invitee doesn't exist in the WF directory we send a warning
2184 ** notification to the invitor
2185 ***********************************************************************/
2186 resultout := 'COMPLETE:WARNING';
2187 ELSE
2188 /***********************************************************************
2189 ** Found INVITEE, set WF Attributes
2190 ***********************************************************************/
2191 WF_ENGINE.SetItemAttrText( itemtype => itemtype
2192 , itemkey => itemkey
2193 , aname => 'WF_INVITEE_ROLE'
2194 , avalue => l_InviteeWFRole
2195 );
2196
2197 WF_ENGINE.SetItemAttrText( itemtype => itemtype
2198 , itemkey => itemkey
2199 , aname => 'WF_INVITEE_NAME'
2200 , avalue => l_InviteeWFName
2201 );
2202 resultout := 'COMPLETE:NO_ERROR';
2203 END IF;
2204
2205 END IF;
2206
2207 ELSIF (funcmode = 'CANCEL')
2208 THEN
2209 /***************************************************************************
2210 ** 'CANCEL' function from WF
2211 ***************************************************************************/
2212 resultout := 'COMPLETE:NO_ERROR';
2213 ELSIF (funcmode = 'TIMEOUT')
2214 THEN
2215 /***************************************************************************
2216 ** 'TIMEOUT' function from WF
2217 ***************************************************************************/
2218 resultout := 'COMPLETE:NO_ERROR';
2219 ELSE
2220 /***************************************************************************
2221 ** Unknown function from WF - raise error
2222 ***************************************************************************/
2223 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2224 END IF;
2225
2226 EXCEPTION
2227 WHEN OTHERS
2228 THEN
2229 /*****************************************************************************
2230 ** Something went wrong return 'ERROR' and set the ERROR_MESSAGE
2231 *****************************************************************************/
2232 wf_engine.SetItemAttrText( itemtype => itemtype
2233 , itemkey => itemkey
2234 , aname => 'ERROR_MESSAGE'
2235 , avalue => 'CAC_VIEW_WF_PVT.DetermineWFRole(): ' || to_char(SQLCODE)||':'||SQLERRM
2236 );
2237
2238 resultout := 'COMPLETE:ERROR';
2239
2240
2241 END DetermineWFRole;
2242
2243
2244 PROCEDURE StartReminders
2245 /*******************************************************************************
2246 ** Start of comments
2247 ** Procedure : StartReminder
2248 ** Description : Given the task ID of the appointment (p_TaskID) and the
2249 ** Resource ID of the invitor (p_INVITOR) this procedure will
2250 ** start WF that will initiate the sending of reminders when the
2251 ** time has come..
2252 ** Parameters :
2253 ** name direction type required?
2254 ** ---- --------- ---- ---------
2255 ** p_api_version IN NUMBER required
2256 ** p_init_msg_list IN VARCHAR2 optional
2257 ** p_commit IN VARCHAR2 optional
2258 ** x_return_status OUT VARCHAR2 optional
2259 ** x_msg_count OUT NUMBER required
2260 ** x_msg_data OUT VARCHAR2 required
2261 ** p_INVITOR IN NUMBER required
2262 ** p_TaskID IN NUMBER required
2263 ** Notes :
2264 **
2265 ** End of comments
2266 *******************************************************************************/
2267 ( p_api_version IN NUMBER
2268 , p_init_msg_list IN VARCHAR2
2269 , p_commit IN VARCHAR2
2270 , x_return_status OUT NOCOPY VARCHAR2
2271 , x_msg_count OUT NOCOPY NUMBER
2272 , x_msg_data OUT NOCOPY VARCHAR2
2273 , p_INVITOR IN NUMBER -- Resource ID of Invitor
2274 , p_TaskID IN NUMBER -- Task ID of the appointment
2275 , p_RemindDate IN DATE -- Date/Time the reminder needs to be send
2276 )
2277 IS
2278 l_api_name CONSTANT VARCHAR2(30) := 'StartReminders';
2279 l_api_version CONSTANT NUMBER := 1.0;
2280 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
2281
2282 l_ItemType VARCHAR2(8) := 'JTFTKRDR';
2283 l_ItemKey VARCHAR2(100);
2284 l_AdminWFRole VARCHAR2(30);
2285
2286 BEGIN
2287 /*****************************************************************************
2288 ** Standard call to check for call compatibility
2289 *****************************************************************************/
2290 IF NOT FND_API.Compatible_API_Call( l_api_version
2291 , p_api_version
2292 , l_api_name
2293 , G_PKG_NAME
2294 )
2295 THEN
2296 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2297 END IF;
2298
2299 /*****************************************************************************
2300 ** Initialize message list if p_init_msg_list is set to TRUE
2301 *****************************************************************************/
2302 IF FND_API.To_Boolean(p_init_msg_list)
2303 THEN
2304 FND_MSG_PUB.Initialize;
2305 END IF;
2306
2307 /*****************************************************************************
2308 ** Initialize API return status to success
2309 *****************************************************************************/
2310 x_return_status := FND_API.G_RET_STS_SUCCESS;
2311
2312 /*****************************************************************************
2313 ** Look up the Calendar Administrator from the Profile (this should be an
2314 ** existing WF_ROLE)
2315 *****************************************************************************/
2316 l_AdminWFRole := FND_PROFILE.Value(name => 'JTF_CALENDAR_ADMINISTRATOR');
2317
2318 /***************************************************************************
2319 ** we need an itemkey
2320 ***************************************************************************/
2321 SELECT to_char(cac_vws_itemkey_s.NEXTVAL) INTO l_ItemKey
2322 FROM DUAL;
2323 /***************************************************************************
2324 ** Initialize the workflow
2325 ***************************************************************************/
2326 wf_engine.CreateProcess( itemtype => l_ItemType
2327 , itemkey => l_ItemKey
2328 , process => 'DELAYSTARTREMINDER'
2329 );
2330
2331 wf_engine.SetItemAttrNumber( itemtype => l_itemtype
2332 , itemkey => l_ItemKey
2333 , aname => 'TASK_ID'
2334 , avalue => p_TaskID
2335 );
2336
2337 wf_engine.SetItemAttrDate( itemtype => l_itemtype
2338 , itemkey => l_ItemKey
2339 , aname => 'REMIND_DATE'
2340 , avalue => p_RemindDate
2341 );
2342
2343 wf_engine.SetItemAttrText( itemtype => l_itemtype
2344 , itemkey => l_ItemKey
2345 , aname => 'CALENDAR_ADMIN'
2346 , avalue => l_AdminWFRole
2347 );
2348
2349 wf_engine.SetItemAttrNumber( itemtype => l_itemtype
2350 , itemkey => l_ItemKey
2351 , aname => 'INVITOR'
2352 , avalue => p_INVITOR
2353 );
2354
2355 /***************************************************************************
2356 ** Start the workflow
2357 ***************************************************************************/
2358 wf_engine.StartProcess( itemtype => l_itemtype
2359 , itemkey => l_ItemKey
2360 );
2361
2362 /***************************************************************************
2363 ** Save the workflow itemtype and item key so we can update the reminder WF
2364 ** if the start date or remind me settings change
2365 ***************************************************************************/
2366
2367 UPDATE jtf_task_all_assignments
2368 SET reminder_wf_item_type = l_itemtype
2369 , reminder_wf_item_key = l_ItemKey
2370 WHERE task_id = p_TaskID;
2371
2372 /***************************************************************************
2373 ** Standard check of p_commit (WF won't start until commited)
2374 ***************************************************************************/
2375 IF FND_API.To_Boolean(p_commit)
2376 THEN
2377 COMMIT WORK;
2378 END IF;
2379
2380 /*****************************************************************************
2381 ** Standard call to get message count and if count is > 1, get message info
2382 *****************************************************************************/
2383 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
2384 , p_data => x_msg_data
2385 );
2386 EXCEPTION
2387 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
2388 THEN
2389 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2390 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
2391 , p_data => x_msg_data
2392 );
2393 WHEN OTHERS
2394 THEN
2395 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2396 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2397 THEN
2398 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
2399 , l_api_name
2400 );
2401 END IF;
2402 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
2403 , p_data => x_msg_data
2404 );
2405
2406 END StartReminders;
2407
2408
2409 PROCEDURE SendReminders
2410 /*******************************************************************************
2411 ** Start of comments
2412 ** Procedure : SendReminders
2413 ** Description :
2414 ** Parameters :
2415 ** name direction type required?
2416 ** ---- --------- ---- ---------
2417 ** itemtype IN VARCHAR2 required
2418 ** itemkey IN VARCHAR2 required
2419 ** actid IN NUMBER required
2420 ** funcmode IN VARCHAR2 required
2421 ** resultout OUT VARCHAR2 required
2422 **
2423 ** Notes :
2424 **
2425 ** End of comments
2426 ******************************************************************************/
2427 ( itemtype IN VARCHAR2
2428 , itemkey IN VARCHAR2
2429 , actid IN NUMBER
2430 , funcmode IN VARCHAR2
2431 , resultout OUT NOCOPY VARCHAR2
2432 )
2433 IS
2434 l_api_name CONSTANT VARCHAR2(30) := 'StartReminderWF';
2435 l_api_version CONSTANT NUMBER := 1.0;
2436 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
2437
2438 l_ItemType VARCHAR2(8) := 'JTFTKRDR';
2439 l_AdminWFRole VARCHAR2(30);
2440 l_StartDateCorrected DATE;
2441 l_EndDateCorrected DATE;
2442 l_user_id NUMBER;
2443 l_DestTimezoneID NUMBER;
2444 l_SourceTimezoneID NUMBER;
2445
2446 CURSOR c_Task
2447 /*****************************************************************************
2448 ** Cursor to pick up all the invitees for the appointment. Also picks up
2449 ** a itemkey from the sequence, this is needed to start the workflow
2450 *****************************************************************************/
2451 (b_TaskID NUMBER
2452 )IS SELECT jta.task_assignment_id TaskAssignmentID
2453 , to_char(cac_vws_itemkey_s.NEXTVAL) ItemKey
2454 , jta.resource_id INVITEE
2455 , jta.resource_type_code INVITEE_CODE
2456 , jtl.task_name TASK_NAME
2457 , jtl.description TASK_DESCRIPTION
2458 , jtb.calendar_start_date START_DATE
2459 , jtb.calendar_end_date END_DATE
2460 , NVL( jtb.timezone_id
2461 ,(NVL( FND_PROFILE.Value('SERVER_TIMEZONE_ID')
2462 , 4)
2463 )
2464 ) SourceTimezoneID
2465 FROM jtf_tasks_b jtb
2466 , jtf_tasks_tl jtl
2467 , jtf_task_all_assignments jta
2468 WHERE jtb.task_id = jtl.task_id
2469 AND jtl.language = userenv('LANG')
2470 AND jta.task_id = jtb.task_id
2471 AND jta.show_on_calendar = 'Y'
2472 AND jta.assignment_status_id <> 4
2473 AND jtb.task_id = b_TaskID;
2474
2475 l_TaskID NUMBER;
2476 l_INVITOR NUMBER;
2477
2478 BEGIN
2479 /*****************************************************************************
2480 ** Retrieve the globals that where set
2481 *****************************************************************************/
2482 l_AdminWFRole := wf_engine.GetItemAttrNumber( itemtype => itemtype
2483 , itemkey => itemkey
2484 , aname => 'CALENDAR_ADMIN'
2485 );
2486
2487 l_TaskID := wf_engine.GetItemAttrNumber( itemtype => itemtype
2488 , itemkey => itemkey
2489 , aname => 'TASK_ID'
2490 );
2491
2492 l_INVITOR:= wf_engine.GetItemAttrNumber( itemtype => itemtype
2493 , itemkey => itemKey
2494 , aname => 'INVITOR'
2495 );
2496
2497 /*****************************************************************************
2498 ** Get Appointment details for every invitee and start a notification WF for
2499 ** all of them.
2500 *****************************************************************************/
2501 FOR r_Task IN c_Task(l_TaskID)
2502 LOOP <<ASSIGNEES>>
2503 /***************************************************************************
2504 ** Initialize the workflow
2505 ***************************************************************************/
2506 wf_engine.CreateProcess( itemtype => l_ItemType
2507 , itemkey => r_Task.ItemKey
2508 , process => 'JTF_TASK_REMINDER'
2509 );
2510
2511 wf_engine.SetItemAttrText( itemtype => l_itemtype
2512 , itemkey => r_Task.ItemKey
2513 , aname => 'CALENDAR_ADMIN'
2514 , avalue => l_AdminWFRole
2515 );
2516
2517 wf_engine.SetItemAttrNumber( itemtype => l_itemtype
2518 , itemkey => r_Task.ItemKey
2519 , aname => 'INVITEE'
2520 , avalue => r_Task.INVITEE
2521 );
2522 ---Bug # 4089393, amigupta, Setting new attribute value INVITEE_CODE
2523
2524 wf_engine.SetItemAttrText( itemtype => l_itemtype
2525 , itemkey => r_Task.ItemKey
2526 , aname => 'INVITEE_CODE'
2527 , avalue => r_Task.INVITEE_CODE
2528 );
2529
2530
2531 wf_engine.SetItemAttrNumber( itemtype => l_itemtype
2532 , itemkey => r_Task.ItemKey
2533 , aname => 'INVITOR'
2534 , avalue => l_INVITOR
2535 );
2536
2537 wf_engine.SetItemAttrNumber( itemtype => l_itemtype
2538 , itemkey => r_Task.ItemKey
2539 , aname => 'TASK_ID'
2540 , avalue => l_TaskID
2541 );
2542
2543 wf_engine.SetItemAttrText( itemtype => l_itemtype
2544 , itemkey => r_Task.ItemKey
2545 , aname => 'TASK_NAME'
2546 , avalue => r_Task.TASK_NAME
2547 );
2548
2549 wf_engine.SetItemAttrText( itemtype => l_itemtype
2550 , itemkey => r_Task.ItemKey
2551 , aname => 'TASK_DESCRIPTION'
2552 , avalue => r_Task.TASK_DESCRIPTION
2553 );
2554
2555 --
2556 -- Start and end date/time will have to be adjusted for the recipients timezone
2557 --
2558
2559 --
2560 -- I need to get the user_id for the INVITEE
2561 --
2562 l_user_id := JTF_CAL_UTILITY_PVT.GetUserID(r_Task.INVITEE);
2563 l_DestTimezoneID := NVL(FND_PROFILE.Value_Specific( name => 'CLIENT_TIMEZONE_ID'
2564 , user_id => l_user_id
2565 )
2566 ,4 -- If not set on any level, default to PST
2567 );
2568
2569 --
2570 -- Adjust startdate from Task/Server timezone to recipient client timezone
2571 --
2572 JTF_CAL_UTILITY_PVT.AdjustForTimezone
2573 ( p_source_tz_id => r_Task.SourceTimezoneID
2574 , p_dest_tz_id => l_DestTimezoneID
2575 , p_source_day_time => r_Task.START_DATE
2576 , x_dest_day_time => l_StartDateCorrected
2577 );
2578
2579 --
2580 -- Adjust enddate from Task/Server timezone to recipient client timezone
2581 --
2582 JTF_CAL_UTILITY_PVT.AdjustForTimezone
2583 ( p_source_tz_id => r_Task.SourceTimezoneID
2584 , p_dest_tz_id => l_DestTimezoneID
2585 , p_source_day_time => r_Task.END_DATE
2586 , x_dest_day_time => l_EndDateCorrected
2587 );
2588
2589
2590 wf_engine.SetItemAttrDate( itemtype => l_itemtype
2591 , itemkey => r_Task.ItemKey
2592 , aname => 'START_DATE'
2593 , avalue => l_StartDateCorrected
2594 );
2595
2596 wf_engine.SetItemAttrDate( itemtype => l_itemtype
2597 , itemkey => r_Task.ItemKey
2598 , aname => 'END_DATE'
2599 , avalue => l_EndDateCorrected
2600 );
2601
2602 /***************************************************************************
2603 ** Start the workflow
2604 ***************************************************************************/
2605 wf_engine.StartProcess( itemtype => l_itemtype
2606 , itemkey => r_Task.ItemKey
2607 );
2608
2609 END LOOP ASSIGNEES;
2610
2611 /*****************************************************************************
2612 ** All went well return 'NO_ERROR'
2613 *****************************************************************************/
2614 resultout := 'COMPLETE:NO_ERROR';
2615
2616 EXCEPTION
2617 WHEN OTHERS
2618 THEN
2619 IF (c_Task%ISOPEN)
2620 THEN
2621 CLOSE c_Task;
2622 END IF;
2623 /*****************************************************************************
2624 ** Something went wrong return 'ERROR' and set the ERROR_MESSAGE
2625 *****************************************************************************/
2626 wf_engine.SetItemAttrText( itemtype => itemtype
2627 , itemkey => itemkey
2628 , aname => 'ERROR_MESSAGE'
2629 , avalue => 'CAC_VIEW_WF_PVT.SendReminders(): ' || to_char(SQLCODE)||':'||SQLERRM
2630 );
2631
2632 resultout := 'COMPLETE:ERROR';
2633
2634 END SendReminders;
2635
2636
2637
2638
2639
2640 PROCEDURE UpdateReminders
2641 /*******************************************************************************
2642 ** Start of comments
2643 ** Procedure : UpdateReminders
2644 ** Description : Given the task ID and a new reminder date this procedure will
2645 ** update all the reminders for the appointment, should only be
2646 ** called if the reminder me or start date has changed
2647 ** Parameters :
2648 ** name direction type required?
2649 ** ---- --------- ---- ---------
2650 ** p_api_version IN NUMBER required
2651 ** p_init_msg_list IN VARCHAR2 optional
2652 ** p_commit IN VARCHAR2 optional
2653 ** x_return_status OUT VARCHAR2 optional
2654 ** x_msg_count OUT NUMBER required
2655 ** x_msg_data OUT VARCHAR2 required
2656 ** p_TaskID IN NUMBER required
2657 ** p_RemindDate IN DATE required
2658 ** Notes :
2659 ** 1) If an invitee does not exist in the WF directory a notification will
2660 ** be send to the invitor saying that the invitation was not send.
2661 ** 2) Currently invitations are only send to employees
2662 ** 3) The WFs won't be started until a commmit is done.
2663 **
2664 ** End of comments
2665 *******************************************************************************/
2666 ( p_api_version IN NUMBER
2667 , p_init_msg_list IN VARCHAR2
2668 , p_commit IN VARCHAR2
2669 , x_return_status OUT NOCOPY VARCHAR2
2670 , x_msg_count OUT NOCOPY NUMBER
2671 , x_msg_data OUT NOCOPY VARCHAR2
2672 , p_TaskID IN NUMBER -- Task ID of the appointment
2673 , p_RemindDate IN DATE -- NEW Date/Time the reminder needs to be send
2674 )
2675 IS
2676 l_api_name CONSTANT VARCHAR2(30) := 'UpdateReminderWF';
2677 l_api_version CONSTANT NUMBER := 1.0;
2678 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
2679
2680 l_ItemType VARCHAR2(8) := 'JTFTKRDR';
2681 l_AdminWFRole VARCHAR2(30);
2682
2683 CURSOR c_Task
2684 /*****************************************************************************
2685 ** Cursor to pick up all the invitees for the appointment. Also picks up
2686 ** a itemkey from the sequence, this is needed to start the workflow
2687 *****************************************************************************/
2688 (b_TaskID NUMBER
2689 )IS SELECT jta.task_assignment_id TaskassignmentID
2690 , jta.reminder_wf_item_type ReminderWFItemType
2691 , jta.reminder_wf_item_key ReminderWFItemKey
2692 , jta.resource_id INVITEE
2693 , jtb.owner_id INVITOR
2694 , jtl.task_name TASK_NAME
2695 , jtl.description TASK_DESCRIPTION
2696 , jtb.calendar_start_date START_DATE
2697 , jtb.calendar_end_date END_DATE
2698 FROM jtf_tasks_b jtb
2699 , jtf_tasks_tl jtl
2700 , jtf_task_all_assignments jta
2701 WHERE jtb.task_id = jtl.task_id
2702 AND jtl.language = userenv('LANG')
2703 AND jta.task_id = jtb.task_id
2704 AND jta.show_on_calendar = 'Y'
2705 AND jta.assignee_role = 'OWNER'
2706 AND jtb.task_id = b_TaskID;
2707
2708 l_return_status VARCHAR2(1);
2709 l_msg_count NUMBER;
2710 l_msg_data VARCHAR2(2000);
2711 l_status VARCHAR2(8);
2712 l_result VARCHAR2(30);
2713
2714
2715
2716 BEGIN
2717 /*****************************************************************************
2718 ** Standard call to check for call compatibility
2719 *****************************************************************************/
2720 IF NOT FND_API.Compatible_API_Call( l_api_version
2721 , p_api_version
2722 , l_api_name
2723 , G_PKG_NAME
2724 )
2725 THEN
2726 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2727 END IF;
2728
2729 /*****************************************************************************
2730 ** Initialize message list if p_init_msg_list is set to TRUE
2731 *****************************************************************************/
2732 IF FND_API.To_Boolean(p_init_msg_list)
2733 THEN
2734 FND_MSG_PUB.Initialize;
2735 END IF;
2736
2737 /*****************************************************************************
2738 ** Initialize API return status to success
2739 *****************************************************************************/
2740 x_return_status := FND_API.G_RET_STS_SUCCESS;
2741
2742 /*****************************************************************************
2743 ** Get Appointment details for every invitee and start a notification WF for
2744 ** all of them.
2745 *****************************************************************************/
2746 FOR r_Task IN c_Task(p_TaskID)
2747 LOOP <<ASSIGNEES>>
2748 /***************************************************************************
2749 ** Initialize the workflow
2750 ***************************************************************************/
2751 IF ( (r_Task.ReminderWFItemType IS NOT NULL)
2752 AND (r_Task.ReminderWFItemKey IS NOT NULL)
2753 )
2754 THEN
2755 -- Check if the process is stil active
2756 WF_ITEM_ACTIVITY_STATUS.Root_Status( itemtype => r_Task.ReminderWFItemType
2757 , itemkey => r_Task.ReminderWFItemKey
2758 , status => l_status
2759 , result => l_result
2760 );
2761 IF (l_status = 'ACTIVE')
2762 THEN
2763 -- abort the existing process
2764 wf_engine.AbortProcess( itemtype => r_Task.ReminderWFItemType
2765 , itemkey => r_Task.ReminderWFItemKey
2766 , process => 'DELAYSTARTREMINDER'
2767 , result => 'COMPLETE'
2768 );
2769 END IF;
2770 END IF;
2771 --Check if this is an update to Do Not Remind me
2772 IF p_RemindDate IS NOT NULL THEN
2773 StartReminders( p_api_version => 1.0
2774 , p_init_msg_list => fnd_api.g_false
2775 , p_commit => fnd_api.g_true
2776 , x_return_status => l_return_status
2777 , x_msg_count => l_msg_count
2778 , x_msg_data => l_msg_data
2779 , p_INVITOR => r_Task.INVITOR
2780 , p_TaskID => p_TaskID
2781 , p_RemindDate => p_RemindDate
2782 );
2783 END IF;
2784
2785 /***************************************************************************
2786 ** Standard check of p_commit (WF won't start until commited)
2787 ***************************************************************************/
2788 IF FND_API.To_Boolean(p_commit)
2789 THEN
2790 COMMIT WORK;
2791 END IF;
2792
2793 /***************************************************************************
2794 ** Save the workflow itemtype and item key so we can update the reminder WF
2795 ** if the start date or remind me settings change
2796 ***************************************************************************/
2797
2798 UPDATE jtf_task_all_assignments
2799 SET reminder_wf_item_type = r_Task.ReminderWFItemType
2800 , reminder_wf_item_key = r_Task.ReminderWFItemKey
2801 WHERE task_id = p_TaskID;
2802
2803 END LOOP ASSIGNEES;
2804
2805 /*****************************************************************************
2806 ** Standard call to get message count and if count is > 1, get message info
2807 *****************************************************************************/
2808 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
2809 , p_data => x_msg_data
2810 );
2811 EXCEPTION
2812 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
2813 THEN
2814 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2815 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
2816 , p_data => x_msg_data
2817 );
2818 WHEN OTHERS
2819 THEN
2820 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2821 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2822 THEN
2823 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
2824 , l_api_name
2825 );
2826 END IF;
2827 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
2828 , p_data => x_msg_data
2829 );
2830
2831 END UpdateReminders;
2832
2833 /*
2834 amigupta, Enh # 3880081 STARTS HERE
2835 */
2836
2837 PROCEDURE UpdateAttendee
2838 /*******************************************************************************
2839 ** Start of comments
2840 ** Procedure : UpdateReminders
2841 ** Description : Given the task ID and a new reminder date this procedure will
2842 ** update all the reminders for the appointment, should only be
2843 ** called if the reminder me or start date has changed
2844 ** Parameters :
2845 ** name direction type required?
2846 ** ---- --------- ---- ---------
2847 ** p_api_version IN NUMBER required
2848 ** p_init_msg_list IN VARCHAR2 optional
2849 ** p_commit IN VARCHAR2 optional
2850 ** x_return_status OUT VARCHAR2 optional
2851 ** x_msg_count OUT NUMBER required
2852 ** x_msg_data OUT VARCHAR2 required
2853 ** p_TaskID IN NUMBER required
2854 ** p_RemindDate IN DATE required
2855 ** Notes :
2856 ** 1) If an invitee does not exist in the WF directory a notification will
2857 ** be send to the invitor saying that the invitation was not send.
2858 ** 2) Currently invitations are only send to employees
2859 ** 3) The WFs won't be started until a commmit is done.
2860 **
2861 ** End of comments
2862 *******************************************************************************/
2863 ( p_api_version IN NUMBER
2864 , p_init_msg_list IN VARCHAR2
2865 , p_commit IN VARCHAR2
2866 , x_return_status OUT NOCOPY VARCHAR2
2867 , x_msg_count OUT NOCOPY NUMBER
2868 , x_msg_data OUT NOCOPY VARCHAR2
2869 , p_INVITEE IN NUMBER -- Resource ID of Invitee
2870 , p_INVITEE_TYPE IN VARCHAR2 --Resource Type of the INVITEE
2871 , p_TaskID IN NUMBER -- Task ID of the appointment
2872 )
2873 IS
2874 l_api_name CONSTANT VARCHAR2(30) := 'UpdateAttendeeWF';
2875 l_api_version CONSTANT NUMBER := 1.0;
2876 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
2877
2878 l_ItemType VARCHAR2(8) := 'CACVWSWF';
2879 l_task_type_name VARCHAR2(30);
2880 l_task_priority_name VARCHAR2(30);
2881 l_timezone VARCHAR2(80);
2882
2883 l_AdminWFRole VARCHAR2(30);
2884 l_AdminWFName VARCHAR2(30);
2885 l_AdminEmpNumber NUMBER;
2886
2887 CURSOR c_invitor
2888 /****************************************************************************
2889 ** Pick up appointment owner
2890 ****************************************************************************/
2891 (p_task_id NUMBER)
2892 IS
2893 SELECT tsk_vl.task_id, owner_id,owner_type_code
2894 FROM jtf_tasks_vl tsk_vl,
2895 jtf_task_all_assignments tsk_asg
2896 WHERE tsk_vl.task_id = p_task_id
2897 and tsk_vl.task_id = tsk_asg.task_id
2898 AND assignee_role = 'OWNER' ;
2899
2900 CURSOR c_Task
2901 /*****************************************************************************
2902 ** Cursor to pick up all the invitees for the appointment. Also picks up
2903 ** a itemkey from the sequence, this is needed to start the workflow
2904 *****************************************************************************/
2905 (b_TaskID NUMBER, b_INVITEE NUMBER, b_INVITEE_TYPE VARCHAR2
2906 )IS SELECT to_char(cac_vws_itemkey_s.NEXTVAL) ItemKey
2907 , jta.task_assignment_id TASK_ASSIGNMENT_ID
2908 , jta.resource_id INVITEE
2909 , jta.resource_type_code INVITEE_CODE
2910 , jtl.task_name TASK_NAME
2911 , jtl.description TASK_DESCRIPTION
2912 , jtb.calendar_start_date START_DATE
2913 , jtb.task_type_id TYPE_ID
2914 , jtb.task_priority_id PRIORITY_ID
2915 , (jtb.calendar_end_date - jtb.calendar_start_date)*24*60 DURATION
2916 , jtb.timezone_id TIMEZONE_ID
2917 FROM jtf_tasks_b jtb
2918 , jtf_tasks_tl jtl
2919 , jtf_task_all_assignments jta
2920 WHERE jtb.task_id = jtl.task_id
2921 AND jtl.language = userenv('LANG')
2922 AND jta.task_id = jtb.task_id
2923 AND jta.show_on_calendar = 'Y'
2924 AND jta.assignee_role = 'ASSIGNEE'
2925 AND jtb.task_id = b_TaskID
2926 AND jta.resource_id = b_INVITEE
2927 AND jta.resource_type_code = b_INVITEE_TYPE ;
2928
2929 l_return_status VARCHAR2(1);
2930 l_msg_count NUMBER;
2931 l_msg_data VARCHAR2(2000);
2932 l_status VARCHAR2(8);
2933 l_result VARCHAR2(30);
2934 l_invitor c_invitor%ROWTYPE;
2935
2936
2937
2938 BEGIN
2939
2940 /*****************************************************************************
2941 ** Standard call to check for call compatibility
2942 *****************************************************************************/
2943 IF NOT FND_API.Compatible_API_Call( l_api_version
2944 , p_api_version
2945 , l_api_name
2946 , G_PKG_NAME
2947 )
2948 THEN
2949 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2950 END IF;
2951
2952 /*****************************************************************************
2953 ** Initialize message list if p_init_msg_list is set to TRUE
2954 *****************************************************************************/
2955 IF FND_API.To_Boolean(p_init_msg_list)
2956 THEN
2957 FND_MSG_PUB.Initialize;
2958 END IF;
2959
2960 /*****************************************************************************
2961 ** Initialize API return status to success
2962 *****************************************************************************/
2963 x_return_status := FND_API.G_RET_STS_SUCCESS;
2964
2965 /*****************************************************************************
2966 ** Get Appointment details for every invitee and start a notification WF for
2967 ** all of them.
2968 *****************************************************************************/
2969 OPEN c_invitor (p_TaskID);
2970 FETCH c_invitor INTO l_invitor;
2971 IF c_invitor%NOTFOUND THEN
2972 CLOSE c_invitor;
2973 RAISE fnd_api.g_exc_unexpected_error;
2974 END IF;
2975 CLOSE c_invitor;
2976
2977 FOR r_Task IN c_Task(p_TaskID, p_INVITEE, p_INVITEE_TYPE)
2978 LOOP <<ASSIGNEES>>
2979
2980 WF_Role( l_invitor.owner_id
2981 , l_invitor.owner_type_code
2982 , l_AdminWFRole
2983 , l_AdminWFName
2984 , l_AdminEmpNumber
2985 );
2986
2987
2988 l_task_type_name := get_type_name(r_Task.TYPE_ID);
2989 l_task_priority_name := get_priority_name(r_Task.PRIORITY_ID);
2990 l_timezone := GetTimezone(p_timezone_id => r_Task.timezone_id);
2991
2992 wf_engine.CreateProcess( itemtype => l_ItemType
2993 , itemkey => r_Task.ItemKey
2994 , process => 'UPDATE_INVITATION'
2995 );
2996
2997 wf_engine.SetItemAttrText( itemtype => l_itemtype
2998 , itemkey => r_Task.ItemKey
2999 , aname => 'CALENDAR_ADMIN'
3000 , avalue => l_AdminWFRole
3001 );
3002
3003 wf_engine.SetItemAttrText( itemtype => l_itemtype
3004 , itemkey => r_Task.ItemKey
3005 , aname => 'INVITOR'
3006 , avalue => l_invitor.owner_id
3007 );
3008
3009 wf_engine.SetItemAttrNumber( itemtype => l_itemtype
3010 , itemkey => r_Task.ItemKey
3011 , aname => 'INVITEE'
3012 , avalue => r_Task.INVITEE
3013 );
3014
3015 wf_engine.SetItemAttrText( itemtype => l_itemtype
3016 , itemkey => r_Task.ItemKey
3017 , aname => 'INVITEE_CODE'
3018 , avalue => r_Task.INVITEE_CODE
3019 );
3020
3021 wf_engine.SetItemAttrNumber( itemtype => l_itemtype
3022 , itemkey => r_Task.ItemKey
3023 , aname => 'TASK_ASSIGNMENT_ID'
3024 , avalue => r_Task.TASK_ASSIGNMENT_ID
3025 );
3026
3027 wf_engine.SetItemAttrNumber( itemtype => l_itemtype
3028 , itemkey => r_Task.ItemKey
3029 , aname => 'TASK_ID'
3030 , avalue => p_TaskID
3031 );
3032
3033 wf_engine.SetItemAttrText( itemtype => l_itemtype
3034 , itemkey => r_Task.ItemKey
3035 , aname => 'TASK_NAME'
3036 , avalue => r_Task.TASK_NAME
3037 );
3038
3039 wf_engine.SetItemAttrText( itemtype => l_itemtype
3040 , itemkey => r_Task.ItemKey
3041 , aname => 'TASK_DESCRIPTION'
3042 , avalue => r_Task.TASK_DESCRIPTION
3043 );
3044
3045 wf_engine.SetItemAttrDate( itemtype => l_itemtype
3046 , itemkey => r_Task.ItemKey
3047 , aname => 'START_DATE'
3048 , avalue => r_Task.START_DATE
3049 );
3050
3051 wf_engine.SetItemAttrText( itemtype => l_itemtype
3052 , itemkey => r_Task.ItemKey
3053 , aname => 'TYPE'
3054 , avalue => l_task_type_name
3055 );
3056 wf_engine.SetItemAttrText( itemtype => l_itemtype
3057 , itemkey => r_Task.ItemKey
3058 , aname => 'PRIORITY'
3059 , avalue => l_task_priority_name
3060 );
3061 wf_engine.SetItemAttrText( itemtype => l_itemtype
3062 , itemkey => r_Task.ItemKey
3063 , aname => 'DURATION'
3064 , avalue => get_duration(r_Task.DURATION)
3065 );
3066 wf_engine.SetItemAttrText( itemtype => l_itemtype
3067 , itemkey => r_Task.ItemKey
3068 , aname => 'TIMEZONE'
3069 , avalue => l_timezone
3070 );
3071
3072 /***************************************************************************
3073 ** Start the workflow
3074 ***************************************************************************/
3075
3076 wf_engine.StartProcess( itemtype => l_itemtype
3077 , itemkey => r_Task.ItemKey
3078 );
3079
3080
3081 /***************************************************************************
3082 ** Standard check of p_commit (WF won't start until commited)
3083 ***************************************************************************/
3084
3085
3086 IF FND_API.To_Boolean(p_commit)
3087 THEN
3088 COMMIT WORK;
3089 END IF;
3090 /***************************************************************************
3091 ** Standard check of p_commit (WF won't start until commited)
3092 ***************************************************************************/
3093 IF FND_API.To_Boolean(p_commit)
3094 THEN
3095 COMMIT WORK;
3096 END IF;
3097
3098 END LOOP ASSIGNEES;
3099
3100 /*****************************************************************************
3101 ** Standard call to get message count and if count is > 1, get message info
3102 *****************************************************************************/
3103 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
3104 , p_data => x_msg_data
3105 );
3106 EXCEPTION
3107 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
3108 THEN
3109
3110 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3111 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
3112 , p_data => x_msg_data
3113 );
3114 WHEN OTHERS
3115 THEN
3116
3117 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3118 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3119 THEN
3120 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
3121 , l_api_name
3122 );
3123 END IF;
3124 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
3125 , p_data => x_msg_data
3126 );
3127
3128 END UpdateAttendee;
3129
3130 /*
3131 amigupta, Enh 3880081 ENDS HERE
3132 */
3133
3134 FUNCTION GetDayNumber
3135 /*******************************************************************************
3136 ** Start of comments
3137 ** FUNCTION : GetDayNumber
3138 ** Description : This function will return a translated enumeration of the day
3139 ** in the month i.e. 1 will return 1st, 2 will return 2nd etc
3140 ** Parameters :
3141 ** name direction type required?
3142 ** ---- --------- ---- ---------
3143 ** p_DayNumber IN NUMBER
3144 **
3145 ** End of comments
3146 ******************************************************************************/
3147 ( p_DayNumber IN NUMBER
3148 )RETURN VARCHAR2
3149 IS
3150 CURSOR c_Day
3151 ( b_DayNumber VARCHAR2
3152 ) IS SELECT meaning
3153 FROM WF_LOOKUPS
3154 WHERE lookup_type = 'JTF_DAY_NUMBERS'
3155 AND lookup_code = b_DayNumber;
3156
3157 l_DayNumber VARCHAR2(80);
3158
3159 BEGIN
3160 OPEN c_Day(to_char(p_DayNumber));
3161 FETCH c_Day INTO l_DayNumber;
3162 CLOSE c_Day;
3163 RETURN l_DayNumber;
3164 END GetDayNumber;
3165
3166
3167 FUNCTION GetDayName
3168 /*******************************************************************************
3169 ** Start of comments
3170 ** FUNCTION : GetWeekdays
3171 ** Description : This function will return a translated string that sums up the
3172 ** days of the week for an appointment
3173 ** Parameters :
3174 ** name direction type required?
3175 ** ---- --------- ---- ---------
3176 ** p_DayCode IN VARCHAR2
3177 **
3178 ** End of comments
3179 ******************************************************************************/
3180 ( p_DayCode IN VARCHAR2
3181 )RETURN VARCHAR2
3182 IS
3183 CURSOR c_Day
3184 ( b_DayCode VARCHAR2
3185 ) IS SELECT meaning
3186 FROM WF_LOOKUPS
3187 WHERE lookup_type = 'JTF_DAY_NAMES'
3188 AND lookup_code = b_DayCode;
3189
3190 l_DayName VARCHAR2(80);
3191
3192 BEGIN
3193 OPEN c_Day(p_DayCode);
3194 FETCH c_Day INTO l_DayName;
3195 CLOSE c_Day;
3196 RETURN l_DayName;
3197 END GetDayName;
3198
3199
3200 FUNCTION GetDays
3201 /*******************************************************************************
3202 ** Start of comments
3203 ** FUNCTION : GetWeekdays
3204 ** Description : This function will return a translated string that sums up the
3205 ** days of the week for an appointment
3206 ** Parameters :
3207 ** name direction type required?
3208 ** ---- --------- ---- ---------
3209 ** p_DayCode IN VARCHAR2
3210 **
3211 ** End of comments
3212 ******************************************************************************/
3213 ( p_Sunday IN VARCHAR2
3214 , p_Monday IN VARCHAR2
3215 , p_Tuesday IN VARCHAR2
3216 , p_Wednesday IN VARCHAR2
3217 , p_Thursday IN VARCHAR2
3218 , p_Friday IN VARCHAR2
3219 , p_Saturday IN VARCHAR2
3220 )RETURN VARCHAR2
3221 IS
3222 l_days VARCHAR2(2000):= NULL;
3223
3224 BEGIN
3225 IF (p_Sunday = 'Y')
3226 THEN
3227 l_days := l_days||GetDayName('SUN');
3228 END IF;
3229
3230 IF (p_Monday = 'Y')
3231 THEN
3232 IF (l_days IS NOT NULL)
3233 THEN
3234 l_days := l_days||',';
3235 END IF;
3236 l_days := l_days||GetDayName('MON');
3237 END IF;
3238
3239 IF (p_Tuesday = 'Y')
3240 THEN
3241 IF (l_days IS NOT NULL)
3242 THEN
3243 l_days := l_days||',';
3244 END IF;
3245 l_days := l_days||GetDayName('TUE');
3246 END IF;
3247
3248 IF (p_Wednesday = 'Y')
3249 THEN
3250 IF (l_days IS NOT NULL)
3251 THEN
3252 l_days := l_days||',';
3253 END IF;
3254 l_days := l_days||GetDayName('WED');
3255 END IF;
3256
3257
3258 IF (p_Thursday = 'Y')
3259 THEN
3260 IF (l_days IS NOT NULL)
3261 THEN
3262 l_days := l_days||',';
3263 END IF;
3264 l_days := l_days||GetDayName('THU');
3265 END IF;
3266
3267
3268 IF (p_Friday = 'Y')
3269 THEN
3270 IF (l_days IS NOT NULL)
3271 THEN
3272 l_days := l_days||',';
3273 END IF;
3274 l_days := l_days||GetDayName('FRI');
3275 END IF;
3276
3277
3278 IF (p_Saturday = 'Y')
3279 THEN
3280 IF (l_days IS NOT NULL)
3281 THEN
3282 l_days := l_days||',';
3283 END IF;
3284 l_days := l_days||GetDayName('SAT');
3285 END IF;
3286
3287 RETURN l_days;
3288 END GetDays;
3289
3290 /*
3291 sankgupt, Bug # 5011863 STARTS HERE
3292 */
3293
3294 PROCEDURE DeleteAttendee
3295 /*******************************************************************************
3296 ** Start of comments
3297 ** Procedure : DeleteAttendee
3298 ** Description : Given the task ID and the Invitee id this procedure will send reminders to
3299 ** attendees if the appointment is deleted
3300 **
3301 ** Parameters :
3302 ** name direction type required
3303 ** ---- --------- ---- ---------
3304 ** p_api_version IN NUMBER required
3305 ** p_init_msg_list IN VARCHAR2 optional
3306 ** p_commit IN VARCHAR2 optional
3307 ** x_return_status OUT VARCHAR2 optional
3308 ** x_msg_count OUT NUMBER required
3309 ** x_msg_data OUT VARCHAR2 required
3310 ** p_TaskID IN NUMBER required
3311 ** p_RemindDate IN DATE required
3312 ** p_INVITEE IN NUMBER required
3313 ** p_INVITEE_TYPE IN VARCHAR2 required
3314 ** Notes :
3315 ** 1) If an invitee does not exist in the WF directory a notification will
3316 ** be send to the invitor saying that the invitation was not send.
3317 ** 2) Currently invitations are only send to employees
3318 ** 3) The WFs won't be started until a commmit is done.
3319 **
3320 ** End of comments
3321 *******************************************************************************/
3322 ( p_api_version IN NUMBER
3323 , p_init_msg_list IN VARCHAR2
3324 , p_commit IN VARCHAR2
3325 , x_return_status OUT NOCOPY VARCHAR2
3326 , x_msg_count OUT NOCOPY NUMBER
3327 , x_msg_data OUT NOCOPY VARCHAR2
3328 , p_INVITEE IN NUMBER -- Resource ID of Invitee
3329 , p_INVITEE_TYPE IN VARCHAR2 --Resource Type of the INVITEE
3330 , p_TaskID IN NUMBER -- Task ID of the appointment
3331 )
3332 IS
3333 l_api_name CONSTANT VARCHAR2(30) := 'UpdateAttendeeWF';
3334 l_api_version CONSTANT NUMBER := 1.0;
3335 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
3336
3337 l_ItemType VARCHAR2(8) := 'CACVWSWF';
3338 l_task_type_name VARCHAR2(30);
3339 l_task_priority_name VARCHAR2(30);
3340 l_timezone VARCHAR2(80);
3341
3342 l_AdminWFRole VARCHAR2(30);
3343 l_AdminWFName VARCHAR2(30);
3344 l_AdminEmpNumber NUMBER;
3345
3346 CURSOR c_invitor
3347 /****************************************************************************
3348 ** Pick up appointment owner
3349 ****************************************************************************/
3350 (p_task_id NUMBER)
3351 IS
3352 SELECT tsk_vl.task_id, owner_id,owner_type_code
3353 FROM jtf_tasks_vl tsk_vl,
3354 jtf_task_all_assignments tsk_asg
3355 WHERE tsk_vl.task_id = p_task_id
3356 and tsk_vl.task_id = tsk_asg.task_id
3357 AND assignee_role = 'OWNER' ;
3358
3359 CURSOR c_Task
3360 /*****************************************************************************
3361 ** Cursor to pick up all the invitees for the appointment. Also picks up
3362 ** a itemkey from the sequence, this is needed to start the workflow
3363 *****************************************************************************/
3364 (b_TaskID NUMBER, b_INVITEE NUMBER, b_INVITEE_TYPE VARCHAR2
3365 )IS SELECT to_char(cac_vws_itemkey_s.NEXTVAL) ItemKey
3366 , jta.task_assignment_id TASK_ASSIGNMENT_ID
3367 , jta.resource_id INVITEE
3368 , jta.resource_type_code INVITEE_CODE
3369 , jtl.task_name TASK_NAME
3370 , jtl.description TASK_DESCRIPTION
3371 , jtb.calendar_start_date START_DATE
3372 , jtb.task_type_id TYPE_ID
3373 , jtb.task_priority_id PRIORITY_ID
3374 , (jtb.calendar_end_date - jtb.calendar_start_date)*24*60 DURATION
3375 , jtb.timezone_id TIMEZONE_ID
3376 FROM jtf_tasks_b jtb
3377 , jtf_tasks_tl jtl
3378 , jtf_task_all_assignments jta
3379 WHERE jtb.task_id = jtl.task_id
3380 AND jtl.language = userenv('LANG')
3381 AND jta.task_id = jtb.task_id
3382 AND jta.show_on_calendar = 'Y'
3383 AND jta.assignee_role = 'ASSIGNEE'
3384 AND jtb.task_id = b_TaskID
3385 AND jta.resource_id = b_INVITEE
3386 AND jta.resource_type_code = b_INVITEE_TYPE ;
3387
3388 l_return_status VARCHAR2(1);
3389 l_msg_count NUMBER;
3390 l_msg_data VARCHAR2(2000);
3391 l_status VARCHAR2(8);
3392 l_result VARCHAR2(30);
3393 l_invitor c_invitor%ROWTYPE;
3394
3395
3396
3397 BEGIN
3398
3399 /*****************************************************************************
3400 ** Standard call to check for call compatibility
3401 *****************************************************************************/
3402 IF NOT FND_API.Compatible_API_Call( l_api_version
3403 , p_api_version
3404 , l_api_name
3405 , G_PKG_NAME
3406 )
3407 THEN
3408 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3409 END IF;
3410
3411 /*****************************************************************************
3412 ** Initialize message list if p_init_msg_list is set to TRUE
3413 *****************************************************************************/
3414 IF FND_API.To_Boolean(p_init_msg_list)
3415 THEN
3416 FND_MSG_PUB.Initialize;
3417 END IF;
3418
3419 /*****************************************************************************
3420 ** Initialize API return status to success
3421 *****************************************************************************/
3422 x_return_status := FND_API.G_RET_STS_SUCCESS;
3423
3424 /*****************************************************************************
3425 ** Get Appointment details for every invitee and start a notification WF for
3426 ** all of them.
3427 *****************************************************************************/
3428 OPEN c_invitor (p_TaskID);
3429 FETCH c_invitor INTO l_invitor;
3430 IF c_invitor%NOTFOUND THEN
3431 CLOSE c_invitor;
3432 RAISE fnd_api.g_exc_unexpected_error;
3433 END IF;
3434 CLOSE c_invitor;
3435
3436 FOR r_Task IN c_Task(p_TaskID, p_INVITEE, p_INVITEE_TYPE)
3437 LOOP <<ASSIGNEES>>
3438
3439 WF_Role( l_invitor.owner_id
3440 , l_invitor.owner_type_code
3441 , l_AdminWFRole
3442 , l_AdminWFName
3443 , l_AdminEmpNumber
3444 );
3445
3446
3447 l_task_type_name := get_type_name(r_Task.TYPE_ID);
3448 l_task_priority_name := get_priority_name(r_Task.PRIORITY_ID);
3449 l_timezone := GetTimezone(p_timezone_id => r_Task.timezone_id);
3450
3451 wf_engine.CreateProcess( itemtype => l_ItemType
3452 , itemkey => r_Task.ItemKey
3453 , process => 'DELETE_INVITATION'
3454 );
3455
3456 wf_engine.SetItemAttrText( itemtype => l_itemtype
3457 , itemkey => r_Task.ItemKey
3458 , aname => 'CALENDAR_ADMIN'
3459 , avalue => l_AdminWFRole
3460 );
3461
3462 wf_engine.SetItemAttrText( itemtype => l_itemtype
3463 , itemkey => r_Task.ItemKey
3464 , aname => 'INVITOR'
3465 , avalue => l_invitor.owner_id
3466 );
3467
3468 wf_engine.SetItemAttrNumber( itemtype => l_itemtype
3469 , itemkey => r_Task.ItemKey
3470 , aname => 'INVITEE'
3471 , avalue => r_Task.INVITEE
3472 );
3473
3474 wf_engine.SetItemAttrText( itemtype => l_itemtype
3475 , itemkey => r_Task.ItemKey
3476 , aname => 'INVITEE_CODE'
3477 , avalue => r_Task.INVITEE_CODE
3478 );
3479
3480 wf_engine.SetItemAttrNumber( itemtype => l_itemtype
3481 , itemkey => r_Task.ItemKey
3482 , aname => 'TASK_ASSIGNMENT_ID'
3483 , avalue => r_Task.TASK_ASSIGNMENT_ID
3484 );
3485
3486 wf_engine.SetItemAttrNumber( itemtype => l_itemtype
3487 , itemkey => r_Task.ItemKey
3488 , aname => 'TASK_ID'
3489 , avalue => p_TaskID
3490 );
3491
3492 wf_engine.SetItemAttrText( itemtype => l_itemtype
3493 , itemkey => r_Task.ItemKey
3494 , aname => 'TASK_NAME'
3495 , avalue => r_Task.TASK_NAME
3496 );
3497
3498 wf_engine.SetItemAttrText( itemtype => l_itemtype
3499 , itemkey => r_Task.ItemKey
3500 , aname => 'TASK_DESCRIPTION'
3501 , avalue => r_Task.TASK_DESCRIPTION
3502 );
3503
3504 wf_engine.SetItemAttrDate( itemtype => l_itemtype
3505 , itemkey => r_Task.ItemKey
3506 , aname => 'START_DATE'
3507 , avalue => r_Task.START_DATE
3508 );
3509
3510 wf_engine.SetItemAttrText( itemtype => l_itemtype
3511 , itemkey => r_Task.ItemKey
3512 , aname => 'TYPE'
3513 , avalue => l_task_type_name
3514 );
3515 wf_engine.SetItemAttrText( itemtype => l_itemtype
3516 , itemkey => r_Task.ItemKey
3517 , aname => 'PRIORITY'
3518 , avalue => l_task_priority_name
3519 );
3520 wf_engine.SetItemAttrText( itemtype => l_itemtype
3521 , itemkey => r_Task.ItemKey
3522 , aname => 'DURATION'
3523 , avalue => get_duration(r_Task.DURATION)
3524 );
3525 wf_engine.SetItemAttrText( itemtype => l_itemtype
3526 , itemkey => r_Task.ItemKey
3527 , aname => 'TIMEZONE'
3528 , avalue => l_timezone
3529 );
3530
3531 /***************************************************************************
3532 ** Start the workflow
3533 ***************************************************************************/
3534
3535 wf_engine.StartProcess( itemtype => l_itemtype
3536 , itemkey => r_Task.ItemKey
3537 );
3538
3539
3540 /***************************************************************************
3541 ** Standard check of p_commit (WF won't start until commited)
3542 ***************************************************************************/
3543
3544
3545 IF FND_API.To_Boolean(p_commit)
3546 THEN
3547 COMMIT WORK;
3548 END IF;
3549 /***************************************************************************
3550 ** Standard check of p_commit (WF won't start until commited)
3551 ***************************************************************************/
3552 IF FND_API.To_Boolean(p_commit)
3553 THEN
3554 COMMIT WORK;
3555 END IF;
3556
3557 END LOOP ASSIGNEES;
3558
3559 /*****************************************************************************
3560 ** Standard call to get message count and if count is > 1, get message info
3561 *****************************************************************************/
3562 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
3563 , p_data => x_msg_data
3564 );
3565 EXCEPTION
3566 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
3567 THEN
3568
3569 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3570 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
3571 , p_data => x_msg_data
3572 );
3573 WHEN OTHERS
3574 THEN
3575
3576 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3577 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3578 THEN
3579 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
3580 , l_api_name
3581 );
3582 END IF;
3583 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
3584 , p_data => x_msg_data
3585 );
3586
3587 END DeleteAttendee;
3588
3589 /*
3590 sankgupt, Bug # 5011863 STARTS HERE
3591 */
3592
3593 PROCEDURE GetRepeatingRule
3594 /*******************************************************************************
3595 ** Start of comments
3596 ** Procedure : GetRepeatingRule
3597 ** Description : Set the attributes for the repeating rule and determine which
3598 ** notification to send
3599 ** Parameters :
3600 ** name direction type required?
3601 ** ---- --------- ---- ---------
3602 ** itemtype IN VARCHAR2 required
3603 ** itemkey IN VARCHAR2 required
3604 ** actid IN NUMBER required
3605 ** funcmode IN VARCHAR2 required
3606 ** resultout OUT VARCHAR2 required
3607 **
3608 ** Notes :
3609 ** 1) Expects WF item attributes 'TASK_ID' to be available to this procedure.
3610 ** 2) This procedure should only be used within Workflow
3611 **
3612 ** End of comments
3613 ******************************************************************************/
3614 ( itemtype IN VARCHAR2
3615 , itemkey IN VARCHAR2
3616 , actid IN NUMBER
3617 , funcmode IN VARCHAR2
3618 , resultout OUT NOCOPY VARCHAR2
3619 )
3620 IS
3621 l_TaskID NUMBER;
3622 l_ResultType VARCHAR2(80);
3623 l_days VARCHAR2(2000);
3624 l_DayNumber VARCHAR2(2000);
3625 l_which VARCHAR2(80);
3626 l_timezone VARCHAR2(80);
3627
3628 CURSOR c_rule
3629 /*****************************************************************************
3630 ** Get the recurrance rule for the given task
3631 *****************************************************************************/
3632 (b_task_id IN NUMBER
3633 )IS SELECT jtr.occurs_which
3634 , jtr.day_of_week
3635 , jtr.date_of_month
3636 , jtr.occurs_month
3637 , jtr.occurs_uom
3638 , jtr.occurs_every
3639 , jtr.occurs_number
3640 , jtr.start_date_active
3641 , jtr.end_date_active
3642 , jtr.sunday
3643 , jtr.monday
3644 , jtr.tuesday
3645 , jtr.wednesday
3646 , jtr.thursday
3647 , jtr.friday
3648 , jtr.saturday
3649 , jtb.timezone_id
3650 , (jtb.calendar_end_date - jtb.calendar_start_date)*24*60 duration
3651 FROM jtf_task_recur_rules jtr
3652 , jtf_tasks_b jtb
3653 WHERE jtb.recurrence_rule_id = jtr.recurrence_rule_id
3654 AND jtb.task_id = b_task_id;
3655
3656 r_rule c_rule%ROWTYPE;
3657
3658 BEGIN
3659 IF (funcmode = 'RUN')
3660 THEN
3661 /***************************************************************************
3662 ** 'RUN' function from WF
3663 ***************************************************************************/
3664
3665 /***************************************************************************
3666 ** Pick up the Task ID Attribute
3667 ***************************************************************************/
3668 l_TaskID := wf_engine.GetItemAttrNumber
3669 ( itemtype => itemtype
3670 , itemkey => itemkey
3671 , aname => 'TASK_ID'
3672 );
3673
3674 /***************************************************************************
3675 ** Get the Rule if one was set
3676 ***************************************************************************/
3677 OPEN c_rule(l_TaskID);
3678 FETCH c_rule INTO r_rule;
3679 IF (c_rule%FOUND)
3680 THEN
3681
3682 WF_ENGINE.SetItemAttrNumber( itemtype => itemtype
3683 , itemkey => itemkey
3684 , aname => 'OCCURS_EVERY'
3685 , avalue => r_rule.occurs_every
3686 );
3687 WF_ENGINE.SetItemAttrText( itemtype => itemtype
3688 , itemkey => itemkey
3689 , aname => 'DURATION'
3690 , avalue => get_duration(r_rule.duration)
3691 );
3692 WF_ENGINE.SetItemAttrDate( itemtype => itemtype
3693 , itemkey => itemkey
3694 , aname => 'REPEAT_END_DATE'
3695 , avalue => r_rule.end_date_active
3696 );
3697
3698 l_Days := GetDays( p_Sunday => r_rule.sunday
3699 , p_Monday => r_rule.monday
3700 , p_Tuesday => r_rule.tuesday
3701 , p_Wednesday => r_rule.wednesday
3702 , p_Thursday => r_rule.thursday
3703 , p_Friday => r_rule.friday
3704 , p_Saturday => r_rule.saturday
3705 );
3706
3707 l_DayNumber := GetDayNumber(p_DayNumber => r_rule.date_of_month);
3708 l_which := GetDayNumber(p_DayNumber => r_rule.occurs_which);
3709 l_timezone := GetTimezone(p_timezone_id => r_rule.timezone_id);
3710
3711 WF_ENGINE.SetItemAttrText( itemtype => itemtype
3712 , itemkey => itemkey
3713 , aname => 'DAYSOFWEEK'
3714 , avalue => l_days
3715 );
3716
3717 WF_ENGINE.SetItemAttrText( itemtype => itemtype
3718 , itemkey => itemkey
3719 , aname => 'DATE_OF_MONTH'
3720 , avalue => l_DayNumber
3721 );
3722
3723 WF_ENGINE.SetItemAttrText( itemtype => itemtype
3724 , itemkey => itemkey
3725 , aname => 'OCCURS_WHICH'
3726 , avalue => l_which
3727 );
3728
3729 WF_ENGINE.SetItemAttrText( itemtype => itemtype
3730 , itemkey => itemkey
3731 , aname => 'TIMEZONE'
3732 , avalue => l_timezone
3733 );
3734
3735
3736 /*************************************************************************
3737 ** Determine the function result
3738 *************************************************************************/
3739 IF r_rule.occurs_uom = 'DAY'
3740 THEN
3741 l_ResultType:= 'REPEAT_DAY';
3742
3743 ELSIF r_rule.occurs_uom = 'WEK'
3744 THEN
3745 l_ResultType:= 'REPEAT_WEEK';
3746
3747 ELSIF r_rule.occurs_uom = 'MON'
3748 THEN
3749 IF (r_rule.date_of_month IS NOT NULL)
3750 THEN
3751 l_ResultType:= 'REPEAT_MON1';
3752 ELSE
3753 l_ResultType:= 'REPEAT_MON2';
3754 END IF;
3755 END IF;
3756 ELSE
3757 /*************************************************************************
3758 ** No rule found
3759 *************************************************************************/
3760 l_ResultType:= 'SINGLE';
3761 END IF;
3762
3763 CLOSE c_rule;
3764
3765 resultout := 'COMPLETE:'||l_ResultType;
3766
3767 ELSIF (funcmode = 'CANCEL')
3768 THEN
3769 /***************************************************************************
3770 ** 'CANCEL' function from WF
3771 ***************************************************************************/
3772 resultout := 'COMPLETE:SINGLE';
3773 ELSIF (funcmode = 'TIMEOUT')
3774 THEN
3775 /***************************************************************************
3776 ** 'TIMEOUT' function from WF
3777 ***************************************************************************/
3778 resultout := 'COMPLETE:SINGLE';
3779 ELSE
3780 /***************************************************************************
3781 ** Unknown function from WF - raise error
3782 ***************************************************************************/
3783 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3784 END IF;
3785
3786 EXCEPTION
3787 WHEN OTHERS
3788 THEN
3789 /*****************************************************************************
3790 ** Something went wrong return 'ERROR' and set the ERROR_MESSAGE
3791 *****************************************************************************/
3792 IF (c_rule%ISOPEN)
3793 THEN
3794 CLOSE c_rule;
3795 END IF;
3796
3797 wf_engine.SetItemAttrText( itemtype => itemtype
3798 , itemkey => itemkey
3799 , aname => 'ERROR_MESSAGE'
3800 , avalue => 'CAC_VIEW_WF_PVT.GetRepeatingRule(): ' || to_char(SQLCODE)||':'||SQLERRM
3801 );
3802
3803 resultout := 'COMPLETE:ERROR';
3804
3805 END GetRepeatingRule;
3806
3807 PROCEDURE GetInvitationStatus
3808 /*******************************************************************************
3809 ** Start of comments
3810 ** Procedure : GetInvitationStatus
3811 ** Description : Set the attributes for the invitation status and determine which
3812 ** notification to send
3813 ** Parameters :
3814 ** name direction type required?
3815 ** ---- --------- ---- ---------
3816 ** itemtype IN VARCHAR2 required
3817 ** itemkey IN VARCHAR2 required
3818 ** actid IN NUMBER required
3819 ** funcmode IN VARCHAR2 required
3820 ** resultout OUT VARCHAR2 required
3821 **
3822 ** Notes :
3823 ** 1) Expects WF item attributes 'ASSIGNMENT_STATUS_ID' to be available to this procedure.
3824 ** 2) This procedure should only be used within Workflow
3825 ** 3) Created for 2219647
3826 **
3827 ** End of comments
3828 ******************************************************************************/
3829 ( itemtype IN VARCHAR2
3830 , itemkey IN VARCHAR2
3831 , actid IN NUMBER
3832 , funcmode IN VARCHAR2
3833 , resultout OUT NOCOPY VARCHAR2
3834 )
3835 IS
3836 l_result_type VARCHAR2(80);
3837 l_assignment_status_id NUMBER;
3838
3839
3840 BEGIN
3841 IF (funcmode = 'RUN')
3842 THEN
3843 /***************************************************************************
3844 ** 'RUN' function from WF
3845 ***************************************************************************/
3846
3847 /***************************************************************************
3848 ** Pick up the Task ID Attribute
3849 ***************************************************************************/
3850 l_assignment_status_id := wf_engine.GetItemAttrNumber
3851 ( itemtype => itemtype
3852 , itemkey => itemkey
3853 , aname => 'ASSIGNMENT_STATUS_ID'
3854 );
3855
3856
3857 /*************************************************************************
3858 ** Determine the function result
3859 *************************************************************************/
3860 IF l_assignment_status_id = 3
3861 THEN
3862 l_result_type:= 'APPROVED';
3863
3864 ELSIF l_assignment_status_id = 4
3865 THEN
3866 l_result_type:= 'REJECTED';
3867 END IF;
3868 resultout := 'COMPLETE:'||l_result_type;
3869 END IF;
3870 IF (funcmode = 'CANCEL') THEN
3871 resultout := 'COMPLETE:';
3872 RETURN;
3873 END IF;
3874 IF (funcmode = 'TIMEOUT') THEN
3875 resultout := 'COMPLETE:';
3876 RETURN;
3877 END IF;
3878 EXCEPTION
3879 WHEN OTHERS
3880 THEN
3881
3882 wf_engine.SetItemAttrText( itemtype => itemtype
3883 , itemkey => itemkey
3884 , aname => 'ERROR_MESSAGE'
3885 , avalue => 'CAC_VIEW_WF_PVT.GetInvitationStatus(): ' || to_char(SQLCODE)||':'||SQLERRM
3886 );
3887 resultout := 'COMPLETE:ERROR';
3888
3889 END GetInvitationStatus;
3890
3891
3892 END CAC_VIEW_WF_PVT;