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 Removed Product Location */
5634 dpurdie 2 
select * from (
3 
    WITH newbom AS (
5640 dpurdie 4 
        -- A set of elements from the NEW BOM that have no corresponding element in the old bom
5 
        -- Element are matched on a Full-Key (FK) made up of the NODE_NAME, OS_NAME and PROD_ID
6 
        -- Note. NODE_ID and OS_ID are not global, but are local to the BOM and are thus
7 
        --       useless for matching purposes.
8 
        SELECT * FROM (
5634 dpurdie 9 
        SELECT osc.PROD_ID,
10 
               CASE WHEN pkg.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 11 
               nn.NODE_NAME ||'_'|| os.OS_NAME || '_'  || osc.PROD_ID as FK,
5634 dpurdie 12 
               nt.NODE_ICON,
13 
               nn.NODE_ID,
14 
               nn.NODE_NAME,
15 
               os.OS_ID,
16 
               os.OS_NAME,
17 
               pkg.PKG_ID,
18 
               pkg.PKG_NAME,
19 
               pv.PKG_VERSION
20 
          FROM OS_CONTENTS osc,
21 
               OPERATING_SYSTEMS os,
22 
               NETWORK_NODES nn,
23 
               NODE_TYPES nt,
24 
               BOM_CONTENTS bc,
25 
               PACKAGE_VERSIONS pv,
26 
               PACKAGES pkg
27 
         WHERE osc.OS_ID = os.OS_ID
28 
           AND os.NODE_ID = nn.NODE_ID
29 
           AND bc.NODE_ID = nn.NODE_ID
30 
           AND osc.PROD_ID = pv.PV_ID
31 
           AND pv.PKG_ID = pkg.PKG_ID
32 
           AND nn.NODE_TYPE_ID = nt.NODE_TYPE_ID
5640 dpurdie 33 
           AND bc.BOM_ID = :BOM_ID
34 
                 )
35 
                 WHERE FK NOT IN (
36 
                                 SELECT
37 
                   nn.NODE_NAME ||'_'|| os.OS_NAME || '_'  || osc.PROD_ID as FK
38 
                FROM OS_CONTENTS osc,
39 
                     OPERATING_SYSTEMS os,
40 
                     NETWORK_NODES nn,
41 
                     BOM_CONTENTS bc,
42 
                     PACKAGE_VERSIONS pv
43 
               WHERE osc.OS_ID = os.OS_ID
44 
                 AND os.NODE_ID = nn.NODE_ID
45 
                 AND bc.NODE_ID = nn.NODE_ID
46 
                 AND osc.PROD_ID = pv.PV_ID
47 
                 AND pv.IS_PATCH IS NULL
48 
                 AND bc.BOM_ID = :COMPARE_BOM_ID
49 
                 )
5634 dpurdie 50 
    ),
51 
    oldbom as (
5640 dpurdie 52 
        -- A set of elements from the OLD BOM that have no corresponding element in the NEW bom
53 
        -- Element are matched on a Full-Key (FK) made up of the NODE_NAME, OS_NAME and PROD_ID
54 
        -- Note. NODE_ID and OS_ID are not global, but are local to the BOM and are thus
55 
        --       useless for matching purposes.
56 
        SELECT * FROM (
5634 dpurdie 57 
        SELECT osc.PROD_ID,
58 
               CASE WHEN pkg.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 59 
               nn.NODE_NAME ||'_'|| os.OS_NAME || '_'  || osc.PROD_ID as FK,
5634 dpurdie 60 
               nt.NODE_ICON,
61 
               nn.NODE_ID,
62 
               nn.NODE_NAME,
63 
               os.OS_ID,
64 
               os.OS_NAME,
65 
               pkg.PKG_ID,
66 
               pkg.PKG_NAME,
67 
               pv.PKG_VERSION
68 
          FROM OS_CONTENTS osc,
69 
               OPERATING_SYSTEMS os,
70 
               NETWORK_NODES nn,
71 
               NODE_TYPES nt,
72 
               BOM_CONTENTS bc,
73 
               PACKAGE_VERSIONS pv,
74 
               PACKAGES pkg
75 
         WHERE osc.OS_ID = os.OS_ID
76 
           AND os.NODE_ID = nn.NODE_ID
77 
           AND bc.NODE_ID = nn.NODE_ID
78 
           AND osc.PROD_ID = pv.PV_ID
79 
           AND pv.PKG_ID = pkg.PKG_ID
80 
           AND nn.NODE_TYPE_ID = nt.NODE_TYPE_ID
5640 dpurdie 81 
           AND bc.BOM_ID = :COMPARE_BOM_ID
82 
           )
83 
                 WHERE FK NOT IN (
84 
                                 SELECT
85 
                   nn.NODE_NAME ||'_'|| os.OS_NAME || '_'  || osc.PROD_ID as FK
86 
                FROM OS_CONTENTS osc,
87 
                     OPERATING_SYSTEMS os,
88 
                     NETWORK_NODES nn,
89 
                     BOM_CONTENTS bc,
90 
                     PACKAGE_VERSIONS pv
91 
               WHERE osc.OS_ID = os.OS_ID
92 
                 AND os.NODE_ID = nn.NODE_ID
93 
                 AND bc.NODE_ID = nn.NODE_ID
94 
                 AND osc.PROD_ID = pv.PV_ID
95 
                 AND pv.IS_PATCH IS NULL
96 
                 AND bc.BOM_ID = :BOM_ID
97 
                 )
5634 dpurdie 98 
    ),
99 
    newNodes as (
5640 dpurdie 100 
        -- A set of NODE_NAMES and NODE_ID from the new BOM
101 
        -- Will be used to:
102 
        --      Determine if the NODE exists in the new bom
103 
        --      Map NODE_ID from the old bom to the new bom for display
104 
      SELECT nn.NODE_ID, NODE_NAME from bom_contents bc, NETWORK_NODES nn where bc.bom_id = :BOM_ID AND bc.NODE_ID = nn.NODE_ID
5634 dpurdie 105 
    ),
106 
    newOS as (
5640 dpurdie 107 
        -- A set of OS_ID, OS_NAME and NODE_NAME from the new BOM
108 
        -- Will be used to:
109 
        --      Determine if the OS_ID exists in the new bom
110 
        --      Map OS_ID from the old bom to the new bom for display
111 
      SELECT OS_ID, OS_NAME, NODE_NAME from operating_systems os, bom_contents bc, NETWORK_NODES nn where bc.bom_id = :BOM_ID and bc.node_id = os.node_id  AND bc.NODE_ID = nn.NODE_ID
5634 dpurdie 112 
    )
5640 dpurdie 113 
    SELECT
114 
        oldbom.PROD_ID as PROD_ID,
115 
        newNodes.NODE_ID as NODE_ID,
116 
        oldbom.NODE_ICON,
117 
        oldbom.NODE_NAME,
118 
        newOS.OS_ID as OS_ID,
119 
        oldbom.OS_NAME,
120 
        oldbom.PKG_ID,
121 
        oldbom.PKG_NAME,
122 
        oldbom.PKG_VERSION,
123 
        oldbom.PK,
124 
        newbom.PROD_ID AS NPROD_ID,
125 
        NVL2(newNodes.NODE_NAME,1,null) as nodeExists,
126 
        NVL2(newOS.OS_NAME,1,null) as osExists
5634 dpurdie 127 
    from newbom
128 
    full outer join oldbom on newbom.PK = oldbom.PK
5640 dpurdie 129 
    left outer join newNodes on oldbom.NODE_NAME = newNodes.NODE_NAME
130 
    left outer join newOS on oldbom.OS_NAME = newOS.OS_NAME and oldbom.NODE_NAME = newOS.NODE_NAME
5634 dpurdie 131 
    ) where
5640 dpurdie 132 
          PROD_ID = :PROD_ID
5634 dpurdie 133 
          AND NPROD_ID IS NULL
134 
          ORDER BY UPPER(PK)
29 jtweddle 135 
 
136 
 
137 
 
5634 dpurdie 138 
 
5640 dpurdie 139