| 119 |
ghuddy |
1 |
/* Release Diff */
|
|
|
2 |
SELECT qry.*
|
|
|
3 |
FROM (
|
|
|
4 |
|
|
|
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
|
|
|
26 |
FROM (
|
|
|
27 |
SELECT pv.PKG_ID, pv.V_EXT
|
|
|
28 |
FROM RELEASE_CONTENT rc,
|
|
|
29 |
PACKAGE_VERSIONS pv
|
|
|
30 |
WHERE rc.PV_ID = pv.PV_ID
|
|
|
31 |
AND rc.RTAG_ID = :RTAG_A
|
|
|
32 |
MINUS
|
|
|
33 |
SELECT pv.PKG_ID, pv.V_EXT
|
|
|
34 |
FROM RELEASE_CONTENT rc,
|
|
|
35 |
PACKAGE_VERSIONS pv
|
|
|
36 |
WHERE rc.PV_ID = pv.PV_ID
|
|
|
37 |
AND rc.RTAG_ID = :RTAG_B
|
|
|
38 |
) ad,
|
|
|
39 |
RELEASE_CONTENT rc,
|
|
|
40 |
PACKAGE_VERSIONS pv,
|
|
|
41 |
PACKAGES pkg,
|
|
|
42 |
VIEWS v
|
|
|
43 |
WHERE rc.RTAG_ID = :RTAG_A
|
|
|
44 |
AND rc.BASE_VIEW_ID = v.VIEW_ID
|
|
|
45 |
AND rc.PV_ID = pv.PV_ID
|
|
|
46 |
AND pv.PKG_ID = pkg.PKG_ID
|
|
|
47 |
AND pv.PKG_ID = ad.PKG_ID
|
|
|
48 |
AND NVL( pv.V_EXT, '|LINK_A_NULL|') = NVL( ad.V_EXT, '|LINK_A_NULL|')
|
|
|
49 |
AND :REMOVED IS NULL
|
|
|
50 |
|
|
|
51 |
UNION
|
|
|
52 |
|
|
|
53 |
/* Added Packages */
|
|
|
54 |
SELECT 'A' AS CHANGE_TYPE,
|
|
|
55 |
v.VIEW_ID,
|
|
|
56 |
v.VIEW_NAME,
|
|
|
57 |
pv.PKG_ID,
|
|
|
58 |
NULL AS PV_ID_A,
|
|
|
59 |
NULL AS COMMENTS_A,
|
|
|
60 |
NULL AS PKG_NAME_A,
|
|
|
61 |
NULL AS PKG_VERSION_A,
|
|
|
62 |
NULL AS MAJOR_MINOR_A,
|
|
|
63 |
NULL AS PATCH_NUMBER_A,
|
|
|
64 |
NULL AS BUILD_NUMBER_A,
|
|
|
65 |
NULL AS PKG_STATE_A,
|
|
|
66 |
pv.PV_ID AS PV_ID_B,
|
|
|
67 |
pv.COMMENTS AS COMMENTS_B,
|
|
|
68 |
pkg.PKG_NAME AS PKG_NAME_B,
|
|
|
69 |
pv.PKG_VERSION AS PKG_VERSION_B,
|
|
|
70 |
pv.V_NMM AS MAJOR_MINOR_B,
|
|
|
71 |
get_patch_version(pv.V_MM) AS PATCH_NUMBER_B,
|
|
|
72 |
get_build_number(pv.V_MM) AS BUILD_NUMBER_B,
|
|
|
73 |
rc.deprecated_state as PKG_STATE_B
|
|
|
74 |
FROM (
|
|
|
75 |
SELECT pv.PKG_ID, pv.V_EXT
|
|
|
76 |
FROM RELEASE_CONTENT rc,
|
|
|
77 |
PACKAGE_VERSIONS pv
|
|
|
78 |
WHERE rc.PV_ID = pv.PV_ID
|
|
|
79 |
AND rc.RTAG_ID = :RTAG_B
|
|
|
80 |
MINUS
|
|
|
81 |
SELECT pv.PKG_ID, pv.V_EXT
|
|
|
82 |
FROM RELEASE_CONTENT rc,
|
|
|
83 |
PACKAGE_VERSIONS pv
|
|
|
84 |
WHERE rc.PV_ID = pv.PV_ID
|
|
|
85 |
AND rc.RTAG_ID = :RTAG_A
|
|
|
86 |
) ad,
|
|
|
87 |
RELEASE_CONTENT rc,
|
|
|
88 |
PACKAGE_VERSIONS pv,
|
|
|
89 |
PACKAGES pkg,
|
|
|
90 |
VIEWS v
|
|
|
91 |
WHERE rc.RTAG_ID = :RTAG_B
|
|
|
92 |
AND v.VIEW_ID = rc.BASE_VIEW_ID
|
|
|
93 |
AND rc.PV_ID = pv.PV_ID
|
|
|
94 |
AND pv.PKG_ID = pkg.PKG_ID
|
|
|
95 |
AND pv.PKG_ID = ad.PKG_ID
|
|
|
96 |
AND NVL( pv.V_EXT, '|LINK_A_NULL|') = NVL( ad.V_EXT, '|LINK_A_NULL|')
|
|
|
97 |
AND :ADDED IS NULL
|
|
|
98 |
|
|
|
99 |
UNION
|
|
|
100 |
|
|
|
101 |
/* UPDATED Packages */
|
|
|
102 |
SELECT ( CASE
|
|
|
103 |
WHEN ra.V_NMM = rb.V_NMM AND ra.patch_number = rb.patch_number AND ra.build_number != rb.build_number THEN
|
|
|
104 |
'UR'
|
|
|
105 |
WHEN ra.PV_ID < rb.PV_ID OR (ra.V_NMM = rb.V_NMM AND ra.patch_number < rb.patch_number ) THEN
|
|
|
106 |
'UW'
|
|
|
107 |
ELSE
|
|
|
108 |
'U'
|
|
|
109 |
END
|
|
|
110 |
) AS CHANGE_TYPE,
|
|
|
111 |
ra.VIEW_ID,
|
|
|
112 |
ra.VIEW_NAME,
|
|
|
113 |
ra.PKG_ID,
|
|
|
114 |
ra.PV_ID AS PV_ID_A,
|
|
|
115 |
ra.COMMENTS AS COMMENTS_A,
|
|
|
116 |
ra.PKG_NAME AS PKG_NAME_A,
|
|
|
117 |
ra.PKG_VERSION AS PKG_VERSION_A,
|
|
|
118 |
ra.V_NMM AS MAJOR_MINOR_A,
|
|
|
119 |
ra.patch_number AS PATCH_NUMBER_A,
|
|
|
120 |
ra.build_number AS BUILD_NUMBER_A,
|
|
|
121 |
ra.DEPRECATED_STATE AS PKG_STATE_A,
|
|
|
122 |
rb.PV_ID AS PV_ID_B,
|
|
|
123 |
rb.COMMENTS AS COMMENTS_B,
|
|
|
124 |
rb.PKG_NAME AS PKG_NAME_B,
|
|
|
125 |
rb.PKG_VERSION AS PKG_VERSION_B,
|
|
|
126 |
rb.V_NMM AS MAJOR_MINOR_B,
|
|
|
127 |
rb.patch_number AS PATCH_NUMBER_B,
|
|
|
128 |
rb.build_number AS BUILD_NUMBER_B,
|
|
|
129 |
rb.DEPRECATED_STATE AS PKG_STATE_B
|
|
|
130 |
FROM (
|
|
|
131 |
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,
|
|
|
132 |
get_patch_version(pv.V_MM) AS patch_number,
|
|
|
133 |
get_build_number(pv.V_MM) AS build_number
|
|
|
134 |
FROM RELEASE_CONTENT rc,
|
|
|
135 |
PACKAGE_VERSIONS pv,
|
|
|
136 |
PACKAGES pkg,
|
|
|
137 |
VIEWS v
|
|
|
138 |
WHERE rc.PV_ID = pv.PV_ID
|
|
|
139 |
AND rc.RTAG_ID = :RTAG_A
|
|
|
140 |
AND pv.PKG_ID = pkg.PKG_ID
|
|
|
141 |
AND v.VIEW_ID = rc.BASE_VIEW_ID
|
|
|
142 |
) ra,
|
|
|
143 |
(
|
|
|
144 |
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,
|
|
|
145 |
get_patch_version(pv.V_MM) AS patch_number,
|
|
|
146 |
get_build_number(pv.V_MM) AS build_number
|
|
|
147 |
FROM RELEASE_CONTENT rc,
|
|
|
148 |
PACKAGE_VERSIONS pv,
|
|
|
149 |
PACKAGES pkg
|
|
|
150 |
WHERE rc.PV_ID = pv.PV_ID
|
|
|
151 |
AND rc.RTAG_ID = :RTAG_B
|
|
|
152 |
AND pv.PKG_ID = pkg.PKG_ID
|
|
|
153 |
) rb
|
|
|
154 |
WHERE ra.PKG_ID = rb.PKG_ID
|
|
|
155 |
AND NVL( ra.V_EXT, '|LINK_A_NULL|') = NVL( rb.V_EXT, '|LINK_A_NULL|')
|
|
|
156 |
AND ra.PV_ID != rb.PV_ID
|
|
|
157 |
AND :UPDATED IS NULL
|
|
|
158 |
|
|
|
159 |
UNION
|
|
|
160 |
|
|
|
161 |
/* NOT CHANGED Packages */
|
|
|
162 |
SELECT NULL AS CHANGE_TYPE,
|
|
|
163 |
ra.VIEW_ID,
|
|
|
164 |
ra.VIEW_NAME,
|
|
|
165 |
ra.PKG_ID,
|
|
|
166 |
ra.PV_ID AS PV_ID_A,
|
|
|
167 |
ra.COMMENTS AS COMMENTS_A,
|
|
|
168 |
ra.PKG_NAME AS PKG_NAME_A,
|
|
|
169 |
ra.PKG_VERSION AS PKG_VERSION_A,
|
|
|
170 |
ra.V_NMM AS MAJOR_MINOR_A,
|
|
|
171 |
get_patch_version(ra.V_MM) AS PATCH_NUMBER_A,
|
|
|
172 |
get_build_number(ra.V_MM) AS BUILD_NUMBER_A,
|
|
|
173 |
ra.DEPRECATED_STATE as PKG_STATE_A,
|
|
|
174 |
rb.PV_ID AS PV_ID_B,
|
|
|
175 |
rb.COMMENTS AS COMMENTS_B,
|
|
|
176 |
rb.PKG_NAME AS PKG_NAME_B,
|
|
|
177 |
rb.PKG_VERSION AS PKG_VERSION_B,
|
|
|
178 |
rb.V_NMM AS MAJOR_MINOR_B,
|
|
|
179 |
get_patch_version(rb.V_MM) AS PATCH_NUMBER_B,
|
|
|
180 |
get_build_number(rb.V_MM) AS BUILD_NUMBER_B,
|
|
|
181 |
rb.DEPRECATED_STATE AS PKG_STATE_B
|
|
|
182 |
FROM (
|
|
|
183 |
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
|
|
|
184 |
FROM RELEASE_CONTENT rc,
|
|
|
185 |
PACKAGE_VERSIONS pv,
|
|
|
186 |
PACKAGES pkg,
|
|
|
187 |
VIEWS v
|
|
|
188 |
WHERE rc.PV_ID = pv.PV_ID
|
|
|
189 |
AND rc.RTAG_ID = :RTAG_A
|
|
|
190 |
AND pv.PKG_ID = pkg.PKG_ID
|
|
|
191 |
AND v.VIEW_ID = rc.BASE_VIEW_ID
|
|
|
192 |
) ra,
|
|
|
193 |
(
|
|
|
194 |
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
|
|
|
195 |
FROM RELEASE_CONTENT rc,
|
|
|
196 |
PACKAGE_VERSIONS pv,
|
|
|
197 |
PACKAGES pkg
|
|
|
198 |
WHERE rc.PV_ID = pv.PV_ID
|
|
|
199 |
AND rc.RTAG_ID = :RTAG_B
|
|
|
200 |
AND pv.PKG_ID = pkg.PKG_ID
|
|
|
201 |
) rb
|
|
|
202 |
WHERE ra.PKG_ID = rb.PKG_ID
|
|
|
203 |
AND NVL( ra.V_EXT, '|LINK_A_NULL|') = NVL( rb.V_EXT, '|LINK_A_NULL|')
|
|
|
204 |
AND ra.PV_ID = rb.PV_ID
|
|
|
205 |
AND :NO_CHANGE IS NULL
|
|
|
206 |
|
|
|
207 |
) qry
|
|
|
208 |
|
|
|
209 |
ORDER BY UPPER(qry.VIEW_NAME),UPPER(qry.PKG_NAME_A)
|