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 ROLES ro,
                           USER_ROLES ur,
                           APPLICATIONS app
                 WHERE ur.ROLE_ID = ro.role_id
                   AND ro.APP_ID = app.APP_ID
                   AND ur.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 ROLES ro,
                           USER_ROLES ur,
                           APPLICATIONS app
                 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 )  OR
                                ( app.APP_ID = app.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)