Subversion Repositories DevTools

Rev

Rev 5513 | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

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

            166
            
 ORDER BY UPPER(pkg.PKG_NAME)       

          

          

            -
            
 

            167
            
 

          

          

            -
            
 

            168