Subversion Repositories DevTools

Rev

Rev 6268 | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
6268 dpurdie 1 
/* Release Diff */
119 ghuddy 2 
SELECT qry.*
123 ghuddy 3 
    FROM (
4 
 
119 ghuddy 5 
       /* Removed Packages */
6 
       SELECT 'R' AS CHANGE_TYPE,
7 
              v.VIEW_ID,
8 
              v.VIEW_NAME,
9 
              pv.PKG_ID,
10 
              pv.PV_ID AS PV_ID_A,
11 
              pv.COMMENTS AS COMMENTS_A,
12 
              pkg.PKG_NAME AS PKG_NAME_A,
13 
              pv.PKG_VERSION AS PKG_VERSION_A,
14 
              pv.V_NMM AS MAJOR_MINOR_A,
15 
              get_patch_version(pv.V_MM) AS PATCH_NUMBER_A,
16 
              get_build_number(pv.V_MM) AS BUILD_NUMBER_A,
17 
              rc.deprecated_state AS PKG_STATE_A,
5081 dpurdie 18 
              NVL2(rc.SDKTAG_ID, 1, 0) AS PKG_SDK_A,
119 ghuddy 19 
              NULL AS PV_ID_B,
20 
              NULL AS COMMENTS_B,
21 
              NULL AS PKG_NAME_B,
22 
              NULL AS PKG_VERSION_B,
23 
              NULL AS MAJOR_MINOR_B,
24 
              NULL AS PATCH_NUMBER_B,
25 
              NULL AS BUILD_NUMBER_B,
5081 dpurdie 26 
              NULL AS PKG_STATE_B,
27 
 
123 ghuddy 28 
       FROM (
29 
             /* ReleaseContent@A - ( ReleaseContent@B + Planned@B(Additions) - Planned@B(Subtractions) ) */
30 
             SELECT pv.PKG_ID, pv.V_EXT
119 ghuddy 31 
                FROM RELEASE_CONTENT rc,
32 
                     PACKAGE_VERSIONS pv
33 
               WHERE rc.PV_ID = pv.PV_ID
123 ghuddy 34 
                 AND rc.RTAG_ID = :RTAG_A
35 
             MINUS
36 
             ((SELECT pv.PKG_ID, pv.V_EXT
37 
                 FROM RELEASE_CONTENT rc,
38 
                      PACKAGE_VERSIONS pv
39 
                WHERE rc.PV_ID = pv.PV_ID
40 
                  AND rc.RTAG_ID = :RTAG_B
41 
               UNION
42 
               SELECT pv.PKG_ID, pv.V_EXT
43 
                 FROM PLANNED pl,
44 
                      PACKAGE_VERSIONS pv
45 
                WHERE pl.PV_ID = pv.PV_ID
46 
                  AND pl.operation = 'A'
47 
                  AND pl.RTAG_ID = :RTAG_B)
48 
              MINUS
119 ghuddy 49 
              SELECT pv.PKG_ID, pv.V_EXT
123 ghuddy 50 
                FROM PLANNED pl,
119 ghuddy 51 
                     PACKAGE_VERSIONS pv
123 ghuddy 52 
               WHERE pl.PV_ID = pv.PV_ID
53 
                 AND pl.operation = 'S'
54 
                 AND pl.RTAG_ID = :RTAG_B)
55 
            ) ad,
56 
            RELEASE_CONTENT rc,
57 
            PACKAGE_VERSIONS pv,
58 
            PACKAGES pkg,
59 
            VIEWS v
60 
       WHERE rc.RTAG_ID = :RTAG_A
61 
         AND rc.BASE_VIEW_ID = v.VIEW_ID
62 
         AND rc.PV_ID = pv.PV_ID
63 
         AND pv.PKG_ID = pkg.PKG_ID
64 
         AND pv.PKG_ID = ad.PKG_ID
65 
         AND NVL( pv.V_EXT, '|LINK_A_NULL|') = NVL( ad.V_EXT, '|LINK_A_NULL|')
66 
         AND :REMOVED IS NULL
67 
 
119 ghuddy 68 
       UNION
123 ghuddy 69 
 
70 
       /* Added Packages */
119 ghuddy 71 
       SELECT 'A' AS CHANGE_TYPE,
72 
              v.VIEW_ID,
73 
              v.VIEW_NAME,
74 
              pv.PKG_ID,
75 
              NULL AS PV_ID_A,
76 
              NULL AS COMMENTS_A,
77 
              NULL AS PKG_NAME_A,
78 
              NULL AS PKG_VERSION_A,
79 
              NULL AS MAJOR_MINOR_A,
80 
              NULL AS PATCH_NUMBER_A,
81 
              NULL AS BUILD_NUMBER_A,
82 
              NULL AS PKG_STATE_A,
5081 dpurdie 83 
 
119 ghuddy 84 
              pv.PV_ID AS PV_ID_B,
85 
              pv.COMMENTS AS COMMENTS_B,
86 
              pkg.PKG_NAME AS PKG_NAME_B,
87 
              pv.PKG_VERSION AS PKG_VERSION_B,
88 
              pv.V_NMM AS MAJOR_MINOR_B,
89 
              get_patch_version(pv.V_MM) AS PATCH_NUMBER_B,
90 
              get_build_number(pv.V_MM) AS BUILD_NUMBER_B,
5081 dpurdie 91 
              rc.deprecated_state as PKG_STATE_B,
92 
              NVL2(rc.SDKTAG_ID, 1, 0) AS PKG_SDK_B
123 ghuddy 93 
       FROM (
94 
             /* ( ReleaseContent@B + Planned@B(Additions) - Planned@B(Subtractions) ) - ReleaseContent@A */
95 
             ((SELECT pv.PKG_ID, pv.V_EXT
96 
                 FROM RELEASE_CONTENT rc,
97 
                      PACKAGE_VERSIONS pv
98 
                WHERE rc.PV_ID = pv.PV_ID
99 
                  AND rc.RTAG_ID = :RTAG_B
100 
               UNION
101 
               SELECT pv.PKG_ID, pv.V_EXT
102 
                 FROM PLANNED pl,
103 
                      PACKAGE_VERSIONS pv
104 
                WHERE pl.PV_ID = pv.PV_ID
105 
                  AND pl.operation = 'A'
106 
                  AND pl.RTAG_ID = :RTAG_B)
107 
              MINUS
119 ghuddy 108 
              SELECT pv.PKG_ID, pv.V_EXT
123 ghuddy 109 
                FROM PLANNED pl,
119 ghuddy 110 
                     PACKAGE_VERSIONS pv
123 ghuddy 111 
               WHERE pl.PV_ID = pv.PV_ID
112 
                 AND pl.operation = 'S'
113 
                 AND pl.RTAG_ID = :RTAG_B)
114 
             MINUS
115 
             SELECT pv.PKG_ID, pv.V_EXT
116 
               FROM RELEASE_CONTENT rc,
117 
                  PACKAGE_VERSIONS pv
118 
              WHERE rc.PV_ID = pv.PV_ID
119 
                AND rc.RTAG_ID = :RTAG_A
120 
            ) ad,
121 
            RELEASE_CONTENT rc,
122 
            PACKAGE_VERSIONS pv,
123 
            PACKAGES pkg,
124 
            VIEWS v
125 
       WHERE rc.RTAG_ID = :RTAG_B
126 
         AND v.VIEW_ID = rc.BASE_VIEW_ID
127 
         AND rc.PV_ID = pv.PV_ID
128 
         AND pv.PKG_ID = pkg.PKG_ID
129 
         AND pv.PKG_ID = ad.PKG_ID
130 
         AND NVL( pv.V_EXT, '|LINK_A_NULL|') = NVL( ad.V_EXT, '|LINK_A_NULL|')
131 
         AND :ADDED IS NULL
132 
 
119 ghuddy 133 
       UNION
123 ghuddy 134 
 
119 ghuddy 135 
       /* UPDATED Packages */
136 
       SELECT ( CASE
137 
                  WHEN ra.V_NMM = rb.V_NMM AND ra.patch_number = rb.patch_number AND ra.build_number != rb.build_number THEN
138 
                    'UR'
6615 dpurdie 139 
                        WHEN ra.V_NMM = rb.V_NMM AND COALESCE(TO_NUMBER(REGEXP_SUBSTR(ra.patch_number, '^\d+(\.\d+)?')), 0) < COALESCE(TO_NUMBER(REGEXP_SUBSTR(rb.patch_number, '^\d+(\.\d+)?')), 0) THEN
119 ghuddy 140 
                    'UW'
141 
                  ELSE
142 
                    'U'
143 
                END
144 
              ) AS CHANGE_TYPE,
145 
              ra.VIEW_ID,
146 
              ra.VIEW_NAME,
147 
              ra.PKG_ID,
148 
              ra.PV_ID AS PV_ID_A,
149 
              ra.COMMENTS AS COMMENTS_A,
150 
              ra.PKG_NAME AS PKG_NAME_A,
151 
              ra.PKG_VERSION AS PKG_VERSION_A,
152 
              ra.V_NMM AS MAJOR_MINOR_A,
153 
              ra.patch_number AS PATCH_NUMBER_A,
154 
              ra.build_number AS BUILD_NUMBER_A,
155 
              ra.DEPRECATED_STATE AS PKG_STATE_A,
5081 dpurdie 156 
              NVL2(ra.SDKTAG_ID, 1, 0) AS PKG_SDK_A,
119 ghuddy 157 
              rb.PV_ID AS PV_ID_B,
158 
              rb.COMMENTS AS COMMENTS_B,
159 
              rb.PKG_NAME AS PKG_NAME_B,
160 
              rb.PKG_VERSION AS PKG_VERSION_B,
161 
              rb.V_NMM AS MAJOR_MINOR_B,
162 
              rb.patch_number AS PATCH_NUMBER_B,
163 
              rb.build_number AS BUILD_NUMBER_B,
5081 dpurdie 164 
              rb.DEPRECATED_STATE AS PKG_STATE_B,
165 
              NVL2(rb.SDKTAG_ID, 1, 0) AS PKG_SDK_B
123 ghuddy 166 
       FROM (
119 ghuddy 167 
              SELECT pv.PKG_ID, pv.V_EXT, pv.PV_ID, pv.V_NMM, pv.V_MM, pv.COMMENTS, pkg.PKG_NAME, pv.PKG_VERSION, v.VIEW_NAME, v.VIEW_ID, rc.DEPRECATED_STATE,
168 
                     get_patch_version(pv.V_MM) AS patch_number,
5081 dpurdie 169 
                     get_build_number(pv.V_MM) AS build_number,
170 
                     rc.SDKTAG_ID AS SDKTAG_ID
119 ghuddy 171 
                FROM RELEASE_CONTENT rc,
172 
                     PACKAGE_VERSIONS pv,
173 
                     PACKAGES pkg,
174 
                     VIEWS v
175 
               WHERE rc.PV_ID = pv.PV_ID
176 
                 AND rc.RTAG_ID = :RTAG_A
177 
                 AND pv.PKG_ID = pkg.PKG_ID
178 
                 AND v.VIEW_ID  = rc.BASE_VIEW_ID
179 
              ) ra,
180 
              (
123 ghuddy 181 
               /* ( ReleaseContent@B + Planned@B(Additions) - Planned@B(Subtractions) ) */
182 
               ((SELECT pv.PKG_ID, pv.V_EXT, pv.PV_ID, pv.V_NMM, pv.V_MM, pv.COMMENTS, pkg.PKG_NAME, pv.PKG_VERSION, rc.DEPRECATED_STATE,
183 
                        get_patch_version(pv.V_MM) AS patch_number,
5081 dpurdie 184 
                        get_build_number(pv.V_MM) AS build_number,
185 
                        rc.SDKTAG_ID AS SDKTAG_ID
123 ghuddy 186 
                   FROM RELEASE_CONTENT rc,
187 
                        PACKAGE_VERSIONS pv,
188 
                        PACKAGES pkg
189 
                  WHERE rc.PV_ID = pv.PV_ID
190 
                    AND rc.RTAG_ID = :RTAG_B
191 
                    AND pv.PKG_ID = pkg.PKG_ID
192 
                 UNION
193 
                 SELECT pv.PKG_ID, pv.V_EXT, pv.PV_ID, pv.V_NMM, pv.V_MM, pv.COMMENTS, pkg.PKG_NAME, pv.PKG_VERSION, NULL AS DEPRECATED_STATE,
194 
                        get_patch_version(pv.V_MM) AS patch_number,
5081 dpurdie 195 
                        get_build_number(pv.V_MM) AS build_number,
196 
                        NULL AS SDKTAG_ID
123 ghuddy 197 
                   FROM PLANNED pl,
198 
                        PACKAGE_VERSIONS pv,
199 
                        PACKAGES pkg
200 
                  WHERE pl.PV_ID = pv.PV_ID
201 
                    AND pv.PKG_ID = pkg.PKG_ID
202 
                    AND pl.operation = 'A'
203 
                    AND pl.RTAG_ID = :RTAG_B)
204 
                MINUS
205 
                SELECT pv.PKG_ID, pv.V_EXT, pv.PV_ID, pv.V_NMM, pv.V_MM, pv.COMMENTS, pkg.PKG_NAME, pv.PKG_VERSION, NULL AS DEPRECATED_STATE,
206 
                       get_patch_version(pv.V_MM) AS patch_number,
5081 dpurdie 207 
                       get_build_number(pv.V_MM) AS build_number,
208 
                       NULL AS SDKTAG_ID
123 ghuddy 209 
                  FROM PLANNED pl,
210 
                       PACKAGE_VERSIONS pv,
211 
                       PACKAGES pkg
212 
                 WHERE pl.PV_ID = pv.PV_ID
213 
                   AND pv.PKG_ID = pkg.PKG_ID
214 
                   AND pl.operation = 'S'
215 
                   AND pl.RTAG_ID = :RTAG_B)
119 ghuddy 216 
              ) rb
123 ghuddy 217 
       WHERE ra.PKG_ID = rb.PKG_ID
218 
         AND NVL( ra.V_EXT, '|LINK_A_NULL|') = NVL( rb.V_EXT, '|LINK_A_NULL|')
219 
         AND ra.PV_ID != rb.PV_ID
220 
         AND :UPDATED IS NULL
221 
 
119 ghuddy 222 
       UNION
123 ghuddy 223 
 
224 
       /* NOT CHANGED Packages - NOTE '0' as change type so these entries appear ahead of additions, in sort order */
6189 dpurdie 225 
       SELECT 'B' AS CHANGE_TYPE,
119 ghuddy 226 
              ra.VIEW_ID,
227 
              ra.VIEW_NAME,
228 
              ra.PKG_ID,
229 
              ra.PV_ID AS PV_ID_A,
230 
              ra.COMMENTS AS COMMENTS_A,
231 
              ra.PKG_NAME AS PKG_NAME_A,
232 
              ra.PKG_VERSION AS PKG_VERSION_A,
233 
              ra.V_NMM AS MAJOR_MINOR_A,
234 
              get_patch_version(ra.V_MM) AS PATCH_NUMBER_A,
235 
              get_build_number(ra.V_MM) AS BUILD_NUMBER_A,
236 
              ra.DEPRECATED_STATE as PKG_STATE_A,
5081 dpurdie 237 
              NVL2(ra.SDKTAG_ID, 1, 0) AS PKG_SDK_A,
119 ghuddy 238 
              rb.PV_ID AS PV_ID_B,
239 
              rb.COMMENTS AS COMMENTS_B,
240 
              rb.PKG_NAME AS PKG_NAME_B,
241 
              rb.PKG_VERSION AS PKG_VERSION_B,
242 
              rb.V_NMM AS MAJOR_MINOR_B,
243 
              get_patch_version(rb.V_MM) AS PATCH_NUMBER_B,
244 
              get_build_number(rb.V_MM) AS BUILD_NUMBER_B,
5081 dpurdie 245 
              rb.DEPRECATED_STATE AS PKG_STATE_B,
246 
              NVL2(rb.SDKTAG_ID, 1, 0) AS PKG_SDK_B
123 ghuddy 247 
       FROM (
5081 dpurdie 248 
              SELECT pv.PKG_ID, pv.V_EXT, pv.PV_ID, pv.V_NMM, pv.V_MM, pv.COMMENTS, pkg.PKG_NAME, pv.PKG_VERSION, v.VIEW_NAME, v.VIEW_ID, rc.DEPRECATED_STATE, rc.SDKTAG_ID
119 ghuddy 249 
                FROM RELEASE_CONTENT rc,
250 
                     PACKAGE_VERSIONS pv,
251 
                     PACKAGES pkg,
252 
                     VIEWS v
253 
               WHERE rc.PV_ID = pv.PV_ID
254 
                 AND rc.RTAG_ID = :RTAG_A
255 
                 AND pv.PKG_ID = pkg.PKG_ID
256 
                 AND v.VIEW_ID = rc.BASE_VIEW_ID
123 ghuddy 257 
            ) ra,
258 
            (
259 
              /* ( ReleaseContent@B + Planned@B(Additions) - Planned@B(Subtractions) ) */
6189 dpurdie 260 
              SELECT pv.PKG_ID, pv.V_EXT, pv.PV_ID, pv.V_NMM, pv.V_MM, pv.COMMENTS, pkg.PKG_NAME, pv.PKG_VERSION, rc.DEPRECATED_STATE , rc.SDKTAG_ID
123 ghuddy 261 
                 FROM RELEASE_CONTENT rc,
262 
                      PACKAGE_VERSIONS pv,
263 
                      PACKAGES pkg
264 
                WHERE rc.PV_ID = pv.PV_ID
265 
                  AND rc.RTAG_ID = :RTAG_B
266 
                  AND pv.PKG_ID = pkg.PKG_ID
267 
               UNION
5081 dpurdie 268 
               SELECT pv.PKG_ID, pv.V_EXT, pv.PV_ID, pv.V_NMM, pv.V_MM, pv.COMMENTS, pkg.PKG_NAME, pv.PKG_VERSION, NULL AS DEPRECATED_STATE, NULL AS SDKTAG_ID
123 ghuddy 269 
                 FROM PLANNED pl,
270 
                      PACKAGE_VERSIONS pv,
271 
                      PACKAGES pkg
272 
                WHERE pl.PV_ID = pv.PV_ID
273 
                  AND pl.RTAG_ID = :RTAG_B
274 
                  AND pl.operation = 'A'
6189 dpurdie 275 
                  AND pv.PKG_ID = pkg.PKG_ID
123 ghuddy 276 
              MINUS
5081 dpurdie 277 
              SELECT pv.PKG_ID, pv.V_EXT, pv.PV_ID, pv.V_NMM, pv.V_MM, pv.COMMENTS, pkg.PKG_NAME, pv.PKG_VERSION, NULL AS DEPRECATED_STATE, NULL AS SDKTAG_ID
123 ghuddy 278 
                FROM PLANNED pl,
119 ghuddy 279 
                     PACKAGE_VERSIONS pv,
280 
                     PACKAGES pkg
123 ghuddy 281 
               WHERE pl.PV_ID = pv.PV_ID
282 
                 AND pl.RTAG_ID = :RTAG_B
283 
                 AND pl.operation = 'S'
119 ghuddy 284 
                 AND pv.PKG_ID = pkg.PKG_ID
123 ghuddy 285 
            ) rb
286 
       WHERE ra.PKG_ID = rb.PKG_ID
287 
         AND NVL( ra.V_EXT, '|LINK_A_NULL|') = NVL( rb.V_EXT, '|LINK_A_NULL|')
288 
         AND ra.PV_ID = rb.PV_ID
289 
         AND :NO_CHANGE IS NULL
290 
 
6189 dpurdie 291 
      /* All Pending additions and Removals
292 
         Change_Type is set so that these will first
293 
       */
294 
      UNION
295 
             SELECT rb.CHANGE_TYPE AS CHANGE_TYPE,
296 
              ra.VIEW_ID,
297 
              ra.VIEW_NAME,
298 
              ra.PKG_ID,
299 
              ra.PV_ID AS PV_ID_A,
300 
              ra.COMMENTS AS COMMENTS_A,
301 
              ra.PKG_NAME AS PKG_NAME_A,
302 
              ra.PKG_VERSION AS PKG_VERSION_A,
303 
              ra.V_NMM AS MAJOR_MINOR_A,
304 
              get_patch_version(ra.V_MM) AS PATCH_NUMBER_A,
305 
              get_build_number(ra.V_MM) AS BUILD_NUMBER_A,
306 
              ra.DEPRECATED_STATE as PKG_STATE_A,
307 
              NVL2(ra.SDKTAG_ID, 1, 0) AS PKG_SDK_A,
308 
              rb.PV_ID AS PV_ID_B,
309 
              rb.COMMENTS AS COMMENTS_B,
310 
              rb.PKG_NAME AS PKG_NAME_B,
311 
              rb.PKG_VERSION AS PKG_VERSION_B,
312 
              rb.V_NMM AS MAJOR_MINOR_B,
313 
              get_patch_version(rb.V_MM) AS PATCH_NUMBER_B,
314 
              get_build_number(rb.V_MM) AS BUILD_NUMBER_B,
315 
              rb.DEPRECATED_STATE AS PKG_STATE_B,
316 
              NVL2(rb.SDKTAG_ID, 1, 0) AS PKG_SDK_B
317 
       FROM (
318 
              SELECT pv.PKG_ID, pv.V_EXT, pv.PV_ID, pv.V_NMM, pv.V_MM, pv.COMMENTS, pkg.PKG_NAME, pv.PKG_VERSION, v.VIEW_NAME, v.VIEW_ID, rc.DEPRECATED_STATE, rc.SDKTAG_ID
319 
                FROM RELEASE_CONTENT rc,
320 
                     PACKAGE_VERSIONS pv,
321 
                     PACKAGES pkg,
322 
                     VIEWS v
323 
               WHERE rc.PV_ID = pv.PV_ID
324 
                 AND rc.RTAG_ID = :RTAG_A
325 
                 AND pv.PKG_ID = pkg.PKG_ID
326 
                 AND v.VIEW_ID = rc.BASE_VIEW_ID
327 
            ) ra,
328 
            (
329 
              /* ( Planned@B(Additions) and Planned@B(Subtractions) ) */
330 
              (
331 
               SELECT 'APA' as CHANGE_TYPE, pv.PKG_ID, pv.V_EXT, pv.PV_ID, pv.V_NMM, pv.V_MM, pv.COMMENTS, pkg.PKG_NAME, pv.PKG_VERSION, NULL AS DEPRECATED_STATE, NULL AS SDKTAG_ID
332 
                 FROM PLANNED pl,
333 
                      PACKAGE_VERSIONS pv,
334 
                      PACKAGES pkg
335 
                WHERE pl.PV_ID = pv.PV_ID
336 
                  AND pl.RTAG_ID = :RTAG_B
337 
                  AND pl.operation = 'A'
338 
                  AND pv.PKG_ID = pkg.PKG_ID)
339 
              UNION
340 
              SELECT 'APS' as CHANGE_TYPE, pv.PKG_ID, pv.V_EXT, pv.PV_ID, pv.V_NMM, pv.V_MM, pv.COMMENTS, pkg.PKG_NAME, pv.PKG_VERSION, NULL AS DEPRECATED_STATE, NULL AS SDKTAG_ID
341 
                FROM PLANNED pl,
342 
                     PACKAGE_VERSIONS pv,
343 
                     PACKAGES pkg
344 
               WHERE pl.PV_ID = pv.PV_ID
345 
                 AND pl.RTAG_ID = :RTAG_B
346 
                 AND pl.operation = 'S'
347 
                 AND pv.PKG_ID = pkg.PKG_ID
348 
            ) rb
349 
       WHERE ra.PKG_ID = rb.PKG_ID
350 
         AND NVL( ra.V_EXT, '|LINK_A_NULL|') = NVL( rb.V_EXT, '|LINK_A_NULL|')
351 
 
352 
 
119 ghuddy 353 
    ) qry
123 ghuddy 354 
   /* NOTE: Sorting by change type has been added to ensure diff.asp can display items correctly and filter out */
6189 dpurdie 355 
   /*       duplicates. Order is Pending, Unchanged, Additions, Removals, and finally Updates. */
356 
 
357 
ORDER BY UPPER(qry.VIEW_NAME),UPPER(qry.PKG_NAME_A),qry.CHANGE_TYPE
358