+@NamedNativeQueries({
+ @NamedNativeQuery(
+ name = "EpNotification.getNotifications",
+ query = "select rowId, 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,notification_hyperlink, active_YN from\n"
+ + "( \n"
+ + "select 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,notification_hyperlink,active_YN\n"
+ + "from\n"
+ + " (\n"
+ + " select user_id, notification_id, is_for_online_users, is_for_all_roles, msg_header, msg_description,msg_source,start_Time, end_time, priority, created_date,notification_hyperlink, creator_ID,active_YN\n"
+ + " from\n"
+ + " (\n"
+ + " select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN,\n"
+ + " a.msg_header,a.msg_description,a.msg_source,a.start_time,a.end_time,a.priority,a.creator_ID,a.notification_hyperlink,a.created_date,b.role_id,b.recv_user_id \n"
+ + " from ep_notification a, ep_role_notification b\n"
+ + " where a.notification_id = b.notification_id\n"
+ + " and (end_time is null || SYSDATE() <= end_time )\n"
+ + " and (start_time is null || SYSDATE() >= start_time)\n"
+ + " and a.is_for_all_roles = 'N'\n"
+ + " ) a,\n"
+ + " (\n"
+ + " select distinct a.user_id, c.role_id, c.app_id, d.APP_NAME\n"
+ + " from fn_user a, fn_user_role b, fn_role c, fn_app d\n"
+ + " where COALESCE(c.app_id,1) = d.app_id\n"
+ + " and a.user_id = b.user_id\n"
+ + " and a.user_id = :user_id\n"
+ + " and b.role_id = c.role_id\n"
+ + " and (d.enabled='Y' or d.app_id=1)\n"
+ + " )b\n"
+ + " where\n"
+ + " (\n"
+ + " a.role_id = b.role_id\n"
+ + " )\n"
+ + " union\n"
+ + " select :user_id, notification_id, is_for_online_users, is_for_all_roles, msg_header, msg_description,msg_source,start_Time, end_time, priority, created_date,notification_hyperlink, creator_ID,active_YN\n"
+ + " from\n"
+ + " (\n"
+ + " select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN,\n"
+ + " a.msg_header,a.msg_description,a.msg_source,a.start_time,a.end_time,a.priority,a.creator_ID,a.created_date, a.notification_hyperlink,b.role_id,b.recv_user_id \n"
+ + " from ep_notification a, ep_role_notification b\n"
+ + " where a.notification_id = b.notification_id\n"
+ + " and (end_time is null || SYSDATE() <= end_time )\n"
+ + " and (start_time is null || SYSDATE() >= start_time)\n"
+ + " and a.is_for_all_roles = 'N'\n"
+ + " ) a\n"
+ + " where\n"
+ + " (\n"
+ + " a.recv_user_id=:user_id\n"
+ + " )\n"
+ + " union\n"
+ + " (\n"
+ + " select :user_id user_id, notification_id, is_for_online_users, is_for_all_roles, msg_header, msg_description, msg_source,start_Time, end_time, priority, created_date,notification_hyperlink, creator_ID,active_YN\n"
+ + " from ep_notification a\n"
+ + " where a.notification_id\n"
+ + " and (end_time is null || SYSDATE() <= end_time )\n"
+ + " and (start_time is null || SYSDATE() >= start_time)\n"
+ + " and a.is_for_all_roles = 'Y'\n"
+ + " )\n"
+ + " ) a\n"
+ + " where\n"
+ + " active_YN = 'Y'\n"
+ + " and\n"
+ + " not exists\n"
+ + " (\n"
+ + " select ID,User_ID,notification_ID,is_viewed,updated_time from ep_user_notification m where user_id = :user_id and m.notification_id = a.notification_id and is_viewed = 'Y'\n"
+ + " )\n"
+ + " order by priority desc, created_date desc,start_Time desc\n"
+ + "\n"
+ + "\n"
+ + " ) t,\n")}
+)
+