3 ============LICENSE_START==========================================
5 ===================================================================
6 Copyright (C) 2017-2018 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============================================
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 ONAP Portal application. -->
44 <hibernate-mapping package="org.onap.portalapp.portal.domain">
46 <!-- multilanguage -->
47 <class name="Language" table="fn_language">
48 <id name="languageId" column="language_id">
49 <generator class="native">
50 <param name="sequence"></param>
53 <property name="languageName" column="language_name" />
54 <property name="languageAlias" column="language_alias" />
57 <class name="DisplayText" table="fn_display_text">
58 <id name="id" column="id">
59 <generator class="native">
60 <param name="sequence"></param>
64 <property name="languageId" column="language_id" />
65 <property name="textId" column="text_id" />
66 <property name="label" column="text_label" />
69 <!-- Widget class mapping details -->
70 <class name="Widget" table="FN_WIDGET">
71 <id name="id" column="WIDGET_ID">
72 <generator class="native">
73 <param name="sequence">seq_fn_widget</param>
76 <property name="width" type="integer">
77 <column name="WDG_WIDTH" not-null="true" default="0"></column>
79 <property name="height" type="integer">
80 <column name="WDG_HEIGHT" not-null="true" default="0"></column>
82 <property name="url" type="string">
83 <column name="WDG_URL" not-null="true" default="?"></column>
85 <property name="name" type="string">
86 <column name="WDG_NAME" not-null="true" default="?"></column>
89 <property name="appId" type="long">
90 <column name="APP_ID" not-null="true"></column>
95 <class name="EPEndpoint" table="EP_ENDPOINTS">
96 <id name="id" column="id">
97 <generator class="native">
98 <param name="sequence">seq_ep_endpoint</param>
101 <property name="name" type="string">
102 <column name="url" not-null="true"></column>
107 <class name="EPEndpointAccount" table="EP_ENDPOINTS_BASIC_AUTH_ACCOUNT">
108 <id name="id" column="id">
109 <generator class="native">
110 <param name="sequence">seq_ep_endpoints_basic_auth_account</param>
113 <property name="ep_id" type="long">
114 <column name="ep_id"></column>
116 <property name="account_id" type="long">
117 <column name="account_id"></column>
121 <class name="WidgetCatalogParameter" table="EP_WIDGET_CATALOG_PARAMETER">
122 <id name="id" column="id">
123 <generator class="native">
124 <param name="sequence">seq_ep_widget_catalog_parameter</param>
127 <property name="widgetId" type="long">
128 <column name="widget_id"></column>
130 <property name="userId" type="long">
131 <column name="user_id" not-null="true"></column>
133 <property name="paramId" type="long">
134 <column name="param_id" not-null="true"></column>
137 <property name="user_value" type="string">
138 <column name="user_value" not-null="true"></column>
143 <class name="BasicAuthCredentials" table="ep_basic_auth_account">
144 <id name="id" column="id">
145 <generator class="native">
146 <param name="sequence">seq_ep_basic_auth_account</param>
149 <property name="applicationName" type="string">
150 <column name="ext_app_name" not-null="true" default="?"></column>
152 <property name="username" type="string">
153 <column name="username"></column>
155 <property name="password" type="string">
156 <column name="password"></column>
158 <property name="isActive" type="string">
159 <column name="active_yn" not-null="true" default="Y"></column>
163 <!-- EPUserNotification class mapping details -->
164 <class name="EPUserNotification" table="ep_user_notification">
165 <id name="id" column="id">
166 <generator class="native">
167 <param name="sequence">seq_ep_user_notification</param>
170 <property name="userId" column="User_ID" />
171 <property name="notificationId" column="notification_ID" />
172 <property name="viewed" column="is_viewed" />
173 <property name="updateTime" column="updated_time" />
177 <!-- User class mapping details -->
178 <class name="EPUser" table="FN_USER">
179 <id name="id" column="user_id">
180 <generator class="native">
181 <param name="sequence">seq_fn_user</param>
185 <property name="orgId" column="org_id" />
186 <property name="managerId" column="manager_id" />
187 <property name="firstName" column="first_name" />
188 <property name="middleInitial" column="middle_name" />
189 <property name="lastName" column="last_name" />
190 <property name="phone" column="phone" />
191 <property name="fax" column="fax" />
192 <property name="cellular" column="cellular" />
193 <property name="email" column="email" />
194 <property name="addressId" column="address_id" />
195 <property name="alertMethodCd" column="alert_method_cd" />
197 <property name="address1" column="address_line_1" />
198 <property name="address2" column="address_line_2" />
199 <property name="city" column="city" />
200 <property name="state" column="state_cd" />
201 <property name="zipCode" column="zip_code" />
202 <property name="country" column="country_cd" />
204 <property name="hrid" column="hrid" />
205 <property name="orgUserId" column="org_user_id" />
206 <property name="orgCode" column="org_code" />
207 <property name="loginId" column="login_id" />
208 <property name="loginPwd" column="login_pwd" />
209 <property name="lastLoginDate" column="last_login_date" type="timestamp" />
211 <property name="locationClli" column="location_clli" />
212 <property name="orgManagerUserId" column="org_manager_userid" />
213 <property name="company" column="company" />
214 <property name="department" column="department" />
215 <property name="departmentName" column="department_name" />
216 <property name="businessUnit" column="business_unit" />
217 <property name="businessUnitName" column="business_unit_name" />
218 <property name="jobTitle" column="job_title" />
219 <property name="siloStatus" column="silo_status" />
220 <property name="financialLocCode" column="fin_loc_code" />
221 <property name="costCenter" column="cost_center" />
223 <property name="active" column="active_yn" type="yes_no" />
224 <property name="internal" column="is_internal_yn" type="yes_no" />
226 <property name="created" type="timestamp" column="created_date" />
227 <property name="modified" type="timestamp" column="modified_date" />
229 <property name="createdId" column="created_id" />
230 <property name="modifiedId" column="modified_id" />
231 <property name="timeZoneId" column="timezone" />
232 <property name="systemUser" column="is_system_user" type="yes_no"/>
233 <property name="languageId" column="language_id" />
235 <set name="EPUserApps" table="FN_USER_ROLE" lazy="false" sort="natural"
237 <key column="user_id" />
238 <one-to-many class="org.onap.portalapp.portal.domain.EPUserApp" />
243 <class name="MicroserviceData" table="ep_microservice">
244 <id name="id" column="id">
245 <generator class="native">
246 <param name="sequence">seq_ep_microservice</param>
250 <property name="name" type="string">
251 <column name="name" not-null="true" default="?"></column>
254 <property name="desc" type="string">
255 <column name="description" not-null="true" default="?"></column>
258 <property name="appId" type="long">
259 <column name="appId" not-null="true"></column>
262 <property name="url" type="string">
263 <column name="endpoint_url" not-null="true" default="?"></column>
266 <property name="securityType" type="string">
267 <column name="security_type" not-null="true" default="?"></column>
271 <property name="username" type="string">
272 <column name="username" not-null="true" default="?"></column>
275 <property name="password" type="string">
276 <column name="password" not-null="true" default="?"></column>
279 <property name="active" type="string">
280 <column name="active" not-null="true" default="Y"></column>
284 <class name="MicroserviceParameter" table="ep_microservice_parameter">
285 <id name="id" column="id">
286 <generator class="native">
287 <param name="sequence">seq_ep_microservice_parameter</param>
291 <property name="serviceId" type="long">
292 <column name="service_id" not-null="true"></column>
295 <property name="para_key" type="string">
296 <column name="para_key" not-null="true" default="?"></column>
299 <property name="para_value" type="string">
300 <column name="para_value" not-null="true" default="?"></column>
304 <!-- UserApp class mapping details -->
305 <class name="EPUserApp" table="fn_user_role">
307 <key-property name="userId" type="long">
308 <column name="user_id" precision="11" scale="0" />
310 <key-many-to-one name="app"
311 class="org.onap.portalapp.portal.domain.EPApp" column="app_id" />
312 <key-many-to-one name="role"
313 class="org.onap.portalapp.portal.domain.EPRole" column="role_id" />
315 <property name="priority">
316 <column name="priority" default="1" precision="4" scale="0" />
320 <!-- User App class mapping details -->
321 <class name="EPApp" table="fn_app">
322 <id name="id" column="app_id">
323 <generator class="native">
324 <param name="sequence">seq_fn_app</param>
327 <property name="name" type="string">
328 <column name="app_name" not-null="true" default="?"></column>
330 <property name="imageUrl" column="app_image_url" />
331 <property name="appDescription" column="app_description" />
332 <property name="appNotes" column="app_notes" />
333 <property name="landingPage" column="app_url" />
334 <property name="alternateLandingPage" column="app_alternate_url" />
335 <property name="appRestEndpoint" column="app_rest_endpoint" />
336 <property name="mlAppName" type="string">
337 <column name="ml_app_name" not-null="true" default="?"></column>
339 <property name="mlAppAdminId" type="string">
340 <column name="ml_app_admin_id" not-null="true" default="?"></column>
342 <property name="motsId" column="mots_id" />
343 <property name="appBasicAuthPassword" type="string">
344 <column name="app_password" not-null="true" default="?"></column>
346 <property name="thumbnail" column="thumbnail" />
347 <property name="appBasicAuthUsername" type="string">
348 <column name="app_username" not-null="true" default="?"></column>
350 <property name="open" type="yes_no">
351 <column name="open" not-null="true" default="Y"></column>
353 <property name="enabled" type="yes_no">
354 <column name="enabled" not-null="true" default="N"></column>
356 <property name="uebTopicName" type="string">
357 <column name="ueb_topic_name"></column>
359 <property name="uebKey" type="string">
360 <column name="ueb_key"></column>
362 <property name="uebSecret" type="string">
363 <column name="ueb_secret"></column>
365 <property name="appType" type="integer">
366 <column name="app_type"></column>
368 <property name="rolesInAAF" type="yes_no">
369 <column name="auth_central"></column>
371 <property name="nameSpace" type="string">
372 <column name="auth_namespace"></column>
374 <property name="modeOfIntegration" type="string">
375 <column name="mode_of_integration"></column>
377 <property name="appAck" type="yes_no">
378 <column name="ack_app"></column>
380 <property name="usesCadi" type="yes_no">
381 <column name="uses_cadi"></column>
384 <one-to-one name="contactUs" class="AppContactUs" cascade="save-update"></one-to-one>
388 <!-- EPUserAppsSortPreference class mapping details -->
389 <class name="EPUserAppsSortPreference" table="ep_pers_user_app_sort">
390 <id name="id" column="ID">
391 <generator class="native">
392 <param name="sequence">seq_ep_usrappsrtprf</param>
395 <property name="userId" column="USER_ID" />
396 <property name="sortPref" column="SORT_PREF" />
399 <!-- EPUserAppsManualSortPreference class mapping details -->
400 <class name="EPUserAppsManualSortPreference" table="ep_pers_user_app_man_sort">
401 <id name="id" column="ID">
402 <generator class="native">
403 <param name="sequence">seq_ep_usrmanappsrtprf</param>
406 <property name="userId" column="user_id"
407 unique-key="uk_1_ep_pers_user_app_man_sort" />
408 <property name="appId" column="app_id"
409 unique-key="uk_1_ep_pers_user_app_man_sort" />
410 <property name="appManualSortOrder" column="sort_order" type="int" />
413 <class name="EPWidgetsManualSortPreference" table="ep_pers_user_widget_placement">
414 <id name="id" column="ID">
415 <generator class="native">
416 <param name="sequence">seq_ep_usrwidgetsrtprf</param>
419 <property name="userId" column="user_id" />
420 <property name="widgetId" column="widget_id" />
421 <property name="widgetRow" column="x" />
422 <property name="widgetCol" column="y" />
423 <property name="widgetHeight" column="height" />
424 <property name="widgetWidth" column="width" />
427 <!-- User Role class mapping details -->
428 <class name="EPRole" table="FN_ROLE" >
429 <id name="id" column="role_id">
430 <generator class="native">
431 <param name="sequence">seq_fn_role</param>
435 <property name="name" column="role_name" />
436 <property name="priority" column="priority" />
437 <property name="active" column="active_yn" type="yes_no" />
438 <!-- 2 lines below may be removed! -->
439 <property name="appId" column="APP_ID" />
440 <property name="appRoleId" column="APP_ROLE_ID" />
441 <!-- 2 lines above may be removed -->
443 <set name="roleFunctions" table="FN_ROLE_FUNCTION" lazy="false"
445 <key column="role_id" />
446 <many-to-many column="function_cd"
447 class="org.onap.portalsdk.core.domain.RoleFunction" />
450 <set name="childRoles" table="FN_ROLE_COMPOSITE" lazy="false"
452 <key column="parent_role_id" />
453 <many-to-many column="child_role_id"
454 class="org.onap.portalapp.portal.domain.EPRole" />
457 <set name="parentRoles" table="FN_ROLE_COMPOSITE" lazy="false"
459 <key column="child_role_id" />
460 <many-to-many column="parent_role_id"
461 class="org.onap.portalapp.portal.domain.EPRole" />
466 <!-- User App class mapping details -->
467 <class name="AppContactUs" table="fn_app_contact_us">
468 <id name="id" column="app_id">
469 <generator class="foreign">
470 <param name="property">app</param>
473 <property name="url" column="url" />
474 <property name="description" column="description" />
475 <property name="contactName" column="contact_name" />
476 <property name="contactEmail" column="contact_email" />
477 <property name="activeYN" column="active_yn" />
479 <one-to-one name="app" class="EPApp" constrained="true"></one-to-one>
483 <!-- Personalization of user app selections -->
484 <class name="PersUserAppSelection" table="fn_pers_user_app_sel">
485 <id name="id" column="id">
486 <generator class="native">
487 <param name="property">seq_fn_pers_user_app_sel</param>
490 <property name="userId" column="user_id" />
491 <property name="appId" column="app_id" />
492 <property name="statusCode" column="status_cd" />
495 <!-- EPAppRoleFunction class mapping details -->
496 <class name="EPAppRoleFunction" table="ep_app_role_function">
497 <id name="id" column="id">
498 <generator class="native">
499 <param name="property">seq_epp_app_role_func</param>
502 <property name="roleId" column="role_id"></property>
503 <property name="appId" column="app_id"></property>
504 <property name="code" column="function_cd"></property>
505 <property name="roleAppId" column="role_app_id"></property>
508 <!-- CentralRoleFunction class mapping details -->
509 <class name="CentralV2RoleFunction" table="ep_app_function">
511 <key-property name="appId"
513 <key-property name="code"
514 column="function_cd" />
516 <property name="name" column="function_name" />
519 <class name="PersUserWidgetSelection" table="ep_pers_user_widget_sel">
520 <id name="id" column="id">
521 <generator class="native">
522 <param name="property">seq_ep_pers_user_widget_sel</param>
525 <property name="userId" column="user_id" />
526 <property name="widgetId" column="widget_id" />
527 <property name="statusCode" column="status_cd" />
530 <!-- EPUserAppRolesRequest class mapping details -->
531 <class name="EPUserAppRolesRequest" table="ep_user_roles_request">
532 <id name="id" column="req_id">
533 <generator class="native">
534 <param name="sequence">seq_ep_user_role_request</param>
537 <property name="userId" column="user_id" />
538 <property name="appId" column="app_id" />
539 <property name="createdDate" column="created_date" type="timestamp" />
540 <property name="updatedDate" column="updated_date" type="timestamp" />
541 <property name="requestStatus" column="request_status" />
543 <set name="epRequestIdDetail" table="ep_user_roles_request_det"
544 lazy="false" cascade="save-update, persist" inverse="true" fetch="select">
545 <key column="req_id" />
547 class="org.onap.portalapp.portal.domain.EPUserAppRolesRequestDetail" />
551 <!-- EPUserAppRolesRequestDetail class mapping details -->
552 <class name="EPUserAppRolesRequestDetail" table="ep_user_roles_request_det">
553 <id name="id" column="id">
554 <generator class="native">
555 <param name="sequence">seq_ep_user_role_request_det</param>
558 <property name="reqRoleId" column="requested_role_id" />
559 <property name="reqType" column="request_type" />
560 <many-to-one name="epRequestIdData" fetch="select"
561 class="org.onap.portalapp.portal.domain.EPUserAppRolesRequest">
562 <column name="req_id" not-null="true" />
566 <!-- multilanguage -->
567 <query name="queryLanguage">
573 <query name="displayText">
575 from DisplayText where languageId = :language_id
579 <query name="queryLanguageByLanguageId">
581 from Language where languageId = :language_id
585 <query name="getActiveUsersForApp">
587 select distinct(u) from EPUser as u inner join fetch u.EPUserApps as apps inner join fetch apps.app as app inner join fetch apps.role as role where app.id = :id and u.active = :active and role.id is not null
591 <!-- show the current user plus related users -->
592 <sql-query name="relatedUsers">
593 <return-scalar column="org_user_id" type="java.lang.String" />
595 select t.org_user_id from (
596 select distinct c.org_user_id org_user_id, c.last_login_date from
597 (select c.org_user_id, c.last_login_date from
599 (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,
601 where a.ROLE_ID = b.role_id
602 and a.APP_ID = b.app_id
603 and a.USER_ID = c.user_id
605 select org_user_id , last_login_date from fn_user where org_user_id=:userId
607 order by c.last_login_date desc limit 10
612 <!-- requires values for named parameters :searchQuery and :userId -->
613 <sql-query name="searchPortal">
614 <return alias="searchResult"
615 class="org.onap.portalapp.portal.ecomp.model.SearchResultItem" />
617 select @rn /*'*/:=/*'*/ @rn+1 AS rowId, CATEGORY, NAME, UUID, TARGET from
619 select distinct 'User' CATEGORY, concat(concat(first_name, ' '), last_name) NAME, user_id UUID, org_user_id TARGET
621 upper( :searchQuery ) != ''
622 and (upper(first_name) like upper(CONCAT('%', :searchQuery, '%'))
623 or upper(last_name) like upper(CONCAT('%', :searchQuery, '%'))
624 or upper(org_user_id) like upper(CONCAT('%', :searchQuery, '%'))
626 ((first_name LIKE (SELECT concat('%',trim(SUBSTRING(:searchQuery, 1, INSTR(:searchQuery,' '))),'%'))
628 last_name LIKE (concat('%',(SELECT(SUBSTRING(:searchQuery, INSTR(:searchQuery,' ')+1, LENGTH(:searchQuery)))),'%')))
630 (last_name LIKE (SELECT concat('%',trim(SUBSTRING(:searchQuery, 1, INSTR(:searchQuery,' '))),'%'))
632 first_name LIKE (concat('%',(SELECT(SUBSTRING(:searchQuery, INSTR(:searchQuery,' ')+1, LENGTH(:searchQuery)))),'%')))))
633 and upper(active_yn) = 'Y'
637 select distinct 'Application' CATEGORY, b1.app_name NAME,
638 if (b1.app_type = 2,'false','true') UUID,
639 if (a1.app_id is null, if (b1.app_type = 2, b1.APP_URL, ''), a1.APP_URL) TARGET
643 from fn_user_role a, fn_user b, fn_app c
644 where a.USER_ID = b.user_id
645 and upper(b.org_user_id) = upper( :userId )
646 and a.app_id = c.app_id
647 and upper(c.APP_NAME) like upper(CONCAT('%', :searchQuery, '%'))
648 and upper(c.ENABLED) = 'Y'
649 ) a1 right outer join
650 (select * from fn_app where
651 upper(APP_NAME) like upper(CONCAT('%', :searchQuery, '%'))
652 and upper(ENABLED) = 'Y') b1
653 on a1.APP_ID = b1.app_id
657 select distinct 'Menu' CATEGORY, b1.TEXT NAME,
658 if (b1.app_type = 2,'false','true') UUID,
659 if (a1.menu_id is null , if (b1.app_type = 2, b1.URL, '') , a1.URL ) TARGET
661 (select distinct d.*, e.app_type from fn_user a,
662 fn_user_role b, fn_menu_functional_roles c, fn_menu_functional d, fn_app e
663 where a.USER_ID = b.user_id
664 and b.role_id = c.role_id
665 and c.menu_id = d.menu_id
666 and upper(a.org_user_id) = upper( :userId )
667 and c.APP_ID = e.app_id
668 and upper(d.TEXT) like upper(CONCAT('%', :searchQuery, '%'))
669 and upper(d.active_yn) = 'Y'
670 and upper(e.enabled) = 'Y'
671 ) a1 right outer join
673 select distinct a.*, c.app_type from fn_menu_functional a, fn_menu_functional_roles b, fn_app c
674 where active_yn = 'Y'
675 and upper(TEXT) like upper(CONCAT('%', :searchQuery, '%'))
676 and a.menu_id = b.menu_id
677 and b.app_id = c.app_id
680 on a1.menu_id = b1.menu_id
683 select distinct CATEGORY, NAME, UUID, TARGET
687 select distinct 'Widget' CATEGORY, d.wdg_name NAME,
690 from fn_user a, fn_user_role b, ep_widget_catalog_role c, ep_widget_catalog d
691 where upper(a.org_user_id) = upper( :userId )
692 and a.user_id = b.user_id
693 and b.role_id = c.role_id
694 and c.widget_id = d.widget_id
695 and d.all_user_flag<>'Y'
696 and (upper(d.wdg_name) like upper(CONCAT('%', :searchQuery, '%')) or upper(d.wdg_desc) like upper(CONCAT('%', :searchQuery, '%')))
700 select distinct 'Widget' CATEGORY, d.wdg_name NAME,
703 from ep_widget_catalog d
704 where d.all_user_flag='Y'
705 and (upper(d.wdg_name) like upper(CONCAT('%', :searchQuery, '%')) or upper(d.wdg_desc) like upper(CONCAT('%', :searchQuery, '%')))
709 select distinct 'Widget' CATEGORY, d.wdg_name NAME,
712 from fn_user a, fn_user_role b, ep_widget_catalog d
713 where upper(a.org_user_id) = upper( :userId )
714 and a.user_id = b.user_id
716 and (upper(d.wdg_name) like upper(CONCAT('%', :searchQuery, '%')) or upper(d.wdg_desc) like upper(CONCAT('%', :searchQuery, '%')))
720 (SELECT @rn /*'*/:=/*'*/ 0) t2 order by NAME
726 <!-- requires values for named parameters :searchQuery and :userId -->
727 <sql-query name="getNotifications">
728 <return alias="notificationResult"
729 class="org.onap.portalapp.portal.transport.EpNotificationItem" />
731 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
734 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
737 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
740 select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN,
741 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
742 from ep_notification a, ep_role_notification b
743 where a.notification_id = b.notification_id
744 and (end_time is null || SYSDATE() <= end_time )
745 and (start_time is null || SYSDATE() >= start_time)
746 and a.is_for_all_roles = 'N'
749 select distinct a.user_id, c.role_id, c.app_id, d.APP_NAME
750 from fn_user a, fn_user_role b, fn_role c, fn_app d
751 where COALESCE(c.app_id,1) = d.app_id
752 and a.user_id = b.user_id
753 and a.user_id = :user_id
754 and b.role_id = c.role_id
755 and (d.enabled='Y' or d.app_id=1)
759 a.role_id = b.role_id
762 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
765 select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN,
766 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
767 from ep_notification a, ep_role_notification b
768 where a.notification_id = b.notification_id
769 and (end_time is null || SYSDATE() <= end_time )
770 and (start_time is null || SYSDATE() >= start_time)
771 and a.is_for_all_roles = 'N'
775 a.recv_user_id=:user_id
779 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
780 from ep_notification a
781 where a.notification_id
782 and (end_time is null || SYSDATE() <= end_time )
783 and (start_time is null || SYSDATE() >= start_time)
784 and a.is_for_all_roles = 'Y'
792 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'
794 order by priority desc, created_date desc,start_Time desc
798 (SELECT @rn /*'*/:=/*'*/ 0) t2
803 <sql-query name="getUsersByOrgIdsNotifications">
804 <return alias="getUsersByOrgIdsNotificationsResult" class="org.onap.portalapp.portal.domain.EPUser"/>
806 SELECT * from fn_user where org_user_id in (:OrgIds)
812 <!-- Gets all Admin notification history; accepts no parameters -->
813 <sql-query name="getAdminNotificationHistoryVO">
814 <return alias="adminNotificationHistoryVOResult"
815 class="org.onap.portalapp.portal.transport.EpNotificationItemVO" />
819 n.notification_ID AS notificationId,
820 n.is_for_online_users AS isForOnlineUsers,
821 n.is_for_all_roles AS isForAllRoles,
822 n.active_YN AS activeYn,
823 n.msg_header AS msgHeader,
824 n.msg_description AS msgDescription,
825 n.msg_source AS msgSource,
826 n.start_Time AS startTime,
827 n.end_time AS endTime,
829 n.creator_ID AS creatorId,
830 n.created_date AS createdDate,
831 n.notification_hyperlink AS notificationHyperlink,
832 u.org_user_id AS loginId
836 fn_user u on u.user_id = n.creator_id
838 n.active_YN='Y' and msg_source='EP'
843 n.notification_ID AS notificationId,
844 n.is_for_online_users AS isForOnlineUsers,
845 n.is_for_all_roles AS isForAllRoles,
846 n.active_YN AS activeYn,
847 n.msg_header AS msgHeader,
848 n.msg_description AS msgDescription,
849 n.msg_source AS msgSource,
850 n.start_Time AS startTime,
851 n.end_time AS endTime,
853 n.creator_ID AS creatorId,
854 n.created_date AS createdDate,
855 n.notification_hyperlink AS notificationHyperlink,
856 u.org_user_id AS loginId
860 fn_user u on u.user_id = n.creator_id
862 ep_role_notification r on r.notification_ID=n.notification_ID
864 n.active_YN='Y' and r.recv_user_id = :user_id and msg_source!='EP'
865 )n order by n.startTime desc
869 <sql-query name="getEpNotificationAppRoles">
870 <return alias="notificationAppRoles"
871 class="org.onap.portalapp.portal.domain.EcompAppRole" />
872 <!-- This query requires no parameters. -->
875 select a.app_id, a.app_name, b.role_id, b.role_name from
876 (select * from fn_app where app_id = 1) a,
877 (select * from fn_role where app_id is null and active_yn = 'Y' and role_id <> 1) b
879 select fn_role.app_id,fn_app.app_name, fn_role.role_id ,fn_role.role_name
881 where fn_role.app_id = fn_app.app_id and fn_app.enabled='Y' and fn_role.active_yn='Y' order by app_name
888 <!-- Gets all notification history -->
889 <sql-query name="getNotificationHistoryVO">
890 <return alias="notificationHistoryVOResult"
891 class="org.onap.portalapp.portal.transport.EpNotificationItemVO" />
894 notificationId, isForOnlineUsers, isForAllRoles, msgHeader, msgDescription,msgSource,
895 startTime, endTime, priority, createdDate,notificationHyperlink,creatorId, loginId, activeYn
899 a.notification_ID AS notificationId,
900 is_for_online_users AS isForOnlineUsers,
901 is_for_all_roles AS isForAllRoles,
902 msg_header AS msgHeader,
903 msg_description AS msgDescription,
904 msg_source AS msgSource,
905 start_Time AS startTime,
908 created_date AS createdDate,
909 creator_ID AS creatorId,
910 notification_hyperlink AS notificationHyperlink,
912 active_YN AS activeYn,
913 if (is_viewed is null, 'N', is_viewed)
917 user_id, login_id,notification_id, is_for_online_users, is_for_all_roles,
918 msg_header, msg_description,msg_source, start_Time, end_time, priority, created_date,
919 creator_ID,notification_hyperlink,active_YN
922 select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN,
923 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,
924 b.role_id,CASE WHEN a.creator_ID IS NOT NULL THEN u.org_user_id
927 END AS login_id,b.recv_user_id
928 from ep_notification a, ep_role_notification b,fn_user u
929 where a.notification_id = b.notification_id and (u.user_id=a.creator_ID OR a.creator_ID IS NULL)
930 and a.is_for_all_roles = 'N'
932 (start_time is null and end_time is null and a.created_date >= DATE_ADD(curdate(),INTERVAL-31 DAY))
934 (start_time is not null and end_time is null and start_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
936 (start_time is null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
938 (start_time is not null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
942 select distinct a.user_id, c.role_id, c.app_id, d.APP_NAME
943 from fn_user a, fn_user_role b, fn_role c, fn_app d
944 where COALESCE(c.app_id,1) = d.app_id
945 and a.user_id = b.user_id
946 and a.user_id = :user_id
947 and b.role_id = c.role_id
948 and (d.enabled='Y' or d.app_id=1)
952 a.role_id = b.role_id
956 :user_id, login_id,notification_id, is_for_online_users, is_for_all_roles,
957 msg_header, msg_description,msg_source, start_Time, end_time, priority, created_date,
958 creator_ID,notification_hyperlink,active_YN
961 select a.notification_ID,a.is_for_online_users,a.is_for_all_roles,a.active_YN,
962 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,
963 b.role_id,CASE WHEN a.creator_ID IS NOT NULL THEN u.org_user_id
966 END AS login_id,b.recv_user_id
967 from ep_notification a, ep_role_notification b,fn_user u
968 where a.notification_id = b.notification_id and (u.user_id=a.creator_ID OR a.creator_ID IS NULL)
969 and a.is_for_all_roles = 'N'
971 (start_time is null and end_time is null and a.created_date >= DATE_ADD(curdate(),INTERVAL-31 DAY))
973 (start_time is not null and end_time is null and start_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
975 (start_time is null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
977 (start_time is not null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
982 a.recv_user_id=:user_id
987 :user_id user_id, b.login_id,notification_id, is_for_online_users, is_for_all_roles,
988 msg_header, msg_description,msg_source, start_Time, end_time, priority, a.created_date,
989 creator_ID, a.notification_hyperlink,a.active_YN
990 from ep_notification a JOIN fn_user b on b.user_id=a.creator_ID
991 where a.notification_id
992 and a.is_for_all_roles = 'Y'
994 (start_time is null and end_time is null and a.created_date >= DATE_ADD(curdate(),INTERVAL-31 DAY))
996 (start_time is not null and end_time is null and start_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
998 (start_time is null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
1000 (start_time is not null and end_time is not null and end_time >= DATE_ADD(curdate(),INTERVAL -31 DAY))
1003 ) a left outer join (
1004 select m.notification_ID, m.is_viewed from ep_user_notification m where user_id = :user_id
1006 on a.notification_id = m.notification_ID
1010 order by start_Time desc,end_time desc
1012 (SELECT @rn /*'*/:=/*'*/ 0) t2 where startTime<=SYSDATE()
1017 <!-- shows the received recipient to whom the notification is delivered from external system -->
1018 <sql-query name="messageRecipients">
1019 <return-scalar column="org_user_id" type="java.lang.String" />
1021 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
1026 <!-- Gets role details for a specified notification -->
1027 <sql-query name="getNotificationRoles">
1028 <return alias="notificationRolesResult"
1029 class="org.onap.portalapp.portal.transport.EpRoleNotificationItem" />
1033 ep_role_notification
1035 notification_Id = :notificationId
1039 <!-- Gets all applications, possibly with contact information -->
1040 <sql-query name="getAppsAndContacts">
1041 <return alias="appContactUs"
1042 class="org.onap.portalapp.portal.ecomp.model.AppContactUsItem" />
1043 <!-- This query requires no parameters. -->
1046 a.app_id as appId, a.app_name as appName,
1047 c.contact_name as contactName,
1048 c.contact_email as contactEmail, c.url, c.description,
1049 c.active_yn as activeYN
1054 on a.app_id = c.app_id
1056 a.enabled = 'Y' and a.app_name is not null and a.app_name != ''
1061 <!-- Gets all rows from the app-contact-us table, extended with app information -->
1062 <sql-query name="getAppContactUsItems">
1063 <return alias="appContactUs"
1064 class="org.onap.portalapp.portal.ecomp.model.AppContactUsItem" />
1065 <!-- This query requires no parameters. -->
1068 c.app_id as appId, c.contact_name as contactName,
1069 c.contact_email as contactEmail, c.url, c.description,
1070 c.active_yn as activeYN, a.app_name as appName
1075 on a.app_id = c.app_id
1077 a.enabled = 'Y' and a.app_name is not null and a.app_name != ''
1082 <!-- Gets one row for each app with the category and catenated functions. -->
1083 <sql-query name="getAppCategoryFunctions">
1084 <return alias="appCategoryFunctions"
1085 class="org.onap.portalapp.portal.ecomp.model.AppCategoryFunctionsItem" />
1086 <!-- This query requires no parameters. -->
1088 SELECT @rn /*'*/:=/*'*/ @rn+1 AS rowId,
1089 r.category as category, r.app_id as appId, r.app_name as application,
1090 group_concat(function_text separator ', ') as functions
1093 app_id, function_text, app_name,
1094 IF (a.parent_menu_id IS NULL, fn.parent_menu, a.text) category
1097 j.text function_text, j.menu_id function_id, j.app_id app_id, j.app_name app_name,
1098 k.text parent_menu, k.parent_menu_id parent_menu_id
1101 x.Text, x.PARENT_MENU_ID, x.menu_id, y.app_id, z.app_name
1103 fn_menu_functional x, fn_menu_functional_roles y, fn_app z
1105 x.MENU_ID = y.menu_id AND x.ACTIVE_YN = 'Y' AND y.app_id = z.app_id
1107 fn_menu_functional k
1108 WHERE j.parent_menu_id = k.menu_id
1110 fn_menu_functional a
1111 WHERE fn.parent_menu_id = a.menu_id
1113 (SELECT @rn /*'*/:=/*'*/ 0) t2
1114 group by r.category, r.app_id, r.app_name
1115 order by category, app_name
1120 <!-- Gets one row for each function-application-role combination. -->
1121 <sql-query name="getAppAccessFunctionRole">
1122 <return alias="appAccessFunctionRole"
1123 class="org.onap.portalapp.portal.domain.GetAccessResult" />
1124 <!-- This query requires one parameters. -->
1126 select @rn /*'*/:=/*'*/ @rn+1 AS row_id, t.role_id, t.ECOMP_FUNCTION, t.APP_NAME, t.APP_MOTS_ID, t.ROLE_NAME,
1127 (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,
1128 o.requested_role_id,o.request_type
1130 (select ECOMP_FUNCTION, APP_NAME, APP_MOTS_ID, ROLE_NAME ,a.app_id,a.role_id
1133 select D.APP_NAME, D.MOTS_ID APP_MOTS_ID, C.ROLE_NAME, d.app_id , c.role_id
1136 where COALESCE(c.app_id,1) = d.app_id
1137 and (d.enabled='Y' or d.app_id=1)
1138 and c.active_yn = 'Y'
1141 select A.TEXT ECOMP_FUNCTION, b.role_id, b.app_id from fn_menu_functional A, fn_menu_functional_roles B
1143 A.MENU_ID = B.MENU_ID
1146 on a.role_id = b.role_id) t left outer join
1147 (select ed.requested_role_id, er.app_id ,ed.request_type from ep_user_roles_request er , ep_user_roles_request_det ed
1148 where er.req_id=ed.req_id
1149 and upper(ed.request_type)='P'
1150 and er.user_id =:userId) o
1151 on t.app_id=o.app_id
1152 and t.role_id = o.requested_role_id
1153 JOIN (SELECT @rn /*'*/:=/*'*/ 0) p
1158 <query name="getCommonWidgetItem">
1159 from CommonWidget where category = :cat order by
1163 <sql-query name="getGuestLastLogin">
1164 <return-scalar column="audit_date" type="java.util.Date" />
1166 select audit_date from fn_audit_log where affected_record_id =:userId order by audit_date desc limit 2;
1170 <sql-query name="getActiveUsers">
1171 <return alias="EPUser" class="org.onap.portalapp.portal.domain.EPUser" />
1173 SELECT * FROM FN_USER WHERE active_yn='Y'
1177 <sql-query name="getAppsAdmins">
1178 <return alias="adminUserApp" class="org.onap.portalapp.portal.domain.AdminUserApp" />
1180 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
1184 <sql-query name="getPortalAdmins">
1185 <return alias="portalAdmin" class="org.onap.portalapp.portal.transport.PortalAdmin" />
1187 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
1191 <!-- Gets personalized list of enabled and accessible apps for regular user -->
1192 <sql-query name="getPersUserApps">
1193 <return alias="persUserApps" class="org.onap.portalapp.portal.domain.EPApp" />
1194 <!-- This query requires one parameter: userId (number) -->
1197 distinct -- multiple roles yield multiple rows
1198 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1199 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1200 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1201 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1202 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL
1204 -- Portal assigns role 999 to app administrator
1205 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1206 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1207 where a.ENABLED = 'Y'
1209 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1211 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1218 <!-- Gets personalized list of enabled and accessible apps for Portal (super)
1220 <sql-query name="getPersAdminApps">
1221 <return alias="persAdminApps" class="org.onap.portalapp.portal.domain.EPApp" />
1222 <!-- This query requires one parameter: userId (number) -->
1225 distinct -- multiple roles yield multiple rows
1226 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1227 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1228 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1229 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1230 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1233 -- Portal assigns role 999 to app administrator
1234 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1235 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1236 where a.ENABLED = 'Y'
1238 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1240 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1242 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1249 <!-- Gets personalized list of enabled and accessible Admin apps by name -->
1250 <sql-query name="getPersAdminAppsOrderByName">
1251 <return alias="AdminAppsOrderByName" class="org.onap.portalapp.portal.domain.EPApp" />
1252 <!-- This query requires one parameter: userId (number) -->
1255 distinct -- multiple roles yield multiple rows
1256 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1257 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1258 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1259 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1260 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1261 a.AUTH_NAMESPACE , a.MODE_OF_INTEGRATION, a.ACK_APP , a.USES_CADI
1263 -- Portal assigns role 999 to app administrator
1264 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1265 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1266 where a.ENABLED = 'Y'
1268 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1270 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1272 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1276 select distinct -- multiple roles yield multiple rows
1277 b.APP_ID , b.APP_NAME , b.APP_IMAGE_URL ,b.APP_DESCRIPTION ,
1278 b.APP_NOTES , b.APP_URL , b.APP_ALTERNATE_URL , b.APP_REST_ENDPOINT ,
1279 b.ML_APP_NAME , b.ML_APP_ADMIN_ID , b.MOTS_ID , b.APP_PASSWORD ,
1280 b.THUMBNAIL , b.APP_USERNAME , b.OPEN , b.ENABLED ,
1281 b.UEB_TOPIC_NAME , b.UEB_KEY , b.UEB_SECRET , b.APP_TYPE , b.AUTH_CENTRAL ,
1282 b.AUTH_NAMESPACE , b.MODE_OF_INTEGRATION , b.ACK_APP , b.USES_CADI
1283 from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d , ep_app_function e
1284 where a.user_id =:userId and b.app_id = c.app_id and a.app_id = c.role_app_id and b.enabled = 'Y' and c.role_id = d.role_id and d.active_yn='Y' and a.role_id = d.role_id and e.app_id = c.app_id
1291 <!--Gets personalized list of enabled and accessible User apps by name -->
1292 <sql-query name="getPersUserAppsOrderByName">
1293 <return alias="UserAppsOrderByName" class="org.onap.portalapp.portal.domain.EPApp" />
1294 <!-- This query requires one parameter: userId (number) -->
1297 distinct -- multiple roles yield multiple rows
1298 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1299 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1300 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1301 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1302 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1303 a.AUTH_NAMESPACE , a.MODE_OF_INTEGRATION, a.ACK_APP , a.USES_CADI
1305 -- Portal assigns role 999 to app administrator
1306 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1307 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1308 where a.ENABLED = 'Y'
1310 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1312 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1316 select distinct -- multiple roles yield multiple rows
1317 b.APP_ID , b.APP_NAME , b.APP_IMAGE_URL ,b.APP_DESCRIPTION ,
1318 b.APP_NOTES , b.APP_URL , b.APP_ALTERNATE_URL , b.APP_REST_ENDPOINT ,
1319 b.ML_APP_NAME , b.ML_APP_ADMIN_ID , b.MOTS_ID , b.APP_PASSWORD ,
1320 b.THUMBNAIL , b.APP_USERNAME , b.OPEN , b.ENABLED ,
1321 b.UEB_TOPIC_NAME , b.UEB_KEY , b.UEB_SECRET , b.APP_TYPE , b.AUTH_CENTRAL ,
1322 b.AUTH_NAMESPACE , b.MODE_OF_INTEGRATION, b.ACK_APP , b.USES_CADI
1323 from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d , ep_app_function e
1324 where a.user_id =:userId and b.app_id = c.app_id and a.app_id = c.role_app_id and b.enabled = 'Y' and c.role_id = d.role_id and d.active_yn='Y' and a.role_id = d.role_id and e.app_id = c.app_id
1332 <!--Gets personalized list of enabled and accessible Super Admin apps by
1334 <sql-query name="getAdminAppsOrderByLastUsed">
1335 <return alias="AdminAppsOrderByLastUsed" class="org.onap.portalapp.portal.domain.EPApp" />
1340 distinct -- multiple roles yield multiple rows
1341 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1342 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1343 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1344 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1345 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1346 a.AUTH_NAMESPACE , a.MODE_OF_INTEGRATION, a.ACK_APP , a.USES_CADI
1348 -- Portal assigns role 999 to app administrator
1349 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1350 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1351 where a.ENABLED = 'Y'
1353 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1355 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1357 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1361 select distinct -- multiple roles yield multiple rows
1362 b.APP_ID , b.APP_NAME , b.APP_IMAGE_URL ,b.APP_DESCRIPTION ,
1363 b.APP_NOTES , b.APP_URL , b.APP_ALTERNATE_URL , b.APP_REST_ENDPOINT ,
1364 b.ML_APP_NAME , b.ML_APP_ADMIN_ID , b.MOTS_ID , b.APP_PASSWORD ,
1365 b.THUMBNAIL , b.APP_USERNAME , b.OPEN , b.ENABLED ,
1366 b.UEB_TOPIC_NAME , b.UEB_KEY , b.UEB_SECRET , b.APP_TYPE , b.AUTH_CENTRAL ,
1367 b.AUTH_NAMESPACE , b.MODE_OF_INTEGRATION, b.ACK_APP , b.USES_CADI
1368 from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d , ep_app_function e
1369 where a.user_id =:userId and b.app_id = c.app_id and a.app_id = c.role_app_id and b.enabled = 'Y' and c.role_id = d.role_id and d.active_yn='Y' and a.role_id = d.role_id and e.app_id = c.app_id
1372 (select FN_AUDIT_LOG.AFFECTED_RECORD_ID, max(FN_AUDIT_LOG.AUDIT_DATE) AS AUDIT_DATE
1373 from FN_APP LEFT JOIN FN_AUDIT_LOG
1374 on FN_APP.APP_ID = FN_AUDIT_LOG.AFFECTED_RECORD_ID
1375 where FN_APP.ENABLED = 'Y' and FN_AUDIT_LOG.user_id=:userId
1376 and (audit_date) BETWEEN (CURDATE() - INTERVAL 90 DAY) and CURDATE()
1377 group by FN_AUDIT_LOG.AFFECTED_RECORD_ID
1379 on A.app_id = B.Affected_record_id
1380 order by AUDIT_DATE DESC;
1384 <!--Gets personalized list of enabled and accessible User apps by LastUsed -->
1385 <sql-query name="getUserAppsOrderByLastUsed">
1386 <return alias="UserAppsOrderByLastUsed" class="org.onap.portalapp.portal.domain.EPApp" />
1388 select * from (select
1389 distinct -- multiple roles yield multiple rows
1390 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1391 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1392 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1393 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1394 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1395 a.AUTH_NAMESPACE , a.MODE_OF_INTEGRATION, a.ACK_APP , a.USES_CADI
1397 -- Portal assigns role 999 to app administrator
1398 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1399 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1400 where a.ENABLED = 'Y'
1402 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1404 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1408 select distinct -- multiple roles yield multiple rows
1409 b.APP_ID , b.APP_NAME , b.APP_IMAGE_URL ,b.APP_DESCRIPTION ,
1410 b.APP_NOTES , b.APP_URL , b.APP_ALTERNATE_URL , b.APP_REST_ENDPOINT ,
1411 b.ML_APP_NAME , b.ML_APP_ADMIN_ID , b.MOTS_ID , b.APP_PASSWORD ,
1412 b.THUMBNAIL , b.APP_USERNAME , b.OPEN , b.ENABLED ,
1413 b.UEB_TOPIC_NAME , b.UEB_KEY , b.UEB_SECRET , b.APP_TYPE , b.AUTH_CENTRAL ,
1414 b.AUTH_NAMESPACE , b.MODE_OF_INTEGRATION, b.ACK_APP , b.USES_CADI
1415 from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d , ep_app_function e
1416 where a.user_id =:userId and b.app_id = c.app_id and a.app_id = c.role_app_id and b.enabled = 'Y' and c.role_id = d.role_id and d.active_yn='Y' and a.role_id = d.role_id and e.app_id = c.app_id
1420 (select FN_AUDIT_LOG.AFFECTED_RECORD_ID, max(FN_AUDIT_LOG.AUDIT_DATE) AS AUDIT_DATE
1421 from FN_APP LEFT JOIN FN_AUDIT_LOG
1422 on FN_APP.APP_ID = FN_AUDIT_LOG.AFFECTED_RECORD_ID
1423 where FN_APP.ENABLED = 'Y' and FN_AUDIT_LOG.user_id=:userId
1424 and (audit_date) BETWEEN (CURDATE() - INTERVAL 90 DAY) and CURDATE()
1425 group by FN_AUDIT_LOG.AFFECTED_RECORD_ID
1427 on A.app_id = B.Affected_record_id
1428 order by AUDIT_DATE DESC;
1435 <!--Gets personalized list of enabled and accessible Super Admin apps by
1437 <sql-query name="getAdminAppsOrderByMostUsed">
1438 <return alias="AdminAppsOrderByMostUsed" class="org.onap.portalapp.portal.domain.EPApp" />
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 ,
1449 a.AUTH_NAMESPACE , a.MODE_OF_INTEGRATION, a.ACK_APP , a.USES_CADI
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.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1460 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1464 select distinct -- multiple roles yield multiple rows
1465 b.APP_ID , b.APP_NAME , b.APP_IMAGE_URL ,b.APP_DESCRIPTION ,
1466 b.APP_NOTES , b.APP_URL , b.APP_ALTERNATE_URL , b.APP_REST_ENDPOINT ,
1467 b.ML_APP_NAME , b.ML_APP_ADMIN_ID , b.MOTS_ID , b.APP_PASSWORD ,
1468 b.THUMBNAIL , b.APP_USERNAME , b.OPEN , b.ENABLED ,
1469 b.UEB_TOPIC_NAME , b.UEB_KEY , b.UEB_SECRET , b.APP_TYPE , b.AUTH_CENTRAL ,
1470 b.AUTH_NAMESPACE , b.MODE_OF_INTEGRATION, b.ACK_APP , b.USES_CADI
1471 from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d , ep_app_function e
1472 where a.user_id =:userId and b.app_id = c.app_id and a.app_id = c.role_app_id and b.enabled = 'Y' and c.role_id = d.role_id and d.active_yn='Y' and a.role_id = d.role_id and e.app_id = c.app_id
1475 (select FN_AUDIT_LOG.AFFECTED_RECORD_ID, COUNT(*) AS total_visits
1476 from FN_APP LEFT JOIN FN_AUDIT_LOG
1477 on FN_APP.APP_ID = FN_AUDIT_LOG.AFFECTED_RECORD_ID
1478 where FN_APP.ENABLED = 'Y' and FN_AUDIT_LOG.user_id=:userId
1479 and DATE(audit_date) BETWEEN (CURDATE() - INTERVAL 90 DAY) and CURDATE()
1480 group by FN_AUDIT_LOG.AFFECTED_RECORD_ID
1482 on A.app_id = B.Affected_record_id
1483 order by total_visits DESC;
1488 <!--Gets personalized list of enabled and accessible NON Super Admin User
1489 apps by Most Used -->
1490 <sql-query name="getUserAppsOrderByMostUsed">
1491 <return alias="UserAppsOrderByMostUsed" class="org.onap.portalapp.portal.domain.EPApp" />
1495 distinct -- multiple roles yield multiple rows
1496 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1497 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1498 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1499 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1500 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1501 a.AUTH_NAMESPACE , a.MODE_OF_INTEGRATION, a.ACK_APP , a.USES_CADI
1503 -- Portal assigns role 999 to app administrator
1504 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1505 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1506 where a.ENABLED = 'Y'
1508 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1510 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1514 select distinct -- multiple roles yield multiple rows
1515 b.APP_ID , b.APP_NAME , b.APP_IMAGE_URL ,b.APP_DESCRIPTION ,
1516 b.APP_NOTES , b.APP_URL , b.APP_ALTERNATE_URL , b.APP_REST_ENDPOINT ,
1517 b.ML_APP_NAME , b.ML_APP_ADMIN_ID , b.MOTS_ID , b.APP_PASSWORD ,
1518 b.THUMBNAIL , b.APP_USERNAME , b.OPEN , b.ENABLED ,
1519 b.UEB_TOPIC_NAME , b.UEB_KEY , b.UEB_SECRET , b.APP_TYPE , b.AUTH_CENTRAL ,
1520 b.AUTH_NAMESPACE , b.MODE_OF_INTEGRATION, b.ACK_APP , b.USES_CADI
1521 from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d, ep_app_function e
1522 where a.user_id =:userId and b.app_id = c.app_id and a.app_id = c.role_app_id and b.enabled = 'Y' and c.role_id = d.role_id and d.active_yn='Y' and a.role_id = d.role_id and e.app_id = c.app_id
1525 (select FN_AUDIT_LOG.AFFECTED_RECORD_ID, COUNT(*) AS total_visits
1526 from FN_APP LEFT JOIN FN_AUDIT_LOG
1527 on FN_APP.APP_ID = FN_AUDIT_LOG.AFFECTED_RECORD_ID
1528 where FN_APP.ENABLED = 'Y' and FN_AUDIT_LOG.user_id=:userId
1529 and DATE(audit_date) BETWEEN (CURDATE() - INTERVAL 90 DAY) and CURDATE()
1530 group by FN_AUDIT_LOG.AFFECTED_RECORD_ID
1532 on A.app_id = B.Affected_record_id
1533 order by total_visits DESC;
1537 <!--Gets personalized list of enabled and accessible Super Admin apps by
1539 <sql-query name="getAdminAppsOrderByManual">
1540 <return alias="AdminAppsOrderByManual" class="org.onap.portalapp.portal.domain.EPApp" />
1544 distinct -- multiple roles yield multiple rows
1545 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1546 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1547 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1548 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1549 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1550 a.AUTH_NAMESPACE , a.MODE_OF_INTEGRATION, a.ACK_APP , a.USES_CADI
1552 -- Portal assigns role 999 to app administrator
1553 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1554 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1555 where a.ENABLED = 'Y'
1557 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1559 (a.OPEN = 'N' and r.ROLE_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1561 (a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S')
1565 select distinct -- multiple roles yield multiple rows
1566 b.APP_ID , b.APP_NAME , b.APP_IMAGE_URL ,b.APP_DESCRIPTION ,
1567 b.APP_NOTES , b.APP_URL , b.APP_ALTERNATE_URL , b.APP_REST_ENDPOINT ,
1568 b.ML_APP_NAME , b.ML_APP_ADMIN_ID , b.MOTS_ID , b.APP_PASSWORD ,
1569 b.THUMBNAIL , b.APP_USERNAME , b.OPEN , b.ENABLED ,
1570 b.UEB_TOPIC_NAME , b.UEB_KEY , b.UEB_SECRET , b.APP_TYPE , b.AUTH_CENTRAL ,
1571 b.AUTH_NAMESPACE , b.MODE_OF_INTEGRATION, b.ACK_APP , b.USES_CADI
1572 from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d, ep_app_function e
1573 where a.user_id =:userId and b.app_id = c.app_id and a.app_id = c.role_app_id and b.enabled = 'Y' and c.role_id = d.role_id and d.active_yn='Y' and a.role_id = d.role_id and e.app_id = c.app_id
1576 (select m.app_id, m.sort_order
1577 from ep_pers_user_app_man_sort m
1578 where USER_ID = :userId
1580 on A.APP_ID = B.app_id
1581 order by sort_order ASC
1587 <!--Gets personalized list of enabled and accessible NON Super admin User
1589 <sql-query name="getUserAppsOrderByManual">
1590 <return alias="UserAppsOrderByManual" class="org.onap.portalapp.portal.domain.EPApp" />
1592 select * from (select
1593 distinct -- multiple roles yield multiple rows
1594 a.APP_ID , a.APP_NAME , a.APP_IMAGE_URL , a.APP_DESCRIPTION ,
1595 a.APP_NOTES , a.APP_URL , a.APP_ALTERNATE_URL , a.APP_REST_ENDPOINT ,
1596 a.ML_APP_NAME , a.ML_APP_ADMIN_ID , a.MOTS_ID , a.APP_PASSWORD ,
1597 a.THUMBNAIL , a.APP_USERNAME , a.OPEN , a.ENABLED ,
1598 a.UEB_TOPIC_NAME , a.UEB_KEY , a.UEB_SECRET , a.APP_TYPE , a.AUTH_CENTRAL ,
1599 a.AUTH_NAMESPACE , a.MODE_OF_INTEGRATION, a.ACK_APP , a.USES_CADI
1601 -- Portal assigns role 999 to app administrator
1602 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1603 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1604 where a.ENABLED = 'Y'
1606 (a.OPEN = 'Y' and p.STATUS_CD = 'S')
1608 (a.OPEN = 'N' and r.USER_ID is not null and (p.STATUS_CD is null or p.STATUS_CD != 'H'))
1612 select distinct -- multiple roles yield multiple rows
1613 b.APP_ID , b.APP_NAME , b.APP_IMAGE_URL ,b.APP_DESCRIPTION ,
1614 b.APP_NOTES , b.APP_URL , b.APP_ALTERNATE_URL , b.APP_REST_ENDPOINT ,
1615 b.ML_APP_NAME , b.ML_APP_ADMIN_ID , b.MOTS_ID , b.APP_PASSWORD ,
1616 b.THUMBNAIL , b.APP_USERNAME , b.OPEN , b.ENABLED ,
1617 b.UEB_TOPIC_NAME , b.UEB_KEY , b.UEB_SECRET , b.APP_TYPE , b.AUTH_CENTRAL ,
1618 b.AUTH_NAMESPACE , b.MODE_OF_INTEGRATION, b.ACK_APP , b.USES_CADI
1619 from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d, ep_app_function e
1620 where a.user_id =:userId and b.app_id = c.app_id and a.app_id = c.role_app_id and b.enabled = 'Y' and c.role_id = d.role_id and d.active_yn='Y' and a.role_id = d.role_id and e.app_id = c.app_id
1623 (select m.app_id, m.sort_order
1624 from ep_pers_user_app_man_sort m
1625 where USER_ID = :userId
1627 on A.APP_ID = B.app_id
1628 order by sort_order ASC
1633 <!-- Gets regular user's list of enabled apps including accessible and select
1635 <sql-query name="getUserAppCatalog">
1636 <return alias="userAppCatalog"
1637 class="org.onap.portalapp.portal.ecomp.model.AppCatalogItem" />
1638 <!-- This query requires one parameter: userId (number) -->
1641 distinct -- multiple roles yield multiple rows
1642 a.APP_ID as id, a.APP_NAME as name, a.ML_APP_NAME as mlAppName, a.APP_IMAGE_URL as imageUrl,
1643 a.APP_DESCRIPTION as description, a.APP_NOTES as notes,
1644 a.APP_URL as url, a.APP_ALTERNATE_URL as alternateUrl,
1645 IF(a.app_type = '2', TRUE, FALSE) as restricted,
1646 IF(a.open = 'Y', TRUE, FALSE) as open,
1647 -- ACCESS(-ible) means user has a defined role OR the application is open
1649 -- regular app and user has a role
1650 when a.OPEN = 'N' and r.ROLE_ID is not null then TRUE
1652 when a.OPEN = 'Y' then TRUE
1655 -- SELECT(-ed) indicates user personalization
1657 -- regular app, user has a role, no personalization
1658 when a.OPEN = 'N' and r.ROLE_ID is not null and p.STATUS_CD is null then TRUE
1659 -- open app and has personalization
1660 when a.OPEN = 'Y' and p.STATUS_CD is not null then TRUE
1663 -- PENDING indicates user checked a box
1665 when a.OPEN = 'N' and q.request_status = 'P' then TRUE
1669 -- Portal assigns role 999 to app administrator
1670 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1671 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1672 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'
1673 where a.ENABLED = 'Y' and a.app_type != 3
1674 -- Show accessible apps first, then the rest; sort by name within each set.
1675 order by access desc, app_name asc
1680 <sql-query name="getMyloginAppDetails">
1681 <return alias="myloginAppDetails" class="org.onap.portalapp.portal.domain.EPApp" />
1683 SELECT * FROM fn_app where ml_app_name =:appName
1688 <sql-query name="getAppDetailsByAppName">
1689 <return alias="appDetailsByAppName" class="org.onap.portalapp.portal.domain.EPApp" />
1691 SELECT * FROM fn_app where app_name =:appName
1696 <sql-query name="getMyAppDetailsByUebKey">
1697 <return alias="myAppDetailsByUebKey" class="org.onap.portalapp.portal.domain.EPApp" />
1699 SELECT * FROM fn_app where ueb_key =:appKey
1705 <sql-query name="deleteAccountEndpoint">
1707 DELETE FROM ep_endpoints_basic_auth_account WHERE account_id =:accountId
1712 <sql-query name="deleteAccountEndpointRecord">
1714 DELETE FROM ep_endpoints_basic_auth_account WHERE account_id =:accountId AND ep_id =:epId
1719 <sql-query name="deleteEPEndpoint">
1721 DELETE FROM ep_endpoints WHERE id =:epId
1726 <sql-query name="deleteBasicAuthAccount">
1728 DELETE FROM ep_basic_auth_account WHERE id =:accountId
1733 <sql-query name="getAppRoles">
1734 <return alias="appRoles" class="org.onap.portalapp.portal.domain.EPRole" />
1736 SELECT * FROM fn_role where role_name =:roleName and app_id =:appId
1741 <sql-query name="getPartnerAppRolesList">
1742 <return alias="partnerAppRolesList" class="org.onap.portalapp.portal.domain.EPRole" />
1744 SELECT * FROM fn_role where app_id =:appId
1749 <sql-query name="getPortalAppRolesList">
1750 <return alias="portalAppRolesList" class="org.onap.portalapp.portal.domain.EPRole" />
1752 SELECT * FROM fn_role where app_id is null
1757 <sql-query name="getPortalAppRoleByRoleId">
1758 <return alias="portalAppByRoleId" class="org.onap.portalapp.portal.domain.EPRole" />
1760 SELECT * FROM fn_role where role_id =:roleId and app_id is null
1765 <sql-query name="getPartnerAppRoleByRoleId">
1766 <return alias="portalAppByRoleId" class="org.onap.portalapp.portal.domain.EPRole" />
1768 SELECT * FROM fn_role where app_role_id =:appRoleId and app_id =:appId
1773 <sql-query name="getPartnerAppRoleById">
1774 <return alias="portalAppByRoleId" class="org.onap.portalapp.portal.domain.EPRole" />
1776 SELECT * FROM fn_role where role_id =:appRoleId and app_id =:appId
1781 <sql-query name="getPortalAppRoles">
1782 <return alias="appRoles" class="org.onap.portalapp.portal.domain.EPRole" />
1784 SELECT * FROM fn_role where role_name =:appRoleName and app_id is null
1789 <sql-query name="deleteMicroserviceParameter">
1791 DELETE FROM ep_microservice_parameter WHERE service_id =:serviceId
1796 <sql-query name="deleteMicroserviceParameterById">
1798 DELETE FROM ep_microservice_parameter WHERE id =:paramId
1803 <sql-query name="deleteWidgetCatalogParameter">
1805 DELETE FROM ep_widget_catalog_parameter WHERE param_id =:paramId
1810 <sql-query name="deleteMicroservice">
1812 DELETE FROM ep_microservice WHERE id =:serviceId
1817 <sql-query name="epUserAppId">
1818 <return alias="userAppId" class="org.onap.portalapp.portal.domain.EPUser" />
1820 SELECT * FROM FN_USER where ORG_USER_ID = :orgUserIdValue
1825 <sql-query name="userAppsSortPreferenceQuery">
1826 <return alias="appsSortPreferenceQuery"
1827 class="org.onap.portalapp.portal.domain.EPUserAppsSortPreference" />
1829 SELECT * from EP_PERS_USER_APP_SORT e where e.USER_ID = :userId
1834 <sql-query name="userAppsManualSortPrfQuery">
1835 <return alias="AppsManualSortPrfQuery"
1836 class="org.onap.portalapp.portal.domain.EPUserAppsManualSortPreference" />
1838 SELECT * from EP_PERS_USER_APP_MAN_SORT where USER_ID =:userId
1843 <sql-query name="userWidgetManualSortPrfQuery">
1844 <return alias="widgetManualSortPrfQuery"
1845 class="org.onap.portalapp.portal.domain.EPWidgetsManualSortPreference" />
1847 SELECT * from EP_PERS_USER_WIDGET_PLACEMENT where USER_ID =:userId
1851 <sql-query name="appRoles">
1852 <return alias="rolesForApp"
1853 class="org.onap.portalapp.portal.domain.EPUserAppRoles" />
1855 SELECT ROLE_ID, APP_ID from FN_ROLE where APP_ROLE_ID = :appRoleId and app_id = :appId
1860 <sql-query name="userAppRolesRequestList">
1861 <return alias="appRolesRequestList"
1862 class="org.onap.portalapp.portal.domain.EPUserAppRolesRequest" />
1864 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'
1869 <sql-query name="userAppRolesRequestDetailList">
1870 <return alias="appRolesRequestDetailList"
1871 class="org.onap.portalapp.portal.domain.EPUserAppRolesRequestDetail" />
1874 SELECT id, req_id, requested_role_id, request_type from EP_USER_ROLES_REQUEST_DET where req_id =:reqId
1879 <!-- Gets list of enabled apps including accessible and select statuses -->
1880 <sql-query name="getAdminAppCatalog">
1881 <return alias="adminAppCatalog"
1882 class="org.onap.portalapp.portal.ecomp.model.AppCatalogItem" />
1883 <!-- This query requires one parameter: userId (number) -->
1886 distinct -- multiple roles yield multiple rows
1887 a.APP_ID as id, a.APP_NAME as name, a.ML_APP_NAME as mlAppName, a.APP_IMAGE_URL as imageUrl,
1888 a.APP_DESCRIPTION as description, a.APP_NOTES as notes,
1889 a.APP_URL as url, a.APP_ALTERNATE_URL as alternateUrl,
1890 IF(a.app_type = '2', TRUE, FALSE) as restricted,
1891 IF(a.open = 'Y', TRUE, FALSE) as open,
1892 -- ACCESS(-ible) means user has a defined role OR the application is open
1894 -- regular app and user has a role
1895 when a.OPEN = 'N' and r.ROLE_ID is not null then TRUE
1897 when a.OPEN = 'Y' then TRUE
1900 -- SELECT(-ed) indicates user personalization
1902 -- regular app, user has a role, no personalization
1903 when a.OPEN = 'N' and r.ROLE_ID is not null and p.STATUS_CD is null then TRUE
1904 -- regular app, user has role, admin forced a personalization
1905 when a.OPEN = 'N' and r.ROLE_ID is not null and p.STATUS_CD = 'S' then TRUE
1906 -- regular app, user has no role, admin forced a personalization
1907 when a.OPEN = 'N' and r.ROLE_ID is null and p.STATUS_CD = 'S' then TRUE
1908 -- open app and has personalization
1909 when a.OPEN = 'Y' and p.STATUS_CD is not null then TRUE
1912 -- PENDING indicates user checked a box
1914 when a.OPEN = 'N' and q.request_status = 'P' then TRUE
1918 -- Portal assigns role 999 to app administrator
1919 left outer join FN_USER_ROLE r ON a.APP_ID = r.APP_ID and r.USER_ID = :userId and r.ROLE_ID != 999
1920 left outer join FN_PERS_USER_APP_SEL p ON a.APP_ID = p.APP_ID and p.USER_ID = :userId
1921 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'
1922 where a.ENABLED = 'Y' and a.app_type != 3
1923 -- Show accessible apps first, then the rest; sort by name within each set.
1924 order by access desc, app_name asc
1929 <sql-query name="userAppCatalogRoles">
1930 <return alias="epUserAppCatalogRoles"
1931 class="org.onap.portalapp.portal.domain.EPUserAppCatalogRoles" />
1933 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
1934 from ep_user_roles_request A left join ep_user_roles_request_det B
1935 on a.req_id = b.req_id
1936 where A.user_id=:userid
1937 and A.app_id IN (select app_id from fn_app where app_name=:appName)
1938 and A.request_status ='P'
1943 <sql-query name="getUserApproles">
1944 <return alias="businessCardUserApplicationRoles"
1945 class="org.onap.portalapp.portal.transport.BusinessCardApplicationRole" />
1948 select fr.role_name, fa.app_name
1950 fn_role fr, fn_user_role fur, fn_app fa, fn_user fu
1951 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)
1957 <!--delete the records from ep_notification table when the endtime is more than 3 months-->
1958 <sql-query name="deleteNotificationsFromEpNotificationTable">
1960 delete from ep_notification where curdate() >= DATE_ADD(end_time,INTERVAL 3 MONTH)
1965 <!-- delete the records from ep_role_notification table when the endtime is more than 3 months -->
1966 <sql-query name="deleteNotificationsFromEpRoleNotificationTable">
1968 delete from ep_role_notification where notification_id in (select notification_id from ep_notification where curdate() >= DATE_ADD(end_time,INTERVAL 3 MONTH))
1972 <!-- delete the records from ep_user_notification table when the endtime is more than 3 months-->
1973 <sql-query name="deleteNotificationsFromEpUserNotificationTable">
1975 delete from ep_user_notification where notification_id in (select notification_id from ep_notification where curdate() >= DATE_ADD(end_time,INTERVAL 3 MONTH))
1979 <!-- Gets list of role functions and it requires two parameters appId and roleId-->
1980 <sql-query name="getAppRoleFunctionList">
1981 <return alias="appRoleFunctionList"
1982 class="org.onap.portalapp.portal.domain.CentralV2RoleFunction" />
1985 SELECT DISTINCT f.app_id , f.function_cd, f.function_name from ep_app_role_function rf, ep_app_function f
1986 where rf.role_id =:roleId and rf.app_id =:appId and rf.app_id = f.app_id and rf.function_cd = f.function_cd
1992 <!-- Gets list of all role functions -->
1993 <sql-query name="getAllRoleFunctions">
1994 <return alias="allRoleFunctions"
1995 class="org.onap.portalapp.portal.domain.CentralV2RoleFunction" />
1998 SELECT * from ep_app_function where app_id =:appId
2004 <!-- Get ep_app_function records provided by single parameter -->
2005 <sql-query name="getRoleFunction">
2006 <return alias="RoleFunction"
2007 class="org.onap.portalapp.portal.domain.CentralV2RoleFunction" />
2010 SELECT * from ep_app_function where function_cd like CONCAT('%', :functionCode,'%') and app_id =:appId
2016 <!-- Gets the current user app roles records and requires two parameters -->
2017 <sql-query name="getUserAppCurrentRoles">
2018 <return alias="userAppCurrentRoles"
2019 class="org.onap.portalapp.portal.transport.EPUserAppCurrentRoles" />
2022 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 and fu.active_yn='Y'
2027 <!-- Gets the current user app roles along with account administrator and requires two parameters -->
2028 <sql-query name="getUserAppExistingRoles">
2029 <return alias="userAppExistingRoles"
2030 class="org.onap.portalapp.portal.transport.EcompUserAppRoles" />
2032 select fr.role_name, fu.app_id, fu.user_id, fu.priority, fu.role_id from fn_user_role fu
2033 left outer join fn_role fr on fu.role_id = fr.role_id
2034 where fu.user_id =:userId and fu.app_id =:appId
2039 <!-- Gets all functions of portal app -->
2040 <sql-query name="getAllFunctions">
2041 <return alias="allFunctions"
2042 class="org.onap.portalsdk.core.domain.RoleFunction" />
2044 select * from fn_function
2049 <!-- Gets the current user app roles records and requires two parameters -->
2050 <sql-query name="getPartnerAppFunctions">
2051 <return alias="partnerAppFunctions"
2052 class="org.onap.portalapp.portal.domain.CentralV2RoleFunction" />
2054 select * from ep_app_function where app_id =:appId
2059 <!-- Gets the all role functions and requires one parameters -->
2060 <sql-query name="uploadAllRoleFunctions">
2061 <return alias="allRoleFunctions"
2062 class="org.onap.portalapp.portal.transport.BulkUploadRoleFunction" />
2064 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
2069 <!-- Gets the all application role functions from ep_app_function table and requires one parameters -->
2070 <sql-query name="uploadPartnerRoleFunctions">
2071 <return alias="partnerRoleFunctions"
2072 class="org.onap.portalapp.portal.transport.BulkUploadRoleFunction" />
2074 select distinct eprf.function_cd, epfn.function_name from ep_app_role_function eprf left outer join ep_app_function epfn on eprf.function_cd = epfn.function_cd where eprf.role_id =:roleId
2079 <sql-query name="getRoletoUpdateInExternalAuthSystem">
2080 <return alias="roletoUpdateInExternalAuthSystem" class="org.onap.portalapp.portal.domain.EPRole" />
2082 SELECT * FROM fn_role where role_name =:appRoleName and app_id =:appId
2087 <!-- Gets all active records from fn_role that is associated with this app-->
2088 <sql-query name="getActiveRolesOfApplication">
2089 <return alias="activeRolesOfApplication" class="org.onap.portalapp.portal.domain.EPRole" />
2091 select * from fn_role where active_yn = 'Y' and app_id=:appId
2096 <sql-query name="getBulkUserRoles">
2097 <return alias="bulkUserRoles" class="org.onap.portalapp.portal.transport.BulkUploadUserRoles" />
2099 select fr.role_name, fu.org_user_id, fa.auth_namespace from fn_user_role fur
2100 left outer join fn_role fr on fr.role_id = fur.role_id
2101 left outer join fn_app fa on fa.app_id = fur.app_id
2102 left outer join fn_user fu on fu.user_id = fur.user_id where fa.ueb_key =:uebKey
2107 <sql-query name="getBulkUsersForSingleRole">
2108 <return alias="bulkUsersForSingleRole" class="org.onap.portalapp.portal.transport.BulkUploadUserRoles" />
2110 select fr.role_name, fu.org_user_id, fa.auth_namespace from fn_user_role fur
2111 left outer join fn_role fr on fr.role_id = fur.role_id
2112 left outer join fn_app fa on fa.app_id = fur.app_id
2113 left outer join fn_user fu on fu.user_id = fur.user_id where fa.ueb_key =:uebKey and fr.role_id =:roleId
2118 <sql-query name="getCentralizedApps">
2119 <return alias="centralizedApps" class="org.onap.portalapp.portal.domain.EPApp" />
2121 select * from fn_app where auth_central = 'Y' and open = 'N' and auth_namespace is not null
2126 <sql-query name="getUserRoles">
2127 <return alias="getUserRolesList" class="org.onap.portalapp.portal.domain.UserRole" />
2130 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
2134 <sql-query name="getRoleFunctionsOfUser">
2136 select distinct ep.function_cd from fn_user_role fu, ep_app_role_function ep, ep_app_function ea
2137 where fu.role_id = ep.role_id
2138 and fu.app_id = ep.app_id
2139 and fu.user_id =:userId and ep.app_id = 1
2140 and ea.function_cd = ep.function_cd
2142 select distinct app_r_f.function_cd from ep_app_role_function app_r_f, ep_app_function a_f
2144 and app_r_f.function_cd = a_f.function_cd
2147 select fa.app_id from fn_user fu, fn_user_role ur, fn_app fa where fu.user_id =:userId and fu.user_id = ur.user_id
2148 and ur.role_id = 999 and ur.app_id = fa.app_id and fa.enabled = 'Y'
2153 <sql-query name="getRoleFunctionsOfUserforAlltheApplications">
2155 select distinct ep.function_cd from fn_user_role fu, ep_app_role_function ep, ep_app_function ea
2156 where fu.role_id = ep.role_id
2157 and fu.app_id = ep.app_id
2158 and fu.user_id =:userId
2159 and ea.function_cd = ep.function_cd
2162 select fa.app_id from fn_user fu, fn_user_role ur, fn_app fa where fu.user_id =:userId and fu.user_id = ur.user_id
2163 and ur.app_id = fa.app_id and fa.enabled = 'Y'
2168 <sql-query name="getApplicationsofTheUserwithAdminAndRoleAdmin">
2169 <return alias="getApplicationsofTheUserwithAdminAndRoleAdmin" class="org.onap.portalapp.portal.domain.AppIdAndNameTransportModel" />
2171 SELECT app.APP_ID, app.APP_NAME, app.APP_TYPE FROM FN_APP app
2172 inner join FN_USER_ROLE userrole ON userrole.APP_ID=app.APP_ID
2173 where userrole.USER_ID = :userId AND userrole.ROLE_ID=999 AND (app.ENABLED = 'Y' OR app.APP_ID=1)
2175 SELECT DISTINCT app.APP_ID, app.app_name, app.APP_TYPE FROM fn_user user
2176 INNER JOIN fn_user_role user_role ON user.USER_ID = user_role.USER_ID
2177 INNER JOIN fn_role role ON role.ROLE_ID = user_role.ROLE_ID
2178 INNER JOIN fn_app app ON app.app_id= user_role.app_id and (app.enabled='Y' or app.app_id=1)
2179 INNER JOIN ep_app_role_function appfunction ON appfunction.app_id = app.app_id and appfunction.role_id=role.ROLE_ID and (appfunction.function_cd like '%APPROVER%')
2180 WHERE user.user_id = :userId ;
2184 <sql-query name="getApplicationsofTheUserContainsApprover">
2185 <return alias="getApplicationsofTheUserContainsApprover" class="org.onap.portalapp.portal.domain.AppIdAndNameTransportModel" />
2187 SELECT DISTINCT app.APP_ID, app.APP_TYPE, app.app_name FROM fn_user user
2188 INNER JOIN fn_user_role user_role ON user.USER_ID = user_role.USER_ID
2189 INNER JOIN fn_role role ON role.ROLE_ID = user_role.ROLE_ID
2190 INNER JOIN fn_app app ON app.app_id= user_role.app_id and (app.enabled='Y' or app.app_id=1)
2191 INNER JOIN ep_app_role_function appfunction ON appfunction.app_id = app.app_id and appfunction.role_id=role.ROLE_ID and (appfunction.function_cd like '%APPROVER%')
2192 WHERE user.user_id = :userId ;
2198 <sql-query name="getUserRolesForLeftMenu">
2199 <return alias="getUserRolesListForLeftMenu" class="org.onap.portalapp.portal.domain.UserRole" />
2202 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
2203 INNER JOIN fn_user user ON user.USER_ID = userrole.USER_ID
2204 INNER JOIN fn_app app ON app.app_id= userrole.app_id
2205 INNER JOIN fn_role role ON role.ROLE_ID = userrole.ROLE_ID
2206 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)
2211 <sql-query name="getRolesForLeftMenu">
2212 <return alias="getUserRolesListForLeftMenu" class="org.onap.portalapp.portal.domain.UserRole" />
2215 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
2216 INNER JOIN fn_user user ON user.USER_ID = userrole.USER_ID
2217 INNER JOIN fn_app app ON app.app_id= userrole.app_id
2218 INNER JOIN fn_role role ON role.ROLE_ID = userrole.ROLE_ID
2219 WHERE user.org_user_id =:org_user_id and (userrole.app_id = 1 or role.role_id = 999) and ((app.enabled='Y' and auth_central='Y' ) or app.app_id=1)
2224 <!-- Gets all functions for an application along with global functions and requires single parameter -->
2225 <sql-query name="getMenuFunctions">
2227 select f.function_cd from ep_app_function f
2228 where f.app_id =:appId
2230 select epa.function_cd from fn_role fnr, ep_app_role_function epr, ep_app_function epa where epr.role_id = fnr.role_id
2231 and epa.function_cd = epr.function_cd and fnr.role_name like 'global%' and fnr.app_id is null and epr.app_id = 1
2236 <sql-query name="getRequestIdsForApp">
2237 <return alias="getRequestIdsForApp" class="org.onap.portalapp.portal.domain.EPUserAppRolesRequest" />
2239 select * from ep_user_roles_request where app_id =:app_id
2244 <sql-query name="ApplicationUserRoles">
2245 <return alias="ApplicationUserRoles" class="org.onap.portalapp.portal.transport.EcompUserRoles" />
2247 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,
2248 fu.active_yn , fr.app_role_id, fr.role_name, epr.function_cd , epf.function_name
2249 from fn_user fu, fn_role fr, fn_user_role fur, ep_app_role_function epr , ep_app_function epf
2250 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' and epr.function_cd= epf.function_cd and epf.app_id=epr.app_id and fur.role_id=epr.role_id
2252 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,
2253 fu.login_id, fu.active_yn , fr.role_id, fr.role_name, earf.function_cd , eaf.function_name
2254 from fn_user_role a, fn_role fr, fn_user fu , ep_app_role_function earf, ep_app_function eaf
2255 where a.role_id in (select b.role_id from ep_app_role_function b where b.role_app_id = 1 and b.app_id =:appId) and a.user_id =fu.user_id and a.role_id = fr.role_id and fr.active_yn='Y' and fu.active_yn='Y'
2256 and earf.role_id = a.role_id and earf.function_cd = eaf.function_cd and earf.app_id = eaf.app_id and earf.role_app_id = 1 and fr.active_yn='Y' and fu.active_yn='Y';
2260 <sql-query name="getCurrentAppRoleFunctions">
2261 <return alias="ApplicationUserRoles" class="org.onap.portalapp.portal.transport.LocalRole" />
2263 select distinct fr.role_id, fr.role_name from fn_role fr, ep_app_function ef, ep_app_role_function epr where fr.role_id = epr.role_id and epr.function_cd = ef.function_cd
2264 and ef.function_cd =:functionCd and epr.app_id =:appId
2269 <sql-query name="deleteUserWidgetPlacement">
2271 DELETE FROM ep_pers_user_widget_placement WHERE user_id =:userId AND widget_id =:widgetId
2276 <sql-query name="getCentralizedAppsOfUser">
2277 <return alias="CentralizedAppsForRoles" class="org.onap.portalapp.portal.domain.CentralizedApp" />
2280 select distinct fa.app_id, fa.app_name
2281 from fn_role fr, fn_user_role fur, fn_app fa, fn_user fu
2282 Where fu.user_id = fur.user_id and fur.role_id = fr.role_id and fa.app_id = fur.app_id
2283 and fu.org_user_id = :userId and (fur.role_id = 999 or fur.role_id = 1) and fr.active_yn='Y' and ((fa.enabled = 'Y' and fa.auth_central='Y') or fa.app_id =1)
2289 <query name="getEPUserByOrgUserId">
2291 FROM EPUser WHERE orgUserId = :org_user_id
2295 <query name="getEPUserByLoginId">
2297 FROM EPUser WHERE loginId = :login_id
2301 <query name="getEPUserByLoginIdLoginPwd">
2303 FROM EPUser WHERE loginId = :login_id and loginPwd = :login_pwd
2307 <sql-query name="getGlobalRoleWithApplicationRoleFunctions">
2308 <return alias="getGlobalRoleWithApplicationRoleFunctions" class="org.onap.portalapp.portal.transport.GlobalRoleWithApplicationRoleFunction" />
2310 select distinct d.role_id, d.role_name , c.function_cd , e.function_name , d.active_yn , d.priority , c.app_id, c.role_app_id
2311 from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d , ep_app_function e
2312 where b.app_id = c.app_id and a.app_id = c.role_app_id and b.enabled = 'Y' and c.role_id = d.role_id and d.active_yn='Y' and e.function_cd = c.function_cd and c.app_id= :appId and e.app_id=c.app_id
2317 <sql-query name="getGlobalRolesOfPortal">
2318 <return alias="getGlobalRolesOfPortal" class="org.onap.portalapp.portal.domain.EPRole" />
2320 select * from fn_role where role_name like 'global_%' and app_id is null and active_yn='Y'
2325 <sql-query name="getAppRoleFunctionOnRoleIdandAppId">
2326 <return alias="appRoleFunctionOnRoleIdandAppId" class="org.onap.portalapp.portal.domain.EPAppRoleFunction" />
2328 select * from ep_app_role_function where app_id =:appId and role_id =:roleId
2333 <sql-query name="getAppFunctionOnCodeAndAppId">
2334 <return alias="appFunctionOnCodeAndAppId" class="org.onap.portalapp.portal.domain.CentralV2RoleFunction" />
2336 select * from ep_app_function where app_id =:appId and function_cd =:functionCd
2341 <sql-query name="getGlobalRoleForRequestedApp">
2342 <return alias="getGlobalRoleForRequestedApp" class="org.onap.portalapp.portal.transport.GlobalRoleWithApplicationRoleFunction" />
2344 select distinct d.role_id, d.role_name , c.function_cd , e.function_name , d.active_yn , d.priority , c.app_id, c.role_app_id
2345 from fn_user_role a, fn_app b, ep_app_role_function c , fn_role d , ep_app_function e
2346 where b.app_id = c.app_id and a.app_id = c.role_app_id and b.enabled = 'Y' and c.role_id = d.role_id and d.active_yn='Y' and e.function_cd = c.function_cd and c.app_id=:requestedAppId and c.role_id =:roleId and e.app_id = c.app_id
2351 <sql-query name="getBulkUploadPartnerGlobalRoleFunctions">
2352 <return alias="bulkUploadPartnerGlobalRoleFunctions" class="org.onap.portalapp.portal.transport.GlobalRoleWithApplicationRoleFunction" />
2354 select distinct fr.role_id, fr.role_name, fr.active_yn, fr.priority, epr.function_cd, ep.function_name, ep.app_id, epr.role_app_id
2355 from fn_role fr, ep_app_function ep, ep_app_role_function epr
2356 where fr.role_id = epr.role_id and ep.function_cd = epr.function_cd and ep.app_id = epr.app_id and epr.app_id = :appId and epr.role_app_id = 1
2361 <sql-query name="updateMenuFunctionalAndRoles">
2363 UPDATE fn_menu_functional m, fn_menu_functional_roles mr SET m.url='' WHERE m.menu_id=mr.menu_id AND mr.app_id=:app_id
2368 <!-- Remove any favorites associated with a menu item that is associated with this app -->
2369 <sql-query name="removeAppFromMenuFavorites">
2371 DELETE FROM fn_menu_favorites using fn_menu_favorites inner join fn_menu_functional_roles where fn_menu_functional_roles.app_id=:app_id AND fn_menu_functional_roles.menu_id=fn_menu_favorites.menu_id
2376 <!-- Remove all role, appid records from fn_menu_functional_role that is associated with this app -->
2377 <sql-query name="removeAppFromMenuFunctionalRoles">
2379 DELETE FROM fn_menu_functional_roles WHERE app_id=:app_id
2384 <!-- Remove all roles, rolefunctions, appid records from ep_app_role_function that is associated with this app -->
2385 <sql-query name="removeAppFromEpAppRoleFunction">
2387 DELETE FROM ep_app_role_function WHERE app_id=:app_id
2392 <!-- Remove all rolefunctions, appid records from ep_app_function that is associated with this app -->
2393 <sql-query name="removeAppFromEpAppFunction">
2395 DELETE FROM ep_app_function WHERE app_id=:app_id
2400 <!-- Remove all records from fn_user_role associated with this app that is associated with this app -->
2401 <sql-query name="removeAppFromFnUserRole">
2403 DELETE FROM fn_user_role WHERE app_id=:app_id
2408 <!-- Remove any widgets that is associated with this app-->
2409 <sql-query name="removeAppFromEpWidgetCatalogRole">
2411 DELETE FROM ep_widget_catalog_role WHERE app_id=:app_id
2416 <!-- Remove any notifications that is associated with this app-->
2417 <sql-query name="removeAppFromEpRoleNotification">
2419 DELETE FROM ep_role_notification using ep_role_notification inner join fn_role
2420 where fn_role.app_id=:app_id and ep_role_notification.role_id= fn_role.role_id
2425 <!-- Remove all records from fn_role that is associated with this app-->
2426 <sql-query name="removeAppFromFnRole">
2428 DELETE FROM fn_role where app_id=:app_id
2433 <!-- Remove app contact us entries that is associated with this app-->
2434 <sql-query name="removeAppFromAppContactUs">
2436 DELETE FROM fn_app_contact_us where app_id=:app_id
2441 <!-- Remove app personalization entries that is associated with this app-->
2442 <sql-query name="removeAppFromEpPersUserAppSel">
2444 DELETE FROM fn_pers_user_app_sel where app_id=:app_id
2449 <!-- Remove app personalization sorting entries that is associated with this app-->
2450 <sql-query name="removeAppFromEpPersUserAppManSort">
2452 DELETE FROM ep_pers_user_app_man_sort where app_id=:app_id
2457 <!-- Remove rows from user role request table entries that is associated with this app-->
2458 <sql-query name="removeAppFromEpUserRolesRequest">
2460 DELETE FROM ep_user_roles_request where app_id=:app_id
2465 <!-- Remove rows from web analytics table entries that is associated with this app-->
2466 <sql-query name="removeAppFromEpWebAnalytics">
2468 DELETE FROM ep_web_analytics_source where app_id=:app_id
2473 <!-- Remove row from fn app table -->
2474 <sql-query name="removeAppFromFnApp">
2476 DELETE FROM fn_app where app_id=:app_id
2482 <query name="getBasicauthAccount">
2483 select id,password from BasicAuthCredentials
2486 <query name="getMicroserviceInfo">
2487 select id,password from MicroserviceData
2490 <sql-query name="getUserRoleOnUserIdAndRoleIdAndAppId">
2491 <return alias="getUserRoleOnUserIdAndRoleIdAndAppId" class="org.onap.portalapp.portal.domain.EPUserApp" />
2493 select * from fn_user_role where user_id= :userId
2494 and role_id= :roleId
2500 <sql-query name="userAppGlobalRoles">
2501 <return alias="userAppGlobalRoles"
2502 class="org.onap.portalapp.portal.domain.EPRole" />
2504 select fr.role_id , fr.role_name ,fr.active_yn, fr.priority, fr.app_id, fr.app_role_id
2505 from fn_user_role a, fn_role fr, fn_user fu
2506 where a.role_id in (select b.role_id from ep_app_role_function b where b.role_app_id = 1 and b.app_id =:appId) and a.user_id =fu.user_id and a.role_id = fr.role_id and fr.active_yn='Y' and fu.active_yn='Y' and fu.user_id =:userId
2511 <sql-query name="getAllCentralizedAppsRoles">
2512 <return alias="allCentralizedAppsRoles"
2513 class="org.onap.portalapp.portal.transport.CentralizedAppRoles" />
2515 select distinct fa.app_id, fa.auth_namespace, fr.role_name, fr.role_id from fn_app fa, fn_role fr where fa.auth_central = 'Y' and fa.auth_namespace is not null
2516 and (fa.app_id = fr.app_id or COALESCE(fr.app_id,1) = fa.app_id) and fr.active_yn = 'Y' order by fa.app_id ;
2520 <sql-query name="getUserCentralizedAppRoles">
2521 <return alias="userCentralizedAppRoles"
2522 class="org.onap.portalapp.portal.transport.CentralizedAppRoles" />
2524 select distinct fur.app_id, fa.auth_namespace, fr.role_name, fur.role_id from fn_user_role fur, fn_app fa, fn_role fr, fn_user fu
2525 where fa.app_id = fur.app_id
2526 and fr.role_id = fur.role_id
2527 and fu.user_id = fur.user_id
2528 and fu.ORG_USER_ID = :orgUserId
2529 and fa.auth_namespace is not null
2530 and fr.active_yn = 'Y'
2534 <sql-query name="getAprroverRoleFunctionsOfUser">
2536 select distinct ep.function_cd from fn_user_role fu, ep_app_role_function ep, ep_app_function ea
2537 where fu.role_id = ep.role_id
2538 and fu.app_id = ep.app_id
2539 and fu.user_id =:userId
2540 and ea.function_cd = ep.function_cd and ea.function_cd like 'approver|%'
2543 select fa.app_id from fn_user fu, fn_user_role ur, fn_app fa where fu.user_id =:userId and fu.user_id = ur.user_id
2544 and ur.app_id = fa.app_id and fa.enabled = 'Y'
2548 <sql-query name="getUserApproverRoles">
2550 select distinct fr.role_name from fn_user_role fu, ep_app_role_function ep, ep_app_function ea, fn_role fr
2551 where fu.role_id = ep.role_id
2552 and fu.app_id = ep.app_id
2553 and fu.user_id = :userId
2554 and fu.role_id = fr.role_id and fr.active_yn='Y'
2555 and ea.function_cd = ep.function_cd and ea.function_cd like 'approver|%'
2558 select fa.app_id from fn_user fu, fn_user_role ur, fn_app fa where fu.user_id =:userId and fu.user_id = ur.user_id
2559 and ur.app_id = fa.app_id and fa.enabled = 'Y'
2563 <sql-query name="getAdminAppsForTheUser">
2565 select fa.app_id from fn_user_role ur,fn_app fa where ur.user_id =:userId and ur.app_id=fa.app_id and ur.role_id= 999 and (fa.enabled = 'Y' || fa.app_id=1)
2572 <sql-query name="getUserRoleOnUserIdAndAppId">
2573 <return alias="getUserRoleOnUserIdAndAppId" class="org.onap.portalapp.portal.domain.EPRole" />
2576 select fr.role_id , fr.role_name ,fr.active_yn, fr.priority, fr.app_id, fr.app_role_id FROM fn_role fr, fn_user_role fur WHERE fr.role_id = fur.role_id AND fur.user_id= :userId AND fur.app_id = :appId AND fr.active_yn='Y'
2581 <sql-query name="getAllAdminAppsofTheUser">
2583 select fa.app_id from fn_user_role ur,fn_app fa where ur.user_id =:userId and ur.app_id=fa.app_id and ur.role_id= 999
2587 <sql-query name="getAllAppsFunctionsOfUser">
2589 select distinct ep.function_cd from fn_user_role fu, ep_app_role_function ep, ep_app_function ea, fn_app fa , fn_role fr
2590 where fu.role_id = ep.role_id
2591 and fu.app_id = ep.app_id
2592 and fu.user_id =:userId
2593 and ea.function_cd = ep.function_cd
2594 and ((fu.app_id = fa.app_id and fa.enabled = 'Y' ) or (fa.app_id = 1))
2595 and fr.role_id = fu.role_id and fr.active_yn='Y'
2597 select distinct app_r_f.function_cd from ep_app_role_function app_r_f, ep_app_function a_f
2599 and app_r_f.function_cd = a_f.function_cd
2602 select fa.app_id from fn_user fu, fn_user_role ur, fn_app fa where fu.user_id =:userId and fu.user_id = ur.user_id
2603 and ur.role_id = 999 and ur.app_id = fa.app_id and fa.enabled = 'Y'
2607 <sql-query name="updateFnUser">
2609 UPDATE fn_user fu SET fu.language_id=:language_id WHERE fu.login_id=:login_id
2612 </hibernate-mapping>