| 5642 |
dpurdie |
1 |
/* Bom Removed Product Location - BASE ENV */
|
|
|
2 |
select * from (
|
|
|
3 |
|
|
|
4 |
WITH newbom as (
|
|
|
5 |
SELECT bec.PROD_ID,
|
|
|
6 |
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,
|
|
|
7 |
nt.NODE_ICON,
|
|
|
8 |
nn.NODE_ID,
|
|
|
9 |
nn.NODE_NAME,
|
|
|
10 |
os.OS_ID,
|
|
|
11 |
os.OS_NAME,
|
|
|
12 |
pkg.PKG_ID,
|
|
|
13 |
pkg.PKG_NAME,
|
|
|
14 |
pv.PKG_VERSION,
|
|
|
15 |
REPLACE(bec.base_env_comments,CHR(13)||CHR(10), '-') as COMMENTS
|
|
|
16 |
FROM BASE_ENV_CONTENTS bec,
|
|
|
17 |
OPERATING_SYSTEMS os,
|
|
|
18 |
OS_BASE_ENV obe,
|
|
|
19 |
NETWORK_NODES nn,
|
|
|
20 |
NODE_TYPES nt,
|
|
|
21 |
BOM_CONTENTS bc,
|
|
|
22 |
PACKAGE_VERSIONS pv,
|
|
|
23 |
PACKAGES pkg
|
|
|
24 |
WHERE obe.OS_ID = os.OS_ID
|
|
|
25 |
AND bec.BASE_ENV_ID = obe.BASE_ENV_ID
|
|
|
26 |
AND os.NODE_ID = nn.NODE_ID
|
|
|
27 |
AND bc.NODE_ID = nn.NODE_ID
|
|
|
28 |
AND bec.PROD_ID = pv.PV_ID
|
|
|
29 |
AND pv.IS_PATCH IS NULL
|
|
|
30 |
AND nn.NODE_TYPE_ID = nt.NODE_TYPE_ID
|
|
|
31 |
AND pv.PKG_ID = pkg.PKG_ID
|
|
|
32 |
AND bc.BOM_ID = :BOM_ID
|
|
|
33 |
),
|
|
|
34 |
oldbom as (
|
|
|
35 |
SELECT bec.PROD_ID,
|
|
|
36 |
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,
|
|
|
37 |
nt.NODE_ICON,
|
|
|
38 |
nn.NODE_ID,
|
|
|
39 |
nn.NODE_NAME,
|
|
|
40 |
os.OS_ID,
|
|
|
41 |
os.OS_NAME,
|
|
|
42 |
pkg.PKG_ID,
|
|
|
43 |
pkg.PKG_NAME,
|
|
|
44 |
pv.PKG_VERSION,
|
|
|
45 |
REPLACE(bec.base_env_comments,CHR(13)||CHR(10), '-') as COMMENTS
|
|
|
46 |
FROM BASE_ENV_CONTENTS bec,
|
|
|
47 |
OPERATING_SYSTEMS os,
|
|
|
48 |
OS_BASE_ENV obe,
|
|
|
49 |
NETWORK_NODES nn,
|
|
|
50 |
NODE_TYPES nt,
|
|
|
51 |
BOM_CONTENTS bc,
|
|
|
52 |
PACKAGE_VERSIONS pv,
|
|
|
53 |
PACKAGES pkg
|
|
|
54 |
WHERE obe.OS_ID = os.OS_ID
|
|
|
55 |
AND bec.BASE_ENV_ID = obe.BASE_ENV_ID
|
|
|
56 |
AND os.NODE_ID = nn.NODE_ID
|
|
|
57 |
AND bc.NODE_ID = nn.NODE_ID
|
|
|
58 |
AND bec.PROD_ID = pv.PV_ID
|
|
|
59 |
AND pv.IS_PATCH IS NULL
|
|
|
60 |
AND nn.NODE_TYPE_ID = nt.NODE_TYPE_ID
|
|
|
61 |
AND pv.PKG_ID = pkg.PKG_ID
|
|
|
62 |
AND bc.BOM_ID = :COMPARE_BOM_ID
|
|
|
63 |
),
|
|
|
64 |
newNodes as (
|
|
|
65 |
SELECT node_id from bom_contents where bom_id = :BOM_ID
|
|
|
66 |
),
|
|
|
67 |
newOS as (
|
|
|
68 |
SELECT os_id from operating_systems os, bom_contents bc where bc.bom_id = :BOM_ID and bc.node_id = os.node_id
|
|
|
69 |
)
|
|
|
70 |
SELECT newbom.*, oldbom.PROD_ID AS OPROD_ID, newNodes.node_id as nodeExists, newOS.os_id as osExists
|
|
|
71 |
from newbom
|
|
|
72 |
full outer join oldbom on newbom.PK = oldbom.PK
|
|
|
73 |
left outer join newNodes on newbom.node_id = newNodes.node_id
|
|
|
74 |
left outer join newOS on newbom.os_id = newOS.os_id
|
|
|
75 |
) where
|
|
|
76 |
PROD_ID = :PROD_ID
|
|
|
77 |
AND OPROD_ID IS NULL
|
|
|
78 |
ORDER BY UPPER(PK)
|
|
|
79 |
|
|
|
80 |
|
|
|
81 |
|
|
|
82 |
|