Subversion Repositories DevTools

Rev

Details | Last modification | View Log | RSS feed

Rev Author Line No. Line
2 rsolanki 1 
/* Role Privileges */
2 
SELECT roc.OBJ_ID,
3 
	   roc.OBJ_NAME,
4 
	   roc.OBJ_DESCRIPTION,
5 
	   perm_active.PERM_ID AS perm_active_id,
6 
	   perm_active.PERM_VALUE AS active_value,
7 
	   perm_visible.PERM_ID AS perm_visible_id,
8 
	   perm_visible.PERM_VALUE AS visible_value,
9 
	   DECODE ( data_filter.obj_id,
10 
	   		  	NULL, 'LIMG_DATA_TABLE_OFF',
11 
				'LIMG_DATA_TABLE' ) AS data_filter
12 
  FROM
13 
	   (
14 
 
15 
	    SELECT DISTINCT
16 
			   co.OBJ_ID,
17 
			   co.OBJ_NAME,
18 
			   co.OBJ_DESCRIPTION
19 
		  FROM ROLE_PRIVILEGES rp,
20 
		  	   CONTROL_OBJECTS co
21 
		 WHERE rp.OBJ_ID (+)= co.OBJ_ID
22 
		   AND rp.ROLE_ID (+)= :ROLE_ID
23 
		   AND co.APP_ID = :APP_ID
24 
 
25 
		 UNION
26 
 
27 
		/* Select data filters even if they are not set in role_privileges */
28 
		SELECT DISTINCT co.OBJ_ID,
29 
			   co.OBJ_NAME,
30 
			   co.OBJ_DESCRIPTION
31 
		  FROM DATA_PERMISSIONS dp,
32 
		  	   DATA_TABLES dt,
33 
		  	   CONTROL_OBJECTS co
34 
		 WHERE dp.DT_ID = dt.DT_ID
35 
		   AND dt.obj_id = co.obj_id
36 
		   AND dp.ROLE_ID = :ROLE_ID
37 
		   AND co.APP_ID = :APP_ID
38 
 
39 
 
40 
	   ) roc,
41 
	   (
42 
 
43 
	   	SELECT co.OBJ_ID,
44 
			   rp.PERM_ID,
45 
			   rp.PERM_VALUE
46 
		  FROM ROLE_PRIVILEGES rp,
47 
		  	   CONTROL_OBJECTS co
48 
	     WHERE rp.OBJ_ID = co.obj_id
49 
		   AND rp.ROLE_ID = :ROLE_ID
50 
		   AND rp.PERM_ID = 1
51 
		   AND co.APP_ID = :APP_ID
52 
 
53 
	   ) perm_visible,
54 
	   (
55 
 
56 
	   	SELECT co.OBJ_ID,
57 
			   rp.PERM_ID,
58 
			   rp.PERM_VALUE
59 
		  FROM ROLE_PRIVILEGES rp,
60 
		  	   CONTROL_OBJECTS co
61 
	     WHERE rp.OBJ_ID = co.obj_id
62 
		   AND rp.ROLE_ID = :ROLE_ID
63 
		   AND rp.PERM_ID = 2
64 
		   AND co.APP_ID = :APP_ID
65 
 
66 
	   ) perm_active,
67 
	   (
68 
 
69 
	    /* Get Data Filters for this role */
70 
		SELECT DISTINCT
71 
			   co.OBJ_ID
72 
		  FROM DATA_TABLES dt,
73 
		  	   DATA_PERMISSIONS dp,
74 
			   CONTROL_OBJECTS co
75 
	     WHERE dp.DT_ID = dt.DT_ID
76 
		   AND dt.OBJ_ID = co.OBJ_ID
77 
		   AND co.APP_ID = :APP_ID
78 
		   AND dp.ROLE_ID = :ROLE_ID
79 
		   AND dp.REF_COLUMN_VAL != 0
80 
 
81 
	   ) data_filter
82 
 
83 
 WHERE perm_active.obj_id (+)= roc.obj_id
84 
   AND perm_visible.obj_id (+)= roc.obj_id
85 
   AND data_filter.obj_id (+) = roc.obj_id
86 
 
87 
ORDER BY UPPER(roc.OBJ_NAME)