[Home] [Help]
PACKAGE BODY: APPS.JTF_CAL_GRANTS_PVT
Source
1 PACKAGE BODY JTF_CAL_GRANTS_PVT AS
2 /* $Header: jtfvcgtb.pls 120.2 2006/07/28 10:36:02 sankgupt ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'JTF_CAL_GRANTS';
5
6 FUNCTION get_username
7 ( resourceId IN VARCHAR2
8 ) RETURN VARCHAR2
9 IS
10 l_username fnd_user.user_name%TYPE;
11 CURSOR C_USERNAME IS
12 select user_name from jtf_rs_resource_extns
13 where resource_id = TO_NUMBER(resourceId);
14 BEGIN
15 OPEN C_USERNAME;
16 FETCH C_USERNAME INTO l_username;
17 IF (C_USERNAME %NOTFOUND) THEN
18 CLOSE C_USERNAME;
19 --RAISE NO_DATA_FOUND;
20 RETURN -1;
21 END IF;
22 CLOSE C_USERNAME;
23 RETURN l_username;
24 END get_username;
25
26 FUNCTION get_instance_set_id
27 ( instance_set_name IN VARCHAR2
28 ) RETURN NUMBER
29 IS
30 l_instance_set_id NUMBER;
31 CURSOR C_INSTANCE_SET_ID IS
32 select instance_set_id from fnd_object_instance_sets
33 where instance_set_name = jtf_task_security_pvt.RESOURCE_TASKS_SET;
34 BEGIN
35 OPEN C_INSTANCE_SET_ID;
36 FETCH C_INSTANCE_SET_ID INTO l_instance_set_id;
37 IF (C_INSTANCE_SET_ID %NOTFOUND) THEN
38 CLOSE C_INSTANCE_SET_ID;
39 --RAISE NO_DATA_FOUND;
40 RETURN -1;
41 END IF;
42 CLOSE C_INSTANCE_SET_ID;
43 RETURN l_instance_set_id;
44 END get_instance_set_id;
45
46 PROCEDURE doRevoke
47 ( api_version IN NUMBER
48 , grant_guid IN RAW
49 )
50 IS
51 l_success VARCHAR2(1);
52 l_error NUMBER;
53 BEGIN
54 fnd_grants_pkg.revoke_grant
55 ( p_api_version => api_version
56 ,p_grant_guid => grant_guid
57 ,x_success => l_success
58 ,x_errorcode => l_error
59 );
60 /** Add error check **/
61 IF (l_success <> 'T') THEN
62 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
63 END IF;
64 END doRevoke;
65
66 PROCEDURE doCalGrant
67 ( api_version IN NUMBER
68 ,menu_name IN VARCHAR2
69 ,p_GranterID IN VARCHAR2
70 ,grantee_key IN VARCHAR2
71 )
72 IS
73 l_grant_guid RAW(16);
74 l_success VARCHAR2(1);
75 l_error NUMBER;
76 BEGIN
77 fnd_grants_pkg.grant_function( p_api_version => api_version
78 , p_menu_name => menu_name
79 , p_instance_type => CALENDAR_INSTANCE_TYPE
80 , p_object_name => CALENDAR_OBJECT
81 , p_instance_pk1_value => p_GranterID
82 , p_instance_pk2_value => CALENDAR_RESOURCE_TYPE
83 , p_grantee_type => GRANTEE_TYPE
84 , p_grantee_key => grantee_key
85 , p_start_date => SYSDATE
86 , p_end_date => NULL
87 , p_program_name => PROGRAM_NAME
88 , p_program_tag => PROGRAM_TAG
89 , x_grant_guid => l_grant_guid
90 , x_success => l_success
91 , x_errorcode => l_error
92 );
93 /** Add error check **/
94 IF (l_success <> 'T') THEN
95 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
96 END IF;
97 END doCalGrant;
98
99 PROCEDURE doTasksGrant
100 ( api_version IN NUMBER
101 ,menu_name IN VARCHAR2
102 ,l_instance_set_id IN NUMBER
103 ,p_GranterID IN VARCHAR2
104 ,grantee_key IN VARCHAR2
105 )
106 IS
107 l_grant_guid RAW(16);
108 l_success VARCHAR2(1);
109 l_error NUMBER;
110 BEGIN
111 fnd_grants_pkg.grant_function( p_api_version => api_version
112 , p_menu_name => menu_name
113 , p_instance_type => TASK_INSTANCE_TYPE
114 , p_instance_set_id => l_instance_set_id
115 , p_object_name => jtf_task_security_pvt.TASK_OBJECT
116 , p_grantee_type => GRANTEE_TYPE
117 , p_grantee_key => grantee_key
118 , p_start_date => SYSDATE
119 , p_end_date => NULL
120 , p_program_name => PROGRAM_NAME
121 , p_program_tag => PROGRAM_TAG
122 , x_grant_guid => l_grant_guid
123 , x_success => l_success
124 , x_errorcode => l_error
125 , p_parameter1 => p_GranterID
126 );
127 /** Add error check **/
128 IF (l_success <> 'T') THEN
129 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
130 END IF;
131 END doTasksGrant;
132
133 PROCEDURE UpdateGrants
134 /*******************************************************************************
135 ** Given:
136 ** - the Granter
137 ** - a list of Read Only Grantees
138 ** - a list of Full Access Grantees
139 ** This API will make sure that the proper grants are create/deleted
140 *******************************************************************************/
141 ( p_api_version IN NUMBER
142 , p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false
143 , p_commit IN VARCHAR2 DEFAULT fnd_api.g_false
144 , p_validation_level IN NUMBER DEFAULT fnd_api.g_valid_level_full
145 , x_return_status OUT NOCOPY VARCHAR2
146 , x_msg_count OUT NOCOPY NUMBER
147 , x_msg_data OUT NOCOPY VARCHAR2
148 , p_GranterID IN VARCHAR2
149 , p_ReadAccess IN VARCHAR2
150 , p_FullAccess IN VARCHAR2
151 )
152 IS
153 l_api_name CONSTANT VARCHAR2(30) := 'UpdateGrants';
154 l_api_version CONSTANT NUMBER := 1.0;
155 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
156 l_grant_guid RAW(16);
157 l_success VARCHAR2(1);
158 l_error NUMBER;
159
160 l_instance_set_id NUMBER;
161
162 l_CalAccessTbl GranteeTbl;
163 l_TaskAccessTbl GranteeTbl;
164
165 l_index1 NUMBER;
166 l_index2 NUMBER;
167 i BINARY_INTEGER;
168 j BINARY_INTEGER;
169
170 revoke_cal_found BOOLEAN:=true;
171 revoke_tasks_found BOOLEAN:=true;
172
173 /** Define a cursor C_CAL to fetch Calendar Read and Full Access for the given p_GranterID **/
174 CURSOR C_CAL IS
175 SELECT fgs.grant_guid, fgs.grantee_key, fmu.menu_name
176 FROM FND_GRANTS fgs, FND_MENUS fmu, FND_OBJECTS fos
177 WHERE fgs.object_id = fos.object_id
178 AND fos.obj_name = CALENDAR_OBJECT
179 AND fgs.menu_id = fmu.menu_id
180 AND fmu.menu_name IN (CALENDAR_READ_PRIVILEGE
181 ,CALENDAR_FULL_PRIVILEGE
182 ) -- Calendar Read and Full Access
183 AND instance_pk1_value = p_GranterID
184 AND instance_pk2_value = CALENDAR_RESOURCE_TYPE
185 AND instance_type = CALENDAR_INSTANCE_TYPE
186 AND grantee_type = GRANTEE_TYPE
187 AND program_name = PROGRAM_NAME
188 AND program_tag = 'ACCESS LEVEL';
189
190 /** Define a cursor C_CAL to fetch Tasks Read and Full Access for the given p_GranterID **/
191 CURSOR C_TASKS IS
192 SELECT fgs.grant_guid, fgs.grantee_key, fmu.menu_name
193 FROM FND_GRANTS fgs, FND_MENUS fmu, FND_OBJECTS fos
194 WHERE fgs.object_id = fos.object_id
195 AND fos.obj_name = jtf_task_security_pvt.TASK_OBJECT
196 AND fgs.menu_id = fmu.menu_id
197 AND fmu.menu_name IN (jtf_task_security_pvt.READ_PRIVILEGE
198 ,jtf_task_security_pvt.FULL_PRIVILEGE
199 ) -- Task Read and Full Access
200 AND instance_type = TASK_INSTANCE_TYPE
201 AND instance_set_id = l_instance_set_id
202 AND parameter1 = p_GranterID
203 AND grantee_type = GRANTEE_TYPE
204 AND program_name = PROGRAM_NAME
205 AND program_tag = 'ACCESS LEVEL';
206
207 BEGIN
208 /*****************************************************************************
209 ** Standard call to check for call compatibility
210 *****************************************************************************/
211 IF NOT FND_API.Compatible_API_Call( l_api_version
212 , p_api_version
213 , l_api_name
214 , G_PKG_NAME
215 )
216 THEN
217 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
218 END IF;
219
220 /*****************************************************************************
221 ** Initialize message list if p_init_msg_list is set to TRUE
222 *****************************************************************************/
223 IF FND_API.To_Boolean(p_init_msg_list)
224 THEN
225 FND_MSG_PUB.Initialize;
226 END IF;
227
228 /*****************************************************************************
229 ** Initialize API return status to success
230 *****************************************************************************/
231 x_return_status := FND_API.G_RET_STS_SUCCESS;
232
233 /*****************************************************************************
234 ** Submit JSP will return a comma delimited string, need to break it up
235 ** Merge p_ReadAccess and p_FullAccess into one table of records
236 ** l_CalAccessTbl for Calendar, l_TaskAccessTbl for Tasks
237 *****************************************************************************/
238 i := 1;
239 l_index1 := 1;
240 l_index2 := 1;
241
242 WHILE length(p_ReadAccess) <> 0
243 LOOP <<READ_ACCESS>>
244 l_index2 := instr(p_ReadAccess
245 ,','
246 ,l_index1
247 ,1
248 );
249 IF (l_index2 = 0)
250 THEN
251 l_index2 := length(p_ReadAccess)+1;
252 END IF;
253
254 l_CalAccessTbl(i).GranteeKey := substr( p_ReadAccess
255 , l_index1
256 ,(l_index2-l_index1)
257 );
258 l_CalAccessTbl(i).AccessLevel := CALENDAR_READ_PRIVILEGE;
259
260 /** Get the username for a given resource id **/
261 l_TaskAccessTbl(i).GranteeKey := get_username(l_CalAccessTbl(i).GranteeKey);
262 IF (l_TaskAccessTbl(i).GranteeKey = '-1') THEN
263 fnd_message.set_name('JTF', 'JTF_CAL_USERNAME_NOT_FOUND');
264 fnd_msg_pub.add;
265 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
266 END IF;
267
268 l_TaskAccessTbl(i).AccessLevel := jtf_task_security_pvt.READ_PRIVILEGE;
269
270 i := i + 1;
271 l_index1 := l_index2 + 1;
272 IF (l_index2 > length(p_ReadAccess))
273 THEN
274 EXIT;
275 END IF;
276 END LOOP READ_ACCESS;
277
278 j := 1;
279 l_index1 := 1;
280 l_index2 := 1;
281 WHILE length(p_FullAccess) <> 0
282 LOOP <<FULL_ACCESS>>
283 l_index2 := instr(p_FullAccess
284 ,','
285 ,l_index1
286 ,1
287 );
288 IF (l_index2 = 0)
289 THEN
290 l_index2 := length(p_FullAccess)+1;
291 END IF;
292
293 l_CalAccessTbl(i).GranteeKey := substr( p_FullAccess
294 , l_index1
295 ,(l_index2-l_index1)
296 );
297 l_CalAccessTbl(i).AccessLevel := CALENDAR_FULL_PRIVILEGE;
298 /** Get the username for a given resource id **/
299 l_TaskAccessTbl(i).GranteeKey := get_username(l_CalAccessTbl(i).GranteeKey);
300 IF (l_TaskAccessTbl(i).GranteeKey = '-1') THEN
301 fnd_message.set_name('JTF', 'JTF_CAL_USERNAME_NOT_FOUND');
302 fnd_msg_pub.add;
303 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
304 END IF;
305
306 l_TaskAccessTbl(i).AccessLevel := jtf_task_security_pvt.FULL_PRIVILEGE;
307
308 i := i + 1;
309 j := j + 1;
310 l_index1 := l_index2 + 1;
311 IF (l_index2 > length(p_FullAccess))
312 THEN
313 EXIT;
314 END IF;
315 END LOOP FULL_ACCESS;
316
317 /** Get the instance_set_id for instance_name RESOURCE_TASKS_SET **/
318 /** It is required in fnd_grants function **/
319 l_instance_set_id := get_instance_set_id(jtf_task_security_pvt.RESOURCE_TASKS_SET);
320 IF ( l_instance_set_id = -1) THEN
321 fnd_message.set_name('JTF', 'JTF_CAL_INST_SET_ID_NOT_FOUND');
322 fnd_msg_pub.add;
323 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
324 END IF;
325
326 /** Grant and Revoke Calendar Read and Full Access **/
327 FOR r_cal IN C_CAL LOOP
328 revoke_cal_found := true;
329 FOR j IN 1 .. NVL(l_CalAccessTbl.LAST,0)
330 LOOP <<ADD_CAL_ACCESS>>
331 IF ((r_cal.grantee_key = l_CalAccessTbl(j).GranteeKey) AND (r_cal.menu_name = l_CalAccessTbl(j).AccessLevel)) THEN
332 /** The resource is already in the database **/
333 /** And the acccess level is the same for the resource **/
334 l_CalAccessTbl(j).GrantType:=0;
335 revoke_cal_found := false;
336 EXIT;
337 END IF;
338 END LOOP ADD_CAL_ACCESS;
339
340 /** The resource is not in the new list, revoke Calendar Read or Full Access **/
341 IF (revoke_cal_found) THEN
342 doRevoke(l_api_version, r_cal.grant_guid);
343 END IF;
344 END LOOP;
345
346
347 /** Loop the l_CalAccessTbl table to grant Read or Full Access **/
348 FOR k IN 1 .. NVL(l_CalAccessTbl.LAST,0)
349 LOOP <<ADD_CAL_ACCESS>>
350 IF ((l_CalAccessTbl(k).GrantType = 1) AND (l_CalAccessTbl(k).AccessLevel=CALENDAR_READ_PRIVILEGE)) THEN
351 /** Grant Calendar READ Access **/
352 doCalGrant(l_api_version, CALENDAR_READ_PRIVILEGE, p_GranterID, l_CalAccessTbl(k).GranteeKey);
353 ELSIF ((l_CalAccessTbl(k).GrantType = 1) AND (l_CalAccessTbl(k).AccessLevel=CALENDAR_FULL_PRIVILEGE)) THEN
354 /** Grant Calendar Full Access **/
355 doCalGrant(l_api_version, CALENDAR_FULL_PRIVILEGE, p_GranterID,l_CalAccessTbl(k).GranteeKey);
356 END IF;
357 END LOOP ADD_CAL_ACCESS;
358
359 /** Grant and Revoke Task Read Access **/
360 FOR r_tasks IN C_TASKS LOOP
361 revoke_tasks_found := true;
362 FOR m IN 1 .. NVL(l_TaskAccessTbl.LAST,0)
363 LOOP <<ADD_TASKS_ACCESS>>
364 IF ((r_tasks.grantee_key = l_TaskAccessTbl(m).GranteeKey) AND (r_tasks.menu_name = l_TaskAccessTbl(m).AccessLevel)) THEN
365 /** The resource is already in the database **/
366 /** The acccess level is the same for the resource **/
367 l_TaskAccessTbl(m).GrantType:=0;
368 revoke_tasks_found := false;
369 EXIT;
370 END IF;
371 END LOOP ADD_TASKS_ACCESS;
372
373 /** The resource is not in the new list, revoke Task Read or Full Access **/
374 IF (revoke_tasks_found) THEN
375 doRevoke(l_api_version, r_tasks.grant_guid);
376 END IF;
377 END LOOP;
378
379
380 FOR n IN 1 .. NVL(l_TaskAccessTbl.LAST,0)
381 LOOP <<ADD_TASKS_ACCESS>>
382 IF ((l_TaskAccessTbl(n).GrantType = 1) AND (l_TaskAccessTbl(n).AccessLevel= jtf_task_security_pvt.READ_PRIVILEGE) ) THEN
383 /** Grant Tasks Read Access **/
384 doTasksGrant(l_api_version,jtf_task_security_pvt.READ_PRIVILEGE,l_instance_set_id, p_GranterID,l_TaskAccessTbl(n).GranteeKey);
385 ELSIF ((l_TaskAccessTbl(n).GrantType = 1) AND (l_TaskAccessTbl(n).AccessLevel=jtf_task_security_pvt.FULL_PRIVILEGE) ) THEN
386 /** Grant Tasks Full Access **/
387 doTasksGrant(l_api_version,jtf_task_security_pvt.FULL_PRIVILEGE,l_instance_set_id, p_GranterID,l_TaskAccessTbl(n).GranteeKey);
388 END IF;
389 END LOOP ADD_TASKS_ACCESS;
390
391 /*****************************************************************************
392 ** Standard check of p_commit
393 *****************************************************************************/
394 IF FND_API.To_Boolean(p_commit)
395 THEN
396 COMMIT WORK;
397 END IF;
398
399 /*****************************************************************************
400 ** Standard call to get message count and if count is > 1, get message info
401 *****************************************************************************/
402 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
403 , p_data => x_msg_data
404 );
405
406 EXCEPTION
407 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
408 THEN
409 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
410 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
411 , p_data => x_msg_data
412 );
413 WHEN OTHERS
414 THEN
415 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
416 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
417 THEN
418 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
419 , l_api_name
420 );
421 END IF;
422 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
423 , p_data => x_msg_data
424 );
425
426 END UpdateGrants;
427
428 PROCEDURE RevokeGrants
429 ( p_api_version IN NUMBER
430 , p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false
431 , p_commit IN VARCHAR2 DEFAULT fnd_api.g_false
432 , x_return_status OUT NOCOPY VARCHAR2
433 , x_msg_count OUT NOCOPY NUMBER
434 , x_msg_data OUT NOCOPY VARCHAR2
435 , p_resourceId IN VARCHAR2
436 , p_groupId IN VARCHAR2
437 )
438 IS
439 l_api_name CONSTANT VARCHAR2(30) := 'RevokeGrants';
440 l_api_version CONSTANT NUMBER := 1.0;
441 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
442 l_return_status VARCHAR2(1);
443 l_msg_count NUMBER;
444 l_msg_data VARCHAR2(2000);
445 l_grant_guid RAW(16);
446
447 BEGIN
448 x_return_status := FND_API.G_RET_STS_SUCCESS;
449
450 l_grant_guid := get_grant_guid(p_resourceId => p_resourceId,
451 p_groupId => p_groupId);
452 IF (l_grant_guid IS NOT NULL) THEN
453 fnd_grants_pkg.revoke_grant
454 (
455 p_api_version => l_api_version,
456 p_grant_guid => l_grant_guid,
457 x_success => l_return_status,
458 x_errorcode => x_msg_data
459 );
460
461 IF (l_return_status <> FND_API.G_TRUE)
462 THEN
463 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
464 END IF;
465 END IF;
466
467 /*****************************************************************************
468 ** Standard call to get message count and if count is > 1, get message info
469 *****************************************************************************/
470 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
471 , p_data => x_msg_data
472 );
473 EXCEPTION
474 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
475 THEN
476 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
477 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
478 , p_data => x_msg_data
479 );
480 WHEN OTHERS
481 THEN
482 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
483 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
484 THEN
485 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
486 , l_api_name
487 );
488 END IF;
489 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
490 , p_data => x_msg_data
491 );
492
493 END RevokeGrants;
494
495 PROCEDURE InvokeGrants
496 ( p_api_version IN NUMBER
497 , p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false
498 , p_commit IN VARCHAR2 DEFAULT fnd_api.g_false
499 , x_return_status OUT NOCOPY VARCHAR2
500 , x_msg_count OUT NOCOPY NUMBER
501 , x_msg_data OUT NOCOPY VARCHAR2
502 , p_resourceId IN VARCHAR2
503 , p_groupId IN VARCHAR2
504 , p_accesslevel IN VARCHAR2
505 )
506 IS
507 l_api_name CONSTANT VARCHAR2(30) := 'InvokeGrants';
508 l_api_version CONSTANT NUMBER := 1.0;
509 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
510 l_return_status VARCHAR2(1);
511 l_msg_count NUMBER;
512 l_msg_data VARCHAR2(2000);
513 l_grant_guid RAW(16);
514 l_read_access CONSTANT VARCHAR2(30) := 'JTF_CAL_READ_ACCESS';
515 l_full_access CONSTANT VARCHAR2(30) := 'JTF_CAL_FULL_ACCESS';
516 l_return BOOLEAN;
517
518 BEGIN
519 x_return_status := FND_API.G_RET_STS_SUCCESS;
520 l_return := has_access_level( p_resourceId => p_resourceId
521 , p_groupId => p_groupId
522 );
523
524 /** Check whether the requstor already has an access level -- Full or Readonly
525 ** If yes, do not grant Readonly Access to the requestor.
526 ** If not, grant Readonly Access to the requestor first */
527
528 IF (l_return = false)
529 THEN
530 fnd_grants_pkg.grant_function( p_api_version => 1.0
531 , p_menu_name => l_read_access
532 , p_instance_type => 'INSTANCE'
533 , p_object_name => 'JTF_TASK_RESOURCE'
534 , p_instance_pk1_value => nvl(p_groupId,1)
535 , p_instance_pk2_value => 'RS_GROUP'
536 , p_grantee_type => 'USER'
537 , p_grantee_key => p_resourceId
538 , p_start_date => SYSDATE
539 , p_end_date => NULL
540 , p_program_name => 'CALENDAR'
541 , p_program_tag => 'ACCESS LEVEL'
542 , x_grant_guid => l_grant_guid
543 , x_success => l_return_status
544 , x_errorcode => l_msg_data
545 );
546 END IF;
547
548 /*****************************************************************************
549 ** Grant Administrator privs to the requestor
550 *****************************************************************************/
551 fnd_grants_pkg.grant_function( p_api_version => 1.0
552 , p_menu_name => p_accesslevel
553 , p_instance_type => 'INSTANCE'
554 , p_object_name => 'JTF_TASK_RESOURCE'
555 , p_instance_pk1_value => nvl(p_groupId,1)
556 , p_instance_pk2_value => 'RS_GROUP'
557 , p_grantee_type => 'USER'
558 , p_grantee_key => p_resourceId
559 , p_start_date => SYSDATE
560 , p_end_date => NULL
561 , p_program_name => 'CALENDAR'
562 , p_program_tag => 'ACCESS LEVEL'
563 , x_grant_guid => l_grant_guid
564 , x_success => l_return_status
565 , x_errorcode => l_msg_data
566 );
567 IF (l_return_status <> FND_API.G_TRUE)
568 THEN
569 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
570 END IF;
571
572 /*****************************************************************************
573 ** Standard call to get message count and if count is > 1, get message info
574 *****************************************************************************/
575 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
576 , p_data => x_msg_data
577 );
578
579 EXCEPTION
580 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
581 THEN
582 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
583 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
584 , p_data => x_msg_data
585 );
586 WHEN OTHERS
587 THEN
588 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
589 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
590 THEN
591 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME
592 , l_api_name
593 );
594 END IF;
595 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
596 , p_data => x_msg_data
597 );
598
599 END InvokeGrants;
600
601
602 FUNCTION get_grant_guid
603 ( p_resourceId IN VARCHAR2
604 , p_groupId IN VARCHAR2
605 ) RETURN RAW
606 IS
607 l_grant_guid RAW(16);
608
609 /*cursor C is
610 SELECT grant_guid FROM FND_GRANTS
611 WHERE grantee_key = p_resourceId
612 AND instance_pk1_value = p_groupId
613 AND instance_pk2_value = 'RS_GROUP'
614 AND program_name = 'CALENDAR'; */
615
616 cursor C is
617 SELECT fgs.grant_guid
618 FROM FND_GRANTS fgs, FND_MENUS fmu
619 WHERE grantee_key = p_resourceId
620 AND instance_pk1_value = p_groupId
621 AND instance_pk2_value = 'RS_GROUP'
622 AND program_name = 'CALENDAR'
623 AND fgs.menu_id = fmu.menu_id
624 AND fmu.menu_name = 'JTF_CAL_ADMIN_ACCESS';
625
626 BEGIN
627 open C;
628 fetch C into l_grant_guid;
629 if (C%NOTFOUND) then
630 close C;
631 raise NO_DATA_FOUND;
632 return NULL;
633 end if;
634 close C;
635 return l_grant_guid;
636 END get_grant_guid;
637
638 FUNCTION has_access_level
639 ( p_resourceId IN VARCHAR2
640 , p_groupId IN VARCHAR2
641 ) RETURN BOOLEAN
642 IS
643 l_count NUMBER;
644 l_return BOOLEAN;
645 cursor c_Count is
646 SELECT 1 FROM FND_GRANTS
647 WHERE grantee_key = p_resourceId
648 AND instance_pk1_value = p_groupId
649 AND instance_pk2_value = 'RS_GROUP'
650 AND program_name = 'CALENDAR';
651
652 BEGIN
653 l_return := false;
654
655 IF (c_Count%ISOPEN)
656 THEN CLOSE c_Count;
657 END IF;
658
659 open c_Count;
660 fetch c_Count into l_count;
661 IF(c_Count%NOTFOUND) then
662 return false;
663 END IF;
664
665 IF (l_count >= 1) THEN
666 l_return := true;
667 END IF;
668
669 IF (c_Count%ISOPEN)
670 THEN CLOSE c_Count;
671 END IF;
672
673 RETURN l_return;
674 END has_access_level;
675
676 END JTF_CAL_GRANTS_PVT;