| 119 |
ghuddy |
1 |
SELECT *
|
|
|
2 |
FROM
|
|
|
3 |
(
|
|
|
4 |
|
|
|
5 |
SELECT usr.user_id, usr.full_name, 0 AS pkg_count, pkg.pkg_name, pv.pkg_version, proj.proj_name, vt.vtree_name, rt.rtag_name, rt.rtag_id, pv.pv_id,
|
|
|
6 |
usr.user_email
|
|
|
7 |
FROM package_versions pv,
|
|
|
8 |
packages pkg,
|
|
|
9 |
release_content rc,
|
|
|
10 |
users usr,
|
|
|
11 |
projects proj,
|
|
|
12 |
vtrees vt,
|
|
|
13 |
release_tags rt
|
|
|
14 |
WHERE rc.pv_id = pv.pv_id
|
|
|
15 |
AND rc.rtag_id = rt.rtag_id
|
|
|
16 |
AND rt.vtree_id = vt.vtree_id
|
|
|
17 |
AND vt.proj_id = proj.proj_id
|
|
|
18 |
AND pv.pkg_id = pkg.pkg_id
|
|
|
19 |
AND pv.modifier_id = usr.user_id
|
|
|
20 |
AND TO_DATE( TO_CHAR( pv.modified_stamp,'DD-MON-YYYY HH24:MI:SS' ),'DD-MON-YYYY HH24:MI:SS' ) > TO_DATE( TO_CHAR( SYSDATE - 90,'DD-MON-YYYY HH24:MI:SS' ),'DD-MON-YYYY HH24:MI:SS' )
|
|
|
21 |
AND pv.dlocked = 'N'
|
|
|
22 |
AND vt.hide = 'N'
|
|
|
23 |
AND pv.pkg_version <> 'current_$USER'
|
|
|
24 |
AND usr.user_id = :USER_ID
|
|
|
25 |
|
|
|
26 |
UNION
|
|
|
27 |
|
|
|
28 |
SELECT usr.user_id, usr.full_name, COUNT(usr.user_id) AS pkg_count,
|
|
|
29 |
NULL AS pkg_name, NULL AS pkg_version, NULL AS proj_name, NULL AS vtree_name, NULL AS rtag_name, TO_NUMBER(NULL) AS rtag_id, TO_NUMBER(NULL) AS pv_id,
|
|
|
30 |
usr.user_email
|
|
|
31 |
FROM package_versions pv,
|
|
|
32 |
release_content rc,
|
|
|
33 |
users usr,
|
|
|
34 |
vtrees vt,
|
|
|
35 |
release_tags rt
|
|
|
36 |
WHERE rc.pv_id = pv.pv_id
|
|
|
37 |
AND rc.rtag_id = rt.rtag_id
|
|
|
38 |
AND rt.vtree_id = vt.vtree_id
|
|
|
39 |
AND pv.modifier_id = usr.user_id
|
|
|
40 |
AND TO_DATE( TO_CHAR( pv.modified_stamp,'DD-MON-YYYY HH24:MI:SS' ),'DD-MON-YYYY HH24:MI:SS' ) > TO_DATE( TO_CHAR( SYSDATE - 90,'DD-MON-YYYY HH24:MI:SS' ),'DD-MON-YYYY HH24:MI:SS' )
|
|
|
41 |
AND pv.dlocked = 'N'
|
|
|
42 |
AND vt.hide = 'N'
|
|
|
43 |
AND pv.pkg_version <> 'current_$USER'
|
|
|
44 |
GROUP BY usr.user_id, usr.full_name, usr.user_email
|
|
|
45 |
|
|
|
46 |
) wip
|
|
|
47 |
ORDER BY UPPER(wip.full_name), wip.pkg_count DESC, UPPER(wip.proj_name), UPPER(wip.vtree_name), UPPER(wip.rtag_id), UPPER(wip.pkg_name)
|
|
|
48 |
|
|
|
49 |
|
|
|
50 |
|