| 13 |
rsolanki |
1 |
/* Find in BOM */
|
|
|
2 |
SELECT DISTINCT
|
|
|
3 |
qry.RESULT_NAME,
|
|
|
4 |
qry.LOCATION,
|
|
|
5 |
qry.URL,
|
|
|
6 |
MIN(qry.display_order) OVER (PARTITION BY qry.RESULT_NAME) AS display_min
|
|
|
7 |
FROM (
|
|
|
8 |
|
|
|
9 |
/* Product Name at OS */
|
|
|
10 |
SELECT pkg.PKG_NAME AS RESULT_NAME,
|
|
|
11 |
'Location: '|| nn.NODE_NAME ||' / '|| os.OS_NAME ||' / '|| pkg.PKG_NAME ||' '|| pv.PKG_VERSION AS LOCATION,
|
|
|
12 |
'ProdDefault.asp?bom_id='|| :BOM_ID ||'&os_id='|| os.OS_ID ||'&prod_id='|| pv.PV_ID AS URL,
|
|
|
13 |
1 AS DISPLAY_ORDER
|
|
|
14 |
FROM OS_CONTENTS osc,
|
|
|
15 |
PACKAGES pkg,
|
|
|
16 |
PACKAGE_VERSIONS pv,
|
|
|
17 |
BOM_CONTENTS bc,
|
|
|
18 |
OPERATING_SYSTEMS os,
|
|
|
19 |
NETWORK_NODES nn
|
|
|
20 |
WHERE osc.OS_ID = os.OS_ID
|
|
|
21 |
AND os.NODE_ID = nn.NODE_ID
|
|
|
22 |
AND nn.NODE_ID = bc.NODE_ID
|
|
|
23 |
AND bc.BOM_ID = :BOM_ID
|
|
|
24 |
AND pv.PKG_ID = pkg.PKG_ID
|
|
|
25 |
AND osc.PROD_ID = pv.PV_ID
|
|
|
26 |
AND UPPER( pkg.PKG_NAME ) LIKE UPPER( :KEYWORD )
|
|
|
27 |
|
|
|
28 |
|
|
|
29 |
UNION
|
|
|
30 |
|
|
|
31 |
|
|
|
32 |
/* Product Name at Base Env */
|
|
|
33 |
SELECT pkg.PKG_NAME AS RESULT_NAME,
|
|
|
34 |
'Location: '|| nn.NODE_NAME ||' / '|| os.OS_NAME ||' / <br>'|| be.BASE_ENV_NAME ||'/'||pkg.PKG_NAME ||' '|| pv.PKG_VERSION AS LOCATION,
|
|
|
35 |
'Os_BaseConf.asp?bom_id='|| :BOM_ID ||'&os_id='|| os.OS_ID AS URL,
|
|
|
36 |
2 AS DISPLAY_ORDER
|
|
|
37 |
FROM BASE_ENV be,
|
|
|
38 |
BASE_ENV_CONTENTS bec,
|
|
|
39 |
OS_BASE_ENV obe,
|
|
|
40 |
NETWORK_NODES nn,
|
|
|
41 |
OPERATING_SYSTEMS os,
|
|
|
42 |
PACKAGES pkg,
|
|
|
43 |
PACKAGE_VERSIONS pv
|
|
|
44 |
WHERE be.BOM_ID = :BOM_ID
|
|
|
45 |
AND bec.BASE_ENV_ID = be.BASE_ENV_ID
|
|
|
46 |
AND pv.PKG_ID = pkg.PKG_ID
|
|
|
47 |
AND bec.PROD_ID = pv.PV_ID
|
|
|
48 |
AND obe.BASE_ENV_ID = be.BASE_ENV_ID
|
|
|
49 |
AND obe.OS_ID = os.OS_ID
|
|
|
50 |
AND os.NODE_ID = nn.NODE_ID
|
|
|
51 |
AND UPPER( pkg.PKG_NAME ) LIKE UPPER( :KEYWORD )
|
|
|
52 |
|
|
|
53 |
|
|
|
54 |
UNION
|
|
|
55 |
|
|
|
56 |
|
|
|
57 |
/* Network Node Name */
|
|
|
58 |
SELECT nn.NODE_NAME AS RESULT_NAME,
|
|
|
59 |
'Location: '|| nn.NODE_NAME AS LOCATION,
|
|
|
60 |
'NodeDefault.asp?bom_id='|| :BOM_ID ||'&node_id='|| nn.NODE_ID AS URL,
|
|
|
61 |
3 AS DISPLAY_ORDER
|
|
|
62 |
FROM BOM_CONTENTS bc,
|
|
|
63 |
NETWORK_NODES nn
|
|
|
64 |
WHERE nn.NODE_ID = bc.NODE_ID
|
|
|
65 |
AND UPPER( nn.NODE_NAME ) LIKE UPPER( :KEYWORD )
|
|
|
66 |
AND bc.BOM_ID = :BOM_ID
|
|
|
67 |
|
|
|
68 |
|
|
|
69 |
UNION
|
|
|
70 |
|
|
|
71 |
|
|
|
72 |
/* OS Name */
|
|
|
73 |
SELECT os.OS_NAME AS RESULT_NAME,
|
|
|
74 |
'Location: '|| nn.NODE_NAME ||' / '|| os.OS_NAME AS LOCATION,
|
|
|
75 |
'OsDefault.asp?bom_id='|| :BOM_ID ||'&os_id='|| os.OS_ID AS URL,
|
|
|
76 |
4 AS DISPLAY_ORDER
|
|
|
77 |
FROM BOM_CONTENTS bc,
|
|
|
78 |
OPERATING_SYSTEMS os,
|
|
|
79 |
NETWORK_NODES nn
|
|
|
80 |
WHERE os.NODE_ID = nn.NODE_ID
|
|
|
81 |
AND nn.NODE_ID = bc.NODE_ID
|
|
|
82 |
AND bc.BOM_ID = :BOM_ID
|
|
|
83 |
AND UPPER( os.OS_NAME ) LIKE UPPER( :KEYWORD )
|
|
|
84 |
|
|
|
85 |
) qry
|
|
|
86 |
ORDER BY display_min
|