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 (
4 
 
5 
  	   	/* Get all applications to be able to group it */
6 
		SELECT DISTINCT
7 
			   DECODE ( app.app_id,
8 
			   		  	:APP_ID, '-',
9 
						DECODE ( :APP_ID, 0, '-', '+') ) AS expand,
10 
			   DECODE ( app.app_id,
11 
			   		  	:APP_ID, app.app_id,
12 
 
13 
			   app.app_id,
14 
			   app.APPLICATION_NAME,
15 
			   TO_NUMBER(NULL) AS role_id,
16 
			   '-' AS role_name,
17 
			   '-' AS IS_ROLE_VARIATION,
18 
			   NULL AS drill_down_image
19 
		  FROM ROLES ro,
20 
		  	   USER_ROLES ur,
21 
			   APPLICATIONS app
22 
		 WHERE ur.ROLE_ID = ro.role_id
23 
		   AND ro.APP_ID = app.APP_ID
24 
		   AND ur.USER_ID = :USER_ID
25 
 
26 
		UNION
27 
 
28 
		/* Get roles for specific application */
29 
		SELECT DECODE ( app.app_id,
30 
			   		  	:APP_ID, '-',
31 
						DECODE ( :APP_ID, 0, '-', '+') ) AS expand,
32 
			   DECODE ( app.app_id,
33 
			   		  	:APP_ID, app.app_id,
34 
 
35 
			   app.app_id,
36 
			   app.APPLICATION_NAME,
37 
			   ro.role_id,
38 
			   ro.role_name,
39 
			   ro.IS_ROLE_VARIATION,
40 
			   DECODE ( ro.IS_ROLE_VARIATION,
41 
			   		    NULL, 'LIMG_DRILL_DOWN', NULL
42 
			   		  ) AS drill_down_image
43 
		  FROM ROLES ro,
44 
		  	   USER_ROLES ur,
45 
			   APPLICATIONS app
46 
		 WHERE ur.ROLE_ID = ro.role_id
47 
		   AND ro.APP_ID = app.APP_ID
48 
		   AND ur.USER_ID = :USER_ID
49 
		   AND (
50 
		   	    ( app.APP_ID = :APP_ID	AND  :APP_ID != 0 )  OR
51 
				( app.APP_ID = app.APP_ID	AND  :APP_ID = 0 )
52 
		   	   )
53 
 
54 
  	   ) qry
55 
ORDER BY qry.order_id DESC, UPPER(qry.application_name), qry.IS_ROLE_VARIATION, UPPER(qry.role_name)
56