| 5642 |
dpurdie |
1 |
/* Bom Removed Product Location - BASE ENV */
|
|
|
2 |
select * from (
|
|
|
3 |
WITH newbom as (
|
|
|
4 |
SELECT bec.PROD_ID,
|
|
|
5 |
CASE WHEN pkg.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,
|
|
|
6 |
nt.NODE_ICON,
|
|
|
7 |
nn.NODE_ID,
|
|
|
8 |
nn.NODE_NAME,
|
|
|
9 |
os.OS_ID,
|
|
|
10 |
os.OS_NAME,
|
|
|
11 |
pkg.PKG_ID,
|
|
|
12 |
pkg.PKG_NAME,
|
|
|
13 |
pv.PKG_VERSION,
|
|
|
14 |
REPLACE(bec.base_env_comments,CHR(13)||CHR(10), '-') as COMMENTS
|
|
|
15 |
FROM BASE_ENV_CONTENTS bec,
|
|
|
16 |
OPERATING_SYSTEMS os,
|
|
|
17 |
OS_BASE_ENV obe,
|
|
|
18 |
NETWORK_NODES nn,
|
|
|
19 |
NODE_TYPES nt,
|
|
|
20 |
BOM_CONTENTS bc,
|
|
|
21 |
PACKAGE_VERSIONS pv,
|
|
|
22 |
PACKAGES pkg
|
|
|
23 |
WHERE obe.OS_ID = os.OS_ID
|
|
|
24 |
AND bec.BASE_ENV_ID = obe.BASE_ENV_ID
|
|
|
25 |
AND os.NODE_ID = nn.NODE_ID
|
|
|
26 |
AND bc.NODE_ID = nn.NODE_ID
|
|
|
27 |
AND bec.PROD_ID = pv.PV_ID
|
|
|
28 |
AND pv.IS_PATCH IS NULL
|
|
|
29 |
AND nn.NODE_TYPE_ID = nt.NODE_TYPE_ID
|
|
|
30 |
AND pv.PKG_ID = pkg.PKG_ID
|
|
|
31 |
AND bc.BOM_ID = :BOM_ID
|
|
|
32 |
),
|
|
|
33 |
oldbom as (
|
|
|
34 |
SELECT bec.PROD_ID,
|
|
|
35 |
CASE WHEN pkg.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,
|
|
|
36 |
nt.NODE_ICON,
|
|
|
37 |
nn.NODE_ID,
|
|
|
38 |
nn.NODE_NAME,
|
|
|
39 |
os.OS_ID,
|
|
|
40 |
os.OS_NAME,
|
|
|
41 |
pkg.PKG_ID,
|
|
|
42 |
pkg.PKG_NAME,
|
|
|
43 |
pv.PKG_VERSION,
|
|
|
44 |
REPLACE(bec.base_env_comments,CHR(13)||CHR(10), '-') as COMMENTS
|
|
|
45 |
FROM BASE_ENV_CONTENTS bec,
|
|
|
46 |
OPERATING_SYSTEMS os,
|
|
|
47 |
OS_BASE_ENV obe,
|
|
|
48 |
NETWORK_NODES nn,
|
|
|
49 |
NODE_TYPES nt,
|
|
|
50 |
BOM_CONTENTS bc,
|
|
|
51 |
PACKAGE_VERSIONS pv,
|
|
|
52 |
PACKAGES pkg
|
|
|
53 |
WHERE obe.OS_ID = os.OS_ID
|
|
|
54 |
AND bec.BASE_ENV_ID = obe.BASE_ENV_ID
|
|
|
55 |
AND os.NODE_ID = nn.NODE_ID
|
|
|
56 |
AND bc.NODE_ID = nn.NODE_ID
|
|
|
57 |
AND bec.PROD_ID = pv.PV_ID
|
|
|
58 |
AND pv.IS_PATCH IS NULL
|
|
|
59 |
AND nn.NODE_TYPE_ID = nt.NODE_TYPE_ID
|
|
|
60 |
AND pv.PKG_ID = pkg.PKG_ID
|
|
|
61 |
AND bc.BOM_ID = :COMPARE_BOM_ID
|
|
|
62 |
),
|
|
|
63 |
newNodes as (
|
|
|
64 |
-- A set of NODE_NAMES and NODE_ID from the new BOM
|
|
|
65 |
-- Will be used to:
|
|
|
66 |
-- Determine if the NODE exists in the new bom
|
|
|
67 |
-- Map NODE_ID from the old bom to the new bom for display
|
|
|
68 |
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
|
|
|
69 |
),
|
|
|
70 |
newOS as (
|
|
|
71 |
-- A set of OS_ID, OS_NAME and NODE_NAME from the new BOM
|
|
|
72 |
-- Will be used to:
|
|
|
73 |
-- Determine if the OS_ID exists in the new bom
|
|
|
74 |
-- Map OS_ID from the old bom to the new bom for display
|
|
|
75 |
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
|
|
|
76 |
)
|
|
|
77 |
SELECT
|
|
|
78 |
oldbom.PROD_ID as PROD_ID,
|
|
|
79 |
newNodes.NODE_ID as NODE_ID,
|
|
|
80 |
oldbom.NODE_ICON,
|
|
|
81 |
oldbom.NODE_NAME,
|
|
|
82 |
newOS.OS_ID as OS_ID,
|
|
|
83 |
oldbom.OS_NAME,
|
|
|
84 |
oldbom.PKG_ID,
|
|
|
85 |
oldbom.PKG_NAME,
|
|
|
86 |
oldbom.PKG_VERSION,
|
|
|
87 |
oldbom.PK,
|
|
|
88 |
newbom.PROD_ID AS NPROD_ID,
|
|
|
89 |
NVL2(newNodes.NODE_NAME,1,null) as nodeExists,
|
|
|
90 |
NVL2(newOS.OS_NAME,1,null) as osExists
|
|
|
91 |
from newbom
|
|
|
92 |
full outer join oldbom on newbom.PK = oldbom.PK
|
|
|
93 |
left outer join newNodes on oldbom.NODE_NAME = newNodes.NODE_NAME
|
|
|
94 |
left outer join newOS on oldbom.OS_NAME = newOS.OS_NAME and oldbom.NODE_NAME = newOS.NODE_NAME
|
|
|
95 |
) where
|
|
|
96 |
PROD_ID = :PROD_ID
|
|
|
97 |
AND NPROD_ID IS NULL
|
|
|
98 |
ORDER BY UPPER(PK)
|
|
|
99 |
|