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>
324 <property name="centralAuth" type="yes_no">
325 <column name="auth_central"></column>
327 <property name="nameSpace" type="string">
328 <column name="auth_namespace"></column>
330 <one-to-one name="contactUs" class="AppContactUs" cascade="save-update"></one-to-one>
334 <!-- EPUserAppsSortPreference class mapping details -->
335 <class name="EPUserAppsSortPreference" table="ep_pers_user_app_sort">
336 <id name="id" column="ID">
337 <generator class="native">
338 <param name="sequence">seq_ep_usrappsrtprf</param>
341 <property name="userId" column="USER_ID" />
342 <property name="sortPref" column="SORT_PREF" />
345 <!-- EPUserAppsManualSortPreference class mapping details -->
346 <class name="EPUserAppsManualSortPreference" table="ep_pers_user_app_man_sort">
347 <id name="id" column="ID">
348 <generator class="native">
349 <param name="sequence">seq_ep_usrmanappsrtprf</param>
352 <property name="userId" column="user_id"
353 unique-key="uk_1_ep_pers_user_app_man_sort" />
354 <property name="appId" column="app_id"
355 unique-key="uk_1_ep_pers_user_app_man_sort" />
356 <property name="appManualSortOrder" column="sort_order" type="int" />
359 <class name="EPWidgetsManualSortPreference" table="ep_pers_user_widget_placement">
360 <id name="id" column="ID">
361 <generator class="native">
362 <param name="sequence">seq_ep_usrwidgetsrtprf</param>
365 <property name="userId" column="user_id" />
366 <property name="widgetId" column="widget_id" />
367 <property name="widgetRow" column="x" />
368 <property name="widgetCol" column="y" />
369 <property name="widgetHeight" column="height" />
370 <property name="widgetWidth" column="width" />
373 <!-- User Role class mapping details -->
374 <class name="EPRole" table="FN_ROLE">
375 <id name="id" column="role_id">
376 <generator class="native">
377 <param name="sequence">seq_fn_role</param>
381 <property name="name" column="role_name" />
382 <property name="priority" column="priority" />
383 <property name="active" column="active_yn" type="yes_no" />
384 <!-- 2 lines below may be removed! -->
385 <property name="appId" column="APP_ID" />
386 <property name="appRoleId" column="APP_ROLE_ID" />
387 <!-- 2 lines above may be removed -->
389 <set name="roleFunctions" table="FN_ROLE_FUNCTION" lazy="false"
391 <key column="role_id" />
392 <many-to-many column="function_cd"
393 class="org.openecomp.portalsdk.core.domain.RoleFunction" />
396 <set name="childRoles" table="FN_ROLE_COMPOSITE" lazy="false"
398 <key column="parent_role_id" />
399 <many-to-many column="child_role_id"
400 class="org.openecomp.portalapp.portal.domain.EPRole" />
403 <set name="parentRoles" table="FN_ROLE_COMPOSITE" lazy="false"
405 <key column="child_role_id" />
406 <many-to-many column="parent_role_id"
407 class="org.openecomp.portalapp.portal.domain.EPRole" />
412 <!-- User App class mapping details -->
413 <class name="AppContactUs" table="fn_app_contact_us">
414 <id name="id" column="app_id">
415 <generator class="foreign">
416 <param name="property">app</param>
419 <property name="url" column="url" />
420 <property name="description" column="description" />
421 <property name="contactName" column="contact_name" />
422 <property name="contactEmail" column="contact_email" />
423 <property name="activeYN" column="active_yn" />
425 <one-to-one name="app" class="EPApp" constrained="true"></one-to-one>
429 <!-- Personalization of user app selections -->
430 <class name="PersUserAppSelection" table="fn_pers_user_app_sel">
431 <id name="id" column="id">
432 <generator class="native">
433 <param name="property">seq_fn_pers_user_app_sel</param>
436 <property name="userId" column="user_id" />
437 <property name="appId" column="app_id" />
438 <property name="statusCode" column="status_cd" />
441 <!-- EPAppRoleFunction class mapping details -->
442 <class name="EPAppRoleFunction" table="ep_app_role_function">
443 <id name="id" column="id">
444 <generator class="native">
445 <param name="property">seq_epp_app_role_func</param>
448 <property name="roleId" column="role_id"></property>
449 <property name="appId" column="app_id"></property>
450 <property name="code" column="function_cd"></property>
453 <!-- CentralRoleFunction class mapping details -->
454 <class name="CentralRoleFunction" table="ep_app_function">
456 <key-property name="appId"
458 <key-property name="code"
459 column="function_cd" />
461 <property name="name" column="function_name" />
464 <class name="PersUserWidgetSelection" table="ep_pers_user_widget_sel">
465 <id name="id" column="id">
466 <generator class="native">
467 <param name="property">seq_ep_pers_user_widget_sel</param>
470 <property name="userId" column="user_id" />
471 <property name="widgetId" column="widget_id" />
472 <property name="statusCode" column="status_cd" />
475 <!-- EPUserAppRolesRequest class mapping details -->
476 <class name="EPUserAppRolesRequest" table="ep_user_roles_request">
477 <id name="id" column="req_id">
478 <generator class="native">
479 <param name="sequence">seq_ep_user_role_request</param>
482 <property name="userId" column="user_id" />
483 <property name="appId" column="app_id" />
484 <property name="createdDate" column="created_date" type="timestamp" />
485 <property name="updatedDate" column="updated_date" type="timestamp" />
486 <property name="requestStatus" column="request_status" />
488 <set name="epRequestIdDetail" table="ep_user_roles_request_det"
489 lazy="false" cascade="save-update, persist" inverse="true" fetch="select">
490 <key column="req_id" />
492 class="org.openecomp.portalapp.portal.domain.EPUserAppRolesRequestDetail" />
496 <!-- EPUserAppRolesRequestDetail class mapping details -->
497 <class name="EPUserAppRolesRequestDetail" table="ep_user_roles_request_det">
498 <id name="id" column="id">
499 <generator class="native">
500 <param name="sequence">seq_ep_user_role_request_det</param>
503 <property name="reqRoleId" column="requested_role_id" />
504 <property name="reqType" column="request_type" />
505 <many-to-one name="epRequestIdData" fetch="select"
506 class="org.openecomp.portalapp.portal.domain.EPUserAppRolesRequest">
507 <column name="req_id" not-null="true" />
511 <!-- show the current user plus related users -->
512 <sql-query name="relatedUsers">
513 <return-scalar column="org_user_id" type="java.lang.String" />
515 select t.org_user_id from (
516 select distinct c.org_user_id org_user_id, c.last_login_date from
517 (select c.org_user_id, c.last_login_date from
519 (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,
521 where a.ROLE_ID = b.role_id
522 and a.APP_ID = b.app_id
523 and a.USER_ID = c.user_id
525 select org_user_id , last_login_date from fn_user where org_user_id=:userId
527 order by c.last_login_date desc limit 10
532 <!-- requires values for named parameters :searchQuery and :userId -->
533 <sql-query name="searchPortal">
534 <return alias="searchResult"
535 class="org.openecomp.portalapp.portal.ecomp.model.SearchResultItem" />
537 select @rn /*'*/:=/*'*/ @rn+1 AS rowId, CATEGORY, NAME, UUID, TARGET from
539 select distinct 'User' CATEGORY, concat(concat(first_name, ' '), last_name) NAME, user_id UUID, org_user_id TARGET
541 upper( :searchQuery ) != ''
542 and (upper(first_name) like upper(CONCAT('%', :searchQuery, '%'))
543 or upper(last_name) like upper(CONCAT('%', :searchQuery, '%'))
544 or upper(org_user_id) like upper(CONCAT('%', :searchQuery, '%')))
545 and upper(active_yn) = 'Y'
549 select distinct 'Application' CATEGORY, b1.app_name NAME,
550 if (b1.app_type = 2,'false','true') UUID,
551 if (a1.app_id is null, if (b1.app_type = 2, b1.APP_URL, ''), a1.APP_URL) TARGET
555 from fn_user_role a, fn_user b, fn_app c
556 where a.USER_ID = b.user_id
557 and upper(b.org_user_id) = upper( :userId )
558 and a.app_id = c.app_id
559 and upper(c.APP_NAME) like upper(CONCAT('%', :searchQuery, '%'))
560 and upper(c.ENABLED) = 'Y'
561 ) a1 right outer join
562 (select * from fn_app where
563 upper(APP_NAME) like upper(CONCAT('%', :searchQuery, '%'))
564 and upper(ENABLED) = 'Y') b1
565 on a1.APP_ID = b1.app_id
569 select distinct 'Menu' CATEGORY, b1.TEXT NAME,
570 if (b1.app_type = 2,'false','true') UUID,
571 if (a1.menu_id is null , if (b1.app_type = 2, b1.URL, '') , a1.URL ) TARGET
573 (select distinct d.*, e.app_type from fn_user a,
574 fn_user_role b, fn_menu_functional_roles c, fn_menu_functional d, fn_app e
575 where a.USER_ID = b.user_id
576 and b.role_id = c.role_id
577 and c.menu_id = d.menu_id
578 and upper(a.org_user_id) = upper( :userId )
579 and c.APP_ID = e.app_id
580 and upper(d.TEXT) like upper(CONCAT('%', :searchQuery, '%'))
581 and upper(d.active_yn) = 'Y'
582 and upper(e.enabled) = 'Y'
583 ) a1 right outer join
585 select distinct a.*, c.app_type from fn_menu_functional a, fn_menu_functional_roles b, fn_app c
586 where active_yn = 'Y'
587 and upper(TEXT) like upper(CONCAT('%', :searchQuery, '%'))
588 and a.menu_id = b.menu_id
589 and b.app_id = c.app_id
592 on a1.menu_id = b1.menu_id
595 select distinct CATEGORY, NAME, UUID, TARGET
599 select distinct 'Widget' CATEGORY, d.wdg_name NAME,
602 from fn_user a, fn_user_role b, ep_widget_catalog_role c, ep_widget_catalog d
603 where upper(a.org_user_id) = upper( :userId )
604 and a.user_id = b.user_id
605 and b.role_id = c.role_id
606 and c.widget_id = d.widget_id
607 and d.all_user_flag<>'Y'
608 and (upper(d.wdg_name) like upper(CONCAT('%', :searchQuery, '%')) or upper(d.wdg_desc) like upper(CONCAT('%', :searchQuery, '%')))
612 select distinct 'Widget' CATEGORY, d.wdg_name NAME,
615 from ep_widget_catalog d
616 where d.all_user_flag='Y'
617 and (upper(d.wdg_name) like upper(CONCAT('%', :searchQuery, '%')) or upper(d.wdg_desc) like upper(CONCAT('%', :searchQuery, '%')))
621 select distinct 'Widget' CATEGORY, d.wdg_name NAME,
624 from fn_user a, fn_user_role b, ep_widget_catalog d
625 where upper(a.org_user_id) = upper( :userId )
626 and a.user_id = b.user_id
628 and (upper(d.wdg_name) like upper(CONCAT('%', :searchQuery, '%')) or upper(d.wdg_desc) like upper(CONCAT('%', :searchQuery, '%')))
632 (SELECT @rn /*'*/:=/*'*/ 0) t2 order by NAME
638 <!-- requires values for named parameters :searchQuery and :userId -->
639 <sql-query name="getNotifications">
640 <return alias="notificationResult"
641 class="org.openecomp.portalapp.portal.transport.EpNotificationItem" />
643 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,notification_hyperlink, active_YN from
646 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
649 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
652 select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN,
653 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
654 from ep_notification a, ep_role_notification b
655 where a.notification_id = b.notification_id
656 and (end_time is null || SYSDATE() <= end_time )
657 and (start_time is null || SYSDATE() >= start_time)
658 and a.is_for_all_roles = 'N'
661 select distinct a.user_id, c.role_id, c.app_id, d.APP_NAME
662 from fn_user a, fn_user_role b, fn_role c, fn_app d
663 where COALESCE(c.app_id,1) = d.app_id
664 and a.user_id = b.user_id
665 and a.user_id = :user_id
666 and b.role_id = c.role_id
667 and (d.enabled='Y' or d.app_id=1)
671 a.role_id = b.role_id
674 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
677 select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN,
678 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
679 from ep_notification a, ep_role_notification b
680 where a.notification_id = b.notification_id
681 and (end_time is null || SYSDATE() <= end_time )
682 and (start_time is null || SYSDATE() >= start_time)
683 and a.is_for_all_roles = 'N'
687 a.recv_user_id=:user_id
691 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
692 from ep_notification a
693 where a.notification_id
694 and (end_time is null || SYSDATE() <= end_time )
695 and (start_time is null || SYSDATE() >= start_time)
696 and a.is_for_all_roles = 'Y'
704 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'
706 order by priority desc, created_date desc,start_Time desc
710 (SELECT @rn /*'*/:=/*'*/ 0) t2
716 <sql-query name="getUsersByOrgIdsNotifications">
717 <return alias="getUsersByOrgIdsNotificationsResult" class="org.openecomp.portalapp.portal.domain.EPUser"/>
719 SELECT * from fn_user where org_user_id in (:OrgIds)
725 <!-- Gets all Admin notification history; accepts no parameters -->
726 <sql-query name="getAdminNotificationHistoryVO">
727 <return alias="adminNotificationHistoryVOResult"
728 class="org.openecomp.portalapp.portal.transport.EpNotificationItemVO" />
732 n.notification_ID AS notificationId,
733 n.is_for_online_users AS isForOnlineUsers,
734 n.is_for_all_roles AS isForAllRoles,
735 n.active_YN AS activeYn,
736 n.msg_header AS msgHeader,
737 n.msg_description AS msgDescription,
738 n.msg_source AS msgSource,
739 n.start_Time AS startTime,
740 n.end_time AS endTime,
742 n.creator_ID AS creatorId,
743 n.created_date AS createdDate,
744 n.notification_hyperlink AS notificationHyperlink,
745 u.org_user_id AS loginId
749 fn_user u on u.user_id = n.creator_id
751 n.active_YN='Y' and msg_source='EP'
756 n.notification_ID AS notificationId,
757 n.is_for_online_users AS isForOnlineUsers,
758 n.is_for_all_roles AS isForAllRoles,
759 n.active_YN AS activeYn,
760 n.msg_header AS msgHeader,
761 n.msg_description AS msgDescription,
762 n.msg_source AS msgSource,
763 n.start_Time AS startTime,
764 n.end_time AS endTime,
766 n.creator_ID AS creatorId,
767 n.created_date AS createdDate,
768 n.notification_hyperlink AS notificationHyperlink,
769 u.org_user_id AS loginId
773 fn_user u on u.user_id = n.creator_id
775 ep_role_notification r on r.notification_ID=n.notification_ID
777 n.active_YN='Y' and r.recv_user_id = :user_id and msg_source!='EP'
778 )n order by n.startTime desc
782 <sql-query name="getEpNotificationAppRoles">
783 <return alias="notificationAppRoles"
784 class="org.openecomp.portalapp.portal.domain.EcompAppRole" />
785 <!-- This query requires no parameters. -->
788 select a.app_id, a.app_name, b.role_id, b.role_name from
789 (select * from fn_app where app_id = 1) a,
790 (select * from fn_role where app_id is null and active_yn = 'Y' and role_id <> 1) b
792 select fn_role.app_id,fn_app.app_name, fn_role.role_id ,fn_role.role_name
794 where fn_role.app_id = fn_app.app_id and fn_app.enabled='Y' and fn_role.active_yn='Y' order by app_name
801 <!-- Gets all notification history -->
802 <sql-query name="getNotificationHistoryVO">
803 <return alias="notificationHistoryVOResult"
804 class="org.openecomp.portalapp.portal.transport.EpNotificationItemVO" />
807 notificationId, isForOnlineUsers, isForAllRoles, msgHeader, msgDescription,msgSource,
808 startTime, endTime, priority, createdDate,notificationHyperlink,creatorId, loginId, activeYn
812 a.notification_ID AS notificationId,
813 is_for_online_users AS isForOnlineUsers,
814 is_for_all_roles AS isForAllRoles,
815 msg_header AS msgHeader,
816 msg_description AS msgDescription,
817 msg_source AS msgSource,
818 start_Time AS startTime,
821 created_date AS createdDate,
822 creator_ID AS creatorId,
823 notification_hyperlink AS notificationHyperlink,
825 active_YN AS activeYn,
826 if (is_viewed is null, 'N', is_viewed)
830 user_id, login_id,notification_id, is_for_online_users, is_for_all_roles,
831 msg_header, msg_description,msg_source, start_Time, end_time, priority, created_date,
832 creator_ID,notification_hyperlink,active_YN
835 select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN,
836 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,
837 b.role_id,CASE WHEN a.creator_ID IS NOT NULL THEN u.org_user_id
840 END AS login_id,b.recv_user_id
841 from ep_notification a, ep_role_notification b,fn_user u
842 where a.notification_id = b.notification_id and (u.user_id=a.creator_ID OR a.creator_ID IS NULL)
843 and a.is_for_all_roles = 'N'
845 (start_time is null and end_time is null and a.created_date >= DATE_ADD(curdate(),INTERVAL-31 DAY))
847 (start_time is not null and end_time is null and start_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
849 (start_time is null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
851 (start_time is not null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
855 select distinct a.user_id, c.role_id, c.app_id, d.APP_NAME
856 from fn_user a, fn_user_role b, fn_role c, fn_app d
857 where COALESCE(c.app_id,1) = d.app_id
858 and a.user_id = b.user_id
859 and a.user_id = :user_id
860 and b.role_id = c.role_id
861 and (d.enabled='Y' or d.app_id=1)
865 a.role_id = b.role_id
869 :user_id, login_id,notification_id, is_for_online_users, is_for_all_roles,
870 msg_header, msg_description,msg_source, start_Time, end_time, priority, created_date,
871 creator_ID,notification_hyperlink,active_YN
874 select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN,
875 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,
876 b.role_id,CASE WHEN a.creator_ID IS NOT NULL THEN u.org_user_id
879 END AS login_id,b.recv_user_id
880 from ep_notification a, ep_role_notification b,fn_user u
881 where a.notification_id = b.notification_id and (u.user_id=a.creator_ID OR a.creator_ID IS NULL)
882 and a.is_for_all_roles = 'N'
884 (start_time is null and end_time is null and a.created_date >= DATE_ADD(curdate(),INTERVAL-31 DAY))
886 (start_time is not null and end_time is null and start_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
888 (start_time is null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
890 (start_time is not null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
895 a.recv_user_id=:user_id
900 :user_id user_id, b.login_id,notification_id, is_for_online_users, is_for_all_roles,
901 msg_header, msg_description,msg_source, start_Time, end_time, priority, a.created_date,
902 creator_ID, a.notification_hyperlink,a.active_YN
903 from ep_notification a JOIN fn_user b on b.user_id=a.creator_ID
904 where a.notification_id
905 and a.is_for_all_roles = 'Y'
907 (start_time is null and end_time is null and a.created_date >= DATE_ADD(curdate(),INTERVAL-31 DAY))
909 (start_time is not null and end_time is null and start_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
911 (start_time is null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
913 (start_time is not null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
916 ) a left outer join (
917 select m.notification_ID, m.is_viewed from ep_user_notification m where user_id = :user_id
919 on a.notification_id = m.notification_ID
923 order by start_Time desc,end_time desc
925 (SELECT @rn /*'*/:=/*'*/ 0) t2 where startTime<=SYSDATE()
930 <!-- shows the received recipient to whom the notification is delivered from external system -->
931 <sql-query name="messageRecipients">
932 <return-scalar column="org_user_id" type="java.lang.String" />
934 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
939 <!-- Gets role details for a specified notification -->
940 <sql-query name="getNotificationRoles">
941 <return alias="notificationRolesResult"
942 class="org.openecomp.portalapp.portal.transport.EpRoleNotificationItem" />
948 notification_Id = :notificationId
952 <!-- Gets all applications, possibly with contact information -->
953 <sql-query name="getAppsAndContacts">
954 <return alias="appContactUs"
955 class="org.openecomp.portalapp.portal.ecomp.model.AppContactUsItem" />
956 <!-- This query requires no parameters. -->
959 a.app_id as appId, a.app_name as appName,
960 c.contact_name as contactName,
961 c.contact_email as contactEmail, c.url, c.description,
962 c.active_yn as activeYN
967 on a.app_id = c.app_id
969 a.enabled = 'Y' and a.app_name is not null and a.app_name != ''
974 <!-- Gets all rows from the app-contact-us table, extended with app information -->
975 <sql-query name="getAppContactUsItems">
976 <return alias="appContactUs"
977 class="org.openecomp.portalapp.portal.ecomp.model.AppContactUsItem" />
978 <!-- This query requires no parameters. -->
981 c.app_id as appId, c.contact_name as contactName,
982 c.contact_email as contactEmail, c.url, c.description,
983 c.active_yn as activeYN, a.app_name as appName
988 on a.app_id = c.app_id
990 a.enabled = 'Y' and a.app_name is not null and a.app_name != ''
995 <!-- Gets one row for each app with the category and catenated functions. -->
996 <sql-query name="getAppCategoryFunctions">
997 <return alias="appCategoryFunctions"
998 class="org.openecomp.portalapp.portal.ecomp.model.AppCategoryFunctionsItem" />
999 <!-- This query requires no parameters. -->
1001 SELECT @rn /*'*/:=/*'*/ @rn+1 AS rowId,
1002 r.category as category, r.app_id as appId, r.app_name as application,
1003 group_concat(function_text separator ', ') as functions
1006 app_id, function_text, app_name,
1007 IF (a.parent_menu_id IS NULL, fn.parent_menu, a.text) category
1010 j.text function_text, j.menu_id function_id, j.app_id app_id, j.app_name app_name,
1011 k.text parent_menu, k.parent_menu_id parent_menu_id
1014 x.Text, x.PARENT_MENU_ID, x.menu_id, y.app_id, z.app_name
1016 fn_menu_functional x, fn_menu_functional_roles y, fn_app z
1018 x.MENU_ID = y.menu_id AND x.ACTIVE_YN = 'Y' AND y.app_id = z.app_id
1020 fn_menu_functional k
1021 WHERE j.parent_menu_id = k.menu_id
1023 fn_menu_functional a
1024 WHERE fn.parent_menu_id = a.menu_id
1026 (SELECT @rn /*'*/:=/*'*/ 0) t2
1027 group by r.category, r.app_id, r.app_name
1028 order by category, app_name
1033 <!-- Gets one row for each function-application-role combination. -->
1034 <sql-query name="getAppAccessFunctionRole">
1035 <return alias="appAccessFunctionRole"
1036 class="org.openecomp.portalapp.portal.domain.GetAccessResult" />
1037 <!-- This query requires one parameters. -->
1039 select @rn /*'*/:=/*'*/ @rn+1 AS row_id, t.role_id, t.ECOMP_FUNCTION, t.APP_NAME, t.APP_MOTS_ID, t.ROLE_NAME,
1040 (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,
1041 o.requested_role_id,o.request_type
1043 (select ECOMP_FUNCTION, APP_NAME, APP_MOTS_ID, ROLE_NAME ,a.app_id,a.role_id
1046 select D.APP_NAME, D.MOTS_ID APP_MOTS_ID, C.ROLE_NAME, d.app_id , c.role_id
1049 where COALESCE(c.app_id,1) = d.app_id
1050 and (d.enabled='Y' or d.app_id=1)
1051 and c.active_yn = 'Y'
1054 select A.TEXT ECOMP_FUNCTION, b.role_id, b.app_id from fn_menu_functional A, fn_menu_functional_roles B
1056 A.MENU_ID = B.MENU_ID
1059 on a.role_id = b.role_id) t left outer join
1060 (select ed.requested_role_id, er.app_id ,ed.request_type from ep_user_roles_request er , ep_user_roles_request_det ed
1061 where er.req_id=ed.req_id
1062 and upper(ed.request_type)='P'
1063 and er.user_id =:userId) o
1064 on t.app_id=o.app_id
1065 and t.role_id = o.requested_role_id
1066 JOIN (SELECT @rn /*'*/:=/*'*/ 0) p
1071 <query name="getCommonWidgetItem">
1072 from CommonWidget where category = :cat order by
1076 <sql-query name="getGuestLastLogin">
1077 <return-scalar column="audit_date" type="java.util.Date" />
1079 select audit_date from fn_audit_log where affected_record_id =:userId order by audit_date desc limit 2;
1083 <sql-query name="getActiveUsers">
1084 <return alias="EPUser" class="org.openecomp.portalapp.portal.domain.EPUser" />
1086 SELECT * FROM FN_USER WHERE active_yn='Y'
1090 <sql-query name="getAppsAdmins">
1091 <return alias="adminUserApp" class="org.openecomp.portalapp.portal.domain.AdminUserApp" />
1093 SELECT apps.APP_NAME, apps.APP_ID, user.USER_ID, user.FIRST_NAME, user.LAST_NAME, user.org_user_id FROM fn_user_role userrole INNER JOIN fn_user user ON user.USER_ID = userrole.USER_ID INNER JOIN fn_app apps ON apps.APP_ID = userrole.APP_ID WHERE user.active_yn='Y' AND userrole.ROLE_ID = :accountAdminRoleId AND (apps.ENABLED = 'Y' OR apps.APP_ID=1)
1097 <sql-query name="getPortalAdmins">
1098 <return alias="portalAdmin" class="org.openecomp.portalapp.portal.transport.PortalAdmin" />
1100 SELECT u.user_id, u.first_name, u.last_name, u.login_id FROM fn_user u, fn_user_role ur WHERE u.active_yn = 'Y' AND u.user_id = ur.user_id AND ur.role_id= :adminRoleId
1104 <!-- Gets personalized list of enabled and accessible apps for regular user -->
1105 <sql-query name="getPersUserApps">
1106 <return alias="persUserApps" class="org.openecomp.portalapp.portal.domain.EPApp" />
1107 <!-- This query requires one parameter: userId (number) -->
1110 distinct -- multiple roles yield multiple rows
1111 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1112 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1113 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1114 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1115 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL
1117 -- Portal assigns role 999 to app administrator
1118 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1119 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1120 where a.ENABLED = 'Y'
1122 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1124 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1131 <!-- Gets personalized list of enabled and accessible apps for Portal (super)
1133 <sql-query name="getPersAdminApps">
1134 <return alias="persAdminApps" class="org.openecomp.portalapp.portal.domain.EPApp" />
1135 <!-- This query requires one parameter: userId (number) -->
1138 distinct -- multiple roles yield multiple rows
1139 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1140 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1141 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1142 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1143 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1146 -- Portal assigns role 999 to app administrator
1147 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1148 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1149 where a.ENABLED = 'Y'
1151 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1153 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1155 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1162 <!-- Gets personalized list of enabled and accessible Admin apps by name -->
1163 <sql-query name="getPersAdminAppsOrderByName">
1164 <return alias="AdminAppsOrderByName" class="org.openecomp.portalapp.portal.domain.EPApp" />
1165 <!-- This query requires one parameter: userId (number) -->
1168 distinct -- multiple roles yield multiple rows
1169 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1170 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1171 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1172 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1173 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1176 -- Portal assigns role 999 to app administrator
1177 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1178 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1179 where a.ENABLED = 'Y'
1181 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1183 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1185 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1193 <!--Gets personalized list of enabled and accessible User apps by name -->
1194 <sql-query name="getPersUserAppsOrderByName">
1195 <return alias="UserAppsOrderByName" class="org.openecomp.portalapp.portal.domain.EPApp" />
1196 <!-- This query requires one parameter: userId (number) -->
1199 distinct -- multiple roles yield multiple rows
1200 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1201 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1202 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1203 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1204 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1207 -- Portal assigns role 999 to app administrator
1208 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1209 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1210 where a.ENABLED = 'Y'
1212 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1214 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1223 <!--Gets personalized list of enabled and accessible Super Admin apps by
1225 <sql-query name="getAdminAppsOrderByLastUsed">
1226 <return alias="AdminAppsOrderByLastUsed" class="org.openecomp.portalapp.portal.domain.EPApp" />
1232 distinct -- multiple roles yield multiple rows
1233 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1234 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1235 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1236 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1237 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1240 -- Portal assigns role 999 to app administrator
1241 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1242 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1243 where a.ENABLED = 'Y'
1245 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1247 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1249 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1254 c.AFFECTED_RECORD_ID, c.AUDIT_DATE
1257 on A.app_id = B.Affected_record_id
1258 order by AUDIT_DATE DESC;
1262 <!--Gets personalized list of enabled and accessible User apps by LastUsed -->
1263 <sql-query name="getUserAppsOrderByLastUsed">
1264 <return alias="UserAppsOrderByLastUsed" class="org.openecomp.portalapp.portal.domain.EPApp" />
1267 select * from (select
1268 distinct -- multiple roles yield multiple rows
1269 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1270 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1271 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1272 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1273 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1276 -- Portal assigns role 999 to app administrator
1277 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1278 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1279 where a.ENABLED = 'Y'
1281 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1283 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1287 c.AFFECTED_RECORD_ID, c.AUDIT_DATE
1290 on A.app_id = B.Affected_record_id
1291 order by AUDIT_DATE DESC;
1298 <!--Gets personalized list of enabled and accessible Super Admin apps by
1300 <sql-query name="getAdminAppsOrderByMostUsed">
1301 <return alias="AdminAppsOrderByMostUsed" class="org.openecomp.portalapp.portal.domain.EPApp" />
1307 distinct -- multiple roles yield multiple rows
1308 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1309 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1310 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1311 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1312 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1315 -- Portal assigns role 999 to app administrator
1316 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1317 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1318 where a.ENABLED = 'Y'
1320 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1322 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1324 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1328 (select FN_AUDIT_LOG.AFFECTED_RECORD_ID, COUNT(*) AS total_visits
1329 from FN_APP LEFT JOIN FN_AUDIT_LOG
1330 on FN_APP.APP_ID = FN_AUDIT_LOG.AFFECTED_RECORD_ID
1331 where FN_APP.ENABLED = 'Y' and FN_AUDIT_LOG.user_id=:userId
1332 and DATE(audit_date) BETWEEN (CURDATE() - INTERVAL 90 DAY) and CURDATE()
1333 group by FN_AUDIT_LOG.AFFECTED_RECORD_ID
1335 on A.app_id = B.Affected_record_id
1336 order by total_visits DESC;
1341 <!--Gets personalized list of enabled and accessible NON Super Admin User
1342 apps by Most Used -->
1343 <sql-query name="getUserAppsOrderByMostUsed">
1344 <return alias="UserAppsOrderByMostUsed" class="org.openecomp.portalapp.portal.domain.EPApp" />
1349 distinct -- multiple roles yield multiple rows
1350 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1351 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1352 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1353 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1354 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1357 -- Portal assigns role 999 to app administrator
1358 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1359 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1360 where a.ENABLED = 'Y'
1362 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1364 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1367 (select FN_AUDIT_LOG.AFFECTED_RECORD_ID, COUNT(*) AS total_visits
1368 from FN_APP LEFT JOIN FN_AUDIT_LOG
1369 on FN_APP.APP_ID = FN_AUDIT_LOG.AFFECTED_RECORD_ID
1370 where FN_APP.ENABLED = 'Y' and FN_AUDIT_LOG.user_id=:userId
1371 and DATE(audit_date) BETWEEN (CURDATE() - INTERVAL 90 DAY) and CURDATE()
1372 group by FN_AUDIT_LOG.AFFECTED_RECORD_ID
1374 on A.app_id = B.Affected_record_id
1375 order by total_visits DESC;
1379 <!--Gets personalized list of enabled and accessible Super Admin apps by
1381 <sql-query name="getAdminAppsOrderByManual">
1382 <return alias="AdminAppsOrderByManual" class="org.openecomp.portalapp.portal.domain.EPApp" />
1387 distinct -- multiple roles yield multiple rows
1388 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1389 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1390 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1391 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1392 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1395 -- Portal assigns role 999 to app administrator
1396 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1397 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1398 where a.ENABLED = 'Y'
1400 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1402 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1404 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1407 (select m.app_id, m.sort_order
1408 from ep_pers_user_app_man_sort m
1409 where USER_ID = :userId
1411 on A.APP_ID = B.app_id
1412 order by sort_order ASC
1418 <!--Gets personalized list of enabled and accessible NON Super admin User
1420 <sql-query name="getUserAppsOrderByManual">
1421 <return alias="UserAppsOrderByManual" class="org.openecomp.portalapp.portal.domain.EPApp" />
1424 select * from (select
1425 distinct -- multiple roles yield multiple rows
1426 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1427 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1428 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1429 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1430 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1433 -- Portal assigns role 999 to app administrator
1434 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1435 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1436 where a.ENABLED = 'Y'
1438 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1440 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1443 (select m.app_id, m.sort_order
1444 from ep_pers_user_app_man_sort m
1445 where USER_ID = :userId
1447 on A.APP_ID = B.app_id
1448 order by sort_order ASC
1453 <!-- Gets regular user's list of enabled apps including accessible and select
1455 <sql-query name="getUserAppCatalog">
1456 <return alias="userAppCatalog"
1457 class="org.openecomp.portalapp.portal.ecomp.model.AppCatalogItem" />
1458 <!-- This query requires one parameter: userId (number) -->
1461 distinct -- multiple roles yield multiple rows
1462 a.APP_ID as id, a.APP_NAME as name, a.ML_APP_NAME as mlAppName, a.APP_IMAGE_URL as imageUrl,
1463 a.APP_DESCRIPTION as description, a.APP_NOTES as notes,
1464 a.APP_URL as url, a.APP_ALTERNATE_URL as alternateUrl,
1465 IF(a.app_type = '2', TRUE, FALSE) as restricted,
1466 IF(a.open = 'Y', TRUE, FALSE) as open,
1467 -- ACCESS(-ible) means user has a defined role OR the application is open
1469 -- regular app and user has a role
1470 when a.OPEN = 'N' and r.ROLE_ID is not null then TRUE
1472 when a.OPEN = 'Y' then TRUE
1475 -- SELECT(-ed) indicates user personalization
1477 -- regular app, user has a role, no personalization
1478 when a.OPEN = 'N' and r.ROLE_ID is not null and p.STATUS_CD is null then TRUE
1479 -- open app and has personalization
1480 when a.OPEN = 'Y' and p.STATUS_CD is not null then TRUE
1483 -- PENDING indicates user checked a box
1485 when a.OPEN = 'N' and q.request_status = 'P' then TRUE
1489 -- Portal assigns role 999 to app administrator
1490 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1491 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1492 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'
1493 where a.ENABLED = 'Y'
1495 -- Show accessible apps first, then the rest; sort by name within each set.
1496 order by access desc, app_name asc
1501 <sql-query name="getMyloginAppDetails">
1502 <return alias="myloginAppDetails" class="org.openecomp.portalapp.portal.domain.EPApp" />
1505 SELECT * FROM fn_app where ml_app_name =:appName
1510 <sql-query name="getMyAppDetailsByUebKey">
1511 <return alias="myAppDetailsByUebKey" class="org.openecomp.portalapp.portal.domain.EPApp" />
1515 SELECT * FROM fn_app where ueb_key =:appKey
1521 <sql-query name="deleteAccountEndpoint">
1523 DELETE FROM ep_endpoints_basic_auth_account WHERE account_id =:accountId
1528 <sql-query name="deleteAccountEndpointRecord">
1530 DELETE FROM ep_endpoints_basic_auth_account WHERE account_id =:accountId AND ep_id =:epId
1535 <sql-query name="deleteEPEndpoint">
1537 DELETE FROM ep_endpoints WHERE id =:epId
1542 <sql-query name="deleteBasicAuthAccount">
1544 DELETE FROM ep_basic_auth_account WHERE id =:accountId
1549 <sql-query name="getAppRoles">
1550 <return alias="appRoles" class="org.openecomp.portalapp.portal.domain.EPRole" />
1553 SELECT * FROM fn_role where role_name =:roleName and app_id =:appId
1558 <sql-query name="getPortalAppRoles">
1559 <return alias="appRoles" class="org.openecomp.portalapp.portal.domain.EPRole" />
1563 SELECT * FROM fn_role where role_name =:appRoleName and app_id is null
1568 <sql-query name="deleteMicroserviceParameter">
1571 DELETE FROM ep_microservice_parameter WHERE service_id =:serviceId
1576 <sql-query name="deleteMicroserviceParameterById">
1579 DELETE FROM ep_microservice_parameter WHERE id =:paramId
1584 <sql-query name="deleteWidgetCatalogParameter">
1587 DELETE FROM ep_widget_catalog_parameter WHERE param_id =:paramId
1592 <sql-query name="deleteMicroservice">
1595 DELETE FROM ep_microservice WHERE id =:serviceId
1600 <sql-query name="epUserAppId">
1601 <return alias="userAppId" class="org.openecomp.portalapp.portal.domain.EPUser" />
1605 SELECT * FROM FN_USER where ORG_USER_ID = :orgUserIdValue
1610 <sql-query name="userAppsSortPreferenceQuery">
1611 <return alias="appsSortPreferenceQuery"
1612 class="org.openecomp.portalapp.portal.domain.EPUserAppsSortPreference" />
1616 SELECT * from EP_PERS_USER_APP_SORT e where e.USER_ID = :userId
1621 <sql-query name="userAppsManualSortPrfQuery">
1622 <return alias="AppsManualSortPrfQuery"
1623 class="org.openecomp.portalapp.portal.domain.EPUserAppsManualSortPreference" />
1627 SELECT * from EP_PERS_USER_APP_MAN_SORT where USER_ID =:userId
1632 <sql-query name="userWidgetManualSortPrfQuery">
1633 <return alias="widgetManualSortPrfQuery"
1634 class="org.openecomp.portalapp.portal.domain.EPWidgetsManualSortPreference" />
1636 SELECT * from EP_PERS_USER_WIDGET_PLACEMENT where USER_ID =:userId
1640 <sql-query name="appRoles">
1641 <return alias="rolesForApp"
1642 class="org.openecomp.portalapp.portal.domain.EPUserAppRoles" />
1644 SELECT ROLE_ID, APP_ID from FN_ROLE where APP_ROLE_ID = :appRoleId and app_id = :appId
1649 <sql-query name="userAppRolesRequestList">
1650 <return alias="appRolesRequestList"
1651 class="org.openecomp.portalapp.portal.domain.EPUserAppRolesRequest" />
1653 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'
1658 <sql-query name="userAppRolesRequestDetailList">
1659 <return alias="appRolesRequestDetailList"
1660 class="org.openecomp.portalapp.portal.domain.EPUserAppRolesRequestDetail" />
1663 SELECT id, req_id, requested_role_id, request_type from EP_USER_ROLES_REQUEST_DET where req_id =:reqId
1668 <!-- Gets list of enabled apps including accessible and select statuses -->
1669 <sql-query name="getAdminAppCatalog">
1670 <return alias="adminAppCatalog"
1671 class="org.openecomp.portalapp.portal.ecomp.model.AppCatalogItem" />
1672 <!-- This query requires one parameter: userId (number) -->
1675 distinct -- multiple roles yield multiple rows
1676 a.APP_ID as id, a.APP_NAME as name, a.ML_APP_NAME as mlAppName, a.APP_IMAGE_URL as imageUrl,
1677 a.APP_DESCRIPTION as description, a.APP_NOTES as notes,
1678 a.APP_URL as url, a.APP_ALTERNATE_URL as alternateUrl,
1679 IF(a.app_type = '2', TRUE, FALSE) as restricted,
1680 IF(a.open = 'Y', TRUE, FALSE) as open,
1681 -- ACCESS(-ible) means user has a defined role OR the application is open
1683 -- regular app and user has a role
1684 when a.OPEN = 'N' and r.ROLE_ID is not null then TRUE
1686 when a.OPEN = 'Y' then TRUE
1689 -- SELECT(-ed) indicates user personalization
1691 -- regular app, user has a role, no personalization
1692 when a.OPEN = 'N' and r.ROLE_ID is not null and p.STATUS_CD is null then TRUE
1693 -- regular app, user has role, admin forced a personalization
1694 when a.OPEN = 'N' and r.ROLE_ID is not null and p.STATUS_CD = 'S' then TRUE
1695 -- regular app, user has no role, admin forced a personalization
1696 when a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S' then TRUE
1697 -- open app and has personalization
1698 when a.OPEN = 'Y' and p.STATUS_CD is not null then TRUE
1701 -- PENDING indicates user checked a box
1703 when a.OPEN = 'N' and q.request_status = 'P' then TRUE
1707 -- Portal assigns role 999 to app administrator
1708 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1709 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1710 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'
1711 where a.ENABLED = 'Y'
1712 -- Show accessible apps first, then the rest; sort by name within each set.
1713 order by access desc, app_name asc
1718 <sql-query name="userAppCatalogRoles">
1719 <return alias="epUserAppCatalogRoles"
1720 class="org.openecomp.portalapp.portal.domain.EPUserAppCatalogRoles" />
1722 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
1723 from ep_user_roles_request A left join ep_user_roles_request_det B
1724 on a.req_id = b.req_id
1725 where A.user_id=:userid
1726 and A.app_id IN (select app_id from fn_app where app_name=:appName)
1727 and A.request_status ='P'
1732 <sql-query name="getUserApproles">
1733 <return alias="businessCardUserApplicationRoles"
1734 class="org.openecomp.portalapp.portal.transport.BusinessCardApplicationRole" />
1738 select fr.role_name, fa.app_name
1740 fn_role fr, fn_user_role fur, fn_app fa, fn_user fu
1741 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 and fr.active_yn='Y' and (fa.enabled = 'Y' or fa.app_id=1)
1747 <!--delete the records from ep_notification table when the endtime is more than 3 months-->
1748 <sql-query name="deleteNotificationsFromEpNotificationTable">
1751 delete from ep_notification where curdate() >= DATE_ADD(end_time,INTERVAL 3 MONTH)
1756 <!-- delete the records from ep_role_notification table when the endtime is more than 3 months -->
1757 <sql-query name="deleteNotificationsFromEpRoleNotificationTable">
1760 delete from ep_role_notification where notification_id in (select notification_id from ep_notification where curdate() >= DATE_ADD(end_time,INTERVAL 3 MONTH))
1764 <!-- delete the records from ep_user_notification table when the endtime is more than 3 months-->
1765 <sql-query name="deleteNotificationsFromEpUserNotificationTable">
1768 delete from ep_user_notification where notification_id in (select notification_id from ep_notification where curdate() >= DATE_ADD(end_time,INTERVAL 3 MONTH))
1772 <!-- Gets list of role functions and it requires two parameters appId and roleId-->
1773 <sql-query name="getAppRoleFunctionList">
1774 <return alias="appRoleFunctionList"
1775 class="org.openecomp.portalapp.portal.domain.CentralRoleFunction" />
1778 SELECT DISTINCT f.app_id , f.function_cd, f.function_name from ep_app_role_function rf, ep_app_function f
1779 where rf.role_id =:roleId and rf.app_id =:appId and rf.app_id = f.app_id and rf.function_cd = f.function_cd
1785 <!-- Gets list of all role functions -->
1786 <sql-query name="getAllRoleFunctions">
1787 <return alias="allRoleFunctions"
1788 class="org.openecomp.portalapp.portal.domain.CentralRoleFunction" />
1791 SELECT * from ep_app_function where app_id =:appId
1797 <!-- Get ep_app_function records provided by single parameter -->
1798 <sql-query name="getRoleFunction">
1799 <return alias="RoleFunction"
1800 class="org.openecomp.portalapp.portal.domain.CentralRoleFunction" />
1803 SELECT * from ep_app_function where function_cd = :functionCode and app_id =:appId
1809 <!-- Gets the record from ep_app_function table and requires two parameters -->
1810 <sql-query name="getAppFunctionDetails">
1811 <return alias="appFunctionDetails"
1812 class="org.openecomp.portalapp.portal.domain.CentralRoleFunction" />
1815 select * from ep_app_function where app_id =:appId and function_cd =:functionCd
1820 <!-- Gets the current user app roles records and requires two parameters -->
1821 <sql-query name="getUserAppCurrentRoles">
1822 <return alias="userAppCurrentRoles"
1823 class="org.openecomp.portalapp.portal.transport.EPUserAppCurrentRoles" />
1826 select distinct fu.role_id, fr.user_id, fu.role_name, fu.priority from fn_role fu left outer join fn_user_role fr ON fu.role_id = fr.role_id and fu.app_id = fr.app_id and fr.role_id != 999 where fu.app_id =:appId and fr.user_id =:userId
1831 <!-- Gets the current user app roles along with account administrator and requires two parameters -->
1832 <sql-query name="getUserAppExistingRoles">
1833 <return alias="userAppExistingRoles"
1834 class="org.openecomp.portalapp.portal.transport.EcompUserAppRoles" />
1836 select fr.role_name, fu.app_id, fu.user_id, fu.priority, fu.role_id from fn_user_role fu
1837 left outer join fn_role fr on fu.role_id = fr.role_id
1838 where fu.user_id =:userId and fu.app_id =:appId
1843 <!-- Gets the current user app roles records and requires two parameters -->
1844 <sql-query name="getAllFunctions">
1845 <return alias="allFunctions"
1846 class="org.openecomp.portalsdk.core.domain.RoleFunction" />
1848 select * from fn_function
1853 <!-- Gets the all role functions and requires one parameters -->
1854 <sql-query name="uploadAllRoleFunctions">
1855 <return alias="allRoleFunctions"
1856 class="org.openecomp.portalapp.portal.transport.BulkUploadRoleFunction" />
1858 select fr.function_cd, fn.function_name from fn_role_function fr left outer join fn_function fn on fr.function_cd = fn.function_cd where role_id =:roleId
1863 <sql-query name="getRoletoUpdateAAF">
1864 <return alias="getRoletoUpdateAAF" class="org.openecomp.portalapp.portal.domain.EPRole" />
1866 SELECT * FROM fn_role where role_name =:appRoleName and app_id =:appId
1871 <sql-query name="getBulkUserRoles">
1872 <return alias="bulkUserRoles" class="org.openecomp.portalapp.portal.transport.BulkUploadUserRoles" />
1874 select fr.role_name, fu.org_user_id, fa.auth_namespace from fn_user_role fur
1875 left outer join fn_role fr on fr.role_id = fur.role_id
1876 left outer join fn_app fa on fa.app_id = fur.app_id
1877 left outer join fn_user fu on fu.user_id = fur.user_id where fa.ueb_key =:uebKey and fu.org_user_id != 'su1234'
1882 <sql-query name="getCentralizedApps">
1883 <return alias="centralizedApps" class="org.openecomp.portalapp.portal.domain.EPApp" />
1885 select * from fn_app where auth_central = 'Y' and open = 'N';
1890 <sql-query name="getUserRoles">
1891 <return alias="getUserRolesList" class="org.openecomp.portalapp.portal.domain.UserRole" />
1895 SELECT DISTINCT user.USER_ID, role.ROLE_ID, user.org_user_id, user.FIRST_NAME, user.LAST_NAME, role.ROLE_NAME FROM fn_user_role userrole INNER JOIN fn_user user ON user.USER_ID = userrole.USER_ID INNER JOIN fn_role role ON role.ROLE_ID = userrole.ROLE_ID WHERE user.org_user_id =:org_user_id and userrole.app_id = 1
1899 <sql-query name="getRoleFunctionsOfUser">
1901 select distinct ep.function_cd from fn_user_role fu, ep_app_role_function ep, ep_app_function ea
1902 where fu.role_id = ep.role_id
1903 and fu.app_id = ep.app_id
1904 and fu.user_id =:userId and ep.app_id = 1
1905 and ea.function_cd = ep.function_cd
1910 <sql-query name="getUserRolesForLeftMenu">
1911 <return alias="getUserRolesListForLeftMenu" class="org.openecomp.portalapp.portal.domain.UserRole" />
1914 SELECT DISTINCT user.USER_ID, role.ROLE_ID, user.org_user_id, user.FIRST_NAME, user.LAST_NAME, role.ROLE_NAME FROM fn_user_role userrole
1915 INNER JOIN fn_user user ON user.USER_ID = userrole.USER_ID
1916 INNER JOIN fn_app app ON app.app_id= userrole.app_id
1917 INNER JOIN fn_role role ON role.ROLE_ID = userrole.ROLE_ID
1918 WHERE user.org_user_id =:org_user_id and (userrole.app_id = 1 or role.role_id = 999) and (app.enabled='Y' or app.app_id=1)
1923 <!-- Gets all functions for an application along with global functions and requires single parameter -->
1924 <sql-query name="getMenuFunctions">
1926 select f.function_cd from ep_app_function f
1927 where f.app_id =:appId
1929 select epa.function_cd from fn_role fnr, ep_app_role_function epr, ep_app_function epa where epr.role_id = fnr.role_id
1930 and epa.function_cd = epr.function_cd and fnr.role_name like 'global%'
1935 <sql-query name="getRequestIdsForApp">
1936 <return alias="getRequestIdsForApp" class="org.openecomp.portalapp.portal.domain.EPUserAppRolesRequest" />
1938 select * from ep_user_roles_request where app_id =:app_id
1943 </hibernate-mapping>