| 13 |
rsolanki |
1 |
/* Bom Removed Product Location */
|
| 5634 |
dpurdie |
2 |
select * from (
|
|
|
3 |
WITH newbom AS (
|
|
|
4 |
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,
|
|
|
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 |
FROM OS_CONTENTS osc,
|
|
|
15 |
OPERATING_SYSTEMS os,
|
|
|
16 |
NETWORK_NODES nn,
|
|
|
17 |
NODE_TYPES nt,
|
|
|
18 |
BOM_CONTENTS bc,
|
|
|
19 |
PACKAGE_VERSIONS pv,
|
|
|
20 |
PACKAGES pkg
|
|
|
21 |
WHERE osc.OS_ID = os.OS_ID
|
|
|
22 |
AND os.NODE_ID = nn.NODE_ID
|
|
|
23 |
AND bc.NODE_ID = nn.NODE_ID
|
|
|
24 |
AND osc.PROD_ID = pv.PV_ID
|
|
|
25 |
AND pv.PKG_ID = pkg.PKG_ID
|
|
|
26 |
AND nn.NODE_TYPE_ID = nt.NODE_TYPE_ID
|
|
|
27 |
AND bc.BOM_ID = :BOM_ID
|
|
|
28 |
),
|
|
|
29 |
oldbom as (
|
|
|
30 |
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,
|
|
|
32 |
nt.NODE_ICON,
|
|
|
33 |
nn.NODE_ID,
|
|
|
34 |
nn.NODE_NAME,
|
|
|
35 |
os.OS_ID,
|
|
|
36 |
os.OS_NAME,
|
|
|
37 |
pkg.PKG_ID,
|
|
|
38 |
pkg.PKG_NAME,
|
|
|
39 |
pv.PKG_VERSION
|
|
|
40 |
FROM OS_CONTENTS osc,
|
|
|
41 |
OPERATING_SYSTEMS os,
|
|
|
42 |
NETWORK_NODES nn,
|
|
|
43 |
NODE_TYPES nt,
|
|
|
44 |
BOM_CONTENTS bc,
|
|
|
45 |
PACKAGE_VERSIONS pv,
|
|
|
46 |
PACKAGES pkg
|
|
|
47 |
WHERE osc.OS_ID = os.OS_ID
|
|
|
48 |
AND os.NODE_ID = nn.NODE_ID
|
|
|
49 |
AND bc.NODE_ID = nn.NODE_ID
|
|
|
50 |
AND osc.PROD_ID = pv.PV_ID
|
|
|
51 |
AND pv.PKG_ID = pkg.PKG_ID
|
|
|
52 |
AND nn.NODE_TYPE_ID = nt.NODE_TYPE_ID
|
|
|
53 |
AND bc.BOM_ID = :COMPARE_BOM_ID
|
|
|
54 |
),
|
|
|
55 |
newNodes as (
|
|
|
56 |
SELECT node_id from bom_contents where bom_id = :BOM_ID
|
|
|
57 |
),
|
|
|
58 |
newOS as (
|
|
|
59 |
SELECT os_id from operating_systems os, bom_contents bc where bc.bom_id = :BOM_ID and bc.node_id = os.node_id
|
|
|
60 |
)
|
|
|
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
|
|
|
62 |
from newbom
|
|
|
63 |
full outer join oldbom on newbom.PK = oldbom.PK
|
|
|
64 |
left outer join newNodes on newbom.node_id = newNodes.node_id
|
|
|
65 |
left outer join newOS on newbom.os_id = newOS.os_id
|
|
|
66 |
) where
|
|
|
67 |
OPROD_ID = :PROD_ID
|
|
|
68 |
AND NPROD_ID IS NULL
|
|
|
69 |
ORDER BY UPPER(PK)
|
| 29 |
jtweddle |
70 |
|
|
|
71 |
|
|
|
72 |
|
| 5634 |
dpurdie |
73 |
|