[Home] [Help]
PACKAGE BODY: APPS.EGO_SECURITY_PUB
Source
1 PACKAGE BODY EGO_SECURITY_PUB AS
2 /* $Header: EGOPSECB.pls 120.3 2006/04/13 04:51:31 ninaraya noship $ */
3 /*---------------------------------------------------------------------------+
4 | This package contains public API for Applications Security |
5 +---------------------------------------------------------------------------*/
6
7 G_PKG_NAME CONSTANT VARCHAR2(30):= 'EGO_SECURITY_PUB';
8
9 --Private - check_override_datasec
10 ------------------------------------
11 -- FUNCTION check_override_datasec
12 -- (
13 -- p_party_id in NUMBER
14 -- ) RETURN BOOLEAN
15 -- IS
16 -- l_dummy VARCHAR2(1) :='';
17 -- CURSOR check_override_function(cp_party_id NUMBER)
18 -- IS
19 -- SELECT 'X'
20 -- FROM fnd_form_functions functions,
21 -- fnd_user users,
22 -- fnd_menu_entries role_privileges,
23 -- fnd_responsibility resp,
24 -- fnd_user_resp_groups user_resps
25 -- WHERE users.customer_id=cp_party_id
26 -- AND user_resps.start_date<= sysdate
27 -- AND nvl( user_resps.end_date,sysdate+1 ) >= sysdate
28 -- AND users.user_id=user_resps.user_id
29 -- AND resp.responsibility_id=user_resps.responsibility_id
30 -- AND resp.menu_id=role_privileges.menu_id
31 -- AND role_privileges.function_id=functions.function_id
32 -- AND functions.function_name = 'EGO_OVERRIDE_DATASEC';
33 -- BEGIN
34 -- OPEN check_override_function(cp_party_id =>p_party_id);
35 -- FETCH check_override_function INTO l_dummy;
36 -- IF(check_override_function%FOUND) THEN
37 -- CLOSE check_override_function;
38 -- RETURN TRUE;
39 -- ELSE
40 -- CLOSE check_override_function;
41 -- RETURN FALSE;
42 -- END IF;
43 -- END check_override_datasec;
44 ------------------------------------------
45
46
47
48 --1. Grant Role
49 ------------------------------------
50 PROCEDURE grant_role
51 (
52 p_api_version IN NUMBER,
53 p_role_name IN VARCHAR2,
54 p_object_name IN VARCHAR2,
55 p_instance_type IN VARCHAR2,
56 p_instance_set_id IN NUMBER,
57 p_instance_pk1_value IN VARCHAR2,
58 p_instance_pk2_value IN VARCHAR2,
59 p_instance_pk3_value IN VARCHAR2,
60 p_instance_pk4_value IN VARCHAR2,
61 p_instance_pk5_value IN VARCHAR2,
62 p_party_id IN NUMBER,
63 p_start_date IN DATE,
64 p_end_date IN DATE,
65 x_return_status OUT NOCOPY VARCHAR2,
66 x_errorcode OUT NOCOPY NUMBER
67 )
68 IS
69
70 x_grant_guid fnd_grants.grant_guid%TYPE;
71 l_grantee_type hz_parties.party_type%TYPE;
72 l_instance_type fnd_grants.instance_type%TYPE;
73 l_grantee_key fnd_grants.grantee_key%TYPE;
74 l_dummy VARCHAR2(1);
75 CURSOR get_party_type (cp_party_id NUMBER)
76 IS
77 SELECT party_type
78 FROM hz_parties
79 WHERE party_id=cp_party_id;
80
81 CURSOR check_fnd_grant_exist (cp_grantee_key VARCHAR2,
82 cp_grantee_type VARCHAR2,
83 cp_menu_name VARCHAR2,
84 cp_object_name VARCHAR2,
85 cp_instance_type VARCHAR2,
86 cp_instance_pk1_value VARCHAR2,
87 cp_instance_pk2_value VARCHAR2,
88 cp_instance_pk3_value VARCHAR2,
89 cp_instance_pk4_value VARCHAR2,
90 cp_instance_pk5_value VARCHAR2,
91 cp_instance_set_id NUMBER,
92 cp_start_date DATE,
93 cp_end_date DATE) IS
94
95 SELECT 'X'
96 FROM fnd_grants grants,
97 fnd_objects obj,
98 fnd_menus menus
99 WHERE grants.grantee_key=cp_grantee_key
100 AND grants.grantee_type=cp_grantee_type
101 AND grants.menu_id=menus.menu_id
102 AND menus.menu_name=cp_menu_name
103 AND grants.object_id = obj.object_id
104 AND obj.obj_name=cp_object_name
105 AND grants.instance_type=cp_instance_type
106 AND ((grants.instance_pk1_value=cp_instance_pk1_value )
107 OR((grants.instance_pk1_value = '*NULL*') AND (cp_instance_pk1_value IS NULL)))
108 AND ((grants.instance_pk2_value=cp_instance_pk2_value )
109 OR((grants.instance_pk2_value = '*NULL*') AND (cp_instance_pk2_value IS NULL)))
110 AND ((grants.instance_pk3_value=cp_instance_pk3_value )
111 OR((grants.instance_pk3_value = '*NULL*') AND (cp_instance_pk3_value IS NULL)))
112 AND ((grants.instance_pk4_value=cp_instance_pk4_value )
113 OR((grants.instance_pk4_value = '*NULL*') AND (cp_instance_pk4_value IS NULL)))
114 AND ((grants.instance_pk5_value=cp_instance_pk5_value )
115 OR((grants.instance_pk5_value = '*NULL*') AND (cp_instance_pk5_value IS NULL)))
116 AND ((grants.instance_set_id=cp_instance_set_id )
117 OR((grants.instance_set_id IS NULL ) AND (cp_instance_set_id IS NULL)))
118 AND (((grants.start_date<=cp_start_date )
119 AND (( grants.end_date IS NULL) OR (cp_start_date <=grants.end_date )))
120 OR ((grants.start_date >= cp_start_date )
121 AND (( cp_end_date IS NULL) OR (cp_end_date >=grants.start_date))));
122
123
124
125 BEGIN
126 IF( p_instance_type <> 'INSTANCE') THEN
127 l_instance_type:='SET';
128 ELSE
129 l_instance_type:=p_instance_type;
130 END IF;
131 OPEN get_party_type (cp_party_id =>p_party_id);
132 FETCH get_party_type INTO l_grantee_type;
133 CLOSE get_party_type;
134 IF( p_party_id = -1000) THEN
135 l_grantee_type :='GLOBAL';
136 l_grantee_key:='HZ_GLOBAL:'||p_party_id;
137 ELSIF (l_grantee_type ='PERSON') THEN
138 l_grantee_type:='USER';
139 l_grantee_key:='HZ_PARTY:'||p_party_id;
140 ELSIF (l_grantee_type ='GROUP') THEN
141 l_grantee_type:='GROUP';
142 l_grantee_key:='HZ_GROUP:'||p_party_id;
143 ELSIF (l_grantee_type ='ORGANIZATION') THEN
144 l_grantee_type:='COMPANY';
145 l_grantee_key:='HZ_COMPANY:'||p_party_id;
146 ELSE
147 null;
148 END IF;
149
150 OPEN check_fnd_grant_exist(cp_grantee_key => l_grantee_key,
151 cp_grantee_type => l_grantee_type,
152 cp_menu_name => p_role_name,
153 cp_object_name => p_object_name,
154 cp_instance_type => l_instance_type,
155 cp_instance_pk1_value => p_instance_pk1_value,
156 cp_instance_pk2_value => p_instance_pk2_value,
157 cp_instance_pk3_value => p_instance_pk3_value,
158 cp_instance_pk4_value => p_instance_pk4_value,
159 cp_instance_pk5_value => p_instance_pk5_value,
160 cp_instance_set_id => p_instance_set_id,
161 cp_start_date => p_start_date,
162 cp_end_date => p_end_date);
163
164 FETCH check_fnd_grant_exist INTO l_dummy;
165 IF( check_fnd_grant_exist%NOTFOUND) THEN
166 fnd_grants_pkg.grant_function(
167 p_api_version => 1.0,
168 p_menu_name => p_role_name ,
169 p_object_name => p_object_name,
170 p_instance_type => l_instance_type,
171 p_instance_set_id => p_instance_set_id,
172 p_instance_pk1_value => p_instance_pk1_value,
173 p_instance_pk2_value => p_instance_pk2_value,
174 p_instance_pk3_value => p_instance_pk3_value,
175 p_instance_pk4_value => p_instance_pk4_value,
176 p_instance_pk5_value => p_instance_pk5_value,
177 p_grantee_type => l_grantee_type,
178 p_grantee_key => l_grantee_key,
179 p_start_date => p_start_date,
180 p_end_date => p_end_date,
181 p_program_name => null,
182 p_program_tag => null,
183 x_grant_guid => x_grant_guid,
184 x_success => x_return_status,
185 x_errorcode => x_errorcode
186 );
187 ELSE
188 x_return_status:='F';
189 END IF;
190
191 CLOSE check_fnd_grant_exist;
192
193 END grant_role;
194 -------------------------------------------------------------
195 --1 a. Grant Privilege
196 ------------------------------------
197 PROCEDURE grant_role
198 (
199 p_api_version IN NUMBER,
200 p_role_name IN VARCHAR2,
201 p_object_name IN VARCHAR2,
202 p_instance_type IN VARCHAR2,
203 p_object_key IN NUMBER,
204 p_party_id IN NUMBER,
205 p_start_date IN DATE,
206 p_end_date IN DATE,
207 x_return_status OUT NOCOPY VARCHAR2,
208 x_errorcode OUT NOCOPY NUMBER
209 )
210 IS
211 -- Start OF comments
212 -- API name : Grant
213 -- TYPE : Public
214 -- Pre-reqs : None
215 -- FUNCTION : Grant a Role on object instances to a Party.
216 -- If this operation fails then the grant is not
217 -- done and error code is returned.
218 --
219 -- Version: Current Version 0.1
220 -- Previous Version : None
221 -- Notes :
222 --
223 -- END OF comments
224 l_instance_set_id fnd_grants.instance_set_id%TYPE;
225 l_instance_pk1_value fnd_grants.instance_pk1_value%TYPE;
226 BEGIN
227 IF( p_instance_type ='SET') THEN
228 l_instance_set_id:=p_object_key;
229 l_instance_pk1_value:= null;
230 ELSE
231 l_instance_set_id:=null;
232 l_instance_pk1_value:= to_char(p_object_key);
233 END IF;
234 grant_role
235 (
236 p_api_version => p_api_version,
237 p_role_name => p_role_name,
238 p_object_name => p_object_name,
239 p_instance_type => p_instance_type,
240 p_instance_set_id => l_instance_set_id,
241 p_instance_pk1_value => l_instance_pk1_value,
242 p_instance_pk2_value => null,
243 p_instance_pk3_value => null,
244 p_instance_pk4_value => null,
245 p_instance_pk5_value => null,
246 p_party_id => p_party_id,
247 p_start_date => p_start_date,
248 p_end_date => p_end_date,
249 x_return_status => x_return_status,
250 x_errorcode => x_errorcode
251 );
252
253 END grant_role;
254 ---------------------------------------------------------------------
255 ------------------------------------
256 --11. Grant Role
257 ------------------------------------
258 PROCEDURE grant_role_guid
259 (
260 p_api_version IN NUMBER,
261 p_role_name IN VARCHAR2,
262 p_object_name IN VARCHAR2,
263 p_instance_type IN VARCHAR2,
264 p_instance_set_id IN NUMBER,
265 p_instance_pk1_value IN VARCHAR2,
266 p_instance_pk2_value IN VARCHAR2,
267 p_instance_pk3_value IN VARCHAR2,
268 p_instance_pk4_value IN VARCHAR2,
269 p_instance_pk5_value IN VARCHAR2,
270 p_party_id IN NUMBER,
271 p_start_date IN DATE,
272 p_end_date IN DATE,
273 x_return_status OUT NOCOPY VARCHAR2,
274 x_errorcode OUT NOCOPY NUMBER,
275 x_grant_guid OUT NOCOPY RAW
276 )
277 IS
278
279 --x_grant_guid fnd_grants.grant_guid%TYPE;
280 l_grantee_type hz_parties.party_type%TYPE;
281 l_instance_type fnd_grants.instance_type%TYPE;
282 l_grantee_key fnd_grants.grantee_key%TYPE;
283 l_dummy VARCHAR2(1);
284 l_msg_count NUMBER;
285 l_msg_data VARCHAR2(4000);
286 CURSOR get_party_type (cp_party_id NUMBER) IS
287 SELECT party_type
288 FROM hz_parties
289 WHERE party_id=cp_party_id;
290
291 CURSOR check_fnd_grant_exist (cp_grantee_key VARCHAR2,
292 cp_grantee_type VARCHAR2,
293 cp_menu_name VARCHAR2,
294 cp_object_name VARCHAR2,
295 cp_instance_type VARCHAR2,
296 cp_instance_pk1_value VARCHAR2,
297 cp_instance_pk2_value VARCHAR2,
298 cp_instance_pk3_value VARCHAR2,
299 cp_instance_pk4_value VARCHAR2,
300 cp_instance_pk5_value VARCHAR2,
301 cp_instance_set_id NUMBER,
302 cp_start_date DATE,
303 cp_end_date DATE) IS
304
305 SELECT 'X'
306 FROM fnd_grants grants,
307 fnd_objects obj,
308 fnd_menus menus
309 WHERE grants.grantee_key=cp_grantee_key
310 AND grants.grantee_type=cp_grantee_type
311 AND grants.menu_id=menus.menu_id
312 AND menus.menu_name=cp_menu_name
313 AND grants.object_id = obj.object_id
314 AND obj.obj_name=cp_object_name
315 AND grants.instance_type=cp_instance_type
316 AND ((grants.instance_pk1_value=cp_instance_pk1_value )
317 OR((grants.instance_pk1_value = '*NULL*') AND (cp_instance_pk1_value IS NULL)))
318 AND ((grants.instance_pk2_value=cp_instance_pk2_value )
319 OR((grants.instance_pk2_value = '*NULL*') AND (cp_instance_pk2_value IS NULL)))
320 AND ((grants.instance_pk3_value=cp_instance_pk3_value )
321 OR((grants.instance_pk3_value = '*NULL*') AND (cp_instance_pk3_value IS NULL)))
322 AND ((grants.instance_pk4_value=cp_instance_pk4_value )
323 OR((grants.instance_pk4_value = '*NULL*') AND (cp_instance_pk4_value IS NULL)))
324 AND ((grants.instance_pk5_value=cp_instance_pk5_value )
325 OR((grants.instance_pk5_value = '*NULL*') AND (cp_instance_pk5_value IS NULL)))
326 AND ((grants.instance_set_id=cp_instance_set_id )
327 OR((grants.instance_set_id IS NULL ) AND (cp_instance_set_id IS NULL)))
328 AND (((grants.start_date<=cp_start_date )
329 AND (( grants.end_date IS NULL) OR (cp_start_date <=grants.end_date )))
330 OR ((grants.start_date >= cp_start_date )
331 AND (( cp_end_date IS NULL) OR (cp_end_date >=grants.start_date))));
332
333 v_start_date DATE := sysdate;
334
335 BEGIN
336 if (p_start_date IS NULL) THEN
337 v_start_date := sysdate;
338 else
339 v_start_date := p_start_date;
340 end if;
341
342 IF( p_instance_type <> 'INSTANCE') THEN
343 l_instance_type:='SET';
344 ELSE
345 l_instance_type:=p_instance_type;
346 END IF;
347 OPEN get_party_type (cp_party_id =>p_party_id);
348 FETCH get_party_type INTO l_grantee_type;
349 CLOSE get_party_type;
350 IF( p_party_id = -1000) THEN
351 l_grantee_type :='GLOBAL';
352 l_grantee_key:='HZ_GLOBAL:'||p_party_id;
353 ELSIF (l_grantee_type ='PERSON') THEN
354 l_grantee_type:='USER';
355 l_grantee_key:='HZ_PARTY:'||p_party_id;
356 ELSIF (l_grantee_type ='GROUP') THEN
357 l_grantee_type:='GROUP';
361 l_grantee_key:='HZ_COMPANY:'||p_party_id;
358 l_grantee_key:='HZ_GROUP:'||p_party_id;
359 ELSIF (l_grantee_type ='ORGANIZATION') THEN
360 l_grantee_type:='COMPANY';
362 ELSE
363 null;
364 END IF;
365
366 OPEN check_fnd_grant_exist(cp_grantee_key => l_grantee_key,
367 cp_grantee_type => l_grantee_type,
368 cp_menu_name => p_role_name,
369 cp_object_name => p_object_name,
370 cp_instance_type => l_instance_type,
371 cp_instance_pk1_value => p_instance_pk1_value,
372 cp_instance_pk2_value => p_instance_pk2_value,
373 cp_instance_pk3_value => p_instance_pk3_value,
374 cp_instance_pk4_value => p_instance_pk4_value,
375 cp_instance_pk5_value => p_instance_pk5_value,
376 cp_instance_set_id => p_instance_set_id,
377 cp_start_date => v_start_date,
378 cp_end_date => p_end_date);
379
380 FETCH check_fnd_grant_exist INTO l_dummy;
381 IF( check_fnd_grant_exist%NOTFOUND) THEN
382 fnd_grants_pkg.grant_function(
383 p_api_version => 1.0,
384 p_menu_name => p_role_name ,
385 p_object_name => p_object_name,
386 p_instance_type => l_instance_type,
387 p_instance_set_id => p_instance_set_id,
388 p_instance_pk1_value => p_instance_pk1_value,
389 p_instance_pk2_value => p_instance_pk2_value,
390 p_instance_pk3_value => p_instance_pk3_value,
391 p_instance_pk4_value => p_instance_pk4_value,
392 p_instance_pk5_value => p_instance_pk5_value,
393 p_grantee_type => l_grantee_type,
394 p_grantee_key => l_grantee_key,
395 p_start_date => v_start_date,
396 p_end_date => p_end_date,
397 p_program_name => null,
398 p_program_tag => null,
399 x_grant_guid => x_grant_guid,
400 x_success => x_return_status,
401 x_errorcode => x_errorcode
402 );
403 -- added for 5151106
404 IF x_return_status = FND_API.G_TRUE AND l_grantee_type = 'COMPANY' THEN
405 EGO_PARTY_PUB.setup_enterprise_user
406 (p_company_id => p_party_id
407 ,x_return_status => x_return_status
408 ,x_msg_count => l_msg_count
409 ,x_msg_data => l_msg_data
410 );
411 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
412 x_return_status := FND_API.G_TRUE;
413 ELSE
414 x_return_status := FND_API.G_FALSE;
415 END IF;
416 IF x_return_status = FND_API.G_FALSE THEN
417 -- add message to fnd_stack
418 fnd_message.Set_Name('EGO','EGO_GENERIC_MSG_TEXT');
419 fnd_message.set_token('MESSAGE', l_msg_data);
420 fnd_msg_pub.Add;
421 END IF;
422 END IF;
423 ELSE
424 -- add message to fnd_stack for Bug 3352200
425 FND_MSG_PUB.INITIALIZE;
426 fnd_message.Set_Name('EGO','EGO_DUPLICATE_ROLE_FOR_GRANTEE');
427 fnd_msg_pub.Add;
428 -- end add message to fnd_stack for Bug 3352200
429 x_return_status:='F';
430 END IF;
431
432 CLOSE check_fnd_grant_exist;
433
434 END grant_role_guid;
435 -------------------------------------------------------------
436 --11 a. Grant Privilege
437 ------------------------------------
438 PROCEDURE grant_role_guid
439 (
440 p_api_version IN NUMBER,
441 p_role_name IN VARCHAR2,
442 p_object_name IN VARCHAR2,
443 p_instance_type IN VARCHAR2,
444 p_object_key IN NUMBER,
445 p_party_id IN NUMBER,
446 p_start_date IN DATE,
447 p_end_date IN DATE,
448 x_return_status OUT NOCOPY VARCHAR2,
449 x_errorcode OUT NOCOPY NUMBER,
450 x_grant_guid OUT NOCOPY RAW
451 )
452 IS
453 -- Start OF comments
454 -- API name : Grant
455 -- TYPE : Public
456 -- Pre-reqs : None
457 -- FUNCTION : Grant a Role on object instances to a Party.
458 -- If this operation fails then the grant is not
459 -- done and error code is returned.
460 --
461 -- Version: Current Version 0.1
462 -- Previous Version : None
463 -- Notes :
464 --
465 -- END OF comments
466 l_instance_set_id fnd_grants.instance_set_id%TYPE;
467 l_instance_pk1_value fnd_grants.instance_pk1_value%TYPE;
468 v_start_date DATE := sysdate;
469
470 BEGIN
471 IF( p_instance_type ='SET') THEN
472 l_instance_set_id:=p_object_key;
473 l_instance_pk1_value:= null;
474 ELSE
475 l_instance_set_id:=null;
479 if (p_start_date IS NULL) THEN
476 l_instance_pk1_value:= to_char(p_object_key);
477 END IF;
478
480 v_start_date := sysdate;
481 else
482 v_start_date := p_start_date;
483 end if;
484
485 grant_role_guid
486 (
487 p_api_version => p_api_version,
488 p_role_name => p_role_name,
489 p_object_name => p_object_name,
490 p_instance_type => p_instance_type,
491 p_instance_set_id => l_instance_set_id,
492 p_instance_pk1_value => l_instance_pk1_value,
493 p_instance_pk2_value => null,
494 p_instance_pk3_value => null,
495 p_instance_pk4_value => null,
496 p_instance_pk5_value => null,
497 p_party_id => p_party_id,
498 p_start_date => v_start_date,
499 p_end_date => p_end_date,
500 x_return_status => x_return_status,
501 x_errorcode => x_errorcode,
502 x_grant_guid => x_grant_guid
503 );
504
505 END grant_role_guid;
506 ---------------------------------------------------------------------
507
508
509 --2. Revoke Grant
510 --------------------------
511 PROCEDURE revoke_grant
512 (
513 p_api_version IN NUMBER,
514 p_grant_guid IN VARCHAR2,
515 x_return_status OUT NOCOPY VARCHAR2,
516 x_errorcode OUT NOCOPY NUMBER
517 )
518 IS
519 -- Start OF comments
520 -- API name : Revoke
521 -- TYPE : Public
522 -- Pre-reqs : None
523 -- FUNCTION : Revoke a Party's role on object instances.
524 -- If this operation fails then the revoke is
525 -- done and error code is returned.
526 --
527 -- Version: Current Version 0.1
528 -- Previous Version : None
529 -- Notes :
530 --
531 -- END OF comments
532
533 l_grant_guid fnd_grants.grant_guid%TYPE;
534 CURSOR get_grant_guid(cp_grant_id VARCHAR2)
535 IS
536 SELECT grant_guid
537 FROM fnd_grants
538 WHERE grant_guid=HEXTORAW(cp_grant_id);
539
540 BEGIN
541 OPEN get_grant_guid(cp_grant_id=>p_grant_guid);
542 FETCH get_grant_guid INTO l_grant_guid;
543 CLOSE get_grant_guid;
544
545 fnd_grants_pkg.revoke_grant(
546 p_api_version => p_api_version,
547 p_grant_guid => l_grant_guid ,
548 x_success => x_return_status,
549 x_errorcode => x_errorcode
550 );
551
552 END revoke_grant;
553 ----------------------------------------------------------------------------
554
555
556
557 --3. Check User Privilege
558 ------------------------------------
559 FUNCTION check_user_privilege
560 (
561 p_api_version IN NUMBER,
562 p_privilege IN VARCHAR2,
563 p_object_name IN VARCHAR2,
564 p_object_key IN NUMBER,
565 p_user_id IN NUMBER
566 )
567 RETURN VARCHAR2
568 IS
569 -- Start OF comments
570 -- API name : check_user_privilege
571 -- TYPE : Public
572 -- Pre-reqs : None
573 -- FUNCTION : check a user's privilege on object instance(s)
574 -- If this operation fails then the check is not
575 -- done and error code is returned.
576 --
577 -- Parameters:
578 -- IN : p_api_version IN NUMBER (required)
579 -- API Version of this procedure
580 --
581 -- p_privilege IN VARCHAR2 (required)
582 -- name of the privilege (function name)
583 --
584 -- p_object_name IN VARCHAR2 (required)
585 -- object on which the privilege should be checked
586 --
587 -- p_object_key IN NUMBER (required)
588 -- object key to an instance
589 --
590 -- p_user_id IN NUMBER (required)
591 -- user for whom the privilege is checked
592 --
593 -- OUT :
594 -- RETURN
595 -- FND_API.G_TRUE privilege EXISTS
596 -- FND_API.G_FALSE NO privilege
597 -- FND_API.G_RET_STS_ERROR if error
598 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error
599 --
600
601 -- Version: Current Version 0.1
602 -- Previous Version : None
603 -- Notes :
604 --
605 -- END OF comments
606 -- On addition of any Required parameters the major version needs
607 -- to change i.e. for eg. 1.X to 2.X.
608 -- On addition of any Optional parameters the minor version needs
609 -- to change i.e. for eg. X.6 to X.7.
610
611
612 l_party_id NUMBER;
613
614
615 CURSOR get_party_id(cp_user_id NUMBER) IS
616 SELECT customer_id
617 FROM fnd_user
618 WHERE user_id=cp_user_id;
619
620 BEGIN
621
622 OPEN get_party_id (cp_user_id => p_user_id);
626 p_privilege => p_privilege,
623 FETCH get_party_id INTO l_party_id;
624 CLOSE get_party_id;
625 RETURN check_party_privilege ( p_api_version => p_api_version,
627 p_object_name => p_object_name,
628 p_object_key => p_object_key,
629 p_party_id => l_party_id);
630
631
632
633 END check_user_privilege;
634 ----------------------------------------------------------------------------
635
636 --3.b.1 Check Party Privilege
637 ------------------------------------
638 FUNCTION check_party_privilege
639 (
640 p_api_version IN NUMBER,
641 p_privilege IN VARCHAR2,
642 p_object_name IN VARCHAR2,
643 p_object_key IN NUMBER,
644 p_party_id IN NUMBER
645 )
646 RETURN VARCHAR2
647 IS
648 BEGIN
649 return check_party_privilege
650 ( p_api_version => p_api_version,
651 p_privilege => p_privilege,
652 p_object_name => p_object_name,
653 p_instance_pk1_value => to_char(p_object_key),
654 p_instance_pk2_value => null,
655 p_instance_pk3_value => null,
656 p_instance_pk4_value => null,
657 p_instance_pk5_value => null,
658 p_party_id => p_party_id
659 );
660
661 END check_party_privilege;
662 ----------------------------------------------------
663
664 --3.b.2 Check Party Privilege
665 ------------------------------------
666 FUNCTION check_party_privilege
667 (
668 p_api_version IN NUMBER,
669 p_privilege IN VARCHAR2,
670 p_object_name IN VARCHAR2,
671 p_instance_pk1_value IN VARCHAR2,
672 p_instance_pk2_value IN VARCHAR2,
673 p_instance_pk3_value IN VARCHAR2,
674 p_instance_pk4_value IN VARCHAR2,
675 p_instance_pk5_value IN VARCHAR2,
676 p_party_id IN NUMBER
677 )
678 RETURN VARCHAR2
679 IS
680 -- Start OF comments
681 -- API name : check_party_privilege
682 -- TYPE : Public
683 -- Pre-reqs : None
684 -- FUNCTION : check a user's privilege on object instance(s)
685 -- If this operation fails then the check is not
686 -- done and error code is returned.
687
688 -- Version: Current Version 0.1
689 -- Previous Version : None
690 -- Notes :
691 --
692 -- END OF comments
693
694
695 l_grantee_key fnd_grants.GRANTEE_KEY%TYPE;
696 l_grantee_type fnd_grants.GRANTEE_TYPE%TYPE;
697
698 BEGIN
699 -- IF(check_override_datasec(p_party_id)) THEN
700 -- RETURN 'T';
701 -- END IF;
702 IF( p_party_id = -1000) THEN
703 l_grantee_key:='HZ_GLOBAL:'||p_party_id;
704 ELSE
705 l_grantee_key:='HZ_PARTY:'||p_party_id;
706 END IF;
707 RETURN EGO_DATA_SECURITY.check_function
708 (
709 p_api_version => p_api_version,
710 p_function => p_privilege,
711 p_object_name => p_object_name,
712 p_instance_pk1_value => p_instance_pk1_value,
713 p_instance_pk2_value => p_instance_pk2_value,
714 p_instance_pk3_value => p_instance_pk3_value,
715 p_instance_pk4_value => p_instance_pk4_value,
716 p_instance_pk5_value => p_instance_pk5_value,
717 p_user_name => l_grantee_key
718 );
719 END check_party_privilege;
720 ----------------------------------------------------------------------------
721
722
723 --4. Get Privileges
724 ------------------------------------
725 PROCEDURE get_privileges
726 (
727 p_api_version IN NUMBER,
728 p_object_name IN VARCHAR2,
729 p_object_key IN NUMBER,
730 p_user_id IN NUMBER,
731 x_return_status OUT NOCOPY VARCHAR2,
732 x_privilege_tbl OUT NOCOPY EGO_DATA_SECURITY.EGO_PRIVILEGE_NAME_TABLE_TYPE
733 )
734 IS
735
736 -- Start OF comments
737 -- API name : get_privileges
738 -- TYPE : Public
739 -- Pre-reqs : None
740 -- FUNCTION : get the list of privileges user has on the object instance
741 -- If this operation fails then the get is not
742 -- done and error code is returned.
743 --
744 -- Version: Current Version 1.0
745 -- Previous Version : None
746 -- Notes :
747 --
748 -- END OF comments
749 l_party_id NUMBER;
750
751
752
753 CURSOR get_party_id(cp_user_id NUMBER) IS
754 SELECT customer_id
755 FROM fnd_user
756 WHERE user_id=cp_user_id;
757 BEGIN
758
759 OPEN get_party_id (cp_user_id => p_user_id);
760 FETCH get_party_id INTO l_party_id;
761 CLOSE get_party_id;
762
763 get_party_privileges ( p_api_version => p_api_version,
764 p_object_name => p_object_name,
765 p_object_key => p_object_key ,
766 p_party_id => l_party_id,
767 x_return_status => x_return_status,
771 ----------------------------------------------------------------------------
768 x_privilege_tbl => x_privilege_tbl);
769
770 END get_privileges;
772
773 --4 b.1 Get Privileges
774 ------------------------------------
775 PROCEDURE get_party_privileges
776 (
777 p_api_version IN NUMBER,
778 p_object_name IN VARCHAR2,
779 p_object_key IN NUMBER,
780 p_party_id IN NUMBER,
781 x_return_status OUT NOCOPY VARCHAR2,
782 x_privilege_tbl OUT NOCOPY EGO_DATA_SECURITY.EGO_PRIVILEGE_NAME_TABLE_TYPE
783 ) IS
784 -- Start OF comments
785 -- API name : get_privileges
786 -- TYPE : Public
787 -- Pre-reqs : None
788 -- FUNCTION : get the list of privileges user has on the object instance
789 -- If this operation fails then the get is not
790 -- done and error code is returned.
791 --
792 -- Version: Current Version 1.0
793 -- Previous Version : None
794 -- Notes :
795 --
796 -- END OF comments
797 BEGIN
798 get_party_privileges
799 (
800 p_api_version => p_api_version,
801 p_object_name => p_object_name,
802 p_instance_pk1_value => to_char(p_object_key),
803 p_instance_pk2_value => null,
804 p_instance_pk3_value => null,
805 p_instance_pk4_value => null,
806 p_instance_pk5_value => null,
807 p_party_id => p_party_id,
808 x_return_status => x_return_status,
809 x_privilege_tbl => x_privilege_tbl
810 );
811 END get_party_privileges;
812
813 ------------------------------------
814
815
816 --4 b.2 get_party_privileges
817 ------------------------------------
818 PROCEDURE get_party_privileges
819 (
820 p_api_version IN NUMBER,
821 p_object_name IN VARCHAR2,
822 p_instance_pk1_value IN VARCHAR2,
823 p_instance_pk2_value IN VARCHAR2,
824 p_instance_pk3_value IN VARCHAR2,
825 p_instance_pk4_value IN VARCHAR2,
826 p_instance_pk5_value IN VARCHAR2,
827 p_party_id IN NUMBER,
828 x_return_status OUT NOCOPY VARCHAR2,
829 x_privilege_tbl OUT NOCOPY EGO_DATA_SECURITY.EGO_PRIVILEGE_NAME_TABLE_TYPE
830 )
831 IS
832
833 --x_functions_tbl EGO_DATA_SECURITY.EGO_PRIVILEGE_NAME_TABLE_TYPE;
834
835 l_grantee_key fnd_grants.GRANTEE_KEY%TYPE;
836 l_grantee_type fnd_grants.GRANTEE_TYPE%TYPE;
837 l_index INTEGER;
838 CURSOR get_party_type (cp_party_id NUMBER)
839 IS
840 SELECT party_type
841 FROM hz_parties
842 WHERE party_id=cp_party_id;
843
844 CURSOR get_object_privileges(cp_object_name VARCHAR2) IS
845 select function_name
846 from fnd_form_functions privs,
847 fnd_objects obj
848 where obj.obj_name=cp_object_name
849 AND obj.object_id=privs.object_id;
850
851
852 BEGIN
853 -- IF(check_override_datasec(p_party_id)) THEN
854 -- l_index:=0;
855 -- x_return_status:='T';
856 -- FOR rec IN get_object_privileges(cp_object_name => p_object_name) LOOP
857 -- x_privilege_tbl(l_index):=rec.function_name;
858 -- l_index:=l_index+1;
859 -- END LOOP;
860 -- RETURN ;
861 -- END IF;
862
863 IF( p_party_id = -1000) THEN
864 l_grantee_key:='HZ_GLOBAL:'||p_party_id;
865 ELSE
866 l_grantee_key:='HZ_PARTY:'||p_party_id;
867 END IF;
868 EGO_DATA_SECURITY.get_functions(
869 p_api_version => p_api_version,
870 p_object_name => p_object_name,
871 p_instance_pk1_value => p_instance_pk1_value ,
872 p_instance_pk2_value => p_instance_pk2_value,
873 p_instance_pk3_value => p_instance_pk3_value,
874 p_instance_pk4_value => p_instance_pk4_value,
875 p_instance_pk5_value => p_instance_pk5_value,
876 p_user_name => l_grantee_key,
877 x_return_status => x_return_status,
878 x_privilege_tbl => x_privilege_tbl
879 );
880
881 END get_party_privileges;
882 ----------------------------------------------------------------------------
883 --5. Get instances
884 -----------------------------------------------
885 PROCEDURE get_instances_with_privilege
886 (
887 p_api_version IN NUMBER,
888 p_privilege IN VARCHAR2,
889 p_object_name IN VARCHAR2,
890 p_party_id IN NUMBER,
891 x_return_status OUT NOCOPY VARCHAR2,
892 x_object_key_tbl OUT NOCOPY ID_TBL_TYPE
893 )
894 IS
895 x_object_key_tbl_fnd EGO_DATA_SECURITY.EGO_INSTANCE_TABLE_TYPE;
896 l_grantee_key FND_GRANTS.grantee_key%TYPE;
897
898
899 BEGIN
900
901
902 IF( p_party_id = -1000) THEN
903 l_grantee_key:='HZ_GLOBAL:'||p_party_id;
904 ELSE
905 l_grantee_key:='HZ_PARTY:'||p_party_id;
906 END IF;
907
908 EGO_DATA_SECURITY.get_instances
909 (
910 p_api_version => p_api_version,
914 x_return_status => x_return_status,
911 p_function => p_privilege,
912 p_object_name => p_object_name,
913 p_user_name => l_grantee_key,
915 x_object_key_tbl => x_object_key_tbl_fnd
916 );
917
918 IF ( x_object_key_tbl_fnd.count >0) THEN
919 FOR i IN x_object_key_tbl_fnd.first .. x_object_key_tbl_fnd.last LOOP
920 x_object_key_tbl(i) :=x_object_key_tbl_fnd(i).PK1_VALUE;
921 END LOOP;
922 END IF;
923 null;
924 END get_instances_with_privilege;
925
926 ---------------------------------------------------------------------
927 --6. get_instances_with_privilege_d
928 ------------------------------------------------
929 PROCEDURE get_instances_with_privilege_d
930 (
931 p_api_version IN NUMBER,
932 p_privilege IN VARCHAR2,
933 p_object_name IN VARCHAR2,
934 p_party_id IN NUMBER,
935 p_delimiter IN VARCHAR2 DEFAULT ',',
936 x_return_status OUT NOCOPY VARCHAR2,
937 x_object_string OUT NOCOPY VARCHAR2
938 )
939 IS
940 -- Start OF comments
941 -- API name : get_instances_with_privilege_d
942 -- TYPE : Public
943 -- Pre-reqs : None
944 -- FUNCTION : get the list of instances on whcih the user has privilege
945 -- If this operation fails then the get is not
946 -- done and error code is returned. It is same as get_instances_with_privilege, but it -- gives the output as comma delimited object_instances.
947 --
948 -- Version: Current Version 0.1
949 -- Previous Version : None
950 -- Notes :
951 --
952 -- END OF comments
953 x_object_key_tbl ID_TBL_TYPE ;
954
955
956 BEGIN
957 x_object_string:='';
958 get_instances_with_privilege(
959 p_api_version,
960 p_privilege,
961 p_object_name,
962 p_party_id,
963 x_return_status,
964 x_object_key_tbl);
965
966 IF ( x_object_key_tbl.count > 0) THEN
967 FOR i IN x_object_key_tbl.first .. x_object_key_tbl.last LOOP
968 x_object_string:=x_object_string || x_object_key_tbl(i) || p_delimiter;
969 END LOOP;
970 x_object_string := RTRIM(x_object_string,p_delimiter);
971 END IF;
972 x_return_status := FND_API.G_RET_STS_SUCCESS;
973
974 END get_instances_with_privilege_d;
975 ----------------------------------------------------------------------------
976
977 --7.a. Get the list of predicates Strings on whcih user has privilege
978 --------------------------------------------------------
979 FUNCTION get_security_predicate
980 (
981 p_api_version IN NUMBER,
982 p_user_id IN NUMBER,
983 p_privilege IN VARCHAR2,
984 p_object_name IN VARCHAR2,
985 p_grant_type IN VARCHAR2 DEFAULT 'UNIVERSAL'
986 ) RETURN VARCHAR2
987 IS
988 -- Start OF comments
989 -- API name : get_security_predicate
990 -- TYPE : Public
991 -- Pre-reqs : None
992 -- Version: Current Version 1.0
993 -- Previous Version : None
994 -- Notes :
995 --
996 -- END OF comments
997 l_api_name CONSTANT VARCHAR2(30) := 'get_security_predicate';
998 l_pk1_column fnd_objects.PK1_COLUMN_NAME%TYPE;
999
1000 CURSOR get_db_object (cp_object_name VARCHAR2) IS
1001 SELECT PK1_COLUMN_NAME
1002 FROM fnd_objects
1003 WHERE OBJ_NAME=cp_object_name;
1004
1005 BEGIN
1006
1007 OPEN get_db_object(p_object_name);
1008 FETCH get_db_object INTO l_pk1_column;
1009 CLOSE get_db_object;
1010
1011
1012
1013 RETURN get_security_predicate(p_api_version=>p_api_version,
1014 p_user_id =>p_user_id,
1015 p_privilege =>p_privilege,
1016 p_object_name =>p_object_name,
1017 p_aliased_pk_column=>l_pk1_column,
1018 p_grant_type => p_grant_type);
1019
1020
1021 END get_security_predicate;
1022 ------------------------------------------------------------------------------------
1023
1024 --7.b. Get the list of predicates Strings on which user has privilege
1025 FUNCTION get_security_predicate
1026 (
1027 p_api_version IN NUMBER,
1028 p_user_id IN NUMBER,
1029 p_privilege IN VARCHAR2,
1030 p_object_name IN VARCHAR2,
1031 p_aliased_pk_column IN VARCHAR2,
1032 p_grant_type IN VARCHAR2 DEFAULT 'UNIVERSAL'
1033 ) RETURN VARCHAR2
1034 IS
1035 -- Start OF comments
1036 -- API name : get_security_predicate
1037 -- TYPE : Public
1038 -- Pre-reqs : None
1039 -- FUNCTION : Returns the predicates belong to a user with a given privilege.
1040
1041
1042 -- Version: Current Version 1.0
1043 -- Previous Version : None
1044 -- Notes :
1048 CURSOR get_party_id(cp_user_id NUMBER) IS
1045 --
1046 -- END OF comments
1047 l_party_id NUMBER;
1049 SELECT customer_id
1050 FROM fnd_user
1051 WHERE user_id=cp_user_id;
1052
1053 BEGIN
1054
1055 OPEN get_party_id (cp_user_id => p_user_id);
1056 FETCH get_party_id INTO l_party_id;
1057 CLOSE get_party_id;
1058
1059
1060 RETURN get_party_security_predicate (
1061 p_api_version => p_api_version,
1062 p_party_id => l_party_id,
1063 p_privilege => p_privilege,
1064 p_object_name => p_object_name,
1065 p_aliased_pk_column => p_aliased_pk_column,
1066 p_grant_type => p_grant_type);
1067
1068 END get_security_predicate;
1069 ------------------------------------------------------------------------------------
1070
1071 --7.c.1 Get the list of predicates Strings on whcih user has privilege
1072 --------------------------------------
1073 FUNCTION get_party_security_predicate
1074 (
1075 p_api_version IN NUMBER,
1076 p_party_id IN NUMBER,
1077 p_privilege IN VARCHAR2,
1078 p_object_name IN VARCHAR2,
1079 p_aliased_pk_column IN VARCHAR2,
1080 p_grant_type IN VARCHAR2 DEFAULT 'UNIVERSAL'
1081 ) RETURN VARCHAR2
1082 IS
1083
1084 x_return_status VARCHAR2(1);
1085 BEGIN
1086 RETURN get_party_security_predicate
1087 (
1088 p_api_version => p_api_version,
1089 p_party_id => p_party_id,
1090 p_privilege => p_privilege,
1091 p_object_name => p_object_name,
1092 p_aliased_pk_column => p_aliased_pk_column,
1093 p_pk2_alias => null,
1094 p_pk3_alias => null,
1095 p_pk4_alias => null,
1096 p_pk5_alias => null,
1097 p_grant_type => p_grant_type,
1098 x_return_status => x_return_status
1099 );
1100 END get_party_security_predicate;
1101 ----------------------------------------------------------------
1102
1103 --7.c.2 Get the list of predicates Strings on whcih user has privilege
1104 --------------------------------------
1105 FUNCTION get_party_security_predicate
1106 (
1107 p_api_version IN NUMBER,
1108 p_party_id IN NUMBER,
1109 p_privilege IN VARCHAR2,
1110 p_object_name IN VARCHAR2,
1111 p_aliased_pk_column IN VARCHAR2,
1112 p_pk2_alias IN VARCHAR2,
1113 p_pk3_alias IN VARCHAR2,
1114 p_pk4_alias IN VARCHAR2,
1115 p_pk5_alias IN VARCHAR2,
1116 p_grant_type IN VARCHAR2 DEFAULT 'UNIVERSAL',
1117 x_return_status OUT NOCOPY VARCHAR2
1118 ) RETURN VARCHAR2
1119 IS
1120 -- Start OF comments
1121 -- API name : get_security_predicate
1122 -- TYPE : Public
1123 -- Pre-reqs : None
1124 -- FUNCTION : Returns the predicates belong to a party with a given privilege.
1125 --
1126 -- Version: Current Version 1.0
1127 -- Previous Version : None
1128 -- Notes :
1129 --
1130 -- END OF comments
1131
1132 l_grantee_key fnd_grants.grantee_key%TYPE;
1133 l_grantee_type fnd_grants.grantee_type%TYPE;
1134 x_predicate VARCHAR2(32000);
1135
1136
1137 BEGIN
1138 -- IF(check_override_datasec(p_party_id)) THEN
1139 -- RETURN ' 1=1 ';
1140 -- END IF;
1141 IF( p_party_id = -1000) THEN
1142 l_grantee_key:='HZ_GLOBAL:'||p_party_id;
1143 ELSE
1144 l_grantee_key:='HZ_PARTY:'||p_party_id;
1145 END IF;
1146
1147 EGO_DATA_SECURITY.get_security_predicate
1148 (
1149 p_api_version => p_api_version,
1150 p_function => p_privilege,
1151 p_object_name => p_object_name,
1152 p_grant_instance_type => p_grant_type,
1153 p_user_name => l_grantee_key,
1154 p_statement_type => 'OTHER',
1155 p_pk1_alias => p_aliased_pk_column,
1156 p_pk2_alias => p_pk2_alias,
1157 p_pk3_alias => p_pk3_alias,
1158 p_pk4_alias => p_pk4_alias,
1159 p_pk5_alias => p_pk5_alias,
1160 x_predicate => x_predicate,
1161 x_return_status => x_return_status
1162 );
1163 RETURN x_predicate;
1164 END get_party_security_predicate;
1165 ------------------------------------------------------------------------------------
1166
1167
1168 --8.a Get Privileges as comma delimited string
1169 ------------------------------------
1170 PROCEDURE get_privileges_d
1171 (
1172 p_api_version IN NUMBER,
1173 p_object_name IN VARCHAR2,
1174 p_object_key IN NUMBER,
1175 p_user_id IN NUMBER,
1176 p_delimiter IN VARCHAR2 DEFAULT ',',
1177 x_return_status OUT NOCOPY VARCHAR2,
1178 x_privileges_string OUT NOCOPY VARCHAR2
1179 )IS
1180
1181 -- Start OF comments
1182 -- API name : get_security_predicate
1183 -- TYPE : Public
1184 -- Pre-reqs : None
1185 -- FUNCTION : It returns all previleges as a string seperating the privileges with comma.
1186
1190 --
1187 -- Version: Current Version 1.0
1188 -- Previous Version : None
1189 -- Notes :
1191 -- END OF comments
1192
1193 l_api_version CONSTANT NUMBER := 1.0;
1194 l_privilege_tbl EGO_DATA_SECURITY.EGO_PRIVILEGE_NAME_TABLE_TYPE ;
1195
1196 BEGIN
1197 get_privileges(p_api_version,
1198 p_object_name,
1199 p_object_key ,
1200 p_user_id ,
1201 x_return_status,
1202 l_privilege_tbl);
1203 x_privileges_string:='';
1204 IF ( l_privilege_tbl.count >0) THEN
1205 FOR i IN l_privilege_tbl.first .. l_privilege_tbl.last LOOP
1206
1207 x_privileges_string :=x_privileges_string ||l_privilege_tbl(i) || p_delimiter;
1208 END LOOP;
1209 -- strip off the trailing ', '
1210 x_privileges_string := substr(x_privileges_string, 1,
1211 length(x_privileges_string) - length(p_delimiter));
1212 END IF;
1213
1214
1215 END get_privileges_d;
1216 ------------------------------------------------------------------------------------
1217
1218 --8.b Get Privileges as comma delimited string
1219 ------------------------------------
1220 PROCEDURE get_party_privileges_d
1221 (
1222 p_api_version IN NUMBER,
1223 p_object_name IN VARCHAR2,
1224 p_object_key IN NUMBER,
1225 p_party_id IN NUMBER,
1226 p_delimiter IN VARCHAR2 DEFAULT ',',
1227 x_return_status OUT NOCOPY VARCHAR2,
1228 x_privileges_string OUT NOCOPY VARCHAR2
1229 )IS
1230
1231 -- Start OF comments
1232 -- API name : get_security_predicate
1233 -- TYPE : Public
1234 -- Pre-reqs : None
1235 -- FUNCTION : It returns all previleges as a string seperating the privileges with comma.
1236
1237 -- Version: Current Version 1.0
1238 -- Previous Version : None
1239 -- Notes :
1240 --
1241 -- END OF comments
1242
1243 BEGIN
1244 get_party_privileges_d
1245 (
1246 p_api_version => p_api_version,
1247 p_object_name => p_object_name,
1248 p_pk1_value => to_char(p_object_key),
1249 p_pk2_value => null,
1250 p_pk3_value => null,
1251 p_pk4_value => null,
1252 p_pk5_value => null,
1253 p_party_id => p_party_id,
1254 p_delimiter => p_delimiter,
1255 x_return_status => x_return_status,
1256 x_privileges_string => x_privileges_string
1257 );
1258
1259 END get_party_privileges_d;
1260 ------------------------------------------------------------------------------------
1261
1262 --8.c Get Privileges as comma delimited string
1263 ------------------------------------
1264 PROCEDURE get_party_privileges_d
1265 (
1266 p_api_version IN NUMBER,
1267 p_object_name IN VARCHAR2,
1268 p_pk1_value IN VARCHAR2,
1269 p_pk2_value IN VARCHAR2,
1270 p_pk3_value IN VARCHAR2,
1271 p_pk4_value IN VARCHAR2,
1272 p_pk5_value IN VARCHAR2,
1273 p_party_id IN NUMBER,
1274 p_delimiter IN VARCHAR2 DEFAULT ',',
1275 x_return_status OUT NOCOPY VARCHAR2,
1276 x_privileges_string OUT NOCOPY VARCHAR2
1277 )IS
1278
1279 -- Start OF comments
1280 -- API name : get_security_predicate
1281 -- TYPE : Public
1282 -- Pre-reqs : None
1283 -- FUNCTION : It returns all previleges as a string seperating the privileges with comma.
1284
1285 -- Version: Current Version 1.0
1286 -- Previous Version : None
1287 -- Notes :
1288 --
1289 -- END OF comments
1290
1291 l_api_version CONSTANT NUMBER := 1.0;
1292 l_privilege_tbl EGO_DATA_SECURITY.EGO_PRIVILEGE_NAME_TABLE_TYPE ;
1293
1294 BEGIN
1295 get_party_privileges
1296 (
1297 p_api_version => p_api_version,
1298 p_object_name => p_object_name,
1299 p_instance_pk1_value => p_pk1_value,
1300 p_instance_pk2_value => p_pk2_value,
1301 p_instance_pk3_value => p_pk3_value,
1302 p_instance_pk4_value => p_pk4_value,
1303 p_instance_pk5_value => p_pk5_value,
1304 p_party_id => p_party_id,
1305 x_return_status => x_return_status,
1306 x_privilege_tbl => l_privilege_tbl
1307 );
1308
1309 x_privileges_string:='';
1310 IF ( l_privilege_tbl.count >0) THEN
1311 FOR i IN l_privilege_tbl.first .. l_privilege_tbl.last LOOP
1312 x_privileges_string :=x_privileges_string ||l_privilege_tbl(i) || p_delimiter;
1313 END LOOP;
1314 -- strip off the trailing ', '
1315 x_privileges_string := substr(x_privileges_string, 1,
1316 length(x_privileges_string) - length(p_delimiter));
1317 END IF;
1318
1319
1320 END get_party_privileges_d;
1321 ------------------------------------------------------------------------------------
1322
1323
1324 --9. Set end date to a grant
1325 ------------------------------------
1326 PROCEDURE set_grant_date
1327 (
1328 p_api_version IN NUMBER,
1329 p_grant_guid IN VARCHAR2,
1330 p_start_date IN DATE,
1331 p_end_date IN DATE,
1332 x_return_status OUT NOCOPY VARCHAR2
1333 )IS
1334 -- Start OF comments
1335 -- API name : SET_GRANT_DATE
1336 -- TYPE : Public
1337 -- Pre-reqs : None
1338 -- FUNCTION :sets start date and end date to a grant
1339 --
1340 --
1341 --
1342 -- Version: Current Version 1.0
1343 -- Previous Version : None
1344 -- Notes :
1345 --
1346 -- END OF comments
1347
1348 --x_success VARCHAR2(2);
1349 l_dummy VARCHAR2(1);
1350 l_grant_guid fnd_grants.grant_guid%TYPE;
1351 CURSOR get_grant_guid(cp_grant_id VARCHAR2,
1352 cp_start_date DATE,
1353 cp_end_date DATE)
1354 IS
1355 SELECT g1.grant_guid
1356 FROM fnd_grants g1, fnd_grants g2
1357 WHERE g1.grant_guid=HEXTORAW(cp_grant_id)
1358 AND g2.grant_guid<>HEXTORAW(cp_grant_id)
1359 AND g1.object_id=g2.object_id
1360 AND g1.menu_id=g2.menu_id
1361 AND g1.instance_type=g2.instance_type
1362 -- 3729803
1363 -- query must take care of instance sets as well
1364 AND NVL(g1.instance_set_id,-1) = NVL(g2.instance_set_id,-1)
1365 AND g1.instance_pk1_value=g2.instance_pk1_value
1366 AND g1.grantee_type=g2.grantee_type
1367 AND g1.grantee_key=g2.grantee_key
1368 AND (
1369 ((g2.start_date<=cp_start_date )
1370 AND (( g2.end_date IS NULL) OR (cp_start_date<=g2.end_date )))
1371 OR ((g2.start_date >= cp_start_date )
1372 AND (( cp_end_date IS NULL) OR (cp_end_date>=g2.start_date)))
1373 );
1374
1375 BEGIN
1376 OPEN get_grant_guid(cp_grant_id=>p_grant_guid,
1377 cp_start_date=>p_start_date,
1378 cp_end_date=>p_end_date);
1379 FETCH get_grant_guid INTO l_grant_guid;
1380
1381 IF( get_grant_guid%NOTFOUND) THEN
1382 fnd_grants_pkg.update_grant (
1383 p_api_version => p_api_version,
1384 p_grant_guid => HEXTORAW(p_grant_guid),
1385 p_start_date => p_start_date,
1386 p_end_date => p_end_date,
1387 x_success => x_return_status
1388 );
1389 ELSE
1390 x_return_status:='F';
1391
1392 END IF;
1393
1394 CLOSE get_grant_guid;
1395
1396 END set_grant_date;
1397 ----------------------------------------------------------------------------
1398
1399 /*
1400 --12. Check_Instance_In_Set
1401 ----------------------------
1402 FUNCTION check_instance_in_set
1403 (
1404 p_api_version IN NUMBER,
1405 p_instance_set_id IN NUMBER,
1406 p_instance_pk1_value IN VARCHAR2
1407 ) return VARCHAR2
1408 IS
1409 l_instance_set_name fnd_object_instance_sets.instance_set_name%TYPE;
1410 CURSOR get_instance_set_name (cp_instance_set_id NUMBER)
1411 IS
1412 SELECT instance_set_name
1413 FROM fnd_object_instance_sets
1414 WHERE instance_set_id=cp_instance_set_id ;
1415
1416 BEGIN
1417 OPEN get_instance_set_name(cp_instance_set_id=>p_instance_set_id);
1418 FETCH get_instance_set_name INTO l_instance_set_name;
1419 CLOSE get_instance_set_name;
1420 RETURN EGO_DATA_SECURITY.check_instance_in_set
1421 (
1422 p_api_version => p_api_version ,
1423 p_instance_set_name => l_instance_set_name,
1424 p_instance_pk1_value => p_instance_pk1_value,
1425 p_instance_pk2_value => null,
1426 p_instance_pk3_value => null,
1427 p_instance_pk4_value => null,
1428 p_instance_pk5_value => null
1429 );
1430
1431 END check_instance_in_set;
1432 -------------------------------------------------------------
1433 */
1434
1435 --12. Check_Instance_In_Set
1436 ------------------------
1437 FUNCTION check_instance_in_set
1438 (
1439 p_api_version IN NUMBER,
1440 p_object_name IN VARCHAR2,
1441 p_instance_set_id IN NUMBER,
1442 p_instance_id IN NUMBER,
1443 p_party_person_id IN NUMBER
1444 )
1445 RETURN VARCHAR2
1446 IS
1447
1448 l_api_version CONSTANT NUMBER := 1.0;
1449 l_api_name CONSTANT VARCHAR2(30) := 'check_instance_in_set';
1450
1451 l_sysdate DATE := Sysdate;
1452
1453 l_dynamic_sql VARCHAR2(32767);
1454 l_pk1_column fnd_objects.PK1_COLUMN_NAME%TYPE;
1455 l_instance_flag BOOLEAN DEFAULT TRUE;
1456 l_instance_set_flag BOOLEAN DEFAULT TRUE;
1457 l_set_predicate VARCHAR2(32767);
1458 l_db_object_name fnd_objects.DATABASE_OBJECT_NAME%TYPE;
1459 l_db_pk_column fnd_objects.PK1_COLUMN_NAME%TYPE;
1460 l_result VARCHAR2(1);
1461 l_dummy VARCHAR2(1);
1462
1463
1464 TYPE DYNAMIC_CUR IS REF CURSOR;
1465 instance_sets_cur DYNAMIC_CUR;
1466
1467
1468 CURSOR predicate_c (cp_object_name VARCHAR2,
1469 cp_instance_set_id NUMBER)
1470 IS
1471 SELECT DISTINCT obj.pk1_column_name, obj.database_object_name, sets.predicate
1472 FROM fnd_objects obj,
1473 fnd_object_instance_sets sets
1474 WHERE obj.obj_name = cp_object_name
1475 AND obj.object_id = sets.object_id
1476 AND sets.instance_set_id = cp_instance_set_id;
1477
1478
1479
1480 BEGIN
1481 IF NOT FND_API.Compatible_API_Call (l_api_version,
1482 p_api_version,
1483 l_api_name ,
1484 G_PKG_NAME)
1485 THEN
1486 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1487 END IF;
1488
1489 -- Step 1.
1490 OPEN predicate_c (p_object_name, p_instance_set_id);
1491 FETCH predicate_c into l_db_pk_column, l_db_object_name, l_set_predicate;
1492 CLOSE predicate_c;
1493 l_set_predicate := REPLACE(l_set_predicate, 'EGO_SCTX.GET_PARTY_PERSON_ID()', p_party_person_id);
1494 --l_set_predicate := REPLACE(l_set_predicate, 'EGO_SCTX.GET_USER_ID()', p_user_id);
1495
1496 IF( length(l_set_predicate ) >0) THEN
1497
1498 l_dynamic_sql := ' SELECT ''X'' FROM sys.dual WHERE EXISTS ' ||
1499 '( SELECT ' || l_db_pk_column || ' FROM ' || l_db_object_name ||
1500 ' WHERE ' || l_db_pk_column || ' = ' || p_instance_id ||
1501 ' AND ' || l_set_predicate || ')';
1502
1503 OPEN instance_sets_cur FOR l_dynamic_sql;
1504 FETCH instance_sets_cur INTO l_dummy;
1505 IF(instance_sets_cur%NOTFOUND) THEN
1506 CLOSE instance_sets_cur;
1507 RETURN FND_API.G_FALSE;
1508 ELSE
1509 CLOSE instance_sets_cur;
1510 RETURN FND_API.G_TRUE;
1511 END IF;
1512 ELSE
1513 --no predicate for the set; universal set
1514 RETURN FND_API.G_TRUE;
1515 END IF;
1516 EXCEPTION
1517 WHEN OTHERS THEN
1518 IF FND_MSG_PUB.Check_Msg_Level
1519 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1520 THEN
1521 FND_MSG_PUB.Add_Exc_Msg
1522 ( G_PKG_NAME ,
1523 l_api_name
1524 );
1525 END IF;
1526 RETURN FND_API.G_FALSE;
1527
1528 END check_instance_in_set;
1529 ---------------------------------------------------------
1530
1531 --13. check_duplicate_grant
1532 ------------------------
1533 FUNCTION check_duplicate_grant
1534 (
1535 p_role_name IN VARCHAR2,
1536 p_object_name IN VARCHAR2,
1537 p_object_key_type IN VARCHAR2,
1538 p_object_key IN NUMBER,
1539 p_party_id IN NUMBER,
1540 p_start_date IN DATE,
1541 p_end_date IN DATE
1542 ) RETURN VARCHAR2
1543 IS
1544 -- Start OF comments
1545 -- API name : check_duplicate_grant
1546 -- TYPE : Public
1547 -- Pre-reqs : None
1548 -- FUNCTION : checks for duplicate grant
1549
1550 -- Parameters:
1551 -- IN : p_role_name IN VARCHAR2(Required)
1552 -- Role Name
1553 --
1554 -- IN :p_object_name IN VARCHAR2(Required)
1555 -- Object name
1556 -- IN :p_object_key_type IN VARCHAR2(Required)
1557 -- Object Key Type
1558 --
1559 -- IN :p_object_key IN NUMBER,
1560 -- Object Key
1561 --
1562 -- IN :p_party_id IN NUMBER,
1563 -- party id
1564 --
1565 -- IN :p_start_date IN DATE,
1566 -- Start date
1567 --
1568 -- IN :p_end date IN DATE,
1569 -- End date
1570 --
1571 -- OUT :
1572 -- RETURN
1573 -- FND_API.G_TRUE IF this grant already exist (duplicate grant)
1574 -- FND_API.G_FALSE NO IF it is not Duplicate grant
1575 -- FND_API.G_RET_STS_ERROR if error
1576 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error
1577
1578 -- Version: Current Version 0.1
1579 -- Previous Version : None
1580 -- Notes :
1581 --
1582 -- END OF comments
1583
1584
1585 l_party_id NUMBER;
1586 l_role_id NUMBER;
1587
1588 CURSOR get_role_id(cp_role_name VARCHAR2) IS
1589 SELECT menu_id
1590 FROM fnd_menus
1591 WHERE menu_name =cp_role_name;
1592
1593 CURSOR check_for_duplicate (cp_grantee_key VARCHAR2,
1594 cp_grantee_type VARCHAR2,
1595 cp_menu_name VARCHAR2,
1596 cp_object_name VARCHAR2,
1597 cp_instance_type VARCHAR2,
1598 cp_instance_pk1_value VARCHAR2,
1599 cp_instance_pk2_value VARCHAR2,
1600 cp_instance_pk3_value VARCHAR2,
1601 cp_instance_pk4_value VARCHAR2,
1602 cp_instance_pk5_value VARCHAR2,
1603 cp_instance_set_id NUMBER,
1604 cp_start_date DATE,
1605 cp_end_date DATE) IS
1606
1607 SELECT 'X'
1608 FROM fnd_grants grants,
1609 fnd_objects obj,
1610 fnd_menus menus
1611 WHERE grants.grantee_key=cp_grantee_key
1612 AND grants.grantee_type=cp_grantee_type
1613 AND grants.menu_id=menus.menu_id
1614 AND menus.menu_name=cp_menu_name
1615 AND grants.object_id = obj.object_id
1616 AND obj.obj_name=cp_object_name
1617 AND grants.instance_type=cp_instance_type
1618 AND ((grants.instance_pk1_value=cp_instance_pk1_value )
1619 OR((grants.instance_pk1_value = '*NULL*') AND (cp_instance_pk1_value IS NULL)))
1620 AND ((grants.instance_pk2_value=cp_instance_pk2_value )
1621 OR((grants.instance_pk2_value = '*NULL*') AND (cp_instance_pk2_value IS NULL)))
1622 AND ((grants.instance_pk3_value=cp_instance_pk3_value )
1623 OR((grants.instance_pk3_value = '*NULL*') AND (cp_instance_pk3_value IS NULL)))
1624 AND ((grants.instance_pk4_value=cp_instance_pk4_value )
1625 OR((grants.instance_pk4_value = '*NULL*') AND (cp_instance_pk4_value IS NULL)))
1626 AND ((grants.instance_pk5_value=cp_instance_pk5_value )
1627 OR((grants.instance_pk5_value = '*NULL*') AND (cp_instance_pk5_value IS NULL)))
1628 AND ((grants.instance_set_id=cp_instance_set_id )
1629 OR((grants.instance_set_id IS NULL ) AND (cp_instance_set_id IS NULL)))
1630 AND (((grants.start_date<=cp_start_date )
1631 AND (( grants.end_date IS NULL) OR (cp_start_date <=grants.end_date )))
1632 OR ((grants.start_date >= cp_start_date )
1633 AND (( cp_end_date IS NULL) OR (cp_end_date >=grants.start_date))));
1634
1635 l_grantee_type hz_parties.party_type%TYPE;
1636 l_grantee_key fnd_grants.grantee_key%TYPE;
1637 l_instance_set_id fnd_grants.instance_set_id%TYPE;
1638 l_instance_pk1_value fnd_grants.instance_pk1_value%TYPE;
1639 l_dummy VARCHAR2(1);
1640 CURSOR get_party_type (cp_party_id NUMBER)
1641 IS
1642 SELECT party_type
1643 FROM hz_parties
1644 WHERE party_id=cp_party_id;
1645
1646
1647 BEGIN
1648
1649
1650 IF( p_object_key_type ='SET') THEN
1651 l_instance_set_id:=p_object_key;
1652 l_instance_pk1_value:= null;
1653 ELSE
1654 l_instance_set_id:=null;
1655 l_instance_pk1_value:= to_char(p_object_key);
1656 END IF;
1657 OPEN get_party_type (cp_party_id =>p_party_id);
1658 FETCH get_party_type INTO l_grantee_type;
1659 CLOSE get_party_type;
1660 IF( p_party_id = -1000) THEN
1661 l_grantee_type :='GLOBAL';
1662 l_grantee_key:='HZ_GLOBAL:'||p_party_id;
1663 ELSIF (l_grantee_type ='PERSON') THEN
1664 l_grantee_type:='USER';
1665 l_grantee_key:='HZ_PARTY:'||p_party_id;
1666 ELSIF (l_grantee_type ='GROUP') THEN
1667 l_grantee_type:='GROUP';
1668 l_grantee_key:='HZ_GROUP:'||p_party_id;
1669 ELSIF (l_grantee_type ='ORGANIZATION') THEN
1670 l_grantee_type:='COMPANY';
1671 l_grantee_key:='HZ_COMPANY:'||p_party_id;
1672 ELSE
1673 null;
1674 END IF;
1675
1676 OPEN check_for_duplicate(cp_grantee_key => l_grantee_key,
1677 cp_grantee_type => l_grantee_type,
1678 cp_menu_name => p_role_name,
1679 cp_object_name => p_object_name,
1680 cp_instance_type => p_object_key_type,
1681 cp_instance_pk1_value => l_instance_pk1_value,
1682 cp_instance_pk2_value => null,
1683 cp_instance_pk3_value => null,
1684 cp_instance_pk4_value => null,
1685 cp_instance_pk5_value => null,
1686 cp_instance_set_id => l_instance_set_id,
1687 cp_start_date => p_start_date,
1688 cp_end_date => p_end_date);
1689 FETCH check_for_duplicate INTO l_dummy;
1690 IF( check_for_duplicate%NOTFOUND) THEN
1691 CLOSE check_for_duplicate ;
1692 RETURN FND_API.G_FALSE;
1693 ELSE
1694 CLOSE check_for_duplicate ;
1695 RETURN FND_API.G_TRUE;
1696 END IF;
1697
1698 EXCEPTION
1699 WHEN OTHERS THEN
1700 RETURN FND_API.G_RET_STS_ERROR;
1701
1702
1703 END check_duplicate_grant;
1704 ---------------------------------------------------------
1705
1706
1707 --14. check_duplicate_item_grant
1708 ------------------------
1709 FUNCTION check_duplicate_item_grant
1710 (
1711 p_role_id IN NUMBER,
1712 p_object_id IN NUMBER,
1713 p_object_key_type IN VARCHAR2,
1714 p_object_key IN NUMBER,
1715 p_party_id IN NUMBER,
1716 p_start_date IN DATE,
1717 p_end_date IN DATE
1718 ) RETURN VARCHAR2
1719 IS
1720 -- Start OF comments
1721 -- API name : check_duplicate_item_grant
1722 -- TYPE : Public
1723 -- Pre-reqs : None
1724 -- FUNCTION : checks for duplicate grant
1725
1726 -- Parameters:
1727 -- IN : p_role_name IN VARCHAR2(Required)
1728 -- Role Name
1729 --
1730 -- IN :p_object_name IN VARCHAR2(Required)
1731 -- Object name
1732 -- IN :p_object_key_type IN VARCHAR2(Required)
1733 -- Object Key Type
1734 --
1735 -- IN :p_object_key IN NUMBER,
1736 -- Object Key
1737 --
1738 -- IN :p_party_id IN NUMBER,
1739 -- party id
1740 --
1741 -- IN :p_start_date IN DATE,
1742 -- Start date
1743 --
1744 -- IN :p_end date IN DATE,
1745 -- End date
1746 --
1747 -- OUT :
1748 -- RETURN
1749 -- FND_API.G_TRUE IF this grant already exist (duplicate grant)
1750 -- FND_API.G_FALSE NO IF it is not Duplicate grant
1751 -- FND_API.G_RET_STS_ERROR if error
1752 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error
1753
1754 -- Version: Current Version 0.1
1755 -- Previous Version : None
1756 -- Notes :
1757 --
1758 -- END OF comments
1759
1760
1761 l_party_id NUMBER;
1762 l_role_id NUMBER;
1763
1764 CURSOR check_for_duplicate (cp_grantee_key VARCHAR2,
1765 cp_grantee_type VARCHAR2,
1766 cp_menu_id NUMBER,
1767 cp_object_id NUMBER,
1768 cp_instance_type VARCHAR2,
1769 cp_instance_pk1_value VARCHAR2,
1770 cp_instance_set_id NUMBER,
1771 cp_start_date DATE,
1772 cp_end_date DATE) IS
1773 SELECT 'X'
1774 FROM fnd_grants grants
1775 WHERE grants.grantee_key=cp_grantee_key
1776 AND grants.grantee_type=cp_grantee_type
1777 AND grants.menu_id=cp_menu_id
1778 AND grants.object_id = cp_object_id
1779 AND grants.instance_type=cp_instance_type
1780 AND ((grants.instance_pk1_value=cp_instance_pk1_value )
1781 OR((grants.instance_pk1_value = '*NULL*') AND (cp_instance_pk1_value IS NULL)))
1782 AND ((grants.instance_set_id=cp_instance_set_id )
1783 OR((grants.instance_set_id IS NULL ) AND (cp_instance_set_id IS NULL)))
1784 AND (((grants.start_date<=cp_start_date )
1785 AND (( grants.end_date IS NULL) OR (cp_start_date <=grants.end_date )))
1786 OR ((grants.start_date >= cp_start_date )
1787 AND (( cp_end_date IS NULL) OR (cp_end_date >=grants.start_date))));
1788
1789 l_grantee_type hz_parties.party_type%TYPE;
1790 l_grantee_key fnd_grants.grantee_key%TYPE;
1791 l_instance_set_id fnd_grants.instance_set_id%TYPE;
1792 l_instance_pk1_value fnd_grants.instance_pk1_value%TYPE;
1793 l_dummy VARCHAR2(1);
1794
1795 CURSOR get_party_type (cp_party_id NUMBER)
1796 IS
1797 SELECT party_type
1798 FROM hz_parties
1799 WHERE party_id=cp_party_id;
1800
1801 BEGIN
1802
1803
1804 IF( p_object_key_type ='SET') THEN
1805 l_instance_set_id:=p_object_key;
1806 l_instance_pk1_value:= null;
1807 ELSE
1808 l_instance_set_id:=null;
1809 l_instance_pk1_value:= to_char(p_object_key);
1810 END IF;
1811 OPEN get_party_type (cp_party_id =>p_party_id);
1812 FETCH get_party_type INTO l_grantee_type;
1813 CLOSE get_party_type;
1814 IF( p_party_id = -1000) THEN
1815 l_grantee_type :='GLOBAL';
1816 l_grantee_key:='HZ_GLOBAL:'||p_party_id;
1817 ELSIF (l_grantee_type ='PERSON') THEN
1818 l_grantee_type:='USER';
1819 l_grantee_key:='HZ_PARTY:'||p_party_id;
1820 ELSIF (l_grantee_type ='GROUP') THEN
1821 l_grantee_type:='GROUP';
1822 l_grantee_key:='HZ_GROUP:'||p_party_id;
1823 ELSIF (l_grantee_type ='ORGANIZATION') THEN
1824 l_grantee_type:='COMPANY';
1825 l_grantee_key:='HZ_COMPANY:'||p_party_id;
1826 ELSE
1827 null;
1828 END IF;
1829
1833 cp_object_id => p_object_id,
1830 OPEN check_for_duplicate(cp_grantee_key => l_grantee_key,
1831 cp_grantee_type => l_grantee_type,
1832 cp_menu_id => p_role_id,
1834 cp_instance_type => p_object_key_type,
1835 cp_instance_pk1_value => l_instance_pk1_value,
1836 cp_instance_set_id => l_instance_set_id,
1837 cp_start_date => p_start_date,
1838 cp_end_date => p_end_date);
1839 FETCH check_for_duplicate INTO l_dummy;
1840 IF( check_for_duplicate%NOTFOUND) THEN
1841 CLOSE check_for_duplicate ;
1842 RETURN FND_API.G_FALSE;
1843 ELSE
1844 CLOSE check_for_duplicate ;
1845 RETURN FND_API.G_TRUE;
1846 END IF;
1847
1848 EXCEPTION
1849 WHEN OTHERS THEN
1850 RETURN FND_API.G_RET_STS_ERROR;
1851
1852
1853 END check_duplicate_item_grant;
1854 ---------------------------------------------------------
1855
1856 --14. creat_instance_set
1857 ------------------------
1858 FUNCTION create_instance_set
1859 (
1860 p_instance_set_name IN VARCHAR2,
1861 p_object_name IN VARCHAR2,
1862 p_predicate IN VARCHAR2,
1863 p_display_name IN VARCHAR2,
1864 p_description IN VARCHAR2
1865 )
1866 RETURN NUMBER
1867 IS
1868
1869 l_api_version CONSTANT NUMBER := 1.0;
1870 l_api_name CONSTANT VARCHAR2(30) := 'check_instance_in_set';
1871
1872 l_instance_set_id NUMBER;
1873
1874
1875
1876
1877 CURSOR get_set_c (cp_instance_set_name VARCHAR2)
1878 IS
1879 SELECT instance_set_id
1880 FROM fnd_object_instance_sets
1881 WHERE instance_set_name = cp_instance_set_name;
1882
1883
1884 BEGIN
1885
1886 -- Step 1.
1887 OPEN get_set_c (p_instance_set_name);
1888 FETCH get_set_c into l_instance_set_id;
1889
1890 IF(get_set_c%NOTFOUND) THEN
1891 CLOSE get_set_c;
1892
1893 FND_OBJECT_INSTANCE_SETS_PKG.LOAD_ROW
1894 (
1895 X_INSTANCE_SET_NAME => p_instance_set_name,
1896 X_OWNER => 'ORACLE',
1897 X_OBJECT_NAME => p_object_name,
1898 X_PREDICATE => p_predicate,
1899 X_DISPLAY_NAME => p_display_name,
1900 X_DESCRIPTION => p_description,
1901 X_CUSTOM_MODE => 'NO_FORCE'
1902 );
1903
1904 ELSE
1905 CLOSE get_set_c;
1906 RETURN l_instance_set_id;
1907 END IF;
1908
1909 -- step 2
1910 OPEN get_set_c (p_instance_set_name);
1911 FETCH get_set_c into l_instance_set_id;
1912
1913 IF(get_set_c%NOTFOUND) THEN
1914 CLOSE get_set_c;
1915 RETURN -1;
1916 ELSE
1917 CLOSE get_set_c;
1918 RETURN l_instance_set_id;
1919 END IF;
1920
1921 EXCEPTION
1922 WHEN OTHERS THEN
1923 IF FND_MSG_PUB.Check_Msg_Level
1924 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1925 THEN
1926 FND_MSG_PUB.Add_Exc_Msg
1927 ( G_PKG_NAME ,
1928 l_api_name
1929 );
1930 END IF;
1931 RETURN -1;
1932
1933 END create_instance_set;
1934 ---------------------------------------------------------
1935
1936
1937 END EGO_SECURITY_PUB;