Subversion Repositories DevTools

Rev

Details | Last modification | View Log | RSS feed

Rev Author Line No. Line
64 jtweddle 1 
 
2 
SELECT qry.*
3 
  FROM (
4 
 
5 
  	   	/* NETWORK NODE LEVEL */
6 
		SELECT DECODE ( COUNT( os.os_id ),
7 
			   		  	0, NULL,
8 
						NULL, NULL,
9 
						DECODE ( parent.node_id,
10 
							     :node_id, '-',
11 
								 '+'
12 
								)
13 
					  ) AS expand,
14 
			   parent.NODE_SEQ_NUM,
15 
			   parent.node_id,
16 
			   parent.NODE_NAME,
17 
			   parent.NODE_ICON,
18 
			   parent.os_id,
19 
			   parent.OS_NAME,
20 
			   parent.BOS_ICON,
21 
			   parent.prod_id,
22 
			   parent.prod_name,
23 
			   parent.SEQ_NUM,
24 
			   parent.CHANGE_LOG_FLAG,
25 
			   NULL AS IS_REJECTED,
26 
			   parent.NODE_VERSION,
27 
			   parent.NODE_LIFE_CYCLE
28 
		  FROM OPERATING_SYSTEMS os,
29 
		  	   (
30 
			    SELECT nn.node_id AS node_id,
31 
					   nn.NODE_NAME,
32 
					   nt.NODE_ICON,
33 
 
34 
					   '-' AS OS_NAME,
35 
					   NULL AS BOS_ICON,
36 
 
37 
					   '-' AS prod_name,
38 
 
39 
					   NULL AS CHANGE_LOG_FLAG,
40 
					   nn.NODE_VERSION,
41 
			   		   nn.NODE_LIFE_CYCLE,
42 
					   bc.SEQ_NUM AS NODE_SEQ_NUM
43 
				  FROM NETWORK_NODES nn,
44 
				  	   BOM_CONTENTS bc,
45 
					   NODE_TYPES nt
46 
				 WHERE bc.node_id = nn.node_id
47 
				   AND nn.node_type_id = nt.node_type_id
48 
				   AND bc.bom_id IN (
49 
				   	   			  	  /* Ensures that node_id IS IN bom_id */
50 
				   	   			  	  SELECT DISTINCT bc.bom_id
51 
									    FROM BOM_CONTENTS bc
52 
									   WHERE bc.bom_id = :bom_id
53 
									     AND (
54 
										   	    ( bc.node_id = bc.node_id AND :node_id = -1 ) OR
55 
												( bc.node_id = :node_id 	      AND :node_id != -1 )
56 
										   	 )
57 
				   	   			  	 )
58 
			   ) parent
59 
		 WHERE os.node_id (+) = parent.node_id
60 
		 GROUP BY  parent.NODE_SEQ_NUM,
61 
		 		   parent.node_id,
62 
		 	   	   parent.NODE_NAME,
63 
				   parent.NODE_ICON,
64 
				   parent.os_id,
65 
				   parent.OS_NAME,
66 
				   parent.BOS_ICON,
67 
				   parent.prod_id,
68 
				   parent.prod_name,
69 
				   parent.SEQ_NUM,
70 
				   parent.CHANGE_LOG_FLAG,
71 
				   parent.NODE_VERSION,
72 
			   	   parent.NODE_LIFE_CYCLE
73 
 
74 
 
75 
 
76 
		UNION
77 
 
78 
 
79 
		/* OS LEVEL */
80 
		SELECT DECODE ( COUNT( * ),
81 
			   		  	0, NULL,
82 
						NULL, NULL,
83 
						DECODE ( parent.node_id,
84 
								 :node_id, DECODE ( parent.os_id,
85 
								 		   		    :os_id, '-',
86 
													DECODE ( parent.node_id,
87 
														   	 :node_id, DECODE ( :os_id, -1, '-', '+')
88 
														   )
89 
												  )
90 
								)
91 
					  ) AS expand,
92 
			   parent.NODE_SEQ_NUM,
93 
			   parent.node_id,
94 
			   parent.NODE_NAME,
95 
			   parent.NODE_ICON,
96 
			   parent.os_id,
97 
			   parent.OS_NAME,
98 
			   parent.BOS_ICON,
99 
			   parent.prod_id,
100 
			   parent.prod_name,
101 
			   parent.SEQ_NUM,
102 
			   parent.CHANGE_LOG_FLAG,
103 
			   NULL AS IS_REJECTED,
104 
			   parent.NODE_VERSION,
105 
			   parent.NODE_LIFE_CYCLE
106 
		  FROM OS_CONTENTS osc,
107 
		  	   (
108 
				SELECT nn.node_id AS node_id,
109 
					   nn.NODE_NAME,
110 
					   NULL AS NODE_ICON,
111 
					   os.os_id AS os_id,
112 
					   os.OS_NAME,
113 
					   DECODE ( bt.BOS_ICON,
114 
					   		  	NULL, 'i_generic_os.gif',
115 
								bt.BOS_ICON
116 
					   		  ) BOS_ICON,
117 
 
118 
					   '-' AS prod_name,
119 
 
120 
					   NULL AS CHANGE_LOG_FLAG,
121 
					   NULL AS NODE_VERSION,
122 
 
123 
					   bc.SEQ_NUM AS NODE_SEQ_NUM
124 
				  FROM NETWORK_NODES nn,
125 
				  	   BOM_CONTENTS bc,
126 
					   OPERATING_SYSTEMS os,
127 
					   BASE_ENV be,
128 
					   OS_BASE_ENV osbe,
129 
					   BOS_TYPES bt
130 
				 WHERE bc.node_id = nn.node_id
131 
				   AND os.node_id = nn.node_id
132 
				   AND osbe.OS_ID (+) = os.OS_ID
133 
				   AND osbe.BASE_ENV_ID = be.base_env_id (+)
134 
				   AND be.BOS_ID = bt.bos_id (+)
135 
				   AND bc.bom_id = :bom_id
136 
				   AND nn.node_id = :node_id
137 
			   ) parent
138 
		 WHERE osc.os_id (+) = parent.os_id
139 
		 GROUP BY  parent.NODE_SEQ_NUM,
140 
		 		   parent.node_id,
141 
		 	   	   parent.NODE_NAME,
142 
				   parent.NODE_ICON,
143 
				   parent.os_id,
144 
				   parent.OS_NAME,
145 
				   parent.BOS_ICON,
146 
				   parent.prod_id,
147 
				   parent.prod_name,
148 
				   parent.SEQ_NUM,
149 
				   parent.CHANGE_LOG_FLAG,
150 
				   parent.NODE_VERSION,
151 
			   	   parent.NODE_LIFE_CYCLE
152 
 
153 
 
154 
		UNION
155 
 
156 
 
157 
		/* PRODUCTS LEVEL */
158 
		SELECT NULL AS expand,
159 
			   bc.SEQ_NUM AS NODE_SEQ_NUM,
160 
			   nn.node_id AS node_id,
161 
			   nn.NODE_NAME,
162 
			   NULL AS NODE_ICON,
163 
			   os.os_id AS os_id,
164 
			   os.OS_NAME,
165 
			   NULL AS BOS_ICON,
166 
			   pv.pv_id AS prod_id,
167 
			   pkg.pkg_name || ' ' || pv.pkg_version AS prod_name,
168 
			   osc.SEQ_NUM,
169 
			   osc.CHANGE_LOG_FLAG,
170 
			   pd.IS_REJECTED,
171 
			   NULL AS NODE_VERSION,
172 
 
173 
		  FROM NETWORK_NODES nn,
174 
		  	   BOM_CONTENTS bc,
175 
			   OPERATING_SYSTEMS os,
176 
			   OS_CONTENTS osc,
177 
			   PACKAGES pkg,
178 
			   PACKAGE_VERSIONS pv,
179 
			   PRODUCT_DETAILS pd
180 
		 WHERE pd.PROD_ID (+) = pv.PV_ID
181 
		   AND bc.node_id = nn.node_id
182 
		   AND os.node_id = nn.node_id
183 
		   AND pv.pkg_id = pkg.pkg_id
184 
		   AND osc.PROD_ID = pv.pv_id
185 
		   AND osc.os_id = os.os_id
186 
		   AND bc.bom_id = :bom_id
187 
		   AND nn.node_id = :node_id
188 
		   AND (
189 
			  	   ( os.os_id = os.os_id	AND :os_id = -1 ) OR
190 
			   	   ( os.os_id = :os_id	AND :os_id != -1 )
191 
			   )
192 
 
193 
		  ) qry
194 
 
195 
 ORDER BY qry.NODE_SEQ_NUM, UPPER(qry.node_name), UPPER(qry.os_name), qry.seq_num
196