Subversion Repositories DevTools

Rev

Details | Last modification | View Log | RSS feed

Rev Author Line No. Line
13 rsolanki 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 
  FROM (
18 
 
19 
  		/* ADDED PATCHES ONLY */
20 
		SELECT 'A' AS CHANGE_TYPE, newbom.PROD_ID, newbom.PK
21 
		  FROM (
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 = :BOM_ID
35 
		  	   ) newbom,
36 
			   (
37 
			   	SELECT osc.PROD_ID,
38 
					   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK
39 
				  FROM OS_CONTENTS osc,
40 
				  	   OPERATING_SYSTEMS os,
41 
					   NETWORK_NODES nn,
42 
					   BOM_CONTENTS bc,
43 
					   PACKAGE_VERSIONS pv
44 
				 WHERE osc.OS_ID = os.OS_ID
45 
				   AND os.NODE_ID = nn.NODE_ID
46 
				   AND bc.NODE_ID = nn.NODE_ID
47 
				   AND osc.PROD_ID = pv.PV_ID
48 
				   AND NOT pv.IS_PATCH IS NULL
49 
				   AND bc.BOM_ID = :COMPARE_BOM_ID
50 
			   ) oldbom
51 
		 WHERE newbom.PK = oldbom.PK (+)
52 
		   AND newbom.PROD_ID = oldbom.PROD_ID (+)
53 
		   AND oldbom.PK IS NULL
54 
 
55 
		UNION
56 
 
57 
		/* ADDED PRODUCTS ONLY */
58 
		SELECT 'A' AS CHANGE_TYPE,
59 
			   newbom.PROD_ID, newbom.PK
60 
		  FROM (
61 
		  		SELECT osc.PROD_ID,
62 
					   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,
63 
					   pv.PKG_ID,
64 
					   NVL( pv.V_EXT, '|LINK_A_NULL|' ) AS V_EXT
65 
				  FROM OS_CONTENTS osc,
66 
				  	   OPERATING_SYSTEMS os,
67 
					   NETWORK_NODES nn,
68 
					   BOM_CONTENTS bc,
69 
					   PACKAGE_VERSIONS pv
70 
				 WHERE osc.OS_ID = os.OS_ID
71 
				   AND os.NODE_ID = nn.NODE_ID
72 
				   AND bc.NODE_ID = nn.NODE_ID
73 
				   AND osc.PROD_ID = pv.PV_ID
74 
				   AND pv.IS_PATCH IS NULL
75 
				   AND bc.BOM_ID = :BOM_ID
76 
		  	   ) newbom,
77 
			   (
78 
			   	SELECT osc.PROD_ID,
79 
					   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,
80 
					   pv.PKG_ID,
81 
					   NVL( pv.V_EXT, '|LINK_A_NULL|' ) AS V_EXT
82 
				  FROM OS_CONTENTS osc,
83 
				  	   OPERATING_SYSTEMS os,
84 
					   NETWORK_NODES nn,
85 
					   BOM_CONTENTS bc,
86 
					   PACKAGE_VERSIONS pv
87 
				 WHERE osc.OS_ID = os.OS_ID
88 
				   AND os.NODE_ID = nn.NODE_ID
89 
				   AND bc.NODE_ID = nn.NODE_ID
90 
				   AND osc.PROD_ID = pv.PV_ID
91 
				   AND pv.IS_PATCH IS NULL
92 
				   AND bc.BOM_ID = :COMPARE_BOM_ID
93 
			   ) oldbom
94 
		 WHERE newbom.PK = oldbom.PK (+)
95 
		   --AND newbom.PROD_ID = oldbom.PROD_ID (+)
96 
		   AND newbom.PKG_ID  = oldbom.PKG_ID (+)
97 
		   AND newbom.V_EXT  = oldbom.V_EXT (+)
98 
		   AND oldbom.PROD_ID IS NULL
99 
 
100 
 
101 
		UNION
102 
 
103 
		/*UPDATED PRODUCTS ONLY*/
104 
 
105 
		SELECT 'U' AS CHANGE_TYPE,
106 
			   osc.PROD_ID,
107 
			   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK
108 
		  FROM OS_CONTENTS osc,
109 
		  	   OPERATING_SYSTEMS os,
110 
			   NETWORK_NODES nn,
111 
			   BOM_CONTENTS bc,
112 
			   PACKAGE_VERSIONS pv
113 
		 WHERE osc.OS_ID = os.OS_ID
114 
		   AND os.NODE_ID = nn.NODE_ID
115 
		   AND bc.NODE_ID = nn.NODE_ID
116 
		   AND osc.PROD_ID = pv.PV_ID
117 
		   AND pv.IS_PATCH IS NULL
118 
		   AND bc.BOM_ID = :BOM_ID
119 
		   AND osc.PROD_ID NOT IN
120 
			   (
121 
		   	   SELECT osc.PROD_ID
122 
			   FROM OS_CONTENTS osc,
123 
			  	    OPERATING_SYSTEMS os,
124 
					NETWORK_NODES nn,
125 
					BOM_CONTENTS bc,
126 
					PACKAGE_VERSIONS pv
127 
			  WHERE osc.OS_ID = os.OS_ID
128 
				AND os.NODE_ID = nn.NODE_ID
129 
				AND bc.NODE_ID = nn.NODE_ID
130 
				AND osc.PROD_ID = pv.PV_ID
131 
				AND pv.IS_PATCH IS NULL
132 
				AND bc.BOM_ID = :COMPARE_BOM_ID
133 
			   )
134 
 
135 
		UNION
136 
 
137 
		/* REMOVED PRODUCTS ONLY*/
138 
		SELECT 'R' AS CHANGE_TYPE, oldbom.PROD_ID, oldbom.PK
139 
		  FROM (
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 
		  	   ) newbom,
156 
			   (
157 
			   	SELECT osc.PROD_ID,
158 
					   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,
159 
					   pv.PKG_ID,
160 
					   NVL( pv.V_EXT, '|LINK_A_NULL|' ) AS V_EXT
161 
				  FROM OS_CONTENTS osc,
162 
				  	   OPERATING_SYSTEMS os,
163 
					   NETWORK_NODES nn,
164 
					   BOM_CONTENTS bc,
165 
					   PACKAGE_VERSIONS pv
166 
				 WHERE osc.OS_ID = os.OS_ID
167 
				   AND os.NODE_ID = nn.NODE_ID
168 
				   AND bc.NODE_ID = nn.NODE_ID
169 
				   AND osc.PROD_ID = pv.PV_ID
170 
				   AND pv.IS_PATCH IS NULL
171 
				   AND bc.BOM_ID = :COMPARE_BOM_ID
172 
			   ) oldbom
173 
		 WHERE newbom.PK (+) = oldbom.PK
174 
		   --AND newbom.PROD_ID (+) = oldbom.PROD_ID
175 
		   AND newbom.PKG_ID (+) = oldbom.PKG_ID
176 
		   AND newbom.V_EXT (+) = oldbom.V_EXT
177 
		   AND newbom.PK IS NULL
178 
 
179 
		UNION
180 
 
181 
		/* REMOVED PATCHES */
182 
		SELECT 'R' AS CHANGE_TYPE, oldbom.PROD_ID, oldbom.PK
183 
		  FROM (
184 
		  		SELECT osc.PROD_ID,
185 
					   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK
186 
				  FROM OS_CONTENTS osc,
187 
				  	   OPERATING_SYSTEMS os,
188 
					   NETWORK_NODES nn,
189 
					   BOM_CONTENTS bc,
190 
					   PACKAGE_VERSIONS pv
191 
				 WHERE osc.OS_ID = os.OS_ID
192 
				   AND os.NODE_ID = nn.NODE_ID
193 
				   AND bc.NODE_ID = nn.NODE_ID
194 
				   AND osc.PROD_ID = pv.PV_ID
195 
				   AND NOT pv.IS_PATCH IS NULL
196 
				   AND bc.BOM_ID = :BOM_ID
197 
		  	   ) newbom,
198 
			   (
199 
			   	SELECT osc.PROD_ID,
200 
					   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK
201 
				  FROM OS_CONTENTS osc,
202 
				  	   OPERATING_SYSTEMS os,
203 
					   NETWORK_NODES nn,
204 
					   BOM_CONTENTS bc,
205 
					   PACKAGE_VERSIONS pv
206 
				 WHERE osc.OS_ID = os.OS_ID
207 
				   AND os.NODE_ID = nn.NODE_ID
208 
				   AND bc.NODE_ID = nn.NODE_ID
209 
				   AND osc.PROD_ID = pv.PV_ID
210 
				   AND NOT pv.IS_PATCH IS NULL
211 
				   AND bc.BOM_ID = :COMPARE_BOM_ID
212 
			   ) oldbom
213 
		 WHERE newbom.PK (+) = oldbom.PK
214 
		   AND newbom.PROD_ID (+) = oldbom.PROD_ID
215 
		   AND newbom.PK IS NULL
216 
 
217 
       ) qry,
218 
	   PACKAGE_VERSIONS pv,
219 
	   PACKAGES pkg,
220 
	   PRODUCT_DETAILS pd
221 
 WHERE qry.PROD_ID = pv.PV_ID
222 
   AND pv.PKG_ID = pkg.PKG_ID
223 
   AND pd.PROD_ID (+) = qry.PROD_ID
224 
 ORDER BY UPPER(pkg.PKG_NAME)