[Home] [Help]
PACKAGE BODY: APPS.JTF_CAL_WF_PVT
Source
1 PACKAGE BODY JTF_CAL_WF_PVT AS
2 /* $Header: jtfvcwb.pls 120.2 2005/11/30 04:25:51 sankgupt ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'JTF_CAL_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) := 'JTFCALWF';
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(jtf_cal_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) := 'JTFCALWF';
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(jtf_cal_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_type_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 = 'JTF_CAL_ADMIN_ACCESS')
861 THEN
862 fnd_grants_pkg.grant_function( p_api_version => 1.0
863 , p_menu_name => 'JTF_CAL_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 => 'JTF_CAL_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) := 'JTFCALWF';
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(jtf_cal_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 => 'JTF_CAL_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 => 'JTF_CAL_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 => 'JTF_CAL_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) := 'JTFCALWF';
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(jtf_cal_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_type_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) := 'JTFCALWF';
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(jtf_cal_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_type_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 jtf_cal_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 ** 'JTFCALWF.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 WF_Role( l_InvitorResourceID
2136 , l_InviteeCode -- Not used, for future enhancements
2137 , l_InvitorWFRole
2138 , l_InvitorWFName
2139 , l_InvitorEmpNumber
2140 );
2141
2142 /***************************************************************************
2143 ** If the invitor doesn't exist in the WF directory we can't send a
2144 ** notification to invitor
2145 ***************************************************************************/
2146 IF ((l_InvitorWFRole IS NULL) OR
2147 (l_InvitorWFName IS NULL)
2148 )
2149 THEN
2150 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2151 ELSE
2152 /*************************************************************************
2153 ** Found INVITOR, set WF Attributes
2154 *************************************************************************/
2155 WF_ENGINE.SetItemAttrText( itemtype => itemtype
2156 , itemkey => itemkey
2157 , aname => 'WF_INVITOR_ROLE'
2158 , avalue => l_InvitorWFRole
2159 );
2160
2161 WF_ENGINE.SetItemAttrText( itemtype => itemtype
2162 , itemkey => itemkey
2163 , aname => 'WF_INVITOR_NAME'
2164 , avalue => l_InvitorWFName
2165 );
2166
2167 /*************************************************************************
2168 ** Determine the WF Role and WF Name for the Invitee
2169 *************************************************************************/
2170 WF_Role( l_InviteeResourceID
2171 , l_InviteeCode -- Not used, for future enhancements
2172 , l_InviteeWFRole
2173 , l_InviteeWFName
2174 , l_InvitorEmpNumber
2175 );
2176
2177 IF ((l_InviteeWFRole IS NULL) OR
2178 (l_InviteeWFName IS NULL)
2179 )
2180 THEN
2181 /***********************************************************************
2182 ** If the invitee doesn't exist in the WF directory we send a warning
2183 ** notification to the invitor
2184 ***********************************************************************/
2185 resultout := 'COMPLETE:WARNING';
2186 ELSE
2187 /***********************************************************************
2188 ** Found INVITEE, set WF Attributes
2189 ***********************************************************************/
2190 WF_ENGINE.SetItemAttrText( itemtype => itemtype
2191 , itemkey => itemkey
2192 , aname => 'WF_INVITEE_ROLE'
2193 , avalue => l_InviteeWFRole
2194 );
2195
2196 WF_ENGINE.SetItemAttrText( itemtype => itemtype
2197 , itemkey => itemkey
2198 , aname => 'WF_INVITEE_NAME'
2199 , avalue => l_InviteeWFName
2200 );
2201 resultout := 'COMPLETE:NO_ERROR';
2202 END IF;
2203 END IF;
2204
2205 ELSIF (funcmode = 'CANCEL')
2206 THEN
2207 /***************************************************************************
2208 ** 'CANCEL' function from WF
2209 ***************************************************************************/
2210 resultout := 'COMPLETE:NO_ERROR';
2211 ELSIF (funcmode = 'TIMEOUT')
2212 THEN
2213 /***************************************************************************
2214 ** 'TIMEOUT' function from WF
2215 ***************************************************************************/
2216 resultout := 'COMPLETE:NO_ERROR';
2217 ELSE
2218 /***************************************************************************
2219 ** Unknown function from WF - raise error
2220 ***************************************************************************/
2221 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2222 END IF;
2223
2224 EXCEPTION
2225 WHEN OTHERS
2226 THEN
2227 /*****************************************************************************
2228 ** Something went wrong return 'ERROR' and set the ERROR_MESSAGE
2229 *****************************************************************************/
2230 wf_engine.SetItemAttrText( itemtype => itemtype
2231 , itemkey => itemkey
2232 , aname => 'ERROR_MESSAGE'
2233 , avalue => 'JTF_CAL_WF_PVT.DetermineWFRole(): ' || to_char(SQLCODE)||':'||SQLERRM
2234 );
2235
2236 resultout := 'COMPLETE:ERROR';
2237
2238 END DetermineWFRole;
2239
2240
2241 PROCEDURE StartReminders
2242 /*******************************************************************************
2243 ** Start of comments
2244 ** Procedure : StartReminder
2245 ** Description : Given the task ID of the appointment (p_TaskID) and the
2246 ** Resource ID of the invitor (p_INVITOR) this procedure will
2247 ** start WF that will initiate the sending of reminders when the
2248 ** time has come..
2249 ** Parameters :
2250 ** name direction type required?
2251 ** ---- --------- ---- ---------
2252 ** p_api_version IN NUMBER required
2253 ** p_init_msg_list IN VARCHAR2 optional
2254 ** p_commit IN VARCHAR2 optional
2255 ** x_return_status OUT VARCHAR2 optional
2256 ** x_msg_count OUT NUMBER required
2257 ** x_msg_data OUT VARCHAR2 required
2258 ** p_INVITOR IN NUMBER required
2259 ** p_TaskID IN NUMBER required
2260 ** Notes :
2261 **
2262 ** End of comments
2263 *******************************************************************************/
2264 ( p_api_version IN NUMBER
2265 , p_init_msg_list IN VARCHAR2
2266 , p_commit IN VARCHAR2
2267 , x_return_status OUT NOCOPY VARCHAR2
2268 , x_msg_count OUT NOCOPY NUMBER
2269 , x_msg_data OUT NOCOPY VARCHAR2
2270 , p_INVITOR IN NUMBER -- Resource ID of Invitor
2271 , p_TaskID IN NUMBER -- Task ID of the appointment
2272 , p_RemindDate IN DATE -- Date/Time the reminder needs to be send
2273 )
2274 IS
2275 l_api_name CONSTANT VARCHAR2(30) := 'StartReminders';
2276 l_api_version CONSTANT NUMBER := 1.0;
2277 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
2278
2279 l_ItemType VARCHAR2(8) := 'JTFTKRDR';
2280 l_ItemKey VARCHAR2(100);
2281 l_AdminWFRole VARCHAR2(30);
2282
2283 BEGIN
2284 /*****************************************************************************
2285 ** Standard call to check for call compatibility
2286 *****************************************************************************/
2287 IF NOT FND_API.Compatible_API_Call( l_api_version
2288 , p_api_version
2289 , l_api_name
2290 , G_PKG_NAME
2291 )
2292 THEN
2293 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2294 END IF;
2295
2296 /*****************************************************************************
2297 ** Initialize message list if p_init_msg_list is set to TRUE
2298 *****************************************************************************/
2299 IF FND_API.To_Boolean(p_init_msg_list)
2300 THEN
2301 FND_MSG_PUB.Initialize;
2302 END IF;
2303
2304 /*****************************************************************************
2305 ** Initialize API return status to success
2306 *****************************************************************************/
2307 x_return_status := FND_API.G_RET_STS_SUCCESS;
2308
2309 /*****************************************************************************
2310 ** Look up the Calendar Administrator from the Profile (this should be an
2311 ** existing WF_ROLE)
2312 *****************************************************************************/
2313 l_AdminWFRole := FND_PROFILE.Value(name => 'JTF_CALENDAR_ADMINISTRATOR');
2314
2315 /***************************************************************************
2316 ** we need an itemkey
2317 ***************************************************************************/
2318 SELECT to_char(jtf_cal_itemkey_s.NEXTVAL) INTO l_ItemKey
2319 FROM DUAL;
2320 /***************************************************************************
2321 ** Initialize the workflow
2322 ***************************************************************************/
2323 wf_engine.CreateProcess( itemtype => l_ItemType
2324 , itemkey => l_ItemKey
2325 , process => 'DELAYSTARTREMINDER'
2326 );
2327
2328 wf_engine.SetItemAttrNumber( itemtype => l_itemtype
2329 , itemkey => l_ItemKey
2330 , aname => 'TASK_ID'
2331 , avalue => p_TaskID
2332 );
2333
2334 wf_engine.SetItemAttrDate( itemtype => l_itemtype
2335 , itemkey => l_ItemKey
2336 , aname => 'REMIND_DATE'
2337 , avalue => p_RemindDate
2338 );
2339
2340 wf_engine.SetItemAttrText( itemtype => l_itemtype
2341 , itemkey => l_ItemKey
2342 , aname => 'CALENDAR_ADMIN'
2343 , avalue => l_AdminWFRole
2344 );
2345
2346 wf_engine.SetItemAttrNumber( itemtype => l_itemtype
2347 , itemkey => l_ItemKey
2348 , aname => 'INVITOR'
2349 , avalue => p_INVITOR
2350 );
2351
2352 /***************************************************************************
2353 ** Start the workflow
2354 ***************************************************************************/
2355 wf_engine.StartProcess( itemtype => l_itemtype
2356 , itemkey => l_ItemKey
2357 );
2358
2359 /***************************************************************************
2360 ** Save the workflow itemtype and item key so we can update the reminder WF
2361 ** if the start date or remind me settings change
2362 ***************************************************************************/
2363
2364 UPDATE jtf_task_all_assignments
2365 SET reminder_wf_item_type = l_itemtype
2366 , reminder_wf_item_key = l_ItemKey
2367 WHERE task_id = p_TaskID;
2368
2369 /***************************************************************************
2370 ** Standard check of p_commit (WF won't start until commited)
2371 ***************************************************************************/
2372 IF FND_API.To_Boolean(p_commit)
2373 THEN
2374 COMMIT WORK;
2375 END IF;
2376
2377 /*****************************************************************************
2378 ** Standard call to get message count and if count is > 1, get message info
2379 *****************************************************************************/
2380 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
2381 , p_data => x_msg_data
2382 );
2383 EXCEPTION
2384 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
2385 THEN
2386 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2387 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
2388 , p_data => x_msg_data
2389 );
2390 WHEN OTHERS
2391 THEN
2392 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2393 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2394 THEN
2395 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
2396 , l_api_name
2397 );
2398 END IF;
2399 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
2400 , p_data => x_msg_data
2401 );
2402
2403 END StartReminders;
2404
2405
2406 PROCEDURE SendReminders
2407 /*******************************************************************************
2408 ** Start of comments
2409 ** Procedure : SendReminders
2410 ** Description :
2411 ** Parameters :
2412 ** name direction type required?
2413 ** ---- --------- ---- ---------
2414 ** itemtype IN VARCHAR2 required
2415 ** itemkey IN VARCHAR2 required
2416 ** actid IN NUMBER required
2417 ** funcmode IN VARCHAR2 required
2418 ** resultout OUT VARCHAR2 required
2419 **
2420 ** Notes :
2421 **
2422 ** End of comments
2423 ******************************************************************************/
2424 ( itemtype IN VARCHAR2
2425 , itemkey IN VARCHAR2
2426 , actid IN NUMBER
2427 , funcmode IN VARCHAR2
2428 , resultout OUT NOCOPY VARCHAR2
2429 )
2430 IS
2431 l_api_name CONSTANT VARCHAR2(30) := 'StartReminderWF';
2432 l_api_version CONSTANT NUMBER := 1.0;
2433 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
2434
2435 l_ItemType VARCHAR2(8) := 'JTFTKRDR';
2436 l_AdminWFRole VARCHAR2(30);
2437 l_StartDateCorrected DATE;
2438 l_EndDateCorrected DATE;
2439 l_user_id NUMBER;
2440 l_DestTimezoneID NUMBER;
2441 l_SourceTimezoneID NUMBER;
2442
2443 CURSOR c_Task
2444 /*****************************************************************************
2445 ** Cursor to pick up all the invitees for the appointment. Also picks up
2446 ** a itemkey from the sequence, this is needed to start the workflow
2447 *****************************************************************************/
2448 (b_TaskID NUMBER
2449 )IS SELECT jta.task_assignment_id TaskAssignmentID
2450 , to_char(jtf_cal_itemkey_s.NEXTVAL) ItemKey
2451 , jta.resource_id INVITEE
2452 , jta.resource_type_code INVITEE_CODE
2453 , jtl.task_name TASK_NAME
2454 , jtl.description TASK_DESCRIPTION
2455 , jtb.calendar_start_date START_DATE
2456 , jtb.calendar_end_date END_DATE
2457 , NVL( jtb.timezone_id
2458 ,(NVL( FND_PROFILE.Value('SERVER_TIMEZONE_ID')
2459 , 4)
2460 )
2461 ) SourceTimezoneID
2462 FROM jtf_tasks_b jtb
2463 , jtf_tasks_tl jtl
2464 , jtf_task_all_assignments jta
2465 WHERE jtb.task_id = jtl.task_id
2466 AND jtl.language = userenv('LANG')
2467 AND jta.task_id = jtb.task_id
2468 AND jta.show_on_calendar = 'Y'
2469 AND jta.assignment_status_id <> 4
2470 AND jtb.task_id = b_TaskID;
2471
2472 l_TaskID NUMBER;
2473 l_INVITOR NUMBER;
2474
2475 BEGIN
2476 /*****************************************************************************
2477 ** Retrieve the globals that where set
2478 *****************************************************************************/
2479 l_AdminWFRole := wf_engine.GetItemAttrNumber( itemtype => itemtype
2480 , itemkey => itemkey
2481 , aname => 'CALENDAR_ADMIN'
2482 );
2483
2484 l_TaskID := wf_engine.GetItemAttrNumber( itemtype => itemtype
2485 , itemkey => itemkey
2486 , aname => 'TASK_ID'
2487 );
2488
2489 l_INVITOR:= wf_engine.GetItemAttrNumber( itemtype => itemtype
2490 , itemkey => itemKey
2491 , aname => 'INVITOR'
2492 );
2493
2494 /*****************************************************************************
2495 ** Get Appointment details for every invitee and start a notification WF for
2496 ** all of them.
2497 *****************************************************************************/
2498 FOR r_Task IN c_Task(l_TaskID)
2499 LOOP <<ASSIGNEES>>
2500 /***************************************************************************
2501 ** Initialize the workflow
2502 ***************************************************************************/
2503 wf_engine.CreateProcess( itemtype => l_ItemType
2504 , itemkey => r_Task.ItemKey
2505 , process => 'JTF_TASK_REMINDER'
2506 );
2507
2508 wf_engine.SetItemAttrText( itemtype => l_itemtype
2509 , itemkey => r_Task.ItemKey
2510 , aname => 'CALENDAR_ADMIN'
2511 , avalue => l_AdminWFRole
2512 );
2513
2514 wf_engine.SetItemAttrNumber( itemtype => l_itemtype
2515 , itemkey => r_Task.ItemKey
2516 , aname => 'INVITEE'
2517 , avalue => r_Task.INVITEE
2518 );
2519 ---Bug # 4089393, amigupta, Setting new attribute value INVITEE_CODE
2520
2521 wf_engine.SetItemAttrText( itemtype => l_itemtype
2522 , itemkey => r_Task.ItemKey
2523 , aname => 'INVITEE_CODE'
2524 , avalue => r_Task.INVITEE_CODE
2525 );
2526
2527
2528 wf_engine.SetItemAttrNumber( itemtype => l_itemtype
2529 , itemkey => r_Task.ItemKey
2530 , aname => 'INVITOR'
2531 , avalue => l_INVITOR
2532 );
2533
2534 wf_engine.SetItemAttrNumber( itemtype => l_itemtype
2535 , itemkey => r_Task.ItemKey
2536 , aname => 'TASK_ID'
2537 , avalue => l_TaskID
2538 );
2539
2540 wf_engine.SetItemAttrText( itemtype => l_itemtype
2541 , itemkey => r_Task.ItemKey
2542 , aname => 'TASK_NAME'
2543 , avalue => r_Task.TASK_NAME
2544 );
2545
2546 wf_engine.SetItemAttrText( itemtype => l_itemtype
2547 , itemkey => r_Task.ItemKey
2548 , aname => 'TASK_DESCRIPTION'
2549 , avalue => r_Task.TASK_DESCRIPTION
2550 );
2551
2552 --
2553 -- Start and end date/time will have to be adjusted for the recipients timezone
2554 --
2555
2556 --
2557 -- I need to get the user_id for the INVITEE
2558 --
2559 l_user_id := JTF_CAL_UTILITY_PVT.GetUserID(r_Task.INVITEE);
2560 l_DestTimezoneID := NVL(FND_PROFILE.Value_Specific( name => 'CLIENT_TIMEZONE_ID'
2561 , user_id => l_user_id
2562 )
2563 ,4 -- If not set on any level, default to PST
2564 );
2565
2566 --
2567 -- Adjust startdate from Task/Server timezone to recipient client timezone
2568 --
2569 JTF_CAL_UTILITY_PVT.AdjustForTimezone
2570 ( p_source_tz_id => r_Task.SourceTimezoneID
2571 , p_dest_tz_id => l_DestTimezoneID
2572 , p_source_day_time => r_Task.START_DATE
2573 , x_dest_day_time => l_StartDateCorrected
2574 );
2575
2576 --
2577 -- Adjust enddate from Task/Server timezone to recipient client timezone
2578 --
2579 JTF_CAL_UTILITY_PVT.AdjustForTimezone
2580 ( p_source_tz_id => r_Task.SourceTimezoneID
2581 , p_dest_tz_id => l_DestTimezoneID
2582 , p_source_day_time => r_Task.END_DATE
2583 , x_dest_day_time => l_EndDateCorrected
2584 );
2585
2586
2587 wf_engine.SetItemAttrDate( itemtype => l_itemtype
2588 , itemkey => r_Task.ItemKey
2589 , aname => 'START_DATE'
2590 , avalue => l_StartDateCorrected
2591 );
2592
2593 wf_engine.SetItemAttrDate( itemtype => l_itemtype
2594 , itemkey => r_Task.ItemKey
2595 , aname => 'END_DATE'
2596 , avalue => l_EndDateCorrected
2597 );
2598
2599 /***************************************************************************
2600 ** Start the workflow
2601 ***************************************************************************/
2602 wf_engine.StartProcess( itemtype => l_itemtype
2603 , itemkey => r_Task.ItemKey
2604 );
2605
2606 END LOOP ASSIGNEES;
2607
2608 /*****************************************************************************
2609 ** All went well return 'NO_ERROR'
2610 *****************************************************************************/
2611 resultout := 'COMPLETE:NO_ERROR';
2612
2613 EXCEPTION
2614 WHEN OTHERS
2615 THEN
2616 IF (c_Task%ISOPEN)
2617 THEN
2618 CLOSE c_Task;
2619 END IF;
2620 /*****************************************************************************
2621 ** Something went wrong return 'ERROR' and set the ERROR_MESSAGE
2622 *****************************************************************************/
2623 wf_engine.SetItemAttrText( itemtype => itemtype
2624 , itemkey => itemkey
2625 , aname => 'ERROR_MESSAGE'
2626 , avalue => 'JTF_CAL_WF_PVT.SendReminders(): ' || to_char(SQLCODE)||':'||SQLERRM
2627 );
2628
2629 resultout := 'COMPLETE:ERROR';
2630
2631 END SendReminders;
2632
2633
2634
2635
2636
2637 PROCEDURE UpdateReminders
2638 /*******************************************************************************
2639 ** Start of comments
2640 ** Procedure : UpdateReminders
2641 ** Description : Given the task ID and a new reminder date this procedure will
2642 ** update all the reminders for the appointment, should only be
2643 ** called if the reminder me or start date has changed
2644 ** Parameters :
2645 ** name direction type required?
2646 ** ---- --------- ---- ---------
2647 ** p_api_version IN NUMBER required
2648 ** p_init_msg_list IN VARCHAR2 optional
2649 ** p_commit IN VARCHAR2 optional
2650 ** x_return_status OUT VARCHAR2 optional
2651 ** x_msg_count OUT NUMBER required
2652 ** x_msg_data OUT VARCHAR2 required
2653 ** p_TaskID IN NUMBER required
2654 ** p_RemindDate IN DATE required
2655 ** Notes :
2656 ** 1) If an invitee does not exist in the WF directory a notification will
2657 ** be send to the invitor saying that the invitation was not send.
2658 ** 2) Currently invitations are only send to employees
2659 ** 3) The WFs won't be started until a commmit is done.
2660 **
2661 ** End of comments
2662 *******************************************************************************/
2663 ( p_api_version IN NUMBER
2664 , p_init_msg_list IN VARCHAR2
2665 , p_commit IN VARCHAR2
2666 , x_return_status OUT NOCOPY VARCHAR2
2667 , x_msg_count OUT NOCOPY NUMBER
2668 , x_msg_data OUT NOCOPY VARCHAR2
2669 , p_TaskID IN NUMBER -- Task ID of the appointment
2670 , p_RemindDate IN DATE -- NEW Date/Time the reminder needs to be send
2671 )
2672 IS
2673 l_api_name CONSTANT VARCHAR2(30) := 'UpdateReminderWF';
2674 l_api_version CONSTANT NUMBER := 1.0;
2675 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
2676
2677 l_ItemType VARCHAR2(8) := 'JTFTKRDR';
2678 l_AdminWFRole VARCHAR2(30);
2679
2680 CURSOR c_Task
2681 /*****************************************************************************
2682 ** Cursor to pick up all the invitees for the appointment. Also picks up
2683 ** a itemkey from the sequence, this is needed to start the workflow
2684 *****************************************************************************/
2685 (b_TaskID NUMBER
2686 )IS SELECT jta.task_assignment_id TaskassignmentID
2687 , jta.reminder_wf_item_type ReminderWFItemType
2688 , jta.reminder_wf_item_key ReminderWFItemKey
2689 , jta.resource_id INVITEE
2690 , jtb.owner_id INVITOR
2691 , jtl.task_name TASK_NAME
2692 , jtl.description TASK_DESCRIPTION
2693 , jtb.calendar_start_date START_DATE
2694 , jtb.calendar_end_date END_DATE
2695 FROM jtf_tasks_b jtb
2696 , jtf_tasks_tl jtl
2697 , jtf_task_all_assignments jta
2698 WHERE jtb.task_id = jtl.task_id
2699 AND jtl.language = userenv('LANG')
2700 AND jta.task_id = jtb.task_id
2701 AND jta.show_on_calendar = 'Y'
2702 AND jta.assignee_role = 'OWNER'
2703 AND jtb.task_id = b_TaskID;
2704
2705 l_return_status VARCHAR2(1);
2706 l_msg_count NUMBER;
2707 l_msg_data VARCHAR2(2000);
2708 l_status VARCHAR2(8);
2709 l_result VARCHAR2(30);
2710
2711
2712
2713 BEGIN
2714 /*****************************************************************************
2715 ** Standard call to check for call compatibility
2716 *****************************************************************************/
2717 IF NOT FND_API.Compatible_API_Call( l_api_version
2718 , p_api_version
2719 , l_api_name
2720 , G_PKG_NAME
2721 )
2722 THEN
2723 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2724 END IF;
2725
2726 /*****************************************************************************
2727 ** Initialize message list if p_init_msg_list is set to TRUE
2728 *****************************************************************************/
2729 IF FND_API.To_Boolean(p_init_msg_list)
2730 THEN
2731 FND_MSG_PUB.Initialize;
2732 END IF;
2733
2734 /*****************************************************************************
2735 ** Initialize API return status to success
2736 *****************************************************************************/
2737 x_return_status := FND_API.G_RET_STS_SUCCESS;
2738
2739 /*****************************************************************************
2740 ** Get Appointment details for every invitee and start a notification WF for
2741 ** all of them.
2742 *****************************************************************************/
2743 FOR r_Task IN c_Task(p_TaskID)
2744 LOOP <<ASSIGNEES>>
2745 /***************************************************************************
2746 ** Initialize the workflow
2747 ***************************************************************************/
2748 IF ( (r_Task.ReminderWFItemType IS NOT NULL)
2749 AND (r_Task.ReminderWFItemKey IS NOT NULL)
2750 )
2751 THEN
2752 -- Check if the process is stil active
2753 WF_ITEM_ACTIVITY_STATUS.Root_Status( itemtype => r_Task.ReminderWFItemType
2754 , itemkey => r_Task.ReminderWFItemKey
2755 , status => l_status
2756 , result => l_result
2757 );
2758 IF (l_status = 'ACTIVE')
2759 THEN
2760 -- abort the existing process
2761 wf_engine.AbortProcess( itemtype => r_Task.ReminderWFItemType
2762 , itemkey => r_Task.ReminderWFItemKey
2763 , process => 'DELAYSTARTREMINDER'
2764 , result => 'COMPLETE'
2765 );
2766 END IF;
2767 END IF;
2768 --Check if this is an update to Do Not Remind me
2769 IF p_RemindDate IS NOT NULL THEN
2770 StartReminders( p_api_version => 1.0
2771 , p_init_msg_list => fnd_api.g_false
2772 , p_commit => fnd_api.g_true
2773 , x_return_status => l_return_status
2774 , x_msg_count => l_msg_count
2775 , x_msg_data => l_msg_data
2776 , p_INVITOR => r_Task.INVITOR
2777 , p_TaskID => p_TaskID
2778 , p_RemindDate => p_RemindDate
2779 );
2780 END IF;
2781
2782 /***************************************************************************
2783 ** Standard check of p_commit (WF won't start until commited)
2784 ***************************************************************************/
2785 IF FND_API.To_Boolean(p_commit)
2786 THEN
2787 COMMIT WORK;
2788 END IF;
2789
2790 /***************************************************************************
2791 ** Save the workflow itemtype and item key so we can update the reminder WF
2792 ** if the start date or remind me settings change
2793 ***************************************************************************/
2794
2795 UPDATE jtf_task_all_assignments
2796 SET reminder_wf_item_type = r_Task.ReminderWFItemType
2797 , reminder_wf_item_key = r_Task.ReminderWFItemKey
2798 WHERE task_id = p_TaskID;
2799
2800 END LOOP ASSIGNEES;
2801
2802 /*****************************************************************************
2803 ** Standard call to get message count and if count is > 1, get message info
2804 *****************************************************************************/
2805 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
2806 , p_data => x_msg_data
2807 );
2808 EXCEPTION
2809 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
2810 THEN
2811 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2812 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
2813 , p_data => x_msg_data
2814 );
2815 WHEN OTHERS
2816 THEN
2817 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2818 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2819 THEN
2820 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
2821 , l_api_name
2822 );
2823 END IF;
2824 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
2825 , p_data => x_msg_data
2826 );
2827
2828 END UpdateReminders;
2829
2830
2831 FUNCTION GetDayNumber
2832 /*******************************************************************************
2833 ** Start of comments
2834 ** FUNCTION : GetDayNumber
2835 ** Description : This function will return a translated enumeration of the day
2836 ** in the month i.e. 1 will return 1st, 2 will return 2nd etc
2837 ** Parameters :
2838 ** name direction type required?
2839 ** ---- --------- ---- ---------
2840 ** p_DayNumber IN NUMBER
2841 **
2842 ** End of comments
2843 ******************************************************************************/
2844 ( p_DayNumber IN NUMBER
2845 )RETURN VARCHAR2
2846 IS
2847 CURSOR c_Day
2848 ( b_DayNumber VARCHAR2
2849 ) IS SELECT meaning
2850 FROM WF_LOOKUPS
2851 WHERE lookup_type = 'JTF_DAY_NUMBERS'
2852 AND lookup_code = b_DayNumber;
2853
2854 l_DayNumber VARCHAR2(80);
2855
2856 BEGIN
2857 OPEN c_Day(to_char(p_DayNumber));
2858 FETCH c_Day INTO l_DayNumber;
2859 CLOSE c_Day;
2860 RETURN l_DayNumber;
2861 END GetDayNumber;
2862
2863
2864 FUNCTION GetDayName
2865 /*******************************************************************************
2866 ** Start of comments
2867 ** FUNCTION : GetWeekdays
2868 ** Description : This function will return a translated string that sums up the
2869 ** days of the week for an appointment
2870 ** Parameters :
2871 ** name direction type required?
2872 ** ---- --------- ---- ---------
2873 ** p_DayCode IN VARCHAR2
2874 **
2875 ** End of comments
2876 ******************************************************************************/
2877 ( p_DayCode IN VARCHAR2
2878 )RETURN VARCHAR2
2879 IS
2880 CURSOR c_Day
2881 ( b_DayCode VARCHAR2
2882 ) IS SELECT meaning
2883 FROM WF_LOOKUPS
2884 WHERE lookup_type = 'JTF_DAY_NAMES'
2885 AND lookup_code = b_DayCode;
2886
2887 l_DayName VARCHAR2(80);
2888
2889 BEGIN
2890 OPEN c_Day(p_DayCode);
2891 FETCH c_Day INTO l_DayName;
2892 CLOSE c_Day;
2893 RETURN l_DayName;
2894 END GetDayName;
2895
2896
2897 FUNCTION GetDays
2898 /*******************************************************************************
2899 ** Start of comments
2900 ** FUNCTION : GetWeekdays
2901 ** Description : This function will return a translated string that sums up the
2902 ** days of the week for an appointment
2903 ** Parameters :
2904 ** name direction type required?
2905 ** ---- --------- ---- ---------
2906 ** p_DayCode IN VARCHAR2
2907 **
2908 ** End of comments
2909 ******************************************************************************/
2910 ( p_Sunday IN VARCHAR2
2911 , p_Monday IN VARCHAR2
2912 , p_Tuesday IN VARCHAR2
2913 , p_Wednesday IN VARCHAR2
2914 , p_Thursday IN VARCHAR2
2915 , p_Friday IN VARCHAR2
2916 , p_Saturday IN VARCHAR2
2917 )RETURN VARCHAR2
2918 IS
2919 l_days VARCHAR2(2000):= NULL;
2920
2921 BEGIN
2922 IF (p_Sunday = 'Y')
2923 THEN
2924 l_days := l_days||GetDayName('SUN');
2925 END IF;
2926
2927 IF (p_Monday = 'Y')
2928 THEN
2929 IF (l_days IS NOT NULL)
2930 THEN
2931 l_days := l_days||',';
2932 END IF;
2933 l_days := l_days||GetDayName('MON');
2934 END IF;
2935
2936 IF (p_Tuesday = 'Y')
2937 THEN
2938 IF (l_days IS NOT NULL)
2939 THEN
2940 l_days := l_days||',';
2941 END IF;
2942 l_days := l_days||GetDayName('TUE');
2943 END IF;
2944
2945 IF (p_Wednesday = 'Y')
2946 THEN
2947 IF (l_days IS NOT NULL)
2948 THEN
2949 l_days := l_days||',';
2950 END IF;
2951 l_days := l_days||GetDayName('WED');
2952 END IF;
2953
2954
2955 IF (p_Thursday = 'Y')
2956 THEN
2957 IF (l_days IS NOT NULL)
2958 THEN
2959 l_days := l_days||',';
2960 END IF;
2961 l_days := l_days||GetDayName('THU');
2962 END IF;
2963
2964
2965 IF (p_Friday = 'Y')
2966 THEN
2967 IF (l_days IS NOT NULL)
2968 THEN
2969 l_days := l_days||',';
2970 END IF;
2971 l_days := l_days||GetDayName('FRI');
2972 END IF;
2973
2974
2975 IF (p_Saturday = 'Y')
2976 THEN
2977 IF (l_days IS NOT NULL)
2978 THEN
2979 l_days := l_days||',';
2980 END IF;
2981 l_days := l_days||GetDayName('SAT');
2982 END IF;
2983
2984 RETURN l_days;
2985 END GetDays;
2986
2987 PROCEDURE GetRepeatingRule
2988 /*******************************************************************************
2989 ** Start of comments
2990 ** Procedure : GetRepeatingRule
2991 ** Description : Set the attributes for the repeating rule and determine which
2992 ** notification to send
2993 ** Parameters :
2994 ** name direction type required?
2995 ** ---- --------- ---- ---------
2996 ** itemtype IN VARCHAR2 required
2997 ** itemkey IN VARCHAR2 required
2998 ** actid IN NUMBER required
2999 ** funcmode IN VARCHAR2 required
3000 ** resultout OUT VARCHAR2 required
3001 **
3002 ** Notes :
3003 ** 1) Expects WF item attributes 'TASK_ID' to be available to this procedure.
3004 ** 2) This procedure should only be used within Workflow
3005 **
3006 ** End of comments
3007 ******************************************************************************/
3008 ( itemtype IN VARCHAR2
3009 , itemkey IN VARCHAR2
3010 , actid IN NUMBER
3011 , funcmode IN VARCHAR2
3012 , resultout OUT NOCOPY VARCHAR2
3013 )
3014 IS
3015 l_TaskID NUMBER;
3016 l_ResultType VARCHAR2(80);
3017 l_days VARCHAR2(2000);
3018 l_DayNumber VARCHAR2(2000);
3019 l_which VARCHAR2(80);
3020 l_timezone VARCHAR2(80);
3021
3022 CURSOR c_rule
3023 /*****************************************************************************
3024 ** Get the recurrance rule for the given task
3025 *****************************************************************************/
3026 (b_task_id IN NUMBER
3027 )IS SELECT jtr.occurs_which
3028 , jtr.day_of_week
3029 , jtr.date_of_month
3030 , jtr.occurs_month
3031 , jtr.occurs_uom
3032 , jtr.occurs_every
3033 , jtr.occurs_number
3034 , jtr.start_date_active
3035 , jtr.end_date_active
3036 , jtr.sunday
3037 , jtr.monday
3038 , jtr.tuesday
3039 , jtr.wednesday
3040 , jtr.thursday
3041 , jtr.friday
3042 , jtr.saturday
3043 , jtb.timezone_id
3044 , (jtb.calendar_end_date - jtb.calendar_start_date)*24*60 duration
3045 FROM jtf_task_recur_rules jtr
3046 , jtf_tasks_b jtb
3047 WHERE jtb.recurrence_rule_id = jtr.recurrence_rule_id
3048 AND jtb.task_id = b_task_id;
3049
3050 r_rule c_rule%ROWTYPE;
3051
3052 BEGIN
3053 IF (funcmode = 'RUN')
3054 THEN
3055 /***************************************************************************
3056 ** 'RUN' function from WF
3057 ***************************************************************************/
3058
3059 /***************************************************************************
3060 ** Pick up the Task ID Attribute
3061 ***************************************************************************/
3062 l_TaskID := wf_engine.GetItemAttrNumber
3063 ( itemtype => itemtype
3064 , itemkey => itemkey
3065 , aname => 'TASK_ID'
3066 );
3067
3068 /***************************************************************************
3069 ** Get the Rule if one was set
3070 ***************************************************************************/
3071 OPEN c_rule(l_TaskID);
3072 FETCH c_rule INTO r_rule;
3073 IF (c_rule%FOUND)
3074 THEN
3075
3076 WF_ENGINE.SetItemAttrNumber( itemtype => itemtype
3077 , itemkey => itemkey
3078 , aname => 'OCCURS_EVERY'
3079 , avalue => r_rule.occurs_every
3080 );
3081 WF_ENGINE.SetItemAttrText( itemtype => itemtype
3082 , itemkey => itemkey
3083 , aname => 'DURATION'
3084 , avalue => get_duration(r_rule.duration)
3085 );
3086 WF_ENGINE.SetItemAttrDate( itemtype => itemtype
3087 , itemkey => itemkey
3088 , aname => 'REPEAT_END_DATE'
3089 , avalue => r_rule.end_date_active
3090 );
3091
3092 l_Days := GetDays( p_Sunday => r_rule.sunday
3093 , p_Monday => r_rule.monday
3094 , p_Tuesday => r_rule.tuesday
3095 , p_Wednesday => r_rule.wednesday
3096 , p_Thursday => r_rule.thursday
3097 , p_Friday => r_rule.friday
3098 , p_Saturday => r_rule.saturday
3099 );
3100
3101 l_DayNumber := GetDayNumber(p_DayNumber => r_rule.date_of_month);
3102 l_which := GetDayNumber(p_DayNumber => r_rule.occurs_which);
3103 l_timezone := GetTimezone(p_timezone_id => r_rule.timezone_id);
3104
3105 WF_ENGINE.SetItemAttrText( itemtype => itemtype
3106 , itemkey => itemkey
3107 , aname => 'DAYSOFWEEK'
3108 , avalue => l_days
3109 );
3110
3111 WF_ENGINE.SetItemAttrText( itemtype => itemtype
3112 , itemkey => itemkey
3113 , aname => 'DATE_OF_MONTH'
3114 , avalue => l_DayNumber
3115 );
3116
3117 WF_ENGINE.SetItemAttrText( itemtype => itemtype
3118 , itemkey => itemkey
3119 , aname => 'OCCURS_WHICH'
3120 , avalue => l_which
3121 );
3122
3123 WF_ENGINE.SetItemAttrText( itemtype => itemtype
3124 , itemkey => itemkey
3125 , aname => 'TIMEZONE'
3126 , avalue => l_timezone
3127 );
3128
3129
3130 /*************************************************************************
3131 ** Determine the function result
3132 *************************************************************************/
3133 IF r_rule.occurs_uom = 'DAY'
3134 THEN
3135 l_ResultType:= 'REPEAT_DAY';
3136
3137 ELSIF r_rule.occurs_uom = 'WEK'
3138 THEN
3139 l_ResultType:= 'REPEAT_WEEK';
3140
3141 ELSIF r_rule.occurs_uom = 'MON'
3142 THEN
3143 IF (r_rule.date_of_month IS NOT NULL)
3144 THEN
3145 l_ResultType:= 'REPEAT_MON1';
3146 ELSE
3147 l_ResultType:= 'REPEAT_MON2';
3148 END IF;
3149 END IF;
3150 ELSE
3151 /*************************************************************************
3152 ** No rule found
3153 *************************************************************************/
3154 l_ResultType:= 'SINGLE';
3155 END IF;
3156
3157 CLOSE c_rule;
3158
3159 resultout := 'COMPLETE:'||l_ResultType;
3160
3161 ELSIF (funcmode = 'CANCEL')
3162 THEN
3163 /***************************************************************************
3164 ** 'CANCEL' function from WF
3165 ***************************************************************************/
3166 resultout := 'COMPLETE:SINGLE';
3167 ELSIF (funcmode = 'TIMEOUT')
3168 THEN
3169 /***************************************************************************
3170 ** 'TIMEOUT' function from WF
3171 ***************************************************************************/
3172 resultout := 'COMPLETE:SINGLE';
3173 ELSE
3174 /***************************************************************************
3175 ** Unknown function from WF - raise error
3176 ***************************************************************************/
3177 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3178 END IF;
3179
3180 EXCEPTION
3181 WHEN OTHERS
3182 THEN
3183 /*****************************************************************************
3184 ** Something went wrong return 'ERROR' and set the ERROR_MESSAGE
3185 *****************************************************************************/
3186 IF (c_rule%ISOPEN)
3187 THEN
3188 CLOSE c_rule;
3189 END IF;
3190
3191 wf_engine.SetItemAttrText( itemtype => itemtype
3192 , itemkey => itemkey
3193 , aname => 'ERROR_MESSAGE'
3194 , avalue => 'JTF_CAL_WF_PVT.GetRepeatingRule(): ' || to_char(SQLCODE)||':'||SQLERRM
3195 );
3196
3197 resultout := 'COMPLETE:ERROR';
3198
3199 END GetRepeatingRule;
3200
3201 PROCEDURE GetInvitationStatus
3202 /*******************************************************************************
3203 ** Start of comments
3204 ** Procedure : GetInvitationStatus
3205 ** Description : Set the attributes for the invitation status and determine which
3206 ** notification to send
3207 ** Parameters :
3208 ** name direction type required?
3209 ** ---- --------- ---- ---------
3210 ** itemtype IN VARCHAR2 required
3211 ** itemkey IN VARCHAR2 required
3212 ** actid IN NUMBER required
3213 ** funcmode IN VARCHAR2 required
3214 ** resultout OUT VARCHAR2 required
3215 **
3216 ** Notes :
3217 ** 1) Expects WF item attributes 'ASSIGNMENT_STATUS_ID' to be available to this procedure.
3218 ** 2) This procedure should only be used within Workflow
3219 ** 3) Created for 2219647
3220 **
3221 ** End of comments
3222 ******************************************************************************/
3223 ( itemtype IN VARCHAR2
3224 , itemkey IN VARCHAR2
3225 , actid IN NUMBER
3226 , funcmode IN VARCHAR2
3227 , resultout OUT NOCOPY VARCHAR2
3228 )
3229 IS
3230 l_result_type VARCHAR2(80);
3231 l_assignment_status_id NUMBER;
3232
3233
3234 BEGIN
3235 IF (funcmode = 'RUN')
3236 THEN
3237 /***************************************************************************
3238 ** 'RUN' function from WF
3239 ***************************************************************************/
3240
3241 /***************************************************************************
3242 ** Pick up the Task ID Attribute
3243 ***************************************************************************/
3244 l_assignment_status_id := wf_engine.GetItemAttrNumber
3245 ( itemtype => itemtype
3246 , itemkey => itemkey
3247 , aname => 'ASSIGNMENT_STATUS_ID'
3248 );
3249
3250
3251 /*************************************************************************
3252 ** Determine the function result
3253 *************************************************************************/
3254 IF l_assignment_status_id = 3
3255 THEN
3256 l_result_type:= 'APPROVED';
3257
3258 ELSIF l_assignment_status_id = 4
3259 THEN
3260 l_result_type:= 'REJECTED';
3261 END IF;
3262 resultout := 'COMPLETE:'||l_result_type;
3263 END IF;
3264 IF (funcmode = 'CANCEL') THEN
3265 resultout := 'COMPLETE:';
3266 RETURN;
3267 END IF;
3268 IF (funcmode = 'TIMEOUT') THEN
3269 resultout := 'COMPLETE:';
3270 RETURN;
3271 END IF;
3272 EXCEPTION
3273 WHEN OTHERS
3274 THEN
3275
3276 wf_engine.SetItemAttrText( itemtype => itemtype
3277 , itemkey => itemkey
3278 , aname => 'ERROR_MESSAGE'
3279 , avalue => 'JTF_CAL_WF_PVT.GetInvitationStatus(): ' || to_char(SQLCODE)||':'||SQLERRM
3280 );
3281 resultout := 'COMPLETE:ERROR';
3282
3283 END GetInvitationStatus;
3284
3285
3286 END JTF_CAL_WF_PVT;