| 13 |
rsolanki |
1 |
/* Compare BOM With Latest From Release Manager */
|
|
|
2 |
SELECT dmbom.pkg_id,
|
|
|
3 |
dmbom.PROD_ID,
|
|
|
4 |
dmbom.os_id,
|
|
|
5 |
pkg.pkg_name AS PROD_NAME,
|
|
|
6 |
nt.NODE_ICON,
|
|
|
7 |
nn.NODE_NAME ||' / '|| os.OS_NAME ||' / ' AS PROD_LOCATION,
|
|
|
8 |
dmbom.pkg_version AS PROD_VERSION,
|
|
|
9 |
DECODE( dmbom.pkg_version,
|
|
|
10 |
rml.pkg_version, NULL,
|
|
|
11 |
CASE WHEN rml.pv_id < dmbom.prod_id THEN
|
|
|
12 |
'Older ('|| rml.pkg_version ||')'
|
|
|
13 |
ELSE
|
|
|
14 |
rml.pkg_version
|
|
|
15 |
END
|
|
|
16 |
) AS RM_LATEST_VERSION,
|
|
|
17 |
rml.pv_id AS LATEST_PROD_ID,
|
|
|
18 |
( CASE WHEN rml.pv_id < dmbom.prod_id THEN
|
|
|
19 |
NULL
|
|
|
20 |
ELSE
|
|
|
21 |
'checked'
|
|
|
22 |
END ) AS CHECKED
|
|
|
23 |
FROM PACKAGES pkg,
|
|
|
24 |
OPERATING_SYSTEMS os,
|
|
|
25 |
NETWORK_NODES nn,
|
|
|
26 |
NODE_TYPES nt,
|
|
|
27 |
(
|
|
|
28 |
/* Latest packages in Release Manager release */
|
|
|
29 |
SELECT pv.pv_id, pv.pkg_id, pv.v_ext,
|
|
|
30 |
pv.pkg_version
|
|
|
31 |
FROM RELEASE_CONTENT rc,
|
|
|
32 |
PACKAGE_VERSIONS pv
|
|
|
33 |
WHERE rc.PV_ID = pv.PV_ID
|
|
|
34 |
AND rc.RTAG_ID = :RTAG_ID
|
|
|
35 |
) rml,
|
|
|
36 |
(
|
|
|
37 |
/* BOM Products */
|
|
|
38 |
SELECT osc.PROD_ID, pv.pkg_id, pv.V_EXT, osc.OS_ID,
|
|
|
39 |
pv.pkg_version
|
|
|
40 |
FROM OS_CONTENTS osc,
|
|
|
41 |
OPERATING_SYSTEMS os,
|
|
|
42 |
BOM_CONTENTS bc,
|
|
|
43 |
PACKAGE_VERSIONS pv
|
|
|
44 |
WHERE osc.OS_ID = os.OS_ID
|
|
|
45 |
AND bc.NODE_ID = os.NODE_ID
|
|
|
46 |
AND osc.PROD_ID = pv.PV_ID
|
|
|
47 |
AND bc.BOM_ID = :BOM_ID
|
|
|
48 |
AND pv.IS_PATCH IS NULL
|
|
|
49 |
) dmbom
|
|
|
50 |
WHERE dmbom.pkg_id = pkg.pkg_id
|
|
|
51 |
AND dmbom.OS_ID = os.OS_ID
|
|
|
52 |
AND os.NODE_ID = nn.NODE_ID
|
|
|
53 |
AND nn.NODE_TYPE_ID = nt.NODE_TYPE_ID
|
|
|
54 |
AND rml.pkg_version IS NOT NULL
|
|
|
55 |
AND rml.pv_id != dmbom.prod_id
|
|
|
56 |
AND rml.pkg_id (+) = dmbom.pkg_id
|
|
|
57 |
/* AND NVL( rml.v_ext (+) , 'LINK_NULL_VALUE' ) = NVL( dmbom.v_ext, 'LINK_NULL_VALUE' ) */
|
|
|
58 |
ORDER BY UPPER(pkg.pkg_name), UPPER(PROD_LOCATION)
|