/* User Permissions */
SELECT co.OBJ_NAME,
	   up.*
 FROM CONTROL_OBJECTS co,
		(
		
		SELECT DISTINCT
			   usrro.ROLE_ID,
			   usrro.ROLE_NAME,
			   usrro.IS_ROLE_VARIATION,
			   DECODE ( perm_active.OBJ_ID, 
			   		  	NULL, perm_visible.OBJ_ID,
						perm_active.OBJ_ID ) AS OBJ_ID,
--				perm_active.OBJ_ID,
--				perm_visible.OBJ_ID,						   
			   perm_visible.PERM_ID AS perm_visible_id,
			   perm_visible.PERM_VALUE AS visible_value,
			   perm_active.PERM_ID AS perm_active_id,
			   perm_active.PERM_VALUE AS active_value
		  FROM 
			   (
				/* Get All User Roles */
			    SELECT ro.ROLE_ID,
					   ro.ROLE_NAME,
					   ro.IS_ROLE_VARIATION
				  FROM ROLES ro,
				  	   USER_ROLES ur
				 WHERE ur.ROLE_ID = ro.ROLE_ID 
				   AND ro.APP_ID = :APP_ID
				   AND ur.USER_ID = :USER_ID 
			   ) usrro,
			   (
			    /* Get "Visible" Settings */
			   	SELECT rco.ROLE_ID,
					   rco.OBJ_ID,
					   rp.PERM_ID,
					   rp.PERM_VALUE
				  FROM ROLE_PRIVILEGES rp,
					   (
					    /* Get Role Controls for This Application */
					    SELECT DISTINCT co.OBJ_ID, ro.ROLE_ID
						  FROM ROLES ro,
						       ROLE_PRIVILEGES rp,
							   CONTROL_OBJECTS co
					     WHERE rp.ROLE_ID = ro.ROLE_ID
						   AND rp.OBJ_ID = co.OBJ_ID
						   AND ro.APP_ID = :APP_ID
					   ) rco
				 WHERE rp.ROLE_ID (+)= rco.ROLE_ID
				   AND rp.OBJ_ID (+) = rco.OBJ_ID
				   AND rp.PERM_ID (+)= 1
			   ) perm_visible,
			   (
			    /* Get "Active" Settings */
			    SELECT rco.ROLE_ID,
					   rco.OBJ_ID,
					   rp.PERM_ID,
					   rp.PERM_VALUE
				  FROM ROLE_PRIVILEGES rp,
					   (
					    /* Get Role Controls for This Application */
					    SELECT DISTINCT co.OBJ_ID, ro.ROLE_ID
						  FROM ROLES ro,
						       ROLE_PRIVILEGES rp,
							   CONTROL_OBJECTS co
					     WHERE rp.ROLE_ID = ro.ROLE_ID
						   AND rp.OBJ_ID = co.OBJ_ID
						   AND ro.APP_ID = :APP_ID
					   ) rco
				 WHERE rp.ROLE_ID (+)= rco.ROLE_ID
				   AND rp.OBJ_ID (+) = rco.OBJ_ID
				   AND rp.PERM_ID (+)= 2
			   ) perm_active
		 WHERE perm_active.ROLE_ID (+) = usrro.ROLE_ID 
		   AND perm_visible.ROLE_ID (+)= usrro.ROLE_ID 
		   AND perm_active.OBJ_ID = perm_visible.OBJ_ID 
		   
		) up
 WHERE up.OBJ_ID = co.OBJ_ID		   
   
ORDER BY UPPER(co.OBJ_NAME), up.IS_ROLE_VARIATION, UPPER(up.ROLE_NAME)