Subversion Repositories DevTools

/* User Roles */
SELECT qry.*
  FROM (
        /* Get all applications to be able to group it */
        SELECT DISTINCT 
               DECODE ( app.app_id, :APP_ID, '-',
                        DECODE ( :APP_ID, 0, '-', '+')
                      ) AS expand,
               DECODE ( app.app_id, :APP_ID, app.app_id,
                        0
                      ) AS order_id,
               app.app_id,
               app.application_name,
               TO_NUMBER(NULL) AS role_id,
               '-' AS role_name,
               '-' AS is_role_variation,
               NULL AS drill_down_image
          FROM applications app,
               roles ro,
               user_roles ur
         WHERE ur.role_id = ro.role_id
           AND ro.app_id = app.app_id
           AND ur.user_id = :USER_ID
        
        UNION
        
        /* Get applications that have no roles yet */
        SELECT '-' AS expand,
               0 AS order_id,
               app.app_id,
               app.application_name,
               TO_NUMBER(NULL) AS role_id,
               '-' AS role_name,
               '-' AS is_role_variation,
               NULL AS drill_down_image
          FROM applications app,
               user_applications ua
         WHERE ua.user_id = :USER_ID
           AND app.app_id = ua.app_id
           AND app.app_id NOT IN
               (
                 SELECT DISTINCT ua.app_id
                 FROM user_applications ua, user_roles ur, roles ro
                 WHERE ur.user_id = ua.user_id
                 AND ur.role_id = ro.role_id
                 AND ro.app_id = ua.app_id
                 AND ua.user_id = :USER_ID
               )
        
        UNION
        
        /* Get roles for specific application */
        SELECT DECODE ( app.app_id, :APP_ID, '-',
                        DECODE ( :APP_ID, 0, '-', '+')
                      ) AS expand,
               DECODE ( app.app_id, :APP_ID, app.app_id,
                        0
                      ) AS order_id,
               app.app_id,
               app.application_name,
               ro.role_id,
               ro.role_name,
               ro.is_role_variation,
               DECODE ( ro.is_role_variation, NULL, 'LIMG_DRILL_DOWN',
                        NULL
                      ) AS drill_down_image
          FROM applications app,
               roles ro,
               user_roles ur
         WHERE ur.role_id = ro.role_id
           AND ro.app_id = app.app_id
           AND ur.user_ID = :USER_ID
           AND ( app.app_id = :APP_ID
                 AND :APP_ID != 0
               )
         ) qry
ORDER BY qry.order_id DESC, UPPER(qry.application_name), qry.is_role_variation, UPPER(qry.role_name)