Subversion Repositories DevTools

Rev

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

Rev Author Line No. Line
119 ghuddy 1 
/* Release Diff */
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,
18 
              NULL AS PV_ID_B,
19 
              NULL AS COMMENTS_B,
20 
              NULL AS PKG_NAME_B,
21 
              NULL AS PKG_VERSION_B,
22 
              NULL AS MAJOR_MINOR_B,
23 
              NULL AS PATCH_NUMBER_B,
24 
              NULL AS BUILD_NUMBER_B,
25 
              NULL AS PKG_STATE_B
123 ghuddy 26 
       FROM (
27 
             /* ReleaseContent@A - ( ReleaseContent@B + Planned@B(Additions) - Planned@B(Subtractions) ) */
28 
             SELECT pv.PKG_ID, pv.V_EXT
119 ghuddy 29 
                FROM RELEASE_CONTENT rc,
30 
                     PACKAGE_VERSIONS pv
31 
               WHERE rc.PV_ID = pv.PV_ID
123 ghuddy 32 
                 AND rc.RTAG_ID = :RTAG_A
33 
             MINUS
34 
             ((SELECT pv.PKG_ID, pv.V_EXT
35 
                 FROM RELEASE_CONTENT rc,
36 
                      PACKAGE_VERSIONS pv
37 
                WHERE rc.PV_ID = pv.PV_ID
38 
                  AND rc.RTAG_ID = :RTAG_B
39 
               UNION
40 
               SELECT pv.PKG_ID, pv.V_EXT
41 
                 FROM PLANNED pl,
42 
                      PACKAGE_VERSIONS pv
43 
                WHERE pl.PV_ID = pv.PV_ID
44 
                  AND pl.operation = 'A'
45 
                  AND pl.RTAG_ID = :RTAG_B)
46 
              MINUS
119 ghuddy 47 
              SELECT pv.PKG_ID, pv.V_EXT
123 ghuddy 48 
                FROM PLANNED pl,
119 ghuddy 49 
                     PACKAGE_VERSIONS pv
123 ghuddy 50 
               WHERE pl.PV_ID = pv.PV_ID
51 
                 AND pl.operation = 'S'
52 
                 AND pl.RTAG_ID = :RTAG_B)
53 
            ) ad,
54 
            RELEASE_CONTENT rc,
55 
            PACKAGE_VERSIONS pv,
56 
            PACKAGES pkg,
57 
            VIEWS v
58 
       WHERE rc.RTAG_ID = :RTAG_A
59 
         AND rc.BASE_VIEW_ID = v.VIEW_ID
60 
         AND rc.PV_ID = pv.PV_ID
61 
         AND pv.PKG_ID = pkg.PKG_ID
62 
         AND pv.PKG_ID = ad.PKG_ID
63 
         AND NVL( pv.V_EXT, '|LINK_A_NULL|') = NVL( ad.V_EXT, '|LINK_A_NULL|')
64 
         AND :REMOVED IS NULL
65 
 
119 ghuddy 66 
       UNION
123 ghuddy 67 
 
68 
       /* Added Packages */
119 ghuddy 69 
       SELECT 'A' AS CHANGE_TYPE,
70 
              v.VIEW_ID,
71 
              v.VIEW_NAME,
72 
              pv.PKG_ID,
73 
              NULL AS PV_ID_A,
74 
              NULL AS COMMENTS_A,
75 
              NULL AS PKG_NAME_A,
76 
              NULL AS PKG_VERSION_A,
77 
              NULL AS MAJOR_MINOR_A,
78 
              NULL AS PATCH_NUMBER_A,
79 
              NULL AS BUILD_NUMBER_A,
80 
              NULL AS PKG_STATE_A,
81 
              pv.PV_ID AS PV_ID_B,
82 
              pv.COMMENTS AS COMMENTS_B,
83 
              pkg.PKG_NAME AS PKG_NAME_B,
84 
              pv.PKG_VERSION AS PKG_VERSION_B,
