Subversion Repositories DevTools

Rev

Rev 5512 | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 5512 Rev 6265
Line 1... Line -...
1 
/* User Role List */
 - 
 
2 
SELECT ro.role_id,
 - 
 
3 
	   ro.ROLE_NAME,
 - 
 
4 
	   ro.COMMENTS
 - 
 
5 
  FROM (
 - 
 
6
- 
 
7 
		SELECT ro.*
 - 
 
8 
		  FROM ROLES ro
 - 
 
9 
		 WHERE ro.APP_ID = :APP_ID
 - 
 
10
- 
 
11 
		MINUS
 - 
 
12
- 
 
13 
		SELECT ro.*
 - 
 
14 
		  FROM ROLES ro,
 - 
 
15 
		       USER_ROLES ur
 - 
 
16 
		 WHERE ro.APP_ID = :APP_ID
 - 
 
17 
		   AND ur.USER_ID = :USER_ID
 - 
 
18 
		   AND ur.ROLE_ID = ro.ROLE_ID
 - 
 
19
- 
 
20 
  		) ro
 - 
 
21 
 WHERE ro.IS_ROLE_VARIATION IS NULL
 - 
 
22 
   AND ro.app_id = :APP_ID
 - 
 
23 
ORDER BY UPPER(ro.role_name)
 - 
 
24
- 
 
25 

            1
            
/* Get a list of all user rols with flags to indicate if they can be delegated or if they have been granted */

          

          

            -
            
 

            2
            
SELECT role_id,

          

          

            -
            
 

            3
            
  role_name,

          

          

            -
            
 

            4
            
  comments,

          

          

            -
            
 

            5
            
  can_delegate,

          

          

            -
            
 

            6
            
  granted

          

          

            -
            
 

            7
            
FROM

          

          

            -
            
 

            8
            
  (SELECT ro.*,

          

          

            -
            
 

            9
            
    (SELECT COUNT(*)

          

          

            -
            
 

            10
            
    FROM ROLES ra,

          

          

            -
            
 

            11
            
      USER_ROLES ur

          

          

            -
            
 

            12
            
    WHERE ra.APP_ID = :APP_ID

          

          

            -
            
 

            13
            
    AND ur.USER_ID  = :USER_ID_USER

          

          

            -
            
 

            14
            
    AND ur.ROLE_ID  = ra.ROLE_ID

          

          

            -
            
 

            15
            
    AND ra.role_id  = ro.role_id

          

          

            -
            
 

            16
            
    ) AS can_Delegate,

          

          

            -
            
 

            17
            
    (SELECT COUNT(*)

          

          

            -
            
 

            18
            
    FROM ROLES ra,

          

          

            -
            
 

            19
            
      USER_ROLES ur

          

          

            -
            
 

            20
            
    WHERE ra.APP_ID = :APP_ID

          

          

            -
            
 

            21
            
    AND ur.USER_ID  = :USER_ID

          

          

            -
            
 

            22
            
    AND ur.ROLE_ID  = ra.ROLE_ID

          

          

            -
            
 

            23
            
    AND ra.role_id  = ro.role_id

          

          

            -
            
 

            24
            
    ) AS granted

          

          

            -
            
 

            25
            
  FROM ROLES ro

          

          

            -
            
 

            26
            
  WHERE ro.APP_ID = :APP_ID

          

          

            -
            
 

            27
            
  )

          

          

            -
            
 

            28
            
WHERE IS_ROLE_VARIATION IS NULL

          

          

            -
            
 

            29
            
AND app_id               = :APP_ID

          

          

            -
            
 

            30
            
ORDER BY UPPER(role_name)

          

          

            -
            
 

            31