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 */
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*/
119 
		SELECT 'R' AS CHANGE_TYPE, oldbom.PROD_ID, oldbom.PK
120 
		  FROM (
121 
		  		SELECT osc.PROD_ID,
122 
					   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,
123 
					   pv.PKG_ID,
124 
					   NVL( pv.V_EXT, '|LINK_A_NULL|' ) AS V_EXT
125 
				  FROM OS_CONTENTS osc,
126 
				  	   OPERATING_SYSTEMS os,
127 
					   NETWORK_NODES nn,
128 
					   BOM_CONTENTS bc,
129 
					   PACKAGE_VERSIONS pv
130 
				 WHERE osc.OS_ID = os.OS_ID
131 
				   AND os.NODE_ID = nn.NODE_ID
132 
				   AND bc.NODE_ID = nn.NODE_ID
133 
				   AND osc.PROD_ID = pv.PV_ID
134 
				   AND pv.IS_PATCH IS NULL
135 
				   AND bc.BOM_ID = :BOM_ID
136 
		  	   ) newbom,
137 
			   (
138 
			   	SELECT osc.PROD_ID,
139 
					   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,
140 
					   pv.PKG_ID,
141 
					   NVL( pv.V_EXT, '|LINK_A_NULL|' ) AS V_EXT
142 
				  FROM OS_CONTENTS osc,
143 
				  	   OPERATING_SYSTEMS os,
144 
					   NETWORK_NODES nn,
145 
					   BOM_CONTENTS bc,
146 
					   PACKAGE_VERSIONS pv
147 
				 WHERE osc.OS_ID = os.OS_ID
148 
				   AND os.NODE_ID = nn.NODE_ID
149 
				   AND bc.NODE_ID = nn.NODE_ID
150 
				   AND osc.PROD_ID = pv.PV_ID
151 
				   AND pv.IS_PATCH IS NULL
152 
				   AND bc.BOM_ID = :COMPARE_BOM_ID
153 
			   ) oldbom
154 
		 WHERE newbom.PK (+) = oldbom.PK
155 
		   --AND newbom.PROD_ID (+) = oldbom.PROD_ID
156 
		   AND newbom.PKG_ID (+) = oldbom.PKG_ID
157 
		   AND newbom.V_EXT (+) = oldbom.V_EXT
158 
		   AND newbom.PK IS NULL
159 
 
160 
		UNION
161 
 
162 
		/* REMOVED PATCHES */
163 
		SELECT 'R' AS CHANGE_TYPE, oldbom.PROD_ID, oldbom.PK
164 
		  FROM (
165 
		  		SELECT osc.PROD_ID,
166 
					   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK
167 
				  FROM OS_CONTENTS osc,
168 
				  	   OPERATING_SYSTEMS os,
169 
					   NETWORK_NODES nn,
170 
					   BOM_CONTENTS bc,
171 
					   PACKAGE_VERSIONS pv
172 
				 WHERE osc.OS_ID = os.OS_ID
173 
				   AND os.NODE_ID = nn.NODE_ID
174 
				   AND bc.NODE_ID = nn.NODE_ID
175 
				   AND osc.PROD_ID = pv.PV_ID
176 
				   AND NOT pv.IS_PATCH IS NULL
177 
				   AND bc.BOM_ID = :BOM_ID
178 
		  	   ) newbom,
179 
			   (
180 
			   	SELECT osc.PROD_ID,
181 
					   nn.NODE_NAME ||'_'|| os.OS_NAME AS PK
182 
				  FROM OS_CONTENTS osc,
183 
				  	   OPERATING_SYSTEMS os,
184 
					   NETWORK_NODES nn,
185 
					   BOM_CONTENTS bc,
186 
					   PACKAGE_VERSIONS pv
187 
				 WHERE osc.OS_ID = os.OS_ID
188 
				   AND os.NODE_ID = nn.NODE_ID
189 
				   AND bc.NODE_ID = nn.NODE_ID
190 
				   AND osc.PROD_ID = pv.PV_ID
191 
				   AND NOT pv.IS_PATCH IS NULL
192 
				   AND bc.BOM_ID = :COMPARE_BOM_ID
193 
			   ) oldbom
194 
		 WHERE newbom.PK (+) = oldbom.PK
195 
		   AND newbom.PROD_ID (+) = oldbom.PROD_ID
196 
		   AND newbom.PK IS NULL
197 
 
198 
       ) qry,
199 
	   PACKAGE_VERSIONS pv,
200 
	   PACKAGES pkg,
201 
	   PRODUCT_DETAILS pd
202 
 WHERE qry.PROD_ID = pv.PV_ID
203 
   AND pv.PKG_ID = pkg.PKG_ID
204 
   AND pd.PROD_ID (+) = qry.PROD_ID
205 
 ORDER BY UPPER(pkg.PKG_NAME)