| 119 |
ghuddy |
1 |
SELECT usr.full_name,
|
|
|
2 |
usr.user_email,
|
|
|
3 |
pkg.pkg_name,
|
|
|
4 |
pv.pkg_version,
|
|
|
5 |
pv.pv_id,
|
|
|
6 |
usr.user_id,
|
|
|
7 |
:RTAG_ID AS rtag_id,
|
|
|
8 |
/*ORA_SYSDATETIME*/ AS date_time_stamp
|
|
|
9 |
FROM package_versions pv,
|
|
|
10 |
packages pkg,
|
|
|
11 |
users usr,
|
|
|
12 |
(
|
|
|
13 |
|
|
|
14 |
(
|
|
|
15 |
/* Users to be notified with ready to build packages */
|
|
|
16 |
SELECT rel.pv_id, pv.owner_id
|
|
|
17 |
FROM release_content rel,
|
|
|
18 |
package_versions pv
|
|
|
19 |
WHERE rel.pv_id = pv.pv_id
|
|
|
20 |
AND rel.rtag_id = :RTAG_ID
|
|
|
21 |
AND rel.pkg_state IN (/*PKG_STATE_MAJOR_READY*/, /*PKG_STATE_MINOR_READY*/)
|
|
|
22 |
/* -------- Do not email current user! -------- */
|
|
|
23 |
AND pv.owner_id != :CURRENT_USER
|
|
|
24 |
/* -------------------------------------------- */
|
|
|
25 |
MINUS
|
|
|
26 |
/* Disregard packages with unofficial dependencies */
|
|
|
27 |
SELECT pv.pv_id, pv.owner_id
|
|
|
28 |
FROM package_dependencies dep,
|
|
|
29 |
package_versions dpv,
|
|
|
30 |
release_content rc,
|
|
|
31 |
package_versions pv,
|
|
|
32 |
(
|
|
|
33 |
SELECT pv.pkg_id, pv.v_ext
|
|
|
34 |
FROM release_content rc,
|
|
|
35 |
package_versions pv
|
|
|
36 |
WHERE rc.pv_id = pv.pv_id
|
|
|
37 |
AND rc.rtag_id = :RTAG_ID
|
|
|
38 |
AND pv.dlocked = 'N'
|
|
|
39 |
AND rc.pkg_state = /*PKG_STATE_OK*/
|
|
|
40 |
) nloc
|
|
|
41 |
WHERE dep.dpv_id = dpv.pv_id
|
|
|
42 |
AND dep.pv_id = rc.pv_id
|
|
|
43 |
AND rc.rtag_id = :RTAG_ID
|
|
|
44 |
AND rc.pkg_state IN (/*PKG_STATE_MAJOR_READY*/, /*PKG_STATE_MINOR_READY*/)
|
|
|
45 |
AND NVL(dpv.v_ext,'|LINK_A_NULL|') = NVL(nloc.v_ext,'|LINK_A_NULL|')
|
|
|
46 |
AND dpv.pkg_id = nloc.pkg_id
|
|
|
47 |
AND rc.pv_id = pv.pv_id
|
|
|
48 |
)
|
|
|
49 |
|
|
|
50 |
MINUS
|
|
|
51 |
/* Disregard users notified in last 8 hours */
|
|
|
52 |
SELECT nh.pv_id, nh.user_id
|
|
|
53 |
FROM notification_history nh
|
|
|
54 |
WHERE nh.rtag_id = :RTAG_ID
|
|
|
55 |
AND TO_DATE( TO_CHAR( nh.date_time_stamp, 'DD-MM-YYYY HH24:MI:SS'), 'DD-MM-YYYY HH24:MI:SS') >
|
|
|
56 |
TO_DATE( TO_CHAR( SYSDATE-(8/24),'DD-MM-YYYY HH24:MI:SS' ), 'DD-MM-YYYY HH24:MI:SS')
|
|
|
57 |
) utn
|
|
|
58 |
WHERE utn.pv_id = pv.pv_id
|
|
|
59 |
AND pv.pkg_id = pkg.pkg_id
|
|
|
60 |
AND pv.owner_id = usr.user_id
|
|
|
61 |
ORDER BY UPPER(usr.full_name)
|