Subversion Repositories DevTools

Rev

Details | Last modification | View Log | RSS feed

Rev Author Line No. Line
64 jtweddle 1 
/* Bom Compare */
2 
SELECT DISTINCT
3 
 
4 
       pv.PV_ID AS PROD_ID,
5 
	   pkg.PKG_NAME,
6 
	   pv.PKG_VERSION,
7 
	   qry.CHANGE_TYPE,
8 
	   pd.IS_REJECTED,
9 
	   ( CASE
10 
	     WHEN qry.CHANGE_TYPE = 'A' THEN
11 
	   		'LIMG_ADDED'
12 
		 WHEN qry.CHANGE_TYPE = 'U' THEN
13 
			'LIMG_UPDATED'
14 
		 ELSE
15 
		 	'LIMG_REMOVED'
16 
		 END ) AS DIFF_ICON,
17 
	pkg.PKG_ID,
18 
	pv.IS_PATCH
19 
  FROM (
20 
 
21 
  		/* ADDED PATCHES ONLY */
22 
		SELECT 'A' AS CHANGE_TYPE, newbom.PROD_ID, newbom.PK
23 
		  FROM (
24 
		  		SELECT osc.PROD_ID,
25 
					   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK
26 
				  FROM OS_CONTENTS osc,
27 
				  	   OPERATING_SYSTEMS os,
28 
					   NETWORK_NODES nn,
29 
					   BOM_CONTENTS bc,
30 
					   PACKAGE_VERSIONS pv
31 
				 WHERE osc.OS_ID = os.OS_ID
32 
				   AND os.NODE_ID = nn.NODE_ID
33 
				   AND bc.NODE_ID = nn.NODE_ID
34 
				   AND osc.PROD_ID = pv.PV_ID
35 
				   AND NOT pv.IS_PATCH IS NULL
36 
				   AND bc.BOM_ID = :COMPARE_BOM_ID
37 
		  	   ) newbom,
38 
			   (
39 
			   	SELECT osc.PROD_ID,
40 
					   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK
41 
				  FROM OS_CONTENTS osc,
42 
				  	   OPERATING_SYSTEMS os,
43 
					   NETWORK_NODES nn,
44 
					   BOM_CONTENTS bc,
45 
					   PACKAGE_VERSIONS pv
46 
				 WHERE osc.OS_ID = os.OS_ID
47 
				   AND os.NODE_ID = nn.NODE_ID
48 
				   AND bc.NODE_ID = nn.NODE_ID
49 
				   AND osc.PROD_ID = pv.PV_ID
50 
				   AND NOT pv.IS_PATCH IS NULL
51 
				   AND bc.BOM_ID = :BOM_ID
52 
			   ) oldbom
53 
		 WHERE newbom.PK = oldbom.PK (+)
54 
		   AND newbom.PROD_ID = oldbom.PROD_ID (+)
55 
		   AND oldbom.PK IS NULL
56 
 
57 
		UNION
58 
 
59 
		/* ADDED PRODUCTS ONLY */
60 
		SELECT DECODE( oldbom.PROD_ID,
61 
					   NULL, 'A', 'U' ) AS CHANGE_TYPE,
62 
			   newbom.PROD_ID, newbom.PK
63 
		  FROM (
64 
		  		SELECT osc.PROD_ID,
65 
					   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,
66 
					   pv.PKG_ID,
67 
					   NVL( pv.V_EXT, '|LINK_A_NULL|' ) AS V_EXT
68 
				  FROM OS_CONTENTS osc,
69 
				  	   OPERATING_SYSTEMS os,
70 
					   NETWORK_NODES nn,
71 
					   BOM_CONTENTS bc,
72 
					   PACKAGE_VERSIONS pv
73 
				 WHERE osc.OS_ID = os.OS_ID
74 
				   AND os.NODE_ID = nn.NODE_ID
75 
				   AND bc.NODE_ID = nn.NODE_ID
76 
				   AND osc.PROD_ID = pv.PV_ID
77 
				   AND pv.IS_PATCH IS NULL
78 
				   AND bc.BOM_ID = :COMPARE_BOM_ID
79 
		  	   ) newbom,
80 
			   (
81 
			   	SELECT osc.PROD_ID,
82 
					   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,
83 
					   pv.PKG_ID,
84 
					   NVL( pv.V_EXT, '|LINK_A_NULL|' ) AS V_EXT
85 
				  FROM OS_CONTENTS osc,
86 
				  	   OPERATING_SYSTEMS os,
87 
					   NETWORK_NODES nn,
88 
					   BOM_CONTENTS bc,
89 
					   PACKAGE_VERSIONS pv
90 
				 WHERE osc.OS_ID = os.OS_ID
91 
				   AND os.NODE_ID = nn.NODE_ID
92 
				   AND bc.NODE_ID = nn.NODE_ID
93 
				   AND osc.PROD_ID = pv.PV_ID
94 
				   AND pv.IS_PATCH IS NULL
95 
				   AND bc.BOM_ID = :BOM_ID
96 
			   ) oldbom
97 
		 WHERE newbom.PK = oldbom.PK (+)
98 
		   --AND newbom.PROD_ID = oldbom.PROD_ID (+)
99 
		   AND newbom.PKG_ID  = oldbom.PKG_ID (+)
100 
		   AND newbom.V_EXT  = oldbom.V_EXT (+)
101 
		   AND ( oldbom.PROD_ID != newbom.PROD_ID  OR oldbom.PROD_ID IS NULL )
102 
		   AND newbom.PROD_ID NOT IN
103 
		   (
104 
		   	   SELECT osc.PROD_ID
105 
			   FROM OS_CONTENTS osc,
106 
			  	    OPERATING_SYSTEMS os,
107 
					NETWORK_NODES nn,
108 
					BOM_CONTENTS bc,
109 
					PACKAGE_VERSIONS pv
110 
			  WHERE osc.OS_ID = os.OS_ID
111 
				AND os.NODE_ID = nn.NODE_ID
112 
				AND bc.NODE_ID = nn.NODE_ID
113 
				AND osc.PROD_ID = pv.PV_ID
114 
				AND pv.IS_PATCH IS NULL
115 
				AND bc.BOM_ID = :BOM_ID
116 
		  )
117 
 
118 
		UNION
119 
 
120 
		/* REMOVED PRODUCTS ONLY*/
121 
		SELECT 'R' AS CHANGE_TYPE, oldbom.PROD_ID, oldbom.PK
122 
		  FROM (
123 
		  		SELECT osc.PROD_ID,
124 
					   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,
125 
					   pv.PKG_ID,
126 
					   NVL( pv.V_EXT, '|LINK_A_NULL|' ) AS V_EXT
127 
				  FROM OS_CONTENTS osc,
128 
				  	   OPERATING_SYSTEMS os,
129 
					   NETWORK_NODES nn,
130 
					   BOM_CONTENTS bc,
131 
					   PACKAGE_VERSIONS pv
132 
				 WHERE osc.OS_ID = os.OS_ID
133 
				   AND os.NODE_ID = nn.NODE_ID
134 
				   AND bc.NODE_ID = nn.NODE_ID
135 
				   AND osc.PROD_ID = pv.PV_ID
136 
				   AND pv.IS_PATCH IS NULL
137 
				   AND bc.BOM_ID = :COMPARE_BOM_ID
138 
		  	   ) newbom,
139 
			   (
140 
			   	SELECT osc.PROD_ID,
141 
					   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,
142 
					   pv.PKG_ID,
143 
					   NVL( pv.V_EXT, '|LINK_A_NULL|' ) AS V_EXT
144 
				  FROM OS_CONTENTS osc,
145 
				  	   OPERATING_SYSTEMS os,
146 
					   NETWORK_NODES nn,
147 
					   BOM_CONTENTS bc,
148 
					   PACKAGE_VERSIONS pv
149 
				 WHERE osc.OS_ID = os.OS_ID
150 
				   AND os.NODE_ID = nn.NODE_ID
151 
				   AND bc.NODE_ID = nn.NODE_ID
152 
				   AND osc.PROD_ID = pv.PV_ID
153 
				   AND pv.IS_PATCH IS NULL
154 
				   AND bc.BOM_ID = :BOM_ID
155 
			   ) oldbom
156 
		 WHERE newbom.PK (+) = oldbom.PK
157 
		   --AND newbom.PROD_ID (+) = oldbom.PROD_ID
158 
		   AND newbom.PKG_ID (+) = oldbom.PKG_ID
159 
		   AND newbom.V_EXT (+) = oldbom.V_EXT
160 
		   AND newbom.PK IS NULL
161 
 
162 
		UNION
163 
 
164 
		/* REMOVED PATCHES */
165 
		SELECT 'R' AS CHANGE_TYPE, oldbom.PROD_ID, oldbom.PK
166 
		  FROM (
167 
		  		SELECT osc.PROD_ID,
168 
					   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK
169 
				  FROM OS_CONTENTS osc,
170 
				  	   OPERATING_SYSTEMS os,
171 
					   NETWORK_NODES nn,
172 
					   BOM_CONTENTS bc,
173 
					   PACKAGE_VERSIONS pv
174 
				 WHERE osc.OS_ID = os.OS_ID
175 
				   AND os.NODE_ID = nn.NODE_ID
176 
				   AND bc.NODE_ID = nn.NODE_ID
177 
				   AND osc.PROD_ID = pv.PV_ID
178 
				   AND NOT pv.IS_PATCH IS NULL
179 
				   AND bc.BOM_ID = :COMPARE_BOM_ID
180 
		  	   ) newbom,
181 
			   (
182 
			   	SELECT osc.PROD_ID,
183 
					   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK
184 
				  FROM OS_CONTENTS osc,
185 
				  	   OPERATING_SYSTEMS os,
186 
					   NETWORK_NODES nn,
187 
					   BOM_CONTENTS bc,
188 
					   PACKAGE_VERSIONS pv
189 
				 WHERE osc.OS_ID = os.OS_ID
190 
				   AND os.NODE_ID = nn.NODE_ID
191 
				   AND bc.NODE_ID = nn.NODE_ID
192 
				   AND osc.PROD_ID = pv.PV_ID
193 
				   AND NOT pv.IS_PATCH IS NULL
194 
				   AND bc.BOM_ID = :BOM_ID
195 
			   ) oldbom
196 
		 WHERE newbom.PK (+) = oldbom.PK
197 
		   AND newbom.PROD_ID (+) = oldbom.PROD_ID
198 
		   AND newbom.PK IS NULL
199 
 
200 
       ) qry,
201 
	   PACKAGE_VERSIONS pv,
202 
	   PACKAGES pkg,
203 
	   PRODUCT_DETAILS pd
204 
 WHERE qry.PROD_ID = pv.PV_ID
205 
   AND pv.PKG_ID = pkg.PKG_ID
206 
   AND pd.PROD_ID (+) = qry.PROD_ID
207 
 ORDER BY UPPER(pkg.PKG_NAME)