Subversion Repositories DevTools

Rev

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

Rev Author Line No. Line
25 rsolanki 1 
/* Bom Compare - Base Configuration */
2 
SELECT DISTINCT
3 
       pv.PV_ID AS PROD_ID,
5634 dpurdie 4 
       pkg.PKG_NAME,
5 
       pv.PKG_VERSION,
6 
       qry.CHANGE_TYPE,
7 
       pd.IS_REJECTED,
8 
       ( CASE
9 
         WHEN qry.CHANGE_TYPE = 'A' THEN
10 
            'LIMG_ADDED'
11 
         WHEN qry.CHANGE_TYPE = 'U' THEN
12 
            'LIMG_UPDATED'
13 
         ELSE
14 
            'LIMG_REMOVED'
15 
         END ) AS DIFF_ICON
25 rsolanki 16 
  FROM (
17 
 
5634 dpurdie 18 
        /* ADDED PATCHES ONLY - BASE CONFIGURATIONS*/
19 
        SELECT 'A' AS CHANGE_TYPE, newbom.PROD_ID, newbom.PK
20 
          FROM (
21 
                SELECT bec.PROD_ID,
22 
                       nn.NODE_NAME ||'_'|| os.OS_NAME AS PK
23 
                  FROM BASE_ENV_CONTENTS bec,
24 
                       OPERATING_SYSTEMS os,
25 
                       OS_BASE_ENV obe,
26 
                       NETWORK_NODES nn,
27 
                       BOM_CONTENTS bc,
28 
                       PACKAGE_VERSIONS pv
29 
                 WHERE obe.OS_ID = os.OS_ID
30 
                   AND obe.BASE_ENV_ID = bec.BASE_ENV_ID
31 
                   AND os.NODE_ID = nn.NODE_ID
32 
                   AND bc.NODE_ID = nn.NODE_ID
33 
                   AND bec.PROD_ID = pv.PV_ID
34 
                   AND NOT pv.IS_PATCH IS NULL
35 
                   AND bc.BOM_ID = :BOM_ID
36 
               ) newbom,
37 
               (
38 
                SELECT bec.PROD_ID,
39 
                       nn.NODE_NAME ||'_'|| os.OS_NAME AS PK
40 
                  FROM BASE_ENV_CONTENTS bec,
41 
                       OPERATING_SYSTEMS os,
42 
                       OS_BASE_ENV obe,
43 
                       NETWORK_NODES nn,
44 
                       BOM_CONTENTS bc,
45 
                       PACKAGE_VERSIONS pv
46 
                 WHERE obe.OS_ID = os.OS_ID
47 
                   AND bec.BASE_ENV_ID = obe.BASE_ENV_ID
48 
                   AND os.NODE_ID = nn.NODE_ID
49 
                   AND bc.NODE_ID = nn.NODE_ID
50 
                   AND bec.PROD_ID = pv.PV_ID
51 
                   AND NOT pv.IS_PATCH IS NULL
52 
                   AND bc.BOM_ID = :COMPARE_BOM_ID
53 
               ) oldbom
54 
         WHERE newbom.PK = oldbom.PK (+)
55 
           AND newbom.PROD_ID = oldbom.PROD_ID (+)
56 
           AND oldbom.PK IS NULL
57 
 
58 
        UNION
59 
 
60 
--  Added, Updated and Removed Packages - Base Config
61 
SELECT CASE WHEN PROD_ID is NULL then 'R' WHEN OPROD_ID is NULL THEN 'A' ELSE 'U' END as CHANGE_TYPE,
62 
       NVL2(prod_id, prod_id, oprod_id) as prod_id ,
63 
       NVL2(pk, pk, opk)  as pk
64 
       from (
65 
      WITH newbom as
66 
      (
67 
                SELECT bec.PROD_ID,
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 
                       pv.PKG_ID,
70 
                       NVL( pv.V_EXT, '|LINK_A_NULL|' ) AS V_EXT,
71 
                       REPLACE(bec.base_env_comments,CHR(13)||CHR(10), '-') as COMMENTS
72 
                  FROM BASE_ENV_CONTENTS bec,
73 
                       OPERATING_SYSTEMS os,
74 
                       OS_BASE_ENV obe,
75 
                       NETWORK_NODES nn,
76 
                       BOM_CONTENTS bc,
77 
                       PACKAGE_VERSIONS pv
78 
                 WHERE obe.OS_ID = os.OS_ID
79 
                   AND bec.BASE_ENV_ID = obe.BASE_ENV_ID
80 
                   AND os.NODE_ID = nn.NODE_ID
81 
                   AND bc.NODE_ID = nn.NODE_ID
82 
                   AND bec.PROD_ID = pv.PV_ID
83 
                   AND pv.IS_PATCH IS NULL
84 
                   AND bc.BOM_ID = :BOM_ID
85 
               ),
86 
    oldbom as
87 
               (
88 
                SELECT bec.PROD_ID,
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 
                       pv.PKG_ID,
91 
                       NVL( pv.V_EXT, '|LINK_A_NULL|' ) AS V_EXT,
92 
                       REPLACE(bec.base_env_comments,CHR(13)||CHR(10), '-') as COMMENTS
93 
                  FROM BASE_ENV_CONTENTS bec,
94 
                       OPERATING_SYSTEMS os,
95 
                       OS_BASE_ENV obe,
96 
                       NETWORK_NODES nn,
97 
                       BOM_CONTENTS bc,
98 
                       PACKAGE_VERSIONS pv
99 
                 WHERE obe.OS_ID = os.OS_ID
100 
                   AND bec.BASE_ENV_ID = obe.BASE_ENV_ID
101 
                   AND os.NODE_ID = nn.NODE_ID
102 
                   AND bc.NODE_ID = nn.NODE_ID
103 
                   AND bec.PROD_ID = pv.PV_ID
104 
                   AND pv.IS_PATCH IS NULL
105 
                   AND bc.BOM_ID = :COMPARE_BOM_ID
106 
               )
107 
        SELECT newbom.PROD_ID, newbom.PK, newbom.pkg_id, newbom.comments,
108 
               oldbom.PROD_ID as OPROD_ID, oldbom.PK as OPK, oldbom.pkg_id as OPKG_ID, oldbom.comments as OCOMMENTS
109 
            from newbom
110 
            full outer join oldbom on newbom.PK = oldbom.PK
111 
      ) where
112 
            PROD_ID is NULL
113 
            or OPKG_ID is null
114 
            or PROD_ID != OPROD_ID
115 
            or COMMENTS != OCOMMENTS
116 
 
117 
        UNION
118 
 
119 
        /* REMOVED PATCHES - BASE CONFIGURATIONS */
120 
        SELECT 'R' AS CHANGE_TYPE, oldbom.PROD_ID, oldbom.PK
121 
          FROM (
122 
                SELECT bec.PROD_ID,
123 
                       nn.NODE_NAME ||'_'|| os.OS_NAME AS PK
124 
                  FROM BASE_ENV_CONTENTS bec,
125 
                       OPERATING_SYSTEMS os,
126 
                       OS_BASE_ENV obe,
127 
                       NETWORK_NODES nn,
128 
                       BOM_CONTENTS bc,
129 
                       PACKAGE_VERSIONS pv
130 
                 WHERE obe.OS_ID = os.OS_ID
131 
                   AND bec.BASE_ENV_ID = obe.BASE_ENV_ID
132 
                   AND os.NODE_ID = nn.NODE_ID
133 
                   AND bc.NODE_ID = nn.NODE_ID
134 
                   AND bec.PROD_ID = pv.PV_ID
135 
                   AND NOT pv.IS_PATCH IS NULL
136 
                   AND bc.BOM_ID = :BOM_ID
137 
               ) newbom,
138 
               (
139 
                SELECT bec.PROD_ID,
140 
                       nn.NODE_NAME ||'_'|| os.OS_NAME AS PK
141 
                  FROM BASE_ENV_CONTENTS bec,
142 
                       OPERATING_SYSTEMS os,
143 
                       OS_BASE_ENV obe,
144 
                       NETWORK_NODES nn,
145 
                       BOM_CONTENTS bc,
146 
                       PACKAGE_VERSIONS pv
147 
                 WHERE obe.OS_ID = os.OS_ID
148 
                   AND bec.BASE_ENV_ID = obe.BASE_ENV_ID
149 
                   AND os.NODE_ID = nn.NODE_ID
150 
                   AND bc.NODE_ID = nn.NODE_ID
151 
                   AND bec.PROD_ID = pv.PV_ID
152 
                   AND NOT pv.IS_PATCH IS NULL
153 
                   AND bc.BOM_ID = :COMPARE_BOM_ID
154 
               ) oldbom
155 
         WHERE newbom.PK (+) = oldbom.PK
156 
           AND newbom.PROD_ID (+) = oldbom.PROD_ID
157 
           AND newbom.PK IS NULL
158 
 
25 rsolanki 159 
       ) qry,
5634 dpurdie 160 
       PACKAGE_VERSIONS pv,
161 
       PACKAGES pkg,
162 
       PRODUCT_DETAILS pd
25 rsolanki 163 
 WHERE qry.PROD_ID = pv.PV_ID
164 
   AND pv.PKG_ID = pkg.PKG_ID
165 
   AND pd.PROD_ID (+) = qry.PROD_ID
5634 dpurdie 166 
 ORDER BY UPPER(pkg.PKG_NAME)
167