Subversion Repositories DevTools

Rev

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

Rev 2 Rev 6
Line 1... Line 1...
1 
/* User Roles */
 1 
/* User Roles */
2 
SELECT qry.*
 2 
SELECT qry.*
3 
  FROM (
 3 
  FROM (
4
- 
 
5 
  	   	/* Get all applications to be able to group it */
 4 
        /* Get all applications to be able to group it */
6 
		SELECT DISTINCT
5 
        SELECT DISTINCT
7 
			   DECODE ( app.app_id,
 6 
               DECODE ( app.app_id, :APP_ID, '-',
8 
			   		  	:APP_ID, '-',
 7 
                        DECODE ( :APP_ID, 0, '-', '+')
9 
						DECODE ( :APP_ID, 0, '-', '+') ) AS expand,
 8 
                      ) AS expand,
10 
			   DECODE ( app.app_id,
 9 
               DECODE ( app.app_id, :APP_ID, app.app_id,
11 
			   		  	:APP_ID, app.app_id,
 10 
                        0
12 
						0 ) AS order_id,
 11 
                      ) AS order_id,
13 
			   app.app_id,
 12 
               app.app_id,
14 
			   app.APPLICATION_NAME,
 13 
               app.application_name,
15 
			   TO_NUMBER(NULL) AS role_id,
 14 
               TO_NUMBER(NULL) AS role_id,
16 
			   '-' AS role_name,
 15 
               '-' AS role_name,
17 
			   '-' AS IS_ROLE_VARIATION,
 16 
               '-' AS is_role_variation,
18 
			   NULL AS drill_down_image
 17 
               NULL AS drill_down_image
19 
		  FROM ROLES ro,
 18 
          FROM applications app,
20 
		  	   USER_ROLES ur,
 19 
               roles ro,
21 
			   APPLICATIONS app
 20 
               user_roles ur
22 
		 WHERE ur.ROLE_ID = ro.role_id
 21 
         WHERE ur.role_id = ro.role_id
23 
		   AND ro.APP_ID = app.APP_ID
 22 
           AND ro.app_id = app.app_id
24 
		   AND ur.USER_ID = :USER_ID
 23 
           AND ur.user_id = :USER_ID
25
24
- 
 
 25 
        UNION
26 
		UNION
26
- 
 
 27 
        /* Get applications that have no roles yet */
- 
 
 28 
        SELECT '-' AS expand,
- 
 
 29 
               0 AS order_id,
- 
 
 30 
               app.app_id,
- 
 
 31 
               app.application_name,
- 
 
 32 
               TO_NUMBER(NULL) AS role_id,
- 
 
 33 
               '-' AS role_name,
- 
 
 34 
               '-' AS is_role_variation,
- 
 
 35 
               NULL AS drill_down_image
- 
 
 36 
          FROM applications app,
- 
 
 37 
               user_applications ua
- 
 
 38 
         WHERE ua.user_id = :USER_ID
- 
 
 39 
           AND app.app_id = ua.app_id
- 
 
 40 
           AND app.app_id NOT IN
- 
 
 41 
               (
- 
 
 42 
                 SELECT DISTINCT ua.app_id
- 
 
 43 
                 FROM user_applications ua, user_roles ur, roles ro
- 
 
 44 
                 WHERE ur.user_id = ua.user_id
- 
 
 45 
                 AND ur.role_id = ro.role_id
- 
 
 46 
                 AND ro.app_id = ua.app_id
- 
 
 47 
                 AND ua.user_id = :USER_ID
- 
 
 48 
               )
- 
 
 49
- 
 
 50 
        UNION
27
51
28 
		/* Get roles for specific application */
 52 
        /* Get roles for specific application */
29 
		SELECT DECODE ( app.app_id,
 53 
        SELECT DECODE ( app.app_id, :APP_ID, '-',
30 
			   		  	:APP_ID, '-',
 54 
                        DECODE ( :APP_ID, 0, '-', '+')
31 
						DECODE ( :APP_ID, 0, '-', '+') ) AS expand,
 55 
                      ) AS expand,
32 
			   DECODE ( app.app_id,
 56 
               DECODE ( app.app_id, :APP_ID, app.app_id,
33 
			   		  	:APP_ID, app.app_id,
 57 
                        0
34 
						0 ) AS order_id,
 58 
                      ) AS order_id,
35 
			   app.app_id,
 59 
               app.app_id,
36 
			   app.APPLICATION_NAME,
 60 
               app.application_name,
37 
			   ro.role_id,
 61 
               ro.role_id,
38 
			   ro.role_name,
 62 
               ro.role_name,
39 
			   ro.IS_ROLE_VARIATION,
 63 
               ro.is_role_variation,
40 
			   DECODE ( ro.IS_ROLE_VARIATION,
 64 
               DECODE ( ro.is_role_variation, NULL, 'LIMG_DRILL_DOWN',
41 
			   		    NULL, 'LIMG_DRILL_DOWN', NULL
 65 
                        NULL
42 
			   		  ) AS drill_down_image
 66 
                      ) AS drill_down_image
43 
		  FROM ROLES ro,
 67 
          FROM applications app,
44 
		  	   USER_ROLES ur,
 68 
               roles ro,
45 
			   APPLICATIONS app
 69 
               user_roles ur
46 
		 WHERE ur.ROLE_ID = ro.role_id
 70 
         WHERE ur.role_id = ro.role_id
47 
		   AND ro.APP_ID = app.APP_ID
 71 
           AND ro.app_id = app.app_id
48 
		   AND ur.USER_ID = :USER_ID
 72 
           AND ur.user_ID = :USER_ID
49 
		   AND (
 - 
 
50 
		   	    ( app.APP_ID = :APP_ID	AND  :APP_ID != 0 )  OR
 73 
           AND ( app.app_id = :APP_ID
51 
				( app.APP_ID = app.APP_ID	AND  :APP_ID = 0 )
 74 
                 AND :APP_ID != 0
52 
		   	   )
75 
               )
53
- 
 
54 
  	   ) qry
 76 
         ) qry
55 
ORDER BY qry.order_id DESC, UPPER(qry.application_name), qry.IS_ROLE_VARIATION, UPPER(qry.role_name)
77 
ORDER BY qry.order_id DESC, UPPER(qry.application_name), qry.is_role_variation, UPPER(qry.role_name)
56 
 
 - 
 
57 

            78