| 64 |
jtweddle |
1 |
/* Formatted on 2006/09/12 14:03 (Formatter Plus v4.8.6) */
|
|
|
2 |
/* Bom Compare */
|
|
|
3 |
SELECT DISTINCT pv.pv_id AS prod_id, pkg.pkg_name, pv.pkg_version,
|
|
|
4 |
pv.is_patch, pd.is_rejected, pkg.pkg_id
|
|
|
5 |
FROM (
|
|
|
6 |
/* ADDED PATCHES ONLY */
|
|
|
7 |
SELECT 'A' AS change_type, newbom.prod_id, newbom.pk
|
|
|
8 |
FROM (SELECT osc.prod_id,
|
|
|
9 |
nn.node_name || '_' || os.os_name AS pk
|
|
|
10 |
FROM os_contents osc,
|
|
|
11 |
operating_systems os,
|
|
|
12 |
network_nodes nn,
|
|
|
13 |
bom_contents bc,
|
|
|
14 |
package_versions pv
|
|
|
15 |
WHERE osc.os_id = os.os_id
|
|
|
16 |
AND os.node_id = nn.node_id
|
|
|
17 |
AND bc.node_id = nn.node_id
|
|
|
18 |
AND osc.prod_id = pv.pv_id
|
|
|
19 |
AND NOT pv.is_patch IS NULL
|
|
|
20 |
AND pv.is_obsolete IS NULL
|
|
|
21 |
AND bc.bom_id = :bom_id) newbom,
|
|
|
22 |
(SELECT osc.prod_id,
|
|
|
23 |
nn.node_name || '_' || os.os_name AS pk
|
|
|
24 |
FROM os_contents osc,
|
|
|
25 |
operating_systems os,
|
|
|
26 |
network_nodes nn,
|
|
|
27 |
bom_contents bc,
|
|
|
28 |
package_versions pv
|
|
|
29 |
WHERE osc.os_id = os.os_id
|
|
|
30 |
AND os.node_id = nn.node_id
|
|
|
31 |
AND bc.node_id = nn.node_id
|
|
|
32 |
AND osc.prod_id = pv.pv_id
|
|
|
33 |
AND NOT pv.is_patch IS NULL
|
|
|
34 |
AND bc.bom_id = :compare_bom_id) oldbom
|
|
|
35 |
WHERE newbom.pk = oldbom.pk(+)
|
|
|
36 |
AND newbom.prod_id = oldbom.prod_id(+)
|
|
|
37 |
AND oldbom.pk IS NULL
|
|
|
38 |
UNION
|
|
|
39 |
/* ADDED PRODUCTS ONLY */
|
|
|
40 |
SELECT DECODE (oldbom.prod_id,
|
|
|
41 |
NULL, 'A',
|
|
|
42 |
'U'
|
|
|
43 |
) AS change_type, newbom.prod_id, newbom.pk
|
|
|
44 |
FROM (SELECT osc.prod_id,
|
|
|
45 |
nn.node_name || '_' || os.os_name AS pk,
|
|
|
46 |
pv.pkg_id,
|
|
|
47 |
NVL (pv.v_ext, '|LINK_A_NULL|') AS v_ext
|
|
|
48 |
FROM os_contents osc,
|
|
|
49 |
operating_systems os,
|
|
|
50 |
network_nodes nn,
|
|
|
51 |
bom_contents bc,
|
|
|
52 |
package_versions pv
|
|
|
53 |
WHERE osc.os_id = os.os_id
|
|
|
54 |
AND os.node_id = nn.node_id
|
|
|
55 |
AND bc.node_id = nn.node_id
|
|
|
56 |
AND osc.prod_id = pv.pv_id
|
|
|
57 |
AND pv.is_patch IS NULL
|
|
|
58 |
AND bc.bom_id = :bom_id) newbom,
|
|
|
59 |
(SELECT osc.prod_id,
|
|
|
60 |
nn.node_name || '_' || os.os_name AS pk,
|
|
|
61 |
pv.pkg_id,
|
|
|
62 |
NVL (pv.v_ext, '|LINK_A_NULL|') AS v_ext
|
|
|
63 |
FROM os_contents osc,
|
|
|
64 |
operating_systems os,
|
|
|
65 |
network_nodes nn,
|
|
|
66 |
bom_contents bc,
|
|
|
67 |
package_versions pv
|
|
|
68 |
WHERE osc.os_id = os.os_id
|
|
|
69 |
AND os.node_id = nn.node_id
|
|
|
70 |
AND bc.node_id = nn.node_id
|
|
|
71 |
AND osc.prod_id = pv.pv_id
|
|
|
72 |
AND pv.is_patch IS NULL
|
|
|
73 |
AND bc.bom_id = :compare_bom_id) oldbom
|
|
|
74 |
WHERE newbom.pk = oldbom.pk(+)
|
|
|
75 |
--AND newbom.PROD_ID = oldbom.PROD_ID (+)
|
|
|
76 |
AND newbom.pkg_id = oldbom.pkg_id(+)
|
|
|
77 |
AND newbom.v_ext = oldbom.v_ext(+)
|
|
|
78 |
AND ( oldbom.prod_id != newbom.prod_id
|
|
|
79 |
OR oldbom.prod_id IS NULL
|
|
|
80 |
)
|
|
|
81 |
AND newbom.prod_id NOT IN (
|
|
|
82 |
SELECT osc.prod_id
|
|
|
83 |
FROM os_contents osc,
|
|
|
84 |
operating_systems os,
|
|
|
85 |
network_nodes nn,
|
|
|
86 |
bom_contents bc,
|
|
|
87 |
package_versions pv
|
|
|
88 |
WHERE osc.os_id = os.os_id
|
|
|
89 |
AND os.node_id = nn.node_id
|
|
|
90 |
AND bc.node_id = nn.node_id
|
|
|
91 |
AND osc.prod_id = pv.pv_id
|
|
|
92 |
AND pv.is_patch IS NULL
|
|
|
93 |
AND bc.bom_id = :compare_bom_id)
|
|
|
94 |
UNION
|
|
|
95 |
/* REMOVED PRODUCTS ONLY*/
|
|
|
96 |
SELECT 'R' AS change_type, oldbom.prod_id, oldbom.pk
|
|
|
97 |
FROM (SELECT osc.prod_id,
|
|
|
98 |
nn.node_name || '_' || os.os_name AS pk,
|
|
|
99 |
pv.pkg_id,
|
|
|
100 |
NVL (pv.v_ext, '|LINK_A_NULL|') AS v_ext
|
|
|
101 |
FROM os_contents osc,
|
|
|
102 |
operating_systems os,
|
|
|
103 |
network_nodes nn,
|
|
|
104 |
bom_contents bc,
|
|
|
105 |
package_versions pv
|
|
|
106 |
WHERE osc.os_id = os.os_id
|
|
|
107 |
AND os.node_id = nn.node_id
|
|
|
108 |
AND bc.node_id = nn.node_id
|
|
|
109 |
AND osc.prod_id = pv.pv_id
|
|
|
110 |
AND pv.is_patch IS NULL
|
|
|
111 |
AND bc.bom_id = :bom_id) newbom,
|
|
|
112 |
(SELECT osc.prod_id,
|
|
|
113 |
nn.node_name || '_' || os.os_name AS pk,
|
|
|
114 |
pv.pkg_id,
|
|
|
115 |
NVL (pv.v_ext, '|LINK_A_NULL|') AS v_ext
|
|
|
116 |
FROM os_contents osc,
|
|
|
117 |
operating_systems os,
|
|
|
118 |
network_nodes nn,
|
|
|
119 |
bom_contents bc,
|
|
|
120 |
package_versions pv
|
|
|
121 |
WHERE osc.os_id = os.os_id
|
|
|
122 |
AND os.node_id = nn.node_id
|
|
|
123 |
AND bc.node_id = nn.node_id
|
|
|
124 |
AND osc.prod_id = pv.pv_id
|
|
|
125 |
AND pv.is_patch IS NULL
|
|
|
126 |
AND bc.bom_id = :compare_bom_id) oldbom
|
|
|
127 |
WHERE newbom.pk(+) = oldbom.pk
|
|
|
128 |
--AND newbom.PROD_ID (+) = oldbom.PROD_ID
|
|
|
129 |
AND newbom.pkg_id(+) = oldbom.pkg_id
|
|
|
130 |
AND newbom.v_ext(+) = oldbom.v_ext
|
|
|
131 |
AND newbom.pk IS NULL
|
|
|
132 |
UNION
|
|
|
133 |
/* REMOVED PATCHES */
|
|
|
134 |
SELECT 'R' AS change_type, oldbom.prod_id, oldbom.pk
|
|
|
135 |
FROM (SELECT osc.prod_id,
|
|
|
136 |
nn.node_name || '_' || os.os_name AS pk
|
|
|
137 |
FROM os_contents osc,
|
|
|
138 |
operating_systems os,
|
|
|
139 |
network_nodes nn,
|
|
|
140 |
bom_contents bc,
|
|
|
141 |
package_versions pv
|
|
|
142 |
WHERE osc.os_id = os.os_id
|
|
|
143 |
AND os.node_id = nn.node_id
|
|
|
144 |
AND bc.node_id = nn.node_id
|
|
|
145 |
AND osc.prod_id = pv.pv_id
|
|
|
146 |
AND NOT pv.is_patch IS NULL
|
|
|
147 |
AND bc.bom_id = :bom_id) newbom,
|
|
|
148 |
(SELECT osc.prod_id,
|
|
|
149 |
nn.node_name || '_' || os.os_name AS pk
|
|
|
150 |
FROM os_contents osc,
|
|
|
151 |
operating_systems os,
|
|
|
152 |
network_nodes nn,
|
|
|
153 |
bom_contents bc,
|
|
|
154 |
package_versions pv
|
|
|
155 |
WHERE osc.os_id = os.os_id
|
|
|
156 |
AND os.node_id = nn.node_id
|
|
|
157 |
AND bc.node_id = nn.node_id
|
|
|
158 |
AND osc.prod_id = pv.pv_id
|
|
|
159 |
AND NOT pv.is_patch IS NULL
|
|
|
160 |
AND pv.is_obsolete IS NULL
|
|
|
161 |
AND bc.bom_id = :compare_bom_id) oldbom
|
|
|
162 |
WHERE newbom.pk(+) = oldbom.pk
|
|
|
163 |
AND newbom.prod_id(+) = oldbom.prod_id
|
|
|
164 |
AND newbom.pk IS NULL) qry,
|
|
|
165 |
package_versions pv,
|
|
|
166 |
PACKAGES pkg,
|
|
|
167 |
product_details pd
|
|
|
168 |
WHERE qry.prod_id = pv.pv_id
|
|
|
169 |
AND pv.pkg_id = pkg.pkg_id
|
|
|
170 |
AND pd.prod_id(+) = qry.prod_id
|
|
|
171 |
AND (pd.is_rejected IS NULL OR pd.is_rejected = 'N')
|
|
|
172 |
MINUS
|
|
|
173 |
SELECT DISTINCT pv.pv_id AS prod_id, pkg.pkg_name, pv.pkg_version,
|
|
|
174 |
pv.is_patch, pd.IS_REJECTED, pkg.pkg_id
|
|
|
175 |
FROM bom_contents bc,
|
|
|
176 |
operating_systems os,
|
|
|
177 |
os_contents osc,
|
|
|
178 |
PACKAGES pkg,
|
|
|
179 |
package_versions pv,
|
|
|
180 |
product_details pd
|
|
|
181 |
WHERE bc.bom_id = :current_bom
|
|
|
182 |
AND os.node_id = bc.node_id
|
|
|
183 |
AND osc.os_id = os.os_id
|
|
|
184 |
AND osc.prod_id = pv.pv_id
|
|
|
185 |
AND pkg.pkg_id = pv.pkg_id
|
|
|
186 |
and pd.PROD_ID(+) = osc.PROD_ID
|
|
|
187 |
ORDER BY pkg_name asc
|