Subversion Repositories DevTools

Rev

Rev 5513 | Rev 5634 | Go to most recent revision | Details | Compare with Previous | 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 */
15 rsolanki 58 
		SELECT DECODE( oldbom.PROD_ID,
59 
					   NULL, 'A', 'U' ) AS CHANGE_TYPE,
13 rsolanki 60 
			   newbom.PROD_ID, newbom.PK
61 
		  FROM (
62 
		  		SELECT osc.PROD_ID,
63 
					   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,
64 
					   pv.PKG_ID,
65 
					   NVL( pv.V_EXT, '|LINK_A_NULL|' ) AS V_EXT
66 
				  FROM OS_CONTENTS osc,
67 
				  	   OPERATING_SYSTEMS os,
68 
					   NETWORK_NODES nn,
69 
					   BOM_CONTENTS bc,
70 
					   PACKAGE_VERSIONS pv
71 
				 WHERE osc.OS_ID = os.OS_ID
72 
				   AND os.NODE_ID = nn.NODE_ID
73 
				   AND bc.NODE_ID = nn.NODE_ID
74 
				   AND osc.PROD_ID = pv.PV_ID
75 
				   AND pv.IS_PATCH IS NULL
76 
				   AND bc.BOM_ID = :BOM_ID
77 
		  	   ) newbom,
78 
			   (
79 
			   	SELECT osc.PROD_ID,
80 
					   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,
81 
					   pv.PKG_ID,
82 
					   NVL( pv.V_EXT, '|LINK_A_NULL|' ) AS V_EXT
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 
			   ) oldbom
95 
		 WHERE newbom.PK = oldbom.PK (+)
96 
		   --AND newbom.PROD_ID = oldbom.PROD_ID (+)
97 
		   AND newbom.PKG_ID  = oldbom.PKG_ID (+)
98 
		   AND newbom.V_EXT  = oldbom.V_EXT (+)
15 rsolanki 99 
		   AND ( oldbom.PROD_ID != newbom.PROD_ID  OR oldbom.PROD_ID IS NULL )
100 
		   AND newbom.PROD_ID NOT IN
101 
		   (
13 rsolanki 102 
		   	   SELECT osc.PROD_ID
103 
			   FROM OS_CONTENTS osc,
104 
			  	    OPERATING_SYSTEMS os,
105 
					NETWORK_NODES nn,
106 
					BOM_CONTENTS bc,
107 
					PACKAGE_VERSIONS pv
108 
			  WHERE osc.OS_ID = os.OS_ID
109 
				AND os.NODE_ID = nn.NODE_ID
110 
				AND bc.NODE_ID = nn.NODE_ID
111 
				AND osc.PROD_ID = pv.PV_ID
112 
				AND pv.IS_PATCH IS NULL
113 
				AND bc.BOM_ID = :COMPARE_BOM_ID
15 rsolanki 114 
		  )
115 
 
13 rsolanki 116 
		UNION
117 
 
118 
		/* REMOVED PRODUCTS ONLY*/
5633 dpurdie 119 
		--SELECT 'R' AS CHANGE_TYPE, oldbom.PROD_ID, oldbom.PK
120 
    SELECT DECODE( newbom.PROD_ID,
121 
					   NULL, 'R', 'U' ) AS CHANGE_TYPE,
122 
			   oldbom.PROD_ID, oldbom.PK
13 rsolanki 123 
		  FROM (
124 
		  		SELECT osc.PROD_ID,
125 
					   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,
126 
					   pv.PKG_ID,
127 
					   NVL( pv.V_EXT, '|LINK_A_NULL|' ) AS V_EXT
128 
				  FROM OS_CONTENTS osc,
129 
				  	   OPERATING_SYSTEMS os,
130 
					   NETWORK_NODES nn,
131 
					   BOM_CONTENTS bc,
132 
					   PACKAGE_VERSIONS pv
133 
				 WHERE osc.OS_ID = os.OS_ID
134 
				   AND os.NODE_ID = nn.NODE_ID
135 
				   AND bc.NODE_ID = nn.NODE_ID
136 
				   AND osc.PROD_ID = pv.PV_ID
137 
				   AND pv.IS_PATCH IS NULL
138 
				   AND bc.BOM_ID = :BOM_ID
139 
		  	   ) newbom,
140 
			   (
141 
			   	SELECT osc.PROD_ID,
142 
					   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,
143 
					   pv.PKG_ID,
144 
					   NVL( pv.V_EXT, '|LINK_A_NULL|' ) AS V_EXT
145 
				  FROM OS_CONTENTS osc,
146 
				  	   OPERATING_SYSTEMS os,
147 
					   NETWORK_NODES nn,
148 
					   BOM_CONTENTS bc,
149 
					   PACKAGE_VERSIONS pv
150 
				 WHERE osc.OS_ID = os.OS_ID
151 
				   AND os.NODE_ID = nn.NODE_ID
152 
				   AND bc.NODE_ID = nn.NODE_ID
153 
				   AND osc.PROD_ID = pv.PV_ID
154 
				   AND pv.IS_PATCH IS NULL
155 
				   AND bc.BOM_ID = :COMPARE_BOM_ID
156 
			   ) oldbom
157 
		 WHERE newbom.PK (+) = oldbom.PK
158 
		   --AND newbom.PROD_ID (+) = oldbom.PROD_ID
159 
		   AND newbom.PKG_ID (+) = oldbom.PKG_ID
160 
		   AND newbom.V_EXT (+) = oldbom.V_EXT
5633 dpurdie 161 
		   --AND newbom.PK IS NULL
162 
       AND ( oldbom.PROD_ID != newbom.PROD_ID  OR newbom.PROD_ID IS NULL )
163 
  	   AND oldbom.PROD_ID NOT IN
164 
		   (
165 
		   	   SELECT osc.PROD_ID
166 
			   FROM OS_CONTENTS osc,
167 
			  	    OPERATING_SYSTEMS os,
168 
					NETWORK_NODES nn,
169 
					BOM_CONTENTS bc,
170 
					PACKAGE_VERSIONS pv
171 
			  WHERE osc.OS_ID = os.OS_ID
172 
				AND os.NODE_ID = nn.NODE_ID
173 
				AND bc.NODE_ID = nn.NODE_ID
174 
				AND osc.PROD_ID = pv.PV_ID
175 
				AND pv.IS_PATCH IS NULL
176 
				AND bc.BOM_ID = :BOM_ID
177 
		  )
178 
 
13 rsolanki 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)