Subversion Repositories DevTools

Rev

Details | Last modification | View Log | RSS feed

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