Subversion Repositories DevTools

/* Role Privileges */
SELECT roc.OBJ_ID,
           roc.OBJ_NAME,
           roc.OBJ_DESCRIPTION,
           perm_active.PERM_ID AS perm_active_id,
           perm_active.PERM_VALUE AS active_value,
           perm_visible.PERM_ID AS perm_visible_id,
           perm_visible.PERM_VALUE AS visible_value,
           DECODE ( data_filter.obj_id,
                                NULL, 'LIMG_DATA_TABLE_OFF',
                                'LIMG_DATA_TABLE' ) AS data_filter
  FROM 
           (
           
            SELECT DISTINCT
                           co.OBJ_ID,
                           co.OBJ_NAME,
                           co.OBJ_DESCRIPTION
                  FROM ROLE_PRIVILEGES rp,
                           CONTROL_OBJECTS co
                 WHERE rp.OBJ_ID (+)= co.OBJ_ID 
                   AND rp.ROLE_ID (+)= :ROLE_ID
                   AND co.APP_ID = :APP_ID

                 UNION  
                 
                /* Select data filters even if they are not set in role_privileges */ 
                SELECT DISTINCT co.OBJ_ID,
                           co.OBJ_NAME,
                           co.OBJ_DESCRIPTION
                  FROM DATA_PERMISSIONS dp,
                           DATA_TABLES dt,
                           CONTROL_OBJECTS co
                 WHERE dp.DT_ID = dt.DT_ID
                   AND dt.obj_id = co.obj_id
                   AND dp.ROLE_ID = :ROLE_ID 
                   AND co.APP_ID = :APP_ID
                   
                   
           ) roc,
           (
           
                SELECT co.OBJ_ID,
                           rp.PERM_ID,
                           rp.PERM_VALUE
                  FROM ROLE_PRIVILEGES rp,
                           CONTROL_OBJECTS co
             WHERE rp.OBJ_ID = co.obj_id
                   AND rp.ROLE_ID = :ROLE_ID
                   AND rp.PERM_ID = 1 
                   AND co.APP_ID = :APP_ID
                  
           ) perm_visible,
           (
           
                SELECT co.OBJ_ID,
                           rp.PERM_ID,
                           rp.PERM_VALUE
                  FROM ROLE_PRIVILEGES rp,
                           CONTROL_OBJECTS co
             WHERE rp.OBJ_ID = co.obj_id
                   AND rp.ROLE_ID = :ROLE_ID
                   AND rp.PERM_ID = 2 
                   AND co.APP_ID = :APP_ID
                  
           ) perm_active,
           (
           
            /* Get Data Filters for this role */
                SELECT DISTINCT 
                           co.OBJ_ID
                  FROM DATA_TABLES dt,
                           DATA_PERMISSIONS dp,
                           CONTROL_OBJECTS co
             WHERE dp.DT_ID = dt.DT_ID
                   AND dt.OBJ_ID = co.OBJ_ID
                   AND co.APP_ID = :APP_ID
                   AND dp.ROLE_ID = :ROLE_ID
                   AND dp.REF_COLUMN_VAL != 0
           
           ) data_filter
           
 WHERE perm_active.obj_id (+)= roc.obj_id
   AND perm_visible.obj_id (+)= roc.obj_id
   AND data_filter.obj_id (+) = roc.obj_id
   
ORDER BY UPPER(roc.OBJ_NAME)