| Line 1... |
Line 1... |
| 1 |
/* Bom Removed Product Location */
|
1 |
/* Bom Removed Product Location */
|
| 2 |
select * from (
|
2 |
select * from (
|
| 3 |
WITH newbom AS (
|
3 |
WITH newbom AS (
|
| - |
|
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 (
|
| 4 |
SELECT osc.PROD_ID,
|
9 |
SELECT osc.PROD_ID,
|
| 5 |
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,
|
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,
|
| - |
|
11 |
nn.NODE_NAME ||'_'|| os.OS_NAME || '_' || osc.PROD_ID as FK,
|
| 6 |
nt.NODE_ICON,
|
12 |
nt.NODE_ICON,
|
| 7 |
nn.NODE_ID,
|
13 |
nn.NODE_ID,
|
| 8 |
nn.NODE_NAME,
|
14 |
nn.NODE_NAME,
|
| 9 |
os.OS_ID,
|
15 |
os.OS_ID,
|
| 10 |
os.OS_NAME,
|
16 |
os.OS_NAME,
|
| Line 22... |
Line 28... |
| 22 |
AND os.NODE_ID = nn.NODE_ID
|
28 |
AND os.NODE_ID = nn.NODE_ID
|
| 23 |
AND bc.NODE_ID = nn.NODE_ID
|
29 |
AND bc.NODE_ID = nn.NODE_ID
|
| 24 |
AND osc.PROD_ID = pv.PV_ID
|
30 |
AND osc.PROD_ID = pv.PV_ID
|
| 25 |
AND pv.PKG_ID = pkg.PKG_ID
|
31 |
AND pv.PKG_ID = pkg.PKG_ID
|
| 26 |
AND nn.NODE_TYPE_ID = nt.NODE_TYPE_ID
|
32 |
AND nn.NODE_TYPE_ID = nt.NODE_TYPE_ID
|
| 27 |
AND bc.BOM_ID = :BOM_ID
|
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 |
)
|
| 28 |
),
|
50 |
),
|
| 29 |
oldbom as (
|
51 |
oldbom as (
|
| - |
|
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 (
|
| 30 |
SELECT osc.PROD_ID,
|
57 |
SELECT osc.PROD_ID,
|
| 31 |
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,
|
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,
|
| - |
|
59 |
nn.NODE_NAME ||'_'|| os.OS_NAME || '_' || osc.PROD_ID as FK,
|
| 32 |
nt.NODE_ICON,
|
60 |
nt.NODE_ICON,
|
| 33 |
nn.NODE_ID,
|
61 |
nn.NODE_ID,
|
| 34 |
nn.NODE_NAME,
|
62 |
nn.NODE_NAME,
|
| 35 |
os.OS_ID,
|
63 |
os.OS_ID,
|
| 36 |
os.OS_NAME,
|
64 |
os.OS_NAME,
|
| Line 48... |
Line 76... |
| 48 |
AND os.NODE_ID = nn.NODE_ID
|
76 |
AND os.NODE_ID = nn.NODE_ID
|
| 49 |
AND bc.NODE_ID = nn.NODE_ID
|
77 |
AND bc.NODE_ID = nn.NODE_ID
|
| 50 |
AND osc.PROD_ID = pv.PV_ID
|
78 |
AND osc.PROD_ID = pv.PV_ID
|
| 51 |
AND pv.PKG_ID = pkg.PKG_ID
|
79 |
AND pv.PKG_ID = pkg.PKG_ID
|
| 52 |
AND nn.NODE_TYPE_ID = nt.NODE_TYPE_ID
|
80 |
AND nn.NODE_TYPE_ID = nt.NODE_TYPE_ID
|
| 53 |
AND bc.BOM_ID = :COMPARE_BOM_ID
|
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 |
)
|
| 54 |
),
|
98 |
),
|
| 55 |
newNodes as (
|
99 |
newNodes as (
|
| - |
|
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
|
| 56 |
SELECT node_id from bom_contents where bom_id = :BOM_ID
|
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
|
| 57 |
),
|
105 |
),
|
| 58 |
newOS as (
|
106 |
newOS as (
|
| - |
|
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
|
| 59 |
SELECT os_id from operating_systems os, bom_contents bc where bc.bom_id = :BOM_ID and bc.node_id = os.node_id
|
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
|
| 60 |
)
|
112 |
)
|
| - |
|
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,
|
| 61 |
SELECT oldbom.*, newbom.PROD_ID AS NPROD_ID, oldbom.PROD_ID AS OPROD_ID , newNodes.node_id as nodeExists, newOS.os_id as osExists
|
125 |
NVL2(newNodes.NODE_NAME,1,null) as nodeExists,
|
| - |
|
126 |
NVL2(newOS.OS_NAME,1,null) as osExists
|
| 62 |
from newbom
|
127 |
from newbom
|
| 63 |
full outer join oldbom on newbom.PK = oldbom.PK
|
128 |
full outer join oldbom on newbom.PK = oldbom.PK
|
| 64 |
left outer join newNodes on newbom.node_id = newNodes.node_id
|
129 |
left outer join newNodes on oldbom.NODE_NAME = newNodes.NODE_NAME
|
| 65 |
left outer join newOS on newbom.os_id = newOS.os_id
|
130 |
left outer join newOS on oldbom.OS_NAME = newOS.OS_NAME and oldbom.NODE_NAME = newOS.NODE_NAME
|
| 66 |
) where
|
131 |
) where
|
| 67 |
OPROD_ID = :PROD_ID
|
132 |
PROD_ID = :PROD_ID
|
| 68 |
AND NPROD_ID IS NULL
|
133 |
AND NPROD_ID IS NULL
|
| 69 |
ORDER BY UPPER(PK)
|
134 |
ORDER BY UPPER(PK)
|
| 70 |
|
135 |
|
| 71 |
|
136 |
|
| 72 |
|
137 |
|
| 73 |
|
138 |
|
| - |
|
139 |
|