Subversion Repositories DevTools

Rev

Details | Last modification | View Log | RSS feed

Rev Author Line No. Line
2 rsolanki 1 
/* User Permissions */
2 
SELECT co.OBJ_NAME,
3 
	   up.*
4 
 FROM CONTROL_OBJECTS co,
5 
		(
6 
 
7 
		SELECT DISTINCT
8 
			   usrro.ROLE_ID,
9 
			   usrro.ROLE_NAME,
10 
			   usrro.IS_ROLE_VARIATION,
11 
			   DECODE ( perm_active.OBJ_ID,
12 
			   		  	NULL, perm_visible.OBJ_ID,
13 
						perm_active.OBJ_ID ) AS OBJ_ID,
14 
--				perm_active.OBJ_ID,
15 
--				perm_visible.OBJ_ID,
16 
			   perm_visible.PERM_ID AS perm_visible_id,
17 
			   perm_visible.PERM_VALUE AS visible_value,
18 
			   perm_active.PERM_ID AS perm_active_id,
19 
			   perm_active.PERM_VALUE AS active_value
20 
		  FROM
21 
			   (
22 
				/* Get All User Roles */
23 
			    SELECT ro.ROLE_ID,
24 
					   ro.ROLE_NAME,
25 
					   ro.IS_ROLE_VARIATION
26 
				  FROM ROLES ro,
27 
				  	   USER_ROLES ur
28 
				 WHERE ur.ROLE_ID = ro.ROLE_ID
29 
				   AND ro.APP_ID = :APP_ID
30 
				   AND ur.USER_ID = :USER_ID
31 
			   ) usrro,
32 
			   (
33 
			    /* Get "Visible" Settings */
34 
			   	SELECT rco.ROLE_ID,
35 
					   rco.OBJ_ID,
36 
					   rp.PERM_ID,
37 
					   rp.PERM_VALUE
38 
				  FROM ROLE_PRIVILEGES rp,
39 
					   (
40 
					    /* Get Role Controls for This Application */
41 
					    SELECT DISTINCT co.OBJ_ID, ro.ROLE_ID
42 
						  FROM ROLES ro,
43 
						       ROLE_PRIVILEGES rp,
44 
							   CONTROL_OBJECTS co
45 
					     WHERE rp.ROLE_ID = ro.ROLE_ID
46 
						   AND rp.OBJ_ID = co.OBJ_ID
47 
						   AND ro.APP_ID = :APP_ID
48 
					   ) rco
49 
				 WHERE rp.ROLE_ID (+)= rco.ROLE_ID
50 
				   AND rp.OBJ_ID (+) = rco.OBJ_ID
51 
				   AND rp.PERM_ID (+)= 1
52 
			   ) perm_visible,
53 
			   (
54 
			    /* Get "Active" Settings */
55 
			    SELECT rco.ROLE_ID,
56 
					   rco.OBJ_ID,
57 
					   rp.PERM_ID,
58 
					   rp.PERM_VALUE
59 
				  FROM ROLE_PRIVILEGES rp,
60 
					   (
61 
					    /* Get Role Controls for This Application */
62 
					    SELECT DISTINCT co.OBJ_ID, ro.ROLE_ID
63 
						  FROM ROLES ro,
64 
						       ROLE_PRIVILEGES rp,
65 
							   CONTROL_OBJECTS co
66 
					     WHERE rp.ROLE_ID = ro.ROLE_ID
67 
						   AND rp.OBJ_ID = co.OBJ_ID
68 
						   AND ro.APP_ID = :APP_ID
69 
					   ) rco
70 
				 WHERE rp.ROLE_ID (+)= rco.ROLE_ID
71 
				   AND rp.OBJ_ID (+) = rco.OBJ_ID
72 
				   AND rp.PERM_ID (+)= 2
73 
			   ) perm_active
74 
		 WHERE perm_active.ROLE_ID (+) = usrro.ROLE_ID
75 
		   AND perm_visible.ROLE_ID (+)= usrro.ROLE_ID
76 
		   AND perm_active.OBJ_ID = perm_visible.OBJ_ID
77 
 
78 
		) up
79 
 WHERE up.OBJ_ID = co.OBJ_ID
80 
 
81 
ORDER BY UPPER(co.OBJ_NAME), up.IS_ROLE_VARIATION, UPPER(up.ROLE_NAME)