[Home] [Help]
PACKAGE BODY: APPS.HZ_DSS_GRANTS_PUB
Source
4 ----------------------------------
1 PACKAGE BODY HZ_DSS_GRANTS_PUB AS
2 /*$Header: ARHPDSXB.pls 120.4 2006/02/02 22:22:39 jhuang noship $ */
3
5 -- declaration of global variables
6 ----------------------------------
7
8 G_API_NAME VARCHAR2(30) := 'HZ_DSS_GRANTS_PUB';
9 G_DSS_RESPONSIBILITY_ID NUMBER(15);
10
11 -------------------------------------------------
12 -- private procedures and functions
13 -------------------------------------------------
14
15 /**
16 * FUNCTION obtain_dss_instance_set_id
17 *
18 * DESCRIPTION
19 *
20 * Obtains the Instance Set ID corresponding to the Object Instance Set
21 * for the given Data Sharing Group, against object HZ_DSS_GROUPS.
22 * This is a "special" Object Instance Sets meant to record the general
23 * grants to a Data Sharing Group, irrespective of the actual table being
24 * protected.
25 *
26 * If such an Object Instance Set cannot be found, one is created.
27 *
28 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
29 *
30 * ARGUMENTS
31 *
32 * NOTES
33 *
34 * MODIFICATION HISTORY
35 *
36 * 09-03-2002 Chris Saulit o Created.
37 */
38
39 FUNCTION obtain_dss_instance_set_id (
40 p_dss_group_code IN VARCHAR2
41 ) RETURN NUMBER IS
42
43 l_instance_set_id NUMBER;
44 l_object_id NUMBER;
45 l_rowid VARCHAR2(64);
46 l_obj_name VARCHAR2(30) := 'HZ_DSS_GROUPS';
47
48 CURSOR c_dss_ois (
49 p_dss_group_code IN VARCHAR2
50 ) IS
51 SELECT fois.instance_set_id
52 FROM fnd_object_instance_sets fois,
53 fnd_objects fo
54 WHERE fo.obj_name = l_obj_name
55 AND fo.object_id = fois.object_id
56 AND fois.predicate LIKE '%''' || p_dss_group_code || '''%';
57
58 CURSOR c_obj (
59 p_obj_name IN VARCHAR2
60 ) IS
64
61 SELECT object_id
62 FROM fnd_objects
63 WHERE obj_name = p_obj_name;
65 BEGIN
66
67 OPEN c_dss_ois(p_dss_group_code);
68 FETCH c_dss_ois INTO l_instance_set_id;
69 IF c_dss_ois%NOTFOUND THEN
70 CLOSE c_dss_ois;
71
72 --
73 -- Object Instance Set not found ... create it!
74 --
75 OPEN c_obj(l_obj_name);
76 FETCH c_obj INTO l_object_id;
77 IF c_obj%NOTFOUND THEN
78 CLOSE c_obj;
79 -- Base Object not found!!! This means seed data is not found.
80 ELSE
81 CLOSE c_obj;
82 --
83 -- Create the Object Instance Set!
84 --
85 SELECT fnd_object_instance_sets_s.NEXTVAL INTO l_instance_set_id FROM DUAL;
86
87 fnd_object_instance_sets_pkg.insert_row(
88 x_rowid => l_rowid,
89 x_instance_set_id => l_instance_set_id,
90 x_instance_set_name => 'HZ_DSS_BASE_' || l_instance_set_id,
91 x_object_id => l_object_id,
92 x_predicate => 'DSS_GROUP_CODE = ''' || p_dss_group_code ||'''',
93 x_display_name => 'HZ_DSS_BASE_' || l_instance_set_id,
94 x_description => 'HZ_DSS_BASE_' || l_instance_set_id,
95 x_creation_date => hz_utility_v2pub.creation_date,
96 x_created_by => hz_utility_v2pub.created_by,
97 x_last_update_date => hz_utility_v2pub.last_update_date,
98 x_last_updated_by => hz_utility_v2pub.last_updated_by,
99 x_last_update_login => hz_utility_v2pub.last_update_login
100 );
101
102 END IF;
103 ELSE
104 CLOSE c_dss_ois;
105 END IF;
106
107 RETURN l_instance_set_id;
108
109 EXCEPTION
110 WHEN OTHERS THEN
111 RETURN NULL;
112
113 END obtain_dss_instance_set_id;
114
115
116 /**
117 * PROCEDURE do_create_fnd_grant
118 *
119 * DESCRIPTION
120 *
121 * Creates a Grant to a Data Sharing Group for a particular data operation.
122 *
123 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
124 *
125 *
126 * ARGUMENTS
127 *
128 *
129 * NOTES
130 *
131 * MODIFICATION HISTORY
132 *
133 * 08-13-2002 Chris Saulit o Created.
134 *
135 */
136
137 PROCEDURE do_create_fnd_grant (
138 p_dss_group_code IN VARCHAR2,
139 p_data_operation_code IN VARCHAR2,
140 p_dss_grantee_type IN VARCHAR2,
141 p_dss_grantee_key IN VARCHAR2,
142 p_grant_start_date IN DATE DEFAULT NULL,
143 p_grant_end_date IN DATE DEFAULT NULL
144 ) IS
145
146 g_procedure_name VARCHAR2(30) := 'DO_CREATE_FND_GRANT';
147 l_fnd_grant_guid RAW(100);
148 l_fnd_success VARCHAR2(1);
149 l_fnd_errorcode NUMBER;
150 l_base_instance_set_id NUMBER;
151 l_fnd_grantee_type VARCHAR2(8);
152 l_fnd_grantee_key VARCHAR2(240);
153 l_menu_name VARCHAR2(30);
154 l_grantee_key_cnt NUMBER;
155 l_end_date DATE;
156
157 CURSOR c_secured_entities(
158 p_dss_group_code IN VARCHAR2
159 ) IS
160 SELECT dse.dss_instance_set_id,
161 fo.obj_name,
162 dse.status
163 FROM hz_dss_secured_entities dse,
164 fnd_object_instance_sets fois,
165 fnd_objects fo
166 WHERE dse.dss_group_code = p_dss_group_code
167 AND fois.instance_set_id = dse.dss_instance_set_id
168 AND fo.object_id = fois.object_id ;
169
170 BEGIN
171
172 -- Given a Data Operation Code, determine to which Menu we should be granting
173 l_menu_name := 'HZ_DSS_' || p_data_operation_code;
174
175 --
176 -- Validate the grantee information
177 --
178
179 IF p_dss_grantee_type NOT IN ('GROUP','USER','GLOBAL') THEN
180 FND_MESSAGE.SET_NAME('FND','FND_GENERIC_MESSAGE');
181 FND_MESSAGE.SET_TOKEN('MESSAGE','Grantee type must be one of: GROUP, USER, GLOBAL');
182 FND_MSG_PUB.ADD;
183 RAISE FND_API.G_EXC_ERROR;
184 END IF;
185
186 --
187 -- Translate the "DSS" Grantee information into appropriate value for FND
188 --
189
190 l_fnd_grantee_type := SUBSTRB(p_dss_grantee_type,1,8);
191 l_fnd_grantee_key := SUBSTRB(p_dss_grantee_key,1,240);
192
193 --
194 -- Validate grantee key information
195 --
196 /*
197 IF l_fnd_grantee_type = 'USER' THEN
198 -- Validate against FND_USER
199 BEGIN
200 SELECT 1
201 INTO l_grantee_key_cnt
202 FROM fnd_user
203 WHERE user_name = l_fnd_grantee_key
204 AND (start_date IS NULL OR start_date < SYSDATE)
205 AND (end_date IS NULL OR end_date > SYSDATE)
206 AND ROWNUM = 1;
207
208 EXCEPTION
209 WHEN NO_DATA_FOUND THEN
210 FND_MESSAGE.SET_NAME('FND','FND_INVALID_USER');
211 FND_MESSAGE.SET_TOKEN('USER_NAME',p_dss_grantee_key);
212 FND_MSG_PUB.ADD;
213 RAISE FND_API.G_EXC_ERROR;
214 END;
215 ELSIF l_fnd_grantee_type = 'GROUP' THEN
216 -- validate against WF_ROLES
217 BEGIN
218 SELECT 1
219 INTO l_grantee_key_cnt
220 FROM wf_roles
221 WHERE name = l_fnd_grantee_key
222 AND orig_system LIKE 'FND_RESP%'
226 WHEN NO_DATA_FOUND THEN
223 AND ROWNUM = 1;
224
225 EXCEPTION
227 FND_MESSAGE.SET_NAME('AR','HZ_DSS_INVALID_RESP');
228 FND_MESSAGE.SET_TOKEN('RESP',p_dss_grantee_key);
229 FND_MSG_PUB.ADD;
230 RAISE FND_API.G_EXC_ERROR;
231 END;
232
233 END IF;
234 */
235 --
236 --
237 -- Create a "base" grant to represent the user's privilege on the Data Sharing Group
238 --
239
240 l_base_instance_set_id := obtain_dss_instance_set_id (p_dss_group_code);
241
242 fnd_grants_pkg.grant_function (
243 p_api_version => 1,
244 p_menu_name => l_menu_name,
245 p_object_name => 'HZ_DSS_GROUPS',
246 p_instance_type => 'SET',
247 p_instance_set_id => l_base_instance_set_id,
248 p_grantee_type => l_fnd_grantee_type, -- e.g. USER GROUP
249 p_grantee_key => l_fnd_grantee_key,
250 p_start_date => SYSDATE,
251 p_end_date => NULL,
252 p_program_name => G_API_NAME,
253 -- The Data Sharing Group is stored in the grant TAG!
254 p_program_tag => p_dss_group_code,
255 x_grant_guid => l_fnd_grant_guid,
256 x_success => l_fnd_success,
257 x_errorcode => l_fnd_errorcode
258 );
259
260 IF l_fnd_success <> FND_API.G_TRUE THEN
261 RAISE FND_API.G_EXC_ERROR;
262 END IF;
263
264 --
265 -- Replicate the same grant to any entities secured by this Data Sharing Group
266 --
267
268 FOR l_secured_entity IN c_secured_entities(p_dss_group_code) LOOP
269
270 IF l_secured_entity.status = 'A' THEN
271 l_end_date := p_grant_end_date;
272 ELSE
273 l_end_date := SYSDATE;
274 END IF;
275
276 fnd_grants_pkg.grant_function (
277 p_api_version => 1,
278 p_menu_name => l_menu_name,
279 p_object_name => l_secured_entity.obj_name,
280 p_instance_type => 'SET',
281 p_instance_set_id => l_secured_entity.dss_instance_set_id,
282 p_grantee_type => l_fnd_grantee_type, -- e.g. USER GROUP
283 p_grantee_key => l_fnd_grantee_key,
284 p_start_date => NVL(p_grant_start_date, SYSDATE),
285 p_end_date => l_end_date,
286 p_program_name => G_API_NAME,
287 -- The Data Sharing Group is stored in the grant TAG!
288 p_program_tag => p_dss_group_code,
289 x_grant_guid => l_fnd_grant_guid,
290 x_success => l_fnd_success,
291 x_errorcode => l_fnd_errorcode
292 );
293
294 IF l_fnd_success <> FND_API.G_TRUE THEN
295 RAISE FND_API.G_EXC_ERROR;
296 END IF;
297
298 END LOOP;
299
300 END do_create_fnd_grant;
301
302
303 /**
304 * PROCEDURE do_revoke_fnd_grant
305 *
306 * DESCRIPTION
307 *
308 * Revokes a Grant to a Data Sharing Group for a particular data operation.
309 *
310 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
311 *
312 *
313 * ARGUMENTS
314 *
315 *
316 * NOTES
317 *
318 * MODIFICATION HISTORY
319 *
320 * 09-26-2002 Chris Saulit o Created.
321 *
322 */
323
324 PROCEDURE do_revoke_fnd_grant (
325 p_dss_group_code IN VARCHAR2,
326 p_data_operation_code IN VARCHAR2,
327 p_dss_grantee_type IN VARCHAR2,
328 p_dss_grantee_key IN VARCHAR2
329 ) IS
330
331 g_procedure_name VARCHAR2(30) := 'DO_REVOKE_FND_GRANT';
332
333 CURSOR c_fnd_grant IS
334 SELECT grant_guid
335 FROM fnd_grants grants,
336 fnd_menus menu
337 WHERE grants.menu_id = menu.menu_id
338 AND menu.menu_name = 'HZ_DSS_'||p_data_operation_code
339 AND program_tag = p_dss_group_code
340 AND grantee_type = p_dss_grantee_type
341 AND (p_dss_grantee_type = 'GLOBAL' OR
342 p_dss_grantee_type <> 'GLOBAL' AND
343 grantee_key = p_dss_grantee_key);
344
345 l_fnd_grant_guid RAW(100);
346 l_fnd_success VARCHAR2(1);
347 l_fnd_errorcode NUMBER;
348
349 BEGIN
350
351 --
352 -- Get the guids of the grant that we wish to revoke, then
353 -- call the FND function to revoke the grant.
354 --
355
356 OPEN c_fnd_grant;
357 LOOP
358 FETCH c_fnd_grant INTO l_fnd_grant_guid;
359 IF c_fnd_grant%NOTFOUND THEN
360 EXIT;
361 END IF;
362
363 fnd_grants_pkg.revoke_grant(
364 p_api_version => 1,
365 p_grant_guid => l_fnd_grant_guid,
366 x_success => l_fnd_success,
367 x_errorcode => l_fnd_errorcode
368 );
369
370 IF l_fnd_success <> FND_API.G_TRUE THEN
371 CLOSE c_fnd_grant;
372 RAISE FND_API.G_EXC_ERROR;
373 END IF;
374 END LOOP;
375 CLOSE c_fnd_grant;
376
377 END do_revoke_fnd_grant;
378
379
383
380 --------------------------------------
381 -- public procedures and functions
382 --------------------------------------
384 /**
385 * PROCEDURE create_grant
386 *
387 * DESCRIPTION
388 *
389 * Creates a set of Grants to a Data Sharing Group.
390 * This signature matches the UI and corresponds to a "UI Grant Create".
391 *
392 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
393 *
394 * ARGUMENTS
395 *
396 * NOTES
397 *
398 * MODIFICATION HISTORY
399 *
400 * 09-03-2002 Chris Saulit o Created.
401 *
402 */
403
404 PROCEDURE create_grant (
405 p_init_msg_list IN VARCHAR2,
406 p_dss_group_code IN VARCHAR2,
407 p_dss_grantee_type IN VARCHAR2,
408 p_dss_grantee_key IN VARCHAR2,
409 p_view_flag IN VARCHAR2,
410 p_insert_flag IN VARCHAR2,
411 p_update_flag IN VARCHAR2,
412 p_delete_flag IN VARCHAR2,
413 p_admin_flag IN VARCHAR2,
414 x_return_status OUT NOCOPY VARCHAR2,
415 x_msg_count OUT NOCOPY NUMBER,
416 x_msg_data OUT NOCOPY VARCHAR2
417 ) IS
418
419 G_PROCEDURE_NAME VARCHAR2(30) := 'CREATE_GRANT';
420
421 CURSOR c_dss_groups IS
422 SELECT status
423 FROM hz_dss_groups_b
424 WHERE dss_group_code = p_dss_group_code;
425
426 l_dsg_status VARCHAR2(1);
427 l_end_date DATE;
428
429 BEGIN
430
431 -- initialize message list if p_init_msg_list is set to TRUE.
432 IF p_init_msg_list IS NOT NULL AND
433 FND_API.to_Boolean(p_init_msg_list)
434 THEN
435 FND_MSG_PUB.initialize;
436 END IF;
437
438 -- initialize API return status to success.
439 x_return_status := FND_API.G_RET_STS_SUCCESS;
440
441 -- standard start of API savepoint
442 SAVEPOINT create_grant;
443
444 OPEN c_dss_groups;
445 FETCH c_dss_groups INTO l_dsg_status;
446 CLOSE c_dss_groups;
447
448 IF l_dsg_status IS NULL THEN
449 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_GR_CODE_INVALID');
450 FND_MSG_PUB.ADD;
451 RAISE FND_API.G_EXC_ERROR;
452 END IF;
453
454 IF l_dsg_status <> 'A' THEN
455 l_end_date := SYSDATE;
456 END IF;
457
458 IF p_view_flag = 'Y' THEN
459 do_create_fnd_grant (
460 p_dss_group_code => p_dss_group_code,
461 p_data_operation_code => 'SELECT',
462 p_dss_grantee_type => p_dss_grantee_type,
463 p_dss_grantee_key => p_dss_grantee_key,
464 p_grant_start_date => SYSDATE,
465 p_grant_end_date => l_end_date
466 );
467 END IF;
468
469 IF p_insert_flag = 'Y' THEN
470 do_create_fnd_grant (
471 p_dss_group_code => p_dss_group_code,
472 p_data_operation_code => 'INSERT',
473 p_dss_grantee_type => p_dss_grantee_type,
474 p_dss_grantee_key => p_dss_grantee_key,
475 p_grant_start_date => SYSDATE,
476 p_grant_end_date => l_end_date
477 );
478 END IF;
479
480 IF p_update_flag = 'Y' THEN
481 do_create_fnd_grant (
482 p_dss_group_code => p_dss_group_code,
483 p_data_operation_code => 'UPDATE',
484 p_dss_grantee_type => p_dss_grantee_type,
485 p_dss_grantee_key => p_dss_grantee_key,
486 p_grant_start_date => SYSDATE,
487 p_grant_end_date => l_end_date
488 );
489 END IF;
490
491 IF p_delete_flag = 'Y' THEN
492 do_create_fnd_grant (
493 p_dss_group_code => p_dss_group_code,
494 p_data_operation_code => 'DELETE',
495 p_dss_grantee_type => p_dss_grantee_type,
496 p_dss_grantee_key => p_dss_grantee_key,
497 p_grant_start_date => SYSDATE,
498 p_grant_end_date => l_end_date
499 );
500 END IF;
501
502 -- standard call to get message count and if count is 1, get message info.
503 FND_MSG_PUB.Count_And_Get(
504 p_encoded => FND_API.G_FALSE,
505 p_count => x_msg_count,
506 p_data => x_msg_data);
507
508 EXCEPTION
509 WHEN FND_API.G_EXC_ERROR THEN
510 ROLLBACK TO create_grant ;
511 x_return_status := FND_API.G_RET_STS_ERROR;
512
513 FND_MSG_PUB.Count_And_Get(
514 p_encoded => FND_API.G_FALSE,
515 p_count => x_msg_count,
516 p_data => x_msg_data);
517
518 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
519 ROLLBACK TO create_grant ;
520 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
521
522 FND_MSG_PUB.Count_And_Get(
523 p_encoded => FND_API.G_FALSE,
524 p_count => x_msg_count,
525 p_data => x_msg_data);
526
527 WHEN OTHERS THEN
528 ROLLBACK TO create_grant ;
529 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
530
531 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_OTHERS_EXCEP');
532 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
533 FND_MSG_PUB.ADD;
534
535 FND_MSG_PUB.Count_And_Get(
536 p_encoded => FND_API.G_FALSE,
540
537 p_count => x_msg_count,
538 p_data => x_msg_data);
539
541 END create_grant;
542
543 /**
544 * PROCEDURE create_grant
545 *
546 * DESCRIPTION
547 *
548 * Creates a set of Grants to a Data Sharing Group.
549 * The procedure is called when a new secured entity is
550 * added to a dss group.
551 *
552 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
553 *
554 * ARGUMENTS
555 *
556 * NOTES
557 *
558 * MODIFICATION HISTORY
559 *
560 * 06-30-2004 Jianying Huang o Created.
561 *
562 */
563
564 PROCEDURE create_grant (
565 p_init_msg_list IN VARCHAR2 DEFAULT NULL,
566 p_dss_group_code IN VARCHAR2,
567 p_dss_instance_set_id IN NUMBER,
568 p_secured_entity_status IN VARCHAR2,
569 x_return_status OUT NOCOPY VARCHAR2,
570 x_msg_count OUT NOCOPY NUMBER,
571 x_msg_data OUT NOCOPY VARCHAR2
572 ) IS
573
574 CURSOR c_grant_exists IS
575 SELECT dsg.status
576 FROM hz_dss_grants_v grants,
577 hz_dss_groups_b dsg
578 WHERE dsg.dss_group_code = p_dss_group_code
579 AND grants.dss_group_code = p_dss_group_code
580 AND ROWNUM = 1;
581
582 CURSOR c_objects IS
583 SELECT obj_name
584 FROM fnd_objects obj,
585 fnd_object_instance_sets ins
586 WHERE instance_set_id = p_dss_instance_set_id
587 AND ins.object_id = obj.object_id;
588
589 CURSOR c_grants IS
590 SELECT *
591 FROM hz_dss_grants_v
592 WHERE dss_group_code = p_dss_group_code;
593
594 l_dsg_status VARCHAR2(1);
595 l_obj_name VARCHAR2(30);
596 l_end_date DATE;
597 l_menu_name VARCHAR2(30);
598 l_fnd_grant_guid RAW(100);
599 l_fnd_success VARCHAR2(1);
600 l_fnd_errorcode NUMBER;
601
602 BEGIN
603
604 -- initialize message list if p_init_msg_list is set to TRUE.
605 IF p_init_msg_list IS NOT NULL AND
606 FND_API.to_Boolean(p_init_msg_list)
607 THEN
608 FND_MSG_PUB.initialize;
609 END IF;
610
611 -- initialize API return status to success.
612 x_return_status := FND_API.G_RET_STS_SUCCESS;
613
614 -- return when no grants exists for this dss group.
615 OPEN c_grant_exists;
616 FETCH c_grant_exists INTO l_dsg_status;
617 CLOSE c_grant_exists;
618
619 IF l_dsg_status IS NULL THEN
620 RETURN;
621 END IF;
622
623 -- standard start of API savepoint
624 SAVEPOINT create_grant;
625
626 --
627 -- get object name
628 --
629 OPEN c_objects;
630 FETCH c_objects INTO l_obj_name;
631 CLOSE c_objects;
632
633 IF l_obj_name IS NULL THEN
634 RAISE FND_API.G_EXC_ERROR;
635 END IF;
636
637 --
638 -- set the end date to null when the dss group and the secured
639 -- entity are active.
640 -- set the end date to sysdate when the dss group or the secured
641 -- entity are inactive.
642 --
643 IF l_dsg_status <> 'A' OR
644 p_secured_entity_status <> 'A'
645 THEN
646 l_end_date := SYSDATE;
647 END IF;
648
649 FOR c_grants_rec IN c_grants LOOP EXIT WHEN c_grants%NOTFOUND;
650
651 FOR i IN 1..4 LOOP
652
653 l_menu_name := NULL;
654
655 -- Given a Data Operation Code, determine to which Menu we should be granting
656
657 IF i = 1 AND c_grants_rec.view_flag = 'Y' THEN
658 l_menu_name := 'SELECT';
659 ELSIF i = 2 AND c_grants_rec.insert_flag = 'Y' THEN
660 l_menu_name := 'INSERT';
661 ELSIF i = 3 AND c_grants_rec.update_flag = 'Y' THEN
662 l_menu_name := 'UPDATE';
663 ELSIF i = 4 AND c_grants_rec.delete_flag = 'Y' THEN
664 l_menu_name := 'DELETE';
665 END IF;
666
667 IF l_menu_name IS NOT NULL THEN
668 l_menu_name := 'HZ_DSS_' || l_menu_name;
669
670 fnd_grants_pkg.grant_function (
671 p_api_version => 1,
672 p_menu_name => l_menu_name,
673 p_object_name => l_obj_name,
674 p_instance_type => 'SET',
675 p_instance_set_id => p_dss_instance_set_id,
676 p_grantee_type => SUBSTRB(c_grants_rec.dss_grantee_type, 1, 8),
677 p_grantee_key => SUBSTRB(c_grants_rec.dss_grantee_key, 1, 240),
678 p_start_date => SYSDATE,
679 p_end_date => l_end_date,
680 p_program_name => G_API_NAME,
681 -- The Data Sharing Group is stored in the grant TAG!
682 p_program_tag => p_dss_group_code,
683 x_grant_guid => l_fnd_grant_guid,
684 x_success => l_fnd_success,
685 x_errorcode => l_fnd_errorcode
686 );
687
688 IF l_fnd_success <> FND_API.G_TRUE THEN
689 RAISE FND_API.G_EXC_ERROR;
690 END IF;
691 END IF;
692
693 END LOOP;
694
695 END LOOP;
696
697 -- standard call to get message count and if count is 1, get message info.
698 FND_MSG_PUB.Count_And_Get(
699 p_encoded => FND_API.G_FALSE,
703 EXCEPTION
700 p_count => x_msg_count,
701 p_data => x_msg_data);
702
704 WHEN FND_API.G_EXC_ERROR THEN
705 ROLLBACK TO create_grant ;
706 x_return_status := FND_API.G_RET_STS_ERROR;
707
708 FND_MSG_PUB.Count_And_Get(
709 p_encoded => FND_API.G_FALSE,
710 p_count => x_msg_count,
711 p_data => x_msg_data);
712
713 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
714 ROLLBACK TO create_grant ;
715 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
716
717 FND_MSG_PUB.Count_And_Get(
718 p_encoded => FND_API.G_FALSE,
719 p_count => x_msg_count,
720 p_data => x_msg_data);
721
722 WHEN OTHERS THEN
723 ROLLBACK TO create_grant ;
724 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
725
726 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_OTHERS_EXCEP');
727 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
728 FND_MSG_PUB.ADD;
729
730 FND_MSG_PUB.Count_And_Get(
731 p_encoded => FND_API.G_FALSE,
732 p_count => x_msg_count,
733 p_data => x_msg_data);
734
735 END create_grant;
736
737 /**
738 * PROCEDURE update_grant
739 *
740 * DESCRIPTION
741 *
742 * Updates a set of Grants against a Data Sharing Group.
743 * This signature matches the UI and corresponds to a "UI Grant Update".
744 *
745 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
746 *
747 * ARGUMENTS
748 *
749 * NOTES
750 *
751 * MODIFICATION HISTORY
752 *
753 * 09-03-2002 Chris Saulit o Created.
754 *
755 */
756
757 PROCEDURE update_grant (
758 p_init_msg_list IN VARCHAR2,
759 p_dss_group_code IN VARCHAR2,
760 p_dss_grantee_type IN VARCHAR2,
761 p_dss_grantee_key IN VARCHAR2,
762 p_view_flag IN VARCHAR2,
763 p_insert_flag IN VARCHAR2,
764 p_update_flag IN VARCHAR2,
765 p_delete_flag IN VARCHAR2,
766 p_admin_flag IN VARCHAR2,
767 x_return_status OUT NOCOPY VARCHAR2,
768 x_msg_count OUT NOCOPY NUMBER,
769 x_msg_data OUT NOCOPY VARCHAR2
770 ) IS
771
772 CURSOR c_grant IS
773 SELECT NVL(view_flag,'N'), NVL(insert_flag,'N'),
774 NVL(update_flag,'N'), NVL(delete_flag,'N'),
775 dsg.status
776 FROM hz_dss_grants_v grants, hz_dss_groups_b dsg
777 WHERE grants.dss_group_code = p_dss_group_code
778 AND dss_grantee_type = p_dss_grantee_type
779 AND (p_dss_grantee_type = 'GLOBAL' OR
780 p_dss_grantee_type <> 'GLOBAL' AND dss_grantee_key = p_dss_grantee_key)
781 AND dsg.dss_group_code = p_dss_group_code;
782
783 l_db_view_flag VARCHAR2(1);
784 l_db_insert_flag VARCHAR2(1);
785 l_db_update_flag VARCHAR2(1);
786 l_db_delete_flag VARCHAR2(1);
787 l_dsg_status VARCHAR2(1);
788 l_end_date DATE;
789
790 BEGIN
791
792 -- initialize message list if p_init_msg_list is set to TRUE.
793 IF p_init_msg_list IS NOT NULL AND
794 FND_API.to_Boolean(p_init_msg_list)
795 THEN
796 FND_MSG_PUB.initialize;
797 END IF;
798
799 -- initialize API return status to success.
800 x_return_status := FND_API.G_RET_STS_SUCCESS;
801
802 -- standard start of API savepoint
803 SAVEPOINT update_grant;
804
805 --
806 -- Get the current state of the grants for this grantee/dsg
807 --
808
809 OPEN c_grant;
810
811 FETCH c_grant INTO
812 l_db_view_flag, l_db_insert_flag,
813 l_db_update_flag, l_db_delete_flag,
814 l_dsg_status;
815
816 -- we don't care if not found (flags will be null, that is ok)
817
818 CLOSE c_grant;
819
820 IF l_dsg_status <> 'A' THEN
821 l_end_date := SYSDATE;
822 END IF;
823
824 -- Process the actions one by one
825
826 --
827 -- View Flag
828 --
829
830 IF NVL(p_view_flag, 'N') = 'Y' AND NVL(l_db_view_flag, 'N') = 'N' THEN
831 --
832 -- create grant
833 --
834 do_create_fnd_grant (
835 p_dss_group_code => p_dss_group_code,
836 p_data_operation_code => 'SELECT',
837 p_dss_grantee_type => p_dss_grantee_type,
838 p_dss_grantee_key => p_dss_grantee_key,
839 p_grant_start_date => SYSDATE,
840 p_grant_end_date => l_end_date
841 );
842 ELSIF NVL(p_view_flag, 'N') = 'N' AND NVL(l_db_view_flag, 'N') = 'Y' THEN
843 --
844 -- revoke grant
845 --
846 do_revoke_fnd_grant (
847 p_dss_group_code => p_dss_group_code,
848 p_data_operation_code => 'SELECT',
849 p_dss_grantee_type => p_dss_grantee_type,
850 p_dss_grantee_key => p_dss_grantee_key
851 );
852 END IF;
853
854 --
855 -- Insert Flag
856 --
857
858 IF NVL(p_insert_flag, 'N') = 'Y' AND NVL(l_db_insert_flag, 'N') = 'N' THEN
859 --
860 -- create grant
861 --
862 do_create_fnd_grant (
866 p_dss_grantee_key => p_dss_grantee_key,
863 p_dss_group_code => p_dss_group_code,
864 p_data_operation_code => 'INSERT',
865 p_dss_grantee_type => p_dss_grantee_type,
867 p_grant_start_date => SYSDATE,
868 p_grant_end_date => l_end_date
869 );
870 ELSIF NVL(p_insert_flag, 'N') = 'N' AND NVL(l_db_insert_flag, 'N') = 'Y' THEN
871 --
872 -- revoke grant
873 --
874 do_revoke_fnd_grant (
875 p_dss_group_code => p_dss_group_code,
876 p_data_operation_code => 'INSERT',
877 p_dss_grantee_type => p_dss_grantee_type,
878 p_dss_grantee_key => p_dss_grantee_key
879 );
880 END IF;
881
882 --
883 -- Update Flag
884 --
885
886 IF NVL(p_update_flag, 'N') = 'Y' AND NVL(l_db_update_flag, 'N') = 'N' THEN
887 --
888 -- create grant
889 --
890 do_create_fnd_grant (
891 p_dss_group_code => p_dss_group_code,
892 p_data_operation_code => 'UPDATE',
893 p_dss_grantee_type => p_dss_grantee_type,
894 p_dss_grantee_key => p_dss_grantee_key,
895 p_grant_start_date => SYSDATE,
896 p_grant_end_date => l_end_date
897 );
898 ELSIF NVL(p_update_flag, 'N') = 'N' AND NVL(l_db_update_flag, 'N') = 'Y' THEN
899 --
900 -- revoke grant
901 --
902 do_revoke_fnd_grant (
903 p_dss_group_code => p_dss_group_code,
904 p_data_operation_code => 'UPDATE',
905 p_dss_grantee_type => p_dss_grantee_type,
906 p_dss_grantee_key => p_dss_grantee_key
907 );
908 END IF;
909
910 --
911 -- Delete Flag
912 --
913
914 IF NVL(p_delete_flag, 'N') = 'Y' AND NVL(l_db_delete_flag, 'N') = 'N' THEN
915 --
916 -- create grant
917 --
918 do_create_fnd_grant (
919 p_dss_group_code => p_dss_group_code,
920 p_data_operation_code => 'DELETE',
921 p_dss_grantee_type => p_dss_grantee_type,
922 p_dss_grantee_key => p_dss_grantee_key,
923 p_grant_start_date => SYSDATE,
924 p_grant_end_date => l_end_date
925 );
926 ELSIF NVL(p_delete_flag, 'N') = 'N' AND NVL(l_db_delete_flag, 'N') = 'Y' THEN
927 --
928 -- revoke grant
929 --
930 do_revoke_fnd_grant (
931 p_dss_group_code => p_dss_group_code,
932 p_data_operation_code => 'DELETE',
933 p_dss_grantee_type => p_dss_grantee_type,
934 p_dss_grantee_key => p_dss_grantee_key
935 );
936 END IF;
937
938 -- standard call to get message count and if count is 1, get message info.
939 FND_MSG_PUB.Count_And_Get(
940 p_encoded => FND_API.G_FALSE,
941 p_count => x_msg_count,
942 p_data => x_msg_data);
943
944 EXCEPTION
945 WHEN FND_API.G_EXC_ERROR THEN
946 ROLLBACK TO update_grant ;
947 x_return_status := FND_API.G_RET_STS_ERROR;
948
949 FND_MSG_PUB.Count_And_Get(
950 p_encoded => FND_API.G_FALSE,
951 p_count => x_msg_count,
952 p_data => x_msg_data);
953
954 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
955 ROLLBACK TO update_grant ;
956 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
957
958 FND_MSG_PUB.Count_And_Get(
959 p_encoded => FND_API.G_FALSE,
960 p_count => x_msg_count,
961 p_data => x_msg_data);
962
963 WHEN OTHERS THEN
964 ROLLBACK TO update_grant ;
965 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
966
967 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_OTHERS_EXCEP');
968 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
969 FND_MSG_PUB.ADD;
970
971 FND_MSG_PUB.Count_And_Get(
972 p_encoded => FND_API.G_FALSE,
973 p_count => x_msg_count,
974 p_data => x_msg_data);
975
976 END update_grant;
977
978
979 /**
980 * PROCEDURE update_grant
981 *
982 * DESCRIPTION
983 *
984 * Updates a set of Grants against a Data Sharing Group.
985 * This procedure is called when a whole DSS group is
986 * disabled/enabled.
987 *
988 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
989 *
990 * ARGUMENTS
991 *
992 * NOTES
993 *
994 * MODIFICATION HISTORY
995 *
996 * 06-29-2004 Jianying Huang o Created.
997 *
998 */
999
1000 PROCEDURE update_grant (
1001 p_init_msg_list IN VARCHAR2,
1002 p_dss_group_code IN VARCHAR2,
1003 p_dss_group_status IN VARCHAR2,
1004 x_return_status OUT NOCOPY VARCHAR2,
1005 x_msg_count OUT NOCOPY NUMBER,
1006 x_msg_data OUT NOCOPY VARCHAR2
1007 ) IS
1008
1009 CURSOR c_grants IS
1010 SELECT grants.grant_guid,
1011 grants.start_date,
1012 dse.status
1013 FROM fnd_grants grants,
1014 fnd_object_instance_sets ins,
1015 hz_dss_secured_entities dse
1016 WHERE grants.program_name = G_API_NAME
1020 AND ins.instance_set_id = dse.dss_instance_set_id;
1017 AND grants.program_tag = p_dss_group_code
1018 AND grants.instance_set_id = ins.instance_set_id
1019 AND ins.instance_set_name NOT LIKE 'HZ_DSS_BASE_%'
1021
1022 l_fnd_grant_guid RAW(100);
1023 l_fnd_success VARCHAR2(1);
1024 l_start_date DATE;
1025 l_dse_status VARCHAR2(1);
1026 l_end_date DATE;
1027
1028 BEGIN
1029
1030 -- initialize message list if p_init_msg_list is set to TRUE.
1031 IF p_init_msg_list IS NOT NULL AND
1032 FND_API.to_Boolean(p_init_msg_list)
1033 THEN
1034 FND_MSG_PUB.initialize;
1035 END IF;
1036
1037 -- initialize API return status to success.
1038 x_return_status := FND_API.G_RET_STS_SUCCESS;
1039
1040 -- standard start of API savepoint
1041 SAVEPOINT update_grant;
1042
1043 OPEN c_grants;
1044 LOOP
1045
1046 FETCH c_grants INTO l_fnd_grant_guid, l_start_date, l_dse_status;
1047 IF c_grants%NOTFOUND THEN
1048 EXIT;
1049 END IF;
1050
1051 --
1052 -- set the end date to null when the dss group and the secured
1053 -- entity are active.
1054 -- set the end date to sysdate when the dss group or the secured
1055 -- entity are inactive.
1056 --
1057 IF p_dss_group_status <> 'A' OR
1058 l_dse_status <> 'A'
1059 THEN
1060 l_end_date := SYSDATE;
1061 ELSE
1062 l_end_date := NULL;
1063 END IF;
1064
1065 fnd_grants_pkg.update_grant (
1066 p_api_version => 1,
1067 p_grant_guid => l_fnd_grant_guid,
1068 p_start_date => l_start_date,
1069 p_end_date => l_end_date,
1070 x_success => l_fnd_success
1071 );
1072
1073 IF l_fnd_success <> FND_API.G_TRUE THEN
1074 CLOSE c_grants;
1075 RAISE FND_API.G_EXC_ERROR;
1076 END IF;
1077
1078 END LOOP;
1079 CLOSE c_grants;
1080
1081 -- standard call to get message count and if count is 1, get message info.
1082 FND_MSG_PUB.Count_And_Get(
1083 p_encoded => FND_API.G_FALSE,
1084 p_count => x_msg_count,
1085 p_data => x_msg_data);
1086
1087 EXCEPTION
1088 WHEN FND_API.G_EXC_ERROR THEN
1089 ROLLBACK TO update_grant ;
1090 x_return_status := FND_API.G_RET_STS_ERROR;
1091
1092 FND_MSG_PUB.Count_And_Get(
1093 p_encoded => FND_API.G_FALSE,
1094 p_count => x_msg_count,
1095 p_data => x_msg_data);
1096
1097 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1098 ROLLBACK TO update_grant ;
1099
1100 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1101 FND_MSG_PUB.Count_And_Get(
1102 p_encoded => FND_API.G_FALSE,
1103 p_count => x_msg_count,
1104 p_data => x_msg_data);
1105
1106 WHEN OTHERS THEN
1107 ROLLBACK TO update_grant ;
1108 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1109
1110 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_OTHERS_EXCEP');
1111 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1112 FND_MSG_PUB.ADD;
1113
1114 FND_MSG_PUB.Count_And_Get(
1115 p_encoded => FND_API.G_FALSE,
1116 p_count => x_msg_count,
1117 p_data => x_msg_data);
1118
1119 END update_grant;
1120
1121
1122 /**
1123 * PROCEDURE update_grant
1124 *
1125 * DESCRIPTION
1126 *
1127 * Updates a set of Grants against a Data Sharing Group.
1128 * This procedure is called when an entity inside a DSS group
1129 * is disabled/enabled.
1130 *
1131 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1132 *
1133 * ARGUMENTS
1134 *
1135 * NOTES
1136 *
1137 * MODIFICATION HISTORY
1138 *
1139 * 06-29-2004 Jianying Huang o Created.
1140 *
1141 */
1142
1143 PROCEDURE update_grant (
1144 p_init_msg_list IN VARCHAR2,
1145 p_dss_group_code IN VARCHAR2,
1146 p_dss_instance_set_id IN NUMBER,
1147 p_secured_entity_status IN VARCHAR2,
1148 x_return_status OUT NOCOPY VARCHAR2,
1149 x_msg_count OUT NOCOPY NUMBER,
1150 x_msg_data OUT NOCOPY VARCHAR2
1151 ) IS
1152
1153 CURSOR c_dss_groups IS
1154 SELECT status
1155 FROM hz_dss_groups_b
1156 WHERE dss_group_code = p_dss_group_code;
1157
1158 CURSOR c_grants IS
1159 SELECT grants.grant_guid,
1160 grants.start_date
1161 FROM fnd_grants grants
1162 WHERE grants.program_name = G_API_NAME
1163 AND grants.program_tag = p_dss_group_code
1164 AND grants.instance_set_id = p_dss_instance_set_id;
1165
1166 l_fnd_grant_guid RAW(100);
1167 l_fnd_success VARCHAR2(1);
1168 l_start_date DATE;
1169 l_dsg_status VARCHAR2(1);
1170 l_end_date DATE;
1171
1172 BEGIN
1173
1174 -- initialize message list if p_init_msg_list is set to TRUE.
1178 FND_MSG_PUB.initialize;
1175 IF p_init_msg_list IS NOT NULL AND
1176 FND_API.to_Boolean(p_init_msg_list)
1177 THEN
1179 END IF;
1180
1181 -- initialize API return status to success.
1182 x_return_status := FND_API.G_RET_STS_SUCCESS;
1183
1184 -- standard start of API savepoint
1185 SAVEPOINT update_grant;
1186
1187 --
1188 -- set the end date to null when the dss group and the secured
1189 -- entity are active.
1190 -- set the end date to sysdate when the dss group or the secured
1191 -- entity are inactive.
1192 --
1193 OPEN c_dss_groups;
1194 FETCH c_dss_groups INTO l_dsg_status;
1195 CLOSE c_dss_groups;
1196
1197 IF l_dsg_status <> 'A' OR
1198 p_secured_entity_status <> 'A'
1199 THEN
1200 l_end_date := SYSDATE;
1201 END IF;
1202
1203 OPEN c_grants;
1204 LOOP
1205 FETCH c_grants INTO l_fnd_grant_guid, l_start_date;
1206 IF c_grants%NOTFOUND THEN
1207 EXIT;
1208 END IF;
1209
1210 fnd_grants_pkg.update_grant (
1211 p_api_version => 1,
1212 p_grant_guid => l_fnd_grant_guid,
1213 p_start_date => l_start_date,
1214 p_end_date => l_end_date,
1215 x_success => l_fnd_success
1216 );
1217
1218 IF l_fnd_success <> FND_API.G_TRUE THEN
1219 CLOSE c_grants;
1220 RAISE FND_API.G_EXC_ERROR;
1221 END IF;
1222
1223 END LOOP;
1224 CLOSE c_grants;
1225
1226 -- standard call to get message count and if count is 1, get message info.
1227 FND_MSG_PUB.Count_And_Get(
1228 p_encoded => FND_API.G_FALSE,
1229 p_count => x_msg_count,
1230 p_data => x_msg_data);
1231
1232 EXCEPTION
1233 WHEN FND_API.G_EXC_ERROR THEN
1234 ROLLBACK TO update_grant ;
1235 x_return_status := FND_API.G_RET_STS_ERROR;
1236
1237 FND_MSG_PUB.Count_And_Get(
1238 p_encoded => FND_API.G_FALSE,
1239 p_count => x_msg_count,
1240 p_data => x_msg_data);
1241
1242 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1243 ROLLBACK TO update_grant ;
1244
1245 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1246 FND_MSG_PUB.Count_And_Get(
1247 p_encoded => FND_API.G_FALSE,
1248 p_count => x_msg_count,
1249 p_data => x_msg_data);
1250
1251 WHEN OTHERS THEN
1252 ROLLBACK TO update_grant ;
1253 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1254
1255 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_OTHERS_EXCEP');
1256 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1257 FND_MSG_PUB.ADD;
1258
1259 FND_MSG_PUB.Count_And_Get(
1260 p_encoded => FND_API.G_FALSE,
1261 p_count => x_msg_count,
1262 p_data => x_msg_data);
1263
1264 END update_grant;
1265
1266
1267 /**
1268 * PROCEDURE check_admin_priv
1269 *
1270 * DESCRIPTION
1271 *
1272 * Checks whether the current user has sufficient privilege to maintain
1273 * a Data Sharing Group.
1274 *
1275 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1276 *
1277 * ARGUMENTS
1278 *
1279 * NOTES
1280 *
1281 * MODIFICATION HISTORY
1282 *
1283 * 09-18-2002 Chris Saulit o Created.
1284 *
1285 */
1286
1287 PROCEDURE check_admin_priv (
1288 p_init_msg_list IN VARCHAR2,
1289 p_dss_group_code IN VARCHAR2,
1290 p_dss_admin_func_code IN VARCHAR2,
1291 x_pass_fail_flag OUT NOCOPY VARCHAR2,
1292 x_return_status OUT NOCOPY VARCHAR2,
1293 x_msg_count OUT NOCOPY NUMBER,
1294 x_msg_data OUT NOCOPY VARCHAR2
1295 ) IS
1296
1297 l_resp_cnt NUMBER;
1298
1299 BEGIN
1300
1301 -- initialize message list if p_init_msg_list is set to TRUE.
1302 IF p_init_msg_list IS NOT NULL AND
1303 FND_API.to_Boolean(p_init_msg_list)
1304 THEN
1305 FND_MSG_PUB.initialize;
1306 END IF;
1307
1308 -- initialize API return status to success and security answer to FALSE
1309 x_return_status := FND_API.G_RET_STS_SUCCESS;
1310 x_pass_fail_flag := FND_API.G_FALSE;
1311
1312 --
1313 -- Validations
1314 --
1315
1316 IF p_dss_admin_func_code NOT IN
1317 (g_dss_admin_create, g_dss_admin_update, g_dss_admin_grant)
1318 THEN
1319 FND_MESSAGE.SET_NAME('FND','FND_GENERIC_MESSAGE');
1320 FND_MESSAGE.SET_TOKEN(
1321 'MESSAGE','p_dss_admin_func_code must be one of: ' ||
1322 g_dss_admin_create ||', '|| g_dss_admin_update||', '||g_dss_admin_grant
1323 ); -- this is a developer error, not a user-facing error
1324 FND_MSG_PUB.ADD;
1325 RAISE FND_API.G_EXC_ERROR;
1326 END IF;
1327
1328 --
1329 -- Check privilege
1330 --
1331 -- Bug 4956762: performance fix. split the original
1332 -- query into 2 and cached responsibility id.
1333 --
1334
1335 IF G_DSS_RESPONSIBILITY_ID IS NULL THEN
1336 BEGIN
1337 SELECT responsibility_id INTO G_DSS_RESPONSIBILITY_ID
1338 FROM fnd_responsibility r
1339 WHERE r.responsibility_key = 'HZ_DSS_ADMIN'
1340 AND r.application_id = 222;
1341 EXCEPTION
1342 WHEN NO_DATA_FOUND THEN
1343 NULL;
1344 END;
1345 END IF;
1346
1347 BEGIN
1348 SELECT 1
1349 INTO l_resp_cnt
1350 FROM fnd_user_resp_groups rg
1351 WHERE rg.user_id = fnd_global.user_id
1352 AND rg.responsibility_id = G_DSS_RESPONSIBILITY_ID
1353 AND rg.responsibility_application_id = 222
1354 AND (rg.end_date IS NULL OR rg.end_date > SYSDATE)
1355 AND (rg.start_date IS NULL OR rg.start_date < SYSDATE);
1356
1357 x_pass_fail_flag := FND_API.G_TRUE;
1358
1359 EXCEPTION
1360 WHEN NO_DATA_FOUND THEN
1361 NULL;
1362 END;
1363
1364 -- standard call to get message count and if count is 1, get message info.
1365 FND_MSG_PUB.Count_And_Get(
1366 p_encoded => FND_API.G_FALSE,
1367 p_count => x_msg_count,
1368 p_data => x_msg_data);
1369
1370 EXCEPTION
1371 WHEN FND_API.G_EXC_ERROR THEN
1372 x_return_status := FND_API.G_RET_STS_ERROR;
1373
1374 FND_MSG_PUB.Count_And_Get(
1375 p_encoded => FND_API.G_FALSE,
1376 p_count => x_msg_count,
1377 p_data => x_msg_data);
1378
1379 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1380 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1381
1382 FND_MSG_PUB.Count_And_Get(
1383 p_encoded => FND_API.G_FALSE,
1384 p_count => x_msg_count,
1385 p_data => x_msg_data);
1386
1387 WHEN OTHERS THEN
1388 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1389
1390 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_OTHERS_EXCEP');
1391 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1392 FND_MSG_PUB.ADD;
1393 FND_MSG_PUB.Count_And_Get(
1394 p_encoded => FND_API.G_FALSE,
1395 p_count => x_msg_count,
1396 p_data => x_msg_data);
1397 END check_admin_priv;
1398
1399
1400 /**
1401 * PROCEDURE check_admin_priv
1402 *
1403 * DESCRIPTION
1404 *
1405 * Checks whether the current user has sufficient privilege to maintain
1406 * a Data Sharing Group.
1407 *
1408 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1409 *
1410 * ARGUMENTS
1411 *
1412 * NOTES
1413 *
1414 * MODIFICATION HISTORY
1415 *
1416 * 09-18-2002 Chris Saulit o Created.
1417 *
1418 */
1419
1420 FUNCTION check_admin_priv (
1421 p_dss_group_code IN VARCHAR2,
1422 p_dss_admin_func_code IN VARCHAR2
1423 ) RETURN VARCHAR2 IS
1424
1425 l_pass_fail_flag VARCHAR2(1);
1426 l_return_status VARCHAR2(1);
1427 l_msg_count NUMBER;
1428 l_msg_data VARCHAR2(2000);
1429
1430 BEGIN
1431
1432 --
1433 -- Call the PL/SQL version
1434 --
1435 check_admin_priv (
1436 p_init_msg_list => FND_API.G_TRUE,
1437 p_dss_group_code => p_dss_group_code,
1438 p_dss_admin_func_code => p_dss_admin_func_code,
1439 x_pass_fail_flag => l_pass_fail_flag,
1440 x_return_status => l_return_status,
1441 x_msg_count => l_msg_count,
1442 x_msg_data => l_msg_data
1443 );
1444
1445
1446 RETURN l_pass_fail_flag;
1447
1448 EXCEPTION
1449 WHEN OTHERS THEN
1450 RETURN FND_API.G_FALSE;
1451
1452 END check_admin_priv;
1453
1454 END HZ_DSS_GRANTS_PUB;