Subversion Repositories DevTools

Rev

Rev 5512 | Details | Compare with Previous | Last modification | View Log | RSS feed

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