[Home] [Help]
PACKAGE BODY: APPS.EGO_DOM_SECURITY_PVT
Source
1 PACKAGE BODY EGO_DOM_SECURITY_PVT AS
2 /* $Header: EGODMSCB.pls 120.10 2006/10/30 17:40:26 ysireesh noship $ */
3 /*---------------------------------------------------------------------------+
4 | This package contains APIs to reslove docuemnt security |
5 | based on data security |
6 +---------------------------------------------------------------------------*/
7
8 G_PKG_NAME CONSTANT VARCHAR2(30):= 'EGO_DOM_SECURITY_PVT';
9
10 --Private - print_debug_msg
11 ---------------------------
12 PROCEDURE print_debug_msg
13 (
14 p_msg IN VARCHAR2,
15 p_debug BOOLEAN DEFAULT true
16 )
17 IS
18 l_count NUMBER;
19 l_bufsize NUMBER:=1;
20 l_substr VARCHAR2(1000);
21 BEGIN
22 IF( NOT p_debug) THEN
23 RETURN ;
24 END IF;
25 IF( length(p_msg) <= 200) THEN
26 RETURN;
27 END IF;
28 WHILE( l_bufsize<=length(p_msg)) LOOP
29 l_substr := substr(p_msg,l_bufsize,200);
30 l_bufsize := l_bufsize+200;
31 END LOOP;
32 END print_debug_msg;
33
34 -------------------------------------------------------------------------
35 --1. Get Users
36 ----------------------------------------------------
37 PROCEDURE Get_Users
38 (
39 p_api_version IN NUMBER,
40 p_entity_name IN VARCHAR2,
41 p_pk1_value IN VARCHAR2,
42 p_pk2_value IN VARCHAR2,
43 p_pk3_value IN VARCHAR2,
44 p_pk4_value IN VARCHAR2,
45 p_pk5_value IN VARCHAR2,
46 p_role_name IN VARCHAR2 DEFAULT NULL,
47 x_grantee_names OUT NOCOPY FND_TABLE_OF_VARCHAR2_120,
48 x_grantee_types OUT NOCOPY FND_TABLE_OF_VARCHAR2_30,
49 x_role_names OUT NOCOPY FND_TABLE_OF_VARCHAR2_30,
50 x_role_display_names OUT NOCOPY FND_TABLE_OF_VARCHAR2_120,
51 x_default_access OUT NOCOPY FND_TABLE_OF_VARCHAR2_30,
52 x_return_status OUT NOCOPY VARCHAR2
53 )
54 IS
55 -- Start OF comments
56 -- API name : Get Users
57 -- Type : Public
58 -- Pre-reqs : None
59 -- Function : Gets list of users who has some role on business object.
60 -- If no role is passed i.e if p_role_name is null
61 -- then the API returns list of roles available on the item
62 -- with the set of users who bear each role.
63 -- If some role is mentioned, then the list of user who bear
64 -- this role on the item are displayed.
65 -- IN : p_api_version IN NUMBER Required
66 -- p_entity_name IN VARCHAR2 Required
67 -- p_pk1_value IN VARCHAR2 Required
68 -- p_pk2_value IN VARCHAR2 Required
69 -- p_pk3_value IN VARCHAR2
70 -- p_pk4_value IN VARCHAR2
71 -- p_pk5_value IN VARCHAR2
72 -- p_role_name IN VARCHAR2
73 -- Version: Current Version 0.1
74 -- Previous Version : None
75 -- Notes :
76 --
77 -- END OF comments
78 l_entity_name VARCHAR2(30);
79 l_pk1_value VARCHAR2(50);
80 l_pk2_value VARCHAR2(50);
81 l_pk3_value VARCHAR2(50) := '';
82 l_pk4_value VARCHAR2(50);
83 l_pk5_value VARCHAR2(50);
84 l_obj_ids VARCHAR2(32767);
85 q1 VARCHAR2(32767);
86 q2 VARCHAR2(32767);
87 l_role_name VARCHAR2(50);
88 l_role_name_temp VARCHAR2(50);
89 l_temp_menu_id NUMBER;
90 l_index NUMBER:=0;
91 memcount NUMBER:=0;
92
93 /*
94 CURSOR get_object_id (cp_entity_name VARCHAR2)
95 IS
96 SELECT object_id
97 FROM
98 FND_OBJECTS
99 WHERE obj_name = cp_entity_name;
100 */
101
102
103 CURSOR get_explicit_grant_users (cp_entity_name VARCHAR2,
104 cp_pk1_value VARCHAR2,
105 cp_pk2_value VARCHAR2,
106 cp_pk3_value VARCHAR2,
107 cp_pk4_value VARCHAR2,
108 cp_pk5_value VARCHAR2,
109 cp_role_name VARCHAR2)
110 IS
111 SELECT
112 parties.party_name grantee_name,
113 grantee_type,
114 role_name,
115 role_display_name,
116 menu_id
117 FROM hz_parties parties,
118 (
119 SELECT DISTINCT
120 GRANTS.grantee_orig_system_id grantee_orig_system_id,
121 DECODE(grants.grantee_key,'GLOBAL','HZ_GLOBAL', SUBSTR(grants.grantee_key,0, INSTR(grants.grantee_key,':',1,1)-1)) grantee_orig_system,
122 GRANTS.grantee_type grantee_type,
123 GRANTS.grantee_key grantee_key,
124 menus.Menu_name role_name,
125 menus.user_menu_name role_display_name,
126 menus.menu_id menu_id
127 FROM fnd_grants GRANTS,
128 fnd_objects OBJECTS,
129 fnd_menus_vl MENUS
130 WHERE OBJECTS.object_id = GRANTS.object_id
131 AND OBJECTS.obj_name=cp_entity_name
132 AND GRANTS.instance_type='INSTANCE'
133 AND GRANTS.instance_pk1_value=cp_pk1_value
134 AND
135 (
136 (
137 grants.instance_pk2_value = cp_pk2_value
138 )
139 OR
140 (
141 (
142 grants.instance_pk2_value = '*NULL*'
143 )
144 AND
145 (
146 cp_pk2_value is NULL
147 )
148 )
149 )
150 AND
151 (
152 (
153 grants.instance_pk3_value = cp_pk3_value
154 )
155 OR
156 (
157 (
158 grants.instance_pk3_value = '*NULL*'
159 )
160 AND
161 (
162 cp_pk3_value is NULL
163 )
164 )
165 )
166 AND
167 (
168 (
169 grants.instance_pk4_value = cp_pk4_value
170 )
171 OR
172 (
173 (
174 grants.instance_pk4_value = '*NULL*'
175 )
176 AND
177 (
178 cp_pk4_value is NULL
179 )
180 )
181 )
182 AND
183 (
184 (
185 grants.instance_pk5_value = cp_pk5_value
186 )
187 OR
188 (
189 (
190 grants.instance_pk5_value = '*NULL*'
191 )
192 AND
193 (
194 cp_pk5_value is NULL
195 )
196 )
197 )
198 AND GRANTS.menu_id = MENUS.menu_id
199 AND
200 (
201 (
202 cp_role_name is null
203 AND menus.menu_name like '%'
204 )
205 OR
206 (
207 MENUS.MENU_NAME in (cp_role_name)
208 )
209 )
210 AND GRANTS.start_date <= sysdate
211 AND
212 (
213 GRANTS.end_date is null
214 OR grants.end_date >= SYSDATE
215 )
216 )
217 grants
218 WHERE grantee_type in ('USER','GROUP') --,'COMPANY','GLOBAL')
219 AND grantee_orig_system in ('HZ_PARTY','HZ_GROUP') --,'HZ_COMPANY', 'HZ_GLOBAL')
220 AND parties.party_id=DECODE(grants.grantee_key,'GLOBAL',-1000, SUBSTR(grants.grantee_key, INSTR(grants.grantee_key,':',1,1)+1));
221 l_grantee_list GRANTEES_TBL_TYPE;
222 l_inst_set_ids VARCHAR2(32767);
223 l_default_access VARCHAR2(50);
224 i NUMBER := 1;
225 query_to_exec VARCHAR2(32767);
226 cursor_select NUMBER;
227 cursor_execute NUMBER;
228 ret INTEGER;
229 l_object_id NUMBER;
230 BEGIN
231 x_return_status:='T';
232 l_entity_name:=p_entity_name;
233 l_pk1_value:=p_pk1_value;
234 l_pk2_value:=p_pk2_value;
235 l_pk3_value:='';
236 l_pk4_value:=p_pk4_value;
237 l_pk5_value:=p_pk5_value;
238 l_role_name:=p_role_name;
239 l_default_access:='Discoverer';
240 l_index:=0;
241
242 IF( p_entity_name ='MTL_SYSTEM_ITEMS' OR p_entity_name ='MTL_ITEM_REVISIONS') THEN
243 l_entity_name:='EGO_ITEM';
244 l_pk1_value:=p_pk2_value;
245 l_pk2_value:=p_pk1_value;
246 l_obj_ids := l_pk1_value || ',' || l_pk2_value;
247 END IF;
248
249 --OPEN get_object_id;
250 --FETCH get_object_id INTO l_object_id;
251 --CLOSE get_object_id;
252
253 FOR rec IN get_explicit_grant_users(
254 l_entity_name,
255 l_pk1_value,
256 l_pk2_value,
257 l_pk3_value,
258 l_pk4_value,
259 l_pk5_value,
260 p_role_name)
261 LOOP
262 l_grantee_list(l_index).grantee_name:=rec.grantee_name;
263 l_grantee_list(l_index).grantee_type:=rec.grantee_type;
264 l_grantee_list(l_index).role_name:=rec.role_name;
265 l_grantee_list(l_index).role_display_name:=rec.role_display_name;
266 l_temp_menu_id := rec.menu_id;
267 l_default_access:=Get_Default_Access(p_menu_id => l_temp_menu_id);
268 l_grantee_list(l_index).default_access:=l_default_access;
269
270 l_index:=l_index+1;
271 END LOOP;
272
273 x_grantee_names := FND_TABLE_OF_VARCHAR2_120();
274 x_grantee_types := FND_TABLE_OF_VARCHAR2_30();
275 x_role_names := FND_TABLE_OF_VARCHAR2_30();
276 x_role_display_names := FND_TABLE_OF_VARCHAR2_120();
277 x_default_access := FND_TABLE_OF_VARCHAR2_30();
278
279 IF( l_grantee_list.count>0) THEN
280 x_grantee_names.extend(l_grantee_list.count);
281 x_grantee_types.extend(l_grantee_list.count);
282 x_role_names.extend(l_grantee_list.count);
283 x_role_display_names.extend(l_grantee_list.count);
284 x_default_access.extend(l_grantee_list.count);
285
286 FOR i in l_grantee_list.first .. l_grantee_list.last LOOP
287 x_grantee_names(memcount+1):=l_grantee_list(i).grantee_name;
288 x_grantee_types(memcount+1):=l_grantee_list(i).grantee_type;
289 x_role_names(memcount+1):=l_grantee_list(i).role_name;
290 x_role_display_names(memcount+1):=l_grantee_list(i).role_display_name;
291 x_default_access(memcount+1):=l_grantee_list(i).default_access;
292 memcount := memcount + 1;
293 END LOOP;
294 END IF;
295
296 /*********************************************************************************************/
297
298 GET_VALID_INSTANCE_SET_IDS
299 (
300 p_obj_name => 'EGO_ITEM' ,
301 p_grantee_type => 'USER' ,
302 p_parent_obj_sql => null ,
303 p_bind1 => null ,
304 p_bind2 => null ,
305 p_bind3 => null ,
306 p_bind4 => null ,
307 p_bind5 => null ,
308 p_obj_ids => l_obj_ids ,
309 x_inst_set_ids => l_inst_set_ids
310 );
311
312 IF( length(l_inst_set_ids) > 0) THEN
313 l_role_name_temp := '''' || l_role_name || '''';
314
315 query_to_exec :=
316 'SELECT ' ||
317 'ltrim(grantee_person.party_name,''* '') grantee_name, ' ||
318 ' grantee_type grantee_type, ' ||
319 'granted_menu_data.menu_name role_name, ' ||
320 'granted_menu.user_menu_name role_display_name, ' ||
321 'granted_menu_data.menu_id menu_id, ' ||
322 'grantee_person.party_id party_id, ' ||
323 'grantee_person_company.party_id company_id, ' ||
324 'grantee_person_company.party_name company_name, ' ||
325 'trunc(grants.start_date) start_date , ' ||
326 'trunc(grants.end_date) end_date ' ||
327 'FROM fnd_grants grants, ' ||
328 'hz_parties grantee_person, ' ||
329 'hz_parties grantee_person_company, ' ||
330 'hz_relationships grantee_person_company_rel, ' ||
331 'fnd_menus_tl granted_menu, ' ||
332 'fnd_menus granted_menu_data, ' ||
333 'fnd_objects obj ' ||
334 'WHERE grants.object_id = obj.object_id ' ||
335 'AND grants.grantee_type = ''USER'' ' ||
336 'AND grantee_person.status = ''A'' ' ||
337 'AND grantee_person.party_type = ''PERSON'' ' ||
338 'AND grantee_person_company_rel.subject_type (+) = ''PERSON'' ' ||
339 'AND grantee_person_company_rel.subject_table_name (+) = ''HZ_PARTIES'' ' ||
340 'AND grantee_person_company_rel.object_table_name (+) = ''HZ_PARTIES'' ' ||
341 'AND grantee_person.party_id (+) = grantee_person_company_rel.subject_id ' ||
342 'AND grantee_person_company_rel.relationship_code(+) = ''EMPLOYEE_OF'' ' ||
343 'AND grantee_person_company_rel.status(+) = ''A'' ' ||
344 'AND grantee_person_company_rel.start_date(+) <= SYSDATE ' ||
345 'AND NVL(grantee_person_company_rel.end_date(+), SYSDATE+1) >= SYSDATE ' ||
346 'AND grantee_person_company.party_id (+) = grantee_person_company_rel.object_id ' ||
347 'AND grantee_person.party_id (+) = grantee_person_company_rel.subject_id ' ||
348 'AND grantee_person_company.status(+) = ''A'' ' ||
349 'AND grantee_person_company_rel.object_type(+) = ''ORGANIZATION'' ' ||
350 'AND grants.grantee_key like ''HZ_PARTY:%'' ' ||
351 'AND to_number(replace(grants.grantee_key,''HZ_PARTY:'','''')) = grantee_person.party_id ' ||
352 'AND substr(grants.grantee_key, 1, instr(grants.grantee_key, '':'')-1) =''HZ_PARTY'' ' ||
353 'AND nvl(grants.end_date, sysdate+1) >= trunc(sysdate) ' ||
354 'AND granted_menu.menu_id = grants.menu_id ' ||
355 'AND granted_menu.language= userenv(''LANG'') ' ||
356 'AND granted_menu.menu_id = granted_menu_data.menu_id ' ||
357 'AND ' ||
358 '( ' ||
359 '( ' ||
360 ':1 is null ' ||
361 'AND granted_menu_data.menu_name like ''%'' ' ||
362 ') ' ||
363 'OR ' ||
364 '( ' ||
365 'granted_menu_data.MENU_NAME in (:2) ' ||
366 ') ' ||
367 ') ' ||
368 'AND grants.instance_type=''SET'' ' ||
369 'AND ' ||
370 '( ' ||
371 'obj.obj_name =''EGO_ITEM'' ' ||
372 'AND ' ||
373 '( ' ||
374 ' grants.instance_set_id in ( :3 ) ' ||
375 ') ' ||
376 ') ' ;
377 END IF;
378 /*********************************************************************************************/
379
380 GET_VALID_INSTANCE_SET_IDS
381 (
382 p_obj_name => 'EGO_ITEM' ,
383 p_grantee_type => 'GROUP' ,
384 p_parent_obj_sql => null ,
385 p_bind1 => null ,
386 p_bind2 => null ,
387 p_bind3 => null ,
388 p_bind4 => null ,
389 p_bind5 => null ,
390 p_obj_ids => l_obj_ids ,
391 x_inst_set_ids => l_inst_set_ids
392 );
393 IF( length(l_inst_set_ids) > 0) THEN
394
395 query_to_exec := query_to_exec || 'UNION ' ||
396 'SELECT ' ||
397 'grantee_group.party_name grantee_name, ' ||
398 'grantee_type grantee_type, ' ||
399 'granted_menu_data.menu_name role_name, ' ||
400 'granted_menu.user_menu_name role_display_name, ' ||
401 'granted_menu_data.menu_id menu_id, ' ||
402 'grantee_group.party_id party_id, ' ||
403 '-1 company_id, ' ||
404 'null company_name, ' ||
405 'trunc(grants.start_date) start_date , ' ||
406 'trunc(grants.end_date) end_date ' ||
407 'FROM fnd_grants grants, ' ||
408 'hz_parties grantee_group, ' ||
409 'fnd_menus_tl granted_menu, ' ||
410 'fnd_menus granted_menu_data, ' ||
411 'fnd_objects obj ' ||
412 'WHERE grants.object_id = obj.object_id ' ||
413 'AND grants.grantee_type = ''GROUP'' ' ||
414 'AND grants.grantee_key like ''HZ_GROUP:%'' ' ||
415 'AND grantee_group.party_type = ''GROUP'' ' ||
416 'AND grantee_group.status = ''A'' ' ||
417 'AND ' ||
418 '( ' ||
419 'grantee_group.party_id = -1000 ' ||
420 'OR EXISTS ' ||
421 '( ' ||
422 'SELECT ' ||
423 ' ''X'' ' ||
424 'FROM fnd_grants f, ' ||
425 'fnd_menus m, ' ||
426 'fnd_objects o ' ||
427 'WHERE f.instance_pk1_value = to_char(grantee_group.party_id) ' ||
428 'AND f.start_date <= SYSDATE ' ||
429 'AND NVL(f.end_date, SYSDATE+1) >= SYSDATE ' ||
430 'AND f.menu_id = m.menu_id ' ||
431 'AND m.menu_name = ''EGO_MANAGE_GROUP'' ' ||
432 'AND f.object_id = o.object_id ' ||
433 'AND o.obj_name = ''EGO_GROUP'' ' ||
434 ') ' ||
435 ') ' ||
436 'AND to_number(replace(grants.grantee_key,''HZ_GROUP:'','''')) = grantee_group.party_id ' ||
437 'AND substr(grants.grantee_key, 1, instr(grants.grantee_key, '':'')-1) =''HZ_GROUP'' ' ||
438 'AND nvl(grants.end_date, sysdate+1) >= trunc(sysdate) ' ||
439 'AND granted_menu.menu_id = grants.menu_id ' ||
440 'AND granted_menu.language= userenv(''LANG'') ' ||
441 'AND granted_menu.menu_id = granted_menu_data.menu_id ' ||
442 'AND ' ||
443 '( ' ||
444 '( ' ||
445 ':4 is null ' ||
446 'AND granted_menu_data.menu_name like ''%'' ' ||
447 ') ' ||
448 'OR ' ||
449 '( ' ||
450 'granted_menu_data.MENU_NAME in ( :5 ) ' ||
451 ') ' ||
452 ') ' ||
453 'AND grants.instance_type=''SET'' ' ||
454 'AND ' ||
455 '( ' ||
456 'obj.obj_name =''EGO_ITEM'' ' ||
457 'AND ' ||
458 '( ' ||
459 ' grants.instance_set_id in ( :6 ) ' ||
460 ') ' ||
461 ') ' ;
462 END IF;
463 /*********************************************************************************************/
464 /*
465 GET_VALID_INSTANCE_SET_IDS
466 (
467 p_obj_name => 'EGO_ITEM' ,
468 p_grantee_type => 'COMPANY' ,
469 p_parent_obj_sql => null ,
470 p_bind1 => null ,
471 p_bind2 => null ,
472 p_bind3 => null ,
473 p_bind4 => null ,
474 p_bind5 => null ,
475 p_obj_ids => l_obj_ids ,
476 x_inst_set_ids => l_inst_set_ids
477 );
478 IF( length(l_inst_set_ids) > 0) THEN
479 query_to_exec := query_to_exec || 'UNION ' ||
480 'SELECT ' ||
481 'grantee_company.party_name grantee_name, ' ||
482 ' grantee_type grantee_type, ' ||
483 'granted_menu_data.menu_name role_name, ' ||
484 'granted_menu.user_menu_name role_display_name, ' ||
485 'grantee_company.party_id party_id, ' ||
486 'grantee_company.party_id company_id, ' ||
487 'grantee_company.party_name company_name, ' ||
488 'trunc(grants.start_date) start_date , ' ||
489 'trunc(grants.end_date) end_date ' ||
490 'FROM fnd_grants grants, ' ||
491 'hz_parties grantee_company, ' ||
492 'fnd_menus_tl granted_menu, ' ||
493 'fnd_menus granted_menu_data, ' ||
494 'fnd_objects obj ' ||
495 'WHERE grants.object_id = obj.object_id ' ||
496 'AND grants.grantee_type = ''COMPANY'' ' ||
497 'AND grants.grantee_key like ''HZ_COMPANY:%'' ' ||
498 'AND grantee_company.party_type = ''ORGANIZATION'' ' ||
499 'AND to_number(replace(grants.grantee_key,''HZ_COMPANY:'' , '''')) = grantee_company.party_id ' ||
500 'AND substr(grants.grantee_key, 1, instr(grants.grantee_key, '':'')-1) =''HZ_COMPANY'' ' ||
501 'AND nvl(grants.end_date, sysdate+1) >= trunc(sysdate) ' ||
502 'AND granted_menu.menu_id = grants.menu_id ' ||
503 'AND granted_menu.language= userenv(''LANG'') ' ||
504 'AND granted_menu.menu_id = granted_menu_data.menu_id ' ||
505 'AND grants.instance_type=''SET'' ' ||
506 'AND ' ||
507 '( ' ||
508 'obj.obj_name =''EGO_ITEM'' ' ||
509 'AND ' ||
510 '( ' ||
511 ' grants.instance_set_id in ( ' || l_inst_set_ids || ' ) ' ||
512 ') ' ||
513 ') ' ;
514 END IF;
515 */
516 /*********************************************************************************************/
517 /*
518 GET_VALID_INSTANCE_SET_IDS
519 (
520 p_obj_name => 'EGO_ITEM' ,
521 p_grantee_type => 'GLOBAL' ,
522 p_parent_obj_sql => null ,
523 p_bind1 => null ,
524 p_bind2 => null ,
525 p_bind3 => null ,
526 p_bind4 => null ,
527 p_bind5 => null ,
528 p_obj_ids => l_obj_ids ,
529 x_inst_set_ids => l_inst_set_ids
530 );
531 IF( length(l_inst_set_ids) > 0) THEN
532 query_to_exec := query_to_exec || 'UNION ' ||
533 'SELECT ' ||
534 'grantee_global.party_name grantee_name, ' ||
535 ' grantee_type grantee_type, ' ||
536 'granted_menu_data.menu_name role_name, ' ||
537 'granted_menu.user_menu_name role_display_name, ' ||
538 'grantee_global.party_id party_id, ' ||
539 '-1 company_id, ' ||
540 'null company_name, ' ||
541 'trunc(grants.start_date) start_date, ' ||
542 'trunc(grants.end_date) end_date ' ||
543 'FROM fnd_grants grants, ' ||
544 'hz_parties grantee_global, ' ||
545 'fnd_menus_tl granted_menu, ' ||
546 'fnd_menus granted_menu_data, ' ||
547 'fnd_objects obj ' ||
548 'WHERE grants.object_id = obj.object_id ' ||
549 'AND grants.grantee_type = ''GLOBAL'' ' ||
550 'AND grantee_global.party_id = -1000 ' ||
551 'AND nvl(grants.end_date, sysdate+1) >= trunc(sysdate) ' ||
552 'AND granted_menu.menu_id = grants.menu_id ' ||
553 'AND granted_menu.language= userenv(''LANG'') ' ||
554 'AND granted_menu.menu_id = granted_menu_data.menu_id ' ||
555 'AND grants.instance_type=''SET'' ' ||
556 'AND ' ||
557 '( ' ||
558 'obj.obj_name =''EGO_ITEM'' ' ||
559 'AND ' ||
560 '( ' ||
561 ' grants.instance_set_id in ( ' || l_inst_set_ids || ' ) ' ||
562 ') ' ||
563 ') ' ;
564 END IF;
565 */
566 /*********************************************************************************************/
567
568 q1 := ' UNION SELECT grantee_person.party_name grantee_name, ' ||
569 ' grantee_type grantee_type, ' ||
570 ' granted_menu.menu_name role_name, ' ||
571 ' granted_menu_tl.user_menu_name role_display_name, ' ||
572 ' granted_menu.menu_id menu_id, ' ||
573 ' grantee_person.PARTY_ID party_id , ' ||
574 ' null company_id , ' ||
575 ' '''' COMPANY_NAME , ' ||
576 ' trunc(grants.start_date) start_date , ' ||
577 ' trunc(grants.end_date) end_date ' ||
578 ' FROM fnd_grants grants, ' ||
579 ' fnd_menus granted_menu, ' ||
580 ' fnd_objects obj, ' ||
581 ' hz_parties grantee_person, ' ||
582 ' fnd_menus_tl granted_menu_tl ' ||
583 ' WHERE granted_menu.menu_id = grants.menu_id ' ||
584 ' AND grants.instance_type = ''SET'' AND grants.instance_pk1_value = ''*NULL*''' ||
585 ' AND grants.instance_set_id IN ( select instance_set.instance_set_id ' ||
586 ' from fnd_object_instance_sets instance_set, mtl_system_items_b item ' ||
587 ' where instance_set.object_id = grants.object_id and ' ||
588 ' (instance_set.instance_set_name = ''EGO_ORG_ITEM_'' || ' ||
589 ' to_char(item.organization_id) or instance_set.instance_set_name = ''EGO_ORG_CAT_ITEM_'' || ' ||
590 ' to_char(item.organization_id) || ''_'' || to_char(item.ITEM_CATALOG_GROUP_ID)) ' ||
591 ' and obj.obj_name = ''EGO_ITEM'' and item.inventory_item_id = ' || l_pk1_value || ' AND item.organization_id = ' || l_pk2_value ||
592 ' AND grants.grantee_type =''USER'' ' ||
593 ' AND grantee_person.party_type = ''PERSON'' ' ||
594 ' AND grantee_person.status = ''A'' ' ||
595 ' AND TO_NUMBER(REPLACE(grants.grantee_key,''HZ_PARTY:'','''')) = grantee_person.party_id ' ||
596 ' AND grantee_key like ''HZ_PARTY:%'' ' ||
597 ' AND (grants.start_date <= SYSDATE AND ( grants.end_date IS NULL OR SYSDATE <= grants.end_date )) ' ||
598 ' AND grants.object_id = obj.object_id ' ||
599 ' AND obj.obj_name = ''EGO_ITEM'') ' ||
600 ' AND granted_menu_tl.menu_id = grants.menu_id ' ||
601 'AND granted_menu_tl.language= userenv(''LANG'') ' ||
602 'AND ' ||
603 '( ' ||
604 '( ' ||
605 l_role_name_temp || ' is null ' ||
606 'AND granted_menu.menu_name like ''%'' ' ||
607 ') ' ||
608 'OR ' ||
609 '( ' ||
610 'granted_menu.MENU_NAME in ( ' || l_role_name_temp || ' ) ' ||
611 ') ' ||
612 ') ' ;
613
614
615 q2 := ' UNION SELECT grantee_group.party_name grantee_name, ' ||
616 ' grantee_type grantee_type, ' ||
617 ' granted_menu.menu_name role_name, ' ||
618 ' granted_menu_tl.user_menu_name role_display_name, ' ||
619 ' granted_menu.menu_id menu_id, ' ||
620 ' grantee_group.PARTY_ID party_id , ' ||
621 ' null company_id , ' ||
622 ' '''' COMPANY_NAME , ' ||
623 ' trunc(grants.start_date) start_date , ' ||
624 ' trunc(grants.end_date) end_date ' ||
625 ' FROM ' ||
626 ' fnd_grants grants , ' ||
627 ' fnd_menus granted_menu, ' ||
628 ' fnd_objects obj , ' ||
629 ' hz_parties member , ' ||
630 ' hz_relationships member_group , ' ||
631 ' hz_parties grantee_group, ' ||
632 ' fnd_menus_tl granted_menu_tl ' ||
633 ' WHERE grants.menu_id = granted_menu.menu_id ' ||
634 ' AND grants.instance_type= ''SET'' AND grants.instance_pk1_value = ''*NULL*'' AND ' ||
635 ' grants.instance_set_id IN ( select instance_set.instance_set_id from ' ||
636 ' fnd_object_instance_sets instance_set, mtl_system_items_b item ' ||
637 ' where instance_set.object_id = grants.object_id and (instance_set.instance_set_name = ''EGO_ORG_ITEM_'' || to_char(item.organization_id) ' ||
638 ' or instance_set.instance_set_name = ''EGO_ORG_CAT_ITEM_'' || to_char(item.organization_id) || ''_'' || to_char(item.ITEM_CATALOG_GROUP_ID)) ' ||
639 ' and obj.obj_name = ''EGO_ITEM'' AND item.inventory_item_id = ' || l_pk1_value || ' AND item.organization_id = ' || l_pk2_value ||
640 ' AND grants.grantee_type = ''GROUP'' ' ||
641 ' AND member_group.object_id = grantee_group.party_id ' ||
642 ' AND member_group.subject_id = member.party_id ' ||
643 ' AND member.party_type = ''PERSON'' ' ||
644 ' AND member.status = ''A'' ' ||
645 ' AND member_group.subject_type = ''PERSON'' ' ||
646 ' AND member_group.object_type = ''GROUP'' ' ||
647 ' AND member_group.relationship_type = ''MEMBERSHIP'' ' ||
648 ' AND member_group.status = ''A'' ' ||
649 ' AND member_group.start_date <= SYSDATE ' ||
650 ' AND (member_group.end_date IS NULL OR member_group.end_date >= SYSDATE) ' ||
651 ' AND TO_NUMBER(REPLACE(grants.grantee_key,''HZ_GROUP:'','''')) = grantee_group.party_id ' ||
652 ' AND grantee_key like ''HZ_GROUP:%'' ' ||
653 ' AND (grants.start_date <= SYSDATE AND (grants.end_date IS NULL OR SYSDATE <= grants.end_date)) ' ||
654 ' AND grants.object_id = obj.object_id ' ||
655 ' AND obj.obj_name = ''EGO_ITEM'') ' ||
656 ' AND granted_menu_tl.menu_id = grants.menu_id ' ||
657 'AND granted_menu_tl.language= userenv(''LANG'') ' ||
658 'AND ' ||
659 '( ' ||
660 '( ' ||
661 l_role_name_temp || ' is null ' ||
662 'AND granted_menu.menu_name like ''%'' ' ||
663 ') ' ||
664 'OR ' ||
665 '( ' ||
666 'granted_menu.MENU_NAME in ( ' || l_role_name_temp || ' ) ' ||
667 ') ' ||
668 ') ' ;
669
670 cursor_select := DBMS_SQL.OPEN_CURSOR;
671 DBMS_SQL.PARSE(cursor_select, query_to_exec || q1 || q2, DBMS_SQL.NATIVE);
672 l_index := 0;
673 dbms_sql.DEFINE_COLUMN(cursor_select, 1, '', 80);
674 dbms_sql.DEFINE_COLUMN(cursor_select, 2, '', 30);
675 dbms_sql.DEFINE_COLUMN(cursor_select, 3, '', 30);
676 dbms_sql.DEFINE_COLUMN(cursor_select, 4, '', 80);
677 dbms_sql.DEFINE_COLUMN(cursor_select, 5, l_temp_menu_id);
678 /*
679 dbms_sql.DEFINE_COLUMN(cursor_select, 7, '', 80);
680 dbms_sql.DEFINE_COLUMN(cursor_select, 8, '', 30);
681 dbms_sql.DEFINE_COLUMN(cursor_select, 9, '', 80);
682 dbms_sql.DEFINE_COLUMN(cursor_select, 10, '', 30);
683 */
684
685 DBMS_SQL.BIND_VARIABLE(cursor_select, ':1', l_role_name_temp);
686 DBMS_SQL.BIND_VARIABLE(cursor_select, ':2', l_role_name_temp);
687 DBMS_SQL.BIND_VARIABLE(cursor_select, ':3', l_inst_set_ids);
688
689 DBMS_SQL.BIND_VARIABLE(cursor_select, ':4', l_role_name_temp);
690 DBMS_SQL.BIND_VARIABLE(cursor_select, ':5', l_role_name_temp);
691 DBMS_SQL.BIND_VARIABLE(cursor_select, ':6', l_inst_set_ids);
692
693 /*
694 DBMS_SQL.BIND_VARIABLE(cursor_select, ':7', l_role_name_temp);
695 DBMS_SQL.BIND_VARIABLE(cursor_select, ':8', l_role_name_temp);
696 DBMS_SQL.BIND_VARIABLE(cursor_select, ':9', l_role_name_temp);
697 DBMS_SQL.BIND_VARIABLE(cursor_select, ':10', l_role_name_temp);
698 */
699
700 cursor_execute := DBMS_SQL.EXECUTE(cursor_select);
701
702 LOOP
703 IF dbms_sql.fetch_rows(cursor_select) > 0 THEN
704 DBMS_SQL.COLUMN_VALUE(cursor_select, 1, l_grantee_list(l_index).grantee_name);
705 DBMS_SQL.COLUMN_VALUE(cursor_select, 2, l_grantee_list(l_index).grantee_type);
706 DBMS_SQL.COLUMN_VALUE(cursor_select, 3, l_grantee_list(l_index).role_name);
707 DBMS_SQL.COLUMN_VALUE(cursor_select, 4, l_grantee_list(l_index).role_display_name);
708 DBMS_SQL.COLUMN_VALUE(cursor_select, 5, l_temp_menu_id);
709
710 l_default_access:=Get_Default_Access(p_menu_id => l_temp_menu_id);
711 l_grantee_list(l_index).default_access := l_default_access;
712
713 l_index:=l_index+1;
714 ELSE
715 EXIT;
716 END IF;
717 END LOOP;
718
719 IF( l_grantee_list.count>0) THEN
720 x_grantee_names.extend(l_grantee_list.count);
721 x_grantee_types.extend(l_grantee_list.count);
722 x_role_names.extend(l_grantee_list.count);
723 x_role_display_names.extend(l_grantee_list.count);
724 x_default_access.extend(l_grantee_list.count);
725
726 FOR i in l_grantee_list.first .. l_grantee_list.last LOOP
727 x_grantee_names(memcount+1):=l_grantee_list(i).grantee_name;
728 x_grantee_types(memcount+1):=l_grantee_list(i).grantee_type;
729 x_role_names(memcount+1):=l_grantee_list(i).role_name;
730 x_role_display_names(memcount+1):=l_grantee_list(i).role_display_name;
731 x_default_access(memcount+1):=l_grantee_list(i).default_access;
732 memcount := memcount + 1;
733 END LOOP;
734 END IF;
735
736 DBMS_SQL.CLOSE_CURSOR(cursor_select);
737
738 EXCEPTION
739 WHEN NO_DATA_FOUND
740 THEN
741 x_return_status:='F';
742
743 WHEN OTHERS
744 THEN
745 x_return_status:='F';
746 END Get_Users;
747
748 /*
749 PROCEDURE GET_VALID_INSTANCE_SET_IDS
750 (
751 p_grantee_type IN VARCHAR2,
752 x_inst_set_ids OUT NOCOPY VARCHAR2
753 )
754 IS
755 BEGIN
756
757 EGO_VALID_INSTANCE_SET_GRANTS.GET_VALID_INSTANCE_SETS
758 (
759 p_obj_name => 'EGO_ITEM' ,
760 p_grantee_type => p_grantee_type ,
761 p_parent_obj_sql => null ,
762 p_bind1 => null ,
763 p_bind2 => null ,
764 p_bind3 => null ,
765 p_bind4 => null ,
766 p_bind5 => null ,
767 p_obj_ids => '48819,204' ,
768 x_inst_set_ids => x_inst_set_ids
769 );
770 */
771
772 PROCEDURE GET_VALID_INSTANCE_SET_IDS
773 (
774 p_obj_name IN VARCHAR2,
775 p_grantee_type IN VARCHAR2,
776 p_parent_obj_sql IN VARCHAR2,
777 p_bind1 IN VARCHAR2,
778 p_bind2 IN VARCHAR2,
779 p_bind3 IN VARCHAR2,
780 p_bind4 IN VARCHAR2,
781 p_bind5 IN VARCHAR2,
782 p_obj_ids IN VARCHAR2,
783 x_inst_set_ids OUT NOCOPY VARCHAR2
784 )
785 IS
786 CURSOR inst_set_preds IS
787 SELECT DISTINCT
788 sets.instance_set_id instance_set_id ,
789 sets.instance_set_name instance_set_name,
790 sets.predicate predicate
791 FROM fnd_grants grants,
792 fnd_object_instance_sets sets,
793 fnd_objects obj
794 WHERE obj.obj_name = p_obj_name
795 AND grants.object_id = obj.object_id
796 AND grants.instance_type='SET'
797 AND grants.parameter1 is null
798 AND nvl(grants.end_date, sysdate+1) >= trunc(sysdate)
799 AND grants.grantee_type = p_grantee_type
800 AND sets.instance_set_id = grants.instance_set_id
801 ORDER BY instance_set_name;
802
803 CURSOR obj_meta_data IS
804 SELECT
805 DATABASE_OBJECT_NAME,
806 PK1_COLUMN_NAME,
807 PK2_COLUMN_NAME,
808 PK3_COLUMN_NAME,
809 PK4_COLUMN_NAME,
810 PK5_COLUMN_NAME
811 FROM fnd_objects
812 WHERE OBJ_NAME = p_obj_name;
813
814 obj_meta_data_rec obj_meta_data%ROWTYPE;
815 i NUMBER := 1;
816 query_to_exec VARCHAR2(32767);
817 obj_std_pkq VARCHAR2(32767);
818 prim_key_str VARCHAR2(32767);
819 inst_set_ids VARCHAR2(32767);
820 cursor_select NUMBER;
821 cursor_execute NUMBER;
822 BEGIN
823 OPEN obj_meta_data;
824 FETCH obj_meta_data INTO obj_meta_data_rec;
825 obj_std_pkq := 'SELECT ' || obj_meta_data_rec.PK1_COLUMN_NAME;
826 prim_key_str := obj_meta_data_rec.PK1_COLUMN_NAME;
827 IF obj_meta_data_rec.PK2_COLUMN_NAME IS NOT NULL THEN
828 obj_std_pkq := obj_std_pkq || ' , ' || obj_meta_data_rec.PK2_COLUMN_NAME;
829 prim_key_str := prim_key_str || ' , ' || obj_meta_data_rec.PK2_COLUMN_NAME;
830 END IF;
831 IF obj_meta_data_rec.PK3_COLUMN_NAME IS NOT NULL THEN
832 obj_std_pkq := obj_std_pkq || ' , ' || obj_meta_data_rec.PK3_COLUMN_NAME;
833 prim_key_str := prim_key_str || ' , ' || obj_meta_data_rec.PK3_COLUMN_NAME;
834 END IF;
835 IF obj_meta_data_rec.PK4_COLUMN_NAME IS NOT NULL THEN
836 obj_std_pkq := obj_std_pkq || ' , ' || obj_meta_data_rec.PK4_COLUMN_NAME;
837 prim_key_str := prim_key_str || ' , ' || obj_meta_data_rec.PK4_COLUMN_NAME;
838 END IF;
839 IF obj_meta_data_rec.PK5_COLUMN_NAME IS NOT NULL THEN
840 obj_std_pkq := obj_std_pkq || ' , ' || obj_meta_data_rec.PK5_COLUMN_NAME;
841 prim_key_str := prim_key_str || ' , ' || obj_meta_data_rec.PK5_COLUMN_NAME;
842 END IF;
843 obj_std_pkq := obj_std_pkq || ' FROM ' || obj_meta_data_rec.DATABASE_OBJECT_NAME;
844 CLOSE obj_meta_data;
845
846 FOR inst_set_preds_rec IN inst_set_preds
847 LOOP
848 IF p_obj_ids IS NOT NULL THEN
849 query_to_exec := 'SELECT 1 from dual WHERE (' || p_obj_ids || ') IN (' || obj_std_pkq;
850 query_to_exec := query_to_exec || ' WHERE ' || inst_set_preds_rec.predicate || ' )';
851 ELSIF p_parent_obj_sql IS NOT NULL THEN
852 query_to_exec := 'SELECT 1 from dual WHERE EXISTS( ' || obj_std_pkq || ' WHERE ';
853 query_to_exec := query_to_exec || inst_set_preds_rec.predicate || ' AND (';
854 query_to_exec := query_to_exec || prim_key_str || ') IN (' || p_parent_obj_sql || '))';
855 END IF;
856 cursor_select := DBMS_SQL.OPEN_CURSOR;
857 DBMS_SQL.PARSE(cursor_select, query_to_exec, DBMS_SQL.NATIVE);
858 IF p_bind1 IS NOT NULL THEN
859 DBMS_SQL.BIND_VARIABLE(cursor_select, ':id1', p_bind1);
860 END IF;
861 IF p_bind2 IS NOT NULL THEN
862 DBMS_SQL.BIND_VARIABLE(cursor_select, ':id2', p_bind2);
863 END IF;
864 IF p_bind3 IS NOT NULL THEN
865 DBMS_SQL.BIND_VARIABLE(cursor_select, ':id3', p_bind3);
866 END IF;
867 IF p_bind4 IS NOT NULL THEN
868 DBMS_SQL.BIND_VARIABLE(cursor_select, ':id4', p_bind4);
869 END IF;
870 IF p_bind5 IS NOT NULL THEN
871 DBMS_SQL.BIND_VARIABLE(cursor_select, ':id5', p_bind5);
872 END IF;
873 cursor_execute := DBMS_SQL.EXECUTE(cursor_select);
874 IF DBMS_SQL.FETCH_ROWS(cursor_select) > 0 THEN
875 IF i = 1 THEN
876 inst_set_ids := to_char(inst_set_preds_rec.instance_set_id);
877 i := 2;
878 ELSE
879 inst_set_ids := inst_set_ids || ',' || inst_set_preds_rec.instance_set_id;
880 END IF;
881 END IF;
882 DBMS_SQL.CLOSE_CURSOR(cursor_select);
883 END LOOP;
884 IF inst_set_ids IS NOT NULL THEN
885 x_inst_set_ids := inst_set_ids; /**** list of valid inst_set_ids ****/
886 ELSE
887 x_inst_set_ids := '-1';
888 END IF;
889 END GET_VALID_INSTANCE_SET_IDS;
890
891
892 FUNCTION Get_Default_Access(p_menu_id IN NUMBER)
893 RETURN VARCHAR2
894 IS
895 l_priv_count NUMBER;
896 BEGIN
897 l_priv_count := -1;
898 -- Get the default access for the folder.
899 -- If the role has 'Add Item People' privilege then it is Administrator,
900 -- If the role has 'Add Item Document' privilege then it is Author,
901 -- If the role has ' View Item Document ' privilege then it is Reader ,
902 -- otherwise give 'Discover' default role on folder.
903
904 SELECT COUNT(function_name)
905 INTO l_priv_count
906 FROM fnd_form_functions
907 WHERE function_name='EGO_ADD_ITEM_PEOPLE' AND
908 function_id IN (SELECT function_id FROM fnd_menu_entries WHERE menu_id = p_menu_id);
909
910 IF(l_priv_count > 0) THEN
911 RETURN 'Administrator';
912 END IF;
913
914 SELECT COUNT(function_name)
915 INTO l_priv_count
916 FROM fnd_form_functions
917 WHERE function_name='EGO_ADD_ITEM_DOCUMENT' AND
918 function_id IN (SELECT function_id FROM fnd_menu_entries WHERE menu_id = p_menu_id);
919
920 IF(l_priv_count > 0) THEN
921 RETURN 'Author';
922 END IF;
923
924 SELECT COUNT(function_name)
925 INTO l_priv_count
926 FROM fnd_form_functions
927 WHERE function_name='EGO_VIEW_ITEM_DOCUMENT_LIST' AND
928 function_id IN (SELECT function_id FROM fnd_menu_entries WHERE menu_id = p_menu_id);
929
930 IF(l_priv_count > 0) THEN
931 RETURN 'Reader';
932 END IF;
933
934 RETURN 'Discoverer';
935 END Get_Default_Access;
936
937 FUNCTION GET_ATTACHMENT_PRIVILAGES
938 (
939 p_entity_name IN VARCHAR2,
940 p_pk1_value IN VARCHAR2,
941 p_pk2_value IN VARCHAR2,
942 p_pk3_value IN VARCHAR2,
943 p_pk4_value IN VARCHAR2,
944 p_pk5_value IN VARCHAR2,
945 p_user_name IN VARCHAR2,
946 p_attachment_id IN NUMBER DEFAULT NULL
947 ) RETURN VARCHAR2
948 IS
949 l_policy_value VARCHAR2(30);
950 l_item_catalog_group_id VARCHAR2(30);
951 l_lifecycle_id VARCHAR2(30);
952 l_current_phase_id VARCHAR2(30);
953 l_viewPriv VARCHAR2(30) DEFAULT NULL;
954 l_editPriv VARCHAR2(30) DEFAULT NULL;
955 l_result VARCHAR2(30);
956 l_party_id VARCHAR2(30);
957 l_category_id VARCHAR2(30);
958 BEGIN
959 IF FND_GLOBAL.User_Id = -1 THEN
960 SELECT PARTY_ID INTO l_party_id FROM EGO_USER_V WHERE user_name = FND_GLOBAL.USER_NAME;
961 ELSE
962 SELECT PARTY_ID INTO l_party_id FROM EGO_USER_V WHERE user_id = TO_CHAR(FND_GLOBAL.User_Id);
963 END IF;
964
965 l_viewPriv := EGO_DATA_SECURITY.CHECK_FUNCTION (
966 1.0,
967 'EGO_VIEW_ITEM_DOCUMENT_LIST',
968 'EGO_ITEM',
969 p_pk2_value,
970 p_pk1_value,
971 NULL,NULL,NULL,
972 'HZ_PARTY:'||l_party_id);
973 l_editPriv := EGO_DATA_SECURITY.CHECK_FUNCTION (
974 1.0,
975 'EGO_ADD_ITEM_DOCUMENT',
976 'EGO_ITEM',
977 p_pk2_value,
978 p_pk1_value,
979 NULL,NULL,NULL,
980 'HZ_PARTY:'||l_party_id);
981
982
983 SELECT
984 Nvl(fad.category_id,fd.category_id)
985 INTO
986 l_category_id
987 FROM
988 fnd_attached_documents fad,fnd_documents fd
989 WHERE
990 (p_attachment_id IS NULL OR fad.attached_Document_id = p_attachment_id)
991 AND (fad.entity_name = p_entity_name)
992 AND (fad.pk1_value = p_pk1_value)
993 AND (p_pk2_value IS NULL OR fad.pk2_value = p_pk2_value)
994 AND (p_pk3_value IS NULL OR fad.pk3_value = p_pk3_value)
995 AND (p_pk4_value IS NULL OR fad.pk4_value = p_pk4_value)
996 AND (p_pk5_value IS NULL OR fad.pk5_value = p_pk5_value)
997 AND fd.document_id = fad.document_id;
998
999 IF (l_editPriv = 'T') THEN
1000 l_result := 'Update';
1001 SELECT
1002 item_catalog_group_id,
1003 lifecycle_id,
1004 current_phase_id
1005 INTO
1006 l_item_catalog_group_id,
1007 l_lifecycle_id,
1008 l_lifecycle_id
1009 FROM
1010 mtl_system_items_b
1011 WHERE
1012 inventory_item_id = p_pk2_value AND
1013 organization_id = p_pk1_value;
1014 ENG_CHANGE_POLICY_PKG.GetChangePolicy
1015 ( 'CATALOG_LIFECYCLE_PHASE'
1016 , 'CHANGE_POLICY'
1017 , l_item_catalog_group_id
1018 , l_lifecycle_id
1019 , l_lifecycle_id
1020 , NULL
1021 , NULL
1022 , 'EGO_CATALOG_GROUP'
1023 , 'ATTACHMENT'
1024 , l_category_id
1025 , l_policy_value
1026 );
1027 IF (l_policy_value = 'ALLOWED')
1028 THEN RETURN('Update');
1029 END IF; --l_policy_value = 'ALLOWED'
1030 END IF; --l_editPriv = 'T'
1031 IF (l_viewPriv = 'T') THEN
1032 RETURN 'View' ;
1033 ELSE
1034 RETURN NULL;
1035 END IF;
1036 EXCEPTION
1037 WHEN OTHERS then
1038 IF (l_viewPriv = 'T') THEN
1039 RETURN 'View' ;
1040 ELSE
1041 RETURN NULL;
1042 END IF;
1043 END GET_ATTACHMENT_PRIVILAGES;
1044
1045 END EGO_DOM_SECURITY_PVT;