3 ================================================================================
5 ================================================================================
6 Copyright (C) 2017 AT&T Intellectual Property
7 ================================================================================
8 Licensed under the Apache License, Version 2.0 (the "License");
9 you may not use this file except in compliance with the License.
10 You may obtain a copy of the License at
12 http://www.apache.org/licenses/LICENSE-2.0
14 Unless required by applicable law or agreed to in writing, software
15 distributed under the License is distributed on an "AS IS" BASIS,
16 WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
17 See the License for the specific language governing permissions and
18 limitations under the License.
19 ================================================================================
21 <!DOCTYPE hibernate-mapping PUBLIC
22 "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
23 "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
25 <!-- Publishes mappings and queries specific to the ECOMP Portal application. -->
26 <hibernate-mapping package="org.openecomp.portalapp.portal.domain">
28 <!-- Widget class mapping details -->
29 <class name="Widget" table="FN_WIDGET">
30 <id name="id" column="WIDGET_ID">
31 <generator class="native">
32 <param name="sequence">seq_fn_widget</param>
35 <property name="width" type="integer">
36 <column name="WDG_WIDTH" not-null="true" default="0"></column>
38 <property name="height" type="integer">
39 <column name="WDG_HEIGHT" not-null="true" default="0"></column>
41 <property name="url" type="string">
42 <column name="WDG_URL" not-null="true" default="?"></column>
44 <property name="name" type="string">
45 <column name="WDG_NAME" not-null="true" default="?"></column>
48 <property name="appId" type="long">
49 <column name="APP_ID" not-null="true"></column>
54 <class name="EPEndpoint" table="EP_ENDPOINTS">
55 <id name="id" column="id">
56 <generator class="native">
57 <param name="sequence">seq_ep_endpoint</param>
60 <property name="name" type="string">
61 <column name="url" not-null="true"></column>
66 <class name="EPEndpointAccount" table="EP_ENDPOINTS_BASIC_AUTH_ACCOUNT">
67 <id name="id" column="id">
68 <generator class="native">
69 <param name="sequence">seq_ep_endpoints_basic_auth_account</param>
72 <property name="ep_id" type="long">
73 <column name="ep_id"></column>
75 <property name="account_id" type="long">
76 <column name="account_id"></column>
80 <class name="WidgetCatalogParameter" table="EP_WIDGET_CATALOG_PARAMETER">
81 <id name="id" column="id">
82 <generator class="native">
83 <param name="sequence">seq_ep_widget_catalog_parameter</param>
86 <property name="widgetId" type="long">
87 <column name="widget_id"></column>
89 <property name="userId" type="long">
90 <column name="user_id" not-null="true"></column>
92 <property name="paramId" type="long">
93 <column name="param_id" not-null="true"></column>
96 <property name="user_value" type="string">
97 <column name="user_value" not-null="true"></column>
102 <class name="BasicAuthCredentials" table="ep_basic_auth_account">
103 <id name="id" column="id">
104 <generator class="native">
105 <param name="sequence">seq_ep_basic_auth_account</param>
108 <property name="applicationName" type="string">
109 <column name="ext_app_name" not-null="true" default="?"></column>
111 <property name="username" type="string">
112 <column name="username"></column>
114 <property name="password" type="string">
115 <column name="password"></column>
117 <property name="isActive" type="string">
118 <column name="active_yn" not-null="true" default="Y"></column>
122 <!-- EPUserNotification class mapping details -->
123 <class name="EPUserNotification" table="ep_user_notification">
124 <id name="id" column="id">
125 <generator class="native">
126 <param name="sequence">seq_ep_user_notification</param>
129 <property name="userId" column="User_ID" />
130 <property name="notificationId" column="notification_ID" />
131 <property name="viewed" column="is_viewed" />
132 <property name="updateTime" column="updated_time" />
136 <!-- User class mapping details -->
137 <class name="EPUser" table="FN_USER">
138 <id name="id" column="user_id">
139 <generator class="native">
140 <param name="sequence">seq_fn_user</param>
144 <property name="orgId" column="org_id" />
145 <property name="managerId" column="manager_id" />
146 <property name="firstName" column="first_name" />
147 <property name="middleInitial" column="middle_name" />
148 <property name="lastName" column="last_name" />
149 <property name="phone" column="phone" />
150 <property name="fax" column="fax" />
151 <property name="cellular" column="cellular" />
152 <property name="email" column="email" />
153 <property name="addressId" column="address_id" />
154 <property name="alertMethodCd" column="alert_method_cd" />
156 <property name="address1" column="address_line_1" />
157 <property name="address2" column="address_line_2" />
158 <property name="city" column="city" />
159 <property name="state" column="state_cd" />
160 <property name="zipCode" column="zip_code" />
161 <property name="country" column="country_cd" />
163 <property name="hrid" column="hrid" />
164 <property name="orgUserId" column="org_user_id" />
165 <property name="orgCode" column="org_code" />
166 <property name="loginId" column="login_id" />
167 <property name="loginPwd" column="login_pwd" />
168 <property name="lastLoginDate" column="last_login_date" type="timestamp" />
170 <property name="locationClli" column="location_clli" />
171 <property name="orgManagerUserId" column="org_manager_userid" />
172 <property name="company" column="company" />
173 <property name="department" column="department" />
174 <property name="departmentName" column="department_name" />
175 <property name="businessUnit" column="business_unit" />
176 <property name="businessUnitName" column="business_unit_name" />
177 <property name="jobTitle" column="job_title" />
178 <property name="siloStatus" column="silo_status" />
179 <property name="financialLocCode" column="fin_loc_code" />
181 <property name="active" column="active_yn" type="yes_no" />
182 <property name="internal" column="is_internal_yn" type="yes_no" />
184 <property name="created" type="timestamp" column="created_date" />
185 <property name="modified" type="timestamp" column="modified_date" />
187 <property name="createdId" column="created_id" />
188 <property name="modifiedId" column="modified_id" />
189 <property name="timeZoneId" column="timezone" />
191 <set name="EPUserApps" table="FN_USER_ROLE" lazy="false" sort="natural"
193 <key column="user_id" />
194 <one-to-many class="org.openecomp.portalapp.portal.domain.EPUserApp" />
199 <class name="MicroserviceData" table="ep_microservice">
200 <id name="id" column="id">
201 <generator class="native">
202 <param name="sequence">seq_ep_microservice</param>
206 <property name="name" type="string">
207 <column name="name" not-null="true" default="?"></column>
210 <property name="desc" type="string">
211 <column name="description" not-null="true" default="?"></column>
214 <property name="appId" type="long">
215 <column name="appId" not-null="true"></column>
218 <property name="url" type="string">
219 <column name="endpoint_url" not-null="true" default="?"></column>
222 <property name="securityType" type="string">
223 <column name="security_type" not-null="true" default="?"></column>
227 <property name="username" type="string">
228 <column name="username" not-null="true" default="?"></column>
231 <property name="password" type="string">
232 <column name="password" not-null="true" default="?"></column>
235 <property name="active" type="string">
236 <column name="active" not-null="true" default="Y"></column>
240 <class name="MicroserviceParameter" table="ep_microservice_parameter">
241 <id name="id" column="id">
242 <generator class="native">
243 <param name="sequence">seq_ep_microservice_parameter</param>
247 <property name="serviceId" type="long">
248 <column name="service_id" not-null="true"></column>
251 <property name="para_key" type="string">
252 <column name="para_key" not-null="true" default="?"></column>
255 <property name="para_value" type="string">
256 <column name="para_value" not-null="true" default="?"></column>
260 <!-- UserApp class mapping details -->
261 <class name="EPUserApp" table="fn_user_role">
263 <key-property name="userId" type="long">
264 <column name="user_id" precision="11" scale="0" />
266 <key-many-to-one name="app"
267 class="org.openecomp.portalapp.portal.domain.EPApp" column="app_id" />
268 <key-many-to-one name="role"
269 class="org.openecomp.portalapp.portal.domain.EPRole" column="role_id" />
271 <property name="priority" type="java.lang.Short">
272 <column name="priority" precision="4" scale="0" />
276 <!-- User App class mapping details -->
277 <class name="EPApp" table="fn_app">
278 <id name="id" column="app_id">
279 <generator class="native">
280 <param name="sequence">seq_fn_app</param>
283 <property name="name" type="string">
284 <column name="app_name" not-null="true" default="?"></column>
286 <property name="imageUrl" column="app_image_url" />
287 <property name="description" column="app_description" />
288 <property name="notes" column="app_notes" />
289 <property name="url" column="app_url" />
290 <property name="alternateUrl" column="app_alternate_url" />
291 <property name="appRestEndpoint" column="app_rest_endpoint" />
292 <property name="mlAppName" type="string">
293 <column name="ml_app_name" not-null="true" default="?"></column>
295 <property name="mlAppAdminId" type="string">
296 <column name="ml_app_admin_id" not-null="true" default="?"></column>
298 <property name="motsId" column="mots_id" />
299 <property name="appPassword" type="string">
300 <column name="app_password" not-null="true" default="?"></column>
302 <property name="thumbnail" column="thumbnail" />
303 <property name="username" type="string">
304 <column name="app_username" not-null="true" default="?"></column>
306 <property name="open" type="yes_no">
307 <column name="open" not-null="true" default="Y"></column>
309 <property name="enabled" type="yes_no">
310 <column name="enabled" not-null="true" default="N"></column>
312 <property name="uebTopicName" type="string">
313 <column name="ueb_topic_name"></column>
315 <property name="uebKey" type="string">
316 <column name="ueb_key"></column>
318 <property name="uebSecret" type="string">
319 <column name="ueb_secret"></column>
321 <property name="appType" type="integer">
322 <column name="app_type"></column>
325 <one-to-one name="contactUs" class="AppContactUs" cascade="save-update"></one-to-one>
329 <!-- EPUserAppsSortPreference class mapping details -->
330 <class name="EPUserAppsSortPreference" table="ep_pers_user_app_sort">
331 <id name="id" column="ID">
332 <generator class="native">
333 <param name="sequence">seq_ep_usrappsrtprf</param>
336 <property name="userId" column="USER_ID" />
337 <property name="sortPref" column="SORT_PREF" />
340 <!-- EPUserAppsManualSortPreference class mapping details -->
341 <class name="EPUserAppsManualSortPreference" table="ep_pers_user_app_man_sort">
342 <id name="id" column="ID">
343 <generator class="native">
344 <param name="sequence">seq_ep_usrmanappsrtprf</param>
347 <property name="userId" column="user_id"
348 unique-key="uk_1_ep_pers_user_app_man_sort" />
349 <property name="appId" column="app_id"
350 unique-key="uk_1_ep_pers_user_app_man_sort" />
351 <property name="appManualSortOrder" column="sort_order" type="int" />
354 <class name="EPWidgetsManualSortPreference" table="ep_pers_user_widget_placement">
355 <id name="id" column="ID">
356 <generator class="native">
357 <param name="sequence">seq_ep_usrwidgetsrtprf</param>
360 <property name="userId" column="user_id" />
361 <property name="widgetId" column="widget_id" />
362 <property name="widgetRow" column="x" />
363 <property name="widgetCol" column="y" />
364 <property name="widgetHeight" column="height" />
365 <property name="widgetWidth" column="width" />
368 <!-- User Role class mapping details -->
369 <class name="EPRole" table="FN_ROLE">
370 <id name="id" column="role_id">
371 <generator class="native">
372 <param name="sequence">seq_fn_role</param>
376 <property name="name" column="role_name" />
377 <property name="priority" column="priority" />
378 <property name="active" column="active_yn" type="yes_no" />
379 <!-- 2 lines below may be removed! -->
380 <property name="appId" column="APP_ID" />
381 <property name="appRoleId" column="APP_ROLE_ID" />
382 <!-- 2 lines above may be removed -->
384 <set name="roleFunctions" table="FN_ROLE_FUNCTION" lazy="false"
386 <key column="role_id" />
387 <many-to-many column="function_cd"
388 class="org.openecomp.portalsdk.core.domain.RoleFunction" />
391 <set name="childRoles" table="FN_ROLE_COMPOSITE" lazy="false"
393 <key column="parent_role_id" />
394 <many-to-many column="child_role_id"
395 class="org.openecomp.portalapp.portal.domain.EPRole" />
398 <set name="parentRoles" table="FN_ROLE_COMPOSITE" lazy="false"
400 <key column="child_role_id" />
401 <many-to-many column="parent_role_id"
402 class="org.openecomp.portalapp.portal.domain.EPRole" />
407 <!-- User App class mapping details -->
408 <class name="AppContactUs" table="fn_app_contact_us">
409 <id name="id" column="app_id">
410 <generator class="foreign">
411 <param name="property">app</param>
414 <property name="url" column="url" />
415 <property name="description" column="description" />
416 <property name="contactName" column="contact_name" />
417 <property name="contactEmail" column="contact_email" />
418 <property name="activeYN" column="active_yn" />
420 <one-to-one name="app" class="EPApp" constrained="true"></one-to-one>
424 <!-- Personalization of user app selections -->
425 <class name="PersUserAppSelection" table="fn_pers_user_app_sel">
426 <id name="id" column="id">
427 <generator class="native">
428 <param name="property">seq_fn_pers_user_app_sel</param>
431 <property name="userId" column="user_id" />
432 <property name="appId" column="app_id" />
433 <property name="statusCode" column="status_cd" />
436 <class name="PersUserWidgetSelection" table="ep_pers_user_widget_sel">
437 <id name="id" column="id">
438 <generator class="native">
439 <param name="property">seq_ep_pers_user_widget_sel</param>
442 <property name="userId" column="user_id" />
443 <property name="widgetId" column="widget_id" />
444 <property name="statusCode" column="status_cd" />
447 <!-- EPUserAppRolesRequest class mapping details -->
448 <class name="EPUserAppRolesRequest" table="ep_user_roles_request">
449 <id name="id" column="req_id">
450 <generator class="native">
451 <param name="sequence">seq_ep_user_role_request</param>
454 <property name="userId" column="user_id" />
455 <property name="appId" column="app_id" />
456 <property name="createdDate" column="created_date" type="timestamp" />
457 <property name="updatedDate" column="updated_date" type="timestamp" />
458 <property name="requestStatus" column="request_status" />
460 <set name="epRequestIdDetail" table="ep_user_roles_request_det"
461 lazy="false" cascade="save-update, persist" inverse="true" fetch="select">
462 <key column="req_id" />
464 class="org.openecomp.portalapp.portal.domain.EPUserAppRolesRequestDetail" />
468 <!-- EPUserAppRolesRequestDetail class mapping details -->
469 <class name="EPUserAppRolesRequestDetail" table="ep_user_roles_request_det">
470 <id name="id" column="id">
471 <generator class="native">
472 <param name="sequence">seq_ep_user_role_request_det</param>
475 <property name="reqRoleId" column="requested_role_id" />
476 <property name="reqType" column="request_type" />
477 <many-to-one name="epRequestIdData" fetch="select"
478 class="org.openecomp.portalapp.portal.domain.EPUserAppRolesRequest">
479 <column name="req_id" not-null="true" />
483 <!-- show the current user plus related users -->
484 <sql-query name="relatedUsers">
485 <return-scalar column="org_user_id" type="java.lang.String" />
487 select t.org_user_id from (
488 select distinct c.org_user_id org_user_id, c.last_login_date from
489 (select c.org_user_id, c.last_login_date from
491 (select distinct role_id, app_id from fn_user_role a1, fn_user a2 where a1.user_id = a2.user_id and a2.org_user_id=:userId ) b,
493 where a.ROLE_ID = b.role_id
494 and a.APP_ID = b.app_id
495 and a.USER_ID = c.user_id
497 select org_user_id , last_login_date from fn_user where org_user_id=:userId
499 order by c.last_login_date desc limit 10
504 <!-- requires values for named parameters :searchQuery and :userId -->
505 <sql-query name="searchPortal">
506 <return alias="searchResult"
507 class="org.openecomp.portalapp.portal.ecomp.model.SearchResultItem" />
509 select @rn /*'*/:=/*'*/ @rn+1 AS rowId, CATEGORY, NAME, UUID, TARGET from
511 select distinct 'User' CATEGORY, concat(concat(first_name, ' '), last_name) NAME, user_id UUID, org_user_id TARGET
513 upper( :searchQuery ) != ''
514 and (upper(first_name) like upper(CONCAT('%', :searchQuery, '%'))
515 or upper(last_name) like upper(CONCAT('%', :searchQuery, '%'))
516 or upper(org_user_id) like upper(CONCAT('%', :searchQuery, '%')))
517 and upper(active_yn) = 'Y'
521 select distinct 'Application' CATEGORY, b1.app_name NAME,
522 if (b1.app_type = 2,'false','true') UUID,
523 if (a1.app_id is null, if (b1.app_type = 2, b1.APP_URL, ''), a1.APP_URL) TARGET
527 from fn_user_role a, fn_user b, fn_app c
528 where a.USER_ID = b.user_id
529 and upper(b.org_user_id) = upper( :userId )
530 and a.app_id = c.app_id
531 and upper(c.APP_NAME) like upper(CONCAT('%', :searchQuery, '%'))
532 and upper(c.ENABLED) = 'Y'
533 ) a1 right outer join
534 (select * from fn_app where
535 upper(APP_NAME) like upper(CONCAT('%', :searchQuery, '%'))
536 and upper(ENABLED) = 'Y') b1
537 on a1.APP_ID = b1.app_id
541 select distinct 'Menu' CATEGORY, b1.TEXT NAME,
542 if (b1.app_type = 2,'false','true') UUID,
543 if (a1.menu_id is null , if (b1.app_type = 2, b1.URL, '') , a1.URL ) TARGET
545 (select distinct d.*, e.app_type from fn_user a,
546 fn_user_role b, fn_menu_functional_roles c, fn_menu_functional d, fn_app e
547 where a.USER_ID = b.user_id
548 and b.role_id = c.role_id
549 and c.menu_id = d.menu_id
550 and upper(a.org_user_id) = upper( :userId )
551 and c.APP_ID = e.app_id
552 and upper(d.TEXT) like upper(CONCAT('%', :searchQuery, '%'))
553 and upper(d.active_yn) = 'Y'
554 and upper(e.enabled) = 'Y'
555 ) a1 right outer join
557 select distinct a.*, c.app_type from fn_menu_functional a, fn_menu_functional_roles b, fn_app c
558 where active_yn = 'Y'
559 and upper(TEXT) like upper(CONCAT('%', :searchQuery, '%'))
560 and a.menu_id = b.menu_id
561 and b.app_id = c.app_id
564 on a1.menu_id = b1.menu_id
567 select distinct CATEGORY, NAME, UUID, TARGET
571 select distinct 'Widget' CATEGORY, d.wdg_name NAME,
574 from fn_user a, fn_user_role b, ep_widget_catalog_role c, ep_widget_catalog d
575 where upper(a.org_user_id) = upper( :userId )
576 and a.user_id = b.user_id
577 and b.role_id = c.role_id
578 and c.widget_id = d.widget_id
579 and d.all_user_flag<>'Y'
580 and (upper(d.wdg_name) like upper(CONCAT('%', :searchQuery, '%')) or upper(d.wdg_desc) like upper(CONCAT('%', :searchQuery, '%')))
584 select distinct 'Widget' CATEGORY, d.wdg_name NAME,
587 from ep_widget_catalog d
588 where d.all_user_flag='Y'
589 and (upper(d.wdg_name) like upper(CONCAT('%', :searchQuery, '%')) or upper(d.wdg_desc) like upper(CONCAT('%', :searchQuery, '%')))
593 select distinct 'Widget' CATEGORY, d.wdg_name NAME,
596 from fn_user a, fn_user_role b, ep_widget_catalog d
597 where upper(a.org_user_id) = upper( :userId )
598 and a.user_id = b.user_id
600 and (upper(d.wdg_name) like upper(CONCAT('%', :searchQuery, '%')) or upper(d.wdg_desc) like upper(CONCAT('%', :searchQuery, '%')))
604 (SELECT @rn /*'*/:=/*'*/ 0) t2 order by NAME
610 <!-- requires values for named parameters :searchQuery and :userId -->
611 <sql-query name="getNotifications">
612 <return alias="notificationResult"
613 class="org.openecomp.portalapp.portal.transport.EpNotificationItem" />
615 select @rn /*'*/:=/*'*/ @rn+1 AS 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, active_YN from
618 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,active_YN
621 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, creator_ID,active_YN
624 select a.*, b.role_id,b.recv_user_id
625 from ep_notification a, ep_role_notification b
626 where a.notification_id = b.notification_id
627 and (end_time is null || SYSDATE() <= end_time )
628 and (start_time is null || SYSDATE() >= start_time)
629 and a.is_for_all_roles = 'N'
632 select a.user_id, c.role_id, c.app_id, d.APP_NAME
633 from fn_user a, fn_user_role b, fn_role c, fn_app d
634 where a.user_id = b.user_id
635 and a.user_id = :user_id
636 and b.role_id = c.role_id
637 and c.app_id = d.app_id
642 a.role_id = b.role_id||a.recv_user_id=:user_id
646 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, creator_ID,active_YN
647 from ep_notification a
648 where a.notification_id
649 and (end_time is null || SYSDATE() <= end_time )
650 and (start_time is null || SYSDATE() >= start_time)
651 and a.is_for_all_roles = 'Y'
659 select * from ep_user_notification m where user_id = :user_id and m.notification_id = a.notification_id and is_viewed = 'Y'
661 order by priority desc, start_Time desc,created_date desc
665 (SELECT @rn /*'*/:=/*'*/ 0) t2
671 <sql-query name="getUsersByOrgIdsNotifications">
672 <return alias="getUsersByOrgIdsNotificationsResult" class="org.openecomp.portalapp.portal.domain.EPUser"/>
674 SELECT * from fn_user where org_user_id in (:OrgIds)
680 <!-- Gets all Admin notification history; accepts no parameters -->
681 <sql-query name="getAdminNotificationHistoryVO">
682 <return alias="adminNotificationHistoryVOResult"
683 class="org.openecomp.portalapp.portal.transport.EpNotificationItemVO" />
686 n.notification_ID AS notificationId,
687 n.is_for_online_users AS isForOnlineUsers,
688 n.is_for_all_roles AS isForAllRoles,
689 n.active_YN AS activeYn,
690 n.msg_header AS msgHeader,
691 n.msg_description AS msgDescription,
692 n.msg_source AS msgSource,
693 n.start_Time AS startTime,
694 n.end_time AS endTime,
696 n.creator_ID AS creatorId,
697 n.created_date AS createdDate,
698 u.org_user_id AS loginId
702 fn_user u on u.user_id = n.creator_id
704 n.active_YN='Y' order by n.start_time desc
708 <sql-query name="getEpNotificationAppRoles">
709 <return alias="notificationAppRoles"
710 class="org.openecomp.portalapp.portal.domain.EcompAppRole" />
711 <!-- This query requires no parameters. -->
714 select a.app_id, a.app_name, b.role_id, b.role_name from
715 (select * from fn_app where app_id = 1) a,
716 (select * from fn_role where app_id is null and active_yn = 'Y' and role_id <> 1) b
718 select fn_role.app_id,fn_app.app_name, fn_role.role_id ,fn_role.role_name
720 where fn_role.app_id = fn_app.app_id and fn_app.enabled='Y' order by app_name
727 <!-- Gets all notification history -->
728 <sql-query name="getNotificationHistoryVO">
729 <return alias="notificationHistoryVOResult"
730 class="org.openecomp.portalapp.portal.transport.EpNotificationItemVO" />
733 notificationId, isForOnlineUsers, isForAllRoles, msgHeader, msgDescription,msgSource,
734 startTime, endTime, priority, createdDate, creatorId, loginId, activeYn
738 a.notification_ID AS notificationId,
739 is_for_online_users AS isForOnlineUsers,
740 is_for_all_roles AS isForAllRoles,
741 msg_header AS msgHeader,
742 msg_description AS msgDescription,
743 msg_source AS msgSource,
744 start_Time AS startTime,
747 created_date AS createdDate,
748 creator_ID AS creatorId,
750 active_YN AS activeYn,
751 if (is_viewed is null, 'N', is_viewed)
755 user_id, login_id,notification_id, is_for_online_users, is_for_all_roles,
756 msg_header, msg_description,msg_source, start_Time, end_time, priority, created_date,
760 select a.*, b.role_id,CASE WHEN a.creator_ID IS NOT NULL THEN u.org_user_id
763 END AS login_id,b.recv_user_id
764 from ep_notification a, ep_role_notification b,fn_user u
765 where a.notification_id = b.notification_id and (u.user_id=a.creator_ID OR a.creator_ID IS NULL)
766 and a.is_for_all_roles = 'N'
768 (start_time is null and end_time is null and a.created_date >= DATE_ADD(curdate(),INTERVAL-31 DAY))
770 (start_time is not null and end_time is null and start_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
772 (start_time is null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
774 (start_time is not null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
778 select a.user_id, c.role_id, c.app_id, d.APP_NAME
779 from fn_user a, fn_user_role b, fn_role c, fn_app d
780 where a.user_id = b.user_id
781 and a.user_id = :user_id
782 and b.role_id = c.role_id
783 and c.app_id = d.app_id
788 a.role_id = b.role_id||a.recv_user_id=:user_id
793 :user_id user_id, b.login_id,notification_id, is_for_online_users, is_for_all_roles,
794 msg_header, msg_description,msg_source, start_Time, end_time, priority, a.created_date,
795 creator_ID, a.active_YN
796 from ep_notification a JOIN fn_user b on b.user_id=a.creator_ID
797 where a.notification_id
798 and a.is_for_all_roles = 'Y'
800 (start_time is null and end_time is null and a.created_date >= DATE_ADD(curdate(),INTERVAL-31 DAY))
802 (start_time is not null and end_time is null and start_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
804 (start_time is null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
806 (start_time is not null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
809 ) a left outer join (
810 select m.notification_ID, m.is_viewed from ep_user_notification m where user_id = :user_id
812 on a.notification_id = m.notification_ID
816 order by start_Time desc,end_time desc
818 (SELECT @rn /*'*/:=/*'*/ 0) t2 where startTime<=SYSDATE()
823 <!-- shows the received recipient to whom the notification is delivered from external system -->
824 <sql-query name="messageRecipients">
825 <return-scalar column="org_user_id" type="java.lang.String" />
827 select u.org_user_id from ep_notification n join ep_role_notification r on r.notification_ID=n.notification_ID join fn_user u on u.user_id=r.recv_user_id where n.notification_id=:notificationId
832 <!-- Gets role details for a specified notification -->
833 <sql-query name="getNotificationRoles">
834 <return alias="notificationRolesResult"
835 class="org.openecomp.portalapp.portal.transport.EpRoleNotificationItem" />
841 notification_Id = :notificationId
845 <!-- Gets all applications, possibly with contact information -->
846 <sql-query name="getAppsAndContacts">
847 <return alias="appContactUs"
848 class="org.openecomp.portalapp.portal.ecomp.model.AppContactUsItem" />
849 <!-- This query requires no parameters. -->
852 a.app_id as appId, a.app_name as appName,
853 c.contact_name as contactName,
854 c.contact_email as contactEmail, c.url, c.description,
855 c.active_yn as activeYN
860 on a.app_id = c.app_id
862 a.enabled = 'Y' and a.app_name is not null and a.app_name != ''
867 <!-- Gets all rows from the app-contact-us table, extended with app information -->
868 <sql-query name="getAppContactUsItems">
869 <return alias="appContactUs"
870 class="org.openecomp.portalapp.portal.ecomp.model.AppContactUsItem" />
871 <!-- This query requires no parameters. -->
874 c.app_id as appId, c.contact_name as contactName,
875 c.contact_email as contactEmail, c.url, c.description,
876 c.active_yn as activeYN, a.app_name as appName
881 on a.app_id = c.app_id
883 a.enabled = 'Y' and a.app_name is not null and a.app_name != ''
888 <!-- Gets one row for each app with the category and catenated functions. -->
889 <sql-query name="getAppCategoryFunctions">
890 <return alias="appCategoryFunctions"
891 class="org.openecomp.portalapp.portal.ecomp.model.AppCategoryFunctionsItem" />
892 <!-- This query requires no parameters. -->
894 SELECT @rn /*'*/:=/*'*/ @rn+1 AS rowId,
895 r.category as category, r.app_id as appId, r.app_name as application,
896 group_concat(function_text separator ', ') as functions
899 app_id, function_text, app_name,
900 IF (a.parent_menu_id IS NULL, fn.parent_menu, a.text) category
903 j.text function_text, j.menu_id function_id, j.app_id app_id, j.app_name app_name,
904 k.text parent_menu, k.parent_menu_id parent_menu_id
907 x.Text, x.PARENT_MENU_ID, x.menu_id, y.app_id, z.app_name
909 fn_menu_functional x, fn_menu_functional_roles y, fn_app z
911 x.MENU_ID = y.menu_id AND x.ACTIVE_YN = 'Y' AND y.app_id = z.app_id
914 WHERE j.parent_menu_id = k.menu_id
917 WHERE fn.parent_menu_id = a.menu_id
919 (SELECT @rn /*'*/:=/*'*/ 0) t2
920 group by r.category, r.app_id, r.app_name
921 order by category, app_name
926 <!-- Gets one row for each function-application-role combination. -->
927 <sql-query name="getAppAccessFunctionRole">
928 <return alias="appAccessFunctionRole"
929 class="org.openecomp.portalapp.portal.domain.GetAccessResult" />
930 <!-- This query requires one parameters. -->
932 select @rn /*'*/:=/*'*/ @rn+1 AS row_id, t.role_id, t.ECOMP_FUNCTION, t.APP_NAME, t.APP_MOTS_ID, t.ROLE_NAME,
933 (select (case when fur.role_id is not null then 'Y' end) from fn_user_role fur where fur.user_id =:userId and fur.role_id = t.role_id and fur.app_id = t.app_id) as role_actv,
934 o.requested_role_id,o.request_type
936 (select ECOMP_FUNCTION, APP_NAME, APP_MOTS_ID, ROLE_NAME ,a.app_id,a.role_id
939 select D.APP_NAME, D.MOTS_ID APP_MOTS_ID, C.ROLE_NAME, d.app_id , c.role_id
942 where COALESCE(c.app_id,1) = d.app_id
943 and (d.enabled='Y' or d.app_id=1)
944 and c.active_yn = 'Y'
947 select A.TEXT ECOMP_FUNCTION, b.role_id, b.app_id from fn_menu_functional A, fn_menu_functional_roles B
949 A.MENU_ID = B.MENU_ID
952 on a.role_id = b.role_id) t left outer join
953 (select ed.requested_role_id, er.app_id ,ed.request_type from ep_user_roles_request er , ep_user_roles_request_det ed
954 where er.req_id=ed.req_id
955 and upper(ed.request_type)='P'
956 and er.user_id =:userId) o
958 and t.role_id = o.requested_role_id
959 JOIN (SELECT @rn /*'*/:=/*'*/ 0) p
964 <query name="getCommonWidgetItem">
965 from CommonWidget where category = :cat order by
969 <sql-query name="getGuestLastLogin">
970 <return-scalar column="audit_date" type="java.util.Date" />
972 select audit_date from fn_audit_log where affected_record_id =:userId order by audit_date desc limit 2;
976 <!-- Gets personalized list of enabled and accessible apps for regular user -->
977 <sql-query name="getPersUserApps">
978 <return alias="persUserApps" class="org.openecomp.portalapp.portal.domain.EPApp" />
979 <!-- This query requires one parameter: userId (number) -->
982 distinct -- multiple roles yield multiple rows
983 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
984 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
985 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
986 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
987 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE
989 -- Portal assigns role 999 to app administrator
990 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
991 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
992 where a.ENABLED = 'Y'
994 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
996 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1003 <!-- Gets personalized list of enabled and accessible apps for Portal (super)
1005 <sql-query name="getPersAdminApps">
1006 <return alias="persAdminApps" class="org.openecomp.portalapp.portal.domain.EPApp" />
1007 <!-- This query requires one parameter: userId (number) -->
1010 distinct -- multiple roles yield multiple rows
1011 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1012 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1013 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1014 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1015 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE
1017 -- Portal assigns role 999 to app administrator
1018 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1019 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1020 where a.ENABLED = 'Y'
1022 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1024 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1026 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1033 <!-- Gets personalized list of enabled and accessible Admin apps by name -->
1034 <sql-query name="getPersAdminAppsOrderByName">
1035 <return alias="AdminAppsOrderByName" class="org.openecomp.portalapp.portal.domain.EPApp" />
1036 <!-- This query requires one parameter: userId (number) -->
1039 distinct -- multiple roles yield multiple rows
1040 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1041 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1042 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1043 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1044 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE
1046 -- Portal assigns role 999 to app administrator
1047 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1048 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1049 where a.ENABLED = 'Y'
1051 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1053 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1055 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1063 <!--Gets personalized list of enabled and accessible User apps by name -->
1064 <sql-query name="getPersUserAppsOrderByName">
1065 <return alias="UserAppsOrderByName" class="org.openecomp.portalapp.portal.domain.EPApp" />
1066 <!-- This query requires one parameter: userId (number) -->
1069 distinct -- multiple roles yield multiple rows
1070 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1071 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1072 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1073 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1074 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE
1076 -- Portal assigns role 999 to app administrator
1077 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1078 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1079 where a.ENABLED = 'Y'
1081 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1083 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1092 <!--Gets personalized list of enabled and accessible Super Admin apps by
1094 <sql-query name="getAdminAppsOrderByLastUsed">
1095 <return alias="AdminAppsOrderByLastUsed" class="org.openecomp.portalapp.portal.domain.EPApp" />
1101 distinct -- multiple roles yield multiple rows
1102 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1103 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1104 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1105 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1106 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE
1108 -- Portal assigns role 999 to app administrator
1109 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1110 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1111 where a.ENABLED = 'Y'
1113 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1115 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1117 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1122 c.AFFECTED_RECORD_ID, c.AUDIT_DATE
1125 on A.app_id = B.Affected_record_id
1126 order by AUDIT_DATE DESC;
1130 <!--Gets personalized list of enabled and accessible User apps by LastUsed -->
1131 <sql-query name="getUserAppsOrderByLastUsed">
1132 <return alias="UserAppsOrderByLastUsed" class="org.openecomp.portalapp.portal.domain.EPApp" />
1135 select * from (select
1136 distinct -- multiple roles yield multiple rows
1137 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1138 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1139 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1140 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1141 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE
1143 -- Portal assigns role 999 to app administrator
1144 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1145 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1146 where a.ENABLED = 'Y'
1148 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1150 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1154 c.AFFECTED_RECORD_ID, c.AUDIT_DATE
1157 on A.app_id = B.Affected_record_id
1158 order by AUDIT_DATE DESC;
1165 <!--Gets personalized list of enabled and accessible Super Admin apps by
1167 <sql-query name="getAdminAppsOrderByMostUsed">
1168 <return alias="AdminAppsOrderByMostUsed" class="org.openecomp.portalapp.portal.domain.EPApp" />
1174 distinct -- multiple roles yield multiple rows
1175 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1176 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1177 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1178 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1179 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE
1181 -- Portal assigns role 999 to app administrator
1182 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1183 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1184 where a.ENABLED = 'Y'
1186 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1188 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1190 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1194 (select FN_AUDIT_LOG.AFFECTED_RECORD_ID, COUNT(*) AS total_visits
1195 from FN_APP LEFT JOIN FN_AUDIT_LOG
1196 on FN_APP.APP_ID = FN_AUDIT_LOG.AFFECTED_RECORD_ID
1197 where FN_APP.ENABLED = 'Y' and FN_AUDIT_LOG.user_id=:userId
1198 and DATE(audit_date) BETWEEN (CURDATE() - INTERVAL 90 DAY) and CURDATE()
1199 group by FN_AUDIT_LOG.AFFECTED_RECORD_ID
1201 on A.app_id = B.Affected_record_id
1202 order by total_visits DESC;
1207 <!--Gets personalized list of enabled and accessible NON Super Admin User
1208 apps by Most Used -->
1209 <sql-query name="getUserAppsOrderByMostUsed">
1210 <return alias="UserAppsOrderByMostUsed" class="org.openecomp.portalapp.portal.domain.EPApp" />
1215 distinct -- multiple roles yield multiple rows
1216 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1217 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1218 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1219 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1220 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE
1222 -- Portal assigns role 999 to app administrator
1223 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1224 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1225 where a.ENABLED = 'Y'
1227 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1229 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1232 (select FN_AUDIT_LOG.AFFECTED_RECORD_ID, COUNT(*) AS total_visits
1233 from FN_APP LEFT JOIN FN_AUDIT_LOG
1234 on FN_APP.APP_ID = FN_AUDIT_LOG.AFFECTED_RECORD_ID
1235 where FN_APP.ENABLED = 'Y' and FN_AUDIT_LOG.user_id=:userId
1236 and DATE(audit_date) BETWEEN (CURDATE() - INTERVAL 90 DAY) and CURDATE()
1237 group by FN_AUDIT_LOG.AFFECTED_RECORD_ID
1239 on A.app_id = B.Affected_record_id
1240 order by total_visits DESC;
1244 <!--Gets personalized list of enabled and accessible Super Admin apps by
1246 <sql-query name="getAdminAppsOrderByManual">
1247 <return alias="AdminAppsOrderByManual" class="org.openecomp.portalapp.portal.domain.EPApp" />
1252 distinct -- multiple roles yield multiple rows
1253 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1254 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1255 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1256 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1257 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE
1259 -- Portal assigns role 999 to app administrator
1260 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1261 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1262 where a.ENABLED = 'Y'
1264 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1266 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1268 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1271 (select m.app_id, m.sort_order
1272 from ep_pers_user_app_man_sort m
1273 where USER_ID = :userId
1275 on A.APP_ID = B.app_id
1276 order by sort_order ASC
1282 <!--Gets personalized list of enabled and accessible NON Super admin User
1284 <sql-query name="getUserAppsOrderByManual">
1285 <return alias="UserAppsOrderByManual" class="org.openecomp.portalapp.portal.domain.EPApp" />
1288 select * from (select
1289 distinct -- multiple roles yield multiple rows
1290 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1291 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1292 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1293 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1294 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE
1296 -- Portal assigns role 999 to app administrator
1297 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1298 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1299 where a.ENABLED = 'Y'
1301 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1303 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1306 (select m.app_id, m.sort_order
1307 from ep_pers_user_app_man_sort m
1308 where USER_ID = :userId
1310 on A.APP_ID = B.app_id
1311 order by sort_order ASC
1316 <!-- Gets regular user's list of enabled apps including accessible and select
1318 <sql-query name="getUserAppCatalog">
1319 <return alias="userAppCatalog"
1320 class="org.openecomp.portalapp.portal.ecomp.model.AppCatalogItem" />
1321 <!-- This query requires one parameter: userId (number) -->
1324 distinct -- multiple roles yield multiple rows
1325 a.APP_ID as id, a.APP_NAME as name, a.APP_IMAGE_URL as imageUrl,
1326 a.APP_DESCRIPTION as description, a.APP_NOTES as notes,
1327 a.APP_URL as url, a.APP_ALTERNATE_URL as alternateUrl,
1328 IF(a.app_type = '2', TRUE, FALSE) as restricted,
1329 IF(a.open = 'Y', TRUE, FALSE) as open,
1330 -- ACCESS(-ible) means user has a defined role OR the application is open
1332 -- regular app and user has a role
1333 when a.OPEN = 'N' and r.ROLE_ID is not null then TRUE
1335 when a.OPEN = 'Y' then TRUE
1338 -- SELECT(-ed) indicates user personalization
1340 -- regular app, user has a role, no personalization
1341 when a.OPEN = 'N' and r.ROLE_ID is not null and p.STATUS_CD is null then TRUE
1342 -- open app and has personalization
1343 when a.OPEN = 'Y' and p.STATUS_CD is not null then TRUE
1346 -- PENDING indicates user checked a box
1348 when a.OPEN = 'N' and q.request_status = 'P' then TRUE
1352 -- Portal assigns role 999 to app administrator
1353 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1354 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1355 left outer join EP_USER_ROLES_REQUEST q ON a.APP_ID = q.APP_ID and q.USER_ID = :userId and q.request_status = 'P'
1356 where a.ENABLED = 'Y'
1358 -- Show accessible apps first, then the rest; sort by name within each set.
1359 order by access desc, app_name asc
1364 <sql-query name="getAppDetails">
1365 <return alias="appDetails" class="org.openecomp.portalapp.portal.domain.EPApp" />
1369 SELECT * FROM fn_app where app_name =:appName
1375 <sql-query name="deleteAccountEndpoint">
1378 DELETE FROM ep_endpoints_basic_auth_account WHERE account_id =:accountId
1383 <sql-query name="deleteAccountEndpointRecord">
1386 DELETE FROM ep_endpoints_basic_auth_account WHERE account_id =:accountId AND ep_id =:epId
1391 <sql-query name="deleteEPEndpoint">
1394 DELETE FROM ep_endpoints WHERE id =:epId
1399 <sql-query name="deleteBasicAuthAccount">
1402 DELETE FROM ep_basic_auth_account WHERE id =:accountId
1407 <sql-query name="getAppRoles">
1408 <return alias="appRoles" class="org.openecomp.portalapp.portal.domain.EPRole" />
1412 SELECT * FROM fn_role where role_name =:roleName and app_id =:appId
1418 <sql-query name="deleteMicroserviceParameter">
1421 DELETE FROM ep_microservice_parameter WHERE service_id =:serviceId
1426 <sql-query name="deleteMicroserviceParameterById">
1429 DELETE FROM ep_microservice_parameter WHERE id =:paramId
1434 <sql-query name="deleteWidgetCatalogParameter">
1437 DELETE FROM ep_widget_catalog_parameter WHERE param_id =:paramId
1442 <sql-query name="deleteMicroservice">
1445 DELETE FROM ep_microservice WHERE id =:serviceId
1450 <sql-query name="epUserAppId">
1451 <return alias="userAppId" class="org.openecomp.portalapp.portal.domain.EPUser" />
1455 SELECT * FROM FN_USER where ORG_USER_ID = :orgUserIdValue
1460 <sql-query name="userAppsSortPreferenceQuery">
1461 <return alias="appsSortPreferenceQuery"
1462 class="org.openecomp.portalapp.portal.domain.EPUserAppsSortPreference" />
1466 SELECT * from EP_PERS_USER_APP_SORT e where e.USER_ID = :userId
1471 <sql-query name="userAppsManualSortPrfQuery">
1472 <return alias="AppsManualSortPrfQuery"
1473 class="org.openecomp.portalapp.portal.domain.EPUserAppsManualSortPreference" />
1477 SELECT * from EP_PERS_USER_APP_MAN_SORT where USER_ID =:userId
1482 <sql-query name="userWidgetManualSortPrfQuery">
1483 <return alias="widgetManualSortPrfQuery"
1484 class="org.openecomp.portalapp.portal.domain.EPWidgetsManualSortPreference" />
1486 SELECT * from EP_PERS_USER_WIDGET_PLACEMENT where USER_ID =:userId
1490 <sql-query name="appRoles">
1491 <return alias="rolesForApp"
1492 class="org.openecomp.portalapp.portal.domain.EpUserAppRoles" />
1494 SELECT ROLE_ID, APP_ID from FN_ROLE where APP_ROLE_ID =:appRoleId and app_id =:appId
1499 <sql-query name="userAppRolesRequestList">
1500 <return alias="appRolesRequestList"
1501 class="org.openecomp.portalapp.portal.domain.EPUserAppRolesRequest" />
1503 SELECT req_id, user_id, app_id, created_date, updated_date, request_status from EP_USER_ROLES_REQUEST where user_id =:userId and app_id=:appId and request_status = 'P'
1508 <sql-query name="userAppRolesRequestDetailList">
1509 <return alias="appRolesRequestDetailList"
1510 class="org.openecomp.portalapp.portal.domain.EPUserAppRolesRequestDetail" />
1513 SELECT id, req_id, requested_role_id, request_type from EP_USER_ROLES_REQUEST_DET where req_id =:reqId
1518 <!-- Gets list of enabled apps including accessible and select statuses -->
1519 <sql-query name="getAdminAppCatalog">
1520 <return alias="adminAppCatalog"
1521 class="org.openecomp.portalapp.portal.ecomp.model.AppCatalogItem" />
1522 <!-- This query requires one parameter: userId (number) -->
1525 distinct -- multiple roles yield multiple rows
1526 a.APP_ID as id, a.APP_NAME as name, a.APP_IMAGE_URL as imageUrl,
1527 a.APP_DESCRIPTION as description, a.APP_NOTES as notes,
1528 a.APP_URL as url, a.APP_ALTERNATE_URL as alternateUrl,
1529 IF(a.app_type = '2', TRUE, FALSE) as restricted,
1530 IF(a.open = 'Y', TRUE, FALSE) as open,
1531 -- ACCESS(-ible) means user has a defined role OR the application is open
1533 -- regular app and user has a role
1534 when a.OPEN = 'N' and r.ROLE_ID is not null then TRUE
1536 when a.OPEN = 'Y' then TRUE
1539 -- SELECT(-ed) indicates user personalization
1541 -- regular app, user has a role, no personalization
1542 when a.OPEN = 'N' and r.ROLE_ID is not null and p.STATUS_CD is null then TRUE
1543 -- regular app, user has no role, admin forced a personalization
1544 when a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S' then TRUE
1545 -- open app and has personalization
1546 when a.OPEN = 'Y' and p.STATUS_CD is not null then TRUE
1549 -- PENDING indicates user checked a box
1551 when a.OPEN = 'N' and q.request_status = 'P' then TRUE
1555 -- Portal assigns role 999 to app administrator
1556 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1557 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1558 left outer join EP_USER_ROLES_REQUEST q ON a.APP_ID = q.APP_ID and q.USER_ID = :userId and q.request_status = 'P'
1559 where a.ENABLED = 'Y'
1560 -- Show accessible apps first, then the rest; sort by name within each set.
1561 order by access desc, app_name asc
1566 <sql-query name="userAppCatalogRoles">
1567 <return alias="epUserAppCatalogRoles"
1568 class="org.openecomp.portalapp.portal.domain.EPUserAppCatalogRoles" />
1570 select a.req_id, B.requested_role_id , A.request_status , A.app_id , (select role_name from fn_role where role_id = B.requested_role_id) as role_name
1571 from ep_user_roles_request A left join ep_user_roles_request_det B
1572 on a.req_id = b.req_id
1573 where A.user_id=:userid
1574 and A.app_id IN (select app_id from fn_app where app_name=:appName)
1575 and A.request_status ='P'
1580 <sql-query name="getUserApproles">
1581 <return alias="businessCardUserApplicationRoles"
1582 class="org.openecomp.portalapp.portal.transport.BusinessCardApplicationRole" />
1586 select fr.role_name, fa.app_name
1588 fn_role fr, fn_user_role fur, fn_app fa, fn_user fu
1589 Where fu.user_id = fur.user_id and fur.role_id = fr.role_id and fa.app_id = fur.app_id and fu.org_user_id = :userId
1595 </hibernate-mapping>