Subversion Repositories DevTools

Rev

Details | Last modification | View Log | RSS feed

Rev Author Line No. Line
15 rsolanki 1 
/* Release Diff */
2 
SELECT qry.*
3 
  FROM (
4 
 
5 
		/* Removed Patches*/
6 
		SELECT 'R' AS CHANGE_TYPE,
7 
			   oldbom.PKG_ID,
8 
			   oldbom.PV_ID AS PV_ID_A,
9 
		       oldbom.PKG_NAME AS PKG_NAME_A,
10 
			   oldbom.PKG_VERSION AS PKG_VERSION_A,
11 
			   oldbom.NODE_NAME AS NODE_NAME_A,
12 
			   oldbom.OS_NAME AS OS_NAME_A,
17 rsolanki 13 
			   oldbom.OS_ID AS OS_ID_A,
14 
			   oldbom.NODE_ID AS NODE_ID_A,
15 rsolanki 15 
			   NULL AS PV_ID_B,
16 
			   NULL AS PKG_NAME_B,
17 
			   NULL AS PKG_VERSION_B,
18 
			   NULL AS NODE_NAME_B,
17 rsolanki 19 
			   NULL AS OS_NAME_B,
20 
			   NULL AS OS_ID_B,
21 
			   NULL AS NODE_ID_B
15 rsolanki 22 
		  FROM (
23 
		  		SELECT osc.PROD_ID,
24 
					   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,
25 
					   pv.PKG_VERSION, pv.PV_ID, pkg.PKG_NAME, pv.PKG_ID, nn.NODE_NAME AS NODE_NAME_B
26 
				  FROM OS_CONTENTS osc,
27 
				  	   OPERATING_SYSTEMS os,
28 
					   NETWORK_NODES nn,
29 
					   BOM_CONTENTS bc,
30 
					   PACKAGE_VERSIONS pv,
31 
					   PACKAGES pkg
32 
				 WHERE osc.OS_ID = os.OS_ID
33 
				   AND os.NODE_ID = nn.NODE_ID
34 
				   AND bc.NODE_ID = nn.NODE_ID
35 
				   AND osc.PROD_ID = pv.PV_ID
36 
				   AND NOT pv.IS_PATCH IS NULL
37 
				   AND bc.BOM_ID = :SBOM_B
38 
				   AND pkg.PKG_ID = pv.PKG_ID
39 
		  	   ) newbom,
40 
			   (
41 
			   	SELECT osc.PROD_ID,
17 rsolanki 42 
					   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK, nn.NODE_ID, os.OS_ID,
15 rsolanki 43 
					   pv.PKG_VERSION, pv.PV_ID, pkg.PKG_NAME, pv.PKG_ID, nn.NODE_NAME, os.OS_NAME
44 
				  FROM OS_CONTENTS osc,
45 
				  	   OPERATING_SYSTEMS os,
46 
					   NETWORK_NODES nn,
47 
					   BOM_CONTENTS bc,
48 
					   PACKAGE_VERSIONS pv,
49 
					   PACKAGES pkg
50 
				 WHERE osc.OS_ID = os.OS_ID
51 
				   AND os.NODE_ID = nn.NODE_ID
52 
				   AND bc.NODE_ID = nn.NODE_ID
53 
				   AND osc.PROD_ID = pv.PV_ID
54 
				   AND NOT pv.IS_PATCH IS NULL
55 
				   AND bc.BOM_ID = :SBOM_A
56 
				   AND pkg.PKG_ID = pv.PKG_ID
57 
 
58 
			   ) oldbom
59 
		 WHERE newbom.PK (+) = oldbom.PK
60 
		   AND newbom.PROD_ID (+) = oldbom.PROD_ID
61 
		   AND newbom.PK IS NULL
62 
		   AND :REMOVED IS NULL
63 
 
64 
 
65 
		UNION
66 
 
67 
		/* Removed Products*/
68 
		SELECT 'R' AS CHANGE_TYPE,
69 
			   oldbom.PKG_ID,
70 
			   oldbom.PV_ID AS PV_ID_A,
71 
		       oldbom.PKG_NAME AS PKG_NAME_A,
72 
			   oldbom.PKG_VERSION AS PKG_VERSION_A,
73 
			   oldbom.NODE_NAME_A,
74 
			   oldbom.OS_NAME_A,
17 rsolanki 75 
			   oldbom.OS_ID AS OS_ID_A,
76 
			   oldbom.NODE_ID AS NODE_ID_A,
15 rsolanki 77 
			   NULL AS PV_ID_B,
78 
			   NULL AS PKG_NAME_B,
79 
			   NULL AS PKG_VERSION_B,
80 
			   NULL AS NODE_NAME_B,
17 rsolanki 81 
			   NULL AS OS_NAME_B,
82 
   			   NULL AS OS_ID_B,
83 
			   NULL AS NODE_ID_B
15 rsolanki 84 
			 FROM (
85 
		  		SELECT osc.PROD_ID,
17 rsolanki 86 
					   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,
15 rsolanki 87 
					   pv.PKG_ID, pv.PKG_VERSION, pkg.PKG_NAME, pv.PV_ID, nn.NODE_NAME AS NODE_NAME_B,
88 
					   NVL( pv.V_EXT, '|LINK_A_NULL|' ) AS V_EXT
89 
				  FROM OS_CONTENTS osc,
90 
				  	   OPERATING_SYSTEMS os,
91 
					   NETWORK_NODES nn,
92 
					   BOM_CONTENTS bc,
93 
					   PACKAGE_VERSIONS pv,
94 
					   PACKAGES pkg
95 
				 WHERE osc.OS_ID = os.OS_ID
96 
				   AND os.NODE_ID = nn.NODE_ID
97 
				   AND bc.NODE_ID = nn.NODE_ID
98 
				   AND osc.PROD_ID = pv.PV_ID
99 
				   AND pv.IS_PATCH IS NULL
100 
				   AND bc.BOM_ID = :SBOM_B
101 
				   AND pkg.PKG_ID = pv.PKG_ID
102 
		  	   ) newbom,
103 
			   (
104 
			   	SELECT osc.PROD_ID,
17 rsolanki 105 
					   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK, nn.NODE_ID, os.OS_ID,
15 rsolanki 106 
					   pv.PKG_ID, pv.PKG_VERSION, pkg.PKG_NAME, pv.PV_ID, nn.NODE_NAME AS NODE_NAME_A, os.OS_NAME AS OS_NAME_A,
107 
					   NVL( pv.V_EXT, '|LINK_A_NULL|' ) AS V_EXT
108 
				  FROM OS_CONTENTS osc,
109 
				  	   OPERATING_SYSTEMS os,
110 
					   NETWORK_NODES nn,
111 
					   BOM_CONTENTS bc,
112 
					   PACKAGE_VERSIONS pv,
113 
					   PACKAGES pkg
114 
				 WHERE osc.OS_ID = os.OS_ID
115 
				   AND os.NODE_ID = nn.NODE_ID
116 
				   AND bc.NODE_ID = nn.NODE_ID
117 
				   AND osc.PROD_ID = pv.PV_ID
118 
				   AND pv.IS_PATCH IS NULL
119 
				   AND bc.BOM_ID = :SBOM_A
120 
				   AND pkg.PKG_ID = pv.PKG_ID
121 
			   ) oldbom
122 
		 WHERE newbom.PK (+) = oldbom.PK
123 
		   --AND newbom.PROD_ID (+) = oldbom.PROD_ID
124 
		   AND newbom.PKG_ID (+) = oldbom.PKG_ID
125 
		   AND newbom.V_EXT (+) = oldbom.V_EXT
126 
		   AND newbom.PK IS NULL
127 
		   AND :REMOVED IS NULL
128 
 
129 
		UNION
130 
 
131 
 
132 
		/* Added Patches */
133 
		SELECT 'A' AS CHANGE_TYPE,
134 
			   newbom.PKG_ID,
135 
			   NULL AS PV_ID_A,
136 
			   NULL AS PKG_NAME_A,
137 
			   NULL AS PKG_VERSION_A,
138 
			   NULL AS NODE_NAME_A,
139 
			   NULL AS OS_NAME_A,
17 rsolanki 140 
			   NULL AS OS_ID_A,
141 
			   NULL AS NODE_ID_A,
15 rsolanki 142 
			   newbom.PV_ID AS PV_ID_B,
143 
			   newbom.PKG_NAME AS PKG_NAME_B,
144 
			   newbom.PKG_VERSION AS PKG_VERSION_B,
145 
			   newbom.NODE_NAME_B,
17 rsolanki 146 
			   newbom.OS_NAME_B,
147 
			   newbom.OS_ID AS OS_ID_B,
148 
			   newbom.NODE_ID AS NODE_ID_B
15 rsolanki 149 
		  FROM (
150 
		  		SELECT osc.PROD_ID,
17 rsolanki 151 
					   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK, nn.NODE_ID, os.OS_ID,
15 rsolanki 152 
					   pv.PKG_VERSION, pv.PV_ID, pkg.PKG_NAME, pv.PKG_ID, nn.NODE_NAME AS NODE_NAME_B, os.OS_NAME AS OS_NAME_B
153 
				  FROM OS_CONTENTS osc,
154 
				  	   OPERATING_SYSTEMS os,
155 
					   NETWORK_NODES nn,
156 
					   BOM_CONTENTS bc,
157 
					   PACKAGE_VERSIONS pv,
158 
					   PACKAGES pkg
159 
				 WHERE osc.OS_ID = os.OS_ID
160 
				   AND os.NODE_ID = nn.NODE_ID
161 
				   AND bc.NODE_ID = nn.NODE_ID
162 
				   AND osc.PROD_ID = pv.PV_ID
163 
				   AND NOT pv.IS_PATCH IS NULL
164 
				   AND bc.BOM_ID = :SBOM_B
165 
				   AND pkg.PKG_ID = pv.PKG_ID
166 
		  	   ) newbom,
167 
			   (
168 
			   	SELECT osc.PROD_ID,
169 
					   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK
170 
				  FROM OS_CONTENTS osc,
171 
				  	   OPERATING_SYSTEMS os,
172 
					   NETWORK_NODES nn,
173 
					   BOM_CONTENTS bc,
174 
					   PACKAGE_VERSIONS pv
175 
				 WHERE osc.OS_ID = os.OS_ID
176 
				   AND os.NODE_ID = nn.NODE_ID
177 
				   AND bc.NODE_ID = nn.NODE_ID
178 
				   AND osc.PROD_ID = pv.PV_ID
179 
				   AND NOT pv.IS_PATCH IS NULL
180 
				   AND bc.BOM_ID = :SBOM_A
181 
			   ) oldbom
182 
		 WHERE newbom.PK = oldbom.PK (+)
183 
		   AND newbom.PROD_ID = oldbom.PROD_ID (+)
184 
		   AND oldbom.PK IS NULL
185 
		   AND :ADDED IS NULL
186 
 
187 
		UNION
188 
 
189 
		/* Added Products */
190 
		SELECT 'A' AS CHANGE_TYPE,
191 
			   newbom.PKG_ID,
192 
			   NULL AS PV_ID_A,
193 
			   NULL AS PKG_NAME_A,
194 
			   NULL AS PKG_VERSION_A,
195 
			   NULL AS NODE_NAME_A,
196 
			   NULL AS OS_NAME_A,
17 rsolanki 197 
			   NULL AS OS_ID_A,
198 
			   NULL AS NODE_ID_A,
15 rsolanki 199 
			   newbom.PV_ID AS PV_ID_B,
200 
			   newbom.PKG_NAME AS PKG_NAME_B,
201 
			   newbom.PKG_VERSION AS PKG_VERSION_B,
202 
			   newbom.NODE_NAME_B,
17 rsolanki 203 
			   newbom.OS_NAME_B,
204 
			   newbom.OS_ID AS OS_ID_B,
205 
			   newbom.NODE_ID AS NODE_ID_B
15 rsolanki 206 
		  FROM (
207 
		  		SELECT osc.PROD_ID,
17 rsolanki 208 
					   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK, nn.NODE_ID, os.OS_ID,
15 rsolanki 209 
					   pv.PKG_ID, pv.PKG_VERSION, pkg.PKG_NAME, pv.PV_ID, nn.NODE_NAME AS NODE_NAME_B, os.OS_NAME AS OS_NAME_B,
210 
					   NVL( pv.V_EXT, '|LINK_A_NULL|' ) AS V_EXT
211 
				  FROM OS_CONTENTS osc,
212 
				  	   OPERATING_SYSTEMS os,
213 
					   NETWORK_NODES nn,
214 
					   BOM_CONTENTS bc,
215 
					   PACKAGE_VERSIONS pv,
216 
					   PACKAGES pkg
217 
				 WHERE osc.OS_ID = os.OS_ID
218 
				   AND os.NODE_ID = nn.NODE_ID
219 
				   AND bc.NODE_ID = nn.NODE_ID
220 
				   AND osc.PROD_ID = pv.PV_ID
221 
				   AND pv.IS_PATCH IS NULL
222 
				   AND bc.BOM_ID = :SBOM_B
223 
				   AND pkg.PKG_ID = pv.PKG_ID
224 
		  	   ) newbom,
225 
			   (
226 
			   	SELECT osc.PROD_ID,
227 
					   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,
228 
					   pv.PKG_ID,
229 
					   NVL( pv.V_EXT, '|LINK_A_NULL|' ) AS V_EXT
230 
				  FROM OS_CONTENTS osc,
231 
				  	   OPERATING_SYSTEMS os,
232 
					   NETWORK_NODES nn,
233 
					   BOM_CONTENTS bc,
234 
					   PACKAGE_VERSIONS pv
235 
				 WHERE osc.OS_ID = os.OS_ID
236 
				   AND os.NODE_ID = nn.NODE_ID
237 
				   AND bc.NODE_ID = nn.NODE_ID
238 
				   AND osc.PROD_ID = pv.PV_ID
239 
				   AND pv.IS_PATCH IS NULL
240 
				   AND bc.BOM_ID = :SBOM_A
241 
			   ) oldbom
242 
		 WHERE newbom.PK = oldbom.PK (+)
243 
		   --AND newbom.PROD_ID = oldbom.PROD_ID (+)
244 
		   AND newbom.PKG_ID  = oldbom.PKG_ID (+)
245 
		   AND newbom.V_EXT  = oldbom.V_EXT (+)
246 
		   AND oldbom.PROD_ID IS NULL
247 
		   AND :ADDED IS NULL
248 
 
249 
 
250 
		UNION
251 
 
252 
		/* UPDATED Packages */
253 
		SELECT (
254 
				CASE WHEN ra.PV_ID < rb.PV_ID THEN
255 
					'UW'
256 
				ELSE
257 
					'U'
258 
				END
259 
				) AS CHANGE_TYPE,
260 
			   ra.PKG_ID,
261 
			   ra.PV_ID AS PV_ID_A,
262 
		       ra.PKG_NAME AS PKG_NAME_A,
263 
			   ra.PKG_VERSION AS PKG_VERSION_A,
264 
			   ra.NODE_NAME_A,
265 
			   ra.OS_NAME_A,
17 rsolanki 266 
			   ra.OS_ID AS OS_ID_A,
267 
			   ra.NODE_ID AS NODE_ID_A,
15 rsolanki 268 
			   rb.PV_ID AS PV_ID_B,
269 
			   rb.PKG_NAME AS PKG_NAME_B,
270 
			   rb.PKG_VERSION AS PKG_VERSION_B,
271 
			   rb.NODE_NAME_B,
17 rsolanki 272 
			   rb.OS_NAME_B,
273 
			   rb.OS_ID AS OS_ID_B,
274 
			   rb.NODE_ID AS NODE_ID_B
275 
 
15 rsolanki 276 
		  FROM (
277 
		   	   SELECT osc.PROD_ID, pv.PV_ID, pv.V_EXT, pv.PKG_VERSION, pkg.PKG_NAME, pv.PKG_ID, nn.NODE_NAME AS NODE_NAME_A,
17 rsolanki 278 
			   os.OS_NAME AS OS_NAME_A, os.OS_ID, nn.NODE_ID
15 rsolanki 279 
			   FROM OS_CONTENTS osc,
280 
			  	    OPERATING_SYSTEMS os,
281 
					NETWORK_NODES nn,
282 
					BOM_CONTENTS bc,
283 
					PACKAGE_VERSIONS pv,
284 
					PACKAGES pkg
285 
			  WHERE osc.OS_ID = os.OS_ID
286 
				AND os.NODE_ID = nn.NODE_ID
287 
				AND bc.NODE_ID = nn.NODE_ID
288 
				AND osc.PROD_ID = pv.PV_ID
289 
				AND pv.IS_PATCH IS NULL
290 
				AND bc.BOM_ID = :SBOM_A
291 
				AND pkg.PKG_ID = pv.PKG_ID
292 
		       ) ra,
293 
			   (
294 
			   SELECT osc.PROD_ID, pv.PV_ID, pv.V_EXT, pv.PKG_VERSION, pkg.PKG_NAME, pv.PKG_ID, nn.NODE_NAME AS NODE_NAME_B,
17 rsolanki 295 
			   os.OS_NAME AS OS_NAME_B, os.OS_ID, nn.NODE_ID
15 rsolanki 296 
			   FROM OS_CONTENTS osc,
297 
			  	    OPERATING_SYSTEMS os,
298 
					NETWORK_NODES nn,
299 
					BOM_CONTENTS bc,
300 
					PACKAGE_VERSIONS pv,
301 
					PACKAGES pkg
302 
			  WHERE osc.OS_ID = os.OS_ID
303 
				AND os.NODE_ID = nn.NODE_ID
304 
				AND bc.NODE_ID = nn.NODE_ID
305 
				AND osc.PROD_ID = pv.PV_ID
306 
				AND pv.IS_PATCH IS NULL
307 
				AND bc.BOM_ID = :SBOM_B
308 
				AND pv.PKG_ID = pkg.PKG_ID
309 
				AND osc.PROD_ID NOT IN (
310 
				SELECT osc.PROD_ID
311 
			   FROM OS_CONTENTS osc,
312 
			  	    OPERATING_SYSTEMS os,
313 
					NETWORK_NODES nn,
314 
					BOM_CONTENTS bc,
315 
					PACKAGE_VERSIONS pv
316 
			  WHERE osc.OS_ID = os.OS_ID
317 
				AND os.NODE_ID = nn.NODE_ID
318 
				AND bc.NODE_ID = nn.NODE_ID
319 
				AND osc.PROD_ID = pv.PV_ID
320 
				AND pv.IS_PATCH IS NULL
321 
				AND bc.BOM_ID = :SBOM_A
322 
				)
323 
			   ) rb
324 
		 WHERE ra.PKG_ID = rb.PKG_ID
325 
		   AND NVL( ra.V_EXT, '|LINK_A_NULL|') = NVL( rb.V_EXT, '|LINK_A_NULL|')
326 
		   AND ra.PV_ID != rb.PV_ID
327 
		   AND :UPDATED IS NULL
328 
		   AND ra.NODE_NAME_A = rb.NODE_NAME_B
329 
		   AND ra.OS_NAME_A = rb.OS_NAME_B
330 
  		) qry
331 
 ORDER BY UPPER(qry.PKG_NAME_A)