Subversion Repositories DevTools

/* 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)