Subversion Repositories DevTools

Rev

Go to most recent revision | Details | Last modification | View Log | RSS feed

Rev Author Line No. Line
13 rsolanki 1 
SELECT qry.*
2 
  FROM (
3 
 
4 
	   	/* PROJECTS */
5 
		SELECT DECODE ( COUNT( br.branch_id ),
6 
			   		  	0, NULL,
7 
						NULL, NULL,
8 
						DECODE ( parent.proj_id,
9 
							   	 :proj_id, '-',
10 
								 '+'
11 
							   )
12 
			   		  ) AS expand,
13 
			   parent.proj_id,
14 
			   parent.proj_name,
15 
 
16 
			   '-' AS branch_name,
17 
               NULL AS branch_comments
18 
		  FROM BRANCHES br,
19 
		  	   (
20 
				SELECT proj.proj_id,
21 
					   proj.PROJ_NAME
22 
				  FROM DM_PROJECTS proj
23 
				 WHERE proj.IS_HIDDEN IS NULL
24 
		  	   ) parent
25 
		 WHERE br.proj_id (+) = parent.proj_id
26 
		 GROUP BY parent.proj_id,
27 
			      parent.proj_name
28 
 
29 
		UNION
30 
 
31 
		/* BRANCHES */
32 
		SELECT DECODE ( COUNT( bm.bom_id ),
33 
			   		  	0, NULL,
34 
						NULL, NULL,
35 
						DECODE ( parent.branch_id,
36 
							   	 :branch_id, '-',
37 
								 '+'
38 
							   )
39 
			   		  ) AS expand,
40 
			   parent.proj_id,
41 
			   parent.proj_name,
42 
			   parent.branch_id,
43 
			   parent.branch_name,
44 
               parent.BRANCH_COMMENTS
45 
		  FROM BOMS bm,
46 
		  	   (
47 
				SELECT proj.proj_id,
48 
					   proj.PROJ_NAME,
49 
					   br.branch_id,
50 
					   br.BRANCH_NAME,
51 
                       br.BRANCH_COMMENTS
52 
				  FROM DM_PROJECTS proj,
53 
				  	   BRANCHES br
54 
				 WHERE br.proj_id = proj.proj_id
55 
				   AND proj.proj_id = :proj_id
56 
				   AND proj.IS_HIDDEN IS NULL
57 
				   AND br.IS_HIDDEN IS NULL
58 
		  	   ) parent
59 
		 WHERE bm.branch_id (+) = parent.branch_id
60 
		 GROUP BY parent.proj_id,
61 
			      parent.proj_name,
62 
				  parent.branch_id,
63 
			      parent.branch_name,
64 
                  parent.BRANCH_COMMENTS
65 
 
66 
 
67 
		  ) qry
68 
 ORDER BY UPPER(qry.proj_name), qry.branch_id
69