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