Subversion Repositories DevTools

Rev

Rev 5634 | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
13 rsolanki 1 
/* Bom Compare */
2 
SELECT DISTINCT
5634 dpurdie 3 
 
13 rsolanki 4 
       pv.PV_ID AS PROD_ID,
5634 dpurdie 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
13 rsolanki 17 
  FROM (
18 
 
5634 dpurdie 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, Updated and Removed Packages
5640 dpurdie 58 
SELECT
59 
       CASE WHEN PROD_ID is NULL then 'R' WHEN OPROD_ID is NULL THEN 'A' ELSE 'U' END as CHANGE_TYPE,
5634 dpurdie 60 
       NVL2(prod_id, prod_id, oprod_id) as prod_id ,
61 
       NVL2(pk, pk, opk)  as pk
62 
       from (
63 
       WITH
64 
        newbom as
65 
            (
5640 dpurdie 66 
                SELECT * FROM (
5634 dpurdie 67 
                SELECT
68 
                   osc.PROD_ID,
69 
                   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,
5640 dpurdie 70 
                   nn.NODE_NAME ||'_'|| os.OS_NAME || '_'  || osc.PROD_ID as FK,
5634 dpurdie 71 
                   pv.PKG_ID,
72 
                   NVL( pv.V_EXT, '|LINK_A_NULL|' ) AS V_EXT,
73 
                   REPLACE(osc.product_comments,CHR(13)||CHR(10), '-') as COMMENTS
74 
                FROM OS_CONTENTS osc,
75 
                     OPERATING_SYSTEMS os,
76 
                     NETWORK_NODES nn,
77 
                     BOM_CONTENTS bc,
78 
                     PACKAGE_VERSIONS pv
79 
               WHERE osc.OS_ID = os.OS_ID
80 
                 AND os.NODE_ID = nn.NODE_ID
81 
                 AND bc.NODE_ID = nn.NODE_ID
82 
                 AND osc.PROD_ID = pv.PV_ID
83 
                 AND pv.IS_PATCH IS NULL
84 
                 AND bc.BOM_ID = :BOM_ID
5640 dpurdie 85 
                 )
86 
                 WHERE FK NOT IN (
87 
                                 SELECT
88 
                   nn.NODE_NAME ||'_'|| os.OS_NAME || '_'  || osc.PROD_ID as FK
89 
                FROM OS_CONTENTS osc,
90 
                     OPERATING_SYSTEMS os,
91 
                     NETWORK_NODES nn,
92 
                     BOM_CONTENTS bc,
93 
                     PACKAGE_VERSIONS pv
94 
               WHERE osc.OS_ID = os.OS_ID
95 
                 AND os.NODE_ID = nn.NODE_ID
96 
                 AND bc.NODE_ID = nn.NODE_ID
97 
                 AND osc.PROD_ID = pv.PV_ID
98 
                 AND pv.IS_PATCH IS NULL
99 
                 AND bc.BOM_ID = :COMPARE_BOM_ID
100 
                 )
101 
 
5634 dpurdie 102 
                 ),
103 
       oldbom as
104 
               (
5640 dpurdie 105 
               SELECT * from (
5634 dpurdie 106 
                SELECT osc.PROD_ID,
107 
                   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,
5640 dpurdie 108 
                   nn.NODE_NAME ||'_'|| os.OS_NAME || '_'  || osc.PROD_ID as FK,
5634 dpurdie 109 
                   pv.PKG_ID,
110 
                   NVL( pv.V_EXT, '|LINK_A_NULL|' ) AS V_EXT,
111 
                   REPLACE(osc.product_comments,CHR(13)||CHR(10),'-') as COMMENTS
112 
                FROM OS_CONTENTS osc,
113 
                     OPERATING_SYSTEMS os,
114 
                     NETWORK_NODES nn,
115 
                     BOM_CONTENTS bc,
116 
                     PACKAGE_VERSIONS pv
117 
               WHERE osc.OS_ID = os.OS_ID
118 
                 AND os.NODE_ID = nn.NODE_ID
119 
                 AND bc.NODE_ID = nn.NODE_ID
120 
                 AND osc.PROD_ID = pv.PV_ID
121 
                 AND pv.IS_PATCH IS NULL
5640 dpurdie 122 
                 AND bc.BOM_ID = :COMPARE_BOM_ID
123 
                 )
124 
                 WHERE FK NOT IN (
125 
                 SELECT
126 
                   nn.NODE_NAME ||'_'|| os.OS_NAME || '_'  || osc.PROD_ID as FK
127 
                FROM OS_CONTENTS osc,
128 
                     OPERATING_SYSTEMS os,
129 
                     NETWORK_NODES nn,
130 
                     BOM_CONTENTS bc,
131 
                     PACKAGE_VERSIONS pv
132 
               WHERE osc.OS_ID = os.OS_ID
133 
                 AND os.NODE_ID = nn.NODE_ID
134 
                 AND bc.NODE_ID = nn.NODE_ID
135 
                 AND osc.PROD_ID = pv.PV_ID
136 
                 AND pv.IS_PATCH IS NULL
137 
                 AND bc.BOM_ID = :BOM_ID
138 
                 )
139 
 
5634 dpurdie 140 
               )
5640 dpurdie 141 
 
5634 dpurdie 142 
        SELECT newbom.PROD_ID, newbom.PK, newbom.pkg_id, newbom.comments,
143 
               oldbom.PROD_ID as OPROD_ID, oldbom.PK as OPK, oldbom.pkg_id as OPKG_ID, oldbom.comments as OCOMMENTS
144 
               FROM newbom
5640 dpurdie 145 
               full outer join oldbom on newbom.PK = oldbom.PK
146 
 
147 
 
5634 dpurdie 148 
      ) where
149 
            PROD_ID is NULL
150 
            or OPKG_ID is NULL
151 
            or PROD_ID != OPROD_ID
152 
            or COMMENTS != OCOMMENTS
5633 dpurdie 153 
 
5640 dpurdie 154 
 
5634 dpurdie 155 
        UNION
156 
 
157 
        /* REMOVED PATCHES */
158 
        SELECT 'R' AS CHANGE_TYPE, oldbom.PROD_ID, oldbom.PK
159 
          FROM (
160 
                SELECT osc.PROD_ID,
161 
                       nn.NODE_NAME ||'_'|| os.OS_NAME AS PK
162 
                  FROM OS_CONTENTS osc,
163 
                       OPERATING_SYSTEMS os,
164 
                       NETWORK_NODES nn,
165 
                       BOM_CONTENTS bc,
166 
                       PACKAGE_VERSIONS pv
167 
                 WHERE osc.OS_ID = os.OS_ID
168 
                   AND os.NODE_ID = nn.NODE_ID
169 
                   AND bc.NODE_ID = nn.NODE_ID
170 
                   AND osc.PROD_ID = pv.PV_ID
171 
                   AND NOT pv.IS_PATCH IS NULL
172 
                   AND bc.BOM_ID = :BOM_ID
173 
               ) newbom,
174 
               (
175 
                SELECT osc.PROD_ID,
176 
                       nn.NODE_NAME ||'_'|| os.OS_NAME AS PK
177 
                  FROM OS_CONTENTS osc,
178 
                       OPERATING_SYSTEMS os,
179 
                       NETWORK_NODES nn,
180 
                       BOM_CONTENTS bc,
181 
                       PACKAGE_VERSIONS pv
182 
                 WHERE osc.OS_ID = os.OS_ID
183 
                   AND os.NODE_ID = nn.NODE_ID
184 
                   AND bc.NODE_ID = nn.NODE_ID
185 
                   AND osc.PROD_ID = pv.PV_ID
186 
                   AND NOT pv.IS_PATCH IS NULL
187 
                   AND bc.BOM_ID = :COMPARE_BOM_ID
188 
               ) oldbom
189 
         WHERE newbom.PK (+) = oldbom.PK
190 
           AND newbom.PROD_ID (+) = oldbom.PROD_ID
191 
           AND newbom.PK IS NULL
192 
 
13 rsolanki 193 
       ) qry,
5634 dpurdie 194 
       PACKAGE_VERSIONS pv,
195 
       PACKAGES pkg,
196 
       PRODUCT_DETAILS pd
13 rsolanki 197 
 WHERE qry.PROD_ID = pv.PV_ID
198 
   AND pv.PKG_ID = pkg.PKG_ID
199 
   AND pd.PROD_ID (+) = qry.PROD_ID
5634 dpurdie 200 
 ORDER BY UPPER(pkg.PKG_NAME)
201