Subversion Repositories DevTools

Rev

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

Rev 5634 Rev 5640
Line 53... Line 53...
53 
           AND oldbom.PK IS NULL
 53 
           AND oldbom.PK IS NULL
54
54
55 
        UNION
55 
        UNION
56
56
57 
--  Added, Updated and Removed Packages
 57 
--  Added, Updated and Removed Packages
- 
 
 58 
SELECT
58 
SELECT CASE WHEN PROD_ID is NULL then 'R' WHEN OPROD_ID is NULL THEN 'A' ELSE 'U' END as CHANGE_TYPE,
59 
       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(prod_id, prod_id, oprod_id) as prod_id ,
60 
       NVL2(pk, pk, opk)  as pk
61 
       NVL2(pk, pk, opk)  as pk
61 
       from (
 62 
       from (
62 
       WITH
63 
       WITH
63 
        newbom as
 64 
        newbom as
64 
            (
 65 
            (
- 
 
 66 
                SELECT * FROM (
65 
                SELECT
67 
                SELECT
66 
                   osc.PROD_ID,
 68 
                   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,
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,
- 
 
 70 
                   nn.NODE_NAME ||'_'|| os.OS_NAME || '_'  || osc.PROD_ID as FK,
68 
                   pv.PKG_ID,
 71 
                   pv.PKG_ID,
69 
                   NVL( pv.V_EXT, '|LINK_A_NULL|' ) AS V_EXT,
 72 
                   NVL( pv.V_EXT, '|LINK_A_NULL|' ) AS V_EXT,
70 
                   REPLACE(osc.product_comments,CHR(13)||CHR(10), '-') as COMMENTS
 73 
                   REPLACE(osc.product_comments,CHR(13)||CHR(10), '-') as COMMENTS
71 
                FROM OS_CONTENTS osc,
 74 
                FROM OS_CONTENTS osc,
72 
                     OPERATING_SYSTEMS os,
 75 
                     OPERATING_SYSTEMS os,
Line 77... Line 80...
77 
                 AND os.NODE_ID = nn.NODE_ID
 80 
                 AND os.NODE_ID = nn.NODE_ID
78 
                 AND bc.NODE_ID = nn.NODE_ID
 81 
                 AND bc.NODE_ID = nn.NODE_ID
79 
                 AND osc.PROD_ID = pv.PV_ID
 82 
                 AND osc.PROD_ID = pv.PV_ID
80 
                 AND pv.IS_PATCH IS NULL
 83 
                 AND pv.IS_PATCH IS NULL
81 
                 AND bc.BOM_ID = :BOM_ID
 84 
                 AND bc.BOM_ID = :BOM_ID
- 
 
 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
82 
                 ),
 102 
                 ),
83 
       oldbom as
103 
       oldbom as
84 
               (
 104 
               (
- 
 
 105 
               SELECT * from (
85 
                SELECT osc.PROD_ID,
 106 
                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,
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,
- 
 
 108 
                   nn.NODE_NAME ||'_'|| os.OS_NAME || '_'  || osc.PROD_ID as FK,
87 
                   pv.PKG_ID,
 109 
                   pv.PKG_ID,
88 
                   NVL( pv.V_EXT, '|LINK_A_NULL|' ) AS V_EXT,
 110 
                   NVL( pv.V_EXT, '|LINK_A_NULL|' ) AS V_EXT,
89 
                   REPLACE(osc.product_comments,CHR(13)||CHR(10),'-') as COMMENTS
 111 
                   REPLACE(osc.product_comments,CHR(13)||CHR(10),'-') as COMMENTS
90 
                FROM OS_CONTENTS osc,
 112 
                FROM OS_CONTENTS osc,
91 
                     OPERATING_SYSTEMS os,
 113 
                     OPERATING_SYSTEMS os,
Line 95... Line 117...
95 
               WHERE osc.OS_ID = os.OS_ID
 117 
               WHERE osc.OS_ID = os.OS_ID
96 
                 AND os.NODE_ID = nn.NODE_ID
 118 
                 AND os.NODE_ID = nn.NODE_ID
97 
                 AND bc.NODE_ID = nn.NODE_ID
 119 
                 AND bc.NODE_ID = nn.NODE_ID
98 
                 AND osc.PROD_ID = pv.PV_ID
 120 
                 AND osc.PROD_ID = pv.PV_ID
99 
                 AND pv.IS_PATCH IS NULL
 121 
                 AND pv.IS_PATCH IS NULL
100 
                 AND bc.BOM_ID = :COMPARE_BOM_ID
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 
 
101 
               )
 140 
               )
- 
 
 141
102 
        SELECT newbom.PROD_ID, newbom.PK, newbom.pkg_id, newbom.comments,
 142 
        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
 143 
               oldbom.PROD_ID as OPROD_ID, oldbom.PK as OPK, oldbom.pkg_id as OPKG_ID, oldbom.comments as OCOMMENTS
104 
               FROM newbom
 144 
               FROM newbom
105 
               full outer join oldbom on newbom.PK = oldbom.PK
 145 
               full outer join oldbom on newbom.PK = oldbom.PK
- 
 
 146 
 
- 
 
 147 
 
106 
      ) where
148 
      ) where
107 
            PROD_ID is NULL
149 
            PROD_ID is NULL
108 
            or OPKG_ID is NULL
 150 
            or OPKG_ID is NULL
109 
            or PROD_ID != OPROD_ID
 151 
            or PROD_ID != OPROD_ID
110 
            or COMMENTS != OCOMMENTS
 152 
            or COMMENTS != OCOMMENTS
111
153
- 
 
 154
112 
        UNION
155 
        UNION
113
156
114 
        /* REMOVED PATCHES */
157 
        /* REMOVED PATCHES */
115 
        SELECT 'R' AS CHANGE_TYPE, oldbom.PROD_ID, oldbom.PK
 158 
        SELECT 'R' AS CHANGE_TYPE, oldbom.PROD_ID, oldbom.PK
116 
          FROM (
 159 
          FROM (