| 5950 |
dpurdie |
1 |
-- =====================================================
|
|
|
2 |
-- WorkNotInProgressByUser.sql
|
|
|
3 |
-- Keep in step with 'UserDangle.sql'
|
|
|
4 |
--
|
|
|
5 |
-- Used by index.asp to list UnRelease Package Versions
|
|
|
6 |
-- modified by the user, but not in use anywhere
|
|
|
7 |
-- =====================================================
|
|
|
8 |
SELECT usr.USER_ID,
|
|
|
9 |
usr.FULL_NAME,
|
|
|
10 |
usr.USER_EMAIL,
|
|
|
11 |
usr.USER_NAME,
|
|
|
12 |
COUNT(usr.USER_ID) AS COUNTER
|
|
|
13 |
FROM (
|
|
|
14 |
/* UnReleased packages not in use */
|
|
|
15 |
SELECT pv.PV_ID, pv.MODIFIER_ID
|
|
|
16 |
FROM PACKAGE_VERSIONS pv
|
|
|
17 |
WHERE pv.pv_id not in (select pv_id from PLANNED )
|
|
|
18 |
AND pv.pv_id not in (select pv_id from WORK_IN_PROGRESS )
|
|
|
19 |
AND pv.pv_id not in (select pv_id from RELEASE_CONTENT )
|
|
|
20 |
AND pv.pv_id not in (select rtd_id from RUNTIME_DEPENDENCIES)
|
|
|
21 |
AND pv.pv_id not in ( SELECT pv_id FROM PACKAGE_PROCESSES )
|
|
|
22 |
--AND pv_id not in (select prod_id from deployment_manager.os_contents )
|
|
|
23 |
AND pv.dlocked != 'Y'
|
|
|
24 |
|
|
|
25 |
) rel,
|
|
|
26 |
USERS usr
|
|
|
27 |
WHERE rel.MODIFIER_ID = usr.USER_ID
|
|
|
28 |
GROUP BY usr.USER_ID, usr.FULL_NAME, usr.USER_EMAIL,usr.USER_NAME
|
|
|
29 |
-- order by UPPER(usr.FULL_NAME )
|
|
|
30 |
order by COUNTER DESC
|
|
|
31 |
|
|
|
32 |
|