seq_fn_widget
seq_ep_endpoint
seq_ep_endpoints_basic_auth_account
seq_ep_widget_catalog_parameter
seq_ep_basic_auth_account
seq_ep_user_notification
seq_fn_user
seq_ep_microservice
seq_ep_microservice_parameter
seq_fn_app
seq_ep_usrappsrtprf
seq_ep_usrmanappsrtprf
seq_ep_usrwidgetsrtprf
seq_fn_role
app
seq_fn_pers_user_app_sel
seq_epp_app_role_func
seq_ep_pers_user_widget_sel
seq_ep_user_role_request
seq_ep_user_role_request_det
'Y'
and (upper(d.wdg_name) like upper(CONCAT('%', :searchQuery, '%')) or upper(d.wdg_desc) like upper(CONCAT('%', :searchQuery, '%')))
)
union
(
select distinct 'Widget' CATEGORY, d.wdg_name NAME,
d.widget_id UUID,
d.wdg_desc TARGET
from ep_widget_catalog d
where d.all_user_flag='Y'
and (upper(d.wdg_name) like upper(CONCAT('%', :searchQuery, '%')) or upper(d.wdg_desc) like upper(CONCAT('%', :searchQuery, '%')))
)
union
(
select distinct 'Widget' CATEGORY, d.wdg_name NAME,
d.widget_id UUID,
d.wdg_desc TARGET
from fn_user a, fn_user_role b, ep_widget_catalog d
where upper(a.org_user_id) = upper( :userId )
and a.user_id = b.user_id
and b.role_id = 1
and (upper(d.wdg_name) like upper(CONCAT('%', :searchQuery, '%')) or upper(d.wdg_desc) like upper(CONCAT('%', :searchQuery, '%')))
)
) y
) t,
(SELECT @rn /*'*/:=/*'*/ 0) t2 order by NAME
;
]]>
= start_time)
and a.is_for_all_roles = 'N'
) a,
(
select distinct a.user_id, c.role_id, c.app_id, d.APP_NAME
from fn_user a, fn_user_role b, fn_role c, fn_app d
where COALESCE(c.app_id,1) = d.app_id
and a.user_id = b.user_id
and a.user_id = :user_id
and b.role_id = c.role_id
and (d.enabled='Y' or d.app_id=1)
)b
where
(
a.role_id = b.role_id
)
union
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
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, a.notification_hyperlink,b.role_id,b.recv_user_id
from ep_notification a, ep_role_notification b
where a.notification_id = b.notification_id
and (end_time is null || SYSDATE() <= end_time )
and (start_time is null || SYSDATE() >= start_time)
and a.is_for_all_roles = 'N'
) a
where
(
a.recv_user_id=:user_id
)
union
(
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
from ep_notification a
where a.notification_id
and (end_time is null || SYSDATE() <= end_time )
and (start_time is null || SYSDATE() >= start_time)
and a.is_for_all_roles = 'Y'
)
) a
where
active_YN = 'Y'
and
not exists
(
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'
)
order by priority desc, created_date desc,start_Time desc
) t,
(SELECT @rn /*'*/:=/*'*/ 0) t2
;
]]>
1) b
union
select fn_role.app_id,fn_app.app_name, fn_role.role_id ,fn_role.role_name
from fn_app, fn_role
where fn_role.app_id = fn_app.app_id and fn_app.enabled='Y' and fn_role.active_yn='Y' order by app_name
]]>
= 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,
(
select distinct a.user_id, c.role_id, c.app_id, d.APP_NAME
from fn_user a, fn_user_role b, fn_role c, fn_app d
where COALESCE(c.app_id,1) = d.app_id
and a.user_id = b.user_id
and a.user_id = :user_id
and b.role_id = c.role_id
and (d.enabled='Y' or d.app_id=1)
) b
where
(
a.role_id = b.role_id
)
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,notification_hyperlink,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,a.notification_hyperlink,
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
)
union
(
select
:user_id user_id, b.login_id,notification_id, is_for_online_users, is_for_all_roles,
msg_header, msg_description,msg_source, start_Time, end_time, priority, a.created_date,
creator_ID, a.notification_hyperlink,a.active_YN
from ep_notification a JOIN fn_user b on b.user_id=a.creator_ID
where a.notification_id
and a.is_for_all_roles = 'Y'
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 left outer join (
select m.notification_ID, m.is_viewed from ep_user_notification m where user_id = :user_id
) m
on a.notification_id = m.notification_ID
where
active_YN = 'Y'
order by start_Time desc,end_time desc
) t,
(SELECT @rn /*'*/:=/*'*/ 0) t2 where startTime<=SYSDATE()
]]>
from CommonWidget where category = :cat order by
sortOrder, title
= DATE_ADD(end_time,INTERVAL 3 MONTH)
]]>
= DATE_ADD(end_time,INTERVAL 3 MONTH))
]]>
= DATE_ADD(end_time,INTERVAL 3 MONTH))
]]>