85 
              pv.V_NMM AS MAJOR_MINOR_B,
86 
              get_patch_version(pv.V_MM) AS PATCH_NUMBER_B,
87 
              get_build_number(pv.V_MM) AS BUILD_NUMBER_B,
123 ghuddy 88 
              rc.deprecated_state as PKG_STATE_B
89 
       FROM (
90 
             /* ( ReleaseContent@B + Planned@B(Additions) - Planned@B(Subtractions) ) - ReleaseContent@A */
91 
             ((SELECT pv.PKG_ID, pv.V_EXT
92 
                 FROM RELEASE_CONTENT rc,
93 
                      PACKAGE_VERSIONS pv
94 
                WHERE rc.PV_ID = pv.PV_ID
95 
                  AND rc.RTAG_ID = :RTAG_B
96 
               UNION
97 
               SELECT pv.PKG_ID, pv.V_EXT
98 
                 FROM PLANNED pl,
99 
                      PACKAGE_VERSIONS pv
100 
                WHERE pl.PV_ID = pv.PV_ID
101 
                  AND pl.operation = 'A'
102 
                  AND pl.RTAG_ID = :RTAG_B)
103 
              MINUS
119 ghuddy 104 
              SELECT pv.PKG_ID, pv.V_EXT
123 ghuddy 105 
                FROM PLANNED pl,
119 ghuddy 106 
                     PACKAGE_VERSIONS pv
123 ghuddy 107 
               WHERE pl.PV_ID = pv.PV_ID
108 
                 AND pl.operation = 'S'
109 
                 AND pl.RTAG_ID = :RTAG_B)
110 
             MINUS
111 
             SELECT pv.PKG_ID, pv.V_EXT
112 
               FROM RELEASE_CONTENT rc,
113 
                  PACKAGE_VERSIONS pv
114 
              WHERE rc.PV_ID = pv.PV_ID
115 
                AND rc.RTAG_ID = :RTAG_A
116 
            ) ad,
117 
            RELEASE_CONTENT rc,
118 
            PACKAGE_VERSIONS pv,
119 
            PACKAGES pkg,
120 
            VIEWS v
121 
       WHERE rc.RTAG_ID = :RTAG_B
122 
         AND v.VIEW_ID = rc.BASE_VIEW_ID
123 
         AND rc.PV_ID = pv.PV_ID
124 
         AND pv.PKG_ID = pkg.PKG_ID
125 
         AND pv.PKG_ID = ad.PKG_ID
126 
         AND NVL( pv.V_EXT, '|LINK_A_NULL|') = NVL( ad.V_EXT, '|LINK_A_NULL|')
127 
         AND :ADDED IS NULL
128 
 
119 ghuddy 129 
       UNION
123 ghuddy 130 
 
119 ghuddy 131 
       /* UPDATED Packages */
132 
       SELECT ( CASE
133 
                  WHEN ra.V_NMM = rb.V_NMM AND ra.patch_number = rb.patch_number AND ra.build_number != rb.build_number THEN
134 
                    'UR'
135 
                  WHEN ra.PV_ID < rb.PV_ID OR (ra.V_NMM = rb.V_NMM AND ra.patch_number < rb.patch_number ) THEN
136 
                    'UW'
137 
                  ELSE
138 
                    'U'
139 
                END
140 
              ) AS CHANGE_TYPE,
141 
              ra.VIEW_ID,
142 
              ra.VIEW_NAME,
143 
              ra.PKG_ID,
144 
              ra.PV_ID AS PV_ID_A,
145 
              ra.COMMENTS AS COMMENTS_A,
146 
              ra.PKG_NAME AS PKG_NAME_A,
147 
              ra.PKG_VERSION AS PKG_VERSION_A,
148 
              ra.V_NMM AS MAJOR_MINOR_A,
149 
              ra.patch_number AS PATCH_NUMBER_A,
150 
              ra.build_number AS BUILD_NUMBER_A,
151 
              ra.DEPRECATED_STATE AS PKG_STATE_A,
152 
              rb.PV_ID AS PV_ID_B,
153 
              rb.COMMENTS AS COMMENTS_B,
154 
              rb.PKG_NAME AS PKG_NAME_B,
155 
              rb.PKG_VERSION AS PKG_VERSION_B,
156 
              rb.V_NMM AS MAJOR_MINOR_B,
157 
              rb.patch_number AS PATCH_NUMBER_B,
158 
              rb.build_number AS BUILD_NUMBER_B,
159 
              rb.DEPRECATED_STATE AS PKG_STATE_B
123 ghuddy 160 
       FROM (
119 ghuddy 161 
              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,
162 
                     get_patch_version(pv.V_MM) AS patch_number,
163 
                     get_build_number(pv.V_MM) AS build_number
164 
                FROM RELEASE_CONTENT rc,
165 
                     PACKAGE_VERSIONS pv,
166 
                     PACKAGES pkg,
167 
                     VIEWS v
168 
               WHERE rc.PV_ID = pv.PV_ID
169 
                 AND rc.RTAG_ID = :RTAG_A
170 
                 AND pv.PKG_ID = pkg.PKG_ID
171 
                 AND v.VIEW_ID  = rc.BASE_VIEW_ID
172 
              ) ra,
173 
              (
123 ghuddy 174 
               /* ( ReleaseContent@B + Planned@B(Additions) - Planned@B(Subtractions) ) */
175 
               ((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,
176 
                        get_patch_version(pv.V_MM) AS patch_number,
177 
                        get_build_number(pv.V_MM) AS build_number
178 
                   FROM RELEASE_CONTENT rc,
179 
                        PACKAGE_VERSIONS pv,
180 
                        PACKAGES pkg
181 
                  WHERE rc.PV_ID = pv.PV_ID
182 
                    AND rc.RTAG_ID = :RTAG_B
183 
                    AND pv.PKG_ID = pkg.PKG_ID
184 
                 UNION
185 
                 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,
186 
                        get_patch_version(pv.V_MM) AS patch_number,
187 
                        get_build_number(pv.V_MM) AS build_number
188 
                   FROM PLANNED pl,
189 
                        PACKAGE_VERSIONS pv,
190 
                        PACKAGES pkg
191 
                  WHERE pl.PV_ID = pv.PV_ID
192 
                    AND pv.PKG_ID = pkg.PKG_ID
193 
                    AND pl.operation = 'A'
194 
                    AND pl.RTAG_ID = :RTAG_B)
195 
                MINUS
196 
                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,
197 
                       get_patch_version(pv.V_MM) AS patch_number,
198 
                       get_build_number(pv.V_MM) AS build_number
199 
                  FROM PLANNED pl,
200 
                       PACKAGE_VERSIONS pv,
201 
                       PACKAGES pkg
202 
                 WHERE pl.PV_ID = pv.PV_ID
203 
                   AND pv.PKG_ID = pkg.PKG_ID
204 
                   AND pl.operation = 'S'
205 
                   AND pl.RTAG_ID = :RTAG_B)
119 ghuddy 206 
              ) rb
123 ghuddy 207 
       WHERE ra.PKG_ID = rb.PKG_ID
208 
         AND NVL( ra.V_EXT, '|LINK_A_NULL|') = NVL( rb.V_EXT, '|LINK_A_NULL|')
209 
         AND ra.PV_ID != rb.PV_ID
210 
         AND :UPDATED IS NULL
211 
 
119 ghuddy 212 
       UNION
123 ghuddy 213 
 
214 
       /* NOT CHANGED Packages - NOTE '0' as change type so these entries appear ahead of additions, in sort order */
215 
       SELECT '0' AS CHANGE_TYPE,
119 ghuddy 216 
              ra.VIEW_ID,
217 
              ra.VIEW_NAME,
218 
              ra.PKG_ID,
219 
              ra.PV_ID AS PV_ID_A,
220 
              ra.COMMENTS AS COMMENTS_A,
221 
              ra.PKG_NAME AS PKG_NAME_A,
222 
              ra.PKG_VERSION AS PKG_VERSION_A,
223 
              ra.V_NMM AS MAJOR_MINOR_A,
224 
              get_patch_version(ra.V_MM) AS PATCH_NUMBER_A,
225 
              get_build_number(ra.V_MM) AS BUILD_NUMBER_A,
226 
              ra.DEPRECATED_STATE as PKG_STATE_A,
227 
              rb.PV_ID AS PV_ID_B,
228 
              rb.COMMENTS AS COMMENTS_B,
229 
              rb.PKG_NAME AS PKG_NAME_B,
230 
              rb.PKG_VERSION AS PKG_VERSION_B,
231 
              rb.V_NMM AS MAJOR_MINOR_B,
232 
              get_patch_version(rb.V_MM) AS PATCH_NUMBER_B,
233 
              get_build_number(rb.V_MM) AS BUILD_NUMBER_B,
234 
              rb.DEPRECATED_STATE AS PKG_STATE_B
123 ghuddy 235 
       FROM (
119 ghuddy 236 
              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
237 
                FROM RELEASE_CONTENT rc,
238 
                     PACKAGE_VERSIONS pv,
239 
                     PACKAGES pkg,
240 
                     VIEWS v
241 
               WHERE rc.PV_ID = pv.PV_ID
242 
                 AND rc.RTAG_ID = :RTAG_A
243 
                 AND pv.PKG_ID = pkg.PKG_ID
244 
                 AND v.VIEW_ID = rc.BASE_VIEW_ID
123 ghuddy 245 
            ) ra,
246 
            (
247 
              /* ( ReleaseContent@B + Planned@B(Additions) - Planned@B(Subtractions) ) */
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, rc.DEPRECATED_STATE
249 
                 FROM RELEASE_CONTENT rc,
250 
                      PACKAGE_VERSIONS pv,
251 
                      PACKAGES pkg
252 
                WHERE rc.PV_ID = pv.PV_ID
253 
                  AND rc.RTAG_ID = :RTAG_B
254 
                  AND pv.PKG_ID = pkg.PKG_ID
255 
               UNION
256 
               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
257 
                 FROM PLANNED pl,
258 
                      PACKAGE_VERSIONS pv,
259 
                      PACKAGES pkg
260 
                WHERE pl.PV_ID = pv.PV_ID
261 
                  AND pl.RTAG_ID = :RTAG_B
262 
                  AND pl.operation = 'A'
263 
                  AND pv.PKG_ID = pkg.PKG_ID)
264 
              MINUS
265 
              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
266 
                FROM PLANNED pl,
119 ghuddy 267 
                     PACKAGE_VERSIONS pv,
268 
                     PACKAGES pkg
123 ghuddy 269 
               WHERE pl.PV_ID = pv.PV_ID
270 
                 AND pl.RTAG_ID = :RTAG_B
271 
                 AND pl.operation = 'S'
119 ghuddy 272 
                 AND pv.PKG_ID = pkg.PKG_ID
123 ghuddy 273 
            ) rb
274 
       WHERE ra.PKG_ID = rb.PKG_ID
275 
         AND NVL( ra.V_EXT, '|LINK_A_NULL|') = NVL( rb.V_EXT, '|LINK_A_NULL|')
276 
         AND ra.PV_ID = rb.PV_ID
277 
         AND :NO_CHANGE IS NULL
278 
 
119 ghuddy 279 
    ) qry
123 ghuddy 280 
   /* NOTE: Sorting by change type has been added to ensure diff.asp can display items correctly and filter out */
281 
   /*       duplicates. Order is Unchanged, Additions, Removals, and finally Updates. */
282 
ORDER BY UPPER(qry.VIEW_NAME),UPPER(qry.PKG_NAME_A),CHANGE_TYPE