Subversion Repositories DevTools

Rev

Rev 5633 | Go to most recent revision | 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
58 
SELECT CASE WHEN PROD_ID is NULL then 'R' WHEN OPROD_ID is NULL THEN 'A' ELSE 'U' END as CHANGE_TYPE,
59 
       NVL2(prod_id, prod_id, oprod_id) as prod_id ,
60 
       NVL2(pk, pk, opk)  as pk
61 
       from (
62 
       WITH
63 
        newbom as
64 
            (
65 
                SELECT
66 
                   osc.PROD_ID,
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 
                   pv.PKG_ID,
69 
                   NVL( pv.V_EXT, '|LINK_A_NULL|' ) AS V_EXT,
70 
                   REPLACE(osc.product_comments,CHR(13)||CHR(10), '-') as COMMENTS
71 
                FROM OS_CONTENTS osc,
72 
                     OPERATING_SYSTEMS os,
73 
                     NETWORK_NODES nn,
74 
                     BOM_CONTENTS bc,
75 
                     PACKAGE_VERSIONS pv
76 
               WHERE osc.OS_ID = os.OS_ID
77 
                 AND os.NODE_ID = nn.NODE_ID
78 
                 AND bc.NODE_ID = nn.NODE_ID
79 
                 AND osc.PROD_ID = pv.PV_ID
80 
                 AND pv.IS_PATCH IS NULL
81 
                 AND bc.BOM_ID = :BOM_ID
82 
                 ),
83 
       oldbom as
84 
               (
85 
                SELECT osc.PROD_ID,
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 
                   pv.PKG_ID,
88 
                   NVL( pv.V_EXT, '|LINK_A_NULL|' ) AS V_EXT,
89 
                   REPLACE(osc.product_comments,CHR(13)||CHR(10),'-') as COMMENTS
90 
                FROM OS_CONTENTS osc,
91 
                     OPERATING_SYSTEMS os,
92 
                     NETWORK_NODES nn,
93 
                     BOM_CONTENTS bc,
94 
                     PACKAGE_VERSIONS pv
95 
               WHERE osc.OS_ID = os.OS_ID
96 
                 AND os.NODE_ID = nn.NODE_ID
97 
                 AND bc.NODE_ID = nn.NODE_ID
98 
                 AND osc.PROD_ID = pv.PV_ID
99 
                 AND pv.IS_PATCH IS NULL
100 
                 AND bc.BOM_ID = :COMPARE_BOM_ID
101 
               )
102 
        SELECT newbom.PROD_ID, newbom.PK, newbom.pkg_id, newbom.comments,
103 
               oldbom.PROD_ID as OPROD_ID, oldbom.PK as OPK, oldbom.pkg_id as OPKG_ID, oldbom.comments as OCOMMENTS
104 
               FROM newbom
105 
               full outer join oldbom on newbom.PK = oldbom.PK
106 
      ) where
107 
            PROD_ID is NULL
108 
            or OPKG_ID is NULL
109 
            or PROD_ID != OPROD_ID
110 
            or COMMENTS != OCOMMENTS
5633 dpurdie 111 
 
5634 dpurdie 112 
        UNION
113 
 
114 
        /* REMOVED PATCHES */
115 
        SELECT 'R' AS CHANGE_TYPE, oldbom.PROD_ID, oldbom.PK
116 
          FROM (
117 
                SELECT osc.PROD_ID,
118 
                       nn.NODE_NAME ||'_'|| os.OS_NAME AS PK
119 
                  FROM OS_CONTENTS osc,
120 
                       OPERATING_SYSTEMS os,
121 
                       NETWORK_NODES nn,
122 
                       BOM_CONTENTS bc,
123 
                       PACKAGE_VERSIONS pv
124 
                 WHERE osc.OS_ID = os.OS_ID
125 
                   AND os.NODE_ID = nn.NODE_ID
126 
                   AND bc.NODE_ID = nn.NODE_ID
127 
                   AND osc.PROD_ID = pv.PV_ID
128 
                   AND NOT pv.IS_PATCH IS NULL
129 
                   AND bc.BOM_ID = :BOM_ID
130 
               ) newbom,
131 
               (
132 
                SELECT osc.PROD_ID,
133 
                       nn.NODE_NAME ||'_'|| os.OS_NAME AS PK
134 
                  FROM OS_CONTENTS osc,
135 
                       OPERATING_SYSTEMS os,
136 
                       NETWORK_NODES nn,
137 
                       BOM_CONTENTS bc,
138 
                       PACKAGE_VERSIONS pv
139 
                 WHERE osc.OS_ID = os.OS_ID
140 
                   AND os.NODE_ID = nn.NODE_ID
141 
                   AND bc.NODE_ID = nn.NODE_ID
142 
                   AND osc.PROD_ID = pv.PV_ID
143 
                   AND NOT pv.IS_PATCH IS NULL
144 
                   AND bc.BOM_ID = :COMPARE_BOM_ID
145 
               ) oldbom
146 
         WHERE newbom.PK (+) = oldbom.PK
147 
           AND newbom.PROD_ID (+) = oldbom.PROD_ID
148 
           AND newbom.PK IS NULL
149 
 
13 rsolanki 150 
       ) qry,
5634 dpurdie 151 
       PACKAGE_VERSIONS pv,
152 
       PACKAGES pkg,
153 
       PRODUCT_DETAILS pd
13 rsolanki 154 
 WHERE qry.PROD_ID = pv.PV_ID
155 
   AND pv.PKG_ID = pkg.PKG_ID
156 
   AND pd.PROD_ID (+) = qry.PROD_ID
5634 dpurdie 157 
 ORDER BY UPPER(pkg.PKG_NAME)
158