Subversion Repositories DevTools

/* Get a list of all user rols with flags to indicate if they can be delegated or if they have been granted */
SELECT role_id,
  role_name,
  comments,
  can_delegate,
  granted
FROM
  (SELECT ro.*,
    (SELECT COUNT(*)
    FROM ROLES ra,
      USER_ROLES ur
    WHERE ra.APP_ID = :APP_ID
    AND ur.USER_ID  = :USER_ID_USER
    AND ur.ROLE_ID  = ra.ROLE_ID
    AND ra.role_id  = ro.role_id
    ) AS can_Delegate,
    (SELECT COUNT(*)
    FROM ROLES ra,
      USER_ROLES ur
    WHERE ra.APP_ID = :APP_ID
    AND ur.USER_ID  = :USER_ID
    AND ur.ROLE_ID  = ra.ROLE_ID
    AND ra.role_id  = ro.role_id
    ) AS granted
  FROM ROLES ro
  WHERE ro.APP_ID = :APP_ID
  )
WHERE IS_ROLE_VARIATION IS NULL
AND app_id               = :APP_ID
ORDER BY UPPER(role_name)