3 ============LICENSE_START==========================================
5 ===================================================================
6 Copyright © 2017 AT&T Intellectual Property. All rights reserved.
7 ===================================================================
9 Unless otherwise specified, all software contained herein is licensed
10 under the Apache License, Version 2.0 (the “License”);
11 you may not use this software except in compliance with the License.
12 You may obtain a copy of the License at
14 http://www.apache.org/licenses/LICENSE-2.0
16 Unless required by applicable law or agreed to in writing, software
17 distributed under the License is distributed on an "AS IS" BASIS,
18 WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
19 See the License for the specific language governing permissions and
20 limitations under the License.
22 Unless otherwise specified, all documentation contained herein is licensed
23 under the Creative Commons License, Attribution 4.0 Intl. (the “License”);
24 you may not use this documentation except in compliance with the License.
25 You may obtain a copy of the License at
27 https://creativecommons.org/licenses/by/4.0/
29 Unless required by applicable law or agreed to in writing, documentation
30 distributed under the License is distributed on an "AS IS" BASIS,
31 WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
32 See the License for the specific language governing permissions and
33 limitations under the License.
35 ============LICENSE_END============================================
37 ECOMP is a trademark and service mark of AT&T Intellectual Property.
39 <!DOCTYPE hibernate-mapping PUBLIC
40 "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
41 "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
43 <!-- Publishes mappings and queries specific to the ECOMP Portal application. -->
44 <hibernate-mapping package="org.openecomp.portalapp.portal.domain">
46 <!-- Widget class mapping details -->
47 <class name="Widget" table="FN_WIDGET">
48 <id name="id" column="WIDGET_ID">
49 <generator class="native">
50 <param name="sequence">seq_fn_widget</param>
53 <property name="width" type="integer">
54 <column name="WDG_WIDTH" not-null="true" default="0"></column>
56 <property name="height" type="integer">
57 <column name="WDG_HEIGHT" not-null="true" default="0"></column>
59 <property name="url" type="string">
60 <column name="WDG_URL" not-null="true" default="?"></column>
62 <property name="name" type="string">
63 <column name="WDG_NAME" not-null="true" default="?"></column>
66 <property name="appId" type="long">
67 <column name="APP_ID" not-null="true"></column>
72 <class name="EPEndpoint" table="EP_ENDPOINTS">
73 <id name="id" column="id">
74 <generator class="native">
75 <param name="sequence">seq_ep_endpoint</param>
78 <property name="name" type="string">
79 <column name="url" not-null="true"></column>
84 <class name="EPEndpointAccount" table="EP_ENDPOINTS_BASIC_AUTH_ACCOUNT">
85 <id name="id" column="id">
86 <generator class="native">
87 <param name="sequence">seq_ep_endpoints_basic_auth_account</param>
90 <property name="ep_id" type="long">
91 <column name="ep_id"></column>
93 <property name="account_id" type="long">
94 <column name="account_id"></column>
98 <class name="WidgetCatalogParameter" table="EP_WIDGET_CATALOG_PARAMETER">
99 <id name="id" column="id">
100 <generator class="native">
101 <param name="sequence">seq_ep_widget_catalog_parameter</param>
104 <property name="widgetId" type="long">
105 <column name="widget_id"></column>
107 <property name="userId" type="long">
108 <column name="user_id" not-null="true"></column>
110 <property name="paramId" type="long">
111 <column name="param_id" not-null="true"></column>
114 <property name="user_value" type="string">
115 <column name="user_value" not-null="true"></column>
120 <class name="BasicAuthCredentials" table="ep_basic_auth_account">
121 <id name="id" column="id">
122 <generator class="native">
123 <param name="sequence">seq_ep_basic_auth_account</param>
126 <property name="applicationName" type="string">
127 <column name="ext_app_name" not-null="true" default="?"></column>
129 <property name="username" type="string">
130 <column name="username"></column>
132 <property name="password" type="string">
133 <column name="password"></column>
135 <property name="isActive" type="string">
136 <column name="active_yn" not-null="true" default="Y"></column>
140 <!-- EPUserNotification class mapping details -->
141 <class name="EPUserNotification" table="ep_user_notification">
142 <id name="id" column="id">
143 <generator class="native">
144 <param name="sequence">seq_ep_user_notification</param>
147 <property name="userId" column="User_ID" />
148 <property name="notificationId" column="notification_ID" />
149 <property name="viewed" column="is_viewed" />
150 <property name="updateTime" column="updated_time" />
154 <!-- User class mapping details -->
155 <class name="EPUser" table="FN_USER">
156 <id name="id" column="user_id">
157 <generator class="native">
158 <param name="sequence">seq_fn_user</param>
162 <property name="orgId" column="org_id" />
163 <property name="managerId" column="manager_id" />
164 <property name="firstName" column="first_name" />
165 <property name="middleInitial" column="middle_name" />
166 <property name="lastName" column="last_name" />
167 <property name="phone" column="phone" />
168 <property name="fax" column="fax" />
169 <property name="cellular" column="cellular" />
170 <property name="email" column="email" />
171 <property name="addressId" column="address_id" />
172 <property name="alertMethodCd" column="alert_method_cd" />
174 <property name="address1" column="address_line_1" />
175 <property name="address2" column="address_line_2" />
176 <property name="city" column="city" />
177 <property name="state" column="state_cd" />
178 <property name="zipCode" column="zip_code" />
179 <property name="country" column="country_cd" />
181 <property name="hrid" column="hrid" />
182 <property name="orgUserId" column="org_user_id" />
183 <property name="orgCode" column="org_code" />
184 <property name="loginId" column="login_id" />
185 <property name="loginPwd" column="login_pwd" />
186 <property name="lastLoginDate" column="last_login_date" type="timestamp" />
188 <property name="locationClli" column="location_clli" />
189 <property name="orgManagerUserId" column="org_manager_userid" />
190 <property name="company" column="company" />
191 <property name="department" column="department" />
192 <property name="departmentName" column="department_name" />
193 <property name="businessUnit" column="business_unit" />
194 <property name="businessUnitName" column="business_unit_name" />
195 <property name="jobTitle" column="job_title" />
196 <property name="siloStatus" column="silo_status" />
197 <property name="financialLocCode" column="fin_loc_code" />
199 <property name="active" column="active_yn" type="yes_no" />
200 <property name="internal" column="is_internal_yn" type="yes_no" />
202 <property name="created" type="timestamp" column="created_date" />
203 <property name="modified" type="timestamp" column="modified_date" />
205 <property name="createdId" column="created_id" />
206 <property name="modifiedId" column="modified_id" />
207 <property name="timeZoneId" column="timezone" />
209 <set name="EPUserApps" table="FN_USER_ROLE" lazy="false" sort="natural"
211 <key column="user_id" />
212 <one-to-many class="org.openecomp.portalapp.portal.domain.EPUserApp" />
217 <class name="MicroserviceData" table="ep_microservice">
218 <id name="id" column="id">
219 <generator class="native">
220 <param name="sequence">seq_ep_microservice</param>
224 <property name="name" type="string">
225 <column name="name" not-null="true" default="?"></column>
228 <property name="desc" type="string">
229 <column name="description" not-null="true" default="?"></column>
232 <property name="appId" type="long">
233 <column name="appId" not-null="true"></column>
236 <property name="url" type="string">
237 <column name="endpoint_url" not-null="true" default="?"></column>
240 <property name="securityType" type="string">
241 <column name="security_type" not-null="true" default="?"></column>
245 <property name="username" type="string">
246 <column name="username" not-null="true" default="?"></column>
249 <property name="password" type="string">
250 <column name="password" not-null="true" default="?"></column>
253 <property name="active" type="string">
254 <column name="active" not-null="true" default="Y"></column>
258 <class name="MicroserviceParameter" table="ep_microservice_parameter">
259 <id name="id" column="id">
260 <generator class="native">
261 <param name="sequence">seq_ep_microservice_parameter</param>
265 <property name="serviceId" type="long">
266 <column name="service_id" not-null="true"></column>
269 <property name="para_key" type="string">
270 <column name="para_key" not-null="true" default="?"></column>
273 <property name="para_value" type="string">
274 <column name="para_value" not-null="true" default="?"></column>
278 <!-- UserApp class mapping details -->
279 <class name="EPUserApp" table="fn_user_role">
281 <key-property name="userId" type="long">
282 <column name="user_id" precision="11" scale="0" />
284 <key-many-to-one name="app"
285 class="org.openecomp.portalapp.portal.domain.EPApp" column="app_id" />
286 <key-many-to-one name="role"
287 class="org.openecomp.portalapp.portal.domain.EPRole" column="role_id" />
289 <property name="priority" type="java.lang.Short">
290 <column name="priority" precision="4" scale="0" />
294 <!-- User App class mapping details -->
295 <class name="EPApp" table="fn_app">
296 <id name="id" column="app_id">
297 <generator class="native">
298 <param name="sequence">seq_fn_app</param>
301 <property name="name" type="string">
302 <column name="app_name" not-null="true" default="?"></column>
304 <property name="imageUrl" column="app_image_url" />
305 <property name="description" column="app_description" />
306 <property name="notes" column="app_notes" />
307 <property name="url" column="app_url" />
308 <property name="alternateUrl" column="app_alternate_url" />
309 <property name="appRestEndpoint" column="app_rest_endpoint" />
310 <property name="mlAppName" type="string">
311 <column name="ml_app_name" not-null="true" default="?"></column>
313 <property name="mlAppAdminId" type="string">
314 <column name="ml_app_admin_id" not-null="true" default="?"></column>
316 <property name="motsId" column="mots_id" />
317 <property name="appPassword" type="string">
318 <column name="app_password" not-null="true" default="?"></column>
320 <property name="thumbnail" column="thumbnail" />
321 <property name="username" type="string">
322 <column name="app_username" not-null="true" default="?"></column>
324 <property name="open" type="yes_no">
325 <column name="open" not-null="true" default="Y"></column>
327 <property name="enabled" type="yes_no">
328 <column name="enabled" not-null="true" default="N"></column>
330 <property name="uebTopicName" type="string">
331 <column name="ueb_topic_name"></column>
333 <property name="uebKey" type="string">
334 <column name="ueb_key"></column>
336 <property name="uebSecret" type="string">
337 <column name="ueb_secret"></column>
339 <property name="appType" type="integer">
340 <column name="app_type"></column>
342 <property name="centralAuth" type="yes_no">
343 <column name="auth_central"></column>
345 <property name="nameSpace" type="string">
346 <column name="auth_namespace"></column>
348 <one-to-one name="contactUs" class="AppContactUs" cascade="save-update"></one-to-one>
352 <!-- EPUserAppsSortPreference class mapping details -->
353 <class name="EPUserAppsSortPreference" table="ep_pers_user_app_sort">
354 <id name="id" column="ID">
355 <generator class="native">
356 <param name="sequence">seq_ep_usrappsrtprf</param>
359 <property name="userId" column="USER_ID" />
360 <property name="sortPref" column="SORT_PREF" />
363 <!-- EPUserAppsManualSortPreference class mapping details -->
364 <class name="EPUserAppsManualSortPreference" table="ep_pers_user_app_man_sort">
365 <id name="id" column="ID">
366 <generator class="native">
367 <param name="sequence">seq_ep_usrmanappsrtprf</param>
370 <property name="userId" column="user_id"
371 unique-key="uk_1_ep_pers_user_app_man_sort" />
372 <property name="appId" column="app_id"
373 unique-key="uk_1_ep_pers_user_app_man_sort" />
374 <property name="appManualSortOrder" column="sort_order" type="int" />
377 <class name="EPWidgetsManualSortPreference" table="ep_pers_user_widget_placement">
378 <id name="id" column="ID">
379 <generator class="native">
380 <param name="sequence">seq_ep_usrwidgetsrtprf</param>
383 <property name="userId" column="user_id" />
384 <property name="widgetId" column="widget_id" />
385 <property name="widgetRow" column="x" />
386 <property name="widgetCol" column="y" />
387 <property name="widgetHeight" column="height" />
388 <property name="widgetWidth" column="width" />
391 <!-- User Role class mapping details -->
392 <class name="EPRole" table="FN_ROLE" >
393 <id name="id" column="role_id">
394 <generator class="native">
395 <param name="sequence">seq_fn_role</param>
399 <property name="name" column="role_name" />
400 <property name="priority" column="priority" />
401 <property name="active" column="active_yn" type="yes_no" />
402 <!-- 2 lines below may be removed! -->
403 <property name="appId" column="APP_ID" />
404 <property name="appRoleId" column="APP_ROLE_ID" />
405 <!-- 2 lines above may be removed -->
407 <set name="roleFunctions" table="FN_ROLE_FUNCTION" lazy="false"
409 <key column="role_id" />
410 <many-to-many column="function_cd"
411 class="org.openecomp.portalsdk.core.domain.RoleFunction" />
414 <set name="childRoles" table="FN_ROLE_COMPOSITE" lazy="false"
416 <key column="parent_role_id" />
417 <many-to-many column="child_role_id"
418 class="org.openecomp.portalapp.portal.domain.EPRole" />
421 <set name="parentRoles" table="FN_ROLE_COMPOSITE" lazy="false"
423 <key column="child_role_id" />
424 <many-to-many column="parent_role_id"
425 class="org.openecomp.portalapp.portal.domain.EPRole" />
430 <!-- User App class mapping details -->
431 <class name="AppContactUs" table="fn_app_contact_us">
432 <id name="id" column="app_id">
433 <generator class="foreign">
434 <param name="property">app</param>
437 <property name="url" column="url" />
438 <property name="description" column="description" />
439 <property name="contactName" column="contact_name" />
440 <property name="contactEmail" column="contact_email" />
441 <property name="activeYN" column="active_yn" />
443 <one-to-one name="app" class="EPApp" constrained="true"></one-to-one>
447 <!-- Personalization of user app selections -->
448 <class name="PersUserAppSelection" table="fn_pers_user_app_sel">
449 <id name="id" column="id">
450 <generator class="native">
451 <param name="property">seq_fn_pers_user_app_sel</param>
454 <property name="userId" column="user_id" />
455 <property name="appId" column="app_id" />
456 <property name="statusCode" column="status_cd" />
459 <!-- EPAppRoleFunction class mapping details -->
460 <class name="EPAppRoleFunction" table="ep_app_role_function">
461 <id name="id" column="id">
462 <generator class="native">
463 <param name="property">seq_epp_app_role_func</param>
466 <property name="roleId" column="role_id"></property>
467 <property name="appId" column="app_id"></property>
468 <property name="code" column="function_cd"></property>
471 <!-- CentralRoleFunction class mapping details -->
472 <class name="CentralRoleFunction" table="ep_app_function">
474 <key-property name="appId"
476 <key-property name="code"
477 column="function_cd" />
479 <property name="name" column="function_name" />
482 <class name="PersUserWidgetSelection" table="ep_pers_user_widget_sel">
483 <id name="id" column="id">
484 <generator class="native">
485 <param name="property">seq_ep_pers_user_widget_sel</param>
488 <property name="userId" column="user_id" />
489 <property name="widgetId" column="widget_id" />
490 <property name="statusCode" column="status_cd" />
493 <!-- EPUserAppRolesRequest class mapping details -->
494 <class name="EPUserAppRolesRequest" table="ep_user_roles_request">
495 <id name="id" column="req_id">
496 <generator class="native">
497 <param name="sequence">seq_ep_user_role_request</param>
500 <property name="userId" column="user_id" />
501 <property name="appId" column="app_id" />
502 <property name="createdDate" column="created_date" type="timestamp" />
503 <property name="updatedDate" column="updated_date" type="timestamp" />
504 <property name="requestStatus" column="request_status" />
506 <set name="epRequestIdDetail" table="ep_user_roles_request_det"
507 lazy="false" cascade="save-update, persist" inverse="true" fetch="select">
508 <key column="req_id" />
510 class="org.openecomp.portalapp.portal.domain.EPUserAppRolesRequestDetail" />
514 <!-- EPUserAppRolesRequestDetail class mapping details -->
515 <class name="EPUserAppRolesRequestDetail" table="ep_user_roles_request_det">
516 <id name="id" column="id">
517 <generator class="native">
518 <param name="sequence">seq_ep_user_role_request_det</param>
521 <property name="reqRoleId" column="requested_role_id" />
522 <property name="reqType" column="request_type" />
523 <many-to-one name="epRequestIdData" fetch="select"
524 class="org.openecomp.portalapp.portal.domain.EPUserAppRolesRequest">
525 <column name="req_id" not-null="true" />
529 <!-- show the current user plus related users -->
530 <sql-query name="relatedUsers">
531 <return-scalar column="org_user_id" type="java.lang.String" />
533 select t.org_user_id from (
534 select distinct c.org_user_id org_user_id, c.last_login_date from
535 (select c.org_user_id, c.last_login_date from
537 (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,
539 where a.ROLE_ID = b.role_id
540 and a.APP_ID = b.app_id
541 and a.USER_ID = c.user_id
543 select org_user_id , last_login_date from fn_user where org_user_id=:userId
545 order by c.last_login_date desc limit 10
550 <!-- requires values for named parameters :searchQuery and :userId -->
551 <sql-query name="searchPortal">
552 <return alias="searchResult"
553 class="org.openecomp.portalapp.portal.ecomp.model.SearchResultItem" />
555 select @rn /*'*/:=/*'*/ @rn+1 AS rowId, CATEGORY, NAME, UUID, TARGET from
557 select distinct 'User' CATEGORY, concat(concat(first_name, ' '), last_name) NAME, user_id UUID, org_user_id TARGET
559 upper( :searchQuery ) != ''
560 and (upper(first_name) like upper(CONCAT('%', :searchQuery, '%'))
561 or upper(last_name) like upper(CONCAT('%', :searchQuery, '%'))
562 or upper(org_user_id) like upper(CONCAT('%', :searchQuery, '%')))
563 and upper(active_yn) = 'Y'
567 select distinct 'Application' CATEGORY, b1.app_name NAME,
568 if (b1.app_type = 2,'false','true') UUID,
569 if (a1.app_id is null, if (b1.app_type = 2, b1.APP_URL, ''), a1.APP_URL) TARGET
573 from fn_user_role a, fn_user b, fn_app c
574 where a.USER_ID = b.user_id
575 and upper(b.org_user_id) = upper( :userId )
576 and a.app_id = c.app_id
577 and upper(c.APP_NAME) like upper(CONCAT('%', :searchQuery, '%'))
578 and upper(c.ENABLED) = 'Y'
579 ) a1 right outer join
580 (select * from fn_app where
581 upper(APP_NAME) like upper(CONCAT('%', :searchQuery, '%'))
582 and upper(ENABLED) = 'Y') b1
583 on a1.APP_ID = b1.app_id
587 select distinct 'Menu' CATEGORY, b1.TEXT NAME,
588 if (b1.app_type = 2,'false','true') UUID,
589 if (a1.menu_id is null , if (b1.app_type = 2, b1.URL, '') , a1.URL ) TARGET
591 (select distinct d.*, e.app_type from fn_user a,
592 fn_user_role b, fn_menu_functional_roles c, fn_menu_functional d, fn_app e
593 where a.USER_ID = b.user_id
594 and b.role_id = c.role_id
595 and c.menu_id = d.menu_id
596 and upper(a.org_user_id) = upper( :userId )
597 and c.APP_ID = e.app_id
598 and upper(d.TEXT) like upper(CONCAT('%', :searchQuery, '%'))
599 and upper(d.active_yn) = 'Y'
600 and upper(e.enabled) = 'Y'
601 ) a1 right outer join
603 select distinct a.*, c.app_type from fn_menu_functional a, fn_menu_functional_roles b, fn_app c
604 where active_yn = 'Y'
605 and upper(TEXT) like upper(CONCAT('%', :searchQuery, '%'))
606 and a.menu_id = b.menu_id
607 and b.app_id = c.app_id
610 on a1.menu_id = b1.menu_id
613 select distinct CATEGORY, NAME, UUID, TARGET
617 select distinct 'Widget' CATEGORY, d.wdg_name NAME,
620 from fn_user a, fn_user_role b, ep_widget_catalog_role c, ep_widget_catalog d
621 where upper(a.org_user_id) = upper( :userId )
622 and a.user_id = b.user_id
623 and b.role_id = c.role_id
624 and c.widget_id = d.widget_id
625 and d.all_user_flag<>'Y'
626 and (upper(d.wdg_name) like upper(CONCAT('%', :searchQuery, '%')) or upper(d.wdg_desc) like upper(CONCAT('%', :searchQuery, '%')))
630 select distinct 'Widget' CATEGORY, d.wdg_name NAME,
633 from ep_widget_catalog d
634 where d.all_user_flag='Y'
635 and (upper(d.wdg_name) like upper(CONCAT('%', :searchQuery, '%')) or upper(d.wdg_desc) like upper(CONCAT('%', :searchQuery, '%')))
639 select distinct 'Widget' CATEGORY, d.wdg_name NAME,
642 from fn_user a, fn_user_role b, ep_widget_catalog d
643 where upper(a.org_user_id) = upper( :userId )
644 and a.user_id = b.user_id
646 and (upper(d.wdg_name) like upper(CONCAT('%', :searchQuery, '%')) or upper(d.wdg_desc) like upper(CONCAT('%', :searchQuery, '%')))
650 (SELECT @rn /*'*/:=/*'*/ 0) t2 order by NAME
656 <!-- requires values for named parameters :searchQuery and :userId -->
657 <sql-query name="getNotifications">
658 <return alias="notificationResult"
659 class="org.openecomp.portalapp.portal.transport.EpNotificationItem" />
661 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
664 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
667 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
670 select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN,
671 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
672 from ep_notification a, ep_role_notification b
673 where a.notification_id = b.notification_id
674 and (end_time is null || SYSDATE() <= end_time )
675 and (start_time is null || SYSDATE() >= start_time)
676 and a.is_for_all_roles = 'N'
679 select distinct a.user_id, c.role_id, c.app_id, d.APP_NAME
680 from fn_user a, fn_user_role b, fn_role c, fn_app d
681 where COALESCE(c.app_id,1) = d.app_id
682 and a.user_id = b.user_id
683 and a.user_id = :user_id
684 and b.role_id = c.role_id
685 and (d.enabled='Y' or d.app_id=1)
689 a.role_id = b.role_id
692 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
695 select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN,
696 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
697 from ep_notification a, ep_role_notification b
698 where a.notification_id = b.notification_id
699 and (end_time is null || SYSDATE() <= end_time )
700 and (start_time is null || SYSDATE() >= start_time)
701 and a.is_for_all_roles = 'N'
705 a.recv_user_id=:user_id
709 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
710 from ep_notification a
711 where a.notification_id
712 and (end_time is null || SYSDATE() <= end_time )
713 and (start_time is null || SYSDATE() >= start_time)
714 and a.is_for_all_roles = 'Y'
722 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'
724 order by priority desc, created_date desc,start_Time desc
728 (SELECT @rn /*'*/:=/*'*/ 0) t2
734 <sql-query name="getUsersByOrgIdsNotifications">
735 <return alias="getUsersByOrgIdsNotificationsResult" class="org.openecomp.portalapp.portal.domain.EPUser"/>
737 SELECT * from fn_user where org_user_id in (:OrgIds)
743 <!-- Gets all Admin notification history; accepts no parameters -->
744 <sql-query name="getAdminNotificationHistoryVO">
745 <return alias="adminNotificationHistoryVOResult"
746 class="org.openecomp.portalapp.portal.transport.EpNotificationItemVO" />
750 n.notification_ID AS notificationId,
751 n.is_for_online_users AS isForOnlineUsers,
752 n.is_for_all_roles AS isForAllRoles,
753 n.active_YN AS activeYn,
754 n.msg_header AS msgHeader,
755 n.msg_description AS msgDescription,
756 n.msg_source AS msgSource,
757 n.start_Time AS startTime,
758 n.end_time AS endTime,
760 n.creator_ID AS creatorId,
761 n.created_date AS createdDate,
762 n.notification_hyperlink AS notificationHyperlink,
763 u.org_user_id AS loginId
767 fn_user u on u.user_id = n.creator_id
769 n.active_YN='Y' and msg_source='EP'
774 n.notification_ID AS notificationId,
775 n.is_for_online_users AS isForOnlineUsers,
776 n.is_for_all_roles AS isForAllRoles,
777 n.active_YN AS activeYn,
778 n.msg_header AS msgHeader,
779 n.msg_description AS msgDescription,
780 n.msg_source AS msgSource,
781 n.start_Time AS startTime,
782 n.end_time AS endTime,
784 n.creator_ID AS creatorId,
785 n.created_date AS createdDate,
786 n.notification_hyperlink AS notificationHyperlink,
787 u.org_user_id AS loginId
791 fn_user u on u.user_id = n.creator_id
793 ep_role_notification r on r.notification_ID=n.notification_ID
795 n.active_YN='Y' and r.recv_user_id = :user_id and msg_source!='EP'
796 )n order by n.startTime desc
800 <sql-query name="getEpNotificationAppRoles">
801 <return alias="notificationAppRoles"
802 class="org.openecomp.portalapp.portal.domain.EcompAppRole" />
803 <!-- This query requires no parameters. -->
806 select a.app_id, a.app_name, b.role_id, b.role_name from
807 (select * from fn_app where app_id = 1) a,
808 (select * from fn_role where app_id is null and active_yn = 'Y' and role_id <> 1) b
810 select fn_role.app_id,fn_app.app_name, fn_role.role_id ,fn_role.role_name
812 where fn_role.app_id = fn_app.app_id and fn_app.enabled='Y' and fn_role.active_yn='Y' order by app_name
819 <!-- Gets all notification history -->
820 <sql-query name="getNotificationHistoryVO">
821 <return alias="notificationHistoryVOResult"
822 class="org.openecomp.portalapp.portal.transport.EpNotificationItemVO" />
825 notificationId, isForOnlineUsers, isForAllRoles, msgHeader, msgDescription,msgSource,
826 startTime, endTime, priority, createdDate,notificationHyperlink,creatorId, loginId, activeYn
830 a.notification_ID AS notificationId,
831 is_for_online_users AS isForOnlineUsers,
832 is_for_all_roles AS isForAllRoles,
833 msg_header AS msgHeader,
834 msg_description AS msgDescription,
835 msg_source AS msgSource,
836 start_Time AS startTime,
839 created_date AS createdDate,
840 creator_ID AS creatorId,
841 notification_hyperlink AS notificationHyperlink,
843 active_YN AS activeYn,
844 if (is_viewed is null, 'N', is_viewed)
848 user_id, login_id,notification_id, is_for_online_users, is_for_all_roles,
849 msg_header, msg_description,msg_source, start_Time, end_time, priority, created_date,
850 creator_ID,notification_hyperlink,active_YN
853 select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN,
854 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,
855 b.role_id,CASE WHEN a.creator_ID IS NOT NULL THEN u.org_user_id
858 END AS login_id,b.recv_user_id
859 from ep_notification a, ep_role_notification b,fn_user u
860 where a.notification_id = b.notification_id and (u.user_id=a.creator_ID OR a.creator_ID IS NULL)
861 and a.is_for_all_roles = 'N'
863 (start_time is null and end_time is null and a.created_date >= DATE_ADD(curdate(),INTERVAL-31 DAY))
865 (start_time is not null and end_time is null and start_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
867 (start_time is null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
869 (start_time is not null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
873 select distinct a.user_id, c.role_id, c.app_id, d.APP_NAME
874 from fn_user a, fn_user_role b, fn_role c, fn_app d
875 where COALESCE(c.app_id,1) = d.app_id
876 and a.user_id = b.user_id
877 and a.user_id = :user_id
878 and b.role_id = c.role_id
879 and (d.enabled='Y' or d.app_id=1)
883 a.role_id = b.role_id
887 :user_id, login_id,notification_id, is_for_online_users, is_for_all_roles,
888 msg_header, msg_description,msg_source, start_Time, end_time, priority, created_date,
889 creator_ID,notification_hyperlink,active_YN
892 select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN,
893 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,
894 b.role_id,CASE WHEN a.creator_ID IS NOT NULL THEN u.org_user_id
897 END AS login_id,b.recv_user_id
898 from ep_notification a, ep_role_notification b,fn_user u
899 where a.notification_id = b.notification_id and (u.user_id=a.creator_ID OR a.creator_ID IS NULL)
900 and a.is_for_all_roles = 'N'
902 (start_time is null and end_time is null and a.created_date >= DATE_ADD(curdate(),INTERVAL-31 DAY))
904 (start_time is not null and end_time is null and start_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
906 (start_time is null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
908 (start_time is not null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
913 a.recv_user_id=:user_id
918 :user_id user_id, b.login_id,notification_id, is_for_online_users, is_for_all_roles,
919 msg_header, msg_description,msg_source, start_Time, end_time, priority, a.created_date,
920 creator_ID, a.notification_hyperlink,a.active_YN
921 from ep_notification a JOIN fn_user b on b.user_id=a.creator_ID
922 where a.notification_id
923 and a.is_for_all_roles = 'Y'
925 (start_time is null and end_time is null and a.created_date >= DATE_ADD(curdate(),INTERVAL-31 DAY))
927 (start_time is not null and end_time is null and start_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
929 (start_time is null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
931 (start_time is not null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
934 ) a left outer join (
935 select m.notification_ID, m.is_viewed from ep_user_notification m where user_id = :user_id
937 on a.notification_id = m.notification_ID
941 order by start_Time desc,end_time desc
943 (SELECT @rn /*'*/:=/*'*/ 0) t2 where startTime<=SYSDATE()
948 <!-- shows the received recipient to whom the notification is delivered from external system -->
949 <sql-query name="messageRecipients">
950 <return-scalar column="org_user_id" type="java.lang.String" />
952 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
957 <!-- Gets role details for a specified notification -->
958 <sql-query name="getNotificationRoles">
959 <return alias="notificationRolesResult"
960 class="org.openecomp.portalapp.portal.transport.EpRoleNotificationItem" />
966 notification_Id = :notificationId
970 <!-- Gets all applications, possibly with contact information -->
971 <sql-query name="getAppsAndContacts">
972 <return alias="appContactUs"
973 class="org.openecomp.portalapp.portal.ecomp.model.AppContactUsItem" />
974 <!-- This query requires no parameters. -->
977 a.app_id as appId, a.app_name as appName,
978 c.contact_name as contactName,
979 c.contact_email as contactEmail, c.url, c.description,
980 c.active_yn as activeYN
985 on a.app_id = c.app_id
987 a.enabled = 'Y' and a.app_name is not null and a.app_name != ''
992 <!-- Gets all rows from the app-contact-us table, extended with app information -->
993 <sql-query name="getAppContactUsItems">
994 <return alias="appContactUs"
995 class="org.openecomp.portalapp.portal.ecomp.model.AppContactUsItem" />
996 <!-- This query requires no parameters. -->
999 c.app_id as appId, c.contact_name as contactName,
1000 c.contact_email as contactEmail, c.url, c.description,
1001 c.active_yn as activeYN, a.app_name as appName
1006 on a.app_id = c.app_id
1008 a.enabled = 'Y' and a.app_name is not null and a.app_name != ''
1013 <!-- Gets one row for each app with the category and catenated functions. -->
1014 <sql-query name="getAppCategoryFunctions">
1015 <return alias="appCategoryFunctions"
1016 class="org.openecomp.portalapp.portal.ecomp.model.AppCategoryFunctionsItem" />
1017 <!-- This query requires no parameters. -->
1019 SELECT @rn /*'*/:=/*'*/ @rn+1 AS rowId,
1020 r.category as category, r.app_id as appId, r.app_name as application,
1021 group_concat(function_text separator ', ') as functions
1024 app_id, function_text, app_name,
1025 IF (a.parent_menu_id IS NULL, fn.parent_menu, a.text) category
1028 j.text function_text, j.menu_id function_id, j.app_id app_id, j.app_name app_name,
1029 k.text parent_menu, k.parent_menu_id parent_menu_id
1032 x.Text, x.PARENT_MENU_ID, x.menu_id, y.app_id, z.app_name
1034 fn_menu_functional x, fn_menu_functional_roles y, fn_app z
1036 x.MENU_ID = y.menu_id AND x.ACTIVE_YN = 'Y' AND y.app_id = z.app_id
1038 fn_menu_functional k
1039 WHERE j.parent_menu_id = k.menu_id
1041 fn_menu_functional a
1042 WHERE fn.parent_menu_id = a.menu_id
1044 (SELECT @rn /*'*/:=/*'*/ 0) t2
1045 group by r.category, r.app_id, r.app_name
1046 order by category, app_name
1051 <!-- Gets one row for each function-application-role combination. -->
1052 <sql-query name="getAppAccessFunctionRole">
1053 <return alias="appAccessFunctionRole"
1054 class="org.openecomp.portalapp.portal.domain.GetAccessResult" />
1055 <!-- This query requires one parameters. -->
1057 select @rn /*'*/:=/*'*/ @rn+1 AS row_id, t.role_id, t.ECOMP_FUNCTION, t.APP_NAME, t.APP_MOTS_ID, t.ROLE_NAME,
1058 (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,
1059 o.requested_role_id,o.request_type
1061 (select ECOMP_FUNCTION, APP_NAME, APP_MOTS_ID, ROLE_NAME ,a.app_id,a.role_id
1064 select D.APP_NAME, D.MOTS_ID APP_MOTS_ID, C.ROLE_NAME, d.app_id , c.role_id
1067 where COALESCE(c.app_id,1) = d.app_id
1068 and (d.enabled='Y' or d.app_id=1)
1069 and c.active_yn = 'Y'
1072 select A.TEXT ECOMP_FUNCTION, b.role_id, b.app_id from fn_menu_functional A, fn_menu_functional_roles B
1074 A.MENU_ID = B.MENU_ID
1077 on a.role_id = b.role_id) t left outer join
1078 (select ed.requested_role_id, er.app_id ,ed.request_type from ep_user_roles_request er , ep_user_roles_request_det ed
1079 where er.req_id=ed.req_id
1080 and upper(ed.request_type)='P'
1081 and er.user_id =:userId) o
1082 on t.app_id=o.app_id
1083 and t.role_id = o.requested_role_id
1084 JOIN (SELECT @rn /*'*/:=/*'*/ 0) p
1089 <query name="getCommonWidgetItem">
1090 from CommonWidget where category = :cat order by
1094 <sql-query name="getGuestLastLogin">
1095 <return-scalar column="audit_date" type="java.util.Date" />
1097 select audit_date from fn_audit_log where affected_record_id =:userId order by audit_date desc limit 2;
1101 <sql-query name="getActiveUsers">
1102 <return alias="EPUser" class="org.openecomp.portalapp.portal.domain.EPUser" />
1104 SELECT * FROM FN_USER WHERE active_yn='Y'
1108 <sql-query name="getAppsAdmins">
1109 <return alias="adminUserApp" class="org.openecomp.portalapp.portal.domain.AdminUserApp" />
1111 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)
1115 <sql-query name="getPortalAdmins">
1116 <return alias="portalAdmin" class="org.openecomp.portalapp.portal.transport.PortalAdmin" />
1118 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
1122 <!-- Gets personalized list of enabled and accessible apps for regular user -->
1123 <sql-query name="getPersUserApps">
1124 <return alias="persUserApps" class="org.openecomp.portalapp.portal.domain.EPApp" />
1125 <!-- This query requires one parameter: userId (number) -->
1128 distinct -- multiple roles yield multiple rows
1129 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1130 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1131 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1132 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1133 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL
1135 -- Portal assigns role 999 to app administrator
1136 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1137 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1138 where a.ENABLED = 'Y'
1140 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1142 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1149 <!-- Gets personalized list of enabled and accessible apps for Portal (super)
1151 <sql-query name="getPersAdminApps">
1152 <return alias="persAdminApps" class="org.openecomp.portalapp.portal.domain.EPApp" />
1153 <!-- This query requires one parameter: userId (number) -->
1156 distinct -- multiple roles yield multiple rows
1157 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1158 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1159 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1160 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1161 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1164 -- Portal assigns role 999 to app administrator
1165 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1166 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1167 where a.ENABLED = 'Y'
1169 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1171 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1173 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1180 <!-- Gets personalized list of enabled and accessible Admin apps by name -->
1181 <sql-query name="getPersAdminAppsOrderByName">
1182 <return alias="AdminAppsOrderByName" class="org.openecomp.portalapp.portal.domain.EPApp" />
1183 <!-- This query requires one parameter: userId (number) -->
1186 distinct -- multiple roles yield multiple rows
1187 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1188 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1189 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1190 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1191 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1194 -- Portal assigns role 999 to app administrator
1195 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1196 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1197 where a.ENABLED = 'Y'
1199 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1201 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1203 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1211 <!--Gets personalized list of enabled and accessible User apps by name -->
1212 <sql-query name="getPersUserAppsOrderByName">
1213 <return alias="UserAppsOrderByName" class="org.openecomp.portalapp.portal.domain.EPApp" />
1214 <!-- This query requires one parameter: userId (number) -->
1217 distinct -- multiple roles yield multiple rows
1218 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1219 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1220 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1221 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1222 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1225 -- Portal assigns role 999 to app administrator
1226 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1227 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1228 where a.ENABLED = 'Y'
1230 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1232 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1241 <!--Gets personalized list of enabled and accessible Super Admin apps by
1243 <sql-query name="getAdminAppsOrderByLastUsed">
1244 <return alias="AdminAppsOrderByLastUsed" class="org.openecomp.portalapp.portal.domain.EPApp" />
1250 distinct -- multiple roles yield multiple rows
1251 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1252 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1253 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1254 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1255 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1258 -- Portal assigns role 999 to app administrator
1259 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1260 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1261 where a.ENABLED = 'Y'
1263 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1265 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1267 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1272 c.AFFECTED_RECORD_ID, c.AUDIT_DATE
1275 on A.app_id = B.Affected_record_id
1276 order by AUDIT_DATE DESC;
1280 <!--Gets personalized list of enabled and accessible User apps by LastUsed -->
1281 <sql-query name="getUserAppsOrderByLastUsed">
1282 <return alias="UserAppsOrderByLastUsed" class="org.openecomp.portalapp.portal.domain.EPApp" />
1285 select * from (select
1286 distinct -- multiple roles yield multiple rows
1287 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1288 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1289 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1290 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1291 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1294 -- Portal assigns role 999 to app administrator
1295 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1296 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1297 where a.ENABLED = 'Y'
1299 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1301 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1305 c.AFFECTED_RECORD_ID, c.AUDIT_DATE
1308 on A.app_id = B.Affected_record_id
1309 order by AUDIT_DATE DESC;
1316 <!--Gets personalized list of enabled and accessible Super Admin apps by
1318 <sql-query name="getAdminAppsOrderByMostUsed">
1319 <return alias="AdminAppsOrderByMostUsed" class="org.openecomp.portalapp.portal.domain.EPApp" />
1325 distinct -- multiple roles yield multiple rows
1326 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1327 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1328 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1329 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1330 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1333 -- Portal assigns role 999 to app administrator
1334 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1335 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1336 where a.ENABLED = 'Y'
1338 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1340 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1342 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1346 (select FN_AUDIT_LOG.AFFECTED_RECORD_ID, COUNT(*) AS total_visits
1347 from FN_APP LEFT JOIN FN_AUDIT_LOG
1348 on FN_APP.APP_ID = FN_AUDIT_LOG.AFFECTED_RECORD_ID
1349 where FN_APP.ENABLED = 'Y' and FN_AUDIT_LOG.user_id=:userId
1350 and DATE(audit_date) BETWEEN (CURDATE() - INTERVAL 90 DAY) and CURDATE()
1351 group by FN_AUDIT_LOG.AFFECTED_RECORD_ID
1353 on A.app_id = B.Affected_record_id
1354 order by total_visits DESC;
1359 <!--Gets personalized list of enabled and accessible NON Super Admin User
1360 apps by Most Used -->
1361 <sql-query name="getUserAppsOrderByMostUsed">
1362 <return alias="UserAppsOrderByMostUsed" class="org.openecomp.portalapp.portal.domain.EPApp" />
1367 distinct -- multiple roles yield multiple rows
1368 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1369 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1370 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1371 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1372 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1375 -- Portal assigns role 999 to app administrator
1376 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1377 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1378 where a.ENABLED = 'Y'
1380 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1382 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1385 (select FN_AUDIT_LOG.AFFECTED_RECORD_ID, COUNT(*) AS total_visits
1386 from FN_APP LEFT JOIN FN_AUDIT_LOG
1387 on FN_APP.APP_ID = FN_AUDIT_LOG.AFFECTED_RECORD_ID
1388 where FN_APP.ENABLED = 'Y' and FN_AUDIT_LOG.user_id=:userId
1389 and DATE(audit_date) BETWEEN (CURDATE() - INTERVAL 90 DAY) and CURDATE()
1390 group by FN_AUDIT_LOG.AFFECTED_RECORD_ID
1392 on A.app_id = B.Affected_record_id
1393 order by total_visits DESC;
1397 <!--Gets personalized list of enabled and accessible Super Admin apps by
1399 <sql-query name="getAdminAppsOrderByManual">
1400 <return alias="AdminAppsOrderByManual" class="org.openecomp.portalapp.portal.domain.EPApp" />
1405 distinct -- multiple roles yield multiple rows
1406 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1407 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1408 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1409 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1410 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1413 -- Portal assigns role 999 to app administrator
1414 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1415 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1416 where a.ENABLED = 'Y'
1418 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1420 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1422 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1425 (select m.app_id, m.sort_order
1426 from ep_pers_user_app_man_sort m
1427 where USER_ID = :userId
1429 on A.APP_ID = B.app_id
1430 order by sort_order ASC
1436 <!--Gets personalized list of enabled and accessible NON Super admin User
1438 <sql-query name="getUserAppsOrderByManual">
1439 <return alias="UserAppsOrderByManual" class="org.openecomp.portalapp.portal.domain.EPApp" />
1442 select * from (select
1443 distinct -- multiple roles yield multiple rows
1444 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1445 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1446 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1447 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1448 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1451 -- Portal assigns role 999 to app administrator
1452 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1453 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1454 where a.ENABLED = 'Y'
1456 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1458 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1461 (select m.app_id, m.sort_order
1462 from ep_pers_user_app_man_sort m
1463 where USER_ID = :userId
1465 on A.APP_ID = B.app_id
1466 order by sort_order ASC
1471 <!-- Gets regular user's list of enabled apps including accessible and select
1473 <sql-query name="getUserAppCatalog">
1474 <return alias="userAppCatalog"
1475 class="org.openecomp.portalapp.portal.ecomp.model.AppCatalogItem" />
1476 <!-- This query requires one parameter: userId (number) -->
1479 distinct -- multiple roles yield multiple rows
1480 a.APP_ID as id, a.APP_NAME as name, a.ML_APP_NAME as mlAppName, a.APP_IMAGE_URL as imageUrl,
1481 a.APP_DESCRIPTION as description, a.APP_NOTES as notes,
1482 a.APP_URL as url, a.APP_ALTERNATE_URL as alternateUrl,
1483 IF(a.app_type = '2', TRUE, FALSE) as restricted,
1484 IF(a.open = 'Y', TRUE, FALSE) as open,
1485 -- ACCESS(-ible) means user has a defined role OR the application is open
1487 -- regular app and user has a role
1488 when a.OPEN = 'N' and r.ROLE_ID is not null then TRUE
1490 when a.OPEN = 'Y' then TRUE
1493 -- SELECT(-ed) indicates user personalization
1495 -- regular app, user has a role, no personalization
1496 when a.OPEN = 'N' and r.ROLE_ID is not null and p.STATUS_CD is null then TRUE
1497 -- open app and has personalization
1498 when a.OPEN = 'Y' and p.STATUS_CD is not null then TRUE
1501 -- PENDING indicates user checked a box
1503 when a.OPEN = 'N' and q.request_status = 'P' then TRUE
1507 -- Portal assigns role 999 to app administrator
1508 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1509 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1510 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'
1511 where a.ENABLED = 'Y'
1513 -- Show accessible apps first, then the rest; sort by name within each set.
1514 order by access desc, app_name asc
1519 <sql-query name="getMyloginAppDetails">
1520 <return alias="myloginAppDetails" class="org.openecomp.portalapp.portal.domain.EPApp" />
1523 SELECT * FROM fn_app where ml_app_name =:appName
1528 <sql-query name="getMyAppDetailsByUebKey">
1529 <return alias="myAppDetailsByUebKey" class="org.openecomp.portalapp.portal.domain.EPApp" />
1533 SELECT * FROM fn_app where ueb_key =:appKey
1539 <sql-query name="deleteAccountEndpoint">
1541 DELETE FROM ep_endpoints_basic_auth_account WHERE account_id =:accountId
1546 <sql-query name="deleteAccountEndpointRecord">
1548 DELETE FROM ep_endpoints_basic_auth_account WHERE account_id =:accountId AND ep_id =:epId
1553 <sql-query name="deleteEPEndpoint">
1555 DELETE FROM ep_endpoints WHERE id =:epId
1560 <sql-query name="deleteBasicAuthAccount">
1562 DELETE FROM ep_basic_auth_account WHERE id =:accountId
1567 <sql-query name="getAppRoles">
1568 <return alias="appRoles" class="org.openecomp.portalapp.portal.domain.EPRole" />
1571 SELECT * FROM fn_role where role_name =:roleName and app_id =:appId
1576 <sql-query name="getPortalAppRoles">
1577 <return alias="appRoles" class="org.openecomp.portalapp.portal.domain.EPRole" />
1581 SELECT * FROM fn_role where role_name =:appRoleName and app_id is null
1586 <sql-query name="deleteMicroserviceParameter">
1589 DELETE FROM ep_microservice_parameter WHERE service_id =:serviceId
1594 <sql-query name="deleteMicroserviceParameterById">
1597 DELETE FROM ep_microservice_parameter WHERE id =:paramId
1602 <sql-query name="deleteWidgetCatalogParameter">
1605 DELETE FROM ep_widget_catalog_parameter WHERE param_id =:paramId
1610 <sql-query name="deleteMicroservice">
1613 DELETE FROM ep_microservice WHERE id =:serviceId
1618 <sql-query name="epUserAppId">
1619 <return alias="userAppId" class="org.openecomp.portalapp.portal.domain.EPUser" />
1623 SELECT * FROM FN_USER where ORG_USER_ID = :orgUserIdValue
1628 <sql-query name="userAppsSortPreferenceQuery">
1629 <return alias="appsSortPreferenceQuery"
1630 class="org.openecomp.portalapp.portal.domain.EPUserAppsSortPreference" />
1634 SELECT * from EP_PERS_USER_APP_SORT e where e.USER_ID = :userId
1639 <sql-query name="userAppsManualSortPrfQuery">
1640 <return alias="AppsManualSortPrfQuery"
1641 class="org.openecomp.portalapp.portal.domain.EPUserAppsManualSortPreference" />
1645 SELECT * from EP_PERS_USER_APP_MAN_SORT where USER_ID =:userId
1650 <sql-query name="userWidgetManualSortPrfQuery">
1651 <return alias="widgetManualSortPrfQuery"
1652 class="org.openecomp.portalapp.portal.domain.EPWidgetsManualSortPreference" />
1654 SELECT * from EP_PERS_USER_WIDGET_PLACEMENT where USER_ID =:userId
1658 <sql-query name="appRoles">
1659 <return alias="rolesForApp"
1660 class="org.openecomp.portalapp.portal.domain.EPUserAppRoles" />
1662 SELECT ROLE_ID, APP_ID from FN_ROLE where APP_ROLE_ID = :appRoleId and app_id = :appId
1667 <sql-query name="userAppRolesRequestList">
1668 <return alias="appRolesRequestList"
1669 class="org.openecomp.portalapp.portal.domain.EPUserAppRolesRequest" />
1671 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'
1676 <sql-query name="userAppRolesRequestDetailList">
1677 <return alias="appRolesRequestDetailList"
1678 class="org.openecomp.portalapp.portal.domain.EPUserAppRolesRequestDetail" />
1681 SELECT id, req_id, requested_role_id, request_type from EP_USER_ROLES_REQUEST_DET where req_id =:reqId
1686 <!-- Gets list of enabled apps including accessible and select statuses -->
1687 <sql-query name="getAdminAppCatalog">
1688 <return alias="adminAppCatalog"
1689 class="org.openecomp.portalapp.portal.ecomp.model.AppCatalogItem" />
1690 <!-- This query requires one parameter: userId (number) -->
1693 distinct -- multiple roles yield multiple rows
1694 a.APP_ID as id, a.APP_NAME as name, a.ML_APP_NAME as mlAppName, a.APP_IMAGE_URL as imageUrl,
1695 a.APP_DESCRIPTION as description, a.APP_NOTES as notes,
1696 a.APP_URL as url, a.APP_ALTERNATE_URL as alternateUrl,
1697 IF(a.app_type = '2', TRUE, FALSE) as restricted,
1698 IF(a.open = 'Y', TRUE, FALSE) as open,
1699 -- ACCESS(-ible) means user has a defined role OR the application is open
1701 -- regular app and user has a role
1702 when a.OPEN = 'N' and r.ROLE_ID is not null then TRUE
1704 when a.OPEN = 'Y' then TRUE
1707 -- SELECT(-ed) indicates user personalization
1709 -- regular app, user has a role, no personalization
1710 when a.OPEN = 'N' and r.ROLE_ID is not null and p.STATUS_CD is null then TRUE
1711 -- regular app, user has role, admin forced a personalization
1712 when a.OPEN = 'N' and r.ROLE_ID is not null and p.STATUS_CD = 'S' then TRUE
1713 -- regular app, user has no role, admin forced a personalization
1714 when a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S' then TRUE
1715 -- open app and has personalization
1716 when a.OPEN = 'Y' and p.STATUS_CD is not null then TRUE
1719 -- PENDING indicates user checked a box
1721 when a.OPEN = 'N' and q.request_status = 'P' then TRUE
1725 -- Portal assigns role 999 to app administrator
1726 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1727 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1728 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'
1729 where a.ENABLED = 'Y'
1730 -- Show accessible apps first, then the rest; sort by name within each set.
1731 order by access desc, app_name asc
1736 <sql-query name="userAppCatalogRoles">
1737 <return alias="epUserAppCatalogRoles"
1738 class="org.openecomp.portalapp.portal.domain.EPUserAppCatalogRoles" />
1740 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
1741 from ep_user_roles_request A left join ep_user_roles_request_det B
1742 on a.req_id = b.req_id
1743 where A.user_id=:userid
1744 and A.app_id IN (select app_id from fn_app where app_name=:appName)
1745 and A.request_status ='P'
1750 <sql-query name="getUserApproles">
1751 <return alias="businessCardUserApplicationRoles"
1752 class="org.openecomp.portalapp.portal.transport.BusinessCardApplicationRole" />
1756 select fr.role_name, fa.app_name
1758 fn_role fr, fn_user_role fur, fn_app fa, fn_user fu
1759 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)
1765 <!--delete the records from ep_notification table when the endtime is more than 3 months-->
1766 <sql-query name="deleteNotificationsFromEpNotificationTable">
1769 delete from ep_notification where curdate() >= DATE_ADD(end_time,INTERVAL 3 MONTH)
1774 <!-- delete the records from ep_role_notification table when the endtime is more than 3 months -->
1775 <sql-query name="deleteNotificationsFromEpRoleNotificationTable">
1778 delete from ep_role_notification where notification_id in (select notification_id from ep_notification where curdate() >= DATE_ADD(end_time,INTERVAL 3 MONTH))
1782 <!-- delete the records from ep_user_notification table when the endtime is more than 3 months-->
1783 <sql-query name="deleteNotificationsFromEpUserNotificationTable">
1786 delete from ep_user_notification where notification_id in (select notification_id from ep_notification where curdate() >= DATE_ADD(end_time,INTERVAL 3 MONTH))
1790 <!-- Gets list of role functions and it requires two parameters appId and roleId-->
1791 <sql-query name="getAppRoleFunctionList">
1792 <return alias="appRoleFunctionList"
1793 class="org.openecomp.portalapp.portal.domain.CentralRoleFunction" />
1796 SELECT DISTINCT f.app_id , f.function_cd, f.function_name from ep_app_role_function rf, ep_app_function f
1797 where rf.role_id =:roleId and rf.app_id =:appId and rf.app_id = f.app_id and rf.function_cd = f.function_cd
1803 <!-- Gets list of all role functions -->
1804 <sql-query name="getAllRoleFunctions">
1805 <return alias="allRoleFunctions"
1806 class="org.openecomp.portalapp.portal.domain.CentralRoleFunction" />
1809 SELECT * from ep_app_function where app_id =:appId
1815 <!-- Get ep_app_function records provided by single parameter -->
1816 <sql-query name="getRoleFunction">
1817 <return alias="RoleFunction"
1818 class="org.openecomp.portalapp.portal.domain.CentralRoleFunction" />
1821 SELECT * from ep_app_function where function_cd like CONCAT('%', :functionCode,'%') and app_id =:appId
1827 <!-- Gets the current user app roles records and requires two parameters -->
1828 <sql-query name="getUserAppCurrentRoles">
1829 <return alias="userAppCurrentRoles"
1830 class="org.openecomp.portalapp.portal.transport.EPUserAppCurrentRoles" />
1833 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
1838 <!-- Gets the current user app roles along with account administrator and requires two parameters -->
1839 <sql-query name="getUserAppExistingRoles">
1840 <return alias="userAppExistingRoles"
1841 class="org.openecomp.portalapp.portal.transport.EcompUserAppRoles" />
1843 select fr.role_name, fu.app_id, fu.user_id, fu.priority, fu.role_id from fn_user_role fu
1844 left outer join fn_role fr on fu.role_id = fr.role_id
1845 where fu.user_id =:userId and fu.app_id =:appId
1850 <!-- Gets the current user app roles records and requires two parameters -->
1851 <sql-query name="getAllFunctions">
1852 <return alias="allFunctions"
1853 class="org.openecomp.portalsdk.core.domain.RoleFunction" />
1855 select * from fn_function
1860 <!-- Gets the all role functions and requires one parameters -->
1861 <sql-query name="uploadAllRoleFunctions">
1862 <return alias="allRoleFunctions"
1863 class="org.openecomp.portalapp.portal.transport.BulkUploadRoleFunction" />
1865 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
1870 <sql-query name="getRoletoUpdateAAF">
1871 <return alias="getRoletoUpdateAAF" class="org.openecomp.portalapp.portal.domain.EPRole" />
1873 SELECT * FROM fn_role where role_name =:appRoleName and app_id =:appId
1878 <sql-query name="getBulkUserRoles">
1879 <return alias="bulkUserRoles" class="org.openecomp.portalapp.portal.transport.BulkUploadUserRoles" />
1881 select fr.role_name, fu.org_user_id, fa.auth_namespace from fn_user_role fur
1882 left outer join fn_role fr on fr.role_id = fur.role_id
1883 left outer join fn_app fa on fa.app_id = fur.app_id
1884 left outer join fn_user fu on fu.user_id = fur.user_id where fa.ueb_key =:uebKey and fu.org_user_id != 'su1234'
1889 <sql-query name="getCentralizedApps">
1890 <return alias="centralizedApps" class="org.openecomp.portalapp.portal.domain.EPApp" />
1892 select * from fn_app where auth_central = 'Y' and open = 'N';
1897 <sql-query name="getUserRoles">
1898 <return alias="getUserRolesList" class="org.openecomp.portalapp.portal.domain.UserRole" />
1902 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
1906 <sql-query name="getRoleFunctionsOfUser">
1908 select distinct ep.function_cd from fn_user_role fu, ep_app_role_function ep, ep_app_function ea
1909 where fu.role_id = ep.role_id
1910 and fu.app_id = ep.app_id
1911 and fu.user_id =:userId and ep.app_id = 1
1912 and ea.function_cd = ep.function_cd
1917 <sql-query name="getUserRolesForLeftMenu">
1918 <return alias="getUserRolesListForLeftMenu" class="org.openecomp.portalapp.portal.domain.UserRole" />
1921 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
1922 INNER JOIN fn_user user ON user.USER_ID = userrole.USER_ID
1923 INNER JOIN fn_app app ON app.app_id= userrole.app_id
1924 INNER JOIN fn_role role ON role.ROLE_ID = userrole.ROLE_ID
1925 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)
1930 <!-- Gets all functions for an application along with global functions and requires single parameter -->
1931 <sql-query name="getMenuFunctions">
1933 select f.function_cd from ep_app_function f
1934 where f.app_id =:appId
1936 select epa.function_cd from fn_role fnr, ep_app_role_function epr, ep_app_function epa where epr.role_id = fnr.role_id
1937 and epa.function_cd = epr.function_cd and fnr.role_name like 'global%'
1942 <sql-query name="getRequestIdsForApp">
1943 <return alias="getRequestIdsForApp" class="org.openecomp.portalapp.portal.domain.EPUserAppRolesRequest" />
1945 select * from ep_user_roles_request where app_id =:app_id
1950 <sql-query name="ApplicationUserRoles">
1951 <return alias="ApplicationUserRoles" class="org.openecomp.portalapp.portal.transport.EcompUserRoles" />
1953 select distinct fu.org_id, fu.manager_id, fu.first_name, fu.middle_name, fu.last_name, fu.phone, fu.email, fu.hrid, fu.org_user_id, fu.org_code, fu.org_manager_userid, fu.job_title, fu.login_id,
1954 fu.active_yn , fr.app_role_id, fr.role_name
1955 from fn_user fu, fn_role fr, fn_user_role fur
1956 where fu.user_id = fur.user_id and fu.active_yn='Y' and fur.role_id = fr.role_id and fr.app_id =:appId and fr.active_yn='Y'
1958 select distinct fu.org_id, fu.manager_id, fu.first_name, fu.middle_name, fu.last_name, fu.phone, fu.email, fu.hrid, fu.org_user_id, fu.org_code, fu.org_manager_userid, fu.job_title, fu.login_id, fu.active_yn , fr.role_id, fr.role_name
1959 from fn_user fu, fn_role fr, fn_user_role fur
1960 where fu.user_id = fur.user_id and fu.active_yn='Y' and fur.app_id=:appId and fr.role_name like 'global%' and fr.active_yn='Y'
1964 </hibernate-mapping>