Subversion Repositories DevTools

Rev

Go to most recent revision | Details | 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,
17 
               NULL AS drill_down_image
18 
          FROM applications app,
19 
               roles ro,
20 
               user_roles ur
21 
         WHERE ur.role_id = ro.role_id
22 
           AND ro.app_id = app.app_id
23 
           AND ur.user_id = :USER_ID
24 
 
25 
        UNION
26 
 
27 
        /* Get applications that have no roles yet */
28 
        SELECT '-' AS expand,
29 
 
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
51 
 
52 
        /* Get roles for specific application */
53 
        SELECT DECODE ( app.app_id, :APP_ID, '-',
54 
                        DECODE ( :APP_ID, 0, '-', '+')
55 
                      ) AS expand,
56 
               DECODE ( app.app_id, :APP_ID, app.app_id,
57 
 
58 
                      ) AS order_id,
59 
               app.app_id,
60 
               app.application_name,
61 
               ro.role_id,
62 
               ro.role_name,
63 
               ro.is_role_variation,
64 
               DECODE ( ro.is_role_variation, NULL, 'LIMG_DRILL_DOWN',
65 
                        NULL
66 
                      ) AS drill_down_image
67 
          FROM applications app,
68 
               roles ro,
69 
               user_roles ur
70 
         WHERE ur.role_id = ro.role_id
71 
           AND ro.app_id = app.app_id
72 
           AND ur.user_ID = :USER_ID
73 
           AND ( app.app_id = :APP_ID
74 
                 AND :APP_ID != 0
75 
               )
76 
         ) qry
77 
ORDER BY qry.order_id DESC, UPPER(qry.application_name), qry.is_role_variation, UPPER(qry.role_name)