| 119 |
ghuddy |
1 |
SELECT *
|
|
|
2 |
FROM (
|
|
|
3 |
/* Hidden Views */
|
|
|
4 |
SELECT '0' AS show,
|
|
|
5 |
vi.view_id,
|
|
|
6 |
vi.view_name,
|
|
|
7 |
usr.user_name AS owner_name,
|
|
|
8 |
usr.user_email AS owner_email,
|
|
|
9 |
vi.base_view,
|
|
|
10 |
Count(vd.pkg_id) AS pkg_count,
|
|
|
11 |
vi.owner_id
|
|
|
12 |
FROM views vi, view_def vd, users usr
|
|
|
13 |
WHERE (vi.owner_id = 0 OR vi.public_read='Y')
|
|
|
14 |
AND usr.user_id = vi.owner_id
|
|
|
15 |
AND vi.view_id = vd.view_id(+)
|
|
|
16 |
AND vi.view_id NOT IN (SELECT vis.view_id FROM view_settings vis WHERE vis.user_id = $USER_ID$ )
|
|
|
17 |
GROUP BY vi.view_id, vi.view_name, usr.user_name, usr.user_email, vi.base_view, vi.owner_id
|
|
|
18 |
UNION
|
|
|
19 |
/* Shown Views */
|
|
|
20 |
SELECT '1' AS show,
|
|
|
21 |
vi.view_id,
|
|
|
22 |
vi.view_name,
|
|
|
23 |
usr.user_name AS owner_name,
|
|
|
24 |
usr.user_email AS owner_email,
|
|
|
25 |
vi.base_view,
|
|
|
26 |
Count(vd.pkg_id) AS pkg_count,
|
|
|
27 |
vi.owner_id
|
|
|
28 |
FROM views vi, view_settings vis, view_def vd, users usr
|
|
|
29 |
WHERE vi.view_id = vis.view_id
|
|
|
30 |
AND usr.user_id = vi.owner_id
|
|
|
31 |
AND vi.view_id = vd.view_id(+)
|
|
|
32 |
AND vis.user_id = $USER_ID$
|
|
|
33 |
AND vi.public_read = 'Y'
|
|
|
34 |
GROUP BY vi.view_id, vi.view_name, usr.user_name, usr.user_email, vi.base_view, vi.owner_id
|
|
|
35 |
) pview
|
|
|
36 |
ORDER BY pview.view_name ASC
|