Subversion Repositories DevTools

Rev

Rev 5633 | Go to most recent revision | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

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

            157
            
 ORDER BY UPPER(pkg.PKG_NAME)       

          

          

            -
            
 

            158
            
 

          

          

            -
            
 

            159