+ UNION
+ select
+ :user_id, login_id,notification_id, is_for_online_users, is_for_all_roles,
+ msg_header, msg_description,msg_source, start_Time, end_time, priority, created_date,
+ creator_ID,active_YN
+ from
+ (
+ select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN,
+ a.msg_header,a.msg_description,a.msg_source,a.start_time,a.end_time,a.priority,a.creator_ID,a.created_date,
+ b.role_id,CASE WHEN a.creator_ID IS NOT NULL THEN u.org_user_id
+
+ ELSE NULL
+ END AS login_id,b.recv_user_id
+ from ep_notification a, ep_role_notification b,fn_user u
+ where a.notification_id = b.notification_id and (u.user_id=a.creator_ID OR a.creator_ID IS NULL)
+ and a.is_for_all_roles = 'N'
+ and (
+ (start_time is null and end_time is null and a.created_date >= DATE_ADD(curdate(),INTERVAL-31 DAY))
+ or
+ (start_time is not null and end_time is null and start_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
+ or
+ (start_time is null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
+ or
+ (start_time is not null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
+ )
+ ) a
+ where
+ (
+ a.recv_user_id=:user_id
+ )