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