/* Control Privileges */
SELECT roc.ROLE_ID,
	   roc.ROLE_NAME,
	   :OBJ_ID AS 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 Roles for this Application */
	    SELECT ro.ROLE_ID, 
			   ro.ROLE_NAME
		  FROM ROLES ro
		 WHERE ro.APP_ID = :APP_ID
		   AND ro.IS_ROLE_VARIATION IS NULL
		   
		   
	   ) roc,
	   (
	   
	    /* Get "Visible" Settings */
	   	SELECT rp.ROLE_ID,
			   co.OBJ_ID,
			   rp.PERM_ID,
			   rp.PERM_VALUE
		  FROM ROLE_PRIVILEGES rp,
		  	   CONTROL_OBJECTS co
	     WHERE rp.OBJ_ID = co.obj_id
		   AND co.OBJ_ID = :OBJ_ID
		   AND rp.PERM_ID = 1 
		   AND co.APP_ID = :APP_ID
		  
	   ) perm_visible,
	   (
	   
	    /* Get "Active" Settings */
	   	SELECT rp.ROLE_ID,
			   co.OBJ_ID,
			   rp.PERM_ID,
			   rp.PERM_VALUE
		  FROM ROLE_PRIVILEGES rp,
		  	   CONTROL_OBJECTS co
	     WHERE rp.OBJ_ID = co.obj_id
		   AND co.OBJ_ID = :OBJ_ID
		   AND rp.PERM_ID = 2 
		   AND co.APP_ID = :APP_ID
		  
	   ) perm_active
	   
 WHERE perm_active.role_id (+)= roc.role_id
   AND perm_visible.role_id (+)= roc.role_id
   
   
ORDER BY UPPER(roc.ROLE_NAME